subsys-data-relational-database-guide.md

# RDB Development

## When to Use

On the basis of the SQLite database, the RDB allows you to operate data with or without native SQL statements. In OpenHarmony, an RDB is also called RDB store.

## Available APIs
### Creating and Deleting an RDB Store

The following table describes APIs available for creating and deleting an RDB store.

Table 1 APIs for creating and deleting an RDB store

| Class| API| Description|
|  ----  |  ----  |  ----  |
| RdbStoreConfig | RdbStoreConfig(const std::string &path, <br> StorageMode storageMode = StorageMode::MODE_DISK, <br> bool readOnly = false, <br> const std::vector<uint8_t> &encryptKey = std::vector<uint8_t>(), <br> const std::string &journalMode = "", <br> const std::string &syncMode = "", <br> const std::string &databaseFileType = "", <br> const std::string &databaseFileSecurityLevel = "") | Configures an RDB store, including setting the name, storage mode, log mode, synchronization mode, and read-only mode, and encrypting the database. <ul><li> **path**: path of the database. </li> <li> **readOnly**: whether the database is read-only. </li> <li> **storageMode**: storage mode. </li> <li> **encryptKey**: key used for database encryption. </li> <li> **journalMode**: database logging mode. </li> <li> **syncMode**: data synchronization mode. </li> <li> **databaseFileType**: database type. </li> <li> **databaseFileSecurityLevel**: security level. </li></ul>|
| RdbOpenCallback | int OnCreate(RdbStore &rdbStore) | Called when an RDB store is created. You can add the method for initializing the table structure and add initialization data used by your application in the callback.|
| RdbOpenCallback | int OnUpgrade(RdbStore &rdbStore, int currentVersion, int targetVersion) | Called when the RDB store is upgraded.|
| RdbOpenCallback | int OnDowngrade(RdbStore &rdbStore, int currentVersion, int targetVersion) | Called when the RDB store is downgraded.|
| RdbHelper | std::shared_ptr\<RdbStore\> GetRdbStore(const RdbStoreConfig &config, int version, RdbOpenCallback &openCallback, int &errCode) | Creates or obtains an RDB store.|
| RdbHelper | int DeleteRdbStore(const std::string &path) | Deletes the specified RDB store.|

### Encrypting an RDB Store

The RDB provides the database encryption capability. When creating an RDB store , you can add a key for security purposes. After that, the RDB store can be accessed only with the correct key.

Table 2 API for changing the key
| Class| API| Description|
|  ----  |  ----  |  ----  |
| RdbStore | int ChangeEncryptKey(const std::vector<uint8_t> &newKey) | Changes the encryption key for an RDB store. <br>Note: The encryption key can be changed only for an encrypted database.|

### Using Predicates

The RDB provides **AbsRdbPredicates** for you to set database operation conditions. The **AbsRdbPredicates** has the following child classes:

- **RdbPredicates**: With this class, you do not need to write complex SQL statements. Instead, you can combine SQL statements simply by calling methods in this class, such as **equalTo**, **notEqualTo**, **groupBy**, **orderByAsc**, and **beginsWith**.
- **RawRdbPredicates**: With this class, you can set **whereClause** and **whereArgs**, but cannot call methods such as **equalTo**.

  Table 7 APIs for RDB predicates
  | Class| API| Description|
  |  ----  |  ----  |  ----  |
  | RdbPredicates | AbsPredicates *EqualTo(std::string field, std::string value) | Sets the **AbsPredicates** to match the field that is equal to the specified value.|
  | RdbPredicates | AbsPredicates *NotEqualTo(std::string field, std::string value) | Sets the **AbsPredicates** to match the field that is not equal to the specified value.|
  | RdbPredicates | AbsPredicates *BeginsWith(std::string field, std::string value) | Sets the **AbsPredicates** to match the field that starts with the specified value.|
  | RdbPredicates | AbsPredicates *Between(std::string field, std::string low, std::string high) | Sets the **AbsPredicates** to match the field that is within the range specified by **low** and **high**.|
  | RdbPredicates | AbsPredicates *OrderByAsc(std::string field) | Sets the **AbsPredicates** to match the column with values sorted in ascending order.|
  | RdbPredicates | void SetWhereClause(std::string whereClause) | Sets **whereClause**.|
  | RdbPredicates | void SetWhereArgs(std::vector\<std::string\> whereArgs) | Sets **whereArgs**, which indicates the value of the placeholder in **whereClause**.|

