github.com/bcampbell/scrapeomat@v0.0.0-20220820232205-23e64141c89e/store/sqlstore/pg/000initial.sql (about)

     1  
     2  CREATE TABLE publication (
     3      id SERIAL PRIMARY KEY,
     4      code TEXT NOT NULL,
     5      name TEXT NOT NULL DEFAULT '',
     6      domain TEXT NOT NULL DEFAULT ''
     7  );
     8  CREATE INDEX ON publication(id);
     9  CREATE INDEX ON publication(code);
    10  
    11  CREATE TABLE article (
    12      id SERIAL PRIMARY KEY,
    13      added TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    14      canonical_url TEXT NOT NULL,
    15      headline TEXT NOT NULL,
    16      content TEXT NOT NULL DEFAULT '',
    17      published TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    18      updated TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    19      publication_id INT NOT NULL REFERENCES publication (id),
    20      -- TODO: keywords
    21  );
    22  CREATE INDEX ON article(id);
    23  
    24  CREATE TABLE author (
    25      id SERIAL PRIMARY KEY,
    26      name TEXT NOT NULL,
    27      rel_link TEXT NOT NULL DEFAULT '',
    28      email TEXT NOT NULL DEFAULT '',
    29      twitter TEXT NOT NULL DEFAULT ''
    30  );
    31  CREATE INDEX ON author(id);
    32  
    33  CREATE TABLE author_attr (
    34      id SERIAL PRIMARY KEY,
    35      author_id INT NOT NULL REFERENCES author (id) ON DELETE CASCADE,
    36      article_id INT NOT NULL REFERENCES article (id) ON DELETE CASCADE
    37  );
    38  CREATE INDEX ON author_attr(author_id);
    39  CREATE INDEX ON author_attr(article_id);
    40  
    41  CREATE TABLE article_url (
    42      id SERIAL PRIMARY KEY,
    43      url TEXT NOT NULL UNIQUE,
    44      article_id INT NOT NULL REFERENCES article (id) ON DELETE CASCADE
    45  );
    46  CREATE INDEX ON article_url(id);
    47  CREATE INDEX ON article_url(article_id);
    48  CREATE INDEX ON article_url(url);
    49