Databases in ConMan
Databases always filled me with dread because they reminded me of the IT Skills classes we used to have at school. In these classes the teacher tried to teach us Excel but knew so little that they would pretty much read out loud from a manual. This was not so much fun. I actually remember a friend of mine drawing patterns in her multiple choice exam sheet because she cared so little.
But the more you understand about databases and the power of data the more exciting it becomes. It is not all about Excel (although now I recognise that it is actually a pretty amazing tool). The most successful businesses have harnessed their ability to use data and although there is always a margin of error they’re able to predict and build their businesses in a much more strategic way. Whether this is ethical or not is a whole other story.
So now that I have to implement a basic DB to my ruby Contact Management (ConMan™) application, even though it’s a long way off from gathering huge amounts of data that I can gain any insights from (I’m pretty much adding contacts to a list at this point), it is cool to try and link my code to a database.
I started by looking at SQL databases. SQL and most other relational database management systems are set up so that the basics are relatively easy to use. I’ve read over the basics and when you are only dealing with one table, the commands are straightforward.
Here are some of the most useful commands that I have used so far:
CREATE — to create a new database
USE — to use a table in that database
SHOW — to show fields in that database
SELECT — to find specific fields in the database
DROP — to delete rows of a database
The Repository Pattern
In order to use effective tests with writing and reading from a Database I am using the Repository Pattern discussed in Patterns of Enterprise Applications by Martin Fowler. A Repository according to Wikipedia is a central location in which data is stored and managed. Using the Repository Pattern introduces a layer which handles and manages data in a way that you would like. E.g. A hash-map for tests and a DB for a real implementation.
In other words, this Pattern introduces a layer between the domain (business logic) layer and the data or data mapping layer (which is more useful for larger complex programs with lots of inter-related data).
The data mapping layer is a class that receives an object and then can get the details of the object from the database, check if it exists and update if necessary.
There are many different ways to introduce this, but one way is that a client creates an object to search for within a Repository. This can then talk to the data mapping layer to carry out the necessary command (eg. read, update, delete).
The Active Record Pattern
Prior to this I had just used the Active Record gem that implements the Active Record Pattern. This provides a query interface that comes with loads of preset methods you can use on the data you are trying to access from your database (DB). It directly accesses the DB you are using, which is really powerful, but makes testing really difficult. That is why I have introduced a layer in between my Database and CRUD functions using the Repository Pattern.
Active Record can be really powerful, but there is some debate as to if Active Record has too many methods. This is because it wraps all the SQL commands in Ruby code, and there are a lot of SQL commands.
So by using SQL I have been able to introduce these ideas to my ConMan app. He has come a long way since my first Ruby command line app and now has a database of his very own!