gnucash-2.3 + sql backend

[Phil Longstaff]

On June 6, 2009 05:16:33 pm Klaus Dahlke wrote:
> On Sat, 6 Jun 2009 13:09:58 -0400
>
> Phil Longstaff <plongstaff at rogers.com> wrote:
> > On June 6, 2009 12:38:14 pm Klaus Dahlke wrote:
> > > Hi Phil,
> > > I digged around a bit and found the following statement in
> > > gnucash.trace:
> > >
> > > * 18:31:18  INFO <qof.session> [qof_session_load_backend]  selected
> > > GnuCash Libdbi (POSTGRESQL) Backend * 18:31:18  CRIT <gnc.backend.dbi>
> > > [pgsql_error_fn()] DBI error: could not connect to server: Connection
> > > refused Is the server running on host "localhost" and accepting TCP/IP
> > > connections on port 0?
> > >
> > > the server is up and running, started even with the -i option:
> > > postgres 22560     1  0 18:30 ?        00:00:00
> > > /usr/lib/postgresql-8.3/bin/postgres --silent-mode=true -i
> > >
> > > The server listens to the default port :5432 and works normal.
> > >
> > > Any ideas?
> >
> > Yes, there's still a problem, and I just fixed it (r18108).
> >
> > Phil
>
> Hi Phil,
> in meanwhile, I figured out to correct that myself: I changed the line to
>
> gnc-backend-dbi.c:      result = dbi_conn_set_option_numeric( be->conn,
> "port", 5432 );
>
> which works for me in the moment (having an hard coded port number is
> probably not the best way).
>
> With that, saving into an existing empty postgres database works fine. But
> I have a delicate problem when it comes to re-open the database.
>
> a) I used 'save as postres' to save foo.xac into the postgres. Works fine.
> b) quitting gnucash
> c) starting gnucash without any other command
> d) it then loads postgres://localhost/dbname:user:password quit fast
> e) unfortunately, some accounts show wrong balances
>
> If an account shows a wrong balances, then gnucash has calculated a balance
> less then zero for the time of when the first entry occurs. Example:
> received an interest payment for a savings account. The first line the
> account shows then: date: 1.1.2002, income=3.67; balance=-44.11
> Thus, gnucash has calculated a balance of 47.78 being present on
> 31.12.2000.
>
> Surprisingly, this is not the case when using sqlite3. The file got saved
> and after quitting gnucash and reading the sqlite3 database, the values are
> the same as before and as in the .xac file.
>
> When further investigating the case, I saw that the differences might be
> linked to having not reconciled entries. On the command line I set all
> entires to be reconsiled: gnucash=# update splits set reconcile_date=
> '20090606120000' where reconcile_date='19700101000000'; UPDATE 9539
> gnucash=# update splits set reconcile_state= 'y' where reconcile_state='n';
> UPDATE 9539
>
> After reading the so modified gnucash database, the values are correct.
>
> Again, it surprises me that when reading a postgres database the sorting
> via reconcile_date has an impact and for sqlite3 not.
>
> BTW: there is an entry in gnucash.trace regarding the timestamp:
> * 23:11:13  WARN <qof.engine> [gnc_iso8601_to_timespec_gmt()]  mktime
> failed to handle daylight saving: tm_hour=0 tm_year=70 tm_min=0 tm_sec=0
> tm_isdst=-1 for string=1970-01-01 00:00:00

I use the statement "SELECT account_guid, reconcile_state, sum(quantity_num) 
as quantity_num, quantity_denom FROM splits GROUP BY account_guid, 
reconcile_state, quantity_denom" to calculate and load the account balances.  
SQLite3 returns the list sorted by account_guid and reconcile_state, but 
postgresql doesn't.  Turns out the list needs to be sorted, so I just added 
"ORDER BY account_guid, reconcile_state".  Committed as r18109.

Phil