[Sommaire] [Imprimer]

   1  #!/usr/local/bin/perl
   2  #
   3  # This script will update a pre-0.9.7 WebCalendar database to have the
   4  # correct tables for 0.9.7.
   5  # (FYI, these changes were necessary to add support for other databases
   6  # like Oracle.)
   7  #
   8  
   9  
  10  $mysql_path = "/usr/local/mysql/bin:/opt/mysql/bin";
  11  $dbname = "intranet";
  12  $tables = "cal_entry cal_entry_user cal_user cal_user_pref";
  13  $out = "commands.sql";
  14  
  15  # look for the mysql executable and mysqldump programs
  16  sub find_executable {
  17    my ( $app ) = @_;
  18    my ( $path, $dir, $file, @dirs );
  19  
  20    my ( $path ) = $ENV{"PATH"} . ":" . $mysql_path;
  21    @dirs = split ( /:/, $path );
  22    foreach $dir ( @dirs ) {
  23      $file = "$dir/$app";
  24      return $file if ( -x $file );
  25    }
  26  
  27    die "Could not find $app executable in:\n$path\n";
  28  }
  29  
  30  
  31  sub string_or_null {
  32    my ( $in ) = @_;
  33    my ( $ret );
  34  
  35    if ( $in eq "\\N" || length ( $in ) == 0 ) {
  36      $ret = "NULL";
  37    } else {
  38      $in =~ s/'/\\'/g;
  39      $ret = "\'$in\'";
  40    }
  41    return $ret;
  42  }
  43  
  44  sub num_or_zero {
  45    my ( $in ) = @_;
  46    return "NULL" if ( $in eq "\\N" );
  47    return "0" if ( $in eq "" );
  48    return $in;
  49  }
  50  
  51  sub date_to_int {
  52    my ( $in ) = @_;
  53    my ( $ret );
  54    if ( $in =~ /(\d\d\d\d)-(\d\d)-(\d\d)/ ) {
  55      $ret = sprintf "%04d%02d%02d", $1, $2, $3;
  56    } else {
  57      $ret = "NULL";
  58    }
  59    #print STDERR "Date \"$in\" -> $ret\n";
  60    return $ret;
  61  }
  62  
  63  
  64  sub time_to_int {
  65    my ( $in ) = @_;
  66    my ( $ret );
  67    if ( $in =~ /(\d\d):(\d\d):(\d\d)/ ) {
  68      $ret = sprintf "%02d%02d%02d", $1, $2, $3;
  69    } else {
  70      $ret = "NULL";
  71    }
  72    #print STDERR "Time \"$in\" -> $ret\n";
  73    return $ret;
  74  }
  75  
  76  
  77  $mysql = &find_executable ( "mysql" );
  78  $mysqldump = &find_executable ( "mysqldump" );
  79  
  80  print "mysql found: $mysql\n";
  81  print "mysqldump found: $mysqldump\n";
  82  
  83  # Get a current dump of the db
  84  $dir = "./data";
  85  mkdir ( $dir, 0755 ) if ( ! -d $dir );
  86  $cmd = "$mysqldump --no-create-info -T $dir $dbname $tables";
  87  print "Command: $cmd\n";
  88  system ( $cmd );
  89  
  90  # use datafiles to create a series of SQL Insert commands
  91  
  92  open ( OUT, ">$out" ) ||
  93    die "Error writing output file: $!\n";
  94  print OUT<<EOF;
  95  #DROP TABLE webcal_user;
  96  CREATE TABLE webcal_user (
  97    cal_login VARCHAR(25) NOT NULL,
  98    cal_passwd VARCHAR(25),
  99    cal_lastname VARCHAR(25),
 100    cal_firstname VARCHAR(25),
 101    cal_is_admin CHAR(1) DEFAULT 'N',
 102    cal_email VARCHAR(75) NULL,
 103    PRIMARY KEY ( cal_login )
 104  );
 105  #DROP TABLE webcal_entry;
 106  CREATE TABLE webcal_entry (
 107    cal_id INT NOT NULL,
 108    cal_group_id INT NULL,
 109    cal_create_by VARCHAR(25) NOT NULL,
 110    cal_date INT NOT NULL,
 111    cal_time INT NULL,
 112    cal_mod_date INT,
 113    cal_mod_time INT,
 114    cal_duration INT NOT NULL,
 115    cal_priority INT DEFAULT 2,
 116    cal_type CHAR(1) DEFAULT 'E',
 117    cal_access CHAR(1) DEFAULT 'P',
 118    cal_name VARCHAR(80) NOT NULL,
 119    cal_description TEXT,
 120    PRIMARY KEY ( cal_id )
 121  );
 122  #DROP TABLE webcal_entry_user;
 123  CREATE TABLE webcal_entry_user (
 124    cal_id int(11) DEFAULT '0' NOT NULL,
 125    cal_login varchar(25) DEFAULT '' NOT NULL,
 126    cal_status char(1) DEFAULT 'A',
 127    PRIMARY KEY (cal_id,cal_login)
 128  );
 129  #DROP TABLE webcal_user_pref;
 130  CREATE TABLE webcal_user_pref (
 131    cal_login varchar(25) NOT NULL,
 132    cal_setting varchar(25) NOT NULL,
 133    cal_value varchar(50) NULL,
 134    PRIMARY KEY ( cal_login, cal_setting )
 135  );
 136  EOF
 137  
 138  open ( IN, "$dir/cal_user.txt" );
 139  print OUT "\n\n\n";
 140  while ( <IN> ) {
 141    chop;
 142    @fields = split /\t/;
 143    print OUT "INSERT INTO webcal_user ( cal_login, cal_passwd, cal_lastname,\n" .
 144     "cal_firstname, cal_is_admin )\n  VALUES ( ";
 145    print OUT &string_or_null ( $fields[0] ) . ", ";
 146    print OUT &string_or_null ( $fields[1] ) . ", ";
 147    print OUT &string_or_null ( $fields[2] ) . ", ";
 148    print OUT &string_or_null ( $fields[3] ) . ", ";
 149    print OUT &string_or_null ( $fields[4] ) . " );\n";
 150  }
 151  close ( IN );
 152  
 153  open ( IN, "$dir/cal_entry.txt" );
 154  print OUT "\n\n\n";
 155  while ( <IN> ) {
 156    chop;
 157    while ( /\\$/ ) {
 158      s/[\\\r\n]+$//g;
 159      chop ( $nextline = <IN> );
 160      $nextline =~ s/[\\\r\n]+$//g;
 161      $_ .= "\\n" . $nextline;
 162    }
 163    @fields = split /\t/;
 164    $i = 0;
 165    print OUT "\nINSERT INTO webcal_entry\n" .
 166     "  ( cal_id, cal_group_id, cal_create_by,\n" .
 167     "  cal_date, cal_time, cal_mod_date, cal_mod_time, cal_duration,\n" .
 168     "  cal_priority, cal_type, cal_access, cal_name,\n" .
 169     "  cal_description )\n  VALUES (\n  ";
 170    print OUT &num_or_zero ( $fields[$i++] ) . ", ";    #cal_id
 171    print OUT "NULL, ";                    #cal_group_id
 172    print OUT &string_or_null ( $fields[$i++] ) . ", ";    #cal_create_by
 173    print OUT &date_to_int ( $fields[$i++] ) . ", ";    #cal_date
 174    print OUT &time_to_int ( $fields[$i++] ) . ", ";    #cal_time
 175    print OUT &date_to_int ( $fields[$i] ) . ", ";    #cal_mod_date
 176    print OUT &time_to_int ( $fields[$i++] ) . ", ";    #cal_mod_time
 177    print OUT &num_or_zero ( $fields[$i++] ) . ", ";    #cal_duration
 178    print OUT &string_or_null ( $fields[$i++] ) . ", ";    #cal_priority
 179    print OUT &string_or_null ( $fields[$i++] ) . ", ";    #cal_type
 180    print OUT &string_or_null ( $fields[$i++] ) . ", ";    #cal_access
 181    $i++; # skip over status since it was never used
 182    print OUT &string_or_null ( $fields[$i++] ) . ", ";    #cal_name
 183    print OUT &string_or_null ( $fields[$i++] ) . ");\n";    #cal_description
 184  }
 185  close ( IN );
 186  
 187  open ( IN, "$dir/cal_entry_user.txt" );
 188  print OUT "\n\n\n";
 189  while ( <IN> ) {
 190    chop;
 191    @fields = split /\t/;
 192    print OUT "INSERT INTO webcal_entry_user\n" .
 193     "( cal_id, cal_login, cal_status )\n" .
 194     "VALUES ( ";
 195    $i = 0;
 196    print OUT &num_or_zero ( $fields[$i++] ) . ", ";
 197    print OUT &string_or_null ( $fields[$i++] ) . ", ";
 198    print OUT &string_or_null ( $fields[$i++] ) . " );\n";
 199  }
 200  close ( IN );
 201  
 202  
 203  open ( IN, "$dir/cal_user_pref.txt" );
 204  print OUT "\n\n\n";
 205  while ( <IN> ) {
 206    chop;
 207    @fields = split /\t/;
 208    print OUT "INSERT INTO webcal_user_pref\n" .
 209     "( cal_login, cal_setting, cal_value )\n" .
 210     "VALUES ( ";
 211    $i = 0;
 212    print OUT &string_or_null ( $fields[$i++] ) . ", ";
 213    print OUT &string_or_null ( $fields[$i++] ) . ", ";
 214    print OUT &string_or_null ( $fields[$i++] ) . " );\n";
 215  }
 216  close ( IN );
 217  
 218  close ( OUT );
 219  
 220  exit 0;