
SQL Tables
----------
These mostly parallel the data structures in the gnc engine.
See table-create.sql for more info.


Session Table, Session Modes
----------------------------
There are four basic modes for accessing the database: "Single User
File Mode", "Single User Update Mode", "Polled Multi-User" and
"Event-Driven Multi-User".  The session table in the database
indicates which mode the database is functioning.

-- "Single User File Mode" -- 
   mode=single-file
   Only one user can have access to the database at a time.  The
   database is used as a glorified file: engine data is 'saved' to
   the database only when the user selects 'save' from the GUI
   dialog.  Just as it would be for a file, this mode erases the
   contents of the database and over-writes it with the new data. 

   This mode exists because it is easy to implement, easy 
   to debug, and has reasonable demands on the database for small
   datasets.  It is not efficient for large datasets, and has 
   a certain amount of risk in its use: because it erases the 
   old data before writing the new data, it is prone to any
   problems during the 'save'. For example, if the database
   crashes during the write (very unlikely), data could be lost.
   If GnuCash has some certain kind of bug (possible), not all
   of the data may be saved.  If GnuCash crashes before a save,
   then any changes since the last save would be lost.

   This mode is mutually exclusive of any other access mode:
   a lockout mechanism prevents other users from accessing the 
   database while it is open in single-user mode.

-- "Single User Update Mode" -- 
   mode=single-update
   Only one user can have access to the database at a time.  Updates 
   to data are stored in the DB as they are made in the GUI; there
   is no need to perform a mass-save at the end of a session.

   This mode is more robust in that there is minimal/no data loss in 
   the event of a crash.

-- "Multi-User Polled" -- 
   mode=multi-user
   Multiple users are assumed, GnuCash polls the database to detect 
   changes in the data.  Mostly implemented. 


-- "Multi-User Events" --
   mode=multi-user-event
   GnuCash uses the SQL LISTEN/NOTIFY async message delivery routines.
   Not implemented.  Probably won't be.  These messages do not provide 
   a sufficient level of detail needed for the kind of notification that 
   would be useful to gnucash.  Ideally, we would like to have notification 
   when some other process has modified a particular record.  But there
   is no way to indicate which record was modified: the local process
   would have to query all records its interested in, thus providing no
   better performance than mere polling.


Safety Lockout
--------------
There is a safety lockout that prevents a single-user mode database from
being accessed by another user, and from having a multi-user mode 
database from being accessed by a single-user-mode client.


Session Design Notes
--------------------
The pgendSyncSingleFile() subroutine performs the equivalent of 'file
save'.  Note that it does this by deleting the entire contents of the 
database, and then writing out the entire contents of the engine.  It
works this way (needs to work this way) in order to make sure that
deleted transactions,etc. are really deleted from the database.  This
is because in this mode, the backend never finds out about deletions.
If you want incremental deletion, then use the 'Single Update' mode.


Connecting to Postgres
----------------------
The Postgres API requires a database to connect to.  The initial
connect is made using the "template1" database, which is the default
database that is always created when Postgres is installed.  Thus,
we assume its always present.


m4 macros
---------
Some of the code is auto-gen'ed from m4 macros.  This mostly just
simplifies some rather repetitive, cut-n-paste code that's identical
from function to function.  If you can think of a better way, let me 
know.


String escapes
--------------
The GUI and the engine support all any characters within a string; however,
in SQL some characters are reserved.  These reserved characters are escaped
in builder.c routine sqlBuilder_escape() before storage.  These convert
single-quotes and backslashes to escaped quotes & backslashes to prevent
SQL corruption.


KVP frames
----------
Storage of KVP values in the sql database is treated more or less as
described in the main KVP docs.  The hierarchical structure is converted
into 'paths' by concatenating key names, and using / as the separator.
(Thus, paths look like file-paths).  The root of each frame is
associated with a guid (and thus, a url kvp://12341234/some/kvp/keys, 
where 12341234 is the guid).

