mumble:benutzerhandbuch:murmur:datenbank

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;
mumble/benutzerhandbuch/murmur/datenbank.txt · Zuletzt geändert: 2022/10/27 14:21 von 127.0.0.1

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki

Dieses Wiki wurde archiviert und wird nicht mehr gepflegt. Mein neues Wiki gibt es auf wiki.natenom.de. Details auf der Startseite.