def test_main(self):
thesaurusTest = [{
'input': "Compte le nombre d'étudiant",
'database': './database/ecole.sql',
'language': './lang/french.csv',
'thesaurus': 'thesaurus/th_french.dat',
'output': "SELECT COUNT(*) FROM eleve;"
}, {
'input': "Compte le nombre des dénomination des étudiant",
'database': './database/ecole.sql',
'language': './lang/french.csv',
'thesaurus': 'thesaurus/th_french.dat',
'output': "SELECT COUNT(eleve.nom) FROM eleve;"
}, {
'input':
"Quelles sont les ancienneté et les dénomination des élève ?",
'database': './database/ecole.sql',
'language': './lang/french.csv',
'thesaurus': 'thesaurus/th_french.dat',
'output': "SELECT eleve.age, eleve.nom FROM eleve;"
}, {
'input':
"Quelles sont les ancienneté et les dénomination des étudiant ?",
'database': './database/ecole.sql',
'language': './lang/french.csv',
'thesaurus': 'thesaurus/th_french.dat',
'output': "SELECT eleve.age, eleve.nom FROM eleve;"
}, {
'input': "Quelles sont les salle des classe",
'database': './database/ecole.sql',
'language': './lang/french.csv',
'thesaurus': 'thesaurus/th_french.dat',
'output': "SELECT classe.salle FROM classe;"
}, {
'input': "Quelles sont les salle des cours",
'database': './database/ecole.sql',
'language': './lang/french.csv',
'thesaurus': 'thesaurus/th_french.dat',
'output': "SELECT classe.salle FROM classe;"
}, {
'input': "Quelles sont les pièce des cours",
'database': './database/ecole.sql',
'language': './lang/french.csv',
'thesaurus': 'thesaurus/th_french.dat',
'output': "SELECT classe.salle FROM classe;"
}]
for test in thesaurusTest:
capturedOutput = StringIO.StringIO()
sys.stdout = capturedOutput
ln2sql_main([
'-d', test['database'], '-l', test['language'], '-i',
test['input'], '-t', test['thesaurus']
])
sys.stdout = sys.__stdout__
self.assertEqual(self._cleanOutput(capturedOutput.getvalue()),
test['output'])
def test_main(self):
for test in thesaurusTest:
capturedOutput = io.StringIO()
sys.stdout = capturedOutput
ln2sql_main([
'-d', test['database'], '-l', test['language'], '-i',
test['input'], '-t', test['thesaurus']
])
sys.stdout = sys.__stdout__
self.assertEqual(self._cleanOutput(capturedOutput.getvalue()),
test['output'])
def test_main(self):
tests = [
{
'input': 'city',
'sqlDump': './database/city.sql',
'output': 'SELECT * FROM city;'
},
{
'input': 'cityName from city',
'sqlDump': './database/city.sql',
'output': 'SELECT city.cityName FROM city;'
},
{
'input': 'all id from city',
'sqlDump': './database/city.sql',
'output': 'SELECT city.id FROM city;'
},
{
'input': 'all name of emp',
'sqlDump': './database/city.sql',
'output': 'SELECT emp.name FROM emp;'
},
{
'input': 'all name and score of all emp',
'sqlDump': './database/city.sql',
'output': 'SELECT emp.name, emp.score FROM emp;'
},
]
for test in tests:
capturedOutput = StringIO.StringIO()
sys.stdout = capturedOutput
ln2sql_main([
'-d', test['sqlDump'], '-l', './lang/english.csv', '-i',
test['input']
])
sys.stdout = sys.__stdout__
self.assertEqual(self._cleanOutput(capturedOutput.getvalue()),
test['output'])
def test_main(self):
correctTest = [{
'input': 'List me the info of city table',
'database': './database/city.sql',
'language': './lang/english.csv',
'output': 'SELECT * FROM city;'
}, {
'input': 'What is the number of the city in this database?',
'database': './database/city.sql',
'language': './lang/english.csv',
'output': 'SELECT COUNT(*) FROM city;'
}, {
'input': 'Tell me all id from city',
'database': './database/city.sql',
'language': './lang/english.csv',
'output': 'SELECT city.id FROM city;'
}, {
'input': 'What are the name of emp',
'database': './database/city.sql',
'language': './lang/english.csv',
'output': 'SELECT emp.name FROM emp;'
}, {
'input': 'List all name and score of all emp',
'database': './database/city.sql',
'language': './lang/english.csv',
'output': 'SELECT emp.name, emp.score FROM emp;'
}, {
'input':
'Count how many city there are where the name is Matthew ?',
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.name = 'matthew';"
}, {
'input':
'What is the emp with the name is rupinder ?',
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT * FROM emp WHERE emp.name = 'rupinder';"
}, {
'input':
'What is the cityName and the score of the emp whose name is matthew',
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT city.cityName, emp.score FROM emp INNER JOIN city ON emp.cityId = city.id WHERE emp.name = 'matthew';"
}, {
'input':
'Count how many city there are where the score is greater than 2 ?',
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.score > '2';"
}, {
'input':
"Show data for city where cityName is 'Pune Agra'",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT * FROM city WHERE city.cityName = 'pune agra';"
}, {
'input':
"Show data for city where cityName is not Pune and id like 1",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT * FROM city WHERE city.cityName != 'pune' AND city.id LIKE '%1%';"
}, {
'input':
"What is the cityName and the score of the emp whose name is rupinder",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT city.cityName, emp.score FROM emp INNER JOIN city ON emp.cityId = city.id WHERE emp.name = 'rupinder';"
}, {
'input':
"count how many city there are ordered by name",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name ASC;"
}, {
'input':
"count how many city there are ordered by name in descending order and ordered by score in ascending order",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"count how many city there are ordered by name in descending order and ordered by score?",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"count distinctly how many city there are ordered by name in descending and ordered by score?",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"Count distinctly how many different name of city there are ordered by name in descending and ordered by score?",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(DISTINCT emp.name) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"What are the distinct name of city with a score equals to 9?",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT DISTINCT emp.name FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.score = '9';"
}]
for test in correctTest:
capturedOutput = io.StringIO()
sys.stdout = capturedOutput
ln2sql_main([
'-d', test['database'], '-l', test['language'], '-i',
test['input']
])
sys.stdout = sys.__stdout__
self.assertEqual(self._cleanOutput(capturedOutput.getvalue()),
test['output'])
def test_main(self):
correctTest = [{
'input': 'List me the info of city table',
'database': './database/city.sql',
'language': './lang/english.csv',
'output': 'SELECT * FROM city;'
}, {
'input': 'What is the number of the city in this database?',
'database': './database/city.sql',
'language': './lang/english.csv',
'output': 'SELECT COUNT(*) FROM city;'
}, {
'input': 'Tell me all id from city',
'database': './database/city.sql',
'language': './lang/english.csv',
'output': 'SELECT city.id FROM city;'
}, {
'input': 'What are the name of emp',
'database': './database/city.sql',
'language': './lang/english.csv',
'output': 'SELECT emp.name FROM emp;'
}, {
'input': 'List all name and score of all emp',
'database': './database/city.sql',
'language': './lang/english.csv',
'output': 'SELECT emp.name, emp.score FROM emp;'
}, {
'input':
'Count how many city there are where the name is Matthew ?',
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.name = 'matthew';"
}, {
'input':
'What is the emp with the name is rupinder ?',
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT * FROM emp WHERE emp.name = 'rupinder';"
}, {
'input':
'What is the cityName and the score of the emp whose name is matthew',
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT city.cityName, emp.score FROM emp INNER JOIN city ON emp.cityId = city.id WHERE emp.name = 'matthew';"
}, {
'input':
'Count how many city there are where the score is greater than 2 ?',
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.score > '2';"
}, {
'input':
"Show data for city where cityName is 'Pune Agra'",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT * FROM city WHERE city.cityName = 'pune agra';"
}, {
'input':
"Show data for city where cityName is not Pune and id like 1",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT * FROM city WHERE city.cityName != 'pune' AND city.id LIKE '%1%';"
}, {
'input':
"What is the cityName and the score of the emp whose name is rupinder",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT city.cityName, emp.score FROM emp INNER JOIN city ON emp.cityId = city.id WHERE emp.name = 'rupinder';"
}, {
'input':
"count how many city there are ordered by name",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name ASC;"
}, {
'input':
"count how many city there are ordered by name in descending order and ordered by score in ascending order",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"count how many city there are ordered by name in descending order and ordered by score?",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input': "Combien y a t'il de client ?",
'database': './database/hotel.sql',
'language': './lang/french.csv',
'output': "SELECT COUNT(*) FROM client;"
}, {
'input':
"Combien y a t'il de client dont le nom est Jean ?",
'database':
'./database/hotel.sql',
'language':
'./lang/french.csv',
'output':
"SELECT COUNT(*) FROM client WHERE client.nom = 'jean';"
}, {
'input':
"Quel est l'age du client dont le nom est Jean ?",
'database':
'./database/hotel.sql',
'language':
'./lang/french.csv',
'output':
"SELECT client.age FROM client WHERE client.nom = 'jean';"
}, {
'input':
"Quel est l'adresse du client dont le nom est Jean et dont l'age est supérieur à 14 ?",
'database':
'./database/hotel.sql',
'language':
'./lang/french.csv',
'output':
"SELECT client.adresse FROM client WHERE client.nom = 'jean' AND client.age > '14';"
}, {
'input':
"Quel est l'adresse et le numéro de téléphone du client dont le nom est Marc et dont l'age est supérieur à 14 groupé par adresse ?",
'database':
'./database/hotel.sql',
'language':
'./lang/french.csv',
'output':
"SELECT client.adresse, client.telephone FROM client WHERE client.nom = 'marc' AND client.age > '14' GROUP BY client.adresse;"
}, {
'input': "Quel est la moyenne d'age des eleve ?",
'database': './database/ecole.sql',
'language': './lang/french.csv',
'output': "SELECT AVG(eleve.age) FROM eleve;"
}, {
'input': "Quels sont distinctivement les age des eleve ?",
'database': './database/ecole.sql',
'language': './lang/french.csv',
'output': "SELECT DISTINCT eleve.age FROM eleve;"
}, {
'input': "compte distinctivement les eleve ?",
'database': './database/ecole.sql',
'language': './lang/french.csv',
'output': "SELECT COUNT(*) FROM eleve;"
}, {
'input': "Compte distinctivement les age des eleve ?",
'database': './database/ecole.sql',
'language': './lang/french.csv',
'output': "SELECT COUNT(DISTINCT eleve.age) FROM eleve;"
}, {
'input':
"count distinctly how many city there are ordered by name in descending and ordered by score?",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"Count distinctly how many different name of city there are ordered by name in descending and ordered by score?",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT COUNT(DISTINCT emp.name) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"What are the distinct name of city with a score equals to 9?",
'database':
'./database/city.sql',
'language':
'./lang/english.csv',
'output':
"SELECT DISTINCT emp.name FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.score = '9';"
}]
for test in correctTest:
capturedOutput = StringIO.StringIO()
sys.stdout = capturedOutput
ln2sql_main([
'-d', test['database'], '-l', test['language'], '-i',
test['input']
])
sys.stdout = sys.__stdout__
self.assertEqual(self._cleanOutput(capturedOutput.getvalue()),
test['output'])
def runTests(self):
allTests = [
{
'input': 'List me the info of city table',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT * FROM city;'
},
{
'input': 'What is the number of the city in this database?',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT COUNT(*) FROM city;'
},
{
'input': 'Count how many city there are where the name is Matthew ?',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.name = 'matthew';"
},
{
'input': 'Count how many city there are where the score is greater than 2 ?',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.score > '2';"
},
{
'input': 'Tell me all id from city',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT city.id FROM city;'
},
{
'input': 'What are the name of emp',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT emp.name FROM emp;'
},
{
'input': 'List all name and score of all emp',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT emp.name, emp.score FROM emp;'
},
{
'input': 'What is the emp with the name is rupinder ?',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT * FROM emp WHERE emp.name = 'rupinder';"
},
{
'input': "Show data for city where cityName is 'Pune Agra'",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT * FROM city WHERE city.cityName = 'pune agra';"
},
{
'input': "Show data for city where cityName is not Pune and id like 1",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT * FROM city WHERE city.cityName != 'pune' AND city.id LIKE '%1%';"
},
{
'input': 'What is the cityName and the score of the emp whose name is matthew',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT city.cityName, emp.score FROM emp INNER JOIN city ON emp.cityId = city.id WHERE emp.name = 'matthew';"
},
{
'input': "What is the cityName and the score of the emp whose name is rupinder",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT city.cityName, emp.score FROM emp INNER JOIN city ON emp.cityId = city.id WHERE emp.name = 'rupinder';"
},
{
'input': "count distinctly how many city there are ordered by name in descending and ordered by score?",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
},
{
'input': "Count distinctly how many different name of city there are ordered by name in descending and ordered by score?",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT COUNT(DISTINCT emp.name) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
},
{
'input': "What are the distinct name of city with a score equals to 9?",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT DISTINCT emp.name FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.score = '9';"
},
{
'input': "count how many city there are ordered by name",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name ASC;"
},
{
'input': "count how many city there are ordered by name in descending order and ordered by score in ascending order",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
},
{
'input': "count how many city there are ordered by name in descending order and ordered by score?",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
},
{
'input': "What is the name and cityId of the emp whose name is Mark and whose cityId is greater than 14 grouped by cityId?",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT emp.name, emp.cityId FROM emp WHERE emp.name = 'mark' AND emp.cityId > '14' GROUP BY emp.cityId;"
},
{
'input': "Show data for city where cityName is not vibgyor and id like 1",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT * FROM city WHERE city.cityName != 'vibgyor' AND city.id LIKE '%1%';"
},
{
'input': "Show data for city where cityName is not 'vibgyor or' and id like 1",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT * FROM city WHERE city.cityName != 'vibgyor or' AND city.id LIKE '%1%';"
},
{
'input': "how many name there are in emp in which the cityId is more than 3 ?",
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT COUNT(emp.name) FROM emp WHERE emp.cityId > '3';"
}
]
for test in allTests:
capturedOutput = StringIO.StringIO()
sys.stdout = capturedOutput
ln2sql_main(['-d', test['database'], '-l', test['language'], '-i', test['input']])
sys.stdout = sys.__stdout__
outputRetrieved = self._cleanOutput(capturedOutput.getvalue())
if outputRetrieved == test['output'] :
status = "PASS"
self.success += 1
self._printTest(status,test['input'],test['output'],outputRetrieved)
else :
status = "FAIL"
self.failure += 1
self._printTest(status,test['input'],test['output'],outputRetrieved)