[sqlite] UTF support

[J Decker]

I saw a few things go by about unicode... and understand that it should
just work to store the data as characters...

I'm getting a unrecognized token... and think this page isn't right...
I was playing with greek translation of 'mary had a little lamb'

http://www.sqlite.org/tokenreq.html

---
"MySQL allows identifiers to be quoted using the grave accent character.
SQLite supports this for interoperability.

H41160: SQLite shall recognize as an ID token any sequence of characters
that begins with a grave accent (u0060), is followed by zero or more
non-zero characters and/or pairs ofgrave accents (u0060) and terminates
with a grave accent (u0022) that is not part of a pair."

--


20:57:51.729|Editoptions@getoption_v4.c(472):Result of prepare failed?
unrecognized token: "'Μαίρη είχε " at char 0[replace into option4_values
(`option_id`,`string`,`segment` ) values
('8b377a68-4358-11e4-ace4-3085a9903449','Μαίρη είχε ένα μικρό αρνί',0)] in
[replace into option4_values (`option_id`,`string`,`segment` ) values
('8b377a68-4358-11e4-ace4-3085a9903449','Μαίρη είχε ένα μικρό αρνί',0)]


---

The actual data isn't as it looks here, as during the unicode to character
conversion of the select fix the wide characters that don't convert unto
utf-8 representations...

this is memory of the command.  that is being passed to prepare.
 between the ' ' there is no non zero character that is not a '

0x029844E8  72 65 70 6c 61 63 65 20 69 6e 74 6f 20 6f 70 74 69 6f
6e 34 5f 76 61 6c 75 65 73 20 28 60 6f 70 74  replace into option4_values
(`opt
0x02984509  69 6f 6e 5f 69 64 60 2c 60 73 74 72 69 6e 67 60 2c 60
73 65 67 6d 65 6e 74 60 20 29 20 76 61 6c 75  ion_id`,`string`,`segment` )
valu
0x0298452A  65 73 20 28 27 38 62 33 37 37 61 36 38 2d 34 33 35 38
2d 31 31 65 34 2d 61 63 65 34 2d 33 30 38 35  es
('8b377a68-4358-11e4-ace4-3085
0x0298454B  61 39 39 30 33 34 34 39 27 2c 27 e0 8e 9c e0 8e b1 e0
8e af e0 8f 81 e0 8e b7 20 e0 8e b5 e0 8e af  a9903449','àŽœàŽ±àŽ¯à..àŽ·
àŽµàŽ¯
0x0298456C  e0 8f 87 e0 8e b5 20 e0 8e ad e0 8e bd e0 8e b1 20 e0
8e bc e0 8e b9 e0 8e ba e0 8f 81 e0 8f 8c 20  à..àŽµ àŽ.àŽ.àŽ±
àŽ.àŽ.àŽºà..à.Œ
0x0298458D  e0 8e b1 e0 8f 81 e0 8e bd e0 8e af 27 2c 30 29 00 fe
ca ef be fe ca ef be cd fd fd fd fd ab ab ab
 àŽ±à..àŽ.àŽ¯',0).þÊï.þÊï.Í«««
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Behavior change: INTEGER PRIMARY KEY and PRAGMA index_list

[Paul Quinn]

You were totally correct, read data after done.
Thanks for your reply.

-Paul

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Clemens Ladisch
Sent: Sunday, October 05, 2014 3:05 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Behavior change: INTEGER PRIMARY KEY and PRAGMA index_list

Paul Quinn wrote:
> In 3.7, issuing "PRAGMA index_list(tablename)" to retrieve the details 
> of an index, sqlite3_column_count() would return 0 for automatic 
> integer primary key indexes, basically indicating there was no 
> explicit index created for that tablename. But in 3.8,
> sqlite3_column_count() returns '3' and we can proceed to evaluate the 
> primary key index. We noticed the 'name' column for this integer 
> primary key index is "" (empty string).

Neither in 3.7 nor in 3.8 does an INTEGER PRIMARY KEY column have an index (the 
rowid is part of the table B-tree structure), and neither version returns a row 
for this from PRAGMA index_list.

I don't know what your code does, but is it possible that it tries to read a 
row even when SQLITE_DONE was returned?


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] Sqlite giving error on unique constraint

