MySQL Workbench als Quelle für Perl-Code

07b86e788c12e66f05d3bb7099aa1967?s=47 Renee
March 07, 2019

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.

07b86e788c12e66f05d3bb7099aa1967?s=128

Renee

March 07, 2019
Tweet

Transcript

  1. None
  2. None
  3. It could go in the wrong direction

  4. None
  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 • …
  8. None
  9. None
  10. None
  11. None
  12. None
  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
  20. None
  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;
  24. None
  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

  35. None
  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

  38. None
  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!