cl-postgres/tests.lisp

   1 (defpackage :cl-postgres-tests
   2   (:use :common-lisp :fiveam :cl-postgres :cl-postgres-error)
   3   (:export #:prompt-connection #:*test-connection* #:with-test-connection))
   4
   5 (in-package :cl-postgres-tests)
   6
   7 (defparameter *test-connection* '("test" "test" "" "localhost"))
   8
   9 (defun prompt-connection (&optional (list *test-connection*))
  10   (flet ((ask (name pos)
  11            (format *query-io* "~a (enter to keep '~a'): " name (nth pos list))
  12            (finish-output *query-io*)
  13            (let ((answer (read-line *query-io*)))
  14              (unless (string= answer "") (setf (nth pos list) answer)))))
  15     (format *query-io* "~%To run this test, you must configure a database connection.~%")
  16     (ask "Database name" 0)
  17     (ask "User" 1)
  18     (ask "Password" 2)
  19     (ask "Hostname" 3)))
  20
  21 ;; Adjust the above to some db/user/pass/host/[port] combination that
  22 ;; refers to a valid postgresql database, then after loading the file,
  23 ;; run the tests with (fiveam:run! :cl-postgres)
  24
  25 (def-suite :cl-postgres)
  26 (in-suite :cl-postgres)
  27
  28 (defmacro with-test-connection (&body body)
  29   `(let ((connection (apply 'open-database *test-connection*)))
  30     (unwind-protect (progn ,@body)
  31       (close-database connection))))
  32
  33 (defmacro with-default-readtable (&body body)
  34   `(let ((*sql-readtable* (default-sql-readtable)))
  35     ,@body))
  36
  37 (defmacro with-rollbacked-transaction (&body body)
  38   `(progn
  39     (exec-query connection "start transaction")
  40     (unwind-protect (progn ,@body)
  41       (exec-query connection "rollback"))))
  42
  43 (test connect-sanity
  44   (with-test-connection
  45     (is (database-open-p connection))))
  46
  47 (test simple-query
  48   (with-test-connection
  49     (destructuring-bind ((a b c d e))
  50         (exec-query connection "select 22::integer, 44.5::double precision, 'abcde'::varchar, true::boolean, 4.5::numeric(5,2)"
  51                     'list-row-reader)
  52       (is (eql a 22))
  53       (is (eql b 44.5d0))
  54       (is (string= c "abcde"))
  55       (is (eql d t))
  56       (is (eql e 9/2)))))
  57
  58 (test sql-strings
  59   (is (string= (to-sql-string :null) "NULL"))
  60   (is (string= (to-sql-string t) "true"))
  61   (is (string= (to-sql-string 400) "400"))
  62   (is (string= (to-sql-string "foo") "foo"))
  63   (is (eq t (nth-value 1 (to-sql-string "bar"))))
  64   (is (eq nil (nth-value 1 (to-sql-string 10)))))
  65
  66 (test date-query
  67   (with-default-readtable
  68     (with-test-connection
  69       (destructuring-bind ((a b c))
  70           (exec-query connection "select '1980-02-01'::date, '2010-04-05 14:42:21.500'::timestamp, '2 years -4 days'::interval"
  71                       'list-row-reader)
  72         (is (= a 2527200000))
  73         (is (= b 3479467341))
  74         (is (equal c '((:MONTHS 24) (:DAYS -4) (:SECONDS 0) (:USECONDS 0))))))))
  75
  76 (test timestamp-with-time-zone
  77   (with-default-readtable
  78     (with-test-connection
  79       (with-rollbacked-transaction
  80         ;; 1. set local time to GMT -- returned time should be what we
  81         ;; pass in -- note we lose the 500 millesconds here :(
  82         (exec-query connection "set local time zone 'GMT'")
  83         (is (equalp (exec-query connection "select '2010-04-05 14:42:21.500'::timestamp with time zone"
  84                                 'list-row-reader)
  85                     '((3479467341))))
  86         ;; 2. set local time to PST8PDT, now we should get back a time
  87         ;; that is 7 hours later than. This means that the input time
  88         ;; is specified in PST8PDT, but the returned timestamp is in
  89         ;; GMT.
  90         (exec-query connection "set time zone 'PST8PDT'")
  91         (is (equalp (exec-query connection "select '2010-04-05 14:42:21.500'::timestamp with time zone"
  92                                 'list-row-reader)
  93                     '((3479492541))))
  94         ;; 3. now we specify the time zone to be GMT but the input time
  95         ;; is in EDT, so the returned time should be 4 hours later.
  96         (exec-query connection "set time zone 'GMT'")
  97         (is (equalp (exec-query connection "select '2010-04-05 14:42:21.500'::timestamp at time zone 'America/New_York'"
  98                                 'list-row-reader)
  99                     '((3479481741))))))))
 100
 101 (test timestamp-with-time-zone-text
 102   (let ((*sql-readtable* (copy-sql-readtable)))
 103     (set-sql-reader oid:+timestamptz+ nil)
 104     (with-test-connection
 105       (with-rollbacked-transaction
 106         ;; 1. GMT input and GMT output
 107         (exec-query connection "set time zone 'GMT'")
 108         (is (equalp (exec-query connection "select '2010-04-05 14:42:21.500'::timestamp with time zone"
 109                                 'list-row-reader)
 110                     '(("2010-04-05 14:42:21.5+00"))))
 111         ;; 2. PST8PDT input and text representation of the same input
 112         ;; time with a -7 hour offset
 113         (exec-query connection "set time zone 'PST8PDT'")
 114         (is (equalp (exec-query connection "select '2010-04-05 14:42:21.500'::timestamp with time zone"
 115                                 'list-row-reader)
 116                     '(("2010-04-05 14:42:21.5-07"))))
 117         ;; 3. EDT input with GMT output (4 hours later, 0 hour offset)
 118         (exec-query connection "set time zone 'GMT'")
 119         (is (equalp (exec-query connection "select '2010-04-05 14:42:21.500'::timestamp at time zone 'America/New_York'"
 120                                 'list-row-reader)
 121                     '(("2010-04-05 18:42:21.5+00"))))))))
 122
 123 (test alist-row-reader
 124   (with-test-connection
 125     (is (equal (exec-query connection "select 42 as foo, 99 as bar" 'alist-row-reader)
 126                '((("foo" . 42) ("bar" . 99)))))))
 127
 128 (test prepared-statement
 129   (with-test-connection
 130     (prepare-query connection "test" "select $1::integer, $2::boolean, $3::text")
 131     (is (equal (exec-prepared connection "test" '(42 nil "foo") 'list-row-reader)
 132                '((42 nil "foo"))))))
 133
 134 (test unprepare-statement
 135   (with-test-connection
 136     (prepare-query connection "test" "select true")
 137     (unprepare-query connection "test")
 138     (prepare-query connection "test" "select false")
 139     (is (equal (exec-prepared connection "test" '() 'list-row-reader)
 140                '((nil))))))
 141
 142 (test prepared-array-param
 143   (with-test-connection
 144     (prepare-query connection "test" "select ($1::int[])[2]")
 145     (is (equal (exec-prepared connection "test" '(#(1 2 3)) 'list-row-reader)
 146                '((2))))
 147     (prepare-query connection "test2" "select ($1::text[])[2]")
 148     (is (equal (exec-prepared connection "test2" '(#("A" "B" "C")) 'list-row-reader)
 149                '(("B"))))))
 150
 151 (test blob
 152   (with-test-connection
 153     (let* ((str "foobar42")
 154            (bytes (coerce #(102 111 111 98 97 114 52 50) '(vector (unsigned-byte 8)))))
 155       (prepare-query connection "test" "select $1::varchar, $2::bytea")
 156       (is (equalp (exec-prepared connection "test" (list str bytes) 'list-row-reader)
 157                   (list (list str bytes)))))))
 158
 159 (test recover-error
 160   (with-test-connection
 161     (signals cl-postgres-error:syntax-error-or-access-violation
 162       (exec-query connection "gubble gubble gabble goo"))
 163     (is (equal (exec-query connection "select false" 'list-row-reader)
 164                '((nil))))))
 165
 166 (test unique-violation-error
 167   (with-test-connection
 168     (exec-query connection "create table test (id int not null primary key, name text)")
 169     (exec-query connection "insert into test values (1, 'bert')")
 170     (signals unique-violation
 171       (exec-query connection "insert into test values (1, 'harry')"))
 172     (exec-query connection "drop table test")))
 173
 174 (test sql-reader
 175   (with-test-connection
 176     (let ((*sql-readtable* (copy-sql-readtable)))
 177       (set-sql-reader 2249 (lambda (text)
 178                              (with-input-from-string (*standard-input* text)
 179                                (read-char) ;; opening paren
 180                                (let ((x (read)))
 181                                  (read-char) ;; comma
 182                                  (cons x (read))))))
 183       (is (equal (exec-query connection "select (10,20)" 'list-row-reader)
 184                  '(((10 . 20))))))
 185     (is (equal (exec-query connection "select (30,40)" 'list-row-reader)
 186                '(("(30,40)"))))))
 187
 188 (test sql-reader-binary
 189   (with-test-connection
 190     (with-binary-row-values
 191       (let ((*sql-readtable* (copy-sql-readtable)))
 192         (set-sql-reader 2249 (lambda (text)
 193                                (with-input-from-string (*standard-input* text)
 194                                  (read-char) ;; opening paren
 195                                  (let ((x (read)))
 196                                    (read-char) ;; comma
 197                                    (cons x (read))))))
 198         (is (equal (exec-query connection "select (10,20)" 'list-row-reader)
 199                    '(((10 . 20))))))
 200       (is (equal (exec-query connection "select (30,40)" 'list-row-reader)
 201                  '(((30 40))))))))
 202
 203 (test bulk-writer
 204   (with-test-connection
 205     (exec-query connection "create table test (a int, b text, c date, d timestamp, e int[])")
 206     (let ((stream (open-db-writer *test-connection* 'test '(a b c d e))))
 207       ;; test a variety of types (int, text, date, timstamp, int array)
 208       (loop for row in '((1 "one" "2012-01-01" "2012-01-01 00:00" #(1 2 3 42))
 209                          (2 "two" "2012-01-01" "2012-01-01 00:00" #(3 2 1 42))
 210
 211                          ;; make sure utf-8 gets through ok
 212                          (3 "κόσμε" "2012-01-01" "2012-01-01 00:00" #(1))
 213
 214                          ;; make sure tabs get through ok
 215                          (4 "one two    three" "2012-01-01" "2012-01-01 00:00" #(1)))
 216            do
 217            (db-write-row stream row))
 218       (close-db-writer stream))
 219     (print (exec-query connection "select * from test"))
 220     (exec-query connection "drop table test")))
 221
 222 (test row-boolean-array
 223   (with-test-connection
 224     (is (equalp (exec-query connection "select row(ARRAY[TRUE, FALSE, TRUE])" 'list-row-reader)
 225                 '(("(\"{t,f,t}\")"))))))
 226
 227 (test row-boolean-array-binary
 228   (with-test-connection
 229     (with-binary-row-values
 230       (is (equalp (exec-query connection "select row(ARRAY[TRUE, FALSE, TRUE])" 'list-row-reader)
 231                   '(((#(T NIL T)))))))))
 232
 233 (test cast-to-bits
 234   (with-test-connection
 235     (is (equalp (exec-query connection "select cast(255 as bit(8)), cast(-44 as bit(128))" 'list-row-reader)
 236                 '((#*11111111
 237                    #*11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111010100))))
 238     (is (equalp (exec-query connection "select row(cast(32 as bit(12)))" 'list-row-reader)
 239                 '(("(000000100000)"))))
 240     (is (equalp (exec-query connection "select ARRAY[cast(32 as bit(16))]" 'list-row-reader)
 241                 '((#(#*0000000000100000)))))
 242     (is (equalp (exec-query connection "select row(ARRAY[cast(32 as bit(16))])" 'list-row-reader)
 243                 '(("({0000000000100000})"))))))
 244
 245 (test cast-to-bits-binary
 246   (with-test-connection
 247     (with-binary-row-values
 248       (is (equalp (exec-query connection "select cast(255 as bit(8)), cast(-44 as bit(128))" 'list-row-reader)
 249                   '((#*11111111
 250                      #*11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111010100))))
 251       (is (equalp (exec-query connection "select row(cast(32 as bit(12)))" 'list-row-reader)
 252                   '(((#*000000100000)))))
 253       (is (equalp (exec-query connection "select ARRAY[cast(32 as bit(16))]" 'list-row-reader)
 254                   '((#(#*0000000000100000)))))
 255       (is (equalp (exec-query connection "select row(ARRAY[cast(32 as bit(16))])" 'list-row-reader)
 256                   '(((#(#*0000000000100000)))))))))
 257
 258 (test cast-to-varbits
 259   (with-test-connection
 260     (is (equalp (exec-query connection "select 255::bit(8)::varbit(8), 44::bit(128)::varbit(128)" 'list-row-reader)
 261                 '((#*11111111
 262                    #*00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000101100))))
 263     (is (equalp (exec-query connection "select row(32::bit(12)::varbit(12))" 'list-row-reader)
 264                 '(("(000000100000)"))))
 265     (is (equalp (exec-query connection "select ARRAY[32::bit(16)::varbit(16)]" 'list-row-reader)
 266                 '((#(#*0000000000100000)))))
 267     (is (equalp (exec-query connection "select row(ARRAY[32::bit(16)::varbit(16)])" 'list-row-reader)
 268                 '(("({0000000000100000})"))))))
 269
 270 (test cast-to-varbits-binary
 271   (with-test-connection
 272     (with-binary-row-values
 273       (is (equalp (exec-query connection "select 255::bit(8)::varbit(8), 44::bit(128)::varbit(128)" 'list-row-reader)
 274                   '((#*11111111
 275                      #*00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000101100))))
 276       (is (equalp (exec-query connection "select row(32::bit(12)::varbit(12))" 'list-row-reader)
 277                   '(((#*000000100000)))))
 278       (is (equalp (exec-query connection "select ARRAY[32::bit(16)::varbit(16)]" 'list-row-reader)
 279                   '((#(#*0000000000100000)))))
 280       (is (equalp (exec-query connection "select row(ARRAY[32::bit(16)::varbit(16)])" 'list-row-reader)
 281                   '(((#(#*0000000000100000)))))))))
 282
 283
 284
 285 (test row-integer-array
 286   (with-test-connection
 287     (is (equalp (exec-query connection "select row(ARRAY[1,2,4,8])" 'list-row-reader)
 288                 '(("(\"{1,2,4,8}\")"))))))
 289
 290 (test row-integer-array-binary
 291   (with-test-connection
 292     (with-binary-row-values
 293       (is (equalp (exec-query connection "select row(ARRAY[1,2,4,8])" 'list-row-reader)
 294                   '(((#(1 2 4 8)))))))))
 295
 296 (test row-string-array
 297   (with-test-connection
 298     (is (equalp (exec-query connection "select row(ARRAY['foo', 'bar', 'baz'])" 'list-row-reader)
 299                 '(("(\"{foo,bar,baz}\")"))))))
 300
 301 (test row-string-array-binary
 302   (with-test-connection
 303     (with-binary-row-values
 304       (is (equalp (exec-query connection "select row(ARRAY['foo', 'bar', 'baz'])" 'list-row-reader)
 305                   '(((#("foo" "bar" "baz")))))))))
 306
 307 (test row-bpchar-array
 308   (with-test-connection
 309     (is (equalp (exec-query connection "select row(ARRAY[cast('foo' as bpchar)])" 'list-row-reader)
 310                 '(("({foo})"))))))
 311
 312 (test row-bpchar-array-binary
 313   (with-test-connection
 314     (with-binary-row-values
 315       (is (equalp (exec-query connection "select row(ARRAY[cast('foo' as bpchar)])" 'list-row-reader)
 316                   '(((#("foo")))))))))
 317
 318 (test row-varchar-array
 319   (with-test-connection
 320     (is (equalp (exec-query connection "select row(ARRAY['foo'::varchar])" 'list-row-reader)
 321                 '(("({foo})"))))))
 322
 323 (test row-varchar-array-binary
 324   (with-test-connection
 325     (with-binary-row-values
 326       (is (equalp (exec-query connection "select row(ARRAY['foo'::varchar])" 'list-row-reader)
 327                   '(((#("foo")))))))))
 328
 329 (test row-oid-array
 330   (with-test-connection
 331     (is (equalp (exec-query connection "select row(ARRAY[1234::oid, 5678::oid])" 'list-row-reader)
 332                 '(("(\"{1234,5678}\")"))))))
 333
 334 (test row-oid-array-binary
 335   (with-test-connection
 336     (with-binary-row-values
 337       (is (equalp (exec-query connection "select row(ARRAY[1234::oid, 5678::oid])" 'list-row-reader)
 338                   '(((#(1234 5678)))))))))
 339
 340 (test row-int2-array
 341   (with-test-connection
 342     (is (equalp (exec-query connection "select row(ARRAY[1234::int2])" 'list-row-reader)
 343                 '(("({1234})"))))))
 344
 345 (test row-int2-array-binary
 346   (with-test-connection
 347     (with-binary-row-values
 348       (is (equalp (exec-query connection "select row(ARRAY[1234::int2])" 'list-row-reader)
 349                   '(((#(1234)))))))))
 350
 351 (test row-int8-array
 352   (with-test-connection
 353     (is (equalp (exec-query connection "select row(ARRAY[123456789012::int8])" 'list-row-reader)
 354                 '(("({123456789012})"))))))
 355
 356 (test row-int8-array-binary
 357   (with-test-connection
 358     (with-binary-row-values
 359       (is (equalp (exec-query connection "select row(ARRAY[123456789012::int8])" 'list-row-reader)
 360                   '(((#(123456789012)))))))))
 361
 362 (test row-float-array
 363   (with-test-connection
 364     (is (equalp (exec-query connection "select row(ARRAY[3.14::float])" 'list-row-reader)
 365                 '(("({3.14})"))))))
 366
 367 (test row-float-array-binary
 368   (with-test-connection
 369     (with-binary-row-values
 370       (is (equalp (exec-query connection "select row(ARRAY[3.14::float])" 'list-row-reader)
 371                   '(((#(3.14d0)))))))))
 372
 373 (test row-double-array
 374   (with-test-connection
 375     (is (equalp (exec-query connection "select row(ARRAY[cast(3.14 as double precision)])" 'list-row-reader)
 376                 '(("({3.14})"))))))
 377
 378 (test row-double-array-binary
 379   (with-test-connection
 380     (with-binary-row-values
 381       (is (equalp (exec-query connection "select row(ARRAY[cast(3.14 as double precision)])" 'list-row-reader)
 382                   '(((#(3.14d0)))))))))
 383
 384 (test row-date-array
 385   (with-default-readtable
 386     (with-test-connection
 387       (is (equalp (elt (exec-query connection "select row(ARRAY['1980-02-01'::date])" 'list-row-reader) 0)
 388                   '("({1980-02-01})"))))))
 389
 390 (test row-date-array-binary
 391   (with-default-readtable
 392     (with-test-connection
 393       (with-binary-row-values
 394         (is (= (elt (caaar (exec-query connection "select row(ARRAY['1980-02-01'::date])" 'list-row-reader)) 0)
 395                2527200000))))))
 396
 397 (test row-timestamp
 398   (with-test-connection
 399     (is (equalp (exec-query connection "select row('2010-04-05 14:42:21.500'::timestamp)"
 400                             'list-row-reader)
 401                 '(("(\"2010-04-05 14:42:21.5\")"))))))
 402
 403 (test row-timestamp-without-time-zone
 404   (with-test-connection
 405     (is (equalp (exec-query connection "select row('2010-04-05 14:42:21.500'::timestamp without time zone)"
 406                             'list-row-reader)
 407                 '(("(\"2010-04-05 14:42:21.5\")"))))))
 408
 409 (test row-timestamp-with-time-zone
 410   (with-test-connection
 411     (with-rollbacked-transaction
 412       (exec-query connection "set time zone 'GMT'")
 413       (is (equalp (exec-query connection "select row('2010-04-05 14:42:21.500'::timestamp with time zone)"
 414                               'list-row-reader)
 415                   '(("(\"2010-04-05 14:42:21.5+00\")")))))))
 416
 417 (test row-timestamp-with-time-zone-binary
 418   (with-default-readtable
 419     (with-test-connection
 420       (with-rollbacked-transaction
 421         (exec-query connection "set time zone 'GMT'")
 422         (with-binary-row-values
 423           (destructuring-bind (gmt pdt)
 424               (caar
 425                (exec-query
 426                 connection
 427                 (concatenate 'string
 428                              "select row('2010-04-05 14:42:21.500'::timestamp with time zone at time zone 'GMT', "
 429                              " '2010-04-05 14:42:21.500'::timestamp with time zone at time zone 'PST')")
 430                 'list-row-reader))
 431             (is (equalp (multiple-value-list gmt)
 432                         '(3479467341)))
 433             (is (equalp (multiple-value-list pdt)
 434                         '(3479438541)))))))))
 435
 436 (test row-timestamp-array
 437   (with-default-readtable
 438     (with-test-connection
 439       (is (equalp (elt (exec-query connection "select row(ARRAY['2010-04-05 14:42:21.500'::timestamp])"
 440                                    'list-row-reader) 0)
 441                   '("(\"{\"\"2010-04-05 14:42:21.5\"\"}\")"))))))
 442
 443 (test row-timestamp-array-binary
 444   (with-default-readtable
 445     (with-binary-row-values
 446         (with-test-connection
 447           (is (equalp (elt (exec-query connection "select row(ARRAY['2010-04-05 14:42:21.500'::timestamp])"
 448                                   'list-row-reader) 0)
 449                       '((#(3479467341)))))))))
 450
 451 (test row-timestamp-without-time-zone-array
 452   (with-test-connection
 453     (is (equalp (elt (exec-query connection "select row(ARRAY['2010-04-05 14:42:21.500'::timestamp without time zone])"
 454                                  'list-row-reader) 0)
 455                 '("(\"{\"\"2010-04-05 14:42:21.5\"\"}\")")))))
 456
 457 (test row-time
 458   (with-test-connection
 459     (is (equalp (exec-query connection "select row('05:00'::time)"
 460                             'list-row-reader)
 461                 '(("(05:00:00)"))))))
 462
 463 (test row-interval-array
 464   (with-default-readtable
 465     (with-test-connection
 466       (with-binary-row-values
 467         (is (equalp (elt (caaar (exec-query connection "select row(ARRAY['2 years -4 days'::interval])"
 468                                             'list-row-reader)) 0)
 469                     '((:MONTHS 24) (:DAYS -4) (:SECONDS 0) (:USECONDS 0))))))))
 470
 471 (defparameter *random-byte-count* 8192)
 472
 473 (test write-bytea
 474   (with-test-connection
 475     (exec-query connection "create table test (a bytea)")
 476     (unwind-protect
 477          (let ((random-bytes (make-array *random-byte-count* :element-type '(unsigned-byte 8))))
 478            (loop for i below *random-byte-count*
 479                          do (setf (aref random-bytes i)
 480                                   (random #x100)))
 481            (prepare-query connection "bytea-insert" "insert into test values ($1)")
 482            (exec-prepared connection "bytea-insert" (list random-bytes))
 483            (is (equalp (exec-query connection "select a from test;" 'list-row-reader)
 484                        `((,random-bytes)))))
 485       (exec-query connection "drop table test"))))
 486
 487 (defun vector-to-hex-string (vec)
 488     (with-output-to-string (s)
 489       (map nil (lambda (x)
 490                  (format s "~(~2,\'0x~)" x))
 491            vec)
 492       s))
 493
 494 (test write-row-bytea
 495   (with-test-connection
 496     (exec-query connection "create table test (a bytea)")
 497     (let ((*random-byte-count* 16))
 498       (unwind-protect
 499            (let ((random-bytes (make-array *random-byte-count* :element-type '(unsigned-byte 8))))
 500              (loop for i below *random-byte-count*
 501                 do (setf (aref random-bytes i)
 502                          (random #x100)))
 503              (prepare-query connection "bytea-insert" "insert into test values ($1)")
 504              (exec-prepared connection "bytea-insert" (list random-bytes))
 505              (is (equalp (exec-query connection "select row(a) from test;" 'list-row-reader)
 506                          `((,(concatenate 'string
 507                                           "(\"\\\\x"
 508                                           (vector-to-hex-string random-bytes)
 509                                           "\")"))))))
 510         (exec-query connection "drop table test")))))
 511
 512 (test write-row-array-bytea
 513   (with-test-connection
 514     (exec-query connection "create table test (a bytea)")
 515     (let ((*random-byte-count* 16))
 516       (unwind-protect
 517            (let ((random-bytes (make-array *random-byte-count* :element-type '(unsigned-byte 8))))
 518              (loop for i below *random-byte-count*
 519                 do (setf (aref random-bytes i)
 520                          (random #x100)))
 521              (prepare-query connection "bytea-insert" "insert into test values ($1)")
 522              (exec-prepared connection "bytea-insert" (list random-bytes))
 523              (is (equalp (exec-query connection "select row(ARRAY[a]) from test;" 'list-row-reader)
 524                          `(((#(,random-bytes)))))))
 525         (exec-query connection "drop table test")))))
 526
 527 (test write-row-array-bytea
 528   (with-test-connection
 529     (with-binary-row-values
 530       (exec-query connection "create table test (a bytea)")
 531       (unwind-protect
 532            (let ((random-bytes (make-array *random-byte-count* :element-type '(unsigned-byte 8))))
 533              (loop for i below *random-byte-count*
 534                 do (setf (aref random-bytes i)
 535                          (random #x100)))
 536              (prepare-query connection "bytea-insert" "insert into test values ($1)")
 537              (exec-prepared connection "bytea-insert" (list random-bytes))
 538              (is (equalp (exec-query connection "select row(ARRAY[a]) from test;" 'list-row-reader)
 539                          `(((#(,random-bytes)))))))
 540         (exec-query connection "drop table test")))))
 541
 542 (test write-row-array-bytea-binary
 543   (with-test-connection
 544     (with-binary-row-values
 545       (exec-query connection "create table test (a bytea)")
 546       (unwind-protect
 547            (let ((random-bytes (make-array *random-byte-count* :element-type '(unsigned-byte 8))))
 548              (loop for i below *random-byte-count*
 549                 do (setf (aref random-bytes i)
 550                          (random #x100)))
 551              (prepare-query connection "bytea-insert" "insert into test values ($1)")
 552              (exec-prepared connection "bytea-insert" (list random-bytes))
 553              (is (equalp (exec-query connection "select row(ARRAY[a]) from test;" 'list-row-reader)
 554                          `(((#(,random-bytes)))))))
 555         (exec-query connection "drop table test")))))
 556
 557 (test row-name-array
 558   (with-test-connection
 559     (is (equalp (exec-query connection "select row(ARRAY['foo'::name])" 'list-row-reader)
 560                 '(("({foo})"))))))
 561
 562 (test row-name-array-binary
 563   (with-test-connection
 564     (with-binary-row-values
 565       (is (equalp (exec-query connection "select row(ARRAY['foo'::name])" 'list-row-reader)
 566                   '(((#("foo")))))))))
 567
 568 (test point
 569   (with-test-connection
 570     (is (equalp (exec-query connection "select point(1,2)" 'list-row-reader)
 571                 '(((1.0d0 2.0d0)))))))
 572
 573 (test row-point
 574   (with-test-connection
 575     (is (equalp (exec-query connection "select row(point(1,2))" 'list-row-reader)
 576                 '(("(\"(1,2)\")"))))))
 577
 578 (test row-point-binary
 579   (with-test-connection
 580     (with-binary-row-values
 581       (is (equalp (exec-query connection "select row(point(1,2))" 'list-row-reader)
 582                   '((((1.0d0 2.0d0)))))))))
 583
 584 (test row-point-array
 585   (with-test-connection
 586     (is (equalp (exec-query connection "select row(ARRAY[point(1,2)])" 'list-row-reader)
 587                 '(("(\"{\"\"(1,2)\"\"}\")"))))))
 588
 589 (test row-point-array-binary
 590   (with-test-connection
 591     (with-binary-row-values
 592       (is (equalp (exec-query connection "select row(ARRAY[point(1,2)])" 'list-row-reader)
 593                   '(((#((1.0d0 2.0d0))))))))))
 594
 595 (test lseg
 596   (with-test-connection
 597     (is (equalp (exec-query connection "select lseg(point(1,2),point(3,4))" 'list-row-reader)
 598                 '((((1.0d0 2.0d0) (3.0d0 4.0d0))))))))
 599
 600 (test row-lseg
 601   (with-test-connection
 602     (is (equalp (exec-query connection "select row(lseg(point(1,2),point(3,4)))" 'list-row-reader)
 603                 '(("(\"[(1,2),(3,4)]\")"))))))
 604
 605 (test row-lseg-binary
 606   (with-test-connection
 607     (with-binary-row-values
 608       (is (equalp (exec-query connection "select row(lseg(point(1,2),point(3,4)))" 'list-row-reader)
 609                   '(((((1.0d0 2.0d0) (3.0d0 4.0d0))))))))))
 610
 611 (test row-lseg-array
 612   (with-test-connection
 613     (is (equalp (exec-query connection "select row(ARRAY[lseg(point(1,2),point(3,4))])" 'list-row-reader)
 614                 '(("(\"{\"\"[(1,2),(3,4)]\"\"}\")"))))))
 615
 616 (test row-lseg-array-binary
 617   (with-test-connection
 618     (with-binary-row-values
 619       (is (equalp (exec-query connection "select row(ARRAY[lseg(point(1,2),point(3,4))])" 'list-row-reader)
 620                   '(((#(((1.0d0 2.0d0) (3.0d0 4.0d0)))))))))))
 621
 622 (test box
 623   (with-test-connection
 624     (is (equalp (exec-query connection "select box(point(1,2),point(3,4))" 'list-row-reader)
 625                 '((((3.0d0 4.0d0) (1.0d0 2.0d0))))))))
 626
 627 (test row-box
 628   (with-test-connection
 629     (is (equalp (exec-query connection "select row(box(point(1,2),point(3,4)))" 'list-row-reader)
 630                 '(("(\"(3,4),(1,2)\")"))))))
 631
 632 (test row-box-binary
 633   (with-test-connection
 634     (with-binary-row-values
 635       (is (equalp (exec-query connection "select row(box(point(1,2),point(3,4)))" 'list-row-reader)
 636                   '(((((3.0d0 4.0d0) (1.0d0 2.0d0))))))))))
 637
 638 (test row-box-array
 639   (with-test-connection
 640     (is (equalp (exec-query connection "select row(ARRAY[box(point(1,2),point(3,4))])" 'list-row-reader)
 641                 '(("(\"{(3,4),(1,2)}\")"))))))
 642
 643 (test row-box-array-binary
 644   (with-test-connection
 645     (with-binary-row-values
 646       (is (equalp (exec-query connection "select row(ARRAY[box(point(1,2),point(3,4))])" 'list-row-reader)
 647                   '(((#(((3.0d0 4.0d0) (1.0d0 2.0d0)))))))))))
 648
 649 (test row-array-nulls
 650   (with-test-connection
 651     (is (equalp (exec-query connection "select row((ARRAY[1,3,4])[5:99])" 'list-row-reader)
 652                 '(("({})"))))))
 653
 654 (test row-array-nulls-binary
 655   (with-test-connection
 656     (cl-postgres::with-binary-row-values
 657       (is (equalp (exec-query connection "select row((ARRAY[1,3,4])[5:99])" 'list-row-reader)
 658                   '(((NIL))))))))
 659
 660 (test row-array-nulls-binary-2
 661   (with-test-connection
 662     (cl-postgres::with-binary-row-values
 663       (is (equalp (exec-query connection "select row(ARRAY[NULL, NULL]);" 'list-row-reader)
 664                   '(((#(:null :null)))))))))
 665
 666 (test row-array-table-nulls-binary
 667   (with-binary-row-values
 668     (with-test-connection
 669       (exec-query connection "create table test (a integer[])")
 670       (unwind-protect
 671            (progn
 672              (prepare-query connection "integer-array-insert" "insert into test values ($1)")
 673              (exec-prepared connection "integer-array-insert" (list "{{0,0},{0,0}}"))
 674              (exec-prepared connection "integer-array-insert" (list "{{1,1}}"))
 675              (exec-prepared connection "integer-array-insert" (list "{{2,2}, {2,2}}"))
 676              (exec-prepared connection "integer-array-insert" (list "{{3,3}}"))
 677              (exec-prepared connection "integer-array-insert" (list "{{4,4}}"))
 678              (is (equalp
 679                   (exec-query
 680                    connection
 681                    "select row(a[2:45]) from test"
 682                    'list-row-reader)
 683                   '(((#2A((0 0)))) ((NIL)) ((#2A((2 2)))) ((NIL)) ((NIL))))))
 684         (exec-query connection "drop table test")))))
 685
 686 (test array-row-text
 687   (with-test-connection
 688     (is (equalp (exec-query connection "select array_agg(row(1,2,3));" 'list-row-reader)
 689                 '(("{\"(1,2,3)\"}"))))))
 690
 691 (test array-row-binary
 692   (with-test-connection
 693     (cl-postgres::with-binary-row-values
 694       (is (equalp (exec-query connection "select array_agg(row(1,2,3));" 'list-row-reader)
 695                   '((#((1 2 3)))))))))