04 - 被遺忘的時光

Full schema preview

startend migration

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),   
        })
    );

}

scenes/scene04/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>{}
                    ))
    );

    # functions
    function is_hi_fi_store_open(dow: DayOfWeek, visit_hour: int64) -> bool
    #
    # The store will open 11:00~22:00 everyday, except:
    # will close on Wednesdays.
    # will close during 13:00~14:00 and 19:00~20:00 everyday.
    #
    using (
        with open_hours:= multirange([range(11, 13), range(14, 19), range(20, 22)])
        select dow != DayOfWeek.Wednesday and contains(open_hours, visit_hour)
    );

    # 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),   
        })
    );

    function test_hi_fi_store_open() -> bool
    using (all({
          is_hi_fi_store_open(DayOfWeek.Monday, 12),
          is_hi_fi_store_open(DayOfWeek.Friday, 15),
          is_hi_fi_store_open(DayOfWeek.Saturday, 21),
        })
    );

    function test_hi_fi_store_close() -> bool 
    using (not all({
          is_hi_fi_store_open(DayOfWeek.Wednesday, 12),
          is_hi_fi_store_open(DayOfWeek.Thursday, 13),
          is_hi_fi_store_open(DayOfWeek.Sunday, 19),
        })
    );

}

劇情提要

臥底近十年後，建明與永仁在Hi-Fi鋪相遇。建明請永仁推薦設備，並一起試聽了被遺忘的時光。試聽過程中，建明請永仁換了一條音源線，歌聲立刻變得更加立體，好像真人就在眼前唱歌一般。畢竟這首歌建明聽過太多次，有太多懷念的過去（詳情請見無間道Ⅱ）。

EdgeQL query

`insert`此場景時間2002年11月28日

scenes/scene04/query.edgeql

insert FuzzyTime {fuzzy_year:= 2002, fuzzy_month:=11, fuzzy_day:=28};

`insert`店家Hi-Fi鋪

scenes/scene04/query.edgeql

insert Store {name:="Hi-Fi鋪"};

`update` `lau`

增加飾演建明十年後的演員劉德華至lau的actors multi link中（留意這邊使用的是+=）。

和nested inserts有點像，我們不需要先insert劉德華，再update lau，直接於update時insert即可。

scenes/scene04/query.edgeql

update lau 
set {
    actors+= (insert Actor {
            name:="劉德華",
            eng_name:= "Andy",
            nickname:= "華仔",
   })
};

`update` `chen`

chen的update可以同時：

將經典台詞「高音甜、中音準、低音勁。一句講哂，通透啦即係。」指定給classic_lines property（留意這邊使用的是:=）。
增加飾演永仁十年後的演員梁朝偉至chen的actors multi link中（留意這邊使用的是+=）。

scenes/scene04/query.edgeql

update chen 
set {
    classic_lines := ["高音甜、中音準、低音勁。一句講哂，通透啦即係。"],
    actors+= (insert Actor {
            name:="梁朝偉",
            eng_name:= "Tony",
            nickname:= "偉仔",
   })
};

`insert` `ChenLauContact`

scenes/scene04/query.edgeql

insert ChenLauContact {
    how:= "面對面",
    detail:= "臥底近十年後，建明與永仁在Hi-Fi鋪相遇，一起試聽了`被遺忘的時光", 
    `when`:=  assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/28_HH24:MI:SS_ID")),
    where:= assert_single((select Store filter .name="Hi-Fi鋪")),
};

編寫`is_hi_fi_store_open`

如果我們想要知道Hi-Fi鋪是否處於營業時間，可以寫一個is_hi_fi_store_open的function來判斷。

假設Hi-Fi鋪每天的營業時間為11:00~22:00，但：

每星期三公休。
13:00~14:00及19:00~20:00為休息時間。

range vs multiranges

判斷某個數字是否在單一區間內，可以使用range()。但如果有多個區間的話，則可以搭配multirange()來處理。

is_hi_fi_store_open function接收兩個變數，一個是dow（DayOfWeek型態）代表星期幾造訪，另一個是visit_hour（int64型態）代表幾點造訪，回傳值則為bool型態。

我們將營業時間拆成11~13、14~19及20~22三個range並包成一個array後，傳給一個multirange，並在with區塊中將其命名為open_hours。

接著判斷dow是否不是星期三，且visit_hour是否在open_hours區間內（使用contains）。如果是的話，代表該時間為店家營業時間，回傳true；否則即為休息時間，回傳false。

scenes/scene04/schema.esdl

function is_hi_fi_store_open(dow: DayOfWeek, visit_hour: int64) -> bool
using (
    with open_hours:= multirange([range(11, 13), range(14, 19), range(20, 22)])
    select dow != DayOfWeek.Wednesday and contains(open_hours, visit_hour)
);

編寫`test_hi_fi_store_open`及`test_hi_fi_store_close`

搭配all和not可以編寫test_hi_fi_store_open及test_hi_fi_store_close來確認is_hi_fi_store_open是否可以準確依照傳入時間，回傳正確的bool值。

