[Phpwebsite-developers] 0.9.4 and Postgres

[Matthew M. <ma...@tu...>]

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!


-- 
Matthew McNaney
Internet Systems Architect
Electronic Student Services
Appalachian State University
Phone: 828-262-6493
http://phpwebsite.appstate.edu
http://ess.appstate.edu