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 load crsqlite extension correctly', async () => { 561 const db = SQLite.openDatabase('test.db'); 562 await db.execAsync([{ sql: 'DROP TABLE IF EXISTS foo;', args: [] }], false); 563 await db.execAsync([{ sql: 'create table foo (a primary key, b);', args: [] }], false); 564 await db.execAsync([{ sql: 'select crsql_as_crr("foo");', args: [] }], false); 565 await db.execAsync([{ sql: 'insert into foo (a,b) values (1,2);', args: [] }], false); 566 const result = await db.execAsync( 567 [{ sql: 'select * from crsql_changes;', args: [] }], 568 false 569 ); 570 console.log('ooxx r', JSON.stringify(result, false, 2)); 571 }); 572 573 t.it('should support Promise.all', async () => { 574 const db = SQLite.openDatabase('test.db'); 575 576 // create table 577 await db.transactionAsync(async (tx) => { 578 await tx.executeSqlAsync('DROP TABLE IF EXISTS Users;', []); 579 await tx.executeSqlAsync( 580 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 581 [] 582 ); 583 }); 584 585 await db.transactionAsync(async (tx) => { 586 await Promise.all([ 587 tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['aaa']), 588 tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['bbb']), 589 tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['ccc']), 590 ]); 591 592 const result = await tx.executeSqlAsync('SELECT COUNT(*) FROM Users'); 593 assert(!isResultSetError(result)); 594 const recordCount = result.rows[0]['COUNT(*)']; 595 t.expect(recordCount).toEqual(3); 596 }); 597 }); 598 599 t.it( 600 'should return `could not prepare ...` error when having write statements in readOnly transaction', 601 async () => { 602 const db = SQLite.openDatabase('test.db'); 603 604 // create table in readOnly transaction 605 await db.transactionAsync(async (tx) => { 606 const result = await tx.executeSqlAsync('DROP TABLE IF EXISTS Users;', []); 607 assert(isResultSetError(result)); 608 t.expect(result.error).toBeDefined(); 609 t.expect(result.error.message).toContain('could not prepare '); 610 }, true); 611 } 612 ); 613 614 t.it('should rollback transaction when exception happens inside a transaction', async () => { 615 const db = SQLite.openDatabase('test.db'); 616 617 // create table 618 await db.transactionAsync(async (tx) => { 619 await tx.executeSqlAsync('DROP TABLE IF EXISTS Users;', []); 620 await tx.executeSqlAsync( 621 'CREATE TABLE IF NOT EXISTS Users (user_id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 622 [] 623 ); 624 }); 625 await db.transactionAsync(async (tx) => { 626 await tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['aaa']); 627 }); 628 await db.transactionAsync(async (tx) => { 629 const result = await tx.executeSqlAsync('SELECT COUNT(*) FROM Users'); 630 assert(!isResultSetError(result)); 631 const recordCount = result.rows[0]['COUNT(*)']; 632 t.expect(recordCount).toEqual(1); 633 }, true); 634 635 await throws(() => 636 db.transactionAsync(async (tx) => { 637 await tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['bbb']); 638 await tx.executeSqlAsync('INSERT INTO Users (name) VALUES (?)', ['ccc']); 639 // exeuting invalid sql statement will throw an exception 640 await tx.executeSqlAsync(undefined); 641 }) 642 ); 643 644 await db.transactionAsync(async (tx) => { 645 const result = await tx.executeSqlAsync('SELECT COUNT(*) FROM Users'); 646 assert(!isResultSetError(result)); 647 const recordCount = result.rows[0]['COUNT(*)']; 648 t.expect(recordCount).toEqual(1); 649 }, true); 650 }); 651 652 t.it('should support async PRAGMA statements', async () => { 653 const db = SQLite.openDatabase('test.db'); 654 await db.transactionAsync(async (tx) => { 655 await tx.executeSqlAsync('DROP TABLE IF EXISTS SomeTable;', []); 656 await tx.executeSqlAsync( 657 'CREATE TABLE IF NOT EXISTS SomeTable (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(64));', 658 [] 659 ); 660 // a result-returning pragma 661 let result = await tx.executeSqlAsync('PRAGMA table_info(SomeTable);', []); 662 assert(!isResultSetError(result)); 663 t.expect(result.rows.length).toEqual(2); 664 t.expect(result.rows[0].name).toEqual('id'); 665 t.expect(result.rows[1].name).toEqual('name'); 666 // a no-result pragma 667 await tx.executeSqlAsync('PRAGMA case_sensitive_like = true;', []); 668 // a setter/getter pragma 669 await tx.executeSqlAsync('PRAGMA user_version = 123;', []); 670 result = await tx.executeSqlAsync('PRAGMA user_version;', []); 671 assert(!isResultSetError(result)); 672 t.expect(result.rows.length).toEqual(1); 673 t.expect(result.rows[0].user_version).toEqual(123); 674 }); 675 }); 676 }); // t.describe('SQLiteAsync') 677 } 678} 679 680function isResultSetError( 681 result: SQLite.ResultSet | SQLite.ResultSetError 682): result is SQLite.ResultSetError { 683 return 'error' in result; 684} 685