SqliteItemRepository.cs 149 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625262626272628262926302631263226332634263526362637263826392640264126422643264426452646264726482649265026512652265326542655265626572658265926602661266226632664266526662667266826692670267126722673267426752676267726782679268026812682268326842685268626872688268926902691269226932694269526962697269826992700270127022703270427052706270727082709271027112712271327142715271627172718271927202721272227232724272527262727272827292730273127322733273427352736273727382739274027412742274327442745274627472748274927502751275227532754275527562757275827592760276127622763276427652766276727682769277027712772277327742775277627772778277927802781278227832784278527862787278827892790279127922793279427952796279727982799280028012802280328042805280628072808280928102811281228132814281528162817281828192820282128222823282428252826282728282829283028312832283328342835283628372838283928402841284228432844284528462847284828492850285128522853285428552856285728582859286028612862286328642865286628672868286928702871287228732874287528762877287828792880288128822883288428852886288728882889289028912892289328942895289628972898289929002901290229032904290529062907290829092910291129122913291429152916291729182919292029212922292329242925292629272928292929302931293229332934293529362937293829392940294129422943294429452946294729482949295029512952295329542955295629572958295929602961296229632964296529662967296829692970297129722973297429752976297729782979298029812982298329842985298629872988298929902991299229932994299529962997299829993000300130023003300430053006300730083009301030113012301330143015301630173018301930203021302230233024302530263027302830293030303130323033303430353036303730383039304030413042304330443045304630473048304930503051305230533054305530563057305830593060306130623063306430653066306730683069307030713072307330743075307630773078307930803081308230833084308530863087308830893090309130923093309430953096309730983099310031013102310331043105310631073108310931103111311231133114311531163117311831193120312131223123312431253126312731283129313031313132313331343135313631373138313931403141314231433144314531463147314831493150315131523153315431553156315731583159316031613162316331643165316631673168316931703171317231733174317531763177317831793180318131823183318431853186318731883189319031913192319331943195319631973198319932003201320232033204320532063207320832093210321132123213321432153216321732183219322032213222322332243225322632273228322932303231323232333234323532363237323832393240324132423243324432453246324732483249325032513252325332543255325632573258325932603261326232633264326532663267326832693270327132723273327432753276327732783279328032813282328332843285328632873288328932903291329232933294329532963297329832993300330133023303330433053306330733083309331033113312331333143315331633173318331933203321332233233324332533263327332833293330333133323333333433353336333733383339334033413342334333443345334633473348334933503351335233533354335533563357335833593360336133623363336433653366336733683369337033713372337333743375337633773378337933803381338233833384338533863387338833893390339133923393339433953396339733983399340034013402340334043405340634073408340934103411341234133414341534163417341834193420342134223423342434253426342734283429343034313432343334343435343634373438343934403441344234433444344534463447344834493450345134523453345434553456345734583459346034613462346334643465346634673468346934703471347234733474347534763477347834793480348134823483348434853486348734883489349034913492349334943495349634973498349935003501350235033504350535063507350835093510351135123513351435153516351735183519352035213522352335243525352635273528352935303531353235333534353535363537353835393540354135423543354435453546354735483549355035513552355335543555355635573558355935603561356235633564356535663567356835693570357135723573357435753576357735783579358035813582358335843585358635873588358935903591359235933594359535963597359835993600360136023603360436053606360736083609361036113612361336143615361636173618361936203621362236233624362536263627362836293630363136323633363436353636363736383639364036413642364336443645364636473648364936503651365236533654365536563657365836593660366136623663366436653666366736683669367036713672367336743675367636773678367936803681368236833684368536863687368836893690369136923693369436953696369736983699370037013702370337043705370637073708370937103711371237133714371537163717371837193720372137223723372437253726372737283729373037313732373337343735373637373738373937403741374237433744374537463747374837493750375137523753375437553756375737583759376037613762376337643765376637673768376937703771377237733774377537763777377837793780378137823783378437853786378737883789379037913792379337943795379637973798379938003801380238033804380538063807380838093810381138123813381438153816381738183819382038213822382338243825382638273828382938303831383238333834383538363837383838393840384138423843384438453846384738483849385038513852385338543855385638573858385938603861386238633864386538663867386838693870387138723873387438753876387738783879388038813882388338843885388638873888388938903891389238933894389538963897389838993900390139023903390439053906390739083909391039113912391339143915391639173918391939203921392239233924392539263927392839293930393139323933393439353936393739383939394039413942394339443945394639473948394939503951395239533954395539563957395839593960396139623963396439653966396739683969397039713972397339743975397639773978397939803981398239833984398539863987398839893990399139923993399439953996399739983999
  1. using MediaBrowser.Controller.Entities;
  2. using MediaBrowser.Controller.Entities.Audio;
  3. using MediaBrowser.Controller.Entities.Movies;
  4. using MediaBrowser.Controller.Entities.TV;
  5. using MediaBrowser.Controller.LiveTv;
  6. using MediaBrowser.Controller.Persistence;
  7. using MediaBrowser.Model.Entities;
  8. using MediaBrowser.Model.Logging;
  9. using MediaBrowser.Model.Querying;
  10. using MediaBrowser.Model.Serialization;
  11. using System;
  12. using System.Collections.Generic;
  13. using System.Data;
  14. using System.Globalization;
  15. using System.IO;
  16. using System.Linq;
  17. using System.Runtime.Serialization;
  18. using System.Text;
  19. using System.Threading;
  20. using System.Threading.Tasks;
  21. using MediaBrowser.Common.Extensions;
  22. using MediaBrowser.Controller.Channels;
  23. using MediaBrowser.Controller.Configuration;
  24. using MediaBrowser.Controller.Playlists;
  25. using MediaBrowser.Model.LiveTv;
  26. namespace MediaBrowser.Server.Implementations.Persistence
  27. {
  28. /// <summary>
  29. /// Class SQLiteItemRepository
  30. /// </summary>
  31. public class SqliteItemRepository : BaseSqliteRepository, IItemRepository
  32. {
  33. private IDbConnection _connection;
  34. private readonly TypeMapper _typeMapper = new TypeMapper();
  35. /// <summary>
  36. /// Gets the name of the repository
  37. /// </summary>
  38. /// <value>The name.</value>
  39. public string Name
  40. {
  41. get
  42. {
  43. return "SQLite";
  44. }
  45. }
  46. /// <summary>
  47. /// Gets the json serializer.
  48. /// </summary>
  49. /// <value>The json serializer.</value>
  50. private readonly IJsonSerializer _jsonSerializer;
  51. /// <summary>
  52. /// The _app paths
  53. /// </summary>
  54. private readonly IServerConfigurationManager _config;
  55. /// <summary>
  56. /// The _save item command
  57. /// </summary>
  58. private IDbCommand _saveItemCommand;
  59. private readonly string _criticReviewsPath;
  60. private IDbCommand _deleteItemCommand;
  61. private IDbCommand _deletePeopleCommand;
  62. private IDbCommand _savePersonCommand;
  63. private IDbCommand _deleteChaptersCommand;
  64. private IDbCommand _saveChapterCommand;
  65. private IDbCommand _deleteStreamsCommand;
  66. private IDbCommand _saveStreamCommand;
  67. private IDbCommand _deleteAncestorsCommand;
  68. private IDbCommand _saveAncestorCommand;
  69. private IDbCommand _deleteUserDataKeysCommand;
  70. private IDbCommand _saveUserDataKeysCommand;
  71. private IDbCommand _deleteItemValuesCommand;
  72. private IDbCommand _saveItemValuesCommand;
  73. private IDbCommand _deleteProviderIdsCommand;
  74. private IDbCommand _saveProviderIdsCommand;
  75. private IDbCommand _deleteImagesCommand;
  76. private IDbCommand _saveImagesCommand;
  77. private IDbCommand _updateInheritedRatingCommand;
  78. private IDbCommand _updateInheritedTagsCommand;
  79. public const int LatestSchemaVersion = 89;
  80. /// <summary>
  81. /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class.
  82. /// </summary>
  83. public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogManager logManager)
  84. : base(logManager)
  85. {
  86. if (config == null)
  87. {
  88. throw new ArgumentNullException("config");
  89. }
  90. if (jsonSerializer == null)
  91. {
  92. throw new ArgumentNullException("jsonSerializer");
  93. }
  94. _config = config;
  95. _jsonSerializer = jsonSerializer;
  96. _criticReviewsPath = Path.Combine(_config.ApplicationPaths.DataPath, "critic-reviews");
  97. }
  98. private const string ChaptersTableName = "Chapters2";
  99. /// <summary>
  100. /// Opens the connection to the database
  101. /// </summary>
  102. /// <returns>Task.</returns>
  103. public async Task Initialize(IDbConnector dbConnector)
  104. {
  105. var dbFile = Path.Combine(_config.ApplicationPaths.DataPath, "library.db");
  106. _connection = await dbConnector.Connect(dbFile, 6000).ConfigureAwait(false);
  107. var createMediaStreamsTableCommand
  108. = "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, TimeBase TEXT NULL, CodecTimeBase TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))";
  109. string[] queries = {
  110. "create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB, ParentId GUID, Path TEXT)",
  111. "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)",
  112. "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)",
  113. "create index if not exists idx_TypedBaseItems2 on TypedBaseItems(Type,Guid)",
  114. "create table if not exists AncestorIds (ItemId GUID, AncestorId GUID, AncestorIdText TEXT, PRIMARY KEY (ItemId, AncestorId))",
  115. "create index if not exists idx_AncestorIds1 on AncestorIds(AncestorId)",
  116. "create index if not exists idx_AncestorIds2 on AncestorIds(AncestorIdText)",
  117. "create table if not exists UserDataKeys (ItemId GUID, UserDataKey TEXT, PRIMARY KEY (ItemId, UserDataKey))",
  118. "create index if not exists idx_UserDataKeys1 on UserDataKeys(ItemId)",
  119. "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT)",
  120. "create index if not exists idx_ItemValues on ItemValues(ItemId)",
  121. "create index if not exists idx_ItemValues2 on ItemValues(ItemId,Type)",
  122. "create table if not exists ProviderIds (ItemId GUID, Name TEXT, Value TEXT, PRIMARY KEY (ItemId, Name))",
  123. "create index if not exists Idx_ProviderIds on ProviderIds(ItemId)",
  124. "create table if not exists Images (ItemId GUID NOT NULL, Path TEXT NOT NULL, ImageType INT NOT NULL, DateModified DATETIME, IsPlaceHolder BIT NOT NULL, SortOrder INT)",
  125. "create index if not exists idx_Images on Images(ItemId)",
  126. "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)",
  127. "create index if not exists idxPeopleItemId on People(ItemId)",
  128. "create index if not exists idxPeopleName on People(Name)",
  129. "create table if not exists "+ChaptersTableName+" (ItemId GUID, ChapterIndex INT, StartPositionTicks BIGINT, Name TEXT, ImagePath TEXT, PRIMARY KEY (ItemId, ChapterIndex))",
  130. "create index if not exists idx_"+ChaptersTableName+"1 on "+ChaptersTableName+"(ItemId)",
  131. createMediaStreamsTableCommand,
  132. "create index if not exists idx_mediastreams1 on mediastreams(ItemId)",
  133. //pragmas
  134. "pragma temp_store = memory",
  135. "pragma shrink_memory"
  136. };
  137. _connection.RunQueries(queries, Logger);
  138. _connection.AddColumn(Logger, "AncestorIds", "AncestorIdText", "Text");
  139. _connection.AddColumn(Logger, "TypedBaseItems", "Path", "Text");
  140. _connection.AddColumn(Logger, "TypedBaseItems", "StartDate", "DATETIME");
  141. _connection.AddColumn(Logger, "TypedBaseItems", "EndDate", "DATETIME");
  142. _connection.AddColumn(Logger, "TypedBaseItems", "ChannelId", "Text");
  143. _connection.AddColumn(Logger, "TypedBaseItems", "IsMovie", "BIT");
  144. _connection.AddColumn(Logger, "TypedBaseItems", "IsSports", "BIT");
  145. _connection.AddColumn(Logger, "TypedBaseItems", "IsKids", "BIT");
  146. _connection.AddColumn(Logger, "TypedBaseItems", "CommunityRating", "Float");
  147. _connection.AddColumn(Logger, "TypedBaseItems", "CustomRating", "Text");
  148. _connection.AddColumn(Logger, "TypedBaseItems", "IndexNumber", "INT");
  149. _connection.AddColumn(Logger, "TypedBaseItems", "IsLocked", "BIT");
  150. _connection.AddColumn(Logger, "TypedBaseItems", "Name", "Text");
  151. _connection.AddColumn(Logger, "TypedBaseItems", "OfficialRating", "Text");
  152. _connection.AddColumn(Logger, "TypedBaseItems", "MediaType", "Text");
  153. _connection.AddColumn(Logger, "TypedBaseItems", "Overview", "Text");
  154. _connection.AddColumn(Logger, "TypedBaseItems", "ParentIndexNumber", "INT");
  155. _connection.AddColumn(Logger, "TypedBaseItems", "PremiereDate", "DATETIME");
  156. _connection.AddColumn(Logger, "TypedBaseItems", "ProductionYear", "INT");
  157. _connection.AddColumn(Logger, "TypedBaseItems", "ParentId", "GUID");
  158. _connection.AddColumn(Logger, "TypedBaseItems", "Genres", "Text");
  159. _connection.AddColumn(Logger, "TypedBaseItems", "ParentalRatingValue", "INT");
  160. _connection.AddColumn(Logger, "TypedBaseItems", "SchemaVersion", "INT");
  161. _connection.AddColumn(Logger, "TypedBaseItems", "SortName", "Text");
  162. _connection.AddColumn(Logger, "TypedBaseItems", "RunTimeTicks", "BIGINT");
  163. _connection.AddColumn(Logger, "TypedBaseItems", "OfficialRatingDescription", "Text");
  164. _connection.AddColumn(Logger, "TypedBaseItems", "HomePageUrl", "Text");
  165. _connection.AddColumn(Logger, "TypedBaseItems", "VoteCount", "INT");
  166. _connection.AddColumn(Logger, "TypedBaseItems", "DisplayMediaType", "Text");
  167. _connection.AddColumn(Logger, "TypedBaseItems", "DateCreated", "DATETIME");
  168. _connection.AddColumn(Logger, "TypedBaseItems", "DateModified", "DATETIME");
  169. _connection.AddColumn(Logger, "TypedBaseItems", "ForcedSortName", "Text");
  170. _connection.AddColumn(Logger, "TypedBaseItems", "IsOffline", "BIT");
  171. _connection.AddColumn(Logger, "TypedBaseItems", "LocationType", "Text");
  172. _connection.AddColumn(Logger, "TypedBaseItems", "IsSeries", "BIT");
  173. _connection.AddColumn(Logger, "TypedBaseItems", "IsLive", "BIT");
  174. _connection.AddColumn(Logger, "TypedBaseItems", "IsNews", "BIT");
  175. _connection.AddColumn(Logger, "TypedBaseItems", "IsPremiere", "BIT");
  176. _connection.AddColumn(Logger, "TypedBaseItems", "EpisodeTitle", "Text");
  177. _connection.AddColumn(Logger, "TypedBaseItems", "IsRepeat", "BIT");
  178. _connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataLanguage", "Text");
  179. _connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataCountryCode", "Text");
  180. _connection.AddColumn(Logger, "TypedBaseItems", "IsHD", "BIT");
  181. _connection.AddColumn(Logger, "TypedBaseItems", "ExternalEtag", "Text");
  182. _connection.AddColumn(Logger, "TypedBaseItems", "DateLastRefreshed", "DATETIME");
  183. _connection.AddColumn(Logger, "TypedBaseItems", "DateLastSaved", "DATETIME");
  184. _connection.AddColumn(Logger, "TypedBaseItems", "IsInMixedFolder", "BIT");
  185. _connection.AddColumn(Logger, "TypedBaseItems", "LockedFields", "Text");
  186. _connection.AddColumn(Logger, "TypedBaseItems", "Studios", "Text");
  187. _connection.AddColumn(Logger, "TypedBaseItems", "Audio", "Text");
  188. _connection.AddColumn(Logger, "TypedBaseItems", "ExternalServiceId", "Text");
  189. _connection.AddColumn(Logger, "TypedBaseItems", "Tags", "Text");
  190. _connection.AddColumn(Logger, "TypedBaseItems", "IsFolder", "BIT");
  191. _connection.AddColumn(Logger, "TypedBaseItems", "InheritedParentalRatingValue", "INT");
  192. _connection.AddColumn(Logger, "TypedBaseItems", "UnratedType", "Text");
  193. _connection.AddColumn(Logger, "TypedBaseItems", "TopParentId", "Text");
  194. _connection.AddColumn(Logger, "TypedBaseItems", "IsItemByName", "BIT");
  195. _connection.AddColumn(Logger, "TypedBaseItems", "SourceType", "Text");
  196. _connection.AddColumn(Logger, "TypedBaseItems", "TrailerTypes", "Text");
  197. _connection.AddColumn(Logger, "TypedBaseItems", "CriticRating", "Float");
  198. _connection.AddColumn(Logger, "TypedBaseItems", "CriticRatingSummary", "Text");
  199. _connection.AddColumn(Logger, "TypedBaseItems", "DateModifiedDuringLastRefresh", "DATETIME");
  200. _connection.AddColumn(Logger, "TypedBaseItems", "InheritedTags", "Text");
  201. _connection.AddColumn(Logger, "TypedBaseItems", "CleanName", "Text");
  202. _connection.AddColumn(Logger, "TypedBaseItems", "PresentationUniqueKey", "Text");
  203. _connection.AddColumn(Logger, "TypedBaseItems", "SlugName", "Text");
  204. _connection.AddColumn(Logger, "TypedBaseItems", "OriginalTitle", "Text");
  205. _connection.AddColumn(Logger, "TypedBaseItems", "PrimaryVersionId", "Text");
  206. _connection.AddColumn(Logger, "TypedBaseItems", "DateLastMediaAdded", "DATETIME");
  207. _connection.AddColumn(Logger, "TypedBaseItems", "Album", "Text");
  208. _connection.AddColumn(Logger, "TypedBaseItems", "IsVirtualItem", "BIT");
  209. _connection.AddColumn(Logger, "TypedBaseItems", "SeriesName", "Text");
  210. _connection.AddColumn(Logger, "UserDataKeys", "Priority", "INT");
  211. string[] postQueries =
  212. {
  213. "create index if not exists idx_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)",
  214. "create index if not exists idx_Type on TypedBaseItems(Type)",
  215. "create index if not exists idx_TopParentId on TypedBaseItems(TopParentId)",
  216. "create index if not exists idx_TypeTopParentId on TypedBaseItems(Type,TopParentId)"
  217. };
  218. _connection.RunQueries(postQueries, Logger);
  219. PrepareStatements();
  220. new MediaStreamColumns(_connection, Logger).AddColumns();
  221. DataExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb");
  222. }
  223. private readonly string[] _retriveItemColumns =
  224. {
  225. "type",
  226. "data",
  227. "StartDate",
  228. "EndDate",
  229. "IsOffline",
  230. "ChannelId",
  231. "IsMovie",
  232. "IsSports",
  233. "IsKids",
  234. "IsSeries",
  235. "IsLive",
  236. "IsNews",
  237. "IsPremiere",
  238. "EpisodeTitle",
  239. "IsRepeat",
  240. "CommunityRating",
  241. "CustomRating",
  242. "IndexNumber",
  243. "IsLocked",
  244. "PreferredMetadataLanguage",
  245. "PreferredMetadataCountryCode",
  246. "IsHD",
  247. "ExternalEtag",
  248. "DateLastRefreshed",
  249. "Name",
  250. "Path",
  251. "PremiereDate",
  252. "Overview",
  253. "ParentIndexNumber",
  254. "ProductionYear",
  255. "OfficialRating",
  256. "OfficialRatingDescription",
  257. "HomePageUrl",
  258. "DisplayMediaType",
  259. "ForcedSortName",
  260. "RunTimeTicks",
  261. "VoteCount",
  262. "DateCreated",
  263. "DateModified",
  264. "guid",
  265. "Genres",
  266. "ParentId",
  267. "Audio",
  268. "ExternalServiceId",
  269. "IsInMixedFolder",
  270. "DateLastSaved",
  271. "LockedFields",
  272. "Studios",
  273. "Tags",
  274. "SourceType",
  275. "TrailerTypes",
  276. "DateModifiedDuringLastRefresh",
  277. "OriginalTitle",
  278. "PrimaryVersionId",
  279. "DateLastMediaAdded",
  280. "Album",
  281. "CriticRating",
  282. "CriticRatingSummary",
  283. "IsVirtualItem"
  284. };
  285. private readonly string[] _mediaStreamSaveColumns =
  286. {
  287. "ItemId",
  288. "StreamIndex",
  289. "StreamType",
  290. "Codec",
  291. "Language",
  292. "ChannelLayout",
  293. "Profile",
  294. "AspectRatio",
  295. "Path",
  296. "IsInterlaced",
  297. "BitRate",
  298. "Channels",
  299. "SampleRate",
  300. "IsDefault",
  301. "IsForced",
  302. "IsExternal",
  303. "Height",
  304. "Width",
  305. "AverageFrameRate",
  306. "RealFrameRate",
  307. "Level",
  308. "PixelFormat",
  309. "BitDepth",
  310. "IsAnamorphic",
  311. "RefFrames",
  312. "CodecTag",
  313. "Comment",
  314. "NalLengthSize",
  315. "IsAvc",
  316. "Title",
  317. "TimeBase",
  318. "CodecTimeBase"
  319. };
  320. /// <summary>
  321. /// Prepares the statements.
  322. /// </summary>
  323. private void PrepareStatements()
  324. {
  325. var saveColumns = new List<string>
  326. {
  327. "guid",
  328. "type",
  329. "data",
  330. "Path",
  331. "StartDate",
  332. "EndDate",
  333. "ChannelId",
  334. "IsKids",
  335. "IsMovie",
  336. "IsSports",
  337. "IsSeries",
  338. "IsLive",
  339. "IsNews",
  340. "IsPremiere",
  341. "EpisodeTitle",
  342. "IsRepeat",
  343. "CommunityRating",
  344. "CustomRating",
  345. "IndexNumber",
  346. "IsLocked",
  347. "Name",
  348. "OfficialRating",
  349. "MediaType",
  350. "Overview",
  351. "ParentIndexNumber",
  352. "PremiereDate",
  353. "ProductionYear",
  354. "ParentId",
  355. "Genres",
  356. "ParentalRatingValue",
  357. "InheritedParentalRatingValue",
  358. "SchemaVersion",
  359. "SortName",
  360. "RunTimeTicks",
  361. "OfficialRatingDescription",
  362. "HomePageUrl",
  363. "VoteCount",
  364. "DisplayMediaType",
  365. "DateCreated",
  366. "DateModified",
  367. "ForcedSortName",
  368. "IsOffline",
  369. "LocationType",
  370. "PreferredMetadataLanguage",
  371. "PreferredMetadataCountryCode",
  372. "IsHD",
  373. "ExternalEtag",
  374. "DateLastRefreshed",
  375. "DateLastSaved",
  376. "IsInMixedFolder",
  377. "LockedFields",
  378. "Studios",
  379. "Audio",
  380. "ExternalServiceId",
  381. "Tags",
  382. "IsFolder",
  383. "UnratedType",
  384. "TopParentId",
  385. "IsItemByName",
  386. "SourceType",
  387. "TrailerTypes",
  388. "CriticRating",
  389. "CriticRatingSummary",
  390. "DateModifiedDuringLastRefresh",
  391. "InheritedTags",
  392. "CleanName",
  393. "PresentationUniqueKey",
  394. "SlugName",
  395. "OriginalTitle",
  396. "PrimaryVersionId",
  397. "DateLastMediaAdded",
  398. "Album",
  399. "IsVirtualItem",
  400. "SeriesName"
  401. };
  402. _saveItemCommand = _connection.CreateCommand();
  403. _saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values (";
  404. for (var i = 1; i <= saveColumns.Count; i++)
  405. {
  406. if (i > 1)
  407. {
  408. _saveItemCommand.CommandText += ",";
  409. }
  410. _saveItemCommand.CommandText += "@" + i.ToString(CultureInfo.InvariantCulture);
  411. _saveItemCommand.Parameters.Add(_saveItemCommand, "@" + i.ToString(CultureInfo.InvariantCulture));
  412. }
  413. _saveItemCommand.CommandText += ")";
  414. _deleteItemCommand = _connection.CreateCommand();
  415. _deleteItemCommand.CommandText = "delete from TypedBaseItems where guid=@Id";
  416. _deleteItemCommand.Parameters.Add(_deleteItemCommand, "@Id");
  417. // People
  418. _deletePeopleCommand = _connection.CreateCommand();
  419. _deletePeopleCommand.CommandText = "delete from People where ItemId=@Id";
  420. _deletePeopleCommand.Parameters.Add(_deletePeopleCommand, "@Id");
  421. _savePersonCommand = _connection.CreateCommand();
  422. _savePersonCommand.CommandText = "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values (@ItemId, @Name, @Role, @PersonType, @SortOrder, @ListOrder)";
  423. _savePersonCommand.Parameters.Add(_savePersonCommand, "@ItemId");
  424. _savePersonCommand.Parameters.Add(_savePersonCommand, "@Name");
  425. _savePersonCommand.Parameters.Add(_savePersonCommand, "@Role");
  426. _savePersonCommand.Parameters.Add(_savePersonCommand, "@PersonType");
  427. _savePersonCommand.Parameters.Add(_savePersonCommand, "@SortOrder");
  428. _savePersonCommand.Parameters.Add(_savePersonCommand, "@ListOrder");
  429. // Ancestors
  430. _deleteAncestorsCommand = _connection.CreateCommand();
  431. _deleteAncestorsCommand.CommandText = "delete from AncestorIds where ItemId=@Id";
  432. _deleteAncestorsCommand.Parameters.Add(_deleteAncestorsCommand, "@Id");
  433. _saveAncestorCommand = _connection.CreateCommand();
  434. _saveAncestorCommand.CommandText = "insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values (@ItemId, @AncestorId, @AncestorIdText)";
  435. _saveAncestorCommand.Parameters.Add(_saveAncestorCommand, "@ItemId");
  436. _saveAncestorCommand.Parameters.Add(_saveAncestorCommand, "@AncestorId");
  437. _saveAncestorCommand.Parameters.Add(_saveAncestorCommand, "@AncestorIdText");
  438. // Chapters
  439. _deleteChaptersCommand = _connection.CreateCommand();
  440. _deleteChaptersCommand.CommandText = "delete from " + ChaptersTableName + " where ItemId=@ItemId";
  441. _deleteChaptersCommand.Parameters.Add(_deleteChaptersCommand, "@ItemId");
  442. _saveChapterCommand = _connection.CreateCommand();
  443. _saveChapterCommand.CommandText = "replace into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath) values (@ItemId, @ChapterIndex, @StartPositionTicks, @Name, @ImagePath)";
  444. _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ItemId");
  445. _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ChapterIndex");
  446. _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@StartPositionTicks");
  447. _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@Name");
  448. _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ImagePath");
  449. // MediaStreams
  450. _deleteStreamsCommand = _connection.CreateCommand();
  451. _deleteStreamsCommand.CommandText = "delete from mediastreams where ItemId=@ItemId";
  452. _deleteStreamsCommand.Parameters.Add(_deleteStreamsCommand, "@ItemId");
  453. _saveStreamCommand = _connection.CreateCommand();
  454. _saveStreamCommand.CommandText = string.Format("replace into mediastreams ({0}) values ({1})",
  455. string.Join(",", _mediaStreamSaveColumns),
  456. string.Join(",", _mediaStreamSaveColumns.Select(i => "@" + i).ToArray()));
  457. foreach (var col in _mediaStreamSaveColumns)
  458. {
  459. _saveStreamCommand.Parameters.Add(_saveStreamCommand, "@" + col);
  460. }
  461. _updateInheritedRatingCommand = _connection.CreateCommand();
  462. _updateInheritedRatingCommand.CommandText = "Update TypedBaseItems set InheritedParentalRatingValue=@InheritedParentalRatingValue where Guid=@Guid";
  463. _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@Guid");
  464. _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@InheritedParentalRatingValue");
  465. _updateInheritedTagsCommand = _connection.CreateCommand();
  466. _updateInheritedTagsCommand.CommandText = "Update TypedBaseItems set InheritedTags=@InheritedTags where Guid=@Guid";
  467. _updateInheritedTagsCommand.Parameters.Add(_updateInheritedTagsCommand, "@Guid");
  468. _updateInheritedTagsCommand.Parameters.Add(_updateInheritedTagsCommand, "@InheritedTags");
  469. // user data
  470. _deleteUserDataKeysCommand = _connection.CreateCommand();
  471. _deleteUserDataKeysCommand.CommandText = "delete from UserDataKeys where ItemId=@Id";
  472. _deleteUserDataKeysCommand.Parameters.Add(_deleteUserDataKeysCommand, "@Id");
  473. _saveUserDataKeysCommand = _connection.CreateCommand();
  474. _saveUserDataKeysCommand.CommandText = "insert into UserDataKeys (ItemId, UserDataKey, Priority) values (@ItemId, @UserDataKey, @Priority)";
  475. _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@ItemId");
  476. _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@UserDataKey");
  477. _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@Priority");
  478. // item values
  479. _deleteItemValuesCommand = _connection.CreateCommand();
  480. _deleteItemValuesCommand.CommandText = "delete from ItemValues where ItemId=@Id";
  481. _deleteItemValuesCommand.Parameters.Add(_deleteItemValuesCommand, "@Id");
  482. _saveItemValuesCommand = _connection.CreateCommand();
  483. _saveItemValuesCommand.CommandText = "insert into ItemValues (ItemId, Type, Value) values (@ItemId, @Type, @Value)";
  484. _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@ItemId");
  485. _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Type");
  486. _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Value");
  487. // provider ids
  488. _deleteProviderIdsCommand = _connection.CreateCommand();
  489. _deleteProviderIdsCommand.CommandText = "delete from ProviderIds where ItemId=@Id";
  490. _deleteProviderIdsCommand.Parameters.Add(_deleteProviderIdsCommand, "@Id");
  491. _saveProviderIdsCommand = _connection.CreateCommand();
  492. _saveProviderIdsCommand.CommandText = "insert into ProviderIds (ItemId, Name, Value) values (@ItemId, @Name, @Value)";
  493. _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@ItemId");
  494. _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@Name");
  495. _saveProviderIdsCommand.Parameters.Add(_saveProviderIdsCommand, "@Value");
  496. // images
  497. _deleteImagesCommand = _connection.CreateCommand();
  498. _deleteImagesCommand.CommandText = "delete from Images where ItemId=@Id";
  499. _deleteImagesCommand.Parameters.Add(_deleteImagesCommand, "@Id");
  500. _saveImagesCommand = _connection.CreateCommand();
  501. _saveImagesCommand.CommandText = "insert into Images (ItemId, ImageType, Path, DateModified, IsPlaceHolder, SortOrder) values (@ItemId, @ImageType, @Path, @DateModified, @IsPlaceHolder, @SortOrder)";
  502. _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@ItemId");
  503. _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@ImageType");
  504. _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@Path");
  505. _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@DateModified");
  506. _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@IsPlaceHolder");
  507. _saveImagesCommand.Parameters.Add(_saveImagesCommand, "@SortOrder");
  508. }
  509. /// <summary>
  510. /// Save a standard item in the repo
  511. /// </summary>
  512. /// <param name="item">The item.</param>
  513. /// <param name="cancellationToken">The cancellation token.</param>
  514. /// <returns>Task.</returns>
  515. /// <exception cref="System.ArgumentNullException">item</exception>
  516. public Task SaveItem(BaseItem item, CancellationToken cancellationToken)
  517. {
  518. if (item == null)
  519. {
  520. throw new ArgumentNullException("item");
  521. }
  522. return SaveItems(new[] { item }, cancellationToken);
  523. }
  524. /// <summary>
  525. /// Saves the items.
  526. /// </summary>
  527. /// <param name="items">The items.</param>
  528. /// <param name="cancellationToken">The cancellation token.</param>
  529. /// <returns>Task.</returns>
  530. /// <exception cref="System.ArgumentNullException">
  531. /// items
  532. /// or
  533. /// cancellationToken
  534. /// </exception>
  535. public async Task SaveItems(IEnumerable<BaseItem> items, CancellationToken cancellationToken)
  536. {
  537. if (items == null)
  538. {
  539. throw new ArgumentNullException("items");
  540. }
  541. cancellationToken.ThrowIfCancellationRequested();
  542. CheckDisposed();
  543. await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  544. IDbTransaction transaction = null;
  545. try
  546. {
  547. transaction = _connection.BeginTransaction();
  548. foreach (var item in items)
  549. {
  550. cancellationToken.ThrowIfCancellationRequested();
  551. var index = 0;
  552. _saveItemCommand.GetParameter(index++).Value = item.Id;
  553. _saveItemCommand.GetParameter(index++).Value = item.GetType().FullName;
  554. _saveItemCommand.GetParameter(index++).Value = _jsonSerializer.SerializeToBytes(item);
  555. _saveItemCommand.GetParameter(index++).Value = item.Path;
  556. var hasStartDate = item as IHasStartDate;
  557. if (hasStartDate != null)
  558. {
  559. _saveItemCommand.GetParameter(index++).Value = hasStartDate.StartDate;
  560. }
  561. else
  562. {
  563. _saveItemCommand.GetParameter(index++).Value = null;
  564. }
  565. _saveItemCommand.GetParameter(index++).Value = item.EndDate;
  566. _saveItemCommand.GetParameter(index++).Value = item.ChannelId;
  567. var hasProgramAttributes = item as IHasProgramAttributes;
  568. if (hasProgramAttributes != null)
  569. {
  570. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsKids;
  571. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsMovie;
  572. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSports;
  573. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSeries;
  574. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsLive;
  575. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsNews;
  576. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsPremiere;
  577. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.EpisodeTitle;
  578. _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsRepeat;
  579. }
  580. else
  581. {
  582. _saveItemCommand.GetParameter(index++).Value = null;
  583. _saveItemCommand.GetParameter(index++).Value = null;
  584. _saveItemCommand.GetParameter(index++).Value = null;
  585. _saveItemCommand.GetParameter(index++).Value = null;
  586. _saveItemCommand.GetParameter(index++).Value = null;
  587. _saveItemCommand.GetParameter(index++).Value = null;
  588. _saveItemCommand.GetParameter(index++).Value = null;
  589. _saveItemCommand.GetParameter(index++).Value = null;
  590. _saveItemCommand.GetParameter(index++).Value = null;
  591. }
  592. _saveItemCommand.GetParameter(index++).Value = item.CommunityRating;
  593. _saveItemCommand.GetParameter(index++).Value = item.CustomRating;
  594. _saveItemCommand.GetParameter(index++).Value = item.IndexNumber;
  595. _saveItemCommand.GetParameter(index++).Value = item.IsLocked;
  596. _saveItemCommand.GetParameter(index++).Value = item.Name;
  597. _saveItemCommand.GetParameter(index++).Value = item.OfficialRating;
  598. _saveItemCommand.GetParameter(index++).Value = item.MediaType;
  599. _saveItemCommand.GetParameter(index++).Value = item.Overview;
  600. _saveItemCommand.GetParameter(index++).Value = item.ParentIndexNumber;
  601. _saveItemCommand.GetParameter(index++).Value = item.PremiereDate;
  602. _saveItemCommand.GetParameter(index++).Value = item.ProductionYear;
  603. if (item.ParentId == Guid.Empty)
  604. {
  605. _saveItemCommand.GetParameter(index++).Value = null;
  606. }
  607. else
  608. {
  609. _saveItemCommand.GetParameter(index++).Value = item.ParentId;
  610. }
  611. _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Genres.ToArray());
  612. _saveItemCommand.GetParameter(index++).Value = item.GetParentalRatingValue() ?? 0;
  613. _saveItemCommand.GetParameter(index++).Value = item.GetInheritedParentalRatingValue() ?? 0;
  614. _saveItemCommand.GetParameter(index++).Value = LatestSchemaVersion;
  615. _saveItemCommand.GetParameter(index++).Value = item.SortName;
  616. _saveItemCommand.GetParameter(index++).Value = item.RunTimeTicks;
  617. _saveItemCommand.GetParameter(index++).Value = item.OfficialRatingDescription;
  618. _saveItemCommand.GetParameter(index++).Value = item.HomePageUrl;
  619. _saveItemCommand.GetParameter(index++).Value = item.VoteCount;
  620. _saveItemCommand.GetParameter(index++).Value = item.DisplayMediaType;
  621. _saveItemCommand.GetParameter(index++).Value = item.DateCreated;
  622. _saveItemCommand.GetParameter(index++).Value = item.DateModified;
  623. _saveItemCommand.GetParameter(index++).Value = item.ForcedSortName;
  624. _saveItemCommand.GetParameter(index++).Value = item.IsOffline;
  625. _saveItemCommand.GetParameter(index++).Value = item.LocationType.ToString();
  626. _saveItemCommand.GetParameter(index++).Value = item.PreferredMetadataLanguage;
  627. _saveItemCommand.GetParameter(index++).Value = item.PreferredMetadataCountryCode;
  628. _saveItemCommand.GetParameter(index++).Value = item.IsHD;
  629. _saveItemCommand.GetParameter(index++).Value = item.ExternalEtag;
  630. if (item.DateLastRefreshed == default(DateTime))
  631. {
  632. _saveItemCommand.GetParameter(index++).Value = null;
  633. }
  634. else
  635. {
  636. _saveItemCommand.GetParameter(index++).Value = item.DateLastRefreshed;
  637. }
  638. if (item.DateLastSaved == default(DateTime))
  639. {
  640. _saveItemCommand.GetParameter(index++).Value = null;
  641. }
  642. else
  643. {
  644. _saveItemCommand.GetParameter(index++).Value = item.DateLastSaved;
  645. }
  646. _saveItemCommand.GetParameter(index++).Value = item.IsInMixedFolder;
  647. _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.LockedFields.Select(i => i.ToString()).ToArray());
  648. _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Studios.ToArray());
  649. if (item.Audio.HasValue)
  650. {
  651. _saveItemCommand.GetParameter(index++).Value = item.Audio.Value.ToString();
  652. }
  653. else
  654. {
  655. _saveItemCommand.GetParameter(index++).Value = null;
  656. }
  657. _saveItemCommand.GetParameter(index++).Value = item.ServiceName;
  658. if (item.Tags.Count > 0)
  659. {
  660. _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Tags.ToArray());
  661. }
  662. else
  663. {
  664. _saveItemCommand.GetParameter(index++).Value = null;
  665. }
  666. _saveItemCommand.GetParameter(index++).Value = item.IsFolder;
  667. _saveItemCommand.GetParameter(index++).Value = item.GetBlockUnratedType().ToString();
  668. var topParent = item.GetTopParent();
  669. if (topParent != null)
  670. {
  671. //Logger.Debug("Item {0} has top parent {1}", item.Id, topParent.Id);
  672. _saveItemCommand.GetParameter(index++).Value = topParent.Id.ToString("N");
  673. }
  674. else
  675. {
  676. //Logger.Debug("Item {0} has null top parent", item.Id);
  677. _saveItemCommand.GetParameter(index++).Value = null;
  678. }
  679. var isByName = false;
  680. var byName = item as IItemByName;
  681. if (byName != null)
  682. {
  683. var dualAccess = item as IHasDualAccess;
  684. isByName = dualAccess == null || dualAccess.IsAccessedByName;
  685. }
  686. _saveItemCommand.GetParameter(index++).Value = isByName;
  687. _saveItemCommand.GetParameter(index++).Value = item.SourceType.ToString();
  688. var trailer = item as Trailer;
  689. if (trailer != null && trailer.TrailerTypes.Count > 0)
  690. {
  691. _saveItemCommand.GetParameter(index++).Value = string.Join("|", trailer.TrailerTypes.Select(i => i.ToString()).ToArray());
  692. }
  693. else
  694. {
  695. _saveItemCommand.GetParameter(index++).Value = null;
  696. }
  697. _saveItemCommand.GetParameter(index++).Value = item.CriticRating;
  698. _saveItemCommand.GetParameter(index++).Value = item.CriticRatingSummary;
  699. if (!item.DateModifiedDuringLastRefresh.HasValue || item.DateModifiedDuringLastRefresh.Value == default(DateTime))
  700. {
  701. _saveItemCommand.GetParameter(index++).Value = null;
  702. }
  703. else
  704. {
  705. _saveItemCommand.GetParameter(index++).Value = item.DateModifiedDuringLastRefresh.Value;
  706. }
  707. var inheritedTags = item.GetInheritedTags();
  708. if (inheritedTags.Count > 0)
  709. {
  710. _saveItemCommand.GetParameter(index++).Value = string.Join("|", inheritedTags.ToArray());
  711. }
  712. else
  713. {
  714. _saveItemCommand.GetParameter(index++).Value = null;
  715. }
  716. if (string.IsNullOrWhiteSpace(item.Name))
  717. {
  718. _saveItemCommand.GetParameter(index++).Value = null;
  719. }
  720. else
  721. {
  722. _saveItemCommand.GetParameter(index++).Value = item.Name.RemoveDiacritics();
  723. }
  724. _saveItemCommand.GetParameter(index++).Value = item.PresentationUniqueKey;
  725. _saveItemCommand.GetParameter(index++).Value = item.SlugName;
  726. _saveItemCommand.GetParameter(index++).Value = item.OriginalTitle;
  727. var video = item as Video;
  728. if (video != null)
  729. {
  730. _saveItemCommand.GetParameter(index++).Value = video.PrimaryVersionId;
  731. }
  732. else
  733. {
  734. _saveItemCommand.GetParameter(index++).Value = null;
  735. }
  736. var folder = item as Folder;
  737. if (folder != null && folder.DateLastMediaAdded.HasValue)
  738. {
  739. _saveItemCommand.GetParameter(index++).Value = folder.DateLastMediaAdded.Value;
  740. }
  741. else
  742. {
  743. _saveItemCommand.GetParameter(index++).Value = null;
  744. }
  745. _saveItemCommand.GetParameter(index++).Value = item.Album;
  746. var season = item as Season;
  747. if (season != null && season.IsVirtualItem.HasValue)
  748. {
  749. _saveItemCommand.GetParameter(index++).Value = season.IsVirtualItem.Value;
  750. }
  751. else
  752. {
  753. _saveItemCommand.GetParameter(index++).Value = null;
  754. }
  755. var hasSeries = item as IHasSeries;
  756. if (hasSeries != null)
  757. {
  758. _saveItemCommand.GetParameter(index++).Value = hasSeries.SeriesName;
  759. }
  760. else
  761. {
  762. _saveItemCommand.GetParameter(index++).Value = null;
  763. }
  764. _saveItemCommand.Transaction = transaction;
  765. _saveItemCommand.ExecuteNonQuery();
  766. if (item.SupportsAncestors)
  767. {
  768. UpdateAncestors(item.Id, item.GetAncestorIds().Distinct().ToList(), transaction);
  769. }
  770. UpdateUserDataKeys(item.Id, item.GetUserDataKeys().Distinct(StringComparer.OrdinalIgnoreCase).ToList(), transaction);
  771. UpdateImages(item.Id, item.ImageInfos, transaction);
  772. UpdateProviderIds(item.Id, item.ProviderIds, transaction);
  773. UpdateItemValues(item.Id, GetItemValues(item), transaction);
  774. }
  775. transaction.Commit();
  776. }
  777. catch (OperationCanceledException)
  778. {
  779. if (transaction != null)
  780. {
  781. transaction.Rollback();
  782. }
  783. throw;
  784. }
  785. catch (Exception e)
  786. {
  787. Logger.ErrorException("Failed to save items:", e);
  788. if (transaction != null)
  789. {
  790. transaction.Rollback();
  791. }
  792. throw;
  793. }
  794. finally
  795. {
  796. if (transaction != null)
  797. {
  798. transaction.Dispose();
  799. }
  800. WriteLock.Release();
  801. }
  802. }
  803. /// <summary>
  804. /// Internal retrieve from items or users table
  805. /// </summary>
  806. /// <param name="id">The id.</param>
  807. /// <returns>BaseItem.</returns>
  808. /// <exception cref="System.ArgumentNullException">id</exception>
  809. /// <exception cref="System.ArgumentException"></exception>
  810. public BaseItem RetrieveItem(Guid id)
  811. {
  812. if (id == Guid.Empty)
  813. {
  814. throw new ArgumentNullException("id");
  815. }
  816. CheckDisposed();
  817. using (var cmd = _connection.CreateCommand())
  818. {
  819. cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid";
  820. cmd.Parameters.Add(cmd, "@guid", DbType.Guid).Value = id;
  821. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
  822. {
  823. if (reader.Read())
  824. {
  825. return GetItem(reader);
  826. }
  827. }
  828. return null;
  829. }
  830. }
  831. private BaseItem GetItem(IDataReader reader)
  832. {
  833. var typeString = reader.GetString(0);
  834. var type = _typeMapper.GetType(typeString);
  835. if (type == null)
  836. {
  837. //Logger.Debug("Unknown type {0}", typeString);
  838. return null;
  839. }
  840. BaseItem item = null;
  841. using (var stream = reader.GetMemoryStream(1))
  842. {
  843. try
  844. {
  845. item = _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem;
  846. }
  847. catch (SerializationException ex)
  848. {
  849. Logger.ErrorException("Error deserializing item", ex);
  850. }
  851. if (item == null)
  852. {
  853. try
  854. {
  855. item = Activator.CreateInstance(type) as BaseItem;
  856. }
  857. catch
  858. {
  859. }
  860. }
  861. if (item == null)
  862. {
  863. return null;
  864. }
  865. }
  866. if (!reader.IsDBNull(2))
  867. {
  868. var hasStartDate = item as IHasStartDate;
  869. if (hasStartDate != null)
  870. {
  871. hasStartDate.StartDate = reader.GetDateTime(2).ToUniversalTime();
  872. }
  873. }
  874. if (!reader.IsDBNull(3))
  875. {
  876. item.EndDate = reader.GetDateTime(3).ToUniversalTime();
  877. }
  878. if (!reader.IsDBNull(4))
  879. {
  880. item.IsOffline = reader.GetBoolean(4);
  881. }
  882. if (!reader.IsDBNull(5))
  883. {
  884. item.ChannelId = reader.GetString(5);
  885. }
  886. var hasProgramAttributes = item as IHasProgramAttributes;
  887. if (hasProgramAttributes != null)
  888. {
  889. if (!reader.IsDBNull(6))
  890. {
  891. hasProgramAttributes.IsMovie = reader.GetBoolean(6);
  892. }
  893. if (!reader.IsDBNull(7))
  894. {
  895. hasProgramAttributes.IsSports = reader.GetBoolean(7);
  896. }
  897. if (!reader.IsDBNull(8))
  898. {
  899. hasProgramAttributes.IsKids = reader.GetBoolean(8);
  900. }
  901. if (!reader.IsDBNull(9))
  902. {
  903. hasProgramAttributes.IsSeries = reader.GetBoolean(9);
  904. }
  905. if (!reader.IsDBNull(10))
  906. {
  907. hasProgramAttributes.IsLive = reader.GetBoolean(10);
  908. }
  909. if (!reader.IsDBNull(11))
  910. {
  911. hasProgramAttributes.IsNews = reader.GetBoolean(11);
  912. }
  913. if (!reader.IsDBNull(12))
  914. {
  915. hasProgramAttributes.IsPremiere = reader.GetBoolean(12);
  916. }
  917. if (!reader.IsDBNull(13))
  918. {
  919. hasProgramAttributes.EpisodeTitle = reader.GetString(13);
  920. }
  921. if (!reader.IsDBNull(14))
  922. {
  923. hasProgramAttributes.IsRepeat = reader.GetBoolean(14);
  924. }
  925. }
  926. if (!reader.IsDBNull(15))
  927. {
  928. item.CommunityRating = reader.GetFloat(15);
  929. }
  930. if (!reader.IsDBNull(16))
  931. {
  932. item.CustomRating = reader.GetString(16);
  933. }
  934. if (!reader.IsDBNull(17))
  935. {
  936. item.IndexNumber = reader.GetInt32(17);
  937. }
  938. if (!reader.IsDBNull(18))
  939. {
  940. item.IsLocked = reader.GetBoolean(18);
  941. }
  942. if (!reader.IsDBNull(19))
  943. {
  944. item.PreferredMetadataLanguage = reader.GetString(19);
  945. }
  946. if (!reader.IsDBNull(20))
  947. {
  948. item.PreferredMetadataCountryCode = reader.GetString(20);
  949. }
  950. if (!reader.IsDBNull(21))
  951. {
  952. item.IsHD = reader.GetBoolean(21);
  953. }
  954. if (!reader.IsDBNull(22))
  955. {
  956. item.ExternalEtag = reader.GetString(22);
  957. }
  958. if (!reader.IsDBNull(23))
  959. {
  960. item.DateLastRefreshed = reader.GetDateTime(23).ToUniversalTime();
  961. }
  962. if (!reader.IsDBNull(24))
  963. {
  964. item.Name = reader.GetString(24);
  965. }
  966. if (!reader.IsDBNull(25))
  967. {
  968. item.Path = reader.GetString(25);
  969. }
  970. if (!reader.IsDBNull(26))
  971. {
  972. item.PremiereDate = reader.GetDateTime(26).ToUniversalTime();
  973. }
  974. if (!reader.IsDBNull(27))
  975. {
  976. item.Overview = reader.GetString(27);
  977. }
  978. if (!reader.IsDBNull(28))
  979. {
  980. item.ParentIndexNumber = reader.GetInt32(28);
  981. }
  982. if (!reader.IsDBNull(29))
  983. {
  984. item.ProductionYear = reader.GetInt32(29);
  985. }
  986. if (!reader.IsDBNull(30))
  987. {
  988. item.OfficialRating = reader.GetString(30);
  989. }
  990. if (!reader.IsDBNull(31))
  991. {
  992. item.OfficialRatingDescription = reader.GetString(31);
  993. }
  994. if (!reader.IsDBNull(32))
  995. {
  996. item.HomePageUrl = reader.GetString(32);
  997. }
  998. if (!reader.IsDBNull(33))
  999. {
  1000. item.DisplayMediaType = reader.GetString(33);
  1001. }
  1002. if (!reader.IsDBNull(34))
  1003. {
  1004. item.ForcedSortName = reader.GetString(34);
  1005. }
  1006. if (!reader.IsDBNull(35))
  1007. {
  1008. item.RunTimeTicks = reader.GetInt64(35);
  1009. }
  1010. if (!reader.IsDBNull(36))
  1011. {
  1012. item.VoteCount = reader.GetInt32(36);
  1013. }
  1014. if (!reader.IsDBNull(37))
  1015. {
  1016. item.DateCreated = reader.GetDateTime(37).ToUniversalTime();
  1017. }
  1018. if (!reader.IsDBNull(38))
  1019. {
  1020. item.DateModified = reader.GetDateTime(38).ToUniversalTime();
  1021. }
  1022. item.Id = reader.GetGuid(39);
  1023. if (!reader.IsDBNull(40))
  1024. {
  1025. item.Genres = reader.GetString(40).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList();
  1026. }
  1027. if (!reader.IsDBNull(41))
  1028. {
  1029. item.ParentId = reader.GetGuid(41);
  1030. }
  1031. if (!reader.IsDBNull(42))
  1032. {
  1033. item.Audio = (ProgramAudio)Enum.Parse(typeof(ProgramAudio), reader.GetString(42), true);
  1034. }
  1035. if (!reader.IsDBNull(43))
  1036. {
  1037. item.ServiceName = reader.GetString(43);
  1038. }
  1039. if (!reader.IsDBNull(44))
  1040. {
  1041. item.IsInMixedFolder = reader.GetBoolean(44);
  1042. }
  1043. if (!reader.IsDBNull(45))
  1044. {
  1045. item.DateLastSaved = reader.GetDateTime(45).ToUniversalTime();
  1046. }
  1047. if (!reader.IsDBNull(46))
  1048. {
  1049. item.LockedFields = reader.GetString(46).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(i => (MetadataFields)Enum.Parse(typeof(MetadataFields), i, true)).ToList();
  1050. }
  1051. if (!reader.IsDBNull(47))
  1052. {
  1053. item.Studios = reader.GetString(47).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList();
  1054. }
  1055. if (!reader.IsDBNull(48))
  1056. {
  1057. item.Tags = reader.GetString(48).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList();
  1058. }
  1059. if (!reader.IsDBNull(49))
  1060. {
  1061. item.SourceType = (SourceType)Enum.Parse(typeof(SourceType), reader.GetString(49), true);
  1062. }
  1063. var trailer = item as Trailer;
  1064. if (trailer != null)
  1065. {
  1066. if (!reader.IsDBNull(50))
  1067. {
  1068. trailer.TrailerTypes = reader.GetString(50).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(i => (TrailerType)Enum.Parse(typeof(TrailerType), i, true)).ToList();
  1069. }
  1070. }
  1071. if (!reader.IsDBNull(51))
  1072. {
  1073. item.DateModifiedDuringLastRefresh = reader.GetDateTime(51).ToUniversalTime();
  1074. }
  1075. if (!reader.IsDBNull(52))
  1076. {
  1077. item.OriginalTitle = reader.GetString(52);
  1078. }
  1079. var video = item as Video;
  1080. if (video != null)
  1081. {
  1082. if (!reader.IsDBNull(53))
  1083. {
  1084. video.PrimaryVersionId = reader.GetString(53);
  1085. }
  1086. }
  1087. var folder = item as Folder;
  1088. if (folder != null && !reader.IsDBNull(54))
  1089. {
  1090. folder.DateLastMediaAdded = reader.GetDateTime(54).ToUniversalTime();
  1091. }
  1092. if (!reader.IsDBNull(55))
  1093. {
  1094. item.Album = reader.GetString(55);
  1095. }
  1096. if (!reader.IsDBNull(56))
  1097. {
  1098. item.CriticRating = reader.GetFloat(56);
  1099. }
  1100. if (!reader.IsDBNull(57))
  1101. {
  1102. item.CriticRatingSummary = reader.GetString(57);
  1103. }
  1104. var season = item as Season;
  1105. if (season != null && !reader.IsDBNull(58))
  1106. {
  1107. season.IsVirtualItem = reader.GetBoolean(58);
  1108. }
  1109. return item;
  1110. }
  1111. /// <summary>
  1112. /// Gets the critic reviews.
  1113. /// </summary>
  1114. /// <param name="itemId">The item id.</param>
  1115. /// <returns>Task{IEnumerable{ItemReview}}.</returns>
  1116. public IEnumerable<ItemReview> GetCriticReviews(Guid itemId)
  1117. {
  1118. try
  1119. {
  1120. var path = Path.Combine(_criticReviewsPath, itemId + ".json");
  1121. return _jsonSerializer.DeserializeFromFile<List<ItemReview>>(path);
  1122. }
  1123. catch (DirectoryNotFoundException)
  1124. {
  1125. return new List<ItemReview>();
  1126. }
  1127. catch (FileNotFoundException)
  1128. {
  1129. return new List<ItemReview>();
  1130. }
  1131. }
  1132. private readonly Task _cachedTask = Task.FromResult(true);
  1133. /// <summary>
  1134. /// Saves the critic reviews.
  1135. /// </summary>
  1136. /// <param name="itemId">The item id.</param>
  1137. /// <param name="criticReviews">The critic reviews.</param>
  1138. /// <returns>Task.</returns>
  1139. public Task SaveCriticReviews(Guid itemId, IEnumerable<ItemReview> criticReviews)
  1140. {
  1141. Directory.CreateDirectory(_criticReviewsPath);
  1142. var path = Path.Combine(_criticReviewsPath, itemId + ".json");
  1143. _jsonSerializer.SerializeToFile(criticReviews.ToList(), path);
  1144. return _cachedTask;
  1145. }
  1146. /// <summary>
  1147. /// Gets chapters for an item
  1148. /// </summary>
  1149. /// <param name="id">The id.</param>
  1150. /// <returns>IEnumerable{ChapterInfo}.</returns>
  1151. /// <exception cref="System.ArgumentNullException">id</exception>
  1152. public IEnumerable<ChapterInfo> GetChapters(Guid id)
  1153. {
  1154. CheckDisposed();
  1155. if (id == Guid.Empty)
  1156. {
  1157. throw new ArgumentNullException("id");
  1158. }
  1159. var list = new List<ChapterInfo>();
  1160. using (var cmd = _connection.CreateCommand())
  1161. {
  1162. cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc";
  1163. cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id;
  1164. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  1165. {
  1166. while (reader.Read())
  1167. {
  1168. list.Add(GetChapter(reader));
  1169. }
  1170. }
  1171. }
  1172. return list;
  1173. }
  1174. /// <summary>
  1175. /// Gets a single chapter for an item
  1176. /// </summary>
  1177. /// <param name="id">The id.</param>
  1178. /// <param name="index">The index.</param>
  1179. /// <returns>ChapterInfo.</returns>
  1180. /// <exception cref="System.ArgumentNullException">id</exception>
  1181. public ChapterInfo GetChapter(Guid id, int index)
  1182. {
  1183. CheckDisposed();
  1184. if (id == Guid.Empty)
  1185. {
  1186. throw new ArgumentNullException("id");
  1187. }
  1188. using (var cmd = _connection.CreateCommand())
  1189. {
  1190. cmd.CommandText = "select StartPositionTicks,Name,ImagePath from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex";
  1191. cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = id;
  1192. cmd.Parameters.Add(cmd, "@ChapterIndex", DbType.Int32).Value = index;
  1193. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
  1194. {
  1195. if (reader.Read())
  1196. {
  1197. return GetChapter(reader);
  1198. }
  1199. }
  1200. return null;
  1201. }
  1202. }
  1203. /// <summary>
  1204. /// Gets the chapter.
  1205. /// </summary>
  1206. /// <param name="reader">The reader.</param>
  1207. /// <returns>ChapterInfo.</returns>
  1208. private ChapterInfo GetChapter(IDataReader reader)
  1209. {
  1210. var chapter = new ChapterInfo
  1211. {
  1212. StartPositionTicks = reader.GetInt64(0)
  1213. };
  1214. if (!reader.IsDBNull(1))
  1215. {
  1216. chapter.Name = reader.GetString(1);
  1217. }
  1218. if (!reader.IsDBNull(2))
  1219. {
  1220. chapter.ImagePath = reader.GetString(2);
  1221. }
  1222. return chapter;
  1223. }
  1224. /// <summary>
  1225. /// Saves the chapters.
  1226. /// </summary>
  1227. /// <param name="id">The id.</param>
  1228. /// <param name="chapters">The chapters.</param>
  1229. /// <param name="cancellationToken">The cancellation token.</param>
  1230. /// <returns>Task.</returns>
  1231. /// <exception cref="System.ArgumentNullException">
  1232. /// id
  1233. /// or
  1234. /// chapters
  1235. /// or
  1236. /// cancellationToken
  1237. /// </exception>
  1238. public async Task SaveChapters(Guid id, IEnumerable<ChapterInfo> chapters, CancellationToken cancellationToken)
  1239. {
  1240. CheckDisposed();
  1241. if (id == Guid.Empty)
  1242. {
  1243. throw new ArgumentNullException("id");
  1244. }
  1245. if (chapters == null)
  1246. {
  1247. throw new ArgumentNullException("chapters");
  1248. }
  1249. cancellationToken.ThrowIfCancellationRequested();
  1250. await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  1251. IDbTransaction transaction = null;
  1252. try
  1253. {
  1254. transaction = _connection.BeginTransaction();
  1255. // First delete chapters
  1256. _deleteChaptersCommand.GetParameter(0).Value = id;
  1257. _deleteChaptersCommand.Transaction = transaction;
  1258. _deleteChaptersCommand.ExecuteNonQuery();
  1259. var index = 0;
  1260. foreach (var chapter in chapters)
  1261. {
  1262. cancellationToken.ThrowIfCancellationRequested();
  1263. _saveChapterCommand.GetParameter(0).Value = id;
  1264. _saveChapterCommand.GetParameter(1).Value = index;
  1265. _saveChapterCommand.GetParameter(2).Value = chapter.StartPositionTicks;
  1266. _saveChapterCommand.GetParameter(3).Value = chapter.Name;
  1267. _saveChapterCommand.GetParameter(4).Value = chapter.ImagePath;
  1268. _saveChapterCommand.Transaction = transaction;
  1269. _saveChapterCommand.ExecuteNonQuery();
  1270. index++;
  1271. }
  1272. transaction.Commit();
  1273. }
  1274. catch (OperationCanceledException)
  1275. {
  1276. if (transaction != null)
  1277. {
  1278. transaction.Rollback();
  1279. }
  1280. throw;
  1281. }
  1282. catch (Exception e)
  1283. {
  1284. Logger.ErrorException("Failed to save chapters:", e);
  1285. if (transaction != null)
  1286. {
  1287. transaction.Rollback();
  1288. }
  1289. throw;
  1290. }
  1291. finally
  1292. {
  1293. if (transaction != null)
  1294. {
  1295. transaction.Dispose();
  1296. }
  1297. WriteLock.Release();
  1298. }
  1299. }
  1300. protected override void CloseConnection()
  1301. {
  1302. if (_connection != null)
  1303. {
  1304. if (_connection.IsOpen())
  1305. {
  1306. _connection.Close();
  1307. }
  1308. _connection.Dispose();
  1309. _connection = null;
  1310. }
  1311. }
  1312. private bool EnableJoinUserData(InternalItemsQuery query)
  1313. {
  1314. if (query.User == null)
  1315. {
  1316. return false;
  1317. }
  1318. if (query.SimilarTo != null)
  1319. {
  1320. return true;
  1321. }
  1322. if (query.SortBy != null && query.SortBy.Length > 0)
  1323. {
  1324. if (query.SortBy.Contains(ItemSortBy.IsFavoriteOrLiked, StringComparer.OrdinalIgnoreCase))
  1325. {
  1326. return true;
  1327. }
  1328. if (query.SortBy.Contains(ItemSortBy.IsPlayed, StringComparer.OrdinalIgnoreCase))
  1329. {
  1330. return true;
  1331. }
  1332. if (query.SortBy.Contains(ItemSortBy.IsUnplayed, StringComparer.OrdinalIgnoreCase))
  1333. {
  1334. return true;
  1335. }
  1336. if (query.SortBy.Contains(ItemSortBy.PlayCount, StringComparer.OrdinalIgnoreCase))
  1337. {
  1338. return true;
  1339. }
  1340. if (query.SortBy.Contains(ItemSortBy.DatePlayed, StringComparer.OrdinalIgnoreCase))
  1341. {
  1342. return true;
  1343. }
  1344. }
  1345. if (query.IsFavoriteOrLiked.HasValue)
  1346. {
  1347. return true;
  1348. }
  1349. if (query.IsFavorite.HasValue)
  1350. {
  1351. return true;
  1352. }
  1353. if (query.IsResumable.HasValue)
  1354. {
  1355. return true;
  1356. }
  1357. if (query.IsPlayed.HasValue)
  1358. {
  1359. return true;
  1360. }
  1361. if (query.IsLiked.HasValue)
  1362. {
  1363. return true;
  1364. }
  1365. return false;
  1366. }
  1367. private string[] GetFinalColumnsToSelect(InternalItemsQuery query, string[] startColumns, IDbCommand cmd)
  1368. {
  1369. var list = startColumns.ToList();
  1370. if (EnableJoinUserData(query))
  1371. {
  1372. list.Add("UserDataDb.UserData.UserId");
  1373. list.Add("UserDataDb.UserData.lastPlayedDate");
  1374. list.Add("UserDataDb.UserData.playbackPositionTicks");
  1375. list.Add("UserDataDb.UserData.playcount");
  1376. list.Add("UserDataDb.UserData.isFavorite");
  1377. list.Add("UserDataDb.UserData.played");
  1378. list.Add("UserDataDb.UserData.rating");
  1379. }
  1380. if (query.SimilarTo != null)
  1381. {
  1382. var item = query.SimilarTo;
  1383. var builder = new StringBuilder();
  1384. builder.Append("(");
  1385. builder.Append("((OfficialRating=@ItemOfficialRating) * 10)");
  1386. //builder.Append("+ ((ProductionYear=@ItemProductionYear) * 10)");
  1387. builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 10 Then 2 Else 0 End )");
  1388. builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 2 Else 0 End )");
  1389. //// genres
  1390. builder.Append("+ ((Select count(value) from ItemValues where ItemId=Guid and Type=2 and value in (select value from itemvalues where ItemId=@SimilarItemId and type=2)) * 10)");
  1391. //// tags
  1392. builder.Append("+ ((Select count(value) from ItemValues where ItemId=Guid and Type=4 and value in (select value from itemvalues where ItemId=@SimilarItemId and type=4)) * 10)");
  1393. builder.Append("+ ((Select count(value) from ItemValues where ItemId=Guid and Type=5 and value in (select value from itemvalues where ItemId=@SimilarItemId and type=5)) * 10)");
  1394. builder.Append("+ ((Select count(value) from ItemValues where ItemId=Guid and Type=3 and value in (select value from itemvalues where ItemId=@SimilarItemId and type=3)) * 3)");
  1395. //builder.Append("+ ((Select count(Name) from People where ItemId=Guid and Name in (select Name from People where ItemId=@SimilarItemId)) * 3)");
  1396. ////builder.Append("(select group_concat((Select Name from People where ItemId=Guid and Name in (Select Name from People where ItemId=@SimilarItemId)), '|'))");
  1397. builder.Append(") as SimilarityScore");
  1398. list.Add(builder.ToString());
  1399. cmd.Parameters.Add(cmd, "@ItemOfficialRating", DbType.String).Value = item.OfficialRating;
  1400. cmd.Parameters.Add(cmd, "@ItemProductionYear", DbType.Int32).Value = item.ProductionYear ?? 0;
  1401. cmd.Parameters.Add(cmd, "@SimilarItemId", DbType.Guid).Value = item.Id;
  1402. var excludeIds = query.ExcludeItemIds.ToList();
  1403. excludeIds.Add(item.Id.ToString("N"));
  1404. query.ExcludeItemIds = excludeIds.ToArray();
  1405. query.ExcludeProviderIds = item.ProviderIds;
  1406. }
  1407. return list.ToArray();
  1408. }
  1409. private string GetJoinUserDataText(InternalItemsQuery query)
  1410. {
  1411. if (!EnableJoinUserData(query))
  1412. {
  1413. return string.Empty;
  1414. }
  1415. return " left join UserDataDb.UserData on (select UserDataKey from UserDataKeys where ItemId=Guid order by Priority LIMIT 1)=UserDataDb.UserData.Key";
  1416. }
  1417. public IEnumerable<BaseItem> GetItemList(InternalItemsQuery query)
  1418. {
  1419. if (query == null)
  1420. {
  1421. throw new ArgumentNullException("query");
  1422. }
  1423. CheckDisposed();
  1424. var now = DateTime.UtcNow;
  1425. using (var cmd = _connection.CreateCommand())
  1426. {
  1427. cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems";
  1428. cmd.CommandText += GetJoinUserDataText(query);
  1429. if (EnableJoinUserData(query))
  1430. {
  1431. cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id;
  1432. }
  1433. var whereClauses = GetWhereClauses(query, cmd);
  1434. var whereText = whereClauses.Count == 0 ?
  1435. string.Empty :
  1436. " where " + string.Join(" AND ", whereClauses.ToArray());
  1437. cmd.CommandText += whereText;
  1438. if (EnableGroupByPresentationUniqueKey(query))
  1439. {
  1440. cmd.CommandText += " Group by PresentationUniqueKey";
  1441. }
  1442. cmd.CommandText += GetOrderByText(query);
  1443. if (query.Limit.HasValue || query.StartIndex.HasValue)
  1444. {
  1445. var limit = query.Limit ?? int.MaxValue;
  1446. cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture);
  1447. if (query.StartIndex.HasValue)
  1448. {
  1449. cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture);
  1450. }
  1451. }
  1452. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  1453. {
  1454. LogQueryTime("GetItemList", cmd, now);
  1455. while (reader.Read())
  1456. {
  1457. var item = GetItem(reader);
  1458. if (item != null)
  1459. {
  1460. yield return item;
  1461. }
  1462. }
  1463. }
  1464. }
  1465. }
  1466. private void LogQueryTime(string methodName, IDbCommand cmd, DateTime startDate)
  1467. {
  1468. var elapsed = (DateTime.UtcNow - startDate).TotalMilliseconds;
  1469. var slowThreshold = 1000;
  1470. #if DEBUG
  1471. slowThreshold = 100;
  1472. #endif
  1473. if (elapsed >= slowThreshold)
  1474. {
  1475. Logger.Debug("{2} query time (slow): {0}ms. Query: {1}",
  1476. Convert.ToInt32(elapsed),
  1477. cmd.CommandText,
  1478. methodName);
  1479. }
  1480. else
  1481. {
  1482. //Logger.Debug("{2} query time: {0}ms. Query: {1}",
  1483. // Convert.ToInt32(elapsed),
  1484. // cmd.CommandText,
  1485. // methodName);
  1486. }
  1487. }
  1488. public QueryResult<BaseItem> GetItems(InternalItemsQuery query)
  1489. {
  1490. if (query == null)
  1491. {
  1492. throw new ArgumentNullException("query");
  1493. }
  1494. CheckDisposed();
  1495. var now = DateTime.UtcNow;
  1496. using (var cmd = _connection.CreateCommand())
  1497. {
  1498. cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems";
  1499. cmd.CommandText += GetJoinUserDataText(query);
  1500. if (EnableJoinUserData(query))
  1501. {
  1502. cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id;
  1503. }
  1504. var whereClauses = GetWhereClauses(query, cmd);
  1505. var whereTextWithoutPaging = whereClauses.Count == 0 ?
  1506. string.Empty :
  1507. " where " + string.Join(" AND ", whereClauses.ToArray());
  1508. var whereText = whereClauses.Count == 0 ?
  1509. string.Empty :
  1510. " where " + string.Join(" AND ", whereClauses.ToArray());
  1511. cmd.CommandText += whereText;
  1512. if (EnableGroupByPresentationUniqueKey(query))
  1513. {
  1514. cmd.CommandText += " Group by PresentationUniqueKey";
  1515. }
  1516. cmd.CommandText += GetOrderByText(query);
  1517. if (query.Limit.HasValue || query.StartIndex.HasValue)
  1518. {
  1519. var limit = query.Limit ?? int.MaxValue;
  1520. cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture);
  1521. if (query.StartIndex.HasValue)
  1522. {
  1523. cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture);
  1524. }
  1525. }
  1526. if (EnableGroupByPresentationUniqueKey(query))
  1527. {
  1528. cmd.CommandText += "; select count (distinct PresentationUniqueKey) from TypedBaseItems";
  1529. }
  1530. else
  1531. {
  1532. cmd.CommandText += "; select count (guid) from TypedBaseItems";
  1533. }
  1534. cmd.CommandText += GetJoinUserDataText(query);
  1535. cmd.CommandText += whereTextWithoutPaging;
  1536. var list = new List<BaseItem>();
  1537. var count = 0;
  1538. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
  1539. {
  1540. LogQueryTime("GetItems", cmd, now);
  1541. while (reader.Read())
  1542. {
  1543. var item = GetItem(reader);
  1544. if (item != null)
  1545. {
  1546. list.Add(item);
  1547. }
  1548. }
  1549. if (reader.NextResult() && reader.Read())
  1550. {
  1551. count = reader.GetInt32(0);
  1552. }
  1553. }
  1554. return new QueryResult<BaseItem>()
  1555. {
  1556. Items = list.ToArray(),
  1557. TotalRecordCount = count
  1558. };
  1559. }
  1560. }
  1561. private string GetOrderByText(InternalItemsQuery query)
  1562. {
  1563. if (query.SimilarTo != null)
  1564. {
  1565. if (query.SortBy == null || query.SortBy.Length == 0)
  1566. {
  1567. if (query.User != null)
  1568. {
  1569. query.SortBy = new[] { "SimilarityScore", "IsPlayed", "Random" };
  1570. }
  1571. else
  1572. {
  1573. query.SortBy = new[] { "SimilarityScore", "Random" };
  1574. }
  1575. query.SortOrder = SortOrder.Descending;
  1576. }
  1577. }
  1578. if (query.SortBy == null || query.SortBy.Length == 0)
  1579. {
  1580. return string.Empty;
  1581. }
  1582. var isAscending = query.SortOrder != SortOrder.Descending;
  1583. return " ORDER BY " + string.Join(",", query.SortBy.Select(i =>
  1584. {
  1585. var columnMap = MapOrderByField(i);
  1586. var columnAscending = isAscending;
  1587. if (columnMap.Item2)
  1588. {
  1589. columnAscending = !columnAscending;
  1590. }
  1591. var sortOrder = columnAscending ? "ASC" : "DESC";
  1592. return columnMap.Item1 + " " + sortOrder;
  1593. }).ToArray());
  1594. }
  1595. private Tuple<string, bool> MapOrderByField(string name)
  1596. {
  1597. if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase))
  1598. {
  1599. // TODO
  1600. return new Tuple<string, bool>("SortName", false);
  1601. }
  1602. if (string.Equals(name, ItemSortBy.Runtime, StringComparison.OrdinalIgnoreCase))
  1603. {
  1604. return new Tuple<string, bool>("RuntimeTicks", false);
  1605. }
  1606. if (string.Equals(name, ItemSortBy.Random, StringComparison.OrdinalIgnoreCase))
  1607. {
  1608. return new Tuple<string, bool>("RANDOM()", false);
  1609. }
  1610. if (string.Equals(name, ItemSortBy.DatePlayed, StringComparison.OrdinalIgnoreCase))
  1611. {
  1612. return new Tuple<string, bool>("LastPlayedDate", false);
  1613. }
  1614. if (string.Equals(name, ItemSortBy.PlayCount, StringComparison.OrdinalIgnoreCase))
  1615. {
  1616. return new Tuple<string, bool>("PlayCount", false);
  1617. }
  1618. if (string.Equals(name, ItemSortBy.IsFavoriteOrLiked, StringComparison.OrdinalIgnoreCase))
  1619. {
  1620. return new Tuple<string, bool>("IsFavorite", true);
  1621. }
  1622. if (string.Equals(name, ItemSortBy.IsFolder, StringComparison.OrdinalIgnoreCase))
  1623. {
  1624. return new Tuple<string, bool>("IsFolder", true);
  1625. }
  1626. if (string.Equals(name, ItemSortBy.IsPlayed, StringComparison.OrdinalIgnoreCase))
  1627. {
  1628. return new Tuple<string, bool>("played", true);
  1629. }
  1630. if (string.Equals(name, ItemSortBy.IsUnplayed, StringComparison.OrdinalIgnoreCase))
  1631. {
  1632. return new Tuple<string, bool>("played", false);
  1633. }
  1634. if (string.Equals(name, ItemSortBy.DateLastContentAdded, StringComparison.OrdinalIgnoreCase))
  1635. {
  1636. return new Tuple<string, bool>("DateLastMediaAdded", false);
  1637. }
  1638. if (string.Equals(name, ItemSortBy.Artist, StringComparison.OrdinalIgnoreCase))
  1639. {
  1640. return new Tuple<string, bool>("(select value from itemvalues where ItemId=Guid and Type=0 LIMIT 1)", false);
  1641. }
  1642. if (string.Equals(name, ItemSortBy.AlbumArtist, StringComparison.OrdinalIgnoreCase))
  1643. {
  1644. return new Tuple<string, bool>("(select value from itemvalues where ItemId=Guid and Type=1 LIMIT 1)", false);
  1645. }
  1646. if (string.Equals(name, ItemSortBy.OfficialRating, StringComparison.OrdinalIgnoreCase))
  1647. {
  1648. return new Tuple<string, bool>("ParentalRatingValue", false);
  1649. }
  1650. if (string.Equals(name, ItemSortBy.Studio, StringComparison.OrdinalIgnoreCase))
  1651. {
  1652. return new Tuple<string, bool>("(select value from itemvalues where ItemId=Guid and Type=3 LIMIT 1)", false);
  1653. }
  1654. return new Tuple<string, bool>(name, false);
  1655. }
  1656. public List<Guid> GetItemIdsList(InternalItemsQuery query)
  1657. {
  1658. if (query == null)
  1659. {
  1660. throw new ArgumentNullException("query");
  1661. }
  1662. CheckDisposed();
  1663. var now = DateTime.UtcNow;
  1664. using (var cmd = _connection.CreateCommand())
  1665. {
  1666. cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems";
  1667. cmd.CommandText += GetJoinUserDataText(query);
  1668. if (EnableJoinUserData(query))
  1669. {
  1670. cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id;
  1671. }
  1672. var whereClauses = GetWhereClauses(query, cmd);
  1673. var whereText = whereClauses.Count == 0 ?
  1674. string.Empty :
  1675. " where " + string.Join(" AND ", whereClauses.ToArray());
  1676. cmd.CommandText += whereText;
  1677. if (EnableGroupByPresentationUniqueKey(query))
  1678. {
  1679. cmd.CommandText += " Group by PresentationUniqueKey";
  1680. }
  1681. cmd.CommandText += GetOrderByText(query);
  1682. if (query.Limit.HasValue || query.StartIndex.HasValue)
  1683. {
  1684. var limit = query.Limit ?? int.MaxValue;
  1685. cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture);
  1686. if (query.StartIndex.HasValue)
  1687. {
  1688. cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture);
  1689. }
  1690. }
  1691. var list = new List<Guid>();
  1692. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  1693. {
  1694. LogQueryTime("GetItemIdsList", cmd, now);
  1695. while (reader.Read())
  1696. {
  1697. list.Add(reader.GetGuid(0));
  1698. }
  1699. }
  1700. return list;
  1701. }
  1702. }
  1703. public QueryResult<Tuple<Guid, string>> GetItemIdsWithPath(InternalItemsQuery query)
  1704. {
  1705. if (query == null)
  1706. {
  1707. throw new ArgumentNullException("query");
  1708. }
  1709. CheckDisposed();
  1710. using (var cmd = _connection.CreateCommand())
  1711. {
  1712. cmd.CommandText = "select guid,path from TypedBaseItems";
  1713. var whereClauses = GetWhereClauses(query, cmd);
  1714. var whereTextWithoutPaging = whereClauses.Count == 0 ?
  1715. string.Empty :
  1716. " where " + string.Join(" AND ", whereClauses.ToArray());
  1717. var whereText = whereClauses.Count == 0 ?
  1718. string.Empty :
  1719. " where " + string.Join(" AND ", whereClauses.ToArray());
  1720. cmd.CommandText += whereText;
  1721. if (EnableGroupByPresentationUniqueKey(query))
  1722. {
  1723. cmd.CommandText += " Group by PresentationUniqueKey";
  1724. }
  1725. cmd.CommandText += GetOrderByText(query);
  1726. if (query.Limit.HasValue || query.StartIndex.HasValue)
  1727. {
  1728. var limit = query.Limit ?? int.MaxValue;
  1729. cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture);
  1730. if (query.StartIndex.HasValue)
  1731. {
  1732. cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture);
  1733. }
  1734. }
  1735. cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging;
  1736. var list = new List<Tuple<Guid, string>>();
  1737. var count = 0;
  1738. Logger.Debug(cmd.CommandText);
  1739. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
  1740. {
  1741. while (reader.Read())
  1742. {
  1743. var id = reader.GetGuid(0);
  1744. string path = null;
  1745. if (!reader.IsDBNull(1))
  1746. {
  1747. path = reader.GetString(1);
  1748. }
  1749. list.Add(new Tuple<Guid, string>(id, path));
  1750. }
  1751. if (reader.NextResult() && reader.Read())
  1752. {
  1753. count = reader.GetInt32(0);
  1754. }
  1755. }
  1756. return new QueryResult<Tuple<Guid, string>>()
  1757. {
  1758. Items = list.ToArray(),
  1759. TotalRecordCount = count
  1760. };
  1761. }
  1762. }
  1763. public QueryResult<Guid> GetItemIds(InternalItemsQuery query)
  1764. {
  1765. if (query == null)
  1766. {
  1767. throw new ArgumentNullException("query");
  1768. }
  1769. CheckDisposed();
  1770. var now = DateTime.UtcNow;
  1771. using (var cmd = _connection.CreateCommand())
  1772. {
  1773. cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems";
  1774. var whereClauses = GetWhereClauses(query, cmd);
  1775. cmd.CommandText += GetJoinUserDataText(query);
  1776. if (EnableJoinUserData(query))
  1777. {
  1778. cmd.Parameters.Add(cmd, "@UserId", DbType.Guid).Value = query.User.Id;
  1779. }
  1780. var whereText = whereClauses.Count == 0 ?
  1781. string.Empty :
  1782. " where " + string.Join(" AND ", whereClauses.ToArray());
  1783. cmd.CommandText += whereText;
  1784. if (EnableGroupByPresentationUniqueKey(query))
  1785. {
  1786. cmd.CommandText += " Group by PresentationUniqueKey";
  1787. }
  1788. cmd.CommandText += GetOrderByText(query);
  1789. if (query.Limit.HasValue || query.StartIndex.HasValue)
  1790. {
  1791. var limit = query.Limit ?? int.MaxValue;
  1792. cmd.CommandText += " LIMIT " + limit.ToString(CultureInfo.InvariantCulture);
  1793. if (query.StartIndex.HasValue)
  1794. {
  1795. cmd.CommandText += " OFFSET " + query.StartIndex.Value.ToString(CultureInfo.InvariantCulture);
  1796. }
  1797. }
  1798. if (EnableGroupByPresentationUniqueKey(query))
  1799. {
  1800. cmd.CommandText += "; select count (distinct PresentationUniqueKey) from TypedBaseItems";
  1801. }
  1802. else
  1803. {
  1804. cmd.CommandText += "; select count (guid) from TypedBaseItems";
  1805. }
  1806. cmd.CommandText += GetJoinUserDataText(query);
  1807. cmd.CommandText += whereText;
  1808. var list = new List<Guid>();
  1809. var count = 0;
  1810. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
  1811. {
  1812. LogQueryTime("GetItemIds", cmd, now);
  1813. while (reader.Read())
  1814. {
  1815. list.Add(reader.GetGuid(0));
  1816. }
  1817. if (reader.NextResult() && reader.Read())
  1818. {
  1819. count = reader.GetInt32(0);
  1820. }
  1821. }
  1822. return new QueryResult<Guid>()
  1823. {
  1824. Items = list.ToArray(),
  1825. TotalRecordCount = count
  1826. };
  1827. }
  1828. }
  1829. private List<string> GetWhereClauses(InternalItemsQuery query, IDbCommand cmd)
  1830. {
  1831. var whereClauses = new List<string>();
  1832. if (EnableJoinUserData(query))
  1833. {
  1834. whereClauses.Add("(UserId is null or UserId=@UserId)");
  1835. }
  1836. if (query.IsCurrentSchema.HasValue)
  1837. {
  1838. if (query.IsCurrentSchema.Value)
  1839. {
  1840. whereClauses.Add("(SchemaVersion not null AND SchemaVersion=@SchemaVersion)");
  1841. }
  1842. else
  1843. {
  1844. whereClauses.Add("(SchemaVersion is null or SchemaVersion<>@SchemaVersion)");
  1845. }
  1846. cmd.Parameters.Add(cmd, "@SchemaVersion", DbType.Int32).Value = LatestSchemaVersion;
  1847. }
  1848. if (query.IsHD.HasValue)
  1849. {
  1850. whereClauses.Add("IsHD=@IsHD");
  1851. cmd.Parameters.Add(cmd, "@IsHD", DbType.Boolean).Value = query.IsHD;
  1852. }
  1853. if (query.IsLocked.HasValue)
  1854. {
  1855. whereClauses.Add("IsLocked=@IsLocked");
  1856. cmd.Parameters.Add(cmd, "@IsLocked", DbType.Boolean).Value = query.IsLocked;
  1857. }
  1858. if (query.IsOffline.HasValue)
  1859. {
  1860. whereClauses.Add("IsOffline=@IsOffline");
  1861. cmd.Parameters.Add(cmd, "@IsOffline", DbType.Boolean).Value = query.IsOffline;
  1862. }
  1863. if (query.IsMovie.HasValue)
  1864. {
  1865. var alternateTypes = new List<string>();
  1866. if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Movie).Name))
  1867. {
  1868. alternateTypes.Add(typeof(Movie).FullName);
  1869. }
  1870. if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Trailer).Name))
  1871. {
  1872. alternateTypes.Add(typeof(Trailer).FullName);
  1873. }
  1874. if (alternateTypes.Count == 0)
  1875. {
  1876. whereClauses.Add("IsMovie=@IsMovie");
  1877. }
  1878. else
  1879. {
  1880. whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)");
  1881. }
  1882. cmd.Parameters.Add(cmd, "@IsMovie", DbType.Boolean).Value = query.IsMovie;
  1883. }
  1884. if (query.IsKids.HasValue)
  1885. {
  1886. whereClauses.Add("IsKids=@IsKids");
  1887. cmd.Parameters.Add(cmd, "@IsKids", DbType.Boolean).Value = query.IsKids;
  1888. }
  1889. if (query.IsSports.HasValue)
  1890. {
  1891. whereClauses.Add("IsSports=@IsSports");
  1892. cmd.Parameters.Add(cmd, "@IsSports", DbType.Boolean).Value = query.IsSports;
  1893. }
  1894. if (query.IsFolder.HasValue)
  1895. {
  1896. whereClauses.Add("IsFolder=@IsFolder");
  1897. cmd.Parameters.Add(cmd, "@IsFolder", DbType.Boolean).Value = query.IsFolder;
  1898. }
  1899. var includeTypes = query.IncludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray();
  1900. if (includeTypes.Length == 1)
  1901. {
  1902. whereClauses.Add("type=@type");
  1903. cmd.Parameters.Add(cmd, "@type", DbType.String).Value = includeTypes[0];
  1904. }
  1905. else if (includeTypes.Length > 1)
  1906. {
  1907. var inClause = string.Join(",", includeTypes.Select(i => "'" + i + "'").ToArray());
  1908. whereClauses.Add(string.Format("type in ({0})", inClause));
  1909. }
  1910. var excludeTypes = query.ExcludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray();
  1911. if (excludeTypes.Length == 1)
  1912. {
  1913. whereClauses.Add("type<>@type");
  1914. cmd.Parameters.Add(cmd, "@type", DbType.String).Value = excludeTypes[0];
  1915. }
  1916. else if (excludeTypes.Length > 1)
  1917. {
  1918. var inClause = string.Join(",", excludeTypes.Select(i => "'" + i + "'").ToArray());
  1919. whereClauses.Add(string.Format("type not in ({0})", inClause));
  1920. }
  1921. if (query.ChannelIds.Length == 1)
  1922. {
  1923. whereClauses.Add("ChannelId=@ChannelId");
  1924. cmd.Parameters.Add(cmd, "@ChannelId", DbType.String).Value = query.ChannelIds[0];
  1925. }
  1926. if (query.ChannelIds.Length > 1)
  1927. {
  1928. var inClause = string.Join(",", query.ChannelIds.Select(i => "'" + i + "'").ToArray());
  1929. whereClauses.Add(string.Format("ChannelId in ({0})", inClause));
  1930. }
  1931. if (query.ParentId.HasValue)
  1932. {
  1933. whereClauses.Add("ParentId=@ParentId");
  1934. cmd.Parameters.Add(cmd, "@ParentId", DbType.Guid).Value = query.ParentId.Value;
  1935. }
  1936. if (!string.IsNullOrWhiteSpace(query.Path))
  1937. {
  1938. whereClauses.Add("Path=@Path");
  1939. cmd.Parameters.Add(cmd, "@Path", DbType.String).Value = query.Path;
  1940. }
  1941. if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey))
  1942. {
  1943. whereClauses.Add("PresentationUniqueKey=@PresentationUniqueKey");
  1944. cmd.Parameters.Add(cmd, "@PresentationUniqueKey", DbType.String).Value = query.PresentationUniqueKey;
  1945. }
  1946. if (query.MinCommunityRating.HasValue)
  1947. {
  1948. whereClauses.Add("CommunityRating>=@MinCommunityRating");
  1949. cmd.Parameters.Add(cmd, "@MinCommunityRating", DbType.Double).Value = query.MinCommunityRating.Value;
  1950. }
  1951. if (query.MinIndexNumber.HasValue)
  1952. {
  1953. whereClauses.Add("IndexNumber>=@MinIndexNumber");
  1954. cmd.Parameters.Add(cmd, "@MinIndexNumber", DbType.Int32).Value = query.MinIndexNumber.Value;
  1955. }
  1956. //if (query.MinPlayers.HasValue)
  1957. //{
  1958. // whereClauses.Add("Players>=@MinPlayers");
  1959. // cmd.Parameters.Add(cmd, "@MinPlayers", DbType.Int32).Value = query.MinPlayers.Value;
  1960. //}
  1961. //if (query.MaxPlayers.HasValue)
  1962. //{
  1963. // whereClauses.Add("Players<=@MaxPlayers");
  1964. // cmd.Parameters.Add(cmd, "@MaxPlayers", DbType.Int32).Value = query.MaxPlayers.Value;
  1965. //}
  1966. if (query.IndexNumber.HasValue)
  1967. {
  1968. whereClauses.Add("IndexNumber=@IndexNumber");
  1969. cmd.Parameters.Add(cmd, "@IndexNumber", DbType.Int32).Value = query.IndexNumber.Value;
  1970. }
  1971. if (query.ParentIndexNumber.HasValue)
  1972. {
  1973. whereClauses.Add("ParentIndexNumber=@ParentIndexNumber");
  1974. cmd.Parameters.Add(cmd, "@ParentIndexNumber", DbType.Int32).Value = query.ParentIndexNumber.Value;
  1975. }
  1976. if (query.MinEndDate.HasValue)
  1977. {
  1978. whereClauses.Add("EndDate>=@MinEndDate");
  1979. cmd.Parameters.Add(cmd, "@MinEndDate", DbType.Date).Value = query.MinEndDate.Value;
  1980. }
  1981. if (query.MaxEndDate.HasValue)
  1982. {
  1983. whereClauses.Add("EndDate<=@MaxEndDate");
  1984. cmd.Parameters.Add(cmd, "@MaxEndDate", DbType.Date).Value = query.MaxEndDate.Value;
  1985. }
  1986. if (query.MinStartDate.HasValue)
  1987. {
  1988. whereClauses.Add("StartDate>=@MinStartDate");
  1989. cmd.Parameters.Add(cmd, "@MinStartDate", DbType.Date).Value = query.MinStartDate.Value;
  1990. }
  1991. if (query.MaxStartDate.HasValue)
  1992. {
  1993. whereClauses.Add("StartDate<=@MaxStartDate");
  1994. cmd.Parameters.Add(cmd, "@MaxStartDate", DbType.Date).Value = query.MaxStartDate.Value;
  1995. }
  1996. if (query.MinPremiereDate.HasValue)
  1997. {
  1998. whereClauses.Add("PremiereDate>=@MinPremiereDate");
  1999. cmd.Parameters.Add(cmd, "@MinPremiereDate", DbType.Date).Value = query.MinPremiereDate.Value;
  2000. }
  2001. if (query.MaxPremiereDate.HasValue)
  2002. {
  2003. whereClauses.Add("PremiereDate<=@MaxPremiereDate");
  2004. cmd.Parameters.Add(cmd, "@MaxPremiereDate", DbType.Date).Value = query.MaxPremiereDate.Value;
  2005. }
  2006. if (query.SourceTypes.Length == 1)
  2007. {
  2008. whereClauses.Add("SourceType=@SourceType");
  2009. cmd.Parameters.Add(cmd, "@SourceType", DbType.String).Value = query.SourceTypes[0];
  2010. }
  2011. else if (query.SourceTypes.Length > 1)
  2012. {
  2013. var inClause = string.Join(",", query.SourceTypes.Select(i => "'" + i + "'").ToArray());
  2014. whereClauses.Add(string.Format("SourceType in ({0})", inClause));
  2015. }
  2016. if (query.ExcludeSourceTypes.Length == 1)
  2017. {
  2018. whereClauses.Add("SourceType<>@SourceType");
  2019. cmd.Parameters.Add(cmd, "@SourceType", DbType.String).Value = query.SourceTypes[0];
  2020. }
  2021. else if (query.ExcludeSourceTypes.Length > 1)
  2022. {
  2023. var inClause = string.Join(",", query.ExcludeSourceTypes.Select(i => "'" + i + "'").ToArray());
  2024. whereClauses.Add(string.Format("SourceType not in ({0})", inClause));
  2025. }
  2026. if (query.TrailerTypes.Length > 0)
  2027. {
  2028. var clauses = new List<string>();
  2029. var index = 0;
  2030. foreach (var type in query.TrailerTypes)
  2031. {
  2032. clauses.Add("TrailerTypes like @TrailerTypes" + index);
  2033. cmd.Parameters.Add(cmd, "@TrailerTypes" + index, DbType.String).Value = "%" + type + "%";
  2034. index++;
  2035. }
  2036. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  2037. whereClauses.Add(clause);
  2038. }
  2039. if (query.IsAiring.HasValue)
  2040. {
  2041. if (query.IsAiring.Value)
  2042. {
  2043. whereClauses.Add("StartDate<=@MaxStartDate");
  2044. cmd.Parameters.Add(cmd, "@MaxStartDate", DbType.Date).Value = DateTime.UtcNow;
  2045. whereClauses.Add("EndDate>=@MinEndDate");
  2046. cmd.Parameters.Add(cmd, "@MinEndDate", DbType.Date).Value = DateTime.UtcNow;
  2047. }
  2048. else
  2049. {
  2050. whereClauses.Add("(StartDate>@IsAiringDate OR EndDate < @IsAiringDate)");
  2051. cmd.Parameters.Add(cmd, "@IsAiringDate", DbType.Date).Value = DateTime.UtcNow;
  2052. }
  2053. }
  2054. if (!string.IsNullOrWhiteSpace(query.Person))
  2055. {
  2056. whereClauses.Add("Guid in (select ItemId from People where Name=@PersonName)");
  2057. cmd.Parameters.Add(cmd, "@PersonName", DbType.String).Value = query.Person;
  2058. }
  2059. if (!string.IsNullOrWhiteSpace(query.SlugName))
  2060. {
  2061. whereClauses.Add("SlugName=@SlugName");
  2062. cmd.Parameters.Add(cmd, "@SlugName", DbType.String).Value = query.SlugName;
  2063. }
  2064. if (!string.IsNullOrWhiteSpace(query.Name))
  2065. {
  2066. whereClauses.Add("CleanName=@Name");
  2067. cmd.Parameters.Add(cmd, "@Name", DbType.String).Value = query.Name.RemoveDiacritics();
  2068. }
  2069. if (!string.IsNullOrWhiteSpace(query.NameContains))
  2070. {
  2071. whereClauses.Add("CleanName like @NameContains");
  2072. cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%" + query.NameContains.RemoveDiacritics() + "%";
  2073. }
  2074. if (!string.IsNullOrWhiteSpace(query.NameStartsWith))
  2075. {
  2076. whereClauses.Add("SortName like @NameStartsWith");
  2077. cmd.Parameters.Add(cmd, "@NameStartsWith", DbType.String).Value = query.NameStartsWith + "%";
  2078. }
  2079. if (!string.IsNullOrWhiteSpace(query.NameStartsWithOrGreater))
  2080. {
  2081. whereClauses.Add("SortName >= @NameStartsWithOrGreater");
  2082. // lowercase this because SortName is stored as lowercase
  2083. cmd.Parameters.Add(cmd, "@NameStartsWithOrGreater", DbType.String).Value = query.NameStartsWithOrGreater.ToLower();
  2084. }
  2085. if (!string.IsNullOrWhiteSpace(query.NameLessThan))
  2086. {
  2087. whereClauses.Add("SortName < @NameLessThan");
  2088. // lowercase this because SortName is stored as lowercase
  2089. cmd.Parameters.Add(cmd, "@NameLessThan", DbType.String).Value = query.NameLessThan.ToLower();
  2090. }
  2091. if (query.ImageTypes.Length > 0 && _config.Configuration.SchemaVersion >= 87)
  2092. {
  2093. var requiredImageIndex = 0;
  2094. foreach (var requiredImage in query.ImageTypes)
  2095. {
  2096. var paramName = "@RequiredImageType" + requiredImageIndex;
  2097. whereClauses.Add("(select path from images where ItemId=Guid and ImageType=" + paramName + " limit 1) not null");
  2098. cmd.Parameters.Add(cmd, paramName, DbType.Int32).Value = (int)requiredImage;
  2099. requiredImageIndex++;
  2100. }
  2101. }
  2102. if (query.IsLiked.HasValue)
  2103. {
  2104. if (query.IsLiked.Value)
  2105. {
  2106. whereClauses.Add("rating>=@UserRating");
  2107. cmd.Parameters.Add(cmd, "@UserRating", DbType.Double).Value = UserItemData.MinLikeValue;
  2108. }
  2109. else
  2110. {
  2111. whereClauses.Add("(rating is null or rating<@UserRating)");
  2112. cmd.Parameters.Add(cmd, "@UserRating", DbType.Double).Value = UserItemData.MinLikeValue;
  2113. }
  2114. }
  2115. if (query.IsFavoriteOrLiked.HasValue)
  2116. {
  2117. if (query.IsFavoriteOrLiked.Value)
  2118. {
  2119. whereClauses.Add("IsFavorite=@IsFavoriteOrLiked");
  2120. }
  2121. else
  2122. {
  2123. whereClauses.Add("(IsFavorite is null or IsFavorite=@IsFavoriteOrLiked)");
  2124. }
  2125. cmd.Parameters.Add(cmd, "@IsFavoriteOrLiked", DbType.Boolean).Value = query.IsFavoriteOrLiked.Value;
  2126. }
  2127. if (query.IsFavorite.HasValue)
  2128. {
  2129. if (query.IsFavorite.Value)
  2130. {
  2131. whereClauses.Add("IsFavorite=@IsFavorite");
  2132. }
  2133. else
  2134. {
  2135. whereClauses.Add("(IsFavorite is null or IsFavorite=@IsFavorite)");
  2136. }
  2137. cmd.Parameters.Add(cmd, "@IsFavorite", DbType.Boolean).Value = query.IsFavorite.Value;
  2138. }
  2139. if (EnableJoinUserData(query))
  2140. {
  2141. if (query.IsPlayed.HasValue)
  2142. {
  2143. if (query.IsPlayed.Value)
  2144. {
  2145. whereClauses.Add("(played=@IsPlayed)");
  2146. }
  2147. else
  2148. {
  2149. whereClauses.Add("(played is null or played=@IsPlayed)");
  2150. }
  2151. cmd.Parameters.Add(cmd, "@IsPlayed", DbType.Boolean).Value = query.IsPlayed.Value;
  2152. }
  2153. }
  2154. if (query.IsResumable.HasValue)
  2155. {
  2156. if (query.IsResumable.Value)
  2157. {
  2158. whereClauses.Add("playbackPositionTicks > 0");
  2159. }
  2160. else
  2161. {
  2162. whereClauses.Add("(playbackPositionTicks is null or playbackPositionTicks = 0)");
  2163. }
  2164. }
  2165. if (query.ArtistNames.Length > 0)
  2166. {
  2167. var clauses = new List<string>();
  2168. var index = 0;
  2169. foreach (var artist in query.ArtistNames)
  2170. {
  2171. clauses.Add("@ArtistName" + index + " in (select value from itemvalues where ItemId=Guid and Type <= 1)");
  2172. cmd.Parameters.Add(cmd, "@ArtistName" + index, DbType.String).Value = artist;
  2173. index++;
  2174. }
  2175. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  2176. whereClauses.Add(clause);
  2177. }
  2178. if (query.Genres.Length > 0)
  2179. {
  2180. var clauses = new List<string>();
  2181. var index = 0;
  2182. foreach (var item in query.Genres)
  2183. {
  2184. clauses.Add("@Genre" + index + " in (select value from itemvalues where ItemId=Guid and Type=2)");
  2185. cmd.Parameters.Add(cmd, "@Genre" + index, DbType.String).Value = item;
  2186. index++;
  2187. }
  2188. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  2189. whereClauses.Add(clause);
  2190. }
  2191. if (query.Tags.Length > 0)
  2192. {
  2193. var clauses = new List<string>();
  2194. var index = 0;
  2195. foreach (var item in query.Tags)
  2196. {
  2197. clauses.Add("@Tag" + index + " in (select value from itemvalues where ItemId=Guid and Type=4)");
  2198. cmd.Parameters.Add(cmd, "@Tag" + index, DbType.String).Value = item;
  2199. index++;
  2200. }
  2201. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  2202. whereClauses.Add(clause);
  2203. }
  2204. if (query.Studios.Length > 0)
  2205. {
  2206. var clauses = new List<string>();
  2207. var index = 0;
  2208. foreach (var item in query.Studios)
  2209. {
  2210. clauses.Add("@Studio" + index + " in (select value from itemvalues where ItemId=Guid and Type=3)");
  2211. cmd.Parameters.Add(cmd, "@Studio" + index, DbType.String).Value = item;
  2212. index++;
  2213. }
  2214. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  2215. whereClauses.Add(clause);
  2216. }
  2217. if (query.Keywords.Length > 0)
  2218. {
  2219. var clauses = new List<string>();
  2220. var index = 0;
  2221. foreach (var item in query.Keywords)
  2222. {
  2223. clauses.Add("@Keyword" + index + " in (select value from itemvalues where ItemId=Guid and Type=5)");
  2224. cmd.Parameters.Add(cmd, "@Keyword" + index, DbType.String).Value = item;
  2225. index++;
  2226. }
  2227. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  2228. whereClauses.Add(clause);
  2229. }
  2230. if (query.OfficialRatings.Length > 0)
  2231. {
  2232. var clauses = new List<string>();
  2233. var index = 0;
  2234. foreach (var item in query.OfficialRatings)
  2235. {
  2236. clauses.Add("OfficialRating=@OfficialRating" + index);
  2237. cmd.Parameters.Add(cmd, "@OfficialRating" + index, DbType.String).Value = item;
  2238. index++;
  2239. }
  2240. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  2241. whereClauses.Add(clause);
  2242. }
  2243. if (query.MinParentalRating.HasValue)
  2244. {
  2245. whereClauses.Add("InheritedParentalRatingValue<=@MinParentalRating");
  2246. cmd.Parameters.Add(cmd, "@MinParentalRating", DbType.Int32).Value = query.MinParentalRating.Value;
  2247. }
  2248. if (query.MaxParentalRating.HasValue)
  2249. {
  2250. whereClauses.Add("InheritedParentalRatingValue<=@MaxParentalRating");
  2251. cmd.Parameters.Add(cmd, "@MaxParentalRating", DbType.Int32).Value = query.MaxParentalRating.Value;
  2252. }
  2253. if (query.HasParentalRating.HasValue)
  2254. {
  2255. if (query.HasParentalRating.Value)
  2256. {
  2257. whereClauses.Add("InheritedParentalRatingValue > 0");
  2258. }
  2259. else
  2260. {
  2261. whereClauses.Add("InheritedParentalRatingValue = 0");
  2262. }
  2263. }
  2264. if (query.HasOverview.HasValue)
  2265. {
  2266. if (query.HasOverview.Value)
  2267. {
  2268. whereClauses.Add("(Overview not null AND Overview<>'')");
  2269. }
  2270. else
  2271. {
  2272. whereClauses.Add("(Overview is null OR Overview='')");
  2273. }
  2274. }
  2275. if (query.HasDeadParentId.HasValue)
  2276. {
  2277. if (query.HasDeadParentId.Value)
  2278. {
  2279. whereClauses.Add("ParentId NOT NULL AND ParentId NOT IN (select guid from TypedBaseItems)");
  2280. }
  2281. }
  2282. if (query.Years.Length == 1)
  2283. {
  2284. whereClauses.Add("ProductionYear=@Years");
  2285. cmd.Parameters.Add(cmd, "@Years", DbType.Int32).Value = query.Years[0].ToString();
  2286. }
  2287. else if (query.Years.Length > 1)
  2288. {
  2289. var val = string.Join(",", query.Years.ToArray());
  2290. whereClauses.Add("ProductionYear in (" + val + ")");
  2291. }
  2292. if (query.LocationTypes.Length == 1)
  2293. {
  2294. whereClauses.Add("LocationType=@LocationType");
  2295. cmd.Parameters.Add(cmd, "@LocationType", DbType.String).Value = query.LocationTypes[0].ToString();
  2296. }
  2297. else if (query.LocationTypes.Length > 1)
  2298. {
  2299. var val = string.Join(",", query.LocationTypes.Select(i => "'" + i + "'").ToArray());
  2300. whereClauses.Add("LocationType in (" + val + ")");
  2301. }
  2302. if (query.ExcludeLocationTypes.Length == 1)
  2303. {
  2304. whereClauses.Add("LocationType<>@ExcludeLocationTypes");
  2305. cmd.Parameters.Add(cmd, "@ExcludeLocationTypes", DbType.String).Value = query.ExcludeLocationTypes[0].ToString();
  2306. }
  2307. else if (query.ExcludeLocationTypes.Length > 1)
  2308. {
  2309. var val = string.Join(",", query.ExcludeLocationTypes.Select(i => "'" + i + "'").ToArray());
  2310. whereClauses.Add("LocationType not in (" + val + ")");
  2311. }
  2312. if (query.MediaTypes.Length == 1)
  2313. {
  2314. whereClauses.Add("MediaType=@MediaTypes");
  2315. cmd.Parameters.Add(cmd, "@MediaTypes", DbType.String).Value = query.MediaTypes[0];
  2316. }
  2317. if (query.MediaTypes.Length > 1)
  2318. {
  2319. var val = string.Join(",", query.MediaTypes.Select(i => "'" + i + "'").ToArray());
  2320. whereClauses.Add("MediaType in (" + val + ")");
  2321. }
  2322. if (query.ExcludeItemIds.Length > 0)
  2323. {
  2324. var excludeIds = new List<string>();
  2325. var index = 0;
  2326. foreach (var id in query.ExcludeItemIds)
  2327. {
  2328. excludeIds.Add("Guid <> @ExcludeId" + index);
  2329. cmd.Parameters.Add(cmd, "@ExcludeId" + index, DbType.Guid).Value = new Guid(id);
  2330. index++;
  2331. }
  2332. whereClauses.Add(string.Join(" AND ", excludeIds.ToArray()));
  2333. }
  2334. if (query.ExcludeProviderIds.Count > 0)
  2335. {
  2336. var excludeIds = new List<string>();
  2337. var index = 0;
  2338. foreach (var pair in query.ExcludeProviderIds)
  2339. {
  2340. if (string.Equals(pair.Key, MetadataProviders.TmdbCollection.ToString(), StringComparison.OrdinalIgnoreCase))
  2341. {
  2342. continue;
  2343. }
  2344. var paramName = "@ExcludeProviderId" + index;
  2345. excludeIds.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = '" + pair.Key + "'), '') <> " + paramName + ")");
  2346. cmd.Parameters.Add(cmd, paramName, DbType.String).Value = pair.Value;
  2347. index++;
  2348. }
  2349. whereClauses.Add(string.Join(" AND ", excludeIds.ToArray()));
  2350. }
  2351. if (query.HasImdbId.HasValue)
  2352. {
  2353. var fn = query.HasImdbId.Value ? "<>" : "=";
  2354. whereClauses.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = 'Imdb'), '') " + fn + " '')");
  2355. }
  2356. if (query.HasTmdbId.HasValue)
  2357. {
  2358. var fn = query.HasTmdbId.Value ? "<>" : "=";
  2359. whereClauses.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = 'Tmdb'), '') " + fn + " '')");
  2360. }
  2361. if (query.HasTvdbId.HasValue)
  2362. {
  2363. var fn = query.HasTvdbId.Value ? "<>" : "=";
  2364. whereClauses.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = 'Tvdb'), '') " + fn + " '')");
  2365. }
  2366. if (query.AlbumNames.Length > 0)
  2367. {
  2368. var clause = "(";
  2369. var index = 0;
  2370. foreach (var name in query.AlbumNames)
  2371. {
  2372. if (index > 0)
  2373. {
  2374. clause += " OR ";
  2375. }
  2376. clause += "Album=@AlbumName" + index;
  2377. cmd.Parameters.Add(cmd, "@AlbumName" + index, DbType.String).Value = name;
  2378. index++;
  2379. }
  2380. clause += ")";
  2381. whereClauses.Add(clause);
  2382. }
  2383. //var enableItemsByName = query.IncludeItemsByName ?? query.IncludeItemTypes.Length > 0;
  2384. var enableItemsByName = query.IncludeItemsByName ?? false;
  2385. if (query.TopParentIds.Length == 1)
  2386. {
  2387. if (enableItemsByName)
  2388. {
  2389. whereClauses.Add("(TopParentId=@TopParentId or IsItemByName=@IsItemByName)");
  2390. cmd.Parameters.Add(cmd, "@IsItemByName", DbType.Boolean).Value = true;
  2391. }
  2392. else
  2393. {
  2394. whereClauses.Add("(TopParentId=@TopParentId)");
  2395. }
  2396. cmd.Parameters.Add(cmd, "@TopParentId", DbType.String).Value = query.TopParentIds[0];
  2397. }
  2398. if (query.TopParentIds.Length > 1)
  2399. {
  2400. var val = string.Join(",", query.TopParentIds.Select(i => "'" + i + "'").ToArray());
  2401. if (enableItemsByName)
  2402. {
  2403. whereClauses.Add("(IsItemByName=@IsItemByName or TopParentId in (" + val + "))");
  2404. cmd.Parameters.Add(cmd, "@IsItemByName", DbType.Boolean).Value = true;
  2405. }
  2406. else
  2407. {
  2408. whereClauses.Add("(TopParentId in (" + val + "))");
  2409. }
  2410. }
  2411. if (query.AncestorIds.Length == 1)
  2412. {
  2413. whereClauses.Add("Guid in (select itemId from AncestorIds where AncestorId=@AncestorId)");
  2414. cmd.Parameters.Add(cmd, "@AncestorId", DbType.Guid).Value = new Guid(query.AncestorIds[0]);
  2415. }
  2416. if (query.AncestorIds.Length > 1)
  2417. {
  2418. var inClause = string.Join(",", query.AncestorIds.Select(i => "'" + new Guid(i).ToString("N") + "'").ToArray());
  2419. whereClauses.Add(string.Format("Guid in (select itemId from AncestorIds where AncestorIdText in ({0}))", inClause));
  2420. }
  2421. if (!string.IsNullOrWhiteSpace(query.AncestorWithPresentationUniqueKey))
  2422. {
  2423. var inClause = "select guid from TypedBaseItems where PresentationUniqueKey=@AncestorWithPresentationUniqueKey";
  2424. whereClauses.Add(string.Format("Guid in (select itemId from AncestorIds where AncestorId in ({0}))", inClause));
  2425. cmd.Parameters.Add(cmd, "@AncestorWithPresentationUniqueKey", DbType.String).Value = query.AncestorWithPresentationUniqueKey;
  2426. }
  2427. if (query.BlockUnratedItems.Length == 1)
  2428. {
  2429. whereClauses.Add("(InheritedParentalRatingValue > 0 or UnratedType <> @UnratedType)");
  2430. cmd.Parameters.Add(cmd, "@UnratedType", DbType.String).Value = query.BlockUnratedItems[0].ToString();
  2431. }
  2432. if (query.BlockUnratedItems.Length > 1)
  2433. {
  2434. var inClause = string.Join(",", query.BlockUnratedItems.Select(i => "'" + i.ToString() + "'").ToArray());
  2435. whereClauses.Add(string.Format("(InheritedParentalRatingValue > 0 or UnratedType not in ({0}))", inClause));
  2436. }
  2437. var excludeTagIndex = 0;
  2438. foreach (var excludeTag in query.ExcludeTags)
  2439. {
  2440. whereClauses.Add("(Tags is null OR Tags not like @excludeTag" + excludeTagIndex + ")");
  2441. cmd.Parameters.Add(cmd, "@excludeTag" + excludeTagIndex, DbType.String).Value = "%" + excludeTag + "%";
  2442. excludeTagIndex++;
  2443. }
  2444. excludeTagIndex = 0;
  2445. foreach (var excludeTag in query.ExcludeInheritedTags)
  2446. {
  2447. whereClauses.Add("(InheritedTags is null OR InheritedTags not like @excludeInheritedTag" + excludeTagIndex + ")");
  2448. cmd.Parameters.Add(cmd, "@excludeInheritedTag" + excludeTagIndex, DbType.String).Value = "%" + excludeTag + "%";
  2449. excludeTagIndex++;
  2450. }
  2451. return whereClauses;
  2452. }
  2453. private bool EnableGroupByPresentationUniqueKey(InternalItemsQuery query)
  2454. {
  2455. if (!query.GroupByPresentationUniqueKey)
  2456. {
  2457. return false;
  2458. }
  2459. if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey))
  2460. {
  2461. return false;
  2462. }
  2463. if (query.User == null)
  2464. {
  2465. return false;
  2466. }
  2467. if (query.IncludeItemTypes.Length == 0)
  2468. {
  2469. return true;
  2470. }
  2471. var types = new[] {
  2472. typeof(Episode).Name,
  2473. typeof(Video).Name ,
  2474. typeof(Movie).Name ,
  2475. typeof(MusicVideo).Name ,
  2476. typeof(Series).Name ,
  2477. typeof(Season).Name };
  2478. if (types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase)))
  2479. {
  2480. return true;
  2481. }
  2482. return false;
  2483. }
  2484. private static readonly Type[] KnownTypes =
  2485. {
  2486. typeof(LiveTvProgram),
  2487. typeof(LiveTvChannel),
  2488. typeof(LiveTvVideoRecording),
  2489. typeof(LiveTvAudioRecording),
  2490. typeof(Series),
  2491. typeof(Audio),
  2492. typeof(MusicAlbum),
  2493. typeof(MusicArtist),
  2494. typeof(MusicGenre),
  2495. typeof(MusicVideo),
  2496. typeof(Movie),
  2497. typeof(Playlist),
  2498. typeof(AudioPodcast),
  2499. typeof(Trailer),
  2500. typeof(BoxSet),
  2501. typeof(Episode),
  2502. typeof(Season),
  2503. typeof(Series),
  2504. typeof(Book),
  2505. typeof(CollectionFolder),
  2506. typeof(Folder),
  2507. typeof(Game),
  2508. typeof(GameGenre),
  2509. typeof(GameSystem),
  2510. typeof(Genre),
  2511. typeof(Person),
  2512. typeof(Photo),
  2513. typeof(PhotoAlbum),
  2514. typeof(Studio),
  2515. typeof(UserRootFolder),
  2516. typeof(UserView),
  2517. typeof(Video),
  2518. typeof(Year),
  2519. typeof(Channel),
  2520. typeof(AggregateFolder)
  2521. };
  2522. public async Task UpdateInheritedValues(CancellationToken cancellationToken)
  2523. {
  2524. await UpdateInheritedParentalRating(cancellationToken).ConfigureAwait(false);
  2525. await UpdateInheritedTags(cancellationToken).ConfigureAwait(false);
  2526. }
  2527. private async Task UpdateInheritedTags(CancellationToken cancellationToken)
  2528. {
  2529. var newValues = new List<Tuple<Guid, string>>();
  2530. using (var cmd = _connection.CreateCommand())
  2531. {
  2532. cmd.CommandText = "select Guid,InheritedTags,(select group_concat(Tags, '|') from TypedBaseItems where (guid=outer.guid) OR (guid in (Select AncestorId from AncestorIds where ItemId=Outer.guid))) as NewInheritedTags from typedbaseitems as Outer where NewInheritedTags <> InheritedTags";
  2533. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  2534. {
  2535. while (reader.Read())
  2536. {
  2537. var id = reader.GetGuid(0);
  2538. string value = reader.IsDBNull(2) ? null : reader.GetString(2);
  2539. newValues.Add(new Tuple<Guid, string>(id, value));
  2540. }
  2541. }
  2542. }
  2543. Logger.Debug("UpdateInheritedTags - {0} rows", newValues.Count);
  2544. if (newValues.Count == 0)
  2545. {
  2546. return;
  2547. }
  2548. await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  2549. IDbTransaction transaction = null;
  2550. try
  2551. {
  2552. transaction = _connection.BeginTransaction();
  2553. foreach (var item in newValues)
  2554. {
  2555. _updateInheritedTagsCommand.GetParameter(0).Value = item.Item1;
  2556. _updateInheritedTagsCommand.GetParameter(1).Value = item.Item2;
  2557. _updateInheritedTagsCommand.Transaction = transaction;
  2558. _updateInheritedTagsCommand.ExecuteNonQuery();
  2559. }
  2560. transaction.Commit();
  2561. }
  2562. catch (OperationCanceledException)
  2563. {
  2564. if (transaction != null)
  2565. {
  2566. transaction.Rollback();
  2567. }
  2568. throw;
  2569. }
  2570. catch (Exception e)
  2571. {
  2572. Logger.ErrorException("Error running query:", e);
  2573. if (transaction != null)
  2574. {
  2575. transaction.Rollback();
  2576. }
  2577. throw;
  2578. }
  2579. finally
  2580. {
  2581. if (transaction != null)
  2582. {
  2583. transaction.Dispose();
  2584. }
  2585. WriteLock.Release();
  2586. }
  2587. }
  2588. private async Task UpdateInheritedParentalRating(CancellationToken cancellationToken)
  2589. {
  2590. var newValues = new List<Tuple<Guid, int>>();
  2591. using (var cmd = _connection.CreateCommand())
  2592. {
  2593. cmd.CommandText = "select Guid,InheritedParentalRatingValue,(select Max(ParentalRatingValue, (select COALESCE(MAX(ParentalRatingValue),0) from TypedBaseItems where guid in (Select AncestorId from AncestorIds where ItemId=Outer.guid)))) as NewInheritedParentalRatingValue from typedbaseitems as Outer where InheritedParentalRatingValue <> NewInheritedParentalRatingValue";
  2594. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  2595. {
  2596. while (reader.Read())
  2597. {
  2598. var id = reader.GetGuid(0);
  2599. var newValue = reader.GetInt32(2);
  2600. newValues.Add(new Tuple<Guid, int>(id, newValue));
  2601. }
  2602. }
  2603. }
  2604. Logger.Debug("UpdateInheritedParentalRatings - {0} rows", newValues.Count);
  2605. if (newValues.Count == 0)
  2606. {
  2607. return;
  2608. }
  2609. await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  2610. IDbTransaction transaction = null;
  2611. try
  2612. {
  2613. transaction = _connection.BeginTransaction();
  2614. foreach (var item in newValues)
  2615. {
  2616. _updateInheritedRatingCommand.GetParameter(0).Value = item.Item1;
  2617. _updateInheritedRatingCommand.GetParameter(1).Value = item.Item2;
  2618. _updateInheritedRatingCommand.Transaction = transaction;
  2619. _updateInheritedRatingCommand.ExecuteNonQuery();
  2620. }
  2621. transaction.Commit();
  2622. }
  2623. catch (OperationCanceledException)
  2624. {
  2625. if (transaction != null)
  2626. {
  2627. transaction.Rollback();
  2628. }
  2629. throw;
  2630. }
  2631. catch (Exception e)
  2632. {
  2633. Logger.ErrorException("Error running query:", e);
  2634. if (transaction != null)
  2635. {
  2636. transaction.Rollback();
  2637. }
  2638. throw;
  2639. }
  2640. finally
  2641. {
  2642. if (transaction != null)
  2643. {
  2644. transaction.Dispose();
  2645. }
  2646. WriteLock.Release();
  2647. }
  2648. }
  2649. private static Dictionary<string, string[]> GetTypeMapDictionary()
  2650. {
  2651. var dict = new Dictionary<string, string[]>(StringComparer.OrdinalIgnoreCase);
  2652. foreach (var t in KnownTypes)
  2653. {
  2654. dict[t.Name] = new[] { t.FullName };
  2655. }
  2656. dict["Recording"] = new[] { typeof(LiveTvAudioRecording).FullName, typeof(LiveTvVideoRecording).FullName };
  2657. dict["Program"] = new[] { typeof(LiveTvProgram).FullName };
  2658. dict["TvChannel"] = new[] { typeof(LiveTvChannel).FullName };
  2659. return dict;
  2660. }
  2661. // Not crazy about having this all the way down here, but at least it's in one place
  2662. readonly Dictionary<string, string[]> _types = GetTypeMapDictionary();
  2663. private IEnumerable<string> MapIncludeItemTypes(string value)
  2664. {
  2665. string[] result;
  2666. if (_types.TryGetValue(value, out result))
  2667. {
  2668. return result;
  2669. }
  2670. return new[] { value };
  2671. }
  2672. public async Task DeleteItem(Guid id, CancellationToken cancellationToken)
  2673. {
  2674. if (id == Guid.Empty)
  2675. {
  2676. throw new ArgumentNullException("id");
  2677. }
  2678. CheckDisposed();
  2679. await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  2680. IDbTransaction transaction = null;
  2681. try
  2682. {
  2683. transaction = _connection.BeginTransaction();
  2684. // Delete people
  2685. _deletePeopleCommand.GetParameter(0).Value = id;
  2686. _deletePeopleCommand.Transaction = transaction;
  2687. _deletePeopleCommand.ExecuteNonQuery();
  2688. // Delete chapters
  2689. _deleteChaptersCommand.GetParameter(0).Value = id;
  2690. _deleteChaptersCommand.Transaction = transaction;
  2691. _deleteChaptersCommand.ExecuteNonQuery();
  2692. // Delete media streams
  2693. _deleteStreamsCommand.GetParameter(0).Value = id;
  2694. _deleteStreamsCommand.Transaction = transaction;
  2695. _deleteStreamsCommand.ExecuteNonQuery();
  2696. // Delete ancestors
  2697. _deleteAncestorsCommand.GetParameter(0).Value = id;
  2698. _deleteAncestorsCommand.Transaction = transaction;
  2699. _deleteAncestorsCommand.ExecuteNonQuery();
  2700. // Delete user data keys
  2701. _deleteUserDataKeysCommand.GetParameter(0).Value = id;
  2702. _deleteUserDataKeysCommand.Transaction = transaction;
  2703. _deleteUserDataKeysCommand.ExecuteNonQuery();
  2704. // Delete item values
  2705. _deleteItemValuesCommand.GetParameter(0).Value = id;
  2706. _deleteItemValuesCommand.Transaction = transaction;
  2707. _deleteItemValuesCommand.ExecuteNonQuery();
  2708. // Delete provider ids
  2709. _deleteProviderIdsCommand.GetParameter(0).Value = id;
  2710. _deleteProviderIdsCommand.Transaction = transaction;
  2711. _deleteProviderIdsCommand.ExecuteNonQuery();
  2712. // Delete images
  2713. _deleteImagesCommand.GetParameter(0).Value = id;
  2714. _deleteImagesCommand.Transaction = transaction;
  2715. _deleteImagesCommand.ExecuteNonQuery();
  2716. // Delete the item
  2717. _deleteItemCommand.GetParameter(0).Value = id;
  2718. _deleteItemCommand.Transaction = transaction;
  2719. _deleteItemCommand.ExecuteNonQuery();
  2720. transaction.Commit();
  2721. }
  2722. catch (OperationCanceledException)
  2723. {
  2724. if (transaction != null)
  2725. {
  2726. transaction.Rollback();
  2727. }
  2728. throw;
  2729. }
  2730. catch (Exception e)
  2731. {
  2732. Logger.ErrorException("Failed to save children:", e);
  2733. if (transaction != null)
  2734. {
  2735. transaction.Rollback();
  2736. }
  2737. throw;
  2738. }
  2739. finally
  2740. {
  2741. if (transaction != null)
  2742. {
  2743. transaction.Dispose();
  2744. }
  2745. WriteLock.Release();
  2746. }
  2747. }
  2748. public List<string> GetPeopleNames(InternalPeopleQuery query)
  2749. {
  2750. if (query == null)
  2751. {
  2752. throw new ArgumentNullException("query");
  2753. }
  2754. CheckDisposed();
  2755. using (var cmd = _connection.CreateCommand())
  2756. {
  2757. cmd.CommandText = "select Distinct Name from People";
  2758. var whereClauses = GetPeopleWhereClauses(query, cmd);
  2759. if (whereClauses.Count > 0)
  2760. {
  2761. cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray());
  2762. }
  2763. cmd.CommandText += " order by ListOrder";
  2764. var list = new List<string>();
  2765. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  2766. {
  2767. while (reader.Read())
  2768. {
  2769. list.Add(reader.GetString(0));
  2770. }
  2771. }
  2772. return list;
  2773. }
  2774. }
  2775. public List<PersonInfo> GetPeople(InternalPeopleQuery query)
  2776. {
  2777. if (query == null)
  2778. {
  2779. throw new ArgumentNullException("query");
  2780. }
  2781. CheckDisposed();
  2782. using (var cmd = _connection.CreateCommand())
  2783. {
  2784. cmd.CommandText = "select ItemId, Name, Role, PersonType, SortOrder from People";
  2785. var whereClauses = GetPeopleWhereClauses(query, cmd);
  2786. if (whereClauses.Count > 0)
  2787. {
  2788. cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray());
  2789. }
  2790. cmd.CommandText += " order by ListOrder";
  2791. var list = new List<PersonInfo>();
  2792. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  2793. {
  2794. while (reader.Read())
  2795. {
  2796. list.Add(GetPerson(reader));
  2797. }
  2798. }
  2799. return list;
  2800. }
  2801. }
  2802. private List<string> GetPeopleWhereClauses(InternalPeopleQuery query, IDbCommand cmd)
  2803. {
  2804. var whereClauses = new List<string>();
  2805. if (query.ItemId != Guid.Empty)
  2806. {
  2807. whereClauses.Add("ItemId=@ItemId");
  2808. cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = query.ItemId;
  2809. }
  2810. if (query.AppearsInItemId != Guid.Empty)
  2811. {
  2812. whereClauses.Add("Name in (Select Name from People where ItemId=@AppearsInItemId)");
  2813. cmd.Parameters.Add(cmd, "@AppearsInItemId", DbType.Guid).Value = query.AppearsInItemId;
  2814. }
  2815. if (query.PersonTypes.Count == 1)
  2816. {
  2817. whereClauses.Add("PersonType=@PersonType");
  2818. cmd.Parameters.Add(cmd, "@PersonType", DbType.String).Value = query.PersonTypes[0];
  2819. }
  2820. if (query.PersonTypes.Count > 1)
  2821. {
  2822. var val = string.Join(",", query.PersonTypes.Select(i => "'" + i + "'").ToArray());
  2823. whereClauses.Add("PersonType in (" + val + ")");
  2824. }
  2825. if (query.ExcludePersonTypes.Count == 1)
  2826. {
  2827. whereClauses.Add("PersonType<>@PersonType");
  2828. cmd.Parameters.Add(cmd, "@PersonType", DbType.String).Value = query.ExcludePersonTypes[0];
  2829. }
  2830. if (query.ExcludePersonTypes.Count > 1)
  2831. {
  2832. var val = string.Join(",", query.ExcludePersonTypes.Select(i => "'" + i + "'").ToArray());
  2833. whereClauses.Add("PersonType not in (" + val + ")");
  2834. }
  2835. if (query.MaxListOrder.HasValue)
  2836. {
  2837. whereClauses.Add("ListOrder<=@MaxListOrder");
  2838. cmd.Parameters.Add(cmd, "@MaxListOrder", DbType.Int32).Value = query.MaxListOrder.Value;
  2839. }
  2840. if (!string.IsNullOrWhiteSpace(query.NameContains))
  2841. {
  2842. whereClauses.Add("Name like @NameContains");
  2843. cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%" + query.NameContains + "%";
  2844. }
  2845. return whereClauses;
  2846. }
  2847. private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, IDbTransaction transaction)
  2848. {
  2849. if (itemId == Guid.Empty)
  2850. {
  2851. throw new ArgumentNullException("itemId");
  2852. }
  2853. if (ancestorIds == null)
  2854. {
  2855. throw new ArgumentNullException("ancestorIds");
  2856. }
  2857. CheckDisposed();
  2858. // First delete
  2859. _deleteAncestorsCommand.GetParameter(0).Value = itemId;
  2860. _deleteAncestorsCommand.Transaction = transaction;
  2861. _deleteAncestorsCommand.ExecuteNonQuery();
  2862. foreach (var ancestorId in ancestorIds)
  2863. {
  2864. _saveAncestorCommand.GetParameter(0).Value = itemId;
  2865. _saveAncestorCommand.GetParameter(1).Value = ancestorId;
  2866. _saveAncestorCommand.GetParameter(2).Value = ancestorId.ToString("N");
  2867. _saveAncestorCommand.Transaction = transaction;
  2868. _saveAncestorCommand.ExecuteNonQuery();
  2869. }
  2870. }
  2871. private List<Tuple<int, string>> GetItemValues(BaseItem item)
  2872. {
  2873. var list = new List<Tuple<int, string>>();
  2874. var hasArtist = item as IHasArtist;
  2875. if (hasArtist != null)
  2876. {
  2877. list.AddRange(hasArtist.Artists.Select(i => new Tuple<int, string>(0, i)));
  2878. }
  2879. var hasAlbumArtist = item as IHasAlbumArtist;
  2880. if (hasAlbumArtist != null)
  2881. {
  2882. list.AddRange(hasAlbumArtist.AlbumArtists.Select(i => new Tuple<int, string>(1, i)));
  2883. }
  2884. list.AddRange(item.Genres.Select(i => new Tuple<int, string>(2, i)));
  2885. list.AddRange(item.Studios.Select(i => new Tuple<int, string>(3, i)));
  2886. list.AddRange(item.Tags.Select(i => new Tuple<int, string>(4, i)));
  2887. list.AddRange(item.Keywords.Select(i => new Tuple<int, string>(5, i)));
  2888. return list;
  2889. }
  2890. private void UpdateImages(Guid itemId, List<ItemImageInfo> images, IDbTransaction transaction)
  2891. {
  2892. if (itemId == Guid.Empty)
  2893. {
  2894. throw new ArgumentNullException("itemId");
  2895. }
  2896. if (images == null)
  2897. {
  2898. throw new ArgumentNullException("images");
  2899. }
  2900. CheckDisposed();
  2901. // First delete
  2902. _deleteImagesCommand.GetParameter(0).Value = itemId;
  2903. _deleteImagesCommand.Transaction = transaction;
  2904. _deleteImagesCommand.ExecuteNonQuery();
  2905. var index = 0;
  2906. foreach (var image in images)
  2907. {
  2908. _saveImagesCommand.GetParameter(0).Value = itemId;
  2909. _saveImagesCommand.GetParameter(1).Value = image.Type;
  2910. _saveImagesCommand.GetParameter(2).Value = image.Path;
  2911. if (image.DateModified == default(DateTime))
  2912. {
  2913. _saveImagesCommand.GetParameter(3).Value = null;
  2914. }
  2915. else
  2916. {
  2917. _saveImagesCommand.GetParameter(3).Value = image.DateModified;
  2918. }
  2919. _saveImagesCommand.GetParameter(4).Value = image.IsPlaceholder;
  2920. _saveImagesCommand.GetParameter(5).Value = index;
  2921. _saveImagesCommand.Transaction = transaction;
  2922. _saveImagesCommand.ExecuteNonQuery();
  2923. index++;
  2924. }
  2925. }
  2926. private void UpdateProviderIds(Guid itemId, Dictionary<string, string> values, IDbTransaction transaction)
  2927. {
  2928. if (itemId == Guid.Empty)
  2929. {
  2930. throw new ArgumentNullException("itemId");
  2931. }
  2932. if (values == null)
  2933. {
  2934. throw new ArgumentNullException("values");
  2935. }
  2936. CheckDisposed();
  2937. // First delete
  2938. _deleteProviderIdsCommand.GetParameter(0).Value = itemId;
  2939. _deleteProviderIdsCommand.Transaction = transaction;
  2940. _deleteProviderIdsCommand.ExecuteNonQuery();
  2941. foreach (var pair in values)
  2942. {
  2943. _saveProviderIdsCommand.GetParameter(0).Value = itemId;
  2944. _saveProviderIdsCommand.GetParameter(1).Value = pair.Key;
  2945. _saveProviderIdsCommand.GetParameter(2).Value = pair.Value;
  2946. _saveProviderIdsCommand.Transaction = transaction;
  2947. _saveProviderIdsCommand.ExecuteNonQuery();
  2948. }
  2949. }
  2950. private void UpdateItemValues(Guid itemId, List<Tuple<int, string>> values, IDbTransaction transaction)
  2951. {
  2952. if (itemId == Guid.Empty)
  2953. {
  2954. throw new ArgumentNullException("itemId");
  2955. }
  2956. if (values == null)
  2957. {
  2958. throw new ArgumentNullException("keys");
  2959. }
  2960. CheckDisposed();
  2961. // First delete
  2962. _deleteItemValuesCommand.GetParameter(0).Value = itemId;
  2963. _deleteItemValuesCommand.Transaction = transaction;
  2964. _deleteItemValuesCommand.ExecuteNonQuery();
  2965. foreach (var pair in values)
  2966. {
  2967. _saveItemValuesCommand.GetParameter(0).Value = itemId;
  2968. _saveItemValuesCommand.GetParameter(1).Value = pair.Item1;
  2969. _saveItemValuesCommand.GetParameter(2).Value = pair.Item2;
  2970. _saveItemValuesCommand.Transaction = transaction;
  2971. _saveItemValuesCommand.ExecuteNonQuery();
  2972. }
  2973. }
  2974. private void UpdateUserDataKeys(Guid itemId, List<string> keys, IDbTransaction transaction)
  2975. {
  2976. if (itemId == Guid.Empty)
  2977. {
  2978. throw new ArgumentNullException("itemId");
  2979. }
  2980. if (keys == null)
  2981. {
  2982. throw new ArgumentNullException("keys");
  2983. }
  2984. CheckDisposed();
  2985. // First delete
  2986. _deleteUserDataKeysCommand.GetParameter(0).Value = itemId;
  2987. _deleteUserDataKeysCommand.Transaction = transaction;
  2988. _deleteUserDataKeysCommand.ExecuteNonQuery();
  2989. var index = 0;
  2990. foreach (var key in keys)
  2991. {
  2992. _saveUserDataKeysCommand.GetParameter(0).Value = itemId;
  2993. _saveUserDataKeysCommand.GetParameter(1).Value = key;
  2994. _saveUserDataKeysCommand.GetParameter(2).Value = index;
  2995. index++;
  2996. _saveUserDataKeysCommand.Transaction = transaction;
  2997. _saveUserDataKeysCommand.ExecuteNonQuery();
  2998. }
  2999. }
  3000. public async Task UpdatePeople(Guid itemId, List<PersonInfo> people)
  3001. {
  3002. if (itemId == Guid.Empty)
  3003. {
  3004. throw new ArgumentNullException("itemId");
  3005. }
  3006. if (people == null)
  3007. {
  3008. throw new ArgumentNullException("people");
  3009. }
  3010. CheckDisposed();
  3011. var cancellationToken = CancellationToken.None;
  3012. await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  3013. IDbTransaction transaction = null;
  3014. try
  3015. {
  3016. transaction = _connection.BeginTransaction();
  3017. // First delete
  3018. _deletePeopleCommand.GetParameter(0).Value = itemId;
  3019. _deletePeopleCommand.Transaction = transaction;
  3020. _deletePeopleCommand.ExecuteNonQuery();
  3021. var listIndex = 0;
  3022. foreach (var person in people)
  3023. {
  3024. cancellationToken.ThrowIfCancellationRequested();
  3025. _savePersonCommand.GetParameter(0).Value = itemId;
  3026. _savePersonCommand.GetParameter(1).Value = person.Name;
  3027. _savePersonCommand.GetParameter(2).Value = person.Role;
  3028. _savePersonCommand.GetParameter(3).Value = person.Type;
  3029. _savePersonCommand.GetParameter(4).Value = person.SortOrder;
  3030. _savePersonCommand.GetParameter(5).Value = listIndex;
  3031. _savePersonCommand.Transaction = transaction;
  3032. _savePersonCommand.ExecuteNonQuery();
  3033. listIndex++;
  3034. }
  3035. transaction.Commit();
  3036. }
  3037. catch (OperationCanceledException)
  3038. {
  3039. if (transaction != null)
  3040. {
  3041. transaction.Rollback();
  3042. }
  3043. throw;
  3044. }
  3045. catch (Exception e)
  3046. {
  3047. Logger.ErrorException("Failed to save people:", e);
  3048. if (transaction != null)
  3049. {
  3050. transaction.Rollback();
  3051. }
  3052. throw;
  3053. }
  3054. finally
  3055. {
  3056. if (transaction != null)
  3057. {
  3058. transaction.Dispose();
  3059. }
  3060. WriteLock.Release();
  3061. }
  3062. }
  3063. private PersonInfo GetPerson(IDataReader reader)
  3064. {
  3065. var item = new PersonInfo();
  3066. item.ItemId = reader.GetGuid(0);
  3067. item.Name = reader.GetString(1);
  3068. if (!reader.IsDBNull(2))
  3069. {
  3070. item.Role = reader.GetString(2);
  3071. }
  3072. if (!reader.IsDBNull(3))
  3073. {
  3074. item.Type = reader.GetString(3);
  3075. }
  3076. if (!reader.IsDBNull(4))
  3077. {
  3078. item.SortOrder = reader.GetInt32(4);
  3079. }
  3080. return item;
  3081. }
  3082. public IEnumerable<MediaStream> GetMediaStreams(MediaStreamQuery query)
  3083. {
  3084. CheckDisposed();
  3085. if (query == null)
  3086. {
  3087. throw new ArgumentNullException("query");
  3088. }
  3089. var list = new List<MediaStream>();
  3090. using (var cmd = _connection.CreateCommand())
  3091. {
  3092. var cmdText = "select " + string.Join(",", _mediaStreamSaveColumns) + " from mediastreams where";
  3093. cmdText += " ItemId=@ItemId";
  3094. cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = query.ItemId;
  3095. if (query.Type.HasValue)
  3096. {
  3097. cmdText += " AND StreamType=@StreamType";
  3098. cmd.Parameters.Add(cmd, "@StreamType", DbType.String).Value = query.Type.Value.ToString();
  3099. }
  3100. if (query.Index.HasValue)
  3101. {
  3102. cmdText += " AND StreamIndex=@StreamIndex";
  3103. cmd.Parameters.Add(cmd, "@StreamIndex", DbType.Int32).Value = query.Index.Value;
  3104. }
  3105. cmdText += " order by StreamIndex ASC";
  3106. cmd.CommandText = cmdText;
  3107. using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
  3108. {
  3109. while (reader.Read())
  3110. {
  3111. list.Add(GetMediaStream(reader));
  3112. }
  3113. }
  3114. }
  3115. return list;
  3116. }
  3117. public async Task SaveMediaStreams(Guid id, IEnumerable<MediaStream> streams, CancellationToken cancellationToken)
  3118. {
  3119. CheckDisposed();
  3120. if (id == Guid.Empty)
  3121. {
  3122. throw new ArgumentNullException("id");
  3123. }
  3124. if (streams == null)
  3125. {
  3126. throw new ArgumentNullException("streams");
  3127. }
  3128. cancellationToken.ThrowIfCancellationRequested();
  3129. await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  3130. IDbTransaction transaction = null;
  3131. try
  3132. {
  3133. transaction = _connection.BeginTransaction();
  3134. // First delete chapters
  3135. _deleteStreamsCommand.GetParameter(0).Value = id;
  3136. _deleteStreamsCommand.Transaction = transaction;
  3137. _deleteStreamsCommand.ExecuteNonQuery();
  3138. foreach (var stream in streams)
  3139. {
  3140. cancellationToken.ThrowIfCancellationRequested();
  3141. var index = 0;
  3142. _saveStreamCommand.GetParameter(index++).Value = id;
  3143. _saveStreamCommand.GetParameter(index++).Value = stream.Index;
  3144. _saveStreamCommand.GetParameter(index++).Value = stream.Type.ToString();
  3145. _saveStreamCommand.GetParameter(index++).Value = stream.Codec;
  3146. _saveStreamCommand.GetParameter(index++).Value = stream.Language;
  3147. _saveStreamCommand.GetParameter(index++).Value = stream.ChannelLayout;
  3148. _saveStreamCommand.GetParameter(index++).Value = stream.Profile;
  3149. _saveStreamCommand.GetParameter(index++).Value = stream.AspectRatio;
  3150. _saveStreamCommand.GetParameter(index++).Value = stream.Path;
  3151. _saveStreamCommand.GetParameter(index++).Value = stream.IsInterlaced;
  3152. _saveStreamCommand.GetParameter(index++).Value = stream.BitRate;
  3153. _saveStreamCommand.GetParameter(index++).Value = stream.Channels;
  3154. _saveStreamCommand.GetParameter(index++).Value = stream.SampleRate;
  3155. _saveStreamCommand.GetParameter(index++).Value = stream.IsDefault;
  3156. _saveStreamCommand.GetParameter(index++).Value = stream.IsForced;
  3157. _saveStreamCommand.GetParameter(index++).Value = stream.IsExternal;
  3158. _saveStreamCommand.GetParameter(index++).Value = stream.Width;
  3159. _saveStreamCommand.GetParameter(index++).Value = stream.Height;
  3160. _saveStreamCommand.GetParameter(index++).Value = stream.AverageFrameRate;
  3161. _saveStreamCommand.GetParameter(index++).Value = stream.RealFrameRate;
  3162. _saveStreamCommand.GetParameter(index++).Value = stream.Level;
  3163. _saveStreamCommand.GetParameter(index++).Value = stream.PixelFormat;
  3164. _saveStreamCommand.GetParameter(index++).Value = stream.BitDepth;
  3165. _saveStreamCommand.GetParameter(index++).Value = stream.IsAnamorphic;
  3166. _saveStreamCommand.GetParameter(index++).Value = stream.RefFrames;
  3167. _saveStreamCommand.GetParameter(index++).Value = stream.CodecTag;
  3168. _saveStreamCommand.GetParameter(index++).Value = stream.Comment;
  3169. _saveStreamCommand.GetParameter(index++).Value = stream.NalLengthSize;
  3170. _saveStreamCommand.GetParameter(index++).Value = stream.IsAVC;
  3171. _saveStreamCommand.GetParameter(index++).Value = stream.Title;
  3172. _saveStreamCommand.GetParameter(index++).Value = stream.TimeBase;
  3173. _saveStreamCommand.GetParameter(index++).Value = stream.CodecTimeBase;
  3174. _saveStreamCommand.Transaction = transaction;
  3175. _saveStreamCommand.ExecuteNonQuery();
  3176. }
  3177. transaction.Commit();
  3178. }
  3179. catch (OperationCanceledException)
  3180. {
  3181. if (transaction != null)
  3182. {
  3183. transaction.Rollback();
  3184. }
  3185. throw;
  3186. }
  3187. catch (Exception e)
  3188. {
  3189. Logger.ErrorException("Failed to save media streams:", e);
  3190. if (transaction != null)
  3191. {
  3192. transaction.Rollback();
  3193. }
  3194. throw;
  3195. }
  3196. finally
  3197. {
  3198. if (transaction != null)
  3199. {
  3200. transaction.Dispose();
  3201. }
  3202. WriteLock.Release();
  3203. }
  3204. }
  3205. /// <summary>
  3206. /// Gets the chapter.
  3207. /// </summary>
  3208. /// <param name="reader">The reader.</param>
  3209. /// <returns>ChapterInfo.</returns>
  3210. private MediaStream GetMediaStream(IDataReader reader)
  3211. {
  3212. var item = new MediaStream
  3213. {
  3214. Index = reader.GetInt32(1)
  3215. };
  3216. item.Type = (MediaStreamType)Enum.Parse(typeof(MediaStreamType), reader.GetString(2), true);
  3217. if (!reader.IsDBNull(3))
  3218. {
  3219. item.Codec = reader.GetString(3);
  3220. }
  3221. if (!reader.IsDBNull(4))
  3222. {
  3223. item.Language = reader.GetString(4);
  3224. }
  3225. if (!reader.IsDBNull(5))
  3226. {
  3227. item.ChannelLayout = reader.GetString(5);
  3228. }
  3229. if (!reader.IsDBNull(6))
  3230. {
  3231. item.Profile = reader.GetString(6);
  3232. }
  3233. if (!reader.IsDBNull(7))
  3234. {
  3235. item.AspectRatio = reader.GetString(7);
  3236. }
  3237. if (!reader.IsDBNull(8))
  3238. {
  3239. item.Path = reader.GetString(8);
  3240. }
  3241. item.IsInterlaced = reader.GetBoolean(9);
  3242. if (!reader.IsDBNull(10))
  3243. {
  3244. item.BitRate = reader.GetInt32(10);
  3245. }
  3246. if (!reader.IsDBNull(11))
  3247. {
  3248. item.Channels = reader.GetInt32(11);
  3249. }
  3250. if (!reader.IsDBNull(12))
  3251. {
  3252. item.SampleRate = reader.GetInt32(12);
  3253. }
  3254. item.IsDefault = reader.GetBoolean(13);
  3255. item.IsForced = reader.GetBoolean(14);
  3256. item.IsExternal = reader.GetBoolean(15);
  3257. if (!reader.IsDBNull(16))
  3258. {
  3259. item.Width = reader.GetInt32(16);
  3260. }
  3261. if (!reader.IsDBNull(17))
  3262. {
  3263. item.Height = reader.GetInt32(17);
  3264. }
  3265. if (!reader.IsDBNull(18))
  3266. {
  3267. item.AverageFrameRate = reader.GetFloat(18);
  3268. }
  3269. if (!reader.IsDBNull(19))
  3270. {
  3271. item.RealFrameRate = reader.GetFloat(19);
  3272. }
  3273. if (!reader.IsDBNull(20))
  3274. {
  3275. item.Level = reader.GetFloat(20);
  3276. }
  3277. if (!reader.IsDBNull(21))
  3278. {
  3279. item.PixelFormat = reader.GetString(21);
  3280. }
  3281. if (!reader.IsDBNull(22))
  3282. {
  3283. item.BitDepth = reader.GetInt32(22);
  3284. }
  3285. if (!reader.IsDBNull(23))
  3286. {
  3287. item.IsAnamorphic = reader.GetBoolean(23);
  3288. }
  3289. if (!reader.IsDBNull(24))
  3290. {
  3291. item.RefFrames = reader.GetInt32(24);
  3292. }
  3293. if (!reader.IsDBNull(25))
  3294. {
  3295. item.CodecTag = reader.GetString(25);
  3296. }
  3297. if (!reader.IsDBNull(26))
  3298. {
  3299. item.Comment = reader.GetString(26);
  3300. }
  3301. if (!reader.IsDBNull(27))
  3302. {
  3303. item.NalLengthSize = reader.GetString(27);
  3304. }
  3305. if (!reader.IsDBNull(28))
  3306. {
  3307. item.IsAVC = reader.GetBoolean(28);
  3308. }
  3309. if (!reader.IsDBNull(29))
  3310. {
  3311. item.Title = reader.GetString(29);
  3312. }
  3313. if (!reader.IsDBNull(30))
  3314. {
  3315. item.TimeBase = reader.GetString(30);
  3316. }
  3317. if (!reader.IsDBNull(31))
  3318. {
  3319. item.CodecTimeBase = reader.GetString(31);
  3320. }
  3321. return item;
  3322. }
  3323. }
  3324. }