Crear y borrar una vista en SQLite

¿Qué es una vista?

Una vista es una consulta cuyo resultado se presenta como una tabla, puesto que tienen la misma estructura: filas y columnas. La diferencia es que sólo se almacena de ellas la definición y no los datos, que se extraen de las otras tablas de la base de datos. En SQLite la vistas son de solo lectura, de forma que no se pueden ejecutar instrucciones INSERT, DELETE o UPDATE sobre ellas para actualizar las tablas.

¿Cómo creo una vista en SQLite?

Para crear una vista en SQLite tenemos la instrucción CREATE VIEW. Tendremos que asignar un nombre a la vista que creamos y ejecutar una consulta que nos devuelva los datos que formarán nuestra vista. La sintaxis sería:

CREATE VIEW Vista_Nueva 
AS 
SELECT
	t1.Id,
        t1.Campo1,
        t2.Campo1 as Campo2
FROM
	tabla t1
INNER JOIN tabla2 t2 ON t1.IdEnTabla2 = t2.Id;

Vamos a poner como ejemplo que tenemos una tabla con variedades de quesos, otra con países y otra con tipos de leche. Y queremos una vista que nos muestre de qué país y con qué tipo de leche está hecho cada queso. Pues podríamos sacarlo con una consulta así:

CREATE VIEW v_QuesosDetallada 
AS 
SELECT
	q.Id,
        q.Nombre,
        p.Nombre as Pais,
        l.Descripcion as TipoLeche
FROM
	Quesos q
INNER JOIN paises p ON q.IdPais = p.Id
INNER JOIN leches l ON q.IdTipoLeche = l.Id;

¿Cómo borro una vista?

Para eliminar una vista en SQLite tenemos la instruccion DROP VIEW, que iría seguida del nombre de la tabla. Siguiendo con el ejemplo de arriba, para borrar la vista v_QuesosDetallada tendríamos que ejecutar la siguiente instrucción:

DROP VIEW v_QuesosDetallada;

De esta forma eliminaríamos la vista de nuestra base de datos.

Si intentamos crear una vista con un nombre que ya existe o si intentamos borrar una que no existe recibiremos un mensaje de error. Por tanto es interesante asegurarse utilizando IF NOT EXISTS a la hora de crear e IF EXISTS a la de borrar.

Calcular la edad a partir de una fecha en SQLite.

Otra entrada sobre SQLite, vendrán algunas más ya que he tenido que estudiar un poco sobre ello para utilizarlo en una aplicación. Y ya que nos ponemos con consultas y truquitos vamos con el siguiente: ¿Cómo calculamos la edad a partir de una fecha? Ya lo vimos en el pasado con SQL-Server, con MySQL y con PostgreSQL, incluso fuera del tema de las bases de datos también lo hicimos con Javascript.

Supongamos que tenemos una base de datos Usuarios con un campo FechaNacimiento donde almacenamos, como te puedes imaginar, una fecha. Para calcular la edad a día de hoy podemos hacer dos cosas:

La primera es el clásico “Restamos un año a otro y luego si el día del cumpleaños todavía no llegó restamos uno año más“, que podríamos expresar así en una consulta:

SELECT (strftime('%Y', 'now') - strftime('%Y', FechaNacimiento )) - (strftime('%m-%d', 'now') < strftime('%m-%d', FechaNacimiento )) from Usuarios;

Como ves, en primer lugar extraemos los años de las dos fechas y los restamos. En la segunda resta añadimos la compración entre mes y día actual y mes y día de la fecha, dicha evaluación devolverá 1 si es anterior y 0 si no lo es, de esta forma resta 1 en ese caso.

Otra solución, convertir ambas fechas a un formato numérico y restarlas, y después convertir a un formato de número entero:

Select cast(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', FechaNacimiento ) as int) from Usuarios;

Utilizar SQLiteStudio en Ubuntu

SQLiteStudio es un software libre multiplataforma que nos proporciona una interfaz gráfica para trabajar con bases de datos SQLite. Existen versiones para Linux, Windows y MacOS.

En el siguiente enlace puedes entrar a la página de descargas de SQLiteStudio, donde hay dos opciones: un instalador y un paquete .tar.xz con una versión portable del programa.

Durante las pruebas he tenido problemas con la versión con instalador en una máquina con Xunbutu, pero la versión portable ha funcionado sin problemas. Basta con descargarla y desempaquetarla:

SQLite Studio Descarga

Descomprimir SQLitestudio

Una vez desempaquetado buscamos el programa y lo ejecutamos. En la primera ejecución nos pedirá que definamos el idioma:

Ejecutable sqlitestudio

Y listo, ya está funcionando SQLiteStudio en tu equipo:

SQLite Xubuntu

Calcular la mediana en SQL-Server

Como no me apetece meterme en una discusión infructuosa con un individuo *troll ultraderechista sexualmente frustrado* voy a aprovechar la hora de comer en el trabajo para comentaros cómo podemos calcular la mediana de una serie de números en SQL-Server. Ya vimos no hace mucho el significado de este término y también cómo podemos calcularlo en Libre Office Calc.

Si vamos a trabajar con SQL-Server 2012 o superior la función PERCENTILE_DISC() nos servirá, sin tener que hacer nada más. Es una función que calcula un percentil concreto para una serie de valores ordenados de un conjunto de filas, y su sintaxis es:

PERCENTILE_DISC ( número ) WITHIN GROUP ( ORDER BY exp_ordenación [ ASC | DESC ] )
OVER ( [ partido_por ] )

Aquí os copio sus argumentos de la web de documentación de Microsoft directamente, donde también tenéis ejemplos de código para SQL-Server y para Azure:

  • número :El percentil que se va a calcular. El valor debe estar entre 0,0 y 1,0.
  • WITHIN GROUP ( ORDER BY exp_ordenación[ ASC | DESC ]):Especifica una lista de valores para ordenar y cuyo percentil se va a calcular. Solo se permite una exp_ordenación. El criterio de ordenación predeterminado es ascendente. La lista de valores puede ser de cualquiera de los tipos de datos válidos para la operación de ordenación.
  • OVER ( partido_por ):Divide el conjunto de resultados generado por la cláusula FROM en particiones a las que se aplica la función de percentil. Para más información, vea Cláusula OVER (Transact-SQL). Las cláusulas y no se pueden especificar en una función PERCENTILE_DISC.

De esta forma PERCENTILE_DISC(0.5) calculará la mediana del cojunto de filas que estamos analizando.

Pero ¿qué pasa si trabajamos con una versión anterior a SQL-Server 2012? Te parecerá algo prehistórico, pero en muchos sitios sigue funcionando SQL-Server 2008 o 2005. ¿Cómo lo hago ahí donde no dispongo de la función PERCENTILE_DISC()?

Bueno, ahí he hecho una solución un poco picapedrera, porque según el conjunto de datos que tengamos las hay bastante mejores. Esta realmente consume muchos recursos, pero por otra parte es universal. Si la tabla cuenta con una columna de Identidad la cosa puede ser más eficiente:

Básicamente he usado una tabla llamada Actividades, donde hay una columna numérica llamada Importe. Para calcular la mediana de este importe vamos a sacar los dos valores centrales dividiendo las filas en dos mitades, ordenando una de forma descendente y otra ascendente. Tras eso los sumamos y hacemos la media de la suma.

Select ((
    Select Top 1 Importe
    From   (
                    Select  Top 50 Percent Importe
                    From    Actividades
                    Where   Importe Is NOT NULL
                    Order By Importe
                    ) As A
    Order By Importe DESC) + 
    (
    Select Top 1 Importe
    From   (
                    Select  Top 50 Percent Importe
                    From    Actividades
                    Where   Importe Is NOT NULL
                    Order By Importe DESC
                    ) As A
    Order By Importe Asc)) / 2 as MedianaImportes

SQL-Server: Reiniciar el valor de una columna de identidad

Imaginemos que hemos borrado todos los registros de una tabla, en SQL-Server, que tenía definida una columna de identidad como clave primaria. Ahora queremos que las nuevas inserciones no comiencen desde el último Id borrado sino desde el principio ¿Cómo lo hacemos? Es muy simple:

DBCC CHECKIDENT ('NuestraTabla', RESEED, 1);

Vale, pero ¿y si no hemos borrado todos los valores sino, por ejemplo, solo un 20% de ellos? ¿Cómo hacemos para que empiece desde el valor máximo? Pues con este método lo haríamos:

DBCC CHECKIDENT ('NuestraTabla', RESEED, 1);
DBCC CHECKIDENT ('NuestraTabla', RESEED);

De esta forma le ponemos el valor a 1 y luego el segundo comando pondrá automáticamente el valor máximo de la tabla.

¿Y si hemos metido manualmente un valor en la Id, desactivando el chequeo de identidad para la inserción, mayor que el que tocaba y queremos que siga desde ahí?

DBCC CHECKIDENT ('NuestraTabla', RESEED);

Pues de nuevo invocamos la función sin ningún valor definido.

SQL-Server: Usar Try…Catch en una transacción.

Desde SQL-Server 2008 es posible utilizar la instrucción Try…Catch. Estas instrucciones nos permiten implementar un mecanismo de control de errores: metemos un bloque de código dentro de la instrucción Try, para intentar ejecutarlo, y dentro de Catch metemos las instrucciones para controlar la respuesta en caso de error.

La sintaxis básica de un Try…Catch en SQL-Server es la siguiente:

BEGIN TRY  
     ---Bloque de código 
END TRY  
BEGIN CATCH  
     --Código en caso de error. 
END CATCH  

Cuando realizamos una transacción podemos exprimir al 100% la instrucción Try…Catch. La idea es la siguiente: Comenzamos la transacción, intentamos una acción en un bloque Try. En caso de que falle mostramos el error y ejecutamos un rollback para anular la transacción. En caso de éxito confirmamos la ejecución de la transacción.

Veamos un código de ejemplo:

BEGIN TRANSACTION;  --Comienza

BEGIN TRY  --Aquí empieza el try
    Insert into Ejemplo(Id,Nombre) values(22,'Manuel');
    Delete From Espera Where Nombre = 'Manuel' 
--Intentaremos esas dos acciones
END TRY  
BEGIN CATCH  
--El primer paso en el Catch
--Será recoger y mostrar
--Todos los errores
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
--Si hay transacción abierta
--Hacemos un rollback sobre ella
--Para anularla
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  
--Fuera del bloque vamos a
--comprobar que haya transacción abierta.
--Si la hay es que no tuvo que ir por el CATCH
--Por tanto confirmamos.
IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO  

SQL-Server: Intercambiar valores 0 y 1 en un Update

Vamos con una cuestión que seguro que nos hemos encontrado más de una vez: Tenemos un campo en una base de datos donde almacenamos un valor verdadero o falso y tenemos que realizar un Update para cambiar dicho estado. ¿Cómo hacemos para modificarlo con el menor costo posible? Te doy un par de soluciones, todas válidas para SQL-Server desde la versión 2008 hasta la 2017 (y supongo que seguirán valiendo en las posteriores):

Para la primera vamos a usar la operación del OR exclusivo bit a bit:

Update TablaEjemplo SET ValorVF = Convert(Bit,ValorVF ^ 1)

Otra opción lógica es usar el operador lógico de negación para invertir el valor:

Update TablaEjemplo SET ValorVF = Convert(Bit,~ ValorVF)

¿Y qué pasa si la tabla no está bien diseñada y en lugar de almacenar un valor bit estamos almacenando un entero? Si no podemos cambiar el diseño de la tabla todavía podemos recurrir a la primera solución, el OR exclusivo bit a bit, pero sin realizar la conversión a tipo bit:

Update TablaEjemplo SET ValorVF = ValorVF ^ 1

Y además tenemos otra opción, con una simple resta y con la función ABS(), que nos devuelve el valor absoluto del parámetro que recibe:

Update TablaEjemplo SET ValorVF = ABS(ValorVF - 1)