Subject RE: [firebird-support] Install Firebird
Author Helen Borrie
Hello Ken,

At 01:47 AM 9/10/2004 +1000, Ken Galbraith wrote:
>My application (Accommodation Reservation System) like (I imagine) any
>application, has quite a few "static" tables which end users can't (& I
>don't want them to) update in any way. When the "Embedded" version of
>Firbird was announced, I was hoping that I could use this to access "read
>only" tables & access "read/write" tables from a server. This does not seem
>to handle my requirement of multiple "instances" of my application running
>on the same workstation.

No; since a database being accessed by one instance of an embedded server
can't be simultaneously accessed by any other instance.

>Your mention of running multiple servers looks like it would handle my

From your description, yes it would, but don't try to get too complicated
here. Use the Classic server on the local workstations. That will do just
what you want to do, but can't do, with embedded.

>Please correct me if I am making the wrong assumption, but I have
>ascertained from the entries in the support n/g that while the "embedded"
>version of Firebird allows accessing db's from a server as well as access
>to a local DB, only "one" task on the W/s can access the local DB.

Only one process in the world can access a db that is being accessed by an
E/s - the one that is running.

>It would be really nice if "read only" db's could be accessed from the
>client m/c & the dynamic R/W db be accessible from a server! I don't believe
>I am an orphan in regard to this requirement as many applications need
>access to read only DB's (such as Location, Postal Code lookup, Country Code
>lookup [in fact a whole range of "lookup tables"] which if they were located
>on client m/c's would eliminate a heck of a lot of network traffic).

Sure: run Classic on the workstations and then each application instance
can have its own process with server instance. The downside to this is
that each application instance will compete with the others for page cache
resources, whereas with SS they would all share one cache. As I understand
your requirements, we are not talking huge resource usage in any case, so
it's a fair guess that it wouldn't make much if any difference for you.

>Maybe the question I am asking is "if a DB is "read only", can multiple
>instances of embedded Firebird access it from a client m/c"? & if not - why

No. As for "why not", the embedded server is just superserver process and
superserver opens the database file with exclusive access. Classic gives
you the same compactness without that limitation.

>because to me that would be an invaluable facility for any application
>I can think of! With the horsepower of current client m/c's, I can't see the
>overheads you have mentioned being a problem.

As I suggested earlier, don't try to complicate matters. Embedded was
introduced for a particular purpose - to create a totally self-contained
stand-alone database application that didn't need a full server
installed. It doesn't fit your requirements -- but there are
configurations that do.

>Lester, I seem to remember you
>mentioning using UK Postal code lookup and that DB must be an order of
>magnitude larger that Australia's (~14,000 post codes) due to the UK having
>PC's down to the street number level (I believe). Even with N/W's going to
>the 1 & 10 Gbps range, lookup tables of a large size are a drain on the
>server Disk I/O and would be better serviced as you have intimated by local
>access thru' a local server.
>PS: When I mentioned r/o & r/w tables above, I am aware that they would need
>to be in separate DB's!

Hmmm. there is no such thing as "r/o and r/w tables". You use SQL
permissions, views and triggers to restrict access to tables.

But there is such a thing as a read-only database. Obviously you are
talking multiple databases here, since a database can't be both read-only
and read-write and a remote server can't be asked to read a local database.

It's valid enough to consider accessing local databases for truly "static"
stuff if it's voluminous but, for example, postcode databases do
change; country codes change; other lookups can be supposed to (tax
rates, lookups to MYOB data, et al.)

The advent of the r/o database opens up the possiblity of distributing your
static lookup databases on CD-Roms or other removable devices (SD cards,
memory sticks) thus avoiding the need to deal with the problem of updating
read-only databases remotely (a nightmarish prospect) when lookup data
changes. Combine that with Classic on the clients for your low-octane
deployments and you've got exactly the solution you dream about.