Android Question Database update when installing the application

[ThePuiu]

What is the most effective method in the following situation: the application has a database attached. The file is copied from File.DirAssets to SafeDirectory = rp.GetSafeDirDefaultExternal ("") when the application is opened if it does not already exist. The problem arises if the database is modified ... it is no longer copied because the file already exists.
How to proceed in this case? I would like the data in the database not to be lost. I was thinking of adding a table containing a single record with a single column containing the version of the database and another table containing the SQL command to make the necessary changes. The file must be read and interpreted first if the local file already exists or copied if it does not exist.

I think this method is good but a bit complicated to implement in a simplistic application.

What other options would there be? Thank you!

LE: it seems that the database cannot be opened from File.DirAssets, so, what other option exists?

 

[aeric]

Updating an embedded DB

A common task we face while enhancing our apps is the need to update the structure of a running DB. This is relatively easy if you know for sure that your users will all go from version 1 to version 2 and then to version 3. But what happens if an user goes from version 1 straight to version 3...

www.b4x.com

 

[aeric]

LE: it seems that the database cannot be opened from File.DirAssets

The working database where you copied from DirAssets is not stored in DirAssets. It should be copied to some other path like “storage/emulated/0”. The path would be different for different devices.
Maybe you can use app like es file explorer to find your db file and copy out to your PC.

 

[Albert Kallal]

I guess this comes down to be this your project, or are we talking about wide distribution?

One of my goals was that if my phone were to 100% die, or go bad?

I did not want my data in the database to go “down” with the phone.

So, I place the database in the external folder. (This may, or may not be an actual SD card). In my case it is.

So, when you plug your phone to computer (USB) then you can browser the fodders and data.

If you place the database in the external folder, then that folder is exposed and thus you can copy to/from the android phone to update the database.

However, back to the first issue:

You modified the database.

So, your choices are:
Run some code on start-up (or even in some setup menu area), you simply have to execute some sql commands to add the extra new columns (or tables) to that existing database.

And if say you had to add a table with some data?
Well, then on start-up, simply copy that database into your save default external.

You can then open that database, and pull data from it into the existing database.

So, yes you need some kind of “version” number, or you can perhaps on start-up check for a missing table, (or column) and if not found, then you run those SQL commands to add that column.

So, perhaps as you note a table with “one” row that has some version number etc. could be used. If the database version number is “lower”, then you call those routines.

In fact, it a good habit. Thus for each structure (schema) changes, ALWAYS write a script to modify. (and give that script some kind of version number).

