Editor simple de SQLite.

Son tan solo 200 líneas de código y desde ya, la librería de SQLite. Desde ya hay programas potentes y también portables como SQLite Studio, el cual uso y recomiendo. Volviendo al editor simple, lo hice para un uso muy específico y limitado, conecta a la base de datos que le indiquemos, muestra las tablas, permite ejecutar consultar cuyo resultado se muestra en un TDBGrid el cual cuenta con un par de opciones y ejecutar sentencias de actualizaciones que realiza mediante TZConnection.ExecuteDirect.
También tiene una opción para leer de un archivo un script SQL y luego ejecutarlo.
Creo que puede ser útil para quienes comiencen con SQLite, Lazarus y ZeosLib.

El código: descargar

unit principal;

{$mode objfpc}{$H+}

interface

uses
Classes, SysUtils, db, FileUtil, Forms, Controls, Graphics, Dialogs,
StdCtrls, Buttons, DBGrids, DbCtrls, ZConnection, ZDataset;

type
  TConexion=(Conectado, NoConectado);

type

{ TForm1 }

TForm1 = class(TForm)
  BBorrarMemo: TBitBtn;
  BCerrarConsulta: TBitBtn;
  btnSelBD: TBitBtn;
  btnConectar: TBitBtn;
  BCerrar: TBitBtn;
  btnDesconectar: TBitBtn;
  BExecute: TBitBtn;
  BConsulta: TBitBtn;
  btnLeerArchivo: TBitBtn;
  cbAutoCommit: TCheckBox;
  cbAutoSizeCol: TCheckBox;
  DataSource1: TDataSource;
  DBGrid1: TDBGrid;
  DBNavigator1: TDBNavigator;
  edBaseDeDatos: TEdit;
  edConexion: TEdit;
  Label1: TLabel;
  lbTablas: TListBox;
  Memo1: TMemo;
  OpenDialog1: TOpenDialog;
  ZConnection1: TZConnection;
  ZQuery1: TZQuery;
  procedure BBorrarMemoClick(Sender: TObject);
  procedure BCerrarConsultaClick(Sender: TObject);
  procedure BConsultaClick(Sender: TObject);
  procedure BExecuteClick(Sender: TObject);
  procedure btnLeerArchivoClick(Sender: TObject);
  procedure btnSelBDClick(Sender: TObject);
  procedure btnConectarClick(Sender: TObject);
  procedure BCerrarClick(Sender: TObject);
  procedure btnDesconectarClick(Sender: TObject);
  procedure cbAutoSizeColChange(Sender: TObject);
  procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
  procedure FormCreate(Sender: TObject);
  procedure lbTablasDblClick(Sender: TObject);
private
  Conexion:TConexion;
  procedure MuestroTablas;
  function HayConexion:Boolean;
{ private declarations }
public
{ public declarations }
end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.FormCreate(Sender: TObject);
begin
  Conexion:=NoConectado;
end;

procedure TForm1.btnSelBDClick(Sender: TObject);
begin
  if OpenDialog1.Execute then edBaseDeDatos.Text:= OpenDialog1.FileName;
end;

procedure TForm1.btnLeerArchivoClick(Sender: TObject);
begin
 if OpenDialog1.Execute then Memo1.Lines.LoadFromFile(OpenDialog1.FileName);
end;

procedure TForm1.BBorrarMemoClick(Sender: TObject);
begin
  Memo1.Clear;
end;

procedure TForm1.BCerrarConsultaClick(Sender: TObject);
begin
  ZQuery1.Close;
end;

procedure TForm1.BConsultaClick(Sender: TObject);
begin
  if not(HayConexion) then Exit;
  ZQuery1.Close;
  ZQuery1.SQL.Text:=Memo1.Text;
  ZQuery1.Open;
end;

procedure TForm1.BExecuteClick(Sender: TObject);
var
  n:Integer;
begin
  if not(HayConexion) then Exit;
  if ZConnection1.ExecuteDirect(Memo1.Text, n) then Memo1.Lines.Add('OK! '+IntToStr(n)+'      filas.');
end;

