My Blog

youtube_analysis.db

  1. Youtube_SocialMedias
    • link_id (INTEGER, PRIMARY KEY, AUTOINCREMENT)
    • channel_id (TEXT, FOREIGN KEY REFERENCES Channels(channel_id))
    • sns_name (TEXT)
    • url (TEXT)
  2. Videos
    • video_id (TEXT, PRIMARY KEY)
    • upload_date (DATE)
    • video_title (TEXT)
    • game_title (TEXT)
    • tags (TEXT)
    • categories (TEXT)
    • description (TEXT)
    • duration (INTEGER)
    • duration_string (TEXT)
    • language (TEXT)
    • playable_in_embed (BOOLEAN)
    • age_limit (INTEGER)
    • comment_count (INTEGER)
    • chatcomment_path (TEXT)
    • ja_srt (TEXT)
    • like_count (INTEGER)
    • release_timestamp (INTEGER)
    • was_live (BOOLEAN)
    • heatmap (TEXT)
    • video_folder_path (TEXT)
    • channel_id (TEXT, FOREIGN KEY REFERENCES Channels(channel_id))
    • video_error (TEXT)
    • thumbnail_url (VARCHAR(255))
    • predicted_gametitle (TEXT)
    • predicted_tag (TEXT)
    • youtube_game_id (TEXT)
  3. BasicChatKeywords
    • keyword_id (INTEGER, PRIMARY KEY, AUTOINCREMENT)
    • keyword (TEXT)
    • emotion_category (TEXT)
    • weight (INTEGER)
    • priority (INTEGER DEFAULT 1)
  4. ChannelChatKeywords
    • keyword_id (INTEGER, PRIMARY KEY, AUTOINCREMENT)
    • channel_id (TEXT, FOREIGN KEY REFERENCES Channels(channel_id))
    • keyword (TEXT)
    • emotion_category (TEXT)
    • weight (INTEGER)
    • priority (INTEGER DEFAULT 1)
  5. AnalysisMethods
    • method_id (INTEGER, PRIMARY KEY, AUTOINCREMENT)
    • method_name (TEXT)
    • description (TEXT)
    • version (TEXT DEFAULT ‘1’)
    • analysis_subtype (TEXT)
  6. ChannelAnalysis
    • channel_id (TEXT, FOREIGN KEY REFERENCES Channels(channel_id))
    • method_id (INTEGER, FOREIGN KEY REFERENCES AnalysisMethods(method_id))
    • analysis_data (TEXT)
    • metadata (TEXT)
    • analyzed_video_ids (TEXT)
    • analyzed_date (TEXT)
    • wordcloud_path (TEXT)
  7. VideoAnalysis
    • video_id (TEXT, FOREIGN KEY REFERENCES Videos(video_id))
    • method_id (INTEGER, FOREIGN KEY REFERENCES AnalysisMethods(method_id))
    • analysis_data (TEXT)
    • use_keyword (TEXT DEFAULT ‘base’)
    • analyzed_date (DATE)
    • onesec_data (TEXT)
    • lost_chatcomment (TEXT)
  8. Channels
    • channel_id (TEXT, PRIMARY KEY)
    • channel_name (TEXT)
    • channel_url (TEXT)
    • uploader_id (TEXT)
    • group_name (TEXT)
    • cutout_video_permission (BOOLEAN DEFAULT NULL)
    • subscribers (INTEGER)
    • subscriber_transition (TEXT)
    • creation_date (DATE)
    • videos_count (INTEGER)
    • total_views (INTEGER)
    • location (TEXT)
    • description (TEXT)
    • icon_url (TEXT)
    • icon_path (TEXT)
    • ytdlp_scrape_date (DATE)
    • browser_scrape_date (DATE)
    • is_active (BOOLEAN DEFAULT NULL)
    • channel_error (TEXT)
    • on_website (TEXT DEFAULT ‘False’)
    • name (TEXT)
    • channel_category_tag (TEXT)
    • channel_game_tag (TEXT)
    • ai_generated_description (TEXT)
    • ai_description_generated_date (DATE)
  9. Aliases
    • alias_id (INTEGER, PRIMARY KEY)
    • game_id (INTEGER, FOREIGN KEY REFERENCES GameTitles(game_id))
    • alias_name (TEXT)
    • region (TEXT DEFAULT ‘Japan’)
  10. YouTubeGameInfo
    • youtube_game_id (TEXT, PRIMARY KEY)
    • youtube_game_title (TEXT)
    • description (TEXT)
    • release_year (INTEGER)
    • publisher (TEXT)
    • category_tag (TEXT)
  11. TwitchGameInfo
    • twitch_game_id (TEXT, PRIMARY KEY)
    • twitch_title (TEXT)
    • igdb_id (TEXT)
    • box_art_url (TEXT)
  12. GameTitleLink
    • game_id (INTEGER, PRIMARY KEY, AUTOINCREMENT)
    • youtube_game_id (TEXT, FOREIGN KEY REFERENCES YouTubeGameInfo(youtube_game_id))
    • twitch_game_id (TEXT, FOREIGN KEY REFERENCES TwitchGameInfo(twitch_game_id))
  13. AliasNames
    • alias_id (INTEGER, PRIMARY KEY, AUTOINCREMENT)
    • channel_id (TEXT, FOREIGN KEY REFERENCES Channels(channel_id))
    • alias_name (TEXT)
  14. TwitchChannels
    • user_login (TEXT, PRIMARY KEY)
    • twitch_user_id (VARCHAR(255))
    • description (TEXT)
    • displayname (VARCHAR(255))
    • followers (INT)
    • team_id (VARCHAR(255) DEFAULT NULL, FOREIGN KEY REFERENCES TwitchTeams(team_id))
    • twitch_channel_tag (TEXT)
  15. ChannelAssociations
    • association_id (INT, PRIMARY KEY, AUTO_INCREMENT)
    • youtube_channel_id (VARCHAR(255), FOREIGN KEY REFERENCES Channels(channel_id))
    • user_login (VARCHAR(255), FOREIGN KEY REFERENCES TwitchChannels(user_login))
  16. TwitchTeams
    • team_id (VARCHAR(255), PRIMARY KEY)
    • name (VARCHAR(255))
    • displayname (VARCHAR(255))
  17. TwitchVideos
    • twitch_video_id (VARCHAR(255), PRIMARY KEY)
    • stream_id (VARCHAR(255))
    • user_login (VARCHAR(255), FOREIGN KEY REFERENCES TwitchChannels(user_login))
    • twitch_video_title (TEXT)
    • created_at (DATETIME)
    • published_at (DATETIME)
    • twitch_game_id (VARCHAR(255), FOREIGN KEY REFERENCES TwitchGameInfo(twitch_game_id))
    • duration (VARCHAR(255))
    • view_count (INT)
    • language (VARCHAR(2))
    • type (TEXT)
  18. TwitchSocialMedias
    • id (VARCHAR(255), PRIMARY KEY)
    • name (VARCHAR(255))
    • title (VARCHAR(255))
    • url (TEXT)
    • user_login (VARCHAR(255), FOREIGN KEY REFERENCES TwitchChannels(user_login))
  19. TwitchClips
    • clip_id (TEXT, PRIMARY KEY)
    • clip_title (TEXT)
    • clip_url (TEXT)
    • view_count (INTEGER)
    • embed_url (TEXT)
    • twitch_video_id (TEXT)
    • created_at (DATETIME)
    • vod_offset (INTEGER)
    • user_login (TEXT, FOREIGN KEY REFERENCES TwitchChannels(user_login))
    • twitch_user_id (TEXT)
    • twitch_game_id (TEXT, FOREIGN KEY REFERENCES TwitchGameInfo(twitch_game_id))