Appropriate Uses for SQLite
sqlite.org
sqlite.org
I've moved everything to SQLite and couldn't be happier. Not only is it easier to distribute assignments (e.g. a single SQLite file, instead of CSVs that need to be manually imported), it does everything I need it to do to teach the concepts of relational databases and join operations. This typically just needs read-only access, so our assignments can involve gigabytes of data without issue.
However, SQLite follows most of the standard syntax that Postgres does, so having students move right into CartoDB, which lets you run raw Postgres, has never been a problem.its just the self hosting that's a pain :).
One more thing that I wish I could have from Postgres as a teacher: how it returns an error when you include non-aggregated columns in the SELECT clause. In MySQL and SQLite, selecting extra columns won't throw an error, and the results look close enough to correct to be fairly dangerous for novices. Better to just throw an error as Postgres does.
Hipp based the syntax and semantics on those of PostgreSQL 6.5. In August 2000, version 1.0 of SQLite was released, with storage based on gdbm (GNU Database Manager).According to this, "SQLite was originally written from PostgreSQL 6.5 documentation" https://www.pgcon.org/2014/schedule/events/736.en.html
So, "fork" wasn't the right word. Apologies.
We (DB Browser for SQLite team) put this one-pager up yesterday about our upcoming "SQLite in the Cloud" project. :)
SqLite usually "just works", can easily be reset if you do something wrong (just delete file!) and has better GUI browsers and tools.
SQLite is good choise for absolute beginners.
Later, when teaching "real" multi-user RDBMSes, although MySQL may be more popular, it makes more sense to teach PostgreSQL as "default open source database". Both will do the job, but PostgreSQL has got more stuff right from the beginning, which is especially important when learning. Think PHP vs. Python, PHP may cut few corners but it's not ideal language for teaching generic concepts.
Maybe not even Python, suggest go or rust. My generation we started learning CS with Pascal and moved to C, when we had a bit more understanding.
Rust? NO! One of the worst choices for a first language. Similar to C++, Rust is really heavy on concepts.
That being said have you considered using docker (or other popular container tech) for making installation of MySQL easier?
Firebird has a more complete support for sql than sqlite has. It also has the advantage of being able to be used both in a manner analogous to sqlite or as a full blown server database.
I mean, really. SQLite is remarkable, impressive, and used everywhere, and we never talk about it. Emacs is a remarkably impressive piece of engineering, bash is the world's default shell for a reason, Nano is the newbie's text edior, and, well, just imagine for a second what would happen if grep or curl stopped working.
Did you ever run Windows?
$ curl --version
curl 7.47.1 (x86_64-w64-mingw32) libcurl/7.47.1 OpenSSL/1.0.2g zlib/1.2.8 libidn/1.32 libssh2/1.7.0 librtmp/2.3
Protocols: dict file ftp ftps gopher http https imap imaps ldap ldaps pop3 pop3s rtmp rtsp scp sftp smtp smtps telnet tftp
Features: IDN IPv6 Largefile SSPI Kerberos SPNEGO NTLM SSL libz TLS-SRP
$ grep --version
grep (GNU grep) 2.24
Copyright (C) 2016 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Written by Mike Haertel and others, see <http://git.sv.gnu.org/cgit/grep.git/tree/AUTHORS>.
$ uname -a
MINGW64_NT-6.3 redacted 2.5.0(0.295/5/3) 2016-03-20 18:54 x86_64 Msys
$
It's up there with bash
bash is the world's default shell for a reason
This would cast nano in the rôle of Tedit, of course.
http://minnie.tuhs.org/cgi-bin/utree.pl?file=V7/usr/src/cmd/...
Or is bash just as bad?
Do you know the reasons for the "Pascal macros" as they don't even make the C code Pascal compliant?
[1] http://minnie.tuhs.org/cgi-bin/utree.pl?file=SysIII/usr/src/...
https://github.com/EtchedPixels/FUZIX/blob/master/Applicatio...
I spent some time making it work on modern machines, and it's... I don't really want to use the word 'bad'; because I don't think it is. I kept finding issues which I would spend ages tracing through the code muttering about ancient C which didn't understand alignment and stuff, and then discover that the code was actually doing everything right and the problem was at my end. I couldn't find a single bug in it.
But incomprehensible --- oh god yes. The way it handles memory is really bizarre. The parser is really bizarre (there's the famous Tom Duff quote: " Nobody really knows what the Bourne shell’s grammar is. Even examination of the source code is little help."). And then they interact!
I guess I'm switching to ZSH now...
Totally OT but, "it's an bird, it's an plane, it's superman!", doesn't exactly roll off the tongue, and neither does the above[1].
We switched to a default shell with incompatible syntax not because of network effects but because it was much better than tcsh — initially just for scripting, later for interactive use as well.
zsh already existed at that point, btw.
As for mastering bash? Nobody masters bash. Brian Fox hasn't mastered bash.
I still like SQLite, and most people don't need anything beyond simple joins, but when you do need some more advanced SQL, it can be a bit challenging.
SQLite does not compete with client/server databases. SQLite competes with fopen().
There are also some nice hooks to allow the use of SQLite from Lua scrips. It's pretty easy and it fits into the Lua world view of data.
Maybe as a log archive format, though...
... oh, wait, that hasn't been solved yet. Instead, there's a variety of hacky workarounds (send it to rsyslog and get it to ship; follow a journal with ncat and ship that; some others). Centralising journald logs is my current ops problem de juor.
(The "reinvent it badly" is systemd replacing syslogd without re-implementing functionality that was important to a big subset of admins, because the people who wrote systemd are focused on the personal workstation case to the exclusion of all others)
systemd is in many respects a major departure from UNIX philosophy, and I predict it will mark an inflection point in the quality and usability of Linux distros that adopt it, hence my efforts to migrate my own systems to FreeBSD.
For those wondering what I'm banging on about, please read The Art of UNIX Programming, which should have been called The Philosophy of UNIX:
http://www.catb.org/esr/writings/taoup/html/.
Edit: what I mean by fundamental is that it shouldn't matter whether the tools are intended for desktop or server use; they should be designed to interoperate seamlessly using text protocols via pipes, sockets and files. That way they can be composed, filtered and transformed in ways not yet dreamed of by their creators. The systemd folks are falling into the Microsoft and Apple trap by trying to anticipate how their software will be used, instead of building it so it can easily be hacked upon for uses they themselves haven't dreamed of (which oddly seems to include 'servers').
To put it bluntly: systemd has neither the hacker nature nor the UNIX nature, and history has been unkind to OSs with neither. I'm betting against it being a Good Thing in the long run.
It's not a Good Thing now, though: It's an attack surface.
Also FWIW, I like ESR a lot :) I'm mildly concerned about thread drift, but... why do you dislike him so?
Honestly, he's kind of worse than RMS, who is merely unpleasant to be around...
But racism, and paranoia? I'd (seriously) like to see evidence of those if you have them. Nothing I have seen him write or do suggests he treats people in any way other than as individuals, on their merits alone.
Sounds pretty paranoid to me.
I might have been wrong about the racism stuff. I swear I saw some stuff, but I can't find it now, so I might have been confused with somebody else.
But at the end of the day, he's still an unpleasant person.
I like SQLite, but I don't think logging is a good use case.
Heard about people login JSON ? (that may be truncated because log lines can be truncated when write is called with size > PIP_BUF in a concurrent environment?)
I know JSON is the new XML, but dinosaurs like me have learnt the hard way that logging should not be stored as a document but a journal of chunks considered truncated, and that relying on the atomicity of system read/write/close/open/seek/tell/unlink is a damned good idea because the day you need logs, is usually the day a major crash happens. Hence a day where a corruption of logs is more likely to happen.
True though that when you have no crashs, you love JSON format. But even truer it is when an incident happens you want a resilient system that still can log in a reliable way.
And this is possible because the only place where a newline can appear in JSON is whitespace (where it can be safely replaced with a space; although most JSON serializers provide enough control over the output that you can just make sure that it never appears there in the first place).
And if \n is prohibited in string it is NOT JSON per ecma xyz anymore. it is something else.
Additional complexities that do not seem meaningful but when in congestion they add up to make your life a hell. But well, energy is cheap, VMs are cheap, operational expenses for cloud so less than coders, why bother?
With this kind of reasoning we will have so broken internet appliance coded with feet that we will experience a massive DDOS made by connected toasters and poorly coded cameras.
This per ECMA-404.
Like I said, the only place where JSON permits actual newlines is as part of insignificant whitespace. But because it's insignificant, newlines can be replaced with any other whitespace character, and the resulting JSON will have the same exact meaning.
And why would grep lose "order of magnitudes in speed"? If you were actually using grep, it'd work exactly the same as it does for any text file. The only thing that JSON does is add some structure to the contents, so that it can be easily converted to some tabular format that's more convenient for structured queries, aggregation etc. But you can still treat it as plain text for all purposes.
Crash diagnosis is but one use for logs.
Given the extensive testing including crash testing, it's plausibly more robust than plain text - because you're almost surely not using any kind of fsync's in your plain-text logger, so that text file isn't as incorruptible as you may think. And you may write a buggy logger, or use a buggy json implementation, or write incorrect error-recovery code when reading the file.
I'm skeptical that robustness is an argument in favor of plain text logging over sqlite.
Agreed. But if the log file is corrupted, then a plain-text one will be easier to decipher for a human than any binary blob.
I am, though, and have been for many years.
* http://untroubled.org/daemontools-encore/multilog.8.html
* http://b0llix.net/perp/site.cgi?page=tinylog.8
This is a very important property. It's also what makes UTF-8 resilient.
Netstrings are one option for ephemeral streams. But they are a bad tradeoff for persistent information: you have to read from the beginning of the stream until you find the relevant information. And a single corrupted byte destroys all the information after it.
Better options for persistent data are (pointer+length)s or memory-pools + ranges.
This can be streamed and even gzipped in said stream, meaning you get compression and a pretty easy format you can use in most platforms these days with very little intermediate processing or extensions.
With JSON you can have additional metadata on each record, and not have it affect the mainline... now, this cannot be queried directly, but it can be very easily streamed/imported elsewhere.
{"foo": 1}{"bar": 2}
{"foo": 1}
{"bar": 2}
As for general logging: I prefer it to be structured text written to stderr, and have a PEG parsing it for me. stderr is pretty much a catch-all log stream, and some 3rd party software insist on writing to it (most do however offer a way to override this behavior, e.g., chromium-headless, libxml2, &c). Having a PEG for those cases means I can still get JSON for everything if I need to, even if the data in the log is unexpected. If I need to, I can then modify the PEG for that use case.
Of course, it's a case-by-case, pragmatic decision. If I only want to log certain data and disregard stderr, or if I can guarantee everything that gets written to stderr will be valid JSON, writing logs as JSON may be better.
{"dtm":"2016-...",...,meta:{...}}
The database is only opened by 2 users on the same machine. One is a normal user, the other one is root for a daemon process. That by itself might be an uncommon scenario.
So I tried it out this morning and found that any writes are invisible unless I restart the app to close the database. For my use case that isn't an improvement as writes made by either user should be visible by the other user. Even tried it with setting read_uncommitted to true, but that did not help either.
Of course it is possible I am still doing something wrong, but at this moment it doesn't look like the WAL journal mode is an option for my app.
A pity as I expected -without WAL- to be able to read when another process is writing, well just a delayed read would be fine, but instead there's a -database is locked- error that pops up to the user.
For the moment I added a patch to my apps whereby the applications handle the locking by itself at a slightly higher level as I got a bit tired of the problem.
This is done via a separate lock file that is opened exclusively before any write action and closed after the write. By doing that I can simply delay the reads for a bit when the GUI process tests to open the lock file and that appears to have cured most problems.
It's a tiny bit more advanced as the above, but that's basically it and it appears to have cured most issues.
edit: might have misread your question, was it about the WAL journal mode? Yes the processes do commit the transactions they write. I need the results immediately, not after sqlite decides to process the WAL journal.
You may wish to take a look at the last section (Transaction Control At The SQL Level) of the following page. Maybe it's relevant to your case:
When staying in normal journal mode the app sees the data just fine and the data is committed directly in that case. Updates/Deletes are all pretty much instant and any queries results are correct. There might be an issue with the database drivers I depend on (FireDAC) in that layer I even go as far as closing the tables on each query/update after a commit. The problem with normal journal mode is the lock error popping up.
When I switch to WAL journal mode the data no longer appears to be written directly even when turning autocommit back on.
So while WAL mode appears to fix the lock issue, the data only gets committed on closing the database connection. As a result the GUI process can't interact with the daemon process anymore as it only sees old data. Opening and closing the database on each insert/update/delete to force the data to be written simply isn't an option.
Looks like the manual locking that I added around reading/writing the data is working out OK. Time and a lot of testing will have to tell.
Despite all that I still like SQLite a lot. It's so incredibly easy to deploy and runs on all the platforms my app is targeting.
The reason this was happening was not because of SQLite, but due to how the FireDAC driver handles the locking.
That driver had a setting "BusyTimeout" which supposedly takes care of a lock waiting time.
According to the documentation it has a default timeout setting of 10 seconds. That clearly did not work, I even had set it manually, still to no effect.
The other day I figured to try and set this via an SQLite pragma setting... (busy_timeout)
I've not seen a "database is locked" issue since then and I've completely removed my manual locking layer, so "case solved" and it certainly wasn't SQLite to blame.
That's an interesting claim. I would rephrase that to, "Are their more http calls that use concurrent connections to a DB, or standalone applications that do not?" I would wager the former.
Even on most websites, I suspect the need for concurrent, long-lived write transactions is much rarer than people assume. If your write transactions are short-lived, then sequential execution is a reasonable approximation of (slow) concurrency, at which point it's a question of load whether that's good enough. But the window in which it's not good enough is very slim - hardware simply isn't all that concurrent in the first place, and as you scale, some sharding strategy is required anyhow.
So the more plausible limitation is long-lived write transactions; e.g. where a write cannot be committed until after some other confirmation occurs, possibly over the network. That simply won't work well at all in sqlite - not that it's a great strategy to use on other DBs...
Yeah, but the sqlite website doesn't need a db at all.
To quote the sqlite website itself:
> The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Each dynamic page does roughly 200 SQL statements. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.
I think its fair to assume that the sqlite site could be redesigned to meet most of its functionality as a largely static site, but that would come at a loss of functionality. And obviously it's a form of dogfooding, but that's not objectionable, right?
https://www.sqlite.org/lockingv3.html
There is a also big section on how to corrupt the database. It's a heads up that if you decide to do shortcuts there will not be a happy ending.
A little more complicated doing concurrent use than with something like MySQL, but there is much more engine on the MySQL side. If multiple concurrent users with high transaction levels, SQLite may not be your best first choice.
On windows it utilizes the http.sys engine (which is the same used by IIS the Microsoft web server)
With FreePascal it also works on Linux .
SQLite does not have multi writer concurrency (usually MVCC as in Oracle/MySQL/PostgreSQL or optimstic transactions like Backplane). If you need those, SQLite is not for you.
Since it completes with fopen(), you get about as much structure and validity.
There is a currently only a simplistic python library which reads databases at about 1MB/s. On the plus side it's dead simple to use, only a single library call to parse a file as schema, tables, and indices.
There is also a C library in development which lexes at about 300-600 MB/s in a single thread (depending on how many columns are actually needed and thus have to be written to per-column lexem buffers) and which I hope will have a release next month.
Because I can live with the ladder: weak types suck in programming languages, but are okay in DBs, and the types get verified multiple times on their way in and out of the DB in most systems.
Besides, it won't mangle your data. Unlike some DBs that I could name...
The reason most people don't complain about this is that it's a far from common issue to totally miswrite your SQL statements so badly that you wind up mixing up columns. And when you do, it's usually detected pretty fast.
Maybe you are thinking about foreign key constraints, which for backwards compatibility are off by default, unless you use a compile-time option to make then on by default.
That kind of depends on how you use it. Obviously this depends on the row size, constraints, etc., but if you want to write more that a few thousand rows per second for longer periods, the performance limitations of SQLite will become very obvious very quickly.
Are you thinking of corrupted files? Sqlite disk operations are atomic.
Do people agree with this? I was under the impression you should not use SQLite for production websites for some reason. Django has this to say, for instance [1]:
When starting your first real project, however, you may want to use a more robust database like PostgreSQL, to avoid database-switching headaches down the road.
[1] https://docs.djangoproject.com/en/1.10/intro/tutorial02/
I tried moving from from MySQL to Postgress. Somehow my unique constrains in Django weren't unique in the database so it threw errors when I tried dumping to Django fixtures.
As SQLite themselves state, they're running a 500K hits/day site on SQLite just fine. They also point out that their site is not particularly write heavy, which is a somewhat important point to be making with SQLite specifically.
What do Django mean by a "real project"? If this is a project you intend to scale to beyond the scope of SQLite, then starting from something that will scale that way in the first place will alleviate later growing pains. If it's your personal site and will always and forever be run on a VPS with 1GB of RAM? There's no reason not to just stick with SQLite -- and you get the benefits of not having to maintain a "real" db service.
Is there more to do for a small site to maintain postgres relative to SQLite?
Run backups and updates, de-lint occasionally; what else?
However, sqlite is limited in what it can do, so when you need to go beyond what it can do then it can be a pain.
I'd say sqlite is a DB choice for people who understand what each DB option gives them. If you were new and needed a default choice, then pg, or mysql, or sqlserver are going to be pretty flexible long term. You also are going to get a lot more technical info on the web about how to use it with whatever web framework you have chosen. However, I have used it for websites where I have a pretty good idea about my data needs. works fine.
I use it more in the "competes with fopen" case though. Super great as a settings / info / persistence store
I don't. I've corrupted SQLite DBs enough to not have warm and fuzzy feelings about it like I used to have.
I think it's only a good choice when you just need a database for your app that will barely be using it, and if you didn't use it you'd be writing to a file instead. And, that's basically what the SQLite docs say.
However, even then, I think it can be short-sighted. I've used webapps before that used SQLite and I thought to myself: if they'd only used MySQL or PostgreSQL and then provided access to it, I could have used it.
Be aware though, if you decide to use a scalable DB like PostgreSQL, it will require a port to be open for the DB, even if only locally. If you're trying to minimize how people can access your data, you don't want a port open/an extra port open, and you're not going to hit it very hard, SQLite's probably your best choice.
And corruptions, while obviously not unheard of, aren't very common. Even in power failure.
Surely it supports AF_UNIX sockets?
On the small projects, DB admin doesn't seem to be much more complex than using a SQLite DB. By the time you get the DB load high enough that you really want to pay attention to administrating it, SQLite has probably given up the ghost long ago.
Don't get me wrong, SQLite is great for what it does. I don't see the upside to it on this though. Even if you know for sure your site will never hit high traffic, it just isn't that hard to run a conventional DB. And if it does, it's a lot easier to pay somebody to set up your DB server right than to convert over to a conventional DB and then get it set up right.
A project running on sqlite can be quickly taken to just about any box and run without any infrastructure dependencies.
You can easily run a hundred instances on a single machine, for dozens of simultaneous users, without any setup or coordination. Computing resources are only required for access, not for availability.
Still love sqlite for what it is though!
Our goal is to provide a SQL backend to everyone or device on the planet. To get that level of scalability and manageability (ie disk usage and cpu usage) you pretty much have to use an in process database. SQLite is the best there is as others in this thread have noted.
Full disclosure, I'm the founder of Lite Engine:
While it was broadly a success, I consider the following major problems when teaching to beginners:
* very loose syntax. CREATE TABLE PERSON ( ID BANANA BANANA BANANA ); is legal :)
* no type-checking: you can insert strings into an INTEGER column and vice versa - while you're trying with a straight face to teach students that one of the advantages of a proper database is that it can enforce some consistency on your data.
* in the same vein - foreign key constraints are NOT enforced by default.
* misusing GROUP BY produces results, but not the ones you want. I'd much rather any use of aggregates that is forbidden by the standard also gave an error, to discourage students from thinking "it produces numbers, therefore it must be ok".
And a bit further down:
> SQLite database is limited in size to 140 terabytes [...] if you are contemplating databases of this magnitude [use something else]
Yeah no. "Large datasets" here means a few megabytes. I figured that out the hard way:
I had a database of about 70 megabytes and ran a query with "COUNT(a)" and "GROUP BY b" on it. This makes it write multiple gigabytes to /tmp until it goes "out of disk space" (yeah /tmp on my ssd isn't large).
I heard nothing but awesome and success stories about SQLite until a few weeks ago when this fiasco happened. I still like SQLite for its simplicity and last week I used it again for another project, but analyzing "large" datasets? Maybe with a simple SELECT WHERE query, but don't try anything more fancy than that when you have 100k+ rows.
Also, have you compared this to what other DBs do?
Over the years I've had my share of complex queries with subqueries and aggregates, both for class and for my own projects, but never have I encountered 70MB exploding into multiple gigabytes (I don't even know what its final size would have been). I guess I could have used EXPLAIN and dug into it, but never having had this before I figured it was SQLite not being made for it.
It is true that sqlite doesn't have as good of a query plan optimizer as larger RDBMSes, and is a little lower-level, but the tradeoff of having the simplicity is that you must understand a little more of the internals to design more complex queries.
this includes whatever you stick in parenthesis.
`select * from table where account_id in (select id from accounts where name like 'Fred%');` for example
Mailing list: http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
It also gave me the chance to learn SQL for fun.
Sadly, it is not often looked upon as an end-user tool.
Wait... Why are you on HN?
It's not a problem or anything, I'm just kind of curious.
I just wonder who else would be interested
I've seen so many people struggle with custom binary formats; I imagine there are countless research hours lost in figuring out how to work with these obscure formats. I've advocated to all students I work with to make use of SQLite to store simulation data for their thesis projects and my experience is that they're quick to pick it up and figure out how to do some pretty complex querying.
It's one of those things that I don't understand about academia: there are so many standards and well-established tools in the tech/IT sector that we don't take advantage of. SQLite and JSON are the two that I constantly advocate to everyone I work with.
On Windows, we have this: https://en.wikipedia.org/wiki/Extensible_Storage_Engine ESENT plays very nice in high-concurrency scenarios.
Implementing client/server where you only need an embedded DB comes at price. It bloats and complicates the installer, increases attack surface, conflicts with other software for listening TCP port number, interferes with firewalls, consumes more resources, slows the startup, etc…
I've also used it as a main data store for single user Win32 apps.
In my early days of web app programming, I had an app that created a brand new SQLite data file for EACH customer that logged in and created an account on the web app. I thought it would be the most secure way to separate datasets and protect privacy for each user whilst negating the multiple write lock issues on the same SQLite database. Tip: Don't even bother to do this! The eventual data maintenance headache was far worse... :)
My solution was to create a simple SQlite database, import all the entries into it, and then generate the views by SELECTing from that store.
Populating the database, even if it got thrown away immediately afterwards, was more efficient than trying to store all the entries in RAM.
However, I was recently pointed at MonetDB:
Monet's an open source column store, and I think it's worth evaluating by anyone doing offline analytics or research-driven work.
I really enjoy using sqlite. Not everything needs a client server model, and having your entire database located in a single file makes a lot of things way easier.
A HN favorite: https://www.sqlite.org/testing.html
SQLite is the Samuel Vimes of software: It's not the fastest, or the strongest, but it's solid as a rock, and entirely dependable.
If I was writing a ray tracer and needed to store vertices, would it makes sense to use a SQL database? How about for a list of object? Or textures?
In general I often need to filter on objects, update object state, generate new objects, remove some others, etc. but I never know when I should stop thinking containers and start thinking "aha! time for SQL"
You should use a database to store data that you want to keep after the program terminates, not so much transient things like in-memory data structures. It's also best used for relational data- stuff that is logically linked together.
For developing a game, maybe storing item tables with items and stats or the player's inventory might be good candidates. Sqlite in particular is good for this because it's easily embedded and a lot of games use it from what I know.
This is oversimplifying a good bit, but it's hard to completely describe the scope of relational DBs.
It's kinda described here: http://gameprogrammingpatterns.com/component.html But the author doesn't take it to it's logical conclusion. I found the full explanation by it's inventor here, in this slide deck: http://scottbilas.com/files/2002/gdc_san_jose/game_objects_s... But I'm fuzzy on the details
Doubtful. Player inventory is not going to be large enough to bother, and item tables you'll want to be in-memory anyway, so you might as well just read them from CSV, JSON, XML etc (and that way you can easily edit them, too).
I would say that SQLite only makes sense when your dataset is too big to be entirely loaded into memory in a cooperative environment (i.e. assuming that your app is not allowed to hog the entire memory). I'd say that starts at tens of megabytes.
I know sqlite is used heavily on iOS and Android and a lot of people use it as a glorified serialization format. Probably not the best in most cases but hey sqlite is so lightweight that it doesn't have much downside. I tend to use it as intended as a lightweight database myself but hey if it works it works.
For C++ especially, I would recommend looking at Boost multi_index library. This gives you the ability to do fast lookups on a variety of keys across the same data.
Pretty much the only benefit I can see from SQLite in those small dataset scenarios is when you need persistence and the ability to change subset of data in an atomic way (if you only need to save the entire in-memory dataset atomically, you can always just do the rename trick to ensure atomicity with far less overhead). Well, and, I guess, optimization of complicated queries - but I'm somewhat skeptical about the ability of their optimizer to use indices in a query that's really complicated; and simple ones are trivial to do explicitly.
Saying that from seeing links to our site (sqlitebrowser.org) from game developers & users on Steam, and also people asking us questions about various database files they're trying to figure out (as an end user).
The various SQLite encryption options around seems to make a difference too, for game developers wanting a simple(-ish) way to "hide" the info from players. Embedding an encryption key isn't a fantastic approach, but it seems to be "good enough" sometimes.
If you don't find yourself needing to share data between two processes at the same time or storing data on the system, you may never need it.
SQLite can actually be used as a decent save system for a video game since you can just store into it and read from it. You could actually do a "Mass-Effect Style" storage system where you can store items from Game 1 and read it on Game 2. You could actually have your studio share a SQLite DB and have your games reference whether a user has played another one of your games.
This enabled the game designers to test changes in game balance without having to wait for modifications in the executable file.
Depending on the design of your game, you can use SQLite in a similar way.
Unless you design it as a service, where one process is using sqlite to store its locking data and you use it from other processes by communicating with this service - then it's ok.
This is the one that usually gets me. For whatever reason, I tend to prefer side projects that are "take a dataset and make a tool out of it". It often ends up with simultaneous bulk writes when the dataset is updating.
I'm a big sqlite fan. Just throwing this out as a limitation for anyone deciding if it's appropriate for their project.
It would not solve the usual sort/group by problems that require cross-machine communication, but would take full advantage of SQLite's optimizations for other problems.
I haven't done web work in a while but am I the only one who thinks that's a ridiculously high number for a single page?
Any FB page probably also needs that many.
For most sites, 200 queries is way, way, way too much.
It is, but it does happen, usually due to design decisions.
When I was still young enough to do PHP (about a decade ago), one of my largest projects was a domain-specific CMS for code collaboration. It was all working fine on my development system, but in production, every page loaded for at least 4-5 seconds. I inspected into the DB queries that were used to build a single page, and found a lot of duplicate
SELECT * FROM {table} WHERE id = {id};
A complete log of the 200+ SQL statements used in generating the page above can be seen at http://sqlite.org/tmp/timeline-sql-log.html
The list of check-ins is computed by a single query. But that query then tosses the list over the wall to another subsystem which generates content for each check-in. And several queries are required for each check-in to extract the relevant information needed for display.
The timeline example above is an information-rich page. Perhaps it could be generated using fewer than 200 SQL statements. But SQL against an SQLite database is so cheap that it has never really been a factor. You can see at the bottom of the page that it was generated in about 25 milliseconds. Profiling indicates that very few of those 25 milliseconds were spent inside the database engine.