Re: [sqlite] SELECT and UPDATE in single query

[Richard Baron Penman]

> If you want to know which rows were updated regardless of the key, what
> you need is a column to hold a unique value for each update transaction,
> and set it as part of the UPDATE.  You could add a datetime column,
> for example, if the time resolution is fine enough.

Good idea. I found setting the status column to epoch was considerably faster:

epoch = int(datetime.datetime.now().strftime('%s%f'))
conn.execute('UPDATE queue SET status=? WHERE key in (SELECT key FROM
queue WHERE status=? LIMIT ?);', (epoch, 0, limit))
rows = conn.execute('SELECT key FROM queue WHERE status=?', (epoch,))
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT and UPDATE in single query

[Keith Medcalf]

>> There is sqlite3_update_hook() function that returns rowid of changed record.
 
> sounds promising, but apparently not supported by the python wrapper.

Not the included sqlite3/pysqlite dbiapi wrapper -- though it is supported by 
Roger Binns APSW wrapper.

http://code.google.com/p/apsw/

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] SELECT and UPDATE in single query

[Richard Baron Penman]

On Fri, Jan 25, 2013 at 1:36 AM, Yongil Jang  wrote:
> There is sqlite3_update_hook() function that returns rowid of changed
> record.

sounds promising, but apparently not supported by the python wrapper.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT and UPDATE in single query

[James K. Lowden]

On Thu, 24 Jan 2013 16:47:02 +1100
Richard Baron Penman  wrote:

> How to find which keys have been updated from this query?

That's the problem with "limit N", right?  It's not based on the data.
Not only do you not know which rows were updated, you don't know which
ones were *selected* except by inspection.  

If you want to process a subset of rows with status=0, a better
approach is to use the primary key.  If the key is a date, say,
restrict the selection to a range of dates, perhaps a year or a
month, and increment the range on each iteration.  

If you want to know which rows were updated regardless of the key, what
you need is a column to hold a unique value for each update transaction,
and set it as part of the UPDATE.  You could add a datetime column,
for example, if the time resolution is fine enough.  

HTH.  

--jkl


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

Re: [sqlite] SELECT and UPDATE in single query

[Yongil Jang]

Penman wrote:
How to find which keys have been updated from this query?

There is sqlite3_update_hook() function that returns rowid of changed
record.

Regards,
Yongil Jang.
On Jan 24, 2013 11:10 PM, "Igor Tandetnik"  wrote:

> On 1/24/2013 12:47 AM, Richard Baron Penman wrote:
>
>> How to find which keys have been updated from this query?
>>
>
> You can't, really. If you need a list of keys (or in fact a list of
> anything from the database), you need to run a SELECT statement. At which
> point you are back where you started and might as well keep the original
> design.
>
> If you really don't want to do that for some reason, you could create an
> AFTER UPDATE trigger on the table, which would call a custom function,
> passing each key to it as the status is being updated.
> --
> 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] SELECT and UPDATE in single query

[Igor Tandetnik]

On 1/24/2013 12:47 AM, Richard Baron Penman wrote:

How to find which keys have been updated from this query?


You can't, really. If you need a list of keys (or in fact a list of 
anything from the database), you need to run a SELECT statement. At 
which point you are back where you started and might as well keep the 
original design.


If you really don't want to do that for some reason, you could create an 
AFTER UPDATE trigger on the table, which would call a custom function, 
passing each key to it as the status is being updated.

--
Igor Tandetnik

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

Re: [sqlite] SELECT and UPDATE in single query

[Richard Baron Penman]

> Why process only N at a time, Richard?

There are a number of workers who request unprocessed jobs from the queue.
But the queue is too big to hold in memory all at once.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT and UPDATE in single query

[Richard Baron Penman]

Thanks for tip about the redundant index.

How to find which keys have been updated from this query?


On Thu, Jan 24, 2013 at 3:32 PM, Keith Medcalf  wrote:
>> I have a table like this:
>>
>> CREATE TABLE queue (
>> key TEXT NOT NULL PRIMARY KEY UNIQUE,
>> status INTEGER
>> );
>> CREATE INDEX IF NOT EXISTS keys ON queue (key);
>
> Your index is redundant.  There is already a unique index on key since it is 
> a primary key.
>
> It should probably be:
>
> create unique index if not exists keys on queue(status, key);
>
>> And then I process it like this, N keys at a time:
>>
>> SELECT key FROM queue WHERE status=0 LIMIT N;
>> BEGIN TRANSACTION;
>> for key in keys:
>> UPDATE queue SET status=1 WHERE key=key;
>> END TRANSACTION;
>>
>> How can this SELECT and UPDATE be combined more efficiently?
>
> update queue set status=1 where key in (select key from queue where status=0 
> limit N);
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT and UPDATE in single query

[James K. Lowden]

On Wed, 23 Jan 2013 21:32:20 -0700
"Keith Medcalf"  wrote:

> > And then I process it like this, N keys at a time:
> > 
> > SELECT key FROM queue WHERE status=0 LIMIT N;
> > BEGIN TRANSACTION;
> > for key in keys:
> > UPDATE queue SET status=1 WHERE key=key;
> > END TRANSACTION;
> > 
> > How can this SELECT and UPDATE be combined more efficiently?
> 
> update queue set status=1 where key in (select key from queue where
> status=0 limit N);

Why process only N at a time, Richard?  If you remove that requirement
the query is much simpler, because "key" is unique:

update queue set status=1 where status = 0;

Besides being nonstandard, the results using "limit N" are
nondeterministic. You don't know which rows are updated, only that (up
to) N are.  

HTH.  

--jkl


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

Re: [sqlite] SELECT and UPDATE in single query

[Igor Tandetnik]

On 1/23/2013 11:22 PM, Richard Baron Penman wrote:

And then I process it like this, N keys at a time:

SELECT key FROM queue WHERE status=0 LIMIT N;
BEGIN TRANSACTION;
for key in keys:
 UPDATE queue SET status=1 WHERE key=key;
END TRANSACTION;


How can this SELECT and UPDATE be combined more efficiently?


Something like this perhaps:

update queue set status = 1 where status = 0 and key <
  (select key from queue where status=0 order by key limit 1 offset N);

--
Igor Tandetnik

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

Re: [sqlite] SELECT and UPDATE in single query

[Keith Medcalf]

> I have a table like this:
> 
> CREATE TABLE queue (
> key TEXT NOT NULL PRIMARY KEY UNIQUE,
> status INTEGER
> );
> CREATE INDEX IF NOT EXISTS keys ON queue (key);

Your index is redundant.  There is already a unique index on key since it is a 
primary key.

It should probably be:

create unique index if not exists keys on queue(status, key);
 
> And then I process it like this, N keys at a time:
> 
> SELECT key FROM queue WHERE status=0 LIMIT N;
> BEGIN TRANSACTION;
> for key in keys:
> UPDATE queue SET status=1 WHERE key=key;
> END TRANSACTION;
> 
> How can this SELECT and UPDATE be combined more efficiently?

update queue set status=1 where key in (select key from queue where status=0 
limit N);

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org



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