PHP best practice question

[BaconTastesGood]

God knows why I'm posting here, other than all the PHP forums I've found on the Web are worthless.

Anyway, most PHP applications require you to create/use a DB and then create some stock tables. This is easy to do, but I'm unclear what the best practice is for:

- handling the situation where the DB exists
- verifying the DB is of the right format
- not destroying tables if they exist
- verifying that existing tables are of the right format

I know how to do all this stuff, either via queries or just doing IF NOT EXISTS, but it ends up being a LOT of error prone code that's hard to maintain (e.g. making sure all columns exist and are of the right format in multiple tables can be a lot of code).

Anyone know of a cleaner way of handling this, short of writing a separate library/package to manage databases and tables? FWIW I'm using PEAR::MDB2.

[Fugitive]

I'm not a PHP expert, but assuming that you're thinking about versioning, it might be useful to look at what Wordpress does in its database update script. In general, it keeps a db schema version number, a set of upgrade code for each step between schema versions, and then just runs through the upgrade steps between the schema version stored in the db and the current version.

That handles differences due to application updates, and can account for things like the need to sanitize certain strings due to a bug between versions 1.2.3 and 1.2.4, but are you also concerned about people manually mucking about with the tables? Or sharing tables with other apps or using tables created externally?

[BaconTastesGood]

I'm not a PHP expert, but assuming that you're thinking about versioning, it might be useful to look at what Wordpress does in its database update script.

WP is actually one of the reasons I ask, because it (and phpBB) have fairly clean upgrade steps. The installer doesn't have to deal with a bunch of bullshit, they just run update.php or something and it does all this magic behind the scenes.

Now, that magic isn't all that magic, but it's goddamned annoying if you're the guy writing it =) That's why I was basically asking, to see if there was some generally accepted right way to do it without making it manual and tedious.

but are you also concerned about people manually mucking about with the tables? Or sharing tables with other apps or using tables created externally?

I'm mostly concerned that A.) an upgrade doesn't trash existing data and B.) a "new" installation that is running on top of an old installation doesn't trash existing data and C.) all permutations of A and B. I'd prefer that the installer not have to know how to manage a MySQL installation.

A typical use case:

- User downloads application, runs install.php, and it does everything you want it to including creating a DB and schema

- Dangerous use case: user does the above, but already has an installation, so the installer has to be careful not to trash existing tables. BUT if it just watches for existing DBs, it has to be careful that it doesn't try to load data from old, obsolete schema.

[EvilIdler]

I think the update scripts are gigantic scripts for every known case where they can mess it up.

The easiest solution I can think of (and I may have to do it for one of my projects)
is to make each table keep a version number. Then you just need to write
functions or classes that handle upgrading/installing in one go.

By keeping a version number, I mean either having a table that lists what
version of the spec the other tables have, OR having each record keep track of
what version schema they're stored in at the moment. But that sounds clunky..

[BaconTastesGood]

I think the update scripts are gigantic scripts for every known case where they can mess it up.

Yeah, that's what I'm worried about.

The easiest solution I can think of (and I may have to do it for one of my projects) is to make each table keep a version number.

Yeah, but even that is error prone (although you'd have to go to a lot of trouble to make it fail).

What I was thinking of doing was just writing a library that allows you to specify a table name and schema and then it automatically checks for validity and builds the table if necessary.

But that's a huge distraction to actually getting the app up and running, so I may just defer all this until closer to launch. Bleh.

[EvilIdler]

Are you expecting to change the datatypes of fields drastically, like going from
string to number? All changes I usually make are increased sizes of fields, and
more new fields. A simple class for each table is backwards compatible if you
do it straight forward like that. Get and set methods should check if a field exists.

This IBM article has the same idea (neat 'dynamic' class):
http://www-128.ibm.com/developerwork...s-php-flexobj/

They say PHP5, but 4.3.x and up has the basic bits for class-based programming.