07 - 互猜底牌
Full schema preview
Full schema preview
scenes/scene06/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),
})
);
}
scenes/scene07/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;
# 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 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/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),
})
);
}
劇情提要
韓琛千鈞一髮之際收到建明簡訊，緊急打給迪路與傻強，將與泰國佬交易的可卡因丟進海裡。黃sir見行動失敗，只得暫時將韓琛及其手下帶回警察局。黃sir確認證據不足以起訴韓琛後，帶隊來到韓琛用餐的房間。黃sir藉機嘲諷韓琛，雖然這次無法逮捕他，但已令他損受幾千萬。韓琛聽後，瞬間翻臉將桌上食物往黃sir座位掃去。兩人言談間針鋒相對，互相猜測著對方安排在己方的臥底是誰。最後，韓琛囂張地帶著手下們大步離去。
EdgeQL query
建立Beverage
全劇至此已是第三次喝飲料了，不禁讓我們想要建立一個Beverage來記錄所有人喝過的飲料。
Beverage有一個property和四個link：
nameproperty為必填的飲料名。produced_bylink為生產廠家。consumed_bylink為被誰所喝。whenlink為何時被喝。wherelink為在哪裡被喝。
scenes/scene07/schema_1st_migration.esdl
type Beverage {
required name: str;
produced_by: Store;
consumed_by: Character;
`when`: FuzzyTime;
where: Place;
}
make 1st migration here（scenes/scene07/schema_1st_migration.esdl）
insert店家龍鼓灘
在開始進行Beverage相關操作前，我們先insert一個龍鼓灘Store object，代表一家名為龍鼓灘茶餐廳。
如果您還記得的話，上一個場景我們也有名為一個龍鼓灘的Landmark object。由於Store及Landmark都是extending自Place，讓我們回顧一下Place的schema：
scenes/scene07/schema.esdl
由於abstract type Place {
required name: str {
delegated constraint exclusive;
};
}
Place的name property使用了delegated constraint exclusive，所以Store及Landmark各自都可以建立自己的龍鼓灘。
但是如果想再insert一個龍鼓灘Store object的話，則會報錯如下：
報錯訊息
insert Beverage
有喝飲料的角色太多了，讓我們insert三種飲料代表就好。
第一種是熱奶茶，為建明於下午茶時間喝的，為龍鼓灘茶餐廳出品。
三點三
粵語中形容分鐘時，習慣將六十分鐘分作十二份，每個數字對應五分鐘，所以下午三點三即代表15:15。
scenes/scene07/query.edgeql
insert Beverage {
name:= "熱奶茶",
produced_by:= assert_single((select Store filter .name="龍鼓灘")),
consumed_by:= lau,
`when`:= (insert FuzzyTime {fuzzy_hour:=15, fuzzy_minute:=15}), #三點三
where:= police_station,
};
第二種是保特瓶裝綠茶，為建明在緝毒過程中請國平拿給他的。我們假設此時為20:15。
綠茶
由於這似乎是華仔代言的牌子，所以我們就不給定produced_by，來幫忙打廣告了。有趣的是，劇中還有一幕，建明打電話請同事跟蹤黃sir時，他又喝了一次同牌子的綠茶。此外，在無間道Ⅲ也會多次看到這個牌子，像是開頭建明與國平聊天時，永仁與黃sir見面的便利商店背景或是警察局的飲料販賣機等。
scenes/scene07/query.edgeql
insert Beverage {
name:= "綠茶",
consumed_by:= lau,
`when`:= (insert FuzzyTime {fuzzy_hour:=20, fuzzy_minute:=15}),
where:= assert_single((select Location filter .name="大廈三樓")),
};
第三種是韓琛在警局被拘留時所喝的飲料，我們假設為龍鼓灘茶餐廳出品的凍檸茶並假設此時為23:15。
scenes/scene07/query.edgeql
insert Beverage {
name:= "凍檸茶",
produced_by:= assert_single((select Store filter .name="龍鼓灘")),
consumed_by:= hon,
`when`:= (insert FuzzyTime {fuzzy_hour:=23, fuzzy_minute:=15}),
where:= police_station,
};
再次熟悉backlinks
假如我們想知道建明喝了哪些飲料，可以這麼做：
但是如果除了所喝的飲料，您又同時想顯示出建明的其它資訊，這時候可以使用backlinks來做：
scenes/scene07/query.edgeql
select lau {name, nickname, beverages:= .<consumed_by[is Beverage] {name}};
{
default::GangsterSpy {
name: '劉建明',
nickname: '劉仔',
beverages: {default::Beverage {name: '熱奶茶'}, default::Beverage {name: '綠茶'}},
},
}
Deep fetching for backlinks
別忘了在backlinks中也可以使用shape，像是這邊的{name}，來選取想要的property或link。
也就是說您可以寫出像是下面這段有趣的query：
scenes/scene07/query.edgeql
select lau {name, nickname, beverages:= .<consumed_by[is Beverage] {name, where : {name}}};
{
default::GangsterSpy {
name: '劉建明',
nickname: '劉仔',
beverages: {
default::Beverage {name: '熱奶茶', where: default::Landmark {name: '警察局'}},
default::Beverage {name: '綠茶', where: default::Location {name: '大廈三樓'}},
},
},
}
- 建明的
nameproperty。 - 建明的
nicknameproperty。 - 建明所喝的飲料及喝飲料的地點。
何時使用backlinks？
當您想取得被aaa所bbb的ccc時，例如被建明（aaa）所喝掉（bbb）的飲料（ccc）時。此時對aaa使用select，ccc通常可以以backlinks的語法一起出現在select的{}內。
建立TeamTreatNumber及CIBTeamTreat
假如CIB部門有一個傳統，當全組人需要一起留下加班處理特殊案件時，部門內除長官外的每一個同事都可以在抽獎箱裡抽出一顆球（抽完後球會放回箱內）。箱內總共有1~10十顆球，上面貼有號碼，只要有人抽到的號碼是9或10的話，就會由部門長官買單請吃一頓下午茶。由於每次操作這個活動都需要花費不少時間，所以有組員提議請我們使用EdgeDB來簡化流程。身為無間道和EdgeDB的雙重愛好者，我們當然是義不容辭啦!
首先建立TeamTreatNumber，其是由extending sequence而來，作為每次活動的計數器。
接著建立CIBTeamTreat如下：
scenes/scene07/schema.esdl
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
}
CIBTeamTreat有兩個property和一個link，我們逐個來看。
team_treat_number
team_treat_number為一property，其使用TeamTreatNumber為記數器，和Scene的scene_number一樣都是自動產生不重覆的編號。
colleagues
colleagues是一個Police的multi link，其預設選擇CIB部門的所有警察，這符合我們的需求，因為長官不須參加抽獎（記得建明是PoliceSpy嗎？）。接著我們加上readonly為true的限制，防止大家抽完獎之後耍賴偷改。
最後我們新增一個int64的point，這是一個link property，顧名思義其為一個colleagues link的property。這個link property並不能由Police來存取（雖然它現在正在Police的{}中），它只能夠在我們針對CIBTeamTreatquery時存取。我們使用內建的math::ceil()及random()來給予CIB部門所有警察一個1~10的預設值，模擬抽獎過程（每個警察都會抽一次，且不會全部都是同一個數字）。
team_treat
team_treat為一computed property（留意這邊使用的是:=），其會返回一個bool值。存取link property需要使用特殊的@符號，所以.colleagues@point相當於動態取得該CIBTeamTreat內colleagues multi link內的所有point。我們使用內建的max()來看看這些point內的最大值是否會大於或等於9。
make end migration here（scenes/scene07/schema.esdl）
insert CIBTeamTreat
讓我們來試試這個抽獎系統吧。下面的query每次都會insert一個CIBTeamTreat，並返回其team_treat之值。如果是true的話，代表建明需要請客。
為什麼不寫成function呢？
您可能會想將抽獎系統寫為function，像是：
edgedb migration create會報錯如下：
原來function內是不能對資料庫進行變動的，包括insert、update及delete。
當然，有時候因為太久沒長官請客，大家會懷疑系統是不是出錯了，那麼可以由下面這個query來列出所有人抽到的數字。
scenes/scene07/query.edgeql
最後，同事們間可能互相調侃誰最帶賽，此時可以用下面的query列出每個人的名字及其抽到的數字。
select(insert CIBTeamTreat) {team_treat_number, team_treat, points:= .colleagues@point};
scenes/scene07/query.edgeql
select(insert CIBTeamTreat) {team_treat_number, team_treat, colleagues: {name, @point}};
{
default::CIBTeamTreat {
team_treat_number: 3,
team_treat: true,
colleagues: {
default::Police {name: '林國平', @point: 1},
default::Police {name: '大象', @point: 10},
default::Police {name: '孖八', @point: 2},
default::Police {name: 'police_11', @point: 7},
default::Police {name: 'police_12', @point: 1},
default::Police {name: 'police_13', @point: 7},
},
},
}
police_12的同事都只抽到1，不過還好大象抽到10，最後大家還是有免費下午茶吃。
現在開始，每次的抽獎記錄都會是一個CIBTeamTreat object。這些記錄累積起來可以有許多應用，例如每年年末，大家可以找出誰是最常幫大家贏得下午茶的人（最常拿9或10分），一起請他吃頓飯。link property是不是一個很酷的功能呀!
link property有一些語法如果不常使用，的確容易忘記。為此EdgeDB貼心準備了cheatsheet供大家參考。
update hon
話說這個場景好像充滿著飲料，讓我們回來繼續關心琛哥，update他離開警察局前對黃sir說的經典台詞到classic_lines。
scenes/scene07/query.edgeql
update hon
set {
classic_lines := .classic_lines ++ ["你見過有人去殯儀館和屍體握手嗎?"],
};
insert ChenLauContact
scenes/scene07/query.edgeql
insert ChenLauContact {
how:= "面對面",
detail:= "毒品被韓琛手下迪路與傻強銷毀，永仁隨韓琛一起被帶回警察局",
`when`:= year_2002,
where:= police_station,
};
insert此場景的Scene
scenes/scene07/query.edgeql
insert Scene {
title:= "互猜底牌",
detail:= "韓琛千鈞一髮之際收到建明簡訊，緊急打給迪路與傻強，將與" ++
"泰國佬交易的可卡因丟進海裡。黃sir見行動失敗，只得暫時" ++
"將韓琛及其手下帶回警察局。回警察局後，黃sir確認證據不" ++
"足以起訴韓琛後，帶隊來到韓琛用餐的房間。黃sir藉機嘲諷" ++
"韓琛，雖然這次無法逮捕他，但以令他損受幾千萬。韓琛聽" ++
"後，瞬間翻臉將桌上食物往黃sir座位掃去。兩人言談間針鋒" ++
"相對，互相猜測著對方安排在己方的臥底是誰。最後，韓琛囂" ++
"張地帶著手下們大步離去。",
who:= (select Gangster filter .nickname in {"迪路", "傻強"}) union {wong, chen, hon, lau},
`when`:= year_2002,
where:= police_station,
remarks:= "1.假設建明喝綠茶時間為20:15。\n2.假設韓琛於23:15喝凍檸茶。"
};
Query review
Query review
scenes/scene07/query.edgeql
insert Store {name:="龍鼓灘"};
insert Beverage {
name:= "熱奶茶",
produced_by:= assert_single((select Store filter .name="龍鼓灘")),
consumed_by:= lau,
`when`:= (insert FuzzyTime {fuzzy_hour:=15, fuzzy_minute:=15}), #三點三
where:= police_station,
};
insert Beverage {
name:= "綠茶",
consumed_by:= lau,
`when`:= (insert FuzzyTime {fuzzy_hour:=20, fuzzy_minute:=15}),
where:= assert_single((select Location filter .name="大廈三樓")),
};
insert Beverage {
name:= "凍檸茶",
produced_by:= assert_single((select Store filter .name="龍鼓灘")),
consumed_by:= hon,
`when`:= (insert FuzzyTime {fuzzy_hour:=23, fuzzy_minute:=15}),
where:= police_station,
};
select Beverage {name} filter .consumed_by=lau;
select lau {name, nickname, beverages:= .<consumed_by[is Beverage] {name}};
select lau {name, nickname, beverages:= .<consumed_by[is Beverage] {name, where : {name}}};
select(insert CIBTeamTreat).team_treat;
select(insert CIBTeamTreat) {team_treat_number, team_treat, points:= .colleagues@point};
select(insert CIBTeamTreat) {team_treat_number, team_treat, colleagues: {name, @point}};
update hon
set {
classic_lines := .classic_lines ++ ["你見過有人去殯儀館和屍體握手嗎?"],
};
insert ChenLauContact {
how:= "面對面",
detail:= "毒品被韓琛手下迪路與傻強銷毀，永仁隨韓琛一起被帶回警察局",
`when`:= year_2002,
where:= police_station,
};
insert Scene {
title:= "互猜底牌",
detail:= "韓琛千鈞一髮之際收到建明簡訊，緊急打給迪路與傻強，將與" ++
"泰國佬交易的可卡因丟進海裡。黃sir見行動失敗，只得暫時" ++
"將韓琛及其手下帶回警察局。回警察局後，黃sir確認證據不" ++
"足以起訴韓琛後，帶隊來到韓琛用餐的房間。黃sir藉機嘲諷" ++
"韓琛，雖然這次無法逮捕他，但以令他損受幾千萬。韓琛聽" ++
"後，瞬間翻臉將桌上食物往黃sir座位掃去。兩人言談間針鋒" ++
"相對，互相猜測著對方安排在己方的臥底是誰。最後，韓琛囂" ++
"張地帶著手下們大步離去。",
who:= (select Gangster filter .nickname in {"迪路", "傻強"}) union {wong, chen, hon, lau},
`when`:= year_2002,
where:= police_station,
remarks:= "1.假設建明喝綠茶時間為20:15。\n2.假設韓琛於23:15喝凍檸茶。"
};
無間吹水
根據訪談，曾志偉橫掃桌上飯菜的經典戲碼，黃秋生事先並不知情。但他根據自己的經驗判斷，曾志偉必定會這樣做，目的是使自己大吃一驚。於是他在演戲時，已經做好往後退的準備，因為不想穿著湯湯水水的衣服繼續演戲。