Databases
sql.sqlite.ddl
import sql.sqlite.ddl
Zuri
sqldoes not re-export this module, so it is reached only by importing it directly.
Schema changes for SQLite.
Creating a table, adding an index and renaming a column are plain
statements. Most other changes to an existing table aren't. SQLite's
ALTER TABLE can't change a column, add or drop a key, or drop a column
that a key or an index uses. For those changes, the table is rebuilt,
following the steps in SQLite's own documentation. A new table is
created with the changed definition, the rows are copied across, the old
table is dropped and the new one is renamed to take its place. Then its
indexes and triggers are created again, and the sequence behind an
id() is carried over, so that no ID is ever reused.
The new definition is the old one with only the changed parts rewritten,
based on the table's own create table statement, so everything else
the table declared, including checks and collations, is kept as it was.
Foreign keys
Dropping a table that other tables refer to deletes its rows first, and
if foreign keys are enforced, that runs their on delete actions, so
rebuilding a table would also delete every row that refers to it. A
rebuild therefore only runs with foreign key enforcement turned off,
which SQLite only allows outside a transaction. schema_change() does
exactly that around every change it runs, sql.migrate runs each
migration through it, and the foreign keys are checked before the change
is committed. A rebuild requested inside a transaction while foreign
keys are enforced raises a NotSupportedError instead of putting the
rows at risk.
Constants
MAX_DECIMAL_PRECISION
sql.sqlite.ddl.MAX_DECIMAL_PRECISION = 15
ZuriThe most significant digits a decimal() holds exactly here. SQLite
keeps a decimal as a double, which carries fifteen.
Classes
SqliteDdl
class sql.sqlite.ddl.SqliteDdl < DdlAdapter
ZuriTurns blueprints into SQLite's statements.
SqliteDdl.type_name()
sql.sqlite.ddl.SqliteDdl.type_name(column) -> string
ZuriParameters
column(Column)
Returns string
SqliteDdl.key_definition()
sql.sqlite.ddl.SqliteDdl.key_definition(column) -> string
Zuriinteger primary key makes the column the table's rowid, and
autoincrement keeps a deleted row's id from being reused.
Parameters
column(Column)
Returns string
SqliteDdl.boolean_literal()
sql.sqlite.ddl.SqliteDdl.boolean_literal(value: bool) -> string
ZuriParameters
value(bool)
Returns string
SqliteDdl.current_default()
sql.sqlite.ddl.SqliteDdl.current_default(column) -> string
ZuriThe moment of the insert, written in the same form the adapter writes a
date.Date, so rows the database stamped and rows a program stamped
sort and compare as the times they are.
Parameters
column(Column)
Returns string
SqliteDdl.check_column()
sql.sqlite.ddl.SqliteDdl.check_column(column)
ZuriParameters
column(Column)
Raises NotSupportedError for a decimal past fifteen digits.
SqliteDdl.alter()
sql.sqlite.ddl.SqliteDdl.alter(connection, blueprint, operation)
ZuriCarries out one change, as a plain statement where SQLite has one and by building the table again where it does not.
Parameters
connection(Connection)blueprint(Blueprint)operation(dict)
SqliteDdl.drop_table()
sql.sqlite.ddl.SqliteDdl.drop_table(connection, table: string, options: dict)
ZuriDrops table, refusing while another table refers to it.
SQLite would otherwise delete the table's rows first, and with foreign
keys enforced, run every on delete that refers to them.
Parameters
connection(Connection)table(string)options(dict) —{ if_exists }.
SqliteDdl.schema_change()
sql.sqlite.ddl.SqliteDdl.schema_change(connection, body: function, transactional: bool) -> any
ZuriRuns body with foreign key enforcement turned off, inside a
transaction that takes the write lock immediately if transactional is
true, and checks every foreign key before committing.
Enforcement is restored to its previous setting however body ends. If
enforcement was already off, nothing is changed and nothing is checked.
Parameters
connection(Connection) — A connection that isn't already in a transaction.body(function)transactional(bool)
Returns any — Whatever body returned.
Raises ForeignKeyViolation if the change left a row referring to a
row that doesn't exist. The change is rolled back.
TableDefinition
class sql.sqlite.ddl.TableDefinition
ZuriA table's create table statement, split into its columns and
constraints so that a rebuild can rewrite only what changes.
Each item keeps the text it was written with, and only the items that a change affects are replaced. That way, everything that the blueprint doesn't know about comes through a rebuild unchanged.
Fields
| Field | Type | Description |
|---|---|---|
items |
list[dict] |
The column definitions and table constraints, in order. |
tail |
string |
What followed the closing parenthesis, such as without rowid. |
TableDefinition.parse()
sql.sqlite.ddl.TableDefinition.parse(sql: string) -> TableDefinition
ZuriReads a create table statement as sqlite_master holds it.
Parameters
sql(string)
Returns TableDefinition
Raises QueryError if it is not one.
TableDefinition.column_names()
sql.sqlite.ddl.TableDefinition.column_names() -> list[string]
ZuriThe names of the columns, in order.
Returns list[string]
TableDefinition.replace_column()
sql.sqlite.ddl.TableDefinition.replace_column(name: string, text: string)
ZuriReplaces the definition of column name.
Parameters
name(string)text(string)
Raises QueryError if there is no such column.
TableDefinition.add_column()
sql.sqlite.ddl.TableDefinition.add_column(name: string, text: string)
ZuriAdds a column after the last one, ahead of the constraints.
Parameters
name(string)text(string)
Raises QueryError if the column is there already.
TableDefinition.add_constraint()
sql.sqlite.ddl.TableDefinition.add_constraint(text: string)
ZuriAdds a table constraint.
Parameters
text(string)
TableDefinition.drop_column()
sql.sqlite.ddl.TableDefinition.drop_column(name: string, table: string)
ZuriDrops column name, and every key, unique and check constraint that
names it.
Parameters
name(string)table(string) — For the error message.
Raises QueryError if there is no such column, or it is the last.
TableDefinition.drop_foreign()
sql.sqlite.ddl.TableDefinition.drop_foreign(name: string, columns, table: string)
ZuriDrops the foreign key called name, or the one on columns, whether it
is a table constraint or written on its column.
Parameters
name(string)columns(list[string]|nil)table(string) — For the error message.
Raises QueryError if there is no such key.
TableDefinition.drop_primary()
sql.sqlite.ddl.TableDefinition.drop_primary(table: string)
ZuriDrops the primary key, a table constraint or written on its column. The columns stay.
Parameters
table(string) — For the error message.
Raises QueryError if there is none.
TableDefinition.refuse_second_primary()
sql.sqlite.ddl.TableDefinition.refuse_second_primary(table: string)
ZuriRaises if the table has a primary key, which a new one would be a second of.
Parameters
table(string) — For the error message.
Raises QueryError if it has one.
TableDefinition.render()
sql.sqlite.ddl.TableDefinition.render(name: string) -> string
ZuriThe create table statement for the definition, under name.
Parameters
name(string) — Already quoted.
Returns string
2026, Richard Ore and Zuri contributors