procedure TForm1.btnConectarClick(Sender: TObject);
begin
  if Conexion=Conectado then
  begin
    ShowMessage('Hay una conexión establecida, primero desconecte dicha conexión.');
    Exit;
  end;
  if not (FileExists(edBaseDeDatos.Text)) then exit;
  ZConnection1.Database:=edBaseDeDatos.Text;
  if not(cbAutoCommit.Checked) then ZConnection1.AutoCommit:=False;
  ZConnection1.Connect;
  if ZConnection1.Connected then
  begin
    edConexion.Text:='Conectado';
    edConexion.Font.Color:=clGreen;
    Conexion:=Conectado;
    MuestroTablas;
  end;
end;

procedure TForm1.BCerrarClick(Sender: TObject);
begin
  Close;
end;

procedure TForm1.btnDesconectarClick(Sender: TObject);
begin
  ZConnection1.Disconnect;
  edConexion.Text:='Desconectado';
  edConexion.Font.Color:=clRed;
  Conexion:=NoConectado;
  lbTablas.Clear;
end;

procedure TForm1.cbAutoSizeColChange(Sender: TObject);
begin
  if cbAutoSizeCol.Checked then DBGrid1.AutoFillColumns:=False else DBGrid1.AutoFillColumns:=True;
end;

procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  ZConnection1.Disconnect;
  CloseAction:=caFree;
end;

procedure TForm1.lbTablasDblClick(Sender: TObject);
begin
  if not(HayConexion) then Exit;
  Memo1.Clear;
  Memo1.Lines.Add('SELECT * FROM '+lbTablas.Items[lbTablas.ItemIndex]+';');
  ZQuery1.Close;
  ZQuery1.SQL.Text:=Memo1.Text;
  ZQuery1.Open;
end;

procedure TForm1.MuestroTablas;
var
  ZQTablas:TZQuery;
begin
  lbTablas.Clear;
  ZQTablas:=TZQuery.Create(nil);
  ZQTablas.Connection:=ZConnection1;
  ZQTablas.SQL.Text:='SELECT name FROM sqlite_master WHERE type='+
  QuotedStr('table')+' and name <>'+QuotedStr('sqlite_sequence');
  ZQTablas.Open;
  if ZQTablas.RecordCount <1 then Exit;
  ZQTablas.First;
  while not(ZQTablas.EOF) do
  begin
    lbTablas.AddItem(ZQTablas.FieldByName('name').AsString,lbTablas);
    ZQTablas.Next;
  end;
  ZQTablas.Close;
  FreeAndNil(ZQTablas);
end;

function TForm1.HayConexion: Boolean;
begin
  if not(ZConnection1.Connected) then
  begin
    Memo1.Lines.Add('No hay establecida ninguna conexión.');
    Exit(False);
  end;
  Result:=True;
end;

end.

TZConnection.ExecuteDirect y TZQuery.

Estos dos componentes pertenecen a Zeos Lib.
TZConnection se utiliza primariamente para establecer una conexión a una base de datos. Entre sus métodos está ExecuteDirect, una función sobrecargada (overloaded). Nada mejor que ver el código fuente:

function ExecuteDirect(SQL:string):boolean;overload;
function ExecuteDirect(SQL:string; var RowsAffected:integer):boolean;overload;

Si únicamente enviamos un parámetro, el string con la sentencia SQL, entonces la primera será llamada. Si usamos los dos parámetros, entonces se llamará a la segunda. Breve aclaración de overload.

Generalmente ExecuteDirect se utiliza para todo lo referido a actualizar la base de datos, por ejemplo: UPDATE, CREATE, DELETE, INSERT, VACUUM.

Como vemos, esta función siempre retorna un boolean en ambas versiones, que será True si hubo éxito o False si hubo un error.
Si además usamos el segundo parámetro, que como observamos es por referencia, el mismo se actualizará con el número de filas afectadas.

Por ejemplo, si mandamos VACUUM, siempre devolverá 0 (cero); en cambio si utilizamos un UPDATE nos devolverá la cantidad de registros actualizados.

Una vez más, veamos el código fuente de la implementación de las funciones:

{**
Executes the SQL statement immediately without the need of a TZQuery component
@param SQL the statement to be executed.
Returns an indication if execution was succesfull.
}

