/** * Test inserts */ public function testInserts() { $TestClothes = $this->DB->insert('runalyze_clothes', array('name'), array('Test-Clothes')); $TestSport = $this->DB->insert('runalyze_sport', array('name'), array('Testsport')); $TestType = $this->DB->insert('runalyze_type', array('name', 'sportid'), array('Testtype', $TestSport)); // Act $Importer = new RunalyzeJsonImporter('../tests/testfiles/backup/default-insert.json.gz'); $Importer->importData(); // Check nothing changed $this->assertEquals($TestClothes, $this->DB->query('SELECT `id` FROM `runalyze_clothes` WHERE `name`="Test-Clothes"')->fetchColumn()); $this->assertEquals($TestSport, $this->DB->query('SELECT `id` FROM `runalyze_sport` WHERE `name`="Testsport"')->fetchColumn()); $this->assertEquals($TestType, $this->DB->query('SELECT `id` FROM `runalyze_type` WHERE `name`="Testtype"')->fetchColumn()); // Check existing/new $NewClothes = $this->DB->query('SELECT `id` FROM `runalyze_clothes` WHERE `name`="New-Clothes"')->fetchColumn(); $NewSport = $this->DB->query('SELECT `id` FROM `runalyze_sport` WHERE `name`="Newsport"')->fetchColumn(); $NewType = $this->DB->query('SELECT `id` FROM `runalyze_type` WHERE `name`="Newtype"')->fetchColumn(); $NewShoe = $this->DB->query('SELECT `id` FROM `runalyze_shoe` WHERE `name`="Testshoe"')->fetchColumn(); $this->assertNotEquals(0, $NewClothes); $this->assertNotEquals(0, $NewSport); $this->assertNotEquals(0, $NewType); $this->assertNotEquals(0, $NewShoe); // Check inserts $this->assertEquals(array('time' => '1234567890', 'weight' => '70', 'pulse_rest' => '45', 'pulse_max' => '205'), $this->DB->query('SELECT `time`, `weight`, `pulse_rest`, `pulse_max` FROM `runalyze_user` WHERE `time`="1234567890" LIMIT 1')->fetch()); $this->assertEquals(array('time' => '1234567890', 'sportid' => $TestSport, 'typeid' => $TestType, 's' => '900.00', 'clothes' => $TestClothes . ',' . $NewClothes, 'shoeid' => '0'), $this->DB->query('SELECT `time`, `sportid`, `typeid`, `s`, `clothes`, `shoeid` FROM `runalyze_training` WHERE `comment`="UNITTEST-1" LIMIT 1')->fetch()); $this->assertEquals(array('time' => '1234567890', 'sportid' => $NewSport, 'typeid' => $NewType, 's' => '1500.00', 'clothes' => $NewClothes, 'shoeid' => $NewShoe), $this->DB->query('SELECT `time`, `sportid`, `typeid`, `s`, `clothes`, `shoeid` FROM `runalyze_training` WHERE `comment`="UNITTEST-2" LIMIT 1')->fetch()); }
/** * Init $this->sports_short */ protected function initShortSports() { $this->sports_short = array(); $sports = $this->DB->query('SELECT `id` FROM `' . PREFIX . 'sport` WHERE `short`=1 AND accountid = ' . SessionAccountHandler::getId())->fetchAll(); foreach ($sports as $sport) { $this->sports_short[] = $sport['id']; } }
/** * Fetch data * @param string $tablename * @param int $id * @return array */ protected function fetch($tablename, $id) { $field = $this->primaryKey($tablename); $AndAccountID = $this->hasAccountID() && $this->tableHasAccountid($tablename) ? 'AND `accountid`=' . (int) $this->AccountID : ''; // TODO: // provide a full fetch for tables as sport / type $result = $this->DB->query('SELECT * FROM `' . PREFIX . $tablename . '` WHERE `' . $field . '`=' . (int) $id . ' ' . $AndAccountID . ' LIMIT 1')->fetch(); if (!is_array($result)) { return array(); } return $result; }
/** * Fetch data * @param string $tablename * @param int $id * @param boolean $fullFetch * @return array */ protected function fetch($tablename, $id, $fullFetch = false) { $field = $this->primaryKey($tablename); $AndAccountID = $this->hasAccountID() && $this->tableHasAccountid($tablename) ? 'AND `accountid`=' . (int) $this->AccountID : ''; if ($fullFetch) { $result = $this->DB->query('SELECT * FROM `' . PREFIX . $tablename . '` WHERE 1 ' . $AndAccountID . ' ' . $this->orderBy($tablename))->fetchAll(); } else { $result = $this->DB->query('SELECT * FROM `' . PREFIX . $tablename . '` WHERE `' . $field . '`=' . (int) $id . ' ' . $AndAccountID . ' LIMIT 1')->fetch(); } if (!is_array($result)) { return array(); } return $result; }
/** * Correct references in configuration */ private function correctConfigReferences() { if (isset($_POST['overwrite_config'])) { $ConfigValues = Configuration\Handle::tableHandles(); foreach ($ConfigValues as $key => $table) { $table = 'runalyze_' . $table; if (isset($this->ReplaceIDs[$table])) { $OldValue = $this->DB->query('SELECT `value` FROM `' . PREFIX . 'conf` WHERE `key`="' . $key . '" LIMIT 1')->fetchColumn(); $NewValue = $this->correctID($table, $OldValue); if ($NewValue != 0) { $this->DB->updateWhere('conf', '`key`="' . $key . '"', 'value', $NewValue); } } } } }
/** * @covers PDOforRunalyze::insert * @covers PDOforRunalyze::update * @covers PDOforRunalyze::updateWhere * @covers PDOforRunalyze::exec */ public function testUpdate() { $this->object->insert('training', array('id', 's', 'distance'), array(1, 600, 1)); $this->object->insert('training', array('id', 's', 'distance'), array(2, 900, 1)); $this->object->insert('training', array('id', 's', 'distance'), array(3, 300, 1)); $this->object->update('training', 1, 'distance', 2); $this->assertEquals(array(600, 2), $this->object->query('SELECT `s`, `distance` FROM `runalyze_training` WHERE `id`=1 LIMIT 1')->fetch(PDO::FETCH_NUM)); $this->object->update('training', 2, 'distance', 3); $this->assertEquals(array(900, 3), $this->object->query('SELECT `s`, `distance` FROM `runalyze_training` WHERE `id`=2 LIMIT 1')->fetch(PDO::FETCH_NUM)); $this->object->update('training', 3, array('s', 'distance'), array(150, 0.5)); $this->assertEquals(array(150, 0.5), $this->object->query('SELECT `s`, `distance` FROM `runalyze_training` WHERE `id`=3 LIMIT 1')->fetch(PDO::FETCH_NUM)); $this->object->updateWhere('training', '`distance` > 1', 'comment', 'Super weit.'); $this->assertEquals("1,2", $this->object->query('SELECT GROUP_CONCAT(`id`) FROM `runalyze_training` WHERE `comment`="Super weit." GROUP BY `accountid`')->fetchColumn()); $this->assertEquals(3, $this->object->exec('DELETE FROM `runalyze_training`')); $this->assertEquals(0, $this->object->query('SELECT COUNT(*) FROM `runalyze_training`')->fetchColumn()); $this->object->exec('TRUNCATE TABLE `runalyze_training`'); }
/** * Test deletes */ public function testDontDeleteTooMuch() { $_POST['delete_trainings'] = true; // Data of account 0 $this->DB->exec('INSERT INTO `runalyze_equipment_type` (`accountid`,`name`) VALUES(0, "")'); $FirstEquipmentType = $this->DB->lastInsertId(); $this->DB->exec('INSERT INTO `runalyze_equipment` (`accountid`,`typeid`,`name`,`notes`) VALUES(0, ' . $FirstEquipmentType . ', "", "")'); $FirstEquipment = $this->DB->lastInsertId(); $this->DB->exec('INSERT INTO `runalyze_route` (`accountid`) VALUES(0)'); $FirstRoute = $this->DB->lastInsertId(); $this->DB->exec('INSERT INTO `runalyze_training` (`accountid`,`routeid`) VALUES(0, ' . $FirstRoute . ')'); $FirstTraining = $this->DB->lastInsertId(); $this->DB->exec('INSERT INTO `runalyze_trackdata` (`accountid`,`activityid`) VALUES(0, ' . $FirstTraining . ')'); $this->DB->exec('INSERT INTO `runalyze_swimdata` (`accountid`,`activityid`) VALUES(0, ' . $FirstTraining . ')'); $this->DB->exec('INSERT INTO `runalyze_hrv` (`accountid`,`activityid`) VALUES(0, ' . $FirstTraining . ')'); $this->DB->exec('INSERT INTO `runalyze_activity_equipment` (`activityid`,`equipmentid`) VALUES(' . $FirstTraining . ', ' . $FirstEquipment . ')'); // Data of account 1 $this->DB->exec('INSERT INTO `runalyze_equipment_type` (`accountid`,`name`) VALUES(1, "")'); $SecondEquipmentType = $this->DB->lastInsertId(); $this->DB->exec('INSERT INTO `runalyze_equipment` (`accountid`,`typeid`,`name`,`notes`) VALUES(1, ' . $SecondEquipmentType . ', "", "")'); $SecondEquipment = $this->DB->lastInsertId(); $this->DB->exec('INSERT INTO `runalyze_route` (`accountid`) VALUES(1)'); $SecondRoute = $this->DB->lastInsertId(); $this->DB->exec('INSERT INTO `runalyze_training` (`accountid`,`routeid`) VALUES(1, ' . $SecondRoute . ')'); $SecondTraining = $this->DB->lastInsertId(); $this->DB->exec('INSERT INTO `runalyze_trackdata` (`accountid`,`activityid`) VALUES(1, ' . $SecondTraining . ')'); $this->DB->exec('INSERT INTO `runalyze_swimdata` (`accountid`,`activityid`) VALUES(1, ' . $SecondTraining . ')'); $this->DB->exec('INSERT INTO `runalyze_hrv` (`accountid`,`activityid`) VALUES(1, ' . $SecondTraining . ')'); $this->DB->exec('INSERT INTO `runalyze_activity_equipment` (`activityid`,`equipmentid`) VALUES(' . $SecondTraining . ', ' . $SecondEquipment . ')'); $Importer = new RunalyzeJsonImporter('../tests/testfiles/backup/default-empty.json.gz', 0); $Importer->importData(); $this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_route` WHERE `accountid`=0')->fetchColumn()); $this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_training` WHERE `accountid`=0')->fetchColumn()); $this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_trackdata` WHERE `accountid`=0')->fetchColumn()); $this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_swimdata` WHERE `accountid`=0')->fetchColumn()); $this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_hrv` WHERE `accountid`=0')->fetchColumn()); $this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_activity_equipment` WHERE `equipmentid`=' . $FirstEquipment)->fetchColumn()); $this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_route` WHERE `accountid`=1')->fetchColumn()); $this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_training` WHERE `accountid`=1')->fetchColumn()); $this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_trackdata` WHERE `accountid`=1')->fetchColumn()); $this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_swimdata` WHERE `accountid`=1')->fetchColumn()); $this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_hrv` WHERE `accountid`=1')->fetchColumn()); $this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_activity_equipment` WHERE `equipmentid`=' . $SecondEquipment)->fetchColumn()); }
/** * Test with existing equipment */ public function testWithExistingEquipment() { $this->DB->exec('INSERT INTO `runalyze_sport` (`name`, `accountid`) VALUES("Sport A", ' . $this->AccountID . ')'); $ExistingSportA = $this->DB->lastInsertId(); $this->DB->exec('INSERT INTO `runalyze_equipment_type` (`name`, `accountid`) VALUES("Typ A", ' . $this->AccountID . ')'); $ExistingTypeA = $this->DB->lastInsertId(); $this->DB->exec('INSERT INTO `runalyze_equipment_type` (`name`, `accountid`) VALUES("Typ AB", ' . $this->AccountID . ')'); $ExistingTypeAB = $this->DB->lastInsertId(); $this->DB->exec('INSERT INTO `runalyze_equipment_sport` (`sportid`, `equipment_typeid`) VALUES(' . $ExistingSportA . ', ' . $ExistingTypeA . ')'); $Importer = new RunalyzeJsonImporter('../tests/testfiles/backup/with-equipment.json.gz', $this->AccountID); $Importer->importData(); $SportA = $this->DB->query('SELECT `id` FROM `runalyze_sport` WHERE `name`="Sport A"')->fetchColumn(); $SportB = $this->DB->query('SELECT `id` FROM `runalyze_sport` WHERE `name`="Sport B"')->fetchColumn(); $this->assertEquals($ExistingSportA, $SportA); $TypeA = $this->DB->query('SELECT `id` FROM `runalyze_equipment_type` WHERE `name`="Typ A"')->fetchColumn(); $TypeAB = $this->DB->query('SELECT `id` FROM `runalyze_equipment_type` WHERE `name`="Typ AB"')->fetchColumn(); $this->assertEquals($ExistingTypeA, $TypeA); $this->assertEquals($ExistingTypeAB, $TypeAB); $this->assertEquals(array(array($SportA, $TypeA), array($SportA, $TypeAB), array($SportB, $TypeAB)), $this->DB->query('SELECT `sportid`, `equipment_typeid` FROM `runalyze_equipment_sport`')->fetchAll(PDO::FETCH_NUM)); }
/** * Init $this->sports_short */ protected function initShortModes() { $this->sports_short = $this->DB->query('SELECT `id` FROM `' . PREFIX . 'sport` WHERE `short`=1 AND accountid = ' . SessionAccountHandler::getId())->fetchAll(PDO::FETCH_COLUMN); $this->types_short = $this->DB->query('SELECT `id` FROM `' . PREFIX . 'type` WHERE `short`=1 AND accountid = ' . SessionAccountHandler::getId())->fetchAll(PDO::FETCH_COLUMN); }
/** * Init $this->sports_short */ protected function initShortModes() { $this->SportsShort = $this->DB->query('SELECT `id` FROM `' . PREFIX . 'sport` WHERE `short`=1 AND accountid = ' . $this->AccountID)->fetchAll(PDO::FETCH_COLUMN); $this->TypesShort = $this->DB->query('SELECT `id` FROM `' . PREFIX . 'type` WHERE `short`=1 AND accountid = ' . $this->AccountID)->fetchAll(PDO::FETCH_COLUMN); }
/** * Equipment type IDs * @return array */ private function fetchEquipmentTypeIDs() { return $this->DB->query('SELECT `id` FROM `' . PREFIX . 'equipment_type` WHERE `accountid`=' . $this->AccountID)->fetchAll(PDO::FETCH_COLUMN); }
/** * Execute * @param \PDOforRunalyze $DB */ public function execute(\PDOforRunalyze $DB) { $this->Data = array(); $Today = new \DateTime('today 23:59'); $Statement = $DB->query($this->query()); while ($row = $Statement->fetch()) { // Don't rely on MySQLs timezone => calculate diff based on timestamp $index = (int) $Today->diff(new \DateTime('@' . $row['time']))->format('%r%a'); $this->Data[$index] = $row['trimp']; } }
/** * Plugin IDs * @return array */ private function fetchPluginIDs() { return $this->DB->query('SELECT `id` FROM `' . PREFIX . 'plugin` WHERE `accountid`=' . $this->AccountID)->fetchAll(PDO::FETCH_COLUMN); }