A table that provides primary keys used in another tables's foreign keys cannot be dropped unless PRAGMA foreign_keys = 0

[juandent]

Hi,

On the topic of migration, I have found something useful to use together with sync_schema(true);
Say we have 2 tables Job and User and User has a foreign key into Job. If we make substantial changes to Job specially like adding/removing a CHECK or UNIQUE, then just executing sync_schema(true) does not change the table. We are forced to drop it and recreate it. However since PRAGMA foreign_keys == 1, the drop_table fails (throws).
Therefore here is my recipe for a solution:

    storage.on_open = [](sqlite3* p) {
        internal::perform_void_exec(p, "PRAGMA foreign_keys = 0");
    };

    std::vector<Job> jobs = storage.get_all<Job>();
    storage.drop_table("job");

        auto m = storage.sync_schema(true);
        std::ostringstream oss;
        for (auto& n : m) {
            oss << n.first << " " << n.second << "\t";
        }
        auto s = oss.str();
        std::ignore = s;
        storage.replace_range(jobs.begin(), jobs.end());
    storage.on_open = [](sqlite3* p) {
        internal::perform_void_exec(p, "PRAGMA foreign_keys = 1");
    };

This works!!

Regards,
Juan Dent

[juandent]

I propose that #pragma foreign_keys be exposed through storage.pragma.foreign_keys of storage type. Yes it is delicate but necessary for easier migrations

[fnc12]

Yes it works but you use not a proper way. The proper way is to drop both tables. Because PRAGMA foreign_key must not be controlled by user. It is enabled automatically when at least one FK exists and it is right. If user has opportunity to disable it then one day he/she will face a case when FK are disabled and row was dropped accidentally with breaking references. If we add breaking references opportunity then we'll stop guarantee that storage keeps PRAGMA foreign_keys enabled when you need it.

[juandent]

during sync_schema() the order of dropping tables may be incorrect and a table with dependency rows may be dropped which triggers an exception if foreign_keys == 1 thus making it impossible to synchronize schema... it happened to me in one of my tests..
I agree with you, but maybe during sync_schema() foreign_keys may be set to 0 and then restored to 1
And have it in 1 always except in sync_schema...

What do you think?

[juandent]

one thing still bothers me:

If this is my make_storage function:

   static auto storage = make_storage(dbFilePath,
        make_unique_index("name_unique.idx", &User::name ),
        make_table("user_table",
            make_column("id", &User::id, primary_key()),
            make_column("name", &User::name),
            make_column("born", &User::born),
            make_column("job", &User::job),
            check(length(&User::name) > 2),
            foreign_key(&User::job).references(&Job::id)),
        make_table("job", 
            make_column("id", &Job::id, primary_key(), autoincrement()),
            make_column("name", &Job::name, unique()),
            make_column("base_salary", &Job::base_salary)));

then say we need to drop both tables, and this is done in the order they are declared... so user_table is dropped first, and it may be recreated at once but not filled with data, otherwise when se drop job there are dependent rows is user_table. So the order of actions in sync_schema must be like this:

1. make backup of user_table into tmp1
   2 drop user_table
2. make backup of job into tmp2
3. drop job
4. create job
5. insert into job all data from tmp2
6. create user_table
7. insert into user_table all data from tmp1

does it work this way? or does it drop/recreate/fill each table in isolation of the rest?

[fnc12]

it works in isolation

[juandent]

then it won't work

[juandent]

so in such severe cases go back to manual migration ...

[fnc12]

it is a corner case. It is a reason of developing more advanced migrations API

[juandent]

or else each table may be tested with has_dependent_rows() and not dropped until we have dropped the dependent tables.... don't know seems like complicated algorithm - what do you think?

[fnc12]

has_dependent_rows can be called at any time by user so I don't see any problem here. Also one can just skip using FK just because of these problems and because it increases runtime overhead