1. Reliability of small database system
- Posted by Spock Aug 20, 2013
- 6414 views
If a program had to manipulate a small data set (say < 2000 records) are there any real advantages to using EDS (or another "proper" database system) compared to using a plain sequence or a flat CSV file?
I am thinking specifically of reliability and error detection.
regards, Spock
2. Re: Reliability of small database system
- Posted by CoJaBo2 Aug 21, 2013
- 6450 views
If you need any kind of error detection or reliability, you need to go with something like SQLite.
The only advantage EDB has is that it "comes with" Eu; however, it is very basic, and does not have any of the typical ACID features of modern databases. Notably, with EDB, a crash or power outage can easily destroy data.
3. Re: Reliability of small database system
- Posted by DerekParnell (admin) Aug 21, 2013
- 6437 views
If you need any kind of error detection or reliability, you need to go with something like SQLite.
The only advantage EDB has is that it "comes with" Eu; however, it is very basic, and does not have any of the typical ACID features of modern databases. Notably, with EDB, a crash or power outage can easily destroy data.
I wrote an experimental version of EDS that improved the reliability a great deal, but at the cost of a minor slowdown. Not that you'd notice on a small database with modern disk drives. I'll see if I can dig it up. It uses a 'Before Image' file that records raw transactions as well as flushing transactions to the D/B. So that in the event of an unexpected application failure (power outage, crash, etc...) it can perform an automatic roll-forward of transactions to bring the database back up a stable state. The only thing you'll lose is the one transaction that was in the process of being written to the BI file.
4. Re: Reliability of small database system
- Posted by Spock Aug 21, 2013
- 6467 views
If you need any kind of error detection or reliability, you need to go with something like SQLite.
The only advantage EDB has is that it "comes with" Eu; however, it is very basic, and does not have any of the typical ACID features of modern databases. Notably, with EDB, a crash or power outage can easily destroy data.
I wrote an experimental version of EDS that improved the reliability a great deal, but at the cost of a minor slowdown. Not that you'd notice on a small database with modern disk drives. I'll see if I can dig it up. It uses a 'Before Image' file that records raw transactions as well as flushing transactions to the D/B. So that in the event of an unexpected application failure (power outage, crash, etc...) it can perform an automatic roll-forward of transactions to bring the database back up a stable state. The only thing you'll lose is the one transaction that was in the process of being written to the BI file.
Derek,
if you describe your process in a little more detail I might be able to just adopt the concept into my program.
regards, Spock
5. Re: Reliability of small database system
- Posted by Spock Aug 27, 2013
- 6354 views
If you need any kind of error detection or reliability, you need to go with something like SQLite.
The only advantage EDB has is that it "comes with" Eu; however, it is very basic, and does not have any of the typical ACID features of modern databases. Notably, with EDB, a crash or power outage can easily destroy data.
I wrote an experimental version of EDS that improved the reliability a great deal, but at the cost of a minor slowdown. Not that you'd notice on a small database with modern disk drives. I'll see if I can dig it up. It uses a 'Before Image' file that records raw transactions as well as flushing transactions to the D/B. So that in the event of an unexpected application failure (power outage, crash, etc...) it can perform an automatic roll-forward of transactions to bring the database back up a stable state. The only thing you'll lose is the one transaction that was in the process of being written to the BI file.
Derek,
if you describe your process in a little more detail I might be able to just adopt the concept into my program.
regards, Spock
Derek,
For the small dataset I have to manipulate I would much rather prefer being able to load the whole thing in memory (into a Euphoria sequence) and work on it directly rather than via some database interface. Periodically (each 10 seconds or so) the whole file, if it had been changed, would be saved to disk using a unique filename based on time/date that is set once at startup. Duplicate files would accumulate but these can be purged from time to time, even automatically so that we keep, say, the last 20 day's worth.
Transactions are simple, only: ADD a new entry, DEL(ete) an existing entry, SET (change) an existing entry. [Entries correspond to single rows]. The app will GET the data directly from the sequence.
Inspired by your idea I am thinking of having a log file containing ALL transactions from the beginning. The process for any operation would then be:
1 Append the ADD/DEL/SET transaction to the log file. 2 Update the internal sequence
At program close, save the sequence to file. Also save a timestamped hash value to the ini file. At program startup the hash value will be checked to ensure the program closed successfully the previous time. If it didn't the user will be alerted and given the option of advising IT or letting the system recreate the database using the log file.
At any time the user can run a verify action on the log file in order to prove the current data is good.
I'm eager to hear any comments about this approach.
regards, Spock
6. Re: Reliability of small database system
- Posted by DerekParnell (admin) Aug 28, 2013
- 6310 views
Inspired by your idea I am thinking of having a log file containing ALL transactions from the beginning. The process for any operation would then be:
Likewise, I'm inspired by your project ... I'll knock up a little demo/proof-of-concept library tonight and see what you think.
I'm more tending towards the Before-Image approach so that in the event of a failure, only the current transaction in progress would be lost. All completed transactions would be automatically restored.
7. Re: Reliability of small database system
- Posted by DerekParnell (admin) Aug 28, 2013
- 6351 views
Likewise, I'm inspired by your project ... I'll knock up a little demo/proof-of-concept library tonight and see what you think.
Sorry but I ran out of time. I've got all the basics working ... add/del/upd and before-imaging etc ... just have to finish off the automatic roll-back process.
8. Re: Reliability of small database system
- Posted by Spock Aug 28, 2013
- 6205 views
Likewise, I'm inspired by your project ... I'll knock up a little demo/proof-of-concept library tonight and see what you think.
Sorry but I ran out of time. I've got all the basics working ... add/del/upd and before-imaging etc ... just have to finish off the automatic roll-back process.
Derek, I really appreciate your doing this. It will mean having a nice easy-to-use but (quite) robust storage system entirely in Euphoria.
regards, Spock
9. Re: Reliability of small database system
- Posted by jimcbrown (admin) Aug 28, 2013
- 6206 views
Derek, I really appreciate your doing this. It will mean having a nice easy-to-use but (quite) robust storage system entirely in Euphoria.
regards, Spock
Seconded.
10. Re: Reliability of small database system
- Posted by useless_ Aug 29, 2013
- 6130 views
Derek, I really appreciate your doing this. It will mean having a nice easy-to-use but (quite) robust storage system entirely in Euphoria.
regards, Spock
Seconded.
Ditto.
useless
11. Re: Reliability of small database system
- Posted by DerekParnell (admin) Aug 29, 2013
- 6066 views
... just have to finish off the automatic roll-back process.
I'm having some issues with the standard library flush() function. It doesn't appear to be writing buffered output to disk in the Windows platform. More testing required.
12. Re: Reliability of small database system
- Posted by useless_ Aug 29, 2013
- 6042 views
... just have to finish off the automatic roll-back process.
I'm having some issues with the standard library flush() function. It doesn't appear to be writing buffered output to disk in the Windows platform. More testing required.
Sadly, even if flush() does work, and the OS (any OS) tells the drive to write it to platters, it may not happen immediately. I don't know the work around for that except to know where it will be written on the platters, and do a low-level read to see if it is there. With even greater abstraction going on in the terabyte hdds, that may not work now either.
useless
13. Re: Reliability of small database system
- Posted by DerekParnell (admin) Aug 30, 2013
- 6030 views
I'm having some issues with the standard library flush() function. It doesn't appear to be writing buffered output to disk in the Windows platform. More testing required.
It turns out that flush() is working quite okay. The problem I was having when using a file opened for "update". It appears that you have to do an explicit seek() prior to a write. Just relying on the file cursor left by reading bytes is not enough.
14. Re: Reliability of small database system
- Posted by K_D_R Sep 23, 2013
- 5867 views
Hello Derek!,
I have been eagerly awaiting news of your improved reliability version of EDS.
Is the project still in the works?
Ken
15. Re: Reliability of small database system
- Posted by DerekParnell (admin) Sep 23, 2013
- 5841 views
Hello Derek!,
I have been eagerly awaiting news of your improved reliability version of EDS.
Is the project still in the works?
Ken
Yes. I just forgot. I'll finish up the docs and post ASAP.
16. Re: Reliability of small database system
- Posted by K_D_R Sep 24, 2013
- 5820 views
Hello Derek!,
I have been eagerly awaiting news of your improved reliability version of EDS.
Is the project still in the works?
Ken
Yes. I just forgot. I'll finish up the docs and post ASAP.
Wonderful! I appreciate your sharing your db library with us. Can't wait to see it!
Thanks again.
Ken
17. Re: Reliability of small database system
- Posted by DerekParnell (admin) Sep 25, 2013
- 5788 views
I appreciate your sharing your db library with us.
While writing up the docs, I managed to find some more mistakes in the code. Sorry for the further delay.
And by the way, its not really a database system as such, more like an Indexed File System and only really suitable for small files (eg. < 1000 records). But it does has the ability to recover the data after a crash.
18. Re: Reliability of small database system
- Posted by sergelli Sep 26, 2013
- 5755 views
In the past, I tried replacing the system Bbase (which I use) by EDS.
During attempts, I noticed that in both cases, the indexing and searching occurrences require heavy code.
But it becomes much easier if we use text files with pre-formatted fields
This way also makes it easy any repairs, because the data is written in a way that a human can read and write with any text editor.
Any attempt to repair files like Dbase or EDS, using a text editor is almost certain failure.
Thus, for small data systems (up to a 10 MIB) I think more practical to use a text file and manages it with programming.
19. Re: Reliability of small database system
- Posted by ChrisB (moderator) Sep 26, 2013
- 5741 views
In the past, I tried replacing the system Bbase (which I use) by EDS.
During attempts, I noticed that in both cases, the indexing and searching occurrences require heavy code.
But it becomes much easier if we use text files with pre-formatted fields
This way also makes it easy any repairs, because the data is written in a way that a human can read and write with any text editor.
Any attempt to repair files like Dbase or EDS, using a text editor is almost certain failure.
Thus, for small data systems (up to a 10 MIB) I think more practical to use a text file and manages it with programming.
Hi
I disagree. Use sqlite for any size of file, flat file database, ini file, sql database, whatever. It journals, so if there is a crash mid write its recoverable. Speed vs EDS not actually a great deal of difference for small files, but IMHO much easier to use. Everybody's probably sick of me going on about how much I like sqlite though, but I just can't fault it for quick data file storage.
Chris
20. Re: Reliability of small database system
- Posted by sergelli Sep 29, 2013
- 5670 views
Hi
I disagree. Use sqlite for any size of file, flat file database, ini file, sql database, whatever. It journals, so if there is a crash mid write its recoverable. Speed vs EDS not actually a great deal of difference for small files, but IMHO much easier to use. Everybody's probably sick of me going on about how much I like sqlite though, but I just can't fault it for quick data file storage.
Chris
Also agree, but for me, a quick viewing is allowed only if the data are recorded in a way that a human can read.
Thus, I believe that writing data in text files, (few data) will be faster for editing.
If you open a sqlite file with a text editor, you will only see things unintelligible.
It is like this or am I mistaken?
21. Re: Reliability of small database system
- Posted by ChrisB (moderator) Sep 29, 2013
- 5666 views
Hi
Yes, you are absolutely correct, data in an sqlite database is not saved in an easily human readable format.
Was that one of the original requirements?
Chris
22. Re: Reliability of small database system
- Posted by jimcbrown (admin) Sep 29, 2013
- 5658 views
Hi
Yes, you are absolutely correct, data in an sqlite database is not saved in an easily human readable format.
Of course, text files are not exactly saved in an easy-for-humans-to-read format either - how many people do you know that can look at a hard disk platter and read the text off of the magnetic coding, or look at a CD/DVD and figure out what the text says based on the pits?
Of course, computers are hardly unique in this aspect - who can read microfiche without a microfiche reader?
Just as you need the right kind of tool (a text editor or text viewer) to read text files and microfiche (a microfiche reader, or at least some kind of magnifying device), with the right tools (the sqlite command line program), the entire sqlite database becomes human readable.
Still, there may be an advantage to using text files as text files tend to be very redundant and therefore very easy to recover from all kind of corruption, even stuff like corruption caused by a bad hard disk. I don't know enough about sqlite to know if it's equal to text files in this regard (though technically it's possible for a binary format to surpass text files in this area, if the format is designed with that level of redundancy in mind).
Was that one of the original requirements?
Chris
Not for the OP (Spock).
23. Re: Reliability of small database system
- Posted by Spock Sep 29, 2013
- 5621 views
Hi
Yes, you are absolutely correct, data in an sqlite database is not saved in an easily human readable format.
Of course, text files are not exactly saved in an easy-for-humans-to-read format either - how many people do you know that can look at a hard disk platter and read the text off of the magnetic coding, or look at a CD/DVD and figure out what the text says based on the pits?
Of course, computers are hardly unique in this aspect - who can read microfiche without a microfiche reader?
Just as you need the right kind of tool (a text editor or text viewer) to read text files and microfiche (a microfiche reader, or at least some kind of magnifying device), with the right tools (the sqlite command line program), the entire sqlite database becomes human readable.
Still, there may be an advantage to using text files as text files tend to be very redundant and therefore very easy to recover from all kind of corruption, even stuff like corruption caused by a bad hard disk. I don't know enough about sqlite to know if it's equal to text files in this regard (though technically it's possible for a binary format to surpass text files in this area, if the format is designed with that level of redundancy in mind).
Was that one of the original requirements?
Chris
Not for the OP (Spock).
My original post didn't specify human readability as a requirement, true. However, it is something that I want and could be related to reliability and error detection (correction). In other flat-file systems that I wrote any significant data corruption is detectable (a hash value on each line will pinpoint the problem area) and the user has the option to actually open the database file in notepad for review/repair.
regards, Spock
24. Re: Reliability of small database system
- Posted by jimcbrown (admin) Sep 29, 2013
- 5652 views
My original post didn't specify human readability as a requirement, true. However, it is something that I want
No problem. Out of curiosity though, why?
and could be related to reliability and error detection (correction). In other flat-file systems that I wrote any significant data corruption is detectable (a hash value on each line will pinpoint the problem area) and the user has the option to actually open the database file in notepad for review/repair.
Thinking it over, I realized that it's possible to have the best of both worlds with sqlite. You can create the database, then dump it into a text file when you're done. Later, recreate the database from the text file for the next use and dump it into the original text file when finished. So you keep and use the text file (which is a script that contains human readable commands to recreate the database, tables, data, etc) as the location where data is saved instead of using a binary sqlite database file for this.
The text file will take up more space, so this isn't practical if your database is too large. Also, you lose some efficiency on loading/saving data. Still, if having stuff saved in human readable text is that important to you, or you believe that having human readable text is more reliable/less prone to corruption than the binary sqlite format (I honestly have no idea whether or not this is the case), then this might work out.
(To save time on loading, you could keep the binary sqlite file around too and just use it directly, but simply write out a new text file when saving data. Presumably, if it's corrupt, you simply won't be able to open it and can then recover data by recreating it from the text file. Likewise, for extra reliability, you could save off the old text files or archive them somewhere (instead of overwriting them) before dumping the database into a new text file.)
25. Re: Reliability of small database system
- Posted by sergelli Sep 29, 2013
- 5637 views
Thinking it over, I realized that it's possible to have the best of both worlds with sqlite. You can create the database, then dump it into a text file when you're done. Later, recreate the database from the text file for the next use and dump it into the original text file when finished. So you keep and use the text file (which is a script that contains human readable commands to recreate the database, tables, data, etc) as the location where data is saved instead of using a binary sqlite database file for this.
The text file will take up more space, so this isn't practical if your database is too large. Also, you lose some efficiency on loading/saving data. Still, if having stuff saved in human readable text is that important to you, or you believe that having human readable text is more reliable/less prone to corruption than the binary sqlite format (I honestly have no idea whether or not this is the case), then this might work out.
(To save time on loading, you could keep the binary sqlite file around too and just use it directly, but simply write out a new text file when saving data. Presumably, if it's corrupt, you simply won't be able to open it and can then recover data by recreating it from the text file. Likewise, for extra reliability, you could save off the old text files or archive them somewhere (instead of overwriting them) before dumping the database into a new text file.)
This method seems perfect. But we must take care that it is not like using a cannon to kill flies.
In my humble opinion, if you are using a small db, I'd better put your data in rows with text fields and manage these fields by a code euphoria.
You can instantly access the fields using matrices, as this example
constant DATE=1,VALUE=1,NAME=3 -- and so on dateSales = MyDbText[LineNumber][DATE] customerName = MyDbText[LineNumber][NAME] valueSales = MyDbText[LineNumber][VALUE]
You can edit the data even using notepad.
Should do so with caution, it can cause a read error and euphoria will break your program.
I prefer an error euphoria, and I'm afraid of a crash of a database
I can fix an error using code euphoria, but do not know if it's the same with a database error.
26. Re: Reliability of small database system
- Posted by ChrisB (moderator) Sep 30, 2013
- 5603 views
Hi
My eyes widened in horror!
You are sooooooo much more likely to get database corruption if you allow humans (cough splut) to edit it. Use eu to error check all the entries, so that users can't input stupid stuff.
If your major worry is computer crashing, and database corruption, then you either need military grade hardware, or nasa level shielding. If you want pretty secure easy to use data storage, then use sqlite, and as Jim says write it out to a text file.
To put the data security into context, my vet database has been running since 2000 (thats right 2000 AD), has gone through various eu upgrades, and a few sqlite versions, and I have not once lost any data through computer crashes (and there have been a few ranging from power cuts, and lightning strikes, and just odd glitches - hard drive failures have meant a quick restore of the entire system, but again, with no data loss apart from between the last backup and the failure.). I do back up in 3 separate locations (thats just sensible business practice though), and I am quite comfortable that if I have a data problem, I will be able to restore from any of the 3 working backups, and have lost no more than a days records.
There are even more robust database systems, but for the level of simplicity vs level of integrity in my own (evangelic) opinion, you can't beat sqlite.
Chris
27. Re: Reliability of small database system
- Posted by sergelli Sep 30, 2013
- 5625 views
Ok, ChrisB
You are absolutely right.
I just want to say that if it is few data to manage, I think it is better to use a text file.
I do this since 1994, when I started using the bdados "dBase IV plus" and in some cases I preferred to use text files instead of Dbase
28. Re: Reliability of small database system
- Posted by Spock Oct 01, 2013
- 5542 views
Hi
My eyes widened in horror!
You are sooooooo much more likely to get database corruption if you allow humans (cough splut) to edit it. Use eu to error check all the entries, so that users can't input stupid stuff.
If your major worry is computer crashing, and database corruption, then you either need military grade hardware, or nasa level shielding. If you want pretty secure easy to use data storage, then use sqlite, and as Jim says write it out to a text file.
To put the data security into context, my vet database has been running since 2000 (thats right 2000 AD), has gone through various eu upgrades, and a few sqlite versions, and I have not once lost any data through computer crashes (and there have been a few ranging from power cuts, and lightning strikes, and just odd glitches - hard drive failures have meant a quick restore of the entire system, but again, with no data loss apart from between the last backup and the failure.). I do back up in 3 separate locations (thats just sensible business practice though), and I am quite comfortable that if I have a data problem, I will be able to restore from any of the 3 working backups, and have lost no more than a days records.
There are even more robust database systems, but for the level of simplicity vs level of integrity in my own (evangelic) opinion, you can't beat sqlite.
Chris
In an earlier version of the fledgling database, a programming error corrupted the data semantically (not syntactically). The file loaded up just fine despite checking. Being able to directly read the data file helped a lot to quickly track the error. I want human readability because it means I can personally take the machine apart to find the fault and then fix it. As I will be "the user" this is not a problem.
regards, Spock
29. Re: Reliability of small database system
- Posted by ChrisB (moderator) Oct 01, 2013
- 5557 views
Hi
Fair enough. As long as you're not allowing numpties to edit it that's ok! (and lord knows, there are enough of those where I work - the bugs they can find!)
Chris
30. Re: Reliability of small database system
- Posted by K_D_R Oct 21, 2013
- 4693 views
I appreciate your sharing your db library with us.
While writing up the docs, I managed to find some more mistakes in the code. Sorry for the further delay.
And by the way, its not really a database system as such, more like an Indexed File System and only really suitable for small files (eg. < 1000 records). But it does has the ability to recover the data after a crash.
No apologies for the delay is necessary as far as I am concerned. I am very grateful for your many generous code contributions.
Considering your expertise, the mistakes you found must be really "thorny", to stall or abort the project. Are the problems of such magnitude that you feel you should abandon the project?
Ken
31. Re: Reliability of small database system
- Posted by DerekParnell (admin) Oct 26, 2013
- 4484 views
Are the problems of such magnitude that you feel you should abandon the project?
Been busy with real life.
Have a look at this and see what you think.
