Counter variable in SQLite

Question

I'm working on a simple todo app that has a column for each day of the week. On startup, the app checks to see if there are any incomplete tasks from before the current date. If so, they're moved to the top of the current date's column.

The app is cloud-based, but the tasks are backed up for offline mode with an SQLite db. I can easily move the tasks by updating their date property, but I need the order property of each task to increment starting at 0 to place them at the top.

I need to be able to define a count variable in SQLite alone and increment it with each update that's performed. I know this code doesn't work, but it's an easy way of explaining what needs to be done:

count = 0

UPDATE `tasks` 
   SET `date` = '2010-01-04', 
       `order` = `count`++ 
 WHERE `date` < '2010-01-04'

I'd rather not use a temporary table or use a counter outside of SQLite, if possible.

@OMG Ponies - I'm starting to think it's just not possible all in SQLite. Here's a visual to better explain what I'd like to do: http://post.ly/HGI2

Answer 1

SQLite is a great tool, but I'd be surprised if this was possible using JUST SQLite. T-SQL support isn't that great within that app.

You could probably update the existing value in the record; adding or subtracting from it, but that would require the records to already be in order.

Answer 2

Actually, you can use addition in Sqlite - check out http://www.sqlite.org/lang_expr.html ; you just need to use an extra UPDATE statement to increment the counter and an inner query to get it again. Make sure to do this all in a Transaction or else you'll get concurrency problems.

Answer 3

Hello destroytoday,

it sounds to me that you'd like a a count column for each row. If i were you, i think you could do the call as

UPDATE `tasks` 
SET 
`date` = '2010-01-04', 
count = count + 1
WHERE `date` < '2010-01-04'

lemme know how that works...

Answer 4

Hello destroytoday,

i would do it with a second SQL-Statement:

UPDATE `tasks`
SET `date` = '2010-01-04',
count = ( SELECT COUNT(`task_id`) FROM `tasks` ) + 1
WHERE `date` < '2010-01-04'

not tested but should work!

cheers