mORMot Open Source

JonG

Member

Registered: 2010-11-12

Posts: 7

Proposed new overloaded CreateSQLIndex method

function TSQLRestServerDB.CreateSQLIndex(Table: TSQLRecordClass;
                                                      const IndexName : RawUTF8;
                                                      const FieldNames: array of Const;
                                                      Unique: boolean): boolean;
var SQL, SQLTableName: RawUTF8;
    TableIndex: integer;
    FieldNamesStr : RawUTF8;
    i : integer;
    sr : RawUTF8;
    IdxNameStr : RawUTF8;
    BuildIdxNameStr : Boolean;
begin
    Result := False;
    TableIndex := Model.GetTableIndex(Table);

    if (TableIndex<0) or
       ((fStaticData<>nil) and (fStaticData[TableIndex]<>nil)) then
      exit; // invalid Table or in Static data (index not needed)
    IdxNameStr := IndexName;

    BuildIdxNameStr := (IdxNameStr = '');

    for I := low(FieldNames) to High(FieldNames) do begin
      VarRecToUTF8(FieldNames[i],sr);
      if sr = '' then Exit;

      if  (Table.FieldIndex(sr)<0) then
        Raise Exception('Invalid Fieldname: "'+sr+'"')
      else begin
        FieldNamesStr := FieldNamesStr+', "'+sr+'"';
        if BuildIdxNameStr then
          IdxNameStr := IdxNameStr + '_' + sr;
        end;
      end;
    if FieldNamesStr = '' then
      Exit;

    SQLTableName := Table.SQLTableName;

    if BuildIdxNameStr then begin
      System.Delete(IdxNameStr,1,1);
      IdxNameStr := SQLTableName+'IdxBy'+IdxNameStr;
      end;

    System.Delete(FieldNamesStr,1,2);

    if Unique then
      SQL := 'UNIQUE ' else
      SQL := '';
    SQL := FormatUTF8('CREATE %INDEX IF NOT EXISTS % ON %(%);',
      [SQL,IdxNameStr,SQLTableName,FieldNamesStr]);
    result := EngineExecuteAll(SQL);
end;

I borrowed heavily from the existing method of the same name.   
Advantages: 1. This method allows indexes to be made up of more than one field. 2. Allows you to specify (or not) the index name.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Proposed new overloaded CreateSQLIndex method

I'm not sure it's so adequate.

Because we've to handle indexes individually, per record and per field
For example, when the database schema is updated, the TSQLRecord.InitializeTable() method is triggered. This method is virtual, and must be overridden to handle indexes on table or individual field creation.

IMHO field creation is not a common task, and don't need such a super-powerful method.