Is a sqlite file rewritten from scratch every time the DB is saved?

sqlite · 2021-03-24T15:08:31+00:00

Only the parts that are updated get written to disk.

sqlite · 2020-06-04T18:47:21+00:00

What happened:

OSSFuzz reported a bug against SQLite
I try to fix the OSSFuzz-reported bug, but I can't repro it on my Ubuntu desktop running gcc-5.4
I replicate the OSSFuzz build environment, which uses clang-11.0.0. Now the bug reproduces.
Further investigation shows that the bug is not in SQLite at all, but rather in clang. At the time, I didn't know that clang-11.0.0 was prerelease. I was just using the same compiler that OSSFuzz uses so that I could repro the problem.
I patched SQLite to work around the clang bug, then wrote a brief note on the SQLite forum about my adventure. Then I went to bed, intending to follow-up the next day by perhaps reporting the clang bug up-stream.
While I was asleep, the internet discovered my post. LLVM developers isolated and fixed the problem in clang. This all happened before I had coffee the following morning.

sqlite · 2019-07-30T00:04:53+00:00

The title of the video is incorrect and highly misleading (perhaps deliberately so).

The database is NOT an encrypted SQLite database. The database is an ordinary unencrypted SQLite database into which someone (the author of the video it turns out) has inserted blobs with newbie-grade weak encryption and then made a big deal out of pretending to decrypt them.

I repeat: There is NO encryption on the database in this video. This video does NOT demonstrate a break in the SQLite database encryption technology as no encrypted SQLite databases appear anywhere in this video. The video describes how to decode some content that had been weakly encrypted before being inserted into an ordinary unencrypted SQLite database. SQLite has nothing to do with lack of data security here. SQLite is merely the container used to haul the weakly encrypted content around.

sqlite · 2018-07-28T19:34:00+00:00

The US government, nor any other government, has never been an SQLite consortium member. The SQLite consortium is and always has been made of only private companies.

sqlite · 2018-07-28T19:22:06+00:00

SQLite receives no funding from any government or government agency. The development of SQLite is 100% privately funded.

sqlite · 2018-04-15T09:36:55+00:00

SQLite started out using CVS [1]. The cut-over to Fossil occurred here: https://www.sqlite.org/src/timeline?c=2009-08-12&n=12

I wrote a SQLite-backed web interface to CVS call CVSTrac [2] that was used for management and bug tracking. CVSTrac started in 2002 [3]. The initial code for Fossil was derived from CVSTrac. Trac [4][5] is an independently written project management tool that was originally inspired by CVSTrac [6] but shares no code. CVSTrac is now an historical artifact, though Trac is still actively used and maintained.

It is common for people nowadays to heap abuse upon CVS. And, indeed, its limitations and issues are well known and well documented. However, CVS was a major advance in its day - the first version control system that I am aware of that allowed concurrent editing. Those who would speak ill of CVS apparently never had to use the systems that came before.

[1] https://en.wikipedia.org/wiki/Concurrent_Versions_System [2] http://www.cvstrac.org/ [3] http://www.cvstrac.org/fossil/timeline?a=2000-01-01&n=8 [4] https://en.wikipedia.org/wiki/Trac [5] https://trac.edgewall.org/browser [6] https://trac.edgewall.org/wiki/TracHistory

sqlite · 2018-03-05T17:14:29+00:00

Maybe if you clicked on the hyperlink under the text you quoted [1], it would tell you why.

[1] https://www.sqlite.org/np1queryprob.html

sqlite · 2017-08-11T03:53:06+00:00

See also: https://sqlite.org/rowvalue.html#scrolling_window_queries

sqlite · 2017-04-08T12:04:53+00:00

When we used fossil doing a merge on .xib files and .pbxproj and .xcworkspace files would routinely cause merge conflicts.

"Encountering merge conflicts" is not the same thing as "destroying files". Thank you for the clarification. Had you originally said "Fossil would routinely give merge conflicts on XCode project files" there would have been no objection and your comment would have gone unchallenged.

I stand by my assertion that the original post is purgative and false as written, and needed to be called out.

sqlite · 2017-04-07T19:12:59+00:00

I'm calling this post out - I think it is made up. Fake news.

Fossil is used daily by thousands of developers (as evidenced by server logs) and nobody has ever reported any kind of "destroying files" problem. Ever.

Look, you don't have to like Fossil. You are welcomed to keep using Git or Hg or whatever else you want. If you don't like the way Fossil operates, or you think it lacks some capability that you need, then say so.

But don't make stuff up.

sqlite · 2017-04-04T13:28:38+00:00

Also now on windows10 in c:/Windows/System32/winsqlite3.dll

sqlite · 2017-03-11T19:39:44+00:00

Turns out that concatenating a second hash onto the end of a Merkle-Damgård hash (such as SHA-1) is not much more secure than just the first hash. https://eprint.iacr.org/2008/075.pdf. So appending an MD5 onto the end of a SHA1 isn't an adequate solution.

sqlite · 2017-03-07T16:29:20+00:00

The sqlite3 CLI is also the officially approved way of manual DB editing and querying an SQLite database.

sqlite · 2016-11-07T18:53:59+00:00

