GAMBAS BOOK 3.19.5

The Index class represents a table index. The properties can be read for an existing DB table. The class has no methods. An index is used, among other things, to quickly find data records with certain properties.

Information on the Indexes property of the Table.Indexes data type in connection with the definition of an index can be found in the Table class in section 22.4.6.

The Index class has the following properties:

Table 22.4.8.1.1 : Properties of the Index class

Example:

First, a DB table `members` is created in the current database `contact.sqlite` if it does not exist and then an index is created on the field `lastname`. The index properties for the DBIndex with the index name `index_lastname_members` are then read:

    Dim hDBTable As Table
    Dim iCount As Integer = 1  
 
    If Not DBCS.DBConnection.Tables.Exist("members") Then 
 
   '-- Create DB table object - Table name: `members`
       hDBTable = DBCS.DBConnection.Tables.Add("members")
 
   '-- Define DB fields
       With hDBTable.Fields
      '-- gb.Serial => INTEGER PRIMARY KEY AUTOINCREMENT
         .Add("m_id", db.Serial) 
         .Add("lastname", db.String)
         .Add("date", db.Date)
         .Add("description", db.String)
       End With
 
   '-- Define primary key » Field name: `m_id`
       hDBTable.PrimaryKey = ["m_id"]
 
   '-- Create DB table `members` in the database `contacts.sqlite
       hDBTable.Update()
 
   '-- Define DBIndex " Indexname: `index_lastname_members`, Fieldname: `lastname`
       If Not hDBTable.Indexes.Exist("index_lastname_members") Then 
          hDBTable.Indexes.Add("index_lastname_members", ["lastname"])
       EndIf
    Else    
   '-- The existing DB table `members` becomes the current DB table
       hDBTable = DBCS.DBConnection.Tables["members"]
 
    Endif

The following statements read the properties of a specific database index and display them:

  Dim hIndex As Index
  Dim sTableIndexName As String = "index_lastname_members"
  Dim vElement As Variant 
 
  If hDBTable.Indexes.Exist(sTableIndexName) Then      
     hIndex = hDBTable.Indexes[sTableIndexName] 
     Print "Index-Name: "; hIndex.Name
     Print "Is the index a primary index: "; hIndex.Primary
     Print "Table name for this index: "; hIndex.Table.Name
     Print "If the index is declared as 'unique': "; hIndex.Unique
     Print "The index is created via "; hIndex.Fields.Count; IIf(1, " DBFeld", " DBFelder")
     For Each vElement In hIndex.Fields
         Print "Index-FieldName "; iCount; ": "; vElement
         Inc iCount
     Next
  Else
     Print "The table "; hDBTable.Name; " has no index: "; sTableIndexName
  Endif

These are the outputs of the index inspection in the console of the IDE for a DB table:

Index name: index_lastname_members
If the index is a primary index: False
Table name for this index: members
If the index is declared as 'unique': False
The index is formed via 1 DB field.
Index field name 1: lastname

How to delete a database index:

  If DBCS.DBConnection.Tables.Exist("members") Then      
     If hDBTable.Indexes.Exist("index_lastname_members") Then 
        Message.Info("The Index `index_lastname_members` existiert.\nThe index will be deleted!")
        hDBTable.Indexes.Remove("index_lastname_members") 
        hDBTable.Indexes.Refresh()
     Else
        Message.Info("The table " & hDBTable.Name & " has no Index: " & sTableIndexName)
     Endif    
  Endif
 
  Catch
    Message.Error("Error!" & gb.NewLine & Error.Text & gb.NewLine & Error.Where)
    DBCS.DBConnection.Rollback()

Notice:

• You can still insert an index delete query in the above source code to rule out surprises.
• If the database table to which an index belongs is deleted, the index is also automatically deleted!
• For example, if you have created two DB tables table1 and table2 in an SQLite3 database, the index name must be unique. This means that you must not give table table2 an index with the same index name as in table table1.