Etiqueta: sql

PostgeSQL: Calcular una edad a partir de una fecha.

en por Donnie Rocken Informática, SQLDeja un comentario

No hace mucho veíamos cómo calcular una edad en SQL-Server. Vamos ahora con otro SGBD ¿Cómo lo hago en Postgres? Pues con mucha menos dificultad, porque este gestor incluye ya una función integrada para el cálculo de edades, presente al menos desde la versión 9 (no se si antes) llamada age().

La función puede ser llamada con uno o con dos argumentos, siempre del tipo timestamp, y devuelve el interváluo entre ambos. Para el ejemplo imaginemos una tabla llamada clientes donde tenemos un campo FechaNac con la fecha de nacimiento guardada.

/*Con el primer ejemplo
pasando sólo una fecha
cogería la edad a día de hoy
de alguien nacido en esa fecha*/
Select age(timestamp FechaNac) from clientes

/*Con el segundo ejemplo
pasando dos fechas
cogería la edad que una
persona nacida en la fecha 2
tenía en la fecha 1*/

Select age(timestamp '2014-01-01',timestamp FechaNac) from clientes

SQL Server: Calcular una edad a partir de una fecha.

en por Donnie Rocken Informática, SQL20 comentarios

¿Cuántas veces no tenemos que sacar, en una consulta, la edad de una persona a partir de su fecha de nacimiento? Es una consulta que me toca hacer habitualmente (y que, mira por dónde, nunca había comentado aquí). ¿Cómo lo hacemos en SQL-Server?

Pensemos que tenemos una tabla clientes, donde hay un campo de tipo DATE llamado FechaNac en el que almacenamos sus fechas de nacimiento. Podrías creer que lo más simple es hacer lo siguiente:

Select DATEDIFF(YEAR,FechaNac,GETDATE()) as Edad from clientes

Pero hay un problema: Esa solución sólo resta los años, no tiene en cuenta el mes y el día ¿qué pasaría entonces? Que nos pondría la edad que ese cliente va a cumplir durante este año, no su edad real. ¿Cómo lo solucionamos? Hay muchas opciones, yo he optado por esta:

Select DATEDIFF(YEAR,FechaNac,GETDATE())
-(CASE
   WHEN DATEADD(YY,DATEDIFF(YEAR,clientes.FechaNac,GETDATE()),clientesFechaNac)>GETDATE() THEN 
      1
   ELSE 
      0 
   END) as Edad
 from clientes

Lo que hacemos es sumar la diferencia de años a la fecha de nacimiento y, en caso de que fuera posterior a hoy (es decir, todavía no ha cumpliado años) restamos 1 a la diferencia, si no restamos 0 y nos quedamos como estamos.

Otra opción, más elegante, podría ser esta:

Select floor(
(cast(convert(varchar(8),getdate(),112) as int)-
cast(convert(varchar(8),clientes.FechaNac,112) as int) ) / 10000
) as edad from clientes

En este caso convertimos la fecha al formato clásico de base de datos como una cadena que aglutina pegados Fecha, mes y día y lo convertimos a un entero (hoy nos quedaría por ejemplo 20151001), se lo restamos a la fecha de nacimiento, dividimos entre diez mil para obtener el año y redondeamos por defecto con la función floor().

Borrar un evento de MySQL

en por Donnie Rocken Informática, SQLDeja un comentario

Hoy me preguntaba en la entrada sobre Eventos en MySQL cómo se borran, así que en vez de contestar en los comentarios voy a dejarlo por aquí en una minientrada general. ¿Cómo se borra un evento? Pues con Drop Event, cuya sintaxis es DROP EVENT [IF EXISTS] event_name. En el viejo ejemplo creábamos uno llamado e_ActualizaSaldoDiario, así que si quisiéramos borrarlo nos bastaría con hacer lo siguiente:

DROP EVENT e_ActualizaSaldoDiario

En caso de que el evento que intentamos borrar no existiera (por haberse borrado previamente) nos devolverá un error. Si queremos evitar esto podemos añadir IF EXISTS:

DROP EVENT IF EXISTS e_ActualizaSaldoDiario

Recordad que para borrarlos, igual que para crearlos, tenéis que tener permisos sobre los eventos en el schema.

SQL-Server ¿es mejor usar MAX() o TOP 1?

en por Donnie Rocken Informática, SQLDeja un comentario

Una duda me asaltaba el otro día preparando una serie de consultas, en una de las cuales tenía que sacar la fecha más alta de una serie de registros. ¿Consumía menos recursos un MAX() o acaso era mejor recurrir a la conjunción de order by y Top 1?

