def test_create_type_statement():
sql = "CREATE TYPE FLUENCY AS ENUM ('basic', 'intermediate');"
statement = next(parse_statements(sqlparse.parse(sql)))
assert_is_instance(statement, CreateType)
assert_equals('FLUENCY', statement.get_name())
assert_equals(['basic', 'intermediate'], statement.get_enum_labels())
def test_set_statement():
sql = "SET search_path = 'cover_art_archive';"
statement = next(parse_statements(sqlparse.parse(sql)))
assert_is_instance(statement, Set)
assert_equals('search_path', statement.get_name())
assert_equals('cover_art_archive', statement.get_value())
def test_create_index():
sql = '''CREATE INDEX statistic_name ON statistic (name); '''
statement = next(parse_statements(sqlparse.parse(sql)))
assert_is_instance(statement, CreateIndex)
assert_equal('statistic_name', statement.get_name())
assert_equal('statistic', statement.get_table())
assert_equal(['name'], statement.get_columns())
assert_false(statement.is_unique())
def test_create_unique_index():
sql = '''CREATE UNIQUE INDEX statistic_name_date_collected ON statistic (name, date_collected); '''
statement = next(parse_statements(sqlparse.parse(sql)))
statement._pprint_tree()
assert_is_instance(statement, CreateIndex)
assert_equal('statistic_name_date_collected', statement.get_name())
assert_equal('statistic', statement.get_table())
assert_equal(['name', 'date_collected'], statement.get_columns())
assert_true(statement.is_unique())
def test_create_table_statement_named_check_constraint():
sql = '''CREATE TABLE table_name (column INTEGER(2) NOT NULL DEFAULT 0 CONSTRAINT check_column CHECK (edits_pending > 0)); '''
statement = next(parse_statements(sqlparse.parse(sql)))
assert_is_instance(statement, CreateTable)
columns = list(statement.get_columns())
assert_equal(1, len(columns))
column = columns[0]
check = column.get_check_constraint()
assert_true(check)
assert_equal('check_column', check.get_name())
assert_equal('edits_pending>0', str(check.get_body()))
def test_parse_statements():
sql = '''
SET search_path = 'cover_art_archive';
CREATE TABLE table_name (
id SERIAL, -- PK
name VARCHAR
);
CREATE TYPE FLUENCY AS ENUM ('basic', 'intermediate', 'advanced', 'native');
'''
statements = sqlparse.parse(sql)
for statement in statements:
statement._pprint_tree()
print()
statements = parse_statements(statements)
for statement in statements:
print(repr(statement))
def parse_sql(sql, schema='musicbrainz'):
tables = []
types = []
indexes = []
statements = parse_statements(sqlparse.parse(sql))
for statement in statements:
if isinstance(statement, Set) and statement.get_name() == 'search_path':
schema = statement.get_value().split(',')[0].strip()
elif isinstance(statement, CreateTable):
tables.append(parse_create_table(statement, schema))
elif isinstance(statement, CreateType):
types.append(parse_create_type(statement, schema))
elif isinstance(statement, CreateIndex):
indexes.append(parse_create_index(statement, schema))
return tables, types, indexes
def test_create_table_statement():
sql = '''
CREATE TABLE table_name (
id SERIAL, -- PK
name VARCHAR(100) NOT NULL,
created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL
);
'''
statement = next(parse_statements(sqlparse.parse(sql)))
assert_is_instance(statement, CreateTable)
assert_equals('table_name', statement.get_name())
columns = list(statement.get_columns())
assert_equals(3, len(columns))
column = columns[0]
assert_equals('id', column.get_name())
assert_equals('SERIAL', column.get_type())
assert_equals(None, column.get_default_value())
assert_equals(['-- PK'], column.get_comments())
assert_equals(False, column.is_not_null())
assert_equals(None, column.get_check_constraint())
column = columns[1]
assert_equals('name', column.get_name())
assert_equals('VARCHAR(100)', column.get_type())
assert_equals(None, column.get_default_value())
assert_equals([], column.get_comments())
assert_equals(True, column.is_not_null())
assert_equals(None, column.get_check_constraint())
column = columns[2]
assert_equals('created', column.get_name())
assert_equals('TIMESTAMP WITH TIME ZONE', column.get_type())
assert_equals('now()', column.get_default_value())
assert_equals([], column.get_comments())
assert_equals(True, column.is_not_null())
assert_equals(None, column.get_check_constraint())