public void ExecuteScalar_Generic_String(string query, string expectedValue)
{
using (var ds = new SQLiteDatastore())
{
ds.ExecuteScalar <string>(query).Should().Be(expectedValue);
}
}
public void Execute_with_missing_param(string paramName)
{
using var db = new SQLiteDatastore();
db.Invoking(x => x.Execute($"Select {paramName};", "'hello world'"))
.Should().Throw <SQLException>()
.And.CommandText.Should().NotBeNullOrEmpty();
}
public void FluentInterfaceTest_With_Many()
{
int recordsToCreate = 1000;
using (var ds = new SQLiteDatastore())
{
ds.Execute(TestDBBuilder.CREATE_MULTIPROPTABLE);
ds.BeginTransaction();
for (int i = 1; i <= recordsToCreate; i++)
{
ds.Execute(string.Format(" INSERT INTO MultiPropTable (IntField, NIntField) VALUES ({0}, {0});\r\n", i));
}
ds.CommitTransaction();
Assert.Equal(recordsToCreate, ds.GetRowCount("MultiPropTable", null));
StartTimer();
var result = ds.From <POCOMultiTypeObject>().Limit(5000, 0).Query();
EndTimer();
result.Should().NotBeEmpty();
result.Should().HaveCount(recordsToCreate);
foreach (DOMultiPropType item in
ds.From <DOMultiPropType>().Read())
{
item.FloatField = 1.0F;
ds.Update(item);
}
}
}
public void OpenConnection()
{
var path = GetTempFilePath(".cruise");
RegesterFileForCleanUp(path);
using (var db = new SQLiteDatastore(path))
{
var conn = db.OpenConnection();
db.PersistentConnection.Should().BeSameAs(conn);
conn.State.Should().Be(ConnectionState.Open);
db.ReleaseConnection();
db.PersistentConnection.Should().BeNull();
try
{
conn.State.Should().Be(ConnectionState.Closed);
}
catch (ObjectDisposedException)
{
#if !SYSTEM_DATA_SQLITE
throw;
#endif
}
db.ConnectionDepth.Should().Be(0);
}
}
public void BackupDatabase_overwrite_openfile_with_inmemory()
{
var tempPath = GetTempFilePath(".crz3");
RegesterFileForCleanUp(tempPath);
using (var ds = new SQLiteDatastore(tempPath))
{
var dbbuilder = new TestDBBuilder();
ds.CreateDatastore(dbbuilder);
var orgTableInfo = ds.QueryGeneric("SELECT * FROM Sqlite_Master;").ToArray();
orgTableInfo.Should().NotBeEmpty();
ds.From <POCOMultiTypeObject>().Query().Should().BeEmpty();
using (var newds = new SQLiteDatastore())
{
newds.CreateDatastore(dbbuilder);
newds.Insert(new POCOMultiTypeObject()
{
ID = 1,
});
newds.From <POCOMultiTypeObject>().Query().Should().NotBeEmpty();
newds.BackupDatabase(tempPath);
}
ds.From <POCOMultiTypeObject>().Query().Should().NotBeEmpty();
}
}
public void BackupDatabase_into_existing()
{
using (var ds = new SQLiteDatastore())
{
var dbbuilder = new TestDBBuilder();
ds.CreateDatastore(dbbuilder);
var orgTableInfo = ds.QueryGeneric("SELECT * FROM Sqlite_Master;").ToArray();
orgTableInfo.Should().NotBeEmpty();
var backupTarget = base.GetTempFilePath(".db");
RegesterFileForCleanUp(backupTarget);
// create database file
using (var targetds = new SQLiteDatastore(backupTarget))
{
targetds.CreateDatastore(dbbuilder);
targetds.Execute("CREATE TABLE Something (" +
"col1 text" +
");");
targetds.CheckTableExists("Something").Should().BeTrue();
targetds.Execute("ALTER Table MultiPropTable ADD COLUMN justanothercolumn text;");
targetds.CheckFieldExists("MultiPropTable", "justanothercolumn").Should().BeTrue();
ds.BackupDatabase(targetds);
targetds.CheckTableExists("something").Should().BeFalse();
targetds.CheckFieldExists("MultiPropTable", "justanothercolumn").Should().BeFalse();
}
}
}
public void BackupDatabase_inmemory()
{
using (var ds = new SQLiteDatastore())
{
var dbbuilder = new TestDBBuilder();
ds.CreateDatastore(dbbuilder);
var orgTableInfo = ds.QueryGeneric("SELECT * FROM Sqlite_Master;").ToArray();
orgTableInfo.Should().NotBeEmpty();
var backupTarget = base.GetTempFilePath(".db");
RegesterFileForCleanUp(backupTarget);
File.Exists(backupTarget).Should().BeFalse();
ds.BackupDatabase(backupTarget);
File.Exists(backupTarget).Should().BeTrue();
using (var newds = new SQLiteDatastore(backupTarget))
{
var newTableInfo = ds.QueryGeneric("SELECT * FROM Sqlite_Master;");
newTableInfo.Should().BeEquivalentTo(orgTableInfo);
}
}
}
public void BackupDatabase_overwrite_existing()
{
using (var ds = new SQLiteDatastore())
{
var dbbuilder = new TestDBBuilder();
ds.CreateDatastore(dbbuilder);
var orgTableInfo = ds.QueryGeneric("SELECT * FROM Sqlite_Master;").ToArray();
orgTableInfo.Should().NotBeEmpty();
var backupTarget = base.GetTempFilePath(".db");
RegesterFileForCleanUp(backupTarget);
// create a file to overwrite
// this doesn't need to be an actual db file
File.WriteAllText(backupTarget, "something");
File.Exists(backupTarget).Should().BeTrue();
// backup the database to the location of the file we just created
ds.BackupDatabase(backupTarget);
File.Exists(backupTarget).Should().BeTrue();
// and conferm that it did overwrite the old file
using (var newds = new SQLiteDatastore(backupTarget))
{
var newTableInfo = ds.QueryGeneric("SELECT * FROM Sqlite_Master;");
newTableInfo.Should().BeEquivalentTo(orgTableInfo);
}
}
}
public void NestedTransactionTest_FullCommit()
{
using (var ds = new SQLiteDatastore())
{
ds.CurrentTransaction.Should().BeNull();
ds.TransactionDepth.Should().Be(0);
ds.Execute("CREATE TABLE TableA (Data TEXT);");
ds.BeginTransaction();
ds.CurrentTransaction.Should().NotBeNull();
ds.TransactionDepth.Should().Be(1);
ds.BeginTransaction();
ds.CurrentTransaction.Should().NotBeNull();
ds.TransactionDepth.Should().Be(2);
ds.Execute("INSERT INTO TableA VALUES ('something');");
ds.CommitTransaction();
ds.GetRowCount("TableA", null).Should().Be(1);
ds.CurrentTransaction.Should().NotBeNull();
ds.TransactionDepth.Should().Be(1);
ds.CommitTransaction();
ds.GetRowCount("TableA", null).Should().Be(1);
ds.CurrentTransaction.Should().BeNull();
ds.TransactionDepth.Should().Be(0);
}
}
public void Ctor_inMemory()
{
using (var db = new SQLiteDatastore())
{
ValidateDatastore(db);
}
}
public void ReadSingleRow_stress()
{
var num = 1000;
var path = GetTempFilePath(".cruise");
RegesterFileForCleanUp(path);
using (var db = new SQLiteDatastore(path))
{
db.Execute(TestDBBuilder.CREATE_MULTIPROPTABLE);
db.Execute(
"WITH RECURSIVE generate_series(value) AS ( " +
" SELECT 1 " +
" UNION ALL " +
" SELECT value +1 FROM generate_series " +
$" WHERE value +1 <={num} " +
") " +
"INSERT INTO MultiPropTable (ID) SELECT * FROM generate_series;");
for (var i = 1; i <= num; i++)
{
var row = db.ReadSingleRow <POCOMultiTypeObject>(i);
row.Should().NotBeNull();
}
db.ConnectionDepth.Should().Be(0);
db.PersistentConnection.Should().BeNull();
}
}
public void Query_With_Invalid_SQL()
{
using (var ds = new SQLiteDatastore())
{
ds.Invoking(x => x.Query <POCOMultiTypeObject>("Invalid SQL;").ToArray())
.Should().Throw <SQLException>();
}
}
protected void VerifySQLiteDatastore(SQLiteDatastore ds)
{
ds.Should().NotBeNull();
ds.Exists.Should().BeTrue();
ds.Invoking(x => x.Execute("EXPLAIN SELECT 1;")).Should().NotThrow();
ds.GetRowCount("sqlite_master", null, null).Should().BeGreaterThan(0);
}
public void Execute_with_param()
{
using var db = new SQLiteDatastore();
db.Invoking(x => x.ExecuteScalar <string>("Select ?;", "'hello world'"))
.Should().Throw <SQLException>()
.And.CommandText.Should().NotBeNullOrEmpty();
// var result = db.ExecuteScalar<string>("Select ?;", "'hello world'");
}
public void Ctor_with_empty_path()
{
Action action = () =>
{
var db = new SQLiteDatastore("");
};
action.Should().Throw <ArgumentException>();
}
public void ExecuteScalar_Generic_Guid()
{
var query = "SELECT @p1;";
var expectedValue = Guid.NewGuid();
using (var ds = new SQLiteDatastore())
{
ds.ExecuteScalar <Guid>(query, expectedValue).Should().Be(expectedValue);
}
}
public void CheckTableExistsTest()
{
using (var ds = new SQLiteDatastore())
{
ds.Execute("CREATE TABLE tbl (col1 TEXT);");
ds.CheckTableExists("tbl");
ds.CheckTableExists("notATable").Should().BeFalse();
}
}
public void GetTableSQLTest()
{
using (var ds = new SQLiteDatastore())
{
ds.CreateTable("something", new ColumnInfo[] { new ColumnInfo("data") });
var tableSQL = ds.GetTableSQL("something");
tableSQL.Should().NotBeNullOrWhiteSpace();
Output.WriteLine(tableSQL);
}
}
public void Read_Empty_Table()
{
using (var ds = new SQLiteDatastore())
{
ds.Execute(TestDBBuilder.CREATE_MULTIPROPTABLE);
ds.CheckTableExists("MultiPropTable").Should().BeTrue();
ds.From <POCOMultiTypeObject>().Invoking(x => x.Query()).Should().NotThrow();
}
}
public void VerifySQLiteDatastore(SQLiteDatastore ds)
{
Assert.True(ds.Exists, "Assert file exists");
AssertEx.NotNullOrWhitespace(ds.Extension, "Assert file has extension");
Assert.NotNull(ds);
AssertEx.NotNullOrWhitespace(ds.Path);
var explaneSelectResult = ds.Execute("EXPLAIN SELECT 1;");
Assert.NotNull(explaneSelectResult);
Assert.True(ds.GetRowCount("sqlite_master", null, null) > 0);
}
public void QueryGeneric_Test(bool nulls)
{
using (var ds = new SQLiteDatastore())
{
ds.Execute(TestDBBuilder.CREATE_MULTIPROPTABLE);
var poco = CreateRandomPoco(nulls);
ds.Insert(poco);
var stuff = ds.QueryGeneric("SELECT * FROM MultiPropTable;").ToArray();
}
}
public void CommitTransaction_WtihNoTransaction()
{
using (var ds = new SQLiteDatastore())
{
ds.Execute("CREATE TABLE TableA (Data TEXT);");
ds.CurrentTransaction.Should().BeNull();
ds.CommitTransaction();//extra commit should not throw exception, but will fail Debug.Assert
ds.CheckTableExists("TableA").Should().BeTrue();
}
}
public void BackupDatabase(SQLiteDatastore targetDatabase)
{
var targetConn = targetDatabase.OpenConnection();
try
{
BackupDatabase(targetConn);
}
finally
{
targetDatabase.ReleaseConnection();
}
}
public void CreateInmemorySQLiteDatastoreTest()
{
var dbBuilder = new TestDBBuilder();
using (var ds = new SQLiteDatastore())
{
Assert.True(ds.Exists);
ds.CreateDatastore(dbBuilder);
VerifySQLiteDatastore(ds);
}
}
public void RollBackTransaction_WtihTransaction()
{
using (var ds = new SQLiteDatastore())
{
ds.BeginTransaction();
ds.Execute("CREATE TABLE TableA (Data TEXT);");
ds.RollbackTransaction();
ds.CheckTableExists("TableA").Should().BeFalse();
}
}
public void CheckFieldExistsTest()
{
using (var ds = new SQLiteDatastore())
{
ds.Execute("CREATE TABLE TableA (ID INTEGER PRIMARY KEY);");
Assert.True(ds.CheckFieldExists("TableA", "id"));
//test ignores case
Assert.True(ds.CheckFieldExists("TableA", "ID"));
//test ignores white space
Assert.True(ds.CheckFieldExists("TableA", " ID"));
Assert.False(ds.CheckFieldExists("TABLEA", "data"));
}
}
public void QueryRowIDAsPrimaryKeyObject()
{
using (var ds = new SQLiteDatastore())
{
ds.Execute(RowIDAsPrimaryKey.CREATE_TABLE_COMMAND);
ds.Insert(new RowIDAsPrimaryKey {
StringField = "something"
});
var result = ds.From <RowIDAsPrimaryKey>().Query().First();
result.RowID.Should().Be(1);
}
}
public void SetTableAutoIncrementStartTest()
{
using (var ds = new SQLiteDatastore())
{
ds.Execute("CREATE TABLE tbl (id INTEGER PRIMARY KEY AUTOINCREMENT);");
ds.ExecuteScalar <int>("SELECT max(id) FROM tbl;").Should().Be(0);
ds.Execute("INSERT INTO tbl DEFAULT VALUES;");
ds.ExecuteScalar <int>("SELECT max(id) FROM tbl;").Should().Be(1);
ds.SetTableAutoIncrementStart("tbl", 100);
ds.ExecuteScalar <int>("SELECT max(id) FROM tbl;").Should().Be(1);
ds.Execute("INSERT INTO tbl DEFAULT VALUES;");
ds.ExecuteScalar <int>("SELECT max(id) FROM tbl;").Should().Be(101);
}
}
public void GetRowCountTest()
{
using (var ds = new SQLiteDatastore())
{
ds.CreateTable("something", new ColumnInfo[] { new ColumnInfo("data") });
var rowCnt = ds.GetRowCount("something", null);
Assert.True(rowCnt == 0);
ds.Execute("INSERT INTO something DEFAULT VALUES");
rowCnt = ds.GetRowCount("something", null);
Assert.Equal(1, rowCnt);
}
}
public void HasForeignKeyErrors()
{
using (var ds = new SQLiteDatastore())
{
ds.Execute("PRAGMA foreign_keys = off;");
ds.Execute("Create table TableA (ID INTEGER PRIMARY KEY);");
ds.Execute("CREATE TABLE TABLEB (ID_B REFERENCES TABLEA (ID));");
ds.Execute("INSERT INTO TABLEA ([ID]) VALUES (1);");
ds.Execute("INSERT INTO TABLEB VALUES (1);");
Assert.False(ds.HasForeignKeyErrors("TableB"));
ds.Execute("INSERT INTO TABLEB VALUES (2);");
Assert.True(ds.HasForeignKeyErrors("TableB"));
}
}
public void Insert()
{
using (var ds = new SQLiteDatastore())
{
ds.CreateDatastore(new TestDBBuilder());
var ent = POCOMultiTypeObject.CreateWithNullID();
ent.ID.Should().BeNull();
ds.Insert(ent);
ent.ID.Should().NotBeNull();
var entAgain = ds.From <POCOMultiTypeObject>().Query().Single();
ent.Should().BeEquivalentTo(entAgain);
}
}
