Re: hint infrastructure setup (v3) - Mailing list pgsql-patches

> Please show us an example of what a typical hint would look like to the
> user.

As an illustration, here is the kind of thing I had with an early
quick and dirty proof of concept implementation I made for myself
one month ago.

As a result of this first try, I've convinced myself that I should do
that cleanly and incrementally, one command at a time, with a proper clean
infrastructure, and intermediate validations...

My idea of the target audience for those hints is my students while
they're learning SQL.


--
-- test HINTs provided on parser errors.
--
-- it should:
-- (1) trigger all possible hints
-- (2) include ACTUAL syntax errors by REAL people
--
-- Note that these test assume a psql like interface which
-- can only send "lexable" and "even" commands to the backend.
-- Thus I cannot test badly closed quotes or parentheses.
--
--
-- (1) all hints
--
--
-- sql command expected

foo;
ERROR:  syntax error at or near "foo" at character 1
HINT:  sql command such as SELECT, CREATE, GRANT... expected

123;
ERROR:  syntax error at or near "123" at character 1
HINT:  sql command such as SELECT, CREATE, GRANT... expected

+;
ERROR:  syntax error at or near "+" at character 1
HINT:  sql command such as SELECT, CREATE, GRANT... expected

'hello world';
ERROR:  syntax error at or near "'hello world'" at character 1
HINT:  sql command such as SELECT, CREATE, GRANT... expected

NULL;
ERROR:  syntax error at or near "NULL" at character 1
HINT:  sql command such as SELECT, CREATE, GRANT... expected

COLUMN;
ERROR:  syntax error at or near "COLUMN" at character 1
HINT:  sql command such as SELECT, CREATE, GRANT... expected

CREAT TABLE foo(id INT4);
ERROR:  syntax error at or near "CREAT" at character 1
HINT:  sql command such as SELECT, CREATE, GRANT... expected

--
-- CREATE xxx

CREATE 123;
ERROR:  syntax error at or near "123" at character 8
HINT:  OR REPLACE, TABLE, DATABASE, USER... expected

CREATE foo;
ERROR:  syntax error at or near "foo" at character 8
HINT:  OR REPLACE, TABLE, DATABASE, USER... expected

CREATE + CREATE ();
ERROR:  syntax error at or near "+" at character 8
HINT:  OR REPLACE, TABLE, DATABASE, USER... expected

CREATE INT4;
ERROR:  syntax error at or near "INT4" at character 8
HINT:  OR REPLACE, TABLE, DATABASE, USER... expected

CREATE OR foo;
ERROR:  syntax error at or near "foo" at character 11
HINT:  REPLACE expected

--
-- DROP xxx

DROP 123;
ERROR:  syntax error at or near "123" at character 6
HINT:  object such as TABLE, DATABASE, USER... expected

DROP foo;
ERROR:  syntax error at or near "foo" at character 6
HINT:  object such as TABLE, DATABASE, USER... expected

DROP USER 123;
ERROR:  syntax error at or near "123" at character 11
HINT:  user id such as calvin expected

DROP SYSID 123;
ERROR:  syntax error at or near "SYSID" at character 6
HINT:  object such as TABLE, DATABASE, USER... expected

DROP USER SYSID 123;
ERROR:  syntax error at or near "123" at character 17

DROP GROUP 321;
ERROR:  syntax error at or near "321" at character 12
HINT:  group id such as admin expected

DROP GROUP calvin, foo;
ERROR:  syntax error at or near "," at character 18
HINT:  ; expected

DROP TABLE (foo);
ERROR:  syntax error at or near "(" at character 12
HINT:  table name... expected

--
-- CREATE USER

CREATE USER COLUMN;
ERROR:  syntax error at or near "COLUMN" at character 13
HINT:  user id such as calvin expected

CREATE USER 123;
ERROR:  syntax error at or near "123" at character 13
HINT:  user id such as calvin expected

CREATE USER 'hello';
ERROR:  syntax error at or near "'hello'" at character 13
HINT:  user id such as calvin expected

CREATE USER calvin foo;
ERROR:  syntax error at or near "foo" at character 20
HINT:  user options or SET or RESET expected

CREATE USER calvin WITH foo;
ERROR:  syntax error at or near "foo" at character 25
HINT:  user options expected

CREATE USER calvin WITH CREATEDB foo;
ERROR:  syntax error at or near "foo" at character 34
HINT:  other user options or ';' expected

CREATE USER calvin PASSWORD 123;
ERROR:  syntax error at or near "123" at character 29
HINT:  password string such as 'secret' expected

CREATE USER calvin WITH PASSWORD 123;
ERROR:  syntax error at or near "123" at character 34
HINT:  password string such as 'secret' expected

CREATE USER calvin WITH ENCRYPTED 'pass';
ERROR:  syntax error at or near "'pass'" at character 35
HINT:  PASSWORD expected

CREATE USER calvin WITH UNENCRYPTED 'pass';
ERROR:  syntax error at or near "'pass'" at character 37
HINT:  PASSWORD expected

CREATE USER calvin WITH ENCRYPTED PASS 123;
ERROR:  syntax error at or near "PASS" at character 35
HINT:  PASSWORD expected

CREATE USER calvin WITH ENCRYPTED PASSWORD 123;
ERROR:  syntax error at or near "123" at character 44
HINT:  password string such as 'secret' expected

CREATE USER calvin WITH UNENCRYPTED PASSWORD 'secret' CREADB;
ERROR:  syntax error at or near "CREADB" at character 55
HINT:  other user options or ';' expected

CREATE USER calvin NOCREATEDB foo;
ERROR:  syntax error at or near "foo" at character 31
HINT:  other user options or ';' expected

CREATE USER calvin WITH IN foo;
ERROR:  syntax error at or near "foo" at character 28
HINT:  GROUP expected

CREATE USER calvin WITH IN GROUP 'foo';
ERROR:  syntax error at or near "'foo'" at character 34
HINT:  group id list expected

CREATE USER calvin WITH IN GROUP 123;
ERROR:  syntax error at or near "123" at character 34
HINT:  group id list expected

CREATE USER calvin WITH IN GROUP admin, column;
ERROR:  syntax error at or near "column" at character 41
HINT:  group id such as admin expected

CREATE USER calvin WITH IN GROUP admin, 123;
ERROR:  syntax error at or near "123" at character 41
HINT:  group id such as admin expected

CREATE USER calvin WITH IN GROUP admin, CREATEDB;
ERROR:  group "admin" does not exist

CREATE USER calvin WITH IN GROUP admin CREATEDB foo;
ERROR:  syntax error at or near "foo" at character 49
HINT:  other user options or ';' expected

CREATE USER calvin NOCREATEUSER CREATEDB foo;
ERROR:  syntax error at or near "foo" at character 42
HINT:  other user options or ';' expected

CREATE USER calvin CREATEUSER foo;
ERROR:  syntax error at or near "foo" at character 31
HINT:  other user options or ';' expected

CREATE USER calvin SYSID foo;
ERROR:  syntax error at or near "foo" at character 26
HINT:  integer constant expected

CREATE USER calvin SYSID 12 foo;
ERROR:  syntax error at or near "foo" at character 29
HINT:  other user options or ';' expected

CREATE USER calvin VALID UNTIL 12;
ERROR:  syntax error at or near "12" at character 32
HINT:  date string such as '01-02-2000' expected

CREATE USER calvin VALID foo;
ERROR:  syntax error at or near "foo" at character 26
HINT:  UNTIL expected

--
-- ALTER USER

ALTER USER calvin foo;
ERROR:  syntax error at or near "foo" at character 19
HINT:  user options or SET or RESET or ; expected

ALTER USER calvin WITH foo;
ERROR:  syntax error at or near "foo" at character 24
HINT:  user options expected

ALTER USER calvin SET foo;
ERROR:  syntax error at or near ";" at character 26
HINT:  SET stuff expected

ALTER USER calvin RESET 123;
ERROR:  syntax error at or near "123" at character 25
HINT:  user id such as calvin expected

--
-- CREATE GROUP

CREATE GROUP 123;
ERROR:  syntax error at or near "123" at character 14
HINT:  group id such as admin expected

CREATE GROUP grp foo;
ERROR:  syntax error at or near "foo" at character 18
HINT:  user list such as "USER calvin" or "SYSID 123" expected

CREATE GROUP grp WITH foo;
ERROR:  syntax error at or near "foo" at character 23
HINT:  user list such as "USER calvin" or "SYSID 123" expected

CREATE GROUP grp WITH USER ;
ERROR:  syntax error at or near ";" at character 28
HINT:  user id such as calvin expected

