forked from marcboeker/go-duckdb
/
simple.go
138 lines (115 loc) · 3.35 KB
/
simple.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
package main
import (
"context"
"database/sql"
"log"
"time"
_ "github.com/PrajwalUppoor/go-duckdb"
)
var db *sql.DB
type user struct {
name string
age int
height float32
awesome bool
bday time.Time
}
func main() {
var err error
db, err = sql.Open("duckdb", "?access_mode=READ_WRITE")
if err != nil {
log.Fatal(err)
}
defer db.Close()
check(db.Ping())
setting := db.QueryRowContext(context.Background(), "SELECT current_setting('access_mode')")
var am string
check(setting.Scan(&am))
log.Printf("DB opened with access mode %s", am)
check(db.ExecContext(context.Background(), "CREATE TABLE users(name VARCHAR, age INTEGER, height FLOAT, awesome BOOLEAN, bday DATE)"))
check(db.ExecContext(context.Background(), "INSERT INTO users VALUES('marc', 99, 1.91, true, '1970-01-01')"))
check(db.ExecContext(context.Background(), "INSERT INTO users VALUES('macgyver', 70, 1.85, true, '1951-01-23')"))
rows, err := db.QueryContext(
context.Background(), `
SELECT name, age, height, awesome, bday
FROM users
WHERE (name = ? OR name = ?) AND age > ? AND awesome = ?`,
"macgyver", "marc", 30, true,
)
check(err)
defer rows.Close()
for rows.Next() {
u := new(user)
err := rows.Scan(&u.name, &u.age, &u.height, &u.awesome, &u.bday)
if err != nil {
log.Fatal(err)
}
log.Printf(
"%s is %d years old, %.2f tall, bday on %s and has awesomeness: %t\n",
u.name, u.age, u.height, u.bday.Format(time.RFC3339), u.awesome,
)
}
check(rows.Err())
res, err := db.ExecContext(context.Background(), "DELETE FROM users")
check(err)
ra, _ := res.RowsAffected()
log.Printf("Deleted %d rows\n", ra)
runTransaction()
testPreparedStmt()
}
func check(args ...interface{}) {
err := args[len(args)-1]
if err != nil {
panic(err)
}
}
func runTransaction() {
log.Println("Starting transaction...")
tx, err := db.Begin()
check(err)
check(
tx.ExecContext(
context.Background(),
"INSERT INTO users VALUES('gru', 25, 1.35, false, '1996-04-03')",
),
)
row := tx.QueryRowContext(context.Background(), "SELECT COUNT(*) FROM users WHERE name = ?", "gru")
var count int64
check(row.Scan(&count))
if count > 0 {
log.Println("User Gru was inserted")
}
log.Println("Rolling back transaction...")
check(tx.Rollback())
row = db.QueryRowContext(context.Background(), "SELECT COUNT(*) FROM users WHERE name = ?", "gru")
check(row.Scan(&count))
if count > 0 {
log.Println("Found user Gru")
} else {
log.Println("Couldn't find user Gru")
}
}
func testPreparedStmt() {
stmt, err := db.PrepareContext(context.Background(), "INSERT INTO users VALUES(?, ?, ?, ?, ?)")
check(err)
defer stmt.Close()
check(stmt.ExecContext(context.Background(), "Kevin", 11, 0.55, true, "2013-07-06"))
check(stmt.ExecContext(context.Background(), "Bob", 12, 0.73, true, "2012-11-04"))
check(stmt.ExecContext(context.Background(), "Stuart", 13, 0.66, true, "2014-02-12"))
stmt, err = db.PrepareContext(context.Background(), "SELECT * FROM users WHERE age > ?")
check(err)
rows, err := stmt.QueryContext(context.Background(), 1)
check(err)
defer rows.Close()
for rows.Next() {
u := new(user)
err := rows.Scan(&u.name, &u.age, &u.height, &u.awesome, &u.bday)
if err != nil {
log.Fatal(err)
}
log.Printf(
"%s is %d years old, %.2f tall, bday on %s and has awesomeness: %t\n",
u.name, u.age, u.height, u.bday.Format(time.RFC3339), u.awesome,
)
}
}