DA.py 19 KB
Newer Older
1
##############################################################################
matt@zope.com's avatar
matt@zope.com committed
2 3
#
# Copyright (c) 2001 Zope Corporation and Contributors. All Rights Reserved.
4
#
matt@zope.com's avatar
matt@zope.com committed
5 6 7 8 9 10
# This software is subject to the provisions of the Zope Public License,
# Version 2.0 (ZPL).  A copy of the ZPL should accompany this distribution.
# THIS SOFTWARE IS PROVIDED "AS IS" AND ANY AND ALL EXPRESS OR IMPLIED
# WARRANTIES ARE DISCLAIMED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
# WARRANTIES OF TITLE, MERCHANTABILITY, AGAINST INFRINGEMENT, AND FITNESS
# FOR A PARTICULAR PURPOSE
11
#
12
##############################################################################
13
__doc__='''Generic Database adapter'''
Jim Fulton's avatar
Jim Fulton committed
14 15


16
__version__='$Revision: 1.107 $'[11:-2]
Jim Fulton's avatar
Jim Fulton committed
17

18
import OFS.SimpleItem, Aqueduct, RDB, re
19
import DocumentTemplate, marshal, md5, base64, Acquisition, os
20
from Aqueduct import decodestring, parse
Jim Fulton's avatar
Jim Fulton committed
21
from Aqueduct import custom_default_report, default_input_form
22
from Globals import DTMLFile, MessageDialog
Jim Fulton's avatar
Jim Fulton committed
23
from cStringIO import StringIO
24
import sys, Globals, OFS.SimpleItem, AccessControl.Role
25
from string import atoi, find, join, split, rstrip
Jim Fulton's avatar
Jim Fulton committed
26
import DocumentTemplate, sqlvar, sqltest, sqlgroup
27 28
from time import time
from zlib import compress, decompress
29
from DateTime.DateTime import DateTime
30
md5new=md5.new
31
import ExtensionClass
32
import DocumentTemplate.DT_Util
33
from cPickle import dumps, loads
Jim Fulton's avatar
Jim Fulton committed
34
from Results import Results
35
from App.Extensions import getBrain
36 37
from AccessControl import getSecurityManager
from AccessControl.DTML import RestrictedDTML
38
from webdav.Resource import Resource
39
from webdav.Lockable import ResourceLockedError
Jim Fulton's avatar
Jim Fulton committed
40
try: from IOBTree import Bucket
Jim Fulton's avatar
Jim Fulton committed
41
except: Bucket=lambda:{}
Jim Fulton's avatar
Jim Fulton committed
42 43


's avatar
committed
44 45
class nvSQL(DocumentTemplate.HTML):
    # Non-validating SQL Template for use by SQLFiles.
46 47 48 49 50 51
    commands={}
    for k, v in DocumentTemplate.HTML.commands.items(): commands[k]=v
    commands['sqlvar' ]=sqlvar.SQLVar
    commands['sqltest']=sqltest.SQLTest
    commands['sqlgroup' ]=sqlgroup.SQLGroup

's avatar
committed
52 53 54
    _proxy_roles=()


55
class SQL(RestrictedDTML, ExtensionClass.Base, nvSQL):
's avatar
committed
56
    # Validating SQL template for Zope SQL Methods.
57
    pass
's avatar
committed
58

59

60
class DA(
Jim Fulton's avatar
Jim Fulton committed
61
    Aqueduct.BaseQuery,Acquisition.Implicit,
62
    Globals.Persistent,
63 64
    AccessControl.Role.RoleManager,
    OFS.SimpleItem.Item,
65
    Resource
66 67
    ):
    'Database Adapter'
Jim Fulton's avatar
Jim Fulton committed
68

69
    _col=None
70 71 72 73
    max_rows_=1000
    cache_time_=0
    max_cache_=100
    class_name_=class_file_=''
74
    _zclass=None
75
    allow_simple_one_argument_traversal=None
's avatar
committed
76
    template_class=SQL
