In general we use schemas to segregate data and provide access control
within web2py. (Authentication and access control is at the DB level and
not the web app level).
As mentioned by Manuele we also use executesql to set the search path as
required.
In our environment each schema may provide similar data structures (Ex: If
you had a client per schema, each schema could have a 'sales' table), while
public has any data that all schemas may want to access.
The one caveat is if you want to use DAL's migrate option, you must
remember to set your table migration names based on your schema names.
(Otherwise if you have two schemas with the same table name (but different
structure), web2py does not know how to distinguish the difference between
the two).
Note: The below assumes sanitized schema names.
Example:
# A migration helper to ensure DB files are named properly
def set_migrate(migrate, table_name, schema=None):
'''
Helper method to set table migration names based on schema names
vars:
migrate: Boolean whether migration should occur
table_name: string representing the table
schema: string representing current schema
'''
if migrate is not False:
migrate = str(schema)+'_'+table_name
return migrate
Later in the models file(s) I would have something like the below assuming
that the variable schema is None or a schema in postgres.
NOTE: (has_schema_access and switch_schema represent custom methods we use)
if db and schema and has_schema_access(db, schema):
# Switch schema prior to table creation
switch_schema(db, schema, 'public')
db.define_table('bucket',
Field('name', 'string', required=True),
Field('variable', 'string'),
migrate=set_migrate(GLOBAL_MIGRATE_BOOL, "bucket.table",
schema=schema))
db.define_table('bucket_meta',
Field('bucket_id', db.bucket),
Field('var', 'string'),
Field('val', 'string'),
migrate=set_migrate(GLOBAL_MIGRATE_BOOL, "bucket_meta.table",
schema=schema))
Hope this helps!
-- Richard
