SQLite3

Seminarios de Formación PolinuXSeminarios de Formación PolinuXValència – 23 de Abril de 2009València – 23 de Abril de 2009

Andrés Ignacio Martínez SotoAndrés Ignacio Martínez Sotoanmarso4@fiv.upv.esanmarso4@fiv.upv.es

SQLite3

Contenido del seminario

¿Qué es SQLite3? Características básicas de SQLite3 Usuarios de SQLite3 Usos de SQLite3

Instalación y herramientas SQLite3 en Detalle

Tipos de datos y soporte de SQL:92 Funciones de agregación y fecha Transacciones , triggers y vistas

Demo: ”Facturas”

SQLite3

SQLite3

Datos básicos Datos básicos de SQLite3de SQLite3

SQLite3

¿Qué es SQLite3?¿Qué es SQLite3?

Sistema de Gestión de Bases de Datos relacionales

creado por D. Richard Hipp Es de dominio público Escrito en C en una biblioteca de <500kb y muy ligero

D Richard Hippautor de SQLite3

SQLite3

Características básicas de SQLite3Características básicas de SQLite3 Multiplataforma:

Windows Linux Mac Os

Muy portable (la base de datos es el fichero y nada más): Servidores Workstations PDAs Teléfonos móviles Otros sistemas embebidos

SQLite3

Características básicas de SQLite3 (II)Características básicas de SQLite3 (II) Sin configuración (Zero-Conf)

La base de datos es el fichero, y no se necesita nada más que acceso al mismo.

”Serverless”: No necesaria infraestructura cliente-servidor pesada (MySQL, Oracle,

PostgreSQL … )

Transacciones ACID {Atomicity, Consistency, Isolation and Durability}

Soporte de triggers

Bases de datos de hasta 2 TB de tamaño

SQLite3

Características básicas de SQLite3 (III)Características básicas de SQLite3 (III) Disponibilidad como biblioteca estática o dinámica Disponibilidad de una API para la mayoría de

lenguajes de programación C/C++ PHP Python C# … Java Ruby

Es fácilmente embebible en un programa de gestión

SQLite3

Usuarios de SQLite3Usuarios de SQLite3 Adobe: Photoshop LightRoom, Air … Apple: Aperture, Safari, Iphone, Ipod Touch … Mozilla: Firefox Google: Gears … Symbian Muchos proyectos de Software Libre

SQLite3

Usos de SQLite3Usos de SQLite3

Base de datos ”personal” (¿sustituto de Access?) Base de datos para educación (para aprender y

comprender como funcionan las bases de datos) Base de datos temporales Base de datos para demostraciones Base de datos para dispositivos embebidos Base de datos para aplicaciones web

SQLite3

No utilizar SQLite3 …No utilizar SQLite3 …

Donde haya mucha concurrencia de accesos Aplicaciones Cliente-Servidor ”grandes” Tablas con campos de tamaño grande

SQLite3

SQLite3

Instalación Instalación herramientasherramientas

SQLite3

Instalación de SQLite3Instalación de SQLite3 Gnu-Linux:

Ubuntu/Debian: sudo apt-get install sqlite3 libsqlite3-dev

Otros: Bajar binarios de SQLite3 y/o compilarlos

Windows: Bajar cli y dll de SQLite3

Mac os X: Bajar binarios de SQLite3

SQLite3

Herramientas para SQLite3Herramientas para SQLite3 SQLite3 Command Line Interface:

Consola para manejar una base de datos SQLite3

SQLite Browser: Cliente gráfico de SQLite3 http://sqlitebrowser.sourceforge.net/

SQLite Administrator: Cliente gráfico de SQLite3 http://sqliteadmin.orbmu2k.de/

SQLiteman: Cliente gráfico de SQLite3 http://sqliteman.com/

SQLite Manager: Extensión para Firefox que permite manejar bases de datos SQLite3:

https://addons.mozilla.org/ca/firefox/addon/5817

SQLite3

Herramientas para SQLite3 (II)Herramientas para SQLite3 (II)

SQLite3 CLI

SQLite3

Herramientas para SQLite3 (III)Herramientas para SQLite3 (III)

SQLiteAdministrator

SQLite3

SQLite3 en detalleSQLite3 en detalle

SQLite3

Tipos de datos soportados por SQLite3Tipos de datos soportados por SQLite3

SQLite3 utiliza tipado dinámico para los valores de las columnas...

NULL: Para valores nulos

INTEGER: Para valores numéricos de 32 bits con signo

REAL: Para valores numéricos de 32 bits con signo en formato IEEE

TEXT o VARCHAR: Para cadenas de texto en UTF-8

BLOB: Para datos binarios

SQLite3

Cosas de SQL:92 no soportadas por SQLite3Cosas de SQL:92 no soportadas por SQLite3 Claves ajenas/integridad referencial:

Pueden implementarse mediante triggers ver http://justatheory.com/computers/databases/sqlite/foreign_key_triggers.html

Triggers: Triggers con ”FOR EACH STATEMENT”, Triggers que se disparan por otro

Trigger.

Modificar tablas: Solo rename table y add column.

Joins: RIGHT y FULL OUTER JOIN no están soportados.

Vistas actualizables (solo vistas de solo lectura)

Grant y Revoke

SQLite3

Funciones de columna y agregadas en SQLite3Funciones de columna y agregadas en SQLite3 SQLite3 permite uso de GROUP BY ,y HAVING

También se permite el uso de: AVG (columna)

COUNT (columna)

MAX (columna) y MIN (columna)

SUM (columna)

SELECT COUNT(*) AS cuantos,nota FROM NotasAlumnoAprobados GROUP BY( nota ) HAVING ( nota>5 ) ORDER BY 1 DESC;

SQLite3

Funciones de fecha en SQLite3Funciones de fecha en SQLite3 SQLite3 tiene como funciones para fechas y horas a

strftime( formato, string, modificador, modificador, … );

date( string, modificador, modificador, …) ;

time( string, modificador, modificador, …) ;

datetime( string, modificador, modificador, …) ;

current_time , current_date y current_timestamp

SELECT COUNT(*), STRFTIME('%Y',fecha) FROM Facturas GROUP BY(

STRFTIME('%Y',fecha)) HAVING ( STRFTIME('%Y','now')-STRFTIME('%Y',fecha)=1 );

SQLite3

Transacciones en SQLite3Transacciones en SQLite3 Las transacciones en SQLite3 cumplen el estándar

ACID (Atomicity, Consistency, Isolation and Durability):

Atomicity (Atomicidad): O se ejecutan todas las tareas de la transacción o ninguna

Consistency (Consistencia): Garantiza que sólo se ejecutan las operaciones que mantienen la integridad de la base de datos

Isolation (Aislamiento): Asegura que una operación no puede ver ni influir otras

Durability (Durabilidad): Una vez realizada una operación, ésta persistirá y no se podrá deshacer aunque falle el sistema.

SQLite3

Transacciones en SQLite3 (I)Transacciones en SQLite3 (I) Todas las transacciones comienzan por

BEGIN { DEFERRED/INMEDIATE/EXCLUSIVE } TRANSACTION (DEFERRED por defecto)

Luego se colocan las operaciones de la transacción

Por último se hace un COMMIT; O END; TRANSACTION

Y si fuera necesario, un ROLLBACK a un SAVEPOINT (punto de guardado)

SQLite3Transacciones en SQLite3 (II)Transacciones en SQLite3 (II)

Ejemplos de transacciones:

BEGIN TRANSACTION; CREATE TABLE Alumno (id INTEGER PRIMARY KEY,nombre TEXT); INSERT INTO Alumno VALUES(1,”Fulanito”); INSERT INTO Alumno VALUES(2,”Menganito”); DELETE FROM Alumno WHERE nombre LIKE '%anito';

SELECT COUNT(*) FROM Alumno;COMMIT;

BEGIN TRANSACTION; UPDATE Alumno SET nombre=”*ito”;

SELECT * FROM Alumno;COMMIT;ROLLBACK;

SQLite3

Triggers en SQLite3Triggers en SQLite3 Creación de Triggers

SQLite3

Triggers en SQLite3 (I)Triggers en SQLite3 (I)

CREATE TRIGGER TR_Alumno BEFORE DELETE ON AlumnoFOR EACH ROWBEGIN

INSERT INTO Old_Alumnos VALUES (OLD.id,OLD.nombre);END;

CREATE TRIGGER UpdateCustAddress AFTER UPDATE OF address ON Customer FOR EACH ROW BEGIN UPDATE orders SET address = new.address WHERE customer_name=old.name; END;

SQLite3

Triggers en SQLite3 (II)Triggers en SQLite3 (II) Función RAISE (emite un error)

CREATE TRIGGER TRProyectoBEFORE DELETE ON Proyectos

WHEN OLD.id IN (SELECT id FROM Tareas

)BEGIN

SELECT RAISE(ROLLBACK,”Existen tareas de éste proyecto … ”);END;

SQLite3

Vistas en SQLite3Vistas en SQLite3

CREATE VIEW ClientesValenciaFacturas ASSELECT codcli,nombre,telefono,direccion FROM Clientes WHERE codcli IN (

SELECT codcli FROM Facturas )AND (codpostal LIKE '46%');

SQLite3

SQLite3

          DemoDemo      de SQLite3de SQLite3

  ” ” FACTURAS ”FACTURAS ”

SQLite3

Base de datos ”Facturas”Base de datos ”Facturas”

SQLite3

SQLite3

¿Preguntas?¿Preguntas?

SQLite3

CTRL+CCTRL+C