public void OpenConnection_WithSafeCloseCallWhenClosed_DatabaseIsClosed()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
Exception openException = null;
ConnectionState dbState = ConnectionState.Broken;
//Act
try
{
using (var db = new SqliteConnection(dbPath, true))
{
//It should be safe to call SafeClose() on a closed database
db.SafeClose();
dbState = db.State;
}
}
catch (Exception e)
{
openException = e;
}
//Assert
Assert.IsNull(openException);
Assert.AreEqual(ConnectionState.Closed, dbState);
}
public void TableSchema_GetTableColumnList_ReturnsCorrectColumnList()
{
//Arrange
IColumnInfo[] columnList = null;
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
//Act
using (var db = new SqliteConnection(dbPath, true))
{
using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db))
{
db.Open();
cmd.ExecuteNonQuery();
//With the table created, we should be able to get a list of columns
columnList = db.GetTableColumnList(tableName);
}
}
//Assert
Assert.IsNotNull(columnList);
Assert.AreEqual(3, columnList.Length); //Should be three columns
Assert.AreEqual("IdColumn", columnList[0].Name);
Assert.IsTrue(columnList[0].IsPrimaryKey);
Assert.AreEqual(DbType.Int64, columnList[0].DataType);
Assert.AreEqual("DateTimeColumn", columnList[1].Name);
Assert.IsFalse(columnList[1].IsPrimaryKey);
Assert.IsTrue(columnList[1].IsNotNull);
Assert.AreEqual(DbType.DateTime, columnList[1].DataType);
Assert.AreEqual("TextColumn", columnList[2].Name);
Assert.IsFalse(columnList[2].IsPrimaryKey);
Assert.AreEqual(DbType.String, columnList[2].DataType);
}
public void TableSchema_AddColumns_IsSuccessful()
{
//Arrange
IColumnInfo[] initialColumnList = null;
IColumnInfo[] finalColumnList = null;
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
//Act
using (var db = new SqliteConnection(dbPath, true))
{
using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db))
{
db.Open();
cmd.ExecuteNonQuery();
initialColumnList = db.GetTableColumnList(tableName);
cmd.CommandText = String.Format(addColumnSql1, tableName);
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format(addColumnSql2, tableName);
cmd.ExecuteNonQuery();
finalColumnList = db.GetTableColumnList(tableName);
}
}
//Assert
Assert.IsNotNull(initialColumnList);
Assert.AreEqual(3, initialColumnList.Length); //Should be three columns
Assert.IsNotNull(finalColumnList);
Assert.AreEqual(5, finalColumnList.Length); //Should be five columns, with the two that were added
Assert.AreEqual("EncryptedColumn", finalColumnList[3].Name);
Assert.AreEqual(DbType.Encrypted, finalColumnList[3].DataType);
Assert.AreEqual("DateTimeOffsetColumn", finalColumnList[4].Name);
Assert.AreEqual(DbType.DateTimeOffset, finalColumnList[4].DataType);
}
public void OpenConnection_WithFailIfMissingOnNewDatabase_CausesException()
{
//Arrange
var csb = new SqliteConnectionStringBuilder
{
DatabaseFilePath = TempDatabaseFiles.GetNewDatabasePath(this),
FailIfMissing = true
};
Exception openException = null;
//Act
try
{
using (var db = new SqliteConnection(csb.ConnectionString))
{
db.Open();
}
}
catch (Exception e)
{
openException = e;
}
//Assert
Assert.IsNotNull(openException as SqliteException);
Assert.IsFalse(File.Exists(csb.DatabaseFilePath));
}
public void OpenConnection_CheckingStateOnDisposedConnection_CausesException()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
Exception disposedException = null;
// ReSharper disable once NotAccessedVariable
ConnectionState dbStateBeforeOpen = ConnectionState.Broken;
//Act
var db = new SqliteConnection(dbPath, true);
db.Dispose();
try
{
// ReSharper disable once RedundantAssignment
dbStateBeforeOpen = db.State;
}
catch (Exception e)
{
disposedException = e;
}
//Assert
Assert.IsNotNull(disposedException as ObjectDisposedException);
}
public void TableSchema_CreateTableWithoutOpen_CausesException()
{
//Arrange
bool tableExists = false;
Exception openException = null;
//Act
try
{
using (var db = new SqliteConnection(TempDatabaseFiles.GetNewDatabasePath(this), true))
{
using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db))
{
//db.Open(); - testing executing query without opening first
cmd.ExecuteNonQuery();
//The following line should never be hit
tableExists = db.TableExists(tableName);
}
}
}
catch (Exception e)
{
openException = e;
}
//Assert
Assert.IsFalse(tableExists);
Assert.IsNotNull(openException as InvalidOperationException);
}
public void OpenConnection_OpenDefaultDatabaseWithMissingDefaultDatabase_CausesException()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
Exception secondConnectionException = null;
//Calling ShutdownSqlite3Provider() should not be necessary, but it is the only way
// to dispose of the default database; which is necessary here because we are running
// multiple tests that need to set the default database.
SqliteConnection.ShutdownSqlite3Provider();
//Act
using (var db = new SqliteConnection(dbPath, true, SqliteOpenFlags.ReadWrite, false)) //false = not default database
{
db.Open();
try
{
//This should fail, because the default database has not been set.
using (var db2 = new SqliteConnection()) { db2.Open(); }
}
catch (Exception e)
{
secondConnectionException = e;
}
}
//Assert
Assert.IsTrue(File.Exists(dbPath));
Assert.IsNotNull(secondConnectionException as InvalidOperationException);
}
public void TableSchema_CreateTableWithOpen_IsSuccessful()
{
//Arrange
bool tableExists;
bool fakeTableExists;
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
//Act
using (var db = new SqliteConnection(dbPath, true))
{
using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db))
{
db.Open();
cmd.ExecuteNonQuery();
tableExists = db.TableExists(tableName);
//Testing calling TableExists() on a table that doesn't exist
fakeTableExists = db.TableExists("fake.table.name_123456");
}
}
using (var db = new SqliteConnection(dbPath, true))
{
//Make sure table still exists
tableExists &= db.TableExists(tableName);
}
//Assert
Assert.IsTrue(tableExists);
Assert.IsFalse(fakeTableExists);
}
public void OpenConnection_WithOpenCallWhenOpen_CausesException()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
Exception openException = null;
//Act
try
{
using (var db = new SqliteConnection(dbPath, true))
{
db.Open();
//Since the database should already be open, this should throw an exception
db.Open();
}
}
catch (Exception e)
{
openException = e;
}
//Assert
Assert.IsNotNull(openException as InvalidOperationException);
}
public void OpenConnection_WithOpenCloseSafeOpenCalls_DatabaseIsOpen()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
Exception openException = null;
ConnectionState dbStateAfterOpen = ConnectionState.Broken;
ConnectionState dbStateAfterClose = ConnectionState.Broken;
ConnectionState dbStateAfterSafeOpen = ConnectionState.Broken;
//Act
try
{
using (var db = new SqliteConnection(dbPath, true))
{
db.Open();
dbStateAfterOpen = db.State;
db.Close();
dbStateAfterClose = db.State;
db.SafeOpen();
dbStateAfterSafeOpen = db.State;
}
}
catch (Exception e)
{
openException = e;
}
//Assert
Assert.IsNull(openException);
Assert.AreEqual(ConnectionState.Open, dbStateAfterOpen);
Assert.AreEqual(ConnectionState.Closed, dbStateAfterClose);
Assert.AreEqual(ConnectionState.Open, dbStateAfterSafeOpen);
}
public void OpenConnection_MultipleConnectionsWithTheDefaultDatabase_IsSuccessful()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
Exception secondConnectionException = null;
Exception fourthConnectionException = null;
Exception thirdConnectionException = null;
//Calling ShutdownSqlite3Provider() should not be necessary, but it is the only way
// to dispose of the default database; which is necessary here because we are running
// multiple tests that need to set the default database.
SqliteConnection.ShutdownSqlite3Provider();
//Act
using (var db = new SqliteConnection(dbPath, true, SqliteOpenFlags.ReadWrite, true)) //true (as last parameter) = make this default database
{
db.Open();
try
{
//Using the default constructor for SqliteConnection causes it to use the default database, if that has been set
// - this should be successful - i.e. not throw an exception
using (var db2 = new SqliteConnection()) { db2.Open(); }
}
catch (Exception e)
{
secondConnectionException = e;
}
try
{
//Opening a new database pointing to the same file, should still fail
using (var db3 = new SqliteConnection(dbPath, true)) { db3.Open(); }
}
catch (Exception e)
{
thirdConnectionException = e;
}
}
try
{
//Using the default constructor for SqliteConnection causes it to use the default database, if that has been set
// - this should be successful even though the original connection was disposed, because default databases do not get disposed
using (var db4 = new SqliteConnection()) { db4.Open(); }
}
catch (Exception e)
{
fourthConnectionException = e;
}
//Assert
Assert.IsTrue(File.Exists(dbPath));
Assert.IsNull(secondConnectionException);
Assert.IsNotNull(thirdConnectionException as InvalidOperationException);
Assert.IsNull(fourthConnectionException);
}
public void SchemaVersion_CheckAndSetVersion_ManagesConnectionStateProperly()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
long schemaVersionBeforeSet;
long intendedSchemaVersion = 23;
long schemaVersionAfterSet;
ConnectionState beforeGetClosed;
ConnectionState afterGetClosed;
ConnectionState beforeGetOpen;
ConnectionState afterGetOpen;
ConnectionState beforeSetClosed;
ConnectionState afterSetClosed;
ConnectionState beforeSetOpen;
ConnectionState afterSetOpen;
//Act
using (var db = new SqliteConnection(dbPath, true))
{
schemaVersionBeforeSet = db.GetDatabaseSchemaVersion();
db.SafeClose(); //Want the database closed so we can make sure that setting
// the schema doesn't require the connection to be open.
beforeSetClosed = db.State;
db.SetDatabaseSchemaVersion(intendedSchemaVersion);
afterSetClosed = db.State;
db.SafeOpen(); //Should now be open
beforeSetOpen = db.State;
db.SetDatabaseSchemaVersion(intendedSchemaVersion);
afterSetOpen = db.State;
}
using (var db = new SqliteConnection(dbPath, true))
{
beforeGetClosed = db.State;
schemaVersionAfterSet = db.GetDatabaseSchemaVersion();
afterGetClosed = db.State;
db.SafeOpen();
beforeGetOpen = db.State;
schemaVersionAfterSet = db.GetDatabaseSchemaVersion();
afterGetOpen = db.State;
}
//Assert
Assert.AreEqual(0, schemaVersionBeforeSet);
Assert.AreEqual(intendedSchemaVersion, schemaVersionAfterSet);
Assert.AreEqual(ConnectionState.Closed, beforeSetClosed); //Should be closed because just closed
Assert.AreEqual(ConnectionState.Closed, afterSetClosed); //Should be closed if previously closed
Assert.AreEqual(ConnectionState.Open, beforeSetOpen); //Should be open because just opened
Assert.AreEqual(ConnectionState.Open, afterSetOpen); //Should be open if previously open
Assert.AreEqual(ConnectionState.Closed, beforeGetClosed); //Should be closed before activity, or explicit Open()
Assert.AreEqual(ConnectionState.Closed, afterGetClosed); //Should be closed if previously closed
Assert.AreEqual(ConnectionState.Open, beforeGetOpen); //Should be open because just opened
Assert.AreEqual(ConnectionState.Open, afterGetOpen); //Should be open if previously open
}
public void TableSchema_AddColumnsInCorrectMode_IsSuccessful()
{
//Arrange
IColumnInfo[] columnList = null;
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
bool beginMaintenanceModeSuccess;
bool endMaintenanceModeSuccess;
Exception openException = null;
//Act
using (var db = new SqliteConnection(dbPath, true))
{
using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db))
{
db.Open();
cmd.ExecuteNonQuery();
}
//Adding the first column using Maintenance Mode
using (var cmd = new SqliteCommand(String.Format(addColumnSql1, tableName), db, true)) //true = maintenance mode
{
beginMaintenanceModeSuccess = db.BeginDatabaseMaintenanceMode();
try
{
//Opening the database should fail, because BeginDatabaseMaintenanceMode() already opens it
db.Open();
}
catch (Exception e)
{
openException = e;
}
db.SafeOpen(); //Safe open should be fine - because it is "safe"
cmd.ExecuteNonQuery();
//GetTableColumnList() should work correctly whether the database is in Maintenance Mode or not
columnList = db.GetTableColumnList(tableName);
endMaintenanceModeSuccess = db.EndDatabaseMaintenanceMode();
}
//Adding the second column NOT using Maintenance Mode
using (var cmd = new SqliteCommand(String.Format(addColumnSql2, tableName), db, false)) //false = not maintenance mode
{
db.Open();
cmd.ExecuteNonQuery();
//GetTableColumnList() should work correctly whether the database is in Maintenance Mode or not
columnList = db.GetTableColumnList(tableName);
}
}
//Assert
Assert.IsNotNull(columnList);
Assert.AreEqual(5, columnList.Length); //Should be five columns, with the two that were added
Assert.IsTrue(beginMaintenanceModeSuccess);
Assert.IsTrue(endMaintenanceModeSuccess);
Assert.IsNotNull(openException as InvalidOperationException);
}
public void OpenConnection_MultipleConnectionsWithTheSameDatabaseFilePath_CausesException()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
Exception secondConnectionException = null;
Exception thirdConnectionException = null;
Exception fourthConnectionException = null;
//Calling ShutdownSqlite3Provider() should not be necessary, but it is the only way
// to dispose of the default database; which is necessary here because we are running
// multiple tests that need to set the default database.
SqliteConnection.ShutdownSqlite3Provider();
//Act
using (var db = new SqliteConnection(dbPath, true, SqliteOpenFlags.ReadWrite, false)) //false = not default database
{
db.Open();
try
{
//This should fail, because there cannot be multiple simultaneous connections to the same database file
using (var db2 = new SqliteConnection(dbPath, true)) { db2.Open(); }
}
catch (Exception e)
{
secondConnectionException = e;
}
try
{
//This should fail, because there cannot be multiple simultaneous connections to the same database file
using (var db3 = new SqliteConnection(dbPath, true)) { db3.Open(); }
}
catch (Exception e)
{
thirdConnectionException = e;
}
}
try
{
//This should be fine, because the other connection has been disposed.
using (var db4 = new SqliteConnection(dbPath, true)) { db4.Open(); }
}
catch (Exception e)
{
fourthConnectionException = e;
}
//Assert
Assert.IsTrue(File.Exists(dbPath));
Assert.IsNotNull(secondConnectionException as InvalidOperationException);
Assert.IsNotNull(thirdConnectionException as InvalidOperationException);
Assert.IsNull(fourthConnectionException);
}
public void OpenConnection_WithSafeOpenMethodCall_DatabaseIsOpen()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
//Act
using (var db = new SqliteConnection(dbPath, true))
{
db.SafeOpen();
//Assert
Assert.AreEqual(ConnectionState.Open, db.State);
}
}
public void OpenConnection_WithFilePathOnly_CreatesFile()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
//Act
using (var db = new SqliteConnection(dbPath, true))
{
db.Open();
}
//Assert
Assert.IsTrue(File.Exists(dbPath));
}
public void WriteAndReadData_EncryptedColumn_IsSuccessful()
{
//Arrange
var cryptEngine = new SampleAesCryptEngine("myTestPassword");
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
int dataItemIndex = 0;
using (var db = new SqliteConnection(dbPath, cryptEngine, true))
{
//Creating my table & adding encrypted column
using (var cmd = new SqliteCommand(String.Format(tableCreateSql, tableName), db))
{
db.SafeOpen();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format(addColumnSql, tableName);
cmd.ExecuteNonQuery();
}
//Act
using (var cmd = new SqliteCommand(String.Format(insertSql2, tableName), db))
{
//Adding my records
foreach (Tuple <DateTime, string> dataItem in _sampleData)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@date", dataItem.Item1);
cmd.Parameters.Add("@text", dataItem.Item2);
cmd.AddEncryptedParameter(new SqliteParameter("@encrypted", dataItem.Item2)); //Adds the string value (Tuple Item2) but with encryption
db.SafeOpen();
cmd.ExecuteNonQuery();
}
}
//Assert
using (var cmd = new SqliteCommand(String.Format(selectSql1, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db))
using (var reader = new SqliteDataReader(cmd))
{
while (reader.Read())
{
Assert.AreEqual(_sampleData[dataItemIndex].Item2, reader.GetString("TextColumn"));
Assert.AreNotEqual(_sampleData[dataItemIndex].Item2, reader.GetString("EncryptedColumn")); //The encrypted text should not match the unencrypted text
Assert.AreEqual(_sampleData[dataItemIndex].Item2, reader.GetDecrypted <string>("EncryptedColumn")); //The decrypted text should match the unencrypted text
dataItemIndex++;
}
Assert.AreEqual(3, dataItemIndex); //Should have read three records
}
}
}
public void SchemaVersion_CheckVersionOnNewDatabase_ReturnsZero()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
long schemaVersion;
//Act
using (var db = new SqliteConnection(dbPath, true))
{
schemaVersion = db.GetDatabaseSchemaVersion();
}
//Assert
Assert.AreEqual(0, schemaVersion);
}
public void OpenConnection_WithMissingOpenMethodCall_DatabaseIsClosed()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
//Act
using (var db = new SqliteConnection(dbPath, true))
{
//Not calling Open() for this test
//db.Open();
//Assert
Assert.AreEqual(ConnectionState.Closed, db.State);
}
}
public void OpenConnection_WithConnectionString_CreatesFile()
{
//Arrange
var csb = new SqliteConnectionStringBuilder
{
DatabaseFilePath = TempDatabaseFiles.GetNewDatabasePath(this),
BusyTimeout = 100,
JournalMode = SqliteJournalModeEnum.Wal
};
//Act
using (var db = new SqliteConnection(csb.ConnectionString))
{
db.Open();
}
//Assert
Assert.IsTrue(File.Exists(csb.DatabaseFilePath));
}
public void InsertSelect_DateTimeColumnStoredUsingStrings_MatchesInsertedValue()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
DateTime dateTimeColumnInsertValue = DateTime.Now;
string textColumnInsertValue = "Hello SQLite!";
DateTime dateTimeColumnSelectValue;
string textColumnSelectValue;
var csb = new SqliteConnectionStringBuilder
{
DatabaseFilePath = dbPath,
JournalMode = SqliteJournalModeEnum.Default,
StoreDateTimeAsTicks = false
};
//Act
using (var db = new SqliteConnection(csb.ConnectionString))
{
using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db))
{
db.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format(insertSql, tableName, "DateTimeColumn");
cmd.Parameters.Add(new SqliteParameter("@date", dateTimeColumnInsertValue));
cmd.Parameters.Add(new SqliteParameter("text", textColumnInsertValue)); //prefixing the parameter name with @ is not required
cmd.ExecuteNonQuery();
cmd.Parameters.Clear(); //This isn't really necessary, because the parameters added above are ignored/not present in the following CommandText values
cmd.CommandText = String.Format(selectSql1, "DateTimeColumn", tableName, "DateTimeColumn");
dateTimeColumnSelectValue = (DateTime)cmd.ExecuteScalar();
cmd.CommandText = String.Format(selectSql1, "TextColumn", tableName, "DateTimeColumn");
textColumnSelectValue = (string)cmd.ExecuteScalar();
}
}
//Assert
Assert.IsFalse(csb.StoreDateTimeAsTicks);
Assert.AreEqual(dateTimeColumnInsertValue, dateTimeColumnSelectValue);
Assert.AreEqual(textColumnInsertValue, textColumnSelectValue);
}
public void OpenConnection_CallingSafeCloseOnDisposedConnection_CausesNoException()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
Exception disposedException = null;
//Act
var db = new SqliteConnection(dbPath, true);
db.Dispose();
try
{
db.SafeClose();
}
catch (Exception e)
{
disposedException = e;
}
//Assert
Assert.IsNull(disposedException);
}
public void SchemaVersion_SetVersionOnNewDatabase_IsSuccessful()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
long schemaVersionBeforeSet;
long intendedSchemaVersion = 23;
long schemaVersionAfterSet;
//Act
using (var db = new SqliteConnection(dbPath, true))
{
schemaVersionBeforeSet = db.GetDatabaseSchemaVersion();
db.SetDatabaseSchemaVersion(intendedSchemaVersion);
}
using (var db = new SqliteConnection(dbPath, true))
{
schemaVersionAfterSet = db.GetDatabaseSchemaVersion();
}
//Assert
Assert.AreEqual(0, schemaVersionBeforeSet);
Assert.AreEqual(intendedSchemaVersion, schemaVersionAfterSet);
}
public void OpenConnection_MultipleDefaultDatabases_CausesException()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
string dbPath2 = TempDatabaseFiles.GetNewDatabasePath(this);
Exception secondConnectionException = null;
//Calling ShutdownSqlite3Provider() should not be necessary, but it is the only way
// to dispose of the default database; which is necessary here because we are running
// multiple tests that need to set the default database.
SqliteConnection.ShutdownSqlite3Provider();
//Act
using (var db = new SqliteConnection(dbPath, true, SqliteOpenFlags.ReadWrite, true)) //true (as last parameter) = make this default database
{
db.Open();
}
try
{
//This should fail, because it is not possible to set two databases to be the default
using (var db2 = new SqliteConnection(dbPath2, true, SqliteOpenFlags.ReadWrite, true)) //true (as last parameter) = make this default database
{
db2.Open();
}
}
catch (Exception e)
{
secondConnectionException = e;
}
//Assert
Assert.IsTrue(File.Exists(dbPath));
Assert.IsFalse(File.Exists(dbPath2));
Assert.IsNotNull(secondConnectionException as InvalidOperationException);
}
public void TableSchema_CreateTableThenDrop_IsSuccessful()
{
//Arrange
bool tableExistsBeforeDrop;
bool tableExistsAfterDrop;
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
//Act
using (var db = new SqliteConnection(dbPath, true))
{
using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db))
{
db.Open();
cmd.ExecuteNonQuery();
}
}
using (var db = new SqliteConnection(dbPath, true))
{
using (var cmd = new SqliteCommand(String.Format(tableDropSql, tableName), db))
{
db.Open();
tableExistsBeforeDrop = db.TableExists(tableName);
cmd.ExecuteNonQuery();
}
}
using (var db = new SqliteConnection(dbPath, true))
{
tableExistsAfterDrop = db.TableExists(tableName);
}
//Assert
Assert.IsTrue(tableExistsBeforeDrop);
Assert.IsFalse(tableExistsAfterDrop);
}
public void Cleanup() => TempDatabaseFiles.CleanUp(this.GetType());
public void InsertSelect_DateTimeOffsetColumn_MatchesInsertedValue()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
DateTimeOffset dateTimeColumnInsertValue1 = DateTimeOffset.Now;
DateTimeOffset dateTimeColumnSelectValue1;
DateTimeOffset dateTimeColumnInsertValue2 = DateTimeOffset.UtcNow.AddHours(2);
DateTimeOffset dateTimeColumnSelectValue2;
string textColumnInsertValue1 = "Hello SQLite!";
string textColumnSelectValue1;
string textColumnInsertValue2 = "Goodbye SQLite!";
string textColumnSelectValue2;
long recordId1;
long recordId2;
Exception duplicateParameterException = null;
//Act
using (var db = new SqliteConnection(dbPath, true))
{
using (var cmd = new SqliteCommand(String.Format(tableCreateSql2, tableName), db))
{
db.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format(insertSql, tableName, "DateTimeOffsetColumn");
cmd.Parameters.Add(new SqliteParameter("@date", dateTimeColumnInsertValue1));
cmd.Parameters.Add(new SqliteParameter("text", textColumnInsertValue1)); //prefixing the parameter name with @ is not required
recordId1 = cmd.ExecuteReturnRowId();
try
{
//This should fail, because adding multiple parameters with the same name is not allowed; we need to clear parameters first.
cmd.Parameters.Add(new SqliteParameter("@date", dateTimeColumnInsertValue2));
cmd.Parameters.Add(new SqliteParameter("text", textColumnInsertValue2)); //prefixing the parameter name with @ is not required
recordId2 = cmd.ExecuteReturnRowId();
}
catch (Exception e)
{
duplicateParameterException = e;
}
cmd.Parameters.Clear(); //allows parameters to be re-added
cmd.Parameters.Add(new SqliteParameter("@date", dateTimeColumnInsertValue2));
cmd.Parameters.Add(new SqliteParameter("@text", textColumnInsertValue2));
recordId2 = cmd.ExecuteReturnRowId();
cmd.Parameters.Clear(); //This isn't really necessary, because the parameters added above are ignored/not present in the following CommandText values
cmd.CommandText = String.Format(selectSql2, "DateTimeOffsetColumn", tableName, "IdColumn", recordId1);
dateTimeColumnSelectValue1 = (DateTimeOffset)cmd.ExecuteScalar();
cmd.CommandText = String.Format(selectSql2, "TextColumn", tableName, "IdColumn", recordId1);
textColumnSelectValue1 = (string)cmd.ExecuteScalar();
cmd.CommandText = String.Format(selectSql2, "DateTimeOffsetColumn", tableName, "IdColumn", recordId2);
dateTimeColumnSelectValue2 = (DateTimeOffset)cmd.ExecuteScalar();
cmd.CommandText = String.Format(selectSql2, "TextColumn", tableName, "IdColumn", recordId2);
textColumnSelectValue2 = (string)cmd.ExecuteScalar();
}
}
//Assert
Assert.IsNotNull(duplicateParameterException as InvalidOperationException);
Assert.AreEqual(1, recordId1); //First inserted record should have IdColumn of 1
Assert.AreEqual(2, recordId2); //Second inserted record should have IdColumn of 2
Assert.AreEqual(dateTimeColumnInsertValue1, dateTimeColumnSelectValue1);
Assert.AreEqual(textColumnInsertValue1, textColumnSelectValue1);
Assert.AreEqual(dateTimeColumnInsertValue2, dateTimeColumnSelectValue2);
Assert.AreEqual(textColumnInsertValue2, textColumnSelectValue2);
}
public void WriteAndReadData_BasicColumns_IsSuccessful()
{
//Arrange
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
int dataItemIndex = 0;
using (var db = new SqliteConnection(dbPath, true))
{
//Creating my table
using (var cmd = new SqliteCommand(String.Format(tableCreateSql, tableName), db))
{
db.SafeOpen();
cmd.ExecuteNonQuery();
}
//Act
using (var cmd = new SqliteCommand(String.Format(insertSql1, tableName), db))
{
//Adding my records
foreach (Tuple <DateTime, string> dataItem in _sampleData)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@date", dataItem.Item1);
cmd.Parameters.Add("@text", dataItem.Item2);
db.SafeOpen();
cmd.ExecuteNonQuery();
}
}
db.SafeClose(); //Closing the database to confirm that readers automatically open the underlying database connection, when necessary
//Assert
using (var cmd = new SqliteCommand(String.Format(selectSql1, "[IdColumn], [DateTimeColumn], [TextColumn]", tableName, "[IdColumn]"), db))
using (var reader = new SqliteDataReader(cmd))
{
while (reader.Read())
{
Assert.AreEqual((dataItemIndex + 1), reader.GetInt64("IdColumn"));
Assert.AreEqual(_sampleData[dataItemIndex].Item1, reader.GetDateTime("DateTimeColumn"));
Assert.AreEqual(_sampleData[dataItemIndex].Item2, reader.GetString("TextColumn"));
dataItemIndex++;
}
Assert.AreEqual(3, dataItemIndex); //Should have read three records
}
Assert.AreEqual(ConnectionState.Open, db.State); //It is expected that a database connection will be open after being used with a DataReader
db.SafeOpen(); //Setting the database to open to confirm that readers do not have problems with an already-open database
dataItemIndex = 0;
using (var cmd = new SqliteCommand(String.Format(selectSql2, "[DateTimeColumn], [TextColumn]", tableName, "[DateTimeColumn] ASC"), db))
using (var reader = new SqliteDataReader(cmd))
{
while (reader.Read())
{
Assert.AreEqual(DateTime.MinValue, reader.GetDateTime("DateTimeColumn")); //Should have found the MinValue based on the ORDER BY clause
dataItemIndex++;
}
Assert.AreEqual(1, dataItemIndex); //Should have only read one record based on the LIMIT clause
}
dataItemIndex = 0;
using (var cmd = new SqliteCommand(String.Format(selectSql2, "[DateTimeColumn], [TextColumn]", tableName, "[DateTimeColumn] DESC"), db))
using (var reader = new SqliteDataReader(cmd))
{
while (reader.Read())
{
Assert.AreEqual(DateTime.MaxValue, reader.GetDateTime("DateTimeColumn")); //Should have found the MaxValue based on the ORDER BY clause
dataItemIndex++;
}
Assert.AreEqual(1, dataItemIndex); //Should have only read one record based on the LIMIT clause
}
}
}
public void WriteAndReadData_EncryptedColumnWithWrongPassword_CausesException()
{
//Arrange
string rightPassword = "******";
string wrongPassword = "******";
var cryptEngine = new SampleAesCryptEngine(rightPassword);
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
Exception decryptException = null;
using (var db = new SqliteConnection(dbPath, cryptEngine, true))
{
//Creating my table & adding encrypted column
using (var cmd = new SqliteCommand(String.Format(tableCreateSql, tableName), db))
{
db.SafeOpen();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format(addColumnSql, tableName);
cmd.ExecuteNonQuery();
}
//Act
using (var cmd = new SqliteCommand(String.Format(insertSql2, tableName), db))
{
//Adding my records
foreach (Tuple <DateTime, string> dataItem in _sampleData)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@date", dataItem.Item1);
cmd.Parameters.Add("@text", dataItem.Item2);
cmd.AddEncryptedParameter(new SqliteParameter("@encrypted", dataItem.Item2)); //Adds the string value (Tuple Item2) but with encryption
db.SafeOpen();
cmd.ExecuteNonQuery();
}
}
}
//Assert
cryptEngine = new SampleAesCryptEngine(wrongPassword); //Testing first with the wrong password
using (var db = new SqliteConnection(dbPath, cryptEngine, true))
using (var cmd = new SqliteCommand(String.Format(selectSql2, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db))
using (var reader = new SqliteDataReader(cmd))
{
while (reader.Read())
{
try
{
//This should fail, because the value that is read from the column cannot be properly decrypted without the correct password
Assert.AreEqual(_sampleData[0].Item2, reader.GetDecrypted <string>("EncryptedColumn"));
}
catch (Exception e)
{
decryptException = e;
}
}
Assert.IsNotNull(decryptException as CryptographicException);
}
//Just checking to make sure we can still decrypt the value with the correct password
cryptEngine = new SampleAesCryptEngine(rightPassword); //Should work correctly with the right password
using (var db = new SqliteConnection(dbPath, cryptEngine, true))
using (var cmd = new SqliteCommand(String.Format(selectSql2, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db))
using (var reader = new SqliteDataReader(cmd))
{
while (reader.Read())
{
Assert.AreEqual(_sampleData[0].Item2, reader.GetDecrypted <string>("EncryptedColumn")); //The decrypted text should match the unencrypted text
}
}
}
public void TableSchema_AddColumnsInWrongMode_CausesException()
{
//Arrange
IColumnInfo[] columnList = null;
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
bool beginMaintenanceModeSuccess;
bool endMaintenanceModeSuccess;
Exception maintenanceModeException = null;
Exception notMaintenanceModeException = null;
//Act
using (var db = new SqliteConnection(dbPath, true))
{
using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db))
{
db.Open();
cmd.ExecuteNonQuery();
}
//Adding the first column using Maintenance Mode
using (var cmd = new SqliteCommand(String.Format(addColumnSql1, tableName), db, true)) //true = maintenance mode
{
db.SafeOpen();
try
{
//Oops. This should fail, because I am executing the command before I set maintenance mode (below)
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
maintenanceModeException = e;
}
beginMaintenanceModeSuccess = db.BeginDatabaseMaintenanceMode();
//GetTableColumnList() should work correctly whether the database is in Maintenance Mode or not
columnList = db.GetTableColumnList(tableName);
}
//Adding the second column NOT using Maintenance Mode
using (var cmd = new SqliteCommand(String.Format(addColumnSql2, tableName), db, false)) //false = not maintenance mode
{
db.SafeOpen();
try
{
//Oops. This should fail, because I am executing the command before I turned off maintenance mode (below)
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
notMaintenanceModeException = e;
}
endMaintenanceModeSuccess = db.EndDatabaseMaintenanceMode();
//GetTableColumnList() should work correctly whether the database is in Maintenance Mode or not
columnList = db.GetTableColumnList(tableName);
}
}
//Assert
Assert.IsNotNull(columnList);
Assert.AreEqual(3, columnList.Length); //Should be three columns, because the add column sql should have failed
Assert.IsTrue(beginMaintenanceModeSuccess);
Assert.IsTrue(endMaintenanceModeSuccess);
Assert.IsNotNull(maintenanceModeException as InvalidOperationException);
Assert.IsNotNull(notMaintenanceModeException as InvalidOperationException);
}
