Archive

Posts Tagged ‘example’

Deadlock or how to hang a process waiting for a resource indefinitely ☠☠☠

April 4, 2014 No comments

It’s a typical exercise, but we always see it theoretically, let’s bring it to practice. We’re developing with semaphores. As we can see here and here, semaphores, among other things will be used to block a process or thread which is trying to access an exclusive resource which is already being used by other process. A typical example is when you go to a public toilet: when the door isn’t locked, you go in and lock the door, when you finish whatever you were doing there, you unlock the door and exit. The same way, when a process tries to use a resource, if the semaphore is open, closes it, use the resource and reopens the semaphore when finish.
But, here we can create as many semaphores as we want, and we are free to do whatever we want with them, so we can create tons of situations.

But, what if we have three processes (P1, P2 and P3), and P1 is waiting for P2, P2 is waiting for P3 and P3 is waiting for P1? We’ll be waiting forever.

What I’m about to code is:

  • We have two processes (P1 and P2)
  • We have two resources (R1 and R2) which are exclusive (only one process can access each moment)
  • P1 wants to access R1, so closes its semaphore
  • P2 wants to access R2, so closes its semaphore
  • P1 wants to access also R2, so waits for its semaphore to be open
  • P2 wants to access also R1, so waits for its semaphore to be open

As P1 is waiting P2 to open R2 semaphore and P2 is waiting P1 to open R1 semaphore, both processes will be waiting indefinitely.

Check this:

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
#include <unistd.h>
#include <stdlib.h>
#include <stdio.h>
#include <stdint.h>
#include <sys/mman.h>
#include <semaphore.h>
#include <string.h>

int main()
{
  sem_t *sem1 = mmap(NULL, sizeof(sem_t), PROT_READ | PROT_WRITE,
             MAP_SHARED | MAP_ANONYMOUS, -1, 0);
  sem_t *sem2 = mmap(NULL, sizeof(sem_t), PROT_READ | PROT_WRITE,
             MAP_SHARED | MAP_ANONYMOUS, -1, 0);

  int child;

  sem_init (sem1, 1, 1);
  sem_init (sem2, 1, 1);

  child = fork();
  if (child==-1)
    exit(1);
  else if (child==0)
    {
      while(1)
    {
      printf ("[%d] Child waits for sem1...\n", getpid());
      sem_wait(sem1);
      printf ("[%d] Child passes sem1.\n", getpid());
      printf ("[%d] Child waits for sem2...\n", getpid());
      sem_wait(sem2);
      printf ("[%d] Child passes sem2.\n", getpid());
      usleep(100);
      printf ("[%d] Child posts sem2\n", getpid());
      sem_post(sem2);
      printf ("[%d] Child posts sem1\n", getpid());
      sem_post(sem1);
    }
      exit(2);
    }
  else
    {
      while(1)
    {
      printf ("[%d] Main waits for sem2...\n", getpid());
      sem_wait(sem2);
      printf ("[%d] Main passes sem2.\n", getpid());
      printf ("[%d] Main waits for sem1...\n", getpid());
      sem_wait(sem1);
      printf ("[%d] Main passes sem1.\n", getpid());
      usleep(100);
      printf ("[%d] Main posts sem1\n", getpid());
      sem_post(sem1);
      printf ("[%d] Main posts sem2\n", getpid());
      sem_post(sem2);
    }
    }
  while (wait(NULL)>=0);

  munmap(sem1, sizeof(sem_t));
  munmap(sem2, sizeof(sem_t));

  return 0;
}

Compile with pthread (gcc -o deadlock deadlock.c -lpthread) and see something like this:

$ ./deadlock
[30643] Main waits for sem2…
[30643] Main passes sem2.
[30643] Main waits for sem1…
[30643] Main passes sem1.
[30644] Child waits for sem1…
[30643] Main posts sem1
[30643] Main posts sem2
[30643] Main waits for sem2…
[30643] Main passes sem2.
[30644] Child passes sem1.
[30643] Main waits for sem1…
[30644] Child waits for sem2…

