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