FYI: SQLite doesn't have ALTER TABLE.
well, "SQLite supports a limited subset of ALTER TABLE" https://www.sqlite.org/lang_altertable.html
We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)
121–125 of 125 posts
Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)
#122Earlier quoted context omitted.
cp does not make atomic snapshots. It copies by reading (usually sequentially) chunk by chunk from the source file, and writing these chunks to the destination file. This takes time. If the database has writes at the time of backup, the backup might be invalid (it contains some old parts and some new parts). (Unless you use e.g. the --reflink option of GNU cp, in which case it makes atomic snapshots on filesystems th…
Isn’t one of the more important points of POSIX that reading sequentially results in an atomic copy ? As long as you keep the file handle open and the fs supports it.
I don't know which point you mean, but that would mean that any writer would be blocked indefinitely by any other reader. That amounts to a read lock. You don't get a read lock just by opening a file for reading. You can test that with a simple shell script
{
echo line1
echo line2
} > test.txt
{
read line
echo got "$line"
sleep 2
read line
echo got "$line"
} test.txt
wait
got line1
got line1
POSIX might contain something that requires aligned blocks of 512 bytes or so to be read or written atomically. But only if you do that in a single system call, of course.
Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)
#123Earlier quoted context omitted.
But that's my point. People using sqlite as the store for a web application seems like the wrong tool for the job.
Why, if you just read from it? It's single writer multiple reader.
Because a single writer limits throughput significantly. For a small hobby website or a blog that may not be an issue.
Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)
#124Earlier quoted context omitted.
Yeah. To be clear, I think even a 150GB sqlite database would perform just fine for beets' purposes.
Even as a heavy PostgreSQL fan and promoter I don't see why you would choose anything BUT sqlite for single-user applications unless you needed something that only other databases provide (richer procedural language support or other extensions that the fairly basic but still adequate featureset SQLite provides) - size of the dataset is only one factor to consider when choosing the storage layer for your application,…
Real typing? https://firebirdsql.org/
Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)
#125This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.
CSV's biggest competitor is XML. But both XML and SQLite have the same issue: They give you just enough rope to hang yourself with. While SQLite is a fantastic micro-database engine and a file format, it isn't a very good universal B2B format because there are too many features you'd have to support to interoperate. I'd argue CSV's biggest strength is that it is easy to parse correctly, because the format is so simpl…
Just as a point of interest, sqlite is behind the geopackage [1] specification, which is a common format for exchanging geospatial data.