github.com/soulteary/pocket-bookcase@v0.0.0-20240428065142-0b5a9a0fc98a/internal/database/migrations/postgres/0001_initial.up.sql (about)

     1  CREATE TABLE IF NOT EXISTS account(
     2  		id       SERIAL,
     3  		username VARCHAR(250) NOT NULL,
     4  		password BYTEA        NOT NULL,
     5  		owner    BOOLEAN      NOT NULL DEFAULT FALSE,
     6  		config   JSONB        NOT NULL DEFAULT '{}',
     7  		PRIMARY KEY (id),
     8  		CONSTRAINT account_username_UNIQUE UNIQUE (username));
     9  
    10  CREATE TABLE IF NOT EXISTS bookmark(
    11  		id          SERIAL,
    12  		url         TEXT       NOT NULL,
    13  		title       TEXT       NOT NULL,
    14  		excerpt     TEXT       NOT NULL DEFAULT '',
    15  		author      TEXT       NOT NULL DEFAULT '',
    16  		public      SMALLINT   NOT NULL DEFAULT 0,
    17  		content     TEXT       NOT NULL DEFAULT '',
    18  		html        TEXT       NOT NULL DEFAULT '',
    19  		modified    TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    20  		has_content BOOLEAN NOT NULL DEFAULT FALSE,
    21  		PRIMARY KEY(id),
    22  		CONSTRAINT bookmark_url_UNIQUE UNIQUE (url));
    23  
    24  CREATE TABLE IF NOT EXISTS tag(
    25  		id   SERIAL,
    26  		name VARCHAR(250) NOT NULL,
    27  		PRIMARY KEY (id),
    28  		CONSTRAINT tag_name_UNIQUE UNIQUE (name));
    29  
    30  CREATE TABLE IF NOT EXISTS bookmark_tag(
    31  		bookmark_id INT      NOT NULL,
    32  		tag_id      INT      NOT NULL,
    33  		PRIMARY KEY(bookmark_id, tag_id),
    34  		CONSTRAINT bookmark_tag_bookmark_id_FK FOREIGN KEY (bookmark_id) REFERENCES bookmark (id),
    35  		CONSTRAINT bookmark_tag_tag_id_FK FOREIGN KEY (tag_id) REFERENCES tag (id));
    36  
    37  CREATE INDEX IF NOT EXISTS bookmark_tag_bookmark_id_FK ON bookmark_tag (bookmark_id);
    38  CREATE INDEX IF NOT EXISTS bookmark_tag_tag_id_FK ON bookmark_tag (tag_id);