Re: [sqlite] Write-ahead logging issue on Android

[Richard Hipp]

On Fri, Oct 4, 2013 at 2:55 PM, Sascha Sertel wrote:

>
> I'm working on a multi-platform app that uses a cross-platform library with
> SQLite 3.7.16.1 built into it (i.e. not using the built-in SQLite version
> in Android or other platforms). We make (re)use of prepared statements for
> INSERT and UPDATE on the database, and we use FULLMUTEX serialized
> threading mode. We use explicit transactions for some bulk INSERT
> operations, but most other calls use implicit transactions.
>
> Everything worked fine until I switched journal_mode from MEMORY to WAL.
> Now when I run the app I started seeing SQLite errors pop up, the first
> error is
>
>
> sqlite3_step failed: unable to open database file (error code: 14)
>


Please activate the error and warning log (http://www.sqlite.org/errlog.html)
and let us know what you see there.

One possible problem:  SQLite needs to create a temporary file so that a
multi-row update can be backed out if a constraint fails, but you are out
of temporary file space, or maybe you don't have access rights on the
temporary file space.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Write-ahead logging issue on Android

[Simon Slavin]

On 4 Oct 2013, at 10:26pm, Sascha Sertel  wrote:

> The error does not come up immediately, creating tables works fine, making
> inserts works fine, it's only when I start making updates (from multiple
> threads) where this error starts showing up.

Any chance that the VFS just doesn't allow other threads to access the files ?  
Would it be possible to write a very simple test that just checks to see if the 
error is always associated with a second thread ?

Did you use any strings in the open mode or any PRAGMAs to modify the default 
file access modes SQLite uses ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Write-ahead logging issue on Android

[Sascha Sertel]

I can see the .wal and .shm files getting created and don't see a reason
why SQLite would suddenly have issues accessing those files.

The error does not come up immediately, creating tables works fine, making
inserts works fine, it's only when I start making updates (from multiple
threads) where this error starts showing up.

// Sascha


On Fri, Oct 4, 2013 at 12:53 PM, Dan Kennedy  wrote:

> On 10/05/2013 01:55 AM, Sascha Sertel wrote:
>
>> Hello everybody,
>>
>> I'm hoping someone might have some insights on a particular issue I've
>> been
>> running into. Here some details:
>>
>> I'm working on a multi-platform app that uses a cross-platform library
>> with
>> SQLite 3.7.16.1 built into it (i.e. not using the built-in SQLite version
>> in Android or other platforms). We make (re)use of prepared statements for
>> INSERT and UPDATE on the database, and we use FULLMUTEX serialized
>> threading mode. We use explicit transactions for some bulk INSERT
>> operations, but most other calls use implicit transactions.
>>
>> Everything worked fine until I switched journal_mode from MEMORY to WAL.
>> Now when I run the app I started seeing SQLite errors pop up, the first
>> error is
>>
>>
>> sqlite3_step failed: unable to open database file (error code: 14)
>>
>
> That error usually indicates that SQLite failed to open
> or create some require file. Is there some reason the app
> might fail to open or create a *-wal or *-shm file in the
> same directory as the database file?
>
> Dan.
>
>
>
>
>
>>
>> and after that I get a lot of follow-up errors from my prepared statements
>> such as
>>
>>
>> sqlite3_bind_text failed: library routine called out of sequence
>> (error code: 21)
>>
>>
>> This does not happen on iOS or other platforms using the same
>> cross-platform library, leading me to believe this might be an Android
>> specific issue, maybe due to VFS differences or something like that.
>>
>> There are multiple threads in the same process accessing the database, all
>> using the same shared connection. My understanding is that due to the
>> FULLMUTEX mode SQLite is serializing all these calls. Now WAL basically
>> reenables concurrent reads and writes, my suspicion is that the error is
>> caused by trying to do concurrent writes even though it shouldn't.
>> However,
>> in that case I would expect to see a database locked error instead of this
>> weird unable to open database file issue.
>>
>> Does any of this sound familiar to anyone? I wasn't able to fix it by any
>> means other than setting journal_mode back, actually all other journaling
>> modes work fine except WAL. I looked around and couldn't really find much
>> on people having issues with WAL on Android, so it might be something
>> about
>> the combination of WAL with prepared statements and FULLMUTEX?
>>
>> Any help is appreciated!
>>
>> // Sascha
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Write-ahead logging issue on Android

[Dan Kennedy]

On 10/05/2013 01:55 AM, Sascha Sertel wrote:

Hello everybody,

I'm hoping someone might have some insights on a particular issue I've been
running into. Here some details:

I'm working on a multi-platform app that uses a cross-platform library with
SQLite 3.7.16.1 built into it (i.e. not using the built-in SQLite version
in Android or other platforms). We make (re)use of prepared statements for
INSERT and UPDATE on the database, and we use FULLMUTEX serialized
threading mode. We use explicit transactions for some bulk INSERT
operations, but most other calls use implicit transactions.

Everything worked fine until I switched journal_mode from MEMORY to WAL.
Now when I run the app I started seeing SQLite errors pop up, the first
error is


sqlite3_step failed: unable to open database file (error code: 14)


That error usually indicates that SQLite failed to open
or create some require file. Is there some reason the app
might fail to open or create a *-wal or *-shm file in the
same directory as the database file?

Dan.







and after that I get a lot of follow-up errors from my prepared statements
such as


sqlite3_bind_text failed: library routine called out of sequence
(error code: 21)


This does not happen on iOS or other platforms using the same
cross-platform library, leading me to believe this might be an Android
specific issue, maybe due to VFS differences or something like that.

There are multiple threads in the same process accessing the database, all
using the same shared connection. My understanding is that due to the
FULLMUTEX mode SQLite is serializing all these calls. Now WAL basically
reenables concurrent reads and writes, my suspicion is that the error is
caused by trying to do concurrent writes even though it shouldn't. However,
in that case I would expect to see a database locked error instead of this
weird unable to open database file issue.

Does any of this sound familiar to anyone? I wasn't able to fix it by any
means other than setting journal_mode back, actually all other journaling
modes work fine except WAL. I looked around and couldn't really find much
on people having issues with WAL on Android, so it might be something about
the combination of WAL with prepared statements and FULLMUTEX?

Any help is appreciated!

// Sascha
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Table: misuse error on sqlite3_declare_vtab()

[Simon]

GOT IT.

Well, I found my mistake that lead to all this.  I'm sorry to have wasted
your time.  Here's what happened for those who struggle with similar
issues...

I originally opted to make sqlite3_module a member of my C++ "module"
class.  My C++ constructor was therefore assigning all the xFunctions to
that module's members.  Later, without realizing the impact, my
implementation changed so my class derived sqlite3_module.  However, I
failed to switch the xFunc assignements to this->xFunc = foo;  In the end,
it was exactly as if I had never assigned anything to them.

Thank you all very much!  :)

Simon


On Fri, Oct 4, 2013 at 3:14 PM, Simon  wrote:

