CouchDB has infected me and I’ve been reading a lot about alternative ways to store data AND organize it. In the midst of options for alternatives to relational databases, these two stand out:

Cassandra – “Cassandra is a highly scalable, eventually consistent, distributed, structured key-value store. Cassandra brings together the distributed systems technologies from Dynamo and the data model from Google’s BigTable. Like Dynamo, Cassandra is eventually consistent. Like BigTable, Cassandra provides a ColumnFamily-based data model richer than typical key/value systems.”

The huge appeal of Cassandra seems to be the approach to make it highly fault-tolerant. Writes never fail. Data is always available. No single point of failure. If you’re making a Twitter-like app, you should consider it.

Tokyo Cabinet – “Tokyo Cabinet uses hash algorithm to retrieve records. If a bucket array has sufficient number of elements, the time complexity of retrieval is “O(1)”. That is, time required for retrieving a record is constant, regardless of the scale of a database. It is also the same about storing and deleting. Collision of hash values is managed by separate chaining. Data structure of the chains is binary search tree. Even if a bucket array has unusually scarce elements, the time complexity of retrieval is “O(log n)”.”

Tokyo Cabinet is slightly newer and is apparently stupidly fast, faster than any other storage solutions out there (at least for now). It’s written in C and provided as API of C, Perl, Ruby, Java, and Lua.

Do you know anyone who has used these two already? Care to share your experiences?

Yesterday I gave a talk at the php|tek 2009 Conference about the ORM in the PHP World. In the first part of the presentation, I’m focusing on what an ORM is, what would make a great ORM, design patters for ORM and tying ORM systems to the PHP world in terms of philosophy, uses and approaches. The second part of the presentation talks about a list of ORMs that I have seen and their pros and cons.

The ORMs I mention:

• Propel
• Doctrine
• dOrm
• Zend Framework – specifically the proposed Zend_Db_Mapper
• Xyster Framework
• CodeIgniter
• ez components’s Persistent Object and Persistent Mapping
• symfony
• PHPLinq
• Solar’s Solar_Sql_Model
• Outlet PHP ORM
• CoughPHP
• ORMer

I plan on talking about each of these ORMs in detail in separate blog posts, so stay tuned!

While working on my presentation about ORMs in PHP for the upcoming php|tek conference in May, I’m finding it slightly challenging to pick a good example of data represented in the application and in the database, so that I could insert various ORMs in between these two ends.

The example should be flexible enough to fit within different ORMs. Think of a scale from ActiveRecord to something really complicated like Java’s Hibernate – that’s the scale for the ORMs. It also needs to be really intuitive, so that the audience of the talk can understand it without much explanation – I want to focus on the ORMs themselves, not something else.

At this point, I’m leaning toward an example representing a user on a social network site and his friends. Something along these lines (don’t beat me up, it’s a very rough draft):

object model:

database model:

I think it’s a good idea to keep this example specific to functionality common in web applications (the alternative is kittens, really). On the other hand, I don’t want the example to be overly complex, so that the audience focuses more on the topic of the presentation rather than the details of the example.

When you come to presentations, what do you look for in examples? Are you looking for something you can basically copy and paste and use in your code? Or are you more interested in high-level theory where examples take a back seat and are there just for flavor but not really to explain?

People borrow my database books and a bunch of them are in circulation at any given time. Also, a lot of database resources are online only (e.g. for Oracle and MySQL). Right now, this is the state of my database bookshelf:

l2db

Not bad! You can probably get through a lot with just these three…

Being an Oracle fan, I often find myself on the defense trying to explain its awesomeness While it’s uber expensive, obviously it has desirable features that sometimes make it worth it. On the other hand, in my experience, Oracle takes a much stricter, “comp-sci” (quotes on purpose) approach while MySQL can be wonderful in forgiving developers and expecting that developers are more concerned with getting stuff done rather than being nitpicky (which is true most of the time, really).

Allow me to elaborate. There are a few features that are available to MySQL developers that are a bit harder to find if you’re using Oracle. Here are a couple:

This one’s kind of a cliche and if I had $1 for every time I heard it, I’d have maybe $20 by now!

“Oracle doesn’t have SHOW CREATE TABLE!”

So let’s level the playing field a little bit. First off, MySQL doesn’t offer “show create table” either. When you see:

mysql>

you’re actually running a MySQL client that provides this functionality. With Oracle, the somewhat default client is sqlplus – it offers access, but it’s not very fancy. You can use gqlplus (built by the open source community!) for things like command line history (aka “arrow up to previous query”), or go full GUI experience with SQL Developer (which I personally recommend, it’s pretty sweet). The latter offers functionality that will do the same thing you expect “show create table” to do when using MySQL client.

More importantly, Oracle offers an entire set of built-in tables that provide data about your database schema. Next time you have access to Oracle, try this:

DESCRIBE user_tables;
DESCRIBE user_tab_columns;
DESCRIBE user_constraints;
DESCRIBE all_source;

Oracle offers you hundreds of ways to find out information about your schema. My hunch is that the reason “show create table” does not come in a default client is because of the amount of possibilities this means. Compare the CREATE TABLE statement available in Oracle and in MySQL. There is simply so much that “show create table” could show you in Oracle, making choices what to omit and deciding what is default perhaps is better left to developers than to a shell script. (It is also my belief that your DDL – how your tables are created – should be stored as code under version control, so there’s that.)

Another “Zomg, MySQL is so much better than Oracle!” claim I witnessed recently is:

“Oracle doesn’t have INSERT IGNORE!”

The response is, yes, of course it does, it’s just not called “insert ignore”. “Insert ignore” is MySQL’s feature that veers away from standard SQL as much as anything else.

So what’s the deal here? I find that the best way to find equivalent features between MySQL and Oracle is to take a step back and think in more general, basic language concepts. What “insert ignore” really does is:

1. attempt to insert
2. if failed due to a constraint violation, continue

This looks to me to be simple exception handling! Oracle comes with PL/SQL, an entire language of its own. Surely it has a way to handle exceptions! Here are a couple of ways to do “insert ignore” in oracle:

begin
    insert into some_table(field) values ('zomg');
exception
    when DUP_VAL_ON_INDEX then
        return;
end;
/

Not only does Oracle allow you to do “insert ignore”, it provides you with fine grained control about exceptions – in this case, I am catching the case when a unique key (such as the primary key) is violated. In his blog post, Hampus recommends a similar approach and also points out that you can use Oracle’s MERGE statement (which, incidentally, is on of my favorite statements!) for this purpose.

I guess the purpose of this post is to once again point out the inconsistency in how different databases implement standard SQL (I talked about this a little bit in a post about The Meaning of Keys). When you see something that Oracle has and MySQL and vice versa is sometimes tied to this different approach. I’m willing to concede that MySQL has some awesomeness, such as the kick-ass GROUP_CONCAT function. But if you still feel like QQ’ing at Oracle (e.g. in comments on this blog post), fair warning, I will continue to defend it – I only ask that you QQ constructively!