03 - 黑白顛倒

Full schema preview

Full schema preview

start1st migrationend migration

scenes/scene02/schema.esdl

module default {

    # scalar types
    scalar type PoliceRank extending enum<Protected, Cadet, PC, SPC, SGT, SSGT, PI, IP, SIP, CIP, SP, SSP, CSP, ACP, SACP, DCP, CP>;
    scalar type GangsterRank extending enum<Nobody, Leader, Boss>;
    scalar type DayOfWeek extending enum<Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday>;

    scalar type FuzzyYear extending int64;
    scalar type FuzzyMonth extending int64 {constraint expression on (__subject__ >=1 and __subject__ <=12)}
    scalar type FuzzyDay extending int64 {constraint expression on (__subject__ >=1 and __subject__ <=31)}
    scalar type FuzzyHour extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=23)}
    scalar type FuzzyMinute extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=59)}
    scalar type FuzzySecond extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=59)}

    scalar type SceneNumber extending sequence;

    # abstract object types
    abstract type Person {
        required name: str;
        nickname: str;
        eng_name: str;
    }

    abstract type IsPolice {
        police_rank: PoliceRank{
            default:= PoliceRank.Cadet;
        };
        dept: str;
        is_officer:= .police_rank >= PoliceRank.PI;
    }

    abstract type IsGangster {
        gangster_rank: GangsterRank {
            default:= GangsterRank.Nobody;
        };
        gangster_boss: GangsterBoss;
    }

    abstract type IsSpy extending IsPolice, IsGangster;

    abstract type Place {
        required name: str {
            delegated constraint exclusive;
        };
    }

    abstract type Event {
        detail: str;
        multi who: Character;
        multi `when`: FuzzyTime;
        multi where: Place;
    }

    # object types
    type Character extending Person {
        classic_lines: array<str>;
        lover: Character;
        multi actors: Actor;
    }

    type Actor extending Person;
    type Police extending Character, IsPolice;
    type Gangster extending Character, IsGangster;

    type GangsterBoss extending Gangster {
        overloaded gangster_rank: GangsterRank {
            default:= GangsterRank.Boss;
            constraint expression on (__subject__ = GangsterRank.Boss);
        };

        # excluding self
        constraint expression on (__subject__ != .gangster_boss) { 
            errmessage := "The boss can't be his/her own boss.";
        }
    }

    type PoliceSpy extending Character, IsSpy;
    type GangsterSpy extending Character, IsSpy;

    type Landmark extending Place;
    type Location extending Place;
    type Store extending Place;

    type FuzzyTime {
        fuzzy_year: FuzzyYear;
        fuzzy_month: FuzzyMonth;
        fuzzy_day: FuzzyDay;
        fuzzy_hour: FuzzyHour;
        fuzzy_minute: FuzzyMinute;
        fuzzy_second: FuzzySecond;
        fuzzy_dow: DayOfWeek; 
        fuzzy_fmt:= (
            with Y:= <str>.fuzzy_year ?? "YYYY",
                 m:= <str>.fuzzy_month ?? "MM",
                 m:= m if len(m) > 1 else "0" ++ m,
                 d:= <str>.fuzzy_day ?? "DD",
                 d:= d if len(d) > 1 else "0" ++ d,
                 H:= <str>.fuzzy_hour ?? "HH24",
                 H:= H if len(H) > 1 else "0" ++ H,
                 M:= <str>.fuzzy_minute ?? "MI",
                 M:= M if len(M) > 1 else "0" ++ M,
                 S:= <str>.fuzzy_second ?? "SS",
                 S:= S if len(S) > 1 else "0" ++ S,
                 dow:= <str>.fuzzy_dow ?? "ID", 
            select Y ++ "/" ++ m ++ "/" ++ d ++ "_" ++
                   H ++ ":" ++ M ++ ":" ++ S ++ "_" ++
                   dow       
        );

        trigger fuzzy_month_day_check after insert, update for each 
        when (exists __new__.fuzzy_month and exists __new__.fuzzy_day) 
        do ( 
            assert_exists(
                cal::to_local_date(__new__.fuzzy_year ?? 2002, __new__.fuzzy_month, __new__.fuzzy_day),
                ) 
        );
        constraint exclusive on (.fuzzy_fmt);
    }

    type ChenLauContact extending Event {
        how: str;
        overloaded who: Character {default:= {chen, lau}}
    }

    type Scene extending Event {
        title: str;
        remarks: str;
        references: array<tuple<str, str>>;
        required scene_number: SceneNumber {
            constraint exclusive;
            default := sequence_next(introspect SceneNumber);
        }
        index on (.scene_number);
    }

    # alias
    alias hon:= assert_exists(assert_single((select GangsterBoss filter .name = "韓琛")));
    alias lau:= assert_exists(assert_single((select GangsterSpy filter .name = "劉建明")));
    alias chen:= assert_exists(assert_single((select PoliceSpy filter .name = "陳永仁")));
    alias wong:= assert_exists(assert_single((select Police filter .name = "黃志誠")));

    alias year_1992:= assert_exists(assert_single((select FuzzyTime 
                                        filter .fuzzy_year = 1992 
                                        and .fuzzy_month ?= <FuzzyMonth>{}
                                        and .fuzzy_day ?= <FuzzyDay>{}
                                        and .fuzzy_hour ?= <FuzzyHour>{}
                                        and .fuzzy_minute ?= <FuzzyMinute>{}
                                        and .fuzzy_second ?= <FuzzySecond>{}   
                                        and .fuzzy_dow ?= <DayOfWeek>{}
                    ))
    );

    # tests
    function test_alias() -> bool
    using (all({
            test_scene01_alias(),
            test_scene02_alias(),
        })
    );

    function test_scene01_alias() -> bool
    using (all({
            (exists hon),          
            (exists lau),
            (exists year_1992),   
        })
    );

    function test_scene02_alias() -> bool
    using (all({
            (exists chen),          
            (exists wong), 
        })
    );

}

