package main

import (
    "database/sql"
    "fmt"

    // 即使导入了 sql, 这里也要显示导入 go-sqlite3,
    // 否则报错 open db err: sql: unknown driver "sqlite3" (forgotten import?)
    _ "github.com/mattn/go-sqlite3"
)

const sql_file string = "/tmp/maan.sqlite3"
const create string = `
    CREATE TABLE trade (
        id INTEGER NOT NULL,
        date DATETIME NOT NULL,
        code VARCHAR NOT NULL,
        name VARCHAR,
        op INTEGER,
        number INTEGER,
        price FLOAT,
        PRIMARY KEY (id)
    );
    INSERT INTO trade (date, code, name, op, number, price)
    VALUES ('2022-06-01', '1234', '平安', 1, 100, 90.0);
    INSERT INTO trade (date, code, name, op, number, price)
    VALUES ('2022-06-02', '1234', '平安', 0, 100, 92.0);
    INSERT INTO trade (date, code, name, op, number, price)
    VALUES ('2022-06-03', '2234', '石油', 0, 4, 6.0);
    INSERT INTO trade (date, code, name, op, number, price)
    VALUES ('2022-06-05', '3234', '媒体', 1, 5, 7.0);
    `

func doExec(db *sql.DB, sql string, args ...interface{}) {
    stmt, err := db.Prepare(sql)
    if err != nil {
        fmt.Println("database error", err)
        panic(err)
    }
    defer stmt.Close()
    stmt.Exec(args...)

}
func main() {
    // open db
    db, err := sql.Open("sqlite3", sql_file)
    if err != nil {
        fmt.Println("open db err:", err)
        return
    }
    // lazy close db
    defer db.Close()

    // create db
    if _, err := db.Exec(create); err != nil {
        fmt.Println("create table err:", err)
        return
    }

    // insert row
    sql := `INSERT INTO trade (date, code, name, op, number, price) 
 VALUES ('2022-06-09', '1234', '平安', 1, 120, 52.0)`
    doExec(db, sql)

    // delete row
    sql = "DELETE FROM trade WHERE id=?"
    doExec(db, sql, 1)

    // update row
    sql = "UPDATE trade SET NAME=? WHERE id=2"
    doExec(db, sql, "更新")

    // query sql
    rows, err := db.Query("SELECT id, name, date, number, price FROM trade WHERE code=?", "1234")
    if err != nil {
        fmt.Println("query db err:", err)
        return
    }
    defer rows.Close()
    for rows.Next() {
        var uid int
        var name string
        var date string
        var number string
        var price float32
        rows.Scan(&uid, &name, &date, &number, &price)
        fmt.Printf("id: %d, name=%s, date: %s, number: %s, price: %f\n", uid, name, date, number, price)
    }
}