<?php include_once($INC . "/sql.inc.php"); include_once($INC . "/state.inc.php"); include_once($INC . "/util.inc.php"); define("RG_DROP_TABLES", 1); define("RG_IGNORE_ERRORS", 1 << 1); $rg_sql_struct_slaves = array(); $rg_sql_struct = array(); $rg_sql_struct[1] = array(); $rg_sql_struct[1]['tables'] = array( "repos" => "CREATE TABLE repos" . " (repo_id SERIAL PRIMARY KEY" . ", name TEXT UNIQUE NOT NULL" . ", uid INTEGER NOT NULL" . ", itime INTEGER NOT NULL" . ", disk_quota_mb INTEGER DEFAULT 0" . ", disk_used_mb INTEGER NOT NULL DEFAULT 0" . ", max_commit_size INTEGER DEFAULT 0" . ", master INTEGER NOT NULL DEFAULT 0" . ", description TEXT NOT NULL DEFAULT ''" . ", git_dir_done INTEGER NOT NULL DEFAULT 0" . ", default_rights TEXT NOT NULL DEFAULT ''" . ", deleted INTEGER NOT NULL DEFAULT 0" . ", max_users INTEGER NOT NULL DEFAULT 0" . ")", "rights" => "CREATE TABLE rights" . " (type TEXT NOT NULL" . ", obj_id INTEGER NOT NULL" . ", uid INTEGER NOT NULL" . ", rights TEXT NOT NULL" . ", itime INTEGER NOT NULL)", "state" => "CREATE TABLE state" . " (var TEXT PRIMARY KEY" . ", value TEXT NOT NULL)", "keys" => "CREATE TABLE keys" . " (key_id SERIAL PRIMARY KEY" . ", itime INTEGER NOT NULL" . ", uid INTEGER NOT NULL" . ", key TEXT UNIQUE NOT NULL)", "users" => "CREATE TABLE users" . " (uid SERIAL PRIMARY KEY" . ", username TEXT UNIQUE NOT NULL" . ", realname TEXT NOT NULL" . ", salt TEXT NOT NULL" . ", pass TEXT NOT NULL" . ", email TEXT NOT NULL" . ", itime INTEGER NOT NULL" . ", suspended INTEGER NOT NULL DEFAULT 0" . ", session_time INTEGER NOT NULL DEFAULT 3600" . ", last_seen INTEGER NOT NULL DEFAULT 0" . ", is_admin INTEGER NOT NULL DEFAULT 0" . ", disk_quota_mb INTEGER NOT NULL DEFAULT 0" . ", disk_used_mb INTEGER NOT NULL DEFAULT 0" . ", rights TEXT NOT NULL" . ", confirmed INTEGER NOT NULL DEFAULT 0" . ", confirm_token TEXT NOT NULL DEFAULT ''" . ")", "sess" => "CREATE TABLE sess" . " (sid TEXT PRIMARY KEY" . ", uid INTEGER NOT NULL" . ", expire INTEGER NOT NULL" . ", session_time INTEGER NOT NULL" . ", ip TEXT NOT NULL)", "forgot_pass" => "CREATE TABLE forgot_pass" . " (token TEXT PRIMARY KEY" . ", uid INTEGER NOT NULL" . ", expire INTEGER NOT NULL)", "tokens" => "CREATE TABLE tokens" . " (token TEXT PRIMARY KEY" . ", sid TEXT NOT NULL" . ", expire INTEGER NOT NULL)" ); $rg_sql_struct[1]['other'] = array(); $rg_sql_struct[2] = array(); $rg_sql_struct[2]['tables'] = array( "suggestions" => "CREATE TABLE suggestions" . " (suggestion_id SERIAL PRIMARY KEY" . ", uid INTEGER NOT NULL" . ", email TEXT NOT NULL" . ", suggestion TEXT NOT NULL)" ); $rg_sql_struct[2]['other'] = array(); $rg_sql_struct[3] = array(); $rg_sql_struct[3]['tables'] = array(); $rg_sql_struct[3]['other'] = array( "add organization field to user" => "ALTER TABLE users" . " ADD organization SMALLINT NOT NULL DEFAULT 0" ); $rg_sql_struct[4] = array(); $rg_sql_struct[4]['tables'] = array( "merge_requests" => "CREATE TABLE merge_requests (" . "repo_id INT NOT NULL" . ", itime INT NOT NULL" . ", namespace TEXT NOT NULL" . ", refname TEXT NOT NULL" . ", old_rev CHAR(40) NOT NULL" . ", new_rev CHAR(40) NOT NULL" . ", done INT NOT NULL DEFAULT 0" . ", ip TEXT NOT NULL" . ")" ); $rg_sql_struct[4]['other'] = array( "merge_request_index_repo_id" => "CREATE INDEX merge_requests_i_repo_id" . " ON merge_requests (repo_id)" ); $rg_sql_struct[5] = array(); $rg_sql_struct[5]['tables'] = array( "history_push" => "CREATE TABLE history_push (" . "itime INT NOT NULL" . ", repo_id INT NOT NULL" . ", ip TEXT NOT NULL" . ", refname TEXT NOT NULL" . ", old_rev CHAR(40) NOT NULL" . ", new_rev CHAR(40) NOT NULL" . ")" ); $rg_sql_struct[6] = array(); $rg_sql_struct[6]['tables'] = array( "bugs_max" => "CREATE TABLE bugs_max (" . "repo_id INT NOT NULL PRIMARY KEY" . ", last_bug_id INT NOT NULL" . ")", "bugs" => "CREATE TABLE bugs (" . "repo_id INT NOT NULL" . ", bug_id INT NOT NULL" . ", itime INT NOT NULL" . ", utime INT NOT NULL" . ", uid INT NOT NULL" . ", ip TEXT NOT NULL" . ", title TEXT NOT NULL" . ", body TEXT NOT NULL" . ", state SMALLINT NOT NULL" . ", assigned_uid INT NOT NULL" . ", deleted INT NOT NULL" . ")", "bug_notes" => "CREATE TABLE bug_notes (" . "repo_id INT NOT NULL" . ", bug_id INT NOT NULL" . ", note TEXT NOT NULL" . ", itime INT NOT NULL" . ", uid INT NOT NULL" . ", ip TEXT NOT NULL" . ")", "bug_labels" => "CREATE TABLE bug_labels (" . "repo_id INT NOT NULL" . ", bug_id INT NOT NULL" . ", label TEXT NOT NULL" . ")" ); $rg_sql_struct[6]['other'] = array( "bugs_index_repo_id_bug_id" => "CREATE UNIQUE INDEX bugs_i_repo_id_bug_id" . " ON bugs (repo_id, bug_id)", "bugs_index_itime" => "CREATE INDEX bugs_i_itime" . " ON bugs (itime)", "bug_notes_index_repo_id_bug_id" => "CREATE INDEX bug_notes_i_repo_id_bug_id" . " ON bug_notes (repo_id, bug_id)", "bug_labels_index_repo_id_bug_id" => "CREATE INDEX bug_labels_i_repo_id_bug_id" . " ON bug_labels (repo_id, bug_id)" ); $rg_sql_struct[7] = array(); $rg_sql_struct[7]['tables'] = array( "bug_search" => "CREATE TABLE bug_search (" . "repo_id INT NOT NULL" . ", uid INT NOT NULL" . ", name TEXT NOT NULL" . ", data TEXT NOT NULL" . ", for_all_users SMALLINT NOT NULL" . ")" ); $rg_sql_struct[7]['other'] = array( "bug_search_repo_id_uid" => "CREATE INDEX bug_search_i_repo_id_uid" . " ON bug_search(repo_id, uid)" ); $rg_sql_struct[8] = array(); $rg_sql_struct[8]['tables'] = array(); $rg_sql_struct[8]['other'] = array( "more info for keys: last_use" => "ALTER TABLE keys" . " ADD last_use INT NOT NULL DEFAULT 0", "more info for keys: last_ip" => "ALTER TABLE keys" . " ADD last_ip TEXT NOT NULL DEFAULT ''" ); $rg_sql_struct[9] = array(); $rg_sql_struct[9]['tables'] = array(); $rg_sql_struct[9]['other'] = array( "allow duplicate ssh keys" => "ALTER TABLE keys" . " DROP CONSTRAINT IF EXISTS keys_pkey", "allow duplicate ssh keys2" => "ALTER TABLE keys" . " DROP CONSTRAINT IF EXISTS keys_key_key", "index on key_id" => "CREATE INDEX keys_i_key_id" . " ON keys(key_id)", "allow duplicate repos names" => "ALTER TABLE repos" . " DROP CONSTRAINT IF EXISTS repos_pkey", "allow duplicate repos names2" => "ALTER TABLE repos" . " DROP CONSTRAINT IF EXISTS repos_name_key", "index on repos names" => "CREATE INDEX repos_i_name" . " ON repos(name)" ); $rg_sql_struct[10] = array(); $rg_sql_struct[10]['tables'] = array( "repo_history" => "CREATE TABLE repo_history (" . "itime INT NOT NULL" . ", repo_id INT NOT NULL" . ", category SMALLINT NOT NULL" . ", message TEXT NOT NULL)" ); $rg_sql_struct[10]['other'] = array(); $rg_sql_struct_slaves['repo_history'] = "repo_history"; $rg_sql_struct[11] = array(); $rg_sql_struct[11]['tables'] = array( "events" => "CREATE TABLE events (" . "id BIGSERIAL PRIMARY KEY" . ", itime INT NOT NULL" . ", prio SMALLINT NOT NULL" . ", data TEXT NOT NULL)" ); $rg_sql_struct[11]['other'] = array(); $rg_sql_struct[12] = array(); $rg_sql_struct[12]['tables'] = array(); $rg_sql_struct[12]['other'] = array( "index repos-uid" => "CREATE INDEX repos_i_uid ON repos(uid)", "index on repos-repo_id" => "CREATE INDEX repos_i_repo_id ON repos(repo_id)" ); $rg_sql_struct[13] = array(); $rg_sql_struct[13]['tables'] = array( "repos_renames" => "CREATE TABLE repos_renames (" . "uid INT NOT NULL" . ", old_name TEXT NOT NULL" . ", itime INT NOT NULL" . ", repo_id INT NOT NULL)" ); $rg_sql_struct[13]['other'] = array( "repos_renames_index_ui_old_name" => "CREATE INDEX repos_renames_i_uid_old_name" . " ON repos_renames(uid, old_name)" ); $rg_sql_struct[14] = array(); $rg_sql_struct[14]['tables'] = array( "users_renames" => "CREATE TABLE users_renames (" . "uid INT NOT NULL" . ", old_name TEXT NOT NULL" . ", itime INT NOT NULL)" ); $rg_sql_struct[14]['other'] = array( "users_renames_index_old_name" => "CREATE INDEX users_renames_i_old_name" . " ON repos_renames(old_name)" ); $rg_sql_struct[15] = array(); $rg_sql_struct[15]['tables'] = array( "watch_repo" => "CREATE TABLE watch_repo (" . "uid INT NOT NULL" . ", repo_id INT NOT NULL)", "watch_bug" => "CREATE TABLE watch_bug (" . "uid INT NOT NULL" . ", repo_id INT NOT NULL" . ", bug_id INT NOT NULL)" ); // This must be the last line $rg_sql_schema_ver = count($rg_sql_struct); /* * Generate structure */ function rg_sql_struct_run($db, $flags, $old_schema_ver) { global $rg_sql_struct; global $rg_sql_schema_ver; $ignore_errors = ($flags & RG_IGNORE_ERRORS) ? TRUE : FALSE; $drop_tables = ($flags & RG_DROP_TABLES) ? TRUE : FALSE; rg_log("sql_struct_run: flags=$flags" . " ignore_errors=" . ($ignore_errors ? "Yes" : "No") . " drop_tables=" . ($drop_tables ? "Yes" : "No") . " old_schema_ver=$old_schema_ver..."); for ($i = $old_schema_ver + 1; $i <= $rg_sql_schema_ver; $i++) { foreach ($rg_sql_struct[$i] as $type => $sqls) { if (count($sqls) == 0) continue; foreach ($sqls as $id => $sql) { rg_log("Applying schema $i, type $type, id $id..."); if ((strcmp($type, "tables") == 0) && ($drop_tables === TRUE)) { rg_log("Dropping table [$id]..."); $sql2 = "DROP TABLE IF EXISTS $id CASCADE"; $res = rg_sql_query($db, $sql2); if ($res === FALSE) { rg_log("WARN: Cannot run sql ($sql2) (" . rg_sql_error() . ")!"); if (!$ignore_errors) return FALSE; } rg_sql_free_result($res); } rg_log("Running [$sql]..."); $res = rg_sql_query($db, $sql); if ($res === FALSE) { rg_log("WARN: Cannot run sql ($sql) (" . rg_sql_error() . ")!"); if (!$ignore_errors) return FALSE; } rg_sql_free_result($res); } } } return TRUE; } /* * Tests if schema update is needed. Return old version */ function rg_sql_struct_update_needed($db) { global $rg_sql_schema_ver; rg_log("sql_struct_update_needed:"); $old = rg_state_get($db, "schema_version"); if ($old === FALSE) { //TODO: error rg_log("\tDEBUG: schema is up to date!"); return FALSE; } if (empty($old)) $old = 0; rg_log("\tDEBUG: old=$old new=$rg_sql_schema_ver"); if ($old == $rg_sql_schema_ver) { rg_log("\tDEBUG: schema is up to date!"); return FALSE; } return $old; } /* * Update schema if needed * Returns FALSE in case of error * This must not be run by web user because of the owner of the locking file. */ function rg_sql_struct_update($db, $flags) { global $rg_sql_schema_ver; rg_log("sql_struct_update: flags=$flags"); $old = rg_sql_struct_update_needed($db); if ($old === FALSE) return TRUE; // If we cannot lock, return error if (rg_lock("schema_update.lock") === FALSE) return FALSE; $ret = FALSE; $rollback = 0; do { if (rg_sql_begin($db) !== TRUE) break; $rollback = 1; $r = rg_sql_struct_run($db, $flags, $old); if ($r !== TRUE) { rg_log("Cannot update schema (" . rg_sql_error() . ")"); break; } $r = rg_state_set($db, "schema_version", $rg_sql_schema_ver); if ($r !== TRUE) { rg_log("Cannot update schema ver (" . rg_state_error() . ")"); break; } if (rg_sql_commit($db) !== TRUE) break; $rollback = 0; $ret = TRUE; } while (0); if ($rollback == 1) rg_sql_rollback($db); rg_unlock("schema_update.lock"); return $ret; } /* * Update slaves (postgresql partitions) * Returns FALSE in case of error */ function rg_sql_struct_slaves_update($db) { global $rg_sql_struct_slaves; rg_prof_start("sql_struct_slaves_update"); rg_log("sql_struct_slaves_update"); $ret = FALSE; $rollback = 0; $locked = FALSE; do { if (empty($rg_sql_struct_slaves)) { $ret = TRUE; break; } // If we cannot lock, return error if (rg_lock("slave_create.lock") === FALSE) break; $locked = TRUE; $last_ts = rg_state_get($db, "slaves_create_last_ts"); if ($last_ts === FALSE) break; $last_ts = intval($last_ts); if ($last_ts == 0) $last_ts = gmmktime(0, 0, 0, gmdate("m") - 1, 1, gmdate("Y")); rg_log("last_ts=$last_ts (" . gmdate("Y-m-d", $last_ts) . ")"); // First second of current month $current_month_ts = gmmktime(0, 0, 0, gmdate("m"), 1, gmdate("Y")); rg_log("current_month_ts=$current_month_ts (" . gmdate("Y-m-d", $current_month_ts) . ")"); // Do we have current month and the next one covered? if ($current_month_ts < $last_ts) { rg_log("\tNo update needed!"); $ret = TRUE; break; } if (rg_sql_begin($db) !== TRUE) break; $rollback = 1; $ok = TRUE; $month = gmdate("m", $last_ts) + 1; $year = gmdate("Y", $last_ts); $ts = gmmktime(0, 0, 0, $month, 1, $year); $stop_ts = gmmktime(0, 0, 0, gmdate("m") + 1, 1, $year); while ($ts <= $stop_ts) { $month++; $next_ts = gmmktime(0, 0, 0, $month, 1, $year); foreach ($rg_sql_struct_slaves as $table) { $slave_table = $table . "_" . gmdate("Y_m", $ts); $sql = "CREATE TABLE " . $slave_table . " (CHECK(itime >= $ts AND itime <= " . ($next_ts - 1) . "))" . " INHERITS (" . $table . ")"; $res = rg_sql_query($db, $sql); if ($res === FALSE) { $ok = FALSE; break; } rg_sql_free_result($res); $sql = "CREATE INDEX " . $slave_table . "_i_itime" . " ON " . $slave_table . " (itime)"; $res = rg_sql_query($db, $sql); if ($res === FALSE) { $ok = FALSE; break; } rg_sql_free_result($res); } $ts = $next_ts; } if ($ok === FALSE) break; $r = rg_state_set($db, "slaves_create_last_ts", $stop_ts); if ($r !== TRUE) { rg_log("Cannot create slave (" . rg_state_error() . ")"); break; } if (rg_sql_commit($db) !== TRUE) break; $rollback = 0; $ret = TRUE; } while (0); if ($rollback == 1) rg_sql_rollback($db); if ($locked === TRUE) rg_unlock("slave_create.lock"); rg_prof_end("sql_struct_slaves_update"); return $ret; } ?>