{QLDB}Amazon QLDB へのアクセス

-- 1. 台帳の作成
aws qldb create-ledger \
--name qldb01 \
--permissions-mode STANDARD \
--no-deletion-protection

-- 2. 台帳一覧
aws qldb list-ledgers

aws qldb describe-ledger --name qldb01

-- 3. QLDB シェルを使用した Amazon QLDB へのアクセス

クライアントEC2(Amazon Linux2)にPython3.8をインストール

amazon-linux-extras list | grep python
sudo amazon-linux-extras install -y python3.8
python3.8 -V

sudo pip3.8 install qldbshell

qldbshell --ledger qldb01

create table testtable
insert into testtable `{"name": "john doe"}`
select * from testtable
drop table testtable
exit

-- 4. 台帳にテーブル、インデックス、テストデータを作成
qldbshell --ledger qldb01

create table tab1
create table tab2
create table tab3

create index on tab1 (vin)
create index on tab3 (vin)

insert into tab1
<<
{ 'vin' : '11111'},
{ 'vin' : '22222'},
{ 'vin' : '33333'}
>>

insert into tab2
<<
{ 'name' : 'alice'},
{ 'name' : 'bob'},
{ 'name' : 'carol'}
>>

insert into tab3
<<
{
'vin' : '11111',
'city' : '',
'owners' : {
'primaryowner' : { 'personid': '' },
'secondaryowners' :
}
},
{
'vin' : '22222',
'city' : '',
'owners' : {
'primaryowner' : { 'personid': '' },
'secondaryowners' :
}
},
{
'vin' : '33333',
'city' : '',
'owners' : {
'primaryowner' : { 'personid': '' },
'secondaryowners' : []
}
}
>>

-- 5. 台帳のテーブルのクエリー

select * from tab1 as v
where v.vin = '11111'

select v.vin, r.city, r.owners, r.vin
from tab1 as v, tab3 as r
where v.vin = '11111'
and v.vin = r.vin

-- 6. 台帳内の文書の変更

-- aliceのpersonid取得

select metadata.id from _ql_committed_tab2 as p
where p.data.name = 'alice'

-- ownerとcityの更新(1回目)

select r.owners.primaryowner, r.city
from tab3 as r
where r.vin = '11111'

update tab3 as r
set r.owners.primaryowner.personid = '3Ax1tFYi2iuC7bebbQBlaM', --replace with your id
r.city = 'tokyo'
where r.vin = '11111'

select r.owners.primaryowner, r.city
from tab3 as r
where r.vin = '11111'

-- bobのpersonid取得
select metadata.id from _ql_committed_tab2 as p
where p.data.name = 'bob'

-- ownerとcityの更新(2回目)
select r.owners.primaryowner, r.city
from tab3 as r
where r.vin = '11111'

update tab3 as r
set r.owners.primaryowner.personid = '0FyC9IJqNE675cdxoFxL2r', --replace with your id
r.city = 'yokohama'
where r.vin = '11111'

select r.owners.primaryowner, r.city
from tab3 as r
where r.vin = '11111'

-- 7. ドキュメントのリビジョン履歴を表示する

select r_id from tab3 as r by r_id
where r.vin = '11111'

select h.data.vin, h.data.city, h.data.owners
from history(tab3) as h
where h.metadata.id = '90uhNA2RiSfCmKkJwki9TW' --replace with your id

select value h.metadata
from history(tab3) as h
where h.metadata.id = '90uhNA2RiSfCmKkJwki9TW' --replace with your id

-- 8. 台帳内の文書の検証

-- ダイジェストの取得

aws qldb get-digest --name qldb01

-- リビジョンの取得

select r.metadata.id, r.blockAddress
from _ql_committed_tab3 as r
where r.data.vin = '11111'

aws qldb get-revision \
--name qldb01 \
--block-address 'IonText="{strandId:\"Ie5RoXlbY005zZWLfrXHAR\",sequenceNo:23}"' \
--document-id 90uhNA2RiSfCmKkJwki9TW \
--digest-tip-address 'IonText="{strandId:\"Ie5RoXlbY005zZWLfrXHAR\",sequenceNo:27}"'

-- ブロックの取得

aws qldb get-block \
--name qldb01 \
--block-address 'IonText="{strandId:\"Ie5RoXlbY005zZWLfrXHAR\",sequenceNo:23}"' \
--digest-tip-address 'IonText="{strandId:\"Ie5RoXlbY005zZWLfrXHAR\",sequenceNo:27}"'

-- 9. 台帳の削除

aws qldb delete-ledger --name qldb01