MySQL Workbench als Quelle für Perl-Code

Ich nutze seit vielen Jahren die MySQL Workbench um z.B. mit Kunden das Datenbankdesign zu besprechen. Das nutze ich dann als Quelle um z.B. DBIx::Class-Klassen zu generieren und/oder eine Types::Tiny-Bibliothek zu erstellen.

Transcript

3. It could go in the wrong direction
5. A picture is worth a thousand words
6. MySQL Workbench
7. • Visual Design Tool • (mainly) for MySQL • Exports for SVG, PDF, PNG • Can connect to (MySQL) databases • Reverse/Forward engineering • Scripting with Python • …
13. @ @ *.1.0.mwb *.1.1.mwb
14. Better coordination with the customer
15. MySQL Workbench - DRY
16. MySQL Workbench - a source for Perl code
17. *.mwb $ unzip project.mwb Archive: project.mwb MySQL Workbench Model archive 1.0 model-schemas: mydb extracting: lock inflating: document.mwb.xml inflating: @db/data.db
18. *.mwb $ unzip project.mwb Archive: project.mwb MySQL Workbench Model archive 1.0 model-schemas: mydb extracting: lock inflating: document.mwb.xml inflating: @db/data.db
19. $ unzip project.mwb Archive: project.mwb MySQL Workbench Model archive 1.0 model-schemas: mydb extracting: lock inflating: document.mwb.xml inflating: @db/data.db MySQL::Workbench::Parser
21. *.mwb DBIx::Class MySQL::Workbench::DBIC
22. use File::Basename; use File::Spec; use MySQL::Workbench::DBIC; my $base_dir = dirname( __FILE__ ); my $mwb_file = File::Spec->catfile( $base_dir, 'projects.mwb' ); my $out_dir = File::Spec->catdir( $base_dir, 'lib' ); my $foo = MySQL::Workbench::DBIC->new( file => $mwb_file, output_path => $out_dir, namespace => 'GPW::DB', version_add => 1, schema_name => 'Schema', uppercase => 1, inherit_from_core => 1, ); $foo->create_schema; print sprintf "Version %s of DB created\n", $foo->version;
23. use File::Basename; use File::Spec; use MySQL::Workbench::DBIC; my $base_dir = dirname( __FILE__ ); my $mwb_file = File::Spec->catfile( $base_dir, 'projects.mwb' ); my $out_dir = File::Spec->catdir( $base_dir, 'lib' ); my $foo = MySQL::Workbench::DBIC->new( file => $mwb_file, output_path => $out_dir, namespace => 'GPW::DB', version_add => 1, schema_name => 'Schema', uppercase => 1, inherit_from_core => 1, ); $foo->create_schema; print sprintf "Version %s of DB created\n", $foo->version;
25. $ perl create_schema.pl Version 1 of DB created $ tree lib/ lib/ └── GPW └── DB ├── Schema │ └── Result │ ├── Person.pm │ ├── ProjectHasPerson.pm │ └── Project.pm └── Schema.pm 4 directories, 4 files
26. package GPW::DB::Schema::Result::Project; # ABSTRACT: Result class for project use base qw(DBIx::Class::Core); our $VERSION = 1; __PACKAGE__->table( 'project' ); __PACKAGE__->add_columns( qw/ project_id project_title / ); __PACKAGE__->set_primary_key( qw/ project_id / ); __PACKAGE__->has_many(project_has_person => 'GPW::DB::Schema::Result::ProjectHasPerson', { 'foreign.project_project_id' => 'self.project_id' }); # --- # Put your own code below this comment # --- # --- 1;
27. package GPW::DB::Schema::Result::Project; # ABSTRACT: Result class for project use base qw(DBIx::Class::Core); our $VERSION = 1; __PACKAGE__->table( 'project' ); __PACKAGE__->add_columns( qw/ project_id project_title / ); __PACKAGE__->set_primary_key( qw/ project_id / ); __PACKAGE__->has_many(project_has_person => 'GPW::DB::Schema::Result::ProjectHasPerson', { 'foreign.project_project_id' => 'self.project_id' }); # --- # Put your own code below this comment # --- # --- 1;
28. package GPW::DB::Schema::Result::Project; # ABSTRACT: Result class for project use base qw(DBIx::Class::Core); our $VERSION = 1; __PACKAGE__->table( 'project' ); __PACKAGE__->add_columns( qw/ project_id project_title / ); __PACKAGE__->set_primary_key( qw/ project_id / ); __PACKAGE__->has_many(project_has_person => 'GPW::DB::Schema::Result::ProjectHasPerson', { 'foreign.project_project_id' => 'self.project_id' }); # --- # Put your own code below this comment # --- # --- 1;
29. *.mwb DBIx::Class MySQL/ PostgreSQL
30. use File::Basename; use File::Spec; use MySQL::Workbench::DBIC; my $base_dir = dirname( __FILE__ ); my $mwb_file = File::Spec->catfile( $base_dir, 'projects.mwb' ); my $out_dir = File::Spec->catdir( $base_dir, 'lib' ); my $foo = MySQL::Workbench::DBIC->new( file => $mwb_file, output_path => $out_dir, namespace => 'GPW::DB', version_add => 1, schema_name => 'Schema', uppercase => 1, inherit_from_core => 1, ); $foo->create_schema; print sprintf "Version %s of DB created\n", $foo->version;
31. use File::Basename; use File::Spec; use MySQL::Workbench::DBIC; my $base_dir = dirname( __FILE__ ); my $mwb_file = File::Spec->catfile( $base_dir, 'projects.mwb' ); my $out_dir = File::Spec->catdir( $base_dir, 'lib' ); my $foo = MySQL::Workbench::DBIC->new( file => $mwb_file, output_path => $out_dir, namespace => 'GPW::DB', version_add => 1, schema_name => 'Schema', column_details => 1, # default 1 uppercase => 1, inherit_from_core => 1, ); $foo->create_schema; print sprintf "Version %s of DB created\n", $foo->version;
32. $ perl create_schema.pl Version 2 of DB created $ tree lib/ lib/ └── GPW └── DB ├── Schema │ └── Result │ ├── Person.pm │ ├── ProjectHasPerson.pm │ └── Project.pm └── Schema.pm 4 directories, 4 files
33. __PACKAGE__->table( 'project' ); __PACKAGE__->add_columns( project_id => { data_type => 'INT', is_numeric => 1, retrieve_on_insert => 1, }, project_title => { data_type => 'VARCHAR', is_nullable => 1, size => 45, }, ); __PACKAGE__->set_primary_key( qw/ project_id / ); __PACKAGE__->has_many(project_has_person => 'GPW::DB::Schema::Result::ProjectHasPerson', { 'foreign.project_project_id' => 'self.project_id' });
34. Ugly hacks
36. __PACKAGE__->load_components( qw/InflateColumn::TimePiece/ ); __PACKAGE__->table( 'project' ); __PACKAGE__->add_columns( project_id => { data_type => 'INT', is_numeric => 1, retrieve_on_insert => 1, }, project_title => { data_type => 'VARCHAR', is_nullable => 1, size => 45, }, created => { data_type => 'INT', is_nullable => 1, is_numeric => 1, 'inflate_time_piece' => 1 }, );
37. *.mwb DBIx::Class MySQL/ PostgreSQL DBIx::Class::DeploymentHandler
39. *.mwb Types::Tiny *::SQLTypesLibrary
40. use MySQL::Workbench::SQLTypesLibrary; my $generator = MySQL::Workbench::SQLTypesLibrary->new( mwb => '/path/to/file.mwb', output => '/path/to/Library.pm', ); $generator->run;
41. package Library; use strict; use warnings; use Type::Library -base, -declare => qw( Varchar45 CustomType ); use Type::Utils -all; use Types::Standard -types; extends 'Types::SQL'; our $VERSION = $version; declare Varchar45, as Varchar[45]; # From Types::SQL declare CustomType, as Str, where { length( $_ ) < 9999 } 1;
42. package GPW::Model::Project; use Moo; use Library qw(:all); has project_id => ( is => 'ro', isa => Int ); has project_title => ( is => 'ro', isa => Varchar45 ); 1;
43. WIP (alpha)
44. TODO
45. • Merge different versions / branches – commit only .xml file (for diffs/merges)? • Improve support for custom datatypes • ...
46. I know it‘s not perfect
47. MySQL Workbench - a source for Perl code
48. Questions?
49. Thank you!