def setUp(self):
try:
os.remove('spam')
except FileNotFoundError:
pass
self.manager = IndexManager('spam', '<i')
self.manager.Node.n = 4
def _select_single_condition(table_name, condition):
metadata = load_metadata()
index_name = MinisqlFacade._has_index(condition[0], table_name)
if index_name:
print('select 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)
records = list()
if operator == '=':
try:
itr = manager.find(key_list)
it_key, value = next(itr)
if it_key[0] == key_list[0]:
records.append(
RecordManager.select(
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]
records.append(
RecordManager.select(
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]
records.append(
RecordManager.select(
table_name,
metadata.tables[table_name].fmt,
with_index=True,
record_offset=value))
else:
break
else:
pass
else:
records = MinisqlFacade.select_record_conditionally_without_index(
table_name, condition)
return records
def _delete_stupid_index(record, table_name):
metadata = load_metadata()
for index_name, index in metadata.tables[table_name].indexes.items():
key_pos = list(metadata.tables[table_name].columns.keys()).index(
index.columns[0])
attribute_name = index.columns[0]
key_list = list()
key_list.append(record[key_pos])
file_path = RecordManager.file_dir + index_name + '.index'
fmt = metadata.tables[table_name].columns[attribute_name].fmt
in_manager = IndexManager(file_path, fmt)
in_manager.delete(key_list)
def test_initial_insert(self):
manager = IndexManager('spam', '<id')
manager.insert([42, 7.6], 518)
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, [(42, 7.6)])
self.assertEqual(node.children, [518, 0])
def _has_index(attribute_name, table_name):
metadata = load_metadata()
for index_name, index in metadata.tables[table_name].indexes.items():
if attribute_name in index.columns:
file_path = RecordManager.file_dir + index_name + '.index'
fmt = metadata.tables[table_name].columns[attribute_name].fmt
manager = IndexManager(file_path, fmt)
try:
manager.iter_leaves()
return index_name
except RuntimeError:
pass
else:
pass
return None
def drop_index(index_name):
metadata = load_metadata()
for table_name, table in metadata.tables.items():
if index_name in table.indexes:
file_path = 'schema/tables/' + table_name + '/' + index_name + '.index'
fmt = ''.join(metadata.tables[table_name].columns[column].fmt
for column in table.indexes[index_name].columns)
manager = IndexManager(file_path, fmt)
metadata.drop_index(table_name, index_name)
try:
for i in manager.iter_leaves():
key_list = list()
key_list.append(i[0][0])
manager.delete(key_list)
except RuntimeError:
pass
metadata.dump()
def test_shrinking_delete(self):
manager = IndexManager('spam', '<id')
manager.insert([42, 7.6], 518)
manager.insert([233, 66.6], 7)
manager.delete([233, 66.6])
manager.delete([42, 7.6])
self.assertEqual(manager.root, 0)
self.assertEqual(manager.first_deleted_block, 1)
self.assertEqual(manager.total_blocks, 2)
def test_find_all(self):
manager = IndexManager('spam', '<id')
manager.insert([42, 7.6], 518)
manager.insert([42, 7.6], 212)
manager.insert([233, 66.6], 7)
results = manager.find([42, 7.6])
self.assertEqual(sorted(results), [212, 518])
def delete_record_all(table_name):
RecordManager.set_file_dir('schema/tables/' + table_name + '/')
metadata = load_metadata()
RecordManager.delete(table_name,
metadata.tables[table_name].fmt,
with_index=False,
conditions={})
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)
try:
for i in manager.iter_leaves():
key_list = list()
key_list.append(i[0][0])
manager.delete(key_list)
except RuntimeError:
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 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 test_multiple_delete(self):
manager = IndexManager('spam', '<id')
manager.insert([42, 7.6], 518)
manager.insert([42, 7.6], 212)
manager.insert([233, 66.6], 7)
deleted_num = manager.delete([42, 7.6])
self.assertEqual(deleted_num, 2)
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 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 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 test_insert_duplicate(self):
manager = IndexManager('spam', '<id')
manager.insert([42, 7.6], 518)
with self.assertRaises(ValueError):
manager.insert([42, 7.6], 233)
def test_init(self):
manager = IndexManager('spam', '<id')
self.assertEqual(manager.root, 0)
self.assertEqual(manager.first_deleted_block, 0)
self.assertEqual(manager.total_blocks, 1)
def test_find_from_empty(self):
manager = IndexManager('spam', '<id')
with self.assertRaises(RuntimeError):
manager.find([23, 3])
def test_find_not_exists(self):
manager = IndexManager('spam', '<id')
manager.insert([42, 7.6], 518)
manager.insert([233, 66.6], 7)
result = manager.find([233, 7.6])
self.assertEqual(next(result), ((233, 66.6), 7))
def test_delete_from_empty(self):
manager = IndexManager('spam', '<id')
with self.assertRaises(ValueError):
manager.delete([2, 3.3])
def test_unsuccessful_delete(self):
manager = IndexManager('spam', '<id')
manager.insert([42, 7.6], 518)
manager.insert([233, 66.6], 7)
with self.assertRaises(ValueError):
manager.delete([2, 3.3])
class TestAdjustingDeletion(unittest.TestCase):
def setUp(self):
try:
os.remove('spam')
except FileNotFoundError:
pass
self.manager = IndexManager('spam', '<i')
self.manager.Node.n = 4
def tearDown(self):
try:
os.remove('spam')
except FileNotFoundError:
pass
del self.manager
def test_delete_transfer(self):
self.manager.Node.n = 4
self.manager.insert(2, 32)
self.manager.insert(24, 67)
self.manager.insert(7, 87)
self.manager.insert(15, 45)
self.manager.insert(11, 43)
self.manager.delete(24)
self.assertEqual(self.manager.root, 3)
Node = self.manager.Node
root_block = BufferManager().get_file_block(self.manager.index_file_path, self.manager.root)
root_node = Node.frombytes(root_block.read())
self.assertEqual(root_node.keys, [(11,)])
left_block = BufferManager().get_file_block(self.manager.index_file_path, 1)
left_node = Node.frombytes(left_block.read())
self.assertEqual(left_node.keys, _convert_to_tuple_list([2, 7]))
self.assertEqual(left_node.children, [32, 87, 2])
right_block = BufferManager().get_file_block(self.manager.index_file_path, 2)
right_node = Node.frombytes(right_block.read())
self.assertEqual(right_node.keys, _convert_to_tuple_list([11, 15]))
self.assertEqual(right_node.children, [43, 45, 0])
def test_delete_fuse(self):
self.manager.Node.n = 4
self.manager.insert(2, 32)
self.manager.insert(24, 67)
self.manager.insert(7, 87)
self.manager.insert(15, 45)
self.manager.insert(11, 43)
self.manager.delete(24)
self.manager.delete(11)
self.assertEqual(self.manager.root, 1)
Node = self.manager.Node
root_block = BufferManager().get_file_block(self.manager.index_file_path, self.manager.root)
root_node = Node.frombytes(root_block.read())
self.assertEqual(root_node.is_leaf, True)
self.assertEqual(root_node.keys, _convert_to_tuple_list([2, 7, 15]))
self.assertEqual(root_node.children, [32, 87, 45, 0])