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.
8 require_once dirname(__FILE__).'/../www/env.inc.php';
9 require_once $gfcommon.'include/pre.php';
12 * Sets up CLI environment based on SAPI and PHP version
14 if (version_compare(phpversion(), '4.3.0', '<') || php_sapi_name() == 'cgi') {
15 // Handle output buffering
16 while (@ob_end_flush());
17 ob_implicit_flush(TRUE);
21 ini_set('track_errors', TRUE);
22 ini_set('html_errors', FALSE);
23 ini_set('magic_quotes_runtime', FALSE);
25 // Define stream constants
26 define('STDIN', fopen('php://stdin', 'r'));
27 define('STDOUT', fopen('php://stdout', 'w'));
28 define('STDERR', fopen('php://stderr', 'w'));
30 // Close the streams on script termination
31 register_shutdown_function(
33 'fclose(STDIN); fclose(STDOUT); fclose(STDERR); return true;')
37 $db_path = dirname(__FILE__).'/';
41 // a huge message will warn not to run it without doing a backup first // warning.
43 // Check if table 'database_startpoint' exists
44 $res = db_query_params ('SELECT COUNT(*) AS proceed FROM pg_class WHERE relname = $1 AND relkind = $2',
45 array('database_startpoint',
48 if (!$res) { // db error
49 show("DB-ERROR-2: ".db_error()."\n");
52 $proceed = db_result($res, 0, 'proceed');
53 if (!$proceed) { // table does not exist
54 show("ERROR: table 'database_startpoint' does not exist.\nRun startpoint.php first.\n");
57 // Check if table 'database_startpoint' has proper values
58 $res = db_query_params ('SELECT * FROM database_startpoint',
61 if (!$res) { // db error
62 show("DB-ERROR-3: ".db_error()."\n");
64 } else if (db_numrows($res) == 0) { // table 'database_startpoint' is empty
65 show("ERROR: table 'database_startpoint' is empty.\nRun startpoint.php first.\n");
67 } else { // get the start date from the db
68 $date = (int) db_result($res, 0, 'db_start_date');
69 $version = db_result($res, 0, 'db_version');
74 if (!apply_fixes($version)) {
75 show("ERROR applying fixes to version $version!\n");
79 // Upgrade main database if no argument or if all)
80 if ($argc == 1 || $argv[1] == 'all') {
81 $scripts = get_scripts($db_path);
82 foreach ($scripts as $script) {
83 if ((int) $script['date'] > $date) {
84 $res = db_query_params ('SELECT * FROM database_changes WHERE filename=$1',
85 array ("{$script['filename']}")) ;
88 show("ERROR-2: ".db_error()."\n");
90 } else if (db_numrows($res) == 0) {
91 show("Running script: {$script['filename']}\n");
92 $result = run_script($script);
94 $res = db_query_params ('INSERT INTO database_changes (filename) VALUES ($1)',
95 array ("{$script['filename']}")) ;
98 show("ERROR-3: ".db_error()."\n");
106 // show("Skipping script: {$script['filename']}\n");
112 // Upgrade activated plugins.
114 require_once $gfcommon.'include/DatabaseInstaller.class.php';
115 $plugins = get_installed_plugins();
116 foreach ($plugins as $plugin) {
117 if ($argv[1] == 'all' || $argv[1] == $plugin) {
118 $di = new DatabaseInstaller($plugin, dirname($db_path) . '/plugins/' . $plugin . '/db');
124 function get_installed_plugins() {
126 $res = db_query_params ('SELECT plugin_name FROM plugins', array ());
127 while ($row = db_fetch_array($res)) {
128 $plugins[] = $row['plugin_name'];
133 function get_scripts($dir) {
136 if ($dh = opendir($dir)) {
137 while (($file = readdir($dh)) !== false) {
138 $pos = strrpos($file, '.');
139 if ($pos !== false && $pos > 0) {
140 $name = substr($file, 0, $pos);
141 if (strlen($name) >= 8) {
142 $date_aux = substr($name, 0, 8);
143 $type_aux = substr($file, $pos + 1);
144 if ((int) $date_aux > 20000000 && ($type_aux=='sql' || $type_aux=='php') && strpos($file, 'debian') === false) {
145 $data[] = array('date'=>$date_aux, 'filename'=>$file, 'ext'=>$type_aux);
152 usort($data, 'compare_scripts');
159 function compare_scripts($script1, $script2) {
160 return strcmp($script1['filename'], $script2['filename']);
163 function run_script($script) {
167 $ext = strtolower($script['ext']);
168 $filename = $script['filename'];
170 // run the php script
172 $exec = 'php -f '.$db_path.$filename;
173 exec($exec, $result);
175 if (count($result)) { // the script produced an output
176 if ($result[count($result)-1] == 'SUCCESS') {
177 show($db_path.$filename." ran correctly\n\n");
180 show($db_path.$filename." FAILED!\n\n");
181 foreach ($result as $line) {
186 show($db_path.$filename." FAILED!\n\n");
189 } else if ($ext == 'sql') {
190 if (//$filename == '20021124-3_gforge-debian-sf-sync.sql' ||
191 $filename == '20021223-drops.sql') {
193 echo "\nskipping $filename";
196 // run the sql script
198 if (run_sql_script($filename)) {
199 show($db_path.$filename." ran correctly\n\n");
202 show($db_path.$filename." FAILED!\n\n");
206 // something went wrong
207 show("\nThe script is not a PHP file nor an SQL file. Something went wrong. Please report this bug\n");
212 function run_sql_script($filename) {
215 $sql_file = $db_path.$filename;
216 $file = @fopen($sql_file, 'rb');
220 $content = fread($file, filesize($sql_file));
223 $content = preg_replace("/--(.*)/", '', $content);
225 $parts = explode(";\n", $content);
228 $is_function = false;
229 $is_copy_stdin = false;
231 for ($i=0;$i<count($parts);$i++) {
233 // Check if it's a function
234 if ((in_string($q, 'create function') || in_string($q, 'create or replace function') ||
235 in_string($q, 'replace function')) && !in_string($q, 'language plpgsql') &&
236 !in_string($q, 'language \'plpgsql\'')&& !in_string($q, 'language \'c\'') &&
237 !in_string($q, 'language c')) {
239 while (!in_string($q, 'language plpgsql') && !in_string($q, 'language \'plpgsql\'')) {
241 $q = $q.';'.$parts[$i];
243 $queries[] = trim($q);
244 // Check if it is a COPY FROM stdin
245 } else if (in_string($q, 'copy') && in_string($q, 'from stdin')) {
246 while (!in_string($q, '\.')) {
248 $q = $q.";\n".$parts[$i];
250 $aux = explode('\.', $q, 2);
251 $queries[] = ltrim($aux[0]."\\.\n");
252 if (trim($aux[1]) != '') {
253 $queries[] = trim($aux[1]);
255 // Else, we just add it up
257 if (trim($q) != '') {
258 $queries[] = trim(preg_replace("/\s+/", ' ', str_replace("\n", ' ', $q)));
267 foreach ($queries as $query) {
268 // Check if it is a DROP TABLE
269 if (in_string($query, 'drop table')) {
270 $aux = explode(' ', trim($query));
271 if (count($aux) == 3 || count($aux) == 4) { // PERFECT!
272 drop_table_if_exists($aux[2], (count($aux) == 4) && preg_match('/CASCADE/i', trim($aux[3])));
276 // Check if it is a DROP SEQUENCE
277 } else if (in_string($query, 'drop sequence')) {
278 $aux = explode(' ', trim($query));
279 if (count($aux) == 3) { // PERFECT!
280 drop_seq_if_exists($aux[2]);
284 // Check if it is a DROP TRIGGER
285 } else if (in_string($query, 'drop trigger')) {
286 $aux = explode(' ', trim($query));
287 if (count($aux) == 5 || count($aux) == 6) { // PERFECT!
288 drop_trigger_if_exists($aux[2], $aux[4]);
292 // Check if it is a DROP VIEW
293 } else if (in_string($query, 'drop view')) {
294 $aux = explode(' ', trim($query));
295 if (count($aux) == 3 || count($aux) == 4) { // PERFECT!
296 drop_view_if_exists($aux[2]);
300 // Check if it is a DROP INDEX
301 } else if (in_string($query, 'drop index')) {
302 $aux = explode(' ', trim($query));
303 if (count($aux) == 3 || count($aux) == 4) { // PERFECT!
304 drop_index_if_exists($aux[2]);
308 // Check if it is a DROP CONSTRAINT
309 } else if (in_string($query, 'alter table') && in_string($query, 'drop constraint')) {
310 $aux = explode(' ', trim($query));
311 $table = trim($aux[2], "\" ");
312 $constraint = trim($aux[5], "\" ");
314 drop_constraint_if_exists($table, $constraint, $query);
316 $res = db_query($query);
318 show(db_error()."\n");
319 show("QUERY: $query\n");
320 show("Continue executing ([Y]es/[N]o)?\n");
322 $answer = strtolower(trim(fgets(STDIN)));
323 if ($answer != 'y' && $answer != 'yes') {
334 // Patch for some 3.0preX versions
335 if ($filename == '20021216.sql') {
336 db_query_params ('SELECT setval($1, (SELECT MAX(theme_id) FROM themes), true)',
337 array('themes_theme_id_key')) ;
339 show("Applying fix for some 3.0preX versions\n");
346 function apply_fixes($version) {
348 if ($version == 'sfee3.3') {
349 $res = db_query_params('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1', array ('sfee3.3fixes')) ;
350 if ($res && db_result($res, 0, 'applied') == '0') {
351 show("Converting SFEE3.3 to SFEE3.0\n");
352 run_script(array('filename'=>'sfee3.3-3.0-1.sql','ext'=>'sql'));
353 run_script(array('filename'=>'sfee3.3-3.0-2.php','ext'=>'php'));
354 run_script(array('filename'=>'sfee3.3-3.0-3.sql','ext'=>'sql'));
355 show("Converting SFEE3.0 to SF2.6\n");
357 run_script(array('filename'=>'sfee3.0-sf26-1.sql','ext'=>'sql'));
358 run_script(array('filename'=>'sfee3.0-sf26-2.php','ext'=>'php'));
359 $queries[] = "INSERT INTO database_changes (filename) VALUES ('sfee3.3fixes')";
361 } else if ($version == 'sfee3.0') {
362 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1',
363 array ('sfee3.0fixes')) ;
364 if ($res && db_result($res, 0, 'applied') == '0') {
365 run_script(array('filename'=>'sfee3.0-sf26-1.sql','ext'=>'sql'));
366 run_script(array('filename'=>'sfee3.0-sf26-2.php','ext'=>'php'));
367 $queries[] = "INSERT INTO database_changes (filename) VALUES ('sfee3.0fixes')";
369 } else if ($version == '2.5') {
370 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1',
372 if ($res && db_result($res, 0, 'applied') == '0') {
373 show("Applying fixes for version 2.5\n");
374 $queries[] = "ALTER TABLE project_task ADD CONSTRAINT project_task_group_project_id_f CHECK (1 = 1)";
375 $queries[] = "INSERT INTO database_changes (filename) VALUES ('2.5fixes')";
377 } else if ($version == '2.6') {
378 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1',
379 array ('2.6fixes')) ;
380 if ($res && db_result($res, 0, 'applied') == '0') {
381 show("Applying fixes for version 2.6\n");
382 $queries[] = "ALTER TABLE project_task ADD CONSTRAINT project_task_group_project_id_f CHECK (1 = 1)";
383 $queries[] = "INSERT INTO database_changes (filename) VALUES ('2.6fixes')";
385 } else if ($version == '3.0pre5') {
386 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1',
387 array ('3.0pre5fixes')) ;
388 if ($res && db_result($res, 0, 'applied') == '0') {
389 show("Applying fixes for version 3.0pre5\n");
390 if (!run_sql_script('fix-gforge3.0pre5.sql')) {
391 show("Error applying fixes for version 3.0pre5\n");
394 $queries[] = "INSERT INTO database_changes (filename) VALUES ('3.0pre5fixes')";
396 } else if ($version == '3.0pre6') {
397 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1', array ('3.0pre6fixes')) ;
398 if ($res && db_result($res, 0, 'applied') == '0') {
399 show("Applying fixes for version 3.0pre6\n");
400 $queries[] = "ALTER TABLE project_task ADD CONSTRAINT project_task_group_project_id_f CHECK (1 = 1)";
401 $queries[] = "INSERT INTO database_changes (filename) VALUES ('3.0pre6fixes')";
403 } else if ($version == '3.0pre7') {
404 $res = db_query_params ('SELECT COUNT(*) AS applied FROM database_changes WHERE filename=$1',
405 array ('3.0pre7fixes')) ;
406 if ($res && db_result($res, 0, 'applied') == '0') {
407 show("Applying fixes for version 3.0pre7\n");
408 $queries[] = "ALTER TABLE project_task ADD CONSTRAINT project_task_group_project_id_f CHECK (1 = 1)";
409 $queries[] = "INSERT INTO database_changes (filename) VALUES ('3.0pre7fixes')";
411 } else if ($version == '4.7') {
412 run_script(array('filename'=>'20070924-project-perm.sql','ext'=>'sql'));
413 run_script(array('filename'=>'20070924-forum-perm.sql','ext'=>'sql'));
414 run_script(array('filename'=>'20070924-artifact-perm.sql','ext'=>'sql'));
418 foreach ($queries as $query) {
419 $res = db_query($query);
421 show("ERROR: ".db_error()."\n");
430 function drop_view_if_exists($name) {
431 $result = drop_if_exists($name, 'DROP VIEW', 'v');
435 function drop_seq_if_exists($name) {
436 $result = drop_if_exists($name, 'DROP SEQUENCE', 'S');
440 function drop_index_if_exists($name) {
441 $result = drop_if_exists($name, 'DROP INDEX', 'i');
445 function drop_table_if_exists($name, $cascade) {
447 $result = drop_if_exists($name, 'DROP TABLE', 'r', 'CASCADE');
449 $result = drop_if_exists($name, 'DROP TABLE', 'r');
454 function drop_if_exists($name, $command, $kind, $commandSuffix = '') {
455 // Strip "name" => name
456 if (preg_match('/^"(.*)"$/', $name, $match)) {
459 $res = db_query_params ('SELECT COUNT(*) AS exists FROM pg_class WHERE relname=$1 AND relkind=$2',
463 show("ERROR:".db_error()."\n");
466 if (db_result($res, 0, 'exists') != '0') {
467 $res = db_query("$command $name $commandSuffix");
469 show("ERROR:".db_error()."\n");
477 function drop_constraint_if_exists($table, $constraint, $query) {
478 $res = db_query_params ('SELECT COUNT(*) AS exists FROM information_schema.constraint_table_usage WHERE table_name=$1 AND constraint_name=$2',
483 show("ERROR:".db_error()."\n");
486 if (db_result($res, 0, 'exists') != '0') {
487 $res = db_query($query);
489 show("ERROR:".db_error()."\n");
495 function drop_trigger_if_exists($name, $on) {
496 $res = db_query_params ('SELECT COUNT(*) AS exists FROM pg_trigger WHERE tgname=$1',
499 show("ERROR:".db_error()."\n");
502 if (db_result($res, 0, 'exists') != '0') {
503 $res = db_query("DROP TRIGGER $name ON $on");
505 show("ERROR:".db_error()."\n");
513 function in_string($haystack, $needle, $case_sensitive = false) {
514 if (!$case_sensitive) {
515 $haystack = strtolower($haystack);
517 if (strpos($haystack, $needle) !== false) {
524 function show($text) {
526 fwrite(STDOUT, $text);
531 // c-file-style: "bsd"