gnucash multi-user sql

Mon Sep 22 14:43:50 CDT 2003

On Wed, Sep 10, 2003 at 01:30:13PM -0400, Brian was heard to remark:
> 
> Is there any documentation anywhere about how to do sql updates to the
> database. I'm interested in creating a web script interface for gnucash.

Yes, but unfortunately buried in src/backend/postgres/README.
We need to get these posted on the website.  Maybe real soon.

1) please subscribe to gnucash-devel at gnucash.org

2) I've attached excerpt below.

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

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

Note that you (or your SQL database admin) will have to make sure
that access permissions on the various GnuCash tables have been set
appropriately.  Typically, this will be by starting the 'psql'
shell, listing all relations with \z, and then issuing
a 'GRANT ALL on gncAccount TO someuser;', and so on, for each
relation.   Failing to do this will result in lots of unhappiness.
The sysadmin will also need to make sure that TCP/IP connections
are properly enabled in the postgres server hba.conf file.

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

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

The first two are single-user access modes.  These are the
'safest' modes to use if you don't need multi-user capability.
The single-update mode is strongly preferred over single-file.
(The single-file mode is strongly deprecated, and can lead to
to massive data loss under certain circumstances: for example,
if your sql connection dies after all the old data has been
deleted, but before the new data has been written out.  Bluntly:
DO NOT USE mode=single-file.  You won't be sorry.).

The multi-user modes are intended for use when multiple users
need to view and make changes to the data.  The default mode
is multi-user.

If you have many users (more than a half-dozen or so), you may
want to run in multi-user-poll mode, instead of the default
multi-user.  This will stop the automatic updates of the
local instance of gnucash, and thus reduce the amount of
GUI redraws that happen automatically as other users
edit data.  It should also reduce network traffic
slightly, and present a slightly lower load on the
sql server.  Different users can mix-n-match the
two multi-user modes.

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 appear 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 their work being clobbered.
Other Options
-------------
The 'options=' and 'tty=' postgres keywords are supported. See the
postgres documentation for more info.

-- 
pub  1024D/01045933 2001-02-01 Linas Vepstas (Labas!) <linas at linas.org>
PGP Key fingerprint = 8305 2521 6000 0B5E 8984  3F54 64A9 9A82 0104 5933