psycopg2/ZPsycopgDA/DA.py

   1 # ZPsycopgDA/DA.py - ZPsycopgDA Zope product: Database Connection
   2 #
   3 # Copyright (C) 2004 Federico Di Gregorio <fog@initd.org>
   4 #
   5 # This program is free software; you can redistribute it and/or modify
   6 # it under the terms of the GNU General Public License as published by the
   7 # Free Software Foundation; either version 2, or (at your option) any later
   8 # version.
   9 #
  10 # Or, at your option this program (ZPsycopgDA) can be distributed under the
  11 # Zope Public License (ZPL) Version 1.0, as published on the Zope web site,
  12 # http://www.zope.org/Resources/ZPL.
  13 #
  14 # This program is distributed in the hope that it will be useful, but
  15 # WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY
  16 # or FITNESS FOR A PARTICULAR PURPOSE.
  17 #
  18 # See the LICENSE file for details.
  19
  20
  21 ALLOWED_PSYCOPG_VERSIONS = ('2.0.1', '2.0.2', '2.0.3', '2.0.4', '2.0.5')
  22
  23 import sys
  24 import time
  25 import db
  26 import re
  27
  28 import Acquisition
  29 import Shared.DC.ZRDB.Connection
  30
  31 from db import DB
  32 from Globals import HTMLFile
  33 from ExtensionClass import Base
  34 from App.Dialogs import MessageDialog
  35 from DateTime import DateTime
  36
  37 # Build Zope version in a float for later cheks
  38 import App
  39 zope_version = App.version_txt.getZopeVersion()
  40 zope_version = float("%s.%s" %(zope_version[:2]))
  41
  42 # ImageFile is deprecated in Zope >= 2.9
  43 if zope_version < 2.9:
  44      from ImageFile import ImageFile
  45 else:
  46      from App.ImageFile import ImageFile
  47
  48 # import psycopg and functions/singletons needed for date/time conversions
  49
  50 import psycopg2
  51 from psycopg2 import NUMBER, STRING, ROWID, DATETIME
  52 from psycopg2.extensions import INTEGER, LONGINTEGER, FLOAT, BOOLEAN, DATE
  53 from psycopg2.extensions import TIME, INTERVAL
  54 from psycopg2.extensions import new_type, register_type
  55
  56
  57 # add a new connection to a folder
  58
  59 manage_addZPsycopgConnectionForm = HTMLFile('dtml/add',globals())
  60
  61 def manage_addZPsycopgConnection(self, id, title, connection_string,
  62                                  zdatetime=None, tilevel=2,
  63                                  check=None, REQUEST=None):
  64     """Add a DB connection to a folder."""
  65     self._setObject(id, Connection(id, title, connection_string,
  66                                    zdatetime, check, tilevel))
  67     if REQUEST is not None: return self.manage_main(self, REQUEST)
  68
  69
  70 # the connection object
  71
  72 class Connection(Shared.DC.ZRDB.Connection.Connection):
  73     """ZPsycopg Connection."""
  74     _isAnSQLConnection = 1
  75
  76     id                = 'Psycopg2_database_connection'
  77     database_type     = 'Psycopg2'
  78     meta_type = title = 'Z Psycopg 2 Database Connection'
  79     icon              = 'misc_/conn'
  80
  81     def __init__(self, id, title, connection_string,
  82                  zdatetime, check=None, tilevel=2, encoding=''):
  83         self.zdatetime = zdatetime
  84         self.id = str(id)
  85         self.edit(title, connection_string, zdatetime,
  86                   check=check, tilevel=tilevel, encoding=encoding)
  87
  88     def factory(self):
  89         return DB
  90
  91     ## connection parameters editing ##
  92
  93     def edit(self, title, connection_string,
  94              zdatetime, check=None, tilevel=2, encoding=''):
  95         self.title = title
  96         self.connection_string = connection_string
  97         self.zdatetime = zdatetime
  98         self.tilevel = tilevel
  99         self.encoding = encoding
 100
 101         self.set_type_casts()
 102
 103         if check: self.connect(self.connection_string)
 104
 105     manage_properties = HTMLFile('dtml/edit', globals())
 106
 107     def manage_edit(self, title, connection_string,
 108                     zdatetime=None, check=None, tilevel=2, encoding='UTF-8',
 109                     REQUEST=None):
 110         """Edit the DB connection."""
 111         self.edit(title, connection_string, zdatetime,
 112                   check=check, tilevel=tilevel, encoding=encoding)
 113         if REQUEST is not None:
 114             msg = "Connection edited."
 115             return self.manage_main(self,REQUEST,manage_tabs_message=msg)
 116
 117     def connect(self, s):
 118         try:
 119             self._v_database_connection.close()
 120         except:
 121             pass
 122
 123         # check psycopg version and raise exception if does not match
 124         if psycopg2.__version__[:5] not in ALLOWED_PSYCOPG_VERSIONS:
 125             raise ImportError("psycopg version mismatch (imported %s)" %
 126                               psycopg2.__version__)
 127
 128         self.set_type_casts()
 129         self._v_connected = ''
 130         dbf = self.factory()
 131
 132         # TODO: let the psycopg exception propagate, or not?
 133         self._v_database_connection = dbf(
 134             self.connection_string, self.tilevel, self.encoding)
 135         self._v_database_connection.open()
 136         self._v_connected = DateTime()
 137
 138         return self
 139
 140     def set_type_casts(self):
 141         # note that in both cases order *is* important
 142         if self.zdatetime:
 143             # use zope internal datetime routines
 144             register_type(ZDATETIME)
 145             register_type(ZDATE)
 146             register_type(ZTIME)
 147         else:
 148             # use the standard
 149             register_type(DATETIME)
 150             register_type(DATE)
 151             register_type(TIME)
 152
 153     ## browsing and table/column management ##
 154
 155     manage_options = Shared.DC.ZRDB.Connection.Connection.manage_options
 156     # + (
 157     #    {'label': 'Browse', 'action':'manage_browse'},)
 158
 159     #manage_tables = HTMLFile('dtml/tables', globals())
 160     #manage_browse = HTMLFile('dtml/browse', globals())
 161
 162     info = None
 163
 164     def table_info(self):
 165         return self._v_database_connection.table_info()
 166
 167
 168     def __getitem__(self, name):
 169         if name == 'tableNamed':
 170             if not hasattr(self, '_v_tables'): self.tpValues()
 171             return self._v_tables.__of__(self)
 172         raise KeyError, name
 173
 174     def tpValues(self):
 175         res = []
 176         conn = self._v_database_connection
 177         for d in conn.tables(rdb=0):
 178             try:
 179                 name = d['TABLE_NAME']
 180                 b = TableBrowser()
 181                 b.__name__ = name
 182                 b._d = d
 183                 b._c = c
 184                 try:
 185                     b.icon = table_icons[d['TABLE_TYPE']]
 186                 except:
 187                     pass
 188                 r.append(b)
 189             except:
 190                 pass
 191         return res
 192
 193
 194 ## database connection registration data ##
 195
 196 classes = (Connection,)
 197
 198 meta_types = ({'name':'Z Psycopg 2 Database Connection',
 199                'action':'manage_addZPsycopgConnectionForm'},)
 200
 201 folder_methods = {
 202     'manage_addZPsycopgConnection': manage_addZPsycopgConnection,
 203     'manage_addZPsycopgConnectionForm': manage_addZPsycopgConnectionForm}
 204
 205 __ac_permissions__ = (
 206     ('Add Z Psycopg Database Connections',
 207      ('manage_addZPsycopgConnectionForm', 'manage_addZPsycopgConnection')),)
 208
 209 # add icons
 210
 211 misc_={'conn': ImageFile('Shared/DC/ZRDB/www/DBAdapterFolder_icon.gif')}
 212
 213 for icon in ('table', 'view', 'stable', 'what', 'field', 'text', 'bin',
 214              'int', 'float', 'date', 'time', 'datetime'):
 215     misc_[icon] = ImageFile('icons/%s.gif' % icon, globals())
 216
 217
 218 ## zope-specific psycopg typecasters ##
 219
 220 # convert an ISO timestamp string from postgres to a Zope DateTime object
 221 def _cast_DateTime(iso, curs):
 222     if iso:
 223         return DateTime(re.split("GMT\+?|GMT-?", iso)[0])
 224
 225     # this will split us into [date, time, GMT/AM/PM(if there)]
 226     #    dt = str.split(' ')
 227     #    if len(dt) > 1:
 228     #        # we now should split out any timezone info
 229     #        dt[1] = dt[1].split('-')[0]
 230     #        dt[1] = dt[1].split('+')[0]
 231     #        return DateTime(' '.join(dt[:2]))
 232     #    else:
 233     #        return DateTime(dt[0])
 234
 235 # convert an ISO date string from postgres to a Zope DateTime object
 236 def _cast_Date(iso, curs):
 237     if iso:
 238         return DateTime(iso)
 239
 240 # Convert a time string from postgres to a Zope DateTime object.
 241 # NOTE: we set the day as today before feeding to DateTime so
 242 # that it has the same DST settings.
 243 def _cast_Time(iso, curs):
 244     if iso:
 245         return DateTime(time.strftime('%Y-%m-%d %H:%M:%S',
 246                                       time.localtime(time.time())[:3]+
 247                                       time.strptime(iso[:8], "%H:%M:%S")[3:]))
 248
 249 # NOTE: we don't cast intervals anymore because they are passed
 250 # untouched to Zope.
 251 def _cast_Interval(iso, curs):
 252     return iso
 253
 254 ZDATETIME = new_type((1184, 1114), "ZDATETIME", _cast_DateTime)
 255 ZINTERVAL = new_type((1186,), "ZINTERVAL", _cast_Interval)
 256 ZDATE = new_type((1082,), "ZDATE", _cast_Date)
 257 ZTIME = new_type((1083,), "ZTIME", _cast_Time)
 258
 259
 260 ## table browsing helpers ##
 261
 262 class TableBrowserCollection(Acquisition.Implicit):
 263     pass
 264
 265 class Browser(Base):
 266     def __getattr__(self, name):
 267         try:
 268             return self._d[name]
 269         except KeyError:
 270             raise AttributeError, name
 271
 272 class values:
 273     def len(self):
 274         return 1
 275
 276     def __getitem__(self, i):
 277         try:
 278             return self._d[i]
 279         except AttributeError:
 280             pass
 281         self._d = self._f()
 282         return self._d[i]
 283
 284 class TableBrowser(Browser, Acquisition.Implicit):
 285     icon = 'what'
 286     Description = check = ''
 287     info = HTMLFile('table_info', globals())
 288     menu = HTMLFile('table_menu', globals())
 289
 290     def tpValues(self):
 291         v = values()
 292         v._f = self.tpValues_
 293         return v
 294
 295     def tpValues_(self):
 296         r=[]
 297         tname=self.__name__
 298         for d in self._c.columns(tname):
 299             b=ColumnBrowser()
 300             b._d=d
 301             try: b.icon=field_icons[d['Type']]
 302             except: pass
 303             b.TABLE_NAME=tname
 304             r.append(b)
 305         return r
 306
 307     def tpId(self): return self._d['TABLE_NAME']
 308     def tpURL(self): return "Table/%s" % self._d['TABLE_NAME']
 309     def Name(self): return self._d['TABLE_NAME']
 310     def Type(self): return self._d['TABLE_TYPE']
 311
 312     manage_designInput=HTMLFile('designInput',globals())
 313     def manage_buildInput(self, id, source, default, REQUEST=None):
 314         "Create a database method for an input form"
 315         args=[]
 316         values=[]
 317         names=[]
 318         columns=self._columns
 319         for i in range(len(source)):
 320             s=source[i]
 321             if s=='Null': continue
 322             c=columns[i]
 323             d=default[i]
 324             t=c['Type']
 325             n=c['Name']
 326             names.append(n)
 327             if s=='Argument':
 328                 values.append("<dtml-sqlvar %s type=%s>'" %
 329                               (n, vartype(t)))
 330                 a='%s%s' % (n, boboType(t))
 331                 if d: a="%s=%s" % (a,d)
 332                 args.append(a)
 333             elif s=='Property':
 334                 values.append("<dtml-sqlvar %s type=%s>'" %
 335                               (n, vartype(t)))
 336             else:
 337                 if isStringType(t):
 338                     if find(d,"\'") >= 0: d=join(split(d,"\'"),"''")
 339                     values.append("'%s'" % d)
 340                 elif d:
 341                     values.append(str(d))
 342                 else:
 343                     raise ValueError, (
 344                         'no default was given for <em>%s</em>' % n)
 345
 346 class ColumnBrowser(Browser):
 347     icon='field'
 348
 349     def check(self):
 350         return ('\t<input type=checkbox name="%s.%s">' %
 351                 (self.TABLE_NAME, self._d['Name']))
 352     def tpId(self): return self._d['Name']
 353     def tpURL(self): return "Column/%s" % self._d['Name']
 354     def Description(self):
 355         d=self._d
 356         if d['Scale']:
 357             return " %(Type)s(%(Precision)s,%(Scale)s) %(Nullable)s" % d
 358         else:
 359             return " %(Type)s(%(Precision)s) %(Nullable)s" % d
 360
 361 table_icons={
 362     'TABLE': 'table',
 363     'VIEW':'view',
 364     'SYSTEM_TABLE': 'stable',
 365     }
 366
 367 field_icons={
 368     NUMBER.name: 'i',
 369     STRING.name: 'text',
 370     DATETIME.name: 'date',
 371     INTEGER.name: 'int',
 372     FLOAT.name: 'float',
 373     BOOLEAN.name: 'bin',
 374     ROWID.name: 'int'
 375     }