Rebuscando por duckduckgo me encontré con este blog donde disipan la duda:

  1. Si el campo a buscar forma parte de un índice cluster, entonces da igual porque ambas se ejecutan a una velocidad extremadamente rápida
  2. Si el campo a buscar no es un índice cluster entonces MAX() da una respuesta más rápida porque la función está mejor optimizada y tiene menos carga que realizar un order by después del select

En el artículo original están los bancos de pruebas con resultados concretos.

Seguridad en WordPress ¿cómo cambiar el prefijo de la base de datos?

en por Donnie Rocken Ciberseguridad-Privacidad, Informática, Lenguajes de programación, SQLDeja un comentario

Si alguna vez has hecho una instalación de WordPress verás que te solicita, para crear el sitio, el nombre de una base de datos, el usuario con el que se conectará a ella, la contraseña de ese usuario y un prefijo. ¿Por qué un prefijo? Simplemente porque así puedes tener varias instalaciones de WordPress (o de otros CMS que usen nombres de tabla muy genéricos tipo «users», «posts», etc.) en una misma base de datos.

Si no cambiamos esta configuración por defecto el sistema pondrá el prefijo «wp_» a las tablas. El no poner un prefijo personalizado, en si, constituye un error. Porque dejando el prefijo por defecto estás provocando: a)Que si un atacante logra ver el nombre de una tabla descubra que el CMS que estás utilizando es WordPress y b) Que dicho atacante, entonces, pueda conocer los nombres de las tablas de tu base de datos. La solución es simple, durante la instalación define un prefijo personalizado.

¿Ya lo tienes instalado? Don’t worry, be nécora. No está todo perdido, tienes todavía varias opciones. La más rápida y simple, instalas este plugin que te permitará cambiarla cómodamente desde la interfaz gráfica. ¿La versión más larga y compleja y élite? Pues los siguientes pasos te lo explican:

Bueno, como paso previo, o paso 0 del proceso HAZ UN BACKUP DE TU BASE DE DATOS POR SI ALGO FALLA Y TIENES QUE RECUPERARLA. Consejo que debes tener siempre en mente cuando te pongas a tocar tablas de una instalación de cualquier cosa.

El primer paso: ir a wp-config.php y cambiar ahí el prefijo (en nuestro caso pondremos como prefijo personalizado my_b457Bch33s_ ).

$table_prefix  = 'my_b457Bch33s_';

El siguiente paso es renombrar todas las tablas de tu instalación de wordpress:

/*Básicamente vas haciendo esto con todas las tablas*/
RENAME table `wp_comments` TO `my_b457Bch33s_comments`;

Tras renombrar las tablas haces un update sobre la tabla options buscando todas las líneas que hagan referencia a tablas con el prefijo viejo para actualizarlas:

UPDATE `my_b457Bch33s_options` SET `option_name`=REPLACE(`option_name`,'wp_','my_b457Bch33s_') WHERE `option_name` LIKE '%wp_%';

Y con la tabla usermeta tres cuartos de lo mismo, update que te crió.

UPDATE `my_b457Bch33s_usermeta` SET `meta_key`=REPLACE(`meta_key`,'wp_','my_b457Bch33s_') WHERE `meta_key` LIKE '%wp_%';

Y tras esto deberías tener todo funcionando de nuevo, pero con el nuevo prefijo, más seguro contra potenciales atacantes.

Función para calcular la distancia entre dos coordenadas en MySQL

en por Donnie Rocken Informática, SQL2 comentarios

Llevo un par de días trabajando con coordenadas geográficas en un proyecto personal, por lo que voy a hablar del tema estos días.

Para saber qué distancia separa dos coordenadas recurrimos a la fórmula del Haversine, o fórmula del semiverseno. Delego la parte matemática teórica y vamos a la parte MySQL, que supongo que es lo que estabas buscando cuando llegaste aquí. Os dejo el código y una pequeña explicación paso a paso. La función requiere cuatro variables (latitud y longitud de cada uno de los puntos) y devuelve el resultado en kilómetros. 

/*vamos a inicializar nuestras variables,
en este caso con un valor constante
pero en vuestro caso lo podéis hacer
con datos sacados de una consulta Select*/

Set @Lat1 = 42.880501;
Set @Lng1 = -8.545732;
/*Son las coordenadas de la catedral de Santiago
o eso me ha dicho DuckDuckGo*/ 

Set @Lat2 = 53.731951;
Set @Lng2 = -6.877150;
/*Como segundo punto he elegido una calle cualquiera 
en Kells, Irlanda*/

/*Y ahora aplicamos la función SQL 
Que nos devolverá la distancia en kilómetros*/

SELECT (acos(sin(radians(@Lat1)) * sin(radians(@Lat2)) + 
cos(radians(@Lat1)) * cos(radians(@Lat2)) * 
cos(radians(@Lng1) - radians(@Lng2))) * 6371) as distanciaKm;

/*el resultado con estos datos es de 
1212.8306071122186 kilómetros*/

/*Si quieres el resultado el millas
cambia el valor 6371 en la función
por 3959*/

Próximamente veremos como hacerlo este cálculo con Javascript, que es mucho más divertido.

Script para gestión de backups de PostgreSQL en FreeBSD

en por Donnie Rocken Informática, SQLDeja un comentario

No recuerdo si había comentado que estaba en plena migración tecnológica laboral, trabajando con el SGBD libre PostgreSQL y el sistema operativo (libre, of course) FreeBSD. Y el primero de los trasteos era simple ¿cómo automatizo los backups? Ya habíamos hablado de este tipo de cuitas alguna vez por aquí, así que vamos a ver cómo con un script, y haciendo uso de Cron, podemos lograrlo:

#!/bin/sh
# pgsql backups --datosde conexion
db_user="tu_usuario"
db_name="tu_base_de_datos"
db_dumpdir="/var/backups/carpeta"
date_format="`date +%Y-%m-%d.%H:%M`"

#ARRIBA CAMBIA LOS EJEMPLOS POR LOS DATOS QUE CORRESPONDAN. 
 
# BORRAMOS LOS BACKUPS MÁS VIEJOS
# PARA NO SATURAR
# EN ESTE CASO, LOS QUE TENGAN MÁS DE
# 4 SEMANAS

expire_time="+4w"
 
# Begin Script #
/usr/local/bin/pg_dump -U $db_user $db_name -f "$db_dumpdir/$db_name-$date_format.pgsql"
/usr/bin/find $db_dumpdir -d 1 -Btime $expire_time -delete

Con este ya tenemos un script que nos genera un backup de la base de datos en nuestra carpeta de copias de seguridad y además borra los que tengan más de cuatro semanas, para que no saturemos el disco duro. Y ahora, como dijimos antes, necesitamos Crontab para automatizar la copia. Asumamos que guardamos el script en la dirección /usr/local/scripts/pg_backup.sh, porque somos gente ordenadita (al menos a la hora de organizar discos duros). En nuestro caso ejecutaremos el script cada 6 horas:

# crontab -e
* /6 * * * /usr/local/scripts/pg_backup.sh > /dev/null 2>&1

Y con esto ya tenemos la cuestión de automatizar las copias de seguridad solventada.

Diferencias entre clave primaria y clave foránea

en por Donnie Rocken Informática, SQLDeja un comentario

Vamos con una entrada de SQL básico, uno de los fundamentos del modelo relacional ¿Qué diferencia existe entre una clave primaria y una foránea?

En el diseño de una base de datos relacional la clave primaria es el campo, o conjunto de campos, que nos permite identificar de forma única un registro. Por así decirlo es como el DNI de esa tabla. Se trata de un valor, o grupo de valores, único que nos permitirá diferenciar un registro concreto. Podemos definir la clave primaria en el momento de la creación de la tabla, dentro de la sintaxis del CREATE TABLE, o a posteriori añadiendo la regla en un ALTER TABLE.

Por su parte la clave foránea es un campo, o conjunto de campos, que nos permite relacionar un registro de una tabla con otro, generalmente de una tabla distnta . Como ejemplo piensa en la clásica base de datos de una tienda, en la que tienes una tabla con productos, cada uno de los cuales tiene un código (que será la clave primaria), una descripción y un precio. Por otra parte tienes una tabla en la que registras las ventas, y podrías tener un código para cada una, la fecha, la hora y el producto. Para identificar el producto en esa tabla lo más práctico, para no repetir información, sería utilizar el código de la tabla de productos (la clave primaria de la primera tabla, que en esta se convierte en clave externa).

Una tabla puede tener relaciones con varias a través de distintas claves foráneas, e incluso referenciarse a si misma (clave foránea recursiva). Al igual que con la clave primaria podemos definir las claves foráneas dentro de la sintaxis de creación de la tabla o a posteriori con un ALTER TABLE.

Por tanto la diferencia es sencilla: la clave primaria identifica un registro único de una tabla. La clave foránea relaciona los datos de un registro de una tabla con los de otra, o con un registro distinto de la misma tabla.

Concatenar varios valores de la misma columna en una sola celda en SQL-Server

en por Donnie Rocken Informática, SQL3 comentarios

