perf/perf-suite-lib/Database.cs

   1 /*
   2  * Copyright (c) 2008 Novell, Inc. (http://www.novell.com)
   3  *
   4  * Contact:
   5  *  Moonlight List (moonlight-list@lists.ximian.com)
   6  *
   7  * Permission is hereby granted, free of charge, to any person
   8  * obtaining a copy of this software and associated documentation
   9  * files (the "Software"), to deal in the Software without
  10  * restriction, including without limitation the rights to use,
  11  * copy, modify, merge, publish, distribute, sublicense, and/or sell
  12  * copies of the Software, and to permit persons to whom the
  13  * Software is furnished to do so, subject to the following
  14  * conditions:
  15  *
  16  * The above copyright notice and this permission notice shall be
  17  * included in all copies or substantial portions of the Software.
  18  *
  19  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  20  * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
  21  * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  22  * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
  23  * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
  24  * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
  25  * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
  26  * OTHER DEALINGS IN THE SOFTWARE.
  27  *
  28  */
  29
  30 using System;
  31 using System.IO;
  32 using System.Xml;
  33 using System.Collections.Generic;
  34 using Mono.Data.SqliteClient;
  35 using System.Data;
  36
  37 namespace PerfSuiteLib {
  38
  39         public static class Database {
  40
  41                 static SqliteConnection connection;
  42                 static IDbTransaction transaction;
  43
  44                 public static void Initialize (string file)
  45                 {
  46                         Console.WriteLine ("*** Initializing database from '{0}' ...", file);
  47
  48                         string uri = String.Format ("URI=file:{0}", file);
  49
  50                         connection = new SqliteConnection (uri);
  51                         connection.Open ();
  52
  53                         if (CheckDatabaseVersion ())
  54                                 CreateTables ();
  55                 }
  56
  57                 public static void BeginTransaction ()
  58                 {
  59                         transaction = connection.BeginTransaction ();
  60                 }
  61
  62                 public static void CommitTransaction ()
  63                 {
  64                         transaction.Commit ();
  65                 }
  66
  67                 public static void Put (DbEntry entry)
  68                 {
  69                         if (entry.IsValid () == false)
  70                                 throw new Exception ("Can't put invalid entry in the database!");
  71
  72                         if (entry.IsInTheDatabase)
  73                                 throw new Exception ("Entry already in the database!");
  74
  75                         IDbCommand cmd = connection.CreateCommand ();
  76                         entry.CreateCommand (ref cmd);
  77
  78                         cmd.ExecuteNonQuery ();
  79                         entry.GiveId (connection.LastInsertRowId);
  80                 }
  81
  82                 public static List <ItemDbEntry> GetAllItemEntries ()
  83                 {
  84                         IDbCommand cmd = connection.CreateCommand ();
  85                         cmd.CommandText = ("SELECT * FROM items");
  86
  87                         IDataReader reader = cmd.ExecuteReader ();
  88                         List <ItemDbEntry> list = new List <ItemDbEntry> ();
  89
  90                         while (reader.Read ())
  91                                 list.Add (new ItemDbEntry (reader, 0));
  92
  93                         return list;
  94                 }
  95
  96                 public static PassDbEntry GetLastPass ()
  97                 {
  98                         IDbCommand cmd = connection.CreateCommand ();
  99                         cmd.CommandText = ("SELECT * " +
 100                                            "FROM passes " +
 101                                            "ORDER BY passes.date DESC " +
 102                                            "LIMIT 1");
 103
 104                         IDataReader reader = cmd.ExecuteReader ();
 105                         if (! reader.Read ())
 106                                 return null;
 107
 108                         return new PassDbEntry (reader, 0);
 109                 }
 110
 111                 public static List <ResultDbEntry> GetResultEntriesForItemEntry (ItemDbEntry item, int limit)
 112                 {
 113                         IDbCommand cmd = connection.CreateCommand ();
 114
 115                         cmd.CommandText = (String.Format ("SELECT results.id, results.item_id, results.pass_id, results.time, passes.id, passes.short_name, passes.author, passes.changelog, passes.date " +
 116                                            "FROM results, passes " +
 117                                            "WHERE results.pass_id = passes.id AND results.item_id = :it " +
 118                                            "ORDER BY passes.date DESC " +
 119                                            "LIMIT {0}", limit));
 120
 121                         IDataParameter p1 = cmd.CreateParameter ();
 122                         p1.ParameterName = ":it";
 123                         p1.Value = item.Id.ToString ();
 124                         cmd.Parameters.Add (p1);
 125
 126                         IDataReader reader = cmd.ExecuteReader ();
 127                         List <ResultDbEntry> list = new List <ResultDbEntry> ();
 128
 129                         while (reader.Read ()) {
 130                                 PassDbEntry pass = new PassDbEntry (reader, 4);
 131                                 list.Add (new ResultDbEntry (reader, 0, item, pass));
 132                         }
 133
 134                         return list;
 135                 }
 136
 137                 public static ItemDbEntry GetItemEntryByUniqueId (string uniqueId)
 138                 {
 139                         IDbCommand cmd = connection.CreateCommand ();
 140                         cmd.CommandText = ("SELECT * FROM items " +
 141                                            "WHERE unique_id = :uq");
 142
 143                         IDataParameter p = cmd.CreateParameter ();
 144                         p.ParameterName = ":uq";
 145                         p.Value = uniqueId;
 146                         cmd.Parameters.Add (p);
 147
 148                         IDataReader reader = cmd.ExecuteReader ();
 149                         if (! reader.Read ())
 150                                 return null;
 151                         else {
 152                                 return new ItemDbEntry (reader, 0);
 153                         }
 154                 }
 155
 156                 private static void CreateTables ()
 157                 {
 158                         Console.WriteLine ("*** Creating database tables...");
 159                         ExecuteCreateCommand ("CREATE TABLE meta (version INTEGER)");
 160                         ExecuteCreateCommand ("INSERT INTO meta VALUES ('1')");
 161
 162                         ExecuteCreateCommand ("CREATE TABLE passes (id INTEGER PRIMARY KEY, short_name TEXT, author TEXT, changelog TEXT, date TEXT)");
 163                         ExecuteCreateCommand ("CREATE TABLE items (id INTEGER PRIMARY KEY, unique_id TEXT, name TEXT, input_file TEXT)");
 164                         ExecuteCreateCommand ("CREATE TABLE results (id INTEGER PRIMARY KEY, item_id INTEGER, pass_id INTEGER, time INTEGER)");
 165                 }
 166
 167                 private static void ExecuteCreateCommand (string cmdString)
 168                 {
 169                         IDbCommand cmd = connection.CreateCommand ();
 170                         cmd.CommandText = cmdString;
 171                         cmd.ExecuteNonQuery ();
 172                 }
 173
 174                 private static bool CheckDatabaseVersion ()
 175                 {
 176                         // FIXME Man, that's ugly
 177                         IDbCommand cmd = connection.CreateCommand ();
 178                         cmd.CommandText = "SELECT * from meta";
 179                         IDataReader reader;
 180                         try {
 181                                 reader = cmd.ExecuteReader ();
 182                                 if (! reader.Read ())
 183                                         return true;
 184                         } catch {
 185                                 return true;
 186                         }
 187
 188                         if (reader [0].ToString () != "1")
 189                                 throw new Exception ("Incompatible database version!");
 190
 191                         return false;
 192                 }
 193
 194         }
 195 }
 196
 197