Textpattern on Postgres

AlanChandler · 2005-05-01 17:37:39

I have been trying to get Textpattern to work with Postgres. I reached the first milestone this afternoon, in that I have

Got past the “setup stage” and populated a database with the right tables (during setup you are asked which type of database you want to create. It still defaults to mySQL but you can select on a radio button that the database will be Postgres).

Got past the “_update.php” stage. In other words I can log in to the new database and get up the page that lists all the sites parameters.

I have not tested it further.

However – now might be a good time to tell everybody and solicit help with taking it further.

Have set up an svn repository that has the latest snapshot of where I am (and which is also syncing with the formal textpattern svn repository). I can do this by using svk, which is a more distributed version of svn, but based on the standard svn repository.

Full details are on my web site at http://www.chandlerfamily.org.uk/archive/17/textpattern-on-postgres

soulship · 2005-05-05 02:42:37

This sounds very interesting. Hopefully someone will jump in here and give you a hand. I am not a coder, but I will keep track of your project for sure.

AlanChandler · 2005-05-05 05:52:30

I’ve made a little more progress now, and put the resultant stuff in the svn repository on my site.

The database is already MAINLY abstracted into lib/txplib_db.php so I have had to concentrate in this file for most things (not really looked at plugins yet – there may be a serious problem here which may mean a rethink – I suspect this will only work if plugins are postgres aware). I think the import stuff also needs careful looking at (since the source databases are almost certainly mysql). However one or two other problems

1) There is occassional use of mysql direct calls elsewhere. I am dealing with them as best I can when they arise. The most difficult is the use of “mysql_insert_id()” which can only be matched in postgres by doing a “pg_query(‘select curval(table_key_seq)’)” statement. [table_key_seq is a name chosen to match the table name, primary key column name and “seq” for each case, so can’t just be hived off into a generic routine]

2) Timestamps are completely different between the two databases. I have therefore decicided to switch over to an integer and use the php routines to store the unix time in this integer. This is the bit that will take some time and I am working through at the moment.

3) Case sensitivity and how to deal with it on column names. This difference between the two databases wrecks the abstraction in txplib_db.php, so I have decided to make all column names be totally lowercase. Unfortunately this has serious knock on effects throughout the code, where the column names are often “extracted()” into case sensitive php variable names. These then have to be “fixed up” to be lower case. Again I am slowly working through the code looking for more occurrances.

So again if anyone wants to help me feel free to drop me an e-mail.

Last edited by AlanChandler (2005-05-05 05:55:39)

AlanChandler · 2005-05-10 23:45:14

Just wanted to say, that at short notice I have to travel to Australia for a month on business. As a result all work on moving this forward has come to a complete halt. I have left my repository running, so anyone wishing to help or whatever can do so.

marios · 2005-05-12 10:22:15

I have been reading this topic from the beginning , allthough I do not have the technical knolledge to comment about it,I found this to be very interesting.(Don’t understand why there is no much activity on this topic)
Just in case it might have been missed by some, there allso are serious thoughts about porting TP to SQLite.
Details here in this topic
It would allso be interesting to learn a little bit more about the advantages and disadvantages for such endeavors in comparison to mySQL

regards, marios

neptho · 2006-02-06 06:24:07

I know I’m a bit late to this game, but if you don’t mind moving to a DB abstraction layer, I wrote a primitive (read: pitiful) one that wraps MySQL/Postgres and comparable SQLite calls (I have not updated it for mysqli). If you’re interested, drop me a note, and I’ll ensure you get the file (I tore down everything when I went “live” with Textpattern earlier today.)

AlanChandler · 2006-02-06 21:15:02

Having been alerted to the continuing existance of this post but the lastest posting from alpha, I just wanted to say that a tarball of the work I have done to date is at http://www.chandlerfamily.org.uk/files/txpg/txpg-v0.1.tar.gz

I am trying to also put up a git repository of the work – but its not working at the moment.

Unfortunately I am no longer working on it for reasons that will become clear if you visit my site.

Textpattern CMS

Textpattern CMS support forum

#1 2005-05-01 17:37:39

Textpattern on Postgres

#2 2005-05-05 02:42:37

Re: Textpattern on Postgres

#3 2005-05-05 05:52:30

Re: Textpattern on Postgres

#4 2005-05-10 23:45:14

Re: Textpattern on Postgres

#5 2005-05-12 10:22:15

Re: Textpattern on Postgres

#6 2006-02-06 06:24:07

Re: Textpattern on Postgres

#7 2006-02-06 21:15:02

Re: Textpattern on Postgres

Board footer