SGBD SQLITE

SGBD SQLITE

1

Documentación SQLite ÍNDICE 1.

Introducción............................................................................................................................. 2

2.

Características Generales de SQLite ................................................................................ 2

3.

Arquitectura de SQLite ......................................................................................................... 3

4.

Instalación y Configuración ................................................................................................ 5

5.

Bases de Datos y Tipos de Datos en SQLite ................................................................ 12

6.

Operaciones básicas en SQLite ....................................................................................... 13

7.

SQLite en Aplicaciones Java ............................................................................................ 20

SGBD SQLITE

2

1. Introducción SQLite es un sistema gestor de base de datos que se encuentra implementado como una librería o un proceso, autocontenido, sin configuración y transaccional. Es Open Source y es de carácter embebido, con lo cual se la utiliza en muchas aplicaciones donde se quiera tener una base de datos pero embebida en la propia aplicación evitando su instalación y configuración como un servidor aparte. El SGBD es un proceso denominado sqlite, el cual construye y mantiene bases de datos en archivos de extensión .db y ofrece todas las funciones estándar de SQL. Permite efectuar operaciones de lectura y escritura sobre dichos ficheros al interactuar con la base de datos. Entre las características que posee se cuentan con el manejo de tablas, índices, triggers1 y vistas, entre otros. Funciona tanto en sistemas de 32 y 64 bits. 2. Características Generales de SQLite SQLite se implementa como una librería compacta, integrada por ficheros escritos en el lenguaje C o sino como un fichero ejecutable de Windows cuyo tamaño es de aproximadamente de 350 KB, dependiendo de la plataforma. El mismo funciona relativamente rápido en el sistema, ya que no utiliza archivos de paginación elevados como sucede en el caso de los motores de base de datos propietarios u open Source que poseen un servidor y un cliente, como ser Microsoft SQL Server, IBM DB2, MySQL, Oracle, PostgresSQL, etc. Esta característica hace que SQLite sea utilizada en una gran variedad de aplicaciones, como ser procesadores de texto, navegadores web, aplicaciones para dispositivos móviles, PDAs, aplicaciones en general, etc., para efectuar el almacenamiento de información sobre tablas relacionales con las ventajas que ello posee por sobre el manejo de archivos. SQLite ha sido testeado cuidadosamente en sus distintas versiones, posee un buen manejo de memoria en caso de fallos y errores de entrada/salida de disco. Las transacciones cumplen con la propiedad ACID (Atomicidad, Consistencia, Integridad y Aislamiento). Resumiendo, se puede decir que las principales características de SQLite son:      1

Cumplimiento con el estándar ACID. No requiere instalación ni configuración para poder ser utilizada. Implementa el estándar SQL’92. Se trata de una base de datos completa contenida en un fichero. Soporta grandes bases de datos y también datos de tipo BLOB.

Trigger: es un procedimiento que se ejecuta en el SGBD cuando se cumple con determinada condición definida para el mismo.

SGBD SQLITE

3

 Su código fuente de implementación es de tamaño reducido.  Es muy rápida al efectuar operaciones de tipo cliente-servidor.  Esta escrita en el lenguaje C y algunos componentes en TCL2.  Su código fuente ha sido 100% testeado pasando exitosamente el mismo.  Su código fuente se encuentra disponible para toda la comunidad de desarrolladores.  Es auto contenido, lo cual significa que no requiere dependencias externas.  Soporta las plataformas UNIX, WINDOWS y OS/2.  Provee una herramienta de línea de comandos para la gestión del motor, como así también existen herramientas de interface gráfica que pueden utilizarse para realizar las operaciones. Los usos sugeridos para SQLite son:  Para mapear formatos de archivo de la aplicación como ser archivos XML y demás.  Bases de datos para dispositivos celulares, MP3, y demás componentes electrónicos.  Bases de datos para sitios web por su tamaño reducido.  Bases de datos para aplicaciones empresariales. Algunas de las aplicaciones conocidas a nivel mundial que utilizan este SGBD:      

Adobe Skype Google Mc Afee Symbian Etc.

Las limitaciones que posee básicamente son tres:   

Posee limitación en el uso de consultas anidadas en la cláusula WHERE. Se hace caso omiso de las claves foráneas. Falta de documentación en español.

