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()