Advanced Features

20.1 Join Collection

Join collections allow clients to define and query across multiple relational tables configured in the project. These joins are created and managed through the Oracle Backend for Firebase Console and are exposed as logical collections that can be queried like any other document collection.

Join Collection Setup and Prerequisites

Parent topic: Advanced Features

20.1.1 Join Collection Setup and Prerequisites

Client can create a join query using the Console. Each join can further be used as a collection.

Prerequisites

The project must already have a relational database schema configured.
Each table involved in the join and used within the query must be part of the relational setup defined for the project in the Console.
Join definitions must be created using the Console UI.

How to Set Up

Define Join View in Console:
- Navigate to the Join Collection setup section.
- Specify the following input fields:
  - view_name: Name of the collection that will be used in the database operation (length >= 1 and <= 120)
  - table: Ordered list of tables involved, representing parent table name followed by child table names
  - columns: Fields to be used within the select clause. Each field includes:
    - col: column name
    - table: table name for which col is associated.
    - alias: Alias for the field, used in the select clause
  - joins: Join type and array of join condition between each parent and child table
    - type:
      - INNER JOIN
      - JOIN
      - LEFT JOIN
      - RIGHT JOIN
      - FULL JOIN
    - parent_table: parent table name
    - child_table: child table name
    - conditions: This object represents conditions that will be concatenated in two round brackets ( condition )
      - logic: AND/OR, represents the type of operation
      - clauses: LHS and RHS represent the condition to join two tables
Publish Security Rule:
- Each join must have a specific security rule to be published from the Console.
- If a rule is not defined, all operations on the join collection will be rejected.
- Define a rule for the join view path:
```
{
  match /<view_name>/_docId {
  allow read: if request.auth is not null && 
                 resource.data.uuid = 'scott'
} 
```
Verify Access by ensuring that the rule is active and test read access using the Console or SDK.

Parent topic: Join Collection

20.2 Collection Group Queries

A collection group allows you to query across all subcollections with the same name, regardless of where they exist in your document hierarchy. It’s different from a normal collection query, which only looks within a specific path. This is useful for querying deeply nested or distributed data structures.

Setup

Collection groups are created during index creation using the Console.
A dedicated security rule must be defined for each collection group.

Example 20-1 Rule for a Collection Group Named recipes:

match /.*/recipes {
  allow read: if <expression>;
}

You can filter documents across all subcollections named recipes and query by field values or document identifiers.

Collection Group Setup and Prerequisites

Parent topic: Advanced Features

20.2.1 Collection Group Setup and Prerequisites

Prerequisites

Subcollections with the same name must exist across multiple parent documents.
A collection group index must be created using the Console.

How to Set Up

Navigate to the Indexing section.
Define a collection group index for the target subcollection name (For example, recipes).

Define the security rule. The following is an example:

match /.*/recipes {
  allow read: if request.auth != null && resource.data.difficulty == "Easy";
}

Use the Console or SDK to run a query across the collection group and verify access.

Parent topic: Collection Group Queries

20.3 Standalone Duality View

Duality views allow relational data to be accessed in a document-style format. These views are automatically created when hierarchical relationships are defined in the Console using primary key-foreign key mappings.

Characteristics

These duality view are not created by Oracle Backend for Firebase project, but created independently by application developer for specific use.
Oracle Backend for Firebase allows read/write on these duality view given that security rule for the Duality View exists.
Each hierarchical relation defined in the Console results in a duality view.
The view is exposed as a logical collection. User-defined duality view can be treated as collection for Oracle Backend for Firebase applications.

Standalone Duality View Setup and Creation

Parent topic: Advanced Features

20.3.1 Standalone Duality View Setup and Creation

An application developer creates the Duality View as per their requirements on any existing table.

Creation Rules

Created Duality View should have an "OID" field at the root level, which should be autogenerated if not passed as part of document and should always be unqiue.
Duality View should be created with all insert, delete, and update rules.
A relational table hierarchy must be defined using primary key–foreign key mappings.
Duality View should be created within the Project owner schema.
SYS_MAKE_OID_FROM_PK, which is an existing function can be used to generate OID by passing primary key column names.

Rule

-- Example with department and employee table 
create table dept (
  deptno number(2) constraint pk_dept primary key,
  dname varchar2(14),
  loc varchar2(13)
) ;

create table emp (
  empno number(4) constraint pk_emp primary key,
  ename varchar2(10),
  job varchar2(9),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2) constraint fk_deptno references dept
);

create or replace json relational duality view department_dv as
select json {'_id' : d.deptno,
             'OID' : SYS_MAKE_OID_FROM_PK(d.deptno),
             'departmentName'   : d.dname,
             'location'         : d.loc,
             'employees' :
               [ select json {'oid' : SYS_MAKE_OID_FROM_PK(e.empno),
                              'employeeNumber' : e.empno,
                              'employeeName'   : e.ename,
                              'job'            : e.job,
                              'salary'         : e.sal}
                 from   emp e with insert update delete
                 where  d.deptno = e.deptno ]}
from dept d with insert update delete;

Security Rule

Finally, a security rule has to created with the view name and corresponding rule for each operation. Supported Operations are: Add document, Update Document , Read Collection/Document.

Note:

Set document is not supported for Duality Views.

{
  match /department_dv/_docId {
  allow read: if request.auth is not null && 
                 resource.data.departmentName = 'engineering'
}

Parent topic: Standalone Duality View

20.4 Snapshot Reads

Snapshot reads allow clients to retrieve the state of a document or collection at a specific point in time. This is useful for:

Implementing audit trails
Supporting versioned data access
Performing time-based queries

Snapshot reads are supported by the underlying ORDS infrastructure and return complete document data for each read operation. The number of rows returned is determined by the document size, and pagination can be applied using limit clauses.

Snapshot Reads Setup & Prerequisites

Parent topic: Advanced Features

20.4.1 Snapshot Reads Setup & Prerequisites

Prerequisites

Snapshot reads are supported by default via the ORDS infrastructure.
Pagination and limit clauses should be configured based on document size.

How to Use

Use limit, startAt, endAt, or startAfter clauses to paginate or filter based on time or document state.
Ensure that the security rules allow read access to the target documents.

Parent topic: Snapshot Reads

20.5 Managing Database Using CLI

Prerequisite

Ensure that the setup of the CLI for Oracle Backend for Firebase is complete.

20 Advanced Features

20.1 Join Collection

20.1.1 Join Collection Setup and Prerequisites

20.2 Collection Group Queries

20.2.1 Collection Group Setup and Prerequisites

20.3 Standalone Duality View

20.3.1 Standalone Duality View Setup and Creation

20.4 Snapshot Reads

20.4.1 Snapshot Reads Setup & Prerequisites

20.5 Managing Database Using CLI