-
Notifications
You must be signed in to change notification settings - Fork 381
/
upgrade-1.7-1.7.1.sql
122 lines (117 loc) · 6.1 KB
/
upgrade-1.7-1.7.1.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
CREATE OR REPLACE VIEW "cards_listing" AS
SELECT cards.id,
to_char(cards.created, 'YYYY-MM-DD"T"HH24:MI:SS'::text) AS created,
to_char(cards.modified, 'YYYY-MM-DD"T"HH24:MI:SS'::text) AS modified,
cards.board_id,
cards.list_id,
cards.name,
cards.description,
to_char(cards.due_date, 'YYYY-MM-DD"T"HH24:MI:SS'::text) AS due_date,
to_date(to_char(cards.due_date, 'YYYY/MM/DD'::text), 'YYYY/MM/DD'::text) AS to_date,
cards."position",
(cards.is_archived)::integer AS is_archived,
cards.attachment_count,
cards.checklist_count,
cards.checklist_item_count,
cards.checklist_item_completed_count,
cards.label_count,
cards.cards_user_count,
cards.cards_subscriber_count,
cards.card_voter_count,
cards.activity_count,
cards.user_id,
cards.name AS title,
cards.due_date AS start,
cards.due_date AS "end",
( SELECT array_to_json(array_agg(row_to_json(cc.*))) AS array_to_json
FROM ( SELECT checklists_listing.id,
checklists_listing.created,
checklists_listing.modified,
checklists_listing.user_id,
checklists_listing.card_id,
checklists_listing.name,
checklists_listing.checklist_item_count,
checklists_listing.checklist_item_completed_count,
checklists_listing."position",
checklists_listing.checklists_items
FROM checklists_listing checklists_listing
WHERE (checklists_listing.card_id = cards.id)
ORDER BY checklists_listing.id) cc) AS cards_checklists,
( SELECT array_to_json(array_agg(row_to_json(cc.*))) AS array_to_json
FROM ( SELECT cards_users_listing.username,
cards_users_listing.profile_picture_path,
cards_users_listing.id,
cards_users_listing.created,
cards_users_listing.modified,
cards_users_listing.card_id,
cards_users_listing.user_id,
cards_users_listing.initials,
cards_users_listing.full_name,
cards_users_listing.email
FROM cards_users_listing cards_users_listing
WHERE (cards_users_listing.card_id = cards.id)
ORDER BY cards_users_listing.id) cc) AS cards_users,
( SELECT array_to_json(array_agg(row_to_json(cv.*))) AS array_to_json
FROM ( SELECT card_voters_listing.id,
card_voters_listing.created,
card_voters_listing.modified,
card_voters_listing.user_id,
card_voters_listing.card_id,
card_voters_listing.username,
card_voters_listing.role_id,
card_voters_listing.profile_picture_path,
card_voters_listing.initials,
card_voters_listing.full_name
FROM card_voters_listing card_voters_listing
WHERE (card_voters_listing.card_id = cards.id)
ORDER BY card_voters_listing.id) cv) AS cards_voters,
( SELECT array_to_json(array_agg(row_to_json(cs.*))) AS array_to_json
FROM ( SELECT cards_subscribers.id,
to_char(cards_subscribers.created, 'YYYY-MM-DD"T"HH24:MI:SS'::text) AS created,
to_char(cards_subscribers.modified, 'YYYY-MM-DD"T"HH24:MI:SS'::text) AS modified,
cards_subscribers.card_id,
cards_subscribers.user_id,
(cards_subscribers.is_subscribed)::integer AS is_subscribed
FROM card_subscribers cards_subscribers
WHERE (cards_subscribers.card_id = cards.id)
ORDER BY cards_subscribers.id) cs) AS cards_subscribers,
( SELECT array_to_json(array_agg(row_to_json(cl.*))) AS array_to_json
FROM ( SELECT cards_labels.label_id,
cards_labels.id,
cards_labels.card_id,
cards_labels.list_id,
cards_labels.board_id,
cards_labels.name,
cards_labels.color
FROM cards_labels_listing cards_labels
WHERE (cards_labels.card_id = cards.id)
ORDER BY cards_labels.id) cl) AS cards_labels,
cards.comment_count,
u.username,
b.name AS board_name,
l.name AS list_name,
cards.custom_fields,
cards.color,
cards.due_date AS notification_due_date,
cards.is_due_date_notification_sent,
cards.archived_date,
to_char(( SELECT activities.created
FROM activities
WHERE ((activities.card_id = cards.id) AND ((activities.type)::text = 'move_card'::text))
ORDER BY activities.id DESC
LIMIT 1), 'YYYY-MM-DD"T"HH24:MI:SS'::text) AS list_moved_date,
u.full_name AS card_created_user,
cards.cover_image_id,
(l.is_archived)::integer AS list_is_archived
FROM (((cards cards
LEFT JOIN users u ON ((u.id = cards.user_id)))
LEFT JOIN boards b ON ((b.id = cards.board_id)))
LEFT JOIN lists l ON ((l.id = cards.list_id)));
DELETE FROM "acl_links_roles" WHERE acl_link_id = (select id from acl_links where slug='user_activities_listing');
DELETE FROM "acl_links" WHERE slug = 'user_activities_listing';
SELECT pg_catalog.setval('acl_links_id_seq', (SELECT MAX(id) FROM acl_links), true);
INSERT INTO "acl_links" ("created", "modified", "name", "url", "method", "slug", "group_id", "is_user_action", "is_guest_action", "is_admin_action", "is_hide", "is_default") values ('now()', 'now()', 'User activities', '/users/?/activities', 'GET', 'user_activities_listing', '2', '1', '0', '0', '0', 't');
SELECT pg_catalog.setval('acl_links_roles_roles_id_seq', (SELECT MAX(id) FROM acl_links_roles), true);
INSERT INTO "acl_links_roles" ("created", "modified", "acl_link_id", "role_id") VALUES
(now(), now(), (select id from acl_links where slug='user_activities_listing'), '1'),
(now(), now(), (select id from acl_links where slug='user_activities_listing'), '2');