Make Web App Connect to different databases depending on SpecialURL

Hi Guys,

Let me explain what I am planning to do in a more detailed manner:

I am having a webApp which connects to a XojoSQL Database File on disk. The database connection is a property of the App, so everyone using that webApp is using this connection which is working fine.
What I would like to do now: I have a second server which should only run the webApp, just because the server should be fast, and not being locked up by other things like mail servers, etc, etc. This shall be a hosted service for users of my app, who dont have the possibility of hosting it themselves. In order to minimize manual work for me, I am planning to do the following:

  • Whenever the user creates an account on my Website (which is a different service) and selects the “hosted” option for purchase, they will have to create a recurring payment via paypal. If this event is successfull, the Joomla Component I am using for this is putting them into a special user group for that.
  • Now this Joomla Installation will make a call to the other server running the webApp, telling it to create a subdomain named after the username of that user in joomla
  • Now, when you access that subdomain, it will redirect you to lets say, http://blabla.domain.com/USERNAME:8080 which is my webapp, which will now trigger the SpecialURL event
  • Now the SpecialURL event shall make a call to the joomla installation and check whether this user is still on his recurring payment plan. If he is, connection to a database file (probably session based, then) which has the same name as the user, (if its not there creating it) will be made.

In my head this already works out great. And I have already written a joomla component that is able to report a boolean value back if this user is member of a specific group in joomla.

BUT, only one session at a time may connect to this database, as it is a file database, right? Any clever Ideas to get around that and minimize the work of connecting to MySQL, creating a MySQL Database dynamically and so on…?

You can have multiple sessions use a single database file. Use the WAL mode to improve performance. In fact, you should be making your database connection at the Session level already, NOT at the app level. Having multiple sessions use a single database connection is asking for trouble. Give each Session its own database property and database connection. It works fine.

Hi Tim,

thanks so far. I’ll give that a try ASAP. I read somewhere that its clever to store a reference to the session on the page, as this would improve performance… Whats the best way to do that?

Dim sess as new Session sess = session ?

And furthermore, just reading a bit on the SQLite Reference, and am wondering if I need to to checkpointing and syncing to disk manually, or if Xojo is keeping track of this automatically.
Also, when I set the db to MultiUser=true, which should turn on the WAL mode, I do not see the additional files being created… :-/

[quote=54467:@Denis Hessberger]Hi Tim,

thanks so far. I’ll give that a try ASAP. I read somewhere that its clever to store a reference to the session on the page, as this would improve performance… Whats the best way to do that?

Dim sess as new Session sess = session ?[/quote]

Don’t store hard references to Sessions. You’re asking for memory leaks if you do that.

Hi Denis,

You’d really need to provide a lot more background information about your application in order to get appropriate feedback, for example:

  • Is this an intranet or internet application?
  • How many users are you expecting and of that number how many will be concurrent?
  • how much data are we talking about and how complex is the database
  • The nature of the application: is it just browsing data, server side calculation intensive or is it a typical administration type app, with a lot of data capture?

My initial expression is that your approach is to try and replicate a desktop style application running on web technology and while that works fine for let’s say a work group solution, it could break horribly if subjected to a large number of concurrent users for instance.

Out of the box XOJO is well placed to enable developers to be very productive in building workgroup style applications and the examples etc. promote this. But to go beyond that, you need a different approach and while I see no reason this can not be done using XOJO, there seems to be no good examples to borrow from.