ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
class.ilDBUpdate4550.php
Go to the documentation of this file.
1<?php
2/* Copyright (c) 1998-2015 ILIAS open source, Extended GPL, see docs/LICENSE */
3
8{
9 public static function cleanupOrphanedChatRoomData()
10 {
14 global $ilDB;
15
16 // Delete orphaned rooms
17 if($ilDB->getDBType() == 'mysql' || $ilDB->getDBType() == 'innodb')
18 {
19 $ilDB->manipulate('
20 DELETE c1
21 FROM chatroom_settings c1
22 INNER JOIN (
23 SELECT chatroom_settings.room_id
24 FROM chatroom_settings
25 LEFT JOIN object_data
26 ON object_data.obj_id = chatroom_settings.object_id
27 WHERE object_data.obj_id IS NULL
28 ) c2
29 ON c2.room_id = c1.room_id');
30 }
31 else
32 {
33 // Oracle and Postgres
34 $ilDB->manipulate('
35 DELETE FROM chatroom_settings
36 WHERE chatroom_settings.room_id IN (
37 SELECT chatroom_settings.room_id
38 FROM chatroom_settings
39 LEFT JOIN object_data
40 ON object_data.obj_id = chatroom_settings.object_id
41 WHERE object_data.obj_id IS NULL
42 )'
43 );
44 }
45
46 // Delete orphaned private rooms
47 if($ilDB->getDBType() == 'mysql' || $ilDB->getDBType() == 'innodb')
48 {
49 $ilDB->manipulate('
50 DELETE c1
51 FROM chatroom_prooms c1
52 INNER JOIN (
53 SELECT chatroom_prooms.proom_id
54 FROM chatroom_prooms
55 LEFT JOIN chatroom_settings
56 ON chatroom_settings.room_id = chatroom_prooms.parent_id
57 WHERE chatroom_settings.room_id IS NULL
58 ) c2
59 ON c2.proom_id = c1.proom_id');
60 }
61 else if($ilDB->getDBType() == 'postgres')
62 {
63 $ilDB->manipulate('
64 DELETE FROM chatroom_prooms
65 WHERE chatroom_prooms.proom_id IN (
66 SELECT c1.proom_id
67 FROM chatroom_prooms c1
68 LEFT JOIN chatroom_settings
69 ON chatroom_settings.room_id = CAST(c1.parent_id as INT)
70 WHERE chatroom_settings.room_id IS NULL
71 )'
72 );
73 }
74 else
75 {
76 // Oracle
77 $ilDB->manipulate('
78 DELETE FROM chatroom_prooms
79 WHERE chatroom_prooms.proom_id IN (
80 SELECT c1.proom_id
81 FROM chatroom_prooms c1
82 LEFT JOIN chatroom_settings
83 ON chatroom_settings.room_id = c1.parent_id
84 WHERE chatroom_settings.room_id IS NULL
85 )'
86 );
87 }
88
89 // Delete orphaned bans
90 if($ilDB->getDBType() == 'mysql' || $ilDB->getDBType() == 'innodb')
91 {
92 $ilDB->manipulate('
93 DELETE c1
94 FROM chatroom_bans c1
95 INNER JOIN (
96 SELECT chatroom_bans.room_id
97 FROM chatroom_bans
98 LEFT JOIN chatroom_settings
99 ON chatroom_settings.room_id = chatroom_bans.room_id
100 WHERE chatroom_settings.room_id IS NULL
101 ) c2
102 ON c2.room_id = c1.room_id');
103 }
104 else
105 {
106 // Oracle and Postgres
107 $ilDB->manipulate('
108 DELETE FROM chatroom_bans
109 WHERE chatroom_bans.room_id IN (
110 SELECT chatroom_bans.room_id
111 FROM chatroom_bans
112 LEFT JOIN chatroom_settings
113 ON chatroom_settings.room_id = chatroom_bans.room_id
114 WHERE chatroom_settings.room_id IS NULL
115 )'
116 );
117 }
118
119 // Delete orphaned history entries
120 if($ilDB->getDBType() == 'mysql' || $ilDB->getDBType() == 'innodb')
121 {
122 $ilDB->manipulate('
123 DELETE c1
124 FROM chatroom_history c1
125 INNER JOIN (
126 SELECT chatroom_history.room_id
127 FROM chatroom_history
128 LEFT JOIN chatroom_settings
129 ON chatroom_settings.room_id = chatroom_history.room_id
130 WHERE chatroom_settings.room_id IS NULL
131 ) c2
132 ON c2.room_id = c1.room_id');
133 }
134 else
135 {
136 // Oracle and Postgres
137 $ilDB->manipulate('
138 DELETE FROM chatroom_history
139 WHERE chatroom_history.room_id IN (
140 SELECT chatroom_history.room_id
141 FROM chatroom_history
142 LEFT JOIN chatroom_settings
143 ON chatroom_settings.room_id = chatroom_history.room_id
144 WHERE chatroom_settings.room_id IS NULL
145 )'
146 );
147 }
148
149 // Delete orphaned users
150 if($ilDB->getDBType() == 'mysql' || $ilDB->getDBType() == 'innodb')
151 {
152 $ilDB->manipulate('
153 DELETE c1
154 FROM chatroom_users c1
155 INNER JOIN (
156 SELECT chatroom_users.room_id
157 FROM chatroom_users
158 LEFT JOIN chatroom_settings
159 ON chatroom_settings.room_id = chatroom_users.room_id
160 WHERE chatroom_settings.room_id IS NULL
161 ) c2
162 ON c2.room_id = c1.room_id');
163 }
164 else
165 {
166 // Oracle and Postgres
167 $ilDB->manipulate('
168 DELETE FROM chatroom_users
169 WHERE chatroom_users.room_id IN (
170 SELECT chatroom_users.room_id
171 FROM chatroom_history
172 LEFT JOIN chatroom_settings
173 ON chatroom_settings.room_id = chatroom_users.room_id
174 WHERE chatroom_settings.room_id IS NULL
175 )'
176 );
177 }
178
179 // Delete orphaned sessions
180 if($ilDB->getDBType() == 'mysql' || $ilDB->getDBType() == 'innodb')
181 {
182 $ilDB->manipulate('
183 DELETE c1
184 FROM chatroom_sessions c1
185 INNER JOIN (
186 SELECT chatroom_sessions.room_id
187 FROM chatroom_sessions
188 LEFT JOIN chatroom_settings
189 ON chatroom_settings.room_id = chatroom_sessions.room_id
190 WHERE chatroom_settings.room_id IS NULL
191 ) c2
192 ON c2.room_id = c1.room_id');
193 }
194 else
195 {
196 // Oracle and Postgres
197 $ilDB->manipulate('
198 DELETE FROM chatroom_sessions
199 WHERE chatroom_sessions.room_id IN (
200 SELECT chatroom_sessions.room_id
201 FROM chatroom_history
202 LEFT JOIN chatroom_settings
203 ON chatroom_settings.room_id = chatroom_sessions.room_id
204 WHERE chatroom_settings.room_id IS NULL
205 )'
206 );
207 }
208
209 // Delete orphaned private sessions
210 if($ilDB->getDBType() == 'mysql' || $ilDB->getDBType() == 'innodb')
211 {
212 $ilDB->manipulate('
213 DELETE c1
214 FROM chatroom_psessions c1
215 INNER JOIN (
216 SELECT chatroom_psessions.proom_id
217 FROM chatroom_psessions
218 LEFT JOIN chatroom_prooms
219 ON chatroom_prooms.proom_id = chatroom_psessions.proom_id
220 WHERE chatroom_prooms.proom_id IS NULL
221 ) c2
222 ON c2.proom_id = c1.proom_id');
223 }
224 else
225 {
226 // Oracle and Postgres
227 $ilDB->manipulate('
228 DELETE FROM chatroom_psessions
229 WHERE chatroom_psessions.proom_id IN (
230 SELECT chatroom_psessions.proom_id
231 FROM chatroom_history
232 LEFT JOIN chatroom_prooms
233 ON chatroom_prooms.proom_id = chatroom_psessions.proom_id
234 WHERE chatroom_prooms.proom_id IS NULL
235 )'
236 );
237 }
238
239 // Delete orphaned private access
240 if($ilDB->getDBType() == 'mysql' || $ilDB->getDBType() == 'innodb')
241 {
242 $ilDB->manipulate('
243 DELETE c1
244 FROM chatroom_proomaccess c1
245 INNER JOIN (
246 SELECT chatroom_proomaccess.proom_id
247 FROM chatroom_proomaccess
248 LEFT JOIN chatroom_prooms
249 ON chatroom_prooms.proom_id = chatroom_proomaccess.proom_id
250 WHERE chatroom_prooms.proom_id IS NULL
251 ) c2
252 ON c2.proom_id = c1.proom_id');
253 }
254 else
255 {
256 // Oracle and Postgres
257 $ilDB->manipulate('
258 DELETE FROM chatroom_proomaccess
259 WHERE chatroom_proomaccess.proom_id IN (
260 SELECT chatroom_proomaccess.proom_id
261 FROM chatroom_history
262 LEFT JOIN chatroom_prooms
263 ON chatroom_prooms.proom_id = chatroom_proomaccess.proom_id
264 WHERE chatroom_prooms.proom_id IS NULL
265 )'
266 );
267 }
268
269 // Delete private room history
270 if($ilDB->getDBType() == 'mysql' || $ilDB->getDBType() == 'innodb')
271 {
272 $ilDB->manipulate('
273 DELETE c1
274 FROM chatroom_history c1
275 INNER JOIN (
276 SELECT chatroom_history.sub_room
277 FROM chatroom_history
278 LEFT JOIN chatroom_prooms
279 ON chatroom_prooms.proom_id = chatroom_history.sub_room
280 WHERE chatroom_prooms.proom_id IS NULL AND chatroom_history.sub_room > 0
281 ) c2
282 ON c2.sub_room = c1.sub_room');
283 }
284 else
285 {
286 // Oracle and Postgres
287 $ilDB->manipulate('
288 DELETE FROM chatroom_history
289 WHERE chatroom_history.sub_room IN (
290 SELECT chatroom_history.sub_room
291 FROM chatroom_history
292 LEFT JOIN chatroom_prooms
293 ON chatroom_prooms.proom_id = chatroom_history.sub_room
294 WHERE chatroom_prooms.proom_id IS NULL AND chatroom_history.sub_room > 0
295 )'
296 );
297 }
298 }
299}
Class ilDBUpdate4550.
global $ilDB