imip-agent

Annotated imiptools/sql.py

1341:f5b485cc5a93
1085:ab557e83c712
2017-10-17 Paul Boddie file changeset files shortlog Merged changes from the default branch. client-editing-simplification
paul@1078 1 
#!/usr/bin/env python
paul@1078 2 
 
 paul@1078      3 
"""
 
 paul@1078      4 
Database utilities.
 
 paul@1078      5 
 
 paul@1078      6 
Copyright (C) 2016 Paul Boddie <paul@boddie.org.uk>
 
 paul@1078      7 
 
 paul@1078      8 
This program is free software; you can redistribute it and/or modify it under
 
 paul@1078      9 
the terms of the GNU General Public License as published by the Free Software
 
 paul@1078     10 
Foundation; either version 3 of the License, or (at your option) any later
 
 paul@1078     11 
version.
 
 paul@1078     12 
 
 paul@1078     13 
This program is distributed in the hope that it will be useful, but WITHOUT
 
 paul@1078     14 
ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 
 paul@1078     15 
FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
 
 paul@1078     16 
details.
 
 paul@1078     17 
 
 paul@1078     18 
You should have received a copy of the GNU General Public License along with
 
 paul@1078     19 
this program.  If not, see <http://www.gnu.org/licenses/>.
 
 paul@1078     20 
"""
 
 paul@1078     21 
 
 paul@1078     22 
import re
 
 paul@1078     23 
 
 paul@1078     24 
