Native support of JSON binary views in database management system

Abstract

The JSON binary view is an object view that returns a JDV object. The JDV objects are virtual as they are not stored in the database as JSON objects. Instead, the JDV objects are stored in split form across tables and table attributes (e.g., columns) and returned by the DBMS in response to database commands for requesting the JDV objects from the JSON binary view. Through the JSON binary view, changes to the state of the JDV object may be specified at the JDV object level. The JDV object is updated in the database using an optimistic lock.

Description

Field of the Invention

[0001] The present disclosure relates to the storage of JavaScript Object Notation (JSON) objects in a database management system (DBMS), including a relational DBMS (RDBMS) and a DBMS that stores a collection of tables or documents, such as JSON objects.

[0002] Related Applications

[0003] This application is related to the U.S. patent application entitled "Techniques for Comprehensively Supporting JSON Schema in a RDBMS" with attorney docket number 50277-5882, filed on the same day by Zhen Hua Liu et al., the entire content of which is incorporated herein by reference. Background Art

[0004] JSON is a lightweight data specification language for formatting "JSON objects". A JSON object includes a collection of fields, where each field is a field name / value pair. The field name is actually the tag name of a node in the JSON object. The name of the field is separated from the value of the field by a colon.

[0005] Both RDBMS vendors and non-SQL vendors support JSON functionality to varying degrees. In particular, RDBMS vendors support storing JSON text in a varchar or character large object (CLOB) column and applying structured query language (SQL) and / or JSON operators to the JSON text as specified by the SQL / JSON standard. The RDBMS may also include a native JSON data type. A column can be defined as a JSON data type, and dot notation can be used to reference a JSON field within the column. JSON operators can operate on columns having a JSON data type.

[0006] Another important way in which RDBMS vendors support JSON functionality is by enabling the generation of JSON objects through an object view of relational data. Under the object view approach, the data of individual JSON objects is stored and retrieved across multiple columns in one or more tables of a relational database. Each of the field values of a JSON object can be stored in a corresponding column across multiple tables of the relational database. In effect, the values of a JSON object are shredded across multiple columns of one or more underlying tables.

[0007] The object view method provides a duality for querying the content of a JSON object. The content of the JSON object can be accessed as a JSON object or as relational data. Through SQL commands, the content of the JSON object can be accessed as a table with rows and columns. Through the object view, the content of the JSON object can be returned as a JSON object with fields including object fields and array fields.

[0008] Unfortunately, RDBMS vendors have not implemented the duality for changing the content of a JSON object under the object view method. The RDBMS provides a very efficient way to modify data into relational data, thus effectively handling DML (Data Manipulation Language) commands issued against columns and tables.

[0009] However, the RDBMS does not provide the capability to handle commands that specify changes to the JSON object in the object view. To change the JSON object, DML commands that specify changes to the underlying tables and columns must be issued.

[0010] Based on the above, it is desirable to provide a method for the RDBM to handle commands that specify changes to the JSON object returned by the object view. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] In the drawings:

[0012] Figure 1 is a diagram depicting a DDL statement defining a JSON duality view and a JDV object returned by the JSON duality view according to an embodiment of the present invention.

[0013] Figure 2A is a diagram of rewriting database commands based on a JSON duality view according to an embodiment of the present invention.

[0014] Figure 2B is a diagram depicting a DDL statement defining a JSON duality view and a JDV object returned by the JSON duality view according to an embodiment of the present invention, where the JSON duality view is based on a single root table.

[0015] Figure 3 is a diagram depicting a DDL statement defining a JSON duality view and a JDV object returned by the JSON duality view according to an embodiment of the present invention, where the JSON duality view includes a sub-table and a root table of object fields within an object view schema of the JSON duality view.

[0016] Figure 4 A diagram depicting a DDL statement that defines a JSON Duality View and a JDV object returned by the JSON Duality View according to an embodiment of the present invention, where the JSON Duality View includes a child table and a root table of an array field within an object view schema of the JSON Duality View.

[0017] Figure 5 A diagram depicting an archetype of an object hierarchy and a table hierarchy of a JSON Duality View according to an embodiment of the present invention.

[0018] Figure 6A A diagram depicting a JDV object returned by a three-table JSON Duality View.

[0019] Figure 6B A diagram depicting an object hierarchy and a table hierarchy of a JSON Duality View according to an embodiment of the present invention.

[0020] Figure 7 Depicts a base recordset hierarchy of a JSON Duality View and a corresponding table hierarchy according to an embodiment of the present invention.

[0021] Figure 8 Depicts a version signature function according to an embodiment of the present invention.

[0022] Figure 9 Depicts a call hierarchy of a version signature and a corresponding object hierarchy according to an embodiment of the present invention.

[0023] Figure 10 Depicts a call hierarchy of a version signature and a corresponding object hierarchy according to an embodiment of the present invention.

[0024] Figure 11 Depicts a database command rewritten to return a JDV object according to an embodiment of the present invention.

[0025] Figure 12 Depicts a flowchart of optimistic serialized updating according to an embodiment of the present invention.

[0026] Figure 13A Depicts a DDL statement that defines a JSON Duality View according to an embodiment of the present invention, with annotations depicting change permissions.

[0027] Figure 13BDepicts a DDL statement that defines a JSON duality view according to an embodiment of the present invention, with annotations depicting change permissions.

[0028] Figure 14 Depicts a DDL statement that defines a JSON duality view with annotations depicting change permissions according to an embodiment of the present invention, and a JDV object returned by the JSON duality view.

[0029] Figure 15A and Figure 15B Depicts the syntax of a DDL statement for defining a JSON duality view according to an embodiment of the present invention.

[0030] Figure 16 Depicts a DDL statement for defining a JSON duality view using a simplified syntax and a JSON schema of the JSON duality view.

[0031] Figure 17 Depicts a computer system that can be used to implement an embodiment of the present invention.

[0032] Figure 18 Depicts a software system that can be used to control operations. Detailed Description

[0033] In the following description, for purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of the present invention. However, it will be clear that the present invention may be practiced without these specific details. In other instances, structures and devices are shown in block diagram form to avoid unnecessarily obscuring the present invention.

[0034] Overview

[0035] A JSON duality view is described herein. A JSON duality view is an object view that returns a JSON duality view object (“JDV object”). JDV objects are virtual in that they are not stored as JSON objects in the database. Instead, JDV objects are stored in a split form across tables and table attributes (such as columns) and are returned by the DBMS in response to a database command that requests a JDV object from the JSON duality view.

[0036] Importantly, through the JSON duality view, changes to the state of a JDV object can be specified at the JDV object level. From a general viewpoint, this ability is similar to specifying state changes at the column level within a DBMS. The DBMS can execute database statements that assign values to columns. Similarly, the DBMS can execute statements that assign or set the state of a JDV object to a copy or instance of the JDV object that has been changed. The DBMS determines the specific underlying records in the database that need to be modified and then modifies those records.

[0037] Thus, changes can be specified at the JDV object level or at the level of the underlying properties of the underlying records that store the content of the JDV object. The ability to specify changes at these two levels can be referred to as data manipulation duality.

[0038] Applications that change JSON objects are configured based on the assumption that changes to the JSON object can be made serially. Changes are made serially when, in a database transaction, a new state that reflects the changes made to the database transaction is assigned to the JSON object and then the new state of the JSON object is committed. At the instant the new state of the JSON object is committed, the current state of the JSON object reflects only those changes.

[0039] To ensure serializability, pessimistic concurrency control can be used to change JDV objects. Under pessimistic concurrency control, the application starts a database transaction and "pessimistically" locks the JDV object, which locks at least all of the underlying records of the JDV object. Then, the application retrieves a copy of the JDV object through the JSON duality view, makes changes to the copy, and then issues a database command to the DBMS through the database transaction to update the JDV object to the copy.

[0040] Pessimistic concurrency control ensures serializability because no other changes can be made to the underlying records while the JDV object is locked. Unfortunately, pessimistic concurrency control adversely affects system performance by generating relatively more locks that are held for a relatively long period of time. Applications (especially Web applications that interact with the DBMS over the Internet) may hold locks on JDV objects for a relatively long period of time.

[0041] This document describes a method for updating JDV objects using optimistic concurrency control as an alternative to pessimistic concurrency control. The method minimizes locking while ensuring serializability. Under this method, a copy of the JDV object is retrieved from the DBMS via a JSON duality view. The copy of the JDV object is modified to a new version. A database statement is submitted to update the JSON object to the new version. In response, the DBMS updates and commits to confirm the new version only if it can guarantee serializability. The number of underlying records and the period for which the records are locked are minimized. It is not necessary to lock all the underlying records of the JDV object before retrieving the copy to be modified, as is the case under pessimistic concurrency control.

[0042] The method of optimistic concurrency control is referred to herein as optimistic serialization update.

[0043] Applicability to various types of DBMS

[0044] The method described in this document is applicable to various types of DBMS. A DBMS manages a database. Database data can be stored in a collection of one or more records. The data within each record is organized into one or more attributes. In a relational DBMS, the collection is called a table (or data frame), the records can be called rows, and the attributes are called columns. In a document DBMS (“DOCS”), the collection of records is a collection of documents or tables, and each of the records can be a data object marked up with a hierarchical markup language, such as a JSON object or an XML document. The attributes are called JSON fields or XML elements. The term field as used in this document refers to a JSON field.

[0045] These techniques are illustrated within the context of a relational DBMS that supports the execution of SQL statements to access and change the database of the DBMS. In a relational database, the columns of a table each store a scalar data type or a composite data type, such as an abstract data type. However, embodiments of the present invention are not necessarily limited to such a relational DBMS. To emphasize the applicability of the techniques described herein outside of relational DBMSs, the columns of a table are referred to herein as attributes, and the rows in a table are referred to herein as records. In the description of SQL statements presented herein, the columns referenced in the SQL statements are referred to herein as attributes.

[0046] Root collection / table definition

[0047] The JSON duality view can be used to return JDV objects of different complexities. The JSON duality view has a single root table. Typically, the JSON duality view maps the attributes of the root table to the scalar fields of the object view schema defined by the JSON duality view. The JSON duality view typically maps the attributes from multiple tables to the object view schema, as will be described in detail later. The tables or attributes whose fields are mapped by the JSON duality view to the object view schema defined by the JSON duality view are referred to herein as the base tables or base attributes with respect to the JSON duality view, the object view schema, or the JDV object returned from the JSON duality view. By mapping JSON fields to base attributes and base tables, the JSON duality view defines the object view schema.

[0048] Figure 1 A basic example of a JSON duality view based on only one table, which is the root table, is shown. Refer to Figure 1 , the DDL statement JDDL1 defines a JSON duality view employee_jdv1 based on the root table employee. The clause JSON MAPPINGVIEW specifies that the DDL statement JDDL1 is defining a JSON duality view with the subsequent name (which is employee_jdv1).

[0049] Similar to the DDL statements commonly used to define views, the DDL statement JDDL1 defines the following main query expression, which projects the attributes that are the attributes of the view. In the case of a JSON duality view, the projected attributes are JDV objects that conform to the object view schema. The term view query expression is referred to herein as the main query expression defined by the JSON duality view or the DDL statement that defines the JSON duality view.

[0050] The view query expression in JDDL1 projects the JSON object returned by the operator json_object(). The json_object() operator expression includes a field mapping that maps the field names of the object view schema to the following source SQL expressions, which evaluate to the values of those fields. The SQL expressions are typically the names of the attributes in the source tables listed in the corresponding FROM clause of the view query expression. JDDL1 maps the values of the attributes empno, empname, job, and sal from the root table employee to JSON fields of the same names (i.e., empno, empname, job, and sal). By mapping the fields in this way, the view query expression defines an object view schema that includes these fields at the root level of the object view schema. The root level will be described later.

[0051] The JDV object 101 is the JDV object returned by employee_jdv1. An example of a query statement that can be executed to generate the JDV object 101 is:

[0052] SELECT obj FROM employee_jdv1

[0053] The JDV object 101 includes the field-value pairs "empno": 7369, "ename": "SMITH", "job": "Accountant", and "sal": "1000", which are generated from the basic attributes empno, ename, job, and sal of the basic records in the root table employee respectively. The records whose values are used to generate the field values are referred to as basic records in this article. As will be described in further detail, JDV object field values can be generated from multiple basic records from multiple tables. The multiple basic records are referred to as a basic record set in this article.

[0054] System-generated fields

[0055] The fields JDVoid and VSTAG are fields automatically generated by the DBMS for any JDV object returned by the JSON duality view. Any object view schema of the JSON duality view is referred to in this text as including these fields. The JDVoid field stores the JDV object identifier, which is a unique identifier generated by the DBMS for the JDV object, used to uniquely identify the JDV object among other JDV objects returned by this JSON duality view and even JDV objects returned by other JSON duality views. If the corresponding JDV object identifiers in the JDVoid field match, the copies of the JDV object are referred to in this text as being the same. However, these copies may be different versions with different contents.

[0056] The DBMS generates the JDV object identifier based on the definition of the JSON duality view of the JDV object. JDDL1 includes an OBJECT ID clause that specifies one or more object ID attributes for the JDV object identifier. The one or more object ID attributes must include at least the primary key of the root table. JDDL1 includes the clause OBJECT ID(empno), thus defining empno as the object ID attribute for the JDV object identifier generated for employee_jdv1.

[0057] The value of the object ID attribute does not become part of the JDV object identifier; rather, the object ID attribute value is the input used to generate the JDV object identifier. The data type domain of the object ID attribute includes many data types. For example, the primary key can be an integer, a real number, or a string character. On the other hand, the JDV object identifiers generated for any JDV object from any JSON duality view have the same data type. The JVD object identifier has a data type that enables efficient comparison of JDV object identifiers to determine equality. Equality is determined without the need for data type conversion, which may be required if the primary key is directly used as the JDV object identifier. If the corresponding JDV object identifiers of the JDV objects match, the JDV objects are logically versions of the same JDV object, although their contents may be different due to the JDV objects having different field values. In addition, the object ID attribute value is encoded within the JDV object identifier in such a way that the original input object ID attribute value can be generated from the encoding. The encoding can also include information such as the data type and length of the object ID attribute.

[0058] Version identifier

[0059] The field VSTAG is a version signature field that contains a version signature. The version signature is unique for each version of a particular JDV object. As will be explained in more detail, the version signature is generated using the base property values as input to a hash-based algorithm (much like the algorithm used for digital signatures). As the base property values of the JDV object change, the JDV object changes and transitions from one version to another. Each version produces a different version signature because the base property values have changed.

[0060] The version identifier can be used to determine whether a previously retrieved JDV object has changed. If the version identifier of a previously retrieved JDV object returned by the JSON duality view does not match the version currently returned by the JSON duality view, then any underlying base property values have changed.

[0061] According to an embodiment, a DBMS implementation can be used to generate functions for JDV object identifiers and version signatures ("version signature functions"). These functions can be used in database statement rewrites that reference the JSON duality view.

[0062] Reference Figure 2A , the database statement JQ21 is a SELECT statement that projects the JDV objects to be returned from employee_jdv1. JQ21 is rewritten as the database statement JQ21' to include a json_object() operator expression. The field mapping of this json_object() operator expression maps JSON fields to the base properties of the root table employee.

[0063] In addition, the field mapping maps the fields JVDOID and VSTAG to function calls for generating JDV object identifiers and version signatures. JVDOID is mapped to OID_FROM_PK(empno). Oid_from_pk() is a JDV object ID function that takes one or more JDV object identifier properties (one of which should be the primary key) as input. In the case of JQ21', the primary key empno is the only JDV object identifier property defined for employee_jdv1.

[0064] VSTAG is mapped to object_sig(record_sig(empno,ename,job,sal)). Obj_sig() and record_sig() are version signature functions. Record_sig() takes one or more input base properties as input parameters. In JQ21', the input properties include all the base properties mapped by the field mapping of the json_object() expression. The version signature functions will be described in more detail later.

[0065] Finally, a primary key can include multiple attributes, and when the primary key includes multiple attributes, the value of the primary key as referred to in this document collectively refers to the values of the multiple attributes.

[0066] Query by specifying JDVoid

[0067] The JDV object ID can be used to identify a JDV object in a database statement. The database statement can be rewritten to use the primary key of the root table to identify the root record in the corresponding root table. To facilitate the use of the primary key in this way, the DBMS implements object ID conversion functions, which are functions that convert the JDV object identifier encoding of the object ID attribute value into the object ID attribute value. The converted value can be referenced by a predicate expression. If the object ID attribute value is the primary key value on the object ID attribute, the converted value can be referenced by a predicate expression on the primary key to select the corresponding JDV object by selecting the record of the JDV object in the root table.

[0068] Figure 2A Shows the rewrite of the database statement JQ22, which specifies to return a JDV object with a specific JDV object ID. The database statement JQ22 is similar to JQ21, except that JQ22 includes a predicate on the JVDOID field.

[0069] JQ22 is rewritten as JQ22'. It includes the object ID conversion function call EXTRACT_PK_COL("56ADEEBD") in an equality predicate that compares the return value of the function with the object ID attribute empno. The DBMS generates the predicate in this way in response to determining that empno is the primary key (the primary key is the object ID attribute defined by the JSON duality view employee_jdv1).

[0070] The JSON duality view can specify multiple object ID attributes. According to an embodiment, the JDV object ID is generated as the concatenation of the corresponding encodings of each value of the multiple object ID attributes. The object ID conversion function includes a parameter that specifies which object ID attribute value to return.

[0071] For example, the table employee has a primary key that includes multiple attributes (i.e., empno and emptyp). Therefore, the object ID clause of employee_jdv1 is OBJECT ID(empno,emptyp).

[0072] The predicate in the database statement rewritten from JQ22 will include a predicate with two equality predicate conditions, one equality predicate condition for each object ID attribute of the primary key, as follows:

[0073] WHERE EXTRACT_PK_COL(“56ADEEBD”,1) = empno and

[0074] EXTRACT_PK_COL(“56ADEEBD”,2) = emptyp

[0075] The second parameter of extract_pk_col() is the following number, which identifies the object ID attribute to be returned based on the positioning of the attribute in the object ID clause of the corresponding JSON duality view.

[0076] The rewrite from JQ22 to JQ22’ is an example of avoiding the evaluation of functions on predicate expressions that reference object view mode fields of JSON duality views. Under function evaluation, each JDV object returned from the JSON duality view is materialized into an in-memory representation that can be evaluated to determine if a field satisfies a predicate expression, such as the predicate expression in JQA below.

[0077] JQA = SELECT obj FROM employee_jdv1

[0078] WHERE obj.job = “Manager”

[0079] Under function evaluation, each JDV object returned from the JSON duality view employee_jdv1 is materialized into a navigable in-memory representation that can be evaluated by the function to determine if the field job is equal to “Manager”.

[0080] To avoid function evaluation for evaluating predicate expressions, JQ* can be rewritten to replace the predicate expression on the field job with a predicate expression on the underlying attribute of job, similar to what was described for JQ22 and JQ22’. The execution of the rewritten statement by the DBMS avoids function evaluation on the predicate expression on the field job of the JDV objects in the JSON duality view employee_jdv1, thereby improving the execution speed and reducing the consumption of computer resources by the DBMS for evaluating JQ*.

[0081] For illustrative purposes, a JDV object is referred to herein as being returned by the JSON duality view when a database command can be issued to the DBMS to request a JDV object from the JSON duality view and the JDV object is returned as a result of the database command or otherwise generated at execution. JQ22 is an example of such a database statement. Even if the JDV object is a virtual object, the JDV object is referred to as being from, in, or contained by the JSON duality view if the JDV object can be returned by the JSON duality view.

[0082] According to an embodiment, the JDV object returned by the JSON duality view has a JSON data type native to the DBMS. The JDV object stored in memory can have an OSON (Oracle TM JSON) data type storage format. The OSON format encodes strings and other forms of values according to a local dictionary stored in the JSON object and includes an encoded map and offsets representing the hierarchical relationships between the fields of the JSON object. This format not only compresses JSON documents, but also enables efficient navigation of JSON objects to facilitate evaluation of path expressions against JSON objects. Since OSON provides these two advantages, it is used in byte-addressable memory and block-based memory.

[0083] Cardinality relationship

[0084] As already mentioned, the JSON duality view can map fields to attributes of base tables other than the root table of the JSON duality view. Another table may be the base table for a subfield of an object field or an array field. Constructing a JSON duality view definition for a multi-table JSON duality view depends on the cardinality relationships between a parent field and its corresponding subfields and between the root table and one or more base tables.

[0085] A JSON object has intra-object cardinality relationships. A cardinality relationship can exist between a JSON object and any of its subfields, between an object field and any of its subfields, or between an array field and any of its subfields. For a subfield of a JSON object, an object field, or an array field, the JSON object, the object field, or the array field can be referred to herein as the parent of the subfield. A parent of an object field or an array field can be referred to herein as a parent field.

[0086] There are two types of in-object cardinality relationships between a parent and its child fields. When the parent field is an array field, the parent and its child fields have a 1:N (one-to-one or many) cardinality relationship. Multiple instances of the child field may exist, with one instance for each element of the array field. When the parent field is an object field, the parent and the child fields of the object field have a 1:1 (one-to-one) cardinality relationship.

[0087] In an embodiment, a multi-table JSON duality view is defined based on a view query expression that includes a subquery expression within an outer query expression. The outer query expression references a parent table, and the subquery expression references a child base table of the parent table. The parent table and the child base table have a 1:1 or 1:N cardinality relationship. The parent table and the child base table may be referred to as correlated herein, and the child base table referenced in the subquery expression may be referred to as a correlated subquery table herein.

[0088] The 1:1 or 1:N cardinality relationship between the parent table and the child table of the JSON duality view corresponds to a primary-foreign key relationship defined in the database dictionary of the DBMS. Such a primary-foreign key relationship is referred to as a registered primary-foreign key relationship herein. The registered primary-foreign key relationship specifies the primary key in one table and the foreign key in another table.

[0089] When a single record in the parent table corresponds to one or more records in the child table, the parent table and the child table have a 1:N cardinality relationship. The 1:N cardinality relationship applies to array fields; the child fields of the array field have base attributes that are attributes of the child table. The 1:N cardinality relationship is based on a registered primary-foreign key relationship, where the parent table includes a primary key and the child table includes a corresponding foreign key. One record in the parent table with a specific primary key value corresponds to one or more records in the child table with equal foreign key values.

[0090] Regarding the parent table and the child table, the cardinality relationship may be referred to as a PK:FK relationship herein. The PK:FK relationship corresponds to a registered primary-foreign key relationship, where the parent table includes a primary key and the child table includes a foreign key.

[0091] When a record in the parent table corresponds to only one record in the child table, the parent table and the child table have a 1:1 cardinality relationship. The 1:1 cardinality relationship applies to object fields; the child fields of the object field have base attributes that are attributes of the child table. The 1:1 cardinality relationship is based on a registered primary-foreign key relationship, where the parent table has a foreign key and the child table has a corresponding primary key. One record in the parent table has a foreign key value that corresponds to only one record in the child table with a primary key value.

[0092] Regarding the parent table and the child table, the cardinality relationship can also be referred to as the FK:PK relationship in this article. The FK:PK relationship corresponds to the registered primary-foreign key relationship, where the parent table includes the foreign key and the child table includes the primary key. Generally, the child table can be a dimension table.

[0093] FK:PK Case - Object Field

[0094] Based on the FK:PK relationship between the root table and the child table, a JSON duality view can be constructed for the object view mode including the object field, where the sub-fields of the object field are mapped to the basic attributes of the child table. The child table is related to the root table through a subquery expression, which includes a field mapping in the json_object() operator expression. Figure 3 Such a JSON duality view is depicted.

[0095] Reference Figure 3 , JDDL3 is a DDL statement for defining and creating the JSON duality view employee_jdv3. Similar to JDDL1, the view query expression of JDDL3 maps the fields of the root table employee to JSON fields such as empno and ename.

[0096] In addition, the view query expression includes a subquery expression for the object field dept_info. Specifically, dept_info is mapped by the view query expression to the following subquery expression.

[0097] SELECT json_object('deptno' value deptno, 'dname' value dname, 'loc' value loc)

[0098] FROM dept

[0099] WHERE dept.deptno = e.deptno.

[0100] The sub-table dept is the correlated subquery table in the subquery expression. Based on the foreign key e.deptno of table employee and the primary key dept.deptno of table dept, table employee and sub-table dept have a FK:PK relationship. The subquery expression defines a scalar subquery because it should return at most one record from table dept. The sub-table dept is the correlated subquery table in the subquery expression because (1) table dept is in the FROM clause of the subquery expression, (2) the table dept attribute dept.deptno and the table employee attribute e.deptno are each operands in the subquery predicate expression dept.deptno = e.deptno, and table employee is in the FROM clause of the outer query expression that contains the subquery expression.

[0101] The subquery expression projects the JSON object returned by json_object(). The field mapping of the operator maps the fields of the object field dept_info to the basic attributes of the sub-table dept. Specifically, the fields deptno, dname, and loc are mapped to the basic attributes in dept with the same names, namely the attributes deptno, dname, and loc respectively.

[0102] The JDV object 301 has an example JDV object returned by employee_jdv3. In addition to including the sub-fields of the JDV object 101, the JDV object 301 also includes the JSON object dept_info as another sub-field of the JDV object 301. The JSON object dept_info includes the sub-fields mapped by the field mapping of the json_object() operator in the subquery expression of employee_jdv3, and these fields are deptno, dname, and loc.

