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