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 --- 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 +----------------------------- 4 files changed, 112 insertions(+), 137 deletions(-) (limited to 'program') 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