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 | CONSTRAINT USU_PK |
10 | PRIMARY KEY(USU_ID), |
11 | CONSTRAINT USU_CUE_UNQ |
12 | UNIQUE(USU_CUE) |
13 | )' |
14 | ); |
15 | $con->exec( |
16 | 'CREATE TABLE IF NOT EXISTS ROL ( |
17 | ROL_ID TEXT NOT NULL, |
18 | ROL_DESCRIPCION TEXT NOT NULL, |
19 | CONSTRAINT ROL_PK |
20 | PRIMARY KEY(ROL_ID), |
21 | CONSTRAINT ROL_DESCR_UNQ |
22 | UNIQUE(ROL_DESCRIPCION) |
23 | )' |
24 | ); |
25 | $con->exec( |
26 | 'CREATE TABLE IF NOT EXISTS USU_ROL ( |
27 | USU_ID INTEGER NOT NULL, |
28 | ROL_ID TEXT NOT NULL, |
29 | CONSTRAINT USU_ROL_PK |
30 | PRIMARY KEY(USU_ID, ROL_ID), |
31 | CONSTRAINT USU_ROL_USU_FK |
32 | FOREIGN KEY (USU_ID) REFERENCES USUARIO(USU_ID), |
33 | CONSTRAINT USU_ROL_ROL_FK |
34 | FOREIGN KEY (ROL_ID) REFERENCES ROL(ROL_ID) |
35 | )' |
36 | ); |
37 | } |
38 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/../modelo/Rol.php"; |
4 | require_once __DIR__ . "/bdCrea.php"; |
5 | require_once __DIR__ . "/rolBusca.php"; |
6 | require_once __DIR__ . "/rolAgrega.php"; |
7 | |
8 | class Bd |
9 | { |
10 | |
11 | private static ?PDO $conexion = null; |
12 | |
13 | static function getConexion(): PDO |
14 | { |
15 | if (self::$conexion === null) { |
16 | |
17 | self::$conexion = new PDO( |
18 | // cadena de conexión |
19 | "sqlite:srvamuchos.db", |
20 | // usuario |
21 | null, |
22 | // contraseña |
23 | null, |
24 | // Opciones: conexiones persistentes y lanza excepciones. |
25 | [PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION] |
26 | ); |
27 | |
28 | bdCrea(self::$conexion); |
29 | |
30 | if (rolBusca("Administrador") === false) { |
31 | $administrador = new Rol( |
32 | id: "Administrador", |
33 | descripcion: "Administra el sistema." |
34 | ); |
35 | rolAgrega($administrador); |
36 | } |
37 | |
38 | if (rolBusca("Cliente") === false) { |
39 | $cliente = new Rol( |
40 | id: "Cliente", |
41 | descripcion: "Realiza compras." |
42 | ); |
43 | rolAgrega($cliente); |
44 | } |
45 | } |
46 | |
47 | return self::$conexion; |
48 | } |
49 | } |
50 |
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) |
15 | VALUES |
16 | (:cue)" |
17 | ); |
18 | $stmt->execute([ |
19 | ":cue" => $modelo->cue, |
20 | ]); |
21 | /* Si usas una secuencia para generar el id, |
22 | * pasa como parámetro de lastInsertId el |
23 | * nombre de dicha secuencia, debes |
24 | * ejecutarlo antes del INSERT y pasarle el |
25 | * id generado al SQL. */ |
26 | $modelo->id = $con->lastInsertId(); |
27 | usuRolAgrega($modelo); |
28 | $con->commit(); |
29 | } |
30 |
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 usuarioBusca(int $usuId) |
8 | { |
9 | $con = Bd::getConexion(); |
10 | $stmt = $con->prepare( |
11 | "SELECT |
12 | USU_ID as id, |
13 | USU_CUE as cue |
14 | FROM USUARIO |
15 | WHERE USU_ID = :usuId" |
16 | ); |
17 | $stmt->execute([":usuId" => $usuId]); |
18 | $stmt->setFetchMode( |
19 | PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, |
20 | Usuario::class |
21 | ); |
22 | /** @var false|Usuario */ |
23 | $usuario = $stmt->fetch(); |
24 | if ($usuario === false) { |
25 | return false; |
26 | } else { |
27 | $usuario->roles = usuRolConsulta($usuId); |
28 | return $usuario; |
29 | } |
30 | } |
31 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/../../lib/php/recibeFetchAll.php"; |
4 | require_once __DIR__ . "/Bd.php"; |
5 | |
6 | function usuarioConsulta() |
7 | { |
8 | $con = Bd::getConexion(); |
9 | $stmt = $con->query( |
10 | "SELECT |
11 | U.USU_ID AS usuId, |
12 | U.USU_CUE AS usuCue, |
13 | GROUP_CONCAT(R.ROL_ID, ', ') AS roles |
14 | FROM USUARIO U |
15 | LEFT JOIN USU_ROL UR |
16 | ON U.USU_ID = UR.USU_ID |
17 | LEFT JOIN ROL R |
18 | ON UR.ROL_ID = R.ROL_ID |
19 | GROUP BY U.USU_CUE |
20 | ORDER BY U.USU_CUE" |
21 | ); |
22 | $resultado = $stmt->fetchAll(PDO::FETCH_OBJ); |
23 | return recibeFetchAll($resultado); |
24 | } |
25 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/Bd.php"; |
4 | require_once __DIR__ . "/usuRolElimina.php"; |
5 | |
6 | function usuarioElimina(int $id) |
7 | { |
8 | $con = Bd::getConexion(); |
9 | $con->beginTransaction(); |
10 | usuRolElimina($id); |
11 | $stmt = $con->prepare( |
12 | "DELETE FROM USUARIO |
13 | WHERE USU_ID = :id" |
14 | ); |
15 | $stmt->execute([":id" => $id]); |
16 | $con->commit(); |
17 | } |
18 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/../modelo/Usuario.php"; |
4 | require_once __DIR__ . "/Bd.php"; |
5 | require_once __DIR__ . "/usuRolAgrega.php"; |
6 | require_once __DIR__ . "/usuRolElimina.php"; |
7 | |
8 | function usuarioModifica(Usuario $modelo) { |
9 | $modelo->valida(); |
10 | $con = Bd::getConexion(); |
11 | $con->beginTransaction(); |
12 | $stmt = $con->prepare( |
13 | "UPDATE USUARIO |
14 | SET USU_CUE = :cue |
15 | WHERE USU_ID = :id" |
16 | ); |
17 | $stmt->execute([ |
18 | ":id" => $modelo->id, |
19 | ":cue" => $modelo->cue |
20 | ]); |
21 | usuRolElimina($modelo->id); |
22 | usuRolAgrega($modelo); |
23 | $con->commit(); |
24 | } |
25 |
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 |
1 | <?php |
2 | |
3 | require_once __DIR__ . "/Bd.php"; |
4 | |
5 | function usuRolElimina(int $usuId) |
6 | { |
7 | $con = Bd::getConexion(); |
8 | $stmt = $con->prepare( |
9 | "DELETE FROM USU_ROL |
10 | WHERE USU_ID = :usuId" |
11 | ); |
12 | $stmt->execute([":usuId" => $usuId]); |
13 | } |
14 |