Last active
December 8, 2020 22:43
-
-
Save sebastienfr/aacc652904044ec1930e142374fe5c37 to your computer and use it in GitHub Desktop.
Read SQLite database from Notes, extract notes content to files
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| module recup | |
| go 1.15 | |
| require github.com/mattn/go-sqlite3 v1.14.5 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| package main | |
| import ( | |
| "bytes" | |
| "compress/gzip" | |
| "database/sql" | |
| "fmt" | |
| _ "github.com/mattn/go-sqlite3" | |
| "log" | |
| "os" | |
| ) | |
| // length, skip | |
| func ReadLengthField(blob []byte) (int, int) { | |
| length := 0 | |
| skip := 0 | |
| dataLength := int(blob[0]) | |
| length = dataLength & 0x7F | |
| for dataLength > 0x7F { | |
| skip += 1 | |
| dataLength = int(blob[skip]) | |
| length = ((dataLength & 0x7F) << (skip * 7)) + length | |
| } | |
| skip += 1 | |
| return length, skip | |
| } | |
| func ProcessNoteBodyBlob(blob []byte) []byte { | |
| data := []byte{} | |
| pos := 0 | |
| //b'\x08\x00\x12': // header | |
| if !bytes.Equal(blob[0:3], []byte{0x08, 0x00, 0x12}) { | |
| fmt.Println("error missing or wrong header 1") | |
| return nil | |
| } | |
| pos += 3 | |
| length, skip := ReadLengthField(blob[pos:]) | |
| pos += skip | |
| //b'\x08\x00\x10': // header | |
| if !bytes.Equal(blob[pos:pos+3], []byte{0x08, 0x00, 0x10}) { | |
| fmt.Println("error missing or wrong header 2") | |
| return nil | |
| } | |
| pos += 3 | |
| length, skip = ReadLengthField(blob[pos:]) | |
| pos += skip | |
| // Now text data begins | |
| if blob[pos] != 0x1A { | |
| fmt.Println("unexpected byte in text header pos") | |
| return nil | |
| } | |
| pos += 1 | |
| length, skip = ReadLengthField(blob[pos:]) | |
| pos += skip | |
| // Read text tag next | |
| if blob[pos] != 0x12 { | |
| fmt.Println("unexpected byte in pos") | |
| return nil | |
| } | |
| pos += 1 | |
| length, skip = ReadLengthField(blob[pos:]) | |
| pos += skip | |
| data = blob[pos : pos+length] | |
| return data | |
| } | |
| func main() { | |
| db, err := sql.Open("sqlite3", "./notes.db") | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| defer db.Close() | |
| rows, err := db.Query("select ZDATA from ZICNOTEDATA WHERE ZDATA IS NOT NULL ORDER BY Z_PK ASC;") | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| i := 0 | |
| defer rows.Close() | |
| for rows.Next() { | |
| var note []byte | |
| err = rows.Scan(¬e) | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| b := bytes.NewBuffer(note) | |
| r, err := gzip.NewReader(b) | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| buf := new(bytes.Buffer) | |
| buf.ReadFrom(r) | |
| ub := buf.Bytes() | |
| txt := ProcessNoteBodyBlob(ub) | |
| fmt.Printf("raw data =%v\n", string(ub)) | |
| fmt.Printf("decoded data=%v\n", string(txt)) | |
| if txt == nil { | |
| fmt.Println("error while reading record") | |
| continue | |
| } | |
| f, err := os.Create(fmt.Sprintf("note_%03d.txt", i)) | |
| i++ | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| _, err = f.Write(txt) | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| r.Close() | |
| f.Close() | |
| } | |
| err = rows.Err() | |
| if err != nil { | |
| log.Fatal(err) | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment