09 - 真相大白

Full schema preview

startend migration

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

}

scenes/scene09/schema.esdl

using extension pg_trgm;
using extension pgcrypto;

module default {
    # global types
    global current_user_id: uuid;

    # 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 {
        access policy authorized_allow_insert_update_delete
            allow insert, update, delete
            using (with police_officer:= (select IsPolice filter .id = global current_user_id),
                select if exists police_officer then (
                        police_officer.police_rank ?? PoliceRank.PC >= PoliceRank.DCP
                    ) else (
                        false
                    )                                                                                                                  
            )
            {
                errmessage := "PoliceRank required: PoliceRank.DCP"
            };

        access policy authorized_allow_select
            allow select
            using (with police_officer:= (select IsPolice filter .id = global current_user_id),
                select if exists police_officer then (
                        police_officer.police_rank ?? PoliceRank.PC >= PoliceRank.SP
                    ) else (
                        false
                    )                                                                                                                  
            )
            {
                errmessage := "PoliceRank required: PoliceRank.SP"
            };

    };

    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 PoliceSpyFile extending Archive {
        multi colleagues: PoliceSpy;
        classified_info: str; 

        access policy authorized_allow_all
            allow all
            using (with police_officer:= (select IsPolice filter .id = global current_user_id),
                select if exists police_officer then (
                        police_officer.police_rank ?? PoliceRank.PC >= PoliceRank.SP
                    ) else (
                        false
                    )                                                                                                                  
            )
        {
            errmessage := "PoliceRank required: PoliceRank.SP"
        };
    }

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

    alias morse_code_of_undercover:= str_replace("..- -. -.. . .-. -.-. --- ...- . .-.", "-", "_"); # undercover

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

    function get_stored_encrypted_password() -> str
    #
    # This function simulates retrieving the underlying stored encrypted password.
    #
    using (
        with code:= morse_code_of_undercover,
             module ext::pgcrypto, 
        select crypt(code, gen_salt())
    );

    function validate_password(code: str) -> bool
    #
    # https://www.edgedb.com/docs/stdlib/pgcrypto
    # 
    # Usage:
    # db> select validate_password(morse_code_of_undercover);
    #
    using (
        with hash:= get_stored_encrypted_password(),
             module ext::pgcrypto,
        select crypt(code, hash) = hash
    );

    function list_police_spy_names(code: str) -> json
    #
    # Noted that PoliceSpyFile is secured by the defined access policies.
    # Usage:
    # db> select list_police_spy_names(morse_code_of_undercover);
    # or 
    # wrapped in an api enpoint
    #
    using (
        with police_spy_file:= PoliceSpyFile if validate_password(code)
                               else <PoliceSpyFile>{},
             names:= array_agg(police_spy_file.colleagues.name),
        select json_object_pack({("names", <json>(names))})
    );

    # tests
    function test_alias() -> bool
    using (all({
            test_scene01_alias(),
            test_scene02_alias(),
            test_scene03_alias(),
            test_scene05_alias(),
            test_scene09_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_scene09_alias() -> bool
    using (all({
            (exists morse_code_of_undercover),
        })
    );

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

}

劇情提要

建明得知黃sir將與警方臥底於大廈見面，通知韓琛。韓琛一面派手下到大廈，一面進行毒品交易。黃sir為掩護永仁離開，被韓琛手下丟下大廈，寧願殉職而不發一言。黃sir死後，建明聯手永仁於停車場擊斃韓琛，最終兩人於警察局見面。當建明正幫永仁處理臥底檔案時，永仁發現其親手所寫帶有「標」字的信封竟然在建明桌上，頓時醒悟原來建明就是韓琛派至警隊的臥底，立即悄然離開。

警隊資安升級計畫

自從黃sir殉職之後，警隊高層了解在趕快找出韓琛臥底的同時，也需要保護好自己派出的臥底，於是決定全面重新檢查一遍資料庫的存取權限。

經過一番資安演練，IT部門也發現平行時空的建明所發現的事情，即是當同時擁有IsPolice及PoliceSpy的讀取權限時，是可以由IsPolice的id來找出其在PoliceSpy中的name。

由於現在能同時存取IsPolice及PoliceSpy的人數過多，高層決定做出以下變更：

PoliceSpy新增兩個access policy：
- 只有副處長級別以上（DCP）可以insert、update及delete。
- 只有警司級別以上（SP）可以select。
新增一個PoliceSpyFile方便各部門協同操作，只有警司級別以上（SP）可以執行全部操作。
於非內網登入系統時，不提供REPL操作。關於臥底資料僅提供一個list_police_spy_names的endpoint，且只有當操作者驗證為警司級別以上（SP）且密碼正確的情況下，才能取得警隊全部臥底的名字。

EdgeQL query

建立 `global` `current_user_id`

current_user_id是一個global scalar，讓我們在全域中都可以存取這個值。

scenes/scene09/schema.esdl

global current_user_id: uuid;

可以透過set這個指令來給定其值，如：

set global current_user_id:=<uuid>"ccc7a858-bd17-11ee-b4be-9f69662124af";

或透過reset將其回復為預設值，如：

reset global current_user_id;

由於我們沒有給定預設值，所以如果執行上述query時，會將global current_user_id變為空的<uuid>{}。

global為關鍵字

存取global scalar時，global關鍵字不可省略。

更新`PoliceSpy`

PoliceSpy新增兩個access policy：

scenes/scene09/schema.esdl

                          type PoliceSpy extending Character, IsSpy { access policy authorized_allow_insert_update_delete allow insert, update, delete using (with police_officer:= (select IsPolice filter .id = global current_user_id), select if exists police_officer then ( police_officer.police_rank ?? PoliceRank.PC >= PoliceRank.DCP ) else ( false )                                                                                                                   ) { errmessage := "PoliceRank required: PoliceRank.DCP" }; access policy authorized_allow_select allow select using (with police_officer:= (select IsPolice filter .id = global current_user_id), select if exists police_officer then ( police_officer.police_rank ?? PoliceRank.PC >= PoliceRank.SP ) else ( false )                                                                                                                   ) { errmessage := "PoliceRank required: PoliceRank.SP" }; ">}; 複雜，我們舉第一個access policy為例來看：

在with區塊內，確認global current_user_id現在所指定的id的確在IsPolice中。

接著在select中使用if cond then {} else {}的語法來判斷需要執行的query。我們用的判斷式是exists police_officer：

如果police_officer存在的話，我們執行police_officer.police_rank ?? PoliceRank.PC >= PoliceRank.DCP。這段query的意思是判斷所找到police_officer的police_rank是否高於PoliceRank.DCP，如果是的話，回傳true，否則回傳false。其中??是當police_officer.police_rank為空set時的預設值，我們預設其為官階最小的PoliceRank.PC。
如果police_officer不存在的話，回傳false。



這麼一來，我們滿足了第一個需求。
建立PoliceSpyFile
PoliceSpyFile extending Archive而來，有一個link、一個property及一個access policy：

colleagues是multi link指向PoliceSpy。
classified_info property為一str，代表所儲存的機密資訊。
access policy的寫法與PoliceSpy類似，但這邊是警司級別以上（SP）可以執行全部操作。

scenes/scene09/schema.esdltype PoliceSpyFile extending Archive {
    multi colleagues: PoliceSpy;
    classified_info: str; 

    access policy authorized_allow_all
        allow all
        using (with police_officer:= (select IsPolice filter .id = global current_user_id),
            select if exists police_officer then (
                    police_officer.police_rank ?? PoliceRank.PC >= PoliceRank.SP
                ) else (
                    false
                )                                                                                                                  
        )
    {
        errmessage := "PoliceRank required: PoliceRank.SP"
    };
}


新增PoliceSpyFile的妙用
警司級別以上（SP）可以對PoliceSpyFile進行全部操作，包括select colleagues，這有可能會選取到多個PoliceSpy。但要對這些PoliceSpy進行update或delete依然需要為副處長級別以上（DCP）。
PoliceSpyFile可以想成一個專案簡報，當您具備足夠權限的時候，可以對這個簡報做任何操作，包括引用專案檔案（但如果權限不足的話，將無法更新或刪除專案檔案）。

這麼一來，我們滿足了第二個需求。
編寫list_police_spy_names
list_police_spy_names大多數情況應該會被某種web framework寫出來的程式所呼叫（例如Python的FastAPI或Rust的Axum）。
假如您有一個/policespy-names的endpoint，可以用GET來取得所有PolicySpy name的JSON格式，那麼處理這個endpoint的view function很有可能可以借助我們所寫的list_police_spy_names。
list_police_spy_names接收一個為str的code參數，並返回JSON格式：

在with區塊，透過validate_password做驗證。如果通過的話，則返回所有PoliceSpyFile，否則返回空set（即<PoliceSpyFile>{}）。
在with區塊，透過array_agg將police_spy_file.colleagues.name轉為array，並存為names。
最後利用json_object_pack及<json>(names)的casting功能返回JSON格式。

scenes/scene09/schema.esdlfunction list_police_spy_names(code: str) -> json
using (
    with police_spy_file:= PoliceSpyFile if validate_password(code)
                           else <PoliceSpyFile>{},
         names:= array_agg(police_spy_file.colleagues.name),
    select json_object_pack({("names", <json>(names))})
);

要完成validate_password function，還需要搭配使用ext::pgcrypto、 morse_code_of_undercover及get_stored_encrypted_password，我們繼續看下去。
使用ext::pgcrypto
scenes/scene09/schema.esdl...
using extension pg_trgm;

module default {
    ...
}


Auth extension
如果您的app有驗證需求的話，可以試試EdgeDB4.0推出的Auth extension。

建立alias morse_code_of_undercover
劇中永仁臥底檔案的密碼就是臥底的摩斯密碼。
根據網路上的搜尋結果，摩斯密碼大多是使用-，但劇中卻是使用_。讓我們尊重原著，使用內建的str_replace()將臥底的摩斯密碼中的-換成_，並存成alias方便使用。
scenes/scene09/schema.esdlalias morse_code_of_undercover:= str_replace("..- -. -.. . .-. -.-. --- ...- . .-.", "-", "_");


不要將機密資訊存為alias
實務上，不應該將機密資訊存為alias。我們這麼做只是方便稍後展示validate_password及list_police_spy_names的效果。

編寫測試alias的function
新增test_scene09_alias function，並更新test_alias。
scenes/scene09/schema.esdlfunction test_alias() -> bool
using (all({
        test_scene01_alias(),
        test_scene02_alias(),
        test_scene03_alias(),
        test_scene05_alias(),
        test_scene09_alias(),
    })
);

function test_scene09_alias() -> bool
using (all({
        (exists morse_code_of_undercover),
    })
);

編寫get_stored_encrypted_password
get_stored_encrypted_password模擬自資料庫中取出hash過的加密密碼（雖然在這邊它看起來只是每次被呼叫時，計算morse_code_of_undercover的hash值）。
scenes/scene09/schema.esdlfunction get_stored_encrypted_password() -> str
using (
    with code:= morse_code_of_undercover,
            module ext::pgcrypto, 
    select crypt(code, gen_salt())
);

編寫validate_password
最後我們參考官方文件中的範例，使用ext::pgcrypto::crypt()來計算所輸入的密碼（salt為hash過的加密密碼），是否會等於hash過的加密密碼本身。如果是的話，代表我們輸入的是正確密碼，返回true，否則則返回false。
scenes/scene09/schema.esdlfunction validate_password(code: str) -> bool
using (
    with hash:= get_stored_encrypted_password(),
            module ext::pgcrypto,
    select crypt(code, hash) = hash
);

至此，我們滿足了第三個需求。

make end migration here（scenes/scene09/schema.esdl）
did you create extension 'pgcrypto'? [y,n,l,c,b,s,q,?]
> y
did you create alias 'default::morse_code_of_undercover'? [y,n,l,c,b,s,q,?]
> y
did you create function 'default::get_stored_encrypted_password'? [y,n,l,c,b,s,q,?]
> y
did you create function 'default::test_scene09_alias'? [y,n,l,c,b,s,q,?]
> y
did you create function 'default::validate_password'? [y,n,l,c,b,s,q,?]
> y
did you create global 'default::current_user_id'? [y,n,l,c,b,s,q,?]
> y
did you create object type 'default::PoliceSpyFile'? [y,n,l,c,b,s,q,?]
> y
did you create function 'default::list_police_spy_names'? [y,n,l,c,b,s,q,?]
> y
did you alter function 'default::test_alias'? [y,n,l,c,b,s,q,?]
> y
did you alter object type 'default::PoliceSpy'? [y,n,l,c,b,s,q,?]
> y



讓人又愛又恨的Access policy？
由於我們添加了兩個access policy到PoliceSpy，從現在開始每次select PoliceSpy時，都要時刻注意global current_user_id所屬的object是否有足夠權限。

測試
測試test_alias
由於test_alias中的test_scene09_alias含有chen（PoliceSpy）的測試，為了能夠select到chen來進行測試，我們從Police中隨意挑選一個PoliceRank為SP的object，將此object的id指定給global current_user_id（由於目前SP等級的警察只有黃sir一個，所以這個query就是將global current_user_id設為黃sir的id）。測試完成後，再執行reset global current_user_id回復為預設值。
scenes/scene09/query.edgeql# end migration needs to be applied before running this query
set global current_user_id:= (select Police filter .police_rank=PoliceRank.SP limit 1).id;

select test_alias();

reset global current_user_id;

測試validate_password
如果輸入正確的密碼，validate_password會回傳true，否則回傳false。
scenes/scene09/query.edgeqlselect validate_password(morse_code_of_undercover); # {true}
select validate_password("27149"); # {false}

測試PoliceSpy、PoliceSpyFile及list_police_spy_names
如果PoliceRank為PoliceRank.SP
與test_alias一樣，我們將global current_user_id設為黃sir的id。
scenes/scene09/query.edgeqlset global current_user_id:= (select Police filter .police_rank=PoliceRank.SP limit 1).id;

接著進行PoliceSpy各項操作測試：

insert會得到AccessPolicyError。
select可以正常執行。
update及delete會得到空set。

這樣的結果符合需求一的部份要求。
scenes/scene09/query.edgeqlInsert PoliceSpy {name:= "test_police_spy_by_SP"}; # AccessPolicyError

select PoliceSpy;

update PoliceSpy
filter .name="陳永仁"
set {
    nickname:= .nickname ++ "!",
}; # {}

delete PoliceSpy filter .name="陳永仁"; # {}

再來進行PoliceSpyFile各項操作測試及list_police_spy_names功能測試：

PoliceSpyFile的各項功能皆能成功操作。
list_police_spy_names在密碼正確的情況下，回傳含有資訊的JSON資料，否則回傳空的JSON資料。

這樣的結果符合需求二及需求三。
scenes/scene09/query.edgeqlinsert PoliceSpyFile {
    colleagues:= chen,
    classified_info:= "Handler: test_SP...",
};

select PoliceSpyFile; 

select list_police_spy_names(morse_code_of_undercover); # {Json("{\"names\": [\"陳永仁\"]}")}
select list_police_spy_names("abc"); # {Json("{\"names\": []}")}

update PoliceSpyFile filter .classified_info="Handler: test_SP..."
set {
    classified_info:= .classified_info ++ "..."
};

delete PoliceSpyFile;

回復global current_user_id為預設值。
scenes/scene09/query.edgeqlreset global current_user_id;

如果PoliceRank為PoliceRank.DCP
由於資料庫中還沒有PoliceRank為DCP的Police object，所以我們先insert一個，再將其id指定給global current_user_id。
scenes/scene09/query.edgeqlinsert Police {name:= "test_DCP", police_rank:=PoliceRank.DCP};
set global current_user_id:= (select Police filter .police_rank=PoliceRank.DCP limit 1).id;

接著進行PoliceSpy各項操作測試，皆能成功操作。
這樣的結果加上PoliceRank為PoliceRank.SP的測試，符合需求一的全部要求。
scenes/scene09/query.edgeqlInsert PoliceSpy {name:= "test_police_spy_by_DPC"};

select PoliceSpy;

update PoliceSpy filter .name="test_police_spy_by_DPC"
set {
    nickname:= "test_police_spy_by_DPC",
};

delete PoliceSpy filter .nickname="test_police_spy_by_DPC";

再來進行PoliceSpyFile各項操作測試及list_police_spy_names功能測試：

PoliceSpyFile的各項功能皆能成功操作。
list_police_spy_names在密碼正確的情況下，回傳含有資訊的JSON資料，否則回傳空的JSON資料。

這樣的結果符合需求二及需求三。
scenes/scene09/query.edgeqlinsert PoliceSpyFile {
    colleagues:= chen,
    classified_info:= "Handler: test_DCP...",
};

select PoliceSpyFile; 

select list_police_spy_names(morse_code_of_undercover); # {Json("{\"names\": [\"陳永仁\"]}")}
select list_police_spy_names("abc"); # {Json("{\"names\": []}")}

update PoliceSpyFile filter .classified_info="Handler: test_DCP..."
set {
    classified_info:= .classified_info ++ "..."
};

delete PoliceSpyFile;

insert ChenLauContact
scenes/scene09/query.edgeqlinsert ChenLauContact {
    how:= "電話",
    detail:= "黃sir殉職後，建明以黃sir手機聯絡永仁",
    `when`:= assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/23_HH24:MI:SS_ID")),
    where:= police_station union (insert Location {name:= "電車站"}),
};

insert ChenLauContact {
    how:= "面對面",
    detail:= "建明擊斃韓琛後，終於在警局與永仁見面，並確認其臥底身份。",
    `when`:= assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/23_HH24:MI:SS_ID")),
    where:= police_station,
};

insert此場景的Scene
scenes/scene09/query.edgeqlinsert Scene {
      title:= "真相大白", 
      detail:= "建明得知黃sir將與警方臥底於大廈見面，通知韓琛。韓琛一面派" ++
               "手下到大廈，一面進行毒品交易。黃sir為掩護永仁離開，被韓琛" ++
               "手下丟下樓，寧願殉職而不發一言。黃sir死後，建明聯手永仁於" ++
               "停車場擊斃韓琛，最終兩人於警察局見面。當建明正幫永仁處理臥" ++
               "底檔案時，永仁發現其親手所寫帶有「標」字的信封竟然在建明桌上，" ++
               "醒悟原來建明就是韓琛派至警隊的臥底，立即悄然離開。",
      who:= (select Gangster filter .nickname in {"迪路", "傻強"}) union {wong, chen, hon, lau},
      `when`:= assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/23_HH24:MI:SS_ID")),
      where:=  (select Location filter .name in {"天台", "電車站"}) union 
               police_station union
               (select(insert Location {name:="停車場"})),         
};
# --8

最後清理
刪除PoliceRank為DCP的測試用Police object，並回復global current_user_id為預設值。
scenes/scene09/query.edgeqldelete Police filter .name="test_DCP";

reset global current_user_id;

Query review

Query review
scenes/scene09/query.edgeqlset global current_user_id:= (select Police filter .police_rank=PoliceRank.SP limit 1).id;

select test_alias();

reset global current_user_id;

select validate_password(morse_code_of_undercover); # {true}
select validate_password("27149"); # {false}

set global current_user_id:= (select Police filter .police_rank=PoliceRank.SP limit 1).id;

Insert PoliceSpy {name:= "test_police_spy_by_SP"}; # AccessPolicyError

select PoliceSpy;

update PoliceSpy
filter .name="陳永仁"
set {
    nickname:= .nickname ++ "!",
}; # {}

delete PoliceSpy filter .name="陳永仁"; # {}

insert PoliceSpyFile {
    colleagues:= chen,
    classified_info:= "Handler: test_SP...",
};

select PoliceSpyFile; 

select list_police_spy_names(morse_code_of_undercover); # {Json("{\"names\": [\"陳永仁\"]}")}
select list_police_spy_names("abc"); # {Json("{\"names\": []}")}

update PoliceSpyFile filter .classified_info="Handler: test_SP..."
set {
    classified_info:= .classified_info ++ "..."
};

delete PoliceSpyFile;

reset global current_user_id;

insert Police {name:= "test_DCP", police_rank:=PoliceRank.DCP};
set global current_user_id:= (select Police filter .police_rank=PoliceRank.DCP limit 1).id;

Insert PoliceSpy {name:= "test_police_spy_by_DPC"};

select PoliceSpy;

update PoliceSpy filter .name="test_police_spy_by_DPC"
set {
    nickname:= "test_police_spy_by_DPC",
};

delete PoliceSpy filter .nickname="test_police_spy_by_DPC";

insert PoliceSpyFile {
    colleagues:= chen,
    classified_info:= "Handler: test_DCP...",
};

select PoliceSpyFile; 

select list_police_spy_names(morse_code_of_undercover); # {Json("{\"names\": [\"陳永仁\"]}")}
select list_police_spy_names("abc"); # {Json("{\"names\": []}")}

update PoliceSpyFile filter .classified_info="Handler: test_DCP..."
set {
    classified_info:= .classified_info ++ "..."
};

delete PoliceSpyFile;


insert ChenLauContact {
    how:= "電話",
    detail:= "黃sir殉職後，建明以黃sir手機聯絡永仁",
    `when`:= assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/23_HH24:MI:SS_ID")),
    where:= police_station union (insert Location {name:= "電車站"}),
};


insert ChenLauContact {
    how:= "面對面",
    detail:= "建明擊斃韓琛後，終於在警局與永仁見面，並確認其臥底身份。",
    `when`:= assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/23_HH24:MI:SS_ID")),
    where:= police_station,
};

insert Scene {
      title:= "真相大白", 
      detail:= "建明得知黃sir將與警方臥底於大廈見面，通知韓琛。韓琛一面派" ++
               "手下到大廈，一面進行毒品交易。黃sir為掩護永仁離開，被韓琛" ++
               "手下丟下樓，寧願殉職而不發一言。黃sir死後，建明聯手永仁於" ++
               "停車場擊斃韓琛，最終兩人於警察局見面。當建明正幫永仁處理臥" ++
               "底檔案時，永仁發現其親手所寫帶有「標」字的信封竟然在建明桌上，" ++
               "醒悟原來建明就是韓琛派至警隊的臥底，立即悄然離開。",
      who:= (select Gangster filter .nickname in {"迪路", "傻強"}) union {wong, chen, hon, lau},
      `when`:= assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/23_HH24:MI:SS_ID")),
      where:=  (select Location filter .name in {"天台", "電車站"}) union 
               police_station union
               (select(insert Location {name:="停車場"})),         
};

delete Police filter .name="test_DCP";

reset global current_user_id;


無間假設
我們假設劇中只有一個天台Location object。
無間吹水
建明要刪掉永仁臥底檔案時，鏡頭內所帶到的資訊及其臥底時間，多處都與三部曲劇情不相吻合。

09 - 真相大白

Full schema preview

劇情提要

警隊資安升級計畫

EdgeQL query

建立 `global` `current_user_id`

更新`PoliceSpy`

建立`PoliceSpyFile`

編寫`list_police_spy_names`

使用`ext::pgcrypto`

建立`alias` `morse_code_of_undercover`

編寫測試`alias`的`function`

編寫`get_stored_encrypted_password`

編寫`validate_password`

測試

測試`test_alias`

測試`validate_password`

測試`PoliceSpy`、`PoliceSpyFile`及`list_police_spy_names`

如果`PoliceRank`為`PoliceRank.SP`

如果`PoliceRank`為`PoliceRank.DCP`

`insert` `ChenLauContact`

`insert`此場景的`Scene`

最後清理

Query review

無間假設

無間吹水

09 - 真相大白

Full schema preview

劇情提要

警隊資安升級計畫

EdgeQL query

建立 global current_user_id

更新PoliceSpy

建立PoliceSpyFile

編寫list_police_spy_names

使用ext::pgcrypto

建立alias morse_code_of_undercover

編寫測試alias的function

編寫get_stored_encrypted_password

編寫validate_password

測試

測試test_alias

測試validate_password

測試PoliceSpy、PoliceSpyFile及list_police_spy_names

如果PoliceRank為PoliceRank.SP

如果PoliceRank為PoliceRank.DCP

insert ChenLauContact

insert此場景的Scene

最後清理

Query review

無間假設

無間吹水

建立 `global` `current_user_id`

更新`PoliceSpy`

建立`PoliceSpyFile`

編寫`list_police_spy_names`

使用`ext::pgcrypto`

建立`alias` `morse_code_of_undercover`

編寫測試`alias`的`function`

編寫`get_stored_encrypted_password`

編寫`validate_password`

測試`test_alias`

測試`validate_password`

測試`PoliceSpy`、`PoliceSpyFile`及`list_police_spy_names`

如果`PoliceRank`為`PoliceRank.SP`

如果`PoliceRank`為`PoliceRank.DCP`

`insert` `ChenLauContact`

`insert`此場景的`Scene`