Anybody using FTS with Unidac SQLite??

[ravink]

Hi

I could only find only a single post on FTS in these forums. It is possible that nobody is using FTS here.

I have searched these forums as well as requested for help using FTS with Unidac but nothing is available. There are no examples available. As my skills are limited I would need an example to get started.

I hope someone can point me to how I can get started with Sqlite FTS.

Regards

Ravi K.
________
HotRachel

[AlexP]

Hello,

The code specified below shows how to work with FTS through UniDAC:

Code: Select all

var
UniConnection: TUniConnection;
UniQuery: TUniQuery;
begin
UniConnection := TUniConnection.Create(nil);
UniQuery := TUniQuery.Create(nil);
try
UniConnection.ProviderName := 'SQLite';
UniConnection.Database := ' ';
UniConnection.Connect;
UniConnection.ExecSQL('CREATE VIRTUAL TABLE mail USING fts3(subject, body)',[]);
UniConnection.ExecSQL('INSERT INTO mail(docid, subject, body) VALUES(1, ''software feedback'', ''found it too slow'')',[]);
UniConnection.ExecSQL('INSERT INTO mail(docid, subject, body) VALUES(2, ''software feedback'', ''no feedback'')',[]);
UniConnection.ExecSQL('INSERT INTO mail(docid, subject, body) VALUES(3, ''slow lunch order'', ''was a software problem'')',[]);

UniQuery.Connection := UniConnection;
UniQuery.SQL.Text := 'SELECT * FROM mail WHERE subject MATCH ''software''';
{SELECT * FROM mail WHERE body MATCH 'feedback';
SELECT * FROM mail WHERE mail MATCH 'software';
SELECT * FROM mail WHERE mail MATCH 'slow';}
UniQuery.Open;
while not UniQuery.Eof do
begin
ShowMessage(UniQuery.FieldByName('subject').AsString);
UniQuery.Next;
end;
UniConnection.ExecSQL('DROP TABLE mail',[]);
finally
UniQuery.Free;
UniConnection.Free;
end; 

[ravink]

Hi

Unfortunately I cannot use your code as there is no solution on how to compile the sqlite library to make it work with Unidac. And since the sqlite library is in C I will need a compiler as well as knowledge of C to use it.

It really will be much easier if Devart is willing to provide the compiled dlls and necessary directives. Other delphi vendors seem to have done it. I don't see why devart cannot do it also.

Regards

Ravi K.

Hello,

The code specified below shows how to work with FTS through UniDAC:

Code: Select all

var
UniConnection: TUniConnection;
UniQuery: TUniQuery;
begin
UniConnection := TUniConnection.Create(nil);
UniQuery := TUniQuery.Create(nil);
try
UniConnection.ProviderName := 'SQLite';
UniConnection.Database := ' ';
UniConnection.Connect;
UniConnection.ExecSQL('CREATE VIRTUAL TABLE mail USING fts3(subject, body)',[]);
UniConnection.ExecSQL('INSERT INTO mail(docid, subject, body) VALUES(1, ''software feedback'', ''found it too slow'')',[]);
UniConnection.ExecSQL('INSERT INTO mail(docid, subject, body) VALUES(2, ''software feedback'', ''no feedback'')',[]);
UniConnection.ExecSQL('INSERT INTO mail(docid, subject, body) VALUES(3, ''slow lunch order'', ''was a software problem'')',[]);

UniQuery.Connection := UniConnection;
UniQuery.SQL.Text := 'SELECT * FROM mail WHERE subject MATCH ''software''';
{SELECT * FROM mail WHERE body MATCH 'feedback';
SELECT * FROM mail WHERE mail MATCH 'software';
SELECT * FROM mail WHERE mail MATCH 'slow';}
UniQuery.Open;
while not UniQuery.Eof do
begin
ShowMessage(UniQuery.FieldByName('subject').AsString);
UniQuery.Next;
end;
UniConnection.ExecSQL('DROP TABLE mail',[]);
finally
UniQuery.Free;
UniConnection.Free;
end; 

[ravink]

Hi

Unfortunately I cannot use your code as there is no solution on how to compile the sqlite library to make it work with Unidac. And since the sqlite library is in C I will need a compiler as well as knowledge of C to use it.

It really will be much easier if Devart is willing to provide the compiled dlls and necessary directives. Other delphi vendors seem to have done it. I don't see why devart cannot do it also.

Regards

Ravi K.

Hello,

The code specified below shows how to work with FTS through UniDAC:

Code: Select all

var
UniConnection: TUniConnection;
UniQuery: TUniQuery;
begin
UniConnection := TUniConnection.Create(nil);
UniQuery := TUniQuery.Create(nil);
try
UniConnection.ProviderName := 'SQLite';
UniConnection.Database := ' ';
UniConnection.Connect;
UniConnection.ExecSQL('CREATE VIRTUAL TABLE mail USING fts3(subject, body)',[]);
UniConnection.ExecSQL('INSERT INTO mail(docid, subject, body) VALUES(1, ''software feedback'', ''found it too slow'')',[]);
UniConnection.ExecSQL('INSERT INTO mail(docid, subject, body) VALUES(2, ''software feedback'', ''no feedback'')',[]);
UniConnection.ExecSQL('INSERT INTO mail(docid, subject, body) VALUES(3, ''slow lunch order'', ''was a software problem'')',[]);

UniQuery.Connection := UniConnection;
UniQuery.SQL.Text := 'SELECT * FROM mail WHERE subject MATCH ''software''';
{SELECT * FROM mail WHERE body MATCH 'feedback';
SELECT * FROM mail WHERE mail MATCH 'software';
SELECT * FROM mail WHERE mail MATCH 'slow';}
UniQuery.Open;
while not UniQuery.Eof do
begin
ShowMessage(UniQuery.FieldByName('subject').AsString);
UniQuery.Next;
end;
UniConnection.ExecSQL('DROP TABLE mail',[]);
finally
UniQuery.Free;
UniConnection.Free;
end; 

[AlexP]

Hello,

We plan to add SQLite library to UniDAC in one of its future versions.

[ravink]

Hi

As I understand here, you are saying that FTS3/FTS4 cannot be used with sqlite without access to a C compiler.

From what I understand based on your answers here as well as direct mails, all that is needed is the sqlite3.dll compiled with different parameters. What I don't understand is since sqlite is Open Source, why devart cannot compile a version with the required settings for unidac users to download. And add an example as well.

If not I will have to wait or look for other sources.

Regards

Ravi K.