01 - 韓琛初現
Full schema preview
Full schema preview
scenes/scene01/initial_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;
}
# 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 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);
}
}
scenes/scene01/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;
}
# 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 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 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>{}
))
);
# tests
function test_alias() -> bool
using (all({
test_scene01_alias(),
})
);
function test_scene01_alias() -> bool
using (all({
(exists hon),
(exists lau),
(exists year_1992),
})
);
}
劇情提要
韓琛準備派遣多個身家較為清白的小弟臥底至香港警隊，包括建明。他向小弟們講述著自己的過去，並說自己不相信算命先生所說的「一將功成萬骨枯」。他認為出來混的，未來的路怎麼走應該由自己決定。
EdgeQL query
make start migration here（scenes/scene01/initial_schema.esdl）
在開始進行所有query前，我們需要先告知EdgeDB初始的schema。
- 如果您是在命令列，需要輸入
edgedb migration create後，再輸入edgedb migrate。 - 如果您是在
EdgeDB REPL，可以使用更便捷的\migration create及\migrate指令。
insert此場景時間1992年
insert韓琛及其演員曾志偉
韓琛於開頭就說出「一將功成萬骨枯」的經典句，我們將此句收錄在classic_lines property中。
此外，雖然actors為multi link，可以包括多個演員。但是我們可以使用assert_single()來確保最多只會接收到一個曾志偉Actor object。這麼一來，如果資料庫內已經有兩個Actor object的name都叫曾志偉時，這個query就會報錯。
scenes/scene01/query.edgeql
insert Actor {
name:= "曾志偉",
eng_name:= "Eric",
nickname:= "獎老",
};
insert GangsterBoss {
name:= "韓琛",
nickname:= "琛哥",
classic_lines:= ["一將功成萬骨枯"],
actors := assert_single((select Actor filter .name = "曾志偉")),
};
insert劉建明及其少年時期演員陳冠希
語法與前面類似，留意filter時也可用in {}的寫法。
scenes/scene01/query.edgeql
insert Actor {
name:= "陳冠希",
eng_name:= "Edison",
};
insert GangsterSpy {
name:= "劉建明",
nickname:= "劉仔",
gangster_boss:= assert_single((select GangsterBoss filter .name = "韓琛")),
dept:= "警校學生",
actors := assert_single((select Actor filter .name in {"陳冠希"})),
};
建立alias
由於每次都要使用(select ... filter ... .xxx=ooo)的語法來選擇object頗為麻煩，針對常用到的object，可以直接在schema中定義alias，方便取用。我們這邊定義了一個hon（韓琛）、lau（劉建明）及year_1992（1992年）的alias。
scenes/scene01/schema.esdl
於alias hon:= assert_exists(assert_single((select GangsterBoss filter .name = "韓琛")));
alias lau:= assert_exists(assert_single((select GangsterSpy 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>{}
))
);
year_1992中，我們用到了?=operator。?=除了像=可以比較兩個set外，還可以比較空set。當兩個set都為空時，會返回true。當有些property可以為空set時，?=是個非常好用的工具。
空set需要型別
空set前別忘了加上型別來casting。
alias year_1992的另一種寫法
也可以使用fuzzy_fmt這個computed property來做為filter的條件。
編寫測試alias的function
我們在alias前都加上了assert_exists()及assert_single()，這樣可以確保每個alias只會返回剛好一個object。我自己會習慣寫一個名為test_alias的function來做測試：
scenes/scene01/schema.esdl
function test_alias() -> bool
using (all({
test_scene01_alias(),
})
);
function test_scene01_alias() -> bool
using (all({
(exists hon),
(exists lau),
(exists year_1992),
})
);
function的語法
習慣寫Python的朋友，常常會在定義function時，在()後加上:。
test_alias中會包含多個場景的sub-test（如test_scene01_alias），當每一個場景的sub-test都返回true時，all會返回true，否則會報錯。而我們利用exists檢查各場景中的alias是否存在，如果全部都存在的話，all會返回true，否則會報錯。
報錯訊息
這麼一來當我們在操作資料庫時，可以隨時透過test_alias來確認每一個alias，是否都如預期地返回了剛好一個object。
make end migration here（scenes/scene01/schema.esdl）
did you create alias 'default::hon'? [y,n,l,c,b,s,q,?]
> y
did you create alias 'default::lau'? [y,n,l,c,b,s,q,?]
> y
did you create alias 'default::year_1992'? [y,n,l,c,b,s,q,?]
> y
did you create function 'default::test_scene01_alias'? [y,n,l,c,b,s,q,?]
> y
did you create function 'default::test_alias'? [y,n,l,c,b,s,q,?]
> y
測試test_alias
scenes/scene01/query.edgeql
# end migration needs to be applied before running this query
select test_alias();
insert此場景的Scene
因為剛剛已經透過test_alias測試了所有alias，所以這裡我們可以放心使用。值得注意的是，我們在insert這個scene時，除了同時insert 佛堂這個Location object外，也同時將其指定為where multi link的值。這樣的模式在EdgeQL稱為nested inserts，是相當常見且實用的。
scenes/scene01/query.edgeql
insert Scene {
title:= "韓琛初現",
detail:= "韓琛準備派遣多個身家較為清白的小弟臥底至香港警隊，包括建明。" ++
"他向小弟們講述著自己的過去，並說自己不相信算命先生所說的" ++
"「一將功成萬骨枯」。他認為出來混的，未來的路怎麼走應該由自己決定。",
remarks:= "1.假設此場景為1992年。",
who:= {hon, lau},
`when`:= year_1992,
where:= (insert Location {name:= "佛堂"}) ,
references:= [("維基百科-無間道", "https://zh.wikipedia.org/zh-tw/%E7%84%A1%E9%96%93%E9%81%93"),
("香港警察職級", "https://zh.wikipedia.org/zh-tw/%E9%A6%99%E6%B8%AF%E8%AD%A6%E5%AF%9F%E8%81%B7%E7%B4%9A")]
};
Query review
Query review
scenes/scene01/query.edgeql
insert FuzzyTime {fuzzy_year:= 1992};
insert Actor {
name:= "曾志偉",
eng_name:= "Eric",
nickname:= "獎老",
};
insert GangsterBoss {
name:= "韓琛",
nickname:= "琛哥",
classic_lines:= ["一將功成萬骨枯"],
actors := assert_single((select Actor filter .name = "曾志偉")),
};
insert Actor {
name:= "陳冠希",
eng_name:= "Edison",
};
insert GangsterSpy {
name:= "劉建明",
nickname:= "劉仔",
gangster_boss:= assert_single((select GangsterBoss filter .name = "韓琛")),
dept:= "警校學生",
actors := assert_single((select Actor filter .name in {"陳冠希"})),
};
select test_alias();
insert Scene {
title:= "韓琛初現",
detail:= "韓琛準備派遣多個身家較為清白的小弟臥底至香港警隊，包括建明。" ++
"他向小弟們講述著自己的過去，並說自己不相信算命先生所說的" ++
"「一將功成萬骨枯」。他認為出來混的，未來的路怎麼走應該由自己決定。",
remarks:= "1.假設此場景為1992年。",
who:= {hon, lau},
`when`:= year_1992,
where:= (insert Location {name:= "佛堂"}) ,
references:= [("維基百科-無間道", "https://zh.wikipedia.org/zh-tw/%E7%84%A1%E9%96%93%E9%81%93"),
("香港警察職級", "https://zh.wikipedia.org/zh-tw/%E9%A6%99%E6%B8%AF%E8%AD%A6%E5%AF%9F%E8%81%B7%E7%B4%9A")]
};
無間假設
理論上，我們應該處理建明由Gangster object轉變到GangsterSpy object的過程，但這對於scene01來說，可能太過複雜，所以在此處直接insert建明為GangsterSpy object。同理，我們將於scene02直接insert永仁為PoliceSpy object，而不處理其由Police object轉變到PoliceSpy object的過程。
無間吹水
佛堂前六個骨灰罈暗指當年韓琛死於屯門的六個兄弟。他藉祭拜為由，於一眾小弟面前展現其「仁義之風」。