root/livinglogic.python.orasql/src/ll/orasql/__init__.py @ 234:62c163941626

Revision 234:62c163941626, 76.1 KB (checked in by Walter Doerwald <walter@…>, 12 years ago)

The fancy fetch methods have been renamed to :meth:xfetchone,
:meth:xfetchmany, :meth:xfetchall and :meth:xfetch. :meth:__iter__
no longer gets overwritten. New methods :meth:xexecute and
:meth:xexecutemany have been added, that support passing unicode
parameters.

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