tests/command/get-data-psql.sh

   1 #!/bin/sh
   2
   3 # This program tests the psql import feature.
   4
   5 TEMPDIR=/tmp/pspp-tst-$$
   6 TESTFILE=$TEMPDIR/`basename $0`.sps
   7
   8 # ensure that top_srcdir and top_builddir  are absolute
   9 if [ -z "$top_srcdir" ] ; then top_srcdir=. ; fi
  10 if [ -z "$top_builddir" ] ; then top_builddir=. ; fi
  11 top_srcdir=`cd $top_srcdir; pwd`
  12 top_builddir=`cd $top_builddir; pwd`
  13
  14 PSPP=$top_builddir/src/ui/terminal/pspp
  15
  16 STAT_CONFIG_PATH=$top_srcdir/config
  17 export STAT_CONFIG_PATH
  18
  19 LANG=C
  20 export LANG
  21
  22 port=6543
  23 dbase=pspptest
  24 PG_CONFIG=${PG_CONFIG:=pg_config}
  25 pgpath=`$PG_CONFIG --bindir`
  26
  27 cleanup()
  28 {
  29     if [ x"$PSPP_TEST_NO_CLEANUP" != x ] ; then
  30         echo "NOT cleaning $TEMPDIR"
  31         return ;
  32     fi
  33     PGHOST=$TEMPDIR $pgpath/pg_ctl -D $TEMPDIR/cluster  stop -W -o "-k $TEMPDIR -h ''"   > /dev/null 2>&1
  34     cd /
  35     rm -rf $TEMPDIR
  36 }
  37
  38
  39 fail()
  40 {
  41     echo $activity
  42     echo FAILED
  43     cleanup;
  44     exit 1;
  45 }
  46
  47
  48 no_result()
  49 {
  50     echo $activity
  51     echo NO RESULT;
  52     cleanup;
  53     exit 2;
  54 }
  55
  56 pass()
  57 {
  58     cleanup;
  59     exit 0;
  60 }
  61
  62 if [ ! -x $pgpath/initdb ] ; then
  63   echo 'No Postgres server was found, so the postgres database interface cannot be tested.'
  64   cleanup;
  65   exit 77;
  66 fi
  67
  68 mkdir -p $TEMPDIR
  69
  70 cd $TEMPDIR
  71
  72 activity="create cluster"
  73 $pgpath/initdb  -D $TEMPDIR/cluster -A trust > /dev/null
  74 if [ $? -ne 0 ] ; then no_result ; fi
  75
  76 activity="run server"
  77 PGHOST=$TEMPDIR PGPORT=$port $pgpath/pg_ctl -D $TEMPDIR/cluster  start -w -o "-k $TEMPDIR -h ''" > /dev/null
  78 if [ $? -ne 0 ] ; then no_result ; fi
  79
  80
  81 activity="create database"
  82 $pgpath/createdb  -h $TEMPDIR  -p $port $dbase > /dev/null 2> /dev/null
  83 if [ $? -ne 0 ] ; then no_result ; fi
  84
  85
  86 activity="populate database"
  87 $pgpath/psql  -h $TEMPDIR -p $port  $dbase > /dev/null << EOF
  88
  89 CREATE TABLE empty (a int, b date, c numeric(23, 4));
  90
  91 -- a largeish table to check big queries work ok.
  92 CREATE TABLE large (x int);
  93 INSERT INTO large  (select * from generate_series(1, 1000));
  94
  95
  96 CREATE TABLE thing (
  97  bool    bool                      ,
  98  bytea   bytea                     ,
  99  char    char                      ,
 100  int8    int8                      ,
 101  int2    int2                      ,
 102  int4    int4                      ,
 103  numeric       numeric(50,6)       ,
 104  text    text                      ,
 105  oid     oid                       ,
 106  float4  float4                    ,
 107  float8  float8                    ,
 108  money   money                     ,
 109  pbchar  bpchar                    ,
 110  varchar varchar                   ,
 111  date    date                      ,
 112  time    time                      ,
 113  timestamp     timestamp           ,
 114  timestamptz   timestamptz         ,
 115  interval      interval            ,
 116  timetz        timetz
 117 );
 118
 119 INSERT INTO thing VALUES (
 120  false,
 121  '0',
 122  'a',
 123  '0',
 124  0,
 125  0,
 126  -256.098,
 127  'this-long-text',
 128  0,
 129  0,
 130  0,
 131  '0.01',
 132  'a',
 133  'A',
 134  '1-Jan-2000',
 135  '00:00',
 136  'January 8 04:05:06 1999',
 137  'January 8 04:05:06 1999 PST',
 138  '1 minutes',
 139  '10:09 UTC+4'
 140 );
 141
 142 INSERT INTO thing VALUES (
 143  null,
 144  null,
 145  null,
 146  null,
 147  null,
 148  null,
 149  null,
 150  null,
 151  null,
 152  null,
 153  null,
 154  null,
 155  null,
 156  null,
 157  null,
 158  null,
 159  null,
 160  null,
 161  null,
 162  null
 163 );
 164
 165 INSERT INTO thing VALUES (
 166  true,
 167  '1',
 168  'b',
 169  '1',
 170  1,
 171  1,
 172  65535.00001,
 173  'that-long-text',
 174  1,
 175  1,
 176  1,
 177  '1.23',
 178  'b',
 179  'B',
 180  '10-Jan-1963',
 181  '01:05:02',
 182  '10-Jan-1963 23:58:00',
 183  '10-Jan-1963 23:58:00 CET',
 184  '2 year 1 month 12 days 1 hours 3 minutes 4 seconds',
 185  '01:05:02 UTC-7'
 186 );
 187 EOF
 188 if [ $? -ne 0 ] ; then fail ; fi
 189
 190 activity="create program 1"
 191 cat > $TESTFILE <<EOF
 192 GET DATA /TYPE=psql
 193         /CONNECT="host=$TEMPDIR port=$port dbname=$dbase"
 194         /UNENCRYPTED
 195         /SQL="select * from thing".
 196
 197 DISPLAY DICTIONARY.
 198
 199 LIST.
 200 EOF
 201 if [ $? -ne 0 ] ; then no_result ; fi
 202
 203
 204 activity="run program 1"
 205 $SUPERVISOR $PSPP --testing-mode -o raw-ascii $TESTFILE
 206 if [ $? -ne 0 ] ; then no_result ; fi
 207
 208 activity="compare output 1"
 209 perl -pi -e 's/^\s*$//g' $TEMPDIR/pspp.list
 210 diff -b  $TEMPDIR/pspp.list - << 'EOF'
 211 1.1 DISPLAY.
 212 +---------------+-------------------------------------------+--------+
 213 |Variable       |Description                                |Position|
 214 #===============#===========================================#========#
 215 |bool           |Format: F8.2                               |       1|
 216 |               |Measure: Scale                             |        |
 217 |               |Display Alignment: Right                   |        |
 218 |               |Display Width: 8                           |        |
 219 +---------------+-------------------------------------------+--------+
 220 |bytea          |Format: AHEX2                              |       2|
 221 |               |Measure: Nominal                           |        |
 222 |               |Display Alignment: Left                    |        |
 223 |               |Display Width: 1                           |        |
 224 +---------------+-------------------------------------------+--------+
 225 |char           |Format: A8                                 |       3|
 226 |               |Measure: Nominal                           |        |
 227 |               |Display Alignment: Left                    |        |
 228 |               |Display Width: 8                           |        |
 229 +---------------+-------------------------------------------+--------+
 230 |int8           |Format: F8.2                               |       4|
 231 |               |Measure: Scale                             |        |
 232 |               |Display Alignment: Right                   |        |
 233 |               |Display Width: 8                           |        |
 234 +---------------+-------------------------------------------+--------+
 235 |int2           |Format: F8.2                               |       5|
 236 |               |Measure: Scale                             |        |
 237 |               |Display Alignment: Right                   |        |
 238 |               |Display Width: 8                           |        |
 239 +---------------+-------------------------------------------+--------+
 240 |int4           |Format: F8.2                               |       6|
 241 |               |Measure: Scale                             |        |
 242 |               |Display Alignment: Right                   |        |
 243 |               |Display Width: 8                           |        |
 244 +---------------+-------------------------------------------+--------+
 245 |numeric        |Format: E40.6                              |       7|
 246 |               |Measure: Scale                             |        |
 247 |               |Display Alignment: Right                   |        |
 248 |               |Display Width: 8                           |        |
 249 +---------------+-------------------------------------------+--------+
 250 |text           |Format: A16                                |       8|
 251 |               |Measure: Nominal                           |        |
 252 |               |Display Alignment: Left                    |        |
 253 |               |Display Width: 16                          |        |
 254 +---------------+-------------------------------------------+--------+
 255 |oid            |Format: F8.2                               |       9|
 256 |               |Measure: Scale                             |        |
 257 |               |Display Alignment: Right                   |        |
 258 |               |Display Width: 8                           |        |
 259 +---------------+-------------------------------------------+--------+
 260 |float4         |Format: F8.2                               |      10|
 261 |               |Measure: Scale                             |        |
 262 |               |Display Alignment: Right                   |        |
 263 |               |Display Width: 8                           |        |
 264 +---------------+-------------------------------------------+--------+
 265 |float8         |Format: F8.2                               |      11|
 266 |               |Measure: Scale                             |        |
 267 |               |Display Alignment: Right                   |        |
 268 |               |Display Width: 8                           |        |
 269 +---------------+-------------------------------------------+--------+
 270 |money          |Format: DOLLAR8.2                          |      12|
 271 |               |Measure: Scale                             |        |
 272 |               |Display Alignment: Right                   |        |
 273 |               |Display Width: 8                           |        |
 274 +---------------+-------------------------------------------+--------+
 275 |pbchar         |Format: A8                                 |      13|
 276 |               |Measure: Nominal                           |        |
 277 |               |Display Alignment: Left                    |        |
 278 |               |Display Width: 8                           |        |
 279 +---------------+-------------------------------------------+--------+
 280 |varchar        |Format: A8                                 |      14|
 281 |               |Measure: Nominal                           |        |
 282 |               |Display Alignment: Left                    |        |
 283 |               |Display Width: 8                           |        |
 284 +---------------+-------------------------------------------+--------+
 285 |date           |Format: DATE11                             |      15|
 286 |               |Measure: Scale                             |        |
 287 |               |Display Alignment: Right                   |        |
 288 |               |Display Width: 8                           |        |
 289 +---------------+-------------------------------------------+--------+
 290 |time           |Format: TIME11.0                           |      16|
 291 |               |Measure: Scale                             |        |
 292 |               |Display Alignment: Right                   |        |
 293 |               |Display Width: 8                           |        |
 294 +---------------+-------------------------------------------+--------+
 295 |timestamp      |Format: DATETIME22.0                       |      17|
 296 |               |Measure: Scale                             |        |
 297 |               |Display Alignment: Right                   |        |
 298 |               |Display Width: 8                           |        |
 299 +---------------+-------------------------------------------+--------+
 300 |timestamptz    |Format: DATETIME22.0                       |      18|
 301 |               |Measure: Scale                             |        |
 302 |               |Display Alignment: Right                   |        |
 303 |               |Display Width: 8                           |        |
 304 +---------------+-------------------------------------------+--------+
 305 |interval       |Format: DTIME13.0                          |      19|
 306 |               |Measure: Scale                             |        |
 307 |               |Display Alignment: Right                   |        |
 308 |               |Display Width: 8                           |        |
 309 +---------------+-------------------------------------------+--------+
 310 |interval_months|Format: F3.0                               |      20|
 311 |               |Measure: Scale                             |        |
 312 |               |Display Alignment: Right                   |        |
 313 |               |Display Width: 8                           |        |
 314 +---------------+-------------------------------------------+--------+
 315 |timetz         |Format: TIME11.0                           |      21|
 316 |               |Measure: Scale                             |        |
 317 |               |Display Alignment: Right                   |        |
 318 |               |Display Width: 8                           |        |
 319 +---------------+-------------------------------------------+--------+
 320 |timetz_zone    |Format: F8.2                               |      22|
 321 |               |Measure: Scale                             |        |
 322 |               |Display Alignment: Right                   |        |
 323 |               |Display Width: 8                           |        |
 324 +---------------+-------------------------------------------+--------+
 325     bool bytea     char     int8     int2     int4                                  numeric             text      oid   float4   float8    money   pbchar  varchar        date        time              timestamp            timestamptz      interval interval_months      timetz timetz_zone
 326 -------- ----- -------- -------- -------- -------- ---------------------------------------- ---------------- -------- -------- -------- -------- -------- -------- ----------- ----------- ---------------------- ---------------------- ------------- --------------- ----------- -----------
 327      .00    30 a             .00      .00      .00                           -2.560980E+002 this-long-text        .00      .00      .00     $.01 a        A        01-JAN-2000     0:00:00   08-JAN-1999 04:05:06   08-JAN-1999 12:05:06    0 00:01:00               0    10:09:00        4.00
 328      .      20               .        .        .                                .                                 .        .        .        .                               .           .                      .                      .             .               .           .         .
 329     1.00    31 b            1.00     1.00     1.00                            6.553500E+004 that-long-text        .00     1.00     1.00    $1.23 b        B        10-JAN-1963     1:05:02   10-JAN-1963 23:58:00   10-JAN-1963 22:58:00   12 01:03:04              25     1:05:02       -7.00
 330 EOF
 331 if [ $? -ne 0 ] ; then fail ; fi
 332
 333
 334 activity="create program 2"
 335 cat > $TESTFILE <<EOF
 336 GET DATA /TYPE=psql
 337         /CONNECT="host=$TEMPDIR port=$port dbname=$dbase"
 338         /UNENCRYPTED
 339         /SQL="select * from empty".
 340
 341 DISPLAY DICTIONARY.
 342
 343 LIST.
 344 EOF
 345 if [ $? -ne 0 ] ; then no_result ; fi
 346
 347 activity="run program 2"
 348 $SUPERVISOR $PSPP --testing-mode -o raw-ascii $TESTFILE
 349 if [ $? -ne 0 ] ; then no_result ; fi
 350
 351 activity="compare output 2"
 352 perl -pi -e 's/^\s*$//g' $TEMPDIR/pspp.list
 353 diff -b  $TEMPDIR/pspp.list - << 'EOF'
 354 1.1 DISPLAY.
 355 +--------+-------------------------------------------+--------+
 356 |Variable|Description                                |Position|
 357 #========#===========================================#========#
 358 |a       |Format: F8.2                               |       1|
 359 |        |Measure: Scale                             |        |
 360 |        |Display Alignment: Right                   |        |
 361 |        |Display Width: 8                           |        |
 362 +--------+-------------------------------------------+--------+
 363 |b       |Format: DATE11                             |       2|
 364 |        |Measure: Scale                             |        |
 365 |        |Display Alignment: Right                   |        |
 366 |        |Display Width: 8                           |        |
 367 +--------+-------------------------------------------+--------+
 368 |c       |Format: E40.2                              |       3|
 369 |        |Measure: Scale                             |        |
 370 |        |Display Alignment: Right                   |        |
 371 |        |Display Width: 8                           |        |
 372 +--------+-------------------------------------------+--------+
 373 EOF
 374 if [ $? -ne 0 ] ; then fail ; fi
 375
 376 activity="create program 3"
 377 cat > $TESTFILE <<EOF
 378 GET DATA /TYPE=psql
 379         /CONNECT="host=$TEMPDIR port=$port dbname=$dbase"
 380         /UNENCRYPTED
 381         /BSIZE = 27
 382         /SQL="select * from large".
 383
 384 NUMERIC diff.
 385 COMPUTE diff = x - lag (x).
 386
 387 TEMPORARY.
 388 SELECT IF (diff <> 1).
 389 LIST.
 390
 391 TEMPORARY.
 392 N OF CASES 6.
 393 LIST.
 394
 395 SORT CASES BY x (D).
 396
 397 TEMPORARY.
 398 N OF CASES 6.
 399 LIST.
 400
 401 EOF
 402 if [ $? -ne 0 ] ; then no_result ; fi
 403
 404 activity="run program 3"
 405 $SUPERVISOR $PSPP --testing-mode -o raw-ascii $TESTFILE
 406 if [ $? -ne 0 ] ; then no_result ; fi
 407
 408 activity="compare output 3"
 409 perl -pi -e 's/^\s*$//g' $TEMPDIR/pspp.list
 410 diff -b  $TEMPDIR/pspp.list - << 'EOF'
 411        x     diff
 412 -------- --------
 413     1.00      .
 414     2.00     1.00
 415     3.00     1.00
 416     4.00     1.00
 417     5.00     1.00
 418     6.00     1.00
 419        x     diff
 420 -------- --------
 421  1000.00     1.00
 422   999.00     1.00
 423   998.00     1.00
 424   997.00     1.00
 425   996.00     1.00
 426   995.00     1.00
 427 EOF
 428 if [ $? -ne 0 ] ; then fail ; fi
 429
 430
 431
 432 # Check for a bug caused by having string variables in the database,
 433 # all of which are null.
 434
 435 activity="populate database 4"
 436 $pgpath/psql  -h $TEMPDIR -p $port  $dbase > /dev/null << EOF
 437
 438 -- a table which has a text field containing only null, or zero
 439 -- length entries.
 440
 441 CREATE TABLE foo (int4  int4, text text);
 442
 443 INSERT INTO foo VALUES ('12', '');
 444
 445 INSERT INTO foo VALUES (null, '');
 446
 447 EOF
 448 if [ $? -ne 0 ] ; then fail ; fi
 449
 450
 451 activity="create program 4"
 452 cat > $TESTFILE <<EOF
 453 GET DATA /TYPE=psql
 454         /CONNECT="host=$TEMPDIR port=$port dbname=$dbase"
 455         /UNENCRYPTED
 456         /SQL="select * from foo".
 457
 458 DISPLAY DICTIONARY.
 459
 460 LIST.
 461 EOF
 462 if [ $? -ne 0 ] ; then no_result ; fi
 463
 464
 465 activity="run program 4"
 466 $SUPERVISOR $PSPP --testing-mode -o raw-ascii $TESTFILE
 467 if [ $? -ne 0 ] ; then no_result ; fi
 468
 469
 470 pass;