test/test-sqlite.sgl — sigil-sqlite

1;;; test.sgl - SQLite Module Tests

2;;;

3;;; Tests basic SQLite functionality.

5(import (sigil core)

6 (sigil sqlite))

8(display "Testing SQLite module...\n")

10;; Test database operations

11(display "1. Opening in-memory database... ")

12(define db (sqlite-open ":memory:"))

13(if db

14 (display "OK\n")

15 (begin (display "FAILED\n") (exit 1)))

17;; Test table creation

18(display "2. Creating table... ")

19(if (sqlite-exec db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")

20 (display "OK\n")

21 (begin (display "FAILED\n") (exit 1)))

23;; Test insertion with sqlite-run

24(display "3. Inserting rows... ")

25(sqlite-run db "INSERT INTO users (name, email) VALUES (?, ?)" "Alice" "[email protected]")

26(sqlite-run db "INSERT INTO users (name, email) VALUES (?, ?)" "Bob" "[email protected]")

27(sqlite-run db "INSERT INTO users (name, email) VALUES (?, ?)" "Charlie" "[email protected]")

28(display "OK\n")

30;; Test query

31(display "4. Querying all users... ")

32(define users (sqlite-query db "SELECT * FROM users"))

33(if (= (length users) 3)

34 (display "OK\n")

35 (begin (display "FAILED - expected 3 rows, got ")

36 (display (length users))

37 (display "\n")

38 (exit 1)))

40;; Print results

41(display " Results:\n")

42(for-each

43 (lambda (row)

44 (display " - ")

45 (display (assoc-ref 'name row))

46 (display " <")

47 (display (assoc-ref 'email row))

48 (display ">\n"))

49 users)

51;; Test query-row

52(display "5. Query single row... ")

53(define alice (sqlite-query-row db "SELECT * FROM users WHERE name = ?" "Alice"))

54(if (and alice (string=? (assoc-ref 'email alice) "[email protected]"))

55 (display "OK\n")

56 (begin (display "FAILED\n") (exit 1)))

58;; Test update

59(display "6. Updating row... ")

60(sqlite-run db "UPDATE users SET email = ? WHERE name = ?" "[email protected]" "Alice")

61(define updated (sqlite-query-row db "SELECT * FROM users WHERE name = ?" "Alice"))

62(if (string=? (assoc-ref 'email updated) "[email protected]")

63 (display "OK\n")

64 (begin (display "FAILED\n") (exit 1)))

66;; Test delete

67(display "7. Deleting row... ")

68(sqlite-run db "DELETE FROM users WHERE name = ?" "Charlie")

69(define remaining (sqlite-query db "SELECT * FROM users"))

70(if (= (length remaining) 2)

71 (display "OK\n")

72 (begin (display "FAILED\n") (exit 1)))

74;; Test last-insert-rowid

75(display "8. Testing last-insert-rowid... ")

76(sqlite-run db "INSERT INTO users (name, email) VALUES (?, ?)" "David" "[email protected]")

77(define rowid (sqlite-last-insert-rowid db))

78(if (> rowid 0)

79 (begin (display "OK (rowid: ")

80 (display rowid)

81 (display ")\n"))

82 (begin (display "FAILED\n") (exit 1)))

84;; Test changes count

85(display "9. Testing changes count... ")

86(sqlite-run db "UPDATE users SET email = '[email protected]' WHERE id > 0")

87(define changes (sqlite-changes db))

88(if (> changes 0)

89 (begin (display "OK (changed: ")

90 (display changes)

91 (display " rows)\n"))

92 (begin (display "FAILED\n") (exit 1)))

94;; Close database

95(display "10. Closing database... ")

96(if (sqlite-close db)

97 (display "OK\n")

98 (begin (display "FAILED\n") (exit 1)))

100(display "\nAll tests passed!\n")