Generación de Sql – ToleSql

El panorama actual

Vuelve a estar de moda lanzar Sql contra nuestras bases de datos, bajo mi punto de vista la culpa de esto es, en gran parte, por la resaca que nos ha dejado la fiesta de Entity Framework que ha durado mucho tiempo, y también por el hype de StackOverflow con Dapper.
Parece ser que una vez que hemos usado tanto el ORM de Microsoft hemos empezado a entender lo que es un ORM y la gente está empezando a ver que no se trata de ORM sí u ORM no, sino que se pueden mezclar ambas cosas y que para ciertas cosas es más viable la ejecución de consultas con SQL.
No quiero dejar de nombrar NHibernate aquí, ORM que, para mí, es bastante mejor que EF. Pero parece que aunque Microsoft se abra y de un aire open source a .NET, los desarrolladores de .NET somos dados a cerrarnos a los productos que nos brinda dicha empresa.

Anyway, los desarrolladores por fin vamos aprendiendo que hay que coger lo mejor de cada cosa, así pues reconocemos las bonanzas de un ORM y los usamos para lo que mejor saben hacer. Pero cuando lo que queremos hacer es una consulta a nuestra base de datos optamos por lanzar un SQL por la conexión de nuestro dbContext y mapearla a un DTO.

Lo que más me gusta de un ORM es poder trabajar directamente con los objetos de mi dominio, cuando cambio un campo o cuando cambio una clase de mi dominio obtengo los errores en tiempo de compilación. Esa es la parte que menos me gusta de lanzar SQL, no detectas los fallos hasta que no ejecutas la aplicación. Además de que si trabajas con DataTables y DataRows el acceso a los campos no es tipado.

Esta situación es lo que me ha llevado a querer generar Sql a partir de objetos de mi dominio. Donde escribo consultas usando expresiones y genero Sql en texto que puedo mandar a mi base de datos.

También existen otras formas de comunicación con la base de datos donde se pueden tipar las consultas, como es LinQ To Sql. Personalmente no lo he usado mucho, pero lo que he probado no me gusta mucho porque me da la sensación de estar atado a un modelo de datos generado y a los diseñadores de Visual Studio. Lo que me gustaría es poder atacar a una base de datos con unos objetos POCO y libres que pueden o no contener propiedades para todos los campos de mi base de datos.

Esto viene de lejos

Hace ya mucho tiempo que intenté crear mi primer generador de SQL, eran tiempos de .NET 1.1 o 2.0, recuerdo que no habían expresiones ni lambdas, si existían no las conocía. En aquellos entonces trataba de construir algo con lo que pudiera generar SQL para distintas bases de datos. En mi trabajo usábamos AS400/DB2 y empezábamos a movernos a SQLServer, habían cosas que cambiaban y quería poder construir SQL que pudiera lanzar a una u otra base de datos.
Era un generador donde ibas añadiendo cada cláusula o cada parte de una cláusula de forma muy tosca, algo así:

    builder.AddWhere("NAME", Operator.Like, "Javier%").StartGroup().AddWhere("SURNAME", Operator.Like, "blabla").AddWhereOr("OTROCAMPO", Operator.Equal, "VALOR").EndGroup()

Para los wheres indicabas campo operador y valor, para los joins indicabas la tabla y un where para el on, cuando usabas or tenías que tener cuidado con los paréntesis y hacías cosas como StartGroup y EndGroup … Era todo muy cuadriculado y cuando te ponías a sustituir SQL existente por código para generarlo todo era una patata, jeje. Tener una forma tan estructurada te limitaba mucho, yo siempre hablo de la plasticidad del SQL.

La plasticidad de SQL

En SQL se hace referencia tablas y columnas de nuestra base de datos. Una base de datos tiene tablas, las tablas tienen columnas y las columnas son de un tipo. Esto nos puede llevar a construir un generador de SQL que se base en esta estructura.
Por ejemplo una consulta tiene una tabla principal, luego hago joins con otras tablas usando una condición para el on donde digo como se unen las tablas. Luego tengo los wheres que no son más que comparar campos con valores mediantes operadores de relación y operadores lógicos como and y or.
Pero no, esto es así en principio, pero luego miramos los selects que tiramos y no son así.
Una consulta no tiene porque tener una tabla principal, podemos hacer un select de otro select y no de una tabla. Podemos también hacer un join con otro select y no con una tabla, también podemos incluir sub consultas dentro del where. Podemos seleccionar columnas de nuestras tablas, pero también podemos tener columnas que no son de ninguna tabla, sino que son un cálculo de columnas de distintas tablas, incluso valores fijos según el valor de una expresión con columnas de distintas tablas.

