from JellyDB.db import Database
from JellyDB.query import Query
from random import choice, randint, sample, seed
db = Database()
db.open('~/ECS165')
# Student Id and 4 grades
grades_table = db.create_table('Grades', 5, 0)
query = Query(grades_table)
records = {}
seed(3562901)
for i in range(0, 1000):
key = 92106429 + i
records[key] = [key, randint(0, 20), randint(0, 20), randint(0, 20), randint(0, 20)]
query.insert(*records[key])
keys = sorted(list(records.keys()))
print("Insert finished")
# Syntax modified for JellyDB
grades_table.create_index(1)
grades_table.create_index(2)
grades_table.create_index(3)
grades_table.create_index(4)
_records = [records[key] for key in keys]
for c in range(grades_table.num_columns):
_keys = list(set([record[c] for record in _records]))
index = {v: [record for record in _records if record[c] == v] for v in _keys}
for key in _keys:
from JellyDB.db import Database
from JellyDB.query import Query
from random import choice, randint, sample, seed
db = Database()
db.open('~/ECS165')
# Student Id and 4 grades
grades_table = db.create_table('Grades', 5, 0)
query = Query(grades_table)
records = {}
seed(3562901)
for i in range(0, 1000):
key = 92106429 + i
records[key] = [
key,
randint(0, 20),
randint(0, 20),
randint(0, 20),
randint(0, 20)
]
query.insert(*records[key])
keys = sorted(list(records.keys()))
print("Insert finished")
for key in keys:
record = query.select(key, 0, [1, 1, 1, 1, 1])[0]
error = False
for i, column in enumerate(record.columns):
if column != records[key][i]:
db = Database()
db.open('~/ECS165')
grades_table = db.create_table('Grades', 5, 0)
keys = []
records = {}
num_threads = 8
seed(8739878934)
# Generate random records
for i in range(0, 10000):
key = 92106429 + i
keys.append(key)
records[key] = [key, 0, 0, 0, 0]
q = Query(grades_table)
q.insert(*records[key])
# create TransactionWorkers
transaction_workers = []
for i in range(num_threads):
transaction_workers.append(TransactionWorker([]))
# generates 10k random transactions
# each transaction will increment the first column of a record 5 times
for i in range(1000):
k = randint(0, 2000 - 1)
transaction = Transaction()
for j in range(5):
key = keys[k * 5 + j]
q = Query(grades_table)
from JellyDB.db import Database
from JellyDB.query import Query
from time import process_time
from random import choice, randrange
# Student Id and 4 grades
db = Database()
grades_table = db.create_table('Grades', 5, 0)
query = Query(grades_table)
keys = []
# Measuring Insert Performance
insert_time_0 = process_time()
for i in range(0, 10000):
query.insert(906659671 + i, 93, 0, 0, 0)
keys.append(906659671 + i)
insert_time_1 = process_time()
print("Inserting 10k records took: \t\t\t", insert_time_1 - insert_time_0)
# Measuring update Performance
update_cols = [
[None, None, None, None, None],
[None, randrange(0, 100), None, None, None],
[None, None, randrange(0, 100), None, None],
[None, None, None, randrange(0, 100), None],
[None, None, None, None, randrange(0, 100)],
]
update_time_0 = process_time()
for i in range(0, 10000):
db = Database()
db.open('~/ECS165')
grades_table = db.create_table('Grades', 5, 0)
keys = []
records = {}
seed(3562901)
num_threads = 8
# Generate random records
for i in range(0, 10000):
key = 92106429 + i
keys.append(key)
records[key] = [key, 0, 0, 0, 0]
q = Query(grades_table)
q.insert(*records[key])
# Create transactions and assign them to workers
transactions = []
transaction_workers = []
for i in range(num_threads):
transaction_workers.append(TransactionWorker())
for i in range(10000):
key = choice(keys)
record = records[key]
c = record[1]
transaction = Transaction()
for i in range(5):
c += 1
def performance_m2(size_to_test):
db = Database()
db.open('~/ECS165')
# Student Id and 4 grades
grades_table = db.create_table('Grades', 5, 0)
query = Query(grades_table)
records = {}
seed(3562901)
for i in range(0, size_to_test):
key = 92106429 + i
records[key] = [
key,
randint(0, 20),
randint(0, 20),
randint(0, 20),
randint(0, 20)
]
query.insert(*records[key])
keys = sorted(list(records.keys()))
print("Insert finished")
time_select_1_s = process_time()
for key in keys:
record = query.select(key, 0, [1, 1, 1, 1, 1])[0]
error = False
for i, column in enumerate(record.columns):
if column != records[key][i]:
error = True
if error:
print('select error on', key, ':', record, ', correct:',
records[key])
# else:
# print('select on', key, ':', record)
time_select_1_f = process_time()
print("Select finished")
time_update_s = process_time()
for _ in range(10):
for key in keys:
updated_columns = [None, None, None, None, None]
for i in range(1, grades_table.num_columns):
value = randint(0, 20)
updated_columns[i] = value
original = records[key].copy()
records[key][i] = value
query.update(key, *updated_columns)
record = query.select(key, 0, [1, 1, 1, 1, 1])[0]
error = False
for j, column in enumerate(record.columns):
if column != records[key][j]:
error = True
if error:
print('update error on', original, 'and', updated_columns,
':', record, ', correct:', records[key])
#else:
#print('update on', original, 'and', updated_columns, ':', record)
updated_columns[i] = None
time_update_f = process_time()
print("Update finished")
for i in range(0, 100):
r = sorted(sample(range(0, len(keys)), 2))
column_sum = sum(map(lambda key: records[key][0], keys[r[0]:r[1] + 1]))
result = query.sum(keys[r[0]], keys[r[1]], 0)
if column_sum != result:
print('sum error on [', keys[r[0]], ',', keys[r[1]], ']: ', result,
', correct: ', column_sum)
# else:
# print('sum on [', keys[r[0]], ',', keys[r[1]], ']: ', column_sum)
print("Aggregate finished")
db.close()
# Student Id and 4 grades
db = Database()
db.open('~/ECS165')
grades_table = db.get_table('Grades')
query = Query(grades_table)
# repopulate with random data
records = {}
seed(3562901)
for i in range(0, size_to_test):
key = 92106429 + i
records[key] = [
key,
randint(0, 20),
randint(0, 20),
randint(0, 20),
randint(0, 20)
]
keys = sorted(list(records.keys()))
for _ in range(10):
for key in keys:
for j in range(1, grades_table.num_columns):
value = randint(0, 20)
records[key][j] = value
keys = sorted(list(records.keys()))
for key in keys:
print(records[key])
print(records[key])
time_select_2_s = process_time()
for key in keys:
record = query.select(key, 0, [1, 1, 1, 1, 1])[0]
error = False
for i, column in enumerate(record.columns):
if column != records[key][i]:
error = True
if error:
print('select error on', key, ':', record, ', correct:',
records[key])
time_select_2_f = process_time()
print("Select finished")
deleted_keys = sample(keys, 100)
for key in deleted_keys:
query.delete(key)
records.pop(key, None)
for i in range(0, 100):
r = sorted(sample(range(0, len(keys)), 2))
column_sum = sum(
map(lambda x: records[x][0]
if x in records else 0, keys[r[0]:r[1] + 1]))
result = query.sum(keys[r[0]], keys[r[1]], 0)
if column_sum != result:
print('sum error on [', keys[r[0]], ',', keys[r[1]], ']: ', result,
', correct: ', column_sum)
print("Aggregate finished")
db.close()
return [
'8192', time_update_f - time_update_s,
time_select_1_f - time_select_1_s, time_select_2_f - time_select_2_s
]
def performance_testing():
print("\n====Performance testing:====\n")
# Student Id and 4 grades
# Grades table has 5 columns, key is first column
db = Database()
grades_table = db.create_table('Grades', 5, 0)
assert grades_table._name == "Grades"
assert grades_table._num_columns == 5
assert grades_table._key == 0
print("Create table good")
return
##################################################
query = Query(grades_table)
keys = []
# Measuring Insert Performance
insert_time_0 = process_time()
for i in range(0, 10000):
query.insert(906659671 + i, 93, 0, 0, 0)
keys.append(906659671 + i)
insert_time_1 = process_time()
print("Inserting 10k records took: \t\t\t", insert_time_1 - insert_time_0)
# Measuring update Performance
update_cols = [
[randrange(0, 100), None, None, None, None],
[None, randrange(0, 100), None, None, None],
[None, None, randrange(0, 100), None, None],
[None, None, None, randrange(0, 100), None],
[None, None, None, None, randrange(0, 100)],
]
update_time_0 = process_time()
for i in range(0, 10000):
query.update(choice(keys), *(choice(update_cols)))
update_time_1 = process_time()
print("Updating 10k records took: \t\t\t", update_time_1 - update_time_0)
# Measuring Select Performance
select_time_0 = process_time()
for i in range(0, 10000):
query.select(choice(keys), [1, 1, 1, 1, 1])
select_time_1 = process_time()
print("Selecting 10k records took: \t\t\t", select_time_1 - select_time_0)
# Measuring Aggregate Performance
agg_time_0 = process_time()
for i in range(0, 10000, 100):
result = query.sum(i, 100, randrange(0, 5))
agg_time_1 = process_time()
print("Aggregate 10k of 100 record batch took:\t", agg_time_1 - agg_time_0)
# Measuring Delete Performance
delete_time_0 = process_time()
for i in range(0, 10000):
query.delete(906659671 + i)
delete_time_1 = process_time()
print("Deleting 10k records took: \t\t\t", delete_time_1 - delete_time_0)
def correctness_testing_after_close(fav_numbers):
print("\n====Correctness testing after close:====\n")
tests_passed = 0
tests_failed = 0
try:
assert fav_numbers._name == "fav_numbers"
assert fav_numbers._num_columns == 3 + Config.METADATA_COLUMN_COUNT
assert fav_numbers._key == 0
print("fav_numbers table attributes after closed passed")
tests_passed += 1
except Exception as exc:
print("fav_numbers table attributes after closed FAILED")
print(traceback.format_exc())
tests_failed += 1
query = Query(fav_numbers)
try:
# Try selecting 2 keys from above.
# Make sure each returns only 1 record and that record is correct.
# Note 25 was deleted in previous block
for testkey in [5, 99]:
# Get all columns
s = get_columns(query.select(testkey, 0, [1, 1, 1]))
# Make sure result is right length
# At this point select should return a record (not a list of records)
# So this is checking how many columns are in that record
assert len(s) == 3
# Make sure primary key matches
assert s[0] == testkey
print("\nfav_numbers primary key select after closed passed")
tests_passed += 1
except Exception as exc:
print("\nfav_numbers primary key select after closed FAILED")
print(traceback.format_exc())
tests_failed += 1
try:
query.delete(99)
print("\nfav_numbers delete after closed passed")
tests_passed += 1
except:
print("\nfav_numbers delete after closed FAILED")
print(traceback.format_exc())
tests_failed += 1
try:
query.delete(5)
print("\nfav_numbers delete updated record after closed passed")
tests_passed += 1
except:
print("\nfav_numbers delete updated record after closed FAILED")
print(traceback.format_exc())
tests_failed += 1
if tests_failed == 0:
print("\nAll {} tests passed!!! :)".format(str(tests_passed)))
else:
print("\nTests passed:", tests_passed)
print("Tests failed:", tests_failed)
def correctness_testing(fav_numbers):
print("\n====Correctness testing:====\n")
tests_passed = 0
tests_failed = 0
try:
assert fav_numbers._name == "fav_numbers"
assert fav_numbers._num_columns == 3 + Config.METADATA_COLUMN_COUNT
assert fav_numbers._key == 0
print("fav_numbers create table passed")
tests_passed += 1
except Exception as exc:
print("fav_numbers create table FAILED")
print(traceback.format_exc())
tests_failed += 1
query = Query(fav_numbers)
nums1 = [5, 6, 7]
nums2 = [25, 26, 27]
nums3 = [99, 98, 97]
query.insert(*nums1)
query.insert(*nums2)
query.insert(*nums3)
try:
# Try selecting 3 keys from above.
# Make sure each returns only 1 record and that record is correct.
for testkey in [5, 25, 99]:
# Get all columns
s = get_columns(query.select(testkey, 0, [1, 1, 1]))
# Make sure result is right length
# At this point select should return a record (not a list of records)
# So this is checking how many columns are in that record
assert len(s) == 3
# Make sure primary key matches
assert s[0] == testkey
print("\nfav_numbers primary key select passed")
tests_passed += 1
except Exception as exc:
print("\nfav_numbers primary key select FAILED")
print(traceback.format_exc())
tests_failed += 1
query.update(5, *(5, 12, 20))
try:
# Try selecting record just updated
s = get_columns(query.select(5, 0, [1, 1, 1]))
assert s == [5, 12, 20]
print("\nfav_numbers update + primary key select passed")
tests_passed += 1
except Exception as exc:
print("\nfav_numbers update + primary key select FAILED")
print(traceback.format_exc())
tests_failed += 1
try:
query.table.create_index(1)
query.table.create_index(2)
# Try selecting record with 26 in column 1 (non-primary key)
s = get_columns(query.select(26, 1, [1, 1, 1]))
assert s == [25, 26, 27]
# Insert a new record with 26 in column 1
query.insert(100, 26, 32)
# Try selecting 26 in column 1 again, should get 2 records now
s = query.select(26, 1, [1, 1, 1])
assert len(s) == 2, "Expected 2 records, instead found {}".format(len(s))
for record in s:
assert record.columns[1] == 26, "Expected 26 in column 1. Record found was {}".format(record)
print("\nfav_numbers non-primary key select passed")
tests_passed += 1
except Exception as exc:
print("\nfav_numbers non-primary key select FAILED")
print(traceback.format_exc())
tests_failed += 1
try:
# Drop indexes and assert they become None
query.table.drop_index(1)
query.table.drop_index(2)
assert query.table._indices.data[4] == None
assert query.table._indices.data[5] == None
# Create indexes again to not break later tests
query.table.create_index(1)
query.table.create_index(2)
print("\nfav_numbers drop_index passed")
tests_passed += 1
except Exception as exc:
print("\nfav_numbers drop_index FAILED")
print(traceback.format_exc())
tests_failed += 1
try:
s = get_columns(query.select(5, 0, [1, 0, 0]))
assert s == [5, None, None]
s = get_columns(query.select(5, 0, [0, 1, 0]))
assert s == [None, 12, None]
s = get_columns(query.select(5, 0, [0, 0, 1]))
assert s == [None, None, 20]
print("\nfav_numbers select certain columns only passed")
tests_passed += 1
except Exception as exc:
print("\nfav_numbers select certain columns only FAILED")
print(traceback.format_exc())
tests_failed += 1
try:
sum = query.sum(5, 99, 0)
assert sum == 129, "Sum all entries in column 0, expected 129, found {}".format(str(sum))
sum = query.sum(5, 99, 1)
assert sum == 136, "Sum all entries in column 1, expected 136, found {}".format(str(sum))
sum = query.sum(5, 6, 1)
assert sum == 12, "Sum first entry in column 1, expected 12, found {}".format(str(sum))
sum = query.sum(25, 99, 2)
assert sum == 124, "Sum last two entries in column 2, expected 124, found {}".format(str(sum))
print("\nfav_numbers sum passed")
tests_passed += 1
except Exception as exc:
print("\nfav_numbers sum FAILED")
print(traceback.format_exc())
tests_failed += 1
try:
query.delete(25)
s = query.select(25, 0, [1, 1, 1])
assert s == None, "Expected None from attempt to select deleted record"
print("\nfav_numbers delete + select passed")
tests_passed += 1
except Exception as exc:
print("\nfav_numbers delete + select FAILED")
print(traceback.format_exc())
tests_failed += 1
if tests_failed == 0:
print("\nAll {} tests passed!!! :)".format(str(tests_passed)))
else:
print("\nTests passed:", tests_passed)
print("Tests failed:", tests_failed)
from JellyDB.db import Database
from JellyDB.query import Query
from random import choice, randint, sample, seed
# Student Id and 4 grades
db = Database()
db.open('~/ECS165')
grades_table = db.get_table('Grades')
query = Query(grades_table)
# repopulate with random data
records = {}
seed(3562901)
for i in range(0, 1000):
key = 92106429 + i
records[key] = [
key,
randint(0, 20),
randint(0, 20),
randint(0, 20),
randint(0, 20)
]
keys = sorted(list(records.keys()))
for _ in range(10):
for key in keys:
for j in range(1, grades_table.num_columns):
value = randint(0, 20)
records[key][j] = value
keys = sorted(list(records.keys()))
for key in keys:
def performance_testing(range_to_test):
db = Database()
grades_table = db.create_table('Grades', 5, 0)
query = Query(grades_table)
records = {}
tests_passed = 0
tests_failed = 0
insert_time_0 = process_time()
for i in range(0, range_to_test):
key = 92106429 + randint(0, 9000)
while key in records:
key = 92106429 + randint(0, 9000)
records[key] = [
key,
randint(0, 20),
randint(0, 20),
randint(0, 20),
randint(0, 20)
]
query.insert(*records[key])
insert_time_1 = process_time()
time_insert = insert_time_1 - insert_time_0
print('inserted', range_to_test, 'records took: \t\t\t', time_insert)
select_time_0 = process_time()
for key in records:
record = query.select(key, [1, 1, 1, 1, 1])[0]
error = False
for i, column in enumerate(record.columns):
if column != records[key][i]:
error = True
if error:
print('select error on', key, ':', record, ', correct:',
records[key])
select_time_1 = process_time()
time_select = select_time_1 - select_time_0
print('select', range_to_test, 'records took: \t\t\t', time_select)
update_time_0 = process_time()
for key in records:
updated_columns = [None, None, None, None, None]
for i in range(1, grades_table.num_columns):
value = randint(0, 20)
updated_columns[i] = value
original = records[key].copy()
records[key][i] = value
query.update(key, *updated_columns)
record = query.select(key, [1, 1, 1, 1, 1])[0]
error = False
for j, column in enumerate(record.columns):
if column != records[key][j]:
error = True
if error:
print('update error on', original, 'and', updated_columns, ':',
record, ', correct:', records[key])
updated_columns[i] = None
update_time_1 = process_time()
time_update = update_time_1 - update_time_0
print('updated', range_to_test, 'records took: \t\t\t', time_update)
keys = sorted(list(records.keys()))
agg_time_0 = process_time()
for c in range(0, grades_table.num_columns):
for i in range(0, 20):
r = sorted(sample(range(0, len(keys)), 2))
column_sum = sum(
map(lambda key: records[key][c], keys[r[0]:r[1] + 1]))
result = query.sum(keys[r[0]], keys[r[1]], c)
if column_sum != result:
print('sum error on [', keys[r[0]], ',', keys[r[1]], ']: ',
result, ', correct: ', column_sum)
agg_time_1 = process_time()
time_sum = agg_time_1 - agg_time_0
print('Sum', range_to_test, 'records took: \t\t\t', time_sum)
delete_time_0 = process_time()
for key in records:
try:
query.delete(key)
s = query.select(key, [1, 1, 1])
assert s == None, "Expected None from attempt to select deleted record"
tests_passed += 1
except Exception as exc:
print("\nfav_numbers delete + select FAILED")
print(traceback.format_exc())
tests_failed += 1
delete_time_1 = process_time()
time_delete = delete_time_1 - delete_time_0
print('Deletion', range_to_test, 'records took: \t\t\t', time_delete,
'true deletion completed:', tests_passed, 'deletion failed:',
tests_failed)
return time_insert, time_select, time_update, time_sum, time_delete