As a way of extending the support for data migration I have added some code

[juandent]

The order in which tables can be dropped is subject to certain restrictions: tables with dependent rows cannot be dropped.
To be able to drop tables with the certainty that we will drop them in correct order, we need an algorithm that "sorts" the types. I have implemented this algorithm. The idea is to use it like this:

		SQLCookbookDb db;
		auto vec0 = db.drop_table<0>(storage);
		auto vec1 = db.drop_table<1>(storage);
		auto vec2 = db.drop_table<2>(storage);
		auto vec3 = db.drop_table<3>(storage);
		auto vec4 = db.drop_table<4>(storage);

		storage.sync_schema();                          // dropping the tables allows for schema changes to be synchronized
                                                                              // as was carefully analyzed in the Guide which I have written

		db.replace_table(storage, vec4);
		db.replace_table(storage, vec3);
		db.replace_table(storage, vec2);
		db.replace_table(storage, vec1);
		db.replace_table(storage, vec0);

This behavior works like a stack and it would be nice to create a vector of vectors but how since each table represents a different type - so vec0 may be something like std::vector<Album> and vec1 something like std::vector<Artist>, etc, clearly impossible to create a stack of vectors... if there is a way to make this more generic please let me know... I don't like to hang my trust to the naming of the vectors (i.e. vec0, vec1,...). If there is a way please let me know!

I am using the CRTP to define a general Database template class which has a couple of extension points:

template<typename ConcreteDatabase>
struct Database
{
	Database()
	{
	}
	auto getDropOrder() const noexcept
	{
		return static_cast<const ConcreteDatabase*>(this)->getDropOrderImpl();
	}
	auto getInsertOrder() const noexcept
	{
		auto dropOrder = getDropOrder();
		return TableOrder::tuple_reverse<decltype(dropOrder)>::type();
	}
	template<size_t Pos, typename Storage>
	auto drop_table(Storage storage)
	{
		auto drop_order = getDropOrder();
		using Element = std::tuple_element_t<Pos, decltype(drop_order)>;
		std::vector<Element> vec;
		vec = storage.get_all<Element>();
		storage.drop_table(storage.tablename<Element>());
		return vec;
	}
	template<typename Storage, typename Vector>
	void replace_table(Storage storage, const Vector& vec)
	{
		storage.replace_range(vec.begin(), vec.end());
	}
	size_t getCountTables() const noexcept
	{
		return static_cast<const ConcreteDatabase*>(this)->getCountTablesImpl();
	}
};

which is common for all projects. A concrete class for each storage schema then follows:

struct SQLCookbookDb : public Database<SQLCookbookDb>
{
	using ListOfTables = std::tuple<Artist, Department, Album, Employee, EmpBonus>;
	auto getDropOrderImpl() const noexcept
	{
		auto newTuple = TableOrder::tuple_divide<Employee, Department, ListOfTables>::convert();
		auto newerTuple = TableOrder::tuple_divide<EmpBonus, Employee, decltype(newTuple)>::convert();
		auto newestTuple = TableOrder::tuple_divide<Album, Artist, decltype(newerTuple)>::convert();
		return newestTuple;
	}
	size_t getCountTablesImpl() const noexcept
	{
		return std::tuple_size_v<ListOfTables>;
	}
};