El SQL tiene una alta plasticidad. Y esa plasticidad nos rompe nuestro builder. Podemos empezar a escribir nuestra consulta con nuestro builder y conforme vayamos avanzando podemos darnos con algo que no soporta nuestro builder, por ejemplo una columna con Case When. Llegados a este punto nos damos cuenta de que no podemos usar el builder y reescribimos todo en SQL para poder añadir esa columna… ¿No es esto una lástima? Que hayamos currado en un builder, tengamos toda la consulta construida y por una sola columna tengamos que dejar de usarlo…

Esto me lleva a pensar que un builder de SQL, por muy avanzado que sea tiene que tener un back door por donde poder escribir SQL. Algo como:

builder.AddSqlRawColumn("case when serie = 'a' then 'Factura legal' case serie = 'b' then 'Factura en negro' end as tipofactura");

Objetivo del builder

En un intento por hacer algo colaborativo, escribí esta issue en GitHub para ver si alguien aportaba cosas, opiniones o ideas. No tuvo mucha aceptación en público aunque sí que por privado estuve hablando con algunas personas al respecto y sacamos buenas conclusiones.

El objetivo del builder es poder construir consultas SQL de forma programática haciendo uso de expresiones de forma que no haya que escribir nombres de campos como literales protegiéndonos así de fallos a la hora de cambios en la base de datos o el modelo.

Pero este builder tiene que tener en cuenta la plasticidad del SQL, así que en todo momento debe poder aceptar expresiones SQL en texto (como ya hemos descrito antes).

¿La panacea?

Bien, te estés tomando este post como te lo estés tomando, ahora es el momento de leer este post de Juan María Hernandez. Personalmente estoy totalmente de acuerdo con Juanma. Sergio León me comentaba “yo escribo las consultas en el SQL Management, luego las pego en Visual Studio y a correr, no me veo traduciendo mi SQL a un builder tipado”.

¿Entonces esto para qué?

Bueno, ¿cómo es que, estando de acuerdo con Sergio León y Juan María Hernández, te pones a escribir un builder de SQL? Considero que el builder tiene un escenario concreto. Personalmente considero que cuando hay que hacer una consulta rara o muy específica, una consulta que nunca cambia lo mejor es escribir SQL, pero hay casos donde el SQL se va construyendo poco a poco.

Yo tengo un escenario particular donde mis clases de acceso a datos tienen un SQL del cual se parte pero que luego puede ir cambiando.
Imaginemos un extracto de una clase como esta:

public class DALBase
{
    protected string SQLWhere { get; set; }
    protected string SQLTablas { get; set; }
    protected string SQLColumnas { get; set; }

    public DALBase(string nombreTabla)
    {
        SQLColumnas = "SELECT ";
        SQLTablas = "FROM " + nombreTabla;
        SQLWhere = "";
    }

    public DataTable GetResult()
    {
        //... 
    }
}

Podríamos tener una clase DALProduct tal que así:

public class DALProduct : DALBase
{
    public DALProduct() : base("Products") { }

    public void FiltrarPorNombre(string nombre)
    {
        if (string.IsNullOrEmpty(SQLWhere))
            SQLWhere += "WHERE ";
        else
            SQLWhere += " AND ";

        SQLWhere += "ProductName = '" + nombre + "'";
    }
}

Lo mismo os parece arcaico pero así hay muchas aplicaciones corriendo, sobre todo aplicaciones muy grandes que se mantienen muchos años y no se han subido al carro de los ORMs.

Bien, este escenario provoca tener fragmentos de SQL repartidos por las clases de acceso a datos, en algunos casos los fragmentos de SQL pueden ser bastante complejos, y no solo añadir al where, también se puede añadir un join e incluso, en algunos casos, se podrían añadir columnas.

¿No existen generadores de SQL como los que buscaba?

Si, si que existen aunque no tanto como los que me esperaba. Por un lado están las contribuciones para dapper como Dapper-Extensions y MicroOrm.Dapper.Repositories que no se me adaptan por ser soluciones intermedias, uno con predicados limitados y otro con un enfoque a repositorio que no me gusta. Por otro lado encontré un proyecto que si que me gustó mucho, es Lambda-sql-builder, este me ayudó mucho a la hora de enfocar mi proyecto puesto que admite poder meterle SQL en raw para cosas no soportadas aunque tiene algunas cosas que no me gusta. La forma de tratar las expressiones no escala puesto que transforma las expresiones en unos nodos propios demasiado estructurados que limitan el uso de expresiones.
Otra cosa que no me ha gustado de algunos es el uso de Attributtes para dar nombres a las tablas o a los campos o a los schemas.

ToleSql

Los ejemplos de código de este post trabajan con una versión anterior de ToleSql, para ver la información más reciente visite la página del proyecto y acceda a su documentación.

El builder que estoy construyendo lo he metido bajo el nombre de proyecto ToleSql, hay muchos proyectos ya y muchos Generator y Builders, así que ultimamente estoy optando por coger nombres propios inventados aunque no indiquen de que va el proyecto.
ToleSql está construido sobre dos capas, una base (SelectBuilder) que admite fragmentos de SQL en texto y otra (ExpressionSelectBuilder) que admite expressiones que son traducidas a SQL.

SelectBuilder

En este caso un framento de código dice más que mil palabras:

[Fact]
public void SelectTableWithColumnsExplicitJoinWhereOrderBy()
{
    SqlConfiguration.SetDialect(new TestDialect());
    var b = new SelectBuilder();

    b.SetMainSourceSql("[WH].[INVOICE]");
    b.AddColumnSql("T0.DATE, T0.TOTAL");
    b.AddColumnSql("T1.NAME, T1.BALANCE");
    b.AddJoinSql("[CUS].[CUSTOMER]", "T1.CUSTOMERID = T0.ID");

    var spec = "SELECT T0.DATE, T0.TOTAL, T1.NAME, T1.BALANCE FROM [WH].[INVOICE] AS [T0] INNER JOIN [CUS].[CUSTOMER] AS [T1] ON T1.CUSTOMERID = T0.ID";
    var gen = b.GetSqlText();
    Assert.Equal(spec, gen);
}

ExpressionSelectBuilder

Aquí va un test:

[Fact]
public void SelectColumnsJoinsAndWhere()
{
    SqlConfiguration.SetDialect(new TestDialect());
    SetModeling();
    var b = new ExpressionSelectBuilder();
    b.SetMainTable<DeliveryNote>();
    b.AddJoin<DeliveryNote, DeliveryNoteDetail>((i, id) => i.Id == id.DeliveryNoteId);
    b.AddJoin<DeliveryNote, Supplier>((i, c) => i.SupplierId == c.Id);
    b.Select<DeliveryNote, Supplier>((i, c) => i.Number, (i, c) => c.Name, (i, c) => i.Date, (i, c) => i.TotalAmount);
    b.Select<DeliveryNoteDetail>((id) => id.Size);
    b.Where<Supplier>(c => c.IsDeleted);
    b.Where<DeliveryNote, DeliveryNoteDetail>((i, id) => i.Year == "B" && id.IsDeleted == false);
    b.Where<DeliveryNote, DeliveryNoteDetail>(WhereOperator.Or, (i, id) => i.Year == "Z");

    var gen = b.GetSqlText();
    var spec = "SELECT [T0].[Number], [T2].[Name], [T0].[Date], [T0].[TotalAmount], [T1].[Size] FROM [WH].[DeliveryNote] AS [T0] INNER JOIN [WH].[DeliveryNoteDetail] AS [T1] ON ([T0].[Id] = [T1].[DeliveryNote_Id]) INNER JOIN [WH].[Supplier] AS [T2] ON ([T0].[SupplierId] = [T2].[Id]) WHERE ([T2].[IsDeleted] = @SqlParam0) AND (([T0].[Year] = @SqlParam1) AND ([T1].[IsDeleted] = @SqlParam2)) OR ([T0].[Year] = @SqlParam3)";

    Assert.Equal(spec, gen);
    Assert.Equal(b.Parameters["SqlParam0"], true);
    Assert.Equal(b.Parameters["SqlParam1"], "B");
    Assert.Equal(b.Parameters["SqlParam2"], false);
    Assert.Equal(b.Parameters["SqlParam3"], "Z");
}

Parto de la idea de que una consulta no tiene por que tener una tabla principal, por eso la clase principal no usa generics. Sin embargo al añadir cosas si que habrá que especificar los tipos de los objetos que intervienen. Por ejemplo cuando añado una columna:

    b.Select<DeliveryNoteDetail>((id) => id.Size);

