Пример #1
0
 def __init__(self, query):
     while query[-1] in [" ", ";"]:
         query = query[:-1]
     self.query = query
     self.columns = []
     self.allColumns = False
     self.tables = []
     self.where = False
     self.twoWhere = False
     self.joinPresent = False
     self.andConditions = []
     self.orConditions = []
     self.tempConditions = []
     self.aggFn = []
     self.aggCol = []
     # self.keywords = ['select', 'from', 'where', 'distinct', 'sum', 'avg', 'max', 'min', 'and', 'or']
     # self.aggKeywords = ['max', 'min', 'sum', 'avg', 'distinct']
     self.stmt = None
     # To check consistency
     self.DB = table.initializeTables()
     try:
         parsed = sqlparse.parse(query)
     except:
         print "Parse error occurred."
         sys.exit(0)
     self.stmt = parsed[0]
     self.checkQuery()
     self.parseQuery()
     self.checkParsedQuery()
     # Check if table is present in DB
     self.improveQuery()
Пример #2
0
 def __init__(self, query):
     while query[-1] in [' ', ';']:
         query = query[:-1]
     self.query = query
     self.columns = []
     self.allColumns = False
     self.tables = []
     self.where = False
     self.twoWhere = False
     self.joinPresent = False
     self.andConditions = []
     self.orConditions = []
     self.tempConditions = []
     self.aggFn = []
     self.aggCol = []
     # self.keywords = ['select', 'from', 'where', 'distinct', 'sum', 'avg', 'max', 'min', 'and', 'or']
     # self.aggKeywords = ['max', 'min', 'sum', 'avg', 'distinct']
     self.stmt = None
     # To check consistency
     self.DB = table.initializeTables()
     try:
         parsed = sqlparse.parse(query)
     except:
         print 'Parse error occurred.'
         sys.exit(0)
     self.stmt = parsed[0]
     self.checkQuery()
     self.parseQuery()
     self.checkParsedQuery()
     # Check if table is present in DB
     self.improveQuery()
Пример #3
0
def main():
  # print sys.argv
  if len(sys.argv) == 1:
    print 'Please enter a sql statement'
    sys.exit(0)
  
  if sys.argv[1] == 'test':
    test()
    sys.exit(0)

  sql = sys.argv[1]
  # print 'Youre sql is:', sql
  procSql = query.Q(sql)
  # print procSql.__dict__
  DB = table.initializeTables()
  DB['new-table'] = table.Table(name='new-table')
  # print DB
  pr = process(procSql, DB)
Пример #4
0
def test():
  sql = ['select max(col1), distinct(col2), col3, * from foo, bar where col1 >= col2 and col2 <= 20;',
          'select * from foo, bar where foo.col1 = bar.col2;',
          'select max(col1),min(col2),col3 from foo,bar where col1>=col2 or col2<=30;',
          'select max(col1),col2 from foo',
          'select * from table1 where A = 10;',
          'select * from table1, table2 where A = 10;',
          'select * from table1 where D = 20',
          'select * from table3 where A = 10',
          'select * from table1, table2 where A = 10 and E = 20',
          'select * from table1, table2 where E = 20',
          'select * from table1, table2 where B = 10',
          'select * from table1, table2 where table1.A = 10 and table1.B = 20',
          'select * from table1 where ',
          'select * from table1 where x=2 and y=2',
          'select * from table1 where x=2 and y=',
          'select * from table1 where x=2',
          'select A from table1',
          'select A,B from table1, table2',
          'select A,table2.B from table1, table2', #Need to make select parser accept table2.B type queries
          'select max(A) from table1'
          ]
  sqlSelected = ['select * from table1',
                  'select * from table2',
                  'select * from table1,table2',
                  'select* from table1',
                  'select*from table1',
                  'select* fromtable1',
                  'select sum(A) from table1',
                  'select sum(A) from table1, table2',
                  'select sum(B) from table1, table2',
                  'select avg(A) from table1',
                  'select min(A) from table',
                  'select max(A) from table1',
                  'select min(A) from table1',
                  'select distinct(A) from table1',
                  'select distinct(B) from table1, table2',
                  'select distinct(table1.B) from table1, table2',
                  'select A,B from table1',
                  'select A, table2.B from table1, table2',
                  'select distinct(A), B from table1',
                  'select distinct(table2.B), table2.B from table1, table2',
                  'select * from table1 where A = 922',
                  'select A from table1 where B = 158',
                  'select A from table1, table2 where B = 158',
                  'select A from table1, table2 where table2.B = 158',
                  'select A from table1, table2 where B = 158 and C = 5727',
                  'select A from table1, table2 where table2.B = 158 and C = 5727',
                  'select A from table1, table2 where B = 158 or C = 5727',
                  'select A from table1, table2 where table2.B = 158 or C = 5727',
                  'select * from table1, table2 where table1.B=table2.B;',
                    #common row should be outputed only once.
                  'select table1.A, table2.B from table1,table2 where table1.B=table2.B;',
                  'select * from table1;',
                  'select * from table1; ',
                  'select * from table1;     '
                ]
  evaluation = ['select * from table1',
                'select max(A) from table1',
                'select min(B) from table1',
                'select avg(C) from table1',
                'select sum(D) from table2',
                'select A from table1',
                'select A,D from table1,table2',
                'select distinct(C) from table3',
                'select B,C from table1 where A=-900',
                'select A,B from table1 where A=775 OR B=803',
                'select A,B from table1 where A=922 OR B=158;',
                'select * from table1,table2 where table1.B=table2.B',
                'select A,D from table1,table2 where table1.B=table2.B',
                'Select A from table4;',
                'Select Z from table1;'
              ]
  tests = evaluation
  totalTests = len(tests)
  testsFail = []
  testsExit = []
  for i in tests:
    print 
    try:
      print i
      q = query.Q(i)
      # print q.__dict__
      DB = table.initializeTables()
      DB['new-table'] = table.Table(name='new-table')
      pr = process(q, DB)
    except SystemExit as e:
      testsExit.append(i)
      print 'sys.exit called'
      pass
    # except:
    #   print sys.exc_type, sys.exc_value, sys.exc_traceback.__dict__
    #   testsFail.append(i)
  print
  print 'Total Tests: ', totalTests
  print 'Tests Pass:'******'Tests Exited:', len(testsExit)
  print '\n'.join([i for i in testsExit])
  print
  print 'Tests Fail:', len(testsFail)
  print '\n'.join([i for i in testsFail])
