From 720e7d376ffe0fb625d0b1e250121823a71da094 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Tue, 29 Apr 2014 11:34:43 +0200 Subject: Fix varius db_prefix issues (#1489839) - Move DDL script execution code to rcube_db class(es). - Improve prefix replacement code, so index names are also modified --- CHANGELOG | 1 + SQL/mssql.initial.sql | 44 +++++------ bin/updatedb.sh | 64 +--------------- installer/rcube_install.php | 60 +-------------- installer/test.php | 1 + program/lib/Roundcube/rcube_db.php | 83 ++++++++++++++++---- program/lib/Roundcube/rcube_db_mssql.php | 20 +++++ program/lib/Roundcube/rcube_db_pgsql.php | 25 +++++- program/lib/Roundcube/rcube_db_sqlsrv.php | 121 +----------------------------- 9 files changed, 141 insertions(+), 278 deletions(-) diff --git a/CHANGELOG b/CHANGELOG index 0d805927c..fd09bb6bb 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -17,6 +17,7 @@ CHANGELOG Roundcube Webmail - Avoid popupmenus being closed when scrollbar is clicked (#1489832) - Add proxy_whitelist configuration option (#1489729) - Fix identities_level=4 handling in new_user_dialog plugin (#1489840) +- Fix varius db_prefix issues (#1489839) RELEASE 1.0.0 ------------- diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql index 1027867e3..9d0521c36 100644 --- a/SQL/mssql.initial.sql +++ b/SQL/mssql.initial.sql @@ -155,42 +155,42 @@ ALTER TABLE [dbo].[contacts] WITH NOCHECK ADD CONSTRAINT [PK_contacts_contact_id] PRIMARY KEY CLUSTERED ( [contact_id] - ) ON [PRIMARY] + ) ON [PRIMARY] GO ALTER TABLE [dbo].[contactgroups] WITH NOCHECK ADD CONSTRAINT [PK_contactgroups_contactgroup_id] PRIMARY KEY CLUSTERED ( [contactgroup_id] - ) ON [PRIMARY] + ) ON [PRIMARY] GO ALTER TABLE [dbo].[contactgroupmembers] WITH NOCHECK ADD CONSTRAINT [PK_contactgroupmembers_id] PRIMARY KEY CLUSTERED ( [contactgroup_id], [contact_id] - ) ON [PRIMARY] + ) ON [PRIMARY] GO ALTER TABLE [dbo].[identities] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [identity_id] - ) ON [PRIMARY] + ) ON [PRIMARY] GO ALTER TABLE [dbo].[session] WITH NOCHECK ADD CONSTRAINT [PK_session_sess_id] PRIMARY KEY CLUSTERED ( [sess_id] - ) ON [PRIMARY] + ) ON [PRIMARY] GO ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [PK_users_user_id] PRIMARY KEY CLUSTERED ( [user_id] - ) ON [PRIMARY] + ) ON [PRIMARY] GO ALTER TABLE [dbo].[searches] WITH NOCHECK ADD @@ -225,22 +225,22 @@ ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [DF_cache_messages_flags] DEFAULT (0) FOR [flags] GO -CREATE INDEX [IX_cache_user_id] ON [dbo].[cache]([user_id]) ON [PRIMARY] +CREATE INDEX [IX_cache_user_id] ON [dbo].[cache]([user_id]) ON [PRIMARY] GO -CREATE INDEX [IX_cache_cache_key] ON [dbo].[cache]([cache_key]) ON [PRIMARY] +CREATE INDEX [IX_cache_cache_key] ON [dbo].[cache]([cache_key]) ON [PRIMARY] GO -CREATE INDEX [IX_cache_shared_cache_key] ON [dbo].[cache_shared]([cache_key]) ON [PRIMARY] +CREATE INDEX [IX_cache_shared_cache_key] ON [dbo].[cache_shared]([cache_key]) ON [PRIMARY] GO -CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY] +CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY] GO -CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY] +CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY] GO -CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY] +CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY] GO CREATE INDEX [IX_cache_expires] ON [dbo].[cache]([expires]) ON [PRIMARY] @@ -269,7 +269,7 @@ ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [CK_contacts_del] CHECK ([del] = '1' or [del] = '0') GO -CREATE INDEX [IX_contacts_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY] +CREATE INDEX [IX_contacts_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY] GO ALTER TABLE [dbo].[contactgroups] ADD @@ -280,7 +280,7 @@ ALTER TABLE [dbo].[contactgroups] ADD CONSTRAINT [CK_contactgroups_del] CHECK ([del] = '1' or [del] = '0') GO -CREATE INDEX [IX_contactgroups_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY] +CREATE INDEX [IX_contactgroups_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY] GO ALTER TABLE [dbo].[contactgroupmembers] ADD @@ -289,7 +289,7 @@ ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [DF_contactgroupmembers_created] DEFAULT (getdate()) FOR [created] GO -CREATE INDEX [IX_contactgroupmembers_contact_id] ON [dbo].[contactgroupmembers]([contact_id]) ON [PRIMARY] +CREATE INDEX [IX_contactgroupmembers_contact_id] ON [dbo].[contactgroupmembers]([contact_id]) ON [PRIMARY] GO ALTER TABLE [dbo].[identities] ADD @@ -302,13 +302,13 @@ ALTER TABLE [dbo].[identities] ADD CONSTRAINT [DF_identities_reply] DEFAULT ('') FOR [reply-to], CONSTRAINT [DF_identities_bcc] DEFAULT ('') FOR [bcc], CONSTRAINT [DF_identities_html_signature] DEFAULT ('0') FOR [html_signature], - CHECK ([standard] = '1' or [standard] = '0'), - CHECK ([del] = '1' or [del] = '0') + CHECK ([standard] = '1' or [standard] = '0'), + CHECK ([del] = '1' or [del] = '0') GO -CREATE INDEX [IX_identities_user_id] ON [dbo].[identities]([user_id]) ON [PRIMARY] +CREATE INDEX [IX_identities_user_id] ON [dbo].[identities]([user_id]) ON [PRIMARY] GO -CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY] +CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY] GO ALTER TABLE [dbo].[session] ADD @@ -317,7 +317,7 @@ ALTER TABLE [dbo].[session] ADD CONSTRAINT [DF_session_ip] DEFAULT ('') FOR [ip] GO -CREATE INDEX [IX_session_changed] ON [dbo].[session]([changed]) ON [PRIMARY] +CREATE INDEX [IX_session_changed] ON [dbo].[session]([changed]) ON [PRIMARY] GO ALTER TABLE [dbo].[users] ADD @@ -326,10 +326,10 @@ ALTER TABLE [dbo].[users] ADD CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created] GO -CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY] +CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY] GO -CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY] +CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY] GO ALTER TABLE [dbo].[searches] ADD diff --git a/bin/updatedb.sh b/bin/updatedb.sh index 1f5e18434..f504634f1 100755 --- a/bin/updatedb.sh +++ b/bin/updatedb.sh @@ -146,20 +146,9 @@ function update_db_schema($package, $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(fix_table_names($sql)); - $sql = ''; - if ($error = $DB->is_error()) { - return $error; - } - } + if ($sql = file_get_contents($file)) { + if (!$DB->exec_script($sql)) { + return $DB->is_error(); } } @@ -185,51 +174,4 @@ function update_db_schema($package, $version, $file) return $DB->is_error(); } -function fix_table_names($sql) -{ - 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); - - // 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/installer/rcube_install.php b/installer/rcube_install.php index 2fae3c5ca..ef7000688 100644 --- a/installer/rcube_install.php +++ b/installer/rcube_install.php @@ -724,7 +724,8 @@ class rcube_install // read schema file from /SQL/* $fname = INSTALL_PATH . "SQL/$engine.initial.sql"; if ($sql = @file_get_contents($fname)) { - $this->exec_sql($sql, $DB); + $DB->set_option('table_prefix', $this->config['db_prefix']); + $DB->exec_script($sql); } else { $this->fail('DB Schema', "Cannot read the schema file: $fname"); @@ -758,63 +759,6 @@ class rcube_install } - /** - * Execute the given SQL queries on the database connection - * - * @param string SQL queries to execute - * @param object rcube_db Database connection - * @return boolen True on success, False on error - */ - 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) == '') - continue; - - $buff .= $line . "\n"; - if (preg_match('/(;|^GO)$/', trim($line))) { - $DB->query($buff); - $buff = ''; - if ($DB->is_error()) - break; - } - } - - return !$DB->is_error(); - } - - - /** - * 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 5d28d5f57..40b052bdc 100644 --- a/installer/test.php +++ b/installer/test.php @@ -115,6 +115,7 @@ $db_working = false; if ($RCI->configured) { if (!empty($RCI->config['db_dsnw'])) { $DB = rcube_db::factory($RCI->config['db_dsnw'], '', false); + $DB->set_debug((bool)$RCI->config['sql_debug']); $DB->db_connect('w'); if (!($db_error_msg = $DB->is_error())) { diff --git a/program/lib/Roundcube/rcube_db.php b/program/lib/Roundcube/rcube_db.php index 2828f26ee..a46df97d3 100644 --- a/program/lib/Roundcube/rcube_db.php +++ b/program/lib/Roundcube/rcube_db.php @@ -31,7 +31,6 @@ class rcube_db protected $db_dsnr; // DSN for read operations protected $db_connected = false; // Already connected ? protected $db_mode; // Connection mode - protected $db_table_dsn_map = array(); protected $dbh; // Connection handle protected $dbhs = array(); protected $table_connections = array(); @@ -100,12 +99,15 @@ class rcube_db $this->db_dsnw = $db_dsnw; $this->db_dsnr = $db_dsnr; $this->db_pconn = $pconn; - $this->db_dsnw_noread = rcube::get_instance()->config->get('db_dsnw_noread', false); $this->db_dsnw_array = self::parse_dsn($db_dsnw); $this->db_dsnr_array = self::parse_dsn($db_dsnr); - $this->db_table_dsn_map = array_map(array($this, 'table_name'), rcube::get_instance()->config->get('db_table_dsn', array())); + $config = rcube::get_instance()->config; + + $this->options['table_prefix'] = $config->get('db_prefix'); + $this->options['dsnw_noread'] = $config->get('db_dsnw_noread', false); + $this->options['table_dsn_map'] = array_map(array($this, 'table_name'), $config->get('db_table_dsn', array())); } /** @@ -206,7 +208,7 @@ class rcube_db // Already connected if ($this->db_connected) { // connected to db with the same or "higher" mode (if allowed) - if ($this->db_mode == $mode || $this->db_mode == 'w' && !$force && !$this->db_dsnw_noread) { + if ($this->db_mode == $mode || $this->db_mode == 'w' && !$force && !$this->options['dsnw_noread']) { return; } } @@ -241,14 +243,14 @@ class rcube_db $table = $m[2]; // always use direct mapping - if ($this->db_table_dsn_map[$table]) { - $mode = $this->db_table_dsn_map[$table]; + if ($this->options['table_dsn_map'][$table]) { + $mode = $this->options['table_dsn_map'][$table]; break; // primary table rules } else if ($mode == 'r') { // connected to db with the same or "higher" mode for this table $db_mode = $this->table_connections[$table]; - if ($db_mode == 'w' && !$this->db_dsnw_noread) { + if ($db_mode == 'w' && !$this->options['dsnw_noread']) { $mode = $db_mode; } } @@ -920,14 +922,8 @@ class rcube_db */ public function table_name($table) { - static $rcube; - - if (!$rcube) { - $rcube = rcube::get_instance(); - } - // add prefix to the table name if configured - if (($prefix = $rcube->config->get('db_prefix')) && strpos($table, $prefix) !== 0) { + if (($prefix = $this->options['table_prefix']) && strpos($table, $prefix) !== 0) { return $prefix . $table; } @@ -953,7 +949,7 @@ class rcube_db */ public function set_table_dsn($table, $mode) { - $this->db_table_dsn_map[$this->table_name($table)] = $mode; + $this->options['table_dsn_map'][$this->table_name($table)] = $mode; } /** @@ -1129,4 +1125,61 @@ class rcube_db return $result; } + + /** + * Execute the given SQL script + * + * @param string SQL queries to execute + * + * @return boolen True on success, False on error + */ + public function exec_script($sql) + { + $sql = $this->fix_table_names($sql); + $buff = ''; + + foreach (explode("\n", $sql) as $line) { + if (preg_match('/^--/', $line) || trim($line) == '') + continue; + + $buff .= $line . "\n"; + if (preg_match('/(;|^GO)$/', trim($line))) { + $this->query($buff); + $buff = ''; + if ($this->db_error) { + break; + } + } + } + + return !$this->db_error; + } + + /** + * Parse SQL file and fix table names according to table prefix + */ + protected function fix_table_names($sql) + { + if (!$this->options['table_prefix']) { + return $sql; + } + + $sql = preg_replace_callback( + '/((TABLE|TRUNCATE|(?options['table_prefix'] . $matches[count($matches)-1]; + } } diff --git a/program/lib/Roundcube/rcube_db_mssql.php b/program/lib/Roundcube/rcube_db_mssql.php index 726e4b421..4138b1489 100644 --- a/program/lib/Roundcube/rcube_db_mssql.php +++ b/program/lib/Roundcube/rcube_db_mssql.php @@ -167,4 +167,24 @@ class rcube_db_mssql extends rcube_db return $result; } + + /** + * Parse SQL file and fix table names according to table prefix + */ + protected function fix_table_names($sql) + { + if (!$this->options['table_prefix']) { + return $sql; + } + + // replace sequence names, and other postgres-specific commands + $sql = preg_replace_callback( + '/((TABLE|(?_seq $sequence = $table . '_seq'; - $rcube = rcube::get_instance(); - // return sequence name if configured - if ($prefix = $rcube->config->get('db_prefix')) { + // modify sequence name if prefix is configured + if ($prefix = $this->options['table_prefix']) { return $prefix . $sequence; } @@ -190,4 +189,24 @@ class rcube_db_pgsql extends rcube_db return $result; } + /** + * Parse SQL file and fix table names according to table prefix + */ + protected function fix_table_names($sql) + { + if (!$this->options['table_prefix']) { + return $sql; + } + + $sql = parent::fix_table_names($sql); + + // replace sequence names, and other postgres-specific commands + $sql = preg_replace_callback( + '/((SEQUENCE |RENAME TO |nextval\()["\']*)([^"\' \r\n]+)/', + array($this, 'fix_table_names_callback'), + $sql + ); + + return $sql; + } } diff --git a/program/lib/Roundcube/rcube_db_sqlsrv.php b/program/lib/Roundcube/rcube_db_sqlsrv.php index 4339f3dfd..7b64ccea2 100644 --- a/program/lib/Roundcube/rcube_db_sqlsrv.php +++ b/program/lib/Roundcube/rcube_db_sqlsrv.php @@ -24,126 +24,8 @@ * @package Framework * @subpackage Database */ -class rcube_db_sqlsrv extends rcube_db +class rcube_db_sqlsrv extends rcube_db_mssql { - public $db_provider = 'mssql'; - - /** - * Object constructor - * - * @param string $db_dsnw DSN for read/write operations - * @param string $db_dsnr Optional DSN for read only operations - * @param bool $pconn Enables persistent connections - */ - public function __construct($db_dsnw, $db_dsnr = '', $pconn = false) - { - parent::__construct($db_dsnw, $db_dsnr, $pconn); - - $this->options['identifier_start'] = '['; - $this->options['identifier_end'] = ']'; - } - - /** - * Driver-specific configuration of database connection - * - * @param array $dsn DSN for DB connections - * @param PDO $dbh Connection handler - */ - protected function conn_configure($dsn, $dbh) - { - // Set date format in case of non-default language (#1488918) - $dbh->query("SET DATEFORMAT ymd"); - } - - /** - * Return SQL function for current time and date - * - * @param int $interval Optional interval (in seconds) to add/subtract - * - * @return string SQL function to use in query - */ - public function now($interval = 0) - { - if ($interval) { - $interval = intval($interval); - return "dateadd(second, $interval, getdate())"; - } - - return "getdate()"; - } - - /** - * Return SQL statement to convert a field value into a unix timestamp - * - * This method is deprecated and should not be used anymore due to limitations - * of timestamp functions in Mysql (year 2038 problem) - * - * @param string $field Field name - * - * @return string SQL statement to use in query - * @deprecated - */ - public function unixtimestamp($field) - { - return "DATEDIFF(second, '19700101', $field) + DATEDIFF(second, GETDATE(), GETUTCDATE())"; - } - - /** - * Abstract SQL statement for value concatenation - * - * @return string SQL statement to be used in query - */ - public function concat(/* col1, col2, ... */) - { - $args = func_get_args(); - - if (is_array($args[0])) { - $args = $args[0]; - } - - return '(' . join('+', $args) . ')'; - } - - /** - * Adds TOP (LIMIT,OFFSET) clause to the query - * - * @param string $query SQL query - * @param int $limit Number of rows - * @param int $offset Offset - * - * @return string SQL query - */ - protected function set_limit($query, $limit = 0, $offset = 0) - { - $limit = intval($limit); - $offset = intval($offset); - $end = $offset + $limit; - - // query without OFFSET - if (!$offset) { - $query = preg_replace('/^SELECT\s/i', "SELECT TOP $limit ", $query); - return $query; - } - - $orderby = stristr($query, 'ORDER BY'); - $offset += 1; - - if ($orderby !== false) { - $query = trim(substr($query, 0, -1 * strlen($orderby))); - } - else { - // it shouldn't happen, paging without sorting has not much sense - // @FIXME: I don't know how to build paging query without ORDER BY - $orderby = "ORDER BY 1"; - } - - $query = preg_replace('/^SELECT\s/i', '', $query); - $query = "WITH paging AS (SELECT ROW_NUMBER() OVER ($orderby) AS [RowNumber], $query)" - . " SELECT * FROM paging WHERE [RowNumber] BETWEEN $offset AND $end ORDER BY [RowNumber]"; - - return $query; - } - /** * Returns PDO DSN string from DSN array */ @@ -158,6 +40,7 @@ class rcube_db_sqlsrv extends rcube_db if ($dsn['port']) { $host .= ',' . $dsn['port']; } + $params[] = 'Server=' . $host; } -- cgit v1.2.3