Chequear y hacer Cambios en Base de Datos Firedac + Firebird 3

[amadis]

Que tal Colegas.

Estoy tratando de hacer mejor un sistema automatico que chequee la estructura de mi base de datos.

Tengo una aplicacion VCL, de la cual subo actualizaciones del EXE a mi servidor y se actualizan los clientes al ejecutar.

Al conectar el EXE a la Base Firebird con Firedac, lo primero que leo es un INTEGER de un campo VERSION.

Luego en el EXE antes de seguir cargando el sistema tengo numeros de Version donde hubo cambios en la estructura de la BD

Código Delphi [-]
 If VERSION < 16 
  then { aqui creo un fdquery y ejecuto alter table y agrego o modifico estructura de las tablas, pongo version =16 y ejecuto el query}
 
 If VERSION < 17 // aqui nuevamente chequeo cambios posteriores
  then { aqui creo un fdquery y ejecuto alter table y agrego o modifico estructura de las tablas , pongo version =17 y ejecuto el query}   
   
 If VERSION < 18 // aqui nuevamente chequeo cambios posteriores
  then { aqui creo un fdquery y ejecuto alter table y agrego o modifico estructura de las tablas, pongo version =18 y y ejecuto el query}

Y asi cada vez que hago cambios en la estructura de Firebird, agrego varias lineas en el EXE, para que si el exe fue encuentra una version desactualizada de la Base de datos la pueda actualizar, pero se me torna incomodo.

¿existe alguna forma de hacerlo todo en SQL, y dejar solo un Query que se ejecute al iniciar y el mismo query compare la version actual de la Base de datos con las diferentes versiones de cambios posteriores y vaya aplicando los cambios necesarios?

Espero que se entienda la idea de lo que quiero lograr? .

Muchas gracias

[Casimiro Notevi]

Si no he entendido mal, tienes muchas opciones. Por ejemplo, tienes todas los cambios en la BD en una tabla específica, algo así como:

numero..sentencia
1.......insert into...
2.......update tabla...
3.......delete registro...
4.......update otratabla...
5.......etc.

Y en tu BD del programa puedes tener un campo que sea algo así como:

ultimasentenciaejecutada
3

Así que al iniciar el programa compruebas cuál es la última ejecutada en esa BD y ejecutas todas las siguientes al 3 (en este ejemplo).
Finalmente cambias ultimasentenciaejecutada por 5, en este caso.

[amadis]

Empezado por Casimiro Notevi

numero..sentencia
1.......insert into...
2.......update tabla...
3.......delete registro...
4.......update otratabla...
5.......etc.

Esta parte numero de sentencia me da una nueva mirada. Esto yo lo tengo en el ultimo EXE actual. El mismo se distribuye a distintos clientes usuarios del mismo soft (por ende cada uno tiene su BD con sus datos)

Y yo guardaba en la BD en un integer de formato 2208181840 (que significa año mes dia y hora del cambio de la BD).

Y lo que me esta pasando es que tengo como 30 procedimientos (uno por cada día que hice cambios en la BD)

Y buscaba una idea de como organizar mejor todos los procedimientos dentro del código fuente. Pensaba en un futuro ir eliminando los procedimientos primeros para limpiar código que ya será inservible.

[amadis]

se me ocurre almacenar en un clientdataset todos los numeros de version de la BD, y en un campo blob el string de sql a ejecutar para actualizar a dichos cambios

[Casimiro Notevi]

Clientdataset no es algo que esté grabado sino algo que lees a memoria, se pierde al cerrar.
Te aconsejo algo como he indicado.

[amadis]

Tenes razon, en el entusiasmo se me pasó ese detalle.

Y grabarlo a disco no me sirve porque entraría en la misma cuestión que quiero controlar.

Serviria alguna forma de tabla en memoria que la pueda editar en tiempo de diseño y se grabe en el exe.

[Casimiro Notevi]

¿Pero tú quieres que se actualicen los programas de los usuarios o sus bases de datos?

[mamcx]

Yo uso un sistema muy simple.

Incrusto como una constante el archivo con las migraciones dentro del .exe, en este formato:

Código SQL [-]
--version: 2

DROP TRIGGER IF EXISTS visit_code_update_tg ON payment;

