
This directory contains code for SQL/Postgres support.  The
SQL backend can be used in several modes. The single-user modes
are more-or-less beta: they work, but haven't been well tested.
The multi-user code is experimental: it still has a variety of
problems.



Postgres Install Instructions
-----------------------------
1) Install postgresql server, client and devel packages.
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 wnly works for the posgres user, and other users that 
   have creatuser 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 Postrges 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.  

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.


Access Modes
------------
There are four different ways or 'modes' in which to access the database.
These are 
mode=single-file
mode=single-update
mode=multi-user-poll
mode=multi-user

The last mode is preferred, but not yet implemented.
See the file 'design.txt' for an explanation.
Enable these by specifying the URL

postgres://localhost/dbname?mode=whatever

The current default is single-update, but will soon be multi-user-poll


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


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

-- distinguish between 'save' and 'save-as' in gnc-book & backend.

-- single-update mode is asking to 'save' data  at end of session
   fix this (again,  ...)

-- allow user to enter URL in GUI dialog, get GUI to remember the URL 

-- Implement GUI to ask user for username/password to log onto the
   server. 

-- add support for rlb's price db

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

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

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


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

-- checkpoint ending balance is showing up as starting balance

-- if another user deletes a transaction, or an account, there is no way
   for us to know.  Thus, any sort of sync will proabably (incorrectly)
   add the deleted transaction back in.  Question: what is the best way
   of having the backend detect that another user has deleted a 
   transaction?
   Method A) keep a master list of all transactions, and compare to
     that.
   Method B) keep an audit trail of deleted transactions, and check
     that.

-- store account balances in database.  This will be tricky ...
   -- split query gets hard ... 
   -- use postgres aggregates ?? 

-- provide support for more query types in gncquery.c

-- multi-user 'save-as' is incorrect:
   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', they would be sorely disappointed. 
   I don't currently have an opinion on the 'best' way of dealing with 
   this situation.

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

-- transaction rollback is 'incorrect'; sort of ?? since we should
   roll back to what's in the db, and not the old stored transaction ...
   (Currently, the register uses xaccTransRollback() to implement
   a single level of undo; it performs the 'undo' from what it has
   cached, rather than from the contents in the DB.  This is OK,
   as long as some other user hasn't modified the DB in the meanwhile.)
   This may not be a problem, as practically speaking, its highly
   unlkikly that one user is modifying data while the other is undoing
   the same transaction.

-- 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 logging history/audit-trail in the SQL server.  
   i.e. save the old copies of stuff in log tables.  Make the username 
   part of the logging scheme.   Having 'audit trails' is considered 
   to be an important accounting feature.

-- let all attached client receive update events via SQL LISTEN/NOTIFY 
   events.

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

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

   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/

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


