1. George Ribeiro
  2. FluentSql

Commits

George Ribeiro  committed ab87bc4

criada cláusula Distinct para ITable e ICommand.

  • Participants
  • Parent commits 83b2501
  • Branches default

Comments (0)

Files changed (12)

File FluentSql.Test/ComplexQuery.cs

View file
  • Ignore whitespace
 using FluentSql.Test.Entities;
 using System.Collections;
 using FluentSql.Aggregates;
+using FluentSql.Exceptions;
 
 namespace FluentSql.Test
 {
             users.Project(users.All, groups.All)
                 .InnerJoin(groups).On(groups["id"] == users["group_id"])
                 .Where(users["id"] == 1 & users["nome"] != "george");
-            string sql_expected = "SELECT u.*, g.* FROM users AS u INNER JOIN groups AS g ON g.id = u.group_id WHERE (u.id = @users_id_1) AND (u.nome <> @users_nome_1)";
+            string sql_expected = "SELECT u.*, g.* FROM users AS u INNER JOIN groups AS g ON g.id = u.group_id "+
+                "WHERE (u.id = @users_id_1) AND (u.nome <> @users_nome_1)";
             Assert.AreEqual(sql_expected, users.ToSql());
         }
         
         }
 
         [Test]
+        public void Consulta_Com_Subselect_No_Where()
+        {
+            var t_users = new Table("tbUsers");
+            var t_groups = new Table("tbGroups");
+            t_users.Project(t_users.All)
+                .Where(t_users["status"] == t_groups.Project(t_groups["status"]).Where(t_groups["id"] == 1));
+            string sql_expected = "SELECT tbUsers.* FROM tbUsers WHERE tbUsers.status = "
+                +"(SELECT tbGroups.status FROM tbGroups WHERE tbGroups.id = @tbGroups_id_1)";
+            Assert.AreEqual(sql_expected, t_users.ToSql());
+        }
+
+        [Test]
         public void Usar_Function_Count()
         {
             var users = new Table("users");
             var produtos = new Table("produtos");
             produtos.Project(F.Max(produtos["preco"]).As("preco_produto"))
                 .Project(produtos["descricao"])
-                .Where(produtos["descricao"] != null);
+                .Where(produtos["descricao"] != null)
+                .GroupBy(produtos["descricao"]);
             string sql_expected = "SELECT MAX(produtos.preco) AS preco_produto, produtos.descricao FROM produtos "
-                +"WHERE produtos.descricao IS NOT NULL";
+                +"WHERE produtos.descricao IS NOT NULL GROUP BY produtos.descricao";
             Assert.AreEqual(sql_expected, produtos.ToSql());
         }
 
         }
 
         [Test]
+        public void Usar_Top_Negativo_Deve_Retornar_Excecao_InvalidClauseException()
+        {
+            try
+            {
+                var users = new Table("users");
+                users.Project(users.All).OrderBy(users["nome"].Desc)
+                    .Top(-20);
+                Assert.Fail();
+            }
+            catch (InvalidClauseException)
+            {
+                Assert.True(true);
+            }
+        }
+
+        [Test]
         public void Usar_Subselect_Na_Consulta()
         {
             ITable users = new Table("users");
             var users = new Table("users");
             users.Project(users["name"], users["password"])
                 .Where(users["active"] == true)
-                .OrderBy(users["created_at"].Desc);
+                .OrderBy(users["created_at"].Desc, users["name"]);
             string sql_expected = "SELECT users.name, users.password FROM users WHERE users.active = @users_active_1 "
-                +"ORDER BY users.created_at DESC";
+                +"ORDER BY users.created_at DESC, users.name";
+            Assert.AreEqual(sql_expected, users.ToSql());
+        }
+
+        [Test]
+        public void Consulta_Zerando_Project_Com_Null()
+        {
+            var users = new Table("users");
+            users.Project(users["name"])
+                .Where(users["name"].Like("%n"));
+            string sql1 = "SELECT users.name FROM users WHERE users.name LIKE @users_name_1";
+            Assert.AreEqual(sql1, users.ToSql());
+            users.Project(null);
+            string sql2 = "SELECT * FROM users WHERE users.name LIKE @users_name_1";
+            Assert.AreEqual(sql2, users.ToSql());
+        }
+
+        [Test]
+        public void Consulta_Where_E_Zerando_Where_Com_Null()
+        {
+            var users = new Table("users");
+            users.Project(users["name"])
+                .Where(users["name"].Like("%n"));
+            string sql1 = "SELECT users.name FROM users WHERE users.name LIKE @users_name_1";
+            Assert.AreEqual(sql1, users.ToSql());
+            users.Where(null).Top(10);
+            string sql2 = "SELECT TOP 10 users.name FROM users";
+            Assert.AreEqual(sql2, users.ToSql());
+        }
+
+        [Test]
+        public void Consulta_Simples_Com_Disctinct()
+        {
+            var users = new Table("users");
+            users.Project(users["nascimento"])
+                .Where(users["nome"].Like("%g"))
+                .Distinct();
+            string sql_expected = "SELECT DISTINCT users.nascimento FROM users WHERE users.nome LIKE @users_nome_1";
             Assert.AreEqual(sql_expected, users.ToSql());
         }
 

