Sign in Join
Wiki copytemporary

SQLite/es

English · Español · Français · Polski · Русский · 中文（中国） · 日本語

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.

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:

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:

   Desventajas:

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

Por ejemplo bajo Linux:

pascal
 SQLiteLibraryName:='./sqlite3.so';

o bien bajo Windows:

pascal
 SQLiteLibraryName:='./sqlite3.dll';
TSQLQuery
sql
 SELECT * FROM foo
sql
 SELECT campo1, campo2, campo5 FROM foo where campo3 = campo4

.

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)

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.

        // 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

pascal
// 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

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;

// 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

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

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;

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:)

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.

Tips

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

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

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:

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:

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:

pascal
 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:

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

pascal
ApplyUpdates;

   

   

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

   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

From the Free Pascal wiki, page "SQLite/es", as it stood on 2023-12-07 after a change by Iskraelectrica, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's Licensing page. If you have concerns about this material, message the admin.

Revision 2, last changed by WikiArchive .

Talk about this page

Nobody has opened a bench for this page yet. Sign in to open one.