A few months back I posted a blog entry about the possibility that SMS 3 may be able to run on more than just MySQL. Since the first released version of SMS (1.2), Anodyne has catered exclusively to MySQL, and why not, it’s one of the most popular database system and is free. On top of that, most hosts trust MySQL because of its price tag and the fact that more people know about it than, say, PostgreSQL or SQLite. For some of these low cost hosts, it’s just absurd to think about using Oracle because the costs can be so prohibitive. Still, there are hosts out there that offer more than just MySQL. A host with Windows servers would probably off MS SQL as an option, and since PostgreSQL is free, that’s also a possibility if a host chose to go that route. Which brings us back to the question, why does SMS only support MySQL?
That question got to bugging me about a year ago, but given the SMS 2 setup, it wasn’t plausible to even attempt to address (not to mention my complete lack of understanding what would go into some type of database abstraction layer). This is another one of those great places where CodeIgniter comes in so handy, because they’ve already thought of that. Frameworks like CakePHP and symfony have full-blown ORM (object relational mapping) to make jumping between database drivers easy, but CI doesn’t have that. Instead, they’ve opted to go with something called Active Record which is based off the Active Record of Ruby fame. It’s really straightforward and makes building queries a helluva lot easier than I’ve done it in the past. For instance, in SMS 2, there might be a query that looks like:
$query = “SELECT * FROM sms_messages WHERE message_id = 1″;
$result = mysql_query($query);
That’s a very simple query and too tough to write. CI makes it a tad easier:
$query = $this->db->get_where(’sms_messages’, array(’message_id’ => 1));
return $query;
That example may not seem like it’s a lot more efficient, but as the queries get bigger and bigger, the Active Record code doesn’t get that much bigger. Take another query for example:
$query = “SELECT a,b,c FROM table WHERE var1 = ’something’ AND var2 = ’something else’ AND var3 = ’something completely different’ ORDER BY d ASC”;
$result = mysql_query($query);
Again, not too bad, but here it goes in CI:
$this->db->select(’a,b,c’);
$this->db->from(’table’);
$this->db->where(’var1′, ’something’);
$this->db->where(’var2′, ’something else’);
$this->db->where(’var3′, ’something completely different’);
$this->db->order_by(’d', ‘asc’);
$query = $this->db->get();
return $query;
First reaction: but wait, that’s more lines of code, why would I want to do that? Pretty simple answer actually: the first query only works in MySQL, the second one, works in MySQL, MySQLi, MS SQL, ODBC, Oracle, SQLite, and PostgreSQL … all by changing a single line of code. CI makes it a lot easier to build applications that work across multiple platforms.
To that end, I can say that Anodyne will officially be supporting MySQL and MySQLi for SMS 3. In addition, we’ll be providing at least experimental support for Oracle, MS SQL, and ODBC. Finally, there will be some limited support provided for PostgreSQL as well as SQLite. There’s a lot of testing that’ll have to be done with some of this stuff, but it’s really exciting to see SMS 3 be able to expand beyond just one database platform.
