· 8 years ago · Feb 10, 2018, 11:48 AM
1#!/bin/bash
2
3#DBMS Program Project that make DataBase Management System Functions
4#Team (No.5):-
5# 1. Khaled Rakha
6# 2. Moaaz Magdy
7# 3. Omaima Ismail
8# 4. Riham Abdul Maguid
9# 5. Rogina Badr
10
11clear
12
13#First Function: Create New DataBase
14Create_DB()
15{
16 loop=1
17 while [ $loop -eq 1 ]
18 do
19 echo "Please enter new DataBase name: "
20 read dbName
21
22 if [ -d $dbName ]
23 then
24 echo " Sorry, DataBase ($dbName) already exists, please try again "
25 echo " ================================================================= "
26 Create_DB
27 else
28 mkdir $dbName
29 echo " DataBase ($dbName) Created Sucessfully "
30 echo " ========================================= "
31
32 echo "Do you want to create new DataBase? (y/n)"
33 read ch
34 if [ $ch = 'y' ]
35 then
36 loop=1
37 echo " ======================================= "
38 elif [ $ch = 'n' ]
39 then
40 loop=0
41 echo " ========================================= "
42 else
43 echo " Sorry you entered wrong input, please enter (y/n) "
44 echo "Note:: This is your last try"
45 echo "Do you want to create new DataBase? (y/n)"
46 read ch
47 if [ $ch = 'y' ]
48 then
49 loop=1
50 echo " ======================================= "
51 else
52 loop=0
53 echo " ========================================= "
54 fi
55 fi
56 fi
57 done
58}
59
60
61#Second Function: Create New Table in specific DataBase
62Create_Table()
63{
64 echo "Please enter existing DataBase name: "
65 read dbName
66 if [ -d $dbName ]
67 then
68 loop=1
69 while [ $loop -eq 1 ]
70 do
71 echo "List of tables in this DataBase ($dbName): "
72 ls $dbName
73 echo " ========================================= "
74 echo "Please enter new Table Name: "
75 read tableName
76 if [ -f $dbName/$tableName ]
77 then
78 echo " Sorry, Table ($tableName) already exists in DataBase ($dbName), please try again "
79 echo " ============================================================================================= "
80 Create_Table
81 else
82 touch $dbName/$tableName
83 echo " Table ($tableName) Created Sucessfully in DataBase ($dbName) "
84 echo " ==================================================================== "
85 echo "Please enter number of Columns needed rather than PK: "
86 read colNum
87
88 #Check if input number is positive integer
89 numCheck=1
90 while [ $numCheck -eq 1 ]
91 do
92 if [[ $colNum -eq $colNum && $colNum -gt 0 ]] 2> /dev/null
93 then
94 numCheck=0
95 else
96 echo " Sorry you entered wrong input, please enter valid number "
97 read colNum
98 fi
99 done
100
101 #Append first line of columns' names in table file
102 echo -n "PK, " >> $dbName/$tableName
103 for i in $(seq $colNum)
104 do
105 echo "Please enter the name of Column number $i: "
106 read colName
107 echo -n "$colName, " >> $dbName/$tableName
108 done
109 echo >> $dbName/$tableName #go to new line in the file
110 echo " Columns of Table ($tableName) defined sucessfully "
111 echo " ============================================================ "
112
113 echo "Do you want to create new Table in same DataBase ($dbName)? (y/n)"
114 read ch
115 if [ $ch = 'y' ]
116 then
117 loop=1
118 echo " =============================================================== "
119 elif [ $ch = 'n' ]
120 then
121 loop=0
122 echo " ==================================================================== "
123 else
124 echo " Sorry you entered wrong input, please enter (y/n) "
125 echo "Note:: This is your last try"
126 echo "Do you want to create new Table in same DataBase ($dbName)? (y/n)"
127 read ch
128 if [ $ch = 'y' ]
129 then
130 loop=1
131 echo " =============================================================== "
132 else
133 loop=0
134 echo " ==================================================================== "
135 fi
136 fi
137 fi
138 done
139 else
140 echo " Sorry, DataBase ($dbName) doesn't exist, please try again "
141 echo " ================================================================== "
142 Create_Table
143 fi
144}
145
146
147#Third Function: Insert Records in specific Table
148Insert()
149{
150 echo "Please enter existing DataBase name: "
151 read dbName
152 if [ -d $dbName ]
153 then
154 echo "List of tables in this DataBase ($dbName): "
155 ls $dbName
156 echo " ========================================= "
157 echo "Please enter existing Table Name: "
158 read tableName
159 if [ -f $dbName/$tableName ]
160 then
161 loop=1
162 while [ $loop -eq 1 ]
163 do
164 pk=$(tail -1 $dbName/$tableName | cut -d"," -f 1) #get least PK
165 if [ $pk = "PK" ] #check if this is first data row
166 then
167 pk=1
168 else
169 pk=$(($pk + 1)) #increment PK by 1
170 fi
171
172 echo -n "$pk, " >> $dbName/$tableName #add the new PK in file
173 colNum=$((`awk -F", " 'NR==1 {print NF}' $dbName/$tableName `-1)) #get number of columns in the table
174
175 #insert values of the row
176 for i in $(seq 2 $colNum)
177 do
178 colName=`head -1 $dbName/$tableName | cut -d"," -f $i` #get each column name
179 echo "Please enter value of ($colName ) Column: "
180 read value
181 echo -n "$value, " >> $dbName/$tableName
182 done
183 echo >> $dbName/$tableName #go to new line in the file
184
185 echo " Record Inserted Sucessfully in Table ($tableName) "
186 echo " ============================================================== "
187 echo "Do you want to insert new record in same Table ($tableName)? (y/n)"
188 read ch
189 if [ $ch = 'y' ]
190 then
191 loop=1
192 echo " ================================================================ "
193 elif [ $ch = 'n' ]
194 then
195 loop=0
196 echo " ============================================================== "
197 else
198 echo " Sorry you entered wrong input, please enter (y/n) "
199 echo "Note:: This is your last try"
200 echo "Do you want to insert new record in same Table ($tableName)? (y/n)"
201 read ch
202 if [ $ch = 'y' ]
203 then
204 loop=1
205 echo " ================================================================ "
206 else
207 loop=0
208 echo " ============================================================== "
209 fi
210 fi
211 done
212
213 else
214 echo " Sorry, Table ($tableName) doesn't exist in DataBase ($dbName), please try again "
215 echo " ====================================================================================== "
216 Insert
217 fi
218 else
219 echo " Sorry, Database ($dbName) doesn't exist, please try again "
220 echo " ================================================================== "
221 Insert
222 fi
223}
224
225
226#Fourth Function: Update specific feild value of specific row in specific Table
227Update()
228{
229 echo "Please enter existing DataBase name: "
230 read dbName
231 if [ -d $dbName ]
232 then
233 echo "List of tables in this DataBase ($dbName): "
234 ls $dbName
235 echo " ========================================= "
236 echo "Please enter existing Table Name: "
237 read tableName
238 if [ -f $dbName/$tableName ]
239 then
240 echo "|__________________________# $tableName Data #_________________________|"
241 cat $dbName/$tableName
242 echo " ====================================================================== "
243
244 loop=1
245 while [ $loop -eq 1 ]
246 do
247 echo "Please enter the primary key of record you want to update: "
248 read prkey
249
250 #Check if input number is positive integer
251 numCheck=1
252 while [ $numCheck -eq 1 ]
253 do
254 if [[ $prkey -eq $prkey && $prkey -gt 0 ]] 2> /dev/null
255 then
256 numCheck=0
257 else
258 echo " Sorry you entered wrong input, please enter valid number "
259 read prkey
260 fi
261 done
262
263 prcheck=$(cut -d "," -f 1 $dbName/$tableName | grep $prkey) #get the entered PK from the PK column
264
265 if [[ ! $prcheck ]] #check if entered PK exists in table
266 then
267 echo " Sorry this primary key ($prkey) doesn't exist in Table ($tableName) "
268 echo " ========================================================================== "
269 else
270 echo "Please enter the column number that you want to update its value"
271 read i
272
273 #Check if input number is positive integer
274 numCheck=1
275 while [ $numCheck -eq 1 ]
276 do
277 if [[ $i -eq $i && $i -gt 0 ]] 2> /dev/null
278 then
279 if [ $i -eq 1 ]
280 then
281 echo " Sorry you can't change PK record value, please enter another column number "
282 read i
283 else
284 numCheck=0
285 fi
286 else
287 echo " Sorry you entered wrong input, please enter valid number "
288 read i
289 fi
290 done
291
292 value=$(awk -F"," '$1=='$prkey' {print $'$i'}' $dbName/$tableName) #get the old value in the specified cell in the row
293 if [[ $value ]]
294 then
295 echo "Note that the old value you want to change is: $value"
296 echo "Please enter the new value:"
297 read newvalue
298 prkey=$(($prkey + 1)) #increment the entered PK by 1 becuase of the header line in the file
299 sed -i "${prkey}s/$value/ $newvalue/" $dbName/$tableName #substitute the old value with new value in the specified cell in the row
300 echo " Record Updated Sucessfully in Table ($tableName) "
301 echo " ======================================================= "
302
303 echo "Do you want to update another record in same Table ($tableName)? (y/n)"
304 read ch
305 if [ $ch = 'y' ]
306 then
307 loop=1
308 echo " ==================================================================== "
309 elif [ $ch = 'n' ]
310 then
311 loop=0
312 echo " ======================================================= "
313 else
314 echo " Sorry you entered wrong input, please enter (y/n) "
315 echo "Note:: This is your last try"
316 echo "Do you want update another record in same Table ($tableName)? (y/n)"
317 read ch
318 if [ $ch = 'y' ]
319 then
320 loop=1
321 echo " ==================================================================== "
322 else
323 loop=0
324 echo " ======================================================= "
325 fi
326 fi
327 else
328 echo " Sorry, the column number you entered doesn't exist in Table ($tableName) "
329 echo " =============================================================================== "
330 fi
331 fi
332 done
333 else
334 echo " Sorry, Table ($tableName) dosen't exist in DataBase ($dbName), please try again "
335 echo " ================================================================================ "
336 Update
337 fi
338 else
339 echo " Sorry, Database ($dbName) doesn't exist, please try again "
340 echo " =================================================================== "
341 Update
342 fi
343}
344
345
346#Fifth Function: Select and display specific all data, column, or row from specific Table
347Select()
348{
349 echo "Please enter existing DataBase name: "
350 read dbName
351 if [ -d $dbName ]
352 then
353 echo "List of tables in this DataBase ($dbName): "
354 ls $dbName
355 echo " ========================================= "
356
357 while [ true ]
358 do
359 echo " 1. Select Table"
360 echo " 2. Select Column"
361 echo " 3. Select Row"
362 echo " 4. Exit"
363 echo "Please choose operation number: "
364 read operation
365 # set operation list
366 case $operation in
367 1)
368 echo "Please enter existing Table Name: "
369 read tableName
370 if [ -f $dbName/$tableName ]
371 then
372 echo "|____________________# Table ($tableName) Data #_______________________|"
373 cat $dbName/$tableName
374 echo " ====================================================================== "
375 else
376 echo " Sorry, Table ($tableName) doesn't exist in DataBase ($dbName), please try again "
377 echo " ====================================================================================== "
378 Select
379 fi
380 ;;
381 2)
382 echo "Please enter existing Table Name: "
383 read tableName
384 if [ -f $dbName/$tableName ]
385 then
386 cat $dbName/$tableName | head -1
387 echo " ===================================================== "
388 num=$((`awk -F", " 'NR==1 {print NF}' $dbName/$tableName `-1))
389 echo "Please enter the column number that you want to select "
390 read colNum
391 if [ $colNum -le $num ]
392 then
393 cat $dbName/$tableName | awk '{print $'$colNum'}'
394 echo " ===================================================== "
395 else
396 echo " Column number not found "
397 echo " ======================= "
398 fi
399 else
400 echo " Sorry, Table ($tableName) doesn't exist in DataBase ($dbName), please try again "
401 echo " ===================================================================================== "
402 Select
403 fi
404 ;;
405 3)
406 echo "Please enter existing Table Name: "
407 read tableName
408 if [ -f $dbName/$tableName ]
409 then
410 echo "|____________________# Table ($tableName) Data #_______________________|"
411 cat $dbName/$tableName
412 echo " ====================================================================== "
413
414 echo "Please enter your query or the word you want to select its row"
415 read query
416 grep -w $query $dbName/$tableName
417 if [ $? -eq 0 ]
418 then
419 echo "OK, $query is found in DataBase ($dbName)"
420 echo " ======================================= "
421 else
422 echo "$query is not found in DataBase ($dbName)"
423 echo " ======================================= "
424 fi
425 else
426 echo " Sorry, Table ($tableName) doesn't exist in DataBase ($dbName), please try again "
427 echo " ====================================================================================== "
428 Select
429 fi
430 ;;
431 4)
432 echo "Exit"
433 echo " ================================================================== "
434 break
435 ;;
436 *)
437 echo " Sorry you entered wrong choice, please try again "
438 ;;
439 esac
440 done
441 else
442 echo " Sorry, Database ($dbName) doesn't exist, please try again "
443 echo " ================================================================== "
444 Select
445 fi
446}
447
448
449#Sixth Function: Display all data in specific Table
450Display_Table()
451{
452 echo "Please enter DataBase name: "
453 read dbName
454 if [ -d $dbName ]
455 then
456 echo "List of tables in this DataBase ($dbName): "
457 ls $dbName
458 echo " ========================================= "
459 loop=1
460 while [ $loop -eq 1 ]
461 do
462 echo "Please enter Table Name: "
463 read tableName
464 if [ -f $dbName/$tableName ]
465 then
466 echo "|____________________# Table ($tableName) Data #_______________________|"
467 cat $dbName/$tableName
468 echo " ====================================================================== "
469
470 echo "Do you want to display another Table in same DataBase ($dbName)? (y/n)"
471 read ch
472 if [ $ch = 'y' ]
473 then
474 loop=1
475 echo " ==================================================================== "
476 elif [ $ch = 'n' ]
477 then
478 loop=0
479 echo " ========================================================================= "
480 else
481 echo " Sorry you entered wrong input, please enter (y/n) "
482 echo "Note:: This is your last try"
483 echo "Do you want display another Table? (y/n)"
484 read ch
485 if [ $ch = 'y' ]
486 then
487 loop=1
488 echo " ==================================================================== "
489 else
490 loop=0
491 echo " ========================================================================= "
492 fi
493 fi
494 else
495 echo " Sorry, Table ($tableName) dosen't exist in DataBase ($dbName), please try again "
496 echo " ================================================================================ "
497 Display_Table
498 fi
499 done
500 else
501 echo " Sorry, Database ($dbName) doesn't exist, please try again "
502 echo " =================================================================== "
503 Display_Table
504 fi
505}
506
507
508#Seventh Function: Delete specific row from specific Table
509Delete_Record ()
510{
511 echo "Please enter DataBase name: "
512 read dbName
513 if [ -d $dbName ]
514 then
515 echo "List of tables in this DataBase ($dbName): "
516 ls $dbName
517 echo " ========================================= "
518 echo "Please enter Table Name: "
519 read tableName
520 if [ -f $dbName/$tableName ]
521 then
522 echo "|_____________________# Table ($tableName) Data #______________________|"
523 cat $dbName/$tableName
524 echo " ====================================================================== "
525 loop=1
526 while [ $loop -eq 1 ]
527 do
528 echo "Please enter the primary key of record you want to delete: "
529 read pk
530 pk=$(($pk + 1)) #increment the entered PK by 1 becuase of the header line in the file
531 sed -i "${pk}d" $dbName/$tableName #delete the selected record
532 echo " Record Deleted Sucessfully from Table ($tableName) "
533 echo " ============================================================= "
534
535 echo "Do you want to delete another record from same Table ($tableName)? (y/n)"
536 read ch
537 if [ $ch = 'y' ]
538 then
539 loop=1
540 echo " ====================================================================== "
541 elif [ $ch = 'n' ]
542 then
543 loop=0
544 echo " ============================================================= "
545 else
546 echo " Sorry you entered wrong input, please enter (y/n) "
547 echo "Note:: This is your last try"
548 echo "Do you want to delete another record in same Table ($tableName)? (y/n)"
549 read ch
550 if [ $ch = 'y' ]
551 then
552 loop=1
553 echo " ====================================================================== "
554 else
555 loop=0
556 echo " ============================================================== "
557 fi
558 fi
559 done
560 else
561 echo " Sorry, Table ($tableName) dosen't exist in DataBase ($dbName), please try again "
562 echo " ================================================================================ "
563 Delete_Record
564 fi
565 else
566 echo " Sorry, Database ($dbName) doesn't exist, please try again "
567 echo " =================================================================== "
568 Delete_Record
569 fi
570}
571
572
573#Eighth Function: Drop specific Table in specific DataBase
574Drop_Table()
575{
576 echo "Please enter existing DataBase name: "
577 read dbName
578 if [ -d $dbName ]
579 then
580 loop=1
581 while [ $loop -eq 1 ]
582 do
583 echo "List of tables in this DataBase ($dbName): "
584 ls $dbName
585 echo " ========================================= "
586 echo "Please enter existing Table Name: "
587 read tableName
588 if [ -f $dbName/$tableName ]
589 then
590 rm $dbName/$tableName
591 echo " Table ($tableName) Dropped Sucessfully from DataBase ($dbName) "
592 echo " ===================================================================== "
593
594 echo "Do you want to drop another Table from same DataBase ($dbName)? (y/n)"
595 read ch
596 if [ $ch = 'y' ]
597 then
598 loop=1
599 echo " =================================================================== "
600 elif [ $ch = 'n' ]
601 then
602 loop=0
603 echo " ===================================================================== "
604 else
605 echo " Sorry you entered wrong input, please enter (y/n) "
606 echo "Note:: This is your last try"
607 echo "Do you want to drop another Table from same DataBase ($dbName)? (y/n)"
608 read ch
609 if [ $ch = 'y' ]
610 then
611 loop=1
612 echo " =================================================================== "
613 else
614 loop=0
615 echo " ===================================================================== "
616 fi
617 fi
618 else
619 echo " Sorry, Table ($tableName) already doesn't exist in DataBase ($dbName), please try again "
620 echo " ============================================================================================ "
621 Drop_Table
622 fi
623 done
624 else
625 echo " Sorry, Database ($dbName) doesn't exist, please try again "
626 echo " ================================================================== "
627 Drop_Table
628 fi
629}
630
631
632#Ninth Function: Drop specific DataBase
633Drop_DB()
634{
635 loop=1
636 while [ $loop -eq 1 ]
637 do
638 echo "Please enter existing DataBase name: "
639 read dbName
640 if [ -d $dbName ]
641 then
642 rm -rf $dbName
643 echo " DataBase ($dbName) Dropped Sucessfully "
644 echo " ========================================== "
645
646 echo "Do you want to delete another DataBase? (y/n)"
647 read ch
648 if [ $ch = 'y' ]
649 then
650 loop=1
651 echo " =========================================== "
652 elif [ $ch = 'n' ]
653 then
654 loop=0
655 echo " ========================================== "
656 else
657 echo " Sorry you entered wrong input, please enter (y/n) "
658 echo "Note:: This is your last try"
659 echo "Do you want to create new DataBase? (y/n)"
660 read ch
661 if [ $ch = 'y' ]
662 then
663 loop=1
664 echo " =========================================== "
665 else
666 loop=0
667 echo " ========================================== "
668 fi
669 fi
670 else
671 echo " Sorry, Database ($dbName) already doesn't exist, please try again "
672 echo " =========================================================================== "
673 Drop_DB
674 fi
675done
676}
677
678
679while [ true ]
680do
681echo " ---------- ^_^ Welcome to Our DBMS Program ^_^ ---------- "
682echo " | |"
683echo " 1. Create DataBase(s) "
684echo " | |"
685echo " 2. Create Table(s) "
686echo " | |"
687echo " 3. Insert Record(s) "
688echo " | |"
689echo " 4. Update Record(s) "
690echo " | |"
691echo " 5. Select Table/Column/Record "
692echo " | |"
693echo " 6. Display Table(s) Data "
694echo " | |"
695echo " 7. Delete Record(s) "
696echo " | |"
697echo " 8. Drop Table(s) "
698echo " | |"
699echo " 9. Drop DataBase(s) "
700echo " | |"
701echo " 10. Exit "
702echo " ========================================================== "
703echo " Please enter your choice number: "
704echo -e '\u2620'
705read ch
706
707case $ch in
708 1)
709 clear
710 Create_DB
711 ;;
712 2)
713 clear
714 Create_Table
715 ;;
716 3)
717 clear
718 Insert
719 ;;
720 4)
721 clear
722 Update
723 ;;
724 5)
725 clear
726 Select
727 ;;
728 6)
729 clear
730 Display_Table
731 ;;
732 7)
733 clear
734 Delete_Record
735 ;;
736 8)
737 clear
738 Drop_Table
739 ;;
740 9)
741 clear
742 Drop_DB
743 ;;
744 10)
745 clear
746 break
747 ;;
748 *)
749 clear
750 echo " Sorry you entered wrong choice, please try again :) "
751 ;;
752esac
753done