Fix GCC 4.3 warning about uninitialized structure member.
[pspp] / tests / command / get-data-psql.sh
1 #!/bin/sh
2
3 # This program tests the psql import feature.
4
5 TEMPDIR=/tmp/pspp-tst-$$
6 TESTFILE=$TEMPDIR/`basename $0`.sps
7
8 # ensure that top_srcdir and top_builddir  are absolute
9 if [ -z "$top_srcdir" ] ; then top_srcdir=. ; fi
10 if [ -z "$top_builddir" ] ; then top_builddir=. ; fi
11 top_srcdir=`cd $top_srcdir; pwd`
12 top_builddir=`cd $top_builddir; pwd`
13
14 PSPP=$top_builddir/src/ui/terminal/pspp
15
16 STAT_CONFIG_PATH=$top_srcdir/config
17 export STAT_CONFIG_PATH
18
19 LANG=C
20 export LANG
21
22 port=6543
23 dbase=pspptest
24 PG_CONFIG=${PG_CONFIG:=pg_config}
25 pgpath=`$PG_CONFIG --bindir`
26
27 cleanup()
28 {
29     if [ x"$PSPP_TEST_NO_CLEANUP" != x ] ; then 
30         echo "NOT cleaning $TEMPDIR"
31         return ; 
32     fi
33     PGHOST=$TEMPDIR $pgpath/pg_ctl -D $TEMPDIR/cluster  stop -W -o "-k $TEMPDIR -h ''"   > /dev/null 2>&1
34     cd /
35     rm -rf $TEMPDIR
36 }
37
38
39 fail()
40 {
41     echo $activity
42     echo FAILED
43     cleanup;
44     exit 1;
45 }
46
47
48 no_result()
49 {
50     echo $activity
51     echo NO RESULT;
52     cleanup;
53     exit 2;
54 }
55
56 pass()
57 {
58     cleanup;
59     exit 0;
60 }
61
62 if [ ! -x $pgpath/initdb ] ; then
63   echo 'No Postgres server was found, so the postgres database interface cannot be tested.'  
64   cleanup;
65   exit 77;
66 fi
67
68 mkdir -p $TEMPDIR
69
70 cd $TEMPDIR
71
72 activity="create cluster"
73 $pgpath/initdb  -D $TEMPDIR/cluster -A trust > /dev/null
74 if [ $? -ne 0 ] ; then no_result ; fi
75
76 activity="run server"
77 PGHOST=$TEMPDIR PGPORT=$port $pgpath/pg_ctl -D $TEMPDIR/cluster  start -w -o "-k $TEMPDIR -h ''" > /dev/null
78 if [ $? -ne 0 ] ; then no_result ; fi
79
80
81 activity="create database"
82 $pgpath/createdb  -h $TEMPDIR  -p $port $dbase > /dev/null 2> /dev/null
83 if [ $? -ne 0 ] ; then no_result ; fi
84
85
86 activity="populate database"
87 $pgpath/psql  -h $TEMPDIR -p $port  $dbase > /dev/null << EOF
88
89 CREATE TABLE empty (a int, b date, c numeric(23, 4));
90
91 -- a largeish table to check big queries work ok.
92 CREATE TABLE large (x int);
93 INSERT INTO large  (select * from generate_series(1, 1000));
94
95
96 CREATE TABLE thing (
97  bool    bool                      ,
98  bytea   bytea                     ,
99  char    char                      ,
100  int8    int8                      ,
101  int2    int2                      ,
102  int4    int4                      ,
103  numeric       numeric(50,6)       ,
104  text    text                      ,
105  oid     oid                       ,
106  float4  float4                    ,
107  float8  float8                    ,
108  money   money                     ,
109  pbchar  bpchar                    ,
110  varchar varchar                   ,
111  date    date                      ,
112  time    time                      ,
113  timestamp     timestamp           ,
114  timestamptz   timestamptz         ,
115  interval      interval            ,
116  timetz        timetz              
117 );
118
119 INSERT INTO thing VALUES (
120  false,
121  '0',
122  'a',
123  '0',
124  0,
125  0,
126  -256.098,
127  'this-long-text',
128  0,
129  0,
130  0,
131  '0.01',
132  'a',
133  'A',
134  '1-Jan-2000',
135  '00:00',
136  'January 8 04:05:06 1999',
137  'January 8 04:05:06 1999 PST',
138  '1 minutes',
139  '10:09 UTC+4'
140 );
141
142 INSERT INTO thing VALUES (
143  null,
144  null,
145  null,
146  null,
147  null,
148  null,
149  null,
150  null,
151  null,
152  null,
153  null,
154  null,
155  null,
156  null,
157  null,
158  null,
159  null,
160  null,
161  null,
162  null
163 );
164
165 INSERT INTO thing VALUES (
166  true,
167  '1',
168  'b',
169  '1',
170  1,
171  1,
172  65535.00001,
173  'that-long-text',
174  1,
175  1,
176  1,
177  '1.23',
178  'b',
179  'B',
180  '10-Jan-1963',
181  '01:05:02',
182  '10-Jan-1963 23:58:00',
183  '10-Jan-1963 23:58:00 CET',
184  '2 year 1 month 12 days 1 hours 3 minutes 4 seconds',
185  '01:05:02 UTC-7'
186 );
187 EOF
188 if [ $? -ne 0 ] ; then fail ; fi
189
190 activity="create program 1"
191 cat > $TESTFILE <<EOF
192 GET DATA /TYPE=psql 
193         /CONNECT="host=$TEMPDIR port=$port dbname=$dbase"
194         /UNENCRYPTED
195         /SQL="select * from thing".
196
197 DISPLAY DICTIONARY.
198
199 LIST.
200 EOF
201 if [ $? -ne 0 ] ; then no_result ; fi
202
203
204 activity="run program 1"
205 $SUPERVISOR $PSPP --testing-mode -o raw-ascii $TESTFILE
206 if [ $? -ne 0 ] ; then no_result ; fi
207
208 activity="compare output 1"
209 perl -pi -e 's/^\s*$//g' $TEMPDIR/pspp.list
210 diff -b  $TEMPDIR/pspp.list - << 'EOF'
211 1.1 DISPLAY.  
212 +---------------+-------------------------------------------+--------+
213 |Variable       |Description                                |Position|
214 #===============#===========================================#========#
215 |bool           |Format: F8.2                               |       1|
216 |               |Measure: Scale                             |        |
217 |               |Display Alignment: Right                   |        |
218 |               |Display Width: 8                           |        |
219 +---------------+-------------------------------------------+--------+
220 |bytea          |Format: AHEX2                              |       2|
221 |               |Measure: Nominal                           |        |
222 |               |Display Alignment: Left                    |        |
223 |               |Display Width: 1                           |        |
224 +---------------+-------------------------------------------+--------+
225 |char           |Format: A8                                 |       3|
226 |               |Measure: Nominal                           |        |
227 |               |Display Alignment: Left                    |        |
228 |               |Display Width: 8                           |        |
229 +---------------+-------------------------------------------+--------+
230 |int8           |Format: F8.2                               |       4|
231 |               |Measure: Scale                             |        |
232 |               |Display Alignment: Right                   |        |
233 |               |Display Width: 8                           |        |
234 +---------------+-------------------------------------------+--------+
235 |int2           |Format: F8.2                               |       5|
236 |               |Measure: Scale                             |        |
237 |               |Display Alignment: Right                   |        |
238 |               |Display Width: 8                           |        |
239 +---------------+-------------------------------------------+--------+
240 |int4           |Format: F8.2                               |       6|
241 |               |Measure: Scale                             |        |
242 |               |Display Alignment: Right                   |        |
243 |               |Display Width: 8                           |        |
244 +---------------+-------------------------------------------+--------+
245 |numeric        |Format: E40.6                              |       7|
246 |               |Measure: Scale                             |        |
247 |               |Display Alignment: Right                   |        |
248 |               |Display Width: 8                           |        |
249 +---------------+-------------------------------------------+--------+
250 |text           |Format: A16                                |       8|
251 |               |Measure: Nominal                           |        |
252 |               |Display Alignment: Left                    |        |
253 |               |Display Width: 16                          |        |
254 +---------------+-------------------------------------------+--------+
255 |oid            |Format: F8.2                               |       9|
256 |               |Measure: Scale                             |        |
257 |               |Display Alignment: Right                   |        |
258 |               |Display Width: 8                           |        |
259 +---------------+-------------------------------------------+--------+
260 |float4         |Format: F8.2                               |      10|
261 |               |Measure: Scale                             |        |
262 |               |Display Alignment: Right                   |        |
263 |               |Display Width: 8                           |        |
264 +---------------+-------------------------------------------+--------+
265 |float8         |Format: F8.2                               |      11|
266 |               |Measure: Scale                             |        |
267 |               |Display Alignment: Right                   |        |
268 |               |Display Width: 8                           |        |
269 +---------------+-------------------------------------------+--------+
270 |money          |Format: DOLLAR8.2                          |      12|
271 |               |Measure: Scale                             |        |
272 |               |Display Alignment: Right                   |        |
273 |               |Display Width: 8                           |        |
274 +---------------+-------------------------------------------+--------+
275 |pbchar         |Format: A8                                 |      13|
276 |               |Measure: Nominal                           |        |
277 |               |Display Alignment: Left                    |        |
278 |               |Display Width: 8                           |        |
279 +---------------+-------------------------------------------+--------+
280 |varchar        |Format: A8                                 |      14|
281 |               |Measure: Nominal                           |        |
282 |               |Display Alignment: Left                    |        |
283 |               |Display Width: 8                           |        |
284 +---------------+-------------------------------------------+--------+
285 |date           |Format: DATE11                             |      15|
286 |               |Measure: Scale                             |        |
287 |               |Display Alignment: Right                   |        |
288 |               |Display Width: 8                           |        |
289 +---------------+-------------------------------------------+--------+
290 |time           |Format: TIME11.0                           |      16|
291 |               |Measure: Scale                             |        |
292 |               |Display Alignment: Right                   |        |
293 |               |Display Width: 8                           |        |
294 +---------------+-------------------------------------------+--------+
295 |timestamp      |Format: DATETIME22.0                       |      17|
296 |               |Measure: Scale                             |        |
297 |               |Display Alignment: Right                   |        |
298 |               |Display Width: 8                           |        |
299 +---------------+-------------------------------------------+--------+
300 |timestamptz    |Format: DATETIME22.0                       |      18|
301 |               |Measure: Scale                             |        |
302 |               |Display Alignment: Right                   |        |
303 |               |Display Width: 8                           |        |
304 +---------------+-------------------------------------------+--------+
305 |interval       |Format: DTIME13.0                          |      19|
306 |               |Measure: Scale                             |        |
307 |               |Display Alignment: Right                   |        |
308 |               |Display Width: 8                           |        |
309 +---------------+-------------------------------------------+--------+
310 |interval_months|Format: F3.0                               |      20|
311 |               |Measure: Scale                             |        |
312 |               |Display Alignment: Right                   |        |
313 |               |Display Width: 8                           |        |
314 +---------------+-------------------------------------------+--------+
315 |timetz         |Format: TIME11.0                           |      21|
316 |               |Measure: Scale                             |        |
317 |               |Display Alignment: Right                   |        |
318 |               |Display Width: 8                           |        |
319 +---------------+-------------------------------------------+--------+
320 |timetz_zone    |Format: F8.2                               |      22|
321 |               |Measure: Scale                             |        |
322 |               |Display Alignment: Right                   |        |
323 |               |Display Width: 8                           |        |
324 +---------------+-------------------------------------------+--------+
325     bool bytea     char     int8     int2     int4                                  numeric             text      oid   float4   float8    money   pbchar  varchar        date        time              timestamp            timestamptz      interval interval_months      timetz timetz_zone
326 -------- ----- -------- -------- -------- -------- ---------------------------------------- ---------------- -------- -------- -------- -------- -------- -------- ----------- ----------- ---------------------- ---------------------- ------------- --------------- ----------- -----------
327      .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 
328      .      20               .        .        .                                .                                 .        .        .        .                               .           .                      .                      .             .               .           .         .   
329     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 
330 EOF
331 if [ $? -ne 0 ] ; then fail ; fi
332
333
334 activity="create program 2"
335 cat > $TESTFILE <<EOF
336 GET DATA /TYPE=psql 
337         /CONNECT="host=$TEMPDIR port=$port dbname=$dbase"
338         /UNENCRYPTED
339         /SQL="select * from empty".
340
341 DISPLAY DICTIONARY.
342
343 LIST.
344 EOF
345 if [ $? -ne 0 ] ; then no_result ; fi
346
347 activity="run program 2"
348 $SUPERVISOR $PSPP --testing-mode -o raw-ascii $TESTFILE
349 if [ $? -ne 0 ] ; then no_result ; fi
350
351 activity="compare output 2"
352 perl -pi -e 's/^\s*$//g' $TEMPDIR/pspp.list
353 diff -b  $TEMPDIR/pspp.list - << 'EOF'
354 1.1 DISPLAY.  
355 +--------+-------------------------------------------+--------+
356 |Variable|Description                                |Position|
357 #========#===========================================#========#
358 |a       |Format: F8.2                               |       1|
359 |        |Measure: Scale                             |        |
360 |        |Display Alignment: Right                   |        |
361 |        |Display Width: 8                           |        |
362 +--------+-------------------------------------------+--------+
363 |b       |Format: DATE11                             |       2|
364 |        |Measure: Scale                             |        |
365 |        |Display Alignment: Right                   |        |
366 |        |Display Width: 8                           |        |
367 +--------+-------------------------------------------+--------+
368 |c       |Format: E40.2                              |       3|
369 |        |Measure: Scale                             |        |
370 |        |Display Alignment: Right                   |        |
371 |        |Display Width: 8                           |        |
372 +--------+-------------------------------------------+--------+
373 EOF
374 if [ $? -ne 0 ] ; then fail ; fi
375
376 activity="create program 3"
377 cat > $TESTFILE <<EOF
378 GET DATA /TYPE=psql 
379         /CONNECT="host=$TEMPDIR port=$port dbname=$dbase"
380         /UNENCRYPTED
381         /BSIZE = 27
382         /SQL="select * from large".
383
384 NUMERIC diff.
385 COMPUTE diff = x - lag (x).
386
387 TEMPORARY.
388 SELECT IF (diff <> 1).
389 LIST.
390
391 TEMPORARY.
392 N OF CASES 6.
393 LIST.
394
395 SORT CASES BY x (D).
396
397 TEMPORARY.
398 N OF CASES 6.
399 LIST.
400
401 EOF
402 if [ $? -ne 0 ] ; then no_result ; fi
403
404 activity="run program 3"
405 $SUPERVISOR $PSPP --testing-mode -o raw-ascii $TESTFILE
406 if [ $? -ne 0 ] ; then no_result ; fi
407
408 activity="compare output 3"
409 perl -pi -e 's/^\s*$//g' $TEMPDIR/pspp.list
410 diff -b  $TEMPDIR/pspp.list - << 'EOF'
411        x     diff
412 -------- --------
413     1.00      .   
414     2.00     1.00 
415     3.00     1.00 
416     4.00     1.00 
417     5.00     1.00 
418     6.00     1.00 
419        x     diff
420 -------- --------
421  1000.00     1.00 
422   999.00     1.00 
423   998.00     1.00 
424   997.00     1.00 
425   996.00     1.00 
426   995.00     1.00 
427 EOF
428 if [ $? -ne 0 ] ; then fail ; fi
429
430 pass;