Difference between revisions of "SQLite/es"

From Lazarus wiki
Jump to navigationJump to search
Line 81: Line 81:
 
=====TSQLite3Connection=====
 
=====TSQLite3Connection=====
  
* DatabaseName: establecer esta propiedad con el nombre del fichero SQLite (¡trayecto absoluto!). Desafortunadamente, no se puede simplemente utilizar un trayecto relativo que trabaje sin cambios tanto en tiempo de ejecución como de diseño ***¿es esto todavía cierto? Can't you just copy the db file in a post-build shell script or symlink it?***. You should make sure that at application start the correct path to the file is always set programmatically, no matter what it contained at designtime.
+
* DatabaseName: establecer esta propiedad con el nombre del fichero SQLite (¡trayecto absoluto!). Desafortunadamente, no se puede simplemente utilizar un trayecto relativo que trabaje sin cambios tanto en tiempo de ejecución como de diseño ***¿es esto todavía cierto? ¿No puedes simplemente copiar el fichero de base de datos en un post-build shell scrip o enlace simbólico al mismo?***. Deberías asegurarte que la aplicación comienza con la ruta correcta al fichero de forma programática, sin problema de lo que contenga en tiempo de diseño.
 
 
  
 
{{Nota|para establecer el trayecto completo a la librería (si ubicas tu sqlite dll/so/dylib en un lugar donde el sistema operativo no lo encuentra por defecto, tal como el directorio application en Linux/OSX), puedes establecer la propiedad ''SQLiteLibraryName''  (ANTES de que se realice la conexión e.g. en el evento OnCreate del formulario principal), tal como esto:}}
 
{{Nota|para establecer el trayecto completo a la librería (si ubicas tu sqlite dll/so/dylib en un lugar donde el sistema operativo no lo encuentra por defecto, tal como el directorio application en Linux/OSX), puedes establecer la propiedad ''SQLiteLibraryName''  (ANTES de que se realice la conexión e.g. en el evento OnCreate del formulario principal), tal como esto:}}

Revision as of 11:25, 3 May 2017

English (en) español (es) français (fr) 日本語 (ja) polski (pl) русский (ru) 中文(中国大陆)‎ (zh_CN)

