Distributed-Java Lab#3 design document, persistent storage

To support configuration changes without requiring recompiling the java
code, I'll use a configuration file lab3.properties, which will be
loaded to a resource bundle and then searched for various keys. To make
editing easier, a level of indirection will be used. For example, the
Driver key is redirected to either the Linux.Driver or Windows.Driver
key, each of which has the name of the particular jdbc driver for the
database in use on that system, Cloudscape and MicroSoft ACCESS
respectively. Switching between use on different systems then avoids
deleting or commenting-out information not relevant to the current
system, making it easier to restore such information when switching
back. The details, specific keywords, and method of switching among
various systems, will be worked out later as I write the code.
This file will be edited manually, so be very careful!

To support authentication of dispatcher-people via a login form, a
table called Users will contain records of the form:
  Seq (int, primary key) Sequentially-assigned
  Name (varchar) Human name of person
  Pass (?) Password or encrypted-password, to be decided later.
A local utility called AuditUsers will be used to build and maintain
this table. The supervisor should make sure that only one instance of
AuditUsers is being run at any time.

To keep track of logged-in users and their sessions, a table called
ActiveUsers will contain records of the form:
  UserID (int, foreign key = Users.Seq)
    Identifies person as listed in Users table
  Expire (longint faked as varchar(14)) Tells when current login session
    will expire, given as milliseconds since epoch, coded as decimal string
  SessionID (varchar) Generated randomly at start of session, passed to
    client somehow such as via cookie, compared to verify which session
    is making a particular service request.
Records are added by the login module. Records are deleted by the
logout module, and optionally by various auditing routines that check
Expire against the current date&time.

To keep track of the text of messages that have been sent to taxicab
vehicles, a table called Msgs will contain records of the form:
  MsgID (int, primary key) Sequentially-assigned (see Note below)
  Message (varchar(80)) Text of the message
Note: To create a new record, the algorithm is to compute the maximum
of the MsgIDs of all existing records, add one, and make that the MsgID
of the new record. But this whole process is *not* a single atomic db
command, rather it's a sequence of two different db commands, so it's
possible for two threads to interleave their db commands and thereby
get an invalid result. For example, after there is one message with
MsgID=1:
  Proc#1 queries for maximum, gets 1.
  Proc#1 adds 1, gets 2.
  Proc#2 queries for maximum, gets 1.
  Proc#1 adds record with MsgID=2, no problem.
  Proc#2 adds 1, gets 2.
  Proc#2 tries to add record with MsgID=2, failure, already such a record.
To avoid this problem, a lock is used to prevent more than one thread
from starting this process during the same interval:
  Proc#1 tries to get lock, succeeds.
  Proc#1 queries for maximum, gets 1.
  Proc#1 adds 1, gets 2.
  Proc#2 tries to get lock, fails, will try again later.
  Proc#1 adds record with MsgID=2, no problem.
  Proc#1 releases lock.
  ...(Proc#2 still waiting before re-try)...
  Proc#2 tries to get lock, succeeds.
  Proc#2 queries for maximum, gets 2.
  Proc#2 adds 2, gets 3.
  Proc#2 adds record with MsgID=3, no problem.
  Proc#2 releases lock.
At the present time, I plan to implement this locking mechanism by the
process trying to add a specific record to a database called Locks. If
it succeeds, there was no previous record, otherwise some other process
must have already set that lock. This method will be thread-safe even
if the processes/threads are running on different JVMs, providing that
there's only a single instance of the database and corresponding data
source but multiple connections to that data source. Releasing the lock
will then be deleting that specific record from Locks.

To keep track of taxicabs that may receive messages, a table called
Cabs will contain records of the form:
  CabID (int, primary key) Sequentially-assigned
  Name (varchar) Name of that taxicab, usually name of primary/sole driver
  OnDuty (boolean) True if that taxicab is currently available
A local utility called AuditCabs will be used to build and maintain
this table, and to update the OnDuty fields. It is a policy decision
whether these records stick with the vehicles or with the drivers if
and when the relation between drivers and taxicabs changes. The
supervisor should make sure that only one instance of AuditCabs is
being run at any time.

To identify which taxicabs received which messages, a table called
MCrelat links the Msgs and Cabs tables by records of the form:
  MsgID (int, foreign key = Msgs.MsgID)
  CabID (int, foreign key = Cabs.CabID)
There is no synchronization problem when more than one
dispatcher-person is posting a message, because each dispatcher-person
is working with a different MsgID hence guaranteeing no conflict
between MCrelat records. The sequence of these MCrelat records is not
important, so interleaving of several records corresponding with
different messages is no problem.

As mentionned above, to provide a system-independent locking mechanism
for non-atomic sequences which must be synchronized with any similar
sequences running in a different thread possibly on a different VM
possibly even a different version of the program: A table called Locks
which contains records of the form:
  LKey (string, primary key, unique)
  UserName (varchar)
where LKey is guaranteed (by the DBMS) to be unique, and UserName is
supplied to help diagnose any bugs that may occur.