Compatibility with PostgreSQL

[kedare]

Hello.

Because Roundcube is compatible with PostgreSQL, I think it would be a good idea to make this plugin compatible too.

Thank you

[christian-putzke]

PostgreSQL support is allready planed and listed in the roadmap: https://github.com/graviox/Roundcube-CardDAV/wiki/Roadmap

[B5r1oJ0A9G]

As far as I can see one just has to create the proper database objects.
Below is a very quick and dirty port from MySQL to PostgreSQL. I've testet it with Roundcube 0.7.2, PostgreSQL 9.1 and ownCloud 3. Havn't testet much. I got all entries from ownCloud, changed an entry and does a synchronization without any issue.

CREATE TABLE IF NOT EXISTS "carddav_server" (
"carddav_server_id" serial,
"user_id" int NOT NULL REFERENCES "users" ON DELETE CASCADE,
"url" varchar(255) NOT NULL,
"username" varchar(128) NOT NULL,
"password" varchar(128) NOT NULL,
"label" varchar(128) NOT NULL,
"read_only" int NOT NULL,
PRIMARY KEY ("carddav_server_id")
);

CREATE TABLE IF NOT EXISTS "carddav_contacts" (
"carddav_contact_id" serial,
"carddav_server_id" int REFERENCES "carddav_server" ON DELETE CASCADE,
"user_id" int,
"etag" varchar(64) NOT NULL,
"last_modified" varchar(128) NOT NULL,
"vcard_id" varchar(64),
"vcard" text NOT NULL,
"words" text,
"firstname" varchar(128) DEFAULT NULL,
"surname" varchar(128) DEFAULT NULL,
"name" varchar(255) DEFAULT NULL,
"email" varchar(255) DEFAULT NULL,
PRIMARY KEY ("carddav_server_id","user_id","vcard_id")
);

CREATE INDEX "user_id" ON "carddav_contacts" ("user_id");

[christian-putzke]

Thanks for your help! I'll try it by my self and if all is working fine I will push that within the next major, maybe already in an earlier minor, release.
Which version of Roundcube CardDAV did you used?

[B5r1oJ0A9G]

I've downloaded CardDAV today. I think it's the recent version:

head -n3 CHANGELOG

Changes from v0.4 to v0.5

• Added automaticly synchronized CardDAV contacts via cronjob
• Added larry skin support

[christian-putzke]

Yep, that is the latest version so your SQL statements should be work with this version.
As already said I will have a look at this and thank you for your help!

[B5r1oJ0A9G]

Just added a second address book to an existing account and new additional user within ownCloud/Roundcube.
Both (multiple address books/user, multiple users) seem to work/sync without any issues.

[christian-putzke]

Sounds great. Thanks again. I'll add your postgreSQL statements to the repository and mention you in the readme!

[B5r1oJ0A9G]

I had to made some additional changes:

Roundcube assumes sequence for last inserted id of table 'carddav_contacts' is named 'carddav_contacts'.
But the name of the implicit created sequence (by data type 'serial') for that table is 'carddav_contacts_carddav_contact_id_seq'.

I decided to rename the sequence accordantly to the other existing sequences:

ALTER SEQUENCE carddav_contacts_carddav_contact_id_seq RENAME TO carddav_contacts_ids;

Further I had to make the sequence name known to Roundcube:

[root@foobar roundcubemail]# diff -C2 ./config/db.inc.php.orig ./config/db.inc.php
*** ./config/db.inc.php.orig    2012-04-11 14:48:37.069923690 +0200
--- ./config/db.inc.php 2012-04-11 13:29:47.263678874 +0200
***************
*** 69,72 ****
--- 69,74 ----
  $rcmail_config['db_sequence_messages'] = 'message_ids';
  
+ $rcmail_config['db_sequence_carddav_contacts'] = 'carddav_contacts_ids';
+ 
  
  // end db config file
[root@foobar roundcubemail]#

Another issue i noticed was that a removed contact will be displayed in the list of addresses again on reload/refresh until a synchronization is executed. Don't know if this is Postgres related.

[christian-putzke]

Thanks for the changes. Do you want to join the development of Roundcube CardDAV and take care of the PostgreSQL integration?

