executor for sql file 执行sql文件中的sql语句

sqlfile.go

package main

import (
	"bufio"
	"database/sql"
	"github.com/alenstar/nanoweb/log"
	_ "github.com/go-sql-driver/mysql"
	"os"
	"strings"
)

func readsqlfile(path string) []string {
	file, err := os.Open(path)
	if err != nil {
		log.Error("os.Open", err.Error())
		return nil
	}

	defer file.Close()

	var sqls []string
	var tmpline string
	scanner := bufio.NewScanner(file)
	commentBegin := false
	//commentEnd := false
	for scanner.Scan() {
		line := scanner.Text()
		if strings.HasSuffix(line, "*/") {
			commentBegin = false
			continue
		}
		if len(line) > 0 {
			if strings.HasPrefix(line, "//") {
				continue
			} else if strings.HasPrefix(line, "--") {
				continue
			} else if strings.HasPrefix(line, "/*") {
				if strings.HasSuffix(line, "*/") {
					continue
				}
				commentBegin = true
				continue
			} else {
				if commentBegin {
                                        if strings.Contains(line,
						"Source Database") {
						line = strings.TrimSpace(line)
						values := strings.Split(line, ":")
						log.Info(values, len(values))
						if len(values) == 2 {
							sqls = append(sqls,
								"create database if not exists "+values[1]+";")
							sqls = append(sqls, "use "+values[1]+";")
						}
					}
					continue
				} else {
					// TODO
					if strings.HasSuffix(line, ";") {
						sqls = append(sqls, tmpline+line)
						tmpline = ""
					} else {
						tmpline = tmpline + line
					}
				}
			}
		}
	}
	return sqls
}


func main() {
	log.Info("begin")
	// Create an sql.DB and check for errors
	db, err := sql.Open("mysql", "root:root@/?charset=utf8")
	if err != nil {
		panic(err.Error())
	}
	// sql.DB should be long lived "defer" closes it once this function ends
	defer db.Close()

	// Test the connection to the database
	err = db.Ping()
	if err != nil {
		panic(err.Error())
	}
	_, err = db.Exec("CREATE DATABASE db_test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
	if err != nil {
		log.Error("db.Exec", err.Error())
	}

	lines := readsqlfile("test.sql")
	_, err = db.Exec("use db_test")
	if err != nil {
		log.Error("db.Exec", err.Error())
	}
	for _, v := range lines {
		// log.Info(v)
		_, err := db.Exec(v)
		if err != nil {
			log.Error("db.Exec", v, err.Error())
		}
	}
	log.Info("end")
}