Free cookie consent management tool by TermsFeed Policy Generator

source: trunk/sources/HeuristicLab.CEDMA.DB/Database.cs @ 435

Last change on this file since 435 was 419, checked in by gkronber, 16 years ago

worked on #211 need to patch the operator-graph of an agent before execution (replacing the OperatorLinks with their targets).

work in progress...

File size: 22.5 KB
Line 
1#region License Information
2/* HeuristicLab
3 * Copyright (C) 2002-2008 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
4 *
5 * This file is part of HeuristicLab.
6 *
7 * HeuristicLab is free software: you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation, either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * HeuristicLab is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
19 */
20#endregion
21
22using System;
23using System.Collections.Generic;
24using System.Linq;
25using System.Text;
26using System.Data.Linq;
27using HeuristicLab.CEDMA.DB.Interfaces;
28using System.ServiceModel;
29using System.Data;
30using System.Data.SQLite;
31using System.Data.Common;
32using System.Threading;
33
34namespace HeuristicLab.CEDMA.DB {
35  [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, ConcurrencyMode = ConcurrencyMode.Multiple, UseSynchronizationContext = false)]
36  public class Database : IDatabase {
37    private string connectionString;
38    private ReaderWriterLockSlim rwLock;
39    public Database(string connectionString) {
40      this.connectionString = connectionString;
41      rwLock = new ReaderWriterLockSlim();
42    }
43
44    #region create empty database
45    public void CreateNew() {
46      rwLock.EnterWriteLock();
47      try {
48        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
49          cnn.Open();
50          using(DbTransaction t = cnn.BeginTransaction()) {
51            using(DbCommand cmd = cnn.CreateCommand()) {
52              cmd.CommandText = "CREATE TABLE Project (ID integer primary key autoincrement, Name text, Description text, CreationTime DateTime)";
53              cmd.Transaction = t;
54              cmd.ExecuteNonQuery();
55            }
56            using(DbCommand cmd = cnn.CreateCommand()) {
57              cmd.CommandText = "CREATE TABLE Agent (ID integer primary key autoincrement, ProjectId integer, ParentAgentId integer, Name text, Status text default " + ProcessStatus.Unknown + ", CreationTime DateTime, RawData Blob)";
58              cmd.Transaction = t;
59              cmd.ExecuteNonQuery();
60            }
61            using(DbCommand cmd = cnn.CreateCommand()) {
62              cmd.CommandText = "CREATE TABLE Result (ID integer primary key autoincrement, AgentId integer, ParentResultId integer, Summary text, Description text, CreationTime DateTime, RawData Blob)";
63              cmd.Transaction = t;
64              cmd.ExecuteNonQuery();
65            }
66            using(DbCommand cmd = cnn.CreateCommand()) {
67              cmd.CommandText = "CREATE TABLE Operator (ID integer primary key autoincrement, Name text, RawData Blob)";
68              cmd.Transaction = t;
69              cmd.ExecuteNonQuery();
70            }
71            t.Commit();
72          }
73        }
74      } finally {
75        rwLock.ExitWriteLock();
76      }
77    }
78    #endregion
79
80    #region insert agent/result/sub-result
81    public long InsertAgent(long? parentAgentId, string name, byte[] rawData) {
82      rwLock.EnterWriteLock();
83      try {
84        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
85          cnn.Open();
86          long id;
87          using(DbTransaction t = cnn.BeginTransaction()) {
88            using(DbCommand c = cnn.CreateCommand()) {
89              c.Transaction = t;
90              c.CommandText = "Insert into Agent (Name, ParentAgentId, CreationTime, RawData) values (@Name, @ParentAgentId, @CreationTime, @RawData); select last_insert_rowid()";
91              DbParameter nameParam = c.CreateParameter();
92              nameParam.ParameterName = "@Name";
93              nameParam.Value = name;
94              c.Parameters.Add(nameParam);
95              DbParameter parentParam = c.CreateParameter();
96              parentParam.ParameterName = "@ParentAgentId";
97              parentParam.Value = parentAgentId;
98              c.Parameters.Add(parentParam);
99              DbParameter creationTimeParam = c.CreateParameter();
100              creationTimeParam.ParameterName = "@CreationTime";
101              creationTimeParam.Value = DateTime.Now;
102              c.Parameters.Add(creationTimeParam);
103              DbParameter dataParam = c.CreateParameter();
104              dataParam.ParameterName = "@RawData";
105              dataParam.Value = rawData;
106              c.Parameters.Add(dataParam);
107              id = (long)c.ExecuteScalar();
108            }
109            t.Commit();
110            return id;
111          }
112        }
113      } finally {
114        rwLock.ExitWriteLock();
115      }
116    }
117
118    public long InsertResult(long agentId, string summary, string description, byte[] rawData) {
119      rwLock.EnterWriteLock();
120      try {
121        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
122          cnn.Open();
123          long id;
124          using(DbTransaction t = cnn.BeginTransaction()) {
125            using(DbCommand c = cnn.CreateCommand()) {
126              c.Transaction = t;
127              c.CommandText = "Insert into Result (AgentId, CreationTime, Summary, Description, RawData) values (@AgentId, @CreationTime, @Summary, @Description, @RawData); select last_insert_rowid()";
128              DbParameter agentIdParam = c.CreateParameter();
129              agentIdParam.ParameterName = "@AgentId";
130              agentIdParam.Value = agentId;
131              c.Parameters.Add(agentIdParam);
132              DbParameter creationParam = c.CreateParameter();
133              creationParam.ParameterName = "@CreationTime";
134              DateTime now = DateTime.Now;
135              creationParam.Value = now;
136              c.Parameters.Add(creationParam);
137              DbParameter summaryParam = c.CreateParameter();
138              summaryParam.ParameterName = "@Summary";
139              summaryParam.Value = summary;
140              c.Parameters.Add(summaryParam);
141              DbParameter descParam = c.CreateParameter();
142              descParam.ParameterName = "@Description";
143              descParam.Value = description;
144              c.Parameters.Add(descParam);
145              DbParameter rawDataParam = c.CreateParameter();
146              rawDataParam.ParameterName = "@RawData";
147              rawDataParam.Value = rawData;
148              c.Parameters.Add(rawDataParam);
149              id = (long)c.ExecuteScalar();
150            }
151            t.Commit();
152            return id;
153          }
154        }
155      } finally {
156        rwLock.ExitWriteLock();
157      }
158    }
159
160    public long InsertSubResult(long resultId, string summary, string description, byte[] rawData) {
161      rwLock.EnterWriteLock();
162      try {
163        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
164          cnn.Open();
165          long id;
166          using(DbTransaction t = cnn.BeginTransaction()) {
167            using(DbCommand c = cnn.CreateCommand()) {
168              c.Transaction = t;
169              c.CommandText = "Insert into Result (ParentResultId, CreationTime, Summary, Description, RawData) values (@ParentResultId, @CreationTime, @Summary, @Description, @RawData); select last_insert_rowid()";
170              DbParameter resultIdParam = c.CreateParameter();
171              resultIdParam.ParameterName = "@ParentResultId";
172              resultIdParam.Value = resultId;
173              c.Parameters.Add(resultIdParam);
174              DbParameter creationParam = c.CreateParameter();
175              creationParam.ParameterName = "@CreationTime";
176              DateTime now = DateTime.Now;
177              creationParam.Value = now;
178              c.Parameters.Add(creationParam);
179              DbParameter summaryParam = c.CreateParameter();
180              summaryParam.ParameterName = "@Summary";
181              summaryParam.Value = summary;
182              c.Parameters.Add(summaryParam);
183              DbParameter descParam = c.CreateParameter();
184              descParam.ParameterName = "@Description";
185              descParam.Value = description;
186              c.Parameters.Add(descParam);
187              DbParameter rawDataParam = c.CreateParameter();
188              rawDataParam.ParameterName = "@RawData";
189              rawDataParam.Value = rawData;
190              c.Parameters.Add(rawDataParam);
191              id = (long)c.ExecuteScalar();
192            }
193            t.Commit();
194            return id;
195          }
196        }
197      } finally {
198        rwLock.ExitWriteLock();
199      }
200    }
201
202    public long InsertOperator(string name, byte[] rawData) {
203      rwLock.EnterWriteLock();
204      try {
205        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
206          cnn.Open();
207          long id;
208          using(DbTransaction t = cnn.BeginTransaction()) {
209            using(DbCommand c = cnn.CreateCommand()) {
210              c.Transaction = t;
211              c.CommandText = "Insert into Operator (Name, RawData) values (@Name, @RawData); select last_insert_rowid()";
212              DbParameter nameParam = c.CreateParameter();
213              nameParam.ParameterName = "@Name";
214              nameParam.Value = name;
215              c.Parameters.Add(nameParam);
216              DbParameter dataParam = c.CreateParameter();
217              dataParam.ParameterName = "@RawData";
218              dataParam.Value = rawData;
219              c.Parameters.Add(dataParam);
220              id = (long)c.ExecuteScalar();
221            }
222            t.Commit();
223            return id;
224          }
225        }
226      } finally {
227        rwLock.ExitWriteLock();
228      }
229    }
230    #endregion
231
232    #region update agent, run, operator
233    public void UpdateAgent(long id, string name) {
234      rwLock.EnterWriteLock();
235      try {
236        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
237          cnn.Open();
238          using(SQLiteTransaction t = cnn.BeginTransaction()) {
239            using(SQLiteCommand c = cnn.CreateCommand()) {
240              c.Transaction = t;
241              c.CommandText = "Update Agent set Name=@Name where id=@Id";
242              DbParameter nameParam = c.CreateParameter();
243              DbParameter idParam = c.CreateParameter();
244              nameParam.ParameterName = "@Name";
245              nameParam.Value = name;
246              idParam.ParameterName = "@Id";
247              idParam.Value = id;
248              c.Parameters.Add(nameParam);
249              c.Parameters.Add(idParam);
250              c.ExecuteNonQuery();
251            }
252            t.Commit();
253          }
254        }
255      } finally {
256        rwLock.ExitWriteLock();
257      }
258    }
259
260    public void UpdateAgent(long id, ProcessStatus status) {
261      rwLock.EnterWriteLock();
262      try {
263        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
264          cnn.Open();
265          using(SQLiteTransaction t = cnn.BeginTransaction()) {
266            using(SQLiteCommand c = cnn.CreateCommand()) {
267              c.Transaction = t;
268              c.CommandText = "Update Agent set Status=@Status where id=@Id";
269              DbParameter statusParam = c.CreateParameter();
270              DbParameter idParam = c.CreateParameter();
271              statusParam.ParameterName = "@Status";
272              statusParam.Value = status.ToString();
273              idParam.ParameterName = "@Id";
274              idParam.Value = id;
275              c.Parameters.Add(statusParam);
276              c.Parameters.Add(idParam);
277              c.ExecuteNonQuery();
278            }
279            t.Commit();
280          }
281        }
282      } finally {
283        rwLock.ExitWriteLock();
284      }
285    }
286
287    public void UpdateAgent(long id, byte[] rawData) {
288      rwLock.EnterWriteLock();
289      try {
290        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
291          cnn.Open();
292          using(SQLiteTransaction t = cnn.BeginTransaction()) {
293            using(SQLiteCommand c = cnn.CreateCommand()) {
294              c.Transaction = t;
295              c.CommandText = "Update Agent set RawData=@RawData where id=@Id";
296              DbParameter rawDataParam = c.CreateParameter();
297              DbParameter idParam = c.CreateParameter();
298              rawDataParam.ParameterName = "@RawData";
299              rawDataParam.Value = rawData;
300              idParam.ParameterName = "@Id";
301              idParam.Value = id;
302              c.Parameters.Add(rawDataParam);
303              c.Parameters.Add(idParam);
304              c.ExecuteNonQuery();
305            }
306            t.Commit();
307          }
308        }
309      } finally {
310        rwLock.ExitWriteLock();
311      }
312    }
313
314    public void UpdateOperator(long id, string name, byte[] rawData) {
315      rwLock.EnterWriteLock();
316      try {
317        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
318          cnn.Open();
319          using(SQLiteTransaction t = cnn.BeginTransaction()) {
320            using(SQLiteCommand c = cnn.CreateCommand()) {
321              c.Transaction = t;
322              c.CommandText = "Update Operator set Name=@Name, RawData=@RawData where id=@Id";
323              DbParameter rawDataParam = c.CreateParameter();
324              DbParameter nameParam = c.CreateParameter();
325              DbParameter idParam = c.CreateParameter();
326              rawDataParam.ParameterName = "@RawData";
327              rawDataParam.Value = rawData;
328              idParam.ParameterName = "@Id";
329              idParam.Value = id;
330              nameParam.ParameterName = "@Name";
331              nameParam.Value = name;
332              c.Parameters.Add(rawDataParam);
333              c.Parameters.Add(nameParam);
334              c.Parameters.Add(idParam);
335              c.ExecuteNonQuery();
336            }
337            t.Commit();
338          }
339        }
340      } finally {
341        rwLock.ExitWriteLock();
342      }
343    }
344
345    #endregion
346
347    #region get agent/result/sub-result/operator
348
349    public ICollection<AgentEntry> GetAgents(ProcessStatus status) {
350      rwLock.EnterReadLock();
351      List<AgentEntry> agents = new List<AgentEntry>();
352      try {
353        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
354          cnn.Open();
355          SQLiteCommand c = cnn.CreateCommand();
356          c.CommandText = "Select id, name, rawdata from Agent where Status=@Status";
357          DbParameter statusParameter = c.CreateParameter();
358          statusParameter.ParameterName = "@Status";
359          statusParameter.Value = status.ToString();
360          c.Parameters.Add(statusParameter);
361          SQLiteDataReader r = c.ExecuteReader();
362          while(r.Read()) {
363            AgentEntry agent = new AgentEntry();
364            agent.ParentAgentId = null;
365            agent.Status = status;
366            agent.Id = r.GetInt32(0);
367            agent.Name = r.IsDBNull(1) ? "" : r.GetString(1);
368            agent.RawData = (byte[])r.GetValue(2);
369            agents.Add(agent);
370          }
371        }
372      } finally {
373        rwLock.ExitReadLock();
374      }
375      return agents;
376    }
377
378    public ICollection<AgentEntry> GetAgents() {
379      rwLock.EnterReadLock();
380      List<AgentEntry> agents = new List<AgentEntry>();
381      try {
382        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
383          cnn.Open();
384          using(DbCommand c = cnn.CreateCommand()) {
385            c.CommandText = "Select id, name, status, rawdata from Agent where ParentAgentId isnull";
386            using(DbDataReader r = c.ExecuteReader()) {
387              while(r.Read()) {
388                AgentEntry agent = new AgentEntry();
389                agent.ParentAgentId = null;
390                agent.Id = r.GetInt32(0);
391                agent.Name = r.IsDBNull(1) ? "-" : r.GetString(1);
392                agent.Status = (ProcessStatus)Enum.Parse(typeof(ProcessStatus), r.GetString(2));
393                agent.RawData = (byte[])r.GetValue(3);
394                agents.Add(agent);
395              }
396            }
397          }
398        }
399      } finally {
400        rwLock.ExitReadLock();
401      }
402      return agents;
403    }
404
405    public ICollection<AgentEntry> GetSubAgents(long parentAgentId) {
406      rwLock.EnterReadLock();
407      List<AgentEntry> agents = new List<AgentEntry>();
408      try {
409        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
410          cnn.Open();
411          using(DbCommand c = cnn.CreateCommand()) {
412            c.CommandText = "Select id, name, status, rawdata from Agent where ParentAgentId=@ParentAgentId";
413            DbParameter parentParameter = c.CreateParameter();
414            parentParameter.ParameterName = "@ParentAgentId";
415            parentParameter.Value = parentAgentId;
416            c.Parameters.Add(parentParameter);
417
418            using(DbDataReader r = c.ExecuteReader()) {
419              while(r.Read()) {
420                AgentEntry agent = new AgentEntry();
421                agent.ParentAgentId = parentAgentId;
422                agent.Id = r.GetInt32(0);
423                agent.Name = r.IsDBNull(1) ? "-" : r.GetString(1);
424                agent.Status = (ProcessStatus)Enum.Parse(typeof(ProcessStatus), r.GetString(2));
425                agent.RawData = (byte[])r.GetValue(3);
426                agents.Add(agent);
427              }
428            }
429          }
430        }
431      } finally {
432        rwLock.ExitReadLock();
433      }
434      return agents;
435    }
436
437    public ICollection<ResultEntry> GetResults(long agentId) {
438      List<ResultEntry> results = new List<ResultEntry>();
439      rwLock.EnterReadLock();
440      try {
441        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
442          cnn.Open();
443          using(DbCommand c = cnn.CreateCommand()) {
444            c.CommandText = "Select Id, CreationTime, Summary, Description, Rawdata from Result where AgentId=@AgentId";
445            DbParameter agentParam = c.CreateParameter();
446            agentParam.ParameterName = "@AgentId";
447            agentParam.Value = agentId;
448            c.Parameters.Add(agentParam);
449            using(DbDataReader r = c.ExecuteReader()) {
450              while(r.Read()) {
451                ResultEntry result = new ResultEntry();
452                result.AgentId = agentId;
453                result.Id = r.GetInt32(0);
454                result.CreationTime = r.GetDateTime(1);
455                result.Summary = r.GetString(2);
456                result.Description = r.GetString(3);
457                result.RawData = (byte[])r.GetValue(4);
458                results.Add(result);
459              }
460            }
461          }
462        }
463      } finally {
464        rwLock.ExitReadLock();
465      }
466      return results;
467    }
468
469    public ICollection<ResultEntry> GetSubResults(long resultId) {
470      List<ResultEntry> results = new List<ResultEntry>();
471      rwLock.EnterReadLock();
472      try {
473        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
474          cnn.Open();
475          using(DbCommand c = cnn.CreateCommand()) {
476            c.CommandText = "Select Id, CreationTime, Summary, Description, Rawdata from Result where ParentResultId=@ParentResultId";
477            DbParameter parentParam = c.CreateParameter();
478            parentParam.ParameterName = "@ParentResultId";
479            parentParam.Value = resultId;
480            c.Parameters.Add(parentParam);
481            using(DbDataReader r = c.ExecuteReader()) {
482              while(r.Read()) {
483                ResultEntry result = new ResultEntry();
484                result.ParentResultId = resultId;
485                result.Id = r.GetInt32(0);
486                result.CreationTime = r.GetDateTime(1);
487                result.Summary = r.GetString(2);
488                result.Description = r.GetString(3);
489                result.RawData = (byte[])r.GetValue(4);
490                results.Add(result);
491              }
492            }
493          }
494        }
495      } finally {
496        rwLock.ExitReadLock();
497      }
498      return results;
499    }
500
501    public ICollection<OperatorEntry> GetOperators() {
502      rwLock.EnterReadLock();
503      List<OperatorEntry> operators = new List<OperatorEntry>();
504      try {
505        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
506          cnn.Open();
507          using(DbCommand c = cnn.CreateCommand()) {
508            c.CommandText = "Select id, name, rawdata from Operator";
509            using(DbDataReader r = c.ExecuteReader()) {
510              while(r.Read()) {
511                OperatorEntry op = new OperatorEntry();
512                op.Id = r.GetInt32(0);
513                op.Name = r.IsDBNull(1) ? "-" : r.GetString(1);
514                op.RawData = (byte[])r.GetValue(2);
515                operators.Add(op);
516              }
517            }
518          }
519        }
520      } finally {
521        rwLock.ExitReadLock();
522      }
523      return operators;
524    }
525    public OperatorEntry GetOperator(long id) {
526      rwLock.EnterReadLock();
527      try {
528        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
529          cnn.Open();
530          using(DbCommand c = cnn.CreateCommand()) {
531            c.CommandText = "Select id, name, rawdata from Operator where id=@Id";
532            DbParameter idParam = c.CreateParameter();
533            idParam.ParameterName = "@Id";
534            idParam.Value = id;
535            c.Parameters.Add(idParam);
536            using(DbDataReader r = c.ExecuteReader()) {
537              r.Read();
538              OperatorEntry op = new OperatorEntry();
539              op.Id = r.GetInt32(0);
540              op.Name = r.IsDBNull(1) ? "-" : r.GetString(1);
541              op.RawData = (byte[])r.GetValue(2);
542              return op;
543            }
544          }
545        }
546      } finally {
547        rwLock.ExitReadLock();
548      }
549    }
550    #endregion
551
552    #region delete operator
553    public void DeleteOperator(long id) {
554      rwLock.EnterWriteLock();
555      try {
556        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
557          cnn.Open();
558          using(DbTransaction t = cnn.BeginTransaction()) {
559            using(DbCommand c = cnn.CreateCommand()) {
560              c.Transaction = t;
561              c.CommandText = "Delete from Operator where id=@Id";
562              DbParameter idParam = c.CreateParameter();
563              idParam.ParameterName = "@Id";
564              idParam.Value = id;
565              c.Parameters.Add(idParam);
566              c.ExecuteNonQuery();
567            }
568            t.Commit();
569          }
570        }
571      } finally {
572        rwLock.ExitWriteLock();
573      }
574    }
575    #endregion
576  }
577}
Note: See TracBrowser for help on using the repository browser.