MySQL-ism help patch for psql
Hey -hackers,
I whipped up a quick patch for supporting some of the common mysql-
based "meta" commands; this is different than some things which have
been discussed in the past, in that it provides just a quick direction
to the appropriate psql command, not an actual alternative syntax for
the same action. This is not intended to be comprehensive, but just
to provide proper direction
The changes are in a single hunk touching only src/bin/psql/
mainloop.c; I modeled the code against the logic currently in place
for the "help" command.
First postgres patch, so bring it on^W^W^Wbe gentle. I obviously
don't expect this to not promote a wild debate/flamewar... ;-) The
formatting and specific wording for the various messages are totally
up-in-the-air, and gladly up for debate.
Regards,
David
--
David Christensen
End Point Corporation
david@endpoint.com
----
diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c
index e2914ae..cc89728 100644
--- a/src/bin/psql/mainloop.c
+++ b/src/bin/psql/mainloop.c
@@ -197,6 +197,48 @@ MainLoop(FILE *source)
continue;
}+#define MYSQL_HELP_CHECK(o) \
+ (pg_strncasecmp(line, (o), strlen(o)) == 0 &&\
+ (line[strlen(o)] == '\0' || line[strlen(o)] == ';' ||
isspace((unsigned char) line[strlen(o)])))
+
+#define MYSQL_HELP_OUTPUT(o) \
+ free(line);\
+ printf(_("See:\n " \
+ o\
+ "\n"\
+ " or \\? for general
help with psql commands\n"));\
+ fflush(stdout);\
+ continue;
+
+ /* Present the Postgres equivalent common mysqlisms */
+ if (pset.cur_cmd_interactive && query_buf->len == 0)
+ {
+ if (MYSQL_HELP_CHECK("use"))
+ {
+ MYSQL_HELP_OUTPUT("\\c database");
+ }
+ else if (MYSQL_HELP_CHECK("show tables"))
+ {
+ MYSQL_HELP_OUTPUT("\\dt");
+ }
+ else if (MYSQL_HELP_CHECK("source"))
+ {
+ MYSQL_HELP_OUTPUT("\\i filename");
+ }
+ else if (MYSQL_HELP_CHECK("show databases"))
+ {
+ MYSQL_HELP_OUTPUT("\\l");
+ }
+ else if (MYSQL_HELP_CHECK("describe"))
+ {
+ MYSQL_HELP_OUTPUT("\\d tablename");
+ }
+ else if (MYSQL_HELP_CHECK("load data infile"))
+ {
+ MYSQL_HELP_OUTPUT("\\copy");
+ }
+ }
+
/* echo back if flag is set */
if (pset.echo == PSQL_ECHO_ALL && !
pset.cur_cmd_interactive)
puts(line);
On Tue, 2010-01-19 at 12:44 -0600, David Christensen wrote:
Hey -hackers,
I whipped up a quick patch for supporting some of the common mysql-
based "meta" commands; this is different than some things which have
been discussed in the past, in that it provides just a quick direction
to the appropriate psql command, not an actual alternative syntax for
the same action. This is not intended to be comprehensive, but just
to provide proper direction
I like that idea. There may be a lot of MySQL people that want to use
the next postgresql release, and this would make it easier.
Please add the patch to the next commitfest:
https://commitfest.postgresql.org/action/commitfest_view?id=6
It's small enough that, if others like it as well, maybe it (or
something similar) could still make it in this release.
Regards,
Jeff Davis
Jeff Davis wrote:
On Tue, 2010-01-19 at 12:44 -0600, David Christensen wrote:
Hey -hackers,
I whipped up a quick patch for supporting some of the common mysql-
based "meta" commands; this is different than some things which have
been discussed in the past, in that it provides just a quick direction
to the appropriate psql command, not an actual alternative syntax for
the same action. This is not intended to be comprehensive, but just
to provide proper directionI like that idea. There may be a lot of MySQL people that want to use
the next postgresql release, and this would make it easier.Please add the patch to the next commitfest:
https://commitfest.postgresql.org/action/commitfest_view?id=6
It's small enough that, if others like it as well, maybe it (or
something similar) could still make it in this release.
I'm not convinced that we should start adding syntax helpers like that
to psql. For now it is an arbitrary subset of MySQL stuff, are we going
to add oracle/db2/mssql/drizzle/mariadb and whatnot later on?
Also I can already see people asking "well you already know that this is
that command - why not emulate it fully?".
So -1 on the general idea of providing that kind of stuff (though I
think there is plenty of opportunity to make psql more useful in itself).
Stefan
I'm not convinced that we should start adding syntax helpers like that
to psql. For now it is an arbitrary subset of MySQL stuff, are we going
to add oracle/db2/mssql/drizzle/mariadb and whatnot later on?
Also I can already see people asking "well you already know that this is
that command - why not emulate it fully?".
Good points. However, it only takes effect in interactive mode, so I
don't see it as a promise to do much. I'll make an analogy to:
$ git difff
git: 'difff' is not a git-command. See 'git --help'.
Did you mean this?
diff
Regards,
Jeff Davis
On Tue, 2010-01-19 at 11:43 -0800, Jeff Davis wrote:
I'm not convinced that we should start adding syntax helpers like that
to psql. For now it is an arbitrary subset of MySQL stuff, are we going
to add oracle/db2/mssql/drizzle/mariadb and whatnot later on?
Also I can already see people asking "well you already know that this is
that command - why not emulate it fully?".Good points. However, it only takes effect in interactive mode, so I
don't see it as a promise to do much. I'll make an analogy to:
On second thought, if it's not a very restricted set of words, it might
limit what commands we can introduce later. In particular I notice that
it uses "load" which is too similar to postgresql's LOAD.
I think the words would need to be prefixed with something to separate
them from normal commands.
Regards,
Jeff Davis
Jeff Davis wrote:
I'm not convinced that we should start adding syntax helpers like that
to psql. For now it is an arbitrary subset of MySQL stuff, are we going
to add oracle/db2/mssql/drizzle/mariadb and whatnot later on?
Also I can already see people asking "well you already know that this is
that command - why not emulate it fully?".Good points. However, it only takes effect in interactive mode, so I
don't see it as a promise to do much. I'll make an analogy to:$ git difff
git: 'difff' is not a git-command. See 'git --help'.Did you mean this?
diff
well the actual output is just:
:~$ git difff
git: 'difff' is not a git-command. See 'git --help'.
which is more or less the same as:
postgres=# \mysql
Invalid command \mysql. Try \? for help.
so I don't really see why we need to add some random second guessing of
what the user actually wanted (and if he is indeed a mysql refugee he
can always use "help" and go on from there).
Stefan
David Christensen escreveu:
I whipped up a quick patch for supporting some of the common mysql-based
"meta" commands; this is different than some things which have been
discussed in the past, in that it provides just a quick direction to the
appropriate psql command, not an actual alternative syntax for the same
action. This is not intended to be comprehensive, but just to provide
proper direction
This idea was proposed and rejected later; search the archives. IMHO it's more
appropriated for a wiki page than a PostgreSQL-*especific* help command. If we
do that, we'll see requests like "why don't you add _my-favorite-db-here_ help
too?". So, -1.
--
Euler Taveira de Oliveira
http://www.timbira.com/
On Tue, 2010-01-19 at 20:52 +0100, Stefan Kaltenbrunner wrote:
Jeff Davis wrote:
I'm not convinced that we should start adding syntax helpers like that
to psql. For now it is an arbitrary subset of MySQL stuff, are we going
to add oracle/db2/mssql/drizzle/mariadb and whatnot later on?
Also I can already see people asking "well you already know that this is
that command - why not emulate it fully?".Good points. However, it only takes effect in interactive mode, so I
don't see it as a promise to do much. I'll make an analogy to:$ git difff
git: 'difff' is not a git-command. See 'git --help'.Did you mean this?
diffwell the actual output is just:
:~$ git difff
git: 'difff' is not a git-command. See 'git --help'.
Well, the actual output on my machine is what I put in the email.
That being said, I don't have much of an opinion, so if you see a
problem, then we can forget it. After all, we would need some kind of a
prefix anyway to avoid conflicting with actual SQL... maybe "\m"? And
that defeats a lot of the purpose.
Regards,
Jeff Davis
Jeff Davis <pgsql@j-davis.com> writes:
That being said, I don't have much of an opinion, so if you see a
problem, then we can forget it. After all, we would need some kind of a
prefix anyway to avoid conflicting with actual SQL... maybe "\m"? And
that defeats a lot of the purpose.
Yeah, requiring a prefix would make it completely pointless I think.
The submitted patch tries to avoid that by only matching syntax that's
invalid in Postgres, but that certainly limits how far we can go with
it. (And like you, I'm a bit worried about the LOAD case.)
The last go-round on this was just a couple months ago:
http://archives.postgresql.org/pgsql-bugs/2009-11/msg00241.php
although I guess that was aimed at a slightly different idea,
namely making "show databases" etc actually *work*. This one at
least has a level of complication that's more in keeping with the
possible gain.
The previous discussion started from the idea that only DESCRIBE,
SHOW DATABASES/TABLES, and USE were worth worrying about. If we
were to agree that we'd go that far and no farther, the potential
conflict with SQL syntax would be pretty limited. I have little
enough experience with mysql to not want to opine too much on how
useful that would be, but it does seem like those are commands
I use right away anytime I am using mysql.
regards, tom lane
On Tue, Jan 19, 2010 at 21:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jeff Davis <pgsql@j-davis.com> writes:
That being said, I don't have much of an opinion, so if you see a
problem, then we can forget it. After all, we would need some kind of a
prefix anyway to avoid conflicting with actual SQL... maybe "\m"? And
that defeats a lot of the purpose.Yeah, requiring a prefix would make it completely pointless I think.
Definitely.
The submitted patch tries to avoid that by only matching syntax that's
invalid in Postgres, but that certainly limits how far we can go with
it. (And like you, I'm a bit worried about the LOAD case.)The last go-round on this was just a couple months ago:
http://archives.postgresql.org/pgsql-bugs/2009-11/msg00241.php
although I guess that was aimed at a slightly different idea,
namely making "show databases" etc actually *work*. This one at
least has a level of complication that's more in keeping with the
possible gain.
I think the gain is actually better with this than to try to do the
work. We'd want it to teach people what to do.
The previous discussion started from the idea that only DESCRIBE,
SHOW DATABASES/TABLES, and USE were worth worrying about. If we
were to agree that we'd go that far and no farther, the potential
conflict with SQL syntax would be pretty limited. I have little
enough experience with mysql to not want to opine too much on how
useful that would be, but it does seem like those are commands
I use right away anytime I am using mysql.
I think just getting te most common cases would still be quite
helpful. Once you get the user to realize that "hey, that backslash
thing seems to do stuff", you've gone most of the way.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
The previous discussion started from the idea that only DESCRIBE,
SHOW DATABASES/TABLES, and USE were worth worrying about. If we
were to agree that we'd go that far and no farther, the potential
conflict with SQL syntax would be pretty limited. I have little
enough experience with mysql to not want to opine too much on how
useful that would be, but it does seem like those are commands
I use right away anytime I am using mysql.
I have no problems paring down the list of cases; these were the
correspondences I saw off the top of my head. I definitely don't want
to conflict with any SQL syntax. The exact wording/output of the
messages can be adjusted at whim.
Regards,
David
--
David Christensen
End Point Corporation
david@endpoint.com
Tom Lane wrote:
Jeff Davis <pgsql@j-davis.com> writes:
That being said, I don't have much of an opinion, so if you see a
problem, then we can forget it. After all, we would need some kind of a
prefix anyway to avoid conflicting with actual SQL... maybe "\m"? And
that defeats a lot of the purpose.Yeah, requiring a prefix would make it completely pointless I think.
The submitted patch tries to avoid that by only matching syntax that's
invalid in Postgres, but that certainly limits how far we can go with
it. (And like you, I'm a bit worried about the LOAD case.)The last go-round on this was just a couple months ago:
http://archives.postgresql.org/pgsql-bugs/2009-11/msg00241.php
although I guess that was aimed at a slightly different idea,
namely making "show databases" etc actually *work*. This one at
least has a level of complication that's more in keeping with the
possible gain.The previous discussion started from the idea that only DESCRIBE,
SHOW DATABASES/TABLES, and USE were worth worrying about. If we
were to agree that we'd go that far and no farther, the potential
conflict with SQL syntax would be pretty limited. I have little
enough experience with mysql to not want to opine too much on how
useful that would be, but it does seem like those are commands
I use right away anytime I am using mysql.
Agreed. I think this discussion mirrors the psql 'help' feature we
added in 8.4. After a lot of discussion we decided that a limited
'help' functionality was the best approach --- the more we added the
less attractive it became.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Tom Lane wrote:
Jeff Davis <pgsql@j-davis.com> writes:
That being said, I don't have much of an opinion, so if you see a
problem, then we can forget it. After all, we would need some kind of a
prefix anyway to avoid conflicting with actual SQL... maybe "\m"? And
that defeats a lot of the purpose.Yeah, requiring a prefix would make it completely pointless I think.
The submitted patch tries to avoid that by only matching syntax that's
invalid in Postgres, but that certainly limits how far we can go with
it. (And like you, I'm a bit worried about the LOAD case.)
yeah requiring a prefix would make it completely pointless
The last go-round on this was just a couple months ago:
http://archives.postgresql.org/pgsql-bugs/2009-11/msg00241.php
although I guess that was aimed at a slightly different idea,
namely making "show databases" etc actually *work*. This one at
least has a level of complication that's more in keeping with the
possible gain.
well providing a hint that one should use different command will only
lead to the path "uhm why not make it work as well" - and we also need
to recongnized that our replacements for some of those commands are not
really equivalent in most cases.
The previous discussion started from the idea that only DESCRIBE,
SHOW DATABASES/TABLES, and USE were worth worrying about. If we
were to agree that we'd go that far and no farther, the potential
conflict with SQL syntax would be pretty limited. I have little
enough experience with mysql to not want to opine too much on how
useful that would be, but it does seem like those are commands
I use right away anytime I am using mysql.
well those are the most common ones I guess for the current version of
the mysql commandline client - but what about future versions or the
fact that we only have partial replacements for some of those that
people are really asking for?
Stefan
David Christensen wrote:
+ if (MYSQL_HELP_CHECK("use")) + { + MYSQL_HELP_OUTPUT("\\c database"); + }
[snip]
+ else if (MYSQL_HELP_CHECK("load data infile")) + { + MYSQL_HELP_OUTPUT("\\copy"); + }
Quite apart from any considerations covered by other people, these two
at least could be positively misleading ... the psql commands are not
exact equivalents of the MySQL commands, AIUI.
cheers
andrew
On Jan 19, 2010, at 2:58 PM, Stefan Kaltenbrunner wrote:
Tom Lane wrote:
Jeff Davis <pgsql@j-davis.com> writes:
That being said, I don't have much of an opinion, so if you see a
problem, then we can forget it. After all, we would need some kind
of a
prefix anyway to avoid conflicting with actual SQL... maybe "\m"?
And
that defeats a lot of the purpose.Yeah, requiring a prefix would make it completely pointless I think.
The submitted patch tries to avoid that by only matching syntax
that's
invalid in Postgres, but that certainly limits how far we can go with
it. (And like you, I'm a bit worried about the LOAD case.)yeah requiring a prefix would make it completely pointless
Agreed.
The last go-round on this was just a couple months ago:
http://archives.postgresql.org/pgsql-bugs/2009-11/msg00241.php
although I guess that was aimed at a slightly different idea,
namely making "show databases" etc actually *work*. This one at
least has a level of complication that's more in keeping with the
possible gain.well providing a hint that one should use different command will
only lead to the path "uhm why not make it work as well" - and we
also need to recongnized that our replacements for some of those
commands are not really equivalent in most cases.
I think if you set this line ahead of time, you don't have to worry
about the detractors; this is equivalent to vim outputting
"Type :quit<Enter> to exit Vim" when you type emacs' quit sequence.
The intent is to show the correct way or to provide a helpful reminder
to people new to psql, not to make it work the same.
The previous discussion started from the idea that only DESCRIBE,
SHOW DATABASES/TABLES, and USE were worth worrying about. If we
were to agree that we'd go that far and no farther, the potential
conflict with SQL syntax would be pretty limited. I have little
enough experience with mysql to not want to opine too much on how
useful that would be, but it does seem like those are commands
I use right away anytime I am using mysql.well those are the most common ones I guess for the current version
of the mysql commandline client - but what about future versions or
the fact that we only have partial replacements for some of those
that people are really asking for?
I think it captures the intent of the people using the tool, and that
it adds a small net benefit in usability for those people. Deciding
to support this small subset does not obligate you to expand the scope
in the future. (Hey ma, this slope ain't slippery!)
Regards,
David
--
David Christensen
End Point Corporation
david@endpoint.com
On Jan 19, 2010, at 3:12 PM, Andrew Dunstan wrote:
David Christensen wrote:
+ if (MYSQL_HELP_CHECK("use")) + { + MYSQL_HELP_OUTPUT("\\c database"); + }[snip]
+ else if (MYSQL_HELP_CHECK("load data infile")) + { + MYSQL_HELP_OUTPUT("\\copy"); + }Quite apart from any considerations covered by other people, these
two at least could be positively misleading ... the psql commands
are not exact equivalents of the MySQL commands, AIUI.
The \copy could definitely be considered a stretch; I know \c supports
more options than the equivalent USE, but isn't it a proper superset
of functionality?
Regards,
David
--
David Christensen
End Point Corporation
david@endpoint.com
On Jan 19, 2010, at 12:58 PM, Stefan Kaltenbrunner wrote:
well providing a hint that one should use different command will only lead to the path "uhm why not make it work as well"
I don't think so. People know it's a different database. They'd be thrilled just to get the hint. I think it's a great idea (notwithstanding the caveats mentioned up-thread).
Best,
David
David Christensen wrote:
Quite apart from any considerations covered by other people, these
two at least could be positively misleading ... the psql commands are
not exact equivalents of the MySQL commands, AIUI.The \copy could definitely be considered a stretch; I know \c supports
more options than the equivalent USE, but isn't it a proper superset
of functionality?
Not really. "use" sets the default db in MySQL (and other DBs like
Sybase and MSSQL). But you don't really connect to a particular
database, unlike Postgres - you connect to the server. And you can
directly query other databases than the default, again unlike Postgres
where you can only directly query the database you're connected to. In
fact, "use" is part of MySQL's SQL dialect, and can be used from any
client, not just part of the metacommands of their commandline client.
See <http://dev.mysql.com/doc/refman/5.5/en/use.html>
cheers
andrew
David Christensen <david@endpoint.com> writes:
On Jan 19, 2010, at 2:58 PM, Stefan Kaltenbrunner wrote:
well those are the most common ones I guess for the current version
of the mysql commandline client - but what about future versions or
the fact that we only have partial replacements for some of those
that people are really asking for?
I think it captures the intent of the people using the tool, and that
it adds a small net benefit in usability for those people. Deciding
to support this small subset does not obligate you to expand the scope
in the future. (Hey ma, this slope ain't slippery!)
I thought Magnus had a really good point: covering these four cases will
probably be enough to teach newbies to look at psql's backslash
commands. And once they absorb that, we're over a big hump.
Also, TTBOMK these commands have been in mysql for many years. I don't
think that commands that might get introduced in future versions would
have anywhere near the same degree of wired-into-the-fingertips uptake
to them.
regards, tom lane
David Christensen <david@endpoint.com> writes:
On Jan 19, 2010, at 3:12 PM, Andrew Dunstan wrote:
Quite apart from any considerations covered by other people, these
two at least could be positively misleading ... the psql commands
are not exact equivalents of the MySQL commands, AIUI.
The \copy could definitely be considered a stretch; I know \c supports
more options than the equivalent USE, but isn't it a proper superset
of functionality?
No, in fact I was going to bring up exactly that point, but Andrew beat
me to it. You can make a good case that mysql databases are more nearly
a match to PG schemas than to PG databases. So arguably instead of "use
foo" a mysql convert would prefer "set search_path = foo". This would
have been a serious headache if we'd accepted the earlier plan of trying
to implement equivalent functionality. In this patch, it could probably
be accommodated by having the help message read something like
Perhaps you want "\c database" or "set search_path = schema".
Or we could punt and leave this one out of the set that have help
messages.
regards, tom lane
