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

     1  DROP TABLE IF EXISTS article_url CASCADE;
     2  DROP TABLE IF EXISTS article_keyword CASCADE;
     3  DROP TABLE IF EXISTS author_attr CASCADE;
     4  DROP TABLE IF EXISTS article CASCADE;
     5  DROP TABLE IF EXISTS author CASCADE;
     6  DROP TABLE IF EXISTS publication CASCADE;
     7  DROP TABLE IF EXISTS version;
     8  DROP TABLE IF EXISTS settings;
     9  
    10  CREATE TABLE publication (
    11      id SERIAL PRIMARY KEY,
    12      code TEXT NOT NULL,
    13      name TEXT NOT NULL DEFAULT '',
    14      domain TEXT NOT NULL DEFAULT ''
    15  );
    16  CREATE INDEX ON publication(id);
    17  CREATE INDEX ON publication(code);
    18  
    19  CREATE TABLE article (
    20      id SERIAL PRIMARY KEY,
    21      added TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    22      canonical_url TEXT NOT NULL,
    23      headline TEXT NOT NULL,
    24      content TEXT NOT NULL DEFAULT '',
    25      published TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    26      updated TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    27      publication_id INT NOT NULL REFERENCES publication (id),
    28      section TEXT NOT NULL DEFAULT '',
    29      extra TEXT NOT NULL DEFAULT ''
    30  );
    31  CREATE INDEX ON article(id);
    32  CREATE INDEX ON article(published);
    33  CREATE INDEX ON article(publication_id);
    34  
    35  CREATE TABLE author (
    36      id SERIAL PRIMARY KEY,
    37      name TEXT NOT NULL,
    38      rel_link TEXT NOT NULL DEFAULT '',
    39      email TEXT NOT NULL DEFAULT '',
    40      twitter TEXT NOT NULL DEFAULT ''
    41  );
    42  CREATE INDEX ON author(id);
    43  
    44  CREATE TABLE author_attr (
    45      id SERIAL PRIMARY KEY,
    46      author_id INT NOT NULL REFERENCES author (id) ON DELETE CASCADE,
    47      article_id INT NOT NULL REFERENCES article (id) ON DELETE CASCADE
    48  );
    49  CREATE INDEX ON author_attr(author_id);
    50  CREATE INDEX ON author_attr(article_id);
    51  
    52  CREATE TABLE article_url (
    53      id SERIAL PRIMARY KEY,
    54      url TEXT NOT NULL,
    55      article_id INT NOT NULL REFERENCES article (id) ON DELETE CASCADE
    56  );
    57  CREATE INDEX ON article_url(id);
    58  CREATE INDEX ON article_url(article_id);
    59  CREATE INDEX ON article_url(url);
    60  
    61  
    62  CREATE TABLE article_keyword (
    63      id SERIAL PRIMARY KEY,
    64      article_id INT NOT NULL REFERENCES article (id) ON DELETE CASCADE,
    65      name TEXT NOT NULL,
    66      url TEXT NOT NULL DEFAULT ''
    67  );
    68  CREATE INDEX ON article_keyword(id);
    69  CREATE INDEX ON article_keyword(article_id);
    70  CREATE INDEX ON article_keyword(name);
    71  
    72  CREATE TABLE version (ver INTEGER NOT NULL);
    73  CREATE TABLE settings (n TEXT, v TEXT NOT NULL);
    74  INSERT INTO version (ver) VALUES (7);
    75