[sqlite] sqlite3_trace active on FTS3 (sub-)statements, pathologically bad at stringifying (segment) BLOBs (quasi-enhancement request)

[Andrew Sutherland]

In our Mozilla debug builds we use sqlite3_trace to expose the SQL 
statements that are invoked via our PR_LOG logging subsystem.  This 
usually works out well but the trace also catches FTS3's statements in 
its net.  During a segment merge, a segment in a Thunderbird FTS3 table 
can easily be >100k.

Unfortunately, the calls to stringify the BLOB end up growing the string 
"%02x" character by "%02x" character, and sqlite3StrAccumAppend grows 
its allocation size linearly and without using realloc.  This results in 
a tremendous number of calls to the allocator and a lot of memory 
traffic (both copying and memsetting).

While this is definitely one of those "well, don't do that" cases, I 
think it would be beneficial if one or more of the following were to 
occur, if only because it would be nice to not have to turn 
sqlite3_trace off on any connection that uses FTS3.

1) Cause FTS3 statements to not generate OP_Trace codes or to ignore 
them during evaluation.
2) Use realloc in sqlite3StrAccumAppend so the memory allocator has a 
fighting chance.  (With the large segment sizes at play, the mozilla 
jemalloc, for example, ends up unmapping things at least some of the time.)
3) Use a more efficient BLOB stringification routine.
4) Allow not stringifying BLOBs at all.
5) Place an upper bound or allow the sqlite3_trace invocation to place 
an upper bound on how large the SQL string can grow.

It's very survivable if none of the above ever happen, too.

Here's a snippet of an unhappy stack using the 3.7.4 amalgamation in the 
off-chance it helps provide better context:
#7  sqlite3Malloc (n=264183) at sqlite3/src/sqlite3.c:17763
#8  sqlite3DbMallocRaw (db=ptr, n=264183) at sqlite3.c:18088
#9  sqlite3StrAccumAppend (p=ptr, z=ptr, N=2) at sqlite3.c:19002
#10 sqlite3VXPrintf (pAccum=ptr, useExtended=1, fmt=ptr "x", 
ap=0x7fcaba7fc8e0) at sqlite3.c:18952
#11 sqlite3XPrintf (p=ptr, zFormat=ptr "%02x") at sqlite3.c:19241
#12 sqlite3VdbeExpandSql (p=ptr, zRawSql=ptr ")") at sqlite3.c:59748
#13 sqlite3VdbeExec (p=0x7fcaac70c5d8) at sqlite3.c:66203
#14 sqlite3Step (p=ptr) at sqlite3.c:58704
#15 sqlite3_step (pStmt=ptr) at sqlite3.c:58768
#16 fts3WriteSegment (p=ptr, iBlock=ptr, z=ptr "", n=166139) at 
sqlite3.c:115871

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

Re: [sqlite] How can we put check constraints on a column

[Dustin Sallings]

On Jan 20, 2011, at 20:51, Sunil Bhardwaj wrote:

> How can we put check constraints on a column in sqlite, while creating a 
> table.
> 
> We want to restrict values for a column in a range say '1 to 10'.


The standard SQL way works.  Are you experiencing otherwise?  I did a 
google search for "sql check constraint'' and pasted in the first results and 
it worked as expected.

-- 
dustin sallings

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

[sqlite] How can we put check constraints on a column

[Sunil Bhardwaj]

Hi

How can we put check constraints on a column in sqlite, while creating a table.

We want to restrict values for a column in a range say '1 to 10'.

Thanks
Sunil Bhardwaj
Ext. 1125 (0120-2567001)
9818868910
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Patch to allow DISTINCT in multi-argument aggregate functions

[Simon Slavin]

On 20 Jan 2011, at 7:48pm, Eric Smith wrote:

> Stephen Oberholtzer wrote:
> 
>> Good news, everyone! I've taught SQLite to feel love!
> 
> FINALLY.  I put in that feature request like 3 years ago.

Unfortunately it has to be wrapped in a transaction, and if you don't begin one 
yourself SQLite makes one for you.

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

Re: [sqlite] Patch to allow DISTINCT in multi-argument aggregate functions

[Eric Smith]

Stephen Oberholtzer wrote:

> Good news, everyone! I've taught SQLite to feel love!

FINALLY.  I put in that feature request like 3 years ago.

--
Eric A. Smith

Computer programs don't like being anthropomorphized.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Patch to allow DISTINCT in multi-argument aggregate functions

[Stephen Oberholtzer]

Good news, everyone! I've taught SQLite to feel love!

No, wait, that's not it. I spotted the thread 'how to use group_concat
uniquely' today, and felt inspired, so I wrote a quick patch.

The patch allows a multi-argument aggregate function to be passed
DISTINCT, under the restriction that every other argument must be
constant.

This allows for, among other things, the following syntax:

sqlite> select group_concat(distinct type, ';') from sqlite_master;
table;index

But you still can't do it if there is more than one column referenced:

sqlite> select group_concat(distinct type, tbl_name) from sqlite_master;
Error: DISTINCT aggregates must have at most one non-constant argument

I know how I would implement that, but that's way more effort than I'm
willing to expend right now.


Here's a quick diff.  If people (in particular, DRH) think it's worth
it, I'll write up the test cases and submit a more formal patch.

Index: src/select.c
===
--- src/select.c
+++ src/select.c
@@ -3487,10 +3487,74 @@
   if( pParse->nErr || db->mallocFailed ) return;
   sqlite3SelectAddTypeInfo(pParse, p);
 }

 /*
+** Set up and validate an aggregate being called with a DISTINCT argument.
+**
+** Verifies that one of the following is true:
+**
+** *  The aggregate takes exactly one argument.
+** *  The aggregate is being passed at most one non-constant argument.
+**
+** If neither of these is satisfied, the return value is zero.
+** Otherwise, the return value is nonzero, and the following are performed:
+**
+** * pFunc->iDistinctArg is set to the index of the argument that is
+**   DISTINCT.
+** * keyExprList is filled out to refer to only the distinct column.
+**   Note that all pointers are aliased to the original expression list;
+**   no freeing must be done.
+**
+*/
+
+static int setupDistinctAggregate(Parse *pParse, struct AggInfo_func
*pFunc, ExprList *keyExprList){
+  int i;
+  ExprList *pEL = pFunc->pExpr->x.pList;
+  struct ExprList_item *pELi;
+  pFunc->iDistinctArg = -1;
+  if( pEL==0 ){
+return 0;
+  }
+  if( pEL->nExpr==1 ){
+/* If there is only one argument to the aggregate, that argument
must be the
+** DISTINCT one.
+*/
+
+pFunc->iDistinctArg = 0;
+  }else{
+/* Okay, there is more than one argument to this aggregate.
+** Require that at most one is non-constant.
+*/
+for(i=0, pELi=pEL->a; inExpr; i++, pELi++){
+  if( !sqlite3ExprIsConstantOrFunction(pELi->pExpr) )
+  {
+if( pFunc->iDistinctArg>=0 ){
+  return 0;
+}
+pFunc->iDistinctArg = i;
+  }
+}
+
+if( pFunc->iDistinctArg<0 ){
+  /* All of the arguments passed to this function are constant.
+  ** Arbitrarily choose the first argument for the DISTINCTness.
+  ** TODO: somehow degrade this to a non-DISTINCT for this case.
+  */
+  pFunc->iDistinctArg = 0;
+}
+  }
+
+  keyExprList->nExpr = 1;
+  keyExprList->nAlloc = 0;
+  keyExprList->iECursor = pEL->iECursor;
+  keyExprList->a = >a[pFunc->iDistinctArg];
+
+  return 1;
+}
+
+/*
 ** Reset the aggregate accumulator.
 **
 ** The aggregate accumulator is a set of memory cells that hold
 ** intermediate results while calculating an aggregate.  This
 ** routine simply stores NULLs in all of those memory cells.
@@ -3507,17 +3571,18 @@
   }
   for(pFunc=pAggInfo->aFunc, i=0; inFunc; i++, pFunc++){
 sqlite3VdbeAddOp2(v, OP_Null, 0, pFunc->iMem);
 if( pFunc->iDistinct>=0 ){
   Expr *pE = pFunc->pExpr;
+  ExprList keyEL;
   assert( !ExprHasProperty(pE, EP_xIsSelect) );
-  if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
-sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
-   "argument");
+  if( !setupDistinctAggregate(pParse, pFunc, ) ){
+sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have at most one "
+   "non-constant argument");
 pFunc->iDistinct = -1;
   }else{
-KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList);
+KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, );
 sqlite3VdbeAddOp4(v, OP_OpenEphemeral, pFunc->iDistinct, 0, 0,
   (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
   }
 }
   }
@@ -3565,12 +3630,12 @@
   nArg = 0;
   regAgg = 0;
 }
 if( pF->iDistinct>=0 ){
   addrNext = sqlite3VdbeMakeLabel(v);
-  assert( nArg==1 );
-  codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg);
+  //assert( nArg==1 );
+  codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg +
pF->iDistinctArg);
 }
 if( pF->pFunc->flags & SQLITE_FUNC_NEEDCOLL ){
   CollSeq *pColl = 0;
   struct ExprList_item *pItem;
   int j;

Index: src/sqliteInt.h
===
--- src/sqliteInt.h
+++ src/sqliteInt.h
@@ -1530,10 +1530,11 @@
   struct AggInfo_func {   /* For each aggregate function */
 

Re: [sqlite] Documentation typos - part no.3

[Richard Hipp]

On Thu, Jan 20, 2011 at 4:16 AM, Marian Cascaval
wrote:

> Hi!
>
> Again, some more Documentation typos
> with some fun while we're at it:
>

Thanks for sending these in.  I have implemented fixes which have been
uploaded to the "draft" website at http://www.sqlite.org/draft/index.html or
which can be seen at the documentation source repository
http://www.sqlite.org/docsrc/ci/9101dd1c25

Please let me know if you find any other problems.
-- 
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] Patch: FTS3 Snawball Stemmer support

[Tod Wulff]

Good day, Alexey.

I thank you for your post as it sent me on a bit of a learning endeavor.

I have found that there is a framework to support user-developed stemmers
already, with one built-in stemmer (Porter) coded into the DBMS by default.

SQLite v3.7.4 facilitates the use FTS3 or FTS4.  The Porter (stemmer)
tokenizer does work well with FTS4 on v3.7.4 as described in the docs.
Relevant info is detailed here: http://www.sqlite.org/fts3.html#tokenizer.
The details on user implemented tokenizers is detailed here:
http://www.sqlite.org/fts3.html#section_5_1.

I was confused by one element in the docs, and will help to clarify it here,
in case someone else struggles with it in the future.  My existing code
contained column names as parameters to the FTS creation constructs.

[code] ...
DBMS_Toss_Error( "Rebuilding FTS Tables as they differ from Production
Table...", 0, 0, 0, 0)
SQLite_Query := "Drop Table API_FTS_DB;`n"
SQLite_Query .= "CREATE VIRTUAL TABLE API_FTS_DB USING FTS4 (Target_Name,
Target_Context, Target_Description, Target_Content, Target_Link,
Parent_Short, Parent_Name, Parent_Link, API_Version);`n"
SQLite_Query .= "INSERT INTO API_FTS_DB SELECT * FROM API_DB;`n"
SQLite_Query .= "SELECT count( * ) AS total_records FROM API_FTS_DB;`n"
... [/code]

