-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql_schema_postgres.sql
140 lines (120 loc) · 3.23 KB
/
sql_schema_postgres.sql
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
CREATE TABLE public.sources (
id SERIAL PRIMARY KEY,
source VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE public.providers (
id SERIAL PRIMARY KEY,
provider VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE public.statuses (
statusCode INTEGER PRIMARY KEY,
shortDescription VARCHAR(300) UNIQUE NOT NULL
);
INSERT INTO public.statuses (statusCode, shortDescription)
VALUES
(100, 'Continue'),
(101, 'Switching Protocols'),
(102, 'Processing'),
(103, 'Early Hints'),
(200, 'OK'),
(201, 'Created'),
(202, 'Accepted'),
(203, 'Non-Authoritative Information'),
(204, 'No Content'),
(205, 'Reset Content'),
(206, 'Partial Content'),
(207, 'Multi-Status'),
(208, 'Already Reported'),
(226, 'IM Used'),
(300, 'Multiple Choices'),
(301, 'Moved Permanently'),
(302, 'Found'),
(303, 'See Other'),
(304, 'Not Modified'),
(305, 'Use Proxy'),
(307, 'Temporary Redirect'),
(308, 'Permanent Redirect'),
(400, 'Bad Request'),
(401, 'Unauthorized'),
(402, 'Payment Required'),
(403, 'Forbidden'),
(404, 'Not Found'),
(405, 'Method Not Allowed'),
(406, 'Not Acceptable'),
(407, 'Proxy Authentication Required'),
(408, 'Request Timeout'),
(409, 'Conflict'),
(410, 'Gone'),
(411, 'Length Required'),
(412, 'Precondition Failed'),
(413, 'Payload Too Large'),
(414, 'URI Too Long'),
(415, 'Unsupported Media Type'),
(416, 'Range Not Satisfiable'),
(417, 'Expectation Failed'),
(418, 'Im a teapot'),
(421, 'Misdirected Request'),
(422, 'Unprocessable Entity'),
(423, 'Locked'),
(424, 'Failed Dependency'),
(425, 'Too Early'),
(426, 'Upgrade Required'),
(428, 'Precondition Required'),
(429, 'Too Many Requests'),
(431, 'Request Header Fields Too Large'),
(451, 'Unavailable For Legal Reasons'),
(500, 'Internal Server Error'),
(501, 'Not Implemented'),
(502, 'Bad Gateway'),
(503, 'Service Unavailable'),
(504, 'Gateway Timeout'),
(505, 'HTTP Version Not Supported'),
(506, 'Variant Also Negotiates'),
(507, 'Insufficient Storage'),
(508, 'Loop Detected'),
(510, 'Not Extended'),
(511, 'Network Authentication Required');
CREATE TABLE public.proxies (
id SERIAL PRIMARY KEY,
proxy VARCHAR(100) NOT NULL,
sourceId INTEGER REFERENCES public.sources(id),
priority INTEGER,
blocked BOOLEAN,
provider INTEGER REFERENCES public.providers(id),
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE public.statistics (
id SERIAL PRIMARY KEY,
proxyId INTEGER REFERENCES public.proxies(id),
counter INTEGER DEFAULT 0,
statusId INTEGER REFERENCES public.statuses(statusCode)
);
# Functions and triggers
# updating my timestamp automatically
CREATE OR REPLACE FUNCTION update_timestamp_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updatedAt := now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_timestamp_trigger
BEFORE INSERT OR UPDATE ON proxies
FOR EACH ROW
EXECUTE FUNCTION update_timestamp_column();
# function for update blocked values
CREATE OR REPLACE FUNCTION update_blocked_status() RETURNS VOID AS $$
BEGIN
UPDATE proxies AS t
SET blocked = FALSE
FROM (
SELECT DISTINCT ON (sourceid)
sourceid,
updatedat
FROM proxies
WHERE updatedat < NOW() - INTERVAL '5 minutes'
ORDER BY sourceid, updatedat DESC
) AS t2
WHERE t.sourceid = t2.sourceid;
END;
$$ LANGUAGE plpgsql;