source: OpenRLabs-Git/deploy/rlabs-docker/web2py-rlabs/gluon/packages/dal/pydal/dialects/mssql.py

main
Last change on this file was 42bd667, checked in by David Fuertes <dfuertes@…>, 4 years ago

Historial Limpio
  • Property mode set to 100755
File size: 14.5 KB
Line 
1from .._compat import basestring
2from ..adapters.mssql import (
3    MSSQL,
4    MSSQLN,
5    MSSQL3,
6    MSSQL4,
7    MSSQL3N,
8    MSSQL4N,
9    Vertica,
10    Sybase,
11)
12from ..helpers.methods import varquote_aux
13from ..objects import Expression
14from .base import SQLDialect
15from . import dialects, sqltype_for
16
17
18@dialects.register_for(MSSQL)
19class MSSQLDialect(SQLDialect):
20    true = 1
21    false = 0
22    true_exp = "1=1"
23    false_exp = "1=0"
24    dt_sep = "T"
25
26    @sqltype_for("boolean")
27    def type_boolean(self):
28        return "BIT"
29
30    @sqltype_for("blob")
31    def type_blob(self):
32        return "IMAGE"
33
34    @sqltype_for("integer")
35    def type_integer(self):
36        return "INT"
37
38    @sqltype_for("bigint")
39    def type_bigint(self):
40        return "BIGINT"
41
42    @sqltype_for("double")
43    def type_double(self):
44        return "FLOAT"
45
46    @sqltype_for("date")
47    def type_date(self):
48        return "DATE"
49
50    @sqltype_for("time")
51    def type_time(self):
52        return "CHAR(8)"
53
54    @sqltype_for("datetime")
55    def type_datetime(self):
56        return "DATETIME"
57
58    @sqltype_for("id")
59    def type_id(self):
60        return "INT IDENTITY PRIMARY KEY"
61
62    @sqltype_for("reference")
63    def type_reference(self):
64        return (
65            "INT%(null)s%(unique)s, CONSTRAINT %(constraint_name)s "
66            + "FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s ON "
67            + "DELETE %(on_delete_action)s"
68        )
69
70    @sqltype_for("big-id")
71    def type_big_id(self):
72        return "BIGINT IDENTITY PRIMARY KEY"
73
74    @sqltype_for("big-reference")
75    def type_big_reference(self):
76        return (
77            "BIGINT%(null)s%(unique)s, CONSTRAINT %(constraint_name)s"
78            + " FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s "
79            + "ON DELETE %(on_delete_action)s"
80        )
81
82    @sqltype_for("reference FK")
83    def type_reference_fk(self):
84        return (
85            ", CONSTRAINT FK_%(constraint_name)s FOREIGN KEY "
86            + "(%(field_name)s) REFERENCES %(foreign_key)s ON DELETE "
87            + "%(on_delete_action)s"
88        )
89
90    @sqltype_for("reference TFK")
91    def type_reference_tfk(self):
92        return (
93            " CONSTRAINT FK_%(constraint_name)s_PK FOREIGN KEY "
94            + "(%(field_name)s) REFERENCES %(foreign_table)s "
95            + "(%(foreign_key)s) ON DELETE %(on_delete_action)s"
96        )
97
98    @sqltype_for("geometry")
99    def type_geometry(self):
100        return "geometry"
101
102    @sqltype_for("geography")
103    def type_geography(self):
104        return "geography"
105
106    def varquote(self, val):
107        return varquote_aux(val, "[%s]")
108
109    def update(self, table, values, where=None):
110        tablename = self.writing_alias(table)
111        whr = ""
112        if where:
113            whr = " %s" % self.where(where)
114        return "UPDATE %s SET %s FROM %s%s;" % (
115            table.sql_shortref,
116            values,
117            tablename,
118            whr,
119        )
120
121    def delete(self, table, where=None):
122        tablename = self.writing_alias(table)
123        whr = ""
124        if where:
125            whr = " %s" % self.where(where)
126        return "DELETE %s FROM %s%s;" % (table.sql_shortref, tablename, whr)
127
128    def select(
129        self,
130        fields,
131        tables,
132        where=None,
133        groupby=None,
134        having=None,
135        orderby=None,
136        limitby=None,
137        distinct=False,
138        for_update=False,
139    ):
140        dst, whr, grp, order, limit, upd = "", "", "", "", "", ""
141        if distinct is True:
142            dst = " DISTINCT"
143        elif distinct:
144            dst = " DISTINCT ON (%s)" % distinct
145        if where:
146            whr = " %s" % self.where(where)
147        if groupby:
148            grp = " GROUP BY %s" % groupby
149            if having:
150                grp += " HAVING %s" % having
151        if orderby:
152            order = " ORDER BY %s" % orderby
153        if limitby:
154            (lmin, lmax) = limitby
155            limit = " TOP %i" % lmax
156        if for_update:
157            upd = " FOR UPDATE"
158        return "SELECT%s%s %s FROM %s%s%s%s%s;" % (
159            dst,
160            limit,
161            fields,
162            tables,
163            whr,
164            grp,
165            order,
166            upd,
167        )
168
169    def left_join(self, val, query_env={}):
170        # Left join must always have an ON clause
171        if not isinstance(val, basestring):
172            val = self.expand(val, query_env=query_env)
173        return "LEFT OUTER JOIN %s" % val
174
175    def random(self):
176        return "NEWID()"
177
178    def cast(self, first, second, query_env={}):
179        # apparently no cast necessary in MSSQL
180        return first
181
182    def _mssql_like_normalizer(self, term):
183        term = term.replace("[", "[[]")
184        return term
185
186    def _like_escaper_default(self, term):
187        if isinstance(term, Expression):
188            return term
189        return self._mssql_like_normalizer(
190            super(MSSQLDialect, self)._like_escaper_default(term)
191        )
192
193    def concat(self, *items, **kwargs):
194        query_env = kwargs.get("query_env", {})
195        tmp = (self.expand(x, "string", query_env=query_env) for x in items)
196        return "(%s)" % " + ".join(tmp)
197
198    def regexp(self, first, second, query_env={}):
199        second = self.expand(second, "string", query_env=query_env)
200        second = second.replace("\\", "\\\\")
201        second = second.replace(r"%", r"\%").replace("*", "%").replace(".", "_")
202        return "(%s LIKE %s ESCAPE '\\')" % (
203            self.expand(first, query_env=query_env),
204            second,
205        )
206
207    def extract(self, first, what, query_env={}):
208        return "DATEPART(%s,%s)" % (what, self.expand(first, query_env=query_env))
209
210    def epoch(self, val, query_env={}):
211        return "DATEDIFF(second, '1970-01-01 00:00:00', %s)" % self.expand(
212            val, query_env=query_env
213        )
214
215    def length(self, val, query_env={}):
216        return "LEN(%s)" % self.expand(val, query_env=query_env)
217
218    def aggregate(self, first, what, query_env={}):
219        if what == "LENGTH":
220            what = "LEN"
221        return super(MSSQLDialect, self).aggregate(first, what, query_env)
222
223    @property
224    def allow_null(self):
225        return " NULL"
226
227    def substring(self, field, parameters, query_env={}):
228        return "SUBSTRING(%s,%s,%s)" % (
229            self.expand(field, query_env=query_env),
230            parameters[0],
231            parameters[1],
232        )
233
234    def primary_key(self, key):
235        return "PRIMARY KEY CLUSTERED (%s)" % key
236
237    def concat_add(self, tablename):
238        return "; ALTER TABLE %s ADD " % tablename
239
240    def drop_index(self, name, table):
241        return "DROP INDEX %s ON %s;" % (self.quote(name), table._rname)
242
243    def st_astext(self, first, query_env={}):
244        return "%s.STAsText()" % self.expand(first, query_env=query_env)
245
246    def st_contains(self, first, second, query_env={}):
247        return "%s.STContains(%s)=1" % (
248            self.expand(first, query_env=query_env),
249            self.expand(second, first.type, query_env=query_env),
250        )
251
252    def st_distance(self, first, second, query_env={}):
253        return "%s.STDistance(%s)" % (
254            self.expand(first, query_env=query_env),
255            self.expand(second, first.type, query_env=query_env),
256        )
257
258    def st_equals(self, first, second, query_env={}):
259        return "%s.STEquals(%s)=1" % (
260            self.expand(first, query_env=query_env),
261            self.expand(second, first.type, query_env=query_env),
262        )
263
264    def st_intersects(self, first, second, query_env={}):
265        return "%s.STIntersects(%s)=1" % (
266            self.expand(first, query_env=query_env),
267            self.expand(second, first.type, query_env=query_env),
268        )
269
270    def st_overlaps(self, first, second, query_env={}):
271        return "%s.STOverlaps(%s)=1" % (
272            self.expand(first, query_env=query_env),
273            self.expand(second, first.type, query_env=query_env),
274        )
275
276    def st_touches(self, first, second, query_env={}):
277        return "%s.STTouches(%s)=1" % (
278            self.expand(first, query_env=query_env),
279            self.expand(second, first.type, query_env=query_env),
280        )
281
282    def st_within(self, first, second, query_env={}):
283        return "%s.STWithin(%s)=1" % (
284            self.expand(first, query_env=query_env),
285            self.expand(second, first.type, query_env=query_env),
286        )
287
288
289@dialects.register_for(MSSQLN)
290class MSSQLNDialect(MSSQLDialect):
291    @sqltype_for("string")
292    def type_string(self):
293        return "NVARCHAR(%(length)s)"
294
295    @sqltype_for("text")
296    def type_text(self):
297        return "NTEXT"
298
299    def ilike(self, first, second, escape=None, query_env={}):
300        if isinstance(second, Expression):
301            second = self.expand(second, "string", query_env=query_env)
302        else:
303            second = self.expand(second, "string", query_env=query_env).lower()
304            if escape is None:
305                escape = "\\"
306                second = second.replace(escape, escape * 2)
307        if second.startswith("n'"):
308            second = "N'" + second[2:]
309        return "(%s LIKE %s ESCAPE '%s')" % (
310            self.lower(first, query_env),
311            second,
312            escape,
313        )
314
315
316@dialects.register_for(MSSQL3)
317class MSSQL3Dialect(MSSQLDialect):
318    @sqltype_for("text")
319    def type_text(self):
320        return "VARCHAR(MAX)"
321
322    @sqltype_for("time")
323    def type_time(self):
324        return "TIME(7)"
325
326    def _rebuild_select_for_limit(
327        self, fields, tables, dst, whr, grp, order, lmin, lmax
328    ):
329        f_outer = ["f_%s" % i for i in range(len(fields.split(",")))]
330        f_inner = [field for field in fields.split(", ")]
331        f_iproxy = ", ".join([self._as(o, n) for (o, n) in zip(f_inner, f_outer)])
332        f_oproxy = ", ".join(f_outer)
333        interp = (
334            "SELECT%s %s FROM ("
335            + "SELECT%s ROW_NUMBER() OVER (%s) AS w_row, %s FROM %s%s%s)"
336            + " TMP WHERE w_row BETWEEN %i and %i;"
337        )
338        return interp % (
339            dst,
340            f_oproxy,
341            dst,
342            order,
343            f_iproxy,
344            tables,
345            whr,
346            grp,
347            lmin,
348            lmax,
349        )
350
351    def select(
352        self,
353        fields,
354        tables,
355        where=None,
356        groupby=None,
357        having=None,
358        orderby=None,
359        limitby=None,
360        distinct=False,
361        for_update=False,
362    ):
363        dst, whr, grp, order, limit, offset, upd = "", "", "", "", "", "", ""
364        if distinct is True:
365            dst = " DISTINCT"
366        elif distinct:
367            dst = " DISTINCT ON (%s)" % distinct
368        if where:
369            whr = " %s" % self.where(where)
370        if groupby:
371            grp = " GROUP BY %s" % groupby
372            if having:
373                grp += " HAVING %s" % having
374        if orderby:
375            order = " ORDER BY %s" % orderby
376        if limitby:
377            (lmin, lmax) = limitby
378            if lmin == 0:
379                dst += " TOP %i" % lmax
380            else:
381                return self._rebuild_select_for_limit(
382                    fields, tables, dst, whr, grp, order, lmin, lmax
383                )
384        if for_update:
385            upd = " FOR UPDATE"
386        return "SELECT%s %s FROM %s%s%s%s%s%s%s;" % (
387            dst,
388            fields,
389            tables,
390            whr,
391            grp,
392            order,
393            limit,
394            offset,
395            upd,
396        )
397
398
399@dialects.register_for(MSSQL4)
400class MSSQL4Dialect(MSSQL3Dialect):
401    def select(
402        self,
403        fields,
404        tables,
405        where=None,
406        groupby=None,
407        having=None,
408        orderby=None,
409        limitby=None,
410        distinct=False,
411        for_update=False,
412    ):
413        dst, whr, grp, order, limit, offset, upd = "", "", "", "", "", "", ""
414        if distinct is True:
415            dst = " DISTINCT"
416        elif distinct:
417            dst = " DISTINCT ON (%s)" % distinct
418        if where:
419            whr = " %s" % self.where(where)
420        if groupby:
421            grp = " GROUP BY %s" % groupby
422            if having:
423                grp += " HAVING %s" % having
424        if orderby:
425            order = " ORDER BY %s" % orderby
426        if limitby:
427            (lmin, lmax) = limitby
428            if lmin == 0:
429                dst += " TOP %i" % lmax
430            else:
431                if not order:
432                    order = " ORDER BY %s" % self.random
433                offset = " OFFSET %i ROWS FETCH NEXT %i ROWS ONLY" % (
434                    lmin,
435                    (lmax - lmin),
436                )
437        if for_update:
438            upd = " FOR UPDATE"
439        return "SELECT%s %s FROM %s%s%s%s%s%s%s;" % (
440            dst,
441            fields,
442            tables,
443            whr,
444            grp,
445            order,
446            limit,
447            offset,
448            upd,
449        )
450
451
452@dialects.register_for(MSSQL3N)
453class MSSQL3NDialect(MSSQLNDialect, MSSQL3Dialect):
454    @sqltype_for("text")
455    def type_text(self):
456        return "NVARCHAR(MAX)"
457
458
459@dialects.register_for(MSSQL4N)
460class MSSQL4NDialect(MSSQLNDialect, MSSQL4Dialect):
461    @sqltype_for("text")
462    def type_text(self):
463        return "NVARCHAR(MAX)"
464
465
466@dialects.register_for(Vertica)
467class VerticaDialect(MSSQLDialect):
468    dt_sep = " "
469
470    @sqltype_for("boolean")
471    def type_boolean(self):
472        return "BOOLEAN"
473
474    @sqltype_for("text")
475    def type_text(self):
476        return "BYTEA"
477
478    @sqltype_for("json")
479    def type_json(self):
480        return self.types["string"]
481
482    @sqltype_for("blob")
483    def type_blob(self):
484        return "BYTEA"
485
486    @sqltype_for("double")
487    def type_double(self):
488        return "DOUBLE PRECISION"
489
490    @sqltype_for("time")
491    def type_time(self):
492        return "TIME"
493
494    @sqltype_for("id")
495    def type_id(self):
496        return "IDENTITY"
497
498    @sqltype_for("reference")
499    def type_reference(self):
500        return "INT REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s"
501
502    @sqltype_for("big-reference")
503    def type_big_reference(self):
504        return "BIGINT REFERENCES %(foreign_key)s ON DELETE" + " %(on_delete_action)s"
505
506    def extract(self, first, what, query_env={}):
507        return "DATE_PART('%s', TIMESTAMP %s)" % (
508            what,
509            self.expand(first, query_env=query_env),
510        )
511
512    def truncate(self, table, mode=""):
513        if mode:
514            mode = " %s" % mode
515        return ["TRUNCATE %s%s;" % (table._rname, mode)]
516
517    def select(self, *args, **kwargs):
518        return SQLDialect.select(self, *args, **kwargs)
519
520
521@dialects.register_for(Sybase)
522class SybaseDialect(MSSQLDialect):
523    @sqltype_for("string")
524    def type_string(self):
525        return "CHAR VARYING(%(length)s)"
526
527    @sqltype_for("date")
528    def type_date(self):
529        return "DATETIME"
Note: See TracBrowser for help on using the repository browser.