JSON to SQLite - code works, but how to improve?

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
KimD
Posts: 223
Joined: Wed Jul 08, 2015 5:51 am
Location: Wellington, New Zealand

JSON to SQLite - code works, but how to improve?

Post by KimD » Tue Apr 24, 2018 6:08 am

Hi. I need to read a JSON file (example below) into an SQLite table. The code below seems to work, but I suspect that there will be easier ways that I could do this. The "Insert Into" statement in particular, scare the **** out of me. I'm only working with 2 fields, and I've already gained a few additional grey hairs trying to get the quotes right ;-) I'd be interested to hear how more experienced LCers would have handled the same problem, as I've got to do a bunch of JSON to SQLite coding over the next few weeks. Thanks in advance.

put specialFolderPath("desktop") & "/Processed_States.json" into tFileName
put JSONImport(URL("file:" & tFileName)) into tArray
Put "BEGIN TRANSACTION;" into ssSQLCode
revExecuteSQL ssDatabaseID, ssSQLCode
If the number of elements of tArray["processed_states_list"]["Item"] > 0 then
Repeat with loadRow = 1 to (the number of elements of tArray["processed_states_list"]["Item"])
Put ("INSERT into processedState(code, description) VALUES (" & quote & tArray["processed_states_list"]["Item"][loadRow]["code"] & quote & "," & quote & tArray["processed_states_list"]["Item"][loadRow]["desc"] & quote & ");") into ssSQLCode
revExecuteSQL ssDatabaseID, ssSQLCode
End Repeat
End if
Put "COMMIT;" into ssSQLCode
revExecuteSQL ssDatabaseID, ssSQLCode

=============================================================================

{
"processed_states_list": {
"item": [
{
"desc": "Whole",
"code": "WH"
},
{
"desc": "Good Hard",
"code": "GH"
},
{
"desc": "Good Toffee",
"code": "GT"
},
{
"desc": "Gooey",
"code": "GO"
},
{
"desc": "Less Gooey",
"code": "LG"
},
{
"desc": "Light Toffee",
"code": "LT"
},
{
"desc": "Sugar Free",
"code": "SF"
},
{
"desc": "Other",
"code": "OT"
}
]
}
}

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: JSON to SQLite - code works, but how to improve?

Post by AxWald » Tue Apr 24, 2018 1:14 pm

Hi,
  1. SQLite does the BEGIN/ COMMIT for you, so you can usually skip this. You're using it here correctly to prohibit single BEGIN/ COMMITs for each record, but:
    .
  2. Why use a repeat for each record, instead of feeding the whole data into the table at once? Should be much faster.
    For table-like data see my example below. Adapting to arrays should be a minor problem.
    .
  3. I found that creating the VALUES part of a SQL action statement is quite likely to introduce "bgus". So I wrote me a function to do it for me. Start with a button:

    Code: Select all

    on mouseUp
       put "Whole" & tab & "WH" & tab & "1,1" & CR & \
             "Good Hard" & tab & "GH" & tab & "2,2" & CR & \
             "Good Toffee" & tab & "GT"  into myData
       --  myData is a tab-delimited table containing the data for our VALUES
       put "S" & tab & "S" & tab & "P" into myFormat
       --  myFormat is a tab-delimited line containing a descriptor entry for each field that needs formatting
       put formatValues(myData,myFormat)
       --  formatValues takes all this and mingles it into a correct VALUE form
    end mouseUp
    The (slightly reduced) function that does the job:

    Code: Select all

    function formatValues what, theFormat
       --  what:  tab-del multi-line data
       --  theFormat: also tab-del, 1 entry per field (can be empty) on 1 line
       --  returns a nice VALUES construct including the final semicolon
       if theFormat is empty then put "1" into theFormat  --  dummy for 1-value data
       set itemdel to tab
       repeat for each line L in what
          put "(" into myLine
          repeat with i = 1 to the number of items of theFormat
             switch char 1 of item i of theFormat
                case "S"   --  single kwote
                   put Kwote(item i of L, "'") & comma after myLine
                   break
                case "P"   --  number with (comma -> points)
                   if item i of L is empty then 
                      put "0.00" & comma after myLine
                   else
                      put com2Pt(item i of L) & comma after myLine
                   end if
                   break
                case "C"   --  number with (points -> comma)
                   if item i of L is empty then 
                      put "0,00" & comma after myLine
                   else
                      put com2Pt(item i of L, true) & comma after myLine
                   end if
                   break
                default  --  no change
                   put item i of L & comma after myLine
             end switch
          end repeat
          delete last char of myLine
          put ")," after myLine
          put myLine & CR after myVar
       end repeat
       delete last char of myVar
       delete last char of myVar
       put ";" after myVar
       return myVar   
    end formatValues
