pli/persistance/sql/sql.py

   1 #=======================================================================
   2
   3 __version__ = '''0.0.01'''
   4 __sub_version__ = '''20051010144817'''
   5 __copyright__ = '''(c) Alex A. Naanou 2003'''
   6
   7
   8 #-----------------------------------------------------------------------
   9
  10
  11
  12
  13 #-----------------------------------------------------------------------
  14 #-------------------------------------------------------transactioned---
  15 # XXX transaction decorator...
  16 def transactioned(meth):
  17         '''
  18         '''
  19         def _transactioned(*p, **n):
  20                 '''
  21                 '''
  22                 # get connection...
  23                 ##!!!!
  24                 connection = None
  25                 res = meth(*p, **n)
  26                 connection.commit()
  27                 return res
  28         return _transactioned
  29
  30 #-------------------------------------------------------autoreconnect---
  31 # XXX this will restore the connection if needed...
  32 def autoreconnect(meth):
  33         '''
  34         '''
  35         def _autoreconnect(self, *p, **n):
  36                 '''
  37                 '''
  38                 # get connection...
  39                 ##!!!!
  40                 connection = None
  41                 # get cursor...
  42                 ##!!!!
  43                 cur = connection.cursor()
  44                 try:
  45                         cur.execute('SELECT 1;').fetchone()[0]
  46                 except:
  47                         ##!!! RECONNECT
  48                         pass
  49                 return meth(*p, **n)
  50         return _autoreconnect
  51
  52
  53
  54 #-----------------------------------------------------------------------
  55 #------------------------------------------------------------SQLError---
  56 class SQLError(Exception):
  57         '''
  58         '''
  59         pass
  60
  61
  62 #--------------------------------------------------------------py2sql---
  63 def py2sql(value):
  64         '''
  65         translate a value into an apropriate sql type.
  66         '''
  67         t = type(value)
  68         if t in (int, float, long):
  69                 return str(value)
  70         if t in (str, unicode):
  71                 ##!!! WRONG !!!##
  72                 return '\'%s\'' % value
  73         if value is None:
  74                 return 'NULL'
  75         raise TypeError, 'can\'t convert objects of type %s.' % t
  76
  77
  78 #-----------------------------------------------------------------------
  79 #--------------------------------------------------------------_WHERE---
  80 ##!!! THINK OF A BETTER WAY TO DO THIS !!!##
  81 # XXX should the condition return the WHERE kw??
  82 class _WHERE(object):
  83         '''
  84         compile and check a condition string...
  85         '''
  86         ##!!! wrap values !!!##
  87         def __init__(self, *p, **n):
  88                 '''
  89                 '''
  90                 if len(p) == 1 and type(p[0]) is str:
  91                         self.condition = p[0]
  92                 if len(p) == 0:
  93                         self.condition = ' AND '.join([ '"%s" = %s' % (k, py2sql(v)) for k, v in n.items() ])
  94         def __str__(self):
  95                 '''
  96                 '''
  97                 c = self.condition
  98                 return str(c)
  99         def __repr__(self):
 100                 '''
 101                 '''
 102                 return str(self.condition)
 103
 104
 105
 106 #-----------------------------------------------------------------------
 107 #-----------------------------------------------------------------SQL---
 108 # XXX Revise extensibility to other SQL dialects...
 109 # TODO rename to pgSQL
 110 class SQL(object):
 111         '''
 112         '''
 113         # the condition clause processor...
 114         where = _WHERE
 115
 116         def __init__(self, connection):
 117                 '''
 118                 '''
 119                 self.connection = connection
 120         # utility methods
 121         def checksqlname(self, name):
 122                 '''
 123                 '''
 124                 return '"%s"' % name.replace('"', '\\"')
 125         ##!!!
 126         def sql2py(self, value):
 127                 '''
 128                 '''
 129                 pass
 130         # SQL expression generators
 131         # TODO add other expressions... (CREATE, DROP, ...)
 132         # TODO make these dialect independent...
 133         # TODO add filter support for select (e.g. rename columns... etc.)
 134         # TODO write more docs...
 135         ##!!! TODO more pedantic input checking!
 136         def select_sql(self, columns, source,
 137                                                 condition=None,
 138                                                 order=None,
 139                                                 # XXX the next two appear to PostgresSQL
 140                                                 #     specific... REVISE!
 141                                                 count=None,
 142                                                 offset=None):
 143                 '''
 144
 145                 SQL Syntax Reference:
 146
 147                 '''
 148                 # cahe some names...
 149                 checksqlname = self.checksqlname
 150
 151                 # construct the source...
 152                 ##!!! CAN THIS BE ANOTHER SELECT HERE????
 153                 source = checksqlname(source)
 154
 155                 # construct columns...
 156                 # NOTE: columns can be:
 157                 #               - list
 158                 #               - dict (???)
 159                 #               - combination (???)
 160                 if type(columns) is not str:
 161                         columns = ', '.join([ checksqlname(n) for n in columns ])
 162
 163                 # process the condition...
 164                 if condition is None:
 165                         condition = ''
 166                 elif type(condition) is self.where:
 167                         condition = '\n\tWHERE ' + str(condition)
 168                 else:
 169                         condition = '\n\tWHERE ' + str(self.where(condition))
 170
 171                 # order...
 172                 # XXX do we need direction here???
 173                 if order != None:
 174                         order = '\n\tORDER BY %s' % checksqlname(order)
 175                 else:
 176                         order = ''
 177
 178                 # count...
 179                 if count != None:
 180                         count = '\n\tLIMIT %s' % py2sql(count)
 181                 else:
 182                         count = ''
 183
 184                 # offset...
 185                 if offset != None:
 186                         offset = '\n\tOFFSET %s' % py2sql(offset)
 187                 else:
 188                         offset = ''
 189
 190                 return 'SELECT %(columns)s \n\tFROM %(source)s%(where)s%(count)s%(offset)s%(order)s ;' \
 191                                         % {'columns': columns,
 192                                                 'source': source,
 193                                                 'where': condition,
 194                                                 'count': count,
 195                                                 'order': order,
 196                                                 'offset': offset,}
 197         # TODO make setting defaults possible... (e.g. clo_x=DEFAULT)
 198         # TODO support complex expression... (???)
 199         ##!!! TODO more pedantic input checking!
 200         def insert_sql(self, table, *p, **n):
 201                 '''
 202                 generate an insert query.
 203
 204                 Format:
 205                         insert_sql(<table_name>[, <value>[, ...]][, <column>=<value>[, ...]]) -> QUERY
 206
 207                 SQL Syntax Reference:
 208                         INSERT INTO table [ ( column [, ...] ) ]
 209                                 { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }
 210                 '''
 211                 # cahe some names...
 212                 checksqlname = self.checksqlname
 213
 214                 # prepare data...
 215                 table = checksqlname(table)
 216                 columns = n.keys()
 217                 values = (tuple([ n[k] for k in columns ]) + p) or ''
 218                 # sqlify the values...
 219                 if values == '':
 220                         columns = ' DEFAULT'
 221                 else:
 222                         values = '( %s ) ' % ', '.join([ py2sql(v) for v in values ])
 223                         # sqlify the columns...
 224                         if len(columns) == 0:
 225                                 columns = ''
 226                         else:
 227                                 columns = ' ( %s )' % ', '.join([ checksqlname(n) for n in columns ])
 228                 # generate the query...
 229                 return 'INSERT INTO %s%s VALUES %s;' % (table, columns, values)
 230         # TODO support complex expression... (???)
 231         # TODO a more elaborate condition... (maybe a whwre method?)
 232         ##!!! TODO more pedantic input checking!
 233         def update_sql(self, table, condition, *p, **n):
 234                 '''
 235
 236                 Format:
 237
 238                 SQL Syntax Reference:
 239                         UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
 240                                 [ WHERE condition ]
 241                 '''
 242                 # sanity checks...
 243                 if len(n) == 0:
 244                         raise SQLError, 'must update at least one column (none given)'
 245                 # cahe some names...
 246                 checksqlname = self.checksqlname
 247
 248                 # prepare data...
 249                 table = checksqlname(table)
 250
 251                 columns = ', '.join([ '%s = %s' % (checksqlname(k), py2sql(v)) for k, v in n.items() ])
 252
 253                 # process the condition...
 254                 if condition is None:
 255                         condition = ''
 256                 elif type(condition) is self.where:
 257                         condition = '\n\tWHERE ' + str(condition)
 258                 else:
 259                         condition = '\n\tWHERE ' + str(self.where(condition))
 260
 261                 # XXX should the condition return the WHERE kw??
 262                 return 'UPDATE %s SET %s%s ;' % (table, columns, condition)
 263         ##!!! TODO more pedantic input checking!
 264         def delete_sql(self, table, condition=None):
 265                 '''
 266
 267                 Format:
 268
 269                 SQL Syntax Reference:
 270                         DELETE FROM [ ONLY ] table [ WHERE condition ]
 271                 '''
 272                 # cahe some names...
 273                 checksqlname = self.checksqlname
 274
 275                 # prepare data...
 276                 table = checksqlname(table)
 277
 278                 # process the condition...
 279                 if condition is None:
 280                         condition = ''
 281                 elif type(condition) is self.where:
 282                         condition = '\n\tWHERE ' + str(condition)
 283                 else:
 284                         condition = '\n\tWHERE ' + str(self.where(condition))
 285
 286                 return 'DELETE FROM %(table)s%(where)s ;' \
 287                                 % {'table': table,
 288                                         'where': condition}
 289         # methods
 290         # XXX make all of the following packable into transactions!!!
 291         # XXX do we need to process the result here???
 292         def select(self, columns, source, condition=None,
 293                                                 order=None, count=None, offset=None):
 294                 '''
 295                 '''
 296                 cur = self.connection.cursor()
 297                 cur.execute(self.select_sql(columns, source, condition, order, count, offset))
 298                 return cur
 299         def insert(self, table, *p, **n):
 300                 '''
 301                 '''
 302                 cur = self.connection.cursor()
 303                 cur.execute(self.insert_sql(table, *p, **n))
 304                 return cur
 305         def update(self, table, condition, *p, **n):
 306                 '''
 307                 '''
 308                 cur = self.connection.cursor()
 309                 cur.execute(self.update_sql(table, condition, *p, **n))
 310                 return cur
 311         def delete(self, table, condition=None):
 312                 '''
 313                 '''
 314                 cur = self.connection.cursor()
 315                 cur.execute(self.delete_sql(table, condition))
 316                 return cur
 317
 318
 319
 320 #=======================================================================
 321 #                                            vim:set ts=4 sw=4 nowrap :