6TfZkRj4

1/*
2  Procedure for selecting last messages for username
3 */
4DROP PROCEDURE IF EXISTS select_last_messages_for_user;
5CREATE PROCEDURE select_last_messages_for_user(_username VARCHAR(128))
6  BEGIN
7
8    DECLARE current_username VARCHAR(128);
9    DECLARE done BOOL DEFAULT FALSE;
10    DECLARE users_cursor CURSOR FOR
11      /*
12        Получение списка всех чатов пользователя admin
13       */
14      SELECT MFU.username AS users
15      FROM
16        (
17          SELECT message_id
18          FROM Messages M JOIN MessagesToUser MTU ON M.id = MTU.message_id
19          WHERE MTU.username = _username
20        ) T1
21        JOIN MessagesFromUser MFU ON T1.message_id = MFU.message_id
22      UNION
23      SELECT MTU.username as users
24      FROM
25        (
26          SELECT message_id
27          FROM Messages M
28            JOIN MessagesFromUser MFU ON M.id = MFU.message_id
29          WHERE MFU.username = _username
30        ) T1
31        JOIN MessagesToUser MTU ON T1.message_id = MTU.message_id;
32    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
33
34    CREATE TEMPORARY TABLE ans_table
35    (
36      id BIGINT AUTO_INCREMENT PRIMARY KEY,
37      text VARCHAR(2048) CHARACTER SET utf32 NOT NULL,
38      is_read BOOL NOT NULL DEFAULT FALSE,
39      from_who VARCHAR(128),
40      chat_name VARCHAR(128)
41    );
42
43    OPEN users_cursor;
44
45    read_loop: LOOP
46      FETCH users_cursor INTO current_username;
47      IF done THEN
48        LEAVE read_loop;
49      END IF;
50
51      INSERT INTO ans_table
52      SELECT M.*, MFU.username AS from_who, current_username AS chat_name
53      FROM Messages M JOIN MessagesFromUser MFU ON M.id = MFU.message_id
54      WHERE M.id = get_last_message_id(_username, current_username);
55
56    END LOOP;
57
58    CLOSE users_cursor;
59
60    SELECT * FROM ans_table;
61    DROP TABLE ans_table;
62
63  END;