4 ms·

SQLite As The MP3 Of Data

69 points by rams 17y ago

henning 17y ago
Can someone refresh my memory of how SQLite handles concurrency? Doesn't it block on writes or something? For a long time I've thought a spiritual successor to Access, Filemaker, etc. based on SQLite would be a great idea.
- fendale 17y ago
  I think it blocks on write for 'the smallest possible time' (its does as much of the data change preparation as possible before locking the database). If you are just using it as a single user for generating excel style reports then this doesn't really matter. I have been thinking for a while that if I had to export a significant amount of data from an app for a tech savvy audience, I would probably send it out in an Sqlite DB instead of XML.
- senko 17y ago
  By default it does deferred transactions (exclusive lock is only acquired when you need to commit), but if you use insert/update/delete outside a transaction, each statement is it's own transaction. Locking is database-wide, although IIRC there's some branch that can do table-level locking.
  - jmtulloss 17y ago
    As far as I know, SQLite's version of "table-level locking" is to put each table in a different database file and ATTACH them all to one database connection.
- eli 17y ago
  Yes, but it's a little more involved: http://www.sqlite.org/lockingv3.html http://www.sqlite.org/lockingv3.html
rarestnews 17y ago
I don't really work with SQLite too much (I mainly work with mySQL and in-memory-structures, like hashes), but here are a few observations that kept me from using it: 1. It uses a lot of IO unnecessarily. (Actually, necessarily, due to their motto, but 99% odf developers aren't aware that this is not necessary) SQLite-based applications often make my hard-drive sqeak like it's on a last spin - SQLite does A LOT of hdd activity (Chrome, Firefox, Adwords editor are notorious for that). It's SQLites motto - 100% consistency first! journal everything!.. but... that's my HDD and I don't want it to be dead because SQLite thinks it's great idea to write every byte it receives to insure me against 0.01% chance of data loss. 2. Head-to-head runs of mySQL vs SQLite in real-world applications - well, SQLite loses badly.. and I mean badly. I use a lot of hash-style databases (i.e. id-value storage, 2 columns only) in some of my tests SQLite lost with 10 times worse performance against mySQL (again, if they didn't try to record everything, like mySQL does with MyISAM - they would've got the chance). That being said - in-memory SQLite tables are very performant! Very! But... almost nobody uses that. As a result - Chrome/Firefox downloads phising update and my HDD lights like a christmas tree (download Filemon from SysInternals and watch how much Chrome or other SQLite-based engine uses your HDD for simple data that MUST be in-memory... or at least used by in-memory cache, like in MySQL... so your users' HDDs will eventually die because of this). I know it's none of developers troubles to think about the fact that their software unnecessarily uses much IO and leads to early hard drive replacement, but still, that's a big reason why I avoid using SQLite. I mean, there are a lot of in-memory structures that can (and should) be used, but once the developer sees how convinient SQLite is, he jumps right on it. I mean.. 99% of times SQLite should be used like this - load data from disk to :memory: table (i.e. replicate it to memory), work with :memory: and eventually dump it to drive (like once in a couple of minutes) and dump on "Save" command. But instead every popular program uses disk-based tables. Why? Because it's a lazy way. Who wants to add in-memory pre-caching when you can just open a table from disk in less commands? It's sad to see the current state of programming. Coming from an area where I had to write programs that would fit into 64KILObytes of memory and 3.5MHZ (that's 1000 times slower than modern single-core processor) - (ZX Spectrum)... and we've had whole huge games in that, working in real-time, with color graphics and almost unlimited galaxies (Elite)... it's just sad to see how the available resources are used these days. People had to use a lot of tricks to make things work with acceptable speeds... And now? I mean it's 3GHZ and gigabytes of data, yet I have to wait 10 seconds for Word (or Firefox) to start up? My main points are: 1) SQLite is convinient; 2) Disk-based SQLite (which is used by 99% or more pieces of software) is nowhere near the possible performance of in-memory; 3) disk-based SQLite is putting a heck of a load on HDD (fire up FileMon during your Chrome session and be amazed); 4) SQLite is nowhere near one-fits-almost-all solution, like mp3 is; 5) even so it will gain more popularity because it's convenient for developers. I have MySQL table with 72billion denormalized items (72million rows) and it's in realtime application... and it performs! I can't even imagine what would SQLite be like with that kind of data. Still, please note that I'm not saying SQLite is bad, I'm saying the default route almost all devleopers take with it has some serious issues and yet nobody seems to care.
jeffesp 17y ago
> Having access to that code allows me to tightly integrate it with Excel, so much so, that I can use Excel functions (built-in functions, VBA user-defined functions and 3rd party add-in functions) directly from SQLite’s SQL; and vice-versa, access SQL functionality via Excel "formula" calls. This sounds really cool and is available here: http://blog.gobansaor.com/projects/xlite/ http://blog.gobansaor.com/projects/xlite/
budwin 17y ago
different tools for different tasks. as far as setup time goes, sqlite wins by a long shot.
stcredzero 17y ago
To be analogous to MP3, it would need: - Enabled distribution by overcoming bandwidth issues - Has been superseded by superior technology, but the majority think it's good enough already - Is seen as "open" even though it's proprietary - Because of the above, has become a defacto standard How many of the above does SQLite fit?
jacoblyles 17y ago
This actually might be good for a problem I ran into today. I'm distributing some source code with a large data file, one that we would like to store in a SQL database for ease of use. However, we don't know that the user machines will have MySQL installed. Would it make sense to use SQLLite in this instance? It sounds like you don't have to install it.
- jf 17y ago
  You do need to "install" SQLite, but not in the traditional sense. Most likely all you need to do is include a library that knows how to read and write to SQLite files. Worst case, you'll need to ship your code with a small static binary and call out to that.
