PEPDatabase.cs
author Thomas
Thu, 14 Nov 2019 16:16:03 +0100
branchsync
changeset 2861 590f021d5fb6
parent 2667 1a7d192f0d56
permissions -rw-r--r--
Remove unused handshake signals
     1 ´╗┐using pEpCOMServerAdapterLib;
     2 using System;
     3 using System.Data.SQLite;
     4 using System.IO;
     5 
     6 namespace pEp
     7 {
     8     /// <summary>
     9     /// Class containing all methods to interact with the pEp database.
    10     /// </summary>
    11     internal class PEPDatabase
    12     {
    13         private const string PEP_DB_NAME                                 = "pEp.db";
    14         private const string PEP_DB_FPP_KEYS_TABLE_NAME                  = "FPPPasswords";
    15         private const string PEP_DB_FPP_ENTRY_IDS_TABLE_NAME             = "FPPEntryIds";
    16         private const string PEP_DB_FPP_MESSAGE_ID_COLUMN_NAME           = "MessageId";
    17         private const string PEP_DB_FPP_KEY_COLUMN_NAME                  = "Key";
    18         private const string PEP_DB_FPP_ENTRY_ID_COLUMN_NAME             = "EntryId";
    19         private const string PEP_DB_RATINGS_TABLE_NAME                   = "Ratings";
    20         private const string PEP_DB_RATINGS_ENTRY_ID_COLUMN_NAME         = "EntryIds";
    21         private const string PEP_DB_RATINGS_RATING_COLUMN_NAME           = "Rating";
    22 
    23         /* SQL statement to create the FPP passwords table.
    24          * The table is defined to not allow duplicates and is only created if not yet existing.
    25          * 
    26          *  FPPPasswords
    27          * --------------------
    28          * | MessageID  | Key |
    29          * --------------------
    30          * |            |     |
    31          * --------------------
    32          */
    33         private const string createPasswordsTable = "create table if not exists " + PEPDatabase.PEP_DB_FPP_KEYS_TABLE_NAME + " ("
    34                                                                                   + PEPDatabase.PEP_DB_FPP_MESSAGE_ID_COLUMN_NAME + " varchar(50), "
    35                                                                                   + PEPDatabase.PEP_DB_FPP_KEY_COLUMN_NAME + " varchar(50), unique("
    36                                                                                   + PEPDatabase.PEP_DB_FPP_MESSAGE_ID_COLUMN_NAME + ", "
    37                                                                                   + PEPDatabase.PEP_DB_FPP_KEY_COLUMN_NAME + "))";
    38 
    39         /* SQL statement to create the FPPEntryIds table.
    40          * The table is defined to not allow duplicates and is only created if not yet existing.
    41          * 
    42          *  FPPEntryIds
    43          * -------------------------
    44          * | MessageID  | EntryID  |
    45          * -------------------------
    46          * |            |          |
    47          * -------------------------
    48          */
    49         private const string createEntryIdsTable = "create table if not exists " + PEPDatabase.PEP_DB_FPP_ENTRY_IDS_TABLE_NAME + " ("
    50                                                                                  + PEPDatabase.PEP_DB_FPP_MESSAGE_ID_COLUMN_NAME + " varchar(50), "
    51                                                                                  + PEPDatabase.PEP_DB_FPP_ENTRY_ID_COLUMN_NAME + " varchar(50), unique("
    52                                                                                  + PEPDatabase.PEP_DB_FPP_MESSAGE_ID_COLUMN_NAME + ", "
    53                                                                                  + PEPDatabase.PEP_DB_FPP_ENTRY_ID_COLUMN_NAME + "))";
    54 
    55         /* SQL statement to create the Ratings table.
    56          * The table is defined to not allow duplicates and is only created if not yet existing.
    57          * 
    58          *  Ratings
    59          * -------------------------
    60          * | EntryID    |  Rating  |
    61          * -------------------------
    62          * |            |          |
    63          * -------------------------
    64          */
    65         private const string createRatingsTable = "create table if not exists " + PEPDatabase.PEP_DB_RATINGS_TABLE_NAME + " ("
    66                                                                                 + PEPDatabase.PEP_DB_RATINGS_ENTRY_ID_COLUMN_NAME + " varchar(50), "
    67                                                                                 + PEPDatabase.PEP_DB_RATINGS_RATING_COLUMN_NAME + " varchar(50), unique("
    68                                                                                 + PEPDatabase.PEP_DB_RATINGS_ENTRY_ID_COLUMN_NAME + "))";
    69 
    70         private static bool dbInitialized = false;
    71         private static object mutexDbAccess = new object();
    72 
    73         /// <summary>
    74         /// The database tables used in the pEp database.
    75         /// </summary>
    76         private enum DatabaseTable
    77         {
    78             Keys,
    79             EntryIds,
    80             Ratings
    81         }
    82 
    83         #region Database operations
    84         /**************************************************************
    85          * 
    86          * Database operations
    87          * 
    88          *************************************************************/
    89 
    90         /// <summary>
    91         /// Stores or updates the rating of a given entry id.
    92         /// </summary>
    93         /// <param name="entryId">The entry id to store its rating for.</param>
    94         /// <param name="rating">The rating to store.</param>
    95         /// <returns>The status of the method.</returns>
    96         public static Globals.ReturnStatus StoreOrUpdateRating(string entryId, pEpRating rating)
    97         {
    98             if (string.IsNullOrEmpty(entryId))
    99             {
   100                 Log.Error("StoreOrUpdateRating: EntryId is null or empty.");
   101                 return Globals.ReturnStatus.Failure;
   102             }
   103 
   104             return PEPDatabase.InsertKeyValuePairIntoDb(DatabaseTable.Ratings, entryId, rating.ToEngineString());
   105         }
   106 
   107         /// <summary>
   108         /// Gets the rating for a given entry id.
   109         /// </summary>
   110         /// <param name="entryId">The entry id to retrieve its rating for.</param>
   111         /// <returns>The stored rating or undefined if an error occured.</returns>
   112         public static pEpRating GetRating(string entryId)
   113         {
   114             pEpRating rating = pEpRating.pEpRatingUndefined;
   115 
   116             if (string.IsNullOrEmpty(entryId))
   117             {
   118                 Log.Error("GetRating: EntryId is null or empty.");
   119             }
   120             else
   121             {
   122                 if (PEPDatabase.GetValueFromDb(DatabaseTable.Ratings, entryId, out string ratingString) == Globals.ReturnStatus.Success)
   123                 {
   124                     rating = AdapterExtensions.ParseRatingString(ratingString);
   125                 }
   126             }
   127 
   128             return rating;
   129         }
   130 
   131         /// <summary>
   132         /// Stores the FPP password for a given message id.
   133         /// </summary>
   134         /// <param name="messageId">The message id to store its password for.</param>
   135         /// <param name="password">The password to store.</param>
   136         /// <returns>The status of the method.</returns>
   137         public static Globals.ReturnStatus StoreFPPPassword(string messageId, string password)
   138         {
   139             if (string.IsNullOrEmpty(messageId) ||
   140                 string.IsNullOrEmpty(password))
   141             {
   142                 Log.Error("StoreFPPKey: messageId or password are null or empty.");
   143                 return Globals.ReturnStatus.Failure;
   144             }
   145 
   146             return PEPDatabase.InsertKeyValuePairIntoDb(DatabaseTable.Keys, messageId, password);
   147         }
   148 
   149         /// <summary>
   150         /// Gets the FPP password for a given message id. Can return null.
   151         /// </summary>
   152         /// <param name="messageId">The message id to get the password for.</param>
   153         /// <returns>The password or null if an error occured.</returns>
   154         public static string GetFPPPassword(string messageId)
   155         {
   156             string password = null;
   157 
   158             if (string.IsNullOrEmpty(messageId))
   159             {
   160                 Log.Error("GetFPPPassword: messageId is null or empty.");
   161             }
   162             else
   163             {
   164                 if (PEPDatabase.GetValueFromDb(DatabaseTable.Keys, messageId, out password) != Globals.ReturnStatus.Success)
   165                 {
   166                     password = null;
   167                     Log.Error("GetFPPPassword: Error getting password.");
   168                 }
   169             }
   170 
   171             return password;
   172         }
   173 
   174         /// <summary>
   175         /// Stores the mail item entry id for a given message id.
   176         /// </summary>
   177         /// <param name="messageId">The message id to store its entry id for.</param>
   178         /// <param name="entryId">The entry id to store.</param>
   179         /// <returns>The status of the method.</returns>
   180         public static Globals.ReturnStatus StoreFPPEntryId(string messageId, string entryId)
   181         {
   182             if (string.IsNullOrEmpty(messageId) ||
   183                 string.IsNullOrEmpty(entryId))
   184             {
   185                 Log.Error("StoreFPPEntryId: messageId or entryId are null or empty.");
   186                 return Globals.ReturnStatus.Failure;
   187             }
   188 
   189             return PEPDatabase.InsertKeyValuePairIntoDb(DatabaseTable.EntryIds, messageId, entryId);
   190         }
   191 
   192         /// <summary>
   193         /// Gets the message entry id for a given message id. Can return null.
   194         /// </summary>
   195         /// <param name="messageId">The message id to get the entry id for.</param>
   196         /// <returns>The entry id or null if an error occured.</returns>
   197         public static string GetFPPEntryId(string messageId)
   198         {
   199             string entryId = null;
   200 
   201             if (string.IsNullOrEmpty(messageId))
   202             {
   203                 Log.Error("GetFPPEntryId: EntryId is null or empty.");
   204             }
   205             else
   206             {
   207                 if (PEPDatabase.GetValueFromDb(DatabaseTable.EntryIds, messageId, out entryId) != Globals.ReturnStatus.Success)
   208                 {
   209                     entryId = null;
   210                     Log.Error("GetPassword: Error getting entry id.");
   211                 }
   212             }
   213 
   214             return entryId;
   215         }
   216 
   217         /// <summary>
   218         /// Opens a SQLiteConnection to the pEp database. This can return null.
   219         /// IMPORTANT: Caller has to make sure to dispose the connection after using it.
   220         /// </summary>
   221         /// <returns>An open SQLiteConnection or null if an error occured.</returns>
   222         private static SQLiteConnection OpenPEPDatabase()
   223         {
   224             SQLiteConnection dbConnection = null;
   225 
   226             try
   227             {
   228                 string dbPath = Path.Combine(Globals.PEPUserFolder, PEPDatabase.PEP_DB_NAME);
   229                 
   230                 // If database does not exist yet, create it
   231                 if (File.Exists(dbPath) == false)
   232                 {
   233                     // Create database
   234                     SQLiteConnection.CreateFile(dbPath);
   235 
   236                     // Set flag
   237                     PEPDatabase.dbInitialized = false;
   238                 }
   239 
   240                 // Open connection
   241                 string dbConnectionPath = "Data Source=" + dbPath;
   242                 dbConnection = new SQLiteConnection(dbConnectionPath);
   243                 dbConnection.Open();
   244 
   245                 // If not initialized yet, make sure all tables are created
   246                 if (PEPDatabase.dbInitialized == false)
   247                 {
   248                     // Create Messages table if not yet existing
   249                     using (SQLiteCommand createTableCmd = new SQLiteCommand(createPasswordsTable, dbConnection))
   250                     {
   251                         createTableCmd.ExecuteNonQuery();
   252                     }
   253 
   254                     // Create EntryIds table if not yet existing
   255                     using (SQLiteCommand createTableCmd = new SQLiteCommand(createEntryIdsTable, dbConnection))
   256                     {
   257                         createTableCmd.ExecuteNonQuery();
   258                     }
   259 
   260                     // Create Ratings table if not yet existing
   261                     using (SQLiteCommand createTableCmd = new SQLiteCommand(createRatingsTable, dbConnection))
   262                     {
   263                         createTableCmd.ExecuteNonQuery();
   264                     }
   265 
   266                     PEPDatabase.dbInitialized = true;
   267                 }
   268             }
   269             catch (Exception ex)
   270             {
   271                 // Dispose connection
   272                 dbConnection?.Dispose();
   273                 Log.Error("OpenFPPDatabase: Error connecting to database. " + ex.ToString());
   274 
   275                 // Set flag
   276                 PEPDatabase.dbInitialized = false;
   277             }
   278 
   279             return dbConnection;
   280         }
   281 
   282         /// <summary>
   283         /// Stores a message id and a corresponding value (key or entryId) in the specified database table.
   284         /// </summary>
   285         /// <param name="dbTable">The database table in which to insert the value.</param>
   286         /// <param name="key">The key to store.</param>
   287         /// <param name="value">The value to store.</param>
   288         /// <returns>Success if if successful, otherwise Failure</returns>
   289         private static Globals.ReturnStatus InsertKeyValuePairIntoDb(DatabaseTable dbTable, string key, string value)
   290         {
   291             string insertValues = null;
   292             Globals.ReturnStatus status = Globals.ReturnStatus.Failure;
   293 
   294             /* SQL statement to insert a keyd and a value into the respective table. Depending on the 
   295              * table, either ignore insertion or replace row
   296              */
   297             if (dbTable == DatabaseTable.EntryIds)
   298             {
   299 
   300                 insertValues = "insert or ignore into " + PEPDatabase.PEP_DB_FPP_ENTRY_IDS_TABLE_NAME + " ("
   301                                                         + PEPDatabase.PEP_DB_FPP_MESSAGE_ID_COLUMN_NAME + ", "
   302                                                         + PEPDatabase.PEP_DB_FPP_ENTRY_ID_COLUMN_NAME + ") values (@key, @value)";
   303 
   304                 Log.Verbose("InsertKeyValuePairIntoDb: Trying to insert value in EntryIds table...");
   305             }
   306             else if (dbTable == DatabaseTable.Keys)
   307             {
   308                 insertValues = "insert or ignore into " + PEPDatabase.PEP_DB_FPP_KEYS_TABLE_NAME + " ("
   309                                                         + PEPDatabase.PEP_DB_FPP_MESSAGE_ID_COLUMN_NAME + ", "
   310                                                         + PEPDatabase.PEP_DB_FPP_KEY_COLUMN_NAME + ") values (@key, @value)";
   311 
   312                 Log.Verbose("InsertKeyValuePairIntoDb: Trying to insert value in Messages table...");
   313             }
   314             else if (dbTable == DatabaseTable.Ratings)
   315             {
   316                 insertValues = "insert or replace into " + PEPDatabase.PEP_DB_RATINGS_TABLE_NAME + " ("
   317                                                          + PEPDatabase.PEP_DB_RATINGS_ENTRY_ID_COLUMN_NAME + ", "
   318                                                          + PEPDatabase.PEP_DB_RATINGS_RATING_COLUMN_NAME + ") values (@key, @value)";
   319 
   320                 Log.Verbose("InsertKeyValuePairIntoDb: Trying to insert value in Messages table...");
   321             }
   322 
   323             // Check first if key and value are not null
   324             if (string.IsNullOrEmpty(key) ||
   325                 string.IsNullOrEmpty(value))
   326             {
   327                 Log.Error("InsertKeyValuePairIntoDb: key or value null or empty.");
   328                 return status;
   329             }
   330 
   331             try
   332             {
   333                 // Lock database
   334                 lock (PEPDatabase.mutexDbAccess)
   335                 {
   336                     // Open a connection to the database
   337                     using (SQLiteConnection dbConnection = PEPDatabase.OpenPEPDatabase())
   338                     {
   339                         if (dbConnection != null)
   340                         {
   341                             // Insert values
   342                             int affectedRows = -1;
   343                             using (SQLiteCommand insertValuesCmd = new SQLiteCommand(insertValues, dbConnection))
   344                             {
   345                                 insertValuesCmd.Parameters.AddWithValue("key", key);
   346                                 insertValuesCmd.Parameters.AddWithValue("value", value);
   347                                 affectedRows = insertValuesCmd.ExecuteNonQuery();
   348                             }
   349 
   350                             // If one row was modified, the entry was inserted correctly
   351                             if (affectedRows == 1)
   352                             {
   353                                 Log.Verbose("InsertKeyAndValueInDb: Message ID and value stored/replaced successfully in database.");
   354                                 status = Globals.ReturnStatus.Success;
   355                             }
   356                             // If no row was modified, the values are already in the database
   357                             else if (affectedRows == 0)
   358                             {
   359                                 Log.Verbose("InsertKeyAndValueInDb: Message ID or value already in database.");
   360                                 status = Globals.ReturnStatus.Success;
   361                             }
   362                             // In any other case, some kind of error occured
   363                             else
   364                             {
   365                                 Log.Error("InsertKeyAndValueInDb: Error storing message ID or value. Affected rows: " + affectedRows);
   366                                 status = Globals.ReturnStatus.Failure;
   367                             }
   368                         }
   369                     }
   370                 }
   371             }
   372             catch (Exception ex)
   373             {
   374                 Log.Error("InsertKeyAndValueInDb: Error storing msg id and key. " + ex.ToString());
   375             }
   376 
   377             return status;
   378         }
   379 
   380         /// <summary>
   381         /// Gets a value from the specified database table.
   382         /// </summary>
   383         /// <param name="dbTable">The database table from which to get the value.</param>
   384         /// <param name="key">The key to get the value for.</param>
   385         /// <param name="value">The retrieved value or null if an error occured.</param>
   386         /// <returns>Success if value was retrieved successfully, otherwise Failure</returns>
   387         private static Globals.ReturnStatus GetValueFromDb(DatabaseTable dbTable, string key, out string value)
   388         {
   389             value = null;
   390             Globals.ReturnStatus status = Globals.ReturnStatus.Failure;
   391 
   392             try
   393             {
   394                 // Lock database
   395                 lock (PEPDatabase.mutexDbAccess)
   396                 {
   397                     // Open a connection to the database
   398                     using (SQLiteConnection dbConnection = PEPDatabase.OpenPEPDatabase())
   399                     {
   400                         if (dbConnection != null)
   401                         {
   402                             // Look up value that corresponds to message id
   403                             string getValue = null;
   404                             if (dbTable == DatabaseTable.EntryIds)
   405                             {
   406                                 getValue = "select " + PEPDatabase.PEP_DB_FPP_ENTRY_ID_COLUMN_NAME +
   407                                            " from " + PEPDatabase.PEP_DB_FPP_ENTRY_IDS_TABLE_NAME +
   408                                            " where " + PEPDatabase.PEP_DB_FPP_MESSAGE_ID_COLUMN_NAME + "=@key";
   409 
   410                                 Log.Verbose("GetValueFromDb: Trying to get EntryId from database...");
   411                             }
   412                             else if (dbTable == DatabaseTable.Keys)
   413                             {
   414                                 getValue = "select " + PEPDatabase.PEP_DB_FPP_KEY_COLUMN_NAME +
   415                                            " from " + PEPDatabase.PEP_DB_FPP_KEYS_TABLE_NAME +
   416                                            " where " + PEPDatabase.PEP_DB_FPP_MESSAGE_ID_COLUMN_NAME + "=@key";
   417 
   418                                 Log.Verbose("GetValueFromDb: Trying to get key from database...");
   419                             }
   420                             else if (dbTable == DatabaseTable.Ratings)
   421                             {
   422                                 getValue = "select " + PEPDatabase.PEP_DB_RATINGS_RATING_COLUMN_NAME +
   423                                            " from " + PEPDatabase.PEP_DB_RATINGS_TABLE_NAME +
   424                                            " where " + PEPDatabase.PEP_DB_RATINGS_ENTRY_ID_COLUMN_NAME + "=@key";
   425 
   426                                 Log.Verbose("GetValueFromDb: Trying to get key from database...");
   427                             }
   428 
   429                             using (SQLiteCommand getKeyCmd = new SQLiteCommand(getValue, dbConnection))
   430                             {
   431                                 getKeyCmd.Parameters.AddWithValue("key", key);
   432 
   433                                 using (SQLiteDataReader getKeyReader = getKeyCmd.ExecuteReader())
   434                                 {
   435                                     while (getKeyReader.Read())
   436                                     {
   437                                         // Set index according to database table
   438                                         string index = null;
   439                                         if (dbTable == DatabaseTable.EntryIds)
   440                                         {
   441                                             index = PEPDatabase.PEP_DB_FPP_ENTRY_ID_COLUMN_NAME;
   442                                         }
   443                                         else if (dbTable == DatabaseTable.Keys)
   444                                         {
   445                                             index = PEPDatabase.PEP_DB_FPP_KEY_COLUMN_NAME;
   446                                         }
   447                                         else if (dbTable == DatabaseTable.Ratings)
   448                                         {
   449                                             index = PEPDatabase.PEP_DB_RATINGS_RATING_COLUMN_NAME;
   450                                         }
   451 
   452                                         // Check also for duplicates
   453                                         if (value != null)
   454                                         {
   455                                             // If two different values are found for the same key, assert error and return
   456                                             if (string.Equals(value, getKeyReader[index] as string) == false)
   457                                             {
   458                                                 Log.Error("GetValueFromDb: Two different values found for same key.");
   459                                                 status = Globals.ReturnStatus.Failure;
   460                                                 value = null;
   461                                                 break;
   462                                             }
   463                                         }
   464                                         else
   465                                         {
   466                                             value = getKeyReader[index] as string;
   467                                             status = (value != null) ? Globals.ReturnStatus.Success : Globals.ReturnStatus.Failure;
   468                                         }
   469                                     }
   470                                 }
   471                             }
   472                         }
   473                     }
   474                 }
   475             }
   476             catch (Exception ex)
   477             {
   478                 Log.Error("GetValueFromDb: Error getting key. " + ex.ToString());
   479             }
   480 
   481             return status;
   482         }
   483     }
   484     #endregion
   485 }
   486