def schemaFromString(self, string): """ Createa a L{Schema} """ s = Schema(self.id()) addSQLToSchema(s, string) return s
def schemaFromString(data): """ Get a L{Schema}. @param data: a C{str} containing SQL. @return: a L{Schema} object with the contents of the given C{str} parsed and added to it as L{Table} objects. """ schema = Schema() addSQLToSchema(schema, data) return schema
def schemaFromPath(path): """ Get a L{Schema}. @param path: a L{FilePath}-like object containing SQL. @return: a L{Schema} object with the contents of the given C{path} parsed and added to it as L{Table} objects. """ schema = Schema(path.basename()) schemaData = path.getContent() addSQLToSchema(schema, schemaData) return schema
def dumpCurrentSchema(verbose=False): schema = Schema("Dumped schema") tables = {} # Tables rows = execSQL( "Schema tables...", "select table_name from information_schema.tables where table_schema = 'public';", verbose) for row in rows: name = row table = Table(schema, name) tables[name] = table # Columns rows = execSQL( "Reading table '{}' columns...".format(name), "select column_name from information_schema.columns where table_schema = 'public' and table_name = '{}';" .format(name), verbose) for row in rows: name = row # TODO: figure out the type column = Column(table, name, None) table.columns.append(column) # 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 = 'public';", verbose) for indexdef in rows: addSQLToSchema(schema, indexdef.replace("public.", "")) # Sequences rows = execSQL( "Schema sequences...", "select sequence_name from information_schema.sequences where sequence_schema = 'public';", verbose) for row in rows: name = row Sequence(schema, name) return schema
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
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 JobInfoSchema = SchemaSyntax(makeJobSchema(Schema(__file__))) class JobFailedError(Exception): """ A job failed to run - we need to be smart about clean up. """ def __init__(self, ex): self._ex = ex class JobTemporaryError(Exception): """ A job failed to run due to a temporary failure. We will get the job to run again after the specified interval (with a built-in back-off based on the number of failures also applied).
@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 LockSchema = SchemaSyntax(makeLockSchema(Schema(__file__))) class NamedLock(Record, fromTable(LockSchema.NAMED_LOCK)): """ An L{AcquiredLock} lock against a shared data store that the current process holds via the referenced transaction. """ @classmethod def acquire(cls, txn, name): """ Acquire a lock with the given name. @param name: The name of the lock to acquire. Against the same store, no two locks may be acquired. @type name: L{unicode}
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)