def makeLockSchema(inSchema):
"""
Create a self-contained schema just for L{Locker} use, in C{inSchema}.
@param inSchema: a L{Schema} to add the locks table to.
@type inSchema: L{Schema}
@return: inSchema
"""
LockTable = Table(inSchema, 'NAMED_LOCK')
LockTable.addColumn("LOCK_NAME", SQLType("varchar", 255))
LockTable.tableConstraint(Constraint.NOT_NULL, ["LOCK_NAME"])
LockTable.tableConstraint(Constraint.UNIQUE, ["LOCK_NAME"])
LockTable.primaryKey = [LockTable.columnNamed("LOCK_NAME")]
return inSchema
def makeLockSchema(inSchema):
"""
Create a self-contained schema just for L{Locker} use, in C{inSchema}.
@param inSchema: a L{Schema} to add the locks table to.
@type inSchema: L{Schema}
@return: inSchema
"""
LockTable = Table(inSchema, "NAMED_LOCK")
LockTable.addColumn("LOCK_NAME", SQLType("varchar", 255))
LockTable.tableConstraint(Constraint.NOT_NULL, ["LOCK_NAME"])
LockTable.tableConstraint(Constraint.UNIQUE, ["LOCK_NAME"])
LockTable.primaryKey = [LockTable.columnNamed("LOCK_NAME")]
return inSchema
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 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)
