I'm struggling to get my head around if it's possible to change the
datasource on the fly with the TransferLoader interceptor.
I have a large application that has many users and each user has their
own datasource. What I am trying to do is have the user login using
the 'main' datasource, and then their specific datasource is retrieved
from a database field and then applied in the session. Is this
possible? I've looked at the dynamic datasources/environments, but I
don't think that's what I'm after.
As I said, I'm using Transfer via TransferLoader and I have my
datasource aliases set up, but how do I apply them?
The problem with this is that transfer needs a Dsn to work and as far
a you know you can't switch it. I would suggest loading multiple
factories with different configurations of dsn. Then when a user needs
to use a transfer factory then use the appropriate one.
Maybe you could use the following LightWire ConfigBean snippet:
// Transfer Configuration Bean: needed for using ColdBox datasource names
var transferConfig =
CreateObject("Component","transfer.com.config.Configuration").init();
Thanks for the replies. So I take it that if I have 50 users of my
app, then I have to create 50 transfer factories? How would this
affect performance? And then the more users I have sign up to the
service, I'll have to create new factories and reinit the app?
I'm beginning to see that Transfer wasn't intended to be used for
multiple dsn usage. I guess the solution is to forget about ORM and go
the old fashioned route and rewrite my model (thankyou MVC!).
I think you have not explained your requirements.
Really confused why you need so many datasources, like as you said
that "50 users of app then 50 transfer factories".
Even in hibernate you would not be able to do this.
I am building a hotel reservation system, where hotels can login and
manage their bookings/guests etc. As there is the potential for a lot
of information, I need the ability for each hotel to have it's own
database/DSN. I can't have one database that contains every hotel's
data as it would become very big and messy.
So, for example, there could be 10 databases... hoteldb1, hoteldb2,
hoteldb3 etc.
There would also be 1 main database, which would contain the
information relating the hotels to the databases/dsns.
When a hotel logs into the application, the system would grab the dsn
relating to their user ID (and store it in a session?) and then supply
the information from their database.
I'm totally open to the fact that this is perhaps too much for
Transfer or any ORM for that matter, or indeed that I am thinking
about this all wrong.
I do have a similar system that is up and running that's written in
procedural code, but obviously as the app grows, I would like the code
organised in a fashion that ColdBox/OO provides.
I'm pretty new with ColdBox/OO, so forgive me if I'm talking
garbage! : )
By the way, a couple of posts back, when I said "Unless anyone has any
better ideas? " I wasn't dismissing any previous replies, I meant if
anyone had any better ideas than my idea of reworking the model. :Just
re-read it and thought it could have been taken the wrong way : )
Any half-decent database platform should be able to handle millions of records, so I don’t think you need use separate databases if that is your concern. If you really want to split it up then mySQL has partitioning or at a very simple level just have a table per hotel in the same database.
The problem is that there are around 50 tables per hotel (it's a
complex app). If I have 10 hotels using it, then that's 500 tables, 20
hotels = 1000 tables.
I would then need to prefix each set of 50 with a number e.g.
1_bookings, 1_guests etc.
I actually did do that with my original non-OO app and I just don't
like the way it feels, so I put new users on their own datasource.
Plus, if the database got damaged or corrupted somehow, I assume that
it could possibly damage every hotel's data, rather than just one.
If you normalise it well (and have proper security), you could have the data shared across all the platforms, so you still have only ~50 tables in the DB.
But I hear you on wanting to have separate DSN’s for each Hotel to have nice seperation.
If you want to go super redundant - have a separate CF instance per hotel, then if the CF instance falls over, it doesn’t take the other’s with it.
Thanks for the input. I've decided to remove transfer for this
application as I have a deadline and don't want to step into more
unknowns. Luckily, I kept anything talking to transfer in my gateways,
so I just need to rebuild those with plain sql.
Most of it was tql, so I'm just dumping the query to screen and then
copy and pasting it and modifying it to accept the arguments etc. It
brings a tear to my eye to see a couple of lines of tql turn into
about 20-30 lines of sql!!
However, it's been a blessing in disguise as I am one of those people
who dove straight in with Coldspring and Transfer, not really knowing
or appreciating what they did. It's forced me to poke around in the
transfer folders and actually look at what is going on under the hood,
so I have an enormous amount of respect for what you have produced
with the framework.
I don't think I'll need to do the same with upcoming apps (using
separate databases), so it will be transfer all the way.
I generally go the normalization route and have them all in one db,
however, that becomes a severe pain in the ass if you want to move a
couple of clients to their own db server or one client gets big enough
and wants to host their own version and their own data after hosting
with you, etc etc.
I'm now in two minds whether to do the normalization route or not, as
it's getting tiresome doing the sql.
The plan was to have the same application name, but just different
datasources. Why? Can you see a pitfall approaching?
Well you are sharing session’s across your application. It’s a bit of a scary one for session hijacking, or if you code something wrong, and people end up under the wrong hotel.
Of course, if each app has it’s own name, that means a bit more memory per app - it’s not all under the same roof as it were.
With ColdBox you can multiple ColdBox apps (each with it's own
Transfer instance) which can use the same model objects. Have a look
at the ModelsExternalLocationPath setting.