Port to PostgreSQL

[Evil Azrael]

Due to the Affection of some Developers against PgSQL, i tried this evening to port it mysql. as 4hours i got the installation and the boardindex running without errors. During this time i saw some nasty things. Some in behalf of MySQL, some in PgSQL and some in SMF.
Probably they are helpful if somebody starts seriously porting SMF to PgSQL, i am having currently no time

- column date NOT NULL default '0000-00-00'
Why this braindead initialisation? Why not simply allows NULLs. MySQL inserts NULLs even if you said NOT NULL.
- most times in the db of SMF  are ints, one or two are timestamps.
Is this intentionally so? Or are you moving from timestamps to ints? Later would be more portable, but probably you know this
- it looks like the name of the poster will be cached with the post and the thread.
will the columns changed if the user changes his name?
- no transaction support
ok, myISAM has no transaction support, but wouldn´t it be good to allow InnoDB tables and transactions?
- Postgresql changes all column names silently to lower case, if the column names aren´t quoted. that behaviour is quite nasty. (It´s more a matter of style, i´ve learned to use lower case for identifiers and upper case for reserved words.
- @mysql_connect
I dunno know what warnings this might show, but i think hiding them is a bad behaviour.
- ifnull
the function with the same behaviour is called coalesce() in PgSQL
- INSERT IGNORE
quite handy in mysql , quite nasty in PgSQL. you would have to workaround this with UPDATE and in case of no affected rows then wiht INSERT or vice versa, which depends of what you expect to be more common.
- REPLACE
same as with INSERT IGNORE
- membergroups of boards
It seems, you put all the groups a board belongs to in a string with length(80). Perhaps sufficient in practical use this is quite limiting, why not to use a separate table?
- find_in_set
relating to the last problem. find_in_set is not available in postgresql, you would have to write your own stored procedure.
- the same applies to unix_timestamp and inet_aton.


Despite the lacking of PostgreSQL support, this forum seems to be a real phpBB killer

[[Unknown]]

Perhaps the worst problem of all is that SMF expects this:
SELECT 1 = 1
> 1

But with PostgreSQL it returns this:
SELECT 1 = 1
> t

Using 0000-00-00 is not braindead .  It actually makes some queries (specifically calendar ones) simpler.

INTs are better for tracking timestamps, and they are smaller. (timestamp = 14, int = 10)  SMF has been built on the assumption that the webserver MAY OR MAY NOT be on the same timezone as the database server.

The posterName is just in case they delete their account.  In most cases, it is not used or even neccessary.  IMHO it's a waste of space, but that's just me.

Transactions are a royal pain for what little benefit they give, imho.

Lowercasing?  Then PostgreSQL sucks.  Another reason why I don't like it. (main reason is the t/f thing, which I cannot stand for programming.)

Hiding errors is because it could say "cannot connect to server, too many connections".  This is a very common message and will only scare forumgoers - instead a "nice" error message is shown.  If the error message is needed, mysql_error() can of course be used.

INSERT IGNORE and REPLACE are very different, it would be best to note.  REPLACE will delete all the rows and then insert.  INSERT IGNORE will only insert rows that do not already exist.

A separate table would cause too many joins, is less efficent, and is not neccessary for most forums/boards.  Mod, if even needed.

FIND_IN_SET may not be in PostgreSQL, but it's in MySQL and used to every advantage possible.  I don't know whether PostgreSQL supports IN either, which is also used a lot.

-[Unknown]