1. Reliability of small database system

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

new topic     » topic index » view message » categorize

2. Re: Reliability of small database system

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.

new topic     » goto parent     » topic index » view message » categorize

3. Re: Reliability of small database system

CoJaBo2 said...

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.

new topic     » goto parent     » topic index » view message » categorize

4. Re: Reliability of small database system

DerekParnell said...
CoJaBo2 said...

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

new topic     » goto parent     » topic index » view message » categorize

5. Re: Reliability of small database system

Spock said...
DerekParnell said...
CoJaBo2 said...

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

new topic     » goto parent     » topic index » view message » categorize

6. Re: Reliability of small database system

Spock said...

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.

new topic     » goto parent     » topic index » view message » categorize

7. Re: Reliability of small database system

DerekParnell said...

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.

new topic     » goto parent     » topic index » view message » categorize

8. Re: Reliability of small database system

DerekParnell said...
DerekParnell said...

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

new topic     » goto parent     » topic index » view message » categorize

9. Re: Reliability of small database system

Spock said...

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.

new topic     » goto parent     » topic index » view message » categorize

10. Re: Reliability of small database system

jimcbrown said...
Spock said...

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

new topic     » goto parent     » topic index » view message » categorize

11. Re: Reliability of small database system

DerekParnell said...

... 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.

new topic     » goto parent     » topic index » view message » categorize

12. Re: Reliability of small database system

DerekParnell said...
DerekParnell said...

... 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

new topic     » goto parent     » topic index » view message » categorize

13. Re: Reliability of small database system

DerekParnell said...

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.

new topic     » goto parent     » topic index » view message » categorize

14. Re: Reliability of small database system

Hello Derek!,

I have been eagerly awaiting news of your improved reliability version of EDS.

Is the project still in the works?

Ken

new topic     » goto parent     » topic index » view message » categorize

15. Re: Reliability of small database system

K_D_R said...

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.

new topic     » goto parent     » topic index » view message » categorize

16. Re: Reliability of small database system

DerekParnell said...
K_D_R said...

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

new topic     » goto parent     » topic index » view message » categorize

17. Re: Reliability of small database system

K_D_R said...

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.

new topic     » goto parent     » topic index » view message » categorize

18. Re: Reliability of small database system

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.

new topic     » goto parent     » topic index » view message » categorize

19. Re: Reliability of small database system

sergelli said...

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

new topic     » goto parent     » topic index » view message » categorize

20. Re: Reliability of small database system

ChrisB said...

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?

new topic     » goto parent     » topic index » view message » categorize

21. Re: Reliability of small database system

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

new topic     » goto parent     » topic index » view message » categorize

22. Re: Reliability of small database system

ChrisB said...

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).

ChrisB said...

Was that one of the original requirements?

Chris

Not for the OP (Spock).

new topic     » goto parent     » topic index » view message » categorize

23. Re: Reliability of small database system

jimcbrown said...
ChrisB said...

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).

ChrisB said...

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

new topic     » goto parent     » topic index » view message » categorize

24. Re: Reliability of small database system

Spock said...

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?

Spock said...

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.)

new topic     » goto parent     » topic index » view message » categorize

25. Re: Reliability of small database system

jimcbrown said...

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.

new topic     » goto parent     » topic index » view message » categorize

26. Re: Reliability of small database system

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

new topic     » goto parent     » topic index » view message » categorize

27. Re: Reliability of small database system

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

new topic     » goto parent     » topic index » view message » categorize

28. Re: Reliability of small database system

ChrisB said...

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

new topic     » goto parent     » topic index » view message » categorize

29. Re: Reliability of small database system

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

new topic     » goto parent     » topic index » view message » categorize

30. Re: Reliability of small database system

DerekParnell said...
K_D_R said...

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

new topic     » goto parent     » topic index » view message » categorize

31. Re: Reliability of small database system

K_D_R said...

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.

new topic     » goto parent     » topic index » view message » categorize

Search



Quick Links

User menu

Not signed in.

Misc Menu