Ejercicios - Bases de datos (3 B) - Enunciados

En construcciónEsta página de ejercicios está en elaboración.

Para facilitar la realización de los ejercicios, se proporcionan unas plantillas de los ejercicios Bases de datos (3 B) (Bases de datos).

Para ayudarle en la realización de estos ejercicios puede consultar una página de comentarios con fragmentos de código.

Puede descargar unas posibles soluciones de estos ejercicios en la página de soluciones.

Bases de datos (3 B) - 0 - Aplicación de ejemplo

Esta aplicación es la aplicación que se puede utilizar de base para el resto de ejercicios (es idéntica al ejercicio Bases de datos (3) 4).

Enlace a ejemplo

Bases de datos (3 B) - 1 - Mejoras en la aplicación (1)

Implemente estas mejoras en la aplicación:

  1. Al borrar todo, ofrecer la opción de insertar automáticamente algunos registros de prueba.
  2. En SQLite, si no existe el directorio donde se quiere guardar la base de datos, mostrar un mensaje indicando el problema.
    Ayuda (haga clic aquí si no se le ocurre cómo hacerlo)

    Para resolver este apartado puede utilizar las funciones dirname() e is_dir()

  3. Al modificar el registro de un usuario, ofrecer la posibilidad de mantener la contraseña (por ejemplo, marcando una casilla).
  4. Hacer que no se aplique el límite del número de registros en las tablas si el valor de $cfg["dbPersonasMaxReg"] o $cfg["dbUsuariosMaxReg"] es 0 o negativo.
Enlace a ejemplo

Bases de datos (3 B) - 2 - Mejoras en la aplicación (2)

Implemente estas mejoras en la aplicación:

Bases de datos (3 B) - 3 - Nueva base de datos: PostgreSQL

Añadir a la aplicación del ejercicio 1 la posibilidad de trabajar con una sistema gestor de bases de datos distinto, por ejemplo PostgreSQL.

  1. Descargue PostgreSQL. En febrero de 2022, cuando se redactó este ejercicio, la última versión disponible para Windows era la versión 14.1, que puede descargar directamente con este enlace: PostgreSQL 14.1 para Windows (284 MB).
  2. Instale PostgreSQL.
    • Contraseña del usuario postgres: postgres
    • Puerto: 5432
    • Configuración regional: Configuración regional por defecto
  3. Abra pgadmin4. Al entrar en pgadmin4 le pedirá primero un master password: master. Y a continuación la contraseña del usuario postgres: postgres.
  4. Con pgadmin4, cree un rol (en postgreSQL, rol es lo mismo que usuario):
    • General >: Name: iaw
    • Definition > Password: iaw
    • Privileges >: Can login
  5. Con pgadmin4, cree un rol (en postgreSQL, rol es lo mismo que usuario) y cree una base de datos con el mismo nombre que el usuario:
    • General > Owner: iaw
    • Definition > Encoding: UTF8
    • Definition > Collation: Spanish_Spain.1252
    • Definition > Character type: Spanish_Spain.1252
  6. En el archivo de configuración de PHP, active la extensión PDO para PostgreSQL. El nombre PostgreSQL se suele abreviar como pgsql.
  7. Cree un archivo biblioteca-postgresql.php a partir de biblioteca-sqlite.php y modifique los archivos biblioteca.php y config.php para poder trabajar con PostgreSQL.
  8. [comunes/config.php]

    En PostgreSQL para realizar la conexión se deben proporcionar cinco parámetros (host, puerto, base de datos, usuario y contraseña).

    // Configuración para PostgreSQL
    
    $cfg["pgsqlHost"]     = "pgsql:host=localhost";             // Nombre de host
    $cfg["pgsqlPort"]     = "5432";                             // Número de puerto
    $cfg["pgsqlDatabase"] = "iaw";                              // Nombre de la base de datos
    $cfg["pgsqlUser"]     = "iaw";                              // Nombre de usuario
    $cfg["pgsqlPassword"] = "";                                 // Contraseña de usuario
    
  9. [comunes/biblioteca-postgresql.php]

    En PostgreSQL, cuando nos conectamos a la base de datos nos conectamos con alguna base de datos ya existente. Esa base de datos no la podemos borrar puesto que es la base de datos activa y la que nos permite estar conectados. Por eso la función borraTodo() para PostgreSQL es como la función borraTodo() para SQLite, que borra y crea las tablas, y no como la función borraTodo() para MySQL, que borra y crea la base de datos.

    Esta puede ser la función de conexión para PostgreSQL:

    // POSTGRESQL: Conexión con la base de datos
    
    function conectaDb()
    {
        global $cfg;
    
        try {
            $conexion = "$cfg[pgsqlHost];port=$cfg[pgsqlPort];dbname=$cfg[pgsqlDatabase];user=$cfg[pgsqlUser];password=$cfg[pgsqlPassword]";
            $tmp = new PDO($conexion);
            $tmp->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
            return $tmp;
        } catch (PDOException $e) {
            print "    <p class=\"aviso\">Error: No puede conectarse con la base de datos. {$e->getMessage()}</p>\n";
            exit;
        }
    }
    

    Para crear un campo autonumérico en PostgreSQL, se suele utilizar el pseudo-tipo SERIAL, como muestra el siguiente ejemplo:

    // POSTGRESQL: Borrado y creación de tablas
    
        $consulta = "CREATE TABLE $cfg[dbUsuariosTabla]  (
                     id SERIAL,
                     usuario VARCHAR($cfg[dbUsuariosTamUsuario]),
                     password VARCHAR($cfg[dbUsuariosTamPassword]),
                     nivel INTEGER NOT NULL,
                     PRIMARY KEY(id)
                     )";
    

    La consulta para comprobar la existencia de una tabla en PostgreSQL sería la siguiente:

    // POSTGRESQL: Comprobación de existencia de las tablas
    
            $consulta = "SELECT COUNT(*) FROM information_schema.tables WHERE table_name='$tabla'";
    
  10. [comunes/demo.php]

    Para que después de insertar los registros de prueba, el contador autonumérico de las claves principales se actualice, realice las siguientes consultas:

      
        if ($cfg["dbMotor"] == POSTGRESQL) {
            foreach ($cfg["dbTablas"] as $tabla) {
                $consulta  = "SELECT setval(pg_get_serial_sequence('$tabla', 'id'), coalesce(max(id),0) + 1, false) FROM $tabla";
                $resultado = $pdo->query($consulta);
                if (!$resultado) {
                    print "    <p class=\"aviso\">Error al ejecutar la consulta. SQLSTATE[{$pdo->errorCode()}]: {$pdo->errorInfo()[2]}</p>\n";
                } else {
                    print "    <p>Contador autonumérico restablecido.</p>\n";
                    print "\n";
                }
            }
        }
      
  11. Compruebe el funcionamiento del programa insertando como administrador registros de Usuarios y Personas, entrando como alguno de los usuarios básicos y creando registros de Personas.

Bases de datos (3 B) - 4 - Agenda multiusuario (1)

Modifique la aplicación del ejercicio 1 de manera que cada usuario básico de la aplicación sólo pueda trabajar con sus propios registros de Personas (listar, borrar, buscar, modificar).

  1. Añada en la tabla Personas un campo que contenga el id del usuario que crea el registro.
    Ayuda (haga clic aquí si no se le ocurre cómo hacerlo)
    // SQLITE:
        $consulta = "CREATE TABLE $cfg[dbPersonasTabla]  (
                     id INTEGER PRIMARY KEY,
                     nombre VARCHAR($cfg[dbPersonasTamNombre]) COLLATE NOCASE,
                     apellidos VARCHAR($cfg[dbPersonasTamApellidos]) COLLATE NOCASE,
                     telefono VARCHAR($cfg[dbPersonasTamTelefono]) COLLATE NOCASE,
                     correo VARCHAR($cfg[dbPersonasTamCorreo]) COLLATE NOCASE,
                     id_usuario INTEGER,
                     FOREIGN KEY(id_usuario) REFERENCES $cfg[dbUsuariosTabla](id) ON DELETE CASCADE ON UPDATE CASCADE
                     )";
    
    // MYSQL:
                     $consulta = "CREATE TABLE $cfg[dbPersonasTabla]  (
                                  id INTEGER UNSIGNED AUTO_INCREMENT,
                                  nombre VARCHAR($cfg[dbPersonasTamNombre]),
                                  apellidos VARCHAR($cfg[dbPersonasTamApellidos]),
                                  telefono VARCHAR($cfg[dbPersonasTamTelefono]),
                                  correo VARCHAR($cfg[dbPersonasTamCorreo]),
                                  id_usuario INTEGER UNSIGNED,
                                  FOREIGN KEY(id_usuario) REFERENCES $cfg[dbUsuariosTabla](id) ON DELETE CASCADE ON UPDATE CASCADE,
                                  PRIMARY KEY(id)
                                  )";
    
  2. Al identificarse como usuario, guarde una variable de sesión con el id del usuario conectado.
    Ayuda (haga clic aquí si no se le ocurre cómo hacerlo)
    $_SESSION["id_usuario"]   = $registro["id"];
    
  3. Los usuarios básicos sólo podrán administrar sus propios registros (listar, borrar, buscar, modificar).
    Ayuda (haga clic aquí si no se le ocurre cómo hacerlo)

    Basta con añadir en las consultas la condición de que el campo de usuario (que indica el usuario que creó el registro) coincide con el usuario conectado. Por ejemplo:

        $consulta = "SELECT * FROM $cfg[dbPersonasTabla]
                     WHERE id_usuario = $_SESSION[id_usuario]
                     ORDER BY $ordena";
    
  4. Un usuario básico podrá insertar un registro de Personas con los mismos datos que un registro de otro usuario, pero no repetir un registro propio.
  5. Los usuarios administradores podrán listar, borrar y buscar todos los registros de Personas.
    Ayuda (haga clic aquí si no se le ocurre cómo hacerlo)

    Las consultas deberá ser diferentes en función del nivel del usuario conectado. Si el nivel es administrador, las consultas deberán combinar la tabla Personas con la tabla Usuarios para mostrar el nombre de usuario del usuario que creó el registro. Por ejemplo:

    if ($_SESSION["nivel"] == NIVEL_ADMINISTRADOR) {
        $consulta = "SELECT
                         personas.id,
                         personas.nombre,
                         personas.apellidos,
                         personas.correo,
                         personas.telefono,
                         usuarios.usuario
                     FROM $cfg[dbPersonasTabla] as personas
                     JOIN $cfg[dbUsuariosTabla] as usuarios
                     ON personas.id_usuario = usuarios.id
                     ORDER BY $ordena";
    } else {
      $consulta = "SELECT * FROM $cfg[dbPersonasTabla]
                   WHERE id_usuario = $_SESSION[id_usuario]
                   ORDER BY $ordena";
    }
    
  6. En los listados (listar, borrar, buscar, modificar) los usuarios administradores verán el nombre del usuario que creó el registro.
    Ayuda (haga clic aquí si no se le ocurre cómo hacerlo)

    La columna Usuario se mostrará cuando el nivel del usuario conectado sea el nivel administrador.

    
        if ($_SESSION["nivel"] == NIVEL_ADMINISTRADOR) {
            print "            <th>\n";
            print "              <button name=\"ordena\" value=\"usuario ASC\" class=\"boton-invisible\">\n";
            print "                <img src=\"../../img/abajo.svg\" alt=\"A-Z\" title=\"A-Z\" width=\"15\" height=\"12\">\n";
            print "              </button>\n";
            print "              Usuario\n";
            print "              <button name=\"ordena\" value=\"usuario DESC\" class=\"boton-invisible\">\n";
            print "                <img src=\"../../img/arriba.svg\" alt=\"Z-A\" title=\"Z-A\" width=\"15\" height=\"12\">\n";
            print "              </button>\n";
            print "            </th>\n";
        }
    ...
            if ($_SESSION["nivel"] == NIVEL_ADMINISTRADOR) {
                print "            <td>$registro[usuario]</td>\n";
            }
    
    
  7. Compruebe que los usuarios administradores pueden ordenar los registros de personas por el campo usuario.
  8. Los usuarios administradores no podrán insertar registros de Personas a nombre de otros usuarios, ni modificar el campo usuario de un registro de Personas (esta funcionalidad se deja para el ejercicio siguiente).
Enlace a ejemplo

Bases de datos (3 B) - 5 - Agenda multiusuario (2) Mejoras

Modifique la aplicación del ejercicio anterior.

Bases de datos (3 B) - 6 - Campo de fecha

Modifique la aplicación del ejercicio 1, añadiendo un campo de tipo fecha en la tabla Personas, por ejemplo para guardar la fecha de nacimiento de la persona.

  1. Incluya el campo "nacido", de tipo DATE en la tabla Personas (tanto para SQLite como para MySQL).
  2. [comunes/demo.php] Incluya en los registros de prueba valores de fechas, con el formato "AAAA-MM-DD" (año, mes, día). Compruebe que se insertan correctamente.
  3. Al recoger la fecha para crear un nuevo registro ([db/tabla-personas/insertar-2.php]), realice las siguientes comprobaciones:
    • si no se recoge ningún valor, asigne el valor "0000-00-00" (de esta manera, unificamos el comportamiento de SQLite y MySQL, como se comenta en la lección diferencias entre MySQL y SQLite).
    • si la fecha no es correcta (es decir, si hace referencia a un día inexistente), muestre un mensaje de error. Para ello puede crear una función compruebaFecha($fecha) en comunes/biblioteca.php que se puede reutilizar al modificar un registro. Para comprobar la fecha, puede utilizar la función checkdate().
    Ayuda (haga clic aquí si no se le ocurre cómo hacerlo)
    • [db/tabla-personas/insertar-2.php]
      if ($nacido == "") {
          $nacido = "0000-00-00";
          $nacidoOk = true;
      } elseif (!compruebaFecha($nacido)) {
          print "    <p class=\"aviso\">La fecha no es correcta.</p>\n";
          print "\n";
      } else {
          $nacidoOk = true;
      }
      
    • [comunes/biblioteca.php]
      function compruebaFecha($fecha)
      {
          if (strlen($fecha) != 10) {
              return false;
          }
          return checkdate(substr($fecha, 5, 2), substr($fecha, 8, 2), substr($fecha, 0, 4));
      }
      
  4. Incluya en los listados (listar, borrar, buscar, modificar) una columna con la fecha de nacimiento. Si el valor guardado es "0000-00-00", no lo muestre. Compruebe que los listados se pueden ordenar por dicha columna.
  5. En el formulario de búsqueda ([db/tabla-personas/buscar-1.php]), incluya tres campos numéricos (año, mes y día) para poder hacer búsquedas por separado.
  6. En el formulario de modificación ([db/tabla-personas/modificar-2.php]), se mostrará el valor actual de la fecha de nacimiento.
Enlace a ejemplo