From b7e7c8f9501850a38705e0a1f18a8ae6e25f1be1 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Wed, 9 Jan 2013 15:57:29 +0100 Subject: Added new database upgrade script, converted DDL scripts to new format --- bin/updatedb.sh | 177 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 177 insertions(+) create mode 100755 bin/updatedb.sh (limited to 'bin') diff --git a/bin/updatedb.sh b/bin/updatedb.sh new file mode 100755 index 000000000..d2cf1b046 --- /dev/null +++ b/bin/updatedb.sh @@ -0,0 +1,177 @@ +#!/usr/bin/env php + | + +-----------------------------------------------------------------------+ +*/ + +define('INSTALL_PATH', realpath(dirname(__FILE__) . '/..') . '/' ); + +require_once INSTALL_PATH . 'program/include/clisetup.php'; + +// get arguments +$opts = rcube_utils::get_opt(array( + 'v' => 'version', + 'd' => 'dir', + 'l' => 'label', +)); + +if (empty($opts['dir'])) { + echo "ERROR: Database schema directory not specified (--dir).\n"; + exit(1); +} +if (empty($opts['label'])) { + echo "ERROR: Database schema label not specified (--label).\n"; + exit(1); +} + +// Check if directory exists +if (!file_exists($opts['dir'])) { + echo "ERROR: Specified database schema directory doesn't exist.\n"; + exit(1); +} + +// version is specified, use release-to-version map +if ($opts['version']) { + // Map old release version string to DB schema version + // Note: This is for backward compat. only, do not need to be updated + $map = array( + '0.1-stable' => 1, + '0.1.1' => 2008030300, + '0.2-alpha' => 2008040500, + '0.2-beta' => 2008060900, + '0.2-stable' => 2008092100, + '0.3-stable' => 2008092100, + '0.3.1' => 2009090400, + '0.4-beta' => 2009103100, + '0.4.2' => 2010042300, + '0.5-beta' => 2010100600, + '0.5' => 2010100600, + '0.5.1' => 2010100600, + '0.6-beta' => 2011011200, + '0.6' => 2011011200, + '0.7-beta' => 2011092800, + '0.7' => 2011111600, + '0.7.1' => 2011111600, + '0.7.2' => 2011111600, + '0.7.3' => 2011111600, + '0.8-beta' => 2011121400, + '0.8-rc' => 2011121400, + '0.8.0' => 2011121400, + '0.8.1' => 2011121400, + '0.8.2' => 2011121400, + '0.8.3' => 2011121400, + '0.8.4' => 2011121400, + '0.9-beta' => 2012080700, + ); + + $version = $map[$opts['version']]; +} + +$RC = rcube::get_instance(); +$DB = rcube_db::factory($RC->config->get('db_dsnw')); + +// Connect to database +$DB->db_connect('w'); +if (!$DB->is_connected()) { + echo "Error connecting to database: " . $DB->is_error() . ".\n"; + exit(1); +} + +// Read DB schema version from database +if (empty($version)) { + @$DB->query("SELECT " . $DB->quote_identifier('value') + ." FROM " . $DB->quote_identifier('system') + ." WHERE name = ?", + $opts['label'] . '-version'); + + $row = $DB->fetch_array(); + $version = $row[0]; +} + +// Assume last version without the "system" table +if (empty($version)) { + $version = 2012080700; +} + +$dir = $opts['dir'] . DIRECTORY_SEPARATOR . $DB->db_provider; +if (!file_exists($dir)) { + echo "DDL Upgrade files for " . $DB->db_provider . " driver not found.\n"; + exit(1); +} + +$dh = opendir($dir); +$result = array(); + +while ($file = readdir($dh)) { + if (preg_match('/^([0-9]+)\.sql$/', $file, $m) && $m[1] > $version) { + $result[] = $m[1]; + } +} +sort($result, SORT_NUMERIC); + +foreach ($result as $v) { + echo "Updating database schema ($v)... "; + $error = update_db_schema($opts['label'], $v, $dir . DIRECTORY_SEPARATOR . "$v.sql"); + + if ($error) { + echo "\nError in DDL upgrade $v: $error\n"; + exit(1); + } + echo "[OK]\n"; +} + +exit(0); + +function update_db_schema($label, $version, $file) +{ + global $DB; + + // read DDL file + if ($lines = file($file)) { + $sql = ''; + foreach ($lines as $line) { + if (preg_match('/^--/', $line) || trim($line) == '') + continue; + + $sql .= $line . "\n"; + if (preg_match('/(;|^GO)$/', trim($line))) { + @$DB->query($sql); + $sql = ''; + if ($error = $DB->is_error()) { + return $error; + } + } + } + } + + $DB->query("UPDATE " . $DB->quote_identifier('system') + ." SET " . $DB->quote_identifier('value') . " = ?" + ." WHERE " . $DB->quote_identifier('name') . " = ?", + $version, $opts['label'] . '-version'); + + if (!$DB->is_error() && !$DB->affected_rows()) { + $DB->query("INSERT INTO " . $DB->quote_identifier('system') + ." (" . $DB->quote_identifier('name') . ", " . $DB->quote_identifier('value') . ")" + ." VALUES (?, ?)", + $opts['label'] . '-version', $version); + } + + return $DB->is_error(); +} + +?> -- cgit v1.2.3 From 7e7431bddadae7802748979864ffafa73f694ede Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Wed, 9 Jan 2013 19:21:58 +0100 Subject: Use updatedb.sh from update.sh + various fixes in updatedb.sh --- bin/update.sh | 36 ++++++++---------------------------- bin/updatedb.sh | 50 ++++++++++++++++++++++++-------------------------- 2 files changed, 32 insertions(+), 54 deletions(-) (limited to 'bin') diff --git a/bin/update.sh b/bin/update.sh index 2015aa904..e1beef053 100755 --- a/bin/update.sh +++ b/bin/update.sh @@ -124,7 +124,7 @@ if ($RCI->configured) { } } else { - echo "Please update your config files manually according to the above messages.\n\n"; + echo "Please update your config files manually according to the above messages.\n"; } } @@ -143,36 +143,18 @@ if ($RCI->configured) { // check database schema if ($RCI->config['db_dsnw']) { - $DB = rcube_db::factory($RCI->config['db_dsnw'], '', false); - $DB->db_connect('w'); - if ($db_error_msg = $DB->is_error()) { - echo "Error connecting to database: $db_error_msg\n"; - $success = false; - } - else if ($err = $RCI->db_schema_check($DB, false)) { - $updatefile = INSTALL_PATH . 'SQL/' . (isset($RCI->db_map[$DB->db_provider]) ? $RCI->db_map[$DB->db_provider] : $DB->db_provider) . '.update.sql'; - echo "WARNING: Database schema needs to be updated!\n"; - echo join("\n", $err) . "\n\n"; - $success = false; - - if ($opts['version']) { - echo "Do you want to run the update queries to get the schmea fixed? (y/N)\n"; - $input = trim(fgets(STDIN)); - if (strtolower($input) == 'y') { - $success = $RCI->update_db($DB, $opts['version']); - } - } - - if (!$success) - echo "Open $updatefile and execute all queries below the comment with the currently installed version number.\n"; - } + echo "Executing database schema update.\n"; + system(INSTALL_PATH . "bin/updatedb.sh --label=roundcube --version=" . $ops['version'] + . " --dir=" . INSTALL_PATH . DIRECTORY_SEPARATOR . "SQL", $res); + + $success = !$res; } - + // index contacts for fulltext searching if (version_compare(version_parse($opts['version']), '0.6.0', '<')) { system(INSTALL_PATH . 'bin/indexcontacts.sh'); } - + if ($success) { echo "This instance of Roundcube is up-to-date.\n"; echo "Have fun!\n"; @@ -183,6 +165,4 @@ else { echo "Open http://url-to-roundcube/installer/ in your browser and follow the instuctions.\n"; } -echo "\n"; - ?> diff --git a/bin/updatedb.sh b/bin/updatedb.sh index d2cf1b046..3d37edf61 100755 --- a/bin/updatedb.sh +++ b/bin/updatedb.sh @@ -45,8 +45,27 @@ if (!file_exists($opts['dir'])) { exit(1); } -// version is specified, use release-to-version map -if ($opts['version']) { +$RC = rcube::get_instance(); +$DB = rcube_db::factory($RC->config->get('db_dsnw')); + +// Connect to database +$DB->db_connect('w'); +if (!$DB->is_connected()) { + echo "Error connecting to database: " . $DB->is_error() . ".\n"; + exit(1); +} + +// Read DB schema version from database +$DB->query("SELECT " . $DB->quote_identifier('value') + ." FROM " . $DB->quote_identifier('system') + ." WHERE " . $DB->quote_identifier('name') ." = ?", + $opts['label'] . '-version'); + +$row = $DB->fetch_array(); +$version = $row[0]; + +// no DB version, but release version is specified +if (!$version && $opts['version']) { // Map old release version string to DB schema version // Note: This is for backward compat. only, do not need to be updated $map = array( @@ -82,28 +101,7 @@ if ($opts['version']) { $version = $map[$opts['version']]; } -$RC = rcube::get_instance(); -$DB = rcube_db::factory($RC->config->get('db_dsnw')); - -// Connect to database -$DB->db_connect('w'); -if (!$DB->is_connected()) { - echo "Error connecting to database: " . $DB->is_error() . ".\n"; - exit(1); -} - -// Read DB schema version from database -if (empty($version)) { - @$DB->query("SELECT " . $DB->quote_identifier('value') - ." FROM " . $DB->quote_identifier('system') - ." WHERE name = ?", - $opts['label'] . '-version'); - - $row = $DB->fetch_array(); - $version = $row[0]; -} - -// Assume last version without the "system" table +// Assume last version before the system table was added if (empty($version)) { $version = 2012080700; } @@ -162,13 +160,13 @@ function update_db_schema($label, $version, $file) $DB->query("UPDATE " . $DB->quote_identifier('system') ." SET " . $DB->quote_identifier('value') . " = ?" ." WHERE " . $DB->quote_identifier('name') . " = ?", - $version, $opts['label'] . '-version'); + $version, $label . '-version'); if (!$DB->is_error() && !$DB->affected_rows()) { $DB->query("INSERT INTO " . $DB->quote_identifier('system') ." (" . $DB->quote_identifier('name') . ", " . $DB->quote_identifier('value') . ")" ." VALUES (?, ?)", - $opts['label'] . '-version', $version); + $label . '-version', $version); } return $DB->is_error(); -- cgit v1.2.3 From 9be085b2f34e9e42ce9821ffadaca98f989e4364 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Mon, 14 Jan 2013 08:44:48 +0100 Subject: Don't attempt to read system table if it doesn't exist --- bin/updatedb.sh | 20 +++++++++++--------- 1 file changed, 11 insertions(+), 9 deletions(-) (limited to 'bin') diff --git a/bin/updatedb.sh b/bin/updatedb.sh index 3d37edf61..bc86d0496 100755 --- a/bin/updatedb.sh +++ b/bin/updatedb.sh @@ -55,16 +55,18 @@ if (!$DB->is_connected()) { exit(1); } -// Read DB schema version from database -$DB->query("SELECT " . $DB->quote_identifier('value') - ." FROM " . $DB->quote_identifier('system') - ." WHERE " . $DB->quote_identifier('name') ." = ?", - $opts['label'] . '-version'); - -$row = $DB->fetch_array(); -$version = $row[0]; +// Read DB schema version from database (if system table exists) +if (in_array('system', (array)$DB->list_tables())) { + $DB->query("SELECT " . $DB->quote_identifier('value') + ." FROM " . $DB->quote_identifier('system') + ." WHERE " . $DB->quote_identifier('name') ." = ?", + $opts['label'] . '-version'); + + $row = $DB->fetch_array(); + $version = $row[0]; +} -// no DB version, but release version is specified +// DB version not found, but release version is specified if (!$version && $opts['version']) { // Map old release version string to DB schema version // Note: This is for backward compat. only, do not need to be updated -- cgit v1.2.3 From c3d061893b3d49ad725d1518ff0c27db54669015 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Mon, 14 Jan 2013 10:06:12 +0100 Subject: Don't attempt to write to 'system' table if it doesn't exist --- bin/updatedb.sh | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'bin') diff --git a/bin/updatedb.sh b/bin/updatedb.sh index bc86d0496..9f2aef1bf 100755 --- a/bin/updatedb.sh +++ b/bin/updatedb.sh @@ -159,6 +159,11 @@ function update_db_schema($label, $version, $file) } } + // escape if 'system' table does not exist + if ($version < 2013011000) { + return; + } + $DB->query("UPDATE " . $DB->quote_identifier('system') ." SET " . $DB->quote_identifier('value') . " = ?" ." WHERE " . $DB->quote_identifier('name') . " = ?", -- cgit v1.2.3 From 9e329c0a275cc0a0ce02d86593c2ee558d846239 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Wed, 16 Jan 2013 12:24:05 +0100 Subject: Renamed option updatedb.sh --label to --package --- bin/update.sh | 2 +- bin/updatedb.sh | 16 ++++++++-------- installer/rcube_install.php | 2 +- 3 files changed, 10 insertions(+), 10 deletions(-) (limited to 'bin') diff --git a/bin/update.sh b/bin/update.sh index e1beef053..536bad9ba 100755 --- a/bin/update.sh +++ b/bin/update.sh @@ -144,7 +144,7 @@ if ($RCI->configured) { // check database schema if ($RCI->config['db_dsnw']) { echo "Executing database schema update.\n"; - system(INSTALL_PATH . "bin/updatedb.sh --label=roundcube --version=" . $ops['version'] + system(INSTALL_PATH . "bin/updatedb.sh --package=roundcube --version=" . $ops['version'] . " --dir=" . INSTALL_PATH . DIRECTORY_SEPARATOR . "SQL", $res); $success = !$res; diff --git a/bin/updatedb.sh b/bin/updatedb.sh index 9f2aef1bf..017c14913 100755 --- a/bin/updatedb.sh +++ b/bin/updatedb.sh @@ -27,15 +27,15 @@ require_once INSTALL_PATH . 'program/include/clisetup.php'; $opts = rcube_utils::get_opt(array( 'v' => 'version', 'd' => 'dir', - 'l' => 'label', + 'p' => 'package', )); if (empty($opts['dir'])) { echo "ERROR: Database schema directory not specified (--dir).\n"; exit(1); } -if (empty($opts['label'])) { - echo "ERROR: Database schema label not specified (--label).\n"; +if (empty($opts['package'])) { + echo "ERROR: Database schema package name not specified (--package).\n"; exit(1); } @@ -60,7 +60,7 @@ if (in_array('system', (array)$DB->list_tables())) { $DB->query("SELECT " . $DB->quote_identifier('value') ." FROM " . $DB->quote_identifier('system') ." WHERE " . $DB->quote_identifier('name') ." = ?", - $opts['label'] . '-version'); + $opts['package'] . '-version'); $row = $DB->fetch_array(); $version = $row[0]; @@ -126,7 +126,7 @@ sort($result, SORT_NUMERIC); foreach ($result as $v) { echo "Updating database schema ($v)... "; - $error = update_db_schema($opts['label'], $v, $dir . DIRECTORY_SEPARATOR . "$v.sql"); + $error = update_db_schema($opts['package'], $v, $dir . DIRECTORY_SEPARATOR . "$v.sql"); if ($error) { echo "\nError in DDL upgrade $v: $error\n"; @@ -137,7 +137,7 @@ foreach ($result as $v) { exit(0); -function update_db_schema($label, $version, $file) +function update_db_schema($package, $version, $file) { global $DB; @@ -167,13 +167,13 @@ function update_db_schema($label, $version, $file) $DB->query("UPDATE " . $DB->quote_identifier('system') ." SET " . $DB->quote_identifier('value') . " = ?" ." WHERE " . $DB->quote_identifier('name') . " = ?", - $version, $label . '-version'); + $version, $package . '-version'); if (!$DB->is_error() && !$DB->affected_rows()) { $DB->query("INSERT INTO " . $DB->quote_identifier('system') ." (" . $DB->quote_identifier('name') . ", " . $DB->quote_identifier('value') . ")" ." VALUES (?, ?)", - $label . '-version', $version); + $package . '-version', $version); } return $DB->is_error(); diff --git a/installer/rcube_install.php b/installer/rcube_install.php index bbb225bc7..a800dfdbe 100644 --- a/installer/rcube_install.php +++ b/installer/rcube_install.php @@ -633,7 +633,7 @@ class rcube_install */ function update_db($version) { - system(INSTALL_PATH . "bin/updatedb.sh --label=roundcube --version=" . $version + system(INSTALL_PATH . "bin/updatedb.sh --package=roundcube --version=" . $version . " --dir=" . INSTALL_PATH . "SQL", $result); return !$result; -- cgit v1.2.3 From c101871d463963fe5426a2db3ba39cb524a96dbd Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Wed, 16 Jan 2013 12:30:15 +0100 Subject: Add and use db_table_system config option --- bin/updatedb.sh | 12 +++++++----- config/db.inc.php.dist | 1 + 2 files changed, 8 insertions(+), 5 deletions(-) (limited to 'bin') diff --git a/bin/updatedb.sh b/bin/updatedb.sh index 017c14913..4b922ea83 100755 --- a/bin/updatedb.sh +++ b/bin/updatedb.sh @@ -55,10 +55,10 @@ if (!$DB->is_connected()) { exit(1); } -// Read DB schema version from database (if system table exists) +// Read DB schema version from database (if 'system' table exists) if (in_array('system', (array)$DB->list_tables())) { $DB->query("SELECT " . $DB->quote_identifier('value') - ." FROM " . $DB->quote_identifier('system') + ." FROM " . $DB->quote_identifier($DB->table_name('system')) ." WHERE " . $DB->quote_identifier('name') ." = ?", $opts['package'] . '-version'); @@ -103,7 +103,7 @@ if (!$version && $opts['version']) { $version = $map[$opts['version']]; } -// Assume last version before the system table was added +// Assume last version before the 'system' table was added if (empty($version)) { $version = 2012080700; } @@ -164,13 +164,15 @@ function update_db_schema($package, $version, $file) return; } - $DB->query("UPDATE " . $DB->quote_identifier('system') + $system_table = $DB->quote_identifier($DB->table_name('system')); + + $DB->query("UPDATE " . $system_table ." SET " . $DB->quote_identifier('value') . " = ?" ." WHERE " . $DB->quote_identifier('name') . " = ?", $version, $package . '-version'); if (!$DB->is_error() && !$DB->affected_rows()) { - $DB->query("INSERT INTO " . $DB->quote_identifier('system') + $DB->query("INSERT INTO " . $system_table ." (" . $DB->quote_identifier('name') . ", " . $DB->quote_identifier('value') . ")" ." VALUES (?, ?)", $package . '-version', $version); diff --git a/config/db.inc.php.dist b/config/db.inc.php.dist index a596d647d..9bbf7d75b 100644 --- a/config/db.inc.php.dist +++ b/config/db.inc.php.dist @@ -49,6 +49,7 @@ $rcmail_config['db_table_cache_thread'] = 'cache_thread'; $rcmail_config['db_table_cache_messages'] = 'cache_messages'; $rcmail_config['db_table_dictionary'] = 'dictionary'; $rcmail_config['db_table_searches'] = 'searches'; +$rcmail_config['db_table_system'] = 'system'; // you can define specific sequence names used in PostgreSQL $rcmail_config['db_sequence_users'] = 'user_ids'; -- cgit v1.2.3