GnuCash and Postgres

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

GnuCash and Postgres

Carlos Correia
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

We are evaluating the possibility of using GnuCash for accountancy.

So far so good, but we also need to insert records in GnuCash from our
ERP system which uses PostgreSQL, so we were thinking of also using
PostgreSQL as a backend for GnuCash.

So the question is: is there anything like a Data Dictionary or any
document where the database layout is described?

Thanks,

Carlos Correia
- --
MEMÓRIA PERSISTENTE, Lda.
Tel.: 219 291 591 - GSM:  967 511 762
e-mail: [hidden email] - URL: http://www.m16e.com
AIM: m16e - ICQ: 257488263 - Jabber: [hidden email]
Skype.com username (VoIP): m16e.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDCz1o90uzwjA1SJURAoRHAJ44cGu4JDRTyXxLfQmPVl2sHd1wsQCdFb8R
L0A2jDJutg4ECojUc9KPXRA=
=FPN2
-----END PGP SIGNATURE-----
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
Reply | Threaded
Open this post in threaded view
|

Re: GnuCash and Postgres

Derek Atkins
Unfortunately no..   Even worse, the gnucash business features are not
supported
by the postgres backend...

The SQL Backend is in the middle of a rewrite, but there's no estimate on when
it will be done.  The developers working on it have been side-tracked for a
long time (I'm hoping they are still making progress, but we've not heard from
them in a while).

Personally, I wouldn't depend on the (current) PG Backend for real data.

-derek

Quoting Carlos Correia <[hidden email]>:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi,
>
> We are evaluating the possibility of using GnuCash for accountancy.
>
> So far so good, but we also need to insert records in GnuCash from our
> ERP system which uses PostgreSQL, so we were thinking of also using
> PostgreSQL as a backend for GnuCash.
>
> So the question is: is there anything like a Data Dictionary or any
> document where the database layout is described?
>
> Thanks,
>
> Carlos Correia
> - --
> MEMÓRIA PERSISTENTE, Lda.
> Tel.: 219 291 591 - GSM:  967 511 762
> e-mail: [hidden email] - URL: http://www.m16e.com
> AIM: m16e - ICQ: 257488263 - Jabber: [hidden email]
> Skype.com username (VoIP): m16e.com
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>
> iD8DBQFDCz1o90uzwjA1SJURAoRHAJ44cGu4JDRTyXxLfQmPVl2sHd1wsQCdFb8R
> L0A2jDJutg4ECojUc9KPXRA=
> =FPN2
> -----END PGP SIGNATURE-----
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>



--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available


_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
Reply | Threaded
Open this post in threaded view
|

Re: GnuCash and Postgres

Gabriel M. Beddingfield
Derek Atkins wrote:

> it will be done.  The developers working on it have been side-tracked for
> a long time (I'm hoping they are still making progress, but we've not
> heard from them in a while).

Orcs?

-Gabriel


_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
Reply | Threaded
Open this post in threaded view
|

Re: GnuCash and Postgres

Derek Atkins
Quoting "Gabriel M. Beddingfield" <[hidden email]>:

> Derek Atkins wrote:
>
>> it will be done.  The developers working on it have been side-tracked for
>> a long time (I'm hoping they are still making progress, but we've not
>> heard from them in a while).
>
> Orcs?

More likely Grues, but you never know.  ;)

> -Gabriel

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
Reply | Threaded
Open this post in threaded view
|

RE: GnuCash and Postgres

ted creedon
In reply to this post by Carlos Correia
I use Postgres here but my use is pretty simplistic. Relational data
integrity is not enforced, the double entry system alerts one to errors.

There is no data dictionary but the tables can be reverse engineered,
they're not that complicated.

tedc

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Carlos Correia
Sent: Tuesday, August 23, 2005 8:15 AM
To: [hidden email]
Subject: GnuCash and Postgres

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

We are evaluating the possibility of using GnuCash for accountancy.

So far so good, but we also need to insert records in GnuCash from our ERP
system which uses PostgreSQL, so we were thinking of also using PostgreSQL
as a backend for GnuCash.

So the question is: is there anything like a Data Dictionary or any document
where the database layout is described?

Thanks,

Carlos Correia
- --
MEMÓRIA PERSISTENTE, Lda.
Tel.: 219 291 591 - GSM:  967 511 762
e-mail: [hidden email] - URL: http://www.m16e.com
AIM: m16e - ICQ: 257488263 - Jabber: [hidden email] Skype.com username
(VoIP): m16e.com -----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDCz1o90uzwjA1SJURAoRHAJ44cGu4JDRTyXxLfQmPVl2sHd1wsQCdFb8R
L0A2jDJutg4ECojUc9KPXRA=
=FPN2
-----END PGP SIGNATURE-----
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user



_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
Reply | Threaded
Open this post in threaded view
|

Re: GnuCash and Postgres

Carlos Correia
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

ted creedon wrote:
| I use Postgres here but my use is pretty simplistic. Relational data
| integrity is not enforced, the double entry system alerts one to errors.
|

