CLI equivalents to transform(add_foreign_keys=)

[simonw]

The new options added in:

• Get add_foreign_keys() to work without modifying sqlite_master #577
  Deserve consideration in the CLI as well.

sqlite-utils/sqlite_utils/db.py

Lines 1706 to 1708 in d2bcdc0

	drop_foreign_keys: Optional[Iterable[str]] = None,
	add_foreign_keys: Optional[ForeignKeysType] = None,
	foreign_keys: Optional[ForeignKeysType] = None,

[simonw]

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.

[simonw]

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

[simonw]

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.

[simonw]

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.

[simonw]

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

[simonw]

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])
)

[simonw]

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];