B4J Code Snippet Parse SQL Schema File Into SQLite

cklester

Active Member
Licensed User
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.

B4X:
'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

Well-Known Member
Licensed User
another code snippets
B4X:
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
 

giannimaione

Well-Known Member
Licensed User
@Erel you are rigth, but when export schema from "DB Browser" sql file is like this:
B4X:
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

Active Member
Licensed User
@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:

B4X:
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.
 
Top