def delete(self):
""" Removes user from the database """
db = get_db()
query = "DELETE FROM `users` WHERE `id` = ?"
values = (self.id, )
DB.execute_delete_query(db, query, values)
Todo.delete_todos_by_user_id(self.id)
def delete(self):
""" Removes todo item from the database """
db = get_db()
query = "DELETE FROM `todo_items` WHERE `id` = ?"
values = (self.id, )
DB.execute_delete_query(db, query, values)
self.update_history('remove')
def set_admin_status(self, is_admin):
"""toggle admin status for user - add new record or delete existed record
in user permission table"""
db = get_db()
if self.is_admin != is_admin:
if self.is_admin:
query = "DELETE FROM users_permissions WHERE user_id = ?"
values = (self.id, )
DB.execute_delete_query(db, query, values)
else:
query = "INSERT INTO `users_permissions` (user_id, permission_id) VALUES (?, ?)"
values = (self.id, 1)
DB.execute_insert_query(db, query, values)
def update_history(self, event):
"""saves events with event time in database"""
event_dict = {
'create': 1,
'remove': 2,
'archive': 3,
'activate': 4,
'update': 5,
'status done': 6,
'status undone': 7
}
history_query = "INSERT INTO todo_history (item_id, change_date, event_id) VALUES (?, ?, ?)"
values = (self.id, time.strftime("%Y-%m-%d %H:%M"), event_dict[event])
DB.execute_insert_query(get_db(), history_query, values)
def save(self):
""" Saves/updates user data in database """
db = get_db()
if self.id: # user edit
query = "UPDATE `users` SET `name` = ?, `password` = ?, `email` = ?, `registration_date` = ? " \
" WHERE id = ?;"
values = (self.name, self.password, self.email,
self.registration_date, self.id)
DB.execute_update_query(db, query, values)
else: # new user
query = "INSERT INTO `users` (`name`, `password`, `email`, `registration_date`) " \
"VALUES (?, ?, ?, ?); "
values = (self.name, self.password, self.email,
self.registration_date)
DB.execute_insert_query(db, query, values)
def is_user_with_name_in_user_list(cls, name):
"""checks that is user with given name in database"""
db = get_db()
query = "SELECT * FROM users WHERE name=?"
values = (name, )
user_from_db = DB.execute_select_query(db, query, values)
return bool(user_from_db)
def active_todos_undone_count(self):
"""connects with db and counts active todos with undone status for user"""
db = get_db()
query = """SELECT COUNT(`id`) FROM `todo_items`
WHERE `owner_id` = ? AND `is_archived` = 0 AND `status` = 0;"""
values = (self.id, )
return DB.execute_select_query(db, query, values)[0][0]
def is_user_with_email_in_user_list(cls, email):
"""checks that is user with given email in database"""
db = get_db()
query = "SELECT * FROM users WHERE email=?"
values = (email, )
user_from_db = DB.execute_select_query(db, query, values)
return bool(user_from_db)
def archived_todos_count(self):
"""connects with db and counts archived todos for user"""
db = get_db()
query = """SELECT COUNT(`id`) FROM `todo_items`
WHERE `owner_id` = ? AND `is_archived` = 1;"""
values = (self.id, )
return DB.execute_select_query(db, query, values)[0][0]
def is_admin(self):
"""checks that user has admin status """
db = get_db()
query = "SELECT users.id FROM users_permissions " \
"JOIN users ON users.id=users_permissions.user_id " \
"JOIN permission_types ON users_permissions.permission_id = permission_types.id " \
"WHERE users.id = ? AND permission_types.name = ?"
values = (self.id, 'admin')
return bool(DB.execute_select_query(db, query, values))
def save(self):
""" Saves/updates todo item in database """
db = get_db()
if self.id:
query = "UPDATE `todo_items` SET `name` = ?, `status` = ?, " \
"`priority` = ?, `due_date` = ?, `is_archived` = ?, `description` = ? " \
"WHERE id = ?;"
values = (self.name, self.status, self.priority, self.due_date,
int(self.is_archived), self.description, self.id)
DB.execute_update_query(db, query, values)
self.update_history('update')
else:
query = """INSERT INTO
`todo_items` (`name`, `create_date`, `priority`, `due_date`,
`owner_id`, `description`)
VALUES (?, ?, ?, ?, ?, ?); """
values = (self.name, self.create_date, self.priority,
self.due_date, self.owner_id, self.description)
DB.execute_insert_query(db, query, values)
self.update_history('create')
def get_users_list(cls):
""" Retrieves all users from database and returns them as list.
Returns:
list(Userlist): list of all users
"""
db = get_db()
users = []
query = """SELECT `name`, `password`, `id`, `email`, `registration_date` FROM `users`;"""
users_from_db = DB.execute_select_query(db, query)
for user in users_from_db:
users.append(User(*user))
return users
def get_by_id(cls, id_):
""" Retrieves user with given id from database.
Args:
id_(int): user id
Returns:
Todo: User object with a given id
"""
db = get_db()
query = """SELECT `name`, `password`, `id`, `email`, `registration_date`
FROM `users`
WHERE `id` = ?;"""
values = (id_, )
user_from_db = DB.execute_select_query(db, query, values)
return User(*user_from_db[0]) if user_from_db else None
def get_by_id(cls, id_):
""" Retrieves todo item with given id from database.
Args:
id_(int): item id
Returns:
Todo: Todo object with a given id
"""
db = get_db()
query = "SELECT `name`, `id`, `status`, `create_date`, `priority`, `due_date`," \
" `owner_id`, `is_archived`, `description` " \
"FROM `todo_items` " \
"WHERE `todo_items`.`id` = ?;"
values = (id_, )
todo_item_from_db = DB.execute_select_query(db, query, values)
return Todo(*todo_item_from_db[0]) if todo_item_from_db else None
def get_by_email(cls, email):
""" Retrieves user with given name from database.
Args:
name(string): user name,
db: dababase object
Returns:
User object with a given name
"""
db = get_db()
query = """SELECT `name`, `password`, `id`, `email`, `registration_date`
FROM `users`
WHERE `email` = ?;"""
values = (email, )
user_from_db = DB.execute_select_query(db, query, values)
return User(*user_from_db[0])
def get_all(cls, user_id, is_archived=False):
""" Retrieves all Todos form database and returns them as list.
Returns:
list(Todo): list of all todos
"""
db = get_db()
todo_list = []
query = "SELECT `name`, `id`, `status`, `create_date`, " \
"`priority`, `due_date`, `owner_id`, `is_archived`, `description` " \
"FROM `todo_items` " \
"WHERE `is_archived` = ? AND `owner_id` = ? ORDER BY create_date DESC;"
values = (int(is_archived), user_id)
todo_items_from_db = DB.execute_select_query(db, query, values)
for item in todo_items_from_db:
todo_list.append(Todo(*item))
return todo_list
def delete_todos_by_user_id(cls, user_id):
""" removes all todos belong to user with user_id"""
db = get_db()
query = "DELETE FROM `todo_items` WHERE `owner_id` = ?"
values = (user_id, )
DB.execute_delete_query(db, query, values)
def get_db():
db = getattr(g, '_database', None)
if not db:
db = g._database = DB.connect(BaseConfig.DATABASE)
return db