Tagged with Programming

ORM vs DBAL [explained]

I work for a large organisation who have a large legacy code base, so picking and choosing frameworks and technologies isn’t done lightly. We often bat ideas around and do a bit of digging around on Stackoverflow and Google before we even begin to consider downloading a copy and play with it.

Recently on the agenda there has been a lot of talks of database frameworks. There’s a lot of them around now, and they all do the same thing but in slightly different ways. One of the key things to understand however is what all the buzz-words mean. Google will through up words like DBAL and ORM. But what do they mean?

Firstly let’s think about how we generally connect to a database in PHP, and why you might need a framework. In PHP there’s three main, methods of connecting to a standard MySQL database – mysql_query(), mysqli_query(), and the PDO(). What they all have in common is that you give them a full SQL statement to evaluate and pass to the database, this is sometimes known as a Prepared Statement.

DBAL – Abstraction Layers
So for example using PHP’s now ancient mysql_query() a query may look like this, notice how we’ve defined our statement for the query.

1
2
3
4
<?php 
    $sql = "SELECT * FROM `users`"; 
    $query = mysql_query($sql); 
?>

Now lets say we’re writting lots of these types of queries, and we don’t want to worry about writting all the SQL for each one. A framework can help you by providing a nice abstraction layer, on some places on the internet this is known as DBALDataBase Abstraction Layer. What makes these frameworks useful is that they are able build queries on the fly from a given list of parameters. Abstraction Layers are quite simple to build, so for example if we were to roll our own vague Abstraction Layer it might have a method to perform an insert for us like so:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
    /**
     *  Inserts into database...
     *  @params string $table - table name
     *  @params array  $values - key/value pair to insert
     *  @return resource
     */
    function insert($table, array $values)
    {
        global $conn;//import connection
 
        $sql = "INSERT INTO {$table} (";
        //extract column names from the keys
        $sql.= implode(", ", array_keys($values));
        $sql.= ") VALUES('";
        //extract values
        $sql.= implode("', '", $values);
        $sql.= ")";
 
        return mysql_query($sql, $conn);
    }
 
    $result = insert('users',array('username'=>'bond007','password'=>'martini'));
 
?>

You can see already that this is a huge timesaver while coding! But hypothetially say what if one day we decided we’d like to port our application across to using Postgres or Oracle? Well, because we’ve abstracted all those scattered database queries through-out your code they now all funnel through our Abstraction Layer. This means we only have to update the underlying database interaction. So the above might look like this for Oracle:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?php
    /**
     *  Inserts into database...
     *  @params string $table - table name
     *  @params array  $values - key/value pair to insert
     *  @return resource
     */
    function insert($table, array $values)
    {
        global $conn;//import connection
 
        $sql = "INSERT INTO {$table} (";
        //extract column names from the keys
        $sql.= implode(", ", array_keys($values));
        $sql.= ") VALUES('";
        //extract values
        $sql.= implode("', '", $values);
        $sql.= ")";
 
        $stmt = oci_parse($conn, $sql);
        return oci_execute($stmt);
    }
 
    $result = insert('users',array('username'=>'bond007','password'=>'martini'));
 
?>

What you’ll have noticed in the above is that the query is the same but now we’re running the query through PHP’s Oracle interface rather than MySQL. What we’ve done is begin to make our application Database Agnostic. The example above of course if pretty straight forward but it is possible to think outside the box, and use a NoSQL solution. So perhaps we’re not inserting into the “users” table but instead into the “users” collection in MongoDB or keyspace in Cassandra.As you can see the possiblities here are quite big.

One of the most popular Database Frameworks and big names around is Doctrine. If you take a look at the different projects they have listed you’ll notice they provide ORM/DBAL’s for Relational Databases, like MySQL/Oracle and then also NoSQL Databases like MongoDB and CouchDB.

ORM – Object Relational Mapping
So whats all this ORM business then? Well ORM stands for Object Relational Mapping, which sounds kinda scary and it is a bit at first glance. Object Relational Mapping is the idea of taking Database Abstraction to the next level and binding it to our application code. It’s hard to kind of explain, but it means you’ll need to define a PHP Object in your application that will correspond to your database schema. Let me try and explain via a code example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<?php
    class User extends MyFirstORM
    {
        /**
         * User ID
         * @var int - defined as INT(11) in schema 
         *            auto-incremental primary key
         */
        public $id;
 
        /**
         * Username
         * @var string - defined as VARCHAR(32) in schema
         */
        public $username;
 
        /**
         * Password
         * @var string - defined as VARCHAR(16) in schema
         */
        public $password;
 
        /**
         * Class Constructor
         */
        public function __construct()
        {
            //some application logic here perhaps
            return ;
        }
    }
 
    //Create a new User Object
    $user = new User();
    //Assign properties of the User Object
    $user->username = 'bond007';
    $user->password = 'martini';
    //Call save method in parent class (MyFirstORM)
    $user->save();
 
