· 4 years ago · Mar 25, 2021, 11:20 AM
1#include "DatabaseAccess.h"
2#include "Album.h"
3#include "User.h"
4#include "ItemNotFoundException.h"
5#include <io.h>
6#include <map>
7#include <iostream>
8
9/*
10Function opens the data base and loads all of the information inside it
11If the database didn't already exist it creates a new one with all of the tables required
12Input: None
13Output: bool that means whether it was opened successfully
14*/
15bool DatabaseAccess::open()
16{
17 std::string dbFileName, sqlStatement;
18 int doesFileExist, res, albumId;
19 char* errMessage = nullptr;
20
21 dbFileName = "davidGalleryDB.sqlite";
22 doesFileExist = _access(dbFileName.c_str(), 0);
23 res = sqlite3_open(dbFileName.c_str(), &this->_db);
24
25 // Exiting if there's any error in the process
26 if (res != SQLITE_OK) {
27 this->_db = nullptr;
28 std::cout << "Failed to open DB" << std::endl;
29 return false;
30 }
31
32 // This is if the database already existed
33 if (doesFileExist == 0)
34 {
35 sqlStatement = "SELECT * FROM ALBUMS;";
36 res = sqlite3_exec(this->_db, sqlStatement.c_str(), albumsCallBack, &this->m_albums, &errMessage);
37
38 // Adding all of the pictures to the albums
39 for (auto& album : this->m_albums)
40 {
41 // Getting album id
42 sqlStatement = "SELECT ID FROM ALBUMS WHERE NAME == '" + album.getName() + "';";
43 res = sqlite3_exec(this->_db, sqlStatement.c_str(), intCallback, &albumId, &errMessage);
44
45 // Adding album pictures to album
46 sqlStatement = "SELECT * FROM PICTURES WHERE ALBUM_ID == " + std::to_string(albumId) + ";";
47
48 res = sqlite3_exec(this->_db, sqlStatement.c_str(), loadPicturesCallback, &album, &errMessage);
49 }
50
51 for (auto& album : this->m_albums)
52 {
53 for (auto& picture : album.getPictures())
54 {
55 // Tagging whoever needs to be tagged in all of the pictures
56 sqlStatement = "SELECT * FROM TAGS WHERE PICTURE_ID == " + std::to_string(picture.getId()) + ";";
57
58 res = sqlite3_exec(this->_db, sqlStatement.c_str(), loadTagsCallback, &picture, &errMessage);
59
60 }
61 }
62 sqlStatement = "SELECT * FROM USERS;";
63 res = sqlite3_exec(this->_db, sqlStatement.c_str(), usersCallBack, &this->m_users, &errMessage);
64
65
66
67 }
68 // This is if the database didn't already exist - creating the tables
69 else
70 {
71 sqlStatement = "CREATE TABLE USERS (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME TEXT NOT NULL); ";
72 sqlStatement += "CREATE TABLE ALBUMS (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME TEXT NOT NULL, CREATION_DATE TEXT NOT NULL, USER_ID INTEGER NOT NULL, FOREIGN KEY(USER_ID) REFERENCES USERS(ID)); ";
73 sqlStatement += "CREATE TABLE PICTURES (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME TEXT NOT NULL, LOCATION TEXT NOT NULL, CREATION_DATE TEXT NOT NULL, ALBUM_ID INTEGER NOT NULL, FOREIGN KEY(ALBUM_ID) REFERENCES ALBUMS(ID)); ";
74 sqlStatement += "CREATE TABLE TAGS (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, PICTURE_ID INTEGER NOT NULL, USER_ID INTEGER NOT NULL, FOREIGN KEY(PICTURE_ID) REFERENCES PICTURES(ID), FOREIGN KEY(USER_ID) REFERENCES USERS(ID)); ";
75
76 res = sqlite3_exec(this->_db, sqlStatement.c_str(), nullptr, nullptr, &errMessage);
77 if (res != SQLITE_OK)
78 {
79 std::cout << "Problem creating tables\n";
80 return false;
81 }
82 }
83 return true;
84}
85
86/*
87Function closes the database
88Input: None
89Output: None
90*/
91void DatabaseAccess::close()
92{
93 sqlite3_close(this->_db);
94 clear();
95}
96
97/*
98Function clears the information inside the class
99Input: None
100Output: None
101*/
102void DatabaseAccess::clear()
103{
104 this->m_albums.clear();
105 this->m_users.clear();
106}
107
108/*
109Function returns all of the albums
110Input: None
111Output: A list of all of the albums
112*/
113const std::list<Album> DatabaseAccess::getAlbums()
114{
115
116 return this->m_albums;
117}
118
119/*
120Function returns all of the albums of a user
121Input: A user
122Output: A list of all of the albums o the user
123*/
124const std::list<Album> DatabaseAccess::getAlbumsOfUser(const User& user)
125{
126 std::list<Album> albums;
127 for (auto& album : m_albums)
128 {
129 if (album.getOwnerId() == user.getId())
130 albums.push_back(album);
131 }
132 return albums;
133
134
135}
136
137/*
138Function creates an album inside the database
139Input: The number that is going to be created
140Output: None
141*/
142void DatabaseAccess::createAlbum(const Album& album)
143{
144 std::string dbFileName, sqlStatement;
145 int res;
146 char* errMessage = nullptr;
147
148 // Sending the sql request
149 sqlStatement = "INSERT INTO ALBUMS (NAME, CREATION_DATE, USER_ID) VALUES ('" + album.getName() + "', '" +
150 album.getCreationDate() + "', " + std::to_string(album.getOwnerId()) + ");";
151
152 res = sqlite3_exec(this->_db, sqlStatement.c_str(), nullptr, nullptr, &errMessage);
153
154 // Pushing the album into the albums list if it was successful
155 if (res != SQLITE_OK)
156 {
157 std::cout << "Problem creating album\n";
158 }
159 else
160 {
161 this->m_albums.push_back(album);
162 }
163}
164
165/*
166Function deletes an album from the database
167Input: The name of the album, the owner id
168Output: None
169*/
170void DatabaseAccess::deleteAlbum(const std::string& albumName, int userId)
171{
172 std::string sqlStatement;
173 int res;
174 char* errMessage = nullptr;
175
176 // Sending the sql request
177 sqlStatement = "DELETE FROM Albums WHERE NAME == '" + albumName + "' AND USER_ID == " + std::to_string(userId) + ";";
178
179 res = sqlite3_exec(this->_db, sqlStatement.c_str(), nullptr, nullptr, &errMessage);
180
181 // Deleting the album from the list of it was successful
182 if (res != SQLITE_OK)
183 {
184 std::cout << "Problem deleting album\n";
185 }
186 else
187 {
188 for (auto album = m_albums.begin(); album != m_albums.end(); album++)
189 {
190 if (album->getName() == albumName && userId == album->getOwnerId())
191 {
192 album = this->m_albums.erase(album);
193 }
194 }
195 }
196}
197
198/*
199Function checks if an album exists in the database
200Input: name of the album, id of the user
201Output: bool that represents whether the album exists or not
202*/
203bool DatabaseAccess::doesAlbumExists(const std::string& albumName, int userId)
204{
205 // Iterating through all of the numbers and checking
206 for (const auto album : this->m_albums)
207 {
208 if (album.getName() == albumName && album.getOwnerId() == userId)
209 {
210 return true;
211 }
212 }
213
214 return false;
215}
216
217/*
218Function opens an album
219Input: name of the album
220Output: The album that was opened
221*/
222Album DatabaseAccess::openAlbum(const std::string& albumName)
223{
224 // Finding the album in the albums list
225 for (auto& album : this->m_albums)
226 {
227 if (albumName == album.getName())
228 {
229 return album;
230 }
231 }
232 throw MyException("No album with name " + albumName + " exists");
233}
234
235/*
236Function closes an open album
237*/
238void DatabaseAccess::closeAlbum(Album& pAlbum)
239{
240 // Nothing to do here
241}
242
243/*
244Function prints all of the albums in the database
245*/
246void DatabaseAccess::printAlbums()
247{
248
249 for (auto& album : m_albums)
250 {
251 std::cout << "NAME: " << album.getName() << " OWNER ID: " << album.getOwnerId() << " CREATION DATE: " << album.getCreationDate() << std::endl;
252 }
253}
254
255/*
256Function adds a picture to an album by name
257Input: Name of the album, Reference to the picture that will be added
258Output: None
259*/
260void DatabaseAccess::addPictureToAlbumByName(const std::string& albumName, const Picture& picture)
261{
262 std::string sqlStatement;
263 int res, albumId;
264 char* errMessage = nullptr;
265
266 // Getting the id of the album
267 sqlStatement = "SELECT ID FROM ALBUMS WHERE NAME == '" + albumName + "';";
268 res = sqlite3_exec(this->_db, sqlStatement.c_str(), intCallback, &albumId, &errMessage);
269
270 // Adding the picture to the pictures table with the album id
271 sqlStatement = "INSERT INTO PICTURES (ID, NAME, LOCATION, CREATION_DATE, ALBUM_ID) VALUES (" +
272 std::to_string(picture.getId()) + ", '" + picture.getName() + "', '" + picture.getPath() + "', '" +
273 picture.getCreationDate() + "', " + std::to_string(albumId) + ");";
274
275
276 res = sqlite3_exec(this->_db, sqlStatement.c_str(), nullptr, nullptr, &errMessage);
277
278 if (res != SQLITE_OK)
279 {
280 std::cout << "Problem adding picture\n";
281 }
282 else
283 {
284 for (auto album = m_albums.begin(); album != m_albums.end(); album++)
285 {
286 if (album->getName() == albumName)
287 {
288 album->addPicture(picture);
289 }
290 }
291
292 }
293}
294
295/*
296Function removes a picture from an album by name
297Input: Name of the album, name of the picture
298Output: None
299*/
300void DatabaseAccess::removePictureFromAlbumByName(const std::string& albumName, const std::string& pictureName)
301{
302 std::string sqlStatement;
303 int res, albumId;
304 char* errMessage = nullptr;
305
306 // Getting the id of the album
307 sqlStatement = "SELECT ID FROM ALBUMS WHERE NAME == '" + albumName + "';";
308 res = sqlite3_exec(this->_db, sqlStatement.c_str(), intCallback, &albumId, &errMessage);
309
310 // Adding the picture to the pictures table with the album id
311 sqlStatement = "DELETE FROM PICTURES WHERE ALBUM_ID == " + std::to_string(albumId) + " AND NAME == '" + pictureName + "';";
312
313
314 res = sqlite3_exec(this->_db, sqlStatement.c_str(), nullptr, nullptr, &errMessage);
315
316 if (res != SQLITE_OK)
317 {
318 std::cout << "Problem removing picture\n";
319 }
320 else
321 {
322 for (auto album = m_albums.begin(); album != m_albums.end(); album++)
323 {
324 if (album->getName() == albumName)
325 {
326 album->removePicture(pictureName);
327 }
328 }
329 }
330}
331
332/*
333Function tags a user in a picture
334Input: Name of the album, name of the picture, id of the user
335Output: None
336*/
337void DatabaseAccess::tagUserInPicture(const std::string& albumName, const std::string& pictureName, int userId)
338{
339 std::string sqlStatement;
340 int res, pictureId, albumId;
341 char* errMessage = nullptr;
342
343 // Getting the album id
344 sqlStatement = "SELECT ID FROM ALBUMS WHERE NAME == '" + albumName + "';";
345 res = sqlite3_exec(this->_db, sqlStatement.c_str(), intCallback, &albumId, &errMessage);;
346
347 // Getting the picture id
348 sqlStatement = "SELECT ID FROM PICTURES WHERE NAME == '" + pictureName +
349 "' AND ALBUM_ID == " + std::to_string(albumId) + ";";
350
351 res = sqlite3_exec(this->_db, sqlStatement.c_str(), intCallback, &pictureId, &errMessage);
352
353 // Tagging the user in the picture
354 sqlStatement = "INSERT INTO TAGS (PICTURE_ID, USER_ID) VALUES (" + std::to_string(pictureId) +
355 ", " + std::to_string(userId) + ");";
356
357 res = sqlite3_exec(this->_db, sqlStatement.c_str(), nullptr, nullptr, &errMessage);
358
359 if (res != SQLITE_OK)
360 {
361 std::cout << "Problem Tagging user tables\n";
362 }
363 else
364 {
365 for (auto album = m_albums.begin(); album != m_albums.end(); album++)
366 {
367 if (album->getName() == albumName)
368 {
369 album->tagUserInPicture(userId, pictureName);
370 }
371 }
372 }
373}
374
375/*
376Function untags a user in a picture
377Input: Name of the album, name of the picture, id of the user
378Output: None
379*/
380void DatabaseAccess::untagUserInPicture(const std::string& albumName, const std::string& pictureName, int userId)
381{
382 std::string sqlStatement;
383 int res, pictureId, albumId;
384 char* errMessage = nullptr;
385
386 // Getting the album id
387 sqlStatement = "SELECT ID FROM ALBUMS WHERE NAME == '" + albumName + "';";
388 res = sqlite3_exec(this->_db, sqlStatement.c_str(), intCallback, &albumId, &errMessage);;
389
390 // Getting the picture id
391 sqlStatement = "SELECT ID FROM PICTURES WHERE NAME == '" + pictureName +
392 "' AND ALBUM_ID == " + std::to_string(albumId) + ";";
393
394 res = sqlite3_exec(this->_db, sqlStatement.c_str(), intCallback, &pictureId, &errMessage);
395
396 //Untagging the user
397 sqlStatement = "DELETE FROM TAGS WHERE PICTURE_ID == " + std::to_string(pictureId) +
398 " AND USER_ID == " + std::to_string(userId) + ";";
399 res = sqlite3_exec(this->_db, sqlStatement.c_str(), nullptr, nullptr, &errMessage);
400
401 std::cout << sqlStatement << std::endl;
402
403 if (res != SQLITE_OK)
404 {
405 std::cout << "Problem untagging user\n";
406 }
407 else
408 {
409 for (auto album = m_albums.begin(); album != m_albums.end(); album++)
410 {
411 if (album->getName() == albumName)
412 {
413 album->untagUserInPicture(userId, pictureName);
414 }
415 }
416 }
417}
418
419/*
420Function prints all of the users
421Input: None
422Output: None
423*/
424void DatabaseAccess::printUsers()
425{
426
427 for (auto& user : this->m_users)
428 {
429 std::cout << "ID: " << user.getId() << " NAME: " << user.getName() << std::endl;
430 }
431}
432
433/*
434Function returns a user by an id
435Input: id of the user
436Output: None
437*/
438User DatabaseAccess::getUser(int userId)
439{
440 for (auto& user : m_users)
441 {
442 if (user.getId() == userId)
443 {
444 return user;
445 }
446 }
447 throw MyException("No user with id " + userId);
448}
449
450/*
451Function creates a user
452Input: Reference to the user
453Output: None
454*/
455void DatabaseAccess::createUser(User& user)
456{
457 std::string sqlStatement;
458 int res;
459 char* errMessage = nullptr;
460
461 // Creating the user
462 sqlStatement = "INSERT INTO USERS (ID, NAME) VALUES (" + std::to_string(user.getId()) +
463 ", '" + user.getName() + "');";
464
465 res = sqlite3_exec(this->_db, sqlStatement.c_str(), nullptr, nullptr, &errMessage);
466
467 // Checking if everything's fine
468 if (res != SQLITE_OK)
469 {
470 std::cout << "Problem creating user\n";
471 }
472 this->m_users.push_back(user);
473}
474
475/*
476Function deletes a user
477Input: Reference to the user
478Output: None
479*/
480void DatabaseAccess::deleteUser(const User& user)
481{
482 std::string sqlStatement;
483 int res;
484 char* errMessage = nullptr;
485
486 // Deleting the user
487 sqlStatement = "DELETE FROM USERS WHERE ID == " + std::to_string(user.getId()) + " AND NAME == '" +
488 user.getName() + "';";
489
490 res = sqlite3_exec(this->_db, sqlStatement.c_str(), nullptr, nullptr, &errMessage);
491
492 // Checking if everything's fine
493 if (res != SQLITE_OK)
494 {
495 std::cout << "Problem deleting user\n";
496 }
497
498 for (auto currentUser = m_users.begin(); currentUser != m_users.end(); ++currentUser)
499 {
500 if (currentUser->getId() == user.getId())
501 {
502 currentUser = this->m_users.erase(currentUser);
503 }
504 }
505
506}
507
508/*
509Function checks if a user exists in the database
510Input: id of the user
511Output: whether the user exists in the database
512*/
513bool DatabaseAccess::doesUserExists(int userId)
514{
515
516 for (auto& user : this->m_users)
517 {
518 if (user.getId() == userId)
519 {
520 return true;
521 }
522 }
523 return false;
524}
525
526/*
527Function returns all of the users in the database
528Input: None
529Output: None
530*/
531const std::list<User> DatabaseAccess::getUsers()
532{
533 return this->m_users;
534}
535
536/*
537Function calculates and returns the number of albums a user owns
538Input: Reference to the user
539Output: Number of albums
540*/
541int DatabaseAccess::countAlbumsOwnedOfUser(const User& user)
542{
543 int albumNum = 0;
544
545 for (auto& album : this->m_albums)
546 {
547 if (album.getName() == user.getName())
548 {
549 albumNum++;
550 }
551 }
552
553 return albumNum;
554}
555
556/*
557Function counts and returns all of the times a user has been tagged in an album
558Input: Reference to the user
559Output: Number of times a user has been tagged
560*/
561int DatabaseAccess::countAlbumsTaggedOfUser(const User& user)
562{
563 int albumsCount = 0;
564 for (const auto& album : this->m_albums) // const so i wont change accidently the values
565 {
566 const std::list<Picture>& pics = album.getPictures();
567 for (const auto& pic : pics)
568 {
569 if (pic.isUserTagged(user))
570 {
571 albumsCount++;
572 break;
573 }
574 }
575 }
576 return albumsCount;
577}
578
579/*
580Function counts the tags of a user
581Input: Reference to the user
582Output: numbre of tags
583*/
584int DatabaseAccess::countTagsOfUser(const User& user)
585{
586
587 int tagCount = 0;
588 for (const auto& album : this->m_albums)
589 {
590 const std::list<Picture>& pics = album.getPictures();
591 for (const auto& pic : pics)
592 {
593 if (pic.isUserTagged(user))
594 {
595 tagCount++;
596 break;
597 }
598 }
599 }
600 return tagCount;
601}
602
603/*
604Function calculates and returns the average of tags per album
605Input: Reference to the user
606Output: the avg
607*/
608float DatabaseAccess::averageTagsPerAlbumOfUser(const User& user)
609{
610 int albumsTaggedCount = countAlbumsTaggedOfUser(user);
611
612 if (0 == albumsTaggedCount) {
613 return 0;
614 }
615
616 return static_cast<float>(countTagsOfUser(user)) / albumsTaggedCount;
617}
618
619/*
620Function finds the most tagged user and returns him
621Input: None
622Output: The most tagged user
623*/
624User DatabaseAccess::getTopTaggedUser()
625{
626 std::map<int, int> userTagsCountMap;
627
628 auto albumsIter = m_albums.begin();
629 for (const auto& album : m_albums) {
630 for (const auto& picture : album.getPictures()) {
631
632 const std::set<int>& userTags = picture.getUserTags();
633 for (const auto& user : userTags) {
634 //As map creates default constructed values,
635 //users which we haven't yet encountered will start from 0
636 userTagsCountMap[user]++;
637 }
638 }
639 }
640
641 if (userTagsCountMap.size() == 0) {
642 throw MyException("There isn't any tagged user.");
643 }
644
645 int topTaggedUser = -1;
646 int currentMax = -1;
647 for (auto entry : userTagsCountMap) {
648 if (entry.second < currentMax) {
649 continue;
650 }
651
652 topTaggedUser = entry.first;
653 currentMax = entry.second;
654 }
655
656 if (-1 == topTaggedUser) {
657 throw MyException("Failed to find most tagged user");
658 }
659
660 return getUser(topTaggedUser);
661
662}
663
664/*
665Function finds the most tagged picture and returns it
666Input: None
667Output: The most tagged picture
668*/
669Picture DatabaseAccess::getTopTaggedPicture()
670{
671 int currentMax = -1;
672 const Picture* mostTaggedPic = nullptr;
673 for (const auto& album : m_albums) {
674 for (const Picture& picture : album.getPictures()) {
675 int tagsCount = picture.getTagsCount();
676 if (tagsCount == 0) {
677 continue;
678 }
679
680 if (tagsCount <= currentMax) {
681 continue;
682 }
683
684 mostTaggedPic = &picture;
685 currentMax = tagsCount;
686 }
687 }
688 if (nullptr == mostTaggedPic) {
689 throw MyException("There isn't any tagged picture.");
690 }
691
692 return *mostTaggedPic;
693
694}
695
696/*
697Function finds all of the tagged pictures of a user and returns them
698Input: The user
699Output: A list of the pictures
700*/
701std::list<Picture> DatabaseAccess::getTaggedPicturesOfUser(const User& user)
702{
703 std::list<Picture> pictures;
704
705 for (const auto& album : m_albums) {
706 for (const auto& picture : album.getPictures()) {
707 if (picture.isUserTagged(user)) {
708 pictures.push_back(picture);
709 }
710 }
711 }
712
713 return pictures;
714
715}
716
717/*
718callback function to get an int that the sql returns
719*/
720int DatabaseAccess::intCallback(void* data, int argc, char** argv, char** azColName)
721{
722 int* final = (int*)data;
723 if (argc == 0)
724 {
725 *final = NULL;
726 }
727 else
728 {
729 *final = std::atoi(argv[0]);
730 }
731 return 0;
732
733}
734
735/*
736Callback function to get all of the albums
737*/
738int DatabaseAccess::albumsCallBack(void* data, int argc, char** argv, char** azColName)
739{
740
741 std::list<Album>* albums = (std::list<Album>*)data;
742 Album tempAlbum;
743 for (int i = 0; i < argc; i++)
744 {
745
746 if (std::string(azColName[i]) == "NAME")
747 {
748 tempAlbum.setName(argv[i]);
749 }
750 else if (std::string(azColName[i]) == "CREATION_DATE")
751 {
752 tempAlbum.setCreationDate(argv[i]);
753 }
754 else if (std::string(azColName[i]) == "USER_ID")
755 {
756 tempAlbum.setOwner(std::stoi(argv[i]));
757 (*albums).push_back(tempAlbum);
758 }
759
760 }
761 return 0;
762}
763
764/*
765Callback function to get all of the users
766*/
767int DatabaseAccess::usersCallBack(void* data, int argc, char** argv, char** azColName)
768{
769 std::list<User>* users = (std::list<User>*)data;
770 for (int i = 0; i < argc; i++)
771 {
772 User user;
773 if (std::string(azColName[i]) == "ID")
774 {
775 user.setId(std::atoi(argv[i]));
776 }
777 else if (std::string(azColName[i]) == "NAME")
778 {
779 user.setName(argv[i]);
780 (*users).push_back(user);
781 }
782 }
783 return 0;
784
785
786}
787
788/*
789Callback function to load pictures to an album
790*/
791int DatabaseAccess::loadPicturesCallback(void* data, int argc, char** argv, char** azColName)
792{
793 Album* album = (Album*)data;
794 Picture tempPic;
795 for (int i = 0; i < argc; i++)
796 {
797 if (std::string(azColName[i]) == "ID")
798 {
799 tempPic.setId(std::stoi(argv[i]));
800 }
801 else if (std::string(azColName[i]) == "NAME")
802 {
803 tempPic.setName(argv[i]);
804 }
805 else if (std::string(azColName[i]) == "LOCATION")
806 {
807 tempPic.setPath(argv[i]);
808 }
809 else if (std::string(azColName[i]) == "CREATION_DATE") // the last colomn, after this set the picture is ready to add
810 {
811 tempPic.setCreationDate(argv[i]);
812 album->addPicture(tempPic);
813 }
814 }
815 return 0;
816
817}
818
819/*
820Callback function to load the tags to a picture
821*/
822int DatabaseAccess::loadTagsCallback(void* data, int argc, char** argv, char** azColName)
823{
824
825 Picture* pic = (Picture*)data;
826 for (int i = 0; i < argc; i++)
827 {
828 User user;
829 if (std::string(azColName[i]) == "ID")
830 {
831 user.setId(std::stoi(argv[i]));
832 }
833 else if (std::string(azColName[i]) == "NAME")
834 {
835 {
836 user.setName(argv[i]);
837 (*pic).tagUser(user);
838 }
839 }
840
841 }
842 return 0;
843
844}
845