$30 off During Our Annual Pro Sale. View Details »

MySQL Workbench als Quelle für Perl-Code

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.

Renee

March 07, 2019
Tweet

More Decks by Renee

Other Decks in Technology

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!