AppleScript problems with dates and custom metadata

DEVONthink
6

OK, I think I got it. Your files are named “Weekday d Monthname y” in DT. They’re named something else in DO (or rather: there are no filenames there). You want to augment the custom meta data in DT so that all links to DO are shown as x-devonthink URLs in the “link” field (as a side node: That’s ok if you have only one link in a file, otherwise … well).

So you need the date of the DO note the dayone link is pointing to. As far as I can tell, this step can not be automatized. In fact, there’s hardly any automation support at all in DO: they offer a CLI with exactly one command, which is a bit poor. No scripting at all. Understandable, given that they want you to use their software, not extract your data from it and use it somewhere else (I tend to be opionated).

Now, DO stores its data in the file ~/Library/Group Containers/5U8NS4GX82.dayoneapp2/Data/Documents/DayOne.sqlite. That is, surprise, a SQLite database. As such, it can be queried with a shell command like so

sqlite3 DayOne.sqlite 'select * from zentry where zuuid="E3470F309F7A425184D6A2F45E72302F"'

and it will utter something like this:

2|12|2|0|0|0|21|7|2021|1|0|0||1|||||||648511200|25.8850209712982|648649823.148132|648518400|268F3C7E-528E-49CD-8B7C-BDBC0C5B803B|MBP-ck|MacBookPro16,1|MacBook Pro|macOS|11.5|||10|1:2021:07|# Das war der 21\.7\.21

An dem auch nix passiert ist\.|||{"meta":{"version":1,"small-lines-removed":true,"created":{"platform":"com.bloombuilt.dayone-mac","version":1300}},"contents":[{"attributes":{"line":{"identifier":"46C5D5EE-4C51-4308-8746-6BB96187B87A","header":1}},"text":"Das war der 21.7.21\n"},{"text":"\nAn dem auch nix passiert ist."}]}||E3470F309F7A425184D6A2F45E72302F|||bplist00�
X$versionY$archiverT$topX$objects

So, the date is contained in this record (being 2021-7-21 in this case). So, simply doing this in the shell

sqlite3 DayOne.sqlite 'select zgregorianyear||"-"||zgregorianmonth||"-"||zgregorianday from zentry where zuuid="E3470F309F7A425184D6A2F45E72302F"'

will tell you this

2021-7-21

which seems is just what you need to look up your DT record. You could of course massage the SQL statement to return the name of this record, but given that it contains the weekday and the name of the month, that might be stretching it a bit.

Now, in JavaScript, I’d do this to get the date of this UUID:

let app = Application.currentApplication();
app.includeStandardAdditions = true;
let result = app.doShellScript(`sqlite3 "/Users/<yourname>/Library/Group Containers/5U8NS4GX82.dayoneapp2/Data/Documents/DayOne.sqlite" 'select zgregorianyear||"-"||zgregorianmonth||"-"||zgregorianday from zentry where zuuid="E3470F309F7A425184D6A2F45E72302F"'`);
console.log(result);

Replacing, of course, <yourname> with yourname without the pointy brackets. And this, finally, prints out “2001-7-21”, just as we want it to.

I would not want to figure out how that’s done in AppleScript with all the quoting and stuff, but I’m sure @BLUEFROG knows the answer to that.

So what you have to do is basically this:

  • find the link to your DO entry in the DT record (in JS, I’d do
  const match = record.plainText().match(/view\?entryId=(.*?\))/);
  const uuid = match[1];
  • feed that uuid into the shell command above
  • run that command and save its output in a variable
  • fool around with this variable to figure out the DT record’s name
  • get its uuid, build the DT link and add it to custom meta data

In fact, I’d rather add it right after the original DO link in the DT record. So that it’s clickable too, and you can have more than one link Something like

