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(); }