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 POST(self):
response = {
'status': '',
'error': ''
}
try:
configData = web.input()
# TODO: Validate user input
gs.generate_db_schema(pg.connect(configData))
# TODO: store in session variable not global variable
web.schema = schema.Schema()
web.config = configData
response['status'] = 'ok'
except Exception, e:
response['status'] = 'error'
response['error'] = str(e)
# 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")
# TODO add try - catch to catch errors
if __name__ == "__main__":
config_data = cp.parse_file('dbbackend/db.cfg')
con = pg.connect(config_data)
generate_db_schema(con)
try:
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']
gs.generate_db_schema(pg.connect(configData))
# TODO: store in session variable not global variable
web.schema = schema.Schema()
web.config = configData
response['status'] = 'ok'
except Exception, e:
response['status'] = 'error'
response['error'] = str(e)
web.header('Content-Type','application/json; charset=utf-8', unique=True)
return json.dumps(response)
def isTest():
if 'WEBPY_ENV' is os.environ:
return os.environ['WEBPY_ENV'] == 'test'
else:
return False
# TODO: Get global vars, create shared global instance of SQLSchema class.
# http://stackoverflow.com/questions/7512681/how-to-keep-a-variable-value-across-requests-in-web-py
web.app = web.application(urls, globals())
if (not isTest()) and __name__ == "__main__":
gs.generate_db_schema(pg.connect(cp.parse_file('dbbackend/db.cfg')))
web.app.run()
web.schema = schema.Schema()
web.config = cp.parse_file('dbbackend/db.cfg')
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_establish_connection_invalid_data(self):
config_data = cp.parse_file('dbbackend/db.cfg')
con = pg.connect(config_data)
assert(True)
def test_establish_connection_no_data(self): con = pg.connect(None) assert(True)