4 // upgrade-db.php => Upgrade the main database.
5 // upgrade-db.php all => Upgrade the main database and active plugins.
6 // upgrade-db.php <plugin> => Upgrade only the database of the given active plugin.
7 echo "Entering upgrade-db.php\n";
9 require_once dirname(__FILE__).'/../www/env.inc.php';
10 require_once $gfcommon.'include/pre.php';
12 $db_path = dirname(__FILE__).'/';
16 // a huge message will warn not to run it without doing a backup first // warning.
18 // Check if table 'database_startpoint' exists
19 $res = db_query_params('SELECT COUNT(*) AS proceed FROM pg_class WHERE relname = $1 AND relkind = $2',
20 array('database_startpoint',
23 if (!$res) { // db error
24 show("DB-ERROR-2: ".db_error()."\n");
27 $proceed = db_result($res, 0, 'proceed');
28 if (!$proceed) { // table does not exist
29 show("ERROR: table 'database_startpoint' does not exist.\nRun startpoint.php first.\n");
32 // Check if table 'database_startpoint' has proper values
33 $res = db_query_params('SELECT * FROM database_startpoint',
36 if (!$res) { // db error
37 show("DB-ERROR-3: ".db_error()."\n");
39 } else if (db_numrows($res) == 0) { // table 'database_startpoint' is empty
40 show("ERROR: table 'database_startpoint' is empty.\nRun startpoint.php first.\n");
42 } else { // get the start date from the db
43 $date = (int) db_result($res, 0, 'db_start_date');
44 $version = db_result($res, 0, 'db_version');
49 if (!apply_fixes($version)) {
50 show("ERROR applying fixes to version $version!\n");
54 // Upgrade main database if no argument or if all)
55 if ($argc == 1 || $argv[1] == 'all') {
56 $scripts = get_scripts($db_path);
57 foreach ($scripts as $script) {
58 if ((int) $script['date'] > $date) {
59 $res = db_query_params('SELECT * FROM database_changes WHERE filename=$1',
60 array ("{$script['filename']}"));
63 show("ERROR-2: ".db_error()."\n");
65 } else if (db_numrows($res) == 0) {
66 show("Running script: {$script['filename']}\n");
67 $result = run_script($script);
69 $res = db_query_params ('INSERT INTO database_changes (filename) VALUES ($1)',
70 array ("{$script['filename']}")) ;
73 show("ERROR-3: ".db_error()."\n");
81 // show("Skipping script: {$script['filename']}\n");
87 // Upgrade activated plugins.
89 require_once $gfcommon.'include/DatabaseInstaller.class.php';
90 $plugins = get_installed_plugins();
91 foreach ($plugins as $plugin) {
92 if ($argv[1] == 'all' || $argv[1] == $plugin) {
93 $di = new DatabaseInstaller($plugin, dirname($db_path) . '/plugins/' . $plugin . '/db');
99 function get_installed_plugins() {
101 $res = db_query_params ('SELECT plugin_name FROM plugins', array ());
102 while ($row = db_fetch_array($res)) {
103 $plugins[] = $row['plugin_name'];
108 function get_scripts($dir) {
111 if ($dh = opendir($dir)) {
112 while (($file = readdir($dh)) !== false) {
113 $pos = strrpos($file, '.');
114 if ($pos !== false && $pos > 0) {
115 $name = substr($file, 0, $pos);
116 if (strlen($name) >= 8) {
117 $date_aux = substr($name, 0, 8);
118 $type_aux = substr($file, $pos + 1);
119 if ((int) $date_aux > 20000000 && ($type_aux=='sql' || $type_aux=='php') && strpos($file, 'debian') === false) {
120 $data[] = array('date'=>$date_aux, 'filename'=>$file, 'ext'=>$type_aux);
127 usort($data, 'compare_scripts');
134 function compare_scripts($script1, $script2) {
135 return strcmp($script1['filename'], $script2['filename']);
138 function run_script($script) {
142 $ext = strtolower($script['ext']);
143 $filename = $script['filename'];
145 // run the php script
147 $exec = 'php -f '.$db_path.$filename;
148 exec($exec, $result);
150 if (count($result)) { // the script produced an output
151 if ($result[count($result)-1] == 'SUCCESS') {
152 show($db_path.$filename." ran correctly\n\n");
155 show($db_path.$filename." FAILED!\n\n");
156 foreach ($result as $line) {
161 show($db_path.$filename." FAILED!\n\n");
164 } else if ($ext == 'sql') {
165 if (//$filename == '20021124-3_gforge-debian-sf-sync.sql' ||
166 $filename == '20021223-drops.sql') {
168 echo "\nskipping $filename";
171 // run the sql script
173 if (run_sql_script($filename)) {
174 show($db_path.$filename." ran correctly\n\n");
177 show($db_path.$filename." FAILED!\n\n");
181 // something went wrong
182 show("\nThe script is not a PHP file nor an SQL file. Something went wrong. Please report this bug\n");
187 function run_sql_script($filename) {
190 $sql_file = $db_path.$filename;
191 $file = @fopen($sql_file, 'rb');
195 $content = fread($file, filesize($sql_file));
198 $content = preg_replace("/--(.*)/", '', $content);
200 $parts = explode(";\n", $content);
203 $is_function = false;
204 $is_copy_stdin = false;
206 for ($i=0;$i<count($parts);$i++) {
208 // Check if it's a function
209 if ((in_string($q, 'create function') || in_string($q, 'create or replace function') ||
210 in_string($q, 'replace function')) && !in_string($q, 'language plpgsql') &&
211 !in_string($q, 'language \'plpgsql\'')&& !in_string($q, 'language \'c\'') &&
212 !in_string($q, 'language c')) {
214 while (!in_string($q, 'language plpgsql') && !in_string($q, 'language \'plpgsql\'')) {
216 $q = $q.';'.$parts[$i];
218 $queries[] = trim($q);
219 // Check if it is a COPY FROM stdin
220 } else if (in_string($q, 'copy') && in_string($q, 'from stdin')) {
221 while (!in_string($q, '\.')) {
223 $q = $q.";\n".$parts[$i];
225 $aux = explode('\.', $q, 2);
226 $queries[] = ltrim($aux[0]."\\.\n");
227 if (trim($aux[1]) != '') {
228 $queries[] = trim($aux[1]);
230 // Else, we just add it up
232 if (trim($q) != '') {
233 $queries[] = trim(preg_replace("/\s+/", ' ', str_replace("\n", ' ', $q)));
242 foreach ($queries as $query) {
243 // Check if it is a DROP TABLE
244 if (in_string($query, 'drop table')) {
245 $aux = explode(' ', trim($query));
246 if (count($aux) == 3 || count($aux) == 4) { // PERFECT!
247 drop_table_if_exists($aux[2], (count($aux) == 4) && preg_match('/CASCADE/i', trim($aux[3])));
251 // Check if it is a DROP SEQUENCE
252 } else if (in_string($query, 'drop sequence')) {
253 $aux = explode(' ', trim($query));
254 if (count($aux) == 3) { // PERFECT!
255 drop_seq_if_exists($aux[2]);
259 // Check if it is a DROP TRIGGER
260 } else if (in_string($query, 'drop trigger')) {
261 $aux = explode(' ', trim($query));
262 if (count($aux) == 5 || count($aux) == 6) { // PERFECT!
263 drop_trigger_if_exists($aux[2], $aux[4]);
267 // Check if it is a DROP VIEW
268 } else if (in_string($query, 'drop view')) {
269 $aux = explode(' ', trim($query));
270 if (count($aux) == 3 || count($aux) == 4) { // PERFECT!
271 drop_view_if_exists($aux[2]);
275 // Check if it is a DROP INDEX
276 } else if (in_string($query, 'drop index')) {
277 $aux = explode(' ', trim($query));
278 if (count($aux) == 3 || count($aux) == 4) { // PERFECT!
279 drop_index_if_exists($aux[2]);
283 // Check if it is a DROP CONSTRAINT
284 } else if (in_string($query, 'alter table') && in_string($query, 'drop constraint')) {
285 $aux = explode(' ', trim($query));
286 $table = trim($aux[2], "\" ");
287 $constraint = trim($aux[5], "\" ");
289 drop_constraint_if_exists($table, $constraint, $query);
291 $res = db_query_params($query, array());
293 show(db_error()."\n");
294 show("QUERY: $query\n");
295 show("Continue executing ([Y]es/[N]o)?\n");
297 $answer = strtolower(trim(fgets(STDIN)));
298 if ($answer != 'y' && $answer != 'yes') {
309 // Patch for some 3.0preX versions
310 if ($filename == '20021216.sql') {
311 db_query_params ('SELECT setval($1, (SELECT MAX(theme_id) FROM themes), true)',
312 array('themes_theme_id_key')) ;
314 show("Applying fix for some 3.0preX versions\n");
321 function apply_fixes($version) {
323 if ($version == 'sfee3.3') {
324 $res = db_query_params('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1', array ('sfee3.3fixes')) ;
325 if ($res && db_result($res, 0, 'applied') == '0') {
326 show("Converting SFEE3.3 to SFEE3.0\n");
327 run_script(array('filename'=>'sfee3.3-3.0-1.sql','ext'=>'sql'));
328 run_script(array('filename'=>'sfee3.3-3.0-2.php','ext'=>'php'));
329 run_script(array('filename'=>'sfee3.3-3.0-3.sql','ext'=>'sql'));
330 show("Converting SFEE3.0 to SF2.6\n");
332 run_script(array('filename'=>'sfee3.0-sf26-1.sql','ext'=>'sql'));
333 run_script(array('filename'=>'sfee3.0-sf26-2.php','ext'=>'php'));
334 $queries[] = "INSERT INTO database_changes (filename) VALUES ('sfee3.3fixes')";
336 } else if ($version == 'sfee3.0') {
337 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1',
338 array ('sfee3.0fixes')) ;
339 if ($res && db_result($res, 0, 'applied') == '0') {
340 run_script(array('filename'=>'sfee3.0-sf26-1.sql','ext'=>'sql'));
341 run_script(array('filename'=>'sfee3.0-sf26-2.php','ext'=>'php'));
342 $queries[] = "INSERT INTO database_changes (filename) VALUES ('sfee3.0fixes')";
344 } else if ($version == '2.5') {
345 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1',
347 if ($res && db_result($res, 0, 'applied') == '0') {
348 show("Applying fixes for version 2.5\n");
349 $queries[] = "ALTER TABLE project_task ADD CONSTRAINT project_task_group_project_id_f CHECK (1 = 1)";
350 $queries[] = "INSERT INTO database_changes (filename) VALUES ('2.5fixes')";
352 } else if ($version == '2.6') {
353 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1',
354 array ('2.6fixes')) ;
355 if ($res && db_result($res, 0, 'applied') == '0') {
356 show("Applying fixes for version 2.6\n");
357 $queries[] = "ALTER TABLE project_task ADD CONSTRAINT project_task_group_project_id_f CHECK (1 = 1)";
358 $queries[] = "INSERT INTO database_changes (filename) VALUES ('2.6fixes')";
360 } else if ($version == '3.0pre5') {
361 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1',
362 array ('3.0pre5fixes')) ;
363 if ($res && db_result($res, 0, 'applied') == '0') {
364 show("Applying fixes for version 3.0pre5\n");
365 if (!run_sql_script('fix-gforge3.0pre5.sql')) {
366 show("Error applying fixes for version 3.0pre5\n");
369 $queries[] = "INSERT INTO database_changes (filename) VALUES ('3.0pre5fixes')";
371 } else if ($version == '3.0pre6') {
372 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1', array ('3.0pre6fixes')) ;
373 if ($res && db_result($res, 0, 'applied') == '0') {
374 show("Applying fixes for version 3.0pre6\n");
375 $queries[] = "ALTER TABLE project_task ADD CONSTRAINT project_task_group_project_id_f CHECK (1 = 1)";
376 $queries[] = "INSERT INTO database_changes (filename) VALUES ('3.0pre6fixes')";
378 } else if ($version == '3.0pre7') {
379 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1',
380 array ('3.0pre7fixes')) ;
381 if ($res && db_result($res, 0, 'applied') == '0') {
382 show("Applying fixes for version 3.0pre7\n");
383 $queries[] = "ALTER TABLE project_task ADD CONSTRAINT project_task_group_project_id_f CHECK (1 = 1)";
384 $queries[] = "INSERT INTO database_changes (filename) VALUES ('3.0pre7fixes')";
386 } else if ($version == '4.7') {
387 run_script(array('filename'=>'20070924-project-perm.sql','ext'=>'sql'));
388 run_script(array('filename'=>'20070924-forum-perm.sql','ext'=>'sql'));
389 run_script(array('filename'=>'20070924-artifact-perm.sql','ext'=>'sql'));
393 foreach ($queries as $query) {
394 $res = db_query_params($query, array());
396 show("ERROR: ".db_error()."\n");
405 function drop_view_if_exists($name) {
406 $result = drop_if_exists($name, 'DROP VIEW', 'v');
410 function drop_seq_if_exists($name) {
411 $result = drop_if_exists($name, 'DROP SEQUENCE', 'S');
415 function drop_index_if_exists($name) {
416 $result = drop_if_exists($name, 'DROP INDEX', 'i');
420 function drop_table_if_exists($name, $cascade) {
422 $result = drop_if_exists($name, 'DROP TABLE', 'r', 'CASCADE');
424 $result = drop_if_exists($name, 'DROP TABLE', 'r');
429 function drop_if_exists($name, $command, $kind, $commandSuffix = '') {
430 // Strip "name" => name
431 if (preg_match('/^"(.*)"$/', $name, $match)) {
434 $res = db_query_params('SELECT COUNT(*) AS exists FROM pg_class WHERE relname=$1 AND relkind=$2',
438 show("ERROR:".db_error()."\n");
441 if (db_result($res, 0, 'exists') != '0') {
442 $res = db_query_params("$command $name $commandSuffix", array());
444 show("ERROR:".db_error()."\n");
452 function drop_constraint_if_exists($table, $constraint, $query) {
453 $res = db_query_params('SELECT COUNT(*) AS exists FROM information_schema.constraint_table_usage WHERE table_name=$1 AND constraint_name=$2',
458 show("ERROR:".db_error()."\n");
461 if (db_result($res, 0, 'exists') != '0') {
462 $res = db_query_params($query, array());
464 show("ERROR:".db_error()."\n");
470 function drop_trigger_if_exists($name, $on) {
471 $res = db_query_params('SELECT COUNT(*) AS exists FROM pg_trigger WHERE tgname=$1',
474 show("ERROR:".db_error()."\n");
477 if (db_result($res, 0, 'exists') != '0') {
478 $res = db_query_params("DROP TRIGGER $1 ON $2", array($name, $on));
480 show("ERROR:".db_error()."\n");
488 function in_string($haystack, $needle, $case_sensitive = false) {
489 if (!$case_sensitive) {
490 $haystack = strtolower($haystack);
492 if (strpos($haystack, $needle) !== false) {
499 function show($text) {
501 fwrite(STDOUT, $text);
506 // c-file-style: "bsd"