ASIX TRANSACCIONES 1 INTEGRIDAD REFERENCIAL 2 TRANSACCIONES 3

ASIX TRANSACCIONES

1. INTEGRIDAD REFERENCIAL 2. TRANSACCIONES 3. BLOQUEAR TABLAS 4. ALTER TABLE 5. USO DEL JOIN EN EL UPDATE 6. VISTAS 7. COMENTAR LA PRÁCTICA 8. DESCARGA E INSTALACIÓN DE SQL SERVER(SI DA TIEMPO Y SE REPETIRÁ EN CLASE 15)

1. INTEGRIDAD REFERENCIAL(My. Sql) • ON DELETE CASCADE (Si no lo ponemos)- Cuando borremos un cliente no nos dejará borrar sus facturas asociadas. • ON DELETE CASCADE (Si lo ponemos)-Cuando borremos un cliente borrará sus facturas asociadas. • ON UPDATE CASCADE(Si no lo ponemos)- Si modifico un CIF de cliente y este tiene facturas asociadas, no me dejará modificar el CIF de cliente • ON UPDATE CASCADE(Si lo ponemos)- Si modifico un CIF de cliente modificará el CIF en sus facturas asociadas.

1. INTEGRIDAD REFERENCIAL(My. Sql) Código para explicación CREATE TABLE CLIENTES (CIF varchar(9) primary key not null, nombre varchar(30) not null, direccion varchar(50), poblacion set ('Barcelona', 'Teruel', 'Badajoz'), web varchar(60), correo varchar(40)) CREATE TABLE facturas (Idfactura int auto_increment primary key not null, Fechafactura datetime , total decimal(12, 2), iva decimal(10, 2), descuento decimal(10, 2), cif varchar(9) not null, constraint fk_clientes_facturas foreign key(cif) references clientes(cif) on update cascade)

1. INTEGRIDAD REFERENCIAL (MYSQL) On delete restrict Constraint fk_clientes_facturas foreign key(cif) references clientes(cif) ) • • • On update cascade Cuando se modifique un cif de cliente en tabla clientes, se modificará automáticamente en tabla facturas. On delete restict Cuando se borre un cif de cliente en tabla clientes, no dejará que se borre si tiene registros asociados en la tabla facturas. On delete Set null Permite borrar o actualizar un registro en clientes poniendo a NULL el campo o campos relacionados en la tabla facturas(siempre y cuando la FK de facturas no se hay definido NOT NULL)

2. TRANSACCIONES Concepto de Transacción • Conjunto de sentencias de SQL que modifican los datos de la base (DML: insert, update, delete) y que deben ser llevadas a cabo en su totalidad o descartarse por completo. • Objetivo: Motor de BBDD debe asegurar consistencia de los datos.

Propiedades ACID es el acrónimo de Atomicity, Consistency, Isolation and Durability: Atomicidad, Consistencia, Aislamiento y Durabilidad en castellano. Los sistemas que cumplen estas cuatro propiedades garantizan que las transacciones se producen de forma fiable. • Atomicidad: La atomicidad nos asegura que cada transacción sea “todo o nada”. Si se produce un fallo en una parte de una transacción, todos los cambios hechos por la transacción hasta ese momento se deshacen y la base de datos no sufre ningún cambio. • Consistencia: Cualquier operación realizada por una transacción nos ha de asegurar que la base de datos pasa de un estado válido a otro estado válido. Es decir, los datos que escriba una transacción deben cumplir con las restricciones del sistema. • Aislamiento: Esta propiedad asegura que una operación no puede afectar a otra. Por lo que la realización de dos transacciones sobre la misma información se realiza de forma independiente y no genera ningún tipo de error. • Durabilidad: Esta propiedad asegura que una vez finalizada una transacción, ésta persistirá aunque falle el sistema inmediatamente después de la finalización de la transacción.

MODO DE TRABAJO Por defecto, My. SQL viene con el modo AUTOCOMMIT activado. –Cada sentencia SQL es una transacción por sí misma, y si no ocurre ningún problema, las actualizaciones se almacenan en disco. Modo AUTOCOMMIT en My. SQL • Podemos activar y desactivar el modo AUTOCOMMIT de la siguiente manera: –SET autocommit = 0; --Desactivamos –SET autocommit = 1; --Activamos -- Por defecto • Con el AUTOCOMMIT desactivado habrá siempre una transacción abierta, que tendremos que terminar con las sentencias COMMIT o ROLLBACK

2. TRANSACCIONES TIPOS DE TRANSACCIONES • INDIVIDUALES • EXPLICITAS INDIVIDUALES • Cada sentencia es considerada una transacción. • Es decir, cuando se ha terminado la ejecución de una sentencia, esta se da por definitiva, por el contrario, si se produce algún error es descartada en su totalidad. • Así trabaja My. Sql por defecto, ODBC utiliza este modo de trabajo.

COMMIT / ROLLBACK • La sentencia COMMIT hace que los cambios realizados en la transacción actual se conviertan en permanentes y se vuelvan visibles para el resto de usuarios • La sentencia ROLLBACK cancela todas las modificaciones producidas en la transacción actual.

SAVEPOINT • Los SAVEPOINT son puntos de control que se sitúan dentro de una transacción. • Cuando se efectúa un ROLLBACK al final de una transacción, éste puede ser total (se deshacen todas las operaciones de la transacción) o hasta uno de los puntos de control de la transacción.

SAVEPOINT • Crearemos un punto de control de la siguiente forma: –SAVEPOINT nombre_punto_control; • Si creamos un punto de control con el mismo nombre que otro creado anteriormente, el viejo queda substituido por el nuevo. • Para deshacer los cambios hasta un determinado punto de control escribiremos: – ROLLBACK TO nombre_punto_control

Ejemplo con tabla clientes start transaction; insert into clientes(idcliente, nombre, poblacion) values (10, 'cli 4', 'Barcelona'); savepoint P 1; insert into clientes(idcliente, nombre, poblacion) values (11, 'cli 5', 'Barcelona'); savepoint P 2; insert into clientes(idcliente, nombre, poblacion) values (12, 'cli 6', 'Barcelona'); SELECT * FROM clientes; rollback TO P 1; -- Deshacemos los insert de 'cli 5' y 'cli 6' SELECT * FROM clientes; rollback TO P 2; /* Dará error (al haber hecho rollback a P 1 desaparece P 2) */ commit;

2. TRANSACCIONES CONCURRENCIA • Al trabajar en un entorno de aplicación con múltiples usuarios, cada uno de los que intenta realizar tareas en el servidor, enviará conjuntos de sentencias agrupadas en transacciones. Si varias transacciones se refieren a los mismos datos, se debe conseguir que cada transacción pueda completarse sin intromisiones. • De modo que cuando se produzca una transacción, el servidor bloqueara los datos a los que accede la transacción en cuestión, haciendo que el resto de transacciones deban esperar su turno.

2. TRANSACCIONES EJEMPLO DE TRANSACCIONES • Dadas las siguiente tablas: CLIENTES y CUENTAS CORRIENTES(1 N) Dni nombre apellido direccion poblacion telefono 1 FELIPE LOTAS CANTABRIA 3 BARCELONA 934528693 2 JUAN FERNANDEZ PARIS 14 BARCELONA 936582587 3 PEDRO MARIN ORIENTE 3 HOSPITALET 936582314 4 MARTA MARTI C/ CARRETERA 3 TERUEL 933285245 5 CARLOS IRISARRI C/ CASTELLOTE 4 ALCAÑIZ 932504589 6 JORGE SALAS C/ MAYOR 3 TERUEL 933145896 Idcta sucursal saldo dni 14 -235 ALCAÑIZ 13000 1 14 -247 ALCAÑIZ 2000 5 14 -325 ALCAÑIZ 6000 5 15 -427 TERUEL 14000 6 15 -220 TERUEL 3000 4 15 -337 TERUEL 3700 4 17 -425 BARCELONA 10000 2

