Creació de la BD i les seves taules (A5.3)
De Wiki personal d'en Guillem Serrat
Estructura de la BD
Aquesta aplicació tindrà una BD anomenada “autenticacio” amb codificació UTF8
$sql = "CREATE DATABASE IF NOT EXISTS autenticacio CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;";
Amb els següents permisos:
- L'usuari iot podrà escriure i llegir (Connexió d'escriptura)
- L'usuari convidat únicament podrà llegir (Connexió de lectura)
$permisosRW = "GRANT SELECT, INSERT, UPDATE, DELETE ON autenticacio.* TO 'iot'@'localhost';"; $permisosR = "GRANT SELECT ON autenticacio.* TO 'convidat'@'localhost';";
Aquesta BD comptarà amb dues taules:
- Taula "usuaris", per emmagatzemar els usuaris del sistema d'autenticació
- Taula "activitat", per emmagatzemar les accions dels usuaris
Taula "usuaris"
A la taula usuaris s'emmagatzemarà el següent, en format UTF-8:
- ID
- Nom d’usuari (únic)
- Contrasenya
- Nom complet
- Email (únic)
- Email verificat
- Telèfon (únic)
- Ciutat
- Edat
- Rol
- Data de registre
- Token d’autenticació
- Número d’intents de login
- Últim intent de login
$sql = "CREATE TABLE usuaris (
id INT AUTO_INCREMENT PRIMARY KEY,
nom_usuari VARCHAR(50) NOT NULL UNIQUE,
contrasenya VARCHAR(255) NOT NULL,
nom_complet VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
email_verificat ENUM('si', 'no') NOT NULL DEFAULT 'no',
telefon VARCHAR(20) NOT NULL UNIQUE,
ciutat VARCHAR(100) NOT NULL,
edat INT NOT NULL,
rol ENUM('ed_admin', 'vi_admin', 'usuari') DEFAULT 'usuari',
data_registre DATETIME DEFAULT CURRENT_TIMESTAMP,
token_recordar VARCHAR(100),
intents_login INT DEFAULT 0,
ultim_intent DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->exec($sql);
Per defecte, l'aplicació comptarà amb dos usuaris base: un administrador editor i un administrador visualitzador.
$sql = "INSERT INTO usuaris (nom_usuari, contrasenya, nom_complet, email, telefon, ciutat, edat, rol) VALUES
('admin', '$2y$12$7K.4YtXSPyGxKP2riYwWBeXqNmvDrlxHY7V.ReAPin7hjVmK/aHHC','Administrador', 'admin@example.com', '630111111', 'Barcelona', '18', 'ed_admin'), ('joan', '$2y$12$7K.4YtXSPyGxKP2riYwWBeXqNmvDrlxHY7V.ReAPin7hjVmK/aHHC', 'Joan Garcia', 'joan@example.com', '630111112', 'Barcelona', '18', 'vi_admin');";$conn->exec($sql);
Taula "activitat"
A la taula usuaris s'emmagatzemarà el següent, en format UTF-8:
- ID
- ID de l’usuari al que correspon l’acció (de la taula usuaris)
- Acció
- Data i hora de l’acció
- IP del client
$sql = "CREATE TABLE activitat (
id INT AUTO_INCREMENT PRIMARY KEY,
usuari_id INT NOT NULL,
accio VARCHAR(50) NOT NULL,
data_hora DATETIME DEFAULT CURRENT_TIMESTAMP,
ip_client VARCHAR(45),
FOREIGN KEY (usuari_id) REFERENCES usuaris(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
Codi complet
<!DOCTYPE html>
<html lang="ca">
<head>
<title>Creació BD i Taula</title>
<meta http-equiv="Content-type" content="text/html; charset=UTF-8">
</head>
<body>
<?php
// Definim els paràmetres per realitzar la connexió
$servername = "127.0.0.1";
$username = "root";
$password = "fjeclot";
try {
$conn = new PDO("mysql:host=$servername", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Creem la BD botiga amb codificació UTF8
$sql = "CREATE DATABASE IF NOT EXISTS autenticacio CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;";
$conn->exec($sql);
// Afegim permissos a diversos usuarios sobre la BD
$permisosRW = "GRANT SELECT, INSERT, UPDATE, DELETE ON autenticacio.* TO 'iot'@'localhost';";
$permisosR = "GRANT SELECT ON autenticacio.* TO 'convidat'@'localhost';";
$conn->exec($permisosRW);
$conn->exec($permisosR);
$conn->exec("FLUSH PRIVILEGES;");
// Segona connexió per crear la taula
$servidor = "127.0.0.1";
$usuari = "root";
$contrasenya = "fjeclot";
$nomDB = "autenticacio";
$conn = new PDO("mysql:host=$servidor;dbname=$nomDB", $usuari, $contrasenya);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Creem la taula usuari, on s'emmagatzemen els usuaris que podran autenticar-se a l'aplicació
$sql = "CREATE TABLE usuaris (
id INT AUTO_INCREMENT PRIMARY KEY,
nom_usuari VARCHAR(50) NOT NULL UNIQUE,
contrasenya VARCHAR(255) NOT NULL,
nom_complet VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
email_verificat ENUM('si', 'no') NOT NULL DEFAULT 'no',
telefon VARCHAR(20) NOT NULL UNIQUE,
ciutat VARCHAR(100) NOT NULL,
edat INT NOT NULL,
rol ENUM('ed_admin', 'vi_admin', 'usuari') DEFAULT 'usuari',
data_registre DATETIME DEFAULT CURRENT_TIMESTAMP,
token_recordar VARCHAR(100),
intents_login INT DEFAULT 0,
ultim_intent DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->exec($sql);
// Inserim usuaris
$sql = "INSERT INTO usuaris (nom_usuari, contrasenya, nom_complet, email, telefon, ciutat, edat, rol) VALUES
('admin', '$2y$12$7K.4YtXSPyGxKP2riYwWBeXqNmvDrlxHY7V.ReAPin7hjVmK/aHHC','Administrador', 'admin@example.com', '630111111', 'Barcelona', '18', 'ed_admin'),
('joan', '$2y$12$7K.4YtXSPyGxKP2riYwWBeXqNmvDrlxHY7V.ReAPin7hjVmK/aHHC', 'Joan Garcia', 'joan@example.com', '630111112', 'Barcelona', '18', 'vi_admin');";
$conn->exec($sql);
// Creem la taula on es registrarà l'activitat dels usuaris
$sql = "CREATE TABLE activitat (
id INT AUTO_INCREMENT PRIMARY KEY,
usuari_id INT NOT NULL,
accio VARCHAR(50) NOT NULL,
data_hora DATETIME DEFAULT CURRENT_TIMESTAMP,
ip_client VARCHAR(45),
FOREIGN KEY (usuari_id) REFERENCES usuaris(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->exec($sql);
echo "Base de Dades i taula creades correctament<br>";
} catch(PDOException $e) {
echo "Error" . $e->getMessage();
}
$conn = null;
?>
<h1>Codi en PHP</h1>
<?php
show_source("creacioBDiTaula.php");
?>
</body>
</html>
