Re: [sqlite] Guidance with Python and nested cursors

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/07/13 18:37, Keith Medcalf wrote:
> cr1 = cn.cursor() cr2 = cn.cursor()
> 
> cr1.execute('select ...') while True: row = cr1.fetchone() if not row: 
> break

While that is normal DBAPI, it is far more verbose and unpythonic than the
SQLite wrappers (disclosure: I'm the author of one of them).  Code can use
iterators and look something like this:

  for id,one,two in db.cursor().execute("select id, one, two from XXX"):
   three=one+two
   db.cursor().execute("insert into  ", (three,))

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHnV8sACgkQmOOfHg372QTfbwCeIYJTKpLSn+RGlsDcMA/S18WI
4QcAoOHgcf3mcRwfWwR2IrB87DbS0oQY
=gtkt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Guidance with Python and nested cursors

[Keith Medcalf]

It is perfectly allowed to open multiple cursors against a single connection.  
You can only execute one statement per cursor at a time, but you can have 
multiple cursors running from the same connection:

cr1 = cn.cursor()
cr2 = cn.cursor()

cr1.execute('select ...')
while True:
row = cr1.fetchone()
if not row:
break
...
cr2.execute('INSERT ...')

for example.  If you are inserting into one of the tables used in the outer 
select, simply make sure that select has an order by with a + in front of one 
of the column names to avoid side effects (ie, changes made to the database by 
the insert are visible to all statements/cursors on that connection even before 
those changes are committed).

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Joseph L. Casale
> Sent: Wednesday, 17 July, 2013 13:41
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Guidance with Python and nested cursors
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille
> Sent: Wednesday, July 17, 2013 1:25 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Guidance with Python and nested cursors
> 
> On Jul 17, 2013, at 9:07 PM, Joseph L. Casale
>  wrote:
> 
> >> I am using Python to query a table for all its rows, for each row,
> I query related rows from a
> >> second table, then perform some processing and insert in to a third
> table.
> >>
> >> What is the technically correct approach for this?
> >
> >From the above outline, one SQL statement:
> 
> Hi,
> Problem is I need to perform some Python processing of the data, then
> insert.
> 
> Thanks!
> jlc
> ___
> 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] Guidance with Python and nested cursors

[Keith Medcalf]

If the table you are modifying is not being used in the in progress queries 
then you can just do the inserts -- wrapping the whole process in a single big 
transaction if you like.  

If the inserts may affect an open cursor (query) then you can specify an ORDER 
BY on the affected query, and put a + in front of one of the column names.  
This has the effect of forcing the query results to be put into a temp b-tree 
on the first call to step, and your row retrievals cursor through the temp 
table (as it were).  This is effectively the same as retrieving the entire 
result set into a list except that the buffer is within the database engine and 
not within your application.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Joseph L. Casale
> Sent: Wednesday, 17 July, 2013 13:07
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Guidance with Python and nested cursors
> 
> I am using Python to query a table for all its rows, for each row, I
> query related rows from a
> second table, then perform some processing and insert in to a third
> table.
> 
> What is the technically correct approach for this? I would rather not
> accumulate all of the first
> tables data to make one off selects from table two, then insert to
> table three. I would prefer to
> iterate over table one etc.
> 
> How does one setup the connection and cursor for this style of task?
> 
> Thanks for any guidance,
> jlc
> ___
> 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] function auxiliary data

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/07/13 04:03, Riccardo Vianello wrote:
> I'm not sure I can do the same with pysqlite),

pysqlite forces a minimum statement cache size of 5.

> but since sqlite3_reset doesn't clear the binding

It can't in general since you can immediately rerun the statement with the
same bindings.  However that isn't exposed in APSW so you can edit the
code to clear the bindings after each reset call.

It doesn't do that at the moment as a form of being lazy - only clearing
bindings when the statement is re-executed.

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHnCn8ACgkQmOOfHg372QT2HwCeL02RdUT1/6rpKLIxuQ4HAehg
4asAnj29dAUc4ZHqVvY1UWZPa15VWOd9
=IO2u
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

[Patrik Nilsson]

>> Prepare query statement;
>> Iterate through one or more rows;
>> Reset statement;
>>
>> Attempt to begin transaction; <--- SQLITE_BUSY

"The sqlite3_reset() function is called to reset a prepared statement
object back to its initial state, ready to be re-executed. Any SQL
statement variables that had values bound to them using the
sqlite3_bind_*() API retain their values. Use sqlite3_clear_bindings()
to reset the bindings."

To close a prepared statement you need to use finalize.

"The sqlite3_finalize() function is called to delete a prepared
statement. If the most recent evaluation of the statement encountered no
errors or if the statement is never been evaluated, then
sqlite3_finalize() returns SQLITE_OK. If the most recent evaluation of
statement S failed, then sqlite3_finalize(S) returns the appropriate
error code or extended error code."

You have one prepared statement open and then try to start a
transaction. This gives you a busy error.

