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