[GNC] Upgrade to 3.x Save As (sqlite3) failing with data corruption error

[Steve Alex]

> On Jun 25, 2018, at 3:40 AM, Geert Janssens <geert.gnucash at kobaltwit.be> wrote:
> 
> Op maandag 25 juni 2018 01:38:35 CEST schreef salex:
>> I've been trying to set up a procedure where two people can access our XML
>> file located on a file server and of course ran into problems and know there
>> are other possible problem (file marked open if you loose the network - yes
>> I know its single user)
>> 
>> My tests last week seemed to work, but I've been cautious. There may be
>> potential problems in that I'm on a Mac and the other user will be on
>> Windows 10.  Then the file server is a debian server.
>> 
>> I noticed that the Windows version 3.x and I was using 2.6.4.  Also noticed
>> that the windows version had Postgres option. I download the new version
>> using brew cask wanting to try postgres for my report system,
>> 
>> I've been using a procedure for years that now seems broken.
>> 
>> I update the main book on a Mac (using xml). A few times a month I do a
>> "Save As" and save off an sqlite3 version in another folder inside my
>> GnuCash folder. I immediately open the xml version and quit.
>> 
>> I then run a bash script to backup the folder(rsync) to the debian server.
>> 
>> The sqlite3 file is used by a Rails server to produce reports I've
>> developed.
>> 
>> I figured we'd use a modified procedure in that the updates would use the
>> XML version on the debian server (maybe a script to check dates or see if
>> file is open). I'd also put in a cron task to backup the folder to another
>> offsite server.
>> 
>> Since I was on the server, when I did my "Save As", it was in the same file
>> system. I alway just did the save as over the existing version since its
>> just a read only file ( just confirm overwrite message)
>> 
>> When I did that with 3.x, after confirming overwrite, I'd get an error
>> message saying it could complete because of data corruption.
>> 
> This sounds like https://bugzilla.gnome.org/show_bug.cgi?id=789594
> If you remove the existing sqlite file before running a Save As, do you still 
> get the error message about data corruption ?

I just saw that in the release 3.2 message, then got this reply.

I don’t think so. I think I set a different file name (or different folder) and there was no error. I think thats when I discovered my report system broke - most likely by the date format change mentioned below.
> 
>> Don't know if this is 3.x problem or not.  Switched back to 2.6 and it
>> worked, even working on the server version.
>> 
>> Before I switched back to 2.6, I did the save as on my mac and copied the
>> file the the server, I haven't verified this but my report system broke. I
>> got no errors but a simple checkbook register had a balance but do ledger
>> lines.
> 
> We had to make some changes to the database schema that may affect your direct 
> access to the book.
> 
> A few that come to mind:
> - the field format to store dates has changed (affecting many tables)

That is most likely it.

What did dates change to? Just another version of a timestamp (and not YYYYMMDDHHMM ? I have lots of code (functions) that is basically taking a ruby date and doing something like;
    def self.month_transactions(date)
      month = Vfwcash.yyyymm(date) # just date parser that returns string in format YYYYMM
      trans = Tran.where('transactions.post_date BETWEEN ? and ?',month+"00",month+"32").order(:post_date,:num)
   end

> - some parts of gnucash now store their slots in flat format instead of 
> hierarchically. If you're not using the slots in your custom report, this 
> won’t affect you

Don’t use slots, just Accounts, Splits and Transactions.


> 
> Regards,
> 
> Geert
> 
>