Sin embargo, por sus ventajas la hace apropiada a una gran variedad de aplicaciones para el usuario. 3. Arquitectura de SQLite A continuación se presenta el diagrama de bloques de la arquitectura de SQLite:

2

TCL: es un lenguaje de programación dinámico e interpretado. Forma parte del TCL/ TK.

4

SGBD SQLITE

En donde se puede ver básicamente los grandes bloques que componen a su arquitectura: El Núcleo (Core) del sistema gestor de base de datos:  Interface: la interface provista por SQLite se encuentra implementada mediante funciones en los archivos de código fuente main.c, legacy.c y vbdeapic, los cuales posee rutinas en general que hacen a las funciones básicas. Entre las funciones más utilizadas se tiene sqlite3_get_table() y sqlite3_complete(). La interface TCL se encuentra implementada en el fichero tclsqlite.c. Todos los comandos en sqlite son precedidos por el prefijo sqlite3, para evitar problemas con cualquier otro sistema gestor de base de datos.  Procesador de comandos de SQL: se trata del componente central cuya función es la de recibir comandos SQL y transformarlos al formato requerido para que el compilador pueda manejarlos, como así también la máquina virtual.  Máquina Virtual: el programa que se genera en el Generador de Código del compilador SQL es ejecutado por la máquina virtual. La misma implementa una máquina abstracta que permite manipular las bases de datos que están almacenadas en los archivos. Tiene una pila para uso inmediato. Los archivos asociados son vdbe.c, vdbe.h y vdbeInt.h. Las funciones que implementa utilizan llamadas a rutinas en el lenguaje C. Para cada grupo de funciones se tiene el fichero en el lenguaje C que las implementa.

SGBD SQLITE

5

El compilador SQL:  Tokenizer: cuando se ejecutan las sentencias SQL, el tokenizer recibe el string y lo que hace es dividirla en tokens y pasarlos uno por uno al Parseador. Este componente se encuentra codificado en el fichero tokenize.c. En este diseño, el tokenizer llama al parseador.  Parser: este componente le asigna un significado a cada tokens según el contexto. Se lo generó utilizando el generador de Parsers Lemon. Define el concepto de un destructor no terminal que maneja automáticamente la memoria.  Generador de código: luego de que el parser ensamble los tokens dentro de sentencias SQL, éste llama al generador de código para que produzca código entendible por la máquina virtual que haga las funciones solicitadas en la sentencia SQL. Los archivos donde se encuentra todo ello son: attach.c, auth.c, build.c, delete.c, expr.c, insert.c, pragma.c, select.c, trigger.c, update.c, vacuum.c y where.c. En los mismos se encuentran codificadas las rutinas para realizar las operaciones SQL estándar. También existen otros ficheros con funciones asociadas. El Backend:  B-Tree: la base de datos SQLite se mantiene en el disco bajo la implementación de un árbol-B contenido en el fichero de código fuente btree.c. Esto es, por cada tabla e índice en la base de datos se almacena un fichero en el disco.  Caché de Páginas: el módulo B-Tree requiere información de los clusters del disco. El tamaño por defecto de los mismos es de 1024 bytes, pero puede variar entre 512 y 65536 bytes. El caché de páginas también provee la abstracción correspondiente de rollback y operaciones de commit de la base de datos sobre el fichero correspondiente a la misma, garantizando eficiencia y eficacia. Se implementa en las librerías pager.c y pager.h.

 Interface con el Sistema Operativo: SQLite utiliza una capa de abstracción para comunicarse con el sistema operativo. Se encuentra definida en el fichero os.h y cada sistema operativo tiene su implementación, como ser por ejemplo os_unix.c, os_win.c. Los componentes accesorios:  Utilitarios: la alocación de memoria y comparación de strings tiene rutinas localizadas en el fichero util.c. Las tablas de símbolos se encuentran en el fichero hash.c entre otros.  Código de Prueba: existen scripts que poseen código de prueba, con la extensión .c para efectuar pruebas diversas, como ser text1.c, md.5.c, os_test.c. 4. Instalación y Configuración Como se indicó dentro de las características del SGBD, el mismo se encuentra estructurado como una librería en C, pero también se puede disponer de un fichero ejecutable del mismo. Además, también se dijo que su instalación es muy simple, es decir

6

SGBD SQLITE

que no requiere setup ni configuración alguna. En el sitio oficial se puede descargar gratuitamente el mismo, en http://www.sqlite.org/download.html se encuentran las opciones de descarga, donde tenemos:   

