SQLITE: JSON1 Extension
sqlite.org
sqlite.org
SELECT DISTINCT user.name
FROM user, json_each(user.phone)
WHERE json_each.value LIKE '704-%';
PS: If you haven't looked at the SQLite source before, check out their tests.
Many of the stats on that page are impressive, but the one that always gets me is that for 122 thousand lines of production code, the project has 90 million lines of tests.
"Trying to improve software quality by increasing the amount of testing is like trying to lose weight by weighing yourself more often. What you eat before you step onto the scale determines how much you will weigh, and the software-development techniques you use determine how many errors testing will find. If you want to lose weight, don't buy a new scale; change your diet. If you want to improve your software, don't just test more; develop better."(McConnell, Steve (2009-11-30). Code Complete (Kindle Location 16276). Microsoft Press. Kindle Edition)
Another unique aspect of SQLite code base is total sticking to KISS(http://www.jarchitect.com/Blog/?p=2392)
There are many cases where someone will write tests that hit an endpoint directly and then assert on the whole response, which in this case is quite huge. They'll then do so for all branches.
Their library / service / etc. code is technically exercised, sure, but doTheFooThing() isn't directly tested, so it could have a bug that is only exposed from another caller with different parameters that would be caught with direct testing. Extreme coupling happens all the time.
Now I'm slowly converting it to sanity, and my teammates are copying me.
To be fair, it was one of those "Get this out now because we're dying" kind of codebases, not do to lack of skill.
But once code is written, it's hard to undo. Then the bad pattern becomes "keeping the same style"
If doTheFooThing() is called from somewhere else than that somewhere else should also have tests. So I find that an argument from "purity" more than practical consideration about bug probability.
Also, if you only test doTheFooThing but not the API then you could accidentally refactor yourself into breaking the API in a backwards-incompatible way (or not be bug-compatible, which is sometimes required, or at least you should detect it and check logs and warn consumers). So the API tests are needed anyway.
There's a balance of course, if doTheFooThing() is an important internal cross-road, or if it is algorithmically non-trivial, or important for other reasons then it should be tested in seperation. But between only semi-integration tests (hitting endpoints and checking responses), and only lots of small unit tests that break or needs rewriting for the simplest refactorings but doesn't catch subtle API breakage, I'd want to work with the former any day. The units of code are often trivial where mistakes are not made, and the mistakes comes when stringing them together, and then it is more difficult to trust the human capacity to figure out failure scenarios than just run the real handlers.
Having full unit coverage with full path coverage is a great ideal. The reality is that for most companies the overhead of having and maintaining these tests is impossible from a business POV. Getting buy-in to spend more than 50% of your time maintaining (writing, updating, verifying) tests is a very hard sell. For companies with under-staffed/over-worked development teams, it just doesn't happen.
At this point in my career I'm firmly in the camp that functional testing is really what matters in most cases. It is a compromise between the realities of business and the needs of engineering. I can test the end product of the development work as a whole and identify that everything works as expected and has no known strange side-effects. This also serves as a contract with your business users as to functionality of the product. If bugs are discovered you can add specific use-case tests to attempt to trigger the bug and prevent regression. All of this does not preclude limited unit testing as well for critical code paths. I find this to be a much more pragmatic approach.
In the case of SQLite I think it mostly is because of hard work to fulfill the ambition to deliver a robust project and because of the existence of fuzzing, which can automate test generation.
You can test known edge case input along with the general input, and it really does give you more robustness.
Above a certain amount of tests it is maybe not the most cost-effective way to reduce defects, but arguing that testing is not the way to go seems misguided to me.
> the SQLite library consists of approximately 122.9 KSLOC of C code. (KSLOC means thousands of "Source Lines Of Code" or, in other words, lines of code excluding blank lines and comments.) By comparison, the project has 745 times as much test code and test scripts - 91596.1 KSLOC.
Though again, I admire SQLite and use it my projects all the time.
That's not true. There are multiple ways to track the progress of losing weight, starting from simple "look at the mirror" ending with fat percent measure.
[Rich Hickey's _amazing_ talk on simple vs easy](https://github.com/matthiasn/talk-transcripts/blob/master/Hi...)
I found that pretty clear by way of it being SQLite :P
For one, this makes debugging easier, and it also means that should business needs change in the future and some field that we've been receiving becomes important for payment processing, it can be extracted from the json field and promoted to a column in the table, without having to _now_ define a load of columns for every possible field that every payment provider can ever supply.
Stuff you want to make configurable at runtime.
That's the issue he was referencing, I think.
It's a shame I don't see Adobe on the list of sponsors/contributers, I have to say. Lightroom makes a huge amount of money for them.
However, SQLITE is great for ad hoc data manipulation as you say, and there are application niches, particularly on the client side where it can be great. By itself, everything I know about it is that is an excellent piece of software and the tests are practically a case study in rigor (again, from what I've heard).... so how could you not love that?
They do, unfortunately. I inherited a web2py application using SQLite as database. It was maybe OK when the original developer wrote it but it's pretty clear now that we should move to a full fledged database.
There already were "fossil" columns in the database because there is no DROP COLUMN and no RENAME COLUMN. The lack of those basic features greatly hinders development because they must be implemented by creating a new table with the new schema and copying all the data in there. Luckily we can stop production to perform those operations, but the extra developer time means that any of those operations costs an unreasonable amount of money to the customer (compared to ALTER TABLE DROP COLUMN). It's not a surprise that they decided to keep those fossils into the database for now and "we'll see what to do".
Another problem I run into was the typelessness of the storage. Basically SQLite has storage classes and maps SQL types to them (Edit: see the note at the end) I quote https://www.sqlite.org/datatype3.html "The important idea here is that the type is recommended, not required. Any column can still store any type of data" and "If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored." I won't go into details but I had some fun with dates, stored in different formats in different TEXT fields and even in the same column of the same table, because different versions of a controller had different ideas about how to parse the dates received from the browser.
For these and other reasons we should migrating to PostgreSQL (even MySQL will do). That would mean rewriting parts of the application and testing it all (guess if the original developer wrote any test?) That costs too much upfront so we're sticking with SQLite for the time being, until we hit some showstopper.
At least a problem this application won't ever have is scaling. There will always be one server and one process writing to the database. SQLite is OK in this scenario. A different web app for a different customer (also inherited) is using SQLite and maybe it will have to scale horizontally in the next months. That means we'll have to replace SQLite with PostgreSQL but again, it costs and will do it only when necessary.
TLDR, my suggestion is to never start a web application with SQLite. It's not its natural environment and there is little to gain because setting up a traditional database is not that difficult anyway. Use SQLite only as embedded db for desktop or mobile applications. I wish we had it in the browser too. I'm sorry that https://www.w3.org/TR/webdatabase/ has died. IMHO it was much better than https://www.w3.org/TR/IndexedDB/
Edit: I read once again the page I linked and concluded that this is because of the choice of the developer. He used varchar for some date fields and numerical fields for others. The problems were in the varchar fields. No blame to SQLite here. Still, automatic data conversion is not a good idea for a database IMHO.
Seriously impressive database.
Built a few iOS app with it as the data store too. Love the 'zero configuration' install. From memory I've even used DOS batch files to manipulate data on SQLite...
I also use it frequently to do some data massaging before loading into a larger database since it has a standard Python module and queries are quick and easy.
Have you heard of storehaus? https://github.com/twitter/storehaus
In terms of the fundamental abstraction offered, it seems comparable to iterlib to me, but I'd love to hear your opinion.
(See also: https://upscaledb.com/)
I understand that JSONB in Postgres is useful primarily for sorts and indexes. Does SQLite work around this somehow, or is that just not included in their experiments?
I wasn't able to get a working build on Windows.
I can't get the compiled json1.so to load on Ubuntu 14.04 lts with stock SQLite.
For what is worth, the sqlite3 version in Ubuntu 16.04 has that json1-extension loaded by default:
sqlite> CREATE TABLE user (name TEXT, phone TEXT);
sqlite> INSERT INTO user VALUES("tester", '{"phone":"704-56"}');
sqlite> SELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%';
tester
Just like Mongo. I was suprised to learn that Mongo can actually store an object with two identical keys. Most drivers will prevent you from doing so, and will fail to display the record fully if it does happen, but it is possible.