def translates_to(self, logic, expectedSQL): print 'Logic Recieved: ' + logic # Create a Logic Tree from the Logic logicTree = p.parse_input(logic) print "|*** LOGIC AST ***|\n" print str(logicTree) # Run dat semantic analysis bro dbSchema = schema.Schema() semanticAnalyser = sa.SemanticAnalyser(logicTree, dbSchema) semanticAnalyser.analyse() # Generate the Symbol Table from the Logic Tree symbolTable = st.SymTable() logicTree.generateSymbolTable(symbolTable) # Generate an IR from the Logic Tree (uses Symbol Table) irGenerator = irg.IRGenerator(dbSchema) logicTree.accept(irGenerator) # Pull out the SQL IR ir = irGenerator.getIR() # Translate the IR to an SQL string sqlGenerator = sg.SQLGenerator() ir.accept(sqlGenerator) translatedSQL = sqlGenerator.getSQL() # If the query result does not match the expectation, let the user know. if translatedSQL.replace('\n', ' ') != expectedSQL.replace('\n', ' '): print "WARNING: Translated SQL does not match the expected result" print "Translated SQL: {" print translatedSQL print "}" print "Expected SQL: {" print expectedSQL print "}" # Run translated and expected SQL queries and compare results. # Force decode to ASCII as unicode SQL throws a massive wobbly. configData = cp.parse_file('dbbackend/db.cfg') con = pg.connect(configData) translatedResult = pg.query(con, translatedSQL.decode('ascii', 'ignore')) expectedResult = pg.query(con, expectedSQL) con.close() result = translatedResult == expectedResult if not result: print translatedResult, " != ", expectedResult return result
def generate_db_schema(con): # Create schema entries for each table tables = pg.query(con, table_query).get('rows') if tables is None: msg = "ERROR: Tables query returned no data in generate_db_schema." print msg raise Exception(msg) dbname = pg.query(con, dbname_query)['rows'][0][0] root = etree.Element("root") dbname_node = etree.SubElement(root, "dbname") dbname_node.text = dbname for table in (table[0] for table in tables): # Create a structure with the name of the table xmltable = etree.SubElement(root, "table") xmltable.set("name", table) # Fetch all the primary keys and update the XML accordingly keys = pg.query(con, primary_key_query % table)['rows'] for key in (key[0] for key in keys): xml_primary_key = etree.SubElement(xmltable, "primaryKey") xml_primary_key.text = key # Fetch all the columns and update the XML accordingly columns = pg.query(con, columns_query % table)['rows'] for column in columns: xml_column = etree.SubElement(xmltable, "column") xml_column.set("name", column[0]) xml_column_type = etree.SubElement(xml_column, "type") xml_column_type.text = column[1] xml_column_ordinal = etree.SubElement(xml_column, "ordinal") xml_column_ordinal.text = column[2] # Write the xml to a file tree = ET.ElementTree(root) tree.write("dbbackend/schema.xml")
sqlGeneratorVisitor = sg.SQLGenerator() irGeneratorVisitor.getIR().accept(sqlGeneratorVisitor) sql = sqlGeneratorVisitor.getSQL() except Exception, e: # Handle SQL generation errors response['status'] = 'sqlgen_error' print 'SQLGENERATOR', str(e) return response # TODO - Save the config_data to a session variable and use that instead # TODO - Check for database connection issues # Parse the database config file, open a connection to the database, and # finally run the SQL query # configData = cp.parse_file('dbbackend/db.cfg') con = pg.connect(configData) queryResult = pg.query(con, sql) con.close() # Check if the query ran OK, and set the response either way if queryResult['status'] == 'ok': response['status'] = 'ok' response['sql'] = sql response['query_columns'] = queryResult['columns'] response['query_rows'] = queryResult['rows'] response['error'] = '' else: response['status'] = 'db_error' response['sql'] = sql response['error'] = queryResult['error'] return response
def test_query_validcon_invaliddata(self): config_data = cp.parse_file('dbbackend/db.cfg') con = pg.connect(config_data) pg.query(con,'ADSFS') assert(True)
def test_query_validcon_validdata(self): config_data = cp.parse_file('dbbackend/db.cfg') con = pg.connect(config_data) pg.query(con,'SELECT * FROM films') assert(True)
def test_query_nocon_validdata(self): pg.query(None,'SELECT * FROM films') assert(True)