Changeset 537

Timestamp:

09/03/08 20:32:02 (16 years ago)

Author:

gkronber

Message:

added tables for items and statements, a class for items and methods to create items and statements and to retrieve items. ticket #261 (Items are stored multiple times in the result entries in the CEDMA DB)

Location:

trunk/sources

Files:

• HeuristicLab.CEDMA.DB.Interfaces/HeuristicLab.CEDMA.DB.Interfaces.csproj (modified) (2 diffs)
• HeuristicLab.CEDMA.DB.Interfaces/IDatabase.cs (modified) (2 diffs)
• HeuristicLab.CEDMA.DB.Interfaces/ItemEntry.cs (added)
• HeuristicLab.CEDMA.DB/Database.cs (modified) (2 diffs)

trunk/sources/HeuristicLab.CEDMA.DB.Interfaces/HeuristicLab.CEDMA.DB.Interfaces.csproj

@@ -4 +4 @@
     <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
     <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
-    <ProductVersion>9.0.21022</ProductVersion>
+    <ProductVersion>9.0.30729</ProductVersion>
     <SchemaVersion>2.0</SchemaVersion>
     <ProjectGuid>{4F9BB789-D561-436B-B226-2BF44B7D0804}</ProjectGuid>
@@ -55 +55 @@
   <ItemGroup>
     <Compile Include="AgentEntry.cs" />
+    <Compile Include="ItemEntry.cs" />
     <Compile Include="OperatorEntry.cs" />
     <Compile Include="ProcessStatus.cs" />

trunk/sources/HeuristicLab.CEDMA.DB.Interfaces/IDatabase.cs

@@ -26 +26 @@
 using System.ServiceModel;
 using System.Data;
+using System.Linq.Expressions;
 
 namespace HeuristicLab.CEDMA.DB.Interfaces {
@@ -80 +81 @@
 
     [OperationContract]
-    OperatorEntry GetOperator(long agentId);
+    OperatorEntry GetOperator(long id);
 
+    [OperationContract]
+    IList<ItemEntry> GetOntologyItems();
+
+    [OperationContract]
+    IList<ItemEntry> GetItems(Guid predicate, Guid property);
+
+    [OperationContract]
+    void LinkItems(ItemEntry subject, ItemEntry predicate, ItemEntry property);
   }
 }

trunk/sources/HeuristicLab.CEDMA.DB/Database.cs

@@ -66 +66 @@
             using(DbCommand cmd = cnn.CreateCommand()) {
               cmd.CommandText = "CREATE TABLE Operator (ID integer primary key autoincrement, Name text, RawData Blob)";
+              cmd.Transaction = t;
+              cmd.ExecuteNonQuery();
+            }
+            using(DbCommand cmd = cnn.CreateCommand()) {
+              cmd.CommandText = "CREATE TABLE Item (guid GUID primary key, RawData Blob)";
+              cmd.Transaction = t;
+              cmd.ExecuteNonQuery();
+            }
+            using(DbCommand cmd = cnn.CreateCommand()) {
+              cmd.CommandText = "CREATE TABLE Statement (subject GUID, predicate GUID, property GUID)";
               cmd.Transaction = t;
               cmd.ExecuteNonQuery();
@@ -619 +629 @@
     }
     #endregion