scenes/scene03/schema_1st_migration.esdl

module default {

    # scalar types
    scalar type PoliceRank extending enum<Protected, Cadet, PC, SPC, SGT, SSGT, PI, IP, SIP, CIP, SP, SSP, CSP, ACP, SACP, DCP, CP>;
    scalar type GangsterRank extending enum<Nobody, Leader, Boss>;
    scalar type DayOfWeek extending enum<Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday>;

    scalar type FuzzyYear extending int64;
    scalar type FuzzyMonth extending int64 {constraint expression on (__subject__ >=1 and __subject__ <=12)}
    scalar type FuzzyDay extending int64 {constraint expression on (__subject__ >=1 and __subject__ <=31)}
    scalar type FuzzyHour extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=23)}
    scalar type FuzzyMinute extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=59)}
    scalar type FuzzySecond extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=59)}

    scalar type SceneNumber extending sequence;

    # abstract object types
    abstract type Person {
        required name: str;
        nickname: str;
        eng_name: str;
    }

    abstract type IsPolice {
        police_rank: PoliceRank{
            default:= PoliceRank.Cadet;
        };
        dept: str;
        is_officer:= .police_rank >= PoliceRank.PI;
    }

    abstract type IsGangster {
        gangster_rank: GangsterRank {
            default:= GangsterRank.Nobody;
        };
        gangster_boss: GangsterBoss;
    }

    abstract type IsSpy extending IsPolice, IsGangster;

    abstract type Place {
        required name: str {
            delegated constraint exclusive;
        };
    }

    abstract type Event {
        detail: str;
        multi who: Character;
        multi `when`: FuzzyTime;
        multi where: Place;
    }

    # object types
    type Character extending Person {
        classic_lines: array<str>;
        lover: Character;
        multi actors: Actor;
    }

    type Actor extending Person;
    type Police extending Character, IsPolice;
    type Gangster extending Character, IsGangster;

    type GangsterBoss extending Gangster {
        overloaded gangster_rank: GangsterRank {
            default:= GangsterRank.Boss;
            constraint expression on (__subject__ = GangsterRank.Boss);
        };

        # excluding self
        constraint expression on (__subject__ != .gangster_boss) { 
            errmessage := "The boss can't be his/her own boss.";
        }
    }

    type PoliceSpy extending Character, IsSpy;
    type GangsterSpy extending Character, IsSpy;

    type Landmark extending Place;
    type Location extending Place;
    type Store extending Place;

    type FuzzyTime {
        fuzzy_year: FuzzyYear;
        fuzzy_month: FuzzyMonth;
        fuzzy_day: FuzzyDay;
        fuzzy_hour: FuzzyHour;
        fuzzy_minute: FuzzyMinute;
        fuzzy_second: FuzzySecond;
        fuzzy_dow: DayOfWeek; 
        fuzzy_fmt:= (
            with Y:= <str>.fuzzy_year ?? "YYYY",
                 m:= <str>.fuzzy_month ?? "MM",
                 m:= m if len(m) > 1 else "0" ++ m,
                 d:= <str>.fuzzy_day ?? "DD",
                 d:= d if len(d) > 1 else "0" ++ d,
                 H:= <str>.fuzzy_hour ?? "HH24",
                 H:= H if len(H) > 1 else "0" ++ H,
                 M:= <str>.fuzzy_minute ?? "MI",
                 M:= M if len(M) > 1 else "0" ++ M,
                 S:= <str>.fuzzy_second ?? "SS",
                 S:= S if len(S) > 1 else "0" ++ S,
                 dow:= <str>.fuzzy_dow ?? "ID", 
            select Y ++ "/" ++ m ++ "/" ++ d ++ "_" ++
                   H ++ ":" ++ M ++ ":" ++ S ++ "_" ++
                   dow       
        );

        trigger fuzzy_month_day_check after insert, update for each 
        when (exists __new__.fuzzy_month and exists __new__.fuzzy_day) 
        do ( 
            assert_exists(
                cal::to_local_date(__new__.fuzzy_year ?? 2002, __new__.fuzzy_month, __new__.fuzzy_day),
                ) 
        );
        constraint exclusive on (.fuzzy_fmt);
    }

    type ChenLauContact extending Event {
        how: str;
        overloaded who: Character {default:= {chen, lau}}
    }

    type Scene extending Event {
        title: str;
        remarks: str;
        references: array<tuple<str, str>>;
        required scene_number: SceneNumber {
            constraint exclusive;
            default := sequence_next(introspect SceneNumber);
        }
        index on (.scene_number);
    }

    # alias
    alias hon:= assert_exists(assert_single((select GangsterBoss filter .name = "韓琛")));
    alias lau:= assert_exists(assert_single((select GangsterSpy filter .name = "劉建明")));
    alias chen:= assert_exists(assert_single((select PoliceSpy filter .name = "陳永仁")));
    alias wong:= assert_exists(assert_single((select Police filter .name = "黃志誠")));

    alias police_station:= assert_exists(assert_single((select Landmark filter .name="警察局")));

    alias year_1992:= assert_exists(assert_single((select FuzzyTime 
                                        filter .fuzzy_year = 1992 
                                        and .fuzzy_month ?= <FuzzyMonth>{}
                                        and .fuzzy_day ?= <FuzzyDay>{}
                                        and .fuzzy_hour ?= <FuzzyHour>{}
                                        and .fuzzy_minute ?= <FuzzyMinute>{}
                                        and .fuzzy_second ?= <FuzzySecond>{}   
                                        and .fuzzy_dow ?= <DayOfWeek>{}
                    ))
    );
    alias year_1994:= assert_exists(assert_single((select FuzzyTime 
                                        filter .fuzzy_year = 1994 
                                        and .fuzzy_month ?= <FuzzyMonth>{}
                                        and .fuzzy_day ?= <FuzzyDay>{}
                                        and .fuzzy_hour ?= <FuzzyHour>{}
                                        and .fuzzy_minute ?= <FuzzyMinute>{}
                                        and .fuzzy_second ?= <FuzzySecond>{}   
                                        and .fuzzy_dow ?= <DayOfWeek>{}
                    ))
    );

    # tests
    function test_alias() -> bool
    using (all({
            test_scene01_alias(),
            test_scene02_alias(),
            test_scene03_alias(),
        })
    );

    function test_scene01_alias() -> bool
    using (all({
            (exists hon),          
            (exists lau),
            (exists year_1992),   
        })
    );

    function test_scene02_alias() -> bool
    using (all({
            (exists chen),          
            (exists wong), 
        })
    );

    function test_scene03_alias() -> bool
    using (all({
            (exists year_1994),   
            (exists police_station),   
        })
    );

}

