DB schema changes/tweaks

[petree2]

I just did an install of serendipity and I had a few questions/comments/bugs.

1) The username field in the serendipity should have unique constraint. I understand that the authorid file is the pkey, but wierd things happen when you have two authors with the same username. This happened because I upgraded from nucleus and it reimported my username from there. (If your curious, most of the admin options (Import being one of them) break in some fashion). Yes, both users have the same userlevel.

2) in the comments table, the field subscribed is not null, but has no default value. When I did my import from nucleus (actually blogcms) the import of every comment failed because the not_null constraint was violated. As a sidenote, I can't think of anything less fun than having the import script spit back at me 60 lines of error messages including the sql statement and the php array, for each and every comment that wouldn't import. That's not a very useful error message. For a few it's ok, but for 1700...firefox just hung for a long ass time with it.

3) Is there a particular reason that tables in postgresql are using OIDs?

4) There should be a note somewhere about long comments being an issue on imports (or in general). Although it only happened to me with two comments, it gave me an error message that the row was too big for the btree index.

5) Is there any reason y'all don't use foreign keys at all? Strikes me as kind of missing out on one of the major benefits of a good sql database....referrential integrity.

Other than that, I'm really excited about moving to this from nucleus. Oh, y'all might want to change the text in the import drop down to allow people to select <a href="http://blogcms.com/">blog:cms</a> as well...it's based around nucleus, so the import worked flawlessly. Oh and maybe include a note in the docs too.

[garvinhicking]

Hi!

1) You are right about that. Right now for the 0.8 release process we will not be able to change the DB keys/indices so easily, so we will include this for our 0.9 release

2.) See first thing - I did not notice this bug, as I'm using MySQL when developing the importers. We will change this key for 0.9, and for now I just committed to the 0.8 branch a fix that will set the 'subscribed' option.

The errors you were getting is just because of the verbosity of postgreSQL and because of using a Serendipity beta-Snapshot. Verbosity is tuned down for final releases (automatically).

3.) Yes, there is. To get the last insert ID of an auto increment column. But since a few weeks we are using the pgsql sequences instead.

4.) This is a pgsql only problem, so basically it just shouldn't a btree index (whatever that is). Do you know a solution what needs to be done? For MySQL and SQLite, the long comments would all import properly...would we just need to remove the index for pgsql?

5.) Yes, there is. MySQL 4 does not natively support foreign keys, and neither does SQLite. And since we try to keep our SQL as unified as possible, we would dislike adding all kind of native SQL mojo in there.

Thanks a lot for your productive feedback. About the blog:cms, I would need to try it out first before adding it "officially", and it seems I currently don't have the time for it. But I put it on my todo list.

Regards,
Garvin

[petree2]

1) Ok, thanks.

2) Hehe...actually I decided to use PostgreSQL because I figured there weren't too many people testing it.

3) Excellent. I was going to ask that question about the use of sequences too, but couldn't come up with good reasoning why to use sequences, so I didn't mention it.

4) Yeah, I'm trying to figure out what the proper use is. I don't know postgresql indexes well enough. I'll get back to you on that one.

5) Common misconception. "Starting from MySQL 3.23.44, InnoDB features foreign key constraints.". That's the thing, you can't do it with MyISAM tables, only with InnoDB. You could use foreign keys if you required people to use InnoDB tables...I'm not sure when foreign keys got mature, but since MySQL 3.23.44 was released November 2, 2001, I would imagine it's gotten stable in the past 3 or so years. Stable enough that "InnoDB is included in binary distributions by default as of MySQL 4.0.".

I understand if you don't want to do that, but for real...once you start using foreign keys, your life can get much easier, but it will stop people from using SQL lite. BTW, is anyone using SQL lite? I've never met anyone who has used it for anything other than single user apps.


Oh and as far as Blog:CMS goes, just so you know, I got it to work with BLOG:CMS v3.5.2.

[Tys]

3) Sequences are great, and there is no reason not to use them. Plus they are guarenteed to be unique where OID's aren't, however slim the chance of looping an OID (and take up extra space). I would avoid using OID's wherever possible.

5) MyISAM tables will allow for foreign key syntax in its table definitions, but not enforce them.

I see no issue with implementing foreign keys on the databases that support it. It adds extra data integrity for those that want it and doesn't really effect those that don't. If you're SQL queries run properly there shouldn't be any hanging refrences anyways. Implementing them (which is dead simple) might even catch some data integrity bugs in the SQL which could be fixed for all databases (foreign key support or not).