How to deal with database files for an app?

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

MichaelBluejay
Posts: 222
Joined: Thu Jul 01, 2010 11:50 am

How to deal with database files for an app?

Post by MichaelBluejay » Sun Feb 23, 2020 9:36 am

As some of you know, I've been intermittently working on developing a bookkeeping app. Now I'm stuck on how I should deal with the (SQLite) data files.

The last app I used steered users to keep all years' data in the same data file. That's dangerous, as I realized when I started using the app in 2014. Sure enough, in 2019 I noticed that my 2015 datafile was corrupted, with the errors carrying forth to 2019. I tried a gazillion ways to delete the offending data and re-enter it, but nothing worked, and the developer was no help, so I was forced to waste a few days re-entering five years' worth of data. That's what prompted me to dust off the LiveCode accounting app I'd started on in 2011.

Backups aren't an answer to this problem. The corruption happened six months prior to my noticing it. All my recent backups contained the same corruption, and my six-month old uncorrupted backup was missing so much data it was useless.

So, my idea for my own app is to keep separate years' data in separate DB files. That way corruption in one year's file won't affect other years, and the most data you could lose from corruption would be one year's worth.

Many users will want to see multiple years' worth of data in the same window, but I figured I can still pull in all the various files' data into the same window and show it, so that shouldn't be a problem.

So far so good. The problem is how to deal with the Accounts table. So far I've been talking about DB files that contain two tables, Journal and Transactions. I also need a table for Accounts, the list of accounts and their attributes.

FIRST IDEA: Make the Accounts table a separate DB file, and require the user to keep all DB files (Accounts db file, and the yearly Transactions/Journal db files) in the same folder. This works, but somehow it seems a little messy. That file could get accidentally deleted, or missed being copied.

SECOND IDEA: Have the DB file for each year contain all 3 tables: Accounts, Journal, and Transactions. That saves from having an extra file, but if the user edits Accounts, my app would need to edit the Accounts table in each of the DB files, which is possible but clunky.

THIRD IDEA: There are probably other solutions that I'm missing, which is one reason I'm posting here.

So, how would you all handle this issue?

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: How to deal with database files for an app?

Post by bangkok » Sun Feb 23, 2020 9:57 am

What about a fourth way ?

To "track" and "kill" once and for all the bug or bugs that corrupt your data ?

I mean, separated files, tables, backups... that's good.

But it would be even better to add the bonus : phase out the bug.

Or a tool that could, routinely, check your data ?

The idea would be : do not wait the year after, to discover the bad data.

Last but not least : what's the nature of the "corruption" ?

MichaelBluejay
Posts: 222
Joined: Thu Jul 01, 2010 11:50 am

Re: How to deal with database files for an app?

Post by MichaelBluejay » Sun Feb 23, 2020 10:23 am

That's an interesting idea, but I don't know enough about disk storage to attempt repairs, but even an expert can't fix a problem where data on a disk is missing or contains different values from the originally-saved values. I have to assume that those things can happen to my data files, and segregate them by year to limit any damage. My question is how to deal with the Accounts table, put it in a separate file, or clone it throughout all the years' db files, or something else?

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: How to deal with database files for an app?

Post by bogs » Sun Feb 23, 2020 10:24 am

I'd do what bangkok said, track down what caused the corruption and fix that. Single files or multiple files isn't going to make any difference in the situation your describing, other than you will have to fix it over multiple files.
Image

okk
Posts: 176
Joined: Wed Feb 04, 2015 11:37 am

Re: How to deal with database files for an app?

Post by okk » Sun Feb 23, 2020 12:37 pm

Hi,
what is the cause of the corrupted database file? Is it because of a hard disc failure? Or because of some bug with the app? I pesonally would not create separate DB files for specific tables or years. SQL should handle this kind of (low) complexity with ease.
From my experience one has to be super careful when construction a SQL statement in Livecode. It is very important to check for all kind of hidden characters such as commas, linebreaks, tabs, strange quotation marks etc. One such odd character can break your database table.
As additional backup it could be perhaps useful to export specific tables regularily as plain text, for example as a CSV file to a cloud.
Additionally I would perhaps create a data integrity checker that searches for errors in the database and tries to fix them, or at least alerts you, so you can revert more quickly to an uncorrupted backup.
Good luck!
Oliver

