• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

sqlite data structure / metadata auto update

I have an application that uses SQLite as database. Everytime I release a new version database structure is changing (tables or fields add/remove). What is the best way to update the structure of the database?

Maybse something like a command line application that compares two db structures and updates the old structure with the newer one?

Please advice

PS: Application is written in delphi. So if you have any delphi-way, please let me know!
1 Solution
Ephraim WangoyaCommented:

Depends on how you are distributing your upgrades
You need to create an update program that modifies the tables according to the version you are distributing

1. Updating the tables could be as simple as running a script file and hoping no errors occur (very bad)

2. Create proper procedures that check the version of the application, using that version, the upgrade only runs the appropriate functions needed to modify the tables. For each individual function you can check whether the table exists, the field to be modified exists or any data to be transformed

You don't need a command line application to compare the db structure since its your application, you already know what is changing between the versions so why check
Hi my friend,
In addition to @ewangoya if when you want tio upgrade, the data is not important, then you can Restore the new database simpliy. you can do it by SQL code in a Delphi program.
Best regards
The best way to update the structure of the database is to use a tool management for the database.
Working on your Database is essential thing. In my opinion, arriving to an effective Database is 30% of your project. To design a robust application to a point where it makes the work as easy and smooth as possible, having the data organized and secured with eliminating or minimize data expanding (growing fast) besides serving the needs that because for it the application was created.

It's all about designing the Database. Keeping in mind your applications might differ from each other. Perhaps they are sharing basic tables/fields, such as User Accounts, but at some point your database might be completely different than any other (in design and function) where it shares few or none tables/Fields.

If you see your Databases are sharing Tables (structure or data) from previous projects then you can use the options that the SQLite provides which are:

Create similar table:
This will enable you to copy the structures (you can amend it too) to a new table.

Export Table:
Set the options Database/Table/Output file/Export format. You can use different formats such as SQL (at configure check "Include table declaration" to include the structure as well) then it generates the SQL queries that are required to have this same table to be created anywhere (with small adjustments if it is going to be across different DB types).

Another option is to have your SQl queries (that was used before) stored and use after adjusting the necessary fields/tables' names from one DB to another.

Create TableX (Field1 integer, Field2 varchar(24), Field3 ...);

If your table shares similar data then:
Insert into TableX (Field1, Field2,...) select Field1, Field2,... from TableY;
You may add Where clause to eliminate certain records as feasible.

So whenever you work on a project you just start adjusting your queries and execute them and move the tables around.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now