# -*- coding: utf-8 -*-
# vi:si:et:sw=4:sts=4:ts=4
##
## Copyright (C) 2013 Async Open Source <http://www.async.com.br>
## All rights reserved
##
## This program is free software; you can redistribute it and/or modify
## it under the terms of the GNU General Public License as published by
## the Free Software Foundation; either version 2 of the License, or
## (at your option) any later version.
##
## This program is distributed in the hope that it will be useful,
## but WITHOUT ANY WARRANTY; without even the implied warranty of
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
## GNU General Public License for more details.
##
## You should have received a copy of the GNU General Public License
## along with this program; if not, write to the Free Software
## Foundation, Inc., or visit: http://www.gnu.org/.
##
## Author(s): Stoq Team <stoq-devel@async.com.br>
##
"""Database expressions.
This contains a list of expressions that are unsupported by Storm.
Most of them are specific to PostgreSQL
"""
from storm.expr import (Expr, NamedFunc, PrefixExpr, SQL, ComparableExpr,
compile as expr_compile, FromExpr, Undef, EXPR,
is_safe_token, BinaryOper, SetExpr)
[docs]class Age(NamedFunc):
"""Given two datetimes, defines how the first is older than the second"""
# http://www.postgresql.org/docs/9.1/static/functions-datetime.html
__slots__ = ()
name = "AGE"
[docs]class Round(NamedFunc):
"""Rounds takes two arguments, first is numeric and second is integer,
first one is the number to be round and the second is the
requested precision.
"""
# See http://www.postgresql.org/docs/8.4/static/typeconv-func.html
__slots__ = ()
name = "ROUND"
[docs]class NullIf(NamedFunc):
"""Returns null if first argument matches second argument
e.g. NULLIF(x, '') could be written in python like (read None as NULL):
x if x != '' else None
"""
# See http://www.postgresql.org/docs/8.4/static/functions-conditional.html
__slots__ = ()
name = "NULLIF"
[docs]class Date(NamedFunc):
"""Extract the date part of a timestamp"""
# http://www.postgresql.org/docs/8.4/static/functions-datetime.html
# FIXME: This is actually an operator
__slots__ = ()
name = "DATE"
[docs]class DateTrunc(NamedFunc):
"""Truncates a part of a datetime"""
# http://www.postgresql.org/docs/9.1/static/functions-datetime.html
__slots__ = ()
name = "DATE_TRUNC"
[docs]class Distinct(NamedFunc):
# http://www.postgresql.org/docs/8.4/interactive/sql-select.html
# FIXME: This is actually an operator
__slots__ = ()
name = "DISTINCT"
[docs]class Field(SQL):
def __init__(self, table, column):
SQL.__init__(self, '%s.%s' % (table, column))
[docs]class Interval(PrefixExpr):
"""Defines a datetime interval"""
# http://www.postgresql.org/docs/9.1/static/functions-datetime.html
__slots__ = ()
prefix = "INTERVAL"
[docs]class TransactionTimestamp(NamedFunc):
"""Current date and time at the start of the current transaction"""
# http://www.postgresql.org/docs/8.4/static/functions-datetime.html
__slots__ = ()
name = "TRANSACTION_TIMESTAMP"
date = lambda: None # pylint
[docs]class StatementTimestamp(NamedFunc):
"""Current date and time at the start of the current statement"""
# http://www.postgresql.org/docs/8.4/static/functions-datetime.html
__slots__ = ()
name = "STATEMENT_TIMESTAMP"
date = lambda: None # pylint
[docs]class CharLength(NamedFunc):
"""The size of the char, just like len() in python"""
# http://www.postgresql.org/docs/8.4/static/functions-string.html
__slots__ = ()
name = "CHAR_LENGTH"
[docs]class LPad(NamedFunc):
"""Fill up the string to length by prepending the characters fill"""
# http://www.postgresql.org/docs/8.4/static/functions-string.html
__slots__ = ()
name = "LPAD"
[docs]class SplitPart(NamedFunc):
"""Split string on delimiter and return the given field"""
# http://www.postgresql.org/docs/8.4/static/functions-string.html
__slots__ = ()
name = "split_part"
[docs]class ArrayAgg(NamedFunc):
__slots__ = ()
name = "array_agg"
[docs]class Contains(BinaryOper):
__slots__ = ()
oper = " @> "
[docs]class IsContainedBy(BinaryOper):
__slots__ = ()
oper = " <@ "
@expr_compile.when(Contains, IsContainedBy)
[docs]def compile_contains(expr_compile, expr, state):
# We currently support only the first argument as a list.
expr1 = "ARRAY[%s]" % ",".join(expr_compile(i, state) for i in expr.expr1)
return '%s%s%s' % (expr1, expr.oper,
expr_compile(expr.expr2, state))
[docs]class NotIn(BinaryOper):
__slots__ = ()
oper = " NOT IN "
@expr_compile.when(NotIn)
[docs]def compile_in(expr_compile, expr, state):
expr1 = expr_compile(expr.expr1, state)
state.precedence = 0 # We're forcing parenthesis here.
return "%s %s (%s)" % (expr1, expr.oper, expr_compile(expr.expr2, state))
[docs]class StoqNormalizeString(NamedFunc):
"""This removes accents and other modifiers from a charater,
it's similar to NLKD normailzation in unicode, but it is run
inside the database.
Note, this is very slow and should be avoided.
In the future this will be replaced by fulltext search which
does normalization in a cheaper way.
"""
# See functions.sql
__slots__ = ()
name = "stoq_normalize_string"
[docs]class Case(ComparableExpr):
"""Works like a Python's if-then-else clause.
.. line-block::
CASE WHEN <condition> THEN <result>
[WHEN <condition> THEN <result>]
END
"""
# http://www.postgresql.org/docs/9.1/static/functions-conditional.html
# FIXME: Support several when clauses.
__slots__ = ("condition", "result", "else_")
prefix = "(unknown)"
def __init__(self, condition, result, else_=None):
self.condition = condition
self.result = result
self.else_ = else_
@expr_compile.when(Case)
[docs]def compile_case(compile, expr, state):
stmt = "CASE WHEN %s THEN %s" % (expr_compile(expr.condition, state),
expr_compile(expr.result, state))
if expr.else_ is not None:
stmt += ' ELSE ' + expr_compile(expr.else_, state)
stmt += ' END'
return stmt
[docs]class Trim(ComparableExpr):
"""Remove the longest string containing the given characters."""
# http://www.postgresql.org/docs/9.1/static/functions-string.html
__slots__ = ("op", "character", "column")
prefix = "(unknown)"
def __init__(self, op, character, column):
self.op = op
self.character = character
self.column = column
@expr_compile.when(Trim)
[docs]def compile_trim(compile, expr, state):
return "TRIM(%s %s FROM %s)" % (
expr.op,
expr_compile(expr.character, state),
expr_compile(expr.column, state))
[docs]class Concat(Expr):
"""Concatenates string together using the || operator."""
# http://www.postgresql.org/docs/8.4/static/functions-string.html
__slots__ = ("inputs",)
prefix = "(unknown)"
def __init__(self, *inputs):
self.inputs = inputs
@expr_compile.when(Concat)
[docs]def compile_concat(compile, expr, state):
return " || ".join(expr_compile(input_, state) for input_ in expr.inputs)
[docs]class Between(Expr):
"""Check if value is between start and end"""
# http://www.postgresql.org/docs/9.1/static/functions-comparison.html
__slots__ = ('value', 'start', 'end')
def __init__(self, value, start, end):
self.value = value
self.start = start
self.end = end
@expr_compile.when(Between)
[docs]def compile_between(compile, expr, state):
return ' %s BETWEEN %s AND %s ' % (
expr_compile(expr.value, state),
expr_compile(expr.start, state),
expr_compile(expr.end, state))
[docs]class GenerateSeries(FromExpr):
__slots__ = ('start', 'end', 'step')
def __init__(self, start, end, step=Undef):
self.start = start
self.end = end
self.step = step
@expr_compile.when(GenerateSeries)
[docs]def compile_generate_series(compile, expr, state):
state.push("context", EXPR)
if expr.step is Undef:
expr = 'generate_series(%s, %s)' % (expr_compile(expr.start, state),
expr_compile(expr.end, state))
else:
expr = 'generate_series(%s, %s, %s)' % (expr_compile(expr.start, state),
expr_compile(expr.end, state),
expr_compile(expr.step, state))
state.pop()
return expr
[docs]class UnionAll(SetExpr):
"""Union all the results
UNION is to UNION ALL what a python's set is to a list. UNION
will remove duplicates from the resulting rows while UNION ALL
will just join all data, making it a little bit faster but possibly
with more rows.
"""
__slots__ = ()
oper = " UNION ALL "
expr_compile.set_precedence(10, UnionAll)
[docs]def is_sql_identifier(identifier):
return (not expr_compile.is_reserved_word(identifier) and
is_safe_token(identifier))
[docs]class Over(ComparableExpr):
"""Check if value is between start and end
Usage:
Over(attr, [partitions], [order by])
e.g.:
Considering the query:
SELECT sale.total_amount OVER (ORDER BY sale.confirm_date DESC) FROM sale;
The window function gets described as:
Over(Sale.total_amount, [], [Desc(Sale.confirm_date)])
"""
__slots__ = ('attribute', 'partitions', 'orders')
def __init__(self, attribute, partitions=None, orders=None):
self.attribute = attribute
self.partitions = partitions
self.orders = orders
@expr_compile.when(Over)
[docs]def compile_over(compile, expr, state):
result = ' %s OVER (' % expr_compile(expr.attribute, state)
if expr.partitions:
partitions = ', '.join(expr_compile(i, state) for i in expr.partitions)
result += 'PARTITION BY %s ' % partitions
if expr.orders:
orders = ', '.join(expr_compile(i, state) for i in expr.orders)
result += 'ORDER BY %s ' % orders
result += ')'
return result