dbtest.py

python web programming 部分

"""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 f, serves 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 workt
"""select * from workt""",
# stupid tests
"""select avg(hours)
   from workt""",
"""select *
   from workt
   where name='carla' -- just carla""",
"""select name, ' ain''t worth ', rate
   from workt -- the works table has more columns
   where name='carla'""",
"""select name, -- name of worker
          hours -- hours worked
   from workt""",
"""select name, hours*rate as pay
   from workt
   order by name""",
"""select name, rate
   from workt
   where rate>=20 and rate<=100""",
"""select name, rate
   from workt
   where rate between 20 and 100""",
"""select name, rate
   from workt
   where rate not between 20 and 100""",
"""select name, rate, hours, hours*rate as pay
   from workt""",
"""select name, rate, hours, hours*rate as pay
   from workt
   where hours*rate>500 and (rate<100 or hours>5)""",
"""select name, rate, hours, hours*rate as pay
   from workt
   where hours*rate>500 and rate<100 or hours>5""",
"""select avg(rate), min(hours), max(hours), sum(hours*rate) as expenses
   from workt""",
"""select * from pagerefs""",
"""select mnth, sum(hits) as totalhits
   from pagerefs
   where mnth<>1
   group by mnth
   order by 2""",
"""select mnth, sum(hits) as totalhits
   from pagerefs
   group by mnth
   order by 2 desc""",
"""select mnth, sum(hits) as totalhits
   from pagerefs
   group by mnth
   having sum(hits)<3000
   order by 2 desc""",
"""select count(distinct mnth), count(distinct page)
   from pagerefs""",
"""select mnth, hits, page
   from  pagerefs
   order by mnth, 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
     select distinct 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 *
   from frequents
   where perweek between 2 and 
        (select avg(perweek) from frequents)""",
"""select *
   from frequents
   where perweek not between 2 and 5""",
"""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
   select distinct drinker from frequents""",
"""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 *
   from frequents f, serves s
   where f.bar = s.bar""",
"""select *
   from frequents f, serves 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 ' """ + DBcatop + """ 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(10), be varchar(12))""",
"""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 """ + DBcatop + """ 'an' where dr='norm' """,
"""drop index tdindex""", # SQL Server requires tablename
"""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""",
"""select * from lazy""",
"""drop view lazy""",
"""drop table templikes""",
]


cleanup_queries = [
#"""drop index wname""",
"""drop view nondrinkers""",
"""drop table workt""",
"""drop table pagerefs""",
"""drop table empty""",
"""drop table frequents""",
"""drop table likes""",
"""drop table serves""",
]

table_names = [
'workt',
'pagerefs',
'empty',
'frequents',
'likes',
'serves',
]

test()