source: trunk/sources/HeuristicLab.Grid/Database.cs @ 1110

Last change on this file since 1110 was 1110, checked in by gkronber, 12 years ago

implemented #461 (HL.Grid shouldn't depend directly on System.Data.SQLite) by removing the direct reference to System.Data.SQLite and replacing it with a factory in HL.Grid.Database.

File size: 12.1 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;
27using System.Data.Common;
28using System.Threading;
29
30namespace HeuristicLab.Grid {
31  class Database {
32    private string connectionString;
33    private ReaderWriterLockSlim rwLock;
34    private DbProviderFactory factory;
35    public Database(string connectionString) {
36      this.connectionString = connectionString;
37      rwLock = new ReaderWriterLockSlim();
38      factory = DbProviderFactories.GetFactory("System.Data.SQLite");
39    }
40
41    #region create empty database
42    public void CreateNew() {
43      rwLock.EnterWriteLock();
44      try {
45        using (DbConnection cnn = factory.CreateConnection()) {
46          cnn.ConnectionString = connectionString;
47          cnn.Open();
48          using(DbTransaction t = cnn.BeginTransaction()) {
49            using(DbCommand cmd = cnn.CreateCommand()) {
50              cmd.CommandText = "CREATE TABLE Job (ID integer primary key autoincrement, Guid text, Status text, CreationTime DateTime, StartTime DateTime, RawData blob)";
51              cmd.Transaction = t;
52              cmd.ExecuteNonQuery();
53            }
54            t.Commit();
55          }
56        }
57      } finally {
58        rwLock.ExitWriteLock();
59      }
60    }
61    #endregion
62
63    internal void InsertJob(Guid guid, JobState jobState, byte[] rawData) {
64      rwLock.EnterWriteLock();
65      try {
66        using (DbConnection cnn = factory.CreateConnection()) {
67          cnn.ConnectionString = connectionString;
68          cnn.Open();
69          using(DbTransaction t = cnn.BeginTransaction()) {
70            using(DbCommand c = cnn.CreateCommand()) {
71              c.Transaction = t;
72              c.CommandText = "Insert into Job (Guid, Status, CreationTime, StartTime, RawData) values (@Guid, @Status, @CreationTime, @StartTime, @RawData)";
73              DbParameter guidParam = c.CreateParameter();
74              guidParam.ParameterName = "@Guid";
75              guidParam.Value = guid.ToString();
76              c.Parameters.Add(guidParam);
77              DbParameter statusParam = c.CreateParameter();
78              statusParam.ParameterName = "@Status";
79              statusParam.Value = jobState.ToString();
80              c.Parameters.Add(statusParam);
81              DbParameter creationParam = c.CreateParameter();
82              creationParam.ParameterName = "@CreationTime";
83              DateTime now = DateTime.Now;
84              creationParam.Value = now;
85              c.Parameters.Add(creationParam);
86              DbParameter startParam = c.CreateParameter();
87              startParam.ParameterName = "@StartTime";
88              if(jobState == JobState.Busy) startParam.Value = now;
89              else startParam.Value = null;
90              c.Parameters.Add(startParam);
91              DbParameter rawDataParam = c.CreateParameter();
92              rawDataParam.ParameterName = "@RawData";
93              rawDataParam.Value = rawData;
94              c.Parameters.Add(rawDataParam);
95              c.ExecuteNonQuery();
96            }
97            t.Commit();
98          }
99        }
100      } finally {
101        rwLock.ExitWriteLock();
102      }
103
104    }
105
106    internal JobEntry GetNextWaitingJob() {
107      rwLock.EnterUpgradeableReadLock();
108      try {
109        using (DbConnection cnn = factory.CreateConnection()) {
110          cnn.ConnectionString = connectionString;
111          cnn.Open();
112          JobEntry job = new JobEntry();
113          using(DbTransaction t = cnn.BeginTransaction()) {
114            using(DbCommand c = cnn.CreateCommand()) {
115              c.Transaction = t;
116              c.CommandText = "select guid,creationtime,starttime,rawdata from job, (select id from job where status=@Status order by creationtime limit 1) as next where job.id=next.id";
117              DbParameter statusParameter = c.CreateParameter();
118              statusParameter.ParameterName = "@Status";
119              statusParameter.Value = JobState.Waiting.ToString();
120              c.Parameters.Add(statusParameter);
121              DbDataReader r = c.ExecuteReader();
122              if(!r.HasRows) {
123                r.Close();
124                t.Commit();
125                return null;
126              }
127              r.Read();             
128              job.Status = JobState.Busy;
129              job.Guid = r.GetGuid(0);
130              job.CreationTime = r.GetDateTime(1);
131              job.StartTime = r.IsDBNull(2) ? null : new Nullable<DateTime>(r.GetDateTime(2));
132              job.RawData = (byte[])r.GetValue(3);
133              r.Close();
134            }
135            rwLock.EnterWriteLock();
136            try {
137              using(DbCommand updateCmd = cnn.CreateCommand()) {
138                updateCmd.Transaction = t;
139                updateCmd.CommandText = "Update job set Status=@Status where Guid=@Guid";
140                DbParameter statusParam = updateCmd.CreateParameter();
141                statusParam.ParameterName = "@Status";
142                statusParam.Value = JobState.Busy.ToString();
143                DbParameter guidParam = updateCmd.CreateParameter();
144                guidParam.ParameterName = "@Guid";
145                guidParam.Value = job.Guid.ToString();
146                updateCmd.Parameters.Add(statusParam);
147                updateCmd.Parameters.Add(guidParam);
148                updateCmd.ExecuteNonQuery();
149              }
150              t.Commit();
151            } finally {
152              rwLock.ExitWriteLock();
153            }
154            return job;
155          }
156        }
157      } finally {
158        rwLock.ExitUpgradeableReadLock();
159      }
160    }
161
162    internal void SetJobResult(Guid guid, byte[] result) {
163      rwLock.EnterWriteLock();
164      try {
165        using (DbConnection cnn = factory.CreateConnection()) {
166          cnn.ConnectionString = connectionString;
167          cnn.Open();
168          using(DbTransaction t = cnn.BeginTransaction()) {
169            using(DbCommand c = cnn.CreateCommand()) {
170              c.Transaction = t;
171              c.CommandText = "Update Job set Status=@Status, RawData=@RawData where Guid=@Guid";
172              DbParameter rawDataParam = c.CreateParameter();
173              DbParameter statusParam = c.CreateParameter();
174              DbParameter guidParam = c.CreateParameter();
175              rawDataParam.ParameterName = "@RawData";
176              rawDataParam.Value = result;
177              guidParam.ParameterName = "@Guid";
178              guidParam.Value = guid.ToString();
179              statusParam.ParameterName = "@Status";
180              statusParam.Value = JobState.Finished.ToString();
181              c.Parameters.Add(rawDataParam);
182              c.Parameters.Add(statusParam);
183              c.Parameters.Add(guidParam);
184              c.ExecuteNonQuery();
185            }
186            t.Commit();
187          }
188        }
189      } finally {
190        rwLock.ExitWriteLock();
191      }
192    }
193
194    internal void UpdateJobState(Guid guid, JobState jobState) {
195      rwLock.EnterWriteLock();
196      try {
197        using (DbConnection cnn = factory.CreateConnection()) {
198          cnn.ConnectionString = connectionString;
199          cnn.Open();
200          using(DbTransaction t = cnn.BeginTransaction()) {
201            using(DbCommand c = cnn.CreateCommand()) {
202              c.Transaction = t;
203              c.CommandText = "Update Job set Status=@Status, StartTime=@StartTime where Guid=@Guid";
204              DbParameter statusParam = c.CreateParameter();
205              DbParameter startTimeParam = c.CreateParameter();
206              DbParameter guidParam = c.CreateParameter();
207              startTimeParam.ParameterName = "@StartTime";
208              if(jobState == JobState.Busy)
209                startTimeParam.Value = DateTime.Now;
210              else
211                startTimeParam.Value = null;
212              guidParam.ParameterName = "@Guid";
213              guidParam.Value = guid.ToString();
214              statusParam.ParameterName = "@Status";
215              statusParam.Value = jobState.ToString();
216              c.Parameters.Add(startTimeParam);
217              c.Parameters.Add(statusParam);
218              c.Parameters.Add(guidParam);
219              c.ExecuteNonQuery();
220            }
221            t.Commit();
222          }
223        }
224      } finally {
225        rwLock.ExitWriteLock();
226      }
227    }
228
229
230    internal JobEntry GetJob(Guid guid) {
231      rwLock.EnterReadLock();
232      try {
233        using (DbConnection cnn = factory.CreateConnection()) {
234          cnn.ConnectionString = connectionString;
235          cnn.Open();
236          DbCommand c = cnn.CreateCommand();
237          c.CommandText = "Select Status, CreationTime, StartTime, Rawdata from Job where Guid=@Guid";
238          DbParameter guidParameter = c.CreateParameter();
239          guidParameter.ParameterName = "@Guid";
240          guidParameter.Value = guid.ToString();
241          c.Parameters.Add(guidParameter);
242          DbDataReader r = c.ExecuteReader();
243          if(r.HasRows) {
244            r.Read();
245            JobEntry job = new JobEntry();
246            job.Guid = guid;
247            job.Status = (JobState)Enum.Parse(typeof(JobState), r.GetString(0));
248            job.CreationTime = r.GetDateTime(1);
249            job.StartTime = r.IsDBNull(2) ? null : new Nullable<DateTime>(r.GetDateTime(2));
250            job.RawData = (byte[])r.GetValue(3);
251            return job;
252          }
253          r.Close();
254        }
255      } finally {
256        rwLock.ExitReadLock();
257      }
258      return null;
259    }
260
261    internal JobState GetJobState(Guid guid) {
262      rwLock.EnterReadLock();
263      try {
264        using (DbConnection cnn = factory.CreateConnection()) {
265          cnn.ConnectionString = connectionString;
266          cnn.Open();
267          DbCommand c = cnn.CreateCommand();
268          c.CommandText = "Select Status from Job where Guid=@Guid";
269          DbParameter guidParameter = c.CreateParameter();
270          guidParameter.ParameterName = "@Guid";
271          guidParameter.Value = guid.ToString();
272          c.Parameters.Add(guidParameter);
273          DbDataReader r = c.ExecuteReader();
274          if(r.HasRows) {
275            r.Read();
276            return (JobState)Enum.Parse(typeof(JobState), r.GetString(0));
277          }
278          r.Close();
279        }
280      } finally {
281        rwLock.ExitReadLock();
282      }
283      return JobState.Unknown;
284    }
285
286    internal long GetJobCount(JobState status) {
287      rwLock.EnterReadLock();
288      try {
289        using (DbConnection cnn = factory.CreateConnection()) {
290          cnn.ConnectionString = connectionString;
291          cnn.Open();
292          DbCommand c = cnn.CreateCommand();
293          c.CommandText = "Select Count(id) from Job where Status=@Status";
294          DbParameter stateParameter = c.CreateParameter();
295          stateParameter.ParameterName = "@Status";
296          stateParameter.Value = status.ToString();
297          c.Parameters.Add(stateParameter);
298          return (long)c.ExecuteScalar();
299        }
300      } finally {
301        rwLock.ExitReadLock();
302      }
303    }
304
305    /// <summary>
306    /// Does nothing right now (= running jobs that disappear are never restarted).
307    /// </summary>
308    internal void RestartExpiredActiveJobs() {
309    }
310
311    /// <summary>
312    /// Does nothing right now (= results are never deleted).
313    /// </summary>
314    internal void DeleteExpiredResults() {
315    }
316  }
317}
Note: See TracBrowser for help on using the repository browser.