SQLite

[Got2BeFree]

When creating an SQLite table from within Gambas, I'd like to mark one of the columns UNIQUE (such as column name "name" in the example code below). I'm not having any success. Is there a way to do that? I know I could check what's being entered against existing rows to make sure the new data is unique.

Code: Select all

    If Not MGlobal.hDB.Tables.Exist("test") Then
        dbTable = MGlobal.hDB.Tables.Add("test")
        dbTable.Fields.Add("id", DB.Integer)
        dbTable.Fields.Add("name", DB.String, 50)
        dbTable.Fields.Add("url", DB.String, 255)
        dbTable.Fields.Add("show", DB.Integer)
        dbTable.PrimaryKey = ["id"]
        dbTable.Update()
    End If

[Quincunxian]

Hi - I'd try creating the name field as an additional index.
You can read about it here on the SQLite site.

As a side note, you can create tables by storing the creation script as a file, reading it in and executing it as a SQL command.

This is an example that I've used to create a 'Category' table.

CREATE TABLE 'Category' (
'Id' INTEGER PRIMARY KEY,
'CategoryName' VARCHAR(128),
'ParentId' INTEGER,
'CategoryUse' INTEGER,
'Information' TEXT,
'DateCreated' DATE,
'DateChanged' DATE);

I find this better than using the code creation method as you can edit an existing file and tweak for new fields and name and it takes far less time to create large table sets.

If you have an existing database with a lot of tables, you can create a backup of all your tables with this subroutine:

Public Function CreateSQLiteScriptText(DropTableClause As Boolean, InName As String) As String

  Dim TmpLine As String
  Dim KeyStr As String = ""
  Dim LastLine As Integer
  Dim TmpTable As Table
  Dim TmpField As Field
  Dim ReturnStr As String = ""

  TmpTable = $Con.Tables[InName]

  If DropTableClause Then TmpLine = "DROP TABLE IF EXISTS '" & InName & "';" & Gb.NewLine

  TmpLine &= "CREATE TABLE '" & InName & "' (" & Gb.NewLine

  LastLine = TmpTable.Fields.Count - 1

  For Each TmpField In TmpTable.Fields
    KeyStr = ""
    TmpLine &= "'" & TmpField.Name & "' "

    If CheckPrimaryKey(TmpTable, TmpField.Name) <> "" Then KeyStr = " PRIMARY KEY"

    If TmpField.Type = Gb.String Then
      If TmpField.Length = 0 Then
        TmpLine &= "TEXT"
      Else
        TmpLine &= GetFieldType(False, TmpField.Type) & "(" & TmpField.Length & ")" & KeyStr
      Endif
    Else
      TmpLine &= GetFieldType(False, TmpField.Type) & KeyStr
    Endif

    If LastLine > 0 Then
      TmpLine &= "," & Gb.NewLine
    Else
      TmpLine &= ");" & Gb.NewLine
    Endif
    LastLine -= 1
    ReturnStr &= TmpLine
    TmpLine = ""
  Next
Finally
  Return ReturnStr
Catch
  Message( Error.Text & Gb.CrLf &  Error.Backtrace)

End

...and create them all with this one.

Public Sub CreateTablesFromSuppliedScripts(InFolder As String, ByRef $InCon As Connection)

  Dim TmpFile As String
  Dim FilesAry As String[]
  Dim SQLCommand As String

  FilesAry = Dir(InFolder, "*.sql", Gb.File)
  If FilesAry.Count > 0 Then
    If Not $InCon.Opened Then $InCon.Open
    For Each TmpFile In FilesAry
      SQLCommand = File.Load(InFolder &/ TmpFile) ' Load the entire script from the file
      $InCon.Exec(SQLCommand)          'Execute the Create Table script.
    Next
    $InCon.Close
  Endif
Catch
  Message( Error.Text & GB.CrLf & Error.Backtrace)

End

[Got2BeFree]

Thanks Quin! I had created db in code (or with DB Browser for SQLite) for so long I had forgotten I can create a db using a script file in Gambas. After digging a little more I realized that I can create a unique index with code. Not exactly what I wanted, but just as good. After giving my use case more thought I decided I really don't even need a unique field since my db will contain just a few (maybe 6-8) records and it won't matter if any records are duplicates.

I'll file your information away somewhere for later use (or forgetting! ).

[Quincunxian]

No problems.
I do a lot of work with SQLite but nothing so complex or large that would require multiple indexes to speed up search functions.
Instead, I found that a bit of effort on good validation in the input form is a better solution and you can give you users a better constructed error message to fix the issue.