/// <summary>
/// 依据主键获取表中的数据（不支持复合主键）
/// </summary>
/// <param name="id">主键值</param>
/// <returns></returns>
public T Get(object id)
{
if (id == null)
{
throw new ArgumentNullException("id");
}
if (dbMetaInfo == null)
{
throw new MapperException(string.Format("实体{0}缺少数据库映射信息", typeof(T).FullName));
}
if (dbMetaInfo.KeyCount < 1)
{
throw new MapperException("缺少主键信息");
}
if (dbMetaInfo.KeyCount != 1)
{
throw new MapperException("复合主键的实体对象，无法使用该方法。");
}
var condition = SqlExpression.Equal(dbMetaInfo.GetKeys()[0], id);
using (var read = new Queryable <T>(DbReader).Where(condition).ExecuteReader())
{
return(Map.Single <T>(read));
}
}
private ITable FindViewEntry(ObjectName viewName)
{
var table = Transaction.GetTable(SystemSchema.ViewTableName);
var schemav = table.GetResolvedColumnName(0);
var namev = table.GetResolvedColumnName(1);
using (var session = new SystemSession(Transaction, SystemSchema.Name)) {
using (var query = session.CreateQuery()) {
var t = table.SimpleSelect(query, namev, SqlExpressionType.Equal,
SqlExpression.Constant(DataObject.String(viewName.Name)));
t = t.ExhaustiveSelect(query,
SqlExpression.Equal(SqlExpression.Reference(schemav), SqlExpression.Constant(viewName.ParentName)));
// This should be at most 1 row in size
if (t.RowCount > 1)
{
throw new ArgumentException(String.Format("Multiple view entries for name '{0}' in the system.", viewName));
}
// Return the entries found.
return(t);
}
}
}
private ITable FindEntry(ObjectName routineName)
{
var table = transaction.GetTable(RoutineTableName);
var schemav = table.GetResolvedColumnName(1);
var namev = table.GetResolvedColumnName(2);
using (var session = new SystemSession(transaction)) {
using (var context = session.CreateQuery()) {
var t = table.SimpleSelect(context, namev, SqlExpressionType.Equal,
SqlExpression.Constant(Field.String(routineName.Name)));
t = t.ExhaustiveSelect(context,
SqlExpression.Equal(SqlExpression.Reference(schemav),
SqlExpression.Constant(Field.String(routineName.ParentName))));
// This should be at most 1 row in size
if (t.RowCount > 1)
{
throw new Exception("Assert failed: multiple procedure names for " + routineName);
}
// Return the entries found.
return(t);
}
}
}
public void ToSql_WithParameterCallback_WithSqlUnion_InvokesCallback()
{
int parameterInvoked = 0;
Sql
.Union(
Sql.Select("Name").From("User").Where(SqlExpression.Equal("Id", new SqlParameter("Id", 42))),
Sql.Select("Name").From("Profile").Where(SqlExpression.GreaterThan("Age", new SqlParameter("Age", 18))))
.ToSql(p =>
{
parameterInvoked++;
switch (p.ParameterName)
{
case "Id":
Assert.Equal(42, p.Value);
break;
case "Age":
Assert.Equal(18, p.Value);
break;
default:
Assert.True(false, "Encountered an unexpected SQL parameter.");
break;
}
});
Assert.Equal(2, parameterInvoked);
}
public bool DropTrigger(ObjectName triggerName)
{
if (triggerName == null)
{
throw new ArgumentNullException("triggerName");
}
var table = transaction.GetMutableTable(TriggerTableName);
var schemaName = triggerName.ParentName;
var name = triggerName.Name;
var schemaCol = table.GetResolvedColumnName(0);
var nameCol = table.GetResolvedColumnName(1);
using (var session = new SystemSession(transaction)) {
using (var query = session.CreateQuery()) {
var t = table.SimpleSelect(query, nameCol, SqlExpressionType.Equal, SqlExpression.Constant(name));
t = t.ExhaustiveSelect(query,
SqlExpression.Equal(SqlExpression.Reference(schemaCol), SqlExpression.Constant(schemaName)));
if (t.RowCount == 0)
{
return(false);
}
table.Delete(t);
transaction.OnObjectDropped(DbObjectType.Trigger, triggerName);
return(true);
}
}
}
public bool TypeExists(ObjectName typeName)
{
if (typesCache.ContainsKey(typeName))
{
return(true);
}
var table = Transaction.GetTable(TypeTableName);
var schemaName = typeName.ParentName;
var name = typeName.Name;
var schemaCol = table.GetResolvedColumnName(1);
var nameCol = table.GetResolvedColumnName(2);
using (var session = new SystemSession(Transaction)) {
using (var query = session.CreateQuery()) {
var t = table.SimpleSelect(query, schemaCol, SqlExpressionType.Equal, SqlExpression.Constant(schemaName));
t = t.ExhaustiveSelect(query, SqlExpression.Equal(SqlExpression.Reference(nameCol), SqlExpression.Constant(name)));
if (t.RowCount > 1)
{
throw new InvalidOperationException(String.Format("Name '{0}' resolves to multiple types.", typeName));
}
return(t.RowCount == 1);
}
}
}
public void Join_WithMultipleJoins_AddsAllJoins()
{
var users = new SqlTable("dbo.Users", "u");
var profiles = new SqlTable("dbo.Profiles", "p");
var addresses = new SqlTable("dbo.Addresses", "a");
var profilesPredicate = SqlExpression.Equal(users + "Id", profiles + "UserId");
var addressesPredicate = SqlExpression.Equal(users + "Id", addresses + "UserId");
var select = Sql.Select("Id", "Name")
.From(users)
.LeftJoin(profiles).On(profilesPredicate)
.LeftJoin(addresses).On(addressesPredicate)
.Go();
Assert.Equal(2, select.From.Joins.Count());
var join = select.From.Joins.First();
Assert.Same(profiles, join.Table);
Assert.NotNull(join.On);
Assert.NotNull(join.On.Predicate);
Assert.Same(profilesPredicate, join.On.Predicate);
Assert.Equal(SqlJoinType.Left, join.Type);
join = select.From.Joins.Last();
Assert.Same(addresses, join.Table);
Assert.NotNull(join.On);
Assert.NotNull(join.On.Predicate);
Assert.Same(addressesPredicate, join.On.Predicate);
Assert.Equal(SqlJoinType.Left, join.Type);
}
public void Equal_WithNullLeftOperand_ThrowsArgumentNull()
{
var right = new SqlConstant(5);
var ex = Assert.Throws <ArgumentNullException>(() => SqlExpression.Equal(null, right));
Assert.Equal("left", ex.ParamName);
}
public void SimpleCursorForLoop()
{
var query = (SqlQueryExpression)SqlExpression.Parse("SELECT * FROM table1");
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareCursorStatement("c1", query));
var loop = new CursorForLoopStatement("i", "c1");
loop.Statements.Add(new DeclareVariableStatement("a", PrimitiveTypes.String()));
loop.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"),
SqlExpression.FunctionCall("cast",
new SqlExpression[] { SqlExpression.VariableReference("i"), SqlExpression.Constant("varchar") })));
loop.Statements.Add(
new ConditionStatement(SqlExpression.Equal(SqlExpression.VariableReference("i"), SqlExpression.Constant(50)),
new SqlStatement[] { new ReturnStatement(SqlExpression.VariableReference("a")) }));
block.Statements.Add(new OpenStatement("c1"));
block.Statements.Add(loop);
var result = AdminQuery.ExecuteStatement(block);
Assert.IsNotNull(result);
Assert.AreEqual(StatementResultType.Result, result.Type);
var value = result.Result.GetValue(0, 0);
Assert.IsNotNull(value);
Assert.IsFalse(Field.IsNullField(value));
}
public void ExpressionType_ReturnsJoin()
{
var table = new SqlTable("dbo.Users", "u");
var predicate = SqlExpression.Equal("u.Id", 5);
var expression = new SqlJoin(SqlJoinType.Left, table);
Assert.Equal(SqlExpressionType.Join, expression.ExpressionType);
}
private static Privileges QueryPrivileges(IQuery queryContext, ITable grantTable, string grantee,
DbObjectType objectType, ObjectName objectName, bool withOption, bool withPublic)
{
var objectCol = grantTable.GetResolvedColumnName(1);
var paramCol = grantTable.GetResolvedColumnName(2);
var granteeCol = grantTable.GetResolvedColumnName(3);
var grantOptionCol = grantTable.GetResolvedColumnName(4);
var granterCol = grantTable.GetResolvedColumnName(5);
ITable t1 = grantTable;
// All that match the given object parameter
// It's most likely this will reduce the search by the most so we do
// it first.
t1 = t1.SimpleSelect(queryContext, paramCol, SqlExpressionType.Equal,
SqlExpression.Constant(Field.String(objectName.FullName)));
// The next is a single exhaustive select through the remaining records.
// It finds all grants that match either public or the grantee is the
// user or role, and that match the object type.
// Expression: ("grantee_col" = grantee OR "grantee_col" = 'public')
var granteeCheck = SqlExpression.Equal(SqlExpression.Reference(granteeCol),
SqlExpression.Constant(Field.String(grantee)));
if (withPublic)
{
granteeCheck = SqlExpression.Or(granteeCheck, SqlExpression.Equal(SqlExpression.Reference(granteeCol),
SqlExpression.Constant(Field.String(User.PublicName))));
}
// Expression: ("object_col" = object AND
// ("grantee_col" = grantee OR "grantee_col" = 'public'))
// All that match the given grantee or public and given object
var expr = SqlExpression.And(SqlExpression.Equal(SqlExpression.Reference(objectCol),
SqlExpression.Constant(Field.BigInt((int)objectType))), granteeCheck);
// Are we only searching for grant options?
if (withOption)
{
var grantOptionCheck = SqlExpression.Equal(SqlExpression.Reference(grantOptionCol),
SqlExpression.Constant(Field.BooleanTrue));
expr = SqlExpression.And(expr, grantOptionCheck);
}
t1 = t1.ExhaustiveSelect(queryContext, expr);
// For each grant, merge with the resultant priv object
Privileges privs = Privileges.None;
foreach (var row in t1)
{
var priv = (int)row.GetValue(0).AsBigInt();
privs |= (Privileges)priv;
}
return(privs);
}
public void RightJoin_WithNullTable_ThrowsArgumentNull()
{
var users = new SqlTable("dbo.Users", "u");
var profiles = new SqlTable("dbo.Profiles", "p");
var select = Sql.Select("Id", "Name")
.From(users);
Assert.Throws <ArgumentNullException>(() => select.RightJoin(null).On(SqlExpression.Equal(users + "Id", profiles + "UserId")));
}
public void ViewFromBuilder()
{
var viewName = ObjectName.Parse("APP.text_view1");
AdminQuery.CreateView(viewName, query => query
.AllColumns()
.From(from => from.Table("test_table"))
.Where(SqlExpression.Equal(SqlExpression.Reference(new ObjectName("a")), SqlExpression.Constant(1))));
}
public static void Delete(this DbConnectionProvider connectionProvider, IEnumerable <object> datas)
{
if (connectionProvider == null)
{
throw new ArgumentNullException(nameof(connectionProvider));
}
if (datas == null)
{
throw new ArgumentNullException(nameof(datas));
}
Type type;
using (var e = datas.GetEnumerator())
{
if (!e.MoveNext())
{
return;
}
type = e.Current.GetType();
}
var schema = EntitySchemaManager.GetSchema(type);
if (schema == null)
{
throw new InvalidConstraintException($"EntityType:{type} schema not found.");
}
if (schema.AccessLevel == AccessLevel.ReadOnly)
{
throw new ArgumentException($"Type:[{schema.EntityType}] read only");
}
var validColumn = schema.Columns;
var keyColumn = validColumn.FirstOrDefault(p => p.IsPrimary);
if (keyColumn == null)
{
throw new ArgumentException($"Type:[{schema.EntityType}] not found parimary");
}
var dbCommand = connectionProvider.CreateCommand(schema.Name, DbCommandMode.Delete);
foreach (var data in datas)
{
var value = keyColumn.GetValue(data);
if (value == null)
{
throw new InvalidOperationException($"schema:{schema.EntityType} primary:{keyColumn.Name} should not be null");
}
var parameter = connectionProvider.CreateParameter($"@{keyColumn.Name}", value);
dbCommand.AddParameter(parameter);
dbCommand.Condition = SqlExpression.Equal(SqlExpression.Member(keyColumn.Name), SqlExpression.Paramter(keyColumn.Name));
var commandText = dbCommand.CommandText;
connectionProvider.ExecuteNonQuery(commandText, parameters: dbCommand.Parameters.ToArray());
dbCommand.ClearParameter();
}
}
public void ToCommand_WithSqlGoWithNullConnection_ThrowsArgumentNull()
{
var query = Sql
.Select("Id", "Name", "Email")
.From("Users")
.Where(SqlExpression.Equal("Id", 42));
Assert.Throws <ArgumentNullException>(() => query.ToCommand(null));
}
public void ToSql_WithSelect_WritesCorrectSql()
{
var sql = Sql
.Select("Id", "Name", "Email")
.From("Users")
.Where(SqlExpression.Or(SqlExpression.Equal("Id", 5), SqlExpression.GreaterThan("Age", "Age" + (SqlConstant)30)))
.ToSql();
Assert.AreEqual("SELECT [Id], [Name], [Email] FROM [Users] WHERE (([Id] = 5) OR ([Age] > @Age))", sql);
}
public void SimpleBinary()
{
var eq = SqlExpression.Equal(SqlExpression.Reference(new ObjectName("a")), SqlExpression.Constant(455));
SerializeAndAssert(eq, (serialized, deserialized) => {
Assert.AreEqual(SqlExpressionType.Equal, deserialized.ExpressionType);
Assert.IsInstanceOf <SqlReferenceExpression>(eq.Left);
Assert.IsInstanceOf <SqlConstantExpression>(eq.Right);
});
}
public void ToSql_WithSelectAndSimpleWhereClause_WritesCorrectSql()
{
var sql = Sql
.Select("Id", "Name", "Email")
.From("Users")
.Where(SqlExpression.Equal("Id", 5))
.ToSql();
Assert.AreEqual("SELECT [Id], [Name], [Email] FROM [Users] WHERE ([Id] = 5)", sql);
}
public void ToSql_WithDelete_WritesCorrectSql()
{
var sql = Sql
.Delete()
.From("Users")
.Where(SqlExpression.Or(SqlExpression.Equal("Id", 5), SqlExpression.GreaterThan("Age", "Age" + (SqlConstant)30)))
.ToSql();
Assert.AreEqual("DELETE FROM [Users] WHERE (([Id] = 5) OR ([Age] > @Age))", sql);
}
public void VisitExpression_WithSqlOn_DispatchesVisit()
{
var fixture = new Fixture().Customize(new AutoMoqCustomization());
var mock = fixture.Freeze <Mock <SqlVisitor> >();
var visitor = mock.Object;
visitor.Visit((SqlExpression) new SqlOn(SqlExpression.Equal("Id", 5)));
mock.Verify(_ => _.Visit(It.IsAny <SqlExpression>()), Times.Once);
mock.Verify(_ => _.Visit(It.IsAny <SqlOn>()), Times.Once);
}
public void Equal_WithNullRightOperand_ReturnsBinaryExpression()
{
var left = new SqlColumn("dbo.Users.Id");
var expression = SqlExpression.Equal(left, null);
Assert.NotNull(expression.Left);
Assert.Same(left, expression.Left);
Assert.Equal(SqlBinaryOperator.Equal, expression.Operator);
Assert.NotNull(expression.Right);
Assert.Same(SqlConstant.Null, expression.Right);
}
public void ToSql_WithMySqlDialect_WithSqlUnion_ReturnsSql()
{
const string expected = "(SELECT `Id`, `Name` FROM `User` WHERE `Id` = ?UserId) UNION (SELECT `Id`, `Name` FROM `UserGroup`)";
var actual = Sql
.Union(
Sql.Select("Id", "Name").From("User").Where(SqlExpression.Equal("Id", new SqlParameter("UserId", 5))),
Sql.Select("Id", "Name").From("UserGroup"))
.ToSql(new MySqlDialect());
Assert.Equal(expected, actual);
}
public void ToSql_WithUpdate_WritesCorrectSql()
{
var sql = Sql
.Update("Users")
.Set("Age", 30)
.Set("Name", "Name" + (SqlConstant)"Wouter")
.Where(SqlExpression.Or(SqlExpression.Equal("Id", 5), SqlExpression.GreaterThan("Age", "Age" + (SqlConstant)30)))
.ToSql();
Assert.AreEqual("UPDATE [Users] SET [Age] = 30, [Name] = @Name WHERE (([Id] = 5) OR ([Age] > @Age))", sql);
}
public void ToSql_WithMySqlDialect_WithSqlSelectFromSubQuery_ReturnsSql()
{
const string expected = "SELECT `Id`, `Name` FROM (SELECT `Id`, `Name` FROM `User`) `a` WHERE `Id` = 42";
var actual = Sql
.Select("Id", "Name")
.From(new SqlSubquery(Sql.Select("Id", "Name").From("User"), "a"))
.Where(SqlExpression.Equal("Id", 42))
.ToSql(new MySqlDialect());
Assert.Equal(expected, actual);
}
public void ToSql_WithSqlUnion_ReturnsSql()
{
const string expected = "(SELECT [Id], [Name] FROM [User] WHERE [Id] = @UserId) UNION (SELECT [Id], [Name] FROM [UserGroup])";
var actual = Sql
.Union(
Sql.Select("Id", "Name").From("User").Where(SqlExpression.Equal("Id", new SqlParameter("UserId", 5))),
Sql.Select("Id", "Name").From("UserGroup"))
.ToSql();
Assert.Equal(expected, actual);
}
public void ToSql_WithSqlSelectFromSubQuery_ReturnsSql()
{
const string expected = "SELECT [Id], [Name] FROM (SELECT [Id], [Name] FROM [User]) [a] WHERE [Id] = 42";
var actual = Sql
.Select("Id", "Name")
.From(new SqlSubquery(Sql.Select("Id", "Name").From("User"), "a"))
.Where(SqlExpression.Equal("Id", 42))
.ToSql();
Assert.Equal(expected, actual);
}
public void ToCommand_WithSqlUpdateWithNullConnection_ThrowsArgumentNull()
{
var query = Sql
.Update("Users")
.Set("FirstName", "John")
.Set("LastName", "Foobar")
.Where(SqlExpression.Equal("Id", 42))
.Go();
Assert.Throws <ArgumentNullException>(() => query.ToCommand(null));
}
public void ToSql_WithUnionWithJoinClause_WritesCorrectSql()
{
var u = new SqlTable("Users", "u");
var p = new SqlTable("Profiles", "p");
var query = Sql.Union(
Sql.Select(u + "Id", u + "Name", p + "Age").From(u).InnerJoin(p, SqlExpression.Equal(u + "Id", p + "UserId")),
Sql.Select(u + "Id", u + "Name", p + "Age").From(u).InnerJoin(p, SqlExpression.Equal(u + "Id", p + "UserId")).Where(SqlExpression.GreaterThanOrEqual(p + "Age", 30)))
.ToSql();
Assert.AreEqual("(SELECT [u].[Id], [u].[Name], [p].[Age] FROM [Users] [u] INNER JOIN [Profiles] [p] ON ([u].[Id] = [p].[UserId])) UNION (SELECT [u].[Id], [u].[Name], [p].[Age] FROM [Users] [u] INNER JOIN [Profiles] [p] ON ([u].[Id] = [p].[UserId]) WHERE ([p].[Age] >= 30))", query);
}
public void ToSql_WithSqlSelectWithParameter_ReturnsSql()
{
const string expected = "SELECT [Id], [Name] FROM [User] WHERE [Id] = @Id";
var actual = Sql
.Select("Id", "Name")
.From("User")
.Where(SqlExpression.Equal("Id", "Id" + (SqlConstant)42))
.Go()
.ToSql();
Assert.Equal(expected, actual);
}
public void ToSql_WithSqlUpdate_ReturnsSql()
{
const string expected = "UPDATE [User] SET [Name] = 'John Doe' WHERE [Id] = 5";
var actual = Sql
.Update("User")
.Set("Name", "John Doe")
.Where(SqlExpression.Equal("Id", 5))
.Go()
.ToSql();
Assert.Equal(expected, actual);
}
