· 5 years ago · Jul 27, 2020, 07:26 PM
1package main
2
3import (
4 "database/sql"
5 "fmt"
6 "log"
7 "os"
8 "time"
9
10 _ "github.com/mattn/go-sqlite3"
11)
12
13var start time.Time
14
15type classManager struct {
16 db *sql.DB
17}
18
19func (d classManager) createTable() error {
20 db, err := d.db.Prepare("CREATE TABLE IF NOT EXISTS classes (ClassID INTEGER PRIMARY KEY, ClassName TEXT)")
21 if err != nil {
22 return err
23 }
24 _, err = db.Exec()
25 if err != nil {
26 return err
27 }
28 db, err = d.db.Prepare("CREATE TABLE IF NOT EXISTS students (FirstName TEXT, LastName TEXT, ClassID TEXT)")
29 if err != nil {
30 return err
31 }
32 _, err = db.Exec()
33 if err != nil {
34 return err
35 }
36 return nil
37}
38
39func timeTaken(t time.Time) {
40 fmt.Println()
41 fmt.Println("Execution took -> ", time.Since(t).Seconds(), " Seconds")
42}
43
44func (d classManager) getID(classname string) (int, error) {
45 data, err := d.db.Query("SELECT ClassID, ClassName FROM classes")
46 if err != nil {
47 return 0, err
48 }
49 var cid int
50 var cname string
51 defer data.Close()
52 for data.Next() {
53 err := data.Scan(&cid, &cname)
54 if err != nil {
55 return 0, err
56 }
57 if cname == classname {
58 return cid, nil
59
60 }
61 }
62 return 0, nil
63}
64
65func (d classManager) getClassName(id int) (string, error) {
66 data, err := d.db.Query("SELECT ClassName, ClassID FROM classes")
67 if err != nil {
68 return "", err
69 }
70
71 var cid int
72 var cname string
73 defer data.Close()
74 for data.Next() {
75 err := data.Scan(&cname, &cid)
76 if err != nil {
77 return "", err
78 }
79 if id == cid {
80 return cname, nil
81 }
82 }
83 return "", nil
84}
85
86func (d classManager) listClasses() error {
87
88 data, err := d.db.Query("SELECT ClassID, ClassName FROM classes")
89 if err != nil {
90 return err
91 }
92 var cid int
93 var cname string
94
95 defer data.Close()
96 for data.Next() {
97 err := data.Scan(&cid, &cname)
98 if err != nil {
99 return err
100 }
101 fmt.Println(cid, " ", cname)
102 }
103 return nil
104
105}
106
107func (d classManager) listStudents(className string) error {
108 data, err := d.db.Query("SELECT FirstName, LastName, ClassID FROM students")
109 if err != nil {
110 return err
111 }
112 var fname, lname string
113 var cid int
114 data.Close()
115 for data.Next() {
116 err := data.Scan(&fname, &lname, &cid)
117 if err != nil {
118 return err
119 }
120 id, err := d.getID(className)
121 if err != nil {
122 return err
123 }
124 if cid == id {
125 fmt.Println(fname, " ", lname, " ", className)
126 }
127 }
128 return nil
129}
130
131func (d classManager) allStudents() error {
132
133 data, err := d.db.Query("SELECT FirstName, LastName, ClassID FROM students")
134 if err != nil {
135 return err
136 }
137 var fname, lname string
138 var cid int
139 defer data.Close()
140 for data.Next() {
141 err := data.Scan(&fname, &lname, &cid)
142 if err != nil {
143 return err
144 }
145 className, err := d.getClassName(cid)
146 if err != nil {
147 return err
148 }
149 fmt.Println(fname, " ", lname, " ", className)
150 }
151 return nil
152}
153
154func (d classManager) addClass(className string) error {
155
156 // Adding new class with unique id in table
157 db, err := d.db.Prepare("INSERT INTO classes (className) VALUES(?)")
158 if err != nil {
159 return err
160 }
161 _, err = db.Exec(className)
162 fmt.Println("Added Class ", className, " Successfully.")
163 if err != nil {
164 return err
165 }
166 return nil
167}
168
169func (d classManager) addStudent(classname, firstName, lastName string) error {
170
171 // Adding new student in student table with unique id
172 id, err := d.getID(classname)
173 if err != nil {
174 return err
175 }
176 db, err := d.db.Prepare("INSERT INTO students (FirstName, LastName, ClassID) VALUES (?, ?, ?)")
177 if err != nil {
178 return err
179 }
180 _, err = db.Exec(firstName, lastName, id)
181 fmt.Println("Added new student to class ", classname, " Successfully.")
182 if err != nil {
183 return err
184 }
185 return nil
186}
187
188func main() {
189
190 // Execution Time
191 defer timeTaken(start)
192 start = time.Now()
193
194 // Open/Close and Assign DB
195 database, err := sql.Open("sqlite3", "./school.db")
196 if err != nil {
197 log.Fatal(err)
198 }
199 defer database.Close()
200 sdb := classManager{
201 db: database,
202 }
203
204 // Create "Default" Tables
205 err = sdb.createTable()
206 if err != nil {
207 log.Fatal(err)
208 }
209
210 // Command Taking and Calling Functions
211 cmd := os.Args[1] + os.Args[2]
212 if len(os.Args) < 3 {
213 fmt.Println("Please enter some command!")
214 return
215 }
216
217 if cmd == "addstudent" {
218 if len(os.Args) < 6 {
219 fmt.Println("Please specify className, firstName, lastName")
220 return
221 }
222 classname, firstName, lastName := os.Args[3], os.Args[4], os.Args[5]
223 sdb.addStudent(classname, firstName, lastName)
224 }
225
226 if cmd == "addclass" {
227 if len(os.Args) < 4 {
228 fmt.Println("Please specify className")
229 return
230 }
231 className := os.Args[3]
232 sdb.addClass(className)
233 }
234
235 if cmd == "listclasses" {
236 err := sdb.listClasses()
237 if err != nil {
238 log.Fatal(err)
239 }
240 }
241
242 if cmd == "liststudents" {
243 if len(os.Args) < 4 {
244 fmt.Println("Please specify class name!")
245 }
246 className := os.Args[3]
247 err := sdb.listStudents(className)
248 if err != nil {
249 log.Fatal(err)
250 }
251 }
252 if cmd == "lastudents" {
253 err := sdb.allStudents()
254 if err != nil {
255 log.Fatal(err)
256 }
257 }
258
259}
260