search

ShokoAnime / ShokoServer

Repository for Shoko Server.
http://shokoanime.com/shoko-server/
MIT License
386 stars 75 forks source link

Database Unification Project #1006

Closed da3dsoul closed 1 year ago

da3dsoul commented 1 year ago

SQL Server vs SQLite

AniDB_Anime

SQL Server vs MySQL

AniDB_Anime

da3dsoul commented 1 year ago

SQL Server

DROP TABLE

AniDB_Anime_Category AniDB_Anime_Review AniDB_Category AniDB_MylistStats AniDB_Review CloudAccount FileFfdshowPreset

SQLite

DROP TABLE

AniDB_Anime_Category AniDB_Anime_Review AniDB_Category AniDB_MylistStats AniDB_Review CloudAccount FileFfdshowPreset

AniDB_Anime

Make DisableExternalLinksFlag NOT NULL

ImportFolder

IsWatched set default to 0 (currrently 1)

VideoLocal

IsVariation set not null default 0

MySQL

DROP TABLE

AniDB_Anime_Category AniDB_Anime_Review AniDB_Category AniDB_MylistStats AniDB_Review CloudAccount FileFfdshowPreset

AniDB_Anime_Character

CharType not null

Versions

auto increment starts at 403?

VideoLocal

drop VideoBitDepth

da3dsoul commented 1 year ago

SQL Server Indexes

