Changeset 375 for trunk/sources/HeuristicLab.CEDMA.DB

Timestamp:

07/09/08 21:37:36 (16 years ago)

Author:

gkronber

Message:

worked on #188

File:

• trunk/sources/HeuristicLab.CEDMA.DB/Database.cs (modified) (1 diff)

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

@@ -32 +32 @@
 
 namespace HeuristicLab.CEDMA.DB {
-  [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, ConcurrencyMode = ConcurrencyMode.Single, UseSynchronizationContext = true)]
+  [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, ConcurrencyMode = ConcurrencyMode.Multiple, UseSynchronizationContext = false)]
   public class Database : IDatabase {
     private string connectionString;
+    private object bigLock = new object();
     public Database(string connectionString) {
       this.connectionString = connectionString;
     }
 
+    #region create empty database
     public void CreateNew() {
-      using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
-        cnn.ConnectionString = connectionString;
-        cnn.Open();
-
-        SQLiteCommand cAgent = cnn.CreateCommand();
-        cAgent.CommandText = "CREATE TABLE Agent (ID integer primary key autoincrement, ProjectId integer, Name text, Status text default 'Unknown', RawData Blob)";
-        SQLiteCommand cProject = cnn.CreateCommand();
-        cProject.CommandText = "CREATE TABLE Project (ID integer primary key autoincrement, Name text, Description text, CreationDate DateTime)";
-        SQLiteCommand cResult = cnn.CreateCommand();
-        cResult.CommandText = "CREATE TABLE Result (ID integer primary key autoincrement, AgentId integer, ParentResultId integer, CreationDate DateTime, RawData Blob)";
-        cAgent.ExecuteNonQuery();
-        cProject.ExecuteNonQuery();
-        cResult.ExecuteNonQuery();
-      }
-    }
-
-    public long CreateAgent() {
-      using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
-        cnn.Open();
-        SQLiteCommand c = cnn.CreateCommand();
-        c.CommandText = "Insert into Agent (Name) values (@Name); select last_insert_rowid()";
-        DbParameter nameParam = c.CreateParameter();
-        nameParam.ParameterName = "@Name";
-        nameParam.Value = DateTime.Now.ToString();
-        c.Parameters.Add(nameParam);
-        long id = (long)c.ExecuteScalar();
-        return id;
-      }
-    }
-
-    public ICollection<AgentEntry> GetAgentEntries() {
-      List<AgentEntry> agents = new List<AgentEntry>();
-      using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
-        cnn.Open();
-        SQLiteCommand c = cnn.CreateCommand();
-        c.CommandText = "Select id, name, status, rawdata from Agent";
-        SQLiteDataReader r = c.ExecuteReader();
-        while(r.Read()) {
-          AgentEntry agent = new AgentEntry(r.GetInt32(0), r.GetString(1), (AgentStatus)Enum.Parse(typeof(AgentStatus), r.GetString(2)), (byte[])r.GetValue(3));
-          agents.Add(agent);
-        }
-      }
-      return agents;
-    }
-
-    public ICollection<AgentEntry> GetAgentEntries(AgentStatus status) {
-      List<AgentEntry> agents = new List<AgentEntry>();
-      using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
-        cnn.Open();
-        SQLiteCommand c = cnn.CreateCommand();
-        c.CommandText = "Select id, name, status, rawdata from Agent where Status=@Status";
-        DbParameter statusParameter = c.CreateParameter();
-        statusParameter.ParameterName = "@Status";
-        statusParameter.Value = (int)status;
-        c.Parameters.Add(statusParameter);
-
-        SQLiteDataReader r = c.ExecuteReader();
-        while(r.Read()) {
-          AgentEntry agent = new AgentEntry(r.GetInt32(0), r.GetString(1), (AgentStatus)Enum.Parse(typeof(AgentStatus), r.GetString(2)), (byte[])r.GetValue(3));
-          agents.Add(agent);
-        }
-      }
-      return agents;
-    }
-
-    public void Update(AgentEntry entry) {
-      using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
-        cnn.Open();
-        SQLiteCommand c = cnn.CreateCommand();
-        c.CommandText = "Update Agent set Name=@Name, Status=@Status, RawData=@RawData where id=@Id";
-        DbParameter nameParam = c.CreateParameter();
-        DbParameter statusParam = c.CreateParameter();
-        DbParameter rawDataParam = c.CreateParameter();
-        DbParameter idParam = c.CreateParameter();
-        nameParam.ParameterName = "@Name";
-        nameParam.Value = entry.Name;
-        statusParam.ParameterName = "@Status";
-        statusParam.Value = entry.Status;
-        rawDataParam.ParameterName = "@RawData";
-        rawDataParam.Value = entry.RawData;
-        idParam.ParameterName = "@Id";
-        idParam.Value = entry.Id;
-        c.Parameters.Add(nameParam);
-        c.Parameters.Add(statusParam);
-        c.Parameters.Add(rawDataParam);
-        c.Parameters.Add(idParam);
-        c.ExecuteNonQuery();
-      }
-    }
-
-    public void Update(ResultEntry result) {
+      lock(bigLock) {
+        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(DbTransaction t = cnn.BeginTransaction()) {
+            using(DbCommand cmd = cnn.CreateCommand()) {
+              cmd.CommandText = "CREATE TABLE Project (ID integer primary key autoincrement, Name text, Description text, CreationTime DateTime)";
+              cmd.Transaction = t;
+              cmd.ExecuteNonQuery();
+            }
+            using(DbCommand cmd = cnn.CreateCommand()) {
+              cmd.CommandText = "CREATE TABLE Agent (ID integer primary key autoincrement, ProjectId integer, Name text, Status text default 'Unknown', RawData Blob)";
+              cmd.Transaction = t;
+              cmd.ExecuteNonQuery();
+            }
+            using(DbCommand cmd = cnn.CreateCommand()) {
+              cmd.CommandText = "CREATE TABLE Run (ID integer primary key autoincrement, AgentId integer, CreationTime DateTime, StartTime DateTime, FinishedTime DateTime, Status text default 'Unknown', RawData Blob)";
+              cmd.Transaction = t;
+              cmd.ExecuteNonQuery();
+            }
+            using(DbCommand cmd = cnn.CreateCommand()) {
+              cmd.CommandText = "CREATE TABLE Result (ID integer primary key autoincrement, RunId integer, ResultId integer, CreationTime DateTime, RawData Blob)";
+              cmd.Transaction = t;
+              cmd.ExecuteNonQuery();
+            }
+            t.Commit();
+          }
+        }
+      }
+    }
+    #endregion
+
+    #region insert agent/run/result/sub-result
+    public long InsertAgent(string name, byte[] rawData) {
+      lock(bigLock) {
+        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          long id;
+          using(DbTransaction t = cnn.BeginTransaction()) {
+            using(DbCommand c = cnn.CreateCommand()) {
+              c.Transaction = t;
+              c.CommandText = "Insert into Agent (Name, RawData) values (@Name, @RawData); select last_insert_rowid()";
+              DbParameter nameParam = c.CreateParameter();
+              nameParam.ParameterName = "@Name";
+              nameParam.Value = name;
+              c.Parameters.Add(nameParam);
+              DbParameter dataParam = c.CreateParameter();
+              dataParam.ParameterName = "@RawData";
+              dataParam.Value = rawData;
+              c.Parameters.Add(dataParam);
+              id = (long)c.ExecuteScalar();
+            }
+            t.Commit();
+            return id;
+          }
+        }
+      }
+    }
+
+    public long InsertRun(long agentId, byte[] rawData) {
+      lock(bigLock) {
+        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          long id;
+          using(DbTransaction t = cnn.BeginTransaction()) {
+            using(DbCommand c = cnn.CreateCommand()) {
+              c.Transaction = t;
+              c.CommandText = "Insert into Run (AgentId, CreationTime, RawData) values (@AgentId, @CreationTime, @RawData); select last_insert_rowid()";
+              DbParameter agentIdParam = c.CreateParameter();
+              agentIdParam.ParameterName = "@AgentId";
+              agentIdParam.Value = agentId;
+              c.Parameters.Add(agentIdParam);
+              DbParameter creationParam = c.CreateParameter();
+              creationParam.ParameterName = "@CreationTime";
+              DateTime now = DateTime.Now;
+              creationParam.Value = now;
+              c.Parameters.Add(creationParam);
+              DbParameter rawDataParam = c.CreateParameter();
+              rawDataParam.ParameterName = "@RawData";
+              rawDataParam.Value = rawData;
+              c.Parameters.Add(rawDataParam);
+              id = (long)c.ExecuteScalar();
+            }
+            t.Commit();
+            return id;
+          }
+        }
+      }
+    }
+
+    public long InsertResult(long runId, byte[] rawData) {
+      lock(bigLock) {
+        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          long id;
+          using(DbTransaction t = cnn.BeginTransaction()) {
+            using(DbCommand c = cnn.CreateCommand()) {
+              c.Transaction = t;
+              c.CommandText = "Insert into Result (RunId, CreationTime, RawData) values (@RunId, @CreationTime, @RawData); select last_insert_rowid()";
+              DbParameter runIdParam = c.CreateParameter();
+              runIdParam.ParameterName = "@RunId";
+              runIdParam.Value = runId;
+              c.Parameters.Add(runIdParam);
+              DbParameter creationParam = c.CreateParameter();
+              creationParam.ParameterName = "@CreationTime";
+              DateTime now = DateTime.Now;
+              creationParam.Value = now;
+              c.Parameters.Add(creationParam);
+              DbParameter rawDataParam = c.CreateParameter();
+              rawDataParam.ParameterName = "@RawData";
+              rawDataParam.Value = rawData;
+              c.Parameters.Add(rawDataParam);
+              id = (long)c.ExecuteScalar();
+            }
+            t.Commit();
+            return id;
+          }
+        }
+      }
+    }
+
+    public long InsertSubResult(long resultId, byte[] rawData) {
+      lock(bigLock) {
+
+        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          long id;
+          using(DbTransaction t = cnn.BeginTransaction()) {
+            using(DbCommand c = cnn.CreateCommand()) {
+              c.Transaction = t;
+              c.CommandText = "Insert into Result (ResultId, CreationTime, RawData) values (@ResultId, @CreationTime, @RawData); select last_insert_rowid()";
+              DbParameter resultIdParam = c.CreateParameter();
+              resultIdParam.ParameterName = "@ResultId";
+              resultIdParam.Value = resultId;
+              c.Parameters.Add(resultIdParam);
+              DbParameter creationParam = c.CreateParameter();
+              creationParam.ParameterName = "@CreationTime";
+              DateTime now = DateTime.Now;
+              creationParam.Value = now;
+              c.Parameters.Add(creationParam);
+              DbParameter rawDataParam = c.CreateParameter();
+              rawDataParam.ParameterName = "@RawData";
+              rawDataParam.Value = rawData;
+              c.Parameters.Add(rawDataParam);
+              id = (long)c.ExecuteScalar();
+            }
+            t.Commit();
+            return id;
+          }
+        }
+      }
+    }
+    #endregion
+
+    #region update agent/run
+    public void UpdateAgent(long id, string name) {
+      lock(bigLock) {
+        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(SQLiteTransaction t = cnn.BeginTransaction()) {
+            using(SQLiteCommand c = cnn.CreateCommand()) {
+              c.Transaction = t;
+              c.CommandText = "Update Agent set Name=@Name where id=@Id";
+              DbParameter nameParam = c.CreateParameter();
+              DbParameter idParam = c.CreateParameter();
+              nameParam.ParameterName = "@Name";
+              nameParam.Value = name;
+              idParam.ParameterName = "@Id";
+              idParam.Value = id;
+              c.Parameters.Add(nameParam);
+              c.Parameters.Add(idParam);
+              c.ExecuteNonQuery();
+            }
+            t.Commit();
+          }
+        }
+      }
+    }
+
+    public void UpdateAgent(long id, ProcessStatus status) {
+      lock(bigLock) {
+        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(SQLiteTransaction t = cnn.BeginTransaction()) {
+            using(SQLiteCommand c = cnn.CreateCommand()) {
+              c.Transaction = t;
+              c.CommandText = "Update Agent set Status=@Status where id=@Id";
+              DbParameter statusParam = c.CreateParameter();
+              DbParameter idParam = c.CreateParameter();
+              statusParam.ParameterName = "@Status";
+              statusParam.Value = status;
+              idParam.ParameterName = "@Id";
+              idParam.Value = id;
+              c.Parameters.Add(statusParam);
+              c.Parameters.Add(idParam);
+              c.ExecuteNonQuery();
+            }
+            t.Commit();
+          }
+        }
+      }
+    }
+
+    public void UpdateAgent(long id, byte[] rawData) {
+      lock(bigLock) {
+        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(SQLiteTransaction t = cnn.BeginTransaction()) {
+            using(SQLiteCommand c = cnn.CreateCommand()) {
+              c.Transaction = t;
+              c.CommandText = "Update Agent set RawData=@RawData where id=@Id";
+              DbParameter rawDataParam = c.CreateParameter();
+              DbParameter idParam = c.CreateParameter();
+              rawDataParam.ParameterName = "@RawData";
+              rawDataParam.Value = rawData;
+              idParam.ParameterName = "@Id";
+              idParam.Value = id;
+              c.Parameters.Add(rawDataParam);
+              c.Parameters.Add(idParam);
+              c.ExecuteNonQuery();
+            }
+            t.Commit();
+          }
+        }
+      }
+    }
+
+    public void UpdateRunStart(long runId, DateTime startTime) {
+      lock(bigLock) {
+        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(SQLiteTransaction t = cnn.BeginTransaction()) {
+            using(SQLiteCommand c = cnn.CreateCommand()) {
+              c.Transaction = t;
+              c.CommandText = "Update Run set StartTime=@StartTime where id=@Id";
+              DbParameter startTimeParam = c.CreateParameter();
+              DbParameter idParam = c.CreateParameter();
+              startTimeParam.ParameterName = "@StartTime";
+              startTimeParam.Value = startTime;
+              idParam.ParameterName = "@Id";
+              idParam.Value = runId;
+              c.Parameters.Add(startTimeParam);
+              c.Parameters.Add(idParam);
+              c.ExecuteNonQuery();
+            }
+            t.Commit();
+          }
+        }
+      }
+    }
+
+    public void UpdateRunFinished(long runId, DateTime finishedTime) {
+      lock(bigLock) {
+        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(SQLiteTransaction t = cnn.BeginTransaction()) {
+            using(SQLiteCommand c = cnn.CreateCommand()) {
+              c.Transaction = t;
+              c.CommandText = "Update Run set FinishedTime=@FinishedTime where id=@Id";
+              DbParameter finishedTimeParam = c.CreateParameter();
+              DbParameter idParam = c.CreateParameter();
+              finishedTimeParam.ParameterName = "@FinishedTime";
+              finishedTimeParam.Value = finishedTime;
+              idParam.ParameterName = "@Id";
+              idParam.Value = runId;
+              c.Parameters.Add(finishedTimeParam);
+              c.Parameters.Add(idParam);
+              c.ExecuteNonQuery();
+            }
+            t.Commit();
+          }
+        }
+      }
+    }
+
+    public void UpdateRunStatus(long runId, ProcessStatus status) {
+      lock(bigLock) {
+        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(SQLiteTransaction t = cnn.BeginTransaction()) {
+            using(SQLiteCommand c = cnn.CreateCommand()) {
+              c.Transaction = t;
+              c.CommandText = "Update Run set Status=@Status where id=@Id";
+              DbParameter statusParam = c.CreateParameter();
+              DbParameter idParam = c.CreateParameter();
+              statusParam.ParameterName = "@Status";
+              statusParam.Value = status;
+              idParam.ParameterName = "@Id";
+              idParam.Value = runId;
+              c.Parameters.Add(statusParam);
+              c.Parameters.Add(idParam);
+              c.ExecuteNonQuery();
+            }
+            t.Commit();
+          }
+        }
+      }
+    }
+
+
+
+    #endregion
+
+
+    #region get agent/run/result/sub-result
+
+    public ICollection<AgentEntry> GetAgents(ProcessStatus status) {
+      lock(bigLock) {
+        List<AgentEntry> agents = new List<AgentEntry>();
+        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          SQLiteCommand c = cnn.CreateCommand();
+          c.CommandText = "Select id, name, status, rawdata from Agent where Status=@Status";
+          DbParameter statusParameter = c.CreateParameter();
+          statusParameter.ParameterName = "@Status";
+          statusParameter.Value = status;
+          c.Parameters.Add(statusParameter);
+
+          SQLiteDataReader r = c.ExecuteReader();
+          while(r.Read()) {
+            AgentEntry agent = new AgentEntry();
+            agent.Id = r.GetInt32(0);
+            agent.Name = r.GetString(1);
+            agent.Status = (ProcessStatus)Enum.Parse(typeof(ProcessStatus), r.GetString(2));
+            agent.RawData = (byte[])r.GetValue(3);
+            agents.Add(agent);
+          }
+        }
+        return agents;
+      }
+    }
+
+    public ICollection<AgentEntry> GetAgents() {
+      lock(bigLock) {
+        List<AgentEntry> agents = new List<AgentEntry>();
+        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(DbCommand c = cnn.CreateCommand()) {
+            c.CommandText = "Select id, name, status, rawdata from Agent";
+            using(DbDataReader r = c.ExecuteReader()) {
+              while(r.Read()) {
+                AgentEntry agent = new AgentEntry();
+                agent.Id = r.GetInt32(0);
+                agent.Name = r.GetString(1);
+                agent.Status = (ProcessStatus)Enum.Parse(typeof(ProcessStatus), r.GetString(2));
+                agent.RawData = (byte[])r.GetValue(3);
+                agents.Add(agent);
+              }
+            }
+          }
+        }
+        return agents;
+      }
+    }
+
+    //(ID integer primary key autoincrement, AgentId integer, CreationTime DateTime, StartTime DateTime, FinishedTime DateTime, Status text default 'Unknown', RawData Blob)";
+
+    public ICollection<RunEntry> GetRuns() {
+      lock(bigLock) {
+        List<RunEntry> runs = new List<RunEntry>();
+        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(DbCommand c = cnn.CreateCommand()) {
+            c.CommandText = "Select Id, AgentId, CreationTime, StartTime, FinishedTime, Status, Rawdata from Run";
+            using(DbDataReader r = c.ExecuteReader()) {
+              while(r.Read()) {
+                RunEntry run = new RunEntry();
+                run.Id = r.GetInt32(0);
+                run.AgentId = r.GetInt32(1);
+                run.CreationTime = r.GetDateTime(2);
+                run.StartTime = r.GetDateTime(3);
+                run.FinishedTime = r.GetDateTime(4);
+                run.Status = (ProcessStatus)Enum.Parse(typeof(ProcessStatus), r.GetString(5));
+                run.RawData = (byte[])r.GetValue(6);
+                runs.Add(run);
+              }
+            }
+          }
+        }
+        return runs;
+      }
+    }
+
+    public ICollection<RunEntry> GetRuns(ProcessStatus status) {
+      lock(bigLock) {
+        List<RunEntry> runs = new List<RunEntry>();
+        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(DbCommand c = cnn.CreateCommand()) {
+            c.CommandText = "Select Id, AgentId, CreationTime, StartTime, FinishedTime, Status, Rawdata from Run where Status=@Status";
+            DbParameter statusParameter = c.CreateParameter();
+            statusParameter.ParameterName = "@Status";
+            statusParameter.Value = status;
+            c.Parameters.Add(statusParameter);
+
+            using(DbDataReader r = c.ExecuteReader()) {
+              while(r.Read()) {
+                RunEntry run = new RunEntry();
+                run.Id = r.GetInt32(0);
+                run.AgentId = r.GetInt32(1);
+                run.CreationTime = r.GetDateTime(2);
+                run.StartTime = r.IsDBNull(3)? null : new Nullable<DateTime>(r.GetDateTime(3));
+                run.FinishedTime = r.IsDBNull(4) ? null:new Nullable<DateTime>(r.GetDateTime(4));
+                run.Status = (ProcessStatus)Enum.Parse(typeof(ProcessStatus), r.GetString(5));
+                run.RawData = (byte[])r.GetValue(6);
+                runs.Add(run);
+              }
+            }
+          }
+        }
+        return runs;
+      }
+    }
+
+    // (ID integer primary key autoincrement, RunId integer, ResultId integer, CreationTime DateTime, RawData Blob)";
+    public ICollection<ResultEntry> GetResults(long runId) {
+      lock(bigLock) {
+        List<ResultEntry> results = new List<ResultEntry>();
+        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
+          cnn.Open();
+          using(DbCommand c = cnn.CreateCommand()) {
+            c.CommandText = "Select Id, RunId, CreationTime, Rawdata from Result";
+            using(DbDataReader r = c.ExecuteReader()) {
+              while(r.Read()) {
+                ResultEntry result = new ResultEntry();
+                result.Id = r.GetInt32(0);
+                result.RunId = r.GetInt32(1);
+                result.CreationTime = r.GetDateTime(2);
+                result.RawData = (byte[])r.GetValue(3);
+                results.Add(result);
+              }
+            }
+          }
+        }
+        return results;
+      }
+    }
+
+    public ICollection<ResultEntry> GetSubResults(long resultId) {
       throw new NotImplementedException();
     }
+    #endregion
   }
 }