On 07/17/2013 06:56 PM, Dušan Paulovič wrote:
> If you remove a busy check, does it output any statements?
> Do you have any custom functions/operations running so they could block
> sqlite in creating new statement?
> 
> 
> 2013/7/17 Loren Keagle 
> 
>> Hi everyone,
>>
>> I have an interesting locking problem that I'm wondering if someone can
>> help with some insight.
>>
>> I have a master database with some metadata, and several sub-databases to
>> store logging events. I have one reader object and one writer object that
>> attach to the sub-databases and encapsulate the read/write operations
>> respectively.
>>
>> I've found a very unexpected locking behavior with the attached databases
>> and exclusive transactions. One of my unit tests does the following:
>>
>> Begin EXCLUSIVE TRANSACTION;
>> insert several rows of data;
>> Commit transaction;
>>
>> Prepare query statement;
>> Iterate through one or more rows;
>> Reset statement;
>>
>> Attempt to begin transaction; <--- SQLITE_BUSY
>> Would like to write more here, but can't unless I close/open the
>> connection;
>>
>> I can't seem to figure out any reason why I can't create a new exclusive
>> transaction here, and I feel it must have to do with the fact that I have
>> attached to sub-databases (possibly the same sub-database) with my
>> reader/writer objects. This is single threaded and only database connection
>> (with attach/detach logic).
>> I have verified that all statements prepared by the connection are
>> properly reset - this is handled by my C++ wrappers, and any errors will
>> throw an exception. I even iterated through all of the current statements
>> with the following code immediately before my transaction failure, with no
>> results:
>>
>> sqlite3_stmt *stmt = NULL;
>> while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL)
>> {
>> if (sqlite3_stmt_busy(stmt))
>> {
>> const char* sql = sqlite3_sql(stmt);
>> std::cout << sql << "\r\n";
>> }
>> }
>>
>> Can anyone think of a reason why attached databases would prevent entering
>> a second transaction? BTW, it doesn't seem to work with immediate
>> transactions either. If I remove the query, everything works fine.
>>
>> Thanks!
>>
>>
>> 
>> This email, including any attachments and files transmitted with it, are
>> for the sole use of the intended recipient(s) to whom this email is
>> addressed, and may contain confidential and/or privileged information. Any
>> unauthorized review, use, disclosure or distribution is prohibited. If you
>> are not the intended recipient, please be advised that you have received
>> this email in error, and please contact the sender by reply email and
>> destroy all copies (including all electronic and hard copies) of the
>> original message. Thank you.
>> ___
>> 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
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Guidance with Python and nested cursors

[Joseph L. Casale]

From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille
Sent: Wednesday, July 17, 2013 1:25 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Guidance with Python and nested cursors

On Jul 17, 2013, at 9:07 PM, Joseph L. Casale  wrote:

>> I am using Python to query a table for all its rows, for each row, I query 
>> related rows from a
>> second table, then perform some processing and insert in to a third table.
>>
>> What is the technically correct approach for this?
>
>From the above outline, one SQL statement:

Hi,
Problem is I need to perform some Python processing of the data, then insert.

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

Re: [sqlite] Guidance with Python and nested cursors

[Petite Abeille]

On Jul 17, 2013, at 9:07 PM, Joseph L. Casale  wrote:

> I am using Python to query a table for all its rows, for each row, I query 
> related rows from a
> second table, then perform some processing and insert in to a third table.
> 
> What is the technically correct approach for this? 

>From the above outline, one SQL statement:

insert
intotableA
(
  ...
)

select  ...
fromtableB

jointableC
on  tableC... = tableB…

"When in Rome, do as the Romans do" -- Ambrose, allegedly

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

[sqlite] Guidance with Python and nested cursors

[Joseph L. Casale]

I am using Python to query a table for all its rows, for each row, I query 
related rows from a
second table, then perform some processing and insert in to a third table.

What is the technically correct approach for this? I would rather not 
accumulate all of the first
tables data to make one off selects from table two, then insert to table three. 
I would prefer to
iterate over table one etc.

How does one setup the connection and cursor for this style of task?

Thanks for any guidance,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

OK, I have looked over all possible solutions for this issue and the best I
could find is to extend sqlite3_index_info structure as follows:
struct sqlite3_index_info {

  ...

  /* Extra info */
  struct sqlite3_index_extras {
int iVersion;
sqlite3_collseq **coll_seq;  /* Collation sequences (same size as
aConstraints) */
  } *extras;
};

this would be binary compatible and also extendable for needs in future.

How about this?


2013/7/17 Dan Kennedy 

> On 07/17/2013 03:46 PM, Dušan Paulovič wrote:
>
>> Hello,
>> in virtual table mechanism is missing a way to correctly handle following
>> queries:
>>
>> SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE;
>> SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE;
>>
>> To xBestIndex function is passed only constraint field = 'abc', but there
>> is no way to correctly compare (by correctly I mean accordingly to passed
>> collation) those strings because of missing collation functions in
>> constraint definition.
>>
>> Patch for this issue exists for a long time:
>> http://osdir.com/ml/sqlite-**users/2011-09/msg00152.html
>>
>> Is there any chance that it could get merged?
>>
>
> I think the main problem is that it is not binary compatible.
>
> Dan.
>
>
>
> __**_
> 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] incremental_vacuum within or outside transaction?

[_ph_]

Is there any rationale for (not) running incremental_vacuum in an
transaction?

Unlike a full VACUUM, it seems to work with or without. 

Purpose:
We are running with auto_vacuum = INCREMENTAL, and when closing the file in
our software, we do an on-demand cleanup like so:

if (Query('pragma freelist_count') > threshold)
   while (time_passed < max_time)
  Exec('pragma incremental_vacuum(1)');

In practcie, the condition involves more than a single query, and we will
elevate that from silent to "with UI" if there's a lot to be reclaimed. 



A quick (statistically insigificant) test suggests that we do make more
progress within an transaction, OTOH it feels "strange". 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/incremental-vacuum-within-or-outside-transaction-tp70086.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

Yes, xRename was a part of the v1 module interface, but v1 module interface
is not the first verson of it.
Old sqlite extensions does not even contain xRename in their VT
implementations and xFindFunction was the last one considered.

I understand what you mean by binary incompatibility, but it is not a
reason to keep this issue unresolved.
Without possibility to custom handling of collation in xBestIndex function
is this great idea limited to be used only in case of numeric queries.
Now it is no way to return correct results from VT using xBestIndex. Yes, I
can keep these constraints on SQLite, but then perhaps these
constraints should be filtered out from xBestIndex consideration.

Perhaps new function returning CollSeq array for constraint structure in
API could resolve it - as I suggested in previous post.
It can be resolved even with binary compatibility.

Dusan


2013/7/17 Jay A. Kreibich 

> On Wed, Jul 17, 2013 at 01:27:20PM +0200, Du?an Paulovi? scratched on the
> wall:
> > But it is the same as with new functions in sqlite3_module. Old SQLite
> > extensions does not implement xRename function which is now needed. Also,
> > new feature could be made optional using macro switch like some other
> > features in SQLite.
>
>   Actually, xRename() was part of the v1 module interface.  xSavepoint(),
>   xRelease(), and xRollbackTo() were added in the v2 interface.  All
>   three of these functions are optional, and the fact they were added
>   at the end of the data structure means a v1 module will run under a
>   modern version of SQLite just fine.
>
>   http://www.sqlite.org/c3ref/module.html
>
>-j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> 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] Sqlite locking issue with ATTACH'ed databases

[Dušan Paulovič]

If you remove a busy check, does it output any statements?
Do you have any custom functions/operations running so they could block
sqlite in creating new statement?


2013/7/17 Loren Keagle 

> Hi everyone,
>
> I have an interesting locking problem that I'm wondering if someone can
> help with some insight.
>
> I have a master database with some metadata, and several sub-databases to
> store logging events. I have one reader object and one writer object that
> attach to the sub-databases and encapsulate the read/write operations
> respectively.
>
> I've found a very unexpected locking behavior with the attached databases
> and exclusive transactions. One of my unit tests does the following:
>
> Begin EXCLUSIVE TRANSACTION;
> insert several rows of data;
> Commit transaction;
>
> Prepare query statement;
> Iterate through one or more rows;
> Reset statement;
>
> Attempt to begin transaction; <--- SQLITE_BUSY
> Would like to write more here, but can't unless I close/open the
> connection;
>
> I can't seem to figure out any reason why I can't create a new exclusive
> transaction here, and I feel it must have to do with the fact that I have
> attached to sub-databases (possibly the same sub-database) with my
> reader/writer objects. This is single threaded and only database connection
> (with attach/detach logic).
> I have verified that all statements prepared by the connection are
> properly reset - this is handled by my C++ wrappers, and any errors will
> throw an exception. I even iterated through all of the current statements
> with the following code immediately before my transaction failure, with no
> results:
>
> sqlite3_stmt *stmt = NULL;
> while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL)
> {
> if (sqlite3_stmt_busy(stmt))
> {
> const char* sql = sqlite3_sql(stmt);
> std::cout << sql << "\r\n";
> }
> }
>
> Can anyone think of a reason why attached databases would prevent entering
> a second transaction? BTW, it doesn't seem to work with immediate
> transactions either. If I remove the query, everything works fine.
>
> Thanks!
>
>
> 
> This email, including any attachments and files transmitted with it, are
> for the sole use of the intended recipient(s) to whom this email is
> addressed, and may contain confidential and/or privileged information. Any
> unauthorized review, use, disclosure or distribution is prohibited. If you
> are not the intended recipient, please be advised that you have received
> this email in error, and please contact the sender by reply email and
> destroy all copies (including all electronic and hard copies) of the
> original message. Thank you.
> ___
> 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] Sqlite locking issue with ATTACH'ed databases

[Simon Slavin]

On 16 Jul 2013, at 11:24pm, Loren Keagle  wrote:

