github.com/team-ide/go-dialect@v1.9.20/gen_sql_test.go (about)

     1  package main
     2  
     3  import (
     4  	"errors"
     5  	"fmt"
     6  	"github.com/tealeg/xlsx"
     7  	"os"
     8  	"strings"
     9  	"testing"
    10  )
    11  
    12  func TestSqlParseGen(t *testing.T) {
    13  	err := sqlParse(`数据库SQL.xlsx`, "dialect/mapping.sql.go")
    14  	if err != nil {
    15  		panic(err)
    16  	}
    17  }
    18  
    19  type sqlDatabaseModel struct {
    20  	Name    string
    21  	sqlList []*sqlDatabaseSqlModel
    22  }
    23  
    24  type sqlDatabaseSqlModel struct {
    25  	Name    string
    26  	Sql     string
    27  	Comment string
    28  }
    29  
    30  func sqlParse(path string, outPath string) (err error) {
    31  	xlsxFForRead, err := xlsx.OpenFile(path)
    32  	if err != nil {
    33  		err = errors.New("excel [" + path + "] open error, " + err.Error())
    34  		return
    35  	}
    36  	sheets := xlsxFForRead.Sheets
    37  
    38  	var databases []*sqlDatabaseModel
    39  
    40  	for _, sheet := range sheets {
    41  		database := &sqlDatabaseModel{}
    42  		database.Name = sheet.Name
    43  
    44  		var titles []string
    45  
    46  		var RowMergeEnd = -1
    47  		var RowMergeCell = -1
    48  		var RowMergeValue string
    49  		for rowIndex, row := range sheet.Rows {
    50  
    51  			if rowIndex == 0 {
    52  				for _, cell := range row.Cells {
    53  					title := cell.Value
    54  					title = strings.TrimSpace(title)
    55  					titles = append(titles, title)
    56  				}
    57  				continue
    58  			}
    59  			var dataType = map[string]string{}
    60  			for cellIndex, cell := range row.Cells {
    61  				if cellIndex >= len(titles) {
    62  					break
    63  				}
    64  				title := titles[cellIndex]
    65  				if title == "" {
    66  					continue
    67  				}
    68  				value := cell.Value
    69  				if title != "SQL" {
    70  					value = strings.TrimSpace(value)
    71  				}
    72  				if cell.VMerge > 0 {
    73  					RowMergeCell = cellIndex
    74  					RowMergeEnd = rowIndex + cell.VMerge
    75  					RowMergeValue = value
    76  				}
    77  				if RowMergeCell == cellIndex {
    78  					if rowIndex <= RowMergeEnd {
    79  						value = RowMergeValue
    80  					} else {
    81  						RowMergeEnd = -1
    82  						RowMergeValue = ""
    83  					}
    84  				}
    85  				dataType[title] = value
    86  			}
    87  			if dataType["名称"] == "" {
    88  				continue
    89  			}
    90  
    91  			sqlDatabaseSql := &sqlDatabaseSqlModel{}
    92  			sqlDatabaseSql.Name = dataType["名称"]
    93  			sqlDatabaseSql.Sql = dataType["SQL"]
    94  			sqlDatabaseSql.Comment = dataType["说明"]
    95  			database.sqlList = append(database.sqlList, sqlDatabaseSql)
    96  		}
    97  
    98  		databases = append(databases, database)
    99  	}
   100  
   101  	outFile, err := os.Create(outPath)
   102  	if err != nil {
   103  		return
   104  	}
   105  	_, err = outFile.WriteString(`package dialect
   106  
   107  `)
   108  	if err != nil {
   109  		return
   110  	}
   111  	for _, one := range databases {
   112  		fmt.Println("-------- database [" + one.Name + "] start --------")
   113  
   114  		var code string
   115  		code += "// " + one.Name + " 数据库 SQL" + "\n"
   116  		funcName := ""
   117  		if strings.EqualFold(one.Name, "Mysql") {
   118  			funcName = "appendMysqlSql"
   119  		} else if strings.EqualFold(one.Name, "Oracle") {
   120  			funcName = "appendOracleSql"
   121  		} else if strings.EqualFold(one.Name, "达梦") {
   122  			funcName = "appendDmSql"
   123  		} else if strings.EqualFold(one.Name, "金仓") {
   124  			funcName = "appendKingBaseSql"
   125  		} else if strings.EqualFold(one.Name, "神通") {
   126  			funcName = "appendShenTongSql"
   127  		} else if strings.EqualFold(one.Name, "Sqlite") {
   128  			funcName = "appendSqliteSql"
   129  		} else if strings.EqualFold(one.Name, "GBase") {
   130  			funcName = "appendGBaseSql"
   131  		} else if strings.EqualFold(one.Name, "Postgresql") {
   132  			funcName = "appendPostgresqlSql"
   133  		} else if strings.EqualFold(one.Name, "DB2") {
   134  			funcName = "appendDb2Sql"
   135  		} else if strings.EqualFold(one.Name, "OpenGauss") {
   136  			funcName = "appendOpenGaussSql"
   137  		}
   138  		code += "func " + funcName + "(mapping *SqlMapping) {" + "\n"
   139  		for _, sqlModel := range one.sqlList {
   140  			code += "\n"
   141  			if sqlModel.Comment != "" {
   142  				code += "\t// " + sqlModel.Comment + "\n"
   143  			}
   144  			code += "\tmapping." + sqlModel.Name + " = `" + "\n"
   145  			code += sqlModel.Sql
   146  			code += "`" + "\n"
   147  
   148  			if sqlModel.Name == "TableCreateColumn" {
   149  				if strings.Contains(strings.ToLower(sqlModel.Sql), "comment") {
   150  					code += "\tmapping.TableCreateColumnHasComment = true" + "\n"
   151  				}
   152  			} else if sqlModel.Name == "ColumnUpdate" {
   153  				if strings.Contains(strings.ToLower(sqlModel.Sql), "comment") {
   154  					code += "\tmapping.ColumnUpdateHasComment = true" + "\n"
   155  				}
   156  				if strings.Contains(strings.ToLower(sqlModel.Sql), strings.ToLower("oldColumnName")) {
   157  					code += "\tmapping.ColumnUpdateHasRename = true" + "\n"
   158  				}
   159  				if strings.Contains(strings.ToLower(sqlModel.Sql), "after") {
   160  					code += "\tmapping.ColumnUpdateHasAfter = true" + "\n"
   161  				}
   162  			}
   163  		}
   164  		code += "}" + "\n\n"
   165  		fmt.Println(code)
   166  		_, err = outFile.WriteString(code)
   167  		if err != nil {
   168  			return
   169  		}
   170  		fmt.Println("-------- database [" + one.Name + "] end --------")
   171  	}
   172  	return
   173  }