1 ! Copyright (C) 2008 Doug Coleman.
2 ! See http://factorcode.org/license.txt for BSD license.
3 USING: io.files kernel tools.test db db.tuples classes
4 db.types continuations namespaces math math.ranges
5 prettyprint calendar sequences db.sqlite math.intervals
6 db.postgresql accessors random math.bitwise
7 math.ranges strings urls fry db.tuples.private ;
10 : sqlite-db ( -- sqlite-db )
11 "tuples-test.db" temp-file <sqlite-db> ;
13 : test-sqlite ( quot -- )
16 "tuples-test.db" temp-file <sqlite-db> _ with-db
20 : postgresql-db ( -- postgresql-db )
24 "thepasswordistrust" >>password
25 "factor-test" >>database ;
27 : test-postgresql ( quot -- )
29 [ ] [ postgresql-db _ with-db ] unit-test
32 ! These words leak resources, but are useful for interactivel testing
33 : sqlite-test-db ( -- )
34 sqlite-db db-open db set ;
36 : postgresql-test-db ( -- )
37 postgresql-db db-open db set ;
39 TUPLE: person the-id the-name the-number the-real
40 ts date time blob factor-blob url ;
42 : <person> ( name age real ts date time blob factor-blob url -- person )
54 : <user-assigned-person> ( id name age real ts date time blob factor-blob url -- person )
64 [ ] [ person recreate-table ] unit-test
65 [ ] [ person ensure-table ] unit-test
66 [ ] [ person drop-table ] unit-test
67 [ ] [ person create-table ] unit-test
68 [ person create-table ] must-fail
69 [ ] [ person ensure-table ] unit-test
71 [ ] [ person1 get insert-tuple ] unit-test
73 [ 1 ] [ person1 get the-id>> ] unit-test
75 [ ] [ person1 get 200 >>the-number drop ] unit-test
77 [ ] [ person1 get update-tuple ] unit-test
79 [ T{ person f 1 "billy" 200 3.14 } ]
80 [ T{ person f 1 } select-tuple ] unit-test
81 [ ] [ person2 get insert-tuple ] unit-test
84 T{ person f 1 "billy" 200 3.14 }
85 T{ person f 2 "johnny" 10 3.14 }
87 ] [ T{ person f f f f 3.14 } select-tuples ] unit-test
90 T{ person f 1 "billy" 200 3.14 }
91 T{ person f 2 "johnny" 10 3.14 }
93 ] [ T{ person f } select-tuples ] unit-test
97 T{ person f 2 "johnny" 10 3.14 }
99 ] [ T{ person f f f 10 3.14 } select-tuples ] unit-test
102 [ ] [ person1 get delete-tuples ] unit-test
103 [ f ] [ T{ person f 1 } select-tuple ] unit-test
105 [ ] [ person3 get insert-tuple ] unit-test
115 T{ timestamp f 2008 3 5 16 24 11 T{ duration f 0 0 0 0 0 0 } }
116 T{ timestamp f 2008 11 22 0 0 0 T{ duration f 0 0 0 0 0 0 } }
117 T{ timestamp f 0 0 0 12 34 56 T{ duration f 0 0 0 0 0 0 } }
118 B{ 115 116 111 114 101 105 110 97 98 108 111 98 }
120 ] [ T{ person f 3 } select-tuple ] unit-test
122 [ ] [ person4 get insert-tuple ] unit-test
131 T{ timestamp f 2008 3 5 16 24 11 T{ duration f 0 0 0 0 0 0 } }
132 T{ timestamp f 2008 11 22 0 0 0 T{ duration f 0 0 0 0 0 0 } }
133 T{ timestamp f 0 0 0 12 34 56 T{ duration f 0 0 0 0 0 0 } }
135 H{ { 1 2 } { 3 4 } { 5 "lol" } }
136 URL" http://www.google.com/search?hl=en&q=trailer+park+boys&btnG=Google+Search"
138 ] [ T{ person f 4 } select-tuple ] unit-test
140 [ ] [ person drop-table ] unit-test ;
142 : db-assigned-person-schema ( -- )
145 { "the-id" "ID" +db-assigned-id+ }
146 { "the-name" "NAME" { VARCHAR 256 } +not-null+ }
147 { "the-number" "AGE" INTEGER { +default+ 0 } }
148 { "the-real" "REAL" DOUBLE { +default+ 0.3 } }
149 { "ts" "TS" TIMESTAMP }
153 { "factor-blob" "FB" FACTOR-BLOB }
156 "billy" 10 3.14 f f f f f f <person> person1 set
157 "johnny" 10 3.14 f f f f f f <person> person2 set
159 T{ timestamp f 2008 3 5 16 24 11 T{ duration f 0 0 0 0 0 0 } }
160 T{ timestamp f 2008 11 22 0 0 0 T{ duration f 0 0 0 0 0 0 } }
161 T{ timestamp f 0 0 0 12 34 56 T{ duration f 0 0 0 0 0 0 } }
162 B{ 115 116 111 114 101 105 110 97 98 108 111 98 } f f <person> person3 set
164 T{ timestamp f 2008 3 5 16 24 11 T{ duration f 0 0 0 0 0 0 } }
165 T{ timestamp f 2008 11 22 0 0 0 T{ duration f 0 0 0 0 0 0 } }
166 T{ timestamp f 0 0 0 12 34 56 T{ duration f 0 0 0 0 0 0 } }
167 f H{ { 1 2 } { 3 4 } { 5 "lol" } } URL" http://www.google.com/search?hl=en&q=trailer+park+boys&btnG=Google+Search" <person> person4 set ;
169 : user-assigned-person-schema ( -- )
172 { "the-id" "ID" INTEGER +user-assigned-id+ }
173 { "the-name" "NAME" { VARCHAR 256 } +not-null+ }
174 { "the-number" "AGE" INTEGER { +default+ 0 } }
175 { "the-real" "REAL" DOUBLE { +default+ 0.3 } }
176 { "ts" "TS" TIMESTAMP }
180 { "factor-blob" "FB" FACTOR-BLOB }
183 1 "billy" 10 3.14 f f f f f f <user-assigned-person> person1 set
184 2 "johnny" 10 3.14 f f f f f f <user-assigned-person> person2 set
186 T{ timestamp f 2008 3 5 16 24 11 T{ duration f 0 0 0 0 0 0 } }
187 T{ timestamp f 2008 11 22 0 0 0 T{ duration f 0 0 0 0 0 0 } }
188 T{ timestamp f 0 0 0 12 34 56 T{ duration f 0 0 0 0 0 0 } }
189 B{ 115 116 111 114 101 105 110 97 98 108 111 98 }
190 f f <user-assigned-person> person3 set
192 T{ timestamp f 2008 3 5 16 24 11 T{ duration f 0 0 0 0 0 0 } }
193 T{ timestamp f 2008 11 22 0 0 0 T{ duration f 0 0 0 0 0 0 } }
194 T{ timestamp f 0 0 0 12 34 56 T{ duration f 0 0 0 0 0 0 } }
195 f H{ { 1 2 } { 3 4 } { 5 "lol" } } URL" http://www.google.com/search?hl=en&q=trailer+park+boys&btnG=Google+Search" <user-assigned-person> person4 set ;
198 TUPLE: paste n summary author channel mode contents timestamp annotations ;
199 TUPLE: annotation n paste-id summary author mode contents ;
203 { "n" "ID" +db-assigned-id+ }
204 { "summary" "SUMMARY" TEXT }
205 { "author" "AUTHOR" TEXT }
206 { "channel" "CHANNEL" TEXT }
207 { "mode" "MODE" TEXT }
208 { "contents" "CONTENTS" TEXT }
209 { "timestamp" "DATE" TIMESTAMP }
210 { "annotations" { +has-many+ annotation } }
213 : annotation-schema-foreign-key ( -- )
214 annotation "ANNOTATION"
216 { "n" "ID" +db-assigned-id+ }
217 { "paste-id" "PASTE_ID" INTEGER { +foreign-id+ paste "ID" } }
218 { "summary" "SUMMARY" TEXT }
219 { "author" "AUTHOR" TEXT }
220 { "mode" "MODE" TEXT }
221 { "contents" "CONTENTS" TEXT }
222 } define-persistent ;
224 : annotation-schema-foreign-key-not-null ( -- )
225 annotation "ANNOTATION"
227 { "n" "ID" +db-assigned-id+ }
228 { "paste-id" "PASTE_ID" INTEGER { +foreign-id+ paste "ID" } +not-null+ }
229 { "summary" "SUMMARY" TEXT }
230 { "author" "AUTHOR" TEXT }
231 { "mode" "MODE" TEXT }
232 { "contents" "CONTENTS" TEXT }
233 } define-persistent ;
235 : annotation-schema-cascade ( -- )
236 annotation "ANNOTATION"
238 { "n" "ID" +db-assigned-id+ }
239 { "paste-id" "PASTE_ID" INTEGER { +foreign-id+ paste "ID" }
240 +on-delete+ +cascade+ }
241 { "summary" "SUMMARY" TEXT }
242 { "author" "AUTHOR" TEXT }
243 { "mode" "MODE" TEXT }
244 { "contents" "CONTENTS" TEXT }
245 } define-persistent ;
247 : annotation-schema-restrict ( -- )
248 annotation "ANNOTATION"
250 { "n" "ID" +db-assigned-id+ }
251 { "paste-id" "PASTE_ID" INTEGER { +foreign-id+ paste "ID" } }
252 { "summary" "SUMMARY" TEXT }
253 { "author" "AUTHOR" TEXT }
254 { "mode" "MODE" TEXT }
255 { "contents" "CONTENTS" TEXT }
256 } define-persistent ;
258 : test-paste-schema ( -- )
259 [ ] [ paste ensure-table ] unit-test
260 [ ] [ annotation ensure-table ] unit-test
261 [ ] [ annotation drop-table ] unit-test
262 [ ] [ paste drop-table ] unit-test
263 [ ] [ paste create-table ] unit-test
264 [ ] [ annotation create-table ] unit-test
271 "contents1" >>contents
279 "annotation1" >>summary
281 "annotation contents" >>contents
285 : test-foreign-key ( -- )
286 [ ] [ annotation-schema-foreign-key ] unit-test
288 [ paste new 1 >>n delete-tuples ] must-fail ;
290 : test-foreign-key-not-null ( -- )
291 [ ] [ annotation-schema-foreign-key-not-null ] unit-test
293 [ paste new 1 >>n delete-tuples ] must-fail ;
295 : test-cascade ( -- )
296 [ ] [ annotation-schema-cascade ] unit-test
298 [ ] [ paste new 1 >>n delete-tuples ] unit-test
299 [ 0 ] [ paste new select-tuples length ] unit-test ;
301 : test-restrict ( -- )
302 [ ] [ annotation-schema-restrict ] unit-test
304 [ paste new 1 >>n delete-tuples ] must-fail ;
306 [ test-foreign-key ] test-sqlite
307 [ test-foreign-key-not-null ] test-sqlite
308 [ test-cascade ] test-sqlite
309 [ test-restrict ] test-sqlite
311 [ test-foreign-key ] test-postgresql
312 [ test-foreign-key-not-null ] test-postgresql
313 [ test-cascade ] test-postgresql
314 [ test-restrict ] test-postgresql
316 : test-repeated-insert
317 [ ] [ person ensure-table ] unit-test
318 [ ] [ person1 get insert-tuple ] unit-test
319 [ person1 get insert-tuple ] must-fail ;
321 TUPLE: serialize-me id data ;
323 : test-serialize ( -- )
324 serialize-me "SERIALIZED"
326 { "id" "ID" +db-assigned-id+ }
327 { "data" "DATA" FACTOR-BLOB }
329 [ serialize-me drop-table ] [ drop ] recover
330 [ ] [ serialize-me create-table ] unit-test
332 [ ] [ T{ serialize-me f f H{ { 1 2 } } } insert-tuple ] unit-test
334 { T{ serialize-me f 1 H{ { 1 2 } } } }
335 ] [ T{ serialize-me f 1 } select-tuples ] unit-test ;
337 TUPLE: exam id name score ;
339 : random-exam ( -- exam )
341 6 [ CHAR: a CHAR: z [a,b] random ] replicate >string
345 : test-intervals ( -- )
349 { "idd" "ID" +db-assigned-id+ }
350 { "named" "NAME" TEXT }
351 { "score" "SCORE" INTEGER }
354 seq>> { "idd" "named" } =
359 { "id" "ID" +db-assigned-id+ }
360 { "name" "NAME" TEXT }
361 { "score" "SCORE" INTEGER }
363 [ exam drop-table ] [ drop ] recover
364 [ ] [ exam create-table ] unit-test
366 [ ] [ T{ exam f f "Kyle" 100 } insert-tuple ] unit-test
367 [ ] [ T{ exam f f "Stan" 80 } insert-tuple ] unit-test
368 [ ] [ T{ exam f f "Kenny" 60 } insert-tuple ] unit-test
369 [ ] [ T{ exam f f "Cartman" 41 } insert-tuple ] unit-test
372 [ T{ exam { name IGNORE } { score IGNORE } } select-tuples length ] unit-test
375 [ T{ exam { name IGNORE } { score IGNORE } } select-tuples first score>> ] unit-test
377 [ T{ exam { name IGNORE } { score IGNORE } { id IGNORE } } select-tuples first score>> ] [ class>> "EXAM" = ] must-fail-with
381 T{ exam f 3 "Kenny" 60 }
382 T{ exam f 4 "Cartman" 41 }
385 T{ exam f f f T{ interval f { 0 t } { 70 t } } } select-tuples
391 T{ exam f T{ interval f { 3 f } { 4 f } } f } select-tuples
395 T{ exam f 4 "Cartman" 41 }
398 T{ exam f T{ interval f { 3 f } { 4 t } } f } select-tuples
402 T{ exam f 3 "Kenny" 60 }
405 T{ exam f T{ interval f { 3 t } { 4 f } } f } select-tuples
409 T{ exam f 3 "Kenny" 60 }
410 T{ exam f 4 "Cartman" 41 }
413 T{ exam f T{ interval f { 3 t } { 4 t } } f } select-tuples
418 T{ exam f 1 "Kyle" 100 }
419 T{ exam f 2 "Stan" 80 }
422 T{ exam f f { "Stan" "Kyle" } } select-tuples
427 T{ exam f 1 "Kyle" 100 }
428 T{ exam f 2 "Stan" 80 }
429 T{ exam f 3 "Kenny" 60 }
432 T{ exam f T{ range f 1 3 1 } } select-tuples
437 T{ exam f 2 "Stan" 80 }
438 T{ exam f 3 "Kenny" 60 }
439 T{ exam f 4 "Cartman" 41 }
442 T{ exam f T{ interval f { 2 t } { 1.0/0.0 f } } } select-tuples
447 T{ exam f 1 "Kyle" 100 }
450 T{ exam f T{ interval f { -1.0/0.0 t } { 2 f } } } select-tuples
455 T{ exam f 1 "Kyle" 100 }
456 T{ exam f 2 "Stan" 80 }
457 T{ exam f 3 "Kenny" 60 }
458 T{ exam f 4 "Cartman" 41 }
461 T{ exam f T{ interval f { -1.0/0.0 t } { 1/0. f } } } select-tuples
466 T{ exam f 1 "Kyle" 100 }
467 T{ exam f 2 "Stan" 80 }
468 T{ exam f 3 "Kenny" 60 }
469 T{ exam f 4 "Cartman" 41 }
472 T{ exam } select-tuples
475 [ 4 ] [ T{ exam } count-tuples ] unit-test ;
477 TUPLE: bignum-test id m n o ;
478 : <bignum-test> ( m n o -- obj )
485 bignum-test "BIGNUM_TEST"
487 { "id" "ID" +db-assigned-id+ }
488 { "m" "M" BIG-INTEGER }
489 { "n" "N" UNSIGNED-BIG-INTEGER }
490 { "o" "O" SIGNED-BIG-INTEGER }
492 [ bignum-test drop-table ] ignore-errors
493 [ ] [ bignum-test ensure-table ] unit-test
494 [ ] [ 63 2^ 1- dup dup <bignum-test> insert-tuple ] unit-test ;
497 ! [ T{ bignum-test f 1
498 ! -9223372036854775808 9223372036854775808 -9223372036854775808 } ]
499 ! [ T{ bignum-test f 1 } select-tuple ] unit-test ;
501 TUPLE: secret n message ;
507 { "n" "ID" +random-id+ system-random-generator }
508 { "message" "MESSAGE" TEXT }
511 [ ] [ secret recreate-table ] unit-test
513 [ t ] [ f "kilroy was here" <secret> [ insert-tuple ] keep n>> integer? ] unit-test
515 [ ] [ f "kilroy was here2" <secret> insert-tuple ] unit-test
517 [ ] [ f "kilroy was here3" <secret> insert-tuple ] unit-test
520 T{ secret } select-tuples
521 first message>> "kilroy was here" head?
525 T{ secret } select-tuples length 3 =
528 [ db-assigned-person-schema test-tuples ] test-sqlite
529 [ user-assigned-person-schema test-tuples ] test-sqlite
530 [ user-assigned-person-schema test-repeated-insert ] test-sqlite
531 [ test-bignum ] test-sqlite
532 [ test-serialize ] test-sqlite
533 [ test-intervals ] test-sqlite
534 [ test-random-id ] test-sqlite
536 [ db-assigned-person-schema test-tuples ] test-postgresql
537 [ user-assigned-person-schema test-tuples ] test-postgresql
538 [ user-assigned-person-schema test-repeated-insert ] test-postgresql
539 [ test-bignum ] test-postgresql
540 [ test-serialize ] test-postgresql
541 [ test-intervals ] test-postgresql
542 [ test-random-id ] test-postgresql
544 TUPLE: does-not-persist ;
547 [ does-not-persist create-sql-statement ]
548 [ class \ not-persistent = ] must-fail-with
552 [ does-not-persist create-sql-statement ]
553 [ class \ not-persistent = ] must-fail-with
557 TUPLE: suparclass id a ;
560 { "id" "ID" +db-assigned-id+ }
564 TUPLE: subbclass < suparclass b ;
566 subbclass "SUBCLASS" {
570 TUPLE: fubbclass < subbclass ;
572 fubbclass "FUBCLASS" { } define-persistent
574 : test-db-inheritance ( -- )
575 [ ] [ subbclass ensure-table ] unit-test
576 [ ] [ fubbclass ensure-table ] unit-test
579 subbclass new 5 >>a "hi" >>b dup insert-tuple id>> "id" set
583 subbclass new "id" get >>id select-tuple
584 [ subbclass? ] [ b>> ] [ a>> ] tri
587 [ ] [ fubbclass new 0 >>a "hi" >>b insert-tuple ] unit-test
589 [ t ] [ fubbclass new select-tuples [ fubbclass? ] all? ] unit-test ;
591 [ test-db-inheritance ] test-sqlite
592 [ test-db-inheritance ] test-postgresql
595 TUPLE: string-encoding-test id string ;
597 string-encoding-test "STRING_ENCODING_TEST" {
598 { "id" "ID" +db-assigned-id+ }
599 { "string" "STRING" TEXT }
602 : test-string-encoding ( -- )
603 [ ] [ string-encoding-test ensure-table ] unit-test
606 string-encoding-test new
607 "\u{copyright-sign}\u{bengali-letter-cha}" >>string
608 [ insert-tuple ] [ id>> "id" set ] bi
611 [ "\u{copyright-sign}\u{bengali-letter-cha}" ] [
612 string-encoding-test new "id" get >>id select-tuple string>>
615 [ test-string-encoding ] test-sqlite
616 [ test-string-encoding ] test-postgresql
618 ! Don't comment these out. These words must infer
619 \ bind-tuple must-infer
620 \ insert-tuple must-infer
621 \ update-tuple must-infer
622 \ delete-tuples must-infer
623 \ select-tuple must-infer
624 \ define-persistent must-infer
625 \ ensure-table must-infer
626 \ create-table must-infer
627 \ drop-table must-infer
629 : test-queries ( -- )
630 [ ] [ exam ensure-table ] unit-test
631 [ ] [ 1000 [ random-exam insert-tuple ] times ] unit-test
634 T{ exam { score T{ interval { from { 0 t } } { to { 100 t } } } } }
636 5 >>limit select-tuples length
639 TUPLE: compound-foo a b c ;
641 compound-foo "COMPOUND_FOO"
643 { "a" "A" INTEGER +user-assigned-id+ }
644 { "b" "B" INTEGER +user-assigned-id+ }
648 : test-compound-primary-key ( -- )
649 [ ] [ compound-foo ensure-table ] unit-test
650 [ ] [ compound-foo drop-table ] unit-test
651 [ ] [ compound-foo create-table ] unit-test
652 [ ] [ 1 2 3 compound-foo boa insert-tuple ] unit-test
653 [ 1 2 3 compound-foo boa insert-tuple ] must-fail
654 [ ] [ 2 3 4 compound-foo boa insert-tuple ] unit-test
655 [ T{ compound-foo { a 2 } { b 3 } { c 4 } } ]
656 [ compound-foo new 4 >>c select-tuple ] unit-test ;
658 [ test-compound-primary-key ] test-sqlite
659 [ test-compound-primary-key ] test-postgresql