Re: [Phpwebsite-developers] 0.9.4 and Postgres

[Jim W. <ji...@ke...>]

Matthew McNaney said:

> Greetings.
> 
> As some of you may know, I have been programming 0.9.4 under Postgres.
> Not because I like it better than MySQL (I don't) but because I want the
> next version to be more compatible. So far 0.9.4 works under Postgres
> swimmingly.
> 
> However I have hit a snag.
> Currently I am working on backward compatibility. My test module is
> announcements.
> Herein lies the problem. Announcement uses variable case column names in
> its tables. For example, in mod_announce we have columns named
> userCreated, userUpdated, dateCreated, etc.
> 
> When those columns are sent to postgres, they are changed to
> usercreated, userupdated, and datecreated. As you can see, postgres
> removes capitalization. This is a problem because when announce queries
> the database, it expects them to contain capitalization. For example,
> when pulling the settings, announce tries to get the result in the
> 'showCurrent' column. But the array returned by select only has a
> 'showcurrent' column. So we get a big ole error.
> 
> Now there is a solution to this problem. I wrote a parser that finds the
> column names in a 'create table' query. Then I run a function from PEAR
> named quoteIdentifier. This function checks to see which db is running
> and applies the correct delimiter. For postgres, it is quotes. Mysql:
> backtick. When the delimiter is applied, the capitalization is retained.
> So far so good.
> 
> But now there are more problems. Every function that accesses these
> tables then needs its columns delimited as well. Every select, update,
> alter, and insert must be delimited or capitalization will be lost and
> the query will fail. So I would have to update every function to do so.
> 
> Is there a simpler solution? Yes. You must make sure that you never use
> capitalization in your column names. If you do that, then everything is
> fine. Both MySQL and Postgres will work fine as long as the column names
> are all lowercase. I am guessing the other databases will behave as
> well.
> 
> But what of 0.9.3 compatibility? Currently, I believe announcements will
> work under MySQL (I haven't tested it yet) because MySQL doesn't force
> lower case letters on column names. But announcements will not work in
> Postgres unless I make the broad changes above.
> 
> So here is what we must decide. Do we delimit column names or not?
> 
> Here is what I think:
> 
> We don't delimit. Instead we tell developers that although 0.9.4 is much
> more friendly to postgres, you have to keep column names lowercase.
> 
> Modules written for 0.9.3 will still work in 0.9.4 under MySQL.
> Developers who want their pre-0.9.4 module to work in 0.9.4 will have to
> lowercase their sql columns AND adjust their code to recognize the
> changed case.
> 
> I feel that delimiting on everything will add an extra layer of work on
> a rather simple process. However, I would like to hear other opinions.
> 
> Thanks,
> Matt
> 
> 
> If you read down to this line you should be able to assemble the clues
> in the email to find the buried treasure! Good luck!
> 

I'm runing 0.9.3 with Postgres and that is what I ended up doing.  My
modifications include converting camel case to words separated by underscore
(e.g. userCreated because user_created).

The other thing that I found was an issue was the handling of sequence based
primary keys.

Because Postgres (and other databases) have unique ways of retrieving index
information on a table, and they are capable of having multi-column primary
keys, this part of the way phpwebsite works must be changed.  

The function in question is called: PHPWS_Database::sqlGetIndex

It seems that phpwebsite needs to do one of two things:

1. Register the names of sequence based primary keys somehow (in some sort of
phpwebsite metatable for example.  Then sqlGetIndex would simply do a lookup
in that metatable.

OR

2. Use a standard name for these types of primary keys (e.g. seqkey or idkey)
for all applications and their tables.  Then sqlGetIndex would not be required.

This and the column naming issue you mentioned before are 99%+ of all that is
necessary to get postgres working.  Note that a couple other things like some
of the aggregate function calls caused problems until they were aliased (e.g.
MAX(date) AS max_date).  Aliasing those is good practice anyway.

Best regards,

Jim Wilson