[sqlite] Share an sqlite3 struct between multiple threads

2005-09-20 Thread Marco Bambini
I know based on http://www.sqlite.org/faq.html#q8 that it is not safe  
to share the same sqlite3 structure between multiple threads.

But what if I protect its access with a mutex?
It is safe?

---
Marco Bambini



Re: [sqlite] Problem with floating point fields, and a feature request

2005-09-20 Thread Jay Sprenkle
On 9/20/05, Cariotoglou Mike <[EMAIL PROTECTED]> wrote:
>
> nothing, when you hand-code. everything, when the code is
> auto-generated, which very frequently it is



Automatically generated bad code is still bad code.
If it's generating floating point comparisons now it's bad code.
It only works most of the time.


RE: [sqlite] Problem with floating point fields, and a feature request

2005-09-20 Thread Cariotoglou Mike
also, it kills the usage of indexes, whereas what I propose would not. 

> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 5:04 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Problem with floating point fields, and 
> a feature request
> 
> On 9/20/05, Cariotoglou Mike <[EMAIL PROTECTED]> wrote:
> >
> > There is an issue with floating point fields, which exists in every 
> > database I have dealt with, and of course exists in sqlite as well, 
> > all versions.
> > Essentially, the issue is this:
> >
> > "When are two floating point values considered equal ?"
> 
> 
> what's wrong with using round(n,2)?
> 
> 
> ---
> The Castles of Dereth Calendar: a tour of the art and 
> architecture of Asheron's Call
> http://www.lulu.com/content/77264
> 
> 



RE: [sqlite] Problem with floating point fields, and a feature request

2005-09-20 Thread Cariotoglou Mike
nothing, when you hand-code. everything, when the code is
auto-generated, which very frequently it is 

> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 5:04 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Problem with floating point fields, and 
> a feature request
> 
> On 9/20/05, Cariotoglou Mike <[EMAIL PROTECTED]> wrote:
> >
> > There is an issue with floating point fields, which exists in every 
> > database I have dealt with, and of course exists in sqlite as well, 
> > all versions.
> > Essentially, the issue is this:
> >
> > "When are two floating point values considered equal ?"
> 
> 
> what's wrong with using round(n,2)?
> 
> 
> ---
> The Castles of Dereth Calendar: a tour of the art and 
> architecture of Asheron's Call
> http://www.lulu.com/content/77264
> 
> 



RE: [sqlite] Problem with floating point fields, and a feature request

2005-09-20 Thread Cariotoglou Mike
collating sequences do not apply to floating point comparisons, do they
? 

