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

1. View Slide

2. View Slide

3. It could go in the wrong direction
   

   View Slide

4. View Slide

5. A picture is worth a thousand words
   

   View Slide

6. MySQL
   Workbench
   

   View Slide

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

   View Slide

8. View Slide

9. View Slide

10. View Slide

11. View Slide

12. View Slide

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

    View Slide

14. Better coordination
    with the customer
    

    View Slide

15. MySQL
    Workbench
    - DRY
    

    View Slide

16. MySQL
    Workbench
    - a source for
    Perl code
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

20. View Slide

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

    View Slide

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;
    

    View Slide

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;
    

    View Slide

24. View Slide

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
    

    View Slide

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;
    

    View Slide

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;
    

    View Slide

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;
    

    View Slide

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

    View Slide

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;
    

    View Slide

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;
    

    View Slide

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
    

    View Slide

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

    View Slide

34. Ugly hacks
    

    View Slide

35. View Slide

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

    View Slide

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

    View Slide

38. View Slide

39. *.mwb
    Types::Tiny
    *::SQLTypesLibrary
    

    View Slide

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

    View Slide

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;
    

    View Slide

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;
    

    View Slide

43. WIP (alpha)
    

    View Slide

44. TODO
    

    View Slide

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

    View Slide

46. I know it‘s
    not perfect
    

    View Slide

47. MySQL
    Workbench
    - a source for
    Perl code
    

    View Slide

48. Questions?
    

    View Slide

49. Thank you!
    

    View Slide