Run sql commands from a file, saving position in a `.index` file in case of error.

indexed_sql_file_runner.go

package main

import (
	"bufio"
	"database/sql"
	"flag"
	"fmt"
	"path"
	"strconv"
	"strings"
	"time"

	//"database/sql"
	_ "github.com/lib/pq"
	"io"
	"os"
	//"strings"
)

var (
	connectionString = ""
	file             = ""
	limiter          = time.Tick(50 * time.Millisecond)
	indexFile = ""
)

type Index struct {
	f      *os.File
}

func init() {
	flag.StringVar(&connectionString, "con-str", "", "Connection string for database")
	flag.StringVar(&file, "file", "", "File with queries, newline separated")
	flag.Parse()

	if strings.TrimSpace(file) == "" || strings.TrimSpace(connectionString) == ""{
		flag.Usage()
		os.Exit(1)
	}

indexFile = fmt.Sprintf(".%s.idx", path.Base(file))
}

func (i *Index) Open() (err error) {
	i.f, err = os.OpenFile(indexFile, os.O_CREATE|os.O_RDWR, 0655)
	if err != nil {
		return fmt.Errorf("failed to open .index: %w", err)
	}
	return nil
}

func (i *Index) Save(index int) error {
	i.f.Truncate(0)
	i.f.Seek(0,0)
	if _, err := i.f.WriteString(fmt.Sprintf("%d\n", index)); err != nil {
		return fmt.Errorf("failed to write string: %w", err)
	}
	return nil
}

func (i *Index) Current() (int, error) {

	st, _ := i.f.Stat()
	if st.Size() == 0 {
		return 0, nil
	}
	buf := make([]byte, st.Size())
	_, err := i.f.ReadAt(buf, 0)
	if err != nil {
		return 0, fmt.Errorf("failed to read: %w", err)
	}
	line, err := strconv.Atoi(strings.TrimSpace(string(buf)))
	if err != nil {
		return 0, fmt.Errorf("failed to atoi: %w", err)
	}
	return line, nil
}

func (i *Index) Close() error {
	return i.f.Close()
}

func main() {

	c, err := sql.Open("postgres", connectionString)
	if err != nil {
		panic(err)
	}

	ind := Index{}
	if err := ind.Open(); err != nil {
		panic(err)
	}
	defer ind.Close()

	f, err := os.OpenFile(file, os.O_RDONLY, os.ModePerm)
	if err != nil {
		panic(err)
	}
	defer f.Close()

	startAt, err := ind.Current()
	if err != nil {
		panic(err)
	}
	startAt++
	line := 0

	r := bufio.NewReader(f)


	for {
		if err := func() (err error) {

			defer func() {
				if err == nil {
					line++

				}
			}()
			start := time.Now()

			s, err := r.ReadString('\n')
			if err != nil {
				if err == io.EOF {
					os.Exit(0)
				}
				return err
			}

			if line < startAt {
				return nil
			}


			<-limiter
			fmt.Printf("line: %d -- %s", line, s)

			res, err := c.Exec(s)
			if err != nil {
				return err
			}

			if err := ind.Save(line); err != nil {
				return err
			}
			if n, _ := res.RowsAffected(); n == 0 {
				fmt.Println("no rows affected")
			}
			diff := time.Since(start)
			fmt.Println(diff)
			return nil
		}(); err != nil {
			panic(err)
		}
	}
}