And you could have the version number in your B4A project if you don’t have some version number in the database. (but, it would be better to add a version number table to the wor

Then, if for some reason say you made about 3 changes to the database schema? Well, just run those scripts on start up for all script updates LESS than your current version number.

In fact, I would setup a table. With a few columns. (version number, sql update string etc. just as you suggested).

Thus in theory you update your software, and even if the database is 2-3 versions behind, the all of the “version” update scripts would in fact run, and then you update the version number as the last step.

So yes, in the past – this idea of a table with one row and some version information is a good approach.

So, you can simple loop + process that version update table in that “upgrade.db”.

And while you check for existence of the production database (and not over write), the upgrade.db, you can always over-write the “older” upgrade db each time on startup.

So, your idea(s) outlined are sensible.

Right now?

I just plug in my phone browse to that application folder, copy the db to my PC and then open up “db browser” for sqlite.

One nasty: (cost me a whole day).

I find that you can copy the database from the phone. But before you copy back, I re-boot the phone. Some phones seem to “cache” the db file, and a file copy did not take.

So, just before I copy from phone:
Re-boot.

Copy file from phone to PC
Delete file on phone.

Re-boot
Copy file back to phone,
Re-boot.

Now I could probably skip one of the above re-boots, but I don’t have time to check right now – I just do it, and thankfully I don’t do this much often. (but for some reason, some kind of caching was going on here – this was a Moto G or E phone – but I just don’t want you to get bitten by this “nasty”.

So, I copied the db from my phone, make the changes, and then put it back. This works for your person dev cycle.

But, for any kind of users?
Well then your idea of a table + version + sql scripts? Yes, that’s idea.

So add a “upgrade.db” to this mix. You can copy that each time to your default external working folder each time.
And thus process that information into the existing db that you don’t want (and did not) overwrite.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[Nokia]

What is the most effective method in the following situation: the application has a database attached. The file is copied from File.DirAssets to SafeDirectory = rp.GetSafeDirDefaultExternal ("") when the application is opened if it does not already exist. The problem arises if the database is modified ... it is no longer copied because the file already exists.
How to proceed in this case? I would like the data in the database not to be lost. I was thinking of adding a table containing a single record with a single column containing the version of the database and another table containing the SQL command to make the necessary changes. The file must be read and interpreted first if the local file already exists or copied if it does not exist.

I think this method is good but a bit complicated to implement in a simplistic application.

What other options would there be? Thank you!

LE: it seems that the database cannot be opened from File.DirAssets, so, what other option exists?

If your are talking bout updating database by adding tables and what not.. because new version app needs it.. then two ways I would approach it.. run a routine before app makes connection to database that checks:

1. put a version in a table like you talked about and run update if version is older than new version and update version when done OR..

2. just check if tables exist.. if they don't created them with code.. (might be simple option..) you can keep a running update on tables depending on version, so if someone skips a few version.. no matter.. all necessary tables would get created..

 

[aeric]

Another way is, I have used "try catch" to SELECT a [new column], when there is an error, inside "catch", I will try to upgrade the table schema by executing the query to ALTER TABLE [table_name] ADD [new_column] [column_definition]. This maybe not a good or clean way. Keeping db version is a better way. I actually use both together.

My current practice when developing app is always create database table using CREATE TABLE command. I am no longer copy db file from DirAssets since I encountered file not found error in new version of Android.

Example:

        If FirstTime Then
            DBFileDir = File.DirInternal
            DBFileName = "app.db"
            If File.Exists(DBFileDir, DBFileName) = False Then
                '' Modified on: 03 Nov 2017
                '' Reason: New version Android show error file not found
                'DBFileDir = DBUtils.CopyDBFromAssets(DBFileName)
                DB.Initialize(DBFileDir, DBFileName, True)
                strSQL = "CREATE TABLE 'Login' ( `user_api_key` TEXT, `user_access_token` TEXT )"
                DB.ExecNonQuery(strSQL)
            End If
            DB.Initialize(DBFileDir, DBFileName, False)
        End If

Dim DB As SQL

    Try
        Dim cur As Cursor
        Dim qry As String
       
        DB.Initialize(Starter.DBFileDir, Starter.DBFileName, False)
       
        ' Test New Columns
        qry = "SELECT"
        qry = qry & " `ReceivedDate`,"
        qry = qry & " `CreatedDate`"
        qry = qry & " FROM `Sales`"
        qry = qry & " LIMIT 1"
        cur = DB.ExecQuery(qry)
        cur.Close  
    Catch
        If LastException.Message.Contains("no such column: ReceivedDate") Then          
            strSQL = "ALTER TABLE `Sales`"
            strSQL = strSQL & " ADD COLUMN `ReceivedDate` TEXT"
            DB.ExecNonQuery(strSQL)
            Sleep(2000)
            ReadData
        Else If LastException.Message.Contains("no such column: CreatedDate") Then
            strSQL = "ALTER TABLE `Sales`"
            strSQL = strSQL & " ADD COLUMN `CreatedDate` TEXT"
            DB.ExecNonQuery(strSQL)
            Sleep(2000)
            ReadData
        Else
            LogColor("[Main] ReadData: " & LastException.Message, Colors.Red)
            ToastMessageShow("Failed to read data file" & CRLF &  LastException.Message, False)
        End If
    End Try

 

[watesoft]

How to uninstall the old version before installing the new version?

How to require users to uninstall the old version before installing the new version when installing the app? Same question, how to do in B4I？

www.b4x.com

 

[ThePuiu]

Thanks to everyone for their help, especially to colleague Albert Kallal for his time! I didn't think of the version with an additional file for the update, it's a good idea and I'm implementing it!

@aeric: must be modified in too many places in the application ...