> Begin EXCLUSIVE TRANSACTION;
> insert several rows of data;
> Commit transaction;
> 
> Prepare query statement;
> Iterate through one or more rows;
> Reset statement;
> 
> Attempt to begin transaction; <--- SQLITE_BUSY
> Would like to write more here, but can't unless I close/open the connection;

I assume you're checking the result codes returned by all the API calls before 
the second BEGIN to see that they all return SQLITE_OK.

Please add a _finalize() after the _reset() just for testing purposes.  I know 
you may not want it as part of your production code.

Is the statement that gets a busy a BEGIN or BEGIN IMMEDIATE or BEGIN EXCLUSIVE 
?

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

Re: [sqlite] Passing collation to xBestIndex

[Jay A. Kreibich]

On Wed, Jul 17, 2013 at 01:27:20PM +0200, Du?an Paulovi? scratched on the wall:
> But it is the same as with new functions in sqlite3_module. Old SQLite
> extensions does not implement xRename function which is now needed. Also,
> new feature could be made optional using macro switch like some other
> features in SQLite.

  Actually, xRename() was part of the v1 module interface.  xSavepoint(),
  xRelease(), and xRollbackTo() were added in the v2 interface.  All
  three of these functions are optional, and the fact they were added
  at the end of the data structure means a v1 module will run under a
  modern version of SQLite just fine.

  http://www.sqlite.org/c3ref/module.html

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

> Some changes, if blob is bigger than a few bytes, you should normalize them. 
> If 2 blobs are equal, their id must be equal and you don't waste time 
> comparing nor memory joining blob content. So you get:
They are quite small (max ~70 bytes...)

>  DROP TABLE IF EXISTS tour_blob;
>  CREATE TABLE tour_blob (id INTEGER PRIMARY KEY,
>n_blob blob);
>
>  DROP TABLE IF EXISTS tour;
>  CREATE TABLE tour (id integer,
>score integer NOT NULL,
>cost integer NOT NULL,
>last_poi integer,
>FOREIGN KEY (unsorted_path) REFERENCES tour_blob(id) ON 
> DELETE CASACADE,
>FOREIGN KEY (unsorted_path_tmp) REFERENCES tour_blob(id) 
> ON DELETE CASACADE,
>PRIMARY KEY(id));
>
> You can replace tour_unsorted_path_idx with a new index too:
>
>> DROP INDEX IF EXISTS tour_unsorted_path_idx;
>> CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path);
>
>  DROP INDEX IF EXISTS tour_unsorted_path_last_poi_cost_idx;
>  CREATE INDEX tour_unsorted_path_last_poi_cost_idx ON tour (unsorted_path, 
> last_poi, cost);
>
> Take care and make cost the last one, because cost is compared with 
> inequality.
Very interesting, this sounds a very good point!

> You use '==' instead '=', take care too. I made the same error in a mail some 
> weeks ago.
Ops...

> For this query:
>
>>
>> SQL STATEMENT: SELECT id FROM tour ORDER BY id LIMIT ?1
>>  8< -
>> Query:   SELECT id FROM tour ORDER BY id LIMIT ?1
>> Explain: 0 0 0 SCAN TABLE tour USING INTEGER PRIMARY KEY (~100 rows)
>>  - >8
>
> I don't know why it doesn't use the primary index. Perhaps analyze statistics 
> before solves the problem.
It seems Explain does not consider the LIMIT, it should not be
something to worry about.

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

Re: [sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

Well, to make it binary compatible with existing virtual table
implementations, there could be added new function returning const pointer
to CollSeq structure from passed index contraint.
This function should be callable only from xBestIndex function.

Something like:
const CollSeq * sqlite3_vtab_constraint_coll_seq(const
sqlite3_index_constraint* pConstraint);

sqlite3_index_constraint contains iTermOffset, so to find a collation
sequence for it should not be so big problem.

How about it?


2013/7/17 Dušan Paulovič 

> But it is the same as with new functions in sqlite3_module. Old SQLite
> extensions does not implement xRename function which is now needed. Also,
> new feature could be made optional using macro switch like some other
> features in SQLite.
>
>
> 2013/7/17 Stephan Beal 
>
>> On Wed, Jul 17, 2013 at 12:24 PM, Dušan Paulovič 
>> wrote:
>>
>> > What do you mean? I use it for a while on Windows and all works.
>> > Binary not compatible with what?
>> >
>>
>> What Dan means is that libraries built with and without this feature might
>> not be binary compatible. i compile against an sqlite3 DLL without this
>> feature, then my sysadmin updates libsqlite3 to one with this feature, and
>> my binary will then likely segfault at some point (or otherwise invoke
>> undefined behaviour) because the binary signatures of the sqlite
>> structures
>> in my app no longer match those in the library. A recompile of my app
>> (with
>> no code changes) would be necessary to fix this.
>>
>> --
>> - stephan beal
>> http://wanderinghorse.net/home/stephan/
>> http://gplus.to/sgbeal
>> ___
>> 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] Memory usage of sqlite3

