def search_by_record(db_name, table_name, col, op, val): table_file = table_name + '.csv' block_num = getBlockNum(db_name, table_file) # max block number res = [] # result list of records # attr_list = catalogManager.getAttr(table_name) #attributes of table attr_info = attr_data(table_name) # function by catalog attr_list = attr_info['attr_name'] attr_type = attr_data(table_name)["attr_type"] index = attr_list.index(col) # print(block_num) # print(index) for num in range(block_num+1): block_list = buffer(db_name, table_file, num).strip().split('\n') for s in block_list: row = s.split(',') for j in range(len(row)): if attr_type[attr_list[j]] == "int": row[j] = int(row[j]) elif attr_type[attr_list[j]] == "float": row[j] = float(row[j]) if check_operator(op, row[index], val): res.append([str(i) for i in row]) return res
def check_unique(db_name, table_name, values): table_file = table_name + '.csv' block_num = getBlockNum(db_name, table_file) # max block number res_record = [] # result records attr_info = attr_data(table_name) attr_uni = attr_info['attr_unique'] # dict {'attr': 'unique'} attr_list = attr_info['attr_name'] attr_type = attr_info["attr_type"] for i in range(block_num + 1): block_list = buffer(db_name, table_file, i).strip().strip('\0').split('\n') for row in block_list: row = row.split(',') if len(row) == attr_info['attr_num'] and row[0] != '': for j in range(len(row)): if attr_type[attr_list[j]] == "int": row[j] = int(row[j]) elif attr_type[attr_list[j]] == "float": row[j] = float(row[j]) res_record.append(row) # get unique attr from catalog for attr in attr_list: if attr_uni[attr] == 'unique': mark = attr_list.index(attr) # index下标 for row in res_record: if len(row) == attr_info['attr_num']: if row[mark] == values[mark]: return 1 return 0
def del_by_record(db_name, table_name, col, op, val): table_file = table_name + '.csv' attr_info = attr_data(table_name) # function by catalog attr_list = attr_info['attr_name'] block_num = getBlockNum(db_name, table_file) # max block number res = [] # result list of records # attr_list = catalogManager.getAttr(table_name) #attributes of table col_index = attr_list.index(col) new_list = [] for num in range(block_num+1): block_list = buffer(db_name, table_file, num).strip().split('\n') new_list = block_list[:] # deep copy for s in block_list: row = s.split(',') if check_operator(op, row[col_index], val): # if condition is satisfied res.append(row) new_list.remove(s) # delete record block_str = '\n'.join(new_list) # convert to str in the block # print(block_str) bufferSave(db_name, table_file, num, block_str) bufferClose() # print("test del", len(res)) # print(block_list) # print(res) return res, len(res)
def search_with_index(db_name, table_name, col, op, val): # search_index_by_range(col, left_key=None, right_key=None, left_equal=False, right_equal=False) # return list of block_num and offset # search_index(self, index_name, key) # return record table_file = table_name + '.csv' index_key = attr_data(table_name)["attr_to_index"] index_name = index_key[col] if index_name is None: return manager = index_manager() # new object if op == "=": pos = manager.search_index(index_name, val) elif op == ">": pos = manager.search_index_by_range(index_name, val, None, False, False) elif op == ">=": pos = manager.search_index_by_range(index_name, val, None, True, False) elif op == "<": pos = manager.search_index_by_range(index_name, None, val, False, False) elif op == "<=": pos = manager.search_index_by_range(index_name, None, val, False, True) else: print("Unknown operator!") manager.write_disk() res = [] if pos == -1: return res if op == "=": block = buffer(db_name, table_file, int(pos[0])).split('\n') res.append(block[int(pos[1])].split(',')) # 'a,b,c,d' => ['a','b','c','d'] else: for i in pos: # i: [block_num, offset] block = buffer(db_name, table_file, int(i[0])).split('\n') res.append(block[int(i[1])].split(',')) # 'a,b,c,d' => ['a','b','c','d'] return res
def create_index(self, table_name, index_name, key_attr): table_file = table_name + '.csv' block_num = getBlockNum("db", table_file) # max block number data = catalog_manager.attr_data(table_name) index = data["attr_name"].index(key_attr) if data["attr_type"][key_attr] == "int": key_size = 10 elif data["attr_type"][key_attr] == "float": key_size = 40 elif data["attr_type"][key_attr] == "char": key_size = int(data["attr_type_length"][key_attr]) degree = int((4096 - (sys.getsizeof(index_name) - 48) + 6 + 11) / (key_size + 23)) self.create_blank_index(index_name, data["attr_type"][key_attr], degree) for num in range(block_num + 1): block_list = buffer("db", table_file, num).strip().split('\n') offset = 0 for s in block_list: if ',' not in s: break row = s.split(',') key = row[index] self.insert_index(index_name, key, [num, offset]) offset += 1 bufferClose()
def print_record(table_name, records, count): res_table = pt.PrettyTable() # attr_list = catalogManager.getAttr(table_name) #attributes of table attr_info = attr_data(table_name) # function by catalog attr_list = attr_info['attr_name'] res_table.field_names = attr_list for row in records: if len(row) == len(attr_list): res_table.add_row(row) print(res_table) print(str(count) + " rows in set.")
def select_all_table(db_name, table_name): table_file = table_name + '.csv' block_num = getBlockNum(db_name, table_file) # max block number attr_list = attr_data(table_name)["attr_name"] # function by catalog res_record = [] # result records for i in range(block_num + 1): block_list = buffer(db_name, table_file, i).strip().strip('\0').split('\n') for row in block_list: # if len(row) == len(attr_list): res_record.append(row.split(',')) # print("select all") return res_record
def insert(detail): table_name = detail['table'] con = detail['condition'] condition = con.split(',') attr = catalog_manager.attr_data(table_name) for x in range(attr["attr_num"]): if attr["attr_type"][attr["attr_name"][x]] == "int": condition[x] = int(condition[x]) elif attr["attr_type"][attr["attr_name"][x]] == "float": condition[x] = float(condition[x]) elif attr["attr_type"][attr["attr_name"][x]] == "char": if condition[x][0] == '\'': condition[x] = re.findall(r'\'(.*)\'', condition[x])[0] elif condition[x][0] == '\"': condition[x] = re.findall(r'\"(.*)\"', condition[x])[0] recordManager.insert_record("db", table_name, condition)
def del_with_index(db_name, table_name, col, op, val): table_file = table_name + '.csv' attr_info = attr_data(table_name) # function by catalog index_info = index_data(table_name) # function by catalog res = [] # deleted records pos = [] index_name = attr_info(table_name)["attr_to_index"][col] if index_name is None: return manager = index_manager() if op == "=": pos = manager.search_index(index_name, val) elif op == ">": pos = manager.search_index_by_range(index_name, val, None, False, False) elif op == ">=": pos = manager.search_index_by_range(index_name, val, None, True, False) elif op == "<": pos = manager.search_index_by_range(index_name, None, val, False, False) elif op == "<=": pos = manager.search_index_by_range(index_name, None, val, False, True) else: print("Unknown operator!") manager.write_disk() for i in pos: # i: [block_num, offset] block_num = i[0] offset = i[1] block_list = buffer(db_name, table_file, block_num).split('\n') record = block_list[offset] # 'a,b,c,d' res.append(record.split(',')) # => ['a','b','c','d'] block_list.remove(record) # append new value block_str = '\n'.join(block_list) # convert to str in the block bufferSave(db_name, table_file, block_num, block_str) # delete index # delete_index(self, index_name, key) for name in index_info['index_name']: if index_info['index_to_attr'][name] == col: # attr_name manager_1 = index_manager() manager_1.delete_index(name, val) manager_1.write_disk() return res, len(res)
def insert_record(db_name, table_name, values): # after handling exception by catalog manager attr_info = attr_data(table_name) # function by catalog index_info = index_data(table_name) # function by catalog table_file = table_name + '.csv' block_num = getBlockNum(db_name, table_file) # max block number block = buffer(db_name, table_file, block_num) # get content in th block # if block space not available, get next block if getsizeof(block) + getsizeof(values) > MAX_SIZE: block_num += 1 block = buffer(db_name, table_file, block_num) print(block_num) # check unique value conflict if check_unique(db_name, table_name, values): print('Unique value conflict!') return # insert values to end of block block_list = block.split('\n') new_val = ','.join([str(i) for i in values]) # convert list to str block_list.append(new_val) # append new value block_offset = block_list.index(new_val) # block offset of insert position block_str = '\n'.join(block_list) # convert to str in the block bufferSave(db_name, table_file, block_num, block_str) bufferClose() # update index # value: [block_num, offset] # function by index: insert_index(self, index_name, key, value) for item in index_info["index_name"]: attr = index_info["index_to_attr"][item] # dic: {"index_name": attr_name} i = attr_info['attr_name'].index(attr) # get index下标 update_key = values[i] update_value = [block_num, block_offset] manager = index_manager() manager.insert_index(item, update_key, update_value) manager.write_disk() # insert success print("1 row inserted!")
def select_record(db_name, table_name, condition): # after handling exception by catalog manager res_record = [] # result records, list of records attr_type = attr_data(table_name)["attr_type"] attr_list = attr_data(table_name)["attr_name"] # function by catalog index_list = index_data(table_name)["index_to_attr"].values() # function by catalog # index_list = [] if condition is None: # no condition, return the whole table res_record = select_all_table(db_name, table_name) elif len(condition) == 1: # one condition col, op, val = condition[0].strip().split(' ') if attr_type[col] == "int": val = int(val) elif attr_type[col] == "float": val = float(val) elif attr_type[col] == "char": val = val.strip('\'') val = val.strip('\"') if col in index_list: res_record = search_with_index(db_name, table_name, col, op, val) else: res_record = search_by_record(db_name, table_name, col, op, val) elif len(condition) == 2: # two conditions # handle the first condition col, op, val = condition[0].strip().split(' ') if attr_type[col] == "int": val = int(val) elif attr_type[col] == "float": val = float(val) elif attr_type[col] == "char": val = val.strip('\'') val = val.strip('\"') if col in index_list: res1 = search_with_index(db_name, table_name, col, op, val) else: res1 = search_by_record(db_name, table_name, col, op, val) # handle the second condition col, op, val = condition[1].strip().split(' ') if attr_type[col] == "int": val = int(val) elif attr_type[col] == "float": val = float(val) elif attr_type[col] == "char": val = val.strip('\'') val = val.strip('\"') if col in index_list: res2 = search_with_index(db_name, table_name, col, op, val) else: res2 = search_by_record(db_name, table_name, col, op, val) # get the intersection records of res1 and res2 res_record = [i for i in res1 if i in res2] else: print("At most 2 'AND' conditions!") # print(res_record) clear_list = [] for item in res_record: if len(item) == len(attr_list): item = [s.strip().strip('\0') for s in item] clear_list.append(item) # print(res_record) count = len(clear_list) # print(count) print_record(table_name, clear_list, count) return count, clear_list