
SQLite 3.8.0 Release with next-generation query planner - conductor
https://www.sqlite.org/news.html?year=2013
======
nraynaud
I'm here to declare my love for sqlite, a database that doesn't asks me to
choose a root password (wtf, everybody hate passwords) and an application user
password (double wtf), doesn't open a socket (being it unix on TCP), doesn't
install a daemon somewhere in the system, doesn't need to be root to start,
doesn't asks me to create a new dba user in my system, doesn't require me to
be understand how the root daemon starter hands over the control to the dba
user when something goes wrong at startup, and generally doesn't bother me
with stuff that has really nothing to do with storing and querying data.

I don't run this in production only because I would have a hard time
convincing everybody to do it or by cargo-culting my fears (depending on the
project). But one day I will.

~~~
viraptor
So in short... if anyone gets the chance of an sql injection, they have access
to all tables and all schemas from any app that can access your database.
Privilege separation is a very good idea. Not opening the database file
directly is a very good idea too.

Just to be clear, I'm not saying every application needs it. If you actually
use it as a replacement for local files, that's great. If you use a multi-user
app containing sensitive data - "root password (wtf, everybody hate
passwords)" this is so wrong. Reminds me of "chmod 777 everything" \- wtf,
everybody hates dealing with permissions.

~~~
abtinf
The gp point that sqlite gets rid of all the "stuff that has really nothing to
do with storing and querying data" still stands.

Sqlite eliminates almost all of the management overhead that other databases
force on you, so its trivial to have one database per application. Indeed, I
have never seen anyone bother with a multi-application sqlite db, which could
also cause serious performance problems. So that renders you sql injection
point moot.

As for permissions, its better to let the OS handle that than some half-baked
database permissions system. Root accounts/passwords _are_ a wtf in a
production environment because they destroy auditability. If you really want
permissions, it is trivial to create system user accounts for the application
and assign the correct file permissions, or even isolate particularly
sensitive information in its own file. Combine this with AppArmor to further
lock down individual applications.

All of this is overkill, but so is a full blown RDBMS in many cases. If you
are starting out building a new application, just target sqlite until you
actually need something bigger. Odds are, YAGNI.

~~~
jacques_chester
> _Sqlite eliminates almost all of the management overhead that other
> databases force on you_

 _Multi-user support_ is what forces that overhead on you.

It's just that SQLite consciously aims to be a single-user system.

> _As for permissions, its better to let the OS handle that than some half-
> baked database permissions system_

This is nonsensical. There isn't an isomorphic mapping from the Unix
permissions model to SQL's permissions model, particularly when you start
introducing stuff like row-level permissions.

If you write an application which uses a DB backend, and you require multi-
user capability, you can either delegate some of that to the database, or roll
your own. In which case, half-baked permissions systems are back! Especially
if you can't isolate database connections (because it renders any app-code
permissions system moot if you allow 3rd party code execution, such as
plugins).

All that overhead is there for a reason. Those reasons are irrelevant in the
single-user case. But they are not irrelevant in the multi-user case.

~~~
epo
> All that overhead is there for a reason.

SQLite's classic use case is where SQL-like access to data would be convenient
but a full RDBMS would be tremendously inconvenient, e.g. embedded systems.
People seem to be citing inappropriate environments for SQLite as reasons to
say it is of no value. You get the same sort of silliness from NoSQL zealots.

------
jcampbell1
My favorite thing about SQLite is the business model. My understanding is the
product is free, but they charge for their test suite. If you build hardware
that is going to use SQLite, then you should pay for the test suite, and that
funds the future development. It is a tautologically perfect business model.
Revenue toward a rock solid project pays to make it even more rock solid.

~~~
kamaal
But these days, any where you could possibly deploy SQLite are generally linux
based platforms.

And you don't really worry about underlying hardware on those machines.

~~~
coob
I'm sure every smartphone vendor would probably disagree with you there.

------
mmastrac
SQLite is one of the most important pieces of software you never see. I've
used it in projects from many years back to recently and you know what's
awesome about it? It Just Works.

~~~
leokun
Unless you want to use it with multiple users and connections, in which case
it stops working. It's good for embedding data storage in clients and testing.

