SQLite · de cero a experto

Antes del motor, el problema. Guardar datos "en cualquier lado" funciona hasta que deja de funcionar: duplicados, datos perdidos, respuestas lentas y nadie sabe quién cambió qué. Este capítulo nombra ese dolor y presenta al responsable de resolverlo.

• Reconocer los 4 dolores de guardar datos sin un motor.
• Saber qué aporta exactamente un sistema gestor de base de datos.
• Distinguir base de datos, motor y modelo.
• Presentar el dominio del curso: una clínica real.

El dolor de la hoja de cálculo

Imagina la agenda de la clínica en un archivo compartido: pacientes en una hoja, citas en otra, pagos en una tercera. Funciona... hasta el primer lunes ocupado:

Qué aporta el motor (SGBD)

Un sistema gestor de base de datos (SGBD o DBMS) es el software que guarda los datos y hace cumplir las reglas. Sus cuatro promesas:

• Integridad: las reglas viven EN la base — tipos, obligatorios, unicidad, relaciones. Ni un cliente descuidado las rompe.
• Concurrencia: cientos de usuarios leen y escriben a la vez sin pisarse (transacciones, cap 25).
• Consultas declarativas: describes QUÉ quieres en SQL, no CÓMO buscarlo — el motor decide el camino (cap 30).
• Seguridad y auditoría: usuarios con permisos mínimos y registro de quién hizo qué — el hilo rojo de este curso.

Tres palabras que no son sinónimos

"Base de datos" coloquialmente nombra a las tres; en el manual seremos precisos porque cada una tiene su propio capítulo.

El dominio del curso: la clínica

Toda la serie webcode aprende sobre un caso real (Pedidos en la fila web). Aquí será una clínica: pacientes, médicos, especialidades, citas, recetas y pagos. Elegimos este dominio porque obliga a las preguntas serias: la historia clínica no puede perderse, dos citas no pueden caer en el mismo horario del mismo médico, y CADA cambio sensible debe quedar auditado — ¿quién lo hizo y desde qué cuenta?

Los cuatro motores de la serie (MySQL, PostgreSQL, SQL Server y SQLite) son los más usados del mundo. Pero SQLite es diferente por diseño: no necesita servidor, no tiene usuarios, no tiene procedures. Hoy conoces qué es, qué puede y qué honestamente no puede.

• Entender qué es un motor embebido y por qué existe.
• Conocer las limitaciones reales de SQLite (sin miedo).
• Saber cuándo SÍ usar SQLite y cuándo NO.
• Compararlo con MySQL, PostgreSQL y SQL Server.

¿Qué es SQLite?

SQLite es un motor de base de datos completo que vive DENTRO de tu aplicación. No hay servidor que arrancar, no hay proceso separado, no hay configuración de red. La base es un archivo (.db) que cualquier programa puede abrir directamente.

Las limitaciones reales

SQLite no es peor que los demás — es DIFERENTE por diseño. Conoce sus limitaciones sin miedo:

¿Cuándo SÍ usar SQLite?

• Apps móviles y de escritorio: cada usuario tiene su propia base local.
• Sitios web pequeños a medianos: tráfico moderado, pocos escritores concurrentes.
• Prototipos y pruebas: arrancas en 0 segundos, sin configurar nada.
• Almacenamiento de configuraciones: Firefox, Chrome, Android lo usan internamente.
• Análisis de datos: cargar CSVs, consultar, explorar — rápido y sin servidor.

¿Cuándo NO usar SQLite?

• Aplicaciones con miles de escritores concurrentes (usar MySQL o PostgreSQL).
• Sistemas que necesitan usuarios y roles de base de datos (usar SQL Server o PostgreSQL).
• Lógica server-side compleja con procedures y funciones (usar PostgreSQL o SQL Server).
• Red: múltiples servidores accediendo a la misma base en tiempo real (usar cualquier servidor).

Los cuatro motores comparados

La gran ventaja de SQLite: la instalación es la más rápida de los cuatro motores. En Linux, un solo apt. En Windows, descargas y listo. No hay servicio que configurar, no hay contraseña que establecer.

• Instalar SQLite en Ubuntu con apt.
• Instalar SQLite en Windows desde sqlite.org.
• Verificar la versión y el modo WAL.
• Entender la diferencia entre el CLI y las bibliotecas de la app.

Ubuntu / Debian

# actualizar repositorios:
sudo apt update

# instalar sqlite3 (el cliente CLI) y libsqlite3-dev (bibliotecas para compilar):
sudo apt install sqlite3 libsqlite3-dev

# verificar instalación:
sqlite3 --version
# 3.45.1 2024-01-30 ...

En Ubuntu, sqlite3 instala el cliente de línea de comandos. libsqlite3-dev es la biblioteca C para compilar programas que usen SQLite (Python, PHP y otros la necesitan internamente).

Windows

# 1. ir a https://www.sqlite.org/download.html
# 2. descargar "Precompiled Binaries for Windows" (sqlite-tools-win-x64-*.zip)
# 3. descomprimir en C:\sqlite\ (o donde prefieras)
# 4. agregar C:\sqlite\ al PATH del sistema:
#    Panel de control → Sistema → Variables de entorno → Path → Nuevo

# verificar en PowerShell o CMD:
sqlite3 --version

No hay instalador gráfico: descargas, descomprimes y agregas al PATH. Sin servicio que arrancar, sin configuración de red, sin base de datos del sistema que crear.

macOS

# SQLite ya viene instalado en macOS — verifica:
sqlite3 --version

# si necesitas una versión más reciente:
brew install sqlite

Activar WAL (debes hacerlo una vez)

# WAL (Write-Ahead Logging) mejora la concurrencia:
sqlite3 clinica.db "PRAGMA journal_mode=WAL;"

# verificar que quedó activo:
sqlite3 clinica.db "PRAGMA journal_mode;"
# resultado: wal

WAL permite que la lectura no bloquee a la escritura y viceversa — mejora enorme sobre el modo DELETE por defecto. Actívalo siempre que crees una base nueva.

La diferencia: CLI vs bibliotecas

Hoy abres tu primera base de datos con SQLite, creas una tabla y haces tus primeras consultas. Todo desde el cliente de línea de comandos — sin servidor, sin configuración, sin permisos que configurar.

• Abrir y crear una base de datos con el CLI sqlite3.
• Crear la primera tabla con INTEGER PRIMARY KEY y tipos estrictos.
• Insertar, consultar y manipular datos con SQL básico.
• Conocer los comandos punto del CLI (.tables, .schema, .mode, etc.).

Abrir y crear una base

# crear (o abrir) una base — si no existe, se crea:
sqlite3 clinica.db

# dentro del prompt, verificar:
.tables
# (vacío — no hay tablas todavía)

# ver la versión:
.version

El comando sqlite3 clinica.db hace TODO: crea el archivo si no existe y abre el cliente con esa base. No hay paso intermedio de CREATE DATABASE — la base ES el archivo.

La primera tabla: tipos de SQLite

-- crear tabla de especialidades con tipos estrictos (SQLite 3.37+):
CREATE TABLE especialidades (
    id INTEGER PRIMARY KEY,
    nombre TEXT NOT NULL UNIQUE,
    descripcion TEXT
);

-- verificar la estructura:
.schema especialidades

Diferencias clave con los otros motores (verificado en doc):

Insertar y consultar

-- insertar especialidades:
INSERT INTO especialidades (nombre, descripcion) VALUES
  ('Medicina General', 'Atención primaria y preventiva'),
  ('Cardiología', 'Enfermedades del corazón'),
  ('Dermatología', 'Enfermedades de la piel');

-- consultar todo:
SELECT * FROM especialidades;

-- insertar un médico (con FK):
CREATE TABLE medicos (
    id INTEGER PRIMARY KEY,
    nombre TEXT NOT NULL,
    apellido TEXT NOT NULL,
    especialidad_id INTEGER,
    FOREIGN KEY (especialidad_id) REFERENCES especialidades(id)
);

INSERT INTO medicos (nombre, apellido, especialidad_id) VALUES
  ('Carlos', 'Ramírez', 1),
  ('María', 'López', 2);

Los comandos punto del CLI

El archivo .db

# fuera del sqlite3, en la terminal:
ls -lh clinica.db
# -rw-r--r-- 1 palmeyda 12K ago 27 10:30 clinica.db

# la base entera es UN archivo — cópiala, muévela, respáldala:
cp clinica.db clinica_backup.db

La simplicidad es literal: la base de datos completa vive en un solo archivo. Copiar el archivo es hacer backup. Moverlo es migrar. No hay carpetas de datos, logs de transacciones ni archivos de configuración aparte.

Aunque interactuar con SQLite mediante la terminal CLI es indispensable para tareas rápidas y automatización, el diseño visual de esquemas y la exploración manual de registros se agilizan enormemente con un gestor gráfico. Dado que SQLite almacena todo en un único archivo, los entornos gráficos son ligeros y no requieren configurar credenciales de red. Hoy estudiaremos las herramientas de administración más populares.

• Instalar y configurar DB Browser for SQLite en sistemas Ubuntu y Windows.
• Explorar la estructura física, índices y datos del archivo de base de datos de manera visual.
• Ejecutar consultas SQL y exportar reportes en formatos planos (CSV/SQL).
• Comprender los riesgos de bloqueo del archivo cuando se mantiene abierto en modo de edición.

1. Herramientas recomendadas

Para trabajar de forma visual con SQLite, disponemos de tres alternativas principales según el flujo de trabajo:

• DB Browser for SQLite (DB4S): La opción recomendada. Es de código abierto, sumamente ligera y está diseñada exclusivamente para exprimir las particularidades de SQLite.
• DBeaver / DataGrip: Gestores empresariales potentes. Ideales si buscas unificar el trabajo de múltiples motores (MySQL, PostgreSQL y SQLite) en un solo software pesado.
• Extensiones de VS Code (ej. SQLite Viewer): Perfectas si deseas inspeccionar bases de datos rápidamente sin salir de tu editor de código.

2. Instalación de DB Browser for SQLite

La instalación es sumamente directa. En sistemas basados en Debian/Ubuntu, se ejecuta a través del gestor de paquetes oficial:

# Actualizamos los repositorios e instalamos el navegador gráfico
sudo apt update
sudo apt install sqlitebrowser

En **Windows** y **macOS**, basta con ingresar a la web oficial sqlitebrowser.org, descargar el instalador ejecutable correspondiente y seguir el asistente clásico de instalación.

3. Inspección y Operaciones Visuales

Una vez abierto DB Browser, haz click en Abrir base de datos y selecciona tu archivo clinica.db. La interfaz se divide en cuatro pestañas de control:

• Estructura de la base de datos: Muestra el esquema de DDL de todas las tablas creadas, sus tipos de datos, índices y triggers.
• Hojear datos: Permite explorar las filas de cada tabla, filtrar datos escribiendo texto directamente sobre las columnas y realizar inserciones/ediciones en celdas de forma manual.
• Editar pragmas: Permite activar o desactivar directivas internas de SQLite (como forzar llaves foráneas o modificar el tamaño de página).
• Ejecutar SQL: Un editor con autocompletado para redactar y lanzar sentencias SQL complejas, visualizando el set de resultados en una grilla inferior.

4. Guardar cambios en disco

A diferencia de los servidores cliente-servidor tradicionales que confirman cada operación de inmediato en la base de datos, los gestores de SQLite a menudo mantienen los cambios en memoria temporal. Debes presionar el botón Escribir cambios (o Ctrl + S) para que las modificaciones se graben físicamente en el archivo .db en disco.

Al iniciar la Parte II de nuestro curso, implementaremos el modelo de datos de la clínica de especialidades. Sin embargo, a diferencia de motores estructurados como PostgreSQL o MySQL, SQLite maneja de forma nativa un sistema de tipado dinámico y afinidad de tipos. Hoy aprenderemos a utilizar las modernas tablas estrictas (STRICT) de SQLite para garantizar la integridad y declararemos las primeras cuatro tablas del modelo.

• Comprender la diferencia entre el tipado dinámico tradicional y las tablas `STRICT` de SQLite.
• Simular tipos ausentes en el motor como fechas (a través de `TEXT`) y booleanos (a través de `INTEGER`).
• Escribir el código DDL para crear las tablas `especialidades`, `medicos`, `pacientes` y `usuarios_app`.
• Configurar restricciones de dominio mediante la cláusula `CHECK`.

1. La afinidad clásica frente a STRICT

Por defecto, SQLite permite insertar texto en una columna numérica o viceversa sin dar ningún error. Para evitar este comportamiento y asimilar el comportamiento riguroso de base de datos de producción, usaremos la declaración STRICT (soportada a partir de SQLite 3.37.0). Los únicos tipos válidos bajo STRICT son:

• INT o INTEGER: Números enteros firmados.
• REAL: Números de punto flotante.
• TEXT: Cadenas de caracteres codificadas en UTF-8 o UTF-16.
• BLOB: Datos binarios en bruto (imágenes, archivos).
• ANY: Habilita el tipado dinámico tradicional solo para esa columna.

2. Simular fechas y booleanos

SQLite no posee tipos nativos como `DATE`, `DATETIME` o `BOOLEAN`. Los resolvemos de la siguiente manera:

• Fechas: Se almacenan como TEXT en formato ISO-8601 (ejemplo: 'YYYY-MM-DD' o 'YYYY-MM-DD HH:MM:SS'). SQLite posee funciones integradas que reconocen estas cadenas para realizar cálculos de tiempo.
• Booleanos: Se almacenan como INTEGER (0 para falso, 1 para verdadero). Usamos la cláusula CHECK (activo IN (0, 1)) para blindar la columna.

3. DDL de las primeras 4 tablas

A continuación se detallan las sentencias DDL estrictas para construir la base de nuestra clínica:

-- Tabla Especialidades
CREATE TABLE especialidades (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT NOT NULL UNIQUE,
    descripcion TEXT
) STRICT;

-- Tabla Médicos
CREATE TABLE medicos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT NOT NULL,
    apellido TEXT NOT NULL,
    colegiatura TEXT NOT NULL UNIQUE,
    especialidad_id INTEGER NOT NULL,
    email TEXT UNIQUE,
    telefono TEXT,
    activo INTEGER NOT NULL DEFAULT 1 CHECK (activo IN (0, 1)),
    FOREIGN KEY (especialidad_id) REFERENCES especialidades(id)
) STRICT;

-- Tabla Pacientes
CREATE TABLE pacientes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    documento TEXT NOT NULL UNIQUE,
    nombre TEXT NOT NULL,
    apellido TEXT NOT NULL,
    fecha_nacimiento TEXT NOT NULL, -- Guardado en formato 'YYYY-MM-DD'
    sexo TEXT NOT NULL CHECK (sexo IN ('M', 'F')),
    telefono TEXT,
    email TEXT,
    direccion TEXT,
    tipo_sangre TEXT,
    alergias TEXT,
    activo INTEGER NOT NULL DEFAULT 1 CHECK (activo IN (0, 1))
) STRICT;

-- Tabla Usuarios App
CREATE TABLE usuarios_app (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    usuario TEXT NOT NULL UNIQUE,
    nombre TEXT NOT NULL,
    rol TEXT NOT NULL CHECK (rol IN ('ADMIN', 'MEDICO', 'RECEPCION')),
    hash_clave TEXT NOT NULL,
    activo INTEGER NOT NULL DEFAULT 1 CHECK (activo IN (0, 1))
) STRICT;

Una vez definida la base del modelo de la clínica, procederemos a declarar las cuatro tablas transaccionales: citas, recetas, pagos y auditoría. Estudiaremos la integridad referencial en SQLite, cómo configurar borrados en cascada y analizaremos una particularidad histórica del motor: por qué las claves foráneas vienen apagadas por defecto y cómo activarlas.

