dnl PSPP - a program for statistical analysis.
dnl Copyright (C) 2017 Free Software Foundation, Inc.
dnl
dnl This program is free software: you can redistribute it and/or modify
dnl it under the terms of the GNU General Public License as published by
dnl the Free Software Foundation, either version 3 of the License, or
dnl (at your option) any later version.
dnl
dnl This program is distributed in the hope that it will be useful,
dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
dnl GNU General Public License for more details.
dnl
dnl You should have received a copy of the GNU General Public License
dnl along with this program.  If not, see <http://www.gnu.org/licenses/>.
dnl
AT_BANNER([GET DATA /TYPE=PSQL])

m4_define([INIT_PSQL],
  [AT_SKIP_IF([test "$PSQL_SUPPORT" = no])
   PGDATA=`pwd`/cluster
   export PGDATA
   PGPORT=$PG_PORT
   export PGPORT
   socket_dir=`mktemp -d`
   PGHOST="$socket_dir"
   export PGHOST
   AT_CHECK([PATH=$PG_PATH:$PATH initdb -A trust], [0], [ignore])
   AT_CHECK([PATH=$PG_PATH:$PATH pg_ctl start -w -o "-k $socket_dir -h ''"], [0], [ignore])
   trap 'CLEANUP_PSQL' 0
   AT_CHECK([PATH=$PG_PATH:$PATH createdb -h "$socket_dir" -p $PG_PORT $PG_DBASE],
      [0], [ignore], [ignore])
   AT_DATA([populate.sql],
     [CREATE TABLE empty (a int, b date, c numeric(23, 4));

      -- a largeish table to check big queries work ok.
      CREATE TABLE large (x int);
      INSERT INTO large  (select * from generate_series(1, 1000));


      CREATE TABLE thing (
       bool    bool                      ,
       bytea   bytea                     ,
       char    char                      ,
       int8    int8                      ,
       int2    int2                      ,
       int4    int4                      ,
       numeric       numeric(50,6)       ,
       text    text                      ,
       oid     oid                       ,
       float4  float4                    ,
       float8  float8                    ,
       money   money                     ,
       pbchar  bpchar                    ,
       varchar varchar                   ,
       date    date                      ,
       time    time                      ,
       timestamp     timestamp           ,
       timestamptz   timestamptz         ,
       interval      interval            ,
       timetz        timetz
      );

      INSERT INTO thing VALUES (
       false,
       '0',
       'a',
       '0',
       0,
       0,
       -256.098,
       'this-long-text',
       0,
       0,
       0,
       '0.01',
       'a',
       'A',
       '1-Jan-2000',
       '00:00',
       'January 8 04:05:06 1999',
       'January 8 04:05:06 1999 PST',
       '1 minutes',
       '10:09 UTC+4'
      );

      INSERT INTO thing VALUES (
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null
      );

      INSERT INTO thing VALUES (
       true,
       '1',
       'b',
       '1',
       1,
       1,
       65535.00001,
       'that-long-text',
       1,
       1,
       1,
       '1.23',
       'b',
       'B',
       '10-Jan-1963',
       '01:05:02',
       '10-Jan-1963 23:58:00',
       '10-Jan-1963 23:58:00 CET',
       '2 year 1 month 12 days 1 hours 3 minutes 4 seconds',
       '01:05:02 UTC-7'
      );
])

   # On Debian, the psql binary in the postgres bindir won't work because
   # it needs libreadline to be LD_PRELOADed into it.  The psql in the
   # normal $PATH works fine though.
   if (PATH=$PG_PATH:$PATH psql -V) >/dev/null 2>&1; then
       psql () {
           PATH=$PG_PATH:$PATH command psql "$$@@"
       }
   fi
   AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < populate.sql],
      [0], [ignore])])

m4_define([CLEANUP_PSQL], [PATH=$PG_PATH:$PATH pg_ctl stop -W -o "-k $socket_dir -h ''"])

AT_SETUP([GET DATA /TYPE=PSQL])
AT_KEYWORDS([slow])
INIT_PSQL

dnl Test with an ordinary query.
AT_CHECK([cat > ordinary-query.sps <<EOF
GET DATA /TYPE=psql
	/CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
	/UNENCRYPTED
	/SQL="select * from thing".

DISPLAY DICTIONARY.

LIST.
EOF
])
AT_CHECK([pspp -o pspp.csv ordinary-query.sps])
AT_CHECK([cat pspp.csv], [0], [dnl
Table: Variables
Name,Position,Measurement Level,Role,Width,Alignment,Print Format,Write Format
bool,1,Unknown,Input,8,Right,F8.2,F8.2
bytea,2,Nominal,Input,1,Left,AHEX2,AHEX2
char,3,Nominal,Input,8,Left,A8,A8
int8,4,Unknown,Input,8,Right,F8.2,F8.2
int2,5,Unknown,Input,8,Right,F8.2,F8.2
int4,6,Unknown,Input,8,Right,F8.2,F8.2
numeric,7,Unknown,Input,8,Right,E40.6,E40.6
text,8,Nominal,Input,16,Left,A16,A16
oid,9,Unknown,Input,8,Right,F8.2,F8.2
float4,10,Unknown,Input,8,Right,F8.2,F8.2
float8,11,Unknown,Input,8,Right,F8.2,F8.2
money,12,Unknown,Input,8,Right,DOLLAR8.2,DOLLAR8.2
pbchar,13,Nominal,Input,8,Left,A8,A8
varchar,14,Nominal,Input,8,Left,A8,A8
date,15,Unknown,Input,8,Right,DATE11,DATE11
time,16,Unknown,Input,8,Right,TIME11.0,TIME11.0
timestamp,17,Unknown,Input,8,Right,DATETIME22.0,DATETIME22.0
timestamptz,18,Unknown,Input,8,Right,DATETIME22.0,DATETIME22.0
interval,19,Unknown,Input,8,Right,DTIME13.0,DTIME13.0
interval_months,20,Unknown,Input,8,Right,F3.0,F3.0
timetz,21,Unknown,Input,8,Right,TIME11.0,TIME11.0
timetz_zone,22,Unknown,Input,8,Right,F8.2,F8.2

Table: Data List
bool,bytea,char,int8,int2,int4,numeric,text,oid,float4,float8,money,pbchar,varchar,date,time,timestamp,timestamptz,interval,interval_months,timetz,timetz_zone
.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
.  ,,,.  ,.  ,.  ,.          ,,.  ,.  ,.  ,.  ,,,.,.,.,.,.,.,.,.  @&t@
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
])

dnl Test query with empty result set.
AT_CHECK([cat > empty-result.sps <<EOF
GET DATA /TYPE=psql
	/CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
	/UNENCRYPTED
	/SQL="select * from empty".

DISPLAY DICTIONARY.

LIST.
EOF
])
AT_CHECK([pspp -o pspp.csv empty-result.sps])
AT_CHECK([cat pspp.csv], [0], [dnl
Table: Variables
Name,Position,Measurement Level,Role,Width,Alignment,Print Format,Write Format
a,1,Unknown,Input,8,Right,F8.2,F8.2
b,2,Unknown,Input,8,Right,DATE11,DATE11
c,3,Unknown,Input,8,Right,E40.2,E40.2
])

dnl Test query with large result set.
AT_CHECK([cat > large-result.sps <<EOF
GET DATA /TYPE=psql
	/CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
	/UNENCRYPTED
	/SQL="select * from large".

NUMERIC diff.
COMPUTE diff = x - lag (x).

TEMPORARY.
SELECT IF (diff <> 1).
LIST.

TEMPORARY.
N OF CASES 6.
LIST.

SORT CASES BY x (D).

TEMPORARY.
N OF CASES 6.
LIST.
EOF
])
AT_CHECK([pspp -o pspp.csv large-result.sps])
AT_CHECK([cat pspp.csv], [0], [dnl
Table: Data List
x,diff
1.00,.  @&t@
2.00,1.00
3.00,1.00
4.00,1.00
5.00,1.00
6.00,1.00

Table: Data List
x,diff
1000.00,1.00
999.00,1.00
998.00,1.00
997.00,1.00
996.00,1.00
995.00,1.00
])

dnl Check for a bug caused by having string variables in the database,
dnl all of which are null.
AT_DATA([all-null-string.sql],
  [-- a table which has a text field containing only null, or zero
   -- length entries.

   CREATE TABLE foo (int4  int4, text text);

   INSERT INTO foo VALUES ('12', '');

   INSERT INTO foo VALUES (null, '');
])
AT_CHECK([psql -h "$socket_dir" -p $PG_PORT $PG_DBASE < all-null-string.sql],
  [0], [ignore])
AT_CAPTURE_FILE([get-data.sps])
AT_CHECK([cat > get-data.sps <<EOF
GET DATA /TYPE=psql
	/CONNECT="host=$socket_dir port=$PGPORT dbname=$PG_DBASE"
	/UNENCRYPTED
	/SQL="select * from foo".

DISPLAY DICTIONARY.

LIST.
EOF
])
AT_CHECK([pspp -o pspp.csv get-data.sps])
AT_CAPTURE_FILE([pspp.csv])
rm -rf "$socket_dir"
AT_CLEANUP