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

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

思路:
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)

入群交流(和以上内容无关):Go中文网 QQ 交流群:729884609 或加微信入微信群:274768166 备注:入群;关注公众号:Go语言中文网

209 次点击  
加入收藏 微博
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传