7. Acceso a Datos en PHP con SQL¶

En este tema aprenderemos a:

Usar un SGBDR como almacén de datos de nuestra aplicación
Conectar el servidor web con el servicio de base de datos
Usar SQL dentro de nuestra aplicación usando el modelo DAO

Está organizado en 8 talleres de unas 3 sesiones de clase. En total unas 24 sesiones de clase más tu trabajo en casa.

La dedicación depende del conocimiento previo, motivación y capacidad de aprendizaje del estudiante para esa sesión en concreto.

Video Clase

7.1. Almacenes de Datos en archivo¶

Video Clase

Arquitectura Web (Arquitectura y Desarrollo Web)
1. Diferentes arquitecturas (y patrones de diseño)
2. Por ejemplo un modelo en tres capas:
Persistencia (permanencia) de los datos.
1. En la lógica (estructuras de datos del programa y sesión HTTP)
2. En archivos (delegamos en el sistema de archivos)
3. En un SGBD (delegamos en un sistema especializado)
4. En un API (cada vez más utilizado)
El concepto es el mismo (persistencia), la solución diferente
Operaciones CRUD
1. Crear (C)
2. Leer (R)
3. Actualizar (U)
4. Borrar (D)

Actividad T07-A01. Usando archivos como almacén de datos

Desarrolla una aplicación web que permita gestionar un almacén de datos de viajeros (dni, nombre, apellidos y fecha de nacimiento) usando un archivo (viajeros.json) donde implementarás las operaciones CRUD.

Create: usas un formulario para añadir usuarios al almacén
Read: creas una página que muestra los viajeros de tu lista y con enlaces a una página que muestra el detalle para ese viajero. Para cada viajero, habrá tres botones: mostrar (para ver los detalles del usuario)
Update: en el botón actualizar
Delete: en el botón borrar

7.2. Almacenes de Datos en un SGBDR¶

Video Clase

¿Porqué interesa usar un SGBDR como almacén de datos?
1. Por casi todo:
  1. Arquitectura
  2. Datos
  3. Operación
2. Pero a un coste:
  1. Rendimiento
  2. y gestión de un sistema extra (a veces)
Repasa lo visto en BD - Bases de Datos
1. Diseño Conceptual, Lógico y Físico de una BD
2. Lenguaje DDL:
  1. Estructura (schema) de una BD
  2. Diseño físico en sql
3. Lenguaje DML:
  1. Consultas SQL. Operación R(ead)
  2. Inserción, Actualización y Borrado. Operaciones C(reate) U(pdate) D(elete)

Actividad T07-A02a. Repasando SQL

Es importante que repases SQL para tener soltura con el manejo del lenguaje. Para repasar puedes revisar Ejercicios para usar SQL (con sqlite) o Ejercicios de SQL sobre BD Northwind (W3Schools) (sgbdr online)

Actividad T07-A02b. Usando un SGBDR

Diseña un almacén de datos que te permita gestionar y acceder a los datos de una aplicación de gestión de viajes sencilla. Se cumplirán los siguientes requisitos:

Necesitamos conocer la información de los viajeros y los vuelos que hacen.
Los viajeros son españoles o con residencia en España, y queremos saber el día que nacen para saber cual es su edad y felicitarle el cumpleaños. Un viajero puede hacer muchos viajes
De los viajes queremos conocer de qué ciudad es el origen y cual es el destino, y claro la fecha (de salida y llegada). Ah! Y de cada ciudad queremos saber cuantos habitantes tiene y una descripción sencilla de 150 caracteres máximo con un recomendación de cosas para ver.

Este es un ejercicio de repaso de diseño conceptual, lógico y físico. Con esta BD trabajaremos en los siguientes clases. El resultado será un BD a la que te podrás conectar:

Creas los objetos de la base de datos
Le añades al menos 3 registros de ejemplo de contenido (para no tener sólo la estructura, sino también el contenido)
Y así tendrás una BD a la que poder acceder desde consola o en tu aplicación

La BD a usar será postgres.

7.3. Conexión a la BD¶

Video Clase

Tres alternativas de conexión (en PHP)
1. Específicas al SGBDR:
  1. MySQLi (object-oriented)
  2. MySQLi (procedural)
2. book.pdo. PHP Data Object

Configuración Inicial (php_mysql_connect)

Definir los parámetros de la cadena de conexión

<?php
     // Depende del SGBDR que uses
     // pero suele ser lo mismo casi siempre
     // Supongamos que le llamamos cadena_conexion.inc

     $equipo_BD="localhost";  // el nombre del equipo donde está la BD
     $nombre_BD="myDB";   // el nombre de la BD (recuerda que puede haber varias)
     $usuario_BD="root"; // el usuario con permisos sobre esa BD
     $password_BD="";   // la contraseña.

     // Ojo a no publicar estos datos y
     // a revisar la conexión si usas un sistema en producción (en desarrollo puede dar igual)
?>

Realizar la conexión

<?php
       require_once 'cadena_conexion.inc'

       try{
            $dbConn= new PDO("mysql:host=$equipo_BD;dbname=$nombre_BD",$usuario_BD,$password_BD);
            echo "La conexión fue bien";
       } catch(Exception $e){
            echo "Falló la conexión con error: " . $e->getMessage();
       }
?>

Comprobar errores usando excepciones (bloque try .. catch)
Cerrar la conexión (cuando ya no la utilizo)

Usando Excepciones (php_exceptions)

Las excepciones son un objeto para gestionar posibles errores
Se pueden crear (vía throw) a medida del usuario. P.ej. para la gestión de una conexión a la BD
Se gestionan ejecutando (vía try) y capturando, si existe, la excepción

<?php
       function divide($dividend, $divisor) {
           if($divisor == 0) {
              throw new Exception("No puedes dividir por 0");
           }
           return $dividend / $divisor;
       }

       try {
           echo divide(5, 0);
       } catch(Exception $e) {
           echo "Imposible realizar la división: " . $e;
       } finally {
           echo "Process complete.";
       }
?>

Actividad T07-A03. Conectando la aplicación al SGBDR

Conecta tu servidor de aplicaciones con el SGBDR (depende del caso concreto). Usas el patrón DAO usando PDO. En una página sencilla:

Creas una base de datos que se llame Viajes. Usas los requisitos de la clase anterior
Tu aplicación se conectará y, si todo va bien, te dirá que «conexión ok»
Y, si algo va mal, mostrará «no tengo conexión con la BD» (y te dirá el error que devuelve). Prueba a que algo vaya mal (por ejemplo apaga el SGBDR)

7.4. Consultar la base de datos¶

Video Clase

Necesitamos una forma de consultar la BD usando la clausula SELECT

Problema de inyección sql :

Se ejecuta código sql no deseado porque no se parametriza la consulta (no se revisan los parámetros):

consulta := "SELECT * FROM usuarios WHERE nombre ='" + nombreUsuario + "';"

// Pero si no revisas bien qué valor tiene             nombreUsuario, podría convertirse en lo que no          quieres, por ejemplo

$resultado_esperado = "SELECT * FROM usuarios WHERE nombre ='Pepe';"
$resultado_real = "SELECT * FROM usuarios WHERE nombre = 'Pepe';
DROP TABLE usuarios;
SELECT * FROM datos WHERE nombre LIKE '%';  "

La solución es preparar las consultas usando PDO:

$statement = $pdo->prepare("SELECT * FROM usuarios WHERE nombre = :nombre");
$statement->bindParam(':nombre', $nombre_usuario);
$statement->execute();
$result = $statement->fetch();

Preparación de Sentencias php_mysql_select

// Suponemos que está ya la cadena de conexión definida

try {
   $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

   // Se prepara la sentencia
   $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests");
   $stmt->execute();

   // Se pueden especificar diferentes modos de respuestas

   $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

   // Se puede utilizar diferentes métodos del objeto PDO  ($stmt).

   foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
       echo $v;
   }
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

Parametrización de la sentencia. Además de la preparación de la sentencia, parametrizas la consulta (vinculando variables a un parámetro )

// Muestro sólo donde cambia
// $limite = 10;

// Esta es la versión que recomiendo (pero puedes usar interrogaciones también
  $stmt = $conn->prepare("SELECT id FROM Viajes WHERE id > :limite");

  // Se vincula la variable al parámetro
  $stmt.bindParams(:limite,$ejemplo)
  $stmt->execute();

  } catch(PDOException $e) {
      echo "Error: " . $e->getMessage();
  }

Actividad T07-A04. Consultar la BD

Usando la BD Viajes, que previamente tendrá al menos tres registros en cada tabla. Crea una aplicación web que responda a las siguientes consultas

Muestra todos los viajeros registrados
Muestra todos los viajes registrados
¿Cuántos viajeros registrados hay?
¿Cuántos viajes registrados hay?
¿Quien es el viajero de menor edad?

7.5. Operaciones Insert / Update / Delete¶

Video Clase

Completando las sentencias DML (Modificando la BD )
1. Insertar Datos
2. Actualizarlos
3. Borrarlos

Insertar Datos (php_mysql_insert):

<?php

// Es útil crear un objeto con los datos a insertar, simplifica el código

$data = [
        'nombre' => $nombre,
        'apellido' => $apellido,
        'edad' => $edad,
];

// Depende del modelo de datos, los nombres de los campos no tienen que coincidir
// Ojo a preparar y parametrizar la instrucción

$sql = "INSERT INTO usuarios (n, s, e) VALUES (:nombre, :apellido, :edad)";
$stmt= $pdo->prepare($sql);
$stmt->execute($data);

?>

Actualizar Datos (php_mysql_update)

