

SQLite 3.7.16 Released - pykello
http://www.sqlite.org/releaselog/3_7_16.html

======
_wmd
Be sure to check out <http://sqlite.org/src4/doc/trunk/www/design.wiki> ...
SQLite4 splits the SQL engine from the storage engine and provides public APIs
for both, including the ability to run the SQL engine on top of any key/value
store that provides an ordered-map interface.

In the new design a row's primary key directly contributes to its physical
key, meaning lookups and iterations on the primary key involve only one scan
(rather than an index scan followed by random lookups in SQLite 3.x/most
DBMS). Basically the old "OID" feature on steroids, except now you don't have
to design for it at all. This also means data is inherently clustered by
primary key, providing further performance benefits.

The new storage engine in 4.x is exciting by itself, it's a log structured
merge tree, similar to LevelDB but using the old SQLite 3.x B+tree instead of
SSTables for the disk segments. Additionally it is possible to incrementally
compact older generations inline as part of each write, rather than
mandatorily having some unpredictable background compactor thread like
LevelDB. Similar to 3.x and unlike LevelDB, the storage engine also supports
interprocess locking, meaning it's trivial to write software with concurrent
readers across multiple processes and a single writer.

The possibility of mixing a key/value store and SQL store, including having
single transactions affecting both stores, is really exciting. The SQL engine
is still a work in progress, but it should be awesome when it's ready.

~~~
xradionut
"In the new design a row's primary key directly contributes to its physical
key, meaning lookups and iterations on the primary key involve only one scan
(rather than an index scan followed by random lookups in SQLite 3.x/most
DBMS)."

Isn't this basically the same concept as a clustered index in MS SQL Server?

~~~
_wmd
Yep, although I'm not sure if clustering is also a hand-triggered process in
SQL Server (like it is in e.g. PostgreSQL). In any case SQLite 4.x will do it
automatically.

~~~
Shorel
In SQL Server that's defined at table creation time.

In Oracle it's the same: defined at creation time, but you call it an Indexed
Ordered Table (IOT).

In MySQL all InnoDB tables are clustered (in that sense) because that's the
only storage strategy of InnoDB.

------
luser001
Ha ha. The foreign_key_check command is going to be a lifesaver for me.

I run with foreign key support disabled, because the referencing ("child")
rows might get inserted before the referencing ("parent") rows (I don't
control the order of inserts).

Now I can just run a periodic scrub job with this command to clean out rows
which didn't successfully transition from "violating foreign keys" to
"satisfying foreign keys", instead of writing my own code.

Neat. SQLite is an awesome database library.

------
ericcholis
Coming from the FoxPro days, I'm a huge fan of SQLite.

~~~
JohnBooty
I didn't imagine I'd ever see FoxPro mentioned on Hacker News. That's like
seeing... a Gnome vs. KDE debate on a FoxPro forum.

~~~
Sami_Lehtinen
What about Clipper and dB3?

~~~
bsg75
Started my career on dBase II, then III, then Paradox, then Foxpro (and VFP).
Eventually on to DB2/2 (OS/2), Sybase, MSSQL, now all PostgreSQL.

The FoxPro / xBase market was very nice to me for about 6 years. I am happily
surprised to see this still exists: <http://www.dbase.com/>

~~~
protomyth
At my first job, I had a foxbase database (government built), Turbo C 2.01
(make sure to patch the FP), and a Postscript printer. We needed some reports
and graphs to satisfy other agencies, what fun.

