Server-Side Scripting/SQL Databases/Go
Appearance
routes/lesson9.go
[edit | edit source]// This program creates and displays a temperature database
// with options to insert, update, and delete records.
//
// References:
// https://golang.org/doc/
// https://astaxie.gitbooks.io/build-web-application-with-golang/content/en/05.3.html
// https://pkg.go.dev/database/sql
package routes
import (
"database/sql"
"html/template"
"log"
"net/http"
"path/filepath"
"strconv"
_ "github.com/mattn/go-sqlite3"
)
func Lesson9(response http.ResponseWriter, request *http.Request) {
response.Header().Set("Content-Type", "text/html; charset=utf-8")
type Data struct {
Table template.HTML
}
result := ""
switch request.Method {
case "GET":
result = processGet9()
case "POST":
result = processPost9(request)
default:
result = "Unexpected request method: " + request.Method
}
data := Data{template.HTML(result)}
path := filepath.Join("templates", "lesson9.html")
parsed, _ := template.ParseFiles(path)
parsed.Execute(response, data)
}
func processGet9() string {
checkDatabase()
result := getData()
return result
}
func processPost9(request *http.Request) string {
country := request.FormValue("country")
temperature := request.FormValue("temperature")
if !countryExists(country) {
insertCountry(country, temperature)
} else if (temperature != "") {
updateCountry(country, temperature)
} else {
deleteCountry(country)
}
result := getData()
return result
}
func checkDatabase() {
query := `
SELECT COUNT(*) AS Count FROM sqlite_master
WHERE name = 'Countries';`
rows := sqliteQuery(query)
var count int
rows.Next()
rows.Scan(&count)
rows.Close()
if count == 1 {
return
}
query = `
CREATE TABLE Countries(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Country TEXT UNIQUE NOT NULL,
Temperature REAL NOT NULL);`
sqliteExec(query)
}
func getData() string {
query := "SELECT ID, Country, Temperature FROM Countries;"
rows := sqliteQuery(query)
result := "<table><tr><th>ID</th>"
result += "<th>Country</th>"
result += "<th>Temperature</th></tr>"
for rows.Next() {
var id int
var country string
var temperature float64
err := rows.Scan(&id, &country, &temperature)
if err != nil {
log.Fatal(err)
}
result += "<tr><td>" + strconv.Itoa(id) + "</td>"
result += "<td>" + country + "</td>"
result += "<td>"+ strconv.FormatFloat(temperature, 'f', 1, 64) + "</td></tr>"
}
result += "</table>"
rows.Close()
return result;
}
func countryExists(country string) bool {
query := `
SELECT EXISTS(
SELECT * FROM Countries
WHERE Country = ?) AS Count;`
rows := sqliteQuery(query, country)
var count int
rows.Next()
rows.Scan(&count)
rows.Close()
return count == 1
}
func insertCountry(country string, temperature string) {
query := `
INSERT INTO Countries (Country, Temperature)
VALUES(?, ?);`
sqliteExec(query, country, temperature);
}
func updateCountry(country string, temperature string) {
query := `
UPDATE Countries
SET Temperature = ?
WHERE Country = ?;`
sqliteExec(query, temperature, country);
}
func deleteCountry(country string) {
query := `
DELETE FROM Countries
WHERE Country = ?;`
sqliteExec(query, country);
}
func sqliteQuery(query string, parameters ...interface{}) *sql.Rows {
db, err := sql.Open("sqlite3", "./temperature.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
var result *sql.Rows
if parameters == nil {
result, err = db.Query(query)
} else {
result, err = db.Query(query, parameters...)
}
if err != nil {
log.Fatal(err)
}
return result
}
func sqliteExec(query string, parameters ...interface{}) sql.Result {
db, err := sql.Open("sqlite3", "./temperature.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
var result sql.Result
if parameters == nil {
result, err = db.Exec(query)
} else {
result, err = db.Exec(query, parameters...)
}
if err != nil {
log.Fatal(err)
}
return result
}
templates/lesson9.html
[edit | edit source]<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Lesson 9</title>
<link rel="stylesheet" href="styles.css">
</head>
<body>
<h1>Temperature Data Entry</h1>
<p>Enter country and temperature. Enter again to update.</p>
<p>Enter country without temperature to delete.</p>
<form method="POST">
<p><label for="country">Country:</label>
<input type="text" id="country" name="country" required>
</p>
<p><label for="stop">Temperature:</label>
<input type="text" id="temperature" name="temperature">
</p>
<p><input type="submit" name="submit" value="Submit"></p>
</form>
<hr>
{{.Table}}
</body>
</html>
Try It
[edit | edit source]See Server-Side Scripting/Routes and Templates/Go to create a test environment.