NAME
DBIx::ModelUpdate - tool for check/update database schema
SYNOPSIS
use DBIx::ModelUpdate;
### Initialize
my $dbh = DBI -> connect ($connection_string, $user, $password);
my $update = DBIx::ModelUpdate -> new ($dbh);
### Ensure that there exists the users table with the admin record
$update -> assert (
tables => {
users => {
columns => {
id => {
TYPE_NAME => 'int',
_EXTRA => 'auto_increment',
_PK => 1,
},
name => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 50,
COLUMN_DEF => 'New user',
NULLABLE => 0,
},
password => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 255,
},
},
data => [
{id => 1, name => 'admin', password => 'bAckd00r'},
],
},
},
);
### Querying the structure
my $schema = $update -> get_tables;
my $users_columns = $update -> get_columns ('users');
ABSTRACT
This module let your application ensure the necessary database structure without much worrying about its current state.DESCRIPTION
When maintaining mod_perl Web applications, I often find myself in a little trouble. Suppose there exist: - a production server with an old version of my application and lots of actual data in its database; - a development server with a brand new version of Perl modules and a few outdated info in its database.
Now I want to upgrade my application so that it will work properly with actual data. In most simple cases all I need is to issue some Ñ<CREATE TABLE/ALTER TABLE> statements in SQL console. In some more complicated cases I write (by hand) a simple SQL script and then run it. Some tool like mysqldiff may help me.
Consider the situation when there are some different Web applications with independent databases sharing some common modules that use DBI and explicitly rely on the database(s) structure. All of these are installed on different servers. What shoud I do after introducing some new features in this common modules? The standard way is to dump the structure of each database, write and test a special SQL script, then run it on the appropriate DB server and then update the code. But I prefer to let my application do it for me.
When starting, my application must ensure that: - there are such and such tables in my base (there can be much others, no matter); - a given table contain such and such columns (it can be a bit larger thugh, it's ok); - dictionnary tables are filled properly.
If eveything is OK the application starts immediately, otherwise it slightly alters the schema and then runs as usual.
ONE TABLE
For example, if I need a users table with standard id, name and password columns in it, I write
$update -> assert (
tables => {
users => {
columns => {
id => {
TYPE_NAME => 'int',
_EXTRA => 'auto_increment',
_PK => 1,
},
name => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 50,
COLUMN_DEF => 'New user',
},
password => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 255,
},
},
},
},
); MANY TABLES
Consider a bit more complex schema consisting of two related tables: users and sex:
$update -> assert (
tables => {
users => {
columns => {
id => {
TYPE_NAME => 'int',
_EXTRA => 'auto_increment',
_PK => 1,
},
name => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 50,
COLUMN_DEF => 'New user',
},
password => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 255,
},
id_sex => {
TYPE_NAME => 'int',
},
},
},
sex => {
columns => {
id => {
TYPE_NAME => 'int',
_EXTRA => 'auto_increment',
_PK => 1,
},
name => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 1,
},
},
},
},
);
MANY TABLES WITH SIMLAR COLUMNS
It's very clear that each entity table in my schema has the same id field, so I will declare it only once:
$update -> assert (
default_columns => {
id => {
TYPE_NAME => 'int',
_EXTRA => 'auto_increment',
_PK => 1,
},
},
tables => {
users => {
columns => {
name => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 50,
COLUMN_DEF => 'New user',
},
password => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 255,
},
id_sex => {
TYPE_NAME => 'int',
},
},
},
sex => {
columns => {
name => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 1,
},
},
},
},
); INDEXING
The next example shows how to index your tables:
$update -> assert (
default_columns => {
id => {
TYPE_NAME => 'int',
_EXTRA => 'auto_increment',
_PK => 1,
},
},
tables => {
users => {
columns => {
name => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 50,
COLUMN_DEF => 'New user',
},
password => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 255,
},
id_sex => {
TYPE_NAME => 'int',
},
},
keys => {
fk_id_sex => 'id_sex'
}
},
sex => {
columns => {
name => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 1,
},
},
},
},
); DICTIONNARY DATA
Finally, I want ensure that each sex is enumerated and named properly:
$update -> assert (
default_columns => {
id => {
TYPE_NAME => 'int',
_EXTRA => 'auto_increment',
_PK => 1,
},
},
tables => {
users => {
columns => {
name => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 50,
COLUMN_DEF => 'New user',
},
password => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 255,
},
id_sex => {
TYPE_NAME => 'int',
},
},
},
sex => {
columns => {
name => {
TYPE_NAME => 'varchar',
COLUMN_SIZE => 1,
},
},
data => [
{id => 1, name => 'M'},
{id => 2, name => 'F'},
]
},
},
); That's all. Now if I want to get back the structure of my database I write
my $schema = $update -> get_tables;or
my $users_columns = $update -> get_columns ('users'); for single table structure.
COMPATIBILITY
As of this version, only MySQL >= 3.23.xx is supported. It's quite easy to clone DBIx::ModelUpdate::mysql and adopt it for your favorite DBMS. Volunteers are welcome.
SECURITY ISSUES
It will be good idea to create DBIx::ModelUpdate with another $dbh than the rest of your application. DBIx::ModelUpdate requires administrative privileges while regular user souldn't.
And, of course, consider another admin password than bAckd00r :-)
SEE ALSO
mysqldiff
AUTHOR
D. E. Ovsyanko, <do@zanas.ru>
COPYRIGHT AND LICENSE
Copyright 2003 by D. E. Ovsyanko
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
1 POD Error
The following errors were encountered while parsing the POD:
- Around line 270:
Non-ASCII character seen before =encoding in 'Ñ<CREATE'. Assuming CP1252