Re: [sqlite] question about case when
"Karthik" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > A question about "case when" > > i have the following table > create table test( >id integer primary key, >category text, >rating integer > ) > the "rating" column takes value 1 to 5, > I need to get number of 1 to 5 rated records for each category and for > this i used the query, > select >category, >sum(case when rating=1 then 1 else 0 end) as onestar, >sum(case when rating=2 then 1 else 0 end) as twostar, >sum(case when rating=3 then 1 else 0 end) as threestar, >sum(case when rating=4 then 1 else 0 end) as fourstar, >sum(case when rating=5 then 1 else 0 end) as fivestar > from test1 >group by category > > i also tried the following query which also worked > select >category, >sum(rating=1) as onestar, >sum(rating==2) as twostar, //both =and == work >sum(rating=3) as threestar, >sum(rating=4) as fourstar, >sum(rating=5) as fivestar > from test1 >group by category > > which of the queries is better? are the two queries just the same > behind the scenes? They are essentially the same. Comparison operators return 1 for true and 0 for false. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with simple select
Aha! It turns out there was not a trailing space, but a trailing null character. The save code had a hard-coded size, something like: sqlite3_bind_text(g_objSaveStmt, 11, saveIdStr.c_str(), 16, SQLITE_STATIC); So when saveIdStr was less than 16 characters long, it was reading past the end of the string data when saving the value. Thanks for the help. > -Original Message- > From: [EMAIL PROTECTED] [mailto:sqlite-users- > [EMAIL PROTECTED] On Behalf Of Noah Hart > Sent: Friday, July 11, 2008 5:34 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] problem with simple select > > My guess is that there is a trailing space in the record. > Try the following: > sqlite> select save_id ||'<' from ae_objects where save_id like 165; > 165< > > And see where the "sean" save_id field ends. > > Regards, Noah > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Sean Riley > Sent: Friday, July 11, 2008 5:04 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] problem with simple select > > Thanks for the quick response. My application is using 3.4.1, but I > grabbed the 3.5.9 executable and got the same thing. > > SQLite version 3.5.9 > Enter ".help" for instructions > sqlite> select * from ae_objects; > 20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2 > sqlite> select * from ae_objects where save_id=165; > sqlite> select * from ae_objects where save_id like 165; > 20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2 > > So I tried what your code from below and it worked for me in a new > database. Strange thing though, if I do the insert from your code into > my existing database, then that new record shows up when I do: > > sqlite> select * from ae_objects where save_id=165; > > But the existing record (the "sean" one) does not! > > > > > CONFIDENTIALITY NOTICE: > This message may contain confidential and/or privileged information. If > you are not the addressee or authorized to receive this for the addressee, > you must not use, copy, disclose, or take any action based on this message > or any information herein. If you have received this message in error, > please advise the sender immediately by reply e-mail and delete this > message. Thank you for your cooperation. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with simple select
My guess is that there is a trailing space in the record. Try the following: sqlite> select save_id ||'<' from ae_objects where save_id like 165; 165< And see where the "sean" save_id field ends. Regards, Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sean Riley Sent: Friday, July 11, 2008 5:04 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] problem with simple select Thanks for the quick response. My application is using 3.4.1, but I grabbed the 3.5.9 executable and got the same thing. SQLite version 3.5.9 Enter ".help" for instructions sqlite> select * from ae_objects; 20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2 sqlite> select * from ae_objects where save_id=165; sqlite> select * from ae_objects where save_id like 165; 20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2 So I tried what your code from below and it worked for me in a new database. Strange thing though, if I do the insert from your code into my existing database, then that new record shows up when I do: sqlite> select * from ae_objects where save_id=165; But the existing record (the "sean" one) does not! CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with simple select
Thanks for the quick response. My application is using 3.4.1, but I grabbed the 3.5.9 executable and got the same thing. SQLite version 3.5.9 Enter ".help" for instructions sqlite> select * from ae_objects; 20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2 sqlite> select * from ae_objects where save_id=165; sqlite> select * from ae_objects where save_id like 165; 20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2 So I tried what your code from below and it worked for me in a new database. Strange thing though, if I do the insert from your code into my existing database, then that new record shows up when I do: sqlite> select * from ae_objects where save_id=165; But the existing record (the "sean" one) does not! > -Original Message- > From: [EMAIL PROTECTED] [mailto:sqlite-users- > [EMAIL PROTECTED] On Behalf Of Noah Hart > Sent: Friday, July 11, 2008 2:24 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] problem with simple select > > Sean, what version of sqlite are you using? > > > With the command line version it appears to work under 3.5.9 > > SQLite version 3.5.9 > Enter ".help" for instructions > sqlite> DROP TABLE if exists ae_objects ; > sqlite> CREATE TABLE ae_objects ( >...> oid INTEGER PRIMARY KEY, >...> nameVARCHAR(64), >...> template_id INTEGER, >...> template_module_id INTEGER, >...> pos_x FLOAT, >...> pos_y FLOAT, >...> pos_z FLOAT, >...> facing FLOAT, >...> sprite_id INTEGER, >...> sprite_module_idINTEGER, >...> save_id VARCHAR(16), >...> save_type INTEGER ); > sqlite> > sqlite> insert into ae_objects values > (40007,1000,1,0,3.43301269412041,12.4330126941204,0.0,0.0,11,0,165,2); > sqlite> select * from ae_objects; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> select * from ae_objects where save_id = 165; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> select * from ae_objects where save_id = '165'; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> select * from ae_objects where save_id like '165'; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> SELECT * FROM ae_objects WHERE save_id<166 and save_id>164; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> > > > > Regards, Noah > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Sean Riley > Sent: Friday, July 11, 2008 2:08 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] problem with simple select > > Hello, > > I have a strange issue with a seemingly simple query. > > The table schema: > > CREATE TABLE ae_objects ( > oid INTEGER PRIMARY KEY, > nameVARCHAR(64), > template_id INTEGER, > template_module_id INTEGER, > pos_x FLOAT, > pos_y FLOAT, > pos_z FLOAT, > facing FLOAT, > sprite_id INTEGER, > sprite_module_idINTEGER, > save_id VARCHAR(16), > save_type INTEGER ); > > Sequence of SQL statements executed: > sqlite> select * from ae_objects; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> select * from ae_objects where save_id = 165; > sqlite> select * from ae_objects where save_id = '165'; > sqlite> select * from ae_objects where save_id like '165'; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> SELECT * FROM ae_objects WHERE save_id<166 and save_id>164; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > > Why does the "save_id = 165" fail to get any rows? I get zero rows when > I attempt to find the row by exact match of the "save_id" column, but > with a "like" or a "greater than 164 and less than 166" I get the one > row I am looking for. > > This only seems happens when that particular column is varchar(16). I > have gotten this behavior on windows and linux. Can provide a db file if > that helps. > > > --- > Sean Riley > Lead Programmer, Areae Inc. > "All problems in computer science can be solved by another level of > indirection", Butler Lampson, 1972 > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > CONFIDENTIALITY NOTICE: > This message may contain confidential and/or privileged information. If > you are not the addressee or authorized to receive this for the addressee, > you must not use, copy, disclose, or take any action based on this message > or any information herein. If you have received this message in error, > please advise the sender immediately by
[sqlite] Version 3.5.9 vs 3.5.2
Hi All, I am currently using the sqlite3 version 3.5.2 and we will have the release come in soon. So I am wondering if I need to upgrade my sqlite to version 3.5.9 before the release. If so then Can you please tell me why I need to upgrade to 3.5.9. At this time I need the buildin function group_concat and this is only the reason that make me to consider 3.5.9. Thanks in advance, JP ___ 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] AIX I/O error on commit and MJ file missing.
Hi all, after running a transaction that copies data from an attached db into the main db, the commit returns an error 10. with a msg "disk I/O error" This is on AIX 5.3 and sqlite 3.5.9 (32bit compile) Other commits seem to be fine. (non attached commits to other db's ) Even stranger is that the sqlite command line (after attaching a db) does not seem to create a master journal during a transaction even though a journal file is created for the initial database. Any ideas on tracking this down? Thanks, Ken ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with simple select
Sean, what version of sqlite are you using? With the command line version it appears to work under 3.5.9 SQLite version 3.5.9 Enter ".help" for instructions sqlite> DROP TABLE if exists ae_objects ; sqlite> CREATE TABLE ae_objects ( ...> oid INTEGER PRIMARY KEY, ...> nameVARCHAR(64), ...> template_id INTEGER, ...> template_module_id INTEGER, ...> pos_x FLOAT, ...> pos_y FLOAT, ...> pos_z FLOAT, ...> facing FLOAT, ...> sprite_id INTEGER, ...> sprite_module_idINTEGER, ...> save_id VARCHAR(16), ...> save_type INTEGER ); sqlite> sqlite> insert into ae_objects values (40007,1000,1,0,3.43301269412041,12.4330126941204,0.0,0.0,11,0,165,2); sqlite> select * from ae_objects; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 sqlite> select * from ae_objects where save_id = 165; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 sqlite> select * from ae_objects where save_id = '165'; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 sqlite> select * from ae_objects where save_id like '165'; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 sqlite> SELECT * FROM ae_objects WHERE save_id<166 and save_id>164; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 sqlite> Regards, Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sean Riley Sent: Friday, July 11, 2008 2:08 PM To: sqlite-users@sqlite.org Subject: [sqlite] problem with simple select Hello, I have a strange issue with a seemingly simple query. The table schema: CREATE TABLE ae_objects ( oid INTEGER PRIMARY KEY, nameVARCHAR(64), template_id INTEGER, template_module_id INTEGER, pos_x FLOAT, pos_y FLOAT, pos_z FLOAT, facing FLOAT, sprite_id INTEGER, sprite_module_idINTEGER, save_id VARCHAR(16), save_type INTEGER ); Sequence of SQL statements executed: sqlite> select * from ae_objects; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 sqlite> select * from ae_objects where save_id = 165; sqlite> select * from ae_objects where save_id = '165'; sqlite> select * from ae_objects where save_id like '165'; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 sqlite> SELECT * FROM ae_objects WHERE save_id<166 and save_id>164; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 Why does the "save_id = 165" fail to get any rows? I get zero rows when I attempt to find the row by exact match of the "save_id" column, but with a "like" or a "greater than 164 and less than 166" I get the one row I am looking for. This only seems happens when that particular column is varchar(16). I have gotten this behavior on windows and linux. Can provide a db file if that helps. --- Sean Riley Lead Programmer, Areae Inc. "All problems in computer science can be solved by another level of indirection", Butler Lampson, 1972 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] problem with simple select
Hello, I have a strange issue with a seemingly simple query. The table schema: CREATE TABLE ae_objects ( oid INTEGER PRIMARY KEY, nameVARCHAR(64), template_id INTEGER, template_module_id INTEGER, pos_x FLOAT, pos_y FLOAT, pos_z FLOAT, facing FLOAT, sprite_id INTEGER, sprite_module_idINTEGER, save_id VARCHAR(16), save_type INTEGER ); Sequence of SQL statements executed: sqlite> select * from ae_objects; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 sqlite> select * from ae_objects where save_id = 165; sqlite> select * from ae_objects where save_id = '165'; sqlite> select * from ae_objects where save_id like '165'; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 sqlite> SELECT * FROM ae_objects WHERE save_id<166 and save_id>164; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 Why does the "save_id = 165" fail to get any rows? I get zero rows when I attempt to find the row by exact match of the "save_id" column, but with a "like" or a "greater than 164 and less than 166" I get the one row I am looking for. This only seems happens when that particular column is varchar(16). I have gotten this behavior on windows and linux. Can provide a db file if that helps. --- Sean Riley Lead Programmer, Areae Inc. "All problems in computer science can be solved by another level of indirection", Butler Lampson, 1972 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] patch to allow integer rtree keys
Filip Navara wrote:
> how about actually attaching the patch? :)
>
> - Filip
>
> On Fri, Jul 11, 2008 at 9:23 PM, Steve Friedman <[EMAIL PROTECTED]> wrote:
>> I've just started using the rtree extension, and have found that the 32-bit
>> float for the range keys is not appropriate for me. Please find attached a
>> patch for rtree.c (based on v1.5) that allows for int -OR- unsigned int -OR-
>> float operation.
>>
>> Steve Friedman
Not sure where it got deleted (since my inbox shows the attachment).
Included inline...
--- rtree.c 2008-07-11 15:04:42.0 -0400
+++ rtreemod.c 2008-07-11 15:04:31.0 -0400
@@ -149,13 +149,36 @@
RtreeConstraint *aConstraint; /* Search constraints. */
};
+#if defined( SQLITE_RTREE_TYPE_INT)
+typedef int ConstraintType;
+# define sqlite3_result_ConstraintType sqlite3_result_int
+# define sqlite3_value_ConstraintType(x) ((int) sqlite3_value_int((x)))
+# define sqlite3_snprintf_ConstraintType( a, b, c) \
+ sqlite3_snprintf( (a), (b), " %d", (c))
+
+#elif defined(SQLITE_RTREE_TYPE_UINT)
+typedef u32 ConstraintType;
+# define sqlite3_result_ConstraintType sqlite3_result_int64
+# define sqlite3_value_ConstraintType(x) ((u32) sqlite3_value_int64((x)))
+# define sqlite3_snprintf_ConstraintType( a, b, c) \
+ sqlite3_snprintf( (a), (b), " %u", (c))
+
+#else
+typedef float ConstraintType;
+# define sqlite3_result_ConstraintType sqlite3_result_double
+# define sqlite3_value_ConstraintType(x) ((float)
sqlite3_value_double((x)))
+# define sqlite3_snprintf_ConstraintType( a, b, c) \
+ sqlite3_snprintf( (a), (b), " %f", (double) (c))
+#endif
+
+
/*
** A search constraint.
*/
struct RtreeConstraint {
int iCoord; /* Index of constrained coordinate */
int op; /* Constraining operation */
- float rValue; /* Constraint value. */
+ ConstraintType rValue;/* Constraint value. */
};
/* Possible values for RtreeConstraint.op */
@@ -198,7 +221,7 @@
*/
struct RtreeCell {
i64 iRowid;
- float aCoord[RTREE_MAX_DIMENSIONS*2];
+ ConstraintType aCoord[RTREE_MAX_DIMENSIONS*2];
};
#define MAX(x,y) ((x) < (y) ? (y) : (x))
@@ -211,14 +234,14 @@
static int readInt16(u8 *p){
return (p[0]<<8) + p[1];
}
-static float readReal32(u8 *p){
+static ConstraintType readReal32(u8 *p){
u32 i = (
(((u32)p[0]) << 24) +
(((u32)p[1]) << 16) +
(((u32)p[2]) << 8) +
(((u32)p[3]) << 0)
);
- return *(float *)
+ return *(ConstraintType *)
}
static i64 readInt64(u8 *p){
return (
@@ -243,9 +266,9 @@
p[1] = (i>> 0)&0xFF;
return 2;
}
-static int writeReal32(u8 *p, float f){
+static int writeReal32(u8 *p, ConstraintType f){
u32 i;
- assert( sizeof(float)==4 );
+ assert( sizeof(ConstraintType)==4 );
assert( sizeof(u32)==4 );
i = *(u32 *)
p[0] = (i>>24)&0xFF;
@@ -543,7 +566,7 @@
/*
** Return coordinate iCoord from cell iCell in node pNode.
*/
-static float nodeGetCoord(
+static ConstraintType nodeGetCoord(
Rtree *pRtree,
RtreeNode *pNode,
int iCell,
@@ -721,8 +744,8 @@
for(ii=0; iinConstraint; ii++){
RtreeConstraint *p = >aConstraint[ii];
-float cell_min = cell.aCoord[(p->iCoord>>1)*2];
-float cell_max = cell.aCoord[(p->iCoord>>1)*2+1];
+ConstraintType cell_min = cell.aCoord[(p->iCoord>>1)*2];
+ConstraintType cell_max = cell.aCoord[(p->iCoord>>1)*2+1];
assert( cell_min<=cell_max );
switch( p->op ){
@@ -769,7 +792,7 @@
nodeGetCell(pRtree, pCursor->pNode, pCursor->iCell, );
for(ii=0; iinConstraint; ii++){
RtreeConstraint *p = >aConstraint[ii];
-float cell_val = cell.aCoord[p->iCoord];
+ConstraintType cell_val = cell.aCoord[p->iCoord];
int res;
switch( p->op ){
case RTREE_LE: res = (cell_val<=p->rValue); break;
@@ -935,8 +958,8 @@
i64 iRowid = nodeGetRowid(pRtree, pCsr->pNode, pCsr->iCell);
sqlite3_result_int64(ctx, iRowid);
}else{
-float fCoord = nodeGetCoord(pRtree, pCsr->pNode, pCsr->iCell, i-1);
-sqlite3_result_double(ctx, fCoord);
+ConstraintType fCoord = nodeGetCoord(pRtree, pCsr->pNode,
pCsr->iCell, i-1);
+sqlite3_result_ConstraintType(ctx, fCoord);
}
return SQLITE_OK;
@@ -1009,7 +1032,7 @@
RtreeConstraint *p = >aConstraint[ii];
p->op = idxStr[ii*2];
p->iCoord = idxStr[ii*2+1]-'a';
- p->rValue = sqlite3_value_double(argv[ii]);
+ p->rValue = sqlite3_value_ConstraintType(argv[ii]);
}
}
}
@@ -1157,8 +1180,8 @@
/*
** Return the N-dimensional volumn of the cell stored in *p.
*/
-static float cellArea(Rtree *pRtree, RtreeCell *p){
- float area = 1.0;
+static ConstraintType cellArea(Rtree *pRtree, RtreeCell *p){
+ ConstraintType area = 1.0;
int ii;
for(ii=0; ii<(pRtree->nDim*2); ii+=2){
area = area * (p->aCoord[ii+1] - p->aCoord[ii]);
@@ -1170,8
Re: [sqlite] error from sqlite3_get_table
On Jul 11, 2008, at 1:45 PM, Gregor Brandt wrote: > I get and SQLITE_ERROR code form sqlite3_get_table. Upon checking > sqlite3_errcode and sqlite3_errmsg I get 0 and 'not an error'. Quoting from the documentation: "The sqlite3_get_table() interface is implemented as a wrapper around sqlite3_exec(). The sqlite3_get_table() routine does not have access to any internal data structures of SQLite. It uses only the public interface defined here. As a consequence, errors that occur in the wrapper layer outside of the internal sqlite3_exec() call are not reflected in subsequent calls to sqlite3_errcode() or sqlite3_errmsg()." > > > The command SELECT * FROM `alarm_history` ORDER BY `alarm_id`; is very > simple and most of the time it workswhy am I getting an error > occasionally? > Maybe you could use the pzErrMsg parameter of sqlite3_get_table() to find out? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] error from sqlite3_get_table
I get and SQLITE_ERROR code form sqlite3_get_table. Upon checking sqlite3_errcode and sqlite3_errmsg I get 0 and 'not an error'. The command SELECT * FROM `alarm_history` ORDER BY `alarm_id`; is very simple and most of the time it workswhy am I getting an error occasionally? Gregor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] incorrect output of .timer during long operation
Per gcc 4.1.2 (on fedora core 8, at least), tv_usec and tv_sec are long
ints. Thus, the computation in timeDiff (see below) overflows if the
computation takes more than around 2147 seconds. I propose the
following patch to shell.c. (Alternatively, a test can be made to see
if the system support long longs, and continue to use usecs.)
-/* Return the difference of two time_structs in microseconds */
-static int timeDiff(struct timeval *pStart, struct timeval *pEnd){
- return (pEnd->tv_usec - pStart->tv_usec) +
- 100*(pEnd->tv_sec - pStart->tv_sec);
+/* Return the different of two time_structs in seconds */
+static float timeDiff(struct timeval *pStart, struct timeval *pEnd){
+ return (pEnd->tv_usec - pStart->tv_usec) / 100.0 +
+ (pEnd->tv_sec - pStart->tv_sec);
}
/*
** Print the timing results.
*/
static void endTimer(void){
if( enableTimer ){
struct rusage sEnd;
getrusage(RUSAGE_SELF, );
printf("CPU Time: user %f sys %f\n",
- 0.01*timeDiff(_utime, _utime),
- 0.01*timeDiff(_stime, _stime));
+ timeDiff(_utime, _utime),
+ timeDiff(_stime, _stime));
}
}
Steve Friedman
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Do I need to free results variables?
If I have something like the following PHP:
$dbh = new PDO ("sqlite:" . $somename);
$resq = $dbh->query ("select * from some_table");
$rest = $resq->fetchAll ();
then do I need to do any cleanup on $resq before re-using it, such as setting
to NULL (seems to be recommended for reuse of $dbh)? Or is that automatic (I
know this happens at script-end, but I might re-use $resq many times).
I couldn't see anything in any docs on either the PHP or sqlite sites, about
this.
Thanks,
-- Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multithreaded app and reload from disk for second db handle? [repost]
[re-repost after initial e-mail was mangled up with HTML/XML tags and second
was cut short by webmail client. Let's hope yahoo gets it right.]
hi,
Just to let you know. I did some more tests regarding the reload from disk and
skimmed through the source code. Here a quick description of how the reloading
from disk works in a multithreaded process:
(1)
If shared cache is disabled, each call to sqlite3_open() will result in a db
handle with its own btree and page cache. If the page cache is not shared, each
db handle needs to refresh its page cache from disk if some other thread or
process using some other db handle has written to the database. The magic piece
of code is in pager.c: pagerSharedLock().
if( memcmp(pPager->dbFileVers, dbFileVers, sizeof(dbFileVers))!=0 ){
pager_reset(pPager);
}
If the version number read from the file on disk is different than the version
number in memory, reset the pager cache.
(2)
If shared cache is enabled, and it's a disk-based database (not in-memory, not
temporary) each call to sqlite3_open() results in a db handle that points to
the same shared btree and page cache (the relevant code can be seen in the
implementation of sqlite3BtreeOpen() ). This also means that if there's a write
transaction from any of the db handles, the page cache version number is
increased. This eventually leads to the fact the version number on disk and
version number in the page cache are the same. I.e. no reload happens.
Hope that helps a bit.
Markus
PS: Below some sample code I used. Simply compile and run with "sampleapp
db-file". Note that it deletes the file first if it exists. Run the
application with e.g. "strace -f" (Linux distro) to see system calls. The
application creates two db handles to the same db (one in the main thread and
one in a newly started thread). The first one only writes and the second one
only reads.
#include
#include
#include
#include
#include
/*
* Sample code. No guarantees for correctness
*/
/*
* some global variables that are quick'n'dirty but do the job
*/
char** argv_global;
/*
* A callback function needed for sqlite3_exec. Copied from sample code.
*/
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i
[sqlite] multithreaded app and reload from disk for second db handle? [repost]
[repost after initial e-mail was mangled up with HTML/XML tags]
hi,
Just to let you know. I did some more tests and skimmed through the source
code. Here a quick description of how the reloading from disk works in a
multithreaded process:
(1)
If shared cache is disabled, each call to sqlite3_open() will result in a db
handle with its own btree and page cache. If the page cache is not shared, each
db handle needs to refresh its page cache from disk if some other thread or
process using some other db handle has written to the database. The magic piece
of code is in pager.c: pagerSharedLock().
if( memcmp(pPager->dbFileVers, dbFileVers, sizeof(dbFileVers))!=0 ){
pager_reset(pPager);
}
If the version number read from the file on disk is different than the version
number in memory, reset the pager cache.
(2)
If shared cache is enabled, and it's a disk-based database (not in-memory, not
temporary) each call to sqlite3_open() results in a db handle that points to
the same shared btree and page cache (the relevant code can be seen in the
implementation of sqlite3BtreeOpen() ). This also means if there's a write
transaction from any of the db handles, the page cache version number is
increased. This eventually leads to the fact the version number on disk and
version number in the page cache are the same. I.e. no reload happens.
Hope that helps bit.
Markus
PS: Below some sample code I used. Simply compile and run with "sampleapp
db-file". Note that it deletes the file first if it exists. Run the
application with e.g. "strace -f" (Linux distro) to see system calls. The
application creates two db handles to the same db (one in the main thread and
one in a newly started thread). The first one only writes and the second one
only reads.
#include
#include
#include
#include
#include
/*
* Sample code. No guarantees for correctness
*/
/*
* some global variables that are quick'n'dirty but do the job
*/
char** argv_global;
/*
* A callback function needed for sqlite3_exec. Copied from sample code.
*/
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i
_
Invite your mail contacts to join your friends list with Windows Live Spaces.
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create_url=/friends.aspx=en-us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] In-memory database (:memory:) shared between mutlipe threads in same process?
hi,
[repost after earlier posting was mangled up with some HTML tags]
I did some tests with the in-memory database and didn't find any way to use
the in-memory database from multiple db handles in the same process. Having
this capability would enable a multithreaded application to have e.g. multiple
read transactions (each with its own db handle) to run in parallel.
As far as I could see this is not possible. Correct? The implementation in
function sqlite3BtreeOpen() seems to ignore any setting of
sqlite3SharedCacheEnabled() if it is a in-memory database. What about having
in-memory databases named like: ":memory:". So as to be
able to indicate to sqlite3_open_v2() to create multiple in-memory db handlers
sharing the same btree.
Actually after a quick and dirty hack in btree.c's sqlite3BtreeOpen()
things _seem_ to work correctly. Of course this is not in any way how it should
be done, but I didn't see a quick way to give in-memory databases a name so
that can be used in the search for existing in-memory databases. But it
indicates that the btree can be shared also for in-memory databases.
//FIXME-XXX originally this is isMemdb=0
if( (flags & BTREE_PRIVATE)==0
&& isMemdb==1
//FIXME-XXX originally this is strcmp(zFulPathname, ...
if( 0==strcmp("", sqlite3PagerFilename(pBt->pPager))
&& sqlite3PagerVfs(pBt->pPager)==pVfs ){
What do you think? I think I could manage to correctly hack the sqlite code so
that it provides naming for :memory: databases. Of course only if the basic
sharing of memory databases between multiple db handles is supposed to work.
Markus
_
Invite your mail contacts to join your friends list with Windows Live Spaces.
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create_url=/friends.aspx=en-us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