the name tuple_derived should be replaced by something more appropriate - it is a temporary name. The implementation of the ordering algorithm is as follows (this needs to be called for every pair of types that require some order between them):

	template<typename I, typename D, typename Tuple>
	struct tuple_divide
	{
	private:
		static constexpr int pos_i = find_in_tuple<I, 0, size, Tuple>::pos;
		static constexpr int pos_d = find_in_tuple<D, 0, size, Tuple>::pos;
                    // ......
	public:
		constexpr static auto convert()               // algorithm
		{
			Tuple t{};

			if constexpr (pos_i > pos_d)						// relative position of Tables is incorrect
			{
				auto t3 = tail<pos_i + 1>(t);					// get tail after i

				auto t2plus = tail<pos_d + 1>(t);				// get tail after d

				auto t2 = head<pos_i - pos_d - 1>(t2plus);		// get elements between d and i

				auto t1 = head<pos_d>(t);						// get elements before d

				auto pair = std::tuple<I, D>();					// create the insertion tuple

				auto mix = std::tuple_cat(t1, pair, t2, t3);	// concatenate tuples into final mix

				return mix;
			}
			else												// no changes needed, return Tuple instance
			{
				return t;
			}
		}

I would like to know what you think about this...
I will prepare .h files to package this physically. I suppose I can then upload the files for your review.

Thanks in advance,
Juan Dent

[juandent]

BTW, I am not suggesting that we drop all the tables on every execution of each of our applications. Only when there have been changes that are not updated (because sync_schema cannot detect a change for instance) or in cases where sync_schema would drop and loose data (see my Guide, data migration)...

[juandent]

I would like to upload the code files of the above mentioned extensions... what is the best way to do this?

[fnc12]

PR

[fnc12]

Actually there can be a schema where one cannot detect a strict sort order of tables cause table A can have a FK to table B and reverse simultaneously. Example: table books has a FK to category storing book's category and table category can has FK to books with the latest added book of this category. Such a schema can be created using external SQLite client, cannot be created using sync_schema but can be supported by sqlite_orm. Developer may want to alter such a schema with sqlite_orm. How would you do this?

[juandent]

Sqlite studio uses pragma foreign_keys 0 before dropping and creating tables and then resets it to 1 This is the reason why I think we should have access to this pragma Sync_schema would be run by shutting down foreign_keys to 0 doing the work, and then resetting to 1 Actually foreign_keys should be 0 when we start dropping tables and reset to 1 after all data is reloaded This should be done with RAII IDIOM to ensure the pragma is reset! That is the way one can create the schema in a SQL client but can also be created within the full data migration using sync_schema Cheers 🍻 Juan

…

Sent from my iPhone s to thi1

On 1 Apr 2022, at 10:02 PM, 1should Zakharov ***@***.***> wrote:  Actually there can be a schema where one cannot detect a strict sort order of tables cause table A can have a FK to table B and reverse simultaneously. Example: table books has a FK to category storing book's category and table category can has FK to books with the latest added book of this category. Such a schema can be created using external SQLite client, cannot be created using sync_schema but can be supported by sqlite_orm. Developer may want to alter such a schema with sqlite_orm. How would you do this? — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.

[juandent]

Since all inserting/updating is done with PRAGMA FOREIGN_KEY 1, we know the DB is in a valid referential integrity state before the sync_schema(), then turn off PRAGMA FOREIGN KEY, load the tables, drop them, reset the schema and reinsert the tables, resetting FOREIGN_KEY to 1
This is the only place where we touch this PRAGMA...

[juandent]

this is how SQLITESTUDIO does the schema changes!!

[juandent]

For instance, I added a column to an existing table and this is the query to be executed in sqlitestudio:

PRAGMA foreign_keys = 0;

CREATE TABLE sqlitestudio_temp_table AS SELECT *
                                          FROM employees_bk;

DROP TABLE employees_bk;

CREATE TABLE employees_bk (
    EmployeeId INTEGER       PRIMARY KEY AUTOINCREMENT
                             NOT NULL,
    LastName   NVARCHAR (20) NOT NULL,
    FirstName  NVARCHAR (20) NOT NULL,
    Title      NVARCHAR (30),
    ReportsTo  INTEGER,
    BirthDate  DATETIME,
    HireDate   DATETIME,
    Address    NVARCHAR (70),
    City       NVARCHAR (40),
    State      NVARCHAR (40),
    Country    NVARCHAR (40),
    PostalCode NVARCHAR (10),
    Phone      NVARCHAR (24),
    Fax        NVARCHAR (24),
    Email      NVARCHAR (60),
    country    TEXT          DEFAULT unknown,
    FOREIGN KEY (
        ReportsTo
    )
    REFERENCES employees_bk (EmployeeId) ON DELETE NO ACTION
                                         ON UPDATE NO ACTION
);

INSERT INTO employees_bk (
                             EmployeeId,
                             LastName,
                             FirstName,
                             Title,
                             ReportsTo,
                             BirthDate,
                             HireDate,
                             Address,
                             City,
                             State,
                             Country,
                             PostalCode,
                             Phone,
                             Fax,
                             Email
                         )
                         SELECT EmployeeId,
                                LastName,
                                FirstName,
                                Title,
                                ReportsTo,
                                BirthDate,
                                HireDate,
                                Address,
                                City,
                                State,
                                Country,
                                PostalCode,
                                Phone,
                                Fax,
                                Email
                           FROM sqlitestudio_temp_table;

DROP TABLE sqlitestudio_temp_table;

PRAGMA foreign_keys = 1;

See? no need to worry about foreign keys!!! Can drop any table by itself!! Thanks to foreign_keys == 0 during the operation

[fnc12]

Why sqlitestudio does this weird combination of statements instead of calling ALTER TABLE ... ADD COLUMN...?

[juandent]

I don't know why but it fully creates the table anew which might be what some schema changes require... its probably the safest route although not the more efficient ... don't know really, but the point is that foreign_keys are set to 0 while the saving the data, dropping, recreating the table and reloading all data

[juandent]

It is necessary to disable foreign_key checking else the insert will not work in the general case where the row with the reportsto FK is not inserted before the current row!!
How else?
BTW, this makes it unnecessary to drop the tables in any specific order!!

[fnc12]

Not sure. It looks like a huge feature with huge amount of test cases

[juandent]

that is what i was trying to avoid --- instead having one object to encapsulate the FK checking is so simple -- the programmer is responsible for its correct use

[fnc12]

The only argument is that your solutions is quick to implement? What I see is that the quickest is not always the best

[juandent]

my argument is that its not that dangerous and yes that it is quick to implement. With so many features on our table such as views, we hardly have the luxury to wait for perfect solutions.. I may be wrong, I will agree with whatever you think its best: you are the parent of the baby and it is noble to want it to be as perfect as possible

[fnc12]

if we merge a feature like one object to encapsulate the FK checking then we'll be unable to unmerge after the next release if we want because we must keep backward compatibility

[fnc12]

@juandent please check out sync-schema-fk-modification branch - it disables PRAGMA foreign_keys in the beginning of sync_schema and enables in the end

[juandent]

this is not enough Eugene!!

[fnc12]

what is your idea instead?

[juandent]

see above please

[fnc12]

Let's concentrate on advanced migrations API. It will solve both cyclic FKs problem and FK enabled/disabled problem