Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: master
Fetching contributors…

Cannot retrieve contributors at this time

file 198 lines (166 sloc) 7.32 kb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using log4net;

namespace DataRepository
{
    /// <summary>
    /// stores the data into a SQLite database
    /// </summary>
    public class SQLiteRepository : IRepository
    {
        private static readonly ILog cLogger = LogManager.GetLogger(typeof(SQLiteRepository).Name);
        private const string SOURCE_TABLE_NAME = "source";

        private const string SQL_CREATE_TABLE =
            "CREATE TABLE IF NOT EXISTS " + SOURCE_TABLE_NAME + " (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
            "student VARCHAR(100) NOT NULL, assignment VARCHAR(100) NOT NULL, source TEXT NOT NULL, tokens TEXT NOT NULL)";

        private const string SQL_INSERT_SET = "INSERT INTO " + SOURCE_TABLE_NAME +
            "(student, assignment, source, tokens) VALUES(@student, @assignment, @sourcecode, @tokens);";

        private const string SQL_RESET = "DELETE FROM " + SOURCE_TABLE_NAME;

        private const string SQL_SELECT_BY_ASSIGNMENT = "SELECT student, assignment, tokens FROM " + SOURCE_TABLE_NAME + " WHERE assignment=@assignment;";

        /// <summary>
        /// path to the repository directory
        /// </summary>
        private static readonly string REPOSITORY_PATH = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData),
                                                            "MUTEX", "repository");

        /// <summary>
        /// full path to the reference DB file
        /// </summary>
        private static readonly string REFERENCE_DB_PATH = Path.Combine(REPOSITORY_PATH, "reference.sqlite3");

        /// <summary>
        /// full path to the archive DB file
        /// </summary>
        private static readonly string ARCHIVE_DB_PATH = Path.Combine(REPOSITORY_PATH, "archive.sqlite3");

        /// <summary>
        /// SQLiteConnection to the reference database
        /// </summary>
        private readonly SQLiteConnection referenceConnection;

        /// <summary>
        /// SQLiteConnection to the archive database
        /// </summary>
        private readonly SQLiteConnection archiveConnection;

        /// <summary>
        /// creates the database file and the table if they don't exist
        /// </summary>
        public SQLiteRepository()
        {
            cLogger.DebugFormat("reference db connection string: {0}", string.Format("Data Source={0}", REFERENCE_DB_PATH));

            referenceConnection = new SQLiteConnection(string.Format("Data Source={0}", REFERENCE_DB_PATH));
            archiveConnection = new SQLiteConnection(string.Format("Data Source={0}", ARCHIVE_DB_PATH));
            cLogger.Debug("SQLiteRepository.ctor()");
            if (!Directory.Exists(REPOSITORY_PATH))
            {
                cLogger.DebugFormat("directory '{0}' does not exist", REPOSITORY_PATH);
                Directory.CreateDirectory(REPOSITORY_PATH);
                SQLiteConnection.CreateFile(REFERENCE_DB_PATH);
                SQLiteConnection.CreateFile(ARCHIVE_DB_PATH);
            }

            referenceConnection.Open();
            archiveConnection.Open();

            CreateTables(referenceConnection);
            CreateTables(archiveConnection);
        }

        /// <summary>
        /// creates the tables
        /// </summary>
        /// <param name="sqLiteConnection"></param>
        private static void CreateTables(SQLiteConnection sqLiteConnection)
        {
            var command = new SQLiteCommand(sqLiteConnection)
            {
                CommandText = "PRAGMA encoding='UTF-8';"
            };

            command.ExecuteNonQuery();

            command.CommandText = SQL_CREATE_TABLE;
            command.ExecuteNonQuery();
        }


        public void Reset()
        {
            var command = new SQLiteCommand
                              {
                                  Connection = referenceConnection,
                                  CommandText = SQL_RESET
                              };

            command.ExecuteNonQuery();

            command.Connection = archiveConnection;
            command.ExecuteNonQuery();

            cLogger.Debug("reset successful");
        }

        /// <summary>
        /// stores the data
        /// </summary>
        /// <param name="data"></param>
        /// <param name="keepForTests"></param>
        public void Store(SourceEntityData data, bool keepForTests)
        {
            var command = new SQLiteCommand(keepForTests ? referenceConnection : archiveConnection)
            {
                CommandText = SQL_INSERT_SET,
                CommandType = CommandType.Text,
                Parameters =
                    {
                        new SQLiteParameter("@student", data.StudentIdentifier),
                        new SQLiteParameter("@assignment", data.AssignmentIdentifier),
                        new SQLiteParameter("@sourcecode", data.RawSource),
                        new SQLiteParameter("@tokens", SerializeTokens(data.Tokens))
                    }
            };

            cLogger.DebugFormat("storing '{0}' by '{1}'", data.AssignmentIdentifier, data.StudentIdentifier);
            cLogger.DebugFormat("tokens stored: {0}", SerializeTokens(data.Tokens));
            command.ExecuteNonQuery();
        }


        /// <summary>
        /// loads data filtered by assignment
        /// </summary>
        /// <param name="assignment"></param>
        /// <returns></returns>
        public IEnumerable<SourceEntityData> LoadByAssignment(string assignment)
        {
            var command = new SQLiteCommand
            {
                Connection = referenceConnection,
                CommandType = CommandType.Text,
                CommandText = SQL_SELECT_BY_ASSIGNMENT,
                Parameters =
                    {
                        new SQLiteParameter("@assignment", assignment)
                    }
            };

            var list = new List<SourceEntityData>();
            using(var reader = command.ExecuteReader())
            {
                while(reader.Read())
                {
                    list.Add(new SourceEntityData(
                        reader.GetString(reader.GetOrdinal("student")),
                        assignment,
                        reader.GetString(reader.GetOrdinal("tokens")).Split(new[] {' '}),
                        string.Empty));
                }
            }

            cLogger.DebugFormat("loaded {0} data sets for assignment {1}", list.Count, assignment);
            return list;
        }

        /// <summary>
        /// concatenates tokens with a single whitespace in between tokens
        /// </summary>
        /// <param name="tokens"></param>
        /// <returns></returns>
        private static string SerializeTokens(IEnumerable<string> tokens)
        {
            var builder = new StringBuilder();

            foreach (var element in tokens)
                builder.AppendFormat("{0} ", element);

            return builder.ToString().Trim();
        }

        public void Dispose()
        {
            referenceConnection.Dispose();
            archiveConnection.Dispose();
        }
    }
}
Something went wrong with that request. Please try again.