Re: [sqlite] readers and writer

[Dmitri Priimak]

Simon Slavin wrote:
> On 14 Oct 2009, at 1:21am, priimak wrote:
>
>   
>> I am heaving small problem with sqlite. I have a webapp which connects
>> to the database using sqlite-jdbc and performs SELECTs to response to
>> different GET requests, while this happens if I try to write to a
>> database ( UPDATE or INSERT ) from command line, that (i.e. update
>> process) would occasionally fail with error message "SQL error near  
>> line
>> 1: database is locked". Notice that I have only one writer, but many
>> readers. Reading documentation (http://www.sqlite.org/ 
>> lockingv3.html) I
>> was under impression that process which intends to update database  
>> will
>> place it in the pending state allowing all currently running reads (
>> SELECTs ) to proceed, while blocking new SELECTs, the lock database
>> apply changes and then unlock it allowing all pending and new  
>> SELECTs to
>> proceed. Am I right about it and if so why do I "SQL error near line  
>> 1:
>> database is locked" when trying to write to a database?
>> 
>
> I hope this will do until an expert comes along.  I think you got it  
> right, you just don't know something.
>
> The SELECT activity requires a lock to the database.  For instance,  
> consider a TABLE contact with columns name, address, phone .  An index  
> is declared on just the name column.  You execute
>
> SELECT phone FROM contacts WHERE name = 'Jackie'
>
> This requires a two-stage process: first use the index to find the ids  
> of the rows which have the right name.  Then look up those rows in the  
> table and find out which phone numbers they have.  Obviously, this  
> requires locking: you wouldn't want someone to make changes to the  
> table between those two steps.  However, it requires locking only  
> against writing: other reads going on at the same time are harmless,  
> but a change between the two steps can invalidate the data.
>
> So if a SELECT is in progress, other SELECT commands can be allowed to  
> proceed without problems. But no INSERT or UPDATE can be allowed until  
> the SELECT is finished.  Hence you will sometimes get a lock on the  
> write.
>
> How you deal with this, I don't know.  Random wait-and-try-again ?
Yes, I understood that, but the impression I got is that SELECT will
place shared lock on the database. While INSERT or UPDATE will first
place PENDING lock indicating that it wants to write. While it is in a
PENDING lock state all operations that placed SHARED lock ( such as
SELECTs ) will allow to complete and new SHARED locks either denied
or blocked ( this part of documentation is not clear as to which one of
these two actions are taken  ). Then when all SHARED locks are
removed due to completion of SELECTs, database moves from PENDING
into EXCLUSIVE lock, which is cleared when update/write completed and then
new/pending SHARED locks are allowed to proceed. This should mean
that with many processes reading and only one writing there is no need to
use sqlite3_busy_timeout() function, which is to be used when we have
many processes trying to write to the database and/or reader if new
SHARED locks are denied while database is in a PENDING and/or
EXCLUSIVE lock state ( again, this point it not clear in documentation ).
Do I understand it correctly?

--
Dmitri Priimak

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

Re: [sqlite] Hi, new member here (and also my first question)

[benang]

Well, I'm pretty sure I haven't. FYI, I wrapped the sqlite3_stmt into a
class and only call its sqlite3_finalize on its destructor. So there's no
way that it would be called twice. Or so I think.

Pavel Ivanov wrote:
>> The pPrior or p pointer isn't null so it should've been
>> freed without error IMHO. Can anybody tell me what's wrong with it?
>> Thanks
>> a lot in advance.
>
> If "pPrior or p pointer" isn't null but was already freed then double
> free can cause segmentation fault. In other words most probably you're
> calling sqlite3_finalize on already finalized statement.
>
> Pavel
>
> On Tue, Oct 13, 2009 at 5:58 AM,   wrote:
>> Hi there, I'm a new member of the mailing list. Nice to meet you all.
>>
>> BTW, I've got one problem that's been bugging me for weeks.
>>
>> Occasionally (not always), I got a seg fault at "static void
>> sqlite3MemFree(void *pPrior)". It happened when I do sqlite3_reset or
>> sqlite3_finalize. The pPrior or p pointer isn't null so it should've
>> been
>> freed without error IMHO. Can anybody tell me what's wrong with it?
>> Thanks
>> a lot in advance.
>>
>>
>> Fare thee well,
>> Bawenang R. P. P.
>>
>> 
>> "If a picture is worth a thousand words, an animations is worth a
>> thousand
>> pictures. And to take that a step further, a game is worth a thousand
>> animations." – Peter Raad, Executive Director, The Guildhall at SMU
>>
>>
>> --


Fare thee well,
Bawenang R. P. P.


"If a picture is worth a thousand words, an animations is worth a thousand
pictures. And to take that a step further, a game is worth a thousand
animations." – Peter Raad, Executive Director, The Guildhall at SMU


--

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

Re: [sqlite] readers and writer

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Slavin wrote:
> Perhaps this passage could be rephrased to warn explicitly about NFS  
> rather than about the more general "files on a network filesystem".

As a general rule network filesystems are buggy.  Local filesystems get to
make all the decisions themselves - there is no other party.  With remote
filesystems everything is passed to the remote server which makes all the
decisions.  This of course is eye wateringly slow adding latency to every
filesystem operation.  So the network clients occasionally make a decision
locally instead of sending it to the server.  (This is also a *lot* easier
to code.)

Earlier NFS releases were remarkably lax on the client side - the Unix
Hater's Guide even has an entire entertaining chapter on it.  SQLite
exercises codepaths that aren't particularly normal compared to most
applications and locking is even rarer.  Unless you can guarantee *all*
client side code, the server side and interactions with multiple clients is
correct then there is the possibility of corrupting SQLite files.  Based on
past experience there is also the probability they will be corrupted.

Are you willing to stake your reputation and whatever else on there being
bug free implementations of AFP and SMB.  (BTW in a past life I coded an SMB
server - the other clients and servers out there are definitely not bug free :-)

Users of SQLite won't appreciate their databases being just a little bit
corrupted infrequently.

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

iEYEARECAAYFAkrVOV4ACgkQmOOfHg372QTxkgCfVrY2bpmoDtfw2rI2pnsG0o8G
uRkAoIRFY8A1sKZRFTyV1/2iqcxH4a6G
=jv8p
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] readers and writer

[Jean-Christophe Deschamps]

