Re: [sqlite] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions

[Jeff Webb]

Thanks for your help.


On 6/16/10 8:11 AM, "Pavel Ivanov"  wrote:

> Is this working as designed and the 3.0.8 behavior was incorrect?  Or is this 
> a valid bug?

I'm not developer of SQLite and don't know what did they intend to do.
But applying common sense I'd say that behavior of 3.0.8 was incorrect
and it's fixed in 3.6.23. To get the same result as you used to you
can write your query like this:

select * from foo where splitstr(foo.col1, '@', 1 ) = 'foo' collate nocase;

Here your intention would be explicit and independent of column definition.


Pavel

On Tue, Jun 15, 2010 at 6:46 PM, Jeff Webb  wrote:
> I scanned through the bug list on the website and didn't see one that was 
> related to this, so please forgive me if this has already been answered.
>
> We have created a function that takes a string and returns a substring that 
> is split on a given character (I'll provide the source below), called 
> splitstr.  In sqlite 3.0.8 we used this function and found that comparing the 
> output to a given string would use the case-sensitivity of the given string.  
> Meaning that if we used "select * from foo where splitstr(foo.col1, '@', 1 ) 
> = 'foo';" and col1 of table foo was defined as "text collate nocase" then the 
> comparisons would be case insensitive.  This was the behavior in 3.0.8.  It 
> is no longer the behavior in 3.6.23.
>
> Is this working as designed and the 3.0.8 behavior was incorrect?  Or is this 
> a valid bug?
>
> To reproduce this I create the following table:
>
>> create table foo( key integer primary key, value text collate nocase );
>> insert into foo VALUES( NULL, 'f...@bar' );
>> insert into foo VALUES( NULL, 'f...@bar' );
>> insert into foo VALUES( NULL, 'f...@bar' );
>> insert into foo VALUES( NULL, 'f...@bar' );
>
>> select * from foo where splitstr( value, '@', 1 ) = 'foo';
> 4|f...@bar
>
> However, in 3.0.8  I would get all records.
>
> Splitstr() is a very simple function:
>
> static void splitstrFunc(  sqlite3_context *context,  int argc,  
> sqlite3_value **argv ){  const char *z = NULL;  char   *temp = NULL;  
> const char *p1 = NULL;  intp2 = 0;  assert( argc==3 );  z = 
> sqlite3_value_text(argv[0]);  if( z==0 )  return;  p1 = 
> sqlite3_value_text(argv[1]);  if( p1 == 0 )  return;  p2 = 
> sqlite3_value_int(argv[2]);  if( (temp = strchr( z, p1[0] )) == NULL )  
> return;  *temp = '\0';  temp++;  if( p2 == 2 )  z = temp;  else  if( p2 
> != 1 )  /* must specify either first or second */  return;  
> sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT); }
> And is added to the sqlite3RegisterGlobalFunctions() aBuiltinFunc[] array as:
>
> (FUNCTION(splitstr, 3, 0, 0, splitstrFunc ),
>
> Thanks for your help
>
> Jeff Webb
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] HELP : sqlite execute low speed in ARM9+Linux embadded system.

[Simon Slavin]

On 17 Jun 2010, at 4:47pm, backup wrote:

> sprintf(sqlstr,"update db set isSent=1 where ID in (select ID from  
> db where isSent=0 limit %d);",RECORD_NUM);

Can you post the commands used to create the table and any indexes on it ?

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

Re: [sqlite] could be memory leak

[Jay A. Kreibich]

On Thu, Jun 17, 2010 at 02:07:50PM -0700, VIKRANT scratched on the wall:
> I have following C program
> It takes the DB path as argument
> 
> For testing, I have placed the DB on a dir over the NFS
> 
> Now after the open call, I unshare the NFS from the NFS
> server to generate I/O errors
> Once the exec starts receiving errors, it is leaking memory..
> after 6 hours, its leaking upto 1G

  "It" is not leaking memory, you are:


  http://sqlite.org/c3ref/exec.html

 If the 5th parameter to sqlite3_exec() is not NULL then any error
 message is written into memory obtained from sqlite3_malloc() and
 passed back through the 5th parameter. To avoid memory leaks, the
 application should invoke sqlite3_free() on error message strings
 returned through the 5th parameter of of sqlite3_exec() after the
 error message string is no longer needed. 



   -j

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

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

[sqlite] Fw: could be memory leak

[VIKRANT]

Adding the right alias...

  
I have following C program
It takes the DB path as argument

For testing, I have placed the DB on a dir over the NFS

Now after the open call, I unshare the NFS from the NFS server to generate I/O 
errors
Once the exec starts receiving errors, it is leaking memory.. after 6 hours, 
its leaking upto 1G


I'm using version 3.3.9 and I see the same with version 3.6.23
Any help will be appreciated!
thanks,
-vikrant

---prstat for vmem---



   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
  8548 root   15M   10M cpu0 0    0   0:00:44  33% vmem/1
Total: 1 processes, 1 lwps, load averages: 0.59, 0.59, 0.40
   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
  8548 root   15M   10M cpu1 0    0   0:00:47  34% vmem/1
Total: 1 processes, 1 lwps, load averages: 0.60, 0.60, 0.40
   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP


   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
  8548 root 1130M 1123M cpu0 0    0   2:53:12  50% vmem/1
Total: 1 processes, 1 lwps, load averages: 1.06, 1.05, 1.05
   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
  8548 root 1130M 1123M cpu0 0    0   2:53:17  50% vmem/1
Total: 1 processes, 1 lwps, load averages: 1.05, 1.05, 1.05


 


-vmem.c--
#include
#include 
#include 
#include 
#include 
#include 
#include 

cbproc(void *iotemp,
    int  argc,
    char **argv,
    char **colname)
{
return 0;
}


int main(int argc, char* argv[])
{
char dbpath[1024];
void* hdb;
int i,j,k;
char sqlcmd[1024];
char *errmsg = (char *) NULL;
int ret;

    sprintf(sqlcmd, "select * from iotemp ");
    strcpy(dbpath, argv[1]);

for(i=0;i<1;i++) {
ret = sqlite3_open(dbpath, (sqlite3 **) &hdb);
fprintf(stdout, "sqlite3_exec: return value = (%d)\n", ret);
if(ret) {
    fprintf(stderr, "sqlite3_open Error errno=(%d)", errno );
}

fprintf(stdout, "Sleeping for 50 sec\n");
sleep(50);

fprintf(stdout, "Sleeping DONE for 50 sec\n");
while(1) {
ret = sqlite3_exec((sqlite3 *) hdb, sqlcmd, cbproc, NULL, &errmsg );
fprintf(stdout, "sqlite3_exec: return value = (%d)\n", ret);
if(ret) {
    fprintf(stderr, "sqlite3_exec Error errno=(%d) (%s)", errno, errmsg);
} else {
    fprintf(stdout, "sqlite3_exec OK \n");
}
}
}

return 0;
}


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

Re: [sqlite] could be memory leak

[VIKRANT]

Sent the earlier email half way..
I'm using version 3.3.9 and I see the same with version 3.6.23
Any help will be appreciated!
thanks,
-vikrant

---prstat for vmem---



   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
  8548 root   15M   10M cpu0 0    0   0:00:44  33% vmem/1
Total: 1 processes, 1 lwps, load averages: 0.59, 0.59, 0.40
   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
  8548 root   15M   10M cpu1 0    0   0:00:47  34% vmem/1
Total: 1 processes, 1 lwps, load averages: 0.60, 0.60, 0.40
   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP


   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
  8548 root 1130M 1123M cpu0 0    0   2:53:12  50% vmem/1
Total: 1 processes, 1 lwps, load averages: 1.06, 1.05, 1.05
   PID USERNAME  SIZE   RSS STATE  PRI NICE  TIME  CPU PROCESS/NLWP
  8548 root 1130M 1123M cpu0 0    0   2:53:17  50% vmem/1
Total: 1 processes, 1 lwps, load averages: 1.05, 1.05, 1.05







From: VIKRANT 
To: sqlite-users@sqlite.org
Sent: Fri, June 18, 2010 2:37:50 AM
Subject: could be memory leak


I have following C program
It takes the DB path as argument

For testing, I have placed the DB on a dir over the NFS

Now after the open call, I unshare the NFS from the NFS server to generate I/O 
errors
Once the exec starts receiving errors, it is leaking memory.. after 6 hours, 
its leaking upto 1G



-vmem.c--
#include
#include 
#include 
#include 
#include 
#include 
#include 

cbproc(void *iotemp,
    int  argc,
    char **argv,
    char **colname)
{
return 0;
}


int main(int argc, char* argv[])
{
char dbpath[1024];
void* hdb;
int i,j,k;
char sqlcmd[1024];
char *errmsg = (char *) NULL;
int ret;

    sprintf(sqlcmd, "select * from iotemp ");
    strcpy(dbpath, argv[1]);

for(i=0;i<1;i++) {
ret = sqlite3_open(dbpath, (sqlite3 **) &hdb);
fprintf(stdout, "sqlite3_exec: return value = (%d)\n", ret);
if(ret) {
    fprintf(stderr, "sqlite3_open Error errno=(%d)", errno );
}

fprintf(stdout, "Sleeping for 50 sec\n");
sleep(50);

fprintf(stdout, "Sleeping DONE for 50 sec\n");
while(1) {
ret = sqlite3_exec((sqlite3 *) hdb, sqlcmd, cbproc, NULL, &errmsg );
fprintf(stdout, "sqlite3_exec: return value = (%d)\n", ret);
if(ret) {
    fprintf(stderr, "sqlite3_exec Error errno=(%d) (%s)", errno, errmsg);
} else {
    fprintf(stdout, "sqlite3_exec OK \n");
}
}
}

return 0;
}



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

[sqlite] could be memory leak

[VIKRANT]

I have following C program
It takes the DB path as argument

For testing, I have placed the DB on a dir over the NFS

Now after the open call, I unshare the NFS from the NFS server to generate I/O 
errors
Once the exec starts receiving errors, it is leaking memory.. after 6 hours, 
its leaking upto 1G



-vmem.c--
#include
#include 
#include 
#include 
#include 
#include 
#include 

cbproc(void *iotemp,
    int  argc,
    char **argv,
    char **colname)
{
return 0;
}


int main(int argc, char* argv[])
{
char dbpath[1024];
void* hdb;
int i,j,k;
char sqlcmd[1024];
char *errmsg = (char *) NULL;
int ret;

    sprintf(sqlcmd, "select * from iotemp ");
    strcpy(dbpath, argv[1]);

for(i=0;i<1;i++) {
ret = sqlite3_open(dbpath, (sqlite3 **) &hdb);
fprintf(stdout, "sqlite3_exec: return value = (%d)\n", ret);
if(ret) {
    fprintf(stderr, "sqlite3_open Error errno=(%d)", errno );
}

fprintf(stdout, "Sleeping for 50 sec\n");
sleep(50);

fprintf(stdout, "Sleeping DONE for 50 sec\n");
while(1) {
ret = sqlite3_exec((sqlite3 *) hdb, sqlcmd, cbproc, NULL, &errmsg );
fprintf(stdout, "sqlite3_exec: return value = (%d)\n", ret);
if(ret) {
    fprintf(stderr, "sqlite3_exec Error errno=(%d) (%s)", errno, errmsg);
} else {
    fprintf(stdout, "sqlite3_exec OK \n");
}
}
}

return 0;
}


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

[sqlite] HELP : sqlite execute low speed in ARM9+Linux embadded system.

[backup]

