Hi
The dburl (or dburi) has become common to use by many systems connecting to
a database. The feature is that one can pass all parameters in a string,
which has similar pattern as http-URI do.
Especially when using psql in a script, having the credentials in one
string is convenient.
The syntax could be:
[scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,column...]*]]]|sql]]]
Example of usage:
psql pgsql://joe:[email protected]:2345/dbname
Where
Scheme: pgsql
Username: joe
Password: p4zzw0rd
Host: example.org
Port: 2345
Database: dbname
I have attached an example of how it could be implemented. It uses libpcre
RegEx to pass the dburl.
best regards
Hans
diff -Naur /home/hsc/tmp/postgresql-10beta1/src/bin/psql/dburl.c ./dburl.c
--- /home/hsc/tmp/postgresql-10beta1/src/bin/psql/dburl.c 1970-01-01
01:00:00.000000000 +0100
+++ ./dburl.c 2017-07-05 13:52:30.823234720 +0200
@@ -0,0 +1,261 @@
+/*
+ * Compile:
+ * gcc -Wall -DUNIT_TEST dburl.c -lpcre -o dburl
+ *
+ * Test:
+ * ./dburl 'pgsqls://example/'
'pgsqls://username:password@host:5432/dbname/SELECT * FROM mytable'
+ * ./dburl 'mysql://username:password@host:3306/dbname/table/column1,column2'
+ * ./dburl 'odbc+dsn:////table/column1,column2'
+ */
+
+//#define INCLUDE_COMMENTS 1
+
+#ifdef UNIT_TEST
+#include <stdio.h>
+#endif
+
+#include <stdlib.h>
+#include <string.h>
+#include <sys/types.h>
+#include <pcre.h>
+#include <errno.h>
+#include "dburl.h"
+
+#define OVECCOUNT (50*3)
+
+#define IDX_SCHEME 1
+#define IDX_DSN IDX_SCHEME+1
+#define IDX_USERNAME IDX_DSN+1
+#define IDX_PASSWORD IDX_USERNAME+1
+#define IDX_HOST IDX_PASSWORD+1
+#define IDX_PORT IDX_HOST+1
+#define IDX_DBNAME IDX_PORT+1
+#define IDX_TABLE IDX_DBNAME+1
+#define IDX_COLUMN IDX_TABLE+1
+#define IDX_SQL IDX_COLUMN+1
+
+const char *schemeitems[] = {
+ "null",
+ "scheme",
+ "dsn",
+ "username",
+ "password",
+ "host",
+ "port",
+ "dbname",
+ "table",
+ "column",
+ "sql"
+};
+
+#ifdef INCLUDE_COMMENTS
+#define cm(msg) "(?#\n " msg "\n)"
+#else
+#define cm(msg)
+#endif
+
+const char syntaxdescription[] =
+"[sql:][scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,column...]*]]]|sql]]]"
+;
+const char dburlregularexpression[] =
+"^"
+ cm("Optional prefix 'sql:'")
+ "(?:sql:)?"
+ cm("Scheme: pgsql")
+ "([-.a-z0-9]*)(?:[+]([-.a-z0-9]*))?"
+ cm("Required: URL identifier")
+ "://"
+ cm("Username + password")
+ "(?:"
+ cm("Username")
+ "([-a-z0-9_]+)"
+ cm("Password")
+ "(?::([^@]*))?@"
+ ")?"
+ cm("Hostname")
+ "("
+ cm("localhost | example")
+ "(?:[a-z0-9]+(?:-+[-a-z0-9]+)*)"
+ "|"
+ cm("Domain name with dot: example.com")
+ "(?:(?:[a-z0-9]+(?:-+[a-z0-9]+)*\\.)?"
+ "(?:[a-z0-9]+(?:-+[a-z0-9]+)*\\.)+(?:[a-z]{2,7})\\.?)"
+ "|"
+ cm("IPv4 number")
+
"(?:(?:25[0-5]|2[0-4][0-9]|[0-1][0-9]{2}|[0-9][0-9]|[0-9])\\.){3}"
+ "(?:25[0-5]|2[0-4][0-9]|[0-1][0-9]{2}|[0-9][0-9]|[0-9])"
+ ")?"
+ cm("Port number: 3306|5432")
+ "(?::(\\d{1,5}))?"
+ cm("DB, table, SQL")
+ "(?:/"
+ "(?:"
+ cm("Dbname: joe|mydb, default $USER")
+ "(?:([_a-z0-9]+)?"
+ "(?:/"
+ "(?:"
+ cm("Table: mytable")
+ "(?:([_a-z0-9]+)"
+ cm("Columns: id, name")
+ "(?:/"
+
"((?:[_a-z0-9]+)"
+
"(?:,[_a-z0-9]+)*"
+ ")?"
+ ")?"
+ ")|("
+ cm("SQL: SELECT id,
name FROM mytable")
+ "[^\\h]+\\h.+"
+ ")?"
+ ")?"
+ ")?"
+ ")?"
+ ")?"
+ ")?"
+"$"
+;
+
+static char *termstring(char *txt, int *ov, int idx, char *para_def) {
+ char *tmp = NULL;
+
+ /* if there is a match on this index... */
+ if (ov[2*idx+1] > 0) {
+ int length = ov[2*idx+1] - ov[2*idx];
+ if ((tmp = malloc(length+1))) {
+ strncpy(tmp, &txt[ov[2*idx]], length);
+ tmp[length] = 0;
+ }
+ }
+ return tmp ? tmp : para_def;
+}
+
+dburl_t *dburlparse(char *dburltext) {
+ dburl_t *dburl = NULL;
+
+ if (dburltext) {
+ pcre *re;
+ const char *error;
+ int erroffset;
+ int ovector[OVECCOUNT];
+ int rc;
+
+ re = pcre_compile(
+ dburlregularexpression,
+ PCRE_CASELESS,
+ &error,
+ &erroffset,
+ NULL);
+ if (re) {
+ rc = pcre_exec(
+ re,
+ NULL,
+ dburltext,
+ strlen(dburltext),
+ 0,
+ 0,
+ ovector,
+ OVECCOUNT);
+ if (rc > 0) {
+#ifdef UNIT_TEST
+ int i;
+ int m = 0;
+ int color = -1;
+ printf("Input: '%s'\n", dburltext);
+ printf(" ");
+ for (i = 1; i < rc; i++) {
+ while (m < ovector[2*i]) {
+ if (color >= 0) {
printf("\e[0m"); color = -1; }
+ printf("_");
+ ++m;
+ }
+ while (m < ovector[2*i+1]) {
+ if (color != i % 2) {
printf("\e[3%dm", i % 2 ? 5 : 3); color = i % 2; }
+ printf("%d", i % 10);
+ ++m;
+ }
+ }
+ if (color >= 0) { printf("\e[0m"); color = -1; }
+ printf("\n");
+ for (i = 0; i < rc; i++) {
+ char *substring_start = dburltext +
ovector[2*i];
+ int substring_length = ovector[2*i+1] -
ovector[2*i];
+ printf("# %2d [%2d %2d]: %.*s\n", i,
ovector[2*i], ovector[2*i+1], substring_length, substring_start);
+ }
+#endif
+ dburl = malloc(sizeof(*dburl));
+ memset(dburl, 0, sizeof(*dburl));
+ /* Assign all possible elements a value */
+ dburl->scheme = termstring(dburltext, ovector,
IDX_SCHEME, NULL);
+ dburl->dsn = termstring(dburltext, ovector,
IDX_DSN, NULL);
+ dburl->username = termstring(dburltext,
ovector, IDX_USERNAME, NULL);
+ dburl->password = termstring(dburltext,
ovector, IDX_PASSWORD, NULL);
+ dburl->host = termstring(dburltext,
ovector, IDX_HOST, NULL);
+ dburl->port = termstring(dburltext,
ovector, IDX_PORT, NULL);
+ dburl->dbname = termstring(dburltext, ovector,
IDX_DBNAME, NULL);
+ dburl->table = termstring(dburltext, ovector,
IDX_TABLE, NULL);
+ dburl->column = termstring(dburltext, ovector,
IDX_COLUMN, NULL);
+ dburl->sql = termstring(dburltext, ovector,
IDX_SQL, NULL);
+ }
+ pcre_free(re);
+ }
+ }
+
+ return dburl;
+}
+
+#ifdef UNIT_TEST
+void fieldfree(char *p) {
+ if (p) {
+ free(p);
+ }
+}
+
+int dburlfree(dburl_t *dburl) {
+ if (dburl) {
+ fieldfree(dburl->scheme);
+ fieldfree(dburl->dsn);
+ fieldfree(dburl->username);
+ fieldfree(dburl->password);
+ fieldfree(dburl->host);
+ fieldfree(dburl->port);
+ fieldfree(dburl->dbname);
+ fieldfree(dburl->table);
+ fieldfree(dburl->column);
+ fieldfree(dburl->sql);
+ free(dburl);
+ dburl = NULL;
+ }
+ return 1;
+}
+
+int main(int argc, char **argv) {
+ int i = 1;
+
+ printf("\n%s\n", syntaxdescription);
+ printf("\n%s\n\n", dburlregularexpression);
+ while (i<argc) {
+ dburl_t *url = dburlparse(argv[i]);
+
+ if (url) {
+ printf("%2d. %-10s: %s\n", IDX_SCHEME,
schemeitems[IDX_SCHEME], url->scheme);
+ printf("%2d. %-10s: %s\n", IDX_DSN,
schemeitems[IDX_DSN], url->dsn);
+ printf("%2d. %-10s: %s\n", IDX_USERNAME,
schemeitems[IDX_USERNAME], url->username);
+ printf("%2d. %-10s: %s\n", IDX_PASSWORD,
schemeitems[IDX_PASSWORD], url->password);
+ printf("%2d. %-10s: %s\n", IDX_HOST,
schemeitems[IDX_HOST], url->host);
+ printf("%2d. %-10s: %s\n", IDX_PORT,
schemeitems[IDX_PORT], url->port);
+ printf("%2d. %-10s: %s\n", IDX_DBNAME,
schemeitems[IDX_DBNAME], url->dbname);
+ printf("%2d. %-10s: %s\n", IDX_TABLE,
schemeitems[IDX_TABLE], url->table);
+ printf("%2d. %-10s: %s\n", IDX_COLUMN,
schemeitems[IDX_COLUMN], url->column);
+ printf("%2d. %-10s: %s\n", IDX_SQL,
schemeitems[IDX_SQL], url->sql);
+ } else {
+ printf("ERROR parsing '%s'\n", argv[i]);
+ }
+ printf("\n");
+
+ // BUG: Had disable, can not free() constant default like
"<scheme>"
+ //dburlfree(url);
+ i++;
+ }
+
+ return 0;
+}
+#endif
diff -Naur /home/hsc/tmp/postgresql-10beta1/src/bin/psql/dburl.h ./dburl.h
--- /home/hsc/tmp/postgresql-10beta1/src/bin/psql/dburl.h 1970-01-01
01:00:00.000000000 +0100
+++ ./dburl.h 2017-07-05 09:24:50.394243999 +0200
@@ -0,0 +1,27 @@
+/*
+ * psql - the PostgreSQL interactive terminal
+ *
+ * Copyright (c) 2017, PostgreSQL Global Development Group
+ *
+ * src/bin/psql/dburl.h
+ */
+#ifndef DBURL_H
+#define DBURL_H
+
+typedef struct {
+ char *scheme;
+ char *dsn;
+ char *username;
+ char *password;
+ char *host;
+ char *port;
+ char *dbname;
+ char *table;
+ char *column;
+ char *sql;
+} dburl_t;
+
+extern dburl_t *dburlparse(char *dburltext);
+extern int dburlfree(dburl_t *dburl);
+
+#endif
diff -Naur /home/hsc/tmp/postgresql-10beta1/src/bin/psql/Makefile ./Makefile
--- /home/hsc/tmp/postgresql-10beta1/src/bin/psql/Makefile 2017-05-15
23:20:59.000000000 +0200
+++ ./Makefile 2017-07-05 08:54:26.222243999 +0200
@@ -19,12 +19,12 @@
REFDOCDIR= $(top_srcdir)/doc/src/sgml/ref
override CPPFLAGS := -I. -I$(srcdir) -I$(libpq_srcdir) $(CPPFLAGS)
-LDFLAGS += -L$(top_builddir)/src/fe_utils -lpgfeutils -lpq
+LDFLAGS += -L$(top_builddir)/src/fe_utils -lpgfeutils -lpq -lpcre
OBJS= command.o common.o conditional.o copy.o crosstabview.o \
describe.o help.o input.o large_obj.o mainloop.o \
prompt.o psqlscanslash.o sql_help.o startup.o stringutils.o \
- tab-complete.o variables.o \
+ tab-complete.o variables.o dburl.o \
$(WIN32RES)
diff -Naur /home/hsc/tmp/postgresql-10beta1/src/bin/psql/startup.c ./startup.c
--- /home/hsc/tmp/postgresql-10beta1/src/bin/psql/startup.c 2017-05-15
23:20:59.000000000 +0200
+++ ./startup.c 2017-07-05 14:15:22.155234720 +0200
@@ -24,7 +24,7 @@
#include "mainloop.h"
#include "fe_utils/print.h"
#include "settings.h"
-
+#include "dburl.h"
/*
@@ -654,7 +654,20 @@
*/
while (argc - optind >= 1)
{
- if (!options->dbname)
+ dburl_t *dburlparam;
+ if (NULL != (dburlparam = dburlparse(argv[optind]))) {
+ if (dburlparam->dbname)
+ options->dbname = dburlparam->dbname;
+ if (dburlparam->username)
+ options->username = dburlparam->username;
+ if (dburlparam->host)
+ options->host = dburlparam->host;
+ if (dburlparam->port)
+ options->port = dburlparam->port;
+ //if (dburlparam->password)
+ // strncpy(password, dburlparam->password,
sizeof(password));
+ }
+ else if (!options->dbname)
options->dbname = argv[optind];
else if (!options->username)
options->username = argv[optind];
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
