learn-postgresql/schema.sql at master · dwyl/learn-postgresql · 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
CREATE TABLE IF NOT EXISTS "people" (
  "inserted_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	"id" SERIAL PRIMARY KEY,
	"name" VARCHAR(50) DEFAULT NULL,
	"username" VARCHAR(50) NOT NULL,
	"bio" VARCHAR(255) DEFAULT NULL,
	"worksfor" VARCHAR(50) DEFAULT NULL,
	"uid" INT NOT NULL, -- the person's GitHub uid e.g: 4185328
	"location" VARCHAR(100) DEFAULT NULL,
	"website" VARCHAR(255) DEFAULT NULL,
	"stars" INT DEFAULT 0,
	"followers" INT DEFAULT 0,
	"following" INT DEFAULT 0,
	"contribs" INT DEFAULT 0,
	"recent_activity" INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS "orgs" (
	"inserted_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	"id" SERIAL PRIMARY KEY,
	"name" VARCHAR(50) DEFAULT NULL,
	"url" VARCHAR(50),
	"description" VARCHAR(255) DEFAULT NULL,
	"location" VARCHAR(50) DEFAULT NULL,
	"website" VARCHAR(255) DEFAULT NULL,
	"email" VARCHAR(255) DEFAULT NULL,
	"pcount" INT DEFAULT 0,
	"uid" INT NOT NULL
);

CREATE TABLE IF NOT EXISTS "repos" (
	"inserted_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	"id" SERIAL PRIMARY KEY,
	"url" VARCHAR(255) NOT NULL, -- know what the char limit is for a repo name?
	"description" TEXT DEFAULT NULL,
	"website" VARCHAR(255) DEFAULT NULL,
	"watchers" INT DEFAULT 0,
	"stars" INT DEFAULT 0,
	"forks" INT DEFAULT 0,
	"commits" INT DEFAULT 0,
	"langs" VARCHAR(255) DEFAULT NULL,
	"tags" TEXT DEFAULT NULL,
	"person_id" INT REFERENCES people (id), -- can be NULL if repo belongs to org.
	"org_id" INT REFERENCES orgs (id) -- this can be NULL if repo is personal.
);

CREATE TABLE IF NOT EXISTS "logs" (
	"id" SERIAL PRIMARY KEY,
	"inserted_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	"url" VARCHAR(255) NOT NULL,
	"next_page" VARCHAR(255) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS "relationships" (
	"inserted_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	"id" SERIAL PRIMARY KEY,
	"person_id" INT REFERENCES people (id) DEFAULT NULL,
	"leader_id" INT REFERENCES people (id) DEFAULT NULL,
	"org_id" INT REFERENCES orgs (id) DEFAULT NULL,
	"repo_id" INT REFERENCES repos (id) DEFAULT NULL
);