Minor PostgreSQL issues

[David T.]

Dean:

Have a look at: http://wiki.gnucash.org/wiki/FAQ#SQL_Database

I'll point you especially to:

"Q:Can I open my GnuCash SQL database with another program, perhaps Microsoft Access?
A: A qualified yes... Qualified, though, because the relation information isn't encoded in the database: there aren't any foreign keys defined at present. All of the 
relations are established in C or Scheme code in the GnuCash libraries. 
What's more, there's a lot of data stored in a single "key/value" table 
which has relations to almost every other table in the database. Because of these limitations, we insist that you must not write to the GnuCash database except through the GnuCash libraries."

David



________________________________
 From: Dean Gibson <gnucash at ultimeth.com>
To: Derek Atkins <warlord at MIT.EDU> 
Cc: gnucash-user at gnucash.org 
Sent: Monday, August 27, 2012 8:59 AM
Subject: Re: Minor PostgreSQL issues
 

On 2012-08-27 06:14, Derek Atkins wrote:
> Hi,
> 
> Dean Gibson <gnucash at ultimeth.com> writes:
> ...
>> 2. If there are any other tables in OTHER SCHEMAS, a GnuCash save
>>     deletes them (ugh).
> I have no idea what you mean by "Other Schema" here.  You cannot share
> the gnucash database with other apps, and I don't know why you would try
> to put multiple applications into the same database.  (Again, I'm using
> MySQL terminology here).

There are LOTS of reasons to share the data.  That's the whole purpose of using a database -- to share the data and make it "extensible".  Eg, doing custom reports with complex filters, or other reporting and alerting functions.  In order to KEEP SEPARATE the DB data in one application from another, and still allow that sharing, SCHEMAs (been in the SQL standard for almost a decade) are used.  As a test before saving my GnuCash data to PostgreSQL, I created an empty DB with both a public SCHEMA and a separate named SCHEMA, and created a token TABLE within it the latter.  The initial save wiped the separate SCHEMA and everything within it.

Put bluntly, MySQL is a toy database, and has been for a decade.  It doesn't have SCHEMAs (as a distinct capability from DATABASE), and it didn't have VIEWs (a requirement for any serious DB use) until fairly recently.  MySQL's main claim to fame is that it's fast and simple.


> 
>> 3. The name of the PostgreSQL database must be all lower case -- I
>>     couldn't find a way to specify/use a mixed case name.
> This is correct, and is done on purpose, because there are cases where
> databases automatically downcase the name and later mixed-case names
> cause problems.  As a result of bugs with allowing mixed case GnuCash
> will always downcase the database name.  Sorry.

Most databases downcase all names unless they are in double quotes. I do understand that the work required to preserve case in a consistent manner, may not be worth the effort.  However, (for example) phpBB (a message board system that stores messages in a DB) bit the bullet on this a number of years ago, and supports preserving DB names that are in double quotes (phpBB also supports SCHEMAs).


> 
>> 4. It's slower to open than the XML-format files, and I believe I
>>     recall that the close is equally slow.  From this I got the
>>     impression that GnuCash did not make incremental changes to the DB,
>>     but did a complete wipe/save, but I may be wrong on this.
> This is where you are wrong.  GnuCash does indeed make incremental
> changes to the DB.

Good!  I'm glad to both be wrong (to clear up my misunderstanding), and that it does make immediate posts.

The only "deal-breaker" in my list of issues is the lack of SCHEMA support.  That's only a "deal-breaker" for using PostgreSQL as a backend store, not a "deal-breaker" for using GnuCash.  I'm perfectly content to use the compressed XML file format for the foreseeable future.

I see you are associated w/ MIT.  I applied to both MIT and CalTech (and Stanford), and was accepted at all three.  I went to CalTech, mainly because I happened to live in the same town (Pasadena), despite the fact that MIT offered me a partial scholarship.  That's not a dig at MIT;  I seriously considered MIT as well.

_______________________________________________
gnucash-user mailing list
gnucash-user at gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.