2. TRANSACCIONES EJEMPLO TRANSACCION • El cliente Jorge Salas quiere traspasar de su cuenta 15 -427, 1. 000 € a la cuenta 14 -325 de Carlos Irisarri. start transaction update cuentascorrientes set saldo=saldo-1000 where dni=6 and idcta='15 -427‘ update cuentascorrientes set saldo=saldo+1000 where idcta='14 -325‘ and dni=5 commit

ACTIVIDAD Traspasar 2000 € de la cuenta 14 -235 a la 15220.

SOLUCIÓN ACTIVIDAD

3. BLOQUEAR TABLAS • Podemos prevenir la modificación de ciertas tablas, bloqueando el acceso a éstas en ciertos momentos. De esta forma, la(s) tabla(s) bloqueada(s) no aceptará(n) accesos de lectura o escritura de otras sesiones. • Se usa cuando queremos leer algo y luego modificarlo, para evitar que entre medio alguien modifique esa información

3. BLOQUEAR TABLAS • La sintaxis para bloquear tablas es la siguiente: –LOCK TABLES nombre_tabla 1 READ | WRITE, nombre_tabla 2 READ | WRITE … • Si un usuario realiza un bloqueo READ, ese usuario y todos los demás sólo podrán leer la tabla • Si un usuario realiza un bloqueo WRITE, sólo ese usuario puede leer o escribir en la tabla, el resto de usuarios serán bloqueados.

3. BLOQUEAR TABLAS Ejemplo bloqueo con READ -- Bloqueo con read LOCK TABLE clientes READ INSERT INTO `prueba`. `CLIENTES` (`idcliente`, `nombre`) VALUES ('789', 'pedrito'); -- Al estar bloqueada no nos deja modificarla SELECT * FROM CLIENTES UNLOCK TABLES -- Sí que podemos leerla

3. BLOQUEAR TABLAS Ejemplo bloqueo con WRITE -- Bloqueo con WRITE LOCK TABLE clientes WRITE INSERT INTO `prueba`. `CLIENTES` (`idcliente`, `nombre`) VALUES ('789', 'pedrito'); -- Al estar bloqueada WRITE nos dejará actualizar SELECT * FROM CLIENTES -- Sí que podemos leerla UNLOCK TABLES

3. BLOQUEAR TABLAS Cuando se ejecuta un LOCK TABLES se hace un COMMIT de todas las transacciones abiertas. • Podemos bloquear todas las tablas de todas las BBDD (por ejemplo, para realizar una copia de seguridad) con la sentencia: –FLUSH TABLES WITH READ LOCK.

3. BLOQUEAR TABLAS Cuando se crea un bloqueo para acceder a una tabla, dentro de la zona de bloqueo no podremos acceder a otras tablas (todas las tablas del servidor) hasta que no se finalice el bloqueo: LOCK TABLE clientes READ; SELECT * FROM clientes; SELECT * FROM filial; --Esta línea nos dará un error

3. Desbloqueo de tablas • Podemos desbloquear todas las tablas que estén bloqueadas con la sentencia: –UNLOCK TABLES • Si acaba la conexión (normal o anormalmente) antes de desbloquear las tablas, éstas se desbloquean automáticamente.

4. ALTER TABLE Ejemplo 1: Adición de una columna: ALTER TABLE CLIENTES ADD codrep varchar(20) NULL Ejemplo 2: Añadir una columna en un sitio en concreto, en este caso después de dirección ALTER TABLE CLIENTES ADD TELEFONO varchar(12) AFTER DIRECCION Ejemplo 3: Añadir una columna como primer campo de una tabla ALTER TABLE CLIENTES ADD CIF_CLIENTE VARCHAR(15) FIRST

4. ALTER TABLE Ejemplo 4: Supresión de restricción FOREIGN KEY: ALTER TABLE filial DROP FOREIGN KEY FK_FILIAL_CLIENTES Ejemplo 5: Añadir una restricción ALTER TABLE filial ADD CONSTRAINT FK_FILIAL_CLIENTES FOREIGN KEY(idcliente) REFERENCES clientes(idcliente) ON DELETE CASCADE ON UPDATE CASCADE