´¯¯¯
>So if a SELECT is in progress, other SELECT commands can be allowed to
>proceed without problems. But no INSERT or UPDATE can be allowed until
>the SELECT is finished.  Hence you will sometimes get a lock on the
>write.
>
>How you deal with this, I don't know.  Random wait-and-try-again ?
`---

Isn't that precisely what sqlite3_busy_timeout() is for?



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

Re: [sqlite] readers and writer

[Simon Slavin]

On 14 Oct 2009, at 1:21am, priimak wrote:

> http://www.sqlite.org/lockingv3.html

By the way, I just read some of that page and a bit of it, while  
possibly technically correct, may be putting some people off from  
using SQLite.

"One should note that POSIX advisory locking is known to be buggy or  
even unimplemented on many NFS implementations (including recent  
versions of Mac OS X) and that there are reports of locking problems  
for network filesystems under Windows. Your best defense is to not use  
SQLite for files on a network filesystem."

This is correct in that implementation of NFS file locking under OS X  
is buggy, as it is in many OSen.  However, almost no users of OS X  
mount shared volumes using NFS.  Both AFP and SMB offer so many other  
benefits (including decent security) that NFS is hardly used at all.   
And both AFP and SMB do locking properly (or at least any bugs do get  
fixed quickly because everyone complains about them).

Perhaps this passage could be rephrased to warn explicitly about NFS  
rather than about the more general "files on a network filesystem".

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

Re: [sqlite] readers and writer

[Simon Slavin]

On 14 Oct 2009, at 1:21am, priimak wrote:

> I am heaving small problem with sqlite. I have a webapp which connects
> to the database using sqlite-jdbc and performs SELECTs to response to
> different GET requests, while this happens if I try to write to a
> database ( UPDATE or INSERT ) from command line, that (i.e. update
> process) would occasionally fail with error message "SQL error near  
> line
> 1: database is locked". Notice that I have only one writer, but many
> readers. Reading documentation (http://www.sqlite.org/ 
> lockingv3.html) I
> was under impression that process which intends to update database  
> will
> place it in the pending state allowing all currently running reads (
> SELECTs ) to proceed, while blocking new SELECTs, the lock database
> apply changes and then unlock it allowing all pending and new  
> SELECTs to
> proceed. Am I right about it and if so why do I "SQL error near line  
> 1:
> database is locked" when trying to write to a database?

I hope this will do until an expert comes along.  I think you got it  
right, you just don't know something.

The SELECT activity requires a lock to the database.  For instance,  
consider a TABLE contact with columns name, address, phone .  An index  
is declared on just the name column.  You execute

SELECT phone FROM contacts WHERE name = 'Jackie'

This requires a two-stage process: first use the index to find the ids  
of the rows which have the right name.  Then look up those rows in the  
table and find out which phone numbers they have.  Obviously, this  
requires locking: you wouldn't want someone to make changes to the  
table between those two steps.  However, it requires locking only  
against writing: other reads going on at the same time are harmless,  
but a change between the two steps can invalidate the data.

So if a SELECT is in progress, other SELECT commands can be allowed to  
proceed without problems. But no INSERT or UPDATE can be allowed until  
the SELECT is finished.  Hence you will sometimes get a lock on the  
write.

How you deal with this, I don't know.  Random wait-and-try-again ?

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

Re: [sqlite] Encoding specs & functions overloading

[Jean-Christophe Deschamps]

The 3.6.18 sqlite3.exe CLI produces the same problem: the internal 
functions below can't be overloaded and trying to do so returns 5.

System is XP Pro x86 SP3.

What can I try next ?


>I see that sqlite3.dll is returning 5 == SQLITE_BUSY for the following
>functions:
>
>upper UTF-8
>lower UTF-8
>like   2-arg  UTF-8
>like   3-arg  UTF-8
>glob   2-arg  UTF-8
>
>If I name the functions x* all works well!




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

Re: [sqlite] Bug in date() function ??

[Pavel Ivanov]

It looks pretty interesting that no matter if date() function works as
is now or with some heuristics applied the following equations are not
always true:

date(some_date, '-1 month', '+1 month') = some_date
date(some_date, '-1 month') = date(some_date, '-1 day', '-1 month', '+1 day')

Looks like a lot of head ache for someone trying to work with such
arithmetics in his application. :)


Pavel

On Tue, Oct 13, 2009 at 3:43 PM, Nicolas Williams
 wrote:
> On Tue, Oct 13, 2009 at 03:30:44PM -0400, D. Richard Hipp wrote:
>> Begin with 2001-03-31
>> Add 1 to 03, yielding 2001-04-31
>> 04-31 means the 31st day from the beginning of april: 2001-05-01
>>
>> Begin with 2001-03-31
>> Subtract 1 from 03 yielding 2001-02-31.
>> 02-31 means the 31st day from the beginning of february:  2001-03-03
>
> The fact that Earth years are not a whole multiple of some convenient
> number of Earth days (i.e., months), is certainly annoying.  What
> SQLite3 does seems perfectly justified; that it may sometimes seem
> surprising is not your fault, but cosmic chance.
>
> I think the OP expected that 2001-03-31 - 1month == 2001-02-28 (or 29,
> on leap years), because that's often (but not always) what people mean
> when they say "a month ago".  You could have a lot of special casing in
> date() to get something closer to what people normally mean by "a month
> ago", but it'd be alot harder to explain the many heuristic choices, and
> the choices might be too specific to one language/culture -- that'd not
> be worthwhile, IMO.
>
> Nico
> --
> ___
> 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] Bug in date() function ??

[Nicolas Williams]

On Tue, Oct 13, 2009 at 03:30:44PM -0400, D. Richard Hipp wrote:
> Begin with 2001-03-31
> Add 1 to 03, yielding 2001-04-31
> 04-31 means the 31st day from the beginning of april: 2001-05-01
> 
> Begin with 2001-03-31
> Subtract 1 from 03 yielding 2001-02-31.
> 02-31 means the 31st day from the beginning of february:  2001-03-03

The fact that Earth years are not a whole multiple of some convenient
number of Earth days (i.e., months), is certainly annoying.  What
SQLite3 does seems perfectly justified; that it may sometimes seem
surprising is not your fault, but cosmic chance.

I think the OP expected that 2001-03-31 - 1month == 2001-02-28 (or 29,
on leap years), because that's often (but not always) what people mean
when they say "a month ago".  You could have a lot of special casing in
date() to get something closer to what people normally mean by "a month
ago", but it'd be alot harder to explain the many heuristic choices, and
the choices might be too specific to one language/culture -- that'd not
be worthwhile, IMO.

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

Re: [sqlite] Bug in date() function ??

[D. Richard Hipp]

On Oct 13, 2009, at 3:17 PM, Keith Roberts wrote:

> On Tue, 13 Oct 2009, D. Richard Hipp wrote:
>
>> To: General Discussion of SQLite Database 
>> From: D. Richard Hipp 
>> Subject: Re: [sqlite] Bug in date() function ??
>>
>>
>> On Oct 13, 2009, at 12:57 PM, Keith Roberts wrote:
>>
>>> Just been messing about with the date functions, and there
>>> appears to be an inconsistency when adding a month
>>> modifier. I'm running Fedora 10.
>>>
>>> From: http://www.sqlite.org/lang_datefunc.html
>>>
>>> "Thus, for example, the data 2001-03-31 modified by '+1
>>> month' initially yields 2001-04-31, but April only has 30
>>> days so the date is normalized to 2001-05-01."
>>
>> Did you happen to read the previous sentence in the documentation?
>
> Yes, I've read it again Richard. As the '+1 month' modifier
> modified the date by adding 31 days to normalise the date
> upwards, I expected the '-1 month' modifier to work in a
> similar fashion, and normalise the date *downwards* also by
> an interval of 31 days.

Begin with 2001-03-31
Add 1 to 03, yielding 2001-04-31
04-31 means the 31st day from the beginning of april: 2001-05-01

Begin with 2001-03-31
Subtract 1 from 03 yielding 2001-02-31.
02-31 means the 31st day from the beginning of february:  2001-03-03

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

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



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

Re: [sqlite] Bug in date() function ??

[Keith Roberts]

On Tue, 13 Oct 2009, D. Richard Hipp wrote:

> To: General Discussion of SQLite Database 
> From: D. Richard Hipp 
> Subject: Re: [sqlite] Bug in date() function ??
> 
>
> On Oct 13, 2009, at 12:57 PM, Keith Roberts wrote:
>
>> Just been messing about with the date functions, and there
>> appears to be an inconsistency when adding a month
>> modifier. I'm running Fedora 10.
>>
>> From: http://www.sqlite.org/lang_datefunc.html
>>
>> "Thus, for example, the data 2001-03-31 modified by '+1
>> month' initially yields 2001-04-31, but April only has 30
>> days so the date is normalized to 2001-05-01."
>
> Did you happen to read the previous sentence in the documentation?

Yes, I've read it again Richard. As the '+1 month' modifier 
modified the date by adding 31 days to normalise the date 
upwards, I expected the '-1 month' modifier to work in a 
similar fashion, and normalise the date *downwards* also by 
an interval of 31 days.

Kind Regards,

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

Re: [sqlite] low-level view of data values?

[Simon Slavin]

On 13 Oct 2009, at 2:07pm, Fred Williams wrote:

> The best way I have found to manage decimal (Business math) with  
> most all
> the databases is to use integers and multiply and divide by the  
> decimal
> offset (i.e. 10, 100, 1000) for presentation purposes,  doing my own
> "bankers rounding"  in code.  Big hassle, but transports well.

That's the approach that's generally used: all stored numbers are  
integers at the lowest level of valid currency: cents rather than  
dollars, etc..  Still doing the same thing we did when I started  
programming on mainframes.

Another problem with 'business math' is that the US and British  
(therefore Australian, Japanese, etc.) definitions require different  
rounding, days-in-a-year, compounding formulae, etc..  In other words  
a single library of calls which do all your business math for you  
would have to have a 'country' setting to make it useful for all  
users.  And nobody wants to be sued for issuing a library with a bug  
in it because they didn't know some tiny little obscure niggle in one  
country's tax law.  So yes, if you have to satisfy auditors you're  
probably going to have to do your own business maths.  At least that  
way you understand how it all works.

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

Re: [sqlite] Encoding specs & functions overloading

[Jean-Christophe Deschamps]

If I set a breakpoint on this:

 rc = sqlite3_create_function(db, p->zName, p->nArg, p->enc, 
p->pContext, p->xFunc, 0, 0);

I see that sqlite3.dll is returning 5 == SQLITE_BUSY for the following 
functions:

upper UTF-8
lower UTF-8
like   2-arg  UTF-8
like   3-arg  UTF-8
glob   2-arg  UTF-8


If I name the functions x* all works well!



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

Re: [sqlite] Question regarding BCC32

[John Elrick]

Cariotoglou Mike wrote:
> Great. thanks for the info. however, this means that at least part of the 
> runtime library actually comes from the VCL, and not the BCC32 libraries, is 
> that not so ?
>   

There are some OBJ files needed, we picked them up from somewhere, but I 
don't recall exactly where.

>  
> and, BTW, would you share your code to embed the .obj file ? I know I have 
> done it in the past, and all it takes is to implement the imports, but I dont 
> have that code around anymore, so I would be obliged...
>   

In Delphi it's the compiler directive {$L 'path\sqlite3.obj'}
... the other needed objs

and then you declare the functions:

  // Primary commands
  function  _sqlite3_open(dbname: PAnsiChar; var db: pointer): integer; 
cdecl; external;
  function  _sqlite3_prepare(db: Pointer; SQLStatement: PAnsiChar; 
nBytes: integer;
 var hstatement: pointer; var Tail: 
PAnsiChar): integer; cdecl; external;
  function  _sqlite3_prepare_v2(db: Pointer; SQLStatement: PAnsiChar; 
nBytes: integer;
 var hstatement: pointer; var Tail: 
PAnsiChar): integer; cdecl; external;
  function  _sqlite3_exec(DB: Pointer; SQLStatement: PAnsiChar; 
Callback: TSQLite3_Callback;
  UserDate: Pointer; var ErrMsg: PAnsiChar): 
Integer; cdecl; external;
...etc

If you are using Delphi, I'd be glad to send you the static library unit 
and our .obj files.


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

Re: [sqlite] Encoding specs & functions overloading

[Jean-Christophe Deschamps]

Update: the problem is in the function registration.


I tried to comment out the UTF-16 registration and the really weird 
thing is that using the following code, only GLOB with 3 arguments gets 
actually registered (along with all 1-arg string functions and the two 
collations).

There must be something obvious, but it escapes me completely right now.

Any hint?

---
SQLITE_PRIVATE int unifuzz_init(
   sqlite3 *db
){
   struct FuncScalar {
 const char *zName;/* Function name */
 int nArg; /* Number of arguments */
 int enc;  /* Optimal text encoding */
 void *pContext;   /* sqlite3_user_data() context */
 void (*xFunc)(sqlite3_context*, int, sqlite3_value**);
   } scalars[] = {
 {"version",   0,  SQLITE_ANY,0, 
versionFunc  },
 {"upper", 1,  SQLITE_UTF8,  (void *) unifuzz_upper, 
caseFunc8},
 {"upper", 1,  SQLITE_UTF16, (void *) unifuzz_upper, 
caseFunc16   },
 {"lower", 1,  SQLITE_UTF8,  (void *) unifuzz_lower, 
caseFunc8},
 {"lower", 1,  SQLITE_UTF16, (void *) unifuzz_lower, 
caseFunc16   },
 {"title", 1,  SQLITE_UTF8,  (void *) unifuzz_title, 
caseFunc8},
 {"title", 1,  SQLITE_UTF16, (void *) unifuzz_title, 
caseFunc16   },
 {"fold",  1,  SQLITE_UTF8,   (void *) unifuzz_fold, 
caseFunc8},
 {"fold",  1,  SQLITE_UTF16,  (void *) unifuzz_fold, 
caseFunc16   },
 {"unaccent",  1,  SQLITE_UTF8,   0, 
unaccFunc8   },
 {"unaccent",  1,  SQLITE_UTF16,  0, 
unaccFunc16  },
 {"proper",1,  SQLITE_UTF8,   0, 
properFunc8  },
 {"proper",1,  SQLITE_UTF16,  0, 
properFunc16 },
 {"like",  2,  SQLITE_UTF8, (void *) , likeFunc8},
//{"like",  2,  SQLITE_UTF16, (void *) , 
likeFunc16   },
 {"like",  3,  SQLITE_UTF8,  (void *) , 
likeFunc8},
//{"like",  3,  SQLITE_UTF16, (void *) , 
likeFunc16   },
 {"glob",  2,  SQLITE_UTF8,  (void *) , 
likeFunc8},
//{"glob",  2,  SQLITE_UTF16, (void *) , 
likeFunc16   },
 {"glob",  3,  SQLITE_UTF8,  (void *) , 
likeFunc8},
//{"glob",  3,  SQLITE_UTF16, (void *) , 
likeFunc16   },
 {"typos", 2,  SQLITE_UTF8,   0, 
typosFunc8   },
 {"typos", 2,  SQLITE_UTF16,  0, typosFunc16  }
   };

 int i;
 for(i = 0; (i < (sizeof(scalars) / sizeof(struct FuncScalar))); i++){
 struct FuncScalar *p = [i];
 sqlite3_create_function(db, p->zName, p->nArg, p->enc, 
p->pContext, p->xFunc, 0, 0);
 }

 /* Also override the default NOCASE case-insensitive collation 
sequence. */
 sqlite3_create_collation(db, "NOCASE",  SQLITE_UTF16LE, 0, 
nocase_collate);
 sqlite3_create_collation(db, "NAMES", SQLITE_UTF16LE, 0, 
letters_collate);
 return SQLITE_OK;
}
-



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

Re: [sqlite] Corrupted database

[McClellen, Chris]

But it does happen and we can reproduce it.  Hard killing a thread is
essentially equivalent to turning off the power.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
Sent: Tuesday, October 13, 2009 12:35 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Corrupted database


On Oct 13, 2009, at 1:56 AM, McClellen, Chris wrote:

> What is your synchronous set to?  Full?  FYI If you are using .NET  
> data
> providers, it is set to "Normal" by default.
>
> If it is not set to full, I have seen corruption when an application
> crashes, or exits when a thread is in the middle of updating the db
> (Synchronous = OFF makes corruption even easier in this case).  I have
> seen apps that do not wait for background threads to finish before
> termination, and without full sync on, either the db or the log gets
> corrupted.  A corrupted log can cause problems for you db on next run
> when recovery happens.

In theory, this shouldn't happen. Unless the application is actually
buffering data that SQLite thinks has been written to the database or
journal file in the process space on some systems.

The "synchronous" setting should only make a difference in the event
of a power or OS failure. That's the theory, anyway.

Dan.



>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Filip Navara
> Sent: Monday, October 12, 2009 12:38 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Corrupted database
>
> Hello,
>
> for a few months we have been occasionally getting corrupted databases
> in the field. So far we were unable to acquire any of them from our
> customers, but this week I finally got hold of one. Output from
> "pragma integrity_check" is included below.
>
> The schema is the following:
>
> CREATE VIRTUAL TABLE "LocalMailsIndex3" USING fts3 (id INTEGER,
> partName TEXT, content TEXT);
> CREATE TABLE LocalMailsIndex3_content(  docid INTEGER PRIMARY
> KEY,c0id, c1partName, c2content);
> CREATE TABLE LocalMailsIndex3_segdir(  level integer,  idx integer,
> start_block integer,  leaves_end_block integer,  end_block integer,
> root blob,  primary key(level, idx));
> CREATE TABLE LocalMailsIndex3_segments(  blockid INTEGER PRIMARY KEY,
> block blob);
> CREATE INDEX "LocalMailsIndex3_contentIndex" ON
> "LocalMailsIndex3_content" ("c0id", "c1partName");
>
> The database is created using SQLite 3.6.14.2, thread safe, on Windows
> with auto_vacuum=incremental. It is always opened as attached database
> with journal_mode=persist. Application crashes were most probably
> involved, but no operating system / power crashes as far as I know.
>
> One thread in the application is periodically running "pragma
> freelist_count" and "pragma incremental_vacuum(...)". Other threads
> are running combination of the following commands and no other:
>
> INSERT INTO mail_fti.LocalMailsIndex3("id", "partName", "content")
> VALUES (@id, @partName, @content)
> SELECT c2content AS content FROM mail_fti.LocalMailsIndex3_content
> WHERE c0...@id AND c1partna...@partname
> SELECT docid FROM mail_fti.LocalMailsIndex3_content WHERE c0id IN  
> (...)
> DELETE FROM mail_fti.LocalMailsIndex3 WHERE doc...@docid
> SELECT ... WHERE "id" IN (SELECT "id" FROM mail_fti.LocalMailsIndex3
> WHERE content MATCH "...")
>
> Anybody has seen something like this?
> Anybody willing to look at it? I can send the database privately.
>
> Best regards,
> Filip Navara
>
> Main freelist: Bad ptr map entry key=5143 expected=(2,0) got=(3,4467)
> Main freelist: freelist leaf count too big on page 5143
> Main freelist: Bad ptr map entry key=5449 expected=(2,0) got=(4,5143)
> Main freelist: freelist leaf count too big on page 5449
> Main freelist: 904 of 908 pages missing from overflow list starting at
> 5143
> On tree page 3878 cell 26: invalid page number 5737
> On tree page 3878 cell 26: Child page depth differs
> On tree page 3878 cell 27: Failed to read ptrmap key=5746
> On tree page 3878 cell 27: invalid page number 5746
> On tree page 3878 cell 28: Failed to read ptrmap key=5748
> On tree page 3878 cell 28: invalid page number 5748
> On tree page 3878 cell 29: Failed to read ptrmap key=5749
> On tree page 3878 cell 29: invalid page number 5749
> On tree page 3878 cell 30: Failed to read ptrmap key=5755
> On tree page 3878 cell 30: invalid page number 5755
> On tree page 3878 cell 31: Failed to read ptrmap key=5757
> On tree page 3878 cell 31: invalid page number 5757
> On tree page 3878 cell 32: Failed to read ptrmap key=5759
> On tree page 3878 cell 32: invalid page number 5759
> On tree page 3878 cell 33: Failed to read ptrmap key=5761
> On tree page 3878 cell 33: invalid page number 5761
> On tree page 3878 cell 34: Failed to read ptrmap key=5763
> On tree page 3878 cell 34: invalid page number 5763
> On tree page 3878 cell 35: Failed to read ptrmap key=5767
> On tree page 

Re: [sqlite] Encoding specs & functions overloading

[Jean-Christophe Deschamps]

Hi Pavel,


>I believe you need to show us your sql query. Maybe something in it
>forces SQLite to use UTF-16 version of the function.

Ummm, I don't kno where the problem is, but _any_ simple select will do 
(for me), e.g.:

An UTF-8 base...

CREATE TABLE "PaysISO" (
   "Nom_Iso" CHAR(43),
   "Code_Iso" CHAR(2));

/* Data "PaysISO" */
insert into "PaysISO" values('AFGHANISTAN', 'AF');
insert into "PaysISO" values('AFRIQUE DU SUD', 'ZA');
insert into "PaysISO" values('ÅLAND, ÎLES', 'AX');
insert into "PaysISO" values('ALBANIE', 'AL');
insert into "PaysISO" values('ALGÉRIE', 'DZ');
insert into "PaysISO" values('ALLEMAGNE', 'DE');
insert into "PaysISO" values('ANDORRE', 'AD');
insert into "PaysISO" values('ANGOLA', 'AO');
insert into "PaysISO" values('ANGUILLA', 'AI');
insert into "PaysISO" values('ANTARCTIQUE', 'AQ');
insert into "PaysISO" values('ANTIGUA ET BARBUDA', 'AG');
insert into "PaysISO" values('ANTILLES NÉERLANDAISES', 'AN');
insert into "PaysISO" values('ARABIE SAOUDITE', 'SA');
insert into "PaysISO" values('ARGENTINE', 'AR');
insert into "PaysISO" values('ARMÉNIE', 'AM');
insert into "PaysISO" values('ARUBA', 'AW');
insert into "PaysISO" values('AUSTRALIE', 'AU');
insert into "PaysISO" values('AUTRICHE', 'AT');
...

select * from PaysISO where Nom_ISO like '%ile%';

RecNo Nom_IsoCode_Iso
- -- 
 1 ÅLAND, ÎLESAX
 2 CAÏMANES, ÎLES KY
 3 CHRISTMAS, ÎLE CX
 4 COCOS (KEELING), ÎLES  CC
 5 COOK, ÎLES CK
 6 FALKLAND, ÎLES (MALVINAS)  FK
 7 FÉROÉ, ÎLESFO
 8 GÉORGIE DU SUD ET LES ÎLES SANDWICH DU SUD GS
 9 ÎLE DE MAN IM
10 ÎLES MINEURES ÉLOIGNÉES DES ÉTATS-UNIS UM
11 ÎLES VIERGES BRITANNIQUES  VG
12 ÎLES VIERGES DES ÉTATS-UNISVI
13 MARIANNES DU NORD, ÎLESMP
14 MARSHALL, ÎLES MH
15 NORFOLK, ÎLE   NF
16 SALOMON, ÎLES  SB
17 SVALBARD ET ÎLE JAN MAYEN  SJ
18 TURKS ET CAÏQUES, ÎLES TC

This is a Unicode (non-ICU) folded, unaccented LIKE:

 {"like",2,  SQLITE_UTF8, (void *) 
, likeFunc8},
 {"like",2,  SQLITE_UTF16,(void *) 
, likeFunc16   },
 {"like",3,  SQLITE_UTF8, (void *) 
, likeFunc8},
 {"like",3,  SQLITE_UTF16,(void *) 
, likeFunc16   },
 {"glob",2,  SQLITE_UTF8, (void *) 
, likeFunc8},
 {"glob",2,  SQLITE_UTF16,(void *) 
, likeFunc16   },
 {"glob",3,  SQLITE_UTF8, (void *) 
, likeFunc8},
 {"glob",3,  SQLITE_UTF16,(void *) 
, likeFunc16   },

The same happens for GLOB with 2-arg (UTF-16 version invoked):
select * from PaysISO where Nom_ISO glob '*ILE*';
(same output).

But I just noticed that glob with 3 args invokes the UTF-8 version:
select * from PaysISO where glob('*ILE*', Nom_ISO, '{');
(same output).

While the 3-args like still calls the UTF-16 version:
select * from PaysISO where like('%ile%', Nom_ISO, '{');
(same output).

In all examples above, I've used either plain ASCII or "weirder" 
accented letters, to further test my code.  Also doing this way shows 
that the native SQLite version is not doing anything.  (When things get 
strange, you start looking behind your shoulders.)

The VC++ debugger pops up each time and shows the caller is sqlite3.dll 
every time, so there is no man-in-the-middle attack against my poor 
extension.  I have only v3.6.18 and no modified/recompiled sqlite3.dll 
here.




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

Re: [sqlite] User-defined function invocation during triggers

[Pavel Ivanov]

With the next version of SQLite (which will be released this week) you
will be able to write a trigger on deletion of dependent rows where
you will just call your function.

Pavel

On Tue, Oct 13, 2009 at 1:19 PM, Igor Tandetnik  wrote:
> mwnn  wrote:
>> i am using triggers to delete dependent rows of a table when a
>> referred row is deleted. Is there a way for me to let SQLite call a
>> user-defined function for every row deleted in a trigger?
>
> Well, you could write your trigger like this:
>
> create trigger ... on delete ...
> begin
>    select mycustomfunction(id) from mytable where condition;
>    delete from mytable where condition;
> end;
>
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] User-defined function invocation during triggers

[Igor Tandetnik]

mwnn  wrote:
> i am using triggers to delete dependent rows of a table when a
> referred row is deleted. Is there a way for me to let SQLite call a
> user-defined function for every row deleted in a trigger?

Well, you could write your trigger like this:

create trigger ... on delete ...
begin
select mycustomfunction(id) from mytable where condition;
delete from mytable where condition;
end;

Igor Tandetnik


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

Re: [sqlite] Bug in date() function ??

[Igor Tandetnik]

Keith Roberts  wrote:
> From: http://www.sqlite.org/lang_datefunc.html
> 
> "Thus, for example, the data 2001-03-31 modified by '+1
> month' initially yields 2001-04-31, but April only has 30
> days so the date is normalized to 2001-05-01."
> 
> When I add '+1 month' to the example data, the example date
> has 31 days added to it. Which is correct.
> 
> When I subtract '-1 month' the date only gets 28 days
> subtracted from it.

By the same logic, subtracting one month from 2001-03-31 leads to 2001-02-31, 
but Feburary only has 28 days, so this gets normalized to 2001-03-03. It's not 
about the number of days. If you want to add or subtract a particular number of 
days, say so.

> Surely adding or subtracting a month modifier to a date
> should be the same number of days in each direction?

If that were the case, then these two equalities could not be both true:

date('2001-03-01', '-1 month') = '2001-02-01'
date('2001-03-01', '+1 month') = '2001-04-01'

Which one of the above do you believe is incorrect?

Igor Tandetnik


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

Re: [sqlite] Bug in date() function ??

[D. Richard Hipp]

On Oct 13, 2009, at 12:57 PM, Keith Roberts wrote:

> Just been messing about with the date functions, and there
> appears to be an inconsistency when adding a month
> modifier. I'm running Fedora 10.
>
> From: http://www.sqlite.org/lang_datefunc.html
>
> "Thus, for example, the data 2001-03-31 modified by '+1
> month' initially yields 2001-04-31, but April only has 30
> days so the date is normalized to 2001-05-01."

Did you happen to read the previous sentence in the documentation?

>
> When I add '+1 month' to the example data, the example date
> has 31 days added to it. Which is correct.
>
> When I subtract '-1 month' the date only gets 28 days
> subtracted from it.
>
> Surely adding or subtracting a month modifier to a date
> should be the same number of days in each direction?
>

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



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

[sqlite] Bug in date() function ??

[Keith Roberts]

Just been messing about with the date functions, and there 
appears to be an inconsistency when adding a month 
modifier. I'm running Fedora 10.

[root ~]# sqlite3
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> SELECT date('2001-03-31');
2001-03-31

sqlite> SELECT date('2001-03-31', '+1 month');
2001-05-01

sqlite> SELECT date('2001-03-31', '+31 day');
2001-05-01

sqlite> SELECT date('2001-03-31', '-31 day');
2001-02-28

sqlite> SELECT date('2001-03-31', '-1 month');
2001-03-03

sqlite> SELECT date('2001-03-31', '-28 day');
2001-03-03

sqlite>

From: http://www.sqlite.org/lang_datefunc.html

"Thus, for example, the data 2001-03-31 modified by '+1 
month' initially yields 2001-04-31, but April only has 30 
days so the date is normalized to 2001-05-01."

When I add '+1 month' to the example data, the example date 
has 31 days added to it. Which is correct.

When I subtract '-1 month' the date only gets 28 days 
subtracted from it.

Surely adding or subtracting a month modifier to a date 
should be the same number of days in each direction?

Kind Regards,

Keith Roberts

-
Websites:
http://www.php-debuggers.net
http://www.karsites.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] User-defined function invocation during triggers

[mwnn]

Hi all,
i am using triggers to delete dependent rows of a table when a   
referred row is deleted. Is there a way for me to let SQLite call a   
user-defined function for every row deleted in a trigger?

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

[sqlite] pthreadMutexEnter throws assertion (when it should not)

[Jan Neerbek]

Hello,

On several machines running Suse10.2 (gcc 4.1.2) we get the following 
assertion
prog: sqlite3.c:15173: pthreadMutexEnter: Assertion `p->id==1 || 
pthreadMutexNotheld(p)' failed.
Aborted

The problem does not seem to occur on Suse11 (gcc 4.3.2), our Ubuntu, nor 
on our Windows platforms.
We are able to reproduce the problem with both sqlite 3.6.18 and 3.6.3 
(have not tried with other versions)

Using the sqlite-amalgamation-3_6_18.zip package I can reproduce the 
problem with a small example. I have included the example main.c below.
The example is compiled as follows:
gcc -DSQLITE_THREADSAFE=1 -DSQLITE_DEBUG=1 -DSQLITE_OMIT_LOAD_EXTENSION -g 
 -pthread  -O2 sqlite3.c main.c -o testprog

The assertion occurs when using -O2, -O3, or -Os, but not when using -O.

My best guess at what is going wrong (disclaimer: I am just guessing here, 
feel free to skip this part) is as follows:
Inside pthreadMutexEnter sqlite do:
  pthread_mutex_lock(>mutex);
  p->owner = pthread_self();
  p->nRef++;
I believe the two last statements get swap'ed around by the optimizer, 
such that nRef++ occurs first. This leads to a possible race with the 
assertion in the beginning of the pthreadMutexEnter (another thread 
entering while the first thread is updating nRef and owner). 
The reason I think this is because:
1) gdb is not sure of which line it is at when inspecting the two above 
lines
2) If I insert a printf just before the assertion I can see that nRef is 1 
and owner==self (thus another thread has updated nRef but not yet owner)
Note pthreadMutexEnter gets called like 1 times before it fails for 
the first time.
Since newer compilers seems to be less likely to generate the race, it 
might be a known dangerous/faulty optimization pattern in gcc 4.1.2 that 
is causing the issue (however my friend google could not confirm that).