77
    connection_hook=None
78

79
    manage_options=(
80
        (
81
        {'label':'Edit', 'action':'manage_main',
82
         'help':('ZSQLMethods','Z-SQL-Method_Edit.stx')},
83
        {'label':'Test', 'action':'manage_testForm',
84
         'help':('ZSQLMethods','Z-SQL-Method_Test.stx')},
85
        {'label':'Advanced', 'action':'manage_advancedForm',
86
         'help':('ZSQLMethods','Z-SQL-Method_Advanced.stx')},
87 88
        )
        +AccessControl.Role.RoleManager.manage_options
's avatar
committed
89
        +OFS.SimpleItem.Item.manage_options
90
        )
91

92 93
    # Specify how individual operations add up to "permissions":
    __ac_permissions__=(
94 95 96
        ('View management screens',
         (
        'manage_main', 'index_html',
97
        'manage_advancedForm', 'PrincipiaSearchSource', 'document_src'
98
        )),
99
        ('Change Database Methods',
100
         ('manage_edit','manage_advanced', 'manage_testForm','manage_test',
's avatar
committed
101
          'manage_product_zclass_info', 'PUT')),
102 103
        ('Use Database Methods', ('__call__',''), ('Anonymous','Manager')),
        )
104

105 106

    def __init__(self, id, title, connection_id, arguments, template):
107
        self.id=str(id)
108
        self.manage_edit(title, connection_id, arguments, template)
109

110
    manage_advancedForm=DTMLFile('dtml/advanced', globals())
Jim Fulton's avatar
Jim Fulton committed
111

112
    test_url___roles__=None
Jim Fulton's avatar
Jim Fulton committed
113
    def test_url_(self):
114 115
        'Method for testing server connection information'
        return 'PING'
Jim Fulton's avatar
Jim Fulton committed
116

Jim Fulton's avatar
Jim Fulton committed
117 118 119 120 121 122 123 124 125 126
    _size_changes={
        'Bigger': (5,5),
        'Smaller': (-5,-5),
        'Narrower': (0,-5),
        'Wider': (0,5),
        'Taller': (5,0),
        'Shorter': (-5,0),
        }

    def _er(self,title,connection_id,arguments,template,
Jim Fulton's avatar
Jim Fulton committed
127
            SUBMIT,sql_pref__cols,sql_pref__rows,REQUEST):
Jim Fulton's avatar
Jim Fulton committed
128
        dr,dc = self._size_changes[SUBMIT]
129

Jim Fulton's avatar
Jim Fulton committed
130 131
        rows=max(1,atoi(sql_pref__rows)+dr)
        cols=max(40,atoi(sql_pref__cols)+dc)
132
        e=(DateTime('GMT') + 365).rfc822()
Jim Fulton's avatar
Jim Fulton committed
133
        resp=REQUEST['RESPONSE']
Jim Fulton's avatar
Jim Fulton committed
134 135
        resp.setCookie('sql_pref__rows',str(rows),path='/',expires=e)
        resp.setCookie('sql_pref__cols',str(cols),path='/',expires=e)
Jim Fulton's avatar
Jim Fulton committed
136
        return self.manage_main(
137
            self,REQUEST,
Jim Fulton's avatar
Jim Fulton committed
138 139 140
            title=title,
            arguments_src=arguments,
            connection_id=connection_id,
Jim Fulton's avatar
Jim Fulton committed
141
            src=template,
142
            sql_pref__cols=cols,sql_pref__rows=rows)
Jim Fulton's avatar
Jim Fulton committed
143 144

    def manage_edit(self,title,connection_id,arguments,template,
Jim Fulton's avatar
Jim Fulton committed
145
                    SUBMIT='Change',sql_pref__cols='50', sql_pref__rows='20',
Jim Fulton's avatar
Jim Fulton committed
146
                    REQUEST=None):
