Even if the physical file is not there, the metadata has been downloaded so for almost everything that deals with resources it shouldn't make a difference. If you delete it, it would succeed as it will delete the metadata.
Let’s say you have 4 revisions of a note. In revision 2 a file was attached, but now in the latest revision the reference to that file is no longer there. So technically that resource is no longer associated with a note. But in the database that resource must not be set to is_associated = 0 .
For that reason, the resource expiration delay is the same as the note revision expiration delay. If revisions are kept for 90 days, resources also will be kept for 90 days.
Note that you are a bit in dangerous territory here. There are some aspects of Joplin that aren’t well documented and may change from version to version, so be extra careful when you delete resources.
Right, that's the reason for all my questions. I wrote the script before a few things were implemented in Joplin, so I'm not entirely sure, if the script is still safe.
Ok, but what does the column is_associated say for an attachment that has not been downloaded? Does it say 0 or 1? IMO it's associated, just not downloaded, but I didn't develop the schema and logic.
Ok, but once again, what does it say in the column is_associated? According to your explanation, it should say 1 during those 90 days (even if the current revision is not associated with that resource), but it will say 0 after 90 days? Somehow this makes no sense. What if the resource was added on day 45 and deleted on day 70? Or do you mean after 90 days of real disassociation? In my example above after 90 days starting at day 70?
If a resource is associated with any note, is_associated is 1, it it’s not is_associated is 0. From the moment is_associated is 0, a timer starts and after x days the resource is deleted.
Thanks for the info, but in this case I would like to point out that resources will be deleted incorrectly, when people use different keep note history for settings across devices.
Scenario:
Device 1: keep history for 10 days, revision X on day 3 removes reference to resource
Device 2: keep history for 5 days, resource is deleted on day 8
Device 1: on day 9 one reverts to revision from day 2, resource gone, but reference still exists in note
Oh, wait, maybe not. I think in that case the history might be kept for 5 days only on all devices.
what does the column is_associated say for an attachment that has not been downloaded? Does it say 0 or 1 ?
I still don't know the answer to that one. Does the info in the database make a distinction betweeen not associated and not downloaded?
This is even more critical than the other issue, because the other one could be remedied by taking the timeframe into account.
But I think it is safe to say that my script (and/or @sciurius's perl script) should not be used with an enabled note history, because it currently doesn't take the note history's timeframe into account.