horde zu Dovecot kompatibel machen

[Xcantion]

Hallo @ all ich bräuchte da mal eure hilfe ich habe eine datenstruktur von 4 tabellen .... und müsste dafür 3,4,5 zeilen anpassen jedoch bin ich kein sql´er und hoffe ihr könnt mir dabei helfen.... den ich kenne mich in mysql nur sehr stark begrenzt aus ....

Tabellen:

--
-- Stellvertreter-Struktur des Views `view_aliases`
--
CREATE TABLE `view_aliases` (
`email` varchar(91)
,`destination` varchar(80)
);
-- --------------------------------------------------------

--
-- Stellvertreter-Struktur des Views `view_users`
--
CREATE TABLE `view_users` (
`email` varchar(91)
,`password` varchar(32)
);
-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `virtual_aliases`
--

CREATE TABLE `virtual_aliases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `source` varchar(40) NOT NULL,
  `destination` varchar(80) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `domain_id` (`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `virtual_domains`
--

CREATE TABLE `virtual_domains` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `virtual_users`
--

CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `user` varchar(40) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

Hier der code der angepasst werden muss...

[B][U]If you are not using domains, then use the following queries:[/U][/B]
[U]For query_auth, enter: [/U]
SELECT * FROM users WHERE userid = L AND password = MD5(P); 

[U]For query_add, enter:[/U] 
INSERT INTO users (userid,password,home) VALUES (L, MD5(P), '/home/L'); [I]NB: You may need to change the "home" value to point to their home directory or file space[/I] 

[U]For query_getpw, enter:[/U] 
SELECT password FROM users WHERE userid = L; 

[U]For query_update, enter: [/U]
UPDATE users SET userid = L WHERE userid = O) LIMIT 1; 

[U]For query_resetpassword, enter:[/U] 
UPDATE users SET password = MD5(P) WHERE userid = L; 

[U]For query_remove, enter:[/U] 
DELETE FROM users WHERE userid = L; 

[U]For query_list, enter:[/U] 
SELECT * FROM users; 

[U]For query_exists, enter:[/U] 
SELECT 1 FROM users WHERE userid = L; 

[B][U]If you are using domains, then use the following queries:[/U][/B]
[U]For query_auth, enter: [/U]
SELECT * FROM users WHERE userid = SUBSTRING_INDEX(L, '@', 1) AND domain = SUBSTRING_INDEX(L, '@', -1) AND password = MD5(P); 

[U]For query_add, enter:[/U] 
INSERT INTO users (domain,userid,password,home) VALUES ( SUBSTRING_INDEX(L, '@', -1), SUBSTRING_INDEX(L, '@', 1), MD5(P), '/home/L'); NB: You may need to change the "home" value to point to their home directory or file space 

[U]For query_getpw, enter:[/U] 
SELECT password FROM users WHERE userid = SUBSTRING_INDEX(L, '@', 1) AND domain = SUBSTRING_INDEX(L, '@', -1); 

[U]For query_update, enter:[/U] 
UPDATE users SET userid = SUBSTRING_INDEX(L, '@', 1) AND domain = SUBSTRING_INDEX(L, '@', -1) WHERE userid = SUBSTRING_INDEX(O, '@', 1) AND domain = SUBSTRING_INDEX(O, '@', -1); 

[U]For query_resetpassword, enter:[/U] 
UPDATE users SET password = MD5(P) WHERE userid = SUBSTRING_INDEX(L, '@', 1) AND domain = SUBSTRING_INDEX(L, '@', -1); 

[U]For query_remove, enter:[/U] 
DELETE FROM users WHERE userid = SUBSTRING_INDEX(L, '@', 1) AND domain = SUBSTRING_INDEX(L, '@', -1); 

[U]For query_list, enter:[/U] 
SELECT * FROM users; 

[U]For query_exists, enter:[/U] 
SELECT 1 FROM users WHERE SUBSTRING_INDEX(L, '@', 1) AND domain = SUBSTRING_INDEX(L, '@', -1);

So hier habe ich noch ein bisschen was schoneinmal angepasst wurde aus dem jahre 2007 ... aber da sich hier und da doch einiges an den tabellen und sql versionen getan hat könnte das mal ne überarbeitung gebrauchen...
zumal auch einiges fehlt hier:

Alte damalige Version

[U]Authenticate the user[/U]
SELECT * FROM view_users WHERE email = \L AND password = MD5(\P);

[U]Add user[/U]
[I]Ugly: hardcoded domain-id.[/I]
INSERT INTO mailserver.virtual_users (id,domain_id,user,password) VALUES (NULL, 1, \L, MD5(\P));

[U]Update user[/U]
UPDATE mailserver.virtual_users SET password = MD5(\P) WHERE virtual_users.user = TRIM('@example.org' FROM \L) LIMIT 1;
[I]I think it makes no sense to change the basic username, so the only thing you can do here (that is, relevant to the mailserver database) is update your password. Attention, hardcoded domain.[/I]

[U]Remove user[/U]
DELETE FROM mailserver.virtual_users WHERE virtual_users.user = TRIM('@example.org' FROM \L) LIMIT 1;

[U]List users[/U]
SELECT * FROM view_users;

und ungefähr so sah der passende ausschnitt dann in der config file aus aus (damals)

$conf['auth']['params']['query_auth'] = 'SELECT * FROM view_users WHERE email = \L AND password = MD5(\P);';

$conf['auth']['params']['query_add'] = 'INSERT INTO mailserver.virtual_users (id,domain_id,user,password) VALUES (NULL, 1, \L, MD5(\P));';

$conf['auth']['params']['query_update'] = 'UPDATE mailserver.virtual_users SET password = MD5(\P) WHERE virtual_users.user = TRIM('@example.org' FROM \L) LIMIT 1;';

$conf['auth']['params']['query_remove'] = 'DELETE FROM mailserver.virtual_users WHERE virtual_users.user = TRIM('@example.org' FROM \L) LIMIT 1;';

$conf['auth']['params']['query_list'] = 'SELECT * FROM view_users;';

Ich würde gerne folgendes haben die als 1. genannten abfragen neu anapssen ... und so anpassen das es auch multidomain fähig ist da ich mehrere domains anbieten kann mit meinem mailserver ... den hier unten in dem veralteten beispiel ist eine domain hardcoded was problematisch ist...