scenes/scene04/schema.esdl

function test_hi_fi_store_open() -> bool
using (all({
        is_hi_fi_store_open(DayOfWeek.Monday, 12),
        is_hi_fi_store_open(DayOfWeek.Friday, 15),
        is_hi_fi_store_open(DayOfWeek.Saturday, 21),
    })
);

function test_hi_fi_store_close() -> bool 
using (not all({
        is_hi_fi_store_open(DayOfWeek.Wednesday, 12),
        is_hi_fi_store_open(DayOfWeek.Thursday, 13),
        is_hi_fi_store_open(DayOfWeek.Sunday, 19),
    })
);

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

did you create function 'default::is_hi_fi_store_open'? [y,n,l,c,b,s,q,?]
> y
did you create function 'default::test_hi_fi_store_close'? [y,n,l,c,b,s,q,?]
> y
did you create function 'default::test_hi_fi_store_open'? [y,n,l,c,b,s,q,?]
> y

測試`test_hi_fi_store_open`及`test_hi_fi_store_close`

scenes/scene04/query.edgeql

select test_hi_fi_store_open(); # {true}
select test_hi_fi_store_close(); # {true}

`insert`此場景的`Scene`

scenes/scene04/query.edgeql

insert Scene {
    title:= "被遺忘的時光",
    detail:= "臥底近十年後，建明與永仁在Hi-Fi鋪相遇。建明請永仁推薦設備，並一起" ++
             "試聽了`被遺忘的時光`。試聽過程中，建明請永仁換了一條音源線，歌聲立" ++
             "刻變得更加立體，好像真人就在眼前唱歌一般，畢竟這首歌建明聽過太多次，" ++
             "有太多懷念的過去（詳情請見無間道Ⅱ）。",
    remarks:= "簽單日期為2002/11/28", 
    who:= {chen, lau},
    `when`:=  assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/28_HH24:MI:SS_ID")),
    where:= assert_single((select Store filter .name="Hi-Fi鋪")),         
};

Query review

scenes/scene04/query.edgeql

insert FuzzyTime {fuzzy_year:= 2002, fuzzy_month:=11, fuzzy_day:=28};

insert Store {name:="Hi-Fi鋪"};

update lau 
set {
    actors+= (insert Actor {
            name:="劉德華",
            eng_name:= "Andy",
            nickname:= "華仔",
   })
};

update chen 
set {
    classic_lines := ["高音甜、中音準、低音勁。一句講哂，通透啦即係。"],
    actors+= (insert Actor {
            name:="梁朝偉",
            eng_name:= "Tony",
            nickname:= "偉仔",
   })
};

insert ChenLauContact {
    how:= "面對面",
    detail:= "臥底近十年後，建明與永仁在Hi-Fi鋪相遇，一起試聽了`被遺忘的時光", 
    `when`:=  assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/28_HH24:MI:SS_ID")),
    where:= assert_single((select Store filter .name="Hi-Fi鋪")),
};

select test_hi_fi_store_open(); # {true}

select test_hi_fi_store_close(); # {true}

insert Scene {
    title:= "被遺忘的時光",
    detail:= "臥底近十年後，建明與永仁在Hi-Fi鋪相遇。建明請永仁推薦設備，並一起" ++
             "試聽了`被遺忘的時光`。試聽過程中，建明請永仁換了一條音源線，歌聲立" ++
             "刻變得更加立體，好像真人就在眼前唱歌一般，畢竟這首歌建明聽過太多次，" ++
             "有太多懷念的過去（詳情請見無間道Ⅱ）。",
    remarks:= "簽單日期為2002/11/28", 
    who:= {chen, lau},
    `when`:=  assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/28_HH24:MI:SS_ID")),
    where:= assert_single((select Store filter .name="Hi-Fi鋪")),         
};

無間吹水

永仁幫建明刷卡的單據日期為2002年11月28日，但永仁於劇末的墓碑往生日期為2002年11月27日，這個問題20年來留給觀眾許多討論空間。有人說這是因為墓碑日期為陰曆，有人則說這是導演與編劇特地想表達「無間輪迴」之意。但根據2022年4K修復版上映時的訪問，似乎這只是一個單純的道具準備疏失。

04 - 被遺忘的時光

Full schema preview

劇情提要

EdgeQL query

insert此場景時間2002年11月28日

insert店家Hi-Fi鋪

update lau

update chen

insert ChenLauContact

編寫is_hi_fi_store_open

編寫test_hi_fi_store_open及test_hi_fi_store_close

測試test_hi_fi_store_open及test_hi_fi_store_close

insert此場景的Scene

Query review

無間吹水

`insert`此場景時間2002年11月28日

`insert`店家Hi-Fi鋪

`update` `lau`

`update` `chen`

`insert` `ChenLauContact`

編寫`is_hi_fi_store_open`

編寫`test_hi_fi_store_open`及`test_hi_fi_store_close`

測試`test_hi_fi_store_open`及`test_hi_fi_store_close`

`insert`此場景的`Scene`