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 AT_KEYWORDS([slow])
 149 INIT_PSQL
 150
 151 dnl Test with an ordinary query.
 152 AT_CHECK([cat > ordinary-query.sps <<EOF
 153 GET DATA /TYPE=psql
 154         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 155         /UNENCRYPTED
 156         /SQL="select * from thing".
 157
 158 DISPLAY DICTIONARY.
 159
 160 LIST.
 161 EOF
 162 ])
 163 AT_CHECK([pspp -o pspp.csv ordinary-query.sps])
 164 AT_CHECK([cat pspp.csv], [0], [dnl
 165 Table: Variables
 166 Name,Position,Label,Measurement Level,Role,Width,Alignment,Print Format,Write Format,Missing Values
 167 bool,1,,Scale,Input,8,Right,F8.2,F8.2,
 168 bytea,2,,Nominal,Input,1,Left,AHEX2,AHEX2,
 169 char,3,,Nominal,Input,8,Left,A8,A8,
 170 int8,4,,Scale,Input,8,Right,F8.2,F8.2,
 171 int2,5,,Scale,Input,8,Right,F8.2,F8.2,
 172 int4,6,,Scale,Input,8,Right,F8.2,F8.2,
 173 numeric,7,,Scale,Input,8,Right,E40.6,E40.6,
 174 text,8,,Nominal,Input,16,Left,A16,A16,
 175 oid,9,,Scale,Input,8,Right,F8.2,F8.2,
 176 float4,10,,Scale,Input,8,Right,F8.2,F8.2,
 177 float8,11,,Scale,Input,8,Right,F8.2,F8.2,
 178 money,12,,Scale,Input,8,Right,DOLLAR8.2,DOLLAR8.2,
 179 pbchar,13,,Nominal,Input,8,Left,A8,A8,
 180 varchar,14,,Nominal,Input,8,Left,A8,A8,
 181 date,15,,Scale,Input,8,Right,DATE11,DATE11,
 182 time,16,,Scale,Input,8,Right,TIME11.0,TIME11.0,
 183 timestamp,17,,Scale,Input,8,Right,DATETIME22.0,DATETIME22.0,
 184 timestamptz,18,,Scale,Input,8,Right,DATETIME22.0,DATETIME22.0,
 185 interval,19,,Scale,Input,8,Right,DTIME13.0,DTIME13.0,
 186 interval_months,20,,Scale,Input,8,Right,F3.0,F3.0,
 187 timetz,21,,Scale,Input,8,Right,TIME11.0,TIME11.0,
 188 timetz_zone,22,,Scale,Input,8,Right,F8.2,F8.2,
 189
 190 Table: Data List
 191 bool,bytea,char,int8,int2,int4,numeric,text,oid,float4,float8,money,pbchar,varchar,date,time,timestamp,timestamptz,interval,interval_months,timetz,timetz_zone
 192 .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
 193 .  ,20,,.  ,.  ,.  ,.          ,,.  ,.  ,.  ,.  ,,,.,.,.,.,.,.,.,.  @&t@
 194 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
 195 ])
 196
 197 dnl Test query with empty result set.
 198 AT_CHECK([cat > empty-result.sps <<EOF
 199 GET DATA /TYPE=psql
 200         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 201         /UNENCRYPTED
 202         /SQL="select * from empty".
 203
 204 DISPLAY DICTIONARY.
 205
 206 LIST.
 207 EOF
 208 ])
 209 AT_CHECK([pspp -o pspp.csv empty-result.sps])
 210 AT_CHECK([cat pspp.csv], [0], [dnl
 211 Table: Variables
 212 Name,Position,Label,Measurement Level,Role,Width,Alignment,Print Format,Write Format,Missing Values
 213 a,1,,Scale,Input,8,Right,F8.2,F8.2,
 214 b,2,,Scale,Input,8,Right,DATE11,DATE11,
 215 c,3,,Scale,Input,8,Right,E40.2,E40.2,
 216 ])
 217
 218 dnl Test query with large result set.
 219 AT_CHECK([cat > large-result.sps <<EOF
 220 GET DATA /TYPE=psql
 221         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 222         /UNENCRYPTED
 223         /SQL="select * from large".
 224
 225 NUMERIC diff.
 226 COMPUTE diff = x - lag (x).
 227
 228 TEMPORARY.
 229 SELECT IF (diff <> 1).
 230 LIST.
 231
 232 TEMPORARY.
 233 N OF CASES 6.
 234 LIST.
 235
 236 SORT CASES BY x (D).
 237
 238 TEMPORARY.
 239 N OF CASES 6.
 240 LIST.
 241 EOF
 242 ])
 243 AT_CHECK([pspp -o pspp.csv large-result.sps])
 244 AT_CHECK([cat pspp.csv], [0], [dnl
 245 Table: Data List
 246 x,diff
 247 1.00,.  @&t@
 248 2.00,1.00
 249 3.00,1.00
 250 4.00,1.00
 251 5.00,1.00
 252 6.00,1.00
 253
 254 Table: Data List
 255 x,diff
 256 1000.00,1.00
 257 999.00,1.00
 258 998.00,1.00
 259 997.00,1.00
 260 996.00,1.00
 261 995.00,1.00
 262 ])
 263
 264 dnl Check for a bug caused by having string variables in the database,
 265 dnl all of which are null.
 266 AT_DATA([all-null-string.sql],
 267   [-- a table which has a text field containing only null, or zero
 268    -- length entries.
 269
 270    CREATE TABLE foo (int4  int4, text text);
 271
 272    INSERT INTO foo VALUES ('12', '');
 273
 274    INSERT INTO foo VALUES (null, '');
 275 ])
 276 AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < all-null-string.sql],
 277   [0], [ignore])
 278 AT_CAPTURE_FILE([get-data.sps])
 279 AT_CHECK([cat > get-data.sps <<EOF
 280 GET DATA /TYPE=psql
 281         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 282         /UNENCRYPTED
 283         /SQL="select * from foo".
 284
 285 DISPLAY DICTIONARY.
 286
 287 LIST.
 288 EOF
 289 ])
 290 AT_CHECK([pspp -o pspp.csv get-data.sps])
 291 AT_CAPTURE_FILE([pspp.csv])
 292 rm -rf "$socket_dir"
 293 AT_CLEANUP