Postgres backend findings

[Dwayne Miller]

I found a simple way to allow an existing database to be used by gnucash 
and postgres without altering code in
pgend_session_begin(). But I'm not sure of the best/proper way to 
implement it.

Problem occurs when an existing database is specified by the user and 
the database contains no gnucash related tables. During the check of the 
version of gnucash tables, the gncversion table is created because it 
does not exist. Gnucash then recognizes that the database structure is 
old and asks the user if they would like to upgrade. Answering yes 
results in gnucash checking for the existance of the gncaccount table. 
This does not exist, so it assumes the action should be to create all of 
the tables (Good so far). However, the create table script will try 
first to create the gncversion table, which was already created as part 
of the check version call. This causes the table-create script to fail 
and results in a fatal error.

So, the solution could one of:

- Add the command 'Drop table gncversion;' before the create table 
gncversion... command in the table-create.sql file. This command does 
not fail if the table does not exist so it would work for all currently 
supported scenarios too. This is the simplest and it works.
-  Add commands to drop each table before creating in the 
table-create.sql script.  This is probably better, as it would remove 
any existing tables that could/would cause problems.

-  Include all tables to the table-drop.sql script and call that as part 
of the create_db function.  It does not appear that the table-drop.sql 
script is currently being used and it does not include all of the 
gnucash related tables.

Thoughts?

Dwayne

Matthew Vanecek wrote:

>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>On Mon, 3 Mar 2003, Dwayne Miller wrote:
>
>  
>
>>I know the PG backend is being redone, so forgive me if the following is 
>>OBE....
>>
>>- During ./configure, the line that tries to compile a file looking for 
>>the location of the postgres headers returns some warnings.  I had to 
>>add a "-w" flag to the compile step so that the warnings would be 
>>suppressed and the configure step would succeed.  The warnings were 
>>something about "/usr/include" path being redefined.  I'm not sure 
>>what's going on here.
>>    
>>
>
>Dunno about that.  Might want to look through the archives for whatever 
>warning you got.  The archives are downloadable and searchable via grep 
>(ruffin it, I tell ya!).
> 
>  
>
>>- After successful compile, trying to save to a postgres database 
>>failed.  Looks like the logic tries to save to an existing database 
>>first.  In my case, the PG database existed, but none of the gnucash 
>>tables existed.  Well, it tries to determine the version of the database 
>>by querying gncversion.  That table does not exist, so the 
>>pgendVersionTable creates one.  Gnucash then reports that the version is 
>>out of date and asks if I want to upgrade, but that fails because none 
>>of the remaining table have been created yet.
>>    
>>
>
>Saving to an existing database that wasn't created by Gnucash will not 
>work, as you have discovered.  This is actually explained in the README. 
>If you save to a non-existent database, your data will save OK.
>
>  
>
>>It looks like the high level logic wants to try to save to an existing 
>>database by calling pgend_session_begin with create_new_db as false.  If 
>>that fails, then it will recall pgend_session_begin with create_new_db 
>>as true.  But it looks like the first pass through pgend_session_begin 
>>ends up with a single table being created, and the second pass fails 
>>because that is all that exists.
>>
>>I can try to rework the logic in pgend_session_begin() unless that's 
>>already been updated (I'm in v1.8.1 code).
>>
>>    
>>
>
>You're welcome to submit a patch.  I'm using the existing implementation 
>more as a reference for interacting with the Engine.  However, others have 
>tried to first manually create the database, then to save to it.  It may 
>be "user-friendly" to allow that (and will be allowed in new 
>implementation).
>
>Do be careful with the pgend_session_begin() code.  It's pretty, umm, 
>intricate.  I'm not real sure what the reasoning was for creating the 
>version table if it didn't exist--personally, I'd rather assume that the 
>absence of the version table indicates absence of *all* Gnucash tables.
>
>Patches should be directed to the gnucash-devel and gnucash-patches lists.
>
>
>- -- 
>Matthew Vanecek
>perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'
>********************************************************************************
>For 93 million miles, there is nothing between the sun and my shadow except me.
>I'm always getting in the way of something...
>-----BEGIN PGP SIGNATURE-----
>Version: GnuPG v1.0.6 (GNU/Linux)
>Comment: For info see http://www.gnupg.org
>
>iD8DBQE+ZChgi/CNzDSN0RIRAkmTAJ0Vwz2aP29jMixRgrlhzEz6xWPZmwCeKgnM
>2UtbzuQIRI10LpM9d44WKkQ=
>=lFXc
>-----END PGP SIGNATURE-----
>
>  
>