- リレーショナル表の列名は一般にスネークケース（単語を _ アンダースコアで連結）ですが、Relational to collection mappingは列名からJSONの属性名を決める際に、スネークケースの名前をそのまま使用します。JSONの属性名はキャメルケース（単語を区切らず２語目以降の先頭文字を大文字）にするのが一般的です。
- サブ・コレクションにドキュメントを保存する際に、親のコレクションのドキュメントも一緒に指定しますが、親となるドキュメントの主キーがサブ・コレクションに反映されません。サブ・コレクションのドキュメントに、明示的に親コレクションのドキュメントの主キー値を含める必要があります。
- JSON Duality Viewで日付データを扱う場合、Timestamp型を使用します。これはデータベースの列の型がDateやTimestampであるためです。JSONドキュメントに日付を含める場合はlongを使うこともありますが、JSON Duality Viewの場合、longの値はNUMBER型として保存します。
- 空文字列の扱いに注意が必要です。元表の列のデータ型がVARCHAR2の場合、Oracle Databaseでは空文字列をnullとして扱います。例えばJSONとして{ "ownerId": "" }を保存した場合、JSON Duality Viewから保存したデータを取り出すと{ "ownerId": null }が返されます。JSON列やJSONコレクション表に保存した場合は{ "ownerId": "" }が返されます。列のデータ型をCLOBにするとnullと空文字列を分けることができますが、空文字列としてEMPTY_CLOB()を与える必要があります。
コレクションrecipesとサブ・コレクションratingsの作成
create table recipes (
id number generated by default on null as identity
constraint recipes_id_pk primary key,
title varchar2(80 char) not null,
description varchar2(800 char),
category varchar2(16 char) constraint recipes_category_ck
check (category in ('Breakfast','Lunch','Dinner','Dessert')),
prep_time number,
-- ingradients is json array so the datatype is required to be json.
ingredients json,
instructions varchar2(4000 char),
-- JavaScript lab does not use averageRating and ratingCount, iOS and Android do.
average_rating number default 0 not null,
rating_count number default 0 not null,
photo_URL varchar2(400 char),
owner_id varchar2(80 char),
created_at date default sysdate not null,
created_by varchar2(80 char)
);
create table ratings (
id number generated by default on null as identity
constraint ratings_id_pk primary key,
recipe_id number not null constraint ratings_recipe_id_fk
references recipes on delete cascade,
author varchar2(160 char) not null,
rating number not null constraint ratings_rating_ck
check (rating in (1,2,3,4,5)),
-- JavaScript LiveLabs does not have created_at for ratings.
created_at date default sysdate not null,
-- "comment" is the keyword of Oracle SQL.
"COMMENT" varchar2(80 char)
);
-- table index
create index ratings_i1 on ratings (recipe_id);SQL> create table recipes (
2 id number generated by default on null as identity
3 constraint recipes_id_pk primary key,
4 title varchar2(80 char) not null,
5 description varchar2(800 char),
6 category varchar2(16 char) constraint recipes_category_ck
7 check (category in ('Breakfast','Lunch','Dinner','Dessert')),
8 prep_time number,
9 -- ingradients is json array so the datatype is required to be json.
10 ingredients json,
11 instructions varchar2(4000 char),
12 -- JavaScript lab does not use averageRating and ratingCount, iOS and Android do.
13 average_rating number,
14 rating_count number,
15 photo_URL varchar2(400 char),
16 owner_id varchar2(80 char),
17 created_at date default sysdate not null,
18 created_by varchar2(80 char)
19* );
Table RECIPESは作成されました。
SQL> create table ratings (
2 id number generated by default on null as identity
3 constraint ratings_id_pk primary key,
4 recipe_id number not null constraint ratings_recipe_id_fk
5 references recipes on delete cascade,
6 author varchar2(160 char) not null,
7 rating number not null constraint ratings_rating_ck
8 check (rating in (1,2,3,4,5)),
9 -- JavaScript LiveLabs does not have created_at for ratings.
10 created_at date default sysdate not null,
11 -- "comment" is the keyword of Oracle SQL.
12 "COMMENT" varchar2(80 char)
13* );
Table RATINGSは作成されました。
SQL> create index ratings_i1 on ratings (recipe_id);
Index RATINGS_I1は作成されました。
SQL>
select * from baas_collection_metadata;
列CONDITIONに、コレクションの実体となるJSON Duality ViewのDDLが記載されています。
SQL> select * from baas_collection_metadata;
TABLE_NAME PATH PATH_HASH TABLE_TYPE CREATED CONDITION
_______________ __________________________ _____________ _____________ ______________________________ _______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
RECIPES$BAAS /recipes 3905671794 rel 26-08-12 02:45:26.450318000 create or replace json relational duality view "RECIPES$BAAS" as select json {'title':"TITLE", 'description':"DESCRIPTION", 'category':"CATEGORY", 'prep_time':"PREP_TIME", 'ingredients':"INGREDIENTS", 'instructions':"INSTRUCTIONS", 'average_rating':"AVERAGE_RATING", 'rating_count':"RATING_COUNT", 'photourl':"PHOTOURL", 'owner_id':"OWNER_ID", 'created_at':"CREATED_AT", 'created_by':"CREATED_BY", 'OID' : SYS_MAKE_OID_FROM_PK("ID"),'_id':"ID",'parent_oid':'_docId'} from "RECIPES" with insert delete update
RATINGS$BAAS /recipes/_docId/ratings 1914104494 rel 26-08-12 02:45:26.452620000 create or replace json relational duality view "RATINGS$BAAS" as select json {'recipe_id':"RECIPE_ID", 'author':"AUTHOR", 'rating':"RATING", 'created_at':"CREATED_AT", 'the_comment':"THE_COMMENT", 'OID' : SYS_MAKE_OID_FROM_PK("ID"),'_id':"ID",'parent_oid': GENERATED USING ( select '/' ||SYS_MAKE_OID_FROM_PK("RECIPES"."ID") from "RECIPES" where "RECIPES"."ID" = "RATINGS"."RECIPE_ID")} from "RATINGS" with insert delete update
SQL>
スネークケースの列名がそのままJSONの属性名となっています。LiveLabsのコードを変更しないで済むように、LiveLabsで使用されている属性名でアクセスできるように、JSON Duality Viewを作り直します。
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW "RECIPES$BAAS" AS
SELECT JSON {
'title' : "TITLE",
'description' : "DESCRIPTION",
'category' : "CATEGORY",
'prepTime' : "PREP_TIME",
'ingredients' : "INGREDIENTS",
'instructions' : "INSTRUCTIONS",
'photoURL' : "PHOTO_URL",
'ownerId' : "OWNER_ID",
'averageRating' : "AVERAGE_RATING",
'ratingCount' : "RATING_COUNT",
'createdAt' : "CREATED_AT",
'createdBy' : "CREATED_BY",
'OID' : SYS_MAKE_OID_FROM_PK("ID"),
'_id' : "ID",
'parent_oid' : '_docId'
}
FROM "RECIPES"
WITH INSERT DELETE UPDATE;
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW "RATINGS$BAAS" AS
SELECT JSON {
'recipeId' : "RECIPE_ID",
'author' : "AUTHOR",
'rating' : "RATING",
'comment' : "COMMENT",
'createdAt' : "CREATED_AT",
'OID' : SYS_MAKE_OID_FROM_PK("ID"),
'_id' : "ID",
'parent_oid' : GENERATED USING (
SELECT '/' || SYS_MAKE_OID_FROM_PK("RECIPES"."ID")
FROM "RECIPES"
WHERE "RECIPES"."ID" = "RATINGS"."RECIPE_ID"
)
}
FROM "RATINGS"
WITH INSERT DELETE UPDATE;SQL> CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW "RECIPES$BAAS" AS
2 SELECT JSON {
3 'title' : "TITLE",
4 'description' : "DESCRIPTION",
5 'category' : "CATEGORY",
6 'prepTime' : "PREP_TIME",
7 'ingredients' : "INGREDIENTS",
8 'instructions' : "INSTRUCTIONS",
9 'photoURL' : "PHOTO_URL",
10 'ownerId' : "OWNER_ID",
11 'averageRating' : "AVERAGE_RATING",
12 'ratingCount' : "RATING_COUNT",
13 'createdAt' : "CREATED_AT",
14 'createdBy' : "CREATED_BY",
15 'OID' : SYS_MAKE_OID_FROM_PK("ID"),
16 '_id' : "ID",
17 'parent_oid' : '_docId'
18 }
19 FROM "RECIPES"
20* WITH INSERT DELETE UPDATE;
View "RECIPES$BAAS"は作成されました。
SQL> CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW "RATINGS$BAAS" AS
2 SELECT JSON {
3 'recipeId' : "RECIPE_ID",
4 'author' : "AUTHOR",
5 'rating' : "RATING",
6 'comment' : "COMMENT",
7 'createdAt' : "CREATED_AT",
8 'OID' : SYS_MAKE_OID_FROM_PK("ID"),
9 '_id' : "ID",
10 'parent_oid' : GENERATED USING (
11 SELECT '/' || SYS_MAKE_OID_FROM_PK("RECIPES"."ID")
12 FROM "RECIPES"
13 WHERE "RECIPES"."ID" = "RATINGS"."RECIPE_ID"
14 )
15 }
16 FROM "RATINGS"
17* WITH INSERT DELETE UPDATE;
View "RATINGS$BAAS"は作成されました。
SQL>
select * from baas_relational_metadata;
SQL> select * from baas_relational_metadata;
ROOT PARENT CHILD PK FK
_______ __________ __________ _________ ________________
0 recipes ratings ["ID"] ["RECIPE_ID"]
1 recipes ["ID"]
0 ratings ["ID"]
SQL>
これから、プラットフォームごとのLiveLabsのワークショップの作業を始めます。
Web/JavaScript SDKでの対応
// ── Rating form ───────────────────────────────
el.ratingForm.addEventListener("submit", (event) => {
event.preventDefault();
const fd = new FormData(el.ratingForm);
runAction("Rating saved.", async () => {
// ── Lab 5 TODO: Add ratings with the SDK ─────
// Use doc() and addDoc(collection(recipeRef, "ratings"), ...) to save
// a rating into the recipe's ratings subcollection.
const recipeRef = doc(collection(db, "recipes"), state.activeRecipeId);
// JSON Duality View Support:
const recipe = state.recipes.find((r) => r.id === state.activeRecipeId);
const recipePk = recipe._id;
await addDoc(collection(recipeRef, "ratings"), {
recipeId: recipePk, // JSON Duality View
author: auth.currentUser.email,
rating: Number(fd.get("rating") ?? "5"),
comment: String(fd.get("comment") ?? "").trim()
});
el.ratingForm.reset();
await refreshRecipes(state.activeRecipeId);
});
});
iOS/Swift SDKでの対応
変更前: "createdAt": now - day,
変更後: "createdAt": FusabaseTimestamp.string(from: Date(timeIntervalSinceNow: -86400)),
141行目
変更前: "createdAt": now - (day / 2),
変更後: "createdAt": FusabaseTimestamp.string(from: Date(timeIntervalSinceNow: -43200)),
162行目
変更前: "createdAt": now - (day / 4),
変更後: "createdAt": FusabaseTimestamp.string(from: Date(timeIntervalSinceNow: -21600)),
183行目
変更前: "createdAt": now,
変更後: "createdAt": FusabaseTimestamp.string(from: Date()),
変数nowおよびdayは使わないので、コメントアウトします。
変更前: "createdAt": Date().timeIntervalSince1970 * 1000.0,
変更後: "createdAt": FusabaseTimestamp.string(from: Date()),
Android/Java SDKでの対応
for (QueryDocumentSnapshot doc : snapshot) {
Recipe r = Recipe.fromMap(doc.getId(), doc.getData());
// Recipe r = doc.toObject(Recipe.class);
if (r != null) {
// r.setId(doc.getId());
mapped.add(r);
}
} for (QueryDocumentSnapshot d : snapshot) {
Rating r = Rating.fromMap(d.getId(), d.getData());
// Rating r = d.toObject(Rating.class);
if (r != null) {
// r.setId(d.getId());
mapped.add(r);
}
}import java.util.Date;
import java.time.Instant;
import java.time.temporal.ChronoUnit;
import com.oracle.mobile.fusabase.Timestamp;変更後: new Timestamp(now.minus(1, ChronoUnit.DAYS))
変更前: now - (day / 2))
変更後: new Timestamp(now.minus(12, ChronoUnit.HOURS))
変更前: now - (day / 4))
変更後: new Timestamp(now.minus(6, ChronoUnit.HOURS))
変更前: now
変更後: Timestamp.now()
private static Map<String, Object> seed(String title, String description, String category,
long prepTime, String instructions,
List<String> ingredients, long createdAt) private static Map<String, Object> seed(String title, String description, String category,
long prepTime, String instructions,
List<String> ingredients, Timestamp createdAt) { // long to Timestamp data.put("createdAt", Timestamp.now()); // long to Timestamp
// data.put("createdAt", System.currentTimeMillis());