How to get/create a list of all files from database

I’ve seen some similar questions, but not this specific question, so I’m asking it here: is it possible to access (or create a view that gives) a list of all the files attached to all entities within a database? Ideally with support for filtering, etc. I was surprised to note just now that Files is not a selectable field in any of the entity views, so I couldn’t figure out how to create an aggregated view myself that would give me access to all attached Files: ultimately I couldn’t figure out any way to surface this information. Hoping it’s possible…?

I don’t think it is possible unfortunately.
I suspect it might be possible via the API to get a list of the ‘secret’ of every file in the workspace, but I don’t suppose there’s much value in having a list of UUIDs.

Thanks: I gather there might be some plans to turn Files into a proper database + relation at some point? (Hopefully!). In the meantime, do you think I could use the API to fetch metadata for each UUID? So I could create a list of (say) filenames and entities for each one?

1 Like

It turns out that I was wrong about the files API - there is no simple way of getting a list of all uploaded files. However, it is possible to create a script that

  • identifies all databases that have the files collection field enabled
  • finds all entities in those databases that have one or more attached files
  • returns a list of the entities and information about the attached files (including the name of the file)

Here is what it looks like:

const fibery = context.getService('fibery');
const schema = await fibery.getSchema();

const types = schema.typeObjects.filter((t) => t.installedMixins.has("Files/Files-mixin")).map((t) => t.name);

const queries = types.map((typeName) => {
    return {
        command: "fibery.entity/query",
        args: {
            query: {
                "q/from": typeName,
                "q/select": {
                    "type": "$type",
                    "public-id": "fibery/public-id",
                    "files": {
                        "q/from": ["Files/Files"],
                        "q/select": ["fibery/id","fibery/name"],
                        "q/limit": "q/no-limit",
                    }
                },
                "q/where": [">", ["q/count", ["Files/Files", "fibery/id"]], "$zero"],
                "q/limit": "q/no-limit",
            },
            "params": {
                "$zero": 0
            }
        }
    };
});

const batchCommandResult = await fibery.executeSingleCommand({
    command: "fibery.command/batch",
    args: {
        commands: queries
    }
});

const data = batchCommandResult.flatMap((r) => r.result);

console.log(data);

As it stands, the script outputs the result to the browser console, but potentially you could just get it to update a text field or something instead.

You can run this script in a button automation in any database in the workspace.

1 Like

Perfect: thanks very much!! I assume I could create a new database to hold suitably configured records, so then the script could just (idempotently) generate entities in that database (one entity for each discovered File, with attributes). That would be exactly what I need :slight_smile: (It would be nice if File was a built-in database type though, so that a script+action wasn’t necessary!)

Thanks again!

Mike

Well, you could create a database to store the results, but bear in mind that there is no polymorphism in Fibery, so although you would be able to store text information about the files and their ‘owning’ entity, you wouldn’t easily be able to create relations to the entities themselves.
And when it comes to updating this database, you would probably want to use some sort of deduplication, so I’d suggest looking at the deduplication template for inspiration. Keep in mind that you would need to dedup per file rather than per owning entity (since an entity can have multiple files).
Also, I don’t know how you would expect to deal with the case where a file is deleted - should the database record be removed, or maybe just flagged as expired.

I figured I’d have an action that would fetch the current list, and the relation, then update the relation so that it reflects the current list. (Add, update or delete as necessary; presumably update unnecessary unless I’d deleted and then re-uploaded the same document to a given entity.)

I’m not sure I entirely get what you’re describing.
The result of scanning for all files (my script above) will be an array something like this:

[
  {
    type: 'Shared space/Slack message',
    files: [
      {
        'fibery/name': 'cust 6.gif',
        'fibery/id': 'cb679dd6-f88d-4fbe-afcc-0287a6613038'
      }
    ],
    'public-id': '1'
  },
  {
    type: 'Shared space/Order',
    files: [
      {
        'fibery/name': '1.png',
        'fibery/id': 'db1e3184-1c11-4ece-9cc4-bb9d7f982439'
      }
    ],
    'public-id': '1'
  },
  {
    type: 'Shared space/Message',
    files: [
      {
        'fibery/name': 'Addendum to Contract with Chris.pdf',
        'fibery/id': '1b6f4947-cd3b-41c4-97b3-63181d9af019'
      }
    ],
    'public-id': '1'
  }
]

I don’t know how you would plan to store this info in a database, but I could imagine it might look something like this:

Now, if the script ran again, and generated the following:

[
  {
    type: 'Product Management/epic',
    files: [
      {
        'fibery/name': 'cust 6.gif',
        'fibery/id': '255d2043-814b-4de3-9662-70163b3a5186'
      }
    ],
    'public-id': '1'
  },
  {
    type: 'Shared space/Message',
    files: [
      {
        'fibery/name': 'Addendum to Contract with Chris.pdf',
        'fibery/id': '1b6f4947-cd3b-41c4-97b3-63181d9af019'
      }
    ],
    'public-id': '1'
  }
]

I imagine you might want your database to look something like this:

I don’t think there’s a trivially simple solution to implementing this behaviour, but it’s certainly possible :slight_smile:

1 Like

This and several other things that have come up recently (treat Notifications as DB; treat Activity as DB) are increasingly making me feel like a further extension to Fibery’s existing superpowers might indeed be to expose all of this sort of “back end” stuff as some form of “database”, at least in the sense that we can then operate on data like Files, Activity, etc. in the same way we do Entities in a Database. This could be incredibly powerful, especially as more functionality comes along for e.g. simple interface building.

Imagine, for example, being able to Chart the Activity in a Workspace! Fibery team could build that, of course, but it then only addresses a more narrow set of what people might want to do with that Activity data. That along with all the obvious stuff like being able to see these things in various Views, Filter, Color, etc. Expose this stuff as a database and you have a lot of amazing possibility.

Sorry, a bit tangential to this topic, but related-ish. :wink: