It is currently Sat Feb 29, 2020 5:39 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: SQLite Database to JSON File Converter
PostPosted: Thu Feb 27, 2020 9:21 am 
Offline
Addict
Addict
User avatar

Joined: Sat Feb 19, 2011 3:47 am
Posts: 2260
Location: Singapore
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:
;==============================================================
;   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

_________________
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too!


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 12 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye