[ome-users] List expected files in omero data Files/ and omero scripts

[Carnë Draug]

On 19 September 2016 at 10:48, Josh Moore <josh at glencoesoftware.com> wrote:
> Tag,
>
> On Wed, Sep 14, 2016 at 5:08 PM, Carnë Draug <carandraug+dev at gmail.com> wrote:
>> On 14 September 2016 at 10:44, "Colin Blackburn"
>
> ...snip...
>
>> But this still won't work.  If a python script is an attachment to an image
>> it will go into the originalfile table, have a 'text/x-python' mimetype'
>> and a originalfile.path value, be stored in Files/, but would still be
>> filtered out by that query.  How can I distinguish between an old script
>> whose repo value has been cleared after an omero upgrade (and therefore
>> won't be in Files/) and a script file that was attached to an image?
>
> I'm unsure of the use case "python script attached to an image" but
> would be happy to hear more if you think this is worthwhile.

I don't have a existing use case for this, I just found that this is
possible to do and I was hoping for a solution that would work in any
database.

> That being said, I think your concern can be simplified down to, "how
> can I distinguish between an old script whose repo value has been
> cleared and a user-uploaded script?" -- You can't. The official
> scripts which are in the ScriptRepo have their repo removed since the
> files have been overwritten. i.e. they no longer exist as files.
> However, deleting them from the DB is not desired since certain
> results will be linked to their execution. The point you raise is that
> what we *should have done* is copy the script into OriginalFile, but
> unfortunately, we did not.
>
> When it comes to scripts, there will apparently need to be manual
> intervention. Comparing who created the scripts (old official scripts
> will be created as root) may help you limit the number of false
> positives.

I can handle this exception manually on our database.  But scripts
may not be the only issue.  My problem is getting a list of all files
that should be in Files/.  This case with scripts was immediately
obvious that I was doing something wrong but now I'm worried I may
be doing something wrong for the other rows too.  Do you think there
could be any other exception?  Any other case where files may have been
removed as expected but remain in the database?

That is, with the exception of this issue about old scripts, is this query
suitable to get a complete list of all files that should be in Files/?

    SELECT
      id
      FROM originalfile
      WHERE (repo IS NULL OR repo = '') AND mimetype != 'Repository';

Or should I instead be getting the ids from all tables that reference
originalfile(id)?

Also, should I open a bug report about making a copy of the older scripts
into Files/ ?  For our current case, I could download the old releases
of omero and copy the old version of the scripts into Files/. Could you
see any issue with doing that?

Thank you
Carnë