1 | <?php |
2 | |
3 | function bdCrea(PDO $con) |
4 | { |
5 | $con->exec( |
6 | 'CREATE TABLE IF NOT EXISTS USUARIO ( |
7 | USU_ID INTEGER, |
8 | USU_CUE TEXT NOT NULL, |
9 | USU_MATCH TEXT NOT NULL, |
10 | CONSTRAINT USU_PK |
11 | PRIMARY KEY(USU_ID), |
12 | CONSTRAINT USU_CUE_UNQ |
13 | UNIQUE(USU_CUE) |
14 | )' |
15 | ); |
16 | $con->exec( |
17 | 'CREATE TABLE IF NOT EXISTS ROL ( |
18 | ROL_ID TEXT, |
19 | ROL_DESCRIPCION TEXT NOT NULL, |
20 | CONSTRAINT ROL_PK |
21 | PRIMARY KEY(ROL_ID), |
22 | CONSTRAINT ROL_DESCR_UNQ |
23 | UNIQUE(ROL_DESCRIPCION) |
24 | )' |
25 | ); |
26 | $con->exec( |
27 | 'CREATE TABLE IF NOT EXISTS USU_ROL ( |
28 | USU_ID INTEGER NOT NULL, |
29 | ROL_ID TEXT NOT NULL, |
30 | CONSTRAINT USU_ROL_PK |
31 | PRIMARY KEY(USU_ID, ROL_ID), |
32 | CONSTRAINT USU_ROL_USU_FK |
33 | FOREIGN KEY (USU_ID) REFERENCES USUARIO(USU_ID), |
34 | CONSTRAINT USU_ROL_ROL_FK |
35 | FOREIGN KEY (ROL_ID) REFERENCES ROL(ROL_ID) |
36 | )' |
37 | ); |
38 | } |
39 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/../const/ROL_CLIENTE.php"; |
4 | require_once __DIR__ . "/../const/ROL_ADMINISTRADOR.php"; |
5 | require_once __DIR__ . "/../modelo/Rol.php"; |
6 | require_once __DIR__ . "/../modelo/Usuario.php"; |
7 | require_once __DIR__ . "/bdCrea.php"; |
8 | require_once __DIR__ . "/usuarioBuscaCue.php"; |
9 | require_once __DIR__ . "/usuarioAgrega.php"; |
10 | require_once __DIR__ . "/rolConsulta.php"; |
11 | require_once __DIR__ . "/rolAgrega.php"; |
12 | require_once __DIR__ . "/rolBusca.php"; |
13 | |
14 | class Bd |
15 | { |
16 | |
17 | private static ?PDO $conexion = null; |
18 | |
19 | static function getConexion(): PDO |
20 | { |
21 | if (self::$conexion === null) { |
22 | |
23 | self::$conexion = new PDO( |
24 | // cadena de conexión |
25 | "sqlite:srvaut.db", |
26 | // usuario |
27 | null, |
28 | // contraseña |
29 | null, |
30 | // Opciones: conexiones persistentes y lanza excepciones. |
31 | [PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION] |
32 | ); |
33 | |
34 | bdCrea(self::$conexion); |
35 | |
36 | if (rolBusca(ROL_ADMINISTRADOR) === false) { |
37 | $administrador = new Rol( |
38 | id: ROL_ADMINISTRADOR, |
39 | descripcion: "Administra el sistema." |
40 | ); |
41 | rolAgrega($administrador); |
42 | } |
43 | |
44 | if (rolBusca("Cliente") === false) { |
45 | $cliente = new Rol( |
46 | id: "Cliente", |
47 | descripcion: "Realiza compras." |
48 | ); |
49 | rolAgrega($cliente); |
50 | } |
51 | |
52 | if (usuarioBuscaCue("pepito") === false) { |
53 | $usuario = new Usuario( |
54 | cue: "pepito", |
55 | match: "cuentos", |
56 | roles: [$cliente] |
57 | ); |
58 | usuarioAgrega($usuario); |
59 | } |
60 | |
61 | if (usuarioBuscaCue("susana") === false) { |
62 | $usuario = new Usuario( |
63 | cue: "susana", |
64 | match: "alegria", |
65 | roles: [$administrador] |
66 | ); |
67 | usuarioAgrega($usuario); |
68 | } |
69 | |
70 | if (usuarioBuscaCue("bebe") === false) { |
71 | $usuario = new Usuario( |
72 | cue: "bebe", |
73 | match: "saurio", |
74 | roles: [$administrador, $cliente] |
75 | ); |
76 | usuarioAgrega($usuario); |
77 | } |
78 | } |
79 | |
80 | return self::$conexion; |
81 | } |
82 | } |
83 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/../modelo/Rol.php"; |
4 | require_once __DIR__ . "/Bd.php"; |
5 | |
6 | function rolAgrega(Rol $modelo) |
7 | { |
8 | $modelo->valida(); |
9 | $con = Bd::getConexion(); |
10 | $stmt = $con->prepare( |
11 | "INSERT INTO ROL |
12 | (ROL_ID, ROL_DESCRIPCION) |
13 | VALUES |
14 | (:id, :descripcion)" |
15 | ); |
16 | $stmt->execute([ |
17 | ":id" => $modelo->id, |
18 | ":descripcion" => $modelo->descripcion |
19 | ]); |
20 | } |
21 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/../modelo/Rol.php"; |
4 | require_once __DIR__ . "/Bd.php"; |
5 | require_once __DIR__ . "/usuRolConsulta.php"; |
6 | |
7 | function rolBusca(string $id) : false|Rol |
8 | { |
9 | $con = Bd::getConexion(); |
10 | $stmt = $con->prepare( |
11 | "SELECT |
12 | ROL_ID as id, |
13 | ROL_DESCRIPCION as descripcion |
14 | FROM ROL |
15 | WHERE ROL_ID = :id" |
16 | ); |
17 | $stmt->execute([":id" => $id]); |
18 | $stmt->setFetchMode( |
19 | PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, |
20 | Rol::class |
21 | ); |
22 | return $stmt->fetch(); |
23 | } |
24 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/../../lib/php/recibeFetchAll.php"; |
4 | require_once __DIR__ . "/../modelo/Rol.php"; |
5 | require_once __DIR__ . "/Bd.php"; |
6 | |
7 | /** @return Rol[] */ |
8 | function rolConsulta() |
9 | { |
10 | $con = Bd::getConexion(); |
11 | $stmt = $con->query( |
12 | "SELECT |
13 | ROL_ID as id, |
14 | ROL_DESCRIPCION as descripcion |
15 | FROM ROL |
16 | ORDER BY ROL_ID" |
17 | ); |
18 | $resultado = $stmt->fetchAll( |
19 | PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, |
20 | Rol::class |
21 | ); |
22 | return recibeFetchAll($resultado); |
23 | } |
24 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/../modelo/Usuario.php"; |
4 | require_once __DIR__ . "/Bd.php"; |
5 | require_once __DIR__ . "/usuRolAgrega.php"; |
6 | |
7 | function usuarioAgrega(Usuario $modelo) |
8 | { |
9 | $modelo->valida(); |
10 | $con = Bd::getConexion(); |
11 | $con->beginTransaction(); |
12 | $stmt = $con->prepare( |
13 | "INSERT INTO USUARIO |
14 | (USU_CUE, USU_MATCH) |
15 | VALUES |
16 | (:cue, :match)" |
17 | ); |
18 | $stmt->execute([ |
19 | ":cue" => $modelo->cue, |
20 | ":match" => password_hash($modelo->match, PASSWORD_DEFAULT) |
21 | ]); |
22 | /* Si usas una secuencia para generar el id, |
23 | * pasa como parámetro de lastInsertId el |
24 | * nombre de dicha secuencia. */ |
25 | $modelo->id = $con->lastInsertId(); |
26 | usuRolAgrega($modelo); |
27 | $con->commit(); |
28 | } |
29 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/../modelo/Usuario.php"; |
4 | require_once __DIR__ . "/Bd.php"; |
5 | require_once __DIR__ . "/usuRolConsulta.php"; |
6 | |
7 | function usuarioBuscaCue(string $cue) |
8 | { |
9 | $con = Bd::getConexion(); |
10 | $stmt = $con->prepare( |
11 | "SELECT |
12 | USU_ID as id, |
13 | USU_CUE as cue, |
14 | USU_MATCH as match |
15 | FROM USUARIO |
16 | WHERE USU_CUE = :cue" |
17 | ); |
18 | $stmt->execute([":cue" => $cue]); |
19 | $stmt->setFetchMode( |
20 | PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, |
21 | Usuario::class |
22 | ); |
23 | /** @var false|Usuario */ |
24 | $usuario = $stmt->fetch(); |
25 | if ($usuario === false) { |
26 | return false; |
27 | } else { |
28 | $usuario->roles = usuRolConsulta($usuario->id); |
29 | return $usuario; |
30 | } |
31 | } |
32 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/usuarioBuscaCue.php"; |
4 | |
5 | function usuarioVerifica(string $cue, string $match) |
6 | { |
7 | $usuario = usuarioBuscaCue($cue); |
8 | if ($usuario !== false && password_verify($match, $usuario->match)) { |
9 | return $usuario; |
10 | } else { |
11 | return false; |
12 | } |
13 | } |
14 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/../modelo/Usuario.php"; |
4 | require_once __DIR__ . "/Bd.php"; |
5 | |
6 | function usuRolAgrega(Usuario $usuario) { |
7 | $roles = $usuario->roles; |
8 | if (sizeof($roles) > 0) { |
9 | $con = Bd::getConexion(); |
10 | $stmt = $con->prepare( |
11 | "INSERT INTO USU_ROL |
12 | (USU_ID, ROL_ID) |
13 | VALUES |
14 | (:usuId, :rolId)" |
15 | ); |
16 | foreach ($roles as $rol) { |
17 | $stmt->execute( |
18 | [ |
19 | ":usuId" => $usuario->id, |
20 | ":rolId" => $rol->id |
21 | ] |
22 | ); |
23 | } |
24 | } |
25 | } |
26 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/../../lib/php/recibeFetchAll.php"; |
4 | require_once __DIR__ . "/../modelo/Rol.php"; |
5 | require_once __DIR__ . "/Bd.php"; |
6 | |
7 | /** @return Rol[] */ |
8 | function usuRolConsulta(int $usuId) |
9 | { |
10 | $con = Bd::getConexion(); |
11 | $stmt = $con->query( |
12 | "SELECT |
13 | UR.ROL_ID AS id, |
14 | R.ROL_DESCRIPCION AS descripcion |
15 | FROM USU_ROL UR, ROL R |
16 | WHERE |
17 | UR.ROL_ID = R.ROL_ID |
18 | AND UR.USU_ID = :usuId |
19 | ORDER BY UR.ROL_ID" |
20 | ); |
21 | $stmt->execute([":usuId" => $usuId]); |
22 | $resultado = $stmt->fetchAll( |
23 | PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, |
24 | Rol::class |
25 | ); |
26 | return recibeFetchAll($resultado); |
27 | } |
28 |