Comments (7)
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.
from sqlite-utils.
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
from sqlite-utils.
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.
from sqlite-utils.
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.
from sqlite-utils.
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
from sqlite-utils.
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])
)
from sqlite-utils.
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];
from sqlite-utils.
Related Issues (20)
- Mechanism for de-registering registered SQL functions HOT 3
- Ability to tell if a Database is an in-memory one HOT 1
- `table.transform()` should preserve `rowid` values HOT 6
- Represent compound foreign keys in table.foreign_keys output HOT 2
- Cascading DELETE not working with Table.delete(pk) HOT 1
- Feature request: sqlite-utils insert-files should be able to convert fields
- Cannot find spatialite on arm64 linux HOT 1
- Move plugin directory into documentation
- `sqlite-utils transform` removes the `AUTOINCREMENT` keyword
- Pyhton 3.12 Bug report HOT 2
- Insert fails with `Error: Python int too large to convert to SQLite INTEGER`; can we use `NUMERIC` here? HOT 1
- str and int as aliases for text and integer HOT 2
- pyright and mypy showing access error messages for basic sqlite-utils usage
- Attached database tables representable by `Table`
- `table.upsert/upsert_all` fails to write row when `not_null` is absent and the schema definition includes `not_null` HOT 3
- `.transform()` effect on triggers and indices
- Automatic JSON de-serialization
- Windows: Correct way to initialize spatialite?
- sqlite-utils command fails when used with xargs HOT 2
- Drop support for Python 3.7 HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlite-utils.