Index Name Columns Index Type Unique Table Object Type
UIX_AniDB_Anime_AnimeID AnimeID Nonclustered unique index Unique dbo.AniDB_Anime Table
IX_AniDB_Anime_Character_AnimeID AnimeID Nonclustered unique index Not unique dbo.AniDB_Anime_Character Table
IX_AniDB_Anime_Character_CharID CharID Nonclustered unique index Not unique dbo.AniDB_Anime_Character Table
UIX_AniDB_Anime_Character_AnimeID_CharID AnimeID, CharID Nonclustered unique index Unique dbo.AniDB_Anime_Character Table
UIX_AniDB_Anime_DefaultImage_ImageType AnimeID, ImageType Nonclustered unique index Unique dbo.AniDB_Anime_DefaultImage Table
IX_AniDB_Anime_Relation_AnimeID AnimeID Nonclustered unique index Not unique dbo.AniDB_Anime_Relation Table
UIX_AniDB_Anime_Relation_AnimeID_RelatedAnimeID AnimeID, RelatedAnimeID Nonclustered unique index Unique dbo.AniDB_Anime_Relation Table
IX_AniDB_Anime_Similar_AnimeID AnimeID Nonclustered unique index Not unique dbo.AniDB_Anime_Similar Table
UIX_AniDB_Anime_Similar_AnimeID_SimilarAnimeID AnimeID, SimilarAnimeID Nonclustered unique index Unique dbo.AniDB_Anime_Similar Table
IX_AniDB_Anime_Tag_AnimeID AnimeID Nonclustered unique index Not unique dbo.AniDB_Anime_Tag Table
UIX_AniDB_Anime_Tag_AnimeID_TagID AnimeID, TagID Nonclustered unique index Unique dbo.AniDB_Anime_Tag Table
IX_AniDB_Anime_Title_AnimeID AnimeID Nonclustered unique index Not unique dbo.AniDB_Anime_Title Table
UIX_AniDB_AnimeUpdate AnimeID Nonclustered unique index Unique dbo.AniDB_AnimeUpdate Table
UIX_AniDB_Character_CharID CharID Nonclustered unique index Unique dbo.AniDB_Character Table
IX_AniDB_Character_Seiyuu_CharID CharID Nonclustered unique index Not unique dbo.AniDB_Character_Seiyuu Table
IX_AniDB_Character_Seiyuu_SeiyuuID SeiyuuID Nonclustered unique index Not unique dbo.AniDB_Character_Seiyuu Table
UIX_AniDB_Character_Seiyuu_CharID_SeiyuuID CharID, SeiyuuID Nonclustered unique index Unique dbo.AniDB_Character_Seiyuu Table
IX_AniDB_Episode_AnimeID AnimeID Nonclustered unique index Not unique dbo.AniDB_Episode Table
UIX_AniDB_Episode_EpisodeID EpisodeID Nonclustered unique index Unique dbo.AniDB_Episode Table
UIX_AniDB_File_Hash Hash Nonclustered unique index Unique dbo.AniDB_File Table
IX_AniDB_GroupStatus_AnimeID AnimeID Nonclustered unique index Not unique dbo.AniDB_GroupStatus Table
UIX_AniDB_GroupStatus_AnimeID_GroupID AnimeID, GroupID Nonclustered unique index Unique dbo.AniDB_GroupStatus Table
UIX_AniDB_Recommendation AnimeID, UserID Nonclustered unique index Unique dbo.AniDB_Recommendation Table
UIX_AniDB_ReleaseGroup_GroupID GroupID Nonclustered unique index Unique dbo.AniDB_ReleaseGroup Table
UIX_AniDB_Seiyuu_SeiyuuID SeiyuuID Nonclustered unique index Unique dbo.AniDB_Seiyuu Table
UIX_AniDB_Tag_TagID TagID Nonclustered unique index Unique dbo.AniDB_Tag Table
IX_AnimeEpisode_AnimeSeriesID AnimeSeriesID Nonclustered unique index Not unique dbo.AnimeEpisode Table
UIX_AnimeEpisode_AniDB_EpisodeID AniDB_EpisodeID Nonclustered unique index Unique dbo.AnimeEpisode Table
IX_AnimeEpisode_User_User_AnimeSeriesID JMMUserID, AnimeSeriesID Nonclustered unique index Not unique dbo.AnimeEpisode_User Table
UIX_AnimeEpisode_User_User_EpisodeID JMMUserID, AnimeEpisodeID Nonclustered unique index Unique dbo.AnimeEpisode_User Table
UIX_AnimeGroup_User_User_GroupID JMMUserID, AnimeGroupID Nonclustered unique index Unique dbo.AnimeGroup_User Table
UIX_AnimeSeries_AniDB_ID AniDB_ID Nonclustered unique index Unique dbo.AnimeSeries Table
UIX_AnimeSeries_User_User_SeriesID JMMUserID, AnimeSeriesID Nonclustered unique index Unique dbo.AnimeSeries_User Table
UIX_BookmarkedAnime_AnimeID BookmarkedAnimeID Nonclustered unique index Unique dbo.BookmarkedAnime Table
UIX_CrossRef_AniDB_Other AnimeID, CrossRefID, CrossRefSource, CrossRefType Nonclustered unique index Unique dbo.CrossRef_AniDB_Other Table
UIX_CrossRef_AniDB_Trakt_Anime AnimeID Nonclustered unique index Unique dbo.CrossRef_AniDB_Trakt Table
UIX_CrossRef_AniDB_Trakt_Season TraktID, TraktSeasonNumber Nonclustered unique index Unique dbo.CrossRef_AniDB_Trakt Table
UIX_CrossRef_AniDB_Trakt_Episode_AniDBEpisodeID AniDBEpisodeID Nonclustered unique index Unique dbo.CrossRef_AniDB_Trakt_Episode Table
UIX_CrossRef_AniDB_TraktV2 AnimeID, TraktSeasonNumber, TraktStartEpisodeNumber, AniDBStartEpisodeType, AniDBStartEpisodeNumber Nonclustered unique index Unique dbo.CrossRef_AniDB_TraktV2 Table
UIX_AniDB_TvDB_AniDBID_TvDBID AniDBID, TvDBID Nonclustered unique index Unique dbo.CrossRef_AniDB_TvDB Table
UIX_CrossRef_AniDB_TvDB_Episode_AniDBID_TvDBID AniDBEpisodeID, TvDBEpisodeID Nonclustered unique index Unique dbo.CrossRef_AniDB_TvDB_Episode Table
UIX_AniDB_TvDB_Episode_Override_AniDBEpisodeID_TvDBEpisodeID AniDBEpisodeID, TvDBEpisodeID Nonclustered unique index Unique dbo.CrossRef_AniDB_TvDB_Episode_Override Table
UIX_CrossRef_File_Episode_Hash_EpisodeID Hash, EpisodeID Nonclustered unique index Unique dbo.CrossRef_File_Episode Table
UIX_FileNameHash FileName, FileSize, Hash Nonclustered unique index Unique dbo.FileNameHash Table
UIX_IgnoreAnime_User_AnimeID JMMUserID, AnimeID, IgnoreType Nonclustered unique index Unique dbo.IgnoreAnime Table
UIX_MovieDB_Movie_Id MovieId Nonclustered unique index Unique dbo.MovieDB_Movie Table
UIX_ScanFileStatus ScanID, Status, CheckDate Nonclustered unique index Not unique dbo.ScanFile Table
UIX_ScheduledUpdate_UpdateType UpdateType Nonclustered unique index Unique dbo.ScheduledUpdate Table
UIX_Trakt_Friend_Username Username Nonclustered unique index Unique dbo.Trakt_Friend Table
UIX_TvDB_Episode_Id Id Nonclustered unique index Unique dbo.TvDB_Episode Table
UIX_TvDB_ImageFanart_Id Id Nonclustered unique index Unique dbo.TvDB_ImageFanart Table
UIX_TvDB_ImagePoster_Id Id Nonclustered unique index Unique dbo.TvDB_ImagePoster Table
UIX_TvDB_ImageWideBanner_Id Id Nonclustered unique index Unique dbo.TvDB_ImageWideBanner Table
UIX_TvDB_Series_Id SeriesID Nonclustered unique index Unique dbo.TvDB_Series Table
IX_Versions_VersionType VersionType, VersionValue, VersionRevision Nonclustered unique index Not unique dbo.Versions Table
UIX_VideoLocal_Hash Hash Nonclustered unique index Not unique dbo.VideoLocal Table
UIX_VideoLocal_User_User_VideoLocalID JMMUserID, VideoLocalID Nonclustered unique index Unique dbo.VideoLocal_User Table
da3dsoul commented 1 year ago

SQLite

Index Name Columns Table Name
UIX2_AniDB_Anime_AnimeID AnimeID AniDB_Anime
UIX_AniDB_AnimeUpdate AnimeID AniDB_AnimeUpdate
IX_AniDB_Anime_Category_AnimeID AnimeID AniDB_Anime_Category
UIX_AniDB_Anime_Category_AnimeID_CategoryID AnimeID, CategoryID AniDB_Anime_Category
IX_AniDB_Anime_Character_AnimeID AnimeID AniDB_Anime_Character
IX_AniDB_Anime_Character_CharID CharID AniDB_Anime_Character
UIX_AniDB_Anime_Character_AnimeID_CharID AnimeID, CharID AniDB_Anime_Character
UIX_AniDB_Anime_DefaultImage_ImageType AnimeID, ImageType AniDB_Anime_DefaultImage
IX_AniDB_Anime_Relation_AnimeID AnimeID AniDB_Anime_Relation
UIX_AniDB_Anime_Relation_AnimeID_RelatedAnimeID AnimeID, RelatedAnimeID AniDB_Anime_Relation
IX_AniDB_Anime_Review_AnimeID AnimeID AniDB_Anime_Review
UIX_AniDB_Anime_Review_AnimeID_ReviewID AnimeID, ReviewID AniDB_Anime_Review
IX_AniDB_Anime_Similar_AnimeID AnimeID AniDB_Anime_Similar
UIX_AniDB_Anime_Similar_AnimeID_SimilarAnimeID AnimeID, SimilarAnimeID AniDB_Anime_Similar
IX_AniDB_Anime_Tag_AnimeID AnimeID AniDB_Anime_Tag
UIX_AniDB_Anime_Tag_AnimeID_TagID AnimeID, TagID AniDB_Anime_Tag
IX_AniDB_Anime_Title_AnimeID AnimeID AniDB_Anime_Title
UIX_AniDB_Category_CategoryID CategoryID AniDB_Category
UIX_AniDB_Character_CharID CharID AniDB_Character
IX_AniDB_Character_Seiyuu_CharID CharID AniDB_Character_Seiyuu
IX_AniDB_Character_Seiyuu_SeiyuuID SeiyuuID AniDB_Character_Seiyuu
UIX_AniDB_Character_Seiyuu_CharID_SeiyuuID CharID, SeiyuuID AniDB_Character_Seiyuu
IX_AniDB_Episode_AnimeID AnimeID AniDB_Episode
UIX_AniDB_Episode_EpisodeID EpisodeID AniDB_Episode
IX_AniDB_File_File_Source File_Source AniDB_File
UIX_AniDB_File_FileID FileID AniDB_File
UIX_AniDB_File_Hash Hash, FileSize AniDB_File
IX_AniDB_GroupStatus_AnimeID AnimeID AniDB_GroupStatus
UIX_AniDB_GroupStatus_AnimeID_GroupID AnimeID, GroupID AniDB_GroupStatus
UIX_AniDB_Recommendation AnimeID, UserID AniDB_Recommendation
UIX_AniDB_ReleaseGroup_GroupID GroupID AniDB_ReleaseGroup
UIX_AniDB_Review_ReviewID ReviewID AniDB_Review
UIX_AniDB_Seiyuu_SeiyuuID SeiyuuID AniDB_Seiyuu
UIX_AniDB_Tag_TagID TagID AniDB_Tag
IX_AnimeEpisode_AnimeSeriesID AnimeSeriesID AnimeEpisode
UIX_AnimeEpisode_AniDB_EpisodeID AniDB_EpisodeID AnimeEpisode
IX_AnimeEpisode_User_User_AnimeSeriesID JMMUserID, AnimeSeriesID AnimeEpisode_User
UIX_AnimeEpisode_User_User_EpisodeID JMMUserID, AnimeEpisodeID AnimeEpisode_User
UIX_AnimeGroup_User_User_GroupID JMMUserID, AnimeGroupID AnimeGroup_User
UIX_AnimeSeries_AniDB_ID AniDB_ID AnimeSeries
UIX_AnimeSeries_User_User_SeriesID JMMUserID, AnimeSeriesID AnimeSeries_User
UIX_BookmarkedAnime_AnimeID BookmarkedAnimeID BookmarkedAnime
UIX_CloudAccount_CloudID [CloudID] CloudAccount
UIX_CrossRef_AniDB_Other AnimeID, CrossRefID, CrossRefSource, CrossRefType CrossRef_AniDB_Other
UIX_CrossRef_AniDB_Trakt_Anime AnimeID CrossRef_AniDB_Trakt
UIX_CrossRef_AniDB_Trakt_Season TraktID, TraktSeasonNumber CrossRef_AniDB_Trakt
UIX_CrossRef_AniDB_TraktV2 AnimeID, TraktSeasonNumber, TraktStartEpisodeNumber, AniDBStartEpisodeType, AniDBStartEpisodeNumber CrossRef_AniDB_TraktV2
UIX_CrossRef_AniDB_Trakt_Episode_AniDBEpisodeID AniDBEpisodeID CrossRef_AniDB_Trakt_Episode
UIX_AniDB_TvDB_AniDBID_TvDBID AniDBID,TvDBID CrossRef_AniDB_TvDB
UIX_CrossRef_AniDB_TvDB_Episode_AniDBID_TvDBID AniDBEpisodeID,TvDBEpisodeID CrossRef_AniDB_TvDB_Episode
UIX_AniDB_TvDB_Episode_Override_AniDBEpisodeID_TvDBEpisodeID AniDBEpisodeID,TvDBEpisodeID CrossRef_AniDB_TvDB_Episode_Override
IX_CrossRef_File_Episode_EpisodeID EpisodeID CrossRef_File_Episode
IX_CrossRef_File_Episode_Hash Hash CrossRef_File_Episode
UIX_CrossRef_File_Episode_Hash_EpisodeID Hash, EpisodeID CrossRef_File_Episode
UIX_FileFfdshowPreset_Hash Hash, FileSize FileFfdshowPreset
UIX_FileNameHash FileName, FileSize, Hash FileNameHash
UIX_IgnoreAnime_User_AnimeID JMMUserID, AnimeID, IgnoreType IgnoreAnime
UIX_MovieDB_Movie_Id MovieId MovieDB_Movie
UIX_ScanFileStatus ScanID,Status,CheckDate ScanFile
UIX_ScheduledUpdate_UpdateType UpdateType ScheduledUpdate
UIX_Trakt_Friend_Username Username Trakt_Friend
UIX_TvDB_Episode_Id Id TvDB_Episode
UIX_TvDB_ImageFanart_Id Id TvDB_ImageFanart
UIX_TvDB_ImagePoster_Id Id TvDB_ImagePoster
UIX_TvDB_ImageWideBanner_Id Id TvDB_ImageWideBanner
UIX_TvDB_Series_Id SeriesID TvDB_Series
IX_Versions_VersionType VersionType,VersionValue,VersionRevision Versions
UIX2_VideoLocal_Hash Hash VideoLocal
UIXVideoLocal VideoLocal_Place_ID [VideoLocal_Place_ID] VideoLocal_Place
UIX2_VideoLocal_User_User_VideoLocalID JMMUserID, VideoLocalID VideoLocal_User
da3dsoul commented 1 year ago

