1# RDB Development 2 3## When to Use 4 5A relational database (RDB) store allows you to operate local data with or without native SQL statements based on SQLite. 6 7## Available APIs 8### Creating and Deleting an RDB Store 9 10The table below describes the APIs 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 RDB store name, storage mode, log mode, synchronization mode, and read-only mode, and whether to encrypt the RDB store.<br/> - **path**: path of the RDB store. <br>- **readOnly**: whether the RDB store is read-only. <br>- **storageMode**: storage mode. <br>- **encryptKey**: key used to encrypt the RDB store. <br>- **journalMode**: logging mode. <br>- **syncMode**: data synchronization mode. <br>- **databaseFileType**: RDB store type. <br>- **databaseFileSecurityLevel**: security level of the RDB store.| 17| RdbOpenCallback | int OnCreate(RdbStore &rdbStore) | Called when an RDB store is created. You can add the method for initializing the table structure and initialization data used by your application in this 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 an RDB store.| 22 23### Encrypting an RDB Store 24 25When 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 that the encryption key can be changed only for an encrypted RDB store.| 31 32### Using Predicates 33 34The RDB store provides **AbsRdbPredicates** for you to set database operation conditions. The **AbsRdbPredicates** class has the following child classes: 35 36- **RdbPredicates**: allows you to combine SQL statements by simply calling methods in this class, such as **equalTo**, **notEqualTo**, **groupBy**, **orderByAsc**, and **beginsWith**. With this class, you do not need to write complex SQL statements. 37- **RawRdbPredicates**: allows you to write complex SQL statements, such as setting **whereClause** and **whereArgs**. However, this class does not support APIs such as **equalTo**. 38 39 **Table 3** APIs for setting RDB predicates 40 | Class| API| Description| 41 | ---- | ---- | ---- | 42 | RdbPredicates | AbsPredicates *EqualTo(std::string field, std::string value) | Sets an **AbsPredicates** to match the field that is equal to the specified value.| 43 | RdbPredicates | AbsPredicates *NotEqualTo(std::string field, std::string value) | Sets an **AbsPredicates** to match the field that is not equal to the specified value.| 44 | RdbPredicates | AbsPredicates *BeginsWith(std::string field, std::string value) | Sets an **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 an **AbsPredicates** to match the field that is within the range specified by **low** and **high**.| 46 | RdbPredicates | AbsPredicates *OrderByAsc(std::string field) | Sets an **AbsPredicates** that sorts values 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 an **AbsPredicates** to specify the remote devices on the network with databases to be synchronized.| 50 | RdbPredicates | AbsRdbPredicates *InAllDevices() | Sets an **AbsPredicates** to connect to all remote devices on the network when synchronizing distributed databases.| 51 52 53### Managing Data in an RDB Store 54 55You can use the APIs provided by the RDB to insert, delete, update, and query local data. 56 57- Inserting data 58 59 Call **int Insert()** to insert data through **ValuesBucket**. If data is inserted, the row number of the data inserted is returned; otherwise, **-1** is returned. 60 61 **Table 4** API for inserting data 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**: name of the target table. <br/>- **initialValues**: data to insert. The data is stored in **ValuesBucket**. 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 **delete()** to delete the data that meets the conditions specified by **AbsRdbPredicates**. If 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**: number of rows to delete.<br> - **predicates**: table name and conditions for deleting the data. **AbsRdbPredicates** has the following classes:<br> - **RdbPredicates**: specifies query 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 **update()** to update data based on the passed data and the conditions specified by **AbsRdbPredicates**. If 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**: number of rows to update.<br> - **values**: new data stored in **ValuesBucket**.<br> - **predicates**: 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**: query conditions. **AbsRdbPredicates** has the following classes:<br> - **RdbPredicates**: specifies query conditions by calling its methods, such as **equalTo**.<br> - **RawRdbPredicates**: specifies the table name, **whereClause**, and **whereArgs** only.<br> - **columns**: 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**: native SQL statement.<br> - **selectionArgs**: parameter values corresponding to the placeholders in the SQL statements. Set it to **null** if the **select** statement has no placeholder. | 97 98### Obtaining the Query Result 99 100You can use the APIs provided by **ResultSet** to traverse and access the data you have queried. A result set can be regarded as a row of data in the queried result. The table below describes the APIs of **ResultSet**. 101 102**Table 8** APIs of **ResultSet** 103| Class| API| Description| 104| ---- | ---- | ---- | 105| ResultSet | int GoTo(int offset) | Moves forwards or backwards by the specified offset relative to its current position.| 106| ResultSet | int GoToRow(int position) | Moves to the specified row.| 107| ResultSet | int GoToNextRow() | Moves to the next row.| 108| ResultSet | int GoToPreviousRow() | Moves 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 of this result set.| 114| ResultSet | int GetColumnCount(int &count) | Obtains the number of columns of this result set.| 115| ResultSet | int GetString(int columnIndex, std::string &value) | Obtains the value in the specified column of the current row, in strings.| 116| ResultSet | int GetBlob(int columnIndex, std::vector\<uint8_t\> &blob) | Obtains the value in the specified column of the current row, in a byte array.| 117| ResultSet | int GetDouble(int columnIndex, double &value) | Obtains the value in the specified column of the current row, in double.| 118 119### Setting Distributed Tables 120 121Call **bool SetDistributedTables()** to set distributed tables for data operations across devices. 122 123**Table 9** API for setting distributed tables 124| Class| API| Description| 125| ---- | ---- | ---- | 126| RdbStore | bool SetDistributedTables(const std::vector<std::string>& tables) | Sets distributed tables.<br> **tables**: names of the distributed tables to set. | 127 128### Obtaining the Distributed Table Name for a Remote Device 129 130You can obtain the distributed table name for 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> 131 132**Table 10** API for obtaining the distributed table name of a remote device 133| Class| API| Description| 134| ---- | ---- | ---- | 135| 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**: ID of the remote device. <br>- **table**: name of the local table.| 136 137### Synchronizing Data Between Devices 138 139**Table 11** API for synchronizing data between devices 140| Class| API| Description| 141| ---- | ---- | ---- | 142| RdbStore | bool Sync(const SyncOption& option, const AbsRdbPredicates& predicate, const SyncCallback& callback) | Synchronizes data between devices. <br/>- **option**: synchronization options, which include **mode** and **isBlock**. **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. **isBlock** specifies whether the invocation of this function is blocked. <br>- **callback**: callback used to return the result. | 143 144### Registering an RDB Store Observer 145 146**Table 12** API for registering an observer 147| Class| API| Description| 148| ---- | ---- | ---- | 149| RdbStore | bool Subscribe(const SubscribeOption& option, RdbStoreObserver *observer) | Registers an observer for this RDB store to listen for distributed data changes. When data in the RDB store changes, a callback will be invoked to return the data changes. <br/>- **option**: subscription type.<br>- **observer**: observer that listens for data changes in the RDB store. | 150 151### Unregistering an RDB Store Observer 152 153**Table 13** API for unregistering an observer 154| Class| API| Description| 155| ---- | ---- | ---- | 156| RdbStore | bool UnSubscribe(const SubscribeOption& option, RdbStoreObserver *observer) | Unregisters the observer of the specified type. <br/>- **option**: subscription type to unregister.<br>- **observer**: observer to unregister. | 157 158### Backing Up and Restoring an RDB Store 159 160You can use the APIs provided by **rdbStore** to back up and restore local database files. 161 162- Backing up an RDB store 163 164 Call **int Backup()** to back up the current database file. **databasePath** specifies the name or path of the backup file to be generated. If the backup is successful, **0** is returned; otherwise, an error code is returned. 165 166 Table 14 API for backing up an RDB store 167 168 | Class| API| Description| 169 | ---- | ---- | ---- | 170 | RdbStore | int Backup(const std::string databasePath, const std::vector<uint8_t> destEncryptKey) | Backs up the current database file.<br>- **databasePath**: name or path of the backup file to generate.<br>- **destEncryptKey**: key used to encrypt the RDB store. Currently, only non-encrypted RDB stores can be backed up. | 171 172- Restoring an RDB store 173 174 Call **int Restore()** to restore an RDB from the backup file. **backupPath** specifies the name or path of the backup file. If the restore is successful, **0** is returned; otherwise, an error code is returned. 175 176 Table 15 API for restoring an RDB store 177 178 | Class| API| Description| 179 | ---- | ---- | ---- | 180 | RdbStore | int Restore(const std::string backupPath, const std::vector<uint8_t> &newKey) | Restore an RDB store.<br> - **backupPath**: name or path of the backup file.<br> - **newKey**: key used to encrypt the RDB store. Currently, only non-encrypted RDB stores can be restored. | 181 182### Transaction 183 184 A transaction is a unit of work performed in a database. If a transaction is successful, **0** is returned. Otherwise, an error code is returned. 185 186 Table 16 Transaction APIs 187 188 | Class| API| Description| 189 | ---- | ---- | ---- | 190 | RdbStore | int BeginTransaction() | Starts a transaction.| 191 | RdbStore | int Commit() | Commits the changes.| 192 | RdbStore | int RollBack() | Rolls back the changes.| 193 194## Constraints 195 196None. 197 198## How to Develop 199 2001. Create an RDB store. 201 202 a. Configure the RDB store attributes, including the RDB store name, storage mode, and read-only mode. 203 204 b. Initialize the table structure and related data in the RDB store. 205 206 c. Create an RDB store. 207 208 The sample code is as follows: 209 ```c++ 210 const std::string DATABASE_NAME = RDB_TEST_PATH + "RdbStoreTest.db"; 211 const std::string CREATE_TABLE_TEST = "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, salary REAL, blobType BLOB)"; 212 213 class OpenCallback : public RdbOpenCallback { 214 public: 215 int OnCreate(RdbStore &rdbStore) override; 216 int OnUpgrade(RdbStore &rdbStore, int oldVersion, int newVersion) override; 217 }; 218 219 int OpenCallback::OnCreate(RdbStore &store) 220 { 221 return store.ExecuteSql(CREATE_TABLE_TEST); 222 } 223 224 RdbStoreConfig config(DATABASE_NAME); 225 OpenCallback callback; 226 227 std::shared_ptr<RdbStore> store = RdbHelper::GetRdbStore(config, 1, callback, 0); 228 ``` 229 2302. Insert data. 231 232 a. Create a **ValuesBucket** to store the data you need to insert. 233 234 b. Call the **insert()** method to insert data into the RDB store. 235 236 c. Create an RDB store. 237 238 The sample code is as follows: 239 ```c++ 240 ValuesBucket values; 241 242 values.PutInt("id", 1); 243 values.PutString("name", std::string("Tom")); 244 values.PutInt("age", 18); 245 values.PutDouble("salary", 100.5); 246 values.PutBlob("blobType", std::vector<uint8_t>{ 1, 2, 3 }); 247 store->Insert(id, "test", values); 248 ``` 249 2503. Query data. 251 252 a. Create a predicate that specifies query conditions. 253 254 b. Specify the data columns to return in the result set. 255 256 c. Call the **query()** method to query data. 257 258 d. Call the **ResultSet** APIs to traverse data in the result set. 259 260 The sample code is as follows: 261 ```c++ 262 std::vector<std::string> columns = {"id", "name", "age", "salary"}; 263 264 RdbPredicates predicates("test"); 265 predicates.EqualTo("age", "25")->OrderByAsc("salary"); 266 std::unique_ptr<ResultSet> resultSet = store->Query(predicates, columns) 267 resultSet.goToNextRow(); 268 ``` 269 2704. Set the distributed tables to be synchronized. 271 272 Call the **SetDistributedTables()** method to set the distributed tables to be synchronized. 273 274 The sample code is as follows: 275 276 ```c++ 277 store->SetDistributedTables("test"); 278 ``` 279 2805. Synchronize data. 281 282 a. Set the data synchronization mode and block status. 283 284 b. Constructs an **AbsPredicates** object to specify remote devices within the network to be synchronized. 285 286 c. Call the **Sync()** method to synchronize data. 287 288 The sample code is as follows: 289 290 ```c++ 291 SyncOption option; 292 option.mode = PUSH; 293 option.isBlock = true; 294 AbsRdbPredicates predicate("test"); 295 predicate.InAllDevices(); 296 store->Sync(option, predicate, [] (const SyncResult& result) { 297 for (const auto& [device, status] : result) { 298 LogI("device=%s status=%d", device.c_str(), status); 299 } 300 }); 301 ``` 302 3036. Subscribe to distributed data. 304 305 a. Override the **OnChange()** function. 306 307 b. Define the distributed data subscription type. 308 309 c. Call APIs to subscribe to or unsubscribe from distributed data. 310 311 The sample code is as follows: 312 313 ```c++ 314 class MyObserver : public RdbStoreObserver { 315 public: 316 void OnChange(const std::vector<std::string>& devices) override { 317 for (const auto& device : devices) { 318 LOGI("device=%s data change", device.c_str()); 319 } 320 } 321 }; 322 323 SubscribeOption option; 324 option.mode = SubscribeMode::REMOTE; 325 MyObserver observer; 326 store->Subscribe(option, &observer); // Subscribe to distributed data. 327 328 store->UnSubscribe(option, &observer); // Unsubscribe from distributed data. 329 ``` 330 3317. Query data across devices. 332 333 a. Obtain the distributed table name for a remote device based on the local table name. 334 335 b. Run SQL statements to query data in the RDB store of the remote device. 336 337 The sample code is as follows: 338 ```c++ 339 std::string tableName = store->ObtainDistributedTableName("123456789abcd", "test"); 340 auto resultSet = store->QuerySql("SELECT * from ?;", tableName); 341 ``` 342 3438. Back up and restore an RDB store. 344 345 a. Back up the current RDB store. 346 347 b. Restore the RDB store from the specified backup file. 348 349 The sample code is as follows: 350 ```c++ 351 std::string backupName = "backup.db"; // Name of the database backup file to generate. 352 std::vector<uint8_t> key; // Key used to encrypt the RDB store. 353 int errno = store->Backup(backupName, key); 354 errno = store->Restore(backupName, key); 355 ``` 356