#1 2010-11-12 14:22:39

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)

Offline

#2 2010-11-14 18:57:14

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,203
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.

Offline

#3 2010-11-15 02:46:14

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!

Offline

#4 2010-11-15 05:38:10

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,203
Website

Re: SQLite3 Framework Indexes

JonG wrote:

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.

Offline

#5 2010-11-15 13:00:30

JonG
Member
Registered: 2010-11-12
Posts: 7

Re: SQLite3 Framework Indexes

ab wrote:
JonG wrote:

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

Offline

#6 2010-11-15 15:55:38

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,203
Website

Re: SQLite3 Framework Indexes

JonG wrote:

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!
smile

Offline

#7 2010-12-12 02:14:31

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.

Offline

#8 2010-12-12 09:12:42

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,203
Website

Re: SQLite3 Framework Indexes

ingoberg wrote:

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.

ingoberg wrote:

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.

Offline

Board footer

Powered by FluxBB