jf 17y ago
Did I miss something obvious? How does one configure Excel to use SQLite?
- MrRage 17y ago
  I don't know how, but it's easy to imagine Excel pulling data from DBs using some sort of interface (ADO maybe?). All you need is some code the implements that interface for SQLite.
- rodrigo 17y ago
  http://blog.gobansaor.com/projects/xlite/ http://blog.gobansaor.com/projects/xlite/ I've used sqlite from MS Access, using sqlite ODBC driver; just for fun though, havent done anything useful with it yet.
vicaya 17y ago
Yeah, SQLite is as lossy as MP3 :) FWIW, I've lost some of my mails at least twice in Apple Mail due to corrupt SQLite files, but that maybe due to OS X's crazy fsync implementation.

69 points by rams 17y ago

Can someone refresh my memory of how SQLite handles concurrency? Doesn't it block on writes or something? For a long time I've thought a spiritual successor to Access, Filemaker, etc. based on SQLite would be a great idea.

fendale 17y ago
I think it blocks on write for 'the smallest possible time' (its does as much of the data change preparation as possible before locking the database). If you are just using it as a single user for generating excel style reports then this doesn't really matter. I have been thinking for a while that if I had to export a significant amount of data from an app for a tech savvy audience, I would probably send it out in an Sqlite DB instead of XML.
senko 17y ago
By default it does deferred transactions (exclusive lock is only acquired when you need to commit), but if you use insert/update/delete outside a transaction, each statement is it's own transaction. Locking is database-wide, although IIRC there's some branch that can do table-level locking.
- jmtulloss 17y ago
  As far as I know, SQLite's version of "table-level locking" is to put each table in a different database file and ATTACH them all to one database connection.
eli 17y ago
Yes, but it's a little more involved: http://www.sqlite.org/lockingv3.html http://www.sqlite.org/lockingv3.html

rarestnews 17y ago

