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![relationStore_local](figures/relationStore_local.jpg)
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   ```