protected static function beforeTableDataCached()
 {
     $date = Date::factory('2010-03-01');
     $archiveTableCreator = new ArchiveTableCreator();
     $archiveTableCreator->getBlobTable($date);
     $archiveTableCreator->getNumericTable($date);
 }
 public static function setUpBeforeClass()
 {
     parent::setUpBeforeClass();
     ArchiveTableCreator::getNumericTable(Date::factory('2015-01-01'));
     ArchiveTableCreator::getNumericTable(Date::factory('2015-02-02'));
     ArchiveTableCreator::getNumericTable(Date::factory('2015-03-03'));
 }
 public function setUp()
 {
     parent::setUp();
     $this->archiveTableDao = self::$fixture->piwikEnvironment->getContainer()->get('Piwik\\DataAccess\\ArchiveTableDao');
     ArchiveTableCreator::getBlobTable(Date::factory('2015-01-01'));
     ArchiveTableCreator::getNumericTable(Date::factory('2015-01-01'));
 }
Exemple #4
0
 protected static function deleteArchiveIds(Date $date, $idArchivesToDelete)
 {
     $batches = array_chunk($idArchivesToDelete, 1000);
     foreach ($batches as $idsToDelete) {
         $query = "DELETE FROM %s WHERE idarchive IN (" . implode(',', $idsToDelete) . ")";
         Db::query(sprintf($query, ArchiveTableCreator::getNumericTable($date)));
         try {
             Db::query(sprintf($query, ArchiveTableCreator::getBlobTable($date)));
         } catch (Exception $e) {
             // Individual blob tables could be missing
         }
     }
 }
 public function assertArchivesExist($expectedPresentArchiveIds, $archiveDate)
 {
     $numericTable = ArchiveTableCreator::getNumericTable($archiveDate);
     $blobTable = ArchiveTableCreator::getBlobTable($archiveDate);
     $numericArchiveCount = $this->getArchiveRowCountWithId($numericTable, $expectedPresentArchiveIds);
     $expectedNumericRowCount = count($expectedPresentArchiveIds) * 3;
     // two metrics + 1 done row
     $this->assertEquals($expectedNumericRowCount, $numericArchiveCount);
     $blobArchiveCount = $this->getArchiveRowCountWithId($blobTable, $expectedPresentArchiveIds);
     $expectedBlobRowCount = count($expectedPresentArchiveIds) * 2;
     // two blob rows
     $this->assertEquals($expectedBlobRowCount, $blobArchiveCount);
 }
 /**
  * Queries and returns archive data using a set of archive IDs.
  *
  * @param array $archiveIds The IDs of the archives to get data from.
  * @param array $recordNames The names of the data to retrieve (ie, nb_visits, nb_actions, etc.)
  * @param string $archiveDataType The archive data type (either, 'blob' or 'numeric').
  * @param bool $loadAllSubtables Whether to pre-load all subtables
  * @throws Exception
  * @return array
  */
 public static function getArchiveData($archiveIds, $recordNames, $archiveDataType, $loadAllSubtables)
 {
     // create the SQL to select archive data
     $inNames = Common::getSqlStringFieldsArray($recordNames);
     if ($loadAllSubtables) {
         $name = reset($recordNames);
         // select blobs w/ name like "$name_[0-9]+" w/o using RLIKE
         $nameEnd = strlen($name) + 2;
         $whereNameIs = "(name = ?\n                            OR (name LIKE ?\n                                 AND SUBSTRING(name, {$nameEnd}, 1) >= '0'\n                                 AND SUBSTRING(name, {$nameEnd}, 1) <= '9') )";
         $bind = array($name, $name . '%');
     } else {
         $whereNameIs = "name IN ({$inNames})";
         $bind = array_values($recordNames);
     }
     $getValuesSql = "SELECT value, name, idsite, date1, date2, ts_archived\n                                FROM %s\n                                WHERE idarchive IN (%s)\n                                  AND " . $whereNameIs;
     // get data from every table we're querying
     $rows = array();
     foreach ($archiveIds as $period => $ids) {
         if (empty($ids)) {
             throw new Exception("Unexpected: id archive not found for period '{$period}' '");
         }
         // $period = "2009-01-04,2009-01-04",
         $date = Date::factory(substr($period, 0, 10));
         if ($archiveDataType == 'numeric') {
             $table = ArchiveTableCreator::getNumericTable($date);
         } else {
             $table = ArchiveTableCreator::getBlobTable($date);
         }
         $sql = sprintf($getValuesSql, $table, implode(',', $ids));
         $dataRows = Db::fetchAll($sql, $bind);
         foreach ($dataRows as $row) {
             $rows[] = $row;
         }
     }
     return $rows;
 }
