"""test script for gadfly

usage gftest.py <directory>

run in current directory creates a database in files
  test.dfs LIKES.grl SERVES.grl FREQUENTS.grl
"""

def test(directory):
    print "testing"
    from gadfly import gadfly
    connect = gadfly()
    connect.startup("test", directory)
    curs = connect.cursor()
    print
    print "TABLE CREATES"
    for x in table_creates:
        print x
        curs.execute(x)
    curs.execute("create table empty (nothing varchar)")
    C = """
    CREATE TABLE work (
       name VARCHAR,
       hours INTEGER,
       rate FLOAT)
       """
    print C
    curs.execute(C)
    print
    C = """
    CREATE TABLE accesses (
       page VARCHAR,
       hits INTEGER,
       month INTEGER)
       """
    print C
    curs.execute(C)
    print
    print "INSERTS"
    C = """
    INSERT INTO work(name, hours, rate) VALUES (?, ?, ?)
    """
    D = [
         ("sam", 30, 40.2),
         ("norm", 45, 10.2),
         ("woody", 80, 5.4),
         ("diane", 3, 4.4),
         ("rebecca", 120, 12.9),
         ("cliff", 26, 200.00),
         ("carla", 9, 3.5),
         ]
    for x in D: print x
    curs.execute(C, D)
    C = "create unique index wname on work(name)"
    print "Unique index:", C
    curs.execute(C)
    print "trying bad insert into unique field"
    C = "insert into work(name, hours, rate) values ('sam', 0, 0)"
    import sys
    try:
        curs.execute(C)
    except:
        print "exception as expected %s(%s)" %(sys.exc_type, sys.exc_value)
    else:
        raise "stop!", "unique index permits nonunique field"
    print; print "testing introspection"
    itests = ["select 10*4 from dual",
              "select * from __table_names__",
              #"select * from __datadefs__", # needs formatting
              "select * from __indices__",
              "select * from __columns__",
              "select * from __indexcols__",
              """
              select i.index_name, is_unique, table_name, column_name
              from __indexcols__ c, __indices__ i
              where c.index_name = i.index_name""",
              ]
    for C in itests:
        print C
        print
        curs.execute(C)
        print curs.pp()
        print
    print "testing complex, neg literals in insert"
    curs.execute("insert into work(name, hours, rate) values ('jo', -1, 3.1e-44-1e26j)")
    curs.execute("select * from work")
    print curs.pp()
    print "deleting jo"; print
    curs.execute("delete from work where name='jo'")
    C = """
    INSERT INTO accesses(page, month, hits) VALUES (?, ?, ?)
    """
    D = [
         ("index.html", 1, 2100),
         ("index.html", 2, 3300),
         ("index.html", 3, 1950),    
         ("products.html", 1, 15),   
         ("products.html", 2, 650),   
         ("products.html", 3, 98),   
         ("people.html", 1, 439),
         ("people.html", 2, 12),
         ("people.html", 3, 665),
         ]
    for x in D: print x
    curs.execute(C, D)
    for (table, stuff) in dpairs:
        ins = "insert into %s values (?, ?, ?)" % table
        if table!="frequents":
           for parameters in dataseq(stuff):
               print "singleinsert", table, parameters
               curs.execute(ins, parameters)
        else:
           print
           print "multiinsert", table
           parameters = dataseq(stuff)
           for p in parameters:
               print p
           print "multiinsert..."
           curs.execute(ins, parameters)
           print;print
    print
    print "INDICES"
    for ci in indices:
        print ci
        curs.execute(ci)
    print
    print "QUERIES"
    for x in workqueries:
        print;print
        print x
        curs.execute(x)
        print curs.pp()
        
    statement = """select name, hours
                   from work"""
    curs.execute(statement)
    print "Hours worked this week"
    print
    for (name,hours) in curs.fetchall():
        print "worker", name, "worked", hours, "hours"
    print
    print "end of work report"

    #return
    for x in queries:
        print; print
        print x
        curs.execute(x)
        #for x in curs.commands:
        #    print x
        all = curs.fetchall()
        if not all:
           print "empty!"
        else:
           print curs.pp()
           #for t in all:
               #print t
    #return
    print
    print "DYNAMIC QUERIES"
    for (x,y) in dynamic_queries:
        print; print
        print x
        print "dynamic=", y
        curs.execute(x, y)
        #for x in curs.commands:
        #    print x
        all = curs.fetchall()
        if not all:
           print "empty!"
        else:
           for t in all:
               print t
    print "repeat test"
    from time import time
    for x in repeats:
        print "repeating", x
        now = time()
        curs.execute(x)
        print time()-now, "first time"
        now = time()
        curs.execute(x)
        print time()-now, "second time"
        now = time()
        curs.execute(x)
        print time()-now, "third time"
    print "*** committing work"
    connect.commit()
    connect.close()
    print; print
    return connect
    
table_creates = [
 "create table frequents (drinker varchar, bar varchar, perweek integer)",
 "create table likes (drinker varchar, beer varchar, perday integer)",
 "create table serves (bar varchar, beer varchar, quantity integer)",
 """Create view nondrinkers(d, b)
    as select drinker, bar
       from frequents
       where drinker not in
         (select drinker from likes)""",
 ]
 
fdata = """\
  adam	lolas		1
  woody	cheers	5
  sam		cheers	5
  norm	cheers	3
  wilt	joes		2
  norm	joes		1
  lola	lolas		6
  norm	lolas		2
  woody	lolas		1
  pierre	frankies	0"""
  
sdata = """\
  cheers	bud		500
  cheers	samaddams	255
  joes	bud		217
  joes	samaddams	13
  joes	mickies	2222
  lolas	mickies	1515
  lolas	pabst		333
  winkos	rollingrock	432
  frankies	snafu       5"""
  
ldata = """\
  adam	bud			2
  wilt	rollingrock		1
  sam		bud			2
  norm	rollingrock		3
  norm	bud			2
  nan		sierranevada	1	
  woody	pabst			2
  lola	mickies		5"""
  
dpairs = [
   ("frequents", fdata),
   ("serves", sdata),
   ("likes", ldata),
  ]
  
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
    
indices = [
"""create index fd on frequents (drinker)""",
"""create index sbb on serves (beer, bar)""",
"""create index lb on likes (beer)""",
"""create index fb on frequents (bar)""",
]

repeats = [
"""-- drinkers bars and beers
   -- where the drinker likes the beer
   -- the bar serves the beer
   -- and the drinker frequents the bar
   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""",
"""select *
   from frequents as f, serves as s
   where f.bar = s.bar and
     not exists(
       select l.drinker, l.beer
       from likes l
       where l.drinker=f.drinker and s.beer=l.beer)""",
"""select * from frequents
   where drinker = 'norm'""",
]

workqueries = [
"""-- everything from work
   select * from work""",
# stupid tests
"""select median(hours)
   from work""",
"""select *
   from work
   where name='carla' -- just carla""",
"""select name, ' ain''t worth ', rate
   from work -- the works table has more columns
   where name='carla'""",
"""select name, -- name of worker
          hours -- hours worked
   from work""",
"""select name, hours*rate as pay
   from work
   order by name""",
"""select name, rate
   from work
   where rate>=20 and rate<=100""",
"""select name, rate
   from work
   where rate between 20 and 100""",
"""select name, rate
   from work
   where rate not between 20 and 100""",
"""select name, rate, hours, hours*rate as pay
   from work""",
"""select name, rate, hours, hours*rate as pay
   from work
   where hours*rate>500 and (rate<100 or hours>5)""",
"""select name, rate, hours, hours*rate as pay
   from work
   where hours*rate>500 and rate<100 or hours>5""",
"""select avg(rate), min(hours), max(hours), sum(hours*rate) as expenses
   from work""",
"""select * from accesses""",
"""select month, sum(hits) as totalhits
   from accesses
   where month<>1
   group by month
   order by 2""",
"""select month, sum(hits) as totalhits
   from accesses
   group by month
   order by 2 desc""",
"""select month, sum(hits) as totalhits
   from accesses
   group by month
   having sum(hits)<3000
   order by 2 desc""",
"""select count(distinct month), count(distinct page)
   from accesses""",
"""select month, hits, page
   from  accesses
   order by month, hits desc""",
]
    
