11 Database

Database APIs help you create and manage database tables for use in mobile apps. As a service developer, you can call the Database Access API from custom API implementations to create and access database tables, and use the Database Management API to manage and view table metadata.

What Can I Do with Database APIs?

As noted above, there are two database APIs:

• The Database Access API, which is available only from custom code implementations using the custom code SDK, lets you to create and access database tables. For security reasons, you can’t call this API from client apps. To try out calls to this API, open a custom API, go to the Custom Catalog, and then click Database Access.

• The Database Management API can be accessed through custom code implementations and HTTP REST calls to manage table metadata and deploy tables. To try out calls to this API from the UI, click to open the side menu, and click Development > APIs. In the Platform APIs section at the bottom of the APIs page, click Database Management.

Database Access API

All your mobile apps’ interactions with the Database Access API are made through custom API implementations. You can’t access this API directly from client apps. This section covers how to use the custom code SDK in a custom API implementation to interact with the database. To learn about designing APIs, see Custom API Design. To learn about implementing a custom API, see Implementing Custom APIs. For complete details for each custom code SDK database method, see Accessing the Database Access API from Custom Code .

Calling the Database Access API from Custom Code

Before we delve into how to implement a custom API to perform database tasks, let’s go over a simplified description of how to call the Database Access API from custom code. Here we talk about some API operations that you learn about later. While they may not make sense now, these steps should give you some context for how you use the operations that you will learn about.

To call the Database Access API from custom code, you add endpoints (resources) and operations (methods) to the custom API, and then you add route definitions to your custom code implementation for the custom API. We are going to talk about how to implement the route definitions in the custom code.

To call the API from your custom code:

1. Add the route definition to the custom code.
   You implement a route definition by calling the service method for the API’s endpoint operation. Say, for example, that your API has a GET operation for the /mobile/custom/FIF_Incidents/incidents endpoint. To implement this from your custom code, you call service.get(). The service method’s arguments are the URI and a function that takes both the request object and the response object as arguments. For example:

   service.get(
   '/mobile/custom/FIF_Incidents/incidents', function (req, res) {
     // your code goes here
   });

2. From the route definition, call the appropriate req.oracleMobile.database method to send your request to the Database Access API, such as get(), getAll(), or insert(). Accessing the Database Access API from Custom Code describes the available methods and the arguments that each method takes, and provides example code.
   Here’s a complete route definition. This route definition calls the getAll() method, which, in turn, calls the Database Access API’s GET /mobile/platform/database/objects/{table} operation. When the getAll() method receives a response from the API, it calls either the result function or the error function, depending on whether an error occurred.

   Notice that the first argument is the name of the table, and that the second argument is a JSON object that contains a fields property. This instructs the getAll() method to return only the customer and status fields.

   /**
    * GET CUSTOMER AND STATUS FOR ALL INCIDENTS
    */
   service.get('/mobile/custom/incidentreport/incidents',
   function (req, res) {
       req.oracleMobile.database.getAll(
         'FIF_Incidents', {fields: 'customer,status'}).then(    
         function (result) {
           res.status(statusCode).send (result.result);
         },
         function (error) {
           res.status(statusCode).send(error.error);
         }
         }
       );
     });

The response to this call would look like this:

{
  "items":[
    {
      "status":"Open",
      "customer":"Lynn Smith"
    },
    {
      "status":"Completed",
      "customer":"John Doe"
    }
  ]
}

Creating and Restructuring Database Tables

You might think that before you can access a database table, you need to first add it to the schema. However, you can create a new table simply by adding a row to the table. This action is referred to as a implicit table creation.

You use the following methods to insert rows into a table:

• insert(): Add one or more rows.

• merge(): Add or update one or more rows.

When you call these methods for a table that doesn’t exist, a new table with the row(s) is created by deriving the table specifications from information in the object and options arguments.

To specify the table structure:

• Call either insert() or merge(), both of which require table and object arguments. In the object argument, which is a JSON object, include all the columns that you want in the table, and provide mock or real data for each column. The column type and size are based on the content. For example, if the value is 100 then the column will be NUMBER(3,0). Don’t worry about the size being too small. If you later post 3.25, the column is resized to NUMBER(5,2), which is large enough for both 100 and 3.25. Also don’t worry about adding all the columns that you need. If you later decide you want more columns, then add the new columns to a JSON object and send it in an insert() or merge() call. The table will be restructured automatically to add the new columns.

  Note:

  The maximum size for a string column is 4000 characters. If you need to store a larger string, then you can use the Storage API to store the object.

  Here’s an example of the JSON object:

  {  
    "incidentReport": 1,
    "title": "Water heater is leaking",
    "customer": "Lynn Smith",
    "address": "200 Oracle Parkway Redwood City, CA 94065",
    "phone": "[phone]",
    "technician": "jwhite",
    "status": "Open",
    "notes": "lynnf|Initial incident report description",
    "priority": 1,
    "imageLink": "http://link.to.storage"
  }

• By default, a set of predefined columns are added and populated automatically whenever you add or update a record using insert() or merge().

  If you don’t want all these columns in your table, then use the extraFields property in the optional options argument to specify which columns to include, such as createdOn,createdBy (be sure to include id if you aren’t specifying a primary key). If you later decide you want to add more predefined columns, you can just add them to the extraFields property the next time you add a row.

  If you don’t want any of these columns, then set the extraFields property to none. However, if you don’t add any predefined columns when you create the table, then you can’t add any later.

  The predefined fields are:

  • id: The row key. This column is added only if both the primaryKeys and extraFields properties are absent. The id is an integer set and incremented automatically.

  • createdBy: Who created it.

  • createdOn: When it was created.

  • modifiedBy: When it was last modified.

  • modifiedOn: Who modified it last.

  The dates are in W3C date-time format, and include hours, minutes, seconds, and a decimal fraction of a second (YYYY-MM-DDThh:mm.ss.SSSZ).

• If you want a primary key, use the primaryKeys property in the options argument to specify which columns to use for the primary key. For example, incidentReport,technician. Note that the order that you list the fields is the order that you use when you retrieve or update a row. Because you can’t retrieve the primary key order from the table metadata, make sure that you document the order of the primary fields.

You can see code examples for these two methods in the next section.

The following table summarizes what aspects of a table can be changed implicitly:

Object	Can It Change?
Table Name	No. The name is set when the table is first created.
Primary Key	No. The primary key is defined when the table is created.
Predefined Columns	Yes. You can allow predefined columns in the table when it’s created by the call. However, you can’t add these predefined columns at a later point if the table was not originally intended to use them. If predefined columns are allowed, then any of them (other than id, that is) can be added by subsequent calls.
Columns	Yes. Although columns are created with the table, subsequent calls can add columns. These calls can also alter the column size. However, you can’t change the column type after the table has been created.

Note:

You can also disable implicit table creation. If the Database_CreateTablesPolicy environment policy is neither allow (the default setting) nor implicitOnly, adding a row to a non-existent table will fail.

Adding and Updating Table Rows

You use the insert() and merge()methods to add and update rows:

• insert() adds one or more rows.

• merge() adds or updates one or more rows. Whether an add or update is performed depends on whether the table uses id or primary key fields to uniquely identify rows.

  • id field: If you include an id property in the object, then the matching row is updated if it exists. Otherwise a new row is added.

  • Primary key fields: If the table uses primary key fields, the matching row is updated if it exists. Otherwise, a new row is added.

Note:

If you submit a batch of rows, then all the rows must have the same set of columns.

To call either of these methods:

• Pass the table name in the first argument.

• If the table doesn’t exist, and you want to limit which predefined columns to include, set the extraFields property in the options argument. For example:

  options = 
    {'extraFields' : 'createdOn,createdBy'}

  If you want all the predefined columns, omit this property. If you don’t want any predefined columns, set it to none. It doesn’t hurt to include it in subsequent adds, but make sure you include it in your first add if you don’t want the full set of predefined columns.

• If the table doesn’t exist, and you want to specify a primary key, make sure you set the primaryKeys property in the options argument. For example:

  options = 
    { 'primaryKeys' : 'incidentReport,technician' }

  The primary key list must be URL encoded.

• Put the row data in the request body in JSON format. The JSON object can contain data for one row or several rows.

  Here is an example of data for one row:

  { 
    "status" : "Open",
    "code" : "3"
  }

  Here is an example of data for multiple rows:

  [
      {
          "status":"Open",
          "code":3},
      {
          "status":"Completed",
          "code":9}
  ]

Here’s an example of using the insert() method to add two rows to the FIF_Status table. The first argument is the table name, and the second argument is the object argument, which contains the rows to add to the table. The third argument is the options argument, which specifies to not add any extra (predefined) fields, and to create a primary key based on the code field.

service.post('/mobile/custom/incidentreport/initStatus', function (req, res) {
  req.oracleMobile.database.insert(
    'FIF_Status',
    [
      {
        "status": "Closed",
        "code": "0"},
      {
        "status": "Completed",
        "code": "9"}
    ],
    {extraFields: 'none', primaryKeys: 'code'}).then(
    function (result) {
      res.status(statusCode).send (result.result);
      },
      function (error) {
        res.status(statusCode).send(error.error);
    }
  );
});

Retrieving Table Rows

You can retrieve a single table row by its primary key or ID, and you can retrieve a set of table rows.

To retrieve a row by its primary key or ID, call the get() method. You use the keys argument to identify the row that you want.

• If the table uses the id column for the row key, then set keys to the row’s ID.

• If the table has a primary key, then set keys to the primary key values in the order in which the primary keys were specified when the first row was added to the table (which resulted in the creation of the table). Use an array for a composite key. For example, if the options.primaryKeys property was set to incidentReport,technician when the table was created, then the values must be listed in that order, such as: ['5690','jwhite'].

Here’s an example of using the get() method to retrieve a row from the FIF_Incidents table. The first argument is the table name, and the second argument is the keys argument:

/**
 * GET INCIDENT BY ID
 */
service.get('/mobile/custom/incidentreport/incidents/:id',
  function (req, res) {
    req.oracleMobile.database.get(
      'FIF_Incidents', req.params.id).then(      
      function (result) {
        res.status(statusCode).send (result.result);
      },
      function (error) {
        res.status(statusCode).send(error.error);
      }
    );
  });

The response body looks like this:

{
  "items":[
    {
      "id":168,
      "title":"Oven not working",
      "technician":"jwhite",
      "status":"Open",
      "customer":"John Doe",
      "incidentReport":"5690",
      "createdBy":"jdoe",
      "createdOn":"2015-11-16T23:42:18.281823+00:00"
    }
  ]
}

To get a set of rows from a table, call the getAll() method.

• To filter the rows, add the columns to search on and the values to match to the qs property in the optional httpOptions argument. For example, this requests all the incident reports for the technician J. White:

  httpOptions.qs = {technician : 'jwhite'};

• To specify which columns to return, use the fields property in the options argument.

  For example, to get a quick phone list:

  options={'fields' : 'customer,phone'}

Here’s an example of using getAll() to retrieve the customer and status fields for all rows in the FIF_Incidents table that match the query string that’s specified in httpOptions.qs.

/**
 * GET ALL INCIDENTS
 */
service.get('/mobile/custom/incidentreport/incidents',
function (req, res) {
    httpOptions={};
    httpOptions.qs = {technician : 'jwhite'};
    req.oracleMobile.database.getAll(
      'FIF_Incidents', {fields: 'customer,status'}, httpOptions).then(
      function (result) {
        rres.status(statusCode).send (result.result);
      },
      function (error) {
        res.status(statusCode).send(error.error);
      }
    );
  }); 

The response body looks like this:

{"items":[
  {"title":"Water heater is leaking",
  "technician":"jwhite",
  ,"customer":"Lynn Smith"
  ...
  "incidentReport":25
  "createdOn":"2015-03-05T12:10:15.171284-07:00"},
  {"title":"Dryer doesn't dry",
  "technician":"jwhite",
  ,"customer":"Lynn Smith"
  ...
  "incidentReport":67
  "createdOn":"2015-08-07T14:22:37.171284-07:00"}
]}

Deleting Table Rows

To delete a row, you call the delete() method.

You use the keys argument to identify the row that you want to delete.

• If the table uses the id column for the row key, then set keys to the row’s ID.

• If the table has a primary key, then set keys to the primary key values in the order in which the primary keys were specified when the first row was added to the table (which resulted in the creation of the table). Use an array for a composite key. For example, if the options.primaryKeys property was set to incidentReport,technician when the table was created, then the values must be listed in that order, such as: ['5690','jwhite'].

