1import assert from 'assert'; 2import { Asset } from 'expo-asset'; 3import * as FS from 'expo-file-system'; 4import { Platform } from 'expo-modules-core'; 5import * as SQLite from 'expo-sqlite'; 6 7export const name = 'SQLite'; 8 9// TODO: Only tests successful cases, needs to test error cases like bad database name etc. 10export function test(t) { 11 t.describe('SQLite', () => { 12 t.it('should be able to drop + create a table, insert, query', async () => { 13 const db = SQLite.openDatabase('test.db'); 14 await new Promise((resolve, reject) => { 15 db.transaction( 16 (tx) => { 17 const nop = () => {}; 18 const onError = (tx, error) => { 19 reject(error); 20 return false; 21 }; 22 23 tx.executeSql('DROP TABLE IF EXISTS Users;', [], nop, onError); 24 tx.executeSql( 25 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64), k INT, j REAL);', 26 [], 27 nop, 28 onError 29 ); 30 tx.executeSql( 31 'INSERT INTO Users (name, k, j) VALUES (?, ?, ?)', 32 ['Tim Duncan', 1, 23.4], 33 nop, 34 onError 35 ); 36 tx.executeSql( 37 'INSERT INTO Users (name, k, j) VALUES ("Manu Ginobili", 5, 72.8)', 38 [], 39 nop, 40 onError 41 ); 42 tx.executeSql( 43 'INSERT INTO Users (name, k, j) VALUES ("Nikhilesh Sigatapu", 7, 42.14)', 44 [], 45 nop, 46 onError 47 ); 48 49 tx.executeSql( 50 'SELECT * FROM Users', 51 [], 52 (tx, results) => { 53 t.expect(results.rows.length).toEqual(3); 54 t.expect(results.rows.item(0).j).toBeCloseTo(23.4); 55 }, 56 onError 57 ); 58 }, 59 reject, 60 () => { 61 resolve(null); 62 } 63 ); 64 }); 65 66 if (Platform.OS !== 'web') { 67 const { exists } = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 68 t.expect(exists).toBeTruthy(); 69 } 70 }); 71 72 if (Platform.OS !== 'web') { 73 t.it( 74 'should work with a downloaded .db file', 75 async () => { 76 await FS.downloadAsync( 77 Asset.fromModule(require('../assets/asset-db.db')).uri, 78 `${FS.documentDirectory}SQLite/downloaded.db` 79 ); 80 81 const db = SQLite.openDatabase('downloaded.db'); 82 await new Promise((resolve, reject) => { 83 db.transaction( 84 (tx) => { 85 const onError = (tx, error) => { 86 reject(error); 87 return false; 88 }; 89 tx.executeSql( 90 'SELECT * FROM Users', 91 [], 92 (tx, results) => { 93 t.expect(results.rows.length).toEqual(3); 94 t.expect(results.rows._array[0].j).toBeCloseTo(23.4); 95 }, 96 onError 97 ); 98 }, 99 reject, 100 () => { 101 resolve(null); 102 } 103 ); 104 }); 105 db.closeAsync(); 106 }, 107 30000 108 ); 109 } 110 111 t.it('should be able to recreate db from scratch by deleting file', async () => { 112 { 113 const db = SQLite.openDatabase('test.db'); 114 await new Promise((resolve, reject) => { 115 db.transaction( 116 (tx) => { 117 const nop = () => {}; 118 const onError = (tx, error) => { 119 reject(error); 120 return false; 121 }; 122 123 tx.executeSql('DROP TABLE IF EXISTS Users;', [], nop, onError); 124 tx.executeSql( 125 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64), k INT, j REAL);', 126 [], 127 nop, 128 onError 129 ); 130 tx.executeSql( 131 'INSERT INTO Users (name, k, j) VALUES (?, ?, ?)', 132 ['Tim Duncan', 1, 23.4], 133 nop, 134 onError 135 ); 136 137 tx.executeSql( 138 'SELECT * FROM Users', 139 [], 140 (tx, results) => { 141 t.expect(results.rows.length).toEqual(1); 142 }, 143 onError 144 ); 145 }, 146 reject, 147 () => { 148 resolve(null); 149 } 150 ); 151 }); 152 } 153 154 if (Platform.OS !== 'web') { 155 const { exists } = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 156 t.expect(exists).toBeTruthy(); 157 } 158 159 if (Platform.OS !== 'web') { 160 await FS.deleteAsync(`${FS.documentDirectory}SQLite/test.db`); 161 const { exists } = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 162 t.expect(exists).toBeFalsy(); 163 } 164 165 { 166 const db = SQLite.openDatabase('test.db'); 167 await new Promise((resolve, reject) => { 168 db.transaction( 169 (tx) => { 170 const nop = () => {}; 171 const onError = (tx, error) => { 172 reject(error); 173 return false; 174 }; 175 176 tx.executeSql('DROP TABLE IF EXISTS Users;', [], nop, onError); 177 tx.executeSql( 178 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64), k INT, j REAL);', 179 [], 180 nop, 181 onError 182 ); 183 tx.executeSql( 184 'SELECT * FROM Users', 185 [], 186 (tx, results) => { 187 t.expect(results.rows.length).toEqual(0); 188 }, 189 onError 190 ); 191 192 tx.executeSql( 193 'INSERT INTO Users (name, k, j) VALUES (?, ?, ?)', 194 ['Tim Duncan', 1, 23.4], 195 nop, 196 onError 197 ); 198 tx.executeSql( 199 'SELECT * FROM Users', 200 [], 201 (tx, results) => { 202 t.expect(results.rows.length).toEqual(1); 203 }, 204 onError 205 ); 206 }, 207 reject, 208 () => { 209 resolve(null); 210 } 211 ); 212 }); 213 } 214 }); 215 216 t.it('should maintain correct type of potentialy null bind parameters', async () => { 217 const db = SQLite.openDatabase('test.db'); 218 await new Promise((resolve, reject) => { 219 db.transaction( 220 (tx) => { 221 const nop = () => {}; 222 const onError = (tx, error) => { 223 reject(error); 224 return false; 225 }; 226 227 tx.executeSql('DROP TABLE IF EXISTS Nulling;', [], nop, onError); 228 tx.executeSql( 229 'CREATE TABLE IF NOT EXISTS Nulling (id INTEGER PRIMARY KEY NOT NULL, x NUMERIC, y NUMERIC)', 230 [], 231 nop, 232 onError 233 ); 234 tx.executeSql('INSERT INTO Nulling (x, y) VALUES (?, ?)', [null, null], nop, onError); 235 tx.executeSql('INSERT INTO Nulling (x, y) VALUES (null, null)', [], nop, onError); 236 237 tx.executeSql( 238 'SELECT * FROM Nulling', 239 [], 240 (tx, results) => { 241 t.expect(results.rows.item(0).x).toBeNull(); 242 t.expect(results.rows.item(0).y).toBeNull(); 243 t.expect(results.rows.item(1).x).toBeNull(); 244 t.expect(results.rows.item(1).y).toBeNull(); 245 }, 246 onError 247 ); 248 }, 249 reject, 250 () => { 251 resolve(null); 252 } 253 ); 254 }); 255 256 if (Platform.OS !== 'web') { 257 const { exists } = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 258 t.expect(exists).toBeTruthy(); 259 } 260 }); 261 262 // Do not try to test PRAGMA statements support in web 263 // as it is expected to not be working. 264 // See https://stackoverflow.com/a/10298712 265 if (Platform.OS !== 'web') { 266 t.it('should support PRAGMA statements', async () => { 267 const db = SQLite.openDatabase('test.db'); 268 await new Promise((resolve, reject) => { 269 db.transaction( 270 (tx) => { 271 const nop = () => {}; 272 const onError = (tx, error) => { 273 reject(error); 274 return false; 275 }; 276 277 tx.executeSql('DROP TABLE IF EXISTS SomeTable;', [], nop, onError); 278 tx.executeSql( 279 'CREATE TABLE IF NOT EXISTS SomeTable (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 280 [], 281 nop, 282 onError 283 ); 284 // a result-returning pragma 285 tx.executeSql( 286 'PRAGMA table_info(SomeTable);', 287 [], 288 (tx, results) => { 289 t.expect(results.rows.length).toEqual(2); 290 t.expect(results.rows.item(0).name).toEqual('id'); 291 t.expect(results.rows.item(1).name).toEqual('name'); 292 }, 293 onError 294 ); 295 // a no-result pragma 296 tx.executeSql('PRAGMA case_sensitive_like = true;', [], nop, onError); 297 // a setter/getter pragma 298 tx.executeSql('PRAGMA user_version = 123;', [], nop, onError); 299 tx.executeSql( 300 'PRAGMA user_version;', 301 [], 302 (tx, results) => { 303 t.expect(results.rows.length).toEqual(1); 304 t.expect(results.rows.item(0).user_version).toEqual(123); 305 }, 306 onError 307 ); 308 }, 309 reject, 310 () => { 311 resolve(null); 312 } 313 ); 314 }); 315 }); 316 } 317 318 t.it('should return correct rowsAffected value', async () => { 319 const db = SQLite.openDatabase('test.db'); 320 await new Promise((resolve, reject) => { 321 db.transaction( 322 (tx) => { 323 const nop = () => {}; 324 const onError = (tx, error) => { 325 reject(error); 326 return false; 327 }; 328 329 tx.executeSql('DROP TABLE IF EXISTS Users;', [], nop, onError); 330 tx.executeSql( 331 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 332 [], 333 nop, 334 onError 335 ); 336 tx.executeSql( 337 'INSERT INTO Users (name) VALUES (?), (?), (?)', 338 ['name1', 'name2', 'name3'], 339 nop, 340 onError 341 ); 342 }, 343 reject, 344 () => { 345 resolve(null); 346 } 347 ); 348 }); 349 await new Promise((resolve, reject) => { 350 db.transaction( 351 (tx) => { 352 const onError = (tx, error) => { 353 reject(error); 354 return false; 355 }; 356 tx.executeSql( 357 'DELETE FROM Users WHERE name=?', 358 ['name1'], 359 (tx, results) => { 360 t.expect(results.rowsAffected).toEqual(1); 361 }, 362 onError 363 ); 364 tx.executeSql( 365 'DELETE FROM Users WHERE name=? OR name=?', 366 ['name2', 'name3'], 367 (tx, results) => { 368 t.expect(results.rowsAffected).toEqual(2); 369 }, 370 onError 371 ); 372 tx.executeSql( 373 // ensure deletion succeedeed 374 'SELECT * from Users', 375 [], 376 (tx, results) => { 377 t.expect(results.rows.length).toEqual(0); 378 }, 379 onError 380 ); 381 }, 382 reject, 383 () => { 384 resolve(null); 385 } 386 ); 387 }); 388 }); 389 390 if (Platform.OS !== 'web') { 391 // It is not expected to work on web, since we cannot execute PRAGMA to enable foreign keys support 392 t.it('should return correct rowsAffected value when deleting cascade', async () => { 393 const db = SQLite.openDatabase('test.db'); 394 db.exec([{ sql: 'PRAGMA foreign_keys = ON;', args: [] }], false, () => {}); 395 await new Promise((resolve, reject) => { 396 db.transaction( 397 (tx) => { 398 const nop = () => {}; 399 const onError = (tx, error) => { 400 reject(error); 401 return false; 402 }; 403 404 tx.executeSql('DROP TABLE IF EXISTS Users;', [], nop, onError); 405 tx.executeSql('DROP TABLE IF EXISTS Posts;', [], nop, onError); 406 tx.executeSql( 407 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 408 [], 409 nop, 410 onError 411 ); 412 tx.executeSql( 413 'CREATE TABLE IF NOT EXISTS Posts (post_id INTEGER PRIMARY KEY NOT NULL, content VARCHAR(64), userposted INTEGER, FOREIGN KEY(userposted) REFERENCES Users(user_id) ON DELETE CASCADE);', 414 [], 415 nop, 416 onError 417 ); 418 tx.executeSql( 419 'INSERT INTO Users (name) VALUES (?), (?), (?)', 420 ['name1', 'name2', 'name3'], 421 nop, 422 onError 423 ); 424 425 tx.executeSql( 426 'INSERT INTO Posts (content, userposted) VALUES (?, ?), (?, ?), (?, ?)', 427 ['post1', 1, 'post2', 1, 'post3', 2], 428 nop, 429 onError 430 ); 431 tx.executeSql('PRAGMA foreign_keys=off;', [], nop, onError); 432 }, 433 reject, 434 () => { 435 resolve(null); 436 } 437 ); 438 }); 439 await new Promise((resolve, reject) => { 440 db.transaction( 441 (tx) => { 442 const nop = () => {}; 443 const onError = (tx, error) => { 444 reject(error); 445 return false; 446 }; 447 tx.executeSql('PRAGMA foreign_keys=on;', [], nop, onError); 448 tx.executeSql( 449 'DELETE FROM Users WHERE name=?', 450 ['name1'], 451 (tx, results) => { 452 t.expect(results.rowsAffected).toEqual(1); 453 }, 454 onError 455 ); 456 tx.executeSql( 457 'DELETE FROM Users WHERE name=? OR name=?', 458 ['name2', 'name3'], 459 (tx, results) => { 460 t.expect(results.rowsAffected).toEqual(2); 461 }, 462 onError 463 ); 464 465 tx.executeSql( 466 // ensure deletion succeeded 467 'SELECT * from Users', 468 [], 469 (tx, results) => { 470 t.expect(results.rows.length).toEqual(0); 471 }, 472 onError 473 ); 474 475 tx.executeSql( 476 'SELECT * from Posts', 477 [], 478 (tx, results) => { 479 t.expect(results.rows.length).toEqual(0); 480 }, 481 onError 482 ); 483 tx.executeSql('PRAGMA foreign_keys=off;', [], nop, onError); 484 }, 485 reject, 486 () => { 487 resolve(null); 488 } 489 ); 490 }); 491 }); 492 } 493 494 if (Platform.OS !== 'web') { 495 t.it('should delete db on filesystem from the `deleteAsync()` call', async () => { 496 const db = SQLite.openDatabase('test.db'); 497 let fileInfo = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 498 t.expect(fileInfo.exists).toBeTruthy(); 499 500 await db.closeAsync(); 501 await db.deleteAsync(); 502 fileInfo = await FS.getInfoAsync(`${FS.documentDirectory}SQLite/test.db`); 503 t.expect(fileInfo.exists).toBeFalsy(); 504 }); 505 } 506 }); 507 508 if (Platform.OS !== 'web') { 509 t.describe('SQLiteAsync', () => { 510 const throws = async (run) => { 511 let error = null; 512 try { 513 await run(); 514 } catch (e) { 515 error = e; 516 } 517 t.expect(error).toBeTruthy(); 518 }; 519 520 t.it('should support async transaction', async () => { 521 const db = SQLite.openDatabase('test.db'); 522 523 // create table 524 await db.transactionAsync(async (tx) => { 525 await tx.executeSqlAsync('DROP TABLE IF EXISTS Users;', []); 526 await tx.executeSqlAsync( 527 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 528 [] 529 ); 530 }); 531 532 // fetch data from network 533 async function fakeUserFetcher(userID) { 534 switch (userID) { 535 case 1: { 536 return Promise.resolve('Tim Duncan'); 537 } 538 case 2: { 539 return Promise.resolve('Manu Ginobili'); 540 } 541 case 3: { 542 return Promise.resolve('Nikhilesh Sigatapu'); 543 } 544 default: { 545 return null; 546 } 547 } 548 } 549 550 const userName = await fakeUserFetcher(1); 551 await db.transactionAsync(async (tx) => { 552 await tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', [userName]); 553 const result = await tx.executeSqlAsync('SELECT * FROM Users LIMIT 1'); 554 assert(!isResultSetError(result)); 555 const currentUser = result.rows[0].name; 556 t.expect(currentUser).toEqual('Tim Duncan'); 557 }); 558 }); 559 560 t.it('should support Promise.all', async () => { 561 const db = SQLite.openDatabase('test.db'); 562 563 // create table 564 await db.transactionAsync(async (tx) => { 565 await tx.executeSqlAsync('DROP TABLE IF EXISTS Users;', []); 566 await tx.executeSqlAsync( 567 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 568 [] 569 ); 570 }); 571 572 await db.transactionAsync(async (tx) => { 573 await Promise.all([ 574 tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['aaa']), 575 tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['bbb']), 576 tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['ccc']), 577 ]); 578 579 const result = await tx.executeSqlAsync('SELECT COUNT(*) FROM Users'); 580 assert(!isResultSetError(result)); 581 const recordCount = result.rows[0]['COUNT(*)']; 582 t.expect(recordCount).toEqual(3); 583 }); 584 }); 585 586 t.it( 587 'should return `could not prepare ...` error when having write statements in readOnly transaction', 588 async () => { 589 const db = SQLite.openDatabase('test.db'); 590 591 // create table in readOnly transaction 592 await db.transactionAsync(async (tx) => { 593 const result = await tx.executeSqlAsync('DROP TABLE IF EXISTS Users;', []); 594 assert(isResultSetError(result)); 595 t.expect(result.error).toBeDefined(); 596 t.expect(result.error.message).toContain('could not prepare '); 597 }, true); 598 } 599 ); 600 601 t.it('should rollback transaction when exception happens inside a transaction', async () => { 602 const db = SQLite.openDatabase('test.db'); 603 604 // create table 605 await db.transactionAsync(async (tx) => { 606 await tx.executeSqlAsync('DROP TABLE IF EXISTS Users;', []); 607 await tx.executeSqlAsync( 608 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 609 [] 610 ); 611 }); 612 await db.transactionAsync(async (tx) => { 613 await tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['aaa']); 614 }); 615 await db.transactionAsync(async (tx) => { 616 const result = await tx.executeSqlAsync('SELECT COUNT(*) FROM Users'); 617 assert(!isResultSetError(result)); 618 const recordCount = result.rows[0]['COUNT(*)']; 619 t.expect(recordCount).toEqual(1); 620 }, true); 621 622 await throws(() => 623 db.transactionAsync(async (tx) => { 624 await tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['bbb']); 625 await tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['ccc']); 626 // exeuting invalid sql statement will throw an exception 627 await tx.executeSqlAsync(undefined); 628 }) 629 ); 630 631 await db.transactionAsync(async (tx) => { 632 const result = await tx.executeSqlAsync('SELECT COUNT(*) FROM Users'); 633 assert(!isResultSetError(result)); 634 const recordCount = result.rows[0]['COUNT(*)']; 635 t.expect(recordCount).toEqual(1); 636 }, true); 637 }); 638 639 t.it('should support async PRAGMA statements', async () => { 640 const db = SQLite.openDatabase('test.db'); 641 await db.transactionAsync(async (tx) => { 642 await tx.executeSqlAsync('DROP TABLE IF EXISTS SomeTable;', []); 643 await tx.executeSqlAsync( 644 'CREATE TABLE IF NOT EXISTS SomeTable (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 645 [] 646 ); 647 // a result-returning pragma 648 let result = await tx.executeSqlAsync('PRAGMA table_info(SomeTable);', []); 649 assert(!isResultSetError(result)); 650 t.expect(result.rows.length).toEqual(2); 651 t.expect(result.rows[0].name).toEqual('id'); 652 t.expect(result.rows[1].name).toEqual('name'); 653 // a no-result pragma 654 await tx.executeSqlAsync('PRAGMA case_sensitive_like = true;', []); 655 // a setter/getter pragma 656 await tx.executeSqlAsync('PRAGMA user_version = 123;', []); 657 result = await tx.executeSqlAsync('PRAGMA user_version;', []); 658 assert(!isResultSetError(result)); 659 t.expect(result.rows.length).toEqual(1); 660 t.expect(result.rows[0].user_version).toEqual(123); 661 }); 662 }); 663 }); // t.describe('SQLiteAsync') 664 } 665} 666 667function isResultSetError( 668 result: SQLite.ResultSet | SQLite.ResultSetError 669): result is SQLite.ResultSetError { 670 return 'error' in result; 671} 672