/
db.go
174 lines (146 loc) · 3.24 KB
/
db.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
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
package main
import (
"database/sql"
"fmt"
"github.com/astaxie/beedb"
_ "github.com/mattn/go-sqlite3"
"time"
)
/*
CREATE TABLE `userinfo` (
`uid` INTEGER PRIMARY KEY AUTOINCREMENT,
`username` VARCHAR(64) NULL,
`password` VARCHAR(64) NULL,
`departname` VARCHAR(64) NULL,
`created` DATE NULL
);
CREATE TABLE `userdeatail` (
`uid` INT(10) NULL,
`intro` TEXT NULL,
`profile` TEXT NULL,
PRIMARY KEY (`uid`)
);
*/
var orm beedb.Model
type Userinfo struct {
Uid int `beedb:"PK"`
Username string
Password string
Departname string
Created string
}
type CompanySkillUser struct {
Cid int
Company string
Skill string
Uid int
Username string
}
type Companyinfo struct {
Cid int `beedb:"PK"`
Company string
CompanyInfo string
AdminUid int
Username string
Created string
Skills string // tech,sales,
}
func xmain() {
db, err := sql.Open("sqlite3", "./httpserver.s3db")
if err != nil {
panic(err)
}
orm = beedb.New(db)
insert()
insertsql()
a := selectone()
fmt.Println(a)
b := selectall()
fmt.Println(b)
update()
updatesql()
findmap()
groupby()
jointable()
deleteone()
deletesql()
deleteall()
}
func insert() {
//save data
var saveone Userinfo
saveone.Username = "Test Add User"
saveone.Departname = "Test Add Departname"
saveone.Created = time.Now().Format("2006-01-02 15:04:05")
orm.Save(&saveone)
fmt.Println(saveone)
}
func insertsql() {
// add one
add := make(map[string]interface{})
add["username"] = "astaxie"
add["departname"] = "cloud develop"
add["created"] = "2012-12-02"
orm.SetTable("userinfo").Insert(add)
}
func selectone() Userinfo {
//get one info
var one Userinfo
orm.Where("uid=?", 1).Find(&one)
return one
}
func selectall() []Userinfo {
//get all data
var alluser []Userinfo
orm.Limit(10).Where("uid>?", 1).FindAll(&alluser)
return alluser
}
func update() {
// //update data
var saveone Userinfo
saveone.Uid = 1
saveone.Username = "Update Username"
saveone.Departname = "Update Departname"
saveone.Created = time.Now().Format("2006-01-02 15:04:05")
orm.Save(&saveone)
fmt.Println(saveone)
}
func updatesql() {
//original SQL update
t := make(map[string]interface{})
t["username"] = "updateastaxie"
//update one
orm.SetTable("userinfo").SetPK("uid").Where(2).Update(t)
//update batch
orm.SetTable("userinfo").Where("uid>?", 3).Update(t)
}
func findmap() {
//Original SQL Backinfo resultsSlice []map[string][]byte
//default PrimaryKey id
c, _ := orm.SetTable("userinfo").SetPK("uid").Where(2).Select("uid,username").FindMap()
fmt.Println(c)
}
func groupby() {
//Original SQL Group By
b, _ := orm.SetTable("userinfo").GroupBy("username").Having("username='updateastaxie'").FindMap()
fmt.Println(b)
}
func jointable() {
//Original SQL Join Table
a, _ := orm.SetTable("userinfo").Join("LEFT", "userdeatail", "userinfo.uid=userdeatail.uid").Where("userinfo.uid=?", 1).Select("userinfo.uid,userinfo.username,userdeatail.profile").FindMap()
fmt.Println(a)
}
func deleteone() {
// // //delete one data
saveone := selectone()
orm.Delete(&saveone)
}
func deletesql() {
//original SQL delete
orm.SetTable("userinfo").Where("uid>?", 2).DeleteRow()
}
func deleteall() {
// //delete all data
alluser := selectall()
orm.DeleteAll(&alluser)
}