Android Question How to add and use database "Templates"

[Harris]

My previous post asked how to ATTACH a temp database to insert new records - SOLVED (many thanks to Erel and others). The tables in my main database inserted records from my temp database tables. Temp tables were emptied so at next restart of the app, no (duplicate) records will be inserted (no key violation).
I shall call this process - Template 1

During successive updates, this process may happen again and again - inserting new records to expand the scope of the app.

My example is exactly this:
The app performs inspections through checklists.
First started out with Cars and Light Trucks. The user can add or remove items from the checklist for either...

Now I need to add Boats, Airplanes and Baby Strollers... Each has an associated (default) checklist for the user to modify. I can not destroy what they have already done (modified) by forcing an over-write to update the tables with new stuff. Next week I may need to add Houses, Golf Carts and Nuclear Reactors.

Template(s)

I have already created the first template and used it (Template 1).
Since I cannot empty the tables in DirInternal (or delete them), how can I best determine when a new template of the same name (temp.db) with new items is available to install?

Anybody confronted this type of situation? (I am sure - games added "new levels and such" all the time).


Your thoughts most welcome.

Thanks

 

[Erel]

When you update your application you should add code that copies the new tables to the current database (if such exist). The name of the new database is not really important. Just copy it from the assets folder to a temporary file.

 

[Harris]

When you update your application you should add code that copies the new tables to the current database (if such exist). The name of the new database is not really important. Just copy it from the assets folder to a temporary file.

Ok, looks like this needs some thought. I have already copied the new (template) db to a new location. Attached it to the current (main) db and appended the associated tables - then emptied the temp tables.

The temp.db now exists in DirDefaultExternal - but it's tables are empty - Good.

Two weeks from now I update temp.db with tables that contain new data that needs to be imported into main.db (and distribute a new apk).
I guess I could check the file timestamp to see if the temp.db (in apk - DirAssets) is newer than the existing temp.db and copy it to DirDefaultExternal - overwriting the existing.
Now the temp.db tables contain records I wish to append. Insert into main.db tables - empty temp.db tables and then wait for next update (where new apk contains a temp.db with file timestamp greater than existing timestamp).

Guess this is one way....

Thanks

 

[Harris]

Crap!

file.LastModified doesn't support files in the assets folder!!!

ok, don't panic... copy to another temp folder and then compare dates....

 

[Harris]

Does this make sense?

Sub CheckforUpdates
    ' make sure temp directory exists...
    File.MakeDir(File.DirDefaultExternal,"tempdir")

    ' if the template exists in assets, copy it to the tempdir
    If File.Exists(File.DirAssets, "temp.db") = True Then
      File.Copy( File.DirAssets, "temp.db",File.DirDefaultExternal,"/tempdir/temp.db")
    Else
      ' otherwise - no template exists - abort
      Return
    End If
  
    ' if no temp db exists in the default folder - this must be first time (do the template update)
    If File.Exists(File.DirDefaultExternal, "temp.db") = False Then
      File.Copy( File.DirDefaultExternal, "/tempdir/temp.db",File.DirDefaultExternal,"temp.db")
      DoUpdates
    
    Else
      ' compare the two dates of temp in each folder.  If the copy in /tempdir is greater than
      ' that in the default dir - It must be new!  Copy it to default and do update
      Dim d1, d2 As Long
      d1 = File.LastModified(File.DirDefaultExternal,"temp.db")
      d2 = File.LastModified(File.DirDefaultExternal,"/tempdir/temp.db")
      If d2 > d1 Then
          File.Copy( File.DirDefaultExternal, "/tempdir/temp.db",File.DirDefaultExternal,"temp.db")
          DoUpdates
      End If
    
    End If
  
End Sub

Sub DoUpdates
    Try
        oSQL.Initialize(File.DirDefaultExternal, "qinspect.db", False)
        oSQL.ExecNonQuery("ATTACH DATABASE '" & File.Combine(File.DirDefaultExternal, "temp.db") & "' AS tempdb")
      
        Dim c As Cursor
        c = oSQL.ExecQuery("Select * FROM tempdb.InspType")

        If c.RowCount > 0 Then
          oSQL.ExecNonQuery("INSERT INTO InspType Select * From tempdb.InspType")  
          oSQL.ExecNonQuery("Delete FROM tempdb.InspType")
        End If
        oSQL.ExecNonQuery("DETACH DATABASE tempdb")' & File.Combine(File.DirDefaultExternal, "temp.db") &"'")
      
        c.Close
      
    Catch
        Msgbox(" Operation failed: "&LastException.Message&" "," Add New Inspections ")
              
    End Try
End Sub

 

[Erel]

I wouldn't use LastModified for this.
I think that it is much better to store the version in the database.

Add a small table with the version.

 

[Harris]

If File.Exists(File.DirAssets, "temp.db") = True Then
      File.Copy( File.DirAssets, "temp.db",File.DirDefaultExternal,"/tempdir/temp.db")
    Else

Seems that copying a file from Assets to any other folder doesn't retain the original files datetime.
So, File.LastModified() has the datetime of when it is Copied to any folder...

How can I get the file's Created datetime?

Thanks

 

[DouglasNYoung]

Harris,
The way I handle this is to have the complete database on a server. When updated I set a date in a text file on the server which the app queries once per day. If the date is greater than that on the data on the device it downloads the entire file and replaces the current version. That way you don't need to worry about some users getting out of sync if they miss an update etc.

Good luck,
Douglas

 

[Harris]

I wouldn't use LastModified for this.
I think that it is much better to store the version in the database.

Add a small table with the version.

Yes, that should work...

Thanks

 

[Harris]

Harris,
The way I handle this is to have the complete database on a server. When updated I set a date in a text file on the server which the app queries once per day. If the date is greater than that on the data on the device it downloads the entire file and replaces the current version. That way you don't need to worry about some users getting out of sync if they miss an update etc.

Good luck,
Douglas

No luxury of a database connection (at this time). I shall try the version route.
When I get my web site up and running, the version check and site update seems to work best.

Thanks for the tips...