function TZAbstractConnection.ExecuteDirect(SQL : String) : boolean;
var
  dummy : Integer;
begin
  result:= ExecuteDirect(SQL,dummy);
end;

{**
Executes the SQL statement immediately without the need of a TZQuery component
@param SQL the statement to be executed.
@param RowsAffected the number of rows that were affected by the statement.
Returns an indication if execution was succesfull.
}

function TZAbstractConnection.ExecuteDirect(SQL: string; var RowsAffected: integer):boolean;
var
  stmt : IZStatement;
begin
  try
    try
      CheckConnected;
      stmt := DbcConnection.CreateStatement;
      RowsAffected:= stmt.ExecuteUpdate(SQL);
      result := (RowsAffected <> -1);
    except
      RowsAffected := -1;
      result := False;
      raise; {------ added by Henk 09-10-2012 --------}
    end;
  finally
    stmt:=nil;
  end;
end;

Ahora veamos la siguiente línea de código:

if ZConnection1.ExecuteDirect(Memo1.Text, n) then Memo1.Lines.Add('OK! '+IntToStr(n)+' filas.');

Sí bien es casi rídiculo hacer esto, un SELECT con ExecuteDirect, se puede, claro que siempre retornará cero, aunque la tabla tenga 500 filas.
En realidad también se puede, por ejemplo usar VACUUM desde un consulta TZQuery. El tema es saber cual de los dos métodos utilizar según lo que necesitemos, desde ya, una consulta será con TZQuery. Un INSERT puede ser tanto con ExecuteDirect o con los métodos de TZQuery, nuevamente, según lo que necesitemos y el estilo propio de cada programador.

Este UPDATE .. SET en realidad no hace nada, pero es válido como ejemplo, ExecuteDirect devuelve 51 en la variable n pasada por referencia, que desde ya, coincide con la cantidad total de filas de la tabla.

TZQuery:

De entrada conviene aclarar una especie de mito que hay de que siempre debe estar asociada con componente TDataSource, esto es falso, así de simple. Solo necesitaremos un TDataSource si los datos de la consulta deben ser mostrados en otros componentes, como ser un TDBGrid, TDBEdit, TDBLookUpComboBox, etc. Muchas veces veo ejemplos de LazReport donde se incluye innecesariamente un TDataSource.

Otra cosa elemental que no se debe intentar hacer, es editar una consulta que contiene JOIN, el error será inevitable. Es un error muy común tener una consulta con JOIN en un TDBGrid y querer actualizar una fila.

Para insertar un registro en una consulta del tipo SELECT campos FROM tabla (y puede también contener WHERE pero nunca JOIN), primero se debe invocar al método INSERT de TZQUery:

ZQuery1.Insert;

Luego, lo más común es utlizar el método FieldByName:

ZQuery1.FieldByName('nombre').asString:=edNombre.Text;
ZQuery1.FieldByName('edad').asInteger:=nEdad;

Y finalmente se usa el método Post para concretar la transacción. Si la propiedad Autocommit de TZConnection es True, entonces la transacción será inmediata:

ZQuery1.Post;

Un campo INTEGER con AUTOINCREMENT: al hacer un INSERT debe ignorarse siempre, ya sea utilizando ExecuteDirect o TZquery.

También puede utilizarse un TZQuery para un UPDATE, SET, etc.:


DataM.ZQa.Close;
DataM.ZQa.SQL.Text:='UPDATE reg SET saldo=saldo-:importe WHERE banco=:elbanco AND fecha>:regfecha;';
DataM.ZQa.Params.ParamByName('elbanco').AsInteger:=elbanco;
DataM.ZQa.Params.ParamByName('regfecha').AsString:=regfecha;
DataM.ZQa.Params.ParamByName('importe').AsCurrency:=importe;
DataM.ZQa.Open;
DataM.ZQa.Close;

Ventaja: se puede usar Params, algo que no se puede con ExecuteDirect.

TDataSource cuándo usarlo?

