
This directory contains code for SQL/Postgres support.  The
SQL backend can be used in several modes. The single-user modes
are beta: they work for me, but haven't had the benefit of lots 
of testers whacking in it and bug-hunting.  The multi-user mode 
is nearing completion: it has a variety of problems, but may be 
usable.



Postgres Install Instructions
-----------------------------
1) Install PostgresSQL server, client and devel packages.
   (Both the 6.x and the 7.x versions of Postgres should work).

2) if installed from redhat, then running /etc/rc.d/init.d/postgresql 
   will setup and initialize basic Postgres first-time setup & config.
3) as root, 'su - postgres' then run 'createuser' to add your user id
   (don't set a password on your postgres db name, yet, GnuCash doesn't 
   have a GUI to ask for your password yet)
   If you've forgotten what users are on the system, you can check
   by starting the 'psql' command shell and typing the command.
   'SELECT * FROM pg_shadow;'
   Note this only works for the postgres user, and other users that 
   have createuser permissions.

4) (optional) enable TCPIP connections to remote hosts. To do this:
   edit the file pg_hba.conf on the remote host to configure and allow 
   access from other hosts.  See 'man pg_hba.conf' for details.  
        RedHat:   /var/lib/pgsql/pg_hba.conf  
	Debian:   /etc/postgresql/pg_hba.conf 
   Also create users.  Be sure to set a password.
   (there appears to be no way to pass an empty password to postgres)
   Note also the user must have 'createuser' permissions in order to 
   lock tables (this is a bug in postgres 6.5 and maybe later ???)

5) Hints and Tips:  
   If you've forgotten what databases you've created in the past,
   you can look the filesystem: 'ls -la /var/lib/postgres/data/base'
   to view the existing databases.  Alternately, if you connect as 
   user postgres, you can 'SELECT * FROM pg_database;'


Alternatively, you can install into a brand new database without
using root privileges. After step 1 above, perform the following:

1) Install postgresql server, client and devel packages.
2) initdb -D ~/gnucash
   This creates a directory structure and supporting files
   under ~/gnucash. The gnucash directory is automatically 
   created. The directories and files are owned by the user
   running 'initdb' and have only user access; group and
   other are cleared.

   By default, the user running 'initdb' is setup as the
   database superuser for the new database.

   You don't have to use the name 'gnucash'; you can choose
   whatever you want.

3) pg_ctl -D ~/gnucash -o "-p 6543" start

   This starts a postmaster process for the new database
   listening on port 6543. If your system doesn't already
   have a postmaster processing running for the system
   database, you can leave out '-o "-p 6543"' and it will
   use the default port 5432. You can use any open port.

   By default, the postmaster process will only accept
   connections from processes on the local host.



GnuCash Build Instructions
--------------------------
Same as usual, but you must specify the flag '--enable-sql' in order
to build in Postgres support.  
i.e. 
./configure --enable-sql
and then 'make'.


How To Use This Thing
---------------------
a) Open your favorite datafile in the usual fashion.
b) Click on 'Save As'
c) enter the following URL instead of a filename in the file picker:
    postgres://localhost/some_dbname_you_pick

The above steps will copy your data into that database.  You can 
then restart gnucash (or keep working) and type in the same URL
in the file open dialogs.  Or try it on the command line:

/usr/local/bin/gnucash postgres://localhost/dbname_whatever

Note: GnuCash will automatically create the database if it does
not already exist. Do *not* try to create the database by hand,
or to specify a database that wasn't created by GnuCash.  

Warning: the postgres backend is in active development, 
and occasionally the database format changes in a way that is
not backwards compatible.  If you have data that you want to 
move from older to newer versions, you can do the following:
-- start up older version of gnucash, open your postgres://...
   URL.
-- 'save as' to an ordinary gnucash file (its a flat XML file)
-- exit the program.
-- start the newer version of gnucash, open the XML file.
-- 'save as' a new postgres database. (be sure to pick a new name).
   

Remote Access
-------------
In principle, you can use URL's such as:
postgres://some.where.com/dbname
However, these URL's require that the host 'some.where.com' have
TCPIP access enabled (by following instructions (6) above).
This is true even if the hostname is your local machine.  Thus,
unless you've setup Postgres TCPIP connections, you *MUST* use
URL's of the form postgres://localhost/dbname

You can specify usernames and passwords in the URL as follows:

postgres://localhost/dbname?user=whomever&password=asdf

Note that the username and password are the pg_shadow username and
password, *NOT* your unix login name and password.