Пример #5
0
def test():
    sql = [
        'select max(col1), distinct(col2), col3, * from foo, bar where col1 >= col2 and col2 <= 20;',
        'select * from foo, bar where foo.col1 = bar.col2;',
        'select max(col1),min(col2),col3 from foo,bar where col1>=col2 or col2<=30;',
        'select max(col1),col2 from foo',
        'select * from table1 where A = 10;',
        'select * from table1, table2 where A = 10;',
        'select * from table1 where D = 20',
        'select * from table3 where A = 10',
        'select * from table1, table2 where A = 10 and E = 20',
        'select * from table1, table2 where E = 20',
        'select * from table1, table2 where B = 10',
        'select * from table1, table2 where table1.A = 10 and table1.B = 20',
        'select * from table1 where ',
        'select * from table1 where x=2 and y=2',
        'select * from table1 where x=2 and y=',
        'select * from table1 where x=2',
        'select A from table1',
        'select A,B from table1, table2',
        'select A,table2.B from table1, table2',  #Need to make select parser accept table2.B type queries
        'select max(A) from table1'
    ]
    sqlSelected = [
        'select * from table1',
        'select * from table2',
        'select * from table1,table2',
        'select* from table1',
        'select*from table1',
        'select* fromtable1',
        'select sum(A) from table1',
        'select sum(A) from table1, table2',
        'select sum(B) from table1, table2',
        'select avg(A) from table1',
        'select min(A) from table',
        'select max(A) from table1',
        'select min(A) from table1',
        'select distinct(A) from table1',
        'select distinct(B) from table1, table2',
        'select distinct(table1.B) from table1, table2',
        'select A,B from table1',
        'select A, table2.B from table1, table2',
        'select distinct(A), B from table1',
        'select distinct(table2.B), table2.B from table1, table2',
        'select * from table1 where A = 922',
        'select A from table1 where B = 158',
        'select A from table1, table2 where B = 158',
        'select A from table1, table2 where table2.B = 158',
        'select A from table1, table2 where B = 158 and C = 5727',
        'select A from table1, table2 where table2.B = 158 and C = 5727',
        'select A from table1, table2 where B = 158 or C = 5727',
        'select A from table1, table2 where table2.B = 158 or C = 5727',
        'select * from table1, table2 where table1.B=table2.B;',
        #common row should be outputed only once.
        'select table1.A, table2.B from table1,table2 where table1.B=table2.B;',
        'select * from table1;',
        'select * from table1; ',
        'select * from table1;     '
    ]
    evaluation = [
        'select * from table1', 'select max(A) from table1',
        'select min(B) from table1', 'select avg(C) from table1',
        'select sum(D) from table2', 'select A from table1',
        'select A,D from table1,table2', 'select distinct(C) from table3',
        'select B,C from table1 where A=-900',
        'select A,B from table1 where A=775 OR B=803',
        'select A,B from table1 where A=922 OR B=158;',
        'select * from table1,table2 where table1.B=table2.B',
        'select A,D from table1,table2 where table1.B=table2.B',
        'Select A from table4;', 'Select Z from table1;'
    ]
    tests = evaluation
    totalTests = len(tests)
    testsFail = []
    testsExit = []
    for i in tests:
        print
        try:
            print i
            q = query.Q(i)
            # print q.__dict__
            DB = table.initializeTables()
            DB['new-table'] = table.Table(name='new-table')
            pr = process(q, DB)
        except SystemExit as e:
            testsExit.append(i)
            print 'sys.exit called'
            pass
        # except:
        #   print sys.exc_type, sys.exc_value, sys.exc_traceback.__dict__
        #   testsFail.append(i)
    print
    print 'Total Tests: ', totalTests
    print 'Tests Pass:'******'Tests Exited:', len(testsExit)
    print '\n'.join([i for i in testsExit])
    print
    print 'Tests Fail:', len(testsFail)
    print '\n'.join([i for i in testsFail])