1# RDB Development 2 3## When to Use 4 5On 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. 6 7## Available APIs 8### Creating and Deleting an RDB Store 9 10The following table describes APIs available for creating and deleting an RDB store. 11 12**Table 1** APIs for creating and deleting an RDB store 13 14| Class| API| Description| 15| ---- | ---- | ---- | 16| 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 RDB store.<br/> - **path**: path of the database.<br/> - **readOnly**: whether the database is read-only.<br/> - **storageMode**: storage mode.<br/> - **encryptKey**: key used for database encryption.<br/> - **journalMode**: database logging mode.<br/> - **syncMode**: data synchronization mode.<br/> - **databaseFileType**: database type.<br/> - **databaseFileSecurityLevel**: security level. | 17| 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.| 18| RdbOpenCallback | int OnUpgrade(RdbStore &rdbStore, int currentVersion, int targetVersion) | Called when the RDB store is upgraded.| 19| RdbOpenCallback | int OnDowngrade(RdbStore &rdbStore, int currentVersion, int targetVersion) | Called when the RDB store is downgraded.| 20| RdbHelper | std::shared_ptr\<RdbStore\> GetRdbStore(const RdbStoreConfig &config, int version, RdbOpenCallback &openCallback, int &errCode) | Creates or obtains an RDB store.| 21| RdbHelper | int DeleteRdbStore(const std::string &path) | Deletes the specified RDB store.| 22 23### Encrypting an RDB Store 24 25The 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. 26 27**Table 2** API for changing the key 28| Class| API| Description| 29| ---- | ---- | ---- | 30| 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 RDB store.| 31 32### Using Predicates 33 34The RDB provides **AbsRdbPredicates** for you to set database operation conditions. The **AbsRdbPredicates** has the following child classes: 35 36- **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**. 37- **RawRdbPredicates**: With this class, you can set **whereClause** and **whereArgs**, but cannot call methods such as **equalTo**. 38 39 **Table 3** APIs for RDB store predicates 40 | Class| API| Description| 41 | ---- | ---- | ---- | 42 | RdbPredicates | AbsPredicates *EqualTo(std::string field, std::string value) | Sets the **AbsPredicates** to match the field that is equal to the specified value.| 43 | RdbPredicates | AbsPredicates *NotEqualTo(std::string field, std::string value) | Sets the **AbsPredicates** to match the field that is not equal to the specified value.| 44 | RdbPredicates | AbsPredicates *BeginsWith(std::string field, std::string value) | Sets the **AbsPredicates** to match the field that starts with the specified value.| 45 | 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**.| 46 | RdbPredicates | AbsPredicates *OrderByAsc(std::string field) | Sets the **AbsPredicates** to match the column with values sorted in ascending order.| 47 | RdbPredicates | void SetWhereClause(std::string whereClause) | Sets **whereClause**.| 48 | RdbPredicates | void SetWhereArgs(std::vector\<std::string\> whereArgs) | Sets **whereArgs**, which indicates the value of the placeholder in **whereClause**.| 49 | RdbPredicates | AbsRdbPredicates *InDevices(std::vector<std::string>& devices) | Sets the **AbsPredicates** to specify the remote devices on the network with databases to be synchronized.| 50 | RdbPredicates | AbsRdbPredicates *InAllDevices() | Sets the **AbsPredicates** to connect to all remote devices on the network when synchronizing distributed databases.| 51 52 53### Managing Data in an RDB Store 54 55The RDB provides APIs for inserting, deleting, updating, and querying data in the local RDB store. 56 57- Inserting data 58 59 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. 60 61 **Table 4** APIs for inserting data tables 62 63 | Class| API| Description| 64 | ---- | ---- | ---- | 65 | 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**. <br/>- **table**: specifies the name of the target table.<br/> - **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**. | 66 67- Deleting data 68 69 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. 70 71 **Table 5** API for deleting data 72 | Class| API| Description| 73 | ---- | ---- | ---- | 74 | RdbStore | int Delete(int &deletedRows, const AbsRdbPredicates &predicates) | Deletes data. <br/>- **deletedRows**: specifies the number of rows to delete.<br/> - **predicates**: specifies the table name and conditions for deleting the data.<br/> **AbsRdbPredicates** has the following classes:<br/> - **RdbPredicates**: specifies update conditions by calling its methods, such as **equalTo**.<br/> - **RawRdbPredicates**: specifies the table name, **whereClause** and **whereArgs** only. | 75 76- Updating data 77 78 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. 79 80 **Table 6** API for updating data 81 | Class| API| Description| 82 | ---- | ---- | ---- | 83 | RdbStore | int Update(int &changedRows, const ValuesBucket &values, const AbsRdbPredicates &predicates) | Updates the data that meets the conditions specified by predicates.<br/> - **changedRows**: specifies the number of rows to update.<br/> - **values**: specifies the new data stored in **ValuesBucket**.<br/> - **predicates**: specifies the table name and conditions for the update operation. **AbsRdbPredicates** has the following classes: <br/>- **RdbPredicates**: specifies update conditions by calling its methods, such as **equalTo**.<br/> - **RawRdbPredicates**: specifies the table name, **whereClause** and **whereArgs** only. | 84 85- Querying data 86 87 You can query data in an RDB store in either of the following ways: 88 89 - Call the **query()** method to query data based on the predicates, without passing any SQL statement. 90 - Run the native SQL statement. 91 92 **Table 7** APIs for querying data 93 | Class| API| Description| 94 | ---- | ---- | ---- | 95 | RdbStore | std::unique_ptr<AbsSharedResultSet> Query(const AbsRdbPredicates &predicates, const std::vector\<std::string\> columns) | Queries data. <br/>- **predicates**: specifies the query conditions. **AbsRdbPredicates** has the following classes: <br/>- **RdbPredicates**: specifies update conditions by calling its methods, such as **equalTo**. <br/>- **RawRdbPredicates**: specifies the table name, **whereClause**, and **whereArgs** only. <br/>- **columns**: specifies the number of columns returned. | 96 | 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. <br/>- **sql**: specifies the native SQL statement. <br/>- **selectionArgs**: specifies the parameter values corresponding to the placeholders in the SQL statements. Set it to **null** if the **select** statement has no placeholder. | 97 98### Using the Result Set 99 100A 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**. 101 102**Table 8** APIs for using the result set 103| Class| API| Description| 104| ---- | ---- | ---- | 105| ResultSet | int GoTo(int offset) | Moves the result set forwards or backwards by the specified offset relative to its current position.| 106| ResultSet | int GoToRow(int position) | Moves the result set to the specified row.| 107| ResultSet | int GoToNextRow() | Moves the result set to the next row.| 108| ResultSet | int GoToPreviousRow() | Moves the result set to the previous row.| 109| ResultSet | int IsStarted(bool &result) | Checks whether the result set has been moved.| 110| ResultSet | int IsEnded(bool &result) | Checks whether the result set is moved after the last line.| 111| ResultSet | int IsAtFirstRow(bool &result) | Checks whether the result set is located in the first row.| 112| ResultSet | int IsAtLastRow(bool &result) | Checks whether the result set is located in the last row.| 113| ResultSet | int GetRowCount(int &count) | Obtains the number of rows in the result set.| 114| ResultSet | int GetColumnCount(int &count) | Obtains the number of columns in the result set.| 115| ResultSet | int GetString(int columnIndex, std::string &value) | Obtains the values in the specified column of the current row, in strings.| 116| 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.| 117| ResultSet | int GetDouble(int columnIndex, double &value) | Obtains the values in the specified column of the current row, in double.| 118 119### Setting a List of Distributed Tables 120 121You can set a list of distributed tables for data operations across devices. 122 123**Table 9** API for setting a distributed table list 124| Class| API| Description| 125| ---- | ---- | ---- | 126| RdbStore | bool SetDistributedTables(const std::vector<std::string>& tables) | Sets a list of distributed tables.<br/> - **tables**: specifies the names of the distributed tables to set. | 127 128### Obtaining the Distributed Table Name for a Remote Device 129 130When querying the RDB store of a remote device, you need to use the distributed table name. You can obtain the distributed table name of a remote device based on the local table name. 131 132**Table 10** API for obtaining the distributed table name of a remote device 133 134| Class| API| Description| 135| ---- | ---- | ---- | 136| RdbStore | std::string ObtainDistributedTableName(const std::string& device, const std::string& table) | Obtains the distributed table name of a remote device based on the local table name. The distributed table name can be used to query the RDB store of the remote device.<br> - **device**: specifies the ID of the remote device.<br/> - **table**: specifies the name of the local table. | 137 138### Synchronizing Data Between Devices 139 140**Table 11** API for cross-device data synchronization 141 142| Class| API| Description| 143| ---- | ---- | ---- | 144| RdbStore | bool Sync(const SyncOption& option, const AbsRdbPredicates& predicate, const SyncCallback& callback) | Synchronizes data between devices.<br/> - **option**: specifies synchronization options, which include the following: <br/>**mode**: specifies how data is synchronized. The value **push** means to push data from the local device to the remote device; the value **pull** means to pull data from the remote device to the local device. <br/>**isBlock**: specifies whether the invocation of this function is blocked. <br/>- **callback**: specifies the callback used to return the result. | 145 146### Registering an RDB Store Observer 147 148**Table 12** API for registering an observer 149 150| Class| API| Description| 151| ---- | ---- | ---- | 152| RdbStore | bool Subscribe(const SubscribeOption& option, RdbStoreObserver *observer) | Registers an observer for this RDB store to subscribe to distributed data changes. When data in the RDB store changes, a callback will be invoked to return the data changes.<br/> - **option**: specifies the subscription type.<br/>- **observer**: specifies the observer of data changes in the RDB store. | 153 154### Unregistering an RDB Store Observer 155 156**Table 13** API for unregistering an observer 157 158| Class| API| Description| 159| ---- | ---- | ---- | 160| RdbStore | bool UnSubscribe(const SubscribeOption& option, RdbStoreObserver *observer) | Unregisters the observer of the specified type to unsubscribe from distributed data changes.<br/> - **option**: specifies the subscription type to unregister.<br/>- **observer**: specifies the observer to unregister. | 161 162## Constraints 163 164None. 165 166## How to Develop 167 1681. Create an RDB store. 169 170 a. Configure the RDB store attributes, including the RDB store name, storage mode, and read-only mode. 171 172 b. Initialize the table structure and related data in the RDB store. 173 174 c. Create an RDB store. 175 176 The sample code is as follows: 177 ``` 178 const std::string DATABASE_NAME = RDB_TEST_PATH + "RdbStoreTest.db"; 179 const CREATE_TABLE_TEST = "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, salary REAL, blobType BLOB)"; 180 181 class OpenCallback : public RdbOpenCallback { 182 public: 183 int OnCreate(RdbStore &rdbStore) override; 184 int OnUpgrade(RdbStore &rdbStore, int oldVersion, int newVersion) override; 185 }; 186 187 int OpenCallback::OnCreate(RdbStore &store) 188 { 189 return store.ExecuteSql(CREATE_TABLE_TEST); 190 } 191 192 RdbStoreConfig config(DATABASE_NAME); 193 OpenCallback callback; 194 195 std::shared_ptr<RdbStore> store = RdbHelper::GetRdbStore(config, 1, callback, 0); 196 ``` 197 1982. Insert data. 199 200 a. Create a **ValuesBucket** to store the data you need to insert. 201 202 b. Call the **insert()** method to insert data into the RDB store. 203 204 c. Create an RDB store. 205 206 The sample code is as follows: 207 ``` 208 ValuesBucket values; 209 210 values.PutInt("id", 1); 211 values.PutString("name", std::string("Tom")); 212 values.PutInt("age", 18); 213 values.PutDouble("salary", 100.5); 214 values.PutBlob("blobType", std::vector<uint8_t>{ 1, 2, 3 }); 215 store->Insert(id, "test", values); 216 ``` 217 2183. Query data. 219 220 a. Create a predicate that specifies query conditions. 221 222 b. Specify the data columns to return in the result set. 223 224 c. Call the **query()** method to query data. 225 226 d. Call the **ResultSet** APIs to traverse data in the result set. 227 228 The sample code is as follows: 229 ``` 230 std::vector<std::string> columns = {"id", "name", "age", "salary"}; 231 232 RdbPredicates predicates("test"); 233 predicates.EqualTo("age", "25")->OrderByAsc("salary"); 234 std::unique_ptr<ResultSet> resultSet = store->Query(predicates, columns) 235 resultSet.goToNextRow(); 236 ``` 237 2384. Set the distributed tables to be synchronized. 239 240 Call the **SetDistributedTables()** method to set the distributed tables to be synchronized. 241 242 The sample code is as follows: 243 244 ``` 245 store->SetDistributedTables("test"); 246 ``` 247 2485. Synchronize data. 249 250 a. Set the data synchronization mode and block status. 251 252 b. Constructs an **AbsPredicates** object to specify remote devices within the network to be synchronized. 253 254 c. Call the **Sync()** method to synchronize data. 255 256 The sample code is as follows: 257 258 ``` 259 SyncOption option; 260 option.mode = PUSH; 261 option.isBlock = true; 262 AbsRdbPredicates predicate("test"); 263 predicate.InAllDevices(); 264 store->Sync(option, predicate, [] (const SyncResult& result) { 265 for (const auto& [device, status] : result) { 266 LogI("device=%s status=%d", device.c_str(), status); 267 } 268 }); 269 ``` 270 2716. Subscribe to distributed data. 272 273 a. Override the **OnChange()** function. 274 275 b. Define the distributed data subscription type. 276 277 c. Call APIs to subscribe to or unsubscribe from distributed data. 278 279 The sample code is as follows: 280 281 ``` 282 class MyObserver : public RdbStoreObserver { 283 public: 284 void OnChange(const std::vector<std::string>& devices) override { 285 for (const auto& device : devices) { 286 LOGI("device=%s data change", device.c_str()); 287 } 288 } 289 }; 290 291 SubscribeOption option; 292 option.mode = SubscribeMode::REMOTE; 293 MyObserver observer; 294 store->Subscribe(option, &observer); // Subscribe to distributed data. 295 296 store->UnSubscribe(option, &observer); // Unsubscribe from distributed data. 297 ``` 298 2997. Query data across devices. 300 301 a. Obtain the distributed table name for a remote device based on the local table name. 302 303 b. Run SQL statements to query data in the RDB store of the remote device. 304 305 The sample code is as follows: 306 ``` 307 std::string tableName = store->ObtainDistributedTableName("123456789abcd", "test"); 308 auto resultSet = store->QuerySql("SELECT * from ?;", tableName); 309 ``` 310