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;