It isn’t always so fast, sometimes we can do lots of iterations before the deadlock, and some other times, it will block in the first iteration, let’s see Wikipedia for more information.

Some theory, in this example, the 4 necessary Coffman conditions have been met:

  • Mutual exclusion: R1 and R2 are exclusive, they only can be acceded by a process at a time.
  • Hold and Wait: P1 acquired R1 and hold it while waits for R2 (acquired by P2)
  • No preemption: P1, for example, can’t steal R2 to P2
  • Circular wait: P1 is waiting P2, P2 is waiting P1

How can we fix that?
We have as many solutions as our imagination can, but let’s see some common ones:

Wait for resources in order

If we are always requesting R1 and R2, let’s do in the same order:

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
...
  child = fork();
  if (child==-1)
    exit(1);
  else if (child==0)
    {
      while(1)
    {
      printf ("[%d] Child waits for sem1...\n", getpid());
      sem_wait(sem1);
      printf ("[%d] Child passes sem1.\n", getpid());
      printf ("[%d] Child waits for sem2...\n", getpid());
      sem_wait(sem2);
      printf ("[%d] Child passes sem2.\n", getpid());
      usleep(100);
      printf ("[%d] Child posts sem2\n", getpid());
      sem_post(sem2);
      printf ("[%d] Child posts sem1\n", getpid());
      sem_post(sem1);
    }
      exit(2);
    }
  else
    {
      while(1)
    {
      printf ("[%d] Main waits for sem1...\n", getpid());
      sem_wait(sem1);
      printf ("[%d] Main passes sem1.\n", getpid());
      printf ("[%d] Main waits for sem2...\n", getpid());
      sem_wait(sem2);
      printf ("[%d] Main passes sem2.\n", getpid());
      usleep(100);
      printf ("[%d] Main posts sem2\n", getpid());
      sem_post(sem2);
      printf ("[%d] Main posts sem1\n", getpid());
      sem_post(sem1);
    }
    }
...

Don’t make a mess

Use the least amount of semaphores, if we can do the same using one semaphore, just use one. We can do it in some cases.

Use the resource if available, skip if not

We can use sem_trywait(), if the resource is busy, it’ll return an error without blocking the application. We only have to do it in one process, but this process will enter fewer times in the critic section:

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
...
  child = fork();
  if (child==-1)
    exit(1);
  else if (child==0)
    {
      while(1)
    {
      printf ("[%d] Child waits for sem1...\n", getpid());
      sem_wait(sem1);
      printf ("[%d] Child passes sem1.\n", getpid());
      printf ("[%d] Child waits for sem2...\n", getpid());
      try = sem_trywait(sem2);
      if (try==0)
        {
          printf ("[%d] Child passes sem2.\n", getpid());
          usleep(100);
          printf ("[%d] Child posts sem2\n", getpid());
          sem_post(sem2);
        }
      else
        printf ("[%d] sem2 busy\n", getpid());
      printf ("[%d] Child posts sem1\n", getpid());
      sem_post(sem1);
    }
      exit(2);
    }
  else
    {
      while(1)
    {
      printf ("[%d] Main waits for sem2...\n", getpid());
      sem_wait(sem2);
      printf ("[%d] Main passes sem2.\n", getpid());
      printf ("[%d] Main waits for sem1...\n", getpid());
      sem_wait(sem1);
      printf ("[%d] Main passes sem1.\n", getpid());
      usleep(100);
      printf ("[%d] Main posts sem1\n", getpid());
      sem_post(sem1);
      printf ("[%d] Main posts sem2\n", getpid());
      sem_post(sem2);
    }
    }
...

Timeouts

