8:golang修复数据库文件(sql报错:database disk image is malformed)

思路:
1:导出sql语句到临时文件
2:修改tmp.sql文件(将最后一行的Rollback改为Commit;)
3:读取tmp.sql并写入到新库中
准备工作(三个文件)
1:sqlite3.exe(自行下载:https://www.sqlite.org/download.html)
2:read.sql(自行创建一个空的.sql文件)
3:dump.sql(自行创建一个空的.sql文件)
调用:

DoRepair(DBPath, newDbName string)

方法定义:

package repairTool
import (
 "bytes"
 "debian/ant/service/application"
 "fmt"
 "io"
 "io/ioutil"
 "os"
 "os/exec"
 "path/filepath"
 "strings"
)
var fileNames = []string{"dump.sql", "read.sql", "sqlite3.exe", `tmp.sql`}
func DoRepair(DBPath, newDbName string) error {
 //防止中文路径报错,这里把需要的文件直接复制到损坏的db文件目录下
 if err := CopyFile(DBPath); err != nil {
 return err
 }
 //导出sql语句到临时文件
 if err := DumpSql(DBPath); err != nil {
 return err
 }
 //修改tmp.sql文件
 if err := ModLastLine(DBPath); err != nil {
 return err
 }
 //读取tmp.sql并写入到新库中
 if err := ReadSql(DBPath, newDbName); err != nil {
 return err
 }
 //删除临时文件
 if err := DelTmp(DBPath); err != nil {
 return err
 }
 return nil
}
//DumpSql Export sql statement to temporary file
func DumpSql(path string) error {
 sqlExe := filepath.Join(path, `sqlite3.exe`)
 if !Exist(sqlExe) {
 return fmt.Errorf("not found file:%s", sqlExe)
 }
 dmt := fmt.Sprintf("/c cd %s&%s&sqlite3.exe QQ.db < dump.sql", path, path[0:2])
 cmd := exec.Command(`cmd.exe`, dmt)
 var out bytes.Buffer
 var stderr bytes.Buffer
 cmd.Stdout = &out
 cmd.Stderr = &stderr
 if err := cmd.Run(); err != nil {
 return fmt.Errorf("DumpSql fail %s : %s", err, stderr.String())
 }
 return nil
}
// ModLastLine Change the last line of Rollback to Commit;
func ModLastLine(path string) error {
 fileName := filepath.Join(path, `tmp.sql`)
 input, err := ioutil.ReadFile(fileName)
 if err != nil {
 return err
 }
 lines := strings.Split(string(input), "\n")
 len := len(lines)
 for i := len - 1; i > 0; i-- {
 if strings.Contains(lines[i], "ROLLBACK") {
 lines[i] = "Commit;"
 break
 }
 }
 output := strings.Join(lines, "\n")
 err = ioutil.WriteFile(fileName, []byte(output), 0644)
 if err != nil {
 return err
 }
 return nil
}
//ReadSql Read tmp.sql and write to the new library
func ReadSql(path, newDbName string) error {
 sqlExe := filepath.Join(path, `sqlite3.exe`)
 if !Exist(sqlExe) {
 return fmt.Errorf("not found file:%s", sqlExe)
 }
 dmt := fmt.Sprintf("/c cd %s&%s&sqlite3.exe %s < read.sql", path, path[0:2], newDbName)
 cmd := exec.Command(`cmd.exe`, dmt)
 var out bytes.Buffer
 var stderr bytes.Buffer
 cmd.Stdout = &out
 cmd.Stderr = &stderr
 if err := cmd.Run(); err != nil {
 return fmt.Errorf("ReadSql fail %s : %s", err, stderr.String())
 }
 return nil
}
//DelTmp Delete temporary files
func DelTmp(path string) error {
 files, _ := ioutil.ReadDir(path)
 for _, f := range files {
 for _, fileName := range fileNames {
 if f.Name() == fileName {
 err := os.Remove(filepath.Join(path, fileName))
 if err != nil {
 return err
 }
 break
 }
 }
 }
 return nil
}
//Exist Whether the file exists
func Exist(filename string) bool {
 _, err := os.Stat(filename)
 return err == nil || os.IsExist(err)
}
//CopyFile Copy files to the location where QQ.db exists
func CopyFile(dbDir string) error {
 cwd, err := os.Getwd()
 if err != nil {
 return err
 }
 repairDir := filepath.Join(cwd, `tools\ios\repairTool`)
 files, _ := ioutil.ReadDir(repairDir)
 for _, f := range files {
 for _, fileName := range fileNames {
 if f.Name() == fileName {
 if _, err := Copy(filepath.Join(dbDir, fileName), filepath.Join(repairDir, fileName)); err != nil {
 return err
 }
 break
 }
 }
 }
 return nil
}
// Copy copy file to dstName
func Copy(dstName, srcName string) (written int64, err error) {
 src, err := os.Open(srcName)
 if err != nil {
 return
 }
 defer src.Close()
 dst, err := os.OpenFile(dstName, os.O_WRONLY|os.O_CREATE, 0644)
 if err != nil {
 return
 }
 defer dst.Close()
 return io.Copy(dst, src)
}
package repairTool
import (
 "bytes"
 "debian/ant/service/application"
 "fmt"
 "io"
 "io/ioutil"
 "os"
 "os/exec"
 "path/filepath"
 "strings"
)
var fileNames = []string{"dump.sql", "read.sql", "sqlite3.exe", `tmp.sql`}
//修复数据库,参考http://jianshu.site/2019/01/20/repairSqlite/
func DoRepair(DBPath, newDbName string) error {
 if err := CopyFile(DBPath); err != nil {
 return err
 }
 application.GApp.Progress.SetMessage("正在修复数据库(20%)")
 if err := DumpSql(DBPath); err != nil {
 return err
 }
 application.GApp.Progress.SetMessage("正在修复数据库(40%)")
 if err := ModLastLine(DBPath); err != nil {
 return err
 }
 application.GApp.Progress.SetMessage("正在修复数据库(60%)")
 if err := ReadSql(DBPath, newDbName); err != nil {
 return err
 }
 application.GApp.Progress.SetMessage("正在修复数据库(80%)")
 if err := DelTmp(DBPath); err != nil {
 return err
 }
 application.GApp.Progress.SetMessage("修复数据库完成")
 return nil
}
//DumpSql Export sql statement to temporary file
func DumpSql(path string) error {
 sqlExe := filepath.Join(path, `sqlite3.exe`)
 if !Exist(sqlExe) {
 return fmt.Errorf("not found file:%s", sqlExe)
 }
 dmt := fmt.Sprintf("/c cd %s&%s&sqlite3.exe QQ.db < dump.sql", path, path[0:2])
 cmd := exec.Command(`cmd.exe`, dmt)
 var out bytes.Buffer
 var stderr bytes.Buffer
 cmd.Stdout = &out
 cmd.Stderr = &stderr
 if err := cmd.Run(); err != nil {
 return fmt.Errorf("DumpSql fail %s : %s", err, stderr.String())
 }
 return nil
}
// ModLastLine Change the last line of Rollback to Commit;
func ModLastLine(path string) error {
 fileName := filepath.Join(path, `tmp.sql`)
 input, err := ioutil.ReadFile(fileName)
 if err != nil {
 return err
 }
 lines := strings.Split(string(input), "\n")
 len := len(lines)
 for i := len - 1; i > 0; i-- {
 if strings.Contains(lines[i], "ROLLBACK") {
 lines[i] = "Commit;"
 break
 }
 }
 output := strings.Join(lines, "\n")
 err = ioutil.WriteFile(fileName, []byte(output), 0644)
 if err != nil {
 return err
 }
 return nil
}
//ReadSql Read tmp.sql and write to the new library
func ReadSql(path, newDbName string) error {
 sqlExe := filepath.Join(path, `sqlite3.exe`)
 if !Exist(sqlExe) {
 return fmt.Errorf("not found file:%s", sqlExe)
 }
 dmt := fmt.Sprintf("/c cd %s&%s&sqlite3.exe %s < read.sql", path, path[0:2], newDbName)
 cmd := exec.Command(`cmd.exe`, dmt)
 var out bytes.Buffer
 var stderr bytes.Buffer
 cmd.Stdout = &out
 cmd.Stderr = &stderr
 if err := cmd.Run(); err != nil {
 return fmt.Errorf("ReadSql fail %s : %s", err, stderr.String())
 }
 return nil
}
//DelTmp Delete temporary files
func DelTmp(path string) error {
 files, _ := ioutil.ReadDir(path)
 for _, f := range files {
 for _, fileName := range fileNames {
 if f.Name() == fileName {
 err := os.Remove(filepath.Join(path, fileName))
 if err != nil {
 return err
 }
 break
 }
 }
 }
 return nil
}
//Exist Whether the file exists
func Exist(filename string) bool {
 _, err := os.Stat(filename)
 return err == nil || os.IsExist(err)
}
//CopyFile Copy files to the location where QQ.db exists
func CopyFile(dbDir string) error {
 cwd, err := os.Getwd()
 if err != nil {
 return err
 }
 repairDir := filepath.Join(cwd, `tools\ios\repairTool`)
 files, _ := ioutil.ReadDir(repairDir)
 for _, f := range files {
 for _, fileName := range fileNames {
 if f.Name() == fileName {
 if _, err := Copy(filepath.Join(dbDir, fileName), filepath.Join(repairDir, fileName)); err != nil {
 return err
 }
 break
 }
 }
 }
 return nil
}
// Copy copy file to dstName
func Copy(dstName, srcName string) (written int64, err error) {
 src, err := os.Open(srcName)
 if err != nil {
 return
 }
 defer src.Close()
 dst, err := os.OpenFile(dstName, os.O_WRONLY|os.O_CREATE, 0644)
 if err != nil {
 return
 }
 defer dst.Close()
 return io.Copy(dst, src)
}

本文来自:简书

感谢作者:aside section._1OhGeD

查看原文:8:golang修复数据库文件(sql报错:database disk image is malformed)

• 编辑
• 预览