From dde7b813c5747fba5d14e47f6dd82bb7b4dc7cf1 Mon Sep 17 00:00:00 2001 From: John Darrington Date: Mon, 4 Feb 2008 01:24:00 +0000 Subject: [PATCH] Added the postgres reader. Closes patch #6388 --- configure.ac | 14 ++++++ doc/ChangeLog | 6 +++ doc/data-io.texi | 2 + doc/files.texi | 78 +++++++++++++++++++++++++++++---- src/data/ChangeLog | 5 +++ src/data/automake.mk | 4 +- src/language/data-io/ChangeLog | 4 ++ src/language/data-io/get-data.c | 71 +++++++++++++++++++++++++++++- src/ui/gui/automake.mk | 1 + src/ui/terminal/automake.mk | 1 + tests/automake.mk | 4 ++ 11 files changed, 179 insertions(+), 11 deletions(-) diff --git a/configure.ac b/configure.ac index 5b8e1a93..cbf3d557 100644 --- a/configure.ac +++ b/configure.ac @@ -47,6 +47,20 @@ fi AM_CONDITIONAL(WITHGUI, test x"$with_gui" != x"no") +dnl Checks needed for psql reader +AC_CHECK_PROG(psql_support, pg_config, yes, no) +if test x"$psql_support" = x"yes" ; then + AC_DEFINE([PSQL_SUPPORT], 1, + [Define to 1 if building in support for reading from postgres databases.]) + PG_CFLAGS=-I`pg_config --includedir` + AC_SUBST(PG_CFLAGS) + PG_LDFLAGS=-L`pg_config --libdir` + AC_SUBST(PG_LDFLAGS) + PG_LIBS=-lpq + AC_SUBST(PG_LIBS) +fi +AM_CONDITIONAL(PSQL_SUPPORT, test x"$psql_support" = x"yes") + dnl Checks needed for gnumeric reader gnm_support=yes; PKG_CHECK_MODULES(LIBXML2, libxml-2.0,, diff --git a/doc/ChangeLog b/doc/ChangeLog index c1fa40eb..021e2343 100644 --- a/doc/ChangeLog +++ b/doc/ChangeLog @@ -1,3 +1,9 @@ +2008-02-04 John Darrington + + * files.texi data-io.texi: Document the GET DATA TYPE=PSQL + option. Thanks to Ben Pfaff for reviewing this text. + + 2007-11-10 Ben Pfaff * not-implemented.texi: Fix @include command so that it works diff --git a/doc/data-io.texi b/doc/data-io.texi index 8ca25108..b6a3a6d2 100644 --- a/doc/data-io.texi +++ b/doc/data-io.texi @@ -14,6 +14,8 @@ their sex, age, etc.@: and their responses are all data and the data pertaining to single respondent is a case. This chapter examines the PSPP commands for defining variables and reading and writing data. +There are alternative commands to read data from predefined sources +such as system files or databases (@xref{GET, GET DATA}.) @quotation Note These commands tell PSPP how to read data, but the data will not diff --git a/doc/files.texi b/doc/files.texi index a00d113d..df01567e 100644 --- a/doc/files.texi +++ b/doc/files.texi @@ -163,8 +163,7 @@ extension. @display GET DATA - /TYPE=@{GNM,TXT@} - /FILE=@{'file-name',file_handle@} + /TYPE=@{GNM,PSQL,TXT@} @dots{}additional subcommands depending on TYPE@dots{} @end display @@ -181,19 +180,19 @@ PSPP currently supports the following file types: @item GNM Spreadsheet files created by Gnumeric (@url{http://gnumeric.org}). +@item PSQL +Relations from PostgreSQL databases (@url{http://postgresql.org}). + @item TXT Textual data files in columnar and delimited formats. @end table -The FILE subcommand is mandatory for all implemented file types. -Specify the file to be read as a string file name or (for textual data -only) a file handle (@pxref{File Handles}). - Each supported file type has additional subcommands, explained in separate sections below. @menu * GET DATA /TYPE=GNM:: +* GET DATA /TYPE=PSQL:: * GET DATA /TYPE=TXT:: @end menu @@ -222,6 +221,10 @@ If this cell is of string (text) format, then the width of the variable is determined from the length of the string it contains, unless the ASSUMEDVARWIDTH subcommand is given. + +The FILE subcommand is mandatory. Specify the name of the file +to be read. + The SHEET subcommand specifies the sheet within the spreadsheet file to read. There are two forms of the SHEET subcommand. In the first form, @@ -253,6 +256,61 @@ variables read from the file. If omitted, the default value is determined from the length of the string in the first spreadsheet cell for each variable. + +@node GET DATA /TYPE=PSQL +@subsection Postgres Database Queries + +@display +GET DATA /TYPE=PSQL + /CONNECT=@{connection info@} + /SQL=@{query@} + [/ASSUMEDVARWIDTH=n] + [/UNENCRYPTED]. +@end display + +@cindex postgres +@cindex databases + +The PSQL type is used to import data from a postgres database server. +The server may be located locally or remotely. +Variables are automatically created based on the table column names +or the names specified in the SQL query. +Postgres data types of high precision, will loose precision when +imported into PSPP. +Not all the postgres data types are able to be represented in PSPP. +If a datum cannot be represented a warning will be issued and that +datum will be set to SYSMIS. + +The CONNECT subcommand is mandatory. +It is a string specifying the parameters of the database server from +which the data should be fetched. +The format of the string is given in the postgres manual +@url{http://www.postgresql.org/docs/8.0/static/libpq.html#LIBPQ-CONNECT}. + +The SQL subcommand is mandatory. +It must be a valid SQL string to retrieve data from the database. + +The ASSUMEDVARWIDTH subcommand specifies the maximum width of string +variables read from the database. +If omitted, the default value is determined from the length of the +string in the first value read for each variable. + +The UNENCRYPTED subcommand allows data to be retrieved over an insecure +connection. +If the connection is not encrypted, and the UNENCRYPTED subcommand is not +given, then an error will occur. +Whether or not the connection is +encrypted depends upon the underlying psql library and the +capabilities of the database server. + +The following syntax is an example: +@example +GET DATA /TYPE=PSQL + /CONNECT='host=example.com port=5432 dbname=product user=fred passwd=xxxx' + /SQL='select * from manufacturer'. +@end example + + @node GET DATA /TYPE=TXT @subsection Textual Data Files @@ -268,9 +326,11 @@ GET DATA /TYPE=TXT @cindex text files @cindex data files When TYPE=TXT is specified, GET DATA reads data in a delimited or -fixed columnar format, much like DATA LIST (@pxref{DATA LIST}). The -FILE subcommand must be specified indicate the name or the file handle -of the file to be read. +fixed columnar format, much like DATA LIST (@pxref{DATA LIST}). + +The FILE subcommand is mandatory. Specify the file to be read as +a string file name or (for textual data +only) a file handle (@pxref{File Handles}). The ARRANGEMENT subcommand determines the file's basic format. DELIMITED, the default setting, specifies that fields in the input diff --git a/src/data/ChangeLog b/src/data/ChangeLog index c1dd098a..012ffaf5 100644 --- a/src/data/ChangeLog +++ b/src/data/ChangeLog @@ -1,3 +1,8 @@ +2008-02-02 John Darrington + + psql-reader.c psql-reader.h: New files. Thanks to Ben Pfaff + for reviewing this code. + 2008-02-02 Ben Pfaff Patch #6347. diff --git a/src/data/automake.mk b/src/data/automake.mk index e0679867..a432e300 100644 --- a/src/data/automake.mk +++ b/src/data/automake.mk @@ -1,7 +1,7 @@ noinst_LIBRARIES += src/data/libdata.a -src_data_libdata_a_CPPFLAGS = $(LIBXML2_CFLAGS) $(AM_CPPFLAGS) +src_data_libdata_a_CPPFLAGS = $(LIBXML2_CFLAGS) $(PG_CFLAGS) $(AM_CPPFLAGS) src_data_libdata_a_SOURCES = \ @@ -69,6 +69,8 @@ src_data_libdata_a_SOURCES = \ src/data/por-file-reader.h \ src/data/por-file-writer.c \ src/data/por-file-writer.h \ + src/data/psql-reader.c \ + src/data/psql-reader.h \ src/data/scratch-handle.c \ src/data/scratch-handle.h \ src/data/scratch-reader.c \ diff --git a/src/language/data-io/ChangeLog b/src/language/data-io/ChangeLog index 112efefb..187d6464 100644 --- a/src/language/data-io/ChangeLog +++ b/src/language/data-io/ChangeLog @@ -1,3 +1,7 @@ +2008-02-02 John Darrington + + * get-data.c (cmd_get_data): Support PSQL type. + 2007-12-07 Ben Pfaff Patch #6302. diff --git a/src/language/data-io/get-data.c b/src/language/data-io/get-data.c index 7b38d622..189054a5 100644 --- a/src/language/data-io/get-data.c +++ b/src/language/data-io/get-data.c @@ -1,5 +1,5 @@ /* PSPP - a program for statistical analysis. - Copyright (C) 2007 Free Software Foundation, Inc. + Copyright (C) 2007, 2008 Free Software Foundation, Inc. This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -19,6 +19,7 @@ #include #include +#include #include #include @@ -38,6 +39,7 @@ static int parse_get_gnm (struct lexer *lexer, struct dataset *); static int parse_get_txt (struct lexer *lexer, struct dataset *); +static int parse_get_psql (struct lexer *lexer, struct dataset *); int cmd_get_data (struct lexer *lexer, struct dataset *ds) @@ -53,11 +55,78 @@ cmd_get_data (struct lexer *lexer, struct dataset *ds) return parse_get_gnm (lexer, ds); else if (lex_match_id (lexer, "TXT")) return parse_get_txt (lexer, ds); + else if (lex_match_id (lexer, "PSQL")) + return parse_get_psql (lexer, ds); msg (SE, _("Unsupported TYPE %s"), lex_tokid (lexer)); return CMD_FAILURE; } +static int +parse_get_psql (struct lexer *lexer, struct dataset *ds) +{ + struct psql_read_info psql; + psql.allow_clear = false; + psql.conninfo = NULL; + psql.str_width = -1; + ds_init_empty (&psql.sql); + + lex_force_match (lexer, '/'); + + if (!lex_force_match_id (lexer, "CONNECT")) + goto error; + + lex_force_match (lexer, '='); + + if (!lex_force_string (lexer)) + goto error; + + psql.conninfo = strdup (ds_cstr (lex_tokstr (lexer))); + + lex_get (lexer); + + while (lex_match (lexer, '/') ) + { + if ( lex_match_id (lexer, "ASSUMEDSTRWIDTH")) + { + lex_match (lexer, '='); + psql.str_width = lex_integer (lexer); + lex_get (lexer); + } + else if ( lex_match_id (lexer, "UNENCRYPTED")) + { + psql.allow_clear = true; + } + else if (lex_match_id (lexer, "SQL")) + { + lex_match (lexer, '='); + if ( ! lex_force_string (lexer) ) + goto error; + + ds_put_substring (&psql.sql, lex_tokstr (lexer)->ss); + lex_get (lexer); + } + } + { + struct dictionary *dict = NULL; + struct casereader *reader = psql_open_reader (&psql, &dict); + + if ( reader ) + proc_set_active_file (ds, reader, dict); + } + + ds_destroy (&psql.sql); + free (psql.conninfo); + + return CMD_SUCCESS; + + error: + + ds_destroy (&psql.sql); + free (psql.conninfo); + + return CMD_FAILURE; +} static int parse_get_gnm (struct lexer *lexer, struct dataset *ds) diff --git a/src/ui/gui/automake.mk b/src/ui/gui/automake.mk index 70cfefdb..dfbc5785 100644 --- a/src/ui/gui/automake.mk +++ b/src/ui/gui/automake.mk @@ -41,6 +41,7 @@ src_ui_gui_psppire_LDADD = \ src/libpspp/libpspp.a \ $(GTK_LIBS) \ $(GLADE_LIBS) \ + $(PG_LIBS) \ gl/libgl.la \ @LIBINTL@ @LIBREADLINE@ diff --git a/src/ui/terminal/automake.mk b/src/ui/terminal/automake.mk index 65cd23a3..0684e313 100644 --- a/src/ui/terminal/automake.mk +++ b/src/ui/terminal/automake.mk @@ -33,6 +33,7 @@ src_ui_terminal_pspp_LDADD = \ src/data/libdata.a \ src/libpspp/libpspp.a \ $(LIBXML2_LIBS) \ + $(PG_LIBS) \ $(LIBICONV) \ gl/libgl.la \ @LIBINTL@ @LIBREADLINE@ diff --git a/tests/automake.mk b/tests/automake.mk index aa9a92ff..6d0fd670 100644 --- a/tests/automake.mk +++ b/tests/automake.mk @@ -154,6 +154,10 @@ if GNM_SUPPORT dist_TESTS += tests/command/get-data-gnm.sh endif +if PSQL_SUPPORT +dist_TESTS += tests/command/get-data-psql.sh +endif + nodist_TESTS = \ tests/libpspp/abt-test \ tests/libpspp/bt-test \ -- 2.30.2