Here’s an example of deleting a row from the FIF_Incidents table. The first argument to the delete() method is the table name, and the second argument is the keys argument.

/**
 * DELETE INCIDENT BY ID
 */
service.delete('/mobile/custom/incidentreport/incidents/:id',
  function (req, res) {
    req.oracleMobile.database.delete(
      'FIF_Incidents', req.params.id).then(      
      function (result) {
        res.send(result.statusCode, result.result);
      },
      function (error) {
        res.send(error.statusCode, error.error);
      }
    );
  });

If the table has a primary key, then the response body looks like this:

{ "rowCount" : 1 } 

If the id is the key value for the table, then the response body looks like this:

{"items":[{"id":42}]}

Executing SQL on a Table

If neither delete(), get(), getAll(), insert(), nor merge() let you perform the database operation that you need to do, then use the sql method.

The sql method lets you execute SQL statements such as insert(), update(), merge(), delete(), or select(). You can use this method for complex actions, such as when you need to join tables, use aggregate functions like count() and sum(), or use a where clause to delete a set of rows.

Note that you can’t use the sql method to create a table or add columns to it. You must either use the insert() or merge() methods to create and restructure the table implicitly, or use the Database Management API to create and re-recreate it explicitly, as described in Database Management API. In addition, the predefined fields are not populated automatically when you use sql.

To use the the sql method:

• Set the required sql argument to the SQL statement that you want to execute. For example:

  SELECT COUNT("incidentReport") "reportCount"
  FROM "FIF_Incidents" WHERE "status" = :status

  See Preventing SQL Injection to learn about precautions that you should take when you write the SQL statement.

• If your SQL statement takes parameters, then you need to pass them in the required bindings argument, which is a JSON object. For example, if you use the SQL statement shown for the sql argument, then you would set bindings to {status:'Open'}. If the SQL statement doesn’t use parameters, then use null or {}.

Here’s an example of executing a SQL statement. In this example, the sql argument is set to a SQL statement that counts the number of rows in the FIF_Incidents table with a status of Open.

/**
 * Get Count of Open Incidents
 */
service.get('/mobile/custom/incidentreport/openReportCount',
  function (req, res) {
    req.oracleMobile.database.sql(
      'SELECT COUNT("incidentReport") "reportCount" ' +
      'FROM "FIF_Incidents" WHERE "status" = :status',
      {status: 'Open'}).then(    
      function (result) {
        res.status(statusCode).send (result.result);
      },
      function (error) {
        res.status(statusCode).send(error.error);
      }
    );
  });

Passing Parameters to the SQL Statement

You might want to let users specify some of the values in the SQL statement. For example, you might want your custom API to have a GET /incidents/count operation, which counts the number of incidents for a given status, and let the user specify which status to count by passing it as a request parameter. You use the bindings argument to pass the parameter to the executeSQL() method.

There are two ways to reference parameters in the SQL statement:

• Use the :name syntax to reference parameters by name. This is the preferred method. For example:

  Select SELECT COUNT("incidentReport") "reportCount"
         FROM "FIF_Incidents" WHERE "status" = :status

  To pass the named parameter, you use a JSON object like this:

  bindings = {status:'Open'}

• Use the ? syntax to reference parameters by the generic names arg1, arg2, arg3, and so on. This is called an anonymous parameter. For example:

  Select SELECT COUNT("incidentReport") "reportCount"
         FROM "FIF_Incidents" WHERE "status" = ?

  To pass the anonymous parameter, you use a JSON object like this:

  bindings = {arg1:'Open'}

Note that unlike anonymous parameters, named parameters can be bound at multiple places in a SQL statement. In the following example, the named parameters :TITLE and :TOTAL_GROSS are bound twice: once if there’s an UPDATE and once if there’s an INSERT.

MERGE INTO "Movies" t0 
    USING
        (SELECT :TITLE "TITLE" FROM DUAL) t1 
    ON 
        (t0."TITLE" = t1."TITLE")
    WHEN MATCHED THEN 
        UPDATE SET t0."TOTAL_GROSS" = :TOTAL_GROSS
    WHEN NOT MATCHED THEN
        INSERT (t0."TITLE", t0."TOTAL_GROSS") VALUES (:TITLE, :TOTAL_GROSS)

