8：golang修复数据库文件（sql报错:database disk image is malformed）

aside section._1OhGeD · · 1335 次点击 · · 开始浏览

这是一个创建于的文章，其中的信息可能已经有所发展或是发生改变。

思路：
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)
}

有疑问加站长微信联系（非本文作者）