Sign in to follow this  
  • entries
    43
  • comments
    51
  • views
    31842

So Long MySQL

Sign in to follow this  
Colin Jeanne

220 views

Thus far I've been using MySQL 4 for my CMS but it doesnt support sequences and I need them. Fortunately my host also supports Postgres SQL 7.4 which does have sequences. Yay!

I need sequences because I've made a change to my blog's database schema: an entry and a comment are now the same thing. Each has a unique ID and references the ID of a their parent. Top level entries are their own parents while comments may have a comment as a parent or an entry. I decided to combine the tables since they were almost the same table. The only difference was that the comment table originally had two extra fields: ParentID and ParentType. ParentType was an enum that would determin if ParentID referenced the entry table or the comment table. This sucked because I couldnt use ON DELETE CASCADE with that.

The reason I need a sequence is that in order to create a parent that references itself I need to get its ID before I insert it into the table which means I can use MySQL's ability to get the last value from AUTO_INCREMENT because it would be worthless.

Hopefully this wont be a hard change. I've abstracted the database enough that I should only have to change the class and then possibly a couple of my queries. The only ones I'm worried about are my paging queries because they use LIMIT. Of course I'll have to change my table to no longer use AUTO_INCREMENT as well.
Sign in to follow this  


3 Comments


Recommended Comments

Why don't you just allow the field to be NULL?
If it's NULL, then its obviously a top level post. Thats how I handled it in the web based blog thing I wrote.

Share this comment


Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now