Upgrade to Lion

[Richard Gaskin]

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:
<http://en.wikipedia.org/wiki/Column-oriented_DBMS#Benefits>

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
> Valentina.

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
> move?

Linux being a diverse ecosystem, not surprisingly there are many. :)

Debian, Ubuntu, Mint and others use Zeitgeist, and Fedora and its family 
use Tracker:
<http://www.linuxforu.com/2011/10/exploring-software-gnome-and-semantic-desktop/>

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:
<http://www.youtube.com/watch?v=w_WW-DHqR3c>

-- 
  Richard Gaskin
  Fourth World Systems
  Software Design and Development for the Desktop, Mobile, and the Web
  ____________________________________________________________________
  Ambassador at FourthWorld.com                http://www.FourthWorld.com