· 5 years ago · Oct 08, 2020, 11:44 AM
1package main
2
3import (
4 "database/sql"
5 "fmt"
6 "log"
7 "os"
8
9 _ "github.com/mattn/go-sqlite3"
10)
11
12type user struct {
13 ID int
14 Name string
15}
16
17// 1) create a new database
18// 2) create other tables and this creates table foo but drops its contents.
19// 3) handle error if table exists
20func main() {
21 var dbName string
22 // takes dbName from command line argument if supplied
23 // else default to foo.db
24 if len(os.Args) < 2 {
25 dbName = "foo.db"
26 } else {
27 dbName = os.Args[1]
28 }
29
30 var tableName string
31 // takes tableName from command line argument if supplied
32 // else default to foo
33 if len(os.Args) < 3 {
34 tableName = "foo"
35 } else {
36 tableName = os.Args[2]
37 }
38
39 // we want to keep the database created, so we comment this line
40 // os.Remove(dbName)
41
42 db, err := sql.Open("sqlite3", dbName)
43 if err != nil {
44 log.Fatal("open:", err)
45 }
46 defer db.Close()
47
48 // for task #3, this creates table user ONLY if it does not exist yet
49 // for task #2, this does not drop table's contents so the next time we run the command
50 // we can see contents from previous sessions
51 sqlStmt := fmt.Sprintf("CREATE table IF NOT EXISTS %s (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT);", tableName)
52 _, err = db.Exec(sqlStmt)
53 if err != nil {
54 fmt.Printf("%q: %s\n", err, sqlStmt)
55 }
56
57 // this populates the table with initial contents of 10 users
58 tx, err := db.Begin()
59 if err != nil {
60 log.Fatal("begin:", err)
61 }
62 stmt, err := tx.Prepare(fmt.Sprintf("insert into %s (id, name) values(?, ?)", tableName))
63 if err != nil {
64 log.Fatal("prepare:", err)
65 }
66 defer stmt.Close()
67 for i := 0; i < 10; i++ {
68 _, err = stmt.Exec(i, fmt.Sprintf("user_%d", i))
69 if err != nil {
70 fmt.Println("insert:", err)
71 }
72 }
73 err = tx.Commit()
74 if err != nil {
75 log.Fatal("commit:", err)
76 }
77
78 // now, we can see if indeed 10 users have been created
79 var us []user
80 rows, err := db.Query(fmt.Sprintf("select id, name from %s", tableName))
81 if err != nil {
82 log.Fatal("select:", err)
83 }
84 defer rows.Close()
85 fmt.Println("==================================================")
86 fmt.Println("querying table", tableName)
87 for rows.Next() {
88 var id int
89 var name string
90 err = rows.Scan(&id, &name)
91 if err != nil {
92 log.Fatal("scan:", err)
93 }
94 fmt.Println(id, name)
95 us = append(us, user{
96 ID: id,
97 Name: name,
98 })
99 }
100 err = rows.Err()
101 if err != nil {
102 log.Fatal("rows:", err)
103 }
104 if len(us) != 10 {
105 log.Fatalf("expected 10 users, got %d users", len(us))
106 } else {
107 fmt.Println("total: 10 users")
108 }
109 fmt.Println("==================================================")
110
111 // and now we would like to query by id
112 fmt.Println("querying name for user.id = 3")
113 stmt, err = db.Prepare(fmt.Sprintf("select name from %s where id = ?", tableName))
114 if err != nil {
115 log.Fatal(err)
116 }
117 defer stmt.Close()
118 var name string
119 err = stmt.QueryRow("3").Scan(&name)
120 if err != nil {
121 log.Fatal(err)
122 }
123 fmt.Println(name)
124 fmt.Println("==================================================")
125}