[GNC] XML vs SQL data integrity question

Mon May 11 23:03:54 EDT 2020

Jeff,

When you get the time, you might try this: Save the xml file twice in
each data format (or at least those that work).  If the two saved
files in the same format are identical in size, you have a high
probability that GnuCash consistently wrote the data.  You won't be
100% sure if that was all your data from the .xml file, but that's
more likely if GnuCash saves the data consistently.  You might also
open the xml file and looking through it (saved reports can be handy
for this), and then open the sql file and comparing dates,
descriptions, etc.  See if you locate the newest and oldest
transaction in each; that's a good start.

Realize that the sql files may not be identical in different formats;
mysql, postgresql, and sqlite3 files of the same data might might
differ in size.  But two mysql files saved from the same xml file
should be.

MySQl should work fine.  That's the one that uses the dolphin logo,
correct?  You can find some nice guis and front end tools for MySQL
databases.  Their web site should list them for download.  Those are
way easier than using command line queries on a sql interpreter shell.
If you have 30 years of data, you probably should be using a sql
backend to save your data.  I do use the .xml format, but I have
hourly snapshots of the entire Windows VM on zfs disks, so I've been
able to rollback to the last snapshot when Windows freezes (which is
rare using it as a Virtual Machine Guest).  So I haven't lost any data
yet.

Consider (when time allows) installing Windows on a new or different
machine (and possibly as a virtual machine guest) and then see if it
still freezes and crashes.  I would not overspend (whatever that means
these days; Under 2K?  I still think that's high), but you could ask
here on this list and see what GnuCash users have.  You will probably
find that there are a lot of Dell's, HP's, Lenovo's, etc., and they
make good ones.  If I don't build my own, I very much like System 76
products (https://system76.com/).  But they are expensive, and they
are probably pretty much identical to a high end Clevo or Sager.

Gordon

On Sat, May 9, 2020 at 1:15 AM Jeff <beastmaster126 at hotmail.com> wrote:
>
> On 5/8/2020 2:03 AM, GWB wrote:
>
> Have you tried sqlite? I don't know how it works on Windows but it might just save it as a file without setting up a salute back end. Maybe try that when you have time.
>
> Gordon
>
> On Fri, May 8, 2020, 1:27 AM Jeff <beastmaster126 at hotmail.com> wrote:
>>
>> On 5/7/2020 8:28 PM, GWB wrote:
>> > The idea about the images is a very good one.  Invoices, receipts,
>> > etc., would do fine in digital format.
>> >
>> > No question that databases are capable of much greater depth,
>> > granularity and ability to search and change.  However, in defense of
>> > the humble xml text file, I give you this:
>> >
>> > cat GnuCash-xml-file.gnucash | grep -B 1 -A 1 date-posted | less
>> >
>> > Which shows every date-posted field and date, and one line before and
>> > one line after.
>> >
>> > And:
>> >
>> > cat GnuCash-xml-file.gnucash | grep -B 6 -A 3 2019-10-13 | less
>> >
>> > Which finds all transactions with date 2019-10-13, the guid,
>> > Description, Currency, etc. (B 6 = 6 lines before, A 3 = 3 lines
>> > after).
>> >
>> > Not to mention awk and sed, which can do much more complicated things
>> > with the search and replace terms.  But don't try this without a
>> > current backup.  awk and sed can very  easily ruin a file if just one
>> > argument for regrep replace is off by the slightest term.
>> >
>> > But if you can get a command line with bash, or something close, and
>> > you saved a copy as xml, you probably can't do that kind of damage
>> > with grep (but of course, someone will prove this wrong).  If you want
>> > to save your search results:
>> >
>> > cat GnuCash-xml-file.gnucash | grep -B 6 -A 3 2019-10-13 >
>> > all-transactions-2019-10-13.txt
>> >
>> > Will give you a file with all the transactions from that date with
>> > surrounding fields.  You might need to use ">>" instead of ">" with
>> > some shells.
>> >
>> > Then you can see them by:
>> >
>> > less all-transactions-2019-10-13.txt
>> >
>> > Which then lets you scroll up, down, search within results, etc.
>> >
>> > Grep, less and a few shell commands are much easier to learn than sql
>> > query language in my opinion.  But no question a database has lots of
>> > advantages over a text file.
>> >
>> > Gordon
>> >
>> > On Thu, May 7, 2020 at 7:32 PM Jeff <beastmaster126 at hotmail.com> wrote:
>> >> On 5/7/2020 11:18 AM, Gregory Gincley wrote:
>> >>> Sounds like you have a couple of issues happening there.
>> >>>
>> >>> I have no experience with Windows, but I've used the postgres backend
>> >>> in linux for many years without issue.
>> >>>
>> >>> I also periodically save to the XML format as a backup.
>> >>>
>> >>> -Greg
>> >>>
>> >>> On Thu, 2020-05-07 at 00:07 -0500, Jeff wrote:
>> >>>> This has probably been discussed here before but; I'm going ask
>> >>>> anyway.
>> >>>> Which do most people find more reliable with GNC, SQL or the default
>> >>>> XML?  And are there any features I would lose other than the
>> >>>> rollback
>> >>>> ability with SQL?
>> >>>>
>> >>>> I'm getting tired of having to track down account and report issues
>> >>>> every time Windoze 10 hiccups.  I use the default XML,
>> >>>> uncompressed.
>> >>>> One set of books has been corrupted several times, I'm assuming when
>> >>>> Windoze just simply kills the GNC program out of the blue.  I have
>> >>>> another set of books for a business that so far, knock on wood,  the
>> >>>> only problem is sometimes various buttons have to be selected
>> >>>> multiple
>> >>>> times to work then all of the windows open in GNC blur while
>> >>>> processing
>> >>>> then go back to normal display.
>> >>>>
>> >>>> My computers are all networked and dual boot Windoze and Ubuntu, so
>> >>>> I
>> >>>> would need SQL on both sides if I switch over.  I'm leaning towards
>> >>>> PostgreSQL (pro's, con's?  Suggestions?).
>> >>>>
>> >>> _______________________________________________
>> >>> 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.
>> >>> .
>> >> I like Greg's idea of periodically saving back to xml.  Had not thought
>> >> of that.  I do prefer the uncompressed XML format because it is
>> >> basically just one big text file, that makes it easier for me to do
>> >> global search and replace if I have to.  And I'm going to have to, I
>> >> wasn't thinking and moved some images that I had linked to
>> >> transactions.  I've been toying with the idea of writing a companion
>> >> product for the sole purpose of storing the images, then I don't have to
>> >> worry about file names or locations, just switching windows.
>> >>
>> >> I want to say the data issue popped up in GNC 2.6(?) and happened again
>> >> last week in version 3.8.  With every new release of GNC I do a complete
>> >> uninstall of the old version before I upgrade to the next version of
>> >> GNC. Windoze 10 is current on both machines.  And to my knowledge GNC is
>> >> the only program I've had this problem with.  It could possibly be a
>> >> windoze/virus software issue but I would expect it on both machines.
>> >>
>> >> I think I will give SQL a try and see how I like it.  I've always been
>> >> more comfortable using databases than text files for indexed data
>> >> storage anyway.
>> >>
>> >> --
>> >> --JEffrey Black M.B.A.
>> >>
>> >> _______________________________________________
>> >> 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.
>> > .
>>
>> It's been awhile since I have used awk and sed.  Somewhere along the way
>> I uninstalled the windoze equivalents and have forgotten a lot of the
>> features available.  One tends to get lazy in windoze. Not case
>> sensitive, mediocre editors (notepad++ excluded), etc. The main plus is
>> the abundance of jpeg utilities.
>>
>> Just spent a couple hours I couldn't afford to waste trying to install
>> PostgreSQL and mysql.  Neither of which will accept a file from GNC.
>> After I finish the last of these damned tax returns I can play with them
>> again.  At one time I had mysql installed and it worked, I uninstalled
>> it because I wasn't using it at the time and needed the disk space.
>> Now, nothing but error messages.  I have to have bits twiddled somewhere
>> in my windoze installs that shouldn't be.  Figures, Windoze is the only
>> legal computer virus in the world.
>>
>> --
>> --JEffrey Black M.B.A.
>>
> Gordon:
>
> No I haven't.
>
> After a mind numbing session of tax forms I've never heard of before, I decided to take a break and mess with PostgreSQL and MySQL.  PostgreSQL absolutely  refuses to  do anything.  Finally managed to get GNC to save to MySQL.  Problem is that GNC locks up on the save as mysql so I do not know for sure that it saved everything.  I ended up killing the GNC process when it ceased using CPU cycles, and opened the new file and it acts like an SQL file, all transactions are immediately saved.  And the load time is much faster.
>
> Is there anyway to determine if the conversion saved ALL of the records?  My records go back almost 30 years, and there are some that I have to keep.
>
> --
> --JEffrey Black M.B.A.