-
Notifications
You must be signed in to change notification settings - Fork 542
/
prepare_data.sh
executable file
·77 lines (65 loc) · 1.69 KB
/
prepare_data.sh
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
#! /bin/bash
cd `dirname $0`
BASE_DIR=`pwd`
dropdb -p $PGPORT -h $PGHOST $DBNAME
createdb -p $PGPORT -h $PGHOST $DBNAME
psql -p $PGPORT -h $PGHOST $DBNAME -c "DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public;"
psql -p $PGPORT -h $PGHOST $DBNAME -c """
DROP TABLE IF EXISTS person CASCADE;
DROP TABLE IF EXISTS person_has_cancer CASCADE;
DROP TABLE IF EXISTS person_smokes CASCADE;
DROP TABLE IF EXISTS friends CASCADE;
CREATE TABLE person (
person_id bigint,
name text
);
CREATE TABLE person_has_cancer (
person_id bigint,
has_cancer boolean,
id bigint
);
CREATE TABLE person_smokes (
person_id bigint,
smokes boolean,
id bigint
);
CREATE TABLE friends (
person_id bigint,
friend_id bigint
);
"""
psql -p $PGPORT -h $PGHOST $DBNAME -c """
INSERT INTO person(person_id, name) VALUES
(1, 'Anna'),
(2, 'Bob'),
(3, 'Edward'),
(4, 'Frank'),
(5, 'Gary'),
(6, 'Helen')
;"""
psql -p $PGPORT -h $PGHOST $DBNAME -c """
INSERT INTO person_smokes(person_id, smokes) VALUES
(1, TRUE),
(2, NULL),
(3, TRUE),
(4, NULL),
(5, NULL),
(6, NULL)
;"""
psql -p $PGPORT -h $PGHOST $DBNAME -c """
INSERT INTO person_has_cancer(person_id, has_cancer) VALUES
(1, NULL),
(2, NULL),
(3, NULL),
(4, NULL),
(5, NULL),
(6, NULL)
;"""
psql -p $PGPORT -h $PGHOST $DBNAME -c """
INSERT INTO friends(person_id, friend_id) VALUES
(1, 2), (2, 1),
(1, 3), (3, 1),
(1, 4), (4, 1),
(3, 4), (4, 3),
(5, 6), (6, 5)
;"""