Add postgresql support

Created by: glz-io

Hi,

For the moment, we can not activate passman when using postgresql database.

The error is below: An exception occurred while executing 'CREATE TABLE "oc_passman_tags" ("tag_id" BIGSERIAL DEFAULT NULL, "user_id" VARCHAR(64) NOT NULL, "tag_label" VARCHAR(64) NOT NULL, "renewal_period" INT DEFAULT 0, "min_pw_strength" INT DEFAULT 0)': SQLSTATE[42601]: Syntax error: 7 ERROR: multiple default values specified for column "tag_id" of table "oc_passman_tags"

using Owncloud 7.0.2

Regards

--- Want to back this issue? **Post a bounty on it!** We accept bounties via Bountysource.

Imported comments:

By t-szczyrba on 2014-09-27 09:08:58 UTC

There is a problem with attached database schema in appinfo/database.xml it seems that specifying autoincrement in schema for owncloud implies serial/bigserial type for psql driver and that forces not to use null as default - so one has to force 'not null'. To fix it one has to add true to every db field declaration

By t-szczyrba on 2014-09-27 09:09:55 UTC

sorry, should be written as <notnull>true</notnull>

By brantje on 2014-09-27 09:10:38 UTC

	<field>
				<name>user_id</name>
				<type>text</type>
				<notnull>true</notnull>
				<length>64</length>
                                <notnull>true</notnull>
			</field>

Like this?

By brantje on 2014-09-27 09:24:34 UTC

Can you try 8f88e196 ?

By t-szczyrba on 2014-09-27 17:01:44 UTC

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

On 27.09.2014 11:24, Sander wrote:

Can you try 8f88e196 https://github.com/brantje/passman/commit/8f88e19696c72cdd2999c54bedd03c1898f340b9 ?

— Reply to this email directly or view it on GitHub https://github.com/brantje/passman/issues/40#issuecomment-57047389.

I've tried to replace it before, but then enablement of application works, it is possible to get into the configuration screen / password question but after writing a password the screen is stuck with a 'rotating wait' image. When click 'create passoword' window 'add new item' shows, but it is not possible to save.

I've inspected some communiation browser<>server and I see http 'get' method is causing erroneus action in server: " SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "oc_passman_items" does not exist LINE 1: ...ROUP_CONCAT(distinct tags.tag_label) AS tags FROM "oc_passma..."

So I think the sql statements in db/*.php or used db schema are not quite compatible with postgresql - I'm completely new to owncloud so I have to figure it out first, how other apps are handling databases,

regards,

T.

-----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.22 (GNU/Linux)

iQIcBAEBAgAGBQJUJuxXAAoJEEji0kirYj2EYJoP/jDhecnKbeUW0W6U4QMW7V8h kqgQdDE1ZYjIIwTNsB09qdLf/tc+rWIxluQl46eB72wJq//cwbwo9yiHAI8alv/5 3wiK/fk0Fhyqcnbvb28ZnxkSEM9Kci2KRFdDSPjMbF+ROXtM35X8w6GLUX/R+CRK KrmZBqwOKg2n2Ci0ZuY0NJNxDSkk4oeC0b8zHYmOl0fHUyFHrqQ1LGFJMGqF1tEf S/q0H+eTM0Y+P4HWG/krdb1GXHjTVnzkc126m4Xaz04105S6NBcsHBqbswNxTYJR VuC1fgDrVgaguEaF5xkmziE9TH5rIIKCWcVoqZJsXhZQMjj+ReCZ7IJj063jpNUd eGing+1djTzr3XV9pzmgd8ln8bmKWgtvTxcGbbjy6OzINK8EDskb9FIXJ7EoJTMO 2Lt76+gRrXduR62SzZsVnG85udcC+R1OZ8F12EblNkQCy/aascV/7gDPqVeUaLAq qwM1m+R8g13KFuy3kC6Q5RiuNFN5TmQcCUWNBW5EAZ2dVIToJsAbr52jW8tb6ed1 ujYW1Akl5jVTgZDNrjCxJSqEzP9I7Mh3HKG9tyJJTj+BdeeX3m83TsTlYIBJTp0K wL+Wv1/Hj9F8z5m57E6lBkVwcFzRhn0mJG9BsXZ2G587fE/tqQ5+/K2qCcyYLNYN kzpqvBKdPJxyi4zD0QdT =pQC7 -----END PGP SIGNATURE-----

By t-szczyrba on 2014-09-28 18:53:31 UTC

On 27.09.2014 11:24, Sander wrote:

Can you try 8f88e196 https://github.com/brantje/passman/commit/8f88e19696c72cdd2999c54bedd03c1898f340b9 ?

— Reply to this email directly or view it on GitHub https://github.com/brantje/passman/issues/40#issuecomment-57047389.

In the attachement I've included diff with modifications necessary to run passman with postgres at least on the preliminary level - so it at least works in somehow usable state. Be careful: it is only for testing purposes / proof of concept and applying it probably makes passman not working with mysql anymore.

The question remains: how to include the necessary modifications into passman? Modify issued sql statements according to DB engine used (from my point of view it is not good design pattern) or use some other statements compatible with mysql and postgresql in the same time / issue data queries in the way how other owncloud apps work?

From another side: there is also graphical artifact visible - the sites don't show in one column, but every new password is displayed right to the password above.

regards,

T.

By brantje on 2014-09-29 13:32:50 UTC

Its better do to a check in the db/* files. Like if($storageEngine=="sqllite"){

Another option is to rewrite the statements. But, i don't have any sqllite experience, so that would take a lot of time, which i don't have currently :(. #34 (closed) is a bit more prio then this one.