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