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 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 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 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 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 CheckTableExistsTest()
{
using (var ds = new SQLiteDatastore())
{
ds.Execute("CREATE TABLE tbl (col1 TEXT);");
ds.CheckTableExists("tbl");
ds.CheckTableExists("notATable").Should().BeFalse();
}
}
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 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 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 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 ReadSingleRow()
{
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 <={1} " +
") " +
"INSERT INTO MultiPropTable (ID) SELECT * FROM generate_series;");
var row = db.ReadSingleRow <POCOMultiTypeObject>(1);
row.Should().NotBeNull();
}
}
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 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 GetTableInfoTest()
{
using (var ds = new SQLiteDatastore())
{
ds.Execute(TestDBBuilder.CREATE_MULTIPROPTABLE);
var ti = ds.GetTableInfo("MultiPropTable");
ti.Should().NotBeNullOrEmpty();
foreach (string fieldName in TestSQLConstants.MULTI_PROP_TABLE_FIELDS)
{
ti.Should().Contain(x => x.Name.ToLower() == fieldName.ToLower());
}
}
}
public void Query_Test(bool nulls)
{
using (var ds = new SQLiteDatastore())
{
ds.Execute(TestDBBuilder.CREATE_MULTIPROPTABLE);
var poco = CreateRandomPoco(nulls);
ds.Insert(poco);
var result = ds.Query <POCOMultiTypeObject>("SELECT * FROM MultiPropTable;")
.SingleOrDefault();
result.Should().NotBeNull();
result.Should().BeEquivalentTo(poco);
}
}
public void FluentInterfaceTest_Single_Record(bool nulls)
{
using (var ds = new SQLiteDatastore())
{
ds.Execute(TestDBBuilder.CREATE_MULTIPROPTABLE);
var poco = POCOMultiTypeObject.CreateWithNullID();
ds.Insert(poco);
var result = ds.From <MultiPropTypeWithAliases>().Query()
.SingleOrDefault();
result.Should().NotBeNull();
poco.Should().BeEquivalentTo(result, config => config.ExcludingMissingMembers());
result.AliasForStringField.Should().Be(result.StringField);
}
}
public void ReadOnly_Throws_On_BeginTransaction()
{
var path = _testReadOnlyPath;
Output.WriteLine(path);
using (var ds = new SQLiteDatastore(path))
{
ds.Execute(TestDBBuilder.CREATE_AUTOINCREMENT_TABLE);
ds.CreateTable("Tbl", new ColumnInfo[] { new ColumnInfo()
{
Name = "Data", DBType = System.Data.DbType.String
} }, false);
System.IO.File.Exists(path).Should().BeTrue();
System.IO.File.SetAttributes(path, System.IO.FileAttributes.ReadOnly);
ds.Invoking(x => x.BeginTransaction()).Should().Throw <ReadOnlyException>();
}
}
public void CommitTransaction_WtihTransaction()
{
using (var ds = new SQLiteDatastore())
{
ds.CurrentTransaction.Should().BeNull();
ds.BeginTransaction();
ds.CurrentTransaction.Should().NotBeNull();
ds.Execute("CREATE TABLE TableA (Data TEXT);");
ds.CommitTransaction();
ds.CurrentTransaction.Should().BeNull();
ds.TransactionDepth.Should().Be(0);
ds.CheckTableExists("TableA").Should().BeTrue();
}
}
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 AddFieldTest()
{
using (var ds = new SQLiteDatastore())
{
ds.Execute("CREATE TABLE TableA (ID INTEGER PRIMARY KEY);");
Assert.True(ds.CheckFieldExists("TableA", "ID"));
Assert.False(ds.CheckFieldExists("TABLEA", "Data"));
ds.AddField("TableA", new ColumnInfo()
{
Name = "Data", DBType = System.Data.DbType.AnsiString
});
Assert.True(ds.CheckFieldExists("TABLEA", "Data"));
Assert.Throws <SQLException>(() => ds.AddField("TableA", new ColumnInfo()
{
Name = "Data", DBType = System.Data.DbType.AnsiString
}));
}
}
public void ReadOnly_Throws_On_Insert()
{
var path = _testReadOnlyPath;
Output.WriteLine(path);
using (var ds = new SQLiteDatastore(path))
{
ds.Execute(TestDBBuilder.CREATE_AUTOINCREMENT_TABLE);
ds.CreateTable("Tbl", new ColumnInfo[] { new ColumnInfo()
{
Name = "Data", DBType = System.Data.DbType.String
} }, false);
System.IO.File.Exists(path).Should().BeTrue();
System.IO.File.SetAttributes(path, System.IO.FileAttributes.ReadOnly);
//TODO assert that connection is not open and transaction depth is 0
ds.Invoking(x => x.Execute("INSERT INTO Tbl (Data) VALUES ('something');")).Should().Throw <ReadOnlyException>();
}
}
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);
}
private void ValidateDatastore(SQLiteDatastore db)
{
db.Execute("Select 1;");
db.Path.Should().NotBeNullOrWhiteSpace();
}