Esta tarde, para hacer un informe en una aplicación en el trabajo (SQL-Server+ASP+VB), necesitaba sacar todos los registros de una columna en un solo registro separado por un símbolo. Podéis decir que podría haber hecho una consulta normal y luego simplemente añadir las comas al imprimirla en pantalla, pero en este caso necesitaba que vinieran así en la consulta SQL.

Si bien en un principio pensé en utilizar un cursor, al final rebuscando por el manual de SQL-Server 2008 (que debe ser de los pocos que tengo en papel y no en PDF) encontré una solución que consume menos recursos.

Empecemos con la tabla, más o menos esta

CREATE TABLE Problemas(
Id INT PRIMARY KEY IDENTITY(1,1),
Incidencia VARCHAR(120),
Usuario INT,
Fecha DATETIME
)

Bien, la cosa es tener una cadena con todas las incidencias separadas por una «tubería» (el caracter | ).

El procedimiento va a ser el siguiente: declaramos una variable, hacemos un select donde vayamos concatenando cada resultado dentro de la variable, hacemos un apañico porque nos va a meter un separador al principio y acabamos con un select sobre la variable para mostrarla/recogerla.

--Declaramos la variable
DECLARE @ListaIncidencias VARCHAR(MAX) = ''
--Ahora vamos con la consulta
SELECT @ListaIncidencias = @ListaIncidencias +'|' + p.Incidencia
FROM Problemas p
--Eliminamos el separador extra que nos meterá al principio
--usando SUBSTRING
SET @ListaIncidencias = SUBSTRING(@ListaIncidencias,2,LEN(@ListaIncidencias))
--Hacemos un SELECT sobre la variable para sacarla por pantalla
SELECT @ListaIncidencias as 'Incidencias'

De esta forma todos los registros aparecerán como una cadena de texto dentro de una sola celda.

Diferencia entre inner join, left join y right join. SQL

en por Donnie Rocken Informática, SQL27 comentarios

Hoy otra cuestión a petición de un lector que decía que su nivel de inglés no era el suficiente para orientarse con la muy válida explicación que hay en StackOverflow que hasta incluye este útil diagrama:

Uniones entre tablas SQL
Diferentes tipos de uniones entre tablas

Si bien finalmente encontró una válida explicación en otras páginas (hay unas cuantas) me da pie para esta entrada. ¿Qué diferencia hay entre un inner join y un left o un right join?

Las más comunes suelen ser las uniones internas entre tablas (inner join), como ejemplo os diré que en los últimos seis meses, desde que empecé en mi actual trabajo, he tenido que hacer sólo 3 uniones externas mientras que internas he hecho docenas. Un inner join, si miráis el diagrama, se podría representar como la intersección entre dos conjuntos, es decir, una consulta con inner join mostraría los registros de las tablas que coincidan en los campos de la unión que se ha definido en dicha consulta. Dicho así es denso, pero con un ejemplo se entiende claro.

Supongamos que tenemos una tabla con los empleados de una empresa (con un identificador llamado Id, un campo de nombre, uno de apellidos y uno con el Id del departamento en que trabaja llamado IdDepartamento) y en otra tenemos los departamentos (Id y Nombre). Algunos trabajadores tienen un departamento asignado y otros no, y obviamente la relación entre ambas tablas se da entre el campo Id de Departamento con el campo IdDepartamento de la tabla Empleados. Utilizando un inner join podríamos obtener el listado de todos los trabajadores que tienen un departamento asignado:

select e.nombre, e.apellidos from empleados e inner join departamentos d on e.IdDepartamento=d.Id;

En cuanto al enlace externo tenemos dos opciones: left join y right join. Con esta unión recuperamos todos los enlaces de una tabla y los datos de la otra que coinciden con la condición definida en la consulta. Cuando usamos una unión left join recogemos todos los datos de la tabla que está a la izquierda de la unión en la consulta y si usamos en cambio right join las de la derecha. Imaginemos las tablas de antes, pero ahora queremos todos los datos de la tabla empleados, tengan o no departamento, y además el dato de en qué departamento están si lo tuvieran. Usaremos un left join para obtener los datos.

select e.nombre, e.apellidos from empleados e left join departamentos d on e.IdDepartamento=d.Id

En este caso obtendríamos los nombres y apellidos de todos los empleados, los departamentos de los que lo tenga y en los que no recibiríamos un campo con un valor NULL.

Finalmetne tendríamos opciones como el full outer join o producto cartesiano, que nos sacaría todos los valores de ambas tablas.

A partir de esto y mezclando varias tablas y condiciones extra en una cláusula WHERE podemos lograr resultados más complejos.