mORMot Open Source

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;

procedure TForm1.LoadPersonList;
var
  PersonRec : TSQLPersonRecord;
begin
  PersonRec:=TSQLPersonRecord.Create(Database,'Name>""',[]);
  Try
    if PersonRec.ID = 0 then
      Exit;
    Repeat
      ListBox1.Items.Append(UTF8ToString(PersonRec.Name));
    until not PersonRec.FillOne;
  Finally
    PersonRec.Free;
  End;

end;