postgreSQL-client-electron/index.html at main · Opyd/postgreSQL-client-electron · 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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
<!DOCTYPE html>
<html lang="pl">
    <head>
        <meta charset="UTF-8" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <title>Portal muzyczny</title>
        <link
            href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css"
            rel="stylesheet"
            integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC"
            crossorigin="anonymous"
        />
        <script
            src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"
            integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM"
            crossorigin="anonymous"
        ></script>

        <style>
            h1{
                text-align: center;
            }
            .app-wrapper{
                display: flex;
                flex-direction: row;
                align-items: stretch;
                justify-content: space-between;
            }
            .app-wrapper *{
                margin: 5px;
                width: 50%;
            }
        </style>
    </head>
    <body>
        <h1>Portal muzyczny</h1>
        <p id="paragraph"></p>
        <div class="app-wrapper">
            <button onclick="createTable()" class="btn btn-success">Stwórz bazę</button>
            <button onclick="endConnection()" class="btn btn-danger">Zakończ połączenie</button>
        </div>
        <script>
            const { Client } = require("pg");
            const client = new Client({
                host: "127.0.0.1",
                port: 5432,
                database: "baza",
                user: "user",
                password: "password",
            });
            client.connect();
            function createTable() {
                const query = `
            CREATE TABLE Kraj_pochodzenia(
                ID_kraju INT GENERATED ALWAYS AS IDENTITY,
                Nazwa VARCHAR(50) NOT NULL,
                PRIMARY KEY(ID_kraju)
            );
            CREATE TABLE Gatunek(
                ID_gatunku INT GENERATED ALWAYS AS IDENTITY,
                Nazwa_gatunku VARCHAR(50),
                PRIMARY KEY(ID_gatunku)
            );
            CREATE Table Wydawca(
                ID_Wydawcy INT GENERATED ALWAYS AS IDENTITY,
                Nazwa_wydawcy VARCHAR(50),
                PRIMARY Key(ID_Wydawcy)
            );
            CREATE TABLE Album(
                ID_Albumu INT UNIQUE GENERATED ALWAYS AS IDENTITY,
                Tytul_albumu VARCHAR(50),
                ID_Wydawcy INT references "wydawca" (id_wydawcy),
                ID_kraj_pochodzenia INT references "kraj_pochodzenia"(id_kraju),
                ID_gatunku INT references "gatunek"(id_gatunku),
                Data_wydania timestamp
            );
            CREATE TABLE Wykonawca(
                ID_wykonawcy INT GENERATED ALWAYS AS IDENTITY,
                Nazwa_wykonawcy VARCHAR ( 50 ) NOT NULL,
                Opis VARCHAR (100),
                PRIMARY KEY(ID_wykonawcy)
            );
            CREATE TABLE Utwor(
                ID_utworu INT GENERATED ALWAYS AS IDENTITY,
                ID_autora INT references "wykonawca" (id_wykonawcy),
                ID_album INT references "album" (id_albumu),
                Tytul_utworu VARCHAR(50) NOT NULL,
                Czas_trwania INT,
                PRIMARY KEY(ID_utworu)
            );
            CREATE Table Lista_uzytkownika(
                ID_listy INT GENERATED ALWAYS AS IDENTITY,
                ID_utworu INT references "utwor" (id_utworu),
                PRIMARY KEY(ID_listy)
            );
            CREATE TABLE Uzytkownik(
                ID_uzytkownika INT GENERATED ALWAYS AS IDENTITY,
                ID_listy_uzytkownika INT references "lista_uzytkownika" (id_listy),
                Login_uzytkownika VARCHAR(30) NOT NULL,
                Haslo_uzytkownika VARCHAR(30) NOT NULL,
                PRIMARY KEY(ID_uzytkownika)
            );
            BEGIN;


CREATE TABLE IF NOT EXISTS album
(
    id_albumu integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    tytul_albumu character varying(50) COLLATE pg_catalog."default",
    id_wydawcy integer,
    id_kraj_pochodzenia integer,
    id_gatunku integer,
    data_wydania timestamp without time zone
);

CREATE TABLE IF NOT EXISTS gatunek
(
    id_gatunku integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    nazwa_gatunku character varying(50) COLLATE pg_catalog."default",
    CONSTRAINT gatunek_pkey PRIMARY KEY (id_gatunku)
);

CREATE TABLE IF NOT EXISTS kraj_pochodzenia
(
    id_kraju integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    nazwa character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT kraj_pochodzenia_pkey PRIMARY KEY (id_kraju)
);

CREATE TABLE IF NOT EXISTS lista_uzytkownika
(
    id_listy integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    id_utworu integer,
    CONSTRAINT lista_uzytkownika_pkey PRIMARY KEY (id_listy)
);

CREATE TABLE IF NOT EXISTS utwor
(
    id_utworu integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    id_autora integer,
    id_album integer,
    tytul_utworu character varying(50) COLLATE pg_catalog."default" NOT NULL,
    czas_trwania integer,
    CONSTRAINT utwor_pkey PRIMARY KEY (id_utworu)
);

CREATE TABLE IF NOT EXISTS uzytkownik
(
    id_uzytkownika integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    id_listy_uzytkownika integer,
    login_uzytkownika character varying(30) COLLATE pg_catalog."default" NOT NULL,
    haslo_uzytkownika character varying(30) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT uzytkownik_pkey PRIMARY KEY (id_uzytkownika)
);

CREATE TABLE IF NOT EXISTS wydawca
(
    id_wydawcy integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    nazwa_wydawcy character varying(50) COLLATE pg_catalog."default",
    CONSTRAINT wydawca_pkey PRIMARY KEY (id_wydawcy)
);

CREATE TABLE IF NOT EXISTS wykonawca
(
    id_wykonawcy integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    nazwa_wykonawcy character varying(50) COLLATE pg_catalog."default" NOT NULL,
    opis character varying(100) COLLATE pg_catalog."default",
    CONSTRAINT wykonawca_pkey PRIMARY KEY (id_wykonawcy)
);

ALTER TABLE IF EXISTS album
    ADD FOREIGN KEY (id_wydawcy)
    REFERENCES wydawca (id_wydawcy) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS album
    ADD FOREIGN KEY (id_kraj_pochodzenia)
    REFERENCES kraj_pochodzenia (id_kraju) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS album
    ADD FOREIGN KEY (id_gatunku)
    REFERENCES gatunek (id_gatunku) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS lista_uzytkownika
    ADD FOREIGN KEY (id_utworu)
    REFERENCES utwor (id_utworu) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS utwor
    ADD FOREIGN KEY (id_album)
    REFERENCES album (id_albumu) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS utwor
    ADD FOREIGN KEY (id_autora)
    REFERENCES wykonawca (id_wykonawcy) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS uzytkownik
    ADD FOREIGN KEY (id_listy_uzytkownika)
    REFERENCES lista_uzytkownika (id_listy) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;
END;
        `;
                const checkexist = `
            SELECT EXISTS (
            SELECT FROM information_schema.tables
            WHERE  table_schema = 'public'
            AND    table_name   = 'utwor'
            );`;
                client.query(checkexist, (err, res) => {
                    if (res["rows"][0]["exists"]) {
                        alert("Baza już istnieje");
                    } else {
                        client.query(query, (err, res) => {
                            if (err) {
                                return;
                            }
                            alert("Baza stworzona");
                        });
                    }
                });

                // client.query(query2, (err,res) =>{
                //     if(err){
                //         return;
                //     }
                //     console.log("Table Created");
                // })
            }
            function endConnection() {
                client.end();
            }
            const p = document.getElementById("paragraph");
        </script>

    </body>
</html>