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])
151 dnl Test with an ordinary query.
152 AT_CHECK([cat > ordinary-query.sps <<EOF
154 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
156 /SQL="select * from thing".
163 AT_CHECK([pspp -o pspp.csv ordinary-query.sps])
164 AT_CHECK([cat pspp.csv], [0], [dnl
166 Name,Position,Measurement Level,Role,Width,Alignment,Print Format,Write Format
167 bool,1,Scale,Input,8,Right,F8.2,F8.2
168 bytea,2,Nominal,Input,1,Left,AHEX2,AHEX2
169 char,3,Nominal,Input,8,Left,A8,A8
170 int8,4,Scale,Input,8,Right,F8.2,F8.2
171 int2,5,Scale,Input,8,Right,F8.2,F8.2
172 int4,6,Scale,Input,8,Right,F8.2,F8.2
173 numeric,7,Scale,Input,8,Right,E40.6,E40.6
174 text,8,Nominal,Input,16,Left,A16,A16
175 oid,9,Scale,Input,8,Right,F8.2,F8.2
176 float4,10,Scale,Input,8,Right,F8.2,F8.2
177 float8,11,Scale,Input,8,Right,F8.2,F8.2
178 money,12,Scale,Input,8,Right,DOLLAR8.2,DOLLAR8.2
179 pbchar,13,Nominal,Input,8,Left,A8,A8
180 varchar,14,Nominal,Input,8,Left,A8,A8
181 date,15,Scale,Input,8,Right,DATE11,DATE11
182 time,16,Scale,Input,8,Right,TIME11.0,TIME11.0
183 timestamp,17,Scale,Input,8,Right,DATETIME22.0,DATETIME22.0
184 timestamptz,18,Scale,Input,8,Right,DATETIME22.0,DATETIME22.0
185 interval,19,Scale,Input,8,Right,DTIME13.0,DTIME13.0
186 interval_months,20,Scale,Input,8,Right,F3.0,F3.0
187 timetz,21,Scale,Input,8,Right,TIME11.0,TIME11.0
188 timetz_zone,22,Scale,Input,8,Right,F8.2,F8.2
191 bool,bytea,char,int8,int2,int4,numeric,text,oid,float4,float8,money,pbchar,varchar,date,time,timestamp,timestamptz,interval,interval_months,timetz,timetz_zone
192 .00,30,a,.00,.00,.00,-2.560980E+002,this-long-text,.00,.00,.00,$.01,a,A,01-JAN-2000,00:00:00,08-JAN-1999 04:05:06,08-JAN-1999 12:05:06,0 00:01:00,0,10:09:00,4.00
193 . ,,,. ,. ,. ,. ,,. ,. ,. ,. ,,,.,.,.,.,.,.,.,. @&t@
194 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,01:05:02,10-JAN-1963 23:58:00,10-JAN-1963 22:58:00,12 01:03:04,25,01:05:02,-7.00
197 dnl Test query with empty result set.
198 AT_CHECK([cat > empty-result.sps <<EOF
200 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
202 /SQL="select * from empty".
209 AT_CHECK([pspp -o pspp.csv empty-result.sps])
210 AT_CHECK([cat pspp.csv], [0], [dnl
212 Name,Position,Measurement Level,Role,Width,Alignment,Print Format,Write Format
213 a,1,Scale,Input,8,Right,F8.2,F8.2
214 b,2,Scale,Input,8,Right,DATE11,DATE11
215 c,3,Scale,Input,8,Right,E40.2,E40.2
218 dnl Test query with large result set.
219 AT_CHECK([cat > large-result.sps <<EOF
221 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
223 /SQL="select * from large".
226 COMPUTE diff = x - lag (x).
229 SELECT IF (diff <> 1).
243 AT_CHECK([pspp -o pspp.csv large-result.sps])
244 AT_CHECK([cat pspp.csv], [0], [dnl
264 dnl Check for a bug caused by having string variables in the database,
265 dnl all of which are null.
266 AT_DATA([all-null-string.sql],
267 [-- a table which has a text field containing only null, or zero
270 CREATE TABLE foo (int4 int4, text text);
272 INSERT INTO foo VALUES ('12', '');
274 INSERT INTO foo VALUES (null, '');
276 AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < all-null-string.sql],
278 AT_CAPTURE_FILE([get-data.sps])
279 AT_CHECK([cat > get-data.sps <<EOF
281 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
283 /SQL="select * from foo".
290 AT_CHECK([pspp -o pspp.csv get-data.sps])
291 AT_CAPTURE_FILE([pspp.csv])