SQLite Database to JSON File Converter
Posted: Thu Feb 27, 2020 9:21 am
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.
As always, your feedback and comments are welcome.
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