Android Question SQL Question

[gadgetmonster]

Hi All,

In my apps next release I need to add some fields to a table in an SQLite database. This is not a problem but I will have to migrate some data using the new fields. This could take up to a minute.

My question is, where is the best place to do this? At the moment I am checking the database in my main activity create. Adding the new fields and migrating the data. I tried using sql.begintransaction and sql.endtransaction but these do no seem to commit the changes.

Any ideas?

 

[jsanchezc]

You can do it at
Activity_Create
Create a config table where you read/ser DatabaseVersion (VersionDb)
Once database is update, set version number at DatabaseVersion.

part of code:

If SQL1.IsInitialized=False Then
      SQL1.Initialize (File.DirDefaultExternal & "/data","cfg.sqlite",False)
    End If
  VersionDb="0"
  SqlString="Select valor from config where modulo='myapp.main' and variable='VersionDb'"
  Dr=SQL1.ExecQuery(SqlString)
  If Dr.RowCount >0 Then
     Dr.Position =0
     VersionDb =Dr.GetString("valor")
  End If
  Dr.Close
  If VersionDb < "1" Then
       If SQL2.IsInitialized=False Then
          SQL2.Initialize (File.DirDefaultExternal & "/data","rdbk.sqlite",False)
       End If
       SqlString="CREATE  INDEX main.runtrack1 ON roadbooklineas (idruta ASC, paso ASC)"
       Try
       SQL2.ExecNonQuery(SqlString)
         Catch
          Log (LastException.Message )
       End Try
    
    
    
    
       SqlString="CREATE  INDEX main.runtrack2 ON roadbooklineas (guid ASC, paso ASC)"
     Try
       SQL2.ExecNonQuery(SqlString)
        Catch
          Log (LastException.Message )
       End Try
     
       SqlString="CREATE  INDEX main.LeerLineaRuta ON roadbooklineas (guid ASC)"
       Try
       SQL2.ExecNonQuery(SqlString)
           Catch
          Log (LastException.Message )
       End Try
    
       SqlString="CREATE  INDEX main.ListaRutas ON roadbookrutas (idruta ASC)"
       Try
       SQL2.ExecNonQuery(SqlString)
           Catch
          Log (LastException.Message )
       End Try
    
       SqlString="CREATE  INDEX main.LeeRuta1 ON roadbookrutas (idruta ASC,guid ASC)"
       Try
       SQL2.ExecNonQuery(SqlString)
          Catch
          Log (LastException.Message )
       End Try
    
       SqlString="CREATE  INDEX main.LeeRuta2 ON roadbookrutas (guid ASC,idruta ASC)"
       Try
       SQL2.ExecNonQuery(SqlString)
         Catch
          Log (LastException.Message )
       End Try
        Try
                SQL2.Close
         Catch
          Log (LastException.Message )
       End Try
    
       Try
       SqlString="Insert Into config (modulo,variable,valor) values('myapp.main','VersionDb','1')"
       SQL1.ExecNonQuery (SqlString)
       Catch
          Log (LastException.Message )
       End Try
       SqlString="update config set valor='1' where modulo='myapp.main' and variable='VersionDb'"
       SQL1.ExecNonQuery (SqlString)
      
  End If

 

[derez]

I the time is the problem, you can do the migration in a separate thread. The user will be able to work with the application immediately but you need to inhibit access to the db until the transactions are done.