[0103] The values of the sub-fields of the JDV object 301 are populated from the basic record of the root table employee with the value of the basic attribute deptno having 094. The attribute deptno is the foreign key of table employee corresponding to the primary key deptno in table dept. The registered primary-foreign key relationship is based on the primary key deptno of table dept and the foreign key deptno of table employee. In the JDV object 301, the field values of the sub-fields of the object field dept_info are populated from the following basic record in dept, whose primary key deptno value 094 was equal to the value of the foreign key deptno in table employee.

[0104] For reasons that will be explained, the JSON duality view definition must map fields to the primary key of any base table. However, it is not necessary to map the foreign key of the base table as a base attribute to a subfield.

[0105] PK:FK Case - Array Field

[0106] Based on the PK:FK relationship that the root table has with the related child table in the subquery expression, a JSON duality view can be constructed for an object view schema that includes an array field having subfields that map to base attributes of the child table. Figure 4 Such a JSON duality view is depicted that returns a JDV object having information from a department and a list of employees in that department.

[0107] Reference Figure 4 , JDDL4 is a DDL statement for defining and creating the JSON duality view deptvf. The view query expression of JDDL4 maps the fields deptno, type, dname, and loc to the attributes in dept having the same names (i.e., the attributes deptno, type, dname, and loc respectively).

[0108] In addition, the view query expression includes a subquery expression for the array field dept_info. Specifically, dept_info is mapped to the subquery expression