SQLite Indexes

UIX2_AniDB_Anime_AnimeID -> UIX_AniDB_Anime_AnimeID IX_AniDB_File_File_Source DROP IX_CrossRef_File_Episode_EpisodeID DROP IX_CrossRef_File_Episode_Hash DROP UIX2_VideoLocal_Hash -> UIX_VideoLocal_Hash UIX2_VideoLocal_User_User_VideoLocalID -> UIX_VideoLocal_User_User_VideoLocalID UIXVideoLocal VideoLocal_Place_ID DROP

SQL Server

UIX_AniDB_File_FileID CREATE

da3dsoul commented 1 year ago

MySQL

Table Index Columns
AniDB_Anime UIX_AniDB_Anime_AnimeID AnimeID
AniDB_AnimeUpdate UIX_AniDB_AnimeUpdate AnimeID
AniDB_Anime_Character IX_AniDB_Anime_Character_AnimeID AnimeID
AniDB_Anime_Character IX_AniDB_Anime_Character_CharID CharID
AniDB_Anime_Character UIX_AniDB_Anime_Character_AnimeID_CharID AnimeID,CharID
AniDB_Anime_DefaultImage UIX_AniDB_Anime_DefaultImage_ImageType AnimeID,ImageType
AniDB_Anime_Relation IX_AniDB_Anime_Relation_AnimeID AnimeID
AniDB_Anime_Relation UIX_AniDB_Anime_Relation_AnimeID_RelatedAnimeID AnimeID,RelatedAnimeID
AniDB_Anime_Review IX_AniDB_Anime_Review_AnimeID AnimeID
AniDB_Anime_Review UIX_AniDB_Anime_Review_AnimeID_ReviewID AnimeID,ReviewID
AniDB_Anime_Similar IX_AniDB_Anime_Similar_AnimeID AnimeID
AniDB_Anime_Similar UIX_AniDB_Anime_Similar_AnimeID_SimilarAnimeID AnimeID,SimilarAnimeID
AniDB_Anime_Tag IX_AniDB_Anime_Tag_AnimeID AnimeID
AniDB_Anime_Tag UIX_AniDB_Anime_Tag_AnimeID_TagID AnimeID,TagID
AniDB_Anime_Title IX_AniDB_Anime_Title_AnimeID AnimeID
AniDB_Character UIX_AniDB_Character_CharID CharID
AniDB_Character_Seiyuu IX_AniDB_Character_Seiyuu_CharID CharID
AniDB_Character_Seiyuu IX_AniDB_Character_Seiyuu_SeiyuuID SeiyuuID
AniDB_Character_Seiyuu UIX_AniDB_Character_Seiyuu_CharID_SeiyuuID CharID,SeiyuuID
AniDB_Episode IX_AniDB_Episode_AnimeID AnimeID
AniDB_Episode UIX_AniDB_Episode_EpisodeID EpisodeID
AniDB_File UIX_AniDB_File_Hash Hash
AniDB_GroupStatus IX_AniDB_GroupStatus_AnimeID AnimeID
AniDB_GroupStatus UIX_AniDB_GroupStatus_AnimeID_GroupID AnimeID,GroupID
AniDB_Recommendation UIX_AniDB_Recommendation AnimeID,UserID
AniDB_ReleaseGroup UIX_AniDB_ReleaseGroup_GroupID GroupID
AniDB_Seiyuu UIX_AniDB_Seiyuu_SeiyuuID SeiyuuID
AniDB_Tag UIX_AniDB_Tag_TagID TagID
AnimeEpisode IX_AnimeEpisode_AnimeSeriesID AnimeSeriesID
AnimeEpisode UIX_AnimeEpisode_AniDB_EpisodeID AniDB_EpisodeID
AnimeEpisode_User IX_AnimeEpisode_User_User_AnimeSeriesID JMMUserID,AnimeSeriesID
AnimeEpisode_User UIX_AnimeEpisode_User_User_EpisodeID JMMUserID,AnimeEpisodeID
AnimeGroup_User UIX_AnimeGroup_User_User_GroupID JMMUserID,AnimeGroupID
AnimeSeries UIX_AnimeSeries_AniDB_ID AniDB_ID
AnimeSeries_User UIX_AnimeSeries_User_User_SeriesID JMMUserID,AnimeSeriesID
BookmarkedAnime UIX_BookmarkedAnime_AnimeID AnimeID
CrossRef_AniDB_Other UIX_CrossRef_AniDB_Other AnimeID,CrossRefID,CrossRefSource,CrossRefType
CrossRef_AniDB_Trakt UIX_CrossRef_AniDB_Trakt_Anime AnimeID
CrossRef_AniDB_Trakt UIX_CrossRef_AniDB_Trakt_Season TraktID,TraktSeasonNumber
CrossRef_AniDB_TraktV2 UIX_CrossRef_AniDB_TraktV2 AnimeID,TraktSeasonNumber,TraktStartEpisodeNumber,AniDBStartEpisodeType,AniDBStartEpisodeNumber
CrossRef_AniDB_Trakt_Episode UIX_CrossRef_AniDB_Trakt_Episode_AniDBEpisodeID AniDBEpisodeID
CrossRef_AniDB_TvDB UIX_AniDB_TvDB_AniDBID_TvDBID AniDBID,TvDBID
CrossRef_AniDB_TvDB_Episode UIX_CrossRef_AniDB_TvDB_Episode_AniDBID_TvDBID AniDBEpisodeID,TvDBEpisodeID
CrossRef_AniDB_TvDB_Episode_Override UIX_AniDB_TvDB_Episode_Override_AniDBEpisodeID_TvDBEpisodeID AniDBEpisodeID,TvDBEpisodeID
CrossRef_File_Episode IX_Xref_Epid EpisodeID
CrossRef_File_Episode UIX_CrossRef_File_Episode_Hash_EpisodeID Hash,EpisodeID
CrossRef_Languages_AniDB_File IX_Xref_Epid FileID
CrossRef_Subtitles_AniDB_File IX_Xref_Sub_AniDBFile FileID
GroupFilter IX_groupfilter_GroupFilterName GroupFilterName
IgnoreAnime UIX_IgnoreAnime_User_AnimeID JMMUserID,AnimeID,IgnoreType
MovieDB_Movie UIX_MovieDB_Movie_Id MovieId
ScanFile UIX_ScanFileStatus ScanID,Status,CheckDate
ScheduledUpdate UIX_ScheduledUpdate_UpdateType UpdateType
TvDB_Episode UIX_TvDB_Episode_Id Id
TvDB_ImageFanart UIX_TvDB_ImageFanart_Id Id
TvDB_ImagePoster UIX_TvDB_ImagePoster_Id Id
TvDB_ImageWideBanner UIX_TvDB_ImageWideBanner_Id Id
TvDB_Series UIX_TvDB_Series_Id SeriesID
Versions IX_Versions_VersionType VersionType,VersionValue,VersionRevision
VideoLocal IX_VideoLocal_Hash Hash
VideoLocal_User UIX_VideoLocal_User_User_VideoLocalID JMMUserID,VideoLocalID
da3dsoul commented 1 year ago

MySQL Indexes

Table Name Columns Task
AniDB_File IX_AniDB_File_FileID FileID Create
CrossRef_File_Episode IX_Xref_Epid EpisodeID Rename to IX_CrossRef_File_Episode_EpisodeID
CrossRef_Languages_AniDB_File IX_Xref_Epid FileID DROP
CrossRef_Subtitles_AniDB_File IX_Xref_Sub_AniDBFile FileID DROP
GroupFilter IX_groupfilter_GroupFilterName GroupFilterName DROP
VideoLocal IX_VideoLocal_Hash Hash Rename to/Make Unique UIX_VideoLocal_Hash