[Keith Medcalf]

The table schema and insert statement might be useful ...

On Monday, 6 October, 2014 14:42, Jeffrey Parker  said:

>I am working with sqlite3 in python 2.7.8 and I am running into a strange
>error where I get the below exception when running an insert into
>statement
>on an empty table. I know this is probably more to do with the python
>libraries but I thought that I would ask the question here in case
>someone
>has any recommendations.
>
>sqlite3.IntegrityError: column VersionName is not unique
>
>The insert still works and when I do the next insert into that table
>there is no exception.




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

[sqlite] Sqlite giving error on unique constraint

[Jeffrey Parker]

Hello,

I am working with sqlite3 in python 2.7.8 and I am running into a strange
error where I get the below exception when running an insert into statement
on an empty table. I know this is probably more to do with the python
libraries but I thought that I would ask the question here in case someone
has any recommendations.

sqlite3.IntegrityError: column VersionName is not unique

The insert still works and when I do the next insert into that table
there is no exception.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] why does gperftools shows sqlite3_memory_used as using the largest amount of memory

[Clemens Ladisch]

Mayank Kumar (mayankum) wrote:
> will the memory used by sqlite keep on increasing, if we don't
> finalize at all during the life time of the db, as we do more insert/
> delete/replace operations ?

Every prepared statement needs some memory (but only a small amount).
As long as you are using these statements, there is no problem.

Anyway, prepared statements do not occupy 86 MB; this sounds like
the page cache.


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

Re: [sqlite] why does gperftools shows sqlite3_memory_used as using the largest amount of memory

[Mayank Kumar (mayankum)]

Thanks Clemens.

Gperftool a malloc replacement, profiler and leak checker, reports that the 
largest amount of memory was being used by sqlite3_memory_used api which sounds 
weird, as it just reports the value of some counters. So it might be an error 
in gperftools itself. Not sure. 

I found that throughout the value of sqlite3_memory_used is always roughly 
around 68MB and still free -lm shows high memory getting exhausted when my 
application runs. 

I guess my question is will the memory used by sqlite keep on increasing, if we 
don't finalize at all during the life time of the db, as we do more 
insert/delete/replace operations ?

-Mayank

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Clemens Ladisch
Sent: Friday, October 03, 2014 1:40 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] why does gperftools shows sqlite3_memory_used as using 
the largest amount of memory

Mayank Kumar (mayankum) wrote:
> -my application sqlite based runs for months before it might get 
> restarted -while its running there are places we need to execute the 
> following series :-
>   - sqlite3_bind_int64(deleteStmt
>   - sqlite3_step(deleteStmt)
>   - sqlite3_reset(deleteStmt);
>
> -note that the prepare and finalize on these happens only once at the time of 
> process start and exit respectively.
>
> Do you  think this can result in uncontrolled use of memory by sqlite in our 
> application ?

No; this is perfectly fine.


But why do you think that the large amount of sqlite3_memory_used is a problem?
SQLite _uses_ memory for caching; that memory is not leaked.


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] Filling struct Select in sqlite

[Kees Nuyt]

On Mon, 6 Oct 2014 10:28:59 +0530, Prakash Premkumar
 wrote:

>Instead of generating an sql query for a select statement, I would like to
>bypass the parser and fill in struct Select by myself based on the data I
>have and Pass it to sqlite3Select() function. Can you please give me some
>pointers in this direction ?
>
>I think eliminating parsing would be an optimization.

Hi Prakash,

Are you aware you can re-use a compiled statement?

If you use placeholders instead of literals where values are
expected in SQL statements, it suffices to sqlite3_bind() new
values, sqlite3_step(), and finally sqlite3_reset() to use the
same compiled statement again and again.

That way, there is no need to _finalize() and _prepare() every
time, and you may not need that optimization at all.

-- 
Groet,

Kees Nuyt

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

Re: [sqlite] Memory Usage

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/02/2014 03:12 PM, David Muchene wrote:
> I was wondering what options I can tune to make sqlite use more
> memory.

Have you tried using memory mapping (there is a pragma).

  https://sqlite.org/mmap.html

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlQyunsACgkQmOOfHg372QQtIgCbBEBxTOeztFZcDRi2f1mCtVI3
Z5AAn39YIdXxxsc6CA6DrwSx94r9Ck6D
=KBxA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] struct SrcList

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/06/2014 04:41 AM, Prakash Premkumar wrote:
> Can you please show me where in sqlite code the struct SrcList is
> filled up ?

You keep asking people to do lots of work on your behalf without
showing you have made any effort yourself.  You have the same tools
the rest of us do.

Why don't you open the source files and search for the code yourself?
 Do a google search for tools that help navigate source code?  For
example cscope is an old command line tool for doing just that.  Show
us why you couldn't work out the answers yourself first.

Avoid being a help vampire:

  http://www.skidmore.edu/~pdwyer/e/eoc/help_vampire.htm

Ask smart questions:

  http://catb.org/~esr/faqs/smart-questions.html

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlQyuc8ACgkQmOOfHg372QQSnQCeJ0smBuAjhWBpb6S3Tl9ouTcT
HhwAoLyIXKfhDkOJeQG6GLac137u/kyY
=SaMM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Long lived prepared statements

[Jim Dodgen]

Thanks Mark,

That confirms what I have been observing.
I have been running a test for about 12 hours doing the reused prepare and
I detect no leakage.

A question about your reply about the automatic finish at the last
fetchrow.  I assume this is the same finish of the handle obtained from my
prepare.  But because I have a reference to the handle holed away it is not
really "finished" and that is why I am able to continue to use the prepared
statement? Is this assumption correct?

*Jim Dodgen*







On Mon, Oct 6, 2014 at 12:08 AM, Mark Lawrence  wrote:

> > I think I know the answer to this so I'm looking for some
> > conformation.  What I do is to prepare a relatively large and complex
> > query. Then I run the same query forever  never doing a finalize. My
> > assumption is I will have no memory leakage.
>
> In principle this should be fine, and if it isn't then it would be a
> bug somewhere, either in the SQLite code (very unlikely) or the
> DBD::SQLite driver for Perl's DBI interface (also unlikely).
>
> Simon wrote:
>
> > When you are finished with the query you should either end on a
> > _reset() or do a _finalize().  Or (harmlessly) do both.  If you do
> > not do one of those, you may find that when you _close() some memory
> > is not released for the statement and/or the database (I'm not sure
> > which, but either way it's bad).
>
> On the Perl/DBI side of things these actions are usually be taken care
> of automatically when the associated objects holding the relevant
> resources go out of scope.
>
> On Sat Oct 04, 2014 at 03:16:23PM -0700, Jim Dodgen wrote:
> > It might be I need more of a Perl DBI question the order of the
> > statements I do are as follows
> >
> > prepare  >> - Done once
> >
> > execute  << done many times followed by: fetchrow_array << until
> > exhausted
> >
> > finish << never done
> >
> > I just don't see that the Execute/Fetchrow-array activity is going to
> > leave a handle or some resource dangling
>
> The above steps are exactly (but not exclusively) what the Perl DBI was
> intended to support. Although I haven't specifically measured the
> memory use, I do the above quite a lot without a problem.
>
> By the way, the last call to fetchrow_array() (that returns 'undef')
> implicitly calls finish() internally.
>
> --
> Mark Lawrence
> ___
> 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 error messages to pysqlite

[Mark Halegua]

Is anyone able to tell me why I'm having this commit problem in python?
 
Mark

On Saturday, October 04, 2014 11:43:41 AM Mark Halegua wrote:
> Just tried the same code on a different system, using sqlite3 3.8.2
> 
> Same result.
> 
> Mark
> 
> On Saturday, October 04, 2014 10:44:20 AM Mark Halegua wrote:
> > Kieth,
> > 
> > thanks for the info, however, I tested the following code on a LOCAL copy
> > of the database, one where at the sqlite3 command line I was able to do
> > an insert without problems, and yet, the python code returned the
> > 'database is locked; error message in the code (I used a wxpython
> > messagebox widget to display the error).
> > 
> > Why would it return a locked message in this case?
> > 
> > Here's the code:
> > def OnSave(self, event):
> > self.dbupdated = True
> > self.pub_title = self.pub_text.GetValue()
> > self.pub_remarks = self.remarks_text.GetValue()
> > print self.pub_title
> > print self.pub_remarks
> > # update relevant tables(s)
> > self.cdata.execute('insert into publishers(publisher_name,
> > 
> > remarks)  values("test", "remarks");')
> > 
> > try:
> > self.database.commit()
> > 
> > except sqlite.Error, dberr:
> > wx.MessageBox(dberr.args[0], 'DB Warning',
> > 
> > wx.OK)
> > 
> > self.dbupdated = False
> > 
> > # clear fields
> > 
> > if self.dbupdated:
> > self.pub_text.SetValue(' ')
> > self.remarks_text.SetValue(' ')
> > 
> > print 'dbupdated = ' , self.dbupdated
> > print self.add
> > self.add = False
> > print self.add
> > 
> > sqlite3 version 3.7.4
> > 
> > Mark
> > 
> > On Friday, October 03, 2014 10:37:06 PM Keith Medcalf wrote:
> > > Yes.  pysqlite/sqlite3 in python tries to manage transactions for you by
> > > automatically starting them, and you need to commit them yourself.  This
> > > is
> > > controlled by the isolation_level attribute set on the connection (can
> > > also
> > > be set as a parameter when you open the connection).
> > > 
> > > The default value is '' (an empty string).  Other valid values are
> > > 'IMMEDIATE', 'EXCLUSIVE' and None.  Basically, pysqlite tries to detect
> > > the
> > > type of statement you are running and if it is a DML statement (select,
> > > insert, update, delete) it automatically begins a transaction for you if
> > > one is not in progress by first doing a 'BEGIN '+isolation_level.  If
> > > the
> > > statement is something other than DML, then it will 'COMMIT' before
> > > executing the DDL (CREATE/DROP) then it will 'COMMIT' after executing
> > > it.
> > > It does not know about the "WITH" statement, so treats those as DDL
> > > rather
> > > than DML.
> > > 
> > > Setting isolation_level to None tells pysqlite/sqlite3 to not manage
> > > transactions and let the SQLite3 engine do it as it would from the C
> > > interface or the sqlite3.exe command line tool (what the dbapi calls
> > > autocommit mode), so you have to issue your own BEGIN and
> > > COMMIT/ROLLBACK
> > > commands where you want them and the pysqlite/sqlite3 interface modules
> > > will not attempt to begin or commit transactions for you.
> > > 
> > > On Friday, 3 October, 2014 21:48, you wrote:
> > > >Ah, ok.  after doing a database commit I get the error going to stderr.
> > > >Now it's just a matter
> > > >of capturing/redirecting the stderr output and using an except there.
> > > >
> > > >On Friday, October 03, 2014 11:35:08 PM you wrote:
> > > >> the sqlite3 command line doesn't require a commit, it gave an error
> > > >> after the attempted insert command.
> > > >> 
> > > >> pysqlite requires one?
> > > >> 
> > > >> Mark
> > > >> 
> > > >> On Friday, October 03, 2014 09:06:56 PM Keith Medcalf wrote:
> > > >> > Are you committing the change?
> > > >> > 
> > > >> > >-Original Message-
> > > >> > >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > >> > >boun...@sqlite.org] On Behalf Of Mark Halegua
> > > >> > >Sent: Friday, 3 October, 2014 20:58
> > > >> > >To: sqlite-users@sqlite.org
> > > >> > >Subject: [sqlite] passing error messages to pysqlite
> > > >> > >
> > > >> > >I have a sqlite3 database.  In the networkied are I have the db is
> > > >
> > > >locked
> > > >
> > > >> > >(wee've
> > > >> > >discussed this before, and I'm using it mostly on a local machine,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best page size and cache size in high memory environment

[Paul van Helden]

Hi All,

My application uses temporary tables that can become quite big (>300MB), so
it makes sense to me to change the page size and cache size with pragma
statements.

The docs say "The default suggested cache size is 2000 pages"

and

"The normal configuration for SQLite running on workstations is for atomic
write to be disabled, for the maximum page size to be set to 65536, for
SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the maximum default page size
to be set to 8192."

So I set my page size to 8192. That means the suggested cache size is still
only 16MB.

If I increase my cache size to 20 000 pages I see a lot more that 10x
increase in memory use and also very slow connection close, presumably due
to deallocation of page memory.

Should I increase page size instead? What do you guys do when it is OK for
SQLite to chew as much memory as you have?

Also, does it make sense to change the page size only for the temp
database? "pragma temp.page_size=65536" ?

Regards,

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

[sqlite] share data across connections

[Marcel Pieruzek]

Dear all,
I'd like to ask for your advice.

My goal is to perform some kind of different math computations 
(whatever) over a _subset_ of items (records) stored in my sqlite DB.

Each record has a number of properties stored in the same DB.
Each calculation needs only _one_ parameter of a certain record.
All calculations are _independent_ of each other.
No DB writing, data is being read only.
SQLite compiled using "SQLITE_THREADSAFE 2".

To achieve this, I wanted to use following pattern:
  1) create a temp table and store ID's of all items (records) I'm 
