10 - 我想做個好人
Full schema preview
Full schema preview
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),
})
);
}
scenes/scene10/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>;
multi lovers: 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),
})
);
}
劇情提要
建明與永仁於天台相見，不料國平也趕到。永仁事先已報警，想持槍壓著建明到樓下交予警方。不料，於進電梯時被國平擊斃，原來他也是韓琛安裝於警隊的臥底。國平向建明表明身份，希望之後一起合作。但最終建明選擇於電梯中殺死國平，並營造永仁與國平雙雙死於槍戰的假象。事後，心兒於葉校長遺物中發現永仁臥底檔案，恢復其警察身份，並由建明代表行禮。
重要提醒
global current_user_id
需要特別注意global current_user_id所屬object的PoliceRank是否合乎access policy!
EdgeQL query
設定global current_user_id
由於本場景需要多次操作PoliceSpy object，我們可以insert一個PoliceRank為DCP的Police object，並將其id指定給global current_user_id。
scenes/scene10/query.edgeql
insert Police {name:= "test_DCP", police_rank:=PoliceRank.DCP};
set global current_user_id:= (select Police filter .police_rank=PoliceRank.DCP limit 1).id;
update chen
將永仁的經典台詞加入到classic_lines property中。
update lau
將建明的經典台詞加入到classic_lines property中。
insert ChenLauContact
這是本劇中，兩人最後一次聯絡了。
scenes/scene10/query.edgeql
insert ChenLauContact {
how:= "面對面",
detail:= "建明與永仁相約於天台上談判",
`when`:= (insert FuzzyTime {
fuzzy_year:=2002,
fuzzy_month:=11,
fuzzy_day:=27,
fuzzy_hour:=15,
fuzzy_minute:=0,
fuzzy_second:=0,
}),
where:= (select Location filter .name="天台"),
};
insert真．林國平
真沒想到，國平竟然也是韓琛的臥底，第一次看到這段時，真是驚訝不已！
可是這麼一來，國平就不應該是Police而是PoliceSpy囉？我們應該刪掉國平 Police object，並新增一個國平 PoliceSpy object嗎？
這樣的話，之前國平 Police object的相關記錄都會被刪除（例如：CIBTeamTreat），這樣合理嗎？又或者我們應該重新去確認所有跟國平 Police object有關的object將其替換為國平 PoliceSpy object？
該怎麼做其實沒有標準的答案，不過一個比較常見的方法是使用soft delete。使用一個類似is_active的property來表達該object的存取狀態，而不真正將其從資料庫中刪除。畢竟在最後一幕之前，我們的確不知道國平是臥底，國平 Police object是一個合適的表達。
最後我們insert國平的PoliceSpy object如下：
scenes/scene10/query.edgeql
with b:= assert_single((select Police filter .name="林國平"))
insert PoliceSpy {
name:= b.name,
nickname:= b.nickname,
police_rank:= b.police_rank,
gangster_boss:= hon,
dept:= b.dept,
actors:= b.actors
};
感情線
在緊湊的臥底對決中，其實導演與編劇也穿插了一些感情戲份，讓我們一起來看看吧。
Mary & 建明
我們insertMary，並指定其為建明的lover。
scenes/scene10/query.edgeql
insert Character {
name:= "Mary",
eng_name:= "Mary",
lover:= lau,
actors:= (insert Actor{
name:= "鄭秀文",
eng_name:= "Sammi",
}),
};
update lau
set {
lover:= assert_single((select Character filter .name="Mary")),
};
心兒 & 永仁
我們insert李心兒，並指定其為永仁的lover。
scenes/scene10/query.edgeql
insert Character {
name:= "李心兒",
lover:= chen,
actors:= (insert Actor{
name:= "陳慧琳",
eng_name:= "Kelly",
}),
};
update chen
set {
lover:= assert_single((select Character filter .name="李心兒")),
};
May & 永仁
現在我們面臨了一個有趣的情形，永仁看起來有兩個lover，但是我們的初始schema只設計了一個single link的lover。我們現在需要將這個single link的lover轉變為multi link的lovers。這其中其實包含了兩步的變更，第一步是將lover重新命名為lovers，第二步是將lovers由single link改為multi link。
您可以選擇做兩次migration，但其實EdgeDB相當聰明，大部份時間能夠猜中我們的意圖，讓我們試試用一步的migration來完成這個變化吧。我們變更Character如下：
scenes/scene10/schema.esdl
接著於命令列執行type Character extending Person {
classic_lines: array<str>;
multi lovers: Character;
multi actors: Actor;
}
edgedb migration create。
make end migration here（scenes/scene10/schema.esdl）
did you drop link 'lover' of object type 'default::Character'? [y,n,l,c,b,s,q,?]
> n
did you rename link 'lover' of object type 'default::Character' to 'lovers'? [y,n,l,c,b,s,q,?]
> y
did you convert link 'lovers' of object type 'default::Character' to 'multi' cardinality? [y,n,l,c,b,s,q,?]
> y
n，於是EdgeDB試著詢問我們。如果不是要drop的話，是否是要rename。如果是要rename的話，是否由single link改為multi link。如此一來，我們原來於lover中所指向的object，在於命令列執行edgedb migrate後也會一併帶到lovers。
如果第一個選項我們選擇了y，EdgeDB會認為我們想先drop掉lover，然候加上一個multi link的lovers。如此一來lovers將會是空set，我們需要在於命令列執行完edgedb migrate後，手動將原來lover所指向的object加進來。
由這個例子可以知道，migration時不一定只能選擇y，應該視當下需求來決定。
最後我們insertMay，並將May加入到chen的lovers。
scenes/scene10/query.edgeql
我們可以確認# end migration needs to be applied before running this query
insert Character{
name:= "May",
eng_name:= "May",
lover:= chen,
actors:= (insert Actor{
name:= "蕭亞軒",
eng_name:= "Elva",
}),
};
update chen
set {
lovers+= assert_single((select Character filter .name="May")),
};
chen的lovers內確實有心兒及May。
斷捨離與detached
假設永仁覺得自己有太多lovers，想利用update幫他斷捨離，但卻發現有時候lovers會被設為空set，他百思不得其解，讓我們一起來看看永仁遇到的情況。永仁一共嘗試了下列四種query，只有第一種會將lovers設為空set，其它三種都可以成功將lovers設定為心兒一人：
原來問題出在query1中，我們在update Character的set（關鍵字）內再次使用了select Character。這個Character將會是外面update Character filter .name="陳永仁"語法中的set，而不是Character這個object type。當想要在各種top-level EdgeQL statements（select, insert, update及delete）內再次引用同一個object type時，需要使用detached，例如：
scenes/scene10/query.edgeql
如果要想避免使用update Character filter .name="陳永仁"
set {lovers:= (select detached Character filter .name="李心兒")};
detached的話，可以：
- 如query2，使用
alias，如chen。 - 如query3，於
with區塊內，暫時命名一個變數，如ch。 - 如query4，於
update時改使用其它object type，如PoliceSpy。
insert此場景的Scene
scenes/scene10/query.edgeql
insert Scene {
title:= "我想做個好人",
detail:= "建明與永仁於天台相見，不料國平也趕到。永仁事先已報警，想持槍壓著建明" ++
"到樓下交予警方。不料，於進電梯時被國平擊斃，原來他也是韓琛安裝於警" ++
"隊的臥底。國平向建明表明身份，希望之後一起合作。但最終建明選擇於電梯" ++
"中殺死國平，並營造永仁與國平雙雙死於槍戰的假象。事後，心兒於葉校長遺" ++
"物中發現永仁臥底檔案，恢復其警察身份，並由建明代表行禮。",
who:= (select Police filter .name="林國平") union
(select PoliceSpy filter .name="林國平") union
{chen, lau},
`when`:= assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/27_15:00:00_ID")),
where:= (select Location filter .name="天台"),
};
uuid選取object的技巧
假設我們想選擇一開始建立的PoliceRank為DCP的Police object，該怎麼寫query呢？
最簡單的方法應該是filter .name="test_DCP"了吧，像是：
object str型態的id的話，又該怎麼選取呢？您可能會寫出以下query：
scenes/scene10/query.edgeql
但是除了這種經典的寫法外，EdgeDB還提供了以下寫法：
with pid:= <str>(select Police filter .name="test_DCP").id,
select Police filter .id=<uuid>pid;
scenes/scene10/query.edgeql
with pid:= <str>(select Police filter .name="test_DCP").id,
select <Police><uuid>pid;
pid此時為str型態的uuid，我們可以在前面使用<Police><uuid>來casting而取得object。
Shape construction
當想要使用上述寫法並搭配shape construction時，需加上()，例如：
另外，如果您已經有一個id set，也可以進行類似的操作，例如：
scenes/scene10/query.edgeql
with pid:= (select Police filter .name="test_DCP").id,
select <Police>pid;
最後清理
讓我們用上述技巧來刪除一開始建立的PoliceRank為DCP的Police object，並reset global current_user_id。
scenes/scene10/query.edgeql
with pid:= <str>(select Police filter .name="test_DCP").id,
delete <Police><uuid>pid;
reset global current_user_id;
Query review
Query review
scenes/scene10/query.edgeql
insert Police {name:= "test_DCP", police_rank:=PoliceRank.DCP};
set global current_user_id:= (select Police filter .police_rank=PoliceRank.DCP limit 1).id;
update chen
set {
classic_lines := .classic_lines ++ ["對唔住，我係差人。"],
};
update lau
set {
classic_lines := ["我以前無得揀，我而家想做好人。"],
};
with b:= assert_single((select Police filter .name="林國平"))
insert PoliceSpy {
name:= b.name,
nickname:= b.nickname,
police_rank:= b.police_rank,
gangster_boss:= hon,
dept:= b.dept,
actors:= b.actors
};
insert ChenLauContact {
how:= "面對面",
detail:= "建明與永仁相約於天台上談判",
`when`:= (insert FuzzyTime {
fuzzy_year:=2002,
fuzzy_month:=11,
fuzzy_day:=27,
fuzzy_hour:=15,
fuzzy_minute:=0,
fuzzy_second:=0,
}),
where:= (select Location filter .name="天台"),
};
insert Character {
name:= "Mary",
eng_name:= "Mary",
lover:= lau,
actors:= (insert Actor{
name:= "鄭秀文",
eng_name:= "Sammi",
}),
};
update lau
set {
lover:= assert_single((select Character filter .name="Mary")),
};
insert Character {
name:= "李心兒",
lover:= chen,
actors:= (insert Actor{
name:= "陳慧琳",
eng_name:= "Kelly",
}),
};
update chen
set {
lover:= assert_single((select Character filter .name="李心兒")),
};
insert Character{
name:= "May",
eng_name:= "May",
lover:= chen,
actors:= (insert Actor{
name:= "蕭亞軒",
eng_name:= "Elva",
}),
};
update chen
set {
lovers+= assert_single((select Character filter .name="May")),
};
select chen.lovers.name;
update Character filter .name="陳永仁"
set {lovers:= (select Character filter .name="李心兒")};
update chen
set {lovers:= (select Character filter .name="李心兒")};
with ch:= (select Character filter .name="陳永仁")
update ch
set {lovers:= (select Character filter .name="李心兒")};
update PoliceSpy filter .name="陳永仁"
set {lovers:= (select Character filter .name="李心兒")};
update Character filter .name="陳永仁"
set {lovers:= (select detached Character filter .name="李心兒")};
insert Scene {
title:= "我想做個好人",
detail:= "建明與永仁於天台相見，不料國平也趕到。永仁事先已報警，想持槍壓著建明" ++
"到樓下交予警方。不料，於進電梯時被國平擊斃，原來他也是韓琛安裝於警" ++
"隊的臥底。國平向建明表明身份，希望之後一起合作。但最終建明選擇於電梯" ++
"中殺死國平，並營造永仁與國平雙雙死於槍戰的假象。事後，心兒於葉校長遺" ++
"物中發現永仁臥底檔案，恢復其警察身份，並由建明代表行禮。",
who:= (select Police filter .name="林國平") union
(select PoliceSpy filter .name="林國平") union
{chen, lau},
`when`:= assert_single((select FuzzyTime filter .fuzzy_fmt="2002/11/27_15:00:00_ID")),
where:= (select Location filter .name="天台"),
};
select Police filter .name="test_DCP";
with pid:= <str>(select Police filter .name="test_DCP").id,
select Police filter .id=<uuid>pid;
with pid:= <str>(select Police filter .name="test_DCP").id,
select <Police><uuid>pid;
with pid:= <str>(select Police filter .name="test_DCP").id,
select (<Police><uuid>pid) {*};
with pid:= (select Police filter .name="test_DCP").id,
select <Police>pid;
with pid:= <str>(select Police filter .name="test_DCP").id,
delete <Police><uuid>pid;
reset global current_user_id;
無間吹水
根據訪談，於拍攝時間只有華仔與編劇導演等少部份人知道，國平也是韓琛所派臥底，甚至連飾演國平的林家棟都是到最後一幕快開拍前才知道。當時他擔心前面的戲份是不是有演得不合劇情的地方，華仔說沒問題，他要的就是這種反差感。