def _rewriter_table(self):
rewriter_table = sql.default_rewriter_table()
#
# Timestamp binary minus operator.
#
rewriter_table[Prototype("-", (Timestamp, Timestamp), Real)] = \
lambda arg0, arg1: "EXTRACT(EPOCH FROM (%s) - (%s))" % (arg0, arg1)
#
# Functions.
#
rewriter_table[Prototype("covers", (Geometry, Geometry), Boolean)] = \
sql.binary_function_rewriter("ST_Covers")
rewriter_table[Prototype("intersects", (Geometry, Geometry), Boolean)] = \
sql.binary_function_rewriter("ST_Intersects")
rewriter_table[Prototype("is_source_of", (UUID,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE source_uuid = %s.uuid AND uuid = (%s))" % \
(self._link_table_name, self._core_table_name, arg0)
rewriter_table[Prototype("is_derived_from", (UUID,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE uuid = %s.uuid AND source_uuid = (%s))" % \
(self._link_table_name, self._core_table_name, arg0)
rewriter_table[Prototype("has_tag", (Text,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE uuid = %s.uuid AND tag = (%s))" % \
(self._tag_table_name, self._core_table_name, arg0)
rewriter_table[Prototype("now", (), Timestamp)] = \
sql.as_is("now() AT TIME ZONE 'UTC'")
return rewriter_table
def _rewriter_table(self):
rewriter_table = sql.default_rewriter_table()
#
# Timestamp binary minus operator.
#
rewriter_table[Prototype("-", (Timestamp, Timestamp), Real)] = \
lambda arg0, arg1: "(julianday(%s) - julianday(%s)) * 86400.0" % (arg0, arg1)
#
# Enable escape sequences with the LIKE operator
#
rewriter_table[Prototype("~=", (Text, Text), Boolean)] = \
lambda arg0, arg1: "(%s) LIKE (%s) ESCAPE '\\'" % (arg0, arg1)
#
# Functions.
#
rewriter_table[Prototype("covers", (Geometry, Geometry), Boolean)] = \
sql.binary_function_rewriter("ST_Covers")
rewriter_table[Prototype("intersects", (Geometry, Geometry), Boolean)] = \
sql.binary_function_rewriter("ST_Intersects")
rewriter_table[Prototype("is_source_of", (UUID,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE source_uuid = %s.uuid AND uuid = (%s))" % \
(self._link_table_name, self._core_table_name, arg0)
rewriter_table[Prototype("is_derived_from", (UUID,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE uuid = %s.uuid AND source_uuid = (%s))" % \
(self._link_table_name, self._core_table_name, arg0)
rewriter_table[Prototype("has_tag", (Text,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE uuid = %s.uuid AND tag = (%s))" % \
(self._tag_table_name, self._core_table_name, arg0)
rewriter_table[Prototype("now", (), Timestamp)] = \
sql.as_is("datetime(\"now\")")
return rewriter_table
def visit_FunctionCall(self, visitable):
# Resolve the type of the function arguments.
for argument in visitable.arguments:
self.visit(argument)
prototype = Prototype(visitable.name, [argument.type for argument in visitable.arguments])
try:
prototypes = function_table.resolve(prototype)
except KeyError:
prototypes = []
if not prototypes:
raise Error("undefined function: \"%s\"" % prototype)
if len(prototypes) > 1:
raise InternalError("cannot uniquely resolve function: \"%s\"" % prototype)
prototype = prototypes[0]
visitable.prototype = prototype
visitable.type = prototype.return_type
from muninn.exceptions import *
from muninn.function import Prototype, FunctionTable
from muninn.schema import *
from muninn.visitor import Visitor
#
# Table of all supported operators and functions
#
type_map = {
UUID: Boolean,
}
function_table = FunctionTable(type_map=type_map)
#
# Logical operators
#
function_table.add(Prototype("not", (Boolean, ), Boolean))
function_table.add(Prototype("and", (Boolean, Boolean), Boolean))
function_table.add(Prototype("or", (Boolean, Boolean), Boolean))
#
# Membership operators
#
function_table.add(Prototype("in", (Integer, Sequence), Boolean))
function_table.add(Prototype("in", (Long, Sequence), Boolean))
function_table.add(Prototype("in", (Real, Sequence), Boolean))
function_table.add(Prototype("in", (Text, Sequence), Boolean))
function_table.add(Prototype("not in", (Integer, Sequence), Boolean))
function_table.add(Prototype("not in", (Long, Sequence), Boolean))
function_table.add(Prototype("not in", (Real, Sequence), Boolean))
function_table.add(Prototype("not in", (Text, Sequence), Boolean))
#
# Comparison operators
def default_rewriter_table():
#
# Table of all supported operators and functions that can be rewritten in standard SQL and without backend specific
# knowledge. NB. This is a subset of the set of all supported operators and functions. Database specific backends
# should add custom implementations of the operators and functions not implemented here.
#
rewriter_table = {}
#
# Logical operators
#
rewriter_table[Prototype("not", (Boolean,), Boolean)] = unary_operator_rewriter("NOT")
rewriter_table[Prototype("and", (Boolean, Boolean), Boolean)] = binary_operator_rewriter("AND")
rewriter_table[Prototype("or", (Boolean, Boolean), Boolean)] = binary_operator_rewriter("OR")
#
# Membership operators
#
in_rewriter = membership_operator_rewriter("in")
rewriter_table[Prototype("in", (Integer, Sequence), Boolean)] = in_rewriter
rewriter_table[Prototype("in", (Long, Sequence), Boolean)] = in_rewriter
rewriter_table[Prototype("in", (Real, Sequence), Boolean)] = in_rewriter
rewriter_table[Prototype("in", (Text, Sequence), Boolean)] = in_rewriter
not_in_rewriter = membership_operator_rewriter("not in")
rewriter_table[Prototype("not in", (Integer, Sequence), Boolean)] = not_in_rewriter
rewriter_table[Prototype("not in", (Long, Sequence), Boolean)] = not_in_rewriter
rewriter_table[Prototype("not in", (Real, Sequence), Boolean)] = not_in_rewriter
rewriter_table[Prototype("not in", (Text, Sequence), Boolean)] = not_in_rewriter
#
# Comparison operators
#
eq_rewriter = binary_operator_rewriter("=")
rewriter_table[Prototype("==", (Long, Long), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (Long, Integer), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (Integer, Long), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (Integer, Integer), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (Real, Real), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (Real, Long), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (Long, Real), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (Real, Integer), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (Integer, Real), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (Boolean, Boolean), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (Text, Text), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (Timestamp, Timestamp), Boolean)] = eq_rewriter
rewriter_table[Prototype("==", (UUID, UUID), Boolean)] = eq_rewriter
ne_rewriter = binary_operator_rewriter("!=")
rewriter_table[Prototype("!=", (Long, Long), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (Long, Integer), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (Integer, Long), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (Integer, Integer), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (Real, Real), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (Real, Long), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (Long, Real), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (Real, Integer), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (Integer, Real), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (Boolean, Boolean), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (Text, Text), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (Timestamp, Timestamp), Boolean)] = ne_rewriter
rewriter_table[Prototype("!=", (UUID, UUID), Boolean)] = ne_rewriter
lt_rewriter = binary_operator_rewriter("<")
rewriter_table[Prototype("<", (Long, Long), Boolean)] = lt_rewriter
rewriter_table[Prototype("<", (Long, Integer), Boolean)] = lt_rewriter
rewriter_table[Prototype("<", (Integer, Long), Boolean)] = lt_rewriter
rewriter_table[Prototype("<", (Integer, Integer), Boolean)] = lt_rewriter
rewriter_table[Prototype("<", (Real, Real), Boolean)] = lt_rewriter
rewriter_table[Prototype("<", (Real, Long), Boolean)] = lt_rewriter
rewriter_table[Prototype("<", (Long, Real), Boolean)] = lt_rewriter
rewriter_table[Prototype("<", (Real, Integer), Boolean)] = lt_rewriter
rewriter_table[Prototype("<", (Integer, Real), Boolean)] = lt_rewriter
rewriter_table[Prototype("<", (Text, Text), Boolean)] = lt_rewriter
rewriter_table[Prototype("<", (Timestamp, Timestamp), Boolean)] = lt_rewriter
gt_rewriter = binary_operator_rewriter(">")
rewriter_table[Prototype(">", (Long, Long), Boolean)] = gt_rewriter
rewriter_table[Prototype(">", (Long, Integer), Boolean)] = gt_rewriter
rewriter_table[Prototype(">", (Integer, Long), Boolean)] = gt_rewriter
rewriter_table[Prototype(">", (Integer, Integer), Boolean)] = gt_rewriter
rewriter_table[Prototype(">", (Real, Real), Boolean)] = gt_rewriter
rewriter_table[Prototype(">", (Real, Long), Boolean)] = gt_rewriter
rewriter_table[Prototype(">", (Long, Real), Boolean)] = gt_rewriter
rewriter_table[Prototype(">", (Real, Integer), Boolean)] = gt_rewriter
rewriter_table[Prototype(">", (Integer, Real), Boolean)] = gt_rewriter
rewriter_table[Prototype(">", (Text, Text), Boolean)] = gt_rewriter
rewriter_table[Prototype(">", (Timestamp, Timestamp), Boolean)] = gt_rewriter
le_rewriter = binary_operator_rewriter("<=")
rewriter_table[Prototype("<=", (Long, Long), Boolean)] = le_rewriter
rewriter_table[Prototype("<=", (Long, Integer), Boolean)] = le_rewriter
rewriter_table[Prototype("<=", (Integer, Long), Boolean)] = le_rewriter
rewriter_table[Prototype("<=", (Integer, Integer), Boolean)] = le_rewriter
rewriter_table[Prototype("<=", (Real, Real), Boolean)] = le_rewriter
rewriter_table[Prototype("<=", (Real, Long), Boolean)] = le_rewriter
rewriter_table[Prototype("<=", (Long, Real), Boolean)] = le_rewriter
rewriter_table[Prototype("<=", (Real, Integer), Boolean)] = le_rewriter
rewriter_table[Prototype("<=", (Integer, Real), Boolean)] = le_rewriter
rewriter_table[Prototype("<=", (Text, Text), Boolean)] = le_rewriter
rewriter_table[Prototype("<=", (Timestamp, Timestamp), Boolean)] = le_rewriter
ge_rewriter = binary_operator_rewriter(">=")
rewriter_table[Prototype(">=", (Long, Long), Boolean)] = ge_rewriter
rewriter_table[Prototype(">=", (Long, Integer), Boolean)] = ge_rewriter
rewriter_table[Prototype(">=", (Integer, Long), Boolean)] = ge_rewriter
rewriter_table[Prototype(">=", (Integer, Integer), Boolean)] = ge_rewriter
rewriter_table[Prototype(">=", (Real, Real), Boolean)] = ge_rewriter
rewriter_table[Prototype(">=", (Real, Long), Boolean)] = ge_rewriter
rewriter_table[Prototype(">=", (Long, Real), Boolean)] = ge_rewriter
rewriter_table[Prototype(">=", (Real, Integer), Boolean)] = ge_rewriter
rewriter_table[Prototype(">=", (Integer, Real), Boolean)] = ge_rewriter
rewriter_table[Prototype(">=", (Text, Text), Boolean)] = ge_rewriter
rewriter_table[Prototype(">=", (Timestamp, Timestamp), Boolean)] = ge_rewriter
rewriter_table[Prototype("~=", (Text, Text), Boolean)] = binary_operator_rewriter("LIKE")
#
# Arithmetic operators.
#
plus_rewriter = unary_operator_rewriter("+")
rewriter_table[Prototype("+", (Long,), Long)] = plus_rewriter
rewriter_table[Prototype("+", (Integer,), Integer)] = plus_rewriter
rewriter_table[Prototype("+", (Real,), Real)] = plus_rewriter
minus_rewriter = unary_operator_rewriter("-")
rewriter_table[Prototype("-", (Long,), Long)] = minus_rewriter
rewriter_table[Prototype("-", (Integer,), Integer)] = minus_rewriter
rewriter_table[Prototype("-", (Real,), Real)] = minus_rewriter
add_rewriter = binary_operator_rewriter("+")
rewriter_table[Prototype("+", (Long, Long), Long)] = add_rewriter
rewriter_table[Prototype("+", (Long, Integer), Long)] = add_rewriter
rewriter_table[Prototype("+", (Integer, Long), Long)] = add_rewriter
rewriter_table[Prototype("+", (Integer, Integer), Integer)] = add_rewriter
rewriter_table[Prototype("+", (Real, Real), Real)] = add_rewriter
rewriter_table[Prototype("+", (Real, Long), Real)] = add_rewriter
rewriter_table[Prototype("+", (Long, Real), Real)] = add_rewriter
rewriter_table[Prototype("+", (Real, Integer), Real)] = add_rewriter
rewriter_table[Prototype("+", (Integer, Real), Real)] = add_rewriter
subtract_rewriter = binary_operator_rewriter("-")
rewriter_table[Prototype("-", (Long, Long), Long)] = subtract_rewriter
rewriter_table[Prototype("-", (Long, Integer), Long)] = subtract_rewriter
rewriter_table[Prototype("-", (Integer, Long), Long)] = subtract_rewriter
rewriter_table[Prototype("-", (Integer, Integer), Integer)] = subtract_rewriter
rewriter_table[Prototype("-", (Real, Real), Real)] = subtract_rewriter
rewriter_table[Prototype("-", (Real, Long), Real)] = subtract_rewriter
rewriter_table[Prototype("-", (Long, Real), Real)] = subtract_rewriter
rewriter_table[Prototype("-", (Real, Integer), Real)] = subtract_rewriter
rewriter_table[Prototype("-", (Integer, Real), Real)] = subtract_rewriter
multiply_rewriter = binary_operator_rewriter("*")
rewriter_table[Prototype("*", (Long, Long), Long)] = multiply_rewriter
rewriter_table[Prototype("*", (Long, Integer), Long)] = multiply_rewriter
rewriter_table[Prototype("*", (Integer, Long), Long)] = multiply_rewriter
rewriter_table[Prototype("*", (Integer, Integer), Integer)] = multiply_rewriter
rewriter_table[Prototype("*", (Real, Real), Real)] = multiply_rewriter
rewriter_table[Prototype("*", (Real, Long), Real)] = multiply_rewriter
rewriter_table[Prototype("*", (Long, Real), Real)] = multiply_rewriter
rewriter_table[Prototype("*", (Real, Integer), Real)] = multiply_rewriter
rewriter_table[Prototype("*", (Integer, Real), Real)] = multiply_rewriter
divide_rewriter = binary_operator_rewriter("/")
rewriter_table[Prototype("/", (Long, Long), Long)] = divide_rewriter
rewriter_table[Prototype("/", (Long, Integer), Long)] = divide_rewriter
rewriter_table[Prototype("/", (Integer, Long), Long)] = divide_rewriter
rewriter_table[Prototype("/", (Integer, Integer), Integer)] = divide_rewriter
rewriter_table[Prototype("/", (Real, Real), Real)] = divide_rewriter
rewriter_table[Prototype("/", (Real, Long), Real)] = divide_rewriter
rewriter_table[Prototype("/", (Long, Real), Real)] = divide_rewriter
rewriter_table[Prototype("/", (Real, Integer), Real)] = divide_rewriter
rewriter_table[Prototype("/", (Integer, Real), Real)] = divide_rewriter
#
# Functions.
#
rewriter_table[Prototype("covers", (Timestamp, Timestamp, Timestamp, Timestamp), Boolean)] = \
lambda left0, right0, left1, right1: "(%s) >= (%s) AND (%s) >= (%s) AND (%s) >= (%s) AND (%s) <= (%s)" % \
(right0, left0, right1, left1, left1, left0, right1, right0)
rewriter_table[Prototype("intersects", (Timestamp, Timestamp, Timestamp, Timestamp), Boolean)] = \
lambda left0, right0, left1, right1: "(%s) >= (%s) AND (%s) >= (%s) AND (%s) >= (%s) AND (%s) <= (%s)" % \
(right0, left0, right1, left1, right0, left1, left0, right1)
return rewriter_table
def build_summary_query(self, where='', parameters=None, aggregates=None, group_by=None, group_by_tag=False,
having=None, order_by=None):
aggregates = aggregates or []
group_by = group_by or []
order_by = order_by or []
if group_by_tag:
group_by = group_by + ['tag']
result_fields = group_by + ['count'] + aggregates
join_set = set(item.split('.')[0] for item in group_by)
# Parse the WHERE clause.
where_clause, where_parameters = '', {}
if where:
ast = parse_and_analyze(where, self._namespace_schemas, parameters)
visitor = _WhereExpressionVisitor(self._rewriter_table, self._column_name, self._named_placeholder)
where_expr, where_parameters, where_namespaces = visitor.do_visit(ast)
if where_expr:
join_set.update(where_namespaces)
where_clause = 'WHERE %s' % where_expr
# Generate the GROUP BY clause.
group_by_clause = ''
group_by_list = [str(i) for i in range(1, len(group_by) + 1)]
if group_by_list:
group_by_clause = 'GROUP BY %s' % ', '.join(group_by_list)
# Generate the HAVING clause
having_clause = ''
if having is not None:
ast = parse_and_analyze(having, self._namespace_schemas, parameters, having=True)
visitor = _WhereExpressionVisitor(self._rewriter_table, self._column_name, self._named_placeholder, visitor if where else None)
having_expr, having_parameters, _ = visitor.do_visit(ast)
where_parameters.update(having_parameters)
having_clause = 'HAVING %s' % having_expr
# Parse the ORDER BY clause.
order_by_clause = ''
order_by_list = []
for item in order_by:
direction = 'DESC' if item.startswith('-') else 'ASC'
name = item[1:] if item.startswith('+') or item.startswith('-') else item
Identifier(name, self._namespace_schemas) # check if the identifier is valid
if name not in result_fields:
raise Error("cannot order result by %r; field is not present in result" % name)
order_by_list.append('"%s" %s' % (name, direction))
order_by_list += [str(i) for i in range(1, len(group_by) + 1)]
if order_by_list:
order_by_clause = 'ORDER BY %s' % ', '.join(order_by_list)
# Generate the SELECT clause.
select_list = []
# group by fields
for item in group_by:
item = Identifier(item, self._namespace_schemas)
column_name = self._column_name(item.namespace, item.identifier)
group_by_functions = GROUP_BY_FUNCTIONS.get(item.muninn_type)
if not group_by_functions: # item.muninn_type not in (Text, Boolean, Long, Integer):
if item.muninn_type:
raise Error("property %r of type %r cannot be part of the group_by field specification" %
(item.property_name, item.muninn_type.name()))
else:
raise Error("property %r cannot be part of the group_by field specification" %
(item.property_name, ))
if item.subscript not in group_by_functions:
if item.subscript:
allowed_message = "; it can be one of %r" % group_by_functions if group_by_functions != [None] \
else ""
raise Error(("group field specification subscript %r of %r is not allowed" + allowed_message) %
(item.subscript, item.canonical))
else:
raise Error("property %r of type %r must specify a subscript (one of %r) to be part of the "
"group_by field specification" % (item.property_name, item.muninn_type.name(),
group_by_functions))
if item.subscript:
column_name = self._rewriter_property(column_name, item.subscript)
select_list.append('%s AS "%s"' % (column_name, item.canonical))
# aggregated fields
select_list.append('COUNT(*) AS count') # always aggregate row count
for item in aggregates:
item = Identifier(item, self._namespace_schemas)
join_set.add(item.namespace)
if not AGGREGATE_FUNCTIONS.get(item.muninn_type):
raise Error("property %r of type %r cannot be part of the summary field specification" %
(item.property_name, item.muninn_type.name()))
elif item.subscript not in AGGREGATE_FUNCTIONS[item.muninn_type]:
if item.subscript:
raise Error("summary field specification subscript %r of %r should be one of %r" %
(item.subscript, item.canonical, AGGREGATE_FUNCTIONS[item.muninn_type]))
else:
raise Error("summary field specification %r must specify a subscript (one of %r)" %
(item.canonical, AGGREGATE_FUNCTIONS[item.muninn_type]))
if item.property_name == 'core.validity_duration':
start_column = self._column_name(item.namespace, 'validity_start')
stop_column = self._column_name(item.namespace, 'validity_stop')
column_name = self._rewriter_table[Prototype('-', (Timestamp, Timestamp), Real)](stop_column,
start_column)
else:
column_name = self._column_name(item.namespace, item.identifier)
select_list.append('%s(%s) AS "%s"' % (item.subscript.upper(), column_name, item.canonical))
select_clause = 'SELECT %s' % ', '.join(select_list)
# Generate the FROM clause.
from_clause = 'FROM %s' % self._table_name('core')
join_set.discard('core')
for namespace in join_set:
from_clause = '%s LEFT JOIN %s USING (uuid)' % (from_clause, self._table_name(namespace))
# Generate the complete query.
query = '%s\n%s' % (select_clause, from_clause)
if where_clause:
query = '%s\n%s' % (query, where_clause)
if group_by_clause:
query = '%s\n%s' % (query, group_by_clause)
if having_clause:
query = '%s\n%s' % (query, having_clause)
if order_by_clause:
query = '%s\n%s' % (query, order_by_clause)
return query, where_parameters, result_fields
def _rewriter_table(self):
rewriter_table = sql.default_rewriter_table()
#
# Timestamp binary minus operator.
#
rewriter_table[Prototype("-", (Timestamp, Timestamp), Real)] = \
lambda arg0, arg1: "(julianday(%s) - julianday(%s)) * 86400.0" % (arg0, arg1)
#
# Enable escape sequences with the LIKE operator
#
rewriter_table[Prototype("~=", (Text, Text), Boolean)] = \
lambda arg0, arg1: "(%s) LIKE (%s) ESCAPE '\\'" % (arg0, arg1)
#
# Functions.
#
rewriter_table[Prototype("covers", (Geometry, Geometry), Boolean)] = \
lambda arg0, arg1: "(ST_Covers(%s, %s)=1)" % (arg0, arg1)
rewriter_table[Prototype("intersects", (Geometry, Geometry), Boolean)] = \
lambda arg0, arg1: "(ST_Intersects(%s, %s)=1)" % (arg0, arg1)
rewriter_table[Prototype("is_source_of", (UUID,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE source_uuid = %s.uuid AND uuid = (%s))" % \
(self._link_table_name, self._core_table_name, arg0)
def is_source_of_subquery(where_expr, where_namespaces):
joins = ''
for namespace in where_namespaces:
joins = "%s INNER JOIN %s USING (uuid)" % (
joins, self._table_name(namespace))
return "{core}.uuid in (SELECT {link}.source_uuid FROM {core} {joins} " \
"INNER JOIN {link} on {link}.uuid = {core}.uuid WHERE {where})".format(
core=self._core_table_name, link=self._link_table_name, joins=joins,
where=where_expr)
rewriter_table[Prototype("is_source_of", (Boolean, ),
Boolean)] = is_source_of_subquery
rewriter_table[Prototype("is_derived_from", (UUID,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE uuid = %s.uuid AND source_uuid = (%s))" % \
(self._link_table_name, self._core_table_name, arg0)
def is_derived_from_subquery(where_expr, where_namespaces):
joins = ''
for namespace in where_namespaces:
joins = "%s INNER JOIN %s USING (uuid)" % (
joins, self._table_name(namespace))
return "{core}.uuid in (SELECT {link}.uuid FROM {core} {joins} " \
"INNER JOIN {link} on {link}.source_uuid = {core}.uuid WHERE {where})".format(
core=self._core_table_name, link=self._link_table_name, joins=joins,
where=where_expr)
rewriter_table[Prototype("is_derived_from", (Boolean, ),
Boolean)] = is_derived_from_subquery
rewriter_table[Prototype("has_tag", (Text,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE uuid = %s.uuid AND tag = (%s))" % \
(self._tag_table_name, self._core_table_name, arg0)
rewriter_table[Prototype("now", (), Timestamp)] = \
sql.as_is("datetime(\"now\")")
def is_defined_rewriter(arg):
namespace_name = arg.split('.')
if len(namespace_name) == 1:
return 'EXISTS (SELECT 1 FROM %s WHERE uuid = %s.uuid)' % \
(arg, self._core_table_name)
else:
return "(%s) IS NOT NULL" % arg
rewriter_table[Prototype("is_defined", (Long, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Integer, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Real, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Boolean, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Text, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Namespace, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Timestamp, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (UUID, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Geometry, ),
Boolean)] = is_defined_rewriter
return rewriter_table
def _rewriter_table(self):
rewriter_table = sql.default_rewriter_table()
#
# Timestamp binary minus operator.
#
rewriter_table[Prototype("-", (Timestamp, Timestamp), Real)] = \
lambda arg0, arg1: "EXTRACT(EPOCH FROM (%s) - (%s))" % (arg0, arg1)
#
# Functions.
#
rewriter_table[Prototype("covers", (Geometry, Geometry), Boolean)] = \
sql.binary_function_rewriter("ST_Covers")
rewriter_table[Prototype("intersects", (Geometry, Geometry), Boolean)] = \
sql.binary_function_rewriter("ST_Intersects")
rewriter_table[Prototype("is_source_of", (UUID,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE source_uuid = %s.uuid AND uuid = (%s))" % \
(self._link_table_name, self._core_table_name, arg0)
def is_source_of_subquery(where_expr, where_namespaces):
joins = ''
for namespace in where_namespaces:
joins = "%s INNER JOIN %s USING (uuid)" % (
joins, self._table_name(namespace))
return "{core}.uuid in (SELECT {link}.source_uuid FROM {core} {joins} " \
"INNER JOIN {link} on {link}.uuid = {core}.uuid WHERE {where})".format(
core=self._core_table_name, link=self._link_table_name, joins=joins,
where=where_expr)
rewriter_table[Prototype("is_source_of", (Boolean, ),
Boolean)] = is_source_of_subquery
rewriter_table[Prototype("is_derived_from", (UUID,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE uuid = %s.uuid AND source_uuid = (%s))" % \
(self._link_table_name, self._core_table_name, arg0)
def is_derived_from_subquery(where_expr, where_namespaces):
joins = ''
for namespace in where_namespaces:
joins = "%s INNER JOIN %s USING (uuid)" % (
joins, self._table_name(namespace))
return "{core}.uuid in (SELECT {link}.uuid FROM {core} {joins} " \
"INNER JOIN {link} on {link}.source_uuid = {core}.uuid WHERE {where})".format(
core=self._core_table_name, link=self._link_table_name, joins=joins,
where=where_expr)
rewriter_table[Prototype("is_derived_from", (Boolean, ),
Boolean)] = is_derived_from_subquery
rewriter_table[Prototype("has_tag", (Text,), Boolean)] = \
lambda arg0: "EXISTS (SELECT 1 FROM %s WHERE uuid = %s.uuid AND tag = (%s))" % \
(self._tag_table_name, self._core_table_name, arg0)
rewriter_table[Prototype("now", (), Timestamp)] = \
sql.as_is("now() AT TIME ZONE 'UTC'")
def is_defined_rewriter(arg):
namespace_name = arg.split('.')
if len(namespace_name) == 1:
return 'EXISTS (SELECT 1 FROM %s WHERE uuid = %s.uuid)' % \
(self._table_name(arg), self._core_table_name)
else:
return "(%s) IS NOT NULL" % arg
rewriter_table[Prototype("is_defined", (Long, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Integer, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Real, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Boolean, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Text, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Namespace, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Timestamp, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (UUID, ),
Boolean)] = is_defined_rewriter
rewriter_table[Prototype("is_defined", (Geometry, ),
Boolean)] = is_defined_rewriter
return rewriter_table
def build_summary_query(self,
where='',
parameters=None,
aggregates=None,
group_by=None,
group_by_tag=False,
order_by=None):
# Namespaces that appear in the "where" expression are combined via inner joins. This ensures that only those
# products that actually have a defined value for a given attribute will be considered by the "where"
# expression. This also means that products that do not occur in all of the namespaces referred to in the
# "where" expression will be ignored.
#
# Other namespaces are combined via (left) outer joins, with the core namespace as the leftmost namespace. This
# ensures that attributes will be returned of any product that occurs in zero or more of the requested
# namespaces.
aggregates = aggregates or []
if group_by_tag:
group_by = group_by + ['tag']
result_fields = group_by + ['count'] + aggregates
outer_join_set, inner_join_set = set(
item.split('.')[0] for item in group_by), set()
# Parse the WHERE clause.
where_clause, where_parameters = '', {}
if where:
ast = parse_and_analyze(where, self._namespace_schemas, parameters)
visitor = _WhereExpressionVisitor(self._rewriter_table,
self._column_name,
self._named_placeholder)
where_expr, where_parameters, where_namespaces = visitor.visit(ast)
if where_expr:
inner_join_set.update(where_namespaces)
where_clause = 'WHERE %s' % where_expr
# Generate the GROUP BY clause.
group_by_clause = ''
group_by_list = [str(i) for i in range(1, len(group_by) + 1)]
if group_by_list:
group_by_clause = 'GROUP BY %s' % ', '.join(group_by_list)
# Parse the ORDER BY clause.
order_by_clause = ''
order_by_list = []
for item in order_by:
direction = 'DESC' if item.startswith('-') else 'ASC'
name = item[1:] if item.startswith('+') or item.startswith(
'-') else item
Identifier(
name,
self._namespace_schemas) # check if the identifier is valid
if name not in result_fields:
raise Error(
"cannot order result by %r; field is not present in result"
% name)
order_by_list.append('"%s" %s' % (name, direction))
order_by_list += [str(i) for i in range(1, len(group_by) + 1)]
if order_by_list:
order_by_clause = 'ORDER BY %s' % ', '.join(order_by_list)
# Generate the SELECT clause.
select_list = []
# group by fields
for item in group_by:
item = Identifier(item, self._namespace_schemas)
column_name = self._column_name(item.namespace, item.attribute)
group_by_functions = GROUP_BY_FUNCTIONS.get(item.muninn_type)
if not group_by_functions: # item.muninn_type not in (Text, Boolean, Long, Integer):
if item.muninn_type:
raise Error(
"property %r of type %r cannot be part of the group_by field specification"
% (item.property, item.muninn_type.name()))
else:
raise Error(
"property %r cannot be part of the group_by field specification"
% (item.property, ))
if item.subscript not in group_by_functions:
if item.subscript:
allowed_message = "; it can be one of %r" % group_by_functions if group_by_functions != [
None
] else ""
raise Error((
"group field specification subscript %r of %r is not allowed"
+ allowed_message) % (item.subscript, item.canonical))
else:
raise Error(
"property %r of type %r must specify a subscript (one of %r) to be part of the group_by field specification"
% (item.property, item.muninn_type.name(),
group_by_functions))
if item.subscript:
column_name = self._rewriter_property(column_name,
item.subscript)
select_list.append('%s AS "%s"' % (column_name, item.canonical))
# aggregated fields
select_list.append('COUNT(*) AS count') # always aggregate row count
for item in aggregates:
item = Identifier(item, self._namespace_schemas)
if not AGGREGATE_FUNCTIONS.get(item.muninn_type):
raise Error(
"property %r of type %r cannot be part of the summary field specification"
% (item.property, item.muninn_type.name()))
elif item.subscript not in AGGREGATE_FUNCTIONS[item.muninn_type]:
if item.subscript:
raise Error(
"summary field specification subscript %r of %r should be one of %r"
% (item.subscript, item.canonical,
AGGREGATE_FUNCTIONS[item.muninn_type]))
else:
raise Error(
"summary field specification %r must specify a subscript (one of %r)"
% (item.canonical,
AGGREGATE_FUNCTIONS[item.muninn_type]))
if item.property == 'core.validity_duration':
start_column = self._column_name(item.namespace,
'validity_start')
stop_column = self._column_name(item.namespace,
'validity_stop')
column_name = self._rewriter_table[Prototype(
'-', (Timestamp, Timestamp), Real)](stop_column,
start_column)
else:
column_name = self._column_name(item.namespace, item.attribute)
select_list.append(
'%s(%s) AS "%s"' %
(item.subscript.upper(), column_name, item.canonical))
select_clause = 'SELECT %s' % ', '.join(select_list)
# Generate the FROM clause.
from_clause = 'FROM %s' % self._table_name('core')
outer_join_set.discard('core')
inner_join_set.discard('core')
for namespace in outer_join_set - inner_join_set:
from_clause = '%s LEFT OUTER JOIN %s USING (uuid)' % (
from_clause, self._table_name(namespace))
for namespace in inner_join_set:
from_clause = '%s INNER JOIN %s USING (uuid)' % (
from_clause, self._table_name(namespace))
# Generate the complete query.
query = '%s\n%s' % (select_clause, from_clause)
if where_clause:
query = '%s\n%s' % (query, where_clause)
if group_by_clause:
query = '%s\n%s' % (query, group_by_clause)
if order_by_clause:
query = '%s\n%s' % (query, order_by_clause)
return query, where_parameters, result_fields