# HG changeset patch # User heiko # Date 1219243366 0 # Node ID 2865079a223c293ac4343fd4f5e5306919ef5cfc # Parent bcd8d4500e24b80101272321c9e642abfcf0d183 - nun wird noch eine extra-Tabelle gepflegt, mit den Vorgangs-Nummern - dafür gibt es auch einen view diff -r bcd8d4500e24 -r 2865079a223c db.mysql --- a/db.mysql Wed Aug 20 14:24:17 2008 +0000 +++ b/db.mysql Wed Aug 20 14:42:46 2008 +0000 @@ -26,10 +26,27 @@ `typ` int(11) default NULL, `vorgang` int(11) default NULL, KEY `message_id` (`message_id`), + KEY `vorgang` (`vorgang`), + KEY `typ` (`typ`), + KEY `mandant` (`mandant`), CONSTRAINT `eht_ibfk_1` FOREIGN KEY (`message_id`) REFERENCES `message` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Temporary table structure for view `eht_message` +-- + +DROP TABLE IF EXISTS `eht_message`; +/*!50001 DROP VIEW IF EXISTS `eht_message`*/; +/*!50001 CREATE TABLE `eht_message` ( + `message_id` bigint(20), + `mandant` int(11), + `typ` int(11), + `vorgang` int(11), + `content` longtext +) */; + +-- -- Temporary table structure for view `header` -- @@ -82,6 +99,16 @@ ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Final view structure for view `eht_message` +-- + +/*!50001 DROP TABLE IF EXISTS `eht_message`*/; +/*!50001 DROP VIEW IF EXISTS `eht_message`*/; +/*!50001 CREATE ALGORITHM=UNDEFINED */ +/*!50013 DEFINER=`mail`@`%` SQL SECURITY DEFINER */ +/*!50001 VIEW `eht_message` AS select `eht`.`message_id` AS `message_id`,`eht`.`mandant` AS `mandant`,`eht`.`typ` AS `typ`,`eht`.`vorgang` AS `vorgang`,`message`.`content` AS `content` from (`eht` left join `message` on((`eht`.`message_id` = `message`.`id`))) */; + +-- -- Final view structure for view `header` -- @@ -100,4 +127,4 @@ /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2008-08-20 14:18:22 +-- Dump completed on 2008-08-20 14:41:50 diff -r bcd8d4500e24 -r 2865079a223c mail2db --- a/mail2db Wed Aug 20 14:24:17 2008 +0000 +++ b/mail2db Wed Aug 20 14:42:46 2008 +0000 @@ -103,6 +103,13 @@ VALUES(?, (SELECT id FROM header_field WHERE name = ?), ?, ?) } ); + my $insert_eht = $DBH->prepare( + qq{ + INSERT INTO eht + (message_id, mandant, typ, vorgang) + VALUES(?, ?, ?, ?) + } + ); # first insert the message and get the database message id my $msg_id; @@ -137,6 +144,12 @@ $insert_header_id->execute($tag); $insert_message_header->execute( $msg_id, $tag, $idx, $header ); } + + # ok and now EHT specific + if ($tag eq "subject" and $header =~ /#(\d{2})-(\d{2}):(\d+)#/) { + syslog( LOG_DEBUG, "msgid:$msg_id: mandant:$1 type:$2 vorgang:$3"); + $insert_eht->execute($msg_id, $1, $2, $3); + } } }