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
