Passionate about data

Data and its implications on software design and development.

Usage of Mixed Case Database Object Names Is Dangerous

Some versions back, Oracle would not allow to create database object names with mixed cases, even if we tried to create them, we could not. In newer versions of Oracle we can create tables, columns, indexes etc using mixed case or lower case, when the names are put inside double quotes. For example

Create mixed case Customer table and Index on the table
CREATE TABLE "Customer" (
"CustomerID" number(10)

CREATE INDEX "IDX_Customer_CustomerID"
on "Customer"("CustomerID");

10 Node Riak Cluster on a Single Machine

When trying to evaluate NoSQL databases, its usually better to try them out. While trying them out, its better to use them with multiple node configurations instead of running single node. Such as clusters in Riak or Replica-set in mongodb maybe even a sharded setup. On our project we evaluated a 10 node Riak cluster so that we could experiment with N, R and W values and decide which values where optimal for us. In Riak here is what N, R and W mean.

N = Number of Riak nodes to which data will be replicated R = Number of Riak nodes which have to return results for the read to be considered successful W = Number of Riak nodes which have to return a write success before the write is considered successful

Transactions Using Groovy.SQL With Spring Annotations and Connection Pools

When using Groovy with Spring framework,  interacting with the database can be done using the Groovy.SQL class which provides a easy to use interface. When using Groovy.SQL, if we have a need to do transactions, we have the .withTransaction method that accepts a closure, to which we can pass in code to execute within the transaction. In our project since we were using spring already, using annotations to define transactions would be a great. Standard @Transactional annotations with Groovy.SQL will not work, since every place where the Groovy.SQL is used a new connection is acquired from the connection pool causing the database work to span multiple connections, which can result in dead-locks on the database. What we really want is that the database connection be the same across all invocations of Groovy.SQL with-in the same transaction started by the annotated method.

Backup in Mongodb Replica-set Configurations

There are multiple ways to take backups of mongodb is different configuraitions, one of the configuration that I have been involved recently is replica-sets. When mongodb is running in replica-set configuration, there is a single primary node and multiple secondary nodes. To take backup of the replica-set we can either do a mongodump of one of the nodes or shutdown one of the secondary nodes and take file copies, since in a replica-set all nodes have the same data (except arbiter). Lets see how we could deal with mongodump method of taking backup.

MSSQL JDBC Driver Behavior

My latest project involves talking to MS-SQL Server using the JDBC driver and Java. While doing this we setup the database connection and had a simple SQL to get the first_name and last_name for a unique user_id from the application_user table in the database.

SELECT first_name,last_name 
FROM application_user 
WHERE user_id = ?

Back to Blogging

There has been a long pause in my blogging activity. I was trying to finish of my latest writing engagement in regards to NoSQL. Working with Martin Fowler on NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence was really fun. This book will provide a concise text and easy way to understand for everyone the rise of the NoSQL movement and help with what kinds of trade-offs need to be made while working with NoSQL.

The book should soon be in print and e-book formats. Martin has written more about it here

With So Much Pain, Why Are Stored Procedures Used So Much

I keep encountering situations where all the business logic for the applications is in stored procedures and the application layer is just calling the stored procedures to get the work done and return the data. There are many problems with this approach some of them are.

  • Writing stored procedure code is fraught with danger as there are no modern IDE’s that support refactoring, provide code smells like “variable not used”, “variable out of scope”.

Replica Sets in MongoDB

Replica sets is a feature of MongoDB for Automatic Failover, in this setup there is a primary server and the rest are secondary servers. If the primary server goes down, the rest of the secondary servers choose a new primary via an election process, each server can also be assigned number of votes, so that you can decide the next primary based on data-center location, machine properties etc, you can also start mongo database processes that act only as election tie-breakers these are known as arbiters, these arbiters will never have data, but just act as agents that break the tie.

Schema Less Databases and Its Ramifications.

In the No-SQL land schema-less is a power full feature that is advertised a lot, schema-less basically means you don’t have to worry about column names and table names in a traditional sense, if you want to change the column name you just start saving the data using the new column name Lets say you have a document database like mongoDB and you have JSON document as shown below.


Effective Use of Data for Better Customer Experience.

For more than seven years I have been getting offers for credit cards from Airlines and Banks. One particular bank has been sending me these solicitations for more than seven years. That is 12 mailings per year, more than 72 mailings so far, remember these are physical paper mailings not the electronic kind. I don’t like the junk, it hurts the environment and worst of all I think its not good use of the data they have. How hard is it to design a system around the data they have.