Category Archives: DBMS

Types of NoSQL Databases

NoSQL databases can be divided into 4 major type

Key-Value: The simplest one, can be though of as a hashmap. Data can grow exponentially without impacting performance much, as long as your keys are unique.

Example: Redis, Riak

Document Based: This is kind of an extension to key-value format, by providing a proper format to the value/ document being saved. Meta-Data is provided to make sure documents are tagged and searchable.

Example: MondoDB, CouchDB

Column Based: In contrast to Row Based storage of normal RDBMS, a column based storage, keeps data stored columnwise. This gives an advantage for searching data based on columns easily and at the same time lets your data grow upto large levels by supporting distribution of data.

Good description of Column-based storage: https://en.wikipedia.org/wiki/Column-oriented_DBMS

Example: Cassandra, Vertica

Graph Based: This kind of databases are ideal for data which is connected to each other in some logical way. Or in simple words, if you can represent your data in form of graph. One good example is A is friend of B, B is friend of C, so we can recommend A to be friends with C.

Example: Neo4J, OrientDB

Additional reads

https://www.3pillarglobal.com/insights/exploring-the-different-types-of-nosql-databases

http://opensourceforu.com/2017/05/different-types-nosql-databases/

http://www.jamesserra.com/archive/2015/04/types-of-nosql-databases/

https://en.wikipedia.org/wiki/NoSQL

CAP Theorem (you can choose 2 of the three- Consistency, Availability and Partition tolerance) Based analysis of NoSQL databases

http://blog.nahurst.com/visual-guide-to-nosql-systems

Generating ER diagram from database -2

Sometime back I wrote about DBvisualizer to generate schema ER design from database.

Here is another way by using schemaspy.

http://schemaspy.sourceforge.net/

This is a simple java based tool/ jar file. As per example given in link above, all you need to run the jar file providing database access details.

java -jar schemaSpy.jar -t dbType -db dbName [-s schema] -u user [-p password] -o outputDir 

You might want to give database drivers jar file path. For example, for Postgres

java -jar /home/kamal/pathto/schemaSpy_5.0.0.jar -t pgsql -db dbnamehere -s public -u dhusername -p dbpassword -host localhost -port 5432  -o /home/kamal/outputdir -dp /home/kamal/pathto/postgresql-9.3-1104.jdbc4.jar

Data Modeling at different levels

When you are designing database for an application, there can be 3 core levels at which you can design your database.

1. Conceptual Level: At this level you are only aware of high level entities and their relationships. For example you know that you have “Employee” Entity who “works for” a “Department” and “has” an “Address”. You are not worried about details.

2. Logical Level: You try to add as much details as possible, without worrying about how it will actually be converted to a physical database structure. So will provide any attributes for “Employee” i.e. Id, FirstName, LastName, AddressId, Salary and define primary and foreign key relations.

3. Physical Level: This is the actual representation of your database design with exact column names, types etc.

database

More info- http://www.1keydata.com/datawarehousing/data-modeling-levels.html

Shared Nothing vs Shared Everything

In database cluster implementation we can have multiple ways to make sure how different nodes will communicate with each other.

Shared nothing approach: None of the nodes will use others memory or storage. This is best suited for the solutions where inter node communication is not required, i.e. a node can come up with a solution on its own.

Shared Memory: In this approach memory is shared, i.e. each node/ processor is working with same memory. This is used when we need nodes to share solutions/ calculations done by other nodes and are available in memory.

Shared Everything: In this approach nodes share memory plus storage. This makes sense when nodes are working on problem where calculations and data created/ used by node is dependent on others.

Further Reads:
https://www.quora.com/What-are-the-differences-between-shared-nothing-shared-memory-and-shared-storage-architectures-in-the-context-of-scalable-computing-analytics

https://en.wikipedia.org/wiki/Shared_nothing_architecture

Kill blocking session in Oracle

To check which all session are live on an oracle server

select * from v$session

To check blocking sessions

SELECT
O.OBJECT_NAME,
S.SID,
S.SERIAL#,
P.SPID,
S.PROGRAM,
SQ.SQL_FULLTEXT,
S.LOGON_TIME
FROM
V$LOCKED_OBJECT L,
DBA_OBJECTS O,
V$SESSION S,
V$PROCESS P,
V$SQL SQ
WHERE
L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;

To kill a session

alter system kill session ‘SID,SERIAL#’;

Mybatis- Using SelectProvider and ResultMap

I recently wrote about how a basic mybatis application can be set with Spring.

But there are situations when queries can be a bit complex than a simple insert or select. In such cases

@SelectProvider

can help us build a dynamic query.

For example, in my user mapper, I need to search based on Id OR status.

@SelectProvider(type = UserSQLBuilder.class, method = "getUsersProvider") 
@ResultMap(MyConfig.USER_MAP)
public List searchUser(@Param("id") String id,@Param("status")  String status);

My UserSQLBuilder class would have a method called getUsersProvider which will create a dynamic query based on paramaters I am passing here.

public class UserSQLBuilder {

