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 }