### Managing Data in an RDB Store

The RDB provides APIs for inserting, deleting, updating, and querying data in the local RDB store.

- Inserting data

  The RDB provides an API for inserting data through **ValuesBucket** in a data table. If the data is added, the row number of the data inserted is returned; otherwise, **-1** is returned.

  Table 3 API for inserting data to a data table

  | Class| API| Description|
  |  ----  |  ----  |  ----  |
  | RdbStore | int Insert(int64_t &outRowId, const std::string &table, const ValuesBucket &initialValues) | Inserts data based on the passed table name and data in **ValuesBucket**. <ul><li>**table**: specifies the name of the target table. </li><li> **initialValues**: specifies the data, stored in **ValuesBucket**, to insert. A series of **put()** methods, such as **PutString(const std::string &columnName, const std::string &value)** and **PutDouble(const std::string &columnName, double value)**, are provided to add data to **ValuesBucket**.</li></ul> |

- Deleting data
  
  Call the **delete()** method to delete data meeting the conditions specified by **AbsRdbPredicates**. If the data is deleted, the row number of the deleted data is returned; otherwise, **0** is returned.

  Table 5 API for deleting data
  | Class| API| Description|
  |  ----  |  ----  |  ----  |
  | RdbStore | int Delete(int &deletedRows, const AbsRdbPredicates &predicates) | Deletes data. <ul><li> **deletedRows**: specifies the number of rows to delete. </li><li> **predicates**: specifies the table name and conditions for deleting the data.  **AbsRdbPredicates** has the following classes: <ul><li> **RdbPredicates**: specifies delete conditions by calling its methods, such as **equalTo**. </li><li> **RawRdbPredicates**: specifies the table name, **whereClause** and **whereArgs** only. </li></ul></li></ul> |

- Updating data

  Call the **update()** method to modify data based on the passed data and the conditions specified by **AbsRdbPredicates**. If the data is updated, the row number of the updated data is returned; otherwise, **0** is returned.

  Table 4 API for updating data
  | Class| API| Description|
  |  ----  |  ----  |  ----  |
  | RdbStore | int Update(int &changedRows, const ValuesBucket &values, const AbsRdbPredicates &predicates) | Updates the data that meets the conditions specified by predicates. <ul><li> **changedRows**: specifies the number of rows to update. </li><li> **values**: specifies the new data stored in **ValuesBucket**. </li><li> **predicates**: specifies the table name and conditions for the update operation.  **AbsRdbPredicates** has the following classes: <ul><li> **RdbPredicates**: specifies update conditions by calling its methods, such as **equalTo**. </li><li> **RawRdbPredicates**: specifies the table name, **whereClause** and **whereArgs** only. </li></ul></li></ul> |

- Querying data

  You can query data in an RDB store in either of the following ways:

  - Call the **query()** method to query data based on the predicates, without passing any SQL statement.
  - Run the native SQL statement.

  Table 6 APIs for querying data
  | Class| API| Description|
  |  ----  |  ----  |  ----  |
  | RdbStore | std::unique_ptr<AbsSharedResultSet> Query(const AbsRdbPredicates &predicates, const std::vector\<std::string\> columns) | Queries data. <ul><li> **predicates**: specifies the query conditions.  **AbsRdbPredicates** has the following classes: <ul><li> **RdbPredicates**: specifies the query conditions by calling its methods, such as **equalTo**. </li><li> **RawRdbPredicates**: specifies the table name, **whereClause**, and **whereArgs** only. </li></ul> <li> **columns**: specifies the number of columns returned.</li></ul></li></ul> |
  | RdbStore | std::unique_ptr<AbsSharedResultSet> QuerySql(const std::string &sql, const std::vector\<std::string\> &selectionArgs = std::vector\<std::string\>()) | Executes the native SQL statements to query data. <ul><li> **sql**: specifies the native SQL statement. </li><li> **selectionArgs**: specifies the parameter values corresponding to the placeholders in the SQL statements. Set it to **null** if the **select** statement has no placeholder.</li></ul> |