[Eduardo Morras]

On Wed, 17 Jul 2013 12:04:52 +0200
Paolo Bolzoni  wrote:

> On Tue, Jul 16, 2013 at 8:13 PM, Eduardo  wrote:
> 
> > Can you show us the query and/or schemas? If not:
> Sure, I appended everything in the bottom of this email.
> Unfortunately gmail will mess-up the layout, I hope it will be
> readable.
> 
> (See here, it seems google does not know the mean of "should")
> https://groups.google.com/forum/#!topic/google-mail-problem-solving-uk/p8KyYZR2e04
> 
> > a) Does it JOIN multiple big tables (in rows and/or columns)?
> No, but I got a sub query is probably the culprit.
> 
> > e) Did you normalize the database?
> Should be, yes.
> 
> > In both cases (if you can show us the query/schema or not) what do you 
> > really want to ask to the database? (not to us but to sqlite3, perhaps the 
> > query can be reformulated)
> 
> 
> Here is everything... I think the problem is shown in the:
> Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows)
> that is part of a sub-query.
> 
> 
> -- First statement (sets the pragmas):
> 
> PRAGMA foreign_keys = ON;
> PRAGMA synchronous = OFF;
> PRAGMA journal_mode = MEMORY;
> PRAGMA cache_size = -10240;
> PRAGMA auto_vacuum = NONE;
> PRAGMA locking_mode = EXCLUSIVE;
> PRAGMA secure_delete = OFF;
> PRAGMA temp_store = 0;
> 
> -- Second statement (creates/clean the schema):
> BEGIN TRANSACTION;
> DROP TABLE IF EXISTS tour;
> CREATE TABLE tour (id integer,
>   score integer NOT NULL,
>   cost integer NOT NULL,
>   last_poi integer,
>   unsorted_path blob,
>   unsorted_path_tmp blob,
>   PRIMARY KEY(id));
> 

Some changes, if blob is bigger than a few bytes, you should normalize them. If 
2 blobs are equal, their id must be equal and you don't waste time comparing 
nor memory joining blob content. So you get:

 DROP TABLE IF EXISTS tour_blob;
 CREATE TABLE tour_blob (id INTEGER PRIMARY KEY,
   n_blob blob);

 DROP TABLE IF EXISTS tour;
 CREATE TABLE tour (id integer,
   score integer NOT NULL,
   cost integer NOT NULL,
   last_poi integer,
   FOREIGN KEY (unsorted_path) REFERENCES tour_blob(id) ON 
DELETE CASACADE,
   FOREIGN KEY (unsorted_path_tmp) REFERENCES tour_blob(id) ON 
DELETE CASACADE,
   PRIMARY KEY(id));

You can replace tour_unsorted_path_idx with a new index too:

> DROP INDEX IF EXISTS tour_unsorted_path_idx;
> CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path);

 DROP INDEX IF EXISTS tour_unsorted_path_last_poi_cost_idx;
 CREATE INDEX tour_unsorted_path_last_poi_cost_idx ON tour (unsorted_path, 
last_poi, cost);

Take care and make cost the last one, because cost is compared with inequality.

You use '==' instead '=', take care too. I made the same error in a mail some 
weeks ago.

For this query:

> 
> SQL STATEMENT: SELECT id FROM tour ORDER BY id LIMIT ?1
>  8< -
> Query:   SELECT id FROM tour ORDER BY id LIMIT ?1
> Explain: 0 0 0 SCAN TABLE tour USING INTEGER PRIMARY KEY (~100 rows)
>  - >8

I don't know why it doesn't use the primary index. Perhaps analyze statistics 
before solves the problem.

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

[sqlite] Sqlite locking issue with ATTACH'ed databases

[Loren Keagle]

Hi everyone,

I have an interesting locking problem that I'm wondering if someone can help 
with some insight.

I have a master database with some metadata, and several sub-databases to store 
logging events. I have one reader object and one writer object that attach to 
the sub-databases and encapsulate the read/write operations respectively.

I've found a very unexpected locking behavior with the attached databases and 
exclusive transactions. One of my unit tests does the following:

Begin EXCLUSIVE TRANSACTION;
insert several rows of data;
Commit transaction;

Prepare query statement;
Iterate through one or more rows;
Reset statement;

Attempt to begin transaction; <--- SQLITE_BUSY
Would like to write more here, but can't unless I close/open the connection;

I can't seem to figure out any reason why I can't create a new exclusive 
transaction here, and I feel it must have to do with the fact that I have 
attached to sub-databases (possibly the same sub-database) with my 
reader/writer objects. This is single threaded and only database connection 
(with attach/detach logic).
I have verified that all statements prepared by the connection are properly 
reset - this is handled by my C++ wrappers, and any errors will throw an 
exception. I even iterated through all of the current statements with the 
following code immediately before my transaction failure, with no results:

sqlite3_stmt *stmt = NULL;
while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL)
{
if (sqlite3_stmt_busy(stmt))
{
const char* sql = sqlite3_sql(stmt);
std::cout << sql << "\r\n";
}
}

