package main

import (
	"database/sql"
	"github.com/mattn/go-sqlite3"
	"log"
	"os"
)

func runQuery(db *sql.DB, query string) {
	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}
	stmt, err := tx.Prepare(query)
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()
	_, err = stmt.Exec()
	if err != nil {
		log.Fatal(err)
	}
	tx.Commit()
}
func main() {
	sql.Register("sqlite3_with_spatialite",
		&sqlite3.SQLiteDriver{
			Extensions: []string{"libspatialite"},
		})

	os.Remove("./foo.db")
	db, err := sql.Open("sqlite3_with_spatialite", "./foo.db")
	if err != nil {
		log.Panic(err)
	}
	defer db.Close()

	// This has to be surrounded by Begin and Commit,
	// otherwise InitSpatialMetaData() is very slow
	q := "SELECT InitSpatialMetaData();"
	runQuery(db, q)

	sqls := []string{
		"DROP TABLE IF EXISTS testtable",
		"CREATE TABLE testtable (id INTEGER PRIMARY KEY AUTOINCREMENT, name CHAR(255));",
		"SELECT AddGeometryColumn('testtable', 'geom', 4326, 'POLYGON', 2);",
		"SELECT CreateSpatialIndex('testtable', 'geom');",
	}
	for _, sql := range sqls {
		_, err = db.Exec(sql)
		if err != nil {
			log.Fatal(err)
		}
	}

	q = "INSERT INTO testtable (name, geom) VALUES ('Test', GeomFromText('POLYGON((10 10, 20 10, 20 20, 10 20, 10 10))', 4326));"
	runQuery(db, q)
}