scenes/scene03/schema.esdl

module default {

    # scalar types
    scalar type PoliceRank extending enum<Protected, Cadet, PC, SPC, SGT, SSGT, PI, IP, SIP, CIP, SP, SSP, CSP, ACP, SACP, DCP, CP>;
    scalar type GangsterRank extending enum<Nobody, Leader, Boss>;
    scalar type DayOfWeek extending enum<Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday>;

    scalar type FuzzyYear extending int64;
    scalar type FuzzyMonth extending int64 {constraint expression on (__subject__ >=1 and __subject__ <=12)}
    scalar type FuzzyDay extending int64 {constraint expression on (__subject__ >=1 and __subject__ <=31)}
    scalar type FuzzyHour extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=23)}
    scalar type FuzzyMinute extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=59)}
    scalar type FuzzySecond extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=59)}

    scalar type SceneNumber extending sequence;

    # abstract object types
    abstract type Person {
        required name: str;
        nickname: str;
        eng_name: str;
    }

    abstract type IsPolice {
        police_rank: PoliceRank{
            default:= PoliceRank.Cadet;
        };
        dept: str;
        is_officer:= .police_rank >= PoliceRank.PI;
    }

    abstract type IsGangster {
        gangster_rank: GangsterRank {
            default:= GangsterRank.Nobody;
        };
        gangster_boss: GangsterBoss;
    }

    abstract type IsSpy extending IsPolice, IsGangster;

    abstract type Place {
        required name: str {
            delegated constraint exclusive;
        };
    }

    abstract type Event {
        detail: str;
        multi who: Character;
        multi `when`: FuzzyTime;
        multi where: Place;
    }

    abstract type Archive;

    # object types
    type Character extending Person {
        classic_lines: array<str>;
        lover: Character;
        multi actors: Actor;
    }

    type Actor extending Person;
    type Police extending Character, IsPolice;
    type Gangster extending Character, IsGangster;

    type GangsterBoss extending Gangster {
        overloaded gangster_rank: GangsterRank {
            default:= GangsterRank.Boss;
            constraint expression on (__subject__ = GangsterRank.Boss);
        };

        # excluding self
        constraint expression on (__subject__ != .gangster_boss) { 
            errmessage := "The boss can't be his/her own boss.";
        }
    }

    type PoliceSpy extending Character, IsSpy;
    type GangsterSpy extending Character, IsSpy;

    type Landmark extending Place;
    type Location extending Place;
    type Store extending Place;

    type FuzzyTime {
        fuzzy_year: FuzzyYear;
        fuzzy_month: FuzzyMonth;
        fuzzy_day: FuzzyDay;
        fuzzy_hour: FuzzyHour;
        fuzzy_minute: FuzzyMinute;
        fuzzy_second: FuzzySecond;
        fuzzy_dow: DayOfWeek; 
        fuzzy_fmt:= (
            with Y:= <str>.fuzzy_year ?? "YYYY",
                 m:= <str>.fuzzy_month ?? "MM",
                 m:= m if len(m) > 1 else "0" ++ m,
                 d:= <str>.fuzzy_day ?? "DD",
                 d:= d if len(d) > 1 else "0" ++ d,
                 H:= <str>.fuzzy_hour ?? "HH24",
                 H:= H if len(H) > 1 else "0" ++ H,
                 M:= <str>.fuzzy_minute ?? "MI",
                 M:= M if len(M) > 1 else "0" ++ M,
                 S:= <str>.fuzzy_second ?? "SS",
                 S:= S if len(S) > 1 else "0" ++ S,
                 dow:= <str>.fuzzy_dow ?? "ID", 
            select Y ++ "/" ++ m ++ "/" ++ d ++ "_" ++
                   H ++ ":" ++ M ++ ":" ++ S ++ "_" ++
                   dow       
        );

        trigger fuzzy_month_day_check after insert, update for each 
        when (exists __new__.fuzzy_month and exists __new__.fuzzy_day) 
        do ( 
            assert_exists(
                cal::to_local_date(__new__.fuzzy_year ?? 2002, __new__.fuzzy_month, __new__.fuzzy_day),
                ) 
        );
        constraint exclusive on (.fuzzy_fmt);
    }

    type CriminalRecord extending Archive {
        required ref_no: str {
            constraint exclusive;
        };
        required code: str;
        multi involved: Character;
        created_at: datetime {
            readonly := true;
            rewrite insert using (datetime_of_statement())
        }
        modified_at: datetime {
            rewrite update using (datetime_of_statement())
        }
    }

    type ChenLauContact extending Event {
        how: str;
        overloaded who: Character {default:= {chen, lau}}
    }

    type Scene extending Event {
        title: str;
        remarks: str;
        references: array<tuple<str, str>>;
        required scene_number: SceneNumber {
            constraint exclusive;
            default := sequence_next(introspect SceneNumber);
        }
        index on (.scene_number);
    }

    # alias
    alias hon:= assert_exists(assert_single((select GangsterBoss filter .name = "韓琛")));
    alias lau:= assert_exists(assert_single((select GangsterSpy filter .name = "劉建明")));
    alias chen:= assert_exists(assert_single((select PoliceSpy filter .name = "陳永仁")));
    alias wong:= assert_exists(assert_single((select Police filter .name = "黃志誠")));

    alias police_station:= assert_exists(assert_single((select Landmark filter .name="警察局")));

    alias year_1992:= assert_exists(assert_single((select FuzzyTime 
                                        filter .fuzzy_year = 1992 
                                        and .fuzzy_month ?= <FuzzyMonth>{}
                                        and .fuzzy_day ?= <FuzzyDay>{}
                                        and .fuzzy_hour ?= <FuzzyHour>{}
                                        and .fuzzy_minute ?= <FuzzyMinute>{}
                                        and .fuzzy_second ?= <FuzzySecond>{}   
                                        and .fuzzy_dow ?= <DayOfWeek>{}
                    ))
    );
    alias year_1994:= assert_exists(assert_single((select FuzzyTime 
                                        filter .fuzzy_year = 1994 
                                        and .fuzzy_month ?= <FuzzyMonth>{}
                                        and .fuzzy_day ?= <FuzzyDay>{}
                                        and .fuzzy_hour ?= <FuzzyHour>{}
                                        and .fuzzy_minute ?= <FuzzyMinute>{}
                                        and .fuzzy_second ?= <FuzzySecond>{}   
                                        and .fuzzy_dow ?= <DayOfWeek>{}
                    ))
    );

    # tests
    function test_alias() -> bool
    using (all({
            test_scene01_alias(),
            test_scene02_alias(),
            test_scene03_alias(),
        })
    );

    function test_scene01_alias() -> bool
    using (all({
            (exists hon),          
            (exists lau),
            (exists year_1992),   
        })
    );

    function test_scene02_alias() -> bool
    using (all({
            (exists chen),          
            (exists wong), 
        })
    );

    function test_scene03_alias() -> bool
    using (all({
            (exists year_1994),   
            (exists police_station),   
        })
    );

}

劇情提要

此劇照引用自IMDb-無間道

永仁留下多次案底，並曾經被建明逮捕，但也逐漸取得黑社會的信任。建明畢業後則由警員（PC）做起，表現優異，獲面試晉陞見習督察（PI）的機會。兩人的路就像黑白顛倒一般，誰是好人，誰又是壞人呢？

EdgeQL query

insert此場景時間1994年

scenes/scene03/query.edgeql

insert FuzzyTime {fuzzy_year:= 1994};

insert地標警察局

scenes/scene03/query.edgeql

insert Landmark {name:= "警察局"};

建立alias及編寫測試alias的function

定義一個year_1994（1994年）及police_station（警察局）的alias。

scenes/scene03/schema_1st_migration.esdl

alias year_1994:= assert_exists(assert_single((select FuzzyTime 
                                    filter .fuzzy_year = 1994 
                                    and .fuzzy_month ?= <FuzzyMonth>{}
                                    and .fuzzy_day ?= <FuzzyDay>{}
                                    and .fuzzy_hour ?= <FuzzyHour>{}
                                    and .fuzzy_minute ?= <FuzzyMinute>{}
                                    and .fuzzy_second ?= <FuzzySecond>{}   
                                    and .fuzzy_dow ?= <DayOfWeek>{}
                    ))
);