Can anyone think of a reason why attached databases would prevent entering a 
second transaction? BTW, it doesn't seem to work with immediate transactions 
either. If I remove the query, everything works fine.

Thanks!



This email, including any attachments and files transmitted with it, are for 
the sole use of the intended recipient(s) to whom this email is addressed, and 
may contain confidential and/or privileged information. Any unauthorized 
review, use, disclosure or distribution is prohibited. If you are not the 
intended recipient, please be advised that you have received this email in 
error, and please contact the sender by reply email and destroy all copies 
(including all electronic and hard copies) of the original message. Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[ibrahim]

On 16.07.2013 13:26, Bernd Lehmkuhl wrote:

On 16 Jul 2013, at 4:39am, Bernd Lehmkuhl  wrote:


Am 15.07.2013 22:26, schrieb Simon Slavin:

The following two statements do different things.

INSERT INTO myTable VALUES (01)
INSERT INTO myTable VALUES ('01')

Can you tell what's being done in your setup ? Is there a way using your API 
that you can stress that the value you're binding or inserting is text, not a 
number ?

As I use a parameterized query, I'm pretty certain that it should be '01' - the 
second case. Stepping through the code in VS Debugger also shows that DbType of 
that parameter is String and Value is '01'.

If the column in the table really is defined as TEXT, and the INSERT commands 
do have apostrophes around the values, then SQLite3 should not be losing that 
zero.

Can you open the database in some other tool (e.g. the sqlite3 command-line 
tool, available from the SQLite site) and see what the table schema says ? Your 
commands should be something like

sqlite3 myDatabaseFile
.schema
.quit

If you have lots of tables you can do ".schema mytable" instead of just 
".schema".

If you want to experiment you can manually type in an INSERT yourself, then do 
a SELECT and see whether the zeros were preserved.

Simon.

Hi Simon,

The actual table definition is :
CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL String, BESCHREIBUNG String, 
BEMERKUNG String, ID_GUID String, IST_PAUSCHALIERT String, 
IST_KATEGORIE_ESK_BETRIEBSSTOF String);

SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select schluessel, typeof(schluessel)
...> from t_vwg_abfallverzeichnis
...> where id_guid in ('BEE7F6108F02416FA0C9D5DC777DB172', 
'6061A3864C2546C4A7DDA9FDB321459F');
1|integer
01 01|text
sqlite>

I *expected* to gain TEXT affinity through the use of System.Data.SQLite and 
it's strongly typed types (doppelt gemoppelt?), but apparently this is not 
totally effective. Maybe Joe Mistachkin can say something about that?

Bernd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
String is not a valid type specifier for TEXT colums see 
http://www.sqlite.org/datatype3.html type names which result in TEXT 
affinity.


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

Re: [sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

But it is the same as with new functions in sqlite3_module. Old SQLite
extensions does not implement xRename function which is now needed. Also,
new feature could be made optional using macro switch like some other
features in SQLite.


2013/7/17 Stephan Beal 

> On Wed, Jul 17, 2013 at 12:24 PM, Dušan Paulovič 
> wrote:
>
> > What do you mean? I use it for a while on Windows and all works.
> > Binary not compatible with what?
> >
>
> What Dan means is that libraries built with and without this feature might
> not be binary compatible. i compile against an sqlite3 DLL without this
> feature, then my sysadmin updates libsqlite3 to one with this feature, and
> my binary will then likely segfault at some point (or otherwise invoke
> undefined behaviour) because the binary signatures of the sqlite structures
> in my app no longer match those in the library. A recompile of my app (with
> no code changes) would be necessary to fix this.
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> 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] SQLite3 : Corrupt/IO Error

[Paolo Bolzoni]

On Wed, Jul 17, 2013 at 12:55 PM, techi eth  wrote:
> 2)  How do we make database safe from these error Or What is Possibility 
> to
> access database after error.
After error, as Stephan said you are out of luck.

You should avoid this errors in the first place:
- make backup often,
- keep the safety features of sqlite3 on,
- use a error resilient filesystem (e.g., zfs),
- use ecc memory.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 : Corrupt/IO Error

[Stephan Beal]

On Wed, Jul 17, 2013 at 12:55 PM, techi eth  wrote:

> Hi,
>
> I have question on SQLITE_CORRUPT, SQLITE_IOERR occur during database
> access.
>
> Question
>
> 1)  How to get generated this kind of error for testing.
>

While your application has the db opened, use a tool of your choice to
overwrite part of the db file. e.g. load it in a text editor and re-save
it. That "should" cause your app to fail fairly quickly.


2)  How do we make database safe from these error Or What is
> Possibility to
> access database after error.
>

By definition, a corrupt db cannot be safely/reliably accessed. Once it is
corrupt, any access has undefined behaviour.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] function auxiliary data

[Riccardo Vianello]

Hi Roger,

