def makeJobSchema(inSchema):
"""
Create a self-contained schema for L{JobInfo} to use, in C{inSchema}.
@param inSchema: a L{Schema} to add the job table to.
@type inSchema: L{Schema}
@return: a schema with just the one table.
"""
# Initializing this duplicate schema avoids a circular dependency, but this
# should really be accomplished with independent schema objects that the
# transaction is made aware of somehow.
JobTable = Table(inSchema, "JOB")
JobTable.addColumn("JOB_ID", SQLType("integer", None), default=Sequence(inSchema, "JOB_SEQ"), notNull=True, primaryKey=True)
JobTable.addColumn("WORK_TYPE", SQLType("varchar", 255), notNull=True)
JobTable.addColumn("PRIORITY", SQLType("integer", 0), default=0, notNull=True)
JobTable.addColumn("WEIGHT", SQLType("integer", 0), default=0, notNull=True)
JobTable.addColumn("NOT_BEFORE", SQLType("timestamp", None), notNull=True)
JobTable.addColumn("IS_ASSIGNED", SQLType("integer", 0), default=0, notNull=True)
JobTable.addColumn("ASSIGNED", SQLType("timestamp", None), default=None)
JobTable.addColumn("OVERDUE", SQLType("timestamp", None), default=None)
JobTable.addColumn("FAILED", SQLType("integer", 0), default=0, notNull=True)
JobTable.addColumn("PAUSE", SQLType("integer", 0), default=0, notNull=True)
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 dumpCurrentSchema(verbose=False):
schemaname = "public"
schema = Schema("Dumped schema")
# Sequences
seqs = {}
rows = execSQL(
"Schema sequences...",
"select sequence_name from information_schema.sequences where sequence_schema = '%s';" % (schemaname,),
verbose
)
for row in rows:
name = row[0]
seqs[name.upper()] = Sequence(schema, name.upper())
# Tables
tables = {}
rows = execSQL(
"Schema tables...",
"select table_name from information_schema.tables where table_schema = '%s';" % (schemaname,),
verbose
)
for row in rows:
name = row[0]
table = Table(schema, name.upper())
tables[name.upper()] = table
# Columns
rows = execSQL(
"Reading table '{}' columns...".format(name),
"select column_name, data_type, is_nullable, character_maximum_length, column_default from information_schema.columns where table_schema = '%s' and table_name = '%s';" % (schemaname, name,),
verbose,
)
for name, datatype, is_nullable, charlen, default in rows:
# TODO: figure out the type
column = Column(table, name.upper(), SQLType(DTYPE_MAP_POSTGRES.get(datatype, datatype), int(charlen) if charlen else 0))
table.columns.append(column)
if default:
if default.startswith("nextval("):
dname = default.split("'")[1].split(".")[-1]
column.default = seqs[dname.upper()]
elif default in DEFAULTVALUE_MAP_POSTGRES:
column.default = DEFAULTVALUE_MAP_POSTGRES[default]
else:
try:
column.default = int(default)
except ValueError:
column.default = default
if is_nullable == "NO":
table.tableConstraint(Constraint.NOT_NULL, [column.name, ])
# Key columns
keys = {}
rows = execSQL(
"Schema key columns...",
"select constraint_name, table_name, column_name from information_schema.key_column_usage where constraint_schema = '%s';" % (schemaname,),
verbose
)
for conname, tname, cname in rows:
keys[conname] = (tname, cname)
# Constraints
constraints = {}
rows = execSQL(
"SChema constraints...",
"select constraint_name, table_name, column_name from information_schema.constraint_column_usage where constraint_schema = '%s';" % (schemaname,),
verbose
)
for conname, tname, cname in rows:
constraints[conname] = (tname, cname)
# References - referential_constraints
rows = execSQL(
"Schema referential constraints...",
"select constraint_name, unique_constraint_name, delete_rule from information_schema.referential_constraints where constraint_schema = '%s';" % (schemaname,),
verbose
)
for conname, uconname, delete in rows:
table = tables[keys[conname][0].upper()]
column = table.columnNamed(keys[conname][1].upper())
column.doesReferenceName(constraints[uconname][0].upper())
if delete != "NO ACTION":
column.deleteAction = delete.lower()
# Indexes
# TODO: handle implicit indexes created via primary key() and unique() statements within CREATE TABLE
rows = execSQL(
"Schema indexes...",
"select indexdef from pg_indexes where schemaname = '%s';" % (schemaname,),
verbose
)
for indexdef in rows:
addSQLToSchema(schema, indexdef[0].replace("%s." % (schemaname,), "").upper())
# Functions
rows = execSQL(
"Schema functions",
"select routine_name from information_schema.routines where routine_schema = '%s';" % (schemaname,),
verbose
)
for row in rows:
name = row[0]
Function(schema, name)
return schema
def parseColumn(self, name):
"""
Parse a column with the given name.
"""
typeName = self.next()
if isinstance(typeName, Function):
[funcIdent, args] = iterSignificant(typeName)
typeName = funcIdent
arggetter = iterSignificant(args)
expect(arggetter, value=u"(")
typeLength = int(
expect(arggetter, ttype=Number.Integer).value.encode("utf-8"))
else:
maybeTypeArgs = self.next()
if isinstance(maybeTypeArgs, Parenthesis):
# type arguments
significant = iterSignificant(maybeTypeArgs)
expect(significant, value=u"(")
typeLength = int(significant.next().value)
else:
# something else
typeLength = None
self.pushback(maybeTypeArgs)
theType = SQLType(typeName.value.encode("utf-8"), typeLength)
theColumn = self.table.addColumn(name=name.encode("utf-8"),
type=theType)
for val in self:
if val.ttype == Punctuation:
return self.checkEnd(val)
else:
expected = True
def oneConstraint(t):
self.table.tableConstraint(t, [theColumn.name])
if val.match(Keyword, "PRIMARY"):
expect(self, ttype=Keyword, value="KEY")
# XXX check to make sure there's no other primary key yet
self.table.primaryKey = [theColumn]
elif val.match(Keyword, "UNIQUE"):
# XXX add UNIQUE constraint
oneConstraint(Constraint.UNIQUE)
elif val.match(Keyword, "NOT"):
# possibly not necessary, as "NOT NULL" is a single keyword
# in sqlparse as of 0.1.2
expect(self, ttype=Keyword, value="NULL")
oneConstraint(Constraint.NOT_NULL)
elif val.match(Keyword, "NOT NULL"):
oneConstraint(Constraint.NOT_NULL)
elif val.match(Keyword, "CHECK"):
self.table.checkConstraint(self.readExpression(
self.next()))
elif val.match(Keyword, "DEFAULT"):
theDefault = self.next()
if isinstance(theDefault, Parenthesis):
iDefault = iterSignificant(theDefault)
expect(iDefault, ttype=Punctuation, value="(")
theDefault = iDefault.next()
if isinstance(theDefault, Function):
thingo = theDefault.tokens[0].get_name()
parens = expectSingle(theDefault.tokens[-1],
cls=Parenthesis)
pareniter = iterSignificant(parens)
if thingo.upper() == "NEXTVAL":
expect(pareniter, ttype=Punctuation, value="(")
seqname = _destringify(
expect(pareniter, ttype=String.Single).value)
defaultValue = self.table.schema.sequenceNamed(
seqname)
defaultValue.referringColumns.append(theColumn)
else:
defaultValue = ProcedureCall(
thingo.encode("utf-8"),
namesInParens(parens),
)
elif theDefault.ttype == Number.Integer:
defaultValue = int(theDefault.value)
elif (theDefault.ttype == Keyword
and theDefault.value.lower() == "false"):
defaultValue = False
elif (theDefault.ttype == Keyword
and theDefault.value.lower() == "true"):
defaultValue = True
elif (theDefault.ttype == Keyword
and theDefault.value.lower() == "null"):
defaultValue = None
elif theDefault.ttype == String.Single:
defaultValue = _destringify(theDefault.value)
# Oracle format for current timestamp mapped to postgres variant
elif (theDefault.ttype == Keyword
and theDefault.value.lower() == "current_timestamp"):
expect(self, ttype=Keyword, value="at")
expect(self, ttype=None, value="time")
expect(self, ttype=None, value="zone")
expect(self, ttype=String.Single, value="'UTC'")
defaultValue = ProcedureCall(
"timezone", [u"UTC", u"CURRENT_TIMESTAMP"])
else:
raise RuntimeError("not sure what to do: default %r" %
(theDefault, ))
theColumn.setDefaultValue(defaultValue)
elif val.match(Keyword, "REFERENCES"):
target = nameOrIdentifier(self.next())
theColumn.doesReferenceName(target)
elif val.match(Keyword, "ON"):
expect(self, ttype=Keyword.DML, value="DELETE")
refAction = self.next()
if (refAction.ttype == Keyword
and refAction.value.upper() == "CASCADE"):
theColumn.deleteAction = "cascade"
elif (refAction.ttype == Keyword
and refAction.value.upper() == "SET"):
setAction = self.next()
if (setAction.ttype == Keyword
and setAction.value.upper() == "NULL"):
theColumn.deleteAction = "set null"
elif (setAction.ttype == Keyword
and setAction.value.upper() == "DEFAULT"):
theColumn.deleteAction = "set default"
else:
raise RuntimeError("Invalid on delete set %r" %
(setAction.value, ))
else:
raise RuntimeError("Invalid on delete %r" %
(refAction.value, ))
else:
expected = False
if not expected:
print("UNEXPECTED TOKEN:", repr(val), theColumn)
print(self.parens)
import pprint
pprint.pprint(self.parens.tokens)
return 0
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(txn, title, schemaname="public"):
"""
Generate the L{Schema}.
"""
schemaname = schemaname.lower()
schema = Schema(title)
# Sequences
seqs = {}
rows = yield txn.execSQL(
"select sequence_name from information_schema.sequences where sequence_schema = '%s';"
% (schemaname, ))
for row in rows:
name = row[0]
seqs[name.upper()] = Sequence(schema, name.upper())
# Tables
tables = {}
rows = yield txn.execSQL(
"select table_name from information_schema.tables where table_schema = '%s';"
% (schemaname, ))
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, character_maximum_length, column_default from information_schema.columns where table_schema = '%s' and table_name = '%s';"
% (
schemaname,
name,
))
for name, datatype, charlen, default in rows:
# TODO: figure out the type
column = Column(
table, name.upper(),
SQLType(DTYPE_MAP.get(datatype, datatype), charlen))
table.columns.append(column)
if default:
if default.startswith("nextval("):
dname = default.split("'")[1].split(".")[-1]
column.default = seqs[dname.upper()]
elif default in DEFAULTVALUE_MAP:
column.default = DEFAULTVALUE_MAP[default]
else:
try:
column.default = int(default)
except ValueError:
column.default = default
# Key columns
keys = {}
rows = yield txn.execSQL(
"select constraint_name, table_name, column_name from information_schema.key_column_usage where constraint_schema = '%s';"
% (schemaname, ))
for conname, tname, cname in rows:
keys[conname] = (tname, cname)
# Constraints
constraints = {}
rows = yield txn.execSQL(
"select constraint_name, table_name, column_name from information_schema.constraint_column_usage where constraint_schema = '%s';"
% (schemaname, ))
for conname, tname, cname in rows:
constraints[conname] = (tname, cname)
# References - referential_constraints
rows = yield txn.execSQL(
"select constraint_name, unique_constraint_name, delete_rule from information_schema.referential_constraints where constraint_schema = '%s';"
% (schemaname, ))
for conname, uconname, delete in rows:
table = tables[keys[conname][0].upper()]
column = table.columnNamed(keys[conname][1].upper())
column.doesReferenceName(constraints[uconname][0].upper())
if delete != "NO ACTION":
column.deleteAction = delete.lower()
# Indexes
# TODO: handle implicit indexes created via primary key() and unique() statements within CREATE TABLE
rows = yield txn.execSQL(
"select indexdef from pg_indexes where schemaname = '%s';" %
(schemaname, ))
for indexdef in rows:
addSQLToSchema(schema, indexdef[0].replace("%s." % (schemaname, ),
"").upper())
# Functions
rows = yield txn.execSQL(
"select routine_name from information_schema.routines where routine_schema = '%s';"
% (schemaname, ))
for row in rows:
name = row[0]
Function(schema, name)
returnValue(schema)