I don't really work with SQLite too much (I mainly work with mySQL and in-memory-structures, like hashes), but here are a few observations that kept me from using it: 1. It uses a lot of IO unnecessarily. (Actually, necessarily, due to their motto, but 99% odf developers aren't aware that this is not necessary) SQLite-based applications often make my hard-drive sqeak like it's on a last spin - SQLite does A LOT of hdd activity (Chrome, Firefox, Adwords editor are notorious for that). It's SQLites motto - 100% consistency first! journal everything!.. but... that's my HDD and I don't want it to be dead because SQLite thinks it's great idea to write every byte it receives to insure me against 0.01% chance of data loss. 2. Head-to-head runs of mySQL vs SQLite in real-world applications - well, SQLite loses badly.. and I mean badly. I use a lot of hash-style databases (i.e. id-value storage, 2 columns only) in some of my tests SQLite lost with 10 times worse performance against mySQL (again, if they didn't try to record everything, like mySQL does with MyISAM - they would've got the chance). That being said - in-memory SQLite tables are very performant! Very! But... almost nobody uses that. As a result - Chrome/Firefox downloads phising update and my HDD lights like a christmas tree (download Filemon from SysInternals and watch how much Chrome or other SQLite-based engine uses your HDD for simple data that MUST be in-memory... or at least used by in-memory cache, like in MySQL... so your users' HDDs will eventually die because of this). I know it's none of developers troubles to think about the fact that their software unnecessarily uses much IO and leads to early hard drive replacement, but still, that's a big reason why I avoid using SQLite. I mean, there are a lot of in-memory structures that can (and should) be used, but once the developer sees how convinient SQLite is, he jumps right on it. I mean.. 99% of times SQLite should be used like this - load data from disk to :memory: table (i.e. replicate it to memory), work with :memory: and eventually dump it to drive (like once in a couple of minutes) and dump on "Save" command. But instead every popular program uses disk-based tables. Why? Because it's a lazy way. Who wants to add in-memory pre-caching when you can just open a table from disk in less commands? It's sad to see the current state of programming. Coming from an area where I had to write programs that would fit into 64KILObytes of memory and 3.5MHZ (that's 1000 times slower than modern single-core processor) - (ZX Spectrum)... and we've had whole huge games in that, working in real-time, with color graphics and almost unlimited galaxies (Elite)... it's just sad to see how the available resources are used these days. People had to use a lot of tricks to make things work with acceptable speeds... And now? I mean it's 3GHZ and gigabytes of data, yet I have to wait 10 seconds for Word (or Firefox) to start up? My main points are: 1) SQLite is convinient; 2) Disk-based SQLite (which is used by 99% or more pieces of software) is nowhere near the possible performance of in-memory; 3) disk-based SQLite is putting a heck of a load on HDD (fire up FileMon during your Chrome session and be amazed); 4) SQLite is nowhere near one-fits-almost-all solution, like mp3 is; 5) even so it will gain more popularity because it's convenient for developers. I have MySQL table with 72billion denormalized items (72million rows) and it's in realtime application... and it performs! I can't even imagine what would SQLite be like with that kind of data. Still, please note that I'm not saying SQLite is bad, I'm saying the default route almost all devleopers take with it has some serious issues and yet nobody seems to care.

jeffesp 17y ago

> Having access to that code allows me to tightly integrate it with Excel, so much so, that I can use Excel functions (built-in functions, VBA user-defined functions and 3rd party add-in functions) directly from SQLite’s SQL; and vice-versa, access SQL functionality via Excel "formula" calls. This sounds really cool and is available here: http://blog.gobansaor.com/projects/xlite/ http://blog.gobansaor.com/projects/xlite/

budwin 17y ago

different tools for different tasks. as far as setup time goes, sqlite wins by a long shot.

stcredzero 17y ago

To be analogous to MP3, it would need: - Enabled distribution by overcoming bandwidth issues - Has been superseded by superior technology, but the majority think it's good enough already - Is seen as "open" even though it's proprietary - Because of the above, has become a defacto standard How many of the above does SQLite fit?

jacoblyles 17y ago

This actually might be good for a problem I ran into today. I'm distributing some source code with a large data file, one that we would like to store in a SQL database for ease of use. However, we don't know that the user machines will have MySQL installed. Would it make sense to use SQLLite in this instance? It sounds like you don't have to install it.

jf 17y ago
You do need to "install" SQLite, but not in the traditional sense. Most likely all you need to do is include a library that knows how to read and write to SQLite files. Worst case, you'll need to ship your code with a small static binary and call out to that.

jf 17y ago

Did I miss something obvious? How does one configure Excel to use SQLite?

MrRage 17y ago
I don't know how, but it's easy to imagine Excel pulling data from DBs using some sort of interface (ADO maybe?). All you need is some code the implements that interface for SQLite.
rodrigo 17y ago
http://blog.gobansaor.com/projects/xlite/ http://blog.gobansaor.com/projects/xlite/ I've used sqlite from MS Access, using sqlite ODBC driver; just for fun though, havent done anything useful with it yet.

vicaya 17y ago

Yeah, SQLite is as lossy as MP3 :) FWIW, I've lost some of my mails at least twice in Apple Mail due to corrupt SQLite files, but that maybe due to OS X's crazy fsync implementation.