Exemple #7
0
 /**
  * Deletes by batches Archive IDs in the specified month,
  *
  * @param Date $date
  * @param $idArchivesToDelete
  * @return int Number of rows deleted from both numeric + blob table.
  */
 protected function deleteArchiveIds(Date $date, $idArchivesToDelete)
 {
     $batches = array_chunk($idArchivesToDelete, 1000);
     $numericTable = ArchiveTableCreator::getNumericTable($date);
     $blobTable = ArchiveTableCreator::getBlobTable($date);
     $deletedCount = 0;
     foreach ($batches as $idsToDelete) {
         $deletedCount += $this->model->deleteArchiveIds($numericTable, $blobTable, $idsToDelete);
     }
     return $deletedCount;
 }
 private function insertArchiveRow($idSite, $date, $periodLabel)
 {
     $periodObject = \Piwik\Period\Factory::build($periodLabel, $date);
     $dateStart = $periodObject->getDateStart();
     $dateEnd = $periodObject->getDateEnd();
     $table = ArchiveTableCreator::getNumericTable($dateStart);
     $idArchive = (int) Db::fetchOne("SELECT MAX(idarchive) FROM {$table} WHERE name LIKE 'done%'");
     $idArchive = $idArchive + 1;
     $periodId = Piwik::$idPeriods[$periodLabel];
     $doneFlag = 'done';
     if ($idArchive % 5 == 1) {
         $doneFlag = Rules::getDoneFlagArchiveContainsAllPlugins(self::$segment1);
     } else {
         if ($idArchive % 5 == 2) {
             $doneFlag .= '.VisitsSummary';
         } else {
             if ($idArchive % 5 == 3) {
                 $doneFlag = Rules::getDoneFlagArchiveContainsOnePlugin(self::$segment1, 'UserCountry');
             } else {
                 if ($idArchive % 5 == 4) {
                     $doneFlag = Rules::getDoneFlagArchiveContainsAllPlugins(self::$segment2);
                 }
             }
         }
     }
     $sql = "INSERT INTO {$table} (idarchive, name, idsite, date1, date2, period, ts_archived)\n                     VALUES ({$idArchive}, 'nb_visits', {$idSite}, '{$dateStart}', '{$dateEnd}', {$periodId}, NOW()),\n                            ({$idArchive}, '{$doneFlag}', {$idSite}, '{$dateStart}', '{$dateEnd}', {$periodId}, NOW())";
     Db::query($sql);
 }
Exemple #9
0
 protected function getTableNumeric()
 {
     return ArchiveTableCreator::getNumericTable($this->dateStart);
 }
Exemple #10
0
 protected static function getTemporaryArchiveIdsOlderThan(Date $date, $purgeArchivesOlderThan)
 {
     $query = "SELECT idarchive\n                FROM " . ArchiveTableCreator::getNumericTable($date) . "\n                WHERE name LIKE 'done%'\n                    AND ((  value = " . ArchiveWriter::DONE_OK_TEMPORARY . "\n                            AND ts_archived < ?)\n                         OR value = " . ArchiveWriter::DONE_ERROR . ")";
     $result = Db::fetchAll($query, array($purgeArchivesOlderThan));
     $idArchivesToDelete = array();
     if (!empty($result)) {
         foreach ($result as $row) {
             $idArchivesToDelete[] = $row['idarchive'];
         }
     }
     return $idArchivesToDelete;
 }
Exemple #11
0
 /**
  * Queries and returns archive data using a set of archive IDs.
  *
  * @param array $archiveIds The IDs of the archives to get data from.
  * @param array $recordNames The names of the data to retrieve (ie, nb_visits, nb_actions, etc.)
  * @param string $archiveDataType The archive data type (either, 'blob' or 'numeric').
  * @param int|null|string $idSubtable  null if the root blob should be loaded, an integer if a subtable should be
  *                                     loaded and 'all' if all subtables should be loaded.
  * @throws Exception
  * @return array
  */
 public function getArchiveData($archiveIds, $recordNames, $archiveDataType, $idSubtable)
 {
     $chunk = new Chunk();
     // create the SQL to select archive data
     $loadAllSubtables = $idSubtable == Archive::ID_SUBTABLE_LOAD_ALL_SUBTABLES;
     if ($loadAllSubtables) {
         $name = reset($recordNames);
         // select blobs w/ name like "$name_[0-9]+" w/o using RLIKE
         $nameEnd = strlen($name) + 1;
         $nameEndAppendix = $nameEnd + 1;
         $appendix = $chunk->getAppendix();
         $lenAppendix = strlen($appendix);
         $checkForChunkBlob = "SUBSTRING(name FROM {$nameEnd} FOR {$lenAppendix}) = '{$appendix}'";
         $checkForSubtableId = "(SUBSTRING(name FROM {$nameEndAppendix} FOR 1) >= '0'\n                                    AND SUBSTRING(name FROM {$nameEndAppendix} FOR 1) <= '9')";
         $whereNameIs = "(name = ? OR (name LIKE ? AND ( {$checkForChunkBlob} OR {$checkForSubtableId} ) ))";
         $bind = array($name, $name . '%');
     } else {
         if ($idSubtable === null) {
             // select root table or specific record names
             $bind = array_values($recordNames);
         } else {
             // select a subtable id
             $bind = array();
             foreach ($recordNames as $recordName) {
                 // to be backwards compatibe we need to look for the exact idSubtable blob and for the chunk
                 // that stores the subtables (a chunk stores many blobs in one blob)
                 $bind[] = $chunk->getRecordNameForTableId($recordName, $idSubtable);
                 $bind[] = ArchiveSelector::appendIdSubtable($recordName, $idSubtable);
             }
         }
         $inNames = Common::getSqlStringFieldsArray($bind);
         $whereNameIs = "name IN ({$inNames})";
     }
     $getValuesSql = "SELECT %s, name, idsite, date1, date2, ts_archived\n                         FROM %s\n                         WHERE idarchive IN (%s)\n                           AND " . $whereNameIs;
     // get data from every table we're querying
     $rows = array();
     foreach ($archiveIds as $period => $ids) {
         if (empty($ids)) {
             throw new Exception("Unexpected: id archive not found for period '{$period}' '");
         }
         // $period = "2009-01-04,2009-01-04",
         $date = Date::factory(substr($period, 0, 10));
         $isNumeric = $archiveDataType == 'numeric';
         if ($isNumeric) {
             $table = ArchiveTableCreator::getNumericTable($date);
         } else {
             $table = ArchiveTableCreator::getBlobTable($date);
         }
         $valueCol = $this->prepareForBinary($table);
         $sql = sprintf($getValuesSql, $valueCol, $table, implode(',', $ids));
         $dataRows = $this->db->fetchAll($sql, $bind);
         $dataRows = $this->binaryOutput($dataRows, true);
         foreach ($dataRows as $row) {
             if ($isNumeric) {
                 $rows[] = $row;
             } else {
                 $row['value'] = $this->uncompress($row['value']);
                 if ($chunk->isRecordNameAChunk($row['name'])) {
                     $this->moveChunkRowToRows($rows, $row, $chunk, $loadAllSubtables, $idSubtable);
                 } else {
                     $rows[] = $row;
                 }
             }
         }
     }
     return $rows;
 }
