Databases: to Postgres

vinay · 2005-02-18 20:41:55

I’ve searched and the last posts I found about this issue were from march of last year. I was wondering if any progress has been made.

one of my hosts decided that they were moving to postgres and made all of their clients move over to pgsql. i’m stuck there because I do host a bunch of other stuff there (and also I’m getting an awesome price, etc)

So I was wondering if anyone had succeeded in porting TXP to postgresql.

I would love it if they had, if not what is needed to do this?

/Vinay

jamiew · 2005-02-23 14:58:01

i also have access to a good deal with a postgres-only host and have been looking into porting txp over.

unfortunately with the massive changes being made semi-privately with the rc’s i’ve opted to wait until the upcoming 1.0 release to really do any serious coding/bugfixing. i’ll post when i do something though.

andjules · 2005-02-25 05:23:00

whatever you do, please seriously conside adodb or pear abstraction libraries (I’ve used adodb and it’s been great). That way, any work you undertake will help any prospective users of alternative databases.

if you’re not sure what i’m talking about, if you incorporate adodb -basically a set of php classes/functions – you then point your sql calls to these functions, rather than directly to your database. the advantage? edit one line and… you’ve got mysql… or postgres… or sql server… or oracle… etc.

Last edited by andjules (2005-02-25 05:38:32)

zem · 2005-02-25 05:42:06

The problem isn’t the lack of a database abstraction layer. Textpattern already has one that’s much simpler and lighter than others like the PEAR library (which is huge).

The problem is the difference in SQL capabilities and syntax. Most obviously, Postgres doesn’t have built-in support for fulltext indexing.

vinay · 2005-02-25 21:11:27

You say built-in. Which means is there something i can do to postgres to make it have fulltext indexing? Is there a compile option or what.

Let me be clear, i have a virtual server box. That means I have root access. But I share this box with about 10 of my friends. And they have several projects running on Postgres. It’s somewhat inefficient (space and cpu wise) for us to have multiple db’s running on the system. It means that we’d have to pay more for space, since we’re already in a crunch. I’d really really like to have Postgres support.

Ideas on how to implement this would be welcomed.

ubernostrum · 2005-02-25 22:29:09

Postgres comes with a module you can optionally add in to do full-text indexing; see this write-up on how to get it working, and there are other modules you can get which do it as well.

Last edited by ubernostrum (2005-02-25 22:32:36)

ubernostrum · 2005-08-07 20:04:25

Howdy, stranger.

zem · 2005-08-12 01:24:02

Actually, if you compile the contrib, whihc is distributed with the postgres source, it does.

That’s not “built-in”. Althought some hosting companies provide Postgres, I’ve yet to see one that has text indexing set up correctly.

One of the questions we ask before adding a new feature is, “is this robust enough to work in a typical (often less-than-ideal) hosting environment without generating an avalanche of support questions?”

Sadly, the answer here is no.

andjules · 2005-08-18 15:30:36

maybe the strategy is to do this via unsupported mods/add-ins, if this is really true:

The problem isn’t the lack of a database abstraction layer. Textpattern already has one

I wouldn’t want to burden the core dev team with this niche project, but it would be nice if someone could take the dive and put together a basic guide/overview to porting to other SQL databases & what features might be affected/need tweaking (which use less-standardized DB/SQL features).

I’ve got a couple of hospital clients who aren’t likely to touch anything that can’t work with Oracle or at least M$ SQL Server (“open source? how can we approve something that’s free?”), – it would be great to know if a port represents a massive headache or just a solid migraine ;-)

Last edited by andjules (2005-08-18 15:31:24)

KurtRaschke · 2005-08-18 15:46:41

> andjules wrote:

it would be great to know if a port represents a massive headache or just a solid migraine ;-)

Well, look through txplib_db.php…that represents what you’ll have to be able to handle in terms of database interaction. I suppose that the first step would be to swap mysql_query() and such for the appropriate functions for connecting to an Oracle DB or Postgres (or whatever else), but then there’s the issue of SQL compatability. The full-text search functions are going to need a rewrite for sure, and I suspect that there’s MySQL-specific SQL in other places. The table schemas will probably need tweaking for another SQL implementation.

Also, if you’re moving to an ACID-compliant DB platform (which MySQL is only with InnoDB, as I recall), then you may want to see where Textpattern could benefit from using transactions. I know there are some operations in Textpattern which involve multiple SQL statements, so maybe bundling them all in a transaction wouldn’t hurt?

Just my two cents.

-Kurt

jamie · 2005-08-23 00:35:18

PHP 5.1 includes PDO, which finally replaces all the *_query nonsense with a unified API. Widespread deployment isn’t even on the horizon but it’s still something handy to keep in mind for your local development “RC”

Any kind of DB abstraction won’t help with the SQL itself, but I’ve ported reasonable sized projects from MySQL to Postgres without too many headaches. Going beyond those two might cause headaches or performance hits. The “lowest common hosting denominator” problem becomes a pretty big issue, too.

The most creative DB abstraction I’ve seen is in DokuWiki — since it uses flatfiles as the default/primary storage medium, upgrading to a genuine database is cake. Fulltext searching remains a issue, though.

Textpattern CMS

Textpattern CMS support forum

#1 2005-02-18 20:41:55

Databases: to Postgres

#2 2005-02-23 14:58:01

Re: Databases: to Postgres

#3 2005-02-25 05:23:00

Re: Databases: to Postgres

#4 2005-02-25 05:42:06

Re: Databases: to Postgres

#5 2005-02-25 21:11:27

Re: Databases: to Postgres

#6 2005-02-25 22:29:09

Re: Databases: to Postgres

#7 2005-08-07 20:04:25

Re: Databases: to Postgres

#8 2005-08-12 01:24:02

Re: Databases: to Postgres

#9 2005-08-18 15:30:36

Re: Databases: to Postgres

#10 2005-08-18 15:46:41

Re: Databases: to Postgres

#11 2005-08-23 00:35:18

Re: Databases: to Postgres

Board footer