Entity Relationship diagram

entity-relationship-diagram.png

Notes:

  • `event_log` is the entry point of the event logs storage
  • `event_type` can be a Registration (0), Call (1), Message (2), Auth (3), QualityStatistics (4)
  • `message_type` supports currently two values : Received (0) or Delivered (1)
  • `registration_type` deals with Register (0), Unregister (1), Expired (2)
  • /!\ the new field 'priority' is currently used to distinguish IMDN from 'real' messages

Query examples

-- Select all call logs.
SELECT event_log.id AS id, sip_from, sip_to, user_agent, date, status_code, reason, completed, call_id, cancelled
FROM event_log
JOIN event_call_log ON event_call_log.id = event_log.id
WHERE type_id = 1;
-- Select all unique user agents.
SELECT DISTINCT user_agent FROM event_log;
-- Select last logs with limit.
SELECT * FROM (
 SELECT event_log.id AS id, event_type.type AS event_type, sip_from, sip_to, user_agent, date, status_code, reason, completed, call_id,
    method, origin, user_exists, -- auth
   cancelled, -- call
   report, -- call_quality_statistics
   message_type.type AS message_type, uri, -- message
   registration_type.type AS registration_type, contacts -- registration
 FROM event_log
 LEFT JOIN event_auth_log ON event_auth_log.id = event_log.id
 LEFT JOIN event_call_log ON event_call_log.id = event_log.id
 LEFT JOIN event_call_quality_statistics_log ON event_call_quality_statistics_log.id = event_log.id
 LEFT JOIN event_message_log ON event_message_log.id = event_log.id
 LEFT JOIN event_registration_log ON event_registration_log.id = event_log.id
 LEFT JOIN event_type ON event_type.id = event_log.type_id
 LEFT JOIN message_type ON message_type.id = event_message_log.type_id
 LEFT JOIN registration_type on registration_type.id = event_registration_log.type_id
 ORDER BY event_log.id DESC
 LIMIT 100
) AS event
ORDER BY event.id ASC;
-- Select call and message logs (from last 24 hours).
SELECT event_log.id AS id, event_type.type AS event_type, sip_from, sip_to, user_agent, date, status_code, reason, completed, call_id,
  cancelled, -- call
 message_type.type AS message_type, uri -- message
FROM event_log
LEFT JOIN event_call_log ON event_call_log.id = event_log.id
LEFT JOIN event_message_log ON event_message_log.id = event_log.id
LEFT JOIN event_type ON event_type.id = event_log.type_id
LEFT JOIN message_type ON message_type.id = event_message_log.type_id
WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY);
-- Count number of messages sent by one user.
select count(*) from event_log where type_id=2 and sip_from LIKE '%user%';
-- Count the number of messages sent by one user to another
-- or Count the number of messages sent by 2 users to each other (when called 2 times with swapped users)
drop procedure if exists getMessageNbSentUser1ToUser2;
DELIMITER //
create procedure getMessageNbSentUser1ToUser2 (user1 varchar(64), user2 varchar(64))
begin

 set @counter := 1;
 set @ev_log_counter := 0;
 set @max := 0;

 -- Declares a temporary table.
 create temporary table myTable (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, sip_to varchar(100), call_id varchar(100));

 -- Insert your required data in the table

 -- user1 to user2
 -- type_id=2 specifies an event_log of type message
 -- we want to select each chatroom and associated call-id to where user1 sent a message
 INSERT INTO myTable (sip_to, call_id)
 SELECT distinct sip_to, call_id from event_log ev1 where type_id=2 and sip_to LIKE '%chatroom%' and sip_from LIKE '%user1%' and priority='normal';

 -- Initialize the @max variable. We'll use this variable in the next WHILE loop.
 SET @max := (select COUNT(ID) FROM myTable);
 set @tmp_count := 0;
 -- Loop
 WHILE @counter <= @max DO
     -- we want to count all message received by user2 and sent by user1
     -- the last 'or' treats the case of a direct message
     set @tmp_count := (SELECT count(*) from event_log where type_id=2 and sip_to LIKE '%user2%' and priority='normal' and ((sip_from=(select myTable.sip_to from myTable where id=@counter)  and call_id=(select myTable.call_id from myTable where id=@counter))) or sip_from LIKE '%user1%' );
     set @ev_log_counter := @ev_log_counter + @tmp_count;

     SET @counter := @counter + 1;
  END WHILE;
 select @ev_log_counter;
 drop table myTable;

END//
delimiter ;
 
 
call getMessageNbSentUser1ToUser2('user1', 'user2');
call getMessageNbSentUser1ToUser2('user2', 'user1');
Tags: