IP : 18.118.151.211Hostname : host45.registrar-servers.comKernel : Linux host45.registrar-servers.com 4.18.0-513.18.1.lve.2.el8.x86_64 #1 SMP Sat Mar 30 15:36:11 UTC 2024 x86_64Disable Function : None :) OS : Linux
PATH:
/
home/
./
../
var/
softaculous/
moodle43/
../
aef/
../
geeklog/
../
./
castopod/
castopod.sql/
/
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */;
-- -- Table structure for table `[[dbprefix]]persons` --
CREATE TABLE `[[dbprefix]]persons` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `full_name` varchar(192) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'This is the full name or alias of the person.', `unique_name` varchar(192) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'This is the slug name or alias of the person.', `information_url` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The url to a relevant resource of information about the person, such as a homepage or third-party profile platform.', `avatar_id` int(10) unsigned DEFAULT NULL, `created_by` int(10) unsigned NOT NULL, `updated_by` int(10) unsigned NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_name` (`unique_name`), KEY `[[dbprefix]]persons_avatar_id_foreign` (`avatar_id`), KEY `[[dbprefix]]persons_created_by_foreign` (`created_by`), KEY `[[dbprefix]]persons_updated_by_foreign` (`updated_by`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1 ;
-- -- Structure for view `[[dbprefix]]credits` -- DROP TABLE IF EXISTS `[[dbprefix]]credits`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `[[dbprefix]]credits` AS select `[[dbprefix]]podcasts_persons`.`person_group` AS `person_group`,`[[dbprefix]]podcasts_persons`.`person_id` AS `person_id`,`[[dbprefix]]persons`.`full_name` AS `full_name`,`[[dbprefix]]podcasts_persons`.`person_role` AS `person_role`,`[[dbprefix]]podcasts_persons`.`podcast_id` AS `podcast_id`,NULL AS `episode_id` from (`[[dbprefix]]podcasts_persons` join `[[dbprefix]]persons` on((`[[dbprefix]]podcasts_persons`.`person_id` = `[[dbprefix]]persons`.`id`))) union select `[[dbprefix]]episodes_persons`.`person_group` AS `person_group`,`[[dbprefix]]episodes_persons`.`person_id` AS `person_id`,`[[dbprefix]]persons`.`full_name` AS `full_name`,`[[dbprefix]]episodes_persons`.`person_role` AS `person_role`,`[[dbprefix]]episodes_persons`.`podcast_id` AS `podcast_id`,`[[dbprefix]]episodes_persons`.`episode_id` AS `episode_id` from ((`[[dbprefix]]episodes_persons` join `[[dbprefix]]persons` on((`[[dbprefix]]episodes_persons`.`person_id` = `[[dbprefix]]persons`.`id`))) join `[[dbprefix]]episodes` on((`[[dbprefix]]episodes_persons`.`episode_id` = `[[dbprefix]]episodes`.`id`))) where (`[[dbprefix]]episodes`.`published_at` <= utc_timestamp()) order by `person_group`,`full_name`,`person_role`,`podcast_id`,`episode_id`;
-- -- Constraints for dumped tables --
-- -- Constraints for table `[[dbprefix]]auth_groups_users` -- ALTER TABLE `[[dbprefix]]auth_groups_users` ADD CONSTRAINT `[[dbprefix]]auth_groups_users_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `[[dbprefix]]users` (`id`) ON DELETE CASCADE;
-- -- Constraints for table `[[dbprefix]]auth_identities` -- ALTER TABLE `[[dbprefix]]auth_identities` ADD CONSTRAINT `[[dbprefix]]auth_identities_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `[[dbprefix]]users` (`id`) ON DELETE CASCADE;
-- -- Constraints for table `[[dbprefix]]auth_permissions_users` -- ALTER TABLE `[[dbprefix]]auth_permissions_users` ADD CONSTRAINT `[[dbprefix]]auth_permissions_users_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `[[dbprefix]]users` (`id`) ON DELETE CASCADE;
-- -- Constraints for table `[[dbprefix]]auth_remember_tokens` -- ALTER TABLE `[[dbprefix]]auth_remember_tokens` ADD CONSTRAINT `[[dbprefix]]auth_remember_tokens_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `[[dbprefix]]users` (`id`) ON DELETE CASCADE;
-- -- Constraints for table `[[dbprefix]]categories` -- ALTER TABLE `[[dbprefix]]categories` ADD CONSTRAINT `[[dbprefix]]categories_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `[[dbprefix]]categories` (`id`);
DELIMITER $$ -- -- Procedures -- CREATE PROCEDURE `[[dbprefix]]analytics_podcasts`( IN `p_podcast_id` INT UNSIGNED, IN `p_episode_id` INT UNSIGNED, IN `p_country_code` VARCHAR(3), IN `p_region_code` VARCHAR(3), IN `p_latitude` DECIMAL(8,6), IN `p_longitude` DECIMAL(9,6), IN `p_service` VARCHAR(128), IN `p_app` VARCHAR(128), IN `p_device` VARCHAR(32), IN `p_os` VARCHAR(32), IN `p_bot` TINYINT(1) UNSIGNED, IN `p_filesize` INT UNSIGNED, IN `p_duration` DECIMAL(8,3) UNSIGNED, IN `p_age` INT UNSIGNED, IN `p_new_listener` TINYINT(1) UNSIGNED, IN `p_subscription_id` INT UNSIGNED ) MODIFIES SQL DATA DETERMINISTIC SQL SECURITY INVOKER COMMENT 'Add one hit in podcast logs tables.' BEGIN
SET @current_datetime = UTC_TIMESTAMP(); SET @current_date = DATE(@current_datetime); SET @current_hour = HOUR(@current_datetime);
IF NOT `p_bot` THEN INSERT INTO `[[dbprefix]]analytics_podcasts`(`podcast_id`, `date`, `duration`, `bandwidth`) VALUES (p_podcast_id, @current_date, `p_duration`, `p_filesize`) ON DUPLICATE KEY UPDATE `duration`=`duration`+`p_duration`, `bandwidth`=`bandwidth`+`p_filesize`, `hits`=`hits`+1, `unique_listeners`=`unique_listeners`+`p_new_listener`; INSERT INTO `[[dbprefix]]analytics_podcasts_by_hour`(`podcast_id`, `date`, `hour`) VALUES (p_podcast_id, @current_date, @current_hour) ON DUPLICATE KEY UPDATE `hits`=`hits`+1; INSERT INTO `[[dbprefix]]analytics_podcasts_by_episode`(`podcast_id`, `episode_id`, `date`, `age`) VALUES (p_podcast_id, p_episode_id, @current_date, p_age) ON DUPLICATE KEY UPDATE `hits`=`hits`+1; INSERT INTO `[[dbprefix]]analytics_podcasts_by_country`(`podcast_id`, `country_code`, `date`) VALUES (p_podcast_id, p_country_code, @current_date) ON DUPLICATE KEY UPDATE `hits`=`hits`+1; INSERT INTO `[[dbprefix]]analytics_podcasts_by_region`(`podcast_id`, `country_code`, `region_code`, `latitude`, `longitude`, `date`) VALUES (p_podcast_id, p_country_code, p_region_code, p_latitude, p_longitude, @current_date) ON DUPLICATE KEY UPDATE `hits`=`hits`+1;
IF `p_subscription_id` THEN INSERT INTO `[[dbprefix]]analytics_podcasts_by_subscription`(`podcast_id`, `episode_id`, `subscription_id`, `date`) VALUES (p_podcast_id, p_episode_id, p_subscription_id, @current_date) ON DUPLICATE KEY UPDATE `hits`=`hits`+1; END IF; END IF; INSERT INTO `[[dbprefix]]analytics_podcasts_by_player`(`podcast_id`, `service`, `app`, `device`, `os`, `is_bot`, `date`) VALUES (p_podcast_id, p_service, p_app, p_device, p_os, p_bot, @current_date) ON DUPLICATE KEY UPDATE `hits`=`hits`+1; END$$
CREATE PROCEDURE `[[dbprefix]]analytics_unknown_useragents`(IN `p_useragent` VARCHAR(191)) MODIFIES SQL DATA DETERMINISTIC SQL SECURITY INVOKER COMMENT 'Add an unknown useragent to table [[dbprefix]]analytics_unknown_useragents.' INSERT INTO `[[dbprefix]]analytics_unknown_useragents`(`useragent`) VALUES (p_useragent) ON DUPLICATE KEY UPDATE `hits`=`hits`+1$$
CREATE PROCEDURE `[[dbprefix]]analytics_website`( IN `p_podcast_id` INT UNSIGNED, IN `p_browser` VARCHAR(191), IN `p_entry_page` VARCHAR(512), IN `p_referer_url` VARCHAR(512), IN `p_domain` VARCHAR(128), IN `p_keywords` VARCHAR(384) ) MODIFIES SQL DATA DETERMINISTIC SQL SECURITY INVOKER COMMENT 'Add one hit in website logs tables.' BEGIN
SET @current_date = DATE(UTC_TIMESTAMP());
INSERT INTO [[dbprefix]]analytics_website_by_browser(`podcast_id`, `browser`, `date`) VALUES (p_podcast_id, p_browser, @current_date) ON DUPLICATE KEY UPDATE `hits`=`hits`+1; INSERT INTO [[dbprefix]]analytics_website_by_referer(`podcast_id`, `referer_url`, `domain`, `keywords`, `date`) VALUES (p_podcast_id, p_referer_url, p_domain, p_keywords, @current_date) ON DUPLICATE KEY UPDATE `hits`=`hits`+1; INSERT INTO [[dbprefix]]analytics_website_by_entry_page(`podcast_id`, `entry_page_url`, `date`) VALUES (p_podcast_id, p_entry_page, @current_date) ON DUPLICATE KEY UPDATE `hits`=`hits`+1; END$$
DELIMITER ; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;