05 - 三年之後又三年
Full schema preview
Full schema preview
scenes/scene04/schema.esdl
module default {
# scalar types
scalar type PoliceRank extending enum<Protected, Cadet, PC, SPC, SGT, SSGT, PI, IP, SIP, CIP, SP, SSP, CSP, ACP, SACP, DCP, CP>;
scalar type GangsterRank extending enum<Nobody, Leader, Boss>;
scalar type DayOfWeek extending enum<Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday>;
scalar type FuzzyYear extending int64;
scalar type FuzzyMonth extending int64 {constraint expression on (__subject__ >=1 and __subject__ <=12)}
scalar type FuzzyDay extending int64 {constraint expression on (__subject__ >=1 and __subject__ <=31)}
scalar type FuzzyHour extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=23)}
scalar type FuzzyMinute extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=59)}
scalar type FuzzySecond extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=59)}
scalar type SceneNumber extending sequence;
# abstract object types
abstract type Person {
required name: str;
nickname: str;
eng_name: str;
}
abstract type IsPolice {
police_rank: PoliceRank{
default:= PoliceRank.Cadet;
};
dept: str;
is_officer:= .police_rank >= PoliceRank.PI;
}
abstract type IsGangster {
gangster_rank: GangsterRank {
default:= GangsterRank.Nobody;
};
gangster_boss: GangsterBoss;
}
abstract type IsSpy extending IsPolice, IsGangster;
abstract type Place {
required name: str {
delegated constraint exclusive;
};
}
abstract type Event {
detail: str;
multi who: Character;
multi `when`: FuzzyTime;
multi where: Place;
}
abstract type Archive;
# object types
type Character extending Person {
classic_lines: array<str>;
lover: Character;
multi actors: Actor;
}
type Actor extending Person;
type Police extending Character, IsPolice;
type Gangster extending Character, IsGangster;
type GangsterBoss extending Gangster {
overloaded gangster_rank: GangsterRank {
default:= GangsterRank.Boss;
constraint expression on (__subject__ = GangsterRank.Boss);
};
# excluding self
constraint expression on (__subject__ != .gangster_boss) {
errmessage := "The boss can't be his/her own boss.";
}
}
type PoliceSpy extending Character, IsSpy;
type GangsterSpy extending Character, IsSpy;
type Landmark extending Place;
type Location extending Place;
type Store extending Place;
type FuzzyTime {
fuzzy_year: FuzzyYear;
fuzzy_month: FuzzyMonth;
fuzzy_day: FuzzyDay;
fuzzy_hour: FuzzyHour;
fuzzy_minute: FuzzyMinute;
fuzzy_second: FuzzySecond;
fuzzy_dow: DayOfWeek;
fuzzy_fmt:= (
with Y:= <str>.fuzzy_year ?? "YYYY",
m:= <str>.fuzzy_month ?? "MM",
m:= m if len(m) > 1 else "0" ++ m,
d:= <str>.fuzzy_day ?? "DD",
d:= d if len(d) > 1 else "0" ++ d,
H:= <str>.fuzzy_hour ?? "HH24",
H:= H if len(H) > 1 else "0" ++ H,
M:= <str>.fuzzy_minute ?? "MI",
M:= M if len(M) > 1 else "0" ++ M,
S:= <str>.fuzzy_second ?? "SS",
S:= S if len(S) > 1 else "0" ++ S,
dow:= <str>.fuzzy_dow ?? "ID",
select Y ++ "/" ++ m ++ "/" ++ d ++ "_" ++
H ++ ":" ++ M ++ ":" ++ S ++ "_" ++
dow
);
trigger fuzzy_month_day_check after insert, update for each
when (exists __new__.fuzzy_month and exists __new__.fuzzy_day)
do (
assert_exists(
cal::to_local_date(__new__.fuzzy_year ?? 2002, __new__.fuzzy_month, __new__.fuzzy_day),
)
);
constraint exclusive on (.fuzzy_fmt);
}
type CriminalRecord extending Archive {
required ref_no: str {
constraint exclusive;
};
required code: str;
multi involved: Character;
created_at: datetime {
readonly := true;
rewrite insert using (datetime_of_statement())
}
modified_at: datetime {
rewrite update using (datetime_of_statement())
}
}
type ChenLauContact extending Event {
how: str;
overloaded who: Character {default:= {chen, lau}}
}
type Scene extending Event {
title: str;
remarks: str;
references: array<tuple<str, str>>;
required scene_number: SceneNumber {
constraint exclusive;
default := sequence_next(introspect SceneNumber);
}
index on (.scene_number);
}
# alias
alias hon:= assert_exists(assert_single((select GangsterBoss filter .name = "韓琛")));
alias lau:= assert_exists(assert_single((select GangsterSpy filter .name = "劉建明")));
alias chen:= assert_exists(assert_single((select PoliceSpy filter .name = "陳永仁")));
alias wong:= assert_exists(assert_single((select Police filter .name = "黃志誠")));
alias police_station:= assert_exists(assert_single((select Landmark filter .name="警察局")));
alias year_1992:= assert_exists(assert_single((select FuzzyTime
filter .fuzzy_year = 1992
and .fuzzy_month ?= <FuzzyMonth>{}
and .fuzzy_day ?= <FuzzyDay>{}
and .fuzzy_hour ?= <FuzzyHour>{}
and .fuzzy_minute ?= <FuzzyMinute>{}
and .fuzzy_second ?= <FuzzySecond>{}
and .fuzzy_dow ?= <DayOfWeek>{}
))
);
alias year_1994:= assert_exists(assert_single((select FuzzyTime
filter .fuzzy_year = 1994
and .fuzzy_month ?= <FuzzyMonth>{}
and .fuzzy_day ?= <FuzzyDay>{}
and .fuzzy_hour ?= <FuzzyHour>{}
and .fuzzy_minute ?= <FuzzyMinute>{}
and .fuzzy_second ?= <FuzzySecond>{}
and .fuzzy_dow ?= <DayOfWeek>{}
))
);
# functions
function is_hi_fi_store_open(dow: DayOfWeek, visit_hour: int64) -> bool
#
# The store will open 11:00~22:00 everyday, except:
# will close on Wednesdays.
# will close during 13:00~14:00 and 19:00~20:00 everyday.
#
using (
with open_hours:= multirange([range(11, 13), range(14, 19), range(20, 22)])
select dow != DayOfWeek.Wednesday and contains(open_hours, visit_hour)
);
# tests
function test_alias() -> bool
using (all({
test_scene01_alias(),
test_scene02_alias(),
test_scene03_alias(),
})
);
function test_scene01_alias() -> bool
using (all({
(exists hon),
(exists lau),
(exists year_1992),
})
);
function test_scene02_alias() -> bool
using (all({
(exists chen),
(exists wong),
})
);
function test_scene03_alias() -> bool
using (all({
(exists year_1994),
(exists police_station),
})
);
function test_hi_fi_store_open() -> bool
using (all({
is_hi_fi_store_open(DayOfWeek.Monday, 12),
is_hi_fi_store_open(DayOfWeek.Friday, 15),
is_hi_fi_store_open(DayOfWeek.Saturday, 21),
})
);
function test_hi_fi_store_close() -> bool
using (not all({
is_hi_fi_store_open(DayOfWeek.Wednesday, 12),
is_hi_fi_store_open(DayOfWeek.Thursday, 13),
is_hi_fi_store_open(DayOfWeek.Sunday, 19),
})
);
}
scenes/scene05/schema.esdl
module default {
# scalar types
scalar type PoliceRank extending enum<Protected, Cadet, PC, SPC, SGT, SSGT, PI, IP, SIP, CIP, SP, SSP, CSP, ACP, SACP, DCP, CP>;
scalar type GangsterRank extending enum<Nobody, Leader, Boss>;
scalar type DayOfWeek extending enum<Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday>;
scalar type FuzzyYear extending int64;
scalar type FuzzyMonth extending int64 {constraint expression on (__subject__ >=1 and __subject__ <=12)}
scalar type FuzzyDay extending int64 {constraint expression on (__subject__ >=1 and __subject__ <=31)}
scalar type FuzzyHour extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=23)}
scalar type FuzzyMinute extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=59)}
scalar type FuzzySecond extending int64 {constraint expression on (__subject__ >=0 and __subject__ <=59)}
scalar type SceneNumber extending sequence;
# abstract object types
abstract type Person {
required name: str;
nickname: str;
eng_name: str;
}
abstract type IsPolice {
police_rank: PoliceRank{
default:= PoliceRank.Cadet;
};
dept: str;
is_officer:= .police_rank >= PoliceRank.PI;
}
abstract type IsGangster {
gangster_rank: GangsterRank {
default:= GangsterRank.Nobody;
};
gangster_boss: GangsterBoss;
}
abstract type IsSpy extending IsPolice, IsGangster;
abstract type Place {
required name: str {
delegated constraint exclusive;
};
}
abstract type Event {
detail: str;
multi who: Character;
multi `when`: FuzzyTime;
multi where: Place;
}
abstract type Archive;
# object types
type Character extending Person {
classic_lines: array<str>;
lover: Character;
multi actors: Actor;
}
type Actor extending Person;
type Police extending Character, IsPolice;
type Gangster extending Character, IsGangster;
type GangsterBoss extending Gangster {
overloaded gangster_rank: GangsterRank {
default:= GangsterRank.Boss;
constraint expression on (__subject__ = GangsterRank.Boss);
};
# excluding self
constraint expression on (__subject__ != .gangster_boss) {
errmessage := "The boss can't be his/her own boss.";
}
}
type PoliceSpy extending Character, IsSpy;
type GangsterSpy extending Character, IsSpy;
type Landmark extending Place;
type Location extending Place;
type Store extending Place;
type FuzzyTime {
fuzzy_year: FuzzyYear;
fuzzy_month: FuzzyMonth;
fuzzy_day: FuzzyDay;
fuzzy_hour: FuzzyHour;
fuzzy_minute: FuzzyMinute;
fuzzy_second: FuzzySecond;
fuzzy_dow: DayOfWeek;
fuzzy_fmt:= (
with Y:= <str>.fuzzy_year ?? "YYYY",
m:= <str>.fuzzy_month ?? "MM",
m:= m if len(m) > 1 else "0" ++ m,
d:= <str>.fuzzy_day ?? "DD",
d:= d if len(d) > 1 else "0" ++ d,
H:= <str>.fuzzy_hour ?? "HH24",
H:= H if len(H) > 1 else "0" ++ H,
M:= <str>.fuzzy_minute ?? "MI",
M:= M if len(M) > 1 else "0" ++ M,
S:= <str>.fuzzy_second ?? "SS",
S:= S if len(S) > 1 else "0" ++ S,
dow:= <str>.fuzzy_dow ?? "ID",
select Y ++ "/" ++ m ++ "/" ++ d ++ "_" ++
H ++ ":" ++ M ++ ":" ++ S ++ "_" ++
dow
);
trigger fuzzy_month_day_check after insert, update for each
when (exists __new__.fuzzy_month and exists __new__.fuzzy_day)
do (
assert_exists(
cal::to_local_date(__new__.fuzzy_year ?? 2002, __new__.fuzzy_month, __new__.fuzzy_day),
)
);
constraint exclusive on (.fuzzy_fmt);
}
type CriminalRecord extending Archive {
required ref_no: str {
constraint exclusive;
};
required code: str;
multi involved: Character;
created_at: datetime {
readonly := true;
rewrite insert using (datetime_of_statement())
}
modified_at: datetime {
rewrite update using (datetime_of_statement())
}
}
type ChenLauContact extending Event {
how: str;
overloaded who: Character {default:= {chen, lau}}
}
type Scene extending Event {
title: str;
remarks: str;
references: array<tuple<str, str>>;
required scene_number: SceneNumber {
constraint exclusive;
default := sequence_next(introspect SceneNumber);
}
index on (.scene_number);
}
# alias
alias hon:= assert_exists(assert_single((select GangsterBoss filter .name = "韓琛")));
alias lau:= assert_exists(assert_single((select GangsterSpy filter .name = "劉建明")));
alias chen:= assert_exists(assert_single((select PoliceSpy filter .name = "陳永仁")));
alias wong:= assert_exists(assert_single((select Police filter .name = "黃志誠")));
alias police_station:= assert_exists(assert_single((select Landmark filter .name="警察局")));
alias year_1992:= assert_exists(assert_single((select FuzzyTime
filter .fuzzy_year = 1992
and .fuzzy_month ?= <FuzzyMonth>{}
and .fuzzy_day ?= <FuzzyDay>{}
and .fuzzy_hour ?= <FuzzyHour>{}
and .fuzzy_minute ?= <FuzzyMinute>{}
and .fuzzy_second ?= <FuzzySecond>{}
and .fuzzy_dow ?= <DayOfWeek>{}
))
);
alias year_1994:= assert_exists(assert_single((select FuzzyTime
filter .fuzzy_year = 1994
and .fuzzy_month ?= <FuzzyMonth>{}
and .fuzzy_day ?= <FuzzyDay>{}
and .fuzzy_hour ?= <FuzzyHour>{}
and .fuzzy_minute ?= <FuzzyMinute>{}
and .fuzzy_second ?= <FuzzySecond>{}
and .fuzzy_dow ?= <DayOfWeek>{}
))
);
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),
})
);
}
劇情提要
永仁與黃sir相約於天台交換情報，韓琛將於這星期進行毒品交易，地點未知。黃sir則說他費盡心力將永仁傷人的案子由坐牢改成看心理醫生，交待永仁要照做。永仁則抱怨自己被黃sir騙了，說好只當三年臥底，結果現在都快十年了，不知道何時才能恢復警察身份。十年間發生了太多事，永仁看著黃sir送的手錶，他有時候真的不知道該用什麼心態面對黃sir（詳情請見無間道Ⅱ及無間道Ⅲ）。
EdgeQL query
insert此場景時間2002年
建立alias及編寫測試alias的function
定義一個year_2002（2002年）的alias。
scenes/scene05/schema.esdl
新增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>{}
))
);
test_scene05_alias function，並更新test_alias。
scenes/scene05/schema.esdl
function test_alias() -> bool
using (all({
test_scene01_alias(),
test_scene02_alias(),
test_scene03_alias(),
test_scene05_alias(),
})
);
function test_scene05_alias() -> bool
using (all({
(exists year_1994),
})
);
make end migration here（scenes/scene05/schema.esdl）
測試test_alias
scenes/scene05/query.edgeql
# end migration needs to be applied before running this query
select test_alias();
update chen
這裡永仁連說了兩句經典台詞，讓我們把它們都加在classic_lines property中（留意這邊使用的語法是classic_lines := .classic_lines ++ array<str>）。
scenes/scene05/query.edgeql
update chen
set {
classic_lines := .classic_lines ++
["你話三年。三年之後又三年，三年之後又三年！十年都嚟緊頭啦老細！",
"收嗲啦！呢句嘢我聽咗九千幾次啦！"],
};
datetime的模糊加減法
假如我們想幫永仁算一下他所說的「三年之後又三年，三年之後又三年！十年都嚟緊頭啦」，大概是多久的話，可以使用cal::relative_duration()。
我們假設永仁從1992年12月1日0時0分0秒，正式開始臥底工作。
首先我們需要將這個時間轉換為datetime型態。您可以選擇使用<datetime>來casting或是使用to_datetime()來轉換。
Casting vs function
初學的朋友可能會搞混這兩個方法。此時可以查看datetime文件，通常沒有()的像是datetime或是cal::local_datetime，這代表是一種型態，可以於其後加上適當的str來casting。而像是有to開頭且有()的
to_datetime()或是cal::to_local_datetime()，則代表function，需要參考其所提供的各種簽名來使用。EdgeDB可以針對同一個function名定義多次，接收不同的參數，像是to_datetime()就提供六種可以呼叫的簽名，這種特性稱為function overloaded。
scenes/scene05/query.edgeql
select <datetime>"1992-12-01T00:00:00+08";
select to_datetime("1992-12-01T00:00:00+08");
select to_datetime(1992, 12, 1, 0, 0, 0, "Asia/hong_kong");
select to_datetime(<cal::local_datetime>"1992-12-01T00:00:00", "Asia/hong_kong");
接下來利用cal::relative_duration來casting一個接近十年時間的str，假設為9年10個月。沒錯，cal::relative_duration可以接受像9 years 10 months這麼人性化的輸入!
datetime加上9年10個月的relative_duration：
scenes/scene05/query.edgeql
最後將結果的select <datetime>"1992-12-01T00:00:00+08" + <cal::relative_duration>"9 years 10 months";
datetime型態轉變為local_datetime型態：
scenes/scene05/query.edgeql
現在我們終於知道永仁與黃sir於本場景見面的時間，大概為2002年10月，這個計算大致符合劇中的時間線。
with t:=(select <datetime>"1992-12-01T00:00:00+08" + <cal::relative_duration>"9 years 10 months")
select cal::to_local_datetime(t, "Asia/hong_kong");
local_datetime的模糊加減法
datetime的計算看起來比較複雜，因為牽扯到timezone。如果您想要計算的是local_datetime的話，那麼可以輕鬆不少。
scenes/scene05/query.edgeql
select <cal::local_datetime>"1992-12-01T00:00:00" + <cal::relative_duration>"9 years 10 months";
update wong
將黃sir的經典台詞指定給classic_lines property（留意這邊使用的是:=）。
25仔
25仔在粵語中即為反骨仔或臥底之意。黃sir此舉乃是在嘲諷永仁。
local_date的模糊加減法
假設黃sir想幫永仁算一下，離永仁25號生日還有幾天，可以使用cal::local_date這麼算：
永仁生日
永仁於劇末的墓碑出生日期為1966年10月25日。
insert此場景的Scene
scenes/scene05/query.edgeql
insert Scene {
title:= "三年之後又三年",
detail:= "永仁與黃sir相約於天台交換情報，韓琛將於這星期進行毒品" ++
"交易，地點未知。黃sir則說他費盡心力將永仁傷人的案子由" ++
"坐牢改成看心理醫生，交待永仁要照做。永仁抱怨自己被黃sir" ++
"騙了，說好只當三年臥底，結果現在都快十年了，不知道何時才" ++
"能恢復警察身份。十年間發生了太多事，永仁看著黃sir送的手錶" ++
"，他有時候真的不知道該用什麼心態面對黃sir（詳情請見無間道Ⅱ" ++
"及無間道Ⅲ）。",
who:= {wong, chen},
`when`:= year_2002,
where:= (insert Location {name:="天台"}),
};
Query review
Query review
scenes/scene05/query.edgeql
insert FuzzyTime {fuzzy_year:= 2002};
select test_alias();
update chen
set {
classic_lines := .classic_lines ++
["你話三年。三年之後又三年，三年之後又三年！十年都嚟緊頭啦老細！",
"收嗲啦！呢句嘢我聽咗九千幾次啦！"],
};
update wong
set {
classic_lines := ["你25號生日嘛!25仔!"],
};
select <datetime>"1992-12-01T00:00:00+08";
select to_datetime("1992-12-01T00:00:00+08");
select to_datetime(1992, 12, 1, 0, 0, 0, "Asia/hong_kong");
select to_datetime(<cal::local_datetime>"1992-12-01T00:00:00", "Asia/hong_kong");
select <cal::relative_duration>"9 years 10 months";
select <datetime>"1992-12-01T00:00:00+08" + <cal::relative_duration>"9 years 10 months";
with t:=(select <datetime>"1992-12-01T00:00:00+08" + <cal::relative_duration>"9 years 10 months")
select cal::to_local_datetime(t, "Asia/hong_kong");
select <cal::local_datetime>"1992-12-01T00:00:00" + <cal::relative_duration>"9 years 10 months";
select <cal::local_date>"2002-10-25" - <cal::local_date>"2002-10-01";
insert Scene {
title:= "三年之後又三年",
detail:= "永仁與黃sir相約於天台交換情報，韓琛將於這星期進行毒品" ++
"交易，地點未知。黃sir則說他費盡心力將永仁傷人的案子由" ++
"坐牢改成看心理醫生，交待永仁要照做。永仁抱怨自己被黃sir" ++
"騙了，說好只當三年臥底，結果現在都快十年了，不知道何時才" ++
"能恢復警察身份。十年間發生了太多事，永仁看著黃sir送的手錶" ++
"，他有時候真的不知道該用什麼心態面對黃sir（詳情請見無間道Ⅱ" ++
"及無間道Ⅲ）。",
who:= {wong, chen},
`when`:= year_2002,
where:= (insert Location {name:="天台"}),
};
無間吹水
有一種說法是黃sir特別喜歡送人手錶。除了於天台送了永仁手錶外，無間道Ⅱ中Mary姐的手錶也是黃sir所送。所以當建明詢問Mary姐其所戴手錶是否為韓琛所送，她並沒有正面回應。