def addSQLToSchema(schema, schemaData):
"""
Add new SQL to an existing schema.
@param schema: The schema to add the new SQL to.
@type schema: L{Schema}
@param schemaData: A string containing some SQL statements.
@type schemaData: C{str}
@return: the C{schema} argument
"""
parsed = parse(schemaData)
for stmt in parsed:
preface = ''
while stmt.tokens and not significant(stmt.tokens[0]):
preface += str(stmt.tokens.pop(0))
if not stmt.tokens:
continue
if stmt.get_type() == 'CREATE':
createType = stmt.token_next(1, True).value.upper()
if createType == u'TABLE':
t = tableFromCreateStatement(schema, stmt)
t.addComment(preface)
elif createType == u'SEQUENCE':
Sequence(schema,
stmt.token_next(2, True).get_name().encode('utf-8'))
elif createType == u'INDEX':
signifindex = iterSignificant(stmt)
expect(signifindex, ttype=Keyword.DDL, value='CREATE')
expect(signifindex, ttype=Keyword, value='INDEX')
indexName = nameOrIdentifier(signifindex.next())
expect(signifindex, ttype=Keyword, value='ON')
[tableName, columnArgs] = iterSignificant(expect(signifindex,
cls=Function))
tableName = nameOrIdentifier(tableName)
arggetter = iterSignificant(columnArgs)
expect(arggetter, ttype=Punctuation, value=u'(')
valueOrValues = arggetter.next()
if isinstance(valueOrValues, IdentifierList):
valuelist = valueOrValues.get_identifiers()
else:
valuelist = [valueOrValues]
expect(arggetter, ttype=Punctuation, value=u')')
idx = Index(schema, indexName, schema.tableNamed(tableName))
for token in valuelist:
columnName = nameOrIdentifier(token)
idx.addColumn(idx.table.columnNamed(columnName))
elif stmt.get_type() == 'INSERT':
insertTokens = iterSignificant(stmt)
expect(insertTokens, ttype=Keyword.DML, value='INSERT')
expect(insertTokens, ttype=Keyword, value='INTO')
tableName = expect(insertTokens, cls=Identifier).get_name()
expect(insertTokens, ttype=Keyword, value='VALUES')
values = expect(insertTokens, cls=Parenthesis)
vals = iterSignificant(values)
expect(vals, ttype=Punctuation, value='(')
valuelist = expect(vals, cls=IdentifierList)
expect(vals, ttype=Punctuation, value=')')
rowData = []
for ident in valuelist.get_identifiers():
rowData.append(
{Number.Integer: int,
String.Single: _destringify}
[ident.ttype](ident.value)
)
schema.tableNamed(tableName).insertSchemaRow(rowData)
else:
print 'unknown type:', stmt.get_type()
return schema
def dumpSchema_oracle(txn, title, schemaname="public"):
"""
Generate the L{Schema}.
"""
schemaname = schemaname.lower()
schema = Schema(title)
# Sequences
seqs = {}
rows = yield txn.execSQL(
"select sequence_name from all_sequences where sequence_owner = '%s'" %
(schemaname.upper(), ))
for row in rows:
name = row[0]
seqs[name.upper()] = Sequence(schema, name.upper())
# Tables
tables = {}
rows = yield txn.execSQL(
"select table_name from all_tables where owner = '%s'" %
(schemaname.upper(), ))
for row in rows:
name = row[0]
table = Table(schema, name.upper())
tables[name.upper()] = table
# Columns
rows = yield txn.execSQL(
"select column_name, data_type, nullable, char_length, data_default from all_tab_columns where owner = '%s' and table_name = '%s'"
% (
schemaname.upper(),
name,
))
for name, datatype, is_nullable, charlen, default in rows:
# TODO: figure out the type
column = Column(
table, name.upper(),
SQLType(
DTYPE_MAP_ORACLE.get(datatype.lower(), datatype.lower()),
charlen))
table.columns.append(column)
if default:
default = default.strip()
if default.startswith("nextval("):
dname = default.split("'")[1].split(".")[-1]
column.default = seqs[dname.upper()]
elif default in DEFAULTVALUE_MAP_ORACLE:
column.default = DEFAULTVALUE_MAP_ORACLE[default]
else:
try:
column.default = int(default)
except ValueError:
column.default = default
if is_nullable == "N":
table.tableConstraint(Constraint.NOT_NULL, [
column.name,
])
# Constraints
constraints = collections.defaultdict(list)
rows = yield txn.execSQL(
"select constraint_name, table_name, column_name, position from all_cons_columns where owner = '%s'"
% (schemaname.upper(), ))
for conname, tname, cname, position in rows:
constraints[conname].append((
tname,
cname,
position,
))
rows = yield txn.execSQL(
"select constraint_name, constraint_type, table_name, r_constraint_name, delete_rule from all_constraints where owner = '%s'"
% (schemaname.upper(), ))
for conname, conntype, tname, r_constraint_name, delete_rule in rows:
if constraints[conname][0][0].upper() in tables:
constraint = constraints[conname]
constraint = sorted(constraint, key=lambda x: x[2])
table = tables[constraint[0][0].upper()]
column_names = [item[1].upper() for item in constraint]
columns = [
table.columnNamed(column_name) for column_name in column_names
]
if conntype == "P":
table.primaryKey = columns
elif conntype == "U":
table.tableConstraint(Constraint.UNIQUE, column_names)
elif conntype == "R":
columns[0].doesReferenceName(
constraints[r_constraint_name][0][0].upper())
if delete_rule.lower() != "no action":
columns[0].deleteAction = delete_rule.lower()
# Indexed columns
idx = collections.defaultdict(list)
rows = yield txn.execSQL(
"select index_name, column_name, column_position from all_ind_columns where index_owner = '%s'"
% (schemaname.upper(), ))
for index_name, column_name, column_position in rows:
idx[index_name].append((column_name, column_position))
# Indexes
rows = yield txn.execSQL(
"select index_name, table_name, uniqueness from all_indexes where owner = '%s'"
% (schemaname.upper(), ))
for index_name, table_name, uniqueness in rows:
if table_name in tables:
table = tables[table_name]
column_names = [
item[0].upper()
for item in sorted(idx[index_name], key=lambda x: x[1])
]
columns = [
table.columnNamed(column_name) for column_name in column_names
]
index = Index(schema, index_name.upper(), table,
uniqueness == "UNIQUE")
for column in columns:
index.addColumn(column)
# Functions
rows = yield txn.execSQL(
"select object_name from all_procedures where owner = '%s'" %
(schemaname.upper(), ))
for row in rows:
name = row[0]
Function(schema, name)
returnValue(schema)
def addSQLToSchema(schema, schemaData):
"""
Add new SQL to an existing schema.
@param schema: The schema to add the new SQL to.
@type schema: L{Schema}
@param schemaData: A string containing some SQL statements.
@type schemaData: C{str}
@return: the C{schema} argument
"""
parsed = parse(schemaData)
for stmt in parsed:
preface = ""
while stmt.tokens and not significant(stmt.tokens[0]):
preface += str(stmt.tokens.pop(0))
if not stmt.tokens:
continue
if stmt.get_type() == "CREATE":
createType = stmt.token_next(1, True).value.upper()
if createType == u"TABLE":
t = tableFromCreateStatement(schema, stmt)
t.addComment(preface)
elif createType == u"SEQUENCE":
Sequence(schema,
stmt.token_next(2, True).get_name().encode("utf-8"))
elif createType in (u"INDEX", u"UNIQUE"):
signifindex = iterSignificant(stmt)
expect(signifindex, ttype=Keyword.DDL, value="CREATE")
token = signifindex.next()
unique = False
if token.match(Keyword, "UNIQUE"):
unique = True
token = signifindex.next()
if not token.match(Keyword, "INDEX"):
raise ViolatedExpectation("INDEX or UNQIUE", token.value)
indexName = nameOrIdentifier(signifindex.next())
expect(signifindex, ttype=Keyword, value="ON")
token = signifindex.next()
if isinstance(token, Function):
[tableName, columnArgs] = iterSignificant(token)
else:
tableName = token
token = signifindex.next()
if token.match(Keyword, "USING"):
[_ignore, columnArgs
] = iterSignificant(expect(signifindex, cls=Function))
else:
raise ViolatedExpectation("USING", token)
tableName = nameOrIdentifier(tableName)
arggetter = iterSignificant(columnArgs)
expect(arggetter, ttype=Punctuation, value=u"(")
valueOrValues = arggetter.next()
if isinstance(valueOrValues, IdentifierList):
valuelist = valueOrValues.get_identifiers()
else:
valuelist = [valueOrValues]
expect(arggetter, ttype=Punctuation, value=u")")
idx = Index(schema, indexName, schema.tableNamed(tableName),
unique)
for token in valuelist:
columnName = nameOrIdentifier(token)
idx.addColumn(idx.table.columnNamed(columnName))
elif createType == u"FUNCTION":
parseFunction(schema, stmt)
elif stmt.get_type() == "INSERT":
insertTokens = iterSignificant(stmt)
expect(insertTokens, ttype=Keyword.DML, value="INSERT")
expect(insertTokens, ttype=Keyword, value="INTO")
token = insertTokens.next()
if isinstance(token, Function):
[tableName, columnArgs] = iterSignificant(token)
tableName = tableName.get_name()
columns = namesInParens(columnArgs)
else:
tableName = token.get_name()
columns = None
expect(insertTokens, ttype=Keyword, value="VALUES")
values = expect(insertTokens, cls=Parenthesis)
vals = iterSignificant(values)
expect(vals, ttype=Punctuation, value="(")
valuelist = expect(vals, cls=IdentifierList)
expect(vals, ttype=Punctuation, value=")")
rowData = []
for ident in valuelist.get_identifiers():
rowData.append({
Number.Integer: int,
String.Single: _destringify
}[ident.ttype](ident.value))
schema.tableNamed(tableName).insertSchemaRow(rowData,
columns=columns)
elif stmt.get_type() == "CREATE OR REPLACE":
createType = stmt.token_next(1, True).value.upper()
if createType == u"FUNCTION":
parseFunction(schema, stmt)
else:
print("unknown type:", stmt.get_type())
return schema
def dumpSchema_oracle(txn, title, schemaname="public"):
"""
Generate the L{Schema}.
"""
schemaname = schemaname.lower()
schema = Schema(title)
# Sequences
seqs = {}
rows = yield txn.execSQL(
"select sequence_name from all_sequences where sequence_owner = '%s'" % (schemaname.upper(),)
)
for row in rows:
name = row[0]
seqs[name.upper()] = Sequence(schema, name.upper())
# Tables
tables = {}
rows = yield txn.execSQL("select table_name from all_tables where owner = '%s'" % (schemaname.upper(),))
for row in rows:
name = row[0]
table = Table(schema, name.upper())
tables[name.upper()] = table
# Columns
rows = yield txn.execSQL(
"select column_name, data_type, nullable, char_length, data_default from all_tab_columns where owner = '%s' and table_name = '%s'"
% (schemaname.upper(), name)
)
for name, datatype, is_nullable, charlen, default in rows:
# TODO: figure out the type
column = Column(
table, name.upper(), SQLType(DTYPE_MAP_ORACLE.get(datatype.lower(), datatype.lower()), charlen)
)
table.columns.append(column)
if default:
default = default.strip()
if default.startswith("nextval("):
dname = default.split("'")[1].split(".")[-1]
column.default = seqs[dname.upper()]
elif default in DEFAULTVALUE_MAP_ORACLE:
column.default = DEFAULTVALUE_MAP_ORACLE[default]
else:
try:
column.default = int(default)
except ValueError:
column.default = default
if is_nullable == "N":
table.tableConstraint(Constraint.NOT_NULL, [column.name])
# Constraints
constraints = collections.defaultdict(list)
rows = yield txn.execSQL(
"select constraint_name, table_name, column_name, position from all_cons_columns where owner = '%s'"
% (schemaname.upper(),)
)
for conname, tname, cname, position in rows:
constraints[conname].append((tname, cname, position))
rows = yield txn.execSQL(
"select constraint_name, constraint_type, table_name, r_constraint_name, delete_rule from all_constraints where owner = '%s'"
% (schemaname.upper(),)
)
for conname, conntype, tname, r_constraint_name, delete_rule in rows:
if constraints[conname][0][0].upper() in tables:
constraint = constraints[conname]
constraint = sorted(constraint, key=lambda x: x[2])
table = tables[constraint[0][0].upper()]
column_names = [item[1].upper() for item in constraint]
columns = [table.columnNamed(column_name) for column_name in column_names]
if conntype == "P":
table.primaryKey = columns
elif conntype == "U":
table.tableConstraint(Constraint.UNIQUE, column_names)
elif conntype == "R":
columns[0].doesReferenceName(constraints[r_constraint_name][0][0].upper())
if delete_rule.lower() != "no action":
columns[0].deleteAction = delete_rule.lower()
# Indexed columns
idx = collections.defaultdict(list)
rows = yield txn.execSQL(
"select index_name, column_name, column_position from all_ind_columns where index_owner = '%s'"
% (schemaname.upper(),)
)
for index_name, column_name, column_position in rows:
idx[index_name].append((column_name, column_position))
# Indexes
rows = yield txn.execSQL(
"select index_name, table_name, uniqueness from all_indexes where owner = '%s'" % (schemaname.upper(),)
)
for index_name, table_name, uniqueness in rows:
if table_name in tables:
table = tables[table_name]
column_names = [item[0].upper() for item in sorted(idx[index_name], key=lambda x: x[1])]
columns = [table.columnNamed(column_name) for column_name in column_names]
index = Index(schema, index_name.upper(), table, uniqueness == "UNIQUE")
for column in columns:
index.addColumn(column)
returnValue(schema)
