Hi,

Reffering to this article:
http://www.sqlite.org/vtab.html

... I'm trying to create virtual table. But before call standard CREATE VIRTUAL TABLE, some module must be created first. FPC has method sqlite3_create_module() but it return 0:

var
  m: sqlite3_module;
  c: cint;
begin
  FillByte(m, SizeOf(m),0);
  c := sqlite3_create_module(dsContacts.SqliteHandle, 'test_module', @m,
nil);

SqliteHandle is assigned. But type sqlite3_module looks strange. It is dummy, without fields. Can I somehow create dummy module with pure SQL syntax? I don't need stuff to which the module is used.

Regards
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20121218/b980bf6d/attachment.html>

A virtual table means you will handle storage.
So I think you must always implement all methods required to handle that ?

That said: probably, the module struct was not implemented because h2pas encountered an error when translating the headers. This needs to be fixed, please enter a bug report.

Michael.

A virtual table means you will handle storage. So I think you must always implement all methods required to handle that ?

After creating virtual table it should working like normal table (with joining etc) handled by TSQLite3Dataset. That it worked on PostgreSQL. This is good alternative for TMemDataset or TBufDataset (especially it is flexible if application using SQLite for "normal" storage). But SQLite need some module inititation via API wrapper. This is new for me. I will report it
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20121218/56da56ac/attachment-0001.html>

You mean a temporary table ?

I'm not an expert, but this is IMHO a completely different concept.

Michael.

If SQLite's API for LoadExtension is similar to the module initiation call, you might use the sqldb implementation as a guide; see http://wiki.lazarus.freepascal.org/Spatialite

An SQLite virtual table is implemented by code called by the SQLite DLL (or presumably compiled into it).
AFAIU, it is opaque to consumers of the sqlite DLL (except that perhaps they know it's a virtual table and there might be some other ways to manipulate it).... so it's similar to say implementing UDFs in Firebird as far SQLDB is concerned.

You mean a temporary table ? > > I'm not an expert, but this is IMHO a completely different concept.

Oh, now I see that SQLite has TEMP table too. I thought that PostgreSQL TEMP table is called VIRTUAL table in SQLite. After quick testing it's working fine without any dll API calls unlike VIRTUAL table. What I read that VIRTUAL table is created in memory and TEMP table is written to temp database. Both are destroyed after disconnect. But can't find where this file is created or maybe temp database mean that it is created in memory also? Anyway, TEMP table is solution for me until issue with VIRTUAL table will be solved
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20121218/20d15510/attachment.html>

If you want a pure memory table in sqlite3, give it the special filename ':memory:'. No special support is required the user code. If you use TSQLite3Connection you can also use the TSQLite3Backup to easily copy the memory database from/to a disk database, if needed.

Ludo

If you want a pure memory table in sqlite3, give it the special filename ':memory:'. No special support is required the user code. If you use TSQLite3Connection you can also use the TSQLite3Backup to easily copy the memory database from/to a disk database, if needed.

What if I want that only one TABLE (not database) should be pure in memory? Because now I crashed my test database :P . I killed application after create temp table and after reconnect sqlite say me that this table exists, but drop table say that table doesnt exists :)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20121218/23c03bc7/attachment.html>

Ok I found solution. To use TEMP tables as pure in memory, just execute command: PRAGMA temp_store=2 . Then all temp tables will be stored in memory. So, I don't really need VIRTUAL tables anymore -------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20121218/9e195cd3/attachment.html>