def isTableExist(self, table_name: str):
import formatting.script as script
found = False
table_name = script.cleanString(table_name)
for tab in self.tb:
if (table_name == tab.table_name):
found = True
return found
def __init__(self, line: str):
import formatting.script as script
# pisahin di tanda #
temp_data = line.split(';')
# besihin data
for i in range(0, temp_data.__len__()):
temp_data[i] = script.cleanString(temp_data[i])
self.table_name = temp_data[0]
self.table_column = temp_data[1].split(',')
# besihin data
for i in range(0, self.table_column.__len__()):
self.table_column[i] = script.cleanString(self.table_column[i])
self.record_size = int(temp_data[2].split(' ')[-1])
self.record_num = int(temp_data[3].split(' ')[-1])
self.key_size = int(temp_data[-1].split(' ')[-1])
def __init__(self, file_name):
import formatting.script as script
# open file yg namanya file_name
file_temp = open(file_name, 'r')
# iterasi file temp per baris sampe akhir
for line in file_temp:
line_list = line[:-1] # hilangin character '\n'
break # berhenti pada baris pertama
# pisahin di tanda #
important_data = line_list.split(';')
for i in range(0, important_data.__len__()):
important_data[i] = script.cleanString(important_data[i])
self._tid_size = int(important_data[0].split(' ')[1])
self._blocksize = int(important_data[1].split(' ')[1])
def calcQEPnCost(self, query: str):
import formatting.script as script
raw_query = query
query = script.cleanString(query)
# ini nested function karena cuma dipake sekali doang
def isValidEnough(q_ery: str):
if ('select' not in q_ery):
return False
else:
if ('from' not in q_ery):
return False
else:
if ('join' not in q_ery):
if ('using' in q_ery):
return False
else:
if ('using' not in q_ery):
return False
return True
# print(isValidEnough(query))
if (isValidEnough(query)):
if ("join" in query.lower()):
print(">> Output:")
join_info = self.parseJoinQuery(query.lower())
print(join_info[0], join_info[1][0])
print([join_info[-1]], join_info[1][1])
part1 = self.isColumnValid(join_info[0], join_info[1][0])
part2 = self.isColumnValid([join_info[-1]], join_info[1][1])
tab1 = self.getTable(self.tb, join_info[1][0])
tab2 = self.getTable(self.tb, join_info[1][1])
bfr_left = self.calcBfr(tab1) # table pertama ini setelah from
smallb_left = self.calcb(
tab1.record_num,
bfr_left) # table pertama ini setelah from
bfr_right = self.calcBfr(tab2) # table kedua ini setelah join
smallb_right = self.calcb(
tab2.record_num, bfr_right) # table kedua ini setelah join
# ini buat masukin ke shared_pool
data_calc_qep = []
print(str(part1) + " " + str(part2))
if (part1 and part2):
for i in range(1, 3):
print("\tTabel(%d) : %s" % (i, join_info[1][i - 1]))
if (i == 1):
print("\tList Kolom : %s" % str(
self.getTable(self.tb,
join_info[1][0]).table_column))
elif (i == 2):
print("\tList Kolom : %s" % str([join_info[-1]]))
# (str(self.getTable(self.tb,join_info[1][-1]).table_column))
print("\tTabel(%d) : %s" % (i, join_info[1][i - 1]))
if (i == 1):
print("\tList Kolom : %s" % str(
self.getTable(self.tb,
join_info[1][0]).table_column))
qep_cost = []
for i in range(0, 2):
print(">> QEP #%d" % (i + 1))
print("\tPROJECTION ", end='')
temp_proj = 'PROJECTION '
for col in join_info[0]:
if (join_info[0][-1] == col):
print(col, end=' -- on the fly\n')
temp_proj = temp_proj + col + " -- on the fly"
else:
print(col, end=', ')
temp_proj = temp_proj + col + ", "
print("\t\tJOIN %s.%s = %s.%s -- BNLJ" %
(join_info[1][0], join_info[-1], join_info[1][1],
join_info[-1]))
temp_join = ("\tJOIN %s.%s = %s.%s -- BNLJ" %
(join_info[1][0], join_info[-1],
join_info[1][1], join_info[-1]))
temp_tab_name = ''
if i == 0:
print("\t%s\t\t%s" %
(join_info[1][0], join_info[1][1]))
tab_name_temp = (
"%s\t\t%s" %
(join_info[1][0], join_info[1][1]))
qep_cost.append(
self.countBNLJ(smallb_left, smallb_right))
else:
print("\t%s\t\t%s" %
(join_info[1][1], join_info[1][0]))
tab_name_temp = (
"%s\t\t%s" %
(join_info[1][1], join_info[1][0]))
qep_cost.append(
self.countBNLJ(smallb_right, smallb_left))
print("\tCost (worst case) : %d block" %
qep_cost[i]) # 99 itu placeholder
data_calc_qep.append(
[temp_proj, temp_join, tab_name_temp, qep_cost[i]])
print(">> QEP optimal : QEP#%d" %
(qep_cost.index(min(qep_cost)) + 1))
idxOptimal = qep_cost.index(min(qep_cost))
self.write_share_pool(raw_query,
imp_data=data_calc_qep[idxOptimal])
else:
print("Error query not valid")
else:
# ini bagian untuk where + selection
# print("Bagian where")
important_data = self.parseWhereQuery(query)
# ambil nilai dari object table
tab = self.getTable(self.tb, important_data.get('table_name'))
col_valid = self.isColumnValid(
important_data.get('projection'),
important_data.get('table_name'))
data_calc_qep = []
if col_valid:
print("\tTabel(%d) : %s" % (1, tab.table_name))
print("\tList kolom : %s" % (str(tab.table_column)))
qep_cost = []
for i in range(0, 4):
print(">> QEP #%d" % (i + 1))
print("\tPROJECTION ", end='')
temp_proj = "PROJECTION "
for col in important_data.get('projection'):
if (important_data.get('projection')[-1] == col):
print(col, end=' -- on the fly\n')
temp_proj = temp_proj + col + " -- on the fly"
else:
print(col, end=', ')
temp_proj = temp_proj + col + ", "
reconstruct = ''
if important_data.get('condition') == None:
for part in important_data.get('condition'):
reconstruct = reconstruct + part + ' '
reconstruct = script.cleanString(reconstruct)
else:
reconstruct = None
smallb = self.calcb(tab.record_num, self.calcBfr(tab))
if (i == 0):
eq = 'A1 Key'
qep_cost.append(self.countA1Key(smallb))
elif i == 1:
eq = 'A1 No Key'
qep_cost.append(self.countA1NoKey(smallb))
elif i == 2:
eq = 'A2'
qep_cost.append(
self.countA2(smallb, self.db.getBlockSize(),
tab.key_size,
self.db.getTidSize()))
elif i == 3:
eq = 'A3'
qep_cost.append(
self.countA3(smallb, self.db.getBlockSize(),
tab.key_size,
self.db.getTidSize()))
print("\tSELECTION %s -- %s" % (reconstruct, eq))
print("\t%s" % important_data.get('table_name'))
print("\tCost (worst case) : %d block" %
qep_cost[i]) # 99 itu placeholder
# temporary var untuk simpen bagian selection dari query
temp_sel = "SELECTION %s -- %s" % (str(reconstruct),
eq)
# temporary var untuk pegang nilai bagian cost worstcase
temp_cost = "Cost (worst case) : %d block" % qep_cost[i]
data_calc_qep.append(
[temp_proj, temp_sel, tab.table_name, temp_cost])
print(">> QEP optimal : QEP#%d" %
(qep_cost.index(min(qep_cost)) + 1))
idxOptimal = qep_cost.index(min(qep_cost))
self.write_share_pool(raw_query,
imp_data=data_calc_qep[idxOptimal])
else:
print("Error column not valid")
else:
print("Error query not valid")
def parseJoinQuery(self, query):
# SELECT nim, nama FROM Mahasiswa JOIN Registrasi using (nim);
import formatting.script as script
query_parse = query.split('from')
# part 1 -> isinya SELECT nim, nama
# ini ambil elemen pertama dari array query_parse
# ini misahin string jadi list di 'select'
# terus ambil elemen terakhir dari string yg dipisahin
column_projection_raw = query_parse[0].split('select')[-1]
# bersihin column_projection_raw
# karena mungkin aja *
column_projection_raw = script.cleanString(column_projection_raw)
# part 1 -> indeks 0 isinya select sama colomn buat projection
# SELECT nim, nama FROM Mahasiswa JOIN Registrasi using (nim);
# Ambil bagian ini nim, nama
column_projection = []
# part 2 -> isinya Mahasiswa JOIN Registrasi using (nim);
# ini misahin string jadi list di 'join'
# table_name_raw akan berisi
# ['Mahasiswa', 'Registrasi using (nim);']
table_name_raw = query_parse[-1].split('join')
# ini ambil elemen terakhir dari array table_name_raw
# terus di pisahin pke split di bagian 'using' jadi list baru yg cuma ada di memory
# selanjutnya ambil bagian pertama dari yang list sementara diatas
table_name_raw[-1] = table_name_raw[-1].split('using')[0]
# ini berisi table name yg bersih
table_name = []
for t_name in table_name_raw:
table_name.append(script.cleanString(t_name))
# part 1.5
# ini lanjutannya klo * jaga jaga
print(type(column_projection_raw))
print(table_name)
if (column_projection_raw == '*'):
# ini ambil semua kolomnya langsung
temp_tab = self.getTable(self.tb, table_name[0])
column_projection = temp_tab.table_column
else:
if (',' not in column_projection_raw):
column_projection.append(column_projection_raw)
else:
# ini klo ada comma
for column_name in column_projection_raw.split(','):
column_projection.append(script.cleanString(column_name))
print(column_projection)
# part 3 -> ini bagian dimana isinya using
# di join dimana
# langkah langkahnya ->
# 1 -> pisahin dibagian using
# 2 -> di bersihin textnya
# 3 -> done
# ini string yg isinya bagian setelah using
joined_on = query.split('using')[-1]
joined_on = script.cleanString(joined_on)
#print(column_projection)
#print(table_name)
#print(joined_on)
return [column_projection, table_name, joined_on]
def parseWhereQuery(self, query):
import formatting.script as script
query_parse = query.split('from')
# SELECT nim, nama FROM Mahasiswa WHERE nim = 190;
# part 1 -> isinya SELECT nim, nama
# ini ambil elemen pertama dari array query_parse
# ini misahin string jadi list di 'select'
# terus ambil elemen terakhir dari string yg dipisahin
column_projection_raw = query_parse[0].split('select')[-1]
# bersihin column_projection_raw
# karena mungkin aja *
column_projection_raw = script.cleanString(column_projection_raw)
# part 1 -> indeks 0 isinya select sama colomn buat projection
# SELECT nim, nama FROM Mahasiswa JOIN Registrasi using (nim);
# Ambil bagian ini nim, nama
column_projection = []
# dilanjutin di part 1.5
# part 2 -> isinya Mahasiswa JOIN Registrasi using (nim);
# ini misahin string jadi list di 'join'
# table_name_raw akan berisi
# ['Mahasiswa', 'nim = 190;']
if ('where' in query_parse[-1]):
table_name_raw = query_parse[-1].split('where')
##### klo ada condition
###################
# condition -> nim = 190
cond = []
for isi in script.cleanString(table_name_raw[-1]).split(' '):
cond.append(script.cleanString(isi))
else:
table_name_raw = list(script.cleanString(query_parse[-1]))
###################
# condition -> nim = 190
cond = None
#print(table_name_raw)
# table_name_clean = bagian pertama indeks ke 0
table_name = script.cleanString(table_name_raw[0])
#print(table_name_raw[-1])
#print(table_name_raw[-1].split(' '))
# part 1.5
# ini lanjutannya klo * jaga jaga
if (column_projection_raw.lower() == '*'):
# ini ambil semua kolomnya langsung
column_projection = self.getTable(self.tb, table_name).table_column
else:
if (',' not in column_projection_raw):
column_projection.append(column_projection_raw)
else:
# ini klo ada comma
for column_name in column_projection_raw.split(','):
column_projection.append(script.cleanString(column_name))
#############
temp = {
'projection': column_projection,
'table_name': table_name,
'condition': cond
}
return (temp)