main.go

package main

import (
	"encoding/json"
	"log"
	"net/http"
	"time"

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

// QueryHandler is the entrypoint of the server
type QueryHandler struct {
	db *sqlx.DB
}

type rowMap map[string]interface{}

type fieldMap map[string]string

type fieldsMap map[string]fieldMap

type successPayload struct {
	Rows      []rowMap  `json:"rows"`
	Fields    fieldsMap `json:"fields"`
	Elapsed   float64   `json:"time"`
	TotalRows int64     `json:"total_rows"`
}

type errorPayload struct {
	Status  int16  `json:"status"`
	Message string `json:"message"`
}

func (h *QueryHandler) ServeHTTP(w http.ResponseWriter, r *http.Request) {

	// Parse the query string for the sql parameter
	// e.g. http://myapp.com/?sql=select * from users;
	q := r.URL.Query()["sql"]

	var count int64
	var err error
	scannedRows := make([]rowMap, count)
	elapsed := 0.0
	fields := fieldsMap{}

	if len(q) > 0 {
		start := time.Now()
		// Execute query for row count.
		count, err = h.checkCount(q[0])
		checkErr(err, w)

		// Obtain the elapsed time since start of query
		elapsed = time.Since(start).Seconds()

		if count > 0 {
			// Prepare the container for scanned rows
			scannedRows = make([]rowMap, count)

			// Execute the SQL query for results this time.
			i := 0
			var rows *sqlx.Rows
			var columns []string

			rows, err = h.db.Queryx(q[0])
			checkErr(err, w)

			columns, err = rows.Columns()
			checkErr(err, w)

			// Populate the fields object with real data from columns
			for _, column := range columns {
				// TODO: fix the type values of the column
				fields[column] = fieldMap{"type": "string"}
			}

			for rows.Next() {
				res := rowMap{}
				err = rows.MapScan(res)
				checkErr(err, w)
				scannedRows[i] = res
				i++
			}
		}
	}

	if err == nil {
		res := successPayload{scannedRows, fields, elapsed, count}

		// Write it back to the client.
		w.Header().Set("Content-Type", "application/json; charset=utf-8")
		json.NewEncoder(w).Encode(res)
	}
}

func (h *QueryHandler) checkCount(query string) (count int64, err error) {
	row := h.db.QueryRowx("SELECT count(*) c FROM (" + query + ") as foo")
	err = row.Scan(&count)
	return count, err
}

func checkErr(err error, w http.ResponseWriter) {
	if err != nil {
		res := errorPayload{http.StatusInternalServerError, err.Error()}
		w.Header().Set("Content-Type", "application/json; charset=utf-8")
		w.WriteHeader(http.StatusInternalServerError)
		json.NewEncoder(w).Encode(res)
		return
	}
}

func main() {
	// db, err := sqlx.Open("postgres", "postgresql://localhost:5432/sql_server_dev?sslmode=disable")
	db, err := sqlx.Open("sqlite3", "./foo.db")
	if err != nil {
		log.Fatalf("Error on initializing database connection: %s", err.Error())
	}

	// Database connection pool
	db.SetMaxIdleConns(100)

	// This opens a connection if necessary.
	err = db.Ping()

	if err != nil {
		log.Fatalf("Error on opening database connection: %s", err.Error())
	}

	http.Handle("/", &QueryHandler{db: db})
	http.ListenAndServe(":1234", nil)
}