Jun 02

SQLite – Triggers: explicación y ejemplos

Un trigger es un evento que se lanza cuando ocurre algo determinado en una base de datos y se ejecuta una sentencia SQL. Ese “algo” que ocurre puede ser que se borre un registro (fila) de una determinada tabla, que se agregue un registro o que se modifique uno o más campos de una tabla, es decir, un INSERT, DELETE o UPDATE.

¿Cuándo se declara el trigger? Puede declararse en cualquier momento y siempre de acuerdo a las necesidades del caso, siendo lo normal hacerlo al momento de crear las tablas, como los índices y constraints.

No es necesario que las tablas actuantes en el trigger estén relacionadas mediante una Foreign Key.

Existe una IDE para SQLite muy práctica para este tipo de cosas, especialmente hasta que le tomemos la mano, nos es de gran ayuda, que es SQLiteStudio (está en inglés) y es de código abierto.

Uno puede, no obstante, prescindir de los triggers de SQLite y hacerlo a mano, es decir, mediante programación, en mi caso, con Free Pascal desde Lazarus, de hecho lo estuve haciendo hasta ahora, por “seguridad”, por preferir tener el control total especialmente en cuanto a la validación de por ejemplo valores duplicados, todavía lo hago, no me llevo bien con el manejo de excepciones, además SQLite carece de Stored Procedures, de momento son excusas válidas. Pero para borrar un registro en otra tabla, actualizar un dato, o agregar, casos simples, empecé a utilizar triggers, más precisamente hace dos días y por suerte además de funcionar correctamente, no es algo difícil de implementar.

Los ejemplos son casos reales de un programa de control de proveedores e insumos.

INSERT:

Un trigger que cree un registro en otra tabla cada vez que el usuario da de alta un insumo.

CREATE TRIGGER agregoinsu
         AFTER INSERT
            ON prod
BEGIN
    INSERT INTO movinsu (
                            idcompra,
                            idprod,
                            fecha,
                            cantidad,
                            precio,
                            total,
                            saldo
                        )
                        VALUES (
                            0,
                            new.id,
                            '2016-01-01',
                            new.ini,
                            0,
                            0,
                            0
                        );
END;

Al trigger lo nombré “agregoinsu”, luego hay que indicar cuando se dispara el trigger, antes o después y antes o después de ¿qué?, en este caso después (AFTER) de que se produjo el INSERT. ¿en qué tabla?, ON prod (en la tabla prod). Nota: la tabla prod debería llamarse insumos, pero se llama prod por motivos que no vale la pena aclarar. Hasta acá definimos cuando se lanza el trigger, ahora debemos indicar que se hace y eso lo hacemos mediante sentencias SQL comprendidas entre BEGIN y END; En este caso se indica que se inserte un registro en la tabla “movinsu” que posee un campo autoincremental “id” que se omite porque se encarga SQLite, el resto de las columnas o campos los especifico. Ahora debo definir los valores de esa nueva fila en “movinsu” y acá aparece la palabra new. Para triggers de inserción, se utiliza new, para triggers de eliminación se utiliza old y para triggers de actualización se pueden utilizar ambos (new y old). “new.id” significa que el nuevo registro en movinsu, en la columna idprod, irá el valor del nuevo (new) id (prod.id) y en la columna cantidad ira el valor “new.ini” que viene de prod.ini. Resumiendo, cada vez que se agregue, o dé de alta, un insumo (prod) se insertará una fila en la tabla movinsu con los valores especificados.

Este trigger debe definirse sobre la tabla que lanza el evento, en este caso, la tabla prod.

DELETE:

Lo contrario al ejemplo anterior, un trigger que elimine un registro en la tabla movinsu cuando se elimina un insumo de la tabla prod.

CREATE TRIGGER borroinsu
         AFTER DELETE
            ON prod
BEGIN
    DELETE FROM movinsu
          WHERE (idprod = old.id) AND 
                (idcompra = 0);
END;

Este trigger es muy simple, AFTER (después) de DELETE (borrar) un registro ON (en) la tabla prod se ejecuta la sentencia SQL comprendida entre BEGIN y END, borrar de la tabla movinsu donde “old.id” sea igual a idprod (de la tabla movinsu). La otra condición idcompra=0 es porque con ese ID identifico el stock inicial; en realidad podría obviar esto, ya que antes de permitir borrar un insumos chequeo primero que no tenga movimientos registrados, si los tiene no permito su eliminación, pero por las dudas prefiero comprobarlo dos veces y no dejar que un trigger se dispare y borre alegremente todos los movimientos de un insumo, no nos olvidemos que cualquiera con un poco de conocimientos puede abrir la tabla y borrar un insumo y el trigger se dispara igual, por ejemplo, un usuario intenta borrar un insumo y el programa no lo deja porque tiene 20 registraciones, el usuario debe primero borrar todas las registraciones y luego borrar el insumo, puede tentarse de meter mano a la base de datos y borrarlo, pues bien, en este caso, el trigger con el condicional (idcompra=0) solo borrará el registro correspondiente al stock inicial dejando “vivas” las otras registraciones. Claro que si lo que se pretende es que el trigger arrase con todo quedaría así:

CREATE TRIGGER borroinsu
         AFTER DELETE
            ON prod
BEGIN
    DELETE FROM movinsu
          WHERE (idprod = old.id);
END;

“old.id” es el campo “id” del registro que se borró. Como es un trigger del tipo delete, solo se puede utilizar old para referenciar un campo.

UPDATE:

En update se puede usar tanto old como new para referenciar campos. Este trigger se lanza cuando se modifica el stock inicial de un insumo (campo “ini”) en la tabla prod y modifica el campo “cantidad” en la tabla movinsu.

CREATE TRIGGER modificoinsu
         AFTER UPDATE OF ini
            ON prod
BEGIN
    UPDATE movinsu
       SET cantidad = new.ini
     WHERE (idprod = new.id) AND 
           (idcompra = 0);
END;

Traduciendo un poco, se lanza después (AFTER) de una modificación (UPDATE) del campo “ini” de la tabla prod (ON prod) y se ejecuta la actualización (UPDATE) de la tabla movinsu, se establece (SET) el campo “cantidad” con el valor de “new.ini” (el nuevo stock inicial) y la claúsula WHERE es la misma que el ejemplo anterior. Si bien el campo “id” no cambia, se debe referenciarlo con new o con old.

Son tres ejemplos sencillos para iniciarse en el tema triggers.

Documentación consultada: (en inglés)

https://sqlite.org/lang_createtrigger.html

https://www.tutorialspoint.com/sqlite/sqlite_triggers.htm

 

May 01

Cambiar color de varios TEdit OnEnter OnExit

Si tenemos pocos (2 o 3) Tedit en un formulario, podemos rápidamente hacer uso de los eventos OnEnter y OnExit de cada edit y listo, total, son pocos. Pero si tenemos muchos TEdit cómo crear un evento para OnEnter y otro para OnExit que sirva para todos los Edits, sean 1, 2, 10 o 50. Simple, creamos dos procedimiento en la clase del formulario, no aparte del Form, sino como miembros de la clase Form.

Ejemplo:

procedure EntraEdit(Sender: TObject);
procedure SaleEdit(Sender: TObject);

Luego implementamos dichos eventos:

procedure TForm1.EntraEdit(Sender: TObject);
begin
 TDBEdit(Sender).Color:=clMoneyGreen;
end;

procedure TForm1.SaleEdit(Sender: TObject);
begin
  TDBEdit(Sender).Color:=clDefault;
end;

Luego desde el inspector de objetos, en la parte Eventos, en cada TEdit deberemos seleccionar en todos el evento OnEnter y OnExit asignando EntraEdit y SaleEdit respectivamente. Con esto logramos reducir la cantidad de código. Con esta optimización, otra ventaja es que si deseamos cambiar el color y tenemos 20 edits, solo necesitamos modificar una sola línea de código.

 

Abr 11

DBGrid: Formato de la columna según el tipo de campo

En tiempo de diseño y conociendo la tabla es una cosa, pero en tiempo de ejecución y sin saber nada de la tabla, ya es distinto. Para empezar nos encontramos con tipos de datos enumerados y por ende, debemos conocerlos. Luego hay que recorrer las columnas o campos y definir el formato según lo que necesitemos, siendo lo normal los tipos de dato numéricos y de fecha. Para el ejemplo se recorrerán las columnas de una tabla SQL y se cambiará el formato del DBGird si el campo es numérico decimal.

