※ 本記事は、Ulrike Schwinnによる”Annotations – The new metadata in 23c“を翻訳したものです。

2023年4月19日

最近投稿された『Oracle Database 23c Free Developer Release – 10 Features you should know』で、私の同僚と私は10の興味深いSQL機能を紹介しました。ここでは、これらの機能の一部を詳しく説明し、詳細と背景情報を提供します。この投稿では、SQL機能の注釈にフォーカスし、いくつかの例を示し、それらの監視方法を示します。
 
注釈とは何で、どのように使用できますか? 通常、名前が示す注釈は、データベース・オブジェクトの追加プロパティ・メタデータです。これらはデータベースに一元的に格納されるため、アプリケーション、モジュールおよびマイクロサービス間でメタデータ情報を共有できます。これらを使用して、ビジネス・ロジックやユーザー・インタフェースをカスタマイズしたり、データベース・オブジェクトに関する情報をメタデータ・リポジトリに提供できます。
 
コメントと同様に、注釈はオプションです。より正確には、注釈は名前と値のペアまたはそれ自体の名前になります。名前は必須であり、値はオプションであり、両方を自由形式テキスト・フィールドとして指定できます。コメントについてはどうでしょうか? 注釈とコメントの違いは何でしょう? 注釈には、コメントよりも広い範囲があります。たとえば、表のみでなく、様々な種類のデータベース・オブジェクトに注釈を使用できます。コメントには名前がなく、自由形式の値しかなく、同じオブジェクトに複数のコメントを追加することはできません。

それらの使用方法を理解するための例をいくつか示します。

注釈は、CREATE文またはALTER文を使用して生成できます。注釈を追加または削除するには、注釈が指定されているスキーマ・オブジェクトに対する CREATE または ALTER 権限が必要です。
通常、注釈は次のようになります。

ANNOTATIONS ([ADD|DROP] annotation_name [ annotation_value ] [ , annotation_name [ annotation_value  ]... )

列注釈および表注釈を含む注釈付き表 EMP_ANNOTATED を作成します。

drop table if exists emp_annotated; -- with new 23c "if exists" syntax 

create table emp_annotated
( empno number,
  ename varchar2(50) annotations (display 'lastname'),
  salary number      annotations (person_salary, column_hidden)
 )
annotations (display 'employees'); 

名前および値は、最大4000文字を保持できる文字列リテラルです。最初の例のperson_salary、colum_hiddenおよびdisplayは名前で、’last_name’および’employees’は値です。予約語または空白文字を使用する必要がある場合は、二重引用符で囲みます。

注釈を追加または削除できます。たとえば、列 ename から列注釈の表示を削除します。

alter table emp_annotated modify ename annotations (drop display);

表および列のみがサポートされているだけでなく、ビューおよびビュー列、マテリアライズド・ビューおよびマテリアライズド・ビュー列、索引、および新しいSQLオブジェクト・ドメインおよび複数列ドメイン列もサポートされています。次のステップでは、注釈付きビュー EMPDEPT_ANN を作成します。

create or replace view empdept_ann
   (emp_id    annotations (Identity, display 'employee Id', category 'emp info'),
    emp_name  annotations (display 'employee name', category 'emp info'),
    emp_dname annotations (category 'emp info'))
    annotations (title 'employee view')
    as select e.empno, e.ename, d.dname from emp e, dept d
       where e.deptno=d.deptno and sal>1000;

次に、表DEPTと列DEPTNOに注釈値ペア display ‘Deptno Index’ で索引を作成しましょう。

create index i_dept_id on dept (deptno) annotations (display 'Deptno Index');
Index created.

最後の手順では、作成した注釈をモニターします。データ・ディクショナリ・ビュー USER_ANNOTATIONS および USER_ANNOTATIONS_USAGE (接頭辞 ALL/DBA) を問い合せて、スキーマ・オブジェクト全体での注釈とその使用状況のリストを追跡します。

表、索引、ドメインおよびビュー注釈のオブジェクト・レベルの注釈を取得するには:

set lines 200
set pages 200
col object_name format a25
col object_type format a15
col annotation_name format a15
col annotation_value format a15
col column_name format a20

select object_name, object_type, annotation_name, annotation_value 
from user_annotations_usage
where column_name is null order by 2,1;

OBJECT_NAME               OBJECT_TYPE     ANNOTATION_NAME ANNOTATION_VALU
------------------------- --------------- --------------- ---------------
YEARBIRTH                 DOMAIN          TITLE           yearformat
I_DEPT_ID                 INDEX           DISPLAY         Deptno Index
EMP_ANNOTATED             TABLE           DISPLAY         employees
PERSON                    TABLE           DISPLAY         person_table
EMPDEPT_ANN               VIEW            TITLE           employee view

5行が選択されました。

列レベルの注釈を取得するには:

select object_name, object_type, column_name, annotation_name, annotation_value 
from user_annotations_usage
where column_name is not null order by 2,1;

OBJECT_NAME               OBJECT_TYPE     COLUMN_NAME          ANNOTATION_NAME ANNOTATION_VALU
------------------------- --------------- -------------------- --------------- ---------------
EMP_ANNOTATED             TABLE           SALARY               COL_HIDDEN
EMP_ANNOTATED             TABLE           SALARY               PERSON_SALARY
PERSON                    TABLE           PERSON_BIRTH         TITLE           yearformat
EMPDEPT_ANN               VIEW            EMP_NAME             CATEGORY        emp info
EMPDEPT_ANN               VIEW            EMP_DNAME            CATEGORY        emp info
EMPDEPT_ANN               VIEW            EMP_NAME             DISPLAY         employee name
EMPDEPT_ANN               VIEW            EMP_ID               DISPLAY         employee Id
EMPDEPT_ANN               VIEW            EMP_ID               IDENTITY
EMPDEPT_ANN               VIEW            EMP_ID               CATEGORY        emp info

9行が選択されました。

次に、列注釈を列ごとに単一のJSONコレクションとして表示:

select object_type, object_name, column_Name, JSON_ARRAYAGG(JSON_OBJECT(Annotation_Name,Annotation_Value)) in_jsonformat 
from user_annotations_usage
where column_name is not null group by object_type, object_name, column_name;

OBJECT_TYPE     OBJECT_NAME               COLUMN_NAME
--------------- ------------------------- --------------------
IN_JSONFORMAT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------
VIEW            EMPDEPT_ANN               EMP_ID
[{"Annotation_Name":"IDENTITY","Annotation_Value":null},{"Annotation_Name":"CATEGORY","Annotation_Value":"emp info"},{"Annotation_Name":"DISPLAY","Annotation_Value":"employee Id"}]
VIEW            EMPDEPT_ANN               EMP_NAME
[{"Annotation_Name":"DISPLAY","Annotation_Value":"employee name"},{"Annotation_Name":"CATEGORY","Annotation_Value":"emp info"}]
VIEW            EMPDEPT_ANN               EMP_DNAME
[{"Annotation_Name":"CATEGORY","Annotation_Value":"emp info"}]
TABLE           PERSON                    PERSON_BIRTH
[{"Annotation_Name":"TITLE","Annotation_Value":"yearformat"}]
TABLE           EMP_ANNOTATED             SALARY
[{"Annotation_Name":"PERSON_SALARY","Annotation_Value":null},{"Annotation_Name":"COL_HIDDEN","Annotation_Value":null}]
5 rows selected.

さらに詳しく