08 - 誰是內鬼

Full schema preview

Full schema preview

start1st migrationend migration

scenes/scene07/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;
    scalar type TeamTreatNumber 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 Beverage {
        required name: str;
        produced_by: Store;
        consumed_by: Character;
        `when`: FuzzyTime;
        where: Place;
    }

    type CIBTeamTreat {
        required team_treat_number: TeamTreatNumber {
            constraint exclusive;
            default := sequence_next(introspect TeamTreatNumber);
        }
        multi colleagues: Police {
            default:= (select Police filter .dept="刑事情報科(CIB)");
            readonly := true;
            point: int64 {
                default:= <int64>math::ceil(random()*10)
            }
        };
        team_treat:= max(.colleagues@point) >= 9
    }

    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>{}
                    ))
    );
    alias year_2002:= assert_exists(assert_single((select FuzzyTime 
                                        filter .fuzzy_year = 2002 
                                        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(),
            test_scene05_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_scene05_alias() -> bool
    using (all({
            (exists year_1994),
        })
    );

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

}

scenes/scene08/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;
    scalar type TeamTreatNumber 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 Envelope {
        name: str {
            default:= "標";
            readonly:= true
        };
        access policy allow_select_insert_delete
            allow select, insert, delete;

        access policy only_one_envelope_exists
            deny insert
            using (exists Envelope)
            {
                errmessage := 'Only one Envelope can be existed.'
            };
    }

    type Beverage {
        required name: str;
        produced_by: Store;
        consumed_by: Character;
        `when`: FuzzyTime;
        where: Place;
    }

    type CIBTeamTreat {
        required team_treat_number: TeamTreatNumber {
            constraint exclusive;
            default := sequence_next(introspect TeamTreatNumber);
        }
        multi colleagues: Police {
            default:= (select Police filter .dept="刑事情報科(CIB)");
            readonly := true;
            point: int64 {
                default:= <int64>math::ceil(random()*10)
            }
        };
        team_treat:= max(.colleagues@point) >= 9
    }

    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>{}
                    ))
    );
    alias year_2002:= assert_exists(assert_single((select FuzzyTime 
                                        filter .fuzzy_year = 2002 
                                        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(),
            test_scene05_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_scene05_alias() -> bool
    using (all({
            (exists year_1994),
        })
    );

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

}

scenes/scene08/schema.esdl

using extension pg_trgm;

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;
    scalar type TeamTreatNumber 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 Envelope {
        name: str {
            default:= "標";
            readonly:= true
        };
        access policy allow_select_insert_delete
            allow select, insert, delete;

        access policy only_one_envelope_exists
            deny insert
            using (exists Envelope)
            {
                errmessage := 'Only one Envelope can be existed.'
            };
    }

    type Beverage {
        required name: str;
        produced_by: Store;
        consumed_by: Character;
        `when`: FuzzyTime;
        where: Place;
    }

    type CIBTeamTreat {
        required team_treat_number: TeamTreatNumber {
            constraint exclusive;
            default := sequence_next(introspect TeamTreatNumber);
        }
        multi colleagues: Police {
            default:= (select Police filter .dept="刑事情報科(CIB)");
            readonly := true;
            point: int64 {
                default:= <int64>math::ceil(random()*10)
            }
        };
        team_treat:= max(.colleagues@point) >= 9
    }

    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>{}
                    ))
    );
    alias year_2002:= assert_exists(assert_single((select FuzzyTime 
                                        filter .fuzzy_year = 2002 
                                        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(),
            test_scene05_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_scene05_alias() -> bool
    using (all({
            (exists year_1994),
        })
    );

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

}

劇情提要

此劇照引用自IMDb-無間道

毒品交易失敗後，韓琛確信身邊有警察臥底。依照建明要求，將所有手下的個人資料裝在信封中，於電影院L13位置交給建明。永仁偷偷在後觀察並尾隨建明離開，欲看清其模樣，不料手機突然響起，因而錯失良機。而建明也隱約感覺到有人跟蹤，隱於牆後查看但未發現人跡。另一方面，警隊高層也懷疑韓琛安插了臥底，於是將建明調至內務部並在O記辦公，專門調查此事。建明依照韓琛所給資料於警隊資料庫中進行搜尋，卻無發現。於此同時，韓琛試探了身邊幾個親近手下，包括永仁與傻強。

EdgeQL query

建立Envelope

由於信封在本劇後半段是一個重要的物件，除了裡面有參與韓琛毒品交易手下的資料外，更特別的是信封上有一個永仁親手寫的「標」字。於是我們想為信封建立一個object type，但同時希望這麼特別的信封只能被生成一次，也就是只能insert一個Envelope object。

我們準備借助EdgeDB的access policy來完成這個需求，其有特殊的resolution order：

• 當object type上沒有施加任何access policy時，這個object type可以被讀取及變動。

• 當object type上有施加任何access policy時，會拆成三個步驟來決定允許操作的範圍：

  • 首先，所有操作先變為deny。
  • 接著，允許標示有allow的操作。
  • 最後，排除標示有deny的操作。

依照上述原理，我們可以建立Envelope的schema，其有一個property及兩個access policy：

• name property為永仁所寫的錯別字「標」，並設定readonly為true。代表當給予預設值後，無法變更此property。
• 第一個policy命名為allow_select_insert_delete，允許進行select、 insert及delete。
• 第二個policy命名為only_one_envelope_exists，使用using (exists Envelope)作為判斷條件，當資料庫中已經有存在Envelope object時，拒絕insert，並給定客製化的報錯訊息Only one Envelope can be existed.。

scenes/scene08/schema_1st_migration.esdl

type Envelope {
    name: str {
        default:= "標";
        readonly:= true
    };
    access policy allow_select_insert_delete
        allow select, insert, delete;

    access policy only_one_envelope_exists
        deny insert
        using (exists Envelope)
        {
            errmessage := 'Only one Envelope can be existed.'
        };
}

readonly vs access policy

readonly是用在property上的constraint，access policy是適用在整個object type上。不過在我們這個例子中，因為沒有allow update，所以Envelope是不能update的，因此如果將readonly刪除也可以。此外，如果在這個例子中執行updatequery的話，EdgeDB並不會報錯，只會返回一個空set。

access policy的延伸應用

既然access policy可以讓我們限制insert的次數，這麼一來我們也可以延伸應用到對照警察職級表，來限制各職級的人數，例如只能insert一名處長（CP）級長官。

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

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

insert Envelope

執行下面query可以成功insert一個Envelope object。

scenes/scene08/query.edgeql

insert Envelope;

但是如果想再insert一個Envelope object的話，則會報錯如下：

報錯訊息

edgedb error: AccessPolicyError: access policy violation on insert of default::Envelope (Only one Envelope can be existed.)

我們的客製化錯誤訊息成功被印出。

update lau

建明受上級指示調至內務部並在O記辦公，調查韓琛臥底。

scenes/scene08/query.edgeql

update lau 
set {
    dept:= "投訴及內部調查科", 
};

使用ext::pg_trgm

建明拿到信封後，想利用警隊的資料庫系統查詢，參與毒品交易手下們的名字有沒有在其中。此時他可以利用EdgeDB的ext::pg_trgmextension來查詢。

scenes/scene08/schema.esdl

using extension pg_trgm;

module default {
    ...
}

extension不可置於module內

留意using extension module;的位置，不可以置於任何module內。

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

did you create extension 'pg_trgm'? [y,n,l,c,b,s,q,?]
> y

學習使用ext::pg_trgm

如果需要的是full text search

可以試試內建的fts模組。但是我自己在使用fts::index後做migration，常常會失敗。可能是我還沒掌握到正確使用方式或是版本功能尚未穩定。

word_similar()

首先建明使用ext::pg_trgm::word_similar()來進行查詢。這個function會計算第一個參數與第二個參數的任意部份相似的程度，並依據最高的分數是否超過預先設定的門檻值，來回傳bool值。

scenes/scene08/query.edgeql

with names:= array_join(array_agg(Police.name), " "), 
       module ext::pg_trgm,
select word_similar("陳永仁", names);

{false}

這段query看起來有點複雜，我們逐個拆解：

• 在with區塊中，利用array_agg將Police.name這個set變為array。接著利用array_join將array中每一個element（str型態）用" "連接起來，命名為names。
• 在with區塊中，將預設module由default轉為ext::pg_trgm。
• 因為轉變了預設module，所以可以直接使用word_similar()查詢。

由於陳永仁這個名字的確沒有出現在Police.name中（陳永仁是PoliceSpy），所以建明得到false。

word_similarity()

如果建明不死心，想知道最高的分數實際上是多少的話，可以使用ext::pg_trgm::word_similarity()。

scenes/scene08/query.edgeql

with names:= array_join(array_agg(Police.name), " "), 
       module ext::pg_trgm,
select word_similarity("陳永仁", names);

{0}

最終建明得到了最低分的0分，這下他徹底死心了。

平時時空的建明

word_similar()

假設平時時空的建明，得知警隊除了平常可以接觸的資料庫外，還有一個機密資料庫，所有臥底檔案都在其中，而他已設法取得權限。 因為擁有存取IsPolice及PoliceSpy兩個object type的權限，他將可以進行下列query：

scenes/scene08/query.edgeql

with is_police_spy:= (select IsPolice filter .police_rank=PoliceRank.Protected),
     police_spy:= (select PoliceSpy filter .id in is_police_spy.id),
     names:= array_join(array_agg(Police.name union police_spy.name), " "), 
     module ext::pg_trgm,
select word_similar("陳永仁", names);

{true}

逐步拆解這段query：

• 在with區塊中，尋找IsPolice中哪些人的police_rank是PoliceRank.Protected並命名為is_police_spy，此時他僅能得到IsPolice中的資訊（即id、police_rank、dept及is_officer而已）。
• 在with區塊中，尋找PoliceSpy中哪些人的id在is_police_spy中並命名為police_spy。
• 在with區塊中，使用array_agg將police_spy.name及police_spy.name合成一個array，接著使用array_join將這個array以" "連接起來， 並命名為names。
• 在with區塊中，將預設module由default轉為ext::pg_trgm。
• 因為轉變了預設module，所以可以直接使用word_similar()查詢。

這一次平時時空的建明得到true，成功找出永仁。

word_similarity()

如果建明使用word_similarity，其query會像是：

scenes/scene08/query.edgeql

with is_police_spy:= (select IsPolice filter .police_rank=PoliceRank.Protected),
     police_spy:= (select PoliceSpy filter .id in is_police_spy.id),
     names:= array_join(array_agg(Police.name union police_spy.name), " "), 
     module ext::pg_trgm,
select word_similarity("陳永仁", names);

{1}

建明會得到1分的最高分，一樣成功找出永仁。

此外，我們假設建明將陳永仁誤植為陳永仨，其query會像是：

scenes/scene08/query.edgeql

with is_police_spy:= (select IsPolice filter .police_rank=PoliceRank.Protected),
     police_spy:= (select PoliceSpy filter .id in is_police_spy.id),
     names:= array_join(array_agg(Police.name union police_spy.name), " "), 
     module ext::pg_trgm,
select word_similarity("陳永仨", names);

{0.5}

這一次建明得到不低的0.5分，他將會以陳永仨為線索之一，繼續追查下去。

with區塊注意事項

with module相當於轉換預設module

下面這段query是錯誤的。

# ❌
with module ext::pg_trgm,
       names:= array_join(array_agg(Police.name), " "), 
select word_similar("陳永仁", names);

其報錯訊息為：

error: InvalidReferenceError: object type or alias 'ext::pg_trgm::Police' does not exist

因為此時module已經由default轉為ext::pg_trgm，而EdgeDB於ext::pg_trgm中找不到Police，所以報錯。

使用default::Police.name的query則可成功執行：

# ✅
with module ext::pg_trgm,
       names:= array_join(array_agg(default::Police.name), " "), 
select word_similar("陳永仁", names);

變數引用

我們可以在with區塊內，使用前面定義的變數，例如：

with is_police_spy:= (select IsPolice filter .police_rank=PoliceRank.Protected),
     police_spy:= (select PoliceSpy filter .id in is_police_spy.id),
     names:= array_join(array_agg(Police.name union police_spy.name), " "), 
     module ext::pg_trgm,
select word_similarity("陳永仁", names);

• police_spy引用了前面定義的is_police_spy。
• names引用了前面定義的police_spy。

insert此場景的Scene

scenes/scene08/query.edgeql

insert Scene {
      title:= "誰是內鬼", 
      detail:= "毒品交易失敗後，韓琛確信身邊有警察臥底。依照建明要求，" ++
               "將所有小弟的個人資料裝在信封中，於電影院L13位置交給建" ++
               "明。永仁偷偷在後觀察並尾隨建明離開，欲看清其模樣，不料" ++
               "手機突然響起，因而錯失良機。而建明也隱約感覺到有人跟蹤，" ++
               "隱於牆後查看但未發現人跡。另一方面，警隊高層也懷疑韓琛" ++
               "安插了臥底，於是將建明調至內務部並在O記辦公，專門調查此" ++
               "事。建明依照韓琛所給資料於警隊資料庫中進行搜尋，卻無發現" ++
               "。於此同時，韓琛試探了身邊所有小弟，包括永仁與傻強...",
      who:= {hon, chen, lau},
      `when`:= (insert FuzzyTime {
                     fuzzy_year:= 2002,
                     fuzzy_month:= 11,
                     fuzzy_day:= 23,
              }),
      where:= police_station union (insert Location {name:="電影院"}),         
};

Query review

Query review

scenes/scene08/query.edgeql

insert Envelope;

update lau 
set {
    dept:= "投訴及內部調查科", 
};

with names:= array_join(array_agg(Police.name), " "), 
       module ext::pg_trgm,
select word_similar("陳永仁", names);

with names:= array_join(array_agg(Police.name), " "), 
       module ext::pg_trgm,
select word_similarity("陳永仁", names);

with is_police_spy:= (select IsPolice filter .police_rank=PoliceRank.Protected),
     police_spy:= (select PoliceSpy filter .id in is_police_spy.id),
     names:= array_join(array_agg(Police.name union police_spy.name), " "), 
     module ext::pg_trgm,
select word_similar("陳永仁", names);

with is_police_spy:= (select IsPolice filter .police_rank=PoliceRank.Protected),
     police_spy:= (select PoliceSpy filter .id in is_police_spy.id),
     names:= array_join(array_agg(Police.name union police_spy.name), " "), 
     module ext::pg_trgm,
select word_similarity("陳永仁", names);

with is_police_spy:= (select IsPolice filter .police_rank=PoliceRank.Protected),
     police_spy:= (select PoliceSpy filter .id in is_police_spy.id),
     names:= array_join(array_agg(Police.name union police_spy.name), " "), 
     module ext::pg_trgm,
select word_similarity("陳永仨", names);

insert Scene {
      title:= "誰是內鬼", 
      detail:= "毒品交易失敗後，韓琛確信身邊有警察臥底。依照建明要求，" ++
               "將所有小弟的個人資料裝在信封中，於電影院L13位置交給建" ++
               "明。永仁偷偷在後觀察並尾隨建明離開，欲看清其模樣，不料" ++
               "手機突然響起，因而錯失良機。而建明也隱約感覺到有人跟蹤，" ++
               "隱於牆後查看但未發現人跡。另一方面，警隊高層也懷疑韓琛" ++
               "安插了臥底，於是將建明調至內務部並在O記辦公，專門調查此" ++
               "事。建明依照韓琛所給資料於警隊資料庫中進行搜尋，卻無發現" ++
               "。於此同時，韓琛試探了身邊所有小弟，包括永仁與傻強...",
      who:= {hon, chen, lau},
      `when`:= (insert FuzzyTime {
                     fuzzy_year:= 2002,
                     fuzzy_month:= 11,
                     fuzzy_day:= 23,
              }),
      where:= police_station union (insert Location {name:="電影院"}),         
};

無間吹水

黃sir於劇末的墓碑往生日期為2002年11月23日，而其識別證上更換日期卻為2008年7月31日。