[0109] SELECT json_arrayagg(json_object('empno' value empno, 'ename' value ename, 'job' value job)

[0110] FROM employee e

[0111] WHERE d.deptno = e.deptno…

[0112] The table employee is the related child table in the subquery expression. Based on the primary key deptno of the table dept and the foreign key e.deptno of the table employee, the table dept has a PK:FK relationship with the table employee.

[0113] The json_arrayagg() operator is an aggregate operator that, when included in a query expression, returns a JSON array of the elements of each record that matches the predicate conditions in the WHERE clause of the query expression. The elements have the values specified by the input argument of this aggregate operator, which is an SQL expression. In the case of the above subquery expression, the SQL expression evaluates the JSON object returned by the json_object() operator. The field mapping of the json_object() operator expression maps the fields of the JSON object to the attributes of the related child table employee. Specifically, the fields empno, ename, and job are mapped to the base attributes in the table employee that have the same attribute names (i.e., empno, ename, and job, respectively).

[0114] The JDV object 401 is the JDV object returned by deptvf. The subfield values of deptno, dname, and loc are obtained from a single base record in the table dept that has the primary key value deptno = 094. The values of the elements of the subarray field emp_info are obtained from the table employee, which has a foreign key deptno corresponding to the primary key deptno in the parent table dept. The array emp_info has three elements, each of which contains the values from one of the three records in the table employee that have the foreign key attribute value deptno = 094. Each element includes an object field that has the fields empno, ename, and job, and the values of these fields are obtained from the corresponding one of the three records that have the same-named base attributes, respectively.

[0115] General model for JSON duality view implementation

[0116] According to an embodiment, the JSON duality view is constructed based on the PK:FK and FK:PK relationships of a table hierarchy that reflects the object hierarchy of the object view schema. The object hierarchy collectively represents the hierarchical relationship between the parent fields and the corresponding child fields of the object view schema, including the in-object cardinality relationship. The table hierarchy also reflects the JSON duality view table implementation of the corresponding object hierarchy. Figure 5 Depicts the object hierarchy and table hierarchy prototypes, which are the object hierarchy 501 and the table hierarchy 502.

[0117] The object hierarchy 501 represents the object hierarchy that may occur in the object view mode. The object hierarchy is similar to a directed graph, with at least one node at each level. The top level of the hierarchy includes a single root node O5R at the root level, which corresponds to the root object of the object view mode. The root node O5R contains the sub-fields of the root object. The nodes below the root level correspond to object fields or array fields. The sub-fields of the root node may be referred to herein as root sub-fields or root fields. For illustrative purposes, the object hierarchy 501 depicts a simple "single-path" hierarchy with only one node at each level.

[0118] The node O5L1 is a node at the next level L1 and is a child node of the root node O5R. The node O5L1 may be referred to herein as the child node O5L1 with respect to the root node O5R, and the root node O5R may be referred to herein as the parent of the node O5L1. The sub-fields of the node OL51 are the sub-fields of the corresponding array field or object field in the parent root node O5R. If the corresponding field is an array field, the parent root node O5R and the array field have a 1:N cardinality relationship with the child node OR5L1. If the corresponding field is an object field, the parent root node O5R and the object field have a 1:1 cardinality relationship with the child node OR5L1.

[0119] The node O5L2 is a node at the next level L2 and is a child node of the node O5L1. The node O5L2 may be referred to herein as the child node O5L2 with respect to the node O5L1, and the node O5L1 may be referred to herein as the parent of the child node O5L2. The sub-fields of the node OR5L2 are the sub-fields of the corresponding array or object field in the parent node O5L1. If the corresponding field is an array field, the parent node O5L1 and the array field have a 1:N relationship with the child node O5L2. If the corresponding field is an object field, the parent node O5L1 and the object field have a 1:1 relationship with the child node OR52.

[0120] The table hierarchy 502 represents the parent-child table arrangement of the object hierarchy of the JSON duality view. For a JSON duality view defined by a view query expression, one or more child tables are each the relevant sub-query tables in the corresponding sub-query expression. The table hierarchy includes the tables for each node in the object hierarchy. The table at a certain level corresponds to the node at that level in the object hierarchy; the table includes the basic attributes of the scalar sub-fields of the corresponding node in the object hierarchy.

[0121] The table hierarchy includes a root table at the root level and corresponds to the root node and root object of the object hierarchy. The table hierarchy 502 includes the root table T5R. The root table T5R corresponds to the root node O5R and is the base table for the scalar sub-fields of the root node O5R.

[0122] At the next level L1 is the base table T5L1. It includes the basic attributes of the corresponding node O5L1. In the JSON duality view defined by the view query expression, the base table T5L1 is referenced in the following sub-query expression, which is nested within the main outer-query expression of the view query expression that references the root table T5R related to the base table T5L1. The base table T5L1 is a sub-table of the root table T5R, which can also be referred to as the parent table of the sub-base table T5L1.

[0123] At the next level L2 is the base table T5L2. It includes the basic attributes of the scalar sub-fields of the corresponding node O5L2. In the JSON duality view defined by the view query expression, the base table T5L2 is referenced in the following sub-query expression, which is nested within the sub-query expression that references the root table T5L1 related to the base table T5L2. The base table T5L2 is a sub-table of the base table T5L1, which can also be referred to as the parent table of the sub-base table T5L2.

[0124] Based on the registered primary foreign key relationships, the tables in the table hierarchy have either a PK:FK or FK:PK relationship. If, within the object hierarchy, the in-object cardinality relationship between the parent and child nodes corresponding to the parent and child tables is 1:1, then the parent and child tables have an FK:PK relationship. If the in-object cardinality relationship between the parent and child nodes corresponding to the parent and child tables is 1:N, then the parent and child tables have a PK:FK relationship.

[0125] As previously mentioned, the object hierarchy 501 and the table hierarchy 502 are each depicted as a simple "single-path" hierarchy with only one node at each level. However, at any level below the root level, the object hierarchy and the table hierarchy may have multiple child nodes and tables. For example, in an object hierarchy with an object view pattern having a root array field and a root object field, the root node has two child nodes, one representing the array field and the other representing the object field. The table hierarchy includes base tables for each of the two child nodes; these base tables are sub-tables of the root table.

[0126] Example Three-Table JSON Duality View

[0127] The following describes example triple - table JSON duality views which, for illustrative purposes, implement an object hierarchy and a table hierarchy with a single path. As used herein, the term path is a sequence of nodes in one or more parent - child relationships as described above, with one node at each level. The single - path object hierarchy and table hierarchy can be represented using the path notation as shown below.

[0128] The following is Figure 3 the path notation of employee_jdv3 shown in

[0129] EMPLOYEE -> {DEPT}

[0130] The path notation refers to the tables in the table hierarchy starting from the root table in parent - child order. The arrows connect the parent table and the child table with a PK:FK or FK:PK relationship. The arrow is called a cardinality vector and points to the table with the primary key in that relationship.

[0131] The brackets surrounding the table name are used to specify that the table is the base table for a scalar sub - field contained in an array field. Thus, Figure 4 the JSON duality view deptvf described in

[0132] DEPT <- [EMPLOYEE]

[0133] Three tables, employee, project, and project_assignment, are used to illustrate the various JSON duality views that can be achieved by changing the root table. The example arrangements use different root tables among these three tables. The table project_assignment includes the foreign - key attributes empno and projectno, whose corresponding primary keys of the same name are in the employee and project tables respectively.

[0134] The first example is the view employee_view represented by the following notation. It returns a JDV object with information about the projects assigned to an employee.

[0135] EMPLOYEE <- [PROJECT_ASSIGNMENT] -> {PROJECT}

[0136] Figure 6 depicts a JDV object 601, which is an example of a JDV object returned by employee_view. The JDV object 601 includes scalar fields empno and ename, and an array field assigned_projects, as root sub-fields of the JDV object 601. Each element of the array field contains scalar sub-fields ass_eid and ass_projid, and an object field proj_info, as sub-fields. The object field proj_info includes scalar fields projid and projname, as sub-fields.

[0137] If the JSON duality view employee_view is defined by a view query expression, then the table employee is both the root table and the parent table related to the child table project_assignment. The related attribute is empno in the table employee and ass_eid in the table project_assignment. The base table of the scalar root sub-fields of the JDV object 601 is the table employee. The base table of the sub-scalar fields of the array field assigned_projects is the table project_assignment.

[0138] In the view query expression, the first outermost sub-query expression includes a second sub-query expression for the object field proj_info. Regarding the second sub-query expression, project_assignment is the parent table and is related to the child table project in the second sub-query expression. The related attribute is projno in the table project_assignment and projno in the table project. The base table of the sub-scalar fields of the object field proj_info is the table project.

[0139] Figure 6B Depicts an object hierarchy 611 and a table hierarchy 612 of employee_view with nodes at each of three levels. In the object hierarchy 611, the root node includes three sub-fields, the scalar fields empno and ename, and the array field assigned_projects. The array field assigned_projects is the parent of the child nodes at level L1. This node includes the sub-fields of assigned_projects, namely the scalar fields ass_eid and ass_projid, and the object field proj_info{}. The object field proj_info{} is the parent of the child nodes at level L2. This node includes the sub-fields of proj_info{}, namely projid and projname.

[0140] In the table hierarchy 612, the root table is the table employee, and the attributes empno and ename of the table employee are the base attributes of the scalar sub - root fields empno and ename. The child table of the root table at level 1 is the base table project_assignment and includes the base attributes ass_eid and ass_projid of the base table project_assignment. The base attributes ass_eid and ass_projid are the base attributes of the sub - scalar fields ass_eid and ass_projid of the array field assigned_projects.

[0141] The base table project_assignment is a child table of the root table employee. The related attributes are empno and ass_eid of the tables employee and project_assignment respectively.

[0142] The child nodes at level L2 include the base table project and the base attributes projid and projname of the base table project. The base attributes projid and projname are the base attributes of the sub - scalar fields projid and projname of the object field proj_info.

[0143] The related foreign keys do not have to be mapped to fields in the object view mode.

[0144] Example PROJECT_VIEW

[0145] The second example is the view project_view represented by the following notation. It returns a JDV object with information about projects and assigned employees.

[0146] PROJECT<-[EMPLOYEE_PROJECT_ASSIGNMENT->{EMPLOYEE}]

[0147] The JDV object 602 is an example of the JDV object returned by the JSON duality view project_View. The JDV object 602 includes the sub - scalar fields projid and projname and the array field assignment_employees, where the elements each contain the sub - scalar fields ass_eid and ass_proj_id (of the array field) and the sub - object field employee_info. The object field employee_info includes the scalar fields empno and ename.

[0148] In the JSON duality view project_view, the table project is both the root table and the parent table related to the child table project_assignment. The root table of the scalar root subfield of the JDV object 602 is the table project. The base table of the sub-scalar fields of the array field assignment_projects is the table project_assignment.

[0149] The table project_assignment is the parent table and is related to the child table employee. The corresponding related attributes are ass_eid in the table project_assignment and empno in the table employee. The base table of the sub-scalar fields of the object field employee_info is the table project.

[0150] Record set hierarchy

[0151] The base record set of a JDV object includes a hierarchy of record subsets, which is referred to herein as the base record set hierarchy. The base record set hierarchy mirrors the table hierarchy of the corresponding JSON duality view of the JDV object. The root record subset is at the root level of the hierarchy and includes one record from the root table. Each subsequent level in the record set hierarchy includes a record subset for each base table at the same level in the corresponding table hierarchy. The child base table corresponding to a "child" record subset has a cardinality relationship with the "parent" record set of the parent base table of that child base table.

[0152] Figure 7 Depicted is the base record set hierarchy 702 generated for the JDV object 601 of the JSON duality view employee_view. Since the table hierarchy 612 is the table hierarchy of employee_view, the base record set hierarchy 702 mirrors the table hierarchy 612 and is also depicted in Figure 8 as well.

[0153] Reference Figure 7 , the base record set hierarchy 702 includes the root record subset R70, the record subset L71, and the record subset L72. The root record subset R70 is at the root level and corresponds to the root table employee in the table hierarchy 612. The record subset L71 is at the first level L1 of the base record set hierarchy 702 and corresponds to the base table proj_assignment. The record subset L72 is at the second level L2 and corresponds to the base table proj. Mirroring the parent-child relationship of its corresponding base tables, the record subset L71 is the child of the root record subset R70 and the record subset L72 is the child of the record subset L71.

[0154] The root record subset R70 includes one record from the root table employee. The record subset L71 includes three records from the base table proj_assignment. The root record subset R70 and the record subset L71 have a PK:FK (“1:N”) relationship. Thus, for one root record in the record subset R70, the record subset L71 includes three records from the base table proj_assignment. The record subset L71 and the record subset L72 have a FK:PK (“1:1”) relationship. Thus, the record subset L72 includes three records from the base table project, one record for each record in the record subset L71.

[0155] Multi-table version signature

[0156] As previously mentioned, the JDV object is returned by executing a database statement that references the corresponding JSON duality view. During compilation, the database statement is rewritten based on the JSON duality view to access the data of the JDV object from the base attributes of the base record set of the JDV object.

[0157] According to an embodiment, a version signature is generated by rewriting the database statement to recursively call a version signature function within the database statement. The version signature function is recursively called by implementing a base version signature call that calls the version signature function as an input parameter, which in turn can call the version signature function as an input parameter. The version signature function returns a signature of a specific type generated for a specific type of input using a hashing algorithm. The input parameters of the version signature function include (1) a call to another base signature that returns a signature, (2) the base attributes of the base table, and (3) the object or array field name.

[0158] Figure 8 Illustrates the version signature function in an embodiment of the present invention. In particular, Figure 8 Illustrates the version signature functions record_sig(), aggr_sig(), obj_sig().

[0159] record_sig() returns the record signature of a record. record_sig() generates the record signature based on inputs that include one or more base attribute values of the record or one or more attribute input parameters.

[0160] object_sig() returns an object signature. There are several ways to call object_sig(). First, a call to object_sig() can have as input parameters one or more record signatures returned by record_sig() and / or an object signature returned by object_sig(). Second, object_sig() can include as input parameters the names of object or array fields and an object signature returned by object_sig().

[0161] aggr_sig() returns an array signature. aggr_sig() is used to generate the signature of an array field. The input to aggr_sig() is a set of signatures that includes an object signature returned by object_sig() and / or a record signature returned by record_sig(). Each signature in the set corresponds to an element of the array field.

[0162] The version signature of a JDV object is the object signature returned by a single base call to object_sig(), unless as described later. Typically, database statements are rewritten such that the parameters of a single base call to object_sig() are other calls to version signature functions, which can make additional calls to version signature functions in a similar fashion.

[0163] The calls are structured such that the calls form a call hierarchy that reflects the object hierarchy of the JSON duality view. The call hierarchy originates from the base object_sig() call. Below the level of the base signature function call, the call hierarchy includes one or more levels, where each level includes a set of version signature function calls corresponding to the nodes at the corresponding level in the object hierarchy; the one or more calls are input parameters to the version signature calls in the previous level within the call hierarchy. For each node at a particular level in the object hierarchy, at the corresponding level in the call hierarchy, there may be (1) a row_sig() call based on the scalar subfield's basic properties of that node, (2) an object_sig() call for each object_field() of that node, and (3) an array_sig() call for each array field of that node.

[0164] The goal of version signature generation is to generate a version signature in a deterministic manner. To be deterministic, the version signatures of instances of the same JDV object should match if the values of the basic attributes of the corresponding basic record set of the instance remain the same. Additionally, the basic records belonging to each record subset should remain the same. If any of the values of the basic attributes differ between the corresponding basic record sets, the version signatures should be different. In an embodiment, if the corresponding record subsets of an instance contain different basic records, the version signatures are different.

[0165] Illustrative Rewriting of the Version Signature Function

[0166] To illustrate how to generate a version signature through database statement rewriting using the version signature function, the following rewriting of the database statement JQB is described. For the definition of employee_jdv3, refer to Figure 3 . This statement requests the version signature of the JDV object returned by employee_jdv3.

[0167] JQB = SELECT VSTAG FROM employee_jdv3

[0168] Figure 9 Illustrates JQ9, the rewritten version of JQB constructed using the version signature function. Figure 9 Also depicted is the object hierarchy 901 (i.e., the table hierarchy of employee_jdv3) and the call hierarchy 902 (which represents the call hierarchy as represented by JQ9).

[0169] Refer to Figure 9 , the call hierarchy 902 includes a basic object_sig() call at the basic level of the call hierarchy 902. At the next level of the call hierarchy 902 corresponding to the root level of the table hierarchy 901 is a version signature function call that is an input parameter to the basic object_sig() call. These version signature functions include a record_sig() call that includes the basic attributes of the root table employee of the root scalar field as input parameters. Also included is an obj_sig() call for the object root field dept_info, for which the subtable dept is the basic table.

[0170] At the next level of the call hierarchy 901 (which corresponds to level L1 of the object hierarchy 901) is a call to record_sig(), which is an input parameter to the basic object_sig() call. The input parameters of record_sig() are the basic attributes of the subtable dept, which are the basic attributes of the scalar subfields of the object field dept_info.

[0171] In JQ9, the SELECT clause of the main outer query expression includes a basic object_sig() call and input version signature function calls corresponding to the root level of the object hierarchy 901, namely calls to record_sig() and obj_sig(). The subquery expression of JQ9 is the input parameter of the obj_sig() call in the main outer query expression. The subquery expression projects a recordsig() call corresponding to the lowest level of the table hierarchy.

[0172] Illustrative Rewriting of Array Fields

[0173] To illustrate how to generate a version signature through the rewriting of database statements for JDV objects with array fields, the following rewriting of the database statement JQC is described. The database statement requests the version signature of the JDV object 401 by specifying the JDV object identifier of the JDV object 401 in the predicate. For the definition of deptvf., refer to Figure 4 .

[0174] JQC = SELECT VSTAG from DEPTVF

[0175] WHERE JVDOID = "59XF45"

[0176] Figure 10 Illustrates JQ10, a rewritten version of JQC constructed using version signature functions. Also depicted is the object hierarchy 1001 and call hierarchy 1002 of the JSON duality view deptvf, which represents the call hierarchy for the basic signature represented by JQ10.

[0177] Refer to Figure 10 , the call hierarchy 1002 includes a basic object_sig() call at the top level of the call hierarchy. The next level of the call hierarchy 1002 corresponds to the root level of the object hierarchy 1001. This level includes a record_sig() call that has input parameters that are the basic attributes of the root table dept. This level also includes an obj_sig() call with array_sig() as an input parameter. This obj_sig() call is a wrapper for array_sig() to include the field name of the corresponding array field emp_info. This field name is used to calculate the object signature, as will be explained later.

[0178] array_sig() generates a signature array from the basic records from employee, which respectively correspond to the elements in the array field employee_info. The input parameter of the array_sig() call is the record_sig() call at the next level L1. The input parameter of record_sig() is the basic attributes of the employee table, that is, the basic attributes of the sub-fields of the array field emp_info.

[0179] In JQ10, the SELECT clause of the main outer query expression includes a version signature function corresponding to the root level of the object hierarchy 1001. The subquery expression of JQ10 is the input parameter of the obj_sig() call in the main outer query expression. The subquery expression projects the array_sig() call, which also corresponds to the root level of the object hierarchy 1001. The record_sig() call in the subquery expression corresponds to the level L1 of the object hierarchy 1001.

[0180] array_sig() behaves as an aggregate function, similar to how the json_arrayagg() operator behaves. When included in a query expression, array_sig() returns a signature generated from the record signatures generated for the corresponding table records that match the predicate conditions of the query expression. The record signatures are generated by calling record_sig() for each record in the record using the input parameters described previously.

[0181] The database statement JQC is an example of a database statement that can be used to generate the version signature of a JDV object by identifying the JDV object identifier in a predicate expression. In an embodiment, when the version signature is said to be generated by the DBMS for a JDV object, the DBMS executes a database statement such as JQC, which requests the version signature of the JDV object (e.g., the VSTAG field) and uses a predicate expression on the JDV object identifier (e.g., an equality comparison between the JVOID field and the JDV object identifier) to identify the JDV object. This database statement can be a previously compiled database statement, and the compilation involves rewriting the database statement using a version signature function, as shown in JQ10.

[0182] Sorting the input to the version signature function

[0183] According to an embodiment, record_sig() and object_sig() generate signatures by applying input parameter values in an order. If applied in a different order, the input parameter values generate different signatures for the same set of input parameters. To deterministically generate version signatures for multiple copies of a JDV object, the call hierarchy for each copy should apply the input parameters of record_sig() and object_sig() in the same order. Thus, the input parameters are applied according to an ordering scheme.

[0184] For record_sig(), the ordering scheme generates a record signature by applying the attribute values of the attribute parameters in the order of the attribute names. For object_sig(), if the input parameter is a call to record_sig(), the record signature returned by that call is applied first, and then the object signature returned by calling the input parameters of obj_sig() or array_sig() is applied. object_sig() can only accept one call to record_sig() as an input parameter.

[0185] If an object_sig() call includes multiple input calls to object_sig(), each object_sig() input call itself should include an input parameter that specifies the field name of the object field or array field corresponding to the object_sig() call. For example, the following object_sig() calls are used to represent a JSON duality view of a person and their delivery and billing addresses, which are represented by object fields.

[0186] obj_sig(record_sig(fname,lname),

[0187] obj_sig(“del_addr”,record_sig(…),

[0188] obj_sig(“bill_addr”,record_sig(…))

[0189] The calling obj_sig() call first applies the record signature of record_sig(), then the object signature returned for the object field bill_addrr, and then the object signature returned for del_addr.

[0190] If an object_sig() call includes more than one object_sig() and array_sig() input call, any array_sig() input call shall be wrapped in the calling obj_sig() that designates the field name of the corresponding array field. For example, the following object sig() calls are used to represent a JSON duality view of a person, their current address, and previous addresses. The current address is represented by an object field, and each of the previous addresses is represented by an array of address fields.

[0191] obj_sig(record_sig(fname,lname),

[0192] obj_sig(“cur_address”,record_sig(…),

[0193] obj_sig(“prev_addresses”,array_sig(record_sig(…)))

[0194] The array_sig() call applies to a set of record signatures generated for the base records from the corresponding base table. For multiple copies of the same JDV object returned by the JSON duality view, the same set of record signatures may be generated, but in a different order. If the same set of signatures is generated but in a different order, the underlying base properties of the base records from which the record signatures are generated may not have changed. To reflect this fact, arr_sig() is configured to generate array signatures “commutatively”, i.e., to generate an array signature for a set of signatures such that the order of the signature set is independent of the resulting version signature.

[0195] Finally, not all base properties are used as inputs to the record signature function. The JSON duality view can define base properties to be excluded from version signature generation. For a JSON duality view, the set of base properties used as inputs to the version signature function can be referred to as the version signature schema with respect to the JSON duality view. The base properties to be excluded can be defined by annotating the view query expression to indicate that these base properties are to be excluded from the version signature schema, similar to the way in which change permissions can be declared for base properties, as described later.

[0196] Illustrative rewrite of the entire JDV object with VSTAG

[0197] The generation of version signatures by rewriting database statements using version signature functions has been illustrated by rewriting database statements that request only the VSTAG field. As previously mentioned, the JDV objects returned from the JSON duality view automatically include a VSTAG field that contains the version signature.Figure 11 Depicts a database statement that is rewritten to return the entire JDV object with the VSTAG field in a multi-table JDV object.

[0198] Reference Figure 11 , the database statement JQ11 requests a JDV object from the JSON duality view deptvf (see Figure 4 ). Based on the definition of deptvf, the DBMS rewrites the database statement JQ11 as the database statement JQ11'.

[0199] Segment 1102 is the part of JQ11' that is constructed to generate a version signature for the JDV object returned by the JSON duality view deptvf. Segment 1102 maps the field VSTAG to a basic object_sig() call. Since no field names are required, the call to array_sig() is not wrapped inside the call to object_sig().

[0200] The basic obj_sig() call is wrapped inside a call to snapshot_sig. snapshot_sig() appends the snapshot time to the version signature. snapshot_sig() is optional. The snapshot time enables the use of snapshot queries associated with the snapshot time to regenerate the version signature. The snapshot query is calculated to reflect the database state at the snapshot time associated with the query.

[0201] Pessimistic locking

[0202] According to an embodiment, pessimistic concurrency control is used to provide transactional consistency for updating a JDV object. Under pessimistic concurrency control, before a database transaction modifies any part of a JDV object, the database transaction issues record locks on all the basic records in the underlying record set to prevent modification of any of the basic records that may need to be changed in that set. Issuing a lock in this way before attempting to modify any basic record of a JDV object is referred to herein as pessimistic locking of the JDV object.

[0203] A database transaction that has a lock on a record prevents other database transactions from obtaining a lock on that record, thereby preventing any of the other database transactions from also modifying the record until the database transaction releases the lock. When a database transaction terminates by commit or abort, the database transaction releases the record lock.

[0204] Pessimistic concurrency control is used to execute a database transaction in response to receiving a select-for-update database statement. The following database statement JQD is a select-for-update statement issued by a database transaction to the DBMS100 to pessimistically lock the JDV object 601, the base record set of which is the base record set hierarchy 702.

[0205] JQD = SELECT obj FROM employee_view WHERE obj.empno = 34 FOR UPDATE

[0206] Holding a record lock on the base record set itself is not sufficient to prevent other database transactions from changing the JDV object while those locks are held. Without other preventive measures, it is possible for a database transaction to be able to insert a record into a child base table of the JDV object, where the inserted record will belong to the child record set corresponding to the child base table, and where the related foreign key value of the child base table is equal to the foreign key value of the child record set. For example, while a database transaction has locked all the base records in the record subset L71, another database transaction can add another record to proj_assignment, where the related foreign key ass_eid = 34, thereby adding another base record to the record subset L71 and modifying the content of the JDV object 601.

[0207] To prevent a record with a related foreign key value from being inserted into a child base table of the JDV object (which may change the content of the JDV object) while the database transaction pessimistically locks the current base record, the database transaction issues a foreign key value lock on the child base table. The foreign key value lock is issued on any related foreign keys of the child table that are related to the primary key of the parent table.

[0208] An example of a mechanism for foreign key value locking is a foreign key index lock. A foreign key index is an index on the foreign key of a registered primary foreign key relationship. The foreign key value locks the index entry for that foreign key value, thereby preventing the insertion or deletion of a record with that foreign key value or the update of the foreign key in the record to or from that foreign key value.

[0209] Consistent read and current read

[0210] There are various capabilities that are important for optimistic serializable updates. These are (1) consistent read mode and (2) current read mode, (3) object disassembly, and (4) delta record set generation. Consistent read and current read are modes for executing database commands and database operations related to transaction consistency.

[0211] Object disassembly is actually the reverse engineering of JDV objects into logical base record sets. In other words, object disassembly extracts and derives base record sets from JDV objects. The base record sets are referred to as derived record sets in this document. Delta record set generation compares the derived record sets with the base record sets generated from the database for JDV objects to determine the differences between the two. This difference is represented by the delta record set. Since these capabilities are important for optimistic serializable updates, a description of these capabilities is provided in this document before describing optimistic serializable updates.

[0212] In the consistent read mode, the reading of database data during the execution of one or more database statements of a database transaction returns data that is consistent with the SCN (system change number) associated with that database transaction. The SCN is referred to as the snapshot time in this document. Transaction consistency requires that the reads only reflect transactions that have been committed and confirmed before the SCN, as well as any uncommitted database changes made by the database transaction.

[0213] In transaction processing in a DBMS, the changes made by a database transaction to a record require changes to the data blocks that store the data for that record. These changes are recorded in a change record, which may include redo records and undo records. Redo records can be used to reapply the changes made by the database transaction to the data blocks. Undo records are used to reverse or undo the changes made by the transaction to the data blocks.

[0214] Undo records are used to provide transaction consistency by performing an operation referred to as a "consistent read operation" in this document. Each undo record is associated with an SCN. For the data blocks whose data is being read by a database statement, the DBMS applies the required undo records to a copy of the data block to bring the copy to a state consistent with the snapshot time of the database statement. The DBMS determines which undo records to apply to the data block based on the corresponding SCN associated with the undo records. The term consistent read operation refers to an operation performed on database data to ensure that the data is consistent with the snapshot time. Such an operation includes applying undo records to the data block, as previously described. Another example of a consistent read operation is to examine the transaction metadata in the data block to determine whether the data in the data block is transactionally consistent with the SCN time. Applying undo records to the data block can be performed in response to determining that the data block is not transactionally consistent with the snapshot time.

[0215] In the current read mode, the read operation reflects changes to the data block that are not committed. Consistent read operations are not performed. For example, the data block contains uncommitted records in a table with a specific foreign key value. The uncommitted records are inserted by a database transaction that has not been committed. Before the database transaction is committed, another database transaction operating in the current mode reads the data block that contains the uncommitted records. The database transaction is executing a query that filters records based on the foreign key value. When the database transaction reads the data block, the consistent read operation is waived, and thus, the database transaction reads the uncommitted records. If the foreign key is indexed by a foreign key value index, the database transaction can also read the data block of the index entry that stores the record in the foreign key value index.

[0216] Object Disassembly

[0217] Object disassembly extracts a derived record set from a JDV object based on the mapping of the basic properties of the corresponding JSON duality view and the table. In fact, the derived record set is generated according to the corresponding object hierarchy and table hierarchy of the JSON duality view.

[0218] Like the basic record set, the derived record set consists of a hierarchy of subsets of records with parent-child relationships, as previously described for the basic record set hierarchy. For each level in the table hierarchy and each basic table at that level, at least one derived record is generated that has the basic properties of the basic table with object view mode fields corresponding to the basic table; the basic properties have scalar values corresponding to the corresponding fields.

[0219] The derived record includes the basic properties required by the JSON duality view. Therefore, the derived record may not include all the properties of the corresponding basic table. Therefore, the derived record is referred to as skeletal in this article.

[0220] If the basic table corresponds to an array field, the derived record subset includes derived records for each element in the array field, and the derived records have basic properties corresponding to the corresponding scalar subfields of the array field. For each element, the corresponding derived record includes the scalar field values of the corresponding basic properties.

[0221] If at a certain level, the basic table is a child table of a parent that is the basic table of an array field, the basic table corresponds to a sub-object field of the array field. The derived record subset of the child table includes derived records for each element, and the derived records have basic properties from the child table that correspond to the corresponding scalar subfields of the array field.

[0222] Incremental Record Set Generation

[0223] Incremental record set generation generates an incremental record set including one or more delta tuples by comparing a derived record set of a JDV object with a base record set generated for the JDV object from a database. A derived record in the derived record set can be a version of the same record in the base record set; a record in the derived record and a base record in the base record set are referred to herein as counterparts or collectively as counterpart sets. A delta tuple and a base record belonging to a counterpart set contain the same primary key value.

[0224] As previously mentioned, the JSON duality view must map JSON fields to any primary key of any base table of the JSON duality view. Thus, a derived record has the same primary key value as its counterpart base record. Counterpart records are identified by looking up derived records and base records with the same primary key value. Logically, counterpart records are the same record but may be different versions of that record.

[0225] A delta tuple represents (1) a set of changes or deltas between records in a counterpart set, (2) a base record deleted from the base record set or a base record inserted into the base record set. A delta tuple includes base attributes and values and is marked to specify the change (if any) represented by the delta tuple.

[0226] For each counterpart set, a delta tuple is generated and filled with the same base attribute values as the corresponding derived record. Then the base attribute values in the delta tuple are compared with the base attribute values in the corresponding base record of the counterpart set. If the base attribute values in the delta tuple do not match the corresponding base attribute values in the corresponding base record, the base attributes in the delta tuple are marked as changed, and the delta tuple is also marked as changed. If there are no differences between the corresponding base attributes, the delta tuple is marked as unchanged.

[0227] If a derived record has no counterpart, a delta tuple is generated that represents an insertion of the base record into the corresponding base table of the derived record. The delta tuple is marked as "added" and filled with the same base attribute values as the base attribute values of the derived record.

[0228] If a base record has no counterpart, a delta tuple is generated that represents the deletion of the base record from its base table. The delta tuple is marked as "deleted" and filled with the same attribute values as the attribute values of the base record.

[0229] A derived record set may contain duplicate derived records of the same base record. In this case, the counterpart set includes multiple derived records and the base record. For each set of duplicates, only one delta tuple is generated, which combines the changes represented by the duplicates. For example, if one derived record indicates a change to one attribute and another derived record indicates a change to another attribute, a single delta tuple specifying the changes to both attributes is generated. If the derived records in the counterpart set indicate conflicting changes (e.g., different changes to the same base attribute), an error is generated.

[0230] Optimistic Serialization Update

[0231] Figure 12 is a flowchart depicting the process of optimistic serialization update using JDV objects. The optimistic serialization update can be executed in response to an UPDATE statement as follows, which uses the JDV object ID to identify the JDV object to be updated and identifies the modified version of the JDV object to which the JDV object is to be set:

[0232] UPDATE deptvf SET object=myJDVO

[0233] WHERE object.JVDOID=myJDVO.JVDOID

[0234] Compiling and executing the UPDATE statement requires more execution steps than Figure 12 depicted. Figure 12 describes the steps related to implementing the optimistic serialization update. The update statement is executed by a database transaction with a snapshot SCN (referred to herein as the current snapshot SCN). Except as described below, the database transaction is executed in a consistent read mode.

[0235] Refer to Figure 12 to generate the current version of the JDV object. The base record set generated for the JDV object is stored as the current base record set (1205).

[0236] Next, the corresponding VSTAG (1210) of the updated JDV object and the current version is compared. If the VSTAGs match, the DBMS determines that the modified JDV object does not represent an update to the current version of the JDV object. The process terminates. This step is referred to herein as the pre-update version check.

[0237] If it is determined that the VSTAGs match, object disassembling is performed to generate a derived record set (1215). Next, a delta record set (1220) is generated based on the derived record set and the current base record.

[0238] Next, perform an "incremental change operation". The incremental change operation only changes the base records corresponding to the incremental tuples that represent modifications, deletions, or additions to the base records of the base table representing the JSON duality view. In an embodiment, this step requires determining for each incremental tuple whether the incremental tuple represents a change to a base record, and if so, generating a change operation for that change. The change operation can be generated by issuing a DML command that effects the change. The primary key value in the incremental tuple is used to identify the base record, and an UPDATE command is issued for the incremental tuples representing changes to the base records; the UPDATE command only updates the attributes marked as changed in the incremental tuple. An INSERT command is issued for the incremental tuples marked as added; the statement specifies the attribute values specified in the incremental tuple.

[0239] The incremental change operations are sorted and checked to ensure that the primary-foreign key relationships and the constraints imposed by the JSON duality view definition are not violated, as will be described in further detail later.

[0240] Issuing UPDATE and DELETE statements against base record locks causes the DBMS to issue a lock on that record. There may typically be many base records in the current base record set for which no DML command is generated at this step. The effect of not locking all base records is that fewer records are locked. Fewer record locks reduce lock contention and rework of database transactions, thus improving the system performance of the DBMS. When compared with pessimistic locking, the system performance improvement from reduced lock contention is even greater. Not only are fewer records locked, but the time period for which the locks are issued is also much shorter.

[0241] Next, for any PK:FK relationships in the table hierarchy of the JSON duality view, issue a foreign key value lock (1225).

[0242] Next, perform "post change validation". Post change validation ensures that when the change operation is committed to confirm the change, the changes made to the base records by the incremental change operation can be executed in a transaction serial manner. In essence, this guarantee is provided by determining whether there are uncommitted confirmed changes to any of the base records of the JDV object when the post change validation is executed.

[0243] In the consistent read mode, generate a version signature (1235) for the JDV object. In the current read mode, generate a version signature (1240). Next, compare the version signature pair (1245).

[0244] If the paired version signatures match, no other database transaction is changing the base record of the JDV object or inserting or deleting a base record that changes the content of the JDV object. In this case, the only base record changes affecting the content of the JDV object are the uncommitted and confirmed base record changes made by the current database transaction. Therefore, the changes to the JDV object have been made serial.

[0245] In response to determining that the version signature pair matches, the database transaction is committed and confirmed (1250). Otherwise, the database transaction is terminated.

[0246] An update to a JDV object that fails the pre-update version check will also fail the post-change verification. In this regard, the pre-update version check is redundant. However, the pre-update version check effectively disqualifies the update without having to trigger the additional processing that occurs after the pre-update version check (such as performing an incremental change operation and accompanying record locking), thereby improving system performance.

[0247] Optimistic serialization updates can be performed on multiple JDV objects at the database statement level or at the database transaction level. At the database statement level, an incremental record set is generated and an incremental change operation is performed on each JDV object. Then, post-change verification is performed on each of the JDV objects. If the version signatures generated in the consistent read mode and the current read mode match for each JDV object, the database statement is committed and confirmed.

[0248] At the database transaction level, multiple database statements can be executed and multiple JDV objects can be changed by generating the corresponding incremental record sets and performing the corresponding incremental change operations. In response to receiving a request to commit and confirm the database transaction, post-change verification is performed on each of the JDV objects. If the version signatures generated in the consistent read mode and the current read mode match for each JDV object, the database statement is committed and confirmed.

[0249] Post-change checking can be applied to the optimistic updates of various record sets found in various DBMSs. Various record sets include, for example, record sets that include one or more rows, one or more XML documents, or one or more JSON objects. Any implementation of post-change checking requires the ability to generate record sets in a consistent read mode and a current read mode, as well as techniques for deterministically detecting differences between two record sets, including the use of deterministically generating version signatures based on version signature patterns.

[0250] Change operation verification and change operation ordering

[0251] Change operation verification refers to checking whether the change operations used to update a JDV object violate the constraints imposed by the corresponding JSON duality view. Such constraints include predicate constraints and change permission constraints. Change operation verification can be performed after incremental record set generation and before post-change verification.

[0252] Predicate constraints can be defined by predicate expressions that are irrelevant predicate expressions in the view query expression. Such predicate expressions are referred to as filter predicate expressions in this article. The change operation will be checked against the predicate constraint, and if the change operation violates the predicate constraint, it may result in a predicate constraint error. For example, the view query expression of the JSON duality view deptvf (see Figure 4 ) includes the following filter predicate expression in the subquery:

[0253] e.job <> “Manager”

[0254] The update of JDV object 401 (see Figure 4 ) that changes the field job from “SMITH” to “MANAGER” will generate an incremental tuple specifying the updated value of the attribute job as “MANAGER”. During change operation verification, the DBMS determines that the attribute value in the incremental tuple does not satisfy the filter predicate expression e.job <> “Manager”, thus detecting a predicate constraint violation and preventing the update.

[0255] Another type of constraint is the change permission constraint defined by the JSON duality view. As will be explained later in this article, the JSON duality view can declare change permissions for basic attributes or basic tables. For example, the change permission declared by deptvf can prohibit the update of the basic attribute job. The modification of JDV object 401 that changes the field job from “SMITH” to “SENIOR ACCOUNTANT” generates an incremental tuple specifying the updated value of the basic attribute job as “SENIOR ACCOUNTANT” and marking the basic attribute as changed. During change operation verification, the DBMS determines that the basic attribute for which the change permission defined by deptvf is NOT UPDATABLE has been changed, thus detecting a change permission violation and preventing the update.

[0256] Another type of constraint is the mapped field constraint. If the updated JDV object includes fields that are not mapped by the corresponding JSON duality view, a mapped field constraint violation is generated.

[0257] Sort the change operations to satisfy FK-PK dependencies. Specifically, a record with a foreign key value has an FK-PK dependency on a record with a primary key value. If the record with the primary key value has been inserted into the database, the dependency is satisfied; otherwise, the dependency is not satisfied. If an incremental change operation inserts a specific base record and another incremental change operation inserts another base record that has an FK-PK dependency on the specific base record, the change operation for the specific base record is executed before the incremental change operation for the other base record is executed.

[0258] Change permission statement

[0259] A JSON duality view can define change permissions by declaring change permissions in the view query expression of the JSON duality view. Change permissions can be declared by annotating the view query expression with a change permission statement. For illustration, Figure 13A and Figure 13B depicts versions of DDL statements JDDL3 and JDDL4 annotated with change statements. In Figure 13A and Figure 13B , DDL statements JDDL3’ and JDDL4’ define JSON duality views employee_jdv3 and deptvf respectively.

[0260] Refer to Figure 13A , JDDL3’ includes various change permission annotations as follows:

[0261] 1. In the clause FROM dept WITH(UPDATE), the WITH(UPDATE) annotation declares that the base attributes of table employee are updatable.

[0262] 2. In the clause FROM dept WITH(UPDATE), the WITH(UPDATE) annotation declares that the base attributes of table dept are updatable.

[0263] 3. In the clause'sal' value sal WITH(NOUPDATE), the WITH(NOUPDATE) annotation declares that the base attribute sal of table employee is not updatable. Thus, change permission statements can generally be made at the base table level for base attributes, but can be overridden for specific base attributes at the attribute-level annotation.

[0264] Refer to Figure 13B ', JDDL4’ includes the following change permission declarations:

[0265] In the clause FROM employee e WITH(DELETE,INSERT,UPDATE), the WITH annotation declares that the base properties of table employee are updatable and that the base records of table employee can be deleted or inserted. By default, the base records of a child table do not have change permission.

[0266] Unnesting and nesting

[0267] The FK:PK relationship between a parent table and a child table has been described as being used to define object fields, corresponding child fields, and base properties. According to an embodiment, the base properties in the child table can be used for fields that are not included in the object fields and that are actually "unnested" to a higher level in the object hierarchy. Specifically, the base properties in the child table are mapped to fields at the object hierarchy level corresponding to the parent table of the child table. Such unnesting is effected by using the UNNESTING clause in the view query expression of the JSON duality view. The base properties mapped in this way are referred to herein as unnested base properties; the fields mapped to the unnested base properties are referred to herein as unnested object field properties.

[0268] Figure 14 Depicted is the JSON duality view employee_jdv14 for which unnested base properties are defined. employee_jdv14 is similar to employee_jdv3, except that JDDL14 includes a UNNEST clause that qualifies the relevant subquery expression, the effect of which is to declare the projection properties of the subquery as unnested base properties of fields at the object hierarchy level of the parent table employee.

[0269] JDO1401 is the JDV object returned by employee_jdv14. All fields in JDO1401 are at the same level in the object hierarchy, including the fields deptno, dname, and loc.

[0270] The base properties of a base table can also be "nested". That is, the base properties are mapped to child fields of an object field, where the child fields are at a lower level in the object hierarchy than the level of the base table. Such nesting is effected by using the json_object() operator expression that maps the base properties to the child fields of the object field. The base properties mapped in this way are referred to herein as nested base properties; the fields mapped to the nested base properties are referred to herein as nested base properties.

[0271] Simplified DDL syntax

[0272] The view query expressions of the JSON duality view can be very complex and very verbose to write and interpret manually. According to an embodiment, the DDL statement for defining a JSON duality view can be generated using a simplified syntax that refers to tables in a way that indicates the root table and base tables and basic annotations regarding object or array fields corresponding to the base tables. Based on the definitions of the referenced tables defined in the database dictionary and the registered PK:FK relationships, the DBMS generates metadata in the database dictionary that defines the JSON duality view. By default, the attributes in the referenced tables are mapped to fields according to the object hierarchy implied by the DDL statement. The DDL statement does not need to explicitly reference the attributes.

[0273] An example of the use of the simplified syntax is the DDL statement JDDL1’ (see Figure 1 ). The JSONIZE clause uses the table employee to declare the JSON duality view employee_jdv1. In response to the DBMS receiving JDDL2’, the DBMS creates the JSON duality view employee_jdv1, mapping the basic attributes of the table employee to the fields of the object view schema as shown in JDDL2.

[0274] DDL3’ is an example DDL statement for defining a multi-table JSON duality view with object fields (i.e., the JSON duality view employee_jdv3) using the simplified syntax. The expression in the JSONIZE clause is referred to herein as a simplified table object mapping and is shown below:

[0275] employee(‘dept_info’:{DEPT})

[0276] This mapping designates the table employee as the root table and the table dept as the child and base table of the object field of the root table employee, and designates the object field name, i.e., dept_info. The parentheses associated with the table employee specify that the table dept contained therein is a child table of the parent table employee. The square brackets indicate that the table dept is the base table of the object field with the specified field name (i.e., dept_info). This arrangement requires an FK:PK relationship between the table employee and the table department, which is supported by the registered primary foreign key relationship, where the primary key is the primary key of the table dept and the corresponding foreign key is the foreign key of the table employee. The DBMS checks its database dictionary to determine whether such a registered primary foreign key relationship exists, i.e., the registered primary foreign key relationship between the primary key deptno of the table dept and the foreign key deptno of the table employee.

[0277] In response to this determination, the DBMS determines that the attributes deptno of employee and deptno of table dept are relevant attributes for the JSON duality view employee_jdv3, and creates the JSON duality view employee_jdv3. This determination is made in the case where these attributes are not explicitly referenced as relevant attributes in JDDL3'.

[0278] In addition, the DBMS determines that the sub-fields of the object dept_info are the fields deptno and dname because these are the attribute names of table dept, and the DBMS also determines that these attributes are the base attributes of these sub-fields. In this way, the DBMS automatically defines the view employee_jdv3 in the case where these fields and attributes are not explicitly referenced in JDDL3'.

[0279] JDDL4' is an example DDL statement that defines a multi-table JSON duality view (i.e., the JSON duality view deptvf) with array fields using a simplified syntax. The JSONIZE clause contains the simplified table object mapping shown below:

[0280] dept('emp_info':[employee])

[0281] The mapping specifies that table dept is the root table and table employee is the child table of the root table employee and the base table of the array field, and specifies the name of the array field, i.e., emp_info. The parentheses associated with table dept specify that the included table employee is a child table of the parent table dept. The square brackets indicate that table employee is the base table of an array field with the specified name (i.e., 'emp_info'). This arrangement requires an FK:PK relationship between table dept and table employee, which is supported by a registered primary foreign key relationship, where the primary key is the primary key of table dept and the corresponding foreign key is the foreign key of table employee. The DBMS relies on its database dictionary to determine whether such a registered primary foreign key relationship exists, i.e., the registered primary foreign key relationship between the primary key deptno of table dept and the foreign key deptno of table employee.

[0282] In response to this determination, the DBMS determines that the attributes deptno of employee and deptno of table dept are relevant attributes for the JSON duality view deptvf, and creates the JSON duality view deptvf. This determination is made in the case where these attributes are not explicitly referenced as relevant attributes in JDDL3'.

[0283] In addition, the DBMS determines that the sub-fields of the array field emp_info are the fields empno, ename, and job because these are the attribute names of the table employe, and the DBMS also determines that these attributes are the base attributes of these sub-fields. In this way, the DBMS automatically defines the view employee_jdv3 without these fields and attributes being explicitly referenced in JDDL3'.

[0284] Figure 15A and Figure 15B shows the BNF (Backus-Naur Form) grammar of a simplified syntax of DDL statements for defining JSON duality views. This grammar is an example of a relational DBMS.

[0285] Simplified DDL Syntax - GRAPHQL

[0286] Another simplified syntax that can be used to define JSON duality views is GraphQL (for example, see GraphQL, October 2021 edition). The DDL statement identifies the root table and an object notation with a syntax similar to GraphQL. Using this object notation, the DDL statement defines the root object, root fields, and zero or more GraphQL object fields and corresponding sub-fields. The GraphQL object fields are associated with sub-tables. Based on the registered parent foreign key relationships between the sub-tables and the corresponding parent tables, the DBMS automatically determines whether the registered parent foreign key relationships correspond to the PK:FK relationships or FK:PK relationships of the parent tables and sub-tables. Based on this determination, the DBMS treats the GraphQL object fields as JSON object fields or array fields. Finally, similar to what was described previously, the sub-table of a specific GraphQL object field can also be the parent table of the sub-table of a nested GraphQL object field at the next level elsewhere in the object hierarchy.

[0287] Figure 16 Depicts the DDL statement JDDL-GQ for defining the JSON duality view deptvfq using GraphQL. The JSON duality view deptvfq is similar to deptvf. The clause FROM dept identifies the table dept as the root table.

[0288] Using square brackets for object notation, JDDL-GQ maps scalar root fields to the attribute names of the root table dept. For example, the JSON field DeptNumber is mapped to the attribute deptno. Using object notation, JDDL-GQ also maps the GraphQL object field emp_info to the child table of the root table dept, namely the table employee. Based on the registered primary-foreign key relationship (where the table dept has the primary key deptno and the table employee has the foreign key deptno), the DBMS determines that this relationship corresponds to a PK:FK relationship between the tables, so Emp_info is an array field. JDDL-GQ maps the sub-fields EmpNum and EmpName of the array field to the basic attributes empno and ename of the table employee.

[0289] View the object view mode

[0290] The DBMS generates output describing the view by using the DDL command DESCRIBE. This output describes details of the metadata of the view, such as the view query expression.

[0291] Users also find the description of the object view mode of the JSON duality view useful. According to an embodiment, the DBMS can generate output describing the object view mode of the JSON duality view. According to an embodiment, the DBMS is configured to provide a function to be called that generates a description of the JSON schema. This function takes the JSON duality view as a parameter. Alternatively, the syntax of the DDL statement requesting the object view mode can also be used.

[0292] Figure 16 An example of the description of the object view mode of deptvfq is shown. This description conforms to the JSON schema.

[0293] Finally, database commands submitted to the DBMS can use a simplified syntax to generate a query expression similar to the view query expression. Similar to the way of defining a JSON duality view, the registered parent-foreign key relationship can be used to generate a query expression that returns a JSON object with array fields or object fields.

[0294] JSON duality view metadata

[0295] In response to receiving a DDL statement defining a JSON duality view, the DBMS generates JDV metadata that defines the view in the database dictionary. This metadata defines various aspects, such as:

[0296] 1. Fields, field names, object hierarchies, object view modes, and table hierarchies.

[0297] 2. Mapping between the root table and root fields and root attributes.

[0298] 3. Mapping between the base table and base attributes to sub-fields of object fields and array fields.

[0299] 4. Mapping of object and array field FK:PK relationships to relationships between object and array fields, and for each FK:PK relationship, the related attributes and tables.

[0300] 5. Mapping between change permissions and the base table and attributes.

[0301] 6. Set of version signature patterns.

[0302] 7. View query expressions in the case where a JSON duality view is defined using a view query expression in a DDL statement.

[0303] 8. One or more JDV object identifier attributes, including one or more JDV object identifiers corresponding to the primary key.

[0304] JDV metadata can be cached in volatile memory in a compile-time form. The cached JDV metadata provides faster access to the JDV metadata. The faster access is accelerated not only by caching the metadata in volatile memory with lower latency, but also by storing the metadata in a compile-time format that is organized for more efficient access by database processes that use the metadata to perform operations such as compilation.

[0305] The terms correlated tables and correlated attributes are generally used herein to refer to the tables and attributes involved in the FK:PK relationships of object fields or the PK:FK relationships of array fields. When used with respect to a table or attribute, the term "correlated" does not limit the tables and attributes to those related by an outer query or subquery query expression in a view query expression of a DDL statement or stored in JDV metadata. As shown above, a DDL statement can be used to define a JSON duality view without describing a view query expression, such as a DDL statement like JDDL3'. Database statements requesting JDV objects in a JSON duality view can be rewritten to include a correlated subquery of an object field or an array field based on the mappings in the JDV metadata (including the mappings between the FK:PK relationships and object fields and the mappings between the FK:PK relationships and array fields).

[0306] JSON Duality View DOCS

[0307] The JSON duality view and related content described herein can be implemented in DOCS. Like views in a relational DBMSFigure 1 Similarly, DOCS provides a view mechanism that can be adapted to map the attributes of collections and tables to fields in an object view mode. DDL statements (such as a simplified DDL syntax) can be used to define JSON duality views and generate JDV metadata that defines JSON duality views.

[0308] DOCS provides a primary key for a table or collection of documents. For example, a JSON document in a collection may have an “_id” field that serves as the primary key. A collection or table of documents may also have one or more foreign keys with primary key values. A database dictionary for DOCS can define registered primary-foreign key relationships that can be used to automatically generate JSON duality views using the simplified DDL syntax described previously.

[0309] In DOCS where documents are stored as JSON objects, a JSON duality view can define a root collection and sub-collections, and the PK:FK relationships between the root collection and sub-collections can be used to define array fields in an object view, whose related attributes include the primary key attribute in the root collection and the foreign key attribute in the sub-collection. Similarly, the FK:PK relationships between the root collection and sub-collections can be used to define object fields.

[0310] A basic record set includes a hierarchy of record subsets, each record subset including one or more JSON objects from a corresponding basic collection. The parent-child relationship between a parent record subset and a child record subset is based on a registered parent-foreign relationship between the primary key and foreign key of the collections of the parent record subset and the child record subset.

[0311] DBMS Overview

[0312] A database management system (DBMS) manages a database. The DBMS may include one or more database servers. A database includes database data and a database dictionary stored on a persistent storage mechanism (such as a set of hard disks). The database data can be stored in one or more record collections. The data within each record is organized into one or more attributes. In a relational DBMS, a collection is called a table (or data frame), a record is called a record, and an attribute is called an attribute. In a document DBMS (“DOCS”), a collection of records is a collection of documents, each of which can be a data object marked up with a hierarchical markup language, such as a JSON object or an XML document. An attribute is called a JSON field or an XML element. A relational DBMS can also store hierarchically marked data objects; however, the hierarchically marked data objects are contained within the attributes of a record, such as an attribute of JSON type.

[0313] Users interact with the database server of the DBMS by submitting commands to the database server that cause the database server to perform operations on data stored in the database. A user can be one or more applications running on a client computer that interact with the database server. Multiple users may also be collectively referred to as users in this document.

[0314] Database commands can be in the form of database statements that conform to a database language. The database language used to express database commands is Structured Query Language (SQL). There are many different versions of SQL, some of which are standard and some are proprietary, and there are various extensions. Data Definition Language (“DDL”) commands are issued to the database server to create or configure data objects referred to as database objects in this document, such as tables, views, or complex data types. SQL / XML is a common extension of SQL used when manipulating XML data in an object-relational database. Another database language used to express database commands is Spark, which uses a syntax based on function or method calls. TM SQL.

[0315] In DOCS, database commands can be in the form of function or object method calls (calls) that invoke CRUD (Create Read Update Delete) operations. An example of the API for such function and method calls is MQL (MondoDB TM Query Language). In DOCS, database objects include collections of documents, views, or fields defined by a JSON schema for a collection. A view can be created by calling a function provided by the DBMS to create a view in the database.

[0316] Changes to the database in the DBMS are made using transaction processing. A database transaction is a set of operations that change the database data. In the DBMS, a database transaction is initiated in response to a database statement that requests a change, such as a DML statement (such as an INSERT or UPDATE statement that requests an update, insertion, or deletion of a record) or a CRUD object method call that requests the creation, update, or deletion of a document. A DML statement or command refers to a statement that specifies a change to the data, such as an INSERT and UPDATE statement. A DML statement or command does not refer to a statement that only queries the database data. Committing a transaction confirms the transaction, making the changes of the transaction permanent.

[0317] Under transaction processing, all changes to a transaction are made atomically. When committing a transaction, either all changes are committed and confirmed, or the transaction is rolled back.

[0318] In a distributed transaction, multiple DBMSs use a two-phase commit confirmation method to commit and confirm the distributed transaction. Each DBMS executes a local transaction in a branch transaction of the distributed transaction. One DBMS (the coordinating DBMS) is responsible for coordinating the commit confirmation of transactions on one or more other database systems. The other DBMSs are referred to as participating DBMSs in this article.

[0319] The two-phase commit confirmation involves two phases, the prepare commit confirmation phase and the commit confirmation phase. In the prepare commit confirmation phase, the branch transactions are prepared in each of the participating database systems. When a branch transaction is prepared on a DBMS, the database is in a "prepared state" such that it can guarantee that the modifications made to the database data as part of the branch transaction can be committed and confirmed. This guarantee may require persistent storage of the change records of the branch transaction. The participating DBMSs confirm when they have completed the prepare commit confirmation phase and have entered the prepared state of the corresponding branch transactions of the participating DBMSs.

[0320] In the commit confirmation phase, the coordinating database system commits and confirms the transaction on the coordinating database system and the participating database systems. Specifically, the coordinating database system sends a message to the participants, requesting the participants to commit and confirm the modifications specified by the transaction to the data on the participating database systems. Then, the participating database systems and the coordinating database system commit and confirm the transaction.

[0321] On the other hand, if a participating database system fails to prepare, or the coordinating database system fails to commit and confirm, at least one of the database systems cannot make the changes specified by the transaction. In this case, all the modifications at each of the participants and at the coordinating database system are rolled back, restoring each database system to its state before the changes.

[0322] A client can issue a series of requests to a DBMS by establishing a database session, such as requests for the execution of queries. A database session includes a specific connection to the database server established for the client, through which the client can issue a series of requests. The database session process executes within the database session and processes the requests issued by the client through the database session. The database session can generate an execution plan for the queries issued by the database session client and marshal subordinate processes for the execution of the plan.

[0323] The database server can maintain session state data about the database session. The session state data reflects the current state of the session and can include the identity of the user for whom the session was established, the services used by the user, instances of object types, language and character set data, statistical data about the use of session resources, values of temporary variables generated by the processes executing software within the session, cursor storage, variables, and other information.

[0324] The database server includes multiple database processes. The database processes run under the control of the database server (i.e., can be created or terminated by the database server) and perform various database server functions. The database processes include processes that run within a database session established for a client.

[0325] A database process is an execution unit. A database process can be a computer system process or thread, or a user-defined execution context, such as a user thread or fiber. A database process can also include "database server system" processes that provide services and / or perform functions on behalf of the entire database server. Such database server system processes include listeners, garbage collectors, log writers, and recovery processes.

[0326] A multi-node database management system consists of interconnected nodes, each running a database server that shares access to the same database. Typically, the nodes are interconnected via a network and share access to shared storage to varying degrees, such as shared access to a set of disk drives and the data blocks stored thereon. The nodes in a multi-node database system can be in the form of a set of computers (e.g., workstations, personal computers) interconnected via a network. Alternatively, the nodes can be nodes of a grid consisting of server blades interconnected with other server blades on a rack.

[0327] Each node in a multi-node database system hosts a database server. A server (such as a database server) is a combination of an integrated software component and a computing resource allocation, such as memory, a node, and processes on the node for executing the integrated software component on a processor, the combination of software and computing resources dedicated to performing a specific function on behalf of one or more clients.

[0328] Resources from multiple nodes in a multi-node database system can be allocated to the software running a specific database server. Each combination of software and resource allocation from a node is a server referred to herein as a "server instance" or "instance". A database server can include multiple database instances, some or all of which run on separate computers (including separate server blades).

[0329] The database dictionary can include multiple data structures that store database metadata. For example, the database dictionary can include multiple files and tables. Portions of the data structures can be cached in the main memory of the database server.

[0330] When a database object is said to be defined by a database dictionary, the database dictionary includes metadata that defines the properties of the database object. For example, the metadata in the database dictionary that defines a database table can specify the attribute names and the data types of the attributes, as well as one or more files or portions thereof that store the table data. The metadata in the database dictionary that defines a procedure can specify the name of the procedure, the parameters and return data types of the procedure, and the data types of the parameters, and can include the source code and its compiled version

[0331] A database object can be defined by a database dictionary, but the metadata in the database dictionary itself may only partially specify the properties of the database object. Other properties can be defined by data structures that may not be considered part of the database dictionary. For example, a user-defined function implemented in a JAVA class can be partially defined by the database dictionary by specifying the name of the user-defined function and by specifying references to files that contain the JAVA class source code (i.e., the.java file) and the compiled version of the class (i.e., the.class file).

[0332] Native data types are the data types supported by the DBMS "out of the box". On the other hand, non-native data types may not be supported by the DBMS out of the box. Non-native data types include user-defined abstract types or object classes. Non-native data types can only be recognized and processed by the DBMS in database commands after the non-native data type is defined in the database dictionary of the DBMS (e.g., by issuing a DDL statement that defines the non-native data type to the DBMS). Native data types do not have to be defined by the database dictionary to be recognized as valid data types and processed by the DBMS in database statements. Typically, the database software of the DBMS is programmed to recognize and process native data types without having to be configured into the DBMS (e.g., by issuing a DDL statement to the DBMS to define the data type) to do so.

[0333] Hardware Overview

[0334] According to one embodiment, the techniques described herein are implemented by one or more special-purpose computing devices. The special-purpose computing device can be hardwired to perform the techniques, or can include digital electronic devices (such as one or more application-specific integrated circuits (ASICs) or field-programmable gate arrays (FPGAs)) that are persistently programmed to perform the techniques, or can include one or more general-purpose hardware processors programmed to perform the techniques according to program instructions in firmware, memory, other storage devices, or a combination. Such special-purpose computing devices can also combine custom hardwired logic, ASICs, or FPGAs with custom programming to complete the techniques. The special-purpose computing device can be a desktop computer system, a portable computer system, a handheld device, a networking device, or any other device that incorporates hardwired and / or program logic to implement the techniques.

[0335] For example, Figure 17 FIG. 1700 is a block diagram of a computer system 1700 on which embodiments of the present invention may be implemented. Computer system 1700 includes a bus 1702 or other communication mechanism for conveying information, and a hardware processor 1704 coupled to bus 1702 for processing information. The hardware processor 1704 may be, for example, a general-purpose microprocessor.

[0336] Computer system 1700 also includes a main memory 1706 coupled to bus 1702, such as a random access memory (RAM) or other dynamic storage device, for storing information and instructions to be executed by processor 1704. The main memory 1706 may also be used to store temporary variables or other intermediate information during execution of instructions to be executed by processor 1704. When such instructions are stored in a non-transitory storage medium accessible to processor 1704, computer system 1700 is presented as a special-purpose machine customized to perform the operations specified in the instructions.

[0337] Computer system 1700 also includes a read only memory (ROM) 1708 or other static storage device coupled to bus 1702 for storing static information and instructions for processor 1704. A storage device 1710, such as a magnetic disk, optical disk, or solid state drive, is provided and coupled to bus 1702 for storing information and instructions.

[0338] Computer system 1700 may be coupled via bus 1702 to a display 1712, such as a cathode ray tube (CRT), for displaying information to a computer user. An input device 1714 including alphanumeric keys and other keys is coupled to bus 1702 for communicating information and command selections to processor 1704. Another type of user input device is a cursor control 1716 for communicating direction information and command selections to processor 1704 and for controlling cursor movement on display 1712, such as a mouse, trackball, or cursor direction keys. This input device typically has two degrees of freedom in two axes (a first axis (e.g., x) and a second axis (e.g., y)), which allows the device to specify a position in a plane.

[0339] The computer system 1700 can implement the techniques described herein using customized hardwired logic, one or more ASICs or FPGAs, firmware, and / or program logic that in combination with the computer system causes or programs the computer system 1700 to be a special-purpose machine. According to one embodiment, the techniques herein are performed by the computer system 1700 in response to the processor 1704 executing one or more sequences of one or more instructions contained in the main memory 1706. Such instructions can be read into the main memory 1706 from another storage medium, such as the storage device 1710. Execution of the instruction sequence contained in the main memory 1706 causes the processor 1704 to perform the process steps described herein. In an alternative embodiment, the hardwired circuitry may be used in place of or in combination with software instructions.

[0340] As used herein, the term “storage medium” refers to any non-transitory medium that stores data and / or instructions that cause a machine to operate in a particular fashion. Such storage medium may include non-volatile media and / or volatile media. Non-volatile media includes, for example, optical or magnetic disks or solid state drives, such as the storage device 1710. Volatile media includes dynamic memory, such as the main memory 1706. Common forms of storage media include, for example, floppy disks, flexible disks, hard disk drives, solid state drives, magnetic tape, or any other magnetic data storage medium, CD-ROM, any other optical data storage medium, any physical medium with patterns of holes, RAM, PROM, and EPROM, FLASH-EPROM, NVRAM, any other memory chip or cartridge.

[0341] A storage medium is different from a transmission medium but may be used in combination with a transmission medium. A transmission medium participates in transferring information between storage media. For example, a transmission medium includes coaxial cables, copper wire, and fiber optics, including the lines that include the bus 1702. The transmission medium can also take the form of acoustic or light waves, such as those generated during radio wave and infrared data communications.

[0342] Various forms of media may be involved in carrying one or more sequences of one or more instructions to the processor 1704 for execution. For example, the instructions may initially be carried on a magnetic disk or solid state drive of a remote computer. The remote computer may load the instructions into its dynamic memory and send the instructions over a telephone line using a modem. A modem local to the computer system 1700 may receive the data over the telephone line and convert the data into an infrared signal using an infrared transmitter. An infrared detector may receive the data carried in the infrared signal and appropriate circuitry may place the data on the bus 1702. The bus 1702 carries the data to the main memory 1706, from which the processor 1704 retrieves and executes the instructions. The instructions received by the main memory 1706 may optionally be stored on the storage device 1710 before or after being executed by the processor 1704.

[0343] The computer system 1700 also includes a communication interface 1718 coupled to the bus 1702. The communication interface 1718 provides two-way data communication coupling to a network link 1720 connected to a local network 1722. For example, the communication interface 1718 may be an integrated services digital network (ISDN) card, a cable modem, a satellite modem, or a modem that provides a data communication connection to a corresponding type of telephone line. As another example, the communication interface 1718 may be a local area network (LAN) card that provides a data communication connection to a compatible LAN. A wireless link may also be implemented. In any such implementation, the communication interface 1718 sends and receives electrical, electromagnetic, or optical signals that carry digital data streams representing various types of information.

[0344] The network link 1720 typically provides data communication to other data devices through one or more networks. For example, the network link 1720 may provide a connection through the local network 1722 to a host 1724 or to data equipment operated by an Internet service provider (ISP) 1726. The ISP 1726 in turn provides data communication services through the global packet data communication network now commonly referred to as the "Internet" 1728. Both the local network 1722 and the Internet 1728 use electrical, electromagnetic, or optical signals that carry digital data streams. Signals through the various networks and signals on the network link 1720 and through the communication interface 1718, which carry digital data to and from the computer system 1700, are example forms of transmission media.

[0345] The computer system 1700 can send messages and receive data, including program code, via one or more networks, network link 1720, and communication interface 1718. In an Internet example, the server 1730 can transmit the request code of an application program via the Internet 1728, the ISP 1726, the local network 1722, and the communication interface 1718.

[0346] The received code can be executed by the processor 1704 when it is received, and / or stored in the storage device 1710 or other non-volatile memory for later execution.

[0347] Software Overview

[0348] Figure 18 is a block diagram of the basic software system 1800 that can be used to control the operation of the computing system 1700. The software system 1800 and its components, including their connections, relationships, and functions, are meant to be exemplary only and are not meant to limit the implementation of one or more example embodiments. Other software systems suitable for implementing one or more example embodiments may have different components, including components with different connections, relationships, and functions.

[0349] The software system 1800 is provided to direct the operation of the computing system 1700. The software system 1800, which can be stored in the system memory (RAM) 1706 and the fixed storage device (e.g., hard disk or flash memory) 1710, includes a kernel or operating system (OS) 1810.

[0350] The OS 1810 manages the low-level aspects of computer operation, including managing the execution of processes, memory allocation, file input and output (I / O), and device I / O. One or more application programs represented as 1802A, 1802B, 1802C…1802N can be “loaded” (e.g., transferred from the fixed storage device 1710 to the memory 1706) for execution by the system 1800. Applications or other software intended to be used on the computer system 1700 can also be stored as a set of downloadable computer-executable instructions, e.g., for downloading and installation from an Internet location (e.g., a web server, an app store, or other online service).

[0351] The software system 1800 includes a graphical user interface (GUI) 1815 for receiving user commands and data in a graphical manner (e.g., “clicking” or “touch gestures”). Subsequently, the system 1800 can act on these inputs according to instructions from the operating system 1810 and / or one or more applications 1802. The GUI 1815 is also used to display the operation results from the OS 1810 and one or more applications 1802, whereby the user can supply additional inputs or terminate the session (e.g., log off).

[0352] OS 1810 can be executed directly on the bare hardware 1820 of the computer system 1700 (e.g., the (one or more) processors 1704). Alternatively, a hypervisor or virtual machine monitor (VMM) 1830 can be inserted between the bare hardware 1820 and the OS 1810. In this configuration, the VMM 1830 acts as a software "buffer" or virtualization layer between the OS 1810 and the bare hardware 1820 of the computer system 1700.

[0353] The VMM 1830 instantiates and runs one or more virtual machine instances ("guest machines"). Each guest machine includes a "guest" operating system, such as the OS 1810, and one or more applications designed to execute on the guest operating system, such as the (one or more) applications 1802. The VMM 1830 presents a virtual operating platform to the guest operating system and manages the execution of the guest operating system.

[0354] In some cases, the VMM 1830 can allow the guest operating system to run as if it were running directly on the bare hardware 1820 of the computer system 1700. In these cases, the same version of the guest operating system configured to execute directly on the bare hardware 1820 can also execute on the VMM 1830 without modification or reconfiguration. In other words, in some cases, the VMM 1830 can provide full hardware and CPU virtualization for the guest operating system.

[0355] In other cases, the guest operating system can be specifically designed or configured to execute on the VMM 1830 for increased efficiency. In these cases, the guest operating system "knows" that it is executing on the virtual machine monitor. In other words, in some cases, the VMM 1830 can provide para-virtualization for the guest operating system.

[0356] A computer system process includes hardware processor time allocation and (physical and / or virtual) memory allocation for storing instructions executed by the hardware processor, for storing data generated by executing instructions by the hardware processor, and / or for storing the hardware processor state (e.g., the contents of registers) between hardware processor time allocations when the computer system process is not running. A computer system process runs under the control of an operating system and can run under the control of other programs executing on the computer system.

[0357] Cloud computing

[0358] The term "cloud computing" is generally used herein to describe a computing model that enables on-demand access to a shared pool of computing resources, such as computer networks, servers, software applications, and services, and that allows for the rapid provisioning and release of resources with minimal management effort or service provider interaction.

[0359] Cloud computing environments (sometimes referred to as cloud environments or the cloud) can be implemented in a variety of different ways to best meet different needs. For example, in a public cloud environment, the underlying computing infrastructure is owned by an organization that makes its cloud services available to other organizations or the public. In contrast, a private cloud environment is typically intended to be used only by or within a single organization. A community cloud is intended to be shared by several organizations within a community; while a hybrid cloud includes two or more types of clouds (e.g., private, community, or public) that are bound together by data and application portability.

[0360] Generally, the cloud computing model enables some of those responsibilities that might previously have been provided by an organization's own information technology department to instead be delivered within the cloud environment as a service layer for consumption by consumers (whether internal or external to the organization, depending on the public / private nature of the cloud). Depending on the specific implementation, the precise definition of the components or features provided or within each cloud service layer may vary, but common examples include: Software as a Service (SaaS), where the consumer uses software applications running on the cloud infrastructure, and the SaaS provider manages or controls the underlying cloud infrastructure and applications. Platform as a Service (PaaS), where the consumer can use software programming languages and development tools supported by the PaaS provider to develop, deploy, and otherwise control their own applications, and the PaaS provider manages or controls the other aspects of the cloud environment (i.e., all under the runtime execution environment). Infrastructure as a Service (IaaS), where the consumer can deploy and run any software applications, and / or provision processing, storage, network, and other basic computing resources, and the IaaS provider manages or controls the underlying physical cloud infrastructure (i.e., all below the operating system layer). Database as a Service (DBaaS), where the consumer uses a database server or database management system running on the cloud infrastructure, and the DbaaS provider manages or controls the underlying cloud infrastructure, applications, and servers, including one or more database servers.

[0361] In the foregoing specification, embodiments of the invention have been described with reference to numerous specific details that may vary with implementation. Accordingly, the specification and drawings are to be regarded in an illustrative rather than a restrictive sense. The sole and exclusive indicator of the scope of the invention, and what the applicant intends to be the scope of the invention, is the literal and equivalent scope of the set of claims that are recited in this application, in the specific form in which such claims are recited, including any subsequent corrections.

Claims

1. A method, comprising: performing a specific database transaction, wherein performing the specific database transaction comprises: receiving a request for updating a JSON object in a JSON duality view to a specific version of the JSON object received by the request; updating a plurality of base records that store values of fields of the JSON object in a database; in a consistent read mode, deterministically generating a first version signature based on the plurality of base records; in a current read mode, deterministically generating a second version signature based on the plurality of base records; determining whether the first version signature matches the second version signature; and if the first version signature matches the second version signature, committing to confirm the update.

2. The method according to claim 1, wherein when deterministically generating the first version signature, a specific base record among the plurality of base records has been changed by an uncommitted database transaction associated with a snapshot time greater than the snapshot time associated with the specific database transaction; wherein determining whether the first version signature matches the second version signature comprises determining that the first version signature does not match the second version signature; in response to determining that the first version signature does not match the second version signature, abandoning the commitment to confirm the specific database transaction.

3. The method according to claim 1, wherein performing the specific database transaction comprises: before updating the plurality of base records, deriving a plurality of derived records from the specific version of the JSON object; determining a subset of the plurality of base records that have been changed by comparing at least the plurality of base records with the plurality of derived records; and wherein updating the plurality of base rows comprises updating the subset.

4. The method according to claim 3, wherein the subset does not include all of the plurality of base records.

5. The method according to claim 4, wherein performing the specific database transaction comprises: before deterministically generating the first version signature, locking only the subset of the base records without locking at least one other base record among the plurality of base records.

6. The method according to claim 4, wherein the plurality of derived records include a specific derived record that has no corresponding base record among the plurality of base records, and wherein performing the specific database transaction comprises inserting a corresponding base record corresponding to the specific derived record.

7. The method according to claim 1, wherein the plurality of base records include parent records from a parent table and child records from a child table, wherein the parent table and the child table are associated with a defined primary foreign key relationship, and wherein performing the specific database transaction comprises locking foreign key values in an external index of the defined primary foreign key relationship.

8. A method, comprising: performing a specific database transaction, wherein performing the specific database transaction comprises: receiving a request for making an update to a database; making the update by changing at least one record in a set of records in the database; In the consistent read mode, a first version signature is deterministically generated based on values in the set of records; In the current read mode, a second version signature is deterministically generated based on values in the set of records; Determine that the first version signature matches the second version signature; and In response to determining that the first version signature matches the second version signature, submit to confirm the update.

9. The method according to claim 8, wherein the set of records includes a plurality of records; and wherein the update is made by changing at least one record comprises: Changing a subset of the plurality of records, the subset including all of the plurality of records.

10. The method according to claim 9, wherein performing the particular database transaction comprises: Before deterministically generating the first version signature, only lock the subset of the plurality of records without locking at least one record in the set of base records.

Images