def create_index(self, table_name, index_name, index_type='Btree'):
'''
Create an index on a specified table with a given name.
Important: An index can only be created on a primary key. Thus the user does not specify the column
table_name -> table's name (needs to exist in database)
index_name -> name of the created index
'''
answer = privileges("'create index'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
if self.tables[
table_name].pk_idx is None: # if no primary key, no index
print('## ERROR - Cant create index. Table has no primary key.')
return
if index_name not in self.tables['meta_indexes'].index_name:
# currently only btree is supported. This can be changed by adding another if.
if index_type == 'Btree':
print('Creating Btree index.')
# insert a record with the name of the index and the table on which it's created to the meta_indexes table
self.tables['meta_indexes']._insert([table_name, index_name])
# crate the actual index
self._construct_index(table_name, index_name)
self.save()
else:
print(
'## ERROR - Cant create index. Another index with the same name already exists.'
)
return
def update(self, table_name, set_value, set_column, condition):
'''
Update the value of a column where condition is met.
table_name -> table's name (needs to exist in database)
set_value -> the new value of the predifined column_name
set_column -> the column that will be altered
condition -> a condition using the following format :
'column[<,<=,==,>=,>]value' or
'value[<,<=,==,>=,>]column'.
operatores supported -> (<,<=,==,>=,>)
'''
answer = privileges("'update'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
self.load(self.savedir)
if self.is_locked(table_name):
return
self.lockX_table(table_name)
self.tables[table_name]._update_row(set_value, set_column, condition)
self.unlock_table(table_name)
self._update()
self.save()
def delete(self, table_name, condition):
'''
Delete rows of a table where condition is met.
table_name -> table's name (needs to exist in database)
condition -> a condition using the following format :
'column[<,<=,==,>=,>]value' or
'value[<,<=,==,>=,>]column'.
operatores supported -> (<,<=,==,>=,>)
'''
answer = privileges("'delete'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
self.load(self.savedir)
if self.is_locked(table_name):
return
self.lockX_table(table_name)
deleted = self.tables[table_name]._delete_where(condition)
self.unlock_table(table_name)
self._update()
self.save()
# we need the save above to avoid loading the old database that still contains the deleted elements
if table_name[:4] != 'meta':
self._add_to_insert_stack(table_name, deleted)
self.save()
def insert(self, table_name, row, lock_load_save=True):
'''
Inserts into table
table_name -> table's name (needs to exist in database)
row -> a list of the values that are going to be inserted (will be automatically casted to predifined type)
lock_load_save -> If false, user need to load, lock and save the states of the database (CAUTION). Usefull for bulk loading
'''
answer = privileges("'insert'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
if lock_load_save:
self.load(self.savedir)
if self.is_locked(table_name):
return
# fetch the insert_stack. For more info on the insert_stack
# check the insert_stack meta table
self.lockX_table(table_name)
insert_stack = self._get_insert_stack_for_table(table_name)
try:
self.tables[table_name]._insert(row, insert_stack)
except Exception as e:
print(e)
print('ABORTED')
# sleep(2)
self._update_meta_insert_stack_for_tb(table_name, insert_stack[:-1])
if lock_load_save:
self.unlock_table(table_name)
self._update()
self.save()
def table_from_object(self, new_table):
'''
Add table obj to database.
'''
answer = privileges("'table from object'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
self.tables.update({new_table._name: new_table})
if new_table._name not in self.__dir__():
setattr(self, new_table._name, new_table)
else:
raise Exception(
f'"{new_table._name}" attribute already exists in class "{self.__class__.__name__}".'
)
self._update()
self.save()
def sort(self, table_name, column_name, asc=False):
'''
Sorts a table based on a column
table_name -> table's name (needs to exist in database)
column_name -> the column that will be used to sort
asc -> If True sort will return results using an ascending order. Def: False
'''
answer = privileges("'sort'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
self.load(self.savedir)
if self.is_locked(table_name):
return
self.lockX_table(table_name)
self.tables[table_name]._sort(column_name, asc=asc)
self.unlock_table(table_name)
self._update()
self.save()
def cast_column(self, table_name, column_name, cast_type):
'''
Change the type of the specified column and cast all the prexisting values.
Basically executes type(value) for every value in column and saves
table_name -> table's name (needs to exist in database)
column_name -> the column that will be casted (needs to exist in table)
cast_type -> needs to be a python type like str int etc. NOT in ''
'''
answer = privileges("'cast column'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
self.load(self.savedir)
if self.is_locked(table_name):
return
self.lockX_table(table_name)
self.tables[table_name]._cast_column(column_name, cast_type)
self.unlock_table(table_name)
self._update()
self.save()
def inner_join(self,
left_table_name,
right_table_name,
condition,
save_as=None,
return_object=False):
'''
Join two tables that are part of the database where condition is met.
left_table_name -> left table's name (needs to exist in database)
right_table_name -> right table's name (needs to exist in database)
condition -> a condition using the following format :
'column[<,<=,==,>=,>]value' or
'value[<,<=,==,>=,>]column'.
operatores supported -> (<,<=,==,>=,>)
save_as -> The name that will be used to save the resulting table in the database. Def: None (no save)
return_object -> If true, the result will be a table object (usefull for internal usage). Def: False (the result will be printed)
'''
answer = privileges("'inner join'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
self.load(self.savedir)
if self.is_locked(left_table_name) or self.is_locked(right_table_name):
print(f'Table/Tables are currently locked')
return
res = self.tables[left_table_name]._inner_join(
self.tables[right_table_name], condition)
if save_as is not None:
res._name = save_as
self.table_from_object(res)
else:
if return_object:
return res
else:
res.show()
def table_from_csv(self,
filename,
name=None,
column_types=None,
primary_key=None):
'''
Create a table from a csv file.
If name is not specified, filename's name is used
If column types are not specified, all are regarded to be of type str
'''
answer = privileges("'table from csv'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
if name is None:
name = filename.split('.')[:-1][0]
file = open(filename, 'r')
first_line = True
for line in file.readlines():
if first_line:
colnames = line.strip('\n').split(',')
if column_types is None:
column_types = [str for _ in colnames]
self.create_table(name=name,
column_names=colnames,
column_types=column_types,
primary_key=primary_key)
self.lockX_table(name)
first_line = False
continue
self.tables[name]._insert(line.strip('\n').split(','))
self.unlock_table(name)
self._update()
self.save()
def __init__(self, name, load=True):
self.tables = {}
self._name = name
self.savedir = f'dbdata/{name}_db'
if load:
try:
self.load(self.savedir)
print(f'Loaded "{name}".')
return
except:
print(f'"{name}" db does not exist, creating new.')
answer = privileges("'create Database'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
# create dbdata directory if it doesnt exist
if not os.path.exists('dbdata'):
os.mkdir('dbdata')
# create new dbs save directory
try:
os.mkdir(self.savedir)
except:
pass
# create all the meta tables
self.create_table('meta_length', ['table_name', 'no_of_rows'],
[str, int])
self.create_table('meta_locks', ['table_name', 'locked'], [str, bool])
self.create_table('meta_insert_stack', ['table_name', 'indexes'],
[str, list])
self.create_table('meta_indexes', ['table_name', 'index_name'],
[str, str])
self.save()
def create_table(self,
name=None,
column_names=None,
column_types=None,
primary_key=None,
load=None):
'''
This method create a new table. This table is saved and can be accessed by
db_object.tables['table_name']
or
db_object.table_name
'''
if name != "meta_length" and name != "meta_locks" and name != "meta_insert_stack" and name != "meta_indexes":
answer = privileges("'create table'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
self.tables.update({
name:
Table(name=name,
column_names=column_names,
column_types=column_types,
primary_key=primary_key,
load=load)
})
# self._name = Table(name=name, column_names=column_names, column_types=column_types, load=load)
# check that new dynamic var doesnt exist already
if name not in self.__dir__():
setattr(self, name, self.tables[name])
else:
raise Exception(
f'Attribute "{name}" already exists in class "{self.__class__.__name__}".'
)
# self.no_of_tables += 1
print(f'New table "{name}"')
self._update()
self.save()
def drop_table(self, table_name):
'''
Drop table with name 'table_name' from current db
'''
answer = privileges("'drop table'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
self.load(self.savedir)
if self.is_locked(table_name):
return
self.tables.pop(table_name)
delattr(self, table_name)
if os.path.isfile(f'{self.savedir}/{table_name}.pkl'):
os.remove(f'{self.savedir}/{table_name}.pkl')
else:
print(f'"{self.savedir}/{table_name}.pkl" does not exist.')
self.delete('meta_locks', f'table_name=={table_name}')
self.delete('meta_length', f'table_name=={table_name}')
self.delete('meta_insert_stack', f'table_name=={table_name}')
# self._update()
self.save()
def drop_db(self):
answer = privileges("'drop database'")
if answer == '2':
print("You need Admin privileges to run this method.")
return
shutil.rmtree(self.savedir)