> -Original Message-
> From: Dan Kennedy [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 5:12 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Problem with floating point fields, and 
> a feature request
> 
> 
> > two floats A and B should be compared with this algorithm :
> > 
> > diff=A-B
> > if (diff>tolerance) then A>B
> > else if (diff<-tolerance) then A > else A = B
> 
> You could define a new collation sequence to do all that. 
> However, it's difficult to say what will happen when you have 
> three numbers A, B and C such that A==B and B==C but A!=C. 
> This will cause the occasional oddity.
> 
> But if you know for some reason this will never happen, 
> everything will work fine. "Know"
> 
> 
> 
> 
> 
>   
> __
> Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
> 
> 
> 



Re: [sqlite] Problem with floating point fields, and a feature request

2005-09-20 Thread Dan Kennedy

> two floats A and B should be compared with this algorithm :
> 
> diff=A-B
> if (diff>tolerance) then A>B
> else if (diff<-tolerance) then A else A = B

You could define a new collation sequence to do all that. However, it's 
difficult to say what will happen when you have three numbers A, B and 
C such that A==B and B==C but A!=C. This will cause the occasional oddity.

But if you know for some reason this will never happen, everything will
work fine. "Know"






__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com


Re: [sqlite] Problem with floating point fields, and a feature request

2005-09-20 Thread Jay Sprenkle
On 9/20/05, Cariotoglou Mike <[EMAIL PROTECTED]> wrote:
>
> There is an issue with floating point fields, which exists in every
> database I have dealt with, and of course exists in sqlite as well, all
> versions.
> Essentially, the issue is this:
>
> "When are two floating point values considered equal ?"


what's wrong with using round(n,2)?


---
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264


[sqlite] Problem with floating point fields, and a feature request

2005-09-20 Thread Cariotoglou Mike
There is an issue with floating point fields, which exists in every
database I have dealt with, and of course exists in sqlite as well, all
versions.
 Essentially, the issue is this:

"When are two floating point values considered equal ?"

Why is this an issue ?
Floating point values are some times used as part of keys. Even worse,
sql resolvers (engines that construct sql statements based on old/new
values of client data) frequently construst statements like this one:

update table
 set a=
where b=

such a statement will result from a user selecting , editing, and
posting an update. Sqlite needs to find the relevant row, (either with
an index or not), which at some point will involve comparing
a float value stored in the database with a user-supplied float value.

Now, depending on how floats are stored, and how they are inputted, this
may or may not be an issue. in sqlite 2, where all is stored as text,
one could choose to store floats with a known precision,
and work around the inherently inaccurate comparison of floats. in
sqlite 3, we have a FLOAT storage class , which means that we now store
floats in binary form (which is good), and also means that
equality is based on comparing floats in a binary way ( all 8 bytes
being equal, in other words). 

This can/will be disastrous in many cases, one such was raised some time
ago by another user (the '9.95' issue). The above update can fail, for
no reason apparent to the user.

It also happens with people working in Delphi, and using native date
formats, which are actually floats. Dates are very often part of keys,
sorting, grouping etc, so all kinds of rounding errors can and will
manifest.

Of course, one can work around this problem, by NOT storing floats in
binary format, but in a well-defined text format. however, this would be
a pity, as a lot of time would be taken by sqlite and user code in
comverting back and forth, plus, in some cases, sqlite will try to
promote values stored as text to binary, re-introducing the problem. not
to mention that, floats stored as text will collate incorrectly, unless
they are right-justified and padded with '0', which increases the
storage requirements. 

This is not new, however, in sqlite we *do* have access to the people
that write the code, so a fix *can* be found, if there is a will and
understanding of the problem :) :)

All possible solutions to this issue have to do with the way that float
equality is established. It boils down to this:

two floats A and B should be compared with this algorithm :

diff=A-B
if (diff>tolerance) then A>B
else if (diff<-tolerance) then A

or

adding a user-defined callback (as you did with the REGEXP operator),
such as 

sqlite_compareFloat(a,b), which defaults to whatever code is now used to
compare two floats, but can be overriden by the user.

the  should be in terms of SIGNIFICANT digits, not number of
decimals, due to the way floating point works.

Having a capability like this would be of immence value to the stability
of database applications, and , I suspect, would not require a major
re-work. 

what do you think, DRH, is this doable ?





RE: [sqlite] problems compiling 3.2.6

2005-09-20 Thread Cariotoglou Mike
drh already fixed it. 

see
http://www.sqlite.org/cvstrac/chngview?cn=2720 

