]> git.stg.codes - stg.git/blob - projects/stargazer/inst/var/01-alter-02.postgresql.sql
Full month stats patches added (derived from GTS version)
[stg.git] / projects / stargazer / inst / var / 01-alter-02.postgresql.sql
1 /*
2  *  DB migration from v01 to v02 (postgres)
3  */
4
5 BEGIN;
6
7 CREATE TABLE tb_month_stats
8 (
9     pk_month_stats BIGSERIAL PRIMARY KEY,
10     fk_user INTEGER NOT NULL,
11     stats_date DATE NOT NULL,
12     cash dm_money,
13     free_mb dm_money,
14     last_activity_time TIMESTAMP NOT NULL,
15     last_cash_add dm_money,
16     last_cash_add_time TIMESTAMP NOT NULL,
17     passive_time INTEGER NOT NULL,
18
19     FOREIGN KEY (fk_user)
20         REFERENCES tb_users (pk_user)
21         ON DELETE CASCADE,
22     UNIQUE (fk_user, stats_date)
23 );
24
25 CREATE TABLE tb_month_stats_traffic
26 (
27     pk_month_stat_traffic BIGSERIAL PRIMARY KEY,
28     fk_month_stats BIGINT NOT NULL,
29     dir_num SMALLINT NOT NULL,
30     download BIGINT NOT NULL,
31     upload BIGINT NOT NULL,
32
33     FOREIGN KEY (fk_month_stats)
34         REFERENCES tb_month_stats (pk_month_stats)
35         ON DELETE CASCADE,
36     UNIQUE (fk_month_stats, dir_num)
37 );
38
39 INSERT INTO tb_month_stats
40     (fk_user,
41      stats_date,
42      cash,
43      free_mb,
44      last_activity_time,
45      last_cash_add,
46      last_cash_add_time,
47      passive_time)
48 SELECT fk_user,
49        stats_date,
50        0,
51        0,
52        '1970-01-01 00:00:00'::TIMESTAMP WITHOUT TIME ZONE,
53        0,
54        '1970-01-01 00:00:00'::TIMESTAMP WITHOUT TIME ZONE,
55        0
56 FROM tb_stats_traffic
57 WHERE date_trunc('month', stats_date) < date_trunc('month', 'now'::DATE)
58 GROUP BY fk_user,
59          stats_date;
60
61 INSERT INTO tb_month_stats_traffic
62     (fk_month_stats,
63      dir_num,
64      download,
65      upload)
66 SELECT s.pk_month_stats,
67        t.dir_num,
68        t.download,
69        t.upload
70 FROM tb_stats_traffic AS t
71 LEFT JOIN tb_month_stats AS s
72     ON s.fk_user = t.fk_user AND
73        s.stats_date = t.stats_date
74 WHERE date_trunc('month', t.stats_date) < date_trunc('month', 'now'::DATE);
75
76 DROP FUNCTION sp_add_stats_traffic ( dm_name, DATE, SMALLINT, BIGINT, BIGINT );
77
78 CREATE FUNCTION sp_add_stats_traffic (_login dm_name,
79                                       _dir_num SMALLINT,
80                                       _upload BIGINT,
81                                       _download BIGINT)
82 RETURNS INTEGER
83 AS $$
84 DECLARE
85     _pk_user INTEGER;
86 BEGIN
87     SELECT pk_user INTO _pk_user
88         FROM tb_users
89         WHERE name = _login;
90
91     IF _pk_user IS NULL THEN
92         RAISE EXCEPTION 'User % not found', _login;
93         RETURN -1;
94     END IF;
95
96     UPDATE tb_stats_traffic SET
97         upload = _upload,
98         download = _download
99     WHERE fk_user = _pk_user AND
100           dir_num = _dir_num;
101
102     IF NOT FOUND THEN
103         INSERT INTO tb_stats_traffic
104             (fk_user,
105              dir_num,
106              upload,
107              download)
108         VALUES
109             (_pk_user,
110              _dir_num,
111              _upload,
112              _download);
113     END IF;
114
115     RETURN 1;
116 END;
117 $$ LANGUAGE plpgsql;
118
119 DELETE FROM tb_stats_traffic WHERE date_trunc('month', stats_date) < date_trunc('month', 'now'::DATE);
120 ALTER TABLE tb_stats_traffic DROP stats_date;
121 ALTER TABLE tb_stats_traffic ADD UNIQUE (fk_user, dir_num);
122
123
124 CREATE FUNCTION sp_add_month_stats (_login dm_name,
125                                     _stats_date DATE,
126                                     _cash dm_money,
127                                     _free_mb dm_money,
128                                     _last_activity_time TIMESTAMP,
129                                     _last_cash_add dm_money,
130                                     _last_cash_add_time TIMESTAMP,
131                                     _passive_time INTEGER)
132 RETURNS BIGINT
133 AS $$
134 DECLARE
135     _pk_user INTEGER;
136     _pk_month_stats BIGINT;
137 BEGIN
138     SELECT pk_user INTO _pk_user
139         FROM tb_users
140         WHERE name = _login;
141
142     IF _pk_user IS NULL THEN
143         RAISE EXCEPTION 'User % not found', _login;
144         RETURN -1;
145     END IF;
146
147     INSERT INTO tb_month_stats
148         (fk_user,
149          stats_date,
150          cash,
151          free_mb,
152          last_activity_time,
153          last_cash_add,
154          last_cash_add_time,
155          passive_time)
156     VALUES
157         (_pk_user,
158          _stats_date,
159          _cash,
160          _free_mb,
161          _last_activity_time,
162          _last_cash_add,
163          _last_cash_add_time,
164          _passive_time);
165
166     SELECT CURRVAL('tb_month_stats_pk_month_stats_seq') INTO _pk_month_stats;
167
168     RETURN _pk_month_stats;
169 END;
170 $$ LANGUAGE plpgsql;
171
172 CREATE FUNCTION sp_add_month_stats_traffic (_pk_month_stats BIGINT,
173                                             _dir_num SMALLINT,
174                                             _upload BIGINT,
175                                             _download BIGINT)
176 RETURNS INTEGER
177 AS $$
178 BEGIN
179     INSERT INTO tb_month_stats_traffic
180         (fk_month_stats,
181          dir_num,
182          upload,
183          download)
184     VALUES
185         (_pk_month_stats,
186          _dir_num,
187          _upload,
188          _download);
189
190     RETURN 1;
191 END;
192 $$ LANGUAGE plpgsql;
193
194 UPDATE tb_info SET version = 7;
195
196 COMMIT;