Re: [sqlite] What is the column value after ALTER TABLE ADD COLUMN?

[Tomash Brechko]

On Fri, May 18, 2007 at 14:00:21 -0500, Doug Nebeker wrote:
> UPDATE xyz SET newcol=function(other_column) WHERE newcol=null;
>  
> Both of the above fail. What is the value in newcol?

The value is NULL, however you have to say "IS NULL":

  UPDATE xyz SET newcol=function(other_column) WHERE newcol IS NULL;

NULLs aren't equal to each other:

sqlite> .nullvalue 
sqlite> select NULL = NULL;




-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: What is the column value after ALTER TABLE ADD COLUMN?

[Igor Tandetnik]

Doug Nebeker <[EMAIL PROTECTED]>
wrote: 

UPDATE xyz SET newcol=function(other_column) WHERE newcol=null;


NULL is never equal to anything, not even another NULL. Make it

UPDATE xyz SET newcol=function(other_column) WHERE newcol is null;

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] What is the column value after ALTER TABLE ADD COLUMN?

[Doug Nebeker]

I must be missing something obvious and I'm hoping someone can help me
out.
 
I have an existing table and add a new column:

ALTER TABLE xyz ADD COLUMN newcol TEXT;
 
Next I want to set some default values to the new column.  Because this
code could potentially get executed later, I'm trying to be safe:
 
UPDATE xyz SET newcol=function(other_column) WHERE newcol=null;
UPDATE xyz SET newcol=function(other_column) WHERE newcol='';
 
Both of the above fail. What is the value in newcol?
 
Thanks for any ideas.
 
Doug


This email was sent to you by Reuters, the global news and information company. 
To find out more about Reuters visit www.about.reuters.com

Any views expressed in this message are those of the individual sender, 
except where the sender specifically states them to be the views of Reuters 
Limited.

Reuters Limited is part of the Reuters Group of companies, of which Reuters 
Group PLC is the ultimate parent company.
Reuters Group PLC - Registered office address: The Reuters Building, South 
Colonnade, Canary Wharf, London E14 5EP, United Kingdom
Registered No: 3296375
Registered in England and Wales

RE: [sqlite] One more SQLite threading question

[Ken]

Dan,
 Can you explain to me how within the context of the test_server.c code that 
the 
 sqlite3_enable_shared_Cache will improve concurrency, for a single DB file 
access?
 
 I just don't see where any concurrency is gained. Sure maybe some memory 
savings. But I must be brain dead, because I don't see how it could improve 
concurrency given that a single thread is used to perform db access. And all 
clients are queued and blocked upon the single threads message queue.
 
 Thanks,
 Ken
 
 
 

Dan Kennedy <[EMAIL PROTECTED]> wrote: > Which in that case whats the point of 
a shared cache? 
> What is it shared against, since all threads must send 
> data to the shared server anyways and none may access
> it concurrently.

The idea is to have a single cache shared accessed by
more than one logical connection (read: more than one
transaction context). It's meant to reduce IO and memory
usage in the case that a process opens more than one 
connection to the same database file.

It's quite a specialised feature. Only really useful
if you are implementing an embedded server.

Dan



> One thing that Other database engines do is allow read and writes to occur 
> without blocking. That is a Reader never blocks a writer and a Writer never 
> blocks a reader. SQLITE does not do this, Only a single writer or Multiple 
> readers, but not both concurrently.
>  
> I'm not trying to pick on sqlite, just pointing out that it really doesn't 
> perform multi threading or even conncurrent access very well in a read/write 
> environment. Read Only, its great. Single threaded Read/Write ... Very good 
> as well.
>  
>  Regards,
>  Ken
>  
> 
> 
> 
> 
> Doug Nebeker  wrote: > > Yes I did the same experiment with a lock that made 
> thread A wait  
> > > until B was finished. So actually only one thread can be active at
> the time.
> > > I don't see how the outcome of this experiment can be of  any 
> > > interest, as there is no time reduction any longer. But your  guess
> is 
> > > right that, it works.
> > 
> >How would multiple threads be faster than a single one when you are
> accessing a single resource?
> 
> Assumably the thread that is accessing the database either spends some
> time gathering data to write
> or processing data it read.  The single resource isn't in use during
> that time.
> 
> This email was sent to you by Reuters, the global news and information 
> company. 
> To find out more about Reuters visit www.about.reuters.com
> 
> Any views expressed in this message are those of the individual sender, 
> except where the sender specifically states them to be the views of Reuters 
> Limited.
> 
> Reuters Limited is part of the Reuters Group of companies, of which Reuters 
> Group PLC is the ultimate parent company.
> Reuters Group PLC - Registered office address: The Reuters Building, South 
> Colonnade, Canary Wharf, London E14 5EP, United Kingdom
> Registered No: 3296375
> Registered in England and Wales
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] One more SQLite threading question

[Ken]

I would be interested in a version of SQLITE that handled threading in a much 
cleaner way. I have a need for a single process version that is threaded. 
 
 But, where  SQLITE locking is concerned each thread is really like a seperate 
Database connection. The locking occurs as a part of the Pager locking which is 
whole file for the duration of the transaction. AFAIK, the shared cache API is 
pretty worthless as the only way to implement this is through a single "server" 
thread. Which in that case whats the point of a shared cache? What is it shared 
against, since all threads must send data to the shared server anyways and none 
may access it concurrently.
 
 One thing that Other database engines do is allow read and writes to occur 
without blocking. That is a Reader never blocks a writer and a Writer never 
blocks a reader. SQLITE does not do this, Only a single writer or Multiple 
readers, but not both concurrently.
 
 I'm not trying to pick on sqlite, just pointing out that it really doesn't 
perform multi threading or even conncurrent access very well in a read/write 
environment. Read Only, its great. Single threaded Read/Write ... Very good as 
well.
 
 Regards,
 Ken
 
 
 
 

Doug Nebeker <[EMAIL PROTECTED]> wrote: > > Yes I did the same experiment with 
a lock that made thread A wait  
> > until B was finished. So actually only one thread can be active at
the time.
> > I don't see how the outcome of this experiment can be of  any 
> > interest, as there is no time reduction any longer. But your  guess
is 
> > right that, it works.
> 
>How would multiple threads be faster than a single one when you are
accessing a single resource?

Assumably the thread that is accessing the database either spends some
time gathering data to write
or processing data it read.  The single resource isn't in use during
that time.

This email was sent to you by Reuters, the global news and information company. 
To find out more about Reuters visit www.about.reuters.com

Any views expressed in this message are those of the individual sender, 
except where the sender specifically states them to be the views of Reuters 
Limited.

Reuters Limited is part of the Reuters Group of companies, of which Reuters 
Group PLC is the ultimate parent company.
Reuters Group PLC - Registered office address: The Reuters Building, South 
Colonnade, Canary Wharf, London E14 5EP, United Kingdom
Registered No: 3296375
Registered in England and Wales



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] One more SQLite threading question

[Doug Nebeker]

> > Yes I did the same experiment with a lock that made thread A wait  
> > until B was finished. So actually only one thread can be active at
the time.
> > I don't see how the outcome of this experiment can be of  any 
> > interest, as there is no time reduction any longer. But your  guess
is 
> > right that, it works.
> 
>How would multiple threads be faster than a single one when you are
accessing a single resource?

Assumably the thread that is accessing the database either spends some
time gathering data to write
or processing data it read.  The single resource isn't in use during
that time.

This email was sent to you by Reuters, the global news and information company. 
To find out more about Reuters visit www.about.reuters.com

Any views expressed in this message are those of the individual sender, 
except where the sender specifically states them to be the views of Reuters 
Limited.

Reuters Limited is part of the Reuters Group of companies, of which Reuters 
Group PLC is the ultimate parent company.
Reuters Group PLC - Registered office address: The Reuters Building, South 
Colonnade, Canary Wharf, London E14 5EP, United Kingdom
Registered No: 3296375
Registered in England and Wales



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] One more SQLite threading question

[Martin Gentry]

Yeah, in the face of "mostly" I was guaranteed to assume it should still be 
avoided (I may need to not make many restrictions where its used). 

Thanks

  

-Original Message-
From: Dan Kennedy <[EMAIL PROTECTED]>
Date: Fri, 18 May 2007 13:31:10 
To:sqlite-users@sqlite.org
Subject: Re: [sqlite] One more SQLite threading question

On Thu, 2007-05-17 at 18:26 -0400, Martin Gentry wrote:
> Can you be a bit more specific? :-)  I ask because this is immediately 
> relevant to some code I'm writing today, and have been operating on the 
> understanding that I should honour the restriction.  I'm fine with honouring 
> the restriction if required, but it might make my life easier if I don't 
> have to.

The current official position, as I understand it, is that you can
pass handles between threads. There are no known reasons that this
will not work. But it's been a source of bugs in the past, and I 
personally wouldn't risk it if I had the choice. Especially if
I thought the code could be deployed with a variety of different
OS's or kernel versions. But that's just an opinion.

Tricky things, threads.

Dan.

> - Original Message - 
> From: <[EMAIL PROTECTED]>
> To: 
> Sent: Thursday, May 17, 2007 5:01 PM
> Subject: Re: [sqlite] One more SQLite threading question
> 
> 
> "Martin Gentry" <[EMAIL PROTECTED]> wrote:
> > Just as an FYI on the threading ...
> > http://www.sqlite.org/capi3ref.html#sqlite3_open
> >
> > "The returned sqlite3* can only be used in the same thread in which it was
> > created. It is an error to call sqlite3_open() in one thread then pass the
> > resulting database handle off to another thread to use. This restriction 
> > is
> > due to goofy design decisions (bugs?) in the way some threading
> > implementations interact with file locks."
> >
> 
> That restriction is due to bugs in GLIBC or maybe the Linux Kernel
> (I'm not sure which) which have been resolved.  And for that matter,
> more recent versions of SQLite work around the bugs even if they
> are there.  So you can mostly ignore this now.  Mostly.
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] One more SQLite threading question

[Dan Kennedy]

On Thu, 2007-05-17 at 18:26 -0400, Martin Gentry wrote:
> Can you be a bit more specific? :-)  I ask because this is immediately 
> relevant to some code I'm writing today, and have been operating on the 
> understanding that I should honour the restriction.  I'm fine with honouring 
> the restriction if required, but it might make my life easier if I don't 
> have to.

The current official position, as I understand it, is that you can
pass handles between threads. There are no known reasons that this
will not work. But it's been a source of bugs in the past, and I 
personally wouldn't risk it if I had the choice. Especially if
I thought the code could be deployed with a variety of different
OS's or kernel versions. But that's just an opinion.

Tricky things, threads.

Dan.

> - Original Message - 
> From: <[EMAIL PROTECTED]>
> To: 
> Sent: Thursday, May 17, 2007 5:01 PM
> Subject: Re: [sqlite] One more SQLite threading question
> 
> 
> "Martin Gentry" <[EMAIL PROTECTED]> wrote:
> > Just as an FYI on the threading ...
> > http://www.sqlite.org/capi3ref.html#sqlite3_open
> >
> > "The returned sqlite3* can only be used in the same thread in which it was
> > created. It is an error to call sqlite3_open() in one thread then pass the
> > resulting database handle off to another thread to use. This restriction 
> > is
> > due to goofy design decisions (bugs?) in the way some threading
> > implementations interact with file locks."
> >
> 
> That restriction is due to bugs in GLIBC or maybe the Linux Kernel
> (I'm not sure which) which have been resolved.  And for that matter,
> more recent versions of SQLite work around the bugs even if they
> are there.  So you can mostly ignore this now.  Mostly.
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-