1package main
2
3import (
4	"database/sql"
5	"encoding/json"
6	"fmt"
7	go_n1ql "github.com/couchbase/go_n1ql"
8	"log"
9)
10
11func main() {
12
13	n1ql, err := sql.Open("n1ql", "http://localhost:8093")
14	if err != nil {
15		log.Fatal(err)
16	}
17
18	err = n1ql.Ping()
19	if err != nil {
20		log.Fatal(err)
21	}
22
23	// Set query parameters
24	ac := []byte(`[{"user": "admin:Administrator", "pass": "asdasd"}]`)
25	go_n1ql.SetQueryParams("creds", string(ac))
26	go_n1ql.SetQueryParams("timeout", "10s")
27	go_n1ql.SetQueryParams("scan_consistency", "request_plus")
28
29	rows, err := n1ql.Query("select element (select * from contacts)")
30
31	if err != nil {
32		log.Fatal(err)
33	}
34
35	for rows.Next() {
36		var contacts string
37		if err := rows.Scan(&contacts); err != nil {
38			log.Fatal(err)
39		}
40		log.Printf("Row returned %s : \n", contacts)
41	}
42
43	if err := rows.Err(); err != nil {
44		log.Fatal(err)
45	}
46
47	rows.Close()
48
49	var name string
50	rows, err = n1ql.Query("select name,type from contacts")
51
52	if err != nil {
53		log.Fatal(err)
54	}
55	for rows.Next() {
56		var contacts string
57		if err := rows.Scan(&contacts, &name); err != nil {
58			log.Fatal(err)
59		}
60		log.Printf("Row returned %s %s: \n", contacts, name)
61	}
62
63	if err := rows.Err(); err != nil {
64		log.Fatal(err)
65	}
66
67	rows.Close()
68
69	// if the following option is set, go_n1ql will return metrics as the last row
70	go_n1ql.SetPassthroughMode(true)
71	name = "dave"
72	rows, err = n1ql.Query("select * from contacts unnest contacts.children where contacts.name = ? and children.age > ?", name, 10)
73
74	if err != nil {
75		log.Fatal(err)
76	}
77	for rows.Next() {
78		var contacts string
79		if err := rows.Scan(&contacts); err != nil {
80			log.Fatal(err)
81		}
82		log.Printf("Row returned %s : \n", contacts)
83	}
84
85	if err := rows.Err(); err != nil {
86		log.Fatal(err)
87	}
88
89	rows.Close()
90
91	rows, err = n1ql.Query("select name,type from contacts")
92
93	if err != nil {
94		log.Fatal(err)
95	}
96	for rows.Next() {
97		var contacts string
98		if err := rows.Scan(&contacts, &name); err != nil {
99			log.Fatal(err)
100		}
101		log.Printf("Row returned %s %s: \n", contacts, name)
102	}
103
104	if err := rows.Err(); err != nil {
105		log.Fatal(err)
106	}
107
108	rows.Close()
109
110	// explain statement
111	rows, err = n1ql.Query("explain select * from contacts")
112
113	if err != nil {
114		log.Fatal(err)
115	}
116	for rows.Next() {
117		var contacts string
118		if err := rows.Scan(&contacts); err != nil {
119			log.Fatal(err)
120		}
121		log.Printf("Row returned %s : \n", contacts)
122	}
123
124	if err := rows.Err(); err != nil {
125		log.Fatal(err)
126	}
127
128	rows.Close()
129
130	go_n1ql.SetPassthroughMode(false)
131
132	// prepared statements with positional args
133
134	stmt, err := n1ql.Prepare("select personal_details, shipped_order_history from users_with_orders where doc_type=? and personal_details.age = ?")
135
136	if err != nil {
137		log.Fatal(err)
138	}
139
140	rows, err = stmt.Query("user_profile", 60)
141	if err != nil {
142		log.Fatal(err)
143	}
144
145	for rows.Next() {
146		var personal, shipped string
147		if err := rows.Scan(&personal, &shipped); err != nil {
148			log.Fatal(err)
149		}
150		log.Printf("Row returned personal_details: %s shipped_order_history %s : \n", personal, shipped)
151	}
152
153	if err := rows.Err(); err != nil {
154		log.Fatal(err)
155	}
156
157	rows.Close()
158
159	// Exec examples
160	result, err := n1ql.Exec("Upsert INTO contacts values (\"irish\",{\"name\":\"irish\", \"type\":\"contact\"})")
161	if err != nil {
162		log.Fatal(err)
163	}
164
165	rowsAffected, err := result.RowsAffected()
166	if err != nil {
167		log.Fatal(err)
168	}
169	log.Printf("Rows affected %d", rowsAffected)
170
171	stmt, err = n1ql.Prepare("Upsert INTO contacts values (?,?)")
172	if err != nil {
173		log.Fatal(err)
174	}
175
176	// Map Values need to be marshaled
177	value, _ := json.Marshal(map[string]interface{}{"name": "irish", "type": "contact"})
178	result, err = stmt.Exec("irish4", value)
179	if err != nil {
180		log.Fatal(err)
181	}
182
183	rowsAffected, err = result.RowsAffected()
184	if err != nil {
185		log.Fatal(err)
186	}
187	log.Printf("Rows affected %d", rowsAffected)
188
189	for i := 0; i < 20; i++ {
190		key := fmt.Sprintf("irish%d", i)
191		result, err = stmt.Exec(key, value)
192		if err != nil {
193			log.Fatal(err)
194		}
195
196		ra, err := result.RowsAffected()
197		if err != nil {
198			log.Fatal(err)
199		}
200		rowsAffected += ra
201	}
202
203	log.Printf("Total Rows Affected %d", rowsAffected)
204
205	stmt.Close()
206	result, err = stmt.Exec("test", "this shouldn't work")
207	if err == nil {
208		log.Fatal("Statement not closed")
209	}
210
211	result, err = n1ql.Exec("delete from contacts use keys ? ", "irish")
212	if err != nil {
213		log.Fatal(err)
214	}
215
216	keys := make([]string, 0)
217	for i := 0; i < 20; i++ {
218		keys = append(keys, fmt.Sprintf("irish%d", i))
219	}
220
221	value, _ = json.Marshal(keys)
222	result, err = n1ql.Exec("delete from contacts use keys ?", value)
223	if err != nil {
224		log.Fatal(err)
225	}
226
227	go_n1ql.UnsetQueryParams("scan_consistency")
228	result, err = n1ql.Exec("delete from contacts use keys ?", value)
229	if err != nil {
230		log.Fatal(err)
231	}
232
233	go_n1ql.UnsetQueryParams("scan_consistency")
234
235	// error expected
236	go_n1ql.SetQueryParams("scan_consistency", "rubbish_plus")
237	result, err = n1ql.Exec("delete from contacts use keys ?", value)
238	if err == nil {
239		log.Fatal("Error expected")
240	}
241
242}
243