thanks for the additional information (and also thanks a lot for APSW,
it's a really valuable tool).

On Wed, Jul 17, 2013 at 11:47 AM, Roger Binns  wrote:
> APSW does sqlite3_reset the statement in many circumstances, including
> hitting the end of the results and on errors.
>
> sqlite3_finalize is only called when the statement is being destroyed
> which includes eviction from the statement cache, and on getting
> SQLITE_SCHEMA error.
>
> I do wish SQLite had a standard statement cache available.  Pretty much
> all wrappers implement their own which is duplicate work and tricky to get
> right.

I think the statement cache is not probably the cause here (although
it's apparently quite convenient to me that APSW allows switching it
off and have the same statement compiled again, I'm not sure I can do
the same with pysqlite), but since sqlite3_reset doesn't clear the
binding and since (I guess) the relevant use case for these auxiliary
data is storing results that depend on the parameter values, if the
metadata is not destroyed when a parameter is reassigned then the
overall usability for this feature seems somewhat reduced to me.

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

[sqlite] SQLite3 : Corrupt/IO Error

[techi eth]

Hi,

I have question on SQLITE_CORRUPT, SQLITE_IOERR occur during database
access.

Question

1)  How to get generated this kind of error for testing.

2)  How do we make database safe from these error Or What is Possibility to
access database after error.

Cheers-

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

Re: [sqlite] Passing collation to xBestIndex

[Stephan Beal]

On Wed, Jul 17, 2013 at 12:24 PM, Dušan Paulovič  wrote:

> What do you mean? I use it for a while on Windows and all works.
> Binary not compatible with what?
>

What Dan means is that libraries built with and without this feature might
not be binary compatible. i compile against an sqlite3 DLL without this
feature, then my sysadmin updates libsqlite3 to one with this feature, and
my binary will then likely segfault at some point (or otherwise invoke
undefined behaviour) because the binary signatures of the sqlite structures
in my app no longer match those in the library. A recompile of my app (with
no code changes) would be necessary to fix this.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

What do you mean? I use it for a while on Windows and all works.
Binary not compatible with what?

Dusan


2013/7/17 Dan Kennedy 

> On 07/17/2013 03:46 PM, Dušan Paulovič wrote:
>
>> Hello,
>> in virtual table mechanism is missing a way to correctly handle following
>> queries:
>>
>> SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE;
>> SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE;
>>
>> To xBestIndex function is passed only constraint field = 'abc', but there
>> is no way to correctly compare (by correctly I mean accordingly to passed
>> collation) those strings because of missing collation functions in
>> constraint definition.
>>
>> Patch for this issue exists for a long time:
>> http://osdir.com/ml/sqlite-**users/2011-09/msg00152.html
>>
>> Is there any chance that it could get merged?
>>
>
> I think the main problem is that it is not binary compatible.
>
> Dan.
>
>
>
> __**_
> 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] Passing collation to xBestIndex

[Dan Kennedy]

On 07/17/2013 03:46 PM, Dušan Paulovič wrote:

Hello,
in virtual table mechanism is missing a way to correctly handle following
queries:

SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE;
SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE;

To xBestIndex function is passed only constraint field = 'abc', but there
is no way to correctly compare (by correctly I mean accordingly to passed
collation) those strings because of missing collation functions in
constraint definition.

Patch for this issue exists for a long time:
http://osdir.com/ml/sqlite-users/2011-09/msg00152.html

Is there any chance that it could get merged?


I think the main problem is that it is not binary compatible.

Dan.



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

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

On Tue, Jul 16, 2013 at 8:13 PM, Eduardo  wrote:

> Can you show us the query and/or schemas? If not:
Sure, I appended everything in the bottom of this email.
Unfortunately gmail will mess-up the layout, I hope it will be
readable.

(See here, it seems google does not know the mean of "should")
https://groups.google.com/forum/#!topic/google-mail-problem-solving-uk/p8KyYZR2e04

> a) Does it JOIN multiple big tables (in rows and/or columns)?
No, but I got a sub query is probably the culprit.

> e) Did you normalize the database?
Should be, yes.

> In both cases (if you can show us the query/schema or not) what do you really 
> want to ask to the database? (not to us but to sqlite3, perhaps the query can 
> be reformulated)


Here is everything... I think the problem is shown in the:
Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows)
that is part of a sub-query.


-- First statement (sets the pragmas):

PRAGMA foreign_keys = ON;
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
PRAGMA cache_size = -10240;
PRAGMA auto_vacuum = NONE;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA secure_delete = OFF;
PRAGMA temp_store = 0;

-- Second statement (creates/clean the schema):
BEGIN TRANSACTION;
DROP TABLE IF EXISTS tour;
CREATE TABLE tour (id integer,
  score integer NOT NULL,
  cost integer NOT NULL,
  last_poi integer,
  unsorted_path blob,
  unsorted_path_tmp blob,
  PRIMARY KEY(id));

DROP INDEX IF EXISTS tour_unsorted_path_idx;
CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path);