(A GUI dialog to prompt for your password hasn't been implemented yet.)

Access Modes
------------
There are three different ways or 'modes' in which to access your 
database.  These are 

mode=single-file
mode=single-update
mode=multi-user

The first two are single-user access modes; single-update is the 
default mode (and the preferred single-user mode).

The multi-user mode is mostly implemented and might be usable.
See the file 'design.txt' for an in-depth explanation of these 
modes.

You can specify a particular access mode by specifying the URL

postgres://localhost/dbname?mode=whatever

You can alternate between multi-user and single-user modes for
the same database.  Just be sure that all users have logged off,
otherwise gnucash won't let you log on in single-user mode. 
Users are 'logged off' automatically when they exist gnucash.
In case they have crashed, (and thus apear to still be logged
in), you can log them off manually by issuing  the following:

echo "UPDATE gncsession SET time_off='NOW' WHERE time_off = 'infinity';" | psql dbname

Hopefully, you do not need to be warned that this dangerous
if there really are other users logged in.  Accessing the
database in single-user mode when there are other users logged
in will result in thier work being clobbered.

Other Options
-------------
The 'options=' and 'tty=' postgres keywords are supported. See the
postgres documentation for more info.


Mangled Balances
----------------
If you've been working in multi-user mode for a while, and the 
balances seem to be mangled, log off all the users, log on the the 
same database in single-update mode, and log off again.  This
will cause balance checkpoints to be recomputed, and should 
probably fix any weird account balances you are seeing.

(Mangled account balances shouldn't happen, and if you have
a test case that can reliably recreate these, you should
submit a bug report).

(Note to developers: think of this as an fsck: balances
shouldn't get wacky, but if you've been running a long time
and something weird has happened, then a periodic cleanup 
is good to have.  Maybe this should be automated ...)


To Be Done
----------
Core bugs/features that still need work:

(none in this list are truly critical at this point, except possibly
the username/password dialog, and its effect with regards to initial db
access).

-- Wire in the GUI to ask user for username/password to log onto the
   server.  (GUI already implemented, just not yet used).  

-- change code for initial db access. We should query the pg_database
   table to see if the database already exists.

-- distinguish between 'save' and 'save-as' in gnc-book & backend.
   If user hits 'save' in the single-user or multi-user mode, it 
   should be a no-op (since the saves have already occured).  Only 
   a 'save-as' requires a from-scratch sync.

-- add version number to the DB, so that new sql db's can be 
   auto-upgraded if table schema change.

-- clear up end/destroy semantics.  After book_end is called,
   its not possible to start a new session, since there are
   uninitialized pointers.  Unless to book is deleted immediately,
   everything is hosed.  This is a generic backend design problem,
   not just an sql backend problem, that needs fixing.

-- allow user to enter URL in GUI dialog.  User can currently type URL
   into the file dialog window; it would be nice to have something 
   slightly nicer tan this.

-- error code should include strings passed back, to be shown in 
   GUI dialogs. This is because the backend needs to return things
   like usernames, etc. in the dialogs, and the backend doesn't 
   have the interfaces for passing this kind of info.
   (actually, the PERR/PINFO strings might do ??)
   In some cases, we have PERR without setting a backend error...
   grep for all PERR's that don't set backend error.

-- note that transaction commit errors may in fact be i/o errors.
   If an i/o error occured during commit, there would be some
   abberant rollback behaviour. Ouch.

-- the transaction rollback code needs to be a gui popup...

-- fix the annoying postgres:,,localhost,asdf file syntax: needs
   mods to gnc-book to keep it happy about lock files & such.
   (coord with rlb on gnc-book redesign)

-- review (actually, design & architect) the communications error
   handling policy.  For example, CopyToEngine() will leave
   the backend in a disabled state if a communication error occurs;
   there will be other debilitating conditions if the backend disappears,
   leaving the engine in a possibly confused state.

   For example, if postgres user is misconfigured, the LOCK TABLE
   will fail on session validate, and FINISH_QUERY will close the
   connection.  Subsequent sql will core dump gnucash, even though this 
   is a sysadmin error.

-- during open of register window, a query of splits is typically run
   twice (once during open -- LedgerDisplayInternal(), and again
   during LedgerDisplayRefresh()) this results in twice as much data 
   to the sql backend as needed.  This is a performance issue,
   should someday be fixed.

-- during transaction edit, query of splits is run twice --
   once during edit recordCB()->xaccSRSaveRegEntry(), and again
   recordCB()->xaccSRRedrawReg().  As above, this is performance 
   problem waiting to aggravate.

-- minor optimization: KVP's for null strings store null strings, 
   this is a waste of time. This occurs primarily for transaction 
   memos, I think.

-- minor optimization: keeping an iguid cache will avoid sql queries.
   also caching the count for single-user mode would be good

To Be Done, Part II
-------------------
This list only affects the multi-user and advanced/optional features.

Most of the items on this list are 'critical' in the sense that
multi-user mode is fundamentally broken unless they are fixed. 

-- before going beta: turn down debugging.  

-- if always in multi-mode, then initial checkpoints not set up.
   They're not needed until there are a lot of transactions in the 
   system.  Need to recompute checkpoints on some periodic basis.

-- we should use LISTEN/NOTIFY to let others know tht things have 
   changed.  This is of limited utility, but still...
   It could be used to update the balances in the main window 
   dynamically.

-- during sync, detect and report conflicting edits to accounts 
   and transactions.  See the notes for pgendSync() for details
   as to what this is about.  For the first pass, this is not a 
   serious issue; its a 'nice to have' thing.  (sync is called
   when user hits the 'save' button, and should be disabled for 
   multi-user modes)

-- implement account rollback (i.e. if other user has modified the
   account, we need to do something to merge their work into ours...)
   ditto for prices ...

-- bug: if another user deletes an account, we need to look at the 
   audit trail to see if the thing has been deleted.
   Otherwise, any edit of this account will incorrectly
   add the deleted account back in. (Note that from the user
   perspective, deleting accounts is a bad idea ...)

-- fix caching in the face of lost contact to the backend.  If the
   backend can't contact its server, then we should just save up caches,
   and then when contact with backend re-established, we should spit
   them out.  The pgendSync routine should more or less do the trick;
   note, however, that the file format needs to save the version number
   ...

-- Implement various advanced database features, such as checking the
   user's permission to view/edit account by account ... (hmmm this
   done by the dbadmin... using SQL commands... which means if user
   tries to write to something they're not allowed to write to,
   then they should be bounced back.)  Does some user have the permission
   to create new accounts ??

