Free cookie consent management tool by TermsFeed Policy Generator

source: trunk/sources/HeuristicLab.Modeling.Database.SQLServerCompact/3.2/DatabaseService.cs @ 2525

Last change on this file since 2525 was 2525, checked in by mkommend, 13 years ago

adapted SQLServerCompact database backend to meet new requirements regarding multiple connections (ticket #800)

File size: 15.6 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.Reflection;
26
27using HeuristicLab.Core;
28using HeuristicLab.DataAnalysis;
29using HeuristicLab.Data;
30using System.Data.Linq;
31
32namespace HeuristicLab.Modeling.Database.SQLServerCompact {
33  public class DatabaseService : IModelingDatabase {
34    private readonly string fileName;
35
36    public DatabaseService(string fileName) {
37      this.fileName = fileName;
38      this.readOnly = false;
39
40    }
41    public DatabaseService(string fileName, bool readOnly)
42      : this(fileName) {
43      this.readOnly = readOnly;
44    }
45
46    private string ConnectionString {
47      get {
48        string connection = "Data Source =" + fileName + ";";
49        if (this.readOnly)
50          connection += "File Mode = Read Only; Temp Path =" + System.IO.Path.GetTempPath() + ";";
51        else
52          connection += "File Mode = Shared Read;";
53        return connection;
54      }
55    }
56
57    private bool readOnly;
58    public bool ReadOnly {
59      get { return this.readOnly; }
60      set {
61        if (ctx != null)
62          throw new InvalidOperationException("Could not change readonly attribute of DatabaseService because connection is opened.");
63        this.readOnly = value;
64      }
65    }
66
67    public void EmptyDatabase() {
68      ctx.Connection.Dispose();
69      ctx.DeleteDatabase();
70      Connect();
71      ctx.CreateDatabase();
72      Disconnect();
73    }
74
75    private ModelingDataContext ctx;
76    public void Connect() {
77      if (ctx != null)
78        Disconnect();
79
80      ctx = new ModelingDataContext(this.ConnectionString);
81      DataLoadOptions dlo = new DataLoadOptions();
82      dlo.LoadWith<ModelResult>(mr => mr.Result);
83      dlo.LoadWith<ModelMetaData>(mmd => mmd.MetaData);
84      dlo.LoadWith<InputVariableResult>(ir => ir.Variable);
85      dlo.LoadWith<InputVariableResult>(ir => ir.Result);
86      dlo.LoadWith<Model>(m => m.TargetVariable);
87      dlo.LoadWith<Model>(m => m.Algorithm);
88      ctx.LoadOptions = dlo;
89
90      if (!ctx.DatabaseExists())
91        ctx.CreateDatabase();
92    }
93
94    public void Disconnect() {
95      if (ctx == null)
96        return;
97      ctx.Connection.Dispose();
98      ctx.Dispose();
99      ctx = null;
100    }
101
102    public IEnumerable<IModel> GetAllModels() {
103      return ctx.Models.ToList().Cast<IModel>();
104    }
105
106    public IEnumerable<int> GetAllModelIds() {
107      return from m in ctx.Models
108             select m.Id;
109    }
110
111    public IEnumerable<IVariable> GetAllVariables() {
112      return ctx.Variables.ToList().Cast<IVariable>();
113    }
114
115    public IEnumerable<IResult> GetAllResults() {
116      return ctx.Results.ToList().Cast<IResult>();
117    }
118
119    public IEnumerable<IResult> GetAllResultsForInputVariables() {
120      return (from ir in ctx.InputVariableResults select ir.Result).Distinct().ToList().Cast<IResult>();
121    }
122
123    public IEnumerable<IMetaData> GetAllMetaData() {
124      return ctx.MetaData.ToList().Cast<IMetaData>();
125    }
126
127    public IEnumerable<IAlgorithm> GetAllAlgorithms() {
128      return ctx.Algorithms.ToList().Cast<IAlgorithm>();
129    }
130
131    public IModel CreateModel(int id, string modelName, ModelType modelType, IAlgorithm algorithm, IVariable targetVariable,
132        int trainingSamplesStart, int trainingSamplesEnd, int validationSamplesStart, int validationSamplesEnd, int testSamplesStart, int testSamplesEnd) {
133      Model m = (Model)CreateModel(modelName, modelType, algorithm, targetVariable, trainingSamplesStart, trainingSamplesEnd, validationSamplesStart, validationSamplesEnd, testSamplesStart, testSamplesEnd);
134      m.Id = id;
135      return m;
136    }
137
138    public IModel CreateModel(string modelName, ModelType modelType, IAlgorithm algorithm, IVariable targetVariable,
139     int trainingSamplesStart, int trainingSamplesEnd, int validationSamplesStart, int validationSamplesEnd, int testSamplesStart, int testSamplesEnd) {
140      Variable target = (Variable)targetVariable;
141      Algorithm algo = (Algorithm)algorithm;
142      Model model = new Model(target, algo, modelType);
143      model.Name = modelName;
144      model.TrainingSamplesStart = trainingSamplesStart;
145      model.TrainingSamplesEnd = trainingSamplesEnd;
146      model.ValidationSamplesStart = validationSamplesStart;
147      model.ValidationSamplesEnd = validationSamplesEnd;
148      model.TestSamplesStart = testSamplesStart;
149      model.TestSamplesEnd = testSamplesEnd;
150
151      return model;
152    }
153
154    public IModel GetModel(int id) {
155      var model = ctx.Models.Where(m => m.Id == id);
156      if (model.Count() == 1)
157        return model.Single();
158      return null;
159    }
160
161    public void PersistModel(IModel model) {
162      Model m = (Model)model;
163      //check if model has to be updated or inserted
164      if (ctx.Models.Any(x => x.Id == model.Id)) {
165        Model orginal = ctx.Models.GetOriginalEntityState(m);
166        if (orginal == null)
167          ctx.Models.Attach(m);
168        ctx.Refresh(RefreshMode.KeepCurrentValues, m);
169      } else
170        ctx.Models.InsertOnSubmit(m);
171      ctx.SubmitChanges();
172    }
173
174    public void DeleteModel(IModel model) {
175      Model m = (Model)model;
176      ctx.ModelData.DeleteAllOnSubmit(ctx.ModelData.Where(x => x.Model == m));
177      ctx.ModelMetaData.DeleteAllOnSubmit(ctx.ModelMetaData.Where(x => x.Model == m));
178      ctx.ModelResults.DeleteAllOnSubmit(ctx.ModelResults.Where(x => x.Model == m));
179      ctx.InputVariableResults.DeleteAllOnSubmit(ctx.InputVariableResults.Where(x => x.Model == m));
180      ctx.InputVariables.DeleteAllOnSubmit(ctx.InputVariables.Where(x => x.Model == m));
181      Model orginal = ctx.Models.GetOriginalEntityState(m);
182      if (orginal == null)
183        ctx.Models.Attach(m);
184      ctx.Models.DeleteOnSubmit(m);
185      ctx.SubmitChanges();
186    }
187
188    public Dataset GetDataset() {
189      if (ctx.Problems.Count() > 1)
190        throw new InvalidOperationException("Could not get dataset. More than one problems are persisted in the database.");
191      if (ctx.Problems.Count() == 1)
192        return ctx.Problems.Single().Dataset;
193      return null;
194    }
195
196    public void PersistProblem(Dataset dataset) {
197      Problem problem;
198      if (ctx.Problems.Count() != 0)
199        throw new InvalidOperationException("Could not persist dataset. A dataset is already saved in the database.");
200      problem = new Problem(dataset);
201      ctx.Problems.InsertOnSubmit(problem);
202      foreach (string variable in dataset.VariableNames) {
203        ctx.Variables.InsertOnSubmit(new Variable(variable));
204      }
205      ctx.SubmitChanges();
206    }
207
208    public IVariable GetVariable(string variableName) {
209      var variables = ctx.Variables.Where(v => v.Name == variableName);
210      if (variables.Count() != 1)
211        throw new ArgumentException("Zero or more than one variable with the name " + variableName + " are persisted in the database.");
212      return variables.Single();
213    }
214
215    public IPredictor GetModelPredictor(IModel model) {
216      var data = (from md in ctx.ModelData
217                  where md.Model == model
218                  select md);
219      if (data.Count() != 1)
220        throw new ArgumentException("No predictor persisted for given model!");
221      return (IPredictor)PersistenceManager.RestoreFromGZip(data.Single().Data);
222    }
223
224    public void PersistPredictor(IModel model, IPredictor predictor) {
225      Model m = (Model)model;
226      ctx.ModelData.DeleteAllOnSubmit(ctx.ModelData.Where(x => x.Model == m));
227      ctx.ModelResults.DeleteAllOnSubmit(ctx.ModelResults.Where(x => x.Model == m));
228      ctx.InputVariableResults.DeleteAllOnSubmit(ctx.InputVariableResults.Where(x => x.Model == m));
229      ctx.InputVariables.DeleteAllOnSubmit(ctx.InputVariables.Where(x => x.Model == m));
230
231      ctx.ModelData.InsertOnSubmit(new ModelData(m, PersistenceManager.SaveToGZip(predictor)));
232      foreach (string variableName in predictor.GetInputVariables())
233        ctx.InputVariables.InsertOnSubmit(new InputVariable(m, (Variable)GetVariable(variableName)));
234
235      ctx.SubmitChanges();
236    }
237
238    public IInputVariable GetInputVariable(IModel model, string inputVariableName) {
239      var inputVariables = ctx.InputVariables.Where(i => i.Model == model && i.Variable.Name == inputVariableName);
240      if (inputVariables.Count() == 1)
241        return inputVariables.Single();
242
243      if (inputVariables.Count() > 1)
244        throw new ArgumentException("More than one input variable with the same name are for the given model persisted.");
245
246      return null;
247    }
248
249    public IAlgorithm GetOrPersistAlgorithm(string algorithmName) {
250      Algorithm algorithm;
251      var algorithms = ctx.Algorithms.Where(algo => algo.Name == algorithmName);
252      if (algorithms.Count() == 0) {
253        algorithm = new Algorithm(algorithmName, "");
254        ctx.Algorithms.InsertOnSubmit(algorithm);
255        ctx.SubmitChanges();
256      } else if (algorithms.Count() == 1)
257        algorithm = algorithms.Single();
258      else
259        throw new ArgumentException("Could not get Algorithm. More than one algorithm with the name " + algorithmName + " are saved in database.");
260      return algorithm;
261    }
262
263    public IResult GetOrPersistResult(string resultName) {
264      Result result;
265      var results = ctx.Results.Where(r => r.Name == resultName);
266      if (results.Count() == 0) {
267        result = new Result(resultName);
268        ctx.Results.InsertOnSubmit(result);
269        ctx.SubmitChanges();
270      } else if (results.Count() == 1)
271        result = results.Single();
272      else
273        throw new ArgumentException("Could not get result. More than one result with the name " + resultName + " are saved in database.");
274      return result;
275    }
276
277    public IMetaData GetOrPersistMetaData(string metaDataName) {
278      MetaData metadata;
279      var md = ctx.MetaData.Where(r => r.Name == metaDataName);
280      if (md.Count() == 0) {
281        metadata = new MetaData(metaDataName);
282        ctx.MetaData.InsertOnSubmit(metadata);
283        ctx.SubmitChanges();
284      } else if (md.Count() == 1)
285        metadata = md.Single();
286      else
287        throw new ArgumentException("Could not get metadata. More than one metadata with the name " + metaDataName + " are saved in database.");
288      return metadata;
289    }
290
291    public IEnumerable<IModelResult> GetModelResults(IModel model) {
292      return ctx.ModelResults.Where(mr => mr.Model == model).Cast<IModelResult>();
293    }
294    public IEnumerable<IInputVariableResult> GetInputVariableResults(IModel model) {
295      return ctx.InputVariableResults.Where(ivr => ivr.Model == model).Cast<IInputVariableResult>();
296    }
297    public IEnumerable<IModelMetaData> GetModelMetaData(IModel model) {
298      return ctx.ModelMetaData.Where(md => md.Model == model).Cast<IModelMetaData>();
299    }
300
301    public IModelResult CreateModelResult(IModel model, IResult result, double value) {
302      Model m = (Model)model;
303      Result r = (Result)result;
304      return new ModelResult(m, r, value);
305    }
306
307    public void PersistModelResults(IModel model, IEnumerable<IModelResult> modelResults) {
308      ctx.ModelResults.DeleteAllOnSubmit(GetModelResults(model).Cast<ModelResult>());
309      ctx.ModelResults.InsertAllOnSubmit(modelResults.Cast<ModelResult>());
310      ctx.SubmitChanges();
311    }
312
313    public IInputVariable CreateInputVariable(IModel model, IVariable variable) {
314      InputVariable inputVariable = new InputVariable((Model)model, (Variable)variable);
315      return inputVariable;
316    }
317
318    public IInputVariableResult CreateInputVariableResult(IInputVariable inputVariable, IResult result, double value) {
319      InputVariable i = (InputVariable)inputVariable;
320      Result r = (Result)result;
321      return new InputVariableResult(i, r, value);
322    }
323
324    public void PersistInputVariableResults(IModel model, IEnumerable<IInputVariableResult> inputVariableResults) {
325      ctx.InputVariableResults.DeleteAllOnSubmit(GetInputVariableResults(model).Cast<InputVariableResult>());
326      ctx.InputVariableResults.InsertAllOnSubmit(inputVariableResults.Cast<InputVariableResult>());
327      ctx.SubmitChanges();
328    }
329
330    public IModelMetaData CreateModelMetaData(IModel model, IMetaData metadata, double value) {
331      Model m = (Model)model;
332      MetaData md = (MetaData)metadata;
333      return new ModelMetaData(m, md, value);
334    }
335
336    public void PersistModelMetaData(IModel model, IEnumerable<IModelMetaData> modelMetaData) {
337      ctx.ModelMetaData.DeleteAllOnSubmit(GetModelMetaData(model).Cast<ModelMetaData>());
338      ctx.ModelMetaData.InsertAllOnSubmit(modelMetaData.Cast<ModelMetaData>());
339      ctx.SubmitChanges();
340    }
341
342    public IModel Persist(HeuristicLab.Modeling.IAlgorithm algorithm) {
343      if (ctx.Problems.Count() == 0)
344        PersistProblem(algorithm.Dataset);
345      return Persist(algorithm.Model, algorithm.Name, algorithm.Description);
346    }
347
348    public IModel Persist(HeuristicLab.Modeling.IAnalyzerModel model, string algorithmName, string algorithmDescription) {
349      Algorithm algorithm = (Algorithm)GetOrPersistAlgorithm(algorithmName);
350      Variable targetVariable = (Variable) GetVariable(model.TargetVariable);
351      Model m = (Model)CreateModel(null, model.Type, algorithm, targetVariable, model.TrainingSamplesStart, model.TrainingSamplesEnd,
352        model.ValidationSamplesStart, model.ValidationSamplesEnd, model.TestSamplesStart, model.TestSamplesEnd);
353      ctx.Models.InsertOnSubmit(m);
354      ctx.SubmitChanges();
355      ctx.ModelData.InsertOnSubmit(new ModelData(m, PersistenceManager.SaveToGZip(model.Predictor)));
356
357      foreach (string variableName in model.Predictor.GetInputVariables())
358        ctx.InputVariables.InsertOnSubmit(new InputVariable(m, (Variable)GetVariable(variableName)));
359
360      foreach (KeyValuePair<string, double> pair in model.MetaData) {
361        MetaData metaData = (MetaData)GetOrPersistMetaData(pair.Key);
362        ctx.ModelMetaData.InsertOnSubmit(new ModelMetaData(m, metaData, pair.Value));
363      }
364
365      foreach (KeyValuePair<ModelingResult, double> pair in model.Results) {
366        Result result = (Result)GetOrPersistResult(pair.Key.ToString());
367        ctx.ModelResults.InsertOnSubmit(new ModelResult(m, result, pair.Value));
368      }
369
370      foreach (InputVariable variable in ctx.InputVariables.Where(iv => iv.Model == m)) {
371        foreach (KeyValuePair<ModelingResult, double> variableResult in model.GetVariableResults(variable.Variable.Name)) {
372          Result result = (Result)GetOrPersistResult(variableResult.Key.ToString());
373          ctx.InputVariableResults.InsertOnSubmit(new InputVariableResult(variable, result, variableResult.Value));
374        }
375      }
376      ctx.SubmitChanges();
377
378      //if connected to database return inserted model
379      if (this.ctx != null)
380        return this.ctx.Models.Where(x => x.Id == m.Id).Single();
381      return null;
382    }
383  }
384}
Note: See TracBrowser for help on using the repository browser.