basis/db/sqlite/sqlite-tests.factor

   1 USING: io io.files io.files.temp io.directories io.launcher
   2 kernel namespaces prettyprint tools.test db.sqlite db sequences
   3 continuations db.types db.tuples unicode accessors arrays
   4 sorting layouts math.parser ;
   5 IN: db.sqlite.tests
   6
   7 : db-path ( -- path ) "test-" cell number>string ".db" 3append temp-file ;
   8 : test.db ( -- sqlite-db ) db-path <sqlite-db> ;
   9
  10 { } [ [ db-path delete-file ] ignore-errors ] unit-test
  11
  12 { } [
  13     test.db [
  14         "create table person (name varchar(30), country varchar(30))" sql-command
  15         "insert into person values('John', 'America')" sql-command
  16         "insert into person values('Jane', 'New Zealand')" sql-command
  17     ] with-db
  18 ] unit-test
  19
  20
  21 { { { "John" "America" } { "Jane" "New Zealand" } } } [
  22     test.db [
  23         "select * from person" sql-query
  24     ] with-db
  25 ] unit-test
  26
  27 { { { "1" "John" "America" } { "2" "Jane" "New Zealand" } } }
  28 [ test.db [ "select rowid, * from person" sql-query ] with-db ] unit-test
  29
  30 { } [
  31     test.db [
  32         "insert into person(name, country) values('Jimmy', 'Canada')"
  33         sql-command
  34     ] with-db
  35 ] unit-test
  36
  37 {
  38     {
  39         { "1" "John" "America" }
  40         { "2" "Jane" "New Zealand" }
  41         { "3" "Jimmy" "Canada" }
  42     }
  43 } [ test.db [ "select rowid, * from person" sql-query ] with-db ] unit-test
  44
  45 [
  46     test.db [
  47         [
  48             "insert into person(name, country) values('Jose', 'Mexico')" sql-command
  49             "insert into person(name, country) values('Jose', 'Mexico')" sql-command
  50             "oops" throw
  51         ] with-transaction
  52     ] with-db
  53 ] must-fail
  54
  55 { 3 } [
  56     test.db [
  57         "select * from person" sql-query length
  58     ] with-db
  59 ] unit-test
  60
  61 { } [
  62     test.db [
  63         [
  64             "insert into person(name, country) values('Jose', 'Mexico')"
  65             sql-command
  66             "insert into person(name, country) values('Jose', 'Mexico')"
  67             sql-command
  68         ] with-transaction
  69     ] with-db
  70 ] unit-test
  71
  72 { 5 } [
  73     test.db [
  74         "select * from person" sql-query length
  75     ] with-db
  76 ] unit-test
  77
  78 [ \ swap ensure-table ] must-fail
  79
  80 ! You don't need a primary key
  81 TUPLE: things one two ;
  82
  83 things "THINGS" {
  84     { "one" "ONE" INTEGER +not-null+ }
  85     { "two" "TWO" INTEGER +not-null+ }
  86 } define-persistent
  87
  88 { { { 0 0 } { 0 1 } { 1 0 } { 1 1 } } } [
  89     test.db [
  90        things create-table
  91         0 0 things boa insert-tuple
  92         0 1 things boa insert-tuple
  93         1 1 things boa insert-tuple
  94         1 0 things boa insert-tuple
  95         f f things boa select-tuples
  96         [ [ one>> ] [ two>> ] bi 2array ] map natural-sort
  97        things drop-table
  98     ] with-db
  99 ] unit-test
 100
 101 ! Tables can have different names than the name of the tuple
 102 TUPLE: foo slot ;
 103 C: <foo> foo
 104 foo "BAR" { { "slot" "SOMETHING" INTEGER +not-null+ } } define-persistent
 105
 106 TUPLE: hi bye try ;
 107 C: <hi> hi
 108 hi "HELLO" {
 109     { "bye" "BUHBYE" INTEGER { +foreign-id+ foo "SOMETHING" } }
 110     { "try" "RETHROW" INTEGER { +foreign-id+ foo "SOMETHING" } }
 111 } define-persistent
 112
 113 { T{ foo { slot 1 } } T{ hi { bye 1 } { try 1 } } } [
 114     test.db [
 115         foo create-table
 116         hi create-table
 117         1 <foo> insert-tuple
 118         f <foo> select-tuple
 119         1 1 <hi> insert-tuple
 120         f f <hi> select-tuple
 121         hi drop-table
 122         foo drop-table
 123     ] with-db
 124 ] unit-test
 125
 126
 127 ! Test SQLite triggers
 128
 129 TUPLE: show id ;
 130 TUPLE: user username data ;
 131 TUPLE: watch show user ;
 132
 133 user "USER" {
 134     { "username" "USERNAME" TEXT +not-null+ +user-assigned-id+ }
 135     { "data" "DATA" TEXT }
 136 } define-persistent
 137
 138 show "SHOW" {
 139     { "id" "ID" +db-assigned-id+ }
 140 } define-persistent
 141
 142 watch "WATCH" {
 143     { "user" "USER" TEXT +not-null+ +user-assigned-id+
 144         { +foreign-id+ user "USERNAME" } }
 145     { "show" "SHOW" BIG-INTEGER +not-null+ +user-assigned-id+
 146         { +foreign-id+ show "ID" } }
 147 } define-persistent
 148
 149 { T{ user { username "littledan" } { data "foo" } } } [
 150     test.db [
 151         user create-table
 152         show create-table
 153         watch create-table
 154         "littledan" "foo" user boa insert-tuple
 155         "mark" "bar" user boa insert-tuple
 156         show new insert-tuple
 157         show new select-tuple
 158         "littledan" f user boa select-tuple
 159         [ id>> ] [ username>> ] bi*
 160         watch boa insert-tuple
 161         watch new select-tuple
 162         user>> f user boa select-tuple
 163     ] with-db
 164 ] unit-test
 165
 166 { } [
 167     test.db [ [
 168             user ensure-table [
 169                 "mew" "foo" user boa insert-tuple
 170                 "denny" "kitty" user boa insert-tuple
 171             ] with-transaction
 172         ] with-transaction
 173     ] with-db
 174 ] unit-test