
How SQLite Is Tested - asymmetric
https://www.sqlite.org/testing.html
======
panic
The section on static analysis is somewhat damning:

 _Static analysis has not proven to be especially helpful in finding bugs in
SQLite. Static analysis has found a few bugs in SQLite, but those are the
exceptions. More bugs have been introduced into SQLite while trying to get it
to compile without warnings than have been found by static analysis._

~~~
masklinn
That's only "damning" if like SQLite your project has several orders of
magnitude more test code than tested code, which I'm reasonably certain is not
the case.

~~~
fit2rule
Static analysis is rarely helpful after the fact. Its usefulness is directly
related to its entry into the project timeline. Just like "handle all
warnings, always" is a generally good mantra, having static analysis is
supposed to keep you ahead of the curve. If you don't do it early, and often,
you're not doing it properly.

Also, having these tools, but no coding rules by code contributors, enforced
with real code-reviews and reviewer(s), will also deliver 'not worth it'
results. Its a process, not a chisel.

~~~
mikeash
That doesn't match what I've seen. When Apple added support for the clang
static analyzer to Xcode, a ton of Objective-C programmers started running it
on their code bases, and among those I know, it found a _ton_ of bugs that had
gone dormant for years.

~~~
fit2rule
Sure, of course it'll find bugs after the fact. But the value to the project
in doing static analysis is in not adding bugs in the first place, because you
caught them with static analysis as a standard part of your development
process. Who knows what those unknown bugs cost those developers over the
years ..

~~~
mikeash
Are you implying that there is no value in finding bugs after the fact?

Sure, it's better to catch them up front. But catching them later is still
_extremely_ valuable.

~~~
fit2rule
Catching them before they're deployed anywhere is the point. Deploying quality
software from 1.0 is a different order of value than 'experience improved for
the 1.1 user because: bugs fixed'. Both circumstances have value: I meant only
to state that static analysis should be up-front, to get the absolute most
value out of it, because it will massively improve your software quality to
have it there.

------
chris_wot
In 2010 I stumbled upn a bug that segfaulted Firefox:

[https://bugzilla.mozilla.org/show_bug.cgi?id=581946](https://bugzilla.mozilla.org/show_bug.cgi?id=581946)

The SQLite guy were ridiculously responsive and added all sorts of tests
around the fix.

It's not every day you can say you found and helped troubleshoot a bug that
literally could be affected 100s of millions of users!

~~~
mSparks
Big fan of sqlite. But have to say I'm surprised to see how much testing it
gets. While it's great when it works sqlite databases are definitely
temperamental.

In fact those test suites being so large is possibly part of the problem.
Sounds like they are being overly prescriptive in their testing, which is why
it's so damn fussy about the order commands are executed without causing it to
leak memory left right and center.

~~~
TickleSteve
regarding being temperamental....

You're going to have to justify that a bit more, that would not be the
overwhelming experience around these parts.

Not to say its perfect but its the weight of evidence is in SQLites favour
here.

~~~
mSparks
Meh, like I said, big fan of SQLite. But any use of it in a "serious"
application will undoubtably run into: Error: database disk image is malformed

On a fairly regular basis. Google gives:About 57,400 results

So I'm definitely not the only one.

~~~
TickleSteve
[http://www.sqlite.org/lockingv3.html#how_to_corrupt](http://www.sqlite.org/lockingv3.html#how_to_corrupt)

[http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption](http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption)

(Just so you know)

~~~
mSparks
Yeah. There's that. But there are also numerous memory leaks that have to be
carefully managed. Opening and closing the database regularly is usually
enough. And if you do manage to let it run long enough to use all the system
ram, that will malform the database to.

Transactions are particularly bad. One bug I ran into earlier in the year
would gobble up 12gb of system ram trying to search and insert a few 10s of mb
of text, just because it was leaving prepared statements laying around without
telling me.

~~~
TickleSteve
Sounds like a pretty serious bug that would probably affect millions of
people....

... thanks for reporting it.

([https://www.sqlite.org/malloc.html](https://www.sqlite.org/malloc.html))

"No memory leaks. The application is responsible for destroying any objects it
allocates."

"Robust against allocation failures. If a memory allocation ever fails (that
is to say, if malloc() or realloc() ever return NULL) then SQLite will recover
gracefully."

~~~
mSparks
Fine. But in practice. By time malloc returns null. It's blown up anything on
the system that doesn't recover gracefully. possibly the file system. Which is
possibly how the database becomes corrupt.

And I see plenty of reported examples and work arounds. Even yum has been
suffering from it for years.

[http://forums.fedoraforum.org/showthread.php?t=260195](http://forums.fedoraforum.org/showthread.php?t=260195)

[http://www.pvangsgaard.com/2016/03/16/yum-error-database-
dis...](http://www.pvangsgaard.com/2016/03/16/yum-error-database-disk-image-
is-malformed/)

~~~
TickleSteve
The number of applications using sqlite successfully is very much greater than
1. You need substantially more evidence than that to state that what appears
to be simple user error is actually a design flaw in software that tests
specifically for the examples you state and is also well known for the quality
of that testing.

~~~
mSparks
I use it very successfully. In combination with sqlcipher and an application
layer that ensures everything happens "just so"

All I said is the databases are temperamental. If you do anything "unexpected"
it breaks. Numerous ways and often. Most of the bugs I've seen would be easily
picked up with non deterministic testing. (Leaking result sets and prepared
statements for example) - which is how I found them.

The testing codebase can only be that huge if they are all deterministic
tests.

Trouble with deterministic tests is they miss all the edge cases and bugs
persist for a long long time.

The transaction stuff is particularly bad. Gobbles memory without a care in
the world till it either runs out and performance drops to near zero or fails
and corrupts the database.

So everyone is forced to break transactions they would prefer to be atomic
into multiple smaller ones.

These aren't things that should need a specific test case making in c, and
many of the problems are horrific optimisation problems rather than show
stoppers.

My point is. For all those kloc of testing, the sqlite code is definitely not
what I would call robust.

How long has sqlite been reporting "out of memory" just because a database
failed to open for some reason?

The biggest problem seems to be right here:

OOM testing is accomplished by simulating OOM errors.

I.e. not simulated in a real way. Which is silly when it is so easy to
generate them.

~~~
TickleSteve
(Read the link above properly)

"The application is responsible for destroying any objects it allocates"

...smells like user error to me.

e.g. "the application must use sqlite3_finalize() on every prepared statement
and sqlite3_close() on every database connection "

I suggest that if you want to continue to assert that SQLite is not robust,
you have to show us your code.

~~~
mSparks
So aside from calling finalize how are we supposed to destroy them?

Because the number of cases where finalize doesn't destroy them or calling
finalize on a closed prepared statement (supposed to be a harmless noop)
crashing it completely seems fairly common. Yum is open source. A big chunk of
the sqlite tests are not. With plenty of examples of yum sqlite databases
becoming corrupt and needing to be rebuilt from scratch why would you need my
code. Yum is infinately more important to those millions of users than
anything I write.

So yeah, great when it works, but testing nothing to be proud of is definitely
my experience.

Pay for this: TH3 is free to SQLite Consortium members and is available by
license to others.

No chance.

That default encryption is locked behind walls doesn't inspire cooperation
either.

------
onderkalaci
The title says "how SQLite is tester", well I don't think the article talks
about "how". It only talks about the different types of tests that they apply.
I'm curious about "how" it is done. Do you guys run a single "test" button and
all the tests are executed? Or, all the tests are done independently? On which
platforms do you test?

~~~
SQLite
There is a testing checklist
([https://www.sqlite.org/checklists/3130000/index](https://www.sqlite.org/checklists/3130000/index)
is an example). Each item on the checklist is usually just a single "button
push" (really a shell command). But we have to push that same button on lots
of different platforms.

------
rodionos
> Millions and millions of test cases

I wasn't sure how to interpret it, but then they mention that it's 40100
distinct test cases, parameterized. I would have rephrased it as "40K of
parameterized test cases".

------
dang
Many prior discussions:
[https://hn.algolia.com/?query=How%20SQLite%20Is%20Tested&sor...](https://hn.algolia.com/?query=How%20SQLite%20Is%20Tested&sort=byDate&dateRange=all&type=story&storyText=false&prefix&page=0).

------
jesseryoung
This seems like an extreme amount of tests, I would be very interested in
hearing how they are managed. Without some sort of system around them I would
imagine that many tests would lose their meaning.

------
64bitbrain
Did I miss something, because I didn't see performance testing. Given some
specific test bed, which has X amount of RAM, How much Insertion Per
Second(IPS) I can get?

~~~
Cthulhu_
> In addition to the three major test harnesses, there several other small
> programs that implement specialized tests.

> 4\. The "speedtest1.c" program estimates the performance of SQLite under a
> typical workload.

> 5\. The "mptester.c" program is a stress test for multiple processes
> concurrently reading and writing a single database.

> 6\. The "threadtest3.c" program is a stress test for multiple threads using
> SQLite simultaneously.

~~~
64bitbrain
Thanks! I dont know how I missed that.

------
cyphar
> The TH3 test harness is a set of proprietary tests, [...] TH3 is free to
> SQLite Consortium members and is available by license to others.

I think it's ridiculous that people make proprietary test suites. In my
opinion, test suites shouldn't even be copyrightable let alone made
proprietary. A test suite is used to define what the precise interface of an
API is, it's just a piece of code rather than a standards document.

EDIT: To be clear, the actual test framework might be copyrightable. But the
actual test cases should not be IMO. It's also a shame that a free software
project requires using proprietary software in order to practically test it.

~~~
Tomte
If your API is defined by a test suite you're doing something very, very
wrong.

Additionally, there are three major test suites for SQLite, and two of them
(with a test coverage that still leaves most other open source projects in the
dust) are open source and free.

I can live with paying members getting additional testing possibilities.

