• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • 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!
0
dinko1
Asked:
dinko1
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
0
 
RezaSadighCommented:
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
0
 
systanCommented:
The best way to update the structure of the database is to use a tool management for the database.
http://www.softpedia.com/get/Internet/Servers/Database-Utils/SQLiteStudio.shtml
0
 
jimyXCommented:
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.

i.e.:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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