Regards,
Jan

main.c---
#include 
#include 
#include 
#include 

#include "sqlite3.h"


// Compile: gcc -DSQLITE_THREADSAFE=1 -DSQLITE_DEBUG=1 
-DSQLITE_OMIT_LOAD_EXTENSION -g  -pthread  -O2 sqlite3.c main.c -o 
testprog
// Run: ./testprog;rm test.db;./testprog;rm test.db;./testprog
// the problem usually occurs within 1-3 attemps (but only on Suse10.2)

//#threads= 2*NUMBER_OF_QUERIES
#define NUMBER_OF_QUERIES 1
//size of test db
#define NUMBER_OF_ROWS 1024*1024
#define NUMBER_OF_ROWS_PER_OUTPUT 1024*10


static void *read_db(sqlite3_stmt *statement, int id)
{
int i = 0;

for (;;)
{
int res = sqlite3_step(statement);
switch (res)
{
case SQLITE_DONE:
case SQLITE_BUSY:
//continue
break;
case SQLITE_ROW:
{
const unsigned char *text  = 
sqlite3_column_text(statement, 1);
 
if (i % NUMBER_OF_ROWS_PER_OUTPUT == 0)
{
printf("Thread(%d): \"%s\"\n", id, text);
}
i++;
break;
}
case SQLITE_MISUSE:
case SQLITE_ERROR:
default:
printf("Failed to read statement '%d'\n", statement);
res = SQLITE_DONE;
break;
}
if (res == SQLITE_DONE)
break; //done
}
return NULL;
}