Lazarus, Free Pascal y muchos de sus componentes son sencillamente espectaculares, más aún teniendo en cuenta que es un proyecto de código abierto que comenzó hace muchos años (2001 creo), con pocos programadores para tamaño proyecto, actualmente entre Free Pascal y Lazarus, son unos 20.
El problema, lo dije desde un principio cuando retomé la programación, motivado por la existencia del IDE Lazarus, es la documentación, muchas veces inexistente y otras veces hay que dedicar mucho tiempo para dar con ella.
Los tutoriales para trabajar con bases de datos son pocos, algunos desactualizados y casi siempre los mismo ejemplos.
Es así como aprendí que para conectar y trabajar con una BD se necesitan 3 componentes: el conector (TZConnection), la consulta (TZQuery) y la fuente de datos (TDataSource). Pues no es así. Me di cuenta leyendo y participando del foro, cuando un usuario planteó una duda y detecté que en su código no utilizaba ningún TDataSource, le pregunté y me respondió si era necesario. Me sembró la duda y de hecho la respuesta es: No. Eso es cambia mucho mi panorama, para empezar no necesitaré tantos data modules si solo tengo un para de consultas, puedo declarar las conexiones y consultas sin necesidad de utilizar el data aware y que no molesten en el Form. Ahora entiendo por qué una vez me dijeron que cuando comienzas con esto, usas todo data aware y luego vas directamente por el código, y así es.

El data aware TDataSource solo es necesario cuando necesitamos enlazar los datos con un componente como puede ser un DBGrid, DBComboBox, etc. caso contrario no es necesario.

El ícono es muy claro, el componente envía los datos de un TDataSet hacia otros componentes, es indispensable para un TDBGrid por ejemplo, por eso es que el DataSource se debe vincular con un DataSet (y éste con un conector) y lo que alimenta al DBGrid es el DataSource. Ahora si no necesitamos alimentar ningún control, entonces no es necesario utilizar ningún DataSource.

Recorrer un DataSet y actualizar registros

Si queremos actualizar un campo de una fila o registro mientras recorremos el TDataSet por ejemplo mediante un while, obtendremos un error en tiempo de ejecución. Con un ejemplo será más sencillo:

WHILE NOT ZQuery1.EOF DO
begin
  ZQuery1.Edit;
  ZQuery1.FieldByName('alguncampo').AsInteger:=0;   
  ZQuery1.Post
  ZQuery1..Next;
end;

El código precendente nos dará el error y es correcto que lo haga, porque no situamos el puntero al comienzo del DataSet que sería con ZQuery1.First.

ZQuery1.First;
WHILE NOT ZQuery1.EOF DO
begin
  ZQuery1.Edit;
  ZQuery1.FieldByName('alguncampo').AsInteger:=0;   
  ZQuery1.Post
  ZQuery1..Next;
end;

Otra manera de resolver esto es mediante el uso de ExecuteDirect (y más rápido si se trata de un gran volumen), si tenemos un identificador único, mejor:

ZQuery1.First;
WHILE NOT ZQuery1.EOF DO
begin
  ZConnection1.ExecuteDirect('UPDATE tabla SET alguncampo=0 WHERE    identificaorunico='+ZQuery1.FieldByName('id').AsString'+' ;');
  ZQuery1..Next;
end;

Se recomienda hacer esto cuando no podamos hacerlo mediante una sentencia SQL UPDATE, ya que puede ser lento dependiendo del volumen de datos.

Insert masivo y rápido en SQLite

El que sabe, sabe; y el que no, se pasa horas buscando, por eso este simple post para algo tan simple, para los que estamos eternamente aprendiendo.

No hay ningún problema con la ejecución directa de sentencias SQL cuando se trata de pocos registros a insertar, haríamos algo así: (pseudo código)

While not Eof do
  ExecuteDitect('lo que sea');

Si nuestro conector con la base de datos tiene la propiedad autocommit en True y son pocos registros, el usuario no lo notará. El problema es que cada vez que se completa una transacción, SQLite requiere dos completas rotaciones del plato del disco, tendiendo en cuenta unas 7.200 rotaciones por minuto, con suerte, viento a favor y sin usar progressbar, podríamos insertar 60 registros por segundo, es decir, 1.200 registros tomaría 20 segundos, entonces debemos incluir una barra de progreso para que el usuario no piense que el programa dejó de funcionar, la querida barra de progreso relentizará aún más el proceso. Hasta aquí la explicación de por qué demora tanto.

Solución: BEGIN …. COMMIT es decir, encerrar las transacciones entre un BEGIN y un COMMIT.

Ejemplo con el componente ZConnection de ZeosLib:

if ZConnection1.Connected then ZConnection1.Disconnect;
ZConnection1.AutoCommit:=False;
Zconnection1.Connect;
ZConnection1.ExecuteDirect('BEGIN; ');
while not EOF(f) do
begin
  ReadLn(f,s);
  ZConnection1.ExecuteDirect(s);
end;
CloseFile(f);
ZConnection1.ExecuteDirect('COMMIT; ');
ZConnection1.Disconnect;

En este caso, f es un archivo de texto plano que contiene lenguaje SQL. La velocidad es increíble, un archivo de 7,7 MB  en menos de 2 segundos, para más de 45.000 registros de 12 campos.

La propiedad autocommit de ZConnection1 debe estar en False, podemos hacerlo en el inspector de objetos de Lazarus o por código.

Obtener la lista de tablas de una base de datos SQL

Es algo muy simple desde consola o en tiempo de diseño, pero en tiempo de ejecución? También es sencillo, lo difícil fue encontrar cómo hacerlo. Resulta que Zeos (ZeosLib), más precisamente su principal componente, ZConnection, posee un procedimiento llamado GetTableNames que nos devuelve un parámetro del tipo TStrings pasado como referencia (lógico, si no fuese pasado como referencia no devolvería nada) con la lista de todas las tablas de la base de datos conectada. Se le puede pasar un TStringList o un ListBox.items por ejemplo.

 Primer ejemplo con TStringList y un TMemo:

 // Definir procedimiento o función o agregar el código donde sea necesario
var
  listatablas:TStringList;
  i:Integer;        
begin
  listatablas:=TStringList.Create; 
  ZConnection1.GetTableNames('',listatablas);   // La base de datos debe estar conectada...
  for i:=0 to listatablas.Count-1 do
    Memo1.Lines.Add(listatablas[i]);     // Memo1 debe estar en el Form
  ...
end;

El primer parámetro pasado podría haber sido por ejemplo ‘c*’ en cuyo caso hubiésemos obtenido la lista de tablas cuyos nombres comiencen con c. Al pasarlo vacío le indicamos que llene el StringList con todas las tablas.

Este ejemplo, con muy poco código, alcanza y sobra para mostrar los nombres de las tablas, pero si queremos que además el usuario pueda elegir una (o varias) tablas podemos valernos de un ListBox. Para ello, no le pasaremos como parámetro el ListBox sino ListBox.Items que es del tipo TStringList.

ZConnection1.GetTableNames('',ListBox1.Items) // Requiere un ListBox en el Form
 

Conectar SQLite y editar una tabla

En este video vemos como hacer un ABM de una tabla en 2 minutos sin escribir código. Desde ya un ABM es mucho más que lo que se muestra, pero que sirva como punto de partida. Ver en pantalla completa.



La conexión se realiza mediante Zconnection, componente del paquete Zeos Lib, ZQuery y un DataSource.

Soltamos los 3 componentes en el Form, indicamos la base de datos en ZConnection1 y el protocolo sqlite-3, conectamos. No es necesario nada más como se puede ver.

ZQuery1: indicamos que se conecta via ZConnection1, escribimos la consulta en SQL. Antes de activar, vamos a DataSource1 y le indicamos que el DataSet vendrá de ZQuery1. Activamos ZQuery1.

Agreamos un DBGrid y le indicamos que el DataSource será DataSource1.

Agregamos un DBNavigator y de igual forma que con el DBGrid le establecemos el DataSource1.

Por ningún motivo usar ZTable nunca, no tiene sentido y creo que no eliminan el componente por razones de compatibilidad con versiones anteriores.

Todo esto se puede hacer en tiempo de ejecución, escribiendo el código, que no es mucho por cierto y es lo más normal cuando se está desarrollando un programa y no un ejemplo. También se utilizan Data Modules para soltar ahí todos los componentes no visuales y trabajar más cómodo.