The implementation caches the paths, associating a 32-bit inode number
with each path.  Caching is done because the same path names will recur 
frequently for different guids (e.g. /reconcile-info/last-date will
occur in most accounts). 

The implementation also caches guids (associating a unique 32-bit int
with each), although the utility of this is a bit dubious.  But hey, it
works.  It saves a little bit of storage. 

The actual values are stored in one of 6 different tables, depending on
the type.  Note that the binary type and the glist type are not currently
implemented.  The glist type could be implemented, as long as the glist
only stored strings ... The binary type could be implemented with blobs.


Version Numbers
---------------
Both the Account structure, and the Transaction structure, have version
numbers in them.  These are used to compare the sql and the engine
contents, and update the one or the other as appropriate.  Version
numbers would not be necessary for single-user access, but are important
for multi-user access, where several engines must be kept in sync with
the database contents.   An alternative to version numbers might have
been the date of the last update.  However, version numbers are better
than dates in the case where the engines reside on machines whose clocks
are not closely synchronized.  (e.g. which may happen if the machines
are not using NTP for time synchronization; or, e.g. if one machine failed 
to have daylight-savings time set correctly: its transactions would be 
an hour newer/older than the others, leading to bad updates).

Prices need to have version numbers added.

/* The pgendAccountCompareVersion() routine compares the version
 * number of the account in the engine and the sql database. It
 * returns a negative number if the sql version is older (or the
 * acount is not present in the sql db). It returns a positive
 * number if the sql version is newer.  It returns zero if the
 * two are equal.
 */



Balances
--------
The GUI displays a running balance in the register display.  When the
engine has a copy of all data, this is easy to compute.  However, if
the dataset is large, then we don't want the engine to have a copy of 
all of the data; we want to leave the bulk of it in the database.
However, that presents a problem for computing the running balances.
We could store a running balance with each journal entry.  However,
this has the potential of making balance updates slow: potentially
a lot of entries would need to be updated.

As an alternate technique, we store running balances in a set of
'checkpoints', each showing a subtotal balance for a date interval.
Unfortunately, there is quite a bit of machinery that needs to be 
implemented in order to make this effective.


In order 
Account balances can be computed using advanced SQL statements.
The basic idea looks like this:

    UPDATE checkpoint
        SET balance = (SELECT sum(expr) from .... WHERE 
        txdate between today - 7 and today)

The above is not a valid SQL statement; below is one that actually
works.  Note that this statement updates *all* checkpoints for the 
indicated accountguid.

    UPDATE gnccheckpoint 
        SET balance = (SELECT sum(gncentry.amount) 
            FROM gncentry, gnctransaction
            WHERE
            gncentry.accountguid = gnccheckpoint.accountguid AND 
            gncentry.transguid = gnctransaction.transguid AND 
            gnctransaction.date_posted BETWEEN date_xpoint AND 
            date_xpoint + 360 )
        WHERE accountguid='111';

Its a better to create a function that does the computation:

    CREATE FUNCTION gncsubtotal (char(32), datetime, datetime)
        RETURNS numeric
        AS 'SELECT sum(gncentry.amount) 
            FROM gncentry, gnctransaction
            WHERE
            gncentry.accountguid = $1 AND
            gncentry.transguid = gnctransaction.transguid AND
            gnctransaction.date_posted BETWEEN $2 AND $3'
        LANGUAGE 'sql';

and use it like this:

    UPDATE gnccheckpoint
        SET balance = (gncsubtotal (accountGuid, date_start, date_end ))
        WHERE accountguid='4c9cad7be044559705988c63ea7affc5';

We can find dates for creating checkpoints like so:

SELECT gnctransaction.date_posted 
   FROM gnctransaction, gncentry
   WHERE
       gncentry.transguid = gnctransaction.transguid AND
       gncentry.accountguid='4c9cad7be044559705988c63ea7affc5'
   ORDER BY gnctransaction.date_posted ASC
   LIMIT 2 OFFSET 10;
   
----------------------------