alias police_station:= assert_exists(assert_single((select Landmark filter .name="警察局")));

新增test_scene03_alias function，並更新test_alias。

scenes/scene03/schema_1st_migration.esdl

function test_alias() -> bool
using (all({
        test_scene01_alias(),
        test_scene02_alias(),
        test_scene03_alias(),
    })
);

function test_scene03_alias() -> bool
using (all({
        (exists year_1994),   
        (exists police_station),   
    })
);

make 1st migration here（scenes/scene03/schema_1st_migration.esdl）

did you create alias 'default::police_station'? [y,n,l,c,b,s,q,?]
> y
did you create alias 'default::year_1994'? [y,n,l,c,b,s,q,?]
> y
did you create function 'default::test_scene03_alias'? [y,n,l,c,b,s,q,?]
> y
did you alter function 'default::test_alias'? [y,n,l,c,b,s,q,?]
> y

測試test_alias

scenes/scene03/query.edgeql

# 1st migration needs to be applied before running this query
select test_alias();

update lau

假設建明由學校畢業至1994年間，官階為PC。

scenes/scene03/query.edgeql

update lau set { 
    police_rank:= PoliceRank.PC
};

insert ChenLauContact

scenes/scene03/query.edgeql

insert ChenLauContact {
    how:= "面對面",
    detail:= "建明逮捕永仁並在警局替其做筆錄。",
    `when`:= year_1994,
    where:= police_station,
};

建立Archive及CriminalRecord

