mORMot Open Source

JonG

Member

Registered: 2010-11-12

Posts: 7

SQLite3 Framework Indexes

Please forgive my ignorance.

If I am using embedded SQLite, how can I execute SQL statements? I want to define some indexes and I don't see the framework way to do it.

Kind regards,
- jon

Last edited by JonG (2010-11-14 09:04:41)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: SQLite3 Framework Indexes

The indexes are created automatically, for properties which need it (TSQLRecord or TRecordReference).

See this method in SQLite3Commons:

    {{ this procedure is created when the associated table is created in the database
      - if FieldIndex is -1, initialization regarding all fields must be made;
        if FieldIndex is specified, initialization regarding this field must be processed
      - override this method in order to initialize indexs or create default records
      - by default, create indexes for all TRecordReference properties, and
        for all TSQLRecord inherited properties (i.e. of sftID type, that is
        an INTEGER field containing the ID of the pointing record) }
    class procedure InitializeTable(Server: TSQLRestServer; const FieldName: ShortString); virtual;

You have dedicated methods to create indexes by hand in TSQLRestServer.CreateSQLIndex.
In this method, you'll see how to execute statements, as shown by this code:

function TSQLRestServer.CreateSQLIndex(Table: TSQLRecordClass; const FieldName: RawUTF8;
  Unique: boolean): boolean;
var SQL, SQLTableName: RawUTF8;
    TableIndex: integer;
begin
  TableIndex := Model.GetTableIndex(Table);
  if (TableIndex<0) or (Table.FieldIndex(FieldName)<0) 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,FieldName,SQLTableName,FieldName]);
  result := EngineExecuteAll(SQL);
end;

All this is described in the documentation of the framework, available from http://synopse.info/forum/viewtopic.php?id=55
Some concepts are available in this documentation, in the SAD document, with all details about the current implementation.
You have some sample sample source code in the repository.
Feel free to ask any question here.

JonG

Member

Registered: 2010-11-12

Posts: 7

Re: SQLite3 Framework Indexes

I had seen EngineExecuteAll but didn't see it in the hierachy of TSQLRest.  Later I saw that the sample code typecasts TSQLRest as TSQLRestServerDB which does have the EngineExecuteAll function.

Thanks!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: SQLite3 Framework Indexes

I had seen EngineExecuteAll but didn't see it in the hierachy of TSQLRest.  Later I saw that the sample code typecasts TSQLRest as TSQLRestServerDB which does have the EngineExecuteAll function.

Indeed. The framework is therefore not bounded to the SQLite engine.
In the upcoming future, we'll add Oracle+MSSQL+MySQL+Firebird support as database engine layer for the framework.

TSQLRestServerDB is the embedded SQLite3 engine. You don't have any server instance, but direct access to the database from your app.
Later on, you can change this embedded behavior into a Client/Server approach, just by changing the class type.

JonG

Member

Registered: 2010-11-12

Posts: 7

Re: SQLite3 Framework Indexes

I had seen EngineExecuteAll but didn't see it in the hierachy of TSQLRest.  Later I saw that the sample code typecasts TSQLRest as TSQLRestServerDB which does have the EngineExecuteAll function.

Indeed. The framework is therefore not bounded to the SQLite engine.
In the upcoming future, we'll add Oracle+MSSQL+MySQL+Firebird support as database engine layer for the framework.

TSQLRestServerDB is the embedded SQLite3 engine. You don't have any server instance, but direct access to the database from your app.
Later on, you can change this embedded behavior into a Client/Server approach, just by changing the class type.

That as well as strong typecasting interface with Delphi is why I selected SQLite and SQLFW for my first foray into SQL.  Thanks for the great work!

Best wishes,
- Jon

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: SQLite3 Framework Indexes

That as well as strong typecasting interface with Delphi is why I selected SQLite and SQLFW for my first foray into SQL.  Thanks for the great work!

Thanks for your interest!

ingoberg

Member

Registered: 2010-11-20

Posts: 17

Re: SQLite3 Framework Indexes

I did override InitializeTable and the field argument was always empty string and it was only called once (not once for every field in the table). Also the CreateSQLIndex method of the server only accepts 1 field. It would be nice if it was an array.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: SQLite3 Framework Indexes

I did override InitializeTable and the field argument was always empty string and it was only called once (not once for every field in the table).

That's true, but it sounds like a required design. It's up to the implementation of this overriden method to handle the two way of calling:
1) without any field name for the whole table creation,
2) with a field name for a database upgrade.

Also the CreateSQLIndex method of the server only accepts 1 field. It would be nice if it was an array.

It's a good idea. I'll try to add it.