Solved

sqlite data structure / metadata auto update

Posted on 2011-03-19
4
515 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

813 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

14 Experts available now in Live!

Get 1:1 Help Now