Account Suspended
Account Suspended
This Account has been suspended.
Contact your hosting provider for more information.
 All Data Structures Functions Variables Pages
ZurmoDatabaseCompatibilityUtil.php
1 <?php
2  /*********************************************************************************
3  * Zurmo is a customer relationship management program developed by
4  * Zurmo, Inc. Copyright (C) 2017 Zurmo Inc.
5  *
6  * Zurmo is free software; you can redistribute it and/or modify it under
7  * the terms of the GNU Affero General Public License version 3 as published by the
8  * Free Software Foundation with the addition of the following permission added
9  * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
10  * IN WHICH THE COPYRIGHT IS OWNED BY ZURMO, ZURMO DISCLAIMS THE WARRANTY
11  * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
12  *
13  * Zurmo is distributed in the hope that it will be useful, but WITHOUT
14  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
15  * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
16  * details.
17  *
18  * You should have received a copy of the GNU Affero General Public License along with
19  * this program; if not, see http://www.gnu.org/licenses or write to the Free
20  * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
21  * 02110-1301 USA.
22  *
23  * You can contact Zurmo, Inc. with a mailing address at 27 North Wacker Drive
24  * Suite 370 Chicago, IL 60606. or at email address contact@zurmo.com.
25  *
26  * The interactive user interfaces in original and modified versions
27  * of this program must display Appropriate Legal Notices, as required under
28  * Section 5 of the GNU Affero General Public License version 3.
29  *
30  * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
31  * these Appropriate Legal Notices must retain the display of the Zurmo
32  * logo and Zurmo copyright notice. If the display of the logo is not reasonably
33  * feasible for technical reasons, the Appropriate Legal Notices must display the words
34  * "Copyright Zurmo Inc. 2017. All rights reserved".
35  ********************************************************************************/
36 
46  {
47  // Begin Not Coding Standard
48  private static $storedFunctions = array(
49  // Permitables - Rights
50 
51  'create function get_permitable_explicit_actual_right(
52  permitable_id int(11),
53  module_name varchar(255),
54  right_name varchar(255)
55  )
56  returns tinyint
57  DETERMINISTIC
58  READS SQL DATA
59  begin
60  declare result tinyint;
61  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
62  begin # RedBean hasn\'t created it yet.
63  return 0;
64  end;
65 
66  select max(type)
67  into result
68  from _right
69  where _right.modulename = module_name and
70  name = right_name and
71  _right.permitable_id = permitable_id;
72  if result is null then
73  return 0;
74  end if;
75  return result;
76  end;',
77 
78  // Permitables - Policies
79 
80  'create function get_permitable_explicit_actual_policy(
81  permitable_id int(11),
82  module_name varchar(255),
83  policy_name varchar(255)
84  )
85  returns varchar(255) # A policy value can be anything RedBean will do whatever it it needs to to store it,
86  DETERMINISTIC
87  READS SQL DATA
88  begin # but since PDO returns it as a string I am too, until I know if that is a bad thing.
89  declare result tinyint;
90  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
91  begin # RedBean hasn\'t created it yet.
92  return null;
93  end;
94 
95  select value
96  into result
97  from policy
98  where policy.modulename = module_name and
99  name = policy_name and
100  policy.permitable_id = permitable_id
101  limit 1;
102  return result;
103  end;',
104 
105  // Permitables - Other
106 
107  'create function permitable_contains_permitable(
108  permitable_id_1 int(11),
109  permitable_id_2 int(11)
110  )
111  returns tinyint
112  DETERMINISTIC
113  READS SQL DATA
114  begin
115  declare result tinyint;
116  declare user_id_1, user_id_2, group_id_1, group_id_2 int(11);
117 
118  # If they are both users just compare if they are the same user.
119  select get_permitable_user_id(permitable_id_1)
120  into user_id_1;
121  select get_permitable_user_id(permitable_id_2)
122  into user_id_2;
123  if user_id_1 is not null and user_id_2 is not null then
124  set result = permitable_id_1 = permitable_id_2;
125  else # Not Coding Standard
126  # If the first is a user and the second is a group return false.
127  select get_permitable_group_id(permitable_id_2)
128  into group_id_2;
129  if user_id_1 is not null and group_id_2 is not null then
130  set result = 0;
131  else # Not Coding Standard
132  # Otherwise the first is a group, just return if it contains
133  # the second.
134  select get_permitable_group_id(permitable_id_1)
135  into group_id_1;
136  if group_id_1 is not null then
137  select group_contains_permitable(group_id_1, permitable_id_2)
138  into result;
139  end if;
140  end if;
141  end if;
142  return result;
143  end;',
144 
145  '# returns null if the permitable is not a user.
146  create function get_permitable_user_id(
147  _permitable_id int(11)
148  )
149  returns int(11)
150  DETERMINISTIC
151  READS SQL DATA
152  begin
153  declare result int(11);
154  declare exit handler for 1146 # Table doesn\'t exist.
155  begin # RedBean hasn\'t created it yet.
156  return 0;
157  end;
158 
159  select id
160  into result
161  from _user
162  where _user.permitable_id = _permitable_id;
163  return result;
164  end;',
165 
166  '# returns null if the permitable is not a group.
167  create function get_permitable_group_id(
168  _permitable_id int(11)
169  )
170  returns int(11)
171  begin
172  declare result int(11);
173  declare exit handler for 1146 # Table doesn\'t exist.
174  begin # RedBean hasn\'t created it yet.
175  return 0;
176  end;
177 
178  select id
179  into result
180  from _group
181  where _group.permitable_id = _permitable_id;
182  return result;
183  end;',
184 
185  // Users - Rights
186 
187  '# recursive_get_user_actual_right could just be called get_user_actual_right
188  # and be called directly, but making the call from php and then doing
189  # a second call to select @result is significantly slower than making
190  # one call and having the extra level of indirection in MySQL.
191  create function get_user_actual_right(
192  _user_id int(11),
193  module_name varchar(255),
194  right_name varchar(255)
195  )
196  returns tinyint
197  DETERMINISTIC
198  READS SQL DATA
199  begin
200  declare result tinyint;
201  declare is_super_administrator tinyint;
202 
203  select named_group_contains_user(\'Super Administrators\', _user_id)
204  into is_super_administrator;
205  if is_super_administrator then
206  set result = 1;
207  else # Not Coding Standard
208  call recursive_get_user_actual_right(_user_id, module_name, right_name, result);
209  end if;
210  return result;
211  end;',
212 
213  'create function get_user_explicit_actual_right(
214  _user_id int(11),
215  module_name varchar(255),
216  right_name varchar(255)
217  )
218  returns tinyint
219  DETERMINISTIC
220  READS SQL DATA
221  begin
222  declare result tinyint;
223  declare _permitable_id int;
224  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
225  begin # RedBean hasn\'t created it yet.
226  return 0;
227  end;
228 
229  select permitable_id
230  into _permitable_id
231  from _user
232  where id = _user_id;
233  select get_permitable_explicit_actual_right(_permitable_id, module_name, right_name)
234  into result;
235  return result;
236  end;',
237 
238  'create function get_user_inherited_actual_right(
239  _user_id int(11),
240  module_name varchar(255),
241  right_name varchar(255)
242  )
243  returns tinyint
244  DETERMINISTIC
245  READS SQL DATA
246  begin
247  declare combined_right tinyint default 0;
248  declare __group_id int(11);
249  declare no_more_records tinyint default 0;
250  declare _group_ids cursor for
251  select _group_id
252  from _group__user
253  where _group__user._user_id = _user_id;
254  declare continue handler for not found
255  set no_more_records = 1;
256  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
257  begin # RedBean hasn\'t created it yet.
258  return 0;
259  end;
260 
261  open _group_ids;
262  fetch _group_ids into __group_id;
263  while no_more_records = 0 do
264  select combined_right |
265  get_group_explicit_actual_right (__group_id, module_name, right_name) |
266  get_group_inherited_actual_right(__group_id, module_name, right_name)
267  into combined_right;
268  fetch _group_ids into __group_id;
269  end while;
270  close _group_ids;
271 
272  select combined_right |
273  get_named_group_explicit_actual_right(\'Everyone\', module_name, right_name)
274  into combined_right;
275 
276  if (combined_right & 2) = 2 then
277  return 2;
278  end if;
279  return combined_right;
280  end;',
281 
282  // Users - Policies
283 
284  'create function get_user_explicit_actual_policy(
285  _user_id int(11),
286  module_name varchar(255),
287  policy_name varchar(255)
288  )
289  returns varchar(255) # A policy value can be anything RedBean will do whatever it it needs to to store it,
290  DETERMINISTIC
291  READS SQL DATA
292  begin # but since PDO returns it as a string I am too, until I know if that is a bad thing.
293  declare result tinyint;
294  declare _permitable_id int;
295  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
296  begin # RedBean hasn\'t created it yet.
297  return 0;
298  end;
299 
300  select permitable_id
301  into _permitable_id
302  from _user
303  where id = _user_id;
304  select get_permitable_explicit_actual_policy(_permitable_id, module_name, policy_name)
305  into result;
306  return result;
307  end;',
308 
309  // Groups - Rights
310 
311  'create function get_group_actual_right(
312  _group_id int(11),
313  module_name varchar(255),
314  right_name varchar(255)
315  )
316  returns tinyint
317  DETERMINISTIC
318  READS SQL DATA
319  begin
320  declare result tinyint;
321 
322  select get_group_explicit_actual_right (_group_id, module_name, right_name) |
323  get_group_inherited_actual_right(_group_id, module_name, right_name)
324  into result;
325  if (result & 2) = 2 then
326  return 2;
327  end if;
328  return result;
329  end;',
330 
331  'create function get_group_explicit_actual_right(
332  _group_id int(11),
333  module_name varchar(255),
334  right_name varchar(255)
335  )
336  returns tinyint
337  DETERMINISTIC
338  READS SQL DATA
339  begin
340  declare result tinyint;
341  declare _permitable_id int;
342  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
343  begin # RedBean hasn\'t created it yet.
344  return 0;
345  end;
346 
347  select permitable_id
348  into _permitable_id
349  from _group
350  where id = _group_id;
351  select get_permitable_explicit_actual_right(_permitable_id, module_name, right_name)
352  into result;
353  return result;
354  end;',
355 
356  'create function get_named_group_explicit_actual_right(
357  group_name varchar(255),
358  module_name varchar(255),
359  right_name varchar(255)
360  )
361  returns tinyint
362  DETERMINISTIC
363  READS SQL DATA
364  begin
365  declare result tinyint;
366  declare _permitable_id int;
367  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
368  begin # RedBean hasn\'t created it yet.
369  return 0;
370  end;
371 
372  select permitable_id
373  into _permitable_id
374  from _group
375  where name = group_name;
376  select get_permitable_explicit_actual_right(_permitable_id, module_name, right_name)
377  into result;
378  return result;
379  end;',
380 
381  'create function get_group_inherited_actual_right(
382  _group_id int(11),
383  module_name varchar(255),
384  right_name varchar(255)
385  )
386  returns tinyint
387  DETERMINISTIC
388  READS SQL DATA
389  begin
390  declare combined_right tinyint;
391 
392  call get_group_inherited_actual_right_ignoring_everyone(_group_id, module_name, right_name, combined_right);
393  select combined_right |
394  get_named_group_explicit_actual_right(\'Everyone\', module_name, right_name)
395  into combined_right;
396  if (combined_right & 2) = 2 then
397  return 2;
398  end if;
399  return combined_right;
400  end;',
401 
402  // Groups - Policies
403 
404  'create function get_group_explicit_actual_policy(
405  _group_id int(11),
406  module_name varchar(255),
407  policy_name varchar(255)
408  )
409  returns tinyint
410  DETERMINISTIC
411  READS SQL DATA
412  begin
413  declare result tinyint;
414  declare _permitable_id int;
415  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
416  begin # RedBean hasn\'t created it yet.
417  return 0;
418  end;
419 
420  select permitable_id
421  into _permitable_id
422  from _group
423  where id = _group_id;
424  select get_permitable_explicit_actual_policy(_permitable_id, module_name, policy_name)
425  into result;
426  return result;
427  end;',
428 
429  'create function get_named_group_explicit_actual_policy(
430  group_name varchar(255),
431  module_name varchar(255),
432  policy_name varchar(255)
433  )
434  returns varchar(255) # A policy value can be anything RedBean will do whatever it it needs to to store it,
435  DETERMINISTIC
436  READS SQL DATA
437  begin # but since PDO returns it as a string I am too, until I know if that is a bad thing.
438  declare result tinyint;
439  declare _permitable_id int;
440  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
441  begin # RedBean hasn\'t created it yet.
442  return 0;
443  end;
444 
445  select permitable_id
446  into _permitable_id
447  from _group
448  where name = group_name;
449  select get_permitable_explicit_actual_policy(_permitable_id, module_name, policy_name)
450  into result;
451  return result;
452  end;',
453 
454  // Groups - Contains
455 
456  'create function named_group_contains_permitable(
457  group_name varchar(255),
458  _permitable_id int(11)
459  )
460  returns tinyint
461  DETERMINISTIC
462  READS SQL DATA
463  begin
464  declare result tinyint default 0;
465  declare group_id_1 int(11);
466  declare _user_id int(11);
467  declare group_id_2 int(11);
468  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
469  begin # RedBean hasn\'t created it yet.
470  return 0;
471  end;
472 
473  if group_name = \'Everyone\' then
474  set result = 1;
475  else # Not Coding Standard
476  select id
477  into group_id_1
478  from _group
479  where _group.name = group_name;
480  set _user_id = get_permitable_user_id(_permitable_id);
481  if _user_id is not null then
482  call recursive_group_contains_user(group_id_1, _user_id, result);
483  else # Not Coding Standard
484  set group_id_2 = get_permitable_group_id(_permitable_id);
485  if group_id_2 is not null then
486  call recursive_group_contains_group(group_id_1, group_id_2, result);
487  end if;
488  end if;
489  end if;
490  return result;
491  end;',
492 
493  'create function named_group_contains_user(
494  _group_name varchar(255),
495  _user_id int(11)
496  )
497  returns tinyint
498  DETERMINISTIC
499  READS SQL DATA
500  begin
501  declare result tinyint default 0;
502  declare _group_id int(11);
503  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
504  begin # RedBean hasn\'t created it yet.
505  return 0;
506  end;
507 
508  if _group_name = \'Everyone\' then
509  set result = 1;
510  else # Not Coding Standard
511  select id
512  into _group_id
513  from _group
514  where _group.name = _group_name;
515  call recursive_group_contains_user(_group_id, _user_id, result);
516  end if;
517  return result;
518  end;',
519 
520  'create function group_contains_permitable(
521  _group_id int(11),
522  _permitable_id int(11)
523  )
524  returns tinyint
525  DETERMINISTIC
526  READS SQL DATA
527  begin
528  declare result tinyint default 0;
529  declare _group_name varchar(255);
530  declare is_everyone tinyint;
531  declare _user_id int(11);
532  declare group_id_2 int(11);
533  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
534  begin # RedBean hasn\'t created it yet.
535  return 0;
536  end;
537 
538  select name
539  into _group_name
540  from _group
541  where _group.id = _group_id;
542  if _group_name = \'Everyone\' then
543  set result = 1;
544  else # Not Coding Standard
545  set _user_id = get_permitable_user_id(_permitable_id);
546  if _user_id is not null then
547  call recursive_group_contains_user(_group_id, _user_id, result);
548  else # Not Coding Standard
549  set group_id_2 = get_permitable_group_id(_permitable_id);
550  if group_id_2 is not null then
551  call recursive_group_contains_group(_group_id, group_id_2, result);
552  end if;
553  end if;
554  end if;
555  return result;
556  end;',
557 
558  'create function group_contains_user(
559  _group_id int(11),
560  _user_id int(11)
561  )
562  returns tinyint
563  DETERMINISTIC
564  READS SQL DATA
565  begin
566  declare result tinyint default 0;
567 
568  call recursive_group_contains_user(_group_id, _user_id, result);
569  return result;
570  end;',
571 
572  // SecurableItems - Permissions
573 
574  'create function get_securableitem_actual_permissions_for_permitable(
575  _securableitem_id int(11),
576  _permitable_id int(11),
577  class_name varchar(255),
578  module_name varchar(255),
579  caching_on tinyint
580  )
581  returns smallint
582  DETERMINISTIC
583  READS SQL DATA
584  begin
585  declare allow_permissions, deny_permissions smallint default 0;
586  declare is_super_administrator, is_owner tinyint;
587 
588  delete from __role_children_cache;
589 
590  select named_group_contains_permitable(\'Super Administrators\', _permitable_id)
591  into is_super_administrator;
592  if is_super_administrator then
593  set allow_permissions = 31;
594  set deny_permissions = 0;
595  else # Not Coding Standard
596  begin
597  declare continue handler for 1054, 1146 # Column, table doesn\'t exist.
598  begin # RedBean hasn\'t created it yet.
599  set is_owner = 0;
600  end;
601  select _securableitem_id in
602  (select securableitem_id
603  from _user, ownedsecurableitem
604  where _user.id = ownedsecurableitem.owner__user_id and
605  permitable_id = _permitable_id)
606  into is_owner;
607  end;
608  if is_owner then
609  set allow_permissions = 31;
610  set deny_permissions = 0;
611  else # Not Coding Standard
612  if caching_on then
613  call get_securableitem_cached_actual_permissions_for_permitable(_securableitem_id, _permitable_id, allow_permissions, deny_permissions);
614  if allow_permissions is null then
615  call recursive_get_securableitem_actual_permissions_for_permitable(_securableitem_id, _permitable_id, class_name, module_name, allow_permissions, deny_permissions);
616  call cache_securableitem_actual_permissions_for_permitable(_securableitem_id, _permitable_id, allow_permissions, deny_permissions);
617  end if;
618  else # Not Coding Standard
619  call recursive_get_securableitem_actual_permissions_for_permitable(_securableitem_id, _permitable_id, class_name, module_name, allow_permissions, deny_permissions);
620  end if;
621  end if;
622  end if;
623  return (allow_permissions << 8) | deny_permissions;
624  end;',
625 
626  'create function any_user_in_a_sub_role_has_read_permission(
627  securableitem_id int(11),
628  role_id int(11),
629  class_name varchar(255),
630  module_name varchar(255)
631  )
632  returns tinyint
633  DETERMINISTIC
634  READS SQL DATA
635  begin
636  declare has_read tinyint default 0;
637 
638  call any_user_in_a_sub_role_has_read_permission(securableitem_id, role_id, class_name, module_name, has_read);
639  return has_read;
640  end;',
641 
642  'create function create_item(user_id int, now_timestamp datetime)
643  returns int
644  begin
645  insert into `item` ( `id`, `createddatetime`, `modifieddatetime`,
646  `createdbyuser__user_id`, `modifiedbyuser__user_id` )
647  VALUES ( NULL, now_timestamp , now_timestamp, user_id, user_id );
648  return last_insert_id();
649  end;',
650 
651  'create function create_email_message(text_content text, html_content text, from_name varchar(128),
652  from_address varchar(255), user_id int, owner_id int,
653  subject varchar(255), headers text, folder_id int,
654  serialized_data text, to_address varchar(255), to_name varchar(128),
655  recipient_type int, contact_item_id int,
656  related_model_type varchar(255), related_model_id int,
657  now_timestamp datetime, content_type varchar(255),
658  campaign_content_id int, contact_id int, autoresponder_content_id int)
659  returns int
660  NOT DETERMINISTIC
661  MODIFIES SQL DATA
662  begin
663  insert into `emailmessagecontent` ( `textcontent`, `htmlcontent` )
664  values ( text_content, html_content );
665  set @contentId = last_insert_id();
666  insert into `emailmessagesender` ( `fromname`, `fromaddress` )
667  values ( from_name, from_address );
668  set @senderId = last_insert_id();
669  set @emailMessageItemId = create_item(1, now_timestamp);
670  insert into `securableitem` ( `item_id` )
671  values ( @emailMessageItemId );
672  insert into `ownedsecurableitem` ( `securableitem_id`, `owner__user_id` )
673  values ( last_insert_id(), owner_id );
674  insert into `emailmessage` ( `subject`, `headers`, `ownedsecurableitem_id`,
675  `content_emailmessagecontent_id`, `sender_emailmessagesender_id`,
676  `folder_emailfolder_id`, `contenttype`,
677  `campaigncontent_campaignemailmessagecontent_id`, `contact_contact_id`, `autorespondercontent_autoresponderemailmessagecontent_id`)
678  values ( subject, headers, last_insert_id(), @contentId, @senderId, folder_id, content_type, campaign_content_id, contact_id, autoresponder_content_id);
679  set @emailMessageId = LAST_INSERT_ID();
680  insert into `auditevent` ( `datetime`, `modulename`, `eventname`, `_user_id`,
681  `modelclassname`, `modelid`, `serializeddata` )
682  values ( now_timestamp, "ZurmoModule", "Item Created", user_id,
683  "EmailMessage", @emailMessageId, serialized_data );
684  insert into `emailmessagerecipient` ( `toaddress`, `toname`, `type`, `emailmessage_id` )
685  values ( to_address, to_name, recipient_type, @emailMessageId );
686  set @recipientId = last_insert_id();
687  insert into `emailmessagerecipient_item` ( `emailmessagerecipient_id`, `item_id` )
688  values ( @recipientId, contact_item_id );
689  call duplicate_filemodels(related_model_type, related_model_id, "emailmessage", @emailMessageId, user_id, now_timestamp);
690  return @emailMessageId;
691  end;',
692  );
693  // End Not Coding Standard
694 
695  // MySQL functions cannot be recursive so we have
696  // to do recursive functions with procedures.
697  // Begin Not Coding Standard
698  private static $storedProcedures = array(
699  // Users - Rights
700 
701  'create procedure recursive_get_user_actual_right(
702  in _user_id int(11),
703  in module_name varchar(255),
704  in right_name varchar(255),
705  out result tinyint
706  )
707  begin
708  declare _role_id int;
709  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
710  begin # RedBean hasn\'t created it yet.
711  set result = 0;
712  end;
713 
714  set result = 0;
715  begin
716  declare continue handler for 1054 # Column doesn\'t exist.
717  begin # RedBean hasn\'t created it yet.
718  set _role_id = null;
719  end;
720 
721  select role_id
722  into _role_id
723  from _user
724  where _user.id = _user_id;
725  if _role_id is not null then
726  call recursive_get_user_role_propagated_actual_allow_right(_role_id, module_name, right_name, result);
727  set result = result & 1;
728  end if;
729  end;
730  select get_user_explicit_actual_right (_user_id, module_name, right_name) |
731  get_user_inherited_actual_right(_user_id, module_name, right_name) |
732  result
733  into result;
734 
735  if (result & 2) = 2 then
736  set result = 2;
737  end if;
738  end;',
739 
740  'create procedure recursive_get_user_role_propagated_actual_allow_right(
741  in _role_id int(11),
742  in module_name varchar(255),
743  in right_name varchar(255),
744  out result tinyint
745  )
746  begin
747  declare sub_role_id int(11);
748  declare no_more_records tinyint default 0;
749  declare sub_role_ids cursor for
750  select id
751  from role
752  where role.role_id = _role_id;
753  declare continue handler for not found
754  set no_more_records = 1;
755  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
756  begin # RedBean hasn\'t created it yet.
757  set result = 0;
758  end;
759 
760  set result = 0;
761  open sub_role_ids;
762  fetch sub_role_ids into sub_role_id;
763  while result = 0 and no_more_records = 0 do
764  begin
765  declare _user_id int(11);
766  declare _user_ids cursor for
767  select id
768  from _user
769  where _user.role_id = sub_role_id;
770 
771  open _user_ids;
772  fetch _user_ids into _user_id;
773  while result = 0 and no_more_records = 0 do
774  call recursive_get_user_actual_right(_user_id, module_name, right_name, result);
775  fetch _user_ids into _user_id;
776  end while;
777  close _user_ids;
778  if result = 0 then
779  call recursive_get_user_role_propagated_actual_allow_right(sub_role_id, module_name, right_name, result);
780  end if;
781  set no_more_records = 0;
782  fetch sub_role_ids into sub_role_id;
783  end;
784  end while;
785  close sub_role_ids;
786  end;',
787 
788  // Groups - Rights
789 
790  'create procedure get_group_inherited_actual_right_ignoring_everyone(
791  in _group_id int(11),
792  in module_name varchar(255),
793  in right_name varchar(255),
794  out result tinyint
795  )
796  begin
797  declare parent_group_id int(11);
798  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
799  begin # RedBean hasn\'t created it yet.
800  set result = 0;
801  end;
802 
803  set result = 0;
804  select _group._group_id
805  into parent_group_id
806  from _group
807  where id = _group_id;
808  if parent_group_id is not null then
809  call get_group_inherited_actual_right_ignoring_everyone(parent_group_id, module_name, right_name, result);
810  select result |
811  get_group_explicit_actual_right(parent_group_id, module_name, right_name)
812  into result;
813  if (result & 2) = 2 then
814  set result = 2;
815  end if;
816  end if;
817  end;',
818 
819  // Groups - Other
820 
821  'create procedure recursive_group_contains_user(
822  in _group_id int(11),
823  in _user_id int(11),
824  out result tinyint
825  )
826  begin
827  declare child_group_id, count tinyint;
828  declare no_more_records tinyint default 0;
829  declare child_group_ids cursor for
830  select id
831  from _group
832  where _group._group_id = _group_id;
833  declare continue handler for not found
834  set no_more_records = 1;
835  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
836  begin # RedBean hasn\'t created it yet.
837  set result = 0;
838  end;
839 
840  set result = 0;
841  select count(*)
842  into count
843  from _group__user
844  where _group__user._group_id = _group_id and
845  _group__user._user_id = _user_id;
846 
847  if count > 0 then
848  set result = 1;
849  else # Not Coding Standard
850  open child_group_ids;
851  fetch child_group_ids into child_group_id;
852  while result = 0 and no_more_records = 0 do
853  call recursive_group_contains_user(child_group_id, _user_id, result);
854  fetch child_group_ids into child_group_id;
855  end while;
856  close child_group_ids;
857  end if;
858  end;',
859 
860  'create procedure recursive_group_contains_group(
861  in group_id_1 int(11),
862  in group_id_2 int(11),
863  out result tinyint
864  )
865  begin
866  declare group_2_parent_group_id, child_group_id int(11);
867  declare no_more_records tinyint default 0;
868  declare child_group_ids cursor for
869  select id
870  from _group
871  where _group._group_id = group_id_1;
872  declare continue handler for not found
873  set no_more_records = 1;
874  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
875  begin # RedBean hasn\'t created it yet.
876  set result = 0;
877  end;
878 
879  set result = 0;
880  if group_id_1 = group_id_2 then
881  set result = 1;
882  else # Not Coding Standard
883  select _group_id
884  into group_2_parent_group_id
885  from _group
886  where id = group_id_2;
887  if group_id_1 = group_2_parent_group_id then
888  set result = 1;
889  else # Not Coding Standard
890  open child_group_ids;
891  fetch child_group_ids into child_group_id;
892  while result = 0 and no_more_records = 0 do
893  call recursive_group_contains_user(child_group_id, group_id_2, result);
894  fetch child_group_ids into child_group_id;
895  end while;
896  close child_group_ids;
897  end if;
898  end if;
899  end;',
900 
901  // SecurableItems - Permissions
902 
903  'create procedure recursive_get_securableitem_actual_permissions_for_permitable(
904  in _securableitem_id int(11),
905  in _permitable_id int(11),
906  in class_name varchar(255),
907  in module_name varchar(255),
908  out allow_permissions tinyint,
909  out deny_permissions tinyint
910  )
911  begin
912  declare propagated_allow_permissions tinyint default 0;
913  declare nameditem_allow_permissions, nameditem_deny_permissions tinyint default 0;
914  declare is_owner tinyint;
915  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
916  begin # RedBean hasn\'t created it yet.
917  set allow_permissions = 0;
918  set deny_permissions = 0;
919  end;
920  begin
921  declare continue handler for 1054, 1146 # Column, table doesn\'t exist.
922  begin # RedBean hasn\'t created it yet.
923  set is_owner = 0;
924  end;
925  select _securableitem_id in
926  (select securableitem_id
927  from _user, ownedsecurableitem
928  where _user.id = ownedsecurableitem.owner__user_id and
929  permitable_id = _permitable_id)
930  into is_owner;
931  end;
932  if is_owner then
933  set allow_permissions = 31;
934  set deny_permissions = 0;
935  else # Not Coding Standard
936  set allow_permissions = 0;
937  set deny_permissions = 0;
938  call get_securableitem_explicit_inherited_permissions_for_permitable(_securableitem_id, _permitable_id, allow_permissions, deny_permissions);
939  call get_securableitem_propagated_allow_permissions_for_permitable (_securableitem_id, _permitable_id, class_name, module_name, propagated_allow_permissions);
940  call get_securableitem_module_and_model_permissions_for_permitable (_securableitem_id, _permitable_id, class_name, module_name, nameditem_allow_permissions, nameditem_deny_permissions);
941  set allow_permissions = allow_permissions | propagated_allow_permissions | nameditem_allow_permissions;
942  set deny_permissions = deny_permissions | nameditem_deny_permissions;
943  end if;
944  end;',
945 
946  'create procedure get_securableitem_explicit_actual_permissions_for_permitable(
947  in _securableitem_id int(11),
948  in _permitable_id int(11),
949  out allow_permissions tinyint,
950  out deny_permissions tinyint
951  )
952  begin
953  select bit_or(permissions)
954  into allow_permissions
955  from permission
956  where type = 1 and
957  permitable_id = _permitable_id and
958  securableitem_id = _securableitem_id;
959 
960  select bit_or(permissions)
961  into deny_permissions
962  from permission
963  where type = 2 and
964  permitable_id = _permitable_id and
965  securableitem_id = _securableitem_id;
966  end;',
967 
968  'create procedure get_securableitem_explicit_inherited_permissions_for_permitable(
969  in _securableitem_id int(11),
970  in _permitable_id int(11),
971  out allow_permissions tinyint,
972  out deny_permissions tinyint
973  )
974  begin
975  declare permissions_permitable_id int(11);
976  declare _type, _permissions, permission_applies tinyint;
977  declare no_more_records tinyint default 0;
978  declare permitable_id_type_and_permissions cursor for
979  select permitable_id, type, bit_or(permissions)
980  from permission
981  where securableitem_id = _securableitem_id
982  group by permitable_id, type;
983  declare continue handler for not found
984  set no_more_records = 1;
985  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
986  begin # RedBean hasn\'t created it yet.
987  set allow_permissions = 0;
988  set deny_permissions = 0;
989  end;
990 
991  set allow_permissions = 0;
992  set deny_permissions = 0;
993  open permitable_id_type_and_permissions;
994  fetch permitable_id_type_and_permissions into
995  permissions_permitable_id, _type, _permissions;
996  # The query will return at most one row with the allow bits and
997  # one with the deny bits, so this loop will loop 0, 1, or 2 times.
998  while no_more_records = 0 do
999  select permitable_contains_permitable(permissions_permitable_id, _permitable_id)
1000  into permission_applies;
1001  if permission_applies then
1002  if _type = 1 then
1003  set allow_permissions = allow_permissions | _permissions;
1004  else # Not Coding Standard
1005  set deny_permissions = deny_permissions | _permissions;
1006  end if;
1007  end if;
1008  fetch permitable_id_type_and_permissions into
1009  permissions_permitable_id, _type, _permissions;
1010  end while;
1011  close permitable_id_type_and_permissions;
1012  end;',
1013 
1014  'create procedure get_securableitem_propagated_allow_permissions_for_permitable(
1015  in _securableitem_id int(11),
1016  in _permitable_id int(11),
1017  in class_name varchar(255),
1018  in module_name varchar(255),
1019  out allow_permissions tinyint
1020  )
1021  begin
1022  declare user_id int(11);
1023  declare user_role_id int(11);
1024  declare parent_role_id int(11);
1025  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1026  begin # RedBean hasn\'t created it yet.
1027  set allow_permissions = 0;
1028  end;
1029 
1030  select role_id into user_role_id from _user where permitable_id = _permitable_id;
1031  set allow_permissions = 0;
1032  select get_permitable_user_id(_permitable_id)
1033  into user_id;
1034  if user_id is not null then
1035  call recursive_get_all_descendent_roles(_permitable_id, user_role_id);
1036  call recursive_get_securableitem_propagated_allow_permissions_permit(_securableitem_id, _permitable_id, class_name, module_name, allow_permissions);
1037  end if;
1038  end;',
1039 
1040  //Transverse role tree to get all descendent roles
1041  '
1042  create procedure recursive_get_all_descendent_roles(in _permitable_id int(11), in parent_role_id int(11))
1043  begin
1044  declare child_role_id int(11);
1045  declare no_more_records tinyint default 0;
1046  declare child_role_ids cursor for
1047  select id
1048  from role
1049  where role_id = parent_role_id;
1050  declare continue handler for not found
1051  set no_more_records = 1;
1052 
1053  open child_role_ids;
1054  fetch child_role_ids into child_role_id;
1055  while no_more_records = 0 do
1056  INSERT IGNORE INTO __role_children_cache VALUES (_permitable_id, child_role_id);
1057  call recursive_get_all_descendent_roles(_permitable_id, child_role_id);
1058  fetch child_role_ids into child_role_id;
1059  end while;
1060  close child_role_ids;
1061  end;
1062  ',
1063 
1064  // Name abbreviated - max is 64. Should end '_for permitable'.
1065  'create procedure recursive_get_securableitem_propagated_allow_permissions_permit(
1066  in _securableitem_id int(11),
1067  in _permitable_id int(11),
1068  in class_name varchar(255),
1069  in module_name varchar(255),
1070  out allow_permissions tinyint
1071  )
1072  begin
1073  declare user_allow_permissions, user_deny_permissions, user_propagated_allow_permissions tinyint;
1074  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1075  begin # RedBean hasn\'t created it yet.
1076  set allow_permissions = 0;
1077  end;
1078 
1079  set allow_permissions = 0;
1080 
1081  begin
1082  declare sub_role_id int(11);
1083  declare no_more_records tinyint default 0;
1084  declare sub_role_ids cursor for
1085  select role_id
1086  from __role_children_cache
1087  where permitable_id = _permitable_id;
1088  declare continue handler for not found
1089  begin
1090  set no_more_records = 1;
1091  end;
1092 
1093  open sub_role_ids;
1094  fetch sub_role_ids into sub_role_id;
1095  while no_more_records = 0 do
1096  begin
1097  declare propagated_allow_permissions tinyint;
1098  declare user_in_role_id, permitable_in_role_id int(11);
1099  declare permitable_in_role_ids cursor for
1100  select permitable_id
1101  from _user
1102  where role_id = sub_role_id;
1103 
1104  open permitable_in_role_ids;
1105  fetch permitable_in_role_ids into permitable_in_role_id;
1106  while no_more_records = 0 do
1107  call recursive_get_securableitem_actual_permissions_for_permitable (_securableitem_id, permitable_in_role_id, class_name, module_name, user_allow_permissions, user_deny_permissions);
1108  call recursive_get_securableitem_propagated_allow_permissions_permit(_securableitem_id, permitable_in_role_id, class_name, module_name, propagated_allow_permissions);
1109  set allow_permissions =
1110  allow_permissions |
1111  (user_allow_permissions & ~user_deny_permissions) |
1112  propagated_allow_permissions;
1113  fetch permitable_in_role_ids into permitable_in_role_id;
1114  end while;
1115  end;
1116  set no_more_records = 0;
1117  fetch sub_role_ids into sub_role_id;
1118  end while;
1119  close sub_role_ids;
1120  end;
1121  end;',
1122 
1123  'create procedure get_securableitem_module_and_model_permissions_for_permitable(
1124  in _securableitem_id int(11),
1125  in _permitable_id int(11),
1126  in class_name varchar(255),
1127  in module_name varchar(255),
1128  out allow_permissions tinyint,
1129  out deny_permissions tinyint
1130  )
1131  begin
1132  declare permissions_permitable_id int(11);
1133  declare _type, _permissions, permission_applies tinyint;
1134  declare no_more_records tinyint default 0;
1135  declare permitable_id_type_and_permissions_for_namedsecurableitem cursor for
1136  select permitable_id, type, bit_or(permissions)
1137  from permission, namedsecurableitem
1138  where permission.securableitem_id = namedsecurableitem.securableitem_id and
1139  (name = class_name or name = module_name)
1140  group by permitable_id, type;
1141  declare continue handler for not found
1142  set no_more_records = 1;
1143  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1144  begin # RedBean hasn\'t created it yet.
1145  set allow_permissions = 0;
1146  set deny_permissions = 0;
1147  end;
1148 
1149  set allow_permissions = 0;
1150  set deny_permissions = 0;
1151  open permitable_id_type_and_permissions_for_namedsecurableitem;
1152  fetch permitable_id_type_and_permissions_for_namedsecurableitem into
1153  permissions_permitable_id, _type, _permissions;
1154  # The query will return at most one row with the allow bits and
1155  # one with the deny bits, so this loop will loop 0, 1, or 2 times.
1156  while no_more_records = 0 do
1157  select permitable_contains_permitable(permissions_permitable_id, _permitable_id)
1158  into permission_applies;
1159  if permission_applies then
1160  if _type = 1 then
1161  set allow_permissions = allow_permissions | _permissions;
1162  else # Not Coding Standard
1163  set deny_permissions = deny_permissions | _permissions;
1164  end if;
1165  end if;
1166  fetch permitable_id_type_and_permissions_for_namedsecurableitem into
1167  permissions_permitable_id, _type, _permissions;
1168  end while;
1169  close permitable_id_type_and_permissions_for_namedsecurableitem;
1170  end;',
1171 
1172  // Permissions - Caching
1173  'create procedure get_securableitem_cached_actual_permissions_for_permitable(
1174  in _securableitem_id int(11),
1175  in _permitable_id int(11),
1176  out _allow_permissions tinyint,
1177  out _deny_permissions tinyint
1178  )
1179  begin
1180  declare exit handler for 1146 # Table doesn\'t exist
1181  begin # so nothing is cached.
1182  set _allow_permissions = null;
1183  set _deny_permissions = null;
1184  end;
1185  select allow_permissions, deny_permissions
1186  into _allow_permissions, _deny_permissions
1187  from actual_permissions_cache
1188  where securableitem_id = _securableitem_id and
1189  permitable_id = _permitable_id;
1190  end;',
1191 
1192  'create procedure cache_securableitem_actual_permissions_for_permitable(
1193  in _securableitem_id int(11),
1194  in _permitable_id int(11),
1195  in _allow_permissions tinyint,
1196  in _deny_permissions tinyint
1197  )
1198  begin
1199  declare exit handler for 1146 # Table doesn\'t exist
1200  begin # so thing is cached.
1201  # Temporary communism. TODO: figure out the best
1202  # place to create the table. It can\'t be done in
1203  # stored routines, so it can\'t be done here and
1204  # we only want to do it when it is necessary.
1205  end;
1206  # Tables cannot be created inside stored routines
1207  # so this cannot automatically create the cache
1208  # table if it doesn\'t exist. So it is done when
1209  # the stored routines are created.
1210  insert into actual_permissions_cache
1211  values (_securableitem_id, _permitable_id, _allow_permissions, _deny_permissions);
1212  end;',
1213 
1214  'create procedure clear_cache_securableitem_actual_permissions(
1215  in _securableitem_id int(11)
1216  )
1217  begin
1218  declare continue handler for 1146 # Table doesn\'t exist.
1219  begin
1220  # noop - nothing to clear.
1221  end;
1222  delete from actual_permissions_cache
1223  where securableitem_id = _securableitem_id;
1224  end;',
1225 
1226  'create procedure clear_cache_all_actual_permissions()
1227  READS SQL DATA
1228  begin
1229  declare continue handler for 1146 # Table doesn\'t exist.
1230  begin
1231  # noop - nothing to clear.
1232  end;
1233  delete from actual_permissions_cache;
1234  end;',
1235 
1236  'create procedure clear_cache_named_securable_all_actual_permissions()
1237  READS SQL DATA
1238  begin
1239  declare continue handler for 1146 # Table doesn\'t exist.
1240  begin
1241  # noop - nothing to clear.
1242  end;
1243  delete from named_securable_actual_permissions_cache;
1244  end;',
1245 
1246  'create procedure clear_cache_actual_rights()
1247  READS SQL DATA
1248  begin
1249  declare continue handler for 1146 # Table doesn\'t exist.
1250  begin
1251  # noop - nothing to clear.
1252  end;
1253  delete from actual_rights_cache;
1254  end;',
1255 
1256  // Read Permissions (Munge)
1257  #model_table_name can be person in the case of contact since person has ownedsecurableitem_id and not contact
1258  'create procedure rebuild(
1259  in model_table_name varchar(255),
1260  in munge_table_name varchar(255)
1261  )
1262  begin
1263  call recreate_tables(munge_table_name);
1264  call rebuild_users (model_table_name, munge_table_name);
1265  call rebuild_groups (model_table_name, munge_table_name);
1266  call rebuild_roles (model_table_name, munge_table_name);
1267  end;',
1268 
1269  'create procedure recreate_tables(
1270  in munge_table_name varchar(255)
1271  )
1272  begin
1273  set @sql = concat("drop table if exists ", munge_table_name);
1274  prepare statement from @sql;
1275  execute statement;
1276  deallocate prepare statement;
1277 
1278  set @sql = concat("create table ", munge_table_name, " (",
1279  "securableitem_id int(11) unsigned not null, ",
1280  "munge_id varchar(12) null, ",
1281  "count int(8) unsigned not null, ",
1282  "primary key (securableitem_id, munge_id))");
1283  prepare statement from @sql;
1284  execute statement;
1285  deallocate prepare statement;
1286 
1287  set @sql = concat("create index index_", munge_table_name, "_securableitem_id", " ",
1288  "on ", munge_table_name, " (securableitem_id)");
1289  prepare statement from @sql;
1290  execute statement;
1291  deallocate prepare statement;
1292  end;',
1293 
1294  'create procedure rebuild_users(
1295  in model_table_name varchar(255),
1296  in munge_table_name varchar(255)
1297  )
1298  begin
1299  set @select_statement = concat("select permission.securableitem_id, _user.id, permission.permitable_id
1300  from ", model_table_name, ", ownedsecurableitem, permission, _user
1301  where ", model_table_name , ".ownedsecurableitem_id = ownedsecurableitem.id and
1302  ownedsecurableitem.securableitem_id = permission.securableitem_id and
1303  permission.permitable_id = _user.permitable_id");
1304  set @rebuild_users_temp_table = CONCAT("create temporary table rebuild_temp_table as ", @select_statement);
1305  prepare statement FROM @rebuild_users_temp_table;
1306  execute statement;
1307  deallocate prepare statement;
1308  begin
1309  declare _securableitem_id, __user_id, _permitable_id int(11);
1310  declare no_more_records tinyint default 0;
1311  declare securableitem_user_and_permitable_ids cursor for
1312  select * from rebuild_temp_table;
1313  declare continue handler for not found
1314  set no_more_records = 1;
1315  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1316  begin # RedBean hasn\'t created it yet.
1317  end;
1318  open securableitem_user_and_permitable_ids;
1319  fetch securableitem_user_and_permitable_ids into _securableitem_id, __user_id, _permitable_id;
1320  while no_more_records = 0 do
1321  call rebuild_a_permitable(munge_table_name, _securableitem_id, __user_id, _permitable_id, "U");
1322  fetch securableitem_user_and_permitable_ids into _securableitem_id, __user_id, _permitable_id;
1323  end while;
1324  close securableitem_user_and_permitable_ids;
1325  drop temporary table if exists rebuild_temp_table;
1326  end;
1327  end;',
1328 
1329  // This procedure is largely duplication of the previous
1330  // procedure, but because variable names cannot be used
1331  // in cursors there isn't much to be done about it.
1332  'create procedure rebuild_groups(
1333  in model_table_name varchar(255),
1334  in munge_table_name varchar(255)
1335  )
1336  begin
1337  set @select_statement = concat("select permission.securableitem_id, _group.id, permission.permitable_id
1338  from ", model_table_name , ", ownedsecurableitem, permission, _group
1339  where
1340  ", model_table_name, ".ownedsecurableitem_id = ownedsecurableitem.id AND
1341  ownedsecurableitem.securableitem_id = permission.securableitem_id AND
1342  permission.permitable_id = _group.permitable_id");
1343  set @rebuild_groups_temp_table = CONCAT("create temporary table rebuild_temp_table as ", @select_statement);
1344  prepare statement FROM @rebuild_groups_temp_table;
1345  execute statement;
1346  deallocate prepare statement;
1347  begin
1348  declare _securableitem_id, __group_id, _permitable_id int(11);
1349  declare no_more_records tinyint default 0;
1350  declare securableitem_group_and_permitable_ids cursor for
1351  select * from rebuild_temp_table;
1352  declare continue handler for not found
1353  set no_more_records = 1;
1354  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1355  begin # RedBean hasn\'t created it yet.
1356  end;
1357  open securableitem_group_and_permitable_ids;
1358  fetch securableitem_group_and_permitable_ids into _securableitem_id, __group_id, _permitable_id;
1359  while no_more_records = 0 do
1360  call rebuild_a_permitable(munge_table_name, _securableitem_id, __group_id, _permitable_id, "G");
1361  fetch securableitem_group_and_permitable_ids into _securableitem_id, __group_id, _permitable_id;
1362  end while;
1363  close securableitem_group_and_permitable_ids;
1364  drop temporary table if exists rebuild_temp_table;
1365  end;
1366  end;',
1367 
1368  'create procedure rebuild_a_permitable(
1369  in munge_table_name varchar(255),
1370  in securableitem_id int(11),
1371  in actual_id int(11),
1372  in _permitable_id int(11),
1373  in _type char
1374  )
1375  begin
1376  declare allow_permissions, deny_permissions, effective_explicit_permissions smallint default 0;
1377  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1378  begin # RedBean hasn\'t created it yet.
1379  end;
1380 
1381  call get_securableitem_explicit_actual_permissions_for_permitable(securableitem_id, _permitable_id, allow_permissions, deny_permissions);
1382  set effective_explicit_permissions = allow_permissions & ~deny_permissions;
1383  if (effective_explicit_permissions & 1) = 1 then # Permission::READ
1384  call increment_count(munge_table_name, securableitem_id, actual_id, _type);
1385  if _type = "G" then
1386  call rebuild_roles_for_users_in_group(munge_table_name, securableitem_id, actual_id);
1387  call rebuild_sub_groups (munge_table_name, securableitem_id, actual_id);
1388  end if;
1389  end if;
1390  end;',
1391 
1392  'create procedure rebuild_roles_for_users_in_group(
1393  in munge_table_name varchar(255),
1394  in _securableitem_id int(11),
1395  in __group_id int(11)
1396  )
1397  begin
1398  declare _role_id int(11);
1399  declare no_more_records tinyint default 0;
1400  declare role_ids cursor for
1401  select role_id
1402  from _group__user, _user
1403  where _group__user._group_id = __group_id and
1404  _user.id = _group__user._user_id;
1405  declare continue handler for not found
1406  set no_more_records = 1;
1407  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1408  begin # RedBean hasn\'t created it yet.
1409  end;
1410 
1411  open role_ids;
1412  fetch role_ids into _role_id;
1413  while no_more_records = 0 do
1414  call increment_parent_roles_counts(munge_table_name, _securableitem_id, _role_id);
1415  fetch role_ids into _role_id;
1416  end while;
1417  close role_ids;
1418  end;',
1419 
1420  'create procedure rebuild_sub_groups(
1421  in munge_table_name varchar(255),
1422  in _securableitem_id int(11),
1423  in __group_id int(11)
1424  )
1425  begin
1426  declare sub_group_id int(11);
1427  declare no_more_records tinyint default 0;
1428  declare sub_group_ids cursor for
1429  select id
1430  from _group
1431  where _group_id = __group_id;
1432  declare continue handler for not found
1433  set no_more_records = 1;
1434  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1435  begin # RedBean hasn\'t created it yet.
1436  end;
1437 
1438  open sub_group_ids;
1439  fetch sub_group_ids into sub_group_id;
1440  while no_more_records = 0 do
1441  call increment_count (munge_table_name, _securableitem_id, sub_group_id, "G");
1442  call rebuild_roles_for_users_in_group(munge_table_name, _securableitem_id, sub_group_id);
1443  call rebuild_sub_groups (munge_table_name, _securableitem_id, sub_group_id);
1444  fetch sub_group_ids into sub_group_id;
1445  end while;
1446  close sub_group_ids;
1447  end;',
1448 
1449  'create procedure rebuild_roles(
1450  in model_table_name varchar(255),
1451  in munge_table_name varchar(255)
1452  )
1453  begin
1454  call rebuild_roles_owned_securableitems (model_table_name, munge_table_name);
1455  call rebuild_roles_securableitem_with_explicit_user_permissions (model_table_name, munge_table_name);
1456  call rebuild_roles_securableitem_with_explicit_group_permissions(model_table_name, munge_table_name);
1457  end;',
1458 
1459  'create procedure rebuild_roles_owned_securableitems(
1460  in model_table_name varchar(255),
1461  in munge_table_name varchar(255)
1462  )
1463  begin
1464  set @select_statement = concat("select role_id, ownedsecurableitem.securableitem_id
1465  from ", model_table_name, ", _user, ownedsecurableitem
1466  where ", model_table_name, ".ownedsecurableitem_id = ownedsecurableitem.id AND
1467  _user.id = ownedsecurableitem.owner__user_id and _user.role_id is not null");
1468  set @rebuild_roles_temp_table = CONCAT("create temporary table rebuild_temp_table as ", @select_statement);
1469  prepare statement FROM @rebuild_roles_temp_table;
1470  execute statement;
1471  deallocate prepare statement;
1472  begin
1473  declare _role_id, _securableitem_id int(11);
1474  declare no_more_records tinyint default 0;
1475  declare role_and_securableitem_ids cursor for
1476  select * from rebuild_temp_table;
1477  declare continue handler for not found
1478  set no_more_records = 1;
1479  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1480  begin # RedBean hasn\'t created it yet.
1481  end;
1482  open role_and_securableitem_ids;
1483  fetch role_and_securableitem_ids into _role_id, _securableitem_id;
1484  while no_more_records = 0 do
1485  call increment_parent_roles_counts(munge_table_name, _securableitem_id, _role_id);
1486  fetch role_and_securableitem_ids into _role_id, _securableitem_id;
1487  end while;
1488  close role_and_securableitem_ids;
1489  drop temporary table if exists rebuild_temp_table;
1490  end;
1491  end;',
1492 
1493  'create procedure rebuild_roles_securableitem_with_explicit_user_permissions(
1494  in model_table_name varchar(255),
1495  in munge_table_name varchar(255)
1496  )
1497  begin
1498  set @select_statement = concat("select role_id, permission.securableitem_id
1499  from ", model_table_name, ", ownedsecurableitem, permission, _user
1500  where ", model_table_name, ".ownedsecurableitem_id = ownedsecurableitem.id AND
1501  ownedsecurableitem.securableitem_id = permission.securableitem_id AND
1502  permission.permitable_id = _user.permitable_id and
1503  ((permission.permissions & 1) = 1) and permission.type = 1");
1504  set @rebuild_roles_temp_table = CONCAT("create temporary table rebuild_temp_table as ", @select_statement);
1505  prepare statement FROM @rebuild_roles_temp_table;
1506  execute statement;
1507  deallocate prepare statement;
1508  begin
1509  declare _role_id, _securableitem_id int(11);
1510  declare no_more_records tinyint default 0;
1511  declare role_and_securableitem_ids cursor for
1512  select * from rebuild_temp_table;
1513  declare continue handler for not found
1514  set no_more_records = 1;
1515  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1516  begin # RedBean hasn\'t created it yet.
1517  end;
1518  open role_and_securableitem_ids;
1519  fetch role_and_securableitem_ids into _role_id, _securableitem_id;
1520  while no_more_records = 0 do
1521  call increment_parent_roles_counts(munge_table_name, _securableitem_id, _role_id);
1522  fetch role_and_securableitem_ids into _role_id, _securableitem_id;
1523  end while;
1524  close role_and_securableitem_ids;
1525  drop temporary table if exists rebuild_temp_table;
1526  end;
1527  end;',
1528 
1529  'create procedure rebuild_roles_securableitem_with_explicit_group_permissions(
1530  in model_table_name varchar(255),
1531  in munge_table_name varchar(255)
1532  )
1533  begin
1534  set @select_statement = concat("select role.role_id, permission.securableitem_id
1535  from ", model_table_name, ", ownedsecurableitem, _user, _group, _group__user, permission, role
1536  where ", model_table_name, ".ownedsecurableitem_id = ownedsecurableitem.id and
1537  ownedsecurableitem.securableitem_id = permission.securableitem_id and
1538  _user.id = _group__user._user_id and
1539  permission.permitable_id = _group.permitable_id and
1540  _group__user._group_id = _group.id and
1541  _user.role_id = role.role_id and
1542  ((permission.permissions & 1) = 1) and
1543  permission.type = 1");
1544  set @rebuild_roles_temp_table = CONCAT("create temporary table rebuild_temp_table as ", @select_statement);
1545  prepare statement FROM @rebuild_roles_temp_table;
1546  execute statement;
1547  deallocate prepare statement;
1548  begin
1549  declare _role_id, _securableitem_id int(11);
1550  declare no_more_records tinyint default 0;
1551  declare role_and_securableitem_ids cursor for
1552  select * from rebuild_temp_table;
1553  declare continue handler for not found
1554  set no_more_records = 1;
1555  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1556  begin # RedBean hasn\'t created it yet.
1557  end;
1558  open role_and_securableitem_ids;
1559  fetch role_and_securableitem_ids into _role_id, _securableitem_id;
1560  while no_more_records = 0 do
1561  call increment_count (munge_table_name, _securableitem_id, _role_id, "R");
1562  call increment_parent_roles_counts(munge_table_name, _securableitem_id, _role_id);
1563  fetch role_and_securableitem_ids into _role_id, _securableitem_id;
1564  end while;
1565  close role_and_securableitem_ids;
1566  drop temporary table if exists rebuild_temp_table;
1567  end;
1568  end;',
1569 
1570  'create procedure increment_count(
1571  in munge_table_name varchar(255),
1572  in securableitem_id int(11),
1573  in item_id int(11),
1574  in _type char
1575  )
1576  begin
1577  # TODO: insert only if the row doesn\'t exist
1578  # in a way that doesn\'t ignore all errors.
1579  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1580  begin # RedBean hasn\'t created it yet.
1581  end;
1582 
1583  set @sql = concat("insert into ", munge_table_name,
1584  "(securableitem_id, munge_id, count) ",
1585  "values (", securableitem_id, ", \'", concat(_type, item_id), "\', 1) ",
1586  "on duplicate key ",
1587  "update count = count + 1");
1588  prepare statement from @sql;
1589  execute statement;
1590  deallocate prepare statement;
1591  end;',
1592 
1593  'create procedure decrement_count(
1594  in munge_table_name varchar(255),
1595  in _securableitem_id int(11),
1596  in item_id int(11),
1597  in _type char
1598  )
1599  begin
1600  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1601  begin # RedBean hasn\'t created it yet.
1602  end;
1603 
1604  update munge_table_name
1605  set count = count - 1
1606  where securableitem_id = _securableitem_id and
1607  munge_id = concat(_type, item_id);
1608  end;',
1609 
1610  'create procedure increment_parent_roles_counts(
1611  in munge_table_name varchar(255),
1612  in securableitem_id int(11),
1613  in _role_id int(11)
1614  )
1615  begin
1616  declare parent_role_id int(11);
1617  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1618  begin # RedBean hasn\'t created it yet.
1619  end;
1620 
1621  select role_id
1622  into parent_role_id
1623  from role
1624  where id = _role_id;
1625  if parent_role_id is not null then
1626  call increment_count (munge_table_name, securableitem_id, parent_role_id, "R");
1627  call increment_parent_roles_counts(munge_table_name, securableitem_id, parent_role_id);
1628  end if;
1629  end;',
1630 
1631  'create procedure decrement_parent_roles_counts(
1632  in munge_table_name varchar(255),
1633  in securableitem_id int(11),
1634  in role_id int(11)
1635  )
1636  begin
1637  declare parent_role_id int(11);
1638  declare exit handler for 1054, 1146 # Column, table doesn\'t exist.
1639  begin # RedBean hasn\'t created it yet.
1640  end;
1641 
1642  select role_id
1643  into parent_role_id
1644  from role
1645  where id = role_id;
1646  if parent_role_id is not null then
1647  call decrement_count (munge_table_name, securableitem_id, parent_role_id);
1648  call decrement_parent_roles_counts(munge_table_name, securableitem_id, parent_role_id);
1649  end if;
1650  end;',
1651 
1652  // Misc
1653 
1654  'create procedure duplicate_filemodels(related_model_type varchar(255), related_model_id int,
1655  new_model_type varchar(255), new_model_id int, user_id int,
1656  now_timestamp datetime)
1657  begin
1658  insert into `filemodel` (`id`, `name`, `size`, `type`, `item_id`,
1659  `filecontent_id`, `relatedmodel_id`, `relatedmodel_type`)
1660  select null as `id`, `name`, `size`, `type`, (select create_item(user_id, now_timestamp)) as `item_id`, `filecontent_id`,
1661  new_model_id as `relatedmodel_id`, new_model_type as `relatedmodel_type`
1662  from `filemodel`
1663  where `relatedmodel_type` = related_model_type and `relatedmodel_id` = related_model_id;
1664  end;',
1665 
1666  'create procedure create_campaign_items(campaign_id int, marketing_list_id int, processed int)
1667  begin
1668  insert into `campaignitem` (`id`, `processed`, `campaign_id`, `contact_id`)
1669  select null as id, processed as `processed`, campaign_id as `campaign_id`, `marketinglistmember`.`contact_id`
1670  from `marketinglistmember`
1671  left join `campaignitem` on `campaignitem`.`contact_id` = `marketinglistmember`.`contact_id`
1672  and `campaignitem`.`campaign_id` = campaign_id
1673  left join `contact` on `contact`.`id` = `marketinglistmember`.`contact_id`
1674  where (`marketinglistmember`.`marketinglist_id` = marketing_list_id
1675  and `campaignitem`.`id` is null and `contact`.`id` is not null);
1676  end;',
1677 
1678  'create procedure generate_campaign_items(active_status int, processing_status int, now_timestamp datetime)
1679  begin
1680  declare loop0_eof boolean default false;
1681  declare campaign_id int(11);
1682  declare marketinglist_id int(11);
1683 
1684  declare cursor0 cursor for select `campaign`.`id`, `campaign`.`marketinglist_id` from `campaign`
1685  where ((`campaign`.`status` = active_status) and (`campaign`.`sendondatetime` < now_timestamp));
1686  declare continue handler for not found set loop0_eof = TRUE;
1687  open cursor0;
1688  loop0: loop
1689  fetch cursor0 into campaign_id, marketinglist_id;
1690  if loop0_eof then
1691  leave loop0;
1692  end if;
1693  call create_campaign_items(campaign_id, marketinglist_id, 0);
1694  update `campaign` set `status` = processing_status where id = campaign_id;
1695  end loop loop0;
1696  close cursor0;
1697  end;
1698 
1699  create procedure update_email_message_for_sending(message_id int, send_attempts int, sent_datetime datetime,
1700  folder_id int, error_serialized_data text, now_timestamp datetime)
1701  begin
1702  set @emailMessageSendErrorId = null;
1703  delete from `emailmessagesenderror`
1704  where id = (select error_emailmessagesenderror_id
1705  from `emailmessage`
1706  where id = message_id);
1707  if (error_serialized_data is not null) then
1708  insert into `emailmessagesenderror` ( id, `createddatetime`,`serializeddata` ) values
1709  (null, now_timestamp , error_serialized_data);
1710  set @emailMessageSendErrorId = last_insert_id();
1711  end if;
1712 
1713  update `emailmessage` set
1714  `sendattempts` = send_attempts,
1715  `sentdatetime` = sent_datetime,
1716  `folder_emailfolder_id` = folder_id,
1717  `error_emailmessagesenderror_id` = @emailMessageSendErrorId
1718  where id = message_id;
1719  end;',
1720  );
1721  // End Not Coding Standard
1722 
1727  public static function callFunction($sql)
1728  {
1729  try
1730  {
1731  return ZurmoRedBean::getCell("select $sql;");
1732  }
1733  catch (RedBean_Exception_SQL $e)
1734  {
1735  self::createStoredFunctionsAndProcedures();
1736  self::createActualPermissionsCacheTable();
1737  self::createNamedSecurableActualPermissionsCacheTable();
1738  self::createActualRightsCacheTable();
1739  return ZurmoRedBean::getCell("select $sql;");
1740  }
1741  }
1742 
1747  public static function callProcedureWithoutOuts($sql)
1748  {
1749  try
1750  {
1751  return ZurmoRedBean::getCell("call $sql;");
1752  }
1753  catch (RedBean_Exception_SQL $e)
1754  {
1755  self::createStoredFunctionsAndProcedures();
1756  self::createActualPermissionsCacheTable();
1757  self::createNamedSecurableActualPermissionsCacheTable();
1758  self::createActualRightsCacheTable();
1759  return ZurmoRedBean::getCell("call $sql;");
1760  }
1761  }
1762 
1763  public static function createStoredFunctionsAndProcedures()
1764  {
1765  assert('RedBeanDatabase::isSetup()');
1766  if (RedBeanDatabase::getDatabaseType() == 'mysql')
1767  {
1768  self::dropStoredFunctionsAndProcedures();
1769  ZurmoRedBean::exec('CREATE TABLE IF NOT EXISTS __role_children_cache(permitable_id int(11),
1770  role_id int(11), PRIMARY KEY (permitable_id, role_id),
1771  UNIQUE KEY (permitable_id, role_id));');
1772  try
1773  {
1774  foreach (self::$storedFunctions as $sql)
1775  {
1776  $sql = self::stripNonFrozenModeErrorHandlersIfFrozen($sql);
1777  ZurmoRedBean::exec($sql);
1778  }
1779  foreach (self::$storedProcedures as $sql)
1780  {
1781  $sql = self::stripNonFrozenModeErrorHandlersIfFrozen($sql);
1782  ZurmoRedBean::exec($sql);
1783  }
1784  if (YII_DEBUG)
1785  {
1786  ZurmoRedBean::exec('create table if not exists log
1787  (timestamp timestamp, message varchar(255))');
1788  ZurmoRedBean::exec('create procedure write_log(in message varchar(255))
1789  begin
1790  insert into log (timestamp, message)
1791  values (now(), message);
1792  end;');
1793  }
1794  }
1795  catch (Exception $e)
1796  {
1797  echo "Failed to create:\n$sql\n";
1798  throw $e;
1799  }
1800  }
1801  else
1802  {
1803  throw new NotSupportedException();
1804  }
1805  }
1806 
1807  // When run in unfrozen mode the stored function and procedures
1808  // must ignore tables and columns that don't exist because they
1809  // will be created as required, and the error handlers as coded
1810  // cater for this. In frozen mode that represents a real error
1811  // and must be allowed to escape. In frozen mode with debug off
1812  // the error handlers are stripped from the routines. In frozen
1813  // mode with debug on a write_log() call is added to the error
1814  // handler which writes to the log table.
1815  protected static function stripNonFrozenModeErrorHandlersIfFrozen($sql)
1816  {
1818  {
1819  // It is deliberate that these regexs are written to not catch
1820  // handlers that are not written consistently with the existing
1821  // ones. If the assertions blow up look at making your handler
1822  // match the expression rather than messing with the expression.
1823  if (!YII_DEBUG)
1824  {
1825  $sql = preg_replace('/ +declare (continue|exit) handler for (1054|1146|1054, 1146).*?begin.*?end;\n/s',
1826  '',
1827  $sql, -1, $count);
1828  }
1829  else
1830  {
1831  $matched = preg_match('/create (function|procedure) ([^( ]+)/', $sql, $matches); // Not Coding Standard
1832  assert('$matched == 1');
1833  $routineName = $matches[2];
1834  $sql = preg_replace('/( +declare (continue|exit) handler for (1054|1146|1054, 1146).*?begin.*?)(end;\n)/s',
1835  "\\1 call write_log(\"$routineName failed! (\\3)\");\n \\4",
1836  $sql, -1, $count);
1837  }
1838 
1839  if (!YII_DEBUG)
1840  {
1841  assert('strpos($sql, "1146") == false'); // Table doesn't exist.
1842  assert('strpos($sql, "1054") == false'); // Column doesn't exist.
1843  }
1844  }
1845  return $sql;
1846  }
1847 
1848  public static function createActualPermissionsCacheTable()
1849  {
1850  ZurmoRedBean::exec('
1851  create table if not exists actual_permissions_cache
1852  (securableitem_id int(11) unsigned not null,
1853  permitable_id int(11) unsigned not null,
1854  allow_permissions tinyint unsigned not null,
1855  deny_permissions tinyint unsigned not null,
1856  primary key (securableitem_id, permitable_id)
1857  ) engine = innodb
1858  default charset = utf8
1859  collate = utf8_unicode_ci');
1860  }
1861 
1862  public static function createNamedSecurableActualPermissionsCacheTable()
1863  {
1864  ZurmoRedBean::exec('
1865  create table if not exists named_securable_actual_permissions_cache
1866  (securableitem_name varchar(64) not null,
1867  permitable_id int(11) unsigned not null,
1868  allow_permissions tinyint unsigned not null,
1869  deny_permissions tinyint unsigned not null,
1870  primary key (securableitem_name, permitable_id)
1871  ) engine = innodb
1872  default charset = utf8
1873  collate = utf8_unicode_ci');
1874  }
1875 
1876  public static function createActualRightsCacheTable()
1877  {
1878  ZurmoRedBean::exec('
1879  create table if not exists actual_rights_cache
1880  (identifier varchar(255) not null,
1881  entry int(11) unsigned not null,
1882  primary key (identifier)
1883  ) engine = innodb
1884  default charset = utf8
1885  collate = utf8_unicode_ci');
1886  }
1887 
1888  public static function dropStoredFunctionsAndProcedures()
1889  {
1890  assert('RedBeanDatabase::isSetup()');
1891  if (RedBeanDatabase::getDatabaseType() == 'mysql')
1892  {
1893  try
1894  {
1895  $rows = ZurmoRedBean::getAll("select routine_name, routine_type from information_schema.routines;");
1896  foreach ($rows as $row)
1897  {
1898  ZurmoRedBean::exec("drop {$row['routine_type']} if exists {$row['routine_name']}");
1899  }
1900  }
1901  catch (Exception $e)
1902  {
1903  if (isset($row))
1904  {
1905  echo "Failed to drop {$row['routine_type']} {$row['routine_name']}.\n";
1906  }
1907  throw $e;
1908  }
1909  if (YII_DEBUG)
1910  {
1911  ZurmoRedBean::exec("drop procedure if exists write_log");
1912  }
1913  }
1914  else
1915  {
1916  throw new NotSupportedException();
1917  }
1918  }
1919  }
1920 ?>
Generated on Wed Dec 2 2020 07:10:33
Account Suspended
Account Suspended
This Account has been suspended.
Contact your hosting provider for more information.