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
10 $mysql_path = "/usr/local/mysql/bin:/opt/mysql/bin";
12 $tables = "cal_entry cal_entry_user cal_user cal_user_pref";
13 $out = "commands.sql";
15 # look for the mysql executable and mysqldump programs
18 my ( $path, $dir, $file, @dirs );
20 my ( $path ) = $ENV{"PATH"} . ":" . $mysql_path;
21 @dirs = split ( /:/, $path );
22 foreach $dir ( @dirs ) {
24 return $file if ( -x $file );
27 die "Could not find $app executable in:\n$path\n";
35 if ( $in eq "\\N" || length ( $in ) == 0 ) {
46 return "NULL" if ( $in eq "\\N" );
47 return "0" if ( $in eq "" );
54 if ( $in =~ /(\d\d\d\d)-(\d\d)-(\d\d)/ ) {
55 $ret = sprintf "%04d%02d%02d", $1, $2, $3;
59 #print STDERR "Date \"$in\" -> $ret\n";
67 if ( $in =~ /(\d\d):(\d\d):(\d\d)/ ) {
68 $ret = sprintf "%02d%02d%02d", $1, $2, $3;
72 #print STDERR "Time \"$in\" -> $ret\n";
77 $mysql = &find_executable ( "mysql" );
78 $mysqldump = &find_executable ( "mysqldump" );
80 print "mysql found: $mysql\n";
81 print "mysqldump found: $mysqldump\n";
83 # Get a current dump of the db
85 mkdir ( $dir, 0755 ) if ( ! -d $dir );
86 $cmd = "$mysqldump --no-create-info -T $dir $dbname $tables";
87 print "Command: $cmd\n";
90 # use datafiles to create a series of SQL Insert commands
92 open ( OUT, ">$out" ) ||
93 die "Error writing output file: $!\n";
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 )
105 #DROP TABLE webcal_entry;
106 CREATE TABLE webcal_entry (
108 cal_group_id INT NULL,
109 cal_create_by VARCHAR(25) NOT NULL,
110 cal_date INT NOT NULL,
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 )
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)
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 )
138 open ( IN, "$dir/cal_user.txt" );
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";
153 open ( IN, "$dir/cal_entry.txt" );
159 chop ( $nextline = <IN> );
160 $nextline =~ s/[\\\r\n]+$//g;
161 $_ .= "\\n" . $nextline;
163 @fields = split /\t/;
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
187 open ( IN, "$dir/cal_entry_user.txt" );
191 @fields = split /\t/;
192 print OUT "INSERT INTO webcal_entry_user\n" .
193 "( cal_id, cal_login, cal_status )\n" .
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";
203 open ( IN, "$dir/cal_user_pref.txt" );
207 @fields = split /\t/;
208 print OUT "INSERT INTO webcal_user_pref\n" .
209 "( cal_login, cal_setting, cal_value )\n" .
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";