The polymorphic database powered by types

match $user isa user,
    has full-name $name,
    has email $email;
get; # This returns all users of any type

match $user isa employee,
    has full-name $name,
    has email $email,
    has employee-id $id;
get; # This returns only users who are employees

match $user-type sub user;
$user isa $user-type,
    has full-name $name,
    has email $email;
get; # This returns all users and their type

insert

$john isa full-time-employee,
    has full-name "John Doe", 
    has primary-email "john.doe@vaticle.com",
    has email "j.doe@vaticle.com",
    has email "john@vaticle.com",
    has employee-id 183;
$readme isa file, has path "/home/johndoe/repos/typedb/readme.md";
$edit isa action, has name "edit file";
$kevin isa user, has email "kevin@vaticle.com";

$perm (subject: $john, object: $readme, action: $edit) isa permission;
$rqst (target: $perm, requestee: $kevin) isa change-request, has requested-change "revoke";

define

full-name sub attribute, value string;
id sub attribute, value string;
email sub id;
employee-id sub id;

user sub entity,
    owns full-name,
    owns email @unique;
employee sub user,
    owns employee-id @key,
    plays team-membership:member;

membership sub relation, abstract,
    relates parent,
    relates member;
team-membership sub membership,
    relates team as parent;

define
rule transitive-team-membership: when {
    (team: $team-1, member: $team-2) isa team-membership;
    (team: $team-2, member: $member) isa team-membership;   
} then { (team: $team-1, member: $member) isa team-membership; };

insert
$john isa user, has email "john@vaticle.com";
$eng isa team, has name "Engineering ";
$cloud isa team, has name "Cloud";
(team: $eng, member: $cloud) isa team-membership;
(team: $cloud, member: $john) isa team-membership;

match
$john isa user, has email "john@vaticle.com";
(team: $team, member: $john) isa team-membership;
get; # This will return both Cloud and Engineering for $team due to the defined rule

match $user isa user; get;

match $user isa user, has email "john@vaticle.com"; get;

match
$user isa user, has email "john@vaticle.com";
(subject: $user, object: $tql, action: $action) isa permission;
$tql isa file, has extension "tql";
get;

match
$user isa user;
(subject: $user, object: $tql, action: $action) isa permission;
$tql isa file, has extension "tql";
(team: $eng, member: $user) isa team-membership;
$eng isa team, has name "Engineering"; 
get;

define
weekly-hours sub attribute value long;
full-time-employee sub employee;
part-time-employee sub employee, owns weekly-hours;

insert
$francois isa full-time-employee,
    has full-name "François Durand",
    has email "francois@vaticle.com",
    has employee-id 184,
    has weekly-hours 35;

# [THW03] Invalid Write: Attribute of type 'weekly-hours' is
# not defined to be owned by type 'full-time-employee'.

insert

$john isa full-time-employee,
    has full-name "John Doe", 
    has primary-email "john.doe@vaticle.com",
    has email "j.doe@vaticle.com",
    has email "john@vaticle.com",
    has employee-id 183;
$readme isa file, has path "/home/johndoe/repos/typedb/readme.md";
$edit isa action, has name "edit file";
$kevin isa user, has email "kevin@vaticle.com";

$perm (subject: $john, object: $readme, action: $edit) isa permission;
$rqst (target: $perm, requestee: $kevin) isa change-request, has requested-change "revoke";

DO $$
DECLARE
    inserted_user_id INTEGER;
    inserted_resource_id INTEGER;
    inserted_action_id INTEGER;
    inserted_requestee_id INTEGER;
    inserted_permission_id INTEGER;
BEGIN
    INSERT INTO users (id, full_name)
    VALUES (DEFAULT, 'John Doe')
    RETURNING id INTO inserted_user_id;
    
    INSERT INTO user_emails (user_id, email, is_primary)
    VALUES
        (inserted_user_id, 'john.doe@vaticle.com', TRUE),
        (inserted_user_id, 'j.doe@vaticle.com', FALSE),
        (inserted_user_id, 'john@vaticle.com', FALSE);
    
    INSERT INTO employees (user_id, employee_id)
    VALUES (inserted_user_id, 183);
    
    INSERT INTO full_time_employees (user_id)
    VALUES (inserted_user_id);
    
    INSERT INTO resources (id)
    VALUES (DEFAULT)
    RETURNING id INTO inserted_resource_id;
    
    INSERT INTO files (inserted_resource_id, path)
    VALUES (inserted_resource_id, '/home/johndoe/repos/typedb/readme.md');
    
    INSERT INTO actions (id, name)
    VALUES (DEFAULT, 'edit file')
    RETURNING id INTO inserted_action_id;
    
    INSERT INTO users (id, full_name)
    VALUES (DEFAULT, NULL)
    RETURNING id INTO inserted_requestee_id;
    
    INSERT INTO user_emails (user_id, email, is_primary)
    VALUES (inserted_requestee_id, 'kevin@vaticle.com', FALSE);
    
    INSERT INTO permissions (id, subject, object, action)
    VALUES (DEFAULT, inserted_user_id, inserted_resource_id, inserted_action_id)
    RETURNING id INTO inserted_permission_id;
    
    INSERT INTO change_requests (id, target, requestee, requested_change)
    VALUES (DEFAULT, inserted_permission_id, inserted_requestee_id, 'revoke');
    
    COMMIT;
END $$;

db.change_requests.aggregate([
    {
        $lookup: {
            from: "permissions",
            localField: "target",
            foreignField: "_id",
            as: "permission"
        }
    },
    {
        $unwind: {
            path: "$permission"
        }
    },
    {
        $lookup: {
            from: "users",
            localField: "user",
            foreignField: "_id",
            as: "user"
        }
    },
    {
        $unwind: {
            path: "$user"
        }
    },
    {
        $lookup: {
            from: "users",
            localField: "permission.user",
            foreignField: "_id",
            as: "permission.user"
        }
    },
    {
        $unwind: {
            path: "$permission.user"
        }
    },
    {
        $lookup: {
            from: "identifiers",
            localField: "permission.object",
            foreignField: "_id",
            as: "permission.object"
        }
    },
    {
        $unwind: {
            path: "$permission.object"
        }
    },
    {
        $lookup: {
            from: "actions",
            localField: "permission.action",
            foreignField: "_id",
            as: "permission.action"
        }
    },
    {
        $unwind: {
            path: "$permission.action"
        }
    },
    {
        $project: {
            _id: false,
            user: "$permission.user.email",
            requestee: "$user.email",
            target: "$permission.object.identifier",
            action: "$permission.action.name",
            request: "$req_change"
        }
    }
])

MATCH
    (john:User),
    (readme:File {path: "/home/johndoe/repos/typedb/readme.md"}),
    (edit:Action {name: "edit file"})
WHERE (
        john.primary_email = "john@vaticle.com"
        OR "john@vaticle.com" IN john.alias_emails
    ) AND NOT EXISTS {
        MATCH (john)<-[:SUBJECT]-(perm:Permission)-[:OBJECT]->(readme)
        WHERE EXISTS ( (perm)-[:ACTION]->(edit) )
    }
WITH john, readme, edit 
CREATE (john)<-[:SUBJECT]-(perm:Permission)-[:OBJECT]->(readme)
WITH edit, perm
CREATE (perm)-[:ACTION]->(edit);
MATCH
    (perm:Permission),
    (perm)-[:SUBJECT]->(john:User),
    (perm)-[:OBJECT]->(readme:File {id: "/home/vaticle/repos/typedb/readme.md"}),
    (perm)-[:ACTION]->(edit:Action {name: "edit file"}),
    (kevin:User)
WHERE (
        john.primary_email = "john@vaticle.com"
        OR "john@vaticle.com" IN john.alias_emails
    ) AND (
        kevin.primary_email = "kevin@vaticle.com"
        OR "kevin@vaticle.com" IN kevin.alias_emails
    )
CREATE
    (rqst:ChangeRequest {requested_change: "revoke"}),
    (rqst)-[:TARGET]->(perm),
    (rsqt)-[:REQUESTEE]->(kevin);

match
$user isa user, has email $email;
$rsrc isa! $resource-type, has id $id;
$resource-type sub resource;
$own (resource: $rsrc, owner: $user) isa resource-ownership;
get $email, $resource-type, $id;

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;

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"
        }
    }
 ] )

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

define
rule transitive-team-membership:
    when {
        (team: $team-1, member: $team-2) isa team-membership;
        (team: $team-2, member: $member) isa team-membership;   
    } then {
        (team: $team-1, member: $member) isa team-membership;
    };

match
$user isa user, has email $email;
$team isa team, has name $name;
(team: $team, member: $user) isa team-membership;
get $email, $name;

CREATE PROCEDURE reload_inherited_team_memberships()
LANGUAGE SQL
BEGIN ATOMIC
TRUNCATE TABLE inherited_team_memberships;
WITH RECURSIVE parent_teams (team, member) AS (
    SELECT team_memberships.team, team_memberships.member
    FROM team_memberships
    WHERE team_memberships.member IN (
        SELECT id
        FROM user
    )
    UNION ALL
    SELECT team_memberships.team, team_memberships.member
    FROM team_memberships
    JOIN parent_teams
    ON parent_teams.team = team_memberships.member
)
INSERT INTO inherited_team_memberships (team, member)
SELECT team, member
FROM parent_teams;
COMMIT;
END;

SELECT users.email AS email, teams.name AS name
FROM inherited_team_memberships
JOIN users
ON users.id = inherited_team_memberships.member
JOIN teams
ON teams.id = inherited_team_memberships.team;

inherited_team_memberships = db.team_memberships.aggregate([
    {
        $lookup: {
            from: "users",
            localField: "member",
            foreignField: "_id",
            as: "member"
        }
    },
    {
        $unwind: {
            path: "$member"
        }
    },
    {
        $graphLookup: {
            from: "team_memberships",
            startWith: "$team",
            connectFromField: "team",
            connectToField: "member",
            as: "parent_teams"
        }
    },
    {
        $project: {
            _id: false,
            email: "$member.email",
            team: {
                $setUnion: [
                    [
                        "$team"
                    ],
                    "$parent_teams.team"
                ]
            }
        }
    },
    {
        $unwind: {
            path: "$team"
        }
    },
    {
        $lookup: {
            from: "teams",
            localField: "team",
            foreignField: "_id",
            as: "team"
        }
    },
    {
        $unwind: {
            path: "$team"
        }
    },
    {
        $project: {
            email: "$email",
            name: "$team.name"
        }
    }
])

db.inherited_team_memerships.remove({})

db.inherited_team_memberships.insertMany(inherited_team_memberships)

db.inherited_team_memberships.find({})

MATCH
    ()<-[sub:SUBJECT]-(perm:InheritedPermission)-[obj:OBJECT]->(),
    (perm)-[act:ACTION]->()
DELETE sub, obj, act, perm;

MATCH
    (perm:Permission),
    (user:User),
    (rsrc:Resource),
    (actn:Action)
WHERE (
    (perm)-[:SUBJECT]->(user:User)
    OR (perm)-[:SUBJECT]->(:Team)<-[:MEMBER_OF*]-(user:User)
) AND (
    (perm)-[:OBJECT]->(rsrc:Resource)
    OR (perm)-[:OBJECT]->(:Collection)<-[:MEMBER_OF*]-(rsrc:Resource)
) AND (
    (perm)-[:ACTION]->(actn:Action)
    OR (perm)-[:ACTION]->(:ActionSet)<-[:MEMBER_OF*]-(actn:Action)
) AND NOT EXISTS {
    MATCH (user)<-[:SUBJECT]-(perm:InheritedPermission)-[:OBJECT]->(rsrc)
    WHERE EXISTS ( (perm)-[:ACTION]->(actn) )
}
WITH user, rsrc, actn
CREATE (user)<-[:SUBJECT]-(perm:InheritedPermission)-[:OBJECT]->(rsrc)
WITH actn, perm
CREATE (perm)-[:ACTION]->(actn);

MATCH
    (user:User)<-[:SUBJECT]-(perm:InheritedPermission)-[:OBJECT]->(rsrc:Resource),
    (perm)-[:ACTION]->(actn:Action)
RETURN user, rsrc, actn;