/// <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));
            }
        }
Exemple #2
0
        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);
        }
Exemple #5
0
        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);
                }
            }
        }
Exemple #6
0
        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);
                }
            }
        }
Exemple #7
0
        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);
        }
Exemple #8
0
        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));
        }
Exemple #10
0
        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);
        }
Exemple #12
0
        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))));
        }
Exemple #14
0
        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();
            }
        }
Exemple #15
0
        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);
        }
Exemple #20
0
        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);
        }
Exemple #21
0
        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);
        }
Exemple #22
0
        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);
        }
Exemple #24
0
        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);
        }
Exemple #27
0
        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);
        }