bW21atnj

1import { server, } from './query';
2
3const data = `
4  INSERT INTO schools (address, approved, name) VALUES ('Staničná 8, Trenčín', TRUE, 'Stredná umelecká škola');
5  INSERT INTO schools (address, approved, name) VALUES ('Nad Ovčírnou IV 2528, Zlín', TRUE, 'Střední průmyslová škola polytechnická');
6  
7  INSERT INTO years (id, name, opened) VALUES (2017, 'Divadelný plagát', FALSE);
8  INSERT INTO years (id, name, opened) VALUES (2018, 'Módny plagát', TRUE);
9`;
10
11const functions = `
12  CREATE FUNCTION delete_expired() RETURNS TRIGGER
13    LANGUAGE plpgsql
14      AS $$
15        BEGIN
16          DELETE FROM keys WHERE expires_at < CURRENT_TIMESTAMP;
17          RETURN NULL;
18        END;
19      $$;
20
21  CREATE FUNCTION validate_key(id CHARACTER VARYING, email CHARACTER VARYING) RETURNS BOOLEAN
22    LANGUAGE sql
23      AS $$
24        SELECT (EXISTS (SELECT 1 FROM keys WHERE id = $1 AND email = $2 AND expires_at > CURRENT_TIMESTAMP));
25      $$;
26
27  CREATE TRIGGER delete_expired BEFORE INSERT ON keys EXECUTE PROCEDURE delete_expired();
28`;
29
30(async () => {
31  try {
32    (await server(`
33      SET TIME ZONE 'UTC';
34      
35      DROP SCHEMA IF EXISTS public CASCADE;
36      CREATE SCHEMA public AUTHORIZATION marekkobida;
37    
38      CREATE TABLE keys (
39        id          CHARACTER VARYING (255)  NOT NULL,
40        created_at  TIMESTAMP                DEFAULT CURRENT_TIMESTAMP NOT NULL,
41        email       CHARACTER VARYING (255)  NOT NULL,
42        expires_at  TIMESTAMP                DEFAULT (CURRENT_TIMESTAMP + INTERVAL '4 MINUTES') NOT NULL
43      );
44
45      CREATE TABLE posters (
46        id          BIGSERIAL                NOT NULL,
47        approved    BOOLEAN                  DEFAULT FALSE NOT NULL,
48        created_at  TIMESTAMP                DEFAULT CURRENT_TIMESTAMP NOT NULL,
49        email       CHARACTER VARYING (255)  NOT NULL,
50        first_name  CHARACTER VARYING (255)  NOT NULL,
51        last_name   CHARACTER VARYING (255)  NOT NULL,
52        name        CHARACTER VARYING (255)  NOT NULL,
53        poster      CHARACTER VARYING (255)  NOT NULL,
54        school_id   BIGINT                   NOT NULL,
55        year_id     BIGINT                   NOT NULL
56      );
57  
58      CREATE TABLE schools (
59        id          BIGSERIAL                NOT NULL,
60        address     CHARACTER VARYING (255)  NOT NULL,
61        approved    BOOLEAN                  DEFAULT FALSE NOT NULL,
62        created_at  TIMESTAMP                DEFAULT CURRENT_TIMESTAMP NOT NULL,
63        name        CHARACTER VARYING (255)  NOT NULL
64      );
65  
66      CREATE TABLE years (
67        id          BIGINT                   NOT NULL,
68        created_at  TIMESTAMP                DEFAULT CURRENT_TIMESTAMP NOT NULL,
69        name        CHARACTER VARYING (255)  NOT NULL,
70        opened      BOOLEAN                  DEFAULT FALSE NOT NULL
71      );
72  
73      ALTER TABLE keys     ADD CONSTRAINT keys_id_email_pkey         PRIMARY KEY (id, email);
74      ALTER TABLE posters  ADD CONSTRAINT posters_email_year_id_key  UNIQUE (email, year_id);
75      ALTER TABLE posters  ADD CONSTRAINT posters_pkey               PRIMARY KEY (id);
76      ALTER TABLE schools  ADD CONSTRAINT schools_pkey               PRIMARY KEY (id);
77      ALTER TABLE years    ADD CONSTRAINT years_pkey                 PRIMARY KEY (id);
78  
79      ALTER TABLE posters  ADD CONSTRAINT posters_school_id_fkey     FOREIGN KEY (school_id)  REFERENCES schools (id)  ON DELETE CASCADE ON UPDATE CASCADE;
80      ALTER TABLE posters  ADD CONSTRAINT posters_year_id_fkey       FOREIGN KEY (year_id)    REFERENCES years (id)    ON DELETE CASCADE ON UPDATE CASCADE;
81    
82      ${functions}
83      
84      ${data}
85    `));
86
87    process.exit();
88  } catch (e) {
89    console.log(e);
90  }
91})();