mORMot Open Source

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

The point for my interest is that I am planning to use a User field in other objects, say to have multiple recipients for a document object, for document flow process, for example, say one user assigns document to several other users of the system for review. In order to link users from TSQLAuthUser to my TMyDocument I will need their Id's, but since common User does not have rights for view of TSQLAuthUser I do not understand at all how to implement that link. In this case the point of using TSQLAuthUser as ancestor for other objects is not achievable. Then I will need to create separate table for my application purposes, to which all users will have read access, and link it somehow to TSQLAuthUsers, since I want to authenticate users using standard authentication scheme.

How exactly and when UserAccessRights should be updated?

I mean when should I use this statement:

Include(fRights.AllowRemoteExecute, reUserCanChangeOwnPassword);

Best regards,
Sergey.

Well, its embarassing, but I cannot go any further.

1. function

Retrieve(aID: TID; Value: TSQLRecord; ForUpdate: boolean=false): boolean; override; 

as stated in the Docs requires ID which I do not have.

2. the example of filling parameters of the SessionUser from the manual (which exploit some undocumented capabilities of retrieve)

fClient.Retrieve('LogonName=?',[TSQLAuthUser],[aUser.LogonName], aUser);

is also working for Admin only, i.e. it does not fill all the properties of aUser object and returns false for any other user.

3. Update like

assert(fClient.Update(aUser));

is working for Admin only again.

Since there were such problems everything was tested with standard TSQLAuthUser object.

Maybe someone could devise working example on how to properly use basic per-user authentication? Including password changing mechanism for non-admin users and user manipulation?

Best regards,

Sergey

Thanks for prompt reply!

Meantime, if I try to get ID by whatever means (while logged in as User, like TSQLAuthUser.GetID, or .ID) the ID is always 0. For Admin it is 0 as well (edited on 19:42).

And this

assert(appClient.UpdateField(TgbSysUser, 'ID', aUser.ID, 'PasswordHASHHexa', aUser.PasswordHashHexa), 'Пароль не изменен!');

is not working, even for Admin.

So I tried update by Logon name because of that. For Admin it is working perfectly.

Once again thank you for reply.

In case there is still interest in subject.

I implement this

approach since 1997 and it never failed me :-)

Well, ID is usually INT or BIGINT, so maximum length of ID key in plain VARCHAR terms will be 20 symbols (for BIGINT ID).

When making treePath I make it with fixed length blocks instead of separating by anything padding zeroes in front of each ID block, so to guess list of 'all parents' chain is quite simple as well as level calculation.

ID(INT)    parentID(INT)   treePath(varchar(500))                isLeaf       name
===========================================================================================
1          NULL            0000000001                            0            the root (1)
2          3               000000000100000000030000000002        0            child (1.3.2)
3          1               00000000010000000003                  1            child (1.3)

isLeaf is usually autocalculated, but can be reset along with treePath calculation procedure.

And to find all children of a certain element I use collation instead of LIKE operator, say to select all children of node ID=1 in above table I would use:

select * from tree where treePath > '0000000001' AND treePath < '0000000001'||'Z'

Never came across tree structure deeper than 25 levels, so treePath = VARCHAR(500) in all my cases (to be on the safe side twice), besides 255 varchars are easily indexed, so VARCHAR(4000) should accomodate 400 levels with INT ID, please correct me if I am wrong.

And nice idea about 62-encoded integer keys, it will still work with all above but with something else but 'Z' for collation.

p.s. I LovE mORMot!