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, ), ], ) ], )