groue

Gentium is a very beautiful font: https://software.sil.org/gentium/

You are not your code.

Yes. GRDB encourages Codable because the user can profit from the code generated by the compiler, and this implies that database values are accessed by column name, on top of the Codable runtime, and those layers have a high cost. When necessary it is possible to access database values by position, and in this case GRDB achieves speed of light (performance nearly identical as raw SQLite).

You're welcome :)

Thank you (GRDB author here).

It is not mentioned in the README of the repository, but SQLiteData wraps GRDB to access the database and get notified of database changes (the meat and butter).

GRDB is by itself a solid "toolkit for SQLite databases, with a focus on application development", with both high levels APIs for everyday coding, and expert SQLite features for the demanding developers. Many apps rely on GRDB alone.

Out of curiosity, I asked on the SQLite forum how the query optimizer fits in this schema: https://sqlite.org/forum/forumpost/93f9bfcec0

I do, for JSON columns. I store UTF8 strings in SQLite, so that it is easy to see JSON values with a plain `SELECT *`). And I load blobs, because I code in Swift and the standard JSON decoder eats raw UTF8 memory buffers, not strings.

This avoids two useless conversions:

- first from a C string loaded from SQLite to a Swift Unicode string (with UTF8 validation).

- next from this Swift Unicode string to a UTF8 memory buffer (so that JSONDecoder can do its job).

SQLite is smart enough to strip the trailing \0 when you load a blob from a string stored in the database :-)

Yes. Now, many short writes look exactly as one very long write from the point of view of an enqueued write that is waiting for its turn :-) I don't quite remember how fair is SQLite scheduling, in practice.

