An Example

Expected output:

open your task.db file in sqlite3 like this

        [Tasks] $ sqlite3 task.db
        sqlite> select title from task limit 1;
        Publish on github

Now this output should match with the one we see at localhost:8080

After running the file, go to localhost:8080 and localhost:8080/add

file ~/main/main.go

package main

import (
    "database/sql"
    "fmt"
    "log"
    "net/http"
    "time"

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

var database *sql.DB
var err error

//Task is the struct used to identify tasks
type Task struct {
    Id      int
    Title   string
    Content string
    Created string
}

//Context is the struct passed to templates
type Context struct {
    Tasks      []Task
    Navigation string
    Search     string
    Message    string
}

func init() {
    database, err = sql.Open("sqlite3", "./tasks.db")
    if err != nil {
        fmt.Println(err)
    }
}

func main() {
    http.HandleFunc("/", ShowAllTasksFunc)
    http.HandleFunc("/add/", AddTaskFunc)
    fmt.Println("running on 8080")
    log.Fatal(http.ListenAndServe(":8080", nil))
}

//ShowAllTasksFunc is used to handle the "/" URL which is the default one
func ShowAllTasksFunc(w http.ResponseWriter, r *http.Request) {
    if r.Method == "GET" {
        context := GetTasks() //true when you want non deleted notes
        w.Write([]byte(context.Tasks[0].Title))
    } else {
        http.Redirect(w, r, "/", http.StatusFound)
    }
}

func GetTasks() Context {
    var task []Task
    var context Context
    var TaskID int
    var TaskTitle string
    var TaskContent string
    var TaskCreated time.Time
    var getTasksql string

    getTasksql = "select id, title, content, created_date from task;"

    rows, err := database.Query(getTasksql)
    if err != nil {
        fmt.Println(err)
    }
    defer rows.Close()
    for rows.Next() {
        err := rows.Scan(&TaskID, &TaskTitle, &TaskContent, &TaskCreated)
        if err != nil {
            fmt.Println(err)
        }
        TaskCreated = TaskCreated.Local()
        a := Task{Id: TaskID, Title: TaskTitle, Content: TaskContent,
            Created: TaskCreated.Format(time.UnixDate)[0:20]}
        task = append(task, a)
    }
    context = Context{Tasks: task}
    return context
}

//AddTaskFunc is used to handle the addition of new task, "/add" URL
func AddTaskFunc(w http.ResponseWriter, r *http.Request) {
    title := "random title"
    content := "random content"
    truth := AddTask(title, content)
    if truth != nil {
        log.Fatal("Error adding task")
    }
    w.Write([]byte("Added task"))
}

//AddTask is used to add the task in the database
func AddTask(title, content string) error {
    query:="insert into task(title, content, created_date, last_modified_at)\ 
    values(?,?,datetime(), datetime())"
    restoreSQL, err := database.Prepare(query)
    if err != nil {
        fmt.Println(err)
    }
    tx, err := database.Begin()
    _, err = tx.Stmt(restoreSQL).Exec(title, content)
    if err != nil {
        fmt.Println(err)
        tx.Rollback()
    } else {
        log.Print("insert successful")
        tx.Commit()
    }
    return err
}

Homework

The homework is to split the code into packages and get it to work, the type definition goes into the types/types.go file, the handler definition goes into the views/views.go, the database read and write methods go into the db/db.go. Make sure that after you refactor the code, that the code runs.

Links

-Previous section -Next section