From 5c4452dac84a1dae8fa74ebcde235efd8896f04c Mon Sep 17 00:00:00 2001
From: Frank Brehm <frank@brehm-online.com>
Date: Thu, 9 Aug 2007 12:20:06 +0000
Subject: [PATCH] Rezepte erstmals importiert

git-svn-id: http://svn.brehm-online.com/svn/cookbook/trunk@12 191103c4-1d37-0410-b3e5-d8c2315c0aac
---
 sbin/initial_import.pl | 199 ++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 196 insertions(+), 3 deletions(-)

diff --git a/sbin/initial_import.pl b/sbin/initial_import.pl
index 55ba817..17740d1 100755
--- a/sbin/initial_import.pl
+++ b/sbin/initial_import.pl
@@ -13,6 +13,7 @@ use Data::Dumper;
 use DBI;
 use Term::ANSIColor;
 use Crypt::SaltedHash;
+use Digest::MD5 qw(md5_hex);
 
 $Data::Dumper::Indent   = 1;
 $Data::Dumper::Sortkeys = 1;
@@ -29,7 +30,10 @@ $| = 1;
 
 my @target_tables = qw(
     users
+    session
+    session_log
     authors
+    recipes
 );
 
 my $admin_data = {
@@ -355,8 +359,11 @@ Erstellt alle notwendigen Ziel-Tabellen ...
 sub create_target_tables {
 
     my %create_method = (
-        'users'   => \&create_user_table,
-        'authors' => \&create_author_table,
+        'users'       => \&create_user_table,
+        'authors'     => \&create_author_table,
+        'session'     => \&create_session_table,
+        'session_log' => \&create_session_log_table,
+        'recipes'     => \&create_recipes_table,
     );
 
     print green_star() . " Erstelle Ziel-Tabellen ...\n";
@@ -440,6 +447,15 @@ sub import_data {
     }
     print ok() . "\n";
 
+    print "   - recipes ... ";
+    eval { die "Tabelle 'recipes' wurde nicht importiert.\n" unless import_receipes_table(); };
+    if ($@) {
+        print not_ok() . "\n";
+        warn $@ . "\n";
+        return undef;
+    }
+    print ok() . "\n";
+
     print "\n";
     return 1;
 
@@ -495,6 +511,183 @@ sub import_author_table {
 
 #-------------------------------------------------------------
 
+=head2 create_session_table( )
+
+=cut
+
+sub create_session_table {
+
+    my $sql = <<END_SQL;
+CREATE TABLE IF NOT EXISTS `session` (
+  `id`           varchar(72)      NOT NULL COMMENT 'Session-Id',
+  `session_data` text             NOT NULL COMMENT 'Sitzungsdaten',
+  `expires`      int(10) unsigned NOT NULL COMMENT 'UNIX-Timestamp für Ungültigkeit',
+  PRIMARY KEY  (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Sitzungsdaten für Catalyst'
+END_SQL
+
+    return undef unless $target_dbh->do($sql);
+    return 1;
+
+}
+
+#-------------------------------------------------------------
+
+=head2 create_session_log_table( )
+
+=cut
+
+sub create_session_log_table {
+
+    my $sql = <<END_SQL;
+CREATE TABLE IF NOT EXISTS `session_log` (
+  `session_log_id` int(10) unsigned NOT NULL auto_increment,
+  `user_id`        int(10) unsigned NOT NULL,
+  `login_time`     datetime         NOT NULL,
+  `logout_time`    datetime                            default NULL,
+  `logout_reason`  enum('normal','automatic','forced') default NULL,
+  `session_id`     varchar(72)                         default NULL,
+  `login`          varchar(50)      NOT NULL,
+  `user_name`      varchar(110)     NOT NULL,
+  `client_ip`      varchar(20)                         default NULL,
+  `client_host`    varchar(200)                        default NULL,
+  `user_agent`     varchar(250)                        default NULL,
+  `server`         varchar(100)                        default NULL COMMENT 'Die Basisadresse des angefragten Servers',
+  PRIMARY KEY              (`session_log_id`),
+  KEY         `user_id`    (`user_id`),
+  KEY         `session_id` (`session_id`)
+) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Session-Log und Statistik';
+END_SQL
+
+    return undef unless $target_dbh->do($sql);
+    return 1;
+
+}
+
+#-------------------------------------------------------------
+
+=head2 create_recipes_table( )
+
+=cut
+
+sub create_recipes_table {
+
+    my $sql = <<END_SQL;
+CREATE TABLE `recipes` (
+  `recipe_id`           int(10) unsigned NOT NULL auto_increment,
+  `old_recipe_id`       int(11)                   default NULL COMMENT 'alte ID aus Kreceipes',
+  `rid`                 char(32)         NOT NULL              COMMENT 'MD5-ID des Rezeptes',
+  `title`               varchar(250)     NOT NULL              COMMENT 'Rezepttitel',
+  `published`           enum('n','y')    NOT NULL default 'n'  COMMENT 'Ist das Rezept veröffentlicht?',
+  `deleted`             enum('n','y')    NOT NULL              COMMENT 'Wurde dieses Rezept gelöscht?',
+  `locked`              enum('n','y')    NOT NULL default 'n'  COMMENT 'Ist das Rezept von einem Administrator gesperrt?',
+  `preface`             text                                   COMMENT 'Vorwort zum Rezept',
+  `instructions`        text             NOT NULL              COMMENT 'Zubereitungsanleitung',
+  `yield_amount`        float unsigned   NOT NULL default '0'  COMMENT 'Ergebnismenge',
+  `yield_amount_offset` float unsigned            default NULL COMMENT 'Ergebnismengen-Spielraum',
+  `yield_type_id`       int(10) unsigned          default NULL COMMENT 'Ergebnismengentyp-Verknüpfung',
+  `prep_time`           time                      default NULL COMMENT 'Zubereitungszeit',
+  `date_created`        datetime         NOT NULL              COMMENT 'Zeitpunkt der Erstellens des Rezeptes',
+  `user_created`        int(10) unsigned NOT NULL              COMMENT 'User-Id des Erstellenden',
+  `date_changed`        datetime         NOT NULL              COMMENT 'Zeitpunkt der letzten Änderung',
+  `user_changed`        int(10) unsigned NOT NULL              COMMENT 'User-Id des letzten Änderers',
+  PRIMARY KEY                    (`recipe_id`),
+  UNIQUE KEY   `rid`             (`rid`),
+  KEY          `old_recipe_id`   (`old_recipe_id`),
+  KEY          `title`           (`title`),
+  FULLTEXT KEY `title_ft`        (`title`),
+  FULLTEXT KEY `instructions_ft` (`instructions`),
+  FULLTEXT KEY `preface_ft`      (`preface`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Eigentliche Rezepte-Tabelle'
+END_SQL
+
+    return undef unless $target_dbh->do($sql);
+    return 1;
+
+}
+
+#-------------------------------------------------------------
+
+=head2 import_receipes_table( )
+
+=cut
+
+sub import_receipes_table {
+
+    my $sql = <<END_SQL;
+SELECT `id`,
+       `title`,
+       `yield_amount`,
+       `yield_amount_offset`,
+       `yield_type_id`,
+       `instructions`,
+       `prep_time`,
+       `ctime`,
+       `mtime`
+  FROM `recipes`
+ ORDER BY `ctime`
+END_SQL
+
+    my ( $source_sth, $target_sth, $recipe, $row, $count, $qparams, $rid );
+
+    unless ( $source_sth = $source_dbh->prepare($sql) ) {
+        return undef;
+    }
+
+    return undef unless $source_sth->execute();
+
+    $sql = <<END_SQL;
+INSERT INTO `recipes` (
+       `old_recipe_id`, `rid`, `title`, `published`, `deleted`, `locked`, `preface`, `instructions`,
+       `yield_amount`, `yield_amount_offset`, `yield_type_id`, `prep_time`,
+       `date_created`, `user_created`, `date_changed`, `user_changed`
+    )
+    VALUES (
+        ?, ?, ?, 'y', 'n', 'n', NULL, ?,
+        ?, ?, ?, ?,
+        ?, 1, ?, 1
+    )
+END_SQL
+    $target_sth = $target_dbh->prepare($sql);
+    unless ( $target_sth ) {
+        $source_sth->finish();
+        return undef;
+    }
+
+    my $i = 0;
+
+    while ( $recipe = $source_sth->fetchrow_hashref() ) {
+
+        $i++;
+        my $digest_source = sprintf( '%07d-%s-%s', $i, time(), $recipe->{'title'} );
+        my $digest        = md5_hex($digest_source);
+
+        $qparams = [
+            $recipe->{'id'},
+            $digest,
+            $recipe->{'title'},
+            $recipe->{'instructions'},
+            $recipe->{'yield_amount'} || 0,
+            $recipe->{'yield_amount_offset'} || undef,
+            ( $recipe->{'yield_type_id'} and $recipe->{'yield_type_id'} > 0 ? $recipe->{'yield_type_id'} : undef ),
+            $recipe->{'prep_time'},
+            $recipe->{'ctime'},
+            $recipe->{'mtime'},
+        ];
+
+        unless ( $target_dbh->do( $sql, {}, @$qparams ) ) {
+            $source_sth->finish();
+            return undef;
+        }
+    }
+
+    return 1;
+
+
+
+}
+#-------------------------------------------------------------
+
 =head2 create_user_table( )
 
 Erstellt die Tabelle 'users' ...
@@ -504,7 +697,7 @@ Erstellt die Tabelle 'users' ...
 sub create_user_table {
 
     my $sql = <<END_SQL;
-CREATE TABLE `users` (
+CREATE TABLE IF NOT EXISTS `users` (
   `user_id`      int(10) unsigned NOT NULL auto_increment,
   `login`        varchar(50)      NOT NULL COMMENT 'Login-Name',
   `vorname`      varchar(100)     NOT NULL,
-- 
2.39.5