On 29/05/2010 14:27, Michael Ludwig wrote:

> 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.
>

Agree using standards is the way to go, I do use gettext for all the
"program" strings, but I don't think there is any standard (true or
de-facto) to translate data contained in a database.

Karsten's approach definitely works but I am not sure yet how I would
use it from e.g. a reporting tool (Report Manager) or users would deal
with it accessing the db via e.g. ODBC, and last but not least how it
performs (it uses 256 varchar fields which are indexed to lookup
translations and they need to be trimmed down to the allowed size of the
particular column.

>> 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
>
>

Very interesting (as all papers from Ann Harrison), thanks!

Werner