[GNC] Adding Transactions From Another Program

[Hal Vaughan]

Interesting!

Did you consider having GC use XML in your save file instead of SQL?

If I do pursue this, I’ll be glad to make the code available and let people know what happens.


Hal

> On Apr 28, 2020, at 5:41 PM, Stu Perlman <sgperlman at gmail.com> wrote:
> 
> Hal,
> 
> I'm sort of doing a liter version of what you are describing. I do manipulate some GC data from outside of GC.  I have GC setup with the MySQL backend and while I don't create any new transactions from outside of GC I do manipulate them. I have some SQL routines that I created to clean up data points such as the Memo & Notes attributes that are part of each transaction. I have not run into any problems or data corruption after a few months of doing this.  I did put a circuit breaker into my bolt-on app that at startup the 1st thing it does it check the GC lock record and if it detects that GC may still be running it will not allow my SQL routines to do anything.
> 
> INSERTING new transaction records using SQL on the surface appears like it should be simple enough but I would proceed with extreme caution and a lot of testing as you go.  I did not try creating any new transactions but I did try to create my own little routine  (and then abandoned the effort)  to let me set monthly budget amounts. The reasons for that attempt are not important to this thread but the outcome may be.  
> 
> I gave up after spending time creating the logic that I needed to insert into and/or change records in the SQL table [budget_amounts].  Everything I tested always looked perfect in the SQL backend until I went to work with that data in GC.  It was like I did not change anything behind the scenes once I opened up GC again.  I would go back to the SQL world and would sometimes notice that my changes to [budget_amounts] were either being a) ignored, b) wiped out or c) some other corruption.  Through trial and error, I discovered that the SQL table [slots] (and it's various pseudo-tables) also maintains budget amount data and that said data in [slots] will persist when GC is restarted after my changes from SQL were executed.  It was then that I decided that the amount of effort that I would spend reverse engineering the logic that GC is using with the [slots] table was never going to be worth the reward nor the risk.
> 
> I don't know for a fact that for your particular use case that writing to [slots] will even be required because I never dug into the transactions that deeply.  Maybe [transactions] and [splits] are isolated from the apparently superior related data that GC maintains in [slots].  If you happen to know the languages that the GC engine is written in you might be able to get the answer with a little bit of code review instead of the time consumed doing a bunch of testing, observing, and evaluating.
> 
> Good luck!  I'd love to know how it goes for you if you end up pursuing this idea.
> 
> - Stu
> 
>  
> 
> On Tue, Apr 28, 2020 at 3:15 PM Hal Vaughan <hal at hal.dance> wrote:
> I’ve done some research on this issue and I’m trying to determine if it’s worth doing more or if I’d just spend time on this topic and find it won’t work.
> 
> I have a situation I’ve been told GnuCash can’t handle, but I have a Python library that can handle it.  (It has to do with calculating interest on a credit line with a variable balance.)
> 
> Years ago, I remember checking into GnuCash and reading the XML data files and finding that each transaction seemed to have a UUID with it, but that the other data in each entry would be easy to generate from my own library written in Python.  I also know that now the data files can be in SQL, using several different engines, and that if it’s an XML file, it’s in gzip format.
> 
> I do not need my program to run as a plug-in or anything like that.  (Last I looked, I had to install MacPorts for that, and last time I installed MacPorts, it made a mess of the libraries for my old Perl scripts and some Python ones.)
> 
> But here’s what I’d like to do:
> 1. Enter my transactions involving the credit line normally in GnuCash.
> 2. Exit GnuCash
> 3. Run my Python script that would read the GnuCash data file and find all transactions with any credit lines.
> 4. Do the interest calculations for my credit line
> 5. Add a debit transaction for the interest amount before each new transaction with the credit line
> 6. Edit the comment field for each transaction involving the credit line to include notes on interest and totals
> 7. Save the ledger and exit my library script.
> 8. Run GnuCash again and have the new transactions included and their amounts included in any account totals.
> 
> Is this even possible?  I don’t mind doing the research to find out how to generate a transaction ID and all the rest.  I just want to know if I can use my own library to add transactions to a file (or edit comments in a transaction) and, when I’m done, for GnuCash to be able to read that file, with my new transactions, in and to include the amounts in the added transactions in account totals.
> 
> 
> 
> Hal
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.