Changeset 26:996ecb496537 in livinglogic.python.orasql

Show
Ignore:
Timestamp:
06/20/05 15:27:02 (15 years ago)
Author:
Walter Doerwald <walter@…>
Branch:
default
Message:

Add generators that return commands for clearing or recreating a schema.

Files:
4 modified

Legend:

Unmodified
Added
Removed
  • NEWS.xml

    r25 r26  
     1<section><title>Changes in 0.6 (released 06/20/2005)</title> 
     2<ulist> 
     3<item>Two new functions have been added: <function>iterdrop</function> is 
     4a generator that yields information about how to clear the schema (i.e. drop 
     5all table, sequences, etc.). <function>itercreate</function> yields information 
     6about how to recreate a schema.</item> 
     7</ulist> 
     8</section> 
     9 
     10 
    111<section><title>Changes in 0.5 (released 06/07/2005)</title> 
    212<ulist> 
  • README

    r23 r26  
    1 ll-orasql 0.4.1 
     1ll-orasql 0.6 
    22 
    33Utilities for working with cx_Oracle. 
  • orasql.py

    r25 r26  
    198198    def __iter__(self): 
    199199        return self.fetch() 
     200 
     201 
     202def formatstring(value): 
     203    result = [] 
     204    current = [] 
     205 
     206    # Helper function: move the content of current to result 
     207    def shipcurrent(force=False): 
     208        if current and (force or (len(current) > 2000)): 
     209            if result: 
     210                result.append(" || ") 
     211            result.append("'%s'" % "".join(current)) 
     212 
     213    for c in value: 
     214        if c == "'": 
     215            current.append("''") 
     216            shipcurrent() 
     217        elif ord(c) < 32 or ord(c)>127: 
     218            shipcurrent(True) 
     219            current = [] 
     220            if result: 
     221                result.append(" || ") 
     222            result.append("chr(%d)" % ord(c)) 
     223        else: 
     224            current.append(c) 
     225            shipcurrent() 
     226    shipcurrent(True) 
     227    return "".join(result) 
     228 
     229 
     230def iterdrop(cursor, fks="disable"): 
     231    """ 
     232    <par>Return an iterator that gives DDL statements that will completely clear the given 
     233    schema, i.e. all functions, procedures, constraints, tables, views and sequences will be dropped.</par> 
     234    <par>The items yielded are tuples consisting of the type of the object, the name of the 
     235    object and the PL/SQL command to be used to drop the object.</par> 
     236    <par><arg>fks</arg> specifies own foreign keys referencing this schema will be treated:</par> 
     237    <dlist> 
     238    <term>"keep"</term><item>Don't drop or disable these constraints (This <em>will</em> generate errors);</item> 
     239    <term>"disable"</term><item>Disable these constraints;</item> 
     240    <term>"drop"</term><item>Drop these constraints.</item> 
     241    </dlist> 
     242    """ 
     243    # Find out who we are 
     244    cursor.execute("select user from dual") 
     245    user = cursor.fetchone().user 
     246 
     247    # drop all procedures and functions 
     248    cursor.execute("select distinct type, name from user_source") 
     249    for rec in cursor: 
     250        if rec.type=="PROCEDURE": 
     251            yield ("procedure", rec.name, "drop procedure %s;" % rec.name) 
     252        elif rec.type=="FUNCTION": 
     253            yield ("function", rec.name, "drop function %s;" % rec.name) 
     254 
     255    # drop all constraints (in the order 'C', 'U', 'R', 'P') 
     256    for (type, desc) in [("C", "check"), ("U", "unique"), ("R", "fk"), ("P", "pk")]: 
     257        cursor.execute("select owner, constraint_name, table_name, r_owner, r_constraint_name from all_constraints where constraint_type='%s'" % type) 
     258        for rec in cursor: 
     259            if rec.owner==user: 
     260                yield (desc, rec.constraint_name, "alter table %s drop constraint %s;" % (rec.table_name, rec.constraint_name)) 
     261            elif rec.r_owner==user: 
     262                name = "%s.%s" % (rec.owner, rec.constraint_name) 
     263                if fks=="disable": 
     264                    yield (desc, name, "alter table %s.%s disable constraint %s;" % (rec.owner, rec.table_name, rec.constraint_name)) 
     265                elif fks=="drop": 
     266                    yield (desc, name, "alter table %s.%s drop constraint %s;" % (rec.owner, rec.table_name, rec.constraint_name)) 
     267 
     268    # drop all views 
     269    cursor.execute("select view_name from user_views") 
     270    for rec in cursor: 
     271        yield ("view", rec.view_name, "drop view %s;" % rec.view_name) 
     272 
     273    # drop all sequences 
     274    cursor.execute("select sequence_name from user_sequences") 
     275    for rec in cursor: 
     276        yield ("sequence", rec.sequence_name, "drop sequence %s;" % rec.sequence_name) 
     277 
     278    # drop all triggers 
     279    cursor.execute("select trigger_name from user_triggers") 
     280    for rec in cursor: 
     281        yield ("trigger", rec.trigger_name, "drop trigger %s;" % rec.trigger_name) 
     282 
     283    # finally drop all tables 
     284    cursor.execute("select table_name from user_tables where nested = 'NO'") 
     285    for rec in cursor: 
     286        yield ("table", rec.table_name, "drop table %s;" % rec.table_name) 
     287 
     288 
     289def _itercreate(cursor, type, name, cache): 
     290    if (type, name) not in cache: 
     291        if type in ("view", "procedure", "function", "type", "trigger"): 
     292            cursor.execute("select referenced_type, referenced_name from user_dependencies where lower(type)=:type and name=:name and referenced_owner=user", type=type, name=name) 
     293            for rec in cursor.fetchall(): 
     294                if rec.referenced_type != "NON-EXISTENT": 
     295                    for result in _itercreate(cursor, rec.referenced_type.lower(), rec.referenced_name, cache): 
     296                        yield result 
     297         
     298        if type == "sequence": 
     299            cursor.execute("select * from user_sequences where sequence_name=:name", name=name) 
     300            rec = cursor.fetchone() 
     301            code  = "create sequence %s\n" % rec.sequence_name.lower() 
     302            code += "\tincrement by %d\n" % rec.increment_by 
     303            code += "\tstart with %d\n" % rec.min_value 
     304            code += "\tmaxvalue %s\n" % rec.max_value 
     305            code += "\tminvalue %d\n" % rec.min_value 
     306            code += "\t%scycle\n" % ["no", ""][rec.cycle_flag=='Y'] 
     307            code += "\tcache %d\n" % rec.cache_size 
     308            code += "\t%sorder;\n" % ["no", ""][rec.order_flag=='Y'] 
     309        elif type == "table": 
     310            code = ["create table %s\n(\n" % name] 
     311            cursor.execute("select * from user_tab_columns where table_name=:name order by column_id asc", name=name) 
     312            for rec in cursor: 
     313                fsize = rec.data_precision 
     314                flen = rec.data_length 
     315                fprec = rec.data_scale 
     316                ftype = rec.data_type.lower() 
     317                code.append("\t%s %s" % (rec.column_name.lower(), ftype)) 
     318                if rec.char_length: 
     319                    fsize = rec.char_length 
     320                if fsize is not None: 
     321                    code.append("(%d" % fsize) 
     322                    if fprec is not None: 
     323                        code.append(", %d" % fprec) 
     324                    code.append(")") 
     325                if rec.data_default is not None: 
     326                    code.append(" default %s" % rec.data_default) 
     327                if rec.nullable == 'N': 
     328                    code.append(" not null") 
     329                code.append(",\n") 
     330            del code[-1] 
     331            code.append("\n);\n") 
     332            code = "".join(code) 
     333        elif type == "pk": 
     334            cursor.execute("select constraint_name, table_name, r_owner, r_constraint_name from user_constraints where constraint_type='P' and constraint_name=:name", name=name) 
     335            table_name = cursor.fetchone().table_name 
     336            cursor.execute("select column_name from user_cons_columns where constraint_name=:name", name=name) 
     337            code = "alter table %s add constraint %s primary key(%s);\n" % (table_name, name, ", ".join(r.column_name.lower() for r in cursor)) 
     338        elif type == "comments": 
     339            cursor.execute("select column_name, comments from user_col_comments where table_name=:name and comments is not null", name=name) 
     340            code = "\n".join("comment on column %s.%s is %s;" % (name, r.column_name, formatstring(r.comments)) for r in cursor) + "\n" 
     341        elif type == "fk": 
     342            cursor.execute("select table_name from user_constraints uc where uc.constraint_name=:name", name=name) 
     343            table_name = cursor.fetchone().table_name 
     344            cursor.execute("select column_name from user_cons_columns where constraint_name=:name order by position", name=name) 
     345            recs1 = cursor.fetchall() 
     346            cursor.execute("select r_constraint_name from user_constraints where constraint_name=:name", name=name) 
     347            r_constraint_name = cursor.fetchone().r_constraint_name 
     348            cursor.execute("select table_name, column_name from user_cons_columns where constraint_name=:name order by position", name=r_constraint_name) 
     349            recs2 = cursor.fetchall() 
     350            code = "alter table %s add constraint %s foreign key (%s) references %s;\n" % (table_name, name, ", ".join(r.column_name for r in recs1), ", ".join("%s(%s)" % (r.table_name, r.column_name) for r in recs2)) 
     351        elif type == "unique": 
     352            cursor.execute("select table_name, r_owner, r_constraint_name from user_constraints where constraint_type='U' and constraint_name=:name", name=name) 
     353            table_name = cursor.fetchone().table_name 
     354            cursor.execute("select column_name from user_cons_columns where constraint_name=:name", name=name) 
     355            code = "alter table %s add constraint %s unique(%s);\n" % (table_name, name, ", ".join(r.column_name for r in cursor)) 
     356        elif type == "view": 
     357            cursor.execute("select text from user_views where view_name=:name", name=name) 
     358            text = cursor.fetchone().text 
     359            code = "create or replace view %s as\n\t%s;\n/\n" % (name, text.strip()) 
     360        elif type in ("function", "procedure", "package", "type"): 
     361            cursor.execute("select text from user_source where lower(type)=:type and name=:name order by line", type=type, name=name) 
     362            code = "".join(rec.text for rec in cursor) 
     363            code = "create or replace %s\n/\n" % code.strip() 
     364        else: 
     365            raise ValueError("unknown type %r" % type) 
     366        cache[(type, name)] = code 
     367        yield (type, name, code) 
     368 
     369 
     370def itercreate(cursor): 
     371    """ 
     372    <par>Return an iterator that yields DDL statements which will recreate the schema.</par> 
     373    <par>Items yielded are tuples containing the type of the object, the name of the object 
     374    and the PL/SQL code used to recreate the object.</par> 
     375    """ 
     376    cache = {} 
     377 
     378    # Sequences 
     379    cursor.execute("select sequence_name from user_sequences") 
     380    for rec in cursor.fetchall(): 
     381        for result in _itercreate(cursor, "sequence", rec.sequence_name, cache): 
     382            yield result 
     383 
     384    # Tables 
     385    cursor.execute("select table_name from user_tables") 
     386    for rec in cursor.fetchall(): 
     387        for result in _itercreate(cursor, "table", rec.table_name, cache): 
     388            yield result 
     389 
     390        # Primary key 
     391        cursor.execute("select constraint_name from user_constraints where constraint_type='P' and table_name=:name", name=rec.table_name) 
     392        for rec2 in cursor.fetchall(): 
     393            for result in _itercreate(cursor, "pk", rec2.constraint_name, cache): 
     394                yield result 
     395 
     396        for result in _itercreate(cursor, "comments", rec.table_name, cache): 
     397            yield result 
     398 
     399    # Foreign keys 
     400    cursor.execute("select * from user_constraints where constraint_type='R'") 
     401    for rec in cursor.fetchall(): 
     402        for result in _itercreate(cursor, "fk", rec.constraint_name, cache): 
     403            yield result 
     404 
     405    # Unique constraints 
     406    cursor.execute("select constraint_name from user_constraints where constraint_type='U'") 
     407    for rec in cursor.fetchall(): 
     408        for result in _itercreate(cursor, "unique", rec.constraint_name, cache): 
     409            yield result 
     410 
     411    # Views 
     412    cursor.execute("select view_name from user_views") 
     413    for rec in cursor.fetchall(): 
     414        for result in _itercreate(cursor, "view", rec.view_name, cache): 
     415            yield result 
     416 
     417    # Functions, procedures, packages, types 
     418    for type in ["function", "procedure", "package", "type"]: 
     419        cursor.execute("select object_name from user_objects where lower(object_type)=:type", type=type) 
     420        for rec in cursor.fetchall(): 
     421            for result in _itercreate(cursor, type, rec.object_name, cache): 
     422                yield result 
  • setup.py

    r25 r26  
    3737setup( 
    3838    name="ll-orasql", 
    39     version="0.5", 
     39    version="0.6", 
    4040    description="Utilities for working with cx_Oracle", 
    4141    long_description=DESCRIPTION,