procedure TForm1.FormatoColumnasGrid;
var
  ind:Integer;
begin
  FormatSettings.DecimalSeparator:='.';
  for ind:=0 to ZQ.FieldCount-1 do
    if (ZQ.FieldDefs.Items[ind].DataType=ftFloat) or (ZQ.FieldDefs.Items[ind].DataType=ftCurrency)
    or (ZQ.FieldDefs.Items[ind].DataType=ftBCD) then
      DBGrid1.Columns[ind].DisplayFormat:='#0.00';
end;

Este procedimiento se llama luego de habilitarse el DBGrid. Se establece el separador decimal para establecer correctamente el formato aunque también se podría hacer de forma automática o preguntarle al usuario.

Se utiliza FieldCount – 1 porque la indexación del ZQuery (de ZeosLib) comienza por cero. DataType es del tipo enumerado, para SQL los valores de numéricos con decimales son: ftFloat, ftCurrency y ftBCD,

Listado de tipos de datos completo en español

Documentación de TFieldType

 

Abr 11

Obtener el string de un enumerado

No existe algo así como EnumToStr pero desde luego hay formas sencillas de obtener o “convertir” (entre comillas) el valor en cadena de caracteres de un enumerado.

¿Cómo? Con el procedimiento Str.

Ejemplo:

Str ( ZQuery1.FieldDefs.Items[i].DataType, s );
showmessage(s)
;

A Str le pasamos el primer parámetro que es el enumerado y el segundo que es una variable del tipo string, ambos por referencia y el procedimiento asignará a la variable, en este caso “s” el valor string del enumerated.

procedure Str(var X: TNumericType[:NumPlaces[:Decimals]];var S: String)

Opcionalmente, se puede establecer el formato numérico, ya que Str también convierte tipos de datos numéricos, sean o no enumerados, aunque para los no enumerados hay funciones más completas y con la comodidad de ser funciones y no procedimientos.

Documentación de Str en Lazarus

Documentación de Str en Free Pascal

 

Abr 07

Insert masivo en SQLite y rápido.

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 el False, podemos hacerlo en el inspector de objetos de Lazarus o por código.

 

Mar 28

Convertir boolean a string

Si bien cualquier programador es capaz de hacer una función que reciba una variable del tipo boolean y devuelva una cadena de caracteres (string) en pocos minutos, ¿para que reinventar la rueda?

El título de esta entrada, en sí está mal, pues sabemos que en Pascal una variable no puede cambiar de type, pero se entiende.

BoolToStr se puede usar de dos formas:

BoolToStr ( variableBoolean )

De este modo retorna un ‘-1’ en caso de que la variable sea True o ‘0’ (cero) si es False.

BoolToStr ( variableBoolean, 'Verdadero', 'Falso')

En cambio de esta forma podemos pasar los strings para cada valor, correspondiendo el primero para True y el siguiente para False. Estos parámetros son recibidos como const (constantes) por la función, como puede apreciarse en la wiki de FreePascal: BoolToStr.

 

Mar 26

Arrastrar y soltar entre listas (Drag and Drop)

Lo primero a tener en cuenta es que lo que se arrastra de un contenedor tiene que ser compatible con el contenedor receptor. Lo habitual es que se arrastren y suelten strings (cadenas) entre listas de strings, es lo más básico. En el ejemplo se arrastrará y soltará desde una lista del tipo TFileListBox a otra del tipo TListBox.

Hay que preparar ambos objetos, el TFileListBox para que permita arrastrar sus ítems y a TListBox para que acepte lo que le llega de TFileListBox.

En un Form crear ambos componentes y dejarles su nombre por default (TFileListBox1 y ListBox1).

Desde el inspector de objetos seleccionamos FileListBox1 y establecemos dmAutomatic en la propiedad DragMode. Con esto solo conseguimos que los elementos contenidos en esa lista se puedan arrastrar. Y con este elemento no es necesario hacer más nada.

Ahora seleccionamos también desde el inspector de objetos ListBox1, en Eventos, definimos OnDragDrop y OnDragOver. (definimos = hacer click en el botón con los ‘…’).

procedure TForm1.ListBox1DragDrop(Sender, Source: TObject; X, Y: Integer);
begin
  if (Source is TFileListBox) then ListBox1.AddItem(FileListBox1.items[FileListBox1.ItemIndex],ListBox1);
end;

ListBox1 agregará ítems que provengan de objetos del tipo TFileListBox.

 

procedure TForm1.ListBox1DragOver(Sender, Source: TObject; X, Y: Integer;
		State: TDragState; var Accept: Boolean);
begin
  Accept:=(Source is TFileListBox);
end;  

ListBox1 aceptará que objetos del tipo TFileListBox le suelten ítems.

Arrastrar y soltar entre listas – Lazarus from lazarus.elsigno.com on Vimeo.

Mar 21

TTimer

El componente TTimer es un temporizador con un intervalo mínimo de un milisegundo aproximado aunque es recomendable establecer un intervalo mínimo de 10 milisegundos para que se aproxime más a la realidad, si el intervalo se establece en 100 o 1000 entonces se obtendrá un mejor resultado. Free Pascal cuenta con varios temporizadores, siendo TTimer el más simple y limitado. Para medir con exactitud es recomendable crear un cronómetro propio en base a la hora provista por el sistema operativo y calcular en base a la diferencia entre finalización e inicio. Pero si lo que buscamos no requiere de mayor precisión que la de un segundo o décima de segundo, entonces Timer es el indicado.

Los ingredientes necesarios para seguir el ejemplo son, además del Form:

  • 1 TTimer
  • 1 TLabel
  • 3 TButton
  • 1 variable integer (privada o pública)

TTimer

PaletaSystem

El componente TTimer se encuentra en la paleta System.

Por defecto el Timer viene activado, para este ejemplo, desde el inspector de objetos lo desactivamos.

Timer1

De paso definimos el intervalo en 100 para obtener una medición lo más real posible, claro que después se puede jugar y poner en 1 a ver qué pasa. Pasará que en 10 segundos medirá 6 o como mucho 7; si lo ponemos en 10, en 10 segundos medirá 8-9 aprox. y en 1000 el margen de “error” será de aprox. 1% o incluso menos.

Form-Timer-Desing

Sí, el contador en verde, bold y agrandado, porque todo en escala de grises es simplemente aburrido.

Pasemos al código:

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, ExtCtrls, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    btnComenzar: TButton;
    btnDetener: TButton;
    btnVolverACero: TButton;
    lblContador: TLabel;
    Timer1: TTimer;
    procedure btnComenzarClick(Sender: TObject);
    procedure btnDetenerClick(Sender: TObject);
    procedure btnVolverACeroClick(Sender: TObject);
    procedure Timer1Timer(Sender: TObject);
    private
      contador:Integer;
    { private declarations }
    public
    { public declarations }
 end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Timer1Timer(Sender: TObject);
begin
  contador:=contador+1;
  lblContador.Caption:=IntToStr(contador);
end;

procedure TForm1.btnComenzarClick(Sender: TObject);
begin
  Timer1.Enabled:=True;
end;

procedure TForm1.btnDetenerClick(Sender: TObject);
begin
  Timer1.Enabled:=False;
end;

procedure TForm1.btnVolverACeroClick(Sender: TObject);
begin
  contador:=0;
  lblContador.Caption:=IntToStr(contador);
end;

end. 

El resultado:

Timer-Run

 

Mar 20

Obtener la lista de tablas de una base de datos SQL

ZConnectionEs 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.

Segundo ejemplo con ListBox:

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

No puede ser más fácil, y con solo una línea de código.

 

Mar 17

DBGrid: seleccionar una columna

El componente DBGrid carece de una opción para marcar una columna de la forma en que lo hacemos con una fila con dgRowSelect, es decir, no tenemos la opción dgColSelect. Lo cual no significa que no podamos hacerlo, de hecho el código es bastante simple:

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
  DBGrid1.Columns[Column.Index].Color := clHighlight;
  DBGrid1.Columns[column.Index].Title.Color := clHighlight;
end;   

Basta con dos líneas de código para el evento TitleClick que nos envía parámetro Column del tipo TColumn, esto nos facilita todo, pues usamos la propiedad Index para saber sobre que columna (el itulo  de la columna) se hizo el click y le cambiamos el color tanto al título como a las celdas, según se desee.

columnclick

Entradas más antiguas «