The AI-native database
for intelligent systems

# Find users that have access to different types of resources
# Return users by email, the resource ID, and the resource type
match
  $user isa user;
  $resource isa! $resource-type;
  $resource-type sub resource;
  resource-ownership (resource: $resource, owner: $user);
fetch {
  "email": $user.email,
  "resource-id": $resource.id,
  "resource-type": $resource-type
};

-- In relational DBs, define one table for each resource type,
-- using a UNION ALL query and JOINs to fetch interconnected data
SELECT
    ue.email AS email,
    'file' AS resource_type,
    f.path AS id
FROM users u
JOIN user_emails ue
ON u.id = ue.user_id
JOIN resource_ownerships ro
ON u.id = ro.user_id
JOIN resources r
ON ro.resource_id = r.id
JOIN files f
ON r.id = f.resource_id
UNION ALL
SELECT
    ue.email AS email,
    'directory' AS resource_type,
    d.path AS id
FROM users u
JOIN user_emails ue
ON u.id = ue.user_id
JOIN resource_ownerships ro
ON u.id = ro.user_id
JOIN resources r
ON ro.resource_id = r.id
JOIN directories d
ON r.id = d.resource_id
UNION ALL
SELECT
    ue.email AS email,
    'commit' AS resource_type,
    c.hash AS id
FROM users u
JOIN user_emails ue
ON u.id = ue.user_id
JOIN resource_ownerships ro
ON u.id = ro.user_id
JOIN resources r
ON ro.resource_id = r.id
JOIN commits c
ON r.id = c.resource_id
UNION ALL
SELECT
    ue.email AS email,
    'repository' AS resource_type,
    re.name AS id
FROM users u
JOIN user_emails ue
ON u.id = ue.user_id
JOIN resource_ownerships ro
ON u.id = ro.user_id
JOIN resources r
ON ro.resource_id = r.id
JOIN repositories re
ON r.id = re.resource_id
UNION ALL
SELECT
    ue.email AS email,
    'table' AS resource_type,
    t.name AS id
FROM users u
JOIN user_emails ue
ON u.id = ue.user_id
JOIN resource_ownerships ro
ON u.id = ro.user_id
JOIN resources r
ON ro.resource_id = r.id
JOIN tables t
ON r.id = t.resource_id
UNION ALL
SELECT
    ue.email AS email,
    'database' AS resource_type,
    f.name AS id
FROM users u
JOIN user_emails ue
ON u.id = ue.user_id
JOIN resource_ownerships ro
ON u.id = ro.user_id
JOIN resources r
ON ro.resource_id = r.id
JOIN databases d
ON r.id = d.resource_id;

// In document DBs, freeform structures make writes trivial,
// but reading and validating data becomes a significant challenge
db.resource_ownerships.aggregate( [
    {
        $lookup:
        {
            from: "resources",
            localField: "resource",
            foreignField: "_id",
            as: "resource"
        }
    },
    {
        $unwind:
        {
            path: "$resource"
        }
    },
    {
        $lookup:
        {
            from: "users",
            localField: "owner",
            foreignField: "_id",
            as: "owner"
        }
    },
    {
        $unwind:
        {
            path: "$owner"
        }
    },
    {
        $unwind:
        {
            path: "$owner.emails"
        }
    },
    {
        $addFields:
        {
            resource_id: {
                $switch: {
                    branches: [
                        {
                            case: { 
                                $eq: ["$resource.resource_type", "file"]
                            }, 
                            then: "$resource.path" 
                        },
                        {
                            case: { 
                                $eq: ["$resource.resource_type", "directory"]
                            }, 
                            then: "$resource.path" 
                        },
                        {
                            case: { 
                                $eq: ["$resource.resource_type", "commit"]
                            }, 
                            then: "$resource.hash" 
                        },
                        {
                            case: { 
                                $eq: ["$resource.resource_type", "repository"]
                            }, 
                            then: "$resource.name" 
                        },
                        {
                            case: { 
                                $eq: ["$resource.resource_type", "table"]
                            }, 
                            then: "$resource.name" 
                        },
                        {
                            case: { 
                                $eq: ["$resource.resource_type", "database"]
                            }, 
                            then: "$resource.name" 
                        }
                    ]
                }
            }
        }
    },
    {
        $project: {
            _id: false,
            email: "$owner.emails",
            resource_type: "$resource.resource_type",
            id: "$resource_id"
        }
    }
 ] )

// In graph DBs, properties are attached directly to the
// nodes that own them, rather than being nodes themselves
MATCH
    (user:User)-[:OWNS]->(rsrc:Resource)
WITH
    rsrc,
    [user.primary_email] + user.alias_emails AS emails,
    labels(rsrc) AS resource_types,
    keys(rsrc) AS properties
UNWIND emails AS email
UNWIND resource_types AS resource_type
WITH
    rsrc, email, resource_type, properties,
    {
        File: "path",
        Directory: "path",
        Commit: "hash",
        Repository: "name",
        Table: "name",
        Database: "name"
    } AS id_type_map
WHERE resource_type IN keys(id_type_map)
AND id_type_map[resource_type] IN properties
RETURN email, resource_type, rsrc[id_type_map[resource_type]] AS id