• Crear el DDL estricto para las tablas `citas`, `recetas`, `pagos` y `auditoria`.
• Implementar restricciones de unicidad compuestas (`UNIQUE`) y relaciones relacionales.
• Activar la directiva `PRAGMA foreign_keys = ON` para habilitar el chequeo referencial.
• Configurar políticas de borrado automático mediante `ON DELETE CASCADE`.

1. DDL de las tablas transaccionales

A continuación se detallan las sentencias SQL estrictas para compilar el resto del modelo clínica:

-- Tabla Citas
CREATE TABLE citas (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    paciente_id INTEGER NOT NULL,
    medico_id INTEGER NOT NULL,
    fecha_hora TEXT NOT NULL, -- Formato 'YYYY-MM-DD HH:MM:SS'
    estado TEXT NOT NULL CHECK (estado IN ('PENDIENTE', 'COMPLETADA', 'CANCELADA', 'NO_ASISTIO')),
    motivo TEXT NOT NULL,
    diagnostico TEXT,
    FOREIGN KEY (paciente_id) REFERENCES pacientes(id),
    FOREIGN KEY (medico_id) REFERENCES medicos(id),
    UNIQUE(medico_id, fecha_hora) -- Impide que un médico tenga citas empalmadas en el mismo horario
) STRICT;

-- Tabla Recetas
CREATE TABLE recetas (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    cita_id INTEGER NOT NULL UNIQUE,
    medicamento TEXT NOT NULL,
    dosis TEXT NOT NULL,
    indicaciones TEXT,
    duracion_dias INTEGER NOT NULL CHECK (duracion_dias > 0),
    FOREIGN KEY (cita_id) REFERENCES citas(id) ON DELETE CASCADE
) STRICT;

-- Tabla Pagos
CREATE TABLE pagos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    cita_id INTEGER NOT NULL,
    monto REAL NOT NULL CHECK (monto >= 0),
    metodo TEXT NOT NULL CHECK (metodo IN ('EFECTIVO', 'TARJETA', 'TRANSFERENCIA')),
    estado TEXT NOT NULL CHECK (estado IN ('PAGADO', 'PENDIENTE', 'REEMBOLSADO')),
    fecha TEXT NOT NULL, -- Formato 'YYYY-MM-DD'
    FOREIGN KEY (cita_id) REFERENCES citas(id)
) STRICT;

-- Tabla Auditoría
CREATE TABLE auditoria (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tabla_afectada TEXT NOT NULL,
    operacion TEXT NOT NULL CHECK (operacion IN ('INSERT', 'UPDATE', 'DELETE')),
    usuario_app TEXT,
    usuario_bd TEXT NOT NULL,
    registro_id INTEGER NOT NULL,
    fecha_hora TEXT NOT NULL, -- Formato 'YYYY-MM-DD HH:MM:SS'
    datos_anteriores TEXT,    -- Datos en formato JSON
    datos_nuevos TEXT         -- Datos en formato JSON
) STRICT;

2. El chequeo referencial obligatorio

Por defecto, SQLite no valida las llaves foráneas (`FOREIGN KEY`) por retrocompatibilidad con la versión 2. Esto significa que si no modificas la configuración, puedes borrar un médico y dejar citas con IDs inexistentes sin recibir alertas. Para evitar esto, es obligatorio lanzar este Pragma al abrir cada conexión:

-- Ejecutar obligatoriamente al conectar la base de datos
PRAGMA foreign_keys = ON;

3. Borrado en cascada (ON DELETE CASCADE)

En la tabla recetas, declaramos ON DELETE CASCADE en su llave foránea. Si una cita médica es cancelada y eliminada del sistema, SQLite eliminará automáticamente la receta correspondiente, previniendo registros huérfanos.

Para aprender de forma profesional a optimizar consultas, programar disparadores, analizar índices y conectar nuestro backend móvil, necesitamos contar con un set de datos de prueba idéntico y determinista. Hoy realizaremos la población de nuestras ocho tablas de la clínica con datos de ejemplo realistas fijados temporalmente en julio de 2026, lo cual garantizará resultados reproducibles en cualquier máquina.

• Conocer los beneficios de los datos de prueba deterministas para la auditoría de queries.
• Insertar las filas semilla en las tablas de especialidades, médicos, pacientes y usuarios.
• Insertar las filas semilla transaccionales en citas, recetas y pagos.
• Agrupar las sentencias en una transacción explícita para multiplicar la velocidad de inserción en disco.

1. La importancia de la reproducibilidad

En el diseño de bases de datos, no se deben usar datos aleatorios (como los generados con librerías tipo Faker) para aprender optimización. Si tus médicos o fechas cambian en cada ejecución, no podrás comparar si un índice mejoró los tiempos de consulta o si un trigger de auditoría se disparó correctamente. Por ello, usaremos fechas estables fijadas en julio de 2026 y hashes precalculados.

2. Sentencias SQL de sembrado (Seed)

Abre tu cliente CLI de SQLite (`sqlite3 clinica.db`) o DB Browser, y ejecuta el bloque de inserción masiva. Nota cómo envolvemos todo el bloque entre las cláusulas BEGIN TRANSACTION; y COMMIT; para optimizar la velocidad en disco:

-- Activamos el chequeo de llaves foráneas y abrimos transacción
PRAGMA foreign_keys = ON;
BEGIN TRANSACTION;

-- Especialidades
INSERT INTO especialidades (id, nombre, descripcion) VALUES
(1, 'Cardiología', 'Tratamiento de enfermedades del corazón y vasos sanguíneos.'),
(2, 'Pediatría', 'Atención médica de niños y adolescentes.'),
(3, 'Dermatología', 'Cuidado de la piel, cabello y uñas.'),
(4, 'Ginecología', 'Salud del sistema reproductor femenino.'),
(5, 'Oftalmología', 'Tratamiento de enfermedades de los ojos.'),
(6, 'Medicina General', 'Atención médica primaria y preventiva.');

-- Médicos
INSERT INTO medicos (id, nombre, apellido, colegiatura, especialidad_id, email, telefono, activo) VALUES
(1, 'Carlos', 'Mendoza', 'CMP12345', 1, 'carlos.mendoza@clinica.com', '987654321', 1),
(2, 'Ana', 'Gómez', 'CMP23456', 2, 'ana.gomez@clinica.com', '987654322', 1),
(3, 'Luis', 'Sánchez', 'CMP34567', 3, 'luis.sanchez@clinica.com', '987654323', 1),
(4, 'María', 'Fernández', 'CMP45678', 4, 'maria.fernandez@clinica.com', '987654324', 1),
(5, 'Jorge', 'Castro', 'CMP56789', 5, 'jorge.castro@clinica.com', '987654325', 1),
(6, 'Elena', 'Ruiz', 'CMP67890', 6, 'elena.ruiz@clinica.com', '987654326', 1),
(7, 'Pedro', 'Vargas', 'CMP78901', 1, 'pedro.vargas@clinica.com', '987654327', 0),
(8, 'Sofía', 'Paredes', 'CMP89012', 2, 'sofia.paredes@clinica.com', '987654328', 1);

-- Pacientes
INSERT INTO pacientes (id, documento, nombre, apellido, fecha_nacimiento, sexo, telefono, email, direccion, tipo_sangre, alergias, activo) VALUES
(1, 'PN000001', 'Juan', 'Pérez', '1985-04-12', 'M', '912345671', 'juan.perez@email.com', 'Av. Larco 123, Miraflores', 'O+', 'Ninguna', 1),
(2, 'PN000002', 'Carmen', 'Luz', '1990-09-22', 'F', '912345672', 'carmen.luz@email.com', 'Calle Tarapacá 456, San Isidro', 'A-', 'Penicilina', 1),
(3, 'PN000003', 'Andrés', 'Soto', '1978-11-05', 'M', '912345673', 'andres.soto@email.com', 'Av. Arequipa 1420, Lince', 'O-', 'Ninguna', 1),
(4, 'PN000004', 'Dora', 'Díaz', '2015-06-18', 'F', '912345674', 'dora.diaz@email.com', 'Jr. Junín 789, Cercado de Lima', 'AB+', 'Polvo', 1),
(5, 'PN000005', 'Hugo', 'Rojas', '1965-02-28', 'M', '912345675', 'hugo.rojas@email.com', 'Calle Las Magnolias 340, Surco', 'B+', 'Ninguna', 1),
(6, 'PN000006', 'Inés', 'Pinto', '2001-12-15', 'F', '912345676', 'ines.pinto@email.com', 'Av. Brasil 2200, Jesús María', 'O+', 'Ninguna', 0);

-- Usuarios de la App
INSERT INTO usuarios_app (id, usuario, nombre, rol, hash_clave, activo) VALUES
(1, 'admin_user', 'Percy Almeyda', 'ADMIN', '$2y$10$abcdefghijklmnopqrstuv', 1),
(2, 'med_carlos', 'Carlos Mendoza', 'MEDICO', '$2y$10$abcdefghijklmnopqrstuw', 1),
(3, 'recep_lucia', 'Lucía Torres', 'RECEPCION', '$2y$10$abcdefghijklmnopqrstux', 1);

-- Citas médicas (Fechas estables fijadas en Julio 2026)
INSERT INTO citas (id, paciente_id, medico_id, fecha_hora, estado, motivo, diagnostico) VALUES
(1, 1, 1, '2026-07-15 09:00:00', 'COMPLETADA', 'Chequeo cardiológico anual.', 'Arritmia leve detectada. Tratamiento con Propranolol iniciado.'),
(2, 2, 2, '2026-07-15 10:00:00', 'COMPLETADA', 'Control de crecimiento infantil.', 'Paciente sano. Dieta balanceada recomendada.'),
(3, 3, 3, '2026-07-15 11:00:00', 'CANCELADA', 'Consulta dermatológica por erupción cutánea.', NULL),
(4, 4, 2, '2026-07-15 12:00:00', 'PENDIENTE', 'Fiebre persistente de dos días.', NULL),
(5, 5, 6, '2026-07-16 08:30:00', 'COMPLETADA', 'Consulta médica general por dolor lumbar.', 'Lumbalgia mecánica. Reposo y analgésicos indicados.'),
(6, 1, 1, '2026-07-20 09:00:00', 'PENDIENTE', 'Control de arritmia post-tratamiento.', NULL);

-- Recetas
INSERT INTO recetas (id, cita_id, medicamento, dosis, indicaciones, duracion_dias) VALUES
(1, 1, 'Propranolol 40mg', '1 tableta cada 12 horas', 'Tomar después de los alimentos.', 30),
(2, 2, 'Multivitamínico Jarabe', '1 cucharadita en el desayuno', 'Uso continuo.', 60),
(3, 5, 'Ibuprofeno 400mg', '1 tableta cada 8 horas', 'Tomar por 5 días de ser necesario.', 5);

-- Pagos
INSERT INTO pagos (id, cita_id, monto, metodo, estado, fecha) VALUES
(1, 1, 150.00, 'TARJETA', 'PAGADO', '2026-07-15'),
(2, 2, 80.00, 'EFECTIVO', 'PAGADO', '2026-07-15'),
(3, 5, 50.00, 'TRANSFERENCIA', 'PENDIENTE', '2026-07-16');

-- Confirmamos los cambios en disco
COMMIT;

Uno de los errores más recurrentes al iniciar en base de datos es la interpretación errónea del valor **NULL**. NULL no representa un número cero, ni una cadena de texto vacía, ni un espacio en blanco; representa la total ausencia o desconocimiento de un dato. SQLite implementa una lógica tri-valorada (verdadero, falso y desconocido) para gestionar los nulos. Hoy estudiaremos cómo evitar trampas lógicas al consultarlo.

• Comprender la lógica tri-valorada y cómo se comporta NULL en las operaciones aritméticas y de comparación.
• Evitar el error de comparar nulos usando los operadores tradicionales (`=` o `!=`).
• Implementar consultas seguras empleando las cláusulas `IS NULL` e `IS NOT NULL`.
• Sustituir nulos en los reportes de salida usando las funciones estándar `COALESCE()` e `IFNULL()`.

1. La lógica tri-valorada con NULL

En SQLite, cualquier operación aritmética que involucre un valor NULL (por ejemplo, monto + NULL) devolverá siempre un valor NULL. A nivel de comparación lógica, evaluar un nulo con operadores tradicionales devuelve un estado UNKNOWN (desconocido), haciendo que la fila sea ignorada:

-- ERROR COMÚN: Esta consulta NUNCA devolverá registros, incluso si hay diagnósticos nulos
SELECT id, motivo FROM citas WHERE diagnostico = NULL;

-- SOLUCIÓN CORRECTA: Usar el operador de evaluación de estado
SELECT id, motivo FROM citas WHERE diagnostico IS NULL;

-- Para obtener las citas que SÍ poseen un diagnóstico registrado:
SELECT id, motivo, diagnostico FROM citas WHERE diagnostico IS NOT NULL;

2. Valores por defecto (DEFAULT)

En el Capítulo 7 declaramos en la tabla de médicos que la columna activo tiene un valor por defecto: activo INTEGER NOT NULL DEFAULT 1. Si al realizar una inserción omitimos esta columna, SQLite inyectará automáticamente un 1. Sin embargo, si insertamos explícitamente un NULL, SQLite STRICT fallará debido a la constraint `NOT NULL`.

3. Reemplazar NULL en reportes

Mostrar la palabra "NULL" en los reportes de cara al usuario final de la clínica resta profesionalismo. SQLite nos provee de dos funciones excelentes para formatear nulos en las consultas de lectura:

• IFNULL(columna, reemplazo): Función propietaria de SQLite que acepta exactamente dos parámetros. Si el primero es nulo, devuelve el segundo.
• COALESCE(col1, col2, ..., reemplazo): Función estándar de SQL. Evalúa una lista de parámetros de forma secuencial y devuelve el primer valor no nulo encontrado en la lista.

-- Uso de IFNULL para mostrar un texto alternativo
SELECT id, motivo, IFNULL(diagnostico, 'Sin diagnóstico registrado aún.') AS diagnostico_formateado
FROM citas;

-- Uso de COALESCE (equivalente y estándar)
SELECT id, documento, COALESCE(telefono, email, 'Sin contacto registrado') AS contacto_principal
FROM pacientes;

A medida que la aplicación móvil o el software clínico evoluciona, surge la necesidad de alterar la estructura original de la base de datos (añadir columnas, cambiar restricciones o renombrar tablas). Sin embargo, debido a su arquitectura simple y sin servidor, la cláusula ALTER TABLE en SQLite posee limitaciones muy estrictas en comparación con otros motores relacionales. Hoy aprenderemos a realizar estas alteraciones de forma segura.

• Conocer qué operaciones soporta la directiva `ALTER TABLE` nativa de SQLite.
• Añadir columnas nuevas y renombrar tablas existentes mediante comandos directos.
• Implementar el patrón de tabla temporal de 5 pasos para realizar modificaciones complejas de esquema.
• Gestionar los permisos de llaves foráneas durante la reconstrucción estructural de una tabla.

1. Capacidades nativas de ALTER TABLE

Nativamente, SQLite permite realizar tres operaciones estructurales rápidas sin necesidad de reconstruir los archivos físicos:

• Renombrar la tabla: Modifica el nombre de la tabla en el diccionario del sistema.
• Renombrar una columna: Cambia el nombre de un campo (soportado desde SQLite 3.25.0).
• Agregar una columna: Añade un campo nuevo al final de la tabla (no permite agregar llaves primarias ni restricciones `UNIQUE`).

-- Renombrar una tabla
ALTER TABLE usuarios_app RENAME TO usuarios_sistema;

-- Renombrar una columna
ALTER TABLE pacientes RENAME COLUMN direccion TO domicilio;

-- Agregar una columna nueva
ALTER TABLE especialidades ADD COLUMN codigo_interno TEXT DEFAULT 'ESP-GEN';

2. El patrón de tabla temporal (Complex Alterations)

Operaciones comunes como cambiar el tipo de datos de una columna en una tabla STRICT, eliminar un campo (en versiones previas a SQLite 3.35.0) o añadir una constraint NOT NULL a posteriori no están soportadas nativamente por ALTER TABLE. Para realizarlas, debemos implementar el **patrón temporal**:

-- 1) Apagar temporalmente la validación de llaves foráneas para poder borrar
PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;

-- 2) Crear la nueva tabla con la estructura deseada (ej. eliminando la columna 'alergias')
CREATE TABLE pacientes_nueva (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    documento        TEXT NOT NULL UNIQUE,
    nombre           TEXT NOT NULL,
    apellido         TEXT NOT NULL,
    fecha_nacimiento TEXT NOT NULL,
    sexo             TEXT NOT NULL CHECK (sexo IN ('M', 'F')),
    telefono         TEXT,
    email            TEXT,
    direccion        TEXT,
    tipo_sangre      TEXT,
    activo           INTEGER NOT NULL DEFAULT 1 CHECK (activo IN (0, 1))
) STRICT;

-- 3) Migrar los datos desde la tabla original seleccionando sólo las columnas a conservar
INSERT INTO pacientes_nueva (id, documento, nombre, apellido, fecha_nacimiento, sexo, telefono, email, direccion, tipo_sangre, activo)
SELECT id, documento, nombre, apellido, fecha_nacimiento, sexo, telefono, email, domicilio, tipo_sangre, activo
FROM pacientes;

-- 4) Eliminar la tabla antigua obsoleta
DROP TABLE pacientes;

-- 5) Renombrar la tabla nueva al nombre original
ALTER TABLE pacientes_nueva RENAME TO pacientes;

-- Confirmamos los cambios en disco y reactivamos las llaves foráneas
COMMIT;
PRAGMA foreign_keys = ON;

Al ingresar a la Parte III del manual, nos enfocaremos en la extracción e interrogación de datos. La sentencia SELECT es el bloque de construcción fundamental de las consultas relacionales, permitiéndonos proyectar las columnas necesarias, filtrar registros y ordenar los resultados devueltos por el motor. Hoy iniciaremos con la sintaxis y operadores relacionales básicos.

• Construir sentencias `SELECT` sencillas para leer datos de las tablas del modelo clínica.
• Asignar alias legibles a las columnas utilizando la palabra clave `AS`.
• Ordenar las filas resultantes de forma ascendente o descendente mediante `ORDER BY`.
• Filtrar registros específicos aplicando la cláusula `WHERE` con operadores lógicos.

1. La proyección de columnas (SELECT)

La proyección consiste en indicar qué columnas deseamos recuperar de la tabla. Aunque es común usar el asterisco (*) para traer todos los campos, es una mala práctica en producción y en redes móviles debido al consumo de ancho de banda innecesario:

-- Recomendado: Proyectar únicamente las columnas estrictamente necesarias
SELECT nombre, apellido, email FROM medicos;

-- Alias de columna (AS) para mejorar la visualización en los reportes
SELECT nombre AS primer_nombre, colegiatura AS nro_registro FROM medicos;

2. Ordenamiento de registros (ORDER BY)

El orden en que SQLite retorna las filas no está garantizado a menos que agregues la cláusula ORDER BY. Puedes ordenar por múltiples columnas y en sentidos opuestos (ascendente con `ASC` y descendente con `DESC`):

-- Ordena los médicos por especialidad_id ascendentemente; ante empates, por apellido descendente
SELECT apellido, nombre, especialidad_id 
FROM medicos 
ORDER BY especialidad_id ASC, apellido DESC;

3. El filtrado de datos (WHERE)

Para extraer únicamente los registros que cumplen con una condición particular, implementamos la cláusula WHERE combinando operadores lógicos (AND, OR, NOT) y relacionales (=, <>, >, <, >=, <=):

-- Obtiene pacientes del sexo femenino que tengan tipo de sangre O+
SELECT nombre, apellido, documento, tipo_sangre 
FROM pacientes 
WHERE sexo = 'F' AND tipo_sangre = 'O+';

-- Obtiene médicos activos de cardiología (especialidad_id = 1) o pediatría (2)
SELECT nombre, apellido, especialidad_id, activo 
FROM medicos 
WHERE (especialidad_id = 1 OR especialidad_id = 2) AND activo = 1;

A medida que la base de datos crece, las consultas sencillas comienzan a retornar cientos o miles de filas. Para construir interfaces web o móviles eficientes, es obligatorio paginar los datos limitando el set de resultados. Hoy estudiaremos cómo estructurar paginaciones con las cláusulas LIMIT y OFFSET, buscaremos patrones con LIKE y el exclusivo operador de SQLite GLOB, y filtraremos rangos de fechas formateadas como texto.

• Controlar el número de registros devueltos mediante la cláusula `LIMIT`.
• Implementar paginación móvil y web combinando `LIMIT` y `OFFSET`.
• Buscar patrones de texto parciales usando comodines con los operadores `LIKE` y `GLOB`.
• Realizar filtros de pertenencia a conjuntos (`IN`) y rangos lógicos de fechas (`BETWEEN`).

1. Paginación de resultados (LIMIT / OFFSET)

La cláusula LIMIT le ordena a SQLite detener la extracción tras obtener un número específico de filas. Complementariamente, OFFSET le indica al motor cuántas filas debe saltarse desde el inicio:

-- Devuelve los 3 primeros pacientes
SELECT id, nombre, apellido FROM pacientes LIMIT 3;

-- Paginación: Salta los primeros 2 registros y obtiene los siguientes 2
SELECT id, nombre, apellido FROM pacientes LIMIT 2 OFFSET 2;

2. Búsqueda de patrones (LIKE vs GLOB)

Para realizar búsquedas por aproximación sobre campos de texto, disponemos de dos operadores en SQLite:

• LIKE: Insensible a las mayúsculas (case-insensitive) para caracteres ASCII básicos. Utiliza los comodines % (cualquier cantidad de caracteres) y _ (un único carácter).
• GLOB: Operador exclusivo de SQLite. Es estrictamente sensible a mayúsculas (case-sensitive) y utiliza comodines Unix estándar (* para múltiples caracteres, ? para uno, y corchetes [a-z] para rangos).

-- Obtiene pacientes cuyos apellidos comiencen con 'P' o 'p'
SELECT nombre, apellido FROM pacientes WHERE apellido LIKE 'p%';

-- Obtiene médicos cuya colegiatura tenga el patrón exacto 'CMP' seguido de dígitos
SELECT nombre, apellido, colegiatura 
FROM medicos 
WHERE colegiatura GLOB 'CMP[0-9]*';

3. Conjuntos y Rangos (IN / BETWEEN)

El operador IN simplifica múltiples condiciones con OR agrupando valores en una lista. El operador BETWEEN filtra en un rango inclusivo cerrado:

-- Pacientes con tipo de sangre A-, O- u O+
SELECT nombre, apellido, tipo_sangre 
FROM pacientes 
WHERE tipo_sangre IN ('A-', 'O-', 'O+');

-- Pagos con montos en el rango de 50.00 a 100.00 inclusive
SELECT id, monto, metodo FROM pagos WHERE monto BETWEEN 50.00 AND 100.00;

4. Filtrar fechas como TEXT

Como las fechas en SQLite se almacenan como cadenas de texto bajo formato ISO-8601 ('YYYY-MM-DD HH:MM:SS'), la comparación alfanumérica (lexicográfica) funciona a la perfección para rangos y ordenamientos temporales:

-- Citas agendadas específicamente el día 15 de Julio de 2026
SELECT id, paciente_id, fecha_hora, estado 
FROM citas 
WHERE fecha_hora BETWEEN '2026-07-15 00:00:00' AND '2026-07-15 23:59:59';

Las funciones escalares toman valores individuales de la fila actual, realizan un cálculo o transformación y devuelven un único resultado por cada fila procesada. SQLite posee un set sumamente potente de funciones para manipular cadenas de texto, realizar operaciones matemáticas básicas y, muy especialmente, procesar y calcular diferencias temporales sobre fechas almacenadas como texto. Hoy aprenderemos a dominarlas.

• Concatenar texto empleando el operador estándar de tuberías dobles `||` en lugar de `CONCAT()`.
• Manipular strings usando funciones como `UPPER()`, `LOWER()`, `LENGTH()` y `SUBSTR()`.
• Aplicar funciones matemáticas de redondeo y obtención de números aleatorios.
• Utilizar las funciones `strftime()`, `date()` y `julianday()` para calcular tiempos y edades.

1. Concatenación y funciones de texto

SQLite se apega estrictamente al estándar SQL para la concatenación de textos. No existe la función CONCAT(); en su lugar, se utiliza el operador de tuberías dobles (||):

-- Concatenar nombre y apellido del médico con un espacio intermedio
SELECT nombre || ' ' || apellido AS nombre_completo, colegiatura FROM medicos;

-- UPPER, LOWER y obtención de tamaño de cadena
SELECT UPPER(apellido) AS apellido_mayusc, LOWER(email) AS email_minusc, LENGTH(documento) AS largo_doc 
FROM pacientes;

-- Extraer subcadenas (SUBSTR: string, inicio_1_index, longitud)
-- Obtiene las primeras 3 letras del apellido
SELECT apellido, SUBSTR(apellido, 1, 3) AS prefijo FROM pacientes;

2. Funciones numéricas básicas

SQLite cuenta con funciones matemáticas comunes para redondear resultados y realizar operaciones absolutas:

-- Redondear montos de pagos a un decimal, y obtener el valor absoluto
SELECT monto, ROUND(monto, 1) AS monto_redondeado, ABS(monto) AS valor_absoluto FROM pagos;

-- Obtener un número aleatorio entero (útil para sorteos de turnos)
SELECT nombre, apellido, RANDOM() AS token_aleatorio FROM medicos;

3. Funciones de fecha y hora (Manejo de TEXT)

Dado que las fechas en SQLite se almacenan bajo el tipo TEXT, el motor provee funciones auxiliares especiales para parsearlas y realizar operaciones matemáticas de adición o sustracción de tiempo:

• date(fecha, mod1, mod2, ...): Retorna la fecha en formato 'YYYY-MM-DD'.
• datetime(fecha, mod1, mod2, ...): Retorna fecha y hora en formato 'YYYY-MM-DD HH:MM:SS'.
• julianday(fecha, ...): Devuelve el día Juliano (número de días desde el 4713 a.C.), ideal para restar fechas y hallar diferencias exactas en días.
• strftime(formato, fecha, ...): Formatea la fecha usando marcadores (%d para día, %m para mes, %Y para año).

-- Calcular la edad exacta de los pacientes en años basándonos en julianday
SELECT nombre, apellido, fecha_nacimiento, 
       CAST((julianday('now') - julianday(fecha_nacimiento)) / 365.25 AS INTEGER) AS edad 
FROM pacientes;

-- Sumar días a una fecha (ej. calcular vencimiento de receta agregando la duración de días a la cita)
-- Asumimos la cita del 15 de Julio de 2026 sumándole 30 días
SELECT date('2026-07-15', '+30 days') AS vencimiento_receta;

-- Formatear fecha para el usuario final (ej: 'DD/MM/YYYY')
SELECT fecha_nacimiento, strftime('%d/%m/%Y', fecha_nacimiento) AS fecha_formateada FROM pacientes;

Las funciones de agregación nos permiten resumir colecciones enteras de registros en un único valor estadístico. Son herramientas indispensables para obtener métricas gerenciales de la clínica (por ejemplo, el total de dinero recaudado o el promedio de citas atendidas por médico). Hoy estudiaremos cómo estructurar agrupamientos y cómo aplicar filtros sobre la información ya sintetizada.

