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])
27 socket_dir=`mktemp -d`
30 AT_CHECK([initdb -A trust], [0], [ignore])
31 AT_CHECK([pg_ctl start -w -o "-k $socket_dir -h ''"], [0], [ignore])
33 AT_CHECK([createdb -h "$socket_dir" -p $PG_PORT $PG_DBASE],
34 [0], [ignore], [ignore])
35 AT_DATA([populate.sql],
36 [CREATE TABLE empty (a int, b date, c numeric(23, 4));
38 -- a largeish table to check big queries work ok.
39 CREATE TABLE large (x int);
40 INSERT INTO large (select * from generate_series(1, 1000));
50 numeric numeric(50,6) ,
61 timestamptz timestamptz ,
66 INSERT INTO thing VALUES (
83 'January 8 04:05:06 1999',
84 'January 8 04:05:06 1999 PST',
89 INSERT INTO thing VALUES (
112 INSERT INTO thing VALUES (
129 '10-Jan-1963 23:58:00',
130 '10-Jan-1963 23:58:00 CET',
131 '2 year 1 month 12 days 1 hours 3 minutes 4 seconds',
135 AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < populate.sql],
138 m4_define([CLEANUP_PSQL], [pg_ctl stop -W -o "-k $socket_dir -h ''"])
140 AT_SETUP([GET DATA /TYPE=PSQL])
143 dnl Test with an ordinary query.
144 AT_CHECK([cat > ordinary-query.sps <<EOF
146 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
148 /SQL="select * from thing".
155 AT_CHECK([pspp -o pspp.csv ordinary-query.sps])
156 AT_CHECK([cat pspp.csv], [0], [dnl
157 Variable,Description,Position
159 bytea,Format: AHEX2,2
164 numeric,Format: E40.6,7
167 float4,Format: F8.2,10
168 float8,Format: F8.2,11
169 money,Format: DOLLAR8.2,12
171 varchar,Format: A8,14
172 date,Format: DATE11,15
173 time,Format: TIME11.0,16
174 timestamp,Format: DATETIME22.0,17
175 timestamptz,Format: DATETIME22.0,18
176 interval,Format: DTIME13.0,19
177 interval_months,Format: F3.0,20
178 timetz,Format: TIME11.0,21
179 timetz_zone,Format: F8.2,22
182 bool,bytea,char,int8,int2,int4,numeric,text,oid,float4,float8,money,pbchar,varchar,date,time,timestamp,timestamptz,interval,interval_months,timetz,timetz_zone
183 .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
184 . ,20,,. ,. ,. ,. ,,. ,. ,. ,. ,,,.,.,.,.,.,.,.,. @&t@
185 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
188 dnl Test query with empty result set.
189 AT_CHECK([cat > empty-result.sps <<EOF
191 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
193 /SQL="select * from empty".
200 AT_CHECK([pspp -o pspp.csv empty-result.sps])
201 AT_CHECK([cat pspp.csv], [0], [dnl
202 Variable,Description,Position
208 dnl Test query with large result set.
209 AT_CHECK([cat > large-result.sps <<EOF
211 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
213 /SQL="select * from large".
216 COMPUTE diff = x - lag (x).
219 SELECT IF (diff <> 1).
233 AT_CHECK([pspp -o pspp.csv large-result.sps])
234 AT_CHECK([cat pspp.csv], [0], [dnl
254 dnl Check for a bug caused by having string variables in the database,
255 dnl all of which are null.
256 AT_DATA([all-null-string.sql],
257 [-- a table which has a text field containing only null, or zero
260 CREATE TABLE foo (int4 int4, text text);
262 INSERT INTO foo VALUES ('12', '');
264 INSERT INTO foo VALUES (null, '');
266 AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < all-null-string.sql],
268 AT_CAPTURE_FILE([get-data.sps])
269 AT_CHECK([cat > get-data.sps <<EOF
271 /CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
273 /SQL="select * from foo".
280 AT_CHECK([pspp -o pspp.csv get-data.sps])
281 AT_CAPTURE_FILE([pspp.csv])