hi, everyone,
   I program in an embaded system: ARM9 soc S3C2410 +Linux 2.4+sqlite  
 3.3.
 All data store in NAND FLASH , the file system is YAFFS .
  My question is the function "sqlite3_exec(.)"  take too many   
 seconds,
  the following is my source code, please see the "printf" 's comment,
  every "sqlite3_exec(.)"   takes 5 seconds, but how to reduce  
 the time?
 //-- start of code
 #define RECORD_NUM 1
 char QueryData(void)
 {
 struct timeval tpstart,tpend;
 float timeuse;
 char ErrorFlag=1;
 unsigned int di,dj,dk;
 DataBuffer[0]='\0'; //DataBuffer is goblal variable
 sprintf(sqlstr,"select * from db where isSent=0 limit  
 %d;",RECORD_NUM);
 p(semid);
 gettimeofday(&tpstart,NULL);
 rc = sqlite3_exec(db, sqlstr, SQLCallBack, 0, &zErrMsg);
 gettimeofday(&tpend,NULL);
 timeuse=100*(tpend.tv_sec-tpstart.tv_sec)+
 tpend.tv_usec-tpstart.tv_usec;
 timeuse/=100;
 printf("sqlite3 select * Used Time:%f\n",timeuse); // print value :5  
 seconds
 v(semid);
 sprintf(sqlstr,"update db set isSent=1 where ID in (select ID from  
 db where isSent=0 limit %d);",RECORD_NUM);
 p(semid);
 gettimeofday(&tpstart,NULL);
 rc = sqlite3_exec(db, sqlstr, 0, 0, &zErrMsg);
 gettimeofday(&tpend,NULL);
 timeuse=100*(tpend.tv_sec-tpstart.tv_sec)+
 tpend.tv_usec-tpstart.tv_usec;
 timeuse/=100;
 printf("sqlite3 update Used Time:%f\n",timeuse); //print value : 5s
 v(semid);
 }
 static int SQLCallBack(void *NotUsed, int argc, char **argv, char  
 **azColName)
 {
 struct timeval tpstart,tpend;
 float timeuse;
 int i;
 char tstr[500];
 gettimeofday(&tpstart,NULL);
 // argv[0] is ID ,no use for server
 sprintf(tstr,"'%s',",argv[1]);
 strcat(DataBuffer,tstr);
 for(i=2; ihttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA integrity_check error recovery questions

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/17/2010 01:50 PM, Simon Slavin wrote:
> Sometimes it's best to use the command-line tool to dump the data to a file 
> of SQL commands,

The command line tool needs its code fixing first:

  http://www.sqlite.org/src/tktview?name=2466653295

In particular look for "dump" (including quotes) in shell.c and then examine
the error handling. Basically errors are silently ignored.  In
SQLITE_CORRUPT cases a table iteration is retried in reverse rowid order.
This means you will not know if dump failed or has only given you some of
the data.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwaj68ACgkQmOOfHg372QQvOwCghNVGYHWUrnYTGPCscTAfKyRC
VbIAoKDwBFFosOaTCvtXktJdVwZrybdn
=EN8y
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA integrity_check error recovery questions

[Simon Slavin]

On 17 Jun 2010, at 9:45pm, Jim Terman wrote:

> Does the integrity_check PRAGMA fix any problems that it encounters or 
> does it just report them?

Report.

> Are there anyway of recovering from these errors or is the database 
> unfixable?

Sometimes VACUUM fixes them.  It can conceivably make things worse.

Sometimes it's best to use the command-line tool to dump the data to a file of 
SQL commands, then use that file to create a new database.  But even that may 
not work properly, depending on the nature of the corruption in your data file.

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

[sqlite] PRAGMA integrity_check error recovery questions

[Jim Terman]

Does the integrity_check PRAGMA fix any problems that it encounters or 
does it just report them?

Are there anyway of recovering from these errors or is the database 
unfixable?

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

Re: [sqlite] c compiler during install

[Glen Scratchley]

Thanks to all who (tried to) help me.
I was unable to get a valid gcc on my machine, so I moved to a defend
RHS machine, which is RHS3
./configure and the commands that follow in the install file, seem to
have worked correctly.
 


Glen Scratchley
DMS Solutions Architect
Macro 4, Inc. 

(973) 526-3900  Direct: (973) 526-3832  Fax: (973) 526-3899 
A Division of the UNICOM Group of Companies
www.macro4.com

***
This message (including any attachments) contains confidential
information that is PRIVILEGED, CONFIDENTIAL and/or ATTORNEY WORK
PRODUCT and is intended only for the individual(s) named herein. If you
are not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this email is strictly
prohibited. If you have received this message in error, please notify
the UNICOM Systems, Inc. Postmaster (postmas...@unicomsi.com) of the
error immediately, do not read or use the email and any attachments in
any manner, destroy all copies, and delete it from your system if the
communication was sent via email. UNICOM Systems, Inc. (818) 838-0606
Fax: (818) 838-0776
** 


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael
(IS)
Sent: Thursday, June 17, 2010 11:23 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install

You must not be a RHN subscriber.  So you'll have to install from the
CD.
 
You should be able to find gcc on the cd and do "rpm -i gcc*" 
It might complain about needing some other things too which you will
also need to install.
 
Or...if you have an X window running your Add/Remove Software entry
might work.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Glen Scratchley
Sent: Thu 6/17/2010 9:38 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install



I think that this means I'm missing something, and the ./configure still
doesn't like it:

[r...@selkirk sqlite-3.6.23.1]# whoami
root
[r...@selkirk sqlite-3.6.23.1]# yum install gcc Loading "installonlyn"
plugin Loading "rhnplugin" plugin There was an error parsing the RHN
proxy settings.
RHN support will be disabled.
Setting up Install Process
Setting up repositories
No Repositories Available to Set Up
Reading repository metadata in from local files Parsing package install
arguments Setting up repositories No Repositories Available to Set Up
Reading repository metadata in from local files No Match for argument:
gcc Nothing to do [r...@selkirk sqlite-3.6.23.1]# [r...@selkirk
sqlite-3.6.23.1]# gcc -v ignoring nonexistent directory
"/usr/lib/gcc/i386-redhat-linux/4.1.1/include"
ignoring nonexistent directory
"/usr/lib/gcc/i386-redhat-linux/4.1.1/../../../../i386-redhat-linux/incl
ude"
#include "..." search starts here:
#include <...> search starts here:
 /usr/local/include
 /usr/include
End of search list.

[


-

This email has been scanned for all known viruses by the MessageLabs
Email Security Service and the Macro 4 internal virus protection system.
.


- 
This email has been scanned for all known viruses by the MessageLabs Email
Security Service and the Macro 4 internal virus protection system.
. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Ed Scherer]

(Arggg!!! Second try sending this; first attempt seems to have been
truncated.
If you don't see my signature at the end of this one, it got truncated
again.)

> Hi,
> I have two processes connecting to a database. Both can modify database.
When
> some process modify database, I want the other to be notified (so, it can
> reload data).
> Currently, I use unix sockets, so a process can notify all listener
process
> that something has changed. But I wonder if there's a better way to
achieve
> that.
> I've tried to a create a custom function (with sqlite3_create_function),
and
> use triggers. But unfortunately, my trigger was executed for the modifying
> process.
> So, is there a sqlitish way to have process notifications, or should I
stick
> to using unix sockets to tell other processes something has changed in the
> database.
> I'm using C++ language (C api for sqlite)
>
> regards
> arno
Howdy, Arno.

I'm a long-time lurker on this list, but until now I haven't had much to
contribute.

However... on this topic, I might finally have something to offer.

About a year ago, I was working on this very problem, and have a working
prototype of an SQLite library with a cross-process asynchronous update
notification mechanism.

When we originally encountered this problem, we tried to characterize it
as best we could and then considered a few alternative solutions
(which we numbered Option #1 through Option #5 in our documents). One of
our (formerly) internal engineering documents includes discussion of this
subject and might provide some useful context; see

http://www.innovision.com/techdocs/sqlite/PersistentDataSharingAndConcurrenc
yControl.pdf

Ultimately, we developed prototypes (i.e., enhanced SQLite libraries)
for a couple of the options (first, Option #5, and later, Option #3).

Another (formerly) internal document further expands on the requirements we
wanted to satisfy and how we went about solving (at least sufficient for a
prototype) the problem; see

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChange.pdf

I'll warn you that the document I just referenced probably needs to be
updated to be a more accurate reflection of our latest solution to the
problem (which is now based on Option #3 rather than Option #5).

>From the perspective of a programmer, what they're seeing with the
enhanced SQLite library is a new SQLite API function:

/*
** The sqlite3_async_update_hook() interface registers a callback function
** with the [database connection] identified by the first argument
** to be invoked whenever the database has been modified in any way.
**
** The monitoring of the database occurs in a separate thread (one thread
** per monitored connection) from the thread that invoked
** sqlite3_async_update_hook().  When a database modification is detected,
** the callback function is called from the monitoring thread.  Thus,
** some thread-level synchronization mechanism (e.g., appropriate thread
** safety mode and database locking) should be used as appropriate.
**
** If the async update hook callback function pointer is NULL then the no
** update callbacks are made.
**
** If an async update hook callback function has been registered with a
** database connection at the time it is closed, the callback function
** will automatically be unregistered as part of the connection close
** operation.
**
** The second argument is a pointer to the function to invoke when the
** database is modified.
** The first argument to the callback is a copy of the third argument
** to sqlite3_async_update_hook().
** The second callback argument is a pointer to the database name.
**
** If another function was previously registered, its pArg value
** is returned.  Otherwise NULL is returned.
*/
void *sqlite3_async_update_hook(
  sqlite3*,
  void(*)(void *, const char *),
  void*
);


When I was working on the Option #5 prototype; I created a video demo.
If I did it again for Option #3, it probably wouldn't be much different.
The demo shows the mechanism working both within a single process as
well as across two processes (a Lua-based GUI prototype app, and the
sqlite3.exe command line utility, each linked against the enhanced
SQLite library). (The Lua aspect is irrelevant here; LuaSQL just happened
to be another thing we wanted to evaluate in this prototype).

The video demo is available in two formats an AVI (codec available at
http://www.techsmith.com/download/codecs.asp):

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChangeExample3.
avi

and a lower-quality MPEG:

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChangeExample3.
mp4


Now... I've been away from this for at least half a year, but here are
some of my thoughts at this point:

1. It appears that our desire to have cross-process update notification
is shared by others, so maybe it's time to have the SQLite community dig
into this issue more and see if we can arrive at a mutually beneficial
solution.

2. Any solution involving

Re: [sqlite] notify all processes of database modification

[Ed Scherer]

> Hi,
> I have two processes connecting to a database. Both can modify database.
When
> some process modify database, I want the other to be notified (so, it can
> reload data).
> Currently, I use unix sockets, so a process can notify all listener
process
> that something has changed. But I wonder if there's a better way to
achieve
> that.
> I've tried to a create a custom function (with sqlite3_create_function),
and
> use triggers. But unfortunately, my trigger was executed for the modifying
> process.
> So, is there a sqlitish way to have process notifications, or should I
stick
> to using unix sockets to tell other processes something has changed in the
> database.
> I'm using C++ language (C api for sqlite)
>
> regards
> arno
Howdy, Arno.

I'm a long-time lurker on this list, but until now I haven't had much to
contribute.

However... on this topic, I might finally have something to offer.

About a year ago, I was working on this very problem, and have a working
prototype of an SQLite library with a cross-process asynchronous update
notification mechanism.

When we originally encountered this problem, we tried to characterize it
as best we could and then considered a few alternative solutions
(which we numbered Option #1 through Option #5 in our documents). One of
our (formerly) internal engineering documents includes discussion of this
subject and might provide some useful context; see

http://www.innovision.com/techdocs/sqlite/PersistentDataSharingAndConcurrenc
yControl.pdf

Ultimately, we developed prototypes (i.e., enhanced SQLite libraries)
for a couple of the options (first, Option #5, and later, Option #3).

Another (formerly) internal document further expands on the requirements we
wanted to satisfy and how we went about solving (at least sufficient for a
prototype) the problem; see

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChange.pdf

I'll warn you that the document I just referenced probably needs to be
updated to be a more accurate reflection of our latest solution to the
problem (which is now based on Option #3 rather than Option #5).

>From the perspective of a programmer, what they're seeing with the
enhanced SQLite library is a new SQLite API function:

/*
** The sqlite3_async_update_hook() interface registers a callback function
** with the [database connection] identified by the first argument
** to be invoked whenever the database has been modified in any way.
**
** The monitoring of the database occurs in a separate thread (one thread
** per monitored connection) from the thread that invoked
** sqlite3_async_update_hook().  When a database modification is detected,
** the callback function is called from the monitoring thread.  Thus,
** some thread-level synchronization mechanism (e.g., appropriate thread
** safety mode and database locking) should be used as appropriate.
**
** If the async update hook callback function pointer is NULL then the no
** update callbacks are made.
**
** If an async update hook callback function has been registered with a
** database connection at the time it is closed, the callback function
** will automatically be unregistered as part of the connection close
** operation.
**
** The second argument is a pointer to the function to invoke when the
** database is modified.
** The first argument to the callback is a copy of the third argument
** to sqlite3_async_update_hook().
** The second callback argument is a pointer to the database name.
**
** If another function was previously registered, its pArg value
** is returned.  Otherwise NULL is returned.
*/
void *sqlite3_async_update_hook(
  sqlite3*,
  void(*)(void *, const char *),
  void*
);


When I was working on the Option #5 prototype; I created a video demo.
If I did it again for Option #3, it probably wouldn't be much different.
The demo shows the mechanism working both within a single process as
well as across two processes (a Lua-based GUI prototype app, and the
sqlite3.exe command line utility, each linked against the enhanced
SQLite library). (The Lua aspect is irrelevant here; LuaSQL just happened
to be another thing we wanted to evaluate in this prototype).

The video demo is available in two formats an AVI (codec available at
http://www.techsmith.com/download/codecs.asp):

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChangeExample3.
avi

and a lower-quality MPEG:

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChangeExample3.
mp4


Now... I've been away from this for at least half a year, but here are
some of my thoughts at this point:

1. It appears that our desire to have cross-process update notification
is shared by others, so maybe it's time to have the SQLite community dig
into this issue more and see if we can arrive at a mutually beneficial
solution.

2. Any solution involving the database writer/changer to take any
explicit action (e.g. signaling, etc.) to notify other processes
possibly using that database file that it has changed is 

Re: [sqlite] c compiler during install

[Glen Scratchley]

Thanks.  Another poster suggested (and I confirmed) that I don't have a
valid connection to the Internet.

Once I fix that, I will confirm that I am an RHN subscriber.

 


Glen Scratchley
DMS Solutions Architect
Macro 4, Inc. 

(973) 526-3900  Direct: (973) 526-3832  Fax: (973) 526-3899 
A Division of the UNICOM Group of Companies
www.macro4.com

***
This message (including any attachments) contains confidential
information that is PRIVILEGED, CONFIDENTIAL and/or ATTORNEY WORK
PRODUCT and is intended only for the individual(s) named herein. If you
are not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this email is strictly
prohibited. If you have received this message in error, please notify
the UNICOM Systems, Inc. Postmaster (postmas...@unicomsi.com) of the
error immediately, do not read or use the email and any attachments in
any manner, destroy all copies, and delete it from your system if the
communication was sent via email. UNICOM Systems, Inc. (818) 838-0606
Fax: (818) 838-0776
** 


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael
(IS)
Sent: Thursday, June 17, 2010 11:23 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install

You must not be a RHN subscriber.  So you'll have to install from the
CD.
 
You should be able to find gcc on the cd and do "rpm -i gcc*" 
It might complain about needing some other things too which you will
also need to install.
 
Or...if you have an X window running your Add/Remove Software entry
might work.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Glen Scratchley
Sent: Thu 6/17/2010 9:38 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install



I think that this means I'm missing something, and the ./configure still
doesn't like it:

[r...@selkirk sqlite-3.6.23.1]# whoami
root
[r...@selkirk sqlite-3.6.23.1]# yum install gcc Loading "installonlyn"
plugin Loading "rhnplugin" plugin There was an error parsing the RHN
proxy settings.
RHN support will be disabled.
Setting up Install Process
Setting up repositories
No Repositories Available to Set Up
Reading repository metadata in from local files Parsing package install
arguments Setting up repositories No Repositories Available to Set Up
Reading repository metadata in from local files No Match for argument:
gcc Nothing to do [r...@selkirk sqlite-3.6.23.1]# [r...@selkirk
sqlite-3.6.23.1]# gcc -v ignoring nonexistent directory
"/usr/lib/gcc/i386-redhat-linux/4.1.1/include"
ignoring nonexistent directory
"/usr/lib/gcc/i386-redhat-linux/4.1.1/../../../../i386-redhat-linux/incl
ude"
#include "..." search starts here:
#include <...> search starts here:
 /usr/local/include
 /usr/include
End of search list.

[


-

This email has been scanned for all known viruses by the MessageLabs
Email Security Service and the Macro 4 internal virus protection system.
.


- 
This email has been scanned for all known viruses by the MessageLabs Email
Security Service and the Macro 4 internal virus protection system.
. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c compiler during install

[Black, Michael (IS)]

You must not be a RHN subscriber.  So you'll have to install from the CD.
 
You should be able to find gcc on the cd and do "rpm -i gcc*" 
It might complain about needing some other things too which you will also need 
to install.
 
Or...if you have an X window running your Add/Remove Software entry might work.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Glen Scratchley
Sent: Thu 6/17/2010 9:38 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install



I think that this means I'm missing something, and the ./configure still
doesn't like it:

[r...@selkirk sqlite-3.6.23.1]# whoami
root
[r...@selkirk sqlite-3.6.23.1]# yum install gcc
Loading "installonlyn" plugin
Loading "rhnplugin" plugin
There was an error parsing the RHN proxy settings.
RHN support will be disabled.
Setting up Install Process
Setting up repositories
No Repositories Available to Set Up
Reading repository metadata in from local files
Parsing package install arguments
Setting up repositories
No Repositories Available to Set Up
Reading repository metadata in from local files
No Match for argument: gcc
Nothing to do
[r...@selkirk sqlite-3.6.23.1]#
[r...@selkirk sqlite-3.6.23.1]# gcc -v
ignoring nonexistent directory
"/usr/lib/gcc/i386-redhat-linux/4.1.1/include"
ignoring nonexistent directory
"/usr/lib/gcc/i386-redhat-linux/4.1.1/../../../../i386-redhat-linux/incl
ude"
#include "..." search starts here:
#include <...> search starts here:
 /usr/local/include
 /usr/include
End of search list.

[

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

Re: [sqlite] c compiler during install

[Glen Scratchley]

It would appear that you are correct:

[r...@selkirk ~]# ping npr.org
PING npr.org (216.35.221.76) 56(84) bytes of data.

--- npr.org ping statistics ---
8 packets transmitted, 0 received, 100% packet loss, time 7002ms

[r...@selkirk ~]#

I will fix this before I continue,
Thanks, 


Glen Scratchley
DMS Solutions Architect
Macro 4, Inc. 

(973) 526-3900  Direct: (973) 526-3832  Fax: (973) 526-3899 
A Division of the UNICOM Group of Companies
www.macro4.com

***
This message (including any attachments) contains confidential
information that is PRIVILEGED, CONFIDENTIAL and/or ATTORNEY WORK
PRODUCT and is intended only for the individual(s) named herein. If you
are not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this email is strictly
prohibited. If you have received this message in error, please notify
the UNICOM Systems, Inc. Postmaster (postmas...@unicomsi.com) of the
error immediately, do not read or use the email and any attachments in
any manner, destroy all copies, and delete it from your system if the
communication was sent via email. UNICOM Systems, Inc. (818) 838-0606
Fax: (818) 838-0776
** 


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Teg
Sent: Thursday, June 17, 2010 10:50 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install

Hello Glen,

I'm not a Linux guy (though I've installed it and run a DB on it
before) but,

GS> There was an error parsing the RHN proxy settings.
GS> RHN support will be disabled.

Suggests your install can't get out on to the 'net to find the files
you're looking for. If YUM can't get to the net, YUM can't download the
upgrades.


C



Thursday, June 17, 2010, 10:13:01 AM, you wrote:

GS> Thanks.

GS> It doesn't appear that I have the necessary files to install gcc:

GS> [r...@selkirk 4.1.1]# gcc -v
GS> bash: gcc: command not found
GS> [r...@selkirk 4.1.1]# yum install gcc Loading "installonlyn" plugin 
GS> Loading "rhnplugin" plugin There was an error parsing the RHN proxy 
GS> settings.
GS> RHN support will be disabled.
GS> Setting up Install Process
GS> Setting up repositories
GS> No Repositories Available to Set Up
GS> Reading repository metadata in from local files Parsing package 
GS> install arguments Setting up repositories No Repositories Available 
GS> to Set Up Reading repository metadata in from local files No Match 
GS> for argument: gcc Nothing to do [r...@selkirk 4.1.1]#

GS> Should I get this from RHS (I don't have install CDs)? 


GS> Glen Scratchley
GS> DMS Solutions Architect
GS> Macro 4, Inc. 



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

-

This email has been scanned for all known viruses by the MessageLabs
Email Security Service and the Macro 4 internal virus protection system.
.


- 
This email has been scanned for all known viruses by the MessageLabs Email
Security Service and the Macro 4 internal virus protection system.
. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c compiler during install

[Teg]

Hello Glen,

I'm not a Linux guy (though I've installed it and run a DB on it
before) but,

GS> There was an error parsing the RHN proxy settings.
GS> RHN support will be disabled.

Suggests your install can't get out on to the 'net to find the files
you're looking for. If YUM can't get to the net, YUM can't download
the upgrades.


C



Thursday, June 17, 2010, 10:13:01 AM, you wrote:

GS> Thanks.

GS> It doesn't appear that I have the necessary files to install gcc:

GS> [r...@selkirk 4.1.1]# gcc -v
GS> bash: gcc: command not found
GS> [r...@selkirk 4.1.1]# yum install gcc
GS> Loading "installonlyn" plugin
GS> Loading "rhnplugin" plugin
GS> There was an error parsing the RHN proxy settings.
GS> RHN support will be disabled.
GS> Setting up Install Process
GS> Setting up repositories
GS> No Repositories Available to Set Up
GS> Reading repository metadata in from local files
GS> Parsing package install arguments
GS> Setting up repositories
GS> No Repositories Available to Set Up
GS> Reading repository metadata in from local files
GS> No Match for argument: gcc
GS> Nothing to do
GS> [r...@selkirk 4.1.1]#

GS> Should I get this from RHS (I don't have install CDs)? 


GS> Glen Scratchley
GS> DMS Solutions Architect
GS> Macro 4, Inc. 



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

Re: [sqlite] SELECT behaviour with INDEX

[Jay A. Kreibich]

On Thu, Jun 17, 2010 at 09:07:56AM -0500, Black, Michael (IS) scratched on the 
wall:
> I sense the beginning of a "How to use indexes" page.
>  
> Things you need know:
> Only one index is used per query (or insert??).

  It isn't that bad.

  In general, it is "one index PER TABLE, per query."  But it isn't
  even that bad.  Sub-queries can often use their own set of indexes,
  or any other situation when it is cheaper to extract a bunch of small
  data sets and combine then, rather than perform an scan, can use
  multiple indexes associated to the same table.  
  
  The easiest case to understand is a chain of OR'ed WHERE conditions...
  if we assume each OR will produce a moderate to small sub-set, it can
  be cheaper to use an index to generate each sub-set and then OR the 
  sub-sets, rather than do a full table scan and apply the OR chain to
  each row.

  Yeah, it gets complex real fast

   -j

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

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

Re: [sqlite] SELECT behaviour with INDEX

[Jay A. Kreibich]

On Thu, Jun 17, 2010 at 08:44:25AM -0500, Black, Michael (IS) scratched on the 
wall:
> Is there any advantage/disadvantage to having seperate indexes in a
> case like this?

  In a case like this, no.  If each column was indexed individually one
  one of them would be used in this query.  There is a (very) small
  performance gain in using the compound index, but you'd have to do a
  lot of data analysis to figure out if the additional overhead and
  storage from the second column is worth the gains in query speed.
  That's a very subjective analysis anyways.


  In any index placement there are advantages and disadvantages.
  Different indexes provide different performance boosts, and you very
  much need the right tool for the job.  It is a hard problem.


> In other words, as a simple design goal to have a separate index
> that matches whatever you select on and order on?

  Yes and no.  If the only concern is query performance, then in theory
  this is true.  But indexes have cost associated.  They add noticeable
  overhead to INSERT/UPDATE/DELETE, and each additional index adds
  additional overhead.  They also add size to the database.  Even if
  your query-to-modification ratio is very very high on the query side,
  adding indexes that provide very marginal benefits may still hurt
  you, thanks to increased I/O costs and cache crowding.

  So, like everything, there is a huge "it depends."  The biggest thing
  is likely query-to-modify ratio, followed by query frequency.  You
  don't spend a lot of time optimizing the query (or index structure)
  for a maintenance routine that runs once a week, especially if that
  modification adds 0.1% overhead to the query you run 10,000 times a
  day (or maybe you do, but the circumstance would be extreme).

  Index placement is deep magic, and is highly dependent on the
  database, the database product, the platform (cache memory, disk
  speeds), the queries, the query frequency, the data flux rate, the
  phase of the moon, and the alignment of the planets.

  It also represents this odd conflict of interest in the whole
  philosophy of SQL query design.  In theory, SQL is all about saying
  what you want, and letting the database engine to all kinds of
  mysterious stuff to get you the results as fast as possible.  In
  practice, it only half works that way, so there is a lot of
  second-guessing about where and when to place indexes.  In many ways,
  that takes more skill and understanding than if SQL just allowed more
  explicit query designation.

  Modern query optimizers are pretty good at doing a good job with the
  tools at hand, but the system is not well setup to provide feedback
  to the developer-- "Put an index on this column and you'll boost
  performance 23.54%", for example.  This isn't unique to SQLite, it
  is an issue with all SQL databases.  Indexes are outside of the
  relational model, so they have evolved in a somewhat haphazard way,
  like the rest of SQL.  It doesn't help that the foundation ideas were
  laid down when a megabyte or two of data was HUGE.  It hasn't scaled
  so well to datasets where good indexes are a critical requirement,
  and not a helpful addition.
  

> It seems to me that if you do "SELECT * FROM log WHERE
> id_clie...@idclient ORDER BY utc DESC LIMIT 1" aren't you better off
> with two indexes, one for id_client and one for utc?  That way you
> don't have to worry about order dependence at all.  It seems only
> if you start doing WHERE clauses on both should you need a compound index.

  Doesn't work that way.  Once a set of rows have been extracted from an
  index, the data is no longer part of the original table, so a second
  index can't be used.  You can look deeper into the same index, but
  not a second index.

  For example, I hand you two phone books--- one ordered by the
  standard last-name, first-name, etc., and a "reverse" phone book that
  is ordered by telephone number (for number => name lookups).

  I then ask you to provide a list of all the people with a last name
  of "Smith", and I want the list ordered by phone number.  You can use
  either phonebook to get the answer, but you can't use both.  You
  either lookup all the Smiths and manually sort that sub-set, or you
  scan the whole reverse book, plucking out the Smiths as you find them
  (and knowing the list is already in order and no further sorting is
  required).

  The only way to use an index to fill both conditions is if the
  phonebook is sorted by both name and then phone number... e.g. a
  compound index on just the right columns.
  
  
  As a general rule of thumb, most database systems can only use one
  index from one table per query (or subquery).  There are a small
  handful of exceptions (such as chained OR conditions), but that's
  a good place to start.

> And in this specific case unless you have a boat load of utc's for
> each client_id the utc index isn't going to buy you much at all.

  Correct.  It is doubtful the I/O

Re: [sqlite] c compiler during install

[Glen Scratchley]

I think that this means I'm missing something, and the ./configure still
doesn't like it:

[r...@selkirk sqlite-3.6.23.1]# whoami
root
[r...@selkirk sqlite-3.6.23.1]# yum install gcc
Loading "installonlyn" plugin
Loading "rhnplugin" plugin
There was an error parsing the RHN proxy settings.
RHN support will be disabled.
Setting up Install Process
Setting up repositories
No Repositories Available to Set Up
Reading repository metadata in from local files
Parsing package install arguments
Setting up repositories
No Repositories Available to Set Up
Reading repository metadata in from local files
No Match for argument: gcc
Nothing to do
[r...@selkirk sqlite-3.6.23.1]# 
[r...@selkirk sqlite-3.6.23.1]# gcc -v
ignoring nonexistent directory
"/usr/lib/gcc/i386-redhat-linux/4.1.1/include"
ignoring nonexistent directory
"/usr/lib/gcc/i386-redhat-linux/4.1.1/../../../../i386-redhat-linux/incl
ude"
#include "..." search starts here:
#include <...> search starts here:
 /usr/local/include
 /usr/include
End of search list.

[r...@selkirk sqlite-3.6.23.1]# ./configure
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking for style of include used by make... GNU
checking for gcc... gcc
checking for C compiler default output file name... conftest.s
checking whether the C compiler works... configure: error: cannot run C
compiled programs.
If you meant to cross compile, use `--host'.
See `config.log' for more details.
[r...@selkirk sqlite-3.6.23.1]#

And the end of config.log is:
configure:2634: checking for gcc
configure:2650: found /usr/libexec/gcc/i386-redhat-linux/4.1.1//gcc
configure:2661: result: gcc
configure:2899: checking for C compiler version
configure:2907: gcc --version >&5
GNU C version 4.1.1 20070105 (Red Hat 4.1.1-52) (i386-redhat-linux)
compiled by GNU C version 4.1.1 20070105 (Red Hat 4.1.1-52).
GGC heuristics: --param ggc-min-expand=64 --param ggc-min-heapsize=64450
configure:2911: $? = 0
configure:2918: gcc -v >&5
ignoring nonexistent directory
"/usr/lib/gcc/i386-redhat-linux/4.1.1/include"
ignoring nonexistent directory
"/usr/lib/gcc/i386-redhat-linux/4.1.1/../../../..
/i386-redhat-linux/include"
#include "..." search starts here:
#include <...> search starts here:
 /usr/local/include
 /usr/include
End of search list.

Execution times (seconds)
 parser:   0.00 ( 0%) usr   0.00 ( 0%) sys   0.01 (33%)
wall
  22 kB ( 3%) ggc
 TOTAL :   0.00 0.02 0.03
 788 kB
configure:2922: $? = 0
configure:2929: gcc -V >&5
gcc: error: unrecognized command line option "-V"

Execution times (seconds)
 TOTAL :   0.00 0.00 0.00
   0 kB
configure:2933: $? = 1
configure:2956: checking for C compiler default output file name
configure:2978: gccconftest.c  >&5
 main
Execution times (seconds)
 parser:   0.00 ( 0%) usr   0.01 (50%) sys   0.00 ( 0%)
wall
  24 kB ( 3%) ggc
 expand:   0.00 ( 0%) usr   0.00 ( 0%) sys   0.01 (33%)
wall
   5 kB ( 1%) ggc
 TOTAL :   0.00 0.02 0.03
 800 kB
configure:2982: $? = 0
configure:3020: result: conftest.s
configure:3037: checking whether the C compiler works
configure:3047: ./conftest.s
./configure: line 3049: ./conftest.s: Permission denied
configure:3051: $? = 126
configure:3060: error: cannot run C compiled programs.
If you meant to cross compile, use `--host'.
See `config.log' for more details.

##  ##
## Cache variables. ##
##  ##

ac_cv_env_CCC_set=
ac_cv_env_CCC_value=
ac_cv_env_CC_set=
ac_cv_env_CC_value=
ac_cv_env_CFLAGS_set=
ac_cv_env_CFLAGS_value=
ac_cv_env_CPPFLAGS_set=
ac_cv_env_CPPFLAGS_value=
ac_cv_env_CPP_set=
ac_cv_env_CPP_value=
ac_cv_env_CXXCPP_set=
ac_cv_env_CXXCPP_value=
ac_cv_env_CXXFLAGS_set=
ac_cv_env_CXXFLAGS_value=
ac_cv_env_CXX_set=
ac_cv_env_CXX_value=
ac_cv_env_F77_set=
ac_cv_env_F77_value=
ac_cv_env_FFLAGS_set=
ac_cv_env_FFLAGS_value=
ac_cv_env_LDFLAGS_set=
ac_cv_env_LDFLAGS_value=
ac_cv_env_LIBS_set=
ac_cv_env_LIBS_value=
ac_cv_env_build_alias_set=
ac_cv_env_build_alias_value=
ac_cv_env_host_alias_set=
ac_cv_env_host_alias_value=
ac_cv_env_target_alias_set=
ac_cv_env_target_alias_value=
ac_cv_exeext=.s
ac_cv_path_install='/usr/bin/install -c'
ac_cv_prog_AWK=gawk
ac_cv_prog_ac_ct_CC=gcc
ac_cv_prog_make_make_set=yes

## - ##
## Output variables. ##
## - ##

ACLOCAL='${SHELL}
/home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.
6.23.1/missing --run aclocal-1.9'
AMDEPBACKSLASH='\'
AMDEP_FALSE='#'
AMDEP_TRUE=''
AMTAR='${SHELL}
/home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.
23.1/missing --run tar'
AR=''
AUTOCONF='${SHELL}
/home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3
.6.23.1/missing --run autoconf'
AUTOHEADER='${SHELL}
/home/coladmin/kits/sqlite-amalgamation-3.6.2

Re: [sqlite] SELECT behaviour with INDEX

[Simon Slavin]

On 17 Jun 2010, at 2:03pm, Gregoire de Turckheim wrote:

> This might not be the scope of sqlite.org documentation area, but it 
> could be a good idea to have a paper about how indexes work.

Yeah, I really have to write that sometime, don't I ?


On 17 Jun 2010, at 2:44pm, Black, Michael (IS) wrote:

> It seems to me that if you do "SELECT * FROM log WHERE id_clie...@idclient 
> ORDER BY utc DESC LIMIT 1" aren't you better off with two indexes, one for 
> id_client and one for utc?  That way you don't have to worry about order 
> dependence at all.  It seems only if you start doing WHERE clauses on both 
> should you need a compound index.

There is an ideal index for every SELECT.  You start with the fields in the 
WHERE, then the fields in the ORDER BY clause, then because of how SQLite works 
you might want to include the ones you're reporting.

CREATE INDEX client_order ON log (id_client, utc DESC)

would theoretically be the 'perfect' index for that SELECT statement.

> And in this specific case unless you have a boat load of utc's for each 
> client_id the utc index isn't going to buy you much at all.

True.  It's all about clumpiness and subclumpiness.

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

Re: [sqlite] c compiler during install

[Black, Michael (IS)]

You missed this step:
After you "su" you are "root".
 
Then:
yum install gcc
 
Then do your configure and install.  Should work fine.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Glen Scratchley
Sent: Thu 6/17/2010 9:21 AM
To: j...@kreibi.ch; General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install



Thanks for the reply.

I'm not sure what your "as root? Really?" meant, so I tried to install
as a regular user; no joy.:
[colad...@selkirk sqlite-3.6.23.1]$
PATH=$PATH:/usr/libexec/gcc/i386-redhat-linux/4.1.1/  export PATH
[colad...@selkirk sqlite-3.6.23.1]$ which cc1
/usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1
[colad...@selkirk sqlite-3.6.23.1]$ CC=cc1 ./configure
./configure: line 1673: config.log: Permission denied
./configure: line 1683: config.log: Permission denied


[colad...@selkirk sqlite-3.6.23.1]$ su
Password:
[r...@selkirk sqlite-3.6.23.1]#
PATH=$PATH:/usr/libexec/gcc/i386-redhat-linux/4.1.1/  export PATH
[r...@selkirk sqlite-3.6.23.1]# which cc1
/usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1
[r...@selkirk sqlite-3.6.23.1]# CC=cc1 ./configure
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking for style of include used by make... GNU
checking for gcc... cc1
checking for C compiler default output file name... conftest.s
checking whether the C compiler works... configure: error: cannot run C
compiled programs.
If you meant to cross compile, use `--host'.
See `config.log' for more details.
[r...@selkirk sqlite-3.6.23.1]#

So I'm concluding that cc1 is not actually a viable c compiler, as
another poster suggested.


Glen Scratchley
DMS Solutions Architect
Macro 4, Inc.

(973) 526-3900  Direct: (973) 526-3832  Fax: (973) 526-3899
A Division of the UNICOM Group of Companies
www.macro4.com

***
This message (including any attachments) contains confidential
information that is PRIVILEGED, CONFIDENTIAL and/or ATTORNEY WORK
PRODUCT and is intended only for the individual(s) named herein. If you
are not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this email is strictly
prohibited. If you have received this message in error, please notify
the UNICOM Systems, Inc. Postmaster (postmas...@unicomsi.com) of the
error immediately, do not read or use the email and any attachments in
any manner, destroy all copies, and delete it from your system if the
communication was sent via email. UNICOM Systems, Inc. (818) 838-0606
Fax: (818) 838-0776
**


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
Sent: Thursday, June 17, 2010 10:03 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install

On Thu, Jun 17, 2010 at 09:48:15AM -0400, Glen Scratchley scratched on
the wall:
> This is my first post; I have some SQL experience, but I am a complete

> newbie with sqlite
> 
> I didn't get any hits with a search of the faq, so if this has been
> answered, I apologize; please point me to it.
> 
> I am trying to install on RHS 5.  When I run .configure I get this:
> 
> [r...@selkirk sqlite-3.6.23.1]# ./configure

  As root?  Really?

> checking for a BSD-compatible install... /usr/bin/install -c checking
> whether build environment is sane... yes checking for gawk... gawk
> checking whether make sets $(MAKE)... yes checking for style of
> include used by make... GNU checking for gcc... no checking for cc...
> no checking for cl.exe... no
> configure: error: no acceptable C compiler found in $PATH See
> `config.log' for more details.


> I am not a programmer, but I assume that this a c compiler:
> 
> [r...@selkirk sqlite-3.6.23.1]# which cc1
> /usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1

  Assuming that is your C compiler, try this:

# CC=cc1 ./configure


-j

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

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

-

This email has been scanned for all known viruses by the MessageLabs
Email
Security Service and the Macro 4 internal virus protection system.
.


- 
This email has been scanned for all known viruses by 

Re: [sqlite] c compiler during install

[Glen Scratchley]

Thanks for the reply.

I'm not sure what your "as root? Really?" meant, so I tried to install
as a regular user; no joy.:
[colad...@selkirk sqlite-3.6.23.1]$
PATH=$PATH:/usr/libexec/gcc/i386-redhat-linux/4.1.1/  export PATH
[colad...@selkirk sqlite-3.6.23.1]$ which cc1
/usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1
[colad...@selkirk sqlite-3.6.23.1]$ CC=cc1 ./configure
./configure: line 1673: config.log: Permission denied
./configure: line 1683: config.log: Permission denied


[colad...@selkirk sqlite-3.6.23.1]$ su
Password:
[r...@selkirk sqlite-3.6.23.1]#
PATH=$PATH:/usr/libexec/gcc/i386-redhat-linux/4.1.1/  export PATH
[r...@selkirk sqlite-3.6.23.1]# which cc1
/usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1
[r...@selkirk sqlite-3.6.23.1]# CC=cc1 ./configure
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking for style of include used by make... GNU
checking for gcc... cc1
checking for C compiler default output file name... conftest.s
checking whether the C compiler works... configure: error: cannot run C
compiled programs.
If you meant to cross compile, use `--host'.
See `config.log' for more details.
[r...@selkirk sqlite-3.6.23.1]# 

So I'm concluding that cc1 is not actually a viable c compiler, as
another poster suggested.


Glen Scratchley
DMS Solutions Architect
Macro 4, Inc. 

(973) 526-3900  Direct: (973) 526-3832  Fax: (973) 526-3899 
A Division of the UNICOM Group of Companies
www.macro4.com

***
This message (including any attachments) contains confidential
information that is PRIVILEGED, CONFIDENTIAL and/or ATTORNEY WORK
PRODUCT and is intended only for the individual(s) named herein. If you
are not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this email is strictly
prohibited. If you have received this message in error, please notify
the UNICOM Systems, Inc. Postmaster (postmas...@unicomsi.com) of the
error immediately, do not read or use the email and any attachments in
any manner, destroy all copies, and delete it from your system if the
communication was sent via email. UNICOM Systems, Inc. (818) 838-0606
Fax: (818) 838-0776
** 


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
Sent: Thursday, June 17, 2010 10:03 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install

On Thu, Jun 17, 2010 at 09:48:15AM -0400, Glen Scratchley scratched on
the wall:
> This is my first post; I have some SQL experience, but I am a complete

> newbie with sqlite
>  
> I didn't get any hits with a search of the faq, so if this has been 
> answered, I apologize; please point me to it.
>  
> I am trying to install on RHS 5.  When I run .configure I get this:
>  
> [r...@selkirk sqlite-3.6.23.1]# ./configure

  As root?  Really?

> checking for a BSD-compatible install... /usr/bin/install -c checking 
> whether build environment is sane... yes checking for gawk... gawk 
> checking whether make sets $(MAKE)... yes checking for style of 
> include used by make... GNU checking for gcc... no checking for cc... 
> no checking for cl.exe... no
> configure: error: no acceptable C compiler found in $PATH See 
> `config.log' for more details.


> I am not a programmer, but I assume that this a c compiler:
>  
> [r...@selkirk sqlite-3.6.23.1]# which cc1
> /usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1

  Assuming that is your C compiler, try this:

# CC=cc1 ./configure


-j

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

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

-

This email has been scanned for all known viruses by the MessageLabs
Email
Security Service and the Macro 4 internal virus protection system.
.


- 
This email has been scanned for all known viruses by the MessageLabs Email
Security Service and the Macro 4 internal virus protection system.
. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c compiler during install

[Glen Scratchley]

Thanks.

It doesn't appear that I have the necessary files to install gcc:

[r...@selkirk 4.1.1]# gcc -v
bash: gcc: command not found
[r...@selkirk 4.1.1]# yum install gcc
Loading "installonlyn" plugin
Loading "rhnplugin" plugin
There was an error parsing the RHN proxy settings.
RHN support will be disabled.
Setting up Install Process
Setting up repositories
No Repositories Available to Set Up
Reading repository metadata in from local files
Parsing package install arguments
Setting up repositories
No Repositories Available to Set Up
Reading repository metadata in from local files
No Match for argument: gcc
Nothing to do
[r...@selkirk 4.1.1]#

Should I get this from RHS (I don't have install CDs)? 


Glen Scratchley
DMS Solutions Architect
Macro 4, Inc. 

(973) 526-3900  Direct: (973) 526-3832  Fax: (973) 526-3899 
A Division of the UNICOM Group of Companies
www.macro4.com

***
This message (including any attachments) contains confidential
information that is PRIVILEGED, CONFIDENTIAL and/or ATTORNEY WORK
PRODUCT and is intended only for the individual(s) named herein. If you
are not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this email is strictly
prohibited. If you have received this message in error, please notify
the UNICOM Systems, Inc. Postmaster (postmas...@unicomsi.com) of the
error immediately, do not read or use the email and any attachments in
any manner, destroy all copies, and delete it from your system if the
communication was sent via email. UNICOM Systems, Inc. (818) 838-0606
Fax: (818) 838-0776
** 


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael
(IS)
Sent: Thursday, June 17, 2010 10:07 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install

You probably don't have the gcc package installed.  You're seeing an
i386 compiler which may not produce what you want plus you won't
necessarily have all the header files you need.  And did you add that
directory path to cc1 yourself to your PATH?  It's quite non-standard
and should be removed from your path.
 
You SHOULD be able to do this:
gcc -v
 
If not, as root, install it using this;
 
yum install gcc
 
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
Sent: Thu 6/17/2010 8:58 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install



> I am not a programmer, but I assume that this a c compiler:
>
> [r...@selkirk sqlite-3.6.23.1]# which cc1
> /usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1
> [r...@selkirk sqlite-3.6.23.1]# echo $PATH 
> /usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/s
> bi
> n:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/libexec/gcc/i386-redhat-linux
> /4
> .1.1

Are you sure that it's compiler? If it is then it's a very non-standard
unusual name. gcc usually named exactly like that 'gcc'
and probably it's in /usr/libexec/gcc/i386-redhat-linux/4.1.1/bin/.
But if there's nothing there and 'cc1 --version' outputs that it's
indeed GNU C Compiler then you can make an additional link to it nearby
and name it 'gcc'.


Pavel

On Thu, Jun 17, 2010 at 9:48 AM, Glen Scratchley
 wrote:
> This is my first post; I have some SQL experience, but I am a complete

> newbie with sqlite
>
> I didn't get any hits with a search of the faq, so if this has been 
> answered, I apologize; please point me to it.
>
> I am trying to install on RHS 5.  When I run .configure I get this:
>
> [r...@selkirk sqlite-3.6.23.1]# ./configure checking for a 
> BSD-compatible install... /usr/bin/install -c checking whether build 
> environment is sane... yes checking for gawk... gawk checking whether 
> make sets $(MAKE)... yes checking for style of include used by make...

> GNU checking for gcc... no checking for cc... no checking for 
> cl.exe... no
> configure: error: no acceptable C compiler found in $PATH See 
> `config.log' for more details.
>
> and the entire config.log is:
>
> hostname = selkirk
> uname -m = i686
> uname -r = 2.6.18-8.el5
> uname -s = Linux
> uname -v = #1 SMP Fri Jan 26 14:15:21 EST 2007
>
> /usr/bin/uname -p = unknown
> /bin/uname -X = unknown
>
> /bin/arch  = i686
> /usr/bin/arch -k   = unknown
> /usr/convex/getsysinfo = unknown
> /usr/bin/hostinfo  = unknown
> /bin/machine   = unknown
> /usr/bin/oslevel   = unknown
> /bin/universe  = unknown
>
> PATH: /usr/kerberos/sbin
> PATH: /usr/kerberos/bin
> PATH: /usr/local/sbin
> PATH: /usr/local/bin
> PATH: /sbin
> PATH: /bin
> PATH: /usr/sbin
> PATH: /usr/bin
> PATH: /root/bin
> PATH: /usr/libexec/gcc/i386-redhat-linux/4.1.1
>
>
> ## --- ##
> ## Core tests. ##
> ## -

Re: [sqlite] SELECT behaviour with INDEX

[Black, Michael (IS)]

I sense the beginning of a "How to use indexes" page.
 
Things you need know:
Only one index is used per query (or insert??).  So multiple indexes will 
not do you any good for a single select (you might still need them for each 
different WHERE or ORDER you do though).
Recommendations
Index order is important, "SELECT * FROM table WHERE a='a' ORDER BY b"; 
should have "CREATE INDEX i ON TABLE(a,b)" and not (b,a).  If you also reversed 
the WHERE and ORDER clause (doing both selects) you would want both indexes.
 
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
Sent: Thu 6/17/2010 8:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SELECT behaviour with INDEX



> Is there any advantage/disadvantage to having seperate indexes in a case like 
> this?

SQLite uses at maximum one index per table per query. So there is a
big disadvantage in having separate indexes especially if @IdClient is
not selective enough.


Pavel

On Thu, Jun 17, 2010 at 9:44 AM, Black, Michael (IS)
 wrote:
> Is there any advantage/disadvantage to having seperate indexes in a case like 
> this?
>
> In other words, as a simple design goal to have a separate index that matches 
> whatever you select on and order on?
>
> It seems to me that if you do "SELECT * FROM log WHERE id_clie...@idclient 
> ORDER BY utc DESC LIMIT 1" aren't you better off with two indexes, one for 
> id_client and one for utc?  That way you don't have to worry about order 
> dependence at all.  It seems only if you start doing WHERE clauses on both 
> should you need a compound index.
>
> And in this specific case unless you have a boat load of utc's for each 
> client_id the utc index isn't going to buy you much at all.
>
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
> Sent: Thu 6/17/2010 7:40 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SELECT behaviour with INDEX
>
>
>
>> It seems to me the DB engine tries to perform an unindexed search if the
>> requested value is not present in the index.
>> Is this behaviour intended ? How can I get rid of it ?
>
> If your index is in that very order as you said, i.e. (utc,
> id_client), then whatever @IdClient you put in the query SQLite will
> perform full index scan because it can benefit from index only for the
> purpose of ordering but not for the purpose of searching. You've just
> got lucky having LIMIT 1 in the query and providing @IdClient that has
> some utc close to maximum. If you provide @IdClient that has utc close
> to minimum then you'll get long execution time again. But if you
> change your index to (id_client, utc) then you'll have fast times for
> any @IdClient.
>
>
> Pavel
>
> On Thu, Jun 17, 2010 at 8:32 AM, Gregoire de Turckheim
>  wrote:
>> Hi,
>>
>> I'm noticing a strange behaviour with SELECT statements.
>>
>> Let's consider this statement :
>>
>> SELECT * FROM log
>> WHERE id_clie...@idclient
>> ORDER BY utc DESC
>> LIMIT 1
>>
>> The table has an index on the "utc" and "id_client" fields pair.
>>
>> When running this query with an @IdClient value which exists in the
>> table, the query returns instantly (< 200ms)
>> When running this query with an @IdClient value which doesn't exist in
>> the table, the query is slow (2,63s)
>> When running this query with an @IdClient value which exists in the
>> table and the "NOT INDEXED" keywords, the query is slow too (2,84s)
>>
>> It seems to me the DB engine tries to perform an unindexed search if the
>> requested value is not present in the index.
>> Is this behaviour intended ? How can I get rid of it ?
>>
>> Regards,
>>
>> --
>> Grégoire de Turckheim-  TAVEO
>> Gérant   -  www.taveo.com
>> Mob: 06 26 92 40 02  -  Tel: 09 50 58 40 02
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
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] c compiler during install

[Black, Michael (IS)]

You probably don't have the gcc package installed.  You're seeing an i386 
compiler which may not produce what you want plus you won't necessarily have 
all the header files you need.  And did you add that directory path to cc1 
yourself to your PATH?  It's quite non-standard and should be removed from your 
path.
 
You SHOULD be able to do this:
gcc -v
 
If not, as root, install it using this;
 
yum install gcc
 
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
Sent: Thu 6/17/2010 8:58 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install



> I am not a programmer, but I assume that this a c compiler:
>
> [r...@selkirk sqlite-3.6.23.1]# which cc1
> /usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1
> [r...@selkirk sqlite-3.6.23.1]# echo $PATH
> /usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbi
> n:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/libexec/gcc/i386-redhat-linux/4
> .1.1

Are you sure that it's compiler? If it is then it's a very
non-standard unusual name. gcc usually named exactly like that 'gcc'
and probably it's in /usr/libexec/gcc/i386-redhat-linux/4.1.1/bin/.
But if there's nothing there and 'cc1 --version' outputs that it's
indeed GNU C Compiler then you can make an additional link to it
nearby and name it 'gcc'.


Pavel

On Thu, Jun 17, 2010 at 9:48 AM, Glen Scratchley
 wrote:
> This is my first post; I have some SQL experience, but I am a complete
> newbie with sqlite
>
> I didn't get any hits with a search of the faq, so if this has been
> answered, I apologize; please point me to it.
>
> I am trying to install on RHS 5.  When I run .configure I get this:
>
> [r...@selkirk sqlite-3.6.23.1]# ./configure
> checking for a BSD-compatible install... /usr/bin/install -c
> checking whether build environment is sane... yes
> checking for gawk... gawk
> checking whether make sets $(MAKE)... yes
> checking for style of include used by make... GNU
> checking for gcc... no
> checking for cc... no
> checking for cl.exe... no
> configure: error: no acceptable C compiler found in $PATH
> See `config.log' for more details.
>
> and the entire config.log is:
>
> hostname = selkirk
> uname -m = i686
> uname -r = 2.6.18-8.el5
> uname -s = Linux
> uname -v = #1 SMP Fri Jan 26 14:15:21 EST 2007
>
> /usr/bin/uname -p = unknown
> /bin/uname -X = unknown
>
> /bin/arch  = i686
> /usr/bin/arch -k   = unknown
> /usr/convex/getsysinfo = unknown
> /usr/bin/hostinfo  = unknown
> /bin/machine   = unknown
> /usr/bin/oslevel   = unknown
> /bin/universe  = unknown
>
> PATH: /usr/kerberos/sbin
> PATH: /usr/kerberos/bin
> PATH: /usr/local/sbin
> PATH: /usr/local/bin
> PATH: /sbin
> PATH: /bin
> PATH: /usr/sbin
> PATH: /usr/bin
> PATH: /root/bin
> PATH: /usr/libexec/gcc/i386-redhat-linux/4.1.1
>
>
> ## --- ##
> ## Core tests. ##
> ## --- ##
>
> configure:2092: checking for a BSD-compatible install
> configure:2160: result: /usr/bin/install -c
> configure:2171: checking whether build environment is sane
> configure:2214: result: yes
> configure:2276: checking for gawk
> configure:2292: found /bin/gawk
> configure:2303: result: gawk
> configure:2314: checking whether make sets $(MAKE)
> configure:2336: result: yes
> configure:2533: checking for style of include used by make
> configure:2561: result: GNU
> configure:2634: checking for gcc
> configure:2664: result: no
> configure:2731: checking for cc
> configure:2778: result: no
> configure:2834: checking for cl.exe
> configure:2864: result: no
> configure:2893: error: no acceptable C compiler found in $PATH
> See `config.log' for more details.
>
> ##  ##
> ## Cache variables. ##
> ##  ##
>
> ac_cv_env_CCC_set=
> ac_cv_env_CCC_value=
> ac_cv_env_CC_set=
> ac_cv_env_CC_value=
> ac_cv_env_CFLAGS_set=
> ac_cv_env_CFLAGS_value=
> ac_cv_env_CPPFLAGS_set=
> ac_cv_env_CPPFLAGS_value=
> ac_cv_env_CPP_set=
> ac_cv_env_CPP_value=
> ac_cv_env_CXXCPP_set=
> ac_cv_env_CXXCPP_value=
> ac_cv_env_CXXFLAGS_set=
> ac_cv_env_CXXFLAGS_value=
> ac_cv_env_CXX_set=
> ac_cv_env_CXX_value=
> ac_cv_env_F77_set=
> ac_cv_env_F77_value=
> ac_cv_env_FFLAGS_set=
> ac_cv_env_FFLAGS_value=
> ac_cv_env_LDFLAGS_set=
> ac_cv_env_LDFLAGS_value=
> ac_cv_env_LIBS_set=
> ac_cv_env_LIBS_value=
> ac_cv_env_build_alias_set=
> ac_cv_env_build_alias_value=
> ac_cv_env_host_alias_set=
> ac_cv_env_host_alias_value=
> ac_cv_env_target_alias_set=
> ac_cv_env_target_alias_value=
> ac_cv_path_install='/usr/bin/install -c'
> ac_cv_prog_AWK=gawk
> ac_cv_prog_make_make_set=yes
>
> ## - ##
> ## Output variables. ##
> ## - ##
>
> ACLOCAL='${SHELL}
> /home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
> sing --run aclocal-1.9'
> AMDEPBACKSLASH='\'
> AMDEP_FALSE='#'
> AMDEP_TRUE=''
> AMTAR='${SHELL}
> /home/coladmin/kits/

Re: [sqlite] c compiler during install

[Glen Scratchley]

 thanks for the reply.  There is nothing in that directory other than that 
executable:
[colad...@selkirk 4.1.1]$ ls -la
total 5156
drwxr-xr-x 2 root root4096 Aug  8  2008 .
drwxr-xr-x 3 root root4096 Aug  8  2008 ..
-rwxr-xr-x 1 root root 5246768 Jan 17  2007 cc1

As I said, I'm not a c programmer; however, it appears to be a compiler.

[colad...@selkirk 4.1.1]$ ./cc1 --version
GNU C version 4.1.1 20070105 (Red Hat 4.1.1-52) (i386-redhat-linux)
compiled by GNU C version 4.1.1 20070105 (Red Hat 4.1.1-52).
GGC heuristics: --param ggc-min-expand=64 --param ggc-min-heapsize=64450
[colad...@selkirk 4.1.1]$

I have no explanation for the odd name; this system was originally built by 
someone else (not that that would make a lot of difference)

I'll try creating a link as you suggested.

Glen Scratchley
DMS Solutions Architect
Macro 4, Inc. 

(973) 526-3900  Direct: (973) 526-3832  Fax: (973) 526-3899 
A Division of the UNICOM Group of Companies
www.macro4.com

***
This message (including any attachments) contains confidential information that 
is PRIVILEGED, CONFIDENTIAL and/or ATTORNEY WORK PRODUCT and is intended only 
for the individual(s) named herein. If you are not the intended recipient, you 
are hereby notified that any dissemination, distribution or copying of this 
email is strictly prohibited. If you have received this message in error, 
please notify the UNICOM Systems, Inc. Postmaster (postmas...@unicomsi.com) of 
the error immediately, do not read or use the email and any attachments in any 
manner, destroy all copies, and delete it from your system if the communication 
was sent via email. UNICOM Systems, Inc. (818) 838-0606 Fax: (818) 838-0776
** 


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Thursday, June 17, 2010 9:59 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] c compiler during install

> I am not a programmer, but I assume that this a c compiler:
>
> [r...@selkirk sqlite-3.6.23.1]# which cc1
> /usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1
> [r...@selkirk sqlite-3.6.23.1]# echo $PATH 
> /usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/s
> bi
> n:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/libexec/gcc/i386-redhat-linux
> /4
> .1.1

Are you sure that it's compiler? If it is then it's a very non-standard unusual 
name. gcc usually named exactly like that 'gcc'
and probably it's in /usr/libexec/gcc/i386-redhat-linux/4.1.1/bin/.
But if there's nothing there and 'cc1 --version' outputs that it's indeed GNU C 
Compiler then you can make an additional link to it nearby and name it 'gcc'.


Pavel

On Thu, Jun 17, 2010 at 9:48 AM, Glen Scratchley  
wrote:
> This is my first post; I have some SQL experience, but I am a complete 
> newbie with sqlite
>
> I didn't get any hits with a search of the faq, so if this has been 
> answered, I apologize; please point me to it.
>
> I am trying to install on RHS 5.  When I run .configure I get this:
>
> [r...@selkirk sqlite-3.6.23.1]# ./configure checking for a 
> BSD-compatible install... /usr/bin/install -c checking whether build 
> environment is sane... yes checking for gawk... gawk checking whether 
> make sets $(MAKE)... yes checking for style of include used by make... 
> GNU checking for gcc... no checking for cc... no checking for 
> cl.exe... no
> configure: error: no acceptable C compiler found in $PATH See 
> `config.log' for more details.
>
> and the entire config.log is:
>
> hostname = selkirk
> uname -m = i686
> uname -r = 2.6.18-8.el5
> uname -s = Linux
> uname -v = #1 SMP Fri Jan 26 14:15:21 EST 2007
>
> /usr/bin/uname -p = unknown
> /bin/uname -X     = unknown
>
> /bin/arch              = i686
> /usr/bin/arch -k       = unknown
> /usr/convex/getsysinfo = unknown
> /usr/bin/hostinfo      = unknown
> /bin/machine           = unknown
> /usr/bin/oslevel       = unknown
> /bin/universe          = unknown
>
> PATH: /usr/kerberos/sbin
> PATH: /usr/kerberos/bin
> PATH: /usr/local/sbin
> PATH: /usr/local/bin
> PATH: /sbin
> PATH: /bin
> PATH: /usr/sbin
> PATH: /usr/bin
> PATH: /root/bin
> PATH: /usr/libexec/gcc/i386-redhat-linux/4.1.1
>
>
> ## --- ##
> ## Core tests. ##
> ## --- ##
>
> configure:2092: checking for a BSD-compatible install
> configure:2160: result: /usr/bin/install -c
> configure:2171: checking whether build environment is sane
> configure:2214: result: yes
> configure:2276: checking for gawk
> configure:2292: found /bin/gawk
> configure:2303: result: gawk
> configure:2314: checking whether make sets $(MAKE)
> configure:2336: result: yes
> configure:2533: checking for style of include used by make
> configure:2561: result: GNU
> configure:2634: checking for gcc
> configure:2664: result: no
> configure:2731: checking for cc
> configure:2778: res

Re: [sqlite] c compiler during install

[Jay A. Kreibich]

On Thu, Jun 17, 2010 at 09:48:15AM -0400, Glen Scratchley scratched on the wall:
> This is my first post; I have some SQL experience, but I am a complete
> newbie with sqlite
>  
> I didn't get any hits with a search of the faq, so if this has been
> answered, I apologize; please point me to it.
>  
> I am trying to install on RHS 5.  When I run .configure I get this:
>  
> [r...@selkirk sqlite-3.6.23.1]# ./configure

  As root?  Really?

> checking for a BSD-compatible install... /usr/bin/install -c
> checking whether build environment is sane... yes
> checking for gawk... gawk
> checking whether make sets $(MAKE)... yes
> checking for style of include used by make... GNU
> checking for gcc... no
> checking for cc... no
> checking for cl.exe... no
> configure: error: no acceptable C compiler found in $PATH
> See `config.log' for more details.


> I am not a programmer, but I assume that this a c compiler:
>  
> [r...@selkirk sqlite-3.6.23.1]# which cc1
> /usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1

  Assuming that is your C compiler, try this:

# CC=cc1 ./configure


-j

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

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

Re: [sqlite] c compiler during install

[Pavel Ivanov]

> I am not a programmer, but I assume that this a c compiler:
>
> [r...@selkirk sqlite-3.6.23.1]# which cc1
> /usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1
> [r...@selkirk sqlite-3.6.23.1]# echo $PATH
> /usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbi
> n:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/libexec/gcc/i386-redhat-linux/4
> .1.1

Are you sure that it's compiler? If it is then it's a very
non-standard unusual name. gcc usually named exactly like that 'gcc'
and probably it's in /usr/libexec/gcc/i386-redhat-linux/4.1.1/bin/.
But if there's nothing there and 'cc1 --version' outputs that it's
indeed GNU C Compiler then you can make an additional link to it
nearby and name it 'gcc'.


Pavel

On Thu, Jun 17, 2010 at 9:48 AM, Glen Scratchley
 wrote:
> This is my first post; I have some SQL experience, but I am a complete
> newbie with sqlite
>
> I didn't get any hits with a search of the faq, so if this has been
> answered, I apologize; please point me to it.
>
> I am trying to install on RHS 5.  When I run .configure I get this:
>
> [r...@selkirk sqlite-3.6.23.1]# ./configure
> checking for a BSD-compatible install... /usr/bin/install -c
> checking whether build environment is sane... yes
> checking for gawk... gawk
> checking whether make sets $(MAKE)... yes
> checking for style of include used by make... GNU
> checking for gcc... no
> checking for cc... no
> checking for cl.exe... no
> configure: error: no acceptable C compiler found in $PATH
> See `config.log' for more details.
>
> and the entire config.log is:
>
> hostname = selkirk
> uname -m = i686
> uname -r = 2.6.18-8.el5
> uname -s = Linux
> uname -v = #1 SMP Fri Jan 26 14:15:21 EST 2007
>
> /usr/bin/uname -p = unknown
> /bin/uname -X     = unknown
>
> /bin/arch              = i686
> /usr/bin/arch -k       = unknown
> /usr/convex/getsysinfo = unknown
> /usr/bin/hostinfo      = unknown
> /bin/machine           = unknown
> /usr/bin/oslevel       = unknown
> /bin/universe          = unknown
>
> PATH: /usr/kerberos/sbin
> PATH: /usr/kerberos/bin
> PATH: /usr/local/sbin
> PATH: /usr/local/bin
> PATH: /sbin
> PATH: /bin
> PATH: /usr/sbin
> PATH: /usr/bin
> PATH: /root/bin
> PATH: /usr/libexec/gcc/i386-redhat-linux/4.1.1
>
>
> ## --- ##
> ## Core tests. ##
> ## --- ##
>
> configure:2092: checking for a BSD-compatible install
> configure:2160: result: /usr/bin/install -c
> configure:2171: checking whether build environment is sane
> configure:2214: result: yes
> configure:2276: checking for gawk
> configure:2292: found /bin/gawk
> configure:2303: result: gawk
> configure:2314: checking whether make sets $(MAKE)
> configure:2336: result: yes
> configure:2533: checking for style of include used by make
> configure:2561: result: GNU
> configure:2634: checking for gcc
> configure:2664: result: no
> configure:2731: checking for cc
> configure:2778: result: no
> configure:2834: checking for cl.exe
> configure:2864: result: no
> configure:2893: error: no acceptable C compiler found in $PATH
> See `config.log' for more details.
>
> ##  ##
> ## Cache variables. ##
> ##  ##
>
> ac_cv_env_CCC_set=
> ac_cv_env_CCC_value=
> ac_cv_env_CC_set=
> ac_cv_env_CC_value=
> ac_cv_env_CFLAGS_set=
> ac_cv_env_CFLAGS_value=
> ac_cv_env_CPPFLAGS_set=
> ac_cv_env_CPPFLAGS_value=
> ac_cv_env_CPP_set=
> ac_cv_env_CPP_value=
> ac_cv_env_CXXCPP_set=
> ac_cv_env_CXXCPP_value=
> ac_cv_env_CXXFLAGS_set=
> ac_cv_env_CXXFLAGS_value=
> ac_cv_env_CXX_set=
> ac_cv_env_CXX_value=
> ac_cv_env_F77_set=
> ac_cv_env_F77_value=
> ac_cv_env_FFLAGS_set=
> ac_cv_env_FFLAGS_value=
> ac_cv_env_LDFLAGS_set=
> ac_cv_env_LDFLAGS_value=
> ac_cv_env_LIBS_set=
> ac_cv_env_LIBS_value=
> ac_cv_env_build_alias_set=
> ac_cv_env_build_alias_value=
> ac_cv_env_host_alias_set=
> ac_cv_env_host_alias_value=
> ac_cv_env_target_alias_set=
> ac_cv_env_target_alias_value=
> ac_cv_path_install='/usr/bin/install -c'
> ac_cv_prog_AWK=gawk
> ac_cv_prog_make_make_set=yes
>
> ## - ##
> ## Output variables. ##
> ## - ##
>
> ACLOCAL='${SHELL}
> /home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
> sing --run aclocal-1.9'
> AMDEPBACKSLASH='\'
> AMDEP_FALSE='#'
> AMDEP_TRUE=''
> AMTAR='${SHELL}
> /home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
> sing --run tar'
> AR=''
> AUTOCONF='${SHELL}
> /home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
> sing --run autoconf'
> AUTOHEADER='${SHELL}
> /home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
> sing --run autoheader'
> AUTOMAKE='${SHELL}
> /home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
> sing --run automake-1.9'
> AWK='gawk'
> BUILD_CFLAGS=''
> CC=''
> CCDEPMODE=''
> CFLAGS=''
> CPP=''
> CPPFLAGS=''
> CXX=''
> CXXCPP=''
> CXXDEPMODE=''
> CXXFLAGS=''
> CYGPATH_W='echo'
> DEFS=''
> DEPDIR='.deps'
> DYNAMIC_EXTENSION_FLAGS=''
> ECHO='echo'
> ECHO_C=''
> ECHO_N='-n'
> ECHO_T=''

Re: [sqlite] SELECT behaviour with INDEX

[Pavel Ivanov]

> Is there any advantage/disadvantage to having seperate indexes in a case like 
> this?

SQLite uses at maximum one index per table per query. So there is a
big disadvantage in having separate indexes especially if @IdClient is
not selective enough.


Pavel

On Thu, Jun 17, 2010 at 9:44 AM, Black, Michael (IS)
 wrote:
> Is there any advantage/disadvantage to having seperate indexes in a case like 
> this?
>
> In other words, as a simple design goal to have a separate index that matches 
> whatever you select on and order on?
>
> It seems to me that if you do "SELECT * FROM log WHERE id_clie...@idclient 
> ORDER BY utc DESC LIMIT 1" aren't you better off with two indexes, one for 
> id_client and one for utc?  That way you don't have to worry about order 
> dependence at all.  It seems only if you start doing WHERE clauses on both 
> should you need a compound index.
>
> And in this specific case unless you have a boat load of utc's for each 
> client_id the utc index isn't going to buy you much at all.
>
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
> Sent: Thu 6/17/2010 7:40 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SELECT behaviour with INDEX
>
>
>
>> It seems to me the DB engine tries to perform an unindexed search if the
>> requested value is not present in the index.
>> Is this behaviour intended ? How can I get rid of it ?
>
> If your index is in that very order as you said, i.e. (utc,
> id_client), then whatever @IdClient you put in the query SQLite will
> perform full index scan because it can benefit from index only for the
> purpose of ordering but not for the purpose of searching. You've just
> got lucky having LIMIT 1 in the query and providing @IdClient that has
> some utc close to maximum. If you provide @IdClient that has utc close
> to minimum then you'll get long execution time again. But if you
> change your index to (id_client, utc) then you'll have fast times for
> any @IdClient.
>
>
> Pavel
>
> On Thu, Jun 17, 2010 at 8:32 AM, Gregoire de Turckheim
>  wrote:
>> Hi,
>>
>> I'm noticing a strange behaviour with SELECT statements.
>>
>> Let's consider this statement :
>>
>> SELECT * FROM log
>> WHERE id_clie...@idclient
>> ORDER BY utc DESC
>> LIMIT 1
>>
>> The table has an index on the "utc" and "id_client" fields pair.
>>
>> When running this query with an @IdClient value which exists in the
>> table, the query returns instantly (< 200ms)
>> When running this query with an @IdClient value which doesn't exist in
>> the table, the query is slow (2,63s)
>> When running this query with an @IdClient value which exists in the
>> table and the "NOT INDEXED" keywords, the query is slow too (2,84s)
>>
>> It seems to me the DB engine tries to perform an unindexed search if the
>> requested value is not present in the index.
>> Is this behaviour intended ? How can I get rid of it ?
>>
>> Regards,
>>
>> --
>> Grégoire de Turckheim    -  TAVEO
>> Gérant                   -  www.taveo.com
>> Mob: 06 26 92 40 02      -  Tel: 09 50 58 40 02
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] c compiler during install

[Glen Scratchley]

This is my first post; I have some SQL experience, but I am a complete
newbie with sqlite
 
I didn't get any hits with a search of the faq, so if this has been
answered, I apologize; please point me to it.
 
I am trying to install on RHS 5.  When I run .configure I get this:
 
[r...@selkirk sqlite-3.6.23.1]# ./configure
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking for style of include used by make... GNU
checking for gcc... no
checking for cc... no
checking for cl.exe... no
configure: error: no acceptable C compiler found in $PATH
See `config.log' for more details.

and the entire config.log is:

hostname = selkirk
uname -m = i686
uname -r = 2.6.18-8.el5
uname -s = Linux
uname -v = #1 SMP Fri Jan 26 14:15:21 EST 2007
 
/usr/bin/uname -p = unknown
/bin/uname -X = unknown
 
/bin/arch  = i686
/usr/bin/arch -k   = unknown
/usr/convex/getsysinfo = unknown
/usr/bin/hostinfo  = unknown
/bin/machine   = unknown
/usr/bin/oslevel   = unknown
/bin/universe  = unknown
 
PATH: /usr/kerberos/sbin
PATH: /usr/kerberos/bin
PATH: /usr/local/sbin
PATH: /usr/local/bin
PATH: /sbin
PATH: /bin
PATH: /usr/sbin
PATH: /usr/bin
PATH: /root/bin
PATH: /usr/libexec/gcc/i386-redhat-linux/4.1.1
 

## --- ##
## Core tests. ##
## --- ##
 
configure:2092: checking for a BSD-compatible install
configure:2160: result: /usr/bin/install -c
configure:2171: checking whether build environment is sane
configure:2214: result: yes
configure:2276: checking for gawk
configure:2292: found /bin/gawk
configure:2303: result: gawk
configure:2314: checking whether make sets $(MAKE)
configure:2336: result: yes
configure:2533: checking for style of include used by make
configure:2561: result: GNU
configure:2634: checking for gcc
configure:2664: result: no
configure:2731: checking for cc
configure:2778: result: no
configure:2834: checking for cl.exe
configure:2864: result: no
configure:2893: error: no acceptable C compiler found in $PATH
See `config.log' for more details.
 
##  ##
## Cache variables. ##
##  ##
 
ac_cv_env_CCC_set=
ac_cv_env_CCC_value=
ac_cv_env_CC_set=
ac_cv_env_CC_value=
ac_cv_env_CFLAGS_set=
ac_cv_env_CFLAGS_value=
ac_cv_env_CPPFLAGS_set=
ac_cv_env_CPPFLAGS_value=
ac_cv_env_CPP_set=
ac_cv_env_CPP_value=
ac_cv_env_CXXCPP_set=
ac_cv_env_CXXCPP_value=
ac_cv_env_CXXFLAGS_set=
ac_cv_env_CXXFLAGS_value=
ac_cv_env_CXX_set=
ac_cv_env_CXX_value=
ac_cv_env_F77_set=
ac_cv_env_F77_value=
ac_cv_env_FFLAGS_set=
ac_cv_env_FFLAGS_value=
ac_cv_env_LDFLAGS_set=
ac_cv_env_LDFLAGS_value=
ac_cv_env_LIBS_set=
ac_cv_env_LIBS_value=
ac_cv_env_build_alias_set=
ac_cv_env_build_alias_value=
ac_cv_env_host_alias_set=
ac_cv_env_host_alias_value=
ac_cv_env_target_alias_set=
ac_cv_env_target_alias_value=
ac_cv_path_install='/usr/bin/install -c'
ac_cv_prog_AWK=gawk
ac_cv_prog_make_make_set=yes
 
## - ##
## Output variables. ##
## - ##
 
ACLOCAL='${SHELL}
/home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
sing --run aclocal-1.9'
AMDEPBACKSLASH='\'
AMDEP_FALSE='#'
AMDEP_TRUE=''
AMTAR='${SHELL}
/home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
sing --run tar'
AR=''
AUTOCONF='${SHELL}
/home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
sing --run autoconf'
AUTOHEADER='${SHELL}
/home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
sing --run autoheader'
AUTOMAKE='${SHELL}
/home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
sing --run automake-1.9'
AWK='gawk'
BUILD_CFLAGS=''
CC=''
CCDEPMODE=''
CFLAGS=''
CPP=''
CPPFLAGS=''
CXX=''
CXXCPP=''
CXXDEPMODE=''
CXXFLAGS=''
CYGPATH_W='echo'
DEFS=''
DEPDIR='.deps'
DYNAMIC_EXTENSION_FLAGS=''
ECHO='echo'
ECHO_C=''
ECHO_N='-n'
ECHO_T=''
EGREP=''
EXEEXT=''
F77=''
FFLAGS=''
GREP=''
INSTALL_DATA='${INSTALL} -m 644'
INSTALL_PROGRAM='${INSTALL}'
INSTALL_SCRIPT='${INSTALL}'
INSTALL_STRIP_PROGRAM='${SHELL} $(install_sh) -c -s'
LDFLAGS=''
LIBOBJS=''
LIBS=''
LIBTOOL=''
LN_S=''
LTLIBOBJS=''
MAKEINFO='${SHELL}
/home/coladmin/kits/sqlite-amalgamation-3.6.23.1.tar/sqlite-3.6.23.1/mis
sing --run makeinfo'
OBJEXT=''
PACKAGE='sqlite'
PACKAGE_BUGREPORT='http://www.sqlite.org'
PACKAGE_NAME='sqlite'
PACKAGE_STRING='sqlite 3.6.23.1'
PACKAGE_TARNAME='sqlite'
PACKAGE_VERSION='3.6.23.1'
PATH_SEPARATOR=':'
RANLIB=''
READLINE_LIBS=''
SET_MAKE=''
SHELL='/bin/sh'
STRIP=''
THREADSAFE_FLAGS=''
VERSION='3.6.23.1'
ac_ct_CC=''
ac_ct_CXX=''
ac_ct_F77=''
am__fastdepCC_FALSE=''
am__fastdepCC_TRUE=''
am__fastdepCXX_FALSE=''
am__fastdepCXX_TRUE=''
am__include='include'
am__leading_dot='.'
am__quote=''
am__tar='${AMTAR} chof - "$$tardir"'
am__untar='${AMTAR} xf -'
bindir='${exec_prefix}/bin'
build=''
build_alias=''
build_cpu=''
build_os=''
build_vendor=''
datadir='${datarootdir}'
datarootdir='${prefix}/share'
docdi

Re: [sqlite] SELECT behaviour with INDEX

[Black, Michael (IS)]

Is there any advantage/disadvantage to having seperate indexes in a case like 
this?
 
In other words, as a simple design goal to have a separate index that matches 
whatever you select on and order on?
 
It seems to me that if you do "SELECT * FROM log WHERE id_clie...@idclient 
ORDER BY utc DESC LIMIT 1" aren't you better off with two indexes, one for 
id_client and one for utc?  That way you don't have to worry about order 
dependence at all.  It seems only if you start doing WHERE clauses on both 
should you need a compound index.
 
And in this specific case unless you have a boat load of utc's for each 
client_id the utc index isn't going to buy you much at all.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
Sent: Thu 6/17/2010 7:40 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SELECT behaviour with INDEX



> It seems to me the DB engine tries to perform an unindexed search if the
> requested value is not present in the index.
> Is this behaviour intended ? How can I get rid of it ?

If your index is in that very order as you said, i.e. (utc,
id_client), then whatever @IdClient you put in the query SQLite will
perform full index scan because it can benefit from index only for the
purpose of ordering but not for the purpose of searching. You've just
got lucky having LIMIT 1 in the query and providing @IdClient that has
some utc close to maximum. If you provide @IdClient that has utc close
to minimum then you'll get long execution time again. But if you
change your index to (id_client, utc) then you'll have fast times for
any @IdClient.


Pavel

On Thu, Jun 17, 2010 at 8:32 AM, Gregoire de Turckheim
 wrote:
> Hi,
>
> I'm noticing a strange behaviour with SELECT statements.
>
> Let's consider this statement :
>
> SELECT * FROM log
> WHERE id_clie...@idclient
> ORDER BY utc DESC
> LIMIT 1
>
> The table has an index on the "utc" and "id_client" fields pair.
>
> When running this query with an @IdClient value which exists in the
> table, the query returns instantly (< 200ms)
> When running this query with an @IdClient value which doesn't exist in
> the table, the query is slow (2,63s)
> When running this query with an @IdClient value which exists in the
> table and the "NOT INDEXED" keywords, the query is slow too (2,84s)
>
> It seems to me the DB engine tries to perform an unindexed search if the
> requested value is not present in the index.
> Is this behaviour intended ? How can I get rid of it ?
>
> Regards,
>
> --
> Grégoire de Turckheim-  TAVEO
> Gérant   -  www.taveo.com
> Mob: 06 26 92 40 02  -  Tel: 09 50 58 40 02
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Avoiding Database Is Locked Error

[Pavel Ivanov]

> In my last post I mentioned that I updated my programs so that I can
> execute an arbitrary query or update, thus eliminating the need to use
> the sqlite command line utility. There really is only one program
> accessing the database now.

Sorry, I've missed this detail. In this case you are safe indeed.


Pavel

On Thu, Jun 17, 2010 at 9:00 AM, Odekirk, Shawn
 wrote:
>>> I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a
>>> problem in my case, since now there really is just one program
> accessing
>>> the database.
>>
>>Did you forget the message this thread was started from? The sqlite3
>>command line utility is a second program, so this could be a problem.
>>
>>Pavel
>
> Thank you for taking the time to respond.
> No, I did not forget the message this thread was started from. I am the
> one who started the thread.
> In my last post I mentioned that I updated my programs so that I can
> execute an arbitrary query or update, thus eliminating the need to use
> the sqlite command line utility. There really is only one program
> accessing the database now.
> Please let me know if you still think that BEGIN IMMEDIATE is something
> I should be using.
> I am a newbie to sqlite and welcome any advice or suggestions.
>
> Thanks,
> Shawn
> ___
> 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] SELECT behaviour with INDEX

[Igor Tandetnik]

Gregoire de Turckheim  wrote:
> This might not be the scope of sqlite.org documentation area, but it
> could be a good idea to have a paper about how indexes work.

It's simple, really. Imagine a phonebook, with names sorted alphabetically. 
Consider each letter of the name as a separate field: the index is on first 
letter, then on second letter, and so on.

Now, think about how you would answer this question: find all names whose 
second letter is X, and return them ordered by first letter. See if you can 
avoid scanning the whole phonebook.
-- 
Igor Tandetnik

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

Re: [sqlite] SELECT behaviour with INDEX

[Gregoire de Turckheim]

Pavel Ivanov a écrit :
>> It seems to me the DB engine tries to perform an unindexed search if the
>> requested value is not present in the index.
>> Is this behaviour intended ? How can I get rid of it ?
>> 
>
> If your index is in that very order as you said, i.e. (utc,
> id_client), then whatever @IdClient you put in the query SQLite will
> perform full index scan because it can benefit from index only for the
> purpose of ordering but not for the purpose of searching. You've just
> got lucky having LIMIT 1 in the query and providing @IdClient that has
> some utc close to maximum. If you provide @IdClient that has utc close
> to minimum then you'll get long execution time again. But if you
> change your index to (id_client, utc) then you'll have fast times for
> any @IdClient.
>
>   
That make perfectly sense, thank you very much !
I didn't imagine the fields' order could be important but I can clearly 
see now.

This might not be the scope of sqlite.org documentation area, but it 
could be a good idea to have a paper about how indexes work.

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

Re: [sqlite] Avoiding Database Is Locked Error

[Odekirk, Shawn]

>> I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a
>> problem in my case, since now there really is just one program
accessing
>> the database.
>
>Did you forget the message this thread was started from? The sqlite3
>command line utility is a second program, so this could be a problem.
>
>Pavel

Thank you for taking the time to respond.
No, I did not forget the message this thread was started from. I am the
one who started the thread.
In my last post I mentioned that I updated my programs so that I can
execute an arbitrary query or update, thus eliminating the need to use
the sqlite command line utility. There really is only one program
accessing the database now.
Please let me know if you still think that BEGIN IMMEDIATE is something
I should be using.
I am a newbie to sqlite and welcome any advice or suggestions.

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

Re: [sqlite] SELECT behaviour with INDEX

[Pavel Ivanov]

> It seems to me the DB engine tries to perform an unindexed search if the
> requested value is not present in the index.
> Is this behaviour intended ? How can I get rid of it ?

If your index is in that very order as you said, i.e. (utc,
id_client), then whatever @IdClient you put in the query SQLite will
perform full index scan because it can benefit from index only for the
purpose of ordering but not for the purpose of searching. You've just
got lucky having LIMIT 1 in the query and providing @IdClient that has
some utc close to maximum. If you provide @IdClient that has utc close
to minimum then you'll get long execution time again. But if you
change your index to (id_client, utc) then you'll have fast times for
any @IdClient.


Pavel

On Thu, Jun 17, 2010 at 8:32 AM, Gregoire de Turckheim
 wrote:
> Hi,
>
> I'm noticing a strange behaviour with SELECT statements.
>
> Let's consider this statement :
>
> SELECT * FROM log
> WHERE id_clie...@idclient
> ORDER BY utc DESC
> LIMIT 1
>
> The table has an index on the "utc" and "id_client" fields pair.
>
> When running this query with an @IdClient value which exists in the
> table, the query returns instantly (< 200ms)
> When running this query with an @IdClient value which doesn't exist in
> the table, the query is slow (2,63s)
> When running this query with an @IdClient value which exists in the
> table and the "NOT INDEXED" keywords, the query is slow too (2,84s)
>
> It seems to me the DB engine tries to perform an unindexed search if the
> requested value is not present in the index.
> Is this behaviour intended ? How can I get rid of it ?
>
> Regards,
>
> --
> Grégoire de Turckheim    -  TAVEO
> Gérant                   -  www.taveo.com
> Mob: 06 26 92 40 02      -  Tel: 09 50 58 40 02
>
> ___
> 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] SELECT behaviour with INDEX

[Igor Tandetnik]

Gregoire de Turckheim  wrote:
> I'm noticing a strange behaviour with SELECT statements.
> 
> Let's consider this statement :
> 
> SELECT * FROM log
> WHERE id_clie...@idclient
> ORDER BY utc DESC
> LIMIT 1
> 
> The table has an index on the "utc" and "id_client" fields pair.
> 
> When running this query with an @IdClient value which exists in the
> table, the query returns instantly (< 200ms)

Just a lucky choice of the ID. Try one that only appears near the bottom of the 
list, when sorted by (utc DESC).

Your index cannot be used to satisfy the WHERE clause, and is only used for 
ORDER BY. So SQLite does a full table scan, in order of utc, until it hits the 
record with the right id_client.

Try an index on (id_client, utc) instead, in that order.
-- 
Igor Tandetnik

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

[sqlite] SELECT behaviour with INDEX

[Gregoire de Turckheim]

Hi,

I'm noticing a strange behaviour with SELECT statements.

Let's consider this statement :

SELECT * FROM log
WHERE id_clie...@idclient
ORDER BY utc DESC
LIMIT 1

The table has an index on the "utc" and "id_client" fields pair.

When running this query with an @IdClient value which exists in the 
table, the query returns instantly (< 200ms)
When running this query with an @IdClient value which doesn't exist in 
the table, the query is slow (2,63s)
When running this query with an @IdClient value which exists in the 
table and the "NOT INDEXED" keywords, the query is slow too (2,84s)

It seems to me the DB engine tries to perform an unindexed search if the 
requested value is not present in the index.
Is this behaviour intended ? How can I get rid of it ?

Regards,

-- 
Grégoire de Turckheim-  TAVEO
Gérant   -  www.taveo.com
Mob: 06 26 92 40 02  -  Tel: 09 50 58 40 02

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

Re: [sqlite] [sqlite-announce] HELP : sqlite execute low speed in ARM9+Linux embadded system.

[D. Richard Hipp]

Questions such as this belong on sqlite-users@sqlite.org, not on 
sqlite-annou...@sqlite.org 
.  Thank you.

On Jun 16, 2010, at 11:24 PM, backup wrote:

> hi, everyone,
>   I program in an embaded system: ARM9 soc S3C2410 +Linux 2.4+sqlite  
> 3.3.
> All data store in NAND FLASH , the file system is YAFFS .
>  My question is the function "sqlite3_exec(.)"  take too many   
> seconds,
>  the following is my source code, please see the "printf" 's comment,
>  every "sqlite3_exec(.)"   takes 5 seconds, but how to reduce  
> the time?
>
> //-- start of code
> #define RECORD_NUM 1
>
> char QueryData(void)
> {
> struct timeval tpstart,tpend;
> float timeuse;
>
> char ErrorFlag=1;
> unsigned int di,dj,dk;
> DataBuffer[0]='\0'; //DataBuffer is goblal variable
> sprintf(sqlstr,"select * from db where isSent=0 limit  
> %d;",RECORD_NUM);
> p(semid);
>
> gettimeofday(&tpstart,NULL);
> rc = sqlite3_exec(db, sqlstr, SQLCallBack, 0, &zErrMsg);
> gettimeofday(&tpend,NULL);
> timeuse=100*(tpend.tv_sec-tpstart.tv_sec)+
> tpend.tv_usec-tpstart.tv_usec;
> timeuse/=100;
> printf("sqlite3 select * Used Time：%f\n",timeuse); // print value :5  
> seconds
>
>
> v(semid);
>
> sprintf(sqlstr,"update db set isSent=1 where ID in (select ID from  
> db where isSent=0 limit %d);",RECORD_NUM);
> p(semid);
> gettimeofday(&tpstart,NULL);
>
>
> rc = sqlite3_exec(db, sqlstr, 0, 0, &zErrMsg);
>
> gettimeofday(&tpend,NULL);
> timeuse=100*(tpend.tv_sec-tpstart.tv_sec)+
> tpend.tv_usec-tpstart.tv_usec;
> timeuse/=100;
> printf("sqlite3 update Used Time：%f\n",timeuse); //print value : 5s
>
>
>
> v(semid);
> }
> static int SQLCallBack(void *NotUsed, int argc, char **argv, char  
> **azColName)
> {
>
> struct timeval tpstart,tpend;
> float timeuse;
>
>
> int i;
> char tstr[500];
> gettimeofday(&tpstart,NULL);
>
> // argv[0] is ID ,no use for server
> sprintf(tstr,"'%s',",argv[1]);
> strcat(DataBuffer,tstr);
> for(i=2; i // printf("%s,",argv[i] ? argv[i] : "N");
> sprintf(tstr,"%s,",argv[i]);
> strcat(DataBuffer,tstr);
> }
> strcat(DataBuffer,"\n");
>
> gettimeofday(&tpend,NULL);
> timeuse=100*(tpend.tv_sec-tpstart.tv_sec)+
> tpend.tv_usec-tpstart.tv_usec;
> timeuse/=100;
> printf("SQLCallBack Used Time：%f\n",timeuse); // print value : 
> 0.000280s
>
> return 0;
> }
>
> //-- end of code
>
>
> 网易为中小企业免费提供企业邮箱（自主域名）  
> ___
> sqlite-announce mailing list
> sqlite-annou...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce

D. Richard Hipp
d...@hwaci.com



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