From d51281739e86093fb6164465db8e15a989481c3a Mon Sep 17 00:00:00 2001 From: Frank Brehm Date: Fri, 16 Jul 2010 14:37:03 +0000 Subject: [PATCH] =?utf8?q?Datenbankmodell=20vervollst=C3=A4ndigt?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit git-svn-id: http://svn.brehm-online.com/svn/my-stuff/postfix-maps/trunk@101 ec8d2aa5-1599-4edb-8739-2b3a1bc399aa --- db/vmail-db.sql | 216 +++++++++++++++++++++ lib/FrBr/Postfix/Db/Result/Canonical.pm | 27 +++ lib/FrBr/Postfix/Db/Result/Domain.pm | 27 +++ lib/FrBr/Postfix/Db/Result/RecipientBcc.pm | 27 +++ lib/FrBr/Postfix/Db/Result/Relocated.pm | 27 +++ lib/FrBr/Postfix/Db/Result/SenderBcc.pm | 27 +++ lib/FrBr/Postfix/Db/Result/Transport.pm | 27 +++ lib/FrBr/Postfix/Db/Result/User.pm | 36 ++++ lib/FrBr/Postfix/Db/Result/Virtual.pm | 27 +++ 9 files changed, 441 insertions(+) create mode 100644 db/vmail-db.sql create mode 100644 lib/FrBr/Postfix/Db/Result/Canonical.pm create mode 100644 lib/FrBr/Postfix/Db/Result/Domain.pm create mode 100644 lib/FrBr/Postfix/Db/Result/RecipientBcc.pm create mode 100644 lib/FrBr/Postfix/Db/Result/Relocated.pm create mode 100644 lib/FrBr/Postfix/Db/Result/SenderBcc.pm create mode 100644 lib/FrBr/Postfix/Db/Result/Transport.pm create mode 100644 lib/FrBr/Postfix/Db/Result/User.pm create mode 100644 lib/FrBr/Postfix/Db/Result/Virtual.pm diff --git a/db/vmail-db.sql b/db/vmail-db.sql new file mode 100644 index 0000000..d66f447 --- /dev/null +++ b/db/vmail-db.sql @@ -0,0 +1,216 @@ +-- phpMyAdmin SQL Dump +-- version 3.3.2 +-- http://www.phpmyadmin.net +-- +-- Host: localhost +-- Erstellungszeit: 16. Juli 2010 um 16:03 +-- Server Version: 5.1.44 +-- PHP-Version: 5.2.13-pl0-gentoo + +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; + +-- +-- Datenbank: `vmail` +-- + +-- -------------------------------------------------------- + +-- +-- Tabellenstruktur für Tabelle `alias` +-- +-- Erzeugt am: 26. November 2007 um 17:49 +-- Aktualisiert am: 24. Februar 2010 um 11:06 +-- Letzter Check am: 24. Februar 2010 um 11:06 +-- + +DROP TABLE IF EXISTS `alias`; +CREATE TABLE IF NOT EXISTS `alias` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `alias` varchar(128) NOT NULL DEFAULT '', + `destination` varchar(128) NOT NULL DEFAULT '', + `enabled` enum('y','n') NOT NULL DEFAULT 'y', + `changed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + KEY `alias` (`alias`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Lokale Email-Aliases und mailman Alias-Informationen' AUTO_INCREMENT=39 ; + +-- -------------------------------------------------------- + +-- +-- Tabellenstruktur für Tabelle `canonical` +-- +-- Erzeugt am: 27. November 2007 um 13:31 +-- Aktualisiert am: 24. Februar 2010 um 11:06 +-- Letzter Check am: 24. Februar 2010 um 11:06 +-- + +DROP TABLE IF EXISTS `canonical`; +CREATE TABLE IF NOT EXISTS `canonical` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `email` varchar(128) NOT NULL DEFAULT '', + `destination` varchar(128) NOT NULL DEFAULT '', + `enabled` enum('y','n') NOT NULL DEFAULT 'y', + `changed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + KEY `email` (`email`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Berichtigung von E-Mail-Adressen (Sender + Empfänger)' AUTO_INCREMENT=1 ; + +-- -------------------------------------------------------- + +-- +-- Tabellenstruktur für Tabelle `domains` +-- +-- Erzeugt am: 27. November 2007 um 14:06 +-- Aktualisiert am: 24. Februar 2010 um 11:06 +-- Letzter Check am: 24. Februar 2010 um 11:06 +-- + +DROP TABLE IF EXISTS `domains`; +CREATE TABLE IF NOT EXISTS `domains` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `domain` varchar(128) NOT NULL DEFAULT '', + `type` enum('local','valias','vmbox','relay') NOT NULL DEFAULT 'local' COMMENT 'Domänentyp', + `enabled` enum('y','n') NOT NULL DEFAULT 'y', + `changed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `domain` (`domain`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Alle Domänen, für die Postfix zuständig ist' AUTO_INCREMENT=18 ; + +-- -------------------------------------------------------- + +-- +-- Tabellenstruktur für Tabelle `recipient_bcc` +-- +-- Erzeugt am: 26. November 2007 um 17:47 +-- Aktualisiert am: 24. Februar 2010 um 11:06 +-- Letzter Check am: 24. Februar 2010 um 11:06 +-- + +DROP TABLE IF EXISTS `recipient_bcc`; +CREATE TABLE IF NOT EXISTS `recipient_bcc` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `email` varchar(128) NOT NULL DEFAULT '', + `destination` varchar(128) NOT NULL DEFAULT '', + `enabled` enum('y','n') NOT NULL DEFAULT 'y', + `changed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `email` (`email`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Empfänger-Adressen mit automatischem BCC' AUTO_INCREMENT=1 ; + +-- -------------------------------------------------------- + +-- +-- Tabellenstruktur für Tabelle `relocated` +-- +-- Erzeugt am: 26. November 2007 um 17:03 +-- Aktualisiert am: 24. Februar 2010 um 11:06 +-- Letzter Check am: 24. Februar 2010 um 11:06 +-- + +DROP TABLE IF EXISTS `relocated`; +CREATE TABLE IF NOT EXISTS `relocated` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `email` varchar(128) NOT NULL DEFAULT '', + `destination` varchar(128) NOT NULL DEFAULT '', + `enabled` enum('y','n') NOT NULL DEFAULT 'y', + `changed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + KEY `email` (`email`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Email-Zuweisung für umgezogene Benutzer' AUTO_INCREMENT=1 ; + +-- -------------------------------------------------------- + +-- +-- Tabellenstruktur für Tabelle `sender_bcc` +-- +-- Erzeugt am: 26. November 2007 um 17:47 +-- Aktualisiert am: 24. Februar 2010 um 11:06 +-- Letzter Check am: 24. Februar 2010 um 11:06 +-- + +DROP TABLE IF EXISTS `sender_bcc`; +CREATE TABLE IF NOT EXISTS `sender_bcc` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `email` varchar(128) NOT NULL DEFAULT '', + `destination` varchar(128) NOT NULL DEFAULT '', + `enabled` enum('y','n') NOT NULL DEFAULT 'y', + `changed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `email` (`email`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Sender-Adressen mit automatischem BCC' AUTO_INCREMENT=1 ; + +-- -------------------------------------------------------- + +-- +-- Tabellenstruktur für Tabelle `transport` +-- +-- Erzeugt am: 26. November 2007 um 17:49 +-- Aktualisiert am: 24. Februar 2010 um 11:06 +-- Letzter Check am: 24. Februar 2010 um 11:06 +-- + +DROP TABLE IF EXISTS `transport`; +CREATE TABLE IF NOT EXISTS `transport` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `domain` varchar(128) NOT NULL DEFAULT '', + `destination` varchar(128) NOT NULL DEFAULT '', + `enabled` enum('y','n') NOT NULL DEFAULT 'y', + `changed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `domain` (`domain`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Mail-Transport-Informationen für alle gehosteten Domains' AUTO_INCREMENT=5 ; + +-- -------------------------------------------------------- + +-- +-- Tabellenstruktur für Tabelle `users` +-- +-- Erzeugt am: 26. November 2007 um 17:57 +-- Aktualisiert am: 24. Februar 2010 um 11:06 +-- Letzter Check am: 24. Februar 2010 um 11:06 +-- + +DROP TABLE IF EXISTS `users`; +CREATE TABLE IF NOT EXISTS `users` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `email` varchar(128) NOT NULL DEFAULT '', + `clear` varchar(128) NOT NULL DEFAULT '', + `name` tinytext NOT NULL, + `uid` int(11) NOT NULL DEFAULT '1023', + `gid` int(11) NOT NULL DEFAULT '1023', + `homedir` tinytext NOT NULL, + `maildir` tinytext NOT NULL, + `quota` tinytext NOT NULL, + `postfix` enum('n','y') NOT NULL DEFAULT 'y', + `enabled` enum('y','n') NOT NULL DEFAULT 'y', + `changed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `email` (`email`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Alle Benutzerinformationen' AUTO_INCREMENT=2 ; + +-- -------------------------------------------------------- + +-- +-- Tabellenstruktur für Tabelle `virtual` +-- +-- Erzeugt am: 26. November 2007 um 17:49 +-- Aktualisiert am: 07. Juli 2010 um 23:34 +-- Letzter Check am: 24. Februar 2010 um 11:06 +-- + +DROP TABLE IF EXISTS `virtual`; +CREATE TABLE IF NOT EXISTS `virtual` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `email` varchar(128) NOT NULL DEFAULT '', + `destination` varchar(128) NOT NULL DEFAULT '', + `enabled` enum('y','n') NOT NULL DEFAULT 'y', + `changed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + KEY `email` (`email`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Virtuelle E-Mail-Domain Aliase' AUTO_INCREMENT=7 ; diff --git a/lib/FrBr/Postfix/Db/Result/Canonical.pm b/lib/FrBr/Postfix/Db/Result/Canonical.pm new file mode 100644 index 0000000..9e96e2e --- /dev/null +++ b/lib/FrBr/Postfix/Db/Result/Canonical.pm @@ -0,0 +1,27 @@ +package FrBr::Postfix::Db::Result::Canonical; + +# $Id$ +# $URL$ + +use strict; +use warnings; + +use base 'DBIx::Class'; + +__PACKAGE__->load_components("Core"); +__PACKAGE__->table("canonical"); +__PACKAGE__->add_columns( + "id" => { data_type => "INT", default_value => undef, is_nullable => 0, size => 10 }, + "email" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "destination" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "enabled" => { data_type => "ENUM", default_value => 'y', is_nullable => 0, size => 1, }, + "changed_at" => { data_type => "TIMESTAMP", default_value => undef, is_nullable => 0, size => 19, }, +); + +__PACKAGE__->set_primary_key("id"); + +1; + +__END__ + +# vim: noai : ts=4 fenc=utf-8 filetype=perl expandtab : diff --git a/lib/FrBr/Postfix/Db/Result/Domain.pm b/lib/FrBr/Postfix/Db/Result/Domain.pm new file mode 100644 index 0000000..20ffaf4 --- /dev/null +++ b/lib/FrBr/Postfix/Db/Result/Domain.pm @@ -0,0 +1,27 @@ +package FrBr::Postfix::Db::Result::Domain; + +# $Id$ +# $URL$ + +use strict; +use warnings; + +use base 'DBIx::Class'; + +__PACKAGE__->load_components("Core"); +__PACKAGE__->table("domains"); +__PACKAGE__->add_columns( + "id" => { data_type => "INT", default_value => undef, is_nullable => 0, size => 10 }, + "domain" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "type" => { data_type => "ENUM", default_value => 'local', is_nullable => 0, size => 1, }, + "enabled" => { data_type => "ENUM", default_value => 'y', is_nullable => 0, size => 1, }, + "changed_at" => { data_type => "TIMESTAMP", default_value => undef, is_nullable => 0, size => 19, }, +); + +__PACKAGE__->set_primary_key("id"); + +1; + +__END__ + +# vim: noai : ts=4 fenc=utf-8 filetype=perl expandtab : diff --git a/lib/FrBr/Postfix/Db/Result/RecipientBcc.pm b/lib/FrBr/Postfix/Db/Result/RecipientBcc.pm new file mode 100644 index 0000000..37acea0 --- /dev/null +++ b/lib/FrBr/Postfix/Db/Result/RecipientBcc.pm @@ -0,0 +1,27 @@ +package FrBr::Postfix::Db::Result::RecipientBcc; + +# $Id$ +# $URL$ + +use strict; +use warnings; + +use base 'DBIx::Class'; + +__PACKAGE__->load_components("Core"); +__PACKAGE__->table("recipient_bcc"); +__PACKAGE__->add_columns( + "id" => { data_type => "INT", default_value => undef, is_nullable => 0, size => 10 }, + "email" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "destination" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "enabled" => { data_type => "ENUM", default_value => 'y', is_nullable => 0, size => 1, }, + "changed_at" => { data_type => "TIMESTAMP", default_value => undef, is_nullable => 0, size => 19, }, +); + +__PACKAGE__->set_primary_key("id"); + +1; + +__END__ + +# vim: noai : ts=4 fenc=utf-8 filetype=perl expandtab : diff --git a/lib/FrBr/Postfix/Db/Result/Relocated.pm b/lib/FrBr/Postfix/Db/Result/Relocated.pm new file mode 100644 index 0000000..86e4d30 --- /dev/null +++ b/lib/FrBr/Postfix/Db/Result/Relocated.pm @@ -0,0 +1,27 @@ +package FrBr::Postfix::Db::Result::Relocated; + +# $Id$ +# $URL$ + +use strict; +use warnings; + +use base 'DBIx::Class'; + +__PACKAGE__->load_components("Core"); +__PACKAGE__->table("relocated"); +__PACKAGE__->add_columns( + "id" => { data_type => "INT", default_value => undef, is_nullable => 0, size => 10 }, + "email" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "destination" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "enabled" => { data_type => "ENUM", default_value => 'y', is_nullable => 0, size => 1, }, + "changed_at" => { data_type => "TIMESTAMP", default_value => undef, is_nullable => 0, size => 19, }, +); + +__PACKAGE__->set_primary_key("id"); + +1; + +__END__ + +# vim: noai : ts=4 fenc=utf-8 filetype=perl expandtab : diff --git a/lib/FrBr/Postfix/Db/Result/SenderBcc.pm b/lib/FrBr/Postfix/Db/Result/SenderBcc.pm new file mode 100644 index 0000000..f68af97 --- /dev/null +++ b/lib/FrBr/Postfix/Db/Result/SenderBcc.pm @@ -0,0 +1,27 @@ +package FrBr::Postfix::Db::Result::SenderBcc; + +# $Id$ +# $URL$ + +use strict; +use warnings; + +use base 'DBIx::Class'; + +__PACKAGE__->load_components("Core"); +__PACKAGE__->table("sender_bcc"); +__PACKAGE__->add_columns( + "id" => { data_type => "INT", default_value => undef, is_nullable => 0, size => 10 }, + "email" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "destination" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "enabled" => { data_type => "ENUM", default_value => 'y', is_nullable => 0, size => 1, }, + "changed_at" => { data_type => "TIMESTAMP", default_value => undef, is_nullable => 0, size => 19, }, +); + +__PACKAGE__->set_primary_key("id"); + +1; + +__END__ + +# vim: noai : ts=4 fenc=utf-8 filetype=perl expandtab : diff --git a/lib/FrBr/Postfix/Db/Result/Transport.pm b/lib/FrBr/Postfix/Db/Result/Transport.pm new file mode 100644 index 0000000..f3a7b67 --- /dev/null +++ b/lib/FrBr/Postfix/Db/Result/Transport.pm @@ -0,0 +1,27 @@ +package FrBr::Postfix::Db::Result::Transport; + +# $Id$ +# $URL$ + +use strict; +use warnings; + +use base 'DBIx::Class'; + +__PACKAGE__->load_components("Core"); +__PACKAGE__->table("transport"); +__PACKAGE__->add_columns( + "id" => { data_type => "INT", default_value => undef, is_nullable => 0, size => 10 }, + "domain" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "destination" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "enabled" => { data_type => "ENUM", default_value => 'y', is_nullable => 0, size => 1, }, + "changed_at" => { data_type => "TIMESTAMP", default_value => undef, is_nullable => 0, size => 19, }, +); + +__PACKAGE__->set_primary_key("id"); + +1; + +__END__ + +# vim: noai : ts=4 fenc=utf-8 filetype=perl expandtab : diff --git a/lib/FrBr/Postfix/Db/Result/User.pm b/lib/FrBr/Postfix/Db/Result/User.pm new file mode 100644 index 0000000..548d939 --- /dev/null +++ b/lib/FrBr/Postfix/Db/Result/User.pm @@ -0,0 +1,36 @@ +package FrBr::Postfix::Db::Result::User; + +# $Id$ +# $URL$ + +use strict; +use warnings; + +use base 'DBIx::Class'; + +__PACKAGE__->load_components("Core"); +__PACKAGE__->table("users"); +__PACKAGE__->add_columns( + "id" => { data_type => "INT", default_value => undef, is_nullable => 0, size => 10 }, + "email" => { data_type => "VARCHAR", default_value => '', is_nullable => 0, size => 128, }, + "clear" => { data_type => "VARCHAR", default_value => '', is_nullable => 0, size => 128, }, + "name" => { data_type => "TINYTEXT", default_value => '', is_nullable => 0, size => 65536, }, + "uid" => { data_type => "INT", default_value => 1023, is_nullable => 0, size => 10 }, + "gid" => { data_type => "INT", default_value => 1023, is_nullable => 0, size => 10 }, + "homedir" => { data_type => "TINYTEXT", default_value => undef, is_nullable => 0, size => 65536, }, + "maildir" => { data_type => "TINYTEXT", default_value => undef, is_nullable => 0, size => 65536, }, + "quota" => { data_type => "TINYTEXT", default_value => undef, is_nullable => 0, size => 65536, }, + "postfix" => { data_type => "ENUM", default_value => 'y', is_nullable => 0, size => 1, }, + "enabled" => { data_type => "ENUM", default_value => 'y', is_nullable => 0, size => 1, }, + "changed_at" => { data_type => "TIMESTAMP", default_value => undef, is_nullable => 0, size => 19, }, +); + +__PACKAGE__->set_primary_key("id"); + +__PACKAGE__->add_unique_constraint("email", ["email"]); + +1; + +__END__ + +# vim: noai : ts=4 fenc=utf-8 filetype=perl expandtab : diff --git a/lib/FrBr/Postfix/Db/Result/Virtual.pm b/lib/FrBr/Postfix/Db/Result/Virtual.pm new file mode 100644 index 0000000..dffaa64 --- /dev/null +++ b/lib/FrBr/Postfix/Db/Result/Virtual.pm @@ -0,0 +1,27 @@ +package FrBr::Postfix::Db::Result::Virtual; + +# $Id$ +# $URL$ + +use strict; +use warnings; + +use base 'DBIx::Class'; + +__PACKAGE__->load_components("Core"); +__PACKAGE__->table("virtual"); +__PACKAGE__->add_columns( + "id" => { data_type => "INT", default_value => undef, is_nullable => 0, size => 10 }, + "email" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "destination" => { data_type => "VARCHAR", default_value => undef, is_nullable => 0, size => 128, }, + "enabled" => { data_type => "ENUM", default_value => 'y', is_nullable => 0, size => 1, }, + "changed_at" => { data_type => "TIMESTAMP", default_value => undef, is_nullable => 0, size => 19, }, +); + +__PACKAGE__->set_primary_key("id"); + +1; + +__END__ + +# vim: noai : ts=4 fenc=utf-8 filetype=perl expandtab : -- 2.39.5