root/livinglogic.python.orasql/src/ll/orasql/__init__.py @ 181:46cc82c90d7a

Revision 181:46cc82c90d7a, 70.2 KB (checked in by Walter Doerwald <walter@…>, 13 years ago)

Complain where there's no source code for the object, i.e. the object
doesn't exist.

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