As the last example, we can wait for a semaphore just a few nanoseconds, if the semaphore opens in this time interval, we will go in, but if not, it’ll return an error, so we can skip:

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
  child = fork();
  if (child==-1)
    exit(1);
  else if (child==0)
    {
      while(1)
    {
      printf ("[%d] Child waits for sem1...\n", getpid());
      sem_wait(sem1);
      printf ("[%d] Child passes sem1.\n", getpid());
      printf ("[%d] Child waits for sem2...\n", getpid());
      timeout.tv_sec=0;
      timeout.tv_nsec=100000;
      try = sem_timedwait(sem2, &timeout);
      if (try==0)
        {
          printf ("[%d] Child passes sem2.\n", getpid());
          usleep(100);
          printf ("[%d] Child posts sem2\n", getpid());
          sem_post(sem2);
        }
      else
        printf ("[%d] sem2 busy\n", getpid());
      printf ("[%d] Child posts sem1\n", getpid());
      sem_post(sem1);
    }
      exit(2);
    }
  else
    {
      while(1)
    {
      printf ("[%d] Main waits for sem2...\n", getpid());
      sem_wait(sem2);
      printf ("[%d] Main passes sem2.\n", getpid());
      printf ("[%d] Main waits for sem1...\n", getpid());
      sem_wait(sem1);
      printf ("[%d] Main passes sem1.\n", getpid());
      usleep(100);
      printf ("[%d] Main posts sem1\n", getpid());
      sem_post(sem1);
      printf ("[%d] Main posts sem2\n", getpid());
      sem_post(sem2);
    }
    }

We can set the timeout with a struct timespec where we can set the time in nanoseconds (variable tv_nsec).

Other algorithms

There are other algorithms which can help, I hope I can dedicate another post to them.
Foto: Moosealope (Flickr) CC-by

Creating a mutex with semaphores between child processes in C [fork()]

March 19, 2014 No comments

We’ve been practicing sharing variables between child processes, but when there are some processes trying to access a shared resource, we need a mutex to make it safer. This time to implement the mutex we’ll use semaphores. This semaphores must be also shared variables to work properly.

First, think about semaphores as variables which can be 0 or 1. So if the semaphore is 1, it’s open and we will close (0 value) it after we pass; if it is 0, we’ll wait until it goes 1 (it’s not like a while (semaphore==0); because the operating system will deactivate the process and reactivate it when the semaphore is open and we can use our system resources for anything else).
But, let’s go further, semaphore’s value can be whatever, not just 0 or 1, but if it’s positive, and we want to pass, we will decrement it and it won’t block our process, but if it’s zero or less, our process will block. So we can say a mutex is a semaphore with 1 and 0 values, used to protect a resource.

To use semaphores we must have in mind three basic functions (there are some more):

  • sem_init(semaphore, pshared, value): Initialize the semaphore with a known value, pshared can be 0 if we want it to be shared between threads of the process, or another value if we want it to be shared between processes. In this case we will put a 1 here.
  • sem_post(semaphore): Increment the semaphore, it’s what we do to free the resource
  • sem_wait(semaphore): Decrement the semaphore, if its value is less than zero, blocks the process until we have a value greater or equal than zero. We’ll use this to check if the resource is locked.

In the next example, we’ll increment a number, but to make it a bit more difficult, it will be stored in a string, each increment must be done by a different child process. The final value of x must be 20. On the other hand, I’ve inserted some random waits to simulate a heavy process and provoke a race condition.

We can change SEMAPHORES constant value from 1 to 0 to see how this program behaves in each case:

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
#include <unistd.h>
#include <stdlib.h>
#include <stdio.h>
#include <stdint.h>
#include <sys/mman.h>
#include <semaphore.h>
#include <string.h>

#define SEMAPHORES 1

