426 lines
14 KiB
Python
426 lines
14 KiB
Python
|
from .._compat import basestring
|
||
|
|
from ..adapters.mssql import MSSQL, MSSQLN, MSSQL3, MSSQL4, MSSQL3N, MSSQL4N, \
|
||
|
|
Vertica, Sybase
|
||
|
|
from ..helpers.methods import varquote_aux
|
||
|
|
from ..objects import Expression
|
||
|
|
from .base import SQLDialect
|
||
|
|
from . import dialects, sqltype_for
|
||
|
|
|
||
|
|
|
||
|
|
@dialects.register_for(MSSQL)
|
||
|
|
class MSSQLDialect(SQLDialect):
|
||
|
|
true = 1
|
||
|
|
false = 0
|
||
|
|
true_exp = '1=1'
|
||
|
|
false_exp = '1=0'
|
||
|
|
dt_sep = "T"
|
||
|
|
|
||
|
|
@sqltype_for('boolean')
|
||
|
|
def type_boolean(self):
|
||
|
|
return 'BIT'
|
||
|
|
|
||
|
|
@sqltype_for('blob')
|
||
|
|
def type_blob(self):
|
||
|
|
return 'IMAGE'
|
||
|
|
|
||
|
|
@sqltype_for('integer')
|
||
|
|
def type_integer(self):
|
||
|
|
return 'INT'
|
||
|
|
|
||
|
|
@sqltype_for('bigint')
|
||
|
|
def type_bigint(self):
|
||
|
|
return 'BIGINT'
|
||
|
|
|
||
|
|
@sqltype_for('double')
|
||
|
|
def type_double(self):
|
||
|
|
return 'FLOAT'
|
||
|
|
|
||
|
|
@sqltype_for('date')
|
||
|
|
def type_date(self):
|
||
|
|
return 'DATE'
|
||
|
|
|
||
|
|
@sqltype_for('time')
|
||
|
|
def type_time(self):
|
||
|
|
return 'CHAR(8)'
|
||
|
|
|
||
|
|
@sqltype_for('datetime')
|
||
|
|
def type_datetime(self):
|
||
|
|
return 'DATETIME'
|
||
|
|
|
||
|
|
@sqltype_for('id')
|
||
|
|
def type_id(self):
|
||
|
|
return 'INT IDENTITY PRIMARY KEY'
|
||
|
|
|
||
|
|
@sqltype_for('reference')
|
||
|
|
def type_reference(self):
|
||
|
|
return 'INT%(null)s%(unique)s, CONSTRAINT %(constraint_name)s ' + \
|
||
|
|
'FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s ON ' + \
|
||
|
|
'DELETE %(on_delete_action)s'
|
||
|
|
|
||
|
|
@sqltype_for('big-id')
|
||
|
|
def type_big_id(self):
|
||
|
|
return 'BIGINT IDENTITY PRIMARY KEY'
|
||
|
|
|
||
|
|
@sqltype_for('big-reference')
|
||
|
|
def type_big_reference(self):
|
||
|
|
return 'BIGINT%(null)s%(unique)s, CONSTRAINT %(constraint_name)s' + \
|
||
|
|
' FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s ' + \
|
||
|
|
'ON DELETE %(on_delete_action)s'
|
||
|
|
|
||
|
|
@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'
|
||
|
|
|
||
|
|
@sqltype_for('reference TFK')
|
||
|
|
def type_reference_tfk(self):
|
||
|
|
return ' CONSTRAINT FK_%(constraint_name)s_PK FOREIGN KEY ' + \
|
||
|
|
'(%(field_name)s) REFERENCES %(foreign_table)s ' + \
|
||
|
|
'(%(foreign_key)s) ON DELETE %(on_delete_action)s'
|
||
|
|
|
||
|
|
@sqltype_for('geometry')
|
||
|
|
def type_geometry(self):
|
||
|
|
return 'geometry'
|
||
|
|
|
||
|
|
@sqltype_for('geography')
|
||
|
|
def type_geography(self):
|
||
|
|
return 'geography'
|
||
|
|
|
||
|
|
def varquote(self, val):
|
||
|
|
return varquote_aux(val, '[%s]')
|
||
|
|
|
||
|
|
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 FROM %s%s;' % (
|
||
|
|
table.sql_shortref, values, tablename, whr)
|
||
|
|
|
||
|
|
def delete(self, table, where=None):
|
||
|
|
tablename = self.writing_alias(table)
|
||
|
|
whr = ''
|
||
|
|
if where:
|
||
|
|
whr = ' %s' % self.where(where)
|
||
|
|
return 'DELETE %s FROM %s%s;' % (table.sql_shortref, 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, 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 = ' TOP %i' % lmax
|
||
|
|
if for_update:
|
||
|
|
upd = ' FOR UPDATE'
|
||
|
|
return 'SELECT%s%s %s FROM %s%s%s%s%s;' % (
|
||
|
|
dst, limit, fields, tables, whr, grp, order, upd)
|
||
|
|
|
||
|
|
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 OUTER JOIN %s' % val
|
||
|
|
|
||
|
|
def random(self):
|
||
|
|
return 'NEWID()'
|
||
|
|
|
||
|
|
def cast(self, first, second, query_env={}):
|
||
|
|
# apparently no cast necessary in MSSQL
|
||
|
|
return first
|
||
|
|
|
||
|
|
def _mssql_like_normalizer(self, term):
|
||
|
|
term = term.replace('[', '[[]')
|
||
|
|
return term
|
||
|
|
|
||
|
|
def _like_escaper_default(self, term):
|
||
|
|
if isinstance(term, Expression):
|
||
|
|
return term
|
||
|
|
return self._mssql_like_normalizer(
|
||
|
|
super(MSSQLDialect, self)._like_escaper_default(term))
|
||
|
|
|
||
|
|
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 regexp(self, first, second, query_env={}):
|
||
|
|
second = self.expand(second, 'string', query_env=query_env)
|
||
|
|
second = second.replace('\\', '\\\\')
|
||
|
|
second = second.replace('%', '\%').replace('*', '%').replace('.', '_')
|
||
|
|
return "(%s LIKE %s ESCAPE '\\')" % (
|
||
|
|
self.expand(first, query_env=query_env), second)
|
||
|
|
|
||
|
|
def extract(self, first, what, query_env={}):
|
||
|
|
return "DATEPART(%s,%s)" % (what,
|
||
|
|
self.expand(first, query_env=query_env))
|
||
|
|
|
||
|
|
def epoch(self, val, query_env={}):
|
||
|
|
return "DATEDIFF(second, '1970-01-01 00:00:00', %s)" % \
|
||
|
|
self.expand(val, query_env=query_env)
|
||
|
|
|
||
|
|
def length(self, val, query_env={}):
|
||
|
|
return "LEN(%s)" % self.expand(val, query_env=query_env)
|
||
|
|
|
||
|
|
def aggregate(self, first, what, query_env={}):
|
||
|
|
if what == 'LENGTH':
|
||
|
|
what = 'LEN'
|
||
|
|
return super(MSSQLDialect, self).aggregate(first, what, query_env)
|
||
|
|
|
||
|
|
@property
|
||
|
|
def allow_null(self):
|
||
|
|
return ' NULL'
|
||
|
|
|
||
|
|
def substring(self, field, parameters, query_env={}):
|
||
|
|
return 'SUBSTRING(%s,%s,%s)' % (
|
||
|
|
self.expand(field, query_env=query_env), parameters[0],
|
||
|
|
parameters[1])
|
||
|
|
|
||
|
|
def primary_key(self, key):
|
||
|
|
return 'PRIMARY KEY CLUSTERED (%s)' % key
|
||
|
|
|
||
|
|
def concat_add(self, tablename):
|
||
|
|
return '; ALTER TABLE %s ADD ' % tablename
|
||
|
|
|
||
|
|
def drop_index(self, name, table):
|
||
|
|
return 'DROP INDEX %s ON %s;' % (self.quote(name), table._rname)
|
||
|
|
|
||
|
|
def st_astext(self, first, query_env={}):
|
||
|
|
return '%s.STAsText()' % self.expand(first, query_env=query_env)
|
||
|
|
|
||
|
|
def st_contains(self, first, second, query_env={}):
|
||
|
|
return '%s.STContains(%s)=1' % (
|
||
|
|
self.expand(first, query_env=query_env),
|
||
|
|
self.expand(second, first.type, query_env=query_env))
|
||
|
|
|
||
|
|
def st_distance(self, first, second, query_env={}):
|
||
|
|
return '%s.STDistance(%s)' % (
|
||
|
|
self.expand(first, query_env=query_env),
|
||
|
|
self.expand(second, first.type, query_env=query_env))
|
||
|
|
|
||
|
|
def st_equals(self, first, second, query_env={}):
|
||
|
|
return '%s.STEquals(%s)=1' % (
|
||
|
|
self.expand(first, query_env=query_env),
|
||
|
|
self.expand(second, first.type, query_env=query_env))
|
||
|
|
|
||
|
|
def st_intersects(self, first, second, query_env={}):
|
||
|
|
return '%s.STIntersects(%s)=1' % (
|
||
|
|
self.expand(first, query_env=query_env),
|
||
|
|
self.expand(second, first.type, query_env=query_env))
|
||
|
|
|
||
|
|
def st_overlaps(self, first, second, query_env={}):
|
||
|
|
return '%s.STOverlaps(%s)=1' % (
|
||
|
|
self.expand(first, query_env=query_env),
|
||
|
|
self.expand(second, first.type, query_env=query_env))
|
||
|
|
|
||
|
|
def st_touches(self, first, second, query_env={}):
|
||
|
|
return '%s.STTouches(%s)=1' % (
|
||
|
|
self.expand(first, query_env=query_env),
|
||
|
|
self.expand(second, first.type, query_env=query_env))
|
||
|
|
|
||
|
|
def st_within(self, first, second, query_env={}):
|
||
|
|
return '%s.STWithin(%s)=1' % (
|
||
|
|
self.expand(first, query_env=query_env),
|
||
|
|
self.expand(second, first.type, query_env=query_env))
|
||
|
|
|
||
|
|
|
||
|
|
@dialects.register_for(MSSQLN)
|
||
|
|
class MSSQLNDialect(MSSQLDialect):
|
||
|
|
@sqltype_for('string')
|
||
|
|
def type_string(self):
|
||
|
|
return 'NVARCHAR(%(length)s)'
|
||
|
|
|
||
|
|
@sqltype_for('text')
|
||
|
|
def type_text(self):
|
||
|
|
return 'NTEXT'
|
||
|
|
|
||
|
|
def ilike(self, first, second, escape=None, query_env={}):
|
||
|
|
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)
|
||
|
|
if second.startswith("n'"):
|
||
|
|
second = "N'" + second[2:]
|
||
|
|
return "(%s LIKE %s ESCAPE '%s')" % (
|
||
|
|
self.lower(first, query_env), second, escape)
|
||
|
|
|
||
|
|
|
||
|
|
@dialects.register_for(MSSQL3)
|
||
|
|
class MSSQL3Dialect(MSSQLDialect):
|
||
|
|
@sqltype_for('text')
|
||
|
|
def type_text(self):
|
||
|
|
return 'VARCHAR(MAX)'
|
||
|
|
|
||
|
|
@sqltype_for('time')
|
||
|
|
def type_time(self):
|
||
|
|
return 'TIME(7)'
|
||
|
|
|
||
|
|
def _rebuild_select_for_limit(self, fields, tables, dst, whr, grp, order,
|
||
|
|
lmin, lmax):
|
||
|
|
f_outer = ['f_%s' % i for i in range(len(fields.split(',')))]
|
||
|
|
f_inner = [field for field in fields.split(', ')]
|
||
|
|
f_iproxy = ', '.join([
|
||
|
|
self._as(o, n) for (o, n) in zip(f_inner, f_outer)])
|
||
|
|
f_oproxy = ', '.join(f_outer)
|
||
|
|
interp = 'SELECT%s %s FROM (' + \
|
||
|
|
'SELECT%s ROW_NUMBER() OVER (%s) AS w_row, %s FROM %s%s%s)' + \
|
||
|
|
' TMP WHERE w_row BETWEEN %i and %i;'
|
||
|
|
return interp % (
|
||
|
|
dst, f_oproxy, dst, order, f_iproxy, tables, whr, grp,
|
||
|
|
lmin, lmax
|
||
|
|
)
|
||
|
|
|
||
|
|
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
|
||
|
|
if lmin == 0:
|
||
|
|
dst += ' TOP %i' % lmax
|
||
|
|
else:
|
||
|
|
return self._rebuild_select_for_limit(
|
||
|
|
fields, tables, dst, whr, grp, order, lmin, lmax
|
||
|
|
)
|
||
|
|
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)
|
||
|
|
|
||
|
|
|
||
|
|
@dialects.register_for(MSSQL4)
|
||
|
|
class MSSQL4Dialect(MSSQL3Dialect):
|
||
|
|
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
|
||
|
|
if lmin == 0:
|
||
|
|
dst += ' TOP %i' % lmax
|
||
|
|
else:
|
||
|
|
if not order:
|
||
|
|
order = ' ORDER BY %s' % self.random
|
||
|
|
offset = ' OFFSET %i ROWS FETCH NEXT %i ROWS ONLY' % (
|
||
|
|
lmin, (lmax - 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)
|
||
|
|
|
||
|
|
|
||
|
|
@dialects.register_for(MSSQL3N)
|
||
|
|
class MSSQL3NDialect(MSSQLNDialect, MSSQL3Dialect):
|
||
|
|
@sqltype_for('text')
|
||
|
|
def type_text(self):
|
||
|
|
return 'NVARCHAR(MAX)'
|
||
|
|
|
||
|
|
|
||
|
|
@dialects.register_for(MSSQL4N)
|
||
|
|
class MSSQL4NDialect(MSSQLNDialect, MSSQL4Dialect):
|
||
|
|
@sqltype_for('text')
|
||
|
|
def type_text(self):
|
||
|
|
return 'NVARCHAR(MAX)'
|
||
|
|
|
||
|
|
|
||
|
|
@dialects.register_for(Vertica)
|
||
|
|
class VerticaDialect(MSSQLDialect):
|
||
|
|
dt_sep = ' '
|
||
|
|
|
||
|
|
@sqltype_for('boolean')
|
||
|
|
def type_boolean(self):
|
||
|
|
return 'BOOLEAN'
|
||
|
|
|
||
|
|
@sqltype_for('text')
|
||
|
|
def type_text(self):
|
||
|
|
return 'BYTEA'
|
||
|
|
|
||
|
|
@sqltype_for('json')
|
||
|
|
def type_json(self):
|
||
|
|
return self.types['string']
|
||
|
|
|
||
|
|
@sqltype_for('blob')
|
||
|
|
def type_blob(self):
|
||
|
|
return 'BYTEA'
|
||
|
|
|
||
|
|
@sqltype_for('double')
|
||
|
|
def type_double(self):
|
||
|
|
return 'DOUBLE PRECISION'
|
||
|
|
|
||
|
|
@sqltype_for('time')
|
||
|
|
def type_time(self):
|
||
|
|
return 'TIME'
|
||
|
|
|
||
|
|
@sqltype_for('id')
|
||
|
|
def type_id(self):
|
||
|
|
return 'IDENTITY'
|
||
|
|
|
||
|
|
@sqltype_for('reference')
|
||
|
|
def type_reference(self):
|
||
|
|
return 'INT REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s'
|
||
|
|
|
||
|
|
@sqltype_for('big-reference')
|
||
|
|
def type_big_reference(self):
|
||
|
|
return 'BIGINT REFERENCES %(foreign_key)s ON DELETE' + \
|
||
|
|
' %(on_delete_action)s'
|
||
|
|
|
||
|
|
def extract(self, first, what, query_env={}):
|
||
|
|
return "DATE_PART('%s', TIMESTAMP %s)" % (what,
|
||
|
|
self.expand(first, query_env=query_env))
|
||
|
|
|
||
|
|
def truncate(self, table, mode=''):
|
||
|
|
if mode:
|
||
|
|
mode = " %s" % mode
|
||
|
|
return ['TRUNCATE %s%s;' % (table._rname, mode)]
|
||
|
|
|
||
|
|
def select(self, *args, **kwargs):
|
||
|
|
return SQLDialect.select(self, *args, **kwargs)
|
||
|
|
|
||
|
|
|
||
|
|
@dialects.register_for(Sybase)
|
||
|
|
class SybaseDialect(MSSQLDialect):
|
||
|
|
@sqltype_for('string')
|
||
|
|
def type_string(self):
|
||
|
|
return 'CHAR VARYING(%(length)s)'
|
||
|
|
|
||
|
|
@sqltype_for('date')
|
||
|
|
def type_date(self):
|
||
|
|
return 'DATETIME'
|