Android Question B4XTable, attach memory DB to main SQLite (file based) DB

[RB Smissaert]

Trying to run a query on the B4XTable memory DB and move the resulting data directly to a table in the main SQLite (file based) table.
I trying to do this by attaching the B4XTable memory DB to the main DB and then running that query from the main DB.
The reason for doing it like this is that the normal way (moving the data via a list and setting the data with B4XTable SetData) can be problematic
with a large data amount of data.

Attaching the memory database to the main database works fine (checked with PRAGMA database_list, run from the main DB), but the query to move
the data fails with this error:

net.sqlcipher.database.SQLiteException: no such table: mem_19.data: insert into GAC select c2, count(*) as cnt from mem_19.data group by c2 order by cnt asc

GAC (group and count) is the table in the main DB and mem_19 is the name given to the attached memory DB.
19 is the B4XTable Tag.

Running select c2, count(*) as cnt from data group by c2 order by cnt asc the normal way (not attaching the memory DB) works all fine.

My question is if anybody has been able to do the above, so attaching the B4XTable memory DB to a file based DB and move data across via SQL.
If so, I would be very interested how it was done.

RBS

 

[Erel]

B4XTable db can't be so large as it is stored in memory, and reading from the memory db should also be very fast.

Have you created a transaction for all the insert commands? Or better use ExecNonQueryBatch?

 

[RB Smissaert]

B4XTable db can't be so large as it is stored in memory, and reading from the memory db should also be very fast.

Have you created a transaction for all the insert commands? Or better use ExecNonQueryBatch?

Yes, all the inserts are done via ExecNonQueryBatch.
To avoid OOM errors I added some code to the Sub SetData in the B4XTable class:

Public Sub SetData(Data As List) As ResumableSub
    
    Dim n As Int
    Dim rs As ResumableSub =  SetNewMemoryDB
    Wait For (rs) Complete (bResult As Boolean)
    
    CreateTable
    
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("INSERT INTO data VALUES (")
    
    For Each Column As B4XTableColumn In Columns
        If Column.ColumnType = COLUMN_TYPE_VOID Then Continue
        sb.Append("?,")
    Next
    
    sb.Remove(sb.Length - 1, sb.Length) 'take the last comma off
    sb.Append(")")                      'add the final bracket
    
    If Data.Size > 0 Then
        If Data.Get(0) Is List Then
            For Each lrow As Object In Data
                n = n + 1
                sql1.AddNonQueryToBatch(sb.ToString, lrow) 'lrow will be a list
                If n = 100000 Then 'to avoid an OOM error!
                    n = 0
                    Dim SenderFilter As Object = sql1.ExecNonQueryBatch("SQL1")
                    Wait For (SenderFilter) SQL1_NonQueryComplete (Success As Boolean)
                End If
            Next
        Else
            For Each row() As Object In Data
                n = n + 1
                sql1.AddNonQueryToBatch(sb.ToString, row)  'row will be a 1D string array
                If n = 100000 Then 'to avoid an OOM error!
                    n = 0
                    Dim SenderFilter As Object = sql1.ExecNonQueryBatch("SQL1")
                    Wait For (SenderFilter) SQL1_NonQueryComplete (Success As Boolean)
                End If
            Next
        End If
    End If
    
    'Log("SetData, sb.ToString: " & sb.ToString)
    
    Dim SenderFilter As Object = sql1.ExecNonQueryBatch("SQL1")
    
'    Dim n As Long = DateTime.Now
    Wait For (SenderFilter) SQL1_NonQueryComplete (Success As Boolean)
'    Log($"Set data time: ${DateTime.Now - n}ms"$)
    CountAll = Data.Size
    mCurrentCount = CountAll
    miCountOfPages = Ceil(mCurrentCount / mRowsPerPage)

    RefreshNow
    
    Return True
    
End Sub

I also added some code to limit the size of the list passed to SetData, eg by reading a large file in chunks.
This is the code that get the file parts to read:

Sub GetTextFileParts(strFolder As String, strFile As String, btEndOfLineByte As Byte) As Long()
    
    Dim i As Int
    Dim n As Int
    Dim iParts As Int
    Dim iBytesInPart As Int
    Dim iBytes As Int
    Dim lPosition As Long
    Dim arrPartEnds() As Long
    
    RAF.Initialize(strFolder, strFile, True)
    
    If RAF.Size <= iMaxTextFileSize Then
        Return arrPartEnds
    End If
    
    iParts = (RAF.Size / iMaxTextFileSize) + 1
    iBytesInPart = RAF.Size / iParts
    Dim arrPartEnds(iParts) As Long
    
    For i = 0 To iParts - 2
        arrPartEnds(i) = iBytesInPart * (i + 1)
    Next
    
    'index of last byte in the file
    arrPartEnds(iParts - 1) = RAF.Size - 1
    
    For i = 0 To iParts - 2 'note last element of arrPartEnds remains the same
        Dim arrBytes(iBytesToCheckForLinebreaks) As Byte
        lPosition = arrPartEnds(i) - iBytesToCheckForLinebreaks / 2
        iBytes = RAF.ReadBytes(arrBytes, 0, iBytesToCheckForLinebreaks, lPosition)
        For n = 0 To iBytes - 1
            If arrBytes(n) = btEndOfLineByte Then
                arrPartEnds(i) = lPosition + n
                Exit
            End If
        Next
    Next
    
    RAF.Close
    
    Return arrPartEnds
    
End Sub

All this works well and I can show a 625 Mb file via B4XTable.

There are situation though where it seems setting the B4XTable data via SetData with a passed list is inefficient and it could be done better with direct data
transfer via an attached DB and I am still interested to see if this can be done.
I understand that an in-memory DB can't be attached to another in-memory DB, so I have to do this via the regular file based application SQLite DB.

