// dbasik provides a service with which to convert spreadsheets containing
// data to JSON for further processing.
// Copyright (C) 2024 M R Lemon
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
// GNU General Public License for more details.
// You should have received a copy of the GNU General Public License
// along with this program. If not, see <https://www.gnu.org/licenses/>.
package main
import (
"database/sql"
"encoding/csv"
"encoding/json"
"errors"
"fmt"
"net/http"
"strconv"
"time"
)
// A custom err to return from our Get() method when looking up a datamap
// that doesn't exist
var (
ErrRecordNotFound = errors.New("record not found")
)
// A Models struct wraps the DatmapModel. We can add other models to this as
// we progress
type Models struct {
Datamaps datamapModel
DatamapLines datamapLineModel
}
// datamapLine holds the data parsed from each line of a submitted datamap CSV file.
// The fields need to be exported otherwise they won't be included when encoding
// the struct to json.
type datamapLine struct {
ID int64 `json:"id"`
Key string `json:"key"`
Sheet string `json:"sheet"`
DataType string `json:"datatype"`
Cellref string `json:"cellref"`
}
type datamapLineModel struct {
DB *sql.DB
}
// datamap includes a slice of datamapLine objects alongside header metadata
type datamap struct {
ID int64 `json:"id"`
Name string `json:"name"`
Description string `json:"description"`
Created time.Time `json:"created"`
DMLs []datamapLine `json:"datamap_lines"`
}
type datamapModel struct {
DB *sql.DB
}
func NewModels(db *sql.DB) Models {
return Models{
Datamaps: datamapModel{DB: db},
DatamapLines: datamapLineModel{DB: db},
}
}
func (m *datamapLineModel) Insert(dm datamap, dmls []datamapLine) (int, error) {
var datamapID int64
tx, err := m.DB.Begin()
if err != nil {
return 0, err
}
err = m.DB.QueryRow(`INSERT INTO datamaps (name, description, created)
VALUES ($1, $2, CURRENT_TIMESTAMP)
RETURNING id`, dm.Name, dm.Description).Scan(&datamapID)
if err != nil {
return 0, err
}
stmt, err := tx.Prepare(`INSERT INTO datamap_lines
(datamap_id, key, sheet, data_type, cellref)
VALUES ($1, $2, $3, $4, $5)`)
if err != nil {
return 0, err
}
defer stmt.Close()
for _, line := range dmls {
_, err := stmt.Exec(
int64(datamapID),
line.Key,
line.Sheet,
line.DataType,
line.Cellref)
if err != nil {
tx.Rollback()
return 0, err
}
}
tx.Commit()
return int(datamapID), nil
}
func (app *application) createDatamapHandler(w http.ResponseWriter, r *http.Request) {
// Parse the multipart form
err := r.ParseMultipartForm(10 << 20) // 10Mb max
if err != nil {
app.serverErrorResponse(w, r, err)
}
// Get form values
dmName := r.FormValue("name")
app.logger.Info("obtain value from form", "name", dmName)
dmDesc := r.FormValue("description")
app.logger.Info("obtain value from form", "description", dmDesc)
// Get the uploaded file and name
file, _, err := r.FormFile("file")
if err != nil {
http.Error(w, "Missing file", http.StatusBadRequest)
return
}
defer file.Close()
// parse the csv
reader := csv.NewReader(file)
var dmls []datamapLine
var dm datamap
for {
line, err := reader.Read()
if err != nil {
if err.Error() == "EOF" {
break // end of file
}
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
if len(line) != 4 {
http.Error(w, "Invalid CSV Format", http.StatusBadRequest)
return
}
dmls = append(dmls, datamapLine{
Key: line[0],
Sheet: line[1],
DataType: line[2],
Cellref: line[3],
})
}
dm = datamap{Name: dmName, Description: dmDesc, Created: time.Now(), DMLs: dmls}
err = app.writeJSONPretty(w, http.StatusOK, envelope{"datamap": dm}, nil)
if err != nil {
app.logger.Debug("writing out csv", "err", err)
app.serverErrorResponse(w, r, err)
}
fmt.Fprintf(w, "file successfully uploaded")
}
func (app *application) saveDatamapHandler(w http.ResponseWriter, r *http.Request) {
// Parse the multipart form
err := r.ParseMultipartForm(10 << 20) // 10Mb max
if err != nil {
app.serverErrorResponse(w, r, err)
}
// Get form values
dmName := r.FormValue("name")
app.logger.Info("obtain value from form", "name", dmName)
dmDesc := r.FormValue("description")
app.logger.Info("obtain value from form", "description", dmDesc)
// Get the uploaded file and name
file, _, err := r.FormFile("file")
if err != nil {
http.Error(w, "Missing file", http.StatusBadRequest)
return
}
defer file.Close()
// parse the csv
reader := csv.NewReader(file)
var dmls []datamapLine
var dm datamap
for {
line, err := reader.Read()
if err != nil {
if err.Error() == "EOF" {
break // end of file
}
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
if len(line) != 4 {
http.Error(w, "Invalid CSV Format", http.StatusBadRequest)
return
}
dmls = append(dmls, datamapLine{
ID: 0,
Key: line[0],
Sheet: line[1],
DataType: line[2],
Cellref: line[3],
})
}
dm = datamap{Name: dmName, Description: dmDesc, Created: time.Now(), DMLs: dmls}
// save to the database
_, err = app.models.DatamapLines.Insert(dm, dmls)
if err != nil {
http.Error(w, "Cannot save to database", http.StatusBadRequest)
return
}
}
func (app *application) getJSONForDatamap(w http.ResponseWriter, r *http.Request) {
// Get the DM out of the database
// dm = datamap{Name: dmName, Description: dmDesc, Created: time.Now(), DMLs: dmls}
// err = app.writeJSONPretty(w, http.StatusOK, envelope{"datamap": dm}, nil)
// if err != nil {
// app.logger.Debug("writing out csv", "err", err)
// app.serverErrorResponse(w, r, err)
// }
// fmt.Fprintf(w, "file successfully uploaded")
}
func (app *application) showDatamapHandler(w http.ResponseWriter, r *http.Request) {
id := r.PathValue("id")
app.logger.Info("the id requested", "id", id)
id_int, err := strconv.ParseInt(id, 10, 64)
if err != nil || id_int < 1 {
app.notFoundResponse(w, r)
}
fmt.Fprintf(w, "show the details for datamap %d\n", id_int)
}
func (app *application) createDatamapLine(w http.ResponseWriter, r *http.Request) {
var input datamapLine
err := json.NewDecoder(r.Body).Decode(&input)
if err != nil {
app.errorResponse(w, r, http.StatusBadRequest, err.Error())
return
}
fmt.Fprintf(w, "%v\n", input)
}
