1 dnl PSPP - a program for statistical analysis.
2 dnl Copyright (C) 2017 Free Software Foundation, Inc.
4 dnl This program is free software: you can redistribute it and/or modify
5 dnl it under the terms of the GNU General Public License as published by
6 dnl the Free Software Foundation, either version 3 of the License, or
7 dnl (at your option) any later version.
9 dnl This program is distributed in the hope that it will be useful,
10 dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
11 dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 dnl GNU General Public License for more details.
14 dnl You should have received a copy of the GNU General Public License
15 dnl along with this program. If not, see <http://www.gnu.org/licenses/>.
17 AT_BANNER([GET DATA /TYPE=PSQL])
19 m4_define([INIT_PSQL],
20 [AT_SKIP_IF([test "$PSQL_SUPPORT" = no])
25 socket_dir=`mktemp -d`
28 AT_CHECK([PATH=$PG_PATH:$PATH initdb -A trust], [0], [ignore])
29 AT_CHECK([PATH=$PG_PATH:$PATH pg_ctl start -w -o "-k $socket_dir -h ''"], [0], [ignore])
31 AT_CHECK([PATH=$PG_PATH:$PATH createdb -h "$socket_dir" -p $PG_PORT $PG_DBASE],
32 [0], [ignore], [ignore])
33 AT_DATA([populate.sql],
34 [CREATE TABLE empty (a int, b date, c numeric(23, 4));
36 -- a largeish table to check big queries work ok.
37 CREATE TABLE large (x int);
38 INSERT INTO large (select * from generate_series(1, 1000));
48 numeric numeric(50,6) ,
59 timestamptz timestamptz ,
64 INSERT INTO thing VALUES (
81 'January 8 04:05:06 1999',
82 'January 8 04:05:06 1999 PST',
87 INSERT INTO thing VALUES (
110 INSERT INTO thing VALUES (
127 '10-Jan-1963 23:58:00',
128 '10-Jan-1963 23:58:00 CET',
129 '2 year 1 month 12 days 1 hours 3 minutes 4 seconds',
134 # On Debian, the psql binary in the postgres bindir won't work because
135 # it needs libreadline to be LD_PRELOADed into it. The psql in the
136 # normal $PATH works fine though.
137 if (PATH=$PG_PATH:$PATH psql -V) >/dev/null 2>&1; then
139 PATH=$PG_PATH:$PATH command psql "$@"
142 AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < populate.sql],
145 m4_define([CLEANUP_PSQL], [PATH=$PG_PATH:$PATH pg_ctl stop -W -o "-k $socket_dir -h ''"])
147 AT_SETUP([GET DATA /TYPE=PSQL])
150 dnl Test with an ordinary query.
151 AT_CHECK([cat > ordinary-query.sps <<EOF
153 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
155 /SQL="select * from thing".
162 AT_CHECK([pspp -o pspp.csv ordinary-query.sps])
163 AT_CHECK([cat pspp.csv], [0], [dnl
164 Variable,Description,Position
166 bytea,Format: AHEX2,2
171 numeric,Format: E40.6,7
174 float4,Format: F8.2,10
175 float8,Format: F8.2,11
176 money,Format: DOLLAR8.2,12
178 varchar,Format: A8,14
179 date,Format: DATE11,15
180 time,Format: TIME11.0,16
181 timestamp,Format: DATETIME22.0,17
182 timestamptz,Format: DATETIME22.0,18
183 interval,Format: DTIME13.0,19
184 interval_months,Format: F3.0,20
185 timetz,Format: TIME11.0,21
186 timetz_zone,Format: F8.2,22
189 bool,bytea,char,int8,int2,int4,numeric,text,oid,float4,float8,money,pbchar,varchar,date,time,timestamp,timestamptz,interval,interval_months,timetz,timetz_zone
190 .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
191 . ,20,,. ,. ,. ,. ,,. ,. ,. ,. ,,,.,.,.,.,.,.,.,. @&t@
192 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
195 dnl Test query with empty result set.
196 AT_CHECK([cat > empty-result.sps <<EOF
198 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
200 /SQL="select * from empty".
207 AT_CHECK([pspp -o pspp.csv empty-result.sps])
208 AT_CHECK([cat pspp.csv], [0], [dnl
209 Variable,Description,Position
215 dnl Test query with large result set.
216 AT_CHECK([cat > large-result.sps <<EOF
218 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
220 /SQL="select * from large".
223 COMPUTE diff = x - lag (x).
226 SELECT IF (diff <> 1).
240 AT_CHECK([pspp -o pspp.csv large-result.sps])
241 AT_CHECK([cat pspp.csv], [0], [dnl
261 dnl Check for a bug caused by having string variables in the database,
262 dnl all of which are null.
263 AT_DATA([all-null-string.sql],
264 [-- a table which has a text field containing only null, or zero
267 CREATE TABLE foo (int4 int4, text text);
269 INSERT INTO foo VALUES ('12', '');
271 INSERT INTO foo VALUES (null, '');
273 AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < all-null-string.sql],
275 AT_CAPTURE_FILE([get-data.sps])
276 AT_CHECK([cat > get-data.sps <<EOF
278 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
280 /SQL="select * from foo".
287 AT_CHECK([pspp -o pspp.csv get-data.sps])
288 AT_CAPTURE_FILE([pspp.csv])