-
Notifications
You must be signed in to change notification settings - Fork 0
/
main.go
138 lines (108 loc) · 3.14 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
/*
Manuals
=======
Install DB
----------
pi@host$ sudo apt-get install postgresql
Create database and its account
-------------------------------
Notice: user/password must match exist accounts in OS !!!
pi@host$ sudo -u postgres psql -U postgres
CREATE USER pi WITH PASSWORD 'xxxxxx';
CREATE DATABASE snsrobot;
GRANT ALL PRIVILEGES ON DATABASE snsrobot TO pi;
\q
# raspbian with raspberry pi 2:
pi@host$ sudo -u postgres cat <<_END >>/var/lib/postgresql/data/pg_hba.conf
# ubuntu:
pi@host$ #sudo -u postgres cat <<_END >>/etc/postgresql/9.4/main/pg_hba.conf
host all "pi" 0.0.0.0/0 trust
_END
pi@host$ sudo service postgresql restart
Create tables in host
---------------------
pi@host$ psql snsrobot
snsrobot => CREATE TABLE userinfo
(
uid serial NOT NULL,
username character varying(100) NOT NULL,
departname character varying(500) NOT NULL,
Created date,
CONSTRAINT userinfo_pkey PRIMARY KEY (uid)
)
WITH (OIDS=FALSE);
snsrobot => \q
Configure the remote access
---------------------------
pi@host$ sudo -u postgres cat <<_END >>/etc/postgresql/9.4/main/postgresql.conf
listen_addresses='*'
_END
pi@host$ sudo service postgresql restart
user@client$ psql -h 192.168.1.109 -U pi -W -d snsrobot
Usage Example
-------------
user@client$ go build
user@client$ ./testpg.exe -c pi:xxxxx@192.168.1.109/snsrobot
[Demo program]: http://astaxie.gitbooks.io/build-web-application-with-golang/content/en/05.4.html
[Remote access]: http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html
*/
package main
import (
"database/sql"
"flag"
"fmt"
_ "github.com/lib/pq"
"time"
)
func main() {
var db_conn string
flag.StringVar(&db_conn, "c", "", "like user:pass@ip/dbname")
flag.Parse()
if db_conn == "" {
flag.Usage()
return
}
dbinfo := fmt.Sprintf("postgres://%s?sslmode=disable", db_conn)
db, err := sql.Open("postgres", dbinfo)
checkErr(err)
defer db.Close()
fmt.Println("\n# Inserting values")
var lastInsertId int
err = db.QueryRow("INSERT INTO userinfo(username,departname,created) VALUES($1,$2,$3) RETURNING uid;", "astaxie", "研发部门", "2012-12-09").Scan(&lastInsertId)
checkErr(err)
fmt.Println("last inserted id =", lastInsertId)
fmt.Println("\n# Updating")
stmt, err := db.Prepare("update userinfo set username=$1 where uid=$2")
checkErr(err)
res, err := stmt.Exec("astaxieupdate", lastInsertId)
checkErr(err)
affect, err := res.RowsAffected()
checkErr(err)
fmt.Println(affect, "rows changed")
fmt.Println("\n# Querying")
rows, err := db.Query("SELECT * FROM userinfo")
checkErr(err)
for rows.Next() {
var uid int
var username string
var department string
var created time.Time
err = rows.Scan(&uid, &username, &department, &created)
checkErr(err)
fmt.Println("uid | username | department | created ")
fmt.Printf("%3v | %8v | %6v | %6v\n", uid, username, department, created)
}
fmt.Println("\n# Deleting")
stmt, err = db.Prepare("delete from userinfo where uid=$1")
checkErr(err)
res, err = stmt.Exec(lastInsertId)
checkErr(err)
affect, err = res.RowsAffected()
checkErr(err)
fmt.Println(affect, "rows changed")
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}