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();
}
/// <summary>
/// Links the current Bean with another Bean in a m:n relational manner and
/// provides data (linkProps) for the Link.
/// </summary>
/// <param name="bean">Bean to be linked.</param>
/// <param name="linkProps">Dictionary of Link Properties.</param>
/// <returns>true, if successful</returns>
public bool LinkWith(Bean bean, IDictionary <string, object> linkProps = null)
{
var ls = GetLinkScenario(bean.GetKind());
var linkedKindPkValue = bean.GetKeyValue();
var linkBean = Api.FindOne(false, ls.LinkKind,
"WHERE " + ls.LinkingKindFkName + " = {0} AND " + ls.LinkedKindFkName + " = {1}",
ls.LinkingKindPkValue, linkedKindPkValue);
if (linkBean != null)
{
throw LinkAlreadyExistsException.New(ls.LinkingKind, ls.LinkedKind);
}
linkBean = Api.Dispense(ls.LinkKind);
if (linkProps != null)
{
linkBean.Import(linkProps);
}
linkBean
.Put(ls.LinkingKindFkName, ls.LinkingKindPkValue)
.Put(ls.LinkedKindFkName, linkedKindPkValue)
.Store();
return(true);
}
/// Doing so has several advantages:
///
/// - All strings prone to typos (bean kind and field names) are encapsulated inside.
/// - You get compile-time checks, IDE assistance and [typed properties](#typed-accessors).
/// - With [Lifecycle Hooks](#lifecycle-hooks), it is easy to implement [data validation](#data-validation) and [relations](#relations).
///
/// For [Custom Beans Classes](#custom-bean-classes), use method overloads with a generic parameter:
///
void Overloads(BeanApi api)
{
#if CODE
api.Dispense <Book>();
api.Load <Book>(1);
api.Find <Book>("WHERE rating > {0}", 7);
// and so on
#endif
}
public void IsNewBean()
{
_storage.EnterFluidMode();
_storage.Store("foo", SharedChecks.MakeRow("a", 1));
var foo = _api.Dispense("foo");
foo["a"] = 2;
Assert.True(_storage.IsNew(foo));
}
public void Scenario()
{
using (var api = new BeanApi("data source=:memory:", SQLiteFactory.Instance))
{
api.EnterFluidMode();
api.DefaultKey(false);
api.AddObserver(new GuidKeyObserver());
var bean = api.Dispense("foo");
var key = api.Store(bean);
Console.WriteLine("Key is: " + key);
}
}
void CRUD(BeanApi api)
{
/// ## Getting Started: Basic CRUD (Create/Read/Update/Delete)
///
/// For basic usage, LimeBean requires no configuration or table classes!
///
/// Take a look at some basic CRUD scenarios:
///
/// **Create**
#if CODE
// Create a Bean.
// "Bean" means row, and "Dispense" makes an empty Bean for a table.
var bean = api.Dispense("book");
// Each bean has a "Kind". Kind is a synonym for "table name"
// You give a Bean its Kind when you Dispense it, or query the database
var kind = bean.GetKind();
Console.WriteLine(kind);
// Fill the new Bean with some data
bean["title"] = "Three Comrades";
bean["rating"] = 10;
// You can also chain .Put() to do this
bean.Put("title", "Three Comrades")
.Put("rating", 10);
// Store it
// Store() will Create or Update a record intelligently
var id = api.Store(bean);
// Store also returns the Primary Key for the saved Bean, even for multi-column/compound keys
Console.WriteLine(id);
#endif
/// **Read** and **Update**
#if CODE
// Load a Bean with a known ID
bean = api.Load("book", id);
// Make some edits
bean["release_date"] = new DateTime(2015, 7, 30);
bean["rating"] = 5;
// Update database
api.Store(bean);
#endif
/// **Delete**
#if CODE
api.Trash(bean);
#endif
}
void BeanOptions(BeanApi api)
{
/// ## Bean Options
/// You can configure the BeanAPI to dispense new Beans with some default options
///
/// **.ValidateGetColumns**
#if CODE
// Sets whether a Bean throws `ColumnNotFoundException` if
// you request a column which isn't stored in the Bean. True by default
api.BeanOptions.ValidateGetColumns = true;
Bean bean = api.Dispense("books");
bean.Put("ColumnOne", 1); // Add a single column
int one = bean.Get <int>("ColumnOne"); // OK
int two = bean.Get <int>("ColumnTwo"); // throws ColumnNotFoundException
#endif
}
void FluidMode(BeanApi api)
{
/// ## Fluid Mode
/// LimeBean mitigates the common inconvenience associated with relational databases,
/// namely necessity to manually create tables, columns and adjust their data types.
/// In this sense, LimeBean takes SQL databases a little closer to NoSQL ones like MongoDB.
///
/// **Fluid Mode** is optional, turned off by default, and is recommended for use only during early development stages
/// (particularly for prototyping and scaffolding).
/// To enable it, invoke the `EnterFluidMode` method on the `BeanApi` object:
#if CODE
api.EnterFluidMode();
// Make a Bean for a table which doesn't yet exist
var bean = api.Dispense("book_types");
// Fill it with some data
// Limebean will automatically detect Types and create columns with the correct Type
bean.Put("name", "War")
.Put("fiction", true);
// Store will automatically create any missing tables (with an auto-incrementing 'id' column) and columns,
// then add the Bean as a new row
var id = api.Store(bean);
// The bean is now available in the database
var savedBean = api.Load("book_types", id);
#endif
/// How does this work? When you save a Bean while in Fluid Mode, LimeBean analyzes its fields and compares
/// their names and types to the database schema.
/// If new data cannot be stored to an existing table, schema alteration occurs.
/// LimeBean can create new tables, add missing columns, and widen data types.
/// It will never truncate data or delete unused columns.
///
/// **NOTE:** LimeBean will not detect renamings.
///
/// **CAUTION:** Automatically generated schema is usually sub-optimal and lacks indexes which are essential
/// for performance. When most planned tables are already in place,
/// it is recommended you turn Fluid Mode off, audit the database structure, add indexes, and make further schema
/// changes with a dedicated database management tool (like HeidiSQL, SSMS, pgAdmin, etc).
///
}
public Auditor(BeanApi api, string auditBlacklist)
{
var exitFluidMode = false;
_auditBlacklist = auditBlacklist == string.Empty
? new List <string>()
: auditBlacklist.ToUpper().Split(';').ToList();
_auditBlacklist.Add("AUDIT");
if ((api.Database == string.Empty && api.Connection.State != ConnectionState.Open) ||
api.IsKnownKind("AUDIT"))
{
return;
}
if (!api.IsFluidMode())
{
api.EnterFluidMode();
exitFluidMode = true;
}
var audit = api.Dispense("AUDIT");
audit
.Put("AuditDate", DateTime.Now)
.Put("Action", new string('X', 16))
.Put("User", new string('X', 64))
.Put("Object", new string('X', 64))
.Put("ObjectId", new string('X', 64))
.Put("Property", new string('X', 64))
.Put("PropertyType", new string('X', 64))
.Put("OldValue", new string('X', 1024))
.Put("NewValue", new string('X', 1024))
.Put("Notes", new string('X', 4096))
.Store();
if (exitFluidMode)
{
api.ExitFluidMode();
}
}
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"));
}
public static T CreateBeam <T>() where T : Bean, new()
{
return(DbConnection.Dispense <T>());
}
public MapsterTests()
{
_api = SQLitePortability.CreateApi();
_bean = _api.Dispense("Bean")
.Put("Id", 123)
.Put("A", 1)
.Put("B", "abc");
_beans = new List <Bean>
{
new Bean
{
["Id"] = 123,
["A"] = 1,
["B"] = "abc"
},
new Bean
{
["Id"] = 124,
["A"] = 2,
["B"] = "def"
},
new Bean
{
["Id"] = 125,
["A"] = 3,
["B"] = "ghi"
}
};
_pocoBean = new PocoBean()
{
Id = 123,
A = 1,
B = "abc"
};
_pocoBeans = new List <PocoBean>
{
new PocoBean
{
Id = 123,
A = 1,
B = "abc"
},
new PocoBean
{
Id = 124,
A = 2,
B = "def"
},
new PocoBean
{
Id = 125,
A = 3,
B = "ghi"
}
};
}
void CRUD()
{
/// ## CRUD
/// (Create / Read / Update / Delete)
///
/// For basic usage, LimeBean requires zero configuration and no additional code!
/// Database schema is maintained on-the-fly:
/// no need to create tables and columns (see [Fluid Mode](#fluid-mode)).
///
/// Take a look at the following sample scenario:
///
#if CODE
// Use a temporary in-memory SQLite database
var api = new BeanApi("Data Source=:memory:", SQLiteFactory.Instance);
// Enter the "Fluid Mode"
api.EnterFluidMode();
#endif
/// **Create**
#if CODE
// Create a bean. "Bean" means "data record", "dispense" means "instantiate new".
var bean = api.Dispense("book");
// Each bean has a kind. "Kind" is a synonym for "table name"
var kind = bean.GetKind();
Console.WriteLine(kind);
// Fill it with some data
bean["title"] = "Three Comrades";
bean["rating"] = 10;
// Store it
// Table "book" with 2 columns, one string and one integer, will be generated automatically
var id = api.Store(bean);
// Each saved bean has an ID, or primary key
Console.WriteLine(id);
#endif
/// **Read**
#if CODE
// Load by ID
bean = api.Load("book", id);
#endif
/// **Update**
#if CODE
// Make some edits
bean["title"] = "Learn LimeBean";
bean["release_date"] = new DateTime(2015, 7, 30);
bean["rating"] = "good";
// Save updated bean
// One new column ("release_date") will be added
// The type of column "rating" will be expanded from integer to string
api.Store(bean);
#endif
/// **Delete**
#if CODE
api.Trash(bean);
// Don't forget to close the connection
api.Dispose();
#endif
}
void CRUD()
{
/// ## CRUD
/// (Create / Read / Update / Delete)
///
/// For basic usage, LimeBean requires zero configuration and no additional code!
/// Database schema is maintained on-the-fly:
/// no need to create tables and columns (see [Fluid Mode](#fluid-mode)).
///
/// Take a look at the following sample scenario:
///
#if CODE
// Use a temporary in-memory SQLite database
var api = new BeanApi("Data Source=:memory:", SQLiteFactory.Instance);
// Enter the "Fluid Mode"
api.EnterFluidMode();
#endif
/// **Create**
#if CODE
// Create a bean. "Bean" means "data record", "dispense" means "instantiate new".
var bean = api.Dispense("book");
// Each bean has a kind. "Kind" is a synonym for "table name"
var kind = bean.GetKind();
Console.WriteLine(kind);
// Fill it with some data
bean["title"] = "Three Comrades";
bean["rating"] = 10;
// Store it
// Table "book" with 2 columns, one string and one integer, will be generated automatically
var id = api.Store(bean);
// Each saved bean has an ID, or primary key
Console.WriteLine(id);
#endif
/// **Read**
#if CODE
// Load by ID
bean = api.Load("book", id);
#endif
/// **Update**
#if CODE
// Make some edits
bean["title"] = "Learn LimeBean";
bean["release_date"] = new DateTime(2015, 7, 30);
bean["rating"] = "good";
// Save updated bean
// One new column ("release_date") will be added
// The type of column "rating" will be expanded from integer to string
api.Store(bean);
#endif
/// **Delete**
#if CODE
api.Trash(bean);
// Don't forget to close the connection
api.Dispose();
#endif
}
/// Doing so has several advantages:
///
/// - All strings prone to typos (bean kind and field names) are encapsulated inside.
/// - You get compile-time checks, IDE assistance and [typed properties](#typed-accessors).
/// - With [Lifecycle Hooks](#lifecycle-hooks), it is easy to implement [data validation](#data-validation) and [relations](#relations).
///
/// For [Custom Beans Classes](#custom-bean-classes), use method overloads with a generic parameter:
///
void Overloads(BeanApi api)
{
#if CODE
api.Dispense<Book>();
api.Load<Book>(1);
api.Find<Book>("WHERE rating > {0}", 7);
// and so on
#endif
}
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)");
}