Los ficheros de código fuente. La documentación. Ficheros precompilados, en sus versiones para Linux, Mac y Windows.

De todas las opciones, la más sencilla es descargar el fichero precompilado para Windows, sqlite-shell-win32-x86-3071100.zip, que se trata de un archivo .zip, lo descargaremos y lo descomprimiremos en un directorio del disco rígido y se tiene el sistema gestor listo para poder utilizarlo.

7

SGBD SQLITE

El archivo sqlite3 es el correspondiente al sistema gestor de base de datos. Para poder utilizarlo existen básicamente tres opciones de uso:  Desde la consola del sistema.  Mediante la herramienta SQLite Manager.  Mediante el navegador web Mozilla Firefox, para el cual existe una extensión que puede agregarse al mismo para su manejo. Para poder utilizarlo desde la consola CMD de Windows, se debe agregar una referencia a la variable Path del sistema, del fichero sqlite3 para que pueda ser utilizado directamente desde la misma. Eso se hace, en las variables de entorno del sistema:

8

SGBD SQLITE

Y luego, desde la consola de Windows en CMD se puede utilizar el comando sqlite3 para acceder al motor de base de datos:

Luego, se tiene la herramienta SQLite Manager, que es una herramienta Wizard que permite gestionar visualmente las bases de datos SQLite.

La misma puede ser descargada gratuitamente sin problemas desde varios sitios web, existen versiones desktop de la herramienta y web también. En este caso se utiliza la versión desktop. Se la descarga, se trata de un fichero .RAR. Una vez efectuada su descarga, se la puede iniciar y posee todas las opciones para el manejo de bases de datos SQLite.

9

SGBD SQLITE

En la figura anterior se puede visualizar las secciones del programa. El mismo, en su parte izquierda Schema presenta la opción de visualizar las bases de datos SQLite que se tengan abiertas. Cada base de datos se almacena en el directorio de instalación como un fichero con extensión .db, el cual almacena todos los elementos de la misma incluidos los datos.

En la parte superior derecha se pueden escribir y ejecutar consultas SQL a la base de datos. En el menú principal se tienen las opciones de la herramienta, incluyendo: File – aquí se pueden acceder y manipular las bases de datos SQLite que se tengan.

10

SGBD SQLITE

Context – ofrece la opción de actualizar el árbol de bases de datos.

Database – acá se tienen opciones básicas para trabajar con la base de datos actual, como ser la creación de tablas, vistas, consultas, importar datos, esquemas para la tabla, etc.

System – acá se tienen opciones genéricas para la aplicación Sqlite

Help – este menú incluye las opciones de ayuda del sistema.

11

SGBD SQLITE

Por último, se tiene una extensión para el navegador Mozilla Firefox, que se lo puede agregar para utilizarlo desde el mismo como herramienta de gestión. La misma tiene opciones similares a SQLite Manager.

12

SGBD SQLITE

5. Bases de Datos y Tipos de Datos en SQLite La mayoría de los motores de base de datos SQL utiliza tipado de datos estático y rígido. De esta manera, el tipo de dato de un valor está determinado por su contenedor, es decir la columna particular en la cual se almacena. SQLite utiliza un sistema de tipos dinámico más general. Aquí, el tipo de datos de una columna es el valor propiamente dicho, no el tipo definido en sí de la columna. Es compatible con la mayoría de tipos de las bases de datos generales de los demás sistemas gestores de base de datos sin problemas. La característica del tipado de datos de ser dinámico, permite llevar a cabo ciertas operaciones que no pueden ser realizadas con otros SGBD. Los tipos de datos básicos de SQLite son:  NULL – para indicar que no posee valor  INTEGER – para el valor de los enteros, de 1 a 8 bytes.  REAL – es valor de un número en coma flotante.  TEXT – para el tipo de datos de un String.  BLOB – es el valor de un objeto de datos grande, el cual se almacena exactamente como se lo obtuvo en la entrada.  El tipo de datos booleano, en SQLite se almacena como un entero (0 ó 1).  Los datos de tipo fecha y hora en SQLite no tienen un tipo definido, por lo cual se debe adaptar su estructura en los tipos básicos TEXT, INTEGER O REAL. También podemos utilizar el concepto de nombres afines (Affinity) para referirnos a algunos tipos de datos. De esta manera, se puede lograr compatibilidad con los tipos de datos manejados por otros SGBD o lenguajes de programación. En la siguiente tabla se pueden visualizar algunos: Nombre Affinity Tipo Real INT INTEGER INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 CHARACTER(20) TEXT VARCHAR(255) VARYING CHARACTER(255) NCHAR(55)