5. USO DEL JOIN EN EL UPDATE Objetivo: Todas nuestras filiales tengan un presupuesto que sea el 10% de la facturación de la empresa madre, es decir el cliente.

5. USO DEL JOIN EN EL UPDATE PASO 1: Creo el campo PRESUPUESTO en la tabla FILIAL ALTER TABLE FILIAL ADD COLUMN (presupuesto DECIMAL (12, 2) NOT NULL); PASO 2: Actualizamos dicha columna con el 10% del presupuesto del cliente UPDATE filial f JOIN clientes c ON f. idcliente=c. idcliente SET f. presupuesto = (c. facturacion * 0. 10);

VISTAS Definición • Es una sentencia select constituida como un objeto en My. Sql separado de la tabla o tablas a las que hace referencia en su definición. • La vista no es una tabla , sino la definición de una consulta y, por tanto, no posee datos propios. • Una vez que esta definida, puede utilizarse exactamente del mismo modo que una tabla, referenciandola por su nombre.

VISTAS • • Una vista fundamentalmente tiene dos utilidades, como mecanismo de seguridad y como herramienta para facilitar el uso de consultas. Mecanismo de seguridad: Si queremos que unos usuarios vean sólo una parte de los datos de una tabla, puede crearse una vista en la que dichos datos no aparezcan y autorizar a los usuarios a trabajar con la vista en lugar de con la tabla. Así conseguiremos personalizar la visión de la base de datos para cada usuario. EJEMPLO Si tengo una tabla con mis clientes donde hay un campo que especifica a qué población pertenece cada cliente, y me interesa que los trabajadores de la oficina de Barcelona solamente puedan trabajar con los clientes de Barcelona, entonces puedo crear una vista de los clientes de Barcelona y a los empleados de la oficina de Barcelona les permito acceder a la vista y no a la tabla. • Facilidad de utilización: El administrador de la BBDD puede realizar órdenes SELECT complejas y almacenarlas como vistas, de manera que usuarios noveles invocarán a la vista en vez de desarrollar el SELECT complejo.

VISTAS • Esta orden crea una vista nueva o reemplaza una vista existente si ponemos la cláusula OR REPLACE. La Orden_select es un SELECT que proporciona la definición de la vista. CREATE VIEW [OR REPLACE] nombre_vista AS Orden select [WITH CHECK OPTION] • La cláusula WITH CHECK OPTION obliga a que las nuevas filas o las filas modificadas sobre la vista, cumplan los criterios del SELECT que define la vista.

VISTAS -Ejemplo. Partimos de la tabla CLIENTES que hemos utilizado en el apartado tres de estos apuntes. Objetivo: crear una vista para que los usuarios de la oficina de Barcelona solamente accedan a los clientes de Barcelona. Nuestro objetivo es que los empleados de la oficina de Barcelona solamente puedan acceder a los clientes de Barcelona, por lo que no podrán acceder ni gestionar clientes de otras poblaciones. CREACIÓN DE LA VISTA Creamos una vista llamada clientebarna, con una consulta que filtra los clientes de Barcelona de la tabla clientes. CREATE VIEW clientebarna AS SELECT * FROM clientes WHERE poblacion='barcelona'

VISTAS -Ejemplo. CONSULTAR LA VISTA SELECT * FROM clientebarna Fijaos cómo consultamos una vista como si fuera una tabla, así el usuario no sabe si consulta una vista o una tabla INSERTAR ELEMENTOS EN LA VISTA INSERT INTO clientebarna(idcliente, nombre, poblacion) VALUES (‘ 2309012', 'benq', 'Barcelona') Insertamos un registro sobre la vista. ¿Guarda el registro la vista? NO!!!, el registro se almacena en la tabla

VISTAS – Añadiendo clausula WITH CHECK OPTION

Explicación de la práctica