// ExecFile executes the SQL commands read from a file.
// Skips lines starting with "#".
//
// 'msg' is the message used to logging, if any.
func (cx *DBContext) ExecFile(filename, msg string) (err error) {
f, err := os.Open(filename)
if err != nil {
return err
}
defer func() {
if err1 := f.Close(); err == nil {
err = err1
}
}()
buf := bufio.NewReader(f)
// Handle multiple lines
for fullLine := ""; ; {
line, err := buf.ReadString('\n')
if err == io.EOF {
break
}
line = strings.TrimSpace(line)
if line == "" || strings.HasPrefix(line, "#") {
continue
}
fullLine += line
if !strings.HasSuffix(line, ";") { // Multiple line
continue
}
if _, err = cx.DB.Exec(fullLine); err != nil {
return fmt.Errorf("SQL code: %q\n%s", fullLine, err)
}
fullLine = ""
}
if datautil.VERBOSE {
log.WithFields(log.Fields{
"db": cx.Conn.GetDBName(),
"file": packer.RelativeDir(filename),
}).Info(msg)
}
return nil
}
// Write writes the SQL code to a file got from the one given.
func (s *SQLInfo) Write(dir string) error {
tmpl := template.Must(template.New("createtb").Parse(tmplCreatetb))
srcDir, srcFile := filepath.Split(s.SrcFilePath)
data := tmplData{
Info: s,
Header: fmt.Sprintf("# Code generated by getsql from %q\n# DO NOT EDIT",
filepath.Join(filepath.Base(srcDir), srcFile)),
}
createFile := func(path, tmplName string) error {
file, err := os.Create(path)
if err != nil {
return err
}
err = tmpl.ExecuteTemplate(file, tmplName, data)
file.Close()
if err != nil {
return err
}
if datautil.VERBOSE {
log.Printf("Write file: '%s'", packer.RelativeDir(path))
}
return nil
}
// Directory for SQL files
err := os.MkdirAll(dir, 0775)
if err != nil && !os.IsExist(err) {
return err
}
// Create the SQL file
return createFile(dbutil.GetSQLFilename(dir, "table", s.DBMS), "createtb")
}
// LoadJSON loads data from a JSON file in the database table.
//
// If src != nil, LoadJSON loads the source from src and the filename is only used
// when recording file information. The type of the argument for the src parameter
// must be []byte, or io.Reader. If src == nil, LoadJSON loads the file
// specified by filename.
func (cx *DBContext) LoadJSON(src interface{}, filename, table string) (err error) {
var dec *json.Decoder
if src != nil {
switch s := src.(type) {
case []byte:
dec = json.NewDecoder(bytes.NewReader(s))
case io.Reader:
dec = json.NewDecoder(s)
default:
return fmt.Errorf("invalid source")
}
} else {
f, err := os.Open(filename)
if err != nil {
return err
}
defer func() {
if err1 := f.Close(); err == nil {
err = err1
}
}()
dec = json.NewDecoder(f)
}
// The first row must have the name of the columns.
firstRecord := make([]string, 0)
if err = dec.Decode(&firstRecord); err != nil {
return err
}
tx, err := cx.DB.Begin()
if err != nil {
return err
}
defer func() {
if err != nil {
err1 := tx.Rollback()
if err1 != nil && datautil.VERBOSE {
log.WithFields(log.Fields{
"file": filename,
"table": table,
}).Warn(err1)
}
}
}()
insertCode := fmt.Sprintf("INSERT INTO %s (%s) VALUES(%s)",
table,
strings.Join(firstRecord, ", "),
cx.bindChars(len(firstRecord)),
)
insert, err := tx.Prepare(insertCode)
if err != nil {
return fmt.Errorf("SQL code: %q\n%s", insertCode, err)
}
list := make([]interface{}, 0)
for line := 2; dec.More(); line++ {
if err = dec.Decode(&list); err != nil {
return fmt.Errorf("%q line %d: %s", filename, line, err)
}
_, err = tx.Stmt(insert).Exec(list...)
if err != nil {
return fmt.Errorf("code: %q vars: %v => %s", insertCode, list, err)
}
}
if err = insert.Close(); err != nil && datautil.VERBOSE {
log.WithFields(log.Fields{
"file": filename,
"table": table,
}).Warn(err)
}
if err = tx.Commit(); err != nil {
return err
}
if datautil.VERBOSE {
log.WithFields(log.Fields{
"file": packer.RelativeDir(filename),
"db": cx.Conn.GetDBName(),
"table": table,
}).Info("Load JSON file")
}
return nil
}