File FluentSql/Command/Delete.cs

View file
  • Ignore whitespace
             return this;
         }
 
+        public ICommand OrderBy(params IOrder[] order)
+        {
+            throw new NotSupportedException("Clause don't supported by command.");
+        }
+
         public ICommand GroupBy(params IGroup[] groups)
         {
             throw new NotSupportedException("Clause don't supported by command.");
             throw new NotSupportedException("Clause don't supported by command.");
         }
 
+        public ICommand Distinct()
+        {
+            throw new NotSupportedException("Clause don't supported by command.");
+        }
+
         #endregion
 
         #region Build Members
             return string.Empty;
         }
         #endregion
-
-        #region ICommand Members
-
-
-        public ICommand OrderBy(params IOrder[] order)
-        {
-            throw new NotSupportedException("Clause don't supported by command.");
-        }
-
-        #endregion
     }
 }

File FluentSql/Command/ICommand.cs

View file
  • Ignore whitespace
         ICommand Count();
         ICommand Values(object values);
         ICommand Top(int number);
+        ICommand Distinct();
     }
 }

File FluentSql/Command/Insert.cs

View file
  • Ignore whitespace
             throw new NotSupportedException("Clause don't supported by command.");
         }
 
+        public ICommand Distinct()
+        {
+            throw new NotSupportedException("Clause don't supported by command.");
+        }
+
         #endregion
 
         #region ICommand Members

File FluentSql/Command/Select.cs

View file
  • Ignore whitespace
 using FluentSql.Expressions;
 using System.Collections;
 using FluentSql.Aggregates;
+using FluentSql.Exceptions;
 
 namespace FluentSql.Command
 {
             this.GroupBys = new List<IGroup>();
             this.Havings = new List<IExpression>();
             this._Count = false;
-            this._Top = 0;
+            this._Top = null;
+            this._Distinct = false;
         }
 
         #region ICommand Members
         public IList<IExpression> Havings { get; set; }
         public ITable Table { get; set; }
         public bool _Count { get; set; }