DROP TABLE IF EXISTS categories;
CREATE TABLE categories (tour_id integer NOT NULL,
order_idx integer NOT NULL,
value integer NOT NULL,
FOREIGN KEY (tour_id) REFERENCES tour(id) ON
DELETE CASCADE);

DROP INDEX IF EXISTS cats_tour_id_idx;
CREATE INDEX cats_tour_id_idx ON categories (tour_id);

DROP TABLE IF EXISTS path;
CREATE TABLE path (tour_id integer NOT NULL,
  order_idx integer NOT NULL,
  node_id integer NOT NULL,
  FOREIGN KEY (tour_id) REFERENCES tour(id) ON DELETE CASCADE);

DROP INDEX IF EXISTS path_tour_id_idx;
CREATE INDEX path_tour_id_idx ON path (tour_id);

DROP TRIGGER IF EXISTS set_last_poi_trg;
CREATE TRIGGER set_last_poi_trg AFTER INSERT ON path BEGIN
  UPDATE tour SET last_poi = ( SELECT node_id
   FROM path
   WHERE tour_id == NEW.tour_id
   ORDER BY order_idx DESC LIMIT 1 )
  WHERE id == NEW.tour_id; END;
COMMIT;
VACUUM;



-- Finally the statement executed in a normal program
-- execution with the explain if appliable.

-- I prepare all this  statements and put in hash table
-- and finalize them at the end of the program.
-- This output comes from the first time, when I store them.


SQL STATEMENT: INSERT INTO path (tour_id, order_idx, node_id) VALUES
(?1, ?2, ?3)

SQL STATEMENT: INSERT INTO tour (score, cost) VALUES (?1, ?2)

SQL STATEMENT: UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2
 8< -
Query:   UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2
Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
 - >8

SQL STATEMENT: UPDATE tour SET unsorted_path=unsorted_path_tmp,
unsorted_path_tmp = NULL WHERE id = ?1;
 8< -
Query:   UPDATE tour SET unsorted_path=unsorted_path_tmp,
unsorted_path_tmp = NULL WHERE id = ?1;
Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
 - >8


SQL STATEMENT: INSERT INTO categories (tour_id, order_idx, value)
VALUES (?1, ?2, ?3)

SQL STATEMENT: DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
 - >8

 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
Explain: 0 0 0 EXECUTE LIST SUBQUERY 0
 - >8

 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows)
 - >8

 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi 

Re: [sqlite] function auxiliary data

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

[I'm the APSW author]

On 17/07/13 00:59, Riccardo Vianello wrote:
> In a test performed using APSW I disabled the statements cache and the 
> code behaved as expected also with parameters binding. This brought me 
> to think that the metadata associated to the parameters in a compiled 
> statement might be preserved until the statement is finalized, but I 
> will investigate things more in detail.

APSW does sqlite3_reset the statement in many circumstances, including
hitting the end of the results and on errors.

sqlite3_finalize is only called when the statement is being destroyed
which includes eviction from the statement cache, and on getting
SQLITE_SCHEMA error.

I do wish SQLite had a standard statement cache available.  Pretty much
all wrappers implement their own which is duplicate work and tricky to get
right.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHmaBcACgkQmOOfHg372QS7nwCfYVAcvSQSD0/aNmY/OGwdG6KI
ZOQAmgLRuJaHxR+BJHSjtK9VU79yFlPR
=w0yh
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Passing collation to xBestIndex

[Dušan Paulovič]

Hello,
in virtual table mechanism is missing a way to correctly handle following
queries:

SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE;
SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE;

To xBestIndex function is passed only constraint field = 'abc', but there
is no way to correctly compare (by correctly I mean accordingly to passed
collation) those strings because of missing collation functions in
constraint definition.

Patch for this issue exists for a long time:
http://osdir.com/ml/sqlite-users/2011-09/msg00152.html

Is there any chance that it could get merged?

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

Re: [sqlite] function auxiliary data

[Riccardo Vianello]

Hi Igor,

On Tue, Jul 16, 2013 at 7:06 PM, Igor Tandetnik  wrote:
> The metadata only survives a single iteration over the statement; in other
> words, it is cleared when you call sqlite3_reset (from the documentation:
> "SQLite will invoke the destructor function... when the SQL statement
> completes"). And you can't rebind the parameter in the middle of an
> iteration. So it looks like everything should work the way you want.

This is what I hoped, but I'm using the auxdata functions in an
extension I'm developing, and the behavior I'm experiencing is a bit
different.

At present I'm accessing the database from python, so I don't know the
exact moment when sqlite3_reset might get called, but from what I'm
seeing, things work as expected as long as literals are used. With
parameters binding, if the same query is repeatedly executed, it
behaves as if metadata were preserved (in my case this means that the
actual values passed to later queries are ignored and the results are
determined by the values passed to the first query execution).

In a test performed using APSW I disabled the statements cache and the
code behaved as expected also with parameters binding. This brought me
to think that the metadata associated to the parameters in a compiled
statement might be preserved until the statement is finalized, but I
will investigate things more in detail.

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