How to create a multi-column unique index?

ma64 · 2011-01-27 03:28:39

Does the framework provide a built-in function for creating unique indexes on multiple columns?
I tried to use the overloaded version of CreateSQLIndex() that takes an array of field names as its second parameter, but instead of a multi-column index it created a separate index for each of the specified fields.

Example:

CreateSQLIndex(TUsers, ['Surname', 'Name', 'Email'], true)

results in the three SQL commands

CREATE UNIQUE INDEX IndexUsersSurname ON Users(Surname)
CREATE UNIQUE INDEX IndexUsersName ON Users(Name)
CREATE UNIQUE INDEX IndexUsersEmail ON Users(Email)

instead of the one that I expected:

CREATE UNIQUE INDEX IndexUsers_Surname_Name_Email ON Users(Surname,Name,Email)

Did I overlook something?

Regards

ab · 2011-01-27 14:13:58

When you're about working with SQL commands inside the framework, a good idea is always to search in SQlite official site.

In this page:
http://www.sqlite.org/lang_createindex.html
you'll find the exact syntax.

 CREATE UNIQUE INDEX IndexUsers_Surname_Name_Email ON Users(Surname,Name,Email)

sounds definitively correct.

There is no built-in function for this multiple-field indexes creations.
But you can do it by hand, on the server side using public EngineExecuteAll or the protected EngineExecute method inside a TSQLRestServer descendant.

ma64 · 2011-01-28 02:37:09

Thanks for explaining!

Since indexes on multiple fields are a fairly common thing when working with databases, a dedicated framework function for the creation of such indexes might be helpful for a lot of users. Here's the method (a modification of the existing CreateSQLIndex()) that I added to SQLite3Commons.pas for that purpose:

function TSQLRestServer.CreateSQLMultiIndex(Table: TSQLRecordClass; const FieldNames: array of RawUTF8; Unique: boolean): boolean;
var
  SQL, SQLTableName: RawUTF8;
  TableIndex: integer;

  function InvalidFieldName(const FieldNames: array of RawUTF8): boolean;
  var
    i: integer;
  begin
    result := false;
    for i := 0 to High(FieldNames) do
      if Table.FieldIndex(FieldNames[i]) < 0 then
        begin
        result := true;
        exit;
        end;
  end;

  function Concat(const FieldNames: array of RawUTF8; Separator: AnsiChar): RawUTF8;
  var
    i, iMax: integer;
  begin
    result := '';
    iMax := High(FieldNames);
    if iMax >= 0 then
      begin
      result := FieldNames[0];
      for i := 1 to iMax do
        result := result + Separator + FieldNames[i];
      end;
  end;

begin
  TableIndex := Model.GetTableIndex(Table);
  if (TableIndex<0) or InvalidFieldName(FieldNames) or
     ((fStaticData<>nil) and (fStaticData[TableIndex]<>nil)) then begin
    result := false; // invalid Table or in Static data (index not needed)
    exit;
  end;
  SQLTableName := Table.SQLTableName;
  if Unique then
    SQL := 'UNIQUE ' else
    SQL := '';
  SQL := FormatUTF8('CREATE %INDEX IF NOT EXISTS Index%_% ON %(%);', [SQL, SQLTableName, Concat(FieldNames, '_'), SQLTableName, Concat(FieldNames, ',')]);
  result := EngineExecuteAll(SQL);
end;

Maybe you find it worth to be integrated into the official code base.

ab · 2011-01-28 07:31:50

Yes, this is a very good idea.

Here is how I've coded this and added to the source code main repository - http://synopse.info/fossil/info/57df69476d

function TSQLRestServer.CreateSQLMultiIndex(Table: TSQLRecordClass;
  const FieldNames: array of RawUTF8; Unique: boolean): boolean;
var SQL, SQLTableName: RawUTF8;
    i, TableIndex: integer;
begin
  result := false;
  if Self=nil then
    exit;
  TableIndex := Model.GetTableIndex(Table);
  if (TableIndex<0) or (high(FieldNames)<0) or
     ((fStaticData<>nil) and (fStaticData[TableIndex]<>nil)) then
    exit; // invalid Table or in Static data (index not needed)
  for i := 0 to high(FieldNames) do
    if Table.FieldIndexFromRawUTF8(FieldNames[i])<0 then
      exit; // wrong field name
  SQLTableName := Table.SQLTableName;
  if Unique then
    SQL := 'UNIQUE ' else
    SQL := '';
  SQL := FormatUTF8('CREATE %INDEX IF NOT EXISTS Index%% ON %(%);',
    [SQL,SQLTableName,RawUTF8ArrayToCSV(FieldNames,''),SQLTableName,
      RawUTF8ArrayToCSV(FieldNames,',')]);
  result := EngineExecuteAll(SQL);
end;

function TSQLRestServer.CreateSQLIndex(Table: TSQLRecordClass; const FieldName: RawUTF8;
  Unique: boolean): boolean;
begin
  result := CreateSQLMultiIndex(Table,[FieldName],Unique);
end;

As you can see, I've made CreateSQLIndex a particular case of CreateSQLMultiIndex.

ma64 · 2011-01-28 17:40:18

Great, Thanks!

CreateSQLIndex() indeed can be seen as special case of CreateSQLMultiIndex().

There is however one small issue with both implementations (yours and mine) of CreateSQLMultiIndex(). Imagine a table with the three fields "cat", "fish" and "catfish" and two indexes created with the following commands:

CreateSQLMultiIndex(TAnimals, [cat, fish], true);
CreateSQLIndex(TAnimals, [catfish], true);

These two commands lead to the same name for both indexes and hence to a database error. Even if a separator is used there is still a chance that an index name collision occurs in case the field names itself contain the separator character. To prevent duplicate index name errors one could perform a

SELECT * FROM sqlite_master WHERE type='index' AND name={name of the index to create}

and if a row is returned then modify the name of the new index. Alternatively indexes generally could be named with increasing numbers (say from "Index000" to "Index999") and before creating a new index the last used index name could be determined with

SELECT MAX(name) FROM sqlite_master WHERE type='index'

Do you think a security check against duplicate index names should be implemented?

ab · 2011-01-29 19:23:48

What about just adding an optional index name parameter?
See http://synopse.info/fossil/info/5fa7c392a0

Since the indexes will be created by the programmer, any duplicate index name could be avoided by setting a custom name for the index, if the automated one could fail.

This is much simple than using a low-level request to get the index list, especially in case of not only SQLite support (for our little mORMot - it's another recursive name "mormot Object Relational Mapping on top" - but not a definitive name, because it's not ready!).

ma64 · 2011-01-30 15:24:36

You're right. Index name checks on the SQL level would require special commands for each supported database engine.
I think it would be a good compromise to add an optional parameter for the index name.

mORMot Open Source

#1 2011-01-27 03:28:39

How to create a multi-column unique index?

#2 2011-01-27 14:13:58

Re: How to create a multi-column unique index?

#3 2011-01-28 02:37:09

Re: How to create a multi-column unique index?

#4 2011-01-28 07:31:50

Re: How to create a multi-column unique index?

#5 2011-01-28 17:40:18

Re: How to create a multi-column unique index?

#6 2011-01-29 19:23:48

Re: How to create a multi-column unique index?

#7 2011-01-30 15:24:36

Re: How to create a multi-column unique index?

Board footer