static void Main(string[] args)
{
string connectionString = null;
string tableName = null;
string[] columnsName = null;
var parser = new Fclp.FluentCommandLineParser();
parser.Setup <string>("connectionString").Callback(x => connectionString = x);
parser.Setup <string>("tableName").Callback(x => tableName = x);
parser.Setup <string>("columnsName").Callback(x => columnsName = x.Split(','));
parser.Parse(args);
Console.WriteLine("Connecting...");
var parameters = new SqlSetParameters(connectionString, tableName, columnsName);
var set = new SqlSet(parameters);
set.CreateObjects();
set.AddIfNotExists(new[] { new ItemDto() });
set.AddIfNotExists(new[] { new ItemDto()
{
Int = 2
} });
}
public void Cannot_Include_Many_In_Many()
{
SqlSet <SqlSetInclude.Employee> set = db.Table <SqlSetInclude.Employee>()
.Include("Orders.OrderDetails");
SqlSetInclude.Employee item = set.First();
}
public void Can_Include_Many_Multiple()
{
SqlSet <SqlSetInclude.Employee> set1 = db.Table <SqlSetInclude.Employee>()
.Include("EmployeeTerritories");
SqlSet <SqlSetInclude.Employee> set2 = set1.Include("Orders");
SqlSetInclude.Employee item = set1.First();
Assert.IsNotNull(item.EmployeeTerritories);
Assert.AreNotEqual(0, item.EmployeeTerritories.Count);
Assert.IsTrue(item.EmployeeTerritories.All(p => Object.ReferenceEquals(p.Employee, item)));
// test immutability
Assert.IsTrue(item.Orders == null || item.Orders.Count == 0);
item = set2.First();
Assert.IsNotNull(item.EmployeeTerritories);
Assert.AreNotEqual(0, item.EmployeeTerritories.Count);
Assert.IsTrue(item.EmployeeTerritories.All(p => Object.ReferenceEquals(p.Employee, item)));
Assert.IsNotNull(item.Orders);
Assert.AreNotEqual(0, item.Orders.Count);
//Assert.IsTrue(item.Orders.All(p => Object.ReferenceEquals(p.Employee, item)));
}
private void Start()
{
uiHandler = GetComponent <LeaderboardUIHandler>();
sql = GetComponent <SqlSet>();
sqlGet = GetComponent <SqlGet>();
confirm.onClick.AddListener(delegate(){ GetInput(); });
}
public void ContainsKey()
{
var db = SqlServerNorthwindDatabase();
SqlSet <SqlSetAnnotated.Model1.Product> table = db.Table <SqlSetAnnotated.Model1.Product>();
Assert.IsTrue(table.ContainsKey(1));
Assert.IsFalse(table.Where("ProductID = 2").ContainsKey(1));
}
public void Find()
{
var db = SqlServerNorthwindDatabase();
SqlSet <SqlSetAnnotated.Model1.Product> table = db.Table <SqlSetAnnotated.Model1.Product>();
Assert.IsNotNull(table.Find(1));
Assert.IsNull(table.Where("ProductID = 2").Find(1));
}
public void Can_Include_One_Nested()
{
SqlSet <SqlSetInclude.EmployeeTerritory> set = db.Table <SqlSetInclude.EmployeeTerritory>()
.Include("Territory.Region");
SqlSetInclude.EmployeeTerritory item = set.First();
Assert.IsNotNull(item.Territory);
Assert.IsNotNull(item.Territory.Region);
}
public void Can_Include_Many_In_One()
{
SqlSet <SqlSetInclude.EmployeeTerritory> set = db.Table <SqlSetInclude.EmployeeTerritory>()
.Include("Employee.Orders");
SqlSetInclude.EmployeeTerritory item = set.First();
Assert.IsNotNull(item.Employee);
Assert.AreNotEqual(0, item.Employee.Orders.Count);
//Assert.IsTrue(item.Employee.Orders.All(p => Object.ReferenceEquals(p.Employee, item.Employee)));
}
public void Can_Include_One_In_Many()
{
SqlSet <SqlSetInclude.Employee> set = db.Table <SqlSetInclude.Employee>()
.Include("EmployeeTerritories.Territory");
SqlSetInclude.Employee item = set.First();
Assert.IsNotNull(item.EmployeeTerritories);
Assert.AreNotEqual(0, item.EmployeeTerritories.Count);
Assert.IsTrue(item.EmployeeTerritories.All(p => p.Territory != null));
}
public void Count_ReturnsNumberOfItems()
{
var values = new SqlSet();
Assert.Equal(0, values.Count);
values.Add(new SqlAssign("Name", "John Doe"));
Assert.Equal(1, values.Count);
values.Add(new SqlAssign("Email", "*****@*****.**"));
Assert.Equal(2, values.Count);
}
public void Remember_Mapper()
{
Database db = SqlServerDatabase();
SqlSet <string> set = db.From(SQL
.SELECT("'a' AS c")
, r => r.GetString(0))
.OrderBy("c");
string value = set.Single();
}
public void Can_Include_Many()
{
SqlSet <SqlSetInclude.Category> set = db.Table <SqlSetInclude.Category>()
.Include("Products");
SqlSetInclude.Category item = set.First();
Assert.IsNotNull(item.Products);
Assert.AreNotEqual(0, item.Products.Count);
Assert.IsTrue(item.Products.All(p => Object.ReferenceEquals(p.Category, item)));
}
public void AsEnumerable_Value_Type()
{
SqlSet <int> set = db.From(SQL
.SELECT("0")
, r => r.GetInt32(0));
set.AsEnumerable();
SqlSet untypedSet = set;
untypedSet.AsEnumerable();
}
public void AsEnumerable_Reference_Type()
{
SqlSet <string> set = db.From(SQL
.SELECT("'a'")
, r => r.GetString(0));
set.AsEnumerable();
SqlSet untypedSet = set;
untypedSet.AsEnumerable();
}
public void AsEnumerableValueType()
{
SqlSet <int> set = conn.Set(SQL
.SELECT("0")
, r => r.GetInt32(0));
set.AsEnumerable();
SqlSet untypedSet = set;
untypedSet.AsEnumerable();
}
public void AsEnumerableReferenceType()
{
SqlSet <string> set = conn.Set(SQL
.SELECT("'a'")
, r => r.GetString(0));
set.AsEnumerable();
SqlSet untypedSet = set;
untypedSet.AsEnumerable();
}
public SqlSetSamples(DbConnection conn, TextWriter log)
{
var db = new Database(conn)
{
Configuration =
{
Log = log
}
};
this.products = db.From <Product>("Products");
}
public void Count_ReturnsNumberOfItems()
{
var values = new SqlSet();
Assert.Equal(0, values.Count);
values.Add(new SqlAssign("Name", "John Doe"));
Assert.Equal(1, values.Count);
values.Add(new SqlAssign("Email", "*****@*****.**"));
Assert.Equal(2, values.Count);
}
public void Can_Include_One()
{
SqlSet <SqlSetInclude.Product> set = db.Table <SqlSetInclude.Product>()
.Where("NOT CategoryID IS NULL AND NOT SupplierID IS NULL")
.Include("Category")
.Include("Supplier");
SqlSetInclude.Product item = set.First();
Assert.IsNotNull(item.Category);
Assert.IsNotNull(item.Supplier);
}
public void Dont_Use_Subquery_For_Cast_Generic()
{
SqlSet set = db.From("products")
.Cast <object>()
.Where("NOT UnitPrice IS NULL");
SqlBuilder expected = SQL
.SELECT("*")
.FROM("products")
.WHERE("NOT UnitPrice IS NULL");
Assert.IsTrue(SqlEquals(set, expected));
}
public override async Task <IOrderedQueryableStorage <Item> > CreateAsync(params Item[] values)
{
var table = TableBuilder.WithName("OrderedItemsSet").WithKeyColumnsFromTypeProperties <Item>().Build();
_serverFixture.DropTable(table.Schema, table.Name);
var mapper = new TypeMapper <Item>(table);
var set = new SqlSet <Item>(_serverFixture.DatabaseDriver, _serverFixture.ConnectionString, table, mapper);
foreach (var value in values)
{
await set.AddAsync(value);
}
return(set);
}
public void Apply_Skip_After_Skip_Call()
{
SqlSet set = db.From("products")
.Skip(5)
.Skip(1);
SqlBuilder expected = SQL
.SELECT("*")
.FROM(SQL
.SELECT("*")
.FROM("products")
.OFFSET(5), "_")
.OFFSET(1);
Assert.IsTrue(SqlEquals(set, expected));
}
public void Apply_Take_After_Take_Call()
{
SqlSet set = db.From("products")
.Take(5)
.Take(1);
SqlBuilder expected = SQL
.SELECT("*")
.FROM(SQL
.SELECT("*")
.FROM("products")
.LIMIT(5), "_")
.LIMIT(1);
Assert.IsTrue(SqlEquals(set, expected));
}
public void Apply_Where_After_Skip_Call()
{
SqlSet set = db.From("products")
.Skip(5)
.Where("NOT UnitPrice IS NULL");
SqlBuilder expected = SQL
.SELECT("*")
.FROM(SQL
.SELECT("*")
.FROM("products")
.OFFSET(5), "_")
.WHERE("NOT UnitPrice IS NULL");
Assert.IsTrue(SqlEquals(set, expected));
}
public void Dont_Use_Subqueries_When_Methods_Are_Called_In_Order()
{
SqlSet set = db.From("products")
.Where("UnitsInStock > 0")
.Skip(0)
.Take(5);
SqlBuilder expected = SQL
.SELECT("*")
.FROM("products")
.WHERE("UnitsInStock > 0")
.LIMIT(5)
.OFFSET(0);
Assert.IsTrue(SqlEquals(set, expected));
}
public void Apply_OrderBy_After_Take_Call()
{
SqlSet set = db.From("products")
.Take(5)
.OrderBy("UnitPrice");
SqlBuilder expected = SQL
.SELECT("*")
.FROM(SQL
.SELECT("*")
.FROM("products")
.LIMIT(5), "_")
.ORDER_BY("UnitPrice");
Assert.IsTrue(SqlEquals(set, expected));
}
public void Apply_Where_After_OrderBy_Call()
{
SqlSet set = db.From("products")
.OrderBy("ProductID DESC")
.Where("NOT UnitPrice IS NULL");
SqlBuilder expected = SQL
.SELECT("*")
.FROM(SQL
.SELECT("*")
.FROM("products")
.ORDER_BY("ProductID DESC"), "_")
.WHERE("NOT UnitPrice IS NULL");
Assert.IsTrue(SqlEquals(set, expected));
}
public void Dont_Use_Subqueries_When_Methods_Are_Called_In_Order()
{
var db = new Database(MySqlConnection(), new AttributeMappingSource().GetModel(typeof(SqlTable.Product)));
SqlSet set = db.Table <SqlTable.Product>()
.Where("UnitsInStock > 0")
.Skip(0)
.Take(5);
SqlBuilder expected = SQL
.SELECT(db.QuoteIdentifier("Id"))
.FROM(db.QuoteIdentifier("Product"))
.WHERE("UnitsInStock > 0")
.LIMIT(5)
.OFFSET(0);
Assert.IsTrue(SqlEquals(set, expected));
}
public void GetEnumerator_ReturnsEnumerator()
{
var values = new SqlSet
{
new SqlAssign("Name", "John Doe"),
new SqlAssign("Email", "*****@*****.**")
};
var enumerator = ((IEnumerable)values).GetEnumerator();
Assert.NotNull(enumerator);
int count = 0;
while (enumerator.MoveNext())
{
count++;
}
Assert.Equal(2, count);
}
public void Dont_Use_Subqueries_When_Methods_Are_Called_In_Order()
{
Database db = MySqlDatabase();
SqlSet set = db.Table <SqlTable.Model1.Product>()
.Where("UnitsInStock > 0")
.Skip(0)
.Take(5);
SqlBuilder expected = SQL
.SELECT(db.QuoteIdentifier("Id"))
.FROM(db.QuoteIdentifier("Product"))
.WHERE("UnitsInStock > 0")
.LIMIT(5)
.OFFSET(0);
Assert.IsTrue(SqlEquals(set, expected));
}
public void Where_Take_Select()
{
SqlSet set = db.From("products")
.Where("UnitsInStock > 0")
.Take(5)
.Select("ProductName");
SqlBuilder expected = SQL
.SELECT("ProductName")
.FROM(SQL
.SELECT("ROWNUM AS dbex_rn, __rn.*")
.FROM(SQL
.SELECT("*")
.FROM("products"), "__rn")
.WHERE("UnitsInStock > 0"), "_")
.WHERE("dbex_rn BETWEEN {0} AND {1}", 1, 5)
.ORDER_BY("dbex_rn");
Assert.IsTrue(SqlEquals(set, expected));
}
public void GetEnumerator_ReturnsEnumerator()
{
var values = new SqlSet
{
new SqlAssign("Name", "John Doe"),
new SqlAssign("Email", "*****@*****.**")
};
var enumerator = ((IEnumerable)values).GetEnumerator();
Assert.NotNull(enumerator);
int count = 0;
while (enumerator.MoveNext())
{
count++;
}
Assert.Equal(2, count);
}
bool SqlEquals(SqlSet set, SqlBuilder query)
{
return TestUtil.SqlEquals(set, query);
}
public void Add_WithNullAssign_ThrowsArgumentNull()
{
var set = new SqlSet();
Assert.Throws<ArgumentNullException>(() => set.Add(null));
}
/// <summary>
/// Visits the specified <see cref="SqlSet"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlSet expression)
{
}
public static bool SqlEquals(SqlSet set, SqlBuilder query)
{
return String.Equals(Regex.Replace(set.ToString(), "dbex_set[0-9]+", "_"), query.ToString(), StringComparison.Ordinal);
}
