Saltar al contenido principal

stored-procedures

Documentación de Stored Procedures, Functions y Views

Toda la lógica de negocio del sistema vive aquí — el backend solo invoca estos objetos, nunca decide reglas por su cuenta.

SQL Server (ControlDB)​

sp_ProvisionDatabase​

Crea una base de datos SQL Server real para un estudiante (patrón original del reto), con tope de tamaño de 20MB impuesto por el motor.

CREATE OR ALTER PROCEDURE sp_ProvisionDatabase
    @OwnerUserId NVARCHAR(100),
    @RequestedName NVARCHAR(100)
AS
BEGIN
    -- Valida caracteres de OwnerUserId y RequestedName (previene inyección)
    -- Construye nombre único: stu_{OwnerUserId}_{RequestedName}
    -- CREATE DATABASE con MAXSIZE = 20MB (fuera de transacción, requisito de SQL Server)
    -- Si el INSERT posterior falla, hace DROP DATABASE de compensación
    -- Registra en dbo.ProvisionedDatabases
END

sp_GetDatabaseUsage​

Consulta el uso real de almacenamiento (sys.master_files) y conexiones activas (sys.dm_exec_sessions) de una base específica.

sp_ListDatabasesByOwner​

Lista todas las bases SQL Server que pertenecen a un usuario.

sp_DeactivateExpiredDatabases​

TTL — recorre bases activas con más de N días sin actividad, las pone OFFLINE y las marca inactivas. Ejecutado vía cron diario.

sp_TouchDatabaseActivity​

Refresca LastActivityAt de una base, para que el TTL no la desactive mientras está en uso real.

sp_RegisterUser​

Registro local (email/password). Recibe el hash ya calculado por el backend (BCrypt) — nunca contraseñas en texto plano. Valida que el email no exista; si existe, devuelve 0 filas (el backend traduce esto a un 409 Conflict).

sp_GetUserByEmail​

Solo lectura — devuelve el usuario y su hash para que el backend verifique la contraseña. El SP nunca valida contraseñas, solo las expone para la comparación en backend.

sp_UpsertOAuthUser​

Lógica central de "no duplicar usuarios": busca primero por Provider + ProviderUserId; si no encuentra, busca por Email (fusiona cuentas si el mismo humano usó Google y GitHub con el mismo correo); si tampoco encuentra, crea un usuario nuevo.

sp_RegisterMySqlProvisioning​

Registra en ControlDB la referencia de una base MySQL creada (nombre, usuario, host, puerto) — para que el dashboard consulte todo desde un solo lugar sin ir a preguntarle a MySQL directamente.

sp_LogAuditEvent​

Inserta un evento de auditoría genérico (UserId, EventType, Details, timestamp). Usado actualmente para registrar cada login (local, Google, GitHub).

fn_HasMySqlDatabase (Function)​

Devuelve 1/0 según si un usuario ya tiene una base MySQL registrada — usada para no aprovisionar dos veces en logins sucesivos.

CREATE OR ALTER FUNCTION dbo.fn_HasMySqlDatabase(@OwnerUserId NVARCHAR(36))
RETURNS BIT
AS
BEGIN
    DECLARE @Result BIT = 0;
    IF EXISTS (SELECT 1 FROM dbo.MySqlProvisionedDatabases WHERE OwnerUserId = @OwnerUserId)
        SET @Result = 1;
    RETURN @Result;
END

vw_PlatformMetrics (View)​

Agrega las métricas públicas de la landing: total de usuarios, bases creadas, bases activas, total de logins, usuarios activos últimos 7 días.

CREATE OR ALTER VIEW dbo.vw_PlatformMetrics AS
SELECT
    (SELECT COUNT(*) FROM dbo.Users) AS TotalUsers,
    (SELECT COUNT(*) FROM dbo.MySqlProvisionedDatabases) AS TotalDatabasesCreated,
    (SELECT COUNT(*) FROM dbo.MySqlProvisionedDatabases WHERE IsActive = 1) AS ActiveDatabases,
    (SELECT COUNT(*) FROM dbo.AuditLog WHERE EventType = 'login') AS TotalLogins,
    (SELECT COUNT(DISTINCT UserId) FROM dbo.AuditLog WHERE EventType = 'login' AND CreatedAt >= DATEADD(DAY, -7, SYSUTCDATETIME())) AS ActiveUsersLast7Days;

MySQL (mysql_control)​

sp_ProvisionMySQLDatabase​

El corazón del aprovisionamiento real de estudiantes. Recibe el OwnerUserId, nombre solicitado, y una contraseña ya generada por el backend. Dentro del motor:

  1. Construye un nombre único de base (stu_{owner}_{nombre}) y de usuario (hash corto).
  2. CREATE DATABASE con charset utf8mb4.
  3. CREATE USER ... IDENTIFIED BY ... accesible desde '%' (cualquier host), con MAX_USER_CONNECTIONS 5.
  4. GRANT ALL PRIVILEGES solo sobre esa base — verificado que el usuario no puede acceder a ninguna otra.
  5. Registra en ProvisionedMySQLDatabases.
  6. Devuelve host, puerto, usuario y nombre de la base (la contraseña la retiene el backend, no se re-consulta después).

sp_DeactivateExpiredMySQLDatabases​

TTL para MySQL. Revoca el acceso (REVOKE ALL PRIVILEGES) de usuarios inactivos por más de N días — no borra la base física, para que sea reversible. Ejecutado vía cron diario.

Usuarios de base de datos (principio de mínimo privilegio)​

UsuarioMotorPermisos
saSQL ServerAdministrador — solo usado internamente por el backend contra ControlDB
backend_provisionerMySQLAcotado: CREATE, DROP, CREATE USER, GRANT OPTION — no es root
u_xxxxxxxx (por estudiante)MySQLSolo sobre su propia base, MAX_USER_CONNECTIONS 5