1 package datasql
2
3 const (
4 SelectAllCommands = "SELECT command_id, name FROM ea_rules_commands"
5 SelectAllPrivileges = "SELECT privilege_id, name FROM ea_rules_privileges"
6
7 SelectAllDefaultRules = `SELECT commands.name AS commandName,
8 privileges.name AS privilegeName,
9 commands.command_id,
10 privileges.privilege_id
11 FROM ea_rules_default rules
12 JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
13 JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id`
14 SelectDefaultRulesByCommandName = `SELECT commands.name AS commandName,
15 privileges.name AS privilegeName,
16 commands.command_id,
17 privileges.privilege_id
18 FROM ea_rules_default rules
19 JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
20 JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
21 WHERE commands.name = $1`
22 SelectPrivilegeByName = "SELECT privilege_id, name FROM ea_rules_privileges WHERE name = $1 LIMIT 1"
23 SelectPrivilegesByName = "SELECT privilege_id, name FROM ea_rules_privileges WHERE name = ANY ($1);"
24 SelectCommandByName = "SELECT command_id, name FROM ea_rules_commands WHERE name = $1 LIMIT 1"
25 SelectCommandsByName = "SELECT command_id, name FROM ea_rules_commands WHERE name = ANY ($1);"
26 InsertRuleDefault = `INSERT INTO ea_rules_default (command_id, privilege_id)
27 SELECT command_id,privilege_id
28 FROM ea_rules_commands comms, ea_rules_privileges privs
29 WHERE comms.name = $1
30 AND privs.name = $2
31 ON CONFLICT DO NOTHING`
32 InsertPrivilege = `INSERT INTO ea_rules_privileges (name) VALUES ($1) ON CONFLICT DO NOTHING;`
33 InsertCommand = `INSERT INTO ea_rules_commands (name) VALUES ($1) ON CONFLICT DO NOTHING;`
34 DeleteCommand = "DELETE FROM ea_rules_commands WHERE name = $1"
35 DeletePrivilege = "DELETE FROM ea_rules_privileges WHERE name = $1"
36 DeleteDefaultRule = `
37 DELETE FROM ea_rules_default
38 WHERE command_id IN
39 (
40 SELECT commands.command_id
41 FROM ea_rules_commands commands
42 WHERE commands.name = $1
43 )
44 AND privilege_id IN
45 (
46 SELECT privileges.privilege_id
47 FROM ea_rules_privileges privileges
48 WHERE privileges.name = $2
49 )
50 ;`
51
52 SelectBannerByName = "SELECT banner_edge_id, banner_name FROM banners WHERE banner_name = $1 LIMIT 1"
53
54 DeletePrivilegeFromBannerRule = `
55 DELETE FROM ea_rules
56 WHERE banner_edge_id IN
57 (
58 SELECT banners.banner_edge_id
59 from banners
60 WHERE banners.banner_name = $1
61 )
62 AND command_id IN
63 (
64 SELECT commands.command_id
65 FROM ea_rules_commands commands
66 WHERE commands.name = $2
67 )
68 AND privilege_id IN
69 (
70 SELECT privileges.privilege_id
71 FROM ea_rules_privileges privileges
72 WHERE privileges.name = $3
73 )
74 ;
75 `
76
77 SelectAllRulesForBanner = `SELECT commands.name AS commandName,
78 privileges.name AS privilegeName,
79 commands.command_id,
80 privileges.privilege_id
81 FROM ea_rules rules
82 JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
83 JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
84 JOIN banners banners ON rules.banner_edge_id = banners.banner_edge_id
85 WHERE banners.banner_name = $1
86 ;`
87
88 SelectAllRulesFarAllBanners = `SELECT banners.banner_name AS bannerName,
89 commands.name AS commandName,
90 privileges.name AS privilegeName,
91 banners.banner_edge_id,
92 commands.command_id,
93 privileges.privilege_id
94 FROM ea_rules rules
95 JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
96 JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
97 JOIN banners banners ON rules.banner_edge_id = banners.banner_edge_id
98 ;`
99
100 SelectAllBannerRulesForCommand = `SELECT banners.banner_name AS bannerName,
101 commands.name AS commandName,
102 privileges.name AS privilegeName,
103 banners.banner_edge_id,
104 commands.command_id,
105 privileges.privilege_id
106 FROM ea_rules rules
107 JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
108 JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
109 JOIN banners banners ON rules.banner_edge_id = banners.banner_edge_id
110 WHERE commands.name = $1
111 ;`
112
113 SelectBannerRulesForCommandAndBanner = `SELECT banners.banner_name AS bannerName,
114 commands.name AS commandName,
115 privileges.name AS privilegeName,
116 banners.banner_edge_id,
117 commands.command_id,
118 privileges.privilege_id
119 FROM ea_rules rules
120 JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
121 JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
122 JOIN banners banners ON rules.banner_edge_id = banners.banner_edge_id
123 WHERE banners.banner_name = $1
124 AND commands.name = $2
125 ;`
126
127 InsertBannerRule = `INSERT INTO ea_rules (banner_edge_id, command_id, privilege_id)
128 SELECT banner_edge_id,command_id,privilege_id
129 FROM banners, ea_rules_commands comms, ea_rules_privileges privs
130 WHERE
131 banners.banner_name = $1
132 AND comms.name = $2
133 AND privs.name = $3
134 ON CONFLICT DO NOTHING
135 ;`
136
137 GetIDsForRuleSegment = `
138 SELECT
139 'command' as type, commands.command_id
140 FROM ea_rules_commands commands
141 WHERE commands.name = $1
142 UNION ALL
143 SELECT
144 'privilege' as type, privileges.privilege_id
145 FROM ea_rules_privileges privileges
146 WHERE privileges.name = $2
147 ;`
148
149 GetIDsForBannerSegment = `
150 SELECT
151 'banner' as type, banners.banner_edge_id as ID
152 FROM banners
153 WHERE banners.banner_name = $1
154 UNION ALL
155 SELECT
156 'command' as type, commands.command_id
157 FROM ea_rules_commands commands
158 WHERE commands.name = $2
159 UNION ALL
160 SELECT
161 'privilege' as type, privileges.privilege_id
162 FROM ea_rules_privileges privileges
163 WHERE privileges.name = $3
164 ;`
165 SelectPrivNamesForCommandAndBanner = `
166 SELECT
167 privileges.name AS privilegeName
168 FROM ea_rules_default rules
169 JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
170 JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
171 WHERE
172 commands.name = $1
173 AND commands.type = $2
174 UNION
175 SELECT
176 privileges.name AS bannerPrivilegeName
177 FROM ea_rules rules
178 JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
179 JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
180 JOIN banners banners ON rules.banner_edge_id = banners.banner_edge_id
181 WHERE
182 banners.banner_edge_id = $3
183 AND commands.name = $1
184 AND commands.type = $2
185 ;`
186 )
187
View as plain text