public void Default_OnlyDirtyPropsWritten()
{
_bean["a"] = "bean change";
_api.Exec("update foo set b='external change'");
_api.Store(_bean);
Assert.Equal("external change", _api.Cell <string>("select b from foo"));
}
public DirtyTrackingTests()
{
_api = SQLitePortability.CreateApi();
_api.Exec("create table foo(id, a, b)");
_api.Exec("insert into foo values(1, 'initial', 'initial')");
_bean = _api.Load("foo", 1);
_api.QueryExecuting += cmd => _queryCount++;
}
public DirtyTrackingTests()
{
_api = SQLitePortability.CreateApi();
_api.Exec("create table foo(id, a, b)");
_api.Exec("insert into foo values(1, 'initial', 'initial')");
_bean = _api.Load("foo", 1);
_api.QueryExecuting += cmd => _queryCount++;
}
public static int Execute(this SqlBuilder sqlBuilder, BeanApi api, params object[] parameters)
{
var query = sqlBuilder.ToSql();
return(query.StartsWith("SELECT")
? throw NotExecutableException.Create()
: api.Exec(query, parameters));
}
public SequelQueryBuilderTests()
{
_api = SQLitePortability.CreateApi();
_api.Exec("CREATE TABLE Product (id INTEGER NOT NULL PRIMARY KEY, Name)");
_api.Dispense("Product").Put("Name", "MacBook Pro 13").Store();
_api.Dispense("Product").Put("Name", "Microsoft Surface IV").Store();
_api.Dispense("Product").Put("Name", "Lenovo ThinkPad X1").Store();
_api.Dispense("Product").Put("Name", "Dell XPS 13").Store();
_api.Dispense("Product").Put("Name", "Lenovo Yoga").Store();
}
public static bool DeleteAmount(int shopId)
{
try
{
DbConnection.Exec("DELETE FROM storage WHERE id_shop = {0}", shopId);
return(true);
}
catch (Exception e)
{
Console.WriteLine("Fehler: " + e);
return(false);
}
}
void GenericSqlQueries(BeanApi api)
{
/// ## Generic SQL Queries
/// Often it's needed to execute queries which don't map to beans:
/// aggregates, grouping, joins, selecting single column, etc.
///
/// `BeanApi` provides methods for such tasks:
///
{
#if CODE
// Load multiple rows
var rows = api.Rows(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author", 7);
// Load a single row
var row = api.Row(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author
ORDER BY COUNT(*) DESC
LIMIT 1", 7);
// Load a column
var col = api.Col <string>("SELECT DISTINCT author FROM book ORDER BY author");
// Load a single value
var count = api.Cell <int>("SELECT COUNT(*) FROM book");
#endif
}
/// For `Rows` and `Col`, there are unbuffered (memory-optimized) counterparts:
{
#if CODE
foreach (var row in api.RowsIterator("SELECT..."))
{
// do something
}
foreach (var item in api.ColIterator("SELECT..."))
{
// do something
}
#endif
}
/// To execute a non-query SQL command, use the `Exec` method:
#if CODE
api.Exec("SET autocommit = 0");
#endif
/// **NOTE:** all described functions accept parameters in the same form as [finder methods](#finding-beans-with-sql) do.
}
public void Assigned_FrozenMode()
{
_api.Exec("create table foo (pk, prop)");
_api.Key("foo", "pk", false);
var bean = _api.Dispense("foo");
bean["pk"] = "pk1";
bean["prop"] = "value1";
var key = _api.Store(bean);
Assert.Equal("pk1", key);
bean["prop"] = "value2";
Assert.Equal(key, _api.Store(bean));
bean = _api.Load("foo", key);
Assert.Equal(key, bean["pk"]);
Assert.Equal("value2", bean["prop"]);
_api.Trash(bean);
Assert.Equal(0, _api.Count("foo"));
}
void GenericSqlQueries(BeanApi api)
{
/// ## Generic SQL Queries
/// Often it's needed to execute queries which don't map to beans:
/// aggregates, grouping, joins, selecting single column, etc.
///
/// `BeanApi` provides methods for such tasks:
///
{
#if CODE
// Load multiple rows
var rows = api.Rows(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author", 7);
// Load a single row
var row = api.Row(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author
ORDER BY COUNT(*) DESC
LIMIT 1", 7);
// Load a column
var col = api.Col<string>("SELECT DISTINCT author FROM book ORDER BY author");
// Load a single value
var count = api.Cell<int>("SELECT COUNT(*) FROM book");
#endif
}
/// For `Rows` and `Col`, there are unbuffered (memory-optimized) counterparts:
{
#if CODE
foreach(var row in api.RowsIterator("...")) {
// do something
}
foreach(var item in api.ColIterator("...")) {
// do something
}
#endif
}
/// To execute a non-query SQL command, use the `Exec` method:
#if CODE
api.Exec("SET autocommit = 0");
#endif
/// **NOTE:** all described functions accept parameters in the same form as [finder methods](#finding-beans-with-sql) do.
}
private void CreateLinkTestScenario(bool withLinks = true)
{
_api.Exec("CREATE TABLE Store (id INTEGER NOT NULL PRIMARY KEY, Name)");
_api.Exec("CREATE TABLE Product (id INTEGER NOT NULL PRIMARY KEY, Name)");
_api.Exec("CREATE TABLE Supplier (id INTEGER NOT NULL PRIMARY KEY, Name)");
_api.Exec("CREATE TABLE StoreProduct_link (id INTEGER NOT NULL PRIMARY KEY, Store_id, Product_id, OnStock, IsSale)");
_api.Exec("CREATE TABLE SupplierProduct_link (id INTEGER NOT NULL PRIMARY KEY, Supplier_id, Product_id, DeliveryTime)");
_api.Dispense("Store").Put("Name", "Main Store").Store();
_api.Dispense("Store").Put("Name", "Oxford Street").Store();
_api.Dispense("Store").Put("Name", "Madison Avenue").Store();
_api.Dispense("Product").Put("Name", "MacBook Pro 13").Store();
_api.Dispense("Product").Put("Name", "Microsoft Surface IV").Store();
_api.Dispense("Product").Put("Name", "Lenovo ThinkPad X1").Store();
_api.Dispense("Product").Put("Name", "Dell XPS 13").Store();
_api.Dispense("Product").Put("Name", "Lenovo Yoga").Store();
_api.Dispense("Supplier").Put("Name", "CheaperNotebooks").Store();
_api.Dispense("Supplier").Put("Name", "Mike''s Notebooks").Store();
_api.Dispense("Supplier").Put("Name", "Laptops Galore").Store();
if (!withLinks)
{
return;
}
_api.Exec("INSERT INTO StoreProduct_link VALUES(1, 1, 1, 4, false)");
_api.Exec("INSERT INTO StoreProduct_link VALUES(2, 1, 2, 5, false)");
_api.Exec("INSERT INTO StoreProduct_link VALUES(3, 1, 3, 2, true)");
_api.Exec("INSERT INTO StoreProduct_link VALUES(4, 1, 4, 9, false)");
_api.Exec("INSERT INTO StoreProduct_link VALUES(5, 1, 5, 6, true)");
_api.Exec("INSERT INTO StoreProduct_link VALUES(6, 2, 3, 10, false)");
_api.Exec("INSERT INTO StoreProduct_link VALUES(7, 2, 5, 7, false)");
_api.Exec("INSERT INTO StoreProduct_link VALUES(8, 3, 1, 15, false)");
_api.Exec("INSERT INTO StoreProduct_link VALUES(9, 3, 2, 21, false)");
_api.Exec("INSERT INTO SupplierProduct_link VALUES(1, 1, 3, 7)");
_api.Exec("INSERT INTO SupplierProduct_link VALUES(2, 1, 5, 7)");
_api.Exec("INSERT INTO SupplierProduct_link VALUES(3, 2, 1, 7)");
_api.Exec("INSERT INTO SupplierProduct_link VALUES(4, 2, 2, 7)");
_api.Exec("INSERT INTO SupplierProduct_link VALUES(5, 2, 4, 7)");
_api.Exec("INSERT INTO SupplierProduct_link VALUES(6, 3, 1, 3)");
_api.Exec("INSERT INTO SupplierProduct_link VALUES(7, 3, 2, 3)");
_api.Exec("INSERT INTO SupplierProduct_link VALUES(8, 3, 3, 3)");
_api.Exec("INSERT INTO SupplierProduct_link VALUES(9, 3, 4, 5)");
_api.Exec("INSERT INTO SupplierProduct_link VALUES(0, 3, 5, 5)");
}
