def insert_record(table_name, attributes): RecordManager.set_file_dir('schema/tables/' + table_name + '/') metadata = load_metadata() position = RecordManager.insert(table_name, metadata.tables[table_name].fmt, tuple(attributes)) for index_name, index in metadata.tables[table_name].indexes.items(): file_path = RecordManager.file_dir + index_name + '.index' fmt = ''.join(metadata.tables[table_name].columns[column].fmt for column in index.columns) manager = IndexManager(file_path, fmt) key_pos = list(metadata.tables[table_name].columns.keys()).index( index.columns[0]) key_list = list() key_list.append(attributes[key_pos]) try: manager.insert( key_list, position) # index can be set on single attribute manager.dump_header() except ValueError: # duplicated key, the inserted record should be deleted RecordManager.delete(table_name, metadata.tables[table_name].fmt, with_index=True, record_offset=position) raise
def delete_record_conditionally(table_name, conditions): RecordManager.set_file_dir('schema/tables/' + table_name + '/') metadata = load_metadata() if len(conditions) == 1: MinisqlFacade._delete_single_condition(table_name, conditions[0]) elif len(conditions) == 3: if conditions[1] == 'and': records = MinisqlFacade.select_record_conditionally( table_name, conditions) for record in records: for index_name, index in metadata.tables[ table_name].indexes.items(): file_path = RecordManager.file_dir + index_name + '.index' fmt = ''.join( metadata.tables[table_name].columns[column].fmt for column in index.columns) manager = IndexManager(file_path, fmt) pos = list( metadata.tables[table_name].columns.keys()).index( index.columns[0]) key_list = list() key_list.append(record[pos]) itr = manager.find(key_list) trash, value = next(itr) manager.delete(key_list) manager.dump_header() RecordManager.delete(table_name, metadata.tables[table_name].fmt, with_index=1, record_offset=value) # each tuple has its PRIMARY KEY index # actually only support single attribute index elif conditions[1] == 'or': MinisqlFacade._delete_single_condition(table_name, conditions[0]) MinisqlFacade._delete_single_condition(table_name, conditions[2]) else: pass # link the records outside else: pass
def test_persistence(self): manager = IndexManager('spam', '<id') manager.insert([42, 7.6], 518) manager.insert([233, 66.6], 7) manager.delete([42, 7.6]) manager.dump_header() manager._manager.flush_all() del manager manager = IndexManager('spam', '<id') self.assertEqual(manager.root, 1) self.assertEqual(manager.first_deleted_block, 0) self.assertEqual(manager.total_blocks, 2) block = BufferManager().get_file_block('spam', 1) Node = manager.Node node = Node.frombytes(block.read()) self.assertEqual(node.is_leaf, True) self.assertEqual(node.keys, [(233, 66.6)]) self.assertEqual(node.children, [7, 0])
def create_index(table_name, index_name, column_name): RecordManager.set_file_dir('schema/tables/' + table_name + '/') offset = -1 metadata = load_metadata() metadata.add_index(table_name, index_name, column_name) records = RecordManager.select(table_name, metadata.tables[table_name].fmt, with_index=False, conditions={}) file_path = RecordManager.file_dir + index_name + '.index' table_target = metadata.tables[table_name] fmt = ''.join(table_target.columns[column].fmt for column in table_target.indexes[index_name].columns) manager = IndexManager(file_path, fmt) key_pos = list( metadata.tables[table_name].columns.keys()).index(column_name) for record in records: key_list = list() key_list.append(record[key_pos]) offset += 1 manager.insert(key_list, offset) metadata.dump() manager.dump_header()
def _delete_single_condition(table_name, condition): metadata = load_metadata() index_name = MinisqlFacade._has_index(condition[0], table_name) if index_name: print('delete with index on ', condition[0]) attribute_name = condition[0] operator = condition[1] key_list = list() key_list.append(condition[2]) file_path = RecordManager.file_dir + index_name + '.index' # fmt = metadata.tables[table_name].columns[attribute_name].fmt manager = IndexManager(file_path, fmt) if operator == '=': try: itr = manager.find(key_list) it_key, value = next(itr) record = RecordManager.select( table_name, metadata.tables[table_name].fmt, with_index=True, record_offset=value) if it_key[0] == key_list[0]: MinisqlFacade._delete_stupid_index(record, table_name) RecordManager.delete(table_name, metadata.tables[table_name].fmt, with_index=True, record_offset=value) except StopIteration: pass elif operator == '>': for i in manager.find(key_list): if i[0][0] > key_list[0]: value = i[1] record = RecordManager.select( table_name, metadata.tables[table_name].fmt, with_index=True, record_offset=value) MinisqlFacade._delete_stupid_index(record, table_name) RecordManager.delete(table_name, metadata.tables[table_name].fmt, with_index=True, record_offset=value) elif operator == '<': for i in manager.iter_leaves(): if i[0][0] < key_list[0]: value = i[1] record = RecordManager.select( table_name, metadata.tables[table_name].fmt, with_index=True, record_offset=value) MinisqlFacade._delete_stupid_index(record, table_name) RecordManager.delete(table_name, metadata.tables[table_name].fmt, with_index=True, record_offset=value) else: break manager.dump_header() else: MinisqlFacade.delete_record_conditionally_without_index( table_name, condition)