RBS

 

[RB Smissaert]

Yes, all the inserts are done via ExecNonQueryBatch.
To avoid OOM errors I added some code to the Sub SetData in the B4XTable class:

Public Sub SetData(Data As List) As ResumableSub
   
    Dim n As Int
    Dim rs As ResumableSub =  SetNewMemoryDB
    Wait For (rs) Complete (bResult As Boolean)
   
    CreateTable
   
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("INSERT INTO data VALUES (")
   
    For Each Column As B4XTableColumn In Columns
        If Column.ColumnType = COLUMN_TYPE_VOID Then Continue
        sb.Append("?,")
    Next
   
    sb.Remove(sb.Length - 1, sb.Length) 'take the last comma off
    sb.Append(")")                      'add the final bracket
   
    If Data.Size > 0 Then
        If Data.Get(0) Is List Then
            For Each lrow As Object In Data
                n = n + 1
                sql1.AddNonQueryToBatch(sb.ToString, lrow) 'lrow will be a list
                If n = 100000 Then 'to avoid an OOM error!
                    n = 0
                    Dim SenderFilter As Object = sql1.ExecNonQueryBatch("SQL1")
                    Wait For (SenderFilter) SQL1_NonQueryComplete (Success As Boolean)
                End If
            Next
        Else
            For Each row() As Object In Data
                n = n + 1
                sql1.AddNonQueryToBatch(sb.ToString, row)  'row will be a 1D string array
                If n = 100000 Then 'to avoid an OOM error!
                    n = 0
                    Dim SenderFilter As Object = sql1.ExecNonQueryBatch("SQL1")
                    Wait For (SenderFilter) SQL1_NonQueryComplete (Success As Boolean)
                End If
            Next
        End If
    End If
   
    'Log("SetData, sb.ToString: " & sb.ToString)
   
    Dim SenderFilter As Object = sql1.ExecNonQueryBatch("SQL1")
   
'    Dim n As Long = DateTime.Now
    Wait For (SenderFilter) SQL1_NonQueryComplete (Success As Boolean)
'    Log($"Set data time: ${DateTime.Now - n}ms"$)
    CountAll = Data.Size
    mCurrentCount = CountAll
    miCountOfPages = Ceil(mCurrentCount / mRowsPerPage)

    RefreshNow
   
    Return True
   
End Sub

I also added some code to limit the size of the list passed to SetData, eg by reading a large file in chunks.
This is the code that get the file parts to read:

Sub GetTextFileParts(strFolder As String, strFile As String, btEndOfLineByte As Byte) As Long()
   
    Dim i As Int
    Dim n As Int
    Dim iParts As Int
    Dim iBytesInPart As Int
    Dim iBytes As Int
    Dim lPosition As Long
    Dim arrPartEnds() As Long
   
    RAF.Initialize(strFolder, strFile, True)
   
    If RAF.Size <= iMaxTextFileSize Then
        Return arrPartEnds
    End If
   
    iParts = (RAF.Size / iMaxTextFileSize) + 1
    iBytesInPart = RAF.Size / iParts
    Dim arrPartEnds(iParts) As Long
   
    For i = 0 To iParts - 2
        arrPartEnds(i) = iBytesInPart * (i + 1)
    Next
   
    'index of last byte in the file
    arrPartEnds(iParts - 1) = RAF.Size - 1
   
    For i = 0 To iParts - 2 'note last element of arrPartEnds remains the same
        Dim arrBytes(iBytesToCheckForLinebreaks) As Byte
        lPosition = arrPartEnds(i) - iBytesToCheckForLinebreaks / 2
        iBytes = RAF.ReadBytes(arrBytes, 0, iBytesToCheckForLinebreaks, lPosition)
        For n = 0 To iBytes - 1
            If arrBytes(n) = btEndOfLineByte Then
                arrPartEnds(i) = lPosition + n
                Exit
            End If
        Next
    Next
   
    RAF.Close
   
    Return arrPartEnds
   
End Sub

All this works well and I can show a 625 Mb file via B4XTable.

There are situation though where it seems setting the B4XTable data via SetData with a passed list is inefficient and it could be done better with direct data
transfer via an attached DB and I am still interested to see if this can be done.
I understand that an in-memory DB can't be attached to another in-memory DB, so I have to do this via the regular file based application SQLite DB.

RBS

Still not figured out how run SQL involving the B4XTable memory DB, attached to the main SQLite, file based DB.
However, I am thinking now that using a file based SQLite DB instead of that memory DB, makes it all simple. Have run some tests with this
where the B4XTable class has the option to either use a memory DB or a file based DB and compared these two. Writing a very large (say over
500 Mb) text file is about 50% slower, but operations involving 2 tables eg:

        If oB4XTable.SearchConditions = 0 Then
            strSQL = "insert into " & strSQLTableNameGAC & " select " & strField & ", count(*) As cnt from " & strSQLTableName & _
                 " group by " & strField & " order by cnt desc"
            cConn.ExecNonQuery(strSQL)
        Else
            arrArgs = oB4XTable.marrSearchArgs
            strSQL = "insert into " & strSQLTableNameGAC & " select " & strField & ", count(*) as cnt from " & strSQLTableName & _
                 " "  & oB4XTable.strCurrentWhereClause & " group by " & strField & " order by cnt desc"
            cConn.ExecNonQuery2(strSQL, arrArgs)
        End If

can be twice as fast as we don't have to transfer the data via a list or a recordset.

I think the B4XTable has the memory DB for speed reasons, but I might be mistaken there, but to me it seems using a file based DB
might be the better option. In any case it is useful to have both options.

RBS