If you use anonymous parameters, then you must use a different generic parameter for each occurrence. For example, with the following SQL statement, you must pass in 4 parameters: arg1 and arg3 provide the title, and arg2 and arg4 provide the total gross:

MERGE INTO "Movies" t0 
    USING
        (SELECT ? "TITLE" FROM DUAL) t1 
    ON 
        (t0."TITLE" = t1."TITLE")
    WHEN MATCHED THEN 
        UPDATE SET t0."TOTAL_GROSS" = ?
    WHEN NOT MATCHED THEN
        INSERT (t0."TITLE", t0."TOTAL_GROSS") VALUES (?, ?)

Here’s an example of how to execute a SQL statement that has a parameter.

/**
 * Get Count of Incidents for a Given Status
 */
service.get('/mobile/custom/incidentreport/openReportCount',
  function (req, res) {
    req.oracleMobile.database.sql(
      'SELECT COUNT("incidentReport") "reportCount" ' +
      'FROM "FIF_Incidents" WHERE "status" = :status',
      {status: 'Open'}).then(    
      function (result) {
        res.status(statusCode).send (result.result);
      },
      function (error) {
        res.status(statusCode).send(error.error);
      }
    );
  });

Labeling Calculated Columns in Select Statements

As with all response bodies for this endpoint, the response body for a SELECT statement is in JSON format. To make it easier to extract a calculated value from the JSON object, always label the functions. Take, for example, the following SQL statement:

SELECT SUM("incidentReport") FROM "FIF_Incidents"

The JSON response looks like this:

{ "SUM(\"incidentReport\")" : 678 }

In this example, the function is labeled reportCount:

SELECT SUM("incidentReport") "reportCount" FROM "FIF_Incidents"

The JSON response looks like this:

{ "reportCount" : 678 }

Preserving Case in SQL Statements

By default, Oracle Database is case-insensitive. However, the tables and columns that you create using the Database Access API are case-sensitive. Therefore, you must enclose the table name, columns, and labels in SQL statements in double quotation marks ("..."). Otherwise, the call might not return any rows.

Say, for example, that your SQL statement is:

Select incidentReport from FIF_Incidents

Because the table and column names are not protected by double quotation marks, the SQL statement will not work as expected. You might get a status of 400 with a message that the table or view does not exist or that there is an invalid identifier.

Instead, use:

Select "incidentReport" from "FIF_Incidents"

Preventing SQL Injection

SQL injection is an attack technique that allows hackers access to databases by co-opting user input with a SQL block that can be interpreted by a backend database. To prevent this type of attack, you must ensure that SQL statements are never passed to the custom code from a mobile app. The SQL statements allowed by the Database Access API must reside in the custom code.

Here are some common SQL injection considerations:

• Passing SQL as User Input

• Preventing SQL Injection with Bind Parameters

Preventing Passing SQL to the Execute SQL Operation

Don’t let users pass SQL into your custom code for use in the executeSQL() method or the POST /mobile/platform/database/sql operation.

For example, don’t write code like the following example, which lets users put a SQL statement in the SQL header, and then pass that SQL statement to be executed. Instead, hard-code the SQL statement, and use parameters when necessary.

/**
 * Example of Code that Lets
 * Users Inject SQL
 */
service.get('/mobile/custom/incidentreport/openReportCount',
  function (req, res) {
    req.oracleMobile.database.sql(
      req.headers.sql
    ).then(    
      function (result) {
        res.status(statusCode).send (result.result);
      },
      function (error) {
        res.status(statusCode).send(error.error);
      }
    );
  });

Preventing SQL Injection with Bind Parameters

In this example, a hacker can pass an escaped SQL block in the status field of an input entry form:

/**
 * Example of Code that Lets
 * Users Inject SQL
 */
service.get('/mobile/custom/incidentreport/openReportCount',
  function (req, res) {
    req.oracleMobile.database.sql(
      'SELECT COUNT("incidentReport") "reportCount" ' +
      'FROM "FIF_Incidents" WHERE "status" = ' + 
      req.body.status
    ).then(    
      function (result) {
        res.status(statusCode).send (result.result);
      },
      function (error) {
        res.status(statusCode).send(error.error);
      }
    );
  });

To prevent this type of attack, use parameters as shown in this example:

