I am working on a very complex multilevel database and I am using
postgresql schemas to organise different aspects of the database.
Using conditional models I do something like this:
models/isl/isi.py:
db.executesql("set search_path to isi;")
db.define_table('rjoernaal',
...
db.executesql("set search_path to isi, public;")
So far so good. The tables were created in schema isi and appadmin
handles it without a problem.
But then I added another schema and use models/nrf/nrf.py:
db.executesql("set search_path to nrf;")
db.define_table("nluruli_publ",
...
)
db.executesql("set search_path to nrf, isi, public")
db.nluruli.publ gets created in the isi-schema. Appadmin sees it and the
controller (at this stage just a grid) handles it.
But I wanted db.nluruli_publ in the schema nrf so I moved it using pgadmin3
to the correct schema.
The controller still handles the data well but this time appadmin says:
Traceback (most recent call last):
File "/home/js/web2py/applications/akb/controllers/appadmin.py", line 231, in
select
nrows = db(query).count()
File "/home/js/web2py/gluon/dal.py", line 9350, in count
return db._adapter.count(self.query,distinct)
File "/home/js/web2py/gluon/dal.py", line 1676, in count
self.execute(self._count(query, distinct))
File "/home/js/web2py/gluon/dal.py", line 1734, in execute
return self.log_execute(*a, **b)
File "/home/js/web2py/gluon/dal.py", line 1728, in log_execute
ret = self.cursor.execute(*a, **b)
ProgrammingError: relation "nluruli_publ" does not exist
LINE 1: SELECT count(*) FROM nluruli_publ WHERE (nluruli_publ.id > 0...
^
Because DAL does not cater for postgresql schemas I have to use this
workaround of 'set search_path...'
Now my questions:
Why did DAL ignore the search path when it created the nluruli_publ table?
Why does appadmin not use the search path defined in the model when it
wants to find the table while the controler nrf.py does not have the same
problem?
Regards
Johann
--