> The option was already on for my own stuff but not sqlite.  However, I
> just had to recompile libSqlite3 with debug option to get more details and
> it's a bit scary...
>
> SQL query using sqlite3_exec() and stmt: create virtual table foo using
> vtable_module1;
> -Calling sqlite3_prepare_v2()...
> -Calling sqlite3_step()...
> staging.bin: sqlite3.c:103774: vtabCallConstructor: Assertion `xConstruct'
> failed.
>
> What's that xConstruct?!  I can't even grep that in the sqlite source
> files!  :(
>
> After having browsed around, I think it might actually be a macro
> (somehow) which aliases either xCreate or xConnect depending on the
> situation, but this is just a guess...  and still, it doesn't really help.
>  I reviewed my xCreate and xConnect and they look fine at first glance...
>
> Thanks,
>   Simon
>
>
> On Fri, Oct 4, 2013 at 2:26 PM, Richard Hipp  wrote:
>
>>
>>
>>
>> On Fri, Oct 4, 2013 at 2:22 PM, Simon  wrote:
>>
>>> Hi Richard,
>>>
>>> That helped a lot and got me passed that point.  However, I didn't go
>>> very far from where I were...
>>>
>>> It now compiles fine, however the program segfaults within the
>>> sqlite3_step() of the "create virtual table foo using vtable_module1;"
>>> statement.
>>>
>>> Using valgrind, I get this interesting message:
>>>
>>> ==31748== Jump to the invalid address stated on the next line
>>> ==31748== at 0x0: ???
>>> ==31748== by 0x5A0D119: vtabCallConstructor (in
>>> /usr/lib64/libsqlite3.so.0.8.6)
>>> ==31748== by 0x5A3D499: sqlite3VdbeExec (in
>>> /usr/lib64/libsqlite3.so.0.8.6)
>>> ==31748== by 0x5A3E969: sqlite3_step (in /usr/lib64/libsqlite3.so.0.8.6)
>>> [...]
>>>
>>> This looks (to me) like one of the NULL function pointers in struct
>>> sqlite3_module is being called.  I added a valid xRename() function as I
>>> had initially thought it was optionnal, but still not working.  I then
>>> tried creating a dummy function for every one of these pointers, but I get
>>> the same result.  I even tried changing the "create virtual..." statement
>>> to include module params (which my module ignores) but again it didn't
>>> change anything.
>>>
>>> Where should I look at next?
>>>
>>>
>> Recompile with line-number information (-g is it?) so that you know
>> exactly which line of code tried to jump to the NULL pointer.  Then you'll
>> know exactly which method you need to add.
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Table: misuse error on sqlite3_declare_vtab()

[Simon]

The option was already on for my own stuff but not sqlite.  However, I just
had to recompile libSqlite3 with debug option to get more details and it's
a bit scary...

SQL query using sqlite3_exec() and stmt: create virtual table foo using
vtable_module1;
-Calling sqlite3_prepare_v2()...
-Calling sqlite3_step()...
staging.bin: sqlite3.c:103774: vtabCallConstructor: Assertion `xConstruct'
failed.

What's that xConstruct?!  I can't even grep that in the sqlite source
files!  :(

After having browsed around, I think it might actually be a macro (somehow)
which aliases either xCreate or xConnect depending on the situation, but
this is just a guess...  and still, it doesn't really help.  I reviewed my
xCreate and xConnect and they look fine at first glance...

Thanks,
  Simon


On Fri, Oct 4, 2013 at 2:26 PM, Richard Hipp  wrote:

>
>
>
> On Fri, Oct 4, 2013 at 2:22 PM, Simon  wrote:
>
>> Hi Richard,
>>
>> That helped a lot and got me passed that point.  However, I didn't go
>> very far from where I were...
>>
>> It now compiles fine, however the program segfaults within the
>> sqlite3_step() of the "create virtual table foo using vtable_module1;"
>> statement.
>>
>> Using valgrind, I get this interesting message:
>>
>> ==31748== Jump to the invalid address stated on the next line
>> ==31748== at 0x0: ???
>> ==31748== by 0x5A0D119: vtabCallConstructor (in
>> /usr/lib64/libsqlite3.so.0.8.6)
>> ==31748== by 0x5A3D499: sqlite3VdbeExec (in
>> /usr/lib64/libsqlite3.so.0.8.6)
>> ==31748== by 0x5A3E969: sqlite3_step (in /usr/lib64/libsqlite3.so.0.8.6)
>> [...]
>>
>> This looks (to me) like one of the NULL function pointers in struct
>> sqlite3_module is being called.  I added a valid xRename() function as I
>> had initially thought it was optionnal, but still not working.  I then
>> tried creating a dummy function for every one of these pointers, but I get
>> the same result.  I even tried changing the "create virtual..." statement
>> to include module params (which my module ignores) but again it didn't
>> change anything.
>>
>> Where should I look at next?
>>
>>
> Recompile with line-number information (-g is it?) so that you know
> exactly which line of code tried to jump to the NULL pointer.  Then you'll
> know exactly which method you need to add.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Write-ahead logging issue on Android

[Sascha Sertel]

Hello everybody,

I'm hoping someone might have some insights on a particular issue I've been
running into. Here some details:

I'm working on a multi-platform app that uses a cross-platform library with
SQLite 3.7.16.1 built into it (i.e. not using the built-in SQLite version
in Android or other platforms). We make (re)use of prepared statements for
INSERT and UPDATE on the database, and we use FULLMUTEX serialized
threading mode. We use explicit transactions for some bulk INSERT
operations, but most other calls use implicit transactions.

Everything worked fine until I switched journal_mode from MEMORY to WAL.
Now when I run the app I started seeing SQLite errors pop up, the first
error is


sqlite3_step failed: unable to open database file (error code: 14)


and after that I get a lot of follow-up errors from my prepared statements
such as


sqlite3_bind_text failed: library routine called out of sequence
(error code: 21)


This does not happen on iOS or other platforms using the same
cross-platform library, leading me to believe this might be an Android
specific issue, maybe due to VFS differences or something like that.

There are multiple threads in the same process accessing the database, all
using the same shared connection. My understanding is that due to the
FULLMUTEX mode SQLite is serializing all these calls. Now WAL basically
reenables concurrent reads and writes, my suspicion is that the error is
caused by trying to do concurrent writes even though it shouldn't. However,
in that case I would expect to see a database locked error instead of this
weird unable to open database file issue.

Does any of this sound familiar to anyone? I wasn't able to fix it by any
means other than setting journal_mode back, actually all other journaling
modes work fine except WAL. I looked around and couldn't really find much
on people having issues with WAL on Android, so it might be something about
the combination of WAL with prepared statements and FULLMUTEX?

Any help is appreciated!

// Sascha
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.8.1

[Warren Young]

On 10/4/2013 12:32, Peter Haworth wrote:

I've seen emntion of version 3.8.1 of sqlite - is there a document somwhere
that describes the changes?


https://www.sqlite.org/draft/releaselog/current.html

You could have found that link yourself from the SQLite download page, 
right next to the link to the 3.8.1 pre-release tarball.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.8.1

[Richard Hipp]

On Fri, Oct 4, 2013 at 2:32 PM, Peter Haworth  wrote:

> I've seen emntion of version 3.8.1 of sqlite - is there a document somwhere
> that describes the changes?
>

http://www.sqlite.org/draft/releaselog/current.html

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 3.8.1

[Peter Haworth]

I've seen emntion of version 3.8.1 of sqlite - is there a document somwhere
that describes the changes?
Thanks,
Pete
lcSQL Software 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Table: misuse error on sqlite3_declare_vtab()

[Richard Hipp]

On Fri, Oct 4, 2013 at 2:22 PM, Simon  wrote:

> Hi Richard,
>
> That helped a lot and got me passed that point.  However, I didn't go very
> far from where I were...
>
> It now compiles fine, however the program segfaults within the
> sqlite3_step() of the "create virtual table foo using vtable_module1;"
> statement.
>
> Using valgrind, I get this interesting message:
>
> ==31748== Jump to the invalid address stated on the next line
> ==31748== at 0x0: ???
> ==31748== by 0x5A0D119: vtabCallConstructor (in
> /usr/lib64/libsqlite3.so.0.8.6)
> ==31748== by 0x5A3D499: sqlite3VdbeExec (in /usr/lib64/libsqlite3.so.0.8.6)
> ==31748== by 0x5A3E969: sqlite3_step (in /usr/lib64/libsqlite3.so.0.8.6)
> [...]
>
> This looks (to me) like one of the NULL function pointers in struct
> sqlite3_module is being called.  I added a valid xRename() function as I
> had initially thought it was optionnal, but still not working.  I then
> tried creating a dummy function for every one of these pointers, but I get
> the same result.  I even tried changing the "create virtual..." statement
> to include module params (which my module ignores) but again it didn't
> change anything.
>
> Where should I look at next?
>
>
Recompile with line-number information (-g is it?) so that you know exactly
which line of code tried to jump to the NULL pointer.  Then you'll know
exactly which method you need to add.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Table: misuse error on sqlite3_declare_vtab()

[Simon]

Hi Richard,

That helped a lot and got me passed that point.  However, I didn't go very
far from where I were...

It now compiles fine, however the program segfaults within the
sqlite3_step() of the "create virtual table foo using vtable_module1;"
statement.

Using valgrind, I get this interesting message:

==31748== Jump to the invalid address stated on the next line
==31748== at 0x0: ???
==31748== by 0x5A0D119: vtabCallConstructor (in
/usr/lib64/libsqlite3.so.0.8.6)
==31748== by 0x5A3D499: sqlite3VdbeExec (in /usr/lib64/libsqlite3.so.0.8.6)
==31748== by 0x5A3E969: sqlite3_step (in /usr/lib64/libsqlite3.so.0.8.6)
[...]

This looks (to me) like one of the NULL function pointers in struct
sqlite3_module is being called.  I added a valid xRename() function as I
had initially thought it was optionnal, but still not working.  I then
tried creating a dummy function for every one of these pointers, but I get
the same result.  I even tried changing the "create virtual..." statement
to include module params (which my module ignores) but again it didn't
change anything.

Where should I look at next?

Thanks again for your help,
  Simon



On Fri, Oct 4, 2013 at 10:54 AM, Richard Hipp  wrote:

>
>
>
> On Fri, Oct 4, 2013 at 10:07 AM, Simon  wrote:
>
>>
>> 3) I call sqlite3_create_module() with module name "vtable1"
>>
>> 4) I call sqlite3_declare_vtab() with this statement: "CREATE TABLE foo (
>> x
>> integer, y integer );"
>>
>
>
> The application must never invoke sqlite3_declare_vtab().  The
> sqlite3_declare_vtab() interface can only be called from within the xCreate
> and xConnect methods of the virtual table implementation.  If called from
> any other context, sqlite3_declare_vtab() returns the error you describe.
>
> When you run "CREATE VIRTUAL TABLE name USING module;"  the xCreate method
> is called to create the new virtual table.  The xCreate method needs to
> tell the SQLite core what the schema for the virtual table is and it uses
> sqlite3_declare_vtab() to do so.
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Major performance difference when joining on FTS4 table's docid column versus custom id column

