btimetable/initDB.cpp at main · i1bro/btimetable · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
#include <iostream>
#include <pqxx/pqxx>

int main() {
    try {
        pqxx::connection C(
            "host=retired.tk "
            "port=58974 "
            "user=postgres "
            "password=T38ssGHzjcRviche7ex "
            "dbname=postgres");
        std::cout << "Connected to " << C.dbname() << std::endl;
        pqxx::work W{C};

        W.exec(
            "DROP TABLE IF EXISTS companies CASCADE;"
            "DROP TABLE IF EXISTS clients CASCADE;"
            "DROP TABLE IF EXISTS employees CASCADE;"
            "DROP TABLE IF EXISTS orders CASCADE;"
            "DROP TABLE IF EXISTS client_accounts CASCADE;"
            "DROP TABLE IF EXISTS company_accounts CASCADE;");

        W.exec(
            "CREATE TABLE companies("
            "id BIGINT GENERATED ALWAYS AS IDENTITY,"
            "name VARCHAR(255) NOT NULL,"
            "rating_sum BIGINT DEFAULT 0,"
            "rating_cnt BIGINT DEFAULT 0,"
            "PRIMARY KEY(id)"
            ")");

        W.exec(
            "CREATE TABLE clients("
            "id BIGINT GENERATED ALWAYS AS IDENTITY,"
            "full_name VARCHAR(255) NOT NULL,"
            "phone_number VARCHAR(16),"
            "email varchar(100),"
            "PRIMARY KEY(id)"
            ")");

        W.exec(
            "CREATE TABLE employees("
            "id BIGINT GENERATED ALWAYS AS IDENTITY,"
            "company_id BIGINT NOT NULL,"
            "full_name VARCHAR(255) NOT NULL,"
            "rating_sum BIGINT DEFAULT 0,"
            "rating_cnt BIGINT DEFAULT 0,"
            "is_deleted BOOLEAN DEFAULT FALSE,"
            "PRIMARY KEY(id),"
            "CONSTRAINT fk_company "
            "FOREIGN KEY(company_id) "
            "REFERENCES companies(id) "
            "ON DELETE CASCADE"
            ")");

        W.exec(
            "CREATE TABLE orders("
            "id BIGINT GENERATED ALWAYS AS IDENTITY,"
            "company_id BIGINT NOT NULL,"
            "title VARCHAR(255) NOT NULL,"
            "time_start BIGINT NOT NULL,"
            "duration BIGINT NOT NULL,"
            "client_id BIGINT DEFAULT NULL,"
            "employee_id BIGINT NOT NULL,"
            "status INT DEFAULT 0,"
            "rating INT DEFAULT 0,"
            "PRIMARY KEY(id),"
            "CONSTRAINT fk_company "
            "FOREIGN KEY(company_id) "
            "REFERENCES companies(id) "
            "ON DELETE CASCADE,"
            "CONSTRAINT fk_client "
            "FOREIGN KEY(client_id) "
            "REFERENCES clients(id) "
            "ON DELETE SET NULL,"
            "CONSTRAINT fk_employee "
            "FOREIGN KEY(employee_id) "
            "REFERENCES employees(id) "
            "ON DELETE CASCADE"
            ")");

        W.exec(
            "CREATE TABLE client_accounts("
            "phone_number VARCHAR(16) NOT NULL,"
            "password VARCHAR(255) NOT NULL,"
            "client_id BIGINT NOT NULL,"
            "PRIMARY KEY(phone_number),"
            "CONSTRAINT fk_client "
            "FOREIGN KEY(client_id) "
            "REFERENCES clients(id) "
            "ON DELETE CASCADE"
            ")");

        W.exec(
            "CREATE TABLE company_accounts("
            "phone_number VARCHAR(16) NOT NULL,"
            "password VARCHAR(255) NOT NULL,"
            "company_id BIGINT NOT NULL,"
            "PRIMARY KEY(phone_number),"
            "CONSTRAINT fk_company "
            "FOREIGN KEY(company_id) "
            "REFERENCES companies(id) "
            "ON DELETE CASCADE"
            ")");

        W.commit();
        std::cout << "OK.\n";
    } catch (std::exception const &e) {
        std::cerr << e.what() << '\n';
        return 1;
    }
    return 0;
}