service.get('/mobile/custom/incidentreport/openReportCount',
  function (req, res) {
    req.oracleMobile.database.executeSQL(
      'SELECT COUNT("incidentReport") "reportCount" ' +
      'FROM "FIF_Incidents" WHERE "status" = ' + 
      :status,
      {status: 'Open'} 
    ).then(    
      function (result) {
        res.status(statusCode).send (result.result);
      },
      function (error) {
        res.status(statusCode).send(error.error);
      }
    );
  });

Database Management API

In addition to the Database Access API, there’s also a Database Management API, which lets you manage the tables that you created through the Database Access API. This API lets you view table metadata, create, drop, and re-create tables.

You can access the Database Management API through custom API implementations and HTTP REST calls. To try out calls to the API, click to open the side menu. Next, click Developmentthen APIs. In the Platform APIs section located at the bottom of the page, click Database Management . For further details about each API operation, see Here, we give a brief overview of the Storage API endpoints. For detailed information, see Oracle Autonomous Mobile Cloud Enterprise REST API Reference..

Creating a Table Explicitly

You can create a table from a JSON object using the POST method for the /mobile/system/databaseManagement/tables endpoint. To restructure a table, use the PUT method for the same endpoint. The PUT method drops the existing table and re-creates it.

To create a table explicitly:

1. If you want to include predefined columns in the table, set the Oracle-Mobile-Extra-Fields header to a comma-separated list of the columns to include from amongst id, createdBy, createdOn, modifiedBy, and modifiedOn. If you don’t want any of these columns, specify none. The id column, which is a row key, is added to the table only if no primary key is specified.

2. Create the JSON object for the request body. The JSON attributes are:

   • name: The table name.

   • columns: An array of the table columns. For each column, specify:

     • name: The column name.

     • type: The data type. The binary data type is not supported.

     • size: (Optional) The size or precision of the column.

     • subSize: (Optional) For decimal columns, the scale of the column, meaning the number of places after the decimal point.

   • primaryKeys: An array of column names.

   • requiredColumns: An array of column names.

3. Call the POST method for the /mobile/system/databaseManagement/tables endpoint.

Here’s an example of a JSON object for creating a table. When used in a POST request, a table called Movies is created with the specified columns and primary key.

{ "name" : "Movies",
  "columns": [
    {"name": "title", "type": "string", "size": 50},
    {"name": "synopsis", "type": "string"},
    {"name": "inTheaters", "type": "boolean"},
    {"name": "releaseDate", "type": "dateTime"},
    {"name": "runningTime", "type": "integer", "size": 3},
    {"name": "totalGross", "type": "decimal", "size": 10, "subSize": 2}],
  "primaryKeys" : [ "title" ],
  "requiredColumns": ["title", "releaseDate" ]
}

The Database Management API creates and executes the following SQL statement based on this request. In this case, the Oracle-Mobile-Extra-Fields request header was set to none, so the table does not have any predefined fields.

CREATE TABLE "Movies" (
    "title" VARCHAR2(50) NOT NULL,
    "synopsis" VARCHAR2(4000),
    "inTheaters" CHAR(1),
    "releaseDate" TIMESTAMP NOT NULL,
    "runningTime" NUMBER(3,0),
    "totalGross" NUMBER(10,2),
    CONSTRAINT "Movies_PK" PRIMARY KEY ("title"))

This example also illustrates some of the data types allowed by the Database Management API and the Database Access API:

Type	Description	Size / Subsize	Database Type
string	A JSON string	Maximum of 4000 bytes	VARCHAR2
dateTime	An ISO- or date-formatted JSON string		TIMESTAMP
boolean	A JSON boolean		CHAR(1) “1” true, “0” false
decimal	A JSON number	Precision (the total number of digits). Optional. / Scale (number of decimal digits). Optional.	NUMBER NUMBER(size) NUMBER(*,subsize)
integer	A JSON number with no decimal digits		NUMBER(size,0) and NUMBER(*0)

The size and subSize attributes are optional. Don’t provide them for columns of type dateTime and boolean. As a best practice, unless you have a valid business constraint, don't provide size or subSize for integers and decimals because doing so limits what values are acceptable and makes it harder to resize the column. When possible, allow the database to size and store the value as efficiently as possible. However, you should provide the size attribute for string columns. The maximum size for a string column is 4000 characters. If you need to store a larger string, then you can use the Storage platform to store the object.