Can not use schema in jDao with PostgreSQL

[nuks]

Personally, this unfeature really block my project. I set it to highest but you can down the priority as you want.

[laurentj]

What are you talking about ? Which schema ? The pgsql api of PHP doesn't say nothing about schema (​http://www.php.net/pgsql).

[bballizlife]

Nuks, more to say here ? Is your problem solved ? Do we still have something wrong in Jelix with pgsql ?

[nuks]

It's not something wrong. Like I setted it in the ticket, it's a new feature, not a bug.

Pgsql use a system of Schema, wich is beetwen one database and some tables. Altought it's not recommanded to use them (because of portability), you must use at least one schema/database. The default Schema is called public. The problem is that this schema allow rights to all the users of the server. So, it's recommanded to create another schema with restricted right and use this one.

The problem with jelix is that you can't choose wich schema you wan't to use. By default, pgsql call the public schema when he is not specified in the request, but if you use another schema, you will get in trouble. So, there are 3 solutions:

• Restrict right of the public schema and use it, it's the easily solution for the database administrator
• Allow to change the default schema in jelix (it's possible with "SET search_path TO foo". I can do a patch if you want)
• Modify the daos to specify schemas.

By default, a PGSQL basic SELECT is a little different than mysql so.

• MYSQL: SELECT [row|const[,…]] FROM table
• PGSQL: SELECT ["row"|const[,…]] FROM "table"[."schema" ]

So if you don't specify the schema, pgsql will choose the default one.

[nuks]

Oups, i writed a wrong thing. It's ["schema". ]"table" and not "table"[."schema" ]

[laurentj]

I'am for the second solution : adding a "schema" parameter in the db profil, and do a query after the connection to select this schema.

[nuks]

yep, when you want to use another schema just change the profil. The problem is when the user want to connect 2 tables of 2 differents schema: it will be not possible with dao so. The advantage is that it's more easy to port a mysql application to a pgsql application.

But the solution 2 is okay for me, it's not realy needed to have the solution 3 in the most cases.

[laurentj]

Added the support of the search_path options in postgresql profiles. Jelix 1.1.x and trunk

• ​http://hg.jelix.org/jelix/jelix-trunk/rev/b9c01b3b1f05
• ​http://hg.jelix.org/jelix/releases/jelix-1.1.x/rev/03a6ef77b778

[laurentj]

doc done.