1# Persisting RDB Store Data 2 3 4## When to Use 5 6A relational database (RDB) store is used to store data in complex relational models, such as the student information including names, student IDs, and scores of each subject, or employee information including names, employee IDs, and positions, based on SQLite. The data is more complex than key-value (KV) pairs due to strict mappings. You can use **RelationalStore** to implement persistence of this type of data. 7 8 9## Basic Concepts 10 11- **Predicates**: a representation of the property or feature of a data entity, or the relationship between data entities. It is used to define operation conditions. 12 13- **ResultSet**: a set of query results, which allows access to the required data in flexible modes. 14 15 16## Working Principles 17 18**RelationalStore** provides APIs for applications to perform data operations. With SQLite as the underlying persistent storage engine, **RelationalStore** provides SQLite database features, including transactions, indexes, views, triggers, foreign keys, parameterized queries, prepared SQL statements, and more. 19 20**Figure 1** Working mechanism 21 22 23 24 25## Constraints 26 27- The default logging mode is Write Ahead Log (WAL), and the default flushing mode is **FULL** mode. 28 29- The RDB store supports a maximum of four read connections and one write connection. A thread performs the read operation when acquiring a read connection. When there is no read connection available but the write connection is idle, the write connection can be used to perform the read operation. 30 31- To ensure data accuracy, only one write operation is allowed at a time. 32 33- Once an application is uninstalled, related database files and temporary files on the device are automatically deleted. 34 35- ArkTS supports the following basic data types: number, string, binary data, and boolean. 36 37- The maximum size of a data record is 2 MB. If a data record exceeds 2 MB, it can be inserted successfully but cannot be read. 38 39## Available APIs 40 41The following table lists the APIs used for RDB data persistence. Most of the APIs are executed asynchronously, using a callback or promise to return the result. The following table uses the callback-based APIs as an example. For more information about the APIs, see [RDB Store](../reference/apis-arkdata/js-apis-data-relationalStore.md). 42 43| API| Description| 44| -------- | -------- | 45| getRdbStore(context: Context, config: StoreConfig, callback: AsyncCallback<RdbStore>): void | Obtains an **RdbStore** instance to implement RDB store operations. You can set **RdbStore** parameters based on actual requirements and use **RdbStore** APIs to perform data operations.| 46| executeSql(sql: string, bindArgs: Array<ValueType>, callback: AsyncCallback<void>):void | Executes an SQL statement that contains specified arguments but returns no value.| 47| insert(table: string, values: ValuesBucket, callback: AsyncCallback<number>):void | Inserts a row of data into a table.| 48| update(values: ValuesBucket, predicates: RdbPredicates, callback: AsyncCallback<number>):void | Updates data in the RDB store based on the specified **predicates** instance.| 49| delete(predicates: RdbPredicates, callback: AsyncCallback<number>):void | Deletes data from the RDB store based on the specified **predicates** instance.| 50| query(predicates: RdbPredicates, columns: Array<string>, callback: AsyncCallback<ResultSet>):void | Queries data in the RDB store based on specified conditions.| 51| deleteRdbStore(context: Context, name: string, callback: AsyncCallback<void>): void | Deletes an RDB store.| 52 53 54## How to Develop 55Unless otherwise specified, the sample code without "stage model" or "FA model" applies to both models. 56 57If error code 14800011 is reported, the RDB store is corrupted and needs to be rebuilt. For details, see [Rebuilding an RDB Store](data-backup-and-restore.md#rebuilding-an-rdb-store). 58 591. Obtain an **RdbStore** instance, which includes operations of creating an RDB store and tables, and upgrading or downgrading the RDB store. <br>Example: 60 61 Stage model: 62 63 ```ts 64 import { relationalStore} from '@kit.ArkData'; // Import the relationalStore module. 65 import { UIAbility } from '@kit.AbilityKit'; 66 import { BusinessError } from '@kit.BasicServicesKit'; 67 import { window } from '@kit.ArkUI'; 68 69 // In this example, Ability is used to obtain an RdbStore instance. You can use other implementations as required. 70 class EntryAbility extends UIAbility { 71 onWindowStageCreate(windowStage: window.WindowStage) { 72 const STORE_CONFIG :relationalStore.StoreConfig= { 73 name: 'RdbTest.db', // Database file name. 74 securityLevel: relationalStore.SecurityLevel.S3, // Database security level. 75 encrypt: false, // Whether to encrypt the database. This parameter is optional. By default, the database is not encrypted. 76 customDir: 'customDir/subCustomDir' // (Optional) Customized database path. The database is created in the context.databaseDir + '/rdb/' + customDir directory, where context.databaseDir indicates the application sandbox path, '/rdb/' indicates a relational database, and customDir indicates the customized path. If this parameter is not specified, an RdbStore instance is created in the sandbox directory of the application. 77 isReadOnly: false // (Optional) Specify whether the RDB store is opened in read-only mode. The default value is false, which means the RDB store is readable and writable. If this parameter is true, data can only be read from the RDB store. If write operation is performed, error code 801 is returned. 78 }; 79 80 // Check the RDB store version. If the version is incorrect, upgrade or downgrade the RDB store. 81 // For example, the RDB store version is 3 and the table structure is EMPLOYEE (NAME, AGE, SALARY, CODES, IDENTITY). 82 const SQL_CREATE_TABLE = 'CREATE TABLE IF NOT EXISTS EMPLOYEE (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INTEGER, SALARY REAL, CODES BLOB, IDENTITY UNLIMITED INT)'; // SQL statement used to create a table. In the statement, the IDENTITY type bigint should be UNLIMITED INT. 83 84 relationalStore.getRdbStore(this.context, STORE_CONFIG, (err, store) => { 85 if (err) { 86 console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`); 87 return; 88 } 89 console.info('Succeeded in getting RdbStore.'); 90 91 // When the RDB store is created, the default version is 0. 92 if (store.version === 0) { 93 store.executeSql(SQL_CREATE_TABLE); // Create a data table. 94 // Set the RDB store version, which must be an integer greater than 0. 95 store.version = 3; 96 } 97 98 // If the RDB store version is not 0 and does not match the current version, upgrade or downgrade the RDB store. 99 // For example, upgrade the RDB store from version 1 to version 2. 100 if (store.version === 1) { 101 // Upgrade the RDB store from version 1 to version 2, and change the table structure from EMPLOYEE (NAME, SALARY, CODES, ADDRESS) to EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS). 102 (store as relationalStore.RdbStore).executeSql('ALTER TABLE EMPLOYEE ADD COLUMN AGE INTEGER'); 103 store.version = 2; 104 } 105 106 // For example, upgrade the RDB store from version 2 to version 3. 107 if (store.version === 2) { 108 // Upgrade the RDB store from version 2 to version 3, and change the table structure from EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS) to EMPLOYEE (NAME, AGE, SALARY, CODES). 109 (store as relationalStore.RdbStore).executeSql('ALTER TABLE EMPLOYEE DROP COLUMN ADDRESS TEXT'); 110 store.version = 3; 111 } 112 }); 113 114 // Before performing data operations on the database, obtain an RdbStore instance. 115 } 116 } 117 ``` 118 119 FA model: 120 121 ```ts 122 import { relationalStore} from '@kit.ArkData'; // Import the relationalStore module. 123 import { featureAbility } from '@kit.AbilityKit'; 124 import { BusinessError } from '@kit.BasicServicesKit'; 125 126 let context = featureAbility.getContext(); 127 128 const STORE_CONFIG :relationalStore.StoreConfig = { 129 name: 'RdbTest.db', // Database file name. 130 securityLevel: relationalStore.SecurityLevel.S3 // Database security level. 131 }; 132 133 // For example, the RDB store version is 3 and the table structure is EMPLOYEE (NAME, AGE, SALARY, CODES, IDENTITY). 134 const SQL_CREATE_TABLE = 'CREATE TABLE IF NOT EXISTS EMPLOYEE (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INTEGER, SALARY REAL, CODES BLOB, IDENTITY UNLIMITED INT)'; // SQL statement used to create a table. In the statement, the IDENTITY type bigint should be UNLIMITED INT. 135 136 relationalStore.getRdbStore(context, STORE_CONFIG, (err, store) => { 137 if (err) { 138 console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`); 139 return; 140 } 141 console.info('Succeeded in getting RdbStore.'); 142 143 // When the RDB store is created, the default version is 0. 144 if (store.version === 0) { 145 store.executeSql(SQL_CREATE_TABLE); // Create a data table. 146 // Set the RDB store version, which must be an integer greater than 0. 147 store.version = 3; 148 } 149 150 // If the RDB store version is not 0 and does not match the current version, upgrade or downgrade the RDB store. 151 // For example, upgrade the RDB store from version 1 to version 2. 152 if (store.version === 1) { 153 // Upgrade the RDB store from version 1 to version 2, and change the table structure from EMPLOYEE (NAME, SALARY, CODES, ADDRESS) to EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS). 154 store.executeSql('ALTER TABLE EMPLOYEE ADD COLUMN AGE INTEGER'); 155 store.version = 2; 156 } 157 158 // For example, upgrade the RDB store from version 2 to version 3. 159 if (store.version === 2) { 160 // Upgrade the RDB store from version 2 to version 3, and change the table structure from EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS) to EMPLOYEE (NAME, AGE, SALARY, CODES). 161 store.executeSql('ALTER TABLE EMPLOYEE DROP COLUMN ADDRESS TEXT'); 162 store.version = 3; 163 } 164 }); 165 166 // Before performing data operations on the database, obtain an RdbStore instance. 167 ``` 168 169 > **NOTE** 170 > 171 > - The RDB store created by an application varies with the context. Multiple RDB stores are created for the same database name with different application contexts. For example, each UIAbility has its own context. 172 > 173 > - When an application calls **getRdbStore()** to obtain an RDB store instance for the first time, the corresponding database file is generated in the application sandbox. When the RDB store is used, temporary files ended with **-wal** and **-shm** may be generated in the same directory as the database file. If you want to move the database files to other places, you must also move these temporary files. After the application is uninstalled, the database files and temporary files generated on the device are also removed. 174 > 175 > - For details about the error codes, see [Universal Error Codes](../reference/errorcode-universal.md) and [RDB Store Error Codes](../reference/apis-arkdata/errorcode-data-rdb.md). 176 1772. Use **insert()** to insert data to the RDB store. <br>Example: 178 179 ```ts 180 let store: relationalStore.RdbStore | undefined = undefined; 181 182 let value1 = 'Lisa'; 183 let value2 = 18; 184 let value3 = 100.5; 185 let value4 = new Uint8Array([1, 2, 3, 4, 5]); 186 let value5 = BigInt('15822401018187971961171'); 187 // You can use either of the following: 188 const valueBucket1: relationalStore.ValuesBucket = { 189 'NAME': value1, 190 'AGE': value2, 191 'SALARY': value3, 192 'CODES': value4, 193 'IDENTITY': value5, 194 }; 195 const valueBucket2: relationalStore.ValuesBucket = { 196 NAME: value1, 197 AGE: value2, 198 SALARY: value3, 199 CODES: value4, 200 IDENTITY: value5, 201 }; 202 const valueBucket3: relationalStore.ValuesBucket = { 203 "NAME": value1, 204 "AGE": value2, 205 "SALARY": value3, 206 "CODES": value4, 207 "IDENTITY": value5, 208 }; 209 210 if (store !== undefined) { 211 (store as relationalStore.RdbStore).insert('EMPLOYEE', valueBucket1, (err: BusinessError, rowId: number) => { 212 if (err) { 213 console.error(`Failed to insert data. Code:${err.code}, message:${err.message}`); 214 return; 215 } 216 console.info(`Succeeded in inserting data. rowId:${rowId}`); 217 }) 218 } 219 ``` 220 221 > **NOTE** 222 > 223 > **RelationalStore** does not provide explicit flush operations for data persistence. The **insert()** method stores data persistently. 224 2253. Modify or delete data based on the specified **Predicates** instance. 226 227 Use **update()** to modify data and **delete()** to delete data. <br>Example: 228 229 ```ts 230 let value6 = 'Rose'; 231 let value7 = 22; 232 let value8 = 200.5; 233 let value9 = new Uint8Array([1, 2, 3, 4, 5]); 234 let value10 = BigInt('15822401018187971967863'); 235 // You can use either of the following: 236 const valueBucket4: relationalStore.ValuesBucket = { 237 'NAME': value6, 238 'AGE': value7, 239 'SALARY': value8, 240 'CODES': value9, 241 'IDENTITY': value10, 242 }; 243 const valueBucket5: relationalStore.ValuesBucket = { 244 NAME: value6, 245 AGE: value7, 246 SALARY: value8, 247 CODES: value9, 248 IDENTITY: value10, 249 }; 250 const valueBucket6: relationalStore.ValuesBucket = { 251 "NAME": value6, 252 "AGE": value7, 253 "SALARY": value8, 254 "CODES": value9, 255 "IDENTITY": value10, 256 }; 257 258 // Modify data. 259 let predicates1 = new relationalStore.RdbPredicates('EMPLOYEE'); // Create predicates for the table named EMPLOYEE. 260 predicates1.equalTo('NAME', 'Lisa'); // Modify the data of Lisa in the EMPLOYEE table to the specified data. 261 if (store !== undefined) { 262 (store as relationalStore.RdbStore).update(valueBucket4, predicates1, (err: BusinessError, rows: number) => { 263 if (err) { 264 console.error(`Failed to update data. Code:${err.code}, message:${err.message}`); 265 return; 266 } 267 console.info(`Succeeded in updating data. row count: ${rows}`); 268 }) 269 } 270 271 // Delete data. 272 predicates1 = new relationalStore.RdbPredicates('EMPLOYEE'); 273 predicates1.equalTo('NAME', 'Lisa'); 274 if (store !== undefined) { 275 (store as relationalStore.RdbStore).delete(predicates1, (err: BusinessError, rows: number) => { 276 if (err) { 277 console.error(`Failed to delete data. Code:${err.code}, message:${err.message}`); 278 return; 279 } 280 console.info(`Delete rows: ${rows}`); 281 }) 282 } 283 ``` 284 2854. Query data based on the conditions specified by **Predicates**. 286 287 Use **query()** to query data. The data obtained is returned in a **ResultSet** object. <br>Example: 288 289 ```ts 290 let predicates2 = new relationalStore.RdbPredicates('EMPLOYEE'); 291 predicates2.equalTo('NAME', 'Rose'); 292 if (store !== undefined) { 293 (store as relationalStore.RdbStore).query(predicates2, ['ID', 'NAME', 'AGE', 'SALARY', 'IDENTITY'], (err: BusinessError, resultSet) => { 294 if (err) { 295 console.error(`Failed to query data. Code:${err.code}, message:${err.message}`); 296 return; 297 } 298 console.info(`ResultSet column names: ${resultSet.columnNames}, column count: ${resultSet.columnCount}`); 299 // resultSet is a cursor of a data set. By default, the cursor points to the -1st record. Valid data starts from 0. 300 while (resultSet.goToNextRow()) { 301 const id = resultSet.getLong(resultSet.getColumnIndex('ID')); 302 const name = resultSet.getString(resultSet.getColumnIndex('NAME')); 303 const age = resultSet.getLong(resultSet.getColumnIndex('AGE')); 304 const salary = resultSet.getDouble(resultSet.getColumnIndex('SALARY')); 305 const identity = resultSet.getValue(resultSet.getColumnIndex('IDENTITY')); 306 console.info(`id=${id}, name=${name}, age=${age}, salary=${salary}, identity=${identity}`); 307 } 308 // Release the data set memory. 309 resultSet.close(); 310 }) 311 } 312 ``` 313 314 > **NOTE** 315 > 316 > Use **close()** to close the **ResultSet** that is no longer used in a timely manner so that the memory allocated can be released. 317 3185. Back up the database in the same directory. 319 320 Two backup modes are available: manual backup and automatic backup (available only for system applications). For details, see [Backing Up an RDB Store](data-backup-and-restore.md#backing-up-an-rdb store). 321 322 Example: Perform manual backup of an RDB store. 323 324 ```ts 325 if (store !== undefined) { 326 // Backup.db indicates the name of the database backup file. By default, it is in the same directory as the RdbStore file. You can also specify the directory, which is in the customDir + backup.db format. 327 (store as relationalStore.RdbStore).backup("Backup.db", (err: BusinessError) => { 328 if (err) { 329 console.error(`Failed to backup RdbStore. Code:${err.code}, message:${err.message}`); 330 return; 331 } 332 console.info(`Succeeded in backing up RdbStore.`); 333 }) 334 } 335 ``` 336 3376. Restore data from the database backup. 338 339 You can restore an RDB store from the manual backup data or automatic backup data (available only for system applications). For details, see [Restoring RDB Store Data](data-backup-and-restore.md#restoring-rdb-store-data). 340 341 Example: Call [restore](../reference/apis-arkdata/js-apis-data-relationalStore.md#restore) to restore an RDB store from the data that is manually backed up. 342 343 ```ts 344 if (store !== undefined) { 345 (store as relationalStore.RdbStore).restore("Backup.db", (err: BusinessError) => { 346 if (err) { 347 console.error(`Failed to restore RdbStore. Code:${err.code}, message:${err.message}`); 348 return; 349 } 350 console.info(`Succeeded in restoring RdbStore.`); 351 }) 352 } 353 ``` 354 3557. Delete the RDB store. 356 357 Use **deleteRdbStore()** to delete the RDB store and related database files. <br>Example: 358 359 Stage model: 360 361 ```ts 362 relationalStore.deleteRdbStore(this.context, 'RdbTest.db', (err: BusinessError) => { 363 if (err) { 364 console.error(`Failed to delete RdbStore. Code:${err.code}, message:${err.message}`); 365 return; 366 } 367 console.info('Succeeded in deleting RdbStore.'); 368 }); 369 ``` 370 371 FA model: 372 373 ```ts 374 relationalStore.deleteRdbStore(context, 'RdbTest.db', (err: BusinessError) => { 375 if (err) { 376 console.error(`Failed to delete RdbStore. Code:${err.code}, message:${err.message}`); 377 return; 378 } 379 console.info('Succeeded in deleting RdbStore.'); 380 }); 381 ```