Table renaming: db.rename_table() and sqlite-utils rename-table

[simonw]

I find myself wanting two new features in sqlite-utils:

• The ability to have the new transformed table set to a specific name, while keeping the old table around
• The ability to rename a table (sqlite-utils doesn't have a table rename function at all right now)

Originally posted by @simonw in https://github.com/simonw/llm/issues/65#issuecomment-1618375042

[simonw]

For the CLI:

sqlite-utils rename-table data.db old_table_name new_table_name

For the Python code, should it go on Table or on Database?

db["foo"].rename_table("bar")

db.rename_table("foo", "bar")

I think I like the second better, it's slightly more clear.

Also need a design for an option for the .transform() method to indicate that the new table should be created with a new name without dropping the old one.

[simonw]

Trying out a simple first implementation:

>>> from sqlite_utils import Database
>>> db = Database(memory=True, tracer=print)
PRAGMA recursive_triggers=on; None
>>> db["foo"].insert({"id": 1})
select name from sqlite_master where type = 'view' None
select name from sqlite_master where type = 'table' None
select name from sqlite_master where type = 'view' None
select name from sqlite_master where type = 'table' None
select name from sqlite_master where type = 'view' None
CREATE TABLE [foo] (
   [id] INTEGER
);
         None
select name from sqlite_master where type = 'view' None
INSERT INTO [foo] ([id]) VALUES (?); [1]
select name from sqlite_master where type = 'table' None
select name from sqlite_master where type = 'table' None
PRAGMA table_info([foo]) None
<Table foo (id)>
>>> db.rename_table("foo", "baz")
ALTER TABLE [foo] RENAME TO [baz] None
>>> print(db.schema)
select sql from sqlite_master where sql is not null None
CREATE TABLE "baz" (
   [id] INTEGER
);

[simonw]

Also need a design for an option for the .transform() method to indicate that the new table should be created with a new name without dropping the old one.

I think keep_table="name_of_table" is good for this.

[simonw]

Manually testing new rename-table command:

$ sqlite-utils schema /tmp/f.db 
CREATE TABLE [foo] (
   [id] INTEGER,
   [name] TEXT
);
$ sqlite-utils rename-table /tmp/f.db bad-table hi
Error: Table "bad-table" could not be renamed. no such table: bad-table
$ sqlite-utils rename-table /tmp/f.db foo foo     
Error: Table "foo" could not be renamed. there is already another table or index with this name: foo
$ sqlite-utils rename-table /tmp/f.db foo bar
$ sqlite-utils schema /tmp/f.db                   
CREATE TABLE "bar" (
   [id] INTEGER,
   [name] TEXT
);

[simonw]

Python method documentation:

• https://sqlite-utils.datasette.io/en/latest/reference.html#sqlite_utils.db.Database.rename_table
• https://sqlite-utils.datasette.io/en/latest/python-api.html#renaming-a-table

[simonw]

CLI documentation:

• https://sqlite-utils.datasette.io/en/latest/cli.html#renaming-a-table
• https://sqlite-utils.datasette.io/en/latest/cli-reference.html#rename-table