MichaelBluejay
Posts: 222
Joined: Thu Jul 01, 2010 11:50 am

Re: How to deal with database files for an app?

Post by MichaelBluejay » Sun Feb 23, 2020 12:47 pm

I have no idea what caused the data corruption while using someone else's app, and frankly, nor do I care.

We all know that data corruption can be caused by forces beyond our control, such as power outages during saves, and disk failures. There is no way I can prevent a disk failure and I'm not going to try.

My post here had nothing to do with fixing corrupted files, and everything to do with how to organize the DB files in my accounting app.

Yes, I will include data-integrity checks within the app.

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: How to deal with database files for an app?

Post by bogs » Sun Feb 23, 2020 1:06 pm

MichaelBluejay wrote:
Sun Feb 23, 2020 12:47 pm
My post here had nothing to do with fixing corrupted files, and everything to do with how to organize the DB files in my accounting app.
And you got exactly 3 answers that tell you exactly that. Ultimately the choice is up to you whether to follow that advice or not.
MichaelBluejay wrote:
Sun Feb 23, 2020 12:47 pm
We all know that data corruption can be caused by forces beyond our control, such as power outages during saves, and disk failures. There is no way I can prevent a disk failure and I'm not going to try.
All of the above is true, however, creating multiple files on a drive that may have an issue isn't going to resolve any of those. Okk's suggestion of a backup file system locally and in a secondary remote location is the best way I know of to hedge against those kind of problems, but I think multiple separate db files is just added complication that serves no purpose. YMMV.
Image

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9582
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: How to deal with database files for an app?

Post by dunbarx » Sun Feb 23, 2020 5:33 pm

Hi.

Reading through this, I realize that I do not know if the original problem app was written in LC.

Was it?

Craig

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9802
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: How to deal with database files for an app?

Post by FourthWorld » Sun Feb 23, 2020 5:52 pm

If I understand this correctly, any method of splitting up tables among multiple files will only limit the scope of damage if corruption occurs, but cannot assist restoration should corruption occur. And unless a table that became corrupted wasn't truly needed (who uses tables they don't need), the impact on the system will be similar even if the scope of the damage is somewhat limited.

There may be other good reasons to split up tables (indeed, one of the MySQL storage options does exactly that), but stepping back to look at the problem as a whole I'd consider nightly snapshots.

Unless the file is prohibitively large, with storage < $50/TB one could automate snapshotting and have confidence that you can go back and retrieve what you need when you need it.

The bigger question here is why the DB system itself isn't able to identify and report corruption after it happens, leaving discovery to chance in the hope that the user happens to try to access a record no longer available.

I'm not familiar with SQLite's options for integrity checking, but given how many millions rely on it I'm confident such checking exists. Employing that with automated snapshotting should solve two problems: you have regular backups with minimal loss potential, and you have a means of identify when corruption occurs closer to real-time, minimizing loss during restoration.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

MichaelBluejay
Posts: 222
Joined: Thu Jul 01, 2010 11:50 am

Re: How to deal with database files for an app?

Post by MichaelBluejay » Sun Feb 23, 2020 7:17 pm

I'm pretty sure the original app wasn't written in LC.
FourthWorld wrote:Unless the file is prohibitively large, with storage < $50/TB one could automate snapshotting and have confidence that you can go back and retrieve what you need when you need it.
I'm not sure that having literally thousands of backup files (365 x 5 = 1825!) is a great idea, though I supposed I might as well offer that option for users who want it

Maybe a better overall solution is to (keep all data in a single SQLite file and) save a "golden" backup when each year's books are closed.

See my OP for why backups aren't always the answer.

Lagi Pittas
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 365
Joined: Mon Jun 10, 2013 1:32 pm

Re: How to deal with database files for an app?

Post by Lagi Pittas » Sun Feb 23, 2020 7:29 pm

simple answer - and not enough storage place or time is not an excuse.

