Source of file MySQLDAL.php
Size: 25,307 Bytes - Last Modified: 2019-07-22T06:17:07+00:00
C:/xampp/htdocs/PodTube/src/classes/DB/MySQLDAL.php
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273
Covered by 2 test(s):
74
Covered by 2 test(s):
757677
Covered by 2 test(s):
78798081
Covered by 2 test(s):
8283
Covered by 2 test(s):
84
Covered by 2 test(s):
858687888990
Covered by 2 test(s):
919293949596979899100101
Covered by 5 test(s):
102
Covered by 5 test(s):
103
Covered by 5 test(s):
104
Covered by 5 test(s):
105
Covered by 5 test(s):
106107108
Covered by 5 test(s):
109110111
Covered by 5 test(s):
112113
Covered by 5 test(s):
114115116117118119120121122123124125126127128
Covered by 5 test(s):
129
Covered by 5 test(s):
130
Covered by 5 test(s):
131
Covered by 5 test(s):
132
Covered by 5 test(s):
133
Covered by 5 test(s):
134
Covered by 5 test(s):
135
Covered by 5 test(s):
136
Covered by 5 test(s):
137
Covered by 5 test(s):
138
Covered by 5 test(s):
139
Covered by 5 test(s):
140
Covered by 5 test(s):
141142
Covered by 5 test(s):
143
Covered by 5 test(s):
144
Covered by 2 test(s):
145146
Covered by 5 test(s):
147
Covered by 2 test(s):
148149
Covered by 5 test(s):
150151
Covered by 5 test(s):
152153154155156157158159160161162163
Covered by 2 test(s):
164
Covered by 2 test(s):
165
Covered by 2 test(s):
166
Covered by 2 test(s):
167
Covered by 2 test(s):
168169170
Covered by 2 test(s):
171172173
Covered by 2 test(s):
174175
Covered by 2 test(s):
176177178179180181182183184185186187188189190191
Covered by 10 test(s):
192193
Covered by 10 test(s):
194
Covered by 10 test(s):
195
Covered by 10 test(s):
196
Covered by 10 test(s):
197
Covered by 10 test(s):
198199200
Covered by 10 test(s):
201
Covered by 10 test(s):
202203
Covered by 1 test(s):
204205
Covered by 1 test(s):
206207208209210211212213214215216217218219220221
Covered by 10 test(s):
222223
Covered by 10 test(s):
224
Covered by 10 test(s):
225
Covered by 10 test(s):
226
Covered by 10 test(s):
227
Covered by 10 test(s):
228229230
Covered by 10 test(s):
231
Covered by 10 test(s):
232233
Covered by 1 test(s):
234235
Covered by 1 test(s):
236237238239240241242243244245246247248249250
Covered by 10 test(s):
251252
Covered by 10 test(s):
253254255
Covered by 10 test(s):
256
Covered by 10 test(s):
257
Covered by 10 test(s):
258
Covered by 10 test(s):
259
Covered by 10 test(s):
260
Covered by 10 test(s):
261
Covered by 10 test(s):
262
Covered by 10 test(s):
263
Covered by 10 test(s):
264
Covered by 10 test(s):
265
Covered by 10 test(s):
266
Covered by 10 test(s):
267268269270
Covered by 10 test(s):
271272273274275276
Covered by 10 test(s):
277278279280281282283284285286287288289
Covered by 5 test(s):
290
Covered by 5 test(s):
291
Covered by 5 test(s):
292
Covered by 5 test(s):
293
Covered by 5 test(s):
294
Covered by 5 test(s):
295296297
Covered by 3 test(s):
298299300301
Covered by 5 test(s):
302303304
Covered by 5 test(s):
305
Covered by 5 test(s):
306
Covered by 5 test(s):
307
Covered by 5 test(s):
308
Covered by 5 test(s):
309
Covered by 5 test(s):
310
Covered by 5 test(s):
311
Covered by 5 test(s):
312
Covered by 5 test(s):
313
Covered by 5 test(s):
314
Covered by 5 test(s):
315
Covered by 5 test(s):
316317
Covered by 5 test(s):
318319320321322323324325326327328329330331332333334
Covered by 1 test(s):
335
Covered by 1 test(s):
336
Covered by 1 test(s):
337338
Covered by 1 test(s):
339
Covered by 1 test(s):
340
Covered by 1 test(s):
341
Covered by 1 test(s):
342
Covered by 1 test(s):
343
Covered by 1 test(s):
344
Covered by 1 test(s):
345
Covered by 1 test(s):
346
Covered by 1 test(s):
347
Covered by 1 test(s):
348349
Covered by 1 test(s):
350351352353354355356357358359360361362363364365366367368
Covered by 3 test(s):
369
Covered by 3 test(s):
370
Covered by 3 test(s):
371
Covered by 3 test(s):
372
Covered by 3 test(s):
373374
Covered by 3 test(s):
375
Covered by 3 test(s):
376377378379
Covered by 3 test(s):
380
Covered by 3 test(s):
381382383
Covered by 3 test(s):
384385386387388389390391392393394395396397398
Covered by 4 test(s):
399400
Covered by 4 test(s):
401
Covered by 4 test(s):
402
Covered by 4 test(s):
403
Covered by 4 test(s):
404
Covered by 4 test(s):
405
Covered by 4 test(s):
406
Covered by 4 test(s):
407
Covered by 4 test(s):
408
Covered by 4 test(s):
409
Covered by 4 test(s):
410
Covered by 4 test(s):
411412
Covered by 4 test(s):
413414415416417418419420421422423424425
Covered by 1 test(s):
426
Covered by 1 test(s):
427
Covered by 1 test(s):
428
Covered by 1 test(s):
429
Covered by 1 test(s):
430431432
Covered by 1 test(s):
433
Covered by 1 test(s):
434
Covered by 1 test(s):
435436437
Covered by 1 test(s):
438439440441442443444445446447448449450451452453454455
Covered by 1 test(s):
456
Covered by 1 test(s):
457
Covered by 1 test(s):
458
Covered by 1 test(s):
459460
Covered by 1 test(s):
461462463464465466467468469470471472473474475
Covered by 1 test(s):
476
Covered by 1 test(s):
477
Covered by 1 test(s):
478
Covered by 1 test(s):
479480481482483484
Covered by 1 test(s):
485486487488489490491492493
Covered by 1 test(s):
494495
Covered by 1 test(s):
496
Covered by 1 test(s):
497
Covered by 1 test(s):
498
Covered by 1 test(s):
499500501502503504
Covered by 1 test(s):
505506507508509510511512513514
Covered by 1 test(s):
515516517518
Covered by 1 test(s):
519
Covered by 1 test(s):
520
Covered by 1 test(s):
521
Covered by 1 test(s):
522
Covered by 1 test(s):
523
Covered by 1 test(s):
524
Covered by 1 test(s):
525
Covered by 1 test(s):
526
Covered by 1 test(s):
527
Covered by 1 test(s):
528
Covered by 1 test(s):
529
Covered by 1 test(s):
530
Covered by 1 test(s):
531
Covered by 1 test(s):
532533534535536537
Covered by 1 test(s):
538539540541542543544545546547
Covered by 2 test(s):
548
Covered by 1 test(s):
549550551
Covered by 1 test(s):
552
Covered by 1 test(s):
553
Covered by 1 test(s):
554
Covered by 1 test(s):
555
Covered by 1 test(s):
556557
Covered by 1 test(s):
558
Covered by 1 test(s):
559560561562563
Covered by 1 test(s):
564
Covered by 1 test(s):
565566567568569
Covered by 1 test(s):
570571572
Covered by 1 test(s):
573
Covered by 1 test(s):
574575
Covered by 2 test(s):
576577578579580581582583584585
Covered by 2 test(s):
586
Covered by 2 test(s):
587
Covered by 1 test(s):
588589590
Covered by 2 test(s):
591592
Covered by 2 test(s):
593
Covered by 1 test(s):
594595
Covered by 2 test(s):
596
Covered by 1 test(s):
597598
Covered by 2 test(s):
599
Covered by 1 test(s):
600601602
Covered by 2 test(s):
603604605606607
Covered by 2 test(s):
608
Covered by 2 test(s):
609
Covered by 2 test(s):
610
Covered by 2 test(s):
611
Covered by 2 test(s):
612613614
Covered by 2 test(s):
615616617618619620621
Covered by 2 test(s):
622623624625626627628629630
Covered by 1 test(s):
631
Covered by 1 test(s):
632633
Covered by 1 test(s):
634635636637638639640641642643644645646
Covered by 1 test(s):
647648
Covered by 1 test(s):
649650
Covered by 1 test(s):
651652
Covered by 1 test(s):
653
Covered by 1 test(s):
654
Covered by 1 test(s):
655656657
Covered by 1 test(s):
658
Covered by 1 test(s):
659660
Covered by 1 test(s):
661662663664665
Covered by 1 test(s):
666667668669670671672673674675676677
Covered by 3 test(s):
678
Covered by 3 test(s):
679
Covered by 3 test(s):
680
Covered by 3 test(s):
681682683684
Covered by 1 test(s):
685
Covered by 1 test(s):
686
Covered by 1 test(s):
687
Covered by 1 test(s):
688689690691692693694695696697698699700701702703704705
Covered by 2 test(s):
706
Covered by 2 test(s):
707
Covered by 2 test(s):
708
Covered by 2 test(s):
709
Covered by 2 test(s):
710
Covered by 1 test(s):
711712713
Covered by 2 test(s):
714715716717718719720721722723724
Covered by 1 test(s):
725
Covered by 1 test(s):
726727
Covered by 1 test(s):
728729730731732733734735736
Covered by 1 test(s):
737
Covered by 1 test(s):
738739740741742743
Covered by 1 test(s):
744
Covered by 1 test(s):
745746747748
Covered by 1 test(s):
749750
Covered by 1 test(s):
751
Covered by 1 test(s):
752753754755
Covered by 1 test(s):
756
Covered by 1 test(s):
757
Covered by 1 test(s):
758
Covered by 1 test(s):
759760
Covered by 1 test(s):
761
Covered by 1 test(s):
762
Covered by 1 test(s):
763764
Covered by 1 test(s):
765766767
Covered by 1 test(s):
768769770
| <?phpnamespace AudioDidact\DB; use AudioDidact\User; use AudioDidact\Video; /** * Class MySQLDAL contains methods for communicating with a SQL database * */class MySQLDAL extends DAL { /** @var string Database table for storing user information */ protected $userTable = "users"; /** @var string Database table for storing feed/video information */ protected $feedTable = "feed"; /** @var array|string SQL database table names */ protected $myDBTables; /** @var array|array array of the correct database table schemas keyed by table name */ protected $correctSchemas; /** * Correct layout of the user table * * @var array */protected $userCorrect = [ ["Field" => "ID", "Type" => "int(11)", "Null" => "NO", "Key" => "PRI", "Default" => null, "Extra" => "auto_increment"], ["Field" => "username", "Type" => "mediumtext", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "password", "Type" => "mediumtext", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "email", "Type" => "mediumtext", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "firstname", "Type" => "mediumtext", "Null" => "YES", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "lastname", "Type" => "mediumtext", "Null" => "YES", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "gender", "Type" => "mediumtext", "Null" => "YES", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "webID", "Type" => "mediumtext", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "feedText", "Type" => "longtext", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "feedLength", "Type" => "int(11)", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "feedDetails", "Type" => "mediumtext", "Null" => "YES", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "privateFeed", "Type" => "tinyint(1)", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "emailVerified", "Type" => "tinyint(1)", "Null" => "NO", "Key" => "", "Default" => "0", "Extra" => ""], ["Field" => "emailVerificationCodes", "Type" => "text", "Null" => "YES", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "passwordRecoveryCodes", "Type" => "text", "Null" => "YES", "Key" => "", "Default" => null, "Extra" => ""] ]; /** * Correct layout of the feed table * * @var array */protected $feedCorrect = [ ["Field" => "ID", "Type" => "int(11)", "Null" => "NO", "Key" => "PRI", "Default" => null, "Extra" => "auto_increment"], ["Field" => "userID", "Type" => "int(11)", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "URL", "Type" => "text", "Null" => "YES", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "orderID", "Type" => "int(11)", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "filename", "Type" => "mediumtext", "Null" => "YES", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "thumbnailFilename", "Type" => "mediumtext", "Null" => "YES", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "videoID", "Type" => "mediumtext", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "videoAuthor", "Type" => "text", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "description", "Type" => "text", "Null" => "YES", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "videoTitle", "Type" => "text", "Null" => "NO", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "duration", "Type" => "int(11)", "Null" => "YES", "Key" => "", "Default" => null, "Extra" => ""], ["Field" => "isVideo", "Type" => "tinyint(1)", "Null" => "NO", "Key" => "", "Default" => "0", "Extra" => ""], ["Field" => "timeAdded", "Type" => "timestamp", "Null" => "NO", "Key" => "", "Default" => "CURRENT_TIMESTAMP", "Extra" => ""] ]; /** * MySQLDAL constructor. * Sets up parent's PDO object using the parameters that are passed in. * * @param $pdoStr * @param \PDO|null $p * @internal param \PDO $pdo */public function __construct($pdoStr, \PDO $p = null){ $this->myDBTables = [$this->userTable, $this->feedTable]; $this->correctSchemas = [$this->userTable => $this->userCorrect, $this->feedTable => $this->feedCorrect]; try{ if($p === null){ parent::$PDO = new \PDO($pdoStr, DB_USER, DB_PASSWORD); } else{ parent::$PDO = $p; } parent::$PDO->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); parent::$PDO->query("SET time_zone = \"+00:00\";"); } catch(\PDOException $e){ echo 'ERROR: ' . $e->getMessage(); throw $e; } } /** * Gets the user by the database user id * * @param int $id * @return null|User * @throws \PDOException */public function getUserByID($id){ try{ $p = parent::$PDO->prepare("SELECT * FROM $this->userTable WHERE ID=:id"); $p->bindValue(":id", $id, \PDO::PARAM_INT); $p->execute(); $rows = $p->fetchAll(\PDO::FETCH_ASSOC); if(count($rows) > 1){ return null; } if(count($rows) == 0){ return null; } $rows = $rows[0]; return $this->setUser($rows); } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Makes a new user object from a database select command. * * @param $row array Database row retrieved from another method * @return User */private function setUser($row){ $user = new User(); $user->setUserID($row["ID"]); $user->setUsername($row["username"]); $user->setPasswdDB($row["password"]); $user->setEmail($row["email"]); $user->setFname($row["firstname"]); $user->setLname($row["lastname"]); $user->setGender(intval($row["gender"])); $user->setWebID($row["webID"]); $user->setFeedText($row["feedText"]); $user->setFeedLength(intval($row["feedLength"])); if($row["feedDetails"] != ""){ $user->setFeedDetails(json_decode($row["feedDetails"], true)); } $user->setPrivateFeed($row["privateFeed"]); if($row["emailVerificationCodes"] != ""){ $user->setEmailVerificationCodes(json_decode($row["emailVerificationCodes"], true)); } if($row["passwordRecoveryCodes"] != ""){ $user->setPasswordRecoveryCodes(json_decode($row["passwordRecoveryCodes"], true)); } $user->setEmailVerified($row["emailVerified"]); return $user; } /** * Gets a User object from a webID * * @param string $webID * @return null|User * @throws \PDOException */public function getUserByWebID($webID){ try{ $p = parent::$PDO->prepare("SELECT * FROM $this->userTable WHERE webID=:id"); $p->bindValue(":id", $webID, \PDO::PARAM_STR); $p->execute(); $rows = $p->fetchAll(\PDO::FETCH_ASSOC); if(count($rows) > 1){ return null; } if(count($rows) == 0){ return null; } $rows = $rows[0]; return $this->setUser($rows); } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Gets a User from the database based on a username * * @param string $username * @return null|User * @throws \PDOException */public function getUserByUsername($username){ $username = mb_strtolower($username); try{ $p = parent::$PDO->prepare("SELECT * FROM $this->userTable WHERE username=:username"); $p->bindValue(":username", $username, \PDO::PARAM_STR); $p->execute(); $rows = $p->fetchAll(\PDO::FETCH_ASSOC); if(count($rows) > 1){ return null; } if(count($rows) == 0){ return null; } $rows = $rows[0]; return $this->setUser($rows); } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Gets User from an email * * @param string $email * @return null|User * @throws \PDOException */public function getUserByEmail($email){ $email = mb_strtolower($email); try{ $p = parent::$PDO->prepare("SELECT * FROM $this->userTable WHERE email=:email"); $p->bindValue(":email", $email, \PDO::PARAM_STR); $p->execute(); $rows = $p->fetchAll(\PDO::FETCH_ASSOC); if(count($rows) > 1){ return null; } if(count($rows) == 0){ return null; } $rows = $rows[0]; return $this->setUser($rows); } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Adds a User object to the database * * @param User $user * @throws \Exception|\PDOException */public function addUser(User $user){ if(!$this->usernameExists($user->getUsername()) && !$this->emailExists($user->getEmail())){ try{ $p = parent::$PDO->prepare("INSERT INTO $this->userTable (username, password, email, firstname, lastname, gender, webID, feedLength, feedText, feedDetails, privateFeed) VALUES (:username,:password,:email,:fname,:lname,:gender,:webID,:feedLength, :feedText,:feedDetails,:privateFeed)"); $p->bindValue(':username', $user->getUsername(), \PDO::PARAM_STR); $p->bindValue(':password', $user->getPasswd(), \PDO::PARAM_STR); $p->bindValue(':email', $user->getEmail(), \PDO::PARAM_STR); $p->bindValue(':fname', $user->getFname(), \PDO::PARAM_STR); $p->bindValue(':lname', $user->getLname(), \PDO::PARAM_STR); $p->bindValue(':gender', $user->getGender(), \PDO::PARAM_INT); $p->bindValue(':webID', $user->getWebID(), \PDO::PARAM_STR); $p->bindValue(':feedLength', $user->getFeedLength(), \PDO::PARAM_INT); $p->bindValue(':feedText', $user->getFeedText(), \PDO::PARAM_STR); $p->bindValue(':feedDetails', json_encode($user->getFeedDetails()), \PDO::PARAM_STR); $p->bindValue(':privateFeed', $user->isPrivateFeed(), \PDO::PARAM_BOOL); $p->execute(); } catch(\PDOException $e){ echo 'ERROR: ' . $e->getMessage(); throw $e; } } else{ throw new \Exception("Username or Email Address Already Exists!"); } } /** * Adds a video to the feed table * * @param Video $vid * @param User $user * @return bool * @throws \PDOException */public function addVideo(Video $vid, User $user){ try{ // Find the largest orderID and add 1 to it to use as the orderID of the newest video $p = parent::$PDO->prepare("SELECT * FROM $this->feedTable WHERE userID=:userid ORDER BY orderID DESC LIMIT 1"); $p->bindValue(":userid", $user->getUserID(), \PDO::PARAM_INT); $p->execute(); $rows = $p->fetchAll(\PDO::FETCH_ASSOC); if(count($rows) == 0){ $order = 1; } else{ $order = intval($rows[0]["orderID"]) + 1; } // Add the new Video to the user's feed $p = parent::$PDO->prepare("INSERT INTO $this->feedTable (userID, URL, videoID, videoAuthor, description, videoTitle, duration, orderID, filename, thumbnailFilename, isVideo) VALUES (:userID,:url,:videoID,:videoAuthor, :description, :videoTitle,:duration, :orderID, :filename, :thumbnailFilename, :isVideo)"); $p->bindValue(":userID", $user->getUserID(), \PDO::PARAM_INT); $p->bindValue(":videoID", $vid->getId(), \PDO::PARAM_STR); $p->bindValue(":url", $vid->getURL(), \PDO::PARAM_STR); $p->bindValue(":videoAuthor", $vid->getAuthor(), \PDO::PARAM_STR); $p->bindValue(":description", $vid->getDesc(), \PDO::PARAM_STR); $p->bindValue(":videoTitle", $vid->getTitle(), \PDO::PARAM_STR); $p->bindValue(":duration", $vid->getDuration(), \PDO::PARAM_STR); $p->bindValue(":orderID", $order, \PDO::PARAM_INT); $p->bindValue(":filename", $vid->getFilename(), \PDO::PARAM_STR); $p->bindValue(":thumbnailFilename", $vid->getThumbnailFilename(), \PDO::PARAM_STR); $p->bindValue(":isVideo", $vid->isIsVideo(), \PDO::PARAM_BOOL); $p->execute(); return true; } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Updates an existing video in the video database for a specific user * * @param Video $vid * @param User $user * @return mixed */public function updateVideo(Video $vid, User $user){ try{ $p = parent::$PDO->prepare("UPDATE $this->feedTable SET videoAuthor = :videoAuthor," . "description = :description, videoTitle = :videoTitle, duration = :duration, filename = :filename," . "thumbnailFilename = :thumbnailFilename, isVideo = :isVideo WHERE videoID = :videoID AND userID = :userID"); $p->bindValue(":userID", $user->getUserID(), \PDO::PARAM_INT); $p->bindValue(":videoID", $vid->getId(), \PDO::PARAM_STR); $p->bindValue(":videoAuthor", $vid->getAuthor(), \PDO::PARAM_STR); $p->bindValue(":description", $vid->getDesc(), \PDO::PARAM_STR); $p->bindValue(":videoTitle", $vid->getTitle(), \PDO::PARAM_STR); $p->bindValue(":duration", $vid->getDuration(), \PDO::PARAM_STR); $p->bindValue(":filename", $vid->getFilename(), \PDO::PARAM_STR); $p->bindValue(":thumbnailFilename", $vid->getThumbnailFilename(), \PDO::PARAM_STR); $p->bindValue(":isVideo", $vid->isIsVideo(), \PDO::PARAM_BOOL); $p->execute(); return true; } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Returns an array of YouTube IDs that are in the feed * * @param User $user * @return array|null * @throws \PDOException */public function getFeed(User $user){ try{ // Limit is not able to be a bound parameter, so I take the intval just to make sure nothing can get // injected $p = parent::$PDO->prepare("SELECT * FROM $this->feedTable WHERE userID=:userid ORDER BY orderID DESC LIMIT " . intval($user->getFeedLength())); $p->bindValue(":userid", $user->getUserID(), \PDO::PARAM_INT); $p->execute(); $rows = $p->fetchAll(\PDO::FETCH_ASSOC); $returner = []; if(count($rows) < 1){ return $returner; } foreach($rows as $row){ $returner[] = $this->setVideo($row); } return $returner; } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Makes a new video object from a database select command. * * @param $row array Database rows retrieved from another method * @return Video */private function setVideo($row){ $vid = new Video(); $vid->setAuthor($row["videoAuthor"]); $vid->setDesc($row["description"]); $vid->setId($row["videoID"]); $vid->setTime(strtotime($row["timeAdded"])); $vid->setDuration(intval($row["duration"])); $vid->setTitle($row["videoTitle"]); $vid->setOrder(intval($row["orderID"])); $vid->setURL($row["URL"]); $vid->setIsVideo(boolval($row["isVideo"])); $vid->setFilename($row["filename"]); $vid->setThumbnailFilename($row["thumbnailFilename"]); return $vid; } /** * Gets all the videos from the database * * @param User $user * @return mixed */public function getFullFeedHistory(User $user){ try{ // Limit is not able to be a bound parameter, so I take the intval just to make sure nothing can get // injected $p = parent::$PDO->prepare("SELECT * FROM $this->feedTable WHERE userID=:userid ORDER BY orderID DESC"); $p->bindValue(":userid", $user->getUserID(), \PDO::PARAM_INT); $p->execute(); $rows = $p->fetchAll(\PDO::FETCH_ASSOC); if(count($rows) < 1){ return []; } $returner = []; foreach($rows as $row){ $returner[] = $this->setVideo($row); } return $returner; } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Sets full feed text in the feed database * * @param User $user * @param $feed * @return bool * @throws \PDOException */public function setFeedText(User $user, $feed){ try{ $p = parent::$PDO->prepare("UPDATE $this->userTable set feedText=:feedText WHERE id=:userid"); $p->bindValue(":userid", $user->getUserID(), \PDO::PARAM_INT); $p->bindValue(":feedText", $feed, \PDO::PARAM_STR); $p->execute(); return true; } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Updates only a user's password in the database * * @param User $user */public function updateUserPassword(User $user){ try{ $p = parent::$PDO->prepare("UPDATE $this->userTable SET `password`=:passwd WHERE ID=:id"); $p->bindValue(":passwd", $user->getPasswd(), \PDO::PARAM_STR); $p->bindValue(":id", $user->getUserID(), \PDO::PARAM_INT); $p->execute(); } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Updates only a user's email verification and password recovery codes in the database * * @param User $user */public function updateUserEmailPasswordCodes(User $user){ try{ $p = parent::$PDO->prepare("UPDATE $this->userTable SET emailVerificationCodes=:email, passwordRecoveryCodes=:pass WHERE ID=:id"); $p->bindValue(":email", json_encode($user->getEmailVerificationCodes()), \PDO::PARAM_STR); $p->bindValue(":pass", json_encode($user->getPasswordRecoveryCodes()), \PDO::PARAM_STR); $p->bindValue(":id", $user->getUserID(), \PDO::PARAM_INT); $p->execute(); } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Updates the user database from a given \User object * * @param User $user * @throws \PDOException */public function updateUser(User $user){ try{ $p = parent::$PDO->prepare("UPDATE $this->userTable SET email=:email, firstname=:fname, lastname=:lname, gender=:gender, feedLength=:feedLen, username=:uname, webID=:webID, feedDetails=:feedDetails,privateFeed=:privateFeed,emailVerified=:emailVerified,emailVerificationCodes=:emailCodes,passwordRecoveryCodes=:passCodes WHERE ID=:id"); $p->bindValue(":id", $user->getUserID(), \PDO::PARAM_INT); $p->bindValue(":email", $user->getEmail(), \PDO::PARAM_STR); $p->bindValue(":fname", $user->getFname(), \PDO::PARAM_STR); $p->bindValue(":lname", $user->getLname(), \PDO::PARAM_STR); $p->bindValue(":gender", $user->getGender(), \PDO::PARAM_INT); $p->bindValue(":feedLen", $user->getFeedLength(), \PDO::PARAM_INT); $p->bindValue(":uname", $user->getUsername(), \PDO::PARAM_STR); $p->bindValue(":webID", $user->getWebID(), \PDO::PARAM_STR); $p->bindValue(":feedDetails", json_encode($user->getFeedDetails()), \PDO::PARAM_STR); $p->bindValue(":privateFeed", $user->isPrivateFeed(), \PDO::PARAM_BOOL); $p->bindValue(":emailVerified", $user->isEmailVerified(), \PDO::PARAM_BOOL); $p->bindValue(":emailCodes", json_encode($user->getEmailVerificationCodes()), \PDO::PARAM_STR); $p->bindValue(":passCodes", json_encode($user->getPasswordRecoveryCodes()), \PDO::PARAM_STR); $p->execute(); } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Generate the tables in the current database * * @param int $code * @return void * @throws \PDOException */public function makeDB($code = 1){ if($code == 1){ $generalSetupSQL = "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\"; SET time_zone = \"+00:00\";"; $sql = ""; foreach($this->myDBTables as $tableName){ $sql .= "CREATE TABLE `$tableName` ("; foreach($this->correctSchemas[$tableName] as $column){ $sql .= $this->makeColumnSQL($column) . ","; } $sql = mb_substr($sql, 0, mb_strlen($sql) - 1); $sql .= ") CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;"; } try{ // Execute all the statements $p = parent::$PDO->prepare($generalSetupSQL . $sql); $p->execute(); } catch(\PDOException $e){ echo "Database creation failed! " . $e->getMessage(); error_log("Database creation failed! " . $e->getMessage()); throw $e; } } else if($code == 2){ $this->updateDBSchema(); } } /** * Generates SQL query to make a column. Returns something in the form of `columnName` columnType NULL/Not * Default Key Extra * * @param $c array dictionary representing a column's correct schema * @return string */protected function makeColumnSQL($c){ $columnText = "`" . $c["Field"] . "` " . $c["Type"]; if($c["Null"] == "NO"){ $columnText .= " NOT NULL"; } else{ $columnText .= " NULL"; } if($c["Default"] != null){ $columnText .= " DEFAULT " . $c["Default"]; } if($c["Key"] == "PRI"){ $columnText .= " PRIMARY KEY"; } if($c["Extra"] == "auto_increment"){ $columnText .= " " . $c["Extra"]; } return $columnText; } protected function updateDBSchema(){ try{ $alterSQL = ""; foreach($this->myDBTables as $tableName){ $currentSchema = $this->describeTable($tableName); $alterSQL .= $this->makeAlterQuery([$tableName => $currentSchema], [$tableName => $this->correctSchemas[$tableName]]); } parent::$PDO->exec($alterSQL); } catch(\PDOException $e){ echo "Database update failed! " . $e->getMessage(); error_log("Database update failed! " . $e->getMessage()); throw $e; } } /** * Function to get layout of a specific table * * @param $table string Table to get layout of * @return array */protected function describeTable($table){ $p = parent::$PDO->prepare("DESCRIBE $table"); $p->execute(); return $p->fetchAll(\PDO::FETCH_ASSOC); } /** * Generates SQL query to add missing columns to the given tables * * @param $currentTables array dictionary in the form of ["tableName"=>[table_schema]] representing the values * that are currently existing in the database * @param $correctTables array dictionary in the form of ["tableName"=>[table_schema]] representing the correct * values * @return string */protected function makeAlterQuery($currentTables, $correctTables){ $sql = ""; // Loop through the given tables foreach($correctTables as $tableName => $table){ // Loop through all the columns in a table foreach($table as $i => $correct){ // Check if the current column is in the existing database table if(!in_array($correct, $currentTables[$tableName], true)){ $sql .= "ALTER TABLE `" . $tableName . "` ADD " . $this->makeColumnSQL($correct); if($i == 0){ $sql .= " FIRST"; } if($i > 0){ $sql .= " AFTER `" . $table[$i - 1]["Field"] . "`"; } $sql .= ";"; } } } return $sql; } /** * Verifies the currently connected database against the current schema * * @return int Returns 0 if all is well, 1 if the user table or feed table do not exist, and 2 if the tables exist * but the schema inside is wrong * @throws \PDOException */public function verifyDB(){ try{ $tables = $this->getDatabaseTables(); foreach($this->myDBTables as $tableName){ if(!in_array($tableName, $tables, true)){ return 1; } } foreach($this->myDBTables as $tableName){ $currentTableSchema = $this->describeTable($tableName); if(!$this->verifySchema($this->correctSchemas[$tableName], $currentTableSchema)){ return 2; } } return 0; } catch(\PDOException $e){ echo "ERROR: " . $e->getMessage(); throw $e; } } /** * Function to return a list of database tables * * @return array */protected function getDatabaseTables(){ $p = parent::$PDO->prepare("SHOW TABLES"); $p->execute(); $rows = $p->fetchAll(\PDO::FETCH_ASSOC); $tables = []; foreach($rows as $r){ $tables[] = array_values($r)[0]; } return $tables; } /** * Checks if two arrays are equal to test that SQL schemas are compliant * * @param $correct * @param $existing * @return bool */private function verifySchema($correct, $existing){ sort($correct); sort($existing); return $correct == $existing; } /** * Returns an array of video IDs that can be safely deleted * * @return array */public function getPrunableVideos(){ $feedTable = $this->feedTable; $userTable = $this->userTable; $pruneSQL = "SELECT `videoID`, MIN((MaxOrderID-orderID)>=feedLength) AS `isUnNeeded` FROM (SELECT`" . $feedTable . "`.`userID`, `" . $userTable . "`.`feedLength`, videoID, orderID FROM`" . $feedTable . "` INNER JOIN`" . $userTable . "` ON `" . $feedTable . "`.`userID` = `" . $userTable . "`.`ID`) Y INNER JOIN (SELECT `userID`, MAX(`orderID`) AS MaxOrderID FROM `" . $feedTable . "` GROUP BY `userID`) AS X ON X.userID=Y.`userID` GROUP BY `videoID`ORDER BY `isUnNeeded` DESC"; $p = parent::$PDO->prepare($pruneSQL); $p->execute(); $rows = $p->fetchAll(\PDO::FETCH_ASSOC); $pruneArray = []; foreach($rows as $r){ if($r["isUnNeeded"] == 0){ continue; } $pruneArray[] = $r["videoID"]; } return $pruneArray; } } |