def test_tokens(self):
m = MetaData()
sane_alone = Table('t', m, Column('id', Integer))
sane_schema = Table('t', m, Column('id', Integer), schema='s')
insane_alone = Table('t t', m, Column('id', Integer))
insane_schema = Table('t t', m, Column('id', Integer),
schema='s s')
ddl = DDL('%(schema)s-%(table)s-%(fullname)s')
dialect = self.mock_engine().dialect
self.assert_compile(ddl.against(sane_alone), '-t-t',
dialect=dialect)
self.assert_compile(ddl.against(sane_schema), 's-t-s.t',
dialect=dialect)
self.assert_compile(ddl.against(insane_alone), '-"t t"-"t t"',
dialect=dialect)
self.assert_compile(ddl.against(insane_schema),
'"s s"-"t t"-"s s"."t t"', dialect=dialect)
# overrides are used piece-meal and verbatim.
ddl = DDL('%(schema)s-%(table)s-%(fullname)s-%(bonus)s',
context={'schema': 'S S', 'table': 'T T', 'bonus': 'b'
})
self.assert_compile(ddl.against(sane_alone), 'S S-T T-t-b',
dialect=dialect)
self.assert_compile(ddl.against(sane_schema), 'S S-T T-s.t-b',
dialect=dialect)
self.assert_compile(ddl.against(insane_alone), 'S S-T T-"t t"-b',
dialect=dialect)
self.assert_compile(ddl.against(insane_schema),
'S S-T T-"s s"."t t"-b', dialect=dialect)
def test_ddl_execute(self):
engine = create_engine('sqlite:///')
cx = engine.connect()
table = self.users
ddl = DDL('SELECT 1')
for py in ('engine.execute(ddl)',
'engine.execute(ddl, table)',
'cx.execute(ddl)',
'cx.execute(ddl, table)',
'ddl.execute(engine)',
'ddl.execute(engine, table)',
'ddl.execute(cx)',
'ddl.execute(cx, table)'):
r = eval(py)
assert list(r) == [(1,)], py
for py in ('ddl.execute()',
'ddl.execute(schema_item=table)'):
try:
r = eval(py)
assert False
except tsa.exc.UnboundExecutionError:
pass
for bind in engine, cx:
ddl.bind = bind
for py in ('ddl.execute()',
'ddl.execute(schema_item=table)'):
r = eval(py)
assert list(r) == [(1,)], py
def test_ddl_execute(self):
engine = create_engine("sqlite:///")
cx = engine.connect()
table = self.users
ddl = DDL("SELECT 1")
for py in (
"engine.execute(ddl)",
"engine.execute(ddl, table)",
"cx.execute(ddl)",
"cx.execute(ddl, table)",
"ddl.execute(engine)",
"ddl.execute(engine, table)",
"ddl.execute(cx)",
"ddl.execute(cx, table)",
):
r = eval(py)
assert list(r) == [(1,)], py
for py in ("ddl.execute()", "ddl.execute(target=table)"):
try:
r = eval(py)
assert False
except tsa.exc.UnboundExecutionError:
pass
for bind in engine, cx:
ddl.bind = bind
for py in ("ddl.execute()", "ddl.execute(target=table)"):
r = eval(py)
assert list(r) == [(1,)], py
def test_tokens(self):
m = MetaData()
sane_alone = Table("t", m, Column("id", Integer))
sane_schema = Table("t", m, Column("id", Integer), schema="s")
insane_alone = Table("t t", m, Column("id", Integer))
insane_schema = Table("t t", m, Column("id", Integer), schema="s s")
ddl = DDL("%(schema)s-%(table)s-%(fullname)s")
dialect = self.mock_engine().dialect
self.assert_compile(ddl.against(sane_alone), "-t-t", dialect=dialect)
self.assert_compile(
ddl.against(sane_schema), "s-t-s.t", dialect=dialect
)
self.assert_compile(
ddl.against(insane_alone), '-"t t"-"t t"', dialect=dialect
)
self.assert_compile(
ddl.against(insane_schema),
'"s s"-"t t"-"s s"."t t"',
dialect=dialect,
)
# overrides are used piece-meal and verbatim.
ddl = DDL(
"%(schema)s-%(table)s-%(fullname)s-%(bonus)s",
context={"schema": "S S", "table": "T T", "bonus": "b"},
)
self.assert_compile(
ddl.against(sane_alone), "S S-T T-t-b", dialect=dialect
)
self.assert_compile(
ddl.against(sane_schema), "S S-T T-s.t-b", dialect=dialect
)
self.assert_compile(
ddl.against(insane_alone), 'S S-T T-"t t"-b', dialect=dialect
)
self.assert_compile(
ddl.against(insane_schema),
'S S-T T-"s s"."t t"-b',
dialect=dialect,
)
def test_metadata(self):
metadata, engine = self.metadata, self.engine
event.listen(metadata, 'before_create', DDL('mxyzptlk'))
event.listen(metadata, 'after_create', DDL('klptzyxm'))
event.listen(metadata, 'before_drop', DDL('xyzzy'))
event.listen(metadata, 'after_drop', DDL('fnord'))
metadata.create_all()
strings = [str(x) for x in engine.mock]
assert 'mxyzptlk' in strings
assert 'klptzyxm' in strings
assert 'xyzzy' not in strings
assert 'fnord' not in strings
del engine.mock[:]
metadata.drop_all()
strings = [str(x) for x in engine.mock]
assert 'mxyzptlk' not in strings
assert 'klptzyxm' not in strings
assert 'xyzzy' in strings
assert 'fnord' in strings
def test_metadata(self):
metadata, engine = self.metadata, self.engine
event.listen(metadata, "before_create", DDL("mxyzptlk"))
event.listen(metadata, "after_create", DDL("klptzyxm"))
event.listen(metadata, "before_drop", DDL("xyzzy"))
event.listen(metadata, "after_drop", DDL("fnord"))
metadata.create_all()
strings = [str(x) for x in engine.mock]
assert "mxyzptlk" in strings
assert "klptzyxm" in strings
assert "xyzzy" not in strings
assert "fnord" not in strings
del engine.mock[:]
metadata.drop_all()
strings = [str(x) for x in engine.mock]
assert "mxyzptlk" not in strings
assert "klptzyxm" not in strings
assert "xyzzy" in strings
assert "fnord" in strings
def test_filter(self):
cx = self.mock_engine()
tbl = Table("t", MetaData(), Column("id", Integer))
target = cx.name
assert DDL("")._should_execute(tbl, cx)
assert DDL("").execute_if(dialect=target)._should_execute(tbl, cx)
assert not DDL("").execute_if(dialect="bogus")._should_execute(tbl, cx)
assert (
DDL("")
.execute_if(callable_=lambda d, y, z, **kw: True)
._should_execute(tbl, cx)
)
assert (
DDL("")
.execute_if(
callable_=lambda d, y, z, **kw: z.engine.name != "bogus"
)
._should_execute(tbl, cx)
)
def test_metadata_deprecated(self):
metadata, engine = self.metadata, self.engine
DDL("mxyzptlk").execute_at("before-create", metadata)
DDL("klptzyxm").execute_at("after-create", metadata)
DDL("xyzzy").execute_at("before-drop", metadata)
DDL("fnord").execute_at("after-drop", metadata)
metadata.create_all()
strings = [str(x) for x in engine.mock]
assert "mxyzptlk" in strings
assert "klptzyxm" in strings
assert "xyzzy" not in strings
assert "fnord" not in strings
del engine.mock[:]
metadata.drop_all()
strings = [str(x) for x in engine.mock]
assert "mxyzptlk" not in strings
assert "klptzyxm" not in strings
assert "xyzzy" in strings
assert "fnord" in strings
def fts_virtual_table(self) -> DDL:
template = """
CREATE VIRTUAL TABLE IF NOT EXISTS %(name)s
USING fts5(%(columns)s, content=%(view_name)s, content_rowid=%(rowid_name)s)
"""
info = {
'name': self.idx_name,
'columns': ', '.join([c.key for c in self.columns]),
'view_name': self.view_name,
'rowid_name': self.rowid_c.key,
}
return DDL(template % info)
def test_metadata_deprecated(self):
metadata, engine = self.metadata, self.engine
DDL('mxyzptlk').execute_at('before-create', metadata)
DDL('klptzyxm').execute_at('after-create', metadata)
DDL('xyzzy').execute_at('before-drop', metadata)
DDL('fnord').execute_at('after-drop', metadata)
metadata.create_all()
strings = [str(x) for x in engine.mock]
assert 'mxyzptlk' in strings
assert 'klptzyxm' in strings
assert 'xyzzy' not in strings
assert 'fnord' not in strings
del engine.mock[:]
metadata.drop_all()
strings = [str(x) for x in engine.mock]
assert 'mxyzptlk' not in strings
assert 'klptzyxm' not in strings
assert 'xyzzy' in strings
assert 'fnord' in strings
def test_filter_deprecated(self):
cx = self.engine
tbl = Table("t", MetaData(), Column("id", Integer))
target = cx.name
assert DDL("")._should_execute_deprecated("x", tbl, cx)
with testing.expect_deprecated(".* is deprecated .*"):
assert DDL("", on=target)._should_execute_deprecated("x", tbl, cx)
with testing.expect_deprecated(".* is deprecated .*"):
assert not DDL("", on="bogus")._should_execute_deprecated(
"x", tbl, cx
)
with testing.expect_deprecated(".* is deprecated .*"):
assert DDL(
"", on=lambda d, x, y, z: True
)._should_execute_deprecated("x", tbl, cx)
with testing.expect_deprecated(".* is deprecated .*"):
assert DDL(
"", on=lambda d, x, y, z: z.engine.name != "bogus"
)._should_execute_deprecated("x", tbl, cx)
def init():
"""Initializes the service."""
# Create ES indexes.
es = Elasticsearch(app.config['ELASTICSEARCH_URL'])
for key in ['ELASTICSEARCH_GLOSSARY', 'ELASTICSEARCH_SIMILARITY']:
try:
if config_name == 'test':
es.indices.delete(index=app.config[key], ignore=[400, 404])
es.indices.create(index=app.config[key])
except TransportError as e:
# ignore already existing index
if e.error == 'resource_already_exists_exception':
pass
else:
raise
es.indices.put_mapping(
doc_type='_doc',
body=json.load(open('./elasticsearch/alegre_glossary.json')),
index=app.config['ELASTICSEARCH_GLOSSARY']
)
es.indices.put_mapping(
doc_type='_doc',
body=json.load(open('./elasticsearch/alegre_similarity.json')),
index=app.config['ELASTICSEARCH_SIMILARITY']
)
es.indices.close(index=app.config['ELASTICSEARCH_SIMILARITY'])
es.indices.put_settings(
body=json.load(open('./elasticsearch/alegre_similarity_settings.json')),
index=app.config['ELASTICSEARCH_SIMILARITY']
)
es.indices.open(index=app.config['ELASTICSEARCH_SIMILARITY'])
# Create database.
with app.app_context():
if not database_exists(db.engine.url):
create_database(db.engine.url)
if config_name == 'test':
db.drop_all()
sqlalchemy.event.listen(
db.metadata,
'before_create',
DDL("""
CREATE OR REPLACE FUNCTION bit_count(value bigint)
RETURNS integer
AS $$ SELECT length(replace(value::bit(64)::text,'0','')); $$
LANGUAGE SQL IMMUTABLE STRICT;
""")
)
db.create_all()
def test_platform_escape(self):
"""test the escaping of % characters in the DDL construct."""
default_from = testing.db.dialect.statement_compiler(
testing.db.dialect, None).default_from()
eq_(
testing.db.execute(text("select 'foo%something'" +
default_from)).scalar(), 'foo%something')
eq_(
testing.db.execute(DDL("select 'foo%%something'" +
default_from)).scalar(), 'foo%something')
def define_temp_tables(cls, metadata):
user_tmp = Table(
"user_tmp", metadata,
Column("id", sa.INT, primary_key=True),
Column('name', sa.VARCHAR(50)),
Column('foo', sa.INT),
sa.UniqueConstraint('name', name='user_tmp_uq'),
sa.Index("user_tmp_ix", "foo"),
prefixes=["TEMPORARY"],
schema='tmp',
)
if testing.requires.view_reflection.enabled and \
testing.requires.temporary_views.enabled:
event.listen(
user_tmp, "after_create",
DDL("create view user_tmp_v as "
"select * from tmp.user_tmp")
)
event.listen(
user_tmp, "before_drop",
DDL("drop view user_tmp_v")
)
def _create_model(self, model):
model_name = model.__name__
meta = type(self._base_declarative)
if isinstance(model, meta):
raise ImproperlyConfigured('Cannot register declarative classes '
'only mixins allowed')
base = getattr(model, '__inherit_from__', None)
if base:
if base not in self._declarative_register:
models = self._bases.get(base)
if not models:
self._bases[base] = models = []
models.append(model)
return
else:
base = self._declarative_register[base]
else:
base = self._base_declarative
#
# Create SqlAlchemy Model
model = meta(model_name, (model, base), {})
create = getattr(model, '__create_sql__', None)
name = model_name.lower()
if create:
event.listen(self.metadata,
'after_create',
DDL(create.format({'name': name})))
drop = getattr(model, '__drop_sql__', None)
if not drop:
logger.warning('Model %s has create statement but not drop. '
'To mute this warning add a __drop_sql__ '
'statement in the model class', name)
else:
event.listen(self.metadata,
'before_drop',
DDL(drop.format({'name': name})))
return model, name
def test_ddl_execute(self):
engine = create_engine("sqlite:///")
cx = engine.connect()
table = self.users
ddl = DDL("SELECT 1")
for spec in (
(cx.execute, ddl),
(cx.execute, ddl, table),
):
fn = spec[0]
arg = spec[1:]
r = fn(*arg)
eq_(list(r), [(1, )])
def _search_index_ddl(cls):
"""
Returns the ddl for creating the actual search index.
"""
tablename = cls.__tablename__
search_vector_name = cls._get_search_option('search_vector_name')
search_index_name = cls._get_search_option('search_index_name').format(
table=tablename)
return DDL("""
CREATE INDEX {search_index_name} ON {table}
USING gin({search_vector_name})
""".format(table=quote_identifier(tablename),
search_index_name=search_index_name,
search_vector_name=search_vector_name))
def _setup_mysql_fulltext_indexes():
for name, cols in _fulltext_indexes.iteritems():
sql = (
'ALTER TABLE %%(table)s '
'ADD FULLTEXT INDEX media_fulltext_%(name)s (%(cols)s)'
) % {
'name': name,
'cols': ', '.join(col.name for col in cols)
}
event.listen(
media_fulltext,
u'after_create',
DDL(sql).execute_if(dialect=u'mysql')
)
def CreateComment(element, comment):
"""
Returns a DDL to comment on a column. This function can only be used in the
context of an SQLAlchemy listen event or any other function that supports the
SQLAlchemy %(fullname)s string formatting.
"""
if isinstance(element, Table):
statement = "COMMENT ON TABLE %(fullname)s IS '{0}'".format(comment)
elif isinstance(element, Column):
statement = "COMMENT ON COLUMN %(fullname)s.{0} IS '{1}'".format(
element.name, comment)
return DDL(statement)
def define_temp_tables(cls, metadata):
if testing.against("hana"):
kw = {
'prefixes': ["GLOBAL TEMPORARY"],
'oracle_on_commit': 'PRESERVE ROWS'
}
else:
kw = {
'prefixes': ["TEMPORARY"],
}
user_tmp = Table("user_tmp", metadata,
Column("id", sa.INT, primary_key=True),
Column('name', sa.VARCHAR(50)), Column('foo', sa.INT),
sa.UniqueConstraint('name', name='user_tmp_uq'),
sa.Index("user_tmp_ix", "foo"), **kw)
if testing.requires.view_reflection.enabled and \
testing.requires.temporary_views.enabled:
event.listen(
user_tmp, "after_create",
DDL("create temporary view user_tmp_v as "
"select * from user_tmp"))
event.listen(user_tmp, "before_drop", DDL("drop view user_tmp_v"))
def create_partition_insert_trigger(table,
interval=25000,
column='biomolecule_id',
part_label='biomol'):
"""
Creates a trigger on the given (partitioned) table to insert entries into their correct partition based on biomolecule_id
"""
create_func = """
CREATE OR REPLACE FUNCTION %(schema)s.%(table)s_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
intvl integer;
part_bound integer;
BEGIN
intvl = TG_ARGV[0]::integer;
IF NEW.{col_id} %% intvl = 0 THEN
part_bound := NEW.{col_id};
ELSE
part_bound := intvl * ((NEW.{col_id} / intvl) + 1);
END IF;
EXECUTE format('INSERT INTO %(fullname)s_{part_label}_le_%%s VALUES ($1.*);', part_bound) USING NEW;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
""".format(col_id=column, part_label=part_label)
create_trigger = """
CREATE TRIGGER insert_%(table)s_trigger
BEFORE INSERT ON %(fullname)s
FOR EACH ROW EXECUTE PROCEDURE %(schema)s.%(table)s_insert_trigger(%(intvl)s);
"""
event.listen(table, 'after_create', DDL(create_func))
event.listen(table, 'after_create',
DDL(create_trigger, context={'intvl': interval}))
def test_tokens(self):
m = MetaData()
bind = self.mock_engine()
sane_alone = Table('t', m, Column('id', Integer))
sane_schema = Table('t', m, Column('id', Integer), schema='s')
insane_alone = Table('t t', m, Column('id', Integer))
insane_schema = Table('t t', m, Column('id', Integer), schema='s s')
ddl = DDL('%(schema)s-%(table)s-%(fullname)s')
self.assertEquals(ddl._expand(sane_alone, bind), '-t-t')
self.assertEquals(ddl._expand(sane_schema, bind), 's-t-s.t')
self.assertEquals(ddl._expand(insane_alone, bind), '-"t t"-"t t"')
self.assertEquals(ddl._expand(insane_schema, bind),
'"s s"-"t t"-"s s"."t t"')
# overrides are used piece-meal and verbatim.
ddl = DDL('%(schema)s-%(table)s-%(fullname)s-%(bonus)s',
context={'schema':'S S', 'table': 'T T', 'bonus': 'b'})
self.assertEquals(ddl._expand(sane_alone, bind), 'S S-T T-t-b')
self.assertEquals(ddl._expand(sane_schema, bind), 'S S-T T-s.t-b')
self.assertEquals(ddl._expand(insane_alone, bind), 'S S-T T-"t t"-b')
self.assertEquals(ddl._expand(insane_schema, bind),
'S S-T T-"s s"."t t"-b')
def build_fulltext(cls):
"""
build up fulltext index after table is created
"""
if FullText not in cls.__bases__:
return
assert cls.__fulltext_columns__, "Model:{0.__name__} No FullText columns defined".format(
cls)
event.listen(
cls.__table__, 'after_create',
DDL(
MYSQL_BUILD_INDEX_QUERY.format(
cls, ", ".join(
(escape_quote(c) for c in cls.__fulltext_columns__)))))
def with_collkey_ddl(): # pragma: no cover
"""Register creation of collkey function.
Can be called at module level in db initialization scripts to create the collkey
function. Once a session is bound to an engine collkey can be used to create indexes
or in order_by clauses, e.g.::
Index('ducet', collkey(common.Value.name)).create(DBSession.bind)
"""
event.listen(
Base.metadata, 'before_create',
DDL("""
CREATE OR REPLACE FUNCTION collkey (text, text, bool, int4, bool) RETURNS bytea
LANGUAGE 'c' IMMUTABLE STRICT AS
'$libdir/collkey_icu.so',
'pgsqlext_collkey';
""").execute_if(dialect='postgresql'))
def _search_vector_ddl(cls):
"""
Returns the ddl for the search vector.
"""
tablename = cls.__tablename__
search_vector_name = cls._get_search_option('search_vector_name')
return DDL(
"""
ALTER TABLE {table}
ADD COLUMN {search_vector_name} tsvector
"""
.format(
table=quote_identifier(tablename),
search_vector_name=search_vector_name
)
)
def _setup_sqlite_ddl(table):
"""
Barcode default for SQLite using a trigger and the ROWID as the sequence
It does not conform to how the legacy DB is setup but testing on sqlite
should not fail. Since we do not plan to use SQLite as the production
database the DDL below serves only to support development/testing.
"""
DDL("""
CREATE TRIGGER set_rack_barcode AFTER INSERT ON rack
BEGIN
UPDATE rack
SET barcode =
SUBSTR("00000000", length(new.rowid), 8-length(new.rowid)) ||
new.rowid
WHERE rowid = new.rowid;
END;
""",
on='sqlite').execute_at('after-create', table)
def _search_trigger_ddl(cls):
"""
Returns the ddl for creating an automatically updated search trigger.
"""
tablename = cls.__tablename__
search_vector_name = cls._get_search_option('search_vector_name')
search_trigger_name = cls._get_search_option(
'search_trigger_name').format(table=tablename)
return DDL("""
CREATE TRIGGER {search_trigger_name}
BEFORE UPDATE OR INSERT ON {table}
FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger({arguments})
""".format(search_trigger_name=search_trigger_name,
table=quote_identifier(tablename),
arguments=', '.join([
search_vector_name,
"'%s'" % cls._get_search_option('catalog')
] + cls.__searchable_columns__)))
def attach_ddl_listeners(self):
# Remove all previously added listeners, so that same listener don't
# get added twice in situations where class configuration happens in
# multiple phases (issue #31).
self.remove_listeners()
for column in self.processed_columns:
# This sets up the trigger that keeps the tsvector column up to
# date.
if column.type.columns:
table = column.table
if (self.option(column, 'weights')
or vectorizer.contains_tsvector(column)):
self.add_listener((table, 'after_create',
self.search_function_ddl(column)))
self.add_listener(
(table, 'after_drop',
DDL(str(DropSearchFunctionSQL(column)))))
self.add_listener(
(table, 'after_create', self.search_trigger_ddl(column)))
def test_platform_escape(self):
"""test the escaping of % characters in the DDL construct."""
default_from = testing.db.dialect.statement_compiler(
testing.db.dialect, None).default_from()
# We're abusing the DDL()
# construct here by pushing a SELECT through it
# so that we can verify the round trip.
# the DDL() will trigger autocommit, which prohibits
# some DBAPIs from returning results (pyodbc), so we
# run in an explicit transaction.
with testing.db.begin() as conn:
eq_(
conn.execute(text("select 'foo%something'" +
default_from)).scalar(), 'foo%something')
eq_(
conn.execute(DDL("select 'foo%%something'" +
default_from)).scalar(), 'foo%something')
def engine(db):
"""Session-wide database engine"""
engine = db.engine
engine.execute("create extension postgis")
engine.execute(CreateSchema('crmp'))
pycds.Base.metadata.create_all(bind=engine)
# TODO: Is this CREATE OR REPLACE FUNCTION necessary?
sqlalchemy.event.listen(
pycds.weather_anomaly.Base.metadata,
'before_create',
DDL('''
CREATE OR REPLACE FUNCTION crmp.DaysInMonth(date) RETURNS double precision AS
$$
SELECT EXTRACT(DAY FROM CAST(date_trunc('month', $1) + interval '1 month' - interval '1 day'
as timestamp));
$$ LANGUAGE sql;
''')
)
pycds.weather_anomaly.Base.metadata.create_all(bind=engine)
yield engine
def search_index_ddl(self, column):
"""
Returns the ddl for creating the actual search index.
:param column: TSVectorType typed SQLAlchemy column object
"""
tablename = column.table.name
search_index_name = self.option(column, 'search_index_name').format(
table=tablename,
column=column.name
)
return DDL(
"""
CREATE INDEX {search_index_name} ON {table}
USING gin({search_vector_name})
"""
.format(
table=quote_identifier(tablename),
search_index_name=search_index_name,
search_vector_name=column.name
)
)
def install_trigram_indice_on_column(table, column):
idx_name = '{table}_{column}_trigram_idx'.format(table = table.__tablename__, column = column)
create_idx_sql = '''
CREATE INDEX
{idx_name}
ON
{table}
USING
gin ({column} gin_trgm_ops)'''.format(idx_name=idx_name, table=table.__tablename__, column=column)
try:
db.engine.execute('''SELECT '{schema}.{idx}'::regclass;'''.format(schema='public', idx=idx_name))
print("Do not need to create index", idx_name)
except sqlalchemy.exc.ProgrammingError:
# index doesn't exist, need to create it.
print("Creating index {idx} on table {tbl}".format(idx=idx_name, tbl=table.__tablename__))
db.engine.execute(
DDL(
create_idx_sql
)
)
def add_reports():
form = AddReportForm()
if form.validate_on_submit():
stu_usn = form.stu_usn.data
st2_name = form.st2_name.data
dept4 = form.dept4.data
sem4 = form.sem4.data
attpercentage = form.attpercentage.data
new_report = report(stu_usn, st2_name, dept4, sem4, attpercentage)
db.session.add(new_report)
db.session.commit()
return render_template('report.html', form=form)
update_subject_dept = DDL('''\
CREATE TRIGGER update_subject_dept UPDATE OF tc_dept ON TEACHERS
BEGIN
UPDATE SUBJECT SET dept3 = 'eee' WHERE sub_id=4;
END;''')
event.listen(teacher.__table__, 'after_create', update_subject_dept)
def create_view(name, selectable, metadata=Meta):
_mt = MetaData() # temp metadata just for initial Table object creation
t = Table(name, metadata) # the actual mat view class is bound to metadata
for c in selectable.c:
t.append_column(Column(c.name, c.type, primary_key=True))
print(f"View {name} adding Col {c.name}")
if not (any([c.primary_key for c in selectable.c])):
t.append_constraint(
PrimaryKeyConstraint(*[c.name for c in selectable.c]))
print(f"Adding PK Constraint {c.name}")
event.listen(metadata, 'after_create', CreateView(name, selectable))
@event.listens_for(metadata, 'after_create')
def create_indexes(target, connection, **kw):
print("After Create Fired")
for idx in t.indexes:
print("Creating Index")
idx.create(connection)
event.listen(metadata, 'before_drop', DDL('DROP VIEW IF EXISTS ' + name))
return t
direction = Decimal(1)
else:
# A modifier already on a request is being voided
direction = Decimal(-1)
if isinstance(modifier, AbsoluteModifier):
absolute += direction * modifier.value
elif isinstance(modifier, RelativeModifier):
relative += direction * modifier.value
payout = (srp_request.base_payout + absolute) * \
(Decimal(1) + relative)
srp_request.payout = PrettyDecimal(payout)
# The next few lines are responsible for adding a full text search index on the
# Request.details column for MySQL.
_create_fts = DDL('CREATE FULLTEXT INDEX ix_%(table)s_details_fulltext '
'ON %(table)s (details);')
_drop_fts = DDL('DROP INDEX ix_%(table)s_details_fulltext ON %(table)s')
event.listen(
Request.__table__,
'after_create',
_create_fts.execute_if(dialect='mysql')
)
event.listen(
Request.__table__,
'before_drop',
_drop_fts.execute_if(dialect='mysql')
)
def DDL(statement, when=None, obj=None, bind=None, dialect=None, context=None):
"""
Construit un objet de type DDL en utilisant l'API
correspondant à la version de SQLAlchemy en cours
d'utilisation.
Cette fonction peut être utilisée pour exécuter
une commande SQL de deux manières distinctes.
- Soit lorsqu'un événement se produit (par exemple,
lors de la création d'une certaine table du modèle),
en passant les paramètres when et obj.
- Soit immédiatement en ne passant que le paramètre bind.
Ces deux cas d'usage sont mutuellement exclusifs.
@param statement: Commande SQL à exécuter touchant au
schéma de la base (et donc, utilisant le DDL).
@type statement: C{str}
@param when: Nom de l'événement à attendre pour exécuter
le DDL, par exemple "before-create".
@type when: C{str}
@param obj: Objet support du DDL à exécuter (généralement
une Table).
@param bind: Connexion à la base de données (Session ou Metadata),
dans le cas où le DDL doit être exécuté immédiatement.
@param dialect: Nom d'un dialecte SQL supporté par SQLAlchemy.
Le DDL ne sera exécuté que si le dialecte utilisé par la
base de données correspond à donné ici.
@type dialect: C{str}
@param context: Contexte qui sera passé au DDL (interpolé)
au moment de son exécution.
@type context: C{dict}
"""
# 2 cas d'utilisation exclusifs :
# - soit lorsqu'un événement se produit
# - soit en exécution immédiate
assert (bind is None or when is not None) or \
(bind is not None or when is None)
if context is None:
context = {}
# SQLAlchemy 0.5/0.6.
if SaEvent is None:
# Les dialectes "postgres" et "postgresql" sont définis
# comme des aliases l'un par rapport à l'autre (sha:7d7d6c2).
# On doit supporter le passage de l'un ou l'autre des noms.
if dialect in ('postgres', 'postresql'):
dialect = ('postgres', 'postgresql')
if isinstance(dialect, (list, tuple)):
# @HACK: Dans SQLAlchemy 0.5.x, seulement 3 arguments
# positionnels sont passés : event, target & bind.
# Dans SQLAlchemy 0.6.x, il y en a 4 : self, event, target & bind.
# On ne s'intéresse qu'à bind (dernier argument à chaque fois).
condition = lambda *args, **kwargs: args[-1].engine.name in dialect
else:
condition = dialect
ddl = SaDDL(statement, on=condition, context=context)
if when is None:
ddl.execute(bind)
else:
# Les mots dans le nom de l'événement sont séparés par
# des '-' dans cette version de SQLAlchemy.
ddl.execute_at(when.replace('_', '-'), obj)
# SQLAlchemy 0.7+
else:
ddl = SaDDL(statement, context=context)
if when is None:
ddl.execute(bind)
else:
# Les mots dans le nom de l'événement sont séparés par
# des '_' dans cette version de SQLAlchemy.
SaEvent.listen(obj, when.replace('-', '_'),
ddl.execute_if(dialect=dialect))
def __init__(self,
db='portalwatch',
host="localhost",
port=5432,
password=None,
user='******',
debug=False):
# Define our connection string
self.log = log.new()
conn_string = "postgresql://"
if user:
conn_string += user
if password:
conn_string += ":" + password
if host:
conn_string += "@" + host
if port:
conn_string += ":" + str(port)
conn_string += "/" + db
log.info("Connecting DB")
self.engine = create_engine(conn_string,
pool_size=20,
client_encoding='utf8',
echo=debug)
add_engine_pidguard(self.engine)
#add_query_time_logging(self.engine)
# register_after_fork(self.engine, self.engine.dispose)
log.info("Connected DB")
# self.engine.connect()
self.session_factory = sessionmaker(
bind=self.engine) # , expire_on_commit=False
# self.session = self.Session()
self.dataset_insert_function = DDL("""
CREATE OR REPLACE FUNCTION dataset_insert_function()
RETURNS TRIGGER AS $$
DECLARE
_snapshot smallint;
_table_name text;
BEGIN
_snapshot := NEW.snapshot;
_table_name := '""" + tab_datasets + """_' || _snapshot;
PERFORM 1 FROM pg_tables WHERE tablename = _table_name;
IF NOT FOUND THEN
EXECUTE
'CREATE TABLE '
|| quote_ident(_table_name)
|| ' (CHECK ("snapshot" = '
|| _snapshot::smallint
|| ')) INHERITS (""" + tab_datasets + """)';
-- Indexes are defined per child, so we assign a default index that uses the partition columns
EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_org') || ' ON '||quote_ident(_table_name) || ' (organisation)';
EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_sn') || ' ON '||quote_ident(_table_name) || ' (snapshot)';
EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_pid') || ' ON '||quote_ident(_table_name) || ' (portalid)';
EXECUTE 'ALTER TABLE ' || quote_ident(_table_name)|| ' ADD CONSTRAINT ' || quote_ident(_table_name||'_pkey') || ' PRIMARY KEY (id, snapshot, portalid)';
EXECUTE 'ALTER TABLE ' || quote_ident(_table_name)|| ' ADD CONSTRAINT ' || quote_ident(_table_name||'_md5_fkey') || ' FOREIGN KEY (md5) REFERENCES datasetsdata (md5) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION';
EXECUTE 'ALTER TABLE ' || quote_ident(_table_name)|| ' ADD CONSTRAINT ' || quote_ident(_table_name||'_portalid_fkey') || ' FOREIGN KEY (portalid, snapshot) REFERENCES portalsnapshot (portalid, snapshot) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION';
END IF;
EXECUTE
'INSERT INTO '
|| quote_ident(_table_name)
|| ' VALUES ($1.*)'
USING NEW;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
""")
self.dataset_insert_trigger = DDL("""
CREATE TRIGGER dataset_insert_trigger
BEFORE INSERT ON """ + tab_datasets + """
FOR EACH ROW EXECUTE PROCEDURE dataset_insert_function();
""")
self.resourcesinfo_insert_function = DDL("""
CREATE OR REPLACE FUNCTION resourcesinfo_insert_function()
RETURNS TRIGGER AS $$
DECLARE
_snapshot smallint;
_table_name text;
BEGIN
_snapshot := NEW.snapshot;
_table_name := '""" + tab_resourcesinfo + """_' || _snapshot;
PERFORM 1 FROM pg_tables WHERE tablename = _table_name;
IF NOT FOUND THEN
EXECUTE
'CREATE TABLE '
|| quote_ident(_table_name)
|| ' (CHECK ("snapshot" = '
|| _snapshot::smallint
|| ')) INHERITS (""" + tab_resourcesinfo + """)';
-- Indexes are defined per child, so we assign a default index that uses the partition columns
EXECUTE 'ALTER TABLE ' || quote_ident(_table_name)|| ' ADD CONSTRAINT ' || quote_ident(_table_name||'_pkey') || ' PRIMARY KEY (uri, snapshot)';
EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_status') || ' ON '||quote_ident(_table_name) || ' (status)';
EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_uri') || ' ON '||quote_ident(_table_name) || ' (uri)';
END IF;
EXECUTE
'INSERT INTO '
|| quote_ident(_table_name)
|| ' VALUES ($1.*)'
USING NEW;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
""")
self.resourcesinfo_insert_trigger = DDL("""
CREATE TRIGGER resourcesinfo_insert_trigger
BEFORE INSERT ON """ + tab_resourcesinfo + """
FOR EACH ROW EXECUTE PROCEDURE resourcesinfo_insert_function();
""")
self.resourcescrawllog_insert_function = DDL("""
CREATE OR REPLACE FUNCTION resourcescrawllog_insert_function()
RETURNS TRIGGER AS $$
DECLARE
_snapshot smallint;
_table_name text;
BEGIN
_snapshot := NEW.snapshot;
_table_name := '""" + tab_resourcescrawllog +
"""_' || _snapshot;
PERFORM 1 FROM pg_tables WHERE tablename = _table_name;
IF NOT FOUND THEN
EXECUTE
'CREATE TABLE '
|| quote_ident(_table_name)
|| ' (CHECK ("snapshot" = '
|| _snapshot::smallint
|| ')) INHERITS (""" + tab_resourcescrawllog + """)';
-- Indexes are defined per child, so we assign a default index that uses the partition columns
EXECUTE 'ALTER TABLE ' || quote_ident(_table_name)|| ' ADD CONSTRAINT ' || quote_ident(_table_name||'_pkey') || ' PRIMARY KEY (uri, snapshot, timestamp)';
EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_status') || ' ON '||quote_ident(_table_name) || ' (status)';
EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_uri') || ' ON '||quote_ident(_table_name) || ' (uri)';
EXECUTE 'CREATE INDEX ' || quote_ident(_table_name||'_domain') || ' ON '||quote_ident(_table_name) || ' (domain)';
END IF;
EXECUTE
'INSERT INTO '
|| quote_ident(_table_name)
|| ' VALUES ($1.*)'
USING NEW;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
""")
self.resourcescrawllog_insert_trigger = DDL("""
CREATE TRIGGER resourcescrawllog_insert_trigger
BEFORE INSERT ON """ + tab_resourcescrawllog + """
FOR EACH ROW EXECUTE PROCEDURE resourcescrawllog_insert_function();
""")
relative = Decimal(0)
# The modifier that's changed isn't reflected yet in the database, so we
# apply it here.
if isinstance(value, Request):
# A modifier being added to the Request
if modifier.voided:
# The modifier being added is already void
return
direction = Decimal(1)
else:
# A modifier already on a request is being voided
direction = Decimal(-1)
if isinstance(modifier, AbsoluteModifier):
absolute += direction * modifier.value
elif isinstance(modifier, RelativeModifier):
relative += direction * modifier.value
payout = (srp_request.base_payout + absolute) * (Decimal(1) + relative)
srp_request.payout = PrettyDecimal(payout)
# The next few lines are responsible for adding a full text search index on the
# Request.details column for MySQL.
_create_fts = DDL("CREATE FULLTEXT INDEX ix_%(table)s_details_fulltext " "ON %(table)s (details);")
_drop_fts = DDL("DROP INDEX ix_%(table)s_details_fulltext ON %(table)s")
event.listen(Request.__table__, "after_create", _create_fts.execute_if(dialect="mysql"))
event.listen(Request.__table__, "before_drop", _drop_fts.execute_if(dialect="mysql"))