147
        """Change database method  properties
Jim Fulton's avatar
Jim Fulton committed
148

149 150 151
        The 'connection_id' argument is the id of a database connection
        that resides in the current folder or in a folder above the
        current folder.  The database should understand SQL.
Jim Fulton's avatar
Jim Fulton committed
152

153 154
        The 'arguments' argument is a string containing an arguments
        specification, as would be given in the SQL method cration form.
Jim Fulton's avatar
Jim Fulton committed
155

156 157 158
        The 'template' argument is a string containing the source for the
        SQL Template.
        """
Jim Fulton's avatar
Jim Fulton committed
159 160 161

        if self._size_changes.has_key(SUBMIT):
            return self._er(title,connection_id,arguments,template,
Jim Fulton's avatar
Jim Fulton committed
162
                            SUBMIT,sql_pref__cols,sql_pref__rows,REQUEST)
Jim Fulton's avatar
Jim Fulton committed
163

164 165
        if self.wl_isLocked():
            raise ResourceLockedError, 'SQL Method is locked via WebDAV'
166

167 168 169
        self.title=str(title)
        self.connection_id=str(connection_id)
        arguments=str(arguments)
170 171
        self.arguments_src=arguments
        self._arg=parse(arguments)
172
        template=str(template)
173
        self.src=template
's avatar
committed
174
        self.template=t=self.template_class(template)
175 176
        t.cook()
        self._v_cache={}, Bucket()
177 178 179
        if REQUEST:
            if SUBMIT=='Change and Test':
                return self.manage_testForm(REQUEST)
180 181
            message='ZSQL Method content changed'
            return self.manage_main(self, REQUEST, manage_tabs_message=message)
182 183
        return ''

's avatar
committed
184

185 186
    def manage_advanced(self, max_rows, max_cache, cache_time,
                        class_name, class_file, direct=None,
187
                        REQUEST=None, zclass='', connection_hook=None):
188
        """Change advanced properties
Jim Fulton's avatar
Jim Fulton committed
189

190
        The arguments are:
Jim Fulton's avatar
Jim Fulton committed
191

192
        max_rows -- The maximum number of rows to be returned from a query.
Jim Fulton's avatar
Jim Fulton committed
193

194
        max_cache -- The maximum number of results to cache
Jim Fulton's avatar
Jim Fulton committed
195

196
        cache_time -- The maximum amound of time to use a cached result.
Jim Fulton's avatar
Jim Fulton committed
197

198 199 200
        class_name -- The name of a class that provides additional
          attributes for result record objects. This class will be a
          base class of the result record class.
Jim Fulton's avatar
Jim Fulton committed
201

202 203
        class_file -- The name of the file containing the class
          definition.
Jim Fulton's avatar
Jim Fulton committed
204

205 206 207 208
        The class file normally resides in the 'Extensions'
        directory, however, the file name may have a prefix of
        'product.', indicating that it should be found in a product
        directory.
Jim Fulton's avatar
Jim Fulton committed
209

210 211 212 213 214
        For example, if the class file is: 'ACMEWidgets.foo', then an
        attempt will first be made to use the file
        'lib/python/Products/ACMEWidgets/Extensions/foo.py'. If this
        failes, then the file 'Extensions/ACMEWidgets.foo.py' will be
        used.
215

216
        """
217 218 219 220 221 222
        # paranoid type checking
        if type(max_rows) is not type(1):
            max_rows=atoi(max_rows)
        if type(max_cache) is not type(1):
            max_cache=atoi(max_cache)
        if type(cache_time) is not type(1):
223
            cache_time=atoi(cache_time)
224 225
        class_name=str(class_name)
        class_file=str(class_file)
226

227 228 229 230 231
        self.max_rows_ = max_rows
        self.max_cache_, self.cache_time_ = max_cache, cache_time
        self._v_cache={}, Bucket()
        self.class_name_, self.class_file_ = class_name, class_file
        self._v_brain=getBrain(self.class_file_, self.class_name_, 1)
232
        self.allow_simple_one_argument_traversal=direct
233

234 235
        self.connection_hook = connection_hook

236 237 238 239 240 241
        if zclass:
            for d in self.aq_acquire('_getProductRegistryData')('zclasses'):
                if ("%s/%s" % (d.get('product'),d.get('id'))) == zclass:
                    self._zclass=d['meta_class']
                    break

242

243
        if REQUEST is not None:
244 245 246
            m="ZSQL Method advanced settings have been set"
            return self.manage_advancedForm(self,REQUEST,manage_tabs_message=m)
##            return self.manage_editedDialog(REQUEST)
's avatar
committed
247

248 249 250
    #def getFindContent(self):
    #    """Return content for use by the Find machinery."""
    #    return '%s\n%s' % (self.arguments_src, self.src)
251 252 253 254

    def PrincipiaSearchSource(self):
        """Return content for use by the Find machinery."""
        return '%s\n%s' % (self.arguments_src, self.src)
255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274


    # WebDAV / FTP support

    default_content_type = 'text/plain'

    def document_src(self, REQUEST=None, RESPONSE=None):
        """Return unprocessed document source."""
        if RESPONSE is not None:
            RESPONSE.setHeader('Content-Type', 'text/plain')
        return '<params>%s</params>\n%s' % (self.arguments_src, self.src)

    def manage_FTPget(self):
        """Get source for FTP download"""
        self.REQUEST.RESPONSE.setHeader('Content-Type', 'text/plain')
        return '<params>%s</params>\n%s' % (self.arguments_src, self.src)

    def PUT(self, REQUEST, RESPONSE):
        """Handle put requests"""
        self.dav__init(REQUEST, RESPONSE)
275
        self.dav__simpleifhandler(REQUEST, RESPONSE, refresh=1)
276
        body = REQUEST.get('BODY', '')
277
        m = re.match('\s*<params>(.*)</params>\s*\n', body, re.I | re.S)
278 279 280 281 282 283 284 285 286 287 288 289 290
        if m:
            self.arguments_src = m.group(1)
            self._arg=parse(self.arguments_src)
            body = body[m.end():]
        template = body
        self.src = template
        self.template=t=self.template_class(template)
        t.cook()
        self._v_cache={}, Bucket()
        RESPONSE.setStatus(204)
        return RESPONSE


291
    def manage_testForm(self, REQUEST):
292 293 294
        " "
        input_src=default_input_form(self.title_or_id(),
                                     self._arg, 'manage_test',
295
                                     '<dtml-var manage_tabs>')
296
        return DocumentTemplate.HTML(input_src)(self, REQUEST, HTTP_REFERER='')
297 298

    def manage_test(self, REQUEST):
299
        """Test an SQL method."""
300 301 302 303 304
        # Try to render the query template first so that the rendered
        # source will be available for the error message in case some
        # error occurs...
        try:    src=self(REQUEST, src__=1)
        except: src="Could not render the query template!"
305
        result=()
306 307 308
        t=v=tb=None
        try:
            try:
309
                src, result=self(REQUEST, test__=1)
310 311
                if find(src,'\0'):
                    src=join(split(src,'\0'),'\n'+'-'*60+'\n')
312 313 314
                if result._searchable_result_columns():
                    r=custom_default_report(self.id, result)
                else:
315
                    r='This statement returned no results.'
316 317 318 319 320
            except:
                t, v, tb = sys.exc_info()
                r='<strong>Error, <em>%s</em>:</strong> %s' % (t, v)

            report=DocumentTemplate.HTML(
321 322
                '<html>\n'
                '<BODY BGCOLOR="#FFFFFF" LINK="#000099" VLINK="#555555">\n'
323
                '<dtml-var manage_tabs>\n<hr>\n%s\n\n'
324 325 326 327 328 329 330 331 332 333 334
                '<hr><strong>SQL used:</strong><br>\n<pre>\n%s\n</pre>\n<hr>\n'
                '</body></html>'
                % (r,src))

            report=apply(report,(self,REQUEST),{self.id:result})

            if tb is not None:
                self.raise_standardErrorMessage(
                    None, REQUEST, t, v, tb, None, report)

            return report
335

336
        finally: tb=None
Jim Fulton's avatar
Jim Fulton committed
337

338 339 340
    def index_html(self, REQUEST):
        """ """
        REQUEST.RESPONSE.redirect("%s/manage_testForm" % REQUEST['URL1'])
341 342 343 344 345

    def _searchable_arguments(self): return self._arg

    def _searchable_result_columns(self): return self._col

346
    def _cached_result(self, DB__, query):
347 348 349 350 351
        pure_query = query
        # we need to munge the incoming query key in the cache
        # so that the same request to a different db is returned
        query += '\nDBConnId: %s' % self.connection_hook
        
352 353 354 355 356 357 358 359 360 361 362 363 364 365
        # Try to fetch from cache
        if hasattr(self,'_v_cache'): cache=self._v_cache
        else: cache=self._v_cache={}, Bucket()
        cache, tcache = cache
        max_cache=self.max_cache_
        now=time()
        t=now-self.cache_time_
        if len(cache) > max_cache / 2:
            keys=tcache.keys()
            keys.reverse()
            while keys and (len(keys) > max_cache or keys[-1] < t):
                key=keys[-1]
                q=tcache[key]
                del tcache[key]
366 367
                if int(cache[q][0]) == key:
                    del cache[q]
368
                del keys[-1]
369

370 371 372 373
        if cache.has_key(query):
            k, r = cache[query]
            if k > t: return r

374 375
        # call the pure query
        result=apply(DB__.query, pure_query)
376
        if self.cache_time_ > 0:
377 378
            tcache[int(now)]=query
            cache[query]= now, result
379

380
        return result
381

382
    def __call__(self, REQUEST=None, __ick__=None, src__=0, test__=0, **kw):
383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399
        """Call the database method

        The arguments to the method should be passed via keyword
        arguments, or in a single mapping object. If no arguments are
        given, and if the method was invoked through the Web, then the
        method will try to acquire and use the Web REQUEST object as
        the argument mapping.

        The returned value is a sequence of record objects.
        """

        if REQUEST is None:
            if kw: REQUEST=kw
            else:
                if hasattr(self, 'REQUEST'): REQUEST=self.REQUEST
                else: REQUEST={}

400 401 402 403 404 405 406 407
        # connection hook
        c = self.connection_id
        # for backwards compatability
        hk = self.connection_hook
        # go get the connection hook and call it
        if hk: c = getattr(self, hk)()
           
        try: dbc=getattr(self, c)
408 409
        except AttributeError:
            raise AttributeError, (
410
                "The database connection <em>%s</em> cannot be found." % (
411
                c))
412 413 414 415

        try: DB__=dbc()
        except: raise 'Database Error', (
            '%s is not connected to a database' % self.id)
416

417 418
        if hasattr(self, 'aq_parent'):
            p=self.aq_parent
419 420
            if self._isBeingAccessedAsZClassDefinedInstanceMethod():
                p=p.aq_parent
421 422 423 424 425
        else: p=None

        argdata=self._argdata(REQUEST)
        argdata['sql_delimiter']='\0'
        argdata['sql_quote__']=dbc.sql_quote__
's avatar
committed
426

427 428
        security=getSecurityManager()
        security.addContext(self)
429 430 431 432 433 434 435 436
        try:
            try:     query=apply(self.template, (p,), argdata)
            except TypeError, msg:
                msg = str(msg)
                if find(msg,'client'):
                    raise NameError("'client' may not be used as an " +
                        "argument name in this context")
                else: raise
437
        finally: security.removeContext(self)
438 439 440

        if src__: return query

Jim Fulton's avatar
Jim Fulton committed
441
        if self.cache_time_ > 0 and self.max_cache_ > 0:
442 443 444 445 446
            result=self._cached_result(DB__, (query, self.max_rows_))
        else: result=DB__.query(query, self.max_rows_)

        if hasattr(self, '_v_brain'): brain=self._v_brain
        else:
447
            brain=self._v_brain=getBrain(self.class_file_, self.class_name_)
448

449 450 451
        zc=self._zclass
        if zc is not None: zc=zc._zclass_

452 453 454
        if type(result) is type(''):
            f=StringIO()
            f.write(result)
455
            f.seek(0)
456
            result=RDB.File(f,brain,p, zc)
457
        else:
458
            result=Results(result, brain, p, zc)
459
        columns=result._searchable_result_columns()
460
        if test__ and columns != self._col: self._col=columns
461 462 463 464 465

        # If run in test mode, return both the query and results so
        # that the template doesn't have to be rendered twice!
        if test__: return query, result

466 467
        return result

468
    def da_has_single_argument(self): return len(self._arg)==1
469 470

    def __getitem__(self, key):
471 472 473 474 475 476 477 478 479
        args=self._arg
        if self.allow_simple_one_argument_traversal and len(args)==1:
            results=self({args.keys()[0]: key})
            if results:
                if len(results) > 1: raise KeyError, key
            else: raise KeyError, key
            r=results[0]
            # if hasattr(self, 'aq_parent'): r=r.__of__(self.aq_parent)
            return r
480

481 482
        self._arg[key] # raise KeyError if not an arg
        return Traverse(self,{},key)
483

484
    def connectionIsValid(self):
485 486
        return (hasattr(self, self.connection_id) and
                hasattr(getattr(self, self.connection_id), 'connected'))
487 488

    def connected(self):
489
        return getattr(getattr(self, self.connection_id), 'connected')()
490

491 492 493 494 495 496 497 498 499 500 501 502 503 504 505

    def manage_product_zclass_info(self):
        r=[]
        Z=self._zclass
        Z=getattr(Z, 'aq_self', Z)
        for d in self.aq_acquire('_getProductRegistryData')('zclasses'):
            z=d['meta_class']
            if hasattr(z._zclass_,'_p_deactivate'):
                # Eek, persistent
                continue
            x={}
            x.update(d)
            x['selected'] = (z is Z) and 'selected' or ''
            del x['meta_class']
            r.append(x)
506 507

        return r
508

's avatar
committed
509 510


511 512 513 514
Globals.default__class_init__(DA)



515
ListType=type([])
516
class Traverse(ExtensionClass.Base):
517 518 519 520 521 522
    """Helper class for 'traversing' searches during URL traversal
    """
    _r=None
    _da=None

    def __init__(self, da, args, name=None):
523 524 525
        self._da=da
        self._args=args
        self._name=name
526 527

    def __bobo_traverse__(self, REQUEST, key):
528 529 530 531 532 533 534 535 536 537 538 539
        name=self._name
        da=self.__dict__['_da']
        args=self._args
        if name:
            if args.has_key(name):
                v=args[name]
                if type(v) is not ListType: v=[v]
                v.append(key)
                key=v

            args[name]=key

540
            if len(args) < len(da._arg):
541 542 543 544 545 546 547 548 549 550 551
                return self.__class__(da, args)
            key=self # "consume" key

        elif da._arg.has_key(key): return self.__class__(da, args, key)

        results=da(args)
        if results:
            if len(results) > 1:
                try: return results[atoi(key)].__of__(da)
                except: raise KeyError, key
        else: raise KeyError, key
552 553 554 555
        r=results[0]
        # if hasattr(da, 'aq_parent'): r=r.__of__(da.aq_parent)
        self._r=r

556 557 558 559 560 561 562 563
        if key is self: return r

        if hasattr(r,'__bobo_traverse__'):
            try: return r.__bobo_traverse__(REQUEST, key)
            except: pass

        try: return getattr(r,key)
        except AttributeError, v:
564
            if str(v) != key: raise AttributeError, v
565 566

        return r[key]
567 568

    def __getattr__(self, name):
569 570 571
        r=self.__dict__['_r']
        if hasattr(r, name): return getattr(r,name)
        return getattr(self.__dict__['_da'], name)