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