### Using the Result Set

A result set can be regarded as rows of data in the queried results. It allows you to traverse and access the data you have queried. The following table describes the external APIs of **ResultSet**.

  Table 8 APIs for using the result set
  | Class| API| Description|
  |  ----  |  ----  |  ----  |
  | ResultSet | int GoTo(int offset) | Moves the result set forwards or backwards by the specified offset relative to its current position.|
  | ResultSet | int GoToRow(int position) | Moves the result set to the specified row.|
  | ResultSet | int GoToNextRow() | Moves the result set to the next row.|
  | ResultSet | int GoToPreviousRow() | Moves the result set to the previous row.|
  | ResultSet | int IsStarted(bool &result) | Checks whether the result set has been moved.|
  | ResultSet | int IsEnded(bool &result) | Checks whether the result set is moved after the last line.| 
  | ResultSet | int IsAtFirstRow(bool &result) | Checks whether the result set is located in the first row.|
  | ResultSet | int IsAtLastRow(bool &result) | Checks whether the result set is located in the last row.|
  | ResultSet | int GetRowCount(int &count) | Obtains the number of rows in the result set.|
  | ResultSet | int GetColumnCount(int &count) | Obtains the number of columns in the result set.|
  | ResultSet | int GetString(int columnIndex, std::string &value) | Obtains the values in the specified column of the current row, in strings.|
  | ResultSet | int GetBlob(int columnIndex, std::vector\<uint8_t\> &blob) | Obtains the values in the specified column of the current row, in a byte array.|
  | ResultSet | int GetDouble(int columnIndex, double &value) | Obtains the values in the specified column of the current row, in double.|

## Constraints

None.

## How to Develop

1. Create an RDB store.

    a. Configure the RDB store attributes, including the database name, storage mode, and read-only mode.

    b. Initialize the table structure and related data in the RDB store.

    c. Create an RDB store.

    The sample code is as follows:
    ```
    const std::string DATABASE_NAME = RDB_TEST_PATH + "RdbStoreTest.db";
    const CREATE_TABLE_TEST = "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, salary REAL, blobType BLOB)";

    class OpenCallback : public RdbOpenCallback {
    public:
        int OnCreate(RdbStore &rdbStore) override;
        int OnUpgrade(RdbStore &rdbStore, int oldVersion, int newVersion) override;
    };

    int OpenCallback::OnCreate(RdbStore &store)
    {
        return store.ExecuteSql(CREATE_TABLE_TEST);
    }

    RdbStoreConfig config(DATABASE_NAME);
    OpenCallback callback;

    std::shared_ptr<RdbStore> store = RdbHelper::GetRdbStore(config, 1, callback, 0); 
    ```

2. Insert data.

    a. Create a **ValuesBucket** to store the data you need to insert.

    b. Call the **insert()** method to insert data into the RDB store.

    The sample code is as follows:
    ```
    ValuesBucket values;

    values.PutInt("id", 1);
    values.PutString("name", std::string("Tom"));
    values.PutInt("age", 18);
    values.PutDouble("salary", 100.5);
    values.PutBlob("blobType", std::vector<uint8_t>{ 1, 2, 3 });
    store->Insert(id, "test", values);
    ```

3. Query data.

    a. Create a predicate that specifies query conditions.

    b. Specify the data columns to return in the result set.

    c. Call the **query()** method to query data.

    d. Call the **ResultSet** APIs to traverse data in the result set.

    The sample code is as follows:
    ```
    std::vector<std::string> columns = {"id", "name", "age", "salary"};

    RdbPredicates predicates("test");
    predicates.EqualTo("age", "25")->OrderByAsc("salary");
    std::unique_ptr<ResultSet> resultSet  = store->Query(predicates, columns)
    resultSet.goToNextRow();
    ```