13

SGBD SQLITE NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB BLOB REAL DOUBLE DOUBLE PRECISION FLOAT NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME

BLOB REAL

NUMERIC

Además el sistema gestor admite el uso de clave primaria para los registros de la tabla. También brinda soporte a las operaciones básicas como ser UNION, INTERSECT, GROUP BY, ORDER BY, etc. Todo lo anterior y lo establecido en el estándar SQL-ANSI 92. 6. Operaciones básicas en SQLite 

Crear base de datos

Para crear una base de datos, se utiliza el siguiente comando desde la consola del sistema: Sqlite3 baseDeDatos.db

Y se creará un archivo con el nombre de la base de datos y la extensión .db. Por ejemplo, en el directorio siguiente tenemos dos archivos correspondientes a dos bases de datos llamadas “Contacto” y “Registro”:

14

SGBD SQLITE

Luego consultamos por las bases de datos del sistema, con el comando .databases:



Crear una tabla CREATE TABLE nomTabla (atr1 tipoDato, atr2 tipoDato, PRIMARY KEY(atr1))

Aquí se pueden utilizar los calificadores para los atributos NOT NULL, UNIQUE y CHECK. La estructura gráfica es:

15



SGBD SQLITE

Inserciones, modificaciones y eliminaciones

En este caso, la sintaxis es la misma que la definida en el estándar definido para SQLANSI. a.

Inserción INSERT INTO TABLE VALUES(VALOR1, VALOR2, VALOR3,…)

La estructura gráfica es:

16

b.

SGBD SQLITE

Eliminación DELETE FROM TABLA WHERE (CONDICION)

Y la estructura gráfica es:

17

c.

SGBD SQLITE

Actualización

UPDATE TABLE SET (CAMPO=VALOR_NUEVO) WHERE (CONDICION)

Y la estructura gráfica es:

18



SGBD SQLITE

Consultas en general

Las consultas se realizan mediante SELECT. SELECT atr1, atr2, atr3 FROM TABLA WHERE (CONDICION);

Y la estructura gráfica es:

19

SGBD SQLITE

Además de todo lo anterior, se dispone de funciones y operaciones generales que se pueden llevar a cabo en el SGDB, como ser:     

Crear, actualizar y eliminar Índices. Crear, actualizar y eliminar Vistas. Crear, actualizar y eliminar Triggers. Gestionar transacciones. Funciones de agregación: promedio, suma, conteo, etc. o Avg(x) o Count(x) o Sum(x) o Total(x) o Max(x) o Min(x) o Etc.

20

SGBD SQLITE

La sintaxis de las mismas se puede visualizar en el sitio oficial del SQLite, en http://www.sqlite.org/lang.html.

Además mediante las herramientas GUI como ser SQLite Manager y el plugin de Mozilla Firefox se pueden realizar estas operaciones visualmente. 7. SQLite en Aplicaciones Java Se puede desarrollar una aplicación Java que maneje bases de datos SQLite, para lo cual se requiere tener instalados los siguientes componentes:  

El SGBD SQLite. SQLite Manager. Driver para Java de SQLite, que se puede descargar gratuitamente desde

 http://www.zentus.com/sqlitejdbc/.



El entorno de desarrollo ECLIPSE IDE.

Los pasos a realizar son: 1. 2. 3. 4.

Crear la base de datos en SQLITE MANAGER. Crear un proyecto de Java en Eclipse. Agregar el Driver para SQLite en el proyecto. Escribir el código fuente.

A continuación se presentará un ejemplo de una aplicación que consulta una tabla denominada “contacto” que posee la siguiente estructura: CONTACTO Atributo Tipo Nombre String Apellido String TipoTel String NroTel String

Las clases básicas que son requeridas en la aplicación para realizar la consulta de datos a una base de datos SQLite existente son: 

Una clase para modelar un objeto que represente a la tabla, en este caso sería Registro.java.  Una clase que permita gestionar la conexión a la base de datos, Conexión.java.  Una clase que permita efectuar las operaciones en la base de datos, ABM.java.  Una clase para mostrar los datos en la interface gráfica, GUI.java. De las anteriores, las clases que se encuentran relacionadas con el acceso la base de datos son:

21

SGBD SQLITE

Clase Registro.java – Esta clase posee los atributos asociados a los campos de la tabla, el constructor y los getters y setters. public class Registro { public String nombre; public String apellido; public String tipoTel; public String nroTel; public Registro(String nombre, String apellido, String tipoTel, String nroTel){ this.nombre = nombre; this.apellido = apellido; this.tipoTel = tipoTel; this.nroTel = nroTel; } /** * @return the nombre */ public String getNombre() { return nombre; } /** * @param nombre the nombre to set */ public void setNombre(String nombre) { this.nombre = nombre; } /** * @return the apellido */ public String getApellido() { return apellido; } /** * @param apellido the apellido to set */ public void setApellido(String apellido) { this.apellido = apellido; } /** * @return the tipoTel */ public String getTipoTel() { return tipoTel; } /** * @param tipoTel the tipoTel to set */

22

SGBD SQLITE public void setTipoTel(String tipoTel) { this.tipoTel = tipoTel; } /** * @return the nroTel */ public String getNroTel() { return nroTel; } /** * @param nroTel the nroTel to set */ public void setNroTel(String nroTel) { this.nroTel = nroTel; }

}

Clase Conexión.java – esta clase inicializa el driver y efectúa la conexión a la base de datos. En la misma Connection es el objeto que permite manejar las conexiones a la base de datos y Statement el objeto que permite manejar las consultas. package proyectosqlitefabio; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import javax.swing.JOptionPane; public class Conexion { Connection conexion; Statement consulta; public String ruta; /** * Constructor for objects of class Conexion */ public Conexion() { //C:\SQLITE DB ruta = "C:/SQLITE DB/contacto.db"; } public void conectar(){ try { Class.forName("org.sqlite.JDBC"); } catch (ClassNotFoundException e) {

23

SGBD SQLITE

//JOptionPane.showMessageDialog(null, e.getMessage()); JOptionPane.showMessageDialog(null, "Error. No se encontró el driver para conectar a la base de datos"); } try { conexion = DriverManager.getConnection("jdbc:sqlite:"+ruta); consulta = conexion.createStatement(); } catch (SQLException e) { JOptionPane.showMessageDialog(null, e.getMessage()); } } }

ABM.java – es la clase donde se realizan las operaciones SQL. La misma extiende de conexión para conectarse a la base de datos y realizar operaciones con la misma. El método insertarRegistro utiliza el método executeUpdate(String sql) con un parámetro de

tipo string, que es la sentencia SQL a ejecutar en la base de datos. Aquí se puede escribir cualquier tipo de consulta y el SGBD la ejecutará y devolverá un ResultSet, que es un puntero sobre una ventana de memoria para recorrer y retornar los datos. El método consultarRegistros(String sql) es similar al insertarRegistro y lo que hace es consultar y retornar los datos de la base de datos. import java.sql.ResultSet; import java.sql.SQLException; import javax.swing.JOptionPane; public class ABM extends Conexion{ public ABM(){ System.out.println("Clase de ABM de contactos"); } public boolean insertarRegistro(String sql){ boolean valor = true; conectar(); try { consulta.executeUpdate(sql); JOptionPane.showMessageDialog(null, "Operacion realizada con exito"); } catch (SQLException e) { valor = false; JOptionPane.showMessageDialog(null, e.getMessage()); } finally{ try{ consulta.close(); conexion.close(); }catch (Exception e){ e.printStackTrace(); } } return valor;

SGBD SQLITE

24

} public ResultSet consultarRegistros(String sql){ conectar(); ResultSet resultado = null; try { resultado = consulta.executeQuery(sql); } catch (SQLException e) { System.out.println("Mensaje:"+e.getMessage()); System.out.println("Estado:"+e.getSQLState()); System.out.println("Codigo del error:"+e.getErrorCode()); JOptionPane.showMessageDialog(null, ""+e.getMessage()); } return resultado; } }

Lo anterior constituye una implementación muy básica de un ABM mediante sentencias SQL sobre la base de datos SQLite. Luego se podría agregar mayores funcionalidades o extenderlas para incorporar más características a la aplicación. En la plataforma Android se dispone de la clase SQLiteDatabase, mediante la cual se puede trabajar con bases de datos SQLite.