pintos-os.org Git - pspp/blob - tests/language/data-io/get-data-psql.at

   1 dnl PSPP - a program for statistical analysis.
   2 dnl Copyright (C) 2017 Free Software Foundation, Inc.
   3 dnl
   4 dnl This program is free software: you can redistribute it and/or modify
   5 dnl it under the terms of the GNU General Public License as published by
   6 dnl the Free Software Foundation, either version 3 of the License, or
   7 dnl (at your option) any later version.
   8 dnl
   9 dnl This program is distributed in the hope that it will be useful,
  10 dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 dnl GNU General Public License for more details.
  13 dnl
  14 dnl You should have received a copy of the GNU General Public License
  15 dnl along with this program.  If not, see <http://www.gnu.org/licenses/>.
  16 dnl
  17 AT_BANNER([GET DATA /TYPE=PSQL])
  18
  19 m4_define([INIT_PSQL],
  20   [AT_SKIP_IF([test "$PSQL_SUPPORT" = no])
  21    PGDATA=`pwd`/cluster
  22    export PGDATA
  23    PGPORT=$PG_PORT
  24    export PGPORT
  25    socket_dir=`mktemp -d`
  26    PGHOST="$socket_dir"
  27    export PGHOST
  28    AT_CHECK([PATH=$PG_PATH:$PATH initdb -A trust], [0], [ignore])
  29    AT_CHECK([PATH=$PG_PATH:$PATH pg_ctl start -w -o "-k $socket_dir -h ''"], [0], [ignore])
  30    trap 'CLEANUP_PSQL' 0
  31    AT_CHECK([PATH=$PG_PATH:$PATH createdb -h "$socket_dir" -p $PG_PORT $PG_DBASE],
  32       [0], [ignore], [ignore])
  33    AT_DATA([populate.sql],
  34      [CREATE TABLE empty (a int, b date, c numeric(23, 4));
  35
  36       -- a largeish table to check big queries work ok.
  37       CREATE TABLE large (x int);
  38       INSERT INTO large  (select * from generate_series(1, 1000));
  39
  40
  41       CREATE TABLE thing (
  42        bool    bool                      ,
  43        bytea   bytea                     ,
  44        char    char                      ,
  45        int8    int8                      ,
  46        int2    int2                      ,
  47        int4    int4                      ,
  48        numeric       numeric(50,6)       ,
  49        text    text                      ,
  50        oid     oid                       ,
  51        float4  float4                    ,
  52        float8  float8                    ,
  53        money   money                     ,
  54        pbchar  bpchar                    ,
  55        varchar varchar                   ,
  56        date    date                      ,
  57        time    time                      ,
  58        timestamp     timestamp           ,
  59        timestamptz   timestamptz         ,
  60        interval      interval            ,
  61        timetz        timetz
  62       );
  63
  64       INSERT INTO thing VALUES (
  65        false,
  66        '0',
  67        'a',
  68        '0',
  69        0,
  70        0,
  71        -256.098,
  72        'this-long-text',
  73        0,
  74        0,
  75        0,
  76        '0.01',
  77        'a',
  78        'A',
  79        '1-Jan-2000',
  80        '00:00',
  81        'January 8 04:05:06 1999',
  82        'January 8 04:05:06 1999 PST',
  83        '1 minutes',
  84        '10:09 UTC+4'
  85       );
  86
  87       INSERT INTO thing VALUES (
  88        null,
  89        null,
  90        null,
  91        null,
  92        null,
  93        null,
  94        null,
  95        null,
  96        null,
  97        null,
  98        null,
  99        null,
 100        null,
 101        null,
 102        null,
 103        null,
 104        null,
 105        null,
 106        null,
 107        null
 108       );
 109
 110       INSERT INTO thing VALUES (
 111        true,
 112        '1',
 113        'b',
 114        '1',
 115        1,
 116        1,
 117        65535.00001,
 118        'that-long-text',
 119        1,
 120        1,
 121        1,
 122        '1.23',
 123        'b',
 124        'B',
 125        '10-Jan-1963',
 126        '01:05:02',
 127        '10-Jan-1963 23:58:00',
 128        '10-Jan-1963 23:58:00 CET',
 129        '2 year 1 month 12 days 1 hours 3 minutes 4 seconds',
 130        '01:05:02 UTC-7'
 131       );
 132 ])
 133
 134    # On Debian, the psql binary in the postgres bindir won't work because
 135    # it needs libreadline to be LD_PRELOADed into it.  The psql in the
 136    # normal $PATH works fine though.
 137    if (PATH=$PG_PATH:$PATH psql -V) >/dev/null 2>&1; then
 138        psql () {
 139            PATH=$PG_PATH:$PATH command psql "$@"
 140        }
 141    fi
 142    AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < populate.sql],
 143       [0], [ignore])])
 144
 145 m4_define([CLEANUP_PSQL], [PATH=$PG_PATH:$PATH pg_ctl stop -W -o "-k $socket_dir -h ''"])
 146
 147 AT_SETUP([GET DATA /TYPE=PSQL])
 148 INIT_PSQL
 149
 150 dnl Test with an ordinary query.
 151 AT_CHECK([cat > ordinary-query.sps <<EOF
 152 GET DATA /TYPE=psql
 153         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 154         /UNENCRYPTED
 155         /SQL="select * from thing".
 156
 157 DISPLAY DICTIONARY.
 158
 159 LIST.
 160 EOF
 161 ])
 162 AT_CHECK([pspp -o pspp.csv ordinary-query.sps])
 163 AT_CHECK([cat pspp.csv], [0], [dnl
 164 Table: Variables
 165 Name,Position,Label,Measurement Level,Role,Width,Alignment,Print Format,Write Format,Missing Values
 166 bool,1,,Scale,Input,8,Right,F8.2,F8.2,
 167 bytea,2,,Nominal,Input,1,Left,AHEX2,AHEX2,
 168 char,3,,Nominal,Input,8,Left,A8,A8,
 169 int8,4,,Scale,Input,8,Right,F8.2,F8.2,
 170 int2,5,,Scale,Input,8,Right,F8.2,F8.2,
 171 int4,6,,Scale,Input,8,Right,F8.2,F8.2,
 172 numeric,7,,Scale,Input,8,Right,E40.6,E40.6,
 173 text,8,,Nominal,Input,16,Left,A16,A16,
 174 oid,9,,Scale,Input,8,Right,F8.2,F8.2,
 175 float4,10,,Scale,Input,8,Right,F8.2,F8.2,
 176 float8,11,,Scale,Input,8,Right,F8.2,F8.2,
 177 money,12,,Scale,Input,8,Right,DOLLAR8.2,DOLLAR8.2,
 178 pbchar,13,,Nominal,Input,8,Left,A8,A8,
 179 varchar,14,,Nominal,Input,8,Left,A8,A8,
 180 date,15,,Scale,Input,8,Right,DATE11,DATE11,
 181 time,16,,Scale,Input,8,Right,TIME11.0,TIME11.0,
 182 timestamp,17,,Scale,Input,8,Right,DATETIME22.0,DATETIME22.0,
 183 timestamptz,18,,Scale,Input,8,Right,DATETIME22.0,DATETIME22.0,
 184 interval,19,,Scale,Input,8,Right,DTIME13.0,DTIME13.0,
 185 interval_months,20,,Scale,Input,8,Right,F3.0,F3.0,
 186 timetz,21,,Scale,Input,8,Right,TIME11.0,TIME11.0,
 187 timetz_zone,22,,Scale,Input,8,Right,F8.2,F8.2,
 188
 189 Table: Data List
 190 bool,bytea,char,int8,int2,int4,numeric,text,oid,float4,float8,money,pbchar,varchar,date,time,timestamp,timestamptz,interval,interval_months,timetz,timetz_zone
 191 .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
 192 .  ,20,,.  ,.  ,.  ,.          ,,.  ,.  ,.  ,.  ,,,.,.,.,.,.,.,.,.  @&t@
 193 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
 194 ])
 195
 196 dnl Test query with empty result set.
 197 AT_CHECK([cat > empty-result.sps <<EOF
 198 GET DATA /TYPE=psql
 199         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 200         /UNENCRYPTED
 201         /SQL="select * from empty".
 202
 203 DISPLAY DICTIONARY.
 204
 205 LIST.
 206 EOF
 207 ])
 208 AT_CHECK([pspp -o pspp.csv empty-result.sps])
 209 AT_CHECK([cat pspp.csv], [0], [dnl
 210 Table: Variables
 211 Name,Position,Label,Measurement Level,Role,Width,Alignment,Print Format,Write Format,Missing Values
 212 a,1,,Scale,Input,8,Right,F8.2,F8.2,
 213 b,2,,Scale,Input,8,Right,DATE11,DATE11,
 214 c,3,,Scale,Input,8,Right,E40.2,E40.2,
 215 ])
 216
 217 dnl Test query with large result set.
 218 AT_CHECK([cat > large-result.sps <<EOF
 219 GET DATA /TYPE=psql
 220         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 221         /UNENCRYPTED
 222         /SQL="select * from large".
 223
 224 NUMERIC diff.
 225 COMPUTE diff = x - lag (x).
 226
 227 TEMPORARY.
 228 SELECT IF (diff <> 1).
 229 LIST.
 230
 231 TEMPORARY.
 232 N OF CASES 6.
 233 LIST.
 234
 235 SORT CASES BY x (D).
 236
 237 TEMPORARY.
 238 N OF CASES 6.
 239 LIST.
 240 EOF
 241 ])
 242 AT_CHECK([pspp -o pspp.csv large-result.sps])
 243 AT_CHECK([cat pspp.csv], [0], [dnl
 244 Table: Data List
 245 x,diff
 246 1.00,.  @&t@
 247 2.00,1.00
 248 3.00,1.00
 249 4.00,1.00
 250 5.00,1.00
 251 6.00,1.00
 252
 253 Table: Data List
 254 x,diff
 255 1000.00,1.00
 256 999.00,1.00
 257 998.00,1.00
 258 997.00,1.00
 259 996.00,1.00
 260 995.00,1.00
 261 ])
 262
 263 dnl Check for a bug caused by having string variables in the database,
 264 dnl all of which are null.
 265 AT_DATA([all-null-string.sql],
 266   [-- a table which has a text field containing only null, or zero
 267    -- length entries.
 268
 269    CREATE TABLE foo (int4  int4, text text);
 270
 271    INSERT INTO foo VALUES ('12', '');
 272
 273    INSERT INTO foo VALUES (null, '');
 274 ])
 275 AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < all-null-string.sql],
 276   [0], [ignore])
 277 AT_CAPTURE_FILE([get-data.sps])
 278 AT_CHECK([cat > get-data.sps <<EOF
 279 GET DATA /TYPE=psql
 280         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 281         /UNENCRYPTED
 282         /SQL="select * from foo".
 283
 284 DISPLAY DICTIONARY.
 285
 286 LIST.
 287 EOF
 288 ])
 289 AT_CHECK([pspp -o pspp.csv get-data.sps])
 290 AT_CAPTURE_FILE([pspp.csv])
 291 rm -rf "$socket_dir"
 292 AT_CLEANUP