?>

I’m hoping the above makes sense, and you can see what we’re trying to achieve here. You’ll notice you don’t have to pass an array or specify a table like our DBAL, instead the ORM framework is intelligent enough to be able to figure it all our for you. What this means is you can create numerous models in your application and using your ORM you can persist them straight to the database without even inferring what the SQL interaction should be. While it might seem clever it comes at a cost. It means your application needs to be structured in a certain way, and it also means you may have to keep your database schema and the models defined in your code base synchronized.

So hopefully you’ve got a vague idea of the benefits of Object Relational Mapping and Database Abstraction Layers for your application. It’s important to remember however my examples above are intended to be simple to understand and straight forward, in reality things are a little more complicated. Whether you use an ORM or a DAL, they’ll require bootstrapping and you’ll be introduced to the method chaining syntax, and more importantly you’ll be encouraged (if not forced) into using PHP 5.3+ which can be a plus or a minus given consideration of legacy code!

Summary
To summarize some of my experiences…

I’ve found ORM’s to be complicated and time-consuming to setup and often come with their own syntax which indicates foreign keys and many-to-many relationships to the ORM. However on the other hand they often provide a very powerful interface for querying data, and given that we’re dealing with objects we can think about solving problems using Object Orientated Programming principles. Remember one of the greatest things about an Object Orientated approach is re-usable code, we can re-create objects over and over again in many places in our application with little over-head. It also means we can manipulate objects many times before we finally commit the object to the database.

In comparision I’ve found DBAL’s to be the quickest solution and often provide a lesser of a learning curve. It also means you get more direct control over your database, and are able to build queries how you want to using more of the features of that database system. For example using Stored Procedures is something which is almost impossible using an ORM but is do-able in most DBALs. We’re also able to begin harnessing the power of transactions in our application along with safely quoting our input and protecting against SQL injection.

That’s my thoughts and experiences using ORMs and DBALs. Let me know if you’ve got anything to contribute in the comments.

Thanks
Adam.

Tagged , , , , , , , , , , ,

Howto get going with your Pi…the hardware [part 1]

So you were curious and thought you’d join the crowd, you thought to yourself “I’ll get me one of those Raspberry Pi things”! Then boom! Weeks later of after many emails telling warning you of delays, and checking the Raspberry Pi website it then arrives.

So now you have it… that credit card sized bit of circuit board, but now what do you do with it?

Powering Your Pi

Firstly if you were half a sleep when you ordered your Raspberry Pi you may have forgotten to order any of the accessories including power adaptor – as I did! I’ve been doing my research and the Pi uses about 500mAh/700mAh depending on the model you got (A or B), this power rating is a minimum, so ideally we’re looking at using quite a bit more somewhere about the 1,200mAh mark. This means your PC or USB Hub isn’t going to provide quite enough power (unless you’ve researched thoroughly your hardware and know otherwise), so the best way to get this thing powered is to use a mobile phone charger. I’ve actually got a Blackberry Bold 9900, and the charger works fine in this occasion. I’ve also got a Google Nexus 7, which I’ve looked into and it provides 2A (2amps thats 2,000mAh) which is ample. I’ve also been a bit cheeky and managed to run my Pi from the USB Media Play port on my 32″ Samsung TV, however I wouldn’t recommend this as a permanent power source. I’ve also been looking around and if your stuck for a power adaptor the Nokia AC-10X is perfect and its cheap – Amazon sell them for around £2-£3.50 so really can’t go wrong!

Storage Devices

Again I was asleep and knew I had a spare SD card lying around somewhere that I wasn’t using. Generally when it comes to SD cards on the Raspberry Pi theres only a few things to consider, a) how much space do you need, b) is speed important, and c) do you care about reliability. When it comes to the first point, you need to consider what you might use the Pi for. If you plan on getting it setup as a media centre or using it as a form of back up you might wanna splash out on a 32GB card, but as a bare minimum you should be looking at is 4GB. When we’ve installed Raspbian and got a few tools, you’ll have a little leftover from 4GB which will be enough to begin programming on (as intended). The second consideration is speed, SD Cards come with a rating known as a “class”, this class is usually a good indication of how many megabytes a second can be read from the card. As a minimum I’d recommend a class 6 rated card, this means you’ll get a healthy 6MB/s read speed (on average). If you plan on programming or running some really heavy applications or web services you may want to consider a class 10 card. The final consideration really is do you value the data thats going to be on the SD card. If your coding something mission critical I’d recommend backing up your code to a desktop PC as a best practice, but ultimately this is a judgement call on which brand of SD Card you want. I’ve got a Veho card thats years old, I know people who swear by SanDisk and others by Transcend – its your call.

