def run(self):
self.queries = []
for table_of_from in self.tables_of_from:
links = []
query = Query()
query.set_from(From(table_of_from))
join_object = Join()
for column in self.columns_of_select:
if column not in self.database_dico[table_of_from]:
foreign_table = self.get_tables_of_column(column)[0]
join_object.add_table(foreign_table)
link = self.get_link(table_of_from, foreign_table)
if not link:
self.queries = ParsingException(
"There is at least column `" + column +
"` that is unreachable from table `" +
table_of_from.upper() + "`!")
return
else:
links.extend(link)
for column in self.columns_of_where:
if column not in self.database_dico[table_of_from]:
foreign_table = self.get_tables_of_column(column)[0]
join_object.add_table(foreign_table)
link = self.get_link(table_of_from, foreign_table)
if not link:
self.queries = ParsingException(
"There is at least column `" + column +
"` that is unreachable from table `" +
table_of_from.upper() + "`!")
return
else:
links.extend(link)
join_object.set_links(self.unique_ordered(links))
query.set_join(join_object)
self.queries.append(query)
def parse_sentence(self, sentence):
number_of_table = 0
number_of_select_column = 0
number_of_where_column = 0
last_table_position = 0
columns_of_select = []
columns_of_where = []
input_for_finding_value = sentence.decode('utf-8').rstrip(
string.punctuation.replace('"', '').replace("'", ""))
columns_of_values_of_where = []
filter_list = [",", "!"]
for filter_element in filter_list:
input_for_finding_value = input_for_finding_value.replace(
filter_element, " ")
input_word_list = input_for_finding_value.split()
number_of_where_column_temp = 0
number_of_table_temp = 0
last_table_position_temp = 0
start_phrase = ''
med_phrase = ''
end_phrase = ''
for i in range(0, len(input_word_list)):
if input_word_list[i] in self.database_dico:
if number_of_table_temp == 0:
start_phrase = input_word_list[:i]
number_of_table_temp += 1
last_table_position_temp = i
for table in self.database_dico:
if input_word_list[i] in self.database_dico[table]:
if number_of_where_column_temp == 0:
med_phrase = input_word_list[
len(start_phrase):last_table_position_temp + 1]
number_of_where_column_temp += 1
break
else:
if (number_of_table_temp != 0) and (
number_of_where_column_temp
== 0) and (i == (len(input_word_list) - 1)):
med_phrase = input_word_list[len(start_phrase):]
end_phrase = input_word_list[len(start_phrase) + len(med_phrase):]
irext = ' '.join(end_phrase)
''' @todo set this part of the algorithm (detection of values of where) in the part of the phrases where parsing '''
if irext:
irext = irext.lower()
# .lower() is necessary to make our own irext case insensetive for proper value extraction and it will not even
# reflect any problems for Case Sensetive fields , it is just for improving logic for our extracting assigners.
# eg -> "show data for city where cityName is LIke Pune" A query like this would also work even if lang you dont write all the permutations of 'like'.
filter_list = [",", "!"]
for filter_element in filter_list:
irext = irext.replace(filter_element, " ")
assignment_list = self.equal_keywords + self.like_keywords + self.greater_keywords + self.less_keywords + self.negation_keywords
# As these words can also be part of assigners
assignment_list.append(':')
assignment_list.append('=')
# custom operators added as they can be possibilities
assignment_list = _transformationSortAlgo(
assignment_list
) # Algorithmic logic for best substitution for extraction of values with the help of assigners.
maverickjoy_general_assigner = "*res*@3#>>*"
maverickjoy_like_assigner = "*like*@3#>>*"
for idx, assigner in enumerate(assignment_list):
if assigner in self.like_keywords:
assigner = str(" " + assigner + " ")
irext = irext.replace(
assigner, str(" " + maverickjoy_like_assigner + " "))
else:
assigner = str(" " + assigner + " ")
# Reason for adding " " these is according to the LOGIC implemented assigner operators help us extract the value,
# hence they should be independent entities not part of some other big entity else logic will fail.
# for eg -> "show data for city where cityName where I like to risk my life is Pune" will end up extacting ,
# 'k' and '1' both. I know its a lame sentence but something like this could be a problem.
irext = irext.replace(
assigner,
str(" " + maverickjoy_general_assigner + " "))
# replace all spaces from values to <_> for proper value assignment in SQL
# eg. (where name is 'abc def') -> (where name is abc<_>def)
for i in re.findall("(['\"].*?['\"])", irext):
irext = irext.replace(
i,
i.replace(' ', '<_>').replace("'", '').replace('"', ''))
irext_list = irext.split()
for idx, x in enumerate(irext_list):
index = idx + 1
if x == maverickjoy_like_assigner:
if index < len(irext_list) and irext_list[
index] != maverickjoy_like_assigner and irext_list[
index] != maverickjoy_general_assigner:
# replace back <_> to spaces from the values assigned
columns_of_values_of_where.append(
str("'%" +
str(irext_list[index]).replace('<_>', ' ') +
"%'"))
if x == maverickjoy_general_assigner:
if index < len(irext_list) and irext_list[
index] != maverickjoy_like_assigner and irext_list[
index] != maverickjoy_general_assigner:
# replace back <_> to spaces from the values assigned
columns_of_values_of_where.append(
str("'" +
str(irext_list[index]).replace('<_>', ' ') +
"'"))
# print "columns_of_values_of_where : ",columns_of_values_of_where
tables_of_from = []
select_phrase = ''
from_phrase = ''
where_phrase = ''
words = re.findall(r"[\w]+",
self.remove_accents(sentence.decode('utf-8')))
for i in range(0, len(words)):
if words[i] in self.database_dico:
if number_of_table == 0:
select_phrase = words[:i]
tables_of_from.append(words[i])
number_of_table += 1
last_table_position = i
for table in self.database_dico:
if words[i] in self.database_dico[table]:
if number_of_table == 0:
columns_of_select.append(words[i])
number_of_select_column += 1
else:
if number_of_where_column == 0:
from_phrase = words[len(select_phrase
):last_table_position + 1]
columns_of_where.append(words[i])
number_of_where_column += 1
break
else:
if (number_of_table !=
0) and (number_of_where_column
== 0) and (i == (len(words) - 1)):
from_phrase = words[len(select_phrase):]
where_phrase = words[len(select_phrase) + len(from_phrase):]
if (number_of_select_column + number_of_table +
number_of_where_column) == 0:
raise ParsingException("No keyword found in sentence!")
if len(tables_of_from) > 0:
from_phrases = []
previous_index = 0
for i in range(0, len(from_phrase)):
if from_phrase[i] in tables_of_from:
from_phrases.append(from_phrase[previous_index:i + 1])
previous_index = i + 1
last_junction_word_index = -1
for i in range(0, len(from_phrases)):
number_of_junction_words = 0
number_of_disjunction_words = 0
for word in from_phrases[i]:
if word in self.junction_keywords:
number_of_junction_words += 1
if word in self.disjunction_keywords:
number_of_disjunction_words += 1
if (number_of_junction_words +
number_of_disjunction_words) > 0:
last_junction_word_index = i
if last_junction_word_index == -1:
from_phrase = sum(from_phrases[:1], [])
where_phrase = sum(from_phrases[1:], []) + where_phrase
else:
from_phrase = sum(from_phrases[:last_junction_word_index + 1],
[])
where_phrase = sum(from_phrases[last_junction_word_index + 1:],
[]) + where_phrase
real_tables_of_from = []
for word in from_phrase:
if word in tables_of_from:
real_tables_of_from.append(word)
tables_of_from = real_tables_of_from
if len(tables_of_from) == 0:
raise ParsingException("No table name found in sentence!")
group_by_phrase = []
order_by_phrase = []
new_where_phrase = []
previous_index = 0
previous_phrase_type = 0
yet_where = 0
for i in range(0, len(where_phrase)):
if where_phrase[i] in self.order_by_keywords:
if yet_where > 0:
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:i])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:i])
else:
new_where_phrase.append(where_phrase[previous_index:i])
previous_index = i
previous_phrase_type = 1
yet_where += 1
if where_phrase[i] in self.group_by_keywords:
if yet_where > 0:
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:i])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:i])
else:
new_where_phrase.append(where_phrase[previous_index:i])
previous_index = i
previous_phrase_type = 2
yet_where += 1
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:])
else:
new_where_phrase.append(where_phrase)
select_parser = SelectParser(columns_of_select, tables_of_from,
select_phrase, self.count_keywords,
self.sum_keywords, self.average_keywords,
self.max_keywords, self.min_keywords,
self.database_dico)
from_parser = FromParser(tables_of_from, columns_of_select,
columns_of_where, self.database_object)
where_parser = WhereParser(
new_where_phrase, tables_of_from, columns_of_values_of_where,
self.count_keywords, self.sum_keywords, self.average_keywords,
self.max_keywords, self.min_keywords, self.greater_keywords,
self.less_keywords, self.between_keywords, self.negation_keywords,
self.junction_keywords, self.disjunction_keywords,
self.database_dico, self.like_keywords)
group_by_parser = GroupByParser(group_by_phrase, tables_of_from,
self.database_dico)
order_by_parser = OrderByParser(order_by_phrase, tables_of_from,
self.asc_keywords, self.desc_keywords,
self.database_dico)
select_parser.start()
from_parser.start()
where_parser.start()
group_by_parser.start()
order_by_parser.start()
queries = from_parser.join()
if queries is None:
raise ParsingException(
"There is at least one unattainable column from the table of FROM!"
)
select_objects = select_parser.join()
where_objects = where_parser.join()
group_by_objects = group_by_parser.join()
order_by_objects = order_by_parser.join()
for i in range(0, len(queries)):
query = queries[i]
query.set_select(select_objects[i])
query.set_where(where_objects[i])
query.set_group_by(group_by_objects[i])
query.set_order_by(order_by_objects[i])
return queries, self.database_dico
class FromParser(Thread):
def __init__(self, tables_of_from, columns_of_select, columns_of_where,
database_object):
Thread.__init__(self)
self.queries = []
self.tables_of_from = tables_of_from
self.columns_of_select = columns_of_select
self.columns_of_where = columns_of_where
self.database_object = database_object
self.database_dico = self.database_object.get_tables_into_dictionnary()
def get_tables_of_column(self, column):
tmp_table = []
for table in self.database_dico:
if column in self.database_dico[table]:
tmp_table.append(table)
return tmp_table
def intersect(self, a, b):
return list(set(a) & set(b))
def difference(self, a, b):
differences = []
for _list in a:
if _list not in b:
differences.append(_list)
return differences
def is_direct_join_is_possible(self, table_src, table_trg):
fk_column_of_src_table = self.database_object.get_foreign_keys_of_table(
table_src)
fk_column_of_trg_table = self.database_object.get_foreign_keys_of_table(
table_trg)
for column in fk_column_of_src_table:
if column.is_foreign()['foreign_table'] == table_trg:
return [(table_src, column.get_name()),
(table_trg, column.is_foreign()['foreign_column'])]
for column in fk_column_of_trg_table:
if column.is_foreign()['foreign_table'] == table_src:
return [(table_src, column.is_foreign()['foreign_column']),
(table_trg, column.get_name())]
# pk_table_src = self.database_object.get_primary_key_names_of_table(table_src)
# pk_table_trg = self.database_object.get_primary_key_names_of_table(table_trg)
# match_pk_table_src_with_table_trg = self.intersect(pk_table_src, self.database_dico[table_trg])
# match_pk_table_trg_with_table_src = self.intersect(pk_table_trg, self.database_dico[table_src])
# if len(match_pk_table_src_with_table_trg) >= 1:
# return [(table_trg, match_pk_table_src_with_table_trg[0]), (table_src, match_pk_table_src_with_table_trg[0])]
# elif len(match_pk_table_trg_with_table_src) >= 1:
# return [(table_trg, match_pk_table_trg_with_table_src[0]),
# (table_src, match_pk_table_trg_with_table_src[0])]
def get_all_direct_linked_tables_of_a_table(self, table_src):
links = []
for table_trg in self.database_dico:
if table_trg != table_src:
link = self.is_direct_join_is_possible(table_src, table_trg)
if link is not None:
links.append(link)
return links
def is_join(self, historic, table_src, table_trg):
historic = historic
links = self.get_all_direct_linked_tables_of_a_table(table_src)
differences = []
for join in links:
if join[0][0] not in historic:
differences.append(join)
links = differences
for join in links:
if join[1][0] == table_trg:
return [0, join]
path = []
historic.append(table_src)
for join in links:
result = [1, self.is_join(historic, join[1][0], table_trg)]
if result[1] != []:
if result[0] == 0:
path.append(result[1])
path.append(join)
else:
path = result[1]
path.append(join)
return path
def get_link(self, table_src, table_trg):
path = self.is_join([], table_src, table_trg)
if len(path) > 0:
path.pop(0)
path.reverse()
return path
def unique(self, _list):
return [list(x) for x in set(tuple(x) for x in _list)]
def unique_ordered(self, _list):
frequency = []
for element in _list:
if element not in frequency:
frequency.append(element)
return frequency
def run(self):
self.queries = []
for table_of_from in self.tables_of_from:
links = []
query = Query()
query.set_from(From(table_of_from))
join_object = Join()
for column in self.columns_of_select:
if column not in self.database_dico[table_of_from]:
foreign_table = self.get_tables_of_column(column)[0]
join_object.add_table(foreign_table)
link = self.get_link(table_of_from, foreign_table)
if not link:
self.queries = ParsingException(
"There is at least column `" + column +
"` that is unreachable from table `" +
table_of_from.upper() + "`!")
return
else:
links.extend(link)
for column in self.columns_of_where:
if column not in self.database_dico[table_of_from]:
foreign_table = self.get_tables_of_column(column)[0]
join_object.add_table(foreign_table)
link = self.get_link(table_of_from, foreign_table)
if not link:
self.queries = ParsingException(
"There is at least column `" + column +
"` that is unreachable from table `" +
table_of_from.upper() + "`!")
return
else:
links.extend(link)
join_object.set_links(self.unique_ordered(links))
query.set_join(join_object)
self.queries.append(query)
def join(self):
Thread.join(self)
return self.queries
def parse_sentence(self, sentence):
# print(sentence)
# sentence = stopwordsFilter.filter(sentence)
# print(sentence)
number_of_table = 0
number_of_select_column = 0
number_of_where_column = 0
last_table_position = 0
columns_of_select = []
columns_of_where = []
# ---------------------------------------------------------------------
input_for_finding_value = sentence
# input_for_finding_value = ""
# for word in sentence:
# input_for_finding_value += word
columns_of_values_of_where = []
###1
#removing punctuations
filter_list = [",", "!"]
# print(type(sentence))
for filter_element in filter_list:
input_for_finding_value = input_for_finding_value.replace(
filter_element, " ")
input_word_list = input_for_finding_value.split()
# print "asd -> ",input_word_list
print(input_word_list)
# === clause extractor
number_of_where_column_temp = 0
number_of_table_temp = 0
last_table_position_temp = 0
start_phrase = ''
med_phrase = ''
end_phrase = ''
print("database dico", self.database_dico)
# finding the value term in database
# finding the table name in the sentence and
# defining start phraase as the phrase befor that and end phrase as phrase after it
###2
input_sentence_copy = input_word_list
for i in range(0, len(input_word_list)):
if input_word_list[i] in self.database_dico:
if number_of_table_temp == 0:
start_phrase = input_word_list[:i]
number_of_table_temp += 1
last_table_position_temp = i
# Now, i got the table name , now for that table name, i am searching column names in query
###3
for table in self.database_dico:
if input_word_list[i] in self.database_dico[table]:
if number_of_where_column_temp == 0:
med_phrase = input_word_list[len(
start_phrase):last_table_position_temp+1]
number_of_where_column_temp += 1
break
else:
if (number_of_table_temp != 0) and (number_of_where_column_temp == 0) and (i == (len(input_word_list)-1)):
med_phrase = input_word_list[len(start_phrase):]
end_phrase = input_word_list[len(start_phrase) + len(med_phrase):]
print(start_phrase)
print(med_phrase)
print(end_phrase)
irext = ' '.join(end_phrase)
# print 'irext :',irext
print(irext)
# ===
# condition_str_where='where'
# exist_check_where=sentence.find(condition_str_where)
# condition_str_for='for'
# exist_check_for=sentence.find(condition_str_for)
# if exist_check_where != -1 or exist_check_for != -1:
if irext:
# print "entered"
# if exist_check_where != -1 :
# irext=sentence.split(condition_str_where)[1]
# else :
# irext=sentence.split(condition_str_for)[1]
mirext = irext.lower()
# print "-----"
# print "sentence : ",sentence
# print "irext : ",irext
# print "-----"
filter_list = [",", "!"]
for filter_element in filter_list:
irext = irext.replace(filter_element, " ")
print(irext)
###4
assignment_list = [" named "," as "," equals to ", " equal to ",
"=", " is ", ":", " equals ", " equal ", " than "]
maverickjoy_assigner_convention = "res@3#>>"
for assigners in assignment_list:
irext = irext.replace(assigners, " res@3#>> ")
# print "ire : ",irext
# print 'irext:',irext
print(irext)
# replace all spaces from values to <_> for proper value assignment in SQL in case where value is separated by space
# eg. (where name is 'abc def') -> (where name is abc<_>def)
for i in re.findall("('.*?')", irext):
irext = irext.replace(i, i.replace(
' ', '<_>').replace("'", ''))
print(irext)
irext_list = irext.split()
# print "ire : ",irext_list
print(irext_list)
# index list values contans the index at which column value is present
index_list_values = [
(i+1) for i, x in enumerate(irext_list) if x == maverickjoy_assigner_convention]
# print "ilv : ",index_list_values
print(index_list_values)
###5
for index in index_list_values:
if index < len(irext_list):
# replace back <_> to spaces from the values assigned
columns_of_values_of_where.append(
str("'"+str(irext_list[index]).replace('<_>', ' ')+"'"))
print(" = > ",columns_of_values_of_where)
# ---------------------------------------------------------------------
tables_of_from = []
select_phrase = ''
from_phrase = ''
where_phrase = ''
# table_candidate = []
# for i in range(0, len(input_word_list)):
# names = (self.thesaurus_object.get_synonyms_of_a_word(input_word_list[i]))
# print(names, input_word_list[i])
# if names:
# for name in names:
# if str(name) in self.database_dico:
# table_candidate.append((name))
# print("table Cndidates are ", table_candidate)
words = re.findall(r"[\w]+", self.remove_accents(sentence)) # separating words again and doing same thing as above i.e. getting end phraase( where phrase)
print(words)
for i in range(0, len(words)):
names = (self.thesaurus_object.get_synonyms_of_a_word(words[i]))
if names:
print("table candidates", names, words[i])
for name in names:
if str(name) in self.database_dico:
if number_of_table == 0:
select_phrase = words[:i]
tables_of_from.append(name)
number_of_table += 1
last_table_position = i
# if number_of_table == 0:
elif words[i] in self.database_dico:
if number_of_table == 0:
select_phrase = words[:i]
tables_of_from.append(words[i])
number_of_table += 1
last_table_position = i
# if words[i] in self.database_dico:
# if number_of_table == 0:
# select_phrase = words[:i]
# tables_of_from.append(words[i])
# number_of_table += 1
# last_table_position = i
for table in self.database_dico:
# similar_column = self.thesaurus_object.get_synonyms_of_a_word(words[i])
if (words[i] in self.database_dico[table]):
if number_of_table == 0:
columns_of_select.append(words[i])
number_of_select_column += 1
else:
if number_of_where_column == 0:
from_phrase = words[len(
select_phrase):last_table_position+1]
columns_of_where.append(words[i])
number_of_where_column += 1
break
# elif(similar_column):
# for s_c in similar_column:
# if words[i] == s_c:
# if number_of_table == 0:
# columns_of_select.append(s_c)
# number_of_select_column += 1
# else:
# if number_of_where_column == 0:
# from_phrase = words[len(
# select_phrase):last_table_position + 1]
# columns_of_where.append(s_c)
# number_of_where_column += 1
else:
if (number_of_table != 0) and (number_of_where_column == 0) and (i == (len(words)-1)):
from_phrase = words[len(select_phrase):]
where_phrase = words[len(select_phrase) + len(from_phrase):]
###6 got where phrase and tables of from
print("where phrase",where_phrase)
print(tables_of_from)
# print "where => ", where_phrase
# print(where_phrase)
if (number_of_select_column + number_of_table + number_of_where_column) == 0:
raise ParsingException("Not able to map to a query!")
print("from phase before", from_phrase)
if len(tables_of_from) > 0:
from_phrases = []
previous_index = 0
for i in range(0, len(from_phrase)):
if from_phrase[i] in tables_of_from:
from_phrases.append(from_phrase[previous_index:i+1])
previous_index = i+1
print("from mphrasses'", from_phrases)
last_junction_word_index = -1
for i in range(0, len(from_phrases)):
number_of_junction_words = 0
number_of_disjunction_words = 0
for word in from_phrases[i]:
if word in self.junction_keywords:
number_of_junction_words += 1
if word in self.disjunction_keywords:
number_of_disjunction_words += 1
if (number_of_junction_words + number_of_disjunction_words) > 0:
last_junction_word_index = i
if last_junction_word_index == -1:
from_phrase = sum(from_phrases[:1], [])
where_phrase = sum(from_phrases[1:], []) + where_phrase
else:
from_phrase = sum(
from_phrases[:last_junction_word_index+1], [])
where_phrase = sum(
from_phrases[last_junction_word_index+1:], []) + where_phrase
print("from phrase",from_phrase)
print("select phrase", select_phrase)
real_tables_of_from = []
for word in from_phrase:
if word in tables_of_from:
real_tables_of_from.append(word)
tables_of_from = real_tables_of_from
print("its here")
print(tables_of_from, real_tables_of_from)
if len(tables_of_from) == 0:
table_candidate = []
for i in range(0, len(input_word_list)):
names = (self.thesaurus_object.get_synonyms_of_a_word(input_word_list[i]))
print(names, input_word_list[i])
if names:
for name in names:
if str(name) in self.database_dico:
table_candidate.append((name))
print("table Cndidates are ", table_candidate)
if len(table_candidate) > 0:
# tables_of_from = 1
tables_of_from.append(table_candidate[0])
if len(table_candidate) == 0:
raise ParsingException("No table name found in sentence!")
group_by_phrase = []
order_by_phrase = []
new_where_phrase = []
previous_index = 0
previous_phrase_type = 0
yet_where = 0
for i in range(0, len(where_phrase)):
if where_phrase[i] in self.order_by_keywords:
if yet_where > 0:
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:i])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:i])
else:
new_where_phrase.append(where_phrase[previous_index:i])
previous_index = i
previous_phrase_type = 1
yet_where += 1
if where_phrase[i] in self.group_by_keywords:
if yet_where > 0:
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:i])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:i])
else:
new_where_phrase.append(where_phrase[previous_index:i])
previous_index = i
previous_phrase_type = 2
yet_where += 1
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:])
else:
new_where_phrase.append(where_phrase)
print("this is break")
print(columns_of_select, tables_of_from, select_phrase)
select_parser = SelectParser(columns_of_select, tables_of_from, select_phrase, self.count_keywords,
self.sum_keywords, self.average_keywords, self.max_keywords, self.min_keywords, self.database_dico)
from_parser = FromParser(
tables_of_from, columns_of_select, columns_of_where, self.database_object)
where_parser = WhereParser(new_where_phrase, self.thesaurus_object, tables_of_from, self.count_keywords,
self.sum_keywords, self.average_keywords, self.max_keywords, self.min_keywords,
self.greater_keywords,
self.less_keywords, self.between_keywords, self.negation_keywords,
self.junction_keywords, self.disjunction_keywords, self.database_dico,
columns_of_values_of_where)
# where_parser = WhereParser(new_where_phrase, tables_of_from, self.count_keywords,
# self.sum_keywords, self.average_keywords, self.max_keywords, self.min_keywords,
# self.greater_keywords,
# self.less_keywords, self.between_keywords, self.negation_keywords, self.junction_keywords,
# self.disjunction_keywords, self.database_dico, columns_of_values_of_where)
group_by_parser = GroupByParser(
group_by_phrase, tables_of_from, self.database_dico)
order_by_parser = OrderByParser(
order_by_phrase, tables_of_from, self.database_dico)
select_parser.start()
from_parser.start()
where_parser.start()
group_by_parser.start()
order_by_parser.start()
# Joining all the objects
queries = from_parser.join()
if queries is None:
raise ParsingException(
"There is at least one unattainable column from the table of FROM!")
select_objects = select_parser.join()
where_objects = where_parser.join()
group_by_objects = group_by_parser.join()
order_by_objects = order_by_parser.join()
print(select_objects)
print(where_objects)
print(group_by_objects)
print(order_by_objects)
print(queries)
for i in range(0, len(queries)):
print(queries[i])
# print("lelngthdfs", len(queries))
for i in range(0, len(queries)):
query = queries[i]
query.set_select(select_objects[i])
query.set_where(where_objects[i])
query.set_group_by(group_by_objects[i])
query.set_order_by(order_by_objects[i])
print(queries)
return queries
def parse_sentence(self, sentence):
number_of_table = 0
number_of_select_column = 0
number_of_where_column = 0
last_table_position = 0
columns_of_select = []
columns_of_where = []
# ---------------------------------------------------------------------
input_for_finding_value=sentence
columns_of_values_of_where=[]
filter_list=[",","!"]
for filter_element in filter_list:
input_for_finding_value=input_for_finding_value.replace(filter_element," ")
input_word_list=input_for_finding_value.split()
# print "asd -> ",input_word_list
#=== clause extractor
number_of_where_column_temp = 0
number_of_table_temp = 0
last_table_position_temp = 0
start_phrase = ''
med_phrase = ''
end_phrase = ''
for i in range(0, len(input_word_list)):
if input_word_list[i] in self.database_dico:
if number_of_table_temp == 0:
start_phrase = input_word_list[:i]
number_of_table_temp+=1
last_table_position_temp = i
for table in self.database_dico:
if input_word_list[i] in self.database_dico[table]:
if number_of_where_column_temp == 0:
med_phrase = input_word_list[len(start_phrase):last_table_position_temp+1]
number_of_where_column_temp+=1
break
else:
if (number_of_table_temp != 0) and (number_of_where_column_temp == 0) and (i == (len(input_word_list)-1)):
med_phrase = input_word_list[len(start_phrase):]
end_phrase = input_word_list[len(start_phrase) + len(med_phrase):]
irext = ' '.join(end_phrase)
# print 'irext :',irext
#===
# condition_str_where='where'
# exist_check_where=sentence.find(condition_str_where)
# condition_str_for='for'
# exist_check_for=sentence.find(condition_str_for)
# if exist_check_where != -1 or exist_check_for != -1:
if irext :
# print "entered"
# if exist_check_where != -1 :
# irext=sentence.split(condition_str_where)[1]
# else :
# irext=sentence.split(condition_str_for)[1]
mirext=irext.lower()
# print "-----"
# print "sentence : ",sentence
# print "irext : ",irext
# print "-----"
filter_list=[",","!"]
for filter_element in filter_list:
irext=irext.replace(filter_element," ")
assignment_list=[" equals to "," equal to ","="," is ",":"," equals "," equal "," than "]
maverickjoy_assigner_convention ="res@3#>>"
for assigners in assignment_list :
irext=irext.replace(assigners," res@3#>> ")
# print "ire : ",irext
# print 'irext:',irext
# replace all spaces from values to <_> for proper value assignment in SQL
# eg. (where name is 'abc def') -> (where name is abc<_>def)
for i in re.findall("('.*?')",irext):
irext = irext.replace(i,i.replace(' ','<_>').replace("'",''))
irext_list = irext.split()
# print "ire : ",irext_list
index_list_values=[(i+1) for i,x in enumerate(irext_list) if x == maverickjoy_assigner_convention]
# print "ilv : ",index_list_values
for index in index_list_values:
if index < len(irext_list):
# replace back <_> to spaces from the values assigned
columns_of_values_of_where.append(str("'"+str(irext_list[index]).replace('<_>',' ')+"'"))
# print " = > ",columns_of_values_of_where
# ---------------------------------------------------------------------
tables_of_from = []
select_phrase = ''
from_phrase = ''
where_phrase = ''
words = re.findall(r"[\w]+", self.remove_accents(sentence))
for i in range(0, len(words)):
if words[i] in self.database_dico:
if number_of_table == 0:
select_phrase = words[:i]
tables_of_from.append(words[i])
number_of_table+=1
last_table_position = i
for table in self.database_dico:
if words[i] in self.database_dico[table]:
if number_of_table == 0:
columns_of_select.append(words[i])
number_of_select_column+=1
else:
if number_of_where_column == 0:
from_phrase = words[len(select_phrase):last_table_position+1]
columns_of_where.append(words[i])
number_of_where_column+=1
break
else:
if (number_of_table != 0) and (number_of_where_column == 0) and (i == (len(words)-1)):
from_phrase = words[len(select_phrase):]
where_phrase = words[len(select_phrase) + len(from_phrase):]
# print "where => ", where_phrase
if (number_of_select_column + number_of_table + number_of_where_column) == 0:
raise ParsingException("No keyword found in sentence!")
if len(tables_of_from) > 0:
from_phrases = []
previous_index = 0
for i in range(0,len(from_phrase)):
if from_phrase[i] in tables_of_from:
from_phrases.append(from_phrase[previous_index:i+1])
previous_index = i+1
last_junction_word_index = -1
for i in range(0, len(from_phrases)):
number_of_junction_words = 0
number_of_disjunction_words = 0
for word in from_phrases[i]:
if word in self.junction_keywords:
number_of_junction_words += 1
if word in self.disjunction_keywords:
number_of_disjunction_words += 1
if (number_of_junction_words + number_of_disjunction_words) > 0:
last_junction_word_index = i
if last_junction_word_index == -1:
from_phrase = sum(from_phrases[:1], [])
where_phrase = sum(from_phrases[1:], []) + where_phrase
else:
from_phrase = sum(from_phrases[:last_junction_word_index+1], [])
where_phrase = sum(from_phrases[last_junction_word_index+1:], []) + where_phrase
real_tables_of_from = []
for word in from_phrase:
if word in tables_of_from:
real_tables_of_from.append(word)
tables_of_from = real_tables_of_from
if len(tables_of_from) == 0:
raise ParsingException("No table name found in sentence!")
group_by_phrase = []
order_by_phrase = []
new_where_phrase = []
previous_index = 0
previous_phrase_type = 0
yet_where = 0
for i in range(0, len(where_phrase)):
if where_phrase[i] in self.order_by_keywords:
if yet_where > 0:
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:i])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:i])
else:
new_where_phrase.append(where_phrase[previous_index:i])
previous_index = i
previous_phrase_type = 1
yet_where += 1
if where_phrase[i] in self.group_by_keywords:
if yet_where > 0:
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:i])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:i])
else:
new_where_phrase.append(where_phrase[previous_index:i])
previous_index = i
previous_phrase_type = 2
yet_where += 1
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:])
else:
new_where_phrase.append(where_phrase)
select_parser = SelectParser(columns_of_select, tables_of_from, select_phrase, self.count_keywords, self.sum_keywords, self.average_keywords, self.max_keywords, self.min_keywords, self.database_dico)
from_parser = FromParser(tables_of_from, columns_of_select, columns_of_where, self.database_object)
where_parser = WhereParser(new_where_phrase, tables_of_from, self.count_keywords, self.sum_keywords, self.average_keywords, self.max_keywords, self.min_keywords, self.greater_keywords, self.less_keywords, self.between_keywords, self.negation_keywords, self.junction_keywords, self.disjunction_keywords, self.database_dico, columns_of_values_of_where)
group_by_parser = GroupByParser(group_by_phrase, tables_of_from, self.database_dico)
order_by_parser = OrderByParser(order_by_phrase, tables_of_from, self.database_dico)
select_parser.start()
from_parser.start()
where_parser.start()
group_by_parser.start()
order_by_parser.start()
queries = from_parser.join()
if queries is None:
raise ParsingException("There is at least one unattainable column from the table of FROM!")
select_objects = select_parser.join()
where_objects = where_parser.join()
group_by_objects = group_by_parser.join()
order_by_objects = order_by_parser.join()
for i in range(0, len(queries)):
query = queries[i]
query.set_select(select_objects[i])
query.set_where(where_objects[i])
query.set_group_by(group_by_objects[i])
query.set_order_by(order_by_objects[i])
return queries
def parse_sentence(self, sentence):
number_of_table = 0
number_of_select_column = 0
number_of_where_column = 0
last_table_position = 0
columns_of_select = []
columns_of_where = []
tables_of_from = []
select_phrase = ''
from_phrase = ''
where_phrase = ''
words = re.findall(r"[\w]+", self.remove_accents(sentence))
for i in range(0, len(words)):
if words[i] == "to" or words[i] == "than":
global VALUE
if words[i + 1].isdigit():
VALUE = words[i + 1]
else:
VALUE = '"' + words[i + 1] + '"'
if words[i] in self.database_dico:
if number_of_table == 0:
select_phrase = words[:i]
tables_of_from.append(words[i])
number_of_table += 1
last_table_position = i
for table in self.database_dico:
if words[i] in self.database_dico[table]:
if number_of_table == 0:
columns_of_select.append(words[i])
number_of_select_column += 1
else:
if number_of_where_column == 0:
from_phrase = words[len(select_phrase
):last_table_position + 1]
columns_of_where.append(words[i])
number_of_where_column += 1
break
else:
if (number_of_table !=
0) and (number_of_where_column
== 0) and (i == (len(words) - 1)):
from_phrase = words[len(select_phrase):]
where_phrase = words[len(select_phrase) + len(from_phrase):]
if len(tables_of_from) == 0:
tables_of_from = ['chatbot']
#raise ParsingException("No table name found in sentence!")
if len(tables_of_from) > 0:
from_phrases = []
previous_index = 0
for i in range(0, len(from_phrase)):
if from_phrase[i] in tables_of_from:
from_phrases.append(from_phrase[previous_index:i + 1])
previous_index = i + 1
last_junction_word_index = -1
for i in range(0, len(from_phrases)):
number_of_junction_words = 0
number_of_disjunction_words = 0
for word in from_phrases[i]:
if word in self.junction_keywords:
number_of_junction_words += 1
if word in self.disjunction_keywords:
number_of_disjunction_words += 1
if (number_of_junction_words +
number_of_disjunction_words) > 0:
last_junction_word_index = i
if last_junction_word_index == -1:
from_phrase = sum(from_phrases[:1], [])
where_phrase = sum(from_phrases[1:], []) + where_phrase
else:
from_phrase = sum(from_phrases[:last_junction_word_index + 1],
[])
where_phrase = sum(from_phrases[last_junction_word_index + 1:],
[]) + where_phrase
real_tables_of_from = []
for word in from_phrase:
if word in tables_of_from:
real_tables_of_from.append(word)
tables_of_from = real_tables_of_from
group_by_phrase = []
order_by_phrase = []
new_where_phrase = []
previous_index = 0
previous_phrase_type = 0
yet_where = 0
for i in range(0, len(where_phrase)):
if where_phrase[i] in self.order_by_keywords:
if yet_where > 0:
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:i])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:i])
else:
new_where_phrase.append(where_phrase[previous_index:i])
previous_index = i
previous_phrase_type = 1
yet_where += 1
if where_phrase[i] in self.group_by_keywords:
if yet_where > 0:
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:i])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:i])
else:
new_where_phrase.append(where_phrase[previous_index:i])
previous_index = i
previous_phrase_type = 2
yet_where += 1
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:])
else:
new_where_phrase.append(where_phrase)
select_parser = SelectParser(columns_of_select, tables_of_from,
select_phrase, self.count_keywords,
self.sum_keywords, self.average_keywords,
self.max_keywords, self.min_keywords,
self.database_dico)
tables_of_from = ['chatbot']
from_parser = FromParser(tables_of_from, columns_of_select,
columns_of_where, self.database_object)
where_parser = WhereParser(
new_where_phrase, tables_of_from, self.count_keywords,
self.sum_keywords, self.average_keywords, self.max_keywords,
self.min_keywords, self.greater_keywords, self.less_keywords,
self.between_keywords, self.negation_keywords,
self.junction_keywords, self.disjunction_keywords,
self.database_dico)
group_by_parser = GroupByParser(group_by_phrase, tables_of_from,
self.database_dico)
order_by_parser = OrderByParser(order_by_phrase, tables_of_from,
self.database_dico)
select_parser.start()
from_parser.start()
where_parser.start()
group_by_parser.start()
order_by_parser.start()
queries = from_parser.join()
if queries is None:
raise ParsingException(
"There is at least one unattainable column from the table of FROM!"
)
select_objects = select_parser.join()
where_objects = where_parser.join()
group_by_objects = group_by_parser.join()
order_by_objects = order_by_parser.join()
for i in range(0, len(queries)):
query = queries[i]
query.set_select(select_objects[i])
query.set_where(where_objects[i])
query.set_group_by(group_by_objects[i])
query.set_order_by(order_by_objects[i])
return queries
def parse_sentence(self, sentence, stopwordsFilter=None):
sys.tracebacklimit = 0 # Remove traceback from Exception
number_of_table = 0
number_of_select_column = 0
number_of_where_column = 0
last_table_position = 0
columns_of_select = []
columns_of_where = []
if stopwordsFilter is not None:
sentence = stopwordsFilter.filter(sentence)
input_for_finding_value = sentence.rstrip(
string.punctuation.replace('"', '').replace("'", ""))
columns_of_values_of_where = []
filter_list = [",", "!"]
for filter_element in filter_list:
input_for_finding_value = input_for_finding_value.replace(
filter_element, " ")
input_word_list = input_for_finding_value.split()
number_of_where_column_temp = 0
number_of_table_temp = 0
last_table_position_temp = 0
start_phrase = ''
med_phrase = ''
end_phrase = ''
''' @todo merge this part of the algorithm (detection of values of where) in the rest of the parsing algorithm (about line 725) '''
for i in range(0, len(input_word_list)):
for table_name in self.database_dico:
if (input_word_list[i] == table_name) or (
input_word_list[i] in self.database_object.
get_table_by_name(table_name).get_equivalences()):
if number_of_table_temp == 0:
start_phrase = input_word_list[:i]
number_of_table_temp += 1
last_table_position_temp = i
columns = self.database_object.get_table_by_name(
table_name).get_columns()
for column in columns:
if (input_word_list[i] == column.get_name()) or (
input_word_list[i] in column.get_equivalences()):
if number_of_where_column_temp == 0:
med_phrase = input_word_list[
len(start_phrase):last_table_position_temp + 1]
number_of_where_column_temp += 1
break
else:
if (number_of_table_temp != 0) and (
number_of_where_column_temp
== 0) and (i == (len(input_word_list) - 1)):
med_phrase = input_word_list[len(start_phrase):]
else:
continue
break
end_phrase = input_word_list[len(start_phrase) + len(med_phrase):]
irext = ' '.join(end_phrase)
''' @todo set this part of the algorithm (detection of values of where) in the WhereParser thread '''
if irext:
irext = self.remove_accents(irext.lower())
filter_list = [",", "!"]
for filter_element in filter_list:
irext = irext.replace(filter_element, " ")
assignment_list = self.equal_keywords + self.like_keywords + self.greater_keywords + self.less_keywords + self.negation_keywords
# As these words can also be part of assigners
assignment_list.append(':')
assignment_list.append('=')
# custom operators added as they can be possibilities
assignment_list = self.transformationSortAlgo(
assignment_list
) # Algorithmic logic for best substitution for extraction of values with the help of assigners.
maverickjoy_general_assigner = "*res*@3#>>*"
maverickjoy_like_assigner = "*like*@3#>>*"
for idx, assigner in enumerate(assignment_list):
if assigner in self.like_keywords:
assigner = str(" " + assigner + " ")
irext = irext.replace(
assigner, str(" " + maverickjoy_like_assigner + " "))
else:
assigner = str(" " + assigner + " ")
# Reason for adding " " these is according to the LOGIC implemented assigner operators help us extract the value,
# hence they should be independent entities not part of some other big entity else logic will fail.
# for eg -> "show data for city where cityName where I like to risk my life is Pune" will end up extacting ,
# 'k' and '1' both. I know its a lame sentence but something like this could be a problem.
irext = irext.replace(
assigner,
str(" " + maverickjoy_general_assigner + " "))
# replace all spaces from values to <_> for proper value assignment in SQL
# eg. (where name is 'abc def') -> (where name is abc<_>def)
for i in re.findall("(['\"].*?['\"])", irext):
irext = irext.replace(
i,
i.replace(' ', '<_>').replace("'", '').replace('"', ''))
irext_list = irext.split()
for idx, x in enumerate(irext_list):
index = idx + 1
if x == maverickjoy_like_assigner:
if index < len(irext_list) and irext_list[
index] != maverickjoy_like_assigner and irext_list[
index] != maverickjoy_general_assigner:
# replace back <_> to spaces from the values assigned
columns_of_values_of_where.append(
str("'%" +
str(irext_list[index]).replace('<_>', ' ') +
"%'"))
if x == maverickjoy_general_assigner:
if index < len(irext_list) and irext_list[
index] != maverickjoy_like_assigner and irext_list[
index] != maverickjoy_general_assigner:
# replace back <_> to spaces from the values assigned
columns_of_values_of_where.append(
str("'" +
str(irext_list[index]).replace('<_>', ' ') +
"'"))
''' ----------------------------------------------------------------------------------------------------------- '''
tables_of_from = []
select_phrase = ''
from_phrase = ''
where_phrase = ''
words = re.findall(r"[\w]+", self.remove_accents(sentence))
for i in range(0, len(words)):
for table_name in self.database_dico:
if (words[i] == table_name) or (
words[i] in self.database_object.get_table_by_name(
table_name).get_equivalences()):
if number_of_table == 0:
select_phrase = words[:i]
tables_of_from.append(table_name)
number_of_table += 1
last_table_position = i
columns = self.database_object.get_table_by_name(
table_name).get_columns()
for column in columns:
if (words[i] == column.get_name()) or (
words[i] in column.get_equivalences()):
if number_of_table == 0:
columns_of_select.append(column.get_name())
number_of_select_column += 1
else:
if number_of_where_column == 0:
from_phrase = words[len(select_phrase
):last_table_position +
1]
columns_of_where.append(column.get_name())
number_of_where_column += 1
break
else:
if (number_of_table !=
0) and (number_of_where_column
== 0) and (i == (len(words) - 1)):
from_phrase = words[len(select_phrase):]
where_phrase = words[len(select_phrase) + len(from_phrase):]
if (number_of_select_column + number_of_table +
number_of_where_column) == 0:
raise ParsingException("No keyword found in sentence!")
if len(tables_of_from) > 0:
from_phrases = []
previous_index = 0
for i in range(0, len(from_phrase)):
for table in tables_of_from:
if (from_phrase[i] == table) or (
from_phrase[i] in self.database_object.
get_table_by_name(table).get_equivalences()):
from_phrases.append(from_phrase[previous_index:i + 1])
previous_index = i + 1
last_junction_word_index = -1
for i in range(0, len(from_phrases)):
number_of_junction_words = 0
number_of_disjunction_words = 0
for word in from_phrases[i]:
if word in self.junction_keywords:
number_of_junction_words += 1
if word in self.disjunction_keywords:
number_of_disjunction_words += 1
if (number_of_junction_words +
number_of_disjunction_words) > 0:
last_junction_word_index = i
if last_junction_word_index == -1:
from_phrase = sum(from_phrases[:1], [])
where_phrase = sum(from_phrases[1:], []) + where_phrase
else:
from_phrase = sum(from_phrases[:last_junction_word_index + 1],
[])
where_phrase = sum(from_phrases[last_junction_word_index + 1:],
[]) + where_phrase
real_tables_of_from = []
for word in from_phrase:
for table in tables_of_from:
if (word == table) or (
word in self.database_object.get_table_by_name(
table).get_equivalences()):
real_tables_of_from.append(table)
tables_of_from = real_tables_of_from
if len(tables_of_from) == 0:
raise ParsingException("No table name found in sentence!")
group_by_phrase = []
order_by_phrase = []
new_where_phrase = []
previous_index = 0
previous_phrase_type = 0
yet_where = 0
for i in range(0, len(where_phrase)):
if where_phrase[i] in self.order_by_keywords:
if yet_where > 0:
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:i])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:i])
else:
new_where_phrase.append(where_phrase[previous_index:i])
previous_index = i
previous_phrase_type = 1
yet_where += 1
if where_phrase[i] in self.group_by_keywords:
if yet_where > 0:
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:i])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:i])
else:
new_where_phrase.append(where_phrase[previous_index:i])
previous_index = i
previous_phrase_type = 2
yet_where += 1
if previous_phrase_type == 1:
order_by_phrase.append(where_phrase[previous_index:])
elif previous_phrase_type == 2:
group_by_phrase.append(where_phrase[previous_index:])
else:
new_where_phrase.append(where_phrase)
try:
select_parser = SelectParser(
columns_of_select, tables_of_from, select_phrase,
self.count_keywords, self.sum_keywords, self.average_keywords,
self.max_keywords, self.min_keywords, self.distinct_keywords,
self.database_dico, self.database_object)
from_parser = FromParser(tables_of_from, columns_of_select,
columns_of_where, self.database_object)
where_parser = WhereParser(
new_where_phrase, tables_of_from, columns_of_values_of_where,
self.count_keywords, self.sum_keywords, self.average_keywords,
self.max_keywords, self.min_keywords, self.greater_keywords,
self.less_keywords, self.between_keywords,
self.negation_keywords, self.junction_keywords,
self.disjunction_keywords, self.like_keywords,
self.distinct_keywords, self.database_dico,
self.database_object)
group_by_parser = GroupByParser(group_by_phrase, tables_of_from,
self.database_dico,
self.database_object)
order_by_parser = OrderByParser(order_by_phrase, tables_of_from,
self.asc_keywords,
self.desc_keywords,
self.database_dico,
self.database_object)
select_parser.start()
from_parser.start()
where_parser.start()
group_by_parser.start()
order_by_parser.start()
queries = from_parser.join()
except:
raise ParsingException("Parsing error occured in thread!")
if isinstance(queries, ParsingException):
raise queries
try:
select_objects = select_parser.join()
where_objects = where_parser.join()
group_by_objects = group_by_parser.join()
order_by_objects = order_by_parser.join()
except:
raise ParsingException("Parsing error occured in thread!")
for i in range(0, len(queries)):
query = queries[i]
query.set_select(select_objects[i])
query.set_where(where_objects[i])
query.set_group_by(group_by_objects[i])
query.set_order_by(order_by_objects[i])
return queries
