WDModfic type of solution for SQLite and MySQL

Hi,
I need to support SQLite and MySQL databases in some of my WD apps.
But since the database structures are probably going to change sometime in the future, I need something like WDModfic ..

* I understand that we need to do some type of database schema comparing and then create fields and indexes with SQL etc ..
But I would prefer an automated solution

Any suggestions?

Regards
Steven Sitas

A few years back, I talked with a guy who did the following:

In the analysis, for each version, generate the SQL script to CREATE the tables and store it
The "wwdmodfic" code would then compare the last CREATE script with the previous one and generate an ALTER TABLE script based on the differences between the two

Hi,

I do it in a simple way.
I have a table in the database which stores the current database version.
Each EXE contains it's required version in a variable.

After starting the programm it compares the version which is required by the exe with the version of the current database.
The "Alter table" statements are also stored in the exe.

For each version step I run the required "Alter Table" statements.

I think my solution is not elegant but it works very well.
The only tricky thing: If you do a change in the structure copy the Alter Statement directly into the code.

Regards


Stefan.

Hi,
I am looking for a way to compare 2 versions of a database and automatically create the alter statements ..
In the past, when I was working with Clarion and MS SQL, I used MS Visio Enterprise and everything was done automatically.

Unfortunatelly my old MS Visio Enterprise installation does not work anymore in Windows 10/11 and the newer versions (if they still have this functionality) cost to much for me - especially when you consider that this is the only thing I need Visio for ...

BTW, it seems like Jetbrains, Datagrip, does it with a "logical price" - I will download the evaluation version and see how it works ...
* It just took a long time to find this (and other SQL tools) with Google - seems like Google is behaving more and more like an "ad machine" these days - the magic search is "generate migration scripts for sql" ...

Regards
Steven Sitas



Edited 1 time(s). Last edit at 04/29/2023 01:34PM by SteveSitas.

I have a solution for MySQL not yet for PostgreSQL and SQLite... so only for MySQL so far.

You don't need to make for each generation a script, so no precompiled SQL script will be executed. Simple, read on structure from old DB and the new DB structure is the actual. So this works, even with keys and links. New tables will be created old fields deleted.



Edited 1 time(s). Last edit at 04/29/2023 09:06PM by apredl.

Hi Alexander,

I received your email and I sent you back an email for my order.
Did you get it ?

Regards
Steven Sitas

apredl Wrote:
-------------------------------------------------------
> I have a solution for MySQL not yet for PostgreSQL
> and SQLite... so only for MySQL so far.
>
> You don't need to make for each generation a
> script, so no precompiled SQL script will be
> executed. Simple, read on structure from old DB
> and the new DB structure is the actual. So this
> works, even with keys and links. New tables will
> be created old fields deleted.

Hi Alexander,

where can i find this solution? Or can you post more information, i dont understand what you mean with "read on structure from old DB and the new DB structure is the actual".

Greetings, Markus

For MySQL I would suggest you to look up this - [www.devart.com]

A tool called Webyog also supports generating necessary SQL queries for alteration between to different databases.

Also check this feature list of Webyog.

For SQLite check out the tool - [www.sqliteexpert.com]

Hope these are useful to you.

--
Yogi Yang



Edited 1 time(s). Last edit at 05/08/2023 02:36PM by YogiYang.

@Markus

Sorry for the delay, some work needed to be done and yesterday my PC lost internet commection (code 56), I need to set it up new....

I explain a bit: The database which is running on the MySQL server, has a unchanged old structure, so to read out the database simply read with SQL commands the database structure.

For example:

SHOW FULL COLUMNS FROM versions;

Result:

ver_id bigint NULL NO PRI NULL auto_increment select,insert,update,references
ver_guid varchar(32) utf8mb3_general_ci YES NULL select,insert,update,references
ver_analyse_version int unsigned NULL YES NULL select,insert,update,references
ver_programm_version varchar(14) utf8mb3_general_ci YES NULL select,insert,update,references
ver_update_url varchar(128) utf8mb3_general_ci YES NULL select,insert,update,references
ver_last_update_date date NULL YES NULL select,insert,update,references

From now on, you can export the analysis (new = the actual running application) with:

HlistItem("Versions")

Voila you are good to go. Translate the WjnDev .type to a MySQL Text type, compare with the old structure. Create missing tables, create missing fields, change fields to a new data type or size, delete fields which are not nessecary any more. Keys and Links are too in my program. Modify looses not data,

Changes to field-names unfortunatly will not have data, because I have no information about that, so you "could" read the values from the old db, store afterwards the data in the new field (reading for example the ID of the table, store it in a array or textfile) and then export to the new structure? Compare the database version with HInfoAnalysis() and if the old version is less than for exampla < 43 then store the data and restore it afterwards with the id.

So my programm updates dynamically the old database to the new structure for the new analysis version. ATM only for MySQL and MariaDB

A little bit of hacking and some time run into this. So asking for 120 Euros is not much I hope/guess?

yours
Alexander Predl

Hi Alexander,

EUR 120,00 is very fair.

I would like to purchase

Regards

Stefan.