def count(self, qry=None):
if isinstance(qry, dict):
qry = self.convert_mongo_query(qry)
where = self._combine_filters(self._pj_get_list_filter(), qry)
count = sb.func.COUNT(sb.Field(self._pj_table, self._pj_id_column))
if where is None:
select = sb.Select(count)
else:
select = sb.Select(count, where=where)
with self._pj_jar.getCursor() as cur:
cur.execute(select, flush_hint=[self._pj_table])
return cur.fetchone()[0]
def __nonzero__(self):
where = self._pj_get_list_filter() or True
select = sb.Select(sb.func.COUNT(
sb.Field(self._pj_table, self._pj_id_column)), where=where)
with self._pj_jar.getCursor() as cur:
cur.execute(select, flush_hint=[self._pj_table])
return cur.fetchone()[0] > 0
def _get_doc(self, database, table, id):
tbl = sb.Table(table)
with self.getCursor() as cur:
cur.execute(sb.Select(sb.Field(table, '*'), tbl.id == id),
beacon="%s:%s:%s" % (database, table, id),
flush_hint=[table])
res = cur.fetchone()
return res['data'] if res is not None else None
def raw_find(self, qry=None, fields=(), **kwargs):
if isinstance(qry, dict):
qry = self.convert_mongo_query(qry)
qry = self._combine_filters(self._pj_get_list_filter(), qry)
# returning the cursor instead of fetchall at the cost of not closing it
# iterating over the cursor is better and this way we expose rowcount
# and friends
cur = self._pj_jar.getCursor()
fields = self._get_sb_fields(fields)
if qry is None:
cur.execute(sb.Select(fields, **kwargs),
flush_hint=[self._pj_table])
else:
cur.execute(sb.Select(fields, qry, **kwargs),
flush_hint=[self._pj_table])
return cur
def _get_doc_py_type(self, database, table, id):
tbl = sb.Table(table)
with self.getCursor() as cur:
datafld = sb.Field(table, 'data')
cur.execute(sb.Select(
sb.JGET(datafld, interfaces.PY_TYPE_ATTR_NAME), tbl.id == id),
beacon="%s:%s:%s" % (database, table, id))
res = cur.fetchone()
return res[0] if res is not None else None
def __getitem__(self, key):
with self._jar.getCursor(False) as cur:
tbl = sb.Table(self.table)
cur.execute(
sb.Select(sb.Field(self.table, 'dbref'), tbl.name == key))
if not cur.rowcount:
raise KeyError(key)
db, tbl, id = cur.fetchone()['dbref']
dbref = serialize.DBRef(tbl, id, db)
return self._jar.load(dbref)
def select(conn, query, print_sql=False, **kwargs):
try:
with conn.cursor() as cur:
sql = sb.sqlrepr(
sb.Select(sb.Field("ser", "data"), where=query, **kwargs),
'postgres')
if print_sql:
print('SQL> ', sql)
cur.execute(sql)
for e in cur.fetchall():
pprint(e[0])
finally:
conn.rollback()
def select(conn, query, print_sql=False):
try:
with conn.cursor() as cur:
converter = mquery.Converter("mq", "data")
sql = sb.sqlrepr(
sb.Select(sb.Field("mq", "data"),
where=converter.convert(query)), 'postgres')
if print_sql:
print('SQL> ', sql)
cur.execute(sql)
for e in cur.fetchall():
pprint.pprint(e[0])
finally:
conn.rollback()
def raw_find_one(self, qry=None, id=None):
if qry is None and id is None:
raise ValueError(
'Missing parameter, at least qry or id must be specified.')
if isinstance(qry, dict):
qry = self.convert_mongo_query(qry)
if id is not None:
qry = self._combine_filters(
self._pj_get_resolve_filter(), qry, self._pj_get_id_filter(id))
else:
qry = self._combine_filters(
self._pj_get_list_filter(), qry)
with self._pj_jar.getCursor() as cur:
cur.execute(sb.Select(sb.Field(self._pj_table, '*'), qry, limit=2),
flush_hint=[self._pj_table])
if cur.rowcount == 0:
return None
if cur.rowcount > 1:
raise ValueError('Multiple results returned.')
return cur.fetchone()
def operator_expr(self, operator, field, key, op2):
op1 = self.getField(field, key, json=True)
# some values, esp. datetime must go through PJ serialize
pjvalue = serialize.ObjectWriter(None).get_state(op2)
op2j = json.dumps(pjvalue, sort_keys=True)
if key == '_id':
op2j = op2
if operator == '$gt':
return op1 > op2j
if operator == '$lt':
return op1 < op2j
if operator == '$gte':
return op1 >= op2j
if operator == '$lte':
return op1 <= op2j
if operator == '$ne':
return op1 != op2j
if operator == '$in':
if not op2:
# SQL burps on empty lists with IN:
return False
# The $in operator in Mongo is pretty powerful, since it
# implements the classic "in" (contains) and the "any" operator.
# There is an optimization for strings:
# sb.JSONB_CONTAINS_ANY(op1, [unicode(e) for e in op2])
ops = [sb.IN(op1, [sb.JSONB(json.dumps(el)) for el in op2])]
ops += [sb.JSONB_SUPERSET(op1, json.dumps(el)) for el in op2]
return sb.OR(*ops)
if operator == '$nin':
if not op2:
# SQL burps on empty lists with IN:
return True
# The $nin operator in Mongo is pretty powerful, since it
# implements the classic "not in" (not contains) and the "not any"
# operator.
# There is an optimization for strings:
# sb.JSONB_CONTAINS_ANY(op1, [unicode(e) for e in op2])
ops = [
sb.IN(op1, [sb.JSONB(json.dumps(el)) for el in op2]),
]
ops += [sb.JSONB_SUPERSET(op1, json.dumps(el)) for el in op2]
return sb.NOT(sb.OR(*ops))
if operator == '$not':
# MongoDB's rationalization for this operator:
# it matches when op1 does not pass the condition
# or when op1 is not set at all.
return sb.OR(
sb.ISNULL(op1),
sb.NOT(
sb.AND(*(self.operator_expr(operator2, field, key, op3)
for operator2, op3 in op2.items()))))
if operator == '$size':
return sb.func.jsonb_array_length(op1) == op2
if operator == '$exists':
return sb.ISNOTNULL(op1) if op2 else sb.ISNULL(op1)
if operator == '$any':
return sb.JSONB_CONTAINS_ANY(op1, op2)
if operator == '$nany':
return sb.NOT(sb.JSONB_CONTAINS_ANY(op1, op2))
if operator == '$all':
return sb.JSONB_SUPERSET(op1, op2j)
if operator == '$elemMatch':
op1 = sb.NoTables(op1)
# SELECT data FROM tbl WHERE EXISTS (
# SELECT value
# FROM jsonb_array_elements(data -> 'arr')
# WHERE value < '3' AND value >= '2'
# );
return sb.EXISTS(
sb.Select(
['value'],
staticTables=[sb.func.jsonb_array_elements(op1)],
where=sb.NoTables(
sb.AND(*(sb.AND(*(self.operator_expr(
operator2, sb.SQLConstant('value'), key, op3)
for operator2, op3 in query.items()))
for query in op2)))))
if operator == '$startswith':
return sb.STARTSWITH(sb.TEXT(op1), op2)
if operator == '$regex':
return sb.RLIKE(sb.TEXT(op1), op2)
else:
raise ValueError("Unrecognized operator %s" % operator)
def keys(self):
with self._jar.getCursor(False) as cur:
cur.execute(sb.Select(sb.Field(self.table, 'name')))
return [doc['name'] for doc in cur.fetchall()]