Jump to content
  • Advertisement
Sign in to follow this  
  • entries
  • comments
  • views

So Long MySQL

Sign in to follow this  
Colin Jeanne


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  


Recommended Comments

I was looking into triggers in the Postgres docs and it looks like those might be useful in the future as well.


Share this comment

Link to comment
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
  • Advertisement

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

GameDev.net is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!