Difference between revisions of "SQLite/es"

From Lazarus wiki
Jump to navigationJump to search
 
(categorización)
 
(74 intermediate revisions by 7 users not shown)
Line 1: Line 1:
 +
{{SQLite}}
 +
 +
{{Infobox databases}}
 +
[[category:Castellano]][[category:Español]]
 +
 
== Soporte para SQLite en FPC/Lazarus ==
 
== Soporte para SQLite en FPC/Lazarus ==
SQLite is an embedded (non-server) single-user database that can be used in FPC and Lazarus applications. Various drivers can be used to access SQLite from FPC/Lazarus programs.
 
All drivers do need the SQLite library/dll in the project directory and executable directory (and distributed with your executable) in order to work.
 
  
=== Built-in SQLDB ===
+
   SQLite es una base de datos embebida (sin servidor) monousuario que puede ser utilizada en aplicaciones FPC y Lazarus. Se pueden utilizar varios controladores para el acceso a SQLite desde programas FPC/Lazarus.
FPC/Lazarus offers the built-in SQLDB components that include support for SQLite databases (''TSQLite3Connection''), which allow you to e.g. create GUIs with database components such as dbgrids. The advantage of using SQLDB is that it is fairly easy to change to a different databse such as Firebird or PostgreSQL without changing your program too much.
+
 
See below for details.
+
   Es necesario que dichos controladores 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), bien en un directorio incluido en la variable PATH del sistema.
 +
 
 +
=== 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.
 +
 
 +
[[Image:sqldbcomponents.png | center]]
 +
 
 +
