[sqlite] [ANN] SQLiteAdmin Released

[Peter Haworth]

I'm pleased to announce the general availability of SQLiteAdmin, an SQLite
database administration tool for Mac and Windows.

A 30-day demo of the application is available for download
here. To
mark the launch of SQLiteAdmin, I am making it available for $9.99 to any
members of this list through May 16th.  To take advantage of this offer,
please send email to p...@lcsql.com  and you will
receive the purchase link by reply.  Please make sure your email doesn't
block messages from that address.

SQLiteAdmin provides complete GUI access to all SQLite Data Definition
Language functions.  Convenience features include the ability to create
multiple columns sharing the same properties in a table with a single mouse
click, or to automatically create an index on the child column of a foreign
key relationship.

Extended Data Definition Functions provide the ability to add, delete, and
modify any SQLite object and its properties.  You can add, rename, and
delete columns from existing tables and the changes will be reflected in
all other schema objects such as indexes, triggers, and views.  You can
also add, modify or remove any table or column constraint.  Extensive
checking is done before any of these changes are made and all changes are
done within the scope of a transaction which is rolled back if any errors
occur.  For complete security, you can request that SQLiteAdmin backs up
your database before any Extended Data Definition Functions are executed

A table browser tool is provided for data inquiry and maintenance purposes.
 Powerful filtering capabilities are available including the ability to
choose which columns to display.  You can define "virtual columns"  to hold
the results of any SQLite expression.  Filter settings can be saved for
future recall and execution.  You can insert, duplicate, delete, and update
(single or multiple) rows using SQLiteAdmin's fill-in-the-blanks approach.

All or selected table rows can be exported to files with columns delimited
by comma, tab, or any other character you choose, or as SQLite INSERT
statements.  External files in delimited format can be processed to insert
data into a table and SQLiteAdmin handles embedded delimiter and carriage
return characters in the column data without requiring them to be escaped.
 You can also process files containing INSERT or any other valid SQLite
statements.

Other tools include support for in-memory databases (including the ability
to save them to disk), access and updating of all PRAGMA values from a
list, access to tune up commands such as VACUUM, ANALYZE, and REINDEX, and
an audit function that will suggest changes to your schema that may provide
improved performance.  In addition, you can manually enter and execute
SQLite commands, including access to the sqlite3 "dot" commands, and single
commands or sequences of commands can be saved for later recall and
execution.

I welcome any feedback, positive or negative, which might help me improve
the product.

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Udi Karni]

Hi Michael,

May I make a request on behalf of the C++ challenged crowd?

Is it possible to fuse your contribution with the existing capabilities of
the ".import" command so that it can be activated via syntax similar to -
"RECORDS DELIMITED BY... FIELDS TERMINATED BY... OPTIONALLY ENCLOSED BY..."

It would make ".import" so much more powerful.

Thanks !

On Sun, May 6, 2012 at 5:59 AM, Black, Michael (IS)
wrote:

