-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql3.go
119 lines (105 loc) · 2.33 KB
/
sql3.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
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
// TestItem : A test Item
type TestItem struct {
URL string
Name string
}
// InitDB : Create database if it doesnt exist
func InitDB(filepath string) *sql.DB {
file := fmt.Sprintf("file:%v?cache=shared&mode=rwc", filepath)
// Initialize the database
db, err := sql.Open("sqlite3", file)
if err != nil {
log.Fatal("Failed to initialize the database:", err)
}
// Check if the database connection is successful
if err := db.Ping(); err != nil {
log.Fatal("Failed to ping the database:", err)
}
log.Println("Database initialized successfully")
return db
}
// CreateTable : Create sqlite table
func CreateTable(db *sql.DB) {
// create table if not exists
sqlTable := `
CREATE TABLE IF NOT EXISTS docker_pool(
url string primary key,
name string
);
`
_, err := db.Exec(sqlTable)
check(err)
}
// ReadItem : Read item from database
func ReadItem(db *sql.DB) (url string) {
sqlReadall := `
SELECT Url FROM docker_pool limit 1;
`
stmt, err := db.Prepare(sqlReadall)
check(err)
err = stmt.QueryRow().Scan(&url)
check(err)
stmt.Close()
return url
}
// StoreItem : Store item in database
func StoreItem(db *sql.DB, items []TestItem) {
sqlAdditem := `
INSERT OR REPLACE INTO docker_pool(
Url,
Name
) values(?, ?)
`
routeSQL, err := db.Prepare(sqlAdditem)
check(err)
for _, item := range items {
tx, err := db.Begin()
check(err)
_, err = tx.Stmt(routeSQL).Exec(item.URL, item.Name)
if err != nil {
log.Println(err)
log.Println("doing rollback")
err = tx.Rollback()
check(err)
} else {
err = tx.Commit()
check(err)
}
}
}
// DelItem : Delete item in database
func DelItem(db *sql.DB, url string) {
sqlDelitem := `
DELETE FROM docker_pool where Url = ?
`
stmt, err := db.Prepare(sqlDelitem)
check(err)
check(err)
_, err = stmt.Exec(url)
check(err)
stmt.Close()
}
// DelName : delete item with a name from database
func DelName(db *sql.DB, name string) {
routeSQL, err := db.Prepare("delete from docker_pool where name = ?;")
check(err)
tx, err := db.Begin()
check(err)
_, err = tx.Stmt(routeSQL).Exec(name)
if err != nil {
log.Println(err)
log.Println("doing rollback")
err = tx.Rollback()
check(err)
} else {
err = tx.Commit()
check(err)
}
}