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