class DatabaseOperations:
 
 paul@1078     25 
 
 paul@1078     26 
    "Special database-related operations."
 
 paul@1078     27 
 
 paul@1082     28 
    def __init__(self, column_names=None, filter_values=None, paramstyle=None):
 
 paul@1078     29 
        self.column_names = column_names
 
 paul@1078     30 
        self.filter_values = filter_values
 
 paul@1082     31 
        self.paramstyle = paramstyle
 
 paul@1078     32 
 
 paul@1082     33 
    def get_query(self, query, columns=None, values=None, setcolumns=None,
 
 paul@1082     34 
                  setvalues=None):
 
 paul@1078     35 
 
 paul@1078     36 
        """
 
 paul@1078     37 
        Return 'query' parameterised with condition clauses indicated by
 
 paul@1078     38 
        ":condition" in 'query' that are themselves populated using the given
 
 paul@1078     39 
        'columns' and 'values' together with any conditions provided when
 
 paul@1078     40 
        initialising this class.
 
 paul@1082     41 
 
 paul@1082     42 
        If 'setcolumns' and 'setvalues' are given, such column details and
 
 paul@1082     43 
        values will be used to parameterise ":set" clauses in the query.
 
 paul@1078     44 
        """
 
 paul@1078     45 
 
 paul@1078     46 
        columns = self.merge_default_columns(columns)
 
 paul@1078     47 
        values = self.merge_default_values(values)
 
 paul@1078     48 
 
 paul@1078     49 
        condition = self.get_condition(columns)
 
 paul@1078     50 
        columnlist = self.columnlist(columns)
 
 paul@1078     51 
        placeholders = self.placeholders(values)
 
 paul@1082     52 
        setters = self.get_setters(setcolumns)
 
 paul@1078     53 
 
 paul@1082     54 
        setvalues = setvalues or []
 
 paul@1082     55 
 
 paul@1082     56 
        # Obtain the placeholder markers in order.
 
 paul@1082     57 
 
 paul@1082     58 
        parts = re.split("(:(?:condition|set|columns|values)(?=[^a-zA-Z]|$))", query)
 
 paul@1082     59 
 
 paul@1082     60 
        l = [parts[0]]
 
 paul@1082     61 
        is_placeholder = True
 
 paul@1082     62 
        all_values = []
 
 paul@1082     63 
 
 paul@1082     64 
        for part in parts[1:]:
 
 paul@1082     65 
            if is_placeholder:
 
 paul@1082     66 
 
 paul@1082     67 
                # Replace ":condition", replicating the given values.
 
 paul@1082     68 
 
 paul@1082     69 
                if part == ":condition":
 
 paul@1082     70 
                    all_values += values
 
 paul@1082     71 
                    l.append(condition)
 
 paul@1082     72 
 
 paul@1082     73 
                # Replace ":set", replicating the given values.
 
 paul@1078     74 
 
 paul@1082     75 
                elif part == ":set":
 
 paul@1082     76 
                    all_values += setvalues
 
 paul@1082     77 
                    l.append(setters)
 
 paul@1082     78 
 
 paul@1082     79 
                # Replace ":columns", providing a column list.
 
 paul@1082     80 
 
 paul@1082     81 
                elif part == ":columns":
 
 paul@1082     82 
                    l.append(columnlist)
 
 paul@1082     83 
 
 paul@1082     84 
                # Replace ":values", replicating the given values.
 
 paul@1082     85 
 
 paul@1082     86 
                elif part == ":values":
 
 paul@1082     87 
                    all_values += values
 
 paul@1082     88 
                    l.append(placeholders)
 
 paul@1082     89 
 
 paul@1082     90 
                else:
 
 paul@1082     91 
                    l.append(part)
 
 paul@1082     92 
            else:
 
 paul@1082     93 
                l.append(part)
 
 paul@1082     94 
 
 paul@1082     95 
            is_placeholder = not is_placeholder
 
 paul@1082     96 
 
 paul@1082     97 
        query = "".join(l)
 
 paul@1078     98 
        return query, all_values
 
 paul@1078     99 
 
 paul@1078    100 
    def get_condition(self, columns=None):
 
 paul@1078    101 
 
 paul@1078    102 
        "Return a condition clause featuring the given 'columns'."
 
 paul@1078    103 
 
 paul@1082    104 
        l = self._get_columns(columns)
 
 paul@1082    105 
        return "where %s" % " and ".join(l)
 
 paul@1082    106 
 
 paul@1082    107 
    def get_setters(self, columns=None):
 
 paul@1082    108 
 
 paul@1082    109 
        "Return set operations featuring the given 'columns'."
 
 paul@1082    110 
 
 paul@1082    111 
        l = self._get_columns(columns)
 
 paul@1082    112 
        return "set %s" % ", ".join(l)
 
 paul@1082    113 
 
 paul@1082    114 
    def _get_columns(self, columns=None):
 
 paul@1082    115 
 
 paul@1082    116 
        "Return a list of statements or tests involving 'columns'."
 
 paul@1082    117 
 
 paul@1078    118 
        l = []
 
 paul@1078    119 
 
 paul@1082    120 
        if columns:
 
 paul@1082    121 
            for column in columns:
 
 paul@1082    122 
                if " " in column:
 
 paul@1082    123 
                    column_name, remaining = column.split(" ", 1)
 
 paul@1085    124 
                    l.append("%s %s" % (self._quote(column_name), remaining.replace("?", self._param())))
 
 paul@1082    125 
                else:
 
 paul@1082    126 
                    l.append("%s = %s" % (self._quote(column), self._param()))
 
 paul@1082    127 
 
 paul@1082    128 
        return l
 
 paul@1082    129 
 
 paul@1082    130 
    def _quote(self, column):
 
 paul@1082    131 
        return '"%s"' % column
 
 paul@1078    132 
 
 paul@1078    133 
    def merge_default_columns(self, columns=None):
 
 paul@1078    134 
        return list(self.column_names or []) + list(columns or [])
 
 paul@1078    135 
 
 paul@1078    136 
    def merge_default_values(self, values=None):
 
 paul@1078    137 
        return list(self.filter_values or []) + list(values or [])
 
 paul@1078    138 
 
 paul@1078    139 
    def columnlist(self, columns=None):
 
 paul@1082    140 
        return ", ".join([self._quote(column) for column in columns])
 
 paul@1078    141 
 
 paul@1078    142 
    def placeholders(self, values=None):
 
 paul@1082    143 
        return ", ".join([self._param()] * len(values))
 
 paul@1082    144 
 
 paul@1082    145 
    def _param(self):
 
 paul@1082    146 
 
 paul@1082    147 
        # NOTE: To be expanded.
 
 paul@1082    148 
 
 paul@1082    149 
        if self.paramstyle == "pyformat":
 
 paul@1082    150 
            return "%s"
 
 paul@1082    151 
        else:
 
 paul@1082    152 
            return "?"
 
 paul@1078    153 
 
 paul@1078    154 
# vim: tabstop=4 expandtab shiftwidth=4