DBIX::Class:: DeploymentHandler (aka DBICDH)
Arthur Axel "fRIOUX" Schmidt
What is DBICDH?
DBIx::Class::DeploymentHandler - DBIx::Class::Schema
deployment for multiple versions.
- w/o multiple versions ->deploy is certainly easier
Why not Schema::Versioned?
- Not a component on Schema ∴ DBIx::Class::Journal
- Optional Component for version warnings
- Run more than one SQL script on upgrades
- Run Perl scripts
- Much more extensible
- A clean break from backcompat
YUCK.
sub _set_db_version {
my $self = shift;
my ($params) = @_;
$params ||= {};
my $version = $params->{version} ? $params->{version} : $self->schema_version;
my $vtable = $self->{vschema}->resultset('Table');
##############################################################################
# !!! NOTE !!!
##############################################################################
#
# The travesty below replaces the old nice timestamp format of %Y-%m-%d %H:%M:%S
# This is necessary since there are legitimate cases when upgrades can happen
# back to back within the same second. This breaks things since we relay on the
# ability to sort by the 'installed' value. The logical choice of an autoinc
# is not possible, as it will break multiple legacy installations. Also it is
# not possible to format the string sanely, as the column is a varchar(20).
# The 'v' character is added to the front of the string, so that any version
# formatted by this new function will sort _after_ any existing 200... strings.
my @tm = gettimeofday();
my @dt = gmtime ($tm[0]);
my $o = $vtable->create({
version => $version,
installed => sprintf("v%04d%02d%02d_%02d%02d%02d.%03.0f",
$dt[5] + 1900,
$dt[4] + 1,
$dt[3],
$dt[2],
$dt[1],
$dt[0],
$tm[1] / 1000, # convert to millisecs, format as up/down rounded int above
),
});
}
DBICDH is a Transformer
- DBICDH is built from 3 smaller robots
- VersionHandler
- VersionStorage
- DeployMethod
VersionHandler
- See doc
- Basically an iterator of "versions" from current version to desired version
- Distinguishes between up and down to error check if you accidentally downgrade
VersionStorage
- See doc
- Where the current (and previous) versions of the database get (surprise!) stored
- Bundled storages are just tables in the database
- This is the easiest and most sensible to override
DeployMethod
- See doc
- Generates DDL
- Runs SQL
- and more
Introduction to the defaults
- lots of options
- defaults/recommended
There are already a lot of different options for building your own
DBICDH, we'll talk about the defaults (aka recommended) here.
Monotonic Version Handler
- integers only
- easily move from one version to the next
The monotonic version handler uses integer only versions, which
follow naturally per revision or release or whatever. So you first
version would be 1, the second would be 2, etc.
The main benefit of monotonic is that it is perfectly simple
and predictable. No issues of how versions are parsed or ordered
or any of that.
SQL::Translator DM
- SQLT for DDL generation
- Runs scripts
The SQLT deploy method uses SQLT to generate SQL for installs,
upgrades, and downgrades. In addition to that it does the actual
running of SQL and Perl scripts and defines the directory layout
for the scripts.
SQLTDM - Perl Scripts
- similar to .psgi - anonymous coderef
- First arg is the schema (except for initialize and _preprocess_schema)
A Perl script used by SQLTDM is actually very similar to a .psgi
script. It should just be an anonymous subref. Scripts for install,
upgrade, and downgrade get the schema as their first argument.
The initialize scripts are not passed any arguments.
SQLTDM - Serialized Schema
- Serialized SQLT objects to represent the schema for any database
SQLTDM - Directory Layout
Note: this is inspired by DBIx::Migration::Directories
$sql_migration_dir
|- _source
| `- deploy
| `- 1
| `- 001-auto.yml
|- SQLite
| |- downgrade
| | `- 2-1
| | `- 001-auto.sql
| |- initialize
| | `- 1
| | |- 001-create_database.pl
| | `- 002-create_users_and_permissions.pl
| |- deploy
| | `- 1
| | `- 001-auto.sql
| `- upgrade
| |- 1-2
| | `- 001-auto.sql
| `- 2-3
| `- 001-auto.sql
|- _common
|- downgrade
| `- 2-1
| `- 002-remove-customers.pl
`- upgrade
`- 1-2
`- 002-generate-customers.pl
Standard Version Storage
- autoinc instead of ridiculo-date
Not a whole lot worth saying here. The main change from
::Versioned is that instead of that crazy date thing I pointed out
above it uses a simple autoincrementing id.
General Usage
- The stages of DBICDH
- How to use them
initialize
- Optional, but recommended by the frewmbot
- CREATE DATABASE
- only Perl
- Could we even have a dbh?
The initialize stage, while not required, should vastly reduce the
amount of manual intervention required for a deploy. Typical usage
will probably be something like having it do CREATE DATABASE FOO
as well as adding a few users to the database and whatnot.
Because initialize is such an open-ended stage, it currently
only supports running perl scripts, instead of perl and sql like
the rest of the stages.
initialize example
# $migrations/MySQL/initialize/1/001_create_db.pl
sub {
use DBI;
my $user = 'root';
my $password = 'foo';
my $dsn = 'bar';
# you should probably
# prompt the user for
# at least the password
my $dbh = DBI->connect($dsn, $user, $password);
$dbh->do('CREATE DATABASE frewmbot')
or die 'Shucks howdy! FAIL:' . $@;
};
install
- run prepare_install first
- schema deploy
- version storage install
- Other basic population
The install stage is where the schema gets deployed, as well as
your version storage. In general this will be mostly automatic,
unless you want to do something like prepopulate a users table.
install example
# $migrations/_common/install/1/01_create_usr.pl
sub {
my $schema = shift;
$schema->resultset('User')->populate([
[qw( username password )],
[qw( frew test )],
]);
};
upgrade
- bump schema version + run prepare_install + prepare_upgrade first
- schema modifications
- vanilla additions/deletions should be automatic
- column renames need user scripting
- also: downgrade
This stage is where your schema gets modified. If you are doing
simple changes like adding/removing tables/columns it should be
entirely automatic. If you are renaming things or transforming
data you'll need to add some scripts yourself.
upgrade example
# $migrations/_common/upgrade/1-2/01_hash_pass.pl
sub {
my $schema = shift;
$_->update({
password => $_->get_column('password')
}) for $schema->resultset('User')->all;
};
_source
and _preprocess_schema
- Look ma! No SQL!
- No obnoxious renamed_from's in you DBIC::Schema
- Don't touch _source
- _preprocess_schema is simple a coderef that takes the old and new schemata
Workflows
catered to team size
- Different teams must work differently
- Cannot give examples for every team
- But will make a few major size based examples
- Small (1-3 devs)
- Medium (4-12 devs)
- Large (> 12 devs)
Small Team - People
- Team of 1-3
- Nathan is a Software Developer/Manager/DBA
- He wears a lot of hats
Small Team - Goals
- Nathan's main goals are to deliver
- software on time/under budget
- software that meets the requirements originally specified
Small Team
How DBICDH can help
- Minimizing the amount of tools to learn
- Minimize need to learn your weird flavor of DDL
- No need to learn your RDBMS' GUI tool
- Just use DBICDH, save time
Small Team
How DBICDH can help (2)
- Minimize interaction with DBICDH
- Set the "ignore_ddl" attribute for your $dh's instantiation
- Set "databases" attribute to []
- No SQL (except what you choose to write) to interact with
Medium Team - People
- Team of 4-12
- Jeff (and others) are Software Engineers
- Anne is the lone DBA
Medium Team - Goals
- The software developers want to have a good relationship with Anne
- They want to be able to use their favorite ORM!
Medium Team - Goals (2)
- Anne wants to ensure that the database has
- Foreign key constraints
- Unique Constraints
- Defaults
- Etc
Medium Team
How DBICDH can help
- The software developers will generally be able to model their data sanely with DBIC naturally, because DBIC
- Has easy-to-work-with relationships
- Encourages sane usage of constraints
Medium Team
How DBICDH can help (2)
- Anne's job can be helped by
- NOT setting the "ignore_ddl" attribute for your $dh's instantiation
- Setting the "databases" attribute to 'MySQL' (or whatever)
- And then Anne can audit and modify the generated DDL
- Furthermore, thanks to the recommended practice of doing upgrades instead of redeploys, Anne can simply audit DDL modifications instead of re-auditing the entire database
Large Team - People
- Team of > 12
- Lots of developers with lots of different goals (we'll go over a specific example later)
- Jeff is DBA
- Catherine is a QA Professional
Large Team - Goals
- Software developers mostly need to be able to be isolated
- Not all team members can know what all the others are working on
Large Team - Goals (2)
- Jeff (DBA) wants to ensure that the same things that a middle sized team's DBA would: database has
- Foreign key constraints
- Unique Constraints
- Defaults
- Etc
Large Team - Goals (3)
- Catherine (QA) needs to be able to
- vet each developer's work to allow it to be released
- make tests that will work sanely with the team's gigantic database
Large Team
How DBICDH can help
- In addition to what's noted before for the medium sized teams, the Software Developers will also be able to take advantage of the flexibility of DBICDH
- With careful use of customization software developers are able to take advantage of their version control's branching mechanisms...(more)
Large Team
How DBICDH can help (2)
- Thanks to DBICDH's ability to run custom perl Catherine can take advantage of DBIx::Class::Fixtures
- This allows her to only export a subset of the existing database
DBIx::Class::Fixtures
A side trip
- Define sets for which rows to export (can be id's or even DBIx::Class search conditions)
- Automatically export related belongs_to's
- Define rules for how much related data (has_many's) to export for rows
Large Team
How DBICDH can help (3)
- Jeff (DBA) inspects and updates each developer's DDL, as discussed previously, but his modifications remain in a branch
- Thanks to some custom VersionHandler's (which we'll discuss later) Catherine (QA) is able to install branched DDL and the other DDL that it depends on
- More on how that works later
- After Catherine decides that she's happy with the overall state of the system, she can promote the code and database changes to production
PRO TIPS
- Deadlocks!
- Use monotonic, install first version, upgrade from there
Implementation Side Trip
- Roles are great!
- But you can have method name collisions etc
Delegation
- Delegation is great!
- But it often shows too much implementation and overriding parts is hard
DO BOTH
- Use Delegation...
- But have roles that make top level methods to automatically delegate to delegate objects
- People can override single methods still
- Or slot in brand new delegates
Implementation Side Trip
END
- Should probably use something more proven at some point, but right
now my half baked solution works great.
Customization
- Already lots of override points so should be easier (thanks to DBICDH::Deprecated)
VersionStorage
Changing table name
- DBICDH Storage
- DBIC Result
- Custom DBICDH
DBICDH Storage
package MyApp::Schema::DBICDHStorage;
use Moose;
extends
'DBIx::Class::DeploymentHandler' .
'::VersionStorage::Standard';
sub _build_version_rs {
$_[0]->schema->register_class(
__VERSION =>
'MyApp::Schema::DBICDHStorageResult'
);
$_[0]->schema->resultset('__VERSION')
}
no Moose;
__PACKAGE__->meta->make_immutable;
1;
DBICDH Storage, pt 2
- Note register class and __VERSION
DBIC Result
package MyApp::Schema::DBICDHStorageResult;
use parent
'DBIx::Class::DeploymentHandler' .
'::VersionStorage::Standard::VersionResult';
__PACKAGE__->table('fl_bench_journal_versions');
1;
DBIC Result, pt 2
- Merely change the table name and we should be golden
Custom DBICDH
package MyApp::Schema::DeploymentHandler;
use Moose;
extends 'DBIx::Class::DeploymentHandler::Dad';
with 'DBIx::Class::DeploymentHandler::WithApplicatorDumple' => {
interface_role => 'DBIx::Class::DeploymentHandler::HandlesDeploy',
class_name =>
'DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator',
delegate_name => 'deploy_method',
attributes_to_assume => ['schema'],
attributes_to_copy => [qw( databases script_directory sql_translator_args )],
},
'DBIx::Class::DeploymentHandler::WithApplicatorDumple' => {
interface_role => 'DBIx::Class::DeploymentHandler::HandlesVersioning',
class_name => 'DBIx::Class::DeploymentHandler::VersionHandler::Monotonic',
delegate_name => 'version_handler',
attributes_to_assume => [qw( database_version schema_version to_version )],
},
'DBIx::Class::DeploymentHandler::WithApplicatorDumple' => {
interface_role => 'DBIx::Class::DeploymentHandler::HandlesVersionStorage',
class_name => 'MyApp::Schema::DBICDHStorage',
delegate_name => 'version_storage',
attributes_to_assume => ['schema'],
};
Custom DBICDH (2)
with 'DBIx::Class::DeploymentHandler::WithReasonableDefaults';
sub prepare_version_storage_install {
$_[0]->prepare_resultsource_install(
$_[0]->version_storage->version_rs->result_source
);
}
sub install_version_storage {
$_[0]->install_resultsource(
$_[0]->version_storage->version_rs->result_source
);
}
sub prepare_install {
$_[0]->prepare_deploy;
$_[0]->prepare_version_storage_install;
}
no Moose;
__PACKAGE__->meta->make_immutable;
1;
VersionStorage: etc
- You also might want to change the columns of the table
- Or even do something like not store on the db
VersionHandler
- Git style non-linear upgrades
- QC can inspect and approve different database migrations in various orders
VersionHandler: branches
- User Auth
- Parts Management
VersionHandler
git handwaving
- git can be used to see what depends on what
- Suppose we already have a module that makes that part easy
VersionHandler
how it might work
- QC requests to install Enemies
- Overridden VersionHandler queries git and sees that we need to install Auth first
- VersionHandler tells us to install Auth and then Enemies
Questions?
THE END