-        public int _Top { get; set; }
+        public int? _Top { get; set; }
+        public bool _Distinct { get; set; }
         
         public string ToSql()
         {
-            return String.Format("SELECT {0}{1} {2}{3}{4}{5}{6}{7}", BuildTop(), BuildProject(), 
+            return String.Format("SELECT {0}{1} {2}{3}{4}{5}{6}{7}", BuildTopOrDistinct(), BuildProject(), 
                 BuildFrom(), BuildJoin(), BuildWhere(), BuildOrderBy(), BuildGroupBy(), BuildHaving());
         }
 
         public ICommand Project(params IProject[] projects)
         {
+            if (projects == null)
+            {
+                Projects.Clear();
+                return this;
+            }
             foreach (var p in projects)
             {
                 Projects.Add(p);
 
         public ICommand Where(IExpression expression)
         {
+            if (expression == null)
+            {
+                Wheres.Clear();
+                return this;
+            }
             Wheres.Add(expression);
             return this;
         }
 
+        public ICommand OrderBy(params IOrder[] orders)
+        {
+            if (orders == null)
+            {
+                Orders.Clear();
+                return this;
+            }
+            foreach (IOrder order in orders)
+            {
+                Orders.Add(order);
+            }
+            return this;
+        }
+
         public ICommand GroupBy(params IGroup[] groups)
         {
+            if (groups == null)
+            {
+                GroupBys.Clear();
+                return this;
+            }
             foreach (IGroup g in groups)
             {
                 GroupBys.Add(g);    
 
         public ICommand Having(IExpression expression)
         {
+            if (expression == null)
+            {
+                Havings.Clear();
+                return this;
+            }
             Havings.Add(expression);
             return this;
         }
 
         public ICommand Top(int number)
         {
+            if (number < 0)
+            {
+                throw new InvalidClauseException("Number invalid for clause top.");
+            }
             this._Top = number;
+            this._Distinct = false;
+            return this;
+        }
+
+        public ICommand Distinct()
+        {
+            this._Distinct = true;
+            this._Top = 0;
             return this;
         }
 
         {
             if (Orders.Count > 0)
             {
-                return " ORDER BY " + string.Join(" AND ", (from o in Orders select o.AsOrder()).ToArray());
+                return " ORDER BY " + string.Join(", ", (from o in Orders select o.AsOrder()).ToArray());
             }
             return string.Empty;
         }
             return string.Empty;
         }
 
-        public string BuildTop()
+        public string BuildTopOrDistinct()
         {
-            if (_Top > 0)
+            if (this._Distinct)
+            {
+                return "DISTINCT "; 
+            }
+            if (_Top.HasValue)
             {
                 return string.Format("TOP {0} ", _Top);
             }
             return string.Empty;
         }
         #endregion
-
-        #region ICommand Members
-
-        public ICommand OrderBy(params IOrder[] orders)
-        {
-            foreach (IOrder order in orders)
-            {
-                Orders.Add(order);
-            }
-            return this;
-        }
-
-        #endregion
     }
 }

File FluentSql/Command/Update.cs

View file
  • Ignore whitespace
             return this;
         }
 
+        public ICommand OrderBy(params IOrder[] order)
+        {
+            throw new NotSupportedException("Clause don't supported by command.");
+        }
+
         public ICommand Values(object values)
         {
             IDictionary<string, object> keyvalue = Utils.ObjectToDicionary(values);
             throw new NotSupportedException("Clause don't supported by command.");
         }
 
+        public ICommand Distinct()
+        {
+            throw new NotSupportedException("Clause don't supported by command.");
+        }
+
         public IList<FluentSql.Expressions.IExpression> Wheres { get; set; }
 
         public IDictionary<string, object> FieldValues { get; set; }
         {
             return String.Format("UPDATE {0} SET {1}{2}", this.Table.Name, BuildValues(), BuildWhere());
         }
-
-        #region ICommand Members
-
-
-        public ICommand OrderBy(params IOrder[] order)
-        {
-            throw new NotSupportedException("Clause don't supported by command.");
-        }
-
-        #endregion
     }
 }

File FluentSql/Exceptions/FluentSqlException.cs

View file
  • Ignore whitespace
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+
+namespace FluentSql.Exceptions
+{
+    public class FluentSqlException : Exception
+    {
+        public FluentSqlException(string message) : base(message) { }
+    }
+}

File FluentSql/Exceptions/InvalidClauseException.cs

View file
  • Ignore whitespace
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+
+namespace FluentSql.Exceptions
+{
+    public class InvalidClauseException : FluentSqlException
+    {
+        public InvalidClauseException(string message) : base(message) { }
+    }
+}

File FluentSql/Expressions/Operator.cs

View file
  • Ignore whitespace
             {
                 if (two is ITable)
                 {
-                    Two = ((ITable)two).ToSql();
+                    Two = String.Format("({0})", ((ITable)two).ToSql());
                 }
                 else
                 {

File FluentSql/FluentSql.csproj

View file
  • Ignore whitespace
     <Compile Include="Command\Delete.cs" />
     <Compile Include="Command\Select.cs" />
     <Compile Include="Command\Update.cs" />
+    <Compile Include="Exceptions\FluentSqlException.cs" />
+    <Compile Include="Exceptions\InvalidClauseException.cs" />
     <Compile Include="Expressions\GreaterThan.cs" />
     <Compile Include="Expressions\GreaterThanOrEqualTo.cs" />
     <Compile Include="Expressions\IExpression.cs" />

File FluentSql/ITable.cs

View file
  • Ignore whitespace
         ITable Insert(object values);
         ITable Update(object values);
         ITable Delete();
+        ITable Distinct();
     }
 }

File FluentSql/Table.cs

View file
  • Ignore whitespace
 
         public IJoin Join(ITable table)
         {
-            IJoin j = _command.Join(table);
-            return j;
+            return _command.Join(table);
         }
 
         public IJoin LeftJoin(ITable table)
         {
-            IJoin j = _command.LeftJoin(table);
-            return j;
+            return _command.LeftJoin(table);
         }
 
         public IJoin RightJoin(ITable table)
             return this;
         }
 
+        public ITable Distinct()
+        {
+            _command.Distinct();
+            return this;
+        }
+
         public ITable Insert(object values)
         {
             _command = new Insert(this);