Skip to content

CLI equivalents to transform(add_foreign_keys=) #585

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
simonw opened this issue Aug 18, 2023 · 7 comments
Closed

CLI equivalents to transform(add_foreign_keys=) #585

simonw opened this issue Aug 18, 2023 · 7 comments
Labels
cli-tool enhancement New feature or request

Comments

@simonw
Copy link
Owner

simonw commented Aug 18, 2023

The new options added in:

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 simonw added enhancement New feature or request cli-tool labels Aug 18, 2023
@simonw
Copy link
Owner Author

simonw commented Aug 18, 2023

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
Copy link
Owner Author

simonw commented Aug 18, 2023

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
Copy link
Owner Author

simonw commented Aug 18, 2023

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 added a commit that referenced this issue Aug 18, 2023
@simonw
Remove unneccessary warning, refs #577, #585
70717dc
@simonw
Copy link
Owner Author

simonw commented Aug 18, 2023

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
Copy link
Owner Author

simonw commented Aug 18, 2023

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
Copy link
Owner Author

simonw commented Aug 18, 2023

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
Copy link
Owner Author

simonw commented Aug 18, 2023

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

@simonw simonw closed this as completed in 56093de Aug 18, 2023
simonw added a commit that referenced this issue Aug 18, 2023
@simonw
Release 3.35
87c6ceb 
Refs #577, #581, #585
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
cli-tool enhancement New feature or request
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@simonw