Just see that there's a few more functions used in here. These they are:

Code: Select all

function Com2Pt what, Reverse
   if Reverse is not true then
      replace comma with "." in what
   else
      replace "." with comma in what
   end if
   return what
end Com2Pt

function kwote what, theChar
   if theChar is empty then put quote into theChar
   return theChar & what & theChar
end kwote
A bonus for the more adventurous ones among us - I just recognized that my more advanced kwote() version (status: development, "may contain hazardous ingredients") could be used in the formatValues() function, too. Have fun testing!

Code: Select all

function kwote what, theChar, ToEscape
   -- kwote("What)" -> "What"
   -- kwote("What","#") -> #What#
   -- kwote("What","1()") -> (What)
   -- kwote("What","2[()]!") -> [(What)]!
   
   if ToEscape is not true then put false into ToEscape
   
   if theChar is empty then -- Default
      put quote into MyChar
      put quote into My2Char
   else  -- theChar not empty:
      if len(theChar) >1 then  -- special mode for different inserts
         if char 1 of thechar is a number then -- a len() is given for multi insert
            put char 1 of thechar into MyLen
            put char 2 to MyLen+1 of theChar into MyChar -- the chunk specified comes before
            put char (MyLen + 2) to len(theChar) of theChar into My2Char -- the remaining stuff behind
         else if char 1 of theChar = "-" then  -- we need to reduce something
            put char 2 of thechar into MyLen
            delete char 1 to MyLen of what
            delete char (MyLen * -1) to -1 of what
            return what
         else  -- standard for () or such
            put char 1 of theChar into MyChar
            put char 2 of theChar into My2Char
            -- note: if theChar doesn't start with a number, anything but char 1 to 2 is ignored!
         end if
      else -- a 1-char theChar:
         put theChar into MyChar
         put theChar into My2Char
      end if
   end if
   if ToEscape then   --- dangerous chars in strings may be escaped!
      put "34,39" into MyNums  -- " '
      repeat for each item X in MyNums
         replace numtochar(X) with "\" & numtochar(X) in what 
      end repeat
   end if
   
   return MyChar & what & My2Char
end kwote
Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

KimD
Posts: 223
Joined: Wed Jul 08, 2015 5:51 am
Location: Wellington, New Zealand

Re: JSON to SQLite - code works, but how to improve?

Post by KimD » Wed Apr 25, 2018 2:47 am

After taking your advice, what I've ended up with is the below -

put specialFolderPath("desktop") & "/" & inputName into tFileName
put JSONImport(URL("file:" & tFileName)) into tArray
If the number of elements of tArray["processed_states_list"]["Item"] > 0 then
Put "INSERT into processedState(code, description) VALUES" into ssSQLCode
Repeat with loadRow = 1 to (the number of elements of tArray["processed_states_list"]["Item"])
Put "(" after ssSQLCode
Put kwote(tArray["processed_states_list"]["Item"][loadRow]["code"], "") & comma after ssSQLCode
Put kwote(tArray["processed_states_list"]["Item"][loadRow]["desc"], "") & ")," after ssSQLCode
End Repeat
delete last char of ssSQLCode
revExecuteSQL the dbID of this stack, ssSQLCode

This seems to work fine, and certainly generates less "did I get the quotes right" fear ;-)

Thanks for your help.

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”