I have made a workaround that allows database like functions using a jsonl file. I am sorry that I don't have time to refine it more for a public display, but I wanted to share it regardless. It works.
[CODE lang="b4x" title="sqlite workaround"]'ChatHistoryDB - JSONL File-Based Drop-In Replacement
'Stores chat history, tool calls/responses, and system prompts as JSON lines on microSD card
'Same API as SQL version - code doesn't know the difference
'FIXED: Added storage parameter and SetCampaignFolder method
'FIXED: Robust JSON string escaping (handles backslashes, quotes, newlines, tabs, carriage returns)
Sub Class_Globals
Private storage As ExternalStorage
Private campaignFolder As ExternalFile
Private Const ChatFileName As String = "chat_history.jsonl"
' Escaping constants for JSON safety
Private Const BACKSLASH As String = "\"
Private Const JSON_QUOTE As String = """"
Private Const JSON_BACKSLASH As String = "\\"
Private Const JSON_NEWLINE As String = "\n"
Private Const JSON_TAB As String = "\t"
Private Const JSON_CARRIAGE_RETURN As String = "\r"
End Sub
Public Sub Initialize
Log("ChatHistoryDB Initialized")
storage.Initialize(Me, "Storage")
End Sub
Public Sub InitializeDatabase(campaignFolder_Param As ExternalFile, storage_param As ExternalStorage) As Boolean
Try
storage = storage_param
campaignFolder = campaignFolder_Param
If Not(campaignFolder.IsInitialized) Then
Log("
ERROR: Campaign folder not initialized")
Return False
End If
If Not(storage.IsInitialized) Then
Log("
ERROR: Storage not initialized")
Return False
End If
Log("
Attempting to initialize database...")
Log(" Folder: " & campaignFolder.Name)
Log(" DB File: " & ChatFileName)
' File will be created on first write
Log("
ChatHistoryDB initialized successfully!")
Log(" Using JSONL format on microSD card")
Log(" File: " & campaignFolder.Name & "/" & ChatFileName)
Return True
Catch
Log("
ERROR initializing ChatHistoryDB")
Log(" Exception: " & LastException.Message)
Return False
End Try
End Sub
' ========================================
' ROBUST JSON STRING ESCAPING
' ========================================
' Escapes special characters for valid JSON output
' Handles: quotes, backslashes, newlines, tabs, carriage returns
' Returns: Safe-for-JSON string
Private Sub EscapeJsonString(s As String) As String
If s = "" Then Return ""
Dim sb As StringBuilder
sb.Initialize
For i = 0 To s.Length - 1
Dim c As String = s.CharAt(i)
Select c
Case QUOTE ' Double quote (ASCII 34)
sb.Append(JSON_QUOTE) ' Becomes \"
Case BACKSLASH ' Backslash (ASCII 92)
sb.Append(JSON_BACKSLASH) ' Becomes \\
Case Chr(10) ' Newline (LF)
sb.Append(JSON_NEWLINE) ' Becomes \n
Case Chr(13) ' Carriage return (CR)
sb.Append(JSON_CARRIAGE_RETURN) ' Becomes \r
Case Chr(9) ' Tab
sb.Append(JSON_TAB) ' Becomes \t
Case Else
sb.Append(c) ' All other characters unchanged
End Select
Next
Return sb.ToString
End Sub
' Save a single message to database (JSONL format)
Public Sub SaveMessageToDatabase(sessionNum As Int, turnNum As Int, messageIdx As Int, role As String, content As String, isToolCall As Int, isToolResponse As Int)
If Not(storage.IsInitialized) Then
Log("
ERROR: Database not initialized - cannot save message")
Return
End If
Try
' Escape all special characters in content
Dim escapedContent As String = EscapeJsonString(content)
Dim escapedRole As String = EscapeJsonString(role)
' Build JSON line manually with proper escaping
Dim sb As StringBuilder
sb.Initialize
sb.Append("{")
sb.Append(QUOTE & "sessionnumber" & QUOTE & ":").Append(sessionNum).Append(",")
sb.Append(QUOTE & "turnnumber" & QUOTE & ":").Append(turnNum).Append(",")
sb.Append(QUOTE & "messageindex" & QUOTE & ":").Append(messageIdx).Append(",")
sb.Append(QUOTE & "role" & QUOTE & ":" & QUOTE).Append(escapedRole).Append(QUOTE & ",")
sb.Append(QUOTE & "content" & QUOTE & ":" & QUOTE).Append(escapedContent).Append(QUOTE & ",")
sb.Append(QUOTE & "istoolcall" & QUOTE & ":").Append(isToolCall).Append(",")
sb.Append(QUOTE & "istoolresponse" & QUOTE & ":").Append(isToolResponse).Append(",")
sb.Append(QUOTE & "timestamp" & QUOTE & ":" & QUOTE).Append(DateTime.Now).Append(QUOTE)
sb.Append("}")
Dim jsonLine As String = sb.ToString & Chr(10)
' Find or create the file
Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
If ChatFile.IsInitialized Then
' File exists - read and append
Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
Dim reader As TextReader
reader.Initialize(inputStream)
Dim existingContent As String = reader.ReadAll
reader.Close
' Write back with new line appended
Dim outputStream As OutputStream = storage.OpenOutputStream(ChatFile)
Dim writer As TextWriter
writer.Initialize(outputStream)
writer.Write(existingContent & jsonLine)
writer.Close
Else
' Create new file
ChatFile = storage.CreateNewFile(campaignFolder, ChatFileName)
Dim outputStream As OutputStream = storage.OpenOutputStream(ChatFile)
Dim writer As TextWriter
writer.Initialize(outputStream)
writer.Write(jsonLine)
writer.Close
End If
Log("
Message saved: " & escapedRole & " (idx " & messageIdx & ")")
Catch
Log("
Error saving message: " & LastException.Message)
End Try
End Sub
' Retrieve history for AI - returns last N messages (reads backwards from EOF efficiently)
' Fixed: String type conversion for sessionNum, proper JSON key matching, robust escaping
Public Sub RetrieveHistoryForAI(sessionNum As String, turnsBack As Int) As List
Dim messages As List
messages.Initialize
If Not(storage.IsInitialized) Then
Log("
ERROR: Not initialized - cannot retrieve messages")
Return messages
End If
Try
' Validate turnsBack parameter
If turnsBack < 1 Then turnsBack = 10
If turnsBack > 100 Then turnsBack = 100
Log("
Retrieving last " & turnsBack & " messages...")
' Find the chat file
Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
If Not(ChatFile.IsInitialized) Then
Log("
No chat history file found yet")
Return messages
End If
' Read entire file
Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
Dim reader As TextReader
reader.Initialize(inputStream)
Dim content As String = reader.ReadAll
reader.Close
' Split into lines
Dim lines() As String = Regex.Split(Chr(10), content)
Log("
Read " & lines.Length & " lines from chat file")
' Parse lines as JSON, filter by sessionNum, collect last N
Dim lineCount As Int = 0
For i = lines.Length - 1 To 0 Step -1
If messages.Size >= turnsBack Then Exit
Dim line As String = lines(i).Trim
If line = "" Then Continue
Try
Dim parser As JSONParser
parser.Initialize(line)
Dim msg As Map = parser.NextObject
' Key fix: Convert sessionNum from JSON (Int) to String for comparison
Dim msgSessionNum As String = msg.Get("sessionnumber") & ""
If msgSessionNum = sessionNum Then
messages.InsertAt(0, msg) ' Insert at beginning to maintain order
lineCount = lineCount + 1
End If
Catch
' Skip malformed JSON lines
Log("
Skipping malformed line: " & line)
End Try
Next
Log("
Retrieved " & messages.Size & " messages from session " & sessionNum)
Return messages
Catch
Log("
ERROR in RetrieveHistoryForAI: " & LastException.Message)
Return messages
End Try
End Sub
' Get message count for session
Public Sub GetMessageCountForSession(sessionNum As String) As Int
If Not(storage.IsInitialized) Then Return 0
Try
Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
If Not(ChatFile.IsInitialized) Then Return 0
' Read and count lines for this session
Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
Dim reader As TextReader
reader.Initialize(inputStream)
Dim content As String = reader.ReadAll
reader.Close
Dim lines() As String = Regex.Split(Chr(10), content)
Dim countFound As Int = 0
For Each line As String In lines
If line.Trim.Length > 0 Then
Try
Dim parser As JSONParser
parser.Initialize(line)
Dim msg As Map = parser.NextObject
If msg.Get("sessionnumber") = sessionNum Then
countFound = countFound + 1
End If
Catch
' Skip malformed
Log(LastException.Message)
End Try
End If
Next
Log("
Message count for session " & sessionNum & ": " & countFound)
Return countFound
Catch
Log("
Error counting messages: " & LastException.Message)
Return 0
End Try
End Sub
' Get highest turn number for session
Public Sub GetHighestTurnNumber(sessionNum As String) As Int
If Not(storage.IsInitialized) Then Return 0
Try
Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
If Not(ChatFile.IsInitialized) Then Return 0
Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
Dim reader As TextReader
reader.Initialize(inputStream)
Dim content As String = reader.ReadAll
reader.Close
Dim lines() As String = Regex.Split(Chr(10), content)
Dim maxTurn As Int = -1
For Each line As String In lines
If line.Trim.Length > 0 Then
Try
Dim parser As JSONParser
parser.Initialize(line)
Dim msg As Map = parser.NextObject
If msg.Get("sessionnumber") = sessionNum Then
Dim turnNum As Int = msg.Get("turnnumber")
If turnNum > maxTurn Then maxTurn = turnNum
End If
Catch
' Skip malformed
Log(LastException.Message)
End Try
End If
Next
Log("
Highest turn for session " & sessionNum & ": " & maxTurn)
Return maxTurn
Catch
Log("
Error getting highest turn: " & LastException.Message)
Return 0
End Try
End Sub
' Get messages in turn range
Public Sub GetMessagesInTurnRange(sessionNum As String, startTurn As Int, endTurn As Int) As List
Dim messages As List
messages.Initialize
If Not(storage.IsInitialized) Then Return messages
Try
Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
If Not(ChatFile.IsInitialized) Then Return messages
Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
Dim reader As TextReader
reader.Initialize(inputStream)
Dim content As String = reader.ReadAll
reader.Close
Dim lines() As String = Regex.Split(Chr(10), content)
For Each line As String In lines
If line.Trim.Length > 0 Then
Try
Dim parser As JSONParser
parser.Initialize(line)
Dim msg As Map = parser.NextObject
If msg.Get("sessionnumber") = sessionNum Then
Dim turnNum As Int = msg.Get("turnnumber")
If turnNum >= startTurn And turnNum <= endTurn Then
messages.Add(msg)
End If
End If
Catch
' Skip malformed
Log(LastException.Message)
End Try
End If
Next
Log("
Retrieved " & messages.Size & " messages in range " & startTurn & " to " & endTurn)
Return messages
Catch
Log("
Error retrieving turn range: " & LastException.Message)
Return messages
End Try
End Sub
' Get all messages for session
Public Sub GetAllMessagesForSession(sessionNum As String) As List
Dim messages As List
messages.Initialize
If Not(storage.IsInitialized) Then Return messages
Try
Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
If Not(ChatFile.IsInitialized) Then Return messages
Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
Dim reader As TextReader
reader.Initialize(inputStream)
Dim content As String = reader.ReadAll
reader.Close
Dim lines() As String = Regex.Split(Chr(10), content)
For Each line As String In lines
If line.Trim.Length > 0 Then
Try
Dim parser As JSONParser
parser.Initialize(line)
Dim msg As Map = parser.NextObject
If msg.Get("sessionnumber") = sessionNum Then
messages.Add(msg)
End If
Catch
' Skip malformed
Log(LastException.Message)
End Try
End If
Next
Log("
Retrieved " & messages.Size & " total messages for session " & sessionNum)
Return messages
Catch
Log("
Error retrieving all messages: " & LastException.Message)
Return messages
End Try
End Sub
' Get messages matching query
Public Sub GetMessagesMatchingQuery(sessionNum As String, searchTerm As String) As List
Dim messages As List
messages.Initialize
If Not(storage.IsInitialized) Then Return messages
Try
Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
If Not(ChatFile.IsInitialized) Then Return messages
Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
Dim reader As TextReader
reader.Initialize(inputStream)
Dim content As String = reader.ReadAll
reader.Close
Dim lines() As String = Regex.Split(Chr(10), content)
Dim searchLower As String = searchTerm.ToLowerCase
For Each line As String In lines
If line.Trim.Length > 0 Then
Try
Dim parser As JSONParser
parser.Initialize(line)
Dim msg As Map = parser.NextObject
If msg.Get("sessionnumber") = sessionNum Then
Dim msgContent As String = msg.Get("content")
If msgContent <> Null And msgContent.ToLowerCase.Contains(searchLower) Then
messages.Add(msg)
End If
End If
Catch
' Skip malformed
Log(LastException.Message)
End Try
End If
Next
Log("
Found " & messages.Size & " messages matching " & QUOTE & searchTerm & QUOTE)
Return messages
Catch
Log("
Error searching messages: " & LastException.Message)
Return messages
End Try
End Sub
' Get tool calls in range
Public Sub GetToolCallsInRange(sessionNum As String, startTurn As Int, endTurn As Int) As List
Dim toolCalls As List
toolCalls.Initialize
If Not(storage.IsInitialized) Then Return toolCalls
Try
Dim ChatFile As ExternalFile = storage.FindFile(campaignFolder, ChatFileName)
If Not(ChatFile.IsInitialized) Then Return toolCalls
Dim inputStream As InputStream = storage.OpenInputStream(ChatFile)
Dim reader As TextReader
reader.Initialize(inputStream)
Dim content As String = reader.ReadAll
reader.Close
Dim lines() As String = Regex.Split(Chr(10), content)
For Each line As String In lines
If line.Trim.Length > 0 Then
Try
Dim parser As JSONParser
parser.Initialize(line)
Dim msg As Map = parser.NextObject
If msg.Get("sessionnumber") = sessionNum Then
Dim turnNum As Int = msg.Get("turnnumber")
Dim isToolCall As Int = msg.Get("istoolcall")
If turnNum >= startTurn And turnNum <= endTurn And isToolCall = 1 Then
toolCalls.Add(msg)
End If
End If
Catch
' Skip malformed
Log(LastException.Message)
End Try
End If
Next
Log("
Retrieved " & toolCalls.Size & " tool calls")
Return toolCalls
Catch
Log("
Error retrieving tool calls: " & LastException.Message)
Return toolCalls
End Try
End Sub
[/CODE]
