def _parse_condition(self, condition, join=False):
'''
Parse the single string condition and return column/s value and operator
'''
# if both_columns (used by the join function) return the names of the names of the columns (left first)
if join:
return split_condition(condition)
# cast the value with the specified column's type and return the column name, the operator and the casted value
left, op, right = split_condition(condition)
if left not in self.column_names:
raise ValueError(f'Condition is not valid (cant find column name)')
coltype = self.column_types[self.column_names.index(left)]
return left, op, coltype(right)
def select(self, table_name, columns, condition=None, order_by=None, asc=False, \
top_k=None, save_as=None, return_object=False):
'''
Selects and outputs a table's data where condtion is met.
table_name -> table's name (needs to exist in database)
columns -> The columns that will be part of the output table (use '*' to select all the available columns)
condition -> a condition using the following format :
'column[<,<=,==,>=,>]value' or
'value[<,<=,==,>=,>]column'.
operatores supported -> (<,<=,==,>=,>)
order_by -> A column name that signals that the resulting table should be ordered based on it. Def: None (no ordering)
asc -> If True order by will return results using an ascending order. Def: False
top_k -> A number (int) that defines the number of rows that will be returned. Def: None (all rows)
save_as -> The name that will be used to save the resulting table in the database. Def: None (no save)
return_object -> If true, the result will be a table object (usefull for internal usage). Def: False (the result will be printed)
'''
self.load(self.savedir)
if self.is_locked(table_name):
return
self.lockX_table(table_name)
if condition is not None and self._has_index(table_name):
condition_left, operator, condition_right = split_condition(condition)
index_name = self.select('meta_indexes', '*', f'table_name=={table_name}', return_object=True).data
# check all the possibilities for indexing:
# ~btree can handle only the primary key
# ~hash indexing can handle only equal operator
if condition_left == self.tables[table_name].column_names[self.tables[table_name].pk_idx] \
or condition_right == self.tables[table_name].column_names[self.tables[table_name].pk_idx]:
for row in index_name:
# if exist in the row index which has the type the HashIndex and the operator is '=='
if 'hashindex' in row and operator == '==':
hi = self._load_idx(row[2])
table = self.tables[table_name]._select_where_with_hashindexing(hi, columns, condition)
break
elif 'btree' in row:
# if exists btree indexing load the object
bt = self._load_idx(row[2])
table = self.tables[table_name]._select_where_with_btree(columns, bt, condition, order_by, asc,
top_k)
break
else:
for row in index_name:
if operator == '==' and 'hashindex' in row and (condition_left in row or condition_right in row):
hi = self._load_idx(row[2])
table = self.tables[table_name]._select_where_with_hashindexing(hi, columns, condition)
break
else:
table = self.tables[table_name]._select_where(columns, condition, order_by, asc, top_k)
self.unlock_table(table_name)
if save_as is not None:
table._name = save_as
self.table_from_object(table)
else:
if return_object:
return table
else:
table.show()
def inner_join(self, left_table_name, right_table_name, condition, save_as=None, return_object=False):
'''
Join two tables that are part of the database where condition is met.
left_table_name -> left table's name (needs to exist in database)
right_table_name -> right table's name (needs to exist in database)
condition -> a condition using the following format :
'column[<,<=,==,>=,>]value' or
'value[<,<=,==,>=,>]column'.
operatores supported -> (<,<=,==,>=,>)
save_as -> The name that will be used to save the resulting table in the database. Def: None (no save)
return_object -> If true, the result will be a table object (usefull for internal usage). Def: False (the result will be printed)
'''
self.load(self.savedir)
if self.is_locked(left_table_name) or self.is_locked(right_table_name):
print(f'Table/Tables are currently locked')
return
self.lockX_table(left_table_name)
self.lockX_table(right_table_name)
if self._has_index(left_table_name) or self._has_index(right_table_name):
condition_left, operator, condition_right = split_condition(condition)
left_indexes = self.select('meta_indexes', '*', f'table_name=={left_table_name}', return_object=True).data
right_indexes = self.select('meta_indexes', '*', f'table_name=={right_table_name}', return_object=True).data
hi = None
# check for the left table
for row in left_indexes:
# if we find the index for the specific column
if 'hashindex' in row and condition_left in row:
hi = self._load_idx(row[2])
# the table that it doesnt has index we use it like 'object'
res = self.tables[right_table_name]._inner_join(self.tables[left_table_name], condition, hi)
break
# if we didnt find the index
if hi is None:
for row in right_indexes:
if 'hashindex' in row and condition_right in row:
hi = self._load_idx(row[2])
res = self.tables[left_table_name]._inner_join(self.tables[right_table_name], condition, hi)
break
# if both tables dont have index call the original _inner_join
if hi is None:
res = self.tables[left_table_name]._inner_join(self.tables[right_table_name], condition)
else:
res = self.tables[left_table_name]._inner_join(self.tables[right_table_name], condition)
self.unlock_table(left_table_name)
self.unlock_table(right_table_name)
if save_as is not None:
res._name = save_as
self.table_from_object(res)
else:
if return_object:
return res
else:
res.show()
def select(self, table_name, columns, condition=None, order_by=None, asc=False,\
top_k=None, save_as=None, return_object=False):
'''
Selects and outputs a table's data where condtion is met.
table_name -> table's name (needs to exist in database)
columns -> The columns that will be part of the output table (use '*' to select all the available columns)
condition -> a condition using the following format :
'column[<,<=,==,>=,>]value' or
'value[<,<=,==,>=,>]column'.
operatores supported -> (<,<=,==,>=,>)
order_by -> A column name that signals that the resulting table should be ordered based on it. Def: None (no ordering)
asc -> If True order by will return results using an ascending order. Def: False
top_k -> A number (int) that defines the number of rows that will be returned. Def: None (all rows)
save_as -> The name that will be used to save the resulting table in the database. Def: None (no save)
return_object -> If true, the result will be a table object (usefull for internal usage). Def: False (the result will be printed)
'''
self.load(self.savedir)
#TABLE LOCKING
if self.isX_locked(table_name):
return
self.lockS_table(table_name)
#ROW LOCKING
#
# if self.is_rowS_locked(table_name, row)
# return
# self.lockS_row(table_name, row)
if condition is not None:
condition_column = split_condition(condition)[0]
if self._has_index(table_name) and condition_column == self.tables[
table_name].column_names[self.tables[table_name].pk_idx]:
index_name = self.select('meta_indexes',
'*',
f'table_name=={table_name}',
return_object=True).index_name[0]
bt = self._load_idx(index_name)
table = self.tables[table_name]._select_where_with_btree(
columns, bt, condition, order_by, asc, top_k)
else:
table = self.tables[table_name]._select_where(
columns, condition, order_by, asc, top_k)
self.unlockS_table(table_name) #TABLE UNLOCKING
#self.unlockS_row(table_name, row) #ROW UNLOCKING
if save_as is not None:
table._name = save_as
self.table_from_object(table)
else:
if return_object:
return table
else:
table.show()
def inner_join(self, left_table_name, right_table_name, condition, save_as=None, return_object=False,
hash_type="nested_loop"):
'''
Join two tables that are part of the database where condition is met.
left_table_name -> left table's name (needs to exist in database)
right_table_name -> right table's name (needs to exist in database)
condition -> a condition using the following format :
'column[<,<=,==,>=,>]value' or
'value[<,<=,==,>=,>]column'.
operatores supported -> (<,<=,==,>=,>)
save_as -> The name that will be used to save the resulting table in the database. Def: None (no save)
return_object -> If true, the result will be a table object (usefull for internal usage). Def: False (the result will be printed)
'''
self.load(self.savedir)
if self.is_locked(left_table_name) or self.is_locked(right_table_name):
print(f'Table/Tables are currently locked')
return
self.lockX_table(left_table_name)
self.lockX_table(right_table_name)
if hash_type == "nested_loop": # nested loop -> inner_join
res = self.tables[left_table_name]._inner_join(self.tables[right_table_name], condition)
elif hash_type == "hash": # hash -> hash_join
if self._has_index(right_table_name):
condition_left, operator, condition_right = split_condition(condition)
right_indexes = self.select('meta_indexes', '*', f'table_name=={right_table_name}',
return_object=True).data
hi = None
# check for the left table
for row in right_indexes:
if 'hashindex' in row and condition_right in row:
hi = self._load_idx(row[2])
res = self.tables[left_table_name]._hash_join(self.tables[right_table_name], condition, hi)
break
# if right table don't have hash index call the _hash_join with the hi == None
if hi is None:
res = self.tables[left_table_name]._hash_join(self.tables[right_table_name], condition, hi)
else:
res = self.tables[left_table_name]._hash_join(self.tables[right_table_name], condition, hi=None)
else:
print("### ERROR WITH hash_type VALUE")
self.unlock_table(left_table_name)
self.unlock_table(right_table_name)
if save_as is not None:
res._name = save_as
self.table_from_object(res)
else:
if return_object:
return res
else:
res.show()
def select(self, columns, table_name, condition, order_by=None, top_k=True,\
desc=None, save_as=None, return_object=True):
'''
Selects and outputs a table's data where condtion is met.
Args:
table_name: string. Name of table (must be part of database).
columns: list. The columns that will be part of the output table (use '*' to select all available columns)
condition: string. A condition using the following format:
'column[<,<=,==,>=,>]value' or
'value[<,<=,==,>=,>]column'.
Operatores supported: (<,<=,==,>=,>)
order_by: string. A column name that signals that the resulting table should be ordered based on it (no order if None).
desc: boolean. If True, order_by will return results in descending order (True by default).
top_k: int. An integer that defines the number of rows that will be returned (all rows if None).
save_as: string. The name that will be used to save the resulting table into the database (no save if None).
return_object: boolean. If True, the result will be a table object (useful for internal use - the result will be printed by default).
'''
# print(table_name)
self.load_database()
if isinstance(table_name, Table):
return table_name._select_where(columns, condition, order_by, desc,
top_k)
if condition is not None:
condition_column = split_condition(condition)[0]
else:
condition_column = ''
# self.lock_table(table_name, mode='x')
if self.is_locked(table_name):
return
if self._has_index(table_name) and condition_column == self.tables[
table_name].column_names[self.tables[table_name].pk_idx]:
index_name = self.select(
'*',
'meta_indexes',
f'table_name={table_name}',
return_object=True).column_by_name('index_name')[0]
bt = self._load_idx(index_name)
table = self.tables[table_name]._select_where_with_btree(
columns, bt, condition, order_by, desc, top_k)
else:
table = self.tables[table_name]._select_where(
columns, condition, order_by, desc, top_k)
# self.unlock_table(table_name)
if save_as is not None:
table._name = save_as
self.table_from_object(table)
else:
if return_object:
return table
else:
return table.show()
def _parse_condition(self, condition, join=False):
'''
Parse the single string condition and return the value of the column and the operator.
Args:
condition: string. A condition using the following format:
'column[<,<=,==,>=,>]value' or
'value[<,<=,==,>=,>]column'.
Operatores supported: (<,<=,==,>=,>)
join: boolean. Whether to join or not (False by default).
'''
# if both_columns (used by the join function) return the names of the names of the columns (left first)
if join:
return split_condition(condition)
# cast the value with the specified column's type and return the column name, the operator and the casted value
left, op, right = split_condition(condition)
if left not in self.column_names:
raise ValueError(f'Condition is not valid (cant find column name)')
coltype = self.column_types[self.column_names.index(left)]
return left, op, coltype(right)
def _parse_condition(self, condition, join=False):
'''
Parse the single string condition and return column/s value and operator
'''
# if both_columns (used by the join function) return the names of the names of the columns (left first)
if join:
return split_condition(condition)
# cast the value with the specified column's type and return the column name, the operator and the casted value
left, op, right = split_condition(condition)
if left not in self.column_names:
raise ValueError(f'Condition is not valid (cant find column name)')
coltype = self.column_types[self.column_names.index(left)]
#############################################################################################################
# if we have between or like operator just return "right" as str
if op == ',between,' or op == ',like,':
return left, op, right
#else if we have in operator return "right" as a list, with same type as "left"
elif op == ',in,':
return left, op, ast.literal_eval(right)
#############################################################################################################
return left, op, coltype(right)
def select_bin_stack(self, table_name, columns, condition=None, order_by=None, asc=False,\
save_as=None, return_object=False):
insert_table = table_name + "_insert_queue"
self.load(self.savedir)
if self.is_locked(table_name):
return
self.lockX_table(table_name)
if condition is not None:
condition_column = split_condition(condition)[0]
list_stack = self.tables[insert_table]
table = self.tables[table_name]._select_stack_bin(
table_name, list_stack, columns, condition, order_by, asc)
self.unlock_table(table_name)
if save_as is not None:
table._name = save_as
self.table_from_object(table)
else:
if return_object:
return table
else:
table.show()
def select_bin(self, table_name, columns, condition=None, order_by=None, asc=False,\
top_k=None, save_as=None, return_object=False):
'''
Selects and outputs a table's data where condtion is met.
table_name -> table's name (needs to exist in database)
columns -> The columns that will be part of the output table (use '*' to select all the available columns)
condition -> a condition using the following format :
'column[==]value' or
'value[==]column'.
operatores supported -> == , <= , >=
order_by -> A column name that signals that the resulting table should be ordered based on it. Def: None (no ordering)
asc -> If True order by will return results using an ascending order. Def: False
top_k -> A number (int) that defines the number of rows that will be returned. Def: None (all rows)
save_as -> The name that will be used to save the resulting table in the database. Def: None (no save)
return_object -> If true, the result will be a table object (usefull for internal usage). Def: False (the result will be printed)
'''
self.load(self.savedir)
if self.is_locked(table_name):
return
self.lockX_table(table_name)
if condition is not None:
condition_column = split_condition(condition)[0]
table = self.tables[table_name]._select_where_bin(
columns, condition, order_by, asc, top_k)
self.unlock_table(table_name)
if save_as is not None:
table._name = save_as
self.table_from_object(table)
else:
if return_object:
return table
else:
table.show()