Interacting

On your very first boot, you’ll want to configure a few things and you’ll want to see the pretty coloured BIOS screen. Obviously we need to plug it into a monitor or TV. The Raspberry Pi supports HDMI and RCA (as stated on the err …box), I have a Playstation 3 so I ‘borrowed’ the HDMI cable which worked fine on my Samsung TV. I later found an old RCA (Yellow/Red/White) cable from an ancient DVD player which also does the trick (for those that have forgotten the RCA/Scart age of TVs you’re only really concerned about the Yellow one for video.) I have to say on my TV I didn’t really notice too much difference between the HDMI or the RCA cable, probably because the Pi outputs as the same resolution on both outputs, however it may differ between TV’s (RCA might be prone to scanlines or wrong refresh rates/flickering). The other bits we need to get going are a mouse and keyboard. If you’re used to a desktop and don’t have a desire to embrace the command line, then you’ll want the mouse – if not brilliant you can use a keyboard only. A problem I’ve read about and encountered myself is repeating keypresses and what appears as unresponsiveness. I have a wireless Microsoft keyboard and mouse, that runs off of a small USB adaptor, this adaptor does BOTH mouse and keyboard on the single USB port. What I found is that is draws quite a bit of power from the USB port on the Raspberry Pi, this results (when I was using my Samsung TV to power it) in repeating and unresponsiveness. I’d press a button once on the keyboard and I’d get a whole row of that letter appear on screen. To solve this I dug out an old wired USB keyboard and wired USB mouse, and it worked fine with that.

Connectivity

Finally the last bit in the jigsaw, network connectivity. It’s not a mandatory thing to have setup on your Pi, however it’s damned useful! The model B Raspberry Pi comes equipped with a 100MBit/s Ethernet port, this is perfect for plugging straight into your router or PC (via ICS – Internet Connection Sharing). If you network your Raspberry Pi it means you can share files between your desktop PC and the Pi, as well as do other cool things like setup a web-server, or remote access the Pi over the internet using SSH, with a network connection the possibilities really open up to all the cool stuff you can do. Whats more you want to be able to share what you’ve done with your friends right?

If you’re close to your router I’d suggest you take advantage of the router’s speed for the ethernet connectivity, if your some distance from the router then ICS might be the way to go. I’m not going to cover ICS but it’s pretty simple, you need to setup a static IP on your desktop PC for your Raspberry Pi to connect to, and then tick the little box which tells Windows to share your Wireless connection.

Another option which might not be apparent is using a Power Ethernet adaptor. This allows you to route an ethernet connection from your router to a wall socket in your house and then pick it up again else where and connect to the ethernet port on the Pi. This maybe more convenient if say you plan on using your Pi to play video streams in your living room and your router is elsewhere in the house.

The other option is to buy a supported USB Wireless Dongle. You may have to hunt around on the internet and do your research before had, as Linux and wireless drivers can be a real pain to setup. One adaptor I’ve seen around that seems reasonable is an Edimax Wireless-N150 Nano adaptor, as of writing Ebuyer are selling these at a good price of £9.99.

Summary

This is it for the first part of this guide, I just wanted to cover some of the basic hardware pitfalls and recommendations for the Raspberry Pi. In the next part I’ll give a quick look at how to get going with Raspbian and get it up and running installed with desktop.

In the mean time here’s some useful part numbers to some reasonably priced accessories:

  • The “Xenta USB to Micro USB Cable” to power your Raspberry Pi from. Ebuyer have these down as 98p (yes pence) a go. Quickfind code: 24226
  • The “Transcend 16GB Secure Digital High Capacity Card” for storage on your Raspberry Pi. It’s a class 10 so its pretty nippy and its priced at £8 on Ebuyer. Quickfind code: 350691
  • The “Edimax Wireless-N150 Nano USB Adapter” is well supported and recommended, again Ebuyer have them for £9.99. Quickfind code: 220220

Thanks for reading

Adam.

 

 

Tagged , , , , , , , , ,