+
+    public IList<ItemEntry> GetOntologyItems() {
+      List<ItemEntry> ontologies = new List<ItemEntry>();
+      List<ItemEntry> items = new List<ItemEntry>();
+      rwLock.EnterReadLock();
+      try {
+        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(DbCommand c = cnn.CreateCommand()) {
+            c.CommandText = "Select guid, rawdata from Item,(select subject from Statement where subject=property) where subject=guid";
+            using(DbDataReader r = c.ExecuteReader()) {
+              while(r.Read()) {
+                ItemEntry ontology = new ItemEntry();
+                ontology.Guid = r.GetGuid(0);
+                ontology.RawData = (byte[])r.GetValue(1);
+                ontologies.Add(ontology);
+              }
+            }
+          }
+          foreach(ItemEntry ontology in ontologies) {
+            using(DbCommand c = cnn.CreateCommand()) {
+              c.CommandText = "Select guid, rawdata from Item,(select property from Statement where subject=@ontology) where guid=property";
+              DbParameter ontologyParam = c.CreateParameter();
+              ontologyParam.ParameterName = "@ontology";
+              ontologyParam.Value = ontology.Guid.ToString();
+              c.Parameters.Add(ontologyParam);
+              using(DbDataReader r = c.ExecuteReader()) {
+                while(r.Read()) {
+                  ItemEntry item = new ItemEntry();
+                  item.Guid = r.GetGuid(0);
+                  item.RawData = (byte[])r.GetValue(1);
+                  items.Add(item);
+                }
+              }
+            }
+          }
+        }
+      } finally {
+        rwLock.ExitReadLock();
+      }
+      return items;
+    }
+
+    public IList<ItemEntry> GetItems(Guid predicate, Guid property) {
+      List<ItemEntry> items = new List<ItemEntry>();
+      rwLock.EnterReadLock();
+      try {
+        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(DbCommand c = cnn.CreateCommand()) {
+            c.CommandText = "Select guid, rawdata from Item,(select subject from Statement where predicate=@Predicate and property=@Property) where subject=guid";
+            DbParameter predParam = c.CreateParameter();
+            predParam.ParameterName = "@Predicate";
+            predParam.Value = predicate;
+            c.Parameters.Add(predParam);
+            DbParameter propertyParam = c.CreateParameter();
+            propertyParam.ParameterName = "@Property";
+            propertyParam.Value = property;
+            c.Parameters.Add(propertyParam);
+            using(DbDataReader r = c.ExecuteReader()) {
+              r.Read();
+              ItemEntry item = new ItemEntry();
+              item.Guid = r.GetGuid(0);
+              item.RawData = (byte[])r.GetValue(1);
+              items.Add(item);
+            }
+          }
+        }
+      } finally {
+        rwLock.ExitReadLock();
+      }
+      return items;
+    }
+
+    public void LinkItems(ItemEntry subject, ItemEntry predicate, ItemEntry property) {
+      rwLock.EnterWriteLock();
+      try {
+        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(SQLiteTransaction t = cnn.BeginTransaction()) {
+            ItemEntry[] items = new ItemEntry[] { subject, predicate, property };
+            foreach(ItemEntry item in items) {
+              long n = 0;
+              using(SQLiteCommand c = cnn.CreateCommand()) {
+                c.Transaction = t;
+                c.CommandText = "select count(guid) from item where guid=@guid";
+                DbParameter guidParam = c.CreateParameter();
+                guidParam.ParameterName = "@guid";
+                guidParam.Value = item.Guid.ToString();
+                c.Parameters.Add(guidParam);
+                n = (long)c.ExecuteScalar();
+              }
+              if(n == 0) using(SQLiteCommand c = cnn.CreateCommand()) {
+                  c.Transaction = t;
+                  c.CommandText = "insert into item (Guid, RawData) values (@Guid, @RawData)";
+                  DbParameter guidParam = c.CreateParameter();
+                  guidParam.ParameterName = "@Guid";
+                  guidParam.Value = item.Guid.ToString();
+                  c.Parameters.Add(guidParam);
+                  DbParameter rawDataParam = c.CreateParameter();
+                  rawDataParam.ParameterName = "@RawData";
+                  rawDataParam.Value = item.RawData;
+                  c.Parameters.Add(rawDataParam);
+                  c.ExecuteNonQuery();
+                }
+            }
+            using(SQLiteCommand c = cnn.CreateCommand()) {
+              c.Transaction = t;
+              c.CommandText = "insert into Statement (Subject, Predicate, Property) values (@Subject, @Predicate, @Property)";
+              DbParameter subjectParam = c.CreateParameter();
+              subjectParam.ParameterName = "@Subject";
+              subjectParam.Value = subject.Guid.ToString();
+              c.Parameters.Add(subjectParam);
+              DbParameter predParam = c.CreateParameter();
+              predParam.ParameterName = "@Predicate";
+              predParam.Value = predicate.Guid.ToString();
+              c.Parameters.Add(predParam);
+              DbParameter propertyParam = c.CreateParameter();
+              propertyParam.ParameterName = "@Property";
+              propertyParam.Value = property.Guid.ToString();
+              c.Parameters.Add(propertyParam);
+              c.ExecuteNonQuery();
+            }
+            t.Commit();
+          }
+        }
+      } finally {
+        rwLock.ExitWriteLock();
+      }
+    }
   }
 }