[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [HACKERS] Are we losing momentum?
From: Sean Chittenden <sean () chittenden ! org>
Date: 2003-04-16 15:45:17
[Download RAW message or body]
> > That's a pretty reasonable thought. I work for a shop that sells
> > Postgres support, and even we install MySQL for the Q&D ticket
> > tracking system we recommend because we can't justify the cost to
> > port it to postgres. If the postgres support were there, we would
> > surely be using it.
>
> > How to fix such a situation, I'm not sure. "MySQL Compatability
> > Mode," anyone? :-)
>
> What issues are creating a compatibility problem for you?
I don't think these should be hacked into the backend/libpq, but I
think it'd be a huge win to hack in "show *" support into psql for
MySQL users so they can type:
SHOW (databases|tables|views|functions|triggers|schemas);
I have yet to meet a MySQL user who understands the concept of system
catalogs even though it's just the 'mysql' database (this irritates me
enough as is)... gah, f- it: mysql users be damned, I have three
developers that think that postgresql is too hard to use because they
can't remember "\d [table name]" and I'm tired of hearing them bitch
when I push using PostgreSQL instead of MySQL. I have better things
to do with my time than convert their output to PostgreSQL. Here goes
nothing...
I've tainted psql and added a MySQL command compatibility layer for
the family of SHOW commands (psql [-m | --mysql]).
The attached patch does a few things:
1) Implements quite a number of SHOW commands (AGGREGATES, CASTS,
CATALOGS, COLUMNS, COMMENTS, CONSTRAINTS, CONVERSIONS, DATABASES,
DOMAINS, FUNCTIONS, HELP, INDEX, LARGEOBJECTS, NAMES, OPERATORS,
PRIVILEGES, PROCESSLIST, SCHEMAS, SEQUENCES, SESSION, STATUS,
TABLES, TRANSACTION, TYPES, USERS, VARIABLES, VIEWS)
SHOW thing
SHOW thing LIKE pattern
SHOW thing FROM pattern
SHOW HELP ON (topic || ALL);
etc.
Some of these don't have \ command eqiv's. :( I was tempted to add
them, but opted not to for now, but it'd certainly be a nice to
have.
2) Implements the necessary tab completion for the SHOW commands for
the tab happy newbies/folks out there. psql is more friendly than
mysql's CLI now in terms of tab completion for the show commands.
3) Few trailing whitespace characters were nuked
4) guc.c is now in sync with the list of available variables used for
tab completion
Few things to note:
1) SHOW INDEXES is the same as SHOW INDEX, I think MySQL is wrong in
this regard and that it should be INDEXES to be plural along with
the rest of the types, but INDEX is preserved for compatibility.
2) There are two bugs that I have yet to address
1) SHOW VARIABLES doesn't work, but "SHOW [TAB][TAB]y" does
2) "SHOW [variable_of_choice];" doesn't work, but "SHOW
[variable_of_choice]\n;" does work... not sure where this
problem is coming from
3) I think psql is more usable as a result of this more verbose
syntax, but it's not the prettiest thing on the planet (wrote a
small parser outside of the backend or libraries: I don't want to
get those dirty with MySQL's filth).
4) In an attempt to wean people over to PostgreSQL's syntax, I
included translation tips on how to use the psql equiv of the SHOW
commands. Going from SHOW foo to \d foo is easy, going from \d foo
to SHOW foo is hard and drives me nuts. This'll help userbase
retention of newbies/converts. :)
5) The MySQL mode is just a bounce layer that provides different
syntax wrapping exec_command() so it should provide little in the
way of maintenance headaches. Some of the SHOW commands, however,
don't have \ couterparts, but once they do and that code is
centralized, this feature should come for zero cost.
6) As an administrator, I'd be interested in having an environment
variable that I could set that'd turn on MySQL mode for some of my
bozo users that way they don't complain if they forget the -m
switch. Thoughts?
I'll try and iron out the last of those two bugs/features, but at this
point, would like to see this patch get wider testing/feedback.
Comments, as always, are welcome.
PostgreSQL_usability++
-sc
--
Sean Chittenden
Index: command.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/command.c,v
retrieving revision 1.95
diff -u -r1.95 command.c
--- command.c 2003/04/04 20:40:45 1.95
+++ command.c 2003/04/16 15:37:53
@@ -66,7 +66,386 @@
static bool do_connect(const char *new_dbname, const char *new_user);
static bool do_shell(const char *command);
+static char *eat_token(char *cp, char *token, size_t token_len);
+static char *eat_whitespace(char *cp);
+static size_t token_len(char *cp);
+
+#define _(x) gettext((x))
+
+/* These macros are a tad evil but saves a gagillion keystrokes and
+ * should only be used in HandleMySQLShowCmds(). */
+#define MYSQL_SHOW_PARSE_OPT_LIKE(cmd, cmd_len) do { \
+ cp = eat_whitespace(&tkn1[tkn1_len]); \
+ cp_pos = 0; \
+ if (cp == NULL) \
+ goto help; \
+ strncpy(my_line, cmd, cmd_len); \
+ tkn2 = eat_token(cp, "LIKE", 4); \
+ tkn2_len = token_len(tkn2); \
+ if (tkn2 != NULL) { \
+ sprintf(&my_line[cmd_len], " %.*s*", tkn2_len, tkn2); \
+ options_string = &my_line[cmd_len]; \
+ } \
+ if (!QUIET()) \
+ fprintf(stderr, "\n\tTIP: In %s, \"%s\" is natively written as \
\"\\%s%.*s%.*s%.*s\"\n\n", getprogname(), line, cmd, (tkn2_len ? 1 : 0), " ", \
tkn2_len, tkn2, (tkn2_len ? 1 : 0), "*"); \ +} while(0)
+
+#define MYSQL_SHOW_COPY_ARG(cmd, cmd_len) do { \
+ strncpy(my_line, cmd, cmd_len); \
+ if (!QUIET()) { \
+ fprintf(stderr, "\n\tTIP: In %s, \"%s\" is natively written as \"\\%s\"\n\n", \
getprogname(), line, cmd); \ + } \
+} while(0)
+
/*----------
+ * HandleMySQLShowCmds:
+ *
+ * Handles all the different "SHOW" commands while in MySQL
+ * compatibility mode, ordinarily called by MainLoop().
+ *
+ * This function is a ripoff of HandleSlashCmds() and acts as a
+ * translation layer between the user input and exec_command().
+ *
+ * 'query_buf' contains the query-so-far, which may be modified by
+ * execution of the backslash command (for example, \r clears it)
+ * query_buf can be NULL if there is no query so far.
+ *
+ * Returns a status code indicating what action is desired, see command.h.
+ *----------
+ */
+
+backslashResult
+HandleMySQLShowCmds(const char *line,
+ PQExpBuffer query_buf,
+ const char **end_of_cmd,
+ volatile int *paren_level)
+{
+ backslashResult status = CMD_SKIP_LINE;
+ bool run_cmd;
+ char *cp, *my_line, *tkn1, *tkn2;
+ char *options_string = NULL;
+ size_t cp_pos, tkn1_len, tkn2_len;
+ const char *continue_parse = NULL; /* tell the mainloop where the
+ * backslash command ended */
+
+#ifdef USE_ASSERT_CHECKING
+ assert(line);
+#endif
+
+ my_line = xstrdup(line);
+ run_cmd = true;
+ cp_pos = strlen(line);
+ continue_parse = &line[cp_pos];
+ if (end_of_cmd != NULL)
+ *end_of_cmd = &line[cp_pos];
+
+ /*
+ * Find the thing that we're showing
+ */
+ cp = tkn1 = eat_token(my_line, "SHOW", 4);
+ tkn1_len = token_len(tkn1);
+ if (cp == NULL) {
+ status = CMD_UNKNOWN;
+ goto error;
+ }
+
+ if (cp[0] == '\0') {
+ help:
+ MYSQL_SHOW_COPY_ARG("?", 2);
+ } else if (strncasecmp(tkn1, "AGGREGATES", tkn1_len) == 0) {
+ /* SHOW AGGREGATES [LIKE pattern] */
+ /* \da [PATTERN] list aggregate functions */
+ MYSQL_SHOW_PARSE_OPT_LIKE("da", 3);
+ } else if (strncasecmp(tkn1, "CASTS", tkn1_len) == 0) {
+ /* SHOW CASTS */
+ /* \dC list casts */
+ MYSQL_SHOW_COPY_ARG("dC", 3);
+ } else if (strncasecmp(tkn1, "CATALOGS", tkn1_len) == 0) {
+ /* SHOW CATALOGS [LIKE pattern] */
+ /* \dS lists system catalogs */
+ MYSQL_SHOW_PARSE_OPT_LIKE("dS", 3);
+ } else if (strncasecmp(tkn1, "COLUMNS", tkn1_len) == 0) {
+ /* SHOW COLUMNS FROM tbl_name */
+ /* \dt [PATTERN] list columns in a table */
+ cp = eat_whitespace(&tkn1[tkn1_len]);
+ cp_pos = 0;
+ if (cp == NULL)
+ goto help;
+
+ /* Skip past the token word FROM and return the next token */
+ cp = tkn2 = eat_token(cp, "FROM", 4);
+ tkn2_len = token_len(tkn2);
+ if (tkn2 == NULL || tkn2[0] == '\0')
+ goto help;
+
+ /* Abuse my_line since my_line is always shorter than the string passed in */
+ strncpy(my_line, "d", 2);
+ sprintf(&my_line[2], "%.*s", tkn2_len, tkn2);
+ options_string = &my_line[2];
+ if (!QUIET())
+ fprintf(stderr, "\n\tTIP: In %s, \"%s\" is natively written as \
\"\\%s%.*s%.*s\"\n\n", getprogname(), line, "d", (tkn2_len ? 1 : 0), " ", tkn2_len, \
tkn2); + } else if (strncasecmp(tkn1, "COMMENTS", tkn1_len) == 0) {
+ /* SHOW COMMENTS [LIKE pattern] */
+ /* \dd [PATTERN] show comment for object */
+ MYSQL_SHOW_PARSE_OPT_LIKE("dd", 3);
+ } else if (strncasecmp(tkn1, "CONVERSIONS", tkn1_len) == 0) {
+ /* SHOW CONVERSIONS [LIKE pattern] */
+ /* \dc [PATTERN] list conversions */
+ MYSQL_SHOW_COPY_ARG("dc", 3);
+ } else if (strncasecmp(tkn1, "DATABASES", tkn1_len) == 0) {
+ /* \l list all databases */
+ MYSQL_SHOW_COPY_ARG("l", 2);
+ } else if (strncasecmp(tkn1, "DOMAINS", tkn1_len) == 0) {
+ /* SHOW DOMAINS [LIKE pattern] */
+ /* \dD [PATTERN] list domains */
+ MYSQL_SHOW_PARSE_OPT_LIKE("dD", 3);
+ } else if (strncasecmp(tkn1, "FUNCTIONS", tkn1_len) == 0) {
+ /* SHOW FUNCTIONS [LIKE pattern] */
+ /* \df [PATTERN] list functions */
+ MYSQL_SHOW_PARSE_OPT_LIKE("df", 3);
+ } else if (strncasecmp(tkn1, "HELP", tkn1_len) == 0) {
+ /* SHOW HELP [ON (function_name|ALL) ]*/
+ /* \h [NAME] help on syntax of SQL commands, * for all commands */
+ strncpy(my_line, "h", 2);
+ cp = eat_whitespace(&tkn1[tkn1_len]);
+ cp_pos = 0;
+ if (cp == NULL)
+ goto help;
+
+ /* Skip past the token word FROM and return the next token */
+ cp = tkn2 = eat_token(cp, "ON", 2);
+ tkn2_len = token_len(tkn2);
+ if (tkn2 == NULL || tkn2[0] == '\0') {
+ strncpy(my_line, "h", 2);
+ } else {
+ strncpy(my_line, "h", 2);
+ if (strncasecmp(tkn2, "ALL", 3) == 0) {
+ strncpy(&my_line[2], " *", 3);
+ tkn2 = &my_line[3];
+ } else
+ sprintf(&my_line[2], " %.*s", tkn2_len, tkn2);
+
+ options_string = &my_line[3];
+ }
+ if (!QUIET())
+ fprintf(stderr, "\n\tTIP: In %s, \"%s\" is natively written as \
\"\\%s%.*s%.*s\"\n\n", getprogname(), line, "h", (tkn2_len ? 1 : 0), " ", tkn2_len, \
tkn2); + } else if (strncasecmp(tkn1, "INDEXES", tkn1_len) == 0 ||
+ strncasecmp(tkn1, "INDEX", tkn1_len) == 0) {
+ /* SHOW INDEX (FROM table_name|LIKE pattern) */
+ /* FROM = query with arg comparing to table names. LIKE = query with arg \
comparing to index names */ + PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ cp = eat_whitespace(&tkn1[tkn1_len]);
+ cp_pos = 0;
+ if (cp == NULL)
+ goto help;
+
+ if (strncasecmp(cp, "FROM", 4) == 0) {
+ cp = tkn2 = eat_token(cp, "FROM", 4);
+
+ tkn2_len = token_len(tkn2);
+ if (tkn2 == NULL || tkn2[0] == '\0')
+ goto help;
+
+ initPQExpBuffer(&buf);
+ /* Ned to populate the buffer */
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname AS \"Schema\", c2.relname AS \"Table\", c.relname AS \
\"Name\",\n" + " u.usename AS \"Owner\", i.indisclustered AS \"Clustered\", \
i.indisunique AS \"Unique\",\n" + " i.indisprimary AS \"Primary\"\n"
+ " FROM pg_catalog.pg_class c\n"
+ " JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n"
+ " JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid\n"
+ " LEFT JOIN pg_catalog.pg_user u ON u.usesysid= c.relowner\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
+ " WHERE c.relkind IN ('i','') AND pg_catalog.pg_table_is_visible(c.oid)\n"
+ " AND c2.relname ~ '^%.*s' ORDER BY c2.relname, c.relname",
+ tkn2_len, tkn2);
+
+ } else if (strncasecmp(cp, "LIKE", 4) == 0) {
+ cp = tkn2 = eat_token(cp, "LIKE", 4);
+
+ tkn2_len = token_len(tkn2);
+ if (tkn2 == NULL || tkn2[0] == '\0')
+ goto help;
+
+ initPQExpBuffer(&buf);
+
+ /* Ned to populate the buffer */
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname AS \"Schema\", c2.relname AS \"Table\", c.relname AS \
\"Name\",\n" + " u.usename AS \"Owner\", i.indisclustered AS \"Clustered\", \
i.indisunique AS \"Unique\",\n" + " i.indisprimary AS \"Primary\"\n"
+ " FROM pg_catalog.pg_class c\n"
+ " JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n"
+ " JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid\n"
+ " LEFT JOIN pg_catalog.pg_user u ON u.usesysid= c.relowner\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
+ " WHERE c.relkind IN ('i','') AND pg_catalog.pg_table_is_visible(c.oid)\n"
+ " AND c.relname ~ '^%.*s' ORDER BY c.relname",
+ tkn2_len, tkn2);
+ } else
+ goto help;
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(&buf);
+ if (!res)
+ goto error;
+
+ if (PQntuples(res) == 0 && !QUIET())
+ fprintf(pset.queryFout, _("No matching indexes found.\n"));
+ else
+ {
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of indexes");
+
+ printQuery(res, &myopt, pset.queryFout);
+ }
+
+ PQclear(res);
+ run_cmd = false;
+ options_string = &my_line[2];
+ } else if (strncasecmp(tkn1, "LARGEOBJECTS", tkn1_len) == 0) {
+ /* SHOW LARGEOBJECTS */
+ /* \dl list large objects */
+ MYSQL_SHOW_COPY_ARG("dl", 3);
+ } else if (strncasecmp(tkn1, "OPERATORS", tkn1_len) == 0) {
+ /* SHOW OPERATORS [LIKE name] */
+ /* \do [NAME] list operators */
+ MYSQL_SHOW_PARSE_OPT_LIKE("do", 3);
+ } else if (strncasecmp(tkn1, "PRIVILEGES", tkn1_len) == 0) {
+ /* SHOW PRIVILEGES (ON name|LIKE pattern) */
+ /* \dp [PATTERN] list table access privileges */
+ MYSQL_SHOW_PARSE_OPT_LIKE("dp", 3);
+ } else if (strncasecmp(tkn1, "PROCESSLIST", tkn1_len) == 0) {
+ /* SHOW PROCESSLIST */
+ /* SELECT * FROM pg_catalog.pg_stat_activity ; */
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT a.datname AS \"Database\", a.procpid AS \"PID\", a.usename AS \
\"Username\",\n" + " a.current_query AS \"Current Query\" FROM \
pg_catalog.pg_stat_activity a"); +
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(&buf);
+ if (!res)
+ goto error;
+
+ if (PQntuples(res) == 0 && !QUIET())
+ fprintf(pset.queryFout, _("No stats available.\n"));
+ else
+ {
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of processes");
+
+ printQuery(res, &myopt, pset.queryFout);
+ }
+
+ PQclear(res);
+ run_cmd = false;
+ options_string = &my_line[2];
+ } else if (strncasecmp(tkn1, "SCHEMAS", tkn1_len) == 0) {
+ /* SHOW SCHEMAS [LIKE pattern] */
+ /* \dn [PATTERN] list schemas */
+ MYSQL_SHOW_PARSE_OPT_LIKE("dn", 3);
+ } else if (strncasecmp(tkn1, "SEQUENCES", tkn1_len) == 0) {
+ /* SHOW SEQUENCES [LIKE pattern] */
+ /* ds [PATTERN] list sequences */
+ MYSQL_SHOW_PARSE_OPT_LIKE("ds", 3);
+ } else if (strncasecmp(tkn1, "STATUS", tkn1_len) == 0) {
+ /* SHOW STATUS */
+ /* SELECT * FROM pg_catalog.pg_stat_database ; */
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT d.datname AS \"Database\", d.numbackends AS \"Num Backends\",\n"
+ " d.xact_commit AS \"Txn Commit\", d.xact_rollback AS \"Txn Rollback\",\n"
+ " d.blks_read AS \"Blocks Read\", d.blks_hit AS \"Blocks Hit\"\n"
+ " FROM pg_catalog.pg_stat_database d");
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(&buf);
+ if (!res)
+ goto error;
+
+ if (PQntuples(res) == 0 && !QUIET())
+ fprintf(pset.queryFout, _("No status available.\n"));
+ else
+ {
+ myopt.nullPrint = NULL;
+ myopt.title = _("Status");
+
+ printQuery(res, &myopt, pset.queryFout);
+ }
+
+ PQclear(res);
+ run_cmd = false;
+ options_string = &my_line[2];
+ } else if (strncasecmp(tkn1, "TABLES", tkn1_len) == 0) {
+ /* SHOW TABLES [LIKE pattern] */
+ /* \dt [PATTERN] list tables */
+ MYSQL_SHOW_PARSE_OPT_LIKE("dt", 3);
+ } else if (strncasecmp(tkn1, "TYPES", tkn1_len) == 0) {
+ /* SHOW TYPES [LIKE pattern] */
+ /* \dT [PATTERN] list data types */
+ MYSQL_SHOW_PARSE_OPT_LIKE("dT", 3);
+ } else if (strncasecmp(tkn1, "USERS", tkn1_len) == 0) {
+ /* SHOW USERS [LIKE pattern] */
+ /* \du [PATTERN] list users */
+ MYSQL_SHOW_PARSE_OPT_LIKE("du", 3);
+ } else if (strncasecmp(tkn1, "VARIABLES", tkn1_len) == 0) {
+ /* INCOMPLETE - XXXXXXXXXXXXXXXXXX
+ * Pushing "SHOW [TAB][TAB]y" however, does work */
+ /* SHOW VARIABLES */
+ /* Need to iterate through the list of variables that would
+ * normally be shown via "SHOW [TAB][TAB]" when not in mysql
+ * compatibility mode. This mode will be an oddity in that
+ * it won't return normal results and will look a tad funky,
+ * I think. This could iterate through the list of
+ * variables stored in *pgsql_variables[] and show their
+ * values. */
+ MYSQL_SHOW_COPY_ARG("?", 2);
+ } else if (strncasecmp(tkn1, "VIEWS", tkn1_len) == 0) {
+ /* SHOW VIEWS [LIKE pattern] */
+ /* \dv [PATTERN list views */
+ MYSQL_SHOW_PARSE_OPT_LIKE("dv", 3);
+ } else {
+ /* SHOW [variable_name] */
+ /* This is syntatically the same the normal SHOW syntax
+ * without MySQL compatibility mode turned on. */
+ /* BUG XXXXXXXXXXXXXX This currently works, but in an odd
+ * manner, and why, I'm not sure.
+ *
+ * "SHOW TimeZone;" Doesn't work, but
+ * "SHOW TimeZone\n;" Does work.
+ */
+ strncpy(my_line, "SHOW", 5);
+ tkn2 = eat_whitespace(&tkn1[tkn1_len]);
+ tkn2_len = token_len(tkn2);
+ strncpy(&my_line[6], tkn2, tkn2_len);
+ options_string = tkn2;
+ }
+
+ if (run_cmd == true)
+ status = exec_command(my_line, options_string, &continue_parse, query_buf, \
paren_level); +
+ error:
+ free(my_line);
+ query_buf->len = 0;
+
+ return status;
+}
+
+
+/*----------
* HandleSlashCmds:
*
* Handles all the different commands that start with '\',
@@ -1726,6 +2105,48 @@
break;
}
return "unknown";
+}
+
+
+static char *eat_token(char *cp, char *token, size_t token_len) {
+ size_t cp_pos;
+ char *p;
+
+ p = cp;
+ /* Skip past the token */
+ cp_pos = strncasecmp(p, token, token_len);
+ if (cp_pos != 0)
+ return(NULL);
+
+ /* Skip past the spaces after the token */
+ p = eat_whitespace(&p[token_len]);
+ return(p);
+}
+
+static char *eat_whitespace(char *cp) {
+ size_t cp_pos;
+
+ if (cp == NULL)
+ return(NULL);
+
+ for (cp_pos = 0; cp[cp_pos] != NULL; cp_pos++)
+ if (!isspace(cp[cp_pos]))
+ break;
+
+ return(&cp[cp_pos]);
+}
+
+static size_t token_len(char *cp) {
+ size_t cp_pos;
+
+ if (cp == NULL)
+ return((size_t)0);
+
+ for (cp_pos = 0; cp[cp_pos] != NULL; cp_pos++)
+ if (isspace(cp[cp_pos]))
+ break;
+
+ return(cp_pos);
}
Index: command.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/command.h,v
retrieving revision 1.14
diff -u -r1.14 command.h
--- command.h 2002/03/27 19:16:13 1.14
+++ command.h 2003/04/16 15:37:53
@@ -26,6 +26,11 @@
} backslashResult;
+backslashResult HandleMySQLShowCmds(const char *line,
+ PQExpBuffer query_buf,
+ const char **end_of_cmd,
+ volatile int *paren_level);
+
backslashResult HandleSlashCmds(const char *line,
PQExpBuffer query_buf,
const char **end_of_cmd,
Index: help.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/help.c,v
retrieving revision 1.72
diff -u -r1.72 help.c
--- help.c 2003/04/14 16:23:36 1.72
+++ help.c 2003/04/16 15:37:54
@@ -97,6 +97,7 @@
puts(_(" -v NAME=VALUE set psql variable 'NAME' to 'VALUE'"));
puts(_(" -X do not read startup file (~/.psqlrc)"));
puts(_(" --help show this help, then exit"));
+ puts(_(" --mysql, -m Run in quasi-MySQL compatibility mode"));
puts(_(" --version output version information, then exit"));
puts(_("\nInput and output options:"));
Index: mainloop.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/mainloop.c,v
retrieving revision 1.55
diff -u -r1.55 mainloop.c
--- mainloop.c 2003/03/21 03:28:29 1.55
+++ mainloop.c 2003/04/16 15:37:54
@@ -274,7 +274,7 @@
else if (line[i] == '/' && line[i + thislen] == '*')
{
in_xcomment++;
- if (in_xcomment == 1)
+ if (in_xcomment == 1)
ADVANCE_1;
}
@@ -404,11 +404,12 @@
}
/* backslash command */
- else if (bslash_count)
+ else if (bslash_count || (pset.mode_mysql == true && i > 0 && strncasecmp(&line[i \
- 1], "SHOW", 4) == 0)) {
const char *end_of_cmd = NULL;
- line[i - prevlen] = '\0'; /* overwrites backslash */
+ if (bslash_count)
+ line[i - prevlen] = '\0'; /* overwrites backslash */
/* is there anything else on the line for the command? */
if (line[query_start + strspn(line + query_start, " \t\n\r")] != '\0')
@@ -423,9 +424,15 @@
appendPQExpBufferStr(query_buf, line + query_start);
}
- /* handle backslash command */
- slashCmdStatus = HandleSlashCmds(&line[i],
- query_buf->len > 0 ? query_buf : previous_buf,
+ /* Handle backslash command/MySQL compatibility */
+ if (pset.mode_mysql == true && strncasecmp(&line[i - 1], "SHOW", 4) == 0)
+ slashCmdStatus = HandleMySQLShowCmds(&line[i - 1],
+ query_buf->len > 0 ? query_buf : previous_buf,
+ &end_of_cmd,
+ &paren_level);
+ else
+ slashCmdStatus = HandleSlashCmds(&line[i],
+ query_buf->len > 0 ? query_buf : previous_buf,
&end_of_cmd,
&paren_level);
Index: settings.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/settings.h,v
retrieving revision 1.13
diff -u -r1.13 settings.h
--- settings.h 2002/03/05 00:01:02 1.13
+++ settings.h 2003/04/16 15:37:54
@@ -51,6 +51,7 @@
bool issuper; /* is the current user a superuser? (used
* to form the prompt) */
bool timing; /* timing of all queries */
+ bool mode_mysql; /* MySQL command compatibility (show commands) */
} PsqlSettings;
extern PsqlSettings pset;
Index: startup.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/startup.c,v
retrieving revision 1.73
diff -u -r1.73 startup.c
--- startup.c 2003/04/04 20:42:13 1.73
+++ startup.c 2003/04/16 15:37:54
@@ -128,6 +128,7 @@
pset.cur_cmd_source = stdin;
pset.cur_cmd_interactive = false;
+ pset.mode_mysql = false;
pset.encoding = PQenv2encoding();
pset.vars = CreateVariableSpace();
@@ -330,6 +331,7 @@
{"host", required_argument, NULL, 'h'},
{"html", no_argument, NULL, 'H'},
{"list", no_argument, NULL, 'l'},
+ {"mysql", no_argument, NULL, 'm'},
{"no-readline", no_argument, NULL, 'n'},
{"output", required_argument, NULL, 'o'},
{"port", required_argument, NULL, 'p'},
@@ -359,7 +361,7 @@
memset(options, 0, sizeof *options);
- while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:Hlno:p:P:qR:sStT:uU:v:VWxX?",
+ while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:Hlmno:p:P:qR:sStT:uU:v:VWxX?",
long_options, &optindex)) != -1)
{
switch (c)
@@ -404,6 +406,9 @@
break;
case 'l':
options->action = ACT_LIST_DB;
+ break;
+ case 'm':
+ pset.mode_mysql = true;
break;
case 'n':
options->no_readline = true;
Index: tab-complete.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/tab-complete.c,v
retrieving revision 1.76
diff -u -r1.76 tab-complete.c
--- tab-complete.c 2003/04/03 20:18:16 1.76
+++ tab-complete.c 2003/04/16 15:37:55
@@ -85,8 +85,11 @@
static char *complete_from_list(const char *text, int state);
static PGresult *exec_query(char *query);
+static bool find_word_in_list(char **word_list, int *valid_indexes, char *word);
char *quote_file_name(char *text, int match_type, char *quote_pointer);
+static char **merge_lists(char **list1, char **list2);
+
/*static char * dequote_file_name(char *text, char quote_char);*/
static char *previous_word(int point, int skip);
@@ -435,7 +438,178 @@
{NULL, NO_SCHEMA, NULL} /* end of list */
};
+char *sql_commands[] = {
+ "ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE", "CLUSTER", "COMMENT",
+ "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE", "DROP", "EXECUTE",
+ "EXPLAIN", "FETCH", "GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY",
+ "PREPARE", "REINDEX", "RESET", "REVOKE", "ROLLBACK", "SELECT", "SET", "SHOW",
+ "TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", NULL
+};
+
+char **mysql_mode_show_merged = NULL;
+
+char *mysql_mode_show_words[] = {
+ "AGGREGATES", "CASTS", "CATALOGS", "COLUMNS", "COMMENTS", "CONVERSIONS", \
"DATABASES", + "DOMAINS", "FUNCTIONS", "HELP", "INDEXES", "LARGEOBJECTS", \
"OPERATORS", "PRIVILEGES", + "PROCESSLIST", "SCHEMAS", "SEQUENCES", "STATUS", \
"TABLES", "TYPES", "USERS", + "VARIABLES", "VIEWS", NULL
+};
+
+int mysql_mode_show_valid_like_commands[] = {
+ 0, 2, 4, 5, 7, 8, 10, 12, 13, 15, 16, 18, 19, 20, 22, -1
+};
+char *mysql_mode_show_select_origin_words[] = {
+ "FROM", "LIKE", NULL
+};
+
+int mysql_mode_show_valid_from_like[] = {
+ 10, -1
+};
+
+char *pgsql_variables[] = {
+ /* these SET arguments are known in gram.y */
+ "CONSTRAINTS",
+ "NAMES",
+ "SESSION",
+ "TRANSACTION",
+
+ /*
+ * the rest should match USERSET and possibly SUSET entries in
+ * backend/utils/misc/guc.c.
+ */
+ "australian_timezones",
+ "authentication_timeout",
+ "autocommit",
+ "checkpoint_segments",
+ "checkpoint_timeout",
+ "checkpoint_warning",
+ "client_encoding",
+ "client_min_messages",
+ "commit_delay",
+ "commit_siblings",
+ "cpu_index_tuple_cost",
+ "cpu_operator_cost",
+ "cpu_tuple_cost",
+ "DateStyle",
+ "db_user_namespace",
+ "deadlock_timeout",
+#ifdef USE_ASSERT_CHECKING
+ "debug_assertions",
+#endif
+ "debug_pretty_print",
+ "debug_print_parse",
+ "debug_print_plan",
+ "debug_print_rewritten",
+#ifdef LOCK_DEBUG
+ "debug_deadlocks",
+#endif
+ "default_statistics_target",
+ "default_transaction_isolation",
+ "default_transaction_read_only",
+ "dynamic_library_path",
+ "effective_cache_size",
+ "enable_hashagg",
+ "enable_hashjoin",
+ "enable_indexscan",
+ "enable_mergejoin",
+ "enable_nestloop",
+ "enable_seqscan",
+ "enable_sort",
+ "enable_tidscan",
+ "explain_pretty_print",
+ "extra_float_digits",
+ "from_collapse_limit",
+ "fsync",
+ "geqo",
+ "geqo_effort",
+ "geqo_generations",
+ "geqo_pool_size",
+ "geqo_random_seed",
+ "geqo_selection_bias",
+ "geqo_threshold",
+ "join_collapse_limit",
+ "krb_server_keyfile",
+ "lc_messages",
+ "lc_monetary",
+ "lc_numeric",
+ "lc_time",
+#ifdef BTREE_BUILD_STATS
+ "log_btree_build_stats",
+#endif
+ "log_connections",
+ "log_duration",
+ "log_executor_stats",
+ "log_hostname",
+ "log_min_error_statement",
+ "log_min_messages",
+ "log_parser_stats",
+ "log_pid",
+ "log_planner_stats",
+ "log_source_port",
+ "log_statement",
+ "log_statement_stats",
+ "log_timestamp",
+ "max_connections",
+ "max_expr_depth",
+ "max_files_per_process",
+ "max_fsm_pages",
+ "max_fsm_relations",
+ "max_locks_per_transaction",
+ "password_encryption",
+ "pre_auth_delay",
+ "preload_libraries",
+ "port",
+ "random_page_cost",
+ "regex_flavor",
+ "search_path",
+ "server_encoding",
+ "silent_mode",
+ "shared_buffers",
+ "seed",
+ "server_encoding",
+ "session_authorization",
+ "shared_buffers",
+ "sort_mem",
+ "sql_inheritance",
+ "ssl",
+ "statement_timeout",
+ "stats_block_level",
+ "stats_command_string",
+ "stats_reset_on_server_start",
+ "stats_row_level",
+ "stats_start_collector",
+ "superuser_reserved_connections",
+#ifdef HAVE_SYSLOG
+ "syslog",
+ "syslog_facility",
+ "syslog_ident",
+#endif
+ "tcpip_socket",
+ "TimeZone",
+ "trace_notify",
+#ifdef LOCK_DEBUG
+ "trace_lock_oidmin",
+ "trace_lock_table",
+ "trace_locks",
+ "trace_lwlocks",
+ "trace_userlocks",
+#endif
+ "transaction_isolation",
+ "transaction_read_only",
+ "transform_null_equals",
+ "unix_socket_directory",
+ "unix_socket_group",
+ "unix_socket_permissions",
+ "vacuum_mem",
+ "virtual_host",
+ "wal_buffers",
+ "wal_debug",
+ "wal_sync_method",
+ "zero_damaged_pages",
+ NULL
+};
+
/* A couple of macros to ease typing. You can use these to complete the given
string with
1) The results from a query you pass it. (Perhaps one of those above?)
@@ -473,123 +647,10 @@
*prev3_wd,
*prev4_wd;
- static char *sql_commands[] = {
- "ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE", "CLUSTER", "COMMENT",
- "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE", "DROP", "EXECUTE",
- "EXPLAIN", "FETCH", "GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY",
- "PREPARE", "REINDEX", "RESET", "REVOKE", "ROLLBACK", "SELECT", "SET", "SHOW",
- "TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", NULL
- };
-
- static char *pgsql_variables[] = {
- /* these SET arguments are known in gram.y */
- "CONSTRAINTS",
- "NAMES",
- "SESSION",
- "TRANSACTION",
-
- /*
- * the rest should match USERSET and possibly SUSET entries in
- * backend/utils/misc/guc.c.
- */
- "australian_timezones",
- "autocommit",
- "client_encoding",
- "client_min_messages",
- "commit_delay",
- "commit_siblings",
- "cpu_index_tuple_cost",
- "cpu_operator_cost",
- "cpu_tuple_cost",
- "DateStyle",
- "deadlock_timeout",
- "debug_pretty_print",
- "debug_print_parse",
- "debug_print_plan",
- "debug_print_rewritten",
- "default_statistics_target",
- "default_transaction_isolation",
- "default_transaction_read_only",
- "dynamic_library_path",
- "effective_cache_size",
- "enable_hashagg",
- "enable_hashjoin",
- "enable_indexscan",
- "enable_mergejoin",
- "enable_nestloop",
- "enable_seqscan",
- "enable_sort",
- "enable_tidscan",
- "explain_pretty_print",
- "extra_float_digits",
- "from_collapse_limit",
- "fsync",
- "geqo",
- "geqo_effort",
- "geqo_generations",
- "geqo_pool_size",
- "geqo_random_seed",
- "geqo_selection_bias",
- "geqo_threshold",
- "join_collapse_limit",
- "krb_server_keyfile",
- "lc_messages",
- "lc_monetary",
- "lc_numeric",
- "lc_time",
- "log_duration",
- "log_executor_stats",
- "log_min_error_statement",
- "log_min_messages",
- "log_parser_stats",
- "log_planner_stats",
- "log_statement",
- "log_statement_stats",
- "max_connections",
- "max_expr_depth",
- "max_files_per_process",
- "max_fsm_pages",
- "max_fsm_relations",
- "max_locks_per_transaction",
- "password_encryption",
- "port",
- "random_page_cost",
- "regex_flavor",
- "search_path",
- "shared_buffers",
- "seed",
- "server_encoding",
- "sort_mem",
- "sql_inheritance",
- "ssl",
- "statement_timeout",
- "stats_block_level",
- "stats_command_string",
- "stats_reset_on_server_start",
- "stats_row_level",
- "stats_start_collector",
- "superuser_reserved_connections",
- "syslog",
- "syslog_facility",
- "syslog_ident",
- "tcpip_socket",
- "TimeZone",
- "trace_notify",
- "transform_null_equals",
- "unix_socket_directory",
- "unix_socket_group",
- "unix_socket_permissions",
- "vacuum_mem",
- "wal_buffers",
- "wal_debug",
- "wal_sync_method",
- NULL
- };
-
static char *backslash_commands[] = {
- "\\a", "\\connect", "\\C", "\\cd", "\\copy", "\\copyright",
+ "\\a", "\\connect", "\\C", "\\cd", "\\copy", "\\copyright",
"\\d", "\\da", "\\dc", "\\dC", "\\dd", "\\dD", "\\df", "\\di",
- "\\dl", "\\dn", "\\do", "\\dp", "\\ds", "\\dS", "\\dt", "\\dT",
+ "\\dl", "\\dn", "\\do", "\\dp", "\\ds", "\\dS", "\\dt", "\\dT",
"\\dv", "\\du",
"\\e", "\\echo", "\\encoding",
"\\f", "\\g", "\\h", "\\help", "\\H", "\\i", "\\l",
@@ -630,13 +691,13 @@
/* CREATE or DROP but not ALTER TABLE sth DROP */
/* complete with something you can create or drop */
- else if (strcasecmp(prev_wd, "CREATE") == 0 ||
+ else if (strcasecmp(prev_wd, "CREATE") == 0 ||
(strcasecmp(prev_wd, "DROP") == 0 &&
strcasecmp(prev3_wd,"TABLE") != 0 ))
matches = completion_matches(text, create_command_generator);
/* ALTER */
- /* complete with what you can alter (TABLE, GROUP, USER, ...)
+ /* complete with what you can alter (TABLE, GROUP, USER, ...)
* unless we're in ALTER TABLE sth ALTER*/
else if (strcasecmp(prev_wd, "ALTER") == 0 &&
strcasecmp(prev3_wd, "TABLE") != 0 )
@@ -695,7 +756,7 @@
}
/* If we have TABLE <sth> DROP COLUMN, provide list of columns */
else if (strcasecmp(prev4_wd, "TABLE") == 0 &&
- strcasecmp(prev2_wd, "DROP") == 0 &&
+ strcasecmp(prev2_wd, "DROP") == 0 &&
strcasecmp(prev_wd, "COLUMN") == 0)
COMPLETE_WITH_ATTR(prev3_wd);
@@ -983,7 +1044,7 @@
"SELECT 'SCHEMA' AS relname ");
/* Complete "GRANT/REVOKE * ON * " with "TO" */
- else if ((strcasecmp(prev4_wd, "GRANT") == 0 ||
+ else if ((strcasecmp(prev4_wd, "GRANT") == 0 ||
strcasecmp(prev4_wd, "REVOKE") == 0) &&
strcasecmp(prev2_wd, "ON") == 0)
{
@@ -1102,6 +1163,42 @@
/* SET, RESET, SHOW */
/* Complete with a variable name */
+ else if (pset.mode_mysql == true &&
+ (strcasecmp(prev3_wd, "SHOW") == 0 &&
+ find_word_in_list(mysql_mode_show_words, mysql_mode_show_valid_like_commands, \
prev2_wd) == true && + strncasecmp(prev_wd, "LIKE", 4) == 0))
+ {
+ if (strncasecmp(prev2_wd, "INDEX", 5) == 0)
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
+ else
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+ }
+ else if (pset.mode_mysql == true && (strcasecmp(prev2_wd, "SHOW") == 0 &&
+ find_word_in_list(mysql_mode_show_words, mysql_mode_show_valid_from_like, \
prev_wd) == true)) + COMPLETE_WITH_LIST(mysql_mode_show_select_origin_words);
+ else if (pset.mode_mysql == true && (strcasecmp(prev2_wd, "SHOW") == 0 &&
+ find_word_in_list(mysql_mode_show_words, \
mysql_mode_show_valid_like_commands, prev_wd) == true)) \
+ COMPLETE_WITH_CONST("LIKE"); + else if (pset.mode_mysql == true && \
(strcasecmp(prev2_wd, "SHOW") == 0 && + strcasecmp(prev_wd, "COLUMNS") == \
0)) + COMPLETE_WITH_CONST("FROM");
+ else if (pset.mode_mysql == true && (strcasecmp(prev2_wd, "SHOW") == 0 &&
+ strcasecmp(prev_wd, "HELP") == 0))
+ COMPLETE_WITH_CONST("ON");
+ else if (pset.mode_mysql == true && (strcasecmp(prev3_wd, "SHOW") == 0 &&
+ strcasecmp(prev2_wd, "HELP") == 0 &&
+ strcasecmp(prev_wd, "ON") == 0))
+ COMPLETE_WITH_LIST(sql_commands);
+ else if (pset.mode_mysql == true && strcasecmp(prev_wd, "SHOW") == 0)
+ {
+ if (mysql_mode_show_merged == NULL)
+ mysql_mode_show_merged = merge_lists(mysql_mode_show_words, pgsql_variables);
+
+ if (mysql_mode_show_merged != NULL)
+ COMPLETE_WITH_LIST(mysql_mode_show_merged);
+ else
+ COMPLETE_WITH_LIST(mysql_mode_show_words);
+ }
else if ((strcasecmp(prev_wd, "SET") == 0 &&
strcasecmp(prev3_wd, "UPDATE") != 0) ||
strcasecmp(prev_wd, "RESET") == 0 ||
@@ -1399,9 +1496,9 @@
/* This creates a list of matching things, according to a query pointed to
by completion_charp.
The query can be one of two kinds:
- - A simple query which must contain a %d and a %s, which will be replaced
+ - A simple query which must contain a %d and a %s, which will be replaced
by the string length of the text and the text itself. The query may also
- have another %s in it, which will be replaced by the value of
+ have another %s in it, which will be replaced by the value of
completion_info_charp.
or:
- A schema query used for completion of both schema and relation names;
@@ -1658,6 +1755,46 @@
return s;
}
+
+
+static bool
+find_word_in_list(char **word_list, int *valid_indexes, char *word)
+{
+ size_t i;
+
+ for (i = 0; valid_indexes[i] != -1; i++)
+ if (strcasecmp(word_list[valid_indexes[i]], word) == 0)
+ return(true);
+
+ return(false);
+}
+
+static char **
+merge_lists(char **list1, char **list2)
+{
+ size_t i, list1_len, list2_len;
+ char *p;
+ char **newlist;
+
+ for (list1_len = 0; list1[list1_len] != NULL; list1_len++)
+ p = list1[list1_len];
+
+ for (list2_len = 0; list2[list2_len] != NULL; list2_len++)
+ p = list2[list2_len];
+
+ newlist = malloc(sizeof(char *) * (list1_len + list2_len));
+ if (newlist == NULL)
+ return(NULL);
+
+ for (list1_len = 0, i = 0; list1[list1_len] != NULL; list1_len++, i++)
+ newlist[i] = list1[list1_len];
+
+ for (list2_len = 0; list2[list2_len] != NULL; list2_len++, i++)
+ newlist[i] = list2[list2_len];
+
+ newlist[i] = NULL;
+ return(newlist);
+}
#if 0
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic