# 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);
+			}
         }
     }