Free cookie consent management tool by TermsFeed Policy Generator

source: branches/3.1/sources/HeuristicLab.CEDMA.DB/Database.cs @ 6575

Last change on this file since 6575 was 543, checked in by gkronber, 16 years ago

removed prototypical implementation of rdf-like statement store and corresponding operators in a consolidation step (to prepare for a more solid implementation). Ticket #261 (Items are stored multiple times in the result entries in the CEDMA DB)

File size: 23.8 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 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            agents.Add(agent);
369          }
370        }
371      } finally {
372        rwLock.ExitReadLock();
373      }
374      return agents;
375    }
376
377    public ICollection<AgentEntry> GetAgents() {
378      rwLock.EnterReadLock();
379      List<AgentEntry> agents = new List<AgentEntry>();
380      try {
381        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
382          cnn.Open();
383          using(DbCommand c = cnn.CreateCommand()) {
384            c.CommandText = "Select id, name, status from Agent where ParentAgentId isnull";
385            using(DbDataReader r = c.ExecuteReader()) {
386              while(r.Read()) {
387                AgentEntry agent = new AgentEntry();
388                agent.ParentAgentId = null;
389                agent.Id = r.GetInt32(0);
390                agent.Name = r.IsDBNull(1) ? "-" : r.GetString(1);
391                agent.Status = (ProcessStatus)Enum.Parse(typeof(ProcessStatus), r.GetString(2));
392                agents.Add(agent);
393              }
394            }
395          }
396        }
397      } finally {
398        rwLock.ExitReadLock();
399      }
400      return agents;
401    }
402
403    public ICollection<AgentEntry> GetSubAgents(long parentAgentId) {
404      rwLock.EnterReadLock();
405      List<AgentEntry> agents = new List<AgentEntry>();
406      try {
407        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
408          cnn.Open();
409          using(DbCommand c = cnn.CreateCommand()) {
410            c.CommandText = "Select id, name, status from Agent where ParentAgentId=@ParentAgentId";
411            DbParameter parentParameter = c.CreateParameter();
412            parentParameter.ParameterName = "@ParentAgentId";
413            parentParameter.Value = parentAgentId;
414            c.Parameters.Add(parentParameter);
415
416            using(DbDataReader r = c.ExecuteReader()) {
417              while(r.Read()) {
418                AgentEntry agent = new AgentEntry();
419                agent.ParentAgentId = parentAgentId;
420                agent.Id = r.GetInt32(0);
421                agent.Name = r.IsDBNull(1) ? "-" : r.GetString(1);
422                agent.Status = (ProcessStatus)Enum.Parse(typeof(ProcessStatus), r.GetString(2));
423                agents.Add(agent);
424              }
425            }
426          }
427        }
428      } finally {
429        rwLock.ExitReadLock();
430      }
431      return agents;
432    }
433
434    public byte[] GetAgentRawData(long id) {
435      rwLock.EnterReadLock();
436      try {
437        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
438          cnn.Open();
439          using(DbCommand c = cnn.CreateCommand()) {
440            c.CommandText = "Select RawData from Agent where Id=@Id";
441            DbParameter idParameter = c.CreateParameter();
442            idParameter.ParameterName = "@Id";
443            idParameter.Value = id;
444            c.Parameters.Add(idParameter);
445            using(DbDataReader r = c.ExecuteReader()) {
446              if(r.HasRows) {
447                r.Read();
448                return (byte[])r.GetValue(0);
449              }
450            }
451          }
452        }
453      } finally {
454        rwLock.ExitReadLock();
455      }
456      return null; // agent with the given id not found
457    }
458
459    public ICollection<ResultEntry> GetResults(long agentId) {
460      List<ResultEntry> results = new List<ResultEntry>();
461      rwLock.EnterReadLock();
462      try {
463        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
464          cnn.Open();
465          using(DbCommand c = cnn.CreateCommand()) {
466            c.CommandText = "Select Id, CreationTime, Summary, Description from Result where AgentId=@AgentId";
467            DbParameter agentParam = c.CreateParameter();
468            agentParam.ParameterName = "@AgentId";
469            agentParam.Value = agentId;
470            c.Parameters.Add(agentParam);
471            using(DbDataReader r = c.ExecuteReader()) {
472              while(r.Read()) {
473                ResultEntry result = new ResultEntry();
474                result.AgentId = agentId;
475                result.Id = r.GetInt32(0);
476                result.CreationTime = r.GetDateTime(1);
477                result.Summary = r.GetString(2);
478                result.Description = r.GetString(3);
479                results.Add(result);
480              }
481            }
482          }
483        }
484      } finally {
485        rwLock.ExitReadLock();
486      }
487      return results;
488    }
489
490    public ICollection<ResultEntry> GetSubResults(long resultId) {
491      List<ResultEntry> results = new List<ResultEntry>();
492      rwLock.EnterReadLock();
493      try {
494        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
495          cnn.Open();
496          using(DbCommand c = cnn.CreateCommand()) {
497            c.CommandText = "Select Id, CreationTime, Summary, Description from Result where ParentResultId=@ParentResultId";
498            DbParameter parentParam = c.CreateParameter();
499            parentParam.ParameterName = "@ParentResultId";
500            parentParam.Value = resultId;
501            c.Parameters.Add(parentParam);
502            using(DbDataReader r = c.ExecuteReader()) {
503              while(r.Read()) {
504                ResultEntry result = new ResultEntry();
505                result.ParentResultId = resultId;
506                result.Id = r.GetInt32(0);
507                result.CreationTime = r.GetDateTime(1);
508                result.Summary = r.GetString(2);
509                result.Description = r.GetString(3);
510                results.Add(result);
511              }
512            }
513          }
514        }
515      } finally {
516        rwLock.ExitReadLock();
517      }
518      return results;
519    }
520
521    public byte[] GetResultRawData(long id) {
522      rwLock.EnterReadLock();
523      try {
524        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
525          cnn.Open();
526          using(DbCommand c = cnn.CreateCommand()) {
527            c.CommandText = "Select RawData from Result where Id=@Id";
528            DbParameter idParameter = c.CreateParameter();
529            idParameter.ParameterName = "@Id";
530            idParameter.Value = id;
531            c.Parameters.Add(idParameter);
532            using(DbDataReader r = c.ExecuteReader()) {
533              if(r.HasRows) {
534                r.Read();
535                return (byte[])r.GetValue(0);
536              }
537            }
538          }
539        }
540      } finally {
541        rwLock.ExitReadLock();
542      }
543      return null; // result with the given id not found
544    }
545
546    public ICollection<OperatorEntry> GetOperators() {
547      rwLock.EnterReadLock();
548      List<OperatorEntry> operators = new List<OperatorEntry>();
549      try {
550        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
551          cnn.Open();
552          using(DbCommand c = cnn.CreateCommand()) {
553            c.CommandText = "Select id, name, rawdata from Operator";
554            using(DbDataReader r = c.ExecuteReader()) {
555              while(r.Read()) {
556                OperatorEntry op = new OperatorEntry();
557                op.Id = r.GetInt32(0);
558                op.Name = r.IsDBNull(1) ? "-" : r.GetString(1);
559                op.RawData = (byte[])r.GetValue(2);
560                operators.Add(op);
561              }
562            }
563          }
564        }
565      } finally {
566        rwLock.ExitReadLock();
567      }
568      return operators;
569    }
570    public OperatorEntry GetOperator(long id) {
571      rwLock.EnterReadLock();
572      try {
573        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
574          cnn.Open();
575          using(DbCommand c = cnn.CreateCommand()) {
576            c.CommandText = "Select id, name, rawdata from Operator where id=@Id";
577            DbParameter idParam = c.CreateParameter();
578            idParam.ParameterName = "@Id";
579            idParam.Value = id;
580            c.Parameters.Add(idParam);
581            using(DbDataReader r = c.ExecuteReader()) {
582              r.Read();
583              OperatorEntry op = new OperatorEntry();
584              op.Id = r.GetInt32(0);
585              op.Name = r.IsDBNull(1) ? "-" : r.GetString(1);
586              op.RawData = (byte[])r.GetValue(2);
587              return op;
588            }
589          }
590        }
591      } finally {
592        rwLock.ExitReadLock();
593      }
594    }
595    #endregion
596
597    #region delete operator
598    public void DeleteOperator(long id) {
599      rwLock.EnterWriteLock();
600      try {
601        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
602          cnn.Open();
603          using(DbTransaction t = cnn.BeginTransaction()) {
604            using(DbCommand c = cnn.CreateCommand()) {
605              c.Transaction = t;
606              c.CommandText = "Delete from Operator where id=@Id";
607              DbParameter idParam = c.CreateParameter();
608              idParam.ParameterName = "@Id";
609              idParam.Value = id;
610              c.Parameters.Add(idParam);
611              c.ExecuteNonQuery();
612            }
613            t.Commit();
614          }
615        }
616      } finally {
617        rwLock.ExitWriteLock();
618      }
619    }
620    #endregion
621  }
622}
Note: See TracBrowser for help on using the repository browser.