[Per Vognsen]

I ended up going with an external content table, and now everything works
like a charm with the expected level of performance.

Thanks for the help!

-Per


On Thu, Oct 3, 2013 at 6:46 AM, Clemens Ladisch  wrote:

> Per Vognsen wrote:
> > Am I wrong to think that joining on docid should be as fast as joining on
> > indexed integer columns in other tables?
>
> Looking up a record by docid is faster than non-FTS lookups on other
> columns, but the virtual table implementation still has to go through
> a separate query to look up the data in the actual table.
>
> > what's the recommended way to speed things up?
>
> If you want to do anything but FTS searches, you should store a copy of
> the data in a 'real' table.  (If you're concerned about space, make the
> FTS table an external content table: <
> http://www.sqlite.org/fts3.html#section_6_2>.)
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Table: misuse error on sqlite3_declare_vtab()

[Richard Hipp]

On Fri, Oct 4, 2013 at 10:07 AM, Simon  wrote:

>
> 3) I call sqlite3_create_module() with module name "vtable1"
>
> 4) I call sqlite3_declare_vtab() with this statement: "CREATE TABLE foo ( x
> integer, y integer );"
>


The application must never invoke sqlite3_declare_vtab().  The
sqlite3_declare_vtab() interface can only be called from within the xCreate
and xConnect methods of the virtual table implementation.  If called from
any other context, sqlite3_declare_vtab() returns the error you describe.

When you run "CREATE VIRTUAL TABLE name USING module;"  the xCreate method
is called to create the new virtual table.  The xCreate method needs to
tell the SQLite core what the schema for the virtual table is and it uses
sqlite3_declare_vtab() to do so.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Table: misuse error on sqlite3_declare_vtab()

[Max Vlasov]

Simon,

don't know what exactly wrong in your particular case, but I'd suggest
setting debugger breakpoints everywhere in your x-handlers and notice the
moment after which calls are ceased (or you get a error code).

Max


On Fri, Oct 4, 2013 at 6:07 PM, Simon  wrote:

