Closed simonw closed 11 months ago
Currently:
sqlite-utils transform --help
Usage: sqlite-utils transform [OPTIONS] PATH TABLE
Transform a table beyond the capabilities of ALTER TABLE
Example:
sqlite-utils transform mydb.db mytable \
--drop column1 \
--rename column2 column_renamed
Options:
--type <TEXT CHOICE>... Change column type to INTEGER, TEXT, FLOAT or BLOB
--drop TEXT Drop this column
--rename <TEXT TEXT>... Rename this column to X
-o, --column-order TEXT Reorder columns
--not-null TEXT Set this column to NOT NULL
--not-null-false TEXT Remove NOT NULL from this column
--pk TEXT Make this column the primary key
--pk-none Remove primary key (convert to rowid table)
--default <TEXT TEXT>... Set default value for this column
--default-none TEXT Remove default from this column
--drop-foreign-key TEXT Drop foreign key constraint for this column
--sql Output SQL without executing it
--load-extension TEXT Path to SQLite extension, with optional
:entrypoint
-h, --help Show this message and exit.
Probably most relevant here is this snippet from:
sqlite-utils create-table --help
--default <TEXT TEXT>... Default value that should be set for a column
--fk <TEXT TEXT TEXT>... Column, other table, other column to set as a
foreign key
The only CLI feature that supports providing just the column name appears to be this:
sqlite-utils add-foreign-key --help
Usage: sqlite-utils add-foreign-key [OPTIONS] PATH TABLE COLUMN [OTHER_TABLE]
[OTHER_COLUMN]
Add a new foreign key constraint to an existing table
Example:
sqlite-utils add-foreign-key my.db books author_id authors id
WARNING: Could corrupt your database! Back up your database file first.
I can drop that WARNING now since I'm not writing to sqlite_master
any more.
I'm not going to implement the foreign_keys=
option that entirely replaces existing foreign keys - I'll just do a --add-foreign-key
multi-option.
Help can now look like this:
--drop-foreign-key TEXT Drop foreign key constraint for this column
--add-foreign-key <TEXT TEXT TEXT>...
Add a foreign key constraint from a column
to another table with another column
Some manual testing:
sqlite-utils create-table /tmp/t.db places id integer name text country integer city integer continent integer --pk id
sqlite-utils schema /tmp/t.db
CREATE TABLE [places] (
[id] INTEGER PRIMARY KEY,
[name] TEXT,
[country] INTEGER,
[city] INTEGER,
[continent] INTEGER
);
sqlite-utils create-table /tmp/t.db country id integer name text
sqlite-utils create-table /tmp/t.db city id integer name text
sqlite-utils create-table /tmp/t.db continent id integer name text
sqlite-utils schema /tmp/t.db
CREATE TABLE [places] (
[id] INTEGER PRIMARY KEY,
[name] TEXT,
[country] INTEGER,
[city] INTEGER,
[continent] INTEGER
);
CREATE TABLE [country] (
[id] INTEGER,
[name] TEXT
);
CREATE TABLE [city] (
[id] INTEGER,
[name] TEXT
);
CREATE TABLE [continent] (
[id] INTEGER,
[name] TEXT
);
sqlite-utils transform /tmp/t.db places --add-foreign-key country country id --add-foreign-key continent continent id
sqlite-utils schema /tmp/t.db
CREATE TABLE [country] (
[id] INTEGER,
[name] TEXT
);
CREATE TABLE [city] (
[id] INTEGER,
[name] TEXT
);
CREATE TABLE [continent] (
[id] INTEGER,
[name] TEXT
);
CREATE TABLE "places" (
[id] INTEGER PRIMARY KEY,
[name] TEXT,
[country] INTEGER REFERENCES [country]([id]),
[city] INTEGER,
[continent] INTEGER REFERENCES [continent]([id])
);
sqlite-utils transform /tmp/t.db places --drop-foreign-key country
sqlite-utils schema /tmp/t.db places
CREATE TABLE "places" (
[id] INTEGER PRIMARY KEY,
[name] TEXT,
[country] INTEGER,
[city] INTEGER,
[continent] INTEGER REFERENCES [continent]([id])
)
And a test of the --sql
option:
sqlite-utils create-table /tmp/t.db places id integer name text country integer city integer continent integer --pk id
sqlite-utils create-table /tmp/t.db country id integer name text
sqlite-utils create-table /tmp/t.db city id integer name text
sqlite-utils create-table /tmp/t.db continent id integer name text
sqlite-utils transform /tmp/t.db places --add-foreign-key country country id --add-foreign-key continent continent id --sql
Outputs:
CREATE TABLE [places_new_6a705d2f5a13] (
[id] INTEGER PRIMARY KEY,
[name] TEXT,
[country] INTEGER REFERENCES [country]([id]),
[city] INTEGER,
[continent] INTEGER REFERENCES [continent]([id])
);
INSERT INTO [places_new_6a705d2f5a13] ([id], [name], [country], [city], [continent])
SELECT [id], [name], [country], [city], [continent] FROM [places];
DROP TABLE [places];
ALTER TABLE [places_new_6a705d2f5a13] RENAME TO [places];
The new options added in:
577
Deserve consideration in the CLI as well.
https://github.com/simonw/sqlite-utils/blob/d2bcdc00c6ecc01a6e8135e775ffdb87572b802b/sqlite_utils/db.py#L1706-L1708