Hi,
I have noticed an issue where invalid SQL is generated for for the INSERT statement when a schema synchronization script is created. This occurs in the situation where the table is being re-created and new columns have been added (It may happen on other occasions as well but I haven't noticed).
In this situation the column list in the INSERT statement contains the columns from the 'new' re-created table, instead of the original table. This results in an incorrect number of columns supplied in the SELECT part of the query and the following error:
Error: near line 23: 1 values for 2 columns
The destination sync CREATE TABLE statement is:
-- WARNING: Re-creating the table
ALTER TABLE alarm_lockout_link_sets RENAME TO alarm_lockout_link_sets_tmp;
CREATE TABLE alarm_lockout_link_sets (link_set_id INTEGER NOT NULL,
link_set TEXT NOT NULL,
CONSTRAINT pk PRIMARY KEY (link_set_id));
INSERT INTO alarm_lockout_link_sets (link_set_id, link_set) SELECT link_set FROM alarm_lockout_link_sets_tmp;
DROP TABLE alarm_lockout_link_sets_tmp;
In the above statement the link_set_id column should not be included in the column list.
The original source CREATE TABLE statement is:
CREATE TABLE alarm_lockout_link_sets(
link_set_id INTEGER PRIMARY KEY NOT NULL,
link_set TEXT NOT NULL
);
The original destination CREATE TABLE statement is:
CREATE TABLE alarm_lockout_link_sets(
link_set TEXT PRIMARY KEY NOT NULL
);
The databases are SQLite. I am also working with MySQL but I have not tested that yet.
I can also see that the sync would fail (assuming the above error was already fixed) with an error like this:
Error: near line 1: table_name.some_field may not be NULL
if a column is added and defined like this:
some_field INTEGER NOT NULL
and no DEFAULT was added. Since it is not really possible for you to guess a sensible default value it might be a nice feature to present a warning during script creation so the user can go back and add a default value.