queries = [
"""select * from nondrinkers""",
"""select drinker as x from likes
   union select beer as x from serves
          union select drinker as x from frequents""",
"""select f.drinker, s.bar, l.beer
   from frequents f, serves s, likes l
   where f.drinker=l.drinker and s.beer=l.beer and s.bar=f.bar""",
"""select * from
   likes where beer in ('bud', 'pabst')""",
"""select l.beer, l.drinker, count(distinct s.bar)
   from likes l, serves s
   where l.beer=s.beer
   group by l.beer, l.drinker
   order by 3 desc""",
"""select l.beer, l.drinker, count(distinct s.bar) as nbars
   from likes l, serves s
   where l.beer=s.beer
   group by l.beer, l.drinker
    union distinct
     select beer, drinker, 0 as nbars
     from likes
     where beer not in (select beer from serves)
   order by 3 desc""",
"""select avg(perweek) from frequents""",
"""select * 
   from frequents
   where perweek <= (select avg(perweek) from frequents)""",
"""select * 
   from serves""",
"""select bar, avg(quantity)
   from serves
   group by bar""",
"""select * 
   from serves s1
   where quantity <= (select avg(quantity) 
                      from serves s2 
                      where s1.bar=s2.bar)""",
"""select * from frequents
   where perweek > (select avg(perweek) from frequents)""",
"""select * from frequents f1
   where perweek > (
   select avg(perweek) from frequents f2
   where f1.drinker = f2.drinker)""",
"""select * from frequents
   where perweek < any (select perweek from frequents)""",
"""select * from frequents
   where perweek >= all (select perweek from frequents)""",
"""select * from frequents
   where perweek <= all (select perweek from frequents)""",
"""select * from frequents f1
   where perweek < any 
   (select perweek from frequents f2
    where f1.drinker = f2.drinker)""",
"""select * from frequents f1
   where perweek = all 
   (select perweek from frequents f2
    where f1.drinker = f2.drinker)""",
"""select * from frequents f1
   where perweek <> all
   (select perweek from frequents f2
    where f1.drinker <> f2.drinker)""",
"""select beer
   from serves
   where beer = any (select beer from likes)""",
"""select beer
   from serves
   where beer <> all (select beer from likes)""",
"""select beer
   from serves
   where beer in (select beer from likes)""",
"""select beer
   from serves
   where beer not in (select beer from likes)""",
#"""select f1.drinker, f2.drinker
#   from frequents f1, frequents f2
#   where f1.drinker<>f2.drinker""",
#"""select *
#   from frequents f1
#   where not exists
#   (select f2.drinker
#    from frequents f2
#    where f1.drinker<>f2.drinker and f1.bar=f2.bar)""",
"""select *
   from frequents
   where perweek between 2 and 
        (select avg(perweek) from frequents)""",
"""select *
   from frequents
   where perweek not between 2 and 5""",
#   "stop",
"""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""",
   #"stop!",
"""select * from serves""",
"""select * from likes""",
"""select * from frequents
   where drinker = 'norm'""",
"""select drinker from likes
   union
   select drinker from frequents""",
"""select drinker from likes
   union distinct
   select drinker from frequents""",
"""select drinker from likes
   except
   select drinker from frequents""",
"""select drinker from likes
   intersect
   select drinker from frequents""",
#   "stop!",
"""select * from frequents
   where drinker>'norm'""",
"""select * from frequents
   where drinker<='norm'""",
"""select * from frequents
   where drinker>'norm' or drinker<'b'""",
"""select * from frequents
   where drinker<>'norm' and 'pierre'<>drinker""",
"""select * from frequents
   where drinker<>'norm'""",
"""select (drinker+' ')*2+bar
   from frequents
   where drinker>bar""",
"""select *
   from frequents as f, serves as s
   where f.bar = s.bar""",
"""select *
   from frequents as f, serves as s
   where f.bar = s.bar and
     not exists(
       select l.drinker, l.beer
       from likes l
       where l.drinker=f.drinker and s.beer=l.beer)""",
"""select *
   from likes l, frequents f
   where f.bar='cheers' and l.drinker=f.drinker and l.beer='bud'""",
"""select *
   from serves s
   where not exists (
     select *
     from likes l, frequents f
     where f.bar = s.bar and f.drinker=l.drinker and s.beer=l.beer)""",
"""select 'nonbeer drinker '+f.drinker
   from frequents f
   where not exists
      (select l.drinker, l.beer from likes l where l.drinker=f.drinker)""",
"""select l.drinker+' likes '+l.beer+' but goes to no bar'
   from likes l
   where not exists (select f.drinker from frequents f where f.drinker=l.drinker)""",
"""select bar from frequents""",
"""select distinct bar from frequents""",
"""select sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)
   from serves""",
"""select beer, sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)
   from serves 
   group by beer""",
"""select sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)
   from serves
   where beer<>'bud'
""",
"""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 beer, sum(quantity), avg(quantity), count(*)
   from serves
   where beer<>'bud'
   group by beer
   having sum(quantity)>100
   order by 4 desc, beer
""",
"""select l.drinker, l.beer, count(*), sum(l.perday*f.perweek)
   from likes l, frequents f
   where l.drinker=f.drinker
   group by l.drinker, l.beer
   order by 4 desc, l.drinker, l.beer
""",
"""select l.drinker, l.beer, f.bar, l.perday, f.perweek
   from likes l, frequents f
   where l.drinker=f.drinker
   order by l.drinker, l.perday desc, f.perweek desc
""",
]

dynamic_queries = [
( "select bar from frequents where drinker=?", ("norm",) ), 
( "select * from frequents where drinker=? or bar=?", ("norm", "cheers") )
]

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()
    connect.commit()
    #print; print "CONNECTION DATA AFTER COMMIT"
    #connect.DUMP_ALL()
    connect.close()
    return connect
    
if __name__=="__main__":
   import sys
   argv = sys.argv
   if len(argv)<2:
      print "USAGE: python <thismodule> <db_directory>"
      print "  please provide a directory for test database!"
   else:
      directory = argv[1]
      test(directory)
      rollbacktest(directory)
      retest(directory)