> Hi there,
>
> I'm currently building my own virtual table implementation. I've built a
> dummy vtable that returns the string "1" to all queries for all columns
> but I'm having trouble getting started (getting a 'misuse' error). Can
> someone help me get in the right direction?
>
>
> >
>
> 1) I first create an sqlite3* object in ":memory:". This one works fine.
>
> 2) Allocate and init my derived sqlite3_module* object.
> 2.1) Function pointers I use are Create,Destroy, Connect,Disconnect,
> Open,Close, BestIndex,Filter, Next,Eof, Column,Rowid.
> 2.2) NULL pointers for Update, Begin,Sync,Commit,Rollback,
> FindFunction,Rename, Savepoint,Release,RollbackTo.
> 2.3) *** ? *** Are there any other members not documented I'd need to init?
>
> 3) I call sqlite3_create_module() with module name "vtable1"
>
> 4) I call sqlite3_declare_vtab() with this statement: "CREATE TABLE foo ( x
> integer, y integer );"
>
> --> That one fails with ERROR 21 (lib misuse).
>
> <
>
>
> Did I forget a step? Do them in wrong order? Anything obvious seems wrong
> to you? Where should I go to get more details and fix the issue?
> My current implementation is rather complex and involves C++ and templates
> a lot. Making a "small example" would be highly time-consuming, so I'd
> prefer to avoid going that way if possible.
>
> Anyone can tell me where I'd find a simple functionnal vtable "hello
> world"? The vtable documentation is great but is missing examples, imo.
> Personnally, I think the vtable documentation page could benefit from one
> example project throughout and develop it. Something like a basic CSV
> vtable would probably help the masses a lot! :)
>
> Thanks for your help and time,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Table: misuse error on sqlite3_declare_vtab()

[Simon]

Hi there,

I'm currently building my own virtual table implementation. I've built a
dummy vtable that returns the string "1" to all queries for all columns
but I'm having trouble getting started (getting a 'misuse' error). Can
someone help me get in the right direction?


>

1) I first create an sqlite3* object in ":memory:". This one works fine.

2) Allocate and init my derived sqlite3_module* object.
2.1) Function pointers I use are Create,Destroy, Connect,Disconnect,
Open,Close, BestIndex,Filter, Next,Eof, Column,Rowid.
2.2) NULL pointers for Update, Begin,Sync,Commit,Rollback,
FindFunction,Rename, Savepoint,Release,RollbackTo.
2.3) *** ? *** Are there any other members not documented I'd need to init?

3) I call sqlite3_create_module() with module name "vtable1"

4) I call sqlite3_declare_vtab() with this statement: "CREATE TABLE foo ( x
integer, y integer );"

--> That one fails with ERROR 21 (lib misuse).

<


Did I forget a step? Do them in wrong order? Anything obvious seems wrong
to you? Where should I go to get more details and fix the issue?
My current implementation is rather complex and involves C++ and templates
a lot. Making a "small example" would be highly time-consuming, so I'd
prefer to avoid going that way if possible.

Anyone can tell me where I'd find a simple functionnal vtable "hello
world"? The vtable documentation is great but is missing examples, imo.
Personnally, I think the vtable documentation page could benefit from one
example project throughout and develop it. Something like a basic CSV
vtable would probably help the masses a lot! :)

Thanks for your help and time,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Array Accessing in SQLite

[Simon Slavin]

On 4 Oct 2013, at 9:32am, techi eth  wrote:

> Can anyone let me know what is best way to handle C Array in SQLite3 database
> ?
> 
> As of now I can think of creating multiple row for storing each array
> element.

When you need the contents of one of the elements of the array, is it 
reasonable to load them all, or do your arrays have thousands of elements ?

If your arrays have many elements, the above solution is good.  Simpler, will 
work for any number of elements, and you can use SQLite API to search and sort 
individual elements.

On the other hand, if your arrays have few elements, assuming that the contents 
of each of the elements is text, you could find a character that you would 
never use in your text, perhaps '|' or '~', and use that as an element 
separator:

freda|johanna|georgina|patricia

that lets you fetch an entire array at once and split it up in your programming 
language.  Faster, files are smaller, but fetches a lot of data each time you 
want only one element and you cannot sort an array by contents.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Array Accessing in SQLite

[techi eth]

Hi,

Can anyone let me know what is best way to handle C Array in SQLite3 database
?

As of now I can think of creating multiple row for storing each array
element.

Thanks..
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 release date and how to compile on windows platform

[Simon Slavin]

On 4 Oct 2013, at 6:44am, Zarian Waheed  wrote:

> Does anyone know if any release date has been announced for SQLite4?

We do know that it hasn't.

> Secondly where can I find the instructions to compile SQLite4 on windows 
> platform.

The instructions aren't that different to those for SQLite3.  And it is in such 
an early stage of development that if you can't figure out how to compile it, 
you probably shouldn't be using it.

Seriously, if you are actually writing a program you need to work correctly, 
you should be using SQLite3, which lots of people understand and has billions 
of installations.  SQLite4 is still in the experimental phase.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users