Tweet
Tweet

Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

It could go in the wrong direction

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

A picture is worth a thousand words

Slide 6

Slide 6 text

MySQL Workbench

Slide 7

Slide 7 text

● Visual Design Tool ● (mainly) for MySQL ● Exports for SVG, PDF, PNG ● Can connect to (MySQL) databases ● Reverse/Forward engineering ● Scripting with Python ● …

Slide 8

Slide 8 text

No content

Slide 9

Slide 9 text

No content

Slide 10

Slide 10 text

No content

Slide 11

Slide 11 text

No content

Slide 12

Slide 12 text

No content

Slide 13

Slide 13 text

@ @ *.1.0.mwb *.1.1.mwb

Slide 14

Slide 14 text

Better coordination with the customer

Slide 15

Slide 15 text

MySQL Workbench - DRY

Slide 16

Slide 16 text

MySQL Workbench - a source for Perl code

Slide 17

Slide 17 text

*.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

Slide 18

Slide 18 text

*.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

Slide 19

Slide 19 text

$ 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

Slide 20

Slide 20 text

No content

Slide 21

Slide 21 text

*.mwb DBIx::Class MySQL::Workbench::DBIC

Slide 22

Slide 22 text

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;

Slide 23

Slide 23 text

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;

Slide 24

Slide 24 text

No content

Slide 25

Slide 25 text

$ 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

Slide 26

Slide 26 text

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;

Slide 27

Slide 27 text

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;

Slide 28

Slide 28 text

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;

Slide 29

Slide 29 text

*.mwb DBIx::Class MySQL/ PostgreSQL

Slide 30

Slide 30 text

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;

Slide 31

Slide 31 text

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;

Slide 32

Slide 32 text

$ 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

Slide 33

Slide 33 text

__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' });

Slide 34

Slide 34 text

Ugly hacks

Slide 35

Slide 35 text

No content

Slide 36

Slide 36 text

__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 }, );

Slide 37

Slide 37 text

*.mwb DBIx::Class MySQL/ PostgreSQL DBIx::Class::DeploymentHandler

Slide 38

Slide 38 text

No content

Slide 39

Slide 39 text

*.mwb Types::Tiny *::SQLTypesLibrary

Slide 40

Slide 40 text

use MySQL::Workbench::SQLTypesLibrary; my $generator = MySQL::Workbench::SQLTypesLibrary->new( mwb => '/path/to/file.mwb', output => '/path/to/Library.pm', ); $generator->run;

Slide 41

Slide 41 text

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;

Slide 42

Slide 42 text

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;

Slide 43

Slide 43 text

WIP (alpha)

Slide 44

Slide 44 text

TODO

Slide 45

Slide 45 text

● Merge different versions / branches – commit only .xml file (for diffs/merges)? ● Improve support for custom datatypes ● ...

Slide 46

Slide 46 text

I know it‘s not perfect

Slide 47

Slide 47 text

MySQL Workbench - a source for Perl code

Slide 48

Slide 48 text

Questions?

Slide 49

Slide 49 text

Thank you!