def relatValorDia(self):
try:
# Abrindo Sessao
conecta = Conexao()
sessao = conecta.Session()
# Query
row = (sessao.query(
func.COALESCE(func.SUM(Venda.valor_recebido),
0).label('vendido'),
func.COUNT(distinct(
Venda.id_cliente)).label('cliente')).filter(
Venda.data_emissao.between(self.dataEmissao,
self.dataFim)))
row.all()
# salvando resultado
for query in row:
self.valorRecebido = str(query.vendido).replace('.', ',')
self.idCliente = query.cliente
# Fechando a COnexao
sessao.close()
except IntegrityError as err:
print(err)
def get_archive_month(cls, session):
"""获取所有有文章发布的月份和这个月份发布的文章数量，用于显示归档(archive)信息
:return: 返回二维元组(month, article_count)的列表
"""
final_result = []
# 获取所有发布过文章的月份
month_baked_sql = sql_bakery(lambda session: session.query(
func.DATE_FORMAT(Post.publish_time, "%Y%m").distinct().label(
"publish_month")))
month_baked_sql += lambda q: q.filter(Post.publish_time < bindparam(
'now')).order_by(text("publish_month desc"))
# 获取特定月份发布的文章数量
article_count_query = sql_bakery(
lambda session: session.query(func.COUNT(Post.id)))
article_count_query += lambda q: q.filter(
func.DATE_FORMAT(Post.publish_time, "%Y%m") == bindparam('month'))
month_results = month_baked_sql(session).\
params(now=datetime.now()).all()
for result in month_results:
article_count = article_count_query(session).\
params(month=result.publish_month).scalar()
final_result.append((datetime.strptime(result.publish_month,
"%Y%m"), article_count))
return final_result
def d_status():
record = donation.query.add_columns(
donation.status,
func.COUNT(donation.id).label('count')).group_by(
donation.status).all()
return record
def top_contributors(self):
return self.session.query(
Signature.signer_key.label('key'),
Signature.signer_name.label('name'),
Signature.signer_email.label('email'),
func.COUNT(Signature.signer_key).label('num_sigs')
).filter(Signature.signer_key != self.ca_key).\
group_by(Signature.signer_key).order_by(desc('num_sigs')).limit(10).all()
def thrusts():
record = event_information.query.add_columns(
event_information.thrust,
func.COUNT(event_information.thrust).label('count')).group_by(
event_information.thrust).all()
return record
def member_count():
record = community_member.query.add_columns(
community_member.community_id,
func.COUNT(community_member.member_id).label('count')).group_by(
community_member.community_id).all()
return record
def status():
record = event_information.query.add_columns(
event_information.status,
func.COUNT(event_information.id).label('count')).group_by(
event_information.status).all()
return record
def ratings(value):
record = event_participation.query.add_columns(
event_participation.rating,
func.COUNT(event_participation.rating).label('count')).group_by(
event_participation.rating).filter(
event_participation.event_id == value).all()
return record
def thrusts():
record = user_information.query.add_columns(
user_information.thrust,
func.COUNT(user_information.thrust).label('count')).group_by(
user_information.thrust).filter(
user_information.thrust != 0).all()
return record
def user_type():
record = user_account.query.add_columns(
user_account.type,
func.COUNT(user_account.id).label('count')).group_by(
user_account.type).filter(
and_(user_account.type != 1,
user_account.type != 5)).all()
return record
def report_open_close_passbook_month(passbook_type, from_month, to_month):
from_month = from_month + ' 00:00:00'
to_month = to_month + ' 00:00:00'
result = db.session.query(
TransactionSlip.transaction_date.cast(Date).label('transaction_date'),
func.COUNT(func.IF(TransactionSlip.transaction_type == TransactionType.OPEN_PASSBOOK, 1, None))
.label('passbook_open'),
func.COUNT(func.IF(and_(TransactionSlip.transaction_type == TransactionType.WITHDRAW,
Passbook.balance_amount == 0), 1, None))
.label('passbook_close'), ) \
.join(Passbook,
Passbook.id == TransactionSlip.passbook_id) \
.join(PassbookTypes,
PassbookTypes.id == Passbook.passbook_type_id) \
.filter(PassbookTypes.id == passbook_type) \
.filter(TransactionSlip.transaction_date.between(from_month, to_month)) \
.group_by(TransactionSlip.transaction_date.cast(Date)) \
.all()
return result
def get_specific_month_hold_count(hold_date):
"""
获取某天有持仓用户
:return: list
"""
# db = database.connection('portfolio_sta')
# metadata = MetaData(bind=db)
t = Table('ds_share', metadata, autoload=True)
# Session = sessionmaker(bind=db)
# session = Session()
rst = session.query(func.COUNT(func.DISTINCT(t.c.ds_uid))).filter( \
t.c.ds_date == hold_date,
t.c.ds_amount > 0)
return rst.all()
def get_specific_month_num_naive(s_date, e_date, t_type):
"""
获取某个时间段内ds_trade_type=t_type的且uid在uids内的用户数
:param s_date: string, 开始日期
:param e_date: string, 结束日期
:param t_type: array, 交易类型
:return: int
"""
# db = database.connection('portfolio_sta')
# metadata = MetaData(bind=db)
t = Table('ds_order_pdate', metadata, autoload=True)
# Session = sessionmaker(bind=db)
# session = Session()
rst = session.query(func.COUNT(func.DISTINCT(t.c.ds_uid))).filter( \
t.c.ds_placed_date >= s_date, \
t.c.ds_placed_date <= e_date, \
t.c.ds_trade_type.in_(t_type))
return rst.all()
def top_contributors_by_month(self):
grouped_sigs = self.session.query(
Signature.signer_key.label('key'),
Signature.signer_name.label('name'),
Signature.signer_email.label('email'),
func.strftime('%Y-%m', Signature.sign_date).label('sign_month'),
func.COUNT(Signature.id).label('num_sigs')).join(Key).filter(
Signature.signer_key != self.ca_key).group_by(
'key', 'sign_month').subquery()
return self.session.query(
grouped_sigs.c.key,
grouped_sigs.c.name,
grouped_sigs.c.email,
grouped_sigs.c.sign_month,
grouped_sigs.c.num_sigs
).group_by(grouped_sigs.c.sign_month).having(grouped_sigs.c.num_sigs==func.MAX(grouped_sigs.c.num_sigs)).\
order_by(desc(grouped_sigs.c.sign_month)).limit(24).all()
def signs_per_month_plot(self):
signs_per_month_data = self.domain_query(
func.strftime('%Y-%m', Signature.sign_date).label('sign_month'),
func.COUNT(Signature.id).label('num_sigs')).join(Key).filter(
Signature.sign_date > date.today() -
relativedelta(years=2)).group_by('sign_month').order_by(
asc('sign_month')).all()
data = dict(signs=[sig.num_sigs for sig in signs_per_month_data],
months=[sig.sign_month for sig in signs_per_month_data])
b = Bar(data,
values='signs',
label='months',
plot_width=800,
legend=False,
color='#0275d8')
return components(b)
def queries(table):
"""Given dictionaries of tables and view-queries, return a dictionary
of all the rest of the queries I need.
"""
def update_where(updcols, wherecols):
"""Return an ``UPDATE`` statement that updates the columns ``updcols``
when the ``wherecols`` match. Every column has a bound parameter of
the same name.
updcols are strings, wherecols are column objects
"""
vmap = OrderedDict()
for col in updcols:
vmap[col] = bindparam(col)
wheres = [c == bindparam(c.name) for c in wherecols]
tab = wherecols[0].table
return tab.update().values(**vmap).where(and_(*wheres))
r = LiSE.allegedb.alchemy.queries_for_table_dict(table)
rulebooks = table['rulebooks']
r['rulebooks_update'] = update_where(['rules'], [
rulebooks.c.rulebook, rulebooks.c.branch, rulebooks.c.turn,
rulebooks.c.tick
])
for t in table.values():
key = list(t.primary_key)
if 'branch' in t.columns and 'turn' in t.columns and 'tick' in t.columns:
branch = t.columns['branch']
turn = t.columns['turn']
tick = t.columns['tick']
if branch in key and turn in key and tick in key:
key = [branch, turn, tick]
r[t.name + '_dump'] = select(list(t.c.values())).order_by(*key)
r[t.name + '_insert'] = t.insert().values(
tuple(bindparam(cname) for cname in t.c.keys()))
r[t.name + '_count'] = select([func.COUNT('*')]).select_from(t)
r['del_char_things'] = table['things'].delete().where(
table['things'].c.character == bindparam('character'))
r['del_char_avatars'] = table['avatars'].delete().where(
table['avatars'].c.character_graph == bindparam('character'))
things = table['things']
r['del_things_after'] = things.delete().where(
and_(
things.c.character == bindparam('character'),
things.c.thing == bindparam('thing'),
things.c.branch == bindparam('branch'),
or_(
things.c.turn > bindparam('turn'),
and_(things.c.turn == bindparam('turn'),
things.c.tick >= bindparam('tick')))))
avatars = table['avatars']
r['del_avatars_after'] = avatars.delete().where(
and_(
avatars.c.character_graph == bindparam('character'),
avatars.c.avatar_graph == bindparam('graph'),
avatars.c.avatar_node == bindparam('avatar'),
avatars.c.branch == bindparam('branch'),
or_(
avatars.c.turn > bindparam('turn'),
and_(avatars.c.turn == bindparam('turn'),
avatars.c.tick >= bindparam('tick')))))
for handledtab in ('character_rules_handled', 'avatar_rules_handled',
'character_thing_rules_handled',
'character_place_rules_handled',
'character_portal_rules_handled', 'node_rules_handled',
'portal_rules_handled'):
ht = table[handledtab]
r['del_{}_turn'.format(handledtab)] = ht.delete().where(
and_(ht.c.branch == bindparam('branch'),
ht.c.turn == bindparam('turn')))
branches = table['branches']
r['branch_children'] = select(
[branches.c.branch]).where(branches.c.parent == bindparam('branch'))
tc = table['turns_completed']
r['turns_completed_update'] = update_where(['turn'], [tc.c.branch])
return r
def count_table(session, table):
c = session.query(func.COUNT('*').label('rows')).select_from(table)
return c.scalar()
def compute_features_from_osm(config):
osm_tables = config['OSM']
bounding_box = WKTElement(config['BOUNDING_BOX'], srid=4326)
grid_obj = config['GRID_OBJ']
geo_feature_obj = config['GEO_FEATURE_OBJ']
try:
for feature_name, osm_table in osm_tables.items():
geo_feature_type = osm_table.wkb_geometry.type.geometry_type
cropped_osm = crop_osm(
osm_table,
bounding_box) # crop the OSM data with a bounding box
sub_query = session.query(grid_obj.gid, cropped_osm.c.fclass,
func.ST_GeogFromWKB(
func.ST_Intersection(grid_obj.geom, cropped_osm.c.wkb_geometry))
.label('intersection')) \
.filter(func.ST_Intersects(grid_obj.geom, cropped_osm.c.wkb_geometry)).subquery()
results = []
if geo_feature_type == 'MULTIPOLYGON':
results = session.query(sub_query.c.gid.label('gid'),
sub_query.c.fclass.label('feature_type'),
literal(feature_name).label('geo_feature'),
func.SUM(func.ST_AREA(sub_query.c.intersection)).label('value'),
literal('area').label('measurement')) \
.group_by(sub_query.c.gid, sub_query.c.fclass).all()
elif geo_feature_type == 'MULTILINESTRING':
results = session.query(sub_query.c.gid.label('gid'),
sub_query.c.fclass.label('feature_type'),
literal(feature_name).label('geo_feature'),
func.SUM(func.ST_LENGTH(sub_query.c.intersection)).label('value'),
literal('length').label('measurement')) \
.group_by(sub_query.c.gid, sub_query.c.fclass).all()
elif geo_feature_type == 'POINT':
results = session.query(sub_query.c.gid.label('gid'),
sub_query.c.fclass.label('feature_type'),
literal(feature_name).label('geo_feature'),
func.COUNT(sub_query.c.intersection).label('value'),
literal('count').label('measurement')) \
.group_by(sub_query.c.gid, sub_query.c.fclass).all()
else:
pass
obj_results = []
for res in results:
obj_results.append(
geo_feature_obj(gid=res[0],
feature_type=res[1],
geo_feature=res[2],
value=res[3],
measurement=res[4]))
# session.add_all(obj_results)
# session.commit()
print('{} has finished'.format(feature_name))
return
except Exception as e:
print(e)
exit(-1)
def queries(table):
"""Given dictionaries of tables and view-queries, return a dictionary
of all the rest of the queries I need.
"""
def update_where(updcols, wherecols):
"""Return an ``UPDATE`` statement that updates the columns ``updcols``
when the ``wherecols`` match. Every column has a bound parameter of
the same name.
updcols are strings, wherecols are column objects
"""
vmap = OrderedDict()
for col in updcols:
vmap[col] = bindparam(col)
wheres = [c == bindparam(c.name) for c in wherecols]
tab = wherecols[0].table
return tab.update().values(**vmap).where(and_(*wheres))
r = allegedb.alchemy.queries_for_table_dict(table)
for t in table.values():
r[t.name + '_dump'] = select(list(
t.c.values())).order_by(*t.primary_key)
r[t.name + '_insert'] = t.insert().values(
tuple(bindparam(cname) for cname in t.c.keys()))
r[t.name + '_count'] = select([func.COUNT('*')]).select_from(t)
r['del_char_things'] = table['things'].delete().where(
table['things'].c.character == bindparam('character'))
r['del_char_avatars'] = table['avatars'].delete().where(
table['avatars'].c.character_graph == bindparam('character'))
things = table['things']
r['del_things_after'] = things.delete().where(
and_(
things.c.character == bindparam('character'),
things.c.thing == bindparam('thing'),
things.c.branch == bindparam('branch'),
or_(
things.c.turn > bindparam('turn'),
and_(things.c.turn == bindparam('turn'),
things.c.tick >= bindparam('tick')))))
avatars = table['avatars']
r['del_avatars_after'] = avatars.delete().where(
and_(
avatars.c.character_graph == bindparam('character'),
avatars.c.avatar_graph == bindparam('graph'),
avatars.c.avatar_node == bindparam('avatar'),
avatars.c.branch == bindparam('branch'),
or_(
avatars.c.turn > bindparam('turn'),
and_(avatars.c.turn == bindparam('turn'),
avatars.c.tick >= bindparam('tick')))))
branches = table['branches']
r['branch_children'] = select(
[branches.c.branch]).where(branches.c.parent == bindparam('branch'))
tc = table['turns_completed']
r['turns_completed_update'] = update_where(['turn'], [tc.c.branch])
return r
def total_keys_and_sigs(self):
sigs = self.domain_query(
func.COUNT(Signature.id).label('num_sigs'),
Signature.sign_date).join(Key).filter(
Signature.sign_date > date.today() -
relativedelta(years=2)).group_by(Signature.sign_date).order_by(
asc(Signature.sign_date)).all()
current_num_sigs = self.session.query(Signature).filter(
Signature.sign_date <= date.today() -
relativedelta(years=2)).count()
if self.ca_key is not None:
ca_sigs = self.domain_query(
func.COUNT(Signature.id).label('num_sigs'),
Signature.sign_date).join(Key).filter(
Signature.sign_date >
date.today() - relativedelta(years=2),
Signature.signer_key == self.ca_key).group_by(
Signature.sign_date).order_by(asc(
Signature.sign_date)).all()
current_num_ca_sigs = self.session.query(Signature).filter(
Signature.sign_date <= date.today() - relativedelta(years=2),
Signature.signer_key == self.ca_key).count()
else:
ca_sigs = []
current_num_ca_sigs = 0
keys = self.domain_query(
func.COUNT(Key.id).label('num_keys'), Key.created).filter(
Key.created > date.today() - relativedelta(years=2)).group_by(
Key.created).order_by(asc(Key.created)).all()
current_num_keys = self.session.query(Key).filter(
Key.created <= date.today() - relativedelta(years=2)).count()
sig_dates = [pandas.Timestamp(sig.sign_date) for sig in sigs]
ca_sig_dates = [pandas.Timestamp(sig.sign_date) for sig in ca_sigs]
key_dates = [pandas.Timestamp(key.created) for key in keys]
dates = list(set(sig_dates + key_dates + ca_sig_dates))
dates.sort()
date_num_sigs = []
date_num_ca_sigs = []
date_num_keys = []
for d in dates:
try:
i = sig_dates.index(d)
current_num_sigs = current_num_sigs + sigs[i].num_sigs
except:
pass
finally:
date_num_sigs.append(current_num_sigs)
try:
i = ca_sig_dates.index(d)
current_num_ca_sigs = current_num_ca_sigs + ca_sigs[i].num_sigs
except:
pass
finally:
date_num_ca_sigs.append(current_num_ca_sigs)
try:
i = key_dates.index(d)
current_num_keys = current_num_keys + keys[i].num_keys
except:
pass
finally:
date_num_keys.append(current_num_keys)
data = {
'Signatures': date_num_sigs,
'Keys': date_num_keys,
'Dates': pandas.Series(dates)
}
y_labels = ['Signatures', 'Keys']
if self.ca_key is not None:
data['CA Auto Signatures'] = date_num_ca_sigs
y_labels.append('CA Auto Signatures')
t = TimeSeries(data, x='Dates', y=y_labels, plot_width=800)
return components(t)
def queries_for_table_dict(table):
r = {
'global_get':
select([table['global'].c.value
]).where(table['global'].c.key == bindparam('key')),
'global_update':
table['global'].update().values(value=bindparam('value')).where(
table['global'].c.key == bindparam('key')),
'graph_type':
select([table['graphs'].c.type
]).where(table['graphs'].c.graph == bindparam('graph')),
'del_edge_val_graph':
table['edge_val'].delete().where(
table['edge_val'].c.graph == bindparam('graph')),
'del_edge_val_after':
table['edge_val'].delete().where(
and_(
table['edge_val'].c.graph == bindparam('graph'),
table['edge_val'].c.orig == bindparam('orig'),
table['edge_val'].c.dest == bindparam('dest'),
table['edge_val'].c.idx == bindparam('idx'),
table['edge_val'].c.key == bindparam('key'),
table['edge_val'].c.branch == bindparam('branch'),
or_(
table['edge_val'].c.turn > bindparam('turn'),
and_(table['edge_val'].c.turn == bindparam('turn'),
table['edge_val'].c.tick >= bindparam('tick'))))),
'del_edges_graph':
table['edges'].delete().where(
table['edges'].c.graph == bindparam('graph')),
'del_edges_after':
table['edges'].delete().where(
and_(
table['edges'].c.graph == bindparam('graph'),
table['edges'].c.orig == bindparam('orig'),
table['edges'].c.dest == bindparam('dest'),
table['edges'].c.idx == bindparam('idx'),
table['edges'].c.branch == bindparam('branch'),
or_(
table['edges'].c.turn > bindparam('turn'),
and_(table['edges'].c.turn == bindparam('turn'),
table['edges'].c.tick >= bindparam('tick'))))),
'del_nodes_graph':
table['nodes'].delete().where(
table['nodes'].c.graph == bindparam('graph')),
'del_nodes_after':
table['nodes'].delete().where(
and_(
table['nodes'].c.graph == bindparam('graph'),
table['nodes'].c.node == bindparam('node'),
table['nodes'].c.branch == bindparam('branch'),
or_(
table['nodes'].c.turn > bindparam('turn'),
and_(table['nodes'].c.turn == bindparam('turn'),
table['nodes'].c.tick >= bindparam('tick'))))),
'del_node_val_graph':
table['node_val'].delete().where(
table['node_val'].c.graph == bindparam('graph')),
'del_node_val_after':
table['node_val'].delete().where(
and_(
table['node_val'].c.graph == bindparam('graph'),
table['node_val'].c.node == bindparam('node'),
table['node_val'].c.key == bindparam('key'),
table['node_val'].c.branch == bindparam('branch'),
or_(
table['node_val'].c.turn > bindparam('turn'),
and_(table['node_val'].c.turn == bindparam('turn'),
table['node_val'].c.tick >= bindparam('tick'))))),
'del_graph':
table['graphs'].delete().where(
table['graphs'].c.graph == bindparam('graph')),
'del_graph_val_after':
table['graph_val'].delete().where(
and_(
table['graph_val'].c.graph == bindparam('graph'),
table['graph_val'].c.key == bindparam('key'),
table['graph_val'].c.branch == bindparam('branch'),
or_(
table['graph_val'].c.turn > bindparam('turn'),
and_(table['graph_val'].c.turn == bindparam('turn'),
table['graph_val'].c.tick >= bindparam('tick'))))),
'global_delete':
table['global'].delete().where(
table['global'].c.key == bindparam('key')),
'graphs_types':
select([table['graphs'].c.graph, table['graphs'].c.type]),
'graphs_named':
select([func.COUNT()]).select_from(table['graphs']).where(
table['graphs'].c.graph == bindparam('graph')),
'update_branches':
table['branches'].update().values(
parent=bindparam('parent'),
parent_turn=bindparam('parent_turn'),
parent_tick=bindparam('parent_tick'),
end_turn=bindparam('end_turn'),
end_tick=bindparam('end_tick')).where(
table['branches'].c.branch == bindparam('branch')),
'update_turns':
table['turns'].update().values(
end_tick=bindparam('end_tick'),
plan_end_tick=bindparam('plan_end_tick')).where(
and_(table['turns'].c.branch == bindparam('branch'),
table['turns'].c.turn == bindparam('turn')))
}
for t in table.values():
key = list(t.primary_key)
if 'branch' in t.columns and 'turn' in t.columns and 'tick' in t.columns:
branch = t.columns['branch']
turn = t.columns['turn']
tick = t.columns['tick']
if branch in key and turn in key and tick in key:
key = [branch, turn, tick]
r[t.name + '_del_time'] = t.delete().where(
and_(t.c.branch == bindparam('branch'),
t.c.turn == bindparam('turn'),
t.c.tick == bindparam('tick')))
r[t.name + '_dump'] = select(list(t.c.values())).order_by(*key)
r[t.name + '_insert'] = t.insert().values(
tuple(bindparam(cname) for cname in t.c.keys()))
r[t.name + '_count'] = select([func.COUNT()]).select_from(t)
r[t.name + '_del'] = t.delete().where(
and_(*[c == bindparam(c.name) for c in t.primary_key]))
return r
def queries_for_table_dict(table):
def hirev_nodes_join(wheres):
hirev = select(
[
table['nodes'].c.graph,
table['nodes'].c.node,
table['nodes'].c.branch,
func.MAX(table['nodes'].c.rev).label('rev')
]
).where(and_(*wheres)).group_by(
table['nodes'].c.graph,
table['nodes'].c.node,
table['nodes'].c.branch
).alias('hirev')
return table['nodes'].join(
hirev,
and_(
table['nodes'].c.graph == hirev.c.graph,
table['nodes'].c.node == hirev.c.node,
table['nodes'].c.branch == hirev.c.branch,
table['nodes'].c.rev == hirev.c.rev
)
)
def hirev_graph_val_join(wheres):
hirev = select(
[
table['graph_val'].c.graph,
table['graph_val'].c.key,
table['graph_val'].c.branch,
func.MAX(table['graph_val'].c.rev).label('rev')
]
).where(and_(*wheres)).group_by(
table['graph_val'].c.graph,
table['graph_val'].c.key,
table['graph_val'].c.branch
).alias('hirev')
return table['graph_val'].join(
hirev,
and_(
table['graph_val'].c.graph == hirev.c.graph,
table['graph_val'].c.key == hirev.c.key,
table['graph_val'].c.branch == hirev.c.branch,
table['graph_val'].c.rev == hirev.c.rev
)
)
def node_val_hirev_join(wheres):
hirev = select(
[
table['node_val'].c.graph,
table['node_val'].c.node,
table['node_val'].c.branch,
table['node_val'].c.key,
func.MAX(table['node_val'].c.rev).label('rev')
]
).where(and_(*wheres)).group_by(
table['node_val'].c.graph,
table['node_val'].c.node,
table['node_val'].c.branch,
table['node_val'].c.key
).alias('hirev')
return table['node_val'].join(
hirev,
and_(
table['node_val'].c.graph == hirev.c.graph,
table['node_val'].c.node == hirev.c.node,
table['node_val'].c.key == hirev.c.key,
table['node_val'].c.branch == hirev.c.branch,
table['node_val'].c.rev == hirev.c.rev
)
)
def edges_recent_join(wheres=None):
hirev = select(
[
table['edges'].c.graph,
table['edges'].c.nodeA,
table['edges'].c.nodeB,
table['edges'].c.idx,
table['edges'].c.branch,
func.MAX(table['edges'].c.rev).label('rev')
]
)
if wheres:
hirev = hirev.where(and_(*wheres))
hirev = hirev.group_by(
table['edges'].c.graph,
table['edges'].c.nodeA,
table['edges'].c.nodeB,
table['edges'].c.idx,
table['edges'].c.branch
).alias('hirev')
return table['edges'].join(
hirev,
and_(
table['edges'].c.graph == hirev.c.graph,
table['edges'].c.nodeA == hirev.c.nodeA,
table['edges'].c.nodeB == hirev.c.nodeB,
table['edges'].c.idx == hirev.c.idx,
table['edges'].c.branch == hirev.c.branch,
table['edges'].c.rev == hirev.c.rev
)
)
def edge_val_recent_join(wheres=None):
hirev = select(
[
table['edge_val'].c.graph,
table['edge_val'].c.nodeA,
table['edge_val'].c.nodeB,
table['edge_val'].c.idx,
table['edge_val'].c.key,
table['edge_val'].c.branch,
func.MAX(table['edge_val'].c.rev).label('rev')
]
)
if wheres:
hirev = hirev.where(
and_(*wheres)
)
hirev = hirev.group_by(
table['edge_val'].c.graph,
table['edge_val'].c.nodeA,
table['edge_val'].c.nodeB,
table['edge_val'].c.idx,
table['edge_val'].c.key,
table['edge_val'].c.branch
).alias('hirev')
return table['edge_val'].join(
hirev,
and_(
table['edge_val'].c.graph == hirev.c.graph,
table['edge_val'].c.nodeA == hirev.c.nodeA,
table['edge_val'].c.nodeB == hirev.c.nodeB,
table['edge_val'].c.idx == hirev.c.idx,
table['edge_val'].c.branch == hirev.c.branch,
table['edge_val'].c.rev == hirev.c.rev
)
)
return {
'ctbranch': select(
[func.COUNT(table['branches'].c.branch)]
).where(
table['branches'].c.branch == bindparam('branch')
),
'ctgraph': select(
[func.COUNT(table['graphs'].c.graph)]
).where(
table['graphs'].c.graph == bindparam('graph')
),
'allbranch': select(
[
table['branches'].c.branch,
table['branches'].c.parent,
table['branches'].c.parent_rev
]
).order_by(table['branches'].c.branch),
'global_get': select(
[table['global'].c.value]
).where(
table['global'].c.key == bindparam('key')
),
'edge_val_ins': table['edge_val'].insert().prefix_with('OR REPLACE').values(
graph=bindparam('graph'),
nodeA=bindparam('orig'),
nodeB=bindparam('dest'),
idx=bindparam('idx'),
key=bindparam('key'),
branch=bindparam('branch'),
rev=bindparam('rev'),
value=bindparam('value')
),
'edge_val_upd': table['edge_val'].update().values(
value=bindparam('value')
).where(
and_(
table['edge_val'].c.graph == bindparam('graph'),
table['edge_val'].c.nodeA == bindparam('orig'),
table['edge_val'].c.nodeB == bindparam('dest'),
table['edge_val'].c.idx == bindparam('idx'),
table['edge_val'].c.key == bindparam('key'),
table['edge_val'].c.branch == bindparam('branch'),
table['edge_val'].c.rev == bindparam('rev')
)
),
'global_items': select(
[
table['global'].c.key,
table['global'].c.value
]
),
'ctglobal': select(
[func.COUNT(table['global'].c.key)]
),
'new_graph': table['graphs'].insert().values(
graph=bindparam('graph'),
type=bindparam('type')
),
'graph_type': select(
[table['graphs'].c.type]
).where(
table['graphs'].c.graph == bindparam('graph')
),
'new_branch': table['branches'].insert().values(
branch=bindparam('branch'),
parent=bindparam('parent'),
parent_rev=bindparam('parent_rev')
),
'del_edge_val_graph': table['edge_val'].delete().where(
table['edge_val'].c.graph == bindparam('graph')
),
'del_edge_graph': table['edges'].delete().where(
table['edges'].c.graph == bindparam('graph')
),
'del_node_val_graph': table['node_val'].delete().where(
table['node_val'].c.graph == bindparam('graph')
),
'del_node_graph': table['nodes'].delete().where(
table['nodes'].c.graph == bindparam('graph')
),
'del_graph': table['graphs'].delete().where(
table['graphs'].c.graph == bindparam('graph')
),
'parrev': select(
[table['branches'].c.parent_rev]
).where(
table['branches'].c.branch == bindparam('branch')
),
'parparrev': select(
[table['branches'].c.parent, table['branches'].c.parent_rev]
).where(
table['branches'].c.branch == bindparam('branch')
),
'global_ins': table['global'].insert().values(
key=bindparam('key'),
value=bindparam('value')
),
'global_upd': table['global'].update().values(
value=bindparam('value')
).where(
table['global'].c.key == bindparam('key')
),
'global_del': table['global'].delete().where(
table['global'].c.key == bindparam('key')
),
'nodes_extant': select(
[table['nodes'].c.node]
).select_from(
hirev_nodes_join(
[
table['nodes'].c.graph == bindparam('graph'),
table['nodes'].c.branch == bindparam('branch'),
table['nodes'].c.rev <= bindparam('rev')
]
)
).where(
table['nodes'].c.extant
),
'node_exists': select(
[table['nodes'].c.extant]
).select_from(
hirev_nodes_join(
[
table['nodes'].c.graph == bindparam('graph'),
table['nodes'].c.node == bindparam('node'),
table['nodes'].c.branch == bindparam('branch'),
table['nodes'].c.rev <= bindparam('rev')
]
)
),
'exist_node_ins': table['nodes'].insert().prefix_with('OR REPLACE').values(
graph=bindparam('graph'),
node=bindparam('node'),
branch=bindparam('branch'),
rev=bindparam('rev'),
extant=bindparam('extant')
),
'exist_node_upd': table['nodes'].update().values(
extant=bindparam('extant')
).where(
and_(
table['nodes'].c.graph == bindparam('graph'),
table['nodes'].c.node == bindparam('node'),
table['nodes'].c.branch == bindparam('branch'),
table['nodes'].c.rev == bindparam('rev')
)
),
'graphs_types': select([
table['graphs'].c.graph,
table['graphs'].c.type
]),
'nodes_dump': select([
table['nodes'].c.graph,
table['nodes'].c.node,
table['nodes'].c.branch,
table['nodes'].c.rev,
table['nodes'].c.extant
]).order_by(
table['nodes'].c.graph,
table['nodes'].c.branch,
table['nodes'].c.rev,
table['nodes'].c.node
),
'graph_val_items': select(
[
table['graph_val'].c.key,
table['graph_val'].c.value
]
).select_from(
hirev_graph_val_join(
[
table['graph_val'].c.graph == bindparam('graph'),
table['graph_val'].c.branch == bindparam('branch'),
table['graph_val'].c.rev <= bindparam('rev')
]
)
),
'graph_val_dump': select([
table['graph_val'].c.graph,
table['graph_val'].c.key,
table['graph_val'].c.branch,
table['graph_val'].c.rev,
table['graph_val'].c.value
]).order_by(
table['graph_val'].c.graph,
table['graph_val'].c.branch,
table['graph_val'].c.rev,
table['graph_val'].c.key
),
'graph_val_get': select(
[
table['graph_val'].c.value
]
).select_from(
hirev_graph_val_join(
[
table['graph_val'].c.graph == bindparam('graph'),
table['graph_val'].c.key == bindparam('key'),
table['graph_val'].c.branch == bindparam('branch'),
table['graph_val'].c.rev <= bindparam('rev')
]
)
),
'graph_val_ins': table['graph_val'].insert().prefix_with('OR REPLACE').values(
graph=bindparam('graph'),
key=bindparam('key'),
branch=bindparam('branch'),
rev=bindparam('rev'),
value=bindparam('value')
),
'graph_val_upd': table['graph_val'].update().values(
value=bindparam('value')
).where(
and_(
table['graph_val'].c.graph == bindparam('graph'),
table['graph_val'].c.key == bindparam('key'),
table['graph_val'].c.branch == bindparam('branch'),
table['graph_val'].c.rev == bindparam('rev')
)
),
'node_val_items': select(
[
table['node_val'].c.key,
table['node_val'].c.value
]
).select_from(
node_val_hirev_join(
[
table['node_val'].c.graph == bindparam('graph'),
table['node_val'].c.node == bindparam('node'),
table['node_val'].c.branch == bindparam('branch'),
table['node_val'].c.rev <= bindparam('rev')
]
)
),
'node_val_dump': select([
table['node_val'].c.graph,
table['node_val'].c.node,
table['node_val'].c.key,
table['node_val'].c.branch,
table['node_val'].c.rev,
table['node_val'].c.value
]).order_by(
table['node_val'].c.graph,
table['node_val'].c.node,
table['node_val'].c.branch,
table['node_val'].c.rev,
table['node_val'].c.key
),
'node_val_get': select(
[
table['node_val'].c.value
]
).select_from(
node_val_hirev_join(
[
table['node_val'].c.graph == bindparam('graph'),
table['node_val'].c.node == bindparam('node'),
table['node_val'].c.key == bindparam('key'),
table['node_val'].c.branch == bindparam('branch'),
table['node_val'].c.rev <= bindparam('rev')
]
)
).where(
table['node_val'].c.value != null()
),
'node_val_ins': table['node_val'].insert().prefix_with('OR REPLACE').values(
graph=bindparam('graph'),
node=bindparam('node'),
key=bindparam('key'),
branch=bindparam('branch'),
rev=bindparam('rev'),
value=bindparam('value')
),
'edge_exists': select(
[table['edges'].c.extant]
).select_from(
edges_recent_join(
[
table['edges'].c.graph == bindparam('graph'),
table['edges'].c.nodeA == bindparam('nodeA'),
table['edges'].c.nodeB == bindparam('nodeB'),
table['edges'].c.idx == bindparam('idx'),
table['edges'].c.branch == bindparam('branch'),
table['edges'].c.rev <= bindparam('rev')
]
)
),
'edges_extant': select(
[
table['edges'].c.nodeA,
table['edges'].c.extant
]
).select_from(
edges_recent_join(
[
table['edges'].c.graph == bindparam('graph'),
table['edges'].c.branch == bindparam('branch'),
table['edges'].c.rev <= bindparam('rev')
]
)
),
'nodeAs': select(
[
table['edges'].c.nodeA,
table['edges'].c.extant
]
).select_from(
edges_recent_join(
[
table['edges'].c.graph == bindparam('graph'),
table['edges'].c.nodeB == bindparam('dest'),
table['edges'].c.branch == bindparam('branch'),
table['edges'].c.rev <= bindparam('rev')
]
)
),
'nodeBs': select(
[
table['edges'].c.nodeB,
table['edges'].c.extant
]
).select_from(
edges_recent_join(
[
table['edges'].c.graph == bindparam('graph'),
table['edges'].c.nodeA == bindparam('orig'),
table['edges'].c.branch == bindparam('branch'),
table['edges'].c.rev <= bindparam('rev')
]
)
),
'multi_edges': select(
[
table['edges'].c.idx,
table['edges'].c.extant
]
).select_from(
edges_recent_join(
[
table['edges'].c.graph == bindparam('graph'),
table['edges'].c.nodeA == bindparam('orig'),
table['edges'].c.nodeB == bindparam('dest'),
table['edges'].c.branch == bindparam('branch'),
table['edges'].c.rev <= bindparam('rev')
]
)
),
'edges_dump': select([
table['edges'].c.graph,
table['edges'].c.nodeA,
table['edges'].c.nodeB,
table['edges'].c.idx,
table['edges'].c.branch,
table['edges'].c.rev,
table['edges'].c.extant
]).order_by(
table['edges'].c.graph,
table['edges'].c.branch,
table['edges'].c.rev,
table['edges'].c.nodeA,
table['edges'].c.nodeB,
table['edges'].c.idx
),
'edge_exist_ins': table['edges'].insert().prefix_with('OR REPLACE').values(
graph=bindparam('graph'),
nodeA=bindparam('orig'),
nodeB=bindparam('dest'),
idx=bindparam('idx'),
branch=bindparam('branch'),
rev=bindparam('rev'),
extant=bindparam('extant')
),
'edge_exist_upd': table['edges'].update().values(
extant=bindparam('extant')
).where(
and_(
table['edges'].c.graph == bindparam('graph'),
table['edges'].c.nodeA == bindparam('orig'),
table['edges'].c.nodeB == bindparam('dest'),
table['edges'].c.idx == bindparam('idx'),
table['edges'].c.branch == bindparam('branch'),
table['edges'].c.rev == bindparam('rev')
)
),
'edge_val_dump': select([
table['edge_val'].c.graph,
table['edge_val'].c.nodeA,
table['edge_val'].c.nodeB,
table['edge_val'].c.idx,
table['edge_val'].c.key,
table['edge_val'].c.branch,
table['edge_val'].c.rev,
table['edge_val'].c.value
]).order_by(
table['edge_val'].c.graph,
table['edge_val'].c.nodeA,
table['edge_val'].c.nodeB,
table['edge_val'].c.idx,
table['edge_val'].c.branch,
table['edge_val'].c.rev,
table['edge_val'].c.key
),
'edge_val_items': select(
[
table['edge_val'].c.key,
table['edge_val'].c.value
]
).select_from(
edge_val_recent_join(
[
table['edge_val'].c.graph == bindparam('graph'),
table['edge_val'].c.nodeA == bindparam('orig'),
table['edge_val'].c.nodeB == bindparam('dest'),
table['edge_val'].c.idx == bindparam('idx'),
table['edge_val'].c.branch == bindparam('branch'),
table['edge_val'].c.rev <= bindparam('rev')
]
)
),
'edge_val_get': select(
[
table['edge_val'].c.value
]
).select_from(
edge_val_recent_join(
[
table['edge_val'].c.graph == bindparam('graph'),
table['edge_val'].c.nodeA == bindparam('orig'),
table['edge_val'].c.nodeB == bindparam('dest'),
table['edge_val'].c.idx == bindparam('idx'),
table['edge_val'].c.key == bindparam('key'),
table['edge_val'].c.branch == bindparam('branch'),
table['edge_val'].c.rev <= bindparam('rev')
]
)
)
}
