search

christian-putzke / Roundcube-CardDAV

Roundcube CardDAV Plugin
http://www.graviox.de
GNU Affero General Public License v3.0
119 stars 28 forks source link

Compatibility with PostgreSQL #8

Closed kedare closed 12 years ago

kedare commented 12 years ago

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 commented 12 years ago

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

B5r1oJ0A9G commented 12 years ago

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 commented 12 years ago

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 commented 12 years ago

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

head -n3 CHANGELOG

Changes from v0.4 to v0.5

christian-putzke commented 12 years ago

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 commented 12 years ago

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 commented 12 years ago

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

B5r1oJ0A9G commented 12 years ago

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 commented 12 years ago

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

B5r1oJ0A9G commented 12 years ago

Why not. But my time is very limited.

christian-putzke commented 12 years ago

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 commented 12 years ago

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 -> https://github.com/graviox/Roundcube-CardDAV/issues/8#issuecomment-5068123

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 commented 11 years ago

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)