Etiqueta: sql-server

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.

Consulta SQL para conocer el número de campos de una tabla

en por Donnie Rocken SQLDeja un comentario

Por avatares del destino hoy estaba modificando una página en ASP clásico y necesitaba saber el número de campos que tenía una tabla. Basta con una consulta para tener estos datos:

SELECT Table_Name, COUNT(*) As NumeroCampos
FROM Information_Schema.Columns
WHERE Table_Name = 'nombre_de_la_tabla'
GROUP BY Table_Name;

Si queréis saber el de todas las tablas de la base de datos podéis hacer lo siguiente:

SELECT Table_Name, COUNT(*) As NumeroCampos
FROM Information_Schema.Columns
GROUP BY Table_Name
ORDER BY Table_Name

Encriptar una columna en MS-SQL Server con clave simétrica

en por Donnie Rocken SQL2 comentarios

Siguiendo con la racha de entradas sobre SQL, vamos a ver cómo encriptar una columna de una tabla SQL-Server usando una clave simétrica.

Bueno, lo primero, tras haber seleccionado la base de datos que queremos usar, es crear una clave maestra si no existe ya, con un password seguro (largo, con mayúsculas, minúsculas, números y símbolos), luego crear un certificado y finalmente una clave simétrica eligiendo el algoritmo a usar (en este caso AES256, robusto y seguro):

IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = 'PoNGo@1#pa55w0rd!fuEr73@para½q@no$me()lo_tAngUen'
GO

CREATE CERTIFICATE NombreDelCertificado
   WITH SUBJECT = 'Certificado Para Ejemplo';
GO

CREATE SYMMETRIC KEY SSN_Clave_Simétrica_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE NombreDelCertificado;
GO

Ok, con esto ya tenemos nuestra clave creada, con su contraseña. Un dato, si sois usuarios de Windows XP o de Windows Server 2000 no podéis usar AES, esos sistemas operativos no lo soportan, tenéis que usar DES (menos robusto y seguro, pero es lo que hay por usar sistemas sin actualizar). Ahora para introducir datos cifrados tienes que hacer lo siguiente:

-- Primero abre la clave simétrica.
OPEN SYMMETRIC KEY SSN_Clave_Simétrica_01
   DECRYPTION BY CERTIFICATE NombreDelCertificado;

--Luego introduce los datos
UPDATE BDEjemplos.Ejemplo
SET ColumnaDatosEncriptados = EncryptByKey(Key_GUID('SSN_Clave_Simétrica_01'), 'Una cadena cualquiera');
GO

En este caso usé un UPDATE pero podría haberlo hecho dentro de un INSERT. En todo caso, la función EncryptByKey() que recibe el identificador de la clave como primer parámetro y el dato a encriptar (String, Real, Float…) como segundo, es la clave de la operación.

Y para acceder a los datos cifados la cosa tampoco se torna muy compleja, es má o menos lo mismo, primero abrir y luego usar una función para desencriptar en la SELECT.En el ejemplo sacaríamos en una columna los datos encriptados y al lado el mismo dato desencriptado.

OPEN SYMMETRIC KEY SSN_Clave_Simétrica_01
   DECRYPTION BY CERTIFICATE NombreDelCertificado;
GO

SELECT ColumnaDatosEncriptados 
    AS 'Dato Encriptado',
    CONVERT(nvarchar, DecryptByKey(ColumnaDatosEncriptados)) 
    AS 'Dato Desencriptado'
    FROM BDEjemplos.Ejemplo;
GO

Existe la posibilidad de hacer esto también con un autenticador para fortalecer la seguridad. Si os interesa podéis mirarlo en la web de ayuda de Microsoft o presionarme para que publique aquí como hacerlo.

La función IFNULL de MySQL

en por Donnie Rocken SQL1 comentario

Si el otro día hablaba de la función IF() no es menos interesante la función IFNULL() de MySQL.

En muchos casos te encuentras bases de datos donde en una columna, por falta de restricciones, hay gente que ha usado el nulo en lugar del cero y están todos mezclados (eso me ha pasado a mi, y luego no había forma de cuadrar los balances). O simplemente quieres sacar un listado de producto y deseas que en el nulo ponga un mensaje tipo «no aplicable» (para un descuento) o «sin unidades» (para un registro de almacén). La función IFNULL facilita este trabajo.

Vamos a ver el primer ejemplo citado. Tenemos una columna donde se han usado nulos en valores que realmente deberían llevar cero, y ahora necesitamos exportarlo a Excel y nos va a hacer el lío tenerlos así. Cierto es que una opción sería modificar toda la columna cambiando nulos por ceros, pero supongamos que no es posible (por falta de permisos de edición sobre la tabla, mismamente). En este caso con una línea tal que esta:

SELECT producto, IFNULL(precio, 0) from tbProductos

La función evalúa el primer parámetro y si este es nulo devuelve el valor indicado en el segundo, mientras que si no lo es devuelve el primero. Es decir, en el ejemplo devolvería el precio del producto si no es nulo, y si lo es devolvería cero.

Veamos otro ejemplo, donde probaríamos la función IFNULL con el ejemplo del descuento:

SELECT producto, IFNULL(descuento, 'NO APLICABLE') from tbProductos

La función IFNULL puede devolver valores del tipo INTEGER, REAL o STRING.

Comentar que en MaríaDB también existe, como la mayoría de las funciones de MySQL. Y que en Microsoft SQL-Server existe la función equivalente ISNULL.

Usando librerías .dll de .NET en SQL-Server2008

en por Donnie Rocken Informática, Lenguajes de programación, SQL1 comentario

Aunque normalmente no se recomiende tener la lógica de negocio en el lado del servidor de la base de datos, en algunos casos puede resultar interesante el hacerlo. Microsoft, en su empeño (por otra parte loable) de lograr una integración total entre sus servicios, nos permite utilizar nuestras .dll creadas en .NET (sea en VB.NET o en C#.NET o en C++) dentro de nuestra base de datos SQL-Server como «código gestionado».

Iré construyendo poco a poco un ejemplo para que veáis, paso a paso como se realiza esto. Lo primero es activar clr para que nos permita la integración con .NET tal que así:

USE [basedatosdeejemplo]
go
sp_configure 'clr enabled', 1
go
reconfigure
go

Seleccionamos la base de datos a utilizar, configuramos clr como ‘enabled’ (armado, activado) con el parámetro 1 y ejecutamos reconfigure para que el cambio tenga efecto. Acuérdate de poner el parámetro (en este caso 1) y de ejecutar reconfigure.

El primer paso está realizado. Lo siguiente es crear el ensamblado para la librería, definiendo el esquema y el tipo de permisos. 


CREATE ASSEMBLY [Utilidades]
AUTHORIZATION [dbo]
FROM 'C:\LibreriaEjemplo.dll'
WITH PERMISSION_SET = SAFE
GO

Creamos el ensamblado con el nombre «Utilidades» (podemos darle el que queramos mientras no sea una palabra reservada), con el esquema dbo (podríamos haber utilizado cualquiera de los disponibles), en FROM le especificamos la dirección del archivo en disco mediante una cadena de texto con la ruta y finalmente los permisos, en este caso SAFE.

SAFE es el permiso más restrictivo que hay, el más «seguro» para nuestro equipo pues limita mucho lo que pueda hacer la librería. En caso de que uses liberías de terceros es el permiso que te reportará más seguridad. Existe también el permiso EXTERNAL_ACCESS, que permite que el código acceda a ciertos recursos externos (registro, archivos, red) y el permiso UNSAFE, que da control sin restricciones a la librería sobre los recursos de la máquina. Si usas una librería propia puedes usar UNSAFE, pero si usas una de un tercero piensa que pueden entrañar riesgos de seguridad.

Con lo puesto ya tenemos la librería disponible en nuestra base datos. ¿Y ahora qué? Ahora simplemente puedes usar las clases y métodos de dicha librería en tu base. Puedes crear tus tipos de datos propios usando sus objetos, incluir sus métodos en procedimientos o triggers. En este ejemplo vamos a suponer que la librería importada tiene una clase Point que guarda las coordenadas de un eje X y un eje Y junto a un valor booleano, y tiene también una función GetCoordsAsText que muestra un mensaje largo con las coordendas. Vamos a crear un tipo de datos Point, usarlo en una tabla, acceder a los valores y usar el método GetCoordsAsText en una función. Pondré comentarios para ir explicando el proceso 

--Creamos el tipo de datos Point como un objeto de la clase Point.
--Para acceder a la clase point tenemos que usar el método external name de SQL-Server
--accediendo a la clase mediante el nombre del assembly que creamos antes y el namespace de la clase (en 
--este caso point)
create type Point
external name Utilidades.Point 
go

--Creamos la tabla

create table dbo.Points
(
	id int identity primary key,
	valor Point
);
go

--Para acceder a los datos de la tabla debemos usar un método que nos devuelva
--los valores almacenados dentro del objeto en forma de texto (en este caso .X para el valor X, .Y para 
--el valor Y o .ToString() para sacar ambos en una columna como texto).

select id,
			valor.X as X,
			valor.Y as Y,
			valor.ToString() as Completo
from dbo.Points

--Si intentáramos acceder al objeto a pelo, como voy a poner debajo, nos devolvería el valor del objeto
--sin convertir (un churrazo con la dirección de memoria del puntero)

select valor from dbo.Points --así nos saldría un churo tipo 0x0000000100020000303000000

--También podríamos usar los métodos de la librería en una función, trigger, función de agregado,
--cursor... En este caso haremos una función que acceda a GetCoordsAsText (que está en la clase 
--UserFunctions)

create function dbo.CoordenadasComoTexto
returns nvarchar(50)
as
    external name Utilidades.UserFunctions.GetCoordsAsText

Y con esto tendríais la función que simplemente ejecuta el método definido en la dll y un tipo de datos igual al objeto. Las posibilidades de esto son muy grandes, así que podéis ir profundizando.

Procedimiento que hace una select desde un XML en SQL-Server

en por Donnie Rocken Informática, SQLDeja un comentario

Bueno, estoy realizando un cursillo de SQL-Server2008 R2 cortesía de la Xunta y la UE (hay que aprovechar, que a ver lo que dura la formación gratis en este país). Así que vamos con pequeño ejemplo: un procedmiento que realiza un select sobre un fichero xml, que recibe como parámetro.

Primero vamos a definir una variable como xml (podríamos también importarlo usando un bulk import, pero así veis más clara):

declare @EjemploXML as xml

set @EjemploXML=
'<?xml version="1.0"?>
<SalespersonMods>
    <SalespersonMod SalespersonID="274">
        <Mods>
            <Mod SalesTerritoryID="3"/>
        </Mods>
    </SalespersonMod>
    <SalespersonMod SalespersonID="278">
        <Mods>
            <Mod SalesTerritoryID="4"/>
        </Mods>	
    </SalespersonMod>
<SalespersonMods>'

En fin, ahí tenéis el xml que vamos a recorrer. Como véis la «chicha» está en los atributos SalespersonID y SalesTerritoryID, que son los datos que queremos sacar en la consulta. Ahora os dejo el código y debajo explico todo el procedimiento:


create procedure selectFromXML
@SalesPersonMods xml
as
begin
  declare @XMLdoc int
  exec sp_xml_preparedocument @XMLdoc output, @SalesPersonMods
  select * from
  openxml(@XMLdoc, '/SalespersonMods/SalespersonMod/Mods/Mod')
  with
  (
	SalesTerritoryID int '@SalesTerritoryID',
	SalespersonId int '../../@SalespersonID'
  )

  exec sp_xml_removedocument @XMLdoc
end

En fin, y explicado rápido: Lo primero es crear el procedimiento con create procedure seguido del nombre que queremos darle, y especificando que recibirá como parámetro @SalesPersonMods, que será un xml. Tras eso declaramos un variable entera (la he llamado @XMLdoc) que almacenará un manejador. Para crear dicho manejador usamos el procedimiento almacenado de sistema sp_xml_preparedocument, que recibe como parámetros la variable entera @XMLdoc (definido como output) y la variable xml @SalesPersonMods. Ya tenemos el manejador listo. Ahora queda hacer la select, que empieza como cualquier select normal: select ‘loquesea’ from (en este caso *, o sea, todo). Y en este caso en vez del nombre de tabla irá la función openxml, que recibirá como parámetros el manejador que creamos antes (@XMLdoc) y una cadena de texto con el elemento del xml en el que queremos posicionarnos.

Movernos por el xml es como movernos por MS-DOS, usamos la jerarquía de etiquetas separadas por barras (/) como si fuera una jerarquía de directorios, usando el punto (.) para referirnos a elementos que parten del mismo nivel jerárquico y el doble punto (..) para navegar hacia atrás en la jerarquía. En este caso queremos acceder al atributo SalesTerritoryID, que es un atributo de /Mod (donde nos hemos situado), por lo que no hay que navegar, accedemos a él simplemente con @SalesTerritoryID (a los atributos nos referimos siempre precediéndolos de una arroba, si fuera texto en la etiqueta lo haríamos sin ella) dándole como nombre de columna SalesTerritoryID (definida como entero, en este caso, por ser el valor más adecuado). SalespersonID en cambio está dos niveles más arriba, así que llegamos a él indicándolo con el doble punto: «../../SalespersonID».

Finalmente, tras todas las operaciones, quitamos el manejador de la memoria para liberarla. Si quieres probar la funcionalidad de esto te basta con copiar y todo el código en el QueryManager de SQL-Server. Finalmente ejecuta el procedimiento pasándole como parámetro la variable sql que definimos arriba y comprueba el resultado.