public function analytics_downloads_per_day() { \Podlove\Feeds\check_for_and_do_compression('text/plain'); $episode_id = isset($_GET['episode']) ? (int) $_GET['episode'] : 0; $cache = \Podlove\Cache\TemplateCache::get_instance(); echo $cache->cache_for('podlove_analytics_dpd_' . $episode_id, function () use($episode_id) { global $wpdb; $episode_cond = ""; if ($episode_id) { $episode_cond = " AND episode_id = {$episode_id}"; } $sql = "SELECT COUNT(*) downloads, post_title, access_date, episode_id, post_id\n\t\t\t\t\tFROM (\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\tmedia_file_id, accessed_at, DATE(accessed_at) access_date, episode_id\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t" . Model\DownloadIntent::table_name() . " di \n\t\t\t\t\t\t\tINNER JOIN " . Model\MediaFile::table_name() . " mf ON mf.id = di.media_file_id\n\t\t\t\t\t\tWHERE 1 = 1 {$episode_cond}\n\t\t\t\t\t\tGROUP BY media_file_id, request_id, access_date\n\t\t\t\t\t) di\n INNER JOIN " . Model\Episode::table_name() . " e ON episode_id = e.id\n\t\t\t\t\tINNER JOIN {$wpdb->posts} p ON e.post_id = p.ID\n\t\t\t\t\tWHERE accessed_at > p.post_date_gmt\n\t\t\t\t\tGROUP BY access_date, episode_id"; $results = $wpdb->get_results($sql, ARRAY_N); $release_date = min(array_column($results, 2)); $csv = '"downloads","title","date","episode_id","post_id","days"' . "\n"; foreach ($results as $row) { $row[1] = '"' . str_replace('"', '""', $row[1]) . '"'; // quote & escape title $row[] = date_diff(date_create($release_date), date_create($row[2]))->format('%a'); $csv .= implode(",", $row) . "\n"; } return $csv; }, 3600); exit; }
private static function get_downloads_per_hour_for_episode($episode_id) { global $wpdb; $sql = "\n\t\t\tSELECT\n\t\t\t \tCOUNT(*) downloads, DATE_FORMAT(accessed_at, '%%Y-%%m-%%d %%H') AS access_hour\n\t\t\tFROM\t\t\n\t\t\t\t\t`" . \Podlove\Model\DownloadIntentClean::table_name() . "` di \n\t\t\t\t\tINNER JOIN `" . \Podlove\Model\MediaFile::table_name() . "` mf ON mf.id = di.media_file_id\n\t\t\t\t\tWHERE episode_id = %d\n\t\t\tGROUP BY access_hour\n\t\t\tORDER BY access_hour\n\t\t\tLIMIT %d\n\t\t"; $data = $wpdb->get_results($wpdb->prepare($sql, $episode_id, self::HOURS_TO_CALCULATE), ARRAY_A); $release_date = $wpdb->get_var($wpdb->prepare("SELECT post_date FROM {$wpdb->posts} p JOIN " . \Podlove\Model\Episode::table_name() . " e ON e.post_id = p.ID WHERE e.id = %d", $episode_id)); if ($data) { $missing_hours = self::add_missing_hours($data, $release_date); array_splice($missing_hours, self::HOURS_TO_CALCULATE); return array_column($missing_hours, 'downloads'); } else { return array(); } }
public static function get_related_episodes($episode_id = FALSE) { global $wpdb; if (!$episode_id) { return array(); } $sql = sprintf('SELECT * FROM ' . Episode::table_name() . ' e WHERE id IN ( SELECT right_episode_id FROM ' . self::table_name() . ' WHERE left_episode_id = %1$d UNION SELECT left_episode_id FROM ' . self::table_name() . ' WHERE right_episode_id = %1$d )', $episode_id); return Episode::find_all_by_sql($sql); }
function podlove_get_duplicate_episode_id(Episode $current_episode) { global $wpdb; $sql = $wpdb->prepare(' SELECT p.ID FROM `' . $wpdb->posts . '` p JOIN `' . Episode::table_name() . '` e ON e.`post_id` = p.`ID` WHERE p.`post_status` IN (\'publish\', \'private\') AND p.post_type = "podcast" AND p.ID != %d AND e.slug = %s LIMIT 0, 1', $current_episode->post_id, $current_episode->slug); return $wpdb->get_var($sql); }
public static function remove_duplicate_episodes() { global $wpdb; // find duplicate episodes $sql = "SELECT post_id, COUNT(*) cnt FROM " . Model\Episode::table_name() . " GROUP BY post_id HAVING cnt > 1"; $duplicate_post_ids = $wpdb->get_col($sql, 0); if ($duplicate_post_ids && count($duplicate_post_ids)) { foreach ($duplicate_post_ids as $post_id) { // only keep first created episode entry $sql = $wpdb->prepare("DELETE FROM\n\t\t\t\t\t\t" . Model\Episode::table_name() . "\n\t\t\t\t\tWHERE post_id = %d AND id != (SELECT id FROM (\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\tid\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t" . Model\Episode::table_name() . "\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tpost_id = %d\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\tid ASC\n\t\t\t\t\t\tLIMIT 1\n\t\t\t\t\t) x)", $post_id, $post_id); $wpdb->query($sql); } self::add_to_repair_log(sprintf(__('Removed duplicate episode datasets (%s) You should verify that they are correct.', 'podlove'), implode(', ', array_map(function ($post_id) { $link = \get_edit_post_link($post_id); $title = \get_the_title($post_id); return sprintf('<a href="%s" target="_blank">%s</a>', $link, $title); }, $duplicate_post_ids)))); } }
public static function latest() { global $wpdb; // Why do we fetch 10 instead of just 1? // Because some of the newest ones might be drafts or otherwise invalid. // So we grab a bunch, filter by validity and then return the first one. $sql = ' SELECT * FROM `' . Episode::table_name() . '` e JOIN `' . $wpdb->posts . '` p ON e.post_id = p.ID ORDER BY p.post_date DESC LIMIT 0, 10'; $episodes = array_filter(Episode::find_all_by_sql($sql), function ($e) { return $e->is_valid(); }); return reset($episodes); }
public static function content() { global $wpdb; $sql = "\n\t\tSELECT\n\t\t\tp.post_status,\n\t\t\tmf.episode_id,\n\t\t\tmf.episode_asset_id,\n\t\t\tmf.size,\n\t\t\tmf.id media_file_id\n\t\tFROM\n\t\t\t`" . Model\MediaFile::table_name() . "` mf\n\t\t\tJOIN `" . Model\Episode::table_name() . "` e ON e.id = mf.`episode_id`\n\t\t\tJOIN `" . $wpdb->posts . "` p ON e.`post_id` = p.`ID`\n\t\tWHERE\n\t\t\tp.`post_type` = 'podcast'\n\t\t\tAND p.post_status in ('private', 'draft', 'publish', 'pending', 'future')\n\t\t"; $rows = $wpdb->get_results($sql, ARRAY_A); $media_files = []; foreach ($rows as $row) { if (!isset($media_files[$row['episode_id']])) { $media_files[$row['episode_id']] = ['post_status' => $row["post_status"]]; } $media_files[$row['episode_id']][$row['episode_asset_id']] = ['size' => $row['size'], 'media_file_id' => $row['media_file_id']]; } $podcast = Model\Podcast::get(); $episodes = $podcast->episodes(); $assets = Model\EpisodeAsset::all(); $header = [__('Episode', 'podlove')]; foreach ($assets as $asset) { $header[] = $asset->title; } $header[] = __('Status', 'podlove'); \Podlove\load_template('settings/dashboard/file_validation', ['episodes' => $episodes, 'assets' => $assets, 'media_files' => $media_files, 'header' => $header]); }
/** * Get episodes related to the given episode. * * @param int|bool $episode_id * @param array $args List of optional arguments. * only_published - If true, only return already published episodes. Default: false. * @return array */ public static function get_related_episodes($episode_id = false, $args = []) { global $wpdb; $defaults = ['only_published' => false]; $args = wp_parse_args($args, $defaults); if (!$episode_id) { return []; } $join = ''; if ($args['only_published']) { $join = 'INNER JOIN ' . $wpdb->posts . ' p ON p.ID = e.post_id AND p.post_status IN (\'publish\', \'private\')'; } $sql = sprintf('SELECT e.* FROM ' . Episode::table_name() . ' e ' . $join . ' WHERE e.id IN ( SELECT right_episode_id FROM ' . self::table_name() . ' WHERE left_episode_id = %1$d UNION SELECT left_episode_id FROM ' . self::table_name() . ' WHERE right_episode_id = %1$d )', $episode_id); return Episode::find_all_by_sql($sql); }
} $episodesTable = \Podlove\Model\Episode::table_name(); $search = ''; $searchand = ''; $n = !empty($query->query_vars['exact']) ? '' : '%'; foreach ((array) $query->query_vars['search_terms'] as $term) { $term = esc_sql(\Podlove\esc_like($term)); $search .= "\n\t\t\t{$searchand}\n\t\t\t(\n\t\t\t\t({$wpdb->posts}.post_title LIKE '{$n}{$term}{$n}')\n\t\t\t\tOR\n\t\t\t\t({$wpdb->posts}.post_content LIKE '{$n}{$term}{$n}')\n\t\t\t\tOR\n\t\t\t\t({$episodesTable}.subtitle LIKE '{$n}{$term}{$n}')\n\t\t\t\tOR\n\t\t\t\t({$episodesTable}.summary LIKE '{$n}{$term}{$n}')\n\t\t\t\tOR\n\t\t\t\t({$episodesTable}.chapters LIKE '{$n}{$term}{$n}')\n\t\t\t)"; $searchand = ' AND '; } if (!empty($search)) { $search = " AND ({$search}) "; if (!is_user_logged_in()) { $search .= " AND ({$wpdb->posts}.post_password = '') "; } } return $search; }, 10, 2); // join into episode table in WordPress searches so we can access episode fields add_filter('posts_join', function ($join, $query) { global $wpdb; if ($query->is_feed()) { return $join; } if (!$query->is_search()) { return $join; } $episodesTable = \Podlove\Model\Episode::table_name(); $join .= " LEFT JOIN {$episodesTable} ON {$wpdb->posts}.ID = {$episodesTable}.post_id "; return $join; }, 10, 2);
/** * Calculates episode contributions and stores them in contributioncount attribute * * Note: Counts only one contribution per episode even if one contributor has * multiple contributions in an episode. */ public function calcContributioncount() { global $wpdb; $sql = "\n\t\t\tSELECT COUNT(*) FROM (\n\t\t\t\tSELECT\n\t\t\t\t\tec.contributor_id, ec.episode_id\n\t\t\t\tFROM\n\t\t\t\t\t" . EpisodeContribution::table_name() . " ec\n\t\t\t\t\tJOIN " . Episode::table_name() . " e ON e.id = ec.episode_id\n\t\t\t\t\tJOIN " . $wpdb->posts . " p ON p.ID = e.post_id\n\t\t\t\tWHERE\n\t\t\t\t\tec.contributor_id = %d\n\t\t\t\tGROUP BY\n\t\t\t\t\tec.episode_id\n\t\t\t) x\n\t\t"; $this->contributioncount = $wpdb->get_var($wpdb->prepare($sql, $this->id)); $this->save(); }
/** * Find and run migration for given version number. * * @todo move migrations into separate files * * @param int $version */ function run_migrations_for_version($version) { global $wpdb; switch ($version) { case 2: $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `chapters` TEXT AFTER `cover_art`', \Podlove\Model\Release::table_name()); $wpdb->query($sql); break; case 3: $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `format` VARCHAR(255) AFTER `slug`', \Podlove\Model\Feed::table_name()); $wpdb->query($sql); break; case 4: $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `title` VARCHAR(255) AFTER `id`', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); break; case 5: \Podlove\Modules\Base::activate('podlove_web_player'); break; case 6: // title column is "int" for some people. this migration fixes that $sql = sprintf('SHOW COLUMNS FROM `wp_podlove_medialocation` WHERE Field = "title"', \Podlove\Model\EpisodeAsset::table_name()); $row = $wpdb->get_row($sql); if (strtolower(substr($row->Type, 0, 3)) === 'int') { $wpdb->query(sprintf('UPDATE `%s` SET title = NULL', \Podlove\Model\EpisodeAsset::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` MODIFY COLUMN `title` VARCHAR(255)', \Podlove\Model\EpisodeAsset::table_name())); } break; case 7: // move language from feed to show $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `language` VARCHAR(255) AFTER `summary`', \Podlove\Model\Show::table_name()); $wpdb->query($sql); $sql = sprintf('ALTER TABLE `%s` DROP COLUMN `language`', \Podlove\Model\Feed::table_name()); $wpdb->query($sql); break; case 8: $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `supports_cover_art` INT', \Podlove\Model\Show::table_name()); $wpdb->query($sql); break; case 9: // huge architecture migration // assume first show will be blueprint for the podcast $show = $wpdb->get_row(sprintf('SELECT * FROM %s LIMIT 1', $wpdb->prefix . 'podlove_show'), ARRAY_A); $show_id = $show['id']; // On my local machine the migration runs twice. // This is a quick fix. caveat: someone who has no show defined // will need to uninstall the plugin. That seems acceptable. if (!$show_id) { return; } // all releases of this show will be converted to episodes $releases = $wpdb->get_results(sprintf(' SELECT E.post_id, R.episode_id, R.active, R.enable, R.slug, R.duration, R.cover_art, R.chapters FROM %s R INNER JOIN %s E ON R.episode_id = E.id WHERE R.show_id = "%s" ', $wpdb->prefix . 'podlove_release', $wpdb->prefix . 'podlove_episode', $show_id), ARRAY_A); // write show settings to podcast $podcast = \Podlove\Model\Podcast::get_instance(); foreach ($show as $key => $value) { $podcast->{$key} = $value; } $podcast->save(); // rebuild show table \Podlove\Model\Show::destroy(); \Podlove\Model\Show::build(); // rebuild episodes table \Podlove\Model\Episode::destroy(); \Podlove\Model\Episode::build(); foreach ($releases as $release) { $episode = new \Podlove\Model\Episode(); foreach ($release as $key => $value) { if (!in_array($key, array('episode_id'))) { $episode->{$key} = $value; } } $episode->save(); } // clean feed table $sql = sprintf('DELETE FROM `%s` WHERE `show_id` != "%s"', \Podlove\Model\Feed::table_name(), $show_id); $wpdb->query($sql); $sql = sprintf('ALTER TABLE `%s` DROP COLUMN `show_id`', \Podlove\Model\Feed::table_name()); $wpdb->query($sql); // fix mediafile table $sql = sprintf('ALTER TABLE `%s` CHANGE `release_id` `episode_id` INT', \Podlove\Model\MediaFile::table_name()); $wpdb->query($sql); // remove suffix $sql = sprintf('ALTER TABLE `%s` DROP COLUMN `suffix`', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); // add more default formats $default_formats = array(array('name' => 'PDF Document', 'type' => 'ebook', 'mime_type' => 'application/pdf', 'extension' => 'pdf'), array('name' => 'ePub Document', 'type' => 'ebook', 'mime_type' => 'application/epub+zip', 'extension' => 'epub'), array('name' => 'PNG Image', 'type' => 'image', 'mime_type' => 'image/png', 'extension' => 'png'), array('name' => 'JPEG Image', 'type' => 'image', 'mime_type' => 'image/jpeg', 'extension' => 'jpg')); foreach ($default_formats as $format) { $f = new Model\FileType(); foreach ($format as $key => $value) { $f->{$key} = $value; } $f->save(); } // update assistant $assistant = \Podlove\Modules\EpisodeAssistant\Episode_Assistant::instance(); $template = $assistant->get_module_option('title_template'); $template = str_replace('%show_slug%', '%podcast_slug%', $template); $assistant->update_module_option('title_template', $template); // update media locations $media_locations = \Podlove\Model\EpisodeAsset::all(); foreach ($media_locations as $media_location) { $media_location->url_template = str_replace('%suffix%', '', $media_location->url_template); $media_location->save(); } break; case 10: $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `summary` TEXT', \Podlove\Model\Episode::table_name()); $wpdb->query($sql); break; case 11: $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `downloadable` INT', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); break; case 12: $sql = sprintf('UPDATE `%s` SET `downloadable` = 1', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); break; case 13: $opus = array('name' => 'Opus Audio', 'type' => 'audio', 'mime_type' => 'audio/opus', 'extension' => 'opus'); $f = new \Podlove\Model\FileType(); foreach ($opus as $key => $value) { $f->{$key} = $value; } $f->save(); break; case 14: $sql = sprintf('ALTER TABLE `%s` RENAME TO `%s`', $wpdb->prefix . 'podlove_medialocation', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); break; case 15: $sql = sprintf('ALTER TABLE `%s` CHANGE `media_location_id` `episode_asset_id` INT', \Podlove\Model\MediaFile::table_name()); $wpdb->query($sql); break; case 16: $sql = sprintf('ALTER TABLE `%s` CHANGE `media_location_id` `episode_asset_id` INT', \Podlove\Model\Feed::table_name()); $wpdb->query($sql); break; case 17: $sql = sprintf('ALTER TABLE `%s` RENAME TO `%s`', $wpdb->prefix . 'podlove_mediaformat', \Podlove\Model\FileType::table_name()); $wpdb->query($sql); break; case 18: $sql = sprintf('ALTER TABLE `%s` CHANGE `media_format_id` `file_type_id` INT', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); break; } }
public function episodes() { global $wpdb; $prev = $this->previous_season(); $next = $this->next_season(); if (is_null($prev) && is_null($next)) { // first and only season $date_range = '1 = 1'; } elseif (is_null($prev)) { // first, completed season $date_range = "DATE(p.post_date) < '" . $next->start_date('Y-m-d') . "'"; } elseif (is_null($next)) { // current running season $date_range = "DATE(p.post_date) > '" . $prev->end_date('Y-m-d') . "'"; } else { // anything inbetween $date_range = "DATE(p.post_date) >= '" . $this->start_date('Y-m-d') . "' AND DATE(p.post_date) <= '" . $this->end_date('Y-m-d') . "'"; } $sql = "SELECT\n\t\t\t\te.*\n\t\t\tFROM\n\t\t\t\t`" . Episode::table_name() . "` e\n\t\t\t\tJOIN `" . $wpdb->posts . "` p ON e.post_id = p.ID\n\t\t\tWHERE\n\t\t\t\tp.post_type = 'podcast' AND\n\t\t\t\tp.post_status = 'publish' AND\n\t\t\t\t{$date_range}\n\t\t\tORDER BY\n\t\t\t\tp.post_date ASC\n\t\t"; return Episode::find_all_by_sql($sql); }
/** * Find one downloadable example file. * * - JOIN episode to avoid dead media files * - ORDER BY e.id DESC, mf.id ASC: get a recent episode and the first asset, which is probably an audio file */ public static function find_example() { return self::find_one_by_sql("\n\t\t\tSELECT\n\t\t\t\tmf.*\n\t\t\tFROM\n\t\t\t\t" . MediaFile::table_name() . " mf\n\t\t\t\tJOIN " . EpisodeAsset::table_name() . " a ON a.id = mf.`episode_asset_id`\n\t\t\t\tJOIN " . Episode::table_name() . " e ON e.id = mf.`episode_id`\n\t\t\tWHERE\n\t\t\t\ta.`downloadable` \n\t\t\t\tAND mf.size > 0\n\t\t\tORDER BY e.id DESC, mf.id ASC\n\t\t\tLIMIT 0,1\n\t\t"); }
/** * Find and run migration for given version number. * * @todo move migrations into separate files * * @param int $version */ function run_migrations_for_version($version) { global $wpdb; switch ($version) { case 10: $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `summary` TEXT', \Podlove\Model\Episode::table_name()); $wpdb->query($sql); break; case 11: $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `downloadable` INT', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); break; case 12: $sql = sprintf('UPDATE `%s` SET `downloadable` = 1', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); break; case 13: $opus = array('name' => 'Opus Audio', 'type' => 'audio', 'mime_type' => 'audio/opus', 'extension' => 'opus'); $f = new \Podlove\Model\FileType(); foreach ($opus as $key => $value) { $f->{$key} = $value; } $f->save(); break; case 14: $sql = sprintf('ALTER TABLE `%s` RENAME TO `%s`', $wpdb->prefix . 'podlove_medialocation', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); break; case 15: $sql = sprintf('ALTER TABLE `%s` CHANGE `media_location_id` `episode_asset_id` INT', \Podlove\Model\MediaFile::table_name()); $wpdb->query($sql); break; case 16: $sql = sprintf('ALTER TABLE `%s` CHANGE `media_location_id` `episode_asset_id` INT', \Podlove\Model\Feed::table_name()); $wpdb->query($sql); break; case 17: $sql = sprintf('ALTER TABLE `%s` RENAME TO `%s`', $wpdb->prefix . 'podlove_mediaformat', \Podlove\Model\FileType::table_name()); $wpdb->query($sql); break; case 18: $sql = sprintf('ALTER TABLE `%s` CHANGE `media_format_id` `file_type_id` INT', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); break; case 19: \Podlove\Model\Template::build(); break; case 20: $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `suffix` VARCHAR(255)', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); $sql = sprintf('ALTER TABLE `%s` DROP COLUMN `url_template`', \Podlove\Model\EpisodeAsset::table_name()); $wpdb->query($sql); break; case 21: $podcast = Model\Podcast::get(); $podcast->url_template = '%media_file_base_url%%episode_slug%%suffix%.%format_extension%'; $podcast->save(); break; case 22: $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `redirect_http_status` INT AFTER `redirect_url`', Model\Feed::table_name()); $wpdb->query($sql); break; case 23: $sql = sprintf('ALTER TABLE `%s` DROP COLUMN `show_description`', Model\Feed::table_name()); $wpdb->query($sql); break; case 24: $podcast = Model\Podcast::get(); update_option('podlove_asset_assignment', array('image' => $podcast->supports_cover_art, 'chapters' => $podcast->chapter_file)); break; case 25: // rename meta podlove_guid to _podlove_guid $episodes = Model\Episode::all(); foreach ($episodes as $episode) { $post = get_post($episode->post_id); // skip revisions if ($post->post_status == 'inherit') { continue; } $guid = get_post_meta($episode->post_id, 'podlove_guid', true); if (!$guid) { $guid = $post->guid; } delete_post_meta($episode->post_id, 'podlove_guid'); update_post_meta($episode->post_id, '_podlove_guid', $guid); } break; case 26: $wpdb->query(sprintf('ALTER TABLE `%s` MODIFY COLUMN `subtitle` TEXT', Model\Episode::table_name())); break; case 27: $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `record_date` DATETIME AFTER `chapters`', Model\Episode::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `publication_date` DATETIME AFTER `record_date`', Model\Episode::table_name())); break; case 28: $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `position` FLOAT AFTER `downloadable`', Model\EpisodeAsset::table_name())); $wpdb->query(sprintf('UPDATE `%s` SET position = id', Model\EpisodeAsset::table_name())); break; case 29: $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `embed_content_encoded` INT AFTER `limit_items`', Model\Feed::table_name())); break; case 30: $wpdb->query(sprintf('ALTER TABLE `%s` MODIFY `autoinsert` VARCHAR(255)', Model\Template::table_name())); break; case 32: flush_rewrite_rules(); break; case 33: $apd = array('name' => 'Auphonic Production Description', 'type' => 'metadata', 'mime_type' => 'application/json', 'extension' => 'json'); $f = new \Podlove\Model\FileType(); foreach ($apd as $key => $value) { $f->{$key} = $value; } $f->save(); break; case 34: $options = get_option('podlove', array()); if (!array_key_exists('episode_archive', $options)) { $options['episode_archive'] = 'on'; } if (!array_key_exists('episode_archive_slug', $options)) { $options['episode_archive_slug'] = '/podcast/'; } if (!array_key_exists('use_post_permastruct', $options)) { $options['use_post_permastruct'] = 'off'; } if (!array_key_exists('custom_episode_slug', $options)) { $options['custom_episode_slug'] = '/podcast/%podcast%/'; } else { $options['custom_episode_slug'] = preg_replace('#/+#', '/', '/' . str_replace('#', '', $options['custom_episode_slug'])); } update_option('podlove', $options); break; case 35: Model\Feed::build_indices(); Model\FileType::build_indices(); Model\EpisodeAsset::build_indices(); Model\MediaFile::build_indices(); Model\Episode::build_indices(); Model\Template::build_indices(); break; case 36: $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `etag` VARCHAR(255)', Model\MediaFile::table_name())); break; case 37: \Podlove\Modules\Base::activate('asset_validation'); break; case 38: \Podlove\Modules\Base::activate('logging'); break; case 39: // migrate previous template autoinsert settings $assignments = Model\TemplateAssignment::get_instance(); $results = $wpdb->get_results(sprintf('SELECT * FROM `%s`', Model\Template::table_name())); foreach ($results as $template) { if ($template->autoinsert == 'beginning') { $assignments->top = $template->id; } elseif ($template->autoinsert == 'end') { $assignments->bottom = $template->id; } } $assignments->save(); // remove template autoinsert column $sql = sprintf('ALTER TABLE `%s` DROP COLUMN `autoinsert`', \Podlove\Model\Template::table_name()); $wpdb->query($sql); break; case 40: $wpdb->query(sprintf('UPDATE `%s` SET position = id WHERE position IS NULL', Model\EpisodeAsset::table_name())); break; case 41: $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `position` FLOAT AFTER `slug`', Model\Feed::table_name())); $wpdb->query(sprintf('UPDATE `%s` SET position = id', Model\Feed::table_name())); break; case 42: $wpdb->query('DELETE FROM `' . $wpdb->options . '` WHERE option_name LIKE "%podlove_chapters_string_%"'); break; case 43: $podlove_options = get_option('podlove', array()); $podlove_website = array('merge_episodes' => isset($podlove_options['merge_episodes']) ? $podlove_options['merge_episodes'] : false, 'hide_wp_feed_discovery' => isset($podlove_options['hide_wp_feed_discovery']) ? $podlove_options['hide_wp_feed_discovery'] : false, 'use_post_permastruct' => isset($podlove_options['use_post_permastruct']) ? $podlove_options['use_post_permastruct'] : false, 'custom_episode_slug' => isset($podlove_options['custom_episode_slug']) ? $podlove_options['custom_episode_slug'] : '/episode/%podcast%', 'episode_archive' => isset($podlove_options['episode_archive']) ? $podlove_options['episode_archive'] : false, 'episode_archive_slug' => isset($podlove_options['episode_archive_slug']) ? $podlove_options['episode_archive_slug'] : '/podcast/', 'url_template' => isset($podlove_options['url_template']) ? $podlove_options['url_template'] : '%media_file_base_url%%episode_slug%%suffix%.%format_extension%'); $podlove_metadata = array('enable_episode_record_date' => isset($podlove_options['enable_episode_record_date']) ? $podlove_options['enable_episode_record_date'] : false, 'enable_episode_publication_date' => isset($podlove_options['enable_episode_publication_date']) ? $podlove_options['enable_episode_publication_date'] : false); $podlove_redirects = array('podlove_setting_redirect' => isset($podlove_options['podlove_setting_redirect']) ? $podlove_options['podlove_setting_redirect'] : array()); add_option('podlove_website', $podlove_website); add_option('podlove_metadata', $podlove_metadata); add_option('podlove_redirects', $podlove_redirects); break; case 44: $wpdb->query('DELETE FROM `' . $wpdb->postmeta . '` WHERE meta_key = "last_validated_at"'); break; case 45: delete_transient('podlove_auphonic_user'); delete_transient('podlove_auphonic_presets'); break; case 46: if (\Podlove\Modules\Base::is_active('contributors')) { // manually trigger activation if the old module was active $module = \Podlove\Modules\Contributors\Contributors::instance(); $module->was_activated('contributors'); // then, migrate existing contributors // register old taxonomy so it can be queried $args = array('hierarchical' => false, 'labels' => array(), 'show_ui' => true, 'show_tagcloud' => true, 'query_var' => true, 'rewrite' => array('slug' => 'contributor')); register_taxonomy('podlove-contributors', 'podcast', $args); $contributor_settings = get_option('podlove_contributors', array()); $contributors = get_terms('podlove-contributors', array('hide_empty' => 0)); if ($contributors && !is_wp_error($contributors) && \Podlove\Modules\Contributors\Model\Contributor::count() == 0) { foreach ($contributors as $contributor) { // create new contributor $new = new \Podlove\Modules\Contributors\Model\Contributor(); $new->publicname = $contributor->name; $new->realname = $contributor->name; $new->slug = $contributor->slug; $new->showpublic = true; if (isset($contributor_settings[$contributor->term_id]['contributor_email'])) { $email = $contributor_settings[$contributor->term_id]['contributor_email']; if ($email) { $new->privateemail = $email; $new->avatar = $email; } } $new->save(); // create contributions $query = new \WP_Query(array('posts_per_page' => -1, 'post_type' => 'podcast', 'tax_query' => array(array('taxonomy' => 'podlove-contributors', 'field' => 'slug', 'terms' => $contributor->slug)))); while ($query->have_posts()) { $post = $query->next_post(); $contribution = new \Podlove\Modules\Contributors\Model\EpisodeContribution(); $contribution->contributor_id = $new->id; $contribution->episode_id = Model\Episode::find_one_by_post_id($post->ID)->id; $contribution->save(); } } } } break; case 47: $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `protected` TINYINT(1) NULL', \Podlove\Model\Feed::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `protection_type` TINYINT(1)', \Podlove\Model\Feed::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `protection_user` VARCHAR(60)', \Podlove\Model\Feed::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `protection_password` VARCHAR(64)', \Podlove\Model\Feed::table_name())); break; case 48: $podcast = Model\Podcast::get(); $podcast->limit_items = '-1'; $podcast->save(); break; case 49: $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `explicit` TINYINT', Model\Episode::table_name())); break; case 50: $podcast = Model\Podcast::get(); $podcast->license_type = 'other'; $podcast->save(); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `license_type` VARCHAR(255) AFTER `publication_date`', Model\Episode::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `license_name` TEXT AFTER `license_type`', Model\Episode::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `license_url` TEXT AFTER `license_name`', Model\Episode::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `license_cc_allow_modifications` TEXT AFTER `license_url`', Model\Episode::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `license_cc_allow_commercial_use` TEXT AFTER `license_cc_allow_modifications`', Model\Episode::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `license_cc_license_jurisdiction` TEXT AFTER `license_cc_allow_commercial_use`', Model\Episode::table_name())); break; case 51: if (\Podlove\Modules\Base::is_active('contributors')) { \Podlove\Modules\Contributors\Model\ContributorGroup::build(); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `group_id` VARCHAR(255) AFTER `role_id`', \Podlove\Modules\Contributors\Model\EpisodeContribution::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `group_id` VARCHAR(255) AFTER `role_id`', \Podlove\Modules\Contributors\Model\ShowContribution::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `paypal` VARCHAR(255) AFTER `flattr`', \Podlove\Modules\Contributors\Model\Contributor::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `bitcoin` VARCHAR(255) AFTER `paypal`', \Podlove\Modules\Contributors\Model\Contributor::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `litecoin` VARCHAR(255) AFTER `bitcoin`', \Podlove\Modules\Contributors\Model\Contributor::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` DROP COLUMN `permanentcontributor`', \Podlove\Modules\Contributors\Model\Contributor::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` DROP COLUMN `role`', \Podlove\Modules\Contributors\Model\Contributor::table_name())); } break; case 52: if (\Podlove\Modules\Base::is_active('contributors')) { $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `jobtitle` VARCHAR(255) AFTER `department`', \Podlove\Modules\Contributors\Model\Contributor::table_name())); } break; case 53: // set all Episode as published (fix for ADN Module) $episodes = Model\Episode::all(); foreach ($episodes as $episode) { $post = get_post($episode->post_id); if ($post->post_status == 'publish') { update_post_meta($episode->post_id, '_podlove_episode_was_published', true); } } break; case 54: if (\Podlove\Modules\Base::is_active('contributors')) { $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `googleplus` TEXT AFTER `ADN`', \Podlove\Modules\Contributors\Model\Contributor::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` CHANGE COLUMN `showpublic` `visibility` TINYINT(1)', \Podlove\Modules\Contributors\Model\Contributor::table_name())); } break; case 55: if (\Podlove\Modules\Base::is_active('contributors')) { \Podlove\Modules\Contributors\Model\DefaultContribution::build(); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `comment` TEXT AFTER `position`', \Podlove\Modules\Contributors\Model\EpisodeContribution::table_name())); $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `comment` TEXT AFTER `position`', \Podlove\Modules\Contributors\Model\ShowContribution::table_name())); } break; case 56: // migrate Podcast Contributors to Default Contributors if (\Podlove\Modules\Base::is_active('contributors')) { $podcast_contributors = \Podlove\Modules\Contributors\Model\ShowContribution::all(); foreach ($podcast_contributors as $podcast_contributor_key => $podcast_contributor) { $new = new \Podlove\Modules\Contributors\Model\DefaultContribution(); $new->contributor_id = $podcast_contributor->contributor_id; $new->group_id = $podcast_contributor->group_id; $new->role_id = $podcast_contributor->role_id; $new->position = $podcast_contributor->positon; $new->save(); } } break; case 57: $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `append_name_to_podcast_title` TINYINT(1) NULL AFTER `embed_content_encoded`', \Podlove\Model\Feed::table_name())); break; case 58: // if contributors module is active, activate social module if (\Podlove\Modules\Base::is_active('contributors')) { \Podlove\Modules\Base::activate('social'); } break; case 59: if (\Podlove\Modules\Base::is_active('bitlove')) { $wpdb->query(sprintf("ALTER TABLE `%s` ADD COLUMN `bitlove` TINYINT(1) DEFAULT '0'", \Podlove\Model\Feed::table_name())); } break; case 60: \Podlove\Modules\Base::activate('oembed'); \Podlove\Modules\Base::activate('feed_validation'); break; case 61: $wpdb->query(sprintf('ALTER TABLE `%s` DROP COLUMN `publication_date`', Model\Episode::table_name())); break; case 62: // rename column $wpdb->query(sprintf('ALTER TABLE `%s` CHANGE COLUMN `record_date` `recording_date` DATETIME', Model\Episode::table_name())); // update settings $meta = get_option('podlove_metadata'); if (isset($meta['enable_episode_publication_date'])) { unset($meta['enable_episode_publication_date']); } if (isset($meta['enable_episode_record_date'])) { $meta['enable_episode_recording_date'] = $meta['enable_episode_record_date']; unset($meta['enable_episode_record_date']); } update_option('podlove_metadata', $meta); break; case 63: if (\Podlove\Modules\Base::is_active('social')) { $tumblr_service = \Podlove\Modules\Social\Model\Service::find_one_by_property('title', 'Tumblr'); $tumblr_service->url_scheme = 'http://%account-placeholder%.tumblr.com/'; $tumblr_service->save(); } break; case 64: if (\Podlove\Modules\Base::is_active('social')) { $services = array(array('title' => '500px', 'type' => 'social', 'description' => '500px Account', 'logo' => '500px-128.png', 'url_scheme' => 'https://500px.com/%account-placeholder%'), array('title' => 'Last.fm', 'type' => 'social', 'description' => 'Last.fm Account', 'logo' => 'lastfm-128.png', 'url_scheme' => 'https://www.lastfm.de/user/%account-placeholder%'), array('title' => 'OpenStreetMap', 'type' => 'social', 'description' => 'OpenStreetMap Account', 'logo' => 'openstreetmap-128.png', 'url_scheme' => 'https://www.openstreetmap.org/user/%account-placeholder%'), array('title' => 'Soup', 'type' => 'social', 'description' => 'Soup Account', 'logo' => 'soup-128.png', 'url_scheme' => 'http://%account-placeholder%.soup.io')); foreach ($services as $service_key => $service) { $c = new \Podlove\Modules\Social\Model\Service(); $c->title = $service['title']; $c->type = $service['type']; $c->description = $service['description']; $c->logo = $service['logo']; $c->url_scheme = $service['url_scheme']; $c->save(); } } break; case 65: if (\Podlove\Modules\Base::is_active('social')) { $flattr_service = \Podlove\Modules\Social\Model\Service::find_one_by_where("`title` = 'Flattr' AND `type` = 'donation'"); if ($flattr_service) { $contributor_flattr_donations_accounts = \Podlove\Modules\Social\Model\ContributorService::find_all_by_property('service_id', $flattr_service->id); foreach ($contributor_flattr_donations_accounts as $contributor_flattr_donations_account) { $contributor = \Podlove\Modules\Contributors\Model\Contributor::find_by_id($contributor_flattr_donations_account->contributor_id); if ($contributor && is_null($contributor->flattr)) { $contributor->flattr = $contributor_flattr_donations_account->value; $contributor->save(); } $contributor_flattr_donations_account->delete(); } $flattr_service->delete(); } } break; case 66: // Temporary add license_type and CC license fields to episode model \Podlove\Model\Episode::property('license_type', 'VARCHAR(255)'); \Podlove\Model\Episode::property('license_cc_allow_modifications', 'VARCHAR(255)'); \Podlove\Model\Episode::property('license_cc_allow_commercial_use', 'VARCHAR(255)'); \Podlove\Model\Episode::property('license_cc_license_jurisdiction', 'VARCHAR(255)'); $podcast = \Podlove\Model\Podcast::get(); $episodes = \Podlove\Model\Episode::all(); // Migration for Podcast if ($podcast->license_type == 'cc' && $podcast->license_cc_allow_commercial_use !== '' && $podcast->license_cc_allow_modifications !== '' && $podcast->license_cc_license_jurisdiction !== '') { $license = array('version' => '3.0', 'commercial_use' => $podcast->license_cc_allow_commercial_use, 'modification' => $podcast->license_cc_allow_modifications, 'jurisdiction' => $podcast->license_cc_license_jurisdiction); $podcast->license_url = \Podlove\Model\License::get_url_from_license($license); $podcast->license_name = \Podlove\Model\License::get_name_from_license($license); $podcast->save(); } // Migration for Episodes foreach ($episodes as $episode) { if ($episode->license_type == 'other' || $episode->license_cc_allow_commercial_use == '' || $episode->license_cc_allow_modifications == '' || $episode->license_cc_license_jurisdiction == '') { continue; } $license = array('version' => '3.0', 'commercial_use' => $episode->license_cc_allow_commercial_use, 'modification' => $episode->license_cc_allow_modifications, 'jurisdiction' => $episode->license_cc_license_jurisdiction); $episode->license_url = \Podlove\Model\License::get_url_from_license($license); $episode->license_name = \Podlove\Model\License::get_name_from_license($license); $episode->save(); } break; case 67: if (\Podlove\Modules\Base::is_active('social')) { $instagram_service = \Podlove\Modules\Social\Model\Service::find_one_by_where("`title` = 'Instagram' AND `type` = 'social'"); if ($instagram_service) { $instagram_service->url_scheme = 'https://instagram.com/%account-placeholder%'; $instagram_service->save(); } } break; case 68: // Do that ADN module fix again, as we forgot to mark all episodes as published if the ADN module is activated $episodes = Model\Episode::all(); foreach ($episodes as $episode) { $post = get_post($episode->post_id); if ($post->post_status == 'publish' && !get_post_meta($episode->post_id, '_podlove_episode_was_published', true)) { update_post_meta($episode->post_id, '_podlove_episode_was_published', true); } } break; case 69: if (\Podlove\Modules\Base::is_active('app_dot_net')) { $adn = \Podlove\Modules\AppDotNet\App_Dot_Net::instance(); if ($adn->get_module_option('adn_auth_key')) { $adn->update_module_option('adn_automatic_announcement', 'on'); } } break; case 70: \Podlove\Model\DownloadIntent::build(); \Podlove\Model\UserAgent::build(); break; case 71: // update for everyone, so even those with inactive service tables get updated $wpdb->query(sprintf('ALTER TABLE `%s` CHANGE COLUMN `type` `category` VARCHAR(255)', \Podlove\Modules\Social\Model\Service::table_name())); $wpdb->query(sprintf("ALTER TABLE `%s` ADD COLUMN `type` VARCHAR(255) AFTER `category`", \Podlove\Modules\Social\Model\Service::table_name())); $services = \Podlove\Modules\Social\Model\Service::all(); foreach ($services as $service) { $service->type = strtolower($service->title); $service->save(); } break; case 72: if (\Podlove\Modules\Base::is_active('social')) { $services = array(array('title' => 'Vimeo', 'type' => 'vimeo', 'category' => 'social', 'description' => 'Vimeo Account', 'logo' => 'vimeo-128.png', 'url_scheme' => 'http://vimeo.com/%account-placeholder%'), array('title' => 'about.me', 'type' => 'about.me', 'category' => 'social', 'description' => 'about.me Account', 'logo' => 'aboutme-128.png', 'url_scheme' => 'http://about.me/%account-placeholder%'), array('title' => 'Gittip', 'type' => 'gittip', 'category' => 'donation', 'description' => 'Gittip Account', 'logo' => 'gittip-128.png', 'url_scheme' => 'https://www.gittip.com/%account-placeholder%')); foreach ($services as $service_key => $service) { $c = new \Podlove\Modules\Social\Model\Service(); $c->title = $service['title']; $c->type = $service['type']; $c->category = $service['category']; $c->description = $service['description']; $c->logo = $service['logo']; $c->url_scheme = $service['url_scheme']; $c->save(); } } break; case 73: if (\Podlove\Modules\Base::is_active('social')) { $jabber_service = \Podlove\Modules\Social\Model\Service::find_one_by_where("`type` = 'jabber' AND `category` = 'social'"); if ($jabber_service) { $jabber_service->url_scheme = 'jabber:%account-placeholder%'; $jabber_service->save(); } } break; case 74: Model\GeoArea::build(); Model\GeoAreaName::build(); \Podlove\Geo_Ip::register_updater_cron(); break; case 75: $tracking = get_option('podlove_tracking'); $tracking['mode'] = 0; update_option('podlove_tracking', $tracking); break; case 76: set_transient('podlove_needs_to_flush_rewrite_rules', true); break; case 77: // delete empty user agents $userAgentTable = Model\UserAgent::table_name(); $downloadIntentTable = Model\DownloadIntent::table_name(); $sql = "SELECT\n\t\t\t\tdi.id\n\t\t\tFROM\n\t\t\t\t{$downloadIntentTable} di\n\t\t\t\tJOIN {$userAgentTable} ua ON ua.id = di.user_agent_id\n\t\t\tWHERE\n\t\t\t\tua.user_agent IS NULL"; $ids = $wpdb->get_col($sql); if (is_array($ids) && count($ids)) { $sql = "UPDATE {$downloadIntentTable} SET user_agent_id = NULL WHERE id IN (" . implode(",", $ids) . ")"; $wpdb->query($sql); $sql = "DELETE FROM {$userAgentTable} WHERE user_agent IS NULL"; $wpdb->query($sql); } break; case 78: if (\Podlove\Modules\Base::is_active('social')) { $c = new \Podlove\Modules\Social\Model\Service(); $c->title = 'Auphonic Credits'; $c->category = 'donation'; $c->type = 'auphonic credits'; $c->description = 'Auphonic Account'; $c->logo = 'auphonic-128.png'; $c->url_scheme = 'https://auphonic.com/donate_credits?user=%account-placeholder%'; $c->save(); } break; case 79: set_transient('podlove_needs_to_flush_rewrite_rules', true); $cache = \Podlove\Cache\TemplateCache::get_instance(); $cache->setup_purge(); break; case 80: $sql = sprintf('ALTER TABLE `%s` ADD COLUMN `httprange` VARCHAR(255)', \Podlove\Model\DownloadIntent::table_name()); $wpdb->query($sql); break; case 81: // remove all caches with old namespace $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE \"_transient_podlove_cache%\""); break; case 82: // set all redirect entries to active $redirect_settings = \Podlove\get_setting('redirects', 'podlove_setting_redirect'); foreach ($redirect_settings as $index => $data) { $redirect_settings[$index]['active'] = 'active'; } update_option('podlove_redirects', array('podlove_setting_redirect' => $redirect_settings)); break; case 83: \Podlove\Model\DownloadIntentClean::build(); $alterations = array('ALTER TABLE `%s` ADD COLUMN `bot` TINYINT', 'ALTER TABLE `%s` ADD COLUMN `client_name` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `client_version` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `client_type` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `os_name` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `os_version` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `device_brand` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `device_model` VARCHAR(255)'); foreach ($alterations as $sql) { $wpdb->query(sprintf($sql, Model\UserAgent::table_name())); } Model\UserAgent::reparse_all(); break; case 84: delete_option('podlove_tpl_cache_keys'); break; case 85: add_option('podlove_tracking_delete_head_requests', 1); break; case 86: if (\Podlove\Modules\Base::is_active('social')) { $c = new \Podlove\Modules\Social\Model\Service(); $c->title = 'Foursquare'; $c->category = 'social'; $c->type = 'foursquare'; $c->description = 'Foursquare Account'; $c->logo = 'foursquare-128.png'; $c->url_scheme = 'https://foursquare.com/%account-placeholder%'; $c->save(); $services = array(array('title' => 'ResearchGate', 'name' => 'researchgate', 'category' => 'social', 'description' => 'ResearchGate URL', 'logo' => 'researchgate-128.png', 'url_scheme' => '%account-placeholder%'), array('title' => 'ORCiD', 'name' => 'orcid', 'category' => 'social', 'description' => 'ORCiD', 'logo' => 'orcid-128.png', 'url_scheme' => 'https://orcid.org/%account-placeholder%'), array('title' => 'Scopus', 'name' => 'scous', 'category' => 'social', 'description' => 'Scopus Author ID', 'logo' => 'scopus-128.png', 'url_scheme' => 'https://www.scopus.com/authid/detail.url?authorId=%account-placeholder%')); foreach ($services as $service_key => $service) { $c = new \Podlove\Modules\Social\Model\Service(); $c->title = $service['title']; $c->category = $service['category']; $c->type = $service['name']; $c->description = $service['description']; $c->logo = $service['logo']; $c->url_scheme = $service['url_scheme']; $c->save(); } } break; case 87: if (\Podlove\Modules\Base::is_active('app_dot_net')) { $adn = \Podlove\Modules\AppDotNet\App_Dot_Net::instance(); if ($adn->get_module_option('adn_auth_key')) { $adn->update_module_option('adn_poster_image_fallback', 'on'); } } break; case 88: $service = new \Podlove\Modules\Social\Model\Service(); $service->title = 'Email'; $service->category = 'social'; $service->type = 'email'; $service->description = 'Email'; $service->logo = 'email-128.png'; $service->url_scheme = 'mailto:%account-placeholder%'; $service->save(); break; case 89: $email_service = \Podlove\Modules\Social\Model\Service::find_one_by_type('email'); foreach (\Podlove\Modules\Contributors\Model\Contributor::all() as $contributor) { if (!$contributor->publicemail) { continue; } $contributor_service = new \Podlove\Modules\Social\Model\ContributorService(); $contributor_service->contributor_id = $contributor->id; $contributor_service->service_id = $email_service->id; $contributor_service->value = $contributor->publicemail; $contributor_service->save(); } break; case 90: \Podlove\Modules\Base::activate('subscribe_button'); break; case 91: $c = new \Podlove\Modules\Social\Model\Service(); $c->title = 'Miiverse'; $c->category = 'social'; $c->type = 'miiverse'; $c->description = 'Miiverse Account'; $c->logo = 'miiverse-128.png'; $c->url_scheme = 'https://miiverse.nintendo.net/users/%account-placeholder%'; $c->save(); break; case 92: $c = new \Podlove\Modules\Social\Model\Service(); $c->title = 'Prezi'; $c->category = 'social'; $c->type = 'prezi'; $c->description = 'Prezis'; $c->logo = 'prezi-128.png'; $c->url_scheme = 'http://prezi.com/user/%account-placeholder%'; $c->save(); break; case 93: // podlove_init_user_agent_refresh(); // do nothing instead, because see 94 below break; case 94: // this is a duplicate of migration 83 but it looks like that didn't work. Model\DownloadIntentClean::build(); $alterations = array('ALTER TABLE `%s` ADD COLUMN `bot` TINYINT', 'ALTER TABLE `%s` ADD COLUMN `client_name` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `client_version` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `client_type` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `os_name` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `os_version` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `device_brand` VARCHAR(255)', 'ALTER TABLE `%s` ADD COLUMN `device_model` VARCHAR(255)'); foreach ($alterations as $sql) { $wpdb->query(sprintf($sql, Model\UserAgent::table_name())); } podlove_init_user_agent_refresh(); // manually trigger intent cron after user agents are parsed // parameter to make sure WP does not skip it due to 10 minute rule wp_schedule_single_event(time() + 120, 'podlove_cleanup_download_intents', ['really' => true]); // manually trigger average cron after intents are calculated wp_schedule_single_event(time() + 240, 'recalculate_episode_download_average', ['really' => true]); break; case 95: // add missing flattr column $wpdb->query(sprintf('ALTER TABLE `%s` ADD COLUMN `flattr` VARCHAR(255) AFTER `avatar`', \Podlove\Modules\Contributors\Model\Contributor::table_name())); break; case 96: \Podlove\DeleteHeadRequests::init(); break; case 97: // recalculate all downloads average data $wpdb->query(sprintf('DELETE FROM `%s` WHERE `meta_key` LIKE "_podlove_eda%%"', $wpdb->postmeta)); break; case 98: delete_transient('podlove_dashboard_stats_contributors'); break; case 99: // Activate network module for migrating users. // Core modules are automatically activated for _new_ setups and // whenever modules change. Since this can't be guaranteed for // existing setups, it must be triggered manually. \Podlove\Modules\Networks\Networks::instance()->was_activated(); break; case 101: // add patreon if (\Podlove\Modules\Social\Model\Service::table_exists()) { \Podlove\Modules\Social\RepairSocial::fix_missing_services(); } break; case 102: // update logos if (\Podlove\Modules\Social\Model\Service::table_exists()) { \Podlove\Modules\Social\Social::update_existing_services(); } break; case 103: $assignment = get_option('podlove_template_assignment', []); if ($assignment['top'] && is_numeric($assignment['top'])) { $assignment['top'] = Model\Template::find_by_id($assignment['top'])->title; } if ($assignment['bottom'] && is_numeric($assignment['bottom'])) { $assignment['bottom'] = Model\Template::find_by_id($assignment['bottom'])->title; } update_option('podlove_template_assignment', $assignment); break; case 104: \Podlove\unschedule_events(\Podlove\Cache\TemplateCache::CRON_PURGE_HOOK); break; case 105: // activate flattr plugin \Podlove\Modules\Base::activate('flattr'); // migrate flattr data $podcast = Model\Podcast::get(); $settings = get_option('podlove_flattr', []); $settings['account'] = $podcast->flattr; $settings['contributor_shortcode_default'] = 'yes'; update_option('podlove_flattr', $settings); break; case 106: podlove_init_user_agent_refresh(); break; case 107: // skipped break; case 108: podlove_init_user_agent_refresh(); break; case 109: \podlove_init_capabilities(); break; case 110: if (\Podlove\Modules\Social\Model\Service::table_exists()) { \Podlove\Modules\Social\Social::update_existing_services(); \Podlove\Modules\Social\Social::build_missing_services(); } break; case 111: if (\Podlove\Modules\Social\Model\Service::table_exists()) { \Podlove\Modules\Social\Social::update_existing_services(); \Podlove\Modules\Social\Social::build_missing_services(); } break; } }
public static function cleanup_download_intents() { global $wpdb; $sql = "INSERT INTO `" . Model\DownloadIntentClean::table_name() . "` (`id`, `user_agent_id`, `media_file_id`, `request_id`, `accessed_at`, `source`, `context`, `geo_area_id`, `lat`, `lng`, `httprange`, `hours_since_release`)\n\t\tSELECT\n\t\t\tdi.id, `user_agent_id`, `media_file_id`, `request_id`, `accessed_at`, `source`, `context`, `geo_area_id`, `lat`, `lng`, `httprange`,\n\t\t\tTIMESTAMPDIFF(HOUR, p.post_date_gmt, accessed_at)\n\t\tFROM\n\t\t\t`" . Model\DownloadIntent::table_name() . "` di\n\t\t\tINNER JOIN " . Model\MediaFile::table_name() . " mf ON mf.id = di.media_file_id -- filter dead intents\n\t\t\tINNER JOIN " . Model\Episode::table_name() . " e ON episode_id = e.id\n\t\t\tINNER JOIN {$wpdb->posts} p ON e.post_id = p.ID\n\t\tWHERE\n\t\t\tdi.accessed_at > p.post_date_gmt -- ignore pre-release intents\n\t\t\tAND user_agent_id NOT IN (SELECT id FROM `" . Model\UserAgent::table_name() . "` WHERE bot) -- filter out bots\n\t\t\tAND di.id > %d\n\t\tGROUP BY media_file_id, request_id, DATE_FORMAT(accessed_at, '%%Y-%%m-%%d %%H') -- deduplication\n\t\t"; $wpdb->query($wpdb->prepare($sql, self::get_last_insert_id())); }
public function __construct() { global $wpdb; $sql = "\n\t\tSELECT\n\t\t\tec.id, ec.role_id, ec.group_id, \n\t\t\t(case when c.gender = 'male' then 1 else 0 end) AS male,\n\t\t\t(case when c.gender = 'female' then 1 else 0 end) AS female,\n\t\t\t(case when c.gender NOT IN ('male', 'female') then 1 else 0 end) AS notattributed\n\t\tFROM\n\t\t\t`" . EpisodeContribution::table_name() . "` ec\n\t\t\tJOIN `" . Episode::table_name() . "` e ON e.id = ec.episode_id\n\t\t\tJOIN `" . $wpdb->posts . "` p ON p.ID = e.`post_id` AND p.post_status IN ('publish', 'private')\n\t\t\tJOIN `" . Contributor::table_name() . "` c ON ec.`contributor_id` = c.id\n\t\t"; $this->contributions = $wpdb->get_results($sql); }