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