alltest.py

python web programming 部分

    c.SELECT("*", FROM="frequents", WHERE="perweek between 2 and (%s)" %
        c.SELECT("avg(perweek)", FROM="frequents")),
    c.SELECT("*", FROM="frequents", WHERE="perweek not between 2 and 5"),
    c.SELECT(["f.drinker", "l.beer", "s.bar"],
        FROM=(["frequents", "f"], ["serves", "s"], ["likes", "l"]),
        WHERE="f.drinker=l.drinker and s.bar=f.bar and s.beer=l.beer"),
    c.SELECT("*", FROM="serves"),
    c.SELECT("*", FROM="likes"),
    c.SELECT("*", FROM="frequents", WHERE="drinker = 'norm'"),
    c.UNION(c.SELECT("drinker", FROM="likes") ,
            c.SELECT("drinker", FROM="frequents")),
    c.UNION(c.SELECT("drinker", FROM="likes"),
            c.SELECT("distinct drinker", FROM="frequents")),
    c.SELECT("*", FROM="frequents", WHERE="drinker>'norm'"),
    c.SELECT("*", FROM="frequents", WHERE="drinker<='norm'"),
    c.SELECT("*", FROM="frequents", WHERE="drinker>'norm' or drinker<'b'"),
    c.SELECT("*", FROM="frequents", WHERE="drinker<>'norm' and 'pierre'<>drinker"),
    c.SELECT("*", FROM="frequents", WHERE="drinker<>'norm'"),
    c.SELECT("*", FROM=[["frequents", "f"], ["serves", "s"]], WHERE="f.bar = s.bar"),
    c.SELECT("*", FROM=[["frequents", "f"], ["serves", "s"]],
            WHERE="f.bar = s.bar and not exists(%s)" %
           c.SELECT("l.drinker, l.beer",
                  FROM="likes l",
                  WHERE="l.drinker=f.drinker and s.beer=l.beer")),
    c.SELECT("*", FROM=[["likes", "l"], ["frequents", "f"]],
                  WHERE="f.bar='cheers' and l.drinker=f.drinker and l.beer='bud'"),
    c.SELECT("*", FROM=["serves", "s"], WHERE="not exists (%s)" %
                  c.SELECT("*",
                          FROM=(["likes", "l"], ["frequents", "f"]),
                          WHERE="f.bar = s.bar and f.drinker=l.drinker and s.beer=l.beer")),
    c.SELECT("'nonbeer drinker ' || f.drinker",
                  FROM=["frequents", "f"],
                  WHERE="not exists (%s)" %
                  c.SELECT("l.drinker, l.beer",
                           FROM=["likes", "l"],
                           WHERE="l.drinker=f.drinker")),
    c.SELECT("l.drinker || ' likes ' || l.beer || ' but goes to no bar'",
                  FROM=["likes", "l"],
                  WHERE="not exists (%s)" %
                  c.SELECT("f.drinker",
                           FROM=["frequents", "f"],
                           WHERE="f.drinker=l.drinker")),
    c.SELECT("bar", FROM="frequents"),
    c.SELECT("distinct bar", FROM="frequents"),
    c.SELECT("sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)",
            FROM="serves"),
    c.SELECT("beer, sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)",
            FROM="serves",
            REST="group by beer"),
    c.SELECT("sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)",
            FROM="serves", WHERE="beer<>'bud'"),
    c.SELECT("bar, sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)",
            FROM="serves",
            WHERE="beer<>'bud'",
            REST="""group by bar
                having sum(quantity)>500 or count(*)>3
                order by 2 desc"""),
    c.SELECT("beer, sum(quantity), avg(quantity), count(*)",
            FROM="serves", WHERE="beer<>'bud'",
            REST="""group by beer
                having sum(quantity)>100
                order by 4 desc, beer"""),
    c.SELECT("l.drinker, l.beer, count(*), sum(l.perday*f.perweek)",
    	FROM="likes l, frequents f", WHERE="l.drinker=f.drinker",
            REST="""group by l.drinker, l.beer
                order by 4 desc, l.drinker, l.beer"""),
    c.SELECT("l.drinker, l.beer, f.bar, l.perday, f.perweek",
            FROM="likes l, frequents f",
            WHERE="l.drinker=f.drinker",
            REST="order by l.drinker, l.perday desc, f.perweek desc"),
    ]

    c.commit()
    print "\n+++RUNNING QUERIES+++\n"
    for x in queries:
        print; print
        print x
        sys.stdout.flush()
        c.execute(x)
        print cpp(c)
        sys.stdout.flush()

    print "\n+++PREPARING DYNAMIC QUERIES+++\n"
    dynamic_queries = [
    ( c.SELECT("bar", FROM="frequents", WHERE="drinker=?"), ("norm",) ), 
    ( c.SELECT("*", FROM="frequents", WHERE="drinker=? or bar=?"), ("norm", "cheers"))
    ]

    print "\n+++RUNNING DYNAMIC QUERIES+++\n"
    for (x,y) in dynamic_queries:
        print x
        print "dynamic data:", y
        c.execute(x, y)
        print cpp(c)

    print "\n+++PREPARING REPEAT TESTS+++\n"
    repeats = [
    c.SELECT(["f.drinker", "l.beer", "s.bar"],
            FROM=[["frequents", "f"], ["serves", "s"], ["likes", "l"]],
            WHERE="f.drinker=l.drinker and s.bar=f.bar and s.beer=l.beer"),
    c.SELECT("*",
       FROM=[["frequents", "f"], ["serves", "s"]],
       WHERE="""f.bar = s.bar and
         not exists(%s)""" %
           c.SELECT(["l.drinker", "l.beer"],
                FROM=["likes", "l"],
                WHERE="l.drinker=f.drinker and s.beer=l.beer")),
    c.SELECT("*", FROM="frequents",
            WHERE="drinker = 'norm'"),
    ]

    print "\n+++RUNNING REPEAT TESTS+++\n"
    from time import time
    for x in repeats:
        print "\nrepeating", x
        now = time()
        c.execute(x)
        print time()-now, "first time"
        now = time()
        c.execute(x)
        print time()-now, "second time"
        now = time()
        c.execute(x)
        print time()-now, "third time"
    print "*** committing work"
    print; print

    print "\n+++RUNNING POST-TEST CLEANUPS+++\n"
    print "(failures normally occur here too)"
    for q in cleanup_queries:
        try:
            print q
            c.execute(q)
        except: # this is a bit dodgy: error classes might vary
            pass

    c.commit()

    return