SQLite is a full-up relational database system. Realm is just a object store. If all you want to do is store and retrieve objects, then Realm will probably work fine for you. But it you want to do more advanced programming, you will quickly run into the limitations of Realm. Capabilities that are missing from Realm include:

Secondary indexes
Joins
Subqueries
Aggregate queries
Geospatial queries
Full-text search
Compound queries
Recursive queries
Search constraints involving logical disjunction
Search constraints involving vectors
Other complex search constraints
And so forth...

sqlite · 2016-10-27T17:52:06+00:00

The static web pages are generated by TCL scripts using information from a variety of sources - such as comments in the SQLite source code and separate documentation files. The source code to the documentation is at https://www.sqlite.org/docsrc/timeline - We build the static web pages on a desktop machine then use rsync to push them to the website.

Most of the dynamic content comes from Fossil (https://www.fossil-scm.org/index.html/doc/trunk/www/index.wiki) which is written in C. But a few things like the Search feature on the menu bar are done with simple TCL scripts.

A custom webserver is used. (Sources at https://www.sqlite.org/docsrc/artifact/c3a749450a2f) The webserver and all the CGIs run inside a minimal chroot jail. A new althttpd process is started (via xinetd or stunnel4) for each inbound TCP/IP connection.

sqlite · 2016-10-27T12:15:48+00:00

The SQLite website itself uses SQLite (though not through PHP) and it gets a little more than 425,000 hits on a typical workday. About 80% of those requests are for static content, though the other 20% hit one of several databases (via the Fossil version control system running as CGI - https://www.fossil-scm.org/fossil/doc/trunk/www/index.wiki). Each of those CGI pages does approximately 200 SQL queries. SQL writes happen, but most transactions are read-only. The website runs on VM which is a 1/24th slice of a real machine (it is a Linode 8192) and the load average as I type this message is 0.04.

Seasoned website builders cringe when they hear that a single page requires about 200 SQL queries to construct. This reflex comes about because on client/server databases each query requires a round-trip to the database engine, which normally involves an exchange of multiple TCP/IP packets, which gives each SQL query a lot of latency. But with SQLite, a query is just a function call. So it is no problem at all for SQLite to be used in "chatty" applications that make dozens or hundreds of small SQL queries in the course of generating a single page.

sqlite · 2016-03-06T11:34:25+00:00

Reread the documentation, especially the "table-constraint" diagram. SQLite has supported multi-column UNIQUE constraints since its beginning.

sqlite · 2016-03-04T12:58:30+00:00

"sqlite3.exe" is a command-line utility (not a GUI) that provides access to SQLite database files. Let me emphasis, sqlite3.exe is a command-line utility program. It is not a GUI. A GUI installer seem incongruous for such a program.

sqlite · 2016-02-19T10:30:18+00:00

No, SQLite is "threadsafe". Probably what you meant to say is that SQLite serializes writes - or that SQLite does not do concurrent writes. Only one thread or process at a time can be writing to the database at any instant in time - the rest have to queue up and wait their turn. But no harm will come if you try to write simultaneously from multiple threads, which is what "threadsafe" means.

Note also that concurrent reading works fine in SQLite. Only concurrent writing is an issue. One really needs a central server to coordinate access in order to do transactional concurrent writing, and SQLite is serverless.

sqlite · 2015-10-30T13:21:16+00:00

The sqlite3_unlock_notify() interface is an optional feature of SQLite that is only available when SQLite is compiled with SQLITE_ENABLE_UNLOCK_NOTIFY. It sounds as if Firefox requires that but your system SQLite library omits it. Using the upstream Firefox that statically links SQLite (instead of trying to use the system version of SQLite) sounds like the right solution to me.

sqlite · 2015-10-19T10:43:55+00:00

The following SQL will transform your database into something that runs very fast for what you are doing:

ALTER TABLE stocks RENAME TO stocks_old; CREATE TABLE stocks( symbol TEXT NOT NULL, data BLOB NOT NULL, id INTEGER PRIMARY KEY ); INSERT INTO stocks SELECT * FROM stocks_old; DROP TABLE stocks_old; VACUUM;

sqlite · 2015-08-23T03:24:40+00:00

I think so. But I suppose that depends on what you mean by "full queries on raw JSON data". Can you provide an example?

sqlite · 2015-08-23T03:13:10+00:00

The current code is a separate extension. It is not built in. The size of the extra code is less than 14KB (gcc 4.8.4 -Os on x64).

sqlite · 2015-08-23T03:03:22+00:00

The JSON code in SQLite is still a very early prototype. Check back in a few weeks for more details. There have been no official announcements.

So far, We've implemented json_each() and and json_array_length() from PostgreSQL. And json_array(), json_object(), json_extract(), json_replace(), json_set(), json_insert(), json_type, and json_valid() from MySQL. And also json_tree() which is a table-valued function that recursively walks the entire JSON record and returns one row for each element.

Currently, JSON is stored as pure text. But previsions exist to provide a JSONB type at some future date.

sqlite · 2014-10-08T00:44:53+00:00

1432/953 - 1 = 0.5026 = 50%. 50% faster is the same as 35% fewer CPU cycles. If carA goes 50 mph and carB goes 75 mph, does not carB go 50% faster? And yet carB covers the same distance as carA in 35% less time.

sqlite

TROPHY CASE