Is there a way to auto update contents durning synchronizing schema?

[alexlee002]

for example, there is a files table:

CREATE TABLE files (fid INTEGER PRIMARY KEY,  path TEXT,  name TEXT);

1. in version 1 I add a new column size:

ALTER TABLE files ADD COLUMN(size INTEGER);

2. in version 2, I combine the value from path and name AS the new value of path, and add a new column mtime:

UPDATE files SET path = path || '/' || name;
ALTER TABLE files ADD COLUMN(mtime DATETIME);

3. in version 3, I add an index on column path:

CREATE INDEX idx_path ON files (path);

The v1, v3 only change the schema, and can be upgrade automatically, but how to work together with version 2?

[fnc12]

two ways:

1. dump and free before calling sync_schema check storage.pragma.user_version and if it is equal 1 (and you need to switch it to 2) then call storage.update_all
2. nice and paid we (sqlite_orm) community have a paid version of sqlite_orm called sqlite_orm_plus. It has rich migration API which allows you performing any migrations without exceptions and losing data. More info here

[alexlee002]

Thanks！ The way use user_version is the way I was using many yeas ago（may orm lib using this way）. I read your pdf document, the way using migrator_1_2 with user_version and auto schema sync is my current way(but not the same way, I don't transform row by row -- the same way is using in WCDB many yeas ago -- and I don't think it's fast, the rows in my table maybe more than 1,000,000, and many tables in the db)， I'm trying to find a more automatically way.

[fnc12]

ok. To solve the issue related to sqlite_orm perfectly you'd better to understand how would you solve it with raw SQLite. How would you solve it with raw SQLite?

[fnc12]

closing due to inactivity. Feel free to reopen if something else appears