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)