From 88a07a009adcd5c2320900ea342d7682ada587ce Mon Sep 17 00:00:00 2001
From: Enrico Tassi <enrico.tassi@inria.fr>
Date: Mon, 2 May 2005 12:55:02 +0000
Subject: [PATCH] added the count table

---
 .../sql/create_mowgli_tables.mysql.sql        | 11 +++-
 helm/metadata/sql/fill_all_derived.sql        | 64 +++++++++++++++++++
 2 files changed, 73 insertions(+), 2 deletions(-)
 create mode 100644 helm/metadata/sql/fill_all_derived.sql

diff --git a/helm/metadata/sql/create_mowgli_tables.mysql.sql b/helm/metadata/sql/create_mowgli_tables.mysql.sql
index bc2e28460..d0846ec7c 100644
--- a/helm/metadata/sql/create_mowgli_tables.mysql.sql
+++ b/helm/metadata/sql/create_mowgli_tables.mysql.sql
@@ -21,11 +21,11 @@ CREATE TABLE objectName (
     value varchar(255) binary not null
 );
 CREATE TABLE no_inconcl_aux (
-    source varchar(255) binary not null,
+    source varchar(255) binary unique not null,
     no smallint(6) not null
 );
 CREATE TABLE no_concl_hyp (
-    source varchar(255) binary not null,
+    source varchar(255) binary unique not null,
     no smallint(6) not null
 );
 CREATE TABLE no_hyp (
@@ -37,6 +37,13 @@ CREATE TABLE owners (
     owner varchar(255) binary not null
 );
 
+CREATE TABLE count (
+    source varchar(255) binary unique not null,
+    conclusion smallint(6) not null,
+    hypothesis smallint(6) not null,
+    statement smallint(6) not null
+);
+
 CREATE INDEX refObj_source ON refObj (source);
 CREATE INDEX refObj_target ON refObj (h_occurrence);
 CREATE INDEX refObj_position ON refObj (h_position);
diff --git a/helm/metadata/sql/fill_all_derived.sql b/helm/metadata/sql/fill_all_derived.sql
new file mode 100644
index 000000000..708015b9a
--- /dev/null
+++ b/helm/metadata/sql/fill_all_derived.sql
@@ -0,0 +1,64 @@
+CREATE TABLE no_inconcl_aux_tmp (
+    source varchar(255) binary unique not null,
+    no smallint(6) not null
+);
+CREATE TABLE no_concl_hyp_tmp (
+    source varchar(255) binary unique not null,
+    no smallint(6) not null
+);
+CREATE TABLE no_hyp_tmp (
+    source varchar(255) binary unique not null,
+    no smallint(6) not null
+);
+
+INSERT INTO no_inconcl_aux_tmp
+SELECT source, COUNT(h_occurrence)
+FROM refObj
+WHERE
+  h_position='http://www.cs.unibo.it/helm/schemas/schema-helm#InConclusion'
+  OR h_position='http://www.cs.unibo.it/helm/schemas/schema-helm#MainConclusion'
+GROUP BY source;
+
+INSERT IGNORE INTO no_inconcl_aux_tmp
+SELECT source, 0
+FROM refObj
+GROUP BY source;
+
+INSERT INTO no_concl_hyp_tmp
+SELECT source, COUNT(DISTINCT h_occurrence)
+FROM refObj
+WHERE NOT (h_position="http://www.cs.unibo.it/helm/schemas/schema-helm#InBody")
+GROUP BY source;
+
+INSERT IGNORE INTO no_concl_hyp_tmp
+SELECT source, 0
+FROM refObj
+GROUP BY source;
+
+INSERT INTO no_hyp_tmp
+SELECT source, COUNT(DISTINCT h_occurrence)
+FROM refObj
+WHERE (h_position="http://www.cs.unibo.it/helm/schemas/schema-helm#MainHypothesis"
+    OR h_position="http://www.cs.unibo.it/helm/schemas/schema-helm#InHypothesis")
+GROUP BY source;
+
+INSERT IGNORE INTO no_hyp_tmp
+SELECT source, 0
+FROM refObj
+GROUP BY source;
+
+INSERT INTO count
+SELECT no_hyp_tmp.source, 
+       no_inconcl_aux_tmp.no, 
+       no_hyp_tmp.no, 
+       no_concl_hyp_tmp.no
+FROM no_hyp_tmp, no_concl_hyp_tmp, no_inconcl_aux_tmp
+WHERE no_hyp_tmp.source = no_concl_hyp_tmp.source AND
+      no_hyp_tmp.source = no_inconcl_aux_tmp.source;
+
+DROP TABLE no_hyp_tmp;
+DROP TABLE no_inconcl_aux_tmp;
+DROP TABLE no_concl_hyp_tmp;
+
+
+
-- 
2.39.2