Bases de datos (3 C). Enunciados. Ejercicios (bases de datos). PHP. Bartolomé Sintes Marco. www.mclibre.org

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

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

Implemente estas mejoras en la aplicación:

Al borrar todo, ofrecer la opción de borrar una de las tablas (usuarios o personas) o las dos.
En SQLite, si no existe el directorio donde se quiere guardar la base de datos, ofrecer la posibilidad de crearlo desde la propia aplicación.
Al establecer la contraseña, indicar la fortaleza de la contraseña (por ejemplo, puntuando de cero a cuatro estrellas si tiene un mínimo de 8 caracteres, si tiene mayúsculas y minúsculas, si tiene números y letras, si incluye un carácter especial). Podría comprobarse también que no haya caracteres o números consecutivos
Al conectarse un usuario, que la aplicación le solicite periódicamente cambiar la contraseña (aunque esto se suele hacer por tiempo, por ejemplo cada 6 meses, en este caso se podría hacer cada N conexiones para poder probarlo).
La inserción de registros repetidos se impide por el programa (en insertar-2.php y en modificar-3.php). Utilizar la restricción SQL UNIQUE para que ese trabajo lo haga la base de datos.
Comprobar si existe el usuario root en la función existenTablas()
Incluir un logotipo de la aplicación. Que se pueda subir el logotipo.
Eliminar de la función cabecera el tercer argumento (profundidad de la página), haciendo que esa profundidad se detecte automáticamente por el programa.
La longitud del campo password debe ser suficiente para alojar el hash elegido. Hacer que la aplicación compruebe que es así.

Bases de datos (3 C) - 2 - 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.

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).
Instale PostgreSQL.
- Contraseña del usuario postgres: postgres
- Puerto: 5432
- Configuración regional: Configuración regional por defecto
Abra pgadmin4. Al entrar en pgadmin4 le pedirá primero un master password: master. Y a continuación la contraseña del usuario postgres: postgres.
Con pgadmin4, cree un rol (en postgreSQL, rol es lo mismo que usuario):
- General >: Name: iaw
- Definition > Password: iaw
- Privileges >: Can login
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
En el archivo de configuración de PHP, active la extensión PDO para PostgreSQL. El nombre PostgreSQL se suele abreviar como pgsql.
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.

[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"]     = "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

[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 {
        $tmp = new PDO("pgsql:host=$cfg[pgsqlHost];port=$cfg[pgsqlPort];dbname=$cfg[pgsqlDatabase];user=$cfg[pgsqlUser];password=$cfg[pgsqlPassword]");
        $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;
    }
}

Nota: El DSN para PostgreSQL permite indicar el servidor en el que se encuentra la base de datos, su puerto, el nombre de la base de datos, el nombre de usuario de la base de datos y su contraseña.

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[tablaUsuarios] (
                 id SERIAL,
                 usuario VARCHAR($cfg[tablaUsuariosTamUsuario]),
                 password VARCHAR($cfg[tablaUsuariosTamPassword]),
                 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'";

[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";
            }
        }
    }

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 C) - 3 - Agenda multiusuario (1)

Modifique la aplicación de la aplicación de ejemplo 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).

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[tablaPersonas] (
                 id INTEGER PRIMARY KEY,
                 nombre VARCHAR($cfg[tablaPersonasTamNombre]) COLLATE NOCASE,
                 apellidos VARCHAR($cfg[tablaPersonasTamApellidos]) COLLATE NOCASE,
                 telefono VARCHAR($cfg[tablaPersonasTamTelefono]) COLLATE NOCASE,
                 correo VARCHAR($cfg[tablaPersonasTamCorreo]) COLLATE NOCASE,
                 id_usuario INTEGER,
                 FOREIGN KEY(id_usuario) REFERENCES $cfg[tablaUsuarios](id) ON DELETE CASCADE ON UPDATE CASCADE
                 )";

// MYSQL:
                 $consulta = "CREATE TABLE $cfg[tablaPersonas] (
                              id INTEGER UNSIGNED AUTO_INCREMENT,
                              nombre VARCHAR($cfg[tablaPersonasTamNombre]),
                              apellidos VARCHAR($cfg[tablaPersonasTamApellidos]),
                              telefono VARCHAR($cfg[tablaPersonasTamTelefono]),
                              correo VARCHAR($cfg[tablaPersonasTamCorreo]),
                              id_usuario INTEGER UNSIGNED,
                              FOREIGN KEY(id_usuario) REFERENCES $cfg[tablaUsuarios](id) ON DELETE CASCADE ON UPDATE CASCADE,
                              PRIMARY KEY(id)
                              )";

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"];
```
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[tablaPersonas]
                 WHERE id_usuario = $_SESSION[id_usuario]
                 ORDER BY $ordena";
```
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.

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[tablaPersonas] as personas
                 JOIN $cfg[tablaUsuarios] as usuarios
                 ON personas.id_usuario = usuarios.id
                 ORDER BY $ordena";
} else {
    $consulta = "SELECT * FROM $cfg[tablaPersonas]
               WHERE id_usuario = $_SESSION[id_usuario]
               ORDER BY $ordena";
}

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";
        }

Compruebe que los usuarios administradores pueden ordenar los registros de personas por el campo usuario.
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).

Bases de datos (3 C) - 5 - Campo de fecha

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

Incluya el campo "nacido", de tipo DATE en la tabla Personas (tanto para SQLite como para MySQL).
[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.
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)); }
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.
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.
En el formulario de modificación ([db/tabla-personas/modificar-2.php]), se mostrará el valor actual de la fecha de nacimiento.

Ejercicios - Bases de datos (3 C) - Enunciados

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

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

Bases de datos (3 C) - 2 - Nueva base de datos: PostgreSQL

Bases de datos (3 C) - 3 - Agenda multiusuario (1)

Bases de datos (3 C) - 4 - Agenda multiusuario (2) Mejoras

Bases de datos (3 C) - 5 - Campo de fecha