From 399db1b647e14947e97a865c09215969f56a7efe Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Sat, 27 Apr 2013 18:31:40 +0200 Subject: Add db_prefix configuration option in place of db_table_*/db_sequence_* options Make possible to use db_prefix for schema initialization in Installer (#1489067) Fix updatedb.sh script so it recognizes also table prefix for external DDL files --- CHANGELOG | 3 +++ SQL/postgres.initial.sql | 42 +++++++++++++++---------------- bin/indexcontacts.sh | 2 +- bin/moduserprefs.sh | 2 +- bin/updatedb.sh | 43 ++++++++++++++++++++++++++++---- config/db.inc.php.dist | 24 ++---------------- installer/config.php | 12 +++++++++ installer/rcube_install.php | 32 +++++++++++++++++++++++- installer/test.php | 8 +++--- program/lib/Roundcube/rcube_db.php | 8 +++--- program/lib/Roundcube/rcube_db_pgsql.php | 15 +++++------ 11 files changed, 124 insertions(+), 67 deletions(-) diff --git a/CHANGELOG b/CHANGELOG index a05d1fb89..523443630 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -1,6 +1,9 @@ CHANGELOG Roundcube Webmail =========================== +- Add db_prefix configuration option in place of db_table_*/db_sequence_* options +- Make possible to use db_prefix for schema initialization in Installer (#1489067) +- Fix updatedb.sh script so it recognizes also table prefix for external DDL files - Fix possible collision in generated thumbnail cache key (#1489069) - Fix exit code on bootsrap errors in CLI mode (#1489044) - Fix error handling in CLI mode, use STDERR and non-empty exit code (#1489043) diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index 32d8edee8..f3b368f7d 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -1,11 +1,11 @@ -- Roundcube Webmail initial database structure -- --- Sequence "user_ids" --- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Sequence "users_seq" +-- Name: users_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- -CREATE SEQUENCE user_ids +CREATE SEQUENCE users_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE @@ -17,7 +17,7 @@ CREATE SEQUENCE user_ids -- CREATE TABLE users ( - user_id integer DEFAULT nextval('user_ids'::text) PRIMARY KEY, + user_id integer DEFAULT nextval('users_seq'::text) PRIMARY KEY, username varchar(128) DEFAULT '' NOT NULL, mail_host varchar(128) DEFAULT '' NOT NULL, created timestamp with time zone DEFAULT now() NOT NULL, @@ -45,11 +45,11 @@ CREATE INDEX session_changed_idx ON session (changed); -- --- Sequence "identity_ids" --- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Sequence "identities_seq" +-- Name: identities_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- -CREATE SEQUENCE identity_ids +CREATE SEQUENCE identities_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE @@ -62,7 +62,7 @@ CREATE SEQUENCE identity_ids -- CREATE TABLE identities ( - identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY, + identity_id integer DEFAULT nextval('identities_seq'::text) PRIMARY KEY, user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, changed timestamp with time zone DEFAULT now() NOT NULL, @@ -82,11 +82,11 @@ CREATE INDEX identities_email_idx ON identities (email, del); -- --- Sequence "contact_ids" --- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Sequence "contacts_seq" +-- Name: contacts_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- -CREATE SEQUENCE contact_ids +CREATE SEQUENCE contacts_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE @@ -99,7 +99,7 @@ CREATE SEQUENCE contact_ids -- CREATE TABLE contacts ( - contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY, + contact_id integer DEFAULT nextval('contacts_seq'::text) PRIMARY KEY, user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, changed timestamp with time zone DEFAULT now() NOT NULL, @@ -115,11 +115,11 @@ CREATE TABLE contacts ( CREATE INDEX contacts_user_id_idx ON contacts (user_id, del); -- --- Sequence "contactgroups_ids" --- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Sequence "contactgroups_seq" +-- Name: contactgroups_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- -CREATE SEQUENCE contactgroups_ids +CREATE SEQUENCE contactgroups_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE @@ -131,7 +131,7 @@ CREATE SEQUENCE contactgroups_ids -- CREATE TABLE contactgroups ( - contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY, + contactgroup_id integer DEFAULT nextval('contactgroups_seq'::text) PRIMARY KEY, user_id integer NOT NULL REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, changed timestamp with time zone DEFAULT now() NOT NULL, @@ -238,11 +238,11 @@ CREATE TABLE dictionary ( ); -- --- Sequence "searches_ids" --- Name: searches_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Sequence "searches_seq" +-- Name: searches_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- -CREATE SEQUENCE search_ids +CREATE SEQUENCE searches_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE @@ -254,7 +254,7 @@ CREATE SEQUENCE search_ids -- CREATE TABLE searches ( - search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY, + search_id integer DEFAULT nextval('searches_seq'::text) PRIMARY KEY, user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, "type" smallint DEFAULT 0 NOT NULL, @@ -274,4 +274,4 @@ CREATE TABLE "system" ( value text ); -INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011700'); +INSERT INTO system (name, value) VALUES ('roundcube-version', '2013042700'); diff --git a/bin/indexcontacts.sh b/bin/indexcontacts.sh index 413dc4b3e..c85a535a7 100755 --- a/bin/indexcontacts.sh +++ b/bin/indexcontacts.sh @@ -35,7 +35,7 @@ if (!$db->is_connected() || $db->is_error()) { } // iterate over all users -$sql_result = $db->query("SELECT user_id FROM " . $RCMAIL->config->get('db_table_users', 'users')." WHERE 1=1"); +$sql_result = $db->query("SELECT user_id FROM " . $db->table_name('users') . " ORDER BY user_id"); while ($sql_result && ($sql_arr = $db->fetch_assoc($sql_result))) { echo "Indexing contacts for user " . $sql_arr['user_id'] . "..."; diff --git a/bin/moduserprefs.sh b/bin/moduserprefs.sh index 049372c2f..9bbc885a6 100755 --- a/bin/moduserprefs.sh +++ b/bin/moduserprefs.sh @@ -61,7 +61,7 @@ if ($args['user']) $query = 'user_id=' . intval($args['user']); // iterate over all users -$sql_result = $db->query("SELECT * FROM " . $rcmail->config->get('db_table_users', 'users')." WHERE $query"); +$sql_result = $db->query("SELECT * FROM " . $db->table_name('users') . " WHERE $query"); while ($sql_result && ($sql_arr = $db->fetch_assoc($sql_result))) { echo "Updating prefs for user " . $sql_arr['user_id'] . "..."; diff --git a/bin/updatedb.sh b/bin/updatedb.sh index aeeaf6856..b4ed8b7ba 100755 --- a/bin/updatedb.sh +++ b/bin/updatedb.sh @@ -180,15 +180,48 @@ function update_db_schema($package, $version, $file) function fix_table_names($sql) { - global $DB; + global $DB, $RC, $dir; + static $tables; + static $sequences; + + $prefix = $RC->config->get('db_prefix'); + $engine = $DB->db_provider; + + if (empty($prefix)) { + return $sql; + } + + if ($tables === null) { + $tables = array(); + $sequences = array(); + + // read complete schema (initial) file + $filename = "$dir/../$engine.initial.sql"; + $schema = @file_get_contents($filename); - foreach (array('users','identities','contacts','contactgroups','contactgroupmembers','session','cache','cache_index','cache_index','cache_messages','dictionary','searches','system') as $table) { - $real_table = $DB->table_name($table); - if ($real_table != $table) { - $sql = preg_replace("/([^a-z0-9_])$table([^a-z0-9_])/i", "\\1$real_table\\2", $sql); + // find table names + if (preg_match_all('/CREATE TABLE (\[dbo\]\.|IF NOT EXISTS )?[`"\[\]]*([^`"\[\] \r\n]+)/i', $schema, $matches)) { + foreach ($matches[2] as $table) { + $tables[$table] = $prefix . $table; + } + } + // find sequence names + if ($engine == 'postgres' && preg_match_all('/CREATE SEQUENCE (IF NOT EXISTS )?"?([^" \n\r]+)/i', $schema, $matches)) { + foreach ($matches[2] as $sequence) { + $sequences[$sequence] = $prefix . $sequence; + } } } + // replace table names + foreach ($tables as $table => $real_table) { + $sql = preg_replace("/([^a-zA-Z0-9_])$table([^a-zA-Z0-9_])/", "\\1$real_table\\2", $sql); + } + // replace sequence names + foreach ($sequences as $sequence => $real_sequence) { + $sql = preg_replace("/([^a-zA-Z0-9_])$sequence([^a-zA-Z0-9_])/", "\\1$real_sequence\\2", $sql); + } + return $sql; } diff --git a/config/db.inc.php.dist b/config/db.inc.php.dist index 9bbf7d75b..17a273a0c 100644 --- a/config/db.inc.php.dist +++ b/config/db.inc.php.dist @@ -36,27 +36,7 @@ $rcmail_config['db_dsnr'] = ''; // see: http://www.php.net/manual/en/features.persistent-connections.php $rcmail_config['db_persistent'] = FALSE; -// you can define specific table names used to store webmail data -$rcmail_config['db_table_users'] = 'users'; -$rcmail_config['db_table_identities'] = 'identities'; -$rcmail_config['db_table_contacts'] = 'contacts'; -$rcmail_config['db_table_contactgroups'] = 'contactgroups'; -$rcmail_config['db_table_contactgroupmembers'] = 'contactgroupmembers'; -$rcmail_config['db_table_session'] = 'session'; -$rcmail_config['db_table_cache'] = 'cache'; -$rcmail_config['db_table_cache_index'] = 'cache_index'; -$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'; -$rcmail_config['db_sequence_identities'] = 'identity_ids'; -$rcmail_config['db_sequence_contacts'] = 'contact_ids'; -$rcmail_config['db_sequence_contactgroups'] = 'contactgroups_ids'; -$rcmail_config['db_sequence_searches'] = 'search_ids'; - +// you can define specific table (and sequence) names prefix +$rcmail_config['db_prefix'] = ''; // end db config file diff --git a/installer/config.php b/installer/config.php index d6846edc7..b9a051ba0 100644 --- a/installer/config.php +++ b/installer/config.php @@ -301,6 +301,18 @@ echo '
'; ?> + +
db_prefix
+
+ '_db_prefix', 'size' => 20, 'id' => "cfgdbprefix")); +echo $input_prefix->show($RCI->getprop('db_prefix')); + +?> +
Optional prefix that will be added to database object names (tables and sequences).
+
+ diff --git a/installer/rcube_install.php b/installer/rcube_install.php index 2e1298707..32b6a5dd7 100644 --- a/installer/rcube_install.php +++ b/installer/rcube_install.php @@ -372,7 +372,7 @@ class rcube_install $existing_tables = $DB->list_tables(); foreach ($db_schema as $table => $cols) { - $table = !empty($this->config['db_table_'.$table]) ? $this->config['db_table_'.$table] : $table; + $table = $this->config['db_prefix'] . $table; if (!in_array($table, $existing_tables)) { $errors[] = "Missing table '".$table."'"; } @@ -655,6 +655,7 @@ class rcube_install */ function exec_sql($sql, $DB) { + $sql = $this->fix_table_names($sql, $DB); $buff = ''; foreach (explode("\n", $sql) as $line) { if (preg_match('/^--/', $line) || trim($line) == '') @@ -673,6 +674,35 @@ class rcube_install } + /** + * Parse SQL file and fix table names according to db_prefix + * Note: This need to be a complete database initial file + */ + private function fix_table_names($sql, $DB) + { + if (empty($this->config['db_prefix'])) { + return $sql; + } + + // replace table names + if (preg_match_all('/CREATE TABLE (\[dbo\]\.|IF NOT EXISTS )?[`"\[\]]*([^`"\[\] \r\n]+)/i', $sql, $matches)) { + foreach ($matches[2] as $table) { + $real_table = $this->config['db_prefix'] . $table; + $sql = preg_replace("/([^a-zA-Z0-9_])$table([^a-zA-Z0-9_])/", "\\1$real_table\\2", $sql); + } + } + // replace sequence names + if ($DB->db_provider == 'postgres' && preg_match_all('/CREATE SEQUENCE (IF NOT EXISTS )?"?([^" \n\r]+)/i', $sql, $matches)) { + foreach ($matches[2] as $sequence) { + $real_sequence = $this->config['db_prefix'] . $sequence; + $sql = preg_replace("/([^a-zA-Z0-9_])$sequence([^a-zA-Z0-9_])/", "\\1$real_sequence\\2", $sql); + } + } + + return $sql; + } + + /** * Handler for Roundcube errors */ diff --git a/installer/test.php b/installer/test.php index 340fe267d..fb3e7e937 100644 --- a/installer/test.php +++ b/installer/test.php @@ -24,7 +24,7 @@ else if (!$read_main) { } echo '
'; -if ($read_db && !empty($RCI->config['db_table_users'])) { +if ($read_db && !empty($RCI->config['db_dsnw'])) { $RCI->pass('db.inc.php'); } else if ($read_db) { @@ -171,7 +171,7 @@ else if ($db_working && $_POST['updatedb']) { // test database if ($db_working) { - $db_read = $DB->query("SELECT count(*) FROM {$RCI->config['db_table_users']}"); + $db_read = $DB->query("SELECT count(*) FROM {$RCI->config['db_prefix']}users"); if ($DB->is_error()) { $RCI->fail('DB Schema', "Database not initialized"); echo '

'; @@ -195,11 +195,11 @@ if ($db_working) { if ($db_working) { // write test $insert_id = md5(uniqid()); - $db_write = $DB->query("INSERT INTO {$RCI->config['db_table_session']} (sess_id, created, ip, vars) VALUES (?, ".$DB->now().", '127.0.0.1', 'foo')", $insert_id); + $db_write = $DB->query("INSERT INTO {$RCI->config['db_prefix']}session (sess_id, created, ip, vars) VALUES (?, ".$DB->now().", '127.0.0.1', 'foo')", $insert_id); if ($db_write) { $RCI->pass('DB Write'); - $DB->query("DELETE FROM {$RCI->config['db_table_session']} WHERE sess_id=?", $insert_id); + $DB->query("DELETE FROM {$RCI->config['db_prefix']}session WHERE sess_id=?", $insert_id); } else { $RCI->fail('DB Write', $RCI->get_error()); diff --git a/program/lib/Roundcube/rcube_db.php b/program/lib/Roundcube/rcube_db.php index 9cda023bf..d86e3dd98 100644 --- a/program/lib/Roundcube/rcube_db.php +++ b/program/lib/Roundcube/rcube_db.php @@ -846,11 +846,9 @@ class rcube_db { $rcube = rcube::get_instance(); - // return table name if configured - $config_key = 'db_table_'.$table; - - if ($name = $rcube->config->get($config_key)) { - return $name; + // add prefix to the table name if configured + if ($prefix = $rcube->config->get('db_prefix')) { + return $prefix . $table; } return $table; diff --git a/program/lib/Roundcube/rcube_db_pgsql.php b/program/lib/Roundcube/rcube_db_pgsql.php index cf23c5e48..adfd2207b 100644 --- a/program/lib/Roundcube/rcube_db_pgsql.php +++ b/program/lib/Roundcube/rcube_db_pgsql.php @@ -53,19 +53,20 @@ class rcube_db_pgsql extends rcube_db /** * Return correct name for a specific database sequence * - * @param string $sequence Secuence name + * @param string $table Table name * * @return string Translated sequence name */ - protected function sequence_name($sequence) + protected function sequence_name($table) { - $rcube = rcube::get_instance(); + // Note: we support only one sequence per table + // Note: The sequence name must be _seq + $sequence = $table . '_seq'; + $rcube = rcube::get_instance(); // return sequence name if configured - $config_key = 'db_sequence_'.$sequence; - - if ($name = $rcube->config->get($config_key)) { - return $name; + if ($prefix = $rcube->config->get('db_prefix')) { + return $prefix . $sequence; } return $sequence; -- cgit v1.2.3