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
165 Name,Position,Label,Measurement Level,Role,Width,Alignment,Print Format,Write Format,Missing Values
166 bool,1,,Scale,Input,8,Right,F8.2,F8.2,
167 bytea,2,,Nominal,Input,1,Left,AHEX2,AHEX2,
168 char,3,,Nominal,Input,8,Left,A8,A8,
169 int8,4,,Scale,Input,8,Right,F8.2,F8.2,
170 int2,5,,Scale,Input,8,Right,F8.2,F8.2,
171 int4,6,,Scale,Input,8,Right,F8.2,F8.2,
172 numeric,7,,Scale,Input,8,Right,E40.6,E40.6,
173 text,8,,Nominal,Input,16,Left,A16,A16,
174 oid,9,,Scale,Input,8,Right,F8.2,F8.2,
175 float4,10,,Scale,Input,8,Right,F8.2,F8.2,
176 float8,11,,Scale,Input,8,Right,F8.2,F8.2,
177 money,12,,Scale,Input,8,Right,DOLLAR8.2,DOLLAR8.2,
178 pbchar,13,,Nominal,Input,8,Left,A8,A8,
179 varchar,14,,Nominal,Input,8,Left,A8,A8,
180 date,15,,Scale,Input,8,Right,DATE11,DATE11,
181 time,16,,Scale,Input,8,Right,TIME11.0,TIME11.0,
182 timestamp,17,,Scale,Input,8,Right,DATETIME22.0,DATETIME22.0,
183 timestamptz,18,,Scale,Input,8,Right,DATETIME22.0,DATETIME22.0,
184 interval,19,,Scale,Input,8,Right,DTIME13.0,DTIME13.0,
185 interval_months,20,,Scale,Input,8,Right,F3.0,F3.0,
186 timetz,21,,Scale,Input,8,Right,TIME11.0,TIME11.0,
187 timetz_zone,22,,Scale,Input,8,Right,F8.2,F8.2,
190 bool,bytea,char,int8,int2,int4,numeric,text,oid,float4,float8,money,pbchar,varchar,date,time,timestamp,timestamptz,interval,interval_months,timetz,timetz_zone
191 .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
192 . ,20,,. ,. ,. ,. ,,. ,. ,. ,. ,,,.,.,.,.,.,.,.,. @&t@
193 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
196 dnl Test query with empty result set.
197 AT_CHECK([cat > empty-result.sps <<EOF
199 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
201 /SQL="select * from empty".
208 AT_CHECK([pspp -o pspp.csv empty-result.sps])
209 AT_CHECK([cat pspp.csv], [0], [dnl
211 Name,Position,Label,Measurement Level,Role,Width,Alignment,Print Format,Write Format,Missing Values
212 a,1,,Scale,Input,8,Right,F8.2,F8.2,
213 b,2,,Scale,Input,8,Right,DATE11,DATE11,
214 c,3,,Scale,Input,8,Right,E40.2,E40.2,
217 dnl Test query with large result set.
218 AT_CHECK([cat > large-result.sps <<EOF
220 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
222 /SQL="select * from large".
225 COMPUTE diff = x - lag (x).
228 SELECT IF (diff <> 1).
242 AT_CHECK([pspp -o pspp.csv large-result.sps])
243 AT_CHECK([cat pspp.csv], [0], [dnl
263 dnl Check for a bug caused by having string variables in the database,
264 dnl all of which are null.
265 AT_DATA([all-null-string.sql],
266 [-- a table which has a text field containing only null, or zero
269 CREATE TABLE foo (int4 int4, text text);
271 INSERT INTO foo VALUES ('12', '');
273 INSERT INTO foo VALUES (null, '');
275 AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < all-null-string.sql],
277 AT_CAPTURE_FILE([get-data.sps])
278 AT_CHECK([cat > get-data.sps <<EOF
280 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
282 /SQL="select * from foo".
289 AT_CHECK([pspp -o pspp.csv get-data.sps])
290 AT_CAPTURE_FILE([pspp.csv])