Natenoms Wiki

Weil Teilen Spaß macht :)

Benutzer-Werkzeuge

Webseiten-Werkzeuge


Seitenleiste

Übersetzungen dieser Seite:

Navigation



Lizenz dieses Wikis
Über dieses Wiki
Feed des Wikis
Impressum


Was gerade in meinem Blog geschieht:

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.

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: 2015/11/04 13:06 (Externe Bearbeitung)