Win64: por favor ver las advertencias [[Windows Programming Tips#FPC 2.6.x/Lazarus warning|aquí]] sobre no usar ciertas versiones de  FPC/Lazarus Win64.
 +
 
 +
=== Acceso directo a SQLite (Producto Comercial) ===
 +
 
 +
   Puedes utilizar una forma sencilla 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, macOS, iOS, Android, Linux y FreeBSD tanto para plataformas de 32 como de 64 bits. LiteDAC 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.
 +
 
 +
==== Soporte para Spatialite ====
 +
 
 +
   Spatialite son extensiones GIS (Geographic Information System) para SQLite que se pueden utilizar dentro de SQLDB. Ver [[Spatialite]].
  
==== Spatialite support ====
+
==== Soporta de encriptación para SQLite  ====
Spatialite are GIS extensions to SQLite which you can use from within SQLDB. See [[Spatialite]].
 
  
==== Support for SQLite encryption ====
+
   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 clave de encriptación.
In recent FPC versions (implemented March 2012), SQLDB included support for some extended versions of SQLite3 which encrypt the SQLite database file using the AES algorithm. Use the password property to set the encryption key.  
 
  
Examples:  
+
   Ejemplos:  
  
- [http://sqlcipher.net/ SQLCipher]: open source, e.g. Windows binaries not for free (you have to compile them yourself)
+
*[http://sqlcipher.net/ SQLCipher]: código abierto (open source), e.g. binarios Windows no disponibles sin coste, no son libres (tienes que compilarlos por ti mismo).
  
- [http://system.data.sqlite.org/ System.Data.SQLite]: open source, Windows (32, 64, CE) binaries available, download e.g one of the Precompiled Binaries and rename SQLite.Interop.dll to sqlite3.dll (if you're using the Statically Linked ones, presumably you need to rename System.Data.SQLite.DLL to sqlite3.dll)
+
*[http://system.data.sqlite.org 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)
  
- [http://wxcode.sourceforge.net/docs/wxsqlite3/ wxSQLite3]: open source, some binaries for Linux available (ex: https://launchpad.net/ubuntu/oneiric/+package/libwxsqlite3-2.8-0)
+
*[http://wxcode.sourceforge.net/docs/wxsqlite3 wxSQLite3]: open source, disponibles algunos binarios para Linux (ex: https://launchpad.net/ubuntu/oneiric/+package/libwxsqlite3-2.8-0)
  
 
=== Zeos ===
 
=== Zeos ===
[http://zeos.firmos.at/portal.php Zeos]
+
 
 +
También se puede utilizar el paquete Zeos con bases de datos SQLite con FreePascal / Lazarus.
 +
 
 +
*[http://zeos.firmos.at Zeos]
  
 
=== SQLitePass ===
 
=== SQLitePass ===
[http://source.online.free.fr/ SqlitePass] components. Status: unknown.
 
  
 +
*Componentes [http://source.online.free.fr/ SqlitePass]. Estado: desconocido.
 +
 +
=== TSQLite3Dataset y TSQLiteDataset ===
 +
 +
   Existen paquetes diferenciados tales como TSQLiteDataset (unidad sqliteds) y TSQLite3Dataset (unidad 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 [http://sqlite4fpc.yolasite.com/ sitio de sqlite4fpc].
 +
 +
   TSqliteDataset y TSqlite3Dataset son descendientes de TDataset que acceden, respectivamente a bases de datos SQLite 2.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 controladores/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.
 +
 +
{{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|SqlDBHowto/es]] 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/es]].
 +
 +
   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.
 +
 +
{{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:
 +
 +
<syntaxhighlight lang=pascal>
 +
SQLiteLibraryName:='./sqlite3.so';
 +
</syntaxhighlight>
 +
 +
o bien bajo Windows:
 +
 +
<syntaxhighlight lang=pascal>
 +
SQLiteLibraryName:='./sqlite3.dll';
 +
</syntaxhighlight>
 +
 +
=====TSQLQuery=====
 +
 +
* SQL: Establécelo a una simple consulta de selección sobre cualquiera de tus tablas. Por ejemplo, si tienes una tabla llamada 'foo' y necesitas que este dataset represente a dicha tabla entonces utiliza lo siguiente:
 +
 +
 +
<syntaxhighlight lang="SQL"> SELECT * FROM foo</syntaxhighlight>
 +
 +
 +
* 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 terminemos de añadir todos los campos (ver más abajo) establecemos transaction y connection 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:
 +
 +
 +
<syntaxhighlight lang="SQL"> SELECT campo1, campo2, campo5 FROM foo where campo3 = campo4</syntaxhighlight>.
 +
 +
 +
* En el código hay que lamar a SQLQuery.ApplyUpdates y SQLTransaction.Commit (o CommitRetaining para que nuestros controles visuales sigan mostrando nuestros datos), 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 
 +
 +
 +
** asegurate de que ProviderFlags para el campo lookup se encuentra completamente vacío de forma que no trate de utilizar su nombre en una consulta update. El campo lookup por si mismo no es un campo de datos, únicamente actúa sobre el valor de otro campo, el correspondiente campo llave, y únicamente se utilizará posteriormente este campo llave en las consultas update. Puedes establecer el campo key a oculto porque lo habitual es que no necestes que se muestre en tu DBGrid pero si que necesta estar definido.
 +
 +
 +
** LookupCache debe establecerse a True. A fecga de escribir esto por alguna razón el campo lookup no muestran anything otherwise (pero todavía funciona) y extrañamente lo opuesto es el caso cuando se trabaja con TSQLite3Dataset u otros componentes TXXXDataset, aquí deben establecerse a False. No estoy todavía seguro de como es el comportamiento adecuado o de si se trata de un error. Si tienes los ProviderFlags de todos tus campos establecidos correctamente entonces debería ser posible crear el SQL que necesitemos sobre la marcha. Para más detalles sobre InsertSQL, UpdateSQL y DeleteSQL, ver [[Working_With_TSQLQuery#TSQLQuery.InsertSQL.2C_TSQLQuery.UpdateSQL_and_TSQLQuery.DeleteSQL:_Basic_Use_of_Parameters/es]].
 +
 +
 +
Después de tener todo lo anteriormente configurado correctamente ya se debería poder utilizar TSQLQuery como con cualquier otro TDataSet, tanto manipulando los datos mediante programación o emplaando un TDataSource en el formulario, conectándolo con TSQLQuery y entonces utilizando controles como TDBGrid, etc.
 +
 +
===== Creando una Base de Datos =====
 +
 +
&nbsp;&nbsp;&nbsp;El método [http://www.freepascal.org/docs-html/fcl/sqldb/tsqlconnection.createdb.html TSQLite3Connection.CreateDB] heredados de la clase padre actulamente no hace nada; para crear una base de datos, si es que no existe una ya, simplemente tiene que escribir la tabla de datos de forma similar a la del siguiente ejemplo:
 +
 +
 +
&nbsp;&nbsp;&nbsp;(Código estraido del ejemplo ''sqlite_encryption_pragma'' que se incluye a partir de Lazarus 1.3)
 +
<syntaxhighlight lang=pascal>
 +
var
 +
  newFile : Boolean;
 +
begin
 +
 +
  SQLite3Connection1.Close; // Asegura que connection está cerrado al inicio (al ser una base de datos monousuario).
 +
 +
  try
 +
    // Ya que estamos creando la base de datos por primera vez,
 +
    // chequeamos opr si ya existiese ya que nos daría error si es así.
 +
 +
    newFile := not FileExists(SQLite3Connection1.DatabaseName);
 +
 +
    if newFile then
 +
    begin
 +
      // Crear la base de datos y las tablas.
 +
      try
 +
        SQLite3Connection1.Open;  // Abrimos la conexión
 +
        SQLTransaction1.Active := true; // Establecemos activa la transacción.
  
=== TSQLite3Dataset and TSQLiteDataset ===
+
        // Aquí es donde establecemos una tabla llamada "DATA" dentro de la base de datos con ExecuteDirect.
There are also separate TSQLiteDataset and TSQLite3Dataset packages; see below for a description on how to use them. Visit the [http://sqlite4fpc.yolasite.com/ sqlite4fpc homepage] to find the API reference and more tutorials.
 
  
TSqliteDataset and TSqlite3Dataset are TDataset descendants that access, respectively, 2.8.x and 3.x.x sqlite databases. For new projects, you would presumably use TSQlite3Dataset as SQLite 3.x is the current version.
+
        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);');
  
Below is a list of the principal advantages and disadvantages compared to other FPC/Lazarus SQLite drivers/access methods:
+
        // En este caso crea la tabla Data con id como llave primaria, que no admite entradas NULL y que se autoincrementa.
 +
        // Crea 3 campos adicionales que no admite entrada NULL uno de tipo DateTime otro dos 128 caracteres.
  
Advantages:
+
        // Creando un índice UNICO basado en id en la tabla DATA.
  
* Flexible: programmers can choose to use or not to use the SQL language, allowing them to work with simple table layouts or any complex layout that SQL/sqlite allows
+
        SQLite3Connection1.ExecuteDirect('CREATE UNIQUE INDEX "Data_id_idx" ON "DATA"( "id" );');
  
Disadvantages:
+
        SQLTransaction1.Commit; // Enviamos y hacemos efectivo lo anterior.
  
* Changing to other databases is more difficult than if you use the SQLDB or Zeos components
+
        ShowMessage('Base de datos Creada Correctamente.');
 +
      except
 +
        ShowMessage('No se ha podido crear la nueva Base de Datos');
 +
      end;
 +
    end;
 +
  except
 +
    ShowMessage('No ha sido posible chequear si el fichero de Base de Datos existe');
 +
  end;
 +
end;</syntaxhighlight>
  
{{Note|Given the above, many users will use SQLDB or Zeos due to the advantages unless they need lower-level access to the SQLite library}}
+
=== Creando collations definidos por el usuario ===
  
== Using the SQLdb components with SQLite ==
+
<syntaxhighlight lang=pascal>
These instructions are focused on SQLDB (the TSQLite3Connection) specifics for SQLite. For a general overview, have a look at [[SqlDBHowto|SqlDBHowto]] which has some useful information about the SQLdb components.
+
// utf8 case-sensitive compare callback function
 +
function UTF8xCompare(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
 +
var S1, S2: AnsiString;
 +
begin
 +
  SetString(S1, data1, len1);
 +
  SetString(S2, data2, len2);
 +
  Result := UnicodeCompareStr(UTF8Decode(S1), UTF8Decode(S2));
 +
end;
  
See [[SQLdb_Tutorial1]] for a tutorial on creating a GUI database-enabled program that is written for SQLite/SQLDB (as well as for Firebird/SQLDB, PostgreSQL/SQLDB, basically any RDBMS SQLDB supports).
+
// utf8 case-insensitive compare callback function
 +
function UTF8xCompare_CI(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
 +
var S1, S2: AnsiString;
 +
begin
 +
  SetString(S1, data1, len1);
 +
  SetString(S2, data2, len2);
 +
  Result := UnicodeCompareText(UTF8Decode(S1), UTF8Decode(S2));
 +
end;
  
We will use a combination of three components from the Lazarus SQLdb tab: TSQLite3Connection, TSQLTransaction and TSQLQuery. The TSQLQuery acts as our TDataset; in the simplest case it just represents one of our tables. For the sake of simplicity: make sure you already have an existing SQLite database file and don't need to create a new one now.
+
// register collation using SQLite3 API (requires sqlite3dyn unit):
TSQLite3Connection can be found in the ''sqlite3conn'' unit, if you want to declare it yourself or are working in FreePascal.
+
sqlite3_create_collation(SQLite3.Handle, 'UTF8_CI', SQLITE_UTF8, nil, @UTF8xCompare_CI);
 +
// or using method of TSQLite3Connection:
 +
CreateCollation('UTF8_CI',1,nil,@UTF8xCompare_CI); 
  
The three components are connected with each other as usual: In the TSQLQuery set the properties Database and Transaction, in the TSQLTransaction set the property Database. There is not much to do in the Transaction and Connection components, most of the interesting things will be done in the TSQLQuery. Configure the components as follows:
+
// now we can use case-insensitive comparison in SQL like:
 +
// SELECT * FORM table1 WHERE column1 COLLATE UTF8_CI = 'á'
 +
 
 +
// but this does not work for LIKE operator
 +
// in order to support also LIKE operator we must overload default LIKE function using sqlite3_create_function()
 +
// http://www.sqlite.org/lang_corefunc.html#like
 +
</syntaxhighlight>
  
TSQLite3Connection:
+
=== Creando funciones definidas por el usuario ===
* DatabaseName: Set this property to the file name (absolute path!) of your SQLite file. Unfortunately, you cannot simply use a relative path that works unchanged at designtime and at runtime ***is this still true? 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.
+
<syntaxhighlight lang=pascal>
 +
// ejemplo de sobrecarga de la funcion LOWER() de SQLite con una función creada por el usuario
 +
procedure UTF8xLower(ctx: psqlite3_context; N: cint; V: ppsqlite3_value); cdecl;
 +
var S: AnsiString;
 +
begin
 +
  SetString(S, sqlite3_value_text(V[0]), sqlite3_value_bytes(V[0]));
 +
  S := UTF8Encode(AnsiLowerCase(UTF8Decode(S)));
 +
  sqlite3_result_text(ctx, PAnsiChar(S), Length(S), sqlite3_destructor_type(SQLITE_TRANSIENT));
 +
end;
  
Note: To set the full library path (if you place your sqlite dll/so/dylib in a place where the OS won't find it, like the application directory on Linux/OSX), you can set the ''SQLiteLibraryName'' property (BEFORE any connection is established e.g. in the OnCreate event of the main form), like this:
+
// registro de la nueva funccion LOWER() usando la API de SQLite3 (requiere la unidad sqlite3dyn):
<syntaxhighlight>
+
sqlite3_create_function(SQLite3.Handle, 'lower', 1, SQLITE_UTF8 or SQLITE_DETERMINISTIC, nil, @UTF8xLower, nil, nil);
SQLiteLibraryName:='./sqlite3.so';
 
 
</syntaxhighlight>
 
</syntaxhighlight>
  
TSQLQuery:
+
=== SQLite3 y Fechas ===
* SQL: Set it to some simple select query on one of your tables. For example, if you have a table 'foo' and want this dataset to represent this table then just use the following: <syntaxhighlight lang="SQL">SELECT * FROM foo</syntaxhighlight>
+
 
 +
* SQLite 3 no almacena las fechas como un valor especial DateTime. Las puede almacenar como strings, doubles o integers - ver http://www.sqlite.org/datatype3.html#datetime.
 +
 
 +
* En cadenas el separador de fecha es '-' acorde al estandar SQL ISO 8601. POr tanto, si realizas un INSERT utilizando la función interna DATE, entonces almacenará algo similar a 'YYYY-MM-DD'.
 +
 
 +
* La lectura de un valor DateTime puede causar problemas para DataSets si se almacena como cadena: el cualificador .AsDateTime puede 'pero puede resolverse utilizando algo como <tt>strftime(''%d/%m/%Y'',recdate) AS sqlite3recdate</tt> en tu sentencia SQL SELECT, lo cual fuerza a SQLite3 a retornar el campo date en un formato específico. (el formato de cadena %d/%m/%d  corresponde al formato de tu fecha local .AsDateTime que pueda ser entendida) ''' ==> Por favor abrir un reporte de error con una aplicación de ejemplo demostrativo si alguien detecta un error en este aspecto '''
 +
 
 +
* Cuando se comparar fechas almacenadas en cadenas (utilizando por ejemplo la función BETWEEN)recordar que la comparación deberá ser siempre una comparación de  <b>string</b>,  y dependerá por tanto de como se haya almacenado el valor del dato.
 +
 
 +
==== Valores por defecto para hora local en lugar de UTC ====
 +
 
 +
CURRENT_TIME, CURRENT_DATE y CURRENT_TIMESTAMP retornan la fecha y/u hora UTC. Para fecha y/o hora local podemos utilizar:
 +
 
 +
  DEFAULT (datetime('now','localtime')) para valores de fecha-hora (datetime) formateados como YYYY-MM-DD HH:MM:SS
 +
  DEFAULT (date('now','localtime')) para valores de fecha formateada como YYYY-MM-DD
 +
  DEFAULT (time('now','localtime')) para valores de hora formateados como HH:MM:SS
 +
 
 +
=== Resolución de problemas sobre SQLDB y SQLite ===
 +
 
 +
* Tener presente que para trabajar en tiempo de diseño (campos, etc) Lazarus debe poder encontrar sqlite3.dll.
 +
* Lo mismo cuenta para el nombre de fichero de la base de datos. Simpre utlizar trayectos absolutos si se utilizan componentes para extraer, e.g nombres de campos en tiempo de diseño. De otra manera la el IDE, al realizar la conexión, creará un fichero vacío en el directorio. En caso de problemas, comprobar si el directorio utilizado contiene una copia de tamaño 0 bytes para el fichero de base de datos.
 +
 
 +
* Si se tiene una  relación maestro/detalle será necesario refrescar el ''dataset'' maestro después de realizar cada mandato ''insert'' para obtener el valor del campo de la clave ajena para el ''dataset'' detalle. Se puede realizar en el evento AfterPost del ''dataset'' maestro, usando RefreshADatasetAfterInsert para ello:
  
* Active: Set this to True from within the IDE to test whether it is all set up correctly. This will also automatically activate the transaction and the connection objects. If you receive an error then either the DatabaseName of the connection is not correct or the SQL query is wrong. Later,  when we are done adding the fields (see below) set them all to inactive again, we don't want the IDE to lock the SQLite database (single user!) when testing the application.
 
  
* ''Probably not necessary for proper operation - will need to be checked (June 2012)'' Now we can add Fields to our TSQLQuery. While the components are still set to active do a right click and "edit fields...". Click the "+" button and add fields. It will list all fields your SQL query returned. Add every field you will need, you can also add lookup fields here; in this case just make sure you have already defined all needed fields in the other datasets before you start adding lookup fields that refer to them. If your table has many columns and you don't need them all you can just leave them out, you can also make your SQL a bit more specific.  
+
<syntaxhighlight lang=pascal>
 +
interface
 +
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
 +
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload; 
 +
 +
implementation
 +
 +
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);
 +
// Este procedimineto refres ca un conjunto de datos (un 'dataset') y sitúa el cursor en el último registro
 +
// Se usa cuando el no se sabe si el 'dataset' está ordenado por una clave primaria autoeincrementada
 +
var
 +
  vLastID: Integer;
 +
  vUpdateStatus : TUpdateStatus;
 +
begin
 +
  vUpdateStatus := pDataset.UpdateStatus;
 +
  //Get last inserted ID in the database
 +
  pDataset.ApplyUpdates;
 +
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
 +
  // Ahora vuelve a la fila respectiva.
 +
  if vUpdateStatus = usInserted then begin
 +
    pDataset.Refresh;
 +
    // Refrescar y vuelve a la fila respectiva.
 +
    pDataset.Locate(pKeyField,vLastID,[]);
 +
  end;
 +
end;
 +
 +
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
 +
// Este procedimineto refres ca un conjunto de datos (un 'dataset') y sitúa el cursor en el último registro
 +
// Se usa cuando el 'dataset' está ordenado por una clave primaria autoeincrementada
 +
var
 +
  vLastID: Integer;
 +
  vUpdateStatus : TUpdateStatus;
 +
begin
 +
  vUpdateStatus := pDataset.UpdateStatus;
 +
  pDataset.ApplyUpdates;
 +
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
 +
  if vUpdateStatus = usInserted then begin
 +
    pDataset.Refresh;
 +
    //Dangerous!
 +
    pDataSet.Last;
 +
  end;
 +
end;
  
* In your code you need to call SQLQuery.ApplyUpdates and SQLTransaction.Commit, TSQLQuery.AfterPost and AfterInsert events are a good place for this when using it with data aware controls but of course you can also postpone these calls to a later time. If you don't call them, the database will not be updated.
+
procedure TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);
 +
begin
 +
  RefreshADatasetAfterInsert(Dataset as TSQLQuery); //Si el conjunto de datos está ordenado por la clave primaria
 +
end; 
  
* "Database is locked": The IDE might still be locking the database (SQLite is a single user database), you probably forgot to set the components to inactive and disconnected again after you were done defining all the fields of your TSQLQuery objects. Use the Form's OnCreate event to set the path and activate the objects at runtime only. Most of the things you set in the TSQLQuery from within the IDE don't require (and some don't even allow) them to be active at design time, the only exception is defining the fields where it wants to read the table design, so inactive at design time should be the normal state.
+
procedure TDataModule1.SQLQuery2AfterPost(DataSet: TDataSet);
 +
begin
 +
  RefreshADatasetAfterInsert(Dataset as TSQLQuery, 'ID'); // Si no se está seguro de si los datos están ordenados por la clave primaria
 +
end;
 +
</syntaxhighlight>
  
* Your tables should all have a primary key and you must make sure that the corresponding field has pfInKey and nothing else in its PoviderFlags (these flags control how and where the field is used when automatically constructing the update and delete queries).
+
=== Vacuum y otras operaciones que deben realizarse fuera de una transacción ===
  
* If you are using lookup fields
+
SQLdb parece requerir siempre una transacción, pero algunas operaciones tales como Pragma y Vacuum se deben realizar fuera de una transacción. Para ello hay que finalizar la transacción, ejecutar lo que se necesite y volver a iniciar la transacción nuevamente (de modo que SQLdb no quede confundido:)
** 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.
 
  
* Usually with simple tables you won't need to set any of the InsertSQL, UpdateSQL and DeleteSQL properties, just leave them empty. If you have the ProviderFlags of all your fields set correctly it should be able to create the needed SQL on the fly. For more details on InsertSQL, UpdateSQL and DeleteSQL, see [[Working_With_TSQLQuery#TSQLQuery.InsertSQL.2C_TSQLQuery.UpdateSQL_and_TSQLQuery.DeleteSQL:_Basic_Use_of_Parameters]].
 
  
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.
+
<syntaxhighlight lang=pascal>
 +
  // commit cualquier operación pendiente y utilizar una sqlconnection "fresca".
 +
  Conn.ExecuteDirect('End Transaction');  // Finalizar la transacción iniciada por SQLdb
 +
  Conn.ExecuteDirect('Vacuum'); // Realizar el Vacuum.
 +
  Conn.ExecuteDirect('Begin Transaction'); // Comenzar una nueva transacción para utilizar con SQLdb.
 +
</syntaxhighlight>
  
 
=== Tips ===
 
=== Tips ===
* Use the TSQLite3Connection.CreateDB procedure to create a new SQLite database.
 
  
== Using TSQLite3Dataset ==
+
* Utiliza el procedimiento TSQLite3Connection.CreateDB para crear una nueva base de datos SQLite.
 +
 
 +
== Utilizando TSQLite3Dataset ==
  
This section details how to use the TSQLite3Dataset and TSQLite3Dataset components to access SQlite databases.
+
&nbsp;&nbsp;&nbsp;Esta sección detalla como utilizar los componentes TSQLite3Dataset y TSQLite3Dataset para acceder a bases de datos SQlite.
by Luiz Américo
+
&nbsp;&nbsp;&nbsp;por Luiz Américo
 
luizmed(at)oi(dot)com(dot)br
 
luizmed(at)oi(dot)com(dot)br
  
  
====Requirements====
+
====Requerimientos====
  
* For sqlite2 databases (legacy):
+
* Para bases de datos sqlite2 (heredado):
 
** FPC 2.0.0 or higher
 
** FPC 2.0.0 or higher
** Lazarus 0.9.10 or higher
+
** Lazarus 0.9.10 o superior.
** SQLite runtime library 2.8.15 or above*
+
** SQLite versión 2.8.15 o superior*
  
* Sqlite2 is not maintained anymore and the binary file cannot be found in the sqlite site
+
* SQLite2 ya no está en fase de mantenimiento y el fichero binario ya no se encuentra en el sitio de sqlite.
  
* For sqlite3 databases:
+
* Para bases  e datos SQLite3:
** FPC 2.0.2 or higher
+
** FPC 2.0.2 o superior.
** Lazarus 0.9.11 (svn revision 8443) or higher
+
** Lazarus 0.9.11 (svn revisión 8443) o superior.
** sqlite runtime library 3.2.1 or higer (get it from [http://www.sqlite.org www.sqlite.org])
+
** SQLite  versión 3.2.1 o superior (mira en [http://www.sqlite.org www.sqlite.org])
  
'''Before initiating a lazarus project, ensure that:'''
+
'''Antes de iniciar un proyecto Lazarus, asegúrate de que:'''
* the sqlite library is either 
+
* La librería de SQLite está en cualquiera de estos dos sitios:
** in the system PATH or
+
** En la variable PATH del sistema.
** in the executable output directory and Lazarus (or current project) directories - this option might work on Windows only
+
** En el directorio donde se genera el ejecutable (del proyecto actual) - esta opción puede que funcione solamente bajo Windows.
* under Linux, put cmem as the first unit in uses clause of the main program
+
* Bajo Linux, sitúa cmem como primera unidad en la claúsula uses del programa principal.
** In Debian, Ubuntu and other Debian-like distros, in order to build Lazarus IDE you must install the packages libsqlite-dev/libsqlite3-dev, not only sqlite/sqlite3 (Also applies to OpenSuSe)
+
** 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).
  
====How To Use (Basic Usage)====
+
====Como utilizar (utilización básica)====
  
Install the package found at /components/sqlite directory (see instructions [[Install_Packages|here]])
+
&nbsp;&nbsp;&nbsp;Instalar el paquete que se encuentra en el directorio /components/sqlite (ver instrucciones [[Install_Packages/es|aquí]])
  
At design time, set the following properties:
+
&nbsp;&nbsp;&nbsp;En tiempo de diseño, establecer las siguientes propiedades:
  
* FileName: path of the sqlite file [required]  
+
* FileName: trayecto al fichero sqlite [requerido]  
* TableName: name of the table used in the sql statement [required]  
+
* TableName: nombre de la table utilizado en la sentencia sql [requerido]  
* SQL: a SQL select statement [optional]  
+
* SQL: a SQL select statement [opcional]  
* SaveOnClose: The default value is false, which means that changes are not saved. One can change it to true. [optional]
+
* SaveOnClose: el valor por defecto es false, lo cual significa que los cambios no se guardan. Se puede cambiar a true. [opcional]
* Active: Needs to be set at design time or at program startup. [required]
+
* Active: necesita ser establecido en tiempo de diseño o al comienzo del programa. [requerido]
  
'''Creating a Table (Dataset)'''
+
'''Creando una tabla (Dataset)'''
  
Double-click the component icon or use the 'Create Table' item of the popup menu that appears when clicking the right mouse button.
+
&nbsp;&nbsp;&nbsp;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.
A simple self-explaining table editor will be shown.
+
&nbsp;&nbsp;&nbsp;Se mostrará un editor de tabla auto-explicable.
  
Here are all field types supported by TSqliteDataset and TSqlite3Dataset:  
+
&nbsp;&nbsp;&nbsp;Estos son todos los tipos soportados por TSqliteDataset y TSqlite3Dataset:  
 
    
 
    
 
* Integer
 
* Integer
Line 147: Line 395:
 
* Currency
 
* Currency
 
    
 
    
'''Retrieving the data'''
+
'''Recabando los datos'''
  
After creating the table or with a previously created Table, open the dataset with the Open method.
+
&nbsp;&nbsp;&nbsp;Después de crear la tabla o con una tabla creada previamente, abrir el dataset con el método Open.
If the SQL property was not set then all records from all fields will be retrieved, the same if you set the SQL to:
 
  
<syntaxhighlight>SQL := 'Select * from TABLENAME';</syntaxhighlight>
+
&nbsp;&nbsp;&nbsp;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:
 +
 
 +
<syntaxhighlight lang=pascal> SQL := 'Select * from TABLENAME';</syntaxhighlight>
  
 
'''Applying changes to the underlying datafile'''
 
'''Applying changes to the underlying datafile'''
  
To use the ApplyUpdates function, the dataset must contain at least one field that fulfills the requirements for a Primary Key (values must be UNIQUE and not NULL)
+
&nbsp;&nbsp;&nbsp;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)
  
It's possible to do that in two ways:
+
&nbsp;&nbsp;&nbsp;Es posible hacer esto de dos maneras:
  
* Set PrimaryKey property to the name of a Primary Key field
+
* Establecer la propiedad de la clave primaria (PrimaryKey) con el nombre del campo de la clave primaria (Primary Key).
* Add an AutoInc field (This is easier since the TSqliteDataSet automatically handles it as a 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)).
  
If one of the two conditions is set, just call
+
&nbsp;&nbsp;&nbsp;Si se establece una de las dos condiciones, entonces simplemente llamar a <syntaxhighlight lang=pascal>ApplyUpdates;</syntaxhighlight>
 
 
<syntaxhighlight>ApplyUpdates;</syntaxhighlight>
 
  
{{Note|If both conditions are set, the field corresponding to PrimaryKey is used to apply the updates.}}
+
&nbsp;&nbsp;&nbsp;{{Nota| Si se establecen ambas condiciones, el campo correspondiente a la clave primaria (PrimaryKey) se utiliza para aplicar las actualizacioness.}}
  
{{Note|Setting PrimaryKey to a field that is not a Primary Key will lead to loss of data if ApplyUpdates is called, so ensure that the chosen field contains not Null and Unique values before using it.}}
+
&nbsp;&nbsp;&nbsp;{{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=====
 
=====Master/detail example=====
An example of implementing master/detail (customer/orders) using locate can be found here: [[TSqlite3 Master Detail Example]]. Implementing master/detail with locate can also be done in SQLDB and probably Zeos.
+
 
 +
&nbsp;&nbsp;&nbsp;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====
 
====Remarks====
  
* Although it has been tested with 10,000 records and worked fine, TSqliteDataset keeps all the data in memory, so remember to retrieve only the necessary data (especially with Memo Fields).
+
* 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).
* The same datafile (Filename property) can host several tables/datasets
+
* El mismo fichero de datos (Filename property) puede albergar varias tablas/conjuntos de datos (datasets).
* Several datasets (different combinations of fields) can be created using the same table simultaneously
+
* Se pueden crear simultaneamente varios conjuntos de datos (diferentes combinaciones de campos) utilizando la misma tabla.
* It's possible to filter the data using WHERE statements in the sql, closing and reopening the dataset (or calling RefetchData method). But in this case, the order and number of fields must remain the same
+
* 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.
* It's also possible to use complex SQL statements using aliases, joins, views in multiple tables (remember that they must reside in the same datafile), but in this case ApplyUpdates won't work. If someone wants to use complex queries and to apply the updates to the datafile, mail me and i will give some hints how to do that
+
* 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.
* Setting filename to a sqlite datafile not created by TSqliteDataset and opening it is allowed but some fields won't have the correct field type detected. These will be treated as string fields.
+
 
 +
* 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.
 +
 
 +
 
 +
&nbsp;&nbsp;&nbsp;Se pueden econtrar ejemplo genéricos en el directorio SVN fpc/fcl-db/src/sqlite.
 +
 
 +
 
 +
==== Utilidades de apoyo ====
 +
 
 +
Como ayuda a la hora de manejar bases de datos SQLite tenemos a nuestra disposición algunas utilidades que nos pueden facilitar el trabajo de su creación / visualización / mantenimiento a la hora de crear un programa, para ello se listan las siguientes:
  
Generic examples can be found at fpc/fcl-db/src/sqlite SVN directory
+
* DB Browser for SQLite: https://github.com/sqlitebrowser/sqlitebrowser
 +
 
 +
* LazSQLX: https://sourceforge.net/directory/os:windows/?q=lazsqlx
  
 
==See also==
 
==See also==
* [[SQLdb_Tutorial1]] Tutorial for Sqlite
+
* [[SQLdb_Tutorial1/es|Tutorial de Sqldb]]  
* [[SqlDBHowto]] General information on SQLDB
+
* [[SqlDBHowto/es|Información General sobre Bases de Datos SQL]]
* [[Working_With_TSQLQuery]] Information on SQLDB's TSQLQuery
+
* [[Working_With_TSQLQuery/es|Información sobre TSQLQuery y Bases de Datos SQL]]
* [http://www.sqlite.org SQLite site]
+
* [http://www.sqlite.org Sitio oficial de SQLite]
 
 
[[Category:Databases]]
 

Latest revision as of 18:10, 7 December 2023

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

Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

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 controladores para el acceso a SQLite desde programas FPC/Lazarus.

   Es necesario que dichos controladores 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), bien en un directorio incluido en la variable PATH del sistema.

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

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

Acceso directo a SQLite (Producto Comercial)

   Puedes utilizar una forma sencilla 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, macOS, iOS, Android, Linux y FreeBSD tanto para plataformas de 32 como de 64 bits. LiteDAC 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.

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 clave de encriptación.

   Ejemplos:

  • SQLCipher: código abierto (open source), e.g. binarios Windows no disponibles sin coste, 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 con bases de datos SQLite con FreePascal / Lazarus.

SQLitePass

TSQLite3Dataset y TSQLiteDataset

   Existen paquetes diferenciados tales como TSQLiteDataset (unidad sqliteds) y TSQLite3Dataset (unidad 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 SQLite 2.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 controladores/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/es 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/es.

   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 tus tablas. Por ejemplo, si tienes una tabla llamada 'foo' y necesitas que este dataset represente a dicha tabla 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 terminemos de añadir todos los campos (ver más abajo) establecemos transaction y connection 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 (o CommitRetaining para que nuestros controles visuales sigan mostrando nuestros datos), 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


    • asegurate de que ProviderFlags para el campo lookup se encuentra completamente vacío de forma que no trate de utilizar su nombre en una consulta update. El campo lookup por si mismo no es un campo de datos, únicamente actúa sobre el valor de otro campo, el correspondiente campo llave, y únicamente se utilizará posteriormente este campo llave en las consultas update. Puedes establecer el campo key a oculto porque lo habitual es que no necestes que se muestre en tu DBGrid pero si que necesta estar definido.


    • LookupCache debe establecerse a True. A fecga de escribir esto por alguna razón el campo lookup no muestran anything otherwise (pero todavía funciona) y extrañamente lo opuesto es el caso cuando se trabaja con TSQLite3Dataset u otros componentes TXXXDataset, aquí deben establecerse a False. No estoy todavía seguro de como es el comportamiento adecuado o de si se trata de un error. Si tienes los ProviderFlags de todos tus campos establecidos correctamente entonces debería ser posible crear el SQL que necesitemos sobre la marcha. Para más detalles sobre InsertSQL, UpdateSQL y DeleteSQL, ver Working_With_TSQLQuery#TSQLQuery.InsertSQL.2C_TSQLQuery.UpdateSQL_and_TSQLQuery.DeleteSQL:_Basic_Use_of_Parameters/es.


Después de tener todo lo anteriormente configurado correctamente ya se debería poder utilizar TSQLQuery como con cualquier otro TDataSet, tanto manipulando los datos mediante programación o emplaando un TDataSource en el formulario, conectándolo con TSQLQuery y entonces utilizando controles como TDBGrid, etc.

Creando una Base de Datos

   El método TSQLite3Connection.CreateDB heredados de la clase padre actulamente no hace nada; para crear una base de datos, si es que no existe una ya, simplemente tiene que escribir la tabla de datos de forma similar a la del siguiente ejemplo:


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

 var
  newFile : Boolean;
 begin

  SQLite3Connection1.Close; // Asegura que connection está cerrado al inicio (al ser una base de datos monousuario).

  try
    // Ya que estamos creando la base de datos por primera vez,
    // chequeamos opr si ya existiese ya que nos daría error si es así.

    newFile := not FileExists(SQLite3Connection1.DatabaseName);

    if newFile then
    begin
      // Crear la base de datos y las tablas.
      try
        SQLite3Connection1.Open;  // Abrimos la conexión
        SQLTransaction1.Active := true; // Establecemos activa la transacción.

        // Aquí es donde establecemos una tabla llamada "DATA" dentro de la base de datos con ExecuteDirect.

        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);');

        // En este caso crea la tabla Data con id como llave primaria, que no admite entradas NULL y que se autoincrementa.
        // Crea 3 campos adicionales que no admite entrada NULL uno de tipo DateTime otro dos 128 caracteres.

        // Creando un índice UNICO basado en id en la tabla DATA.

        SQLite3Connection1.ExecuteDirect('CREATE UNIQUE INDEX "Data_id_idx" ON "DATA"( "id" );');

        SQLTransaction1.Commit; // Enviamos y hacemos efectivo lo anterior.

        ShowMessage('Base de datos Creada Correctamente.');
      except
        ShowMessage('No se ha podido crear la nueva Base de Datos');
      end;
    end;
  except
    ShowMessage('No ha sido posible chequear si el fichero de Base de Datos existe');
  end;
 end;

Creando collations definidos por el usuario

// utf8 case-sensitive compare callback function
function UTF8xCompare(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
var S1, S2: AnsiString;
begin
  SetString(S1, data1, len1);
  SetString(S2, data2, len2);
  Result := UnicodeCompareStr(UTF8Decode(S1), UTF8Decode(S2));
end;

// utf8 case-insensitive compare callback function
function UTF8xCompare_CI(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
var S1, S2: AnsiString;
begin
  SetString(S1, data1, len1);
  SetString(S2, data2, len2);
  Result := UnicodeCompareText(UTF8Decode(S1), UTF8Decode(S2));
end;

// register collation using SQLite3 API (requires sqlite3dyn unit):
sqlite3_create_collation(SQLite3.Handle, 'UTF8_CI', SQLITE_UTF8, nil, @UTF8xCompare_CI);
// or using method of TSQLite3Connection:
CreateCollation('UTF8_CI',1,nil,@UTF8xCompare_CI);  

// now we can use case-insensitive comparison in SQL like:
// SELECT * FORM table1 WHERE column1 COLLATE UTF8_CI = 'á'

// but this does not work for LIKE operator
// in order to support also LIKE operator we must overload default LIKE function using sqlite3_create_function()
// http://www.sqlite.org/lang_corefunc.html#like

Creando funciones definidas por el usuario

// ejemplo de sobrecarga de la funcion LOWER() de SQLite con una función creada por el usuario
procedure UTF8xLower(ctx: psqlite3_context; N: cint; V: ppsqlite3_value); cdecl;
var S: AnsiString;
begin
  SetString(S, sqlite3_value_text(V[0]), sqlite3_value_bytes(V[0]));
  S := UTF8Encode(AnsiLowerCase(UTF8Decode(S)));
  sqlite3_result_text(ctx, PAnsiChar(S), Length(S), sqlite3_destructor_type(SQLITE_TRANSIENT));
end;

// registro de la nueva funccion LOWER() usando la API de SQLite3 (requiere la unidad sqlite3dyn):
sqlite3_create_function(SQLite3.Handle, 'lower', 1, SQLITE_UTF8 or SQLITE_DETERMINISTIC, nil, @UTF8xLower, nil, nil);

SQLite3 y Fechas

  • En cadenas el separador de fecha es '-' acorde al estandar SQL ISO 8601. POr tanto, si realizas un INSERT utilizando la función interna DATE, entonces almacenará algo similar a 'YYYY-MM-DD'.
  • La lectura de un valor DateTime puede causar problemas para DataSets si se almacena como cadena: el cualificador .AsDateTime puede 'pero puede resolverse utilizando algo como strftime(%d/%m/%Y,recdate) AS sqlite3recdate en tu sentencia SQL SELECT, lo cual fuerza a SQLite3 a retornar el campo date en un formato específico. (el formato de cadena %d/%m/%d corresponde al formato de tu fecha local .AsDateTime que pueda ser entendida) ==> Por favor abrir un reporte de error con una aplicación de ejemplo demostrativo si alguien detecta un error en este aspecto
  • Cuando se comparar fechas almacenadas en cadenas (utilizando por ejemplo la función BETWEEN)recordar que la comparación deberá ser siempre una comparación de string, y dependerá por tanto de como se haya almacenado el valor del dato.

Valores por defecto para hora local en lugar de UTC

CURRENT_TIME, CURRENT_DATE y CURRENT_TIMESTAMP retornan la fecha y/u hora UTC. Para fecha y/o hora local podemos utilizar:

 DEFAULT (datetime('now','localtime')) para valores de fecha-hora (datetime) formateados como YYYY-MM-DD HH:MM:SS
 DEFAULT (date('now','localtime')) para valores de fecha formateada como YYYY-MM-DD
 DEFAULT (time('now','localtime')) para valores de hora formateados como HH:MM:SS

Resolución de problemas sobre SQLDB y SQLite

  • Tener presente que para trabajar en tiempo de diseño (campos, etc) Lazarus debe poder encontrar sqlite3.dll.
  • Lo mismo cuenta para el nombre de fichero de la base de datos. Simpre utlizar trayectos absolutos si se utilizan componentes para extraer, e.g nombres de campos en tiempo de diseño. De otra manera la el IDE, al realizar la conexión, creará un fichero vacío en el directorio. En caso de problemas, comprobar si el directorio utilizado contiene una copia de tamaño 0 bytes para el fichero de base de datos.
  • Si se tiene una relación maestro/detalle será necesario refrescar el dataset maestro después de realizar cada mandato insert para obtener el valor del campo de la clave ajena para el dataset detalle. Se puede realizar en el evento AfterPost del dataset maestro, usando RefreshADatasetAfterInsert para ello:


interface
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload;  
 
implementation
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);
// Este procedimineto refres ca un conjunto de datos (un 'dataset') y sitúa el cursor en el último registro
// Se usa cuando el no se sabe si el 'dataset' está ordenado por una clave primaria autoeincrementada
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  //Get last inserted ID in the database
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  // Ahora vuelve a la fila respectiva.
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    // Refrescar y vuelve a la fila respectiva.
    pDataset.Locate(pKeyField,vLastID,[]);
  end;
end;
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
// Este procedimineto refres ca un conjunto de datos (un 'dataset') y sitúa el cursor en el último registro
// Se usa cuando el 'dataset' está ordenado por una clave primaria autoeincrementada
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    //Dangerous!
    pDataSet.Last;
  end;
end;

procedure TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery); //Si el conjunto de datos está ordenado por la clave primaria
end;  

procedure TDataModule1.SQLQuery2AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery, 'ID'); // Si no se está seguro de si los datos están ordenados por la clave primaria
end;

Vacuum y otras operaciones que deben realizarse fuera de una transacción

SQLdb parece requerir siempre una transacción, pero algunas operaciones tales como Pragma y Vacuum se deben realizar fuera de una transacción. Para ello hay que finalizar la transacción, ejecutar lo que se necesite y volver a iniciar la transacción nuevamente (de modo que SQLdb no quede confundido:)


  // commit cualquier operación pendiente y utilizar una sqlconnection "fresca".
  Conn.ExecuteDirect('End Transaction');  // Finalizar la transacción iniciada por SQLdb
  Conn.ExecuteDirect('Vacuum'); // Realizar el Vacuum.
  Conn.ExecuteDirect('Begin Transaction'); // Comenzar una nueva transacción para utilizar con SQLdb.

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 (heredado):
    • FPC 2.0.0 or higher
    • Lazarus 0.9.10 o superior.
    • SQLite versión 2.8.15 o superior*
  • SQLite2 ya no está en fase de mantenimiento y el fichero binario ya no se encuentra en el sitio de sqlite.
  • Para bases e datos SQLite3:
    • FPC 2.0.2 o superior.
    • Lazarus 0.9.11 (svn revisión 8443) o superior.
    • SQLite versión 3.2.1 o superior (mira en 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 variable 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 unidad 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.


Utilidades de apoyo

Como ayuda a la hora de manejar bases de datos SQLite tenemos a nuestra disposición algunas utilidades que nos pueden facilitar el trabajo de su creación / visualización / mantenimiento a la hora de crear un programa, para ello se listan las siguientes:

See also