CREATE GROUP grp WITH USER 123;
ERROR:  syntax error at or near "123" at character 28
HINT:  user id such as calvin expected

CREATE GROUP grp WITH USER calvin, 123;
ERROR:  syntax error at or near "123" at character 36
HINT:  user id such as calvin expected

CREATE GROUP grp WITH SYSID calvin;
ERROR:  syntax error at or near "calvin" at character 29
HINT:  integer constant expected

CREATE GROUP grp WITH SYSID 'hello';
ERROR:  syntax error at or near "'hello'" at character 29
HINT:  integer constant expected

CREATE GROUP grp WITH USER calvin, hobbes SYSID 'hello';
ERROR:  syntax error at or near "'hello'" at character 49
HINT:  integer constant expected

CREATE GROUP grtp USER 123;
ERROR:  syntax error at or near "123" at character 24
HINT:  user id such as calvin expected

CREATE GROUP grtp SYSID calvin;
ERROR:  syntax error at or near "calvin" at character 25
HINT:  integer constant expected

CREATE GROUP grp WITH USER calvin, hobbes SYSID 123 foo;
ERROR:  syntax error at or near "foo" at character 53
HINT:  user list such as "USER calvin" or "SYSID 123" expected

--
-- ALTER GROUP

ALTER GROUP grp;
ERROR:  syntax error at or near ";" at character 16
HINT:  ADD or DROP expected

ALTER GROUP grp foo;
ERROR:  syntax error at or near "foo" at character 17
HINT:  ADD or DROP expected

ALTER GROUP grp ADD calvin;
ERROR:  syntax error at or near "calvin" at character 21
HINT:  USER user id list expected

ALTER GROUP grp DROP calvin;
ERROR:  syntax error at or near "calvin" at character 22
HINT:  USER user id list expected

ALTER GROUP grp ADD USER 123;
ERROR:  syntax error at or near "123" at character 26
HINT:  user id such as calvin expected

ALTER GROUP grp DROP USER 123;
ERROR:  syntax error at or near "123" at character 27
HINT:  user id such as calvin expected

ALTER GROUP grp ADD USER calvin, USER 123;
ERROR:  syntax error at or near "USER" at character 34
HINT:  user id such as calvin expected

ALTER GROUP grp DROP USER calvin, 123;
ERROR:  syntax error at or near "123" at character 35
HINT:  user id such as calvin expected

ALTER GROUP grp ADD SYSID calvin;
ERROR:  syntax error at or near "SYSID" at character 21
HINT:  USER user id list expected

ALTER GROUP grp DROP SYSID calvin;
ERROR:  syntax error at or near "SYSID" at character 22
HINT:  USER user id list expected

--
-- CREATE SCHEMA...

CREATE SCHEMA 123;
ERROR:  syntax error at or near "123" at character 15
HINT:  schema name or AUTHORIZATION expected

CREATE SCHEMA sch foo;
ERROR:  syntax error at or near "foo" at character 19
HINT:  AUTHORIZATION or ... expected

CREATE SCHEMA sch AUTHORIZATION 123;
ERROR:  syntax error at or near "123" at character 33
HINT:  user id such as calvin expected

CREATE SCHEMA AUTHORIZATION 123;
ERROR:  syntax error at or near "123" at character 29
HINT:  user id such as calvin expected

CREATE SCHEMA sch AUTHORIZATION calvin CREATE foo;
ERROR:  syntax error at or near "foo" at character 47
HINT:  TABLE, INDEX, SEQUENCE, TRIGGER or VIEW expected

CREATE SCHEMA sch AUTHORIZATION calvin CREATE TABLE +;
ERROR:  syntax error at or near "+" at character 53
HINT:  table name... expected

CREATE SCHEMA sch AUTHORIZATION calvin CREATE TABLE foo() xxx;
ERROR:  syntax error at or near "xxx" at character 59
HINT:  schema statement list such as CREATE TABLE/INDEX/SEQUENCE/TRIGGER/VIEW or GRANT or ; expected

CREATE SCHEMA sch AUTHORIZATION calvin CREATE TABLE foo() CREATE TABLE bla() x;
ERROR:  syntax error at or near "x" at character 78
HINT:  schema statement list such as CREATE TABLE/INDEX/SEQUENCE/TRIGGER/VIEW or GRANT or ; expected


--
Fabien Coelho - coelho@cri.ensmp.fr