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