Especifico el tipo DeliveryNoteDetail para indicar que quiero la columna correspondiente a la propiedad Size. En el caso de querer indicar columnas de varias tablas, debo indicar los distintos tipos:

    b.Select<DeliveryNote, Supplier>((i, c) => i.Number, (i, c) => c.Name, (i, c) => i.Date, (i, c) => i.TotalAmount);

Incluso puedo seleccionar columnas que son combinación de otras:

    b.Select<DeliveryNote, Supplier>((i, c) => i.Number + "-" + s.Name);

Esto es igual para el Where. También se acepta OrderBy y pronto GroupBy y Having, cuando usamos estos los tipos que intervienen han debido ser incluidos como tablas en la consulta, ya sea como tabla principal o como un join. Si no es así falla y te obliga a añadir dicho tipo a la consulta.

También podemos proyectar el resultado a un objeto de forma que se asignan aliases a las columnas resultantes:

[Fact]
public void SelectProjection()
{
    SqlConfiguration.SetDialect(new TestDialect());
    SetModeling();
    var b = new ExpressionSelectBuilder();
    b.SetMainTable<DeliveryNote>();
    b.AddJoin<DeliveryNote, Supplier>((dn, s) => dn.SupplierId == s.Id);
    b.Select<DeliveryNote, Supplier>((dn, s) => new DeliveryNoteDto
    {
        Number = dn.Number,
        TotalAmount = dn.TotalAmount,
        Date = dn.Date,
        SupplierName = s.Name
    });

    var gen = b.GetSqlText();
    var spec = "SELECT [T0].[Number] AS Number, [T0].[TotalAmount] AS TotalAmount, [T0].[Date] AS Date, [T1].[Name] AS SupplierName FROM [WH].[DeliveryNote] AS [T0] INNER JOIN [WH].[Supplier] AS [T1] ON ([T0].[SupplierId] = [T1].[Id])";

    Assert.Equal(spec, gen);
}

A la hora de generar el SQL, ToleSql puede generar nombres de tablas y campos distintos a los nombres de tipos y propiedades. Como no me gustan los attributes por ser invasivos, se usa una especie de configuración que llamo Modeling:

private void SetModeling()
{
    Modeling.ResetModeling();
    Modeling.Model<Supplier>().SetSchema("WH");
    Modeling.Model<DeliveryNote>().SetSchema("WH");
    Modeling.Model<DeliveryNoteDetail>().SetSchema("WH");
    Modeling.Model<Product>().SetSchema("WH");
    Modeling.Model<EquivalentProduct>().SetSchema("WH");
    Modeling.Model<DeliveryNoteDetail>().SetColumnName(dnd => dnd.DeliveryNoteId, "DeliveryNote_Id");
    Modeling.Model<User>()
        .SetSchema("LoB")
        .SetTable("SecurityProfile")
        .SetColumnName(u => u.CreatedBy, "CreatedBy_Id");
}

Con Modeling se puede especificar el nombre de cada tabla y el schema de cada una de ellas, también podemos especificar nombres de columnas. De momento esto se hace de forma global, pero además de esto también me gustaría añadir la posibilidad de Modelar por consulta, de forma que cada consulta miraría en su Modeling y luego en el global.

Por otra parte también se ha creado teniendo en cuenta que se pueda generar SQL para distintas bases de datos, hay una clase base para crear dialectos (DialectBase), donde se establecen las palabras clave de SQL, simbolos y posibles formas de generar los nombres de nuestras tablas y columnas. De momento incorpora un dialecto para SQLServer que es con el que se están haciendo las pruebas. En un futuro si se crea otro dialecto habrá que ver si la forma escogida para hacerlo es la correcta.

De momento está en fase de construcción, está desarrollado en .NET Core aunque también se puede usar desde .NET Framework. El proyecto está disponible en GitHub y se aceptan issues en español.

Actualmente estoy intentando que pueda tragar cualquier cosa, e incluso se pueda extender de forma fácil. La siguiente fase será hacerla más fácil de usar con clases que admitan métodos LinQ que al final usaran ExpressionSelectBuilder para ir montando las consultas.

Aquí se pueden encontrar las instrucciones de instalación y uso.

Conclusión

Como conclusión solo decir que me lo estoy pasando muy bien construyendolo 😀 En fin, como he dicho no es una solución para todos los escenarios y hay que tener en cuenta que a veces pagamos un precio muy alto por abstraernos, así que habrá que usarlo con cuidado 😉

