sql-tables.sql

-- This script was generated by the ERD tool in pgAdmin 4.
-- Please log an issue at https://redmine.postgresql.org/projects/pgadmin4/issues/new if you find any bugs, including reproduction steps.
BEGIN;

CREATE SEQUENCE IF NOT EXISTS books_book_id_seq;
CREATE TABLE IF NOT EXISTS public.books
(
    book_id integer NOT NULL DEFAULT nextval('books_book_id_seq'::regclass),
    book_name text COLLATE pg_catalog."default",
    book_author text COLLATE pg_catalog."default",
    book_number_of_pages bigint,
    book_genre text COLLATE pg_catalog."default",
    book_count bigint,
    CONSTRAINT books_pkey PRIMARY KEY (book_id)
);

CREATE SEQUENCE IF NOT EXISTS logs_log_id_seq;
CREATE TABLE IF NOT EXISTS public.logs
(
    log_id integer NOT NULL DEFAULT nextval('logs_log_id_seq'::regclass),
    user_id bigint,
    book_id bigint,
    borrowed boolean DEFAULT false,
    read boolean DEFAULT false,
    favorited boolean DEFAULT false,
    added boolean DEFAULT false,
    "timestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT logs_pkey PRIMARY KEY (log_id)
);

CREATE SEQUENCE IF NOT EXISTS users_user_id_seq;
CREATE TABLE IF NOT EXISTS public.users
(
    user_id integer NOT NULL DEFAULT nextval('users_user_id_seq'::regclass),
    user_name text COLLATE pg_catalog."default",
    user_password text COLLATE pg_catalog."default",
    CONSTRAINT users_pkey PRIMARY KEY (user_id)
);

ALTER TABLE IF EXISTS public.logs
    ADD CONSTRAINT logs_book_id_fkey FOREIGN KEY (book_id)
    REFERENCES public.books (book_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

ALTER TABLE IF EXISTS public.logs
    ADD CONSTRAINT logs_user_id_fkey FOREIGN KEY (user_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

END;