sql.txt

DROP TABLE IF EXISTS USER_BOOKS;

DROP TABLE IF EXISTS USERS;

DROP TABLE IF EXISTS BOOKS;

CREATE TABLE USERS (
	USERNAME TEXT NOT NULL PRIMARY KEY,
	EMAIL TEXT NOT NULL,
	PWD TEXT NOT NULL,
	PFP TEXT NULL
);

CREATE TABLE BOOKS (
	ID TEXT NOT NULL PRIMARY KEY,
	NAME TEXT,
	DESCRIPTION TEXT,
	AUTHOR TEXT,
	EDITOR TEXT,
	PUBLICATION_DATE DATE,
	PAGE_QTY INT,
	BOOK_COVER_URI TEXT
);

DROP TABLE IF EXISTS USER_BOOKS;

CREATE TABLE USER_BOOKS (
	BOOK_ID TEXT NOT NULL,
	USERNAME TEXT NOT NULL,
	CREATION_DATE DATE,
	UPDATE_DATE DATE,
	CONSTRAINT PK_USER_BOOKS PRIMARY KEY (BOOK_ID, USERNAME),
	CONSTRAINT FK_USERS FOREIGN KEY (USERNAME) REFERENCES USERS (USERNAME) ON DELETE CASCADE,
	CONSTRAINT FK_BOOKS FOREIGN KEY (BOOK_ID) REFERENCES BOOKS (ID) ON DELETE CASCADE
);