> I modified my csvimport to allow for quoted fields.  Still automaticallhy
> detects the # of columns.
> If quoted fields also contain internal quotes they must be escaped by
> doubling them.
> e.g.
> col1,"this is, a ""quoted"" field",col3
> It's not fully RFC4180 compliant as it doesn't allow for CF/LF inside
> quotes -- does anybody really need/use that?
>
> #include 
> #include 
> #include 
> #include "sqlite3.h"
> #define BUFSIZE 100
> #define MOD 10
> #define MAXTOK 10
> char *nextToken(char *line) {
>  static char token[MAXTOK];
>  static char *p;
>  int n=0;
>  if (line) {
>p = line;
>  }
>  while(*p && *p != ',' && *p!='\r' && *p!= '\n') {
>if (*p == '"') { // quoted field
>  ++p; // move past first quote
>  while((*p && *p!='"') || *(p+1)=='"') {
>if (*p == '"' && *(p+1)=='"') { // escaped quote?
>  token[n++]='"';
>  p+=2;
>  continue;
>}
>token[n++] = *p;
>++p;
>  }
>  token[n++]=0;
>  ++p;
>  if (*p == ',') ++p;
>  return token;
>}
>token[n++]=*p;
>++p;
>  }
>  if (n > MAXTOK) {
>fprintf(stderr,"Token too big??\n");
>exit(1);
>  }
>  token[n]=0;
>  ++p;
>  if (*p == ',') ++p;
>  if (n > 0) {
>return token;
>  }
>  return NULL;
> }
> // Add comma delimited file to exisiting database/table
> // Quoted strings are accepted
> int main(int argc, char *argv[]) {
>  sqlite3 *db;
>  sqlite3_stmt *stmt;
>  int rc;
>  int ncol=0;
>  int nline=0;
>  char *buf=malloc(BUFSIZE);
>  char sql[8192];
>  FILE *fp;
>  char *filename;
>  char *databasename;
>  char *tablename;
>  if (argc != 4) {
>fprintf(stderr,"Usage: %s filename databasename tablename\n",argv[0]);
>exit(1);
>  }
>  filename = argv[1];
>  databasename = argv[2];
>  tablename = argv[3];
>  rc = sqlite3_open_v2(databasename,&db,SQLITE_OPEN_READWRITE,NULL);
>  if (rc) {
>fprintf(stderr,"Error opening database '%s':
> %s\n",databasename,sqlite3_errmsg(db));
>exit(1);
>  }
>  sprintf(sql,"insert into %s values (",tablename);
>  fp=fopen(filename,"r");
>  if (fp == NULL) {
>perror(filename);
>exit(1);
>  }
>  buf[BUFSIZE-1] = '*';
>  fgets(buf,BUFSIZE,fp);
>  if (buf[BUFSIZE-1] != '*') {
>fprintf(stderr,"BUFSIZE not big enough...aborting\n");
>exit(1);
>  }
>  // count the columns
>  char *p=nextToken(buf);
>  ncol=0;
>  while(p) {
>++ncol;
>strcat(sql,ncol==1?"":",");
>strcat(sql,"?");
>p=nextToken(NULL);
>  }
>  printf("%d columns detected\n",ncol);
>  strcat(sql,")");
>  rewind(fp);
>  // Let's wrap things in a transaction
>  rc = sqlite3_exec(db,"BEGIN",NULL,NULL,NULL);
>  if (rc) {
>fprintf(stderr,"BEGIN failed on '%s': %s\n",sql,sqlite3_errmsg(db));
>exit(1);
>  }
>  // prepare our statement
>  rc = sqlite3_prepare(db,sql,strlen(sql),&stmt,NULL);
>  if (rc) {
>fprintf(stderr,"Prepare failed on '%s': %s\n",sql,sqlite3_errmsg(db));
>exit(1);
>  }
>  // Loop over file file
>  while(fgets(buf,BUFSIZE,fp)) {
>char *p=nextToken(buf);
>int i=1;
>++nline;
>if ((nline % MOD)==0) {
>  printf("%d\r",nline);
>  fflush(stdout);
>}
>while(p) { // bind the columns as text, table will take care of
> conversion to column types
>  rc=sqlite3_bind_text(stmt,i,p,-1,SQLITE_TRANSIENT);
>  if (rc) {
>fprintf(stderr,"bind_text failed on '%s':
> %s\n",sql,sqlite3_errmsg(db));
>exit(1);
>  }
>  ++i;
>  p=nextToken(NULL);
>}
>if (--i != ncol) {
>  fprintf(stderr,"expected %d cols, got %d cols on
> line#%d\n",ncol,i,nline);
>} else {
>  rc = sqlite3_step(stmt);
>  if (rc != SQLITE_DONE) {
>fprintf(stderr,"Insert failed on '%s':
> %s\n",sql,sqlite3_errmsg(db));
>exit(1);
>  }
>  rc = sqlite3_reset(stmt);
>  if (rc) {
>fprintf(stderr,"Reset failed on '%s': %s\n",sql,sqlite3_errmsg(db));
>exit(1);
>  }
>}
>  }
>  rc=sqlite3_finalize(stmt);
>  if (rc) {
>fprintf(stderr,"Finalize failed: %s\n",sqlite3_errmsg(db));
>exit(1);
>  }
>  printf("%d inserts, committing...\n",nline);
>  rc = sqlite3_exec(db,"COMMIT",NULL,NULL,NULL);
>  if (rc) {
>fprintf(stderr,"COMMIT failed on '%s': %s\n",sql,sqlite3_errmsg(db));
>exit(1);
>  }
>  rc=sqlite3_close(db);
>  if (rc) {
>fprintf(stderr,"Close failed on '%s': %s\n",argv[2],sqlite3_errmsg(db));
>exit(1);
>  }
>  fclose(fp);
>  return 0;
> }
>
>
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grum

Re: [sqlite] inner join problem

[Keith Medcalf]

> update dzhhq set mnote=
> case when  mnote in (select mnote from dzhhq inner join (select bs,stkcode
> from buysell) b on dzhhq.stkcode=b.stkcode) then 'sell'||mnote
> else mnote
> end
 
> is ok, but when I use this cmd then appear error msg:
 
> update dzhhq set mnote=
> case when mnote in (select mnote from dzhhq inner join (select bs,stkcode
> from buysell) b on dzhhq.stkcode=b.stkcode and (dzhhq.qph60 and (buysell.bs="sell")) then 'sell'||mnote
> else mnote
> end

Buysell.qph60, buysell.bs, and "sell" are not visible columns.  You don't 
include the actual error message. " and ' quote different things.  ' quotes 
string values and " quotes names (database/table/column/index &c).

update dzhhq set mnote=
case when mnote in (select mnote from dzhhq, buysell where 
dzhhq.stkcode=buysell.stkcode and dzhhq.qph60http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] inner join problem

[YAN HONG YE]

update dzhhq set mnote=
case when  mnote in (select mnote from dzhhq inner join (select bs,stkcode from 
buysell) b on dzhhq.stkcode=b.stkcode) then 'sell'||mnote
else mnote
end

is ok, but when I use this cmd then appear error msg:

update dzhhq set mnote=
case when mnote in (select mnote from dzhhq inner join (select bs,stkcode from 
buysell) b on dzhhq.stkcode=b.stkcode and (dzhhq.qph60http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Details on New Features

[Gabor Grothendieck]

On Sun, May 6, 2012 at 8:00 PM, Donald Griggs  wrote:
> Regarding:   What precisely are the
> "improvements" in handling of CSV inputs?
>
>
> Gabor, I don't know about "precisely" -- I'll let others on the list tell
> me where I'm off, but here's my take:
>
>
> A lot of strange things call themselves csv, but the change attempts to
> make the sqlite3 utility's CSV inputs perform a bit more closely to RFC4180.
>     http://tools.ietf.org/html/rfc4180
>
> http://en.wikipedia.org/wiki/Comma-separated_values#Toward_standardization
>
> In particular, during CSV mode import:
>  -- Allow any field to be surrounded by double quote characters without
> those characters being considered part of the field data.
>  -- Allow fields to contain embedded commas (or other separators) when the
> field is surrounded by double quote characters.
>  -- Allow fields to span multiple lines if they are surrounded by double
> quote characters.
>  -- Allow the double quote character to be escaped by having two adjacent
> double quote characters. (But note that a field consisting solely of two
> double quote characters still represents an empty string field.)
>
>  -- On output in CSV mode, surround text fields with double quotes when
> needed.
>
>
> See check-in [93aa17d866]   http://www.sqlite.org/src/info/93aa17d866
>

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

Re: [sqlite] Details on New Features

[Donald Griggs]

Regarding:   What precisely are the
"improvements" in handling of CSV inputs?


Gabor, I don't know about "precisely" -- I'll let others on the list tell
me where I'm off, but here's my take:


A lot of strange things call themselves csv, but the change attempts to
make the sqlite3 utility's CSV inputs perform a bit more closely to RFC4180.
 http://tools.ietf.org/html/rfc4180

http://en.wikipedia.org/wiki/Comma-separated_values#Toward_standardization

In particular, during CSV mode import:
  -- Allow any field to be surrounded by double quote characters without
those characters being considered part of the field data.
  -- Allow fields to contain embedded commas (or other separators) when the
field is surrounded by double quote characters.
  -- Allow fields to span multiple lines if they are surrounded by double
quote characters.
  -- Allow the double quote character to be escaped by having two adjacent
double quote characters. (But note that a field consisting solely of two
double quote characters still represents an empty string field.)

  -- On output in CSV mode, surround text fields with double quotes when
needed.


See check-in [93aa17d866]   http://www.sqlite.org/src/info/93aa17d866

(By the way, I believe the sqlite3 command line utility (CLI) was intended
to be more of a debug tool than a production component -- but it surely is
useful!)

For an example of CSV import, if I have file MyStuff.csv whose data is
shown below between the barred lines below (words in square brackets [] are
just my comments and were not present in the import file):
==
1,cat
2,"rat"[quotes are optional unless separator(s)
embedded]
3 ,"grey fox"  [extra whitespace will be handled differently
when affinity is numeric]
4, spacedog[There's a space before and after spacedog --
trust me]
5,o'possum
6,"big, bad, wolf"
7,"two-lined   [Fields can span lines]
zebra"
8, [Second field empty. (Maybe I forgot to type
"Missing lynx")]
9,imperial ("laughing") loon
==
 Now I create a test database.

C:\util>sqlite3 test.db

SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> /* Define a simple table t, comprised of an integer column and a
text column */
sqlite> Create table t ( id integer, animal);

sqlite> /*  import the data above using csv mode */
sqlite> .mode csv
sqlite> .import MyStuff.csv   t


sqlite> /* Show the table in CSV mode
sqlite> select * from t;
1,cat
2,rat
3,"grey fox"
4," spacedog "
5,"o'possum"
6,"big, bad, wolf"
7,"two-lined
zebra"
8,""
9,"imperial (""laughing"") loon"
sqlite>
sqlite>
sqlite>
sqlite> /* Try changing the separator and show it again in LIST mode */
sqlite> .separator |
sqlite> .mode list
sqlite> select * from t;
1|cat
2|rat
3|grey fox
4| spacedog
5|o'possum
6|big, bad, wolf
7|two-lined
zebra
8|
9|imperial ("laughing") loon
sqlite>

Does this answer your questions?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ubuntu 11.10 and Sqlite3

[Kees Nuyt]

On Sun, 6 May 2012 19:01:02 +0200 (CEST), "pl...@libero.it"
 wrote:

>Hi at all, 
>i have installed sqlite3 on Ubuntu 11.10 but I get error mismatch when i 
>execute sqlite3 by terminal session
>
>SQLite header and source version mismatch
>2009-09-11 14:05:07 b084828a771ec40be85f07c590ca99de4f6c24ee
>2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
> 
>On internet i find this : 
> 
>ldconfig
> 
>but i get always this error : SQLite header and source version mismatch


It's often better to link the command line tool statically, so you are
completely independent of the system's sqlite3 version.

The shell commands below work for me on 
SunOS 5.11 snv_130 i86pc i386 i86pc (AKA Solaris eXpress Community
Edition [1], but I'm sure there are simpler ways:

set -xv

cd ${HOME}/src/sqlite3 \
&& fossil pull \
&& fossil update trunk \
&& cd

cd ${HOME}/bld/sqlite3 \
&& rm -rf ${HOME}/bld/sqlite3/* \
&& ../../src/sqlite3/configure \
--prefix=${HOME}/usr \
--enable-load-extension \
--enable-threadsafe \
--disable-tcl \
--enable-readline \
--with-readline-lib="-L/usr/lib -R/usr/lib -lreadline -ltermcap" \
--with-readline-inc=-I/usr/include/readline \
&& make sqlite3.c \
&& cp ../../src/sqlite3/src/shell.c ./ \
&& gcc -Os \
-DSQLITE_ENABLE_FTS3=1 \
-DSQLITE_ENABLE_RTREE=1 \
-DSQLITE_ENABLE_STAT2 \
-DSQLITE_ENABLE_STAT3 \
-DHAVE_READLINE=1 \
-I./ -I/usr/include/readline \
-L/usr/lib -lreadline -ltermcap \
-R/usr/lib \
-o sqlite3 sqlite3.c shell.c \
&& chmod 755 sqlite3 \
&& cp -p sqlite3 ${HOME}/usr/bin \
&& strip ${HOME}/usr/bin/sqlite3 \
&& mv -f sqlite3 ${HOME}/usr/bin/sqlite$( gawk \
'/#define SQLITE_(VERSION_NUMBER|SOURCE_ID) /{
buf=buf $3
}
END{
sub(/"/,"_",buf)
print buf
}
' sqlite3.h ) \
&& cd
set +xv

Notes:
1)  make sqlite3.c
builds the amalgamation source from a complete sqlite3 source tree.

2) The line with the gawk command gives the binary a version dependent
name, which makes it easy to feed SQL scripts to different versions of
sqlite3, and compare the results.

3) fossil is available from http://www.fossil-scm.org/download.html 


The statically linked sqlite3 executable is about 587 kByte, 521 kByte
if stripped, and contains objects from shell.c and sqlite3.c (=the
amalgamation). The system libraries are dynamically linked.

[1] Yeah, I really should move to OpenIndiana 151a3 by now, I'm
patiently waiting for a stable release...

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Ubuntu 11.10 and Sqlite3

[Kevin Benson]

http://comments.gmane.org/gmane.comp.db.sqlite.general/71215

--
   --
  --
 --Ô¿Ô--
K e V i N


On Sun, May 6, 2012 at 1:01 PM, pl...@libero.it  wrote:

> Hi at all,
> i have installed sqlite3 on Ubuntu 11.10 but I get error mismatch when i
> execute sqlite3 by terminal session
>
> SQLite header and source version mismatch
> 2009-09-11 14:05:07 b084828a771ec40be85f07c590ca99de4f6c24ee
> 2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
>
> On internet i find this :
>
> ldconfig
>
> but i get always this error : SQLite header and source version mismatch
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking/Concurrency

[Igor Tandetnik]

KUSHAL SHAH  wrote:
> My scenario: Multiple processes want to access the database. How do I 
> configure so that I can get as much concurrency w.r.t read
> and write. Meaning, have multiple readers and writers. 

The best concurrency between separate processes (and thus, necessarily, 
separate connections) you can achieve with SQLite is by enabling WAL mode. 
Which, as I said, gives you multiple readers and a single writer at the same 
time. Under no circumstances would SQLite allow two simultaneous writing 
transactions.

> That is fine, I can handle it in my application. However, I just want to 
> confirm, how to achieve the multi-read and 1 write
> scenario. My understanding: 
> Keep threading mode to Serialized.

If you don't have multiple threads sharing a single connection (and it sounds 
like you don't), then threading mode is irrelevant.

> Keep the locking mode to Normal. Again, this is the default. So I don't have 
> to do anything.
> Finally, the journal mode to WAL.
> 
> Is this correct?

Yes, sounds good to me.
-- 
Igor Tandetnik

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

Re: [sqlite] Locking/Concurrency

[KUSHAL SHAH]

Sorry, when I said mutex mode, I meant the Runtime selection of threading mode: 
http://sqlite.org/threadsafe.html
 
At this point, I am sorry, I am confused with threading mode, journal mode and 
locking modes.
 
My scenario: Multiple processes want to access the database. How do I configure 
so that I can get as much concurrency w.r.t read and write. Meaning, have 
multiple readers and writers.
 
I think we are saying we can only have multiple readers and 1 writer at any 
point in time. Any connection that tries to write when there is already a write 
happening, will get SQLite_Busy.
 
That is fine, I can handle it in my application. However, I just want to 
confirm, how to achieve the multi-read and 1 write scenario. My understanding:
Keep threading mode to Serialized. This is the default. So I don't have to do 
anything.
Keep the locking mode to Normal. Again, this is the default. So I don't have to 
do anything.
Finally, the journal mode to WAL.
 
Is this correct?
 
Also, in WAL mode, the simple answer for why there cant be multiple writes is 
because you cannot have multiple pages being written at the same time in the 
same file. It also complicates transaction behavior, crash recovery, 
etc. Correct?
 
Thanks,
Kushal.

  


 From: Igor Tandetnik 
To: sqlite-users@sqlite.org 
Sent: Saturday, May 5, 2012 6:02 PM
Subject: Re: [sqlite] Locking/Concurrency
  
KUSHAL SHAH  wrote:
> I am starting simple where I have exe1 having 1 connection and exe2 having 
> another connection.
> Now if both of them open the connections in mutex mode, will the read/write 
> requests be serialized. It seems you are saying No.

I'm not sure what you mean by "mutex mode" - I'm not familiar with this term.

If you have multiple connections, each used by a single thread (whether in the 
same or different EXEs, doesn't matter), then:

- in "traditional" roll-back journal mode, at any point in time the database 
may be accessed by multiple readers, *or* by a single writer
- in WAL mode, the database may be accessed by multiple readers *and* a single 
writer.

> If so, then what is the advantage of mutex? Like, I would expect that a 
> single thread connection will anyways serialize its
> requests from the client. What am I missing? 

I don't understand the term "thread connection" either.

What scenario do you have in mind, exactly? How many threads are using how many 
connections? Which of these threads attempt to read, and which attempt to write?

> Finally, how do I enable WAL mode

The documentation at http://sqlite.org/wal.html explains this in detail.

> Specifically, can I do that in System.Data.SqLite client?

I'm unfamiliar with this client. Hopefully, someone more knowledgeable will 
chime in soon.

> Also, what happens when 2 connections(with mutex) try to write? Will one of 
> them get sqlite_busy?

Yes.
-- 
Igor Tandetnik

___
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] ordering doesn't work the way I expected

[Petite Abeille]

On May 6, 2012, at 8:34 PM, Simon Slavin wrote:

> I'm not sure about a requirement for consistency.  NULL isn't a value.  It 
> means something like 'value unknown'.  Anything attempting to sort columns 
> with nulls in can sort any of the nulls into any position.

Well, as pointed out by Yuriy and Pavel, it's a bug due to SQLite trying to 
take a shortcut by assuming unique keys are already properly sorted, but not 
accounting for the presence of nulls in such index:

http://www.sqlite.org/src/info/2a5629202f

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

Re: [sqlite] how to update the select commad

[Igor Tandetnik]

叶艳红  wrote:
> update dzhhq set mnote=
> case when  (select dzhhq.*,b.bs from dzhhq inner join (select bs,stkcode
> from buysell) b on dzhhq.stkcode=b.stkcode) then 'sell'||mnote
> else mnote
> end

I'm not quite sure I understand what you are trying to achieve. Perhaps 
something like this:

update dzhhq set mnote= 'sell' || mnote
where stkcode in (select stkcode from buysell);

-- 
Igor Tandetnik

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

Re: [sqlite] ordering doesn't work the way I expected

[Luuk]

On 06-05-2012 20:28, Petite Abeille wrote:
> 
> On May 6, 2012, at 8:18 PM, Luuk wrote:
> 
>> So, if it is not defined how to sort column b,
>> how can anyone correctly sort the column a?
> 
> While the standard itself might well have nothing helpful to say about how a 
> null value should be sorted by default ( NULLS FIRST or NULLS LAST), a given 
> implementation (e.g. SQLite) will have an opinion about it.  That opinion 
> should be consistent. And it appears it's not. Which would qualify as an 
> anomaly.
> 

The NULLS FIRST or NULLS LAST is about if the NULLs should be before, or
after the 'other' data.

It does not say anything about how to sort the fields which all have the
'value' NULL

sqlite> .null (NULL)
sqlite> select NULL or NULL;
(NULL)
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordering doesn't work the way I expected

[Simon Slavin]

On 6 May 2012, at 7:28pm, Petite Abeille  wrote:

> On May 6, 2012, at 8:18 PM, Luuk wrote:
> 
>> So, if it is not defined how to sort column b,
>> how can anyone correctly sort the column a?
> 
> While the standard itself might well have nothing helpful to say about how a 
> null value should be sorted by default ( NULLS FIRST or NULLS LAST), a given 
> implementation (e.g. SQLite) will have an opinion about it.  That opinion 
> should be consistent. And it appears it's not. Which would qualify as an 
> anomaly.

I'm not sure about a requirement for consistency.  NULL isn't a value.  It 
means something like 'value unknown'.  Anything attempting to sort columns with 
nulls in can sort any of the nulls into any position.

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

[sqlite] how to update the select commad

[叶艳红]

update dzhhq set mnote=
case when  (select dzhhq.*,b.bs from dzhhq inner join (select bs,stkcode
from buysell) b on dzhhq.stkcode=b.stkcode) then 'sell'||mnote
else mnote
end
this command:
(select dzhhq.*,b.bs from dzhhq inner join (select bs,stkcode from buysell)
b on dzhhq.stkcode=b.stkcode);
Could searched the result 
But how to update

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

Re: [sqlite] ordering doesn't work the way I expected

[Petite Abeille]

On May 6, 2012, at 8:18 PM, Luuk wrote:

> So, if it is not defined how to sort column b,
> how can anyone correctly sort the column a?

While the standard itself might well have nothing helpful to say about how a 
null value should be sorted by default ( NULLS FIRST or NULLS LAST), a given 
implementation (e.g. SQLite) will have an opinion about it.  That opinion 
should be consistent. And it appears it's not. Which would qualify as an 
anomaly.

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

Re: [sqlite] ordering doesn't work the way I expected

[Pavel Ivanov]

This is already fixed in the SQLite's trunk code (see
http://www.sqlite.org/src/info/2a5629202f) and thus will be fixed in
next SQLite release, i.e. 3.7.12.

Pavel

On Sun, May 6, 2012 at 1:34 PM, William Parsons  wrote:
> In my application, I've encountered a problem with ordering where the result
> doesn't match what I would have expected, and would like some clarification.
> The issue is illustrated by the following:
>
> % sqlite3 :memory:
> SQLite version 3.7.10 2012-01-16 13:28:40
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table x(a int, b int unique);
> sqlite> insert into x(a) values (1);
> sqlite> insert into x(a) values (4);
> sqlite> insert into x(a) values (3);
> sqlite> insert into x(a) values (5);
> sqlite> insert into x(b) values (6);
> sqlite> insert into x(b) values (8);
> sqlite> insert into x(b) values (7);
> sqlite> insert into x(a) values (2);
> sqlite> select * from x;
> 1|
> 4|
> 3|
> 5|
> |6
> |8
> |7
> 2|
> sqlite> select * from x order by b, a;
> 1|
> 4|
> 3|
> 5|
> 2|
> |6
> |7
> |8
> sqlite> .e
>
> Why aren't the numbers sorted as I would have expected?  Note that if I remove
> the unique constraint from column b it works as expected.
>
> --
> William Parsons
> ___
> 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] ordering doesn't work the way I expected

[Yuriy Kaminskiy]

William Parsons wrote:
> In my application, I've encountered a problem with ordering where the result 
> doesn't match what I would have expected, and would like some clarification.
> The issue is illustrated by the following:
> 
> % sqlite3 :memory:
> SQLite version 3.7.10 2012-01-16 13:28:40
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table x(a int, b int unique);
> sqlite> insert into x(a) values (1);
> sqlite> insert into x(a) values (4);
> sqlite> insert into x(a) values (3);
> sqlite> insert into x(a) values (5);
> sqlite> insert into x(b) values (6);
> sqlite> insert into x(b) values (8);
> sqlite> insert into x(b) values (7);
> sqlite> insert into x(a) values (2);
> sqlite> select * from x;
> 1|
> 4|
> 3|
> 5|
> |6
> |8
> |7
> 2|
> sqlite> select * from x order by b, a;
> 1|
> 4|
> 3|
> 5|
> 2|
> |6
> |7
> |8
> sqlite> .e
> 
> Why aren't the numbers sorted as I would have expected?  Note that if I 
> remove 
> the unique constraint from column b it works as expected.

Looks similar to this issue/bug: http://www.sqlite.org/src/info/2a5629202f
... and I think it was fixed in trunk [aka what-will-be-3.7.12]:
http://www.sqlite.org/src/info/9870e4c4fe?sbs=0

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

Re: [sqlite] ordering doesn't work the way I expected

[Petite Abeille]

On May 6, 2012, at 7:34 PM, William Parsons wrote:

> Why aren't the numbers sorted as I would have expected?

Hmmm, yes, sounds like a, hmmm, functional anomaly :(

>  Note that if I remove 
> the unique constraint from column b it works as expected.

Hmmm, yes, sounds like a, hmmm, functional anomaly :(

P.S.

The wisdom of having a null unique key is debatable...

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

Re: [sqlite] ordering doesn't work the way I expected

[Luuk]

On 06-05-2012 19:34, William Parsons wrote:
> In my application, I've encountered a problem with ordering where the result 
> doesn't match what I would have expected, and would like some clarification.
> The issue is illustrated by the following:
> 
> % sqlite3 :memory:
> SQLite version 3.7.10 2012-01-16 13:28:40
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table x(a int, b int unique);
> sqlite> insert into x(a) values (1);
> sqlite> insert into x(a) values (4);
> sqlite> insert into x(a) values (3);
> sqlite> insert into x(a) values (5);
> sqlite> insert into x(b) values (6);
> sqlite> insert into x(b) values (8);
> sqlite> insert into x(b) values (7);
> sqlite> insert into x(a) values (2);
> sqlite> select * from x;
> 1|
> 4|
> 3|
> 5|
> |6
> |8
> |7
> 2|
> sqlite> select * from x order by b, a;
> 1|
> 4|
> 3|
> 5|
> 2|
> |6
> |7
> |8
> sqlite> .e
> 
> Why aren't the numbers sorted as I would have expected?  Note that if I 
> remove 
> the unique constraint from column b it works as expected.
> 

quote from: http://en.wikipedia.org/wiki/Order_by
The SQL standard's core functionality does not explicitly define a
default sort order for Nulls.


So, if it is not defined how to sort column b,
how can anyone correctly sort the column a?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ordering doesn't work the way I expected

[William Parsons]

In my application, I've encountered a problem with ordering where the result 
doesn't match what I would have expected, and would like some clarification.
The issue is illustrated by the following:

% sqlite3 :memory:
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table x(a int, b int unique);
sqlite> insert into x(a) values (1);
sqlite> insert into x(a) values (4);
sqlite> insert into x(a) values (3);
sqlite> insert into x(a) values (5);
sqlite> insert into x(b) values (6);
sqlite> insert into x(b) values (8);
sqlite> insert into x(b) values (7);
sqlite> insert into x(a) values (2);
sqlite> select * from x;
1|
4|
3|
5|
|6
|8
|7
2|
sqlite> select * from x order by b, a;
1|
4|
3|
5|
2|
|6
|7
|8
sqlite> .e

Why aren't the numbers sorted as I would have expected?  Note that if I remove 
the unique constraint from column b it works as expected.

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

[sqlite] Ubuntu 11.10 and Sqlite3

[pl...@libero.it]

Hi at all, 
i have installed sqlite3 on Ubuntu 11.10 but I get error mismatch when i 
execute sqlite3 by terminal session

SQLite header and source version mismatch
2009-09-11 14:05:07 b084828a771ec40be85f07c590ca99de4f6c24ee
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
 
On internet i find this : 
 
ldconfig
 
but i get always this error : SQLite header and source version mismatch
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Jean-Christophe Deschamps]

Hi Simon,

My test cases weren't intended to supply data, they're crash tests.  I 
have faith that Mike's software correctly interprets syntactically 
correct cases.  I was interested in making sure it didn't crash, hang 
or spew on syntactic errors.


You're right.

JcD 


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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Simon Slavin]

On 6 May 2012, at 3:20pm, Jean-Christophe Deschamps  wrote:

> Also fields starting with a " delimiter should comply with the rules.

My test cases weren't intended to supply data, they're crash tests.  I have 
faith that Mike's software correctly interprets syntactically correct cases.  I 
was interested in making sure it didn't crash, hang or spew on syntactic errors.

('spew' is to loop around generating endless output.  Not a crash nor a hang, a 
third common failure mode.  And one that fills up disk until the OS terminates 
the app.)

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Jean-Christophe Deschamps]

Not sure I buy your test cases.


I don't either, but for diverging reasons.

Fields containg double quotes must be in double quotes.  So only one 
of your examples is valid


first,""second"",third

I'll have to fix that one.


No, it's invalid.  Apply to " the same rules that apply to ' for 
literals in SQL statements: if user data consists of 'ABC' (including 
the quotes), then the fragment SQL is

WHERE column = '''ABC''' (i.e. you still need the outer quotes).

So the above should be:
first,"""second""",third

BUT, fields that don't start with a " delimiter shouldn't be considered 
delimited.


first,second"",third

has 3 data fields for me:
first
second""
third

Also fields starting with a " delimiter should comply with the rules.

first,""second,third
^-- missing comma separator

I could add the CR/LF...but I've never seen that used in a CSV 
format.  Then again, may as well use the standard.  That's the nice 
thing about standards...there are so many to choose from :-)


I've had to process many CSVs with either 0x00, CR, LF or CRLF 
embedded. Some TSV (Tab separated values), some with ; as separators, 
even some with multiline comments...


JcD 


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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Simon Slavin]

On 6 May 2012, at 3:03pm, "Black, Michael (IS)"  wrote:

> Not sure I buy your test cases.
> 
> Fields containg double quotes must be in double quotes.  So only one of your 
> examples is valid
> 
> first,""second"",third
> 
> I'll have to fix that one.

Sure.  The rest aren't interpretation tests.  They're crash tests: your app 
shouldn't crash, hang, or spew on them.  It doesn't have to turn them into 
something they're not.

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Black, Michael (IS)]

Not sure I buy your test cases.

Fields containg double quotes must be in double quotes.  So only one of your 
examples is valid

first,""second"",third

I'll have to fix that one.



I could add the CR/LF...but I've never seen that used in a CSV format.  Then 
again, may as well use the standard.  That's the nice thing about 
standards...there are so many to choose from :-)





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Sunday, May 06, 2012 8:35 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] is SQLite the right tool to analyze a 44GB file


On 6 May 2012, at 1:59pm, "Black, Michael (IS)"  wrote:

> I modified my csvimport to allow for quoted fields.  Still automaticallhy 
> detects the # of columns.
> If quoted fields also contain internal quotes they must be escaped by 
> doubling them.
> e.g.
> col1,"this is, a ""quoted"" field",col3

I trust you as a competent programmer, but I have test cases for you:

first",second,"third
first,""second,third
first"",second,third
first,""second"",third
first,""second,""third
first"",second,""third
first"",second"",third

producing error messages (or how many columns are filled) is up to you.

> It's not fully RFC4180 compliant as it doesn't allow for CF/LF inside quotes 
> -- does anybody really need/use that?

Yes, in that I have multi-line address fields and multi-paragraph notes fields. 
 But we don't store that data in CSV files, so it wouldn't matter that a CSV 
import tool couldn't handle it.  Actually that system uses MySQL not SQLite.  
Might be interesting to see if the SQLite shell tool failed to handle it.

We had a lot of discussion about allowing CR (which is the character we use) 
but decided that multi-paragraph notes fields was an idea whose time had come.

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Simon Slavin]

On 6 May 2012, at 1:59pm, "Black, Michael (IS)"  wrote:

> I modified my csvimport to allow for quoted fields.  Still automaticallhy 
> detects the # of columns.
> If quoted fields also contain internal quotes they must be escaped by 
> doubling them.
> e.g.
> col1,"this is, a ""quoted"" field",col3

I trust you as a competent programmer, but I have test cases for you:

first",second,"third
first,""second,third
first"",second,third
first,""second"",third
first,""second,""third
first"",second,""third
first"",second"",third

producing error messages (or how many columns are filled) is up to you.

> It's not fully RFC4180 compliant as it doesn't allow for CF/LF inside quotes 
> -- does anybody really need/use that?

Yes, in that I have multi-line address fields and multi-paragraph notes fields. 
 But we don't store that data in CSV files, so it wouldn't matter that a CSV 
import tool couldn't handle it.  Actually that system uses MySQL not SQLite.  
Might be interesting to see if the SQLite shell tool failed to handle it.

We had a lot of discussion about allowing CR (which is the character we use) 
but decided that multi-paragraph notes fields was an idea whose time had come.

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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Black, Michael (IS)]

I modified my csvimport to allow for quoted fields.  Still automaticallhy 
detects the # of columns.
If quoted fields also contain internal quotes they must be escaped by doubling 
them.
e.g.
col1,"this is, a ""quoted"" field",col3
It's not fully RFC4180 compliant as it doesn't allow for CF/LF inside quotes -- 
does anybody really need/use that?

#include 
#include 
#include 
#include "sqlite3.h"
#define BUFSIZE 100
#define MOD 10
#define MAXTOK 10
char *nextToken(char *line) {
  static char token[MAXTOK];
  static char *p;
  int n=0;
  if (line) {
p = line;
  }
  while(*p && *p != ',' && *p!='\r' && *p!= '\n') {
if (*p == '"') { // quoted field
  ++p; // move past first quote
  while((*p && *p!='"') || *(p+1)=='"') {
if (*p == '"' && *(p+1)=='"') { // escaped quote?
  token[n++]='"';
  p+=2;
  continue;
}
token[n++] = *p;
++p;
  }
  token[n++]=0;
  ++p;
  if (*p == ',') ++p;
  return token;
}
token[n++]=*p;
++p;
  }
  if (n > MAXTOK) {
fprintf(stderr,"Token too big??\n");
exit(1);
  }
  token[n]=0;
  ++p;
  if (*p == ',') ++p;
  if (n > 0) {
return token;
  }
  return NULL;
}
// Add comma delimited file to exisiting database/table
// Quoted strings are accepted
int main(int argc, char *argv[]) {
  sqlite3 *db;
  sqlite3_stmt *stmt;
  int rc;
  int ncol=0;
  int nline=0;
  char *buf=malloc(BUFSIZE);
  char sql[8192];
  FILE *fp;
  char *filename;
  char *databasename;
  char *tablename;
  if (argc != 4) {
fprintf(stderr,"Usage: %s filename databasename tablename\n",argv[0]);
exit(1);
  }
  filename = argv[1];
  databasename = argv[2];
  tablename = argv[3];
  rc = sqlite3_open_v2(databasename,&db,SQLITE_OPEN_READWRITE,NULL);
  if (rc) {
fprintf(stderr,"Error opening database '%s': 
%s\n",databasename,sqlite3_errmsg(db));
exit(1);
  }
  sprintf(sql,"insert into %s values (",tablename);
  fp=fopen(filename,"r");
  if (fp == NULL) {
perror(filename);
exit(1);
  }
  buf[BUFSIZE-1] = '*';
  fgets(buf,BUFSIZE,fp);
  if (buf[BUFSIZE-1] != '*') {
fprintf(stderr,"BUFSIZE not big enough...aborting\n");
exit(1);
  }
  // count the columns
  char *p=nextToken(buf);
  ncol=0;
  while(p) {
++ncol;
strcat(sql,ncol==1?"":",");
strcat(sql,"?");
p=nextToken(NULL);
  }
  printf("%d columns detected\n",ncol);
  strcat(sql,")");
  rewind(fp);
  // Let's wrap things in a transaction
  rc = sqlite3_exec(db,"BEGIN",NULL,NULL,NULL);
  if (rc) {
fprintf(stderr,"BEGIN failed on '%s': %s\n",sql,sqlite3_errmsg(db));
exit(1);
  }
  // prepare our statement
  rc = sqlite3_prepare(db,sql,strlen(sql),&stmt,NULL);
  if (rc) {
fprintf(stderr,"Prepare failed on '%s': %s\n",sql,sqlite3_errmsg(db));
exit(1);
  }
  // Loop over file file
  while(fgets(buf,BUFSIZE,fp)) {
char *p=nextToken(buf);
int i=1;
++nline;
if ((nline % MOD)==0) {
  printf("%d\r",nline);
  fflush(stdout);
}
while(p) { // bind the columns as text, table will take care of conversion 
to column types
  rc=sqlite3_bind_text(stmt,i,p,-1,SQLITE_TRANSIENT);
  if (rc) {
fprintf(stderr,"bind_text failed on '%s': %s\n",sql,sqlite3_errmsg(db));
exit(1);
  }
  ++i;
  p=nextToken(NULL);
}
if (--i != ncol) {
  fprintf(stderr,"expected %d cols, got %d cols on line#%d\n",ncol,i,nline);
} else {
  rc = sqlite3_step(stmt);
  if (rc != SQLITE_DONE) {
fprintf(stderr,"Insert failed on '%s': %s\n",sql,sqlite3_errmsg(db));
exit(1);
  }
  rc = sqlite3_reset(stmt);
  if (rc) {
fprintf(stderr,"Reset failed on '%s': %s\n",sql,sqlite3_errmsg(db));
exit(1);
  }
}
  }
  rc=sqlite3_finalize(stmt);
  if (rc) {
fprintf(stderr,"Finalize failed: %s\n",sqlite3_errmsg(db));
exit(1);
  }
  printf("%d inserts, committing...\n",nline);
  rc = sqlite3_exec(db,"COMMIT",NULL,NULL,NULL);
  if (rc) {
fprintf(stderr,"COMMIT failed on '%s': %s\n",sql,sqlite3_errmsg(db));
exit(1);
  }
  rc=sqlite3_close(db);
  if (rc) {
fprintf(stderr,"Close failed on '%s': %s\n",argv[2],sqlite3_errmsg(db));
exit(1);
  }
  fclose(fp);
  return 0;
}




Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Oliver Schneider [oli...@f-prot.com]
Sent: Friday, May 04, 2012 12:40 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] is SQLite the right tool to analyze a 44GB file


On 2012-05-01 20:41, Baruch Burstein wrote:
> It is already wrapped in a transaction.
> I seem to remember seeing somewhere that the .import command doesn't
> understand escaping, e.g.
>
> "one","two,three"
>
> will get imported as
>
> "one" | "two | three"
>
> (the quotes are part of the data, and the second column was split 

Re: [sqlite] FTS simple tokenizer with custom delimeters

[Richard Hipp]

On Sun, May 6, 2012 at 5:50 AM, Jos Groot Lipman  wrote:

> While looking around in the source of the simple tokenizer I found code
> that
> suggests custom delimeters can be specified (I want to exclude the
> underscore).
>
>
>
> http://www.sqlite.org/src/artifact/5c98225a53705e5ee34824087478cf477bdb7004?
> ln=76-87
>
> An indeed:
>  CREATE VIRTUAL TABLE ft USING fts3(title, body, tokenize=simple XX
> [&'\" *()./\\=,:;%<>-?!])
> seems to work fine.
>
> As far as I can tell this feature is undocumented which means I am not
> suppose to use it.
> Is this:
> - An oversight
> - For good reason as it is unstable
> - or: because the syntax might change in the near future?
>

Likely the reason is that we forgot that this feature even exists.  It
seems to have existed in the simple tokenizer, unchanged, since the
original introduction of FTS1 back in 2006.  The fact that it uses argv[1]
instead of argv[0]


>
> Also: I need to include the dummy XX as the delimeters are searched in
> argv[1] in stead of argv[0]. I cannot find what the argv[0] is supposed to
> do here. Any reason?
>

Probably this is a bug.  As far as I can tell, the alternative delimiter
feature of FTS1/2/3 has never been tested.  (We do not (yet) do
full-coverage testing of FTS.)  You may be the first person to ever use
this feature.  But it has been in the code for so long now that we dare not
change it for fear of breaking long-established programs.



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



-- 
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] Implement autocomplete with fts4aux

[Richard Hipp]

On Sun, May 6, 2012 at 6:17 AM, Jos Groot Lipman  wrote:

> With fts4aux, the term table would be a perfect candidate to implement an
> autocomplete function in our application.
>
> However a query
>  SELECT term FROM ft_terms WHERE term LIKE 'descr%'
> is quite slow as no 'index' is used (indexes are not possible on virtual
> tables).
>
> With checkin  
> http://www.sqlite.org/src/info/386701de Dan added an optimization for
> queries with '=', '<' and '>' in fts4aux so I rewrote my query to
>  SELECT term FROM ft_terms WHERE term BETWEEN 'descr' AND 'descr' ||
> CAST(x'FF' AS CHAR)
> and indeed: blazingly fast.
>
> Is there any risk with this (rather ugly) workaround?
>


This is basically what "term GLOB 'descr*'" does.  Did you try using GLOB
instead of LIKE.  Remember that LIKE is case insensitive whereas GLOB is
case sensitive, and fts4aux is case sensitive, so the optimization cannot
be applied to LIKE (unless you run PRAGMA case_sensitive_like).


>
> Is there any chance an optimization gets added for the simple LIKE 'abcd%'
> filter? I think it will be a very common usage case for the fts4aux virtual
> table
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Implement autocomplete with fts4aux

[Jos Groot Lipman]

With fts4aux, the term table would be a perfect candidate to implement an
autocomplete function in our application.
 
However a query 
  SELECT term FROM ft_terms WHERE term LIKE 'descr%'
is quite slow as no 'index' is used (indexes are not possible on virtual
tables).
 
With checkin  
http://www.sqlite.org/src/info/386701de Dan added an optimization for
queries with '=', '<' and '>' in fts4aux so I rewrote my query to
  SELECT term FROM ft_terms WHERE term BETWEEN 'descr' AND 'descr' ||
CAST(x'FF' AS CHAR)
and indeed: blazingly fast.
 
Is there any risk with this (rather ugly) workaround?
 
Is there any chance an optimization gets added for the simple LIKE 'abcd%'
filter? I think it will be a very common usage case for the fts4aux virtual
table
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS simple tokenizer with custom delimeters

[Jos Groot Lipman]

While looking around in the source of the simple tokenizer I found code that
suggests custom delimeters can be specified (I want to exclude the
underscore).
 
 
http://www.sqlite.org/src/artifact/5c98225a53705e5ee34824087478cf477bdb7004?
ln=76-87
 
An indeed:
  CREATE VIRTUAL TABLE ft USING fts3(title, body, tokenize=simple XX
[&'\" *()./\\=,:;%<>-?!])
seems to work fine.
 
As far as I can tell this feature is undocumented which means I am not
suppose to use it.
Is this:
- An oversight
- For good reason as it is unstable
- or: because the syntax might change in the near future?
 
Also: I need to include the dummy XX as the delimeters are searched in
argv[1] in stead of argv[0]. I cannot find what the argv[0] is supposed to
do here. Any reason?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users