static int populate_db(sqlite3 *db)
{
int res = SQLITE_OK;
const char *insert_item = "INSERT INTO MyTable VALUES(%d,'Test data 
for SQLite:%d')";
int size = strlen(insert_item);
char *buf = (char *) malloc((size + 1 + 20)*sizeof(char));
 
if ((res = sqlite3_exec(db, "BEGIN;", NULL, NULL, NULL)) != SQLITE_OK)
printf("Failed to start transaction.\n");

if (res == SQLITE_OK)
{
int i = 0;
int insert_max = NUMBER_OF_ROWS; //lots of data to warmup the 
cache
for (i=0;res==SQLITE_OK && iquery;
const char *filename = data->filename;

int res = sqlite3_open(filename, );

if (res == SQLITE_OK)
{
if (res == SQLITE_OK && (res = sqlite3_prepare(db, query, -1, 
, NULL)) != SQLITE_OK)
printf("Failed to prepare %s statement.\n", data->debugname);
 
read_db(resultSet, data->id);

if (resultSet != NULL)
sqlite3_finalize(resultSet);
sqlite3_close(db);
}
else
{

[sqlite] best device characteristic settings when using jffs2 file system ?

[O'Neill, Owen]

Hi Everyone,

I'm using sqlite on a JFFS2 file system (writing to NAND flash) so I'm
wondering what the best file system characteristics to report via the
xSectorSize and xDeviceCharacteristics methods are ?

http://www.sqlite.org/c3ref/io_methods.html

(JFFS2 is in summary a rotating log journaling file system with extra
wear levelling and other stuff making it more suitable for flash
devices)

My concerns for the 'best' settings are based on both the data security
and performance aspects.


Many thanks in advance for any advice on the subject (or even just what
the 'right' questions to ask the JFFS2 people are !)

Owen


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

Re: [sqlite] Hi, new member here (and also my first question)

[Pavel Ivanov]

> The pPrior or p pointer isn't null so it should've been
> freed without error IMHO. Can anybody tell me what's wrong with it? Thanks
> a lot in advance.

If "pPrior or p pointer" isn't null but was already freed then double
free can cause segmentation fault. In other words most probably you're
calling sqlite3_finalize on already finalized statement.

Pavel

On Tue, Oct 13, 2009 at 5:58 AM,   wrote:
> Hi there, I'm a new member of the mailing list. Nice to meet you all.
>
> BTW, I've got one problem that's been bugging me for weeks.
>
> Occasionally (not always), I got a seg fault at "static void
> sqlite3MemFree(void *pPrior)". It happened when I do sqlite3_reset or
> sqlite3_finalize. The pPrior or p pointer isn't null so it should've been
> freed without error IMHO. Can anybody tell me what's wrong with it? Thanks
> a lot in advance.
>
>
> Fare thee well,
> Bawenang R. P. P.
>
> 
> "If a picture is worth a thousand words, an animations is worth a thousand
> pictures. And to take that a step further, a game is worth a thousand
> animations." – Peter Raad, Executive Director, The Guildhall at SMU
>
>
> --
>
> http://www.its.ac.id
> ___
> 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] Need Help SQL

[Rick Ratchford]

Thanks! 

Cheers!
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
#>boun...@sqlite.org] On Behalf Of sub sk79
#>Sent: Monday, October 12, 2009 9:35 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Need Help SQL
#>
#>Hi!,
#>
#>Here is a non-math version using PL/SQL date operators and functions
#>available in StepSqlite (https://www.metatranz.com/stepsqlite/).
#>Hopefully this should be easier to follow.
#>
#>You can compile the below code directly to a win32 dll on the
#>StepSqlite website and then use it in your VB code.
#>
#>Assumes Date column is in '-MM-DD'. If using a different format,
#>just call   DateTime.setDateFormat()   to set proper format.
#>
#>
#>create table items(ID integer, Date date, Price float);
#>PACKAGE BODY MyPackage IS
#>PROCEDURE get_prices (start_month char, start_day char, end_month
#>char, end_day char  ) IS
#>BEGIN
#>-- n_* below are dates normalized to fall in a given year, here I
#>chose year 2000 because its a leap year and has all possible
#>day-numbers for proper normalization.
#>FOR item IN
#>(SELECT id, to_char(date, 'MM') mon, to_char(date, 'DD') day, price
#> FROM (SELECT id, date, price,
#> to_date('2000-' || start_month||'-'||start_day,
#>'-MM-DD') n_start,
#> to_date('2000-' || end_month  ||'-'||end_day,
#>'-MM-DD') n_end,
#> to_date(to_char(date, '2000-MM-DD'), '-MM-DD')
#>n_date
#>FROM items
#>  )
#> WHERE (n_start < n_end AND n_date between n_start and n_end)
#>OR (n_start > n_end AND n_date NOT between n_end and n_start)
#> ORDER BY to_char(date, 'MM-DD')
#> )
#>LOOP
#>DBMS_OUTPUT.put_line(item.id || '  ' || item.mon || '
#>'||item.day||'  '||item.price);
#>END LOOP;
#>END;
#>
#>BEGIN
#>insert into items(id, date, price) values(1,'2004-01-01',  1.1);
#>insert into items(id, date, price) values(2,'2004-02-01',  1.1);
#>insert into items(id, date, price) values(3,'2004-02-16',  1.1);
#>insert into items(id, date, price) values(4,'2004-10-01',  1.1);
#>insert into items(id, date, price) values(5,'2004-10-22',  1.1);
#>
#>insert into items(id, date, price) values(51,'2005-01-01',  5.1);
#>insert into items(id, date, price) values(52,'2005-02-01',  5.1);
#>insert into items(id, date, price) values(53,'2005-02-16',  5.1);
#>insert into items(id, date, price) values(54,'2005-10-01',  5.1);
#>insert into items(id, date, price) values(55,'2005-10-22',  5.1);
#>
#>insert into items(id, date, price) values(61,'2006-01-01',  6.1);
#>insert into items(id, date, price) values(62,'2006-02-01',  6.1);
#>insert into items(id, date, price) values(63,'2006-02-16',  6.1);
#>insert into items(id, date, price) values(64,'2006-10-01',  6.1);
#>insert into items(id, date, price) values(65,'2006-10-22',  6.1);
#>
#>DBMS_OUTPUT.put_line('Price data Range: 02-15 to 10-21');
#>get_prices('02', '15', '10', '21');
#>
#>DBMS_OUTPUT.put_line('Price data Range: 10-21 to 02-15');
#>get_prices('10', '21', '02','15');
#>
#>rollback;
#>END;
#>
#>
#>Result:
#>
#>Price data Range: 02-15 to 10-21
#>3  02  16  1.1
#>53  02  16  5.1
#>63  02  16  6.1
#>4  10  01  1.1
#>54  10  01  5.1
#>64  10  01  6.1
#>Price data Range: 10-21 to 02-15
#>1  01  01  1.1
#>51  01  01  5.1
#>61  01  01  6.1
#>2  02  01  1.1
#>52  02  01  5.1
#>62  02  01  6.1
#>5  10  22  1.1
#>55  10  22  5.1
#>65  10  22  6.1
#>
#>
#>
#>Regards,
#>SK
#>___
#>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] Encoding specs & functions overloading

[Pavel Ivanov]

> I'm using the 3.6.18 Windows dll downloaded direct from the site.
>
> I just re-checked that.

I believe you need to show us your sql query. Maybe something in it
forces SQLite to use UTF-16 version of the function.

Pavel

On Mon, Oct 12, 2009 at 11:44 PM, Jean-Christophe Deschamps
 wrote:
> Thank you for your fast answer.
>
>
>>I'm surprised by this too.  In fact, I cannot reproduce it.
>
> I'm using the 3.6.18 Windows dll downloaded direct from the site.
>
> I just re-checked that.
>
>
>
>
> ___
> 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] low-level view of data values?

[Fred Williams]

- Original Message - 
From: "Dan Phillips" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, October 13, 2009 2:40 AM
Subject: Re: [sqlite] low-level view of data values?


> On Tue, Oct 13, 2009 at 2:01 AM, Robert Simpson  
> wrote:
>> I'm pretty sure I do store them as strings -- SQLite doesn't have a
>> "decimal" datatype, and "double" doesn't cut the precision mustard for
>> emulating "decimal".
>
> Yes, but I believe the Decimals-as-text will only work if the column
> is declared as TEXT rather than as NUMERIC. If it is NUMERIC, then the
> Decimals will be end up being stored as REALs. You can verify this by
> doing a "select typeof(MyField)" SQL statement.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


The best way I have found to manage decimal (Business math) with most all 
the databases is to use integers and multiply and divide by the decimal 
offset (i.e. 10, 100, 1000) for presentation purposes,  doing my own 
"bankers rounding"  in code.  Big hassle, but transports well.

As long as computer scientists are driving the bus, business math will 
remain a step child, although probably 90+ % of all db apps are business 
related, IMHO.  No offence intended Dr. H!

Fred

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

[sqlite] Hi, new member here (and also my first question)

[benang]

Hi there, I'm a new member of the mailing list. Nice to meet you all.

BTW, I've got one problem that's been bugging me for weeks.

Occasionally (not always), I got a seg fault at "static void
sqlite3MemFree(void *pPrior)". It happened when I do sqlite3_reset or
sqlite3_finalize. The pPrior or p pointer isn't null so it should've been
freed without error IMHO. Can anybody tell me what's wrong with it? Thanks
a lot in advance.


Fare thee well,
Bawenang R. P. P.


"If a picture is worth a thousand words, an animations is worth a thousand
pictures. And to take that a step further, a game is worth a thousand
animations." – Peter Raad, Executive Director, The Guildhall at SMU


--

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

Re: [sqlite] sqlite build issues on IBM's HP-UX 11i

[Kiran Kumar.M.R]

Hi,
We have used Sqlite-3.6.16 on both 
HP-UX PA-RISC( Compiler gcc 4.1.1 and aCC A.03.95)
and newer HP-UX IA64 machines ( aCC A06.23).

Compilation of amalgamation file sqlite3.c is usually problematic.
libsqlite.sl is compiled without using amalgamation.
All functionality works fine.

While debugging in gdb, symbol information will not appear.
We load sqlite3 object files from the compile environment into the debugger.
This works and debugging can be done with all symbol information.

So I suggest compile without amalgamation and save a copy of the object
files.
Load object files in debugger whenever required.
 
Regards,
Kiran



***
This e-mail and attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed
above. Any use of the information contained herein in any way (including,
but not limited to, total or partial disclosure, reproduction, or
dissemination) by persons other than the intended recipient's) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!

***


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Monday, October 12, 2009 6:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite build issues on IBM's HP-UX 11i


On Oct 12, 2009, at 8:55 AM, Dr. David Kirkby wrote:
>> /var/tmp//ccv990We.s:587: Warning: .stabs: description field '1161d' 
>> too big, try a different debug format

>> if a developer wants to take a look, and does not have access to an 
>> HP-UX machine, I can give you an account on the machine, where you 
>> can test it yourself.
>
> Did any of the developers see this? Is there a more appropriate list 
> for bug reports like this?

Thank you for your kind offer of access to an HPUX machine.

However, the problem here is that the stabs debugging format does not
support source code files longer than 32768 lines.  The sqlite3.c source
file is around 110,000 lines long.  Hence, stabs is simply not going to work
with SQLite.  This is a fundamental limitation of the compiler on HPUX. No
amount of debugging on an HPUX machine is going to fix this, I'm afraid.

SQLite should still work on HPUX, despite the avalanche of warnings.   
You just won't get useful information out of a symbolic debugger.

If in the future you encounter problems in HPUX that we cannot replicate on
one of our local machines (linux, mac, windows, on x86 and x86_64) then we
may well take you up on your offer to debug on HPUX/pa-risc.  But at this
time, that would not be useful.  Thanks.

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



___
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] low-level view of data values?

[Dan Phillips]

On Tue, Oct 13, 2009 at 2:01 AM, Robert Simpson  wrote:
> I'm pretty sure I do store them as strings -- SQLite doesn't have a
> "decimal" datatype, and "double" doesn't cut the precision mustard for
> emulating "decimal".

Yes, but I believe the Decimals-as-text will only work if the column
is declared as TEXT rather than as NUMERIC. If it is NUMERIC, then the
Decimals will be end up being stored as REALs. You can verify this by
doing a "select typeof(MyField)" SQL statement.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users