sqlite_schema.go

package main

import (
	"bytes"
	"fmt"
	"os"
	"strings"

	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
)

type TableName struct {
	Name string `db:"name"`
}

type TableColumn struct {
	CID       int         `db:"cid"`
	Name      string      `db:"name"`
	Type      string      `db:"type"`
	NotNull   int         `db:"notnull"`
	DfltValue interface{} `db:"dflt_value"`
	PK        int         `db:"pk"`
}

func main() {

	dbFile := strings.Join(os.Args[1:], " ")
	//log.Println(dbFile)

	db, err := sqlx.Open("sqlite3", dbFile)
	perr(err)
	defer db.Close()

	var tables = map[string][]TableColumn{}

	rowsx, err := db.Queryx(`SELECT name FROM sqlite_master WHERE type='table';`)
	perr(err)
	for rowsx.Next() {
		tn := TableName{}
		perr(rowsx.StructScan(&tn))
		tables[tn.Name] = []TableColumn{}
	}

	for name, _ := range tables {
		rowsx, err := db.Queryx(`pragma table_info(` + name + `);`)
		perr(err)
		for rowsx.Next() {
			ci := TableColumn{}
			perr(rowsx.StructScan(&ci))
			tables[name] = append(tables[name], ci)
		}
	}

	// {
	// 	data, err := json.MarshalIndent(tables, "", " ")
	// 	perr(err)
	// 	log.Println(string(data))
	// }

	out := bytes.NewBuffer([]byte{})
	_, err = out.WriteString(fmt.Sprint("package main\n\n"))
	perr(err)

	for name, cols := range tables {
		_, err := out.WriteString(fmt.Sprint("type T", name, " struct {\n"))
		perr(err)

		for _, col := range cols {
			col.Type = strings.ToLower(col.Type)

			var (
				t      string
				dbname string = col.Name
			)

			if col.PK != 0 {
				//dbname = "_rowid_"
			}

			col.Name = strings.ToUpper(col.Name)[0:1] + col.Name[1:]

			switch {
			case strings.HasPrefix(col.Type, "int"):
				t = "int"

				if col.NotNull == 0 {
					t = "*" + t
				}
			case strings.HasPrefix(col.Type, "blob"):
				t = "[]byte"
			case strings.HasPrefix(col.Type, "clob"):
				fallthrough
			case strings.HasPrefix(col.Type, "varchar"):
				t = "string"

				if col.NotNull == 0 {
					t = "*" + t
				}
			default:
				panic("Unknown type '" + col.Type + "'")
			}

			_, err := out.WriteString(fmt.Sprint(col.Name, " ", t, " `db:", `"`, dbname, `"`, "`", "\n"))
			perr(err)
		}

		// type TableColumn struct {
		// 	CID       int         `db:"cid"`
		// 	Name      string      `db:"name"`
		// 	Type      string      `db:"type"`
		// 	NotNull   int         `db:"notnull"`
		// 	DfltValue interface{} `db:"dflt_value"`
		// 	PK        int         `db:"pk"`
		// }

		_, err = out.WriteString(fmt.Sprint("}\n"))
		perr(err)
		_, err = out.WriteString(fmt.Sprint("const T", name, `Name = "`, name, `"`, "\n\n"))
		perr(err)
	}

	fmt.Print(out.String())

}

func perr(err error) {
	if err != nil {
		panic(err)
	}
}