def test_parse_create_table_with_foreign_keys(self):
sql = """
CREATE TABLE people(
team_id INTEGER,
job_id INTEGER,
FOREIGN KEY (team_id) REFERENCES teams,
FOREIGN KEY (job_id) REFERENCES jobs(id)
ON DELETE SET NULL
MATCH FULL
ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED
);
"""
self.assertEqual(
parse(sql),
[
ast.CreateTableStatement(
name="people",
columns=[
ast.Column(
name="team_id",
definition=ast.ColumnDefinition(type="INTEGER"),
),
ast.Column(
name="job_id",
definition=ast.ColumnDefinition(type="INTEGER"),
),
],
constraints=[
ast.ForeignKeyConstraint(
columns=["team_id"],
foreign_table="teams",
foreign_columns=[],
on_delete=None,
on_update=None,
match=None,
deferrable=None,
initially_deferred=None,
),
ast.ForeignKeyConstraint(
columns=["job_id"],
foreign_table="jobs",
foreign_columns=["id"],
on_delete=ast.OnDelete.SET_NULL,
on_update=ast.OnUpdate.CASCADE,
match=ast.ForeignKeyMatch.FULL,
deferrable=True,
initially_deferred=True,
),
],
)
],
)
def test_parse_column_with_constraints(self):
self.assertEqual(
parse_column("id INTEGER PRIMARY KEY"),
ast.Column(
name="id",
definition=ast.ColumnDefinition(
type="INTEGER", constraints=[ast.PrimaryKeyConstraint()]),
),
)
self.assertEqual(
parse_column("id INTEGER UNIQUE"),
ast.Column(
name="id",
definition=ast.ColumnDefinition(
type="INTEGER", constraints=[ast.UniqueConstraint()]),
),
)
self.assertEqual(
parse_column("id INTEGER not NULL"),
ast.Column(
name="id",
definition=ast.ColumnDefinition(
type="INTEGER", constraints=[ast.NotNullConstraint()]),
),
)
self.assertEqual(
parse_column("model INTEGER REFERENCES models"),
ast.Column(
name="model",
definition=ast.ColumnDefinition(
type="INTEGER",
constraints=[
ast.ForeignKeyConstraint(columns=[],
foreign_table="models",
foreign_columns=[])
],
),
),
)
self.assertEqual(
parse_column("name TEXT NOT NULL check(name != '')"),
ast.Column(
name="name",
definition=ast.ColumnDefinition(
type="TEXT",
constraints=[
ast.NotNullConstraint(),
ast.CheckConstraint(
ast.Infix("!=", ast.Identifier("name"),
ast.String(""))),
],
),
),
)
def test_parse_create_table_with_foreign_keys_multiple_on_delete_clauses(
self):
sql = """
CREATE TABLE people(
job_id INTEGER,
FOREIGN KEY (job_id) REFERENCES jobs
ON DELETE SET NULL
ON DELETE NO ACTION
);
"""
self.assertEqual(
parse(sql),
[
ast.CreateTableStatement(
name="people",
columns=[
ast.Column(
name="job_id",
definition=ast.ColumnDefinition(type="INTEGER"),
)
],
constraints=[
ast.ForeignKeyConstraint(
columns=["job_id"],
foreign_table="jobs",
foreign_columns=[],
on_delete=ast.OnDelete.NO_ACTION,
on_update=None,
match=None,
deferrable=None,
initially_deferred=None,
),
],
)
],
)
def test_parse_create_table_with_foreign_keys_deferrable_constraints(self):
sql = """
CREATE TABLE people(
id1 INTEGER,
id2 INTEGER,
id3 INTEGER,
FOREIGN KEY (id1) REFERENCES table1 NOT DEFERRABLE,
FOREIGN KEY (id2) REFERENCES table2 DEFERRABLE,
FOREIGN KEY (id3) REFERENCES table3 DEFERRABLE INITIALLY IMMEDIATE
);
"""
self.assertEqual(
parse(sql),
[
ast.CreateTableStatement(
name="people",
columns=[
ast.Column(
name="id1",
definition=ast.ColumnDefinition(type="INTEGER")),
ast.Column(
name="id2",
definition=ast.ColumnDefinition(type="INTEGER")),
ast.Column(
name="id3",
definition=ast.ColumnDefinition(type="INTEGER")),
],
constraints=[
ast.ForeignKeyConstraint(
columns=["id1"],
foreign_table="table1",
foreign_columns=[],
on_delete=None,
on_update=None,
match=None,
deferrable=False,
initially_deferred=None,
),
ast.ForeignKeyConstraint(
columns=["id2"],
foreign_table="table2",
foreign_columns=[],
on_delete=None,
on_update=None,
match=None,
deferrable=True,
initially_deferred=None,
),
ast.ForeignKeyConstraint(
columns=["id3"],
foreign_table="table3",
foreign_columns=[],
on_delete=None,
on_update=None,
match=None,
deferrable=True,
initially_deferred=False,
),
],
)
],
)