Exemple #12
0
 public function deleteByPeriodRange(Date $date)
 {
     $query = "DELETE FROM %s WHERE period = ? AND ts_archived < ?";
     $yesterday = Date::factory('yesterday')->getDateTime();
     $bind = array(Piwik::$idPeriods['range'], $yesterday);
     $numericTable = ArchiveTableCreator::getNumericTable($date);
     $this->db->query(sprintf($query, $numericTable), $bind);
     Log::debug("Purging Custom Range archives: done [ purged archives older than {$yesterday} from {$numericTable} / blob ]");
     try {
         $this->db->query(sprintf($query, ArchiveTableCreator::getBlobTable($date)), $bind);
     } catch (Exception $e) {
         // Individual blob tables could be missing
     }
 }
 protected static function deleteArchiveIds(Date $date, $idArchivesToDelete)
 {
     $batches = array_chunk($idArchivesToDelete, 1000);
     $numericTable = ArchiveTableCreator::getNumericTable($date);
     $blobTable = ArchiveTableCreator::getBlobTable($date);
     foreach ($batches as $idsToDelete) {
         self::getModel()->deleteArchiveIds($numericTable, $blobTable, $idsToDelete);
     }
 }
 /**
  * Queries and returns archive IDs for a set of sites, periods, and a segment.
  *
  * @param array $siteIds
  * @param array $periods
  * @param Segment $segment
  * @param array $plugins List of plugin names for which data is being requested.
  * @return array Archive IDs are grouped by archive name and period range, ie,
  *               array(
  *                   'VisitsSummary.done' => array(
  *                       '2010-01-01' => array(1,2,3)
  *                   )
  *               )
  * @throws
  */
 public static function getArchiveIds($siteIds, $periods, $segment, $plugins)
 {
     if (empty($siteIds)) {
         throw new \Exception("Website IDs could not be read from the request, ie. idSite=");
     }
     foreach ($siteIds as $index => $siteId) {
         $siteIds[$index] = (int) $siteId;
     }
     $monthToPeriods = array();
     foreach ($periods as $period) {
         /** @var Period $period */
         $table = ArchiveTableCreator::getNumericTable($period->getDateStart());
         $monthToPeriods[$table][] = $period;
     }
     $nameCondition = self::getNameCondition($plugins, $segment);
     $Archive = Factory::getDAO('archive');
     $results = $Archive->getArchiveIds($siteIds, $monthToPeriods, $nameCondition);
     return $results;
 }
 /**
  * @param OutputInterface $output
  * @param Date[] $dates
  * @param bool $forceOptimzation
  */
 private function optimizeArchiveTables(OutputInterface $output, $dates, $forceOptimzation = false)
 {
     $output->writeln("Optimizing archive tables...");
     foreach ($dates as $date) {
         $numericTable = ArchiveTableCreator::getNumericTable($date);
         $this->performTimedPurging($output, "Optimizing table {$numericTable}...", function () use($numericTable, $forceOptimzation) {
             Db::optimizeTables($numericTable, $forceOptimzation);
         });
         $blobTable = ArchiveTableCreator::getBlobTable($date);
         $this->performTimedPurging($output, "Optimizing table {$blobTable}...", function () use($blobTable, $forceOptimzation) {
             Db::optimizeTables($blobTable, $forceOptimzation);
         });
     }
 }