How SQLite is tested
sqlite.org
sqlite.org
Just take a look at https://www.sqlite.org/changes.html — after the recent 3.8.0, there were 2 bug fix releases separated by 3-4 days (!), with bug descriptions sounding relatively, hmm, trivial, should I say?
I use SQLite, and consider it a great software; just want to make a point about my own dislike of over-reliance testing. Hight-level languages and strong (automatically) provable invariants should be our basis for writing reliable software, not C with a ton of tests.
2. Our software also needs to be fast. No one needs a slow database engine. There is no high-level language that beats C on tasks like writing sqlite.
3. Finally, show me that unicorn high-level language with strong, automatically provable invariants and one piece of widely used, reliable software that was written in it.
Sqlite works. Testing works.
If there actually was a credible alternative to C for writing software that is efficient and reliable with requirements that sqlite has, I can assure you that people would use it. The problem is: it doesn't exist.
Not implying that this is true now, but this is the specific goal of Rust, with Servo.
And you'd need tests either way. With certain high-level languages you could reduce the need for valgrind and signed integer overflow tests perhaps, but nearly all other test (and thus error) classes remain. You will want to be safe from regressions, you will want to make sure that you can recover gracefully upon process termination, OOM or power failures. You also want to make sure that your SELECT doesn't yield different result sets than other database engines for the same data set.
All those cases are not going away, just because you use a different language. And proving things correct necessitates that your formal model accurately describes what you want to create. Bugs in said model will still be bugs in the application, even if you proved that the application conforms exactly (which still isn't easy).
Having said that, if you have the choice, it's still unwise to spend huge amounts of time writing+maintaining tests but then balk at the relatively short amount needed to use a strong type system. It won't catch lots of things, but it will catch quite a few. For the limited concepts it can express, it can ensure correctness more rigorously, easily and quickly than any testing can. Might as well use the easy method where you can, right?
But sqlite is old and extremely portable - the only language that springs to mind would be C++. And while that does provide advantages, it's a bit of a stretch to claim those would merit a rewrite.
But no language is ever going to prevent you from coding the wrong intent into your application - your programming language has no idea whether (for example) you are intending to keep or omit a left join in your optimisation routine.
This reminds me of an old Google(?) interview question.
"How do you test a Calculator after dropping it?" http://www.youtube.com/watch?v=ILkT_HV9DVU&t=46m50s
For all the possible things that could go wrong in a fancy graphing calculator, what should you do to ensure it works properly?
For real-world purposes, I see if it turns on and gives me 4 when I do 2+2. If it fails, I buy a new one off Amazon.
But what bugs me about sqlite is
a) suppressing errors giving surprising results (hint: try opening a misspelt database file)
b) "syntax error near 'as'": almost impossible to find errors in longer sql statements (are other engines better, giving line numbers?)
c) no full outer join https://www.sqlite.org/omitted.html which can be worked around by writing about the double code, thus giving chance for twice as much errors
I really love the simplicity of using it, though!
e.g. SQLite is not trying to do everything, and does not encourage you to. I guess you basically wrap your style around it, rather than other way around.