Upgrade to Lion
ambassador at fourthworld.com
Thu May 31 10:47:20 EDT 2012
Kay C Lan wrote:
> As for SQLite being ubiquitous on moblie devices, in my own opinion it's
> due more to it's price, like mySQL and gmail. If people had to pay $5 a
> month for the privilege I think suddenly the numbers would shift. Just
> because a billion people do, doesn't mean it's the best.
True, but sometimes it works the other way as well. Like Torvalds says,
"Given enough eyeballs all bugs are shallow."
Consider the power of WebKit, MySQL, the BSD-originated Darwin kernel
that drives your Mac, and more - all free an open, all performing at
least on par with many proprietary alternatives.
With the growing ubiquity of FOSS, the value of software is not easily
measured by the purchase price of a license. A better measure is the
number of hours that went into making it.
SQLite is public domain so a great many eyeballs have been looking at
that code and optimizing it for some time. It's not perfect, and there
are no doubt many opportunities for further optimization, but there's a
reason it's so commonly used.
> Earlier this year I took 130 million data points from the gps database and
> fed them into my own SQLite and Valentina dbs. Basically I needed to find
> the gradient that represented optimum rate of climb. To do this I needed to
> locate all hills, sift out those that where downhill rather than uphill,
> remove any instance where drafting was a factor and then seasonally, time
> of day, compare to extract trends.
> The sql statements were not that complex, just a LOT of toing and froing
> between LC and the db. I soon abandoned SQLite as it was clear that
> Valentina was getting the answers quicker.
Ruslan's genius is noteworthy, but perhaps the smartest decision he made
with Valentina was to design it using a columnar data store.
Columnar stores are radically different from row-based stores, such as
most SQL-based implementations use. For the relatively low cost of some
additional overhead in updates, columnar stores allow optimized searches
in ways that row-based system can rarely match.
This page provides a good intro to the differences:
Additionally, the structure of an SQLite DB, particularly the indexing,
can radically improve performance. While it's unlikely that it could be
optimized to beat Valentina, there may be opportunities to speed up the
SQLite DB to be at least closer to it.
> Maybe it has more to do with the speed LC talks to each db. I used the LC
> db commands for SQLite whilst I've migrated across to using API calls for
That's another valuable point. The LC externals API is somewhat limited
and carries a bit of overhead. How that plays out with each external
can't be known unless we were in a position to examine the source of each.
> But what about Spotlight where the data is presented in a unknown vast
> assortment? I can do a search on 'Borrower' and 'Latitude', because
> Spotlight can look into the home inventory and gps dbs. How could Apple
> possibly have known that such fields would be added and whether the field
> would hold a constant 2 chars or 0-2K chars or a blob of binary. As far as
> I know there is limit of 62 dbs which SQLite can attach to. How close is
> Spotlight to reaching that limit, I've no clue, but Versions has just added
> one more, and as anything approaches it limit, it slows.
While Apple includes SQLite and recommends devs use it for things like
prefs files, I don't believe they use it for Spotlight. My
understanding is that Spotlight uses a proprietary data store.
> But I'm often wrong. And I see why, once all these dbs are set-up, indexed
> and connected, there should be very neglible processing required to just
> keep updating the data.
So it might seem to those of us who use DB APIs but never have to
implement the driver. As an exercise I've experimented with crafting a
variety of data storage schemes from scratch. It taught me many things
about the challenges of efficiently supporting both reads and writes,
but mostly it taught be to appreciate the excellent work so many have
already done with such things. :)
> I'm assured that Linux is a zippier resource dieted OS. Does it have a
> variety of SQLite dbs, all interconnected, and keeping track of your every
Linux being a diverse ecosystem, not surprisingly there are many. :)
Debian, Ubuntu, Mint and others use Zeitgeist, and Fedora and its family
Among other uses, Zeitgeist plays a role in supporting the new HUD in
Ubuntu, which is sort of like a mix between Spotlight and Quicksilver
but taken to a deeper level of interoperability:
Fourth World Systems
Software Design and Development for the Desktop, Mobile, and the Web
Ambassador at FourthWorld.com http://www.FourthWorld.com
More information about the Use-livecode