mORMot Open Source

larand54

Member

Registered: 2018-12-25

Posts: 104

Multiple field index

Is there a way to have mormot create one index using two or more fields?

---

Delphi-11, WIN10

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,653

Website

Re: Multiple field index

Did you make some search first?

It sounds like if IRestOrmServer.CreateSqlMultiIndex() is what you are looking for.

larand54

Member

Registered: 2018-12-25

Posts: 104

Re: Multiple field index

That looks good, I will soon test it.

---

Delphi-11, WIN10

sag2007

Member

From: Moscow, RU

Registered: 2015-09-23

Posts: 12

Re: Multiple field index

Good day!

I am trying to create unique index on two fields Name and SubSystem for TFolder class:

  TSubSystem = class(TOrm)
    private
      FName: RawUtf8;
    published
      property Name: RawUtf8 read FName write FName stored AS_UNIQUE;
  end;

  TFolder = class(TOrm)
    private
      FName: RawUtf8;
      FSubsystem: TSubSystem;
    published
      property Name: RawUtf8 read FName write FName;
      property Subsystem: TSubSystem read FSubsystem write FSubsystem;
      // NEED TO Define unique constraint on Name and Subsystem together
  end;

  TRoleBaseModel = class(TOrmModel)
    public
      constructor Create; reintroduce;
  end;

  TRoleBaseServer = class(TRestServerDB)
  public
    constructor Create(const aModel : TRoleBaseModel; aDBFileName: TFileName); reintroduce;
  end;

I am trying to do it recommended way:

procedure TAppMain.FormCreate(Sender: TObject);
begin

  AppModel := CreateRoleBaseModel;

  AppServer := TRoleBaseServer.Create(AppModel, '');

  // Create the unique index

  try

    if AppServer.CreateSqlMultiIndex(TFolder, ['Name', 'Subsystem'], true, 'UniqueFolderIndex') then
      ShowMessage('Unique index created successfully.')
    else
      ShowMessage('Failed to create unique index.');

  except
    on E: Exception do
      Writeln('Error creating unique index: ' + E.Message);
  end;

  AppServer.CreateMissingTables;

  AppClient := TRoleBaseClient.Create(AppServer);

end;

On the first pass, when there is no database file and tables it reports 'Failed to create unique index' with no exception error. The rest is created as it should.

On the second try, when there is db and tables, it says 'Unique index created successfully', but index is not created.

At the same time,

AppServer.Execute('CREATE UNIQUE INDEX AppMemberIndex_uq ON Folder (Name, Subsystem);');

works fine.

What is wrong with me??
===================
D12.2 MORMOT2.2 stable

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: Multiple field index

What is wrong with me??

The order of call is incorrect. You must call CreateMissingTables first and then CreateSqlMultiIndex. Look in unit test.orm.extdb.pas to see how it is implemented there. Or do it in the function InitializeTable of the ORM object.

With best regards
Thomas

sag2007

Member

From: Moscow, RU

Registered: 2015-09-23

Posts: 12

Re: Multiple field index

Thank you so much!