Android Question DButils: error-free JSON import into SQLite

Discussion in 'Android Questions' started by peacemaker, Oct 16, 2018.

  1. peacemaker

    peacemaker Well-Known Member Licensed User

    HI, All

    If we have HTTP API and get the JSON reply that should be saved into SQLite DB table - the API may be changed, extra fields can be added into JSON suddently...

    The SQLite DB structure is always fixed, so the task is to automate any JSON import (insert to or update the table), but only the fields that are really got in JSON, to do all without errors.

    Maybe anyone already tried to create such code for DButils?
     
  2. KMatle

    KMatle Expert Licensed User

    Usually an api doesn't change out of the sudden. Can you show us an example? A db table is fixed by intention to handle and check the data.

    Of course you can store the complete Json in a string in the db but this makes no sense to me. Do you have an example?
     
  3. DonManfred

    DonManfred Expert Licensed User

    You can use jsonparser to get next Object/next array. Cast the Object into a Map or the Array into a List (you need to know how the json is formed.

    You can use map.containskey to check for existence of a key.

    There will be no automatic way. You need to write your own parser which maps the data to your database.
     
    peacemaker likes this.
  4. peacemaker

    peacemaker Well-Known Member Licensed User

    The main task - to avoid errors. So, if API is changed - but the old app versions with old SQLite structure can work without error with only old DB fields (existing in this old version), got from the new API (that has more fields).


    So, it's the main result - JSON cannot be parsed "automatically".
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    JSON is always parsed automatically. It is up to you to implement the logic and the meaning of each field.

    New fields added shouldn't break your code.
    Structural changes can break it.

    If there is no "logic" involved then store the json text as-is.
     
  6. peacemaker

    peacemaker Well-Known Member Licensed User

    Hmmm, i meant indeed this - "parsing of the logic" of JSON: decode all maps and lists, whole the structure of JSON, and this structure _should_be_ according to the SQLite table structure for "automatic" save.
    And if suddently JSON structure is changed - that the logic parser can save only the existing (old) structure, without breaking the app.

    Or maybe to be more simple - the logic parser is to be the automatic SQL DB generator from JSON.
    Each list in the JSON - it's a DB table record, IMHO...
     
  7. Erel

    Erel Administrator Staff Member Licensed User

    How can the old program work with the new data? What is the data used for?
     
  8. peacemaker

    peacemaker Well-Known Member Licensed User

    Old app should work error-free as before with the new data only that is got by the logic parser, ignoring new structure elements (fields).
    Seems, here should be the JSON structure and SQLite DB structure comparator...

    Actually, as the simplest way - if the structure is found that changed - the whole DB can be re-created to avoid the errors (sure all data lost). But only if was changed.

    But the topic idea is to save app work with old structure, but new data from new JSON structure, error-free.

    Main feature - just simplification of the work with internal SQLite DB that any time is created\updated according to the JSON (changed any time by the server programmer) automatically.
    Seems, such super-function can be part of DButils - ExecuteDBfromJSON.
     
    Last edited: Oct 17, 2018
  9. npsonic

    npsonic Active Member Licensed User

    Here you go. Created something for you, maybe this is answer for your problem. This method uses SQLite database more like the NoSQL.
    SQLite columns are created according to keys in json, so if first json contains keys 1 and 2 and second json contains only key 3 your table will contain columns 1, 2 and 3.

    It's also good to notice that json must only contain maps, there is no key which to use to create new column if json contains list.

    Code:
    'When reset is set to true all columns that doesn't exists in json will be dropped.
    Sub InsertJson (Json As String, Reset As Boolean)
        
    sql.ExecNonQuery("CREATE TABLE IF NOT EXISTS json (rowid INTEGER PRIMARY KEY)")
        
    Dim Res As ResultSet = sql.ExecQuery("PRAGMA table_info(json)")
        
    Dim par As JSONParser : par.Initialize(Json)
        
    Dim k As Map = par.NextObject
        
    Do While Res.NextRow
            
    For Each key As String In k.Keys
                
    If Res.GetString("name") = key Then
                    k.Remove(key)
                    
    Exit
                
    End If
            
    Next
        
    Loop
        
    For Each key As String In k.Keys
            
    sql.ExecNonQuery($"ALTER TABLE json ADD COLUMN ${key} NONE"$)
        
    Next
        Res.Close
        par.Initialize(Json)
        k = par.NextObject
        
    If Reset Then
            
    Dim c As String
            
    For Each key As String In k.Keys
                c = c & key & 
    ","
            
    Next
            c = c.SubString2(
    0,c.LastIndexOf(","))
            
    sql.ExecNonQuery($"CREATE TABLE json_backup(rowid INTEGER PRIMARY KEY,${c})"$)
            
    sql.ExecNonQuery($"INSERT INTO json_backup Select rowid,${c} FROM json"$)
            
    sql.ExecNonQuery($"DROP TABLE json"$)
            
    sql.ExecNonQuery($"CREATE TABLE json(rowid INTEGER PRIMARY key,${c})"$)
            
    sql.ExecNonQuery($"INSERT INTO json Select rowid,${c} FROM json_backup"$)
            
    sql.ExecNonQuery($"DROP TABLE json_backup"$)
        
    End If
        
    Dim part1 = "INSERT INTO json (", part2 = ") VALUES (" As String
        
    For Each key As String In k.Keys
            part1 = part1 & key & 
    ","
            part2 = part2 & 
    $"'${k.Get(key)}',"$
        
    Next
        part1 = part1.SubString2(
    0,part1.LastIndexOf(","))
        part2 = part2.SubString2(
    0,part2.LastIndexOf(","))
        
    sql.ExecQuery(part1 & part2 & ")")
        k.Clear
    End Sub
     
    Last edited: Oct 17, 2018
    peacemaker likes this.
  10. peacemaker

    peacemaker Well-Known Member Licensed User

    WOW ! Looks super !
    Just created ? Not tested yet ?
     
  11. npsonic

    npsonic Active Member Licensed User

    Just created and did quick testing. It should work.
     
  12. npsonic

    npsonic Active Member Licensed User

    Here's test if someone just want to "copy paste" test this.

    Code:
    Sub Process_Globals
    End Sub

    Sub Globals
        
    Private sql As SQL
    End Sub

    Sub Activity_Create(FirstTime As Boolean)
        
    sql.Initialize(File.DirInternal,"db",True)
        InsertJson(
    "{key1: val1}",False'Table will contain column 'key1'
        InsertJson("{key3: val3}",False'Table will contain columns 'key1' and 'key3'
        InsertJson("{key3: val3, key4: val4, key5: val5}",True'Table will contain columns 'key3', 'key4' and 'key5' (Column 'key1' will be dropped)
      
        
    Log("--------Columns--------")
        
    Dim Res As ResultSet = sql.ExecQuery("PRAGMA table_info(json)")
        
    Do While Res.NextRow
            
    Log(Res.GetString("name"))
        
    Loop
        
    Log("-------------------------")
        Res.Close
    End Sub

    Sub Activity_Resume
    End Sub

    Sub Activity_Pause (UserClosed As Boolean)
    End Sub

    'When reset is set to true all columns that doesn't exists in json will be dropped.
    Sub InsertJson (Json As String, Reset As Boolean)
        
    sql.ExecNonQuery("CREATE TABLE IF NOT EXISTS json (rowid INTEGER PRIMARY KEY)")
        
    Dim Res As ResultSet = sql.ExecQuery("PRAGMA table_info(json)")
        
    Dim par As JSONParser : par.Initialize(Json)
        
    Dim k As Map = par.NextObject
        
    Do While Res.NextRow
            
    For Each key As String In k.Keys
                
    If Res.GetString("name") = key Then
                    k.Remove(key)
                    
    Exit
                
    End If
            
    Next
        
    Loop
        
    For Each key As String In k.Keys
            
    sql.ExecNonQuery($"ALTER TABLE json ADD COLUMN ${key} NONE"$)
        
    Next
        Res.Close
        par.Initialize(Json)
        k = par.NextObject
        
    If Reset Then
            
    Dim c As String
            
    For Each key As String In k.Keys
                c = c & key & 
    ","
            
    Next
            c = c.SubString2(
    0,c.LastIndexOf(","))
            
    sql.ExecNonQuery($"CREATE TABLE json_backup(rowid INTEGER PRIMARY KEY,${c})"$)
            
    sql.ExecNonQuery($"INSERT INTO json_backup Select rowid,${c} FROM json"$)
            
    sql.ExecNonQuery($"DROP TABLE json"$)
            
    sql.ExecNonQuery($"CREATE TABLE json(rowid INTEGER PRIMARY key,${c})"$)
            
    sql.ExecNonQuery($"INSERT INTO json Select rowid,${c} FROM json_backup"$)
            
    sql.ExecNonQuery($"DROP TABLE json_backup"$)
        
    End If 
        
    Dim part1 = "INSERT INTO json (", part2 = ") VALUES (" As String
        
    For Each key As String In k.Keys
            part1 = part1 & key & 
    ","
            part2 = part2 & 
    $"'${k.Get(key)}',"$
        
    Next
        part1 = part1.SubString2(
    0,part1.LastIndexOf(","))
        part2 = part2.SubString2(
    0,part2.LastIndexOf(","))
        
    sql.ExecQuery(part1 & part2 & ")")
        k.Clear
    End Sub
     
    Last edited: Oct 17, 2018
    peacemaker likes this.
  13. peacemaker

    peacemaker Well-Known Member Licensed User

    Thanks again. Soon will test and will think about more detailed analyzing each .NextObject, to check the lists... It needs to check whole the structure to get all the arrays (aka SQL tables), comparing their names in Pragma...

    upd: now tested and started to use such filter, removing the new keys from the input JSON from server:
    Code:
    Sub FilterJsonMap (SQL As SQL, Table As String, SourceMap As MapAs Map
        
    Dim Res As ResultSet = SQL.ExecQuery($"PRAGMA table_info(${Table})"$)
        
    Dim Out As Map
        Out.Initialize
        
    Do While Res.NextRow
            
    For i = 0 To SourceMap.Size - 1
                
    Dim key As String = SourceMap.GetKeyAt(i)
                
    Dim name As String = Res.GetString("name")    'db table field name
                If name = key Then
                    Out.Put(SourceMap.GetKeyAt(i), SourceMap.GetValueAt(i))
                    
    Exit
                
    End If
            
    Next
        
    Loop
        Res.Close
        
    Return Out
    End Sub

    I use this sub before updating the DB.
     
    Last edited: Oct 19, 2018
  14. peacemaker

    peacemaker Well-Known Member Licensed User

    Just found the topic https://www.b4x.com/android/forum/threads/json-getobjfrompath.85539/
    I guess, this sub can help to analyze JSON's structure.

    NB.: sorry for postings, but i'd like to describe - i'm posting here trying to understand (and maybe create, if it's real a kind of JSONutils) the code module for work with JSON maximally error-free and automated, but now i understand not all yet... So, for whole the community, all found links\subs... are being collected in this topic...

    So, task is get all objects, arrays, values with their names.
    Or fully automated JSON analyzing is impossible if we do not know the object names "a priori"?

    Code:
    Dim Items As List = others.JSON_GetObjFromPath(root, "/Items")
                        DBUtils.DropTable(Starter.SQL, 
    "PositionTypes")
                        others.JSON_CreateTableAuto(Starter.SQL, 
    "PositionTypes", Items)
                        DBUtils.InsertMaps(Starter.SQL, 
    "PositionTypes", Items)

    ....
    'RowsListOfMaps - parsed from JSON: say Items=[{PositionType=1, Name=Name1, Count=1}, {PositionType=2, Name=Name2, Count=0}]
    Sub JSON_CreateTableAuto(SQL As SQL, TableName As String, RowsListOfMaps As List)
        
    Dim sb As StringBuilder
        sb.Initialize
        sb.Append(
    "(")
        
    Dim row As Map = RowsListOfMaps.Get(0)
        
    For i = 0 To row.Size - 1
            
    Dim field, ftype, fvalue As String
            field = row.GetKeyAt(i)
            fvalue = row.GetValueAt(i)
            
    If IsNumber(fvalue) = False Then
                ftype = DBUtils.DB_TEXT
            
    Else
                
    If fvalue.Contains("."Then
                    ftype = DBUtils.DB_REAL
                
    Else
                    ftype = DBUtils.DB_INTEGER
                
    End If
            
    End If
            
    If i > 0 Then sb.Append(", ")
            sb.Append(
    "[").Append(field).Append("]").Append(" ").Append(ftype)
        
    Next
        sb.Append(
    ", rowid INTEGER PRIMARY KEY")
        sb.Append(
    ")")
        
    Dim query As String = "CREATE TABLE IF NOT EXISTS [" & TableName & "] " & sb.ToString
        
    SQL.ExecNonQuery(query)
    End Sub
     
    Last edited: Oct 19, 2018
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice