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)
