Page 1 of 1

SQLite Database to JSON File Converter

Posted: Thu Feb 27, 2020 9:21 am
by TI-994A
A small conversion utility that automatically reads all the tables and data from a given SQLite database file and converts them into JSON format, written into a text file.

This example demonstrates the use of PureBasic's various built-in Database & JSON library functions.

Code: Select all

;==============================================================
;   Export2Json - SQLite Database to JSON File Converter 
;
;   1. reads SQLite databases and automatically converts
;      all the tables and table-entries into JSON format.
;
;   2. BLOBs are Base64-encoded into ASCII strings.
;
;   3. results are written directly to text files using
;      the database filename, but with .TXT extensions.
;
;   4. due to speed considerations when processing large
;      database files, there is no GUI output.
;
;   Tested & working on Windows 8.1 and MacOS Catalina,
;   both running PureBasic version 5.71 LTS (x64).
;
;   by Syed Ibrahim (TI-994A) - free to use, improve, share...
;
;   26th February 2020
;==============================================================

EnableExplicit

UseSQLiteDatabase()

Enumeration  
  #db
  #json
  
  #guiTimer = 0  
  
  #mainWindow = 0
  
  #dbFileLabel = 0
  #jsonFileLabel
  #openDBFileButton
  #exportJSONFileButton
  
EndEnumeration

Define wFlags, appQuit, process, databaseFileName.s, ofrPattern.s

