Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sqlite data structure / metadata auto update

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi problems to abort a printjob 9 24
ServiceCenter IR Query Expressions 1 40
programming a polycom voip phone 3 24
BATCH to EXE Converter 2 36
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …

860 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