From 399db1b647e14947e97a865c09215969f56a7efe Mon Sep 17 00:00:00 2001
From: Aleksander Machniak <alec@alec.pl>
Date: Sat, 27 Apr 2013 12:31:40 -0400
Subject: [PATCH] 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
bin/indexcontacts.sh | 2
installer/rcube_install.php | 32 +++++++
installer/config.php | 12 +++
config/db.inc.php.dist | 24 -----
bin/moduserprefs.sh | 2
program/lib/Roundcube/rcube_db_pgsql.php | 15 ++-
installer/test.php | 8 +-
program/lib/Roundcube/rcube_db.php | 8 -
SQL/postgres.initial.sql | 42 +++++-----
bin/updatedb.sh | 43 +++++++++-
11 files changed, 124 insertions(+), 67 deletions(-)
diff --git a/CHANGELOG b/CHANGELOG
index a05d1fb..5234436 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 32d8ede..f3b368f 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 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 @@
--
--- 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 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 @@
--
--- 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 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 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 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 @@
);
--
--- 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 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 @@
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 413dc4b..c85a535 100755
--- a/bin/indexcontacts.sh
+++ b/bin/indexcontacts.sh
@@ -35,7 +35,7 @@
}
// 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 049372c..9bbc885 100755
--- a/bin/moduserprefs.sh
+++ b/bin/moduserprefs.sh
@@ -61,7 +61,7 @@
$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 aeeaf68..b4ed8b7 100755
--- a/bin/updatedb.sh
+++ b/bin/updatedb.sh
@@ -180,13 +180,46 @@
function fix_table_names($sql)
{
- global $DB;
+ global $DB, $RC, $dir;
+ static $tables;
+ static $sequences;
- 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);
+ $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);
+
+ // 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 9bbf7d7..17a273a 100644
--- a/config/db.inc.php.dist
+++ b/config/db.inc.php.dist
@@ -36,27 +36,7 @@
// 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 d6846ed..b9a051b 100644
--- a/installer/config.php
+++ b/installer/config.php
@@ -301,6 +301,18 @@
?>
</dd>
+
+<dt class="propname">db_prefix</dt>
+<dd>
+<?php
+
+$input_prefix = new html_inputfield(array('name' => '_db_prefix', 'size' => 20, 'id' => "cfgdbprefix"));
+echo $input_prefix->show($RCI->getprop('db_prefix'));
+
+?>
+<div>Optional prefix that will be added to database object names (tables and sequences).</div>
+</dd>
+
</dl>
</fieldset>
diff --git a/installer/rcube_install.php b/installer/rcube_install.php
index 2e12987..32b6a5d 100644
--- a/installer/rcube_install.php
+++ b/installer/rcube_install.php
@@ -372,7 +372,7 @@
$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 @@
*/
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) == '')
@@ -674,6 +675,35 @@
/**
+ * 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
*/
function raise_error($p)
diff --git a/installer/test.php b/installer/test.php
index 340fe26..fb3e7e9 100644
--- a/installer/test.php
+++ b/installer/test.php
@@ -24,7 +24,7 @@
}
echo '<br />';
-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 @@
// 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 '<p><input type="submit" name="initdb" value="Initialize database" /></p>';
@@ -195,11 +195,11 @@
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 9cda023..d86e3dd 100644
--- a/program/lib/Roundcube/rcube_db.php
+++ b/program/lib/Roundcube/rcube_db.php
@@ -846,11 +846,9 @@
{
$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 cf23c5e..adfd220 100644
--- a/program/lib/Roundcube/rcube_db_pgsql.php
+++ b/program/lib/Roundcube/rcube_db_pgsql.php
@@ -53,19 +53,20 @@
/**
* 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 <table_name>_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;
--
Gitblit v1.9.1