我們需要一個object type來記錄永仁的犯罪記錄。我們選擇建立一個abstract object type Archive，並建立一個object type CriminalRecord來extending Archive。

CriminalRecord有四個property及一個multi link：

• ref_no property為必填的檔案編號，並使用constraint exclusive，確保此編號不會重覆。
• code property為必填的犯罪代碼。
• involved multi link為一眾涉案人等。
• created_at property為檔案建立時間。使用rewrite在insert時，以datetime_of_statement()為預設值。此外，我們還加上readonly := true的限制，這麼一來就無法修改檔案的建立時間。
• modified_at property為檔案修改時間。使用rewrite在update時，自動以datetime_of_statement()覆寫。

scenes/scene03/schema.esdl

abstract type Archive;

type CriminalRecord extending Archive {
    required ref_no: str {
        constraint exclusive;
    };
    required code: str;
    multi involved: Character;
    created_at: datetime {
        readonly := true;
        rewrite insert using (datetime_of_statement())
    }
    modified_at: datetime {
        rewrite update using (datetime_of_statement())
    }
}

make end migration here（scenes/scene03/schema.esdl）

did you create object type 'default::Archive'? [y,n,l,c,b,s,q,?]
> y
did you create object type 'default::CriminalRecord'? [y,n,l,c,b,s,q,?]
> y

insert CriminalRecord

我們選擇使用tuple搭配for-loop來insert片中出現的兩次犯罪記錄，這個模式在EdgeDB中稱為bulk inserts。

• 於with區塊中，建立一個records set，裡面有兩個tuple代表兩次犯罪記錄。tuple的第一個元素為ref_no，而第二個元素為code。
• 接著使用for-loop + union + (insert CriminalRecord)的語法，來insert兩次犯罪記錄。我們可以使用.0來取得tuple的第一個元素、.1來取得tuple的第二個元素，依此類推。

scenes/scene03/query.edgeql

# end migration needs to be applied before running this query
with records:= {("CCR9314768", "OFFNCE: A.O.A.B.H   "), ("RN992317", "CD-POD   ")},
for record in records
union (insert CriminalRecord {
                ref_no:= record.0, 
                code:= record.1,
                involved:= chen,
});

記得加上union (...)

剛開始接觸for-loop語法時，很容易會忘記加上union。此外，union後面接的是()而非{}，這也是一個常會犯錯的地方。

也可以使用named tuple的語法

with records:= {(ref_no:= "CCR9314768", code:= "OFFNCE: A.O.A.B.H   "), (ref_no:= "RN992317", code:= "CD-POD   ")},
for record in records
union (insert CriminalRecord {
                ref_no:= record.ref_no, 
                code:= record.code,
                involved:= chen,
});

如果insert順序很重要

由於set是無序的，所以insert的順序並不能保證。如果想要有確定的insert順序，需要搭配array與range_unpack。

with records:= [("CCR9314768", "OFFNCE: A.O.A.B.H   "), ("RN992317", "CD-POD   ")],
    record_len:= len(records),
for i in range_unpack(range(0, record_len))
union (insert CriminalRecord {
                ref_no:= array_get(records, i).0, 
                code:= array_get(records, i).1, 
                involved:= chen,
});

其原理是array是有序的，可以使用array_get來索引，而range_unpack可以有序地返回range內的值。不過，這樣的需求應該不太常見。

select CriminalRecord {**}

我們可以使用splats的語法，來看看兩次insert是否成功。

select type {*} vs select type {**}

• select type {*}可以列出一個type的所有property。
• select type {**}除了可以列出select type {*}所列出的，還可以列出type所有的link type（但只會列出一層深度，不會遞迴全部列出）。

scenes/scene03/query.edgeql

select CriminalRecord {**};

{
  default::CriminalRecord {
    id: 740a4c4c-bc4b-11ee-b314-afc01e783d2e,
    code: 'OFFNCE: A.O.A.B.H   ',
    created_at: <datetime>'2024-01-26T13:04:35.046364Z',
    modified_at: {},
    ref_no: 'CCR9314768',
    involved: {
      default::PoliceSpy {
        nickname: '仁哥',
        name: '陳永仁',
        eng_name: {},
        id: db49a9aa-bc48-11ee-aae4-4fe16706e7ad,
        classic_lines: {},
      },
    },
  },
  default::CriminalRecord {
    id: 740a56ce-bc4b-11ee-b314-bf9055e83044,
    code: 'CD-POD   ',
    created_at: <datetime>'2024-01-26T13:04:35.046364Z',
    modified_at: {},
    ref_no: 'RN992317',
    involved: {
      default::PoliceSpy {
        nickname: '仁哥',
        name: '陳永仁',
        eng_name: {},
        id: db49a9aa-bc48-11ee-aae4-4fe16706e7ad,
        classic_lines: {},
      },
    },
  },
}

從上述結果可以觀察到：

• created_at自動於insert時，產生檔案建立時間了。
• 因為我們還沒有對檔案update，所以modified_at為空set。
• 我們在輸入code property時，在其後多加了許多空格，讓我們學習如何update吧。

Timezone

不知道您有沒有注意到因為created_at與modified_at為datetime型態，所以其是帶有timezone資訊的（預設是UTC）。 或許您會有衝動想要將其轉為香港當地時間，但當時香港仍是英國殖民地，或許所有犯罪記錄會以英國時區的UTC儲存在英國伺服器。 Who knows? 讓我們暫時接受這個設定，即使它可能是個美麗的錯誤。

update CriminalRecord

for-loop除了可以用在insert外，也能夠用在update。 由於code property是str型態，經過翻找文件之後，我們發現str_trim_end正好可以滿足需求。

scenes/scene03/query.edgeql

for record in CriminalRecord
union (
    update record
    set {
        code:= str_trim_end(.code)
    }
);

再次select CriminalRecord {**}

scenes/scene03/query.edgeql

select CriminalRecord {**};

{
  default::CriminalRecord {
    id: 740a4c4c-bc4b-11ee-b314-afc01e783d2e,
    code: 'OFFNCE: A.O.A.B.H',
    created_at: <datetime>'2024-01-26T13:04:35.046364Z',
    modified_at: <datetime>'2024-01-26T13:05:35.533826Z',
    ref_no: 'CCR9314768',
    involved: {
      default::PoliceSpy {
        nickname: '仁哥',
        name: '陳永仁',
        eng_name: {},
        id: db49a9aa-bc48-11ee-aae4-4fe16706e7ad,
        classic_lines: {},
      },
    },
  },
  default::CriminalRecord {
    id: 740a56ce-bc4b-11ee-b314-bf9055e83044,
    code: 'CD-POD',
    created_at: <datetime>'2024-01-26T13:04:35.046364Z',
    modified_at: <datetime>'2024-01-26T13:05:35.533826Z',
    ref_no: 'RN992317',
    involved: {
      default::PoliceSpy {
        nickname: '仁哥',
        name: '陳永仁',
        eng_name: {},
        id: db49a9aa-bc48-11ee-aae4-4fe16706e7ad,
        classic_lines: {},
      },
    },
  },
}

從上述結果可以觀察到：

• created_at並沒有變動。
• modified_at自動於update時更新。
• 使用str_trim_end成功去除了code property後的多餘空格。

學習使用backlinks

假設現在我們想知道永仁有哪些犯罪記錄，但是卻不想從CriminalRecord下手的話，backlinks是一個不錯的選擇。

由於CriminalRecord中的involved是個multi link，連接了involved及Character。backlinks讓我們可以反向來對這種關係進行query：

• [is type]讓我們指定要尋找哪一個type下的link。
• .<link是指[is type]這個type下的哪一個link。

scenes/scene03/query.edgeql

select chen {criminal_records:= .<involved[is CriminalRecord] {**}};

上面這段query的白話文

從CriminalRecord的involved link中，找出跟chen有關的CriminalRecord，命名為criminal_records，並使用{**}列出結果。

{
  default::PoliceSpy {
    criminal_records: {
      default::CriminalRecord {
        id: 740a4c4c-bc4b-11ee-b314-afc01e783d2e,
        code: 'OFFNCE: A.O.A.B.H',
        created_at: <datetime>'2024-01-26T13:04:35.046364Z',
        modified_at: <datetime>'2024-01-26T13:05:35.533826Z',
        ref_no: 'CCR9314768',
        involved: {
          default::PoliceSpy {
            nickname: '仁哥',
            name: '陳永仁',
            eng_name: {},
            id: db49a9aa-bc48-11ee-aae4-4fe16706e7ad,
            classic_lines: {},
          },
        },
      },
      default::CriminalRecord {
        id: 740a56ce-bc4b-11ee-b314-bf9055e83044,
        code: 'CD-POD',
        created_at: <datetime>'2024-01-26T13:04:35.046364Z',
        modified_at: <datetime>'2024-01-26T13:05:35.533826Z',
        ref_no: 'RN992317',
        involved: {
          default::PoliceSpy {
            nickname: '仁哥',
            name: '陳永仁',
            eng_name: {},
            id: db49a9aa-bc48-11ee-aae4-4fe16706e7ad,
            classic_lines: {},
          },
        },
      },
    },
  },
}

