Conexión a Bases de Datos con `DBI`

Una aplicación Shiny que lee un archivo .csv o .txt es un prototipo. Para que una aplicación sea considerada de nivel profesional o empresarial, debe interactuar con una fuente de datos centralizada, robusta y eficiente. Aquí es donde entran las bases de datos.

Comparativa: Archivo Plano vs. Base de Datos

El paquete DBI (Database Interface) es el estándar de oro en R. No se conecta directamente a la base de datos, sino que actúa como una **interfaz universal**. Proporciona un conjunto consistente de funciones (dbConnect, dbGetQuery, etc.) que funcionan igual sin importar la base de datos subyacente.

Para cada tipo de base de datos, se utiliza un paquete "backend" o "driver" que traduce las llamadas de `DBI` al lenguaje específico del motor.

• Para SQLite (nuestro caso): RSQLite
• Para PostgreSQL: RPostgres
• Para SQL Server, Oracle, etc.: odbc

Este enfoque modular es poderoso: si mañana migras tu base de datos de SQLite a PostgreSQL, solo necesitas cambiar la línea de conexión; el resto de tu código de consulta permanece idéntico.

Nuestra base de datos icfes_data.db utiliza un esquema en estrella. A continuación se muestra el diagrama y el código SQL para crear cada tabla.

Definición de Tablas (SQL DDL)

-- Tabla de Dimensión: Colegios
CREATE TABLE Colegios (
    cole_dane_sede TEXT PRIMARY KEY,
    cole_nombre_sede TEXT,
    cole_naturaleza TEXT,
    cole_municipio TEXT,
    cole_departamento TEXT
);

-- Tabla de Dimensión: Examenes
CREATE TABLE Examenes (
    examen_id INTEGER PRIMARY KEY,
    periodo TEXT NOT NULL
);

-- Tabla de Hechos: Resultados
CREATE TABLE Resultados (
    resultado_id INTEGER PRIMARY KEY AUTOINCREMENT,
    estudiante_id TEXT,
    cole_dane_sede TEXT,
    examen_id INTEGER,
    punt_global INTEGER,
    punt_matematicas INTEGER,
    -- ... otros puntajes
    FOREIGN KEY (cole_dane_sede) REFERENCES Colegios(cole_dane_sede),
    FOREIGN KEY (examen_id) REFERENCES Examenes(examen_id)
);

Toda interacción con una base de datos debe seguir un ciclo de tres pasos riguroso, especialmente dentro de una aplicación Shiny.

El Patrón Profesional: `tryCatch` con `finally`

Para garantizar que la conexión siempre se cierre, incluso si ocurre un error durante la consulta, envolvemos nuestro código en un bloque tryCatch. El código dentro de finally se ejecutará sin importar qué pase, previniendo conexiones "colgadas".

con <- NULL # Inicializar la conexión como NULL
tryCatch({
    # 1. CONECTAR
    con <- dbConnect(RSQLite::SQLite(), dbname = "database/icfes_data.db")
    
    # 2. CONSULTAR (de forma segura)
    depto_seleccionado <- "BOGOTA D.C." # Esto vendría de un input$ de Shiny
    query <- "SELECT * FROM Colegios WHERE cole_departamento = ?;"
    datos <- dbGetQuery(con, query, params = list(depto_seleccionado))
    
}, error = function(e) {
    # Manejar el error, por ejemplo, mostrar una notificación en Shiny
    showNotification(paste("Error en la base de datos:", e$message), type = "error")
    
}, finally = {
    # 3. DESCONECTAR (SIEMPRE se ejecuta)
    if (!is.null(con)) {
        dbDisconnect(con)
    }
})

# ¡NO HACER ESTO!
depto <- input$depto_select # Imagina que el usuario escribe: "BOGOTA D.C.'; DROP TABLE Resultados; --"
query <- paste0("SELECT * FROM Colegios WHERE cole_departamento = '", depto, "';")
dbGetQuery(con, query) # ¡Acabas de borrar tu tabla de resultados!

query <- "SELECT * FROM Colegios WHERE cole_departamento = ?;"
datos <- dbGetQuery(con, query, params = list(input$depto_select))

Nuestra aplicación de monitoreo es el ejemplo perfecto de estos conceptos en acción. Demuestra por qué el esfuerzo de crear una base de datos vale la pena.

La Prueba Definitiva: Rendimiento

Al presionar "Ejecutar Comparativa", el servidor demuestra que delegar el trabajo a la base de datos es órdenes de magnitud más rápido que procesar un archivo plano en R.

# Fragmento del server en app.R
observeEvent(input$run_benchmark, {
    # ...
    # --- Método Base de Datos (Rápido) ---
    start_time_db <- Sys.time()
    
    con <- dbConnect(RSQLite::SQLite(), db_path)
    # La consulta SQL hace todo el trabajo pesado de unir y filtrar
    query <- "
        SELECT AVG(r.punt_global) as avg_score
        FROM Resultados r
        JOIN Colegios c ON r.cole_dane_sede = c.cole_dane_sede
        WHERE c.cole_naturaleza = 'OFICIAL' AND c.cole_municipio = 'BOGOTA D.C.'
    "
    resultado_db <- dbGetQuery(con, query)
    dbDisconnect(con)
    
    end_time_db <- Sys.time()
    # ...
})

1. Conexión Global (Anti-Patrón)

Crear un objeto de conexión en `global.R` y reusarlo es una mala práctica. Las conexiones pueden expirar ("timeout") y no manejan bien múltiples usuarios simultáneos, creando un estado compartido problemático.

2. Conectar por Consulta (Nuestro Enfoque)

Abrir una conexión, ejecutar la consulta y cerrarla inmediatamente dentro de cada `reactive` o `observeEvent` es un patrón muy seguro y robusto. Garantiza que no queden conexiones "colgadas" y es fácil de depurar.

3. Connection Pooling (Estándar de la Industria con `pool`)

Para aplicaciones con alto tráfico, abrir y cerrar conexiones constantemente es ineficiente. El paquete pool es la solución profesional. Crea un "pool" de conexiones que están siempre abiertas y listas para ser usadas.

Implementación con `pool`:

# En global.R o al inicio de app.R
library(pool)
db_pool <- dbPool(
    drv = RSQLite::SQLite(),
    dbname = "database/icfes_data.db"
)

# En el server, simplemente usa el pool como si fuera una conexión
# pool se encarga de "prestarte" una conexión y devolverla automáticamente
server <- function(input, output, session) {
    
    datos_filtrados <- reactive({
        # pool maneja la conexión por ti
        dbGetQuery(db_pool, "SELECT * FROM Resultados WHERE punt_global > ?;", params = list(input$slider))
    })
    
    # Al cerrar la app, es crucial cerrar el pool
    onStop(function() {
        poolClose(db_pool)
    })
}