Database - gotonode/ohtu GitHub Wiki

Our application utilises a SQLite database. When the app is started the database, named bookmarks.db, is created in the root file of the project automatically if there is no database already in existence.

The automated tests use a separate SQLite database named test.db. Always before executing the tests a new test.db is created and any existing old test database is deleted. The test.db is created into a temporary file and is always deleted after the tests are completed.

The Database Diagram:

The CREATE TABLE Statements:

CREATE TABLE IF NOT EXISTS User (
    id INTEGER PRIMARY KEY,
    username varchar(100),
    password char(128)
);

CREATE TABLE IF NOT EXISTS Bookmark (
    id INTEGER PRIMARY KEY,
    user_id integer NOT NULL,
    title varchar(200),
    addDate datetime default current_timestamp,
    type varchar(1),
    FOREIGN KEY (user_id) REFERENCES User(id)
);

CREATE TABLE IF NOT EXISTS Blogpost (
    id integer NOT NULL,
    author varchar(100),
    url varchar(400),
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES Bookmark(id)
);

CREATE TABLE IF NOT EXISTS Video (
    id integer NOT NULL,
    url varchar(400),
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES Bookmark(id)
);

CREATE TABLE IF NOT EXISTS Book (
    id integer NOT NULL,
    author varchar(100),
    isbn varchar(20),
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES Bookmark(id)
);

Here is the code used in https://yuml.me to create the database diagram:

[Bookmark|(pk) id:Integer; (fk) user_id:User; title:String; addDate:Date; type:Char],
[Blogpost|(pk)(fk) id:Bookmark; author:String; url:String],
[Video|(pk)(fk) id:Bookmark; url:String],
[Book| (pk)(fk) id:Bookmark; author:String; isbn:String],
[User| (pk) id:Integer; username:String; password:String],

[Bookmark]-[Blogpost], [Bookmark]-[Video], [Bookmark]-[Book], [Bookmark]*-1[User]