<?php

   // Resulta que el nombre del usuario 34 es Pepe

   $nombre = 'Pepe';

   $sql = "UPDATE usuarios SET n = :nombre where id = 34";
   $stmt= $pdo->prepare($sql);

   // Recuerda asignar la variable al parámetro
   // Puede ser con bindParam, con parámetros (ejemplo anterior) o con parámetros posicionales (con números)
   $stmt->bindParam(':nombre', $nombre);
   $stmt->execute($data);

   ?>

Borrar Datos (php_mysql_delete)

<?php

   // Resulta quieres borrar el usuario 34

   $id = 34;

   $sql = "DELETE FROM usuarios where id = 34";
   $stmt= $pdo->prepare($sql);

   // Vinculo el parámetro, usando un array
   $stmt->execute(array ('id' => 5));

?>

Construyendo la aplicación para el usuario
1. Entrada: formularios HTML dinámicos (se construyen en base a la información de la BD)
2. Validación y Preparación de datos (depende de la operación CRUD a implementar)
3. Operación con el Almacén de datos
4. Respuesta, en HTML, al usuario

Actividad T07-A05. Modificando la BD (desde una aplicación web)

Seguimos usando la BD de Viajes, modificando su contenido desde una aplicación web. Vas construyendo tu aplicación para que el usuario pueda:

Añadir viajeros, viajes y ciudades. Depende cómo sea tu modelo de datos (ojo a las FK)
Actualizar los datos de un usuario
Borrar un registro en cada una de las tablas que tengas

7.6. Transacciones¶

Video Clase

Repasa el concepto de transacción :
1. Grupo de instrucciones …
2. … que se ejecutan (commit) de forma completa …
3. … o se anulan (rollback) bajo ciertas condiciones
4. Un sistema transaccional cumple con las propiedades ACID
  1. Atomicity
  2. Consistency
  3. Isolation
  4. Durability
Es muy fácil añadir esa capacidad transaccional a nuestra aplicación:
1. El SGDB debe soportarlo (ACID Compliant)
2. Hay que definir que es una transacción para …
3. … deshabilitar el modo auto-commit (sino se ejecuta cada instrucción de forma individual)
4. Un ejemplo sencillo :
  1. Comienzas la transacción (pdo.begintransaction)
  2. Se van ejecutando diferentes instrucciones (o solo una, en el caso más simple)
  3. Confirmas la transacción (pdo.commit) pero …
  4. … revisas si hay algún problema. Y la anulas (pdo.rollback) si lo necesitas
La estrategia, muchas veces es la misma, agrupar un conjunto de sentencias (o sólo una, para simplificar) y usar el control transaccional que te da PDO

Actividad T07-A06. Añadiendo transacciones a nuestra aplicación

Seguimos usando la BD de Viajes. La idea es entender el uso de los métodos PDO para las transacciones, por ejemplo:

Que si vas a insertar una ciudad, que no está en la BD, aborte la transacción y te lance a la página de «crear ciudad»
Que si añades una persona con el mismo nombre, se aborte la transacción
Que si una persona tiene registrados más de 10 viajes, no se le permita agregar uno más.

Realmente las transacciones, involucran modificación de la BD, como ya visteis el año pasado, pero aquí la idea es saber cómo usar PDO para usarlas (no son tan buenos ejemplos de transacciones como tal).

7.7. APIs con BD¶

Video Clase

Podemos usar un API para el acceso al almacén de datos
1. Aisla totalmente la capa de datos (se convierte en microservicio)
2. Independiente del SGBD utilizado (lo hace más flexible)
3. Con las ventajas (y costes) de las APIs (REST en nuestro contexto)
Arquitectura de la aplicación:
1. Aplicación del almacén de datos (API)
  1. Decides el servidor de aplicaciones y el SGBD.
  2. Decides el sistema de autenticación (muy importante en este caso)
  3. Desarrollas tu aplicación (como API RESTFul)
2. Aplicación del Usuario:
  1. Usa el almacén de datos a través del API
  2. Tienes total flexibilidad: puedes usar un almacén de datos, o varios, desde una aplicación o varias (concepto de los microservicios)
En la práctica es muy útil el uso de POO para el desarrollo del API, aprovechando el uso de PDO.

Actividad T07-A07. Refactoriza tu Aplicación de Viajes (operaciones R)

Refactoriza la aplicación de viajes para usar un API para la consulta del almacén (sólo consultas). Esto implica desarrollar dos aplicaciones web servidor (una podría ser cliente, pero estamos en contexto de programación servidor):

Un servicio web para el API
Un servicio web para la aplicación

Las parte de modificación de la BD la dejas pendiente de desarrollo (clase siguiente) pero tienes que actualizar los scripts para que devuelvan algún tipo de información al usuario (p.ej que no está disponible la inserción de datos)

7.8. Ampliar la funcionalidad¶

Video Clase

El uso del API permite ampliar:
1. La plataforma hardware: usar otro SGBD, mejorar la velocidad de respuesta, etc
2. La funcionalidad software: añadir funcionalidad

Actividad T07-A08. Refactorizar Aplicación Viajes (API RESTFul)

Añade al API las acciones DML para tener tu aplicación de viajes totalmente refactorizada.