[Typo3-dev] Porting to PostgreSQL (WAS: How to integrate non-MySQL ... )

[Frank Joerdens]

Hi Kasper,

it doesn't look half bad, at least much better than I'd expected after
just a couple hours of fiddling: I can import the .sql file and only get
errors on 3 insert lines with PostgreSQL complaining about an incorrect
syntax for bytea (aka blob/tinyblob). I think this is probably bogus
though (i.e. those binary data got corrupted in PostgreSQL), since with
bytea, certain characters need to be escaped:

http://www.postgresql.org/docs/7.4/interactive/datatype-binary.html

- dunno how MySQL handles this, but it certainly doesn't *look* like
there was escaped stuff in the blob/tinyblob fields.

I'd need to know which DB abstraction layer you're going to use, and
then read up on how it handles blobs/bytea - your TYPO3DBAL_020304.pdf
mentions both PEAR MDB and ADODB ... how do we go about this?

Here's what I did:
------------------

1) changed every varchar to text

- This was a bit of a knee-jerk action on my part since I don't actually
know if you're using varchar fields with different lengths to *enforce*
a restriction on the field length. If you don't, then, at least with
PostgreSQL, there's no reason - i.e. no performance penalty or gain, no
wasted storage - to not use text everywhere, which is the most flexible
type. If you tell me you want those different varchar fields, it's no
problem to change it back.

- Question: The field 'IP' in table sys_log was varchar(15) and I
changed it to text. A nifty thing about PostgreSQL ist that it supports
types such as IP address, which allows e.g. proper sorting on IP
addresses, and enforces IP addresses, i.e. you couldn't insert anything
that wouldn't look like an IP address. Is this functionality generally
wasted when using database abstraction since you always need to go for
the lowest common denominator? An argument against DBAL ... ;)

2) changed every blob/tinyblob to bytea

- As I mention above, we'd need to know what the abstraction layer does
with blobs. Hopefully they're using bytea and not lo for blobs in
PostgreSQL.

3) I had to change the names for indexes (= PostgreSQL equivalent for
MySQL Keys) to avoid a name collision - in PostgreSQL, you can't have
two indexes with the same name in the same database, even if they're for
different tables.

- Question: Do you want UNIQUE indexes, i.e. to enforce a unique
constraint on those columns, or do you just need the indexes to speed up
searches on those columns? I've been using only indexes, not unique
indexes.

4) Removed the duplicate table creation stuff in database.sql and the
DROP TABLE lines (also, IF EXISTS doesn't work in PostgreSQL).

5) comments '#' don't work (// works), removed all comments for the time
being

6) removed NOT NULL constraint for column usergroup, userMods, uc,
TSconfig, in be_users

- It was choking on the inserts for this table, since there's no
DEFAULT, and the insert SQL string didn't provide any values for those
fields. What's better, removing NOT NULL, or choosing a sensible default
for those fields. What's a sensible default for binary fields? Why do
you want NOT NULL on those fields anyway?

7) inserted DEFAULT '' in column usergroup_cached_list in table be_users


What I haven't done yet, which definitely needs to be done:
-----------------------------------------------------------

1) Increment the sequences to the last value for the inserts, since this
doesn't happen automagically. Alternatively, don't insert any value(s)
for the uid field, and let the sequence mechanism handle it. That'd mean
that the rows'd get numbered in the order in which they're being
inserted, which is not what's happening now.

Regards,

Frank

PS: Is it more appropriate to write to you directly about this? Or
should I be using the typo3-dev list?