Database

The Database

The database is the most fundamental component of the quotebook. It is the quotebook. The database also contains users, logs, likes etc. Currently the quotebook is built on SQLite, there are plans to migrate to mysql at some point before the quotebook is deployed.

ER Diagram

DDL

CREATE TABLE users (
`id` INTEGER PRIMARY KEY,
`name` TEXT NOT NULL,
`email` TEXT UNIQUE NOT NULL,
`password_hash` TEXT NOT NULL,
`created_at` INTEGER NOT NULL,
`style` TEXT DEFAULT 'light',
`plevel` INTEGER NOT NULL
);

CREATE TABLE `quotes` (
`id` INTEGER PRIMARY KEY,
`author` TEXT DEFAULT 'Unknown',
`year` TEXT NOT NULL,
`quote` TEXT NOT NULL,
`likes` INTEGER DEFAULT 0
);

CREATE TABLE `reports` (
`id` INTEGER PRIMARY KEY,
`user_id` INTEGER REFERENCES users (id),
`quote_id` INTEGER REFERENCES quotes (id),
`reason` TEXT NOT NULL,
`details` TEXT,
`status` INTEGER
);

CREATE TABLE `logs` (
`id` INTEGER PRIMARY KEY,
`user_id` INTEGER REFERENCES users (id),
`action` TEXT,
`message` TEXT,
`time` TEXT,
`ip` TEXT,
`agent` TEXT
);

CREATE TABLE `likes` (
`user_id` INTEGER REFERENCES users (id),
`quote_id` INTEGER REFERENCES quotes (id)
);

CREATE TABLE `comments` (
`user_id` INTEGE REFERENCES users(id),
`quote_id` INTEGER REFERENCES quotes (id),
`comment` TEXT
);

Database Manager

The database manager is a class in utils\databaseManager.py that handles the connection to the database. A query can be executed with the .query() method. A security design of the .query() method is that only one statement can be executed i.e

db.query("SELECT * FROM quotes") # This runs fine
db.query("SELECT * FROM quotes; SELECT * FROM likes") # This returns a DatabaseSecurityError

if the option to execute multiple statements is desired then use:

db.multi_query("SELECT * FROM quotes; SELECT * FROM likes") # This runs fine

One important thing to note is that the Database Manager never raises an error, it returns it.