7 ms·
It should be made clear that this functionality is not equivalent to the Postgres JSON support where you can create indexes based on the structure of the JSON.
by jzelinskie 10y ago
It should be made clear that this functionality is not equivalent to the Postgres JSON support where you can create indexes based on the structure of the JSON. This is an extension to the query language that adds support for encoding/decoding within the context of a query:
SELECT DISTINCT user.name
FROM user, json_each(user.phone)
WHERE json_each.value LIKE '704-%';
It's pretty neat considering they rolled it all on their own: https://www.sqlite.org/src/artifact/552a7d730863419e https://www.sqlite.org/src/artifact/552a7d730863419e
PS: If you haven't looked at the SQLite source before, check out their tests.
- brotherjerky 10y agoAnything specific about their tests? I found this doc: http://www.sqlite.org/testing.html http://www.sqlite.org/testing.html
- jncraton 10y agoSQLite is frequently cited for having incredible robustness, and this is certainly related to incredible test coverage. 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.
- chrisweekly 10y agoTests are like guard rails. Nobody's saying they shouldn't be there, but they're safety nets. [Rich Hickey's _amazing_ talk on simple vs easy](https://github.com/matthiasn/talk-transcripts/blob/master/Hickey_Rich/SimpleMadeEasy.md https://github.com/matthiasn/talk-transcripts/blob/master/Hi...)
- sAbakumoff 10y agoThough I am a huge fan of SQLite, I am not sure if the incredible test coverage necessarily leads to success : "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 http://www.jarchitect.com/Blog/?p=2392)
- austinpray 10y agoThat is not an apt analogy. Unit tests improve code quality: code that is easily testable has high cohesion and low coupling. Tests can also serve as valuable documentation.
- brutuscat 10y agoNot quite a lot apparently.... https://www.scribd.com/document/225906827/Software-Defect-Origins-and-Removal-Methods2013 https://www.scribd.com/document/225906827/Software-Defect-Or... https://theholyjava.wordpress.com/2015/01/26/challenging-myself-with-copliens-why-most-unit-testing-is-waste/ https://theholyjava.wordpress.com/2015/01/26/challenging-mys...
- sAbakumoff 10y agoThat is analogy I copied from McConnell, Steve (2009-11-30). Code Complete (Kindle Location 16276). Microsoft Press. Kindle Edition. I tend to rely on what I written on this book
- sigzero 10y agoThat's fine. It's a good book. That doesn't mean the analogy works in every situation. I happen to think it doesn't in this one. Dr. Hipp is a great coder. Telling him to "develop better" from that analogy just falls apart. Tests are there for a reason and Dr. Hipp uses them to great effect on the quality of SQLite.
- sAbakumoff 10y agoYeah I agree. Actually the whole point of the original comment was about SQLite's robustness as the result of multiple development practices they use(including testing of course), but testing coverage can't lead to success itself. That's it)
- Someone 10y agoHigh cohesion and low coupling are good, and easily testable correlates with that, but having lots of unit tests doesn't imply that the code is easily testable, and not having unit tests doesn't imply that code has low cohesion or high coupling. It's even possible code needs many lines of testing partly because it lacks in ease of testing. 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.
- bdarnell 10y agoThe `sqllogictest` suite (https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki) is a great resource that is not SQLite-specific. It's a huge collection of queries and expected responses (in an easily-parseable format) that mostly sticks to the SQL standard so it can be run against any database backend. We're using it in CockroachDB.
- mgalka 10y agoHow does the speed compare to MondoDB?
- seepel 10y agoOn the other hand the same version supports indices on expressions that could be used to create an index on json values. Not exactly the same but could be used for similar use cases.
- hvidgaard 10y agoKinda like SQL Server. Postgres has the superiour implementation for now.
- throwawaygehehe 10y agoI'm pretty sure one can create indices based on JSON expressions (see, for example, http://sqlite.1065341.n5.nabble.com/Partial-indexes-on-JSON-properties-td91662.html http://sqlite.1065341.n5.nabble.com/Partial-indexes-on-JSON-...). I don't know how it compares to Postgres' JSON support.
- mercer 10y agoThat sounds great. Could anyone explain what the differences between the two would be?
- astrostl 10y ago> It should be made clear that this functionality is not equivalent to the Postgres JSON support I found that pretty clear by way of it being SQLite :P