source: OpenRLabs-Git/deploy/rlabs-docker/web2py-rlabs/gluon/packages/dal/pydal/dialects/base.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: 22.0 KB
Line 
1import datetime
2from .._compat import integer_types, basestring, string_types
3from ..adapters.base import SQLAdapter
4from ..helpers.methods import use_common_filters
5from ..objects import Expression, Field, Table, Select
6from . import Dialect, dialects, sqltype_for
7
8long = integer_types[-1]
9
10
11class CommonDialect(Dialect):
12    quote_template = "%s"
13
14    def _force_bigints(self):
15        if "big-id" in self.types and "reference" in self.types:
16            self.types["id"] = self.types["big-id"]
17            self.types["reference"] = self.types["big-reference"]
18
19    def quote(self, val):
20        return self.quote_template % val
21
22    def varquote(self, val):
23        return val
24
25    def sequence_name(self, tablename):
26        return self.quote("%s_sequence" % tablename)
27
28    def trigger_name(self, tablename):
29        return "%s_sequence" % tablename
30
31    def coalesce_zero(self, val, query_env={}):
32        return self.coalesce(val, [0], query_env)
33
34
35@dialects.register_for(SQLAdapter)
36class SQLDialect(CommonDialect):
37    quote_template = '"%s"'
38    true = "T"
39    false = "F"
40    true_exp = "1"
41    false_exp = "0"
42    dt_sep = " "
43
44    @sqltype_for("string")
45    def type_string(self):
46        return "VARCHAR(%(length)s)"
47
48    @sqltype_for("boolean")
49    def type_boolean(self):
50        return "CHAR(1)"
51
52    @sqltype_for("text")
53    def type_text(self):
54        return "TEXT"
55
56    @sqltype_for("json")
57    def type_json(self):
58        return self.types["text"]
59
60    @sqltype_for("password")
61    def type_password(self):
62        return self.types["string"]
63
64    @sqltype_for("blob")
65    def type_blob(self):
66        return "BLOB"
67
68    @sqltype_for("upload")
69    def type_upload(self):
70        return self.types["string"]
71
72    @sqltype_for("integer")
73    def type_integer(self):
74        return "INTEGER"
75
76    @sqltype_for("bigint")
77    def type_bigint(self):
78        return self.types["integer"]
79
80    @sqltype_for("float")
81    def type_float(self):
82        return "FLOAT"
83
84    @sqltype_for("double")
85    def type_double(self):
86        return "DOUBLE"
87
88    @sqltype_for("decimal")
89    def type_decimal(self):
90        return "NUMERIC(%(precision)s,%(scale)s)"
91
92    @sqltype_for("date")
93    def type_date(self):
94        return "DATE"
95
96    @sqltype_for("time")
97    def type_time(self):
98        return "TIME"
99
100    @sqltype_for("datetime")
101    def type_datetime(self):
102        return "TIMESTAMP"
103
104    @sqltype_for("id")
105    def type_id(self):
106        return "INTEGER PRIMARY KEY AUTOINCREMENT"
107
108    @sqltype_for("reference")
109    def type_reference(self):
110        return (
111            "INTEGER REFERENCES %(foreign_key)s "
112            + "ON DELETE %(on_delete_action)s %(null)s %(unique)s"
113        )
114
115    @sqltype_for("list:integer")
116    def type_list_integer(self):
117        return self.types["text"]
118
119    @sqltype_for("list:string")
120    def type_list_string(self):
121        return self.types["text"]
122
123    @sqltype_for("list:reference")
124    def type_list_reference(self):
125        return self.types["text"]
126
127    @sqltype_for("big-id")
128    def type_big_id(self):
129        return self.types["id"]
130
131    @sqltype_for("big-reference")
132    def type_big_reference(self):
133        return self.types["reference"]
134
135    @sqltype_for("reference FK")
136    def type_reference_fk(self):
137        return (
138            ', CONSTRAINT  "FK_%(constraint_name)s" FOREIGN KEY '
139            + "(%(field_name)s) REFERENCES %(foreign_key)s "
140            + "ON DELETE %(on_delete_action)s"
141        )
142
143    def alias(self, original, new):
144        return ("%s AS " + self.quote_template) % (original, new)
145
146    def insert(self, table, fields, values):
147        return "INSERT INTO %s(%s) VALUES (%s);" % (table, fields, values)
148
149    def insert_empty(self, table):
150        return "INSERT INTO %s DEFAULT VALUES;" % table
151
152    def where(self, query):
153        return "WHERE %s" % query
154
155    def update(self, table, values, where=None):
156        tablename = self.writing_alias(table)
157        whr = ""
158        if where:
159            whr = " %s" % self.where(where)
160        return "UPDATE %s SET %s%s;" % (tablename, values, whr)
161
162    def delete(self, table, where=None):
163        tablename = self.writing_alias(table)
164        whr = ""
165        if where:
166            whr = " %s" % self.where(where)
167        return "DELETE FROM %s%s;" % (tablename, whr)
168
169    def select(
170        self,
171        fields,
172        tables,
173        where=None,
174        groupby=None,
175        having=None,
176        orderby=None,
177        limitby=None,
178        distinct=False,
179        for_update=False,
180    ):
181        dst, whr, grp, order, limit, offset, upd = "", "", "", "", "", "", ""
182        if distinct is True:
183            dst = " DISTINCT"
184        elif distinct:
185            dst = " DISTINCT ON (%s)" % distinct
186        if where:
187            whr = " %s" % self.where(where)
188        if groupby:
189            grp = " GROUP BY %s" % groupby
190            if having:
191                grp += " HAVING %s" % having
192        if orderby:
193            order = " ORDER BY %s" % orderby
194        if limitby:
195            (lmin, lmax) = limitby
196            limit = " LIMIT %i" % (lmax - lmin)
197            offset = " OFFSET %i" % lmin
198        if for_update:
199            upd = " FOR UPDATE"
200        return "SELECT%s %s FROM %s%s%s%s%s%s%s;" % (
201            dst,
202            fields,
203            tables,
204            whr,
205            grp,
206            order,
207            limit,
208            offset,
209            upd,
210        )
211
212    def count(self, val, distinct=None, query_env={}):
213        return ("COUNT(%s)" if not distinct else "COUNT(DISTINCT %s)") % self.expand(
214            val, query_env=query_env
215        )
216
217    def join(self, val, query_env={}):
218        if isinstance(val, (Table, Select)):
219            val = val.query_name(query_env.get("parent_scope", []))
220        elif not isinstance(val, basestring):
221            val = self.expand(val, query_env=query_env)
222        return "JOIN %s" % val
223
224    def left_join(self, val, query_env={}):
225        # Left join must always have an ON clause
226        if not isinstance(val, basestring):
227            val = self.expand(val, query_env=query_env)
228        return "LEFT JOIN %s" % val
229
230    def cross_join(self, val, query_env={}):
231        if isinstance(val, (Table, Select)):
232            val = val.query_name(query_env.get("parent_scope", []))
233        elif not isinstance(val, basestring):
234            val = self.expand(val, query_env=query_env)
235        return "CROSS JOIN %s" % val
236
237    @property
238    def random(self):
239        return "Random()"
240
241    def _as(self, first, second, query_env={}):
242        return "%s AS %s" % (self.expand(first, query_env=query_env), second)
243
244    def cast(self, first, second, query_env={}):
245        return "CAST(%s)" % self._as(first, second, query_env)
246
247    def _not(self, val, query_env={}):
248        return "(NOT %s)" % self.expand(val, query_env=query_env)
249
250    def _and(self, first, second, query_env={}):
251        return "(%s AND %s)" % (
252            self.expand(first, query_env=query_env),
253            self.expand(second, query_env=query_env),
254        )
255
256    def _or(self, first, second, query_env={}):
257        return "(%s OR %s)" % (
258            self.expand(first, query_env=query_env),
259            self.expand(second, query_env=query_env),
260        )
261
262    def belongs(self, first, second, query_env={}):
263        ftype = first.type
264        first = self.expand(first, query_env=query_env)
265        if isinstance(second, str):
266            return "(%s IN (%s))" % (first, second[:-1])
267        elif isinstance(second, Select):
268            if len(second._qfields) != 1:
269                raise ValueError("Subquery in belongs() must select exactly 1 column")
270            sub = second._compile(query_env.get("current_scope", []))[1][:-1]
271            return "(%s IN (%s))" % (first, sub)
272        if not second:
273            return "(1=0)"
274        items = ",".join(
275            self.expand(item, ftype, query_env=query_env) for item in second
276        )
277        return "(%s IN (%s))" % (first, items)
278
279    # def regexp(self, first, second):
280    #     raise NotImplementedError
281
282    def lower(self, val, query_env={}):
283        return "LOWER(%s)" % self.expand(val, query_env=query_env)
284
285    def upper(self, first, query_env={}):
286        return "UPPER(%s)" % self.expand(first, query_env=query_env)
287
288    def like(self, first, second, escape=None, query_env={}):
289        """Case sensitive like operator"""
290        if isinstance(second, Expression):
291            second = self.expand(second, "string", query_env=query_env)
292        else:
293            second = self.expand(second, "string", query_env=query_env)
294            if escape is None:
295                escape = "\\"
296                second = second.replace(escape, escape * 2)
297        return "(%s LIKE %s ESCAPE '%s')" % (
298            self.expand(first, query_env=query_env),
299            second,
300            escape,
301        )
302
303    def ilike(self, first, second, escape=None, query_env={}):
304        """Case insensitive like operator"""
305        if isinstance(second, Expression):
306            second = self.expand(second, "string", query_env=query_env)
307        else:
308            second = self.expand(second, "string", query_env=query_env).lower()
309            if escape is None:
310                escape = "\\"
311                second = second.replace(escape, escape * 2)
312        return "(%s LIKE %s ESCAPE '%s')" % (
313            self.lower(first, query_env=query_env),
314            second,
315            escape,
316        )
317
318    def _like_escaper_default(self, term):
319        if isinstance(term, Expression):
320            return term
321        term = term.replace("\\", "\\\\")
322        term = term.replace(r"%", r"\%").replace("_", r"\_")
323        return term
324
325    def startswith(self, first, second, query_env={}):
326        return "(%s LIKE %s ESCAPE '\\')" % (
327            self.expand(first, query_env=query_env),
328            self.expand(
329                self._like_escaper_default(second) + "%", "string", query_env=query_env
330            ),
331        )
332
333    def endswith(self, first, second, query_env={}):
334        return "(%s LIKE %s ESCAPE '\\')" % (
335            self.expand(first, query_env=query_env),
336            self.expand(
337                "%" + self._like_escaper_default(second), "string", query_env=query_env
338            ),
339        )
340
341    def replace(self, first, tup, query_env={}):
342        second, third = tup
343        return "REPLACE(%s,%s,%s)" % (
344            self.expand(first, "string", query_env=query_env),
345            self.expand(second, "string", query_env=query_env),
346            self.expand(third, "string", query_env=query_env),
347        )
348
349    def concat(self, *items, **kwargs):
350        query_env = kwargs.get("query_env", {})
351        tmp = (self.expand(x, "string", query_env=query_env) for x in items)
352        return "(%s)" % " || ".join(tmp)
353
354    def contains(self, first, second, case_sensitive=True, query_env={}):
355        if first.type in ("string", "text", "json", "jsonb"):
356            if isinstance(second, Expression):
357                second = Expression(
358                    second.db,
359                    self.concat(
360                        "%",
361                        Expression(
362                            second.db,
363                            self.replace(second, (r"%", r"\%"), query_env=query_env),
364                        ),
365                        r"%",
366                    ),
367                )
368            else:
369                second = "%" + self._like_escaper_default(str(second)) + r"%"
370        elif first.type.startswith("list:"):
371            if isinstance(second, Expression):
372                second = Expression(
373                    second.db,
374                    self.concat(
375                        r"%|",
376                        Expression(
377                            second.db,
378                            self.replace(
379                                Expression(
380                                    second.db,
381                                    self.replace(second, (r"%", r"\%"), query_env),
382                                ),
383                                ("|", "||"),
384                            ),
385                        ),
386                        r"|%",
387                    ),
388                )
389            else:
390                second = str(second).replace("|", "||")
391                second = "%|" + self._like_escaper_default(second) + "|%"
392        op = case_sensitive and self.like or self.ilike
393        return op(first, second, escape="\\", query_env=query_env)
394
395    def eq(self, first, second=None, query_env={}):
396        if second is None:
397            return "(%s IS NULL)" % self.expand(first, query_env=query_env)
398        if first.type in ("json", "jsonb"):
399            if isinstance(second, (string_types, int, float)):
400                return "(%s = '%s')" % (
401                    self.expand(first, query_env=query_env),
402                    self.expand(second, query_env=query_env),
403                )
404        return "(%s = %s)" % (
405            self.expand(first, query_env=query_env),
406            self.expand(second, first.type, query_env=query_env),
407        )
408
409    def ne(self, first, second=None, query_env={}):
410        if second is None:
411            return "(%s IS NOT NULL)" % self.expand(first, query_env=query_env)
412        if first.type in ("json", "jsonb"):
413            if isinstance(second, (string_types, int, float)):
414                return "(%s <> '%s')" % (
415                    self.expand(first, query_env=query_env),
416                    self.expand(second, query_env=query_env),
417                )
418        return "(%s <> %s)" % (
419            self.expand(first, query_env=query_env),
420            self.expand(second, first.type, query_env=query_env),
421        )
422
423    def lt(self, first, second=None, query_env={}):
424        if second is None:
425            raise RuntimeError("Cannot compare %s < None" % first)
426        if first.type in ("json", "jsonb"):
427            if isinstance(second, (string_types, int, float)):
428                return "(%s < '%s')" % (
429                    self.expand(first, query_env=query_env),
430                    self.expand(second, query_env=query_env),
431                )
432        return "(%s < %s)" % (
433            self.expand(first, query_env=query_env),
434            self.expand(second, first.type, query_env=query_env),
435        )
436
437    def lte(self, first, second=None, query_env={}):
438        if second is None:
439            raise RuntimeError("Cannot compare %s <= None" % first)
440        if first.type in ("json", "jsonb"):
441            if isinstance(second, (string_types, int, float)):
442                return "(%s <= '%s')" % (
443                    self.expand(first, query_env=query_env),
444                    self.expand(second, query_env=query_env),
445                )
446        return "(%s <= %s)" % (
447            self.expand(first, query_env=query_env),
448            self.expand(second, first.type, query_env=query_env),
449        )
450
451    def gt(self, first, second=None, query_env={}):
452        if second is None:
453            raise RuntimeError("Cannot compare %s > None" % first)
454        if first.type in ("json", "jsonb"):
455            if isinstance(second, (string_types, int, float)):
456                return "(%s > '%s')" % (
457                    self.expand(first, query_env=query_env),
458                    self.expand(second, query_env=query_env),
459                )
460        return "(%s > %s)" % (
461            self.expand(first, query_env=query_env),
462            self.expand(second, first.type, query_env=query_env),
463        )
464
465    def gte(self, first, second=None, query_env={}):
466        if second is None:
467            raise RuntimeError("Cannot compare %s >= None" % first)
468        if first.type in ("json", "jsonb"):
469            if isinstance(second, (string_types, int, float)):
470                return "(%s >= '%s')" % (
471                    self.expand(first, query_env=query_env),
472                    self.expand(second, query_env=query_env),
473                )
474        return "(%s >= %s)" % (
475            self.expand(first, query_env=query_env),
476            self.expand(second, first.type, query_env=query_env),
477        )
478
479    def _is_numerical(self, field_type):
480        return field_type in (
481            "integer",
482            "float",
483            "double",
484            "bigint",
485            "boolean",
486        ) or field_type.startswith("decimal")
487
488    def add(self, first, second, query_env={}):
489        if self._is_numerical(first.type) or isinstance(first.type, Field):
490            return "(%s + %s)" % (
491                self.expand(first, query_env=query_env),
492                self.expand(second, first.type, query_env=query_env),
493            )
494        else:
495            return self.concat(first, second, query_env=query_env)
496
497    def sub(self, first, second, query_env={}):
498        return "(%s - %s)" % (
499            self.expand(first, query_env=query_env),
500            self.expand(second, first.type, query_env=query_env),
501        )
502
503    def mul(self, first, second, query_env={}):
504        return "(%s * %s)" % (
505            self.expand(first, query_env=query_env),
506            self.expand(second, first.type, query_env=query_env),
507        )
508
509    def div(self, first, second, query_env={}):
510        return "(%s / %s)" % (
511            self.expand(first, query_env=query_env),
512            self.expand(second, first.type, query_env=query_env),
513        )
514
515    def mod(self, first, second, query_env={}):
516        return "(%s %% %s)" % (
517            self.expand(first, query_env=query_env),
518            self.expand(second, first.type, query_env=query_env),
519        )
520
521    def on(self, first, second, query_env={}):
522        table_rname = first.query_name(query_env.get("parent_scope", []))[0]
523        if use_common_filters(second):
524            second = self.adapter.common_filter(second, [first])
525        return ("%s ON %s") % (table_rname, self.expand(second, query_env=query_env))
526
527    def invert(self, first, query_env={}):
528        return "%s DESC" % self.expand(first, query_env=query_env)
529
530    def comma(self, first, second, query_env={}):
531        return "%s, %s" % (
532            self.expand(first, query_env=query_env),
533            self.expand(second, query_env=query_env),
534        )
535
536    def extract(self, first, what, query_env={}):
537        return "EXTRACT(%s FROM %s)" % (what, self.expand(first, query_env=query_env))
538
539    def epoch(self, val, query_env={}):
540        return self.extract(val, "epoch", query_env)
541
542    def length(self, val, query_env={}):
543        return "LENGTH(%s)" % self.expand(val, query_env=query_env)
544
545    def aggregate(self, first, what, query_env={}):
546        return "%s(%s)" % (what, self.expand(first, query_env=query_env))
547
548    def not_null(self, default, field_type):
549        return "NOT NULL DEFAULT %s" % self.adapter.represent(default, field_type)
550
551    @property
552    def allow_null(self):
553        return ""
554
555    def coalesce(self, first, second, query_env={}):
556        expressions = [self.expand(first, query_env=query_env)] + [
557            self.expand(val, first.type, query_env=query_env) for val in second
558        ]
559        return "COALESCE(%s)" % ",".join(expressions)
560
561    def raw(self, val, query_env={}):
562        return val
563
564    def substring(self, field, parameters, query_env={}):
565        return "SUBSTR(%s,%s,%s)" % (
566            self.expand(field, query_env=query_env),
567            parameters[0],
568            parameters[1],
569        )
570
571    def case(self, query, true_false, query_env={}):
572        _types = {bool: "boolean", int: "integer", float: "double"}
573        return "CASE WHEN %s THEN %s ELSE %s END" % (
574            self.expand(query, query_env=query_env),
575            self.adapter.represent(
576                true_false[0], _types.get(type(true_false[0]), "string")
577            ),
578            self.adapter.represent(
579                true_false[1], _types.get(type(true_false[1]), "string")
580            ),
581        )
582
583    def primary_key(self, key):
584        return "PRIMARY KEY(%s)" % key
585
586    def drop_table(self, table, mode):
587        return ["DROP TABLE %s;" % table._rname]
588
589    def truncate(self, table, mode=""):
590        if mode:
591            mode = " %s" % mode
592        return ["TRUNCATE TABLE %s%s;" % (table._rname, mode)]
593
594    def create_index(self, name, table, expressions, unique=False):
595        uniq = " UNIQUE" if unique else ""
596        with self.adapter.index_expander():
597            rv = "CREATE%s INDEX %s ON %s (%s);" % (
598                uniq,
599                self.quote(name),
600                table._rname,
601                ",".join(self.expand(field) for field in expressions),
602            )
603        return rv
604
605    def drop_index(self, name, table):
606        return "DROP INDEX %s;" % self.quote(name)
607
608    def constraint_name(self, table, fieldname):
609        return "%s_%s__constraint" % (table, fieldname)
610
611    def concat_add(self, tablename):
612        return ", ADD "
613
614    def writing_alias(self, table):
615        return table.sql_fullref
616
617
618class NoSQLDialect(CommonDialect):
619    @sqltype_for("string")
620    def type_string(self):
621        return str
622
623    @sqltype_for("boolean")
624    def type_boolean(self):
625        return bool
626
627    @sqltype_for("text")
628    def type_text(self):
629        return str
630
631    @sqltype_for("json")
632    def type_json(self):
633        return self.types["text"]
634
635    @sqltype_for("password")
636    def type_password(self):
637        return self.types["string"]
638
639    @sqltype_for("blob")
640    def type_blob(self):
641        return self.types["text"]
642
643    @sqltype_for("upload")
644    def type_upload(self):
645        return self.types["string"]
646
647    @sqltype_for("integer")
648    def type_integer(self):
649        return long
650
651    @sqltype_for("bigint")
652    def type_bigint(self):
653        return self.types["integer"]
654
655    @sqltype_for("float")
656    def type_float(self):
657        return float
658
659    @sqltype_for("double")
660    def type_double(self):
661        return self.types["float"]
662
663    @sqltype_for("date")
664    def type_date(self):
665        return datetime.date
666
667    @sqltype_for("time")
668    def type_time(self):
669        return datetime.time
670
671    @sqltype_for("datetime")
672    def type_datetime(self):
673        return datetime.datetime
674
675    @sqltype_for("id")
676    def type_id(self):
677        return long
678
679    @sqltype_for("reference")
680    def type_reference(self):
681        return long
682
683    @sqltype_for("list:integer")
684    def type_list_integer(self):
685        return list
686
687    @sqltype_for("list:string")
688    def type_list_string(self):
689        return list
690
691    @sqltype_for("list:reference")
692    def type_list_reference(self):
693        return list
694
695    def quote(self, val):
696        return val
Note: See TracBrowser for help on using the repository browser.