	public String getUsersProvider(Map parameters) {
		
		String id = (String) parameters.get("id");
		String status = (String) parameters.get("status");
		
		StringBuilder query = new StringBuilder();
		query.append("select id, user_name, user_address, status from users");
		if (status.equals("NA") && !id.equals("NA")) {
			query.append(" where id like '%" + id + "%'");
		} else if (!status.equals("NA") && id.equals("NA")) {
			query.append(" where status ='" + status + "'");
	        return query.toString();
	}
}

Another important thing to note here is that I am using @ResultMap to map result of query to my User (List) object. If we look at the query, it returns user_name, user_address etc. Whereas in my User class I have userName, userAddress and so on. The challenge is to map query values to object values.

There are actually multiple ways.

1. I can simply modify my query – select user_name as userName, user_address as userAddress from users.
2. Explicitly tell my mapper to map the values from column to object like

@SelectProvider(type = UserSQLBuilder.class, method = "getUsersProvider") 
@Results({ @Result(property = "userName", column = "user_name"),
@Result(property = "userAddress", column = "user_address") })
public List searchUser(@Param("id") String id,@Param("status")  String status);

3. Create a @ResultMap which can be reused, useful in cases when same mapping has to be done more than once.
@ResultMap(MyConfig.USER_MAP)
statement tells the mapper to look for a map in config file.

in MyConfig.java, I will define

public static final String REPORT_MAP = "com.test.mapper.UserMapper.user";
//code here

private void registerUserMap(org.apache.ibatis.session.Configuration config) {
		// registering result maps
		List flags = new ArrayList();
		List resultMappings = new ArrayList();
		flags.add(ResultFlag.ID);

		org.apache.ibatis.mapping.ResultMapping.Builder resultBuilder = new org.apache.ibatis.mapping.ResultMapping.Builder(
				config, "userName", "user_name", String.class);
		resultBuilder.flags(flags);
		resultMappings.add(resultBuilder.build());

		resultBuilder = new org.apache.ibatis.mapping.ResultMapping.Builder(config, "userAddess",
				"user_address", String.class);
		resultBuilder.flags(flags);
		resultMappings.add(resultBuilder.build());

		Builder resultMapBuilder = null;
		resultMapBuilder = new Builder(config, "com.gcp.dao.mapper.ReportMapper.report", Report.class, resultMappings, true);
		config.addResultMap(resultMapBuilder.build());
	
	}

And while defining my session factory, I will register this Map.

 @Bean
		public SqlSessionFactory sqlSessionFactory() throws Exception {
			org.apache.ibatis.mapping.Environment environment = new org.apache.ibatis.mapping.Environment("",
					new JdbcTransactionFactory(), getDataSource());
			org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration(environment);

			registerReportMap(config);

			return new SqlSessionFactoryBuilder().build(config);
		}

Using Mybatis with Spring

Mybatis is Java Persistence framework, build upn JDBC. I look at it mostly as a bridge between conventional JDBC and an ORM solution like hibernate. The flexibility Mybatis provide is to write queries similar to JDBC, yet takes away all the complexity and boilerplate code for creating and maintaining connections and transactions. It can also map your objects to Tables like ORM solution, without getting into complexities of an ORM fraework, but at a cost of some additional code.

Mappers: Mappers are core of Mybatis implementation. A Mapper class would contain queries to be executed by mybatis. A good practice is to create a mapper for each table in database.

Here is a simple Mapper class

public interface UserMapper {

	/**
	 * This method validates the credentials for any user.
	 * 
	 * @param userName
	 * @param password
	 * @return
	 */
	@Select("select id from myuser where username=#{userName} and password=#{password}")
	public Integer verifyAdminUser(@Param("userName") String userName,
			@Param("password") String password);

	/**
	 * This method returns list of all users.
	 * 
	 * @return
	 */
	@Select("select username, password from myuser")
	public List getUsers();

In the getUsers method, you can see mybatis will automatically convert the results into a Java List with User objects.

Coming to Setting up Mybatis with Spring, if you are using Java based configuration, all you need to add is

@MapperScan("com.test.mapper")

and tell location of your mappers package(s).

In addition you would want spring to manage transactions for you by

@MapperScan("com.test.mapper")
public class MyConfig implements TransactionManagementConfigurer{

//JNDI lookup for database
private DataSource getDataSource() {
		InitialContext ic;
		DataSource ds = null;
		try {
			ic = new InitialContext();
			String jndiName ="jndi";
			ds = (DataSource) ic.lookup(jndiName);
		} 
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ds;
	}

	 @Bean
		public SqlSessionFactory sqlSessionFactory() throws Exception {
			org.apache.ibatis.mapping.Environment environment = new org.apache.ibatis.mapping.Environment("",
					new JdbcTransactionFactory(), getDataSource());
			org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration(environment);

			return new SqlSessionFactoryBuilder().build(config);
		}

	@Override
	public PlatformTransactionManager annotationDrivenTransactionManager() {
		return  new DataSourceTransactionManager(getDataSource());
	}

You are all set to use Mappers just by Autowiring in any class.

Returning auto generated id in spring – mybatis

Faced a problem with returning the autogenerated id for new rows being created for a postgres table through spring – mybatis.

@Insert("insert into user (name) values (#{name})")
public void insertActor(User user);

One solution ofcourse was to simply get nextval from the sequence, but I wanted something cleaner where I need not be worried about multithreading issues.

Solution one Tried: One good solution found for postgres was using “Returning” keyword with Insert statement

insert into user (name) values (#{name}) Returning id;

Somehow that did not work with mybatis.

Solution that worked:

@Options(useGeneratedKeys=true, keyProperty = "userId", keyColumn="id")
@Insert("insert into user (name) values (#{name})")
public void insertActor(User user);

@Options, with useGeneratedKeys flag worked just fine. keyProperty defined the property name in Java and keyColumn name defined name of column in table.

Postgres error: Relation Does not exist

Faced an interesting error while accessing postgres table with a simple java code.

"select * from User"

A little googling revealed that if you are using multicase table name (U is caps), you will need to give the table name in quotes.

http://stackoverflow.com/questions/695289/cannot-simply-use-postgresql-table-name-relation-does-not-exist

A better fix would be to always use lower case characters when declaring tables in postgres.