Re:SQLite 3.7.16 Released
by _wmd on Tuesday March 19, 2013 @10:06AM
(#5400926)
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.
Re:SQLite 3.7.16 Released
by xradionut on Tuesday March 19, 2013 @10:47AM
(#5401183)
"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?
Re:SQLite 3.7.16 Released
by _wmd on Tuesday March 19, 2013 @10:50AM
(#5401200)
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.
Re:SQLite 3.7.16 Released
by rogerbinns on Tuesday March 19, 2013 @08:41PM
(#5404753)
> ... including the ability to run the SQL engine on top of any key/value store ...
Note that you can do that today with the virtual tables interface in SQLite 3. For example several people have written virtual tables where the underlying data is in a csv file, I wrote one that backends into CouchDB etc. It is a little ugly in that SQLite semantics bleed through the interface (eg BestIndex method) but does work. http://www.sqlite.org/vtab.html
Re:SQLite 3.7.16 Released
by _wmd on Tuesday March 19, 2013 @11:58AM
(#5401656)
Beware MDB isn't quite as general purpose as the other options, specifically feeding it large records can cause it fall back to placing records in pages of their own, which can be wasteful on storage. It's easy to work around (by splitting your records up) but this needs to be considered as a factor of your design.
Re:SQLite 3.7.16 Released
by luser001 on Tuesday March 19, 2013 @12:18PM
(#5401778)
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.
Re:SQLite 3.7.16 Released
by malkia on Tuesday March 19, 2013 @10:28AM
(#5401053)
I had an exam in Ashton Tate's dBase in the last year at my high school (this was a special mathematical high school in Bulgaria, every big city had one such school or more)
Re:SQLite 3.7.16 Released
by protomyth on Tuesday March 19, 2013 @03:15PM
(#5402941)
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.
Re:SQLite 3.7.16 Released
by genwin on Tuesday March 19, 2013 @01:02PM
(#5402102)
Thanks for the blast from the past! FoxPro was leading edge in its time. I'm also a fan of SQLite; it works great for my website. Who needs bigger iron (on a single machine anyway) when SQLite can insert a million+ rows a second?
Re:SQLite 3.7.16 Released
by ericcholis on Tuesday March 19, 2013 @09:51AM
(#5400845)
Actually, rather similar. Visual FoxPro used SQL syntax for large majority of it's database and free table operations. Some commands were slightly different, and allowed you to use VFP specific commands in them. But, transitioning a VFP app to use a SQL backend was relatively simple.
Actually, my shop still runs a few VFP tasks that connect to various MySQL backends as well as JSON API services.
Re:SQLite 3.7.16 Released
by meepmorp on Tuesday March 19, 2013 @02:22PM
(#5402639)
I thought you meant real FoxPro - like 2.x for DOS/Unix.
Now, that was a desktop database development system. Lightning fast, portable between SCO and DOS, easy to work with. Made clipper and dBase look like pokey hunks of poop in comparison. That and netware 3.12, plus some low powered machines with token ring cards for clients - golden days, I tell you.
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.
Isn't this basically the same concept as a clustered index in MS SQL Server?
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.
Note that you can do that today with the virtual tables interface in SQLite 3. For example several people have written virtual tables where the underlying data is in a csv file, I wrote one that backends into CouchDB etc. It is a little ugly in that SQLite semantics bleed through the interface (eg BestIndex method) but does work. http://www.sqlite.org/vtab.html
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.
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/
The only complain (not sure to whom) I have is that WebSQL have not got proper attention because SQLite is the only solution.
http://en.wikipedia.org/wiki/Visual_FoxPro
It had a lot of fans back in the day.
But real guys preferred Clipper due the advanced abilities such as eval.
Actually, my shop still runs a few VFP tasks that connect to various MySQL backends as well as JSON API services.
Side note, there is a very useful Python library for working with VFP xBase (.dbf) tables: https://pypi.python.org/pypi/dbf
Now, that was a desktop database development system. Lightning fast, portable between SCO and DOS, easy to work with. Made clipper and dBase look like pokey hunks of poop in comparison. That and netware 3.12, plus some low powered machines with token ring cards for clients - golden days, I tell you.