func insertEntries(sqlite *sql.DB, m []Model) error {
	if len(m) == 0 {
		return nil
	}
	tableName := ""
	rowCount := 0
	foundEntry := false
	for _, mdl := range m {
		if reflect.ValueOf(mdl).Elem().IsValid() {
			tableName = mdl.tableName()
			foundEntry = true
			reflTypes := reflect.TypeOf(mdl).Elem()
			for j := 0; j < reflTypes.NumField(); j++ {
				if _, ignore := reflTypes.Field(j).Tag.Lookup("ignore"); ignore {
					continue
				}
				rowCount++
			}
			break
		}
	}
	if !foundEntry {
		return nil
	}
	tx, err := sqlite.Begin()
	if err != nil {
		return err
	}
	query := fmt.Sprintf("INSERT INTO %s VALUES (", tableName)
	for i := 0; i < rowCount; i++ {
		query += "?"
		if i+1 < rowCount {
			query += ", "
		}
	}
	query += ")"
	stmt, err := tx.Prepare(query)
	if err != nil {
		return errors.Wrapf(err, "Error while preparing query %s", query)
	}
	defer stmt.Close()
	for _, entry := range m {
		values := make([]interface{}, rowCount)
		reflValues := reflect.ValueOf(entry).Elem()
		reflTypes := reflect.TypeOf(entry).Elem()
		if !reflValues.IsValid() {
			continue
		}
		for j := 0; j < reflValues.NumField(); j++ {
			if _, ignore := reflTypes.Field(j).Tag.Lookup("ignore"); ignore {
				continue
			}
			v := reflValues.Field(j).Interface()
			values[j] = v
		}
		if _, err := stmt.Exec(values...); err != nil {
			return errors.Wrap(err, fmt.Sprintf("Could not insert entry %v", entry))
		}
	}
	if err := tx.Commit(); err != nil {
		return errors.Wrap(err, "Error while commiting entries")
	}
	return nil
}
insertEntries INSERTs entries of []model into a given SQLite database.
It does it by dynamically parsing all fields of a struct implementing
model using reflection and creating a query for SQLite out of it.
package model
import (
	"archive/zip"
	"database/sql"
	"fmt"
	"io"
	"io/ioutil"
	"os"
	"path/filepath"
	"reflect"
	"sync"
	"time"
	"github.com/davecgh/go-spew/spew"
	"github.com/pkg/errors"
	"github.com/sergi/go-diff/diffmatchpatch"
	_ "github.com/mattn/go-sqlite3"
)
const manifestFilename = "manifest.json"
type Database struct {
	BlockRange []*BlockRange
	Bookmark   []*Bookmark
	InputField []*InputField
	Location   []*Location
	Note       []*Note
	Tag        []*Tag
	TagMap     []*TagMap
	UserMark   []*UserMark
}
func (db *Database) FetchFromTable(tableName string, id int) Model {
	if db == nil {
		return nil
	}
	table := reflect.ValueOf(db).Elem().FieldByName(tableName)
	if !table.IsValid() {
		panic(fmt.Sprintf("Table %s does not exist in Database", tableName))
	}
	if id >= table.Len() {
		return nil
	}
	if table.Index(id).IsNil() {
		return nil
	}
	return table.Index(id).Interface().(Model)
}
func MakeDatabaseCopy(db *Database) *Database {
	newDB := &Database{}
	dbFields := reflect.ValueOf(db).Elem()
	for i := 0; i < dbFields.NumField(); i++ {
		field := dbFields.Field(i)
		if !field.CanInterface() {
			continue
		}
		tp := field.Kind()
		switch tp {
		case reflect.Slice:
			cpSlice := reflect.MakeSlice(field.Type(), field.Len(), field.Len())
			for j := 0; j < field.Len(); j++ {
				elem := field.Index(j)
				if elem.IsNil() {
					continue
				}
				switch t := elem.Interface().(type) {
				case Model:
					cpSlice.Index(j).Set(reflect.ValueOf(MakeModelCopy(elem.Interface().(Model))))
				default:
					panic(fmt.Sprintf("Element type %T is not supported for copying", t))
				}
			}
			cpField := reflect.ValueOf(newDB).Elem().Field(i)
			cpField.Set(cpSlice)
		default:
			panic(fmt.Sprintf("Field type %T is not supported for copying", tp))
		}
	}
	return newDB
}
func (db *Database) Equals(other *Database) bool {
	dbCp := MakeDatabaseCopy(db)
	otherCp := MakeDatabaseCopy(other)
	for _, db := range []*Database{dbCp, otherCp} {
		locIDChanges := SortByUniqueKey(&db.Location)
		UpdateIDs(db.Bookmark, "LocationID", locIDChanges)
		UpdateIDs(db.Bookmark, "PublicationLocationID", locIDChanges)
		UpdateIDs(db.InputField, "LocationID", locIDChanges)
		UpdateIDs(db.Note, "LocationID", locIDChanges)
		UpdateIDs(db.TagMap, "LocationID", locIDChanges)
		UpdateIDs(db.UserMark, "LocationID", locIDChanges)
		SortByUniqueKey(&db.Bookmark)
		SortByUniqueKey(&db.InputField)
		tagIDChanges := SortByUniqueKey(&db.Tag)
		UpdateIDs(db.TagMap, "TagID", tagIDChanges)
		umIDChanges := SortByUniqueKey(&db.UserMark)
		UpdateIDs(db.BlockRange, "UserMarkID", umIDChanges)
		UpdateIDs(db.Note, "UserMarkID", umIDChanges)
		SortByUniqueKey(&db.BlockRange)
		noteIDChanges := SortByUniqueKey(&db.Note)
		UpdateIDs(db.TagMap, "NoteID", noteIDChanges)
		SortByUniqueKey(&db.TagMap)
	}
	dbFields := reflect.ValueOf(dbCp).Elem()
	otherFields := reflect.ValueOf(otherCp).Elem()
	for i := 0; i < dbFields.NumField(); i++ {
		dbSlice := dbFields.Field(i)
		otherSlice := otherFields.Field(i)
		if !dbSlice.CanInterface() || !otherSlice.CanInterface() {
			continue
		}
		if dbSlice.Len() != otherSlice.Len() {
			fmt.Printf("Length of slices at index %d are not equal: %d vs %d\n", i, dbSlice.Len(), otherSlice.Len())
			return false
		}
		for j := 0; j < dbSlice.Len(); j++ {
			dElem := dbSlice.Index(j)
			oElem := otherSlice.Index(j)
			if dElem.IsNil() {
				if oElem.IsNil() {
					continue
				} else {
					return false
				}
			}
			if !dElem.MethodByName("Equals").Call([]reflect.Value{oElem})[0].Bool() {
				fmt.Println("Found different entries: ")
				left := spew.Sdump(dElem.Interface())
				right := spew.Sdump(oElem.Interface())
				fmt.Printf("%s \nvs\n %s", left, right)
				dmp := diffmatchpatch.New()
				diffs := dmp.DiffMain(left, right, true)
				fmt.Println("Diff:")
				fmt.Println(dmp.DiffPrettyText(diffs))
				return false
			}
		}
	}
	return true
}
func (db *Database) ImportJWLBackup(filename string) error {
	tmp, err := ioutil.TempDir("", "go-jwlm")
	if err != nil {
		return errors.Wrap(err, "Error while creating temporary directory")
	}
	defer os.RemoveAll(tmp)
	r, err := zip.OpenReader(filename)
	if err != nil {
		return err
	}
	defer r.Close()
	for _, file := range r.File {
		fileReader, err := file.Open()
		if err != nil {
			return err
		}
		defer fileReader.Close()
		path := filepath.Join(tmp, file.Name)
		targetFile, err := os.OpenFile(path, os.O_WRONLY|os.O_CREATE|os.O_TRUNC, file.Mode())
		if err != nil {
			return err
		}
		defer targetFile.Close()
		if _, err := io.Copy(targetFile, fileReader); err != nil {
			return errors.Wrap(err, "Error while copying files from backup to temporary folder")
		}
	}
	path := filepath.Join(tmp, manifestFilename)
	manifest := manifest{}
	if err := manifest.importManifest(path); err != nil {
		return errors.Wrap(err, "Error while importing manifest")
	}
	if err := manifest.validateManifest(); err != nil {
		return err
	}
	path = filepath.Join(tmp, manifest.UserDataBackup.DatabaseName)
	return db.importSQLite(path)
}
func (db *Database) importSQLite(filename string) error {
	sqlite, err := sql.Open("sqlite3", filename+"?immutable=1")
	if err != nil {
		return errors.Wrap(err, "Error while opening SQLite database")
	}
	defer sqlite.Close()
	emptyTables := []string{"PlaylistItem", "PlaylistItemChild", "PlaylistMedia"}
	for _, table := range emptyTables {
		count, err := getTableEntryCount(sqlite, table)
		if err != nil {
			return err
		}
		if count > 0 {
			return fmt.Errorf("Table %s is not empty. Merging of these entries are not supported yet", table)
		}
	}
	var wg sync.WaitGroup
	wg.Add(8)
	errors := make(chan error, 10)
	go func() {
		mdl, err := fetchFromSQLite(sqlite, &BlockRange{})
		if err != nil {
			errors <- err
			wg.Done()
			return
		}
		db.BlockRange = BlockRange{}.MakeSlice(mdl)
		wg.Done()
	}()
	go func() {
		mdl, err := fetchFromSQLite(sqlite, &Bookmark{})
		if err != nil {
			errors <- err
			wg.Done()
			return
		}
		db.Bookmark = Bookmark{}.MakeSlice(mdl)
		wg.Done()
	}()
	go func() {
		mdl, err := fetchFromSQLite(sqlite, &InputField{})
		if err != nil {
			errors <- err
			wg.Done()
			return
		}
		db.InputField = InputField{}.MakeSlice(mdl)
		wg.Done()
	}()
	go func() {
		mdl, err := fetchFromSQLite(sqlite, &Location{})
		if err != nil {
			errors <- err
			wg.Done()
			return
		}
		db.Location = Location{}.MakeSlice(mdl)
		wg.Done()
	}()
	go func() {
		mdl, err := fetchFromSQLite(sqlite, &Note{})
		if err != nil {
			errors <- err
			wg.Done()
			return
		}
		db.Note = Note{}.MakeSlice(mdl)
		wg.Done()
	}()
	go func() {
		mdl, err := fetchFromSQLite(sqlite, &Tag{})
		if err != nil {
			errors <- err
			wg.Done()
			return
		}
		db.Tag = Tag{}.MakeSlice(mdl)
		wg.Done()
	}()
	go func() {
		mdl, err := fetchFromSQLite(sqlite, &TagMap{})
		if err != nil {
			errors <- err
			wg.Done()
			return
		}
		db.TagMap = TagMap{}.MakeSlice(mdl)
		wg.Done()
	}()
	go func() {
		mdl, err := fetchFromSQLite(sqlite, &UserMark{})
		if err != nil {
			errors <- err
			wg.Done()
			return
		}
		db.UserMark = UserMark{}.MakeSlice(mdl)
		wg.Done()
	}()
	wg.Wait()
	select {
	case err := <-errors:
		return err
	default:
		return nil
	}
}
func fetchFromSQLite(sqlite *sql.DB, modelType Model) ([]Model, error) {
	capacity, err := getSliceCapacity(sqlite, modelType)
	if err != nil {
		return nil, errors.Wrap(err, "Could not determine number of entries in SQLite database")
	}
	result := make([]Model, capacity)
	rows, err := sqlite.Query(fmt.Sprintf("SELECT * FROM %s", modelType.tableName()))
	if err != nil {
		return nil, errors.Wrap(err, "Error while querying SQLite database")
	}
	i := 1
	defer rows.Close()
	for rows.Next() {
		var m Model
		switch tp := modelType.(type) {
		case *BlockRange:
			m = &BlockRange{}
		case *Bookmark:
			m = &Bookmark{}
		case *InputField:
			m = &InputField{pseudoID: i}
		case *Location:
			m = &Location{}
		case *Note:
			m = &Note{}
		case *Tag:
			m = &Tag{}
		case *TagMap:
			m = &TagMap{}
		case *UserMark:
			m = &UserMark{}
		default:
			panic(fmt.Sprintf("Fetching %T is not supported!", tp))
		}
		mn, err := m.scanRow(rows)
		if err != nil {
			return nil, errors.Wrap(err, "Error while scanning results from SQLite database")
		}
		result[mn.ID()] = mn
		i++
	}
	err = rows.Err()
	if err != nil {
		return nil, errors.Wrap(err, "Error while scanning results from SQLite database")
	}
	return result, nil
}
func getTableEntryCount(sqlite *sql.DB, tableName string) (int, error) {
	var count int
	err := sqlite.QueryRow(fmt.Sprintf("SELECT Count(*) FROM %s", tableName)).Scan(&count)
	if err != nil {
		return 0, errors.Wrapf(err, "Error while determing entry count of table %s", tableName)
	}
	return count, nil
}
func getSliceCapacity(sqlite *sql.DB, modelType Model) (int, error) {
	var query string
	if modelType.idName() != "" {
		query = fmt.Sprintf("SELECT %s FROM %s ORDER BY %s DESC LIMIT 1",
			modelType.idName(), modelType.tableName(), modelType.idName())
	} else {
		query = fmt.Sprintf("SELECT COUNT(*) FROM %s", modelType.tableName())
	}
	row, err := sqlite.Query(query)
	if err != nil {
		return 0, err
	}
	defer row.Close()
	capacity := 0
	for row.Next() {
		if err := row.Scan(&capacity); err != nil {
			return 0, err
		}
	}
	return capacity + 1, nil
}
func (db *Database) ExportJWLBackup(filename string) error {
	tmp, err := ioutil.TempDir("", "go-jwlm")
	if err != nil {
		return errors.Wrap(err, "Error while creating temporary directory")
	}
	defer os.RemoveAll(tmp)
	dbPath := filepath.Join(tmp, "user_data.db")
	if err := db.saveToNewSQLite(dbPath); err != nil {
		return errors.Wrap(err, "Could not create SQLite database for exporting")
	}
	manifestPath := filepath.Join(tmp, manifestFilename)
	mfst, err := generateManifest("go-jwlm", dbPath)
	if err != nil {
		return errors.Wrap(err, "Error while generating manifest")
	}
	if err := mfst.exportManifest(manifestPath); err != nil {
		return errors.Wrap(err, "Error while creating manifest.json")
	}
	files := []string{dbPath, manifestPath}
	if err := zipFiles(filename, files); err != nil {
		return errors.Wrap(err, fmt.Sprintf("Error while storing files in zip archive %s", filename))
	}
	return nil
}
func (db *Database) saveToNewSQLite(filename string) error {
	if err := createEmptySQLiteDB(filename); err != nil {
		return errors.Wrap(err, "Error while creating new empty SQLite database")
	}
	sqlite, err := sql.Open("sqlite3", filename)
	if err != nil {
		return errors.Wrap(err, "Error while opening SQLite database")
	}
	defer sqlite.Close()
	dbFields := reflect.ValueOf(db).Elem()
	for j := 0; j < dbFields.NumField(); j++ {
		slice := dbFields.Field(j).Interface()
		mdl, err := MakeModelSlice(slice)
		if err != nil {
			return err
		}
		if err := insertEntries(sqlite, mdl); err != nil {
			return errors.Wrapf(err, "Error while inserting entries of field %d", j)
		}
	}
	lastModified := time.Now().Format("2006-01-02T15:04:05-07:00")
	_, err = sqlite.Exec(fmt.Sprintf("UPDATE LastModified SET LastModified = \"%s\" WHERE LastModified = (SELECT * FROM LastModified)", lastModified))
	if err != nil {
		return errors.Wrap(err, "Error while updating LastModified")
	}
	_, err = sqlite.Exec("VACUUM")
	if err != nil {
		return errors.Wrap(err, "Error while vacuuming SQLite DB")
	}
	return nil
}