def where(self, **kwargs):
conditions = []
for k, v in kwargs.items():
if not v: # ignore None and empty lists
continue
elif isinstance(v, list):
conditions.append(web.reparam(k + " in $v", locals()))
else:
conditions.append(web.reparam(k + "=$v", locals()))
if not conditions:
return "1=1"
return web.SQLQuery.join(conditions, " AND ")
def process(c, ordering_func=None):
# ordering_func is used when the query contains emebbabdle objects
#
# example: {'links': {'title: 'foo', 'url': 'http://example.com/foo'}}
if c.datatype == 'ref':
metadata = self.get_metadata(c.value)
if metadata is None:
# required object is not found so the query result wil be empty.
# Raise StopIteration to indicate empty result.
raise StopIteration
c.value = metadata.id
if c.op == '~':
op = Literal('LIKE')
c.value = c.value.replace('*', '%')
else:
op = Literal(c.op)
if c.key in ['key', 'type', 'created', 'last_modified']:
#@@ special optimization to avoid join with thing.type when there are non-common properties in the query.
#@@ Since type information is already present in property table,
#@@ getting property id is equivalent to join with type.
if c.key == 'type' and type_required:
return
if isinstance(c.value, list):
q = web.sqlors('thing.%s %s ' % (c.key, op), c.value)
else:
q = web.reparam('thing.%s %s $c.value' % (c.key, op), locals())
xwheres = [q]
# Add thing table explicitly because get_table is not called
tables['_thing'] = DBTable("thing")
else:
table = get_table(c.datatype, c.key)
key_id = self.get_property_id(type_id, c.key)
if not key_id:
raise StopIteration
q1 = web.reparam('%(table)s.key_id=$key_id' % {'table': table}, locals())
if isinstance(c.value, list):
q2 = web.sqlors('%s.value %s ' % (table, op), c.value)
else:
q2 = web.reparam('%s.value %s $c.value' % (table, op), locals())
xwheres = [q1, q2]
if ordering_func:
xwheres.append(ordering_func(table))
wheres.extend(xwheres)
def process_sort(query):
"""Process sort field in the query and returns the db column to order by."""
if query.sort:
sort_key = query.sort.key
if sort_key.startswith('-'):
ascending = " desc"
sort_key = sort_key[1:]
else:
ascending = ""
if sort_key in ['key', 'type', 'created', 'last_modified']:
order = 'thing.' + sort_key # make sure c.key is valid
# Add thing table explicitly because get_table is not called
tables['_thing'] = DBTable("thing")
else:
table = get_table(query.sort.datatype, sort_key)
key_id = self.get_property_id(type_id, sort_key)
if key_id is None:
raise StopIteration
q = '%(table)s.key_id=$key_id' % {'table': table}
wheres.append(web.reparam(q, locals()))
order = table.label + '.value'
return order + ascending
else:
return None
def versions(self, query):
what = 'thing.key, version.revision, transaction.*'
where = 'version.thing_id = thing.id AND version.transaction_id = transaction.id'
if config.get('use_machine_comment'):
what += ", version.machine_comment"
def get_id(key):
meta = self.get_metadata(key)
if meta:
return meta.id
else:
raise StopIteration
for c in query.conditions:
key, value = c.key, c.value
assert key in ['key', 'type', 'author', 'ip', 'comment', 'created', 'bot']
try:
if key == 'key':
key = 'thing_id'
value = get_id(value)
elif key == 'type':
key = 'thing.type'
value = get_id(value)
elif key == 'author':
key = 'transaction.author_id'
value = get_id(value)
else:
# 'bot' column is not enabled
if key == 'bot' and not config.use_bot_column:
raise StopIteration
key = 'transaction.' + key
except StopIteration:
# StopIteration is raised when a non-existing object is referred in the query
return []
where += web.reparam(' AND %s=$value' % key, locals())
sort = query.sort
if sort and sort.startswith('-'):
sort = sort[1:] + ' desc'
sort = 'transaction.' + sort
t = self.db.transaction()
if config.query_timeout:
self.db.query("SELECT set_config('statement_timeout', $query_timeout, false)", dict(query_timeout=config.query_timeout))
result = self.db.select(['thing','version', 'transaction'], what=what, where=where, offset=query.offset, limit=query.limit, order=sort)
result = result.list()
author_ids = list(set(r.author_id for r in result if r.author_id))
authors = self.get_metadata_list_from_ids(author_ids)
t.commit()
for r in result:
r.author = r.author_id and authors[r.author_id].key
return result
def process_sort(query):
"""Process sort field in the query and returns the db column to order by."""
if query.sort:
sort_key = query.sort.key
if sort_key.startswith("-"):
ascending = " desc"
sort_key = sort_key[1:]
else:
ascending = ""
if sort_key in ["key", "type", "created", "last_modified"]:
order = "thing." + sort_key # make sure c.key is valid
# Add thing table explicitly because get_table is not called
tables["_thing"] = DBTable("thing")
else:
table = get_table(query.sort.datatype, sort_key)
key_id = self.get_property_id(type, sort_key)
if key_id is None:
raise StopIteration
q = "%(table)s.key_id=$key_id" % {"table": table}
wheres.append(web.reparam(q, locals()))
order = table.label + ".value"
return order + ascending
else:
return None
def query(category, olid, offset=0, limit=10):
category_id = get_category_id(category)
if isinstance(olid, list):
where = web.reparam('deleted=false AND category_id = $category_id AND ', locals()) \
+ web.sqlors('olid=', olid)
elif olid is None:
where = web.reparam('deleted=false AND category_id=$category_id', locals())
else:
where = web.reparam('deleted=false AND category_id=$category_id AND olid=$olid', locals())
result = getdb().select('cover',
what='*',
where= where,
order='last_modified desc',
offset=offset,
limit=limit)
return result.list()
def query(category, olid, offset=0, limit=10):
category_id = get_category_id(category)
deleted = False
if isinstance(olid, list):
if len(olid) == 0:
olid = [-1]
where = web.reparam('deleted=$deleted AND category_id = $category_id AND olid IN $olid', locals())
elif olid is None:
where = web.reparam('deleted=$deleted AND category_id=$category_id', locals())
else:
where = web.reparam('deleted=$deleted AND category_id=$category_id AND olid=$olid', locals())
result = getdb().select('cover',
what='*',
where= where,
order='last_modified desc',
offset=offset,
limit=limit)
return result.list()
def sqlin(name, values):
"""
>>> sqlin('id', [1, 2, 3, 4])
<sql: 'id IN (1, 2, 3, 4)'>
>>> sqlin('id', [])
<sql: '1 = 2'>
"""
def sqljoin(queries, sep):
result = ""
for q in queries:
if result:
result = result + sep
result = result + q
return result
if not values:
return web.reparam('1 = 2', {})
else:
values = [web.reparam('$v', locals()) for v in values]
return name + ' IN ('+ sqljoin(values, ", ") + ')'
def __init__(self, db, session, subscription):
"""
:type db: web.DB
:type session: dict
:type subscription: numbers.Integral
:param db:
:param session:
:param subscription:
"""
self.db = db
self.sub = subscription
self.where = web.reparam("subscription=$id", {'id': self.sub})
self.storage = session
def get_many(self, keys):
if not keys:
return '{}'
xkeys = [web.reparam('$k', dict(k=k)) for k in keys]
query = 'SELECT thing.key, data.data from thing, data ' \
+ 'WHERE data.revision = thing.latest_revision and data.thing_id=thing.id ' \
+ ' AND thing.key IN (' + self.sqljoin(xkeys, ', ') + ')'
def process(query):
yield '{\n'
for i, r in enumerate(self.db.query(query)):
if i:
yield ',\n'
yield simplejson.dumps(r.key)
yield ": "
yield r.data
yield '}'
return "".join(process(query))
def get(self, ports):
if isinstance(ports, list):
arg = "({0})".format(",".join(map(str, ports)))
else:
arg = "({0})".format(ports)
query = """SELECT aliases.port,
COALESCE(ports.name, '') AS 'name',
COALESCE(ports.protocols, '') AS 'protocols',
COALESCE(ports.description, '') AS 'description',
aliases.active,
aliases.name AS alias_name,
aliases.description AS alias_description
FROM {table_aliases} AS `aliases`
LEFT JOIN {table_ports} AS `ports`
ON ports.port=aliases.port
WHERE aliases.port IN {port_list}
ORDER BY aliases.port ASC""".format(
table_ports=self.table_ports,
table_aliases=self.table_aliases,
port_list=web.reparam(arg, {}))
rows = self.db.query(query, vars={'plist': arg})
return list(rows)
def get_many(self, keys):
if not keys:
return "{}"
xkeys = [web.reparam("$k", dict(k=k)) for k in keys]
query = (
"SELECT thing.key, data.data from thing, data "
+ "WHERE data.revision = thing.latest_revision and data.thing_id=thing.id "
+ " AND thing.key IN ("
+ self.sqljoin(xkeys, ", ")
+ ")"
)
def process(query):
yield "{\n"
for i, r in enumerate(self.db.query(query)):
if i:
yield ",\n"
yield simplejson.dumps(r.key)
yield ": "
yield process_json(r.key, r.data)
yield "}"
return "".join(process(query))
def versions(self, query):
what = 'thing.key, version.revision, transaction.*'
where = 'version.thing_id = thing.id AND version.transaction_id = transaction.id'
if config.get('use_machine_comment'):
what += ", version.machine_comment"
def get_id(key):
meta = self.get_metadata(key)
if meta:
return meta.id
else:
raise StopIteration
for c in query.conditions:
key, value = c.key, c.value
assert key in [
'key', 'type', 'author', 'ip', 'comment', 'created', 'bot',
'revision'
]
try:
if key == 'key':
key = 'thing_id'
value = get_id(value)
elif key == 'revision':
key = 'version.revision'
elif key == 'type':
key = 'thing.type'
value = get_id(value)
elif key == 'author':
key = 'transaction.author_id'
value = get_id(value)
else:
# 'bot' column is not enabled
if key == 'bot' and not config.use_bot_column:
bots = get_bot_users(self.db)
if value == True or str(value).lower() == "true":
where += web.reparam(
" AND transaction.author_id IN $bots",
{"bots": bots})
else:
where += web.reparam(
" AND (transaction.author_id NOT IN $bots OR transaction.author_id IS NULL)",
{"bots": bots})
continue
else:
key = 'transaction.' + key
except StopIteration:
# StopIteration is raised when a non-existing object is referred in the query
return []
where += web.reparam(' AND %s=$value' % key, locals())
sort = query.sort
if sort and sort.startswith('-'):
sort = sort[1:] + ' desc'
sort = 'transaction.' + sort
t = self.db.transaction()
if config.query_timeout:
self.db.query(
"SELECT set_config('statement_timeout', $query_timeout, false)",
dict(query_timeout=config.query_timeout))
result = self.db.select(['thing', 'version', 'transaction'],
what=what,
where=where,
offset=query.offset,
limit=query.limit,
order=sort)
result = result.list()
author_ids = list(set(r.author_id for r in result if r.author_id))
authors = self.get_metadata_list_from_ids(author_ids)
t.commit()
for r in result:
r.author = r.author_id and authors[r.author_id].key
return result
def get_metadata_list(self, keys):
where = web.reparam('site_id=$self.site_id', locals()) + web.sqlors(
'key=', keys)
result = self.db.select('thing', what='*', where=where).list()
d = dict((r.key, r) for r in result)
return d
def recentchanges(self, limit=100, offset=0, **kwargs):
tables = ["transaction t"]
what = "t.*"
order = "t.created DESC"
wheres = ["1 = 1"]
key = kwargs.pop("key", None)
if key is not None:
thing_id = self.get_thing_id(key)
if thing_id is None:
return []
else:
tables.append("version v")
wheres.append("v.transaction_id = t.id AND v.thing_id = $thing_id")
bot = kwargs.pop("bot", None)
if bot is not None:
bot_ids = [r.thing_id for r in self.db.query("SELECT thing_id FROM account WHERE bot='t'")] or [-1]
if bot is True or str(bot).lower() == "true":
wheres.append("t.author_id IN $bot_ids")
else:
wheres.append("(t.author_id NOT in $bot_ids OR t.author_id IS NULL)")
author = kwargs.pop("author", None)
if author is not None:
author_id = self.get_thing_id(author)
if not author_id:
# Unknown author. Implies no changes by him.
return []
else:
wheres.append("t.author_id=$author_id")
ip = kwargs.pop("ip", None)
if ip is not None:
if not self._is_valid_ipv4(ip):
return []
else:
# Don't include edits by logged in users when queried by ip.
wheres.append("t.ip = $ip AND t.author_id is NULL")
kind = kwargs.pop("kind", None)
if kind is not None:
wheres.append("t.action = $kind")
begin_date = kwargs.pop("begin_date", None)
if begin_date is not None:
wheres.append("t.created >= $begin_date")
end_date = kwargs.pop("end_date", None)
if end_date is not None:
# end_date is not included in the interval.
wheres.append("t.created < $end_date")
data = kwargs.pop("data", None)
if data:
for i, (k, v) in enumerate(data.items()):
t = "ti%d" % i
tables.append("transaction_index " + t)
q = "%s.tx_id = t.id AND %s.key=$k AND %s.value=$v" % (t, t, t)
# k, v are going to change in the next iter of the loop.
# bind the current values by calling reparam.
wheres.append(web.reparam(q, locals()))
wheres = list(self._process_wheres(wheres, locals()))
where = web.SQLQuery.join(wheres, " AND ")
rows = self.db.select(
tables, what=what, where=where, limit=limit, offset=offset, order=order, vars=locals()
).list()
authors = self.get_keys(row.author_id for row in rows)
return [self._process_transaction(row, authors) for row in rows]
def _process_wheres(self, wheres, vars):
for w in wheres:
if isinstance(w, basestring):
yield web.reparam(w, vars)
else:
yield w
def get_metadata_list(self, keys):
where = web.reparam('site_id=$self.site_id', locals()) + web.sqlors('key=', keys)
result = self.db.select('thing', what='*', where=where).list()
d = dict((r.key, r) for r in result)
return d
def versions(self, query):
what = "thing.key, version.revision, transaction.*"
where = "version.thing_id = thing.id AND version.transaction_id = transaction.id"
if config.get("use_machine_comment"):
what += ", version.machine_comment"
def get_id(key):
meta = self.get_metadata(key)
if meta:
return meta.id
else:
raise StopIteration
for c in query.conditions:
key, value = c.key, c.value
assert key in ["key", "type", "author", "ip", "comment", "created", "bot", "revision"]
try:
if key == "key":
key = "thing_id"
value = get_id(value)
elif key == "revision":
key = "version.revision"
elif key == "type":
key = "thing.type"
value = get_id(value)
elif key == "author":
key = "transaction.author_id"
value = get_id(value)
else:
# 'bot' column is not enabled
if key == "bot" and not config.use_bot_column:
bots = get_bot_users(self.db)
if value == True or str(value).lower() == "true":
where += web.reparam(" AND transaction.author_id IN $bots", {"bots": bots})
else:
where += web.reparam(
" AND (transaction.author_id NOT IN $bots OR transaction.author_id IS NULL)",
{"bots": bots},
)
continue
else:
key = "transaction." + key
except StopIteration:
# StopIteration is raised when a non-existing object is referred in the query
return []
where += web.reparam(" AND %s=$value" % key, locals())
sort = query.sort
if sort and sort.startswith("-"):
sort = sort[1:] + " desc"
sort = "transaction." + sort
t = self.db.transaction()
if config.query_timeout:
self.db.query(
"SELECT set_config('statement_timeout', $query_timeout, false)",
dict(query_timeout=config.query_timeout),
)
result = self.db.select(
["thing", "version", "transaction"],
what=what,
where=where,
offset=query.offset,
limit=query.limit,
order=sort,
)
result = result.list()
author_ids = list(set(r.author_id for r in result if r.author_id))
authors = self.get_metadata_list_from_ids(author_ids)
t.commit()
for r in result:
r.author = r.author_id and authors[r.author_id].key
return result
def get_metadata_list(self, keys):
where = web.reparam("site_id=$self.site_id", locals()) + web.sqlors("key=", keys)
result = self.db.select("thing", what="*", where=where).list()
d = dict((r.key, r) for r in result)
return d
def _process_wheres(self, wheres, vars):
for w in wheres:
if isinstance(w, string_types):
yield web.reparam(w, vars)
else:
yield w
def recentchanges(self, limit=100, offset=0, **kwargs):
tables = ['transaction t']
what = 't.*'
order = 't.created DESC'
wheres = ["1 = 1"]
if offset < 0:
offset = 0
key = kwargs.pop('key', None)
if key is not None:
thing_id = self.get_thing_id(key)
if thing_id is None:
return []
else:
tables.append('version v')
wheres.append('v.transaction_id = t.id AND v.thing_id = $thing_id')
bot = kwargs.pop('bot', None)
if bot is not None:
bot_ids = get_bot_users(self.db)
if bot is True or str(bot).lower() == "true":
wheres.append("t.author_id IN $bot_ids")
else:
wheres.append("(t.author_id NOT in $bot_ids OR t.author_id IS NULL)")
author = kwargs.pop('author', None)
if author is not None:
author_id = self.get_thing_id(author)
if not author_id:
# Unknown author. Implies no changes by him.
return []
else:
wheres.append("t.author_id=$author_id")
ip = kwargs.pop("ip", None)
if ip is not None:
if not self._is_valid_ipv4(ip):
return []
else:
# Don't include edits by logged in users when queried by ip.
wheres.append("t.ip = $ip AND t.author_id is NULL")
kind = kwargs.pop('kind', None)
if kind is not None:
wheres.append('t.action = $kind')
begin_date = kwargs.pop('begin_date', None)
if begin_date is not None:
wheres.append("t.created >= $begin_date")
end_date = kwargs.pop('end_date', None)
if end_date is not None:
# end_date is not included in the interval.
wheres.append("t.created < $end_date")
data = kwargs.pop('data', None)
if data:
for i, (k, v) in enumerate(data.items()):
t = 'ti%d' % i
tables.append('transaction_index ' + t)
q = '%s.tx_id = t.id AND %s.key=$k AND %s.value=$v' % (t, t, t)
# k, v are going to change in the next iter of the loop.
# bind the current values by calling reparam.
wheres.append(web.reparam(q, locals()))
wheres = list(self._process_wheres(wheres, locals()))
where = web.SQLQuery.join(wheres, " AND ")
rows = self.db.select(tables, what=what, where=where, limit=limit, offset=offset, order=order, vars=locals()).list()
authors = self.get_keys(row.author_id for row in rows)
return [self._process_transaction(row, authors) for row in rows]
