Fulltext search over contact fields. Attention: DATABASE SCHEMA CHANGED\!

thomascube

2011-02-14 3e2637351da9559a4aa420004ac90e9fe30477ef

Fulltext search over contact fields. Attention: DATABASE SCHEMA CHANGED\!

 CHANGELOG

@@ -1,7 +1,12 @@
CHANGELOG Roundcube Webmail
===========================

- Fulltext search over (almost) all data for contacts
- Extend address book with rich contact information
- Support strftime format in date_today option

RELEASE 0.5.1
-------------
- Fix handling of attachments with invalid content type (#1487767)
- Add workaround for DBMail's bug http://www.dbmail.org/mantis/view.php?id=881 (#1487766)
- Use IMAP's ID extension (RFC2971) to print more info into debug log

 SQL/mssql.initial.sql

@@ -16,7 +16,8 @@
    [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,

    [firstname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

    [surname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,

    [vcard] [text] COLLATE Latin1_General_CI_AI NULL 
    [vcard] [text] COLLATE Latin1_General_CI_AI NULL ,

    [words] [text] COLLATE Latin1_General_CI_AI NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO




 SQL/mssql.upgrade.sql

@@ -96,4 +96,8 @@
GO

ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL

GO




-- Updates from version 0.5.x



ALTER TABLE [dbo].[contacts] ADD [words] [text] COLLATE Latin1_General_CI_AI NULL 
GO


 SQL/mysql.initial.sql

@@ -87,6 +87,7 @@
 `firstname` varchar(128) NOT NULL DEFAULT '',
 `surname` varchar(128) NOT NULL DEFAULT '',
 `vcard` text NULL,
 `words` text NULL,
 `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(`contact_id`),
 CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)

 SQL/mysql.update.sql

@@ -133,3 +133,6 @@

TRUNCATE TABLE `messages`;

-- Updates from version 0.5.*

ALTER TABLE `contacts` ADD `words` TEXT NULL AFTER `vcard`;

 SQL/postgres.initial.sql

@@ -103,14 +103,15 @@
CREATE TABLE contacts (
    contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY,
    user_id integer NOT NULL
    REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
    changed timestamp with time zone DEFAULT now() NOT NULL,
    del smallint DEFAULT 0 NOT NULL,
    name varchar(128) DEFAULT '' NOT NULL,
    email varchar(255) DEFAULT '' NOT NULL,
    firstname varchar(128) DEFAULT '' NOT NULL,
    surname varchar(128) DEFAULT '' NOT NULL,
    vcard text
    vcard text,
    words text
);

CREATE INDEX contacts_user_id_idx ON contacts (user_id, email);

 SQL/postgres.update.sql

@@ -89,3 +89,9 @@
ALTER TABLE contacts ALTER email TYPE varchar(255);

TRUNCATE messages;


-- Updates from version 0.5.x

ALTER TABLE contacts ADD words TEXT NULL;


 SQL/sqlite.initial.sql

@@ -31,7 +31,8 @@
  email varchar(255) NOT NULL default '',
  firstname varchar(128) NOT NULL default '',
  surname varchar(128) NOT NULL default '',
  vcard text NOT NULL default ''
  vcard text NOT NULL default '',
  words text NOT NULL default ''
);

CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);

 SQL/sqlite.update.sql

@@ -182,3 +182,42 @@

DELETE FROM messages;


-- Updates from version 0.5.x

CREATE TABLE contacts_tmp (
    contact_id integer NOT NULL PRIMARY KEY,
    user_id integer NOT NULL default '0',
    changed datetime NOT NULL default '0000-00-00 00:00:00',
    del tinyint NOT NULL default '0',
    name varchar(128) NOT NULL default '',
    email varchar(255) NOT NULL default '',
    firstname varchar(128) NOT NULL default '',
    surname varchar(128) NOT NULL default '',
    vcard text NOT NULL default ''
);

INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts;

DROP TABLE contacts;
CREATE TABLE contacts (
    contact_id integer NOT NULL PRIMARY KEY,
    user_id integer NOT NULL default '0',
    changed datetime NOT NULL default '0000-00-00 00:00:00',
    del tinyint NOT NULL default '0',
    name varchar(128) NOT NULL default '',
    email varchar(255) NOT NULL default '',
    firstname varchar(128) NOT NULL default '',
    surname varchar(128) NOT NULL default '',
    vcard text NOT NULL default '',
    words text NOT NULL default ''
);

INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp;

CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
DROP TABLE contacts_tmp;

DELETE FROM messages;

 bin/indexcontacts.sh

New file
@@ -0,0 +1,54 @@
#!/usr/bin/env php
<?php
/*

 +-----------------------------------------------------------------------+
 | bin/indexcontacts.sh                                                  |
 |                                                                       |
 | This file is part of the Roundcube Webmail client                     |
 | Copyright (C) 2011, The Roundcube Dev Team                            |
 | Licensed under the GNU GPL                                            |
 |                                                                       |
 | PURPOSE:                                                              |
 |   Update the fulltext index for all contacts of the internal          |
 |   address book.                                                       |
 +-----------------------------------------------------------------------+
 | Author: Thomas Bruederli <roundcube@gmail.com>                        |
 +-----------------------------------------------------------------------+

 $Id$

*/

define('INSTALL_PATH', realpath(dirname(__FILE__) . '/..') . '/' );

require_once INSTALL_PATH.'program/include/clisetup.php';


// connect to DB
$RCMAIL = rcmail::get_instance();

$db = $RCMAIL->get_dbh();
$db->db_connect('w');

if (!$db->is_connected() || $db->is_error())
    die("No DB connection\n");

// iterate over all users
$sql_result = $db->query("SELECT user_id FROM " . $RCMAIL->config->get('db_table_users', 'users')." WHERE 1");
while ($sql_result && ($sql_arr = $db->fetch_assoc($sql_result))) {
    echo "Indexing contacts for user " . $sql_arr['user_id'] . "...";
    
    $contacts = new rcube_contacts($db, $sql_arr['user_id']);
    $contacts->set_pagesize(9999);
    
    $result = $contacts->list_records();
    while ($result->count && ($row = $result->next())) {
        unset($row['words']);
        $contacts->update($row['ID'], $row);
    }
    
    echo "done.\n";
}

?>

 program/include/rcube_addressbook.php

@@ -32,6 +32,7 @@
    const ERROR_NO_CONNECTION = 2;
    const ERROR_INCOMPLETE = 3;
    const ERROR_SAVING = 4;
    const ERROR_SEARCH = 5;
    
    /** public properties (mandatory) */
    public $primary_key;
@@ -384,6 +385,26 @@
      
        return $out;
    }


    /**
     * Normalize the given string for fulltext search.
     * Currently only optimized for Latin-1 characters; to be extended
     *
     * @param string Input string (UTF-8)
     * @return string Normalized string
     */
    protected static function normalize_string($str)
    {
        $norm = strtolower(strtr(utf8_decode($str),
            'ÇçäâàåéêëèïîìÅÉöôòüûùÿøØáíóúñÑÁÂÀãÃÊËÈÍÎÏÓÔõÕÚÛÙýÝ',
            'ccaaaaeeeeiiiaeooouuuyooaiounnaaaaaeeeiiioooouuuyy'));

        return preg_replace(
            array('/[\s;\+\-\/]+/i', '/(\d)\s+(\d)/', '/\s\w{1,3}\s/'),
            array(' ', '\\1\\2', ' '),
            $norm);
    }
    
}


 program/include/rcube_contacts.php

@@ -5,7 +5,7 @@
 | program/include/rcube_contacts.php                                    |
 |                                                                       |
 | This file is part of the Roundcube Webmail client                     |
 | Copyright (C) 2006-2010, The Roundcube Dev Team                       |
 | Copyright (C) 2006-2011, The Roundcube Dev Team                       |
 | Licensed under the GNU GPL                                            |
 |                                                                       |
 | PURPOSE:                                                              |
@@ -41,10 +41,11 @@
    private $user_id = 0;
    private $filter = null;
    private $result = null;
    private $search_fields;
    private $search_string;
    private $cache;
    private $table_cols = array('name', 'email', 'firstname', 'surname', 'vcard');
    private $table_cols = array('name', 'email', 'firstname', 'surname');
    private $fulltext_cols = array('name', 'firstname', 'surname', 'middlename', 'nickname',
      'jobtitle', 'organization', 'department', 'maidenname', 'email', 'phone',
      'address', 'street', 'locality', 'zipcode', 'region', 'country', 'website', 'im', 'notes');

    // public properties
    public $primary_key = 'contact_id';
@@ -115,8 +116,6 @@
    {
        $this->result = null;
        $this->filter = null;
        $this->search_fields = null;
        $this->search_string = null;
        $this->cache = null;
    }

@@ -253,8 +252,15 @@
                $ids     = $this->db->array2list($ids, 'integer');
                $where[] = 'c.' . $this->primary_key.' IN ('.$ids.')';
            }
            else if ($strict)
            else if ($strict) {
                $where[] = $this->db->quoteIdentifier($col).' = '.$this->db->quote($value);
            }
            else if ($col == '*') {
                $words = array();
                foreach(explode(" ", self::normalize_string($value)) as $word)
                    $words[] = $this->db->ilike('words', '%'.$word.'%');
                $where[] = '(' . join(' AND ', $words) . ')';
              }
            else
                $where[] = $this->db->ilike($col, '%'.$value.'%');
        }
@@ -528,15 +534,21 @@
    private function convert_save_data($save_data, $record = array())
    {
        $out = array();
        $words = '';

        // copy values into vcard object
        $vcard = new rcube_vcard($record['vcard'] ? $record['vcard'] : $save_data['vcard']);
        $vcard->reset();
        foreach ($save_data as $key => $values) {
            list($field, $section) = explode(':', $key);
            $fulltext = in_array($field, $this->fulltext_cols);
            foreach ((array)$values as $value) {
                if (isset($value))
                    $vcard->set($field, $value, $section);
                if ($fulltext && is_array($value))
                    $words .= ' ' . self::normalize_string(join(" ", $value));
                else if ($fulltext && strlen($value) >= 3)
                    $words .= ' ' . self::normalize_string($value);
            }
        }
        $out['vcard'] = $vcard->export();
@@ -552,6 +564,9 @@
        // save all e-mails in database column
        $out['email'] = join(", ", $vcard->email);

        // join words for fulltext search
        $out['words'] = join(" ", array_unique(explode(" ", $words)));

        return $out;
    }


 program/include/rcube_ldap.php

@@ -407,6 +407,17 @@

        $filter = '(|';
        $wc = !$strict && $this->prop['fuzzy_search'] ? '*' : '';
        if ($fields != '*')
        {
            // search_fields are required for fulltext search
            if (!$this->prop['search_fields'])
            {
                $this->set_error(self::ERROR_SEARCH, 'nofulltextsearch');
                $this->result = new rcube_result_set();
                return $this->result;
            }
        }
        
        if (is_array($this->prop['search_fields']))
        {
            foreach ($this->prop['search_fields'] as $k => $field)

 program/include/rcube_vcard.php

@@ -135,11 +135,15 @@
    $typemap = $this->typemap;
    
    // copy name fields to output array
    foreach (array('firstname','surname','middlename','nickname','organization') as $col)
      $out[$col] = $this->$col;
    foreach (array('firstname','surname','middlename','nickname','organization') as $col) {
      if (strlen($this->$col))
        $out[$col] = $this->$col;
    }
    
    $out['prefix'] = $this->raw['N'][0][3];
    $out['suffix'] = $this->raw['N'][0][4];
    if ($this->raw['N'][0][3])
      $out['prefix'] = $this->raw['N'][0][3];
    if ($this->raw['N'][0][4])
      $out['suffix'] = $this->raw['N'][0][4];
    
    // convert from raw vcard data into associative data for Roundcube
    foreach (array_flip($this->fieldmap) as $tag => $col) {

 program/steps/addressbook/search.inc

@@ -26,7 +26,7 @@
$search_request = md5('addr'.$search);

// get contacts for this user
$result = $CONTACTS->search(array('name','email'), $search);
$result = $CONTACTS->search('*', $search);

// save search settings in session
$_SESSION['search'][$search_request] = $CONTACTS->get_search_set();