26 lines
1.0 KiB
SQL
26 lines
1.0 KiB
SQL
CREATE TABLE author
|
|
( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
when_created TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
name VARCHAR(128) NOT NULL CONSTRAINT author_name_unique UNIQUE
|
|
);
|
|
|
|
CREATE TABLE book
|
|
( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
author_id INTEGER NOT NULL CONSTRAINT book_author_fk REFERENCES author (id),
|
|
gutenberg_id INTEGER NOT NULL CONSTRAINT book_gutid_unique UNIQUE,
|
|
when_created TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
title VARCHAR(128) NOT NULL
|
|
);
|
|
|
|
COMMENT ON TABLE book IS 'A book present in the catalog of Project Gutenberg,
|
|
<https://www.gutenberg.org/>. Book titles are intentionally not unique,
|
|
not even within a given author (see for example "Paradise Lost" by John
|
|
Milton). In the catalog some titles have no author but for this table
|
|
the foreign key to author is required, so there is a bogus "[no author]"
|
|
row in the author table.';
|
|
|
|
CREATE INDEX book_authid ON book (author_id);
|
|
CREATE INDEX book_title ON book (title);
|
|
|
|
GRANT SELECT ON author, book TO pgc_user;
|