Procedure Export2Json(databaseFileName.s)
  
  Protected.s databaseQuery, databaseColumnName, jsonFileName
  Protected i, blobBase64Size, *blobBase64Data, *databaseColumnBlobData
  Protected databaseTableCount, databaseColumnCount, databaseColumnIndex, databaseColumnBlobSize
  Protected jsonObject, jsonTableArray, jsonTableElements, jsonTableElement, jsonTableElementName
  
  ; open the given database and create a new JSON data object
  If (OpenDatabase(#db, databaseFileName, "", "") And CreateJSON(#json))
    
    ; get the number of tables in the database
    databaseQuery = "SELECT count(*) FROM sqlite_master WHERE type='table' AND " +
                    "name != 'android_metadata' AND name NOT LIKE 'sqlite_%';"
    
    If DatabaseQuery(#db, databaseQuery)    
      
      If NextDatabaseRow(#db)
        
        databaseTableCount = GetDatabaseLong(#db, 0) - 1       
        
        ; get all the table names from the database
        databaseQuery = "Select name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"
        
        If DatabaseQuery(#db, databaseQuery)          
          
          Dim tableNames.s(databaseTableCount)
          
          ; save the table names into an array
          While NextDatabaseRow(#db)
            tableNames(i) = GetDatabaseString(#db, 0)
            i + 1            
          Wend
          
          ; set the newly created JSON data object as a JSON object
          jsonObject = SetJSONObject(JSONValue(#json))
          
          ; instantiate and insert a JSON array node into
          ; the JSON object with the KEY name 'Tables'
          jsonTableArray = SetJSONArray(AddJSONMember(jsonObject, "Tables"))
          
          For i = 0 To databaseTableCount
            
            ; write the table names as KEY names for the JSON array elements
            SetJSONString(AddJSONElement(jsonTableArray), tableNames(i))
            
            ; get all the records from the currently selected table
            databaseQuery = "SELECT * FROM " + tableNames(i)     
            If DatabaseQuery(#db, databaseQuery)      
              
              ; get the total number of columns of the currently selected table
              databaseColumnCount = DatabaseColumns(#db)
              
              While NextDatabaseRow(#db)
                
                ; insert a new JSON array element node                
                jsonTableElements = AddJSONElement(jsonTableArray)
                
                ; set a new JSON object into the JSON array element node
                jsonTableElement = SetJSONObject(jsonTableElements) 
                
                For databaseColumnIndex = 0 To databaseColumnCount - 1
                  
                  ; get the current column name of the currently selected table
                  databaseColumnName = DatabaseColumnName(#db, databaseColumnIndex)
                  
                  ; set the column name as the KEY name for the JSON object entry
                  jsonTableElementName = AddJSONMember(jsonTableElement, databaseColumnName)
                  
                  ; set the column value as the VALUE for the JSON object entry
                  ; according to the respective column value data type
                  Select DatabaseColumnType(#db, databaseColumnIndex)                     
                                            
                    Case #PB_Database_Long 
                      SetJSONInteger(jsonTableElementName, 
                                     GetDatabaseLong(#db, databaseColumnIndex))
                      
                    Case #PB_Database_Double 
                      SetJSONDouble(jsonTableElementName, 
                                    GetDatabaseDouble(#db, databaseColumnIndex))
                      
                    Case #PB_Database_Float  
                      SetJSONFloat(jsonTableElementName, 
                                   GetDatabaseFloat(#db, databaseColumnIndex))
                      
                    Case #PB_Database_Quad 
                      SetJSONQuad(jsonTableElementName, 
                                  GetDatabaseQuad(#db, databaseColumnIndex))
                      
                    Case #PB_Database_String                      
                      SetJSONString(jsonTableElementName, 
                                    GetDatabaseString(#db, databaseColumnIndex))
                      
                    Case #PB_Database_Blob
                      ; blobs are converted to Base64 ASCII strings
                      databaseColumnBlobSize = DatabaseColumnSize(#db, databaseColumnIndex)
                      
                      If databaseColumnBlobSize
                        *databaseColumnBlobData = AllocateMemory(databaseColumnBlobSize)    
                        
                        If GetDatabaseBlob(#db, databaseColumnIndex, 
                                           *databaseColumnBlobData, 
                                           databaseColumnBlobSize)                          
                                                    
                          blobBase64Size = databaseColumnBlobSize * 1.4
                          *blobBase64Data = AllocateMemory(blobBase64Size)                          
                          blobBase64Size = Base64EncoderBuffer(*databaseColumnBlobData, 
                                                               databaseColumnBlobSize, 
                                                               *blobBase64Data, blobBase64Size)                          
                          SetJSONString(jsonTableElementName, 
                                        PeekS(*blobBase64Data, blobBase64Size, #PB_Ascii))
                          
                          FreeMemory(*databaseColumnBlobData)
                          FreeMemory(*blobBase64Data)
                          
                        EndIf
                        
                      EndIf
                      
                  EndSelect         
                  
                Next         
                
              Wend
              
              FinishDatabaseQuery(#db)  
              
            EndIf      
            
          Next
          
          ; the resultant JSON output is written to a file with the
          ; same name as the database file but with a .TXT extension
          jsonFileName = GetPathPart(databaseFileName) + 
                         GetFilePart(databaseFileName, #PB_FileSystem_NoExtension) + ".txt"

          If SaveJSON(#json, jsonFileName, #PB_JSON_PrettyPrint)
            SetGadgetText(#jsonFileLabel, "exported JSON file: " + jsonFileName)
            SetGadgetText(#dbFileLabel, "currently selected database: NONE")
            MessageRequester("Database to JSON", "Export completed!" + #CRLF$ + jsonFileName)
          EndIf                    
          
        EndIf  
        
      EndIf  
      
    EndIf  
    
    FreeJSON(#json)
    CloseDatabase(#db)    
    
  EndIf  
  
EndProcedure

Procedure readDatabase(databaseFileName.s) 
  
  If databaseFileName <> ""
    Export2Json(databaseFileName)
    DisableGadget(#openDBFileButton, #False)
    DisableGadget(#exportJSONFileButton, #False)
  EndIf 
  
EndProcedure

wFlags = #PB_Window_SystemMenu | #PB_Window_ScreenCentered
OpenWindow(#mainWindow, 0, 0, 600, 250, "Database to JSON Exporter", wFlags)
ButtonGadget(#openDBFileButton, 20, 20, 560, 50, "SELECT DATABASE")
ButtonGadget(#exportJSONFileButton, 20, 130, 560, 50, "EXPORT TO JSON FILE")
TextGadget(#dbFileLabel, 20, 80, 560, 40, "currently selected database: NONE", #PB_Text_Center)
TextGadget(#jsonFileLabel, 20, 190, 560, 40, "exported JSON file: NONE", #PB_Text_Center)
SetGadgetColor(#dbFileLabel, #PB_Gadget_BackColor, RGB(255, 255, 255))
SetGadgetColor(#jsonFileLabel, #PB_Gadget_BackColor, RGB(255, 255, 255))

Repeat
  
  Select WaitWindowEvent()
      
    Case #PB_Event_CloseWindow
      appQuit = #True
      
    Case #PB_Event_Gadget
      Select EventGadget()
          
        Case #openDBFileButton
          ofrPattern = "SQLite (*.sqlite)|*.sqlite|SQL (*.sql)|*.sql|Database (*.db)|*.db"
          databaseFileName = OpenFileRequester("Select SQLite Database:", "", ofrPattern, 0)     
          If databaseFileName = ""
            SetGadgetText(#dbFileLabel, "currently selected database: NONE")
          Else
            SetGadgetText(#dbFileLabel, "currently selected database: " + databaseFileName)
          EndIf
          
        Case #exportJSONFileButton
          If databaseFileName <> ""
            process = #True
            SetGadgetText(#jsonFileLabel, "processing " + GetFilePart(databaseFileName) + "...")
            DisableGadget(#openDBFileButton, #True)  
            DisableGadget(#exportJSONFileButton, #True) 
            AddWindowTimer(#mainWindow, #guiTimer, 1000)
          EndIf
          
      EndSelect         
      
    Case #PB_Event_Timer
      If process
        process = #False
        readDatabase(databaseFileName)
        RemoveWindowTimer(#mainWindow, #guiTimer)
        databaseFileName = "" 
      EndIf  
      
  EndSelect
  
Until appQuit
As always, your feedback and comments are welcome. :D

Re: SQLite Database to JSON File Converter

Posted: Thu Apr 30, 2020 12:17 pm
by blueb
Thanks for this Syed. :D

Sorry for the late response, it works perfectly with the SQLite DB that I tried ( 2 tables and about 10,000 records).

I've never used JSON before, so I'm still trying to see how I can make JSON easier to use in an application.

Where's the best place to start with JSON, and do you think it'd be easier than SQL direct?

blueb

Re: SQLite Database to JSON File Converter

Posted: Thu Apr 30, 2020 12:36 pm
by infratec
JSON is not comparable or usable as a database :wink:

It is only useful (in my opinion) for data exchange.
It only stores datastructures in a text.

Maybe as replacement for 'Preference files'.

Many REST APIs uses JSON as dataformat.

But you can not make something like queries to a JSON text.

Re: SQLite Database to JSON File Converter

Posted: Thu Apr 30, 2020 12:52 pm
by Little John
By the way ...
; the resultant JSON output is written to a file with the
; same name as the database file but with a .TXT extension
Technically speaking, it's true that JSON files are text files (more precisely: UTF-8 without BOM). However, text files in general can contain various content. JSON files are special text files with a well defined format. Their names normally have the extension ".json" (not ".txt"), so that they can be identified as JSON files already by their name.

Re: SQLite Database to JSON File Converter

Posted: Thu Apr 30, 2020 6:56 pm
by davido
@TI-994A,
I find this very helpful.
Thank you for sharing.

Re: SQLite Database to JSON File Converter

Posted: Sat May 02, 2020 11:17 am
by TI-994A
blueb wrote:...it works perfectly with the SQLite DB that I tried ( 2 tables and about 10,000 records) ...

... Where's the best place to start with JSON, and do you think it'd be easier than SQL direct?
Hi @blueb. Thank you for the feedback.

JSON data could be used as input parameters for dynamic SQL queries, and even as formatted query results, but the two are quite exclusive in functionality. Nevertheless, the ubiquity of the JSON format in REST APIs, as infratec had mentioned, makes it very useful to know.

The PureBasic JSON library does a good job at handling such data, and I had posted a simple tutorial on it, here:

> PureBasic JSON: A Quick Tutorial

Re: SQLite Database to JSON File Converter

Posted: Sat May 02, 2020 11:27 am
by TI-994A
Thank you, @LittleJohn and @davido for your kind feedback.
Little John wrote:... Their names normally have the extension ".json" (not ".txt"), so that they can be identified as JSON files already by their name.
You're absolutely right; I should have made a note of that. The decision to use the text file extension was simply to facilitate the use of standard text editors in case there were no default code editors for the JSON file type installed.