Hi
2016-11-13 19:46 GMT+01:00 Pavel Stehule <[email protected]>:
> Hi
>
> I am sending a initial implementation of psql content commands. This
> design is reaction to Tom's objections against psql file ref variables.
> This design is more cleaner, more explicit and more practical - import can
> be in one step.
>
> Now supported commands are:
>
> r - read file without any modification
> rq - read file, escape as literal and use outer quotes
> rb - read binary file - transform to hex code string
> rbq - read binary file, transform to hex code string and use outer quotes
>
> Usage:
>
> create table testt(a xml);
> insert into test values( {rq /home/pavel/.local/share/rhythmbox/rhythmdb.xml}
> );
> \set xxx {r /home/pavel/.local/share/rhythmbox/rhythmdb.xml}
>
> This patch is demo of this design - one part is redundant - I'll clean it
> in next iteration.
>
> Regards
>
>
here is cleaned patch
* the behave is much more psqlish - show error only when the command is
exactly detected - errors are related to processed files only - the behave
is similar to psql variables - we doesn't raise a error, when the variable
doesn't exists.
* no more duplicate code
* some basic doc
* some basic regress tests
Regards
Pavel
> Pavel
>
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 2410bee..141df6f 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2991,6 +2991,57 @@ testdb=> <userinput>\setenv LESS -imx4F</userinput>
</refsect3>
</refsect2>
+ <refsect2 id="APP-PSQL-content-commands">
+ <title>Content Commands</title>
+
+ <para>
+ These commands inject some content to processed query.
+
+<programlisting>
+testdb=> <userinput>CREATE TABLE my_table(id int, image bytea);</userinput>
+testdb=> <userinput>INSERT INTO my_table VALUES(1, {rbq ~/avatar.gif } ); </userinput>
+</programlisting>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>{r <replaceable class="parameter">filename</>}</literal></term>
+ <listitem>
+ <para>
+ Returns content of file.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>{rb <replaceable class="parameter">filename</>}</literal></term>
+ <listitem>
+ <para>
+ Returns content of binary file encoded to hex code.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>{rq <replaceable class="parameter">filename</>}</literal></term>
+ <listitem>
+ <para>
+ Returns content of file, escaped and quoted as string literal
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>{rb <replaceable class="parameter">filename</>}</literal></term>
+ <listitem>
+ <para>
+ Returns content of binary file encoded to hex code and quoted.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </refsect2>
+
<refsect2>
<title>Advanced Features</title>
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index a7fdd8a..43f699f 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -462,6 +462,7 @@ static void setalarm(int seconds);
/* callback functions for our flex lexer */
static const PsqlScanCallbacks pgbench_callbacks = {
NULL, /* don't need get_variable functionality */
+ NULL, /* don't need eval_content_command functionality */
pgbench_error
};
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index a7789df..de7fecd 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -12,6 +12,7 @@
#include <limits.h>
#include <math.h>
#include <signal.h>
+#include <sys/stat.h>
#ifndef WIN32
#include <unistd.h> /* for write() */
#else
@@ -168,6 +169,211 @@ psql_get_variable(const char *varname, bool escape, bool as_ident)
return result;
}
+#define READFILE_NONE (0)
+#define READFILE_NOESCAPE (1 << 0)
+#define READFILE_ESCAPE_TEXT (1 << 1)
+#define READFILE_ESCAPE_BINARY (1 << 2)
+#define READFILE_QUOTE (1 << 3)
+
+/*
+ * file-content-fetching callback for read file content commands.
+ */
+static char *
+get_file_content(char *fname, int mode)
+{
+ FILE *fd;
+ char *result = NULL;
+
+ expand_tilde(&fname);
+ canonicalize_path(fname);
+
+ fd = fopen(fname, PG_BINARY_R);
+ if (fd)
+ {
+ struct stat fst;
+
+ if (fstat(fileno(fd), &fst) != -1)
+ {
+ if (S_ISREG(fst.st_mode))
+ {
+ if (fst.st_size <= ((int64) 1024) * 1024 * 1024)
+ {
+ size_t size;
+ PQExpBufferData raw_data;
+ char buf[512];
+
+ initPQExpBuffer(&raw_data);
+
+ while ((size = fread(buf, 1, sizeof(buf), fd)) > 0)
+ appendBinaryPQExpBuffer(&raw_data, buf, size);
+
+ if (!ferror(fd) && !(PQExpBufferDataBroken(raw_data)))
+ {
+ if (!(mode & READFILE_NOESCAPE))
+ {
+ if (mode & READFILE_ESCAPE_BINARY)
+ {
+ unsigned char *escaped_value;
+ size_t escaped_size;
+
+ escaped_value = PQescapeByteaConn(pset.db,
+ (const unsigned char *) raw_data.data, raw_data.len,
+ &escaped_size);
+
+ if (escaped_value)
+ {
+ if (mode & READFILE_QUOTE)
+ {
+ PQExpBufferData resultbuf;
+
+ initPQExpBuffer(&resultbuf);
+
+ appendPQExpBufferChar(&resultbuf, '\'');
+ appendBinaryPQExpBuffer(&resultbuf,
+ (const char *) escaped_value, escaped_size - 1);
+ appendPQExpBufferChar(&resultbuf, '\'');
+ PQfreemem(escaped_value);
+
+ if (PQExpBufferDataBroken(resultbuf))
+ psql_error("out of memory\n");
+ else
+ result = resultbuf.data;
+ }
+ else
+ result = (char *) escaped_value;
+ }
+ else
+ psql_error("%s\n", PQerrorMessage(pset.db));
+ }
+ else
+ {
+ /*
+ * should be READFILE_ESCAPE_TEXT & READFILE_QUOTE
+ * escaping of text file has not sense without quoting
+ */
+ result = PQescapeLiteral(pset.db,
+ raw_data.data, raw_data.len);
+ if (result == NULL)
+ psql_error("%s\n", PQerrorMessage(pset.db));
+ }
+ }
+ else
+ result = raw_data.data;
+ }
+ else
+ {
+ if (PQExpBufferDataBroken(raw_data))
+ psql_error("out of memory\n");
+ else
+ psql_error("%s: %s\n", fname, strerror(errno));
+ }
+
+ if (result != raw_data.data)
+ termPQExpBuffer(&raw_data);
+ }
+ else
+ psql_error("%s is too big (greather than 1GB)\n", fname);
+ }
+ else
+ psql_error("%s is not regular file\n", fname);
+ }
+ else
+ psql_error("%s: %s\n", fname, strerror(errno));
+
+ fclose(fd);
+ }
+ else
+ psql_error("%s: %s\n", fname, strerror(errno));
+
+ return result;
+}
+
+/*
+ * a implementation of psql content commands - curly bracket
+ * commands.
+ *
+ * It does nothing, when command is not detected - like evaluation of
+ * psql variables. Usually the SQL statement fails on syntax error - the
+ * behave is same like evaluation of psql variable.
+ */
+void
+psql_eval_content_command(PQExpBuffer output_buf, int start_offset)
+{
+ char *content_command;
+ char *cptr;
+ int readfile_mode = READFILE_NONE;
+
+ /* do updatable copy of input buffer, input buffer should not be changed */
+ content_command = pg_strdup(output_buf->data + start_offset);
+
+ /* skip initial left bracket */
+ cptr = content_command + 1;
+
+ /* skip initial spaces */
+ while (*cptr == ' ')
+ cptr++;
+
+ if (*cptr != '\0')
+ {
+ char *cname = cptr;
+
+ /* find a end of statement */
+ while (*cptr != ' ' && *cptr != '\0')
+ cptr++;
+
+ /* the space is required in supported commands */
+ if (*cptr != '\0')
+ {
+ *cptr++ = '\0';
+
+ if (strcmp(cname, "r") == 0)
+ readfile_mode = READFILE_NOESCAPE;
+ else if (strcmp(cname, "rb") == 0)
+ readfile_mode = READFILE_ESCAPE_BINARY;
+ else if (strcmp(cname, "rq") == 0)
+ readfile_mode = READFILE_ESCAPE_TEXT | READFILE_QUOTE;
+ else if (strcmp(cname, "rbq") == 0)
+ readfile_mode = READFILE_ESCAPE_BINARY | READFILE_QUOTE;
+
+ if (readfile_mode != READFILE_NONE)
+ {
+ /* parse file name from the rest of content command */
+
+ /* skip initial spaces */
+ while (*cptr == ' ')
+ cptr++;
+
+ if (cptr != '\0')
+ {
+ char *eptr = cptr + strlen(cptr) - 1 - 1; /* drop right bracket */
+
+ while (*eptr == ' ' && eptr > cptr)
+ eptr--;
+
+ eptr[1] = '\0';
+
+ if (cptr != '\0')
+ {
+ char *result = get_file_content(cptr, readfile_mode);
+
+ if (result != NULL)
+ {
+ /* remove content command text from output buffer */
+ output_buf->len = start_offset;
+ output_buf->data[output_buf->len] = '\0';
+
+ /* append the result of content command */
+ appendPQExpBufferStr(output_buf, result);
+ PQfreemem(result);
+ }
+ }
+ }
+ }
+ }
+ }
+
+ PQfreemem(content_command);
+}
/*
* Error reporting for scripts. Errors should look like
diff --git a/src/bin/psql/common.h b/src/bin/psql/common.h
index bdcb58f..18a9c86 100644
--- a/src/bin/psql/common.h
+++ b/src/bin/psql/common.h
@@ -11,6 +11,7 @@
#include <setjmp.h>
#include "libpq-fe.h"
+#include "pqexpbuffer.h"
#include "fe_utils/print.h"
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
@@ -19,6 +20,7 @@ extern bool openQueryOutputFile(const char *fname, FILE **fout, bool *is_pipe);
extern bool setQFout(const char *fname);
extern char *psql_get_variable(const char *varname, bool escape, bool as_ident);
+extern void psql_eval_content_command(PQExpBuffer output_buf, int start_offset);
extern void psql_error(const char *fmt,...) pg_attribute_printf(1, 2);
diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c
index 37dfa4d..69c697b 100644
--- a/src/bin/psql/mainloop.c
+++ b/src/bin/psql/mainloop.c
@@ -20,6 +20,7 @@
/* callback functions for our flex lexer */
const PsqlScanCallbacks psqlscan_callbacks = {
psql_get_variable,
+ psql_eval_content_command,
psql_error
};
diff --git a/src/bin/psql/psqlscanslash.l b/src/bin/psql/psqlscanslash.l
index 86832a8..06b34a4 100644
--- a/src/bin/psql/psqlscanslash.l
+++ b/src/bin/psql/psqlscanslash.l
@@ -19,6 +19,7 @@
#include "postgres_fe.h"
#include "psqlscanslash.h"
+#include "common.h"
#include "libpq-fe.h"
}
@@ -46,6 +47,7 @@ static enum slash_option_type option_type;
static char *option_quote;
static int unquoted_option_chars;
static int backtick_start_offset;
+static int curlybracket_start_offset;
/* Return values from yylex() */
@@ -98,6 +100,7 @@ extern void slash_yyset_column(int column_no, yyscan_t yyscanner);
%x xslashdquote
%x xslashwholeline
%x xslashend
+%x xslashcurlybrackets
/*
* Assorted character class definitions that should match psqlscan.l.
@@ -228,6 +231,14 @@ other .
BEGIN(xslashdquote);
}
+"{" {
+ curlybracket_start_offset = output_buf->len;
+ ECHO;
+ *option_quote = '{';
+ unquoted_option_chars = 0;
+ BEGIN(xslashcurlybrackets);
+ }
+
:{variable_char}+ {
/* Possible psql variable substitution */
if (option_type == OT_NO_EVAL ||
@@ -362,6 +373,25 @@ other .
}
+<xslashcurlybrackets>{
+ /* curly brackets: copy everything until next right curly bracket */
+
+"}" {
+ /* In NO_EVAL mode, don't evaluate the command */
+ ECHO;
+ if (option_type != OT_NO_EVAL)
+ {
+ if (cur_state->callbacks->eval_content_command)
+ cur_state->callbacks->eval_content_command(cur_state->output_buf,
+ curlybracket_start_offset);
+ }
+ BEGIN(xslasharg);
+ }
+
+{other}|\n { ECHO; }
+
+}
+
<xslashdquote>{
/* double-quoted text: copy verbatim, including the double quotes */
@@ -580,6 +610,7 @@ psql_scan_slash_option(PsqlScanState state,
case xslashquote:
case xslashbackquote:
case xslashdquote:
+ case xslashcurlybrackets:
/* must have hit EOL inside quotes */
state->callbacks->write_error("unterminated quoted string\n");
termPQExpBuffer(&mybuf);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b556c00..b8d6c83 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -58,7 +58,7 @@ extern char *filename_completion_function();
#endif
/* word break characters */
-#define WORD_BREAKS "\t\n@$><=;|&{() "
+#define WORD_BREAKS "\t\n@$><=;|&() "
/*
* Since readline doesn't let us pass any state through to the tab completion
@@ -1343,6 +1343,11 @@ psql_completion(const char *text, int start, int end)
"\\timing", "\\unset", "\\x", "\\w", "\\watch", "\\z", "\\!", NULL
};
+ /* psql's content commands. */
+ static const char *const content_commands[] = {
+ "{r", "{rq", "{rb", "{rbq", NULL
+ };
+
(void) end; /* "end" is not used */
#ifdef HAVE_RL_COMPLETION_APPEND_CHARACTER
@@ -1368,6 +1373,10 @@ psql_completion(const char *text, int start, int end)
if (text[0] == '\\')
COMPLETE_WITH_LIST_CS(backslash_commands);
+ else if (text[0] == '{')
+ {
+ COMPLETE_WITH_LIST_CS(content_commands);
+ }
/* If current word is a variable interpolation, handle that case */
else if (text[0] == ':' && text[1] != ':')
{
@@ -3222,6 +3231,11 @@ psql_completion(const char *text, int start, int end)
completion_charp = "\\";
matches = completion_matches(text, complete_from_files);
}
+ else if (TailMatchesCS1("\{r|\{rb|\{rq|\{rbq"))
+ {
+ completion_charp = "{";
+ matches = completion_matches(text, complete_from_files);
+ }
/*
* Finally, we look through the list of "things", such as TABLE, INDEX and
diff --git a/src/fe_utils/psqlscan.l b/src/fe_utils/psqlscan.l
index 55067b4..49d6dae 100644
--- a/src/fe_utils/psqlscan.l
+++ b/src/fe_utils/psqlscan.l
@@ -47,6 +47,8 @@
*/
typedef int YYSTYPE;
+static int curlybracket_start_offset;
+
/*
* Set the type of yyextra; we use it as a pointer back to the containing
* PsqlScanState.
@@ -115,6 +117,7 @@ extern void psql_yyset_column(int column_no, yyscan_t yyscanner);
* <xuiend> end of a quoted identifier with Unicode escapes, UESCAPE can follow
* <xus> quoted string with Unicode escapes
* <xusend> end of a quoted string with Unicode escapes, UESCAPE can follow
+ * <xcb> curly bracket string
*
* Note: we intentionally don't mimic the backend's <xeu> state; we have
* no need to distinguish it from <xe> state, and no good way to get out
@@ -133,6 +136,7 @@ extern void psql_yyset_column(int column_no, yyscan_t yyscanner);
%x xuiend
%x xus
%x xusend
+%x xcb
/*
* In order to make the world safe for Windows and Mac clients as well as
@@ -673,6 +677,24 @@ other .
}
}
+"{" {
+ curlybracket_start_offset = cur_state->output_buf->len;
+ BEGIN(xcb);
+ ECHO;
+ }
+
+<xcb>"}" {
+ BEGIN(INITIAL);
+ ECHO;
+ if (cur_state->callbacks->eval_content_command)
+ cur_state->callbacks->eval_content_command(cur_state->output_buf,
+ curlybracket_start_offset);
+ }
+
+<xcb>.|\n {
+ ECHO;
+ }
+
/*
* psql-specific rules to handle backslash commands and variable
* substitution. We want these before {self}, also.
diff --git a/src/include/fe_utils/psqlscan.h b/src/include/fe_utils/psqlscan.h
index 1f10ecc..a70a40e 100644
--- a/src/include/fe_utils/psqlscan.h
+++ b/src/include/fe_utils/psqlscan.h
@@ -54,6 +54,8 @@ typedef struct PsqlScanCallbacks
/* Fetch value of a variable, as a pfree'able string; NULL if unknown */
/* This pointer can be NULL if no variable substitution is wanted */
char *(*get_variable) (const char *varname, bool escape, bool as_ident);
+ /* eval psql content command */
+ void (*eval_content_command) (PQExpBuffer output_buf, int start_offset);
/* Print an error message someplace appropriate */
/* (very old gcc versions don't support attributes on function pointers) */
#if defined(__GNUC__) && __GNUC__ < 4
diff --git a/src/test/regress/input/misc.source b/src/test/regress/input/misc.source
index dd2d1b2..bca752f 100644
--- a/src/test/regress/input/misc.source
+++ b/src/test/regress/input/misc.source
@@ -273,3 +273,21 @@ drop table oldstyle_test;
--
-- rewrite rules
--
+
+--
+-- psql content commands
+--
+CREATE TABLE test_setref(a text, b bytea);
+
+-- use two different ways for import data - result should be same
+\lo_import @abs_builddir@/data/hash.data
+\set lo_oid :LASTOID
+INSERT INTO test_setref (b) VALUES(lo_get(:lo_oid));
+\lo_unlink :lo_oid
+SELECT md5(b) FROM test_setref;
+TRUNCATE test_setref;
+
+INSERT INTO test_setref(a,b) VALUES({rq @abs_builddir@/data/hash.data}, {rbq @abs_builddir@/data/hash.data});
+SELECT md5(a), md5(b) FROM test_setref;
+
+DROP TABLE test_setref;
diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source
index 574ef0d..0c8eb97 100644
--- a/src/test/regress/output/misc.source
+++ b/src/test/regress/output/misc.source
@@ -708,3 +708,27 @@ drop table oldstyle_test;
--
-- rewrite rules
--
+--
+-- psql content commands
+--
+CREATE TABLE test_setref(a text, b bytea);
+-- use two different ways for import data - result should be same
+\lo_import @abs_builddir@/data/hash.data
+\set lo_oid :LASTOID
+INSERT INTO test_setref (b) VALUES(lo_get(:lo_oid));
+\lo_unlink :lo_oid
+SELECT md5(b) FROM test_setref;
+ md5
+----------------------------------
+ e446fe6ea5a347e69670633412c7f8cb
+(1 row)
+
+TRUNCATE test_setref;
+INSERT INTO test_setref(a,b) VALUES({rq @abs_builddir@/data/hash.data}, {rbq @abs_builddir@/data/hash.data});
+SELECT md5(a), md5(b) FROM test_setref;
+ md5 | md5
+----------------------------------+----------------------------------
+ e446fe6ea5a347e69670633412c7f8cb | e446fe6ea5a347e69670633412c7f8cb
+(1 row)
+
+DROP TABLE test_setref;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
