Creación de Vistas en SQLite

Introducción

En este laboratorio, exploraremos la creación de vistas (views) en SQLite, centrándonos en la construcción de vistas complejas, la consulta a través de ellas, la actualización a través de vistas actualizables (updatable views) y la eliminación de vistas obsoletas.

Comenzaremos comprendiendo las vistas como tablas virtuales basadas en conjuntos de resultados de sentencias SQL, útiles para simplificar las consultas y controlar el acceso a los datos. El laboratorio lo guiará a través de la creación de vistas simples a partir de tablas individuales, como una tabla employees (empleados), y luego avanzará hacia vistas más complejas que involucran uniones (joins) entre tablas, como la unión de las tablas employees y departments (departamentos). Aprenderá a consultar estas vistas como si fueran tablas regulares y explorará las posibilidades de actualizar datos a través de vistas actualizables, así como la forma de eliminar correctamente las vistas cuando ya no sean necesarias.

Crear Tablas: empleados y departamentos

En este paso, creará dos tablas, employees (empleados) y departments (departamentos), e insertará algunos datos de ejemplo. Estas tablas se utilizarán para crear y consultar vistas (views) en los siguientes pasos.

Primero, abra la shell de SQLite ejecutando el siguiente comando en la terminal:

sqlite3 /home/labex/project/employees.db

Este comando abre la shell de SQLite y se conecta a la base de datos employees.db. Si el archivo de la base de datos no existe, SQLite lo creará.

Ahora, cree la tabla employees con la siguiente sentencia SQL:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    department TEXT,
    salary REAL
);

Esta sentencia SQL crea una tabla llamada employees con cinco columnas: id, first_name (nombre), last_name (apellido), department (departamento) y salary (salario). La columna id es la clave primaria (primary key) de la tabla.

A continuación, inserte algunos datos de ejemplo en la tabla employees:

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 50000.00),
('Jane', 'Smith', 'Marketing', 60000.00),
('Peter', 'Jones', 'Sales', 55000.00),
('Mary', 'Brown', 'IT', 70000.00);

Esta sentencia SQL inserta cuatro filas en la tabla employees.

Ahora, cree la tabla departments con la siguiente sentencia SQL:

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT,
    location TEXT
);

Esta sentencia SQL crea una tabla llamada departments con tres columnas: id, name (nombre) y location (ubicación). La columna id es la clave primaria de la tabla.

A continuación, inserte algunos datos de ejemplo en la tabla departments:

INSERT INTO departments (name, location) VALUES
('Sales', 'New York'),
('Marketing', 'London'),
('IT', 'San Francisco');

Esta sentencia SQL inserta tres filas en la tabla departments.

Puede verificar la creación de la tabla y la inserción de datos consultando las tablas:

SELECT * FROM employees;
SELECT * FROM departments;

Estos comandos mostrarán el contenido de las tablas employees y departments, respectivamente.

Crear una Vista Simple

En este paso, creará una vista simple llamada employee_info que selecciona columnas específicas de la tabla employees (empleados).

Una vista (view) es una tabla virtual basada en el conjunto de resultados de una sentencia SQL. Simplifica las consultas complejas y proporciona un nivel de abstracción.

Para crear la vista employee_info, ejecute la siguiente sentencia SQL en la shell de SQLite:

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

Esta sentencia SQL crea una vista llamada employee_info que selecciona las columnas id, first_name (nombre), last_name (apellido) y department (departamento) de la tabla employees.

Ahora puede consultar la vista como si fuera una tabla:

SELECT * FROM employee_info;

Este comando mostrará el contenido de la vista employee_info, que es un subconjunto de las columnas de la tabla employees.

Crear una Vista Compleja con Joins

En este paso, creará una vista más compleja llamada employee_department_info que une (joins) las tablas employees (empleados) y departments (departamentos).

Unir tablas (Joining tables) le permite combinar datos de múltiples tablas basándose en una columna relacionada.

Para crear la vista employee_department_info, ejecute la siguiente sentencia SQL en la shell de SQLite:

CREATE VIEW employee_department_info AS
SELECT
    e.first_name,
    e.last_name,
    e.department,
    d.location
FROM
    employees e
JOIN
    departments d ON e.department = d.name;

Esta sentencia SQL crea una vista llamada employee_department_info que une las tablas employees y departments en la columna department (departamento). Selecciona el nombre (first name), el apellido (last name), el departamento (department) del empleado y la ubicación (location) del departamento.

Ahora puede consultar la vista para ver los datos combinados:

SELECT * FROM employee_department_info;

Este comando mostrará el contenido de la vista employee_department_info, mostrando la información del empleado junto con la ubicación de su departamento.

Crear una Vista con Funciones de Agregación

En este paso, creará una vista llamada department_salary_stats que utiliza funciones de agregación (aggregate functions) para calcular el salario promedio (average salary) para cada departamento.

Las funciones de agregación realizan cálculos sobre un conjunto de valores y devuelven un único resultado.

Para crear la vista department_salary_stats, ejecute la siguiente sentencia SQL en la shell de SQLite:

CREATE VIEW department_salary_stats AS
SELECT
    department,
    AVG(salary) AS average_salary
FROM
    employees
GROUP BY
    department;

Esta sentencia SQL crea una vista llamada department_salary_stats que calcula el salario promedio para cada departamento utilizando la función AVG y agrupa los resultados por departamento utilizando la cláusula GROUP BY.

Ahora puede consultar la vista para ver el salario promedio para cada departamento:

SELECT * FROM department_salary_stats;

Este comando mostrará el contenido de la vista department_salary_stats, mostrando el salario promedio para cada departamento.

Actualizar Datos a Través de una Vista Actualizable

En este paso, aprenderá sobre las limitaciones de las vistas de SQLite y cómo crear vistas verdaderamente actualizables utilizando disparadores (triggers) INSTEAD OF.

Importante: Por defecto, las vistas de SQLite son de solo lectura. No se pueden directamente ACTUALIZAR (UPDATE), INSERTAR (INSERT) o ELIMINAR (DELETE) datos a través de una vista. Sin embargo, puede crear vistas actualizables utilizando disparadores INSTEAD OF.

Primero, entendamos la limitación intentando una actualización directa en nuestra vista existente:

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

Esto fallará con un error porque las vistas de SQLite son de solo lectura por defecto.

Para crear una vista verdaderamente actualizable, necesitamos usar disparadores INSTEAD OF. Recreemos la vista employee_info y agreguemos un disparador INSTEAD OF para las actualizaciones:

DROP VIEW IF EXISTS employee_info;

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

Ahora, cree un disparador INSTEAD OF para manejar las operaciones UPDATE:

CREATE TRIGGER update_employee_info
INSTEAD OF UPDATE ON employee_info
BEGIN
    UPDATE employees
    SET first_name = NEW.first_name,
        last_name = NEW.last_name,
        department = NEW.department
    WHERE id = OLD.id;
END;

Ahora puede actualizar datos a través de la vista:

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

Esta declaración UPDATE ahora funcionará porque el disparador INSTEAD OF redirige la actualización a la tabla subyacente employees.

Puede verificar la actualización consultando directamente la tabla employees:

SELECT * FROM employees WHERE id = 1;

Este comando mostrará la fila en la tabla employees con id = 1, y debería ver que la columna department se ha actualizado a 'HR'.

Eliminar Vistas y Disparadores Obsoletos

En este paso, eliminará las vistas y los disparadores que creó en los pasos anteriores.

A medida que su base de datos evoluciona, algunas vistas y disparadores pueden volverse obsoletos o innecesarios. Es importante eliminar estos objetos para mantener un esquema de base de datos limpio y eficiente.

Primero, elimine el disparador INSTEAD OF:

DROP TRIGGER IF EXISTS update_employee_info;

Luego, elimine las vistas. Para eliminar una vista, use la instrucción DROP VIEW. Por ejemplo, para eliminar la vista employee_info, ejecute el siguiente comando:

DROP VIEW IF EXISTS employee_info;

La cláusula IF EXISTS previene un error si la vista no existe.

Elimine también las otras vistas:

DROP VIEW IF EXISTS employee_department_info;
DROP VIEW IF EXISTS department_salary_stats;

Puede verificar que las vistas se han eliminado consultando la tabla sqlite_master:

SELECT name FROM sqlite_master WHERE type='view';

Este comando debería devolver un resultado vacío, lo que indica que no hay vistas en la base de datos.

Finalmente, salga del shell de SQLite:

.exit

Este comando cierra la conexión a la base de datos employees.db y lo devuelve a la terminal de Linux.

Resumen

En este laboratorio, has aprendido a crear, consultar, actualizar y eliminar vistas en SQLite. Comenzaste creando vistas simples basadas en una sola tabla y luego avanzaste a vistas más complejas que involucran joins (uniones) y funciones agregadas. Descubriste que las vistas de SQLite son de solo lectura por defecto, pero aprendiste a crear vistas verdaderamente actualizables utilizando disparadores (triggers) INSTEAD OF. También aprendiste a eliminar correctamente vistas y disparadores obsoletos para mantener un esquema de base de datos limpio. Estas habilidades son esenciales para simplificar consultas complejas, controlar el acceso a los datos y gestionar tus bases de datos SQLite de manera efectiva.