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 |
|
---|
22 | using System;
|
---|
23 | using System.Collections.Generic;
|
---|
24 | using System.Linq;
|
---|
25 | using System.Text;
|
---|
26 | using System.Data.Linq;
|
---|
27 | using HeuristicLab.CEDMA.DB.Interfaces;
|
---|
28 | using System.ServiceModel;
|
---|
29 | using System.Data;
|
---|
30 | using System.Data.SQLite;
|
---|
31 | using System.Data.Common;
|
---|
32 | using System.Threading;
|
---|
33 |
|
---|
34 | namespace 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 | t.Commit();
|
---|
67 | }
|
---|
68 | }
|
---|
69 | } finally {
|
---|
70 | rwLock.ExitWriteLock();
|
---|
71 | }
|
---|
72 | }
|
---|
73 | #endregion
|
---|
74 |
|
---|
75 | #region insert agent/result/sub-result
|
---|
76 | public long InsertAgent(long? parentAgentId, string name, byte[] rawData) {
|
---|
77 | rwLock.EnterWriteLock();
|
---|
78 | try {
|
---|
79 | using(DbConnection cnn = new SQLiteConnection(connectionString)) {
|
---|
80 | cnn.Open();
|
---|
81 | long id;
|
---|
82 | using(DbTransaction t = cnn.BeginTransaction()) {
|
---|
83 | using(DbCommand c = cnn.CreateCommand()) {
|
---|
84 | c.Transaction = t;
|
---|
85 | c.CommandText = "Insert into Agent (Name, ParentAgentId, CreationTime, RawData) values (@Name, @ParentAgentId, @CreationTime, @RawData); select last_insert_rowid()";
|
---|
86 | DbParameter nameParam = c.CreateParameter();
|
---|
87 | nameParam.ParameterName = "@Name";
|
---|
88 | nameParam.Value = name;
|
---|
89 | c.Parameters.Add(nameParam);
|
---|
90 | DbParameter parentParam = c.CreateParameter();
|
---|
91 | parentParam.ParameterName = "@ParentAgentId";
|
---|
92 | parentParam.Value = parentAgentId;
|
---|
93 | c.Parameters.Add(parentParam);
|
---|
94 | DbParameter creationTimeParam = c.CreateParameter();
|
---|
95 | creationTimeParam.ParameterName = "@CreationTime";
|
---|
96 | creationTimeParam.Value = DateTime.Now;
|
---|
97 | c.Parameters.Add(creationTimeParam);
|
---|
98 | DbParameter dataParam = c.CreateParameter();
|
---|
99 | dataParam.ParameterName = "@RawData";
|
---|
100 | dataParam.Value = rawData;
|
---|
101 | c.Parameters.Add(dataParam);
|
---|
102 | id = (long)c.ExecuteScalar();
|
---|
103 | }
|
---|
104 | t.Commit();
|
---|
105 | return id;
|
---|
106 | }
|
---|
107 | }
|
---|
108 | } finally {
|
---|
109 | rwLock.ExitWriteLock();
|
---|
110 | }
|
---|
111 | }
|
---|
112 |
|
---|
113 | public long InsertResult(long agentId, string summary, string description, byte[] rawData) {
|
---|
114 | rwLock.EnterWriteLock();
|
---|
115 | try {
|
---|
116 | using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
|
---|
117 | cnn.Open();
|
---|
118 | long id;
|
---|
119 | using(DbTransaction t = cnn.BeginTransaction()) {
|
---|
120 | using(DbCommand c = cnn.CreateCommand()) {
|
---|
121 | c.Transaction = t;
|
---|
122 | c.CommandText = "Insert into Result (AgentId, CreationTime, Summary, Description, RawData) values (@AgentId, @CreationTime, @Summary, @Description, @RawData); select last_insert_rowid()";
|
---|
123 | DbParameter agentIdParam = c.CreateParameter();
|
---|
124 | agentIdParam.ParameterName = "@AgentId";
|
---|
125 | agentIdParam.Value = agentId;
|
---|
126 | c.Parameters.Add(agentIdParam);
|
---|
127 | DbParameter creationParam = c.CreateParameter();
|
---|
128 | creationParam.ParameterName = "@CreationTime";
|
---|
129 | DateTime now = DateTime.Now;
|
---|
130 | creationParam.Value = now;
|
---|
131 | c.Parameters.Add(creationParam);
|
---|
132 | DbParameter summaryParam = c.CreateParameter();
|
---|
133 | summaryParam.ParameterName = "@Summary";
|
---|
134 | summaryParam.Value = summary;
|
---|
135 | c.Parameters.Add(summaryParam);
|
---|
136 | DbParameter descParam = c.CreateParameter();
|
---|
137 | descParam.ParameterName = "@Description";
|
---|
138 | descParam.Value = description;
|
---|
139 | c.Parameters.Add(descParam);
|
---|
140 | DbParameter rawDataParam = c.CreateParameter();
|
---|
141 | rawDataParam.ParameterName = "@RawData";
|
---|
142 | rawDataParam.Value = rawData;
|
---|
143 | c.Parameters.Add(rawDataParam);
|
---|
144 | id = (long)c.ExecuteScalar();
|
---|
145 | }
|
---|
146 | t.Commit();
|
---|
147 | return id;
|
---|
148 | }
|
---|
149 | }
|
---|
150 | } finally {
|
---|
151 | rwLock.ExitWriteLock();
|
---|
152 | }
|
---|
153 | }
|
---|
154 |
|
---|
155 | public long InsertSubResult(long resultId, string summary, string description, byte[] rawData) {
|
---|
156 | rwLock.EnterWriteLock();
|
---|
157 | try {
|
---|
158 | using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
|
---|
159 | cnn.Open();
|
---|
160 | long id;
|
---|
161 | using(DbTransaction t = cnn.BeginTransaction()) {
|
---|
162 | using(DbCommand c = cnn.CreateCommand()) {
|
---|
163 | c.Transaction = t;
|
---|
164 | c.CommandText = "Insert into Result (ParentResultId, CreationTime, Summary, Description, RawData) values (@ParentResultId, @CreationTime, @Summary, @Description, @RawData); select last_insert_rowid()";
|
---|
165 | DbParameter resultIdParam = c.CreateParameter();
|
---|
166 | resultIdParam.ParameterName = "@ParentResultId";
|
---|
167 | resultIdParam.Value = resultId;
|
---|
168 | c.Parameters.Add(resultIdParam);
|
---|
169 | DbParameter creationParam = c.CreateParameter();
|
---|
170 | creationParam.ParameterName = "@CreationTime";
|
---|
171 | DateTime now = DateTime.Now;
|
---|
172 | creationParam.Value = now;
|
---|
173 | c.Parameters.Add(creationParam);
|
---|
174 | DbParameter summaryParam = c.CreateParameter();
|
---|
175 | summaryParam.ParameterName = "@Summary";
|
---|
176 | summaryParam.Value = summary;
|
---|
177 | c.Parameters.Add(summaryParam);
|
---|
178 | DbParameter descParam = c.CreateParameter();
|
---|
179 | descParam.ParameterName = "@Description";
|
---|
180 | descParam.Value = description;
|
---|
181 | c.Parameters.Add(descParam);
|
---|
182 | DbParameter rawDataParam = c.CreateParameter();
|
---|
183 | rawDataParam.ParameterName = "@RawData";
|
---|
184 | rawDataParam.Value = rawData;
|
---|
185 | c.Parameters.Add(rawDataParam);
|
---|
186 | id = (long)c.ExecuteScalar();
|
---|
187 | }
|
---|
188 | t.Commit();
|
---|
189 | return id;
|
---|
190 | }
|
---|
191 | }
|
---|
192 | } finally {
|
---|
193 | rwLock.ExitWriteLock();
|
---|
194 | }
|
---|
195 | }
|
---|
196 | #endregion
|
---|
197 |
|
---|
198 | #region update agent/run
|
---|
199 | public void UpdateAgent(long id, string name) {
|
---|
200 | rwLock.EnterWriteLock();
|
---|
201 | try {
|
---|
202 | using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
|
---|
203 | cnn.Open();
|
---|
204 | using(SQLiteTransaction t = cnn.BeginTransaction()) {
|
---|
205 | using(SQLiteCommand c = cnn.CreateCommand()) {
|
---|
206 | c.Transaction = t;
|
---|
207 | c.CommandText = "Update Agent set Name=@Name where id=@Id";
|
---|
208 | DbParameter nameParam = c.CreateParameter();
|
---|
209 | DbParameter idParam = c.CreateParameter();
|
---|
210 | nameParam.ParameterName = "@Name";
|
---|
211 | nameParam.Value = name;
|
---|
212 | idParam.ParameterName = "@Id";
|
---|
213 | idParam.Value = id;
|
---|
214 | c.Parameters.Add(nameParam);
|
---|
215 | c.Parameters.Add(idParam);
|
---|
216 | c.ExecuteNonQuery();
|
---|
217 | }
|
---|
218 | t.Commit();
|
---|
219 | }
|
---|
220 | }
|
---|
221 | } finally {
|
---|
222 | rwLock.ExitWriteLock();
|
---|
223 | }
|
---|
224 | }
|
---|
225 |
|
---|
226 | public void UpdateAgent(long id, ProcessStatus status) {
|
---|
227 | rwLock.EnterWriteLock();
|
---|
228 | try {
|
---|
229 | using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
|
---|
230 | cnn.Open();
|
---|
231 | using(SQLiteTransaction t = cnn.BeginTransaction()) {
|
---|
232 | using(SQLiteCommand c = cnn.CreateCommand()) {
|
---|
233 | c.Transaction = t;
|
---|
234 | c.CommandText = "Update Agent set Status=@Status where id=@Id";
|
---|
235 | DbParameter statusParam = c.CreateParameter();
|
---|
236 | DbParameter idParam = c.CreateParameter();
|
---|
237 | statusParam.ParameterName = "@Status";
|
---|
238 | statusParam.Value = status.ToString();
|
---|
239 | idParam.ParameterName = "@Id";
|
---|
240 | idParam.Value = id;
|
---|
241 | c.Parameters.Add(statusParam);
|
---|
242 | c.Parameters.Add(idParam);
|
---|
243 | c.ExecuteNonQuery();
|
---|
244 | }
|
---|
245 | t.Commit();
|
---|
246 | }
|
---|
247 | }
|
---|
248 | } finally {
|
---|
249 | rwLock.ExitWriteLock();
|
---|
250 | }
|
---|
251 | }
|
---|
252 |
|
---|
253 | public void UpdateAgent(long id, byte[] rawData) {
|
---|
254 | rwLock.EnterWriteLock();
|
---|
255 | try {
|
---|
256 | using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
|
---|
257 | cnn.Open();
|
---|
258 | using(SQLiteTransaction t = cnn.BeginTransaction()) {
|
---|
259 | using(SQLiteCommand c = cnn.CreateCommand()) {
|
---|
260 | c.Transaction = t;
|
---|
261 | c.CommandText = "Update Agent set RawData=@RawData where id=@Id";
|
---|
262 | DbParameter rawDataParam = c.CreateParameter();
|
---|
263 | DbParameter idParam = c.CreateParameter();
|
---|
264 | rawDataParam.ParameterName = "@RawData";
|
---|
265 | rawDataParam.Value = rawData;
|
---|
266 | idParam.ParameterName = "@Id";
|
---|
267 | idParam.Value = id;
|
---|
268 | c.Parameters.Add(rawDataParam);
|
---|
269 | c.Parameters.Add(idParam);
|
---|
270 | c.ExecuteNonQuery();
|
---|
271 | }
|
---|
272 | t.Commit();
|
---|
273 | }
|
---|
274 | }
|
---|
275 | } finally {
|
---|
276 | rwLock.ExitWriteLock();
|
---|
277 | }
|
---|
278 | }
|
---|
279 |
|
---|
280 | #endregion
|
---|
281 |
|
---|
282 | #region get agent/result/sub-result
|
---|
283 |
|
---|
284 | public ICollection<AgentEntry> GetAgents(ProcessStatus status) {
|
---|
285 | rwLock.EnterReadLock();
|
---|
286 | List<AgentEntry> agents = new List<AgentEntry>();
|
---|
287 | try {
|
---|
288 | using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
|
---|
289 | cnn.Open();
|
---|
290 | SQLiteCommand c = cnn.CreateCommand();
|
---|
291 | c.CommandText = "Select id, name, rawdata from Agent where Status=@Status";
|
---|
292 | DbParameter statusParameter = c.CreateParameter();
|
---|
293 | statusParameter.ParameterName = "@Status";
|
---|
294 | statusParameter.Value = status.ToString();
|
---|
295 | c.Parameters.Add(statusParameter);
|
---|
296 | SQLiteDataReader r = c.ExecuteReader();
|
---|
297 | while(r.Read()) {
|
---|
298 | AgentEntry agent = new AgentEntry();
|
---|
299 | agent.ParentAgentId = null;
|
---|
300 | agent.Status = status;
|
---|
301 | agent.Id = r.GetInt32(0);
|
---|
302 | agent.Name = r.IsDBNull(1)?"":r.GetString(1);
|
---|
303 | agent.RawData = (byte[])r.GetValue(2);
|
---|
304 | agents.Add(agent);
|
---|
305 | }
|
---|
306 | }
|
---|
307 | } finally {
|
---|
308 | rwLock.ExitReadLock();
|
---|
309 | }
|
---|
310 | return agents;
|
---|
311 | }
|
---|
312 |
|
---|
313 | public ICollection<AgentEntry> GetAgents() {
|
---|
314 | rwLock.EnterReadLock();
|
---|
315 | List<AgentEntry> agents = new List<AgentEntry>();
|
---|
316 | try {
|
---|
317 | using(DbConnection cnn = new SQLiteConnection(connectionString)) {
|
---|
318 | cnn.Open();
|
---|
319 | using(DbCommand c = cnn.CreateCommand()) {
|
---|
320 | c.CommandText = "Select id, name, status, rawdata from Agent where ParentAgentId isnull";
|
---|
321 | using(DbDataReader r = c.ExecuteReader()) {
|
---|
322 | while(r.Read()) {
|
---|
323 | AgentEntry agent = new AgentEntry();
|
---|
324 | agent.ParentAgentId = null;
|
---|
325 | agent.Id = r.GetInt32(0);
|
---|
326 | agent.Name = r.IsDBNull(1)?"-":r.GetString(1);
|
---|
327 | agent.Status = (ProcessStatus)Enum.Parse(typeof(ProcessStatus), r.GetString(2));
|
---|
328 | agent.RawData = (byte[])r.GetValue(3);
|
---|
329 | agents.Add(agent);
|
---|
330 | }
|
---|
331 | }
|
---|
332 | }
|
---|
333 | }
|
---|
334 | } finally {
|
---|
335 | rwLock.ExitReadLock();
|
---|
336 | }
|
---|
337 | return agents;
|
---|
338 | }
|
---|
339 |
|
---|
340 | public ICollection<AgentEntry> GetSubAgents(long parentAgentId) {
|
---|
341 | rwLock.EnterReadLock();
|
---|
342 | List<AgentEntry> agents = new List<AgentEntry>();
|
---|
343 | try {
|
---|
344 | using(DbConnection cnn = new SQLiteConnection(connectionString)) {
|
---|
345 | cnn.Open();
|
---|
346 | using(DbCommand c = cnn.CreateCommand()) {
|
---|
347 | c.CommandText = "Select id, name, status, rawdata from Agent where ParentAgentId=@ParentAgentId";
|
---|
348 | DbParameter parentParameter = c.CreateParameter();
|
---|
349 | parentParameter.ParameterName = "@ParentAgentId";
|
---|
350 | parentParameter.Value = parentAgentId;
|
---|
351 | c.Parameters.Add(parentParameter);
|
---|
352 |
|
---|
353 | using(DbDataReader r = c.ExecuteReader()) {
|
---|
354 | while(r.Read()) {
|
---|
355 | AgentEntry agent = new AgentEntry();
|
---|
356 | agent.ParentAgentId = parentAgentId;
|
---|
357 | agent.Id = r.GetInt32(0);
|
---|
358 | agent.Name = r.IsDBNull(1)?"-":r.GetString(1);
|
---|
359 | agent.Status = (ProcessStatus)Enum.Parse(typeof(ProcessStatus), r.GetString(2));
|
---|
360 | agent.RawData = (byte[])r.GetValue(3);
|
---|
361 | agents.Add(agent);
|
---|
362 | }
|
---|
363 | }
|
---|
364 | }
|
---|
365 | }
|
---|
366 | } finally {
|
---|
367 | rwLock.ExitReadLock();
|
---|
368 | }
|
---|
369 | return agents;
|
---|
370 | }
|
---|
371 |
|
---|
372 | public ICollection<ResultEntry> GetResults(long agentId) {
|
---|
373 | List<ResultEntry> results = new List<ResultEntry>();
|
---|
374 | rwLock.EnterReadLock();
|
---|
375 | try {
|
---|
376 | using(DbConnection cnn = new SQLiteConnection(connectionString)) {
|
---|
377 | cnn.Open();
|
---|
378 | using(DbCommand c = cnn.CreateCommand()) {
|
---|
379 | c.CommandText = "Select Id, CreationTime, Summary, Description, Rawdata from Result where AgentId=@AgentId";
|
---|
380 | DbParameter agentParam = c.CreateParameter();
|
---|
381 | agentParam.ParameterName = "@AgentId";
|
---|
382 | agentParam.Value = agentId;
|
---|
383 | c.Parameters.Add(agentParam);
|
---|
384 | using(DbDataReader r = c.ExecuteReader()) {
|
---|
385 | while(r.Read()) {
|
---|
386 | ResultEntry result = new ResultEntry();
|
---|
387 | result.AgentId = agentId;
|
---|
388 | result.Id = r.GetInt32(0);
|
---|
389 | result.CreationTime = r.GetDateTime(1);
|
---|
390 | result.Summary = r.GetString(2);
|
---|
391 | result.Description = r.GetString(3);
|
---|
392 | result.RawData = (byte[])r.GetValue(4);
|
---|
393 | results.Add(result);
|
---|
394 | }
|
---|
395 | }
|
---|
396 | }
|
---|
397 | }
|
---|
398 | } finally {
|
---|
399 | rwLock.ExitReadLock();
|
---|
400 | }
|
---|
401 | return results;
|
---|
402 | }
|
---|
403 |
|
---|
404 | public ICollection<ResultEntry> GetSubResults(long resultId) {
|
---|
405 | List<ResultEntry> results = new List<ResultEntry>();
|
---|
406 | rwLock.EnterReadLock();
|
---|
407 | try {
|
---|
408 | using(DbConnection cnn = new SQLiteConnection(connectionString)) {
|
---|
409 | cnn.Open();
|
---|
410 | using(DbCommand c = cnn.CreateCommand()) {
|
---|
411 | c.CommandText = "Select Id, CreationTime, Summary, Description, Rawdata from Result where ParentResultId=@ParentResultId";
|
---|
412 | DbParameter parentParam = c.CreateParameter();
|
---|
413 | parentParam.ParameterName = "@ParentResultId";
|
---|
414 | parentParam.Value = resultId;
|
---|
415 | c.Parameters.Add(parentParam);
|
---|
416 | using(DbDataReader r = c.ExecuteReader()) {
|
---|
417 | while(r.Read()) {
|
---|
418 | ResultEntry result = new ResultEntry();
|
---|
419 | result.ParentResultId = resultId;
|
---|
420 | result.Id = r.GetInt32(0);
|
---|
421 | result.CreationTime = r.GetDateTime(1);
|
---|
422 | result.Summary = r.GetString(2);
|
---|
423 | result.Description = r.GetString(3);
|
---|
424 | result.RawData = (byte[])r.GetValue(4);
|
---|
425 | results.Add(result);
|
---|
426 | }
|
---|
427 | }
|
---|
428 | }
|
---|
429 | }
|
---|
430 | } finally {
|
---|
431 | rwLock.ExitReadLock();
|
---|
432 | }
|
---|
433 | return results;
|
---|
434 | }
|
---|
435 | #endregion
|
---|
436 | }
|
---|
437 | } |
---|