Werner F. Bruhin schrieb am 29.05.2010 um 12:04:41 (+0200):

> On 27/05/2010 23:41, Michael Ludwig wrote:
> > Werner F. Bruhin schrieb am 27.05.2010 um 10:40:58 (+0200):
> >
> >> I would like to go "real" multi users, i.e. at least tens if
> >> not hundreds of connections.
> >>
> > You can have those connections for only one user. Most web
> > applications use one user - like "www" - representing the
> > application on behalf of whatever user behind the browser owns
> > the session.
> >
> I am still investigating, but the application will most likely not
> be browser based, it might be a desktop app (Python, wxPython)
> "just" accessing the db over the Internet or doing it as an RIA
> (Rich Internet Application) using a tool like
> http://www.captaincasa.com/ .

Don't know that CaptainCasa tool, but it sounds as if it has a Java
server in front of any database it may need. I think that's safer
than directly exposing Firebird to the Internet. Don't know if the
latter option should be considered at all.

> Fair enough, also I am looking for "generic" papers/tutorials on
> dealing with multiple users in the database.

Never seen anything like that. The concepts do not seem to be
difficult, though. Make sure you provide the concept of roles to
have an abstraction layer for flexibility in your user model, just
as FB does with roles.

> It "allows" multi user access over a LAN, what I want to do is
> allow access to the db over the Internet and have a "central"
> database which is shared and a "local" database which is
> owned/controlled by each user. Obviously a potential
> synchronization headache (or a nice challenge :-) ).
>
> The shared data is all the master data such as countries, wine
> regions etc etc, and all the wine specific data such as wine
> information, vintage, tasting notes etc and then there is private
> data such as the consumption and purchase history.

I see, If the app requires Internet access anyway, it could probably
also store all the data on the central server. And if not, the
shared data could probably be synchronized from the main server to
the local database, just like a system update. I think I wouldn't go
for a hybrid model where parts of the data are stored locally, and
others remotely.

> The challenges I am already aware of are:
>
> - within the master data most tables have to handle multiple
> languages, current shareware supports this, but only one language
> is active (i.e. all users accessing the system have to use the
> same one). So, needs to be redesigned and allow language
> selection per user. For this I have two different designs done in
> a test environment. One is an "gettext" type implementation using
> stored procedures (Karsten Hilbert from the gnuMed project did the
> original implement and the second one uses a "translation" table
> per table for any column which needs translation. Still very much
> open for other ideas on how one could deal with this.

I'd always use some tried and true de-facto standard, like, for
instance, Gettext.

> Do I have to worry about concurrent access to the same data or can
> I just "depend" on FB handling this for me. I.e. two users access
> the same row, can I count on the FB engine to allow both updates
> to go through, the later one will obviously overwrite the earlier
> one.

Or run into an exception because of an update conflict:

Statement failed, SQLCODE = -913
deadlock
-update conflicts with concurrent update
-concurrent transaction number is 5780

You have to think about your transactions and configure them as most
suitable for your application. Here's an interesting article on the
topic of transaction configuration:

Firebird for the Database Expert:
Episode 5 - Locking and Record Versions
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_expert5

--
Michael Ludwig