1# RDB Development 2 3## When to Use 4 5A relational database (RDB) store allows you to manage local data with or without native SQL statements based on SQLite. 6 7 8## Available APIs 9 10Most of the RDB store APIs are asynchronous interfaces, which can use a callback or promise to return the result. This document uses the promise-based APIs as an example. For more information about the APIs, see [RDB Store](../reference/apis/js-apis-data-relationalStore.md). 11 12### Creating or Deleting an RDB Store 13 14The following table describes the APIs for creating and deleting an RDB store. 15 16**Table 1** APIs for creating and deleting an RDB store 17 18| API | Description | 19| ------------------------------------------------------------ | ------------------------------------------------------------ | 20| getRdbStore(context: Context, config: StoreConfig): Promise<RdbStore> | Obtains an RDB store. This API uses a promise to return the result. You can set parameters for the RDB store based on service requirements and call APIs to perform data operations.<br>- **context**: application context.<br>- **config**: configuration of the RDB store.| 21| deleteRdbStore(context: Context, name: string): Promise<void> | Deletes an RDB store. This API uses a promise to return the result.<br>- **context**: application context.<br>- **name**: name of the RDB store to delete.| 22 23### Managing Data in an RDB Store 24 25The RDB provides APIs for inserting, deleting, updating, and querying data in a local RDB store. 26 27- **Inserting Data** 28 29 The RDB provides APIs for inserting data through a **ValuesBucket** in a data table. If the data is inserted, the row ID of the data inserted will be returned; otherwise, **-1** will be returned. 30 31 **Table 2** API for inserting data 32 33 34 | Class | API | Description | 35 | ---------- | ------------------------------------------------------------ | ------------------------------------------------------------ | 36 | RdbStore | insert(table: string, values: ValuesBucket): Promise<number> | Inserts a row of data into a table. This API uses a promise to return the result.<br>If the operation is successful, the row ID will be returned; otherwise, **-1** will be returned.<br>- **table**: name of the target table.<br>- **values**: data to be inserted into the table.| 37 38- **Updating Data** 39 40 Call **update()** to pass in new data and specify the update conditions by using **RdbPredicates**. If the data is updated, the number of rows of the updated data will be returned; otherwise, **0** will be returned. 41 42 **Table 3** API for updating data 43 44 45 | Class | API | Description | 46 | ---------- | ------------------------------------------------------------ | ------------------------------------------------------------ | 47 | RdbStore | update(values: ValuesBucket, predicates: RdbPredicates): Promise<number> | Updates data based on the specified **RdbPredicates** object. This API uses a promise to return the number of rows updated.<br>- **values**: data to update, which is stored in **ValuesBucket**.<br>- **predicates**: conditions for updating data.| 48 49- **Deleting Data** 50 51 Call **delete()** to delete the data that meets the conditions specified by **RdbPredicates**. If the data is deleted, the number of rows of the deleted data will be returned; otherwise, **0** will be returned. 52 53 **Table 4** API for deleting data 54 55 56 | Class | API | Description | 57 | ---------- | ---------------------------------------------------------- | ------------------------------------------------------------ | 58 | RdbStore | delete(predicates: RdbPredicates): Promise<number> | Deletes data from the RDB store based on the specified **RdbPredicates** object. This API uses a promise to return the number of rows deleted.<br>- **predicates**: conditions for deleting data.| 59 60- **Querying Data** 61 62 You can query data in an RDB store in either of the following ways: 63 64 - Call the **query()** method to query data based on the predicates, without passing any SQL statement. 65 - Run the native SQL statement. 66 67 **Table 5** APIs for querying data 68 69 | Class | API | Description | 70 | ---------- | ------------------------------------------------------------ | ------------------------------------------------------------ | 71 | RdbStore | query(predicates: RdbPredicates, columns?: Array<string>): Promise<ResultSet> | Queries data from the RDB store based on specified conditions. This API uses a promise to return the result.<br>- **predicates**: conditions for querying data.<br>- **columns**: columns to query. If this parameter is not specified, the query applies to all columns.| 72 | RdbStore | querySql(sql: string, bindArgs?: Array<ValueType>): Promise<ResultSet> | Queries data using the specified SQL statement. This API uses a promise to return the result.<br>- **sql**: SQL statement.<br>- **bindArgs**: arguments in the SQL statement.| 73 | RdbStore | remoteQuery(device: string, table: string, predicates: RdbPredicates, columns: Array<string>): Promise<ResultSet> | Queries data from the database of a remote device based on specified conditions. This API uses a promise to return the result.<br>- **device**: network ID of the remote device.<br>- **table**: name of the table to be queried.<br>- **predicates**: **RdbPredicates** that specifies the query condition.<br>- **columns**: columns to query. If this parameter is not specified, the query applies to all columns.| 74 75### Using Predicates 76 77The **RDB** module provides **RdbPredicates** for you to set database operation conditions. 78 79The following table lists common predicates. For more information about predicates, see [**RdbPredicates**](../reference/apis/js-apis-data-relationalStore.md#rdbpredicates). 80 81**Table 6** APIs for using RDB store predicates 82 83| Class | API | Description | 84| --------------- | ------------------------------------------------------------ | ------------------------------------------------------------ | 85| RdbPredicates | equalTo(field: string, value: ValueType): RdbPredicates | Sets an **RdbPredicates** to search for the data that is equal to the specified value.<br>- **field**: column name in the database table.<br>- **value**: value to match the **RdbPredicates**.<br>- **RdbPredicates**: **RdbPredicates** object created.| 86| RdbPredicates | notEqualTo(field: string, value: ValueType): RdbPredicates | Sets an **RdbPredicates** to search for the data that is not equal to the specified value.<br>- **field**: column name in the database table.<br>- **value**: value to match the **RdbPredicates**.<br>- **RdbPredicates**: **RdbPredicates** object created.| 87| RdbPredicates | or(): RdbPredicates | Adds the OR condition to the **RdbPredicates**.<br>- **RdbPredicates**: **RdbPredicates** with the OR condition.| 88| RdbPredicates | and(): RdbPredicates | Adds the AND condition to the **RdbPredicates**.<br>- **RdbPredicates**: **RdbPredicates** with the AND condition.| 89| RdbPredicates | contains(field: string, value: string): RdbPredicates | Sets an **RdbPredicates** to search for the data that contains the specified value.<br>- **field**: column name in the database table.<br>- **value**: value to match the **RdbPredicates**.<br>- **RdbPredicates**: **RdbPredicates** object created.| 90 91 92### Using the Result Set 93 94You 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. 95 96For details about how to use **ResultSet** APIs, see [ResultSet](../reference/apis/js-apis-data-relationalStore.md#resultset). 97 98> **NOTICE**<br> 99> After a result set is used, you must call the **close()** method to close it explicitly. 100 101**Table 7** APIs for using the result set 102 103| Class | API | Description | 104| ----------- | ---------------------------------------- | ------------------------------------------ | 105| ResultSet | goToFirstRow(): boolean | Moves to the first row of the result set. | 106| ResultSet | getString(columnIndex: number): string | Obtains the value in the form of a string based on the specified column and current row. | 107| ResultSet | getBlob(columnIndex: number): Uint8Array | Obtains the value in the form of a byte array based on the specified column and the current row.| 108| ResultSet | getDouble(columnIndex: number): number | Obtains the value in the form of double based on the specified column and current row. | 109| ResultSet | getLong(columnIndex: number): number | Obtains the value in the form of a long integer based on the specified column and current row. | 110| ResultSet | close(): void | Closes the result set. | 111 112 113 114### Setting Distributed Tables 115 116> **NOTE** 117> 118> - The **ohos.permission.DISTRIBUTED_DATASYNC** permission is required for calling the **setDistributedTables**, **obtainDistributedTableName**, **sync**, **on** and **off** APIs of **RdbStore**. 119> - The devices must be connected over network before the distributed tables are used. For details about the APIs and usage, see [Device Management](../reference/apis/js-apis-device-manager.md). 120 121**Setting Distributed Tables** 122 123**Table 8** API for setting distributed tables 124 125| Class | API | Description | 126| ---------- | ------------------------------------------------------------ | ------------------------------------------------------------ | 127| RdbStore | setDistributedTables(tables: Array\<string>): Promise\<void> | Sets distributed tables. This API uses a promise to return the result.<br>- **tables**: names of the distributed tables to set.| 128 129**Obtaining the Distributed Table Name for a Remote Device** 130 131You 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. 132 133**Table 9** API for obtaining the distributed table name of a remote device 134 135| Class | API | Description | 136| ---------- | ------------------------------------------------------------ | ------------------------------------------------------------ | 137| RdbStore | obtainDistributedTableName(device: string, table: string): Promise\<string> | Obtains the distributed table name for a remote device based on the local table name. The distributed table name is required when the RDB store of a remote device is queried. This API uses a promise to return the result.<br>- **device**: remote device.<br>- **table**: local table name.| 138 139**Synchronizing Data Between Devices** 140 141**Table 10** API for synchronizing data between devices 142 143| Class | API | Description | 144| ---------- | ------------------------------------------------------------ | ------------------------------------------------------------ | 145| RdbStore | sync(mode: SyncMode, predicates: RdbPredicates): Promise\<Array\<[string, number]>> | Synchronizes data between devices. This API uses a promise to return the result.<br>- **mode**: synchronization mode. **SYNC_MODE_PUSH** means to push data from the local device to a remote device. **SYNC_MODE_PULL** means to pull data from a remote device to the local device.<br>- **predicates**: specifies the data and devices to synchronize.<br>- **string**: device ID. <br>- **number**: synchronization status of each device. The value **0** indicates a successful synchronization. Other values indicate a synchronization failure.| 146 147**Registering an RDB Store Observer** 148 149**Table 11** API for registering an observer 150 151| Class | API | Description | 152| ---------- | ------------------------------------------------------------ | ------------------------------------------------------------ | 153| RdbStore | on(event: 'dataChange', type: SubscribeType, observer: Callback\<Array\<string>>): void | 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>- **type**: subscription type. **SUBSCRIBE_TYPE_REMOTE**: subscribes to remote data changes.<br>- **observer**: observer that listens for data changes in the RDB store.| 154 155**Unregistering an RDB Store Observer** 156 157**Table 12** API for unregistering an observer 158 159| Class | API | Description | 160| ---------- | ------------------------------------------------------------ | ------------------------------------------------------------ | 161| RdbStore | off(event:'dataChange', type: SubscribeType, observer: Callback\<Array\<string>>): void; | Unregisters the observer of the specified type from the RDB store. This API uses an asynchronous callback to return the result.<br>- **type**: subscription type. **SUBSCRIBE_TYPE_REMOTE**: subscribes to remote data changes.<br>- **observer**: observer to unregister.| 162 163### Backing Up and Restoring an RDB Store 164 165**Backing Up an RDB Store** 166 167**Table 13** API for backing up an RDB store 168 169| Class | API | Description | 170| ---------- | --------------------------------------------- | ------------------------------------------------------------ | 171| RdbStore | backup(destName: string): Promise<void> | Backs up an RDB store. This API uses a promise to return the result.<br>- **destName**: name of the RDB backup file.| 172 173**Restoring an RDB Store** 174 175**Table 14** API for restoring an RDB store 176 177| Class | API | Description | 178| ---------- | --------------------------------------------- | ------------------------------------------------------------ | 179| RdbStore | restore(srcName: string): Promise<void> | Restores an RDB store from a backup file. This API uses a promise to return the result.<br>- **srcName**: name of the backup file used to restore the RDB store.| 180 181### Transaction 182 183**Table 15** Transaction APIs 184 185| Class | API | Description | 186| -------- | ----------------------- | --------------------------------- | 187| RdbStore | beginTransaction(): void | Starts the transaction before executing SQL statements.| 188| RdbStore | commit(): void | Commits the executed SQL statements. | 189| RdbStore | rollBack(): void | Rolls back the SQL statements that have been executed. | 190 191## How to Develop 192 1931. Create an RDB store. 194 195 (1) Configure the RDB store attributes, including the RDB store name, storage mode, and whether read-only mode is used. 196 197 (2) Initialize the table structure and related data in the RDB store. 198 199 (3) Create an RDB store. 200 201 FA model: 202 203 ```js 204 import relationalStore from '@ohos.data.relationalStore' 205 import featureAbility from '@ohos.ability.featureAbility' 206 207 var store; 208 209 // Obtain the context. 210 let context = featureAbility.getContext(); 211 212 const STORE_CONFIG = { 213 name: "RdbTest.db", 214 securityLevel: relationalStore.SecurityLevel.S1 215 }; 216 217 relationalStore.getRdbStore(context, STORE_CONFIG, function (err, rdbStore) { 218 store = rdbStore; 219 if (err) { 220 console.error(`Get RdbStore failed, err: ${err}`); 221 return; 222 } 223 console.info(`Get RdbStore successfully.`); 224 }) 225 ``` 226 Stage model: 227 ```ts 228 import relationalStore from '@ohos.data.relationalStore' 229 import UIAbility from '@ohos.app.ability.UIAbility' 230 231 class EntryAbility extends UIAbility { 232 onWindowStageCreate(windowStage) { 233 var store; 234 const STORE_CONFIG = { 235 name: "RdbTest.db", 236 securityLevel: relationalStore.SecurityLevel.S1 237 }; 238 239 relationalStore.getRdbStore(this.context, STORE_CONFIG, function (err, rdbStore) { 240 store = rdbStore; 241 if (err) { 242 console.error(`Get RdbStore failed, err: ${err}`); 243 return; 244 } 245 console.info(`Get RdbStore successfully.`); 246 }) 247 } 248 } 249 ``` 250 2512. Insert data. 252 253 (1) Create a **ValuesBucket** to store the data you need to insert. 254 255 (2) Call the **insert()** method to insert data into the RDB store. 256 257 The sample code is as follows: 258 259 ```js 260 let u8 = new Uint8Array([1, 2, 3]); 261 const valueBucket = { "name": "Tom", "age": 18, "salary": 100.5, "blobType": u8 }; 262 let insertPromise = store.insert("test", valueBucket); 263 ``` 264 265 ```js 266 // Use a transaction to insert data. 267 try { 268 store.beginTransaction(); 269 let u8 = new Uint8Array([1, 2, 3]); 270 const valueBucket = { "name": "Tom", "age": 18, "salary": 100.5, "blobType": u8 }; 271 let promise = store.insert("test", valueBucket); 272 promise.then(() => { 273 store.commit(); 274 }) 275 } catch (err) { 276 console.error(`Transaction failed, err: ${err}`); 277 store.rollBack(); 278 } 279 ``` 280 2813. Query data. 282 283 (1) Create an **RdbPredicates** object to specify query conditions. 284 285 (2) Call the **query()** API to query data. 286 287 (3) Call the **resultSet()** API to obtain the result. 288 289 The sample code is as follows: 290 291 ```js 292 let predicates = new relationalStore.RdbPredicates("test"); 293 predicates.equalTo("name", "Tom"); 294 let promisequery = store.query(predicates); 295 promisequery.then((resultSet) => { 296 resultSet.goToFirstRow(); 297 const id = resultSet.getLong(resultSet.getColumnIndex("id")); 298 const name = resultSet.getString(resultSet.getColumnIndex("name")); 299 const age = resultSet.getLong(resultSet.getColumnIndex("age")); 300 const salary = resultSet.getDouble(resultSet.getColumnIndex("salary")); 301 const blobType = resultSet.getBlob(resultSet.getColumnIndex("blobType")); 302 resultSet.close(); 303 }) 304 ``` 305 3064. Set the distributed tables to be synchronized. 307 308 (1) Add the following permission to the permission configuration file: 309 310 ```json 311 "requestPermissions": 312 { 313 "name": "ohos.permission.DISTRIBUTED_DATASYNC" 314 } 315 ``` 316 317 (2) Obtain the required permissions. 318 319 (3) Set the distributed tables. 320 321 (4) Check whether the setting is successful. 322 323 The sample code is as follows: 324 325 ```js 326 let context = featureAbility.getContext(); 327 context.requestPermissionsFromUser(['ohos.permission.DISTRIBUTED_DATASYNC'], 666, function (result) { 328 console.info(`result.requestCode=${result.requestCode}`); 329 }) 330 let promise = store.setDistributedTables(["test"]); 331 promise.then(() => { 332 console.info(`setDistributedTables success.`); 333 }).catch((err) => { 334 console.error(`setDistributedTables failed, ${err}`); 335 }) 336 ``` 337 3385. Synchronize data across devices. 339 340 (1) Construct an **RdbPredicates** object to specify remote devices within the network to be synchronized. 341 342 (2) Call **rdbStore.sync()** to synchronize data. 343 344 (3) Check whether the data synchronization is successful. 345 346 The sample code is as follows: 347 348 ```js 349 let predicate = new relationalStore.RdbPredicates('test'); 350 predicate.inDevices(['12345678abcde']); 351 let promise = store.sync(relationalStore.SyncMode.SYNC_MODE_PUSH, predicate); 352 promise.then((result) => { 353 console.info(`sync done.`); 354 for (let i = 0; i < result.length; i++) { 355 console.info(`device=${result[i][0]}, status=${result[i][1]}`); 356 } 357 }).catch((err) => { 358 console.error(`sync failed, err: ${err}`); 359 }) 360 ``` 361 3626. Subscribe to distributed data. 363 364 (1) Register an observer to listen for distributed data changes. 365 366 (2) When data in the RDB store changes, a callback will be invoked to return the data changes. 367 368 The sample code is as follows: 369 370 ```js 371 function storeObserver(devices) { 372 for (let i = 0; i < devices.length; i++) { 373 console.info(`device= ${devices[i]} data changed`); 374 } 375 } 376 377 try { 378 store.on('dataChange', relationalStore.SubscribeType.SUBSCRIBE_TYPE_REMOTE, storeObserver); 379 } catch (err) { 380 console.error(`register observer failed, err: ${err}`); 381 } 382 ``` 383 3847. Query data across devices. 385 386 (1) Obtain the distributed table name for a remote device based on the local table name. 387 388 (2) Call the resultSet() API to obtain the result. 389 390 The sample code is as follows: 391 392 ```js 393 import deviceManager from '@ohos.distributedHardware.deviceManager' 394 395 let deviceIds = []; 396 deviceManager.createDeviceManager('bundleName', (err, value) => { 397 if (!err) { 398 let devManager = value; 399 if (devManager != null) { 400 // Obtain device IDs. 401 let devices = devManager.getTrustedDeviceListSync(); 402 for (let i = 0; i < devices.length; i++) { 403 deviceIds[i] = devices[i].deviceId; 404 } 405 } 406 } 407 }) 408 409 let tableName = store.obtainDistributedTableName(deviceIds[0], "test"); 410 let resultSet = store.querySql("SELECT * FROM " + tableName); 411 ``` 412 4138. Query data of a remote device. 414 415 (1) Construct a predicate object for querying distributed tables, and specify the remote distributed table name and the remote device. 416 417 (2) Call the resultSet() API to obtain the result. 418 419 The sample code is as follows: 420 421 ```js 422 let rdbPredicate = new relationalStore.RdbPredicates('employee'); 423 predicates.greaterThan("id", 0) ; 424 let promiseQuery = store.remoteQuery('12345678abcde', 'employee', rdbPredicate); 425 promiseQuery.then((resultSet) => { 426 while (resultSet.goToNextRow()) { 427 let idx = resultSet.getLong(0); 428 let name = resultSet.getString(1); 429 let age = resultSet.getLong(2); 430 console.info(`indx: ${idx}, name: ${name}, age: ${age}`); 431 } 432 resultSet.close(); 433 }).catch((err) => { 434 console.error(`failed to remoteQuery, err: ${err}`); 435 }) 436 ``` 437 4389. Back up and restore an RDB store. 439 440 (1) Back up the current RDB store. 441 442 The sample code is as follows: 443 444 ```js 445 let promiseBackup = store.backup("dbBackup.db"); 446 promiseBackup.then(() => { 447 console.info(`Backup success.`); 448 }).catch((err) => { 449 console.error(`Backup failed, err: ${err}`); 450 }) 451 ``` 452 453 (2) Restore the RDB store using the backup file. 454 455 The sample code is as follows: 456 457 ```js 458 let promiseRestore = store.restore("dbBackup.db"); 459 promiseRestore.then(() => { 460 console.info(`Restore success.`); 461 }).catch((err) => { 462 console.error(`Restore failed, err: ${err}`); 463 }) 464 ``` 465