def dataseq(s):
    from string import split
    l = split(s, "\n")
    result = map(split, l)
    from string import atoi
    for l in result:
        l[2] = atoi(l[2])
    result = map(tuple, result)
    return result
    

updates = [
"""select * from frequents""",
"""select * from likes""",
"""select * from serves""",
"""select bar, sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)
   from serves
   where beer<>'bud'
   group by bar
   having sum(quantity)>500 or count(*)>3
   order by 2 desc
""",

"""select count(*), d from nondrinkers group by d""",
"""insert into frequents (drinker, perweek, bar)
   values ('billybob', 4, 'cheers')""",
"""select * from nondrinkers""",
"""create table templikes (dr varchar, be varchar)""",
"""select * from templikes""",
"""insert into templikes(dr, be)
   select drinker, beer from likes""",
"""create index tdindex on templikes(dr)""",
"""create index tbindex on templikes(be)""",
"""select * from templikes""",
"""delete from templikes where be='rollingrock' """,
"""select * from templikes""",
"""update templikes set dr=dr+'an' where dr='norm' """,
"""drop index tdindex""",
"""delete from templikes
   where dr=(select min(dr) from templikes)""",
"""insert into templikes (dr, be) 
   select max(dr), min(be) from templikes""",
"""select * from templikes""",
"""select * from frequents""",
"""update frequents 
   set perweek=(select max(perweek) 
                from frequents
                where drinker='norm')
   where drinker='woody'""",
"""select * from frequents""",
"""create view lazy as
   select drinker, sum(perweek) as wasted 
   from frequents
   group by drinker
   having sum(perweek)>4
   order by drinker""",
"""select * from lazy""",
"""drop view lazy""",
"""drop table templikes""",
]

trace_updates = [
"""drop index tdindex""",
]

rollback_queries = [
"""select * from likes""",
"""select * from frequents""",
"""select * from nondrinkers""",
"""select * from alldrinkers""",
"""select * from dummy""",
]
rollback_updates = [
"""create table dummy (nothing varchar)""",
"""insert into frequents(drinker, bar, perweek)
   values ('nobody', 'nobar', 0)""",
"""insert into likes(drinker, beer, perday)
   values ('wally', 'nobar', 0)""",
"""drop view alldrinkers""",
]
keep_updates = [
"""insert into frequents(drinker, bar, perweek)
   values ('peter', 'pans', 1)""",
"""create view alldrinkers as
    select drinker from frequents
    union
    select drinker from likes""",
]

def rollbacktest(directory):
    print "*" * 30
    print "*** recovery test ***"
    print; print; print
    import sys
    from gadfly import gadfly
    print "*** connecting"
    connect = gadfly("test", directory)
    cursor = connect.cursor()
    connect.autocheckpoint = 0
    print "*** executing updates to commit"
    for x in keep_updates:
        print x
        cursor.execute(x)
    connect.verbose=1
    print "*** COMMITTING OPERATIONS (connection set to verbose)"
    connect.commit()
    print "*** DUMP LOG"
    connect.dumplog()
    print; print "*** RUNNING OPS TO ROLL BACK"
    preresults = []
    for s in rollback_queries:
        print; print; print s
        try:
            cursor.execute(s)
            preresults.append(cursor.fetchall())
            print cursor.pp()
        except:
            d = sys.exc_type
            print "exception", d
            preresults.append(d)
    print; print "*** now updating with ops to rollback"
    for s in rollback_updates:
        print; print; print s
        cursor.execute(s)
    print; print; print "*** testing noncommitted results"
    for dummy in (1,2):
        postresults = []
        for s in rollback_queries:
            print s
            try:
                cursor.execute(s)
                postresults.append(cursor.fetchall())
                print cursor.pp()
            except:
                d = sys.exc_type
                print "*** exception", d
                postresults.append(d)
        if preresults==postresults:
            print "*** same results as before uncommitted updates"
        else:
            print "*** differing results from before uncommitted updates"
        if dummy==1:
            print; print "*** ROLLING BACK!"
            connect.rollback()
    print; print "*** EMULATING RECOVERY"
    for s in rollback_updates:
        print; print; print s
        cursor.execute(s)
    for dummy in (1,2):
        postresults = []
        for s in rollback_queries:
            print s
            try: 
                cursor.execute(s)
                postresults.append(cursor.fetchall())
                print cursor.pp()
            except:
                d = sys.exc_type
                print "*** exception", d
                postresults.append(d)
        if preresults==postresults:
            print "*** same results as before uncommitted updates"
        else:
            print "*** differing results from before uncommitted updates"
        if dummy==1:
            print "*** RESTART: DUMPLOG"
            connect.dumplog()
            print "*** RESTARTING (RECOVER FROM LOG, DISCARD UNCOMMITTED)"
            connect.restart()
    
def retest(directory): 
    print "*" * 30
    print "*** reconnect test"
    from gadfly import gadfly
    connect = gadfly("test", directory)
    cursor = connect.cursor()
    for s in updates:
        print; print
        print s
        if s in trace_updates:
            cursor.EVAL_DUMP = 1
        cursor.execute(s)
        cursor.EVAL_DUMP = 0
        print cursor.pp()
    #print; print "CONNECTION DATA BEFORE COMMIT"
    #connect.DUMP_ALL()
    c.commit()
    #print; print "CONNECTION DATA AFTER COMMIT"
    #connect.DUMP_ALL()
    connect.close()
    return connect
    
if __name__=="__main__":
    import sys
    import mx.ODBC.Windows
    from gadflyCursor import gadflyCursor
    from jetCursor import jetCursor
    from ocelotCursor import ocelotCursor
    from ssCursor import ssCursor
    from baseCursor import baseCursor
    from oracleCursor import oracleCursor
    import gadfly

    class testCursor(jetCursor):

        def execute(self, query, data=None):
            # self.description = None
            # return
            try:
                if data:
                    return self.cursor.execute(query, data)
                else:
                    return self.cursor.execute(query)
            except:
                print "*** DATABASE OPERATION ERROR ***"
                print "Exception data:\n%s\n(%s)" %(sys.exc_type, str(sys.exc_value).replace("[","\n["))
                sys.stdout.flush()

        def commit(self):
            pass

#    argv = sys.argv
#    if len(argv)<2:
#        print "USAGE: python %s <db_directory>"
#        print "  please provide a directory for test database!"
#    else:
#        directory = argv[1]
#        connect = gadfly.gadfly()
#        connect.startup("test", directory)
#        c = testcursor(connect.cursor())
#        test(c)

#    connect = mx.ODBC.Windows.connect("testORA", user="scott", password="tiger")
#    connect = mx.ODBC.Windows.connect("MQIS", user="SA")
    connect = mx.ODBC.Windows.connect("WebData")
#    c = testCursor(connect.cursor(), connect)
#    c = testCursor(baseCursor(None, None), None)
#    connect = mx.ODBC.Windows.connect("testOcelot")
    c = testCursor(connect.cursor(), connect)
    test(c, None)