github.com/RevenueMonster/sqlike@v1.0.6/sql/dialect/mysql/index.go (about)

     1  package mysql
     2  
     3  import (
     4  	"regexp"
     5  	"strconv"
     6  
     7  	sqlstmt "github.com/RevenueMonster/sqlike/sql/stmt"
     8  	"github.com/RevenueMonster/sqlike/sqlike/indexes"
     9  )
    10  
    11  // HasIndexByName :
    12  func (ms MySQL) HasIndexByName(stmt sqlstmt.Stmt, dbName, table, indexName string) {
    13  	stmt.WriteString(`SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND INDEX_NAME = ?;`)
    14  	stmt.AppendArgs(dbName, table, indexName)
    15  }
    16  
    17  // HasIndex :
    18  func (ms MySQL) HasIndex(stmt sqlstmt.Stmt, dbName, table string, idx indexes.Index) {
    19  	nonUnique, idxType := true, "BTREE"
    20  	switch idx.Type {
    21  	case indexes.Unique:
    22  		nonUnique = false
    23  	case indexes.FullText:
    24  		idxType = "FULLTEXT"
    25  	case indexes.Spatial:
    26  		idxType = "SPATIAL"
    27  	case indexes.Primary:
    28  		nonUnique = false
    29  	}
    30  	args := []interface{}{dbName, table, idxType, nonUnique}
    31  	stmt.WriteString("SELECT COUNT(1) FROM (")
    32  	stmt.WriteString("SELECT INDEX_NAME, COUNT(*) AS c FROM INFORMATION_SCHEMA.STATISTICS ")
    33  	stmt.WriteString("WHERE TABLE_SCHEMA = ? ")
    34  	stmt.WriteString("AND TABLE_NAME = ? ")
    35  	stmt.WriteString("AND INDEX_TYPE = ? ")
    36  	stmt.WriteString("AND NON_UNIQUE = ? ")
    37  	stmt.WriteString("AND COLUMN_NAME IN ")
    38  	stmt.WriteByte('(')
    39  	for i, col := range idx.Columns {
    40  		if i > 0 {
    41  			stmt.WriteByte(',')
    42  		}
    43  		stmt.WriteByte('?')
    44  		args = append(args, col.Name)
    45  	}
    46  	stmt.WriteByte(')')
    47  	stmt.WriteString(" GROUP BY INDEX_NAME")
    48  	stmt.WriteString(") AS temp WHERE temp.c = ?")
    49  	stmt.WriteByte(';')
    50  	args = append(args, int64(len(idx.Columns)))
    51  	stmt.AppendArgs(args...)
    52  }
    53  
    54  // GetIndexes :
    55  func (ms MySQL) GetIndexes(stmt sqlstmt.Stmt, dbName, table string) {
    56  	stmt.WriteString(`SELECT DISTINCT INDEX_NAME, INDEX_TYPE, NON_UNIQUE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?;`)
    57  	stmt.AppendArgs(dbName, table)
    58  }
    59  
    60  // CreateIndexes :
    61  func (ms MySQL) CreateIndexes(stmt sqlstmt.Stmt, db, table string, idxs []indexes.Index, supportDesc bool) {
    62  	stmt.WriteString("ALTER TABLE " + ms.TableName(db, table))
    63  	for i, idx := range idxs {
    64  		if i > 0 {
    65  			stmt.WriteByte(',')
    66  		}
    67  
    68  		stmt.WriteString(" ADD " + ms.getIndexByType(idx.Type) + " ")
    69  		name := idx.GetName()
    70  		if idx.Type == indexes.MultiValued {
    71  			stmt.WriteString(name + "( (CAST(")
    72  			if regexp.MustCompile(`(?is).+\s*\-\>\s*.+`).MatchString(idx.Cast) {
    73  				stmt.WriteString(idx.Cast)
    74  			} else {
    75  				stmt.WriteString("`" + idx.Cast + "` -> '$'")
    76  			}
    77  			stmt.WriteString(" AS " + idx.As + ")) )")
    78  		} else {
    79  			if name != "" {
    80  				stmt.WriteString(ms.Quote(name))
    81  			}
    82  			stmt.WriteString(" (")
    83  			for j, col := range idx.Columns {
    84  				if j > 0 {
    85  					stmt.WriteByte(',')
    86  				}
    87  				stmt.WriteString(ms.Quote(col.Name))
    88  				if !supportDesc {
    89  					continue
    90  				}
    91  				if col.Direction == indexes.Descending {
    92  					stmt.WriteString(" DESC")
    93  				}
    94  			}
    95  			stmt.WriteByte(')')
    96  		}
    97  
    98  		if idx.Comment != "" {
    99  			stmt.WriteString(" COMMENT " + strconv.Quote(idx.Comment))
   100  		}
   101  
   102  	}
   103  	stmt.WriteByte(';')
   104  }
   105  
   106  // DropIndexes :
   107  func (ms MySQL) DropIndexes(stmt sqlstmt.Stmt, db, table string, idxs []string) {
   108  	stmt.WriteString("ALTER TABLE " + ms.TableName(db, table) + " ")
   109  	for i, idx := range idxs {
   110  		if idx == "PRIMARY" {
   111  			// stmt.WriteString("DROP PRIMARY KEY")
   112  			continue
   113  		}
   114  		if i > 0 {
   115  			stmt.WriteByte(',')
   116  		}
   117  
   118  		stmt.WriteString("DROP INDEX " + ms.Quote(idx))
   119  	}
   120  	stmt.WriteByte(';')
   121  }
   122  
   123  func (ms MySQL) getIndexByType(k indexes.Type) (idx string) {
   124  	switch k {
   125  	case indexes.FullText:
   126  		idx = "FULLTEXT INDEX"
   127  	case indexes.Spatial:
   128  		idx = "SPATIAL INDEX"
   129  	case indexes.Unique:
   130  		idx = "UNIQUE INDEX"
   131  	case indexes.Primary:
   132  		idx = "PRIMARY KEY"
   133  	default:
   134  		idx = "INDEX"
   135  	}
   136  	return
   137  }