private static async Task Given_a_logTtable_and_an_ftsTable(IDbConnection connection)
{
var tableSql = SQLiteSQLGenerator.Table <FTSContext.Log>();
await connection.ExecuteAsync(tableSql);
var ftsTableSql = SQLiteSQLGenerator.FTSTable <FTSContext.Log>(FTSTableType.ExternalContent);
await connection.ExecuteAsync(ftsTableSql);
}
public void When_generating_table_sql_for_model_with_no_id_and_without_rowid()
{
var e = Should.Throw <ArgumentException>(() =>
SQLiteSQLGenerator.Table <SomeModelWithNoId>(withoutRowId: true));
e.Message.ShouldBe("Every WITHOUT ROWID table must have a PRIMARY KEY specified. Check you model.\r\nParameter name: withoutRowId");
e.ParamName.ShouldBe("withoutRowId");
}
public void When_generating_full_text_search_content_less_table_sql()
{
Should.Throw <KeyNotFoundException>(() => SQLiteSQLGenerator.FTSTable <SampleModel>(FTSTableType.ContentLess, m => m.Composite))
.Message.ShouldBe("Could not find a mapping for property: Composite. Ensure it is not marked with an IgnoreAttribute.");
var ftsTableSql = SQLiteSQLGenerator.FTSTable <SampleModel>(FTSTableType.ContentLess, m => m.Flag, m => m.Text, m => m.Guid);
ftsTableSql.ShouldNotBeNullOrWhiteSpace();
ftsTableSql.ShouldBe("CREATE VIRTUAL TABLE IF NOT EXISTS SampleModel_fts USING FTS4 (content=\"\", [Flag], [Text], [Key]);");
}
public void When_generating_table_sql_for_model_with_no_id_and_with_rowid()
{
var tableSql = SQLiteSQLGenerator.Table <SomeModelWithNoId>(withoutRowId: false);
tableSql.ShouldNotBeNullOrWhiteSpace();
tableSql.ShouldBe("CREATE TABLE IF NOT EXISTS SomeModelWithNoId (\r\n"
+ " [_Entry_TimeStamp_Epoch_ms_] INTEGER DEFAULT (CAST((julianday('now') - 2440587.5)*86400000 AS INTEGER)),\r\n"
+ " [Name] TEXT NOT NULL,\r\n"
+ " [Age] INTEGER NOT NULL,\r\n"
+ " [Alias] TEXT\r\n);");
}
public void When_generating_table_sql_for_model_with_overridden_name()
{
var tableSql = SQLiteSQLGenerator.Table <SampleModel>("FooFoo");
tableSql.ShouldNotBeNullOrWhiteSpace();
tableSql.ShouldBe("CREATE TABLE IF NOT EXISTS FooFoo (\r\n"
+ " [_Entry_TimeStamp_Epoch_ms_] INTEGER DEFAULT (CAST((julianday('now') - 2440587.5)*86400000 AS INTEGER)),\r\n"
+ " [Id] INTEGER PRIMARY KEY NOT NULL,\r\n"
+ " [Text] TEXT NOT NULL,\r\n"
+ " [Int] INTEGER NOT NULL,\r\n"
+ " [Decimal] REAL NOT NULL,\r\n"
+ " [Double] REAL NOT NULL,\r\n"
+ " [Float] REAL NOT NULL,\r\n"
+ " [Flag] INTEGER NOT NULL,\r\n"
+ " [Binary] BLOB NOT NULL,\r\n"
+ " [Key] TEXT NOT NULL,\r\n"
+ " [DateTime] TEXT NOT NULL,\r\n"
+ " [DateTimeOffset] TEXT NOT NULL\r\n);");
}
public void When_generating_full_text_search_external_content_table_sql()
{
Should.Throw <KeyNotFoundException>(() => SQLiteSQLGenerator.FTSTable <SampleModel>(FTSTableType.ExternalContent, m => m.Composite))
.Message.ShouldBe("Could not find a mapping for property: Composite. Ensure it is not marked with an IgnoreAttribute.");
var ftsTableSql = SQLiteSQLGenerator.FTSTable <SampleModel>(FTSTableType.ExternalContent, m => m.Flag, m => m.Text, m => m.Guid);
ftsTableSql.ShouldNotBeNullOrWhiteSpace();
ftsTableSql.ShouldBe("CREATE VIRTUAL TABLE IF NOT EXISTS SampleModel_fts USING FTS4 (content=\"SampleModel\", [Flag], [Text], [Key]);\r\n\r\n"
+ "CREATE TRIGGER IF NOT EXISTS SampleModel_bu BEFORE UPDATE ON SampleModel BEGIN\r\n"
+ " DELETE FROM SampleModel_fts WHERE docId = old.rowId;\r\n"
+ "END;\r\n\r\n"
+ "CREATE TRIGGER IF NOT EXISTS SampleModel_bd BEFORE DELETE ON SampleModel BEGIN\r\n"
+ " DELETE FROM SampleModel_fts WHERE docId = old.rowId;\r\n"
+ "END;\r\n\r\n"
+ "CREATE TRIGGER IF NOT EXISTS SampleModel_au AFTER UPDATE ON SampleModel BEGIN\r\n"
+ " INSERT INTO SampleModel_fts (docId, [Flag], [Text], [Key]) VALUES (new.rowId, new.[Flag], new.[Text], new.[Key]);\r\n"
+ "END;\r\n\r\n"
+ "CREATE TRIGGER IF NOT EXISTS SampleModel_ai AFTER INSERT ON SampleModel BEGIN\r\n"
+ " INSERT INTO SampleModel_fts (docId, [Flag], [Text], [Key]) VALUES (new.rowId, new.[Flag], new.[Text], new.[Key]);\r\n"
+ "END;");
}
public void When_generating_table_for_model_with_inheritance()
{
var parentTableSql = SQLiteSQLGenerator.Table <Parent>();
parentTableSql.ShouldNotBeNullOrWhiteSpace();
parentTableSql.ShouldBe("CREATE TABLE IF NOT EXISTS Parent (\r\n"
+ " [_Entry_TimeStamp_Epoch_ms_] INTEGER DEFAULT (CAST((julianday('now') - 2440587.5)*86400000 AS INTEGER)),\r\n"
+ " [Id] INTEGER PRIMARY KEY NOT NULL,\r\n"
+ " [Name] TEXT NOT NULL,\r\n"
+ " [Age] INTEGER NOT NULL\r\n);");
var childTableSql = SQLiteSQLGenerator.Table <Child>();
childTableSql.ShouldNotBeNullOrWhiteSpace();
childTableSql.ShouldBe("CREATE TABLE IF NOT EXISTS Child (\r\n"
+ " [_Entry_TimeStamp_Epoch_ms_] INTEGER DEFAULT (CAST((julianday('now') - 2440587.5)*86400000 AS INTEGER)),\r\n"
+ " [Toy] TEXT NOT NULL,\r\n"
+ " [PetName] TEXT NOT NULL,\r\n"
+ " [Id] INTEGER PRIMARY KEY NOT NULL,\r\n"
+ " [Name] TEXT NOT NULL,\r\n"
+ " [Age] INTEGER NOT NULL\r\n);");
}
public async Task When_querying_multiple_multiple_rows()
{
using (var conn = new SQLiteInMemoryConnection())
{
await conn.ExecuteAsync(SQLiteSQLGenerator.Table <ModelOne>());
await conn.ExecuteAsync(SQLiteSQLGenerator.Table <ModelTwo>());
var repoOne = conn.GetDBContext <ModelOne>(SQLiteDialect.Instance);
var repoTwo = conn.GetDBContext <ModelTwo>(SQLiteDialect.Instance, "ModelTwo");
await repoOne.Insert(new[]
{
new ModelOne {
Name = "M1-A"
},
new ModelOne {
Name = "M1-B"
},
new ModelOne {
Name = "M1-C"
}
});
await repoTwo.Insert(new[]
{
new ModelTwo {
Category = "M2-C-A", Number = 1
},
new ModelTwo {
Category = "M2-C-B", Number = 2
},
new ModelTwo {
Category = "M2-C-C", Number = 3
},
new ModelTwo {
Category = "M2-C-D", Number = 4
}
});
using (var reader = await conn.QueryMultipleAsync("SELECT Id, Name FROM ModelOne; SELECT Id, Number, Category FROM ModelTwo;"))
{
reader.IsConsumed.ShouldBeFalse();
var modelOnes = (await reader.ReadAsync <ModelOne>(false)).ToArray();
reader.IsConsumed.ShouldBeFalse();
modelOnes.Length.ShouldBe(3);
modelOnes[0].Name.ShouldBe("M1-A");
modelOnes[1].Name.ShouldBe("M1-B");
modelOnes[2].Name.ShouldBe("M1-C");
var modelTwos = (await reader.ReadAsync <ModelTwo>()).ToArray();
reader.IsConsumed.ShouldBeTrue();
modelTwos.Length.ShouldBe(4);
modelTwos[0].Number.ShouldBe(1);
modelTwos[0].Category.ShouldBe("M2-C-A");
modelTwos[1].Number.ShouldBe(2);
modelTwos[1].Category.ShouldBe("M2-C-B");
modelTwos[2].Number.ShouldBe(3);
modelTwos[2].Category.ShouldBe("M2-C-C");
modelTwos[3].Number.ShouldBe(4);
modelTwos[3].Category.ShouldBe("M2-C-D");
}
}
}
