9

Things

Posted in Productivity by alexwasserman 2794 days ago

The new Things 2 uses a SQLITE database, not XML.

Had to write a quick parsing script to format nicely, etc.

Pull the script from here:

http://www.alexwasserman.com/code_geeklets.htm

Then point a script geeklet at it. The sample Geeklet attached runs the script from this directory: $HOME/Documents/Programming/Scripts/Things/Things.sh Put the script wherever you want and modify to suit yourself.

Thanks to Andy for his help below.

Enjoy.


Geeklet files to download

Comments

User Avatar
alexwasserman 2775 days ago
Just noticed this picks up on completed items.

Update the script to:

sqlite3 -line $HOME/Library/Application\ Support/Cultured\ Code/Things\ beta/ThingsLibrary.db "select ZTITLE from ZTHING where ZTRASHED=0 and ZSTATUS=0" | grep . | awk -F= '{print "- " $2}'
User Avatar
andypullen 2746 days ago
Still displays scheduled items. The script below should only display things in Today and Next.

sqlite3 -line $HOME/Library/Application\ Support/Cultured\ Code/Things\ beta/ThingsLibrary.db "select ZTITLE from ZTHING where ZTRASHED=0 and ZSTATUS=0 and ZSTART=1 and ZFOCUSLEVEL1!=2" | grep . | awk -F= '{print "-" $2}'
User Avatar
evansiegel 2726 days ago
Holy crap! This is great! Since upgrading to the beta I'm been hunting for a great way to update my geektool. Thanks!

In what order does this list my todos? Is it in the order that they were created? Is there a way for me to sort them by due date?
User Avatar
alexwasserman 2706 days ago
@evansiegel -

You'll want to update the SQL command to end with:

order by ZDUEDATE

So, something like:

select ZTITLE from ZTHING where ZTRASHED=0 and ZSTATUS=0 and ZSTART=1 and ZFOCUSLEVEL1 !=2 order by ZDUEDATE DESC;

DESC is descending order or due date. Change that to ASC for ascending order of due date.

The whole thing would be:

sqlite3 -line $HOME/Library/Application\ Support/Cultured\ Code/Things\ beta/ThingsLibrary.db "select ZTITLE from ZTHING where ZTRASHED=0 and ZSTATUS=0 and ZSTART=1 and ZFOCUSLEVEL1 !=2 order by ZDUEDATE;" | grep . | awk -F= '{print "-" $2}'
User Avatar
andypullen 2651 days ago
Since out of beta, the path is now $HOME/Library/Containers/com.culturedcode.things/Data/Library/Application\ Support/Cultured\ Code/Things/ThingsLibrary.db

However, Things locks the database while it's open so the sqlite3 command won't trigger. While Things closed, it works fine. Any thoughts?

The whole code:

sqlite3 -line $HOME/Library/Containers/com.culturedcode.things/Data/Library/Application\ Support/Cultured\ Code/Things/ThingsLibrary.db "select ZTITLE from ZTHING where ZTRASHED=0 and ZSTATUS=0 and ZSTART=1 and ZFOCUSLEVEL1 !=2 order by ZDUEDATE;" | grep . | awk -F= '{print "-" $2}'
User Avatar
alexwasserman 2651 days ago
@Andy - I have Things open, and Geektool running and I'm fine.

I can run from the command line without issues either.

What error or output are you seeing?
User Avatar
andypullen 2651 days ago
@alexwasserman

When Things 2.0 is open, I get a "Error: database is locked" message in Terminal. If it's closed, it returns the task list as it should. It seems like Things uses a read-lock on the database. But it's working for you?
User Avatar
nigi10 2643 days ago
@andypullen

I got the same error but I think a solution would be to copy the database file before reading from it. So for example

cp $HOME/Library/Containers/com.culturedcode.things/Data/Library/Application\ Support/Cultured\ Code/Things/ThingsLibrary.db $HOME/Library/Containers/com.culturedcode.things/Data/Library/Application\ Support/Cultured\ Code/Things/ThingsLibraryUnlocked.db;

sqlite3 -line $HOME/Library/Containers/com.culturedcode.things/Data/Library/Application\ Support/Cultured\ Code/Things/ThingsLibraryUnlocked.db "select ZTITLE from ZTHING where ZTRASHED=0 and ZSTATUS=0 and ZSTART=1 and ZFOCUSLEVEL1 !=2 order by ZDUEDATE;" | grep . | awk -F= '{print "-" $2}';

It is maybe not as elegant and takes a little more time but as long as your database is small enough this should work.
User Avatar
alexwasserman 2641 days ago
@Andy, the first time I tested I didn't get a lock error, a couple of weeks ago.

Now I do, perhaps I've upgraded versions?

I don't run Things that often, just because GeekTool keeps the list of my desktop, so I don't find it a big problem if the GeekLet dies while it's open, then works again as soon as it shuts, which seems to be the case here.

@Nigi10, rather than copying the whole database each time, you could maintain a text file of the data, and just echo it instead. Something similar is done for the weather geeklets where they run an empty script to copy down a png from a weather website which is then displayed with another image geeklet.

Here, use a script geeklet to write the output to a text file. Check for the error condition, and just don't touch the file if it happens. As soon as it can read the file, you'll echo out the updated data.

You could actually do the above in a single more complex script, or actually put in into a script file and run it.

Plenty of options here, largely depends on your usage of Things, tbh. You could write your script to only copy if you get a write-error, etc.
User Avatar
justingerard 2633 days ago
Could someone please write such a script? I'd love to have a working script for this as school starts.
User Avatar
alexwasserman 2629 days ago
Turns out that you can't upload .sh scripts.

I've put it here:

http://www.alexwasserman.com/apps/things-geeklet.html

You can just point your Geeklet straight at the script. Run it the first time without Things open. It'll cache the data.

After that, if it detects the database is locked it'll use the cache. If not, it'll show current data and refresh the cache each time.
User Avatar
justingerard 2628 days ago
How could I also display the inbox in the script? I'm not good with sql so i'd like to be able to display inbox, next, scheduled, and someday. any ideas?
User Avatar
alexwasserman 2628 days ago
Sure, if I could work out the schema. But it's not intuitive, and as far as I know, CulturedCode don't provide a data dictionary, or even any documentation on it.

Everything seems to be in ZTHING, but there's no simple identifier column for type of entry, or to distinguish types that I can see.
User Avatar
alexwasserman 2628 days ago
Actually, try the script now. You should get output that shows the type, then the title, in order: TODAY, NEXT, SCHEDULED, SOMEDAY.
User Avatar
justingerard 2627 days ago
Wait, I meant to say that the geeklet is working for me without the sh script. Can i add a few more parameters to show the rest of those sections?
User Avatar
alexwasserman 2626 days ago
Yeah, I know, but the script is better. And, not really. It's easier to show what you want with one script, than a much more complicated SQL query.

Also, better for GeekTools to be running a script. It can start it and kill it, and better for debugging etc.

Also, much easier to setup.
User Avatar
aapit 2627 days ago
Alex, thanks for this great script!

I'm using Things 2 and had some trouble squeezing the Today items out of Things with your script. It's the only category that doesn't display any of my items out of the box. For me, it does work when I use ZSCHEDULER=2 instead of 1, on line 18. To also fetch the upcoming scheduled items that display in the Today view, I check if ZSTART is 1 *or* 2.

select '1', 'TODAY' AS "TYPE", ZTITLE from ZTHING where ZTRASHED=0 AND ZSTATUS=0 AND ZFOCUSLEVEL IS NULL AND ZSCHEDULER=2 AND (ZSTART=1 OR ZSTART=2)

But since I have yet to discover the connection between the Things database and any kind of logic, either earthly or extraterrestrial, I guess it's safe to say that your mileage may vary.
User Avatar
nestor 2623 days ago
Hi, I can't get it to work: as I'm a newbie maybe there's something I'm missing, here's what I did:

-downloaded and double clicked the glet

-placed the glet and the .sh script in my documents/geeklet folder

-command: $HOME/Documenti/Geeklet/Things.sh

What's wrong? Thanks
User Avatar
nestor 2623 days ago
>What's wrong? Thanks

I think I could get it work by simply pasting the code from the shell script in the command field, is it correct?

Just a little question: as I have a huge number of "next", I'd like to see just the today list which variable should I change and how?

Thanks a lot

Nestor
User Avatar
alexwasserman 2622 days ago
Can you open up Terminal (from Utilities in Applications), and run:

$HOME/Documenti/Geeklet/Things.sh

Should return some data.

For just Today:

export SQL="select '1', 'TODAY' AS "TYPE", ZTITLE from ZTHING where ZTRASHED=0 AND ZSTATUS=0 AND ZFOCUSLEVEL IS NULL AND ZSCHEDULER=1 AND ZSTART=1"

Remove the remaining lines (the union and selects).
User Avatar
johare 2580 days ago
I found that ZSCHEDULER should equal 2 instead of 1 in order to filter TODAY

Log in to comment or register here.