· 6 years ago · Jun 21, 2019, 03:22 AM
1-- Numbers table creation
2DROP temporary TABLE IF EXISTS tally;
3
4CREATE temporary TABLE tally
5 (
6 n INT NOT NULL auto_increment PRIMARY KEY
7 );
8
9INSERT INTO tally
10 (n)
11SELECT NULL
12FROM (SELECT 0 AS N
13 UNION ALL
14 SELECT 1
15 UNION ALL
16 SELECT 2
17 UNION ALL
18 SELECT 3
19 UNION ALL
20 SELECT 4
21 UNION ALL
22 SELECT 5
23 UNION ALL
24 SELECT 6
25 UNION ALL
26 SELECT 7
27 UNION ALL
28 SELECT 8
29 UNION ALL
30 SELECT 9) a,
31 (SELECT 0 AS N
32 UNION ALL
33 SELECT 1
34 UNION ALL
35 SELECT 2
36 UNION ALL
37 SELECT 3
38 UNION ALL
39 SELECT 4
40 UNION ALL
41 SELECT 5
42 UNION ALL
43 SELECT 6
44 UNION ALL
45 SELECT 7
46 UNION ALL
47 SELECT 8
48 UNION ALL
49 SELECT 9) b;
50
51-- Split users by comma from first table
52DROP temporary TABLE IF EXISTS tmppermissions2;
53
54CREATE temporary TABLE tmppermissions2
55 (
56 userid VARCHAR(255) NOT NULL,
57 permissions TEXT NOT NULL
58 );
59
60INSERT INTO tmppermissions2
61SELECT userid,
62 permissions
63FROM (SELECT Substring_index(Substring_index(t.userids, ',', tally.n), ',', -1
64 )
65 userId,
66 t.permissions
67 permissions
68 FROM tally
69 INNER JOIN tmppermissions t
70 ON Char_length(t.userids) - Char_length(
71 REPLACE(t.userids, ',',
72 '')) >=
73 tally.n - 1
74 ORDER BY n) AS split;
75
76-- Gets the users with the same permissions
77DROP temporary TABLE IF EXISTS sharedprofiles;
78
79CREATE temporary TABLE sharedprofiles
80 (
81 userids VARCHAR(255) NOT NULL,
82 permissions TEXT NOT NULL,
83 profileid INT(11)
84 );
85
86INSERT INTO sharedprofiles
87SELECT Group_concat(userid),
88 permissions,
89 NULL
90FROM tmppermissions2
91WHERE userid NOT IN (SELECT split.userid
92 FROM (SELECT Substring_index(Substring_index(r.userids,
93 ',',
94 t.n), ',', -1)
95 userId
96 FROM tally t
97 INNER JOIN tmppermissions r
98 ON Char_length(r.userids)
99 - Char_length(
100 REPLACE(r.userids, ',',
101 '')) >=
102 t.n - 1
103 WHERE Position(',' IN r.userids) > 0
104 ORDER BY n) AS split
105 WHERE split.userid IN (SELECT *
106 FROM (SELECT Group_concat(userid
107 ORDER
108 BY userid ASC)
109 AS
110 users
111 FROM
112 tmpcurrentresources2
113 GROUP BY resourceid,
114 sectionid
115 ORDER BY users) b
116 WHERE Position(',' IN b.users) =
117 0))
118GROUP BY permissions
119ORDER BY Group_concat(userid);
120
121-- Gets the users with specific permissions
122DROP temporary TABLE IF EXISTS singleprofiles;
123
124CREATE temporary TABLE singleprofiles
125 (
126 userid VARCHAR(255) NOT NULL,
127 permissions TEXT NOT NULL,
128 profileid INT(11)
129 );
130
131INSERT INTO singleprofiles
132SELECT userid,
133 permissions,
134 NULL
135FROM tmppermissions2
136WHERE userid IN (SELECT split.userid
137 FROM (SELECT Substring_index(Substring_index(r.userids, ',',
138 t.n),
139 ',', -1)
140 userId
141 FROM tally t
142 INNER JOIN tmppermissions r
143 ON Char_length(r.userids) -
144 Char_length(
145 REPLACE(r.userids, ',',
146 '')) >=
147 t.n - 1
148 WHERE Position(',' IN r.userids) > 0
149 ORDER BY n) AS split
150 WHERE split.userid IN (SELECT *
151 FROM (SELECT Group_concat(userid
152 ORDER BY
153 userid ASC)
154 AS
155 users
156 FROM tmpcurrentresources2
157 GROUP BY resourceid,
158 sectionid
159 ORDER BY users) b
160 WHERE Position(',' IN b.users) = 0))
161ORDER BY userid;
162
163-- Merge the results
164SELECT *
165FROM sharedprofiles
166UNION
167SELECT *
168FROM singleprofiles;