mORMot Open Source

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

Error when creating AuthGroup table in Oracle 11g

When i try to create an External AuthGroup table in oracle, the following error occurs:

ORA-02329: column of datatype LOB cannot be unique or primary key

The create statement constructed is as follows:

SQLite3DB.TSQLRestServerStaticExternal.ExecuteDirect('CREATE TABLE AuthGroup (ID  NUMBER(22,0) PRIMARY KEY, Ident NCLOB NOT NULL UNIQUE,SessionTimeout NUMBER(22,0),AccessRights NCLOB)',(...),(...),False)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Error when creating AuthGroup table in Oracle 11g

You are right: those standards tables were not meant to be defined as external.

TSQLAuthUser and TSQLAuthGroup have now "index ..." attributes to their RawUTF8 properties, to allow direct handling in external databases.
See http://synopse.info/fossil/info/cbea85fb7b

But I'm not sure it is a good idea to use those tables with an external engine.
It should be good to enable caching on their content on server side, otherwise the whole session handling will consume unneeded DB resources.
But caching will be handle only per ID, non per unique property. Declare those tables as member of a SQLite3 table could make sense.

I've added a new optional parameter to the new global function:

function VirtualTableExternalRegisterAll(aModel: TSQLModel;
  aExternalDB: TSQLDBConnectionProperties; DoNotCacheUserGroupTables: boolean=false): boolean;

See http://synopse.info/fossil/info/60faca79f7

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

Re: Error when creating AuthGroup table in Oracle 11g

You are right: those standards tables were not meant to be defined as external.

If they are not meant to be defined as external, I'm not really sure how you see the use of UserAuthentication with these tables when using an external database.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Error when creating AuthGroup table in Oracle 11g

You can have some tables remaining on your server folder, using SQlite3 process, or a very fast in-memory database via TSQLRestServerStaticInMemory.

If you request is that user authentication is part of the main data, not only internal process - e.g. if your login/password is entered by the user - it does make sense to have it on the external DB.
You are right.

I guess this is why I stated the DoNotCacheUserGroupTables: boolean parameter to FALSE by default.

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

Re: Error when creating AuthGroup table in Oracle 11g

ok, thanks