On May 13, 2:24 pm, annet <[email protected]> wrote:
> I know web2py can connect to multiple databases, and I wonder whether
> it would be possible to have two db.py files: db1.py and db2.py, where
> db1.py connects to the database containing the first type of data and
> db2.py connects to the company's database. db2.py would also contain
> the auth_ tables.
Hi annet
I also process requests from multiple companies. I use a separate DB
for each company:
db.define_table(
'company_db',
Field('name'),
Field('connection_string'),
Field('access_key')
)
I populate this list manually, and this is how I enable a company for
access. Then, I select from this table right there in the model file
to keep the list available in a dict, and create the models on the sub-
DBs in a routine that is called on every record:
def define_company_db_tables(db_C);
db_C.define_table(
'mytable1',
'description', length=128
'otherfield, 'integer'
)
db_C.define_table(
'mytable2',
'blah', length=128
'bleh, 'integer'
)
rows = db().select(db.company_db.ALL)
company_db_dict = {}
for row in rows:
company_db_dict[row.id] = DAL(row.connection_string)
define_company_db_tables(company_db_dict[row.id])
# This line is not required by the app,
# but allows the admin interface to be used
# to query the DBs of each company
exec('db_%d = company_db_dict[row.id]' % (row.id))
The primary reason I do this is so that at some later date it the
company in question wants to self-host the web-app, for whatever
reason, they will be able to easily extact their specific data since
it all lies within a single DB. In the controllers, I check for a
provided access_key either in the args, or in POST-data, and use that
to find the correct DB for that company by doing a select on
db.company_db, followed by an index into company_db_dict. You could
also make the company_db_dict hash directly from the access_key to the
db connection.
I don't know whether this strategy is considered "good practice" or
not, but it's working quite well so far.
