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

5 files changed, 114 insertions, 195 deletions

diff --git a/program/include/rcmail_install.php b/program/include/rcmail_install.php
index ca06f10b7..eec21ec7e 100644
--- a/program/include/rcmail_install.php
+++ b/program/include/rcmail_install.php
@@ -710,7 +710,8 @@ class rcmail_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");
@@ -745,63 +746,6 @@ class rcmail_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
    */
   function raise_error($p)
diff --git a/program/lib/Roundcube/rcube_db.php b/program/lib/Roundcube/rcube_db.php
index a2271fd6d..29f125d8a 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;
                     }
                 }
@@ -921,14 +923,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;
         }
 
@@ -954,7 +950,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;
     }
 
     /**
@@ -1130,4 +1126,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|(?<!ON )UPDATE|INSERT INTO|FROM'
+            . '| ON(?! (DELETE|UPDATE))|REFERENCES|CONSTRAINT|FOREIGN KEY|INDEX)'
+            . '\s+(IF (NOT )?EXISTS )?[`"]*)([^`"\( \r\n]+)/',
+            array($this, 'fix_table_names_callback'),
+            $sql
+        );
+
+        return $sql;
+    }
+
+    /**
+     * Preg_replace callback for fix_table_names()
+     */
+    protected function fix_table_names_callback($matches)
+    {
+        return $matches[1] . $this->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|(?<!ON )UPDATE|INSERT INTO|FROM(?! deleted)| ON(?! (DELETE|UPDATE|\[PRIMARY\]))'
+            . '|REFERENCES|CONSTRAINT|TRIGGER|INDEX)\s+(\[dbo\]\.)?[\[\]]*)([^\[\]\( \r\n]+)/',
+            array($this, 'fix_table_names_callback'),
+            $sql
+        );
+
+        return $sql;
+    }
 }
diff --git a/program/lib/Roundcube/rcube_db_pgsql.php b/program/lib/Roundcube/rcube_db_pgsql.php
index 68bf6d85d..a92d3cf36 100644
--- a/program/lib/Roundcube/rcube_db_pgsql.php
+++ b/program/lib/Roundcube/rcube_db_pgsql.php
@@ -73,10 +73,9 @@ class rcube_db_pgsql extends rcube_db
         // 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
-        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;
         }