SQLite Forum

Anton Dyachenko
>Please explain what you mean by 'migration'

Migration is either (or both) change in the schema of one or more tables or changing data in one or more tables, needed when our app introduces new functionality.

>What does your software do for each 'migration' ?

the workflow in our app for any created connection is:

2. setting pragmas journal_mode persist, foreign_keys = ON
3. setting some handlers like bussy handler, commit hook, custom regexp function
4. reading database metadata like version and uuid stored in a one-row table
5. check if the app latest expected db version higher than the version stored in the db

migration-related workflow (when app version is higher than db version). We use three numbers for encoding version major/minor/patch. Major number changes when we are going to break forward compatibility (new db won't work in old app version), minor version changes when we introduced any table schema change, and patch changes when only data change is needed:

1. app searches for all registered migrations needed to be applied to the db based on the current db version, each migration provides pair of (fromVersion, toVersion) metadata
2. for each migration a new transaction is started mostly to isolate each migration from others
3. we run all migrations consequently until db version reach the current app version

after db is fully migrated to the current app version it is registered to be used for the rest of the app logic.

>Is the database stored on a disk in the computer doing the processing, or is it accessed across a network ?

my app never does migration for a remote db, only local db can be migrated, and the app doesn't use conventional OS facilities for accessing db over the network. Our VFS is developed specifically to access db over the network.

>Do you have more than one connection accessing the same database at the same time ?

yes, this is possible in the app, but this is not the case for the unit test. For unit tests we use gtest and this one is 100% single-threaded, however, some other unit tests can be multithreaded.

>Do you have more than one thread using the same connection at the same time ?

no, this is prohibited/handled via connection pool. Whenever any piece of app wants to work with a db it asks for a free connection from the pool, when it is done it returns the connection back to the pool, with some cleaning routings. So any connection can be used only by one thread at a time but by many threads during a lifetime.

>Having a table and a view with the same name has proved to cause problems in the past. Can you rewrite some of your code to avoid it ?

In version 1.x.0 there was created a view XXX, then in version 2.a.0 we have introduced a new table with "internal"/temporary/pre-release name YYY, then in 2.a+n.0 version we have removed the XXX view and renamed the YYY table with "internal" name YYY to the same name that the view had XXX.

Yes, surely I can change the table name but it is kind of a last resort. The codebase of my app has a kind of ORM for pure people which provides some metadata about db schema like table and column names which then are used in the c++ code that dynamically generates queries - I think you can imagine what kind of unreadable nightmare it is. Since 2.x version, all queries that can be hardcoded now are hardcoded so there are lots of queries where this table name is hardcoded now. I have already changed it once from internal YYY to release name XXX so it is definitely possible to change it the second time but last time it caused serial of bugs, in other words from a QA point of view this is a VERY risky change that would require full regression of my app.

>When you access the database with a database browser for diagnosing your problem, are you using that same VFS ?

no, I always use vanilla db browser without committing changes, specifically to narrow down any potential issues related to our VFS

>If not, does your custom VFS understand the locking that the other VFS might be using to access the same database ?

our VFS implements sqlite locking protocol internally/in memory and doesn't expose outside any data that can be used to play nicely with other VFS and doesn't respect any other VFS locking. So this means that when I open a db in db browser simultaneously with my app accessing the same db then db browser can't know if it is safe to commit changes or not, that is why I never commit changes from db browser, but this is good enough for debugging purposes.

our app with this VFS exists for more than 7 years now, so apart from flush/sync I am not aware of any other issue related to VFS.

I believe that before my recent change the unit test has used either the same connection that performs migration or a connection that was created AFTER migration of a db. Now with my changes, I believe that unit test uses a connection that is created BEFORE migration starts. 

The test is the first who ask for a connection from the pool (which is empty initially). The pool creates one and returns it to the test, at this point the db has a view with the name XXX. Then migration code (in the same thread) asks for another connection from the pool which in turn creates a new one. Migration code performs all migrations in the second connection and returns it to the pool. At this point, the db doesn't have a view XXX but has a table with the same name XXX. Then the test continues with the first connection. 

I assume this first connection(used by test) doesn't refresh the cached schema, but when I have added vacuum after each migration's transaction the connection used by the test sees the most recent schema and test passes.