45 lines
2.0 KiB
SQL
45 lines
2.0 KiB
SQL
CREATE TABLE `TelegramBots` (
|
|
`Name` varchar(255) NOT NULL,
|
|
`Token` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`Name`));
|
|
|
|
CREATE TABLE `TelegramRecipients` (
|
|
`Name` varchar(255) NOT NULL,
|
|
`BotName` varchar(255) DEFAULT NULL,
|
|
`Type` enum('Person','Group') DEFAULT 'Person',
|
|
`ChatId` double NOT NULL,
|
|
UNIQUE KEY `Name` (`Name`,`BotName`),
|
|
KEY `BotName` (`BotName`),
|
|
CONSTRAINT `TelegramRecipients_ibfk_1` FOREIGN KEY (`BotName`) REFERENCES `TelegramBots` (`Name`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
CREATE TABLE `TelegramInbox` (
|
|
`Id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`RecipientBotName` varchar(255) DEFAULT NULL,
|
|
`Sender` varchar(255) DEFAULT NULL,
|
|
`Message` varchar(4096) DEFAULT NULL,
|
|
`Status` enum('Unread','Read') DEFAULT 'Unread',
|
|
`LastUpdated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`Id`),
|
|
KEY `RecipientBotName` (`RecipientBotName`),
|
|
KEY `Sender` (`Sender`),
|
|
CONSTRAINT `TelegramInbox_ibfk_1` FOREIGN KEY (`RecipientBotName`) REFERENCES `TelegramBots` (`Name`),
|
|
CONSTRAINT `TelegramInbox_ibfk_2` FOREIGN KEY (`Sender`) REFERENCES `TelegramRecipients` (`Name`)
|
|
);
|
|
|
|
CREATE TABLE `TelegramOutbox` (
|
|
`Id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`SenderBotName` varchar(255) NOT NULL,
|
|
`RecipientName` varchar(255) NOT NULL,
|
|
`Message` varchar(1024) DEFAULT NULL,
|
|
`Format` enum('Text','Markdown') DEFAULT 'Markdown',
|
|
`Status` enum('Unsent','Sent','Error') DEFAULT 'Unsent',
|
|
`ErrorMsg` varchar(255) DEFAULT NULL,
|
|
`LastUpdated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`Id`),
|
|
KEY `SenderBotName` (`SenderBotName`),
|
|
KEY `RecipientName` (`RecipientName`),
|
|
CONSTRAINT `TelegramOutbox_ibfk_1` FOREIGN KEY (`SenderBotName`) REFERENCES `TelegramBots` (`Name`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT `TelegramOutbox_ibfk_2` FOREIGN KEY (`RecipientName`) REFERENCES `TelegramRecipients` (`Name`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
|