root/livinglogic.python.orasql/src/ll/orasql/__init__.py @ 236:bc9100e6888e

Revision 236:bc9100e6888e, 78.1 KB (checked in by Walter Doerwald <walter@…>, 12 years ago)

Add a getobject() method to Connection and Synonym. procedure/function name are casesensitive now.

Connection.getobject() returns the schema object with a specified name.

Synonym.getobject() returns the object for which the Synonym object is a synonym.

Line 
1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3
4## Copyright 2004-2008 by LivingLogic AG, Bayreuth/Germany.
5## Copyright 2004-2008 by Walter Dörwald
6##
7## All Rights Reserved
8##
9## Permission to use, copy, modify, and distribute this software and its documentation
10## for any purpose and without fee is hereby granted, provided that the above copyright
11## notice appears in all copies and that both that copyright notice and this permission
12## notice appear in supporting documentation, and that the name of LivingLogic AG or
13## the author not be used in advertising or publicity pertaining to distribution of the
14## software without specific, written prior permission.
15##
16## LIVINGLOGIC AG AND THE AUTHOR DISCLAIM ALL WARRANTIES WITH REGARD TO THIS SOFTWARE,
17## INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS, IN NO EVENT SHALL
18## LIVINGLOGIC AG OR THE AUTHOR BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL
19## DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER
20## IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR
21## IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
22
23
24"""
25:mod:`ll.orasql` contains utilities for working with cx_Oracle__:
26
27*   It allows calling procedures with keyword arguments (via the
28    :class:`Procedure` class).
29
30*   Query results will be put into :class:`Record` objects, where database
31    fields are accessible as object attributes.
32
33*   The :class:`Connection` class provides methods for iterating through the
34    database metadata.
35
36__ http://www.computronix.com/utilities.shtml#Oracle
37"""
38
39
40import datetime, itertools
41
42from cx_Oracle import *
43
44from ll import misc
45
46try:
47    import astyle
48except ImportError:
49    from ll import astyle
50
51
52# ipipe support
53try:
54    import ipipe
55except ImportError:
56    ipipe = None
57
58
59__docformat__ = "reStructuredText"
60
61
62style_connection = astyle.style_url
63
64
65class SQLObjectNotFoundError(Exception):
66    def __init__(self, obj):
67        self.obj = obj
68
69    def __str__(self):
70        return "%r not found" % self.obj
71
72
73class SQLNoSuchObjectError(Exception):
74    def __init__(self, name, owner):
75        self.name = name
76        self.owner = owner
77
78    def __str__(self):
79        if self.owner is None:
80            return "no object named %r" % (self.name, )
81        else:
82            return "no object named %r for owner %r" % (self.name, self.owner)
83
84
85class UnknownModeError(ValueError):
86    def __init__(self, mode):
87        self.mode = mode
88
89    def __str__(self):
90        return "unknown mode %r" % self.mode
91
92
93class UnknownSchemaError(ValueError):
94    def __init__(self, schema):
95        self.schema = schema
96
97    def __str__(self):
98        return "unknown schema %r" % self.schema
99
100
101class ConflictError(ValueError):
102    def __init__(self, object, message):
103        self.object = object
104        self.message = message
105
106    def __str__(self):
107        return "conflict in %r: %s" % (self.object, self.message)
108
109
110_default = object() # marker object for unset parameters
111
112
113class Record(dict):
114    """
115    A :class:`Record` is a subclass of :class:`dict` that is used for storing
116    results of database queries. Both item and attribute access (i.e.
117    :meth:`__getitem__` and :meth:`__getattr__`) are available. Field names are
118    case insensitive.
119    """
120    def __init__(self, arg=None, **kwargs):
121        dict.__init__(self)
122        self.update(arg, **kwargs)
123
124    def update(self, arg=None, **kwargs):
125        if arg is not None:
126            # if arg is a mapping use iteritems
127            dict.update(self, ((key.lower(), value) for (key, value) in getattr(arg, "iteritems", arg)))
128        dict.update(self, ((key.lower(), value) for (key, value) in kwargs.iteritems()))
129
130    @classmethod
131    def fromdata(cls, cursor, row):
132        """
133        This class method can be used to create a :class:`Record` object from the
134        database data.
135        """
136        return cls((descr[0].lower(), cursor._decode(field, descr[1] in (BLOB, BINARY))) for (descr, field) in itertools.izip(cursor.description, row))
137
138    def __getitem__(self, name):
139        return dict.__getitem__(self, name.lower())
140
141    def __setitem__(self, name, value):
142        dict.__setitem__(self, name.lower(), value)
143
144    def __delitem__(self, name):
145        dict.__delitem__(self, name.lower())
146
147    def __getattr__(self, name):
148        try:
149            return self.__getitem__(name)
150        except KeyError:
151            raise AttributeError(name)
152
153    def __setattr__(self, name, value):
154        self.__setitem__(name, value)
155
156    def __delattr__(self, name):
157        try:
158            self.__delitem__(name)
159        except KeyError:
160            raise AttributeError(name)
161
162    def __xattrs__(self, mode="default"):
163        # Return the attributes of this record. This is for interfacing with ipipe
164        return self.iterkeys()
165
166    def __xrepr__(self, mode):
167        if mode == "header":
168            yield (astyle.style_default, "%s.%s" % (self.__class__.__module__, self.__class__.__name__))
169        else:
170            yield (astyle.style_default, repr(self))
171
172    def __repr__(self):
173        return "%s.%s(%s)" % (self.__class__.__module__, self.__class__.__name__, ", ".join("%s=%r" % item for item in self.iteritems()))
174
175
176class _AllTypes(object):
177    def __init__(self, connection, class_, schema, count):
178        self.connection = connection
179        self.class_ = class_
180        self.type = class_.type
181        self.schema = schema
182        self.count = count
183
184    def __xattrs__(self, mode="default"):
185        return ("type", "count")
186
187    def __xrepr__(self, mode):
188        if mode == "header" or mode == "footer":
189            yield (astyle.style_default, self.type + "s")
190        else:
191            yield (astyle.style_default, repr(self))
192
193    def __iter__(self):
194        return self.class_.iterobjects(self.connection, self.schema)
195
196
197class SessionPool(SessionPool):
198    """
199    :class:`SessionPool` is a subclass of :class:`cx_Oracle.SessionPool`.
200    """
201
202    def __init__(self, user, password, database, min, max, increment, connectiontype=None, threaded=False):
203        if connectiontype is None:
204            connectiontype = Connection
205        super(SessionPool, self).__init__(user, password, database, min, max, increment, connectiontype, threaded)
206
207    def connectstring(self):
208        return "%s@%s" % (self.username, self.tnsentry)
209
210    def __repr__(self):
211        return "<%s.%s object db=%r at 0x%x>" % (self.__class__.__module__, self.__class__.__name__, self.connectstring(), id(self))
212
213
214class Connection(Connection):
215    """
216    :class:`Connection` is a subclass of :class:`cx_Oracle.Connection`.
217    """
218    def __init__(self, *args, **kwargs):
219        """
220        Create a new connection. In addition to the parameters supported by
221        :func:`cx_Oracle.connect` the following keyword argument is supported.
222
223        :var:`readlobs` : bool or integer
224            If :var:`readlobs` is :const:`False` all cursor fetch return
225            :class:`LOB` objects as usual. If :var:`readlobs` is an :class:`int`
226            (or :class:`long`) :class:`LOB`s with a maximum size of :var:`readlobs`
227            will be returned as strings. If :var:`readlobs` is :const:`True`
228            all :class:`LOB` values will be returned as strings.
229        """
230        if "readlobs" in kwargs or "unicode" in kwargs:
231            kwargs = kwargs.copy()
232            self.readlobs = kwargs.pop("readlobs", False)
233            self.unicode = kwargs.pop("unicode", False)
234        else:
235            self.readlobs = False
236            self.unicode = False
237        super(Connection, self).__init__(*args, **kwargs)
238
239    def connectstring(self):
240        return "%s@%s" % (self.username, self.tnsentry)
241
242    def cursor(self, readlobs=None):
243        """
244        Return a new cursor for this connection. For the meaning of
245        :var:`readlobs` see :meth:`__init__`.
246        """
247        return Cursor(self, readlobs=readlobs)
248
249    def __repr__(self):
250        return "<%s.%s object db=%r at 0x%x>" % (self.__class__.__module__, self.__class__.__name__, self.connectstring(), id(self))
251
252    def __xrepr__(self, mode):
253        if mode == "header" or mode=="footer":
254            yield (astyle.style_default, "oracle connection to %s" % self.connectstring())
255        elif mode == "cell":
256            yield (style_connection, self.connectstring())
257        else:
258            yield (astyle.style_default, repr(self))
259
260    def iterschema(self, schema="user"):
261        """
262        Generator that returns the number of different object types for this
263        database. For the meaning of :var:`schema` see :meth:`iterobjects`.
264        """
265        cursor = self.cursor()
266        if schema == "all":
267            cursor.xexecute("select object_type as type, count(*) as count from all_objects group by object_type")
268        else:
269            cursor.xexecute("select object_type as type, count(*) as count from user_objects group by object_type")
270        for row in cursor.xfetchall():
271            class_ = Object.name2type.get(row.type.lower(), None)
272            if class_ is not None:
273                yield _AllTypes(self, class_, schema, row.count)
274        if schema == "all":
275            cursor.execute("select count(*) as count from all_tab_privs")
276        else:
277            cursor.execute("select count(*) as count from user_tab_privs where owner=user")
278        yield _AllTypes(self, Privilege, schema, cursor.fetchone()[0])
279
280    def itertables(self, schema="user"):
281        """
282        Generator that yields all table definitions in the current users schema
283        (or all users schemas). :var:`schema` specifies from which tables should
284        be yielded:
285
286        ``"user"``
287            Only tables belonging to the current user (and those objects these
288            depend on) will be yielded.
289
290        ``"all"``
291            All tables from all users will be yielded.
292
293        Tables that are materialized view will be skipped in both casess.
294        """
295        if schema not in ("user", "all"):
296            raise UnknownSchemaError(schema)
297
298        cursor = self.cursor()
299
300        if schema == "all":
301            cursor.execute("select decode(owner, user, null, owner) as owner, table_name from all_tables minus select decode(owner, user, null, owner) as owner, mview_name as table_name from all_mviews")
302        else:
303            cursor.execute("select null as owner, table_name from user_tables minus select null as owner, mview_name as table_name from user_mviews")
304        for rec in cursor.xfetchall():
305            yield Table(rec.table_name, rec.owner, self)
306
307    def iterfks(self, schema="user"):
308        """
309        Generator that yields all foreign key constraints in the current users
310        schema (or all users schemas). :var:`schema` specifies from which tables
311        should be yielded:
312
313        ``"user"``
314            Only tables belonging to the current user (and those objects these
315            depend on) will be yielded.
316
317        ``"all"``
318            All tables from all users will be yielded.
319        """
320        if schema not in ("user", "all"):
321            raise UnknownSchemaError(schema)
322
323        cursor = self.cursor()
324
325        if schema == "all":
326            cursor.execute("select decode(owner, user, null, owner) as owner, constraint_name from all_constraints where constraint_type='R' order by owner, table_name, constraint_name")
327        else:
328            cursor.execute("select null as owner, constraint_name from user_constraints where constraint_type='R' order by table_name, constraint_name")
329        for rec in cursor.xfetchall():
330            yield ForeignKey(rec.constraint_name, rec.owner, self)
331
332    def iterobjects(self, mode="create", schema="user"):
333        """
334        Generator that yields the sequences, tables, primary keys, foreign keys,
335        comments, unique constraints, indexes, views, functions, procedures,
336        packages and types in the current users schema (or all users schemas)
337        in a specified order.
338
339        :var:`mode` specifies the order in which objects will be yielded:
340
341        ``"create"``
342            Create order, i.e. recreating the objects in this order will not lead
343            to errors.
344
345        ``"drop"``
346            Drop order, i.e. dropping the objects in this order will not lead to
347            errors.
348
349        ``"flat"``
350            Unordered.
351
352        :var:`schema` specifies from which schema objects should be yielded:
353
354        ``"user"``
355            Only objects belonging to the current user (and those objects these
356            depend on) will be yielded.
357
358        ``"all"``
359            All objects from all users will be yielded.
360        """
361        if mode not in ("create", "drop", "flat"):
362            raise UnknownModeError(mode)
363
364        if schema not in ("user", "all"):
365            raise UnknownSchemaError(schema)
366
367        done = set()
368
369        cursor = self.cursor()
370
371        def do(obj):
372            if mode == "create":
373                for subobj in obj.iterreferencesall(self, done):
374                    yield subobj
375            elif mode == "drop":
376                for subobj in obj.iterreferencedbyall(self, done):
377                    yield subobj
378            else:
379                if obj not in done:
380                    done.add(obj)
381                    yield obj
382
383        def dosequences():
384            # select * from all_sequences where sequence_owner=nvl(:owner, user) and sequence_name=:name
385            if schema == "all":
386                cursor.xexecute("select decode(sequence_owner, user, null, sequence_owner) as sequence_owner, sequence_name from all_sequences")
387            else:
388                cursor.xexecute("select null as sequence_owner, sequence_name from user_sequences")
389            for rec in cursor.xfetchall():
390                for obj in do(Sequence(rec.sequence_name, rec.sequence_owner, self)):
391                    yield obj
392
393        def dotables():
394            if schema == "all":
395                cursor.xexecute("select decode(owner, user, null, owner) as owner, table_name from all_tables")
396            else:
397                cursor.xexecute("select null as owner, table_name from user_tables")
398            for rec in cursor.xfetchall():
399                obj = Table(rec.table_name, rec.owner, self)
400                if mode == "create" or mode == "flat":
401                    for subobj in do(obj):
402                        yield subobj
403   
404                if not obj.ismview(self):
405                    # Primary key
406                    if schema == "all":
407                        cursor.xexecute("select decode(owner, user, null, owner) as owner, constraint_name from all_constraints where constraint_type='P' and owner=:owner and table_name=:name", owner=rec.owner, name=rec.table_name)
408                    else:
409                        cursor.xexecute("select null as owner, constraint_name from user_constraints where constraint_type='P' and table_name=:name", name=rec.table_name)
410                    for rec2 in cursor.xfetchall():
411                        for subobj in do(PrimaryKey(rec2.constraint_name, rec2.owner, self)):
412                            yield subobj
413   
414                    # Comments
415                    if schema == "all":
416                        cursor.xexecute("select column_name from all_tab_columns where owner=:owner and table_name=:name order by column_id", owner=rec.owner, name=rec.table_name)
417                    else:
418                        cursor.xexecute("select column_name from user_tab_columns where table_name=:name order by column_id", name=rec.table_name)
419                    for rec2 in cursor.xfetchall():
420                        # No dependency checks neccessary, but use do anyway
421                        for subobj in do(Comment("%s.%s" % (rec.table_name, rec2.column_name), rec.owner, self)):
422                            yield subobj
423
424                if mode == "drop":
425                    for subobj in do(obj):
426                        yield subobj
427
428        def doconstraints():
429            if schema == "all":
430                cursor.xexecute("select constraint_type, decode(owner, user, null, owner) as owner, constraint_name from all_constraints where constraint_type in ('R', 'U') order by owner, table_name, constraint_type, constraint_name")
431            else:
432                cursor.xexecute("select constraint_type, null as owner, constraint_name from user_constraints where constraint_type in ('R', 'U') order by table_name, constraint_type, constraint_name")
433            types = {"U": UniqueConstraint, "R": ForeignKey}
434            for rec in cursor.xfetchall():
435                for subobj in do(types[rec.constraint_type](rec.constraint_name, rec.owner, self)):
436                    yield subobj
437
438        def doindexes():
439            if schema == "all":
440                cursor.xexecute("select decode(owner, user, null, owner) as owner, index_name from all_indexes where index_type in ('NORMAL', 'FUNCTION-BASED NORMAL') order by owner, table_name, index_name")
441            else:
442                cursor.xexecute("select null as owner, index_name from user_indexes where index_type in ('NORMAL', 'FUNCTION-BASED NORMAL') order by table_name, index_name")
443            for rec in cursor.xfetchall():
444                for subobj in do(Index(rec.index_name, rec.owner, self)):
445                    yield subobj
446
447        def dosynonyms():
448            if schema == "all":
449                cursor.xexecute("select decode(owner, user, null, owner) as owner, synonym_name from all_synonyms")
450            else:
451                cursor.xexecute("select null as owner, synonym_name from user_synonyms")
452            for rec in cursor.xfetchall():
453                for subobj in do(Synonym(rec.synonym_name, rec.owner, self)):
454                    yield subobj
455
456        def doviews():
457            if schema == "all":
458                cursor.xexecute("select decode(owner, user, null, owner) as owner, view_name from all_views")
459            else:
460                cursor.xexecute("select null as owner, view_name from user_views")
461            for rec in cursor.xfetchall():
462                for subobj in do(View(rec.view_name, rec.owner, self)):
463                    yield subobj
464
465        def domviews():
466            if schema == "all":
467                cursor.xexecute("select decode(owner, user, null, owner) as owner, mview_name from all_mviews")
468            else:
469                cursor.xexecute("select null as owner, mview_name from user_mviews")
470            for rec in cursor.xfetchall():
471                for subobj in do(MaterializedView(rec.mview_name, rec.owner, self)):
472                    yield subobj
473
474        def docode():
475            for type in (Function, Procedure, Package, PackageBody, Type, Trigger, JavaSource):
476                if schema == "all":
477                    cursor.xexecute("select decode(owner, user, null, owner) as owner, object_name from all_objects where lower(object_type)=lower(:type)", type=type.type)
478                else:
479                    cursor.xexecute("select null as owner, object_name from user_objects where lower(object_type)=lower(:type)", type=type.type)
480                for rec in cursor.xfetchall():
481                    for subobj in do(type(rec.object_name, rec.owner, self)):
482                        yield subobj
483
484        funcs = [dosequences, dotables, doconstraints, doindexes, dosynonyms, doviews, domviews, docode]
485        if mode == "drop":
486            funcs = reversed(funcs)
487
488        for func in funcs:
489            for obj in func():
490                yield obj
491
492    def getobject(self, name, owner=None):
493        """
494        Return the object named :var:`name` from the schema. If :var:`owner` is
495        :const:`None` the current schema is queried, else the specified one is
496        used.
497        """
498        cursor = self.cursor()
499        for query in (
500            "select object_name, decode(owner, user, null, owner) as owner, object_type from all_objects where object_name = :object_name and owner = nvl(:owner, user)",
501            "select object_name, decode(owner, user, null, owner) as owner, object_type from all_objects where object_name = :object_name and lower(owner) = lower(nvl(:owner, user))",
502            "select object_name, decode(owner, user, null, owner) as owner, object_type from all_objects where lower(object_name) = lower(:object_name) and owner = nvl(:owner, user)",
503            "select object_name, decode(owner, user, null, owner) as owner, object_type from all_objects where lower(object_name) = lower(:object_name) and lower(owner) = lower(nvl(:owner, user))",
504        ):
505            cursor.xexecute(query, object_name=name, owner=owner)
506            rec = cursor.xfetchone()
507            if rec is not None:
508                type = rec.object_type.lower()
509                try:
510                    cls = Object.name2type[type]
511                except KeyError:
512                    raise TypeError("type %s not supported" % type)
513                else:
514                    return cls(rec.object_name, rec.owner, self)
515        raise SQLNoSuchObjectError(name, owner)
516
517
518    def iterprivileges(self, schema="user"):
519        """
520        Generator that yields object privileges for the current users (or all
521        users) objects. :var:`schema` specifies which privileges should be
522        yielded:
523
524        ``"user"``
525            Only object privileges for objects belonging to the current user will
526            be yielded.
527
528        ``"all"``
529            All object privileges will be yielded.
530        """
531        return Privilege.iterobjects(self, schema)
532
533
534def connect(*args, **kwargs):
535    """
536    Create a connection to the database and return a :class:`Connection` object.
537    """
538    return Connection(*args, **kwargs)
539
540
541class Cursor(Cursor):
542    """
543    A subclass of the cursor class in :mod:`cx_Oracle`. A :class:`Cursor` object
544    provides the following additional methods: :meth:`xexecute`,
545    :meth:`xexecutemany`, :meth:`xfetchone`, :meth:`xfetchmany`, :meth:`xfetchall`
546    and :meth:`xfetch`. The "execute" methods support a unicode statement and
547    unicode parameters (they will be encoded in the client encoding before being
548    passed to the database). The "fetch" methods will return records as
549    :class:`Record` objects and string values and ``CLOB`` values, if the
550    cursors :attr:`readlobs` attribute has the appropriate value) will be
551    returned as :class:`unicode` objects (except for :class:`BLOB` values).
552    (Note that strings in the national character set (and :class:`NCLOB` values)
553    are not supported).
554    """
555    def __init__(self, connection, readlobs=None):
556        """
557        Return a new cursor for the connection :var:`connection`. For the meaning
558        of :var:`readlobs` see :meth:`Connection.__init__.
559        """
560        super(Cursor, self).__init__(connection)
561        self.readlobs = (readlobs if readlobs is not None else connection.readlobs)
562
563    def _encode(self, value):
564        # Helper method that encodes :var:`value` using the client encoding (if :var:`value` is :class:`unicode`)
565        if isinstance(value, unicode):
566            return value.encode(self.connection.encoding)
567        return value
568
569    def _decode(self, value, isblob):
570        if isinstance(value, LOB) and (self.readlobs is True or (isinstance(self.readlobs, (int, long)) and value.size() <= self.readlobs)):
571            value = value.read()
572        if isinstance(value, str) and not isblob:
573            value = value.decode(self.connection.encoding)
574        return value
575
576    def xexecute(self, statement, parameters=None, **kwargs):
577        kwargs = dict((self._encode(key), self._encode(value)) for (key, value) in kwargs.iteritems())
578        if parameters is not None:
579            if isinstance(parameters, dict):
580                parameters = dict((self._encode(key), self._encode(value)) for (key, value) in parameters.iteritems())
581            else:
582                parameters = map(self._encode, parameters)
583            return self.execute(self._encode(statement), parameters, **kwargs)
584        return self.execute(self._encode(statement), **kwargs)
585
586    def xexecutemany(self, statement, parameters):
587        def _encode(value):
588            if isinstance(value, dict):
589                return dict((self._encode(key), self._encode(value)) for (key, value) in value.iteritems())
590            else:
591                return map(self._encode, value)
592        return self.executemany(self._encode(statement), map(_encode, parameters))
593
594    def xfetchone(self, type=Record):
595        row = self.fetchone()
596        if row is not None:
597            row = type.fromdata(self, row)
598        return row
599
600    def xfetchmany(self, rows=0, type=Record):
601        return [type.fromdata(self, row) for row in self.fetchmany(rows)]
602
603    def xfetchall(self, type=Record):
604        return [type.fromdata(self, row) for row in self.fetchall()]
605
606    def xfetch(self, type=Record):
607        while True:
608            yield type.fromdata(self, self.next())
609
610    def __xrepr__(self, mode):
611        if mode == "header" or mode == "footer":
612            if self.statement:
613                yield (astyle.style_default, self.statement)
614            else:
615                yield (astyle.style_default, "no statement")
616        else:
617            yield (astyle.style_default, repr(self))
618
619    def __repr__(self):
620        return "<%s.%s statement=%r at 0x%x>" % (self.__class__.__module__, self.__class__.__name__, self.statement, id(self))
621
622
623def formatstring(value, latin1=False):
624    result = []
625    current = []
626
627    if latin1:
628        upper = 255
629    else:
630        upper = 127
631    # Helper function: move the content of current to result
632    def shipcurrent(force=False):
633        if current and (force or (len(current) > 2000)):
634            if result:
635                result.append(" || ")
636            result.append("'%s'" % "".join(current))
637
638    for c in value:
639        if c == "'":
640            current.append("''")
641            shipcurrent()
642        elif ord(c) < 32 or ord(c)>upper:
643            shipcurrent(True)
644            current = []
645            if result:
646                result.append(" || ")
647            result.append("chr(%d)" % ord(c))
648        else:
649            current.append(c)
650            shipcurrent()
651    shipcurrent(True)
652    return "".join(result)
653
654
655class MixinNormalDates(object):
656    """
657    Mixin class that provides methods for determining creation and modification
658    dates for objects.
659    """
660    def cdate(self, connection=None):
661        (connection, cursor) = self.getcursor(connection)
662        cursor.xexecute("select created from all_objects where lower(object_type)=:type and object_name=:name and owner=nvl(:owner, user)", type=self.__class__.type, name=self.name, owner=self.owner)
663        row = cursor.xfetchone()
664        if row is None:
665            raise SQLObjectNotFoundError(self)
666        return row.created
667
668    def udate(self, connection=None):
669        (connection, cursor) = self.getcursor(connection)
670        cursor.xexecute("select last_ddl_time from all_objects where lower(object_type)=:type and object_name=:name and owner=nvl(:owner, user)", type=self.__class__.type, name=self.name, owner=self.owner)
671        row = cursor.xfetchone()
672        if row is None:
673            raise SQLObjectNotFoundError(self)
674        return row.last_ddl_time
675
676
677class MixinCodeDDL(object):
678    """
679    Mixin class that provides methods returning the create and drop statements
680    for various objects.
681    """
682    def createddl(self, connection=None, term=True):
683        (connection, cursor) = self.getcursor(connection)
684        cursor.xexecute("select text from all_source where lower(type)=lower(:type) and owner=nvl(:owner, user) and name=:name order by line", type=self.__class__.type, owner=self.owner, name=self.name)
685        code = "\n".join((rec.text or "").rstrip() for rec in cursor.xfetch()) # sqlplus strips trailing spaces when executing SQL scripts, so we do that too
686        if not code:
687            raise SQLObjectNotFoundError(self)
688        code = " ".join(code.split(None, 1)) # compress "PROCEDURE          FOO"
689        code = code.strip()
690        if self.owner is not None:
691            type = self.__class__.type
692            code = code[code.lower().find(type)+len(type):].strip() # drop "procedure" etc.
693            code = "create or replace %s %s.%s\n" % (type, self.owner, code)
694        else:
695            code = "create or replace %s\n" % code
696        if term:
697            code += "\n/\n"
698        else:
699            code += "\n"
700        return code
701
702    def dropddl(self, connection=None, term=True):
703        if self.owner is not None:
704            name = "%s.%s" % (self.owner, self.name)
705        else:
706            name = self.name
707        code = "drop %s %s" % (self.__class__.type, name)
708        if term:
709            code += ";\n"
710        else:
711            code += "\n"
712        return code
713
714
715def getfullname(name, owner):
716    parts = []
717    if owner is not None:
718        if owner != owner.upper():
719            parts.append('"%s"' % owner)
720        else:
721            parts.append(owner)
722    for part in name.split("."):
723        if part != part.upper():
724            parts.append('"%s"' % part)
725        else:
726            parts.append(part)
727    return ".".join(parts)
728
729
730class Object(object):
731    """
732    The base class for all Python classes modelling schema objects in the
733    database.
734
735    Subclasses are: :class:`Sequence`, :class:`Table`, :class:`PrimaryKey`,
736    :class:`Comment`, :class:`ForeignKey`, :class:`Index`, :class:`Unique`,
737    :class:`Synonym`, :class:`View`, :class:`MaterializedView`, :class:`Library`,
738    :class:`Function`, :class:`Package`, :class:`Type`, :class:`Trigger`,
739    :class:`JavaSource` and :class:`Column`.
740    """
741    name2type = {} # maps the Oracle type name to the Python class (populated by the metaclass)
742
743    class __metaclass__(type):
744        def __new__(mcl, name, bases, dict):
745            typename = None
746            if "type" in dict and name != "Object":
747                typename = dict["type"]
748            cls = type.__new__(mcl, name, bases, dict)
749            if typename is not None:
750                Object.name2type[typename] = cls
751            return cls
752
753    def __init__(self, name, owner=None, connection=None):
754        self.name = name
755        self.owner = owner
756        self.connection = connection
757
758    def __repr__(self):
759        if self.owner is not None:
760            return "%s.%s(%r, %r)" % (self.__class__.__module__, self.__class__.__name__, self.name, self.owner)
761        else:
762            return "%s.%s(%r)" % (self.__class__.__module__, self.__class__.__name__, self.name)
763
764    def __str__(self):
765        if self.owner is not None:
766            return "%s(%s, %s)" % (self.__class__.__name__, self.name, self.owner)
767        else:
768            return "%s(%s)" % (self.__class__.__name__, self.name)
769
770    def __eq__(self, other):
771        return self.__class__ is other.__class__ and self.name == other.name and self.owner == other.owner
772
773    def __ne__(self, other):
774        return not self.__eq__(other)
775
776    def __hash__(self):
777        return hash(self.__class__.__name__) ^ hash(self.name) ^ hash(self.owner)
778
779    def getfullname(self):
780        return getfullname(self.name, self.owner)
781
782    @misc.notimplemented
783    def createddl(self, connection=None, term=True):
784        """
785        Return SQL code to create this object.
786        """
787
788    @misc.notimplemented
789    def dropddl(self, connection=None, term=True):
790        """
791        Return SQL code to drop this object
792        """
793
794    @misc.notimplemented
795    def cdate(self, connection=None):
796        """
797        Return a :class:`datetime.datetime` object with the creation date of
798        :var:`self` in the database specified by :var:`connection` (or
799        :const:`None` if such information is not available).
800        """
801
802    @misc.notimplemented
803    def udate(self, connection=None):
804        """
805        Return a :class:`datetime.datetime` object with the last modification
806        date of :var:`self` in the database specified by :var:`connection`
807        (or :const:`None` if such information is not available).
808        """
809
810    def iterreferences(self, connection=None):
811        """
812        Objects directly used by :var:`self`.
813
814        If :var:`connection` is not :const:`None` it will be used as the database
815        connection from which to fetch data. If :var:`connection` is :const:`None`
816        the connection from which :var:`self` has been extracted will be used. If
817        there is not such connection, you'll get an exception.
818        """
819        (connection, cursor) = self.getcursor(connection)
820        cursor.xexecute("select referenced_type, decode(referenced_owner, user, null, referenced_owner) as referenced_owner, referenced_name from all_dependencies where type=upper(:type) and name=:name and owner=nvl(:owner, user) and type != 'NON-EXISTENT'", type=self.type, name=self.name, owner=self.owner)
821        for rec in cursor.xfetchall():
822            try:
823                type = Object.name2type[rec.referenced_type.lower()]
824            except KeyError:
825                pass # FIXME: Issue a warning?
826            else:
827                yield type(rec.referenced_name, rec.referenced_owner, connection)
828
829    def iterreferencesall(self, connection=None, done=None):
830        """
831        All objects used by :var:`self` (recursively).
832
833        For the meaning of :var:`connection` see :meth:`iterreferences`.
834
835        :var:`done` is used internally and shouldn't be passed.
836        """
837        if done is None:
838            done = set()
839        if self not in done:
840            done.add(self)
841            for obj in self.iterreferences(connection):
842                for subobj in obj.iterreferencesall(connection, done):
843                    yield subobj
844            yield self
845
846    def iterreferencedby(self, connection=None):
847        """
848        Objects using :var:`self`.
849
850        For the meaning of :var:`connection` see :meth:`iterreferences`.
851        """
852        (connection, cursor) = self.getcursor(connection)
853        cursor.xexecute("select type, decode(owner, user, null, owner) as owner, name from all_dependencies where referenced_type=upper(:type) and referenced_name=:name and referenced_owner=nvl(:owner, user) and type != 'NON-EXISTENT'", type=self.type, name=self.name, owner=self.owner)
854        for rec in cursor.xfetchall():
855            try:
856                type = Object.name2type[rec.type.lower()]
857            except KeyError:
858                pass # FIXME: Issue a warning?
859            else:
860                yield type(rec.name, rec.owner, connection)
861
862    def iterreferencedbyall(self, connection=None, done=None):
863        """
864        All objects depending on :var:`self` (recursively).
865
866        For the meaning of :var:`connection` see :meth:`iterreferences`.
867
868        :var:`done` is used internally and shouldn't be passed.
869        """
870        if done is None:
871            done = set()
872        if self not in done:
873            done.add(self)
874            for obj in self.iterreferencedby(connection):
875                for subobj in obj.iterreferencedbyall(connection, done):
876                    yield subobj
877            yield self
878
879    def getconnection(self, connection):
880        if connection is None:
881            connection = self.connection
882        if connection is None:
883            raise TypeError("no connection available")
884        return connection
885
886    def getcursor(self, connection):
887        connection = self.getconnection(connection)
888        return (connection, connection.cursor())
889
890    def getconnectstring(self):
891        if self.connection:
892            return self.connection.connectstring()
893        return None
894    connectstring = property(getconnectstring)
895
896    @classmethod
897    def iterobjects(cls, connection, schema="user"):
898        """
899        Generator that yields all objects of this type in the database schema
900        of :var:`cursor`.
901        """
902        cursor = connection.cursor()
903        if schema=="all":
904            cursor.xexecute("select decode(owner, user, null, owner) as owner, object_name from all_objects where lower(object_type) = :type", type=cls.type)
905        else:
906            cursor.xexecute("select null as owner, object_name from user_objects where lower(object_type) = :type", type=cls.type)
907        return (cls(row.object_name, row.owner, connection) for row in cursor.xfetch())
908
909    def __iter__(self):
910        return iter(self.createddl().splitlines())
911
912    def __xrepr__(self, mode):
913        if mode == "cell":
914            yield (astyle.style_type_type, self.__class__.__name__)
915            yield (astyle.style_default, "(")
916            yield (astyle.style_default, self.name)
917            if self.owner is not None:
918                yield (astyle.style_default, ",")
919                yield (astyle.style_default, self.owner)
920            yield (astyle.style_default, ")")
921        else:
922            yield (astyle.style_default, repr(self))
923
924    def __xattrs__(self, mode="default"):
925        yield "type"
926        yield "name"
927        yield "owner"
928        yield "connection"
929   
930        if mode == "detail":
931            yield "cdate()"
932            yield "udate()"
933            yield "-createddl()"
934            yield "-dropddl()"
935            yield "-iterreferences()"
936            yield "-iterreferencedby()"
937            yield "-iterreferencesall()"
938            yield "-iterreferencedbyall()"
939
940
941class Sequence(MixinNormalDates, Object):
942    """
943    Models a sequence in the database.
944    """
945    type = "sequence"
946
947    def _createddl(self, connection, term, copyvalue):
948        (connection, cursor) = self.getcursor(connection)
949        cursor.xexecute("select * from all_sequences where sequence_owner=nvl(:owner, user) and sequence_name=:name", owner=self.owner, name=self.name)
950        rec = cursor.xfetchone()
951        if rec is None:
952            raise SQLObjectNotFoundError(self)
953        code  = "create sequence %s\n" % self.getfullname()
954        code += "\tincrement by %d\n" % rec.increment_by
955        if copyvalue:
956            code += "\tstart with %d\n" % (rec.last_number + rec.increment_by)
957        else:
958            code += "\tstart with %d\n" % rec.min_value
959        code += "\tmaxvalue %s\n" % rec.max_value
960        code += "\tminvalue %d\n" % rec.min_value
961        code += "\t%scycle\n" % ["no", ""][rec.cycle_flag == "Y"]
962        if rec.cache_size:
963            code += "\tcache %d\n" % rec.cache_size
964        else:
965            code += "\tnocache\n"
966        code += "\t%sorder" % ["no", ""][rec.order_flag == "Y"]
967        if term:
968            code += ";\n"
969        else:
970            code += "\n"
971        return code
972
973    def createddl(self, connection=None, term=True):
974        return self._createddl(connection, term, False)
975
976    def createddlcopy(self, connection=None, term=True):
977        """
978        Return SQL code to create an identical copy of this sequence.
979        """
980        return self._createddl(connection, term, True)
981
982    def dropddl(self, connection=None, term=True):
983        code = "drop sequence %s" % self.getfullname()
984        if term:
985            code += ";\n"
986        else:
987            code += "\n"
988        return code
989
990    def iterreferences(self, connection=None, schema="all"):
991        # Shortcut: a sequence doesn't depend on anything
992        if False:
993            yield None
994
995    def __xattrs__(self, mode="default"):
996        for attr in super(Sequence, self).__xattrs__(mode):
997            yield attr
998            if attr == "-createddl()":
999                yield "-createddlcopy()"
1000
1001
1002def _columntype(rec, data_precision=None, data_scale=None, char_length=None):
1003    ftype = rec.data_type.lower()
1004    if data_precision is None:
1005        data_precision = rec.data_precision
1006    if data_scale is None:
1007        data_scale = rec.data_scale
1008    if char_length is None:
1009        char_length = rec.char_length
1010
1011    fsize = data_precision
1012    fprec = data_scale
1013    if ftype == "number" and fprec == 0 and fsize is None:
1014        ftype = "integer"
1015    elif ftype == "number" and fprec is None and fsize is None:
1016        ftype = "number"
1017    elif ftype == "number" and fprec == 0:
1018        ftype = "number(%d)" % fsize
1019    elif ftype == "number":
1020        ftype = "number(%d, %d)" % (fsize, fprec)
1021    elif ftype == "raw":
1022        ftype = "raw(%d)" % rec.data_length
1023    else:
1024        if char_length != 0:
1025            fsize = char_length
1026        if fsize is not None:
1027            ftype += "(%d" % fsize
1028            if rec.char_used == "B":
1029                ftype += " byte"
1030            elif rec.char_used == "C":
1031                ftype += " char"
1032            if fprec is not None:
1033                ftype += ", %d" % fprec
1034            ftype += ")"
1035    return ftype
1036
1037
1038def _columndefault(rec):
1039    if rec.data_default is not None and rec.data_default != "null\n":
1040        return rec.data_default.rstrip("\n")
1041    return "null"
1042
1043
1044class Table(MixinNormalDates, Object):
1045    """
1046    Models a table in the database.
1047    """
1048    type = "table"
1049
1050    def createddl(self, connection=None, term=True):
1051        (connection, cursor) = self.getcursor(connection)
1052        if self.ismview(connection):
1053            return ""
1054        cursor.xexecute("select * from all_tab_columns where owner=nvl(:owner, user) and table_name=:name order by column_id asc", owner=self.owner, name=self.name)
1055        recs = cursor.xfetchall()
1056        if not recs:
1057            raise SQLObjectNotFoundError(self)
1058        code = ["create table %s\n(\n" % self.getfullname()]
1059        for (i, rec) in enumerate(recs):
1060            if i:
1061                code.append(",\n")
1062            code.append("\t%s %s" % (rec.column_name, _columntype(rec)))
1063            default = _columndefault(rec)
1064            if default != "null":
1065                code.append(" default %s" % default)
1066            if rec.nullable == "N":
1067                code.append(" not null")
1068        if term:
1069            code.append("\n);\n")
1070        else:
1071            code.append("\n)\n")
1072        return "".join(code)
1073
1074    def dropddl(self, connection=None, term=True):
1075        if self.ismview(connection):
1076            return ""
1077        code = "drop table %s" % self.getfullname()
1078        if term:
1079            code += ";\n"
1080        else:
1081            code += "\n"
1082        return code
1083
1084    def mview(self, connection=None):
1085        """
1086        The materialized view this table belongs to (or :const:`None` if it's a
1087        real table).
1088        """
1089        (connection, cursor) = self.getcursor(connection)
1090        cursor.xexecute("select mview_name from all_mviews where owner=nvl(:owner, user) and mview_name=:name", owner=self.owner, name=self.name)
1091        rec = cursor.xfetchone()
1092        if rec is not None:
1093            rec = MaterializedView(self.name, self.owner, connection)
1094        return rec
1095
1096    def ismview(self, connection=None):
1097        """
1098        Is this table a materialized view?
1099        """
1100        return self.mview(connection) is not None
1101
1102    @classmethod
1103    def iterobjects(cls, connection, schema="user"):
1104        cursor = connection.cursor()
1105        if schema == "all":
1106            cursor.xexecute("select decode(owner, user, null, owner) as owner, table_name from all_tables")
1107        else:
1108            cursor.xexecute("select null as owner, table_name from user_tables")
1109        return (cls(row.table_name, row.owner, connection=connection) for row in cursor.xfetch())
1110
1111    def itercolumns(self, connection=None):
1112        """
1113        Generator that yields all column objects of the :class:`Table` :var:`self`.
1114        """
1115        (connection, cursor) = self.getcursor(connection)
1116        cursor.xexecute("select column_name from all_tab_columns where owner=nvl(:owner, user) and table_name=:name order by column_id", owner=self.owner, name=self.name)
1117
1118        for rec in cursor.xfetchall():
1119            yield Column("%s.%s" % (self.name, rec.column_name), self.owner, connection)
1120
1121    def iterrecords(self, connection=None):
1122        """
1123        Generator that yields all records of the table :var:`self`.
1124        """
1125        (connection, cursor) = self.getcursor(connection)
1126        query = "select * from %s" % self.getfullname()
1127        cursor.xexecute(query)
1128        return cursor.xfetch()
1129
1130    def itercomments(self, connection=None):
1131        """
1132        Generator that yields all column comments of the table :var:`self`.
1133        """
1134        (connection, cursor) = self.getcursor(connection)
1135        cursor.xexecute("select column_name from all_tab_columns where owner=nvl(:owner, user) and table_name=:name order by column_id", owner=self.owner, name=self.name)
1136        for rec in cursor.xfetchall():
1137            yield Comment("%s.%s" % (self.name, rec.column_name), self.owner, connection)
1138
1139    def iterconstraints(self, connection=None):
1140        """
1141        Generator that yields all constraints for this table.
1142        """
1143        (connection, cursor) = self.getcursor(connection)
1144        # Primary and unique key(s)
1145        cursor.xexecute("select decode(owner, user, null, owner) as owner, constraint_type, constraint_name from all_constraints where constraint_type in ('P', 'U', 'R') and owner=nvl(:owner, user) and table_name=:name", owner=self.owner, name=self.name)
1146        types = {"P": PrimaryKey, "U": UniqueConstraint, "R": ForeignKey}
1147        for rec in cursor.xfetchall():
1148            yield types[rec.constraint_type](rec.constraint_name, rec.owner, connection)
1149
1150    def iterreferences(self, connection=None):
1151        connection = self.getconnection(connection)
1152        # A table doesn't depend on anything ...
1153        if self.ismview(connection):
1154            # ... unless it was created by a materialized view, in which case it depends on the view
1155            yield MaterializedView(self.name, self.owner, connection)
1156
1157    def iterreferencedby(self, connection=None):
1158        if not self.ismview(connection):
1159            for obj in self.itercomments(connection):
1160                yield obj
1161            for obj in self.iterconstraints(connection):
1162                yield obj
1163        for obj in super(Table, self).iterreferencedby(connection):
1164            # skip the materialized view
1165            if not isinstance(obj, MaterializedView) or obj.name != self.name or obj.owner != self.owner:
1166                yield obj
1167
1168    def __xattrs__(self, mode="default"):
1169        for attr in super(Table, self).__xattrs__(mode):
1170            yield attr
1171        if mode=="detail":
1172            yield "-itercolumns()"
1173            yield "-iterrecords()"
1174            yield "-itercomments()"
1175            yield "-iterconstraints()"
1176            yield "mview()"
1177
1178
1179class Constraint(Object):
1180    """
1181    Base class of all constraints (primary key constraints, foreign key
1182    constraints and unique constraints).
1183    """
1184
1185
1186class PrimaryKey(Constraint):
1187    """
1188    Models a primary key constraint in the database.
1189    """
1190    type = "pk"
1191
1192    def createddl(self, connection=None, term=True):
1193        (connection, cursor) = self.getcursor(connection)
1194        cursor.xexecute("select decode(owner, user, null, owner) as owner, constraint_name, table_name, r_owner, r_constraint_name from all_constraints where constraint_type='P' and owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1195        rec2 = cursor.xfetchone()
1196        if rec2 is None:
1197            raise SQLObjectNotFoundError(self)
1198        cursor.xexecute("select column_name from all_cons_columns where owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1199        tablename = getfullname(rec2.table_name, rec2.owner)
1200        pkname = getfullname(self.name, None)
1201        code = "alter table %s add constraint %s primary key(%s)" % (tablename, pkname, ", ".join(r.column_name for r in cursor.xfetch()))
1202        if term:
1203            code += ";\n"
1204        else:
1205            code += "\n"
1206        return code
1207
1208    def dropddl(self, connection=None, term=True):
1209        (connection, cursor) = self.getcursor(connection)
1210        cursor.xexecute("select decode(owner, user, null, owner) as owner, table_name from all_constraints where owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1211        rec = cursor.xfetchone()
1212        tablename = getfullname(rec.table_name, rec.owner)
1213        pkname = getfullname(self.name, None)
1214        code = "alter table %s drop constraint %s" % (tablename, pkname)
1215        if term:
1216            code += ";\n"
1217        else:
1218            code += "\n"
1219        return code
1220
1221    def cdate(self, connection=None):
1222        (connection, cursor) = self.getcursor(connection)
1223        cursor.xexecute("select last_change from all_constraints where constraint_type='P' and constraint_name=:name and owner=nvl(:owner, user)", name=self.name, owner=self.owner)
1224        row = cursor.fetchone()
1225        if row is None:
1226            raise SQLObjectNotFoundError(self)
1227        return None
1228
1229    def udate(self, connection=None):
1230        (connection, cursor) = self.getcursor(connection)
1231        cursor.xexecute("select last_change from all_constraints where constraint_type='P' and constraint_name=:name and owner=nvl(:owner, user)", name=self.name, owner=self.owner)
1232        row = cursor.fetchone()
1233        if row is None:
1234            raise SQLObjectNotFoundError(self)
1235        return row[0]
1236
1237    def iterreferencedby(self, connection=None):
1238        (connection, cursor) = self.getcursor(connection)
1239        cursor.xexecute("select decode(owner, user, null, owner) as owner, constraint_name from all_constraints where constraint_type='R' and r_owner=nvl(:owner, user) and r_constraint_name=:name", owner=self.owner, name=self.name)
1240        for rec in cursor.xfetchall():
1241            yield ForeignKey(rec.constraint_name, rec.owner, connection)
1242
1243        cursor.xexecute("select decode(owner, user, null, owner) as owner, index_name from all_indexes where owner=nvl(:owner, user) and index_name=:name", owner=self.owner, name=self.name)
1244        rec = cursor.xfetchone() # Is there an index for this constraint?
1245        if rec is not None:
1246            yield Index(rec.index_name, rec.owner, connection)
1247
1248    def iterreferences(self, connection=None):
1249        (connection, cursor) = self.getcursor(connection)
1250        cursor.xexecute("select decode(owner, user, null, owner) as owner, table_name from all_constraints where constraint_type='P' and owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1251        for rec in cursor.xfetchall():
1252            yield Table(rec.table_name, rec.owner, connection)
1253
1254    def table(self, connection=None):
1255        """
1256        Return the :class:`Table` :var:`self` belongs to.
1257        """
1258        (connection, cursor) = self.getcursor(connection)
1259        cursor.xexecute("select table_name from all_constraints where constraint_type='P' and owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1260        rec = cursor.xfetchone()
1261        return Table(rec.table_name, self.owner, connection)
1262
1263    def __xattrs__(self, mode="default"):
1264        for attr in super(PrimaryKey, self).__xattrs__(mode):
1265            yield attr
1266        if mode == "detail":
1267            yield "table()"
1268
1269
1270class Comment(Object):
1271    """
1272    Models a column comment in the database.
1273    """
1274    type = "comment"
1275
1276    def createddl(self, connection=None, term=True):
1277        (connection, cursor) = self.getcursor(connection)
1278        tcname = self.name.split(".")
1279        cursor.xexecute("select comments from all_col_comments where owner=nvl(:owner, user) and table_name=:tname and column_name=:cname", owner=self.owner, tname=tcname[0], cname=tcname[1])
1280        row = cursor.xfetchone()
1281        if row is None:
1282            raise SQLObjectNotFoundError(self)
1283
1284        name = self.getfullname()
1285        if row.comments:
1286            code = "comment on column %s is %s" % (name, formatstring(row.comments, latin1=True))
1287        else:
1288            code = "comment on column %s is ''" % name
1289        if term:
1290            code += ";\n"
1291        else:
1292            code += "\n"
1293        return code
1294
1295    def dropddl(self, connection=None, term=True):
1296        # will be dropped with the table
1297        return ""
1298
1299    def cdate(self, connection=None):
1300        return None
1301
1302    def udate(self, connection=None):
1303        return None
1304
1305    def iterreferences(self, connection=None):
1306        connection = self.getconnection(connection)
1307        yield Table(self.name.split(".")[0], self.owner, connection)
1308
1309    def iterreferencedby(self, connection=None):
1310        if False:
1311            yield None
1312
1313
1314class ForeignKey(Constraint):
1315    """
1316    Models a foreign key constraint in the database.
1317    """
1318    type = "fk"
1319
1320    def createddl(self, connection=None, term=True):
1321        (connection, cursor) = self.getcursor(connection)
1322        # Add constraint_type to the query, so we don't pick up another constraint by accident
1323        cursor.xexecute("select decode(r_owner, user, null, r_owner) as r_owner, r_constraint_name, table_name from all_constraints where constraint_type='R' and owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1324        rec = cursor.xfetchone()
1325        if rec is None:
1326            raise SQLObjectNotFoundError(self)
1327        cursor.xexecute("select column_name from all_cons_columns where owner=nvl(:owner, user) and constraint_name=:name order by position", owner=self.owner, name=self.name)
1328        fields1 = ", ".join(r.column_name for r in cursor.xfetch())
1329        cursor.xexecute("select table_name, column_name from all_cons_columns where owner=nvl(:owner, user) and constraint_name=:name order by position", owner=rec.r_owner, name=rec.r_constraint_name)
1330        fields2 = ", ".join("%s(%s)" % (getfullname(r.table_name, rec.r_owner), r.column_name) for r in cursor.xfetch())
1331        tablename = getfullname(rec.table_name, self.owner)
1332        fkname = getfullname(self.name, None)
1333        code = "alter table %s add constraint %s foreign key (%s) references %s" % (tablename, fkname, fields1, fields2)
1334        if term:
1335            code += ";\n"
1336        else:
1337            code += "\n"
1338        return code
1339
1340    def _ddl(self, connection, cmd, term):
1341        (connection, cursor) = self.getcursor(connection)
1342        cursor.xexecute("select table_name from all_constraints where owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1343        rec = cursor.xfetchone()
1344        if rec is None:
1345            raise SQLObjectNotFoundError(self)
1346        tablename = getfullname(rec.table_name, self.owner)
1347        fkname = getfullname(self.name, None)
1348        code = "alter table %s %s constraint %s" % (tablename, cmd, fkname)
1349        if term:
1350            code += ";\n"
1351        else:
1352            code += "\n"
1353        return code
1354
1355    def dropddl(self, connection=None, term=True):
1356        return self._ddl(connection, "drop", term)
1357
1358    def enableddl(self, connection=None, term=True):
1359        return self._ddl(connection, "enable", term)
1360
1361    def disableddl(self, connection=None, term=True):
1362        return self._ddl(connection, "disable", term)
1363
1364    def cdate(self, connection=None):
1365        (connection, cursor) = self.getcursor(connection)
1366        cursor.xexecute("select last_change from all_constraints where constraint_type='R' and constraint_name=:name and owner=nvl(:owner, user)", name=self.name, owner=self.owner)
1367        row = cursor.fetchone()
1368        if row is None:
1369            raise SQLObjectNotFoundError(self)
1370        return None
1371
1372    def udate(self, connection=None):
1373        (connection, cursor) = self.getcursor(connection)
1374        cursor.xexecute("select last_change from all_constraints where constraint_type='R' and constraint_name=:name and owner=nvl(:owner, user)", name=self.name, owner=self.owner)
1375        row = cursor.fetchone()
1376        if row is None:
1377            raise SQLObjectNotFoundError(self)
1378        return row[0]
1379
1380    def iterreferencedby(self, connection=None):
1381        # Shortcut: Nobody references a foreign key
1382        if False:
1383            yield None
1384
1385    def iterreferences(self, connection=None):
1386        yield self.table(connection)
1387        yield self.pk(connection)
1388
1389    def table(self, connection=None):
1390        """
1391        Return the :class:`Table` :var:`self` belongs to.
1392        """
1393        (connection, cursor) = self.getcursor(connection)
1394        cursor.xexecute("select table_name from all_constraints where constraint_type='R' and owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1395        rec = cursor.xfetchone()
1396        return Table(rec.table_name, self.owner, connection)
1397
1398    def pk(self, connection=None):
1399        """
1400        Return the primary key referenced by :var:`self`.
1401        """
1402        (connection, cursor) = self.getcursor(connection)
1403        cursor.xexecute("select decode(r_owner, user, null, r_owner) as r_owner, r_constraint_name from all_constraints where constraint_type='R' and owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1404        rec = cursor.xfetchone()
1405        return PrimaryKey(rec.r_constraint_name, rec.r_owner, connection)
1406
1407    def isenabled(self, connection=None):
1408        """
1409        Return whether this constraint is enabled.
1410        """
1411        (connection, cursor) = self.getcursor(connection)
1412        cursor.xexecute("select status from all_constraints where constraint_type='R' and owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1413        rec = cursor.fetchone()
1414        return rec[0] == "ENABLED"
1415
1416    def __xattrs__(self, mode="default"):
1417        for attr in super(ForeignKey, self).__xattrs__(mode):
1418            yield attr
1419        if mode == "detail":
1420            yield "table()"
1421            yield "pk()"
1422
1423
1424class Index(MixinNormalDates, Object):
1425    """
1426    Models an index in the database.
1427    """
1428    type = "index"
1429
1430    def createddl(self, connection=None, term=True):
1431        (connection, cursor) = self.getcursor(connection)
1432        if self.isconstraint(connection):
1433            return ""
1434        cursor.xexecute("select index_name, table_name, uniqueness from all_indexes where table_owner=nvl(:owner, user) and index_name=:name", owner=self.owner, name=self.name)
1435        rec = cursor.xfetchone()
1436        if rec is None:
1437            raise SQLObjectNotFoundError(self)
1438        tablename = getfullname(rec.table_name, self.owner)
1439        indexname = self.getfullname()
1440        if rec.uniqueness == "UNIQUE":
1441            unique = " unique"
1442        else:
1443            unique = ""
1444        cursor.xexecute("select aie.column_expression, aic.column_name from all_ind_columns aic, all_ind_expressions aie where aic.table_owner=aie.table_owner(+) and aic.index_name=aie.index_name(+) and aic.column_position=aie.column_position(+) and aic.table_owner=nvl(:owner, user) and aic.index_name=:name order by aic.column_position", owner=self.owner, name=self.name)
1445        code = "create%s index %s on %s (%s)" % (unique, indexname, tablename, ", ".join(r.column_expression or r.column_name for r in cursor.xfetch()))
1446        if term:
1447            code += ";\n"
1448        else:
1449            code += "\n"
1450        return code
1451
1452    def dropddl(self, connection=None, term=True):
1453        if self.isconstraint(connection):
1454            return ""
1455        code = "drop index %s" % getfullname(self.name, self.owner)
1456        if term:
1457            code += ";\n"
1458        else:
1459            code += "\n"
1460        return code
1461
1462    def constraint(self, connection=None):
1463        """
1464        If this index is generated by a constraint, return the constraint
1465        otherwise return :const:`None`.
1466        """
1467        (connection, cursor) = self.getcursor(connection)
1468        cursor.xexecute("select constraint_type from all_constraints where owner=nvl(:owner, user) and constraint_name=:name and constraint_type in ('U', 'P')", owner=self.owner, name=self.name)
1469        rec = cursor.xfetchone()
1470        if rec is not None:
1471            rec = {"U": UniqueConstraint, "P": PrimaryKey}[rec.constraint_type](self.name, self.owner, connection)
1472        return rec
1473
1474    def isconstraint(self, connection=None):
1475        """
1476        Is this index generated by a constraint?
1477        """
1478        return self.constraint(connection) is not None
1479
1480    def iterreferences(self, connection=None):
1481        constraint = self.constraint(connection)
1482        # if self is generated by a constraint, self depends on it
1483        if constraint is not None:
1484            yield constraint
1485        else:
1486            for obj in super(Index, self).iterreferences(connection):
1487                yield obj
1488
1489    def table(self, connection=None):
1490        """
1491        Return the :class:`Table` :var:`self` belongs to.
1492        """
1493        (connection, cursor) = self.getcursor(connection)
1494        cursor.xexecute("select table_name from all_indexes where table_owner=nvl(:owner, user) and index_name=:name", owner=self.owner, name=self.name)
1495        rec = cursor.xfetchone()
1496        return Table(rec.table_name, self.owner, connection)
1497
1498    def __xattrs__(self, mode="default"):
1499        for attr in super(Index, self).__xattrs__(mode):
1500            yield attr
1501        if mode == "detail":
1502            yield "constraint()"
1503            yield "table()"
1504
1505
1506class UniqueConstraint(Constraint):
1507    """
1508    Models a unique constraint in the database.
1509    """
1510    type = "unique"
1511
1512    def createddl(self, connection=None, term=True):
1513        (connection, cursor) = self.getcursor(connection)
1514        # Add constraint_type to the query, so we don't pick up another constraint by accident
1515        cursor.xexecute("select table_name from all_constraints where constraint_type='U' and owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1516        rec = cursor.xfetchone()
1517        if rec is None:
1518            raise SQLObjectNotFoundError(self)
1519        tablename = getfullname(rec.table_name, self.owner)
1520        uniquename = getfullname(self.name, None)
1521        cursor.xexecute("select column_name from all_cons_columns where owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1522        code = "alter table %s add constraint %s unique(%s)" % (tablename, uniquename, ", ".join(r.column_name for r in cursor.xfetch()))
1523        if term:
1524            code += ";\n"
1525        else:
1526            code += "\n"
1527        return code
1528
1529    def dropddl(self, connection=None, term=True):
1530        (connection, cursor) = self.getcursor(connection)
1531        cursor.xexecute("select table_name from all_constraints where constraint_type='U' and owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1532        rec = cursor.xfetchone()
1533        if rec is None:
1534            raise SQLObjectNotFoundError(self)
1535        tablename = getfullname(rec.table_name, self.owner)
1536        uniquename = getfullname(self.name, None)
1537        code = "alter table %s drop constraint %s" % (tablename, uniquename)
1538        if term:
1539            code += ";\n"
1540        else:
1541            code += "\n"
1542        return code
1543
1544    def cdate(self, connection=None):
1545        (connection, cursor) = self.getcursor(connection)
1546        cursor.xexecute("select last_change from all_constraints where constraint_type='U' and constraint_name=:name and owner=nvl(:owner, user)", name=self.name, owner=self.owner)
1547        row = cursor.fetchone()
1548        if row is None:
1549            raise SQLObjectNotFoundError(self)
1550        return None
1551
1552    def udate(self, connection=None):
1553        (connection, cursor) = self.getcursor(connection)
1554        cursor.xexecute("select last_change from all_constraints where constraint_type='U' and constraint_name=:name and owner=nvl(:owner, user)", name=self.name, owner=self.owner)
1555        row = cursor.fetchone()
1556        if row is None:
1557            raise SQLObjectNotFoundError(self)
1558        return row[0]
1559
1560    def iterreferencedby(self, connection=None):
1561        (connection, cursor) = self.getcursor(connection)
1562        cursor.xexecute("select decode(owner, user, null, owner) as owner, constraint_name from all_constraints where constraint_type='R' and r_owner=nvl(:owner, user) and r_constraint_name=:name", owner=self.owner, name=self.name)
1563        for rec in cursor.xfetchall():
1564            yield ForeignKey(rec.constraint_name, rec.owner, connection)
1565
1566        cursor.xexecute("select decode(owner, user, null, owner) as owner, index_name from all_indexes where owner=nvl(:owner, user) and index_name=:name", owner=self.owner, name=self.name)
1567        rec = cursor.xfetchone() # Ist there an index for this constraint?
1568        if rec is not None:
1569            yield Index(rec.index_name, rec.owner, connection)
1570
1571    def iterreferences(self, connection=None):
1572        (connection, cursor) = self.getcursor(connection)
1573        cursor.xexecute("select decode(owner, user, null, owner) as owner, table_name from all_constraints where constraint_type='U' and owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1574        for rec in cursor.xfetchall():
1575            yield Table(rec.table_name, rec.owner, connection)
1576
1577    def table(self, connection=None):
1578        """
1579        Return the :class:`Table` :var:`self` belongs to.
1580        """
1581        (connection, cursor) = self.getcursor(connection)
1582        cursor.xexecute("select table_name from all_constraints where constraint_type='U' and owner=nvl(:owner, user) and constraint_name=:name", owner=self.owner, name=self.name)
1583        rec = cursor.xfetchone()
1584        return Table(rec.table_name, self.owner, connection)
1585
1586    def __xattrs__(self, mode="default"):
1587        for attr in super(UniqueConstraint, self).__xattrs__(mode):
1588            yield attr
1589        if mode == "detail":
1590            yield "table()"
1591
1592
1593class Synonym(Object):
1594    """
1595    Models a synonym in the database.
1596    """
1597    type = "synonym"
1598
1599    def createddl(self, connection=None, term=True):
1600        (connection, cursor) = self.getcursor(connection)
1601        cursor.xexecute("select table_owner, table_name, db_link from all_synonyms where owner=nvl(:owner, user) and synonym_name=:name", owner=self.owner, name=self.name)
1602        rec = cursor.xfetchone()
1603        if rec is None:
1604            raise SQLObjectNotFoundError(self)
1605        owner = self.owner
1606        if owner == "PUBLIC":
1607            public = "public "
1608            owner = None
1609        else:
1610            public = ""
1611        name = getfullname(self.name, owner)
1612        name2 = getfullname(rec.table_name, rec.table_owner)
1613        code = "create or replace %ssynonym %s for %s" % (public, name, name2)
1614        if rec.db_link is not None:
1615            code += "@%s" % rec.db_link
1616        if term:
1617            code += ";\n"
1618        else:
1619            code += "\n"
1620        return code
1621
1622    def dropddl(self, connection=None, term=True):
1623        owner = self.owner
1624        if owner == "PUBLIC":
1625            public = "public "
1626            owner = None
1627        else:
1628            public = ""
1629        name = getfullname(self.name, owner)
1630        code = "drop %ssynonym %s" % (public, name)
1631        if term:
1632            code += ";\n"
1633        else:
1634            code += "\n"
1635        return code
1636
1637    def cdate(self, connection=None):
1638        return None
1639
1640    def udate(self, connection=None):
1641        return None
1642
1643    def iterreferences(self, connection=None, schema="all"):
1644        # Shortcut: a synonym doesn't depend on anything
1645        if False:
1646            yield None
1647
1648    def getobject(self, connection=None):
1649        """
1650        Get the object for which :var:`self` is a synonym.
1651        """
1652        (connection, cursor) = self.getcursor(connection)
1653        cursor.xexecute("select table_owner, table_name, db_link from all_synonyms where owner=nvl(:owner, user) and synonym_name=:name", owner=self.owner, name=self.name)
1654        rec = cursor.xfetchone()
1655        if rec is None:
1656            raise SQLObjectNotFoundError(self)
1657        return connection.getobject(rec.table_name, rec.table_owner)
1658
1659
1660class View(MixinNormalDates, Object):
1661    """
1662    Models a view in the database.
1663    """
1664    type = "view"
1665
1666    def createddl(self, connection=None, term=True):
1667        (connection, cursor) = self.getcursor(connection)
1668        cursor.xexecute("select text from all_views where owner=nvl(:owner, user) and view_name=:name", owner=self.owner, name=self.name)
1669        rec = cursor.xfetchone()
1670        if rec is None:
1671            raise SQLObjectNotFoundError(self)
1672        code = "\n".join(line.rstrip() for line in rec.text.strip().splitlines()) # Strip trailing whitespace
1673        code = "create or replace view %s as\n\t%s" % (self.getfullname(), code)
1674        if term:
1675            code += "\n/\n"
1676        else:
1677            code += "\n"
1678        return code
1679
1680    def dropddl(self, connection=None, term=True):
1681        code = "drop view %s" % self.getfullname()
1682        if term:
1683            code += ";\n"
1684        else:
1685            code += "\n"
1686        return code
1687
1688    def iterrecords(self, connection=None):
1689        (connection, cursor) = self.getcursor(connection)
1690        query = "select * from %s" % self.getfullname()
1691        cursor.xexecute(query)
1692        return cursor.xfetch()
1693
1694
1695class MaterializedView(View):
1696    """
1697    Models a meterialized view in the database.
1698    """
1699    type = "materialized view"
1700
1701    def createddl(self, connection=None, term=True):
1702        (connection, cursor) = self.getcursor(connection)
1703        cursor.xexecute("select * from all_mviews where owner=nvl(:owner, user) and mview_name=:name", owner=self.owner, name=self.name)
1704        rec = cursor.xfetchone()
1705        if rec is None:
1706            raise SQLObjectNotFoundError(self)
1707        code = "\n".join(line.rstrip() for line in rec.query.strip().splitlines()) # Strip trailing whitespace
1708        code = "create materialized view %s\nrefresh %s on %s as\n\t%s" % (self.getfullname(), rec.refresh_method, rec.refresh_mode, code)
1709        if term:
1710            code += "\n/\n"
1711        else:
1712            code += "\n"
1713        return code
1714
1715    def dropddl(self, connection=None, term=True):
1716        code = "drop materialized view %s" % self.getfullname()
1717        if term:
1718            code += ";\n"
1719        else:
1720            code += "\n"
1721        return code
1722
1723    def iterreferences(self, connection=None):
1724        # skip the table
1725        for obj in super(MaterializedView, self).iterreferences(connection):
1726            if not isinstance(obj, Table) or obj.name != self.name or obj.owner != self.owner:
1727                yield obj
1728
1729    def iterreferencedby(self, connection=None):
1730        connection = self.getconnection(connection)
1731        yield Table(self.name, self.owner, connection)
1732
1733
1734class Library(Object):
1735    """
1736    Models a library in the database.
1737    """
1738    type = "library"
1739
1740    def createddl(self, connection=None, term=True):
1741        (connection, cursor) = self.getcursor(connection)
1742        cursor.xexecute("select file_spec from all_libraries where owner=nvl(:owner, user) and library_name=:name", owner=self.owner, name=self.name)
1743        rec = cursor.xfetchone()
1744        if rec is None:
1745            raise SQLObjectNotFoundError(self)
1746        return "create or replace library %s as %r" % (self.getfullname(), rec.file_spec)
1747        if term:
1748            code += ";\n"
1749        else:
1750            code += "\n"
1751        return code
1752
1753    def dropddl(self, connection=None, term=True):
1754        code = "drop library %s" % self.getfullname()
1755        if term:
1756            code += ";\n"
1757        else:
1758            code += "\n"
1759        return code
1760
1761
1762class Argument(object):
1763    """
1764    :class:`Argument` objects hold information about the arguments of a
1765    stored procedure.
1766    """
1767    def __init__(self, name, position, datatype, isin, isout):
1768        self.name = name
1769        self.position = position
1770        self.datatype = datatype
1771        self.isin = isin
1772        self.isout = isout
1773
1774    def __repr__(self):
1775        return "<%s.%s name=%r position=%r at 0x%x>" % (self.__class__.__module__, self.__class__.__name__, self.name, self.position, id(self))
1776
1777    def __xattrs__(self, mode="default"):
1778        return ("name", "position", "datatype", "isin", "isout")
1779
1780
1781class Callable(MixinNormalDates, MixinCodeDDL, Object):
1782    """
1783    Models a callable object in the database, i.e. functions and procedures.
1784    """
1785
1786    _ora2cx = {
1787        "date": DATETIME,
1788        "number": NUMBER,
1789        "varchar2": STRING,
1790        "clob": CLOB,
1791        "blob": BLOB,
1792    }
1793   
1794    def __init__(self, name, owner=None, connection=None):
1795        Object.__init__(self, name, owner, connection)
1796        self._argsbypos = None
1797        self._argsbyname = None
1798        self._returnvalue = None
1799
1800    def _calcargs(self, cursor):
1801        if self._argsbypos is None:
1802            cursor.xexecute("select object_id from all_objects where owner=nvl(:owner, user) and object_name=:name and object_type=:type", owner=self.owner, name=self.name, type=self.type.upper())
1803            if cursor.fetchone() is None:
1804                raise SQLObjectNotFoundError(self)
1805            self._argsbypos = []
1806            self._argsbyname = {}
1807            cursor.xexecute("select lower(argument_name) as name, lower(in_out) as in_out, lower(data_type) as datatype from all_arguments where owner=nvl(:owner, user) and object_name=:name and data_level=0 order by sequence", owner=self.owner, name=self.name)
1808            i = 0 # argument position (skip return value)
1809            for record in cursor.xfetch():
1810                arginfo = Argument(record.name, i, record.datatype, "in" in record.in_out, "out" in record.in_out)
1811                if record.name is None: # this is the return value
1812                    self._returnvalue = arginfo
1813                else:
1814                    self._argsbypos.append(arginfo)
1815                    self._argsbyname[arginfo.name] = arginfo
1816                    i += 1
1817
1818    def _getparamarray(self, cursor, *args, **kwargs):
1819        # Get preinitialized parameter array
1820        la = len(args)
1821        lra = len(self._argsbypos)
1822        if la > lra:
1823            raise TypeError("too many parameters for %r: %d given, %d expected" % (self, la, lra))
1824        realargs = list(args) + [_default]*(lra-la)
1825
1826        # Put keyword arguments into the parameter array
1827        for (key, value) in kwargs.iteritems():
1828            try:
1829                arginfo = self._argsbyname[key.lower()]
1830            except KeyError:
1831                raise TypeError("unknown parameter for %r: %s" % (self, key))
1832            else:
1833                if realargs[arginfo.position] is not _default:
1834                    raise TypeError("duplicate argument for %r: %s" % (self, key))
1835            realargs[arginfo.position] = cursor._encode(value)
1836
1837        # Replace out parameters (and strings that are longer than the allowed
1838        # maximum) with variables; replace unspecified parameters with :const:`None`
1839        for arginfo in self._argsbypos:
1840            realarg = realargs[arginfo.position]
1841            if realarg is _default:
1842                realarg = None
1843                realargs[arginfo.position] = realarg
1844            if arginfo.isout or arginfo.datatype == "blob" or (isinstance(realarg, basestring) and len(realarg) >= 4000):
1845                try:
1846                    type = self._ora2cx[arginfo.datatype]
1847                except KeyError:
1848                    raise TypeError("can't handle parameter %s of type %s in %r" % (arginfo.name, arginfo.datatype, self))
1849                var = cursor.var(type)
1850                var.setvalue(0, realarg)
1851                realargs[arginfo.position] = var
1852        return realargs
1853
1854    def iterarguments(self, connection=None):
1855        """
1856        Generator that yields all arguments of the function/procedure :var:`self`.
1857        """
1858        (connection, cursor) = self.getcursor(connection)
1859        self._calcargs(cursor)
1860        for arginfo in self._argsbypos:
1861            yield arginfo
1862
1863    def __xattrs__(self, mode="default"):
1864        for attr in Object.__xattrs__(self, mode):
1865            yield attr
1866        if mode == "detail":
1867            yield "-iterarguments()"
1868
1869
1870class Procedure(Callable):
1871    """
1872    Models a procedure in the database. A :class:`Procedure` object can be
1873    used as a wrapper for calling the procedure with keyword arguments.
1874    """
1875
1876    type = "procedure"
1877
1878    def __call__(self, cursor, *args, **kwargs):
1879        """
1880        Call the procedure with arguments :var:`args` and keyword arguments
1881        :var:`kwargs`. :var:`cursor` must be a :mod:`cx_Oracle` or
1882        :mod:`ll.orasql` cursor. This will return a :class:`Record` object
1883        containing the result of the call (i.e. this record will contain all in
1884        and out parameters).
1885        """
1886        self._calcargs(cursor)
1887
1888        # Get preinitialized parameter array
1889        realargs = self._getparamarray(cursor, *args, **kwargs)
1890
1891        if self.owner is None:
1892            name = self.name
1893        else:
1894            name = "%s.%s" % (self.owner, self.name)
1895        name = cursor._encode(name)
1896
1897        return Record((self._argsbypos[i].name, cursor._decode(value, self._argsbypos[i].datatype == "blob")) for (i, value) in enumerate(cursor.callproc(name, realargs)))
1898
1899
1900class Function(Callable):
1901    """
1902    Models a function in the database. A :class:`Function` object can be
1903    used as a wrapper for calling the function with keyword arguments.
1904    """
1905    type = "function"
1906
1907    def __call__(self, cursor, *args, **kwargs):
1908        """
1909        Call the function with arguments :var:`args` and keyword arguments
1910        :var:`kwargs`. :var:`cursor` must be a :mod:`cx_Oracle` or
1911        :mod:`ll.orasql` cursor. This will return a tuple containing the result
1912        and a :class:`Record` object containing the modified parameters (i.e.
1913        this record will contain all in and out parameters).
1914        """
1915        self._calcargs(cursor)
1916
1917        # Get preinitialized parameter array
1918        realargs = self._getparamarray(cursor, *args, **kwargs)
1919
1920        if self.owner is None:
1921            name = self.name
1922        else:
1923            name = "%s.%s" % (self.owner, self.name)
1924        name = cursor._encode(name)
1925
1926        returnvalue = cursor._decode(cursor.callfunc(name, self._ora2cx[self._returnvalue.datatype], realargs), self._returnvalue.datatype == "blob")
1927        result = Record()
1928        for (i, value) in enumerate(realargs):
1929            arginfo = self._argsbypos[i]
1930            if arginfo.isout:
1931                value = value.getvalue(0)
1932            result[arginfo.name] = cursor._decode(value, arginfo.datatype == "blob")
1933        return (returnvalue, result)
1934
1935
1936class Package(MixinNormalDates, MixinCodeDDL, Object):
1937    """
1938    Models a package in the database.
1939    """
1940    type = "package"
1941
1942
1943class PackageBody(MixinNormalDates, MixinCodeDDL, Object):
1944    """
1945    Models a package body in the database.
1946    """
1947    type = "package body"
1948
1949
1950class Type(MixinNormalDates, MixinCodeDDL, Object):
1951    """
1952    Models a type definition in the database.
1953    """
1954    type = "type"
1955
1956
1957class Trigger(MixinNormalDates, MixinCodeDDL, Object):
1958    """
1959    Models a trigger in the database.
1960    """
1961    type = "trigger"
1962
1963
1964class JavaSource(MixinNormalDates, Object):
1965    """
1966    Models Java source code in the database.
1967    """
1968    type = "java source"
1969
1970    def createddl(self, connection=None, term=True):
1971        (connection, cursor) = self.getcursor(connection)
1972        cursor.xexecute("select text from all_source where type='JAVA SOURCE' and owner=nvl(:owner, user) and name=:name order by line", owner=self.owner, name=self.name)
1973        code = "\n".join((rec.text or "").rstrip() for rec in cursor.xfetch())
1974        code = code.strip()
1975
1976        code = "create or replace and compile java source named %s as\n%s\n" % (self.getfullname(), code)
1977        if term:
1978            code += "/\n"
1979        return code
1980
1981    def dropddl(self, connection=None, term=True):
1982        code = "drop java source %s" % self.getfullname()
1983        if term:
1984            code += ";\n"
1985        else:
1986            code += "\n"
1987        return code
1988
1989
1990class Privilege(object):
1991    """
1992    Models a database object privilege (i.e. a grant).
1993    """
1994    type = "privilege" # required by iterschema()
1995
1996    def __init__(self, privilege, name, grantor, grantee, owner=None, connection=None):
1997        self.privilege = privilege
1998        self.name = name
1999        self.grantor = grantor
2000        self.grantee = grantee
2001        self.owner = owner
2002        self.connection = connection
2003
2004    def __repr__(self):
2005        if self.owner is not None:
2006            return "%s.%s(%r, %r, %r, %r)" % (self.__class__.__module__, self.__class__.__name__, self.privilege, self.name, self.grantee, self.owner)
2007        else:
2008            return "%s.%s(%r, %r, %r)" % (self.__class__.__module__, self.__class__.__name__, self.privilege, self.name, self.grantee)
2009
2010    def __str__(self):
2011        if self.owner is not None:
2012            return "%s(%r, %r, %r, %r)" % (self.__class__.__name__, self.privilege, self.name, self.grantee, self.owner)
2013        else:
2014            return "%s(%r, %r, %r)" % (self.__class__.__name__, self.privilege, self.name, self.grantee)
2015
2016    def getconnection(self, connection):
2017        if connection is None:
2018            connection = self.connection
2019        if connection is None:
2020            raise TypeError("no connection available")
2021        return connection
2022
2023    def getcursor(self, connection):
2024        connection = self.getconnection(connection)
2025        return (connection, connection.cursor())
2026
2027    def getconnectstring(self):
2028        if self.connection:
2029            return self.connection.connectstring()
2030        return None
2031    connectstring = property(getconnectstring)
2032
2033    @classmethod
2034    def iterobjects(self, connection, schema="user"):
2035        """
2036        Generator that yields object privileges for the current users (or all
2037        users) objects.
2038
2039        :var:`schema` specifies which privileges should be yielded:
2040
2041        ``"user"``
2042            Only object privileges for objects belonging to the current user will
2043            be yielded.
2044
2045        ``"all"``
2046            All object privileges will be yielded.
2047        """
2048        if schema not in ("user", "all"):
2049            raise UnknownSchemaError(schema)
2050
2051        cursor = connection.cursor() # can't use :meth:`getcursor` as we're in a classmethod
2052
2053        if schema == "all":
2054            cursor.xexecute("select decode(table_schema, user, null, table_schema) as owner, privilege, table_name as object, decode(grantor, user, null, grantor) as grantor, grantee from all_tab_privs order by table_schema, table_name, privilege")
2055        else:
2056            cursor.xexecute("select null as owner, privilege, table_name as object, decode(grantor, user, null, grantor) as grantor, grantee from user_tab_privs where owner=user order by table_name, privilege")
2057        return (Privilege(rec.privilege, rec.object, rec.grantor, rec.grantee, rec.owner, cursor.connection) for rec in cursor.xfetch())
2058
2059    def grantddl(self, connection=None, term=True, mapgrantee=True):
2060        """
2061        Return SQL code to grant this privilege. If :var:`mapgrantee` is a list
2062        or a dictionary and ``self.grantee`` is not in this list (or dictionary)
2063        no command will returned. If it's a dictionary and ``self.grantee`` is
2064        in it, the privilege will be granted to the user specified as the value
2065        instead of the original one. If :var:`mapgrantee` is true (the default)
2066        the privilege will be granted to the original grantee.
2067        """
2068        (connection, cursor) = self.getcursor(connection)
2069        if mapgrantee is True:
2070            grantee = self.grantee
2071        elif isinstance(mapgrantee, (list, tuple)):
2072            if self.grantee.lower() in (g.lower() or f in mapgrantee):
2073                grantee = self.grantee
2074            else:
2075                grantee = None
2076        else:
2077            mapgrantee = dict((key.lower(), value) for (key, value) in mapgrantee.iteritems())
2078            grantee = mapgrantee.get(self.grantee.lower(), None)
2079        if grantee is None:
2080            return ""
2081        code = "grant %s on %s to %s" % (self.privilege, self.name, grantee)
2082        if term:
2083            code += ";\n"
2084        return code
2085
2086    def __xattrs__(self, mode="default"):
2087        yield ipipe.AttributeDescriptor("privilege", "the type of the privilege")
2088        yield ipipe.AttributeDescriptor("name", "the name of the object for which this privilege grants access")
2089        yield ipipe.AttributeDescriptor("owner", "the owner of the object")
2090        yield ipipe.AttributeDescriptor("grantor", "who granted this privilege?")
2091        yield ipipe.AttributeDescriptor("grantee", "to whom has this privilege been granted?")
2092        yield ipipe.AttributeDescriptor("connection")
2093        if mode == "detail":
2094            yield ipipe.IterMethodDescriptor("grantddl", "the SQL statement to issue this privilege")
2095
2096
2097class Column(Object):
2098    """
2099    Models a single column of a table in the database. This is used to output
2100    ``ALTER TABLE ...`` statements for adding, dropping and modifying columns.
2101    """
2102    type = "column"
2103
2104    def _getcolumnrecord(self, cursor):
2105        name = self.name.split(".")
2106        cursor.xexecute("select * from all_tab_columns where owner=nvl(:owner, user) and table_name=:table_name and column_name=:column_name", owner=self.owner, table_name=name[0], column_name=name[1])
2107        rec = cursor.xfetchone()
2108        if rec is None:
2109            raise SQLObjectNotFoundError(self)
2110        return rec
2111
2112    def addddl(self, connection=None, term=True):
2113        (connection, cursor) = self.getcursor(connection)
2114        rec = self._getcolumnrecord(cursor)
2115        name = self.name.split(".")
2116        code = ["alter table %s add %s" % (getfullname(name[0], self.owner), getfullname(name[1], None))]
2117        code.append(" %s" % _columntype(rec))
2118        default = _columndefault(rec)
2119        if default != "null":
2120            code.append(" default %s" % default)
2121        if rec.nullable == "N":
2122            code.append(" not null")
2123        if term:
2124            code.append(";\n")
2125        else:
2126            code.append("\n")
2127        return "".join(code)
2128
2129    def modifyddl(self, connection, cursorold, cursornew, term=True):
2130        (connection, cursor) = self.getcursor(connection)
2131
2132        rec = self._getcolumnrecord(cursor)
2133        recold = self._getcolumnrecord(cursorold)
2134        recnew = self._getcolumnrecord(cursornew)
2135
2136        name = self.name.split(".")
2137
2138        code = ["alter table %s modify %s" % (getfullname(name[0], self.owner), getfullname(name[1], None))]
2139        # Has the type changed?
2140        if recold.data_precision != recnew.data_precision or recold.data_length != recnew.data_length or recold.data_scale != recnew.data_scale or recold.char_length != recnew.char_length or recold.data_type != recnew.data_type or recold.data_type_owner != recnew.data_type_owner:
2141            # Has only the size changed?
2142            if rec.data_type == recold.data_type == recnew.data_type and rec.data_type_owner == recold.data_type_owner == recnew.data_type_owner:
2143                try:
2144                    data_precision = max(r.data_precision for r in (rec, recold, recnew) if r.data_precision is not None)
2145                except ValueError:
2146                    data_precision = None
2147                try:
2148                    data_scale = max(r.data_scale for r in (rec, recold, recnew) if r.data_scale is not None)
2149                except ValueError:
2150                    data_scale = None
2151                try:
2152                    char_length = max(r.char_length for r in (rec, recold, recnew) if r.char_length is not None)
2153                except ValueError:
2154                    char_length = None
2155                code.append(" %s" % _columntype(rec, data_precision=data_precision, data_scale=data_scale, char_length=char_length))
2156            else: # The type has changed too
2157                if recnew.data_type != rec.data_type or recnew.data_type_owner != rec.data_type_owner:
2158                    raise ConflictError(self, "data_type unmergeable")
2159                elif recnew.data_precision != rec.data_precision:
2160                    raise ConflictError(self, "data_precision unmergeable")
2161                elif recnew.data_scale != rec.data_scale:
2162                    raise ConflictError(self, "data_scale unmergeable")
2163                elif recnew.char_length != rec.char_length:
2164                    raise ConflictError(self, "char_length unmergeable")
2165                code.append(" %s" % _columntype(recnew))
2166
2167        # Has the default changed?
2168        default = _columndefault(rec)
2169        olddefault = _columndefault(recold)
2170        newdefault = _columndefault(recnew)
2171        if olddefault != newdefault:
2172            if newdefault != default:
2173                raise ConflictError(self, "default value unmergable")
2174            code.append(" default %s" % newdefault)
2175
2176        # Check nullability
2177        if recold.nullable != recnew.nullable:
2178            if recnew.nullable == "N":
2179                code.append(" not null")
2180            else:
2181                code.append(" null")
2182
2183        if term:
2184            code.append(";\n")
2185        else:
2186            code.append("\n")
2187
2188        return "".join(code)
2189
2190    def dropddl(self, connection=None, term=True):
2191        (connection, cursor) = self.getcursor(connection)
2192        name = self.name.split(".")
2193        code = "alter table %s drop column %s" % (getfullname(name[0], self.owner), getfullname(name[1], None))
2194        if term:
2195            code += ";\n"
2196        else:
2197            code += "\n"
2198        return code
2199
2200    def cdate(self, connection=None):
2201        # The column creation date is the table creation date
2202        (connection, cursor) = self.getcursor(connection)
2203        cursor.xexecute("select created from all_objects where lower(object_type)='table' and object_name=:name and owner=nvl(:owner, user)", name=self.name.split(".")[0], owner=self.owner)
2204        row = cursor.fetchone()
2205        if row is None:
2206            raise SQLObjectNotFoundError(self)
2207        return row[0]
2208
2209    def udate(self, connection=None):
2210        # The column modification date is the table modification date
2211        (connection, cursor) = self.getcursor(connection)
2212        cursor.xexecute("select last_ddl_time from all_objects where lower(object_type)='table' and object_name=:name and owner=nvl(:owner, user)", name=self.name.split(".")[0], owner=self.owner)
2213        row = cursor.fetchone()
2214        if row is None:
2215            raise SQLObjectNotFoundError(self)
2216        return row[0]
2217
2218    def iterreferences(self, connection=None):
2219        connection = self.getconnection(connection)
2220        name = self.name.split(".")
2221        yield Table(name[0], self.owner, connection)
2222
2223    def iterreferencedby(self, connection=None):
2224        if False:
2225            yield None
2226
2227    def datatype(self, connection=None):
2228        """
2229        The SQL type of this column.
2230        """
2231        (connection, cursor) = self.getcursor(connection)
2232        rec = self._getcolumnrecord(cursor)
2233        return _columntype(rec)
2234
2235    def default(self, connection=None):
2236        """
2237        The SQL default value for this column.
2238        """
2239        (connection, cursor) = self.getcursor(connection)
2240        rec = self._getcolumnrecord(cursor)
2241        return _columndefault(rec)
2242
2243    def nullable(self, connection=None):
2244        """
2245        Is this column nullable?
2246        """
2247        (connection, cursor) = self.getcursor(connection)
2248        rec = self._getcolumnrecord(cursor)
2249        return rec.nullable == "Y"
2250
2251    def comment(self, connection=None):
2252        """
2253        The comment for this column.
2254        """
2255        name = self.name.split(".")
2256        (connection, cursor) = self.getcursor(connection)
2257        cursor.xexecute("select comments from all_col_comments where owner=nvl(:owner, user) and table_name=:table_name and column_name=:column_name", owner=self.owner, table_name=name[0], column_name=name[1])
2258        rec = cursor.xfetchone()
2259        if rec is None:
2260            raise SQLObjectNotFoundError(self)
2261        return rec.comments or None
2262
2263    def __xattrs__(self, mode="default"):
2264        for attr in super(Column, self).__xattrs__(mode):
2265            if attr == "-createddl()":
2266                yield "-addddl()"
2267            else:
2268                yield attr
2269        yield "datatype()"
2270        yield "default()"
2271        yield "nullable()"
2272        yield "comment()"
2273
2274    def __iter__(self):
2275        return None
Note: See TracBrowser for help on using the browser.