Inhaltsverzeichnis
Datenbank des Mumble-Servers
Per Voreinstellung verwendet ein Mumble-Server eine SQLite-Datenbank, es ist aber auch möglich MySQL zu verwenden, dazu müssen einige der Datenbankeinstellungen in der .ini-Konfigurationsdatei geändert werden.
Von den Enwicklern von Mumble wird empfohlen, die SQlite-Datenbank zu verwenden, da diese am besten getestet ist und da mit dieser auch bei Updates alle geänderten Werte automatisch konvertiert werden. Bei MySQL der PostgreSQL ist das nicht immer der Fall.
Es war einmal vor langer Zeit … da änderte man Werte noch mit irgendwelchen Scripten direkt in der Datenbank. Dies sollte man mittlerweile nicht mehr tun, da die Inhalte der Datenbank unter anderem gecached werden und so Inkonsistenzen entstehen könnten.
Enthaltene Daten
In der Datenbank des Mumble-Servers sind folgende Daten enthalten:
Tabelle | Inhalt |
---|---|
acl | ACL |
bans | Bannliste |
channels | Kanäle |
channel_links | Kanalverknüpfungen |
channel_info | Kanalbeschreibungen |
slog | Serverlogs |
config | Konfigurationseinstellungen |
groups | Gruppen |
group_members | Gruppenmitglieder |
servers | virtuelle Server |
users | Benutzer |
user_info | Benutzerkommentare, … |
Schema der Datenbank in Version 1.2.5 (sqlite)
.schema CREATE TABLE `acl` (`server_id` INTEGER NOT NULL, `channel_id` INTEGER NOT NULL, `priority` INTEGER, `user_id` INTEGER, `group_name` TEXT, `apply_here` INTEGER, `apply_sub` INTEGER, `grantpriv` INTEGER, `revokepriv` INTEGER); CREATE TABLE `bans` (`server_id` INTEGER NOT NULL, `base` BLOB, `mask` INTEGER, `name` TEXT, `hash` TEXT, `reason` TEXT, `start` DATE, `duration` INTEGER); CREATE TABLE `channel_info` (`server_id` INTEGER NOT NULL, `channel_id` INTEGER NOT NULL, `key` INTEGER, `value` TEXT); CREATE TABLE `channel_links` (`server_id` INTEGER NOT NULL, `channel_id` INTEGER NOT NULL, `link_id` INTEGER NOT NULL); CREATE TABLE `channels` (`server_id` INTEGER NOT NULL, `channel_id` INTEGER NOT NULL, `parent_id` INTEGER, `name` TEXT, `inheritacl` INTEGER); CREATE TABLE `config` (`server_id` INTEGER NOT NULL, `key` TEXT, `value` TEXT); CREATE TABLE `group_members` (`group_id` INTEGER NOT NULL, `server_id` INTEGER NOT NULL, `user_id` INTEGER NOT NULL, `addit` INTEGER); CREATE TABLE `groups` (`group_id` INTEGER PRIMARY KEY AUTOINCREMENT, `server_id` INTEGER NOT NULL, `name` TEXT, `channel_id` INTEGER NOT NULL, `inherit` INTEGER, `inheritable` INTEGER); CREATE TABLE `meta` (`keystring` TEXT PRIMARY KEY, `value` TEXT); CREATE TABLE `servers` (`server_id` INTEGER PRIMARY KEY AUTOINCREMENT); CREATE TABLE `slog`(`server_id` INTEGER NOT NULL, `msg` TEXT, `msgtime` DATE); CREATE TABLE `user_info` (`server_id` INTEGER NOT NULL, `user_id` INTEGER NOT NULL, `key` INTEGER, `value` TEXT); CREATE TABLE `users` (`server_id` INTEGER NOT NULL, `user_id` INTEGER NOT NULL, `name` TEXT NOT NULL, `pw` TEXT, `lastchannel` INTEGER, `texture` BLOB, `last_active` DATE); CREATE UNIQUE INDEX `acl_channel_pri` ON `acl`(`server_id`, `channel_id`, `priority`); CREATE UNIQUE INDEX `channel_id` ON `channels`(`server_id`, `channel_id`); CREATE UNIQUE INDEX `channel_info_id` ON `channel_info`(`server_id`, `channel_id`, `key`); CREATE UNIQUE INDEX `config_key` ON `config`(`server_id`, `key`); CREATE UNIQUE INDEX `groups_name_channels` ON `groups`(`server_id`, `channel_id`, `name`); CREATE INDEX `slog_time` ON `slog`(`msgtime`); CREATE UNIQUE INDEX `user_info_id` ON `user_info`(`server_id`, `user_id`, `key`); CREATE UNIQUE INDEX `users_id` ON `users` (`server_id`, `user_id`); CREATE UNIQUE INDEX `users_name` ON `users` (`server_id`,`name`); CREATE TRIGGER `acl_del_channel` AFTER DELETE ON `channels` FOR EACH ROW BEGIN DELETE FROM `acl` WHERE `channel_id` = OLD.`channel_id` AND `server_id` = OLD.`server_id`; END; CREATE TRIGGER `acl_del_user` AFTER DELETE ON `users` FOR EACH ROW BEGIN DELETE FROM `acl` WHERE `user_id` = OLD.`user_id` AND `server_id` = OLD.`server_id`; END; CREATE TRIGGER `bans_del_server` AFTER DELETE ON `servers` FOR EACH ROW BEGIN DELETE FROM `bans` WHERE `server_id` = OLD.`server_id`; END; CREATE TRIGGER `channel_info_del_channel` AFTER DELETE ON `channels` FOR EACH ROW BEGIN DELETE FROM `channel_info` WHERE `channel_id` = OLD.`channel_id` AND `server_id` = OLD.`server_id`; END; CREATE TRIGGER `channel_links_del_channel` AFTER DELETE ON `channels` FOR EACH ROW BEGIN DELETE FROM `channel_links` WHERE `server_id` = OLD.`server_id` AND (`channel_id` = OLD.`channel_id` OR `link_id` = OLD.`channel_id`); END; CREATE TRIGGER `channels_parent_del` AFTER DELETE ON `channels` FOR EACH ROW BEGIN DELETE FROM `channels` WHERE `parent_id` = OLD.`channel_id` AND `server_id` = OLD.`server_id`; UPDATE `users` SET `lastchannel`=0 WHERE `lastchannel` = OLD.`channel_id` AND `server_id` = OLD.`server_id`; END; CREATE TRIGGER `channels_server_del` AFTER DELETE ON `servers` FOR EACH ROW BEGIN DELETE FROM `channels` WHERE `server_id` = OLD.`server_id`; END; CREATE TRIGGER `config_server_del` AFTER DELETE ON `servers` FOR EACH ROW BEGIN DELETE FROM `config` WHERE `server_id` = OLD.`server_id`; END; CREATE TRIGGER `groups_del_channel` AFTER DELETE ON `channels` FOR EACH ROW BEGIN DELETE FROM `groups` WHERE `channel_id` = OLD.`channel_id` AND `server_id` = OLD.`server_id`; END; CREATE TRIGGER `groups_members_del_group` AFTER DELETE ON `groups` FOR EACH ROW BEGIN DELETE FROM `group_members` WHERE `group_id` = OLD.`group_id`; END; CREATE TRIGGER `groups_members_del_user` AFTER DELETE ON `users` FOR EACH ROW BEGIN DELETE FROM `group_members` WHERE `user_id` = OLD.`user_id` AND `server_id` = OLD.`server_id`; END; CREATE TRIGGER `slog_server_del` AFTER DELETE ON `servers` FOR EACH ROW BEGIN DELETE FROM `slog` WHERE `server_id` = OLD.`server_id`; END; CREATE TRIGGER `slog_timestamp` AFTER INSERT ON `slog` FOR EACH ROW BEGIN UPDATE `slog` SET `msgtime` = datetime('now') WHERE rowid = NEW.rowid; END; CREATE TRIGGER `user_info_del_user` AFTER DELETE ON `users` FOR EACH ROW BEGIN DELETE FROM `user_info` WHERE `user_id` = OLD.`user_id` AND `server_id` = OLD.`server_id`; END; CREATE TRIGGER `users_server_del` AFTER DELETE ON `servers` FOR EACH ROW BEGIN DELETE FROM `users` WHERE `server_id` = OLD.`server_id`; END; CREATE TRIGGER `users_update_timestamp` AFTER UPDATE OF `lastchannel` ON `users` FOR EACH ROW BEGIN UPDATE `users` SET `last_active` = datetime('now') WHERE `user_id` = OLD.`user_id` AND `server_id` = OLD.`server_id`; END;