def alter_column(self, table_name, column_name, nullable=None, server_default=False, name=None, type_=None, autoincrement=None, **kw): existing = self.columns[column_name] existing_transfer = self.column_transfers[column_name] if name is not None and name != column_name: # note that we don't change '.key' - we keep referring # to the renamed column by its old key in _create(). neat! existing.name = name existing_transfer["name"] = name if type_ is not None: type_ = sqltypes.to_instance(type_) existing.type = type_ existing_transfer["expr"] = cast(existing_transfer["expr"], type_) if nullable is not None: existing.nullable = nullable if server_default is not False: sql_schema.DefaultClause(server_default)._set_parent(existing) if autoincrement is not None: existing.autoincrement = bool(autoincrement)
def alter_column( self, table_name, column_name, nullable=None, server_default=False, name=None, type_=None, autoincrement=None, **kw ): existing = self.columns[column_name] existing_transfer = self.column_transfers[column_name] if name is not None and name != column_name: # note that we don't change '.key' - we keep referring # to the renamed column by its old key in _create(). neat! existing.name = name existing_transfer["name"] = name if type_ is not None: type_ = sqltypes.to_instance(type_) # old type is being discarded so turn off eventing # rules. Alternatively we can # erase the events set up by this type, but this is simpler. # we also ignore the drop_constraint that will come here from # Operations.implementation_for(alter_column) if isinstance(existing.type, SchemaEventTarget): existing.type._create_events = ( existing.type.create_constraint ) = False if existing.type._type_affinity is not type_._type_affinity: existing_transfer["expr"] = cast( existing_transfer["expr"], type_ ) existing.type = type_ # we *dont* however set events for the new type, because # alter_column is invoked from # Operations.implementation_for(alter_column) which already # will emit an add_constraint() if nullable is not None: existing.nullable = nullable if server_default is not False: if server_default is None: existing.server_default = None else: sql_schema.DefaultClause(server_default)._set_parent(existing) if autoincrement is not None: existing.autoincrement = bool(autoincrement)
def reflecttable(self, table, include_columns, exclude_columns=()): """Given a Table object, load its internal constructs based on introspection. This is the underlying method used by most dialects to produce table reflection. Direct usage is like:: from sqlalchemy import create_engine, MetaData, Table from sqlalchemy.engine import reflection engine = create_engine('...') meta = MetaData() user_table = Table('user', meta) insp = Inspector.from_engine(engine) insp.reflecttable(user_table, None) :param table: a :class:`~sqlalchemy.schema.Table` instance. :param include_columns: a list of string column names to include in the reflection process. If ``None``, all columns are reflected. """ dialect = self.bind.dialect # table attributes we might need. reflection_options = {} schema = table.schema table_name = table.name # apply table options tbl_opts = self.get_table_options(table_name, schema, **table.kwargs) if tbl_opts: table.kwargs.update(tbl_opts) # table.kwargs will need to be passed to each reflection method. Make # sure keywords are strings. tblkw = table.kwargs.copy() for (k, v) in list(tblkw.items()): del tblkw[k] tblkw[str(k)] = v if isinstance(schema, str): schema = schema.decode(dialect.encoding) if isinstance(table_name, str): table_name = table_name.decode(dialect.encoding) # columns found_table = False cols_by_orig_name = {} for col_d in self.get_columns(table_name, schema, **tblkw): found_table = True orig_name = col_d['name'] name = col_d['name'] if include_columns and name not in include_columns: continue if exclude_columns and name in exclude_columns: continue coltype = col_d['type'] col_kw = { 'nullable': col_d['nullable'], } for k in ('autoincrement', 'quote', 'info', 'key'): if k in col_d: col_kw[k] = col_d[k] colargs = [] if col_d.get('default') is not None: # the "default" value is assumed to be a literal SQL # expression, so is wrapped in text() so that no quoting # occurs on re-issuance. colargs.append( sa_schema.DefaultClause(sql.text(col_d['default']), _reflected=True)) if 'sequence' in col_d: # TODO: mssql and sybase are using this. seq = col_d['sequence'] sequence = sa_schema.Sequence(seq['name'], 1, 1) if 'start' in seq: sequence.start = seq['start'] if 'increment' in seq: sequence.increment = seq['increment'] colargs.append(sequence) cols_by_orig_name[orig_name] = col = \ sa_schema.Column(name, coltype, *colargs, **col_kw) table.append_column(col) if not found_table: raise exc.NoSuchTableError(table.name) # Primary keys pk_cons = self.get_pk_constraint(table_name, schema, **tblkw) if pk_cons: pk_cols = [ cols_by_orig_name[pk] for pk in pk_cons['constrained_columns'] if pk in cols_by_orig_name and pk not in exclude_columns ] pk_cols += [ pk for pk in table.primary_key if pk.key in exclude_columns ] primary_key_constraint = sa_schema.PrimaryKeyConstraint( name=pk_cons.get('name'), *pk_cols) table.append_constraint(primary_key_constraint) # Foreign keys fkeys = self.get_foreign_keys(table_name, schema, **tblkw) for fkey_d in fkeys: conname = fkey_d['name'] # look for columns by orig name in cols_by_orig_name, # but support columns that are in-Python only as fallback constrained_columns = [ cols_by_orig_name[c].key if c in cols_by_orig_name else c for c in fkey_d['constrained_columns'] ] if exclude_columns and set(constrained_columns).intersection( exclude_columns): continue referred_schema = fkey_d['referred_schema'] referred_table = fkey_d['referred_table'] referred_columns = fkey_d['referred_columns'] refspec = [] if referred_schema is not None: sa_schema.Table(referred_table, table.metadata, autoload=True, schema=referred_schema, autoload_with=self.bind, **reflection_options) for column in referred_columns: refspec.append(".".join( [referred_schema, referred_table, column])) else: sa_schema.Table(referred_table, table.metadata, autoload=True, autoload_with=self.bind, **reflection_options) for column in referred_columns: refspec.append(".".join([referred_table, column])) if 'options' in fkey_d: options = fkey_d['options'] else: options = {} table.append_constraint( sa_schema.ForeignKeyConstraint(constrained_columns, refspec, conname, link_to_name=True, **options)) # Indexes indexes = self.get_indexes(table_name, schema) for index_d in indexes: name = index_d['name'] columns = index_d['column_names'] unique = index_d['unique'] flavor = index_d.get('type', 'unknown type') if include_columns and \ not set(columns).issubset(include_columns): util.warn( "Omitting %s KEY for (%s), key covers omitted columns." % (flavor, ', '.join(columns))) continue # look for columns by orig name in cols_by_orig_name, # but support columns that are in-Python only as fallback sa_schema.Index( name, *[ cols_by_orig_name[c] if c in cols_by_orig_name else table.c[c] for c in columns ], **dict(unique=unique))
def reflecttable(self, connection, table, include_columns): denormalize = self.identifier_preparer._denormalize_name normalize = self.identifier_preparer._normalize_name st = ('SELECT COLUMNNAME, MODE, DATATYPE, CODETYPE, LEN, DEC, ' ' NULLABLE, "DEFAULT", DEFAULTFUNCTION ' 'FROM COLUMNS ' 'WHERE TABLENAME=? AND SCHEMANAME=%s ' 'ORDER BY POS') fk = ('SELECT COLUMNNAME, FKEYNAME, ' ' REFSCHEMANAME, REFTABLENAME, REFCOLUMNNAME, RULE, ' ' (CASE WHEN REFSCHEMANAME = CURRENT_SCHEMA ' ' THEN 1 ELSE 0 END) AS in_schema ' 'FROM FOREIGNKEYCOLUMNS ' 'WHERE TABLENAME=? AND SCHEMANAME=%s ' 'ORDER BY FKEYNAME ') params = [denormalize(table.name)] if not table.schema: st = st % 'CURRENT_SCHEMA' fk = fk % 'CURRENT_SCHEMA' else: st = st % '?' fk = fk % '?' params.append(denormalize(table.schema)) rows = connection.execute(st, params).fetchall() if not rows: raise exc.NoSuchTableError(table.fullname) include_columns = set(include_columns or []) for row in rows: (name, mode, col_type, encoding, length, scale, nullable, constant_def, func_def) = row name = normalize(name) if include_columns and name not in include_columns: continue type_args, type_kw = [], {} if col_type == 'FIXED': type_args = length, scale # Convert FIXED(10) DEFAULT SERIAL to our Integer if (scale == 0 and func_def is not None and func_def.startswith('SERIAL')): col_type = 'INTEGER' type_args = length, elif col_type in 'FLOAT': type_args = length, elif col_type in ('CHAR', 'VARCHAR'): type_args = length, type_kw['encoding'] = encoding elif col_type == 'LONG': type_kw['encoding'] = encoding try: type_cls = ischema_names[col_type.lower()] type_instance = type_cls(*type_args, **type_kw) except KeyError: util.warn("Did not recognize type '%s' of column '%s'" % (col_type, name)) type_instance = sqltypes.NullType col_kw = {'autoincrement': False} col_kw['nullable'] = (nullable == 'YES') col_kw['primary_key'] = (mode == 'KEY') if func_def is not None: if func_def.startswith('SERIAL'): if col_kw['primary_key']: # No special default- let the standard autoincrement # support handle SERIAL pk columns. col_kw['autoincrement'] = True else: # strip current numbering col_kw['server_default'] = schema.DefaultClause( sql.text('SERIAL')) col_kw['autoincrement'] = True else: col_kw['server_default'] = schema.DefaultClause( sql.text(func_def)) elif constant_def is not None: col_kw['server_default'] = schema.DefaultClause( sql.text("'%s'" % constant_def.replace("'", "''"))) table.append_column(schema.Column(name, type_instance, **col_kw)) fk_sets = itertools.groupby(connection.execute(fk, params), lambda row: row.FKEYNAME) for fkeyname, fkey in fk_sets: fkey = list(fkey) if include_columns: key_cols = set([r.COLUMNNAME for r in fkey]) if key_cols != include_columns: continue columns, referants = [], [] quote = self.identifier_preparer._maybe_quote_identifier for row in fkey: columns.append(normalize(row.COLUMNNAME)) if table.schema or not row.in_schema: referants.append('.'.join([ quote(normalize(row[c])) for c in ('REFSCHEMANAME', 'REFTABLENAME', 'REFCOLUMNNAME') ])) else: referants.append('.'.join([ quote(normalize(row[c])) for c in ('REFTABLENAME', 'REFCOLUMNNAME') ])) constraint_kw = {'name': fkeyname.lower()} if fkey[0].RULE is not None: rule = fkey[0].RULE if rule.startswith('DELETE '): rule = rule[7:] constraint_kw['ondelete'] = rule table_kw = {} if table.schema or not row.in_schema: table_kw['schema'] = normalize(fkey[0].REFSCHEMANAME) ref_key = schema._get_table_key(normalize(fkey[0].REFTABLENAME), table_kw.get('schema')) if ref_key not in table.metadata.tables: schema.Table(normalize(fkey[0].REFTABLENAME), table.metadata, autoload=True, autoload_with=connection, **table_kw) constraint = schema.ForeignKeyConstraint(columns, referants, link_to_name=True, **constraint_kw) table.append_constraint(constraint)
def reflecttable(self, connection, table, include_columns): preparer = self.identifier_preparer if table.schema is not None: schema_where_clause = "n.nspname = :schema" schemaname = table.schema if isinstance(schemaname, str): schemaname = schemaname.decode(self.encoding) else: schema_where_clause = "pg_catalog.pg_table_is_visible(c.oid)" schemaname = None SQL_COLS = """ SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) AS DEFAULT, a.attnotnull, a.attnum, a.attrelid as table_oid FROM pg_catalog.pg_attribute a WHERE a.attrelid = ( SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE (%s) AND c.relname = :table_name AND c.relkind in ('r','v') ) AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum """ % schema_where_clause s = sql.text(SQL_COLS, bindparams=[ sql.bindparam('table_name', type_=sqltypes.Unicode), sql.bindparam('schema', type_=sqltypes.Unicode) ], typemap={ 'attname': sqltypes.Unicode, 'default': sqltypes.Unicode }) tablename = table.name if isinstance(tablename, str): tablename = tablename.decode(self.encoding) c = connection.execute(s, table_name=tablename, schema=schemaname) rows = c.fetchall() if not rows: raise exc.NoSuchTableError(table.name) domains = self._load_domains(connection) for name, format_type, default, notnull, attnum, table_oid in rows: if include_columns and name not in include_columns: continue ## strip (30) from character varying(30) attype = re.search('([^\([]+)', format_type).group(1) nullable = not notnull is_array = format_type.endswith('[]') try: charlen = re.search('\(([\d,]+)\)', format_type).group(1) except: charlen = False numericprec = False numericscale = False if attype == 'numeric': if charlen is False: numericprec, numericscale = (None, None) else: numericprec, numericscale = charlen.split(',') charlen = False if attype == 'double precision': numericprec, numericscale = (53, False) charlen = False if attype == 'integer': numericprec, numericscale = (32, 0) charlen = False args = [] for a in (charlen, numericprec, numericscale): if a is None: args.append(None) elif a is not False: args.append(int(a)) kwargs = {} if attype == 'timestamp with time zone': kwargs['timezone'] = True elif attype == 'timestamp without time zone': kwargs['timezone'] = False if attype in ischema_names: coltype = ischema_names[attype] else: if attype in domains: domain = domains[attype] if domain['attype'] in ischema_names: # A table can't override whether the domain is nullable. nullable = domain['nullable'] if domain['default'] and not default: # It can, however, override the default value, but can't set it to null. default = domain['default'] coltype = ischema_names[domain['attype']] else: coltype = None if coltype: coltype = coltype(*args, **kwargs) if is_array: coltype = PGArray(coltype) else: util.warn("Did not recognize type '%s' of column '%s'" % (attype, name)) coltype = sqltypes.NULLTYPE colargs = [] if default is not None: match = re.search(r"""(nextval\(')([^']+)('.*$)""", default) if match is not None: # the default is related to a Sequence sch = table.schema if '.' not in match.group(2) and sch is not None: # unconditionally quote the schema name. this could # later be enhanced to obey quoting rules / "quote schema" default = match.group(1) + ( '"%s"' % sch) + '.' + match.group(2) + match.group(3) colargs.append(schema.DefaultClause(sql.text(default))) table.append_column( schema.Column(name, coltype, nullable=nullable, *colargs)) # Primary keys PK_SQL = """ SELECT attname FROM pg_attribute WHERE attrelid = ( SELECT indexrelid FROM pg_index i WHERE i.indrelid = :table AND i.indisprimary = 't') ORDER BY attnum """ t = sql.text(PK_SQL, typemap={'attname': sqltypes.Unicode}) c = connection.execute(t, table=table_oid) for row in c.fetchall(): pk = row[0] if pk in table.c: col = table.c[pk] table.primary_key.add(col) if col.default is None: col.autoincrement = False # Foreign keys FK_SQL = """ SELECT conname, pg_catalog.pg_get_constraintdef(oid, true) as condef FROM pg_catalog.pg_constraint r WHERE r.conrelid = :table AND r.contype = 'f' ORDER BY 1 """ t = sql.text(FK_SQL, typemap={ 'conname': sqltypes.Unicode, 'condef': sqltypes.Unicode }) c = connection.execute(t, table=table_oid) for conname, condef in c.fetchall(): m = re.search( 'FOREIGN KEY \((.*?)\) REFERENCES (?:(.*?)\.)?(.*?)\((.*?)\)', condef).groups() (constrained_columns, referred_schema, referred_table, referred_columns) = m constrained_columns = [ preparer._unquote_identifier(x) for x in re.split(r'\s*,\s*', constrained_columns) ] if referred_schema: referred_schema = preparer._unquote_identifier(referred_schema) elif table.schema is not None and table.schema == self.get_default_schema_name( connection): # no schema (i.e. its the default schema), and the table we're # reflecting has the default schema explicit, then use that. # i.e. try to use the user's conventions referred_schema = table.schema referred_table = preparer._unquote_identifier(referred_table) referred_columns = [ preparer._unquote_identifier(x) for x in re.split(r'\s*,\s', referred_columns) ] refspec = [] if referred_schema is not None: schema.Table(referred_table, table.metadata, autoload=True, schema=referred_schema, autoload_with=connection) for column in referred_columns: refspec.append(".".join( [referred_schema, referred_table, column])) else: schema.Table(referred_table, table.metadata, autoload=True, autoload_with=connection) for column in referred_columns: refspec.append(".".join([referred_table, column])) table.append_constraint( schema.ForeignKeyConstraint(constrained_columns, refspec, conname, link_to_name=True)) # Indexes IDX_SQL = """ SELECT c.relname, i.indisunique, i.indexprs, i.indpred, a.attname FROM pg_index i, pg_class c, pg_attribute a WHERE i.indrelid = :table AND i.indexrelid = c.oid AND a.attrelid = i.indexrelid AND i.indisprimary = 'f' ORDER BY c.relname, a.attnum """ t = sql.text(IDX_SQL, typemap={'attname': sqltypes.Unicode}) c = connection.execute(t, table=table_oid) indexes = {} sv_idx_name = None for row in c.fetchall(): idx_name, unique, expr, prd, col = row if expr and not idx_name == sv_idx_name: util.warn( "Skipped unsupported reflection of expression-based index %s" % idx_name) sv_idx_name = idx_name continue if prd and not idx_name == sv_idx_name: util.warn( "Predicate of partial index %s ignored during reflection" % idx_name) sv_idx_name = idx_name if not indexes.has_key(idx_name): indexes[idx_name] = [unique, []] indexes[idx_name][1].append(col) for name, (unique, columns) in indexes.items(): schema.Index(name, *[table.columns[c] for c in columns], **dict(unique=unique))
def alter_column( self, table_name: str, column_name: str, nullable: Optional[bool] = None, server_default: Optional[Union["Function", str, bool]] = False, name: Optional[str] = None, type_: Optional["TypeEngine"] = None, autoincrement: None = None, comment: Union[str, "Literal[False]"] = False, **kw ) -> None: existing = self.columns[column_name] existing_transfer: Dict[str, Any] = self.column_transfers[column_name] if name is not None and name != column_name: # note that we don't change '.key' - we keep referring # to the renamed column by its old key in _create(). neat! existing.name = name existing_transfer["name"] = name # pop named constraints for Boolean/Enum for rename if ( "existing_type" in kw and isinstance(kw["existing_type"], SchemaEventTarget) and kw["existing_type"].name # type:ignore[attr-defined] ): self.named_constraints.pop( kw["existing_type"].name, None # type:ignore[attr-defined] ) if type_ is not None: type_ = sqltypes.to_instance(type_) # old type is being discarded so turn off eventing # rules. Alternatively we can # erase the events set up by this type, but this is simpler. # we also ignore the drop_constraint that will come here from # Operations.implementation_for(alter_column) if isinstance(existing.type, SchemaEventTarget): existing.type._create_events = ( # type:ignore[attr-defined] existing.type.create_constraint # type:ignore[attr-defined] # noqa ) = False self.impl.cast_for_batch_migrate( existing, existing_transfer, type_ ) existing.type = type_ # we *dont* however set events for the new type, because # alter_column is invoked from # Operations.implementation_for(alter_column) which already # will emit an add_constraint() if nullable is not None: existing.nullable = nullable if server_default is not False: if server_default is None: existing.server_default = None else: sql_schema.DefaultClause( server_default )._set_parent( # type:ignore[attr-defined] existing ) if autoincrement is not None: existing.autoincrement = bool(autoincrement) if comment is not False: existing.comment = comment
def reflecttable(self, connection, table, include_columns): c = connection.execute( "select distinct OWNER from systables where tabname=?", table.name.lower()) rows = c.fetchall() if not rows: raise exc.NoSuchTableError(table.name) else: if table.owner is not None: if table.owner.lower() in [r[0] for r in rows]: owner = table.owner.lower() else: raise AssertionError( "Specified owner %s does not own table %s" % (table.owner, table.name)) else: if len(rows) == 1: owner = rows[0][0] else: raise AssertionError( "There are multiple tables with name %s in the schema, you must specifie owner" % table.name) c = connection.execute( """select colname , coltype , collength , t3.default , t1.colno from syscolumns as t1 , systables as t2 , OUTER sysdefaults as t3 where t1.tabid = t2.tabid and t2.tabname=? and t2.owner=? and t3.tabid = t2.tabid and t3.colno = t1.colno order by t1.colno""", table.name.lower(), owner) rows = c.fetchall() if not rows: raise exc.NoSuchTableError(table.name) for name, colattr, collength, default, colno in rows: name = name.lower() if include_columns and name not in include_columns: continue # in 7.31, coltype = 0x000 # ^^-- column type # ^-- 1 not null , 0 null nullable, coltype = divmod(colattr, 256) if coltype not in (0, 13) and default: default = default.split()[-1] if coltype == 0 or coltype == 13: # char , varchar coltype = ischema_names.get(coltype, InfoString)(collength) if default: default = "'%s'" % default elif coltype == 5: # decimal precision, scale = (collength & 0xFF00) >> 8, collength & 0xFF if scale == 255: scale = 0 coltype = InfoNumeric(precision, scale) else: try: coltype = ischema_names[coltype] except KeyError: util.warn("Did not recognize type '%s' of column '%s'" % (coltype, name)) coltype = sqltypes.NULLTYPE colargs = [] if default is not None: colargs.append(schema.DefaultClause(sql.text(default))) table.append_column( schema.Column(name, coltype, nullable=(nullable == 0), *colargs)) # FK c = connection.execute( """select t1.constrname as cons_name , t1.constrtype as cons_type , t4.colname as local_column , t7.tabname as remote_table , t6.colname as remote_column from sysconstraints as t1 , systables as t2 , sysindexes as t3 , syscolumns as t4 , sysreferences as t5 , syscolumns as t6 , systables as t7 , sysconstraints as t8 , sysindexes as t9 where t1.tabid = t2.tabid and t2.tabname=? and t2.owner=? and t1.constrtype = 'R' and t3.tabid = t2.tabid and t3.idxname = t1.idxname and t4.tabid = t2.tabid and t4.colno = t3.part1 and t5.constrid = t1.constrid and t8.constrid = t5.primary and t6.tabid = t5.ptabid and t6.colno = t9.part1 and t9.idxname = t8.idxname and t7.tabid = t5.ptabid""", table.name.lower(), owner) rows = c.fetchall() fks = {} for cons_name, cons_type, local_column, remote_table, remote_column in rows: try: fk = fks[cons_name] except KeyError: fk = ([], []) fks[cons_name] = fk refspec = ".".join([remote_table, remote_column]) schema.Table(remote_table, table.metadata, autoload=True, autoload_with=connection) if local_column not in fk[0]: fk[0].append(local_column) if refspec not in fk[1]: fk[1].append(refspec) for name, value in fks.iteritems(): table.append_constraint( schema.ForeignKeyConstraint(value[0], value[1], None, link_to_name=True)) # PK c = connection.execute( """select t1.constrname as cons_name , t1.constrtype as cons_type , t4.colname as local_column from sysconstraints as t1 , systables as t2 , sysindexes as t3 , syscolumns as t4 where t1.tabid = t2.tabid and t2.tabname=? and t2.owner=? and t1.constrtype = 'P' and t3.tabid = t2.tabid and t3.idxname = t1.idxname and t4.tabid = t2.tabid and t4.colno = t3.part1""", table.name.lower(), owner) rows = c.fetchall() for cons_name, cons_type, local_column in rows: table.primary_key.add(table.c[local_column])
def test_server_default_positional(self): target = schema.DefaultClause('y') c = self._fixture(target) assert c.server_default is target assert target.column is c
def reflecttable(self, connection, table, include_columns): # Get base columns if table.schema is not None: current_schema = table.schema else: current_schema = self.get_default_schema_name(connection) s = sql.select([columns, domains], tables.c.table_name==table.name, from_obj=[columns.join(tables).join(domains)], order_by=[columns.c.column_id]) c = connection.execute(s) found_table = False # makes sure we append the columns in the correct order while True: row = c.fetchone() if row is None: break found_table = True (name, type, nullable, charlen, numericprec, numericscale, default, primary_key, max_identity, table_id, column_id) = ( row[columns.c.column_name], row[domains.c.domain_name], row[columns.c.nulls] == 'Y', row[columns.c.width], row[domains.c.precision], row[columns.c.scale], row[columns.c.default], row[columns.c.pkey] == 'Y', row[columns.c.max_identity], row[tables.c.table_id], row[columns.c.column_id], ) if include_columns and name not in include_columns: continue # FIXME: else problems with SybaseBinary(size) if numericscale == 0: numericscale = None args = [] for a in (charlen, numericprec, numericscale): if a is not None: args.append(a) coltype = self.ischema_names.get(type, None) if coltype == SybaseString and charlen == -1: coltype = SybaseText() else: if coltype is None: util.warn("Did not recognize type '%s' of column '%s'" % (type, name)) coltype = sqltypes.NULLTYPE coltype = coltype(*args) colargs = [] if default is not None: colargs.append(schema.DefaultClause(sql.text(default))) # any sequences ? col = schema.Column(name, coltype, nullable=nullable, primary_key=primary_key, *colargs) if int(max_identity) > 0: col.sequence = schema.Sequence(name + '_identity') col.sequence.start = int(max_identity) col.sequence.increment = 1 # append the column table.append_column(col) # any foreign key constraint for this table ? # note: no multi-column foreign keys are considered s = "select st1.table_name, sc1.column_name, st2.table_name, sc2.column_name from systable as st1 join sysfkcol on st1.table_id=sysfkcol.foreign_table_id join sysforeignkey join systable as st2 on sysforeignkey.primary_table_id = st2.table_id join syscolumn as sc1 on sysfkcol.foreign_column_id=sc1.column_id and sc1.table_id=st1.table_id join syscolumn as sc2 on sysfkcol.primary_column_id=sc2.column_id and sc2.table_id=st2.table_id where st1.table_name='%(table_name)s';" % { 'table_name' : table.name } c = connection.execute(s) foreignKeys = {} while True: row = c.fetchone() if row is None: break (foreign_table, foreign_column, primary_table, primary_column) = ( row[0], row[1], row[2], row[3], ) if not primary_table in foreignKeys.keys(): foreignKeys[primary_table] = [['%s' % (foreign_column)], ['%s.%s'%(primary_table, primary_column)]] else: foreignKeys[primary_table][0].append('%s'%(foreign_column)) foreignKeys[primary_table][1].append('%s.%s'%(primary_table, primary_column)) for primary_table in foreignKeys.keys(): #table.append_constraint(schema.ForeignKeyConstraint(['%s.%s'%(foreign_table, foreign_column)], ['%s.%s'%(primary_table,primary_column)])) table.append_constraint(schema.ForeignKeyConstraint(foreignKeys[primary_table][0], foreignKeys[primary_table][1], link_to_name=True)) if not found_table: raise exc.NoSuchTableError(table.name)
def reflecttable(self, saconn, table, include_columns): # see http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html meta = _jdbc_metadata(saconn) ora_tablename = self._denormalize_name(table.name) ora_schema = self._denormalize_name(table.schema) # load columns rs = meta.getColumns(None, ora_schema, ora_tablename, None) while rs.next(): catalog, _, _, raw_colname, java_data_type, data_type, \ column_size, _, decimal_digits, radix, _, remarks, \ default, _, _, char_length, position, is_nullable = _jdbc_row(rs) nullable = is_nullable == 'YES' colname = self._normalize_name(raw_colname) if include_columns and colname not in include_columns: continue try: sa_type = sa_types[java_data_type] coltype = self._colspecs[sa_type] except KeyError: # jdbc OTHER type, or just oracle doing its own thing try: coltype = self._ischema_names[data_type] except KeyError: util.warn("Did not recognize other type '%s' of column '%s'" % (data_type, colname)) coltype = sqltypes.NULLTYPE colargs = [] if default is not None: colargs.append(schema.DefaultClause(sql.text(default))) table.append_column(schema.Column(colname, coltype, nullable=nullable, *colargs)) if not table.columns: raise AssertionError("Couldn't find any column information for table %s" % table.name) # load PK rs = meta.getPrimaryKeys(None, ora_schema, ora_tablename) rows = _jdbc_fetchall(rs) rows.sort(key=lambda row: row[-2]) # sort by key_seq for catalog, _, _, raw_colname, key_seq, pk_name in rows: colname = self._normalize_name(raw_colname) table.primary_key.add(table.c[colname]) # load FKs fks = {} rs = meta.getImportedKeys(None, ora_schema, ora_tablename) rows = _jdbc_fetchall(rs) rows.sort(key=lambda row: (row[11], row[8])) # sort by fk, key_seq for row in rows: # [None, 'HR', 'COUNTRIES', 'COUNTRY_ID', None, 'HR', 'LOCATIONS', 'COUNTRY_ID', 1, None, 1, 'LOC_C_ID_FK', 'COUNTRY_C_ID_PK', 7] fk_catalog, fk_raw_schema, fk_raw_tablename, fk_raw_colname, \ catalog, _, _, raw_colname, \ key_seq, update_rule, delete_rule, \ constraint_name, referenced_constraint_name, deferrability = row colname = self._normalize_name(raw_colname) fk_schema = self._normalize_name(fk_raw_schema) fk_tablename = self._normalize_name(fk_raw_tablename) fk_colname = self._normalize_name(fk_raw_colname) try: fk = fks[constraint_name] except KeyError: fk = ([], []) fks[constraint_name] = fk if fk_tablename is None: # ticket 363 util.warn( ("Got 'None' querying 'table_name' - does the user have " "proper rights to the table?")) continue if fk_schema and fk_schema != table.schema: refspec = ".".join([fk_schema, fk_tablename, fk_colname]) t = schema.Table(fk_tablename, table.metadata, autoload=True, autoload_with=saconn, schema=fk_schema, useexisting=True) else: refspec = ".".join([fk_tablename, fk_colname]) t = schema.Table(fk_tablename, table.metadata, autoload=True, autoload_with=saconn, useexisting=True) if colname not in fk[0]: fk[0].append(colname) if refspec not in fk[1]: fk[1].append(refspec) for name, value in fks.iteritems(): table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1], name=name))
def test_server_default_onupdate_keyword_as_schemaitem(self): target = schema.DefaultClause('y', for_update=True) c = self._fixture(server_onupdate=target) assert c.server_onupdate is target assert target.column is c
def test_server_default_onupdate_positional(self): target = schema.DefaultClause('y', for_update=True) c = self._fixture(target) assert c.server_onupdate is target assert target.column is c
def test_server_default_keyword_as_schemaitem(self): target = schema.DefaultClause('y') c = self._fixture(server_default=target) assert c.server_default is target assert target.column is c
def reflecttable(self, connection, table, include_columns): preparer = self.identifier_preparer resolve_synonyms = table.kwargs.get('oracle_resolve_synonyms', False) if resolve_synonyms: actual_name, owner, dblink, synonym = self._resolve_synonym( connection, desired_owner=self._denormalize_name(table.schema), desired_synonym=self._denormalize_name(table.name)) else: actual_name, owner, dblink, synonym = None, None, None, None if not actual_name: actual_name = self._denormalize_name(table.name) if not dblink: dblink = '' if not owner: owner = self._denormalize_name( table.schema or self.get_default_schema_name(connection)) c = connection.execute( "select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS%(dblink)s where TABLE_NAME = :table_name and OWNER = :owner" % {'dblink': dblink}, { 'table_name': actual_name, 'owner': owner }) while True: row = c.fetchone() if row is None: break (colname, coltype, length, precision, scale, nullable, default) = (self._normalize_name(row[0]), row[1], row[2], row[3], row[4], row[5] == 'Y', row[6]) if include_columns and colname not in include_columns: continue # INTEGER if the scale is 0 and precision is null # NUMBER if the scale and precision are both null # NUMBER(9,2) if the precision is 9 and the scale is 2 # NUMBER(3) if the precision is 3 and scale is 0 #length is ignored except for CHAR and VARCHAR2 if coltype == 'NUMBER': if precision is None and scale is None: coltype = OracleNumeric elif precision is None and scale == 0: coltype = OracleInteger else: coltype = OracleNumeric(precision, scale) elif coltype == 'CHAR' or coltype == 'VARCHAR2': coltype = ischema_names.get(coltype, OracleString)(length) else: coltype = re.sub(r'\(\d+\)', '', coltype) try: coltype = ischema_names[coltype] except KeyError: util.warn("Did not recognize type '%s' of column '%s'" % (coltype, colname)) coltype = sqltypes.NULLTYPE colargs = [] if default is not None: colargs.append(schema.DefaultClause(sql.text(default))) table.append_column( schema.Column(colname, coltype, nullable=nullable, *colargs)) if not table.columns: raise AssertionError( "Couldn't find any column information for table %s" % actual_name) c = connection.execute( """SELECT ac.constraint_name, ac.constraint_type, loc.column_name AS local_column, rem.table_name AS remote_table, rem.column_name AS remote_column, rem.owner AS remote_owner FROM all_constraints%(dblink)s ac, all_cons_columns%(dblink)s loc, all_cons_columns%(dblink)s rem WHERE ac.table_name = :table_name AND ac.constraint_type IN ('R','P') AND ac.owner = :owner AND ac.owner = loc.owner AND ac.constraint_name = loc.constraint_name AND ac.r_owner = rem.owner(+) AND ac.r_constraint_name = rem.constraint_name(+) -- order multiple primary keys correctly ORDER BY ac.constraint_name, loc.position, rem.position""" % {'dblink': dblink}, { 'table_name': actual_name, 'owner': owner }) fks = {} while True: row = c.fetchone() if row is None: break #print "ROW:" , row (cons_name, cons_type, local_column, remote_table, remote_column, remote_owner) = row[0:2] + tuple( [self._normalize_name(x) for x in row[2:]]) if cons_type == 'P': table.primary_key.add(table.c[local_column]) elif cons_type == 'R': try: fk = fks[cons_name] except KeyError: fk = ([], []) fks[cons_name] = fk if remote_table is None: # ticket 363 util.warn( ("Got 'None' querying 'table_name' from " "all_cons_columns%(dblink)s - does the user have " "proper rights to the table?") % {'dblink': dblink}) continue if resolve_synonyms: ref_remote_name, ref_remote_owner, ref_dblink, ref_synonym = self._resolve_synonym( connection, desired_owner=self._denormalize_name(remote_owner), desired_table=self._denormalize_name(remote_table)) if ref_synonym: remote_table = self._normalize_name(ref_synonym) remote_owner = self._normalize_name(ref_remote_owner) if not table.schema and self._denormalize_name( remote_owner) == owner: refspec = ".".join([remote_table, remote_column]) t = schema.Table(remote_table, table.metadata, autoload=True, autoload_with=connection, oracle_resolve_synonyms=resolve_synonyms, useexisting=True) else: refspec = ".".join([ x for x in [remote_owner, remote_table, remote_column] if x ]) t = schema.Table(remote_table, table.metadata, autoload=True, autoload_with=connection, schema=remote_owner, oracle_resolve_synonyms=resolve_synonyms, useexisting=True) if local_column not in fk[0]: fk[0].append(local_column) if refspec not in fk[1]: fk[1].append(refspec) for name, value in fks.iteritems(): table.append_constraint( schema.ForeignKeyConstraint(value[0], value[1], name=name, link_to_name=True))
def reflecttable(self, connection, table, include_columns): # This is defined in the function, as it relies on win32com constants, # that aren't imported until dbapi method is called if not hasattr(self, 'ischema_names'): self.ischema_names = { const.dbByte: AcBinary, const.dbInteger: AcInteger, const.dbLong: AcInteger, const.dbSingle: AcFloat, const.dbDouble: AcFloat, const.dbDate: AcDateTime, const.dbLongBinary: AcBinary, const.dbMemo: AcText, const.dbBoolean: AcBoolean, const.dbText: AcUnicode, # All Access strings are unicode const.dbCurrency: AcNumeric, } # A fresh DAO connection is opened for each reflection # This is necessary, so we get the latest updates dtbs = daoEngine.OpenDatabase(connection.engine.url.database) try: for tbl in dtbs.TableDefs: if tbl.Name.lower() == table.name.lower(): break else: raise exc.NoSuchTableError(table.name) for col in tbl.Fields: coltype = self.ischema_names[col.Type] if col.Type == const.dbText: coltype = coltype(col.Size) colargs = \ { 'nullable': not(col.Required or col.Attributes & const.dbAutoIncrField), } default = col.DefaultValue if col.Attributes & const.dbAutoIncrField: colargs['default'] = schema.Sequence(col.Name + '_seq') elif default: if col.Type == const.dbBoolean: default = default == 'Yes' and '1' or '0' colargs['server_default'] = schema.DefaultClause( sql.text(default)) table.append_column(schema.Column(col.Name, coltype, **colargs)) # TBD: check constraints # Find primary key columns first for idx in tbl.Indexes: if idx.Primary: for col in idx.Fields: thecol = table.c[col.Name] table.primary_key.add(thecol) if isinstance(thecol.type, AcInteger) and \ not (thecol.default and isinstance(thecol.default.arg, schema.Sequence)): thecol.autoincrement = False # Then add other indexes for idx in tbl.Indexes: if not idx.Primary: if len(idx.Fields) == 1: col = table.c[idx.Fields[0].Name] if not col.primary_key: col.index = True col.unique = idx.Unique else: pass # TBD: multi-column indexes for fk in dtbs.Relations: if fk.ForeignTable != table.name: continue scols = [c.ForeignName for c in fk.Fields] rcols = ['%s.%s' % (fk.Table, c.Name) for c in fk.Fields] table.append_constraint( schema.ForeignKeyConstraint(scols, rcols, link_to_name=True)) finally: dtbs.Close()
def reflecttable(self, connection, table, include_columns): # Query to extract the details of all the fields of the given table tblqry = """ SELECT DISTINCT r.rdb$field_name AS fname, r.rdb$null_flag AS null_flag, t.rdb$type_name AS ftype, f.rdb$field_sub_type AS stype, f.rdb$field_length AS flen, f.rdb$field_precision AS fprec, f.rdb$field_scale AS fscale, COALESCE(r.rdb$default_source, f.rdb$default_source) AS fdefault FROM rdb$relation_fields r JOIN rdb$fields f ON r.rdb$field_source=f.rdb$field_name JOIN rdb$types t ON t.rdb$type=f.rdb$field_type AND t.rdb$field_name='RDB$FIELD_TYPE' WHERE f.rdb$system_flag=0 AND r.rdb$relation_name=? ORDER BY r.rdb$field_position """ # Query to extract the PK/FK constrained fields of the given table keyqry = """ SELECT se.rdb$field_name AS fname FROM rdb$relation_constraints rc JOIN rdb$index_segments se ON rc.rdb$index_name=se.rdb$index_name WHERE rc.rdb$constraint_type=? AND rc.rdb$relation_name=? """ # Query to extract the details of each UK/FK of the given table fkqry = """ SELECT rc.rdb$constraint_name AS cname, cse.rdb$field_name AS fname, ix2.rdb$relation_name AS targetrname, se.rdb$field_name AS targetfname FROM rdb$relation_constraints rc JOIN rdb$indices ix1 ON ix1.rdb$index_name=rc.rdb$index_name JOIN rdb$indices ix2 ON ix2.rdb$index_name=ix1.rdb$foreign_key JOIN rdb$index_segments cse ON cse.rdb$index_name=ix1.rdb$index_name JOIN rdb$index_segments se ON se.rdb$index_name=ix2.rdb$index_name AND se.rdb$field_position=cse.rdb$field_position WHERE rc.rdb$constraint_type=? AND rc.rdb$relation_name=? ORDER BY se.rdb$index_name, se.rdb$field_position """ # Heuristic-query to determine the generator associated to a PK field genqry = """ SELECT trigdep.rdb$depended_on_name AS fgenerator FROM rdb$dependencies tabdep JOIN rdb$dependencies trigdep ON (tabdep.rdb$dependent_name=trigdep.rdb$dependent_name AND trigdep.rdb$depended_on_type=14 AND trigdep.rdb$dependent_type=2) JOIN rdb$triggers trig ON (trig.rdb$trigger_name=tabdep.rdb$dependent_name) WHERE tabdep.rdb$depended_on_name=? AND tabdep.rdb$depended_on_type=0 AND trig.rdb$trigger_type=1 AND tabdep.rdb$field_name=? AND (SELECT count(*) FROM rdb$dependencies trigdep2 WHERE trigdep2.rdb$dependent_name = trigdep.rdb$dependent_name) = 2 """ tablename = self._denormalize_name(table.name) # get primary key fields c = connection.execute(keyqry, ["PRIMARY KEY", tablename]) pkfields = [self._normalize_name(r['fname']) for r in c.fetchall()] # get all of the fields for this table c = connection.execute(tblqry, [tablename]) found_table = False while True: row = c.fetchone() if row is None: break found_table = True name = self._normalize_name(row['fname']) if include_columns and name not in include_columns: continue args = [name] kw = {} # get the data type coltype = ischema_names.get(row['ftype'].rstrip()) if coltype is None: util.warn("Did not recognize type '%s' of column '%s'" % (str(row['ftype']), name)) coltype = sqltypes.NULLTYPE else: coltype = coltype(row) args.append(coltype) # is it a primary key? kw['primary_key'] = name in pkfields # is it nullable? kw['nullable'] = not bool(row['null_flag']) # does it have a default value? if row['fdefault'] is not None: # the value comes down as "DEFAULT 'value'" assert row['fdefault'].upper().startswith('DEFAULT '), row defvalue = row['fdefault'][8:] args.append(schema.DefaultClause(sql.text(defvalue))) col = schema.Column(*args, **kw) if kw['primary_key']: # if the PK is a single field, try to see if its linked to # a sequence thru a trigger if len(pkfields)==1: genc = connection.execute(genqry, [tablename, row['fname']]) genr = genc.fetchone() if genr is not None: col.sequence = schema.Sequence(self._normalize_name(genr['fgenerator'])) table.append_column(col) if not found_table: raise exc.NoSuchTableError(table.name) # get the foreign keys c = connection.execute(fkqry, ["FOREIGN KEY", tablename]) fks = {} while True: row = c.fetchone() if not row: break cname = self._normalize_name(row['cname']) try: fk = fks[cname] except KeyError: fks[cname] = fk = ([], []) rname = self._normalize_name(row['targetrname']) schema.Table(rname, table.metadata, autoload=True, autoload_with=connection) fname = self._normalize_name(row['fname']) refspec = rname + '.' + self._normalize_name(row['targetfname']) fk[0].append(fname) fk[1].append(refspec) for name, value in fks.iteritems(): table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1], name=name, link_to_name=True))
def reflecttable(self, table, include_columns): dialect = self.conn.dialect # MySQL dialect does this. Applicable with other dialects? if hasattr(dialect, '_connection_charset') \ and hasattr(dialect, '_adjust_casing'): charset = dialect._connection_charset dialect._adjust_casing(table) # table attributes we might need. reflection_options = dict( (k, table.kwargs.get(k)) for k in dialect.reflection_options if k in table.kwargs) schema = table.schema table_name = table.name # apply table options tbl_opts = self.get_table_options(table_name, schema, **table.kwargs) if tbl_opts: table.kwargs.update(tbl_opts) # table.kwargs will need to be passed to each reflection method. Make # sure keywords are strings. tblkw = table.kwargs.copy() for (k, v) in tblkw.items(): del tblkw[k] tblkw[str(k)] = v # Py2K if isinstance(schema, str): schema = schema.decode(dialect.encoding) if isinstance(table_name, str): table_name = table_name.decode(dialect.encoding) # end Py2K # columns found_table = False for col_d in self.get_columns(table_name, schema, **tblkw): found_table = True name = col_d['name'] if include_columns and name not in include_columns: continue coltype = col_d['type'] col_kw = { 'nullable':col_d['nullable'], } if 'autoincrement' in col_d: col_kw['autoincrement'] = col_d['autoincrement'] if 'quote' in col_d: col_kw['quote'] = col_d['quote'] colargs = [] if col_d.get('default') is not None: # the "default" value is assumed to be a literal SQL expression, # so is wrapped in text() so that no quoting occurs on re-issuance. colargs.append(sa_schema.DefaultClause(sql.text(col_d['default']))) if 'sequence' in col_d: # TODO: mssql, maxdb and sybase are using this. seq = col_d['sequence'] sequence = sa_schema.Sequence(seq['name'], 1, 1) if 'start' in seq: sequence.start = seq['start'] if 'increment' in seq: sequence.increment = seq['increment'] colargs.append(sequence) col = sa_schema.Column(name, coltype, *colargs, **col_kw) table.append_column(col) if not found_table: raise exc.NoSuchTableError(table.name) # Primary keys pk_cons = self.get_pk_constraint(table_name, schema, **tblkw) if pk_cons: primary_key_constraint = sa_schema.PrimaryKeyConstraint(name=pk_cons.get('name'), *[table.c[pk] for pk in pk_cons['constrained_columns'] if pk in table.c] ) table.append_constraint(primary_key_constraint) # Foreign keys fkeys = self.get_foreign_keys(table_name, schema, **tblkw) for fkey_d in fkeys: conname = fkey_d['name'] constrained_columns = fkey_d['constrained_columns'] referred_schema = fkey_d['referred_schema'] referred_table = fkey_d['referred_table'] referred_columns = fkey_d['referred_columns'] refspec = [] if referred_schema is not None: sa_schema.Table(referred_table, table.metadata, autoload=True, schema=referred_schema, autoload_with=self.conn, **reflection_options ) for column in referred_columns: refspec.append(".".join( [referred_schema, referred_table, column])) else: sa_schema.Table(referred_table, table.metadata, autoload=True, autoload_with=self.conn, **reflection_options ) for column in referred_columns: refspec.append(".".join([referred_table, column])) table.append_constraint( sa_schema.ForeignKeyConstraint(constrained_columns, refspec, conname, link_to_name=True)) # Indexes indexes = self.get_indexes(table_name, schema) for index_d in indexes: name = index_d['name'] columns = index_d['column_names'] unique = index_d['unique'] flavor = index_d.get('type', 'unknown type') if include_columns and \ not set(columns).issubset(include_columns): util.warn( "Omitting %s KEY for (%s), key covers omitted columns." % (flavor, ', '.join(columns))) continue sa_schema.Index(name, *[table.columns[c] for c in columns], **dict(unique=unique))