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

   1 AT_BANNER([GET DATA /TYPE=PSQL])
   2
   3 m4_define([INIT_PSQL],
   4   [AT_SKIP_IF([test "$PSQL_SUPPORT" = no])
   5    PATH=$PG_PATH:$PATH
   6    export PATH
   7    PGDATA=`pwd`/cluster
   8    export PGDATA
   9    PGPORT=$PG_PORT
  10    export PGPORT
  11    socket_dir=`mktemp -d`
  12    PGHOST="$socket_dir"
  13    export PGHOST
  14    AT_CHECK([initdb -A trust], [0], [ignore])
  15    AT_CHECK([pg_ctl start -w -o "-k $socket_dir -h ''"], [0], [ignore])
  16    trap 'CLEANUP_PSQL' 0
  17    AT_CHECK([createdb -h "$socket_dir" -p $PG_PORT $PG_DBASE],
  18       [0], [ignore], [ignore])
  19    AT_DATA([populate.sql],
  20      [CREATE TABLE empty (a int, b date, c numeric(23, 4));
  21
  22       -- a largeish table to check big queries work ok.
  23       CREATE TABLE large (x int);
  24       INSERT INTO large  (select * from generate_series(1, 1000));
  25
  26
  27       CREATE TABLE thing (
  28        bool    bool                      ,
  29        bytea   bytea                     ,
  30        char    char                      ,
  31        int8    int8                      ,
  32        int2    int2                      ,
  33        int4    int4                      ,
  34        numeric       numeric(50,6)       ,
  35        text    text                      ,
  36        oid     oid                       ,
  37        float4  float4                    ,
  38        float8  float8                    ,
  39        money   money                     ,
  40        pbchar  bpchar                    ,
  41        varchar varchar                   ,
  42        date    date                      ,
  43        time    time                      ,
  44        timestamp     timestamp           ,
  45        timestamptz   timestamptz         ,
  46        interval      interval            ,
  47        timetz        timetz
  48       );
  49
  50       INSERT INTO thing VALUES (
  51        false,
  52        '0',
  53        'a',
  54        '0',
  55        0,
  56        0,
  57        -256.098,
  58        'this-long-text',
  59        0,
  60        0,
  61        0,
  62        '0.01',
  63        'a',
  64        'A',
  65        '1-Jan-2000',
  66        '00:00',
  67        'January 8 04:05:06 1999',
  68        'January 8 04:05:06 1999 PST',
  69        '1 minutes',
  70        '10:09 UTC+4'
  71       );
  72
  73       INSERT INTO thing VALUES (
  74        null,
  75        null,
  76        null,
  77        null,
  78        null,
  79        null,
  80        null,
  81        null,
  82        null,
  83        null,
  84        null,
  85        null,
  86        null,
  87        null,
  88        null,
  89        null,
  90        null,
  91        null,
  92        null,
  93        null
  94       );
  95
  96       INSERT INTO thing VALUES (
  97        true,
  98        '1',
  99        'b',
 100        '1',
 101        1,
 102        1,
 103        65535.00001,
 104        'that-long-text',
 105        1,
 106        1,
 107        1,
 108        '1.23',
 109        'b',
 110        'B',
 111        '10-Jan-1963',
 112        '01:05:02',
 113        '10-Jan-1963 23:58:00',
 114        '10-Jan-1963 23:58:00 CET',
 115        '2 year 1 month 12 days 1 hours 3 minutes 4 seconds',
 116        '01:05:02 UTC-7'
 117       );
 118 ])
 119    AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < populate.sql],
 120       [0], [ignore])])
 121
 122 m4_define([CLEANUP_PSQL], [pg_ctl stop -W -o "-k $socket_dir -h ''"])
 123
 124 AT_SETUP([GET DATA /TYPE=PSQL])
 125 INIT_PSQL
 126
 127 dnl Test with an ordinary query.
 128 AT_CHECK([cat > ordinary-query.sps <<EOF
 129 GET DATA /TYPE=psql
 130         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 131         /UNENCRYPTED
 132         /SQL="select * from thing".
 133
 134 DISPLAY DICTIONARY.
 135
 136 LIST.
 137 EOF
 138 ])
 139 AT_CHECK([pspp -o pspp.csv ordinary-query.sps])
 140 AT_CHECK([cat pspp.csv], [0], [dnl
 141 Variable,Description,Position
 142 bool,Format: F8.2,1
 143 bytea,Format: AHEX2,2
 144 char,Format: A8,3
 145 int8,Format: F8.2,4
 146 int2,Format: F8.2,5
 147 int4,Format: F8.2,6
 148 numeric,Format: E40.6,7
 149 text,Format: A16,8
 150 oid,Format: F8.2,9
 151 float4,Format: F8.2,10
 152 float8,Format: F8.2,11
 153 money,Format: DOLLAR8.2,12
 154 pbchar,Format: A8,13
 155 varchar,Format: A8,14
 156 date,Format: DATE11,15
 157 time,Format: TIME11.0,16
 158 timestamp,Format: DATETIME22.0,17
 159 timestamptz,Format: DATETIME22.0,18
 160 interval,Format: DTIME13.0,19
 161 interval_months,Format: F3.0,20
 162 timetz,Format: TIME11.0,21
 163 timetz_zone,Format: F8.2,22
 164
 165 Table: Data List
 166 bool,bytea,char,int8,int2,int4,numeric,text,oid,float4,float8,money,pbchar,varchar,date,time,timestamp,timestamptz,interval,interval_months,timetz,timetz_zone
 167 .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
 168 .  ,20,,.  ,.  ,.  ,.          ,,.  ,.  ,.  ,.  ,,,.,.,.,.,.,.,.,.  @&t@
 169 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
 170 ])
 171
 172 dnl Test query with empty result set.
 173 AT_CHECK([cat > empty-result.sps <<EOF
 174 GET DATA /TYPE=psql
 175         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 176         /UNENCRYPTED
 177         /SQL="select * from empty".
 178
 179 DISPLAY DICTIONARY.
 180
 181 LIST.
 182 EOF
 183 ])
 184 AT_CHECK([pspp -o pspp.csv empty-result.sps])
 185 AT_CHECK([cat pspp.csv], [0], [dnl
 186 Variable,Description,Position
 187 a,Format: F8.2,1
 188 b,Format: DATE11,2
 189 c,Format: E40.2,3
 190 ])
 191
 192 dnl Test query with large result set.
 193 AT_CHECK([cat > large-result.sps <<EOF
 194 GET DATA /TYPE=psql
 195         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 196         /UNENCRYPTED
 197         /SQL="select * from large".
 198
 199 NUMERIC diff.
 200 COMPUTE diff = x - lag (x).
 201
 202 TEMPORARY.
 203 SELECT IF (diff <> 1).
 204 LIST.
 205
 206 TEMPORARY.
 207 N OF CASES 6.
 208 LIST.
 209
 210 SORT CASES BY x (D).
 211
 212 TEMPORARY.
 213 N OF CASES 6.
 214 LIST.
 215 EOF
 216 ])
 217 AT_CHECK([pspp -o pspp.csv large-result.sps])
 218 AT_CHECK([cat pspp.csv], [0], [dnl
 219 Table: Data List
 220 x,diff
 221 1.00,.  @&t@
 222 2.00,1.00
 223 3.00,1.00
 224 4.00,1.00
 225 5.00,1.00
 226 6.00,1.00
 227
 228 Table: Data List
 229 x,diff
 230 1000.00,1.00
 231 999.00,1.00
 232 998.00,1.00
 233 997.00,1.00
 234 996.00,1.00
 235 995.00,1.00
 236 ])
 237
 238 dnl Check for a bug caused by having string variables in the database,
 239 dnl all of which are null.
 240 AT_DATA([all-null-string.sql],
 241   [-- a table which has a text field containing only null, or zero
 242    -- length entries.
 243
 244    CREATE TABLE foo (int4  int4, text text);
 245
 246    INSERT INTO foo VALUES ('12', '');
 247
 248    INSERT INTO foo VALUES (null, '');
 249 ])
 250 AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < all-null-string.sql],
 251   [0], [ignore])
 252 AT_CAPTURE_FILE([get-data.sps])
 253 AT_CHECK([cat > get-data.sps <<EOF
 254 GET DATA /TYPE=psql
 255         /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
 256         /UNENCRYPTED
 257         /SQL="select * from foo".
 258
 259 DISPLAY DICTIONARY.
 260
 261 LIST.
 262 EOF
 263 ])
 264 AT_CHECK([pspp -o pspp.csv get-data.sps])
 265 AT_CAPTURE_FILE([pspp.csv])
 266 rm -rf "$socket_dir"
 267 AT_CLEANUP