poc-campaign-manager-backend/scripts/database/v1.sql at master · pryv/poc-campaign-manager-backend · 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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS versions (
  version INTEGER PRIMARY_KEY
);
INSERT INTO versions (
  version
) VALUES (
  1
);

ALTER TABLE campaigns RENAME TO TempOldTable;
CREATE TABLE campaigns (
  campaign_id STRING PRIMARY_KEY,
  title text NOT NULL,
  pryv_app_id text,
  description text NOT NULL,
  permissions text not NULL,
  created integer NOT NULL,
  modified integer NOT NULL,
  status string NOT NULL,
  user_id string NOT NULL
);
INSERT INTO campaigns (
  campaign_id,
  title,
  pryv_app_id,
  description,
  permissions,
  created,
  modified,
  status,
  user_id
) SELECT
  campaign_id,
  title,
  pryv_app_id,
  description,
  permissions,
  created,
  created,
  "created",
  user_id
 FROM TempOldTable;
DROP TABLE TempOldTable;

ALTER TABLE invitations RENAME TO TempOldTable;
CREATE TABLE invitations (
  invitation_id string PRIMARY_KEY,
  access_token string,
  status string NOT NULL,
  created integer NOT NULL,
  modified integer NOT NULL,
  campaign_id string NOT NULL,
  requester_id string NOT NULL,
  requestee_id string NOT NULL,
  head_id string
);
INSERT INTO invitations (
  invitation_id,
  access_token,
  status,
  created,
  modified,
  campaign_id,
  requester_id,
  requestee_id,
  head_id
) SELECT
  invitation_id,
  access_token,
  status,
  created,
  modified,
  campaign_id,
  requester_id,
  requestee_id,
  NULL
 FROM TempOldTable;
DROP TABLE TempOldTable;

END TRANSACTION;