-
Notifications
You must be signed in to change notification settings - Fork 0
/
main.go
126 lines (104 loc) · 2.77 KB
/
main.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
package main
import (
"database/sql"
"fmt"
sqlx "github.com/jmoiron/sqlx"
"log"
"time"
//Driver for sqlite
sqlite "github.com/mattn/go-sqlite3"
)
// In this test, we create a disk-based database and a memory only one (using
// the ATTACH sql command)
//
// Also, I tested connection hooks (though we have no reason to use them at
// present)
//
func main() {
sql3Conn := []*sqlite.SQLiteConn{}
sql.Register("sqlite3ConnectionCatchingDriver",
&sqlite.SQLiteDriver{
ConnectHook: func(newConn *sqlite.SQLiteConn) error {
sql3Conn = append(sql3Conn, newConn)
return nil
},
},
)
db, err := sqlx.Connect("sqlite3ConnectionCatchingDriver", "myDb.sqlite?cache=shared")
if err != nil {
log.Fatal("Connect: ", err)
}
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS main.person (
id INTEGER PRIMARY KEY,
name TEXT
);
ATTACH DATABASE 'file::memory:?cache=shared' AS mem;
CREATE TABLE IF NOT EXISTS mem.sessionActivity (
id INTEGER PRIMARY KEY,
personId INTEGER NOT NULL,
dateTime DATETIME DEFAULT CURRENT_TIMESTAMP
);
`)
if err != nil {
log.Fatal("Schema Build: ", err)
}
insertPerson, err := db.PrepareNamed(`
INSERT OR IGNORE INTO main.person (id, name)
VALUES(:id, :name);
`)
if err != nil {
log.Fatal("Prepare Statement 1: ", err)
}
insertSessionActivity, err := db.PrepareNamed(`
INSERT OR IGNORE INTO mem.sessionActivity (personId)
VALUES(:personId);
`)
if err != nil {
log.Fatal("Prepare Statement 2: ", err)
}
_, err = insertPerson.Exec(map[string]interface{}{
"id": 1,
"name": "Jim",
})
if err != nil {
log.Fatal("insert person: ", err)
}
_, err = insertSessionActivity.Exec(map[string]interface{}{
"personId": 1,
})
if err != nil {
log.Fatal("insert session activity 1: ", err)
}
time.Sleep(time.Second)
_, err = insertSessionActivity.Exec(map[string]interface{}{
"personId": 1,
})
if err != nil {
log.Fatal("insert session activity 2: ", err)
}
// This query is a join between a memory-db table and a disk-db table. It works!
rows, err := db.Query(`
SELECT
main.person.name as personName,
main.person.id as personId,
mem.sessionActivity.dateTime as dateTime
FROM mem.sessionActivity
LEFT OUTER JOIN main.person
ON mem.sessionActivity.personId = main.person.id
ORDER BY
mem.sessionActivity.dateTime ASC
`)
if err != nil {
log.Fatal("Select : ", err)
}
// iterate over each row
for rows.Next() {
var personName string
var personID int64
var dateTime time.Time
err = rows.Scan(&personName, &personID, &dateTime)
fmt.Println("Result:", personID, personName, dateTime)
}
fmt.Println("Finished")
}