def _make_index(params, conn_table):
return sa_schema.Index(
params['name'],
*[conn_table.c[cname] for cname in params['column_names']],
unique=params['unique'])
def register_schema(providers: List[str],
db_name: str,
schema_base: DeclarativeMeta,
entity_type: str = 'stock'):
"""
function for register schema,please declare them before register
:param providers: the supported providers for the schema
:type providers:
:param db_name: database name for the schema
:type db_name:
:param schema_base:
:type schema_base:
:param entity_type: the schema related entity_type
:type entity_type:
:return:
:rtype:
"""
schemas = []
for item in schema_base._decl_class_registry.items():
cls = item[1]
if type(cls) == DeclarativeMeta:
if dbname_map_schemas.get(db_name):
schemas = dbname_map_schemas[db_name]
global_schemas.append(cls)
add_to_map_list(the_map=entity_map_schemas,
key=entity_type,
value=cls)
schemas.append(cls)
dbname_map_schemas[db_name] = schemas
for provider in providers:
# track in in _providers
if provider not in global_providers:
global_providers.append(provider)
if not provider_map_dbnames.get(provider):
provider_map_dbnames[provider] = []
provider_map_dbnames[provider].append(db_name)
dbname_map_base[db_name] = schema_base
# create the db & table
engine = get_db_engine(provider, db_name=db_name)
schema_base.metadata.create_all(engine)
session_fac = get_db_session_factory(provider, db_name=db_name)
session_fac.configure(bind=engine)
for provider in providers:
engine = get_db_engine(provider, db_name=db_name)
# create index for 'timestamp','entity_id','code','report_period','updated_timestamp
for table_name, table in iter(schema_base.metadata.tables.items()):
index_list = []
with engine.connect() as con:
rs = con.execute("PRAGMA INDEX_LIST('{}')".format(table_name))
for row in rs:
index_list.append(row[1])
logger.debug('engine:{},table:{},index:{}'.format(
engine, table_name, index_list))
for col in [
'timestamp', 'entity_id', 'code', 'report_period',
'created_timestamp', 'updated_timestamp'
]:
if col in table.c:
column = eval('table.c.{}'.format(col))
index = schema.Index('{}_{}_index'.format(table_name, col),
column)
if index.name not in index_list:
index.create(engine)
for cols in [('timestamp', 'entity_id'), ('timestamp', 'code')]:
if (cols[0] in table.c) and (col[1] in table.c):
column0 = eval('table.c.{}'.format(col[0]))
column1 = eval('table.c.{}'.format(col[1]))
index = schema.Index(
'{}_{}_{}_index'.format(table_name, col[0], col[1]),
column0, column1)
if index.name not in index_list:
index.create(engine)
def _make_index(params, conn_table):
# TODO: add .info such as 'duplicates_constraint'
return sa_schema.Index(
params['name'],
*[conn_table.c[cname] for cname in params['column_names']],
unique=params['unique'])
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
elif attype == 'double precision':
numericprec, numericscale = (True, False)
charlen = False
elif 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
coltype = None
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']]
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:
if 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 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 = 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
table.dispatch.column_reflect(table, col_d)
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, 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:
pk_cols = [
table.c[pk] for pk in pk_cons['constrained_columns']
if pk in table.c and pk not in exclude_columns
] + [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']
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.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]))
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))
def Index(*args, **kws):
index = schema.Index(*args, **kws)
db.metadata.__dict__.setdefault("index_list", []).append(index)
def _index(self, name, tablename, columns, schema=None, **kw):
t = sa_schema.Table(tablename or 'no_table',
sa_schema.MetaData(),
*[sa_schema.Column(n, NULLTYPE) for n in columns],
schema=schema)
return sa_schema.Index(name, *list(t.c), **kw)
def createDB(self):
"""
Step through and create the DB structure, relationships and constraints
**Note that order matters here, have to define a Table before you can link to it**
TODO this can/should all be redone using the new syntax and relations
see: http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html# for
some examples.
NOTE: if one stops using sqlite then change file_id, logging_id and file_logging_id
to BigIntegers (sqlite doesn't know BigInteger)
"""
self.init_db(self.user, self.password, self.db_name)
metadata = self.metadata
data_table = schema.Table('mission',
metadata,
schema.Column('mission_id',
types.Integer,
autoincrement=True,
primary_key=True,
nullable=False),
schema.Column('mission_name',
types.String(20),
nullable=False,
unique=True),
schema.Column(
'rootdir',
types.String(150),
nullable=False,
),
schema.Column(
'incoming_dir',
types.String(150),
nullable=False,
),
schema.Column(
'codedir',
types.String(150),
nullable=True,
),
schema.Column(
'inspectordir',
types.String(150),
nullable=True,
),
schema.Column(
'errordir',
types.String(150),
nullable=True,
),
extend_existing=True)
data_table = schema.Table(
'satellite',
metadata,
schema.Column('satellite_id',
types.Integer,
autoincrement=True,
primary_key=True,
nullable=False),
schema.Column('satellite_name', types.String(20),
nullable=False), # hmm long enough?
schema.Column(
'mission_id',
types.Integer,
schema.ForeignKey('mission.mission_id'),
nullable=False,
),
schema.UniqueConstraint('satellite_name',
'mission_id',
name='unique_pairs_satellite'),
extend_existing=True)
data_table = schema.Table(
'instrument',
metadata,
schema.Column('instrument_id',
types.Integer,
autoincrement=True,
primary_key=True,
nullable=False),
schema.Column('instrument_name', types.String(20), nullable=False),
# hmm long enough?
schema.Column(
'satellite_id',
types.Integer,
schema.ForeignKey('satellite.satellite_id'),
nullable=False,
),
schema.UniqueConstraint('instrument_name',
'satellite_id',
name='unique_pairs_instrument'),
extend_existing=True)
data_table = schema.Table(
'product',
metadata,
schema.Column('product_id',
types.Integer,
autoincrement=True,
primary_key=True,
nullable=False,
index=True),
schema.Column('product_name',
types.String(100),
nullable=False,
index=True),
# hmm long enough?
schema.Column(
'instrument_id',
types.Integer,
schema.ForeignKey('instrument.instrument_id'),
nullable=False,
),
schema.Column('relative_path', types.String(100),
nullable=False), # hmm long enough?
schema.Column('level', types.Float, nullable=False),
schema.Column('format', types.Text,
nullable=False), # hmm long enough?
schema.Column('product_description', types.Text,
nullable=True), # hmm long enough?
schema.UniqueConstraint('product_name',
'instrument_id',
'relative_path',
name='unique_triplet_product'),
extend_existing=True)
data_table = schema.Table(
'instrumentproductlink',
metadata,
schema.Column('instrument_id',
types.Integer,
schema.ForeignKey('instrument.instrument_id'),
nullable=False),
schema.Column('product_id',
types.Integer,
schema.ForeignKey('product.product_id'),
nullable=False),
schema.PrimaryKeyConstraint('instrument_id', 'product_id'),
extend_existing=True)
data_table = schema.Table(
'process',
metadata,
schema.Column('process_id',
types.Integer,
autoincrement=True,
primary_key=True,
nullable=False,
index=True),
schema.Column('process_name', types.String(50),
nullable=False), # hmm long enough?
schema.Column('output_product',
types.Integer,
schema.ForeignKey('product.product_id'),
nullable=True,
index=True),
schema.Column('output_timebase',
types.String(10),
nullable=True,
index=True),
schema.Column('extra_params', types.Text, nullable=True),
schema.UniqueConstraint('process_name', 'output_product'),
extend_existing=True)
data_table = schema.Table(
'productprocesslink',
metadata,
schema.Column('process_id',
types.Integer,
schema.ForeignKey('process.process_id'),
nullable=False),
schema.Column('input_product_id',
types.Integer,
schema.ForeignKey('product.product_id'),
nullable=False),
schema.Column('optional', types.Boolean, nullable=False),
# schema.Column('yesterday', types.Integer, nullable=False),
# schema.Column('tomorrow', types.Integer, nullable=False),
schema.PrimaryKeyConstraint('process_id', 'input_product_id'),
extend_existing=True)
data_table = schema.Table(
'file',
metadata,
# this was a bigint, sqlalchemy doesn't seem to like this... think here
schema.Column('file_id',
types.Integer,
autoincrement=True,
primary_key=True,
nullable=False,
index=True),
schema.Column('filename',
types.String(250),
nullable=False,
unique=True,
index=True),
schema.Column('utc_file_date',
types.Date,
nullable=True,
index=True),
schema.Column('utc_start_time',
types.DateTime,
nullable=True,
index=True),
schema.Column('utc_stop_time',
types.DateTime,
nullable=True,
index=True),
schema.Column('data_level',
types.Float,
nullable=False,
index=True),
schema.Column('interface_version',
types.SmallInteger,
nullable=False),
schema.Column('quality_version',
types.SmallInteger,
nullable=False),
schema.Column('revision_version',
types.SmallInteger,
nullable=False),
schema.Column('verbose_provenance', types.Text, nullable=True),
schema.Column('check_date', types.DateTime, nullable=True),
schema.Column('quality_comment', types.Text, nullable=True),
schema.Column('caveats', types.Text, nullable=True),
schema.Column('file_create_date', types.DateTime, nullable=False),
schema.Column('met_start_time', types.Float, nullable=True),
schema.Column('met_stop_time', types.Float, nullable=True),
schema.Column('exists_on_disk', types.Boolean, nullable=False),
schema.Column('quality_checked',
types.Boolean,
nullable=True,
default=False),
schema.Column('product_id',
types.Integer,
schema.ForeignKey('product.product_id'),
nullable=False),
schema.Column('shasum', types.String(40), nullable=True),
schema.Column('process_keywords', types.Text, nullable=True),
schema.CheckConstraint(
'utc_stop_time is not NULL OR met_stop_time is not NULL'),
schema.CheckConstraint(
'utc_start_time is not NULL OR met_start_time is not NULL'),
schema.CheckConstraint(
'met_start_time <= met_stop_time'), # in case of one entry
schema.CheckConstraint(
'utc_start_time <= utc_stop_time'), # in case of one entry
schema.CheckConstraint('interface_version >= 1'),
schema.UniqueConstraint('utc_file_date',
'product_id',
'interface_version',
'quality_comment',
'revision_version',
name='Unique file tuple'),
extend_existing=True)
schema.Index('ix_file_big',
data_table.columns['filename'],
data_table.columns['utc_file_date'],
data_table.columns['utc_start_time'],
data_table.columns['utc_stop_time'],
unique=True)
data_table = schema.Table(
'unixtime',
metadata,
schema.Column('file_id',
types.Integer,
schema.ForeignKey('file.file_id'),
primary_key=True,
index=True),
schema.Column('unix_start', types.Integer, index=True),
schema.Column('unix_stop', types.Integer, index=True),
schema.CheckConstraint('unix_start <= unix_stop'),
)
data_table = schema.Table(
'filefilelink',
metadata,
schema.Column('source_file',
types.Integer,
schema.ForeignKey('file.file_id'),
nullable=False,
index=True),
schema.Column('resulting_file',
types.Integer,
schema.ForeignKey('file.file_id'),
nullable=False,
index=True),
schema.PrimaryKeyConstraint('source_file', 'resulting_file'),
schema.CheckConstraint('source_file <> resulting_file'),
# TODO this is supposed to be more general than !=
extend_existing=True)
data_table = schema.Table(
'code',
metadata,
schema.Column('code_id',
types.Integer,
autoincrement=True,
primary_key=True,
nullable=False,
index=True),
schema.Column('filename',
types.String(250),
nullable=False,
unique=False),
schema.Column('relative_path', types.String(100), nullable=False),
schema.Column('code_start_date', types.Date, nullable=False),
schema.Column('code_stop_date', types.Date, nullable=False),
schema.Column('code_description', types.Text, nullable=False),
schema.Column('process_id',
types.Integer,
schema.ForeignKey('process.process_id'),
nullable=False,
index=True),
schema.Column('interface_version',
types.SmallInteger,
nullable=False),
schema.Column('quality_version',
types.SmallInteger,
nullable=False),
schema.Column('revision_version',
types.SmallInteger,
nullable=False),
schema.Column('output_interface_version',
types.SmallInteger,
nullable=False),
schema.Column('active_code',
types.Boolean,
nullable=False,
default=False),
schema.Column('date_written', types.Date, nullable=False),
schema.Column('shasum', types.String(40), nullable=True),
schema.Column('newest_version', types.Boolean, nullable=False),
schema.Column('arguments', types.Text, nullable=True),
schema.Column('ram', types.Float,
nullable=True), # amanount of ram used in Gigs
schema.Column('cpu', types.SmallInteger,
nullable=True), # number of cpus used
schema.CheckConstraint('code_start_date <= code_stop_date'),
schema.CheckConstraint('interface_version >= 1'),
schema.CheckConstraint('output_interface_version >= 1'),
extend_existing=True)
data_table = schema.Table(
'processqueue',
metadata,
schema.Column('file_id',
types.Integer,
schema.ForeignKey('file.file_id'),
primary_key=True,
nullable=False,
unique=True,
index=True),
schema.Column('version_bump', types.SmallInteger, nullable=True),
schema.Column('instrument_id',
types.Integer,
schema.ForeignKey('instrument.instrument_id'),
nullable=False),
schema.CheckConstraint('version_bump is NULL or version_bump < 3'),
extend_existing=True)
data_table = schema.Table(
'filecodelink',
metadata,
schema.Column('resulting_file',
types.Integer,
schema.ForeignKey('file.file_id'),
nullable=False),
schema.Column('source_code',
types.Integer,
schema.ForeignKey('code.code_id'),
nullable=False),
schema.PrimaryKeyConstraint('resulting_file', 'source_code'),
extend_existing=True)
data_table = schema.Table('release',
metadata,
schema.Column(
'file_id',
types.Integer,
schema.ForeignKey('file.file_id'),
nullable=False,
),
schema.Column('release_num',
types.String(20),
nullable=False),
schema.PrimaryKeyConstraint(
'file_id', 'release_num'),
extend_existing=True)
data_table = schema.Table(
'processpidlink', metadata,
schema.Column('ppl_id',
types.Integer,
autoincrement=True,
primary_key=True,
nullable=False),
schema.Column('pid', types.Integer, nullable=True),
schema.Column('hostname', types.String(100), nullable=True),
schema.Column('process_id',
types.Integer,
schema.ForeignKey('process.process_id'),
nullable=True),
schema.Column('currentlyprocessing',
types.Boolean,
nullable=True,
default='f'),
schema.Column('start_time',
types.DateTime,
nullable=True,
default=func.now()),
schema.Column('end_time',
types.DateTime,
nullable=True,
default=func.now()))
data_table = schema.Table(
'logging',
metadata,
schema.Column('logging_id',
types.Integer,
autoincrement=True,
primary_key=True,
nullable=False),
schema.Column('currently_processing',
types.Boolean,
nullable=False,
default=False),
schema.Column('pid', types.Integer, nullable=True),
schema.Column('processing_start_time',
types.DateTime,
nullable=False),
# might have to be a TIMESTAMP
schema.Column('processing_end_time', types.DateTime,
nullable=True),
schema.Column('comment', types.Text, nullable=True),
schema.Column('mission_id',
types.Integer,
schema.ForeignKey('mission.mission_id'),
nullable=False),
schema.Column('user', types.String(30), nullable=False),
schema.Column('hostname', types.String(100), nullable=False),
# schema.PrimaryKeyConstraint('logging_id'),
schema.CheckConstraint(
'processing_start_time < processing_end_time'),
extend_existing=True)
data_table = schema.Table(
'logging_file',
metadata,
schema.Column('logging_file_id',
types.Integer,
autoincrement=True,
primary_key=True,
nullable=False),
schema.Column('logging_id',
types.Integer,
schema.ForeignKey('logging.logging_id'),
nullable=False),
schema.Column('file_id',
types.Integer,
schema.ForeignKey('file.file_id'),
nullable=False),
schema.Column('code_id',
types.Integer,
schema.ForeignKey('code.code_id'),
nullable=False),
schema.Column('comments', types.Text, nullable=True),
# schema.PrimaryKeyConstraint('logging_file_id'),
extend_existing=True)
data_table = schema.Table(
'inspector',
metadata,
schema.Column('inspector_id',
types.Integer,
autoincrement=True,
primary_key=True,
nullable=False,
index=True),
schema.Column('filename',
types.String(250),
nullable=False,
unique=False),
schema.Column('relative_path', types.String(250), nullable=False),
schema.Column('description', types.Text, nullable=False),
schema.Column('interface_version',
types.SmallInteger,
nullable=False),
schema.Column('quality_version',
types.SmallInteger,
nullable=False),
schema.Column('revision_version',
types.SmallInteger,
nullable=False),
schema.Column('output_interface_version',
types.SmallInteger,
nullable=False),
schema.Column('active_code',
types.Boolean,
nullable=False,
default=False,
index=True),
schema.Column('date_written', types.Date, nullable=False),
schema.Column('shasum', types.String(40), nullable=True),
schema.Column('newest_version',
types.Boolean,
nullable=False,
index=True),
schema.Column('arguments', types.Text, nullable=True),
schema.Column('product',
types.Integer,
schema.ForeignKey('product.product_id'),
nullable=False),
schema.CheckConstraint('interface_version >= 1'),
schema.CheckConstraint('output_interface_version >= 1'),
extend_existing=True)
# TODO move this out so that the user chooses the db type
# engine = create_engine('postgres:///' + self.filename, echo=False)
# metadata.bind = engine
metadata.create_all(checkfirst=True)
def _index(self, name, tablename, columns, schema=None, **kw):
t = sa_schema.Table(tablename or 'no_table',
self._metadata(),
*[sa_schema.Column(n, NULLTYPE) for n in columns],
schema=schema)
return sa_schema.Index(name, *[t.c[n] for n in columns], **kw)
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))
