B4J Code Snippet Parse SQL Schema File Into SQLite

When you create a SQLite database with DB Browser for SQLite, you can output a schema file for the database. Instead of manually copying each CREATE line into your source, you can simply use the code below to read and parse the schema file automatically and create the SQLite database inside of and for your app.

'returns a list of string commands you can pass to an SQLite database
Sub ParseSQLSchemaFile (dir As String, fn As String) As List
    Dim txtSql As String
    Dim cmdList As List
    Dim i As Int

    cmdList.Initialize
    If File.Exists(xui.DefaultFolder,fn) Then
        txtSql = File.ReadString(dir, fn)
        Dim sb As StringBuilder
        sb.Initialize
        i = 0
        Do While i < txtSql.Length
            Dim next_char As String = txtSql.CharAt(i)
            If next_char = ";" Then
                sb.Append(next_char)
                cmdList.Add(sb)
                sb.Initialize
            Else If Asc(next_char) <> 10 And Asc(next_char) <> 13 And Asc(next_char) <> 9 Then
                sb.Append(next_char)
            End If
            i=i+1
        Loop
    End If
    Return cmdList
End Sub

'here is an example function making use of it
Sub CreateBaseDB (schemaFile as string, dbfn as string)
    SQL1.InitializeSQLite(xui.DefaultFolder,dbfn,True)
    Dim txtCmdList As List
 
    txtCmdList.Initialize
    txtCmdList = ParseSQLSchemaFile(xui.DefaultFolder, schemaFile)

    For Each cmd As String In txtCmdList
        SQL1.ExecNonQuery(cmd)
    Next
End Sub

Please note that generally (from what I've seen), the DB Browser for SQLite outputs a "BEGIN TRANSACTION;" and "COMMIT;", so you can be sure SQLite will be operating at peak efficiency with this code.

 

[giannimaione]

another code snippets

Public Sub chekTable
    Dim cmd As String
    Dim s As String = File.ReadString(File.DirAssets,"demoSQLite.sql")
    Dim obj() As Object
    obj = Regex.Split (";", s)
   For i=0 To obj.Length-1
        cmd = obj(i)
        cmd = cmd.Replace ("`" , Chr(34))
       If cmd.Length > 2 Then
           Log(cmd)
       Try
            sql.ExecNonQuery (cmd)
        Catch
            Log(LastException.Message)
        End Try
    End If
  Next
End Sub

 

[Erel]

Never call ExecNonQuery multiple times without explicitly creating a transaction.

Best option, which is very very simple, is to use the async batch method.
[B4X] SQL with Wait For

 

[giannimaione]

@Erel you are rigth, but when export schema from "DB Browser" sql file is like this:

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "demo" (
    "id"    INTEGER,
    "field1"    TEXT
);
COMMIT;
CREATE TABLE .....
.....
....
;
COMMIT;

on first line there is BEGIN TRANSACTION;
and on last line COMMIT;

 

[cklester]

@Erel you are rigth, but...
on first line there is BEGIN TRANSACTION;
and on last line COMMIT;

At first, I pulled those lines out and ran the appropriate command:

if cmd = "BEGIN TRANSACTION;" then
   sql1.BeginTransaction

and same for commit. But then I figured there was no reason to do that to properly transacted SQL.

@Erel , let me know if having the BEGIN TRANSACTION; and COMMIT; in the source (string) works exactly the same as doing it manually.

 

[cklester]

    obj = Regex.Split (";", s)

Ha! I looked for how to split a string by some delimiter. Thanks!

        cmd = cmd.Replace ("`" , Chr(34))

What does this do? Why replace the back ticks? What is Chr(34)?

           sql.ExecNonQuery (cmd)

Does this mean I didn't have to remove all the whitespace from the commands?!

 

[giannimaione]

Does this mean I didn't have to remove all the whitespace from the commands?!

yes!

What does this do? Why replace the back ticks? What is Chr(34)?

I do not remember
chr(34) is "double quote"