mirror of https://github.com/sipwise/db-schema.git
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
44 lines
1.0 KiB
44 lines
1.0 KiB
USE prosody;
|
|
SET autocommit=0;
|
|
|
|
DELIMITER //
|
|
|
|
CREATE FUNCTION UuidToBin(_uuid BINARY(36))
|
|
RETURNS BINARY(16)
|
|
LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER
|
|
RETURN
|
|
UNHEX(CONCAT(
|
|
SUBSTR(_uuid, 15, 4),
|
|
SUBSTR(_uuid, 10, 4),
|
|
SUBSTR(_uuid, 1, 8),
|
|
SUBSTR(_uuid, 20, 4),
|
|
SUBSTR(_uuid, 25) ));
|
|
|
|
CREATE FUNCTION UuidFromBin(_bin BINARY(16))
|
|
RETURNS BINARY(36)
|
|
LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER
|
|
RETURN
|
|
LCASE(CONCAT_WS('-',
|
|
HEX(SUBSTR(_bin, 5, 4)),
|
|
HEX(SUBSTR(_bin, 3, 2)),
|
|
HEX(SUBSTR(_bin, 1, 2)),
|
|
HEX(SUBSTR(_bin, 9, 2)),
|
|
HEX(SUBSTR(_bin, 11))
|
|
));
|
|
|
|
//
|
|
DELIMITER ;
|
|
|
|
CREATE TABLE IF NOT EXISTS `sipwise_mam` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
`username` varchar(255) NOT NULL,
|
|
`key` BINARY(16) NOT NULL UNIQUE,
|
|
`stanza` text NOT NULL,
|
|
`epoch` int(11) NOT NULL,
|
|
`with` varchar(255),
|
|
PRIMARY KEY (`id`),
|
|
INDEX(`username`)
|
|
) ENGINE=InnoDB;
|
|
|
|
COMMIT;
|