Since data integrity is not enforced, it will be harder for an
application to write directly in GnuCash DB, once you would have to
assure that data is being properly written :-(

| There is no data dictionary but the tables can be reverse engineered,
| they're not that complicated.
|

You're right, though it's not that simple. Perhaps I'll join the SQL
backend dev team ;-)

Thanks

Carlos
- --
MEMÓRIA PERSISTENTE, Lda.
Tel.: 219 291 591 - GSM:  967 511 762
e-mail: [hidden email] - URL: http://www.m16e.com
AIM: m16e - ICQ: 257488263 - Jabber: [hidden email]
Skype.com username (VoIP): m16e.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDDKWq90uzwjA1SJURAk5DAJ9aCjNEG3xaJb3VMruUZmiZvhJ2KgCfbAEg
D9eyUXKvKiKFjKuCMn5f5HA=
=td5k
-----END PGP SIGNATURE-----
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
Reply | Threaded
Open this post in threaded view
|

Re: GnuCash and Postgres

Derek Atkins
Hi,

Quoting Carlos Correia <[hidden email]>:

> Since data integrity is not enforced, it will be harder for an
> application to write directly in GnuCash DB, once you would have to
> assure that data is being properly written :-(

This is true both in SQL and XML.  Gnucash does not support another
application
writing into its datastore.  If you want to import data into the Gnucash
datastore you should use the Gnucash APIs or (eventually) QSF.

Even with the SQL re-write, data integrity will not be enforces at the SQL
level.  It's can't be enforced because SQL has no concept of the financial
relationships between objects.  How would you define "all transactions must be
balanced" in SQL?

> | There is no data dictionary but the tables can be reverse engineered,
> | they're not that complicated.
> |
>
> You're right, though it's not that simple. Perhaps I'll join the SQL
> backend dev team ;-)

You're welcome to...  I'm hoping that Matthew will speak up, but he might not
read this list.  I think we should move this discussion over to the
-devel list
if you intend to proceed.

> Thanks
>
> Carlos

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
Reply | Threaded
Open this post in threaded view
|

RE: GnuCash and Postgres

ted creedon
 
How would you define "all transactions must be balanced" in SQL?

> You don't. I would suggest using Visio Enterprise Architect for the Data
Modeling though.

tedc

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
Reply | Threaded
Open this post in threaded view
|

RE: GnuCash and Postgres

Derek Atkins
Ted,

First, why are your email replies always so weird?  You see how in my
reply your
text is indented/quoted, and mine is not?  Why are your replies
"different"? You seem to quote your own replies and not quote the
people to whom you are
replying, which makes it VERY hard to follow what you're saying. It's
even more
difficult to follow your text when you have a reply to a reply to a reply and
everyone else is using the standard quoting method and you use your bastard
quoting method.

Could you please try to reply in the Internet Standard Reply form?  I
know that
Derrick Brashear complained to you on the OpenAFS list.  I'm now
complaining to
you on this list.  Please change you behavior if you want people to listen to
you and understand what you're trying to say.

I'm absolutely certain that Outlook can use the standard quoting methods that
everyone else uses.

Second,

Quoting ted creedon <[hidden email]>:

>
> How would you define "all transactions must be balanced" in SQL?
>
>> You don't. I would suggest using Visio Enterprise Architect for the Data
> Modeling though.

See, if you can't enforce this kind of work in SQL then by definition any
process that is inputting data into the database must know all the financial
relationships and enforce them itself.  In other words, every application must
have all of Gnucash's knowledge of the financial relationships in order to
properly insert data.  Ergo, only Gnucash should be inserting data into the
database.

Where can I get Visio Enterprise Architect for Linux?  Honestly, I don't think
the gnucash data model is sufficiently challenging that we need something like
that.  I think a simple Dia diagram would suffice to show the table layouts.

> tedc

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
Reply | Threaded
Open this post in threaded view
|

RE: GnuCash and Postgres

Josh Sled
In reply to this post by ted creedon
On Wed, 2005-08-24 at 14:50 -0700, ted creedon wrote:
>  How would you define "all transactions must be balanced" in SQL?
>
> > You don't. I would suggest using Visio Enterprise Architect for the Data
> Modeling though.

Sure you can; the following model does not admit unbalanced
transactions:

create table accounts
  ( account_id int identity not null,
    name #...,
  )

create table amounts
  ( amount_id int identity not null,
    value float not null,
    credit_account_id int not null foreign key (accounts.account_id),
    debit_account_id int not null foreign key (accounts.account_id)
  )

create table transactions
  ( txn_id int identity not null,
    description varchar(255),
    # dates, &c.
  )

create table transaction_values
  ( transaction_id int not null foreign key (transactions.txn_id)
    value_idx int not null, -- 0,1,2,3,...
    amount_id int not null foreign key (amounts.amount_id)
    primary key (transaction_id,split_idx)
  )

But I agree with Derek -- it's besides the point.  The whole concept of
"well I can just insert randomly into some other app's database!" breaks
down rather quickly.

And re: the quoting.

...jsled

--
http://asynchronous.org/ - `a=jsled; b=asynchronous.org; echo ${a}@${b}`
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user