Solved

sqlite data structure / metadata auto update

Posted on 2011-03-19
4
514 Views
Last Modified: 2012-05-11
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
Comment
Question by:dinko1
4 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35173774

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
 
LVL 2

Expert Comment

by:RezaSadigh
ID: 35174264
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
 
LVL 14

Expert Comment

by:systan
ID: 35174803
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
 
LVL 24

Accepted Solution

by:
jimyX earned 500 total points
ID: 35174849
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now