Turbogears app with two database-connection?

[Johnny Blonde]

Hello there.

I´m new to all this Turbogears and web-developments-framework-stuff,
but i have some experience in developing web-apps "manually", with php
and so on.

I need to develop an app, with something "special":

I have one legacy database of our main booking software, interbase
actually, that i need to access. i am definately not allowed to
write/modeify data in it, so it has to be strictly read only. the
model, of course, is given within the database.

for the app itself to save something i need a second database
connection defined (i was thinking of postgreSQL for now). the model
can be defined freely. in here goes all the data entered into the app,
which is, not physically but logically, related to the data in the
legacy DB. this database is the main-database as it will keep all the
data, the other one is just needed to provide some data.

what are your suggestions to realize this thing? is it possible to
have two SQLobject-database-connections? (i got into how to do it with
sqlobject, but not sure how to do it within turbogears?
would i have two separate models for each database?
or span one model over the two? if so, how can i do this?
is it the most convenient way, to reproduce the needed data from the
legacy into the postgreSQL-db?
is it alright to skip defining sqlhub.processConnection and instead
pass a connection to each class in the model.py?

looking forward to any comment or suggestion, idea or trick.

Kind regards,
Frank.

PS: Turbogears (, so far, seems to) ROCK ;-)

[Felix Schwarz]

Johnny Blonde schrieb:

> I need to develop an app, with something "special":
>
> I have one legacy database of our main booking software, interbase
> actually, that i need to access. i am definately not allowed to
> write/modeify data in it, so it has to be strictly read only. the
> model, of course, is given within the database.
>
> for the app itself to save something i need a second database

Had the same situation here and TurboGears provides some very nice mechanisms to
handle it. Just split your model into two different packages and use
turbogears.database.PackageHub.

For example:
---------
legacy.py
__connection__ = turbogears.database.PackageHub("myapp.legacy")
(my legacy model)
---------
model.py
hub = PackageHub("quarantine")
__connection__ = hub
(your app model)

Use the configuration system to define different credentials:
sqlobject.dburi = "mysql://foo:secret@localhost/mydb"
myapp.legacy.dburi = "mysql://bar:supersecret@localhost/xams"

> would i have two separate models for each database?

yes.

fs

[Johnny Blonde]

Hello Felix,

thanks a lot for your nice comment.

i had no time yet to try your tip, but i have few questions on it:

the connection to the legacy is established automatically?
what happens when i "tg-admin sql create"? is the legacy model
processed as well? (i don´t want it to be processed)
how can i lateron access the legacy-model within the app? - i have to
import myapp.legacy in order to access it?

i now have something like this:
=========================================================
=========================================================
from datetime import datetime
from turbogears.database import PackageHub
from sqlobject import *

hub = PackageHub("myapp.legacy")
__connection__ = hub

class Teilnehmer(SQLObject):
class sqlmeta:
fromDatabase=True
idName="NR"
=========================================================
=========================================================

should it work like this?

Regards,
Frank

[Jim Steil]

I have the same situation here.  But, I've taken a totally different approach.  We have an ERP system running on an AS/400.  I have LOTs of other little apps that access data (mostly order and sales history info) from the ERP system.  Instead of dealing with the AS/400 database and two db's in TurboGears, I have written synchronization scripts that run nightly to grab data off the production AS/400 and bring it in to my MySQL database.  This works for me because for my needs, day old data is acceptable.  The other added benefit is that I can now dictate what the model looks like and can make it play nice with SQLObject.  The synchronization scripts are not difficult to write.  Also, I've been playing with another open source tool called JitterBit which will do the whole thing for you if you have a server you can spare for it.

I don't know if this will work in your environment, but this is the way I was able to implement the same kind of thing.  Also, I haven't tried it, but does SQLObject work with Interbase?  I thought there were only a few databases supported.

    -Jim

[Johnny Blonde]

Hello Jim,

yes, it does work with interbase using the firebird driver... it at
least for now does what i need ;-)

well, i fear it won´t be enough to copy the data once a day or twice,
because some data has to be looked up in realtime. after the first
lookup some data will be replicated to the new database by the new
application.

i first was thinking of just replicating it every hour or so, but the
ceo told me, it won´t be enough, as data needs to be FAST, man... so, i
´ll try it this way...

Regards,
Frank

[Felix Schwarz]

Hi Frank,

Johnny Blonde schrieb:

> the connection to the legacy is established automatically?

Yes, its the same.

> what happens when i "tg-admin sql create"? is the legacy model
> processed as well? (i don´t want it to be processed)

I don't think so but I'm not completely sure (I'm not using tg-admin).
Just try :-)

> how can i lateron access the legacy-model within the app? - i have to
> import myapp.legacy in order to access it?

Basically, its just SQLObject so use your "legacy model" as the normal
TurboGears model. Its really just the same (besides fromDatabase = True).

> i now have something like this:

(...)

> should it work like this?

Seems to be fine.

fs