19dFGpJa

1const pg = require('pg');
2const dotenv = require('dotenv').config()
3const connectionString = process.env.DATABASE_URL || 'admintechfugees://admintechfugees:CeP$787@localhost:5432/dbbasefugees';
4
5const client = new pg.Client(connectionString);
6client.connect();
7
8const queryUser = client.query(
9  'CREATE TABLE IF NOT EXISTS Users('
10  + 'user_id SERIAL,'
11  + 'username VARCHAR(40) NOT NULL UNIQUE,'
12  + 'name VARCHAR(40) NOT NULL,'
13  + 'firstname VARCHAR(40) NOT NULL,'
14  + 'email VARCHAR(60) UNIQUE,'
15  + 'location Varchar(60) NOT NULL,'
16  + 'CONSTRAINT pk_user PRIMARY KEY (user_id))',
17
18  (err, res) => {
19    if (err) throw err
20    console.log("TABLE USERS : " + res)
21    client.end()
22  });
23
24const queryProject = client.query(
25  'CREATE TABLE IF NOT EXISTS Projects('
26  + 'project_id SERIAL,'
27  + 'name VARCHAR(40) NOT NULL,'
28  + 'CONSTRAINT pk_project PRIMARY KEY (project_id))',
29
30  (err, res) => {
31    if (err) throw err
32    console.log("TABLE PROJECTS : " + res)
33    client.end()
34  });
35
36const queryOrganization = client.query(
37  'CREATE TABLE IF NOT EXISTS Organizations('
38  + 'organization_id SERIAL,'
39  + 'name VARCHAR(40) NOT NULL UNIQUE,'
40  + 'website VARCHAR(100),'
41  + 'user_id INTEGER,'
42  + 'project_id INTEGER,'
43  + 'CONSTRAINT pk_organization PRIMARY KEY (organization_id),'
44  + 'CONSTRAINT fk_organization_user FOREIGN KEY (user_id) REFERENCES Users(user_id),'
45  + 'CONSTRAINT fk_organization_project FOREIGN KEY (project_id) REFERENCES Projects(project_id))',
46
47  (err, res) => {
48    if (err) throw err
49    console.log("TABLE ORGANIZATIONS : " +res)
50    client.end()
51  });
52
53const queryChallenge = client.query(
54  'CREATE TABLE IF NOT EXISTS Challenges('
55  + 'challenge_id SERIAL,'
56  + 'name VARCHAR(40) NOT NULL,'
57  + 'description VARCHAR(600) NOT NULL,'
58  + 'organization_id INTEGER,'
59  + 'CONSTRAINT pk_challenge PRIMARY KEY (challenge_id),'
60  + 'CONSTRAINT fk_challenge_organization FOREIGN KEY (organization_id) REFERENCES Organizations(organization_id))',
61
62  (err, res) => {
63    if (err) throw err
64    console.log("TABLE CHALLENGES : " +res)
65    client.end()
66  });
67
68const queryResponse = client.query(
69  'CREATE TABLE IF NOT EXISTS Responses('
70  + 'response_id SERIAL,'
71  + 'name VARCHAR(40) NOT NULL,'
72  + 'project_id INTEGER,'
73  + 'CONSTRAINT fk_response_project FOREIGN KEY (project_id) REFERENCES Projects(project_id))',
74
75  (err, res) => {
76    if (err) throw err
77    console.log("TABLE RESPONSES : " +res)
78    client.end()
79  });
80
81queryUser.on('end', () => { client.end(); });
82queryProject.on('end', () => { client.end(); });
83queryOrganization.on('end', () => { client.end(); });
84queryChallenge.on('end', () => { client.end(); });
85queryResponse.on('end', () => { client.end(); });