[http://howfuckedismydatabase.com/sqlite/](http://howfuckedismydatabase.com/sqlite/)

~~~
eksith
Until 2010, I ran a forum on SQLite with 600 users/day + around 1000 posts/day
on a single OpenBSD machine with 2gb RAM and 7200RPM HDD. We moved to Postgres
when we passed 1600 posts/day.

There was some minor slowdown noticeable around 6AM - 10AM, but besides that,
we never had major issues. The bottleneck was usually the network, not the DB.

Edit: Anyone interested in playing with SQLite should give the SQLite Manager
plugin for FF a try : [https://addons.mozilla.org/en-US/firefox/addon/sqlite-
manage...](https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/)

(I'm not affiliated with the developer, I just like the tool)

~~~
guelo
I thought SQLite locked the entire DB when doing an INSERT. Seems like that
would slow a forum app considerably. Though I guess at 1600 INSERTs per day,
which averages to about 1 per minute assuming even distribution throughout the
day, you won't have that many lock collisions.

~~~
abtinf
The sqlite locking model can eat 1600 inserts before breakfast with only minor
lock contention. The whole file lock doesn't take effect until the moment the
write is ready to go to disk.

[http://www.sqlite.org/lockingv3.html](http://www.sqlite.org/lockingv3.html)

------
tzury
Just to remind us all what "testing" means[1]:

    
    
        As of version 3.8.0, the SQLite library consists of approximately 84.3 
        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 1084 times as much test code and 
        test scripts - 91452.5 KSLOC.
    
    

[1] [http://www.sqlite.org/testing.html](http://www.sqlite.org/testing.html)

------
jacques_chester
The related article on the new planner is great reading too:
[https://www.sqlite.org/queryplanner-
ng.html](https://www.sqlite.org/queryplanner-ng.html)

------
johnsoft
For me at least, partial indices are the killer new feature of this release -
[https://www.sqlite.org/partialindex.html](https://www.sqlite.org/partialindex.html)

------
Sami_Lehtinen
"Query planner are what make SQL database engines so amazingly useful and
powerful. (This is true of all SQL database engines, not just SQLite.)"

No it isn't. I have been using SQL databases which got absolutely horrible
query optimizer totally ruining any performance exceptations. I have blogged
about that, and I was very frustrated and disappointed with results.

Which is faster if id>100 or simply walking database by using series of
selects select ... id=101 ... select ... id=102 ... Turns out that the later
method is a much faster. Engineers also then suggested that I could optimize
the query by using structure select ... id=101 UNION select ... id=102. That's
simply insane. How ever the query was written, query otpimizer / planner
should make sure that all these queries are delivered using similar background
process. Of course that walk is impossbile to optimizer, because it's series
of individual queries. But it's just crazy that select id>100 is slower than
that!

Yes, you might have guessed that the SQL database I'm referring to isn't
SQLite.

And finally, I love SQLite and use it for most of my (work & hobby) projects
as local database.

~~~
ww520
Just curious, what database is that? If the index on id is a B+ tree rather
than a hashtable, it's very unlikely id > 100 would be slower. You are issuing
one query with it vs. N queries with the 2nd approach.

Were there a lot of qualified rows for id > 100? If there're a million rows
qualified for id > 100, which way is faster?

------
chris_wot
I love query planners and optimisers. However, here's where I get a bit
bemused: as it states in the article, a query planner must often go on
incomplete information. Therefore you have to know the underlying assumptions
built into the planner.

This is where explain/execution plans are great. Unfortunately, even these
aren't completely documented. SQL Server is not bad, and Postgres is not only
well documented but you can read the source. Oracle is often a basket case -
there are literally thousands of undocumented parameters that you can set, not
to mention underlying data views that also haven't been documented. In fact,
there have been whole books trying to unravel the Oracle CBO, but even the
most obscure often admit that Oracle fix optimiser bugs which can cause an
impact on query plans.

In some cases, you must implement hints, because there is no way of catering
for all queries under all loads based on all possible data distributions.
Sure, you can add a variety of different types of indexes, histograms and
other statistics to try to influence the execution plan, but at the end of the
day sometimes you simply have to add a hint to force a particular sort of
join, etc.

I guess my main point as I muse on query optimizers/planners is that none of
them are perfect. It occurs to me that one of the best so far is actually
Postgres, but alas - they don't allow for hints.

What's my main point here? It really comes down to the fact that the ideal for
database queries is to allow for a declarative syntax ("give me all rows of
product x that are over $100") without having to worry about the mechanics of
retrieving the data, it's just not entirely possible. You really need to give
developers and database folks the ability to nail down the actual mechanics of
data manipulation.

------
meshko
The write up of the NGQP seems (at least superficially?) weird: they say that
the reason for the rewrite was poor performance on a particular artificial
benchmark (8-way join.. how often do people join 8 tables in practice?). At
the same time, I was surprised to learn that the only kind of join algorithm
SQLite supports is nested loops. I would think they would benefit more from
adding hash and merge joins instead of rewriting the QP from scratch.

~~~
hobs
I regularly do 20+ table joins.

------
orf
People in this thread say SQlite is fine for apps with lots concurrent writes
(people advocate running forums and other multi-user apps using it) but that's
not correct in my experience. I wrote a blog[1] in Python that uses a Sqlite
database and on each post view a view counter was incremented[2] using a
simple UPDATE ... views = views + 1 on the post row. Removing this update
caused page speeds to increase hugely, from ~10 requests a second to ~200 or
so. Maybe I was doing it wrong, but I can't see how.

1\. [https://github.com/orf/simple](https://github.com/orf/simple)

2\.
[https://github.com/orf/simple/commit/2121401030cab4d500330e9...](https://github.com/orf/simple/commit/2121401030cab4d500330e98ea2d77dc9007b02c)

~~~
sk5t
I'm not familiar with the ORM here, but is it really just an increment
("update posts set ctr = ctr + 1 where id = 9") or a select, add one in code,
and then an update? Also bear in mind that one operation in an explicit
transaction like this is quite slow, at least prior to WAL.

------
chubs
Be ready to hear more and more from SQLite as the world moves to mobile
(iOS/android) - it really shines in that environment.

------
ChrisBanner
Ah, the beta presupposition: SQLite version 3.8.0 is actually one of the most
heavily tested SQLite releases ever. Thousands and thousands of beta copies
have be downloaded, and presumably tested...

------
netcraft
I really wish there was something like sqlite to replace MS Access - actually
I think what I really want is something to replace MS Excel that isn't MS
Access. The ability to have a self contained file based database that can be
moved around and shared, but that can also hook up to external data sets has a
lot of use cases in enterprises. But it would require a good query view, the
ability to create forms and reports, and have a sane sql dialect.

~~~
seabrookmx
Well SQLite is already around and stable, so all you'd need then is a really
good Access style front-end for it.

------
galapago
"May you do good and not evil May you find forgiveness for yourself and
forgive others May you share freely, never taking more than you give."

------
zzzeek
can it nest joins in parenthesis yet?

    
    
      SQLite version 3.7.15.2 2013-01-09 11:53:05
      Enter ".help" for instructions
      Enter SQL statements terminated with a ";"
      sqlite> create table a(id integer);
      sqlite> create table b(id integer);
      sqlite> create table c(id integer);
      sqlite> select a.id, b.id, c.id from a 
         ...>     left outer join (b join c on b.id=c.id) on b.id=a.id;
      Error: no such column: b.id
    

SQLite is now the only major database I'm aware of that still can't handle
this syntax (postgresql, mysql, oracle, SQL server, firebird, etc., no
problem. I'm also well aware of how to use a SELECT subquery as a workaround).

Supporting some crude form of stored procedure would be nice too (you can
already add SQL functions, why not procedures?), so that a 3rd party can
provide semi-native ALTER support (don't worry, I don't want to put ALTER into
SQLite itself, I understand it requires rewriting of table data, I'd just like
there to be a standard implementation of that approach somewhere).

------
adrianlmm
I haven't tried SQL Lite, I always used Firebird SQL embedded.

~~~
eksith
It's a pretty nice little DB for a lot of things.

If you just want to play with it a bit, there's a Firefox extension available
: [https://addons.mozilla.org/en-US/firefox/addon/sqlite-
manage...](https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/)

It's not very powerful, but you can play around with different schemas. You
can even export to different formats and edit manually if you like.

------
unono
Is there something about sqlite that postgres can't come in to replace it
completely (a kind of dynamic postgres that starts up and shuts down like
sqlite). With mysql,postgres,and sqlite, the open source db industry gets
diluted. Some kind of merger would be great.

edit: to below posters, I'm fully aware sqlite is embedded, i use it everyday
for android. I'm just asking if there's actually a technical reason why sqlite
and postgres (and mysql) couldn't be merged. A db that can be both embedded or
run as server. This would reduce the duplication of effort in open source db
community.

~~~
dnr
SQLite isn't a replacement for postgresql (or vice versa). SQLite is a
replacement for fopen.

Among its many advantages in that situation: a database is contained in a
single file with minimal overhead, the source is shipped as one .c file and
encouraged to be statically linked, and the code size is relatively small
(compared to something like postgresql).

~~~
mamcx
I hope sqlite someday add functions and/or store procedures. Plus better error
messages (like when a foreign key is violated)

~~~
kevingadd
Why exactly do you need stored procedures in sqlite? You can just write code.
Are you using it in a scenario where you can't write code to interact with
sqlite?

~~~
mamcx
For the same reason are used in others DBs.

Some things are better expressed in the DB native language. Help to abstract
and share implementation of DBs that are used by different languages.

------
edwinyzh
It might be off-topic, but my dream is that SQLite would also become a
document-based database, like MongoDB, but keep SQLite's lightweight and
speed!

~~~
qaruxj
If you really wanted, you could always take a FriendFeed style approach and
create a document store on top of SQLite.
[http://backchannel.org/blog/friendfeed-schemaless-
mysql](http://backchannel.org/blog/friendfeed-schemaless-mysql)

------
pcunite
Quote: "Do not fear the zero!"

I love SQLite ...

------
liminal
How does SQLite compare to H2? When would I want to use one or the other?

~~~
ww520
I guess when you want to use a pure Java solution. H2 is good that it's just a
jar file. You pre-configure and package up everything in one bundle. There
will be no setup. Things just work when you copy the bundle over.

------
Shorel
I wish I could use it in the browser in a future proof way.

Web SQL Database rocks.

------
tomasien
Sqlite and Core Data are synonymous in my mind (hint: it's because I'm a
terrible programmer)

