def _compute_ratios(stats_clause):
tc_session.expunge_all()
avg_from_rate = select([alias(
select([func.avg(RouterStats.circ_from_rate)],
stats_clause)
)]).as_scalar()
avg_to_rate = select([alias(
select([func.avg(RouterStats.circ_to_rate)],
stats_clause)
)]).as_scalar()
avg_bi_rate = select([alias(
select([func.avg(RouterStats.circ_bi_rate)],
stats_clause)
)]).as_scalar()
avg_ext = select([alias(
select([func.avg(RouterStats.avg_first_ext)],
stats_clause)
)]).as_scalar()
avg_sbw = select([alias(
select([func.avg(RouterStats.sbw)],
stats_clause)
)]).as_scalar()
RouterStats.table.update(stats_clause, values=
{RouterStats.table.c.circ_from_ratio:
(1-RouterStats.table.c.circ_from_rate)/(1-avg_from_rate),
RouterStats.table.c.circ_to_ratio:
(1-RouterStats.table.c.circ_to_rate)/(1-avg_to_rate),
RouterStats.table.c.circ_bi_ratio:
(1-RouterStats.table.c.circ_bi_rate)/(1-avg_bi_rate),
RouterStats.table.c.ext_ratio:
avg_ext/RouterStats.table.c.avg_first_ext,
RouterStats.table.c.sbw_ratio:
RouterStats.table.c.sbw/avg_sbw}).execute()
tc_session.commit()
def full_name( self ):
aliased_organisation = sql.alias( Organization.table )
aliased_person = sql.alias( Person.table )
return sql.functions.coalesce( sql.select( [sql.functions.coalesce(aliased_person.c.first_name,'') + ' ' + sql.functions.coalesce(aliased_person.c.last_name, '')],
whereclause = and_( aliased_person.c.party_id == self.id ),
).limit( 1 ).as_scalar(),
sql.select( [aliased_organisation.c.name],
whereclause = and_( aliased_organisation.c.party_id == self.id ),
).limit( 1 ).as_scalar() )
def query(self):
pq = qualstat_getstatdata(column("eval_type") == "f")
base = alias(pq)
query = (select([
func.array_agg(column("queryid")).label("queryids"),
"qualid",
cast(column("quals"), JSONB).label('quals'),
"occurences",
"execution_count",
func.array_agg(column("query")).label("queries"),
"avg_filter",
"filter_ratio"
]).select_from(
join(base, powa_databases,
onclause=(
powa_databases.c.oid == literal_column("dbid"))))
.where(powa_databases.c.datname == bindparam("database"))
.where(column("avg_filter") > 1000)
.where(column("filter_ratio") > 0.3)
.group_by(column("qualid"), column("execution_count"),
column("occurences"),
cast(column("quals"), JSONB),
column("avg_filter"), column("filter_ratio"))
.order_by(column("occurences").desc())
.limit(200))
return query
def full_name(self):
aliased_organisation = sql.alias(Organization.table)
aliased_person = sql.alias(Person.table)
return sql.functions.coalesce(
sql.select(
[
sql.functions.coalesce(aliased_person.c.first_name, '') +
' ' +
sql.functions.coalesce(aliased_person.c.last_name, '')
],
whereclause=and_(aliased_person.c.party_id == self.id),
).limit(1).as_scalar(),
sql.select(
[aliased_organisation.c.name],
whereclause=and_(aliased_organisation.c.party_id == self.id),
).limit(1).as_scalar())
def __init__(self, *tables, **kwargs):
self.tables = {}
self.aliases = kwargs.get('aliases', {})
for t in tables:
self.tables[t] = t
if not self.aliases.has_key(t):
self.aliases[t] = sql.alias(t)
if isinstance(t, sql.Join):
for t2 in t.columns:
self.tables[t2.table] = t2
self.aliases[t2.table] = self.aliases[t]
self.binary = None
def get_range_data(offset_, size_):
tbl_main = alias(tbl_def, 't')
join_condition = []
pk_names_desc = [name+" DESC" for name in pk_names]
sub_q = select(pk_cols).order_by(", ".join(pk_names_desc)).offset(offset_).limit(1).alias()
for pk_name in pk_names:
item = (tbl_main.c[pk_name] <= sub_q.c[pk_name])
join_condition.append(item)
if len(join_condition) > 1:
j = join(tbl_main, sub_q, and_(*join_condition))
else:
j = join(tbl_main, sub_q, join_condition[0])
return select([tbl_main]).select_from(j).order_by(", ".join(pk_names_desc)).limit(size_)
def get_range_data(offset_, size_):
tbl_main = alias(tbl_def, 't')
join_condition = []
pk_names_desc = [name + " DESC" for name in pk_names]
sub_q = select(pk_cols).order_by(
", ".join(pk_names_desc)).offset(offset_).limit(1).alias()
for pk_name in pk_names:
item = (tbl_main.c[pk_name] <= sub_q.c[pk_name])
join_condition.append(item)
if len(join_condition) > 1:
j = join(tbl_main, sub_q, and_(*join_condition))
else:
j = join(tbl_main, sub_q, join_condition[0])
return select([tbl_main]).select_from(j).order_by(
", ".join(pk_names_desc)).limit(size_)
def _compute_ranks():
tc_session.expunge_all()
l_session = tc_session()
min_r = select([func.min(BwHistory.rank)],
BwHistory.table.c.router_idhex
== RouterStats.table.c.router_idhex).as_scalar()
avg_r = select([func.avg(BwHistory.rank)],
BwHistory.table.c.router_idhex
== RouterStats.table.c.router_idhex).as_scalar()
max_r = select([func.max(BwHistory.rank)],
BwHistory.table.c.router_idhex
== RouterStats.table.c.router_idhex).as_scalar()
avg_bw = select([func.avg(BwHistory.bw)],
BwHistory.table.c.router_idhex
== RouterStats.table.c.router_idhex).as_scalar()
avg_desc_bw = select([func.avg(BwHistory.desc_bw)],
BwHistory.table.c.router_idhex
== RouterStats.table.c.router_idhex).as_scalar()
RouterStats.table.update(values=
{RouterStats.table.c.min_rank:min_r,
RouterStats.table.c.avg_rank:avg_r,
RouterStats.table.c.max_rank:max_r,
RouterStats.table.c.avg_bw:avg_bw,
RouterStats.table.c.avg_desc_bw:avg_desc_bw}).execute()
#min_avg_rank = select([func.min(RouterStats.avg_rank)]).as_scalar()
# the commented query breaks mysql because UPDATE cannot reference
# target table in the FROM clause. So we throw in an anonymous alias and wrap
# another select around it in order to get the nested SELECT stored into a
# temporary table.
# FIXME: performance? no idea
#max_avg_rank = select([func.max(RouterStats.avg_rank)]).as_scalar()
max_avg_rank = select([alias(select([func.max(RouterStats.avg_rank)]))]).as_scalar()
RouterStats.table.update(values=
{RouterStats.table.c.percentile:
(100.0*RouterStats.table.c.avg_rank)/max_avg_rank}).execute()
l_session.commit()
tc_session.remove()
def get_ticcl_variants(session, wordform, lexicon_id, corpus_id):
wf_to = alias(Wordform)
q = select([Wordform.wordform.label('wordform_from'),
WordformLinkSource.wordform_from_correct,
wf_to.c.wordform.label('wordform_to'),
WordformLinkSource.wordform_to_correct,
WordformLinkSource.ld,
func.sum(TextAttestation.frequency).label('freq_in_corpus')]) \
.select_from(WordformLink.__table__
.join(WordformLinkSource)
.join(Wordform,
onclause=WordformLink.wordform_from == Wordform.wordform_id)
.join(wf_to, onclause=WordformLink.wordform_to == wf_to.c.wordform_id)
.join(TextAttestation,
onclause=wf_to.c.wordform_id == TextAttestation.wordform_id)
.join(Document).join(corpusId_x_documentId).join(Corpus)) \
.where(and_(Wordform.wordform == wordform,
WordformLinkSource.lexicon_id == lexicon_id,
Corpus.corpus_id == corpus_id)) \
.group_by('wordform_to',
WordformLinkSource.wordform_from_correct,
WordformLinkSource.wordform_to_correct,
WordformLinkSource.ld) \
.order_by(desc('freq_in_corpus'), WordformLinkSource.ld, 'wordform_to')
df = pd.read_sql(q, session.get_bind())
correct = None
if df.shape[0] > 0:
correct = df.loc[0, 'wordform_from_correct']
df = df[['wordform_to', 'wordform_to_correct', 'ld', 'freq_in_corpus']]
df.columns = ['wordform', 'correct', 'ld', 'freq_in_corpus']
return {
'wordform': wordform,
'correct': bool(correct),
'links': df.to_dict(orient='record')
}
def query(self):
pq = qualstat_getstatdata(bindparam("server"),
column("eval_type") == "f")
base = alias(pq)
query = (
select([
# queryid in pg11+ is int64, so the value can exceed javascript's
# Number.MAX_SAFE_INTEGER, which mean that the value can get
# truncated by the browser, leading to looking for unexisting
# queryid when processing this data. To avoid that, simply cast
# the value to text.
func.array_agg(cast(column("queryid"),
TEXT)).label("queryids"),
column("qualid"),
cast(column("quals"), JSONB).label('quals'),
column("occurences"),
column("execution_count"),
func.array_agg(column("query")).label("queries"),
column("avg_filter"),
column("filter_ratio")
]).select_from(
join(base,
powa_databases,
onclause=(powa_databases.c.oid == literal_column("dbid")
and powa_databases.c.srvid
== literal_column("srvid")))).
where(powa_databases.c.datname == bindparam("database")).where(
powa_databases.c.srvid == bindparam("server")).where(
column("avg_filter") > 1000).where(
column("filter_ratio") > 0.3).group_by(
column("qualid"), column("execution_count"),
column("occurences"), cast(column("quals"), JSONB),
column("avg_filter"),
column("filter_ratio")).order_by(
column("occurences").desc()).limit(200))
return query
def read(self,
df_name=None,
query=None,
where=None,
group_by=None,
order_by=None,
limit=None,
offset=None,
disable_count_total=False,
**kwargs):
"""Read data from SQL.
Args:
df_name (str): Dataframe name to read
query (str SQL query or SQLAlchemy Selectable): query to select items
where (str): query string for filtering items
group_by (str): column name to group
order_by (srt): column name to sort by
limit (int): number of items to return per a page
offset (int): offset of cursor
disable_count_total (bool): if True, `self.count_total` will not be calculated
**kwargs: kwargs for function `pandas.read_sql_query`
or `influxdb.DataFrameClient.query`
"""
if df_name is not None:
self.df_name = df_name
# Create a query
q = query
q_count = 'select count(*) from ({}) as sub'.format(q) if q is not None else None
if q is None:
# Check if the table exits on DB
if not inspect(self._engine).has_table(self.df_name):
self.df = self._initialize_df()
return
# Create a query
if self._config.current_db['engine'] in ['postgresql', 'timescaledb']:
q = sql.select('*', from_obj=sql.table(self.df_name))
if group_by is not None:
q = sql.select(
[sql.text(self._distinct(c))
for c in self._get_column_names_from_db()],
from_obj=sql.table(self.df_name))
q = q.group_by(sql.text(group_by))
if where is not None:
q = q.where(sql.text(where))
q_count = sql.select([sql.text('count(*)')], from_obj=sql.alias(q, 'sub'))
else:
q = sql.select('*', from_obj=sql.table(self.df_name))
q_count = sql.select([sql.text('count(*)')], from_obj=sql.table(self.df_name))
if group_by is not None:
q = sql.select(
[sql.text(self._distinct(c))
for c in self._get_column_names_from_db()],
from_obj=sql.table(self.df_name))
q = q.group_by(sql.text(group_by))
q_count = sql.select([sql.text('count(distinct {})'.format(group_by))],
from_obj=sql.table(self.df_name))
if where is not None:
q = q.where(sql.text(where))
q_count = q_count.where(sql.text(where))
# Add common options
if order_by is not None:
q = q.order_by(sql.text(order_by))
if limit is not None:
q = q.limit(limit)
if offset is not None:
q = q.offset(offset)
# Read table from DB
try:
# Calculate total number of rows
if not disable_count_total:
count = self._engine.execute(q_count).scalar()
else:
count = 0
# Fetch data
if 'index_col' not in kwargs.keys():
kwargs.update({'index_col': 'uuid_in_df'})
df = pd.read_sql_query(q, self._engine, **kwargs)
df = df[~df.index.duplicated(keep='last')]
except Exception as e:
self.logger.warning('Could not execute SQL statement: "{0}" (reason: {1})'.
format(str(q), str(e)))
count = 0
df = self._initialize_df()
self._count_total = count
self.df = df
def _load_tree_rows(self, conn, where, order_by, parent_id):
"""Load rows as a tree."""
if where is not None:
# FIXME: If we have a where clause, we cant load the results lazily
# because, we don't know if a row's children/grandchildren/etc will
# match. If this optimization (loading the leafs and the necessary
# parents until the root) good enough?
children = {}
node_mapper = {}
def load_rows(where_):
query = self.select(conn,
self.table,
columns=self.table.columns,
where=where_,
order_by=order_by)
for row in query:
row_id = row[self.id_column_idx]
if row_id in node_mapper:
continue
c_list = children.setdefault(row[self.parent_column_idx],
[])
node = Node(data=row)
c_list.append(node)
node_mapper[row_id] = node
load_rows(where)
if not children:
return
# Load parents incrementally until we are left with the root
while children.keys() != [None]:
parents_to_load = []
for parent, c_list in children.items():
if parent is None:
continue
node = node_mapper.get(parent, None)
if node is None:
parents_to_load.append(parent)
continue
node.extend(c_list)
node.children_len = len(node)
del children[parent]
if parents_to_load:
where = self.table.columns[self.ID_COLUMN].in_(
parents_to_load)
load_rows(where)
for node in children[None]:
yield node
else:
# If there's no where clause, we can load the results lazily
where = self.table.columns[self.PARENT_ID_COLUMN] == parent_id
if self.CHILDREN_LEN_COLUMN is None:
count_table = alias(self.table, '__count')
# We could use the comparison between the columns, but that would
# make sqlalchemy add self.table in the FROM clause, which
# would produce wrong results.
count_where = '%s.%s = %s.%s' % (
count_table.name, self.PARENT_ID_COLUMN, self.table.name,
self.ID_COLUMN)
count_select = select([func.count(1)],
whereclause=count_where,
from_obj=[count_table])
columns = self.table.columns.values()
# We have to compile this here or else sqlalchemy would put
# this inside the FROM part.
columns.append('(%s)' % (_compile(count_select), ))
extra_count_col = True
else:
columns = self.table.columns
extra_count_col = False
query = self.select(conn,
self.table,
columns=columns,
where=where,
order_by=order_by)
for row in query:
if extra_count_col:
children_len = row.pop(-1)
else:
children_len = row[self.children_len_column_idx]
yield Node(data=row, children_len=children_len)
def get_old_messages_backend(request, user_profile,
anchor = REQ(converter=int),
num_before = REQ(converter=to_non_negative_int),
num_after = REQ(converter=to_non_negative_int),
narrow = REQ('narrow', converter=narrow_parameter, default=None),
use_first_unread_anchor = REQ(default=False, converter=ujson.loads),
apply_markdown=REQ(default=True,
converter=ujson.loads)):
# type: (HttpRequest, UserProfile, int, int, int, Optional[List[Dict[str, Any]]], bool, bool) -> HttpResponse
include_history = ok_to_include_history(narrow, user_profile.realm)
if include_history and not use_first_unread_anchor:
query = select([column("id").label("message_id")], None, table("zerver_message"))
inner_msg_id_col = literal_column("zerver_message.id")
elif narrow is None:
query = select([column("message_id"), column("flags")],
column("user_profile_id") == literal(user_profile.id),
table("zerver_usermessage"))
inner_msg_id_col = column("message_id")
else:
# TODO: Don't do this join if we're not doing a search
query = select([column("message_id"), column("flags")],
column("user_profile_id") == literal(user_profile.id),
join(table("zerver_usermessage"), table("zerver_message"),
literal_column("zerver_usermessage.message_id") ==
literal_column("zerver_message.id")))
inner_msg_id_col = column("message_id")
num_extra_messages = 1
is_search = False
if narrow is not None:
# Add some metadata to our logging data for narrows
verbose_operators = []
for term in narrow:
if term['operator'] == "is":
verbose_operators.append("is:" + term['operand'])
else:
verbose_operators.append(term['operator'])
request._log_data['extra'] = "[%s]" % (",".join(verbose_operators),)
# Build the query for the narrow
num_extra_messages = 0
builder = NarrowBuilder(user_profile, inner_msg_id_col)
search_term = None # type: Optional[Dict[str, Any]]
for term in narrow:
if term['operator'] == 'search':
if not is_search:
search_term = term
query = query.column(column("subject")).column(column("rendered_content"))
is_search = True
else:
# Join the search operators if there are multiple of them
search_term['operand'] += ' ' + term['operand']
else:
query = builder.add_term(query, term)
if is_search:
query = builder.add_term(query, search_term)
# We add 1 to the number of messages requested if no narrow was
# specified to ensure that the resulting list always contains the
# anchor message. If a narrow was specified, the anchor message
# might not match the narrow anyway.
if num_after != 0:
num_after += num_extra_messages
else:
num_before += num_extra_messages
sa_conn = get_sqlalchemy_connection()
if use_first_unread_anchor:
condition = column("flags").op("&")(UserMessage.flags.read.mask) == 0
# We exclude messages on muted topics when finding the first unread
# message in this narrow
muting_conditions = exclude_muting_conditions(user_profile, narrow)
if muting_conditions:
condition = and_(condition, *muting_conditions)
first_unread_query = query.where(condition)
first_unread_query = first_unread_query.order_by(inner_msg_id_col.asc()).limit(1)
first_unread_result = list(sa_conn.execute(first_unread_query).fetchall())
if len(first_unread_result) > 0:
anchor = first_unread_result[0][0]
else:
anchor = LARGER_THAN_MAX_MESSAGE_ID
before_query = None
after_query = None
if num_before != 0:
before_anchor = anchor
if num_after != 0:
# Don't include the anchor in both the before query and the after query
before_anchor = anchor - 1
before_query = query.where(inner_msg_id_col <= before_anchor) \
.order_by(inner_msg_id_col.desc()).limit(num_before)
if num_after != 0:
after_query = query.where(inner_msg_id_col >= anchor) \
.order_by(inner_msg_id_col.asc()).limit(num_after)
if anchor == LARGER_THAN_MAX_MESSAGE_ID:
# There's no need for an after_query if we're targeting just the target message.
after_query = None
if before_query is not None:
if after_query is not None:
query = union_all(before_query.self_group(), after_query.self_group())
else:
query = before_query
elif after_query is not None:
query = after_query
else:
# This can happen when a narrow is specified.
query = query.where(inner_msg_id_col == anchor)
main_query = alias(query)
query = select(main_query.c, None, main_query).order_by(column("message_id").asc())
# This is a hack to tag the query we use for testing
query = query.prefix_with("/* get_old_messages */")
query_result = list(sa_conn.execute(query).fetchall())
# The following is a little messy, but ensures that the code paths
# are similar regardless of the value of include_history. The
# 'user_messages' dictionary maps each message to the user's
# UserMessage object for that message, which we will attach to the
# rendered message dict before returning it. We attempt to
# bulk-fetch rendered message dicts from remote cache using the
# 'messages' list.
search_fields = dict() # type: Dict[int, Dict[str, Text]]
message_ids = [] # type: List[int]
user_message_flags = {} # type: Dict[int, List[str]]
if include_history:
message_ids = [row[0] for row in query_result]
# TODO: This could be done with an outer join instead of two queries
user_message_flags = dict((user_message.message_id, user_message.flags_list()) for user_message in
UserMessage.objects.filter(user_profile=user_profile,
message__id__in=message_ids))
for row in query_result:
message_id = row[0]
if user_message_flags.get(message_id) is None:
user_message_flags[message_id] = ["read", "historical"]
if is_search:
(_, subject, rendered_content, content_matches, subject_matches) = row
search_fields[message_id] = get_search_fields(rendered_content, subject,
content_matches, subject_matches)
else:
for row in query_result:
message_id = row[0]
flags = row[1]
user_message_flags[message_id] = parse_usermessage_flags(flags)
message_ids.append(message_id)
if is_search:
(_, _, subject, rendered_content, content_matches, subject_matches) = row
search_fields[message_id] = get_search_fields(rendered_content, subject,
content_matches, subject_matches)
cache_transformer = lambda row: MessageDict.build_dict_from_raw_db_row(row, apply_markdown)
id_fetcher = lambda row: row['id']
message_dicts = generic_bulk_cached_fetch(lambda message_id: to_dict_cache_key_id(message_id, apply_markdown),
Message.get_raw_db_rows,
message_ids,
id_fetcher=id_fetcher,
cache_transformer=cache_transformer,
extractor=extract_message_dict,
setter=stringify_message_dict)
message_list = []
for message_id in message_ids:
msg_dict = message_dicts[message_id]
msg_dict.update({"flags": user_message_flags[message_id]})
msg_dict.update(search_fields.get(message_id, {}))
message_list.append(msg_dict)
statsd.incr('loaded_old_messages', len(message_list))
ret = {'messages': message_list,
"result": "success",
"msg": ""}
return json_success(ret)
def store_packages(self, **kwargs):
pkg_data = kwargs.get("pkg_data")
rpms = {}
files = 0
log.info("Storing packages...")
# Remove any existing records
fdl = alias(RpmFileDetailLink)
rd = alias(RpmDetail)
delete_fdl = delete(fdl).where(
exists(
select(
[1]).where(rd.c.system_id == self.system.system_id).where(
rd.c.rpm_detail_id == fdl.c.rpm_detail_id)))
log.info(
f"Pruned {State.get_db_session().execute(delete_fdl).rowcount} links."
)
system_rpm_detail = State.get_db_session().query(RpmDetail).filter(
RpmDetail.system == self.system)
system_rpm_detail.delete()
log.info("Pruned existing RpmDetail records.")
system_rpm_info = State.get_db_session().query(RpmInfo).filter(
RpmInfo.system == self.system)
system_rpm_info.delete()
log.info("Pruned existing RpmInfo records.")
State.get_db_session().flush()
State.get_db_session().commit()
fieldnames = (
'package_name',
'version',
'release',
'architecture',
'installation_tid',
'installation_date',
'file_name',
'file_size',
'digest',
'file_class',
'flag',
'source_rpm',
'rpm_name',
)
objects = []
for row in self._convert_results(file_iter=pkg_data,
fieldnames=fieldnames):
file = {
key: value if value != '(none)' else None
for key, value in row.items()
}
rpm_key = '+'.join([
file['package_name'] or 'none',
file['version'] or 'none',
file['architecture'] or 'none',
])
rpm = rpms.get(rpm_key, None)
if not rpm:
try:
installation_tid = int(file['installation_tid']
or ''.strip())
except ValueError:
installation_tid = None
try:
installation_date = parser.parse(file['installation_date'])
except ValueError:
installation_date = None
rpm = RpmInfo(
name=file['package_name'],
version=file['version'],
release=file['release'],
filename=file['rpm_name'],
architecture=file['architecture'],
installation_tid=installation_tid,
installation_date=installation_date,
system_id=self.system.system_id,
)
State.get_db_session().add(rpm)
State.get_db_session().flush()
rpms[rpm_key] = rpm
try:
file_size = int(file['file_size'] or ''.strip())
except ValueError:
file_size = None
objects.append({
"rpm_info_id": rpm.rpm_info_id,
"file_location": file['file_name'],
"file_size": file_size,
"digest": file['digest'] or None,
"file_info": file['file_class'],
"file_flag": file['flag'],
"system_id": self.system.system_id,
"file_changed": None,
})
files += 1
if files % 50000 == 0:
log.info(f"{files}")
State.get_db_session().bulk_insert_mappings(RpmDetail, objects)
State.get_db_session().flush()
objects.clear()
if objects:
State.get_db_session().bulk_insert_mappings(RpmDetail, objects)
objects.clear()
State.get_db_session().flush()
State.get_db_session().commit()
log.info('..done')
def store_files(self, **kwargs):
file_iter = kwargs.get("file_iter")
files = 0
log.info("Storing files...")
# Remove any existing records
fdl = alias(RpmFileDetailLink)
fd = alias(FileDetail)
delete_fdl = delete(fdl).where(
exists(
select(
[1]).where(fd.c.system_id == self.system.system_id).where(
fd.c.file_detail_id == fdl.c.file_detail_id)))
log.info(
f"Pruned {State.get_db_session().execute(delete_fdl).rowcount} links."
)
# delete FileStorage links
FileDifference(system=self.system).clear_system_file_storage()
system_files = State.get_db_session().query(FileDetail).filter(
FileDetail.system == self.system)
system_files.delete()
log.info("Pruned existing FileDetails.")
State.get_db_session().flush()
State.get_db_session().commit()
objects = []
for file_dict in self._convert_results(file_iter=file_iter):
src = FileOrigin.UnknownSource
file_path = file_dict.get("path", "")
if (file_path.startswith("/dev/") or file_path.startswith("/tmp/")
or file_path.startswith("/proc/")
or (file_path.startswith("/var/log/")
and file_path.endswith(".log"))):
src = FileOrigin.EphemeralContent
file_rec = {
"system_id": self.system.system_id,
"file_location": file_path or None,
"file_type": file_dict['type'],
"owner_uid": file_dict['uid'],
"owner_gid": file_dict['gid'],
"owner_name": file_dict['user'] or None,
"owner_group": file_dict['group'] or None,
"file_mode": file_dict['mode'] or None,
"file_target": file_dict['target'] or None,
"target_type": file_dict['target_type'] or None,
"md5_digest": file_dict['md5'] or None,
"sha256_digest": file_dict['sha256'] or None,
"file_info": file_dict['info'] or None,
"file_perm_mode": file_dict['perm'] or None,
"origin": src.name,
}
objects.append(file_rec)
files += 1
if files % 50000 == 0:
log.info(f"{files}")
State.get_db_session().bulk_insert_mappings(
FileDetail, objects)
State.get_db_session().flush()
objects.clear()
if objects:
State.get_db_session().bulk_insert_mappings(FileDetail, objects)
objects.clear()
State.get_db_session().flush()
State.get_db_session().commit()
log.info('..done')
def ComposeNodeQuery(*nodes):
return select(["*"], from_obj=[alias(n.query) for n in nodes])
print (u)
print u in session
# Session.remove()
session.close()
# u.fullname = 'Tang'
session = Session()
print u in session
print (2, session)
# session.add(u)
# session.commit()
# print(our_user)
from sqlalchemy.sql import select, join, alias
cnt1 = alias(select([func.count("*")]).where(User.id <= 2))
cnt2 = alias(select([func.count("*")]).where(User.id >= 100))
cnt3 = alias(select([func.count("*")]).where(User.id == 7))
# r = session.execute(select(['*'], from_obj=[cnt1, cnt2, cnt3]))
def InitData():
ss = Session()
c1 = Card()
c1.cardid = 1
c1.level = 5
c2 = Card()
c2.cardid = 1
c2.level = 7
c3 = Card()
c3.cardid = 2
def _get_table_clause(self, tbl_clz):
table_clause = sql.table(tbl_clz.name)
table_clause.schema = tbl_clz.scheme
return sql.alias(table_clause, tbl_clz.alias)
def execute(self, request, user, page="1", sort="score"):
page = int(page)
offset = (page - 1)*50
order = {"members" : (desc("ialliances_members"),),
"size" : (desc("ialliances_size"),),
"value" : (desc("ialliances_value"),),
"score" : (desc("ialliances_score"),),
"avg_size" : (desc("ialliances_avg_size"),),
"avg_value" : (desc("ialliances_avg_value"),),
"avg_score" : (desc("ialliances_avg_score"),),
"t10s" : (desc("ialliances_t10s"),),
"t50s" : (desc("ialliances_t50s"),),
"t100s" : (desc("ialliances_t100s"),),
"t200s" : (desc("ialliances_t200s"),),
"t10v" : (desc("ialliances_t10v"),),
"t50v" : (desc("ialliances_t50v"),),
"t100v" : (desc("ialliances_t100v"),),
"t200v" : (desc("ialliances_t200v"),),
}
if sort not in order.keys():
sort = "score"
order = order.get(sort)
members = count().label("members")
size = sum(Planet.size).label("size")
value = sum(Planet.value).label("value")
score = sum(Planet.score).label("score")
avg_size = size.op("/")(members).label("avg_size")
avg_value = value.op("/")(members).label("avg_value")
avg_score = score.op("/")(members).label("avg_score")
t10s = count(case(whens=((Planet.score_rank <= 10 ,1),), else_=None)).label("t10s")
t50s = count(case(whens=((Planet.score_rank <= 50 ,1),), else_=None)).label("t50s")
t100s = count(case(whens=((Planet.score_rank <= 100 ,1),), else_=None)).label("t100s")
t200s = count(case(whens=((Planet.score_rank <= 200 ,1),), else_=None)).label("t200s")
t10v = count(case(whens=((Planet.value_rank <= 10 ,1),), else_=None)).label("t10v")
t50v = count(case(whens=((Planet.value_rank <= 50 ,1),), else_=None)).label("t50v")
t100v = count(case(whens=((Planet.value_rank <= 100 ,1),), else_=None)).label("t100v")
t200v = count(case(whens=((Planet.value_rank <= 200 ,1),), else_=None)).label("t200v")
alliance1 = aliased(Alliance)
Q = session.query(size, value, score,
avg_size, avg_value, avg_score,
t10s, t50s, t100s, t200s,
t10v, t50v, t100v, t200v,
members,
alliance1.id,
)
Q = Q.join(Planet.intel)
Q = Q.join((alliance1, Intel.alliance,))
Q = Q.filter(Planet.active == True)
Q = Q.group_by(alliance1.id)
ialliances = alias(Q.subquery(), "ialliances")
alliance2 = aliased(Alliance)
Q = session.query(alliance2.name, alliance2.members)
Q = Q.add_columns(ialliances)
Q = Q.filter(alliance2.id == ialliances.c.id)
count_ = Q.count()
pages = count_/50 + int(count_%50 > 0)
pages = range(1, 1+pages)
for o in order:
Q = Q.order_by(o)
Q = Q.limit(50).offset(offset)
return render("ialliances.tpl", request, alliances=Q.all(), offset=offset, pages=pages, page=page, sort=sort)
def get_messages_backend(request: HttpRequest, user_profile: UserProfile,
anchor_val: Optional[str]=REQ(
'anchor', str_validator=check_string, default=None),
num_before: int=REQ(converter=to_non_negative_int),
num_after: int=REQ(converter=to_non_negative_int),
narrow: OptionalNarrowListT=REQ('narrow', converter=narrow_parameter, default=None),
use_first_unread_anchor_val: bool=REQ('use_first_unread_anchor',
validator=check_bool, default=False),
client_gravatar: bool=REQ(validator=check_bool, default=False),
apply_markdown: bool=REQ(validator=check_bool, default=True)) -> HttpResponse:
anchor = parse_anchor_value(anchor_val, use_first_unread_anchor_val)
if num_before + num_after > MAX_MESSAGES_PER_FETCH:
return json_error(_("Too many messages requested (maximum {}).").format(
MAX_MESSAGES_PER_FETCH,
))
if user_profile.realm.email_address_visibility != Realm.EMAIL_ADDRESS_VISIBILITY_EVERYONE:
# If email addresses are only available to administrators,
# clients cannot compute gravatars, so we force-set it to false.
client_gravatar = False
include_history = ok_to_include_history(narrow, user_profile)
if include_history:
# The initial query in this case doesn't use `zerver_usermessage`,
# and isn't yet limited to messages the user is entitled to see!
#
# This is OK only because we've made sure this is a narrow that
# will cause us to limit the query appropriately later.
# See `ok_to_include_history` for details.
need_message = True
need_user_message = False
elif narrow is None:
# We need to limit to messages the user has received, but we don't actually
# need any fields from Message
need_message = False
need_user_message = True
else:
need_message = True
need_user_message = True
query, inner_msg_id_col = get_base_query_for_search(
user_profile=user_profile,
need_message=need_message,
need_user_message=need_user_message,
)
query, is_search = add_narrow_conditions(
user_profile=user_profile,
inner_msg_id_col=inner_msg_id_col,
query=query,
narrow=narrow,
)
if narrow is not None:
# Add some metadata to our logging data for narrows
verbose_operators = []
for term in narrow:
if term['operator'] == "is":
verbose_operators.append("is:" + term['operand'])
else:
verbose_operators.append(term['operator'])
request._log_data['extra'] = "[{}]".format(",".join(verbose_operators))
sa_conn = get_sqlalchemy_connection()
if anchor is None:
# The use_first_unread_anchor code path
anchor = find_first_unread_anchor(
sa_conn,
user_profile,
narrow,
)
anchored_to_left = (anchor == 0)
# Set value that will be used to short circuit the after_query
# altogether and avoid needless conditions in the before_query.
anchored_to_right = (anchor >= LARGER_THAN_MAX_MESSAGE_ID)
if anchored_to_right:
num_after = 0
first_visible_message_id = get_first_visible_message_id(user_profile.realm)
query = limit_query_to_range(
query=query,
num_before=num_before,
num_after=num_after,
anchor=anchor,
anchored_to_left=anchored_to_left,
anchored_to_right=anchored_to_right,
id_col=inner_msg_id_col,
first_visible_message_id=first_visible_message_id,
)
main_query = alias(query)
query = select(main_query.c, None, main_query).order_by(column("message_id").asc())
# This is a hack to tag the query we use for testing
query = query.prefix_with("/* get_messages */")
rows = list(sa_conn.execute(query).fetchall())
query_info = post_process_limited_query(
rows=rows,
num_before=num_before,
num_after=num_after,
anchor=anchor,
anchored_to_left=anchored_to_left,
anchored_to_right=anchored_to_right,
first_visible_message_id=first_visible_message_id,
)
rows = query_info['rows']
# The following is a little messy, but ensures that the code paths
# are similar regardless of the value of include_history. The
# 'user_messages' dictionary maps each message to the user's
# UserMessage object for that message, which we will attach to the
# rendered message dict before returning it. We attempt to
# bulk-fetch rendered message dicts from remote cache using the
# 'messages' list.
message_ids: List[int] = []
user_message_flags: Dict[int, List[str]] = {}
if include_history:
message_ids = [row[0] for row in rows]
# TODO: This could be done with an outer join instead of two queries
um_rows = UserMessage.objects.filter(user_profile=user_profile,
message__id__in=message_ids)
user_message_flags = {um.message_id: um.flags_list() for um in um_rows}
for message_id in message_ids:
if message_id not in user_message_flags:
user_message_flags[message_id] = ["read", "historical"]
else:
for row in rows:
message_id = row[0]
flags = row[1]
user_message_flags[message_id] = UserMessage.flags_list_for_flags(flags)
message_ids.append(message_id)
search_fields: Dict[int, Dict[str, str]] = dict()
if is_search:
for row in rows:
message_id = row[0]
(topic_name, rendered_content, content_matches, topic_matches) = row[-4:]
try:
search_fields[message_id] = get_search_fields(rendered_content, topic_name,
content_matches, topic_matches)
except UnicodeDecodeError as err: # nocoverage
# No coverage for this block since it should be
# impossible, and we plan to remove it once we've
# debugged the case that makes it happen.
raise Exception(str(err), message_id, narrow)
message_list = messages_for_ids(
message_ids=message_ids,
user_message_flags=user_message_flags,
search_fields=search_fields,
apply_markdown=apply_markdown,
client_gravatar=client_gravatar,
allow_edit_history=user_profile.realm.allow_edit_history,
)
statsd.incr('loaded_old_messages', len(message_list))
ret = dict(
messages=message_list,
result='success',
msg='',
found_anchor=query_info['found_anchor'],
found_oldest=query_info['found_oldest'],
found_newest=query_info['found_newest'],
history_limited=query_info['history_limited'],
anchor=anchor,
)
return json_success(ret)
def _load_tree_rows(self, conn, where, order_by, parent_id):
"""Load rows as a tree."""
if where is not None:
# FIXME: If we have a where clause, we cant load the results lazily
# because, we don't know if a row's children/grandchildren/etc will
# match. If this optimization (loading the leafs and the necessary
# parents until the root) good enough?
children = {}
node_mapper = {}
def load_rows(where_):
query = self.select(
conn, self.table, columns=self.table.columns,
where=where_, order_by=order_by)
for row in query:
row_id = row[self.id_column_idx]
if row_id in node_mapper:
continue
c_list = children.setdefault(
row[self.parent_column_idx], [])
node = Node(data=row)
c_list.append(node)
node_mapper[row_id] = node
load_rows(where)
if not children:
return
# Load parents incrementally until we are left with the root
while children.keys() != [None]:
parents_to_load = []
for parent, c_list in children.items():
if parent is None:
continue
node = node_mapper.get(parent, None)
if node is None:
parents_to_load.append(parent)
continue
node.extend(c_list)
node.children_len = len(node)
del children[parent]
if parents_to_load:
where = self.table.columns[self.ID_COLUMN].in_(
parents_to_load)
load_rows(where)
for node in children[None]:
yield node
else:
# If there's no where clause, we can load the results lazily
where = self.table.columns[self.PARENT_ID_COLUMN] == parent_id
if self.CHILDREN_LEN_COLUMN is None:
count_table = alias(self.table, '__count')
# We could use the comparison between the columns, but that would
# make sqlalchemy add self.table in the FROM clause, which
# would produce wrong results.
count_where = '%s.%s = %s.%s' % (
count_table.name, self.PARENT_ID_COLUMN,
self.table.name, self.ID_COLUMN)
count_select = select(
[func.count(1)],
whereclause=count_where, from_obj=[count_table])
columns = self.table.columns.values()
# We have to compile this here or else sqlalchemy would put
# this inside the FROM part.
columns.append('(%s)' % (_compile(count_select), ))
extra_count_col = True
else:
columns = self.table.columns
extra_count_col = False
query = self.select(
conn, self.table, columns=columns,
where=where, order_by=order_by)
for row in query:
if extra_count_col:
children_len = row.pop(-1)
else:
children_len = row[self.children_len_column_idx]
yield Node(data=row, children_len=children_len)
