{"id":13430,"date":"2024-06-27T13:31:12","date_gmt":"2024-06-27T11:31:12","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=13430"},"modified":"2024-07-02T00:12:57","modified_gmt":"2024-07-01T22:12:57","slug":"islandsql-episode-9-graphql-json-and-flexible-schemas-with-duality-views","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2024\/06\/27\/islandsql-episode-9-graphql-json-and-flexible-schemas-with-duality-views\/","title":{"rendered":"IslandSQL Episode 9: GraphQL, JSON and Flexible Schemas With Duality Views"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

In the last episode<\/a>, we looked at some new features in Oracle Database 23.4. The IslandSQL<\/a> grammar now covers all statements that can contain static DML statements and code in PL\/SQL and PL\/pgSQL. <\/p>\n\n\n\n

While implementing the ANTLR grammar for the create JSON relational duality view<\/a> statement I stumbled over GraphQL in this syntax diagram:<\/p>\n\n\n\n

\"create_json_relational_duality_view<\/a><\/figure>\n\n\n\n

You can use GraphQL<\/a> as an alternative to a subquery to describe the source of your JSON relational duality view. This feature was already part of 23.3. Usually, I don’t like it when there are several ways to do the same thing. However, in this case, GraphQL helped me to understand the variant of the select statement<\/code> in the duality view better. GraphQL might even be better suited to describe the content of a duality view.<\/p>\n\n\n\n

The funny thing is that JSON is about schema flexibility and GraphQL needs a schema to work. That sounds contradictory. However, if we reduce the scope of schema flexibility to a JSON object within existing entities, this can work quite well.<\/p>\n\n\n\n

In this blog post, I explore some features related to the schema flexibility of JSON relational duality views.<\/p>\n\n\n\n

    \n
  1. Setup<\/a><\/li>\n\n\n\n
  2. Read-only View \u00e0 la 19c<\/a><\/li>\n\n\n\n
  3. Read-only Duality View<\/a><\/li>\n\n\n\n
  4. Updateable Duality View Using SELECT<\/a><\/li>\n\n\n\n
  5. Updateable Duality View Using GraphQL<\/a><\/li>\n\n\n\n
  6. GraphQL vs. SELECT<\/a><\/li>\n\n\n\n
  7. Insert Into Duality View<\/a><\/li>\n\n\n\n
  8. Update Duality View<\/a><\/li>\n\n\n\n
  9. Delete From Duality View<\/a><\/li>\n<\/ol>\n\n\n\n

    1. Setup<\/h2>\n\n\n\n

    The examples in this blog post require an Oracle Database 23.4 (yes, 23.3 is not enough). In a schema of your choice you can run the following setup script:<\/p>\n\n\n\n

    1) Setup for extended dept and emp table<\/span><\/path><\/path><\/svg><\/span>
    set<\/span> linesize <\/span>200<\/span><\/span>\nset<\/span> pagesize <\/span>1000<\/span>  <\/span><\/span>\nset<\/span> <\/span>long<\/span> <\/span>32767<\/span><\/span>\ncolumn<\/span> ext format a72<\/span><\/span>\ncolumn<\/span> data format a130<\/span><\/span>\nalter session<\/span> <\/span>set<\/span> nls_date_format = <\/span>'YYYY-MM-DD'<\/span>;<\/span><\/span>\n<\/span>\ndrop<\/span> <\/span>table<\/span> <\/span>if<\/span> <\/span>exists<\/span> emp;<\/span><\/span>\ndrop<\/span> <\/span>table<\/span> <\/span>if<\/span> <\/span>exists<\/span> dept;<\/span><\/span>\n<\/span>\ncreate<\/span> <\/span>table<\/span> dept (<\/span><\/span>\n   deptno <\/span>number<\/span>(<\/span>2<\/span>, <\/span>0<\/span>)      <\/span>not<\/span> <\/span>null<\/span> <\/span>constraint<\/span> dept_pk <\/span>primary key<\/span>,<\/span><\/span>\n   dname  <\/span>varchar2<\/span>(<\/span>14<\/span> <\/span>char<\/span>) <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   loc    <\/span>varchar2<\/span>(<\/span>13<\/span> <\/span>char<\/span>) <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   ext    json(object)<\/span><\/span>\n);<\/span><\/span>\n<\/span>\ncreate<\/span> <\/span>table<\/span> emp (<\/span><\/span>\n   empno    <\/span>number<\/span>(<\/span>4<\/span>, <\/span>0<\/span>)      <\/span>not<\/span> <\/span>null<\/span>  <\/span>constraint<\/span> emp_pk <\/span>primary key<\/span>,<\/span><\/span>\n   ename    <\/span>varchar2<\/span>(<\/span>10<\/span> <\/span>char<\/span>) <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   job      <\/span>varchar2<\/span>(<\/span>9<\/span> <\/span>char<\/span>)  <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   mgr      <\/span>number<\/span>(<\/span>4<\/span>, <\/span>0<\/span>)                <\/span>constraint<\/span> emp_mgr_fk <\/span>references<\/span> emp,<\/span><\/span>\n   hiredate <\/span>date<\/span>              <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   sal      <\/span>number<\/span>(<\/span>7<\/span>, <\/span>2<\/span>)      <\/span>not<\/span> <\/span>null<\/span>,<\/span><\/span>\n   comm     <\/span>number<\/span>(<\/span>7<\/span>, <\/span>2<\/span>),<\/span><\/span>\n   deptno   <\/span>number<\/span>(<\/span>2<\/span>, <\/span>0<\/span>)      <\/span>not<\/span> <\/span>null<\/span>  <\/span>constraint<\/span> emp_deptno_fk <\/span>references<\/span> dept,<\/span><\/span>\n   ext      json(object)<\/span><\/span>\n);<\/span><\/span>\n<\/span>\ninsert<\/span> <\/span>into<\/span> dept (deptno, dname, loc)<\/span><\/span>\nvalues<\/span> (<\/span>10<\/span>, <\/span>'ACCOUNTING'<\/span>, <\/span>'NEW YORK'<\/span>),<\/span><\/span>\n       (<\/span>20<\/span>, <\/span>'RESEARCH'<\/span>,   <\/span>'DALLAS'<\/span>),<\/span><\/span>\n       (<\/span>30<\/span>, <\/span>'SALES'<\/span>,      <\/span>'CHICAGO'<\/span>),<\/span><\/span>\n       (<\/span>40<\/span>, <\/span>'OPERATIONS'<\/span>, <\/span>'BOSTON'<\/span>);<\/span><\/span>\ncommit<\/span>;<\/span><\/span>\n       <\/span><\/span>\ninsert<\/span> <\/span>into<\/span> emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)<\/span><\/span>\nvalues<\/span> (<\/span>7566<\/span>, <\/span>'JONES'<\/span>,  <\/span>'MANAGER'<\/span>,   <\/span>7839<\/span>, <\/span>date<\/span> <\/span>'1981-04-02'<\/span>, <\/span>2975<\/span>, <\/span>null<\/span>, <\/span>20<\/span>),<\/span><\/span>\n       (<\/span>7698<\/span>, <\/span>'BLAKE'<\/span>,  <\/span>'MANAGER'<\/span>,   <\/span>7839<\/span>, <\/span>date<\/span> <\/span>'1981-05-01'<\/span>, <\/span>2850<\/span>, <\/span>null<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7782<\/span>, <\/span>'CLARK'<\/span>,  <\/span>'MANAGER'<\/span>,   <\/span>7839<\/span>, <\/span>date<\/span> <\/span>'1981-06-09'<\/span>, <\/span>2450<\/span>, <\/span>null<\/span>, <\/span>10<\/span>),<\/span><\/span>\n       (<\/span>7788<\/span>, <\/span>'SCOTT'<\/span>,  <\/span>'ANALYST'<\/span>,   <\/span>7566<\/span>, <\/span>date<\/span> <\/span>'1987-04-19'<\/span>, <\/span>3000<\/span>, <\/span>null<\/span>, <\/span>20<\/span>),<\/span><\/span>\n       (<\/span>7902<\/span>, <\/span>'FORD'<\/span>,   <\/span>'ANALYST'<\/span>,   <\/span>7566<\/span>, <\/span>date<\/span> <\/span>'1981-12-03'<\/span>, <\/span>3000<\/span>, <\/span>null<\/span>, <\/span>20<\/span>),<\/span><\/span>\n       (<\/span>7499<\/span>, <\/span>'ALLEN'<\/span>,  <\/span>'SALESMAN'<\/span>,  <\/span>7698<\/span>, <\/span>date<\/span> <\/span>'1981-02-20'<\/span>, <\/span>1600<\/span>,  <\/span>300<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7521<\/span>, <\/span>'WARD'<\/span>,   <\/span>'SALESMAN'<\/span>,  <\/span>7698<\/span>, <\/span>date<\/span> <\/span>'1981-02-22'<\/span>, <\/span>1250<\/span>,  <\/span>500<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7654<\/span>, <\/span>'MARTIN'<\/span>, <\/span>'SALESMAN'<\/span>,  <\/span>7698<\/span>, <\/span>date<\/span> <\/span>'1981-09-28'<\/span>, <\/span>1250<\/span>, <\/span>1400<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7844<\/span>, <\/span>'TURNER'<\/span>, <\/span>'SALESMAN'<\/span>,  <\/span>7698<\/span>, <\/span>date<\/span> <\/span>'1981-09-08'<\/span>, <\/span>1500<\/span>,    <\/span>0<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7900<\/span>, <\/span>'JAMES'<\/span>,  <\/span>'CLERK'<\/span>,     <\/span>7698<\/span>, <\/span>date<\/span> <\/span>'1981-12-03'<\/span>,  <\/span>950<\/span>, <\/span>null<\/span>, <\/span>30<\/span>),<\/span><\/span>\n       (<\/span>7934<\/span>, <\/span>'MILLER'<\/span>, <\/span>'CLERK'<\/span>,     <\/span>7782<\/span>, <\/span>date<\/span> <\/span>'1982-01-23'<\/span>, <\/span>1300<\/span>, <\/span>null<\/span>, <\/span>10<\/span>),<\/span><\/span>\n       (<\/span>7369<\/span>, <\/span>'SMITH'<\/span>,  <\/span>'CLERK'<\/span>,     <\/span>7902<\/span>, <\/span>date<\/span> <\/span>'1980-12-17'<\/span>,  <\/span>800<\/span>, <\/span>null<\/span>, <\/span>20<\/span>),<\/span><\/span>\n       (<\/span>7839<\/span>, <\/span>'KING'<\/span>,   <\/span>'PRESIDENT'<\/span>, <\/span>null<\/span>, <\/span>date<\/span> <\/span>'1981-11-17'<\/span>, <\/span>5000<\/span>, <\/span>null<\/span>, <\/span>10<\/span>),<\/span><\/span>\n       (<\/span>7876<\/span>, <\/span>'ADAMS'<\/span>,  <\/span>'CLERK'<\/span>,     <\/span>7788<\/span>, <\/span>date<\/span> <\/span>'1987-05-23'<\/span>, <\/span>1100<\/span>, <\/span>null<\/span>, <\/span>20<\/span>);<\/span><\/span>\ncommit<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/path><\/path><\/svg><\/span>
    <\/span><\/span>\nSession altered.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nTable EMP dropped.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nTable DEPT dropped.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nTable DEPT created.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nTable EMP created.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\n4 rows inserted.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nCommit complete.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\n14 rows inserted.<\/span><\/span>\n<\/span><\/span>\n<\/span><\/span>\nCommit complete.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    There are a few changes to the well-known dept<\/code> and emp<\/code> tables I’d like to highlight:<\/p>\n\n\n\n