• Registered: 2017-02-01
  • Posts: 106

Topic: General update question

I have a general update question about the database.
.
Lets have the following situation to make my question understandable.
.
1: You have created an application and give it to others (customers).
2: The others are using the application and enter a lot of data to the database.
3: You make a new version with changed database structure (more tables, more fields, changed fields, etc.).
4: The others are getting the new version and install/copy it over the old version. They keep their old database to keep the data they have entered.
.
The question is now:
.
1: Will the old database be keept unchanged and you (the persion who has created the application) must do all changes with SQL Statements.
2: The database will be updated with all changings.
3: The database will be rewritten and the others can say "goodbye" to their datas. In this case it would be needed to write a database backup as a dump and restore procedure and not as a simple copy of the database file.
.
Any experience for this szenario?
.
Thank you.
.
.
.
P.S. sorry for the dot-lines, but the forum does not accept empty lines I am entering...

  • Registered: 2014-06-25
  • Posts: 865

Re: General update question

Updates should be planned carefully in order to whether being able to import the old data into a new structure or keeping the old structure and just add new features with no new tables or column.

When I do a major updates on my databases, I run several tests to make sure that I could export the data from an older version properly and import  them into the new one without data loss. Most of the time it is just on or two reference tables to be updated. The key is to design the database to be modulable with no major impact. It is best to have a lot of small tables rather than one big table with a lot of columns where upgrades would turn into a nightmare.

3 Reply by teco049 (edited by teco049 2017-02-08 16:06:43)

  • Registered: 2017-02-01
  • Posts: 106

Re: General update question

tcoton wrote:

Updates should be planned carefully in order to whether being able to import the old data into a new structure or keeping the old structure and just add new features with no new tables or column.

When I do a major updates on my databases, I run several tests to make sure that I could export the data from an older version properly and import  them into the new one without data loss. Most of the time it is just on or two reference tables to be updated. The key is to design the database to be modulable with no major impact. It is best to have a lot of small tables rather than one big table with a lot of columns where upgrades would turn into a nightmare.

I am aware about these possible problems and how to design a database structure. Best as a N3.

But the big question is how structure updates are handled by MyVisualDatabase. Can I update the structure and send it to other users or does I need to add an export feature and import feature for others. I can not await that others will use an additonal database browser for exporting, updating and importing datas from an old structure to a new structure.
To include tables for every future usage is a soundable theory, but not possible in all development plans.

So finaly the question seems to be still open about how MyVisualDatabase would handle a structure update and if it is possible to start an application without accessing the database and do a manual structure updates via sqlexecute() before the database would be touched by the application.

  • Registered: 2014-06-25
  • Posts: 865

Re: General update question

Up to now, I could not find any other option than scripting an export on old database and import on the new one.

As soon as the application starts, it opens the sqlite.db file, maybe there could be a workaround to lock the app whilst performing the update at first launch but that might involve some serious scripting! I am looking forward to seeing this feature developed by the community as it might help a lot.

I did not use MySql yet, though I think the behaviour might be the same.

  • Registered: 2015-11-13
  • Posts: 652

Re: General update question

Not sure if this answers your question. But there isn't anything that MVD does automatically to upgrade database tables for users. What I do is create a separate MVD program. The only thing this MVD program does is run a script similar to the following:

SqlExecute('ALTER TABLE MyTable RENAME TO TempTable');
    SqlExecute('CREATE TABLE MyTable ('
      + 'id               INTEGER PRIMARY KEY ASC,'
      + 'Field1    TEXT,'
      + 'Field2    TEXT,'
      + 'Field3    TEXT,'
      + 'NewField  TEXT)');

    SqlExecute('INSERT INTO MyTable ('
                            + 'id,'
                            + 'Field1,'
                            + 'Field2,'
                            + 'Field3) '
                        + 'SELECT id,'
                            + 'Field1,'
                            + 'Field2,'
                            + 'Field3 '
                    + 'FROM TempTable');
    SqlExecute('DROP TABLE TempTable');

I use a third party install software to install the upgraded MVD application (without the database). The same install process then installs and runs the MVD database table upgrade program (script above). After the installation the database table upgrade MVD program is deleted because it's not needed any longer. One thing you have to pay attention to is making sure that the "server" setting in the settings.ini file in the database upgrade folder is pointing to the installed application database. My install process does this automatically so the user does not need to do anything. The whole thing is pretty seamless from my standpoint. Hope this helps.

  • Registered: 2016-11-05
  • Posts: 149

Re: General update question

ehwagner wrote:

Not sure if this answers your question. But there isn't anything that MVD does automatically to upgrade database tables for users. What I do is create a separate MVD program. The only thing this MVD program does is run a script similar to the following:

SqlExecute('ALTER TABLE MyTable RENAME TO TempTable');
    SqlExecute('CREATE TABLE MyTable ('
      + 'id               INTEGER PRIMARY KEY ASC,'
      + 'Field1    TEXT,'
      + 'Field2    TEXT,'
      + 'Field3    TEXT,'
      + 'NewField  TEXT)');

    SqlExecute('INSERT INTO MyTable ('
                            + 'id,'
                            + 'Field1,'
                            + 'Field2,'
                            + 'Field3) '
                        + 'SELECT id,'
                            + 'Field1,'
                            + 'Field2,'
                            + 'Field3 '
                    + 'FROM TempTable');
    SqlExecute('DROP TABLE TempTable');

I use a third party install software to install the upgraded MVD application (without the database). The same install process then installs and runs the MVD database table upgrade program (script above). After the installation the database table upgrade MVD program is deleted because it's not needed any longer. One thing you have to pay attention to is making sure that the "server" setting in the settings.ini file in the database upgrade folder is pointing to the installed application database. My install process does this automatically so the user does not need to do anything. The whole thing is pretty seamless from my standpoint. Hope this helps.

Can you attach your project where you said that ou use third party install software?