Show -> Select rewrite

araddon · araddon · Mar 8, 2016 · Feb 29, 2016 · Mar 1, 2016 · Mar 2, 2016
commit 81618e16f1e98b4a86e7f04cdeb3488079c5300a
diff --git a/datasource/schemadb.go b/datasource/schemadb.go
@@ -26,7 +26,9 @@ var (
 	// normal tables
 	defaultSchemaTables = []string{"tables"}
 	defaultTableColumns = []string{"Table"}
+	columnColumns       = []string{"Field", "Type", "Null", "Key", "Default", "Extra"}
 	tableColumnMap      = map[string]int{"Table": 0}
+	columnsColumnMap    = map[string]int{"Field": 0, "Type": 1, "Null": 2, "Key": 3, "Default": 4, "Extra": 5}
 )
 
 type (

diff --git a/plan/sql_rewrite.go b/plan/sql_rewrite.go
@@ -16,8 +16,9 @@ func RewriteShowAsSelect(stmt *rel.SqlShow, ctx *Context) (*rel.SqlSelect, error
 	raw := strings.ToLower(stmt.Raw)
 	u.Debugf("attempting to rewrite %s", raw)
 	sel := rel.SqlSelect{}
+	showType := strings.ToLower(stmt.ShowType)
 	switch {
-	case strings.ToLower(stmt.ShowType) == "tables" || strings.ToLower(stmt.Identity) == ctx.SchemaName:
+	case showType == "tables" || strings.ToLower(stmt.Identity) == ctx.SchemaName:
 		if stmt.Full {
 			// SHOW FULL TABLES;    = select name, table_type from tables;
 		} else {
@@ -33,6 +34,8 @@ func RewriteShowAsSelect(stmt *rel.SqlShow, ctx *Context) (*rel.SqlSelect, error
 		// SHOW CREATE TABLE
 		//case strings.ToLower(stmt.Identity) == "databases":
 		// SHOW databases;  ->  select name from databases;
+	case showType == "variables":
+		// SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
 	default:
 		u.Warnf("unhandled %s", raw)
 		return nil, fmt.Errorf("Unrecognized:   %s", raw)

diff --git a/rel/parse_sql.go b/rel/parse_sql.go
@@ -463,150 +463,94 @@ func (m *Sqlbridge) parseDescribe() (SqlStatement, error) {
 func (m *Sqlbridge) parseShow() (*SqlShow, error) {
 
 	/*
-		SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
-		SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]  [LIKE 'pattern' | WHERE expr]
-		SHOW CREATE {TABLE | DATABASE | EVENT ...}
-
-		- SHOW tables;
-		- SHOW FULL TABLES FROM `auths` like '%'
-		- SHOW SESSION VARIABLES LIKE 'lower_case_table_names';
-		- SHOW COLUMNS FROM `mydb`.`mytable`
-		- SHOW CREATE TABLE `temp_schema`.`users`
+		don't currently support all these
+		http://dev.mysql.com/doc/refman/5.7/en/show.html
+
+		SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
+		SHOW CREATE DATABASE db_name
+		SHOW CREATE TABLE tbl_name
+		SHOW CREATE TRIGGER trigger_name
+		SHOW CREATE VIEW view_name
+		SHOW DATABASES [like_or_where]
+		SHOW ENGINE engine_name {STATUS | MUTEX}
+		SHOW [STORAGE] ENGINES
+		SHOW INDEX FROM tbl_name [FROM db_name]
+		SHOW [FULL] TABLES [FROM db_name] [like_or_where]
+		SHOW TRIGGERS [FROM db_name] [like_or_where]
+		SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
+		SHOW WARNINGS [LIMIT [offset,] row_count]
 	*/
+	likeLhs := "Table"
 	req := &SqlShow{}
 	req.Raw = m.l.RawInput()
 	m.Next() // Consume Show
 
-	//u.Debugf("show %v", m.Cur())
 	switch strings.ToLower(m.Cur().V) {
 	case "full":
-		// SHOW FULL COLUMNS FROM `table` FROM `dbname` LIKE '%'
 		req.Full = true
 		m.Next()
+	case "global", "session":
+		req.Scope = strings.ToLower(m.Next().V)
 	case "create":
 		// SHOW CREATE TABLE `temp_schema`.`users`
 		req.ShowType = "create"
 		m.Next()
 		req.Create = true
-		req.CreateWhat = m.Next().V
+		req.CreateWhat = m.Next().V // {TABLE | DATABASE | EVENT ...}
 		if m.Cur().T == lex.TokenIdentity {
-			req.Identity = m.Cur().V
-			m.Next()
-			//u.Infof("cur? %v", m.Cur())
+			req.Identity = m.Next().V
 			return req, nil
 		}
+		return nil, fmt.Errorf("Expected Identity for SHOW CREATE {TABLE | DATABASE | EVENT ...} but got %s", m.Cur().T.String())
 	}
 
-	u.Debugf("show 2 %v", m.Cur())
-	switch strings.ToLower(m.Cur().V) {
+	//u.Debugf("show %v", m.Cur())
+	objectType := strings.ToLower(m.Cur().V)
+	switch objectType {
+	case "databases":
+		req.ShowType = "databases"
+		m.Next()
+	case "variables":
+		req.ShowType = "variables"
+		m.Next()
 	case "columns":
 		m.Next() // consume columns
+		likeLhs = "Field"
 		req.ShowType = "columns"
 		//SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]  [LIKE 'pattern' | WHERE expr]
-		//u.Debugf("cur? %v", m.Cur())
-		switch m.Cur().T {
-		case lex.TokenEOF, lex.TokenEOS:
-			return req, nil
-		case lex.TokenFrom, lex.TokenIN:
-			//req.ShowType = "from"
-			m.Next()
-			req.Identity = m.Next().V
-			switch m.Cur().T {
-			case lex.TokenEOF, lex.TokenEOS:
-				return req, nil
-			case lex.TokenFrom, lex.TokenIN:
-				m.Next() // {from | in} we don't need this
-				req.Db = m.Cur().V
-				if m.Cur().T == lex.TokenIdentity {
-					switch m.Peek().T {
-					case lex.TokenLike:
-						// `table` FROM `schema` LIKE '%'
-						// WTF, this is not even valid expression syntax?  eff u mysql, we
-						// need to rearrange bc the `table` is what we need for like expression
-						m.Next() // consume identity of DB
-						m.Next() // consume LIKE
-						// We need to create a valid expression for vm, but this syntax isn't valid so we need to create
-						fixedExpr := fmt.Sprintf("%s LIKE %q", expr.IdentityMaybeQuote('`', req.Identity), m.Next().V)
-						tree, err := expr.ParseExpression(fixedExpr)
-						if err != nil {
-							u.Errorf("could not parse: %v", err)
-							return nil, err
-						}
-						req.Like = tree.Root
-					}
-				}
-			default:
-				u.Warnf("unhandled:  %v", m.Cur())
-			}
-		default:
-			u.Warnf("unhandled:  %v", m.Cur())
+		// | Field      | Type     | Null | Key | Default | Extra          |
+		if err := m.parseShowFromTable(req); err != nil {
+			return nil, err
+		}
+		if err := m.parseShowFromDatabase(req); err != nil {
+			return nil, err
 		}
-
 	case "tables":
-		req.ShowType = m.Cur().V
+		req.ShowType = objectType
 		m.Next() // consume Tables
-		switch m.Cur().T {
-		case lex.TokenEOF, lex.TokenEOS:
-			return req, nil
-		case lex.TokenLike:
-			// SHOW TABLES LIKE '%'
-			//m.SqlTokenPager.Backup()
-			u.Debugf("doing Like: %v %v", m.Cur(), m.Peek())
-			tree := expr.NewTree(m.SqlTokenPager)
-			if err := m.parseNode(tree); err != nil {
-				u.Errorf("could not parse: %v", err)
-				return nil, err
-			}
-			req.Like = tree.Root
-		}
-		m.Next() // ?? Why are we consuming this?
-		switch strings.ToLower(m.Cur().V) {
-		case "from":
-			m.Next()
-			if m.Cur().T == lex.TokenIdentity {
-				switch m.Peek().T {
-				case lex.TokenLike:
-					// SHOW FULL TABLES FROM `schema` LIKE '%'
-					u.Debugf("doing Like: %v %v", m.Cur(), m.Peek())
-					tree := expr.NewTree(m.SqlTokenPager)
-					if err := m.parseNode(tree); err != nil {
-						u.Errorf("could not parse: %v", err)
-						return nil, err
-					}
-					req.Like = tree.Root
-				}
-			}
-		}
-	}
-
-	u.Infof("cur? %v", m.Cur())
-	switch m.Cur().T {
-	case lex.TokenLike:
-		// SHOW FULL TABLES FROM `schema` LIKE '%'
-		u.Debugf("doing Like: %v %v", m.Cur(), m.Peek())
-		tree := expr.NewTree(m.SqlTokenPager)
-		if err := m.parseNode(tree); err != nil {
-			u.Errorf("could not parse: %v", err)
+		// SHOW [FULL] TABLES [FROM db_name] [like_or_where]
+		if err := m.parseShowFromDatabase(req); err != nil {
 			return nil, err
 		}
-		req.Like = tree.Root
 	}
 
 	switch m.Cur().T {
 	case lex.TokenEOF, lex.TokenEOS:
 		return req, nil
-	case lex.TokenIdentity:
-		u.Infof("show identity? %v", m.Cur())
-		req.Identity = m.Cur().V
-		// SHOW FULL TABLES FROM `schema` LIKE '%'
-		//m.Next()
-	default:
-		u.Warnf("unhandled:  %v", m.Cur())
-		//return nil, fmt.Errorf("expected idenity but got: %v", m.Cur())
-	}
-
-	switch strings.ToLower(m.Cur().V) {
-	case "where":
+	case lex.TokenLike:
+		// SHOW TABLES LIKE '%'
+		//u.Debugf("doing Like: %v %v", m.Cur(), m.Peek())
+		m.Next() // Consume Like
+		ex, err := expr.ParseExpression(fmt.Sprintf("%s LIKE %q", likeLhs, m.Cur().V))
+		m.Next()
+		if err != nil {
+			u.Errorf("Error parsing fake expression: %v", err)
+		} else {
+			req.Like = ex.Root
+		}
+		//u.Debugf("doing Like: %v %v", m.Cur(), m.Peek())
+	case lex.TokenWhere:
+		m.Next() // consume where
 		u.Debugf("doing where: %v %v", m.Cur(), m.Peek())
 		tree := expr.NewTree(m.SqlTokenPager)
 		if err := m.parseNode(tree); err != nil {
@@ -1569,6 +1513,40 @@ func (m *Sqlbridge) parseWith(req *SqlSelect) error {
 	return nil
 }
 
+func (m *Sqlbridge) parseShowFromTable(req *SqlShow) error {
+
+	switch m.Cur().T {
+	case lex.TokenFrom, lex.TokenIN:
+		m.Next() // Consume {FROM | IN}
+	default:
+		// FROM OR IN are required for this statement
+		return fmt.Errorf("Expected { FROM | IN } for SHOW but got %q", m.Cur().V)
+	}
+
+	if m.Cur().T != lex.TokenIdentity {
+		return fmt.Errorf("Expected { FROM | IN } IDENTITY for SHOW but got %q", m.Cur().V)
+	}
+	req.Identity = m.Next().V
+	return nil
+}
+
+func (m *Sqlbridge) parseShowFromDatabase(req *SqlShow) error {
+
+	switch m.Cur().T {
+	case lex.TokenFrom, lex.TokenIN:
+		m.Next() // Consume {FROM | IN}
+	default:
+		// this is optional
+		return nil
+	}
+
+	if m.Cur().T != lex.TokenIdentity {
+		return fmt.Errorf("Expected { FROM | IN } IDENTITY for SHOW but got %q", m.Cur().V)
+	}
+	req.Db = m.Next().V
+	return nil
+}
+
 func parseJsonObject(pg expr.TokenPager, jh u.JsonHelper) error {
 	if pg.Cur().T != lex.TokenLeftBrace {
 		return fmt.Errorf("Expected json { but got: %v", pg.Cur().T.String())

diff --git a/rel/parse_sql_test.go b/rel/parse_sql_test.go
@@ -108,9 +108,9 @@ func TestSqlLexOnly(t *testing.T) {
 	// TODO:
 	//parseSqlTest(t, `INSERT INTO events (id,event_date,event) SELECT id,last_logon,"last_logon" FROM users;`)
 	// parseSqlTest(t, `REPLACE INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;`)
-	parseSqlTest(t, `insert into mytable (id, str) values (0, 'a')`)
-	parseSqlTest(t, `upsert into mytable (id, str) values (0, 'a')`)
-	parseSqlTest(t, `insert into mytable (id, str) values (0, 'a'),(1,'b');`)
+	parseSqlTest(t, `insert into mytable (id, str) values (0, "a")`)
+	parseSqlTest(t, `upsert into mytable (id, str) values (0, "a")`)
+	parseSqlTest(t, `insert into mytable (id, str) values (0, "a"),(1,"b");`)
 
 	parseSqlTest(t, `SELECT LAST_INSERT_ID();`)
 	parseSqlTest(t, `SELECT CHARSET();`)
@@ -119,6 +119,12 @@ func TestSqlLexOnly(t *testing.T) {
 
 	parseSqlTest(t, `DESCRIBE mytable`)
 	parseSqlTest(t, `show tables`)
+	parseSqlTest(t, `show tables LIKE "user%";`)
+	parseSqlTest(t, `show databases`)
+	parseSqlTest(t, "SHOW FULL COLUMNS FROM `tablex` FROM `dbx` LIKE '%';")
+	parseSqlTest(t, `SHOW VARIABLES`)
+	parseSqlTest(t, `SHOW GLOBAL VARIABLES like '%'`)
+	//parseSqlTest(t, `SHOW VARIABLES where `)
 
 	parseSqlTest(t, `select 3, director from movies`)
 	parseSqlTest(t, `select director, year from movies where year BETWEEN 2000 AND 2010;`)
@@ -382,7 +388,7 @@ func TestSqlShowAst(t *testing.T) {
 	assert.Tf(t, show.ShowType == "columns", "has SHOW 'Columns'? %#v", show)
 	assert.Tf(t, show.Db == "dbx", "has SHOW db: %q", show.Db)
 	assert.Tf(t, show.Identity == "tablex", "has identity: %q", show.Identity)
-	assert.Tf(t, show.Like.String() == "tablex LIKE \"%\"", "has Like? %q", show.Like.String())
+	assert.Tf(t, show.Like.String() == "Field LIKE \"%\"", "has Like? %q", show.Like.String())
 }
 
 func TestSqlCommands(t *testing.T) {

diff --git a/rel/sql.go b/rel/sql.go
@@ -175,9 +175,10 @@ type (
 	SqlShow struct {
 		Raw        string // full raw statement
 		Db         string // Database/Schema name
+		Full       bool   // SHOW FULL TABLE FROM
+		Scope      string // {FULL, GLOBAL, SESSION}
 		ShowType   string // object type, [tables, columns, etc]
 		From       string // `table`   or `schema`.`table`
-		Full       bool   // SHOW FULL TABLE FROM
 		Identity   string // `table`   or `schema`.`table`
 		Create     bool
 		CreateWhat string

diff --git a/vm/vm.go b/vm/vm.go
@@ -1069,6 +1069,10 @@ func operateStrings(op lex.Token, av, bv value.StringValue) value.Value {
 }
 
 func likeCompare(a, b string) (value.BoolValue, bool) {
+	// Do we want to always do this replacement?   Or do this at parse time or config?
+	if strings.Contains(b, "%") {
+		b = strings.Replace(b, "%", "*", -1)
+	}
 	match, err := glob.Match(b, a)
 	//u.Debugf("ran LIKE: match?%v err=%v  expr:  %s LIKE %s", match, err, b, a)
 	if err != nil {