SP/web2py/gluon/packages/dal/pydal/dialects/base.py

import datetime
from .._compat import integer_types, basestring
from ..adapters.base import SQLAdapter
from ..helpers.methods import use_common_filters
from ..objects import Expression, Field, Table, Select
from . import Dialect, dialects, sqltype_for

long = integer_types[-1]


class CommonDialect(Dialect):
    quote_template = '%s'

    def _force_bigints(self):
        if 'big-id' in self.types and 'reference' in self.types:
            self.types['id'] = self.types['big-id']
            self.types['reference'] = self.types['big-reference']

    def quote(self, val):
        return self.quote_template % val

    def varquote(self, val):
        return val

    def sequence_name(self, tablename):
        return self.quote('%s_sequence' % tablename)

    def trigger_name(self, tablename):
        return '%s_sequence' % tablename

    def coalesce_zero(self, val, query_env={}):
        return self.coalesce(val, [0], query_env)


@dialects.register_for(SQLAdapter)
class SQLDialect(CommonDialect):
    quote_template = '"%s"'
    true = "T"
    false = "F"
    true_exp = "1"
    false_exp = "0"
    dt_sep = " "

    @sqltype_for('string')
    def type_string(self):
        return 'VARCHAR(%(length)s)'

    @sqltype_for('boolean')
    def type_boolean(self):
        return 'CHAR(1)'

    @sqltype_for('text')
    def type_text(self):
        return 'TEXT'

    @sqltype_for('json')
    def type_json(self):
        return self.types['text']

    @sqltype_for('password')
    def type_password(self):
        return self.types['string']

    @sqltype_for('blob')
    def type_blob(self):
        return 'BLOB'

    @sqltype_for('upload')
    def type_upload(self):
        return self.types['string']

    @sqltype_for('integer')
    def type_integer(self):
        return 'INTEGER'

    @sqltype_for('bigint')
    def type_bigint(self):
        return self.types['integer']

    @sqltype_for('float')
    def type_float(self):
        return 'FLOAT'

    @sqltype_for('double')
    def type_double(self):
        return 'DOUBLE'

    @sqltype_for('decimal')
    def type_decimal(self):
        return 'NUMERIC(%(precision)s,%(scale)s)'

    @sqltype_for('date')
    def type_date(self):
        return 'DATE'

    @sqltype_for('time')
    def type_time(self):
        return 'TIME'

    @sqltype_for('datetime')
    def type_datetime(self):
        return 'TIMESTAMP'

    @sqltype_for('id')
    def type_id(self):
        return 'INTEGER PRIMARY KEY AUTOINCREMENT'

    @sqltype_for('reference')
    def type_reference(self):
        return 'INTEGER REFERENCES %(foreign_key)s ' + \
            'ON DELETE %(on_delete_action)s %(null)s %(unique)s'

    @sqltype_for('list:integer')
    def type_list_integer(self):
        return self.types['text']

    @sqltype_for('list:string')
    def type_list_string(self):
        return self.types['text']

    @sqltype_for('list:reference')
    def type_list_reference(self):
        return self.types['text']

    @sqltype_for('big-id')
    def type_big_id(self):
        return self.types['id']

    @sqltype_for('big-reference')
    def type_big_reference(self):
        return self.types['reference']

    @sqltype_for('reference FK')
    def type_reference_fk(self):
        return ', CONSTRAINT  "FK_%(constraint_name)s" FOREIGN KEY ' + \
            '(%(field_name)s) REFERENCES %(foreign_key)s ' + \
            'ON DELETE %(on_delete_action)s'

    def alias(self, original, new):
        return ('%s AS ' + self.quote_template) % (original, new)

    def insert(self, table, fields, values):
        return 'INSERT INTO %s(%s) VALUES (%s);' % (table, fields, values)

    def insert_empty(self, table):
        return 'INSERT INTO %s DEFAULT VALUES;' % table

    def where(self, query):
        return 'WHERE %s' % query

    def update(self, table, values, where=None):
        tablename = self.writing_alias(table)
        whr = ''
        if where:
            whr = ' %s' % self.where(where)
        return 'UPDATE %s SET %s%s;' % (tablename, values, whr)

    def delete(self, table, where=None):
        tablename = self.writing_alias(table)
        whr = ''
        if where:
            whr = ' %s' % self.where(where)
        return 'DELETE FROM %s%s;' % (tablename, whr)

    def select(self, fields, tables, where=None, groupby=None, having=None,
               orderby=None, limitby=None, distinct=False, for_update=False):
        dst, whr, grp, order, limit, offset, upd = '', '', '', '', '', '', ''
        if distinct is True:
            dst = ' DISTINCT'
        elif distinct:
            dst = ' DISTINCT ON (%s)' % distinct
        if where:
            whr = ' %s' % self.where(where)
        if groupby:
            grp = ' GROUP BY %s' % groupby
            if having:
                grp += ' HAVING %s' % having
        if orderby:
            order = ' ORDER BY %s' % orderby
        if limitby:
            (lmin, lmax) = limitby
            limit = ' LIMIT %i' % (lmax - lmin)
            offset = ' OFFSET %i' % lmin
        if for_update:
            upd = ' FOR UPDATE'
        return 'SELECT%s %s FROM %s%s%s%s%s%s%s;' % (
            dst, fields, tables, whr, grp, order, limit, offset, upd)

    def count(self, val, distinct=None, query_env={}):
        return ('COUNT(%s)' if not distinct else 'COUNT(DISTINCT %s)') % \
            self.expand(val, query_env=query_env)

    def join(self, val, query_env={}):
        if isinstance(val, (Table, Select)):
            val = val.query_name(query_env.get('parent_scope', []))
        elif not isinstance(val, basestring):
            val = self.expand(val, query_env=query_env)
        return 'JOIN %s' % val

    def left_join(self, val, query_env={}):
        # Left join must always have an ON clause
        if not isinstance(val, basestring):
            val = self.expand(val, query_env=query_env)
        return 'LEFT JOIN %s' % val

    def cross_join(self, val, query_env={}):
        if isinstance(val, (Table, Select)):
            val = val.query_name(query_env.get('parent_scope', []))
        elif not isinstance(val, basestring):
            val = self.expand(val, query_env=query_env)
        return 'CROSS JOIN %s' % val

    @property
    def random(self):
        return 'Random()'

    def _as(self, first, second, query_env={}):
        return '%s AS %s' % (self.expand(first, query_env=query_env), second)

    def cast(self, first, second, query_env={}):
        return 'CAST(%s)' % self._as(first, second, query_env)

    def _not(self, val, query_env={}):
        return '(NOT %s)' % self.expand(val, query_env=query_env)

    def _and(self, first, second, query_env={}):
        return '(%s AND %s)' % (self.expand(first, query_env=query_env),
            self.expand(second, query_env=query_env))

    def _or(self, first, second, query_env={}):
        return '(%s OR %s)' % (self.expand(first, query_env=query_env),
            self.expand(second, query_env=query_env))

    def belongs(self, first, second, query_env={}):
        ftype = first.type
        first = self.expand(first, query_env=query_env)
        if isinstance(second, str):
            return '(%s IN (%s))' % (first, second[:-1])
        elif isinstance(second, Select):
            if len(second._qfields) != 1:
                raise ValueError(
                    'Subquery in belongs() must select exactly 1 column')
            sub = second._compile(query_env.get('current_scope', []))[1][:-1]
            return '(%s IN (%s))' % (first, sub)
        if not second:
            return '(1=0)'
        items = ','.join(self.expand(item, ftype, query_env=query_env)
                for item in second)
        return '(%s IN (%s))' % (first, items)

    # def regexp(self, first, second):
    #     raise NotImplementedError

    def lower(self, val, query_env={}):
        return 'LOWER(%s)' % self.expand(val, query_env=query_env)

    def upper(self, first, query_env={}):
        return 'UPPER(%s)' % self.expand(first, query_env=query_env)

    def like(self, first, second, escape=None, query_env={}):
        """Case sensitive like operator"""
        if isinstance(second, Expression):
            second = self.expand(second, 'string', query_env=query_env)
        else:
            second = self.expand(second, 'string', query_env=query_env)
            if escape is None:
                escape = '\\'
                second = second.replace(escape, escape * 2)
        return "(%s LIKE %s ESCAPE '%s')" % (
            self.expand(first, query_env=query_env), second, escape)

    def ilike(self, first, second, escape=None, query_env={}):
        """Case insensitive like operator"""
        if isinstance(second, Expression):
            second = self.expand(second, 'string', query_env=query_env)
        else:
            second = self.expand(second, 'string', query_env=query_env).lower()
            if escape is None:
                escape = '\\'
                second = second.replace(escape, escape*2)
        return "(%s LIKE %s ESCAPE '%s')" % (
            self.lower(first, query_env=query_env), second, escape)

    def _like_escaper_default(self, term):
        if isinstance(term, Expression):
            return term
        term = term.replace('\\', '\\\\')
        term = term.replace('%', '\%').replace('_', '\_')
        return term

    def startswith(self, first, second, query_env={}):
        return "(%s LIKE %s ESCAPE '\\')" % (
            self.expand(first, query_env=query_env),
            self.expand(self._like_escaper_default(second)+'%', 'string',
                query_env=query_env))

    def endswith(self, first, second, query_env={}):
        return "(%s LIKE %s ESCAPE '\\')" % (
            self.expand(first, query_env=query_env),
            self.expand('%'+self._like_escaper_default(second), 'string',
                query_env=query_env))

    def replace(self, first, tup, query_env={}):
        second, third = tup
        return 'REPLACE(%s,%s,%s)' % (
            self.expand(first, 'string', query_env=query_env),
            self.expand(second, 'string', query_env=query_env),
            self.expand(third, 'string', query_env=query_env))

    def concat(self, *items, **kwargs):
        query_env = kwargs.get('query_env', {})
        tmp = (self.expand(x, 'string', query_env=query_env) for x in items)
        return '(%s)' % ' || '.join(tmp)

    def contains(self, first, second, case_sensitive=True, query_env={}):
        if first.type in ('string', 'text', 'json'):
            if isinstance(second, Expression):
                second = Expression(
                    second.db,
                    self.concat('%', Expression(
                        second.db, self.replace(second, ('%', '\%'),
                            query_env=query_env)), '%'))
            else:
                second = '%'+self._like_escaper_default(str(second))+'%'
        elif first.type.startswith('list:'):
            if isinstance(second, Expression):
                second = Expression(
                    second.db, self.concat('%|', Expression(
                        second.db, self.replace(Expression(
                            second.db, self.replace(
                                second, ('%', '\%'), query_env)),
                                ('|', '||'))), '|%'))
            else:
                second = str(second).replace('|', '||')
                second = '%|'+self._like_escaper_default(second)+'|%'
        op = case_sensitive and self.like or self.ilike
        return op(first, second, escape='\\', query_env=query_env)

    def eq(self, first, second=None, query_env={}):
        if second is None:
            return '(%s IS NULL)' % self.expand(first, query_env=query_env)
        return '(%s = %s)' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def ne(self, first, second=None, query_env={}):
        if second is None:
            return '(%s IS NOT NULL)' % self.expand(first, query_env=query_env)
        return '(%s <> %s)' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def lt(self, first, second=None, query_env={}):
        if second is None:
            raise RuntimeError("Cannot compare %s < None" % first)
        return '(%s < %s)' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def lte(self, first, second=None, query_env={}):
        if second is None:
            raise RuntimeError("Cannot compare %s <= None" % first)
        return '(%s <= %s)' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def gt(self, first, second=None, query_env={}):
        if second is None:
            raise RuntimeError("Cannot compare %s > None" % first)
        return '(%s > %s)' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def gte(self, first, second=None, query_env={}):
        if second is None:
            raise RuntimeError("Cannot compare %s >= None" % first)
        return '(%s >= %s)' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def _is_numerical(self, field_type):
        return field_type in \
            ('integer', 'float', 'double', 'bigint', 'boolean') or \
            field_type.startswith('decimal')

    def add(self, first, second, query_env={}):
        if self._is_numerical(first.type) or isinstance(first.type, Field):
            return '(%s + %s)' % (
                self.expand(first, query_env=query_env),
                self.expand(second, first.type, query_env=query_env))
        else:
            return self.concat(first, second, query_env=query_env)

    def sub(self, first, second, query_env={}):
        return '(%s - %s)' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def mul(self, first, second, query_env={}):
        return '(%s * %s)' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def div(self, first, second, query_env={}):
        return '(%s / %s)' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def mod(self, first, second, query_env={}):
        return '(%s %% %s)' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def on(self, first, second, query_env={}):
        table_rname = first.query_name(query_env.get('parent_scope', []))[0]
        if use_common_filters(second):
            second = self.adapter.common_filter(second, [first])
        return ('%s ON %s') % (table_rname,
            self.expand(second, query_env=query_env))

    def invert(self, first, query_env={}):
        return '%s DESC' % self.expand(first, query_env=query_env)

    def comma(self, first, second, query_env={}):
        return '%s, %s' % (self.expand(first, query_env=query_env),
            self.expand(second, query_env=query_env))

    def extract(self, first, what, query_env={}):
        return "EXTRACT(%s FROM %s)" % (what,
            self.expand(first, query_env=query_env))

    def epoch(self, val, query_env={}):
        return self.extract(val, 'epoch', query_env)

    def length(self, val, query_env={}):
        return "LENGTH(%s)" % self.expand(val, query_env=query_env)

    def aggregate(self, first, what, query_env={}):
        return "%s(%s)" % (what, self.expand(first, query_env=query_env))

    def not_null(self, default, field_type):
        return 'NOT NULL DEFAULT %s' % \
            self.adapter.represent(default, field_type)

    @property
    def allow_null(self):
        return ''

    def coalesce(self, first, second, query_env={}):
        expressions = [self.expand(first, query_env=query_env)] + \
            [self.expand(val, first.type, query_env=query_env)
                for val in second]
        return 'COALESCE(%s)' % ','.join(expressions)

    def raw(self, val, query_env={}):
        return val

    def substring(self, field, parameters, query_env={}):
        return 'SUBSTR(%s,%s,%s)' % (
            self.expand(field, query_env=query_env), parameters[0],
            parameters[1])

    def case(self, query, true_false, query_env={}):
        _types = {bool: 'boolean', int: 'integer', float: 'double'}
        return 'CASE WHEN %s THEN %s ELSE %s END' % (
            self.expand(query, query_env=query_env),
            self.adapter.represent(
                true_false[0], _types.get(type(true_false[0]), 'string')),
            self.adapter.represent(
                true_false[1], _types.get(type(true_false[1]), 'string')))

    def primary_key(self, key):
        return 'PRIMARY KEY(%s)' % key

    def drop_table(self, table, mode):
        return ['DROP TABLE %s;' % table._rname]

    def truncate(self, table, mode=''):
        if mode:
            mode = " %s" % mode
        return ['TRUNCATE TABLE %s%s;' % (table._rname, mode)]

    def create_index(self, name, table, expressions, unique=False):
        uniq = ' UNIQUE' if unique else ''
        with self.adapter.index_expander():
            rv = 'CREATE%s INDEX %s ON %s (%s);' % (
                uniq, self.quote(name), table._rname, ','.join(
                    self.expand(field) for field in expressions))
        return rv

    def drop_index(self, name, table):
        return 'DROP INDEX %s;' % self.quote(name)

    def constraint_name(self, table, fieldname):
        return '%s_%s__constraint' % (table, fieldname)

    def concat_add(self, tablename):
        return ', ADD '

    def writing_alias(self, table):
        return table.sql_fullref


class NoSQLDialect(CommonDialect):
    @sqltype_for('string')
    def type_string(self):
        return str

    @sqltype_for('boolean')
    def type_boolean(self):
        return bool

    @sqltype_for('text')
    def type_text(self):
        return str

    @sqltype_for('json')
    def type_json(self):
        return self.types['text']

    @sqltype_for('password')
    def type_password(self):
        return self.types['string']

    @sqltype_for('blob')
    def type_blob(self):
        return self.types['text']

    @sqltype_for('upload')
    def type_upload(self):
        return self.types['string']

    @sqltype_for('integer')
    def type_integer(self):
        return long

    @sqltype_for('bigint')
    def type_bigint(self):
        return self.types['integer']

    @sqltype_for('float')
    def type_float(self):
        return float

    @sqltype_for('double')
    def type_double(self):
        return self.types['float']

    @sqltype_for('date')
    def type_date(self):
        return datetime.date

    @sqltype_for('time')
    def type_time(self):
        return datetime.time

    @sqltype_for('datetime')
    def type_datetime(self):
        return datetime.datetime

    @sqltype_for('id')
    def type_id(self):
        return long

    @sqltype_for('reference')
    def type_reference(self):
        return long

    @sqltype_for('list:integer')
    def type_list_integer(self):
        return list

    @sqltype_for('list:string')
    def type_list_string(self):
        return list

    @sqltype_for('list:reference')
    def type_list_reference(self):
        return list

    def quote(self, val):
        return val