def reflecttable(self, connection, table, include_columns):
preparer = self.identifier_preparer
if table.schema is None:
pragma = "PRAGMA "
else:
pragma = "PRAGMA %s." % preparer.quote_identifier(table.schema)
qtable = preparer.format_table(table, False)
c = connection.execute("%stable_info(%s)" % (pragma, qtable))
found_table = False
while True:
row = c.fetchone()
if row is None:
break
found_table = True
(name, type_, nullable, default, has_default,
primary_key) = (row[1], row[2].upper(), not row[3], row[4], row[4]
is not None, row[5])
name = re.sub(r'^\"|\"$', '', name)
if include_columns and name not in include_columns:
continue
match = re.match(r'(\w+)(\(.*?\))?', type_)
if match:
coltype = match.group(1)
args = match.group(2)
else:
coltype = "VARCHAR"
args = ''
try:
coltype = ischema_names[coltype]
except KeyError:
util.warn("Did not recognize type '%s' of column '%s'" %
(coltype, name))
coltype = sqltypes.NullType
if args is not None:
args = re.findall(r'(\d+)', args)
coltype = coltype(*[int(a) for a in args])
colargs = []
if has_default:
colargs.append(DefaultClause(sql.text(default)))
table.append_column(
schema.Column(name,
coltype,
primary_key=primary_key,
nullable=nullable,
*colargs))
if not found_table:
raise exc.NoSuchTableError(table.name)
c = connection.execute("%sforeign_key_list(%s)" % (pragma, qtable))
fks = {}
while True:
row = c.fetchone()
if row is None:
break
(constraint_name, tablename, localcol,
remotecol) = (row[0], row[2], row[3], row[4])
tablename = re.sub(r'^\"|\"$', '', tablename)
localcol = re.sub(r'^\"|\"$', '', localcol)
remotecol = re.sub(r'^\"|\"$', '', remotecol)
try:
fk = fks[constraint_name]
except KeyError:
fk = ([], [])
fks[constraint_name] = fk
# look up the table based on the given table's engine, not 'self',
# since it could be a ProxyEngine
remotetable = schema.Table(tablename,
table.metadata,
autoload=True,
autoload_with=connection)
constrained_column = table.c[localcol].name
refspec = ".".join([tablename, remotecol])
if constrained_column not in fk[0]:
fk[0].append(constrained_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],
link_to_name=True))
# check for UNIQUE indexes
c = connection.execute("%sindex_list(%s)" % (pragma, qtable))
unique_indexes = []
while True:
row = c.fetchone()
if row is None:
break
if (row[2] == 1):
unique_indexes.append(row[1])
# loop thru unique indexes for one that includes the primary key
for idx in unique_indexes:
c = connection.execute("%sindex_info(%s)" % (pragma, idx))
cols = []
while True:
row = c.fetchone()
if row is None:
break
cols.append(row[2])
def test_autoincrement(self, metadata, connection):
Table(
"ai_1",
metadata,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_2",
metadata,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_3",
metadata,
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
)
Table(
"ai_4",
metadata,
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
Column("int_n2", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_5",
metadata,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_6",
metadata,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
)
Table(
"ai_7",
metadata,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, autoincrement=True, primary_key=True),
)
Table(
"ai_8",
metadata,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
)
metadata.create_all(connection)
table_names = [
"ai_1",
"ai_2",
"ai_3",
"ai_4",
"ai_5",
"ai_6",
"ai_7",
"ai_8",
]
mr = MetaData()
for name in table_names:
tbl = Table(name, mr, autoload_with=connection)
tbl = metadata.tables[name]
# test that the flag itself reflects appropriately
for col in tbl.c:
if "int_y" in col.name:
is_(col.autoincrement, True)
is_(tbl._autoincrement_column, col)
else:
eq_(col.autoincrement, "auto")
is_not(tbl._autoincrement_column, col)
eng = [
engines.testing_engine(options={"implicit_returning": False}),
engines.testing_engine(options={"implicit_returning": True}),
]
for counter, engine in enumerate(eng):
connection.execute(tbl.insert())
if "int_y" in tbl.c:
eq_(
connection.execute(select(tbl.c.int_y)).scalar(),
counter + 1,
)
assert (list(connection.execute(
tbl.select()).first()).count(counter + 1) == 1)
else:
assert 1 not in list(
connection.execute(tbl.select()).first())
connection.execute(tbl.delete())
from sqlalchemy.dialects.postgresql import ARRAY, DOUBLE_PRECISION
from credovi.schema import metadata, schema
from credovi.util.sqlalchemy import Vector3D
# RAW CHAINS
raw_chains = Table('raw_chains', metadata,
Column('pdb', String(4), nullable=False),
Column('assembly_serial', Integer, nullable=False, autoincrement=False),
Column('entity_serial', Integer, nullable=False, autoincrement=False),
Column('pdb_chain_id', String(1), nullable=False),
Column('pdb_chain_asu_id', String(1), nullable=False),
Column('chain_type', String(50)),
Column('rotation', ARRAY(DOUBLE_PRECISION)),
Column('translation', ARRAY(DOUBLE_PRECISION)),
Column('is_at_identity', Boolean(create_constraint=False), DefaultClause('false'), nullable=False),
schema=schema, prefixes=['unlogged'])
PrimaryKeyConstraint(raw_chains.c.pdb, raw_chains.c.assembly_serial,
raw_chains.c.entity_serial, deferrable=True, initially='deferred')
# RAW LIGANDS
raw_ligands = Table('raw_ligands', metadata,
Column('pdb', String(4), nullable=False, primary_key=True),
Column('assembly_serial', Integer, nullable=False, primary_key=True, autoincrement=False),
Column('entity_serial', Integer, nullable=False, autoincrement=False, primary_key=True),
Column('pdb_chain_id', String(1), nullable=False),
Column('res_num', Integer),
Column('ligand_name', String(64), nullable=False),
Column('num_hvy_atoms', Integer),
Column('ism', Text),
class TypeCompileTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = mysql.dialect()
@testing.combinations(
# column type, args, kwargs, expected ddl
# e.g. Column(Integer(10, unsigned=True)) ==
# 'INTEGER(10) UNSIGNED'
(mysql.MSNumeric, [], {}, "NUMERIC"),
(mysql.MSNumeric, [None], {}, "NUMERIC"),
(mysql.MSNumeric, [12], {}, "NUMERIC(12)"),
(
mysql.MSNumeric,
[12, 4],
{
"unsigned": True
},
"NUMERIC(12, 4) UNSIGNED",
),
(
mysql.MSNumeric,
[12, 4],
{
"zerofill": True
},
"NUMERIC(12, 4) ZEROFILL",
),
(
mysql.MSNumeric,
[12, 4],
{
"zerofill": True,
"unsigned": True
},
"NUMERIC(12, 4) UNSIGNED ZEROFILL",
),
(mysql.MSDecimal, [], {}, "DECIMAL"),
(mysql.MSDecimal, [None], {}, "DECIMAL"),
(mysql.MSDecimal, [12], {}, "DECIMAL(12)"),
(mysql.MSDecimal, [12, None], {}, "DECIMAL(12)"),
(
mysql.MSDecimal,
[12, 4],
{
"unsigned": True
},
"DECIMAL(12, 4) UNSIGNED",
),
(
mysql.MSDecimal,
[12, 4],
{
"zerofill": True
},
"DECIMAL(12, 4) ZEROFILL",
),
(
mysql.MSDecimal,
[12, 4],
{
"zerofill": True,
"unsigned": True
},
"DECIMAL(12, 4) UNSIGNED ZEROFILL",
),
(mysql.MSDouble, [None, None], {}, "DOUBLE"),
(
mysql.MSDouble,
[12, 4],
{
"unsigned": True
},
"DOUBLE(12, 4) UNSIGNED",
),
(
mysql.MSDouble,
[12, 4],
{
"zerofill": True
},
"DOUBLE(12, 4) ZEROFILL",
),
(
mysql.MSDouble,
[12, 4],
{
"zerofill": True,
"unsigned": True
},
"DOUBLE(12, 4) UNSIGNED ZEROFILL",
),
(mysql.MSReal, [None, None], {}, "REAL"),
(mysql.MSReal, [12, 4], {
"unsigned": True
}, "REAL(12, 4) UNSIGNED"),
(mysql.MSReal, [12, 4], {
"zerofill": True
}, "REAL(12, 4) ZEROFILL"),
(
mysql.MSReal,
[12, 4],
{
"zerofill": True,
"unsigned": True
},
"REAL(12, 4) UNSIGNED ZEROFILL",
),
(mysql.MSFloat, [], {}, "FLOAT"),
(mysql.MSFloat, [None], {}, "FLOAT"),
(mysql.MSFloat, [12], {}, "FLOAT(12)"),
(mysql.MSFloat, [12, 4], {}, "FLOAT(12, 4)"),
(mysql.MSFloat, [12, 4], {
"unsigned": True
}, "FLOAT(12, 4) UNSIGNED"),
(mysql.MSFloat, [12, 4], {
"zerofill": True
}, "FLOAT(12, 4) ZEROFILL"),
(
mysql.MSFloat,
[12, 4],
{
"zerofill": True,
"unsigned": True
},
"FLOAT(12, 4) UNSIGNED ZEROFILL",
),
(mysql.MSInteger, [], {}, "INTEGER"),
(mysql.MSInteger, [4], {}, "INTEGER(4)"),
(mysql.MSInteger, [4], {
"unsigned": True
}, "INTEGER(4) UNSIGNED"),
(mysql.MSInteger, [4], {
"zerofill": True
}, "INTEGER(4) ZEROFILL"),
(
mysql.MSInteger,
[4],
{
"zerofill": True,
"unsigned": True
},
"INTEGER(4) UNSIGNED ZEROFILL",
),
(mysql.MSBigInteger, [], {}, "BIGINT"),
(mysql.MSBigInteger, [4], {}, "BIGINT(4)"),
(mysql.MSBigInteger, [4], {
"unsigned": True
}, "BIGINT(4) UNSIGNED"),
(mysql.MSBigInteger, [4], {
"zerofill": True
}, "BIGINT(4) ZEROFILL"),
(
mysql.MSBigInteger,
[4],
{
"zerofill": True,
"unsigned": True
},
"BIGINT(4) UNSIGNED ZEROFILL",
),
(mysql.MSMediumInteger, [], {}, "MEDIUMINT"),
(mysql.MSMediumInteger, [4], {}, "MEDIUMINT(4)"),
(
mysql.MSMediumInteger,
[4],
{
"unsigned": True
},
"MEDIUMINT(4) UNSIGNED",
),
(
mysql.MSMediumInteger,
[4],
{
"zerofill": True
},
"MEDIUMINT(4) ZEROFILL",
),
(
mysql.MSMediumInteger,
[4],
{
"zerofill": True,
"unsigned": True
},
"MEDIUMINT(4) UNSIGNED ZEROFILL",
),
(mysql.MSTinyInteger, [], {}, "TINYINT"),
(mysql.MSTinyInteger, [1], {}, "TINYINT(1)"),
(mysql.MSTinyInteger, [1], {
"unsigned": True
}, "TINYINT(1) UNSIGNED"),
(mysql.MSTinyInteger, [1], {
"zerofill": True
}, "TINYINT(1) ZEROFILL"),
(
mysql.MSTinyInteger,
[1],
{
"zerofill": True,
"unsigned": True
},
"TINYINT(1) UNSIGNED ZEROFILL",
),
(mysql.MSSmallInteger, [], {}, "SMALLINT"),
(mysql.MSSmallInteger, [4], {}, "SMALLINT(4)"),
(
mysql.MSSmallInteger,
[4],
{
"unsigned": True
},
"SMALLINT(4) UNSIGNED",
),
(
mysql.MSSmallInteger,
[4],
{
"zerofill": True
},
"SMALLINT(4) ZEROFILL",
),
(
mysql.MSSmallInteger,
[4],
{
"zerofill": True,
"unsigned": True
},
"SMALLINT(4) UNSIGNED ZEROFILL",
),
)
def test_numeric(self, type_, args, kw, res):
"Exercise type specification and options for numeric types."
type_inst = type_(*args, **kw)
self.assert_compile(type_inst, res)
# test that repr() copies out all arguments
self.assert_compile(eval("mysql.%r" % type_inst), res)
@testing.combinations(
(mysql.MSChar, [1], {}, "CHAR(1)"),
(mysql.NCHAR, [1], {}, "NATIONAL CHAR(1)"),
(mysql.MSChar, [1], {
"binary": True
}, "CHAR(1) BINARY"),
(mysql.MSChar, [1], {
"ascii": True
}, "CHAR(1) ASCII"),
(mysql.MSChar, [1], {
"unicode": True
}, "CHAR(1) UNICODE"),
(
mysql.MSChar,
[1],
{
"ascii": True,
"binary": True
},
"CHAR(1) ASCII BINARY",
),
(
mysql.MSChar,
[1],
{
"unicode": True,
"binary": True
},
"CHAR(1) UNICODE BINARY",
),
(mysql.MSChar, [1], {
"charset": "utf8"
}, "CHAR(1) CHARACTER SET utf8"),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"binary": True
},
"CHAR(1) CHARACTER SET utf8 BINARY",
),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"unicode": True
},
"CHAR(1) CHARACTER SET utf8",
),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"ascii": True
},
"CHAR(1) CHARACTER SET utf8",
),
(
mysql.MSChar,
[1],
{
"collation": "utf8_bin"
},
"CHAR(1) COLLATE utf8_bin",
),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"collation": "utf8_bin"
},
"CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"binary": True
},
"CHAR(1) CHARACTER SET utf8 BINARY",
),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"collation": "utf8_bin",
"binary": True
},
"CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
),
(mysql.MSChar, [1], {
"national": True
}, "NATIONAL CHAR(1)"),
(
mysql.MSChar,
[1],
{
"national": True,
"charset": "utf8"
},
"NATIONAL CHAR(1)",
),
(
mysql.MSChar,
[1],
{
"national": True,
"charset": "utf8",
"binary": True
},
"NATIONAL CHAR(1) BINARY",
),
(
mysql.MSChar,
[1],
{
"national": True,
"binary": True,
"unicode": True
},
"NATIONAL CHAR(1) BINARY",
),
(
mysql.MSChar,
[1],
{
"national": True,
"collation": "utf8_bin"
},
"NATIONAL CHAR(1) COLLATE utf8_bin",
),
(
mysql.MSString,
[1],
{
"charset": "utf8",
"collation": "utf8_bin"
},
"VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
),
(
mysql.MSString,
[1],
{
"national": True,
"collation": "utf8_bin"
},
"NATIONAL VARCHAR(1) COLLATE utf8_bin",
),
(
mysql.MSTinyText,
[],
{
"charset": "utf8",
"collation": "utf8_bin"
},
"TINYTEXT CHARACTER SET utf8 COLLATE utf8_bin",
),
(
mysql.MSMediumText,
[],
{
"charset": "utf8",
"binary": True
},
"MEDIUMTEXT CHARACTER SET utf8 BINARY",
),
(mysql.MSLongText, [], {
"ascii": True
}, "LONGTEXT ASCII"),
(
mysql.ENUM,
["foo", "bar"],
{
"unicode": True
},
"""ENUM('foo','bar') UNICODE""",
),
(String, [20], {
"collation": "utf8"
}, "VARCHAR(20) COLLATE utf8"),
)
@testing.exclude("mysql", "<", (4, 1, 1), "no charset support")
def test_charset(self, type_, args, kw, res):
"""Exercise CHARACTER SET and COLLATE-ish options on string types."""
type_inst = type_(*args, **kw)
self.assert_compile(type_inst, res)
@testing.combinations(
(mysql.MSBit(), "BIT"),
(mysql.MSBit(1), "BIT(1)"),
(mysql.MSBit(63), "BIT(63)"),
)
def test_bit_50(self, type_, expected):
"""Exercise BIT types on 5.0+ (not valid for all engine types)"""
self.assert_compile(type_, expected)
@testing.combinations(
(BOOLEAN(), "BOOL"),
(Boolean(), "BOOL"),
(mysql.TINYINT(1), "TINYINT(1)"),
(mysql.TINYINT(1, unsigned=True), "TINYINT(1) UNSIGNED"),
)
def test_boolean_compile(self, type_, expected):
self.assert_compile(type_, expected)
def test_timestamp_fsp(self):
self.assert_compile(mysql.TIMESTAMP(fsp=5), "TIMESTAMP(5)")
@testing.combinations(
([TIMESTAMP], {}, "TIMESTAMP NULL"),
([mysql.MSTimeStamp], {}, "TIMESTAMP NULL"),
(
[
mysql.MSTimeStamp(),
DefaultClause(sql.text("CURRENT_TIMESTAMP")),
],
{},
"TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP",
),
(
[mysql.MSTimeStamp,
DefaultClause(sql.text("CURRENT_TIMESTAMP"))],
{
"nullable": False
},
"TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp,
DefaultClause(sql.text("'1999-09-09 09:09:09'")),
],
{
"nullable": False
},
"TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09'",
),
(
[
mysql.MSTimeStamp(),
DefaultClause(sql.text("'1999-09-09 09:09:09'")),
],
{},
"TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09'",
),
(
[
mysql.MSTimeStamp(),
DefaultClause(
sql.text("'1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP")),
],
{},
"TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp,
DefaultClause(
sql.text("'1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP")),
],
{
"nullable": False
},
"TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp(),
DefaultClause(
sql.text("CURRENT_TIMESTAMP "
"ON UPDATE CURRENT_TIMESTAMP")),
],
{},
"TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP "
"ON UPDATE CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp,
DefaultClause(
sql.text("CURRENT_TIMESTAMP "
"ON UPDATE CURRENT_TIMESTAMP")),
],
{
"nullable": False
},
"TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP "
"ON UPDATE CURRENT_TIMESTAMP",
),
)
def test_timestamp_defaults(self, spec, kw, expected):
"""Exercise funky TIMESTAMP default syntax when used in columns."""
c = Column("t", *spec, **kw)
Table("t", MetaData(), c)
self.assert_compile(schema.CreateColumn(c), "t %s" % expected)
def test_datetime_generic(self):
self.assert_compile(mysql.DATETIME(), "DATETIME")
def test_datetime_fsp(self):
self.assert_compile(mysql.DATETIME(fsp=4), "DATETIME(4)")
def test_time_generic(self):
"""Exercise TIME."""
self.assert_compile(mysql.TIME(), "TIME")
def test_time_fsp(self):
self.assert_compile(mysql.TIME(fsp=5), "TIME(5)")
def test_time_result_processor(self):
eq_(
mysql.TIME().result_processor(None, None)(datetime.timedelta(
seconds=35, minutes=517, microseconds=450)),
datetime.time(8, 37, 35, 450),
)
from sqlalchemy.schema import PrimaryKeyConstraint
from credovi.schema import metadata, schema
from credovi.util.sqlalchemy import PTree, comment_on_table_elements
biomolecules = Table(
'biomolecules',
metadata,
Column('biomolecule_id', Integer, nullable=False),
Column('structure_id', Integer, nullable=False),
Column('path', PTree, nullable=False),
Column('assembly_serial', Integer, nullable=False),
Column('assembly_type', String(32)), # e.g. monomeric
Column('conformational_state_bm',
Integer,
DefaultClause('0'),
nullable=False),
Column('structural_interaction_bm',
Integer,
DefaultClause('0'),
nullable=False),
Column('num_chains', Integer, DefaultClause('0'), nullable=False),
Column('num_ligands', Integer, DefaultClause('0'), nullable=False),
Column('num_atoms', Integer, DefaultClause('0'), nullable=False),
schema=schema)
PrimaryKeyConstraint(biomolecules.c.biomolecule_id,
deferrable=True,
initially='deferred')
Index('idx_biomolecules_structures_id',
biomolecules.c.structure_id,
def test_autoincrement(self):
Table(
"ai_1",
metadata,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_2",
metadata,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_3",
metadata,
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
)
Table(
"ai_4",
metadata,
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
Column("int_n2", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_5",
metadata,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_6",
metadata,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
)
Table(
"ai_7",
metadata,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, autoincrement=True, primary_key=True),
)
Table(
"ai_8",
metadata,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
)
metadata.create_all()
table_names = [
"ai_1",
"ai_2",
"ai_3",
"ai_4",
"ai_5",
"ai_6",
"ai_7",
"ai_8",
]
mr = MetaData(testing.db)
for name in table_names:
tbl = Table(name, mr, autoload=True)
tbl = metadata.tables[name]
# test that the flag itself reflects appropriately
for col in tbl.c:
if "int_y" in col.name:
is_(col.autoincrement, True)
is_(tbl._autoincrement_column, col)
else:
eq_(col.autoincrement, "auto")
is_not_(tbl._autoincrement_column, col)
# mxodbc can't handle scope_identity() with DEFAULT VALUES
if testing.db.driver == "mxodbc":
eng = [
engines.testing_engine(
options={"implicit_returning": True})
]
else:
eng = [
engines.testing_engine(
options={"implicit_returning": False}),
engines.testing_engine(
options={"implicit_returning": True}),
]
for counter, engine in enumerate(eng):
with engine.begin() as conn:
conn.execute(tbl.insert())
if "int_y" in tbl.c:
eq_(
conn.execute(select([tbl.c.int_y])).scalar(),
counter + 1,
)
assert (list(conn.execute(
tbl.select()).first()).count(counter + 1) == 1)
else:
assert 1 not in list(
conn.execute(tbl.select()).first())
conn.execute(tbl.delete())
def test_autoincrement(self):
meta = self.metadata
Table(
"ai_1",
meta,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
mysql_engine="MyISAM",
)
Table(
"ai_2",
meta,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
mysql_engine="MyISAM",
)
Table(
"ai_3",
meta,
Column(
"int_n",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
Column("int_y", Integer, primary_key=True, autoincrement=True),
mysql_engine="MyISAM",
)
Table(
"ai_4",
meta,
Column(
"int_n",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
Column(
"int_n2",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
mysql_engine="MyISAM",
)
Table(
"ai_5",
meta,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column(
"int_n",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
mysql_engine="MyISAM",
)
Table(
"ai_6",
meta,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
mysql_engine="MyISAM",
)
Table(
"ai_7",
meta,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
mysql_engine="MyISAM",
)
Table(
"ai_8",
meta,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
mysql_engine="MyISAM",
)
meta.create_all(testing.db)
table_names = [
"ai_1",
"ai_2",
"ai_3",
"ai_4",
"ai_5",
"ai_6",
"ai_7",
"ai_8",
]
mr = MetaData()
mr.reflect(testing.db, only=table_names)
with testing.db.begin() as conn:
for tbl in [mr.tables[name] for name in table_names]:
for c in tbl.c:
if c.name.startswith("int_y"):
assert c.autoincrement
elif c.name.startswith("int_n"):
assert not c.autoincrement
conn.execute(tbl.insert())
if "int_y" in tbl.c:
assert conn.scalar(select(tbl.c.int_y)) == 1
assert (
list(conn.execute(tbl.select()).first()).count(1) == 1
)
else:
assert 1 not in list(conn.execute(tbl.select()).first())
def upgrade():
op.add_column('users', Column('first_name', sa.String(255), DefaultClause(""), nullable=False))
op.add_column('users', Column('last_name', sa.String(255), DefaultClause(""), nullable=False))
op.add_column('users', Column('email', sa.String(255), DefaultClause(""), nullable=False))
def test_empty_insert_pk3(self):
assert_raises(exc.DBAPIError, self._test_empty_insert, Table('c'
, MetaData(testing.db), Column('x', Integer,
primary_key=True), Column('y', Integer,
DefaultClause('123'), primary_key=True)))
def upgrade():
op.drop_column('user_source', 'username')
op.add_column(
'user_source',
Column('user_id', sa.Integer, DefaultClause(-1), nullable=False))
def downgrade():
op.drop_column('user_source', 'user_id')
op.add_column(
'user_source',
Column('username', sa.String(255), DefaultClause(""), nullable=False))
def upgrade():
op.add_column(
'new', Column('url',
sa.String(2083),
DefaultClause(""),
nullable=False))
# residues master table (won't be empty!)
residues = Table(
'residues',
metadata,
Column('residue_id', Integer, nullable=False),
Column('biomolecule_id', Integer, nullable=False),
Column('chain_id', Integer, nullable=False),
Column('path', PTree, nullable=False),
Column('res_name', String(3), nullable=False),
Column('res_num', Integer, nullable=False),
Column('ins_code', String(1), nullable=False), # ' '
Column('entity_type_bm', Integer, nullable=False),
Column('is_disordered',
Boolean(create_constraint=False),
DefaultClause('false'),
nullable=False),
Column('is_incomplete',
Boolean(create_constraint=False),
DefaultClause('false'),
nullable=False),
schema=schema)
PrimaryKeyConstraint(residues.c.residue_id,
deferrable=True,
initially='deferred')
Index('idx_residues_biomolecule_id', residues.c.biomolecule_id)
Index('idx_residues_chain_id', residues.c.chain_id, residues.c.res_num,
residues.c.ins_code)
Index('idx_residues_path', residues.c.path, postgresql_using='gist')
from credovi.schema import metadata, schema
from credovi.util.sqlalchemy import PTree, comment_on_table_elements
prot_fragments = Table('prot_fragments', metadata,
Column('prot_fragment_id', Integer, nullable=False),
Column('biomolecule_id', Integer, nullable=False),
Column('chain_id', Integer, nullable=False),
Column('path', PTree),
Column('sstruct_serial', Integer),
Column('sstruct', String(1)),
Column('fragment_size', Integer, nullable=False),
Column('fragment_seq', Text),
Column('prot_fragment_nterm_id', Integer),
Column('prot_fragment_cterm_id', Integer),
Column('completeness', Float(3,2), DefaultClause('0'), nullable=False),
schema=schema)
PrimaryKeyConstraint(prot_fragments.c.prot_fragment_id, deferrable=True, initially='deferred')
Index('idx_prot_fragments_biomolecule_id', prot_fragments.c.biomolecule_id)
Index('idx_prot_fragments_chain', prot_fragments.c.chain_id, prot_fragments.c.sstruct_serial, unique=True)
Index('idx_prot_fragments_path', prot_fragments.c.path, postgresql_using='gist')
Index('idx_prot_fragments_seq', prot_fragments.c.fragment_seq)
prot_fragment_comments = {
"table": "Contains information about secondary structure fragments in proteins.",
"columns":
{
"prot_fragment_id": "Primary key of the protein fragment.",
"biomolecule_id": "Primary key of the biomolecule the secondary structure fragment belongs to.",
"chain_id": "Primary key of the chain the secondary structure belongs to.",
def test_default_reflection(self):
"""Test reflection of column defaults."""
from sqlalchemy.dialects.mysql import VARCHAR
def_table = Table(
"mysql_def",
MetaData(testing.db),
Column(
"c1",
VARCHAR(10, collation="utf8_unicode_ci"),
DefaultClause(""),
nullable=False,
),
Column("c2", String(10), DefaultClause("0")),
Column("c3", String(10), DefaultClause("abc")),
Column("c4", TIMESTAMP, DefaultClause("2009-04-05 12:00:00")),
Column("c5", TIMESTAMP),
Column(
"c6",
TIMESTAMP,
DefaultClause(
sql.text("CURRENT_TIMESTAMP "
"ON UPDATE CURRENT_TIMESTAMP")),
),
)
def_table.create()
try:
reflected = Table("mysql_def", MetaData(testing.db), autoload=True)
finally:
def_table.drop()
assert def_table.c.c1.server_default.arg == ""
assert def_table.c.c2.server_default.arg == "0"
assert def_table.c.c3.server_default.arg == "abc"
assert def_table.c.c4.server_default.arg == "2009-04-05 12:00:00"
assert str(reflected.c.c1.server_default.arg) == "''"
assert str(reflected.c.c2.server_default.arg) == "'0'"
assert str(reflected.c.c3.server_default.arg) == "'abc'"
assert (str(
reflected.c.c4.server_default.arg) == "'2009-04-05 12:00:00'")
assert reflected.c.c5.default is None
assert reflected.c.c5.server_default is None
assert reflected.c.c6.default is None
assert re.match(
r"CURRENT_TIMESTAMP(\(\))? ON UPDATE CURRENT_TIMESTAMP(\(\))?",
str(reflected.c.c6.server_default.arg).upper(),
)
reflected.create()
try:
reflected2 = Table("mysql_def",
MetaData(testing.db),
autoload=True)
finally:
reflected.drop()
assert str(reflected2.c.c1.server_default.arg) == "''"
assert str(reflected2.c.c2.server_default.arg) == "'0'"
assert str(reflected2.c.c3.server_default.arg) == "'abc'"
assert (str(
reflected2.c.c4.server_default.arg) == "'2009-04-05 12:00:00'")
assert reflected.c.c5.default is None
assert reflected.c.c5.server_default is None
assert reflected.c.c6.default is None
assert re.match(
r"CURRENT_TIMESTAMP(\(\))? ON UPDATE CURRENT_TIMESTAMP(\(\))?",
str(reflected.c.c6.server_default.arg).upper(),
)
class Trip(Base):
__tablename__ = 'trips'
id = Column('trip_id', Integer, primary_key=True, index=True)
Location = Table(
'gotorussia_travels_locations',
Base.metadata,
Column(
'id',
Integer,
primary_key=True,
nullable=False,
server_default=DefaultClause(
"nextval('gotorussia_travels_locations_id_seq'::regclass)",
for_update=False),
),
Column('type_id', JSONB),
Column('object_title', String(255), nullable=False),
Column('object_place', String(255)),
Column('object_coord', String(255)),
Column('object_description', Text, nullable=False),
Column('adress', Text),
Column('region_id', Integer),
Column('local_id', Integer),
Column(
'lat',
DOUBLE_PRECISION(precision=53),
server_default=DefaultClause('0', for_update=False),
),
def test_autoincrement(self):
meta = MetaData(testing.db)
try:
Table(
"ai_1",
meta,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
mysql_engine="MyISAM",
)
Table(
"ai_2",
meta,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
mysql_engine="MyISAM",
)
Table(
"ai_3",
meta,
Column(
"int_n",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
Column("int_y", Integer, primary_key=True, autoincrement=True),
mysql_engine="MyISAM",
)
Table(
"ai_4",
meta,
Column(
"int_n",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
Column(
"int_n2",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
mysql_engine="MyISAM",
)
Table(
"ai_5",
meta,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column(
"int_n",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
mysql_engine="MyISAM",
)
Table(
"ai_6",
meta,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
mysql_engine="MyISAM",
)
Table(
"ai_7",
meta,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
mysql_engine="MyISAM",
)
Table(
"ai_8",
meta,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
mysql_engine="MyISAM",
)
meta.create_all()
table_names = [
"ai_1",
"ai_2",
"ai_3",
"ai_4",
"ai_5",
"ai_6",
"ai_7",
"ai_8",
]
mr = MetaData(testing.db)
mr.reflect(only=table_names)
for tbl in [mr.tables[name] for name in table_names]:
for c in tbl.c:
if c.name.startswith("int_y"):
assert c.autoincrement
elif c.name.startswith("int_n"):
assert not c.autoincrement
tbl.insert().execute()
if "int_y" in tbl.c:
assert select([tbl.c.int_y]).scalar() == 1
assert list(tbl.select().execute().first()).count(1) == 1
else:
assert 1 not in list(tbl.select().execute().first())
finally:
meta.drop_all()
class Certificate(db.Model):
__tablename__ = "certificates"
__table_args__ = (
Index(
"ix_certificates_cn",
"cn",
postgresql_ops={"cn": "gin_trgm_ops"},
postgresql_using="gin",
),
Index(
"ix_certificates_name",
"name",
postgresql_ops={"name": "gin_trgm_ops"},
postgresql_using="gin",
),
)
id = Column(Integer, primary_key=True)
ix = Index("ix_certificates_id_desc",
id.desc(),
postgresql_using="btree",
unique=True)
external_id = Column(String(128))
owner = Column(String(128), nullable=False)
name = Column(String(256), unique=True)
description = Column(String(1024))
notify = Column(Boolean, default=True)
body = Column(Text(), nullable=False)
chain = Column(Text())
csr = Column(Text())
private_key = Column(Vault)
issuer = Column(String(128))
serial = Column(String(128))
cn = Column(String(128))
deleted = Column(Boolean, index=True, default=False)
dns_provider_id = Column(Integer(),
ForeignKey("dns_providers.id",
ondelete="CASCADE"),
nullable=True)
not_before = Column(ArrowType)
not_after = Column(ArrowType)
not_after_ix = Index("ix_certificates_not_after", not_after.desc())
date_created = Column(ArrowType, DefaultClause(func.now()), nullable=False)
signing_algorithm = Column(String(128))
status = Column(String(128))
bits = Column(Integer())
san = Column(String(1024)) # TODO this should be migrated to boolean
rotation = Column(Boolean, default=False)
user_id = Column(Integer, ForeignKey("users.id"))
authority_id = Column(Integer,
ForeignKey("authorities.id", ondelete="CASCADE"))
root_authority_id = Column(
Integer, ForeignKey("authorities.id", ondelete="CASCADE"))
rotation_policy_id = Column(Integer, ForeignKey("rotation_policies.id"))
key_type = Column(String(128))
notifications = relationship(
"Notification",
secondary=certificate_notification_associations,
backref="certificate",
)
destinations = relationship(
"Destination",
secondary=certificate_destination_associations,
backref="certificate",
)
sources = relationship("Source",
secondary=certificate_source_associations,
backref="certificate")
domains = relationship("Domain",
secondary=certificate_associations,
backref="certificate")
roles = relationship("Role",
secondary=roles_certificates,
backref="certificate")
replaces = relationship(
"Certificate",
secondary=certificate_replacement_associations,
primaryjoin=id ==
certificate_replacement_associations.c.certificate_id, # noqa
secondaryjoin=id ==
certificate_replacement_associations.c.replaced_certificate_id, # noqa
backref="replaced",
)
replaced_by_pending = relationship(
"PendingCertificate",
secondary=pending_cert_replacement_associations,
backref="pending_replace",
)
logs = relationship("Log", backref="certificate")
endpoints = relationship("Endpoint", backref="certificate")
rotation_policy = relationship("RotationPolicy")
sensitive_fields = ("private_key", )
def __init__(self, **kwargs):
self.body = kwargs["body"].strip()
cert = self.parsed_cert
self.issuer = defaults.issuer(cert)
self.cn = defaults.common_name(cert)
self.san = defaults.san(cert)
self.not_before = defaults.not_before(cert)
self.not_after = defaults.not_after(cert)
self.serial = defaults.serial(cert)
# when destinations are appended they require a valid name.
if kwargs.get("name"):
self.name = get_or_increase_name(
defaults.text_to_slug(kwargs["name"]), self.serial)
else:
self.name = get_or_increase_name(
defaults.certificate_name(self.cn, self.issuer,
self.not_before, self.not_after,
self.san),
self.serial,
)
self.owner = kwargs["owner"]
if kwargs.get("private_key"):
self.private_key = kwargs["private_key"].strip()
if kwargs.get("chain"):
self.chain = kwargs["chain"].strip()
if kwargs.get("csr"):
self.csr = kwargs["csr"].strip()
self.notify = kwargs.get("notify", True)
self.destinations = kwargs.get("destinations", [])
self.notifications = kwargs.get("notifications", [])
self.description = kwargs.get("description")
self.roles = list(set(kwargs.get("roles", [])))
self.replaces = kwargs.get("replaces", [])
self.rotation = kwargs.get("rotation")
self.rotation_policy = kwargs.get("rotation_policy")
self.key_type = kwargs.get("key_type")
self.signing_algorithm = defaults.signing_algorithm(cert)
self.bits = defaults.bitstrength(cert)
self.external_id = kwargs.get("external_id")
self.authority_id = kwargs.get("authority_id")
self.dns_provider_id = kwargs.get("dns_provider_id")
for domain in defaults.domains(cert):
self.domains.append(Domain(name=domain))
# Check integrity before saving anything into the database.
# For user-facing API calls, validation should also be done in schema validators.
self.check_integrity()
def check_integrity(self):
"""
Integrity checks: Does the cert have a valid chain and matching private key?
"""
if self.private_key:
validators.verify_private_key_match(
utils.parse_private_key(self.private_key),
self.parsed_cert,
error_class=AssertionError,
)
if self.chain:
chain = [self.parsed_cert] + utils.parse_cert_chain(self.chain)
validators.verify_cert_chain(chain, error_class=AssertionError)
@cached_property
def parsed_cert(self):
assert self.body, "Certificate body not set"
return utils.parse_certificate(self.body)
@property
def active(self):
return self.notify
@property
def organization(self):
return defaults.organization(self.parsed_cert)
@property
def organizational_unit(self):
return defaults.organizational_unit(self.parsed_cert)
@property
def country(self):
return defaults.country(self.parsed_cert)
@property
def state(self):
return defaults.state(self.parsed_cert)
@property
def location(self):
return defaults.location(self.parsed_cert)
@property
def distinguished_name(self):
return self.parsed_cert.subject.rfc4514_string()
"""
# Commenting this property as key_type is now added as a column. This code can be removed in future.
@property
def key_type(self):
if isinstance(self.parsed_cert.public_key(), rsa.RSAPublicKey):
return "RSA{key_size}".format(
key_size=self.parsed_cert.public_key().key_size
)
elif isinstance(self.parsed_cert.public_key(), ec.EllipticCurvePublicKey):
return get_key_type_from_ec_curve(self.parsed_cert.public_key().curve.name)
"""
@property
def validity_remaining(self):
return abs(self.not_after - arrow.utcnow())
@property
def validity_range(self):
return self.not_after - self.not_before
@property
def subject(self):
return self.parsed_cert.subject
@property
def public_key(self):
return self.parsed_cert.public_key()
@hybrid_property
def expired(self):
# can't compare offset-naive and offset-aware datetimes
if arrow.Arrow.fromdatetime(self.not_after) <= arrow.utcnow():
return True
@expired.expression
def expired(cls):
return case([(cls.not_after <= arrow.utcnow(), True)], else_=False)
@hybrid_property
def revoked(self):
if "revoked" == self.status:
return True
@revoked.expression
def revoked(cls):
return case([(cls.status == "revoked", True)], else_=False)
@hybrid_property
def has_private_key(self):
return self.private_key is not None
@has_private_key.expression
def has_private_key(cls):
return case([(cls.private_key.is_(None), True)], else_=False)
@hybrid_property
def in_rotation_window(self):
"""
Determines if a certificate is available for rotation based
on the rotation policy associated.
:return:
"""
now = arrow.utcnow()
end = now + timedelta(days=self.rotation_policy.days)
if self.not_after <= end:
return True
@in_rotation_window.expression
def in_rotation_window(cls):
"""
Determines if a certificate is available for rotation based
on the rotation policy associated.
:return:
"""
return case(
[(extract("day", cls.not_after - func.now()) <=
RotationPolicy.days, True)],
else_=False,
)
@property
def extensions(self):
# setup default values
return_extensions = {"sub_alt_names": {"names": []}}
try:
for extension in self.parsed_cert.extensions:
value = extension.value
if isinstance(value, x509.BasicConstraints):
return_extensions["basic_constraints"] = value
elif isinstance(value, x509.SubjectAlternativeName):
return_extensions["sub_alt_names"]["names"] = value
elif isinstance(value, x509.ExtendedKeyUsage):
return_extensions["extended_key_usage"] = value
elif isinstance(value, x509.KeyUsage):
return_extensions["key_usage"] = value
elif isinstance(value, x509.SubjectKeyIdentifier):
return_extensions["subject_key_identifier"] = {
"include_ski": True
}
elif isinstance(value, x509.AuthorityInformationAccess):
return_extensions["certificate_info_access"] = {
"include_aia": True
}
elif isinstance(value, x509.AuthorityKeyIdentifier):
aki = {
"use_key_identifier": False,
"use_authority_cert": False
}
if value.key_identifier:
aki["use_key_identifier"] = True
if value.authority_cert_issuer:
aki["use_authority_cert"] = True
return_extensions["authority_key_identifier"] = aki
elif isinstance(value, x509.CRLDistributionPoints):
return_extensions["crl_distribution_points"] = {
"include_crl_dp": value
}
# TODO: Not supporting custom OIDs yet. https://github.com/Netflix/lemur/issues/665
else:
current_app.logger.warning(
"Custom OIDs not yet supported for clone operation.")
except InvalidCodepoint as e:
sentry.captureException()
current_app.logger.warning(
"Unable to parse extensions due to underscore in dns name")
except ValueError as e:
sentry.captureException()
current_app.logger.warning("Unable to parse")
current_app.logger.exception(e)
return return_extensions
def __repr__(self):
return "Certificate(name={name})".format(name=self.name)
def test_default_reflection(self):
"""Test reflection of column defaults."""
# TODO: this test is a mess. should be broken into individual
# combinations
from sqlalchemy.dialects.mysql import VARCHAR
def_table = Table(
"mysql_def",
MetaData(),
Column(
"c1",
VARCHAR(10, collation="utf8_unicode_ci"),
DefaultClause(""),
nullable=False,
),
Column("c2", String(10), DefaultClause("0")),
Column("c3", String(10), DefaultClause("abc")),
Column("c4", TIMESTAMP, DefaultClause("2009-04-05 12:00:00")),
Column("c5", TIMESTAMP),
Column(
"c6",
TIMESTAMP,
DefaultClause(
sql.text(
"CURRENT_TIMESTAMP " "ON UPDATE CURRENT_TIMESTAMP"
)
),
),
Column("c7", mysql.DOUBLE(), DefaultClause("0.0000")),
Column("c8", mysql.DOUBLE(22, 6), DefaultClause("0.0000")),
)
def_table.create(testing.db)
try:
reflected = Table(
"mysql_def", MetaData(), autoload_with=testing.db
)
finally:
def_table.drop(testing.db)
assert def_table.c.c1.server_default.arg == ""
assert def_table.c.c2.server_default.arg == "0"
assert def_table.c.c3.server_default.arg == "abc"
assert def_table.c.c4.server_default.arg == "2009-04-05 12:00:00"
assert str(reflected.c.c1.server_default.arg) == "''"
assert str(reflected.c.c2.server_default.arg) == "'0'"
assert str(reflected.c.c3.server_default.arg) == "'abc'"
assert (
str(reflected.c.c4.server_default.arg) == "'2009-04-05 12:00:00'"
)
assert reflected.c.c5.default is None
assert reflected.c.c5.server_default is None
assert reflected.c.c6.default is None
assert str(reflected.c.c7.server_default.arg) in ("0", "'0'")
# this is because the numeric is 6 decimal places, MySQL
# formats it to that many places.
assert str(reflected.c.c8.server_default.arg) in (
"0.000000",
"'0.000000'",
)
assert re.match(
r"CURRENT_TIMESTAMP(\(\))? ON UPDATE CURRENT_TIMESTAMP(\(\))?",
str(reflected.c.c6.server_default.arg).upper(),
)
reflected.create(testing.db)
try:
reflected2 = Table(
"mysql_def", MetaData(), autoload_with=testing.db
)
finally:
reflected.drop(testing.db)
assert str(reflected2.c.c1.server_default.arg) == "''"
assert str(reflected2.c.c2.server_default.arg) == "'0'"
assert str(reflected2.c.c3.server_default.arg) == "'abc'"
assert (
str(reflected2.c.c4.server_default.arg) == "'2009-04-05 12:00:00'"
)
assert reflected.c.c5.default is None
assert reflected.c.c5.server_default is None
assert reflected.c.c6.default is None
assert str(reflected.c.c7.server_default.arg) in ("0", "'0'")
assert str(reflected.c.c8.server_default.arg) in (
"0.000000",
"'0.000000'",
)
assert re.match(
r"CURRENT_TIMESTAMP(\(\))? ON UPDATE CURRENT_TIMESTAMP(\(\))?",
str(reflected.c.c6.server_default.arg).upper(),
)
}
}
comment_on_table_elements(pi_atoms, pi_atom_comments)
create_partition_insert_trigger(pi_atoms, PI_GROUP_PARTITION_SIZE)
# interactions between pi groups (and rings)
pi_interactions = Table(
'pi_interactions',
metadata,
Column('pi_interaction_id', Integer, primary_key=True),
Column('biomolecule_id', Integer, nullable=False),
Column('pi_bgn_id', Integer, nullable=False),
Column('pi_bgn_is_ring',
Boolean(create_constraint=False),
DefaultClause('false'),
nullable=False),
Column('pi_end_id', Integer, nullable=False),
Column('pi_end_is_ring',
Boolean(create_constraint=False),
DefaultClause('false'),
nullable=False),
# Column('closest_atom_bgn_id', Integer),
# Column('closest_atom_end_id', Integer),
Column('distance', Float(3, 2), nullable=False),
# Column('closest_atom_distance', Float(3,2)),
Column('dihedral', Float(5, 2), nullable=False),
Column('theta', Float(5, 2), nullable=False),
Column('iota', Float(5, 2), nullable=False),
Column(
'interaction_type', String(2)
def test_autoincrement(self):
Table('ai_1', metadata,
Column('int_y', Integer, primary_key=True, autoincrement=True),
Column('int_n', Integer, DefaultClause('0'), primary_key=True))
Table('ai_2', metadata,
Column('int_y', Integer, primary_key=True, autoincrement=True),
Column('int_n', Integer, DefaultClause('0'), primary_key=True))
Table('ai_3', metadata,
Column('int_n', Integer, DefaultClause('0'), primary_key=True),
Column('int_y', Integer, primary_key=True, autoincrement=True))
Table('ai_4', metadata,
Column('int_n', Integer, DefaultClause('0'), primary_key=True),
Column('int_n2', Integer, DefaultClause('0'), primary_key=True))
Table('ai_5', metadata,
Column('int_y', Integer, primary_key=True, autoincrement=True),
Column('int_n', Integer, DefaultClause('0'), primary_key=True))
Table('ai_6', metadata,
Column('o1', String(1), DefaultClause('x'), primary_key=True),
Column('int_y', Integer, primary_key=True, autoincrement=True))
Table('ai_7', metadata,
Column('o1', String(1), DefaultClause('x'), primary_key=True),
Column('o2', String(1), DefaultClause('x'), primary_key=True),
Column('int_y', Integer, autoincrement=True, primary_key=True))
Table('ai_8', metadata,
Column('o1', String(1), DefaultClause('x'), primary_key=True),
Column('o2', String(1), DefaultClause('x'), primary_key=True))
metadata.create_all()
table_names = [
'ai_1', 'ai_2', 'ai_3', 'ai_4', 'ai_5', 'ai_6', 'ai_7', 'ai_8'
]
mr = MetaData(testing.db)
for name in table_names:
tbl = Table(name, mr, autoload=True)
tbl = metadata.tables[name]
# test that the flag itself reflects appropriately
for col in tbl.c:
if 'int_y' in col.name:
is_(col.autoincrement, True)
is_(tbl._autoincrement_column, col)
else:
eq_(col.autoincrement, 'auto')
is_not_(tbl._autoincrement_column, col)
# mxodbc can't handle scope_identity() with DEFAULT VALUES
if testing.db.driver == 'mxodbc':
eng = \
[engines.testing_engine(options={
'implicit_returning': True})]
else:
eng = \
[engines.testing_engine(options={
'implicit_returning': False}),
engines.testing_engine(options={
'implicit_returning': True})]
for counter, engine in enumerate(eng):
engine.execute(tbl.insert())
if 'int_y' in tbl.c:
assert engine.scalar(select([tbl.c.int_y])) \
== counter + 1
assert list(
engine.execute(tbl.select()).first()).\
count(counter + 1) == 1
else:
assert 1 \
not in list(engine.execute(tbl.select()).first())
engine.execute(tbl.delete())
class ReflectionTest(fixtures.TestBase, AssertsCompiledSQL):
__only_on__ = "mysql", "mariadb"
__backend__ = True
@testing.combinations(
(
mysql.VARCHAR(10, collation="utf8_unicode_ci"),
DefaultClause(""),
"''",
),
(String(10), DefaultClause("abc"), "'abc'"),
(String(10), DefaultClause("0"), "'0'"),
(
TIMESTAMP,
DefaultClause("2009-04-05 12:00:00"),
"'2009-04-05 12:00:00'",
),
(TIMESTAMP, None, None),
(
TIMESTAMP,
DefaultClause(
sql.text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")),
re.compile(
r"CURRENT_TIMESTAMP(\(\))? ON UPDATE CURRENT_TIMESTAMP(\(\))?",
re.I,
),
),
(mysql.DOUBLE(), DefaultClause("0.0000"), "0"),
(mysql.DOUBLE(22, 6), DefaultClause("0.0000"), "0.000000"),
(Integer, DefaultClause("1"), "1"),
(Integer, DefaultClause("-1"), "-1"),
(mysql.DOUBLE, DefaultClause("-25.03"), "-25.03"),
(mysql.DOUBLE, DefaultClause("-.001"), "-0.001"),
argnames="datatype, default, expected",
)
def test_default_reflection(self, datatype, default, expected, metadata,
connection):
t1 = Table("t1", metadata, Column("x", datatype, default))
t1.create(connection)
insp = inspect(connection)
datatype_inst = types.to_instance(datatype)
col = insp.get_columns("t1")[0]
if hasattr(expected, "match"):
assert expected.match(col["default"])
elif isinstance(datatype_inst, (Integer, Numeric)):
pattern = re.compile(r"\'?%s\'?" % expected)
assert pattern.match(col["default"])
else:
eq_(col["default"], expected)
def test_reflection_with_table_options(self, metadata, connection):
comment = r"""Comment types type speedily ' " \ '' Fun!"""
if testing.against("mariadb"):
kwargs = dict(
mariadb_engine="MEMORY",
mariadb_default_charset="utf8",
mariadb_auto_increment="5",
mariadb_avg_row_length="3",
mariadb_password="******",
mariadb_connection="fish",
)
else:
kwargs = dict(
mysql_engine="MEMORY",
mysql_default_charset="utf8",
mysql_auto_increment="5",
mysql_avg_row_length="3",
mysql_password="******",
mysql_connection="fish",
)
def_table = Table("mysql_def",
metadata,
Column("c1", Integer()),
comment=comment,
**kwargs)
conn = connection
def_table.create(conn)
reflected = Table("mysql_def", MetaData(), autoload_with=conn)
if testing.against("mariadb"):
assert def_table.kwargs["mariadb_engine"] == "MEMORY"
assert def_table.comment == comment
assert def_table.kwargs["mariadb_default_charset"] == "utf8"
assert def_table.kwargs["mariadb_auto_increment"] == "5"
assert def_table.kwargs["mariadb_avg_row_length"] == "3"
assert def_table.kwargs["mariadb_password"] == "secret"
assert def_table.kwargs["mariadb_connection"] == "fish"
assert reflected.kwargs["mariadb_engine"] == "MEMORY"
assert reflected.comment == comment
assert reflected.kwargs["mariadb_comment"] == comment
assert reflected.kwargs["mariadb_default charset"] == "utf8"
assert reflected.kwargs["mariadb_avg_row_length"] == "3"
assert reflected.kwargs["mariadb_connection"] == "fish"
# This field doesn't seem to be returned by mariadb itself.
# assert reflected.kwargs['mariadb_password'] == 'secret'
# This is explicitly ignored when reflecting schema.
# assert reflected.kwargs['mariadb_auto_increment'] == '5'
else:
assert def_table.kwargs["mysql_engine"] == "MEMORY"
assert def_table.comment == comment
assert def_table.kwargs["mysql_default_charset"] == "utf8"
assert def_table.kwargs["mysql_auto_increment"] == "5"
assert def_table.kwargs["mysql_avg_row_length"] == "3"
assert def_table.kwargs["mysql_password"] == "secret"
assert def_table.kwargs["mysql_connection"] == "fish"
assert reflected.kwargs["mysql_engine"] == "MEMORY"
assert reflected.comment == comment
assert reflected.kwargs["mysql_comment"] == comment
assert reflected.kwargs["mysql_default charset"] == "utf8"
assert reflected.kwargs["mysql_avg_row_length"] == "3"
assert reflected.kwargs["mysql_connection"] == "fish"
# This field doesn't seem to be returned by mysql itself.
# assert reflected.kwargs['mysql_password'] == 'secret'
# This is explicitly ignored when reflecting schema.
# assert reflected.kwargs['mysql_auto_increment'] == '5'
def test_reflection_on_include_columns(self, metadata, connection):
"""Test reflection of include_columns to be sure they respect case."""
meta = metadata
case_table = Table(
"mysql_case",
meta,
Column("c1", String(10)),
Column("C2", String(10)),
Column("C3", String(10)),
)
case_table.create(connection)
reflected = Table(
"mysql_case",
MetaData(),
autoload_with=connection,
include_columns=["c1", "C2"],
)
for t in case_table, reflected:
assert "c1" in t.c.keys()
assert "C2" in t.c.keys()
reflected2 = Table(
"mysql_case",
MetaData(),
autoload_with=connection,
include_columns=["c1", "c2"],
)
assert "c1" in reflected2.c.keys()
for c in ["c2", "C2", "C3"]:
assert c not in reflected2.c.keys()
def test_autoincrement(self, metadata, connection):
meta = metadata
Table(
"ai_1",
meta,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
mysql_engine="MyISAM",
)
Table(
"ai_2",
meta,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
mysql_engine="MyISAM",
)
Table(
"ai_3",
meta,
Column(
"int_n",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
Column("int_y", Integer, primary_key=True, autoincrement=True),
mysql_engine="MyISAM",
)
Table(
"ai_4",
meta,
Column(
"int_n",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
Column(
"int_n2",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
mysql_engine="MyISAM",
)
Table(
"ai_5",
meta,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column(
"int_n",
Integer,
DefaultClause("0"),
primary_key=True,
autoincrement=False,
),
mysql_engine="MyISAM",
)
Table(
"ai_6",
meta,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
mysql_engine="MyISAM",
)
Table(
"ai_7",
meta,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
mysql_engine="MyISAM",
)
Table(
"ai_8",
meta,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
mysql_engine="MyISAM",
)
meta.create_all(connection)
table_names = [
"ai_1",
"ai_2",
"ai_3",
"ai_4",
"ai_5",
"ai_6",
"ai_7",
"ai_8",
]
mr = MetaData()
mr.reflect(connection, only=table_names)
for tbl in [mr.tables[name] for name in table_names]:
for c in tbl.c:
if c.name.startswith("int_y"):
assert c.autoincrement
elif c.name.startswith("int_n"):
assert not c.autoincrement
connection.execute(tbl.insert())
if "int_y" in tbl.c:
assert connection.scalar(select(tbl.c.int_y)) == 1
assert (list(connection.execute(
tbl.select()).first()).count(1) == 1)
else:
assert 1 not in list(connection.execute(tbl.select()).first())
def test_view_reflection(self, metadata, connection):
Table("x", metadata, Column("a", Integer), Column("b", String(50)))
metadata.create_all(connection)
conn = connection
conn.exec_driver_sql("CREATE VIEW v1 AS SELECT * FROM x")
conn.exec_driver_sql(
"CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM x")
conn.exec_driver_sql(
"CREATE ALGORITHM=UNDEFINED VIEW v3 AS SELECT * FROM x")
conn.exec_driver_sql(
"CREATE DEFINER=CURRENT_USER VIEW v4 AS SELECT * FROM x")
@event.listens_for(metadata, "before_drop")
def cleanup(*arg, **kw):
with testing.db.begin() as conn:
for v in ["v1", "v2", "v3", "v4"]:
conn.exec_driver_sql("DROP VIEW %s" % v)
insp = inspect(connection)
for v in ["v1", "v2", "v3", "v4"]:
eq_(
[(col["name"], col["type"].__class__)
for col in insp.get_columns(v)],
[("a", mysql.INTEGER), ("b", mysql.VARCHAR)],
)
def test_skip_not_describable(self, metadata, connection):
@event.listens_for(metadata, "before_drop")
def cleanup(*arg, **kw):
with testing.db.begin() as conn:
conn.exec_driver_sql("DROP TABLE IF EXISTS test_t1")
conn.exec_driver_sql("DROP TABLE IF EXISTS test_t2")
conn.exec_driver_sql("DROP VIEW IF EXISTS test_v")
conn = connection
conn.exec_driver_sql("CREATE TABLE test_t1 (id INTEGER)")
conn.exec_driver_sql("CREATE TABLE test_t2 (id INTEGER)")
conn.exec_driver_sql("CREATE VIEW test_v AS SELECT id FROM test_t1")
conn.exec_driver_sql("DROP TABLE test_t1")
m = MetaData()
with expect_warnings(
"Skipping .* Table or view named .?test_v.? could not be "
"reflected: .* references invalid table"):
m.reflect(views=True, bind=conn)
eq_(m.tables["test_t2"].name, "test_t2")
assert_raises_message(
exc.UnreflectableTableError,
"references invalid table",
Table,
"test_v",
MetaData(),
autoload_with=conn,
)
@testing.exclude("mysql", "<", (5, 0, 0), "no information_schema support")
def test_system_views(self):
dialect = testing.db.dialect
connection = testing.db.connect()
view_names = dialect.get_view_names(connection, "information_schema")
self.assert_("TABLES" in view_names)
def test_nullable_reflection(self, metadata, connection):
"""test reflection of NULL/NOT NULL, in particular with TIMESTAMP
defaults where MySQL is inconsistent in how it reports CREATE TABLE.
"""
meta = metadata
# this is ideally one table, but older MySQL versions choke
# on the multiple TIMESTAMP columns
row = connection.exec_driver_sql(
"show variables like '%%explicit_defaults_for_timestamp%%'").first(
)
explicit_defaults_for_timestamp = row[1].lower() in ("on", "1", "true")
reflected = []
for idx, cols in enumerate([
[
"x INTEGER NULL",
"y INTEGER NOT NULL",
"z INTEGER",
"q TIMESTAMP NULL",
],
["p TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP"],
["r TIMESTAMP NOT NULL"],
["s TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"],
["t TIMESTAMP"],
["u TIMESTAMP DEFAULT CURRENT_TIMESTAMP"],
]):
Table("nn_t%d" % idx, meta) # to allow DROP
connection.exec_driver_sql("""
CREATE TABLE nn_t%d (
%s
)
""" % (idx, ", \n".join(cols)))
reflected.extend({
"name": d["name"],
"nullable": d["nullable"],
"default": d["default"],
} for d in inspect(connection).get_columns("nn_t%d" % idx))
if connection.dialect._is_mariadb_102:
current_timestamp = "current_timestamp()"
else:
current_timestamp = "CURRENT_TIMESTAMP"
eq_(
reflected,
[
{
"name": "x",
"nullable": True,
"default": None
},
{
"name": "y",
"nullable": False,
"default": None
},
{
"name": "z",
"nullable": True,
"default": None
},
{
"name": "q",
"nullable": True,
"default": None
},
{
"name": "p",
"nullable": True,
"default": current_timestamp
},
{
"name": "r",
"nullable": False,
"default": None if explicit_defaults_for_timestamp else
("%(current_timestamp)s "
"ON UPDATE %(current_timestamp)s") % {
"current_timestamp": current_timestamp
},
},
{
"name": "s",
"nullable": False,
"default": current_timestamp
},
{
"name": "t",
"nullable":
True if explicit_defaults_for_timestamp else False,
"default": None if explicit_defaults_for_timestamp else
("%(current_timestamp)s "
"ON UPDATE %(current_timestamp)s") % {
"current_timestamp": current_timestamp
},
},
{
"name": "u",
"nullable":
True if explicit_defaults_for_timestamp else False,
"default": current_timestamp,
},
],
)
def test_reflection_with_unique_constraint(self, metadata, connection):
insp = inspect(connection)
meta = metadata
uc_table = Table(
"mysql_uc",
meta,
Column("a", String(10)),
UniqueConstraint("a", name="uc_a"),
)
uc_table.create(connection)
# MySQL converts unique constraints into unique indexes.
# separately we get both
indexes = dict((i["name"], i) for i in insp.get_indexes("mysql_uc"))
constraints = set(i["name"]
for i in insp.get_unique_constraints("mysql_uc"))
self.assert_("uc_a" in indexes)
self.assert_(indexes["uc_a"]["unique"])
self.assert_("uc_a" in constraints)
# reflection here favors the unique index, as that's the
# more "official" MySQL construct
reflected = Table("mysql_uc", MetaData(), autoload_with=testing.db)
indexes = dict((i.name, i) for i in reflected.indexes)
constraints = set(uc.name for uc in reflected.constraints)
self.assert_("uc_a" in indexes)
self.assert_(indexes["uc_a"].unique)
self.assert_("uc_a" not in constraints)
def test_reflect_fulltext(self, metadata, connection):
mt = Table(
"mytable",
metadata,
Column("id", Integer, primary_key=True),
Column("textdata", String(50)),
mariadb_engine="InnoDB",
mysql_engine="InnoDB",
)
Index(
"textdata_ix",
mt.c.textdata,
mysql_prefix="FULLTEXT",
mariadb_prefix="FULLTEXT",
)
metadata.create_all(connection)
mt = Table("mytable", MetaData(), autoload_with=testing.db)
idx = list(mt.indexes)[0]
eq_(idx.name, "textdata_ix")
eq_(idx.dialect_options[testing.db.name]["prefix"], "FULLTEXT")
self.assert_compile(
CreateIndex(idx),
"CREATE FULLTEXT INDEX textdata_ix ON mytable (textdata)",
)
@testing.requires.mysql_ngram_fulltext
def test_reflect_fulltext_comment(
self,
metadata,
connection,
):
mt = Table(
"mytable",
metadata,
Column("id", Integer, primary_key=True),
Column("textdata", String(50)),
mysql_engine="InnoDB",
)
Index(
"textdata_ix",
mt.c.textdata,
mysql_prefix="FULLTEXT",
mysql_with_parser="ngram",
)
metadata.create_all(connection)
mt = Table("mytable", MetaData(), autoload_with=connection)
idx = list(mt.indexes)[0]
eq_(idx.name, "textdata_ix")
eq_(idx.dialect_options["mysql"]["prefix"], "FULLTEXT")
eq_(idx.dialect_options["mysql"]["with_parser"], "ngram")
self.assert_compile(
CreateIndex(idx),
"CREATE FULLTEXT INDEX textdata_ix ON mytable "
"(textdata) WITH PARSER ngram",
)
def test_non_column_index(self, metadata, connection):
m1 = metadata
t1 = Table("add_ix",
m1,
Column("x", String(50)),
mysql_engine="InnoDB")
Index("foo_idx", t1.c.x.desc())
m1.create_all(connection)
insp = inspect(connection)
eq_(
insp.get_indexes("add_ix"),
[{
"name": "foo_idx",
"column_names": ["x"],
"unique": False
}],
)
def _bug_88718_96365_casing_0(self):
fkeys_casing_0 = [
{
"name": "FK_PlaylistTTrackId",
"constrained_columns": ["TTrackID"],
"referred_schema": "Test_Schema",
"referred_table": "Track",
"referred_columns": ["trackid"],
"options": {},
},
{
"name": "FK_PlaylistTrackId",
"constrained_columns": ["TrackID"],
"referred_schema": None,
"referred_table": "Track",
"referred_columns": ["trackid"],
"options": {},
},
]
ischema_casing_0 = [
("Test", "Track", "TrackID"),
("Test_Schema", "Track", "TrackID"),
]
return fkeys_casing_0, ischema_casing_0
def _bug_88718_96365_casing_1(self):
fkeys_casing_1 = [
{
"name": "FK_PlaylistTTrackId",
"constrained_columns": ["TTrackID"],
"referred_schema": "Test_Schema",
"referred_table": "Track",
"referred_columns": ["trackid"],
"options": {},
},
{
"name": "FK_PlaylistTrackId",
"constrained_columns": ["TrackID"],
"referred_schema": None,
"referred_table": "Track",
"referred_columns": ["trackid"],
"options": {},
},
]
ischema_casing_1 = [
(util.u("Test"), util.u("Track"), "TrackID"),
(util.u("Test_Schema"), util.u("Track"), "TrackID"),
]
return fkeys_casing_1, ischema_casing_1
def _bug_88718_96365_casing_2(self):
fkeys_casing_2 = [
{
"name": "FK_PlaylistTTrackId",
"constrained_columns": ["TTrackID"],
# I haven't tested schema name but since col/table both
# do it, assume schema name also comes back wrong
"referred_schema": "test_schema",
"referred_table": "track",
"referred_columns": ["trackid"],
"options": {},
},
{
"name": "FK_PlaylistTrackId",
"constrained_columns": ["TrackID"],
"referred_schema": None,
# table name also comes back wrong (probably schema also)
# with casing=2, see https://bugs.mysql.com/bug.php?id=96365
"referred_table": "track",
"referred_columns": ["trackid"],
"options": {},
},
]
ischema_casing_2 = [
("Test", "Track", "TrackID"),
("Test_Schema", "Track", "TrackID"),
]
return fkeys_casing_2, ischema_casing_2
def test_correct_for_mysql_bugs_88718_96365(self):
dialect = mysql.dialect()
for casing, (fkeys, ischema) in [
(0, self._bug_88718_96365_casing_0()),
(1, self._bug_88718_96365_casing_1()),
(2, self._bug_88718_96365_casing_2()),
]:
dialect._casing = casing
dialect.default_schema_name = "Test"
connection = mock.Mock(dialect=dialect,
execute=lambda stmt, params: ischema)
dialect._correct_for_mysql_bugs_88718_96365(fkeys, connection)
eq_(
fkeys,
[
{
"name": "FK_PlaylistTTrackId",
"constrained_columns": ["TTrackID"],
"referred_schema": "Test_Schema",
"referred_table": "Track",
"referred_columns": ["TrackID"],
"options": {},
},
{
"name": "FK_PlaylistTrackId",
"constrained_columns": ["TrackID"],
"referred_schema": None,
"referred_table": "Track",
"referred_columns": ["TrackID"],
"options": {},
},
],
)
def test_case_sensitive_column_constraint_reflection(
self, metadata, connection):
# test for issue #4344 which works around
# MySQL 8.0 bug https://bugs.mysql.com/bug.php?id=88718
m1 = metadata
Table(
"Track",
m1,
Column("TrackID", Integer, primary_key=True),
mysql_engine="InnoDB",
)
Table(
"Track",
m1,
Column("TrackID", Integer, primary_key=True),
schema=testing.config.test_schema,
mysql_engine="InnoDB",
)
Table(
"PlaylistTrack",
m1,
Column("id", Integer, primary_key=True),
Column(
"TrackID",
ForeignKey("Track.TrackID", name="FK_PlaylistTrackId"),
),
Column(
"TTrackID",
ForeignKey(
"%s.Track.TrackID" % (testing.config.test_schema, ),
name="FK_PlaylistTTrackId",
),
),
mysql_engine="InnoDB",
)
m1.create_all(connection)
if connection.dialect._casing in (1, 2):
# the original test for the 88718 fix here in [ticket:4344]
# actually set referred_table='track', with the wrong casing!
# this test was never run. with [ticket:4751], I've gone through
# the trouble to create docker containers with true
# lower_case_table_names=2 and per
# https://bugs.mysql.com/bug.php?id=96365 the table name being
# lower case is also an 8.0 regression.
eq_(
inspect(connection).get_foreign_keys("PlaylistTrack"),
[
{
"name": "FK_PlaylistTTrackId",
"constrained_columns": ["TTrackID"],
"referred_schema": testing.config.test_schema,
"referred_table": "Track",
"referred_columns": ["TrackID"],
"options": {},
},
{
"name": "FK_PlaylistTrackId",
"constrained_columns": ["TrackID"],
"referred_schema": None,
"referred_table": "Track",
"referred_columns": ["TrackID"],
"options": {},
},
],
)
else:
eq_(
sorted(
inspect(connection).get_foreign_keys("PlaylistTrack"),
key=lambda elem: elem["name"],
),
[
{
"name": "FK_PlaylistTTrackId",
"constrained_columns": ["TTrackID"],
"referred_schema": testing.config.test_schema,
"referred_table": "Track",
"referred_columns": ["TrackID"],
"options": {},
},
{
"name": "FK_PlaylistTrackId",
"constrained_columns": ["TrackID"],
"referred_schema": None,
"referred_table": "Track",
"referred_columns": ["TrackID"],
"options": {},
},
],
)
def test_get_foreign_key_name_w_foreign_key_in_name(
self, metadata, connection):
Table(
"a",
metadata,
Column("id", Integer, primary_key=True),
mysql_engine="InnoDB",
)
cons = ForeignKeyConstraint(["aid"], ["a.id"],
name="foreign_key_thing_with_stuff")
Table(
"b",
metadata,
Column("id", Integer, primary_key=True),
Column("aid", ),
cons,
mysql_engine="InnoDB",
)
actual_name = cons.name
metadata.create_all(connection)
if testing.requires.foreign_key_constraint_name_reflection.enabled:
expected_name = actual_name
else:
expected_name = "b_ibfk_1"
eq_(
inspect(connection).get_foreign_keys("b"),
[{
"name": expected_name,
"constrained_columns": ["aid"],
"referred_schema": None,
"referred_table": "a",
"referred_columns": ["id"],
"options": {},
}],
)
@testing.requires.mysql_fully_case_sensitive
def test_case_sensitive_reflection_dual_case_references(
self, metadata, connection):
# this tests that within the fix we do for MySQL bug
# 88718, we don't do case-insensitive logic if the backend
# is case sensitive
m = metadata
Table(
"t1",
m,
Column("some_id", Integer, primary_key=True),
mysql_engine="InnoDB",
)
Table(
"T1",
m,
Column("Some_Id", Integer, primary_key=True),
mysql_engine="InnoDB",
)
Table(
"t2",
m,
Column("id", Integer, primary_key=True),
Column("t1id", ForeignKey("t1.some_id", name="t1id_fk")),
Column("cap_t1id", ForeignKey("T1.Some_Id", name="cap_t1id_fk")),
mysql_engine="InnoDB",
)
m.create_all(connection)
eq_(
dict((rec["name"], rec)
for rec in inspect(connection).get_foreign_keys("t2")),
{
"cap_t1id_fk": {
"name": "cap_t1id_fk",
"constrained_columns": ["cap_t1id"],
"referred_schema": None,
"referred_table": "T1",
"referred_columns": ["Some_Id"],
"options": {},
},
"t1id_fk": {
"name": "t1id_fk",
"constrained_columns": ["t1id"],
"referred_schema": None,
"referred_table": "t1",
"referred_columns": ["some_id"],
"options": {},
},
},
)
from sqlalchemy.dialects.postgresql import ARRAY, REAL
from credovi.schema import metadata, schema
from credovi.util.sqlalchemy import comment_on_table_elements
domains = Table('domains',
metadata,
Column('domain_id',
Integer,
autoincrement=True,
nullable=False),
Column('db_source', String(12), nullable=False),
Column('db_accession_id', String(16), nullable=False),
Column('num_chains',
Integer,
DefaultClause('0'),
nullable=False),
Column('num_binding_sites',
Integer,
DefaultClause('0'),
nullable=False),
Column('num_bs_with_drug_likes',
Integer,
DefaultClause('0'),
nullable=False),
Column('num_bs_with_fragments',
Integer,
DefaultClause('0'),
nullable=False),
Column('description', Text),
schema=schema)
def test_timestamp_defaults(self):
"""Exercise funky TIMESTAMP default syntax when used in columns."""
columns = [
([TIMESTAMP], {}, "TIMESTAMP NULL"),
([mysql.MSTimeStamp], {}, "TIMESTAMP NULL"),
(
[
mysql.MSTimeStamp(),
DefaultClause(sql.text("CURRENT_TIMESTAMP")),
],
{},
"TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp,
DefaultClause(sql.text("CURRENT_TIMESTAMP")),
],
{"nullable": False},
"TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp,
DefaultClause(sql.text("'1999-09-09 09:09:09'")),
],
{"nullable": False},
"TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09'",
),
(
[
mysql.MSTimeStamp(),
DefaultClause(sql.text("'1999-09-09 09:09:09'")),
],
{},
"TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09'",
),
(
[
mysql.MSTimeStamp(),
DefaultClause(
sql.text(
"'1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP"
)
),
],
{},
"TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp,
DefaultClause(
sql.text(
"'1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP"
)
),
],
{"nullable": False},
"TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp(),
DefaultClause(
sql.text(
"CURRENT_TIMESTAMP " "ON UPDATE CURRENT_TIMESTAMP"
)
),
],
{},
"TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP "
"ON UPDATE CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp,
DefaultClause(
sql.text(
"CURRENT_TIMESTAMP " "ON UPDATE CURRENT_TIMESTAMP"
)
),
],
{"nullable": False},
"TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP "
"ON UPDATE CURRENT_TIMESTAMP",
),
]
for spec, kw, expected in columns:
c = Column("t", *spec, **kw)
Table("t", MetaData(), c)
self.assert_compile(schema.CreateColumn(c), "t %s" % expected)
def test_empty_insert_pk4(self):
self._test_empty_insert(
Table('d', MetaData(testing.db),
Column('x', Integer, primary_key=True),
Column('y', Integer, DefaultClause('123'))))
def test_autoincrement(self):
meta = MetaData(testing.db)
try:
Table('ai_1',
meta,
Column('int_y',
Integer,
primary_key=True,
autoincrement=True),
Column('int_n',
Integer,
DefaultClause('0'),
primary_key=True),
mysql_engine='MyISAM')
Table('ai_2',
meta,
Column('int_y',
Integer,
primary_key=True,
autoincrement=True),
Column('int_n',
Integer,
DefaultClause('0'),
primary_key=True),
mysql_engine='MyISAM')
Table('ai_3',
meta,
Column('int_n',
Integer,
DefaultClause('0'),
primary_key=True,
autoincrement=False),
Column('int_y',
Integer,
primary_key=True,
autoincrement=True),
mysql_engine='MyISAM')
Table('ai_4',
meta,
Column('int_n',
Integer,
DefaultClause('0'),
primary_key=True,
autoincrement=False),
Column('int_n2',
Integer,
DefaultClause('0'),
primary_key=True,
autoincrement=False),
mysql_engine='MyISAM')
Table('ai_5',
meta,
Column('int_y',
Integer,
primary_key=True,
autoincrement=True),
Column('int_n',
Integer,
DefaultClause('0'),
primary_key=True,
autoincrement=False),
mysql_engine='MyISAM')
Table('ai_6',
meta,
Column('o1', String(1), DefaultClause('x'),
primary_key=True),
Column('int_y',
Integer,
primary_key=True,
autoincrement=True),
mysql_engine='MyISAM')
Table('ai_7',
meta,
Column('o1', String(1), DefaultClause('x'),
primary_key=True),
Column('o2', String(1), DefaultClause('x'),
primary_key=True),
Column('int_y',
Integer,
primary_key=True,
autoincrement=True),
mysql_engine='MyISAM')
Table('ai_8',
meta,
Column('o1', String(1), DefaultClause('x'),
primary_key=True),
Column('o2', String(1), DefaultClause('x'),
primary_key=True),
mysql_engine='MyISAM')
meta.create_all()
table_names = [
'ai_1', 'ai_2', 'ai_3', 'ai_4', 'ai_5', 'ai_6', 'ai_7', 'ai_8'
]
mr = MetaData(testing.db)
mr.reflect(only=table_names)
for tbl in [mr.tables[name] for name in table_names]:
for c in tbl.c:
if c.name.startswith('int_y'):
assert c.autoincrement
elif c.name.startswith('int_n'):
assert not c.autoincrement
tbl.insert().execute()
if 'int_y' in tbl.c:
assert select([tbl.c.int_y]).scalar() == 1
assert list(tbl.select().execute().first()).count(1) == 1
else:
assert 1 not in list(tbl.select().execute().first())
finally:
meta.drop_all()
class Authority(db.Model):
__tablename__ = "authorities"
id = Column(Integer, primary_key=True)
owner = Column(String(128), nullable=False)
name = Column(String(128), unique=True)
body = Column(Text())
chain = Column(Text())
active = Column(Boolean, default=True)
plugin_name = Column(String(64))
description = Column(Text)
options = Column(JSON)
date_created = Column(DateTime, DefaultClause(func.now()), nullable=False)
roles = relationship(
"Role",
secondary=roles_authorities,
passive_deletes=True,
backref=db.backref("authority"),
lazy="dynamic",
)
user_id = Column(Integer, ForeignKey("users.id"))
authority_certificate = relationship(
"Certificate",
backref="root_authority",
uselist=False,
foreign_keys="Certificate.root_authority_id",
)
certificates = relationship("Certificate",
backref="authority",
foreign_keys="Certificate.authority_id")
authority_pending_certificate = relationship(
"PendingCertificate",
backref="root_authority",
uselist=False,
foreign_keys="PendingCertificate.root_authority_id",
)
pending_certificates = relationship(
"PendingCertificate",
backref="authority",
foreign_keys="PendingCertificate.authority_id",
)
def __init__(self, **kwargs):
self.owner = kwargs["owner"]
self.roles = kwargs.get("roles", [])
self.name = kwargs.get("name")
self.description = kwargs.get("description")
self.authority_certificate = kwargs["authority_certificate"]
self.plugin_name = kwargs["plugin"]["slug"]
self.options = kwargs.get("options")
@property
def plugin(self):
return plugins.get(self.plugin_name)
@property
def is_cab_compliant(self):
"""
Parse the options to find whether authority is CAB Forum Compliant,
i.e., adhering to the CA/Browser Forum Baseline Requirements.
Returns None if option is not available
"""
if not self.options:
return None
options_array = json.loads(self.options)
if isinstance(options_array, list):
for option in options_array:
if "name" in option and option["name"] == 'cab_compliant':
return option["value"]
return None
@property
def max_issuance_days(self):
if self.is_cab_compliant:
return current_app.config.get("PUBLIC_CA_MAX_VALIDITY_DAYS", 397)
@property
def default_validity_days(self):
if self.is_cab_compliant:
return current_app.config.get("PUBLIC_CA_MAX_VALIDITY_DAYS", 397)
return current_app.config.get("DEFAULT_VALIDITY_DAYS",
365) # 1 year default
def __repr__(self):
return "Authority(name={name})".format(name=self.name)
@property
def is_cn_optional(self):
"""
Parse the options to find whether common name is treated as an optional field.
Returns False if option is not available
"""
if not self.options:
return False
options_array = json.loads(self.options)
if isinstance(options_array, list):
for option in options_array:
if "name" in option and option["name"] == 'cn_optional':
return option["value"]
return False
class PendingCertificate(db.Model):
__tablename__ = "pending_certs"
id = Column(Integer, primary_key=True)
external_id = Column(String(128))
owner = Column(String(128), nullable=False)
name = Column(String(256), unique=True)
description = Column(String(1024))
notify = Column(Boolean, default=True)
number_attempts = Column(Integer)
rename = Column(Boolean, default=True)
resolved = Column(Boolean, default=False)
resolved_cert_id = Column(Integer, nullable=True)
cn = Column(String(128))
csr = Column(Text(), nullable=False)
chain = Column(Text())
private_key = Column(Vault, nullable=True)
date_created = Column(ArrowType, DefaultClause(func.now()), nullable=False)
dns_provider_id = Column(
Integer, ForeignKey("dns_providers.id", ondelete="CASCADE")
)
status = Column(Text(), nullable=True)
last_updated = Column(
ArrowType, DefaultClause(func.now()), onupdate=func.now(), nullable=False
)
rotation = Column(Boolean, default=False)
user_id = Column(Integer, ForeignKey("users.id"))
authority_id = Column(Integer, ForeignKey("authorities.id", ondelete="CASCADE"))
root_authority_id = Column(
Integer, ForeignKey("authorities.id", ondelete="CASCADE")
)
rotation_policy_id = Column(Integer, ForeignKey("rotation_policies.id"))
notifications = relationship(
"Notification",
secondary=pending_cert_notification_associations,
backref="pending_cert",
passive_deletes=True,
)
destinations = relationship(
"Destination",
secondary=pending_cert_destination_associations,
backref="pending_cert",
passive_deletes=True,
)
sources = relationship(
"Source",
secondary=pending_cert_source_associations,
backref="pending_cert",
passive_deletes=True,
)
roles = relationship(
"Role",
secondary=pending_cert_role_associations,
backref="pending_cert",
passive_deletes=True,
)
replaces = relationship(
"Certificate",
secondary=pending_cert_replacement_associations,
backref="pending_cert",
passive_deletes=True,
)
options = Column(JSONType)
rotation_policy = relationship("RotationPolicy")
sensitive_fields = ("private_key",)
def __init__(self, **kwargs):
self.csr = kwargs.get("csr")
self.private_key = kwargs.get("private_key", "")
if self.private_key:
# If the request does not send private key, the key exists but the value is None
self.private_key = self.private_key.strip()
self.external_id = kwargs.get("external_id")
# when destinations are appended they require a valid name.
if kwargs.get("name"):
self.name = get_or_increase_name(defaults.text_to_slug(kwargs["name"]), 0)
self.rename = False
else:
# TODO: Fix auto-generated name, it should be renamed on creation
self.name = get_or_increase_name(
defaults.certificate_name(
kwargs["common_name"],
kwargs["authority"].name,
dt.now(),
dt.now(),
False,
),
self.external_id,
)
self.rename = True
self.cn = defaults.common_name(utils.parse_csr(self.csr))
self.owner = kwargs["owner"]
self.number_attempts = 0
if kwargs.get("chain"):
self.chain = kwargs["chain"].strip()
self.notify = kwargs.get("notify", True)
self.destinations = kwargs.get("destinations", [])
self.notifications = kwargs.get("notifications", [])
self.description = kwargs.get("description")
self.roles = list(set(kwargs.get("roles", [])))
self.replaces = kwargs.get("replaces", [])
self.rotation = kwargs.get("rotation")
self.rotation_policy = kwargs.get("rotation_policy")
try:
self.dns_provider_id = kwargs.get("dns_provider").id
except (AttributeError, KeyError, TypeError, Exception):
pass