如果想知道全部Character的CriminalRecord，query可以這麼寫：

scenes/scene03/query.edgeql

select Character {name, criminal_records:= .<involved[is CriminalRecord] {**}};

加上易辨別的property

由於現在是對Character進行query（不是明確的chen了），加上name的話會比較好辨別，否則只能看到每個Character的criminal_records。

{
  default::GangsterBoss {name: '韓琛', criminal_records: {}},
  default::Police {name: '黃志誠', criminal_records: {}},
  default::PoliceSpy {
    name: '陳永仁',
    criminal_records: {
      default::CriminalRecord {
        id: 740a4c4c-bc4b-11ee-b314-afc01e783d2e,
        code: 'OFFNCE: A.O.A.B.H',
        created_at: <datetime>'2024-01-26T13:04:35.046364Z',
        modified_at: <datetime>'2024-01-26T13:05:35.533826Z',
        ref_no: 'CCR9314768',
        involved: {
          default::PoliceSpy {
            nickname: '仁哥',
            name: '陳永仁',
            eng_name: {},
            id: db49a9aa-bc48-11ee-aae4-4fe16706e7ad,
            classic_lines: {},
          },
        },
      },
      default::CriminalRecord {
        id: 740a56ce-bc4b-11ee-b314-bf9055e83044,
        code: 'CD-POD',
        created_at: <datetime>'2024-01-26T13:04:35.046364Z',
        modified_at: <datetime>'2024-01-26T13:05:35.533826Z',
        ref_no: 'RN992317',
        involved: {
          default::PoliceSpy {
            nickname: '仁哥',
            name: '陳永仁',
            eng_name: {},
            id: db49a9aa-bc48-11ee-aae4-4fe16706e7ad,
            classic_lines: {},
          },
        },
      },
    },
  },
  default::GangsterSpy {name: '劉建明', criminal_records: {}},
}

insert此場景的Scene

scenes/scene03/query.edgeql

insert Scene {
      title:= "黑白顛倒",
      detail:= "永仁留下多次案底，並曾經被建明逮捕，但也逐漸取得黑社會的信任。" ++
               "建明畢業後則由警員（PC）做起，表現優異，獲面試晉陞見習督察（PI）" ++
               "的機會。兩人的路就像黑白顛倒一般，誰是好人，誰又是壞人呢？",
      remarks:= "1.假設此時為1994年。",       
      who:= {chen, lau},
      `when`:= year_1994,
      where:= police_station,  
};

Query review

Query review

scenes/scene03/query.edgeql

insert FuzzyTime {fuzzy_year:= 1994};

insert Landmark {name:= "警察局"};

select test_alias();

update lau set { 
    police_rank:= PoliceRank.PC
};

insert ChenLauContact {
    how:= "面對面",
    detail:= "建明逮捕永仁並在警局替其做筆錄。",
    `when`:= year_1994,
    where:= police_station,
};

with records:= {("CCR9314768", "OFFNCE: A.O.A.B.H   "), ("RN992317", "CD-POD   ")},
for record in records
union (insert CriminalRecord {
                ref_no:= record.0, 
                code:= record.1,
                involved:= chen,
});



select CriminalRecord {**};

for record in CriminalRecord
union (
    update record
    set {
        code:= str_trim_end(.code)
    }
);

select CriminalRecord {**};

select chen {criminal_records:= .<involved[is CriminalRecord] {**}};

select Character {name, criminal_records:= .<involved[is CriminalRecord] {**}};

insert Scene {
      title:= "黑白顛倒",
      detail:= "永仁留下多次案底，並曾經被建明逮捕，但也逐漸取得黑社會的信任。" ++
               "建明畢業後則由警員（PC）做起，表現優異，獲面試晉陞見習督察（PI）" ++
               "的機會。兩人的路就像黑白顛倒一般，誰是好人，誰又是壞人呢？",
      remarks:= "1.假設此時為1994年。",       
      who:= {chen, lau},
      `when`:= year_1994,
      where:= police_station,  
};

無間假設

我們假設無間道內警察只會在同一個地方辦公，即police_station這個alias。從建明與黃sir的對話來看，大多數警察辦公場景應該是在警察總部。

無間吹水

假設建明於1994年仍是散仔（PC），其於劇中識別證之更換時間為1999年7月30日，時任高級督察（SIP），在四~五年間連升數級，這又是一個如葉校長般的人物呀。