--version: 3
ALTER TABLE doc_codes
ADD COLUMN doc_code  TEXT PRIMARY KEY DEFAULT generate_ulid() CHECK (not_empty(doc_code));

Eso lo comparo contra la version de la BD y simplemente es partir el archivo, chequear la version y recorrerlo.

[amadis]

Empezado por Casimiro Notevi

¿Pero tú quieres que se actualicen los programas de los usuarios o sus bases de datos?

La actualizacion de los programas de usuario ya la tengo implementada.

Lo que busco es optimizar el actualizador de base de datos, que de momento lo tengo en un procedimiento en el exe y le voy agregando comprobaciones de versión para que actualice la BD segun corresponda para quedar siempre al dia. (misma version de requisitos del exe con la version de la BD).

[amadis]

Empezado por mamcx

Yo uso un sistema muy simple.

Incrusto como una constante el archivo con las migraciones dentro del .exe, en este formato:

Código SQL [-]
--version: 2

DROP TRIGGER IF EXISTS visit_code_update_tg ON payment;

--version: 3
ALTER TABLE doc_codes
ADD COLUMN doc_code  TEXT PRIMARY KEY DEFAULT generate_ulid() CHECK (not_empty(doc_code));

Eso lo comparo contra la version de la BD y simplemente es partir el archivo, chequear la version y recorrerlo.

Buena idea. Yo hago algo muy similar

Yo al principio en el form principal, tenia un procedimiento que chequeaba la version de la BD y luego ejecutaba las correcciones necesarias.

Luego como el procedure iba creciendo lo saqué a una unit individual para no verlo y que no moleste.

Y estaba buscando ideas para optimizarlo.

[amadis]

Empezado por mamcx

Yo uso un sistema muy simple.

Incrusto como una constante el archivo con las migraciones dentro del .exe, en este formato:

Código SQL [-]
--version: 2

DROP TRIGGER IF EXISTS visit_code_update_tg ON payment;

--version: 3
ALTER TABLE doc_codes
ADD COLUMN doc_code  TEXT PRIMARY KEY DEFAULT generate_ulid() CHECK (not_empty(doc_code));

Eso lo comparo contra la version de la BD y simplemente es partir el archivo, chequear la version y recorrerlo.

Podrias compartir un poco mas sobre como haces la comparacion y el archivo con las constantes.?

En cuanto a la logica para el chequeo, se me ocurre en el source, del form principal, luego de crear los datos y conexion a bd.

Código Delphi [-]
IF tablafirebird.DBVERSION < VERACTUAL
then actualiza
else ejecuta el programa

Mi procedure Actualiza lo vengo haciendo asi
pero es un lio

Código Delphi [-]
If dbversion = 2 then Actaliza3;
If dbversion = 3 then Actaliza4;
If dbversion = x then Actalizax+1;
// asi tengo tantas lineas If ejecutando procedures como versiones

Cada procedure carga en un TFDQuery las sentencia SQL necesarias para actualizar a la version en cuestión.

Funciona, pero me parece que no es prolijo.

[mamcx]

Empezado por amadis

Podrias compartir un poco mas sobre como haces la comparacion y el archivo con las constantes.?

Lo hago en otros lenguajes pero quizás la forma mas simple en Delphi es ponerlo en un TMemo y copiar ahi el SQL.

Empezado por amadis

En cuanto a la logica para el chequeo, se me ocurre en el source, del form principal, luego de crear los datos y conexion a bd.

De hecho, yo lo pongo como una función de la BD:

Código SQL [-]
CREATE OR REPLACE FUNCTION db_master_version(OUT RETURNS INTEGER)
AS $$
BEGIN
  RETURNS = 2;
END;
$$
LANGUAGE plpgsql;

Para la logica, lo que hago es que hago in split del SQL en cada "GO"

Código SQL [-]
--version: 2

DROP TRIGGER IF EXISTS visit_code_update_tg ON payment;

GO

--version: 3
ALTER TABLE doc_codes
ADD COLUMN doc_code  TEXT PRIMARY KEY DEFAULT generate_ulid() CHECK (not_empty(doc_code));

GO

Y parser el segmento de "version". Esto es rust pero la lógica creo se entiende:

Código PHP:

// of es el SQL con los comandos de DB
pub fn load_cmds(of: &str) -> Vec<CmdDB> {
    let mut cmd = Vec::new();
    let mut sql = CmdDB {
        version: 0,
        sql: "".to_string(),
    };

    // Recorro por lineas
    for line in of.lines() {
        // Al encontrar esto, paseo el numero como un integer
        if line.starts_with("--name:") {
            sql.version = line.replace("--name:", "").trim().parse().unwrap();
            sql.sql.clear();
            continue;
        };
        // Acumulo el SQL hasta que encuentro el GO
        if line.starts_with("GO") {
            cmd.push(sql.clone())
        } else {
            sql.sql += &format!("{}\n", line);
        }
    }
    // Retorno los comandos partidos por version
    cmd
}

//Se ven asi:

CmdDB {
        version: 2,
        sql: "
DROP TRIGGER IF EXISTS visit_code_update_tg ON payment;

"}; 


[amadis]

Que tal colegas, finalmente le he dado un giro a todo este tema y lo he resuelto de la siguiente manera, que me resulta transparente para chequear y agregar cambios y practico.

A todo lo planteado anteriormente lo reoslvi, creando una tabla SQLITE con un campo ID (UNTEGER) y STRING (BLOB).
En el ID voy agregando el Nº de version de la base de datos, y en el campo STRING, agrego el string SQL necesario para pasar de la version anterior.

Suponiendo que la version inicial es 1

en la tabla tengo

Código SQL [-]
2  |  ALTER TABLE CLIENTES
       ADD INACTIVO BOOLEAN
       DEFAULT FALSE

A la tabla Sqlite le iré agregando todos los cambios siguientes

Luego en delphi, DESCARGO A ARCHIVO EL SQLITE.DB

Código Delphi [-]
VAR
 ResStream: TResourceStream;

begin
        // guardo dbversion

 ResStream := TResourceStream.Create(HInstance, 'DBVER', RT_RCDATA);
  try
    ResStream.Position := 0;
    ResStream.SaveToFile('DbVer.db');
    finally
    ResStream.Free;
  end;

luego de crear el programa, cargo a un query el archivo sqlite


Hago una consulta para obtener el Nº de version actual y lo guardo en la variable DBVer

y luego ejecuto una funcion que hace todo el chequeo y luego continua la carga del sistema

Código Delphi [-]

Function TDatos.VersionBD : boolean;
var
 MaxVer : integer;

begin

DMVersion.QVersion.Last;   //query que esta en un datamodule de control de version este es el que tiene la tabla SQLITE se abre al iniciar y carga toda la taba
MaxVer := DMVersion.QVersionID.Value;

if DBVer < MaxVer
  then
    begin
     if MessageDlg('Su versión de la Base de Datos es :'+ Inttostr(DbVer)+' y la esperada por el Sistema es : '+InttoStr(MaxVer)+#10#13+
     '¿Desea actulizar la Base de datos antes de continuar?',MtInformation,[mbyes,mbno],0)=mryes
      then
        begin
            while DBVer < MaxVer
            do
               begin  // aqui tengo otro query  conectado a firebird con transaccion update que se encargara de hacer los cambios
               DmVersion.QVersion.Locate('ID',DbVer+1);
                QactDB.Connection.StartTransaction;
                QactDB.SQL.Clear;
                 Try
                   QactDB.SQL.Append(Dmversion.QVersionSTRING.Value);
                   QactDB.OpenOrExecute;
                   QactDB.Close;
                   QactDB.SQL.Clear;
                   QactDB.SQL.Append('update config set version = :v');
                   QactDB.ParamByName('v').AsInteger := DmVersion.QVersionID.Value;
                   QactDB.OpenOrExecute;
                   QactDB.Connection.Commit;
                   DbVer := DmVersion.QVersionID.Value;
                 
                  Except
                   On E: Exception
                    do
                      begin
                        QactDB.Connection.Rollback;     
                        MessageDlg('No se ha podido actualizar a la Version :'+DmVersion.QVersionID.AsString+#10#13+
                        E.Message,mterror,[mbok],0);
                        Break;
                      end;


                 End;
               end;
        end;
    end;
result := true;

De esa forma he resuelto sin tener que parsear textos ni recorrer archivos manualmente

Cada vez que agrego un cambio debo agregar a los recursos del ejecutable la nueva tabla SQLITE con los cambios

Espero sirva de ayuda

Gracias a todos por sus aportes