Re: hint infrastructure setup (v3)

> 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(at)cri(dot)ensmp(dot)fr