3 * Address book backend accessing the NextCloud database directly.
7 * Only returns two fields: email and name (Full name).
9 * @author Christian Weiske <cweiske@cweiske.de>
10 * @license AGPLv3+ http://www.gnu.org/licenses/agpl.html
12 class nextcloud_sql_addressbook_backend extends rcube_addressbook
15 * Nextcloud address book ID
22 * Database connection to the NextCloud database
29 * Database table prefix
33 protected $prefix = 'oc_';
36 * Result of the last operation
38 * @var rcube_result_set
43 * Stored SQL filter to limit record list
47 protected $filter = null;
50 * Set required parameters
52 * @param int $abId Addressbook ID (oc_addressbooks.id)
53 * @param rcube_db $db Connection to the NextCloud database
54 * @param string $prefix Database table prefix
56 public function __construct($abId, $db, $prefix)
60 $this->prefix = $prefix;
64 * Get the title of this address book
66 * Used in contact details view.
68 * @return string Address book name
70 public function get_name()
72 $sql = 'SELECT displayname'
73 . ' FROM ' . $this->prefix . 'addressbooks'
75 $stmt = $this->db->query($sql, $this->abId);
76 $row = $this->db->fetch_assoc($stmt);
78 return $row['displayname'] . ' (Nextcloud)';
82 * Save a search string for future listings.
84 * Needed to share the filter between search(), list_records() and count().
86 * @param string $filter Part of the SQL statement used to filter contacts
90 public function set_search_set($filter)
92 $this->filter = $filter;
96 * Getter for saved search properties
98 * @return string Filtering part of the contact-fetching SQL statement
100 public function get_search_set()
102 return $this->filter;
106 * Reset saved results and search parameters
110 public function reset()
112 $this->result = null;
113 $this->filter = null;
118 * List the current set of contact records
120 * @param array $cols List of cols to show, NULL means all
126 * @param int $subset Only return this number of records,
127 * use negative values for tail
128 * @param boolean $nocount Do not calculate the number of all records
130 * @return rcube_result_set
132 * @internal Paging information is stored in $this->list_page
135 public function list_records($cols = null, $subset = 0, $nocount = false)
137 $this->result = new rcube_result_set();
141 p_email.cardid AS id,
142 p_email.value AS email,
145 %PREFIX%cards_properties AS p_email
146 JOIN %PREFIX%cards_properties AS p_name
147 ON p_name.cardid = p_email.cardid
148 AND p_name.name = "FN"
150 p_email.addressbookid = ?
151 AND p_email.name = "EMAIL"
158 $this->filter ? ' AND ' . $this->filter : '',
159 $this->replaceTablePrefix($sql)
162 $firstRecord = $this->list_page * $this->page_size - $this->page_size;
163 $stmt = $this->db->limitquery(
165 $firstRecord, $this->page_size,
168 foreach ($stmt as $row) {
172 'name' => $row['name'],
173 'email' => $row['email'],
179 //do not fetch the numer of all records
180 $this->result->count = count($this->result->records);
182 $this->result->count = $this->count()->count;
185 return $this->result;
191 * @param array|string $fields One or more field names to search in. Examples:
194 * @param array|string $value Search value
195 * @param int $mode Search mode. Sum of self::SEARCH_*.
196 * @param boolean $select False: only count records; do not select them
197 * @param boolean $nocount True to not calculate the total record count
198 * @param array $required List of fields that cannot be empty
200 * @return rcube_result_set List of contact records and 'count' value
202 public function search(
203 $fields, $value, $mode = 0, $select = true,
204 $nocount = false, $required = []
206 $where = $this->buildSearchQuery($fields, $value, $mode);
208 return new rcube_result_set();
211 $this->set_search_set($where);
213 return $this->list_records(null, 0, $nocount);
215 return $this->count();
220 * Build an SQL WHERE clause to search for $value
222 * TODO: We do not support space-separated search words yet
224 * @param array|string $fields One or more field names to search in.
228 * @param array|string $value Search value
229 * @param int $mode Search mode. Sum of self::SEARCH_*.
231 * @return string Part of an SQL query, but without the prefixed " AND "
233 protected function buildSearchQuery($fields, $value, $mode)
235 if ($fields === 'ID') {
236 return 'p_email.cardid = ' . intval($value);
238 } else if ($fields === '*') {
240 . $this->buildSearchQueryField('name', $value, $mode)
242 . $this->buildSearchQueryField('email', $value, $mode)
246 $fields = (array) $fields;
248 foreach ($fields as $field) {
249 if ($field != 'name' && $field != 'email') {
253 $sqlParts[] = $this->buildSearchQueryField($field, $value, $mode);
255 return '(' . implode(' OR ', $sqlParts) . ')';
259 * Build a search SQL for a single field
261 * @param string $field Field name. Examples:
264 * @param array|string $value Search value
265 * @param int $mode Search mode. Sum of self::SEARCH_*.
267 * @return string Part of an SQL query
269 protected function buildSearchQueryField($field, $value, $mode)
271 $sqlField = 'p_' . $field . '.value';
273 if ($mode & self::SEARCH_STRICT) {
275 return $sqlField . ' = ' . $this->db->quote($value);
277 } else if ($mode & self::SEARCH_PREFIX) {
278 return $this->db->ilike($sqlField, $value . '%');
281 return $this->db->ilike($sqlField, '%' . $value . '%');
285 * Count number of available contacts in database
287 * @return rcube_result_set Result set with values for 'count' and 'first'
289 public function count()
291 $count = isset($this->cache['count'])
292 ? $this->cache['count']
295 return new rcube_result_set(
296 $count, ($this->list_page - 1) * $this->page_size
301 * Count number of available contacts in database
303 * @return int Contacts count
305 protected function _count()
308 SELECT COUNT(*) AS cnt
310 %PREFIX%cards_properties AS p_email
311 JOIN %PREFIX%cards_properties AS p_name
312 ON p_name.cardid = p_email.cardid
313 AND p_name.name = "FN"
315 p_email.addressbookid = ?
316 AND p_email.name = "EMAIL"
322 $this->filter ? ' AND ' . $this->filter : '',
323 $this->replaceTablePrefix($sql)
326 $stmt = $this->db->query($sql, $this->abId);
327 $row = $this->db->fetch_assoc($stmt);
329 $this->cache['count'] = (int) $row['cnt'];
330 return $this->cache['count'];
334 * Return the last result set
336 * @return rcube_result_set Current result set or NULL if nothing selected yet
338 public function get_result()
340 return $this->result;
344 * Get a specific contact record
346 * @param mixed $id Record identifier
347 * @param boolean $assoc True to return record as associative array.
348 * False: a result set is returned
350 * @return rcube_result_set|array|null Result object with all record fields
351 * NULL when it does not exist/
354 public function get_record($id, $assoc = false)
358 p_email.cardid AS ID,
359 p_email.value AS email,
362 %PREFIX%cards_properties AS p_email
363 JOIN %PREFIX%cards_properties AS p_name
364 ON p_name.cardid = p_email.cardid
365 AND p_name.name = "FN"
367 p_email.addressbookid = ?
368 AND p_email.cardid = ?
369 AND p_email.name = "EMAIL"
373 $stmt = $this->db->query(
374 $this->replaceTablePrefix($sql),
377 $row = $this->db->fetch_assoc($stmt);
379 if ($row === false) {
383 $this->result = new rcube_result_set(1);
387 'name' => $row['name'],
388 'email' => $row['email'],
392 return $assoc ? $this->result->first() : $this->result;
396 * Replace the %PREFIX% variable in SQL queries with the configured
397 * NextCloud table prefix
399 * @param string $sql SQL query with %PREFIX% variables
401 * @return string Working SQL query
403 protected function replaceTablePrefix($sql)
405 return str_replace('%PREFIX%', $this->prefix, $sql);