• Utilizar las funciones de agregación estándar de SQL (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`).
• Comprender la diferencia de comportamiento entre `COUNT(*)` y `COUNT(columna)` frente a registros con valores nulos.
• Agrupar datos basados en columnas comunes mediante la cláusula `GROUP BY`.
• Filtrar los grupos resultantes utilizando la cláusula `HAVING` en lugar de `WHERE`.

1. Funciones de agregación básicas

Las funciones resumen operan sobre una columna e ignoran de forma automática los valores NULL (a excepción de `COUNT(*)` que cuenta la fila física completa):

-- Total de dinero recaudado y promedio de montos pagados
SELECT SUM(monto) AS total_caja, AVG(monto) AS pago_promedio FROM pagos;

-- Obtener el precio mínimo y máximo registrado en la clínica
SELECT MIN(monto) AS pago_minimo, MAX(monto) AS pago_maximo FROM pagos;

-- La trampa de COUNT con NULL
-- count(*) cuenta todas las filas (6 citas); count(diagnostico) sólo cuenta las citas con dato no nulo (3 citas)
SELECT COUNT(*) AS total_citas, COUNT(diagnostico) AS citas_con_diagnostico FROM citas;

2. Agrupamiento de datos (GROUP BY)

La cláusula GROUP BY divide el set de resultados en "cajas" o subgrupos según los valores de una columna. La agregación se ejecuta de forma independiente para cada subgrupo:

-- Contar cuántos médicos están asignados a cada especialidad
SELECT especialidad_id, COUNT(*) AS cantidad_medicos 
FROM medicos 
GROUP BY especialidad_id;

-- Sumar lo recaudado por cada método de pago
SELECT metodo, SUM(monto) AS recaudado, COUNT(*) AS transacciones 
FROM pagos 
GROUP BY metodo;

3. Filtrado de agregados (HAVING)

La cláusula WHERE actúa fila por fila **antes** de que el agrupamiento suceda, por lo que no puede evaluar funciones agregadas (ej: WHERE SUM(monto) > 100 fallará). Para filtrar grupos **después** de procesar la agregación, se utiliza HAVING:

-- ERROR: Esto producirá un fallo de compilación SQL inmediato
SELECT especialidad_id, COUNT(*) FROM medicos WHERE COUNT(*) > 1 GROUP BY especialidad_id;

-- SOLUCIÓN: Usar HAVING para filtrar el subgrupo ya agregado
SELECT especialidad_id, COUNT(*) AS cantidad 
FROM medicos 
GROUP BY especialidad_id 
HAVING COUNT(*) > 1;

En bases de datos relacionales bien diseñadas, los datos están normalizados (separados en distintas tablas especializadas para evitar redundancias). Para reconstruir la información y presentar reportes comprensibles al usuario, debemos cruzar los datos. Hoy aprenderemos a enlazar tablas cruzando llaves primarias y foráneas mediante la cláusula estándar INNER JOIN.

• Comprender el concepto de normalización y cómo se relacionan físicamente las tablas.
• Evitar los productos cartesianos desastrosos producidos al consultar múltiples tablas sin criterios de enlace.
• Implementar la sintaxis de `INNER JOIN` utilizando la cláusula de empalme `ON`.
• Emplear alias de tablas para escribir consultas limpias y resolver ambigüedades de nombres de columnas.

1. La trampa del producto cartesiano

Si intentas listar dos tablas separadas por comas en la cláusula FROM sin definir una relación explícita de empalme, el motor cruzará cada fila de la primera tabla con cada fila de la segunda (multiplicación). Si tienes 8 médicos y 6 especialidades, obtendrás 48 filas de combinaciones absurdas e incorrectas:

-- EVITAR: Producto cartesiano (retorna datos cruzados incorrectamente)
SELECT medicos.nombre, especialidades.nombre FROM medicos, especialidades;

2. Enlazar datos con INNER JOIN

La cláusula INNER JOIN (o simplemente JOIN) filtra los registros combinados exigiendo que se cumpla la igualdad declarada en la cláusula ON (típicamente: llave_foranea = llave_primaria). Sólo se devuelven las filas que poseen coincidencias en ambas tablas:

-- Proyección correcta de médicos con su nombre de especialidad real
SELECT medicos.nombre, medicos.apellido, especialidades.nombre AS especialidad
FROM medicos
INNER JOIN especialidades ON medicos.especialidad_id = especialidades.id;

3. Uso de alias de tabla y consultas multi-nivel

Cuando las consultas enlazan tres o más tablas, escribir los nombres completos de las tablas se vuelve engorroso. Podemos asignarles alias cortos directamente en el FROM o JOIN:

-- Consulta de tres tablas: Citas enlazadas con Pacientes y Médicos
SELECT c.id AS cita_id, 
       c.fecha_hora, 
       p.nombre || ' ' || p.apellido AS paciente, 
       m.nombre || ' ' || m.apellido AS medico
FROM citas AS c
INNER JOIN pacientes AS p ON c.paciente_id = p.id
INNER JOIN medicos AS m ON c.medico_id = m.id
ORDER BY c.fecha_hora ASC;

A diferencia de INNER JOIN, que exige correspondencia rigurosa en ambas tablas, las uniones externas (Outer Joins) permiten conservar los registros de una tabla incluso si no tienen coincidencias en la tabla relacionada. Hoy estudiaremos la implementación de LEFT JOIN en SQLite, cómo utilizarlo para encontrar datos huérfanos o inactivos, y analizaremos las limitaciones honestas del motor: la ausencia de RIGHT JOIN y FULL JOIN.

• Diferenciar conceptualmente `INNER JOIN` de las uniones externas (`LEFT JOIN`).
• Consultar datos y preservar filas sin asociación rellenándolas con valores NULL.
• Implementar el patrón de exclusión `IS NULL` para ubicar registros inactivos (ej. pacientes sin citas).
• Simular y sortear la ausencia de soporte para `RIGHT JOIN` y `FULL JOIN` en SQLite.

1. La mecánica de LEFT JOIN

Un LEFT JOIN preserva el 100% de las filas de la tabla izquierda (la declarada en el `FROM`). Si un registro de la izquierda no encuentra coincidencia a la derecha (en la tabla del `JOIN`), SQLite completa las columnas de la derecha con valores NULL:

-- Listar todos los pacientes, tengan o no citas registradas
SELECT p.id, p.nombre, p.apellido, c.id AS cita_id, c.fecha_hora 
FROM pacientes AS p
LEFT JOIN citas AS c ON p.id = c.paciente_id;

Si observas el resultado, la paciente **Inés Pinto** (id = 6) aparecerá en el reporte con `cita_id = NULL` y `fecha_hora = NULL`, ya que fue sembrada sin ninguna cita médica en el Capítulo 8. Un `INNER JOIN` la habría excluido por completo.

2. Encontrar huérfanos o ausencias (Exclusión)

Una técnica de análisis muy potente es usar `LEFT JOIN` para filtrar únicamente los registros de la izquierda que **no** tienen correspondencia a la derecha. Añadimos un WHERE ... IS NULL:

-- Obtener la lista exclusiva de pacientes que NUNCA han reservado una cita
SELECT p.id, p.nombre, p.apellido 
FROM pacientes AS p
LEFT JOIN citas AS c ON p.id = c.paciente_id
WHERE c.id IS NULL;

3. Limitaciones: No RIGHT ni FULL JOIN

Para simplificar el tamaño del motor, los desarrolladores de SQLite decidieron no programar de forma nativa las cláusulas `RIGHT JOIN` ni `FULL OUTER JOIN`. Si intentas ejecutarlas, recibirás un error de sintaxis. Para sortear esto, aplicamos dos técnicas de emulación:

• Simular RIGHT JOIN: Invierte el orden de las tablas en tu consulta. Escribir A RIGHT JOIN B es matemáticamente idéntico a redactar B LEFT JOIN A.
• Simular FULL JOIN: Combina una consulta LEFT JOIN con su inversa utilizando el operador de conjunto UNION, el cual unifica los resultados eliminando duplicados.

-- Emulación de un FULL OUTER JOIN entre Pacientes y Citas en SQLite
SELECT p.id, p.nombre, c.id AS cita_id
FROM pacientes AS p
LEFT JOIN citas AS c ON p.id = c.paciente_id
UNION
SELECT p.id, p.nombre, c.id AS cita_id
FROM citas AS c
LEFT JOIN pacientes AS p ON c.paciente_id = p.id;

Soporte de combinaciones por motor

A medida que cruzamos múltiples tablas, calculamos agregaciones y filtramos por condiciones compuestas, nuestras consultas SQL se vuelven difíciles de leer y mantener. Tradicionalmente esto se resolvía anidando subconsultas en el FROM. Las **Common Table Expressions (CTEs)** o expresiones de tabla común, permiten estructurar estas subconsultas como tablas virtuales con nombre propio arriba del bloque principal, aumentando drásticamente la claridad del código.

• Definir la estructura sintáctica de una CTE utilizando la palabra clave `WITH`.
• Descomponer reportes complejos en bloques modulares legibles y reutilizables en memoria.
• Implementar consultas recursivas (`WITH RECURSIVE`) para generar series de datos al vuelo sin tablas físicas.
• Comparar el rendimiento y legibilidad de una subconsulta anidada frente a una CTE.

1. Sintaxis de una CTE estándar

Una CTE actúa como una vista temporal que sólo existe durante el tiempo de ejecución de esa consulta específica. Su estructura se declara al inicio:

-- CTE que calcula el total pagado agrupado por cita
WITH PagosPorCita AS (
    SELECT cita_id, SUM(monto) AS total_pagado
    FROM pagos
    GROUP BY cita_id
)
-- Consulta principal que consume la CTE como si fuera una tabla física
SELECT c.id AS cita_id, 
       c.fecha_hora, 
       p.nombre || ' ' || p.apellido AS paciente, 
       IFNULL(pg.total_pagado, 0.00) AS total_abonado
FROM citas AS c
INNER JOIN pacientes AS p ON c.paciente_id = p.id
LEFT JOIN PagosPorCita AS pg ON c.id = pg.cita_id;

2. El poder de las CTEs recursivas

Una característica sumamente potente y a menudo desconocida de SQLite es su soporte para WITH RECURSIVE. Esto permite generar series de números, calendarios o jerarquías al vuelo sin consumir espacio físico. Por ejemplo, generemos una agenda de fechas para los próximos 7 días a partir del 15 de Julio de 2026:

-- CTE recursiva que autogenera una secuencia de fechas
WITH RECURSIVE calendario(fecha) AS (
    -- Fila ancla (punto de inicio)
    VALUES('2026-07-15')
    UNION ALL
    -- Relación recursiva (suma 1 día a la fila anterior)
    SELECT date(fecha, '+1 day') 
    FROM calendario
    -- Condición de parada para evitar un bucle infinito
    LIMIT 7
)
SELECT fecha, strftime('%w', fecha) AS dia_semana FROM calendario;

3. CTEs múltiples

Puedes declarar varias CTEs en una sola sentencia separándolas por comas. Cada CTE puede hacer referencia a las declaradas anteriormente en la misma línea:

-- Encadenamiento de CTEs independientes
WITH MedicosCardiologos AS (
    SELECT id, nombre || ' ' || apellido AS medico 
    FROM medicos 
    WHERE especialidad_id = 1
),
CitasDeCardiologia AS (
    SELECT id, paciente_id, medico_id, fecha_hora 
    FROM citas 
    WHERE medico_id IN (SELECT id FROM MedicosCardiologos)
)
SELECT cc.fecha_hora, mc.medico, p.nombre || ' ' || p.apellido AS paciente
FROM CitasDeCardiologia AS cc
INNER JOIN MedicosCardiologos AS mc ON cc.medico_id = mc.id
INNER JOIN pacientes AS p ON cc.paciente_id = p.id;

¡Taller Práctico! Ha llegado el momento de consolidar los conocimientos adquiridos en la Parte III. A continuación, resolveremos cuatro requerimientos reales de analítica para la gestión de la clínica. Te sugerimos intentar escribir cada consulta SQL por tu cuenta sobre tu base de datos local antes de expandir y estudiar la solución propuesta.

• Extraer la lista de pacientes con saldos pendientes (deudores) cruzando citas, pagos y pacientes.
• Calcular un reporte de productividad médica (citas completadas y recaudación individual).
• Identificar a los pacientes con mayor recurrencia de atención en la clínica.
• Crear un consolidado de consultas por especialidad utilizando subconsultas agregadas con CTEs.

Ejercicio 1: Pacientes deudores (saldos pendientes)

Requerimiento: Obtener la lista de pacientes que asistieron y completaron su cita médica pero cuyo estado de pago figura como 'PENDIENTE', mostrando el monto de la deuda.

-- Solución al requerimiento de cobranza
SELECT p.id AS paciente_id, 
       p.nombre || ' ' || p.apellido AS paciente, 
       c.fecha_hora, 
       pag.monto AS deuda, 
       pag.estado AS estado_pago
FROM citas AS c
INNER JOIN pacientes AS p ON c.paciente_id = p.id
INNER JOIN pagos AS pag ON c.id = pag.cita_id
WHERE c.estado = 'COMPLETADA' AND pag.estado = 'PENDIENTE';

Ejercicio 2: Productividad y facturación médica

Requerimiento: Reportar el total de citas atendidas y el monto recaudado por cada médico. El reporte debe incluir a todos los médicos, incluso si no han atendido citas o no han recaudado dinero aún (mostrando 0 en tal caso), ordenado de mayor a menor facturación.

-- Solución combinando LEFT JOIN y agregaciones
SELECT m.id AS medico_id, 
       m.nombre || ' ' || m.apellido AS medico,
       COUNT(c.id) AS citas_atendidas,
       ROUND(IFNULL(SUM(p.monto), 0.00), 2) AS total_recaudado
FROM medicos AS m
LEFT JOIN citas AS c ON m.id = c.medico_id AND c.estado = 'COMPLETADA'
LEFT JOIN pagos AS p ON c.id = p.cita_id AND p.estado = 'PAGADO'
GROUP BY m.id
ORDER BY total_recaudado DESC;

Ejercicio 3: El paciente estrella (mayor recurrencia)

Requerimiento: Encontrar al paciente que ha completado la mayor cantidad de citas en la clínica.

-- Solución limitando a la fila superior
SELECT p.id AS paciente_id, 
       p.nombre || ' ' || p.apellido AS paciente,
       COUNT(c.id) AS citas_completadas
FROM pacientes AS p
INNER JOIN citas AS c ON p.id = c.paciente_id
WHERE c.estado = 'COMPLETADA'
GROUP BY p.id
ORDER BY citas_completadas DESC
LIMIT 1;

Ejercicio 4: Reporte consolidado de especialidades (con CTEs)

Requerimiento: Obtener un listado de todas las especialidades indicando la cantidad de médicos adscritos y el número total de citas agendadas históricamente en esa rama.

-- Solución modularizada con una CTE precalculada
WITH ConteoCitasPorMedico AS (
    SELECT medico_id, COUNT(*) AS total_citas
    FROM citas
    GROUP BY medico_id
)
SELECT e.nombre AS especialidad, 
       COUNT(DISTINCT m.id) AS total_medicos,
       IFNULL(SUM(cc.total_citas), 0) AS consultas_totales
FROM especialidades AS e
LEFT JOIN medicos AS m ON e.id = m.especialidad_id
LEFT JOIN ConteoCitasPorMedico AS cc ON m.id = cc.medico_id
GROUP BY e.id;

Al iniciar la Parte IV de nuestro manual, nos adentraremos en los comandos de manipulación de datos (DML) para modificar el estado de la base de datos. El comando INSERT permite añadir registros individuales o masivos, copiar información entre tablas relacionales y, mediante el dialecto único de SQLite, gestionar conflictos de restricciones en caliente sin detener la ejecución de nuestro programa.

• Redactar sentencias `INSERT INTO` tradicionales indicando columnas y valores.
• Insertar múltiples filas en una sola llamada de manera atómica para optimizar la velocidad.
• Duplicar o migrar datos masivamente combinando `INSERT INTO` con `SELECT`.
• Manejar conflictos de restricciones UNIQUE y PRIMARY KEY con `OR IGNORE` y `OR REPLACE`.

1. Inserción tradicional (Simple y Múltiple)

La inserción básica requiere declarar los nombres de las columnas y sus valores correspondientes. Para optimizar el tráfico y los bloqueos de archivo, SQLite soporta la inserción múltiple en una única sentencia:

-- Inserción simple de una especialidad
INSERT INTO especialidades (nombre, descripcion) 
VALUES ('Nutrición', 'Atención nutricional preventiva y clínica.');

-- Inserción múltiple en un solo viaje
INSERT INTO especialidades (nombre, descripcion) VALUES 
('Psicología', 'Terapia de salud mental y acompañamiento emocional.'),
('Urología', 'Tratamiento del sistema urinario y reproductor.');

2. Copiado de datos (INSERT ... SELECT)

Puedes rellenar una tabla a partir del resultado de una consulta de lectura. Es la técnica clásica para realizar migraciones, respaldos o poblar bitácoras históricas:

-- Insertar un registro de auditoría leyendo datos del médico insertado
INSERT INTO auditoria (tabla_afectada, operacion, usuario_bd, registro_id, fecha_hora, datos_nuevos)
SELECT 'especialidades', 'INSERT', 'admin_user', id, '2026-07-20 12:00:00', '{"nombre": "' || nombre || '"}'
FROM especialidades 
WHERE nombre = 'Psicología';

3. Gestión de conflictos de restricciones

SQLite cuenta con modificadores excelentes para manejar colisiones de llaves duplicadas:

• INSERT OR IGNORE: Si la fila a insertar viola una restricción de clave primaria o índice único, el motor la ignora silenciosamente (sin arrojar un error al programa) y continúa con la ejecución.
• INSERT OR REPLACE: Si hay una colisión, elimina el registro en conflicto e inserta el nuevo valor (sobrescritura automática).

-- Ignora la inserción porque 'Cardiología' ya existe (UNIQUE en columna nombre)
INSERT OR IGNORE INTO especialidades (nombre, descripcion) 
VALUES ('Cardiología', 'Intento duplicado de inserción');

-- Sobrescribe la especialidad con ID = 1 modificando su descripción
INSERT OR REPLACE INTO especialidades (id, nombre, descripcion) 
VALUES (1, 'Cardiología', 'Tratamiento avanzado del aparato cardiocirculatorio.');

Los comandos destructivos de modificación (UPDATE) y eliminación (DELETE) deben manejarse con sumo cuidado en cualquier base de datos. Hoy aprenderemos a actualizar registros de forma condicionada y a borrar filas de forma controlada. Además, analizaremos cómo se comporta SQLite ante la ausencia de la cláusula TRUNCATE TABLE y descubriremos cómo resetear las secuencias internas de ID de autoincremento.

• Actualizar información en columnas específicas limitando el impacto mediante la cláusula `WHERE`.
• Eliminar filas de forma física empleando filtros rigurosos de exclusión.
• Comprender el peligro de omitir la cláusula `WHERE` en comandos destructivos.
• Simular la sentencia `TRUNCATE TABLE` en SQLite restableciendo el registro en `sqlite_sequence`.

1. Modificación de datos (UPDATE)

El comando UPDATE cambia los valores de las columnas indicadas en las filas que cumplan la condición del `WHERE`. Puedes modificar múltiples columnas en una sola sentencia separándolas por comas:

-- Actualizar la dirección y teléfono de la paciente Carmen Luz (id = 2)
UPDATE pacientes 
SET direccion = 'Calle Los Horizontes 789, San Isidro', telefono = '999888777' 
WHERE id = 2;

-- Desactivar temporalmente la cuenta de un usuario
UPDATE usuarios_app 
SET activo = 0 
WHERE usuario = 'recep_lucia';

2. Eliminación física de registros (DELETE)

El comando DELETE FROM borra filas completas del disco físico. Al igual que el update, requiere un filtro condicional para no eliminar todo el universo de datos:

-- Eliminar un registro de pago cancelado o rechazado de forma segura
DELETE FROM pagos 
WHERE id = 3 AND estado = 'PENDIENTE';

3. Sorpresa: La simulación de TRUNCATE TABLE

En motores pesados existe la directiva TRUNCATE TABLE pagos;, la cual vacía la tabla y reinicia la secuencia numérica del ID autoincremental de forma instantánea. SQLite **no soporta** esta directiva. Si realizas un DELETE FROM pagos;, los datos se borrarán, pero al insertar un nuevo pago, el motor continuará asignando IDs a partir del último valor correlativo anterior.

Para emular un truncado real en SQLite reseteando el contador de secuencia, interactuamos con la tabla interna del sistema sqlite_sequence:

-- 1) Vaciamos la tabla de pagos físicamente
DELETE FROM pagos;

-- 2) Eliminamos la secuencia autoincremental de la tabla de pagos en el registro del sistema
DELETE FROM sqlite_sequence WHERE name = 'pagos';

Las transacciones son el mecanismo fundamental para garantizar la integridad de los datos en cualquier base de datos bajo el estándar ACID (Atomicidad, Consistencia, Aislamiento y Durabilidad). Hoy estudiaremos cómo maneja SQLite las transacciones físicas sobre el disco, los comandos de control (BEGIN, COMMIT y ROLLBACK), sus tres modos de bloqueo de inicio (DEFERRED, IMMEDIATE y EXCLUSIVE), y la activación del modo WAL para optimizar la concurrencia.

• Agrupar múltiples sentencias de modificación en un bloque de ejecución atómico con `COMMIT`.
• Revertir de forma segura operaciones incompletas ante fallos de validación con `ROLLBACK`.
• Diferenciar el comportamiento de los tres niveles de bloqueo en el inicio de transacciones.
• Activar el modo WAL (Write-Ahead Logging) para permitir lectores y escritores simultáneos.

1. Control de transacciones atómicas (BEGIN, COMMIT y ROLLBACK)

Por defecto, SQLite opera en modo *autocommit*, lo que significa que cada sentencia SQL individual se ejecuta dentro de su propia mini-transacción implícita. Para agrupar operaciones complejas y asegurar que se ejecuten "todas o ninguna" (atomicidad), abrimos una transacción explícita:

-- Transacción exitosa: se registra el pago y se marca la cita como COMPLETADA
BEGIN TRANSACTION;

UPDATE citas 
SET estado = 'COMPLETADA' 
WHERE id = 1;

INSERT INTO pagos (cita_id, monto, metodo, estado, fecha) 
VALUES (1, 150.00, 'TARJETA', 'PAGADO', '2026-07-01 10:30:00');

COMMIT;

Si una de las sentencias del bloque falla (por ejemplo, una violación de llave única) o si cancelamos el proceso desde la aplicación, debemos revertir todos los cambios realizados para evitar inconsistencias en el modelo clínico:

-- Transacción fallida o cancelada
BEGIN TRANSACTION;

-- Se inserta un médico con colegiatura duplicada (violación de UNIQUE)
INSERT INTO medicos (nombre, apellido, colegiatura, especialidad_id, email, activo)
VALUES ('Ana', 'Martínez', 'CMP-88888', 1, 'ana.martinez@email.com', 1);

-- Al detectar el error en la aplicación, revertimos todo al estado anterior
ROLLBACK;

2. Modos de bloqueo al iniciar transacciones

A diferencia de motores como PostgreSQL o MySQL, donde configuras el nivel de aislamiento de la transacción (como `READ COMMITTED` o `SERIALIZABLE`), SQLite ofrece siempre un aislamiento **SERIALIZABLE** mediante el bloqueo del archivo de base de datos. Lo que controlamos en SQLite es el momento en que se adquiere ese bloqueo usando tres variantes de BEGIN:

-- 1. DEFERRED (Por defecto)
BEGIN DEFERRED TRANSACTION;
-- No se adquiere ningún bloqueo inicial. Los bloqueos se adquieren dinámicamente
-- cuando se lee (Shared lock) o se escribe (Reserved lock) por primera vez.

-- 2. IMMEDIATE
BEGIN IMMEDIATE TRANSACTION;
-- Adquiere un bloqueo de escritura (Reserved lock) inmediatamente. Impide que otros
-- clientes abran transacciones de escritura, pero permite que sigan leyendo.

-- 3. EXCLUSIVE
BEGIN EXCLUSIVE TRANSACTION;
-- Adquiere un bloqueo exclusivo de inmediato. Impide cualquier tipo de acceso 
-- (lectura o escritura) por parte de otras conexiones.

3. Concurrencia moderna: El Modo WAL

Por defecto, cuando un escritor está modificando la base de datos en SQLite, ningún lector puede acceder al archivo, provocando demoras en aplicaciones web. SQLite resuelve esto mediante el modo **Write-Ahead Logging (WAL)**. En este modo, los cambios se escriben en un archivo auxiliar separado (`.db-wal`), permitiendo que los lectores lean los datos limpios directamente del archivo original de forma concurrente:

-- Habilitar el modo WAL en la base de datos
PRAGMA journal_mode = WAL;

-- Verificar que el modo WAL esté activo
PRAGMA journal_mode;
-- Resultado esperado: 'wal'

En el desarrollo de aplicaciones clínicas, a menudo necesitamos procesar datos intermedios complejos (por ejemplo, para generar reportes mensuales de productividad o auditorías de facturación) sin alterar la estructura física ni el almacenamiento permanente de la base de datos. Para ello, SQLite provee tablas temporales y la sintaxis CTAS (Create Table As Select), herramientas esenciales para optimizar el rendimiento y aislar conjuntos de datos de trabajo por sesión.

• Crear y estructurar manualmente tablas temporales con la cláusula `TEMP`.
• Utilizar `CREATE TEMP TABLE ... AS SELECT` (CTAS) para almacenar rápidamente el resultado de consultas complejas.
• Comprender el ciclo de vida, alcance y aislamiento exclusivo por conexión de las tablas temporales.
• Conocer las limitaciones de SQLite con respecto a llaves foráneas e índices en el uso de CTAS y tablas temporales.

1. Creación de tablas temporales manuales (TEMP TABLE)

Una tabla temporal en SQLite se define utilizando la palabra clave TEMP o TEMPORARY. Viven en un área de almacenamiento temporal (en memoria o en archivos temporales del sistema) y solo son visibles para la conexión que las creó. Las demás conexiones activas a la base de datos no pueden verlas ni interactuar con ellas:

-- Crear una tabla temporal manual con tipado estricto
CREATE TEMP TABLE temp_pacientes_riesgo (
    paciente_id INTEGER PRIMARY KEY,
    documento TEXT UNIQUE,
    nombre_completo TEXT,
    alergias TEXT
) STRICT;

-- Poblar la tabla temporal filtrando pacientes con alergias graves
INSERT INTO temp_pacientes_riesgo (paciente_id, documento, nombre_completo, alergias)
SELECT id, documento, nombre || ' ' || apellido, alergias
FROM pacientes
WHERE alergias IS NOT NULL AND alergias != 'Ninguna' AND alergias LIKE '%penicilina%';

-- Consultar la tabla temporal de trabajo
SELECT * FROM temp_pacientes_riesgo;

2. El patrón CTAS (CREATE TABLE AS SELECT)

La cláusula CREATE TEMP TABLE ... AS SELECT nos permite crear la estructura de una tabla temporal y llenarla con datos en un solo paso. Es sumamente útil para capturar "fotografías" de los datos en un instante específico para análisis posteriores:

-- Crear tabla temporal de productividad médica acumulada con CTAS
CREATE TEMP TABLE temp_productividad_resumen AS
SELECT 
    m.id AS medico_id,
    m.nombre || ' ' || m.apellido AS medico,
    e.nombre AS especialidad,
    COUNT(c.id) AS total_citas,
    IFNULL(SUM(p.monto), 0.0) AS total_facturado
FROM medicos m
JOIN especialidades e ON m.especialidad_id = e.id
JOIN citas c ON m.id = c.medico_id
LEFT JOIN pagos p ON c.id = p.cita_id
WHERE c.estado = 'COMPLETADA'
GROUP BY m.id;

-- Consultar la tabla resultante del CTAS
SELECT * 
FROM temp_productividad_resumen 
WHERE total_facturado > 100.0 
ORDER BY total_facturado DESC;

3. Ciclo de vida y destrucción de tablas temporales

Las tablas temporales de SQLite se destruyen automáticamente tan pronto como la conexión de base de datos se cierra (por ejemplo, al finalizar la ejecución de un script PHP o al salir del cliente CLI con .exit). Sin embargo, es una buena práctica liberarlas explícitamente en el script cuando ya no se necesitan para no consumir memoria innecesariamente:

-- Verificar la existencia y estructura en la base de datos temporal
.tables
-- Salida típica en consola: temp.temp_pacientes_riesgo  temp.temp_productividad_resumen

-- Eliminar las tablas temporales manualmente
DROP TABLE IF EXISTS temp_pacientes_riesgo;
DROP TABLE IF EXISTS temp_productividad_resumen;

4. Comparativa de tablas temporales multi-motor

La tabla siguiente muestra cómo manejan las tablas temporales los cuatro motores principales de la serie:

Una de las mayores ventajas de agrupar operaciones clínicas bajo una transacción es evitar la fragmentación de datos. Imaginemos un flujo donde se agenda una cita médica, se procesa un pago por adelantado en ventanilla y se le expide al paciente una receta inicial. Si la inserción del pago o de la receta falla, la cita no debe quedar flotando como agendada sin el respaldo correspondiente. Hoy implementaremos un flujo transaccional atómico completo tanto en SQL nativo como en el backend.

• Construir un flujo secuencial clínico multitabla asegurado por transacciones.
• Utilizar `last_insert_rowid()` para enlazar llaves foráneas dependientes en caliente.
• Simular fallos y verificar cómo `ROLLBACK` mantiene el modelo en su estado original.
• Implementar la lógica transaccional en un script PHP backend utilizando bloques `try-catch` y PDO.

1. La transacción atómica en SQL nativo

En el CLI de SQLite podemos ejecutar este bloque directamente. Haremos uso de la función integrada last_insert_rowid(), la cual lee el último identificador de fila autoincremental asignado por la conexión actual:

-- Forzar transacciones inmediatas para evitar bloqueos por concurrencia
BEGIN IMMEDIATE TRANSACTION;

-- Paso 1: Registrar la nueva cita médica para el paciente
INSERT INTO citas (paciente_id, medico_id, fecha_hora, estado, motivo, diagnostico)
VALUES (2, 3, '2026-07-15 11:00:00', 'PENDIENTE', 'Control de hipertensión', NULL);

-- Paso 2: Registrar el pago utilizando el ID retornado por last_insert_rowid()
INSERT INTO pagos (cita_id, monto, metodo, estado, fecha)
VALUES (last_insert_rowid(), 120.00, 'EFECTIVO', 'PAGADO', '2026-07-15 11:02:00');

-- Paso 3: Registrar la receta médica asociada
INSERT INTO recetas (cita_id, medicamento, dosis, indicaciones, duracion_dias)
VALUES (last_insert_rowid(), 'Losartán 50mg', '1 tableta al día', 'Tomar por las mañanas', 30);

-- Si no hay errores de integridad, confirmamos en disco
COMMIT;

2. Simulación de fallo y reversión (ROLLBACK)

¿Qué sucede si intentamos registrar una cita en un horario donde el médico ya está ocupado? La restricción UNIQUE(medico_id, fecha_hora) disparará un error y obligará a la base de datos a abortar la transacción completa:

-- Caso con error: Médico ya tiene una cita reservada
BEGIN IMMEDIATE TRANSACTION;

-- Este INSERT fallará porque el médico 1 ya tiene cita agendada el '2026-07-02 09:00:00'
INSERT INTO citas (paciente_id, medico_id, fecha_hora, estado, motivo)
VALUES (3, 1, '2026-07-02 09:00:00', 'PENDIENTE', 'Dolor muscular');
-- Mensaje esperado: Runtime error: UNIQUE constraint failed: citas.medico_id, citas.fecha_hora

-- Dado que el paso previo falló, abortamos de inmediato. Ningún cambio se conserva
ROLLBACK;

3. Implementación real en PHP con PDO

Dado que SQLite es una base de datos embebida, la lógica transaccional y el control de errores suelen recaer firmemente en la capa del lenguaje backend. A continuación, vemos la implementación correcta usando PHP y el manejo de excepciones de PDO:

<?php
try {
    // 1. Establecer conexión con la base de datos clínica
    $pdo = new PDO('sqlite:clinica.db');
    
    // Forzar el reporte de errores mediante excepciones
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Habilitar de forma obligatoria las llaves foráneas
    $pdo->exec('PRAGMA foreign_keys = ON;');

    // 2. Iniciar la transacción explícita
    $pdo->beginTransaction();

    // 3. Insertar la Cita
    $stmtCita = $pdo->prepare("
        INSERT INTO citas (paciente_id, medico_id, fecha_hora, estado, motivo, diagnostico)
        VALUES (:paciente_id, :medico_id, :fecha_hora, 'PENDIENTE', :motivo, NULL)
    ");
    $stmtCita->execute([
        ':paciente_id' => 3,
        ':medico_id'   => 4,
        ':fecha_hora'  => '2026-07-15 15:30:00',
        ':motivo'      => 'Chequeo dermatológico habitual'
    ]);

    // Recuperar el ID de la cita generado automáticamente
    $citaId = $pdo->lastInsertId();

    // 4. Insertar el Pago asociado a la cita
    $stmtPago = $pdo->prepare("
        INSERT INTO pagos (cita_id, monto, metodo, estado, fecha)
        VALUES (:cita_id, :monto, 'TRANSFERENCIA', 'PAGADO', :fecha)
    ");
    $stmtPago->execute([
        ':cita_id' => $citaId,
        ':monto'   => 150.00,
        ':fecha'   => '2026-07-15 15:35:00'
    ]);

    // 5. Insertar la Receta médica
    $stmtReceta = $pdo->prepare("
        INSERT INTO recetas (cita_id, medicamento, dosis, indicaciones, duracion_dias)
        VALUES (:cita_id, :medicamento, :dosis, :indicaciones, :duracion)
    ");
    $stmtReceta->execute([
        ':cita_id'     => $citaId,
        ':medicamento' => 'Betametasona Crema 0.05%',
        ':dosis'       => 'Aplicar una capa delgada',
        ':indicaciones'=> 'Colocar en la zona afectada cada 12 horas tras higiene',
        ':duracion'    => 7
    ]);

    // 6. Si no hubo fallos en ningún paso, confirmamos la transacción
    $pdo->commit();
    echo "Transacción clínica completada con éxito. Cita ID: " . $citaId;

} catch (Exception $e) {
    // 7. En caso de error, revertimos todo
    if (isset($pdo) && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "Error en la transacción clínica. Cambios revertidos: " . $e->getMessage();
}
?>

A medida que nuestro modelo de base de datos clínica crece, las consultas que involucran múltiples relaciones (JOINs), agregados y formateo de datos tienden a volverse largas y repetitivas. Para evitar duplicar este código SQL en múltiples archivos de nuestra aplicación PHP, SQLite nos permite crear Vistas. Una vista es una tabla virtual definida a partir de una consulta SQL que se ejecuta dinámicamente cuando es consultada.

• Definir e implementar vistas de lectura para simplificar joins complejos entre pacientes, médicos y citas.
• Encapsular cálculos de negocio y agregados (saldos pendientes) en una estructura centralizada.
• Consultar y filtrar vistas aplicando predicados WHERE optimizados por el motor.
• Conocer las limitaciones de SQLite respecto a vistas modificables (Updatable Views) y vistas materializadas.

1. Creación de una vista para detalle de citas

En lugar de escribir cuatro joins cada vez que necesitamos renderizar la agenda de la clínica en la aplicación, creamos una vista que unifique la información clave:

-- Crear una vista para simplificar la lectura de la agenda clínica
CREATE VIEW vista_agenda_detallada AS
SELECT 
    c.id AS cita_id,
    c.fecha_hora,
    c.estado AS estado_cita,
    p.documento AS paciente_documento,
    p.nombre || ' ' || p.apellido AS paciente_nombre,
    m.nombre || ' ' || m.apellido AS medico_nombre,
    e.nombre AS especialidad
FROM citas c
JOIN pacientes p ON c.paciente_id = p.id
JOIN medicos m ON c.medico_id = m.id
JOIN especialidades e ON m.especialidad_id = e.id;

-- Consultar la vista como si fuera una tabla normal
SELECT * 
FROM vista_agenda_detallada 
WHERE estado_cita = 'PENDIENTE' 
ORDER BY fecha_hora ASC;

2. Vista analítica para cobros y saldos deudores

Las vistas también pueden encapsular fórmulas matemáticas y filtros lógicos complejos. En este ejemplo, creamos una vista que calcula dinámicamente el saldo deudor de cada paciente basándose en el costo estándar de cita médica y los pagos registrados:

-- Crear vista para el cálculo de saldos y morosidad de pacientes
CREATE VIEW vista_pacientes_deudores AS
SELECT 
    p.id AS paciente_id,
    p.documento,
    p.nombre || ' ' || p.apellido AS paciente_nombre,
    COUNT(c.id) AS total_citas,
    -- Estimamos 150.00 como costo fijo de cada cita agendada
    (COUNT(c.id) * 150.00) AS total_cargado,
    IFNULL(SUM(pa.monto), 0.0) AS total_pagado,
    ((COUNT(c.id) * 150.00) - IFNULL(SUM(pa.monto), 0.0)) AS saldo_deudor
FROM pacientes p
JOIN citas c ON p.id = c.paciente_id
LEFT JOIN pagos pa ON c.id = pa.cita_id
WHERE c.estado != 'CANCELADA'
GROUP BY p.id
HAVING saldo_deudor > 0.0;

-- Consultar los pacientes con saldo pendiente utilizando la vista
SELECT paciente_nombre, total_citas, saldo_deudor 
FROM vista_pacientes_deudores 
ORDER BY saldo_deudor DESC;

3. Gestión de vistas en SQLite

Las vistas se almacenan en el catálogo del esquema de la base de datos. Para listarlas y administrarlas, podemos interactuar con el CLI o con la tabla del sistema sqlite_schema:

-- Listar todas las vistas registradas mediante comando punto del CLI
.views
-- Salida esperada: vista_agenda_detallada  vista_pacientes_deudores

-- Consultar la definición SQL exacta de una vista en el catálogo
SELECT name, sql 
FROM sqlite_schema 
WHERE type = 'view';

-- Eliminar una vista de la base de datos
DROP VIEW IF EXISTS vista_agenda_detallada;

En bases de datos cliente-servidor como MySQL o PostgreSQL, los procedimientos almacenados (CREATE PROCEDURE) permiten compilar y ejecutar lógica compleja directamente en el servidor de base de datos, reduciendo el tráfico de red. Sin embargo, en SQLite no existen los procedimientos almacenados. Al ser un motor embebido que comparte el mismo proceso que la aplicación, SQLite delega esta responsabilidad por completo al lenguaje anfitrión (como PHP o Python).

• Comprender la limitación honesta de SQLite respecto a la ejecución de código procedural en el motor.
• Implementar el patrón de "procedimiento almacenado" simulado en la capa de aplicación (PHP).
• Asegurar la consistencia de las simulaciones mediante validaciones previas y transacciones.
• Comparar la arquitectura procedural entre los cuatro motores principales de la serie.

1. Por qué SQLite no implementa procedimientos almacenados

A diferencia de los motores tradicionales, SQLite no tiene un demonio o proceso de servidor que escuche peticiones en un puerto. La base de datos es simplemente una biblioteca enlazada al backend. Dado que el costo de comunicación de red entre la aplicación y el motor es **cero** (todo ocurre en la memoria del mismo proceso), no hay una ganancia de rendimiento real al ejecutar loops o condicionales dentro del motor de base de datos. Por ende, la lógica procedural se escribe en el lenguaje de programación de la aplicación.

2. Simulación de un Procedure clínico en PHP

A continuación, simularemos un procedimiento almacenado para registrar una cita. La lógica requiere validar que el médico esté activo y que no tenga otra cita agendada en la misma fecha y hora. Si las validaciones pasan, se inserta el registro; de lo contrario, se lanza un error y se aborta la transacción:

<?php
/**
 * Simulación del procedimiento almacenado "sp_registrar_cita"
 */
function sp_registrar_cita(PDO $pdo, $paciente_id, $medico_id, $fecha_hora, $motivo) {
    try {
        $pdo->beginTransaction();

        // 1. Validar que el médico esté activo
        $stmtMed = $pdo->prepare("SELECT activo FROM medicos WHERE id = :medico_id");
        $stmtMed->execute([':medico_id' => $medico_id]);
        $medico = $stmtMed->fetch(PDO::FETCH_ASSOC);

        if (!$medico) {
            throw new Exception("El médico especificado no existe.");
        }
        if ($medico['activo'] == 0) {
            throw new Exception("El médico seleccionado no está activo.");
        }

        // 2. Validar que el médico no tenga cruce de horarios (Cita duplicada)
        $stmtCita = $pdo->prepare("
            SELECT COUNT(*) AS total 
            FROM citas 
            WHERE medico_id = :medico_id AND fecha_hora = :fecha_hora AND estado != 'CANCELADA'
        ");
        $stmtCita->execute([
            ':medico_id' => $medico_id,
            ':fecha_hora' => $fecha_hora
        ]);
        $cruce = $stmtCita->fetch(PDO::FETCH_ASSOC);

        if ($cruce['total'] > 0) {
            throw new Exception("El médico ya tiene una cita agendada en esa fecha y hora.");
        }

        // 3. Si todo está correcto, proceder a la inserción
        $stmtInsert = $pdo->prepare("
            INSERT INTO citas (paciente_id, medico_id, fecha_hora, estado, motivo, diagnostico)
            VALUES (:paciente_id, :medico_id, :fecha_hora, 'PENDIENTE', :motivo, NULL)
        ");
        $stmtInsert->execute([
            ':paciente_id' => $paciente_id,
            ':medico_id'   => $medico_id,
            ':fecha_hora'  => $fecha_hora,
            ':motivo'      => $motivo
        ]);

        $cita_id = $pdo->lastInsertId();
        $pdo->commit();
        
        return [
            'status' => 'success',
            'cita_id' => $cita_id,
            'message' => 'Cita registrada correctamente.'
        ];

    } catch (Exception $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        return [
            'status' => 'error',
            'message' => $e->getMessage()
        ];
    }
}
?>

3. Comparativa de capacidad procedural de base de datos

A continuación, vemos una comparativa del soporte de procedimientos almacenados en los cuatro motores de la serie:

En SQLite no es posible definir funciones directamente en la base de datos utilizando sintaxis SQL (como CREATE FUNCTION). Sin embargo, SQLite permite inyectar funciones personalizadas escritas en el lenguaje de programación anfitrión a la conexión de base de datos activa. Estas funciones se conocen como UDF (User-Defined Functions) y pueden ejecutarse dentro de consultas SQL como si fuesen funciones nativas del motor.

• Comprender la arquitectura de las funciones definidas por el usuario (UDF) en SQLite.
• Registrar funciones escalares personalizadas en tiempo de ejecución utilizando PDO en PHP.
• Utilizar funciones inyectadas desde el backend para operaciones de formateo y validación en consultas SQL.
• Reconocer el impacto en la portabilidad del archivo de base de datos al depender de UDFs.

1. Cómo funcionan las UDF en SQLite

Dado que la biblioteca de SQLite se ejecuta dentro del mismo espacio de direcciones que tu aplicación PHP, registrar una UDF es extremadamente rápido y eficiente. No requiere compilar extensiones ni código en C. Simplemente asociamos una función de PHP (un closure o callback) a un alias de función SQL en el objeto de conexión de PDO utilizando el método sqliteCreateFunction:

<?php
// Conectar a la base de datos clínica
$pdo = new PDO('sqlite:clinica.db');

// Registrar la función UDF "calcular_edad" en SQLite
// Parámetros: alias SQL, función callback de PHP, número de argumentos esperados
$pdo->sqliteCreateFunction('calcular_edad', function($fecha_nacimiento) {
    if (!$fecha_nacimiento) return null;
    $nacimiento = new DateTime($fecha_nacimiento);
    $hoy = new DateTime('2026-08-29'); // Fecha de simulación fija del curso
    $edad = $hoy->diff($nacimiento);
    return $edad->y;
}, 1);

// Ejecutar una consulta SQL utilizando la UDF inyectada
$stmt = $pdo->query("
    SELECT documento, nombre, apellido, calcular_edad(fecha_nacimiento) AS edad 
    FROM pacientes 
    WHERE activo = 1
");
$pacientes = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Imprimir los datos con las edades calculadas al vuelo por PHP
foreach ($pacientes as $p) {
    echo "Paciente: {$p['nombre']} {$p['apellido']} - Edad: {$p['edad']} años\n";
}
?>

2. UDF para validación segura de contraseñas

Otro caso de uso indispensable es la verificación de credenciales. SQLite no tiene funciones criptográficas nativas potentes para descifrar algoritmos modernos como `bcrypt` o `argon2`. Al registrar una UDF que invoque las bibliotecas nativas de seguridad de PHP, resolvemos la autenticación directamente en la query:

<?php
// Registrar la UDF "validar_hash"
$pdo->sqliteCreateFunction('validar_hash', function($password_ingresado, $hash_guardado) {
    // Retorna 1 si coincide, 0 si no (SQLite interpreta 1 como verdadero y 0 como falso)
    return password_verify($password_ingresado, $hash_guardado) ? 1 : 0;
}, 2);

// Credenciales enviadas por el formulario
$usuario_ingresado = 'percy';
$clave_ingresada = 'clave_secreta_123';

// Consulta SQL que valida la credencial usando la UDF
$stmt = $pdo->prepare("
    SELECT id, usuario, rol 
    FROM usuarios_app 
    WHERE usuario = :usuario AND validar_hash(:clave, hash_clave) = 1 AND activo = 1
");
$stmt->execute([
    ':usuario' => $usuario_ingresado,
    ':clave'   => $clave_ingresada
]);
$usuario_logueado = $stmt->fetch(PDO::FETCH_ASSOC);

if ($usuario_logueado) {
    echo "Autenticación exitosa. Rol: " . $usuario_logueado['rol'];
} else {
    echo "Credenciales inválidas.";
}
?>

A diferencia de motores como MySQL (que provee variables de usuario con @var), PostgreSQL (con set_config) o SQL Server (con SESSION_CONTEXT), SQLite no tiene variables de sesión nativas. En el desarrollo de sistemas clínicos, esto plantea un desafío: ¿cómo comunicamos el usuario autenticado en la aplicación (por ejemplo, el médico logueado en PHP) a los triggers internos del motor para que registren la auditoría? Hoy aprenderemos el patrón de simulación mediante tablas temporales específicas de la conexión.

• Analizar la limitación de SQLite con respecto al almacenamiento de estados de sesión.
• Implementar el patrón de variables de sesión utilizando tablas temporales de un solo registro.
• Vincular variables temporales de sesión con los triggers del motor para auditar operaciones.
• Escribir un script PHP que inicialice y gestione este contexto de sesión de manera automatizada.

1. El patrón de Tabla Temporal de Variables de Sesión

Como las tablas temporales son exclusivas de la conexión que las crea, podemos diseñar una tabla temporal llamada temp.variables_sesion de estructura clave-valor. Esto nos permite definir variables en caliente desde el backend y leerlas en cualquier consulta SQL o trigger dentro de la misma sesión:

-- Crear la tabla temporal de variables de sesión en SQLite
CREATE TEMP TABLE variables_sesion (
    clave TEXT PRIMARY KEY,
    valor TEXT
) STRICT;

-- Registrar el usuario activo de la aplicación en la sesión actual
INSERT OR REPLACE INTO variables_sesion (clave, valor) 
VALUES ('usuario_app', 'percy.almeyda');

-- Consultar la variable de sesión registrada
SELECT valor 
FROM variables_sesion 
WHERE clave = 'usuario_app';
-- Resultado esperado: 'percy.almeyda'

2. Enlazando la sesión con Triggers

Una vez definida la variable temporal de sesión, cualquier trigger en la base de datos puede capturarla dinámicamente mediante una subconsulta estructurada. Esto nos permite resolver auditorías avanzadas sin modificar las firmas de las tablas físicas:

-- Trigger clínico que captura el usuario de aplicación simulado
CREATE TRIGGER auditar_cita_registro
AFTER INSERT ON citas
FOR EACH ROW
BEGIN
    INSERT INTO auditoria (
        tabla_afectada, operacion, usuario_app, usuario_bd, 
        registro_id, fecha_hora, datos_anteriores, datos_nuevos
    )
    VALUES (
        'citas',
        'INSERT',
        -- Captura la variable de sesión temporal. Si no existe, se guarda como 'DESCONOCIDO'
        IFNULL((SELECT valor FROM variables_sesion WHERE clave = 'usuario_app'), 'DESCONOCIDO'),
        'sqlite_internal', -- SQLite no tiene servidor, el usuario de base de datos es el proceso
        NEW.id,
        datetime('now', 'localtime'),
        NULL,
        'Paciente ID: ' || NEW.paciente_id || ' | Medico ID: ' || NEW.medico_id
    );
END;

3. Implementación automatizada desde PHP

En el backend de la clínica, la creación de la tabla temporal y la asignación del usuario de la sesión web deben ocurrir inmediatamente después de establecer la conexión con la base de datos:

<?php
// Inicializar conexión
$pdo = new PDO('sqlite:clinica.db');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('PRAGMA foreign_keys = ON;');

// Supongamos que recuperamos el usuario autenticado de la sesión de PHP
session_start();
$_SESSION['usuario_sistema'] = 'percy.almeyda'; // Usuario logueado en la app

// Paso obligatorio: Crear la estructura temporal e inyectar el usuario
$pdo->exec("
    CREATE TEMP TABLE IF NOT EXISTS variables_sesion (
        clave TEXT PRIMARY KEY,
        valor TEXT
    ) STRICT;
");

$stmtSesion = $pdo->prepare("INSERT OR REPLACE INTO variables_sesion (clave, valor) VALUES ('usuario_app', :usuario)");
$stmtSesion->execute([':usuario' => $_SESSION['usuario_sistema']]);

// A partir de este momento, cualquier operación de modificación de datos 
// activará los triggers de auditoría registrando el usuario correcto
$stmtCita = $pdo->prepare("
    INSERT INTO citas (paciente_id, medico_id, fecha_hora, estado, motivo)
    VALUES (1, 2, '2026-07-20 10:00:00', 'PENDIENTE', 'Consulta preventiva')
");
$stmtCita->execute();

echo "Cita registrada y auditada bajo el usuario: " . $_SESSION['usuario_sistema'];
?>

Los disparadores o Triggers son bloques de código SQL estructurado que el motor de base de datos ejecuta de forma automática cuando ocurren eventos de modificación de datos: INSERT, UPDATE o DELETE. SQLite implementa un soporte muy sólido para triggers (incluyendo eventos BEFORE, AFTER e INSTEAD OF), permitiéndonos automatizar la integridad del modelo clínico y centralizar las auditorías directamente en el archivo .db.

• Diseñar e implementar triggers de auditoría con la cláusula condicional `WHEN`.
• Utilizar `SELECT RAISE(ABORT, ...)` para disparar excepciones y abortar modificaciones inválidas.
• Implementar triggers del tipo `INSTEAD OF` sobre vistas para simular inserciones en tablas virtuales.
• Conocer las limitaciones de SQLite respecto a la lógica procedural dentro del cuerpo de los disparadores.

1. Auditoría automática mediante Triggers y WHEN

Dado que SQLite no tiene bloques IF/ELSE dentro de los triggers, para ejecutar código condicional (por ejemplo, auditar únicamente si cambia el estado o el correo electrónico del médico) se utiliza la cláusula WHEN inmediatamente antes del cuerpo BEGIN:

-- Trigger para auditar actualizaciones en la tabla medicos
CREATE TRIGGER auditar_medicos_update
AFTER UPDATE ON medicos
-- Se ejecuta solo si el email o el estado activo realmente cambiaron
WHEN OLD.email != NEW.email OR OLD.activo != NEW.activo
BEGIN
    INSERT INTO auditoria (
        tabla_afectada, operacion, usuario_app, usuario_bd, 
        registro_id, fecha_hora, datos_anteriores, datos_nuevos
    )
    VALUES (
        'medicos',
        'UPDATE',
        -- Leemos el usuario de la variable de sesión simulada en el Capítulo 27
        IFNULL((SELECT valor FROM variables_sesion WHERE clave = 'usuario_app'), 'SISTEMA'),
        'sqlite_internal',
        NEW.id,
        datetime('now', 'localtime'),
        'Email: ' || OLD.email || ' | Activo: ' || OLD.activo,
        'Email: ' || NEW.email || ' | Activo: ' || NEW.activo
    );
END;

2. Restricciones personalizadas con RAISE(ABORT, 'mensaje')

Si necesitamos realizar validaciones de negocio complejas que superen las capacidades de un constraint `CHECK` (por ejemplo, evitar que se registren citas en el pasado), usamos un trigger BEFORE INSERT que aborte la transacción mediante la función especial RAISE:

-- Trigger para evitar registrar citas en el pasado
CREATE TRIGGER validar_fecha_cita_insert
BEFORE INSERT ON citas
FOR EACH ROW
BEGIN
    -- Validamos si la fecha_hora de la cita es anterior al tiempo actual
    SELECT 
        CASE 
            WHEN NEW.fecha_hora < datetime('now', 'localtime') THEN 
                RAISE(ABORT, 'Error clínico: No es posible programar una cita médica en una fecha u hora pasada.')
        END;
END;

-- Prueba de fallo: Intentamos insertar una cita en el año 2020
INSERT INTO citas (paciente_id, medico_id, fecha_hora, estado, motivo)
VALUES (1, 2, '2020-01-01 10:00:00', 'PENDIENTE', 'Consulta general');
-- Salida esperada: Runtime error: Error clínico: No es posible programar una cita médica en una fecha u hora pasada.

3. Vistas modificables con Triggers INSTEAD OF

Como aprendimos en el Capítulo 24, las vistas en SQLite son de solo lectura. Sin embargo, si asociamos un trigger del tipo INSTEAD OF a una vista, podemos capturar el intento de escritura y desviar los datos hacia las tablas físicas subyacentes:

-- Supongamos una vista simplificada de pacientes
CREATE VIEW vista_pacientes_rapida AS
SELECT id, documento, nombre, apellido, activo FROM pacientes;

-- Definir un trigger INSTEAD OF para procesar inserciones en la vista
CREATE TRIGGER insertar_paciente_desde_vista
INSTEAD OF INSERT ON vista_pacientes_rapida
FOR EACH ROW
BEGIN
    INSERT INTO pacientes (documento, nombre, apellido, fecha_nacimiento, sexo, activo)
    VALUES (NEW.documento, NEW.nombre, NEW.apellido, '2000-01-01', 'O', NEW.activo);
END;

-- Insertar datos a través de la vista
INSERT INTO vista_pacientes_rapida (documento, nombre, apellido, activo)
VALUES ('99999999', 'Carlos', 'Vega', 1);

-- Verificar que los datos se almacenaron en la tabla física real
SELECT id, documento, nombre, apellido, fecha_nacimiento FROM pacientes WHERE documento = '99999999';

En motores cliente-servidor clásicos como PostgreSQL o SQL Server, la seguridad se gestiona internamente creando usuarios de base de datos, asignándoles contraseñas y limitando sus privilegios con sentencias como GRANT y REVOKE. En SQLite, no existe el concepto de usuarios, roles o contraseñas de motor. La base de datos es un único archivo físico en disco y la seguridad depende enteramente de los permisos del sistema operativo y la configuración de la aplicación backend.

• Comprender la ausencia de usuarios y roles internos en la arquitectura de SQLite.
• Configurar permisos correctos en el sistema operativo Linux para proteger el archivo `.db`.
• Asegurar el directorio contenedor para permitir operaciones de journaling (archivos WAL y SHM).
• Analizar alternativas de cifrado de base de datos a nivel de archivo mediante extensiones como SQLCipher.

1. La seguridad en el sistema operativo (Modelo Linux)

Dado que SQLite no tiene un puerto ni una interfaz de red, el acceso al archivo físico de la base de datos se controla mediante los permisos del sistema de archivos. Para que una aplicación web (por ejemplo, PHP bajo Apache o Nginx) pueda leer y escribir en la base de datos clínica, el servidor web (usualmente el usuario www-data en Ubuntu) debe poseer permisos de lectura y escritura:

# Cambiar el propietario del archivo .db al usuario del servidor web
sudo chown www-data:www-data clinica.db

# Establecer permisos restrictivos: lectura/escritura para el dueño, lectura para el grupo
chmod 640 clinica.db

# Verificar los permisos del archivo
ls -l clinica.db
# Salida esperada: -rw-r----- 1 www-data www-data 201437 Aug 29 14:16 clinica.db

2. El riesgo del directorio contenedor

Una particularidad crítica de SQLite es que no basta con asignar permisos de escritura al archivo `.db`. Cuando SQLite realiza transacciones en modo WAL (Write-Ahead Logging), necesita crear y eliminar al vuelo archivos auxiliares de journal en el mismo directorio (como clinica.db-wal y clinica.db-shm). Si el servidor web no tiene permisos de **escritura sobre la carpeta contenedora**, SQLite arrojará errores de bloqueo e impedirá modificar datos:

# Ruta recomendada en producción: fuera del directorio web raiz para evitar descargas
# /var/www/data/clinica.db

# Cambiar el propietario de la carpeta de datos
sudo chown -R www-data:www-data /var/www/data/

# Permisos de la carpeta: el servidor web debe poder escribir en ella
chmod 750 /var/www/data/

3. Cifrado a nivel de archivo: SQLCipher

Dado que cualquiera con acceso físico al archivo `clinica.db` puede copiarlo y ver la información de los pacientes con un simple editor de SQLite, los entornos que requieren cumplimiento regulatorio (como HIPAA para salud) implementan **SQLCipher**. Esta extensión añade cifrado transparente de 256 bits (AES) sobre el archivo SQLite sin alterar la sintaxis SQL:

-- Sintaxis típica de conexión en SQLCipher (ejecutado al iniciar conexión)
PRAGMA key = 'clave_secreta_maestra_clinica';

-- Toda la base de datos clínica se lee y escribe cifrada en disco.
-- Si un atacante copia el archivo .db, solo verá datos binarios corruptos.

PHP Data Objects (PDO) es la extensión estándar y recomendada para conectarse a bases de datos en PHP de forma segura y orientada a objetos. Dado que SQLite es una base de datos sin servidor físico, el proceso de conexión se reduce a apuntar a la ruta del archivo .db en el disco. Hoy aprenderemos a establecer una conexión robusta en PHP, configurar el reporte de excepciones y forzar la activación de la integridad referencial.

• Construir el DSN (Data Source Name) para archivos SQLite en almacenamiento físico y en memoria.
• Implementar un archivo de configuración de conexión reutilizable con manejo de excepciones `PDOException`.
• Forzar la directiva de integridad referencial obligatoria `PRAGMA foreign_keys = ON;`.
• Configurar los atributos de PDO para reportar de forma estricta los errores de sintaxis o bloqueo.

1. Estructura de conexión robusta (conexion.php)

A diferencia de motores como MySQL, donde especificamos host, puerto, usuario y contraseña, la cadena de conexión de SQLite solo requiere el prefijo del driver y la ruta del archivo. Es una buena práctica encapsular esto en un bloque `try-catch` para capturar errores de lectura o permisos de archivo:

<?php
/**
 * conexion.php - Retorna la instancia de conexión PDO a SQLite
 */
function obtener_conexion() {
    // Ruta al archivo de la base de datos clínica
    $ruta_db = __DIR__ . '/clinica.db';
    $dsn = "sqlite:" . $ruta_db;

    try {
        // Inicializar la conexión PDO
        $pdo = new PDO($dsn);

        // 1. Configurar reporte estricto de errores como excepciones
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // 2. Establecer el modo de recuperación por defecto a array asociativo
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

        // 3. Habilitar la validación obligatoria de llaves foráneas
        $pdo->exec("PRAGMA foreign_keys = ON;");

        // 4. (Opcional) Aumentar el tiempo de espera por bloqueo de base de datos a 5 segundos (5000 ms)
        $pdo->exec("PRAGMA busy_timeout = 5000;");

        return $pdo;

    } catch (PDOException $e) {
        // Loguear el error en producción y lanzar un mensaje genérico para seguridad
        error_log("Error de conexión SQLite: " . $e->getMessage());
        die("Error crítico de base de datos. Por favor, intente más tarde.");
    }
}

// Ejemplo de uso:
$db = obtener_conexion();
echo "Conexión establecida y configurada con éxito.";
?>

2. Conexiones ultrarrápidas en Memoria

SQLite ofrece un modo especial que almacena la base de datos completamente en la memoria RAM del servidor. Este modo es ideal para ejecutar pruebas unitarias y simulaciones de desarrollo sumamente rápidas, ya que los datos se destruyen inmediatamente al finalizar la ejecución del script:

<?php
// Conexión a base de datos volátil en memoria RAM
try {
    $pdoMemoria = new PDO('sqlite::memory:');
    $pdoMemoria->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Crear tablas de prueba de velocidad
    $pdoMemoria->exec("CREATE TABLE pruebas (id INTEGER PRIMARY KEY, valor TEXT) STRICT;");
    $pdoMemoria->exec("INSERT INTO pruebas (valor) VALUES ('Prueba en RAM');");
    
    $res = $pdoMemoria->query("SELECT * FROM pruebas")->fetch();
    echo "Dato recuperado de la memoria RAM: " . $res['valor'];
    
} catch (PDOException $e) {
    echo "Fallo en memoria: " . $e->getMessage();
}
?>

La inyección SQL (SQL Injection) es una de las vulnerabilidades más comunes y destructivas en el desarrollo web. Ocurre cuando concatenamos directamente cadenas de texto ingresadas por el usuario dentro de nuestras consultas SQL. Hoy estudiaremos cómo prevenir este riesgo de raíz utilizando Sentencias Preparadas (Prepared Statements) en PHP con PDO para proteger los datos médicos de nuestra clínica.

• Identificar y analizar el riesgo de la inyección SQL por concatenación de variables en PHP.
• Implementar consultas preparadas utilizando marcadores con nombre y de posición en PDO.
• Comprender la diferencia operativa entre los métodos `bindValue` y `bindParam`.
• Asegurar la sanitización automática de datos clínicos en operaciones de búsqueda e inserción.

1. La vulnerabilidad: Concatenación directa de variables

Imaginemos un formulario de búsqueda de pacientes por número de documento. Si el script PHP toma el valor directamente de la URL y lo concatena a la query, un atacante podría ingresar código malicioso para eludir el filtrado:

<?php
// CÓDIGO VULNERABLE - NUNCA HACER ESTO EN PRODUCCIÓN
$documento = $_GET['doc']; // Entrada del usuario: "12345678' OR '1'='1"

// La query final se compila como: SELECT * FROM pacientes WHERE documento = '12345678' OR '1'='1'
// Esto devolverá todos los pacientes de la base de datos, violando la privacidad médica.
$sql = "SELECT * FROM pacientes WHERE documento = '" . $documento . "'";
$resultado = $pdo->query($sql)->fetchAll();
?>

2. La solución: Sentencias preparadas con marcadores con nombre

Las sentencias preparadas evitan este ataque al separar completamente la estructura del comando SQL de los datos clínicos. El motor de SQLite compila la estructura de la consulta primero y luego le inyecta las variables en una fase de binding separada. Ninguna entrada del usuario podrá alterar la semántica de la consulta:

<?php
// CÓDIGO SEGURO CONTRA INYECCIÓN SQL
$documento_seguro = $_GET['doc']; // Entrada del usuario

// 1. Preparar la estructura SQL en el motor de base de datos con un marcador ":doc"
$stmt = $pdo->prepare("SELECT * FROM pacientes WHERE documento = :doc AND activo = 1");

// 2. Asociar el valor del usuario de forma segura y ejecutar
$stmt->execute([
    ':doc' => $documento_seguro
]);

// 3. Recuperar los datos del paciente
$paciente = $stmt->fetch();
if ($paciente) {
    echo "Paciente encontrado: " . $paciente['nombre'] . " " . $paciente['apellido'];
} else {
    echo "No se encontró ningún paciente activo con ese documento.";
}
?>

3. Comparativa de Binding: bindValue vs bindParam

PDO provee dos métodos específicos para enlazar valores. Es importante comprender sus diferencias para evitar comportamientos inesperados en ejecuciones secuenciales:

<?php
// 1. bindValue(): Enlaza el valor inmediato de la variable al momento de la llamada
$valor = 'Odontología';
$stmt = $pdo->prepare("SELECT * FROM especialidades WHERE nombre = :nombre");
$stmt->bindValue(':nombre', $valor); // Se enlaza el string 'Odontología'
$valor = 'Pediatría'; // Modificar la variable no altera el enlace previo
$stmt->execute(); // Ejecuta buscando 'Odontología'

// 2. bindParam(): Enlaza la variable por referencia. Su valor se lee al ejecutar
$edad_limite = 30;
$stmt = $pdo->prepare("SELECT * FROM pacientes WHERE fecha_nacimiento > :fecha");
$stmt->bindParam(':fecha', $fecha_calculada); // Enlazado por referencia a $fecha_calculada

// Modificamos el valor de la variable de forma dinámica antes de la ejecución
$fecha_calculada = '1996-08-29';
$stmt->execute(); // Se ejecuta usando '1996-08-29'
?>

En este capítulo integraremos lo aprendido en las últimas dos secciones de la serie. Construiremos un flujo completo de autenticación de usuarios clínicos en PHP que, tras una verificación exitosa de contraseñas, establezca la sesión del usuario. Luego, conectaremos este contexto de sesión con el motor de SQLite mediante una tabla temporal, logrando que los triggers de auditoría registren automáticamente quién realiza cada modificación.

• Desarrollar un flujo de login seguro validando credenciales mediante Prepared Statements y `password_verify`.
• Inicializar automáticamente el contexto de sesión mediante tablas temporales en la conexión activa de PDO.
• Realizar modificaciones en el modelo clínico y verificar el disparo de los triggers de auditoría.
• Interpretar los registros generados en la tabla `auditoria` para confirmar la trazabilidad del usuario de la app.

1. Helper de conexión PDO con auditoría inyectada

Para evitar repetir la lógica de configuración en cada script clínico de nuestro sistema backend, definimos una función helper que inicialice la base de datos, configure excepciones, habilite llaves foráneas y declare el usuario de la sesión:

<?php
/**
 * Conecta a la base de datos e inyecta la variable de sesión para auditoría
 */
function obtener_conexion_auditada($usuario_app) {
    try {
        $pdo = new PDO('sqlite:' . __DIR__ . '/clinica.db');
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        // Forzar llaves foráneas
        $pdo->exec("PRAGMA foreign_keys = ON;");

        // Crear la tabla temporal de sesión si no existe
        $pdo->exec("
            CREATE TEMP TABLE IF NOT EXISTS variables_sesion (
                clave TEXT PRIMARY KEY,
                valor TEXT
            ) STRICT;
        ");

        // Inyectar el usuario logueado en la tabla temporal
        $stmt = $pdo->prepare("INSERT OR REPLACE INTO variables_sesion (clave, valor) VALUES ('usuario_app', :usuario)");
        $stmt->execute([':usuario' => $usuario_app]);

        return $pdo;
    } catch (PDOException $e) {
        die("Error de inicialización de base de datos: " . $e->getMessage());
    }
}
?>

2. El script de Login, actualización y verificación

A continuación, desarrollamos el script completo que simula la autenticación de una recepcionista, conecta la base de datos de auditoría, actualiza la dirección de un paciente de la clínica y verifica el log de auditoría resultante:

<?php
// Requerir el helper de conexión
// require_once 'conexion_auditada.php';

// Variables enviadas por el formulario clínico de autenticación
$usuario_enviado = 'recepcion01';
$clave_enviada = 'recep456'; // Contraseña en texto plano

try {
    // Establecer conexión inicial para validar la autenticación
    $pdo_auth = new PDO('sqlite:' . __DIR__ . '/clinica.db');
    $pdo_auth->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 1. Consultar el hash de clave del usuario utilizando consultas preparadas
    $stmt = $pdo_auth->prepare("SELECT usuario, hash_clave, rol, activo FROM usuarios_app WHERE usuario = :user");
    $stmt->execute([':user' => $usuario_enviado]);
    $usuario = $stmt->fetch(PDO::FETCH_ASSOC);

    // 2. Verificar la contraseña contra el hash bcrypt guardado en el sembrado clínico
    if ($usuario && password_verify($clave_enviada, $usuario['hash_clave']) && $usuario['activo'] == 1) {
        echo "Login exitoso. Usuario activo: " . $usuario['usuario'] . " | Rol: " . $usuario['rol'] . "\n\n";

        // 3. Obtener la conexión configurada con el usuario activo para auditoría
        $pdo = obtener_conexion_auditada($usuario['usuario']);

        // 4. Modificar información del paciente (ID 1)
        echo "Operación: Modificando la dirección del paciente ID 1...\n";
        $stmtUpdate = $pdo->prepare("UPDATE pacientes SET direccion = :dir WHERE id = 1");
        $stmtUpdate->execute([':dir' => 'Av. San Martín 456, Ica']);

        // 5. Consultar la tabla de auditoría para verificar el trigger
        $stmtAudit = $pdo->query("
            SELECT id, tabla_afectada, operacion, usuario_app, fecha_hora, datos_anteriores, datos_nuevos 
            FROM auditoria 
            ORDER BY id DESC LIMIT 1
        ");
        $log = $stmtAudit->fetch(PDO::FETCH_ASSOC);

        // Imprimir los resultados del log de auditoría
        echo "--- LOG DE AUDITORÍA DETECTADO EN EL MOTOR ---\n";
        echo "ID Registro: " . $log['id'] . "\n";
        echo "Tabla Modificada: " . $log['tabla_afectada'] . "\n";
        echo "Operación: " . $log['operacion'] . "\n";
        echo "Usuario del Sistema: " . $log['usuario_app'] . "\n";
        echo "Fecha y Hora: " . $log['fecha_hora'] . "\n";
        echo "Datos Nuevos en disco: " . $log['datos_nuevos'] . "\n";

    } else {
        echo "Error de autenticación: Usuario o clave incorrectos.\n";
    }

} catch (PDOException $e) {
    echo "Fallo de conexión o base de datos: " . $e->getMessage() . "\n";
}
?>

3. Salida típica del script en el terminal

Al ejecutar el script anterior en la línea de comandos, la salida demuestra que el trigger capturó con precisión el usuario logueado en la aplicación web sin necesidad de mandarlo como parámetro de inserción en la tabla de pacientes:

Login exitoso. Usuario activo: recepcion01 | Rol: RECEPCION

Operación: Modificando la dirección del paciente ID 1...
--- LOG DE AUDITORÍA DETECTADO EN EL MOTOR ---
ID Registro: 1
Tabla Modificada: pacientes
Operación: UPDATE
Usuario del Sistema: recepcion01
Fecha y Hora: 2026-08-29 14:16:53
Datos Nuevos en disco: Direccion: Av. San Martín 456, Ica

La base clínica de seis pacientes responde al instante, pero la producción real mide las consultas por milisegundos sobre decenas de miles de registros. Hoy veremos cómo SQLite decide ejecutar una consulta y cómo un índice — una copia ordenada de una columna con punteros a las filas — convierte un recorrido total de la tabla (SCAN) en una búsqueda directa (SEARCH). La herramienta estrella es EXPLAIN QUERY PLAN, el plan de ejecución del motor.

• Entender cómo funciona un índice B-tree y cuándo SQLite decide usarlo.
• Crear índices simples, únicos y compuestos con CREATE INDEX.
• Leer EXPLAIN QUERY PLAN: SCAN vs SEARCH vs USE TEMP B-TREE.
• Diseñar el índice correcto para la clínica sin castigar las escrituras.

1. El costo de recorrer toda la tabla

Sin índice, una consulta por apellido obliga a SQLite a revisar fila por fila hasta encontrar las coincidencias: la tabla pacientes se recorre completa en cada llamada, con un costo lineal O(n). Con un índice B-tree sobre la columna, el motor baja a O(log n): desciende por el árbol ordenado y solo toca las páginas necesarias. Empecemos por inspeccionar la situación real del modelo con el plan de ejecución:

sqlite> EXPLAIN QUERY PLAN
   ...> SELECT id, apellido, documento
   ...> FROM pacientes
   ...> WHERE apellido = 'Quispe';
QUERY PLAN
`--SCAN pacientes

SCAN pacientes es el diagnóstico honesto del motor: recorrido total, sin atajos. Para una clínica pequeña es irrelevante; para millones de pacientes, cada consulta de este tipo arrastra el árbol entero.

2. Crear y listar índices

La sintaxis es estándar: CREATE INDEX para índices que admiten duplicados, CREATE UNIQUE INDEX para forzar valores únicos mediante el índice, y IF NOT EXISTS para repetir sin error. Juntos con las llaves foráneas, creamos los índices que reportes y buscadores clínicos pedirán a gritos:

-- Búsquedas frecuentes por apellido medico y de paciente
CREATE INDEX IF NOT EXISTS idx_pacientes_apellido ON pacientes(apellido);
CREATE INDEX IF NOT EXISTS idx_medicos_apellido  ON medicos(apellido);

-- Reporte "citas de hoy" y cruces por paciente
CREATE INDEX IF NOT EXISTS idx_citas_fecha      ON citas(fecha_hora);
CREATE INDEX IF NOT EXISTS idx_citas_paciente   ON citas(paciente_id);

-- El JOIN pagos -> citas (cobranza) merece su propio índice
CREATE INDEX IF NOT EXISTS idx_pagos_cita       ON pagos(cita_id);

-- Cómo verificar lo que existe sobre una tabla
PRAGMA index_list('citas');

seq  name                    unique  origin  partial
---  ----------------------  ------  ------  -------
0    sqlite_autoindex_citas_1 1        u       0
1    idx_citas_fecha          0        c       0
2    idx_citas_paciente       0        c       0

La primera fila es un dato que casi nadie conoce: la restricción UNIQUE(medico_id, fecha_hora) declarada en el capítulo de constraints ya creó un índice automático — sqlite_autoindex_citas_1. SQLite reutiliza esos índices en cuanto puede, por eso los índices que agregamos deben complementar, no duplicar. Para ver qué columnas usa uno de nuestros índices:

PRAGMA index_info('idx_citas_fecha');

-- Resultado:
-- seqno  cid  name
-- 0      3    fecha_hora

3. El antes y el después en el plan de ejecución

Ahora la prueba de fuego: repetimos la misma consulta que abrió SCAN y el motor debe cambiar la estrategia por sí solo, sin tocar el SQL:

sqlite> EXPLAIN QUERY PLAN
   ...> SELECT id, apellido, documento
   ...> FROM pacientes
   ...> WHERE apellido = 'Quispe';
QUERY PLAN
`--SEARCH pacientes USING INDEX idx_pacientes_apellido (apellido=?)

SEARCH ... USING INDEX significa que el plan bajó de recorrido total a búsqueda por índice. Lo mismo ocurre con el agregado de citas por médico, que además aprovecha el índice único que ya existía por diseño:

-- Consulta de la agenda de un médico (periodo de julio 2026)
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT fecha_hora, estado FROM citas
   ...> WHERE medico_id = 2
   ...>   AND fecha_hora BETWEEN '2026-07-01 00:00:00' AND '2026-07-31 23:59:59';
QUERY PLAN
`--SEARCH citas USING INDEX sqlite_autoindex_citas_1 (medico_id=?)

4. Ordenamientos: USE TEMP B-TREE

Cuando una consulta pide ORDER BY o agrupa con GROUP BY y no existe un índice que ya entregue las filas en orden, SQLite construye sobre la marcha una estructura temporal de ordenamiento. El plan lo denuncia sin rodeos:

sqlite> EXPLAIN QUERY PLAN
   ...> SELECT documento, apellido FROM pacientes ORDER BY apellido;
QUERY PLAN
|--SCAN pacientes
`--USE TEMP B-TREE FOR ORDER BY

-- Tras crear idx_pacientes_apellido (paso 2), el mismo ORDER BY:
QUERY PLAN
`--SCAN pacientes USING INDEX idx_pacientes_apellido

El índice que creamos para el filtro WHERE apellido = ? también sirve para el ORDER BY apellido: dos problemas técnicos, una sola estructura.

En SQLite no existe un mysqldump del servidor porque no hay servidor: la base de datos ES un archivo. El reto de producción se concentra en tres frentes: copiar el archivo sin corromperlo mientras hay escritores activos, moverlo a otro equipo y poder probar la restauración. Aprenderemos las herramientas del CLI que lo resuelven: .backup, .dump, .restore y los diagnósticos .timer, .stats y PRAGMA integrity_check.

• Diferenciar la copia segura en caliente (.backup) del copiado crudo con cp.
• Generar y restaurar un volcado lógico en texto con .dump.
• Verificar la integridad después de cada restauración.
• Probar restauraciones y diseñar la estrategia de respaldos de la clínica.

1. Copia segura en caliente: .backup

El error clásico es copiar el archivo clinica.db con cp mientras la aplicación escribe. Con el modo WAL (capítulo 21), la base consta de tres archivos en plena actividad — clinica.db, clinica.db-wal y clinica.db-shm — y la copia cruda queda a medias: catastrófico si se restaura. El comando .backup usa internamente la Online Backup API del motor: copia al vuelo, con lectura consistente, sin cerrar la clínica ni bloquear a los usuarios por más de unos instantes:

# Copia de seguridad EN CALIENTE (sitio operando, WAL activo)
sqlite3 clinica.db ".backup '/var/backups/clinica_$(date +%F).db'"

# Equivalente dentro del propio CLI: .save es un alias de .backup
sqlite3 clinica.db
sqlite> .save /var/backups/clinica_manual.db
sqlite> .quit

La copia resultante es un archivo SQLite sano y autocontenido que incluye todos los datos del WAL ya consolidados. Fuera de conexión, una simple copia con cp es válida; el problema es solo mientras hay escritores.

2. El volcado lógico: .dump

El comando .dump convierte la base completa en un único archivo de texto UTF-8 con las sentencias SQL que la reconstruyen. Su superpoder: el formato es puro SQL, portátil a otros motores y sin preocupación por el WAL:

# Volcado lógico + compresión (la práctica recomendada por el manual oficial)
sqlite3 clinica.db .dump | gzip -c > /var/backups/clinica_$(date +%F).dump.gz

# Restaurar ese volcado en una base limpia
zcat /var/backups/clinica_2026-08-29.dump.gz | sqlite3 clinica_restaurada.db

# Volcar solo una tabla (útil para móvernos con un dominio puntual)
sqlite3 clinica.db ".dump auditoria" > auditoria_problema.sql

El dump inicia con PRAGMA foreign_keys=OFF; y envuelve todo en una transacción, algo a tener presente si acostumbras a abrir los volcados como documentos de texto: son código, no lectura.

3. Restauración y verificación obligatoria

Restaurar con .restore copia el contenido de un respaldo al archivo destino; con .dump se restaura canalizando texto. En ambos casos el paso final es innegociable: comprobar que la restauración quedó íntegra.

# Ruta 1: recuperar un respaldo binario
sqlite3 clinica.db ".restore '/var/backups/clinica_2026-08-29.db'"

# Ruta 2: recuperar desde el volcado lógico
zcat /var/backups/clinica_2026-08-29.dump.gz | sqlite3 clinica.db

# Verificación (un backup sin prueba no es un backup)
sqlite3 clinica.db "PRAGMA integrity_check;"
printf 'integridad: ok (o el detalle del problema)\n'

sqlite3 clinica.db "
SELECT 'medicos', COUNT(*) FROM medicos
UNION ALL SELECT 'citas',   COUNT(*) FROM citas
UNION ALL SELECT 'pagos',   COUNT(*) FROM pagos;"

PRAGMA integrity_check;  -- respuesta esperada:
ok

medicos|8
citas|6
pagos|3

PRAGMA integrity_check recorre las estructuras B-tree buscando corrupción lógica; existe también PRAGMA quick_check, más rápido y suficiente para la mayoría de verificaciones rutinarias. Y si el archivo llega dañado desde un disco fallido, el CLI ofrece .recover: rescata tantas páginas como pueda en un volcado de emergencia, anteponiendo una salvación parcial a la pérdida total.

4. Operación y diagnóstico en producción

Sin SHOW PROCESSLIST ni monitor del servidor, el propio CLI es el cuarto de control: tiempos reales con .timer, memoria con .stats y una vista de la base abierta con .databases. Cuando el archivo crece y queda fragmentado tras muchas bajas, VACUUM lo compacta reescribiéndolo desde cero; VACUUM INTO entrega de paso una copia compactada:

sqlite3 clinica.db
sqlite> .timer on         -- cronometra cada sentencia
sqlite> .stats on         -- memoria y paginación del motor
sqlite> .databases        -- base(s) conectada(s)

sqlite> SELECT COUNT(*) FROM citas;
Run Time: real 0.002 user 0.001000 sys 0.000000

sqlite> VACUUM;                       -- compacta el archivo en sitio
sqlite> VACUUM INTO 'clinica_optima.db'; -- copia compacta para mover

Este es el capítulo de la chuleta. Cierras la serie de bases de datos de webcode y te llevas una herramienta que los otros tres manuales comparten: la tabla maestra de equivalencias de tipos entre los cuatro motores. La columna resaltada de hoy es la tuya, SQLite. Al final, el examen de graduación y el mapa de los 35 capítulos caminados.

• Leer la tabla de equivalencias de tipos con la columna SQLite resaltada.
• Contrastar las capacidades que sí y que no ofrece cada motor de la serie.
• Resolver el examen de graduación con entregables verificables.
• Cerrar la serie con la categoría bases de datos completa.

1. Anexo: equivalencias de tipos (columna SQLite)

Verificada contra la documentación oficial de cada motor. Recuerda la regla del motor embebido: SQLite no tiene tipos estrictos, sino afinidad — con las tablas STRICT (3.37+) ese comportamiento dinámico se disciplina.

La sintaxis asociada que ya manejas como dialecto SQLite:

Guarda este anexo: es la chuleta oficial de la categoría. Cuando abras cualquier otro manual de la serie, la misma tabla volverá con su columna resaltada.

2. Qué te llevas de cada parte del manual

3. Examen de graduación

No hay certificado sin entregables. Sustenta cada punto sobre tu propia clinica.db:

1. Recrea las 8 tablas del modelo con STRICT y llaves foráneas habilitadas.
2. Sembra los datos deterministas de julio 2026 dentro de una sola transacción.
3. Genera con JOINs: agenda del día, pacientes deudores y productividad por médico.
4. Crea una vista analítica y consúltala como si fuera una tabla.
5. Explica el trigger de auditoría y de qué conexión depende para saber quién modifica.
6. Construye el login PHP con PDO + password_verify y verifica la auditoría.
7. Pasa una consulta de SCAN a SEARCH con un índice y muestra ambos planes.
8. Genera un .dump, restaura en otra base y ejecuta PRAGMA integrity_check.
9. Justifica el tipo SQLite elegido para cada columna usando este anexo.