[B5r1oJ0A9G]

Why not. But my time is very limited.

[christian-putzke]

I've added you to the repository team and granted you pull and push rights. You can now add the postgreSQL changes on your own. It's okay that your time is very limited. There shouldn't be very often changes on the SQL schemes.

[miynat]

Hi,

we are using carddav 0.5.1 and postgresql 9.1.4. When i try to add a contact, i get this error message :

PHP Fatal error: Call to undefined method rcube_mdb2::like() in /var/www/html/roundcube/plugins/carddav/carddav_addressbook.php on line 327

Then, i changed "like" with "ilike" at line 327 in carddav_addressbook.php. This time i get this error:

[24-Aug-2012 11:26:50] MDB2 Error: invalid number (-11): _doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_pgsql_8d21fb52dc3fcf5f3b21216f8f97b246e6f8cdea1 ('5', 'f')]
[Native message: ERROR: invalid input syntax for integer: "f"
LINE 1: ...t_pgsql_8d21fb52dc3fcf5f3b21216f8f97b246e6f8cdea1 ('5', 'f')
^]

[24-Aug-2012 11:26:50 +0300]: DB Error: MDB2 Error: invalid number Query: _doQuery: [Error message: Could not execute statement] [Last executed query: EXECUTE mdb2_statement_pgsql_8d21fb52dc3fcf5f3b21216f8f97b246e6f8cdea1 ('5', 'f')] [Native message: ERROR: invalid input syntax for integer: "f" LINE 1: ...t_pgsql_8d21fb52dc3fcf5f3b21216f8f97b246e6f8cdea1 ('5', 'f') ^] in /var/www/html/roundcube/program/include/rcube_mdb2.php on line 727 (POST /roundcube/?_orig_source=carddav_addressbook3?_task=addressbook&_action=save)
[24-Aug-2012 11:26:50] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: SELECT currval('carddav_contacts_carddav_contact_id_seq')]
[Native message: ERROR: currval of sequence "carddav_contacts_carddav_contact_id_seq" is not yet defined in this session]

After making the changes mentioned in this comment -> #8 (comment)

i get the same error message:

[24-Aug-2012 11:31:56] MDB2 Error: invalid number (-11): _doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_pgsql_8449cd6a3da508832b580fe0c9783a304fb93563b ('5', 'f')]
[Native message: ERROR: invalid input syntax for integer: "f"
LINE 1: ...t_pgsql_8449cd6a3da508832b580fe0c9783a304fb93563b ('5', 'f')
^]

[24-Aug-2012 11:31:56 +0300]: DB Error: MDB2 Error: invalid number Query: _doQuery: [Error message: Could not execute statement] [Last executed query: EXECUTE mdb2_statement_pgsql_8449cd6a3da508832b580fe0c9783a304fb93563b ('5', 'f')] [Native message: ERROR: invalid input syntax for integer: "f" LINE 1: ...t_pgsql_8449cd6a3da508832b580fe0c9783a304fb93563b ('5', 'f') ^] in /var/www/html/roundcube/program/include/rcube_mdb2.php on line 727 (POST /roundcube/?_orig_source=carddav_addressbook3?_task=addressbook&_action=save)
[24-Aug-2012 11:31:56] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: SELECT currval('carddav_contacts_ids')]
[Native message: ERROR: currval of sequence "carddav_contacts_ids" is not yet defined in this session]

How can i solve this issue?

[ghost]

did someone fix this issue? I've got the exactly same issue:

[22-Mar-2013 00:47:38 +0000]: DB Error: MDB2 Error: invalid number Query: _doQuery: [Error message: Could not execute statement] [Last executed query: EXECUTE mdb2_statement_pgsql_13a419c53ce5b00f9c6badca11033ae894e329eb6a ('4', 'f')] [Native message: ERROR: invalid input syntax for integer: "f" LINE 1: ..._pgsql_13a419c53ce5b00f9c6badca11033ae894e329eb6a ('4', 'f') ^] in /usr/local/www/roundcube/program/include/rcube_mdb2.php on line 727 (POST /webmail/?_orig_source=carddav_addressbook18?_task=addressbook&_action=save)