Bleeding Edge innovation: automatically initialize databases on installation

(28)

Do you develop a website? It is infinitely better to synchronize live and development sites using the PEAR Installer

(25)

doing the PEAR thing

(19)

Using PEAR 1.4.0 to install PEAR packages on a remote host

(19)

PEAR now fits in a bottle: meet go-pear.phar

(17)

Syndicate This Blog

RSS 0.91 feed

RSS 1.0 feed

RSS 2.0 feed

ATOM 0.3 feed

ATOM 1.0 feed

RSS 2.0 Comments

Powered by

Serendipity PHP Weblog

Saturday, October 29. 2005

Bleeding Edge innovation: automatically initialize databases on installation

A few minutes ago, I released a proof-of-concept custom install task/custom install role pair of packages at pear.chiaraquartet.net.

PEAR_Task_Chiara_Managedb and PEAR_Installer_Role_Chiaramdb2schema work together along with a post-install script in the PEAR_Task_Chiara_Managedb package to automatically initialize and upgrade the database structure of applications.

The end-user is responsible for setting the DSN that should be used to access the database via the post-installation script (installation will simply fail with an error message if this has not occurred).

To use, your package should contain a <usesrole>/<usestask> combo like this following:

<usesrole>
<role>chiaramdb2schema</role>
<package>PEAR_Installer_Role_Chiaramdb2schema</package>
<channel>pear.chiaraquartet.net</channel>
</usesrole>
<usestask>
<task>chiara_managedb</task>
<package>PEAR_Task_Chiara_Managedb</package>
<channel>pear.chiaraquartet.net</channel>
</usestask>

Then, in the contents section, an MDB2_Schema-based schema file defining database structure should be entered like so:

The rest of the work is performed by the excellent MDB2_Schema package, which has the ability to examine two schema files, generate a diff, and literally upgrade the database structure. Because of this strength, it is no longer necessary to provide special sql files differentiating each release (although this is not a bad idea for those who like to do manual configuration). Note that as a proof-of-concept, this code is alpha stability. This means that those using it will be testing out the solution for the purpose of experimenting with seriously bleeding-edge technology. All the same, the source code for such a complex task was incredibly easy - and simple - to write, showing just how much is actually possible with PEAR 1.4.3 and newer.

These packages both require PEAR 1.4.3 and newer to function properly, due to bugfixes. PEAR 1.4.3 is not released yet, and is undergoing final release preparations at the moment, but will be out very soon.

Enjoy!

Posted by Greg Beaver in PEAR at 23:46 | Comments (9) | Trackbacks (0)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

Damn, that idea ran around in my head for months now, but I was to busy to implement it. Nice work!

#1 Toby (Homepage) on 2005-10-30 02:35 (Reply)

Well you can get your .sql files, you can make MDB2_Schema skip doing the actual query but still generate the SQL by using the disable_query option, which in turns allows you to write the output into a .sql file, that way your users will have both options.

#2 Helgi �ormar on 2005-10-30 03:31 (Reply)

Nice work. I love to see progress on PHP deployment.

However, I'm not sure about upgrading via diff. What would happen, for example, if you rename a column. Would the upgrade drop the old column and add a new one, losing any values that might have been in that field?

#3 Jeff Moore (Homepage) on 2005-10-30 11:00 (Reply)

Hi Jeff,

If you rename a column, it would indeed be dropped.

A post-installation script would be the perfect place to implement just such a change.

tasks in PEAR are designed only for relatively simple transformations - even this task is a bit of a stretch to the design of tasks.

However, if used judiciously, it will function wonderfully.

In terms of renaming a column, that usually implies a radical shift in design, and automating changes of this nature is almost always a bad idea

#3.1 Greg Beaver (Homepage) on 2005-10-30 11:11 (Reply)

While it doesn't do diffs, DB_Table does create the table for you on first run, and without any XML (the schema is stored in the object as a PHP array, which has other benefits such as auto-validation and fast form generation).

http://pear.php.net/DB_Table

#4 Paul M. Jones (Homepage) on 2005-10-30 12:22 (Reply)

Jeff:
In MDB2_Schema then you use the "was" tag when you are updating field/table names if you don't want to lose the values.

Paul:
Well DB_Table does do anything remotely like MDB2_Schema, they are kinda on different ends what they do in regards to databases

#5 Helgi �ormar on 2005-10-30 16:52 (Reply)

I stand corrected: this is far more powerful than I realized it was

Perhaps Lukas could elucidate the full range of possibility...

Greg

#5.1 Greg Beaver (Homepage) on 2005-10-30 17:07 (Reply)

Here is the old documentation Manuel wrote:
http://www.backendmedia.de/MDB2/docs/xml_schema_documentation.html

Its missing some of the new features added like the "primary", "autoincrement", "overwrite", and "length" tags in index fields ..

#5.1.1 Lukas on 2005-10-31 00:26 (Reply)

Do you think there would be interest in a PEAR package purely for creating/updating DB structures? I looked for this recently and was suprised not to see anything.

I did roll my own but took the naive approach of depending on mysql's "SHOW CREATE TABLE foo" functionality. I've got a class that stores the sql in a serialized array and has methods to check for the existence of a table, create it if it doesn't exist, and update it if it isn't the same structure as the stored version.

Comparing stored to existing is done by comparing the generated CREATE TABLE statement to the stored CREATE TABLE. Updating is done by renaming foo to foo_$rand, creating foo, and inserting into foo from foo_$rand...

All that was my second try after manually storing fields, indexes, keys, etc got way too complicated. The naive approach doesn't handle renaming of columns either... But it's letting me generate tables and keep them updated as I update/install modules in my cms...

It seems like this would be a common problem and having a DB_Structure class that provided this sort of functionality with drivers for different flavors of DB's would be a helpful addition to PEAR. What do you think?

I am going to have to look into MDB2_Schema though...

#6 metapundit (Homepage) on 2006-01-13 15:36 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	Standard emoticons like :-) and ;-) are converted to images. E-Mail addresses will not be displayed and will only be used for E-Mail notifications To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above: BBCode format allowed You can use [geshi lang=lang_name [,ln={y\|n}]][/lang] tags to embed source code snippets
	Remember Information? Subscribe to this entry
Submitted comments will be subject to moderation before being displayed.