-- Review versioning verification in backend. The desired semantic for 
   updates should be like CVS: multiple nearly-simultaneous writers 
   are allowed; however, only one wins, and others are kicked back. 
   The losers know themselves because they are trying to update info 
   of the wrong version. 
   -- pgend_transaction_commit does it correctly; but the GUI doesn't
      report a rollback.  (need to get err message out of engine, into 
      gui).
   -- pgTransactionSync() is broken, but its not used anywhere.
   -- pgend_account_commit checks version but doesn't rollback.
      (nor does the GUI report a rollback.)
   -- pgendSync does the right thing, except that it doesn't
      detect any version conflicts, nor does it notify the user
      of such conflicts.  (actually, it also screws checkpoints).

   I'm not sure how critical this all is; with a small number of users
   it shouldn't be a problem.  With a huge number of users, each editing
   the same transaction (unlikely!?) then there is risk of clobbered
   data, but so what?  versioning is at least partly a people-management
   problem.  Anyway, what's there now should be pretty good & should
   work for now.  Except its mostly untested.

-- review & match up against docs at 
   http://www.lupercalia.net/gnc-db/

-- nice-to-have performance improvement:
   many queries are needlessly duplicated, even in multi-user mode.
   They are duplicated just in case some other user has modified
   the data.  But if we last checked just a few seconds ago,
   we don't really need to update, not yet.  We can wait a few 
   seconds ...  doing this uniformly can cut down on sql traffic.

-- use version numbers for commodities.  Right now, multi-user
   updates of commodities are not detected (this seem OK for
   now, since this is a rare occurrence, right ???)

-- multi-user 'save-as' has unexpected results:
   As it currently works, save-as (sync) is a mass-copy of all data 
   out of the engine into the named storage location.  If the indicated 
   storage location doesn't exist, its created, and all of the engine 
   data is copied into it.  If the storage location (i.e. database) 
   does exist, and contains data, then the engine contents are merged 
   into it.

   From the engine point of view, the above is the easiest thing to do.
   However, in the multi-user mode, it may not be what the user is 
   expecting.  In the multi-user mode, the engine would not contain 
   a full copy of the database data; instead, it contains only a subset. 
   A 'save as', if implemented as above, would save only that subset, 
   instead of all of the data.   If the user thought 'save as'
   means the same thing as 'copy', and hoped to copy all of the sql 
   data to a file, they would be sorely disappointed.  I don't 
   currently have an opinion on the 'best' way of dealing with this 
   situation.  A reasonable solution may well be 'don't do that'.

-- Implement PR_BALANCE query type in gncquery.c.  This query is 
   supposed to return unbalanced transactions from the database.
   I think it might be better to leave this unbalanced, and just
   plain force everything in the database to be balanced.  In this
   case, we should then modify the code to check for unbalanced 
   transactions before commiting them, or otherwise force the backend
   to run in balanced mode.  We could set 'force_double_entry=2'
   but this is currently unimplmented in the engine.  Alternately, 
   we could call 'Scrub' at appropriate times.
    

============================= END OF FILE ========================
