Android Question DButils: error-free JSON import into SQLite

[peacemaker]

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?

 

[KMatle]

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?

 

[DonManfred]

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

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]

Usually an api doesn't change out of the sudden

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).

no automatic way

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

 

[Erel]

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.

 

[peacemaker]

JSON is always parsed automatically. It is up to you to implement the logic

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...

 

[Erel]

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

 

[peacemaker]

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.

 

[npsonic]

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.

'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

 

[peacemaker]

Created something for you

WOW ! Looks super !
Just created ? Not tested yet ?

 

[npsonic]

WOW ! Looks super !
Just created ? Not tested yet ?

Just created and did quick testing. It should work.

 

[npsonic]

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

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

 

[peacemaker]

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:

Spoiler: Code

Sub FilterJsonMap (SQL As SQL, Table As String, SourceMap As Map) As 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.

 

[peacemaker]

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"?

Spoiler: Now created sub for auto table creating from JSON

                    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