add column sync_schema will recreate table
frankz61 opened this issue · 12 comments
in my table create sql, primary key just set pk and autoincremnet.
when i use sync_schema to add new column.
table droped and recreated.
i debuged the calculate_remove_add_columns, catch the problem with the pk column.
the storage pk column info notnull is true
the db pk column info notnull is false
it makes next result to the drop&recreated table.
i make sure the pk column of db struct info not null is false.
but older db file cant be compatible this problem.
i just modified the code, set the db pk col to not null.
if (dbColumnInfo.pk) { dbColumnInfo.notnull = true; }
please show me your C++ struct mapped to the storage
this is struct and mapped code.
&Task::id can't be NULL cause it is int - int can't be NULL. That's why sync_schema tries to make task_id column non-nullable - it is correct behavior. If you still want to call sync_schema and avoid table recreating replace int id with something nullable like std::optional<int> id
In SQLite, it is possible to create a primary key column without explicitly declaring it as NOT NULL. However, in other databases, defining a column as a primary key automatically sets it to NOT NULL.
i used msvc2017. it not support std::optional very well.
In SQLite, it is possible to create a primary key column without explicitly declaring it as NOT NULL. However, in other databases, defining a column as a primary key automatically sets it to NOT NULL.
If PRAGMA table_info returns notnull as 0 then it can be NULL. This is how the engine works not sqlite_orm.
i used msvc2017. it not support std::optional very well.
Ok you can try the other way: add migration code which will extract all records from task table, call sync_schema and put the data back again. You can do it manually with a lot of pain or you can purchase sqlite_orm_plus which is sqlite_orm with migrations API added
Okay, the sync_schema(true) function will recreate the table and re-import the data into the database.
However, I've noticed that in the table_info, the primary key (PK) column has its 'notnull' attribute set to 0.
This is quite confusing.
I believe that not check a 'notnull' constraint on the PK column could be a viable solution.
I believe that not check a 'notnull' constraint on the PK column could be a viable solution.
What do you mean?
In the calculate_remove_add_columns function, when a column is identified as a primary key (PK), ensure that both the storage and database settings for 'not null' are set to true.
@SevenFantastic notnull is defined by SQLite. There is no reason to override it in sqlite_orm
This is indeed correct and represents a specific issue with SQLite.
The solution is to make the SQL creation process more sensitive and to use it with increased caution.
but the most compatible migration strategy often involves adding columns rather than recreating tables.
anyway it is not a bug. Closing