/
main.go
149 lines (121 loc) · 4.17 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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
_ "github.com/mattn/go-sqlite3"
"gopkg.in/gorp.v1"
)
func main() {
// initialize the DbMap
dbmap := initMysql("post")
// dbmap := initSqlite()
defer dbmap.Db.Close()
// delete any existing rows
err := dbmap.TruncateTables()
checkErr(err, "TruncateTables failed")
// create two posts
p1 := newPost("Go 1.1 released!", "Lorem ipsum lorem ipsum")
p2 := newPost("Go 1.2 released!", "Lorem ipsum lorem ipsum")
// insert rows - auto increment PKs will be set properly after the insert
err = dbmap.Insert(&p1, &p2)
checkErr(err, "Insert failed")
// use convenience SelectInt
count, err := dbmap.SelectInt("select count(*) from posts")
checkErr(err, "select count(*) failed")
log.Println("Rows after inserting:", count)
// update a row
p2.Title = "Go 1.2 is better than ever"
count, err = dbmap.Update(&p2)
checkErr(err, "Update failed")
log.Println("Rows updated:", count)
// fetch one row - note use of "post_id" instead of "Id" since column is aliased
//
// Postgres users should use $1 instead of ? placeholders
// See 'Known Issues' below
//
err = dbmap.SelectOne(&p2, "select * from posts where post_id=?", p2.Id)
checkErr(err, "SelectOne failed")
log.Println("p2 row:", p2)
// fetch all rows
var posts []Post
_, err = dbmap.Select(&posts, "select * from posts order by post_id")
checkErr(err, "Select failed")
log.Println("All rows:")
for x, p := range posts {
log.Printf(" %d: %v\n", x, p)
}
// delete row by PK
count, err = dbmap.Delete(&p1)
checkErr(err, "Delete failed")
log.Println("Rows deleted:", count)
// delete row manually via Exec
_, err = dbmap.Exec("delete from posts where post_id=?", p2.Id)
checkErr(err, "Exec failed")
// confirm count is zero
count, err = dbmap.SelectInt("select count(*) from posts")
checkErr(err, "select count(*) failed")
log.Println("Row count - should be zero:", count)
log.Println("Done!")
}
type Post struct {
// db tag lets you specify the column name if it differs from the struct field
Id int64 `db:"post_id"`
Created int64
Title string `db:",size:50"` // Column size set to 50
Body string `db:"article_body,size:1024"` // Set both column name and size
}
func newPost(title, body string) Post {
return Post{
Created: time.Now().UnixNano(),
Title: title,
Body: body,
}
}
func initSqlite() *gorp.DbMap {
// connect to db using standard Go database/sql API
// use whatever database/sql driver you wish
db, err := sql.Open("sqlite3", "/tmp/post_db.bin")
checkErr(err, "sql.Open failed")
// construct a gorp DbMap
dbmap := &gorp.DbMap{Db: db, Dialect: gorp.SqliteDialect{}}
// add a table, setting the table name to 'posts' and
// specifying that the Id property is an auto incrementing PK
dbmap.AddTableWithName(Post{}, "posts").SetKeys(true, "Id")
// create the table. in a production system you'd generally
// use a migration tool, or create the tables via scripts
err = dbmap.CreateTablesIfNotExists()
checkErr(err, "Create tables failed")
return dbmap
}
//createDB
func createMysqlDB(db *sql.DB, DBName string) error {
sql := fmt.Sprintf(`CREATE DATABASE IF NOT EXISTS %s`, DBName)
_, err := db.Exec(sql)
return err
}
func initMysql(DBName string) *gorp.DbMap {
// connect to db using standard Go database/sql API
// use whatever database/sql driver you wish
db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/?parseTime=true")
createMysqlDB(db, DBName)
db, err = sql.Open("mysql", fmt.Sprintf("root:123456@tcp(127.0.0.1:3306)/%s?parseTime=true", DBName))
checkErr(err, "sql.Open failed")
// construct a gorp DbMap
dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{"InnoDB", "UTF8"}}
// add a table, setting the table name to 'posts' and
// specifying that the Id property is an auto incrementing PK
dbmap.AddTableWithName(Post{}, "posts").SetKeys(true, "Id")
// create the table. in a production system you'd generally
// use a migration tool, or create the tables via scripts
err = dbmap.CreateTablesIfNotExists()
checkErr(err, "Create tables failed")
return dbmap
}
func checkErr(err error, msg string) {
if err != nil {
log.Fatalln(msg, err)
}
}