int main()
{
  char *x = mmap(NULL, sizeof(char)*10, PROT_READ | PROT_WRITE,
               MAP_SHARED | MAP_ANONYMOUS, -1, 0);
  strcpy(x, "0");

  int i;
  int child;
  sem_t *semaphore = mmap(NULL, sizeof(sem_t), PROT_READ | PROT_WRITE,
             MAP_SHARED | MAP_ANONYMOUS, -1, 0);
  int temp;
  sem_init (semaphore, 1, 1);
  for (i=0; i<10; ++i)
    {
      child = fork();
      if (child==0)
    {
      usleep(rand()%20000);

      if (SEMAPHORES)
        sem_wait(semaphore);
      printf("[%d] Trying to access the resource\n", getpid());
      temp=atoi(x);
      printf("[%d] Using the resource\n", getpid());
      temp++;
      sprintf(x, "%d", temp);

      if (SEMAPHORES)
        sem_post(semaphore);
      printf("[%d] Just used the resource\n", getpid());
      usleep(rand()%20000);

      if (SEMAPHORES)
        sem_wait(semaphore);
      printf("[%d] Trying to access the resource\n", getpid());
      temp=atoi(x);
      printf("[%d] Using the resource\n", getpid());
      temp++;
      sprintf(x, "%d", temp);

      if (SEMAPHORES)
        sem_post(semaphore);
      printf("[%d] Just used the resource\n", getpid());
      printf("[%d] EXITING\n", getpid());
      exit(1);
    }
    }

   while (wait(NULL)>=0);

  printf("x is: %s\n", x);
  munmap(x, sizeof(int));
  munmap(semaphore, sizeof(sem_t));

  return 0;
}

Each time a process wants to enter our critic section, it will be written on screen by its process Id, so we can see when a process is accessing the resource, and we can detect if two or more processes are accessing simultaneously (and we don’t want it). Remember, then final x value must be 20 and without semaphores we may or may have not this value, it isn’t under our control.

Note: To compile the example, include pthread:

$ gcc -o example example.c -lpthread

Photo: Paul Albertella (Flickr) CC-by

MySQL loops and cursors with examples

December 16, 2013 No comments

First of all, you may not abuse of these techniques, and you must use them only when necessary. Most of the times you can find a faster solution.

Let’s see an easy loop, similar to a for loop, we will make a SELECT x with x from 1 to 9:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DELIMITER $$
CREATE PROCEDURE simple_loop ( )
BEGIN
  DECLARE counter BIGINT DEFAULT 0;
 
  my_loop: LOOP
    SET counter=counter+1;

    IF counter=10 THEN
      LEAVE my_loop;
    END IF;

    SELECT counter;

  END LOOP my_loop;
END$$
DELIMITER ;

if we do:

1
CALL simple_loop();

We’ll see something like

+———+
| counter |
+———+
| 1 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 2 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 3 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 4 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 5 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 6 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 7 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 8 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 9 |
+———+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

The code we iterate is between LOOP…END LOOP. What we see right before (my_loop) is a label, just to give a name to that loop, and reference it. In this example, we simply increment counter variable, and with a condition we can exit the loop when this variable reaches the value 10. We won’t see the 10 because we leave the loop before printing this number.

Let’s do something a bit more complicated, we will record scores in a game, this game will be a skill test to do in the shortest time as possible, hopscotch jumping and with obstacles. There are two types of penalty, touch the ground with both feet and hitting an obstacle. At the end of the tests the results will be written on a table, and a score will be assigned, this score will be also stored on that table to avoid calculating it each time.

1
2
3
4
5
6
7
8
9
CREATE TABLE Runners (
    Runner_id BIGINT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(120) NOT NULL,
    Time BIGINT NOT NULL,
    Penalty1 BIGINT NOT NULL,
    Penalty2 BIGINT NOT NULL,
    Points BIGINT,
    PRIMARY KEY (Runner_id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

Now, enter some test information:

1
2
3
4
5
6
7
INSERT INTO Runners VALUES (NULL, 'Michael', 123, 5, 2, NULL);
INSERT INTO Runners VALUES (NULL, 'Sarah', 83, 3, 3, NULL);
INSERT INTO Runners VALUES (NULL, 'John', 323, 1, 1, NULL);
INSERT INTO Runners VALUES (NULL, 'Ramon', 100, 8, 4, NULL);
INSERT INTO Runners VALUES (NULL, 'Andrew', 143, 4, 3, NULL);
INSERT INTO Runners VALUES (NULL, 'Antoine', 199, 3, 2, NULL);
INSERT INTO Runners VALUES (NULL, 'David', 101, 2, 1, NULL);

The first thing to do is a stored procedure with the basic loop, and a SELECT in each iteration, just to see that we are doing ok. (I will explain the code later):

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
DROP PROCEDURE IF EXISTS cursorTest;
DELIMITER $$
CREATE PROCEDURE cursorTest (
) BEGIN
-- Variables where we will store what the SELECT returns
  DECLARE v_name VARCHAR(120);
  DECLARE v_time BIGINT;
  DECLARE v_penalty1 BIGINT;
  DECLARE v_penalty2 BIGINT;
-- Variable to control the end of the loop
  DECLARE fin INTEGER DEFAULT 0;

-- El SELECT to query
  DECLARE runners_cursor CURSOR FOR
    SELECT Name, Time, Penalty1, Penalty2 FROM Runners;

-- Exit condition
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

  OPEN runners_cursor;
  get_runners: LOOP
    FETCH runners_cursor INTO v_name, v_time, v_penalty1, v_penalty2;
    IF fin = 1 THEN
       LEAVE get_runners;
    END IF;

  SELECT v_name, v_time, v_penalty1, v_penalty2;

  END LOOP get_runners;

  CLOSE runners_cursor;
END$$
DELIMITER ;

Must have in mind something. With the CURSOR we will go through the result of a SELECT statement, and we’ll have to store the values returned for each row in variables (that’s why I declared v_name, v_time, v_penalty1 and v_penalty2). In the end, each iteration will do a SELECT Name, Time, Penalty1, Penalty2 INTO v_name, v_time, v_penalty1, v_penalty2 WHERE …, so we’ll have these variables filled with the data obtained for each row in each iteration. That’s DECLARE xxx CURSOR FOR SELECT …

We must put a finish condition, usually we will finish the loop when no more results are found, that’s why we use DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1, In this case, we will set fin to 1 when no more rows are found.

Inside the loop, we will test the value of this variable and LEAVE the loop if fin is set to 1, it’s automatically done when we reach the condition given before.

One step more, let’s create a function to assign the scores to each one of the runners with a formula. For example, if Time is the time taken in seconds, 500-Time will be the initial score and we will take away 5*penalty+3*penalty2. So:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP FUNCTION IF EXISTS calculate_runner_points;
DELIMITER $$
CREATE FUNCTION calculate_runner_points (
  In_time BIGINT,
  In_penalty1 BIGINT,
  In_penalty2 BIGINT
) RETURNS BIGINT
BEGIN
  DECLARE points BIGINT;
 
  SET points = 500 - In_time - In_penalty1*5 - In_penalty2*3;
 
  RETURN points;
END$$
DELIMITER ;

Now, this is the code to calculate the final score of all players:

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
DROP PROCEDURE IF EXISTS calculate_all_points;
DELIMITER $$
CREATE PROCEDURE calculate_all_points (
) BEGIN
-- Variables where we will store what the SELECT returns
  DECLARE v_name VARCHAR(120);
  DECLARE v_time BIGINT;
  DECLARE v_penalty1 BIGINT;
  DECLARE v_penalty2 BIGINT;
  DECLARE v_runner_id BIGINT;
-- Variable to control the end of the loop
  DECLARE fin INTEGER DEFAULT 0;

-- SELECT to query
  DECLARE runners_cursor CURSOR FOR
    SELECT Runner_id, Name, Time, Penalty1, Penalty2 FROM Runners;

-- Exit condition
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

  OPEN runners_cursor;
  get_runners: LOOP
    FETCH runners_cursor INTO v_runner_id, v_name, v_time, v_penalty1,
v_penalty2;
    IF fin = 1 THEN
       LEAVE get_runners;
    END IF;

  UPDATE Runners SET Points=calculate_runner_points(v_time, v_penalty1,
v_penalty2) WHERE Runner_id=v_runner_id;

  END LOOP get_runners;

  CLOSE runners_cursor;
END$$
DELIMITER ;

Of course, as I said in the beginning of this post we must see if there is no other method to do the same. I know using MySQL loops is amazing, as I always say with regex, but there may be faster methods, like:

1
UPDATE Runners SET Points=calculate_runner_points(Time, Penalty1, Penalty2);

But, we can do more things with the loop, for example, if the time is greater than 250, we can swap penalties, editing the loop, with a IF statement, we could also do it in the procedure, but that’s an option.

A small example (or not so small) come to mi mind. Imagine we have a user system. Each user has its information stored in three tables: one for login, password and access info; another one for profile data and the last one for permissions. In this cas, all tables except permission table has one row per user. But as the permission table stores the access level for a user and another object (maybe a web page), and one single user can have permissions over several pages, there may be some rows for one user.

We will also have a table to store messages between users.

We will also have pages, these pages will be objects in our system, and users can see , edit, create derivates and delete them (if they are allowed to). A hierarchy may exist with pages, so we can have child pages, but when we create a new page:

  • If a user is allowed to edit a parent page, will be allowed to edit the new child page
  • If a user could create derivatives in a parent, will be also allowed in the child
  • If a user was allowd to edit and create derivatives in the parent page, will also be allowed to remove the child page.
  • We will have to send a message to the user telling him or her, about the new page and what is allowed to do with it.
  • We also have these procedures and functions:
    • can_create_derivatives(user, page) – It will return TRUE if the user can create derivatives
    • can_edit(user, page) – It will do the same, but with edit permission
    • new_permission(user, page, permission) – It will allow the user to do what permission says with this page
    • message(from, to, message) – Send a message to a user.

Functions can_create_derivatives() and can_edit() seem to be easy to understand, but what they do internally is far more complicated. It’s done by a colleague and I don’t want to fight with it. The same with new_permission() (it can insert rows or updates existent ones) or message(), it can send notifications and create a job to send real e-mail, so our procedure may be something like:

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
DROP PROCEDURE IF EXISTS create_page;
DELIMITER $$
CREATE PROCEDURE create_page (
  IN in_name VARCHAR(120),
  IN in_parent BIGINT
) BEGIN
  DECLARE v_user_id BIGINT;
  DECLARE v_create_derivatives TINYINT;
  DECLARE v_object_id BIGINT;
  DECLARE v_msg TEXT;

-- Variable to control the end of the LOOP
  DECLARE fin INTEGER DEFAULT 0;

-- The SELECT
  DECLARE users_cursor CURSOR FOR
    SELECT User_id FROM Users;

-- Exit condition
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

  INSERT INTO Pages (Name, Parent) VALUES (in_name, in_parent);
  SELECT LAST_INSERT_ID() INTO v_object_id;

  OPEN users_cursor;
  get_users: LOOP
    FETCH users_cursor INTO v_user_id;

    IF fin = 1 THEN
       LEAVE get_users;
    END IF;

    SET v_msg = CONCAT('New permissions on page ',in_name,': ');  

    IF can_create_derivatives(v_user_id, in_parent) THEN
      CALL new_permission(v_user_id, v_object_id, 'derivatives');
      SET v_msg = CONCAT(v_msg, 'Create derivatives ');
      SET v_create_derivatives=1;
    ELSE
      SET v_create_derivatives=0;
    END IF;

    IF can_edit(v_user_id, in_parent) THEN
      CALL new_permission(v_user_id, v_object_id, 'edit');
      SET v_msg = CONCAT(v_msg, 'Edit ');
      IF v_create_derivatives=1 THEN
         CALL new_permission(v_user_id, v_object_id, 'remove');
         SET v_msg = CONCAT(v_msg, 'Remove ');
      END IF;
    END IF;

    CALL message(1, v_user_id, v_msg);
  END LOOP get_users;

  CLOSE users_cursor;
END$$
DELIMITER ;

I’m sure I can give you better examples in the future. I accept suggestions in comments, so I can recreate them in my computer and solve them in future posts.

Photo: Kellie Bollaret (Flickr CC) Licensed CC-by

Top