What's cool with WAL mode is that SQLITE_BUSY won't happen for readers (except very rare scenarios: https://www.sqlite.org/wal.html#sometimes_queries_return_sql...)

One should only expect SQLITE_BUSY for writes (if a writer is already holding the lock, and the busy timeout expires before the other writer releases the lock). So yes, prefer short writes, or adjust your timeout. Generally speaking, SQLITE_BUSY can not be 100% prevented for writes.

It is always safe, and by "safe" I mean "safe for data". You won't have to deal with data corruption. Precisely, see "How To Corrupt An SQLite Database File": https://www.sqlite.org/howtocorrupt.html

Now concurrent accesses from different processes/connections can lead to runtime errors (SQLITE_BUSY), because the database happens to be locked by one connection.

Those errors are greatly reduced by the WAL mode (https://sqlite.org/wal.html) which provides ultra-robust single-writer/multiple-readers semantics:

- Writes can not happen concurrently (SQLITE_BUSY).

- One can reduce the occurrences of such SQLITE_BUSY errors by using a built-in timeout (https://www.sqlite.org/c3ref/busy_timeout.html).

- Several reads can happen concurrently, including with writers.

- A writer connection can enter the "Serializable" isolation level.

- A reader connection can enter the "Snapshot Isolation" level.

For more details, see https://www.sqlite.org/isolation.html

During all the years I've been developing the GRDB library (https://github.com/groue/GRDB.swift), I could never see SQLite fail its documented guarantees. This made it possible to build one of the most concurrency-focused SQLite toolkit for Swift, and I'm pretty happy with it (https://github.com/groue/GRDB.swift/blob/master/Documentatio...).

The music is quite good! The covers of Freddie Freeloader and Flamenco Sketches are quite worth it, IMHO :-)

Library author here - SQLite concurrency needs a little care: don't miss the Concurrency Guide: https://github.com/groue/GRDB.swift/blob/master/Documentatio...

You can try https://github.com/groue/GRDB.swift

It's a thin wrapper around SQLite, with convenience APIs for record types, robust support for concurrency, and a focus on GUI apps.

I often promise myself to have a look at SQLite virtual file system API, for this very purpose, but could not yet find the guts :-)

We start to see ORMs like Diesel (https://diesel.rs) or GRDB (http://github.com/groue/GRDB.swift) that radically simplify their domain by removing traditional ORM features like implicit uniquing, lazy-loading, or auto-updates. This of course gives more work to do on the client side. But this work is positive and often simpler than expected. With traditional ORMs, there is still some work to do, which often looks like fights: against misunderstandings, against leaky abstractions, against configuration levels that don't match the needs of application, and generally speaking against the framework.

ORMs are not done yet :-)

Related article: "Automatic Undo/Redo Using SQLite" https://www.sqlite.org/undoredo.html

There are ORMs that address partial records, multi-threading troubles brought by lazy loading, uniquing, auto-updating records, and, importantly, put raw SQL on the same level as the query builder. I think of [Diesel](http://diesel.rs) and [GRDB.swift](https://github.com/groue/GRDB.swift/blob/master/Documentatio...).

I agree. Sometimes, I explain that the feature request can be implemented, but not for free. I flag such issues with the "needs sponsoring" tag.

Unique to GRDB.swift, as far as I know, is the ability to perform database writes and then wait until snapshot isolation has been reached in another connection before releasing the writing lock. This allows to read from a known database state, while allowing other writes to be performed. The ability to read for a precisely known database state allows nice features like database observation and reactive patterns, while taking advantage from the WAL mode by not holding locks longer than necessary.

I'd also like to thank the other for his attempt at raising the general awareness of transaction isolation. Even when you deal with simpler databases like SQLite, isolation is a serious topic. When serializing accesses hinders performances, dealing with the "WAL mode" that grants SQLite with single-writer-multipler-readers abilities is everything but trivial, because of the huge amount of documentation that has to be read in order to reach the desired level of isolation. I've written an SQLite library in Swift that provides by default the snapshot isolation level which grants most developers with both peace of mind, and non-blocking reads. Check out its "concurrency guide" if you're interested: https://github.com/groue/GRDB.swift/blob/master/README.md#co...

They sound similar to change data capture in SQLServer.

Yes. There is a difference, though: SQLite is embedded, and there is zero latency between an application and its database.

This means that change notifications can be emitted synchronously right after a committed transaction, allowing the application to process the change before any other change could be performed.

This makes it much easier to get a very robust solution for a whole class of database synchronization problems.

I didn't know about windowing function. This page looks like a nice description: https://community.modeanalytics.com/sql/tutorial/sql-window-...

Sure, and French don't have a word for "entrepreneur" neither

External librairies can also support the migration by providing "fix-its" which helps the compiler display an helpful message whenever a Swift 2-era line has not been updated to Swift 3 (see for example this PR: https://github.com/groue/GRDB.swift/pull/114)

Simplest way would be to embed public key in the application

Yes, but one eventually faces the need to update the certificate on the server, and still support installed applications (which fails when apps have an obsolete certificate pinned). Hence the need for smooth certificate updates, and the good smell of HPKP. But it addresses another need, hence my confusion.

Mobile apps typically have other key pinning mechanisms that are preloaded (i.e. baked into the binary), but that's typically easy to bypass if you're the owner of a device; it's not really effective as a mechanism against reverse engineering.

Your comment makes me realize that HPKP can bootstrap itself, and is unlike regular pinning (with certificate bundled in the binary, as you say).

So do you agree that a powerful enough owner of the device should be considered able to setup a server which poses as the regular server the app talks to, and sniff any request the app sends to its server?

(edited for spelling)

Yes, like a rogue one installed through any jailbreaking technique, isn't it?

May I ask a stupid question? Last time I heard about HPKP was as a solution to prevent an attacker from installing a fake root CA certificate in the client system (a mobile device), so that it could observe the behavior of a mobile app, by posing as its API server (for any purpose of reverse-engineering). Is such an attack possible, and HPKP a reasonable solution to it?

Fine, as long as you can produce replacement data with the desired qualities.

After one year in the making, GRDB.swift has acquired experience about the impact of protocol-oriented programming and immutability on the way applications can access SQLite databases. First, those new paradigms actually help avoiding some hard-to-reproduce concurrency bugs. Next, they are fundamentally incompatible with frameworks like Core Data and Realm, which rely heavily on both class inheritance and mutability. This has consequences on application architecture.