developer.jelix.org is not used any more and exists only for history. Post new tickets on the Github account.
developer.jelix.org n'est plus utilisée, et existe uniquement pour son historique. Postez les nouveaux tickets sur le compte github.

Opened 13 years ago

Closed 11 years ago

Last modified 11 years ago

#477 closed enhancement (fixed)

Can not use schema in jDao with PostgreSQL

Reported by: nuks Owned by:
Priority: normal Milestone: Jelix 1.1.6
Component: jelix:db Version: 1.0.2
Severity: normal Keywords:
Cc: Blocked By:
Blocking: Documentation needed: no
Hosting Provider: Php version:

Description

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

Change History (10)

comment:1 Changed 13 years ago by nuks

  • Documentation needed set

comment:2 Changed 13 years ago by laurentj

  • Component changed from jelix to jelix:db
  • Milestone Jelix 1.0.3 deleted
  • Priority changed from highest to normal

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

comment:3 Changed 12 years ago by bballizlife

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

comment:4 Changed 12 years ago by 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.

comment:5 Changed 12 years ago by nuks

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

comment:6 Changed 12 years ago by laurentj

  • Documentation needed unset

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.

comment:7 Changed 12 years ago by 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.

comment:8 Changed 11 years ago by laurentj

  • Milestone set to Jelix 1.1.6
  • Resolution set to fixed
  • Status changed from new to closed

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

comment:9 Changed 11 years ago by laurentj

  • Documentation needed set

comment:10 Changed 11 years ago by laurentj

  • Documentation needed unset

doc done.

Note: See TracTickets for help on using tickets.