6 comments

  • Muy buen post Javier, yo también he pensado muchas veces en esta línea…

    Hace mucho tiempo yo trabajé en CARE Technologies, SA donde nos encargamos de generar la BD, Server y Cliente en función de un modelo conceptual… La idea era genial y estuve mucho tiempo generando consultas contra la BD como comentas. Teníamos unas estructuras similares a las que comentas y a partir de ellas se generaban las sql… incluso gestionábamos los joins jugando con los alias, …

    Durante ese momento que yo era el responsable de server y BD apareció Linq que me cambió mi forma de entender el acceso a los datos, ya que si existe ya un lenguaje que la gente de .net ya usa y conoce, ¿para qué crear estructuras paralelas que hacen o mismo?

    Varios años después, ya en Murcia y en contacto se me ocurrió la idea de coger Linq y utilizando un nuevo motor creado por mí (no recuerdo el nombre de las clases para hacerlo) implementé un preprocesado que hacia lo que comentas para poder meterle mano a las consultas, optimizarlas e incluso solucionar errores que actualmente tiene LinqToEntity… Mi solución mezclaba LinqToEntity y LinqToObject para solucionar una consulta de la mejor y más óptima forma posible… Ahí el tiempo de me acumuló pero tengo en VSO el proyecto como lo dejé en su día y era una idea genial… a ver si con esto me animo y lo rescato?

    De todas formas cuando yo programo separa las consultas a la BD en dos tipos:

    Consultas que utilizo para cargar entidades para modificarlas y luego guardarlas (típicos servicios de creación, modificación o eliminación de objetos)
    Consultas para devolver la información a la capa de presentación

    En este segundo caso NUNCA cargo entidades sino campos particulares que se necesitan mostrar. Por ejemplo: si necesito mostrar de una factura el número, año y cliente la SQL que hago es (lo escribo de memoria sin compilar):

    var result = context.Get()
    .Select(x => new { x.numero, x.anyo, x.cliente.nombre })

    Pero nunca:

    var result = context.Get();

    o:

    var result = context.Get()
    .Include(“Cliente”);

    De todas formas muy buena reflexión… estaré al tanto de tus avances…

    Saludos

  • Que bueno leerte Xavi!

    Creo que es una buena forma de organizarse, consultar a la base de datos solo lo que necesitas.

    Creo que a esto del SQL le hemos dado vueltas todos, unos lo han dicho en voz alta y otros no, pero es normal que usando C# y teniendo LinQ, cuando escribes SQL piensas… y no habrá nada para esto?

    Me alegro mucho de tenerte por aquí.
    Un abrazo!

  • Hola Xavi,
    Ese context.Get().Select(….) si la clase se encarga de optimizar la consulta para que solo incluya los joins necesarios me resolverías el tema que nunca inicie por temas de tiempo pero que en algún momento de mi vida también me eh planteado para escenarios muy contretos pero importantes.
    Ojala rescates al pequeño olvidado.
    Saludos,

  • Me leí el articulo, pero no me queda clara la ventaja de trabajar el acceso a la base de datos de esta forma que con linq to entities (que no es lo mismo que linq to sql) que también tiene el tipado de las consultas y el poder mapear a cualquier DTO.

    Hay alguna cosa que se me esta pasando?

    • Hola Pablo, no es la primera vez que me lo preguntan.

      A ver, son dos enfoques distintos. Confundo algunas veces linq2entities y linq2Sql, pero creo que el enfoque de ambos es el mismo, te proveen una forma de consultar objetos abstrayendote de como están guardados en el modelo relacional, en la base de datos.

      El enfoque de ToleSql es distinto, en este caso se provee de una forma de realizar consultas SQL, es decir, consulto tablas y hago join entre ellas por determinadas columnas, la ventaja que aporta ToleSql es que esas tablas y esas columnas están representadas por objetos que se corresponden 1 a 1 con elementos del modelo relacional; un tipo es una tabla y una propiedad de un tipo es una columna de la tabla que representa su tipo.

      Resumiendo, ToleSql se focaliza en realizar consultas SQL de un modelo relacional, los demás LinqProviders te permiten realizar consultas sobre un modelo de objetos abstrayendote del modelo relacional.

      Lo que pasa es que algunas veces usamos los LinQ Providers sobre objetos que son una fiel representación de nuestras tablas, pero no siempre debe ser así.

      En definitiva, enfoques distintos.

      • Hola Javier, ahora me queda más claro. Creo que todo se resume a que se utilizan los ORM de forma que no están diseñados para obtener varias ventajas (query tipadas, etc) pero que termina mapeando 1 a 1 con la BD.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

Demuestra que no eres un bot *