Soporte para SQLite en FPC/Lazarus

   SQLite es una base de datos embebida (sin servidor) monousuario que puede ser utilizada en aplicaciones FPC y Lazarus. Se pueden utilizar varios drivers para el acceso a SQLite desde programas FPC/Lazarus.

   Es necesario que dichos drivers de SQLite (librerias .dll / .so / .dylib) se ubiquen bien sea en el directorio de proyecto donde se encuentre el ejecutable para que funcione (no debemos por tanto olvidarnos de incluirlos con el programa a la hora de distribuirlo.

Win64: por favor ver las advertencias here sobre no usar ciertas versiones de FPC/Lazarus Win64.

Acceso directo a SQLite (Producto Comercial)

   Puedes utilizar una forma sencialla para conectar SQLite con Lazarus. Estos componentes se llaman LiteDAC, SQLite Data Access Components (LiteDAC) es una librería de componentes que aportan conectividad nativa con SQLite desde Lazarus y FreePascal bajo Windows, Mac OS X, iOS, Android, Linux y FreeBSD tanto para plataformas de 32 como de 64 bits. LietDAC está diseñado para programadores que quieran diseñar aplicaciones de bases de datos en plataforma cruzada tanto para escritorio como móvil sin tener que desplegar librerías adicionales. Puedes descargar una versión de prueba de este producto comercial en componentes Lazarus.


SQLDB incluido con Lazarus / FreePascal

   FPC/Lazarus incluyen componentes SQLDB que aportan soporte para bases de datos SQLite (TSQLite3Connection) de la solapa SQLdb en la paleta de componentes, permitiendo por ejemplo crear interfaces gráficos (GUIS) con componentes tales como DBGrids. La ventaja de utilizar SQLDB es que resulta bastante fácil el cambio a otra base de datos diferente como puede ser Firebird o PostgreSQL sin tener por ello que realizar grandes cambios.

Ver más abajo para más detalles.

sqldbcomponents.png

Soporte para Spatialite

   Spatialite son extensiones GIS (Geographic Information System) para SQLite que se pueden utilizar dentro de SQLDB. Ver Spatialite.

Soporta de encriptación para SQLite

   En versiones recientes de FPC (implementadas en Marzo de 2012), SQLDB incluye soporte para algunas versiones extendidas de SQLite3 que encriptan la base de datos SQLite mediante el algoritmo AES. Utiliza la propiedad password para establecer la llave de encriptación.

   Ejemplos:

  • SQLCipher: código abierto (open source), e.g. binarios Windows no disponibles sin cargo, no son libres (tienes que compilarlos por ti mismo).
  • System.Data.SQLite: código abierto (open source), binarios disponibles para Windows (32, 64, CE), descarga e.g uno de los binarios precompilados y renombra SQLite.Interop.dll como sqlite3.dll (si estás utilizando los enlazados estáticamente, presumiblemente necesitas renombrar System.Data.SQLite.DLL como sqlite3.dll)

Zeos

También se puede utilizar el paquete Zeos para ulitizar bases de datos SQLite con FreePascal / Lazarus.

SQLitePass

TSQLite3Dataset y TSQLiteDataset

   Existen paquetes diferenciados tales como TSQLiteDataset (unit sqliteds) y TSQLite3Dataset (unit sqlite3ds); leer más abajo para una descripción sobre como utilizarlos. Para encontrar la API de referencia y más tutoriales puedes visitar el sitio de sqlite4fpc.

   TSqliteDataset y TSqlite3Dataset son descendientes de TDataset que acceden, respectivamente a bases de datos SQLite2.8.x y 3.x.x. Para nuevos proyectos debería utilizarse presumiblemente TSQlite3Dataset como SQLite 3.x (es la versión actual).

   A continuación se muestra un listado de las principales ventajas y desventajas en comparación con otros drivers/métodos de acceso de FPC/Lazarus.

   Ventajas:

  • Flexible: los programadores pueden escoger entre utilizar o no el lenguaje SQL, permitiendo trabajar con esquemas de tablas simples o con esquemas más complejos que permite SQL/SQLite.

   Desventajas:

  • El cambio hacia otras bases de datos es más difícil que si se utilizan componentes SQLDB o Zeos.
Note-icon.png

Nota: Dicho esto, bastantes programadores utilizarán SQLDB o Zeos dadas las ventajas que aportan a menos que necesiten un menor nivel de acceso a la librería SQLite

Utilizando componentes SQLdb con SQLite

   Estas instrucciones se centran en SQLDB (la TSQLite3Connection) específicamente para SQLite. Para una visión general, echa un vistazo a SqlDBHowto la cual tiene bastante información útil sobre componentes SQLdb.

   Para un tutorial sobre como crear un programa de base de datos con interface gráfica de usuario (GUI) escrita para SQLite/SQLDB (así como para Firebird/SQLDB, PostgreSQL/SQLDB, básicamente soporta cualquier RDBMS SQLDB)se puede visitar SQLdb_Tutorial1.

   Utilizaremos una combinación de tres componentes de la pestaña SQLdb de Lazarus: TSQLite3Connection, TSQLTransaction y TSQLQuery. TSQLQuery actúa como nuestro TDataset; en el caso más simple representa una de nuestras tablas. En pos de la simplicidad: asegúrate de tener previamente una base de datos SQLite y que no tienes que crear una nueva en ese instante.

   TSQLite3Connection se puede encontrar en la unidad sqlite3conn, si necesitas declararla por ti mismo o si estás trabajando en FreePascal.

   Los tres componentes están conectados entre ellos como es habitual: en TSQLQuery hay que establecer las propiedades Database y Transaction, en TSQLTransaction la propiedad Database. No hay mucho más que hacer en los componentes Transaction y Connection, la mayor parte de las cosas interesantes se realizarán en el componente TSQLQuery.

   A continuación se explica como configurar los componentes:

TSQLite3Connection
  • DatabaseName: establecer esta propiedad con el nombre del fichero SQLite (¡trayecto absoluto!). Desafortunadamente, no se puede simplemente utilizar un trayecto relativo que trabaje sin cambios tanto en tiempo de ejecución como de diseño ***¿es esto todavía cierto? ¿No puedes simplemente copiar el fichero de base de datos en un post-build shell scrip o enlace simbólico al mismo?***. Deberías asegurarte que la aplicación comienza con la ruta correcta al fichero de forma programática, sin problema de lo que contenga en tiempo de diseño.
Note-icon.png

Nota: para establecer el trayecto completo a la librería (si ubicas tu sqlite dll/so/dylib en un lugar donde el sistema operativo no lo encuentra por defecto, tal como el directorio application en Linux/OSX), puedes establecer la propiedad SQLiteLibraryName (ANTES de que se realice la conexión e.g. en el evento OnCreate del formulario principal), tal como esto:

Por ejemplo bajo Linux:

 SQLiteLibraryName:='./sqlite3.so';

o bien bajo Windows:

 SQLiteLibraryName:='./sqlite3.dll';
TSQLQuery
  • SQL: Establécelo a una simple consulta de selección sobre cualquiera de tur tablas. Por ejemplo, si tienes una tabla llamada 'foo' y necesitas que este dataset represente a dicha table entonces utiliza lo siguiente:
 SELECT * FROM foo
  • Active: establécelo a True desde el IDE para testear si todo está configurado correctamente. Esto además activará automáticamente los objetos transaction y connection. Si recibes un mensaje de error entonces o bien DatabaseName de la conexión no es correcta o la query de SQL está mal. Posteriormente, cuando añadimos los campos (ver más abajo) los establecemos de nuevo como inactivos. No queremos que el IDE bloquee la base de datos SQLite cuando testeamos la aplicación (recuerda: ¡monousuario!).
  • Probablemente no sea necesario para el funcionamiento adecuado - tendrá que ser verificado (June 2012) Ahora ya podemos añadir campos a nuestro TSQLQuery. Mientras los componentes están todavía establecidos como activos haz un click en el pulsador derecho y "Edición de campos...". Add Click en el pulsador "+" y añade campos. Esto listará todos los campos retornados por la consulta SQL. Añade cuantos campos necesites, también puedes añadir aquí campos lookupAdd; en este caso asegúrate de que tienes ya definidos todos los campos necesarios en el otro dataset antes de comenzar a añadir campos lookup que hagan referencias a los mismos. Si la tabla tiene muchas columnas y no las necesitas todas, pudes especificar únicamente las que quieres, puedes además hace un poco más específica la consulta SQL, por ejemplo:
 SELECT campo1, campo2, campo5 FROM foo where campo3 = campo4

.

  • En el código hay que lamar a SQLQuery.ApplyUpdates y SQLTransaction.Commit, los eventos TSQLQuery.AfterPost y AfterInsert son buenos candidatos para esta tarea cuando se utlizan con controles enlazados a datos(data aware controls), pero se pueden posponer estas llamadas para otro momento. Si no se realizan estas llamadas, las tablas no serán actualizadas.
  • "La Base de datos está bloqueada": El IDE todavía podría estar bloqueando la base de datos (SQLite es una base de datos de monousuario), es probable que olvidaras de poner los componentes inactivos y desconectados de nuevo después de terminar la definición de los campos de los objetos TSQLQuery. Utiliza el evento OnCreate del formulario para establecer la ruta y activar los objetos en tiempo de ejecución. La mayor parte de las cosas que ha establecido en el TSQLQuery desde el IDE no requieren (y algunos ni siquiera lo permiten) estar activos en tiempo de diseño, la única excepción es la definición de los campos en que quieres leer el diseño de la tabla, en tiempo de diseño estar inactivos debe ser el estado normal.
  • Tus tablas deberían tener todas una primary key (llave primaria) y asegurarse de que el correspondiente campo tenga pfInKey y nada más en sus PoviderFlags (estos flags controlan como y donde se utiliza el campo cuando se contruyen automáticamente las consultas update y delete).
  • Si estás utilizando campos lookup
    • make sure the ProviderFlags for the lookup field is completely empty so it won't attempt to use its name in an update query. The lookup field itself is not a data field, it only acts on the value of another field, the corresponding key field, and only this key field will later be used in the update queries. You can set the key field to hidden because usually you don't want to see it in your DBGrid but it needs to be defined.
    • LookupCache must be set to True. At the time of this writing for some reason the lookup field will not display anything otherwise (but still work) and strangely the exact opposite is the case when working with the TSQLite3Dataset or other TXXXDataset components, here it must be set to False. I'm not yet sure whether this is intended behavior or a bug.

After the above is all set up correctly, you should now be able to use the TSQLQuery like any other TDataset, either by manipulating its data programmatically or by placing a TDatasouce on the Form, connecting it to the TSQLQuery and then using data contols like TDBGrid etc.

Creating a Database

   The TSQLite3Connection.CreateDB method inherited from the parent class actually does nothing; to create a database if no file exists yet, you simply have to write table data as in the following example:


   (Código estraido del ejemplo sqlite_encryption_pragma que se incluye a partir de Lazarus 1.3)

 var
  newFile : Boolean;
 begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  try
    // Since we're making this database for the first time,
    // check whether the file already exists
    newFile := not FileExists(SQLite3Connection1.DatabaseName);

    if newFile then
    begin
      // Create the database and the tables
      try
        SQLite3Connection1.Open;
        SQLTransaction1.Active := true;

        // Here we're setting up a table named "DATA" in the new database
        SQLite3Connection1.ExecuteDirect('CREATE TABLE "DATA"('+
                    ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
                    ' "Current_Time" DateTime NOT NULL,'+
                    ' "User_Name" Char(128) NOT NULL,'+
                    ' "Info" Char(128) NOT NULL);');

        // Creating an index based upon id in the DATA Table
        SQLite3Connection1.ExecuteDirect('CREATE UNIQUE INDEX "Data_id_idx" ON "DATA"( "id" );');

        SQLTransaction1.Commit;

        ShowMessage('Succesfully created database.');
      except
        ShowMessage('Unable to Create new Database');
      end;
    end;
  except
    ShowMessage('Unable to check if database file exists');
  end;
 end;

SQLite3 y Fechas

  • SQLite 3 doesn't store dates as a special DateTime value. It can stores them as strings, doubles or integers - see http://www.sqlite.org/datatype3.html#datetime.
  • In strings, the date separator is '-' as per SQL standard/ISO 8601. Thus, if you do an INSERT using the built-in DATE function, it will store it as something like 'YYYY-MM-DD'.
  • Reading a DateTime value can cause problems for DataSets if they are stored as strings: the .AsDateTime qualifier can stall on an SQLite 'string date' but this can be overcome by using something like strftime(%d/%m/%Y,recdate) AS sqlite3recdate in your SQL SELECT statement, which forces SQLite3 to return the date record in a specified format. (the format string %d/%m/%d corresponds to your locale date format which .AsDateTime will understand) ==> Please open a bug report with an example application demonstrating the problemif this is the case
  • When comparing dates stored as strings (using for example the BETWEEN function) remember that the comparison will always be a string comparison, and will therefore depend on how you have stored the date value.

Tips

  • Utiliza el procedimiento TSQLite3Connection.CreateDB para crear una nueva base de datos SQLite.

Utilizando TSQLite3Dataset

   Esta sección detalla como utilizar los componentes TSQLite3Dataset y TSQLite3Dataset para acceder a bases de datos SQlite.    por Luiz Américo luizmed(at)oi(dot)com(dot)br


Requerimientos

  • Para bases de datos sqlite2 (legado):
    • FPC 2.0.0 or higher
    • Lazarus 0.9.10 o superior.
    • SQLite runtime library 2.8.15 or above*
  • Sqlite2 ya no está en fase de mantenimiento y el fichero binario ya no se encuentra en el sitio de sqlite.
  • Para basesd e datos sqlite3:
    • FPC 2.0.2 o superior.
    • Lazarus 0.9.11 (svn revisión 8443) o superior.
    • sqlite runtime library 3.2.1 or higer (get it from www.sqlite.org)

Antes de iniciar un proyecto Lazarus, asegúrate de que:

  • La librería de sqlite está en cualquiera de estos dos sitios:
    • En la PATH del sistema.
    • En el directorio donde se genera el ejecutable (del proyecto actual) - esta opción puede que funcione solamente bajo Windows.
  • Bajo Linux, sitúa cmem como primera unit en la claúsula uses del programa principal.
    • En Debian, Ubuntu y otras distros basadas en Debian, en orden a contruir el IDE de Lazarus debes instalar los paquetes libsqlite-dev/libsqlite3-dev, no solamente sqlite/sqlite3 (Esto también se aplica a OpenSuSe).

Como utilizar (utilización básica)

   Instalar el paquete que se encuentra en el directorio /components/sqlite (ver instrucciones aquí)

   En tiempo de diseño, establecer las siguientes propiedades:

  • FileName: trayecto al fichero sqlite [requerido]
  • TableName: nombre de la table utilizado en la sentencia sql [requerido]
  • SQL: a SQL select statement [opcional]
  • SaveOnClose: el valor por defecto es false, lo cual significa que los cambios no se guardan. Se puede cambiar a true. [opcional]
  • Active: necesita ser establecido en tiempo de diseño o al comienzo del programa. [requerido]

Creando una tabla (Dataset)

   Haz doble click en el icono del componenete o utiliza el elemento 'Create Table' de menú emergente que aparece cuando se cliquea el pulsador derecho del ratón.    Se mostrará un editor de tabla auto-explicable.

   Estos son todos los tipos soportados por TSqliteDataset y TSqlite3Dataset:

  • Integer
  • AutoInc
  • String
  • Memo
  • Bool
  • Float
  • Word
  • DateTime
  • Date
  • Time
  • LargeInt
  • Currency

Recabando los datos

   Después de crear la tabla o con una tabla creada previamente, abrir el dataset con el método Open.

   Si la propiedad SQL no se ha establecido a un valor entonces se recogen todos los registros de todos los campos, lo mismo que si se establece SQL a:

 SQL := 'Select * from TABLENAME';

Applying changes to the underlying datafile

   Para utilizar la función ApplyUpdates, el dataset debe contener al menos un campo que cumpla completamente los requerimientos de clave primaria (Primary Key) (los valores deben ser por tanto ÚNICOS y distintos de NULL)

   Es posible hacer esto de dos maneras:

  • Establecer la propiedad de la clave primaria (PrimaryKey) con el nombre del campo de la clave primaria (Primary Key).
  • Añadir un campo autoincrementable (AutoInc) (Esto es lo más fácil dado que TSqliteDataSet automáticamente lo maneja como una clave primaria (Primary Key)).

   Si se establece una de las dos condiciones, entonces simplemente llamar a

ApplyUpdates;

   

Note-icon.png

Nota: Si se establecen ambas condiciones, el campo correspondiente a la clave primaria (PrimaryKey) se utiliza para aplicar las actualizacioness.

   

Note-icon.png

Nota: Establecer como PrimaryKey un campo que no es de este tipo conllevará pérdida de datos si se llama a ApplyUpdates, por tanto hay que asegurarse de que el campo escogido no contenga NULL y que los valores sean únicos sin repetición.

Master/detail example

   AUn ejemplo de implementación master/detail (customer/orders) using locate can be found here: TSqlite3 Master Detail Example. Implementing master/detail with locate se puee hacer también en SQLDB y probablemente en Zeos.

Remarks

  • A pesar de haberse testeado con 10.000 registros y haber funcionado bien, hay que tener en cuenta que TSqliteDataset mantiene todos los datos en memoria, por tanto recuerda que hay que recabar solamente los datos necesarios (especialmente con campos tipo Memo).
  • El mismo fichero de datos (Filename property) puede albergar varias tablas/conjuntos de datos (datasets).
  • Se pueden crear simultaneamente varios conjuntos de datos (diferentes combinaciones de campos) utilizando la misma tabla.
  • Es posible filtrar los datos mediante el uso de sentencias WHERE de SQL, cerrando y reabriendo el dataset (o llamando al método RefetchData ). En este caso el orden y número de campos deben permanecer igual.
  • Es posible además utilizar complejas sentencias SQL recurriendo a los alias, joins, views y tablas múltiples (recuerda que deben residir en el mismo fichero de datos) pero en este caso ApplyUpdates no funcionará. Si alguien necesita utilizar consultas complejas y aplicar los cambios al fichero de datos, envíame un correo y te daré algunas sugerencias de como hacerlo.
  • Estableciendo el nombre de fichero a un fichero de datos sqlite no creado por el TSqliteDataset y abriéndolo está permitido pero algunos campos no tendrán detectado correctamente el tipo de campo. Estos serán tratados como campos String.


   Se pueden econtrar ejemplo genéricos en el directorio SVN fpc/fcl-db/src/sqlite.

See also