1, You backup daily or whenver the time to renter data is much more than the time to backup
2. you backup at the end of the week (or only at the end of the week)
3. then a monthly backup

at the end of the year you save all the monthly backups and maybe all the weekly at the most you have about 60 data files.

With the best will in the world a single zipped backup will not be more than 100MB -^G of data- 5 yerasof data on a 32G USB

I DO NOT trust those expensive tapedrives - they let me down twice at customer sites - i had to recreate data from multiple drives.
I don't trust USBs or Dropbox by thenselves.

So I backup on external drives, on my computer, on dropbox - one way or the other - only a comet will be the end of my data.

p.s.

I learnt this lesson when I was called in to resurrect data at multiple different sites on Novell networks, Corvus networks and HP taped drives and people who never backed up and the drive became intermittent.

Prevention is better than cure.

Backup and test your backups trust no one

<OFFSOAPBOX>

Lagi

p.s. as you have proved backups that haven't been tested aren't to be trusted.
Use Richards suggestion of snapshots and only remove the oldest weekly (not monthly) backups when you know the data is fine - and did i say keep backups on multiple places?

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9802
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: How to deal with database files for an app?

Post by FourthWorld » Sun Feb 23, 2020 7:33 pm

MichaelBluejay wrote:
Sun Feb 23, 2020 7:17 pm
See my OP for why backups aren't always the answer.
I did. And yet the ultimately are, as the remedy there is to restore the broken part from a backup. The rest of the exploration is reducing the scope of the backup, but the backup itself remains central to restoration.

Storing diff-based backups would cut down file size. And frankly, even if everything were going swimmingly with this app nightly backups are a good habit for any user, and essential for any business.

At the heart of this is an issue proposed but not acknowledged: that a DB file can be corrupted yet have that corruption undetected for months.

THAT's the unusual part here. Backups are common, and would solve the problem if the corruption were detected earlier.

So while we could explore all sorts of options for restoring parts and pieces of various table files, we can reduce the effort needed to restore any single table if we can identify corruption sooner.

I would focus on finding and using integrity checking tools for SQLite first, then consider other options once that's in place.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

MichaelBluejay
Posts: 222
Joined: Thu Jul 01, 2010 11:50 am

Re: How to deal with database files for an app?

Post by MichaelBluejay » Sun Feb 23, 2020 7:45 pm

Okay, I'm almost talked into keeping all the data in one file, and doing integrity checks, auto backups, along with golden backups, but rather than asking what programmers would do, I realize I should ask what users would prefer. After all, they're the potential customers. So I'm gonna ask around on the bookkeeping forums and see what they say. I'll report back here.

Of course, I could let the user choose whether they want a single datafile or separate files for separate years. Probably that's the best solution, because then everyone's happy.

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9802
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: How to deal with database files for an app?

Post by FourthWorld » Sun Feb 23, 2020 8:44 pm

MichaelBluejay wrote:
Sun Feb 23, 2020 7:45 pm
Okay, I'm almost talked into keeping all the data in one file, and doing integrity checks, auto backups, along with golden backups, but rather than asking what programmers would do, I realize I should ask what users would prefer. After all, they're the potential customers. So I'm gonna ask around on the bookkeeping forums and see what they say. I'll report back here.
I like user-centric solutions. Looking forward to reading what you learn.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: How to deal with database files for an app?

Post by AxWald » Mon Feb 24, 2020 12:10 am

Hi.
MichaelBluejay wrote:
Sun Feb 23, 2020 7:45 pm
Of course, I could let the user choose whether they want a single datafile or separate files for separate years.
Michael:
Hey User, regarding your backups: would you prefer a single datafile or separate files for separate years?
User 1:
Huh? You mean bagels? Got some? Gimmi!
User 2:
Don't you see I'm busy? Bugger off!
User 3:
Don't bother me with techno babble! I bleed $$$ for this **bleep** computer, get your **bleep**ing stuff done, now!
So:
MichaelBluejay wrote:
Sun Feb 23, 2020 7:45 pm
Probably that's the best solution, because then everyone's happy.
;-) Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”