> -Original Message-
> From: Maurizio Ferraris [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 4:13 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] problems compiling 3.2.6
> 
> I also have the same problems with VC6, and unfortunately I 
> cannot switch to
> VC7 for other reasons.
> At the moment I reverted back to 3.2.5.
> When I will have some spare time I will try to fix it myself 
> and I will inform the list.
> My guess is: update too the latest SDK (that unfortunately 
> does not integrate well with VC6) for the first problem and 
> add another (__int64) cast before the conversion to double.
> Of course this is just a hack and I hope someone will find a 
> better fix.
> Regards.
> Mau.
> 
> Drew, Stephen wrote:
> > Further update:
> > 
> > I get both these errors in Visual Studio 6.
> > 
> > As you say, I think the first is just that Visual Studio 6 has a 
> > missing definition in winbase.h. I agree with you that it is in the 
> > documentation (perhaps a check could be performed and it 
> defined if it 
> > doesn't exist...)
> > 
> > The second seems to be a limitation of VS6 - it certainly isn't a 
> > problem in VS7. Can't really think of any neat solution around it 
> > either...
> > 
> > This doesn't concern me, as - as I mention - I use VS7...
> > 
> > Steve
> > 
> > -Original Message-
> > From: Drew, Stephen
> > Sent: 19 September 2005 17:13
> > To: sqlite-users@sqlite.org
> > Subject: RE: [sqlite] problems compiling 3.2.6
> > 
> > Mike,
> > 
> > 3.2.6 compiles fine in Visual Studio 7 (.NET 2003).  I can 
> give it a 
> > go in my copy of Visual Studio 6 if you like...
> > 
> > Steve
> > 
> > -Original Message-
> > From: Cariotoglou Mike [mailto:[EMAIL PROTECTED]
> > Sent: 19 September 2005 10:14
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] problems compiling 3.2.6
> > 
> > I tried to compile 3.2.6 locally, using visual c 6, as I do 
> with all 
> > sqlite releases. this version introduces a couple of 
> changes that do 
> > not
> > compile:
> > 
> > os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : 
> undeclared 
> > identifier
> > vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to 
> > double not implemented, use signed __int64
> > 
> > the first error has to do with an old version of winbase.h, 
> which for 
> > some reason omits the definition of 
> INVALID_SET_FILE_POINTER (although 
> > the documentation mentions it). this is probably a local 
> problem, and 
> > I will try to fix locally (although I would like to hear from other 
> > people about it. I am compiling WITHOUT mfc).
> > 
> > the second I have no idea, as I don't know C.
> > 
> > anybody help ?
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> 
> 
> 



RE: [sqlite] problems compiling 3.2.6

2005-09-20 Thread Steven Van Ingelgem
Or download the CVS version, it has already been fixed by drh in there!

-Oorspronkelijk bericht-
Van: Maurizio Ferraris [mailto:[EMAIL PROTECTED]
Verzonden: dinsdag 20 september 2005 15:13
Aan: sqlite-users@sqlite.org
Onderwerp: Re: [sqlite] problems compiling 3.2.6


I also have the same problems with VC6, and unfortunately I cannot switch to
VC7 for other reasons.
At the moment I reverted back to 3.2.5.
When I will have some spare time I will try to fix it myself and I will
inform the list.
My guess is: update too the latest SDK (that unfortunately does not
integrate well with VC6) for the first problem and add another (__int64)
cast before the conversion to double.
Of course this is just a hack and I hope someone will find a better fix.
Regards.
Mau.

Drew, Stephen wrote:
> Further update:
> 
> I get both these errors in Visual Studio 6.
> 
> As you say, I think the first is just that Visual Studio 6 has a missing
> definition in winbase.h. I agree with you that it is in the
> documentation (perhaps a check could be performed and it defined if it
> doesn't exist...)
> 
> The second seems to be a limitation of VS6 - it certainly isn't a
> problem in VS7. Can't really think of any neat solution around it
> either...
> 
> This doesn't concern me, as - as I mention - I use VS7...
> 
> Steve
> 
> -Original Message-
> From: Drew, Stephen 
> Sent: 19 September 2005 17:13
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] problems compiling 3.2.6
> 
> Mike,
> 
> 3.2.6 compiles fine in Visual Studio 7 (.NET 2003).  I can give it a go
> in my copy of Visual Studio 6 if you like...
> 
> Steve 
> 
> -Original Message-
> From: Cariotoglou Mike [mailto:[EMAIL PROTECTED]
> Sent: 19 September 2005 10:14
> To: sqlite-users@sqlite.org
> Subject: [sqlite] problems compiling 3.2.6
> 
> I tried to compile 3.2.6 locally, using visual c 6, as I do with all
> sqlite releases. this version introduces a couple of changes that do not
> compile:
> 
> os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : undeclared
> identifier
> vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to double
> not implemented, use signed __int64
> 
> the first error has to do with an old version of winbase.h, which for
> some reason omits the definition of INVALID_SET_FILE_POINTER (although
> the documentation mentions it). this is probably a local problem, and I
> will try to fix locally (although I would like to hear from other people
> about it. I am compiling WITHOUT mfc).
> 
> the second I have no idea, as I don't know C.
> 
> anybody help ?
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 


Re: [sqlite] problems compiling 3.2.6

2005-09-20 Thread Maurizio Ferraris

I also have the same problems with VC6, and unfortunately I cannot switch to
VC7 for other reasons.
At the moment I reverted back to 3.2.5.
When I will have some spare time I will try to fix it myself and I will
inform the list.
My guess is: update too the latest SDK (that unfortunately does not
integrate well with VC6) for the first problem and add another (__int64)
cast before the conversion to double.
Of course this is just a hack and I hope someone will find a better fix.
Regards.
Mau.

Drew, Stephen wrote:

Further update:

I get both these errors in Visual Studio 6.

As you say, I think the first is just that Visual Studio 6 has a missing
definition in winbase.h. I agree with you that it is in the
documentation (perhaps a check could be performed and it defined if it
doesn't exist...)

The second seems to be a limitation of VS6 - it certainly isn't a
problem in VS7. Can't really think of any neat solution around it
either...

This doesn't concern me, as - as I mention - I use VS7...

Steve

-Original Message-
From: Drew, Stephen 
Sent: 19 September 2005 17:13

To: sqlite-users@sqlite.org
Subject: RE: [sqlite] problems compiling 3.2.6

Mike,

3.2.6 compiles fine in Visual Studio 7 (.NET 2003).  I can give it a go
in my copy of Visual Studio 6 if you like...

Steve 


-Original Message-
From: Cariotoglou Mike [mailto:[EMAIL PROTECTED]
Sent: 19 September 2005 10:14
To: sqlite-users@sqlite.org
Subject: [sqlite] problems compiling 3.2.6

I tried to compile 3.2.6 locally, using visual c 6, as I do with all
sqlite releases. this version introduces a couple of changes that do not
compile:

os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : undeclared
identifier
vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to double
not implemented, use signed __int64

the first error has to do with an old version of winbase.h, which for
some reason omits the definition of INVALID_SET_FILE_POINTER (although
the documentation mentions it). this is probably a local problem, and I
will try to fix locally (although I would like to hear from other people
about it. I am compiling WITHOUT mfc).

the second I have no idea, as I don't know C.

anybody help ?












Re: [sqlite] group by error in 3.2.6?

2005-09-20 Thread Eric Bohlman

Edzard Pasma wrote:

There is a difference in the behaviour of GROUP BY in version 3.2.6. If you group by a column that 
contains NULL values, each NULL value comes on a seperate output line. Conceptually not so bad, if 
we read NULL as "unknown" and not as "empty". But I guess it is an error.


This does indeed appear to be an error: see 
http://archives.postgresql.org/pgsql-sql/1999-05/msg00164.php
for a discussion which cites the SQL-92 specification as saying that two 
NULLs are considered "not distinct" and therefore should be grouped 
together.


MSSQL (and its parent Sybase) document that nulls will be grouped together.


Re[2]: [sqlite] Storing RTF text in a field

2005-09-20 Thread Sergey Startsev
Hello,

Use functions sqlite3_prepare(), then sqlite3_bind_blob to assign RTF
text to a blob parameter

Tuesday, September 20, 2005, 5:39:59 PM, you wrote:

>> Now when I try to save it to database, I get an error: unrecognized token:
>> "\". What should I do?


-- 
Best regards

 Sergey Startsev
 SQLite Analyzer - SQLite database management tool with GUI.
 http://www.kraslabs.com/sqlite_analyzer.html



[sqlite] group by error in 3.2.6?

2005-09-20 Thread Edzard Pasma
There is a difference in the behaviour of GROUP BY in version 3.2.6. If you 
group by a column that contains NULL values, each NULL value comes on a 
seperate output line. Conceptually not so bad, if we read NULL as "unknown" and 
not as "empty". But I guess it is an error.

For the rest I'm very happy with this version. Thanks, Edzard Pasma


_
Tired of spam and viruses? Get a VolcanoMail account with SpamShield Pro and 
Anti-Virus technology! http://www.volcanomail.com/


Re: [sqlite] Storing RTF text in a field

2005-09-20 Thread Edwin Knoppert

Simple convert it to 2byte hex "0AFF00..." etcc.

So the update query should use X'your hex data here'




- Original Message - 
From: "Gaurav Patole" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, September 20, 2005 7:14 AM
Subject: [sqlite] Storing RTF text in a field



Hello,

  I have the following table



CREATE TABLE Tasks(taskID INTEGER PRIMARY KEY, parentTaskID INTEGER, 
projID

INTEGER, name varchar(25), assignee varchar(25), start_date timestamp,
end_date timestamp, completed_date timestamp, last_edited timestamp,
created_date timestamp, last_edited_by varchar(25), author varchar(25),
percent_complete INTEGER, priority INTEGER, status INTEGER, description
blob, last_modified timestamp);



With description field as BLOB.



I wish to store the description that may contain RTF text.

Example:



{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\froman\fprq2\fcharset
0 Times New Roman;}}

{\colortbl ;\red0\green0\blue255;}

\viewkind4\uc1\pard\fi-360\li900\tx900\f0\fs24 1.\tab Showing comment
notifications though all the notification options are unchecked when we 
copy

and paste the task having comments.

\par \pard\fi-360\li900 2.\tab Description of 15 to 20 lines is not
retaining when we reassigned the task.

\par 3.\tab Problem in copy and paste: Copy and pasting the task having
attachments, source file's attachments gets disappeared - \cf1\b
Completed\cf0\b0 .

\par 4.\tab Normal user is not able to mark his task as completed - \cf1\b
Completed\cf0\b0 .

\par 5.\tab Problem in copy paste: Not asking for changing resource when
resource is not available in the source project.

\par 6.\tab User level rights user s/d be able to move his task.

\par \pard

\par \pard\li360 Date: 19 September' 05

\par Private Build 62

\par

\par \pard\fi-360\li720\tx720 1.\tab User s/d attaches attachment while
assigning the new task.

\par \pard\fi-360\li720 2.\tab Problem with right click move to option: 
The
option is not working when user has not changed the resource name, means 
its

not working with default resource name.

\par 3.\tab Once click on no to all (if resource is not available) next 
time

unable to copy and paste the task from one project to another, also
simulating with moving tasks by drag and drop.

\par 4.\tab While right click move to don't allow the user to select
multiple resources initially rather than showing message later.

\par 5.\tab Making project status as on-hold or proposed, all its existing
tasks are not becoming on-hold.

\par 6.\tab Project manager s/d be able to view his completed and proposed
projects.

\par 7.\tab All the tasks of the completed projects s/d become completed.

\par 8.\tab Not changing assigned to field while moving the task from one
project to another project, by using right click move to option.

\par 9.\tab Assignee field s/d gets changed while reassigning the task 
from

right click - reassigned to option for example if amol is reassigning the
task to bhavna which is assigned to amol by sayali , in this case author 
s/d
be sayali and assignee s/d be amol. Presently assignee and author remains 
as

sayali.

\par \pard Reassigned to is not changing the assignee field, author and
assignee field remains same though task is reassigned by

\par }







Now when I try to save it to database, I get an error: unrecognized token:
"\". What should I do?



Thanks,

Gaurav.










Re: [sqlite] Re: query problem

2005-09-20 Thread D. Richard Hipp
On Tue, 2005-09-20 at 06:57 +0200, Alain Bertrand wrote:
> Do you have an approximativ dateframe to see 
> this fix incorporated in a release ?
> 

No.  The problem is obscure so I am not in a big rush
to get it out.  You can fix the problem in your copy by
downloading the latest from CVS.  Or you can get the patches
directly from http://www.sqlite.org/cvstrac/chngview?cn=2725.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>