From: Christian Weiske <cweiske@cweiske.de>
Date: Sat, 29 Dec 2018 09:28:05 +0000 (+0100)
Subject: Initial commit after working 5 evenings on it
X-Git-Tag: v0.1.0~3
X-Git-Url: https://git.cweiske.de/roundcube-nextcloud_sql_addressbook.git/commitdiff_plain/5e90878707aeb8370d1bb4a93aafc9ff154f39e6

Initial commit after working 5 evenings on it
---

5e90878707aeb8370d1bb4a93aafc9ff154f39e6
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..fc40c61
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,2 @@
+/config.inc.php
+/README.html
diff --git a/README.rst b/README.rst
new file mode 100644
index 0000000..1f0b42b
--- /dev/null
+++ b/README.rst
@@ -0,0 +1,38 @@
+***********************************************
+NextCloud SQL address book plugin for Roundcube
+***********************************************
+
+Roundcube__ plugin that allows access to NextCloud__ address books.
+Uses direct database access (SQL), which is much faster than accessing the
+address book entries via CardDAV__.
+
+__ https://roundcube.net/
+__ https://nextcloud.com/
+__ https://plugins.roundcube.net/packages/roundcube/carddav
+
+
+Features
+========
+- List all user's NextCloud address books
+- Search
+- Autocomplete
+
+Missing features
+----------------
+- Access to address books shared by other people in NextCloud
+- Access to more fields than "email" and "name" (full name)
+- Updating/adding address book entries (not planned)
+
+
+Prerequisites
+=============
+- Read-only SQL database access from Roundcube to the NextCloud database
+- Roundcube user login e-mail addresses must equal the username in NextCloud
+  (Users log in with `alice@example.org` in both Roundcube and NextCloud)
+
+
+Debugging
+=========
+If you do not see any address books:
+The address books are only found if the ``principaluri`` in the ``oc_addressbooks``
+table equals ``principals/users/`` + ``$useremailaddress``.
diff --git a/config.inc.php.dist b/config.inc.php.dist
new file mode 100644
index 0000000..20a504f
--- /dev/null
+++ b/config.inc.php.dist
@@ -0,0 +1,4 @@
+<?php
+$config['nextcloud_sql_addressbook_dsn'] = 'mysql://user:pass@localhost/dbname';
+$config['nextcloud_sql_addressbook_dbtableprefix'] = 'oc_';
+?>
diff --git a/nextcloud_sql_addressbook.php b/nextcloud_sql_addressbook.php
new file mode 100644
index 0000000..6b5bb9f
--- /dev/null
+++ b/nextcloud_sql_addressbook.php
@@ -0,0 +1,138 @@
+<?php
+require_once __DIR__ . '/nextcloud_sql_addressbook_backend.php';
+
+/**
+ * Make a user's Nextcloud address books available in Roundcube.
+ *
+ * Directly accesses the database, which is much faster than using CardDAV.
+ *
+ * @author  Christian Weiske <cweiske@cweiske.de>
+ * @license AGPLv3+ http://www.gnu.org/licenses/agpl.html
+ */
+class nextcloud_sql_addressbook extends rcube_plugin
+{
+    /**
+     * Main roundcube instance
+     *
+     * @var rcube
+     */
+    protected $rcube;
+
+    /**
+     * Database table prefix
+     *
+     * @var string
+     */
+    protected $prefix = 'oc_';
+    
+    /**
+     * Initialization method, needs to be implemented by the plugin itself
+     *
+     * @return void
+     */
+    public function init()
+    {
+        $this->load_config();
+        $this->add_hook('addressbooks_list', [$this, 'addressbooks_list']);
+        $this->add_hook('addressbook_get', [$this, 'addressbook_get']);
+
+        $this->rcube = rcube::get_instance();
+        
+        $this->db = rcube_db::factory(
+            $this->rcube->config->get('nextcloud_sql_addressbook_dsn')
+        );
+        $this->db->set_debug((bool) $this->rcube->config->get('sql_debug'));
+        
+        $this->prefix = $this->rcube->config->get(
+            'nextcloud_sql_addressbook_dbtableprefix', 'oc_'
+        );
+
+        // use this address books for autocompletion queries
+        $config = rcmail::get_instance()->config;
+        $sources = (array) $config->get(
+            'autocomplete_addressbooks', array('sql')
+        );
+        foreach ($this->listAddressbooks() as $addressBook) {
+            if (!in_array($addressBook['id'], $sources)) {
+                $sources[] = $addressBook['id'];
+            }
+        }
+        $config->set('autocomplete_addressbooks', $sources);
+    }
+
+    /**
+     * Load the nextcloud address book names
+     *
+     * The "id" may not contain any "-" because that would break "_cid",
+     * the "contact IDs" which are "$contactid-$addressbookid".
+     * See rcmail_get_cids()
+     *
+     * @param array $arguments Unknown data, with a "sources" key that we have
+     *                         to modify
+     *
+     * @return array Arguments with our address books added to the "sources" key
+     */
+    public function addressbooks_list($arguments)
+    {
+        $arguments['sources'] = array_merge(
+            $arguments['sources'], $this->listAddressbooks()
+        );
+        return $arguments;
+    }
+
+    /**
+     * Build a list of address books for the user
+     *
+     * @return array Array of arrays with the following keys:
+     *               id, name, groups, readonly, undelete, autocomplete
+     */
+    protected function listAddressbooks()
+    {
+        $principalUri = 'principals/users/'
+            . $this->rcube->user->data['username'];
+
+        $sql = 'SELECT id, displayname'
+             . ' FROM ' . $this->prefix . 'addressbooks'
+             . ' WHERE principaluri = ?'
+             . ' ORDER BY displayname';
+        $stmt = $this->db->query($sql, [$principalUri]);
+        $addressBooks = [];
+        foreach ($stmt as $row) {
+            $addressBooks[] = [
+                'id'           => 'nextcloud_' . $row['id'],
+                'name'         => $row['displayname'] . ' (Nextcloud)',
+                'groups'       => false,
+                'readonly'     => true,
+                'undelete'     => false,
+                'autocomplete' => true,
+            ];
+        }
+        return $addressBooks;
+    }
+
+    /**
+     * Return a adress book object for the given address book ID
+     *
+     * @param array $arguments Some data with an "id" key that contains the
+     *                         address book ID
+     *
+     * @return array $arguments with added "instance" key
+     */
+    public function addressbook_get($arguments)
+    {
+        $parts = explode('_', $arguments['id'], 2);
+        if (count($parts) != 2 || $parts[0] != 'nextcloud') {
+            return $arguments;
+        }
+
+        $id = $parts[1];
+        //FIXME: security check if this ID really belongs to the user
+
+        $arguments['instance'] = new nextcloud_sql_addressbook_backend(
+            $id, $this->db, $this->prefix
+        );
+        
+        return $arguments;
+    }
+}
+?>
diff --git a/nextcloud_sql_addressbook_backend.php b/nextcloud_sql_addressbook_backend.php
new file mode 100644
index 0000000..60ab38e
--- /dev/null
+++ b/nextcloud_sql_addressbook_backend.php
@@ -0,0 +1,408 @@
+<?php
+/**
+ * Address book backend accessing the NextCloud database directly.
+ *
+ * Read-only.
+ *
+ * Only returns two fields: email and name (Full name).
+ *
+ * @author  Christian Weiske <cweiske@cweiske.de>
+ * @license AGPLv3+ http://www.gnu.org/licenses/agpl.html
+ */
+class nextcloud_sql_addressbook_backend extends rcube_addressbook
+{
+    /**
+     * Nextcloud address book ID
+     *
+     * @var intger
+     */
+    protected $abId;
+
+    /**
+     * Database connection to the NextCloud database
+     *
+     * @var rcube_db
+     */
+    protected $db;
+
+    /**
+     * Database table prefix
+     *
+     * @var string
+     */
+    protected $prefix = 'oc_';
+
+    /**
+     * Result of the last operation
+     *
+     * @var rcube_result_set
+     */
+    protected $result;
+
+    /**
+     * Stored SQL filter to limit record list
+     *
+     * @var string
+     */
+    protected $filter  = null;
+
+    /**
+     * Set required parameters
+     *
+     * @param int      $abId   Addressbook ID (oc_addressbooks.id)
+     * @param rcube_db $db     Connection to the NextCloud database
+     * @param string   $prefix Database table prefix
+     */
+    public function __construct($abId, $db, $prefix)
+    {
+        $this->abId   = $abId;
+        $this->db     = $db;
+        $this->prefix = $prefix;
+    }
+
+    /**
+     * Get the title of this address book
+     *
+     * Used in contact details view.
+     *
+     * @return string Address book name
+     */
+    public function get_name()
+    {
+        $sql = 'SELECT displayname'
+             . ' FROM ' . $this->prefix . 'addressbooks'
+             . ' WHERE id = ?';
+        $stmt = $this->db->query($sql, $this->abId);
+        $row = $this->db->fetch_assoc($stmt);
+
+        return $row['displayname'] . ' (Nextcloud)';
+    }
+
+    /**
+     * Save a search string for future listings.
+     *
+     * Needed to share the filter between search(), list_records() and count().
+     *
+     * @param string $filter Part of the SQL statement used to filter contacts
+     *
+     * @return void
+     */
+    public function set_search_set($filter)
+    {
+        $this->filter = $filter;
+    }
+
+    /**
+     * Getter for saved search properties
+     *
+     * @return string Filtering part of the contact-fetching SQL statement
+     */
+    public function get_search_set()
+    {
+        return $this->filter;
+    }
+
+    /**
+     * Reset saved results and search parameters
+     *
+     * @return void
+     */
+    public function reset()
+    {
+        $this->result = null;
+        $this->filter = null;
+        $this->cache  = null;
+    }
+
+    /**
+     * List the current set of contact records
+     *
+     * @param array   $cols    List of cols to show, NULL means all
+     *                         Known values:
+     *                         - name
+     *                         - firstname
+     *                         - surname
+     *                         - email
+     * @param int     $subset  Only return this number of records,
+     *                         use negative values for tail
+     * @param boolean $nocount Do not calculate the number of all records
+     *
+     * @return rcube_result_set
+     *
+     * @internal Paging information is stored in $this->list_page
+     *           and starts with 1
+     */
+    public function list_records($cols = null, $subset = 0, $nocount = false)
+    {
+        $this->result = new rcube_result_set();
+
+        $sql = <<<SQL
+SELECT
+    p_email.cardid AS id,
+    p_email.value AS email,
+    p_name.value AS name
+FROM
+    %PREFIX%cards_properties AS p_email
+    JOIN %PREFIX%cards_properties AS p_name
+        ON p_name.cardid = p_email.cardid
+            AND p_name.name = "FN"
+WHERE
+    p_email.addressbookid = ?
+    AND p_email.name = "EMAIL"
+    %FILTER%
+ORDER BY name, email
+SQL;
+        
+        $sql = str_replace(
+            '%FILTER%',
+            $this->filter ? ' AND ' . $this->filter : '',
+            $this->replaceTablePrefix($sql)
+        );
+
+        $firstRecord = $this->list_page * $this->page_size - $this->page_size;
+        $stmt = $this->db->limitquery(
+            $sql,
+            $firstRecord, $this->page_size,
+            $this->abId
+        );
+        foreach ($stmt as $row) {
+            $this->result->add(
+                [
+                    'ID'    => $row['id'],
+                    'name'  => $row['name'],
+                    'email' => $row['email'],
+                ]
+            );
+        }
+
+        if ($nocount) {
+            //do not fetch the numer of all records
+            $this->result->count = count($this->result->records);
+        } else {
+            $this->result->count = $this->count()->count;
+        }
+        
+        return $this->result;
+    }
+
+    /**
+     * Search records
+     *
+     * @param array|string $fields   One or more field names to search in. Examples:
+     *                               - '*'
+     *                               - 'ID'
+     * @param array|string $value    Search value
+     * @param int          $mode     Search mode. Sum of self::SEARCH_*.
+     * @param boolean      $select   False: only count records; do not select them
+     * @param boolean      $nocount  True to not calculate the total record count
+     * @param array        $required List of fields that cannot be empty
+     *
+     * @return rcube_result_set List of contact records and 'count' value
+     */
+    public function search(
+        $fields, $value, $mode = 0, $select = true,
+        $nocount = false, $required = []
+    ) {
+        $where = $this->buildSearchQuery($fields, $value, $mode);
+        if (empty($where)) {
+            return new rcube_result_set();
+        }
+        
+        $this->set_search_set($where);
+        if ($select) {
+            return $this->list_records(null, 0, $nocount);
+        } else {
+            return $this->count();
+        }
+    }
+
+    /**
+     * Build an SQL WHERE clause to search for $value
+     *
+     * TODO: We do not support space-separated search words yet
+     *
+     * @param array|string $fields One or more field names to search in.
+     *                             Examples:
+     *                             - '*'
+     *                             - 'ID'
+     * @param array|string $value  Search value
+     * @param int          $mode   Search mode. Sum of self::SEARCH_*.
+     *
+     * @return string Part of an SQL query, but without the prefixed " AND "
+     */
+    protected function buildSearchQuery($fields, $value, $mode)
+    {
+        if ($fields === 'ID') {
+            return 'p_email.cardid = ' . intval($value);
+
+        } else if ($fields === '*') {
+            return '('
+                . $this->buildSearchQueryField('name', $value, $mode)
+                . ' OR '
+                . $this->buildSearchQueryField('email', $value, $mode)
+                . ')';
+        }
+
+        $fields = (array) $fields;
+        $sqlParts = [];
+        foreach ($fields as $field) {
+            if ($field != 'name' && $field != 'email') {
+                continue;
+            }
+
+            $sqlParts[] = $this->buildSearchQueryField($field, $value, $mode);
+        }
+        return '(' . implode(' OR ', $sqlParts) . ')';
+    }
+
+    /**
+     * Build a search SQL for a single field
+     *
+     * @param string       $field Field name. Examples:
+     *                            - '*'
+     *                            - 'ID'
+     * @param array|string $value Search value
+     * @param int          $mode  Search mode. Sum of self::SEARCH_*.
+     *
+     * @return string Part of an SQL query
+     */
+    protected function buildSearchQueryField($field, $value, $mode)
+    {
+        $sqlField = 'p_' . $field . '.value';
+        
+        if ($mode & self::SEARCH_STRICT) {
+            //exact match
+            return $sqlField . ' = ' . $this->db->quote($value);
+
+        } else if ($mode & self::SEARCH_PREFIX) {
+            return $this->db->ilike($sqlField, $value . '%');            
+        }
+
+        return $this->db->ilike($sqlField, '%' . $value . '%');
+    }
+
+    /**
+     * Count number of available contacts in database
+     *
+     * @return rcube_result_set Result set with values for 'count' and 'first'
+     */
+    public function count()
+    {
+        $count = isset($this->cache['count'])
+            ? $this->cache['count']
+            : $this->_count();
+
+        return new rcube_result_set(
+            $count, ($this->list_page - 1) * $this->page_size
+        );
+    }
+
+    /**
+     * Count number of available contacts in database
+     *
+     * @return int Contacts count
+     */
+    protected function _count()
+    {
+        $sql = <<<SQL
+SELECT COUNT(*) AS cnt
+FROM
+    %PREFIX%cards_properties AS p_email
+    JOIN %PREFIX%cards_properties AS p_name
+        ON p_name.cardid = p_email.cardid
+            AND p_name.name = "FN"
+WHERE
+    p_email.addressbookid = ?
+    AND p_email.name = "EMAIL"
+    %FILTER%
+SQL;
+        
+        $sql = str_replace(
+            '%FILTER%',
+            $this->filter ? ' AND ' . $this->filter : '',
+            $this->replaceTablePrefix($sql)
+        );
+
+        $stmt = $this->db->query($sql, $this->abId);
+        $row = $this->db->fetch_assoc($stmt);
+
+        $this->cache['count'] = (int) $row['cnt'];
+        return $this->cache['count'];
+    }
+    
+    /**
+     * Return the last result set
+     *
+     * @return rcube_result_set Current result set or NULL if nothing selected yet
+     */
+    public function get_result()
+    {
+        return $this->result;
+    }
+
+    /**
+     * Get a specific contact record
+     *
+     * @param mixed   $id    Record identifier
+     * @param boolean $assoc True to return record as associative array.
+     *                       False: a result set is returned
+     *
+     * @return rcube_result_set|array|null Result object with all record fields
+     *                                     NULL when it does not exist/
+     *                                     is not accessible
+     */
+    public function get_record($id, $assoc = false)
+    {
+        $sql = <<<SQL
+SELECT
+    p_email.cardid AS ID,
+    p_email.value AS email,
+    p_name.value AS name
+FROM
+    %PREFIX%cards_properties AS p_email
+    JOIN %PREFIX%cards_properties AS p_name
+        ON p_name.cardid = p_email.cardid
+            AND p_name.name = "FN"
+WHERE
+    p_email.addressbookid = ?
+    AND p_email.cardid = ?
+    AND p_email.name = "EMAIL"
+ORDER BY name, email
+SQL;
+
+        $stmt = $this->db->query(
+            $this->replaceTablePrefix($sql),
+            $this->abId, $id
+        );
+        $row = $this->db->fetch_assoc($stmt);
+
+        if ($row === false) {
+            return null;
+        }
+
+        $this->result = new rcube_result_set(1);
+        $this->result->add(
+            [
+                'ID' => $row['id'],
+                'name'  => $row['name'],
+                'email' => $row['email'],
+            ]
+        );
+        
+        return $assoc ? $this->result->first() : $this->result;
+    }
+
+    /**
+     * Replace the %PREFIX% variable in SQL queries with the configured
+     * NextCloud table prefix
+     *
+     * @param string $sql SQL query with %PREFIX% variables
+     *
+     * @return string Working SQL query
+     */
+    protected function replaceTablePrefix($sql)
+    {
+        return str_replace('%PREFIX%', $this->prefix, $sql);
+    }
+}
+?>