From e60ae4735361e9fc46b973296fb6549f5c813885 Mon Sep 17 00:00:00 2001 From: Frank Brehm Date: Fri, 10 Aug 2007 07:57:46 +0000 Subject: [PATCH] Bis zu den Zutaten des Rezepts gekommen git-svn-id: http://svn.brehm-online.com/svn/cookbook/trunk@17 191103c4-1d37-0410-b3e5-d8c2315c0aac --- lib/CookBook/Db.pm | 5 +- lib/CookBook/Db/IngredientGroups.pm | 59 ++++++ lib/CookBook/Db/Ingredients.pm | 59 ++++++ lib/CookBook/Db/RecipeIngredients.pm | 70 +++++++ sbin/initial_import.pl | 295 ++++++++++++++++++++++++++- 5 files changed, 480 insertions(+), 8 deletions(-) create mode 100644 lib/CookBook/Db/IngredientGroups.pm create mode 100644 lib/CookBook/Db/Ingredients.pm create mode 100644 lib/CookBook/Db/RecipeIngredients.pm diff --git a/lib/CookBook/Db.pm b/lib/CookBook/Db.pm index fb77c84..f2440dd 100644 --- a/lib/CookBook/Db.pm +++ b/lib/CookBook/Db.pm @@ -25,8 +25,11 @@ use base qw/DBIx::Class::Schema/; __PACKAGE__->load_classes( qw/ Authors - RecipeAuthors + Ingredients + IngredientGroups Recipes + RecipeAuthors + RecipeIngredients Session SessionLog Users diff --git a/lib/CookBook/Db/IngredientGroups.pm b/lib/CookBook/Db/IngredientGroups.pm new file mode 100644 index 0000000..b2170ba --- /dev/null +++ b/lib/CookBook/Db/IngredientGroups.pm @@ -0,0 +1,59 @@ +package CookBook::Db::IngredientGroups; + +# $Id$ +# $URL$ + +=head1 NAME + +CookBook::Db::IngredientGroups + +=head1 DESCRIPTION + +Module for abstract database access to the table 'ingredient_groups' + +=cut + +#--------------------------------------------------------------------------- + +use strict; +use warnings; + +use CookBook::Common; +use base qw/DBIx::Class/; + +__PACKAGE__->load_components( + qw/ + PK::Auto + Core + / +); + +__PACKAGE__->table('ingredient_groups'); + +__PACKAGE__->add_columns( + "ingredient_group_id" => { + 'data_type' => "INT", + 'default_value' => undef, + 'is_nullable' => 0, + 'size' => 10, + 'is_auto_increment' => 1, + 'extras' => { 'unsigned' => 1 }, + }, + "ingredient_group_name" => { 'data_type' => "VARCHAR", 'default_value' => "", 'is_nullable' => 0, 'size' => 100 }, + "date_created" => { 'data_type' => "DATETIME", 'default_value' => "", 'is_nullable' => 0, 'size' => 19 }, + "date_changed" => { 'data_type' => "DATETIME", 'default_value' => "", 'is_nullable' => 0, 'size' => 19 }, +); + +__PACKAGE__->set_primary_key("ingredient_group_id"); +__PACKAGE__->add_unique_constraint( "ingredient_group_name", ["ingredient_group_name"] ); + +# __PACKAGE__->might_have( 'user' => 'CookBook::Db::Users', { 'foreign.user_id' => 'self.user_id' } ); + +#---------------------------------------------------------------------------------------- + +1; + +#---------------------------------------------------------------------------------------- + +__END__ + diff --git a/lib/CookBook/Db/Ingredients.pm b/lib/CookBook/Db/Ingredients.pm new file mode 100644 index 0000000..176ac81 --- /dev/null +++ b/lib/CookBook/Db/Ingredients.pm @@ -0,0 +1,59 @@ +package CookBook::Db::Ingredients; + +# $Id$ +# $URL$ + +=head1 NAME + +CookBook::Db::Ingredients + +=head1 DESCRIPTION + +Module for abstract database access to the table 'ingredients' + +=cut + +#--------------------------------------------------------------------------- + +use strict; +use warnings; + +use CookBook::Common; +use base qw/DBIx::Class/; + +__PACKAGE__->load_components( + qw/ + PK::Auto + Core + / +); + +__PACKAGE__->table('ingredients'); + +__PACKAGE__->add_columns( + "ingredient_id" => { + 'data_type' => "INT", + 'default_value' => undef, + 'is_nullable' => 0, + 'size' => 10, + 'is_auto_increment' => 1, + 'extras' => { 'unsigned' => 1 }, + }, + "ingredient_name" => { 'data_type' => "VARCHAR", 'default_value' => "", 'is_nullable' => 0, 'size' => 100 }, + "date_created" => { 'data_type' => "DATETIME", 'default_value' => "", 'is_nullable' => 0, 'size' => 19 }, + "date_changed" => { 'data_type' => "DATETIME", 'default_value' => "", 'is_nullable' => 0, 'size' => 19 }, +); + +__PACKAGE__->set_primary_key("ingredient_id"); +__PACKAGE__->add_unique_constraint( "ingredient_name", ["ingredient_name"] ); + +# __PACKAGE__->might_have( 'user' => 'CookBook::Db::Users', { 'foreign.user_id' => 'self.user_id' } ); + +#---------------------------------------------------------------------------------------- + +1; + +#---------------------------------------------------------------------------------------- + +__END__ + diff --git a/lib/CookBook/Db/RecipeIngredients.pm b/lib/CookBook/Db/RecipeIngredients.pm new file mode 100644 index 0000000..abc025f --- /dev/null +++ b/lib/CookBook/Db/RecipeIngredients.pm @@ -0,0 +1,70 @@ +package CookBook::Db::RecipeIngredients; + +# $Id$ +# $URL$ + +=head1 NAME + +CookBook::Db::RecipeIngredients + +=head1 DESCRIPTION + +Module for abstract database access to the table 'recipe_ingredients' + +=cut + +#--------------------------------------------------------------------------- + +use strict; +use warnings; + +use CookBook::Common; +use base qw/DBIx::Class/; + +__PACKAGE__->load_components( + qw/ + PK::Auto + Core + / +); + +__PACKAGE__->table('recipe_ingredients'); + +__PACKAGE__->add_columns( + "recipe_ingredient_id" => { + 'data_type' => "INT", + 'default_value' => undef, + 'is_nullable' => 0, + 'size' => 10, + 'is_auto_increment' => 1, + 'extras' => { 'unsigned' => 1 }, + }, + "recipe_id" => { 'data_type' => "INT", 'default_value' => 0, 'is_nullable' => 0, 'size' => 10, 'extras' => { 'unsigned' => 1 } }, + "ingredient_id" => + { 'data_type' => "INT", 'default_value' => undef, 'is_nullable' => 1, 'size' => 10, 'extras' => { 'unsigned' => 1 } }, + "amount" => { 'data_type' => "FLOAT", 'default_value' => undef, 'is_nullable' => 1, 'size' => 32, 'extras' => { 'unsigned' => 1 }, }, + "amount_offset" => + { 'data_type' => "FLOAT", 'default_value' => undef, 'is_nullable' => 1, 'size' => 32, 'extras' => { 'unsigned' => 1 }, }, + "unit_id" => { 'data_type' => "INT", 'default_value' => undef, 'is_nullable' => 1, 'size' => 10, 'extras' => { 'unsigned' => 1 } }, + "order_index" => { 'data_type' => "INT", 'default_value' => 0, 'is_nullable' => 0, 'size' => 10, 'extras' => { 'unsigned' => 1 } }, + "ingredient_group_id" => + { 'data_type' => "INT", 'default_value' => undef, 'is_nullable' => 1, 'size' => 10, 'extras' => { 'unsigned' => 1 } }, + "date_created" => { 'data_type' => "DATETIME", 'default_value' => "", 'is_nullable' => 0, 'size' => 19 }, + "date_changed" => { 'data_type' => "DATETIME", 'default_value' => "", 'is_nullable' => 0, 'size' => 19 }, +); + +__PACKAGE__->set_primary_key("recipe_ingredient_id"); + +__PACKAGE__->belongs_to( 'recipe' => 'CookBook::Db::Recipes', 'recipe_id', ); +__PACKAGE__->belongs_to( 'ingredient' => 'CookBook::Db::Ingredients', 'ingredient_id', ); +__PACKAGE__->belongs_to( 'ingredient_group' => 'CookBook::Db::IngredientGroupss', 'ingredient_group_id', ); +#__PACKAGE__->belongs_to( 'unit' => 'CookBook::Db::Units', 'unit_id', ); + +#---------------------------------------------------------------------------------------- + +1; + +#---------------------------------------------------------------------------------------- + +__END__ + diff --git a/sbin/initial_import.pl b/sbin/initial_import.pl index 1e8f81f..24240b6 100755 --- a/sbin/initial_import.pl +++ b/sbin/initial_import.pl @@ -34,8 +34,11 @@ my @target_tables = qw( session_log authors yield_types + ingredients + ingredient_groups recipes recipe_authors + recipe_ingredients ); my $admin_data = { @@ -92,6 +95,7 @@ my ( $target_dbh, $source_dbh ); my %map_recipe_id; my %map_author_id; +my %map_ingredient_id; my $port; @@ -364,13 +368,16 @@ Erstellt alle notwendigen Ziel-Tabellen ... sub create_target_tables { my %create_method = ( - 'users' => \&create_user_table, - 'authors' => \&create_author_table, - 'session' => \&create_session_table, - 'session_log' => \&create_session_log_table, - 'recipes' => \&create_recipes_table, - 'yield_types' => \&create_yield_types_table, - 'recipe_authors' => \&create_recipe_authors_table, + 'users' => \&create_user_table, + 'authors' => \&create_author_table, + 'session' => \&create_session_table, + 'session_log' => \&create_session_log_table, + 'recipes' => \&create_recipes_table, + 'yield_types' => \&create_yield_types_table, + 'recipe_authors' => \&create_recipe_authors_table, + 'ingredients' => \&create_ingredients_table, + 'ingredient_groups' => \&create_ingredient_groups_table, + 'recipe_ingredients' => \&create_recipe_ingredients_table, ); print green_star() . " Erstelle Ziel-Tabellen ...\n"; @@ -463,6 +470,24 @@ sub import_data { } print ok() . "\n"; + print " - ingredients ... "; + eval { die "Tabelle 'ingredients' wurde nicht importiert.\n" unless import_ingredients_table(); }; + if ($@) { + print not_ok() . "\n"; + warn $@ . "\n"; + return undef; + } + print ok() . "\n"; + + print " - ingredient_groups ... "; + eval { die "Tabelle 'ingredient_groups' wurde nicht importiert.\n" unless import_ingredient_groups_table(); }; + if ($@) { + print not_ok() . "\n"; + warn $@ . "\n"; + return undef; + } + print ok() . "\n"; + print " - recipes ... "; eval { die "Tabelle 'recipes' wurde nicht importiert.\n" unless import_receipes_table(); }; if ($@) { @@ -480,6 +505,17 @@ sub import_data { return undef; } print ok() . "\n"; + + print " - recipe_ingredients ... "; + eval { die "Tabelle 'recipe_ingredients' wurde nicht importiert.\n" unless import_recipe_ingredients_table(); }; + if ($@) { + print not_ok() . "\n"; + warn $@ . "\n"; + return undef; + } + print ok() . "\n"; + + print "\n"; return 1; @@ -543,6 +579,150 @@ sub import_author_table { #------------------------------------------------------------- +=head2 create_ingredients_table( ) + +=cut + +sub create_ingredients_table { + + my $sql = <do($sql); + return 1; + +} ## end sub create_recipe_authors_table + +#------------------------------------------------------------- + +=head2 import_ingredients_table( ) + +=cut + +sub import_ingredients_table { + + my $sql = <prepare($sql) ) { + return undef; + } + + return undef unless $source_sth->execute(); + + $sql = <prepare($sql); + unless ($target_sth) { + $source_sth->finish(); + return undef; + } + + while ( $ingr = $source_sth->fetchrow_hashref() ) { + + $qparams = [ $ingr->{'name'} ]; + + unless ( $target_dbh->do( $sql, {}, @$qparams ) ) { + $source_sth->finish(); + return undef; + } + + $map_ingredient_id{ $ingr->{'id'} } = $target_dbh->{'mysql_insertid'}; + + } + + return 1; + +} ## end sub import_receipes_table + +#------------------------------------------------------------- + +=head2 create_ingredient_groups_table( ) + +=cut + +sub create_ingredient_groups_table { + + my $sql = <do($sql); + return 1; + +} ## end sub create_recipe_authors_table + +#------------------------------------------------------------- + +=head2 import_ingredient_groups_table( ) + +=cut + +sub import_ingredient_groups_table { + + my $sql = <prepare($sql) ) { + return undef; + } + + return undef unless $source_sth->execute(); + + $sql = <prepare($sql); + unless ($target_sth) { + $source_sth->finish(); + return undef; + } + + while ( $ingr = $source_sth->fetchrow_hashref() ) { + + $qparams = [ $ingr->{'id'}, $ingr->{'name'} ]; + + unless ( $target_dbh->do( $sql, {}, @$qparams ) ) { + $source_sth->finish(); + return undef; + } + + } + + return 1; + +} ## end sub import_receipes_table + +#------------------------------------------------------------- + =head2 create_session_table( ) =cut @@ -777,6 +957,7 @@ END_SQL if ( $old_rid != $row->{'recipe_id'} ) { $i = 1; + $old_rid = $row->{'recipe_id'}; } else { $i++; @@ -796,6 +977,106 @@ END_SQL #------------------------------------------------------------- +=head2 create_recipe_ingredients_table( ) + +=cut + +sub create_recipe_ingredients_table { + + my $sql = <do($sql); + return 1; + +} ## end sub create_recipe_authors_table + +#------------------------------------------------------------- + +=head2 import_recipe_ingredients_table( ) + +=cut + +sub import_recipe_ingredients_table { + + my $sql = <prepare($sql) ) { + return undef; + } + + return undef unless $source_sth->execute(); + + $sql = <prepare($sql); + unless ($target_sth) { + $source_sth->finish(); + return undef; + } + + $old_rid = 0; + $i = 0; + + while ( $row = $source_sth->fetchrow_hashref() ) { + + if ( $old_rid != $row->{'recipe_id'} ) { + $i = 1; + $old_rid = $row->{'recipe_id'}; + } + else { + $i++; + } + + $qparams = []; + push @$qparams, $map_recipe_id{ $row->{'recipe_id'} }; + push @$qparams, ( $row->{'ingredient_id'} and $row->{'ingredient_id'} > 0 ) ? $map_ingredient_id{ $row->{'ingredient_id'} } : undef; + push @$qparams, $row->{'amount'}; + push @$qparams, $row->{'amount_offset'} || undef; + push @$qparams, $row->{'unit_id'}; + push @$qparams, $i; + push @$qparams, ( ( $row->{'group_id'} and $row->{'group_id'} > 0 ) ? $row->{'group_id'} : undef ); + + #$qparams = [ $map_recipe_id{ $row->{'recipe_id'} }, $map_author_id{ $row->{'author_id'} }, $i, ]; + + unless ( $target_dbh->do( $sql, {}, @$qparams ) ) { + $source_sth->finish(); + return undef; + } + + } ## end while ( $row = $source_sth->fetchrow_hashref(... + + return 1; + +} ## end sub import_recipe_authors_table + +#------------------------------------------------------------- + =head2 create_user_table( ) Erstellt die Tabelle 'users' ... -- 2.39.5