In the docs pointed to above, to implement a tokenizer, one's construct must
resemble this:

[code] ...
CREATE VIRTUAL TABLE porter USING fts3(tokenize=porter);
... [/code]


What I ended up getting working was this:

[code]...
SQLite_Query .= "CREATE VIRTUAL TABLE API_FTS_DB USING FTS4 (Target_Name,
Target_Context, Target_Description, Target_Content, Target_Link,
Parent_Short, Parent_Name, Parent_Link, API_Version, tokenize=porter);`n"
...[/code]

In other words, the use of the tokenize parameter, along with column names
parameters, is supported.

Thanks, again, Alexey.  Take care.

-t

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Alexey Pechnikov
Sent: Wednesday, January 19, 2011 09:13 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Patch: FTS3 Snawball Stemmer support

Hello!

May be this will be useful for somebody too. The code is not well tested yet
but demonstrate the solution.

http://sqlite.mobigroup.ru/wiki?name=FTS3+Snowball+Stemmer

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
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] How can we use POWER(2,32) like function in SQLite

[Max Vlasov]

On Thu, Jan 20, 2011 at 8:23 AM, Sunil Bhardwaj <
sbhard...@ipolicynetworks.com> wrote:

> Hi
>
> Please suggest, How can we use POWER(2,32) like function in SQLite?
>
>

This particular call or any with base 2 can be replaced with bitwise shift

SELECT 1 << 32

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

[sqlite] Documentation typos - part no.3

[Marian Cascaval]

Hi!

Again, some more Documentation typos
with some fun while we're at it:

1.
http://www.sqlite.org/c3ref/intro.html
"These pages _defined_ the C-language interface to SQLite."
Good old days!

2.
http://www.sqlite.org/quickstart.html
"... sqlite3_exec() on line _27_ ..."
Most C++ editors insist on _28_.

3.
http://www.sqlite.org/quickstart.html
"... and sqlite3_close() on line _31_ ..."
Not to mention _33_.

4.
http://www.sqlite.org/cintro.html
"The "sqlite3_column()" shown in the list is _place holders_  for an entire 
_families_ of ..."
Further down the page the truth is revealed: "... is really _a 
place-holder_ 
for an entire _family_ of ..."

5.
http://www.sqlite.org/cintro.html
"However, the SQLite allows the same prepared statement _to_ evaluated multiple 
times."
To be, or not _to be_: that is the .. query.

6.
http://www.sqlite.org/cintro.html
"_These_ is accomplished using the following routines:"
Know what? Is hard to make fun of every typo I find. I'm not that 
resourceful. Couldn't find any jokes for _This_ one.

7.
http://www.sqlite.org/cintro.html
"SQLite includes _interface_ that can be used to extend its functionality. Such 
routines include:"
The textual construction _faces_ a  lack of plural.

8.
http://www.sqlite.org/cintro.html
"The sqlite3_create_collation() interface is used to create ... The 
sqlite3_create_module() interface is used __ register ..."
It used _to_ be correct in the first sentence but not anymore in the second.


.. and the last one, which I hope is taken as a joke and nothing more (by the 
way, thank you again Mr.Hipp for making easy, even for non-programmers, to use 
SQL statements from within C++ code):


9.
http://www.sqlite.org/cintro.html
"A complete list of functions that form the SQLite application _program_ 
interface ..."
Who wrote this definitely doesn't like _programming_ ;)




Marian Cascaval



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

Re: [sqlite] How can we use POWER(2,32) like function in SQLite

[Simon Davies]

On 20 January 2011 05:23, Sunil Bhardwaj  wrote:
> Hi
>
> Please suggest, How can we use POWER(2,32) like function in SQLite?

Vanilla SQL has no power function, but see
extension-functions.c in http://sqlite.org/contrib.

>
> Thanks
> Sunil Bhardwaj
> Ext. 1125 (0120-2567001)
> 9818868910

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