Schemas, search parth, auth_user and table definitions in modules

[Johann Spies]

Earlier in this mailing list the suggestion was made that one can use the sql search path to include/exclude schemas from web2py.


In a setup like this it becomes tricky:

Schema: Public
              Table(s): auth-related tables
             
Schema: wbank:  
              Tables related to wbank

Schema: doccenter:
              Tables related to doccenter (which refer to auth_user)

and other to follow.

As long as the table definitions do not refer to auth_user I could do the following in the module (I have tried different sorts of solutions.  This is my last one):

class Wbank(object):
   
    def __init__(self, db):
        self.db = db
        self.db.executesql("set search_path to wbank;")
        self.T = current.T
        self.auth = current.session.auth
        self.request = current.request
        self.tabelle = ['wbdocuments','wbmaster']
        self.define_tables()
        current.db = self.db
        self.db.executesql("set search_path to wbank, public;")

In this case the tables are created in the schema 'wbank' and everthing works OK. 

But when he table definition contains references to db.auth_user  I get problems.  For example:

def signature(db):

    #db = current.db

    db.executesql("set search_path to doccenter, public;")

    request = current.request

    akb_signature_uuid = db.Table(db, 'akb_signature_uuid',

                     Field('uuid', length = 64, default = lambda:str(uuid.uuid4()),

                           notnull = True, writable = False, readable = False,

                           unique = True, ondelete = 'CASCADE'),

                     Field('created_on', 'datetime', default = request.now,

                           readable = False, writable = False),

                     Field('created_by', db.auth_user, default = current.auth_user,

                           readable = False, writable = False),

                     Field('updated_on', 'datetime', default = request.now,

                           readable = False, writable = False),

                     Field('updated_by', db.auth_user, update = current.auth_user,

                           readable = False, writable = False)

                     )

    db.executesql("set search_path to doccenter;")

   

    return akb_signature_uuid



class Docs(object):

   

    def __init__(self, db):

        self.db = db

        self.T = current.T

        self.auth = current.session.auth

        print self.auth

        if self.auth:

            current.auth_user = self.auth.user.id

        else:

            current.auth_user = None

        self.request = current.request

        self.akb_signature_uuid = signature(self.db)

        self.db.executesql("set search_path to doccenter;")

        self.request = current.request

        self.tabelle = ['dc_location', 'dc_category',

                        'doccenter']

        self.define_tables()

        current.db = self.db

        self.db.executesql("set search_path to doccenter, public;")



    def dc_location(self):

        table = self.db.define_table('dc_location',

                                     Field('location'),

                                     self.akb_signature_uuid)

In this case I get the following ticket:

  File "/home/js/web2py/gluon/dal.py", line 1469, in execute
    return self.log_execute(*a, **b)
  File "/home/js/web2py/gluon/dal.py", line 1463, in log_execute
    ret = self.cursor.execute(*a, **b)
ProgrammingError: relation "auth_user" does not exist

How do I get DAL to create the tables in schema doccenter and at the same time have access to auth_user?

Regards
Johann

[Johann Spies]

The following code seems to have solved my problem (I replaced the class with a function to which db is passed by the controller):

def docs(db):
    T = current.T
    auth = current.session.auth
    if auth:
        auth_user = self.auth.user.id
    else:
        auth_user = None
    request = current.request
    akb_signature_uuid = signature(db)
    dctabelle = set(['dc_location', 'dc_category',
                        'doccenter'])
    db_tabelle = db.tables()
    dbtabelle = set(db.tables())
    if dctabelle - dbtabelle:

        db.define_table('dc_location',
                        Field('location'),

                        akb_signature_uuid)
...

Regards
Johann