interested in
  2) fire several threads concurrently to read different properties of 
a record and calculate the results independently


The criteria in 1) may not be trivial, therefore should be executed only 
once.
Because the number of properties read from DB and calculated is dynamic 
and determined at run-time,
each property must be read using a separate sqlite3_stmt object (and a 
separate connection object).


Is there any other way how to share the list of records determined in 
step 1) across multiple connections/threads for further processing?


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

[sqlite] SQL logic error or missing database no such table: config

[Alessio Fabriziani]

Hello,
Using SQLite for C# (package from NuGet).
In some cases...I get this exception but database and table are present.

Why I get this exception? Corruption? What?

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

Re: [sqlite] Comiple time warning: possible array index out of bound within assert() in an error path

[Richard Hipp]

On Sun, Oct 5, 2014 at 1:54 PM, ISHIKAWA,chiaki 
wrote:

>
> I am reporting a suspicious compile-time error warning and
> a fix to avoid a negative array index access during run time within
> an assert().
>

This harmless compiler warning should now be fixed.

But as an aside:  The warning was inside an assert() statement, which
should be a no-op unless you compile with -DSQLITE_DEBUG.  Are you
generating a debugging build?  I ask because the -DSQLITE_DEBUG
compile-time option makes the SQLite library use about 2.4 times more CPU
time.  The -DSQLITE_DEBUG option is great for testing and debugging work,
but for a release build, or for any build where performance is important,
it should be omitted.

-- 
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] Performance gain in SQLite

[David Bicking]

I recall there is or once was a way to compile sqlite so that you could
embed in your program the pre-generated sql. This was for embedded
programs, not to speed things up, but to remove the parser and save
memory in very small embedded systems. For it to work, the sql and the
database schema couldn't change.

I haven't heard this mentioned in a very long time, so I don't know that
it is still supported anymore. But it might be worth your searching the
sqlite web site, or the user list from a few years back.

David

On 10/06/2014 07:50 AM, Prakash Premkumar wrote:
> Will I get any performance benefits if I directly generate the Vdbe program
> instead of generating the sql string ?
>
> My application emits out sql strings for execution. Instead if I generate
> the Vdbe program myself , will I get performance benefits since, I am
> bypassing the parser.
>
> Thanks
> Prakash
> ___
> 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] Comiple time warning: possible array index out of bound within assert() in an error path

[ISHIKAWA,chiaki]

Hi,

Thank you for the great package.

I am reporting a suspicious compile-time error warning and
a fix to avoid a negative array index access during run time within
an assert().

Background:

I was compiling mozilla thunderbird, and ever since I switched to
GCC 4.9.1 with its improved compile-time check, I received
a compile-time warning during the compilation of
sqlite3 source which mozilla
has imported for its own use.


This is inside a function called fts2EvalIncrPhraseNext().

The warning is of the following form (the line numbers are slightly off
due to the revisions between the time mozilla imported the source and
the current pre-released zip I obtained from your
web server. As of today, it would be
line 133915 in sqlite-amalgamation-201409301904.zip
(1.47 MiB)

WARNING lines:

/REF-COMM-CENTRAL/comm-central/mozilla/db/sqlite3/src/sqlite3.c: In
function ‘fts3EvalNextRow.part.612’:
/REF-COMM-CENTRAL/comm-central/mozilla/db/sqlite3/src/sqlite3.c:131262:19:
warning: array subscript is below array bounds [-Warray-bounds]
   assert( rc!=SQLITE_OK || a[p->nToken-1].bIgnore==0 );
   ^
Looking at the code, it became obvious that
the assert() is called in an else clause of
an if in the following form.

if (p->nToken == 1 && p->bIncr ) {
  ...
} else {
  ... assert()...
 }

So that means the compiler deduces that p->nToken can be either larger
than 1 (> 1) or smaller than 1 (meaning 0 ).
So if p->nToken == 0 during runtime, then it would cause a negative
index to be used within the assert() statement.

Now, of course, logically this may not happen from the behavior of
sqlite3. I am not familiar with the code.

However, the following patch fixes the warning to disappear and
takes care of the strange case of p->nToken == 0 (or smaller. Not sure
if nToken is unsigned) as well.

I am keen on seeing compiler warnings disappear from
the compilation of mozilla software. Since sqlite3 is imported from
upstream site, i.e. yours, I would like to see the
issue solved here.

Thank you in advance for your attention.

# HG changeset patch
# Parent 089cc337820f0ed141601f4fb1477d57583a5eed
# User ISHIKAWA, Chiaki 
In an error path, an index to an array can be negative.



diff --git a/db/sqlite3/src/sqlite3.c b/db/sqlite3/src/sqlite3.c
--- a/db/sqlite3/src/sqlite3.c
+++ b/db/sqlite3/src/sqlite3.c
@@ -129044,17 +129044,17 @@ static int fts3EvalIncrPhraseNext(
   /* Advance the iterator for each token in the phrase once. */
   for(i=0; rc==SQLITE_OK && inToken && bEof==0; i++){
 rc = incrPhraseTokenNext(pTab, p, i, [i], );
 if( a[i].bIgnore==0 && (bMaxSet==0 || DOCID_CMP(iMax,
a[i].iDocid)<0) ){
   iMax = a[i].iDocid;
   bMaxSet = 1;
 }
   }
-  assert( rc!=SQLITE_OK || a[p->nToken-1].bIgnore==0 );
+  assert( rc!=SQLITE_OK || ((p->nToken >= 1) &&
a[p->nToken-1].bIgnore==0) );
   assert( rc!=SQLITE_OK || bMaxSet );

   /* Keep advancing iterators until they all point to the same
document */
   for(i=0; inToken; i++){
 while( rc==SQLITE_OK && bEof==0
 && a[i].bIgnore==0 && DOCID_CMP(a[i].iDocid, iMax)<0
 ){
   rc = incrPhraseTokenNext(pTab, p, i, [i], );


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

Re: [sqlite] Performance gain in SQLite

[Swithun Crowe]

Hello

PP> Will I get any performance benefits if I directly generate the Vdbe
PP> program instead of generating the sql string ?

I think any (tiny) performance gains will be offset by the extra work
required to create the VDBE program and to keep your code up to date with
SQLite. SQL (the language) won't change, but the internals of SQLite can
change.

PP> My application emits out sql strings for execution. Instead if I
PP> generate the Vdbe program myself , will I get performance benefits
PP> since, I am bypassing the parser.

Rather than generating SQL strings to the pass to SQLite, can you create
SQL statements, prepare them and bind values to them? This way, the
parsing is only done once per statement.

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

[sqlite] Performance gain in SQLite

[Prakash Premkumar]

Will I get any performance benefits if I directly generate the Vdbe program
instead of generating the sql string ?

My application emits out sql strings for execution. Instead if I generate
the Vdbe program myself , will I get performance benefits since, I am
bypassing the parser.

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

Re: [sqlite] struct SrcList

[Prakash Premkumar]

Thanks for your reply Hick. Can you please show me where in sqlite code the
struct SrcList is filled up ?

Thanks
Prakash

On Mon, Oct 6, 2014 at 5:01 PM, Hick Gunter  wrote:

> I estimate that you have about a  1 : 2^^32 chance of assigning the
> correct value. I guess it is an index into a table of cursors required for
> processing the statement and there will be assertions to satisfy.
>
> -Ursprüngliche Nachricht-
> Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> Gesendet: Montag, 06. Oktober 2014 11:59
> An: General Discussion of SQLite Database
> Betreff: [sqlite] struct SrcList
>
> Hi,
>
> struct SrcList has a field int iCursor, if I'm manually filling up this
> struct ,can I assign any integral value to iCursor,so that this value can
> be used by OpenRead or OpenReadWrite opcode when it accesses this
> particular table ?
>
> Thanks
> Prakash
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> 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] struct SrcList

[Hick Gunter]

I estimate that you have about a  1 : 2^^32 chance of assigning the correct 
value. I guess it is an index into a table of cursors required for processing 
the statement and there will be assertions to satisfy.

-Ursprüngliche Nachricht-
Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
Gesendet: Montag, 06. Oktober 2014 11:59
An: General Discussion of SQLite Database
Betreff: [sqlite] struct SrcList

Hi,

struct SrcList has a field int iCursor, if I'm manually filling up this struct 
,can I assign any integral value to iCursor,so that this value can  be used by 
OpenRead or OpenReadWrite opcode when it accesses this particular table ?

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] struct SrcList

[Prakash Premkumar]

Hi,

struct SrcList has a field int iCursor, if I'm manually filling up this
struct ,can I assign any integral value to iCursor,so that this value can
 be used by OpenRead or OpenReadWrite opcode when it accesses this
particular table ?

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

Re: [sqlite] Curious datetime/strftime BUG?

[Clemens Ladisch]

John wrote:
> On 5/10/2014 19:59, Clemens Ladisch wrote:
>> The documentation  says:
>> | These functions only work for dates between -01-01 00:00:00 and
>> | -12-31 23:59:59. For dates outside that range, the results of
>> | these functions are undefined.
>
> All equivalent functions should return consistent results.

Why do you assume that "undefined" should imply consistency?  datetime()
could return "Cthulhu fhtagn" at the Ides of any month in such a year,
and there would be nothing wrong with it.  Undefined allows _anything_.


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

Re: [sqlite] Filling struct Select in sqlite

[Clemens Ladisch]

Prakash Premkumar wrote:
> Instead of generating an sql query for a select statement, I would like to
> bypass the parser and fill in struct Select by myself based on the data I
> have and Pass it to sqlite3Select() function. Can you please give me some
> pointers in this direction ?

Just do the same that sqlite3_prepare_v2() and all the functions called from
it do.

(For a generic question like this, no more specific answer is possible.)

> I think eliminating parsing would be an optimization.

"Think" is the wrong word to use here.  Optimizations make sense only
when you have identified a bottleneck by measuring.


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

Re: [sqlite] Long lived prepared statements

[Mark Lawrence]

> I think I know the answer to this so I'm looking for some
> conformation.  What I do is to prepare a relatively large and complex
> query. Then I run the same query forever  never doing a finalize. My
> assumption is I will have no memory leakage.

In principle this should be fine, and if it isn't then it would be a
bug somewhere, either in the SQLite code (very unlikely) or the
DBD::SQLite driver for Perl's DBI interface (also unlikely).

Simon wrote:

> When you are finished with the query you should either end on a
> _reset() or do a _finalize().  Or (harmlessly) do both.  If you do
> not do one of those, you may find that when you _close() some memory
> is not released for the statement and/or the database (I'm not sure
> which, but either way it's bad).

On the Perl/DBI side of things these actions are usually be taken care
of automatically when the associated objects holding the relevant
resources go out of scope.

On Sat Oct 04, 2014 at 03:16:23PM -0700, Jim Dodgen wrote:
> It might be I need more of a Perl DBI question the order of the
> statements I do are as follows
> 
> prepare  >> - Done once
> 
> execute  << done many times followed by: fetchrow_array << until
> exhausted
> 
> finish << never done
>
> I just don't see that the Execute/Fetchrow-array activity is going to
> leave a handle or some resource dangling

The above steps are exactly (but not exclusively) what the Perl DBI was
intended to support. Although I haven't specifically measured the
memory use, I do the above quite a lot without a problem.

By the way, the last call to fetchrow_array() (that returns 'undef')
implicitly calls finish() internally.

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