[DT data](x-devonthinkurl://<uuid>)

Disclaimer: I only tested the shell command, and only with one record. All the rest is … well, untested. Also, DayOne may decide to change their database schema at any point in time, and that may break the shell or the sql command. But until then, it should work.

1 Like
7

Yes, that’s correct, preserving the original Day One link in the DT markdown record (although see also later in this post, picking up on an idea from you). And, yes, there are records with more than one link but I could live with having one only as custom metadata for the relevant records.

I very much appreciate all you have done, which is most kind. I’m very impressed by the Day One manipulation. However, I am prepared to do that part manually and then work with AppleScript on the rest (if it’s possible!).

I’ve tried a slightly different approach to what I’ll call “the AppleScript part” (meaning findlng and linking in DT to a record the date of which is input manually):

set dAnswer to "01/07/2021"
set userEntry to display dialog ¬
	"Enter the link date as DD/MM/YYYY" default answer dAnswer
set userDate to text returned of userEntry
set linkDate to date userDate
set wDay to weekday of linkDate
set dateDay to day of linkDate
set dateMonth to month of linkDate
set dateYear to year of linkDate
set theLink to wDay & " " & dateDay & " " & dateMonth & " " & dateYear & ".md"


try
	tell application id "DNtp"
		set theDatabase to open database "/Users/stapp/Documents/DevonThink/Diaries.dtBase2"
		set foundRecord to search "\"" & theLink & "\"" in theDatabase
	end tell

That works well to return something like:

{content id 1173009 of database id 4 of application "DEVONthink 3"}

BUT I am at present struggling with how to obtain the UUID from foundRecord. However, I seem to have inched nearer to a potential solution — even if nowhere near so automated as yours. It might even be possible in due course to replace the Day One link in the DT record (which I would not mind doing if I could guarantee it would work!).

Please don’t think I’m ungrateful for your work with JavaScript. It’s just, for the time being at least, I’m happy to concentrate on some manual work and then AppleScript to automate the rest of the process.

Stephen

8 
set UUID to UUID of foundRecord

Just like any other property

9

…which, in my latest script (as previously posted) produces the following error:

Error: Can’t get uuid of {content id 1173009 of database id 4 of application "DEVONthink 3"}.

Stephen

10

Ok, I didn’t look up the description of search in the dictionary. Where it clearly says that the return value is a list. I should have thought of that, because there might always be more than one record to match a search criterion.

So do something like

tell application id "DNtp"
	set theLink to "21.7.21"
	set foundRecords to search "\"" & theLink & "\""
	set theUuid to uuid of (first item of foundRecords)
end tell
11

…and the solution appears to be to remove

 of application "DEVONthink 3"

from the code quoted at the end of my earlier post. Work continues…

Stephen

12

No. One part of solution is to look closely at the output: {…} means that there’s a list. And the other part of the solution is to look closely at the documentation, where it says that search returns a list. I didn’t do neither of these parts, unfortunately.

13

We both posted at the same time. Your solution works perfectly and is (of course!) far more elegant than mine.

14

I have learned a lot from your patient tuition. Thanks so much for your time and help. It is really appreciated.

Stephen

15

I’ve posted my solution to your problem in a new thread

It is, of course, in JavaScript (and I’d rather do something very bad to myself than try to write that in AppleScript). I tried it out with (only) two records here, one linking to the other. And I decided to use “name==Wednesday 7 July 2021” as search criteria, assuming that you really named your files in DT that way. Otherwise, you’d have to change that (the search string, that is). When I used just “Wednesday 7 July 2021”, I got far too many results. Also, limiting the search to just the name should speed it up a bit.

16

You really like a challenge. dpn’t you? :grinning: That’s rather similar to me.

You drive me ever closer to need to try to understand JavaScript (which, for an AppleScript newbie, is moderately terrifying). I really appreciate what you have done and will try it with a subset of my data. I especially appreciate the careful annotations but no doubt shall manage to break something when working with it! (For example. the file names have .md extensions so I need to take account of that.)

I think my requirement is, as you say, very specialist so I do hope others can learn something useful from all you work.

Thanks again!

Stephen

17

Staying in this thread for the moment…I have:

  • loaded your JS in script editor;
  • changed your user name to mine;
  • added the path to my appropriate database

and run the script. I get the following error:

Error -1700: Can't convert types.

Because it’s JS I can’t even begin to debug so have to throw myself again at your mercy! Please don’t devote time to it if you’re busy. I continue developing something much less sophisticated in AppleScript.

Stephen

18

Please open the script editor’s message area (click on the three lines at the bottom of the window). You’ll get some context there. And then we’ll try to solve that.

19

Under Replies (which seems to provide the fullest context) I see:

app = Application("DEVONthink 3")
	app.database./Users/stapp/Documents/DevonThink/Diaries.dtBase2.root()
		--> Error -1700: Can't convert types.
Result:
Error -1700: Can't convert types.

which seems to hint that I’ve not properly added the name of the relevant DT database.

Stephen

20

The name has to be in qoutes. I suppose you left those out.
Here

const databaseName = "";

you just just enter Diaries between the quotes:

const databaseName = "Diaries";

No need for all the other stuff (Users/stapp etc): DT needs only the name of the database

21

Here’s the start of the code as now (and the database name was previously within the quotation marks):

(() => {
  const app = Application("DEVONthink 3");
  app.includeStandardAdditions = true; 
  const databaseName = "Diaries"; /* Add database name if you want to search for DT records only in this DB */

and here’s the error I still get:

app = Application("DEVONthink 3")
	app.database.Diaries.root()
		--> Error -1700: Can't convert types.
Result:
Error -1700: Can't convert types.

Stephen

22

I see. Obviously, it’s a bit more complicated…
Change the following if

if (databaseName !== "") {
    /* Get the database record and setup the "in" option for search (see below) */
    var DTdatabase = app.database[databaseName].root();
    DBOption["in"] = DTdatabase;
  }

to

if (databaseName !== "") {
    /* Get the database record and setup the "in" option for search (see below) */
    const db = app.database[databaseName];
    DTdatabase = db.root();
    DBOption["in"] = DTdatabase;
  }

I’m not quite sure why one would need the additional step, but that seems to work here.
What also works:

if (databaseName !== "") {
    /* Get the database record and setup the "in" option for search (see below) */
    var DTdatabase = app.database[databaseName].root;
    DBOption["in"] = DTdatabase;
  }

i.e. the original code without the parenthesis. No idea why, though…

23

I suspect making everyone else crazy with this thread - but I’m still getting the same error.

The start of the code is now:

(() => {
  const app = Application("DEVONthink 3");
  app.includeStandardAdditions = true; 
  const databaseName = "Diaries"; /* Add database name if you want to search for DT records only in this DB */
  const DBOption = {} /* Search all databases, otherwise see next if statement */
  if (databaseName !== "") {
    /* Get the database record and setup the "in" option for search (see below) */
    const db = app.database[databaseName];
    DTdatabase = db.root();
    DBOption["in"] = DTdatabase;
  }

and the error:

app = Application("DEVONthink 3")
	app.database.Diaries.root()
		--> Error -1700: Can't convert types.
Result:
Error -1700: Can't convert types.

Stephen

24

Insert your favorite swear word <<HERE>>

it must be app.databases[databaseName] with a trailing s (of course, banging head against wall).
Sorry again.

I didn’t test this part of the code with the rest because I didn’t want to run it through my database. I did test it in Script Editor separately, and there I actually wrote databases.

<rant>
This is, BTW, another reason why I abhore Apples scripting language: In every other language I know one is taught from the beginning to avoid identifiers that can easily be confused. With AS, it is normal to have “databases”/“database”. And then you get these USELESS error messages. Every other language would tell you “hey the object app does not have a property/method/whatever database, go and check again”. Here, you get -1700. Yuck.
</rant>

25

I laughed out loud! That appears to have effected a major improvement and something now happens. I need to analyse the result but it initially looks very promising. Thanks so much. It may be a little time before I can confirm all is exactly as it should be as I’m a little overwhelmed with things here just at the moment!