public Sub CreateTable(SQL As SQL, TableName As String, FieldsAndTypes As Map, PrimaryKey As String)
Dim pk As String, startMap As Int
' // create the table
If Not(TableExist(SQL,TableName)) Then
' // start map at 1
startMap = 1
' // set primary key
If FieldsAndTypes.GetKeyAt(0) = PrimaryKey Then
pk = " PRIMARY KEY"
End If
' // create table and first col
SQL.ExecNonQuery("CREATE TABLE [" & TableName & "] (" & _
FieldsAndTypes.GetKeyAt(0) & " " & _
FieldsAndTypes.GetValueAt(0)& pk & ")")
End If
' // add rest of cols to table
For i = startMap To FieldsAndTypes.Size - 1
Dim field, ftype As String
field = FieldsAndTypes.GetKeyAt(i)
ftype = FieldsAndTypes.GetValueAt(i)
pk = ""
If field = PrimaryKey Then
pk = " PRIMARY KEY"
End If
If Not(FieldExists(SQL,TableName,field)) Then
' // add the colum
Try
SQL.ExecNonQuery("ALTER TABLE [" & TableName & "] ADD COLUMN [" & field & "] " & ftype & pk)
Catch
Log("CreateTable() " & field & " already exists")
End Try
End If
Next
End Sub
public Sub FieldExists(SQL As SQL, TableName As String, TableField As String) As Boolean
Dim result As Boolean
result = False
Dim t As List
t = ExecuteMemoryTable(SQL, "PRAGMA table_info ('" & TableName & "')", Null, 0)
For i = 0 To t.Size - 1
Dim values() As String
values = t.Get(i)
If values(1).ToLowerCase = TableField.ToLowerCase Then ' case insensitive
result = True
Exit
End If
Next
Return result
End Sub
