6 commits - plugins/calendar plugins/libkolab plugins/tasklist

Aleksander Machniak machniak at kolabsys.com
Mon Sep 22 09:17:58 CEST 2014


 plugins/calendar/drivers/kolab/SQL/oracle.initial.sql    |   31 ++
 plugins/calendar/drivers/kolab/kolab_driver.php          |   36 +-
 plugins/calendar/lib/calendar_itip.php                   |   16 -
 plugins/libkolab/SQL/oracle.initial.sql                  |  184 +++++++++++++++
 plugins/libkolab/lib/kolab_storage.php                   |    4 
 plugins/libkolab/lib/kolab_storage_cache.php             |  107 ++++++--
 plugins/tasklist/drivers/kolab/tasklist_kolab_driver.php |   24 -
 7 files changed, 329 insertions(+), 73 deletions(-)

New commits:
commit ff60323e9029ff7c99662c7631ad6dc72f80df11
Merge: aa5f400 fbe1759
Author: Aleksander Machniak <machniak at kolabsys.com>
Date:   Mon Sep 22 09:17:51 2014 +0200

    Merge branch 'oracle'



commit fbe1759c00984d4c5a99928b6a26216150ed4a2a
Author: Aleksander Machniak <machniak at kolabsys.com>
Date:   Thu Sep 18 15:24:16 2014 +0200

    Skip multifolder insert for Oracle, we can't put long data inline

diff --git a/plugins/libkolab/lib/kolab_storage_cache.php b/plugins/libkolab/lib/kolab_storage_cache.php
index 913005b..4f09e0f 100644
--- a/plugins/libkolab/lib/kolab_storage_cache.php
+++ b/plugins/libkolab/lib/kolab_storage_cache.php
@@ -789,6 +789,40 @@ class kolab_storage_cache
         $line = '';
         if ($object) {
             $sql_data = $this->_serialize($object);
+
+            // Skip multifolder insert for Oracle, we can't put long data inline
+            if ($this->db->db_provider == 'oracle') {
+                $extra_cols = '';
+                if ($this->extra_cols) {
+                    $extra_cols = array_map(function($n) { return "`{$n}`"; }, $this->extra_cols);
+                    $extra_cols = ', ' . join(', ', $extra_cols);
+                    $extra_args = str_repeat(', ?', count($this->extra_cols));
+                }
+
+                $params = array($this->folder_id, $msguid, $object['uid'], $sql_data['changed'],
+                    $sql_data['data'], $sql_data['xml'], $sql_data['tags'], $sql_data['words']);
+
+                foreach ($this->extra_cols as $col) {
+                    $params[] = $sql_data[$col];
+                }
+
+                $result = $this->db->query(
+                    "INSERT INTO `{$this->cache_table}` "
+                    . " (`folder_id`, `msguid`, `uid`, `created`, `changed`, `data`, `xml`, `tags`, `words` $extra_cols)"
+                    . " VALUES (?, ?, ?, " . $this->db->now() . ", ?, ?, ?, ?, ? $extra_args)",
+                    $params
+                );
+
+                if (!$this->db->affected_rows($result)) {
+                    rcube::raise_error(array(
+                        'code' => 900, 'type' => 'php',
+                        'message' => "Failed to write to kolab cache"
+                    ), true);
+                }
+
+                return;
+            }
+
             $values = array(
                 $this->db->quote($this->folder_id),
                 $this->db->quote($msguid),


commit e9e871a43f4a1ff5095a438e2ccd5d1016c6a37b
Author: Aleksander Machniak <machniak at kolabsys.com>
Date:   Wed Sep 17 19:50:26 2014 +0200

    Quote columns in ORDER BY

diff --git a/plugins/libkolab/lib/kolab_storage_cache.php b/plugins/libkolab/lib/kolab_storage_cache.php
index 25b45ce..913005b 100644
--- a/plugins/libkolab/lib/kolab_storage_cache.php
+++ b/plugins/libkolab/lib/kolab_storage_cache.php
@@ -593,7 +593,7 @@ class kolab_storage_cache
     public function set_order_by($sortcols)
     {
         if (!empty($sortcols)) {
-            $this->order_by = join(', ', (array)$sortcols);
+            $this->order_by = '`' . join('`, `', (array)$sortcols) . '`';
         }
         else {
             $this->order_by = null;


commit 9e49c5c83bca4a2b18a490c54b6568bbaa9a4e8d
Author: Aleksander Machniak <machniak at kolabsys.com>
Date:   Wed Sep 17 18:06:17 2014 +0200

    Quote also column aliases in sql tables, otherwise they will be returned uppercase in Oracle

diff --git a/plugins/libkolab/lib/kolab_storage_cache.php b/plugins/libkolab/lib/kolab_storage_cache.php
index c03554b..25b45ce 100644
--- a/plugins/libkolab/lib/kolab_storage_cache.php
+++ b/plugins/libkolab/lib/kolab_storage_cache.php
@@ -468,7 +468,7 @@ class kolab_storage_cache
 
             // fetch full object data on one query if a small result set is expected
             $fetchall = !$uids && ($this->limit ? $this->limit[0] : $this->count($query)) < 500;
-            $sql_query = "SELECT " . ($fetchall ? '*' : '`msguid` AS _msguid, `uid`') . " FROM `{$this->cache_table}` ".
+            $sql_query = "SELECT " . ($fetchall ? '*' : '`msguid` AS `_msguid`, `uid`') . " FROM `{$this->cache_table}` ".
                          "WHERE `folder_id` = ? " . $this->_sql_where($query);
             if (!empty($this->order_by)) {
                 $sql_query .= ' ORDER BY ' . $this->order_by;
@@ -551,7 +551,7 @@ class kolab_storage_cache
             $this->_read_folder_data();
 
             $sql_result = $this->db->query(
-                "SELECT COUNT(*) AS numrows FROM `{$this->cache_table}` ".
+                "SELECT COUNT(*) AS `numrows` FROM `{$this->cache_table}` ".
                 "WHERE `folder_id` = ?" . $this->_sql_where($query),
                 $this->folder_id
             );


commit d2e7c27bf4e44036def6592b772e220c13697606
Author: Aleksander Machniak <machniak at kolabsys.com>
Date:   Mon Sep 15 12:26:03 2014 +0200

    Initial DDL for Oracle

diff --git a/plugins/calendar/drivers/kolab/SQL/oracle.initial.sql b/plugins/calendar/drivers/kolab/SQL/oracle.initial.sql
new file mode 100644
index 0000000..d6d882b
--- /dev/null
+++ b/plugins/calendar/drivers/kolab/SQL/oracle.initial.sql
@@ -0,0 +1,31 @@
+/**
+ * Roundcube Calendar Kolab backend
+ *
+ * @author Aleksander Machniak
+ * @licence GNU AGPL
+ **/
+
+CREATE TABLE "kolab_alarms" (
+    "alarm_id" varchar(255) NOT NULL PRIMARY KEY,
+    "user_id" integer NOT NULL
+        REFERENCES "users" ("user_id") ON DELETE CASCADE,
+    "notifyat" timestamp DEFAULT NULL,
+    "dismissed" smallint DEFAULT 0 NOT NULL
+);
+
+CREATE INDEX "kolab_alarms_user_id_idx" ON "kolab_alarms" ("user_id");
+
+
+CREATE TABLE "itipinvitations" (
+    "token" varchar(64) NOT NULL PRIMARY KEY,
+    "event_uid" varchar(255) NOT NULL,
+    "user_id" integer NOT NULL
+        REFERENCES "users" ("user_id") ON DELETE CASCADE,
+    "event" long NOT NULL,
+    "expires" timestamp DEFAULT NULL,
+    "cancelled" smallint DEFAULT 0 NOT NULL
+);
+
+CREATE INDEX "itipinvitations_user_id_idx" ON "itipinvitations" ("user_id", "event_uid");
+
+INSERT INTO "system" ("name", "value") VALUES ('calendar-kolab-version', '2014041700');
diff --git a/plugins/libkolab/SQL/oracle.initial.sql b/plugins/libkolab/SQL/oracle.initial.sql
new file mode 100644
index 0000000..2c078cb
--- /dev/null
+++ b/plugins/libkolab/SQL/oracle.initial.sql
@@ -0,0 +1,184 @@
+/**
+ * libkolab database schema
+ *
+ * @version 1.1
+ * @author Aleksander Machniak
+ * @licence GNU AGPL
+ **/
+
+
+CREATE TABLE "kolab_folders" (
+    "folder_id" number NOT NULL PRIMARY KEY,
+    "resource" VARCHAR(255) NOT NULL,
+    "type" VARCHAR(32) NOT NULL,
+    "synclock" integer DEFAULT 0 NOT NULL,
+    "ctag" VARCHAR(40) DEFAULT NULL
+);
+
+CREATE INDEX "kolab_folders_resource_idx" ON "kolab_folders" ("resource", "type");
+
+CREATE SEQUENCE "kolab_folders_seq"
+    START WITH 1 INCREMENT BY 1 NOMAXVALUE;
+
+CREATE TRIGGER "kolab_folders_seq_trig"
+BEFORE INSERT ON "kolab_folders" FOR EACH ROW
+BEGIN
+    :NEW."folder_id" := "kolab_folders_seq".nextval;
+END;
+
+
+CREATE TABLE "kolab_cache_contact" (
+    "folder_id" number NOT NULL
+        REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+    "msguid" number NOT NULL,
+    "uid" varchar(128) NOT NULL,
+    "created" timestamp DEFAULT NULL,
+    "changed" timestamp DEFAULT NULL,
+    "data" clob NOT NULL,
+    "xml" clob NOT NULL,
+    "tags" varchar(255) DEFAULT NULL,
+    "words" clob DEFAULT NULL,
+    "type" varchar(32) NOT NULL,
+    "name" varchar(255) DEFAULT NULL,
+    "firstname" varchar(255) DEFAULT NULL,
+    "surname" varchar(255) DEFAULT NULL,
+    "email" varchar(255) DEFAULT NULL,
+    PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_contact_type_idx" ON "kolab_cache_contact" ("folder_id", "type");
+CREATE INDEX "kolab_cache_contact_uid2msguid" ON "kolab_cache_contact" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_event" (
+    "folder_id" number NOT NULL
+        REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+    "msguid" number NOT NULL,
+    "uid" varchar(128) NOT NULL,
+    "created" timestamp DEFAULT NULL,
+    "changed" timestamp DEFAULT NULL,
+    "data" clob NOT NULL,
+    "xml" clob NOT NULL,
+    "tags" varchar(255) DEFAULT NULL,
+    "words" clob DEFAULT NULL,
+    "dtstart" timestamp DEFAULT NULL,
+    "dtend" timestamp DEFAULT NULL,
+    PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_event_uid2msguid" ON "kolab_cache_event" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_task" (
+    "folder_id" number NOT NULL
+        REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+    "msguid" number NOT NULL,
+    "uid" varchar(128) NOT NULL,
+    "created" timestamp DEFAULT NULL,
+    "changed" timestamp DEFAULT NULL,
+    "data" clob NOT NULL,
+    "xml" clob NOT NULL,
+    "tags" varchar(255) DEFAULT NULL,
+    "words" clob DEFAULT NULL,
+    "dtstart" timestamp DEFAULT NULL,
+    "dtend" timestamp DEFAULT NULL,
+    PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_task_uid2msguid" ON "kolab_cache_task" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_journal" (
+    "folder_id" number NOT NULL
+        REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+    "msguid" number NOT NULL,
+    "uid" varchar(128) NOT NULL,
+    "created" timestamp DEFAULT NULL,
+    "changed" timestamp DEFAULT NULL,
+    "data" clob NOT NULL,
+    "xml" clob NOT NULL,
+    "tags" varchar(255) DEFAULT NULL,
+    "words" clob DEFAULT NULL,
+    "dtstart" timestamp DEFAULT NULL,
+    "dtend" timestamp DEFAULT NULL,
+    PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_journal_uid2msguid" ON "kolab_cache_journal" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_note" (
+    "folder_id" number NOT NULL
+        REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+    "msguid" number NOT NULL,
+    "uid" varchar(128) NOT NULL,
+    "created" timestamp DEFAULT NULL,
+    "changed" timestamp DEFAULT NULL,
+    "data" clob NOT NULL,
+    "xml" clob NOT NULL,
+    "tags" varchar(255) DEFAULT NULL,
+    "words" clob DEFAULT NULL,
+    PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_note_uid2msguid" ON "kolab_cache_note" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_file" (
+    "folder_id" number NOT NULL
+        REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+    "msguid" number NOT NULL,
+    "uid" varchar(128) NOT NULL,
+    "created" timestamp DEFAULT NULL,
+    "changed" timestamp DEFAULT NULL,
+    "data" clob NOT NULL,
+    "xml" clob NOT NULL,
+    "tags" varchar(255) DEFAULT NULL,
+    "words" clob DEFAULT NULL,
+    "filename" varchar(255) DEFAULT NULL,
+    PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_file_filename" ON "kolab_cache_file" ("folder_id", "filename");
+CREATE INDEX "kolab_cache_file_uid2msguid" ON "kolab_cache_file" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_configuration" (
+    "folder_id" number NOT NULL
+        REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+    "msguid" number NOT NULL,
+    "uid" varchar(128) NOT NULL,
+    "created" timestamp DEFAULT NULL,
+    "changed" timestamp DEFAULT NULL,
+    "data" clob NOT NULL,
+    "xml" clob NOT NULL,
+    "tags" varchar(255) DEFAULT NULL,
+    "words" clob DEFAULT NULL,
+    "type" varchar(32) NOT NULL,
+    PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_config_type" ON "kolab_cache_configuration" ("folder_id", "type");
+CREATE INDEX "kolab_cache_config_uid2msguid" ON "kolab_cache_configuration" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_freebusy" (
+    "folder_id" number NOT NULL
+        REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+    "msguid" number NOT NULL,
+    "uid" varchar(128) NOT NULL,
+    "created" timestamp DEFAULT NULL,
+    "changed" timestamp DEFAULT NULL,
+    "data" clob NOT NULL,
+    "xml" clob NOT NULL,
+    "tags" varchar(255) DEFAULT NULL,
+    "words" clob DEFAULT NULL,
+    "dtstart" timestamp DEFAULT NULL,
+    "dtend" timestamp DEFAULT NULL,
+    PRIMARY KEY("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_fb_uid2msguid" ON "kolab_cache_freebusy" ("folder_id", "uid", "msguid");
+
+
+INSERT INTO "system" ("name", "value") VALUES ('libkolab-version', '2014021000');


commit 788635b2876b2db1e5d66046a0bdfa729a016afd
Author: Aleksander Machniak <machniak at kolabsys.com>
Date:   Mon Sep 15 12:23:46 2014 +0200

    Use consistent column/table quoting in sql queries

diff --git a/plugins/calendar/drivers/kolab/kolab_driver.php b/plugins/calendar/drivers/kolab/kolab_driver.php
index 6575a23..938bcee 100644
--- a/plugins/calendar/drivers/kolab/kolab_driver.php
+++ b/plugins/calendar/drivers/kolab/kolab_driver.php
@@ -1081,14 +1081,12 @@ class kolab_driver extends calendar_driver
     // get alarm information stored in local database
     if (!empty($candidates)) {
       $alarm_ids = array_map(array($this->rc->db, 'quote'), array_keys($candidates));
-      $result = $this->rc->db->query(sprintf(
-          "SELECT * FROM " . $this->rc->db->table_name('kolab_alarms') . "
-           WHERE alarm_id IN (%s) AND user_id=?",
-           join(',', $alarm_ids),
-           $this->rc->db->now()
-          ),
-          $this->rc->user->ID
-       );
+      $result = $this->rc->db->query("SELECT *"
+        . " FROM " . $this->rc->db->table_name('kolab_alarms', true)
+        . " WHERE `alarm_id` IN (" . join(',', $alarm_ids) . ")"
+          . " AND `user_id` = ?",
+        $this->rc->user->ID
+      );
 
       while ($result && ($e = $this->rc->db->fetch_assoc($result))) {
         $dbdata[$e['alarm_id']] = $e;
@@ -1117,27 +1115,26 @@ class kolab_driver extends calendar_driver
    */
   public function dismiss_alarm($alarm_id, $snooze = 0)
   {
+    $alarms_table = $this->rc->db->table_name('kolab_alarms', true);
     // delete old alarm entry
-    $this->rc->db->query(
-      "DELETE FROM " . $this->rc->db->table_name('kolab_alarms') . "
-       WHERE alarm_id=? AND user_id=?",
-       $alarm_id,
-       $this->rc->user->ID
+    $this->rc->db->query("DELETE FROM $alarms_table"
+      . " WHERE `alarm_id` = ? AND `user_id` = ?",
+      $alarm_id,
+      $this->rc->user->ID
     );
 
     // set new notifyat time or unset if not snoozed
     $notifyat = $snooze > 0 ? date('Y-m-d H:i:s', time() + $snooze) : null;
 
-    $query = $this->rc->db->query(
-      "INSERT INTO " . $this->rc->db->table_name('kolab_alarms') . "
-       (alarm_id, user_id, dismissed, notifyat)
-       VALUES(?, ?, ?, ?)",
+    $query = $this->rc->db->query("INSERT INTO $alarms_table"
+      . " (`alarm_id`, `user_id`, `dismissed`, `notifyat`)"
+      . " VALUES (?, ?, ?, ?)",
       $alarm_id,
       $this->rc->user->ID,
       $snooze > 0 ? 0 : 1,
       $notifyat
     );
-    
+
     return $this->rc->db->affected_rows($query);
   }
 
@@ -1792,7 +1789,8 @@ class kolab_driver extends calendar_driver
   {
     $db = $this->rc->get_dbh();
     foreach (array('kolab_alarms', 'itipinvitations') as $table) {
-      $db->query("DELETE FROM " . $this->rc->db->table_name($table) . " WHERE user_id=?", $args['user']->ID);
+      $db->query("DELETE FROM " . $this->rc->db->table_name($table, true)
+        . " WHERE `user_id` = ?", $args['user']->ID);
     }
   }
 }
diff --git a/plugins/calendar/lib/calendar_itip.php b/plugins/calendar/lib/calendar_itip.php
index de40122..5622372 100644
--- a/plugins/calendar/lib/calendar_itip.php
+++ b/plugins/calendar/lib/calendar_itip.php
@@ -35,7 +35,7 @@ class calendar_itip extends libcalendaring_itip
   {
     parent::__construct($plugin, $domain);
 
-    $this->db_itipinvitations = $this->rc->db->table_name('itipinvitations');
+    $this->db_itipinvitations = $this->rc->db->table_name('itipinvitations', true);
   }
 
   /**
@@ -61,7 +61,7 @@ class calendar_itip extends libcalendaring_itip
   public function get_invitation($token)
   {
     if ($parts = $this->decode_token($token)) {
-      $result = $this->rc->db->query("SELECT * FROM $this->db_itipinvitations WHERE token=?", $parts['base']);
+      $result = $this->rc->db->query("SELECT * FROM $this->db_itipinvitations WHERE `token` = ?", $parts['base']);
       if ($result && ($rec = $this->rc->db->fetch_assoc($result))) {
         $rec['event'] = unserialize($rec['event']);
         $rec['attendee'] = $parts['attendee'];
@@ -113,8 +113,8 @@ class calendar_itip extends libcalendaring_itip
       // update record in DB
       $query = $this->rc->db->query(
         "UPDATE $this->db_itipinvitations
-         SET event=?
-         WHERE token=?",
+         SET `event` = ?
+         WHERE `token` = ?",
         self::serialize_event($invitation['event']),
         $invitation['token']
       );
@@ -150,11 +150,11 @@ class calendar_itip extends libcalendaring_itip
       return $token;
 
     // delete old entry
-    $this->rc->db->query("DELETE FROM $this->db_itipinvitations WHERE token=?", $base);
+    $this->rc->db->query("DELETE FROM $this->db_itipinvitations WHERE `token` = ?", $base);
 
     $query = $this->rc->db->query(
       "INSERT INTO $this->db_itipinvitations
-       (token, event_uid, user_id, event, expires)
+       (`token`, `event_uid`, `user_id`, `event`, `expires`)
        VALUES(?, ?, ?, ?, ?)",
       $base,
       $event['uid'],
@@ -181,8 +181,8 @@ class calendar_itip extends libcalendaring_itip
     // flag invitation record as cancelled
     $this->rc->db->query(
       "UPDATE $this->db_itipinvitations
-       SET cancelled=1
-       WHERE event_uid=? AND user_id=?",
+       SET `cancelled` = 1
+       WHERE `event_uid` = ? AND `user_id` = ?",
        $event['uid'],
        $this->rc->user->ID
     );
diff --git a/plugins/libkolab/lib/kolab_storage.php b/plugins/libkolab/lib/kolab_storage.php
index 61c8208..dfd1887 100644
--- a/plugins/libkolab/lib/kolab_storage.php
+++ b/plugins/libkolab/lib/kolab_storage.php
@@ -1562,8 +1562,6 @@ class kolab_storage
     {
         $db = rcmail::get_instance()->get_dbh();
         $prefix = 'imap://' . urlencode($args['username']) . '@' . $args['host'] . '/%';
-        $db->query("DELETE FROM " . $db->table_name('kolab_folders') . " WHERE resource LIKE ?", $prefix);
+        $db->query("DELETE FROM " . $db->table_name('kolab_folders', true) . " WHERE `resource` LIKE ?", $prefix);
     }
-
 }
-
diff --git a/plugins/libkolab/lib/kolab_storage_cache.php b/plugins/libkolab/lib/kolab_storage_cache.php
index eec058d..c03554b 100644
--- a/plugins/libkolab/lib/kolab_storage_cache.php
+++ b/plugins/libkolab/lib/kolab_storage_cache.php
@@ -96,8 +96,8 @@ class kolab_storage_cache
      */
     public function select_by_id($folder_id)
     {
-        $folders_table = $this->db->table_name('kolab_folders');
-        $sql_arr = $this->db->fetch_assoc($this->db->query("SELECT * FROM $folders_table WHERE folder_id=?", $folder_id));
+        $folders_table = $this->db->table_name('kolab_folders', true);
+        $sql_arr = $this->db->fetch_assoc($this->db->query("SELECT * FROM $folders_table WHERE `folder_id` = ?", $folder_id));
         if ($sql_arr) {
             $this->metadata = $sql_arr;
             $this->folder_id = $sql_arr['folder_id'];
@@ -188,7 +188,7 @@ class kolab_storage_cache
 
                     // read cache index
                     $sql_result = $this->db->query(
-                        "SELECT msguid, uid FROM $this->cache_table WHERE folder_id=?",
+                        "SELECT `msguid`, `uid` FROM `{$this->cache_table}` WHERE `folder_id` = ?",
                         $this->folder_id
                     );
 
@@ -211,7 +211,7 @@ class kolab_storage_cache
                     if (!empty($del_index)) {
                         $quoted_ids = join(',', array_map(array($this->db, 'quote'), $del_index));
                         $this->db->query(
-                            "DELETE FROM $this->cache_table WHERE folder_id=? AND msguid IN ($quoted_ids)",
+                            "DELETE FROM `{$this->cache_table}` WHERE `folder_id` = ? AND `msguid` IN ($quoted_ids)",
                             $this->folder_id
                         );
                     }
@@ -252,8 +252,8 @@ class kolab_storage_cache
                 $this->_read_folder_data();
 
                 $sql_result = $this->db->query(
-                    "SELECT * FROM $this->cache_table ".
-                    "WHERE folder_id=? AND msguid=?",
+                    "SELECT * FROM `{$this->cache_table}` ".
+                    "WHERE `folder_id` = ? AND `msguid` = ?",
                     $this->folder_id,
                     $msguid
                 );
@@ -298,7 +298,7 @@ class kolab_storage_cache
         // remove old entry
         if ($this->ready) {
             $this->_read_folder_data();
-            $this->db->query("DELETE FROM $this->cache_table WHERE folder_id=? AND msguid=?",
+            $this->db->query("DELETE FROM `{$this->cache_table}` WHERE `folder_id` = ? AND `msguid` = ?",
                 $this->folder_id, $msguid);
         }
 
@@ -345,13 +345,13 @@ class kolab_storage_cache
                     $cols[$idx] = "$col = ?";
                 }
 
-                $query = "UPDATE $this->cache_table SET " . implode(', ', $cols)
-                    . " WHERE folder_id = ? AND msguid = ?";
+                $query = "UPDATE `{$this->cache_table}` SET " . implode(', ', $cols)
+                    . " WHERE `folder_id` = ? AND `msguid` = ?";
                 $args[] = $this->folder_id;
                 $args[] = $olduid;
             }
             else {
-                $query = "INSERT INTO $this->cache_table (created, " . implode(', ', $cols)
+                $query = "INSERT INTO `{$this->cache_table}` (`created`, " . implode(', ', $cols)
                     . ") VALUES (" . $this->db->now() . str_repeat(', ?', count($cols)) . ")";
             }
 
@@ -388,8 +388,8 @@ class kolab_storage_cache
                 $this->_read_folder_data();
 
                 $this->db->query(
-                    "UPDATE $this->cache_table SET folder_id=?, msguid=? ".
-                    "WHERE folder_id=? AND msguid=?",
+                    "UPDATE `{$this->cache_table}` SET `folder_id` = ?, `msguid` = ? ".
+                    "WHERE `folder_id` = ? AND `msguid` = ?",
                     $target->cache->get_folder_id(),
                     $new_msguid,
                     $this->folder_id,
@@ -421,7 +421,7 @@ class kolab_storage_cache
         $this->_read_folder_data();
 
         $result = $this->db->query(
-            "DELETE FROM $this->cache_table WHERE folder_id=?",
+            "DELETE FROM `{$this->cache_table}` WHERE `folder_id` = ?",
             $this->folder_id
         );
 
@@ -443,8 +443,8 @@ class kolab_storage_cache
 
         // resolve new message UID in target folder
         $this->db->query(
-            "UPDATE $this->folders_table SET resource=? ".
-            "WHERE resource=?",
+            "UPDATE `{$this->folders_table}` SET `resource` = ? ".
+            "WHERE `resource` = ?",
             $target->get_resource_uri(),
             $this->resource_uri
         );
@@ -468,8 +468,8 @@ class kolab_storage_cache
 
             // fetch full object data on one query if a small result set is expected
             $fetchall = !$uids && ($this->limit ? $this->limit[0] : $this->count($query)) < 500;
-            $sql_query = "SELECT " . ($fetchall ? '*' : 'msguid AS _msguid, uid') . " FROM $this->cache_table ".
-                         "WHERE folder_id=? " . $this->_sql_where($query);
+            $sql_query = "SELECT " . ($fetchall ? '*' : '`msguid` AS _msguid, `uid`') . " FROM `{$this->cache_table}` ".
+                         "WHERE `folder_id` = ? " . $this->_sql_where($query);
             if (!empty($this->order_by)) {
                 $sql_query .= ' ORDER BY ' . $this->order_by;
             }
@@ -551,8 +551,8 @@ class kolab_storage_cache
             $this->_read_folder_data();
 
             $sql_result = $this->db->query(
-                "SELECT COUNT(*) AS numrows FROM $this->cache_table ".
-                "WHERE folder_id=? " . $this->_sql_where($query),
+                "SELECT COUNT(*) AS numrows FROM `{$this->cache_table}` ".
+                "WHERE `folder_id` = ?" . $this->_sql_where($query),
                 $this->folder_id
             );
 
@@ -807,12 +807,18 @@ class kolab_storage_cache
         }
 
         if ($buffer && (!$msguid || (strlen($buffer) + strlen($line) > $this->max_sql_packet()))) {
-            $extra_cols = $this->extra_cols ? ', ' . join(', ', $this->extra_cols) : '';
+            $extra_cols = '';
+            if ($this->extra_cols) {
+                $extra_cols = array_map(function($n) { return "`{$n}`"; }, $this->extra_cols);
+                $extra_cols = ', ' . join(', ', $extra_cols);
+            }
+
             $result = $this->db->query(
-                "INSERT INTO $this->cache_table ".
-                " (folder_id, msguid, uid, created, changed, data, xml, tags, words $extra_cols)".
+                "INSERT INTO `{$this->cache_table}` ".
+                " (`folder_id`, `msguid`, `uid`, `created`, `changed`, `data`, `xml`, `tags`, `words` $extra_cols)".
                 " VALUES $buffer"
             );
+
             if (!$this->db->affected_rows($result)) {
                 rcube::raise_error(array(
                     'code' => 900, 'type' => 'php',
@@ -849,13 +855,20 @@ class kolab_storage_cache
         if (!empty($this->folder_id) || !$this->ready)
             return;
 
-        $sql_arr = $this->db->fetch_assoc($this->db->query("SELECT folder_id, synclock, ctag FROM $this->folders_table WHERE resource=?", $this->resource_uri));
+        $sql_arr = $this->db->fetch_assoc($this->db->query(
+                "SELECT `folder_id`, `synclock`, `ctag`"
+                . " FROM `{$this->folders_table}` WHERE `resource` = ?",
+                $this->resource_uri
+        ));
+
         if ($sql_arr) {
             $this->metadata = $sql_arr;
             $this->folder_id = $sql_arr['folder_id'];
         }
         else {
-            $this->db->query("INSERT INTO $this->folders_table (resource, type) VALUES (?, ?)", $this->resource_uri, $this->folder->type);
+            $this->db->query("INSERT INTO `{$this->folders_table}` (`resource`, `type`)"
+                . " VALUES (?, ?)", $this->resource_uri, $this->folder->type);
+
             $this->folder_id = $this->db->insert_id('kolab_folders');
             $this->metadata = array();
         }
@@ -870,7 +883,7 @@ class kolab_storage_cache
             return;
 
         $this->_read_folder_data();
-        $sql_query = "SELECT synclock, ctag FROM $this->folders_table WHERE folder_id=?";
+        $sql_query = "SELECT `synclock`, `ctag` FROM `{$this->folders_table}` WHERE `folder_id` = ?";
 
         // abort if database is not set-up
         if ($this->db->is_error()) {
@@ -887,7 +900,7 @@ class kolab_storage_cache
         }
 
         // set lock
-        $this->db->query("UPDATE $this->folders_table SET synclock = ? WHERE folder_id = ?", time(), $this->folder_id);
+        $this->db->query("UPDATE `{$this->folders_table}` SET `synclock` = ? WHERE `folder_id` = ?", time(), $this->folder_id);
     }
 
     /**
@@ -899,7 +912,7 @@ class kolab_storage_cache
             return;
 
         $this->db->query(
-            "UPDATE $this->folders_table SET synclock = 0, ctag = ? WHERE folder_id = ?",
+            "UPDATE `{$this->folders_table}` SET `synclock` = 0, `ctag` = ? WHERE `folder_id` = ?",
             $this->metadata['ctag'],
             $this->folder_id
         );
@@ -921,8 +934,8 @@ class kolab_storage_cache
             $this->_read_folder_data();
 
             $sql_result = $this->db->query(
-                "SELECT msguid FROM $this->cache_table ".
-                "WHERE folder_id=? AND uid=? ORDER BY msguid DESC",
+                "SELECT `msguid` FROM `{$this->cache_table}` ".
+                "WHERE `folder_id` = ? AND `uid` = ? ORDER BY `msguid` DESC",
                 $this->folder_id,
                 $uid
             );
diff --git a/plugins/tasklist/drivers/kolab/tasklist_kolab_driver.php b/plugins/tasklist/drivers/kolab/tasklist_kolab_driver.php
index 5af5730..2134302 100644
--- a/plugins/tasklist/drivers/kolab/tasklist_kolab_driver.php
+++ b/plugins/tasklist/drivers/kolab/tasklist_kolab_driver.php
@@ -711,12 +711,10 @@ class tasklist_kolab_driver extends tasklist_driver
         // get alarm information stored in local database
         if (!empty($candidates)) {
             $alarm_ids = array_map(array($this->rc->db, 'quote'), array_keys($candidates));
-            $result = $this->rc->db->query(sprintf(
-                "SELECT * FROM " . $this->rc->db->table_name('kolab_alarms') . "
-                 WHERE alarm_id IN (%s) AND user_id=?",
-                 join(',', $alarm_ids),
-                 $this->rc->db->now()
-                ),
+            $result = $this->rc->db->query("SELECT *"
+                . " FROM " . $this->rc->db->table_name('kolab_alarms', true)
+                . " WHERE `alarm_id` IN (" . join(',', $alarm_ids) . ")"
+                    . " AND `user_id` = ?",
                 $this->rc->user->ID
             );
 
@@ -751,8 +749,8 @@ class tasklist_kolab_driver extends tasklist_driver
     {
         // delete old alarm entry
         $this->rc->db->query(
-            "DELETE FROM " . $this->rc->db->table_name('kolab_alarms') . "
-             WHERE alarm_id=? AND user_id=?",
+            "DELETE FROM " . $this->rc->db->table_name('kolab_alarms', true) . "
+             WHERE `alarm_id` = ? AND `user_id` = ?",
             $id,
             $this->rc->user->ID
         );
@@ -761,9 +759,9 @@ class tasklist_kolab_driver extends tasklist_driver
         $notifyat = $snooze > 0 ? date('Y-m-d H:i:s', time() + $snooze) : null;
 
         $query = $this->rc->db->query(
-            "INSERT INTO " . $this->rc->db->table_name('kolab_alarms') . "
-             (alarm_id, user_id, dismissed, notifyat)
-             VALUES(?, ?, ?, ?)",
+            "INSERT INTO " . $this->rc->db->table_name('kolab_alarms', true) . "
+             (`alarm_id`, `user_id`, `dismissed`, `notifyat`)
+             VALUES (?, ?, ?, ?)",
             $id,
             $this->rc->user->ID,
             $snooze > 0 ? 0 : 1,
@@ -782,8 +780,8 @@ class tasklist_kolab_driver extends tasklist_driver
     {
         // delete alarm entry
         $this->rc->db->query(
-            "DELETE FROM " . $this->rc->db->table_name('kolab_alarms') . "
-             WHERE alarm_id=? AND user_id=?",
+            "DELETE FROM " . $this->rc->db->table_name('kolab_alarms', true) . "
+             WHERE `alarm_id` = ? AND `user_id` = ?",
             $id,
             $this->rc->user->ID
         );




More information about the commits mailing list