06 - 有內鬼終止交易
Full schema preview
本場景無須migration
Full schema preview
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),
})
);
}
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),
})
);
}
劇情提要
O記聯合CIB準備於今晚韓琛與泰國佬交易可卡因（古柯鹼）時，來個人贓並獲。建明知道後，假裝打電話給家人，通知韓琛。韓琛一直監聽警方頻道，並指示迪路和傻強四處亂晃，不要前往交易地點。過程中，永仁一直以摩斯密碼與黃sir聯絡。黃sir在得知韓琛監聽頻道後，隨即轉換頻道，並使用舊頻道發出今晚行動取消的指令。韓琛信以為真，指示迪路和傻強可以前往龍鼓灘交易。正當交易完成，黃sir準備先逮捕迪路和傻強將毒品扣下，再衝進屋逮捕韓琛之際，建仁使用簡訊傳送「有內鬼，終止交易」到韓琛所在位置附近的所有手機。
EdgeQL query
insert地點大廈三樓
update wong
黃sir為有組織罪案及三合會調查科（O記）的警司（SP），update其相關property。
scenes/scene06/query.edgeql
update wong
set {
police_rank:= PoliceRank.SP,
dept:= "有組織罪案及三合會調查科(O記)",
};
insert數名O記警察
insert黃sir麾下警察。
scenes/scene06/query.edgeql
for i in range_unpack(range(1, 11))
union (
insert Police {
name:= "police_" ++ <str>i,
dept:= "有組織罪案及三合會調查科(O記)",
police_rank:= PoliceRank.SPC,
}
);
update lau
建明為刑事情報科（CIB）的高級督察（SIP），update其相關property。
insert數名CIB警察
insert建明麾下警察。
scenes/scene06/query.edgeql
insert Police{
name:= "林國平",
nickname:="大B",
police_rank:= PoliceRank.SSGT,
dept:= "刑事情報科(CIB)",
actors:= (
insert Actor{
name:= "林家棟",
eng_name:= "Gordon",
}
)
};
for name in {"大象", "孖八"}
union (
insert Police {
name:= name,
nickname:= name,
dept:= "刑事情報科(CIB)",
police_rank:= PoliceRank.SGT,
}
);
for i in range_unpack(range(11, 14))
union (
insert Police {
name:= "police_" ++ <str>i,
dept:= "刑事情報科(CIB)",
police_rank:= PoliceRank.SPC,
}
);
insert數名韓琛小弟
insert數名韓琛小弟，包括兩個小leader迪路與傻強。
scenes/scene06/query.edgeql
insert Gangster {
name:= "迪比亞路",
nickname:= "迪路",
gangster_boss:= hon,
gangster_rank:= GangsterRank.Leader,
actors:= (insert Actor {
name:= "林迪安",
eng_name:="Dion",
}),
};
insert Gangster {
name:= "徐偉強",
nickname:= "傻強",
gangster_boss:= hon,
gangster_rank:= GangsterRank.Leader,
actors:= (insert Actor {
name:= "杜汶澤",
eng_name:= "Edward",
}),
};
for i in range_unpack(range(1, 11))
union (
insert Gangster {
name:= "gangster_" ++ <str>i,
gangster_boss:= hon,
gangster_rank:= GangsterRank.Nobody,
}
);
insert數個提及的地標
scenes/scene06/query.edgeql
for loc in {"葵涌碼頭", "三號幹線", "龍鼓灘"}
union (
insert Landmark{
name:= loc,
}
);
insert ChenLauContact
scenes/scene06/query.edgeql
insert ChenLauContact {
how:= "面對面",
detail:= "黃sir帶隊進入韓琛毒品交易現場",
`when`:= year_2002,
where:= assert_single((select Location filter .name="大廈三樓")),
};
學習使用group - 情境1
讓我們假想一下自己是編劇。這個場景的人物相比前面多了不少，我們必須確認從警察的視角來看，各個階級的人力配置合不合理，此時group會是一個不錯的工具。group以後的操作，大多需要用到它的key、grouping及elements。
首先我們做以下嘗試：
- 於
with區塊選取Police、PoliceSpy及GangsterSpy（建明及永仁也都是警察）命名為p。 - 於
with區塊group p並依照police_rank來分類，結果命名為g。 - 最後使用
{**}顯示g的細節。
scenes/scene06/query.edgeql
with p:= Police union PoliceSpy union GangsterSpy,
g:= (group p by .police_rank),
select g {**};
Deep fetching
在熟悉上面這段query的時候，或許您會很想只顯示key裡面的police_rank及elements裡面的name，卻發現不知道怎麼達成。您可能會做以下嘗試：
#❌
with p:= Police union PoliceSpy union GangsterSpy,
g:= (group p by .police_rank),
select g {key {police_rank},
elements {name}};
:：
#✅
with p:= Police union PoliceSpy union GangsterSpy,
g:= (group p by .police_rank),
select g {key: {police_rank},
elements: {name}};
{
{key: {police_rank: Protected}, elements: {default::PoliceSpy {name: '陳永仁'}}},
{
key: {police_rank: SPC},
elements: {
default::Police {name: 'police_1'},
default::Police {name: 'police_2'},
default::Police {name: 'police_3'},
default::Police {name: 'police_4'},
default::Police {name: 'police_5'},
default::Police {name: 'police_6'},
default::Police {name: 'police_7'},
default::Police {name: 'police_8'},
default::Police {name: 'police_9'},
default::Police {name: 'police_10'},
default::Police {name: 'police_11'},
default::Police {name: 'police_12'},
default::Police {name: 'police_13'},
},
},
{key: {police_rank: SGT}, elements: {default::Police {name: '大象'}, default::Police {name: '孖八'}}},
{key: {police_rank: SSGT}, elements: {default::Police {name: '林國平'}}},
{key: {police_rank: SIP}, elements: {default::GangsterSpy {name: '劉建明'}}},
{key: {police_rank: SP}, elements: {default::Police {name: '黃志誠'}}},
}
但這麼一來，結果會非常長，我們舉黃sir為例，因為SP級別只有他一人。
...
{
id: 10d5423b-15cf-4c4d-89ca-e39b4bfa9902,
grouping: {'police_rank'},
key: {id: 46542b84-47fb-44b8-bdfd-2bc34e924eee, police_rank: SP},
elements: {
default::Police {
is_officer: true,
classic_lines: ['你25號生日嘛!25仔!'],
eng_name: {},
name: '黃志誠',
nickname: '黃sir',
dept: '有組織罪案及三合會調查科(O記)',
police_rank: SP,
id: db630896-bc48-11ee-aae4-df71814b08b4,
},
},
},
...
{**}，但是我們的目的僅是想知道各個階級的人數，所以可以做下列修改：
- 利用
group的key可以得到police_rank（因為這個property就是我們在by時使用的），並在g{ }中命名為police_rank。 - 利用
group的elements得到該分類中每一個object（即Police或PoliceSpy或GangsterSpy），接著使用count來計算其數量，並在g{ }中命名為counts。 - 最後，由於
police_rank是一個enum，所以可以使用order by對其進行排序，並加上desc，這麼一來就會改成官階較大的排在前面。
scenes/scene06/query.edgeql
with p:= Police union PoliceSpy union GangsterSpy,
g:= (group p by .police_rank),
select g {police_rank:= .key.police_rank,
counts:= count(.elements)}
order by .police_rank desc;
{
{police_rank: SP, counts: 1},
{police_rank: SIP, counts: 1},
{police_rank: SSGT, counts: 1},
{police_rank: SGT, counts: 2},
{police_rank: SPC, counts: 13},
{police_rank: Protected, counts: 1},
}
- 高階長官
SP及SIP各一位 - 比較有工作經驗的
SSGT一位及SGT兩位。 - 主要執勤探員
SPC十三位。 - 臥底探員一位。
編劇此時可以依據這個結果，來請教相關專業人士這樣的人力配置是否合理。
學習使用group - 情境2
假設片場工作人員在休息時間聊到，不知道劇中出現的地名：
- 最長的有多長呢？
- 哪一個長度又是出現最多次的呢？
此時又是可以好好運用group的機會囉。因為這次不像上面一樣，有內建的police_rank可以使用，所以需要使用using來組合出想要如何分類的操作。
針對第一個問題：
- 首先對
nameproperty使用len，並取名為name_length，這樣就可以將其放在by之後來分類。 - 利用
group的key得到剛剛定義的name_length，並在g{ }中命名為name_length。 - 利用
group的elements得到該分類中每一個Place，接著使用count來計算其數量，並在g{ }中命名為counts。 - 利用
group的elements得到nameproperty，並在g{ }中命名為names。 - 最後使用
order by對name_length進行排序，並加上desc，這麼一來長度較長的地名就會顯示在前面了。
scenes/scene06/query.edgeql
with g:= (group Place
using name_length:= len(.name)
by name_length),
select g {name_length:= .key.name_length ,
counts:= count(.elements),
names:= .elements.name}
order by .name_length desc;
{
{name_length: 6, counts: 1, names: {'Hi-Fi鋪'}},
{name_length: 4, counts: 3, names: {'大廈三樓', '葵涌碼頭', '三號幹線'}},
{name_length: 3, counts: 2, names: {'警察局', '龍鼓灘'}},
{name_length: 2, counts: 3, names: {'佛堂', '天台', '警校'}},
}
針對第二個問題，只需將order by的目標改為counts即可。
scenes/scene06/query.edgeql
with g:= (group Place
using name_length:= len(.name)
by name_length),
select g {name_length:= .key.name_length ,
counts:= count(.elements),
names:= .elements.name}
order by .counts desc;
{
{name_length: 4, counts: 3, names: {'大廈三樓', '葵涌碼頭', '三號幹線'}},
{name_length: 2, counts: 3, names: {'佛堂', '天台', '警校'}},
{name_length: 3, counts: 2, names: {'警察局', '龍鼓灘'}},
{name_length: 6, counts: 1, names: {'Hi-Fi鋪'}},
}
結果發現地名長度為4及2的組別都出現三次。
insert此場景的Scene
這裡選擇人物時，其實也可以像前面一樣使用Police及Gangster。但這麼一來，就是假設要選取全部的Police及Gangster，如果之後我們修改了前面幾個場景的query，在現在這種人物比較多的場景會難以偵錯（您可以假想Scene被極度簡化，只包含重要演員，但配角及台前幕後許多工作人員都未計入）。
所以我們這邊示範使用with搭配filter，限縮選取範圍。
scenes/scene06/query.edgeql
with policemen:= (select Police filter .dept in {"有組織罪案及三合會調查科(O記)", "刑事情報科(CIB)"}),
gangsters:= (select Gangster filter .gangster_boss=hon)
insert Scene {
title:= "有內鬼終止交易",
detail:= "O記聯合CIB準備於今晚韓琛與泰國佬交易可卡因（古柯鹼）時，來個" ++
"人贓並獲。建明知道後，假裝打電話給家人，通知韓琛。韓琛一直監" ++
"聽警方頻道，並指示迪路和傻強四處亂晃，不要前往交易地點。過程中，" ++
"永仁一直以摩斯密碼與黃sir聯絡。黃sir在得知韓琛監聽頻道後，隨即" ++
"轉換頻道，並使用舊頻道發出今晚行動取消的指令。韓琛信以為真，指示" ++
"迪路和傻強可以前往龍鼓灘交易。正當交易完成，黃sir準備先逮捕迪路" ++
"和傻強將毒品扣下，再衝進屋逮捕韓琛之際，建仁使用簡訊傳送「有內鬼，" ++
"終止交易」到韓琛所在位置附近的所有手機。",
who:= policemen union gangsters union {chen, lau, hon},
`when`:= year_2002,
where:= (select Place filter .name="大廈三樓" or .name="龍鼓灘"),
remarks:= "1.假設國平官階為`SSGT`，大象與孖八官階為`SGT`。"
};
Query review
Query review
scenes/scene06/query.edgeql
insert Location {name:="大廈三樓"};
update wong
set {
police_rank:= PoliceRank.SP,
dept:= "有組織罪案及三合會調查科(O記)",
};
for i in range_unpack(range(1, 11))
union (
insert Police {
name:= "police_" ++ <str>i,
dept:= "有組織罪案及三合會調查科(O記)",
police_rank:= PoliceRank.SPC,
}
);
update lau
set {
police_rank:= PoliceRank.SIP,
dept:= "刑事情報科(CIB)",
};
insert Police{
name:= "林國平",
nickname:="大B",
police_rank:= PoliceRank.SSGT,
dept:= "刑事情報科(CIB)",
actors:= (
insert Actor{
name:= "林家棟",
eng_name:= "Gordon",
}
)
};
for name in {"大象", "孖八"}
union (
insert Police {
name:= name,
nickname:= name,
dept:= "刑事情報科(CIB)",
police_rank:= PoliceRank.SGT,
}
);
for i in range_unpack(range(11, 14))
union (
insert Police {
name:= "police_" ++ <str>i,
dept:= "刑事情報科(CIB)",
police_rank:= PoliceRank.SPC,
}
);
insert Gangster {
name:= "迪比亞路",
nickname:= "迪路",
gangster_boss:= hon,
gangster_rank:= GangsterRank.Leader,
actors:= (insert Actor {
name:= "林迪安",
eng_name:="Dion",
}),
};
insert Gangster {
name:= "徐偉強",
nickname:= "傻強",
gangster_boss:= hon,
gangster_rank:= GangsterRank.Leader,
actors:= (insert Actor {
name:= "杜汶澤",
eng_name:= "Edward",
}),
};
for i in range_unpack(range(1, 11))
union (
insert Gangster {
name:= "gangster_" ++ <str>i,
gangster_boss:= hon,
gangster_rank:= GangsterRank.Nobody,
}
);
for loc in {"葵涌碼頭", "三號幹線", "龍鼓灘"}
union (
insert Landmark{
name:= loc,
}
);
insert ChenLauContact {
how:= "面對面",
detail:= "黃sir帶隊進入韓琛毒品交易現場",
`when`:= year_2002,
where:= assert_single((select Location filter .name="大廈三樓")),
};
with p:= Police union PoliceSpy union GangsterSpy,
g:= (group p by .police_rank),
select g {**};
with p:= Police union PoliceSpy union GangsterSpy,
g:= (group p by .police_rank),
select g {police_rank:= .key.police_rank,
counts:= count(.elements)}
order by .police_rank desc;
with g:= (group Place
using name_length:= len(.name)
by name_length),
select g {name_length:= .key.name_length ,
counts:= count(.elements),
names:= .elements.name}
order by .name_length desc;
with g:= (group Place
using name_length:= len(.name)
by name_length),
select g {name_length:= .key.name_length ,
counts:= count(.elements),
names:= .elements.name}
order by .counts desc;
with policemen:= (select Police filter .dept in {"有組織罪案及三合會調查科(O記)", "刑事情報科(CIB)"}),
gangsters:= (select Gangster filter .gangster_boss=hon)
insert Scene {
title:= "有內鬼終止交易",
detail:= "O記聯合CIB準備於今晚韓琛與泰國佬交易可卡因（古柯鹼）時，來個" ++
"人贓並獲。建明知道後，假裝打電話給家人，通知韓琛。韓琛一直監" ++
"聽警方頻道，並指示迪路和傻強四處亂晃，不要前往交易地點。過程中，" ++
"永仁一直以摩斯密碼與黃sir聯絡。黃sir在得知韓琛監聽頻道後，隨即" ++
"轉換頻道，並使用舊頻道發出今晚行動取消的指令。韓琛信以為真，指示" ++
"迪路和傻強可以前往龍鼓灘交易。正當交易完成，黃sir準備先逮捕迪路" ++
"和傻強將毒品扣下，再衝進屋逮捕韓琛之際，建仁使用簡訊傳送「有內鬼，" ++
"終止交易」到韓琛所在位置附近的所有手機。",
who:= policemen union gangsters union {chen, lau, hon},
`when`:= year_2002,
where:= (select Place filter .name="大廈三樓" or .name="龍鼓灘"),
remarks:= "1.假設國平官階為`SSGT`，大象與孖八官階為`SGT`。"
};
無間假設
- 建明與黃sir的職級稍後才會提及，我們提前於此與部門一起
update。 - 建明的小組成員其實於前一幕假扮律師時已經出現，我們省略該場景，改於此處
insert。 - 假設大象與孖八的兩人的本名也是大象與孖八。兩人應該是隸屬於O記黃sir手下，但因為CIB建明手下於劇中大多沒有出現人名，所以假設兩人為CIB部門。
- 三號幹線是劇中稍後才會出現的地名，提前移至此處
insert。
無間吹水
- 建明傳送的簡訊原文是「有內鬼，終止交易」，但最終於路人手機上顯示的是「有內鬼終止交易」，少了一個逗號。
- 本片開頭韓琛提到以前在屯門做代客泊車的工作，可以得知其為屯門當地人或是與該地淵緣深厚，所以毒品交易地點選擇附近的龍鼓灘也甚為合理。
- 本場景有O記及CIB部門，卻沒有毒品調查科（MB）參與，有點令人費解。