SqliteItemRepository.cs 210 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032203320342035203620372038203920402041204220432044204520462047204820492050205120522053205420552056205720582059206020612062206320642065206620672068206920702071207220732074207520762077207820792080208120822083208420852086208720882089209020912092209320942095209620972098209921002101210221032104210521062107210821092110211121122113211421152116211721182119212021212122212321242125212621272128212921302131213221332134213521362137213821392140214121422143214421452146214721482149215021512152215321542155215621572158215921602161216221632164216521662167216821692170217121722173217421752176217721782179218021812182218321842185218621872188218921902191219221932194219521962197219821992200220122022203220422052206220722082209221022112212221322142215221622172218221922202221222222232224222522262227222822292230223122322233223422352236223722382239224022412242224322442245224622472248224922502251225222532254225522562257225822592260226122622263226422652266226722682269227022712272227322742275227622772278227922802281228222832284228522862287228822892290229122922293229422952296229722982299230023012302230323042305230623072308230923102311231223132314231523162317231823192320232123222323232423252326232723282329233023312332233323342335233623372338233923402341234223432344234523462347234823492350235123522353235423552356235723582359236023612362236323642365236623672368236923702371237223732374237523762377237823792380238123822383238423852386238723882389239023912392239323942395239623972398239924002401240224032404240524062407240824092410241124122413241424152416241724182419242024212422242324242425242624272428242924302431243224332434243524362437243824392440244124422443244424452446244724482449245024512452245324542455245624572458245924602461246224632464246524662467246824692470247124722473247424752476247724782479248024812482248324842485248624872488248924902491249224932494249524962497249824992500250125022503250425052506250725082509251025112512251325142515251625172518251925202521252225232524252525262527252825292530253125322533253425352536253725382539254025412542254325442545254625472548254925502551255225532554255525562557255825592560256125622563256425652566256725682569257025712572257325742575257625772578257925802581258225832584258525862587258825892590259125922593259425952596259725982599260026012602260326042605260626072608260926102611261226132614261526162617261826192620262126222623262426252626262726282629263026312632263326342635263626372638263926402641264226432644264526462647264826492650265126522653265426552656265726582659266026612662266326642665266626672668266926702671267226732674267526762677267826792680268126822683268426852686268726882689269026912692269326942695269626972698269927002701270227032704270527062707270827092710271127122713271427152716271727182719272027212722272327242725272627272728272927302731273227332734273527362737273827392740274127422743274427452746274727482749275027512752275327542755275627572758275927602761276227632764276527662767276827692770277127722773277427752776277727782779278027812782278327842785278627872788278927902791279227932794279527962797279827992800280128022803280428052806280728082809281028112812281328142815281628172818281928202821282228232824282528262827282828292830283128322833283428352836283728382839284028412842284328442845284628472848284928502851285228532854285528562857285828592860286128622863286428652866286728682869287028712872287328742875287628772878287928802881288228832884288528862887288828892890289128922893289428952896289728982899290029012902290329042905290629072908290929102911291229132914291529162917291829192920292129222923292429252926292729282929293029312932293329342935293629372938293929402941294229432944294529462947294829492950295129522953295429552956295729582959296029612962296329642965296629672968296929702971297229732974297529762977297829792980298129822983298429852986298729882989299029912992299329942995299629972998299930003001300230033004300530063007300830093010301130123013301430153016301730183019302030213022302330243025302630273028302930303031303230333034303530363037303830393040304130423043304430453046304730483049305030513052305330543055305630573058305930603061306230633064306530663067306830693070307130723073307430753076307730783079308030813082308330843085308630873088308930903091309230933094309530963097309830993100310131023103310431053106310731083109311031113112311331143115311631173118311931203121312231233124312531263127312831293130313131323133313431353136313731383139314031413142314331443145314631473148314931503151315231533154315531563157315831593160316131623163316431653166316731683169317031713172317331743175317631773178317931803181318231833184318531863187318831893190319131923193319431953196319731983199320032013202320332043205320632073208320932103211321232133214321532163217321832193220322132223223322432253226322732283229323032313232323332343235323632373238323932403241324232433244324532463247324832493250325132523253325432553256325732583259326032613262326332643265326632673268326932703271327232733274327532763277327832793280328132823283328432853286328732883289329032913292329332943295329632973298329933003301330233033304330533063307330833093310331133123313331433153316331733183319332033213322332333243325332633273328332933303331333233333334333533363337333833393340334133423343334433453346334733483349335033513352335333543355335633573358335933603361336233633364336533663367336833693370337133723373337433753376337733783379338033813382338333843385338633873388338933903391339233933394339533963397339833993400340134023403340434053406340734083409341034113412341334143415341634173418341934203421342234233424342534263427342834293430343134323433343434353436343734383439344034413442344334443445344634473448344934503451345234533454345534563457345834593460346134623463346434653466346734683469347034713472347334743475347634773478347934803481348234833484348534863487348834893490349134923493349434953496349734983499350035013502350335043505350635073508350935103511351235133514351535163517351835193520352135223523352435253526352735283529353035313532353335343535353635373538353935403541354235433544354535463547354835493550355135523553355435553556355735583559356035613562356335643565356635673568356935703571357235733574357535763577357835793580358135823583358435853586358735883589359035913592359335943595359635973598359936003601360236033604360536063607360836093610361136123613361436153616361736183619362036213622362336243625362636273628362936303631363236333634363536363637363836393640364136423643364436453646364736483649365036513652365336543655365636573658365936603661366236633664366536663667366836693670367136723673367436753676367736783679368036813682368336843685368636873688368936903691369236933694369536963697369836993700370137023703370437053706370737083709371037113712371337143715371637173718371937203721372237233724372537263727372837293730373137323733373437353736373737383739374037413742374337443745374637473748374937503751375237533754375537563757375837593760376137623763376437653766376737683769377037713772377337743775377637773778377937803781378237833784378537863787378837893790379137923793379437953796379737983799380038013802380338043805380638073808380938103811381238133814381538163817381838193820382138223823382438253826382738283829383038313832383338343835383638373838383938403841384238433844384538463847384838493850385138523853385438553856385738583859386038613862386338643865386638673868386938703871387238733874387538763877387838793880388138823883388438853886388738883889389038913892389338943895389638973898389939003901390239033904390539063907390839093910391139123913391439153916391739183919392039213922392339243925392639273928392939303931393239333934393539363937393839393940394139423943394439453946394739483949395039513952395339543955395639573958395939603961396239633964396539663967396839693970397139723973397439753976397739783979398039813982398339843985398639873988398939903991399239933994399539963997399839994000400140024003400440054006400740084009401040114012401340144015401640174018401940204021402240234024402540264027402840294030403140324033403440354036403740384039404040414042404340444045404640474048404940504051405240534054405540564057405840594060406140624063406440654066406740684069407040714072407340744075407640774078407940804081408240834084408540864087408840894090409140924093409440954096409740984099410041014102410341044105410641074108410941104111411241134114411541164117411841194120412141224123412441254126412741284129413041314132413341344135413641374138413941404141414241434144414541464147414841494150415141524153415441554156415741584159416041614162416341644165416641674168416941704171417241734174417541764177417841794180418141824183418441854186418741884189419041914192419341944195419641974198419942004201420242034204420542064207420842094210421142124213421442154216421742184219422042214222422342244225422642274228422942304231423242334234423542364237423842394240424142424243424442454246424742484249425042514252425342544255425642574258425942604261426242634264426542664267426842694270427142724273427442754276427742784279428042814282428342844285428642874288428942904291429242934294429542964297429842994300430143024303430443054306430743084309431043114312431343144315431643174318431943204321432243234324432543264327432843294330433143324333433443354336433743384339434043414342434343444345434643474348434943504351435243534354435543564357435843594360436143624363436443654366436743684369437043714372437343744375437643774378437943804381438243834384438543864387438843894390439143924393439443954396439743984399440044014402440344044405440644074408440944104411441244134414441544164417441844194420442144224423442444254426442744284429443044314432443344344435443644374438443944404441444244434444444544464447444844494450445144524453445444554456445744584459446044614462446344644465446644674468446944704471447244734474447544764477447844794480448144824483448444854486448744884489449044914492449344944495449644974498449945004501450245034504450545064507450845094510451145124513451445154516451745184519452045214522452345244525452645274528452945304531453245334534453545364537453845394540454145424543454445454546454745484549455045514552455345544555455645574558455945604561456245634564456545664567456845694570457145724573457445754576457745784579458045814582458345844585458645874588458945904591459245934594459545964597459845994600460146024603460446054606460746084609461046114612461346144615461646174618461946204621462246234624462546264627462846294630463146324633463446354636463746384639464046414642464346444645464646474648464946504651465246534654465546564657465846594660466146624663466446654666466746684669467046714672467346744675467646774678467946804681468246834684468546864687468846894690469146924693469446954696469746984699470047014702470347044705470647074708470947104711471247134714471547164717471847194720472147224723472447254726472747284729473047314732473347344735473647374738473947404741474247434744474547464747474847494750475147524753475447554756475747584759476047614762476347644765476647674768476947704771477247734774477547764777477847794780478147824783478447854786478747884789479047914792479347944795479647974798479948004801480248034804480548064807480848094810481148124813481448154816481748184819482048214822482348244825482648274828482948304831483248334834483548364837483848394840484148424843484448454846484748484849485048514852485348544855485648574858485948604861486248634864486548664867486848694870487148724873487448754876487748784879488048814882488348844885488648874888488948904891489248934894489548964897489848994900490149024903490449054906490749084909491049114912491349144915491649174918491949204921492249234924492549264927492849294930493149324933493449354936493749384939494049414942494349444945494649474948494949504951495249534954495549564957495849594960496149624963496449654966496749684969497049714972497349744975497649774978497949804981498249834984498549864987498849894990499149924993499449954996499749984999500050015002500350045005500650075008500950105011501250135014501550165017501850195020502150225023502450255026502750285029503050315032503350345035503650375038503950405041504250435044504550465047504850495050505150525053505450555056505750585059506050615062506350645065506650675068506950705071507250735074507550765077507850795080508150825083508450855086508750885089509050915092509350945095509650975098509951005101510251035104510551065107510851095110511151125113511451155116511751185119512051215122512351245125512651275128512951305131513251335134513551365137513851395140514151425143514451455146514751485149515051515152515351545155515651575158515951605161516251635164516551665167516851695170517151725173517451755176517751785179518051815182518351845185518651875188518951905191519251935194519551965197519851995200520152025203520452055206520752085209521052115212521352145215521652175218521952205221522252235224522552265227522852295230523152325233523452355236523752385239524052415242524352445245524652475248524952505251525252535254525552565257525852595260526152625263526452655266526752685269527052715272527352745275527652775278527952805281528252835284528552865287528852895290529152925293529452955296529752985299530053015302530353045305530653075308530953105311531253135314531553165317531853195320532153225323532453255326532753285329533053315332533353345335533653375338533953405341534253435344534553465347534853495350535153525353535453555356535753585359536053615362536353645365536653675368536953705371537253735374537553765377537853795380538153825383538453855386538753885389539053915392539353945395539653975398539954005401540254035404540554065407540854095410541154125413541454155416541754185419542054215422542354245425542654275428542954305431543254335434543554365437543854395440544154425443544454455446544754485449545054515452545354545455545654575458545954605461546254635464546554665467546854695470547154725473547454755476547754785479548054815482548354845485548654875488548954905491549254935494549554965497549854995500550155025503550455055506550755085509551055115512551355145515551655175518551955205521552255235524552555265527552855295530553155325533553455355536553755385539554055415542554355445545554655475548554955505551555255535554555555565557555855595560556155625563556455655566556755685569557055715572557355745575557655775578557955805581558255835584558555865587558855895590559155925593559455955596559755985599560056015602560356045605560656075608560956105611561256135614561556165617561856195620562156225623562456255626562756285629563056315632563356345635563656375638563956405641564256435644564556465647564856495650565156525653565456555656565756585659566056615662566356645665566656675668566956705671567256735674567556765677567856795680568156825683568456855686568756885689569056915692569356945695569656975698569957005701570257035704570557065707570857095710571157125713571457155716571757185719572057215722572357245725572657275728572957305731573257335734573557365737573857395740574157425743574457455746574757485749575057515752575357545755575657575758575957605761576257635764576557665767576857695770577157725773577457755776577757785779578057815782578357845785578657875788578957905791579257935794579557965797579857995800580158025803580458055806580758085809581058115812581358145815581658175818581958205821582258235824582558265827582858295830583158325833583458355836583758385839584058415842584358445845584658475848584958505851585258535854585558565857585858595860586158625863586458655866586758685869587058715872587358745875587658775878587958805881588258835884588558865887588858895890
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Globalization;
  4. using System.IO;
  5. using System.Linq;
  6. using System.Runtime.Serialization;
  7. using System.Text;
  8. using System.Threading;
  9. using System.Threading.Tasks;
  10. using Emby.Server.Implementations.Devices;
  11. using Emby.Server.Implementations.Playlists;
  12. using MediaBrowser.Controller.Channels;
  13. using MediaBrowser.Controller.Collections;
  14. using MediaBrowser.Controller.Configuration;
  15. using MediaBrowser.Controller.Entities;
  16. using MediaBrowser.Controller.Entities.Audio;
  17. using MediaBrowser.Controller.Entities.Movies;
  18. using MediaBrowser.Controller.Entities.TV;
  19. using MediaBrowser.Controller.Extensions;
  20. using MediaBrowser.Controller.LiveTv;
  21. using MediaBrowser.Controller.Persistence;
  22. using MediaBrowser.Controller.Playlists;
  23. using MediaBrowser.Model.Dto;
  24. using MediaBrowser.Model.Entities;
  25. using MediaBrowser.Model.IO;
  26. using MediaBrowser.Model.LiveTv;
  27. using MediaBrowser.Model.Logging;
  28. using MediaBrowser.Model.Querying;
  29. using MediaBrowser.Model.Serialization;
  30. using MediaBrowser.Model.Reflection;
  31. using SQLitePCL.pretty;
  32. using MediaBrowser.Model.System;
  33. using MediaBrowser.Model.Threading;
  34. using MediaBrowser.Model.Extensions;
  35. namespace Emby.Server.Implementations.Data
  36. {
  37. /// <summary>
  38. /// Class SQLiteItemRepository
  39. /// </summary>
  40. public class SqliteItemRepository : BaseSqliteRepository, IItemRepository
  41. {
  42. private readonly TypeMapper _typeMapper;
  43. /// <summary>
  44. /// Gets the name of the repository
  45. /// </summary>
  46. /// <value>The name.</value>
  47. public string Name
  48. {
  49. get
  50. {
  51. return "SQLite";
  52. }
  53. }
  54. /// <summary>
  55. /// Gets the json serializer.
  56. /// </summary>
  57. /// <value>The json serializer.</value>
  58. private readonly IJsonSerializer _jsonSerializer;
  59. /// <summary>
  60. /// The _app paths
  61. /// </summary>
  62. private readonly IServerConfigurationManager _config;
  63. private readonly string _criticReviewsPath;
  64. private readonly IMemoryStreamFactory _memoryStreamProvider;
  65. private readonly IFileSystem _fileSystem;
  66. private readonly IEnvironmentInfo _environmentInfo;
  67. private readonly ITimerFactory _timerFactory;
  68. private ITimer _shrinkMemoryTimer;
  69. /// <summary>
  70. /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class.
  71. /// </summary>
  72. public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogger logger, IMemoryStreamFactory memoryStreamProvider, IAssemblyInfo assemblyInfo, IFileSystem fileSystem, IEnvironmentInfo environmentInfo, ITimerFactory timerFactory)
  73. : base(logger)
  74. {
  75. if (config == null)
  76. {
  77. throw new ArgumentNullException("config");
  78. }
  79. if (jsonSerializer == null)
  80. {
  81. throw new ArgumentNullException("jsonSerializer");
  82. }
  83. _config = config;
  84. _jsonSerializer = jsonSerializer;
  85. _memoryStreamProvider = memoryStreamProvider;
  86. _fileSystem = fileSystem;
  87. _environmentInfo = environmentInfo;
  88. _timerFactory = timerFactory;
  89. _typeMapper = new TypeMapper(assemblyInfo);
  90. _criticReviewsPath = Path.Combine(_config.ApplicationPaths.DataPath, "critic-reviews");
  91. DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db");
  92. }
  93. private const string ChaptersTableName = "Chapters2";
  94. protected override int? CacheSize
  95. {
  96. get
  97. {
  98. return 20000;
  99. }
  100. }
  101. protected override bool EnableTempStoreMemory
  102. {
  103. get
  104. {
  105. return true;
  106. }
  107. }
  108. protected override void CloseConnection()
  109. {
  110. if (_shrinkMemoryTimer != null)
  111. {
  112. _shrinkMemoryTimer.Dispose();
  113. _shrinkMemoryTimer = null;
  114. }
  115. base.CloseConnection();
  116. }
  117. /// <summary>
  118. /// Opens the connection to the database
  119. /// </summary>
  120. public void Initialize(SqliteUserDataRepository userDataRepo)
  121. {
  122. using (var connection = CreateConnection())
  123. {
  124. RunDefaultInitialization(connection);
  125. var createMediaStreamsTableCommand
  126. = "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))";
  127. string[] queries = {
  128. "PRAGMA locking_mode=EXCLUSIVE",
  129. "create table if not exists TypedBaseItems (guid GUID primary key NOT NULL, type TEXT NOT NULL, data BLOB NULL, ParentId GUID NULL, Path TEXT NULL)",
  130. "create table if not exists AncestorIds (ItemId GUID, AncestorId GUID, AncestorIdText TEXT, PRIMARY KEY (ItemId, AncestorId))",
  131. "create index if not exists idx_AncestorIds1 on AncestorIds(AncestorId)",
  132. "create index if not exists idx_AncestorIds5 on AncestorIds(AncestorIdText,ItemId)",
  133. "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT, CleanValue TEXT)",
  134. "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)",
  135. "drop index if exists idxPeopleItemId",
  136. "create index if not exists idxPeopleItemId1 on People(ItemId,ListOrder)",
  137. "create index if not exists idxPeopleName on People(Name)",
  138. "create table if not exists "+ChaptersTableName+" (ItemId GUID, ChapterIndex INT, StartPositionTicks BIGINT, Name TEXT, ImagePath TEXT, PRIMARY KEY (ItemId, ChapterIndex))",
  139. createMediaStreamsTableCommand,
  140. "create index if not exists idx_mediastreams1 on mediastreams(ItemId)",
  141. "pragma shrink_memory"
  142. };
  143. connection.RunQueries(queries);
  144. connection.RunInTransaction(db =>
  145. {
  146. var existingColumnNames = GetColumnNames(db, "AncestorIds");
  147. AddColumn(db, "AncestorIds", "AncestorIdText", "Text", existingColumnNames);
  148. existingColumnNames = GetColumnNames(db, "TypedBaseItems");
  149. AddColumn(db, "TypedBaseItems", "Path", "Text", existingColumnNames);
  150. AddColumn(db, "TypedBaseItems", "StartDate", "DATETIME", existingColumnNames);
  151. AddColumn(db, "TypedBaseItems", "EndDate", "DATETIME", existingColumnNames);
  152. AddColumn(db, "TypedBaseItems", "ChannelId", "Text", existingColumnNames);
  153. AddColumn(db, "TypedBaseItems", "IsMovie", "BIT", existingColumnNames);
  154. AddColumn(db, "TypedBaseItems", "IsSports", "BIT", existingColumnNames);
  155. AddColumn(db, "TypedBaseItems", "IsKids", "BIT", existingColumnNames);
  156. AddColumn(db, "TypedBaseItems", "CommunityRating", "Float", existingColumnNames);
  157. AddColumn(db, "TypedBaseItems", "CustomRating", "Text", existingColumnNames);
  158. AddColumn(db, "TypedBaseItems", "IndexNumber", "INT", existingColumnNames);
  159. AddColumn(db, "TypedBaseItems", "IsLocked", "BIT", existingColumnNames);
  160. AddColumn(db, "TypedBaseItems", "Name", "Text", existingColumnNames);
  161. AddColumn(db, "TypedBaseItems", "OfficialRating", "Text", existingColumnNames);
  162. AddColumn(db, "TypedBaseItems", "MediaType", "Text", existingColumnNames);
  163. AddColumn(db, "TypedBaseItems", "Overview", "Text", existingColumnNames);
  164. AddColumn(db, "TypedBaseItems", "ParentIndexNumber", "INT", existingColumnNames);
  165. AddColumn(db, "TypedBaseItems", "PremiereDate", "DATETIME", existingColumnNames);
  166. AddColumn(db, "TypedBaseItems", "ProductionYear", "INT", existingColumnNames);
  167. AddColumn(db, "TypedBaseItems", "ParentId", "GUID", existingColumnNames);
  168. AddColumn(db, "TypedBaseItems", "Genres", "Text", existingColumnNames);
  169. AddColumn(db, "TypedBaseItems", "SortName", "Text", existingColumnNames);
  170. AddColumn(db, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames);
  171. AddColumn(db, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames);
  172. AddColumn(db, "TypedBaseItems", "HomePageUrl", "Text", existingColumnNames);
  173. AddColumn(db, "TypedBaseItems", "DateCreated", "DATETIME", existingColumnNames);
  174. AddColumn(db, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames);
  175. AddColumn(db, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames);
  176. AddColumn(db, "TypedBaseItems", "IsLive", "BIT", existingColumnNames);
  177. AddColumn(db, "TypedBaseItems", "IsNews", "BIT", existingColumnNames);
  178. AddColumn(db, "TypedBaseItems", "IsPremiere", "BIT", existingColumnNames);
  179. AddColumn(db, "TypedBaseItems", "EpisodeTitle", "Text", existingColumnNames);
  180. AddColumn(db, "TypedBaseItems", "IsRepeat", "BIT", existingColumnNames);
  181. AddColumn(db, "TypedBaseItems", "PreferredMetadataLanguage", "Text", existingColumnNames);
  182. AddColumn(db, "TypedBaseItems", "PreferredMetadataCountryCode", "Text", existingColumnNames);
  183. AddColumn(db, "TypedBaseItems", "IsHD", "BIT", existingColumnNames);
  184. AddColumn(db, "TypedBaseItems", "ExternalEtag", "Text", existingColumnNames);
  185. AddColumn(db, "TypedBaseItems", "DateLastRefreshed", "DATETIME", existingColumnNames);
  186. AddColumn(db, "TypedBaseItems", "DateLastSaved", "DATETIME", existingColumnNames);
  187. AddColumn(db, "TypedBaseItems", "IsInMixedFolder", "BIT", existingColumnNames);
  188. AddColumn(db, "TypedBaseItems", "LockedFields", "Text", existingColumnNames);
  189. AddColumn(db, "TypedBaseItems", "Studios", "Text", existingColumnNames);
  190. AddColumn(db, "TypedBaseItems", "Audio", "Text", existingColumnNames);
  191. AddColumn(db, "TypedBaseItems", "ExternalServiceId", "Text", existingColumnNames);
  192. AddColumn(db, "TypedBaseItems", "Tags", "Text", existingColumnNames);
  193. AddColumn(db, "TypedBaseItems", "IsFolder", "BIT", existingColumnNames);
  194. AddColumn(db, "TypedBaseItems", "InheritedParentalRatingValue", "INT", existingColumnNames);
  195. AddColumn(db, "TypedBaseItems", "UnratedType", "Text", existingColumnNames);
  196. AddColumn(db, "TypedBaseItems", "TopParentId", "Text", existingColumnNames);
  197. AddColumn(db, "TypedBaseItems", "TrailerTypes", "Text", existingColumnNames);
  198. AddColumn(db, "TypedBaseItems", "CriticRating", "Float", existingColumnNames);
  199. AddColumn(db, "TypedBaseItems", "CleanName", "Text", existingColumnNames);
  200. AddColumn(db, "TypedBaseItems", "PresentationUniqueKey", "Text", existingColumnNames);
  201. AddColumn(db, "TypedBaseItems", "OriginalTitle", "Text", existingColumnNames);
  202. AddColumn(db, "TypedBaseItems", "PrimaryVersionId", "Text", existingColumnNames);
  203. AddColumn(db, "TypedBaseItems", "DateLastMediaAdded", "DATETIME", existingColumnNames);
  204. AddColumn(db, "TypedBaseItems", "Album", "Text", existingColumnNames);
  205. AddColumn(db, "TypedBaseItems", "IsVirtualItem", "BIT", existingColumnNames);
  206. AddColumn(db, "TypedBaseItems", "SeriesName", "Text", existingColumnNames);
  207. AddColumn(db, "TypedBaseItems", "UserDataKey", "Text", existingColumnNames);
  208. AddColumn(db, "TypedBaseItems", "SeasonName", "Text", existingColumnNames);
  209. AddColumn(db, "TypedBaseItems", "SeasonId", "GUID", existingColumnNames);
  210. AddColumn(db, "TypedBaseItems", "SeriesId", "GUID", existingColumnNames);
  211. AddColumn(db, "TypedBaseItems", "ExternalSeriesId", "Text", existingColumnNames);
  212. AddColumn(db, "TypedBaseItems", "Tagline", "Text", existingColumnNames);
  213. AddColumn(db, "TypedBaseItems", "ProviderIds", "Text", existingColumnNames);
  214. AddColumn(db, "TypedBaseItems", "Images", "Text", existingColumnNames);
  215. AddColumn(db, "TypedBaseItems", "ProductionLocations", "Text", existingColumnNames);
  216. AddColumn(db, "TypedBaseItems", "ThemeSongIds", "Text", existingColumnNames);
  217. AddColumn(db, "TypedBaseItems", "ThemeVideoIds", "Text", existingColumnNames);
  218. AddColumn(db, "TypedBaseItems", "TotalBitrate", "INT", existingColumnNames);
  219. AddColumn(db, "TypedBaseItems", "ExtraType", "Text", existingColumnNames);
  220. AddColumn(db, "TypedBaseItems", "Artists", "Text", existingColumnNames);
  221. AddColumn(db, "TypedBaseItems", "AlbumArtists", "Text", existingColumnNames);
  222. AddColumn(db, "TypedBaseItems", "ExternalId", "Text", existingColumnNames);
  223. AddColumn(db, "TypedBaseItems", "SeriesPresentationUniqueKey", "Text", existingColumnNames);
  224. AddColumn(db, "TypedBaseItems", "ShowId", "Text", existingColumnNames);
  225. AddColumn(db, "TypedBaseItems", "OwnerId", "Text", existingColumnNames);
  226. existingColumnNames = GetColumnNames(db, "ItemValues");
  227. AddColumn(db, "ItemValues", "CleanValue", "Text", existingColumnNames);
  228. existingColumnNames = GetColumnNames(db, ChaptersTableName);
  229. AddColumn(db, ChaptersTableName, "ImageDateModified", "DATETIME", existingColumnNames);
  230. existingColumnNames = GetColumnNames(db, "MediaStreams");
  231. AddColumn(db, "MediaStreams", "IsAvc", "BIT", existingColumnNames);
  232. AddColumn(db, "MediaStreams", "TimeBase", "TEXT", existingColumnNames);
  233. AddColumn(db, "MediaStreams", "CodecTimeBase", "TEXT", existingColumnNames);
  234. AddColumn(db, "MediaStreams", "Title", "TEXT", existingColumnNames);
  235. AddColumn(db, "MediaStreams", "NalLengthSize", "TEXT", existingColumnNames);
  236. AddColumn(db, "MediaStreams", "Comment", "TEXT", existingColumnNames);
  237. AddColumn(db, "MediaStreams", "CodecTag", "TEXT", existingColumnNames);
  238. AddColumn(db, "MediaStreams", "PixelFormat", "TEXT", existingColumnNames);
  239. AddColumn(db, "MediaStreams", "BitDepth", "INT", existingColumnNames);
  240. AddColumn(db, "MediaStreams", "RefFrames", "INT", existingColumnNames);
  241. AddColumn(db, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames);
  242. AddColumn(db, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames);
  243. }, TransactionMode);
  244. string[] postQueries =
  245. {
  246. // obsolete
  247. "drop index if exists idx_TypedBaseItems",
  248. "drop index if exists idx_mediastreams",
  249. "drop index if exists idx_"+ChaptersTableName,
  250. "drop index if exists idx_UserDataKeys1",
  251. "drop index if exists idx_UserDataKeys2",
  252. "drop index if exists idx_TypeTopParentId3",
  253. "drop index if exists idx_TypeTopParentId2",
  254. "drop index if exists idx_TypeTopParentId4",
  255. "drop index if exists idx_Type",
  256. "drop index if exists idx_TypeTopParentId",
  257. "drop index if exists idx_GuidType",
  258. "drop index if exists idx_TopParentId",
  259. "drop index if exists idx_TypeTopParentId6",
  260. "drop index if exists idx_ItemValues2",
  261. "drop index if exists Idx_ProviderIds",
  262. "drop index if exists idx_ItemValues3",
  263. "drop index if exists idx_ItemValues4",
  264. "drop index if exists idx_ItemValues5",
  265. "drop index if exists idx_UserDataKeys3",
  266. "drop table if exists UserDataKeys",
  267. "drop table if exists ProviderIds",
  268. "drop index if exists Idx_ProviderIds1",
  269. "drop table if exists Images",
  270. "drop index if exists idx_Images",
  271. "drop index if exists idx_TypeSeriesPresentationUniqueKey",
  272. "drop index if exists idx_SeriesPresentationUniqueKey",
  273. "drop index if exists idx_TypeSeriesPresentationUniqueKey2",
  274. "drop index if exists idx_AncestorIds3",
  275. "drop index if exists idx_AncestorIds4",
  276. "drop index if exists idx_AncestorIds2",
  277. "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)",
  278. "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)",
  279. "create index if not exists idx_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)",
  280. "create index if not exists idx_GuidTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,Type,IsFolder,IsVirtualItem)",
  281. //"create index if not exists idx_GuidMediaTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,MediaType,IsFolder,IsVirtualItem)",
  282. "create index if not exists idx_CleanNameType on TypedBaseItems(CleanName,Type)",
  283. // covering index
  284. "create index if not exists idx_TopParentIdGuid on TypedBaseItems(TopParentId,Guid)",
  285. // series
  286. "create index if not exists idx_TypeSeriesPresentationUniqueKey1 on TypedBaseItems(Type,SeriesPresentationUniqueKey,PresentationUniqueKey,SortName)",
  287. // series counts
  288. // seriesdateplayed sort order
  289. "create index if not exists idx_TypeSeriesPresentationUniqueKey3 on TypedBaseItems(SeriesPresentationUniqueKey,Type,IsFolder,IsVirtualItem)",
  290. // live tv programs
  291. "create index if not exists idx_TypeTopParentIdStartDate on TypedBaseItems(Type,TopParentId,StartDate)",
  292. // covering index for getitemvalues
  293. "create index if not exists idx_TypeTopParentIdGuid on TypedBaseItems(Type,TopParentId,Guid)",
  294. // used by movie suggestions
  295. "create index if not exists idx_TypeTopParentIdGroup on TypedBaseItems(Type,TopParentId,PresentationUniqueKey)",
  296. "create index if not exists idx_TypeTopParentId5 on TypedBaseItems(TopParentId,IsVirtualItem)",
  297. // latest items
  298. "create index if not exists idx_TypeTopParentId9 on TypedBaseItems(TopParentId,Type,IsVirtualItem,PresentationUniqueKey,DateCreated)",
  299. "create index if not exists idx_TypeTopParentId8 on TypedBaseItems(TopParentId,IsFolder,IsVirtualItem,PresentationUniqueKey,DateCreated)",
  300. // resume
  301. "create index if not exists idx_TypeTopParentId7 on TypedBaseItems(TopParentId,MediaType,IsVirtualItem,PresentationUniqueKey)",
  302. // items by name
  303. "create index if not exists idx_ItemValues6 on ItemValues(ItemId,Type,CleanValue)",
  304. "create index if not exists idx_ItemValues7 on ItemValues(Type,CleanValue,ItemId)",
  305. // Used to update inherited tags
  306. "create index if not exists idx_ItemValues8 on ItemValues(Type, ItemId, Value)",
  307. };
  308. connection.RunQueries(postQueries);
  309. //await Vacuum(_connection).ConfigureAwait(false);
  310. }
  311. userDataRepo.Initialize(WriteLock, _connection);
  312. _shrinkMemoryTimer = _timerFactory.Create(OnShrinkMemoryTimerCallback, null, TimeSpan.FromMinutes(1), TimeSpan.FromMinutes(30));
  313. }
  314. private void OnShrinkMemoryTimerCallback(object state)
  315. {
  316. try
  317. {
  318. using (WriteLock.Write())
  319. {
  320. using (var connection = CreateConnection())
  321. {
  322. connection.RunQueries(new string[]
  323. {
  324. "pragma shrink_memory"
  325. });
  326. }
  327. }
  328. GC.Collect();
  329. }
  330. catch (Exception ex)
  331. {
  332. Logger.ErrorException("Error running shrink memory", ex);
  333. }
  334. }
  335. private readonly string[] _retriveItemColumns =
  336. {
  337. "type",
  338. "data",
  339. "StartDate",
  340. "EndDate",
  341. "ChannelId",
  342. "IsMovie",
  343. "IsSports",
  344. "IsKids",
  345. "IsSeries",
  346. "IsLive",
  347. "IsNews",
  348. "IsPremiere",
  349. "EpisodeTitle",
  350. "IsRepeat",
  351. "CommunityRating",
  352. "CustomRating",
  353. "IndexNumber",
  354. "IsLocked",
  355. "PreferredMetadataLanguage",
  356. "PreferredMetadataCountryCode",
  357. "IsHD",
  358. "ExternalEtag",
  359. "DateLastRefreshed",
  360. "Name",
  361. "Path",
  362. "PremiereDate",
  363. "Overview",
  364. "ParentIndexNumber",
  365. "ProductionYear",
  366. "OfficialRating",
  367. "HomePageUrl",
  368. "ForcedSortName",
  369. "RunTimeTicks",
  370. "DateCreated",
  371. "DateModified",
  372. "guid",
  373. "Genres",
  374. "ParentId",
  375. "Audio",
  376. "ExternalServiceId",
  377. "IsInMixedFolder",
  378. "DateLastSaved",
  379. "LockedFields",
  380. "Studios",
  381. "Tags",
  382. "TrailerTypes",
  383. "OriginalTitle",
  384. "PrimaryVersionId",
  385. "DateLastMediaAdded",
  386. "Album",
  387. "CriticRating",
  388. "IsVirtualItem",
  389. "SeriesName",
  390. "SeasonName",
  391. "SeasonId",
  392. "SeriesId",
  393. "PresentationUniqueKey",
  394. "InheritedParentalRatingValue",
  395. "ExternalSeriesId",
  396. "Tagline",
  397. "ProviderIds",
  398. "Images",
  399. "ProductionLocations",
  400. "ThemeSongIds",
  401. "ThemeVideoIds",
  402. "TotalBitrate",
  403. "ExtraType",
  404. "Artists",
  405. "AlbumArtists",
  406. "ExternalId",
  407. "SeriesPresentationUniqueKey",
  408. "ShowId",
  409. "OwnerId"
  410. };
  411. private readonly string[] _mediaStreamSaveColumns =
  412. {
  413. "ItemId",
  414. "StreamIndex",
  415. "StreamType",
  416. "Codec",
  417. "Language",
  418. "ChannelLayout",
  419. "Profile",
  420. "AspectRatio",
  421. "Path",
  422. "IsInterlaced",
  423. "BitRate",
  424. "Channels",
  425. "SampleRate",
  426. "IsDefault",
  427. "IsForced",
  428. "IsExternal",
  429. "Height",
  430. "Width",
  431. "AverageFrameRate",
  432. "RealFrameRate",
  433. "Level",
  434. "PixelFormat",
  435. "BitDepth",
  436. "IsAnamorphic",
  437. "RefFrames",
  438. "CodecTag",
  439. "Comment",
  440. "NalLengthSize",
  441. "IsAvc",
  442. "Title",
  443. "TimeBase",
  444. "CodecTimeBase"
  445. };
  446. private string GetSaveItemCommandText()
  447. {
  448. var saveColumns = new List<string>
  449. {
  450. "guid",
  451. "type",
  452. "data",
  453. "Path",
  454. "StartDate",
  455. "EndDate",
  456. "ChannelId",
  457. "IsKids",
  458. "IsMovie",
  459. "IsSports",
  460. "IsSeries",
  461. "IsLive",
  462. "IsNews",
  463. "IsPremiere",
  464. "EpisodeTitle",
  465. "IsRepeat",
  466. "CommunityRating",
  467. "CustomRating",
  468. "IndexNumber",
  469. "IsLocked",
  470. "Name",
  471. "OfficialRating",
  472. "MediaType",
  473. "Overview",
  474. "ParentIndexNumber",
  475. "PremiereDate",
  476. "ProductionYear",
  477. "ParentId",
  478. "Genres",
  479. "InheritedParentalRatingValue",
  480. "SortName",
  481. "ForcedSortName",
  482. "RunTimeTicks",
  483. "HomePageUrl",
  484. "DateCreated",
  485. "DateModified",
  486. "PreferredMetadataLanguage",
  487. "PreferredMetadataCountryCode",
  488. "IsHD",
  489. "ExternalEtag",
  490. "DateLastRefreshed",
  491. "DateLastSaved",
  492. "IsInMixedFolder",
  493. "LockedFields",
  494. "Studios",
  495. "Audio",
  496. "ExternalServiceId",
  497. "Tags",
  498. "IsFolder",
  499. "UnratedType",
  500. "TopParentId",
  501. "TrailerTypes",
  502. "CriticRating",
  503. "CleanName",
  504. "PresentationUniqueKey",
  505. "OriginalTitle",
  506. "PrimaryVersionId",
  507. "DateLastMediaAdded",
  508. "Album",
  509. "IsVirtualItem",
  510. "SeriesName",
  511. "UserDataKey",
  512. "SeasonName",
  513. "SeasonId",
  514. "SeriesId",
  515. "ExternalSeriesId",
  516. "Tagline",
  517. "ProviderIds",
  518. "Images",
  519. "ProductionLocations",
  520. "ThemeSongIds",
  521. "ThemeVideoIds",
  522. "TotalBitrate",
  523. "ExtraType",
  524. "Artists",
  525. "AlbumArtists",
  526. "ExternalId",
  527. "SeriesPresentationUniqueKey",
  528. "ShowId",
  529. "OwnerId"
  530. };
  531. var saveItemCommandCommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values (";
  532. for (var i = 0; i < saveColumns.Count; i++)
  533. {
  534. if (i > 0)
  535. {
  536. saveItemCommandCommandText += ",";
  537. }
  538. saveItemCommandCommandText += "@" + saveColumns[i];
  539. }
  540. saveItemCommandCommandText += ")";
  541. return saveItemCommandCommandText;
  542. }
  543. /// <summary>
  544. /// Save a standard item in the repo
  545. /// </summary>
  546. /// <param name="item">The item.</param>
  547. /// <param name="cancellationToken">The cancellation token.</param>
  548. /// <exception cref="System.ArgumentNullException">item</exception>
  549. public void SaveItem(BaseItem item, CancellationToken cancellationToken)
  550. {
  551. if (item == null)
  552. {
  553. throw new ArgumentNullException("item");
  554. }
  555. SaveItems(new List<BaseItem> { item }, cancellationToken);
  556. }
  557. /// <summary>
  558. /// Saves the items.
  559. /// </summary>
  560. /// <param name="items">The items.</param>
  561. /// <param name="cancellationToken">The cancellation token.</param>
  562. /// <exception cref="System.ArgumentNullException">
  563. /// items
  564. /// or
  565. /// cancellationToken
  566. /// </exception>
  567. public void SaveItems(List<BaseItem> items, CancellationToken cancellationToken)
  568. {
  569. if (items == null)
  570. {
  571. throw new ArgumentNullException("items");
  572. }
  573. cancellationToken.ThrowIfCancellationRequested();
  574. CheckDisposed();
  575. var tuples = new List<Tuple<BaseItem, List<Guid>, BaseItem, string, List<string>>>();
  576. foreach (var item in items)
  577. {
  578. var ancestorIds = item.SupportsAncestors ?
  579. item.GetAncestorIds().Distinct().ToList() :
  580. null;
  581. var topParent = item.GetTopParent();
  582. var userdataKey = item.GetUserDataKeys().FirstOrDefault();
  583. var inheritedTags = item.GetInheritedTags();
  584. tuples.Add(new Tuple<BaseItem, List<Guid>, BaseItem, string, List<string>>(item, ancestorIds, topParent, userdataKey, inheritedTags));
  585. }
  586. using (WriteLock.Write())
  587. {
  588. using (var connection = CreateConnection())
  589. {
  590. connection.RunInTransaction(db =>
  591. {
  592. SaveItemsInTranscation(db, tuples);
  593. }, TransactionMode);
  594. }
  595. }
  596. }
  597. private void SaveItemsInTranscation(IDatabaseConnection db, List<Tuple<BaseItem, List<Guid>, BaseItem, string, List<string>>> tuples)
  598. {
  599. var requiresReset = false;
  600. var statements = PrepareAllSafe(db, new string[]
  601. {
  602. GetSaveItemCommandText(),
  603. "delete from AncestorIds where ItemId=@ItemId",
  604. "insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values (@ItemId, @AncestorId, @AncestorIdText)"
  605. }).ToList();
  606. using (var saveItemStatement = statements[0])
  607. {
  608. using (var deleteAncestorsStatement = statements[1])
  609. {
  610. using (var updateAncestorsStatement = statements[2])
  611. {
  612. foreach (var tuple in tuples)
  613. {
  614. if (requiresReset)
  615. {
  616. saveItemStatement.Reset();
  617. }
  618. var item = tuple.Item1;
  619. var topParent = tuple.Item3;
  620. var userDataKey = tuple.Item4;
  621. SaveItem(item, topParent, userDataKey, saveItemStatement);
  622. //Logger.Debug(_saveItemCommand.CommandText);
  623. var inheritedTags = tuple.Item5;
  624. if (item.SupportsAncestors)
  625. {
  626. UpdateAncestors(item.Id, tuple.Item2, db, deleteAncestorsStatement, updateAncestorsStatement);
  627. }
  628. UpdateItemValues(item.Id, GetItemValuesToSave(item, inheritedTags), db);
  629. requiresReset = true;
  630. }
  631. }
  632. }
  633. }
  634. }
  635. private void SaveItem(BaseItem item, BaseItem topParent, string userDataKey, IStatement saveItemStatement)
  636. {
  637. saveItemStatement.TryBind("@guid", item.Id);
  638. saveItemStatement.TryBind("@type", item.GetType().FullName);
  639. if (TypeRequiresDeserialization(item.GetType()))
  640. {
  641. saveItemStatement.TryBind("@data", _jsonSerializer.SerializeToBytes(item, _memoryStreamProvider));
  642. }
  643. else
  644. {
  645. saveItemStatement.TryBindNull("@data");
  646. }
  647. saveItemStatement.TryBind("@Path", item.Path);
  648. var hasStartDate = item as IHasStartDate;
  649. if (hasStartDate != null)
  650. {
  651. saveItemStatement.TryBind("@StartDate", hasStartDate.StartDate);
  652. }
  653. else
  654. {
  655. saveItemStatement.TryBindNull("@StartDate");
  656. }
  657. if (item.EndDate.HasValue)
  658. {
  659. saveItemStatement.TryBind("@EndDate", item.EndDate.Value);
  660. }
  661. else
  662. {
  663. saveItemStatement.TryBindNull("@EndDate");
  664. }
  665. saveItemStatement.TryBind("@ChannelId", item.ChannelId);
  666. var hasProgramAttributes = item as IHasProgramAttributes;
  667. if (hasProgramAttributes != null)
  668. {
  669. saveItemStatement.TryBind("@IsKids", hasProgramAttributes.IsKids);
  670. saveItemStatement.TryBind("@IsMovie", hasProgramAttributes.IsMovie);
  671. saveItemStatement.TryBind("@IsSports", hasProgramAttributes.IsSports);
  672. saveItemStatement.TryBind("@IsSeries", hasProgramAttributes.IsSeries);
  673. saveItemStatement.TryBind("@IsLive", hasProgramAttributes.IsLive);
  674. saveItemStatement.TryBind("@IsNews", hasProgramAttributes.IsNews);
  675. saveItemStatement.TryBind("@IsPremiere", hasProgramAttributes.IsPremiere);
  676. saveItemStatement.TryBind("@EpisodeTitle", hasProgramAttributes.EpisodeTitle);
  677. saveItemStatement.TryBind("@IsRepeat", hasProgramAttributes.IsRepeat);
  678. }
  679. else
  680. {
  681. saveItemStatement.TryBindNull("@IsKids");
  682. saveItemStatement.TryBindNull("@IsMovie");
  683. saveItemStatement.TryBindNull("@IsSports");
  684. saveItemStatement.TryBindNull("@IsSeries");
  685. saveItemStatement.TryBindNull("@IsLive");
  686. saveItemStatement.TryBindNull("@IsNews");
  687. saveItemStatement.TryBindNull("@IsPremiere");
  688. saveItemStatement.TryBindNull("@EpisodeTitle");
  689. saveItemStatement.TryBindNull("@IsRepeat");
  690. }
  691. saveItemStatement.TryBind("@CommunityRating", item.CommunityRating);
  692. saveItemStatement.TryBind("@CustomRating", item.CustomRating);
  693. saveItemStatement.TryBind("@IndexNumber", item.IndexNumber);
  694. saveItemStatement.TryBind("@IsLocked", item.IsLocked);
  695. saveItemStatement.TryBind("@Name", item.Name);
  696. saveItemStatement.TryBind("@OfficialRating", item.OfficialRating);
  697. saveItemStatement.TryBind("@MediaType", item.MediaType);
  698. saveItemStatement.TryBind("@Overview", item.Overview);
  699. saveItemStatement.TryBind("@ParentIndexNumber", item.ParentIndexNumber);
  700. saveItemStatement.TryBind("@PremiereDate", item.PremiereDate);
  701. saveItemStatement.TryBind("@ProductionYear", item.ProductionYear);
  702. var parentId = item.ParentId;
  703. if (parentId == Guid.Empty)
  704. {
  705. saveItemStatement.TryBindNull("@ParentId");
  706. }
  707. else
  708. {
  709. saveItemStatement.TryBind("@ParentId", parentId);
  710. }
  711. if (item.Genres.Count > 0)
  712. {
  713. saveItemStatement.TryBind("@Genres", string.Join("|", item.Genres.ToArray()));
  714. }
  715. else
  716. {
  717. saveItemStatement.TryBindNull("@Genres");
  718. }
  719. saveItemStatement.TryBind("@InheritedParentalRatingValue", item.InheritedParentalRatingValue);
  720. saveItemStatement.TryBind("@SortName", item.SortName);
  721. saveItemStatement.TryBind("@ForcedSortName", item.ForcedSortName);
  722. saveItemStatement.TryBind("@RunTimeTicks", item.RunTimeTicks);
  723. saveItemStatement.TryBind("@HomePageUrl", item.HomePageUrl);
  724. saveItemStatement.TryBind("@DateCreated", item.DateCreated);
  725. saveItemStatement.TryBind("@DateModified", item.DateModified);
  726. saveItemStatement.TryBind("@PreferredMetadataLanguage", item.PreferredMetadataLanguage);
  727. saveItemStatement.TryBind("@PreferredMetadataCountryCode", item.PreferredMetadataCountryCode);
  728. saveItemStatement.TryBind("@IsHD", item.IsHD);
  729. saveItemStatement.TryBind("@ExternalEtag", item.ExternalEtag);
  730. if (item.DateLastRefreshed != default(DateTime))
  731. {
  732. saveItemStatement.TryBind("@DateLastRefreshed", item.DateLastRefreshed);
  733. }
  734. else
  735. {
  736. saveItemStatement.TryBindNull("@DateLastRefreshed");
  737. }
  738. if (item.DateLastSaved != default(DateTime))
  739. {
  740. saveItemStatement.TryBind("@DateLastSaved", item.DateLastSaved);
  741. }
  742. else
  743. {
  744. saveItemStatement.TryBindNull("@DateLastSaved");
  745. }
  746. saveItemStatement.TryBind("@IsInMixedFolder", item.IsInMixedFolder);
  747. if (item.LockedFields.Length > 0)
  748. {
  749. saveItemStatement.TryBind("@LockedFields", string.Join("|", item.LockedFields.Select(i => i.ToString()).ToArray()));
  750. }
  751. else
  752. {
  753. saveItemStatement.TryBindNull("@LockedFields");
  754. }
  755. if (item.Studios.Length > 0)
  756. {
  757. saveItemStatement.TryBind("@Studios", string.Join("|", item.Studios.ToArray()));
  758. }
  759. else
  760. {
  761. saveItemStatement.TryBindNull("@Studios");
  762. }
  763. if (item.Audio.HasValue)
  764. {
  765. saveItemStatement.TryBind("@Audio", item.Audio.Value.ToString());
  766. }
  767. else
  768. {
  769. saveItemStatement.TryBindNull("@Audio");
  770. }
  771. saveItemStatement.TryBind("@ExternalServiceId", item.ServiceName);
  772. if (item.Tags.Length > 0)
  773. {
  774. saveItemStatement.TryBind("@Tags", string.Join("|", item.Tags));
  775. }
  776. else
  777. {
  778. saveItemStatement.TryBindNull("@Tags");
  779. }
  780. saveItemStatement.TryBind("@IsFolder", item.IsFolder);
  781. saveItemStatement.TryBind("@UnratedType", item.GetBlockUnratedType().ToString());
  782. if (topParent != null)
  783. {
  784. //Logger.Debug("Item {0} has top parent {1}", item.Id, topParent.Id);
  785. saveItemStatement.TryBind("@TopParentId", topParent.Id.ToString("N"));
  786. }
  787. else
  788. {
  789. //Logger.Debug("Item {0} has null top parent", item.Id);
  790. saveItemStatement.TryBindNull("@TopParentId");
  791. }
  792. var trailer = item as Trailer;
  793. if (trailer != null && trailer.TrailerTypes.Count > 0)
  794. {
  795. saveItemStatement.TryBind("@TrailerTypes", string.Join("|", trailer.TrailerTypes.Select(i => i.ToString()).ToArray()));
  796. }
  797. else
  798. {
  799. saveItemStatement.TryBindNull("@TrailerTypes");
  800. }
  801. saveItemStatement.TryBind("@CriticRating", item.CriticRating);
  802. if (string.IsNullOrWhiteSpace(item.Name))
  803. {
  804. saveItemStatement.TryBindNull("@CleanName");
  805. }
  806. else
  807. {
  808. saveItemStatement.TryBind("@CleanName", GetCleanValue(item.Name));
  809. }
  810. saveItemStatement.TryBind("@PresentationUniqueKey", item.PresentationUniqueKey);
  811. saveItemStatement.TryBind("@OriginalTitle", item.OriginalTitle);
  812. var video = item as Video;
  813. if (video != null)
  814. {
  815. saveItemStatement.TryBind("@PrimaryVersionId", video.PrimaryVersionId);
  816. }
  817. else
  818. {
  819. saveItemStatement.TryBindNull("@PrimaryVersionId");
  820. }
  821. var folder = item as Folder;
  822. if (folder != null && folder.DateLastMediaAdded.HasValue)
  823. {
  824. saveItemStatement.TryBind("@DateLastMediaAdded", folder.DateLastMediaAdded.Value);
  825. }
  826. else
  827. {
  828. saveItemStatement.TryBindNull("@DateLastMediaAdded");
  829. }
  830. saveItemStatement.TryBind("@Album", item.Album);
  831. saveItemStatement.TryBind("@IsVirtualItem", item.IsVirtualItem);
  832. var hasSeries = item as IHasSeries;
  833. if (hasSeries != null)
  834. {
  835. saveItemStatement.TryBind("@SeriesName", hasSeries.SeriesName);
  836. }
  837. else
  838. {
  839. saveItemStatement.TryBindNull("@SeriesName");
  840. }
  841. if (string.IsNullOrWhiteSpace(userDataKey))
  842. {
  843. saveItemStatement.TryBindNull("@UserDataKey");
  844. }
  845. else
  846. {
  847. saveItemStatement.TryBind("@UserDataKey", userDataKey);
  848. }
  849. var episode = item as Episode;
  850. if (episode != null)
  851. {
  852. saveItemStatement.TryBind("@SeasonName", episode.SeasonName);
  853. saveItemStatement.TryBind("@SeasonId", episode.SeasonId);
  854. }
  855. else
  856. {
  857. saveItemStatement.TryBindNull("@SeasonName");
  858. saveItemStatement.TryBindNull("@SeasonId");
  859. }
  860. if (hasSeries != null)
  861. {
  862. saveItemStatement.TryBind("@SeriesId", hasSeries.SeriesId);
  863. saveItemStatement.TryBind("@SeriesPresentationUniqueKey", hasSeries.SeriesPresentationUniqueKey);
  864. }
  865. else
  866. {
  867. saveItemStatement.TryBindNull("@SeriesId");
  868. saveItemStatement.TryBindNull("@SeriesPresentationUniqueKey");
  869. }
  870. saveItemStatement.TryBind("@ExternalSeriesId", item.ExternalSeriesId);
  871. saveItemStatement.TryBind("@Tagline", item.Tagline);
  872. saveItemStatement.TryBind("@ProviderIds", SerializeProviderIds(item));
  873. saveItemStatement.TryBind("@Images", SerializeImages(item));
  874. if (item.ProductionLocations.Length > 0)
  875. {
  876. saveItemStatement.TryBind("@ProductionLocations", string.Join("|", item.ProductionLocations));
  877. }
  878. else
  879. {
  880. saveItemStatement.TryBindNull("@ProductionLocations");
  881. }
  882. if (item.ThemeSongIds.Length > 0)
  883. {
  884. saveItemStatement.TryBind("@ThemeSongIds", string.Join("|", item.ThemeSongIds.ToArray()));
  885. }
  886. else
  887. {
  888. saveItemStatement.TryBindNull("@ThemeSongIds");
  889. }
  890. if (item.ThemeVideoIds.Length > 0)
  891. {
  892. saveItemStatement.TryBind("@ThemeVideoIds", string.Join("|", item.ThemeVideoIds.ToArray()));
  893. }
  894. else
  895. {
  896. saveItemStatement.TryBindNull("@ThemeVideoIds");
  897. }
  898. saveItemStatement.TryBind("@TotalBitrate", item.TotalBitrate);
  899. if (item.ExtraType.HasValue)
  900. {
  901. saveItemStatement.TryBind("@ExtraType", item.ExtraType.Value.ToString());
  902. }
  903. else
  904. {
  905. saveItemStatement.TryBindNull("@ExtraType");
  906. }
  907. string artists = null;
  908. var hasArtists = item as IHasArtist;
  909. if (hasArtists != null)
  910. {
  911. if (hasArtists.Artists.Length > 0)
  912. {
  913. artists = string.Join("|", hasArtists.Artists);
  914. }
  915. }
  916. saveItemStatement.TryBind("@Artists", artists);
  917. string albumArtists = null;
  918. var hasAlbumArtists = item as IHasAlbumArtist;
  919. if (hasAlbumArtists != null)
  920. {
  921. if (hasAlbumArtists.AlbumArtists.Length > 0)
  922. {
  923. albumArtists = string.Join("|", hasAlbumArtists.AlbumArtists);
  924. }
  925. }
  926. saveItemStatement.TryBind("@AlbumArtists", albumArtists);
  927. saveItemStatement.TryBind("@ExternalId", item.ExternalId);
  928. var program = item as LiveTvProgram;
  929. if (program != null)
  930. {
  931. saveItemStatement.TryBind("@ShowId", program.ShowId);
  932. }
  933. else
  934. {
  935. saveItemStatement.TryBindNull("@ShowId");
  936. }
  937. var ownerId = item.OwnerId;
  938. if (ownerId != Guid.Empty)
  939. {
  940. saveItemStatement.TryBind("@OwnerId", ownerId);
  941. }
  942. else
  943. {
  944. saveItemStatement.TryBindNull("@OwnerId");
  945. }
  946. saveItemStatement.MoveNext();
  947. }
  948. private string SerializeProviderIds(BaseItem item)
  949. {
  950. // Ideally we shouldn't need this IsNullOrWhiteSpace check but we're seeing some cases of bad data slip through
  951. var ids = item.ProviderIds
  952. .Where(i => !string.IsNullOrWhiteSpace(i.Value))
  953. .ToList();
  954. if (ids.Count == 0)
  955. {
  956. return null;
  957. }
  958. return string.Join("|", ids.Select(i => i.Key + "=" + i.Value).ToArray());
  959. }
  960. private void DeserializeProviderIds(string value, BaseItem item)
  961. {
  962. if (string.IsNullOrWhiteSpace(value))
  963. {
  964. return;
  965. }
  966. if (item.ProviderIds.Count > 0)
  967. {
  968. return;
  969. }
  970. var parts = value.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
  971. foreach (var part in parts)
  972. {
  973. var idParts = part.Split('=');
  974. if (idParts.Length == 2)
  975. {
  976. item.SetProviderId(idParts[0], idParts[1]);
  977. }
  978. }
  979. }
  980. private string SerializeImages(BaseItem item)
  981. {
  982. var images = item.ImageInfos;
  983. if (images.Length == 0)
  984. {
  985. return null;
  986. }
  987. var imageStrings = images.Where(i => !string.IsNullOrWhiteSpace(i.Path)).Select(ToValueString).ToArray();
  988. return string.Join("|", imageStrings);
  989. }
  990. private void DeserializeImages(string value, BaseItem item)
  991. {
  992. if (string.IsNullOrWhiteSpace(value))
  993. {
  994. return;
  995. }
  996. if (item.ImageInfos.Length > 0)
  997. {
  998. return;
  999. }
  1000. var parts = value.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
  1001. var list = new List<ItemImageInfo>();
  1002. foreach (var part in parts)
  1003. {
  1004. var image = ItemImageInfoFromValueString(part);
  1005. if (image != null)
  1006. {
  1007. list.Add(image);
  1008. }
  1009. }
  1010. item.ImageInfos = list.ToArray(list.Count);
  1011. }
  1012. public string ToValueString(ItemImageInfo image)
  1013. {
  1014. var delimeter = "*";
  1015. var path = image.Path;
  1016. if (path == null)
  1017. {
  1018. path = string.Empty;
  1019. }
  1020. return path +
  1021. delimeter +
  1022. image.DateModified.Ticks.ToString(CultureInfo.InvariantCulture) +
  1023. delimeter +
  1024. image.Type;
  1025. }
  1026. public ItemImageInfo ItemImageInfoFromValueString(string value)
  1027. {
  1028. var parts = value.Split(new[] { '*' }, StringSplitOptions.None);
  1029. if (parts.Length < 3)
  1030. {
  1031. return null;
  1032. }
  1033. var image = new ItemImageInfo();
  1034. image.Path = parts[0];
  1035. long ticks;
  1036. if (long.TryParse(parts[1], NumberStyles.Any, CultureInfo.InvariantCulture, out ticks))
  1037. {
  1038. image.DateModified = new DateTime(ticks, DateTimeKind.Utc);
  1039. }
  1040. ImageType type;
  1041. if (Enum.TryParse(parts[2], true, out type))
  1042. {
  1043. image.Type = type;
  1044. }
  1045. return image;
  1046. }
  1047. /// <summary>
  1048. /// Internal retrieve from items or users table
  1049. /// </summary>
  1050. /// <param name="id">The id.</param>
  1051. /// <returns>BaseItem.</returns>
  1052. /// <exception cref="System.ArgumentNullException">id</exception>
  1053. /// <exception cref="System.ArgumentException"></exception>
  1054. public BaseItem RetrieveItem(Guid id)
  1055. {
  1056. if (id == Guid.Empty)
  1057. {
  1058. throw new ArgumentNullException("id");
  1059. }
  1060. CheckDisposed();
  1061. //Logger.Info("Retrieving item {0}", id.ToString("N"));
  1062. using (WriteLock.Read())
  1063. {
  1064. using (var connection = CreateConnection(true))
  1065. {
  1066. using (var statement = PrepareStatementSafe(connection, "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid"))
  1067. {
  1068. statement.TryBind("@guid", id);
  1069. foreach (var row in statement.ExecuteQuery())
  1070. {
  1071. return GetItem(row, new InternalItemsQuery());
  1072. }
  1073. }
  1074. return null;
  1075. }
  1076. }
  1077. }
  1078. private bool TypeRequiresDeserialization(Type type)
  1079. {
  1080. if (_config.Configuration.SkipDeserializationForBasicTypes)
  1081. {
  1082. if (type == typeof(Channel))
  1083. {
  1084. return false;
  1085. }
  1086. if (type == typeof(UserRootFolder))
  1087. {
  1088. return false;
  1089. }
  1090. }
  1091. if (type == typeof(Season))
  1092. {
  1093. return false;
  1094. }
  1095. if (type == typeof(MusicArtist))
  1096. {
  1097. return false;
  1098. }
  1099. if (type == typeof(Person))
  1100. {
  1101. return false;
  1102. }
  1103. if (type == typeof(MusicGenre))
  1104. {
  1105. return false;
  1106. }
  1107. if (type == typeof(GameGenre))
  1108. {
  1109. return false;
  1110. }
  1111. if (type == typeof(Genre))
  1112. {
  1113. return false;
  1114. }
  1115. if (type == typeof(Studio))
  1116. {
  1117. return false;
  1118. }
  1119. if (type == typeof(ManualCollectionsFolder))
  1120. {
  1121. return false;
  1122. }
  1123. if (type == typeof(CameraUploadsFolder))
  1124. {
  1125. return false;
  1126. }
  1127. if (type == typeof(PlaylistsFolder))
  1128. {
  1129. return false;
  1130. }
  1131. if (type == typeof(PhotoAlbum))
  1132. {
  1133. return false;
  1134. }
  1135. if (type == typeof(Year))
  1136. {
  1137. return false;
  1138. }
  1139. if (type == typeof(Book))
  1140. {
  1141. return false;
  1142. }
  1143. if (type == typeof(RecordingGroup))
  1144. {
  1145. return false;
  1146. }
  1147. if (type == typeof(LiveTvProgram))
  1148. {
  1149. return false;
  1150. }
  1151. if (type == typeof(LiveTvAudioRecording))
  1152. {
  1153. return false;
  1154. }
  1155. if (type == typeof(AudioPodcast))
  1156. {
  1157. return false;
  1158. }
  1159. if (type == typeof(AudioBook))
  1160. {
  1161. return false;
  1162. }
  1163. if (type == typeof(Audio))
  1164. {
  1165. return false;
  1166. }
  1167. if (type == typeof(MusicAlbum))
  1168. {
  1169. return false;
  1170. }
  1171. return true;
  1172. }
  1173. private BaseItem GetItem(IReadOnlyList<IResultSetValue> reader, InternalItemsQuery query)
  1174. {
  1175. return GetItem(reader, query, HasProgramAttributes(query), HasEpisodeAttributes(query), HasStartDate(query), HasTrailerTypes(query), HasArtistFields(query), HasSeriesFields(query));
  1176. }
  1177. private BaseItem GetItem(IReadOnlyList<IResultSetValue> reader, InternalItemsQuery query, bool enableProgramAttributes, bool hasEpisodeAttributes, bool queryHasStartDate, bool hasTrailerTypes, bool hasArtistFields, bool hasSeriesFields)
  1178. {
  1179. var typeString = reader.GetString(0);
  1180. var type = _typeMapper.GetType(typeString);
  1181. if (type == null)
  1182. {
  1183. //Logger.Debug("Unknown type {0}", typeString);
  1184. return null;
  1185. }
  1186. BaseItem item = null;
  1187. if (TypeRequiresDeserialization(type))
  1188. {
  1189. using (var stream = _memoryStreamProvider.CreateNew(reader[1].ToBlob()))
  1190. {
  1191. stream.Position = 0;
  1192. try
  1193. {
  1194. item = _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem;
  1195. }
  1196. catch (SerializationException ex)
  1197. {
  1198. Logger.ErrorException("Error deserializing item", ex);
  1199. }
  1200. }
  1201. }
  1202. if (item == null)
  1203. {
  1204. try
  1205. {
  1206. item = Activator.CreateInstance(type) as BaseItem;
  1207. }
  1208. catch
  1209. {
  1210. }
  1211. }
  1212. if (item == null)
  1213. {
  1214. return null;
  1215. }
  1216. var index = 2;
  1217. if (queryHasStartDate)
  1218. {
  1219. if (!reader.IsDBNull(index))
  1220. {
  1221. var hasStartDate = item as IHasStartDate;
  1222. if (hasStartDate != null)
  1223. {
  1224. hasStartDate.StartDate = reader[index].ReadDateTime();
  1225. }
  1226. }
  1227. index++;
  1228. }
  1229. if (!reader.IsDBNull(index))
  1230. {
  1231. item.EndDate = reader[index].ReadDateTime();
  1232. }
  1233. index++;
  1234. if (!reader.IsDBNull(index))
  1235. {
  1236. item.ChannelId = reader.GetString(index);
  1237. }
  1238. index++;
  1239. if (enableProgramAttributes)
  1240. {
  1241. var hasProgramAttributes = item as IHasProgramAttributes;
  1242. if (hasProgramAttributes != null)
  1243. {
  1244. if (!reader.IsDBNull(index))
  1245. {
  1246. hasProgramAttributes.IsMovie = reader.GetBoolean(index);
  1247. }
  1248. index++;
  1249. if (!reader.IsDBNull(index))
  1250. {
  1251. hasProgramAttributes.IsSports = reader.GetBoolean(index);
  1252. }
  1253. index++;
  1254. if (!reader.IsDBNull(index))
  1255. {
  1256. hasProgramAttributes.IsKids = reader.GetBoolean(index);
  1257. }
  1258. index++;
  1259. if (!reader.IsDBNull(index))
  1260. {
  1261. hasProgramAttributes.IsSeries = reader.GetBoolean(index);
  1262. }
  1263. index++;
  1264. if (!reader.IsDBNull(index))
  1265. {
  1266. hasProgramAttributes.IsLive = reader.GetBoolean(index);
  1267. }
  1268. index++;
  1269. if (!reader.IsDBNull(index))
  1270. {
  1271. hasProgramAttributes.IsNews = reader.GetBoolean(index);
  1272. }
  1273. index++;
  1274. if (!reader.IsDBNull(index))
  1275. {
  1276. hasProgramAttributes.IsPremiere = reader.GetBoolean(index);
  1277. }
  1278. index++;
  1279. if (!reader.IsDBNull(index))
  1280. {
  1281. hasProgramAttributes.EpisodeTitle = reader.GetString(index);
  1282. }
  1283. index++;
  1284. if (!reader.IsDBNull(index))
  1285. {
  1286. hasProgramAttributes.IsRepeat = reader.GetBoolean(index);
  1287. }
  1288. index++;
  1289. }
  1290. else
  1291. {
  1292. index += 9;
  1293. }
  1294. }
  1295. if (!reader.IsDBNull(index))
  1296. {
  1297. item.CommunityRating = reader.GetFloat(index);
  1298. }
  1299. index++;
  1300. if (HasField(query, ItemFields.CustomRating))
  1301. {
  1302. if (!reader.IsDBNull(index))
  1303. {
  1304. item.CustomRating = reader.GetString(index);
  1305. }
  1306. index++;
  1307. }
  1308. if (!reader.IsDBNull(index))
  1309. {
  1310. item.IndexNumber = reader.GetInt32(index);
  1311. }
  1312. index++;
  1313. if (HasField(query, ItemFields.Settings))
  1314. {
  1315. if (!reader.IsDBNull(index))
  1316. {
  1317. item.IsLocked = reader.GetBoolean(index);
  1318. }
  1319. index++;
  1320. if (!reader.IsDBNull(index))
  1321. {
  1322. item.PreferredMetadataLanguage = reader.GetString(index);
  1323. }
  1324. index++;
  1325. if (!reader.IsDBNull(index))
  1326. {
  1327. item.PreferredMetadataCountryCode = reader.GetString(index);
  1328. }
  1329. index++;
  1330. }
  1331. if (!reader.IsDBNull(index))
  1332. {
  1333. item.IsHD = reader.GetBoolean(index);
  1334. }
  1335. index++;
  1336. if (HasField(query, ItemFields.ExternalEtag))
  1337. {
  1338. if (!reader.IsDBNull(index))
  1339. {
  1340. item.ExternalEtag = reader.GetString(index);
  1341. }
  1342. index++;
  1343. }
  1344. if (HasField(query, ItemFields.DateLastRefreshed))
  1345. {
  1346. if (!reader.IsDBNull(index))
  1347. {
  1348. item.DateLastRefreshed = reader[index].ReadDateTime();
  1349. }
  1350. index++;
  1351. }
  1352. if (!reader.IsDBNull(index))
  1353. {
  1354. item.Name = reader.GetString(index);
  1355. }
  1356. index++;
  1357. if (!reader.IsDBNull(index))
  1358. {
  1359. item.Path = reader.GetString(index);
  1360. }
  1361. index++;
  1362. if (!reader.IsDBNull(index))
  1363. {
  1364. item.PremiereDate = reader[index].ReadDateTime();
  1365. }
  1366. index++;
  1367. if (HasField(query, ItemFields.Overview))
  1368. {
  1369. if (!reader.IsDBNull(index))
  1370. {
  1371. item.Overview = reader.GetString(index);
  1372. }
  1373. index++;
  1374. }
  1375. if (!reader.IsDBNull(index))
  1376. {
  1377. item.ParentIndexNumber = reader.GetInt32(index);
  1378. }
  1379. index++;
  1380. if (!reader.IsDBNull(index))
  1381. {
  1382. item.ProductionYear = reader.GetInt32(index);
  1383. }
  1384. index++;
  1385. if (!reader.IsDBNull(index))
  1386. {
  1387. item.OfficialRating = reader.GetString(index);
  1388. }
  1389. index++;
  1390. if (HasField(query, ItemFields.HomePageUrl))
  1391. {
  1392. if (!reader.IsDBNull(index))
  1393. {
  1394. item.HomePageUrl = reader.GetString(index);
  1395. }
  1396. index++;
  1397. }
  1398. if (HasField(query, ItemFields.SortName))
  1399. {
  1400. if (!reader.IsDBNull(index))
  1401. {
  1402. item.ForcedSortName = reader.GetString(index);
  1403. }
  1404. index++;
  1405. }
  1406. if (!reader.IsDBNull(index))
  1407. {
  1408. item.RunTimeTicks = reader.GetInt64(index);
  1409. }
  1410. index++;
  1411. if (HasField(query, ItemFields.DateCreated))
  1412. {
  1413. if (!reader.IsDBNull(index))
  1414. {
  1415. item.DateCreated = reader[index].ReadDateTime();
  1416. }
  1417. index++;
  1418. }
  1419. if (!reader.IsDBNull(index))
  1420. {
  1421. item.DateModified = reader[index].ReadDateTime();
  1422. }
  1423. index++;
  1424. item.Id = reader.GetGuid(index);
  1425. index++;
  1426. if (HasField(query, ItemFields.Genres))
  1427. {
  1428. if (!reader.IsDBNull(index))
  1429. {
  1430. item.Genres = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList();
  1431. }
  1432. index++;
  1433. }
  1434. if (!reader.IsDBNull(index))
  1435. {
  1436. item.ParentId = reader.GetGuid(index);
  1437. }
  1438. index++;
  1439. if (!reader.IsDBNull(index))
  1440. {
  1441. ProgramAudio audio;
  1442. if (Enum.TryParse(reader.GetString(index), true, out audio))
  1443. {
  1444. item.Audio = audio;
  1445. }
  1446. }
  1447. index++;
  1448. // TODO: Even if not needed by apps, the server needs it internally
  1449. // But get this excluded from contexts where it is not needed
  1450. if (!reader.IsDBNull(index))
  1451. {
  1452. item.ServiceName = reader.GetString(index);
  1453. }
  1454. index++;
  1455. if (!reader.IsDBNull(index))
  1456. {
  1457. item.IsInMixedFolder = reader.GetBoolean(index);
  1458. }
  1459. index++;
  1460. if (HasField(query, ItemFields.DateLastSaved))
  1461. {
  1462. if (!reader.IsDBNull(index))
  1463. {
  1464. item.DateLastSaved = reader[index].ReadDateTime();
  1465. }
  1466. index++;
  1467. }
  1468. if (HasField(query, ItemFields.Settings))
  1469. {
  1470. if (!reader.IsDBNull(index))
  1471. {
  1472. item.LockedFields = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(
  1473. i =>
  1474. {
  1475. MetadataFields parsedValue;
  1476. if (Enum.TryParse(i, true, out parsedValue))
  1477. {
  1478. return parsedValue;
  1479. }
  1480. return (MetadataFields?)null;
  1481. }).Where(i => i.HasValue).Select(i => i.Value).ToArray();
  1482. }
  1483. index++;
  1484. }
  1485. if (HasField(query, ItemFields.Studios))
  1486. {
  1487. if (!reader.IsDBNull(index))
  1488. {
  1489. item.Studios = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
  1490. }
  1491. index++;
  1492. }
  1493. if (HasField(query, ItemFields.Tags))
  1494. {
  1495. if (!reader.IsDBNull(index))
  1496. {
  1497. item.Tags = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
  1498. }
  1499. index++;
  1500. }
  1501. if (hasTrailerTypes)
  1502. {
  1503. var trailer = item as Trailer;
  1504. if (trailer != null)
  1505. {
  1506. if (!reader.IsDBNull(index))
  1507. {
  1508. trailer.TrailerTypes = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(
  1509. i =>
  1510. {
  1511. TrailerType parsedValue;
  1512. if (Enum.TryParse(i, true, out parsedValue))
  1513. {
  1514. return parsedValue;
  1515. }
  1516. return (TrailerType?)null;
  1517. }).Where(i => i.HasValue).Select(i => i.Value).ToList();
  1518. }
  1519. }
  1520. index++;
  1521. }
  1522. if (HasField(query, ItemFields.OriginalTitle))
  1523. {
  1524. if (!reader.IsDBNull(index))
  1525. {
  1526. item.OriginalTitle = reader.GetString(index);
  1527. }
  1528. index++;
  1529. }
  1530. var video = item as Video;
  1531. if (video != null)
  1532. {
  1533. if (!reader.IsDBNull(index))
  1534. {
  1535. video.PrimaryVersionId = reader.GetString(index);
  1536. }
  1537. }
  1538. index++;
  1539. if (HasField(query, ItemFields.DateLastMediaAdded))
  1540. {
  1541. var folder = item as Folder;
  1542. if (folder != null && !reader.IsDBNull(index))
  1543. {
  1544. folder.DateLastMediaAdded = reader[index].ReadDateTime();
  1545. }
  1546. index++;
  1547. }
  1548. if (!reader.IsDBNull(index))
  1549. {
  1550. item.Album = reader.GetString(index);
  1551. }
  1552. index++;
  1553. if (!reader.IsDBNull(index))
  1554. {
  1555. item.CriticRating = reader.GetFloat(index);
  1556. }
  1557. index++;
  1558. if (!reader.IsDBNull(index))
  1559. {
  1560. item.IsVirtualItem = reader.GetBoolean(index);
  1561. }
  1562. index++;
  1563. var hasSeries = item as IHasSeries;
  1564. if (hasSeriesFields)
  1565. {
  1566. if (hasSeries != null)
  1567. {
  1568. if (!reader.IsDBNull(index))
  1569. {
  1570. hasSeries.SeriesName = reader.GetString(index);
  1571. }
  1572. }
  1573. index++;
  1574. }
  1575. if (hasEpisodeAttributes)
  1576. {
  1577. var episode = item as Episode;
  1578. if (episode != null)
  1579. {
  1580. if (!reader.IsDBNull(index))
  1581. {
  1582. episode.SeasonName = reader.GetString(index);
  1583. }
  1584. index++;
  1585. if (!reader.IsDBNull(index))
  1586. {
  1587. episode.SeasonId = reader.GetGuid(index);
  1588. }
  1589. }
  1590. else
  1591. {
  1592. index++;
  1593. }
  1594. index++;
  1595. }
  1596. if (hasSeriesFields)
  1597. {
  1598. if (hasSeries != null)
  1599. {
  1600. if (!reader.IsDBNull(index))
  1601. {
  1602. hasSeries.SeriesId = reader.GetGuid(index);
  1603. }
  1604. }
  1605. index++;
  1606. }
  1607. if (HasField(query, ItemFields.PresentationUniqueKey))
  1608. {
  1609. if (!reader.IsDBNull(index))
  1610. {
  1611. item.PresentationUniqueKey = reader.GetString(index);
  1612. }
  1613. index++;
  1614. }
  1615. if (HasField(query, ItemFields.InheritedParentalRatingValue))
  1616. {
  1617. if (!reader.IsDBNull(index))
  1618. {
  1619. item.InheritedParentalRatingValue = reader.GetInt32(index);
  1620. }
  1621. index++;
  1622. }
  1623. if (HasField(query, ItemFields.ExternalSeriesId))
  1624. {
  1625. if (!reader.IsDBNull(index))
  1626. {
  1627. item.ExternalSeriesId = reader.GetString(index);
  1628. }
  1629. index++;
  1630. }
  1631. if (HasField(query, ItemFields.Taglines))
  1632. {
  1633. if (!reader.IsDBNull(index))
  1634. {
  1635. item.Tagline = reader.GetString(index);
  1636. }
  1637. index++;
  1638. }
  1639. if (!reader.IsDBNull(index))
  1640. {
  1641. DeserializeProviderIds(reader.GetString(index), item);
  1642. }
  1643. index++;
  1644. if (query.DtoOptions.EnableImages)
  1645. {
  1646. if (!reader.IsDBNull(index))
  1647. {
  1648. DeserializeImages(reader.GetString(index), item);
  1649. }
  1650. index++;
  1651. }
  1652. if (HasField(query, ItemFields.ProductionLocations))
  1653. {
  1654. if (!reader.IsDBNull(index))
  1655. {
  1656. item.ProductionLocations = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToArray();
  1657. }
  1658. index++;
  1659. }
  1660. if (HasField(query, ItemFields.ThemeSongIds))
  1661. {
  1662. if (!reader.IsDBNull(index))
  1663. {
  1664. item.ThemeSongIds = SplitToGuids(reader.GetString(index));
  1665. }
  1666. index++;
  1667. }
  1668. if (HasField(query, ItemFields.ThemeVideoIds))
  1669. {
  1670. if (!reader.IsDBNull(index))
  1671. {
  1672. item.ThemeVideoIds = SplitToGuids(reader.GetString(index));
  1673. }
  1674. index++;
  1675. }
  1676. if (!reader.IsDBNull(index))
  1677. {
  1678. item.TotalBitrate = reader.GetInt32(index);
  1679. }
  1680. index++;
  1681. if (!reader.IsDBNull(index))
  1682. {
  1683. ExtraType extraType;
  1684. if (Enum.TryParse(reader.GetString(index), true, out extraType))
  1685. {
  1686. item.ExtraType = extraType;
  1687. }
  1688. }
  1689. index++;
  1690. if (hasArtistFields)
  1691. {
  1692. var hasArtists = item as IHasArtist;
  1693. if (hasArtists != null && !reader.IsDBNull(index))
  1694. {
  1695. hasArtists.Artists = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
  1696. }
  1697. index++;
  1698. var hasAlbumArtists = item as IHasAlbumArtist;
  1699. if (hasAlbumArtists != null && !reader.IsDBNull(index))
  1700. {
  1701. hasAlbumArtists.AlbumArtists = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
  1702. }
  1703. index++;
  1704. }
  1705. if (!reader.IsDBNull(index))
  1706. {
  1707. item.ExternalId = reader.GetString(index);
  1708. }
  1709. index++;
  1710. if (HasField(query, ItemFields.SeriesPresentationUniqueKey))
  1711. {
  1712. if (hasSeries != null)
  1713. {
  1714. if (!reader.IsDBNull(index))
  1715. {
  1716. hasSeries.SeriesPresentationUniqueKey = reader.GetString(index);
  1717. }
  1718. }
  1719. index++;
  1720. }
  1721. if (enableProgramAttributes)
  1722. {
  1723. var program = item as LiveTvProgram;
  1724. if (program != null)
  1725. {
  1726. if (!reader.IsDBNull(index))
  1727. {
  1728. program.ShowId = reader.GetString(index);
  1729. }
  1730. index++;
  1731. }
  1732. else
  1733. {
  1734. index++;
  1735. }
  1736. }
  1737. if (!reader.IsDBNull(index))
  1738. {
  1739. item.OwnerId = reader.GetGuid(index);
  1740. }
  1741. index++;
  1742. return item;
  1743. }
  1744. private Guid[] SplitToGuids(string value)
  1745. {
  1746. var ids = value.Split('|');
  1747. var result = new Guid[ids.Length];
  1748. for (var i = 0; i < result.Length; i++)
  1749. {
  1750. result[i] = new Guid(ids[i]);
  1751. }
  1752. return result;
  1753. }
  1754. /// <summary>
  1755. /// Gets the critic reviews.
  1756. /// </summary>
  1757. /// <param name="itemId">The item id.</param>
  1758. public List<ItemReview> GetCriticReviews(Guid itemId)
  1759. {
  1760. return new List<ItemReview>();
  1761. }
  1762. /// <summary>
  1763. /// Saves the critic reviews.
  1764. /// </summary>
  1765. /// <param name="itemId">The item id.</param>
  1766. /// <param name="criticReviews">The critic reviews.</param>
  1767. public void SaveCriticReviews(Guid itemId, IEnumerable<ItemReview> criticReviews)
  1768. {
  1769. }
  1770. /// <summary>
  1771. /// Gets chapters for an item
  1772. /// </summary>
  1773. /// <param name="id">The id.</param>
  1774. /// <returns>IEnumerable{ChapterInfo}.</returns>
  1775. /// <exception cref="System.ArgumentNullException">id</exception>
  1776. public List<ChapterInfo> GetChapters(Guid id)
  1777. {
  1778. CheckDisposed();
  1779. if (id == Guid.Empty)
  1780. {
  1781. throw new ArgumentNullException("id");
  1782. }
  1783. using (WriteLock.Read())
  1784. {
  1785. using (var connection = CreateConnection(true))
  1786. {
  1787. var list = new List<ChapterInfo>();
  1788. using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"))
  1789. {
  1790. statement.TryBind("@ItemId", id);
  1791. foreach (var row in statement.ExecuteQuery())
  1792. {
  1793. list.Add(GetChapter(row));
  1794. }
  1795. }
  1796. return list;
  1797. }
  1798. }
  1799. }
  1800. /// <summary>
  1801. /// Gets a single chapter for an item
  1802. /// </summary>
  1803. /// <param name="id">The id.</param>
  1804. /// <param name="index">The index.</param>
  1805. /// <returns>ChapterInfo.</returns>
  1806. /// <exception cref="System.ArgumentNullException">id</exception>
  1807. public ChapterInfo GetChapter(Guid id, int index)
  1808. {
  1809. CheckDisposed();
  1810. if (id == Guid.Empty)
  1811. {
  1812. throw new ArgumentNullException("id");
  1813. }
  1814. using (WriteLock.Read())
  1815. {
  1816. using (var connection = CreateConnection(true))
  1817. {
  1818. using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex"))
  1819. {
  1820. statement.TryBind("@ItemId", id);
  1821. statement.TryBind("@ChapterIndex", index);
  1822. foreach (var row in statement.ExecuteQuery())
  1823. {
  1824. return GetChapter(row);
  1825. }
  1826. }
  1827. }
  1828. }
  1829. return null;
  1830. }
  1831. /// <summary>
  1832. /// Gets the chapter.
  1833. /// </summary>
  1834. /// <param name="reader">The reader.</param>
  1835. /// <returns>ChapterInfo.</returns>
  1836. private ChapterInfo GetChapter(IReadOnlyList<IResultSetValue> reader)
  1837. {
  1838. var chapter = new ChapterInfo
  1839. {
  1840. StartPositionTicks = reader.GetInt64(0)
  1841. };
  1842. if (!reader.IsDBNull(1))
  1843. {
  1844. chapter.Name = reader.GetString(1);
  1845. }
  1846. if (!reader.IsDBNull(2))
  1847. {
  1848. chapter.ImagePath = reader.GetString(2);
  1849. }
  1850. if (!reader.IsDBNull(3))
  1851. {
  1852. chapter.ImageDateModified = reader[3].ReadDateTime();
  1853. }
  1854. return chapter;
  1855. }
  1856. /// <summary>
  1857. /// Saves the chapters.
  1858. /// </summary>
  1859. public void SaveChapters(Guid id, List<ChapterInfo> chapters)
  1860. {
  1861. CheckDisposed();
  1862. if (id == Guid.Empty)
  1863. {
  1864. throw new ArgumentNullException("id");
  1865. }
  1866. if (chapters == null)
  1867. {
  1868. throw new ArgumentNullException("chapters");
  1869. }
  1870. var index = 0;
  1871. using (WriteLock.Write())
  1872. {
  1873. using (var connection = CreateConnection())
  1874. {
  1875. connection.RunInTransaction(db =>
  1876. {
  1877. // First delete chapters
  1878. db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", id.ToGuidBlob());
  1879. using (var saveChapterStatement = PrepareStatement(db, "replace into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath, ImageDateModified) values (@ItemId, @ChapterIndex, @StartPositionTicks, @Name, @ImagePath, @ImageDateModified)"))
  1880. {
  1881. foreach (var chapter in chapters)
  1882. {
  1883. if (index > 0)
  1884. {
  1885. saveChapterStatement.Reset();
  1886. }
  1887. saveChapterStatement.TryBind("@ItemId", id.ToGuidBlob());
  1888. saveChapterStatement.TryBind("@ChapterIndex", index);
  1889. saveChapterStatement.TryBind("@StartPositionTicks", chapter.StartPositionTicks);
  1890. saveChapterStatement.TryBind("@Name", chapter.Name);
  1891. saveChapterStatement.TryBind("@ImagePath", chapter.ImagePath);
  1892. saveChapterStatement.TryBind("@ImageDateModified", chapter.ImageDateModified);
  1893. saveChapterStatement.MoveNext();
  1894. index++;
  1895. }
  1896. }
  1897. }, TransactionMode);
  1898. }
  1899. }
  1900. }
  1901. private bool EnableJoinUserData(InternalItemsQuery query)
  1902. {
  1903. if (query.User == null)
  1904. {
  1905. return false;
  1906. }
  1907. if (query.SimilarTo != null && query.User != null)
  1908. {
  1909. //return true;
  1910. }
  1911. var sortingFields = query.OrderBy.Select(i => i.Item1).ToList();
  1912. if (sortingFields.Contains(ItemSortBy.IsFavoriteOrLiked, StringComparer.OrdinalIgnoreCase))
  1913. {
  1914. return true;
  1915. }
  1916. if (sortingFields.Contains(ItemSortBy.IsPlayed, StringComparer.OrdinalIgnoreCase))
  1917. {
  1918. return true;
  1919. }
  1920. if (sortingFields.Contains(ItemSortBy.IsUnplayed, StringComparer.OrdinalIgnoreCase))
  1921. {
  1922. return true;
  1923. }
  1924. if (sortingFields.Contains(ItemSortBy.PlayCount, StringComparer.OrdinalIgnoreCase))
  1925. {
  1926. return true;
  1927. }
  1928. if (sortingFields.Contains(ItemSortBy.DatePlayed, StringComparer.OrdinalIgnoreCase))
  1929. {
  1930. return true;
  1931. }
  1932. if (sortingFields.Contains(ItemSortBy.SeriesDatePlayed, StringComparer.OrdinalIgnoreCase))
  1933. {
  1934. return true;
  1935. }
  1936. if (query.IsFavoriteOrLiked.HasValue)
  1937. {
  1938. return true;
  1939. }
  1940. if (query.IsFavorite.HasValue)
  1941. {
  1942. return true;
  1943. }
  1944. if (query.IsResumable.HasValue)
  1945. {
  1946. return true;
  1947. }
  1948. if (query.IsPlayed.HasValue)
  1949. {
  1950. return true;
  1951. }
  1952. if (query.IsLiked.HasValue)
  1953. {
  1954. return true;
  1955. }
  1956. return false;
  1957. }
  1958. private readonly List<ItemFields> allFields = Enum.GetNames(typeof(ItemFields))
  1959. .Select(i => (ItemFields)Enum.Parse(typeof(ItemFields), i, true))
  1960. .ToList();
  1961. private IEnumerable<string> GetColumnNamesFromField(ItemFields field)
  1962. {
  1963. if (field == ItemFields.Settings)
  1964. {
  1965. return new[] { "IsLocked", "PreferredMetadataCountryCode", "PreferredMetadataLanguage", "LockedFields" };
  1966. }
  1967. if (field == ItemFields.ServiceName)
  1968. {
  1969. return new[] { "ExternalServiceId" };
  1970. }
  1971. if (field == ItemFields.SortName)
  1972. {
  1973. return new[] { "ForcedSortName" };
  1974. }
  1975. if (field == ItemFields.Taglines)
  1976. {
  1977. return new[] { "Tagline" };
  1978. }
  1979. if (field == ItemFields.Tags)
  1980. {
  1981. return new[] { "Tags" };
  1982. }
  1983. return new[] { field.ToString() };
  1984. }
  1985. private bool HasField(InternalItemsQuery query, ItemFields name)
  1986. {
  1987. var fields = query.DtoOptions.Fields;
  1988. switch (name)
  1989. {
  1990. case ItemFields.HomePageUrl:
  1991. case ItemFields.CustomRating:
  1992. case ItemFields.ProductionLocations:
  1993. case ItemFields.Settings:
  1994. case ItemFields.OriginalTitle:
  1995. case ItemFields.Taglines:
  1996. case ItemFields.SortName:
  1997. case ItemFields.Studios:
  1998. case ItemFields.Tags:
  1999. case ItemFields.ThemeSongIds:
  2000. case ItemFields.ThemeVideoIds:
  2001. case ItemFields.DateCreated:
  2002. case ItemFields.Overview:
  2003. case ItemFields.Genres:
  2004. case ItemFields.DateLastMediaAdded:
  2005. case ItemFields.ExternalEtag:
  2006. case ItemFields.PresentationUniqueKey:
  2007. case ItemFields.InheritedParentalRatingValue:
  2008. case ItemFields.ExternalSeriesId:
  2009. case ItemFields.SeriesPresentationUniqueKey:
  2010. case ItemFields.DateLastRefreshed:
  2011. case ItemFields.DateLastSaved:
  2012. return fields.Contains(name);
  2013. case ItemFields.ServiceName:
  2014. return true;
  2015. default:
  2016. return true;
  2017. }
  2018. }
  2019. private bool HasProgramAttributes(InternalItemsQuery query)
  2020. {
  2021. var excludeParentTypes = new string[]
  2022. {
  2023. "Series",
  2024. "Season",
  2025. "MusicAlbum",
  2026. "MusicArtist",
  2027. "PhotoAlbum"
  2028. };
  2029. if (excludeParentTypes.Contains(query.ParentType ?? string.Empty, StringComparer.OrdinalIgnoreCase))
  2030. {
  2031. return false;
  2032. }
  2033. if (query.IncludeItemTypes.Length == 0)
  2034. {
  2035. return true;
  2036. }
  2037. var types = new string[]
  2038. {
  2039. "Program",
  2040. "Recording",
  2041. "TvChannel",
  2042. "LiveTvAudioRecording",
  2043. "LiveTvVideoRecording",
  2044. "LiveTvProgram",
  2045. "LiveTvTvChannel"
  2046. };
  2047. return types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase));
  2048. }
  2049. private bool HasStartDate(InternalItemsQuery query)
  2050. {
  2051. var excludeParentTypes = new string[]
  2052. {
  2053. "Series",
  2054. "Season",
  2055. "MusicAlbum",
  2056. "MusicArtist",
  2057. "PhotoAlbum"
  2058. };
  2059. if (excludeParentTypes.Contains(query.ParentType ?? string.Empty, StringComparer.OrdinalIgnoreCase))
  2060. {
  2061. return false;
  2062. }
  2063. if (query.IncludeItemTypes.Length == 0)
  2064. {
  2065. return true;
  2066. }
  2067. var types = new string[]
  2068. {
  2069. "Program",
  2070. "Recording",
  2071. "LiveTvAudioRecording",
  2072. "LiveTvVideoRecording",
  2073. "LiveTvProgram"
  2074. };
  2075. return types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase));
  2076. }
  2077. private bool HasEpisodeAttributes(InternalItemsQuery query)
  2078. {
  2079. if (query.IncludeItemTypes.Length == 0)
  2080. {
  2081. return true;
  2082. }
  2083. var types = new string[]
  2084. {
  2085. "Episode"
  2086. };
  2087. return types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase));
  2088. }
  2089. private bool HasTrailerTypes(InternalItemsQuery query)
  2090. {
  2091. if (query.IncludeItemTypes.Length == 0)
  2092. {
  2093. return true;
  2094. }
  2095. var types = new string[]
  2096. {
  2097. "Trailer"
  2098. };
  2099. return types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase));
  2100. }
  2101. private bool HasArtistFields(InternalItemsQuery query)
  2102. {
  2103. var excludeParentTypes = new string[]
  2104. {
  2105. "Series",
  2106. "Season",
  2107. "PhotoAlbum"
  2108. };
  2109. if (excludeParentTypes.Contains(query.ParentType ?? string.Empty, StringComparer.OrdinalIgnoreCase))
  2110. {
  2111. return false;
  2112. }
  2113. if (query.IncludeItemTypes.Length == 0)
  2114. {
  2115. return true;
  2116. }
  2117. var types = new string[]
  2118. {
  2119. "Audio",
  2120. "MusicAlbum",
  2121. "MusicVideo",
  2122. "AudioBook",
  2123. "AudioPodcast",
  2124. "LiveTvAudioRecording",
  2125. "Recording"
  2126. };
  2127. return types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase));
  2128. }
  2129. private bool HasSeriesFields(InternalItemsQuery query)
  2130. {
  2131. var excludeParentTypes = new string[]
  2132. {
  2133. "PhotoAlbum"
  2134. };
  2135. if (excludeParentTypes.Contains(query.ParentType ?? string.Empty, StringComparer.OrdinalIgnoreCase))
  2136. {
  2137. return false;
  2138. }
  2139. if (query.IncludeItemTypes.Length == 0)
  2140. {
  2141. return true;
  2142. }
  2143. var types = new string[]
  2144. {
  2145. "Book",
  2146. "AudioBook",
  2147. "Episode",
  2148. "Season"
  2149. };
  2150. return types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase));
  2151. }
  2152. private string[] GetFinalColumnsToSelect(InternalItemsQuery query, string[] startColumns)
  2153. {
  2154. var list = startColumns.ToList();
  2155. foreach (var field in allFields)
  2156. {
  2157. if (!HasField(query, field))
  2158. {
  2159. foreach (var fieldToRemove in GetColumnNamesFromField(field).ToList())
  2160. {
  2161. list.Remove(fieldToRemove);
  2162. }
  2163. }
  2164. }
  2165. if (!HasProgramAttributes(query))
  2166. {
  2167. list.Remove("IsKids");
  2168. list.Remove("IsMovie");
  2169. list.Remove("IsSports");
  2170. list.Remove("IsSeries");
  2171. list.Remove("IsLive");
  2172. list.Remove("IsNews");
  2173. list.Remove("IsPremiere");
  2174. list.Remove("EpisodeTitle");
  2175. list.Remove("IsRepeat");
  2176. list.Remove("ShowId");
  2177. }
  2178. if (!HasEpisodeAttributes(query))
  2179. {
  2180. list.Remove("SeasonName");
  2181. list.Remove("SeasonId");
  2182. }
  2183. if (!HasStartDate(query))
  2184. {
  2185. list.Remove("StartDate");
  2186. }
  2187. if (!HasTrailerTypes(query))
  2188. {
  2189. list.Remove("TrailerTypes");
  2190. }
  2191. if (!HasArtistFields(query))
  2192. {
  2193. list.Remove("AlbumArtists");
  2194. list.Remove("Artists");
  2195. }
  2196. if (!HasSeriesFields(query))
  2197. {
  2198. list.Remove("SeriesId");
  2199. list.Remove("SeriesName");
  2200. }
  2201. if (!HasEpisodeAttributes(query))
  2202. {
  2203. list.Remove("SeasonName");
  2204. list.Remove("SeasonId");
  2205. }
  2206. if (!query.DtoOptions.EnableImages)
  2207. {
  2208. list.Remove("Images");
  2209. }
  2210. if (EnableJoinUserData(query))
  2211. {
  2212. list.Add("UserData.UserId");
  2213. list.Add("UserData.lastPlayedDate");
  2214. list.Add("UserData.playbackPositionTicks");
  2215. list.Add("UserData.playcount");
  2216. list.Add("UserData.isFavorite");
  2217. list.Add("UserData.played");
  2218. list.Add("UserData.rating");
  2219. }
  2220. if (query.SimilarTo != null)
  2221. {
  2222. var item = query.SimilarTo;
  2223. var builder = new StringBuilder();
  2224. builder.Append("(");
  2225. builder.Append("((OfficialRating=@ItemOfficialRating) * 10)");
  2226. //builder.Append("+ ((ProductionYear=@ItemProductionYear) * 10)");
  2227. builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 10 Then 2 Else 0 End )");
  2228. builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 2 Else 0 End )");
  2229. //// genres, tags
  2230. builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type in (2,3,4,5) and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and Type in (2,3,4,5))) * 10)");
  2231. //builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=3 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=3)) * 3)");
  2232. //builder.Append("+ ((Select count(Name) from People where ItemId=Guid and Name in (select Name from People where ItemId=@SimilarItemId)) * 3)");
  2233. ////builder.Append("(select group_concat((Select Name from People where ItemId=Guid and Name in (Select Name from People where ItemId=@SimilarItemId)), '|'))");
  2234. builder.Append(") as SimilarityScore");
  2235. list.Add(builder.ToString());
  2236. var excludeIds = query.ExcludeItemIds.ToList();
  2237. excludeIds.Add(item.Id.ToString("N"));
  2238. if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Trailer).Name))
  2239. {
  2240. var hasTrailers = item as IHasTrailers;
  2241. if (hasTrailers != null)
  2242. {
  2243. excludeIds.AddRange(hasTrailers.GetTrailerIds().Select(i => i.ToString("N")));
  2244. }
  2245. }
  2246. query.ExcludeItemIds = excludeIds.ToArray(excludeIds.Count);
  2247. query.ExcludeProviderIds = item.ProviderIds;
  2248. }
  2249. return list.ToArray(list.Count);
  2250. }
  2251. private void BindSimilarParams(InternalItemsQuery query, IStatement statement)
  2252. {
  2253. var item = query.SimilarTo;
  2254. if (item == null)
  2255. {
  2256. return;
  2257. }
  2258. statement.TryBind("@ItemOfficialRating", item.OfficialRating);
  2259. statement.TryBind("@ItemProductionYear", item.ProductionYear ?? 0);
  2260. statement.TryBind("@SimilarItemId", item.Id);
  2261. }
  2262. private string GetJoinUserDataText(InternalItemsQuery query)
  2263. {
  2264. if (!EnableJoinUserData(query))
  2265. {
  2266. return string.Empty;
  2267. }
  2268. return " left join UserData on UserDataKey=UserData.Key And (UserId=@UserId)";
  2269. }
  2270. private string GetGroupBy(InternalItemsQuery query)
  2271. {
  2272. var groups = new List<string>();
  2273. if (EnableGroupByPresentationUniqueKey(query))
  2274. {
  2275. groups.Add("PresentationUniqueKey");
  2276. }
  2277. if (query.GroupBySeriesPresentationUniqueKey)
  2278. {
  2279. groups.Add("SeriesPresentationUniqueKey");
  2280. }
  2281. if (groups.Count > 0)
  2282. {
  2283. return " Group by " + string.Join(",", groups.ToArray(groups.Count));
  2284. }
  2285. return string.Empty;
  2286. }
  2287. private string GetFromText(string alias = "A")
  2288. {
  2289. return " from TypedBaseItems " + alias;
  2290. }
  2291. public int GetCount(InternalItemsQuery query)
  2292. {
  2293. if (query == null)
  2294. {
  2295. throw new ArgumentNullException("query");
  2296. }
  2297. CheckDisposed();
  2298. //Logger.Info("GetItemList: " + _environmentInfo.StackTrace);
  2299. var now = DateTime.UtcNow;
  2300. // Hack for right now since we currently don't support filtering out these duplicates within a query
  2301. if (query.Limit.HasValue && query.EnableGroupByMetadataKey)
  2302. {
  2303. query.Limit = query.Limit.Value + 4;
  2304. }
  2305. var commandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count(distinct PresentationUniqueKey)" })) + GetFromText();
  2306. commandText += GetJoinUserDataText(query);
  2307. var whereClauses = GetWhereClauses(query, null);
  2308. var whereText = whereClauses.Count == 0 ?
  2309. string.Empty :
  2310. " where " + string.Join(" AND ", whereClauses.ToArray(whereClauses.Count));
  2311. commandText += whereText;
  2312. //commandText += GetGroupBy(query);
  2313. using (WriteLock.Read())
  2314. {
  2315. using (var connection = CreateConnection(true))
  2316. {
  2317. using (var statement = PrepareStatementSafe(connection, commandText))
  2318. {
  2319. if (EnableJoinUserData(query))
  2320. {
  2321. statement.TryBind("@UserId", query.User.Id);
  2322. }
  2323. BindSimilarParams(query, statement);
  2324. // Running this again will bind the params
  2325. GetWhereClauses(query, statement);
  2326. var count = statement.ExecuteQuery().SelectScalarInt().First();
  2327. LogQueryTime("GetCount", commandText, now);
  2328. return count;
  2329. }
  2330. }
  2331. }
  2332. }
  2333. public List<BaseItem> GetItemList(InternalItemsQuery query)
  2334. {
  2335. if (query == null)
  2336. {
  2337. throw new ArgumentNullException("query");
  2338. }
  2339. CheckDisposed();
  2340. //Logger.Info("GetItemList: " + _environmentInfo.StackTrace);
  2341. var now = DateTime.UtcNow;
  2342. // Hack for right now since we currently don't support filtering out these duplicates within a query
  2343. if (query.Limit.HasValue && query.EnableGroupByMetadataKey)
  2344. {
  2345. query.Limit = query.Limit.Value + 4;
  2346. }
  2347. var commandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns)) + GetFromText();
  2348. commandText += GetJoinUserDataText(query);
  2349. var whereClauses = GetWhereClauses(query, null);
  2350. var whereText = whereClauses.Count == 0 ?
  2351. string.Empty :
  2352. " where " + string.Join(" AND ", whereClauses.ToArray(whereClauses.Count));
  2353. commandText += whereText;
  2354. commandText += GetGroupBy(query);
  2355. commandText += GetOrderByText(query);
  2356. if (query.Limit.HasValue || query.StartIndex.HasValue)
  2357. {
  2358. var offset = query.StartIndex ?? 0;
  2359. if (query.Limit.HasValue || offset > 0)
  2360. {
  2361. commandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture);
  2362. }
  2363. if (offset > 0)
  2364. {
  2365. commandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture);
  2366. }
  2367. }
  2368. using (WriteLock.Read())
  2369. {
  2370. using (var connection = CreateConnection(true))
  2371. {
  2372. var list = new List<BaseItem>();
  2373. using (var statement = PrepareStatementSafe(connection, commandText))
  2374. {
  2375. if (EnableJoinUserData(query))
  2376. {
  2377. statement.TryBind("@UserId", query.User.Id);
  2378. }
  2379. BindSimilarParams(query, statement);
  2380. // Running this again will bind the params
  2381. GetWhereClauses(query, statement);
  2382. var hasEpisodeAttributes = HasEpisodeAttributes(query);
  2383. var hasProgramAttributes = HasProgramAttributes(query);
  2384. var hasStartDate = HasStartDate(query);
  2385. var hasTrailerTypes = HasTrailerTypes(query);
  2386. var hasArtistFields = HasArtistFields(query);
  2387. var hasSeriesFields = HasSeriesFields(query);
  2388. foreach (var row in statement.ExecuteQuery())
  2389. {
  2390. var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields);
  2391. if (item != null)
  2392. {
  2393. list.Add(item);
  2394. }
  2395. }
  2396. }
  2397. // Hack for right now since we currently don't support filtering out these duplicates within a query
  2398. if (query.EnableGroupByMetadataKey)
  2399. {
  2400. var limit = query.Limit ?? int.MaxValue;
  2401. limit -= 4;
  2402. var newList = new List<BaseItem>();
  2403. foreach (var item in list)
  2404. {
  2405. AddItem(newList, item);
  2406. if (newList.Count >= limit)
  2407. {
  2408. break;
  2409. }
  2410. }
  2411. list = newList;
  2412. }
  2413. LogQueryTime("GetItemList", commandText, now);
  2414. return list;
  2415. }
  2416. }
  2417. }
  2418. private void AddItem(List<BaseItem> items, BaseItem newItem)
  2419. {
  2420. var providerIds = newItem.ProviderIds.ToList();
  2421. for (var i = 0; i < items.Count; i++)
  2422. {
  2423. var item = items[i];
  2424. foreach (var providerId in providerIds)
  2425. {
  2426. if (providerId.Key == MetadataProviders.TmdbCollection.ToString())
  2427. {
  2428. continue;
  2429. }
  2430. if (item.GetProviderId(providerId.Key) == providerId.Value)
  2431. {
  2432. if (newItem.SourceType == SourceType.Library)
  2433. {
  2434. items[i] = newItem;
  2435. }
  2436. return;
  2437. }
  2438. }
  2439. }
  2440. items.Add(newItem);
  2441. }
  2442. private void LogQueryTime(string methodName, string commandText, DateTime startDate)
  2443. {
  2444. var elapsed = (DateTime.UtcNow - startDate).TotalMilliseconds;
  2445. var slowThreshold = 1000;
  2446. #if DEBUG
  2447. slowThreshold = 10;
  2448. #endif
  2449. if (elapsed >= slowThreshold)
  2450. {
  2451. Logger.Debug("{2} query time (slow): {0}ms. Query: {1}",
  2452. Convert.ToInt32(elapsed),
  2453. commandText,
  2454. methodName);
  2455. }
  2456. else
  2457. {
  2458. //Logger.Debug("{2} query time: {0}ms. Query: {1}",
  2459. // Convert.ToInt32(elapsed),
  2460. // commandText,
  2461. // methodName);
  2462. }
  2463. }
  2464. public QueryResult<BaseItem> GetItems(InternalItemsQuery query)
  2465. {
  2466. if (query == null)
  2467. {
  2468. throw new ArgumentNullException("query");
  2469. }
  2470. CheckDisposed();
  2471. if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0))
  2472. {
  2473. var returnList = GetItemList(query);
  2474. return new QueryResult<BaseItem>
  2475. {
  2476. Items = returnList.ToArray(returnList.Count),
  2477. TotalRecordCount = returnList.Count
  2478. };
  2479. }
  2480. //Logger.Info("GetItems: " + _environmentInfo.StackTrace);
  2481. var now = DateTime.UtcNow;
  2482. var list = new List<BaseItem>();
  2483. // Hack for right now since we currently don't support filtering out these duplicates within a query
  2484. if (query.Limit.HasValue && query.EnableGroupByMetadataKey)
  2485. {
  2486. query.Limit = query.Limit.Value + 4;
  2487. }
  2488. var commandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns)) + GetFromText();
  2489. commandText += GetJoinUserDataText(query);
  2490. var whereClauses = GetWhereClauses(query, null);
  2491. var whereText = whereClauses.Count == 0 ?
  2492. string.Empty :
  2493. " where " + string.Join(" AND ", whereClauses.ToArray(whereClauses.Count));
  2494. var whereTextWithoutPaging = whereText;
  2495. commandText += whereText;
  2496. commandText += GetGroupBy(query);
  2497. commandText += GetOrderByText(query);
  2498. if (query.Limit.HasValue || query.StartIndex.HasValue)
  2499. {
  2500. var offset = query.StartIndex ?? 0;
  2501. if (query.Limit.HasValue || offset > 0)
  2502. {
  2503. commandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture);
  2504. }
  2505. if (offset > 0)
  2506. {
  2507. commandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture);
  2508. }
  2509. }
  2510. var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0;
  2511. var statementTexts = new List<string>();
  2512. if (!isReturningZeroItems)
  2513. {
  2514. statementTexts.Add(commandText);
  2515. }
  2516. if (query.EnableTotalRecordCount)
  2517. {
  2518. commandText = string.Empty;
  2519. if (EnableGroupByPresentationUniqueKey(query))
  2520. {
  2521. commandText += " select count (distinct PresentationUniqueKey)" + GetFromText();
  2522. }
  2523. else if (query.GroupBySeriesPresentationUniqueKey)
  2524. {
  2525. commandText += " select count (distinct SeriesPresentationUniqueKey)" + GetFromText();
  2526. }
  2527. else
  2528. {
  2529. commandText += " select count (guid)" + GetFromText();
  2530. }
  2531. commandText += GetJoinUserDataText(query);
  2532. commandText += whereTextWithoutPaging;
  2533. statementTexts.Add(commandText);
  2534. }
  2535. using (WriteLock.Read())
  2536. {
  2537. using (var connection = CreateConnection(true))
  2538. {
  2539. return connection.RunInTransaction(db =>
  2540. {
  2541. var result = new QueryResult<BaseItem>();
  2542. var statements = PrepareAllSafe(db, statementTexts);
  2543. if (!isReturningZeroItems)
  2544. {
  2545. using (var statement = statements[0])
  2546. {
  2547. if (EnableJoinUserData(query))
  2548. {
  2549. statement.TryBind("@UserId", query.User.Id);
  2550. }
  2551. BindSimilarParams(query, statement);
  2552. // Running this again will bind the params
  2553. GetWhereClauses(query, statement);
  2554. var hasEpisodeAttributes = HasEpisodeAttributes(query);
  2555. var hasProgramAttributes = HasProgramAttributes(query);
  2556. var hasStartDate = HasStartDate(query);
  2557. var hasTrailerTypes = HasTrailerTypes(query);
  2558. var hasArtistFields = HasArtistFields(query);
  2559. var hasSeriesFields = HasSeriesFields(query);
  2560. foreach (var row in statement.ExecuteQuery())
  2561. {
  2562. var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields);
  2563. if (item != null)
  2564. {
  2565. list.Add(item);
  2566. }
  2567. }
  2568. }
  2569. }
  2570. if (query.EnableTotalRecordCount)
  2571. {
  2572. using (var statement = statements[statements.Count - 1])
  2573. {
  2574. if (EnableJoinUserData(query))
  2575. {
  2576. statement.TryBind("@UserId", query.User.Id);
  2577. }
  2578. BindSimilarParams(query, statement);
  2579. // Running this again will bind the params
  2580. GetWhereClauses(query, statement);
  2581. result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First();
  2582. }
  2583. }
  2584. LogQueryTime("GetItems", commandText, now);
  2585. result.Items = list.ToArray(list.Count);
  2586. return result;
  2587. }, ReadTransactionMode);
  2588. }
  2589. }
  2590. }
  2591. private string GetOrderByText(InternalItemsQuery query)
  2592. {
  2593. var orderBy = query.OrderBy.ToList();
  2594. var enableOrderInversion = false;
  2595. if (query.SimilarTo != null)
  2596. {
  2597. if (orderBy.Count == 0)
  2598. {
  2599. orderBy.Add(new Tuple<string, SortOrder>(ItemSortBy.Random, SortOrder.Ascending));
  2600. orderBy.Add(new Tuple<string, SortOrder>("SimilarityScore", SortOrder.Descending));
  2601. //orderBy.Add(new Tuple<string, SortOrder>(ItemSortBy.Random, SortOrder.Ascending));
  2602. }
  2603. }
  2604. query.OrderBy = orderBy.ToArray();
  2605. if (orderBy.Count == 0)
  2606. {
  2607. return string.Empty;
  2608. }
  2609. return " ORDER BY " + string.Join(",", orderBy.Select(i =>
  2610. {
  2611. var columnMap = MapOrderByField(i.Item1, query);
  2612. var columnAscending = i.Item2 == SortOrder.Ascending;
  2613. if (columnMap.Item2 && enableOrderInversion)
  2614. {
  2615. columnAscending = !columnAscending;
  2616. }
  2617. var sortOrder = columnAscending ? "ASC" : "DESC";
  2618. return columnMap.Item1 + " " + sortOrder;
  2619. }).ToArray());
  2620. }
  2621. private Tuple<string, bool> MapOrderByField(string name, InternalItemsQuery query)
  2622. {
  2623. if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase))
  2624. {
  2625. // TODO
  2626. return new Tuple<string, bool>("SortName", false);
  2627. }
  2628. if (string.Equals(name, ItemSortBy.Runtime, StringComparison.OrdinalIgnoreCase))
  2629. {
  2630. return new Tuple<string, bool>("RuntimeTicks", false);
  2631. }
  2632. if (string.Equals(name, ItemSortBy.Random, StringComparison.OrdinalIgnoreCase))
  2633. {
  2634. return new Tuple<string, bool>("RANDOM()", false);
  2635. }
  2636. if (string.Equals(name, ItemSortBy.DatePlayed, StringComparison.OrdinalIgnoreCase))
  2637. {
  2638. if (query.GroupBySeriesPresentationUniqueKey)
  2639. {
  2640. return new Tuple<string, bool>("MAX(LastPlayedDate)", false);
  2641. }
  2642. return new Tuple<string, bool>("LastPlayedDate", false);
  2643. }
  2644. if (string.Equals(name, ItemSortBy.PlayCount, StringComparison.OrdinalIgnoreCase))
  2645. {
  2646. return new Tuple<string, bool>("PlayCount", false);
  2647. }
  2648. if (string.Equals(name, ItemSortBy.IsFavoriteOrLiked, StringComparison.OrdinalIgnoreCase))
  2649. {
  2650. // (Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 10 Then 2 Else 0 End )
  2651. return new Tuple<string, bool>("(Select Case When IsFavorite is null Then 0 Else IsFavorite End )", true);
  2652. }
  2653. if (string.Equals(name, ItemSortBy.IsFolder, StringComparison.OrdinalIgnoreCase))
  2654. {
  2655. return new Tuple<string, bool>("IsFolder", true);
  2656. }
  2657. if (string.Equals(name, ItemSortBy.IsPlayed, StringComparison.OrdinalIgnoreCase))
  2658. {
  2659. return new Tuple<string, bool>("played", true);
  2660. }
  2661. if (string.Equals(name, ItemSortBy.IsUnplayed, StringComparison.OrdinalIgnoreCase))
  2662. {
  2663. return new Tuple<string, bool>("played", false);
  2664. }
  2665. if (string.Equals(name, ItemSortBy.DateLastContentAdded, StringComparison.OrdinalIgnoreCase))
  2666. {
  2667. return new Tuple<string, bool>("DateLastMediaAdded", false);
  2668. }
  2669. if (string.Equals(name, ItemSortBy.Artist, StringComparison.OrdinalIgnoreCase))
  2670. {
  2671. return new Tuple<string, bool>("(select CleanValue from itemvalues where ItemId=Guid and Type=0 LIMIT 1)", false);
  2672. }
  2673. if (string.Equals(name, ItemSortBy.AlbumArtist, StringComparison.OrdinalIgnoreCase))
  2674. {
  2675. return new Tuple<string, bool>("(select CleanValue from itemvalues where ItemId=Guid and Type=1 LIMIT 1)", false);
  2676. }
  2677. if (string.Equals(name, ItemSortBy.OfficialRating, StringComparison.OrdinalIgnoreCase))
  2678. {
  2679. return new Tuple<string, bool>("InheritedParentalRatingValue", false);
  2680. }
  2681. if (string.Equals(name, ItemSortBy.Studio, StringComparison.OrdinalIgnoreCase))
  2682. {
  2683. return new Tuple<string, bool>("(select CleanValue from itemvalues where ItemId=Guid and Type=3 LIMIT 1)", false);
  2684. }
  2685. if (string.Equals(name, ItemSortBy.SeriesDatePlayed, StringComparison.OrdinalIgnoreCase))
  2686. {
  2687. return new Tuple<string, bool>("(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where Played=1 and B.SeriesPresentationUniqueKey=A.PresentationUniqueKey)", false);
  2688. }
  2689. if (string.Equals(name, ItemSortBy.SeriesSortName, StringComparison.OrdinalIgnoreCase))
  2690. {
  2691. return new Tuple<string, bool>("SeriesName", false);
  2692. }
  2693. return new Tuple<string, bool>(name, false);
  2694. }
  2695. public List<Guid> GetItemIdsList(InternalItemsQuery query)
  2696. {
  2697. if (query == null)
  2698. {
  2699. throw new ArgumentNullException("query");
  2700. }
  2701. CheckDisposed();
  2702. //Logger.Info("GetItemIdsList: " + _environmentInfo.StackTrace);
  2703. var now = DateTime.UtcNow;
  2704. var commandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" })) + GetFromText();
  2705. commandText += GetJoinUserDataText(query);
  2706. var whereClauses = GetWhereClauses(query, null);
  2707. var whereText = whereClauses.Count == 0 ?
  2708. string.Empty :
  2709. " where " + string.Join(" AND ", whereClauses.ToArray(whereClauses.Count));
  2710. commandText += whereText;
  2711. commandText += GetGroupBy(query);
  2712. commandText += GetOrderByText(query);
  2713. if (query.Limit.HasValue || query.StartIndex.HasValue)
  2714. {
  2715. var offset = query.StartIndex ?? 0;
  2716. if (query.Limit.HasValue || offset > 0)
  2717. {
  2718. commandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture);
  2719. }
  2720. if (offset > 0)
  2721. {
  2722. commandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture);
  2723. }
  2724. }
  2725. using (WriteLock.Read())
  2726. {
  2727. using (var connection = CreateConnection(true))
  2728. {
  2729. var list = new List<Guid>();
  2730. using (var statement = PrepareStatementSafe(connection, commandText))
  2731. {
  2732. if (EnableJoinUserData(query))
  2733. {
  2734. statement.TryBind("@UserId", query.User.Id);
  2735. }
  2736. BindSimilarParams(query, statement);
  2737. // Running this again will bind the params
  2738. GetWhereClauses(query, statement);
  2739. foreach (var row in statement.ExecuteQuery())
  2740. {
  2741. list.Add(row[0].ReadGuidFromBlob());
  2742. }
  2743. }
  2744. LogQueryTime("GetItemList", commandText, now);
  2745. return list;
  2746. }
  2747. }
  2748. }
  2749. public List<Tuple<Guid, string>> GetItemIdsWithPath(InternalItemsQuery query)
  2750. {
  2751. if (query == null)
  2752. {
  2753. throw new ArgumentNullException("query");
  2754. }
  2755. CheckDisposed();
  2756. var now = DateTime.UtcNow;
  2757. var commandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid", "path" })) + GetFromText();
  2758. var whereClauses = GetWhereClauses(query, null);
  2759. var whereText = whereClauses.Count == 0 ?
  2760. string.Empty :
  2761. " where " + string.Join(" AND ", whereClauses.ToArray(whereClauses.Count));
  2762. commandText += whereText;
  2763. commandText += GetGroupBy(query);
  2764. commandText += GetOrderByText(query);
  2765. if (query.Limit.HasValue || query.StartIndex.HasValue)
  2766. {
  2767. var offset = query.StartIndex ?? 0;
  2768. if (query.Limit.HasValue || offset > 0)
  2769. {
  2770. commandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture);
  2771. }
  2772. if (offset > 0)
  2773. {
  2774. commandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture);
  2775. }
  2776. }
  2777. var list = new List<Tuple<Guid, string>>();
  2778. using (WriteLock.Read())
  2779. {
  2780. using (var connection = CreateConnection(true))
  2781. {
  2782. using (var statement = PrepareStatementSafe(connection, commandText))
  2783. {
  2784. if (EnableJoinUserData(query))
  2785. {
  2786. statement.TryBind("@UserId", query.User.Id);
  2787. }
  2788. // Running this again will bind the params
  2789. GetWhereClauses(query, statement);
  2790. foreach (var row in statement.ExecuteQuery())
  2791. {
  2792. var id = row.GetGuid(0);
  2793. string path = null;
  2794. if (!row.IsDBNull(1))
  2795. {
  2796. path = row.GetString(1);
  2797. }
  2798. list.Add(new Tuple<Guid, string>(id, path));
  2799. }
  2800. }
  2801. }
  2802. LogQueryTime("GetItemIdsWithPath", commandText, now);
  2803. return list;
  2804. }
  2805. }
  2806. public QueryResult<Guid> GetItemIds(InternalItemsQuery query)
  2807. {
  2808. if (query == null)
  2809. {
  2810. throw new ArgumentNullException("query");
  2811. }
  2812. CheckDisposed();
  2813. if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0))
  2814. {
  2815. var returnList = GetItemIdsList(query);
  2816. return new QueryResult<Guid>
  2817. {
  2818. Items = returnList.ToArray(returnList.Count),
  2819. TotalRecordCount = returnList.Count
  2820. };
  2821. }
  2822. //Logger.Info("GetItemIds: " + _environmentInfo.StackTrace);
  2823. var now = DateTime.UtcNow;
  2824. var commandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" })) + GetFromText();
  2825. commandText += GetJoinUserDataText(query);
  2826. var whereClauses = GetWhereClauses(query, null);
  2827. var whereText = whereClauses.Count == 0 ?
  2828. string.Empty :
  2829. " where " + string.Join(" AND ", whereClauses.ToArray(whereClauses.Count));
  2830. var whereTextWithoutPaging = whereText;
  2831. commandText += whereText;
  2832. commandText += GetGroupBy(query);
  2833. commandText += GetOrderByText(query);
  2834. if (query.Limit.HasValue || query.StartIndex.HasValue)
  2835. {
  2836. var offset = query.StartIndex ?? 0;
  2837. if (query.Limit.HasValue || offset > 0)
  2838. {
  2839. commandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture);
  2840. }
  2841. if (offset > 0)
  2842. {
  2843. commandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture);
  2844. }
  2845. }
  2846. var list = new List<Guid>();
  2847. var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0;
  2848. var statementTexts = new List<string>();
  2849. if (!isReturningZeroItems)
  2850. {
  2851. statementTexts.Add(commandText);
  2852. }
  2853. if (query.EnableTotalRecordCount)
  2854. {
  2855. commandText = string.Empty;
  2856. if (EnableGroupByPresentationUniqueKey(query))
  2857. {
  2858. commandText += " select count (distinct PresentationUniqueKey)" + GetFromText();
  2859. }
  2860. else if (query.GroupBySeriesPresentationUniqueKey)
  2861. {
  2862. commandText += " select count (distinct SeriesPresentationUniqueKey)" + GetFromText();
  2863. }
  2864. else
  2865. {
  2866. commandText += " select count (guid)" + GetFromText();
  2867. }
  2868. commandText += GetJoinUserDataText(query);
  2869. commandText += whereTextWithoutPaging;
  2870. statementTexts.Add(commandText);
  2871. }
  2872. using (WriteLock.Read())
  2873. {
  2874. using (var connection = CreateConnection(true))
  2875. {
  2876. return connection.RunInTransaction(db =>
  2877. {
  2878. var result = new QueryResult<Guid>();
  2879. var statements = PrepareAllSafe(db, statementTexts);
  2880. if (!isReturningZeroItems)
  2881. {
  2882. using (var statement = statements[0])
  2883. {
  2884. if (EnableJoinUserData(query))
  2885. {
  2886. statement.TryBind("@UserId", query.User.Id);
  2887. }
  2888. BindSimilarParams(query, statement);
  2889. // Running this again will bind the params
  2890. GetWhereClauses(query, statement);
  2891. foreach (var row in statement.ExecuteQuery())
  2892. {
  2893. list.Add(row[0].ReadGuidFromBlob());
  2894. }
  2895. }
  2896. }
  2897. if (query.EnableTotalRecordCount)
  2898. {
  2899. using (var statement = statements[statements.Count - 1])
  2900. {
  2901. if (EnableJoinUserData(query))
  2902. {
  2903. statement.TryBind("@UserId", query.User.Id);
  2904. }
  2905. BindSimilarParams(query, statement);
  2906. // Running this again will bind the params
  2907. GetWhereClauses(query, statement);
  2908. result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First();
  2909. }
  2910. }
  2911. LogQueryTime("GetItemIds", commandText, now);
  2912. result.Items = list.ToArray(list.Count);
  2913. return result;
  2914. }, ReadTransactionMode);
  2915. }
  2916. }
  2917. }
  2918. private bool IsAlphaNumeric(string str)
  2919. {
  2920. if (string.IsNullOrWhiteSpace(str))
  2921. return false;
  2922. for (int i = 0; i < str.Length; i++)
  2923. {
  2924. if (!(char.IsLetter(str[i])) && (!(char.IsNumber(str[i]))))
  2925. return false;
  2926. }
  2927. return true;
  2928. }
  2929. private bool IsValidType(string value)
  2930. {
  2931. return IsAlphaNumeric(value);
  2932. }
  2933. private bool IsValidMediaType(string value)
  2934. {
  2935. return IsAlphaNumeric(value);
  2936. }
  2937. private bool IsValidId(string value)
  2938. {
  2939. return IsAlphaNumeric(value);
  2940. }
  2941. private bool IsValidPersonType(string value)
  2942. {
  2943. return IsAlphaNumeric(value);
  2944. }
  2945. private List<string> GetWhereClauses(InternalItemsQuery query, IStatement statement, string paramSuffix = "")
  2946. {
  2947. if (query.IsResumable ?? false)
  2948. {
  2949. query.IsVirtualItem = false;
  2950. }
  2951. var whereClauses = new List<string>();
  2952. if (EnableJoinUserData(query))
  2953. {
  2954. //whereClauses.Add("(UserId is null or UserId=@UserId)");
  2955. }
  2956. if (query.IsHD.HasValue)
  2957. {
  2958. whereClauses.Add("IsHD=@IsHD");
  2959. if (statement != null)
  2960. {
  2961. statement.TryBind("@IsHD", query.IsHD);
  2962. }
  2963. }
  2964. if (query.IsLocked.HasValue)
  2965. {
  2966. whereClauses.Add("IsLocked=@IsLocked");
  2967. if (statement != null)
  2968. {
  2969. statement.TryBind("@IsLocked", query.IsLocked);
  2970. }
  2971. }
  2972. var exclusiveProgramAttribtues = !(query.IsMovie ?? true) ||
  2973. !(query.IsSports ?? true) ||
  2974. !(query.IsKids ?? true) ||
  2975. !(query.IsNews ?? true) ||
  2976. !(query.IsSeries ?? true);
  2977. if (exclusiveProgramAttribtues)
  2978. {
  2979. if (query.IsMovie.HasValue)
  2980. {
  2981. var alternateTypes = new List<string>();
  2982. if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Movie).Name))
  2983. {
  2984. alternateTypes.Add(typeof(Movie).FullName);
  2985. }
  2986. if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Trailer).Name))
  2987. {
  2988. alternateTypes.Add(typeof(Trailer).FullName);
  2989. }
  2990. if (alternateTypes.Count == 0)
  2991. {
  2992. whereClauses.Add("IsMovie=@IsMovie");
  2993. if (statement != null)
  2994. {
  2995. statement.TryBind("@IsMovie", query.IsMovie);
  2996. }
  2997. }
  2998. else
  2999. {
  3000. whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)");
  3001. if (statement != null)
  3002. {
  3003. statement.TryBind("@IsMovie", query.IsMovie);
  3004. }
  3005. }
  3006. }
  3007. if (query.IsSeries.HasValue)
  3008. {
  3009. whereClauses.Add("IsSeries=@IsSeries");
  3010. if (statement != null)
  3011. {
  3012. statement.TryBind("@IsSeries", query.IsSeries);
  3013. }
  3014. }
  3015. if (query.IsNews.HasValue)
  3016. {
  3017. whereClauses.Add("IsNews=@IsNews");
  3018. if (statement != null)
  3019. {
  3020. statement.TryBind("@IsNews", query.IsNews);
  3021. }
  3022. }
  3023. if (query.IsKids.HasValue)
  3024. {
  3025. whereClauses.Add("IsKids=@IsKids");
  3026. if (statement != null)
  3027. {
  3028. statement.TryBind("@IsKids", query.IsKids);
  3029. }
  3030. }
  3031. if (query.IsSports.HasValue)
  3032. {
  3033. whereClauses.Add("IsSports=@IsSports");
  3034. if (statement != null)
  3035. {
  3036. statement.TryBind("@IsSports", query.IsSports);
  3037. }
  3038. }
  3039. }
  3040. else
  3041. {
  3042. var programAttribtues = new List<string>();
  3043. if (query.IsMovie ?? false)
  3044. {
  3045. var alternateTypes = new List<string>();
  3046. if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Movie).Name))
  3047. {
  3048. alternateTypes.Add(typeof(Movie).FullName);
  3049. }
  3050. if (query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(typeof(Trailer).Name))
  3051. {
  3052. alternateTypes.Add(typeof(Trailer).FullName);
  3053. }
  3054. if (alternateTypes.Count == 0)
  3055. {
  3056. programAttribtues.Add("IsMovie=@IsMovie");
  3057. }
  3058. else
  3059. {
  3060. programAttribtues.Add("(IsMovie is null OR IsMovie=@IsMovie)");
  3061. }
  3062. if (statement != null)
  3063. {
  3064. statement.TryBind("@IsMovie", true);
  3065. }
  3066. }
  3067. if (query.IsSports ?? false)
  3068. {
  3069. programAttribtues.Add("IsSports=@IsSports");
  3070. if (statement != null)
  3071. {
  3072. statement.TryBind("@IsSports", query.IsSports);
  3073. }
  3074. }
  3075. if (query.IsNews ?? false)
  3076. {
  3077. programAttribtues.Add("IsNews=@IsNews");
  3078. if (statement != null)
  3079. {
  3080. statement.TryBind("@IsNews", query.IsNews);
  3081. }
  3082. }
  3083. if (query.IsSeries ?? false)
  3084. {
  3085. programAttribtues.Add("IsSeries=@IsSeries");
  3086. if (statement != null)
  3087. {
  3088. statement.TryBind("@IsSeries", query.IsSeries);
  3089. }
  3090. }
  3091. if (query.IsKids ?? false)
  3092. {
  3093. programAttribtues.Add("IsKids=@IsKids");
  3094. if (statement != null)
  3095. {
  3096. statement.TryBind("@IsKids", query.IsKids);
  3097. }
  3098. }
  3099. if (programAttribtues.Count > 0)
  3100. {
  3101. whereClauses.Add("(" + string.Join(" OR ", programAttribtues.ToArray(programAttribtues.Count)) + ")");
  3102. }
  3103. }
  3104. if (query.SimilarTo != null && query.MinSimilarityScore > 0)
  3105. {
  3106. whereClauses.Add("SimilarityScore > " + (query.MinSimilarityScore - 1).ToString(CultureInfo.InvariantCulture));
  3107. }
  3108. if (query.IsFolder.HasValue)
  3109. {
  3110. whereClauses.Add("IsFolder=@IsFolder");
  3111. if (statement != null)
  3112. {
  3113. statement.TryBind("@IsFolder", query.IsFolder);
  3114. }
  3115. }
  3116. var includeTypes = query.IncludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray();
  3117. if (includeTypes.Length == 1)
  3118. {
  3119. whereClauses.Add("type=@type");
  3120. if (statement != null)
  3121. {
  3122. statement.TryBind("@type", includeTypes[0]);
  3123. }
  3124. }
  3125. else if (includeTypes.Length > 1)
  3126. {
  3127. var inClause = string.Join(",", includeTypes.Select(i => "'" + i + "'").ToArray());
  3128. whereClauses.Add(string.Format("type in ({0})", inClause));
  3129. }
  3130. var excludeTypes = query.ExcludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray();
  3131. if (excludeTypes.Length == 1)
  3132. {
  3133. whereClauses.Add("type<>@type");
  3134. if (statement != null)
  3135. {
  3136. statement.TryBind("@type", excludeTypes[0]);
  3137. }
  3138. }
  3139. else if (excludeTypes.Length > 1)
  3140. {
  3141. var inClause = string.Join(",", excludeTypes.Select(i => "'" + i + "'").ToArray());
  3142. whereClauses.Add(string.Format("type not in ({0})", inClause));
  3143. }
  3144. if (query.ChannelIds.Length == 1)
  3145. {
  3146. whereClauses.Add("ChannelId=@ChannelId");
  3147. if (statement != null)
  3148. {
  3149. statement.TryBind("@ChannelId", query.ChannelIds[0]);
  3150. }
  3151. }
  3152. else if (query.ChannelIds.Length > 1)
  3153. {
  3154. var inClause = string.Join(",", query.ChannelIds.Where(IsValidId).Select(i => "'" + i + "'").ToArray());
  3155. whereClauses.Add(string.Format("ChannelId in ({0})", inClause));
  3156. }
  3157. if (query.ParentId.HasValue)
  3158. {
  3159. whereClauses.Add("ParentId=@ParentId");
  3160. if (statement != null)
  3161. {
  3162. statement.TryBind("@ParentId", query.ParentId.Value);
  3163. }
  3164. }
  3165. if (!string.IsNullOrWhiteSpace(query.Path))
  3166. {
  3167. whereClauses.Add("Path=@Path");
  3168. if (statement != null)
  3169. {
  3170. statement.TryBind("@Path", query.Path);
  3171. }
  3172. }
  3173. if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey))
  3174. {
  3175. whereClauses.Add("PresentationUniqueKey=@PresentationUniqueKey");
  3176. if (statement != null)
  3177. {
  3178. statement.TryBind("@PresentationUniqueKey", query.PresentationUniqueKey);
  3179. }
  3180. }
  3181. if (query.MinCommunityRating.HasValue)
  3182. {
  3183. whereClauses.Add("CommunityRating>=@MinCommunityRating");
  3184. if (statement != null)
  3185. {
  3186. statement.TryBind("@MinCommunityRating", query.MinCommunityRating.Value);
  3187. }
  3188. }
  3189. if (query.MinIndexNumber.HasValue)
  3190. {
  3191. whereClauses.Add("IndexNumber>=@MinIndexNumber");
  3192. if (statement != null)
  3193. {
  3194. statement.TryBind("@MinIndexNumber", query.MinIndexNumber.Value);
  3195. }
  3196. }
  3197. if (query.MinDateCreated.HasValue)
  3198. {
  3199. whereClauses.Add("DateCreated>=@MinDateCreated");
  3200. if (statement != null)
  3201. {
  3202. statement.TryBind("@MinDateCreated", query.MinDateCreated.Value);
  3203. }
  3204. }
  3205. if (query.MinDateLastSaved.HasValue)
  3206. {
  3207. whereClauses.Add("(DateLastSaved not null and DateLastSaved>=@MinDateLastSavedForUser)");
  3208. if (statement != null)
  3209. {
  3210. statement.TryBind("@MinDateLastSaved", query.MinDateLastSaved.Value);
  3211. }
  3212. }
  3213. if (query.MinDateLastSavedForUser.HasValue)
  3214. {
  3215. whereClauses.Add("(DateLastSaved not null and DateLastSaved>=@MinDateLastSavedForUser)");
  3216. if (statement != null)
  3217. {
  3218. statement.TryBind("@MinDateLastSavedForUser", query.MinDateLastSavedForUser.Value);
  3219. }
  3220. }
  3221. //if (query.MinPlayers.HasValue)
  3222. //{
  3223. // whereClauses.Add("Players>=@MinPlayers");
  3224. // cmd.Parameters.Add(cmd, "@MinPlayers", DbType.Int32).Value = query.MinPlayers.Value;
  3225. //}
  3226. //if (query.MaxPlayers.HasValue)
  3227. //{
  3228. // whereClauses.Add("Players<=@MaxPlayers");
  3229. // cmd.Parameters.Add(cmd, "@MaxPlayers", DbType.Int32).Value = query.MaxPlayers.Value;
  3230. //}
  3231. if (query.IndexNumber.HasValue)
  3232. {
  3233. whereClauses.Add("IndexNumber=@IndexNumber");
  3234. if (statement != null)
  3235. {
  3236. statement.TryBind("@IndexNumber", query.IndexNumber.Value);
  3237. }
  3238. }
  3239. if (query.ParentIndexNumber.HasValue)
  3240. {
  3241. whereClauses.Add("ParentIndexNumber=@ParentIndexNumber");
  3242. if (statement != null)
  3243. {
  3244. statement.TryBind("@ParentIndexNumber", query.ParentIndexNumber.Value);
  3245. }
  3246. }
  3247. if (query.ParentIndexNumberNotEquals.HasValue)
  3248. {
  3249. whereClauses.Add("(ParentIndexNumber<>@ParentIndexNumberNotEquals or ParentIndexNumber is null)");
  3250. if (statement != null)
  3251. {
  3252. statement.TryBind("@ParentIndexNumberNotEquals", query.ParentIndexNumberNotEquals.Value);
  3253. }
  3254. }
  3255. if (query.MinEndDate.HasValue)
  3256. {
  3257. whereClauses.Add("EndDate>=@MinEndDate");
  3258. if (statement != null)
  3259. {
  3260. statement.TryBind("@MinEndDate", query.MinEndDate.Value);
  3261. }
  3262. }
  3263. if (query.MaxEndDate.HasValue)
  3264. {
  3265. whereClauses.Add("EndDate<=@MaxEndDate");
  3266. if (statement != null)
  3267. {
  3268. statement.TryBind("@MaxEndDate", query.MaxEndDate.Value);
  3269. }
  3270. }
  3271. if (query.MinStartDate.HasValue)
  3272. {
  3273. whereClauses.Add("StartDate>=@MinStartDate");
  3274. if (statement != null)
  3275. {
  3276. statement.TryBind("@MinStartDate", query.MinStartDate.Value);
  3277. }
  3278. }
  3279. if (query.MaxStartDate.HasValue)
  3280. {
  3281. whereClauses.Add("StartDate<=@MaxStartDate");
  3282. if (statement != null)
  3283. {
  3284. statement.TryBind("@MaxStartDate", query.MaxStartDate.Value);
  3285. }
  3286. }
  3287. if (query.MinPremiereDate.HasValue)
  3288. {
  3289. whereClauses.Add("PremiereDate>=@MinPremiereDate");
  3290. if (statement != null)
  3291. {
  3292. statement.TryBind("@MinPremiereDate", query.MinPremiereDate.Value);
  3293. }
  3294. }
  3295. if (query.MaxPremiereDate.HasValue)
  3296. {
  3297. whereClauses.Add("PremiereDate<=@MaxPremiereDate");
  3298. if (statement != null)
  3299. {
  3300. statement.TryBind("@MaxPremiereDate", query.MaxPremiereDate.Value);
  3301. }
  3302. }
  3303. if (query.TrailerTypes.Length > 0)
  3304. {
  3305. var clauses = new List<string>();
  3306. var index = 0;
  3307. foreach (var type in query.TrailerTypes)
  3308. {
  3309. var paramName = "@TrailerTypes" + index;
  3310. clauses.Add("TrailerTypes like " + paramName);
  3311. if (statement != null)
  3312. {
  3313. statement.TryBind(paramName, "%" + type + "%");
  3314. }
  3315. index++;
  3316. }
  3317. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  3318. whereClauses.Add(clause);
  3319. }
  3320. if (query.IsAiring.HasValue)
  3321. {
  3322. if (query.IsAiring.Value)
  3323. {
  3324. whereClauses.Add("StartDate<=@MaxStartDate");
  3325. if (statement != null)
  3326. {
  3327. statement.TryBind("@MaxStartDate", DateTime.UtcNow);
  3328. }
  3329. whereClauses.Add("EndDate>=@MinEndDate");
  3330. if (statement != null)
  3331. {
  3332. statement.TryBind("@MinEndDate", DateTime.UtcNow);
  3333. }
  3334. }
  3335. else
  3336. {
  3337. whereClauses.Add("(StartDate>@IsAiringDate OR EndDate < @IsAiringDate)");
  3338. if (statement != null)
  3339. {
  3340. statement.TryBind("@IsAiringDate", DateTime.UtcNow);
  3341. }
  3342. }
  3343. }
  3344. if (query.PersonIds.Length > 0)
  3345. {
  3346. // TODO: Should this query with CleanName ?
  3347. var clauses = new List<string>();
  3348. var index = 0;
  3349. foreach (var personId in query.PersonIds)
  3350. {
  3351. var paramName = "@PersonId" + index;
  3352. clauses.Add("(select Name from TypedBaseItems where guid=" + paramName + ") in (select Name from People where ItemId=Guid)");
  3353. if (statement != null)
  3354. {
  3355. statement.TryBind(paramName, personId.ToGuidBlob());
  3356. }
  3357. index++;
  3358. }
  3359. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  3360. whereClauses.Add(clause);
  3361. }
  3362. if (!string.IsNullOrWhiteSpace(query.Person))
  3363. {
  3364. whereClauses.Add("Guid in (select ItemId from People where Name=@PersonName)");
  3365. if (statement != null)
  3366. {
  3367. statement.TryBind("@PersonName", query.Person);
  3368. }
  3369. }
  3370. if (!string.IsNullOrWhiteSpace(query.MinSortName))
  3371. {
  3372. whereClauses.Add("SortName>=@MinSortName");
  3373. if (statement != null)
  3374. {
  3375. statement.TryBind("@MinSortName", query.MinSortName);
  3376. }
  3377. }
  3378. if (!string.IsNullOrWhiteSpace(query.ExternalSeriesId))
  3379. {
  3380. whereClauses.Add("ExternalSeriesId=@ExternalSeriesId");
  3381. if (statement != null)
  3382. {
  3383. statement.TryBind("@ExternalSeriesId", query.ExternalSeriesId);
  3384. }
  3385. }
  3386. if (!string.IsNullOrWhiteSpace(query.ExternalId))
  3387. {
  3388. whereClauses.Add("ExternalId=@ExternalId");
  3389. if (statement != null)
  3390. {
  3391. statement.TryBind("@ExternalId", query.ExternalId);
  3392. }
  3393. }
  3394. if (!string.IsNullOrWhiteSpace(query.Name))
  3395. {
  3396. whereClauses.Add("CleanName=@Name");
  3397. if (statement != null)
  3398. {
  3399. statement.TryBind("@Name", GetCleanValue(query.Name));
  3400. }
  3401. }
  3402. if (!string.IsNullOrWhiteSpace(query.NameContains))
  3403. {
  3404. whereClauses.Add("CleanName like @NameContains");
  3405. if (statement != null)
  3406. {
  3407. statement.TryBind("@NameContains", "%" + GetCleanValue(query.NameContains) + "%");
  3408. }
  3409. }
  3410. if (!string.IsNullOrWhiteSpace(query.NameStartsWith))
  3411. {
  3412. whereClauses.Add("SortName like @NameStartsWith");
  3413. if (statement != null)
  3414. {
  3415. statement.TryBind("@NameStartsWith", query.NameStartsWith + "%");
  3416. }
  3417. }
  3418. if (!string.IsNullOrWhiteSpace(query.NameStartsWithOrGreater))
  3419. {
  3420. whereClauses.Add("SortName >= @NameStartsWithOrGreater");
  3421. // lowercase this because SortName is stored as lowercase
  3422. if (statement != null)
  3423. {
  3424. statement.TryBind("@NameStartsWithOrGreater", query.NameStartsWithOrGreater.ToLower());
  3425. }
  3426. }
  3427. if (!string.IsNullOrWhiteSpace(query.NameLessThan))
  3428. {
  3429. whereClauses.Add("SortName < @NameLessThan");
  3430. // lowercase this because SortName is stored as lowercase
  3431. if (statement != null)
  3432. {
  3433. statement.TryBind("@NameLessThan", query.NameLessThan.ToLower());
  3434. }
  3435. }
  3436. if (query.ImageTypes.Length > 0)
  3437. {
  3438. foreach (var requiredImage in query.ImageTypes)
  3439. {
  3440. whereClauses.Add("Images like '%" + requiredImage + "%'");
  3441. }
  3442. }
  3443. if (query.IsLiked.HasValue)
  3444. {
  3445. if (query.IsLiked.Value)
  3446. {
  3447. whereClauses.Add("rating>=@UserRating");
  3448. if (statement != null)
  3449. {
  3450. statement.TryBind("@UserRating", UserItemData.MinLikeValue);
  3451. }
  3452. }
  3453. else
  3454. {
  3455. whereClauses.Add("(rating is null or rating<@UserRating)");
  3456. if (statement != null)
  3457. {
  3458. statement.TryBind("@UserRating", UserItemData.MinLikeValue);
  3459. }
  3460. }
  3461. }
  3462. if (query.IsFavoriteOrLiked.HasValue)
  3463. {
  3464. if (query.IsFavoriteOrLiked.Value)
  3465. {
  3466. whereClauses.Add("IsFavorite=@IsFavoriteOrLiked");
  3467. }
  3468. else
  3469. {
  3470. whereClauses.Add("(IsFavorite is null or IsFavorite=@IsFavoriteOrLiked)");
  3471. }
  3472. if (statement != null)
  3473. {
  3474. statement.TryBind("@IsFavoriteOrLiked", query.IsFavoriteOrLiked.Value);
  3475. }
  3476. }
  3477. if (query.IsFavorite.HasValue)
  3478. {
  3479. if (query.IsFavorite.Value)
  3480. {
  3481. whereClauses.Add("IsFavorite=@IsFavorite");
  3482. }
  3483. else
  3484. {
  3485. whereClauses.Add("(IsFavorite is null or IsFavorite=@IsFavorite)");
  3486. }
  3487. if (statement != null)
  3488. {
  3489. statement.TryBind("@IsFavorite", query.IsFavorite.Value);
  3490. }
  3491. }
  3492. if (EnableJoinUserData(query))
  3493. {
  3494. if (query.IsPlayed.HasValue)
  3495. {
  3496. if (query.IsPlayed.Value)
  3497. {
  3498. whereClauses.Add("(played=@IsPlayed)");
  3499. }
  3500. else
  3501. {
  3502. whereClauses.Add("(played is null or played=@IsPlayed)");
  3503. }
  3504. if (statement != null)
  3505. {
  3506. statement.TryBind("@IsPlayed", query.IsPlayed.Value);
  3507. }
  3508. }
  3509. }
  3510. if (query.IsResumable.HasValue)
  3511. {
  3512. if (query.IsResumable.Value)
  3513. {
  3514. whereClauses.Add("playbackPositionTicks > 0");
  3515. }
  3516. else
  3517. {
  3518. whereClauses.Add("(playbackPositionTicks is null or playbackPositionTicks = 0)");
  3519. }
  3520. }
  3521. if (query.ArtistIds.Length > 0)
  3522. {
  3523. var clauses = new List<string>();
  3524. var index = 0;
  3525. foreach (var artistId in query.ArtistIds)
  3526. {
  3527. var paramName = "@ArtistIds" + index;
  3528. clauses.Add("(select CleanName from TypedBaseItems where guid=" + paramName + ") in (select CleanValue from itemvalues where ItemId=Guid and Type<=1)");
  3529. if (statement != null)
  3530. {
  3531. statement.TryBind(paramName, artistId.ToGuidBlob());
  3532. }
  3533. index++;
  3534. }
  3535. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  3536. whereClauses.Add(clause);
  3537. }
  3538. if (query.AlbumIds.Length > 0)
  3539. {
  3540. var clauses = new List<string>();
  3541. var index = 0;
  3542. foreach (var albumId in query.AlbumIds)
  3543. {
  3544. var paramName = "@AlbumIds" + index;
  3545. clauses.Add("Album in (select Name from typedbaseitems where guid=" + paramName + ")");
  3546. if (statement != null)
  3547. {
  3548. statement.TryBind(paramName, albumId.ToGuidBlob());
  3549. }
  3550. index++;
  3551. }
  3552. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  3553. whereClauses.Add(clause);
  3554. }
  3555. if (query.ExcludeArtistIds.Length > 0)
  3556. {
  3557. var clauses = new List<string>();
  3558. var index = 0;
  3559. foreach (var artistId in query.ExcludeArtistIds)
  3560. {
  3561. var paramName = "@ExcludeArtistId" + index;
  3562. clauses.Add("(select CleanName from TypedBaseItems where guid=" + paramName + ") not in (select CleanValue from itemvalues where ItemId=Guid and Type<=1)");
  3563. if (statement != null)
  3564. {
  3565. statement.TryBind(paramName, artistId.ToGuidBlob());
  3566. }
  3567. index++;
  3568. }
  3569. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  3570. whereClauses.Add(clause);
  3571. }
  3572. if (query.GenreIds.Length > 0)
  3573. {
  3574. var clauses = new List<string>();
  3575. var index = 0;
  3576. foreach (var genreId in query.GenreIds)
  3577. {
  3578. var paramName = "@GenreId" + index;
  3579. clauses.Add("(select CleanName from TypedBaseItems where guid=" + paramName + ") in (select CleanValue from itemvalues where ItemId=Guid and Type=2)");
  3580. if (statement != null)
  3581. {
  3582. statement.TryBind(paramName, genreId.ToGuidBlob());
  3583. }
  3584. index++;
  3585. }
  3586. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  3587. whereClauses.Add(clause);
  3588. }
  3589. if (query.Genres.Length > 0)
  3590. {
  3591. var clauses = new List<string>();
  3592. var index = 0;
  3593. foreach (var item in query.Genres)
  3594. {
  3595. clauses.Add("@Genre" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=2)");
  3596. if (statement != null)
  3597. {
  3598. statement.TryBind("@Genre" + index, GetCleanValue(item));
  3599. }
  3600. index++;
  3601. }
  3602. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  3603. whereClauses.Add(clause);
  3604. }
  3605. if (query.Tags.Length > 0)
  3606. {
  3607. var clauses = new List<string>();
  3608. var index = 0;
  3609. foreach (var item in query.Tags)
  3610. {
  3611. clauses.Add("@Tag" + index + " in (select CleanValue from itemvalues where ItemId=Guid and Type=4)");
  3612. if (statement != null)
  3613. {
  3614. statement.TryBind("@Tag" + index, GetCleanValue(item));
  3615. }
  3616. index++;
  3617. }
  3618. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  3619. whereClauses.Add(clause);
  3620. }
  3621. if (query.StudioIds.Length > 0)
  3622. {
  3623. var clauses = new List<string>();
  3624. var index = 0;
  3625. foreach (var studioId in query.StudioIds)
  3626. {
  3627. var paramName = "@StudioId" + index;
  3628. clauses.Add("(select CleanName from TypedBaseItems where guid=" + paramName + ") in (select CleanValue from itemvalues where ItemId=Guid and Type=3)");
  3629. if (statement != null)
  3630. {
  3631. statement.TryBind(paramName, studioId.ToGuidBlob());
  3632. }
  3633. index++;
  3634. }
  3635. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  3636. whereClauses.Add(clause);
  3637. }
  3638. if (query.OfficialRatings.Length > 0)
  3639. {
  3640. var clauses = new List<string>();
  3641. var index = 0;
  3642. foreach (var item in query.OfficialRatings)
  3643. {
  3644. clauses.Add("OfficialRating=@OfficialRating" + index);
  3645. if (statement != null)
  3646. {
  3647. statement.TryBind("@OfficialRating" + index, item);
  3648. }
  3649. index++;
  3650. }
  3651. var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")";
  3652. whereClauses.Add(clause);
  3653. }
  3654. if (query.MinParentalRating.HasValue)
  3655. {
  3656. whereClauses.Add("InheritedParentalRatingValue<=@MinParentalRating");
  3657. if (statement != null)
  3658. {
  3659. statement.TryBind("@MinParentalRating", query.MinParentalRating.Value);
  3660. }
  3661. }
  3662. if (query.MaxParentalRating.HasValue)
  3663. {
  3664. whereClauses.Add("InheritedParentalRatingValue<=@MaxParentalRating");
  3665. if (statement != null)
  3666. {
  3667. statement.TryBind("@MaxParentalRating", query.MaxParentalRating.Value);
  3668. }
  3669. }
  3670. if (query.HasParentalRating.HasValue)
  3671. {
  3672. if (query.HasParentalRating.Value)
  3673. {
  3674. whereClauses.Add("InheritedParentalRatingValue > 0");
  3675. }
  3676. else
  3677. {
  3678. whereClauses.Add("InheritedParentalRatingValue = 0");
  3679. }
  3680. }
  3681. if (query.HasOverview.HasValue)
  3682. {
  3683. if (query.HasOverview.Value)
  3684. {
  3685. whereClauses.Add("(Overview not null AND Overview<>'')");
  3686. }
  3687. else
  3688. {
  3689. whereClauses.Add("(Overview is null OR Overview='')");
  3690. }
  3691. }
  3692. if (!string.IsNullOrWhiteSpace(query.HasNoAudioTrackWithLanguage))
  3693. {
  3694. whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Audio' and MediaStreams.Language=@HasNoAudioTrackWithLanguage limit 1) is null)");
  3695. if (statement != null)
  3696. {
  3697. statement.TryBind("@HasNoAudioTrackWithLanguage", query.HasNoAudioTrackWithLanguage);
  3698. }
  3699. }
  3700. if (!string.IsNullOrWhiteSpace(query.HasNoInternalSubtitleTrackWithLanguage))
  3701. {
  3702. whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' and MediaStreams.IsExternal=0 and MediaStreams.Language=@HasNoInternalSubtitleTrackWithLanguage limit 1) is null)");
  3703. if (statement != null)
  3704. {
  3705. statement.TryBind("@HasNoInternalSubtitleTrackWithLanguage", query.HasNoInternalSubtitleTrackWithLanguage);
  3706. }
  3707. }
  3708. if (!string.IsNullOrWhiteSpace(query.HasNoExternalSubtitleTrackWithLanguage))
  3709. {
  3710. whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' and MediaStreams.IsExternal=1 and MediaStreams.Language=@HasNoExternalSubtitleTrackWithLanguage limit 1) is null)");
  3711. if (statement != null)
  3712. {
  3713. statement.TryBind("@HasNoExternalSubtitleTrackWithLanguage", query.HasNoExternalSubtitleTrackWithLanguage);
  3714. }
  3715. }
  3716. if (!string.IsNullOrWhiteSpace(query.HasNoSubtitleTrackWithLanguage))
  3717. {
  3718. whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' and MediaStreams.Language=@HasNoSubtitleTrackWithLanguage limit 1) is null)");
  3719. if (statement != null)
  3720. {
  3721. statement.TryBind("@HasNoSubtitleTrackWithLanguage", query.HasNoSubtitleTrackWithLanguage);
  3722. }
  3723. }
  3724. if (query.HasChapterImages.HasValue)
  3725. {
  3726. if (query.HasChapterImages.Value)
  3727. {
  3728. whereClauses.Add("((select imagepath from Chapters2 where Chapters2.ItemId=A.Guid and imagepath not null limit 1) not null)");
  3729. }
  3730. else
  3731. {
  3732. whereClauses.Add("((select imagepath from Chapters2 where Chapters2.ItemId=A.Guid and imagepath not null limit 1) is null)");
  3733. }
  3734. }
  3735. if (query.HasDeadParentId.HasValue && query.HasDeadParentId.Value)
  3736. {
  3737. whereClauses.Add("ParentId NOT NULL AND ParentId NOT IN (select guid from TypedBaseItems)");
  3738. }
  3739. if (query.Years.Length == 1)
  3740. {
  3741. whereClauses.Add("ProductionYear=@Years");
  3742. if (statement != null)
  3743. {
  3744. statement.TryBind("@Years", query.Years[0].ToString());
  3745. }
  3746. }
  3747. else if (query.Years.Length > 1)
  3748. {
  3749. var val = string.Join(",", query.Years.ToArray());
  3750. whereClauses.Add("ProductionYear in (" + val + ")");
  3751. }
  3752. var isVirtualItem = query.IsVirtualItem ?? query.IsMissing;
  3753. if (isVirtualItem.HasValue)
  3754. {
  3755. whereClauses.Add("IsVirtualItem=@IsVirtualItem");
  3756. if (statement != null)
  3757. {
  3758. statement.TryBind("@IsVirtualItem", isVirtualItem.Value);
  3759. }
  3760. }
  3761. if (query.IsSpecialSeason.HasValue)
  3762. {
  3763. if (query.IsSpecialSeason.Value)
  3764. {
  3765. whereClauses.Add("IndexNumber = 0");
  3766. }
  3767. else
  3768. {
  3769. whereClauses.Add("IndexNumber <> 0");
  3770. }
  3771. }
  3772. if (query.IsUnaired.HasValue)
  3773. {
  3774. if (query.IsUnaired.Value)
  3775. {
  3776. whereClauses.Add("PremiereDate >= DATETIME('now')");
  3777. }
  3778. else
  3779. {
  3780. whereClauses.Add("PremiereDate < DATETIME('now')");
  3781. }
  3782. }
  3783. var queryMediaTypes = query.MediaTypes.Where(IsValidMediaType).ToArray();
  3784. if (queryMediaTypes.Length == 1)
  3785. {
  3786. whereClauses.Add("MediaType=@MediaTypes");
  3787. if (statement != null)
  3788. {
  3789. statement.TryBind("@MediaTypes", queryMediaTypes[0]);
  3790. }
  3791. }
  3792. else if (queryMediaTypes.Length > 1)
  3793. {
  3794. var val = string.Join(",", queryMediaTypes.Select(i => "'" + i + "'").ToArray());
  3795. whereClauses.Add("MediaType in (" + val + ")");
  3796. }
  3797. if (query.ItemIds.Length > 0)
  3798. {
  3799. var includeIds = new List<string>();
  3800. var index = 0;
  3801. foreach (var id in query.ItemIds)
  3802. {
  3803. includeIds.Add("Guid = @IncludeId" + index);
  3804. if (statement != null)
  3805. {
  3806. statement.TryBind("@IncludeId" + index, new Guid(id));
  3807. }
  3808. index++;
  3809. }
  3810. whereClauses.Add("(" + string.Join(" OR ", includeIds.ToArray()) + ")");
  3811. }
  3812. if (query.ExcludeItemIds.Length > 0)
  3813. {
  3814. var excludeIds = new List<string>();
  3815. var index = 0;
  3816. foreach (var id in query.ExcludeItemIds)
  3817. {
  3818. excludeIds.Add("Guid <> @ExcludeId" + index);
  3819. if (statement != null)
  3820. {
  3821. statement.TryBind("@ExcludeId" + index, new Guid(id));
  3822. }
  3823. index++;
  3824. }
  3825. whereClauses.Add(string.Join(" AND ", excludeIds.ToArray()));
  3826. }
  3827. if (query.ExcludeProviderIds.Count > 0)
  3828. {
  3829. var excludeIds = new List<string>();
  3830. var index = 0;
  3831. foreach (var pair in query.ExcludeProviderIds)
  3832. {
  3833. if (string.Equals(pair.Key, MetadataProviders.TmdbCollection.ToString(), StringComparison.OrdinalIgnoreCase))
  3834. {
  3835. continue;
  3836. }
  3837. var paramName = "@ExcludeProviderId" + index;
  3838. //excludeIds.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = '" + pair.Key + "'), '') <> " + paramName + ")");
  3839. excludeIds.Add("(ProviderIds is null or ProviderIds not like " + paramName + ")");
  3840. if (statement != null)
  3841. {
  3842. statement.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%");
  3843. }
  3844. index++;
  3845. break;
  3846. }
  3847. whereClauses.Add(string.Join(" AND ", excludeIds.ToArray()));
  3848. }
  3849. if (query.HasImdbId.HasValue)
  3850. {
  3851. whereClauses.Add("ProviderIds like '%imdb=%'");
  3852. }
  3853. if (query.HasTmdbId.HasValue)
  3854. {
  3855. whereClauses.Add("ProviderIds like '%tmdb=%'");
  3856. }
  3857. if (query.HasTvdbId.HasValue)
  3858. {
  3859. whereClauses.Add("ProviderIds like '%tvdb=%'");
  3860. }
  3861. if (query.HasThemeSong.HasValue)
  3862. {
  3863. if (query.HasThemeSong.Value)
  3864. {
  3865. whereClauses.Add("ThemeSongIds not null");
  3866. }
  3867. else
  3868. {
  3869. whereClauses.Add("ThemeSongIds is null");
  3870. }
  3871. }
  3872. if (query.HasThemeVideo.HasValue)
  3873. {
  3874. if (query.HasThemeVideo.Value)
  3875. {
  3876. whereClauses.Add("ThemeVideoIds not null");
  3877. }
  3878. else
  3879. {
  3880. whereClauses.Add("ThemeVideoIds is null");
  3881. }
  3882. }
  3883. var includedItemByNameTypes = GetItemByNameTypesInQuery(query).SelectMany(MapIncludeItemTypes).ToList();
  3884. var enableItemsByName = (query.IncludeItemsByName ?? false) && includedItemByNameTypes.Count > 0;
  3885. var queryTopParentIds = query.TopParentIds.Where(IsValidId).ToArray();
  3886. if (queryTopParentIds.Length == 1)
  3887. {
  3888. if (enableItemsByName && includedItemByNameTypes.Count == 1)
  3889. {
  3890. whereClauses.Add("(TopParentId=@TopParentId or Type=@IncludedItemByNameType)");
  3891. if (statement != null)
  3892. {
  3893. statement.TryBind("@IncludedItemByNameType", includedItemByNameTypes[0]);
  3894. }
  3895. }
  3896. else if (enableItemsByName && includedItemByNameTypes.Count > 1)
  3897. {
  3898. var itemByNameTypeVal = string.Join(",", includedItemByNameTypes.Select(i => "'" + i + "'").ToArray());
  3899. whereClauses.Add("(TopParentId=@TopParentId or Type in (" + itemByNameTypeVal + "))");
  3900. }
  3901. else
  3902. {
  3903. whereClauses.Add("(TopParentId=@TopParentId)");
  3904. }
  3905. if (statement != null)
  3906. {
  3907. statement.TryBind("@TopParentId", queryTopParentIds[0]);
  3908. }
  3909. }
  3910. else if (queryTopParentIds.Length > 1)
  3911. {
  3912. var val = string.Join(",", queryTopParentIds.Select(i => "'" + i + "'").ToArray());
  3913. if (enableItemsByName && includedItemByNameTypes.Count == 1)
  3914. {
  3915. whereClauses.Add("(Type=@IncludedItemByNameType or TopParentId in (" + val + "))");
  3916. if (statement != null)
  3917. {
  3918. statement.TryBind("@IncludedItemByNameType", includedItemByNameTypes[0]);
  3919. }
  3920. }
  3921. else if (enableItemsByName && includedItemByNameTypes.Count > 1)
  3922. {
  3923. var itemByNameTypeVal = string.Join(",", includedItemByNameTypes.Select(i => "'" + i + "'").ToArray());
  3924. whereClauses.Add("(Type in (" + itemByNameTypeVal + ") or TopParentId in (" + val + "))");
  3925. }
  3926. else
  3927. {
  3928. whereClauses.Add("(TopParentId in (" + val + "))");
  3929. }
  3930. }
  3931. if (query.AncestorIds.Length == 1)
  3932. {
  3933. whereClauses.Add("Guid in (select itemId from AncestorIds where AncestorId=@AncestorId)");
  3934. if (statement != null)
  3935. {
  3936. statement.TryBind("@AncestorId", new Guid(query.AncestorIds[0]));
  3937. }
  3938. }
  3939. if (query.AncestorIds.Length > 1)
  3940. {
  3941. var inClause = string.Join(",", query.AncestorIds.Select(i => "'" + new Guid(i).ToString("N") + "'").ToArray());
  3942. whereClauses.Add(string.Format("Guid in (select itemId from AncestorIds where AncestorIdText in ({0}))", inClause));
  3943. }
  3944. if (!string.IsNullOrWhiteSpace(query.AncestorWithPresentationUniqueKey))
  3945. {
  3946. var inClause = "select guid from TypedBaseItems where PresentationUniqueKey=@AncestorWithPresentationUniqueKey";
  3947. whereClauses.Add(string.Format("Guid in (select itemId from AncestorIds where AncestorId in ({0}))", inClause));
  3948. if (statement != null)
  3949. {
  3950. statement.TryBind("@AncestorWithPresentationUniqueKey", query.AncestorWithPresentationUniqueKey);
  3951. }
  3952. }
  3953. if (!string.IsNullOrWhiteSpace(query.SeriesPresentationUniqueKey))
  3954. {
  3955. whereClauses.Add("SeriesPresentationUniqueKey=@SeriesPresentationUniqueKey");
  3956. if (statement != null)
  3957. {
  3958. statement.TryBind("@SeriesPresentationUniqueKey", query.SeriesPresentationUniqueKey);
  3959. }
  3960. }
  3961. if (query.BlockUnratedItems.Length == 1)
  3962. {
  3963. whereClauses.Add("(InheritedParentalRatingValue > 0 or UnratedType <> @UnratedType)");
  3964. if (statement != null)
  3965. {
  3966. statement.TryBind("@UnratedType", query.BlockUnratedItems[0].ToString());
  3967. }
  3968. }
  3969. if (query.BlockUnratedItems.Length > 1)
  3970. {
  3971. var inClause = string.Join(",", query.BlockUnratedItems.Select(i => "'" + i.ToString() + "'").ToArray());
  3972. whereClauses.Add(string.Format("(InheritedParentalRatingValue > 0 or UnratedType not in ({0}))", inClause));
  3973. }
  3974. if (query.ExcludeInheritedTags.Length > 0)
  3975. {
  3976. var tagValues = query.ExcludeInheritedTags.Select(i => "'" + GetCleanValue(i) + "'").ToArray();
  3977. var tagValuesList = string.Join(",", tagValues);
  3978. whereClauses.Add("((select CleanValue from itemvalues where ItemId=Guid and Type=6 and cleanvalue in (" + tagValuesList + ")) is null)");
  3979. }
  3980. return whereClauses;
  3981. }
  3982. private List<string> GetItemByNameTypesInQuery(InternalItemsQuery query)
  3983. {
  3984. var list = new List<string>();
  3985. if (IsTypeInQuery(typeof(Person).Name, query))
  3986. {
  3987. list.Add(typeof(Person).Name);
  3988. }
  3989. if (IsTypeInQuery(typeof(Genre).Name, query))
  3990. {
  3991. list.Add(typeof(Genre).Name);
  3992. }
  3993. if (IsTypeInQuery(typeof(MusicGenre).Name, query))
  3994. {
  3995. list.Add(typeof(MusicGenre).Name);
  3996. }
  3997. if (IsTypeInQuery(typeof(GameGenre).Name, query))
  3998. {
  3999. list.Add(typeof(GameGenre).Name);
  4000. }
  4001. if (IsTypeInQuery(typeof(MusicArtist).Name, query))
  4002. {
  4003. list.Add(typeof(MusicArtist).Name);
  4004. }
  4005. if (IsTypeInQuery(typeof(Studio).Name, query))
  4006. {
  4007. list.Add(typeof(Studio).Name);
  4008. }
  4009. return list;
  4010. }
  4011. private bool IsTypeInQuery(string type, InternalItemsQuery query)
  4012. {
  4013. if (query.ExcludeItemTypes.Contains(type, StringComparer.OrdinalIgnoreCase))
  4014. {
  4015. return false;
  4016. }
  4017. return query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(type, StringComparer.OrdinalIgnoreCase);
  4018. }
  4019. private string GetCleanValue(string value)
  4020. {
  4021. if (string.IsNullOrWhiteSpace(value))
  4022. {
  4023. return value;
  4024. }
  4025. return value.RemoveDiacritics().ToLower();
  4026. }
  4027. private bool EnableGroupByPresentationUniqueKey(InternalItemsQuery query)
  4028. {
  4029. if (!query.GroupByPresentationUniqueKey)
  4030. {
  4031. return false;
  4032. }
  4033. if (query.GroupBySeriesPresentationUniqueKey)
  4034. {
  4035. return false;
  4036. }
  4037. if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey))
  4038. {
  4039. return false;
  4040. }
  4041. if (query.User == null)
  4042. {
  4043. return false;
  4044. }
  4045. if (query.IncludeItemTypes.Length == 0)
  4046. {
  4047. return true;
  4048. }
  4049. var types = new[] {
  4050. typeof(Episode).Name,
  4051. typeof(Video).Name ,
  4052. typeof(Movie).Name ,
  4053. typeof(MusicVideo).Name ,
  4054. typeof(Series).Name ,
  4055. typeof(Season).Name };
  4056. if (types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase)))
  4057. {
  4058. return true;
  4059. }
  4060. return false;
  4061. }
  4062. private static readonly Type[] KnownTypes =
  4063. {
  4064. typeof(LiveTvProgram),
  4065. typeof(LiveTvChannel),
  4066. typeof(LiveTvVideoRecording),
  4067. typeof(LiveTvAudioRecording),
  4068. typeof(Series),
  4069. typeof(Audio),
  4070. typeof(MusicAlbum),
  4071. typeof(MusicArtist),
  4072. typeof(MusicGenre),
  4073. typeof(MusicVideo),
  4074. typeof(Movie),
  4075. typeof(Playlist),
  4076. typeof(AudioPodcast),
  4077. typeof(AudioBook),
  4078. typeof(Trailer),
  4079. typeof(BoxSet),
  4080. typeof(Episode),
  4081. typeof(Season),
  4082. typeof(Series),
  4083. typeof(Book),
  4084. typeof(CollectionFolder),
  4085. typeof(Folder),
  4086. typeof(Game),
  4087. typeof(GameGenre),
  4088. typeof(GameSystem),
  4089. typeof(Genre),
  4090. typeof(Person),
  4091. typeof(Photo),
  4092. typeof(PhotoAlbum),
  4093. typeof(Studio),
  4094. typeof(UserRootFolder),
  4095. typeof(UserView),
  4096. typeof(Video),
  4097. typeof(Year),
  4098. typeof(Channel),
  4099. typeof(AggregateFolder)
  4100. };
  4101. public void UpdateInheritedValues(CancellationToken cancellationToken)
  4102. {
  4103. UpdateInheritedTags(cancellationToken);
  4104. }
  4105. private void UpdateInheritedTags(CancellationToken cancellationToken)
  4106. {
  4107. using (WriteLock.Write())
  4108. {
  4109. using (var connection = CreateConnection())
  4110. {
  4111. connection.RunInTransaction(db =>
  4112. {
  4113. connection.ExecuteAll(string.Join(";", new string[]
  4114. {
  4115. "delete from itemvalues where type = 6",
  4116. "insert into itemvalues (ItemId, Type, Value, CleanValue) select ItemId, 6, Value, CleanValue from ItemValues where Type=4",
  4117. @"insert into itemvalues (ItemId, Type, Value, CleanValue) select AncestorIds.itemid, 6, ItemValues.Value, ItemValues.CleanValue
  4118. FROM AncestorIds
  4119. LEFT JOIN ItemValues ON (AncestorIds.AncestorId = ItemValues.ItemId)
  4120. where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type = 4 "
  4121. }));
  4122. }, TransactionMode);
  4123. }
  4124. }
  4125. }
  4126. private static Dictionary<string, string[]> GetTypeMapDictionary()
  4127. {
  4128. var dict = new Dictionary<string, string[]>(StringComparer.OrdinalIgnoreCase);
  4129. foreach (var t in KnownTypes)
  4130. {
  4131. dict[t.Name] = new[] { t.FullName };
  4132. }
  4133. dict["Recording"] = new[] { typeof(LiveTvAudioRecording).FullName, typeof(LiveTvVideoRecording).FullName };
  4134. dict["Program"] = new[] { typeof(LiveTvProgram).FullName };
  4135. dict["TvChannel"] = new[] { typeof(LiveTvChannel).FullName };
  4136. return dict;
  4137. }
  4138. // Not crazy about having this all the way down here, but at least it's in one place
  4139. readonly Dictionary<string, string[]> _types = GetTypeMapDictionary();
  4140. private IEnumerable<string> MapIncludeItemTypes(string value)
  4141. {
  4142. string[] result;
  4143. if (_types.TryGetValue(value, out result))
  4144. {
  4145. return result;
  4146. }
  4147. return new[] { value }.Where(IsValidType);
  4148. }
  4149. public void DeleteItem(Guid id, CancellationToken cancellationToken)
  4150. {
  4151. if (id == Guid.Empty)
  4152. {
  4153. throw new ArgumentNullException("id");
  4154. }
  4155. CheckDisposed();
  4156. using (WriteLock.Write())
  4157. {
  4158. using (var connection = CreateConnection())
  4159. {
  4160. connection.RunInTransaction(db =>
  4161. {
  4162. // Delete people
  4163. ExecuteWithSingleParam(db, "delete from People where ItemId=@Id", id.ToGuidBlob());
  4164. // Delete chapters
  4165. ExecuteWithSingleParam(db, "delete from " + ChaptersTableName + " where ItemId=@Id", id.ToGuidBlob());
  4166. // Delete media streams
  4167. ExecuteWithSingleParam(db, "delete from mediastreams where ItemId=@Id", id.ToGuidBlob());
  4168. // Delete ancestors
  4169. ExecuteWithSingleParam(db, "delete from AncestorIds where ItemId=@Id", id.ToGuidBlob());
  4170. // Delete item values
  4171. ExecuteWithSingleParam(db, "delete from ItemValues where ItemId=@Id", id.ToGuidBlob());
  4172. // Delete the item
  4173. ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", id.ToGuidBlob());
  4174. }, TransactionMode);
  4175. }
  4176. }
  4177. }
  4178. private void ExecuteWithSingleParam(IDatabaseConnection db, string query, byte[] value)
  4179. {
  4180. using (var statement = PrepareStatement(db, query))
  4181. {
  4182. statement.TryBind("@Id", value);
  4183. statement.MoveNext();
  4184. }
  4185. }
  4186. public List<string> GetPeopleNames(InternalPeopleQuery query)
  4187. {
  4188. if (query == null)
  4189. {
  4190. throw new ArgumentNullException("query");
  4191. }
  4192. CheckDisposed();
  4193. var commandText = "select Distinct Name from People";
  4194. var whereClauses = GetPeopleWhereClauses(query, null);
  4195. if (whereClauses.Count > 0)
  4196. {
  4197. commandText += " where " + string.Join(" AND ", whereClauses.ToArray());
  4198. }
  4199. commandText += " order by ListOrder";
  4200. using (WriteLock.Read())
  4201. {
  4202. using (var connection = CreateConnection(true))
  4203. {
  4204. var list = new List<string>();
  4205. using (var statement = PrepareStatementSafe(connection, commandText))
  4206. {
  4207. // Run this again to bind the params
  4208. GetPeopleWhereClauses(query, statement);
  4209. foreach (var row in statement.ExecuteQuery())
  4210. {
  4211. list.Add(row.GetString(0));
  4212. }
  4213. }
  4214. return list;
  4215. }
  4216. }
  4217. }
  4218. public List<PersonInfo> GetPeople(InternalPeopleQuery query)
  4219. {
  4220. if (query == null)
  4221. {
  4222. throw new ArgumentNullException("query");
  4223. }
  4224. CheckDisposed();
  4225. var commandText = "select ItemId, Name, Role, PersonType, SortOrder from People";
  4226. var whereClauses = GetPeopleWhereClauses(query, null);
  4227. if (whereClauses.Count > 0)
  4228. {
  4229. commandText += " where " + string.Join(" AND ", whereClauses.ToArray());
  4230. }
  4231. commandText += " order by ListOrder";
  4232. using (WriteLock.Read())
  4233. {
  4234. using (var connection = CreateConnection(true))
  4235. {
  4236. var list = new List<PersonInfo>();
  4237. using (var statement = PrepareStatementSafe(connection, commandText))
  4238. {
  4239. // Run this again to bind the params
  4240. GetPeopleWhereClauses(query, statement);
  4241. foreach (var row in statement.ExecuteQuery())
  4242. {
  4243. list.Add(GetPerson(row));
  4244. }
  4245. }
  4246. return list;
  4247. }
  4248. }
  4249. }
  4250. private List<string> GetPeopleWhereClauses(InternalPeopleQuery query, IStatement statement)
  4251. {
  4252. var whereClauses = new List<string>();
  4253. if (query.ItemId != Guid.Empty)
  4254. {
  4255. whereClauses.Add("ItemId=@ItemId");
  4256. if (statement != null)
  4257. {
  4258. statement.TryBind("@ItemId", query.ItemId.ToGuidBlob());
  4259. }
  4260. }
  4261. if (query.AppearsInItemId != Guid.Empty)
  4262. {
  4263. whereClauses.Add("Name in (Select Name from People where ItemId=@AppearsInItemId)");
  4264. if (statement != null)
  4265. {
  4266. statement.TryBind("@AppearsInItemId", query.AppearsInItemId.ToGuidBlob());
  4267. }
  4268. }
  4269. var queryPersonTypes = query.PersonTypes.Where(IsValidPersonType).ToList();
  4270. if (queryPersonTypes.Count == 1)
  4271. {
  4272. whereClauses.Add("PersonType=@PersonType");
  4273. if (statement != null)
  4274. {
  4275. statement.TryBind("@PersonType", queryPersonTypes[0]);
  4276. }
  4277. }
  4278. else if (queryPersonTypes.Count > 1)
  4279. {
  4280. var val = string.Join(",", queryPersonTypes.Select(i => "'" + i + "'").ToArray());
  4281. whereClauses.Add("PersonType in (" + val + ")");
  4282. }
  4283. var queryExcludePersonTypes = query.ExcludePersonTypes.Where(IsValidPersonType).ToList();
  4284. if (queryExcludePersonTypes.Count == 1)
  4285. {
  4286. whereClauses.Add("PersonType<>@PersonType");
  4287. if (statement != null)
  4288. {
  4289. statement.TryBind("@PersonType", queryExcludePersonTypes[0]);
  4290. }
  4291. }
  4292. else if (queryExcludePersonTypes.Count > 1)
  4293. {
  4294. var val = string.Join(",", queryExcludePersonTypes.Select(i => "'" + i + "'").ToArray());
  4295. whereClauses.Add("PersonType not in (" + val + ")");
  4296. }
  4297. if (query.MaxListOrder.HasValue)
  4298. {
  4299. whereClauses.Add("ListOrder<=@MaxListOrder");
  4300. if (statement != null)
  4301. {
  4302. statement.TryBind("@MaxListOrder", query.MaxListOrder.Value);
  4303. }
  4304. }
  4305. if (!string.IsNullOrWhiteSpace(query.NameContains))
  4306. {
  4307. whereClauses.Add("Name like @NameContains");
  4308. if (statement != null)
  4309. {
  4310. statement.TryBind("@NameContains", "%" + query.NameContains + "%");
  4311. }
  4312. }
  4313. return whereClauses;
  4314. }
  4315. private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, IDatabaseConnection db, IStatement deleteAncestorsStatement, IStatement updateAncestorsStatement)
  4316. {
  4317. if (itemId == Guid.Empty)
  4318. {
  4319. throw new ArgumentNullException("itemId");
  4320. }
  4321. if (ancestorIds == null)
  4322. {
  4323. throw new ArgumentNullException("ancestorIds");
  4324. }
  4325. CheckDisposed();
  4326. // First delete
  4327. deleteAncestorsStatement.Reset();
  4328. deleteAncestorsStatement.TryBind("@ItemId", itemId.ToGuidBlob());
  4329. deleteAncestorsStatement.MoveNext();
  4330. foreach (var ancestorId in ancestorIds)
  4331. {
  4332. updateAncestorsStatement.Reset();
  4333. updateAncestorsStatement.TryBind("@ItemId", itemId.ToGuidBlob());
  4334. updateAncestorsStatement.TryBind("@AncestorId", ancestorId.ToGuidBlob());
  4335. updateAncestorsStatement.TryBind("@AncestorIdText", ancestorId.ToString("N"));
  4336. updateAncestorsStatement.MoveNext();
  4337. }
  4338. }
  4339. public QueryResult<Tuple<BaseItem, ItemCounts>> GetAllArtists(InternalItemsQuery query)
  4340. {
  4341. return GetItemValues(query, new[] { 0, 1 }, typeof(MusicArtist).FullName);
  4342. }
  4343. public QueryResult<Tuple<BaseItem, ItemCounts>> GetArtists(InternalItemsQuery query)
  4344. {
  4345. return GetItemValues(query, new[] { 0 }, typeof(MusicArtist).FullName);
  4346. }
  4347. public QueryResult<Tuple<BaseItem, ItemCounts>> GetAlbumArtists(InternalItemsQuery query)
  4348. {
  4349. return GetItemValues(query, new[] { 1 }, typeof(MusicArtist).FullName);
  4350. }
  4351. public QueryResult<Tuple<BaseItem, ItemCounts>> GetStudios(InternalItemsQuery query)
  4352. {
  4353. return GetItemValues(query, new[] { 3 }, typeof(Studio).FullName);
  4354. }
  4355. public QueryResult<Tuple<BaseItem, ItemCounts>> GetGenres(InternalItemsQuery query)
  4356. {
  4357. return GetItemValues(query, new[] { 2 }, typeof(Genre).FullName);
  4358. }
  4359. public QueryResult<Tuple<BaseItem, ItemCounts>> GetGameGenres(InternalItemsQuery query)
  4360. {
  4361. return GetItemValues(query, new[] { 2 }, typeof(GameGenre).FullName);
  4362. }
  4363. public QueryResult<Tuple<BaseItem, ItemCounts>> GetMusicGenres(InternalItemsQuery query)
  4364. {
  4365. return GetItemValues(query, new[] { 2 }, typeof(MusicGenre).FullName);
  4366. }
  4367. public List<string> GetStudioNames()
  4368. {
  4369. return GetItemValueNames(new[] { 3 }, new List<string>(), new List<string>());
  4370. }
  4371. public List<string> GetAllArtistNames()
  4372. {
  4373. return GetItemValueNames(new[] { 0, 1 }, new List<string>(), new List<string>());
  4374. }
  4375. public List<string> GetMusicGenreNames()
  4376. {
  4377. return GetItemValueNames(new[] { 2 }, new List<string> { "Audio", "MusicVideo", "MusicAlbum", "MusicArtist" }, new List<string>());
  4378. }
  4379. public List<string> GetGameGenreNames()
  4380. {
  4381. return GetItemValueNames(new[] { 2 }, new List<string> { "Game" }, new List<string>());
  4382. }
  4383. public List<string> GetGenreNames()
  4384. {
  4385. return GetItemValueNames(new[] { 2 }, new List<string>(), new List<string> { "Audio", "MusicVideo", "MusicAlbum", "MusicArtist", "Game", "GameSystem" });
  4386. }
  4387. private List<string> GetItemValueNames(int[] itemValueTypes, List<string> withItemTypes, List<string> excludeItemTypes)
  4388. {
  4389. CheckDisposed();
  4390. withItemTypes = withItemTypes.SelectMany(MapIncludeItemTypes).ToList();
  4391. excludeItemTypes = excludeItemTypes.SelectMany(MapIncludeItemTypes).ToList();
  4392. var now = DateTime.UtcNow;
  4393. var typeClause = itemValueTypes.Length == 1 ?
  4394. ("Type=" + itemValueTypes[0].ToString(CultureInfo.InvariantCulture)) :
  4395. ("Type in (" + string.Join(",", itemValueTypes.Select(i => i.ToString(CultureInfo.InvariantCulture)).ToArray()) + ")");
  4396. var commandText = "Select Value From ItemValues where " + typeClause;
  4397. if (withItemTypes.Count > 0)
  4398. {
  4399. var typeString = string.Join(",", withItemTypes.Select(i => "'" + i + "'").ToArray());
  4400. commandText += " AND ItemId In (select guid from typedbaseitems where type in (" + typeString + "))";
  4401. }
  4402. if (excludeItemTypes.Count > 0)
  4403. {
  4404. var typeString = string.Join(",", excludeItemTypes.Select(i => "'" + i + "'").ToArray());
  4405. commandText += " AND ItemId not In (select guid from typedbaseitems where type in (" + typeString + "))";
  4406. }
  4407. commandText += " Group By CleanValue";
  4408. using (WriteLock.Read())
  4409. {
  4410. using (var connection = CreateConnection(true))
  4411. {
  4412. var list = new List<string>();
  4413. using (var statement = PrepareStatementSafe(connection, commandText))
  4414. {
  4415. foreach (var row in statement.ExecuteQuery())
  4416. {
  4417. if (!row.IsDBNull(0))
  4418. {
  4419. list.Add(row.GetString(0));
  4420. }
  4421. }
  4422. }
  4423. LogQueryTime("GetItemValueNames", commandText, now);
  4424. return list;
  4425. }
  4426. }
  4427. }
  4428. private QueryResult<Tuple<BaseItem, ItemCounts>> GetItemValues(InternalItemsQuery query, int[] itemValueTypes, string returnType)
  4429. {
  4430. if (query == null)
  4431. {
  4432. throw new ArgumentNullException("query");
  4433. }
  4434. if (!query.Limit.HasValue)
  4435. {
  4436. query.EnableTotalRecordCount = false;
  4437. }
  4438. CheckDisposed();
  4439. //Logger.Info("GetItemValues: " + _environmentInfo.StackTrace);
  4440. var now = DateTime.UtcNow;
  4441. var typeClause = itemValueTypes.Length == 1 ?
  4442. ("Type=" + itemValueTypes[0].ToString(CultureInfo.InvariantCulture)) :
  4443. ("Type in (" + string.Join(",", itemValueTypes.Select(i => i.ToString(CultureInfo.InvariantCulture)).ToArray()) + ")");
  4444. InternalItemsQuery typeSubQuery = null;
  4445. var itemCountColumns = new List<Tuple<string, string>>();
  4446. var typesToCount = query.IncludeItemTypes;
  4447. if (typesToCount.Length > 0)
  4448. {
  4449. var itemCountColumnQuery = "select group_concat(type, '|')" + GetFromText("B");
  4450. typeSubQuery = new InternalItemsQuery(query.User)
  4451. {
  4452. ExcludeItemTypes = query.ExcludeItemTypes,
  4453. IncludeItemTypes = query.IncludeItemTypes,
  4454. MediaTypes = query.MediaTypes,
  4455. AncestorIds = query.AncestorIds,
  4456. ExcludeItemIds = query.ExcludeItemIds,
  4457. ItemIds = query.ItemIds,
  4458. TopParentIds = query.TopParentIds,
  4459. ParentId = query.ParentId,
  4460. IsPlayed = query.IsPlayed
  4461. };
  4462. var whereClauses = GetWhereClauses(typeSubQuery, null);
  4463. whereClauses.Add("guid in (select ItemId from ItemValues where ItemValues.CleanValue=A.CleanName AND " + typeClause + ")");
  4464. var typeWhereText = whereClauses.Count == 0 ?
  4465. string.Empty :
  4466. " where " + string.Join(" AND ", whereClauses.ToArray());
  4467. itemCountColumnQuery += typeWhereText;
  4468. //itemCountColumnQuery += ")";
  4469. itemCountColumns.Add(new Tuple<string, string>("itemTypes", "(" + itemCountColumnQuery + ") as itemTypes"));
  4470. }
  4471. var columns = _retriveItemColumns.ToList();
  4472. columns.AddRange(itemCountColumns.Select(i => i.Item2).ToArray());
  4473. columns = GetFinalColumnsToSelect(query, columns.ToArray()).ToList();
  4474. var commandText = "select " + string.Join(",", columns.ToArray()) + GetFromText();
  4475. commandText += GetJoinUserDataText(query);
  4476. var innerQuery = new InternalItemsQuery(query.User)
  4477. {
  4478. ExcludeItemTypes = query.ExcludeItemTypes,
  4479. IncludeItemTypes = query.IncludeItemTypes,
  4480. MediaTypes = query.MediaTypes,
  4481. AncestorIds = query.AncestorIds,
  4482. ExcludeItemIds = query.ExcludeItemIds,
  4483. ItemIds = query.ItemIds,
  4484. TopParentIds = query.TopParentIds,
  4485. ParentId = query.ParentId,
  4486. IsPlayed = query.IsPlayed
  4487. };
  4488. var innerWhereClauses = GetWhereClauses(innerQuery, null);
  4489. var innerWhereText = innerWhereClauses.Count == 0 ?
  4490. string.Empty :
  4491. " where " + string.Join(" AND ", innerWhereClauses.ToArray());
  4492. var whereText = " where Type=@SelectType";
  4493. if (typesToCount.Length == 0)
  4494. {
  4495. whereText += " And CleanName In (Select CleanValue from ItemValues where " + typeClause + " AND ItemId in (select guid from TypedBaseItems" + innerWhereText + "))";
  4496. }
  4497. else
  4498. {
  4499. //whereText += " And itemTypes not null";
  4500. whereText += " And CleanName In (Select CleanValue from ItemValues where " + typeClause + " AND ItemId in (select guid from TypedBaseItems" + innerWhereText + "))";
  4501. }
  4502. var outerQuery = new InternalItemsQuery(query.User)
  4503. {
  4504. IsFavorite = query.IsFavorite,
  4505. IsFavoriteOrLiked = query.IsFavoriteOrLiked,
  4506. IsLiked = query.IsLiked,
  4507. IsLocked = query.IsLocked,
  4508. NameLessThan = query.NameLessThan,
  4509. NameStartsWith = query.NameStartsWith,
  4510. NameStartsWithOrGreater = query.NameStartsWithOrGreater,
  4511. Tags = query.Tags,
  4512. OfficialRatings = query.OfficialRatings,
  4513. GenreIds = query.GenreIds,
  4514. Genres = query.Genres,
  4515. Years = query.Years
  4516. };
  4517. var outerWhereClauses = GetWhereClauses(outerQuery, null);
  4518. whereText += outerWhereClauses.Count == 0 ?
  4519. string.Empty :
  4520. " AND " + string.Join(" AND ", outerWhereClauses.ToArray());
  4521. //cmd.CommandText += GetGroupBy(query);
  4522. commandText += whereText;
  4523. commandText += " group by PresentationUniqueKey";
  4524. commandText += " order by SortName";
  4525. if (query.Limit.HasValue || query.StartIndex.HasValue)
  4526. {
  4527. var offset = query.StartIndex ?? 0;
  4528. if (query.Limit.HasValue || offset > 0)
  4529. {
  4530. commandText += " LIMIT " + (query.Limit ?? int.MaxValue).ToString(CultureInfo.InvariantCulture);
  4531. }
  4532. if (offset > 0)
  4533. {
  4534. commandText += " OFFSET " + offset.ToString(CultureInfo.InvariantCulture);
  4535. }
  4536. }
  4537. var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0;
  4538. var statementTexts = new List<string>();
  4539. if (!isReturningZeroItems)
  4540. {
  4541. statementTexts.Add(commandText);
  4542. }
  4543. if (query.EnableTotalRecordCount)
  4544. {
  4545. var countText = "select count (distinct PresentationUniqueKey)" + GetFromText();
  4546. countText += GetJoinUserDataText(query);
  4547. countText += whereText;
  4548. statementTexts.Add(countText);
  4549. }
  4550. using (WriteLock.Read())
  4551. {
  4552. using (var connection = CreateConnection(true))
  4553. {
  4554. return connection.RunInTransaction(db =>
  4555. {
  4556. var list = new List<Tuple<BaseItem, ItemCounts>>();
  4557. var result = new QueryResult<Tuple<BaseItem, ItemCounts>>();
  4558. var statements = PrepareAllSafe(db, statementTexts);
  4559. if (!isReturningZeroItems)
  4560. {
  4561. using (var statement = statements[0])
  4562. {
  4563. statement.TryBind("@SelectType", returnType);
  4564. if (EnableJoinUserData(query))
  4565. {
  4566. statement.TryBind("@UserId", query.User.Id);
  4567. }
  4568. if (typeSubQuery != null)
  4569. {
  4570. GetWhereClauses(typeSubQuery, null);
  4571. }
  4572. BindSimilarParams(query, statement);
  4573. GetWhereClauses(innerQuery, statement);
  4574. GetWhereClauses(outerQuery, statement);
  4575. var hasEpisodeAttributes = HasEpisodeAttributes(query);
  4576. var hasProgramAttributes = HasProgramAttributes(query);
  4577. var hasStartDate = HasStartDate(query);
  4578. var hasTrailerTypes = HasTrailerTypes(query);
  4579. var hasArtistFields = HasArtistFields(query);
  4580. var hasSeriesFields = HasSeriesFields(query);
  4581. foreach (var row in statement.ExecuteQuery())
  4582. {
  4583. var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields);
  4584. if (item != null)
  4585. {
  4586. var countStartColumn = columns.Count - 1;
  4587. list.Add(new Tuple<BaseItem, ItemCounts>(item, GetItemCounts(row, countStartColumn, typesToCount)));
  4588. }
  4589. }
  4590. LogQueryTime("GetItemValues", commandText, now);
  4591. }
  4592. }
  4593. if (query.EnableTotalRecordCount)
  4594. {
  4595. commandText = "select count (distinct PresentationUniqueKey)" + GetFromText();
  4596. commandText += GetJoinUserDataText(query);
  4597. commandText += whereText;
  4598. using (var statement = statements[statements.Count - 1])
  4599. {
  4600. statement.TryBind("@SelectType", returnType);
  4601. if (EnableJoinUserData(query))
  4602. {
  4603. statement.TryBind("@UserId", query.User.Id);
  4604. }
  4605. if (typeSubQuery != null)
  4606. {
  4607. GetWhereClauses(typeSubQuery, null);
  4608. }
  4609. BindSimilarParams(query, statement);
  4610. GetWhereClauses(innerQuery, statement);
  4611. GetWhereClauses(outerQuery, statement);
  4612. result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First();
  4613. LogQueryTime("GetItemValues", commandText, now);
  4614. }
  4615. }
  4616. if (result.TotalRecordCount == 0)
  4617. {
  4618. result.TotalRecordCount = list.Count;
  4619. }
  4620. result.Items = list.ToArray(list.Count);
  4621. return result;
  4622. }, ReadTransactionMode);
  4623. }
  4624. }
  4625. }
  4626. private ItemCounts GetItemCounts(IReadOnlyList<IResultSetValue> reader, int countStartColumn, string[] typesToCount)
  4627. {
  4628. var counts = new ItemCounts();
  4629. if (typesToCount.Length == 0)
  4630. {
  4631. return counts;
  4632. }
  4633. var typeString = reader.IsDBNull(countStartColumn) ? null : reader.GetString(countStartColumn);
  4634. if (string.IsNullOrWhiteSpace(typeString))
  4635. {
  4636. return counts;
  4637. }
  4638. var allTypes = typeString.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries)
  4639. .ToLookup(i => i).ToList();
  4640. foreach (var type in allTypes)
  4641. {
  4642. var value = type.ToList().Count;
  4643. var typeName = type.Key;
  4644. if (string.Equals(typeName, typeof(Series).FullName, StringComparison.OrdinalIgnoreCase))
  4645. {
  4646. counts.SeriesCount = value;
  4647. }
  4648. else if (string.Equals(typeName, typeof(Episode).FullName, StringComparison.OrdinalIgnoreCase))
  4649. {
  4650. counts.EpisodeCount = value;
  4651. }
  4652. else if (string.Equals(typeName, typeof(Movie).FullName, StringComparison.OrdinalIgnoreCase))
  4653. {
  4654. counts.MovieCount = value;
  4655. }
  4656. else if (string.Equals(typeName, typeof(MusicAlbum).FullName, StringComparison.OrdinalIgnoreCase))
  4657. {
  4658. counts.AlbumCount = value;
  4659. }
  4660. else if (string.Equals(typeName, typeof(MusicArtist).FullName, StringComparison.OrdinalIgnoreCase))
  4661. {
  4662. counts.ArtistCount = value;
  4663. }
  4664. else if (string.Equals(typeName, typeof(Audio).FullName, StringComparison.OrdinalIgnoreCase))
  4665. {
  4666. counts.SongCount = value;
  4667. }
  4668. else if (string.Equals(typeName, typeof(Game).FullName, StringComparison.OrdinalIgnoreCase))
  4669. {
  4670. counts.GameCount = value;
  4671. }
  4672. else if (string.Equals(typeName, typeof(Trailer).FullName, StringComparison.OrdinalIgnoreCase))
  4673. {
  4674. counts.TrailerCount = value;
  4675. }
  4676. counts.ItemCount += value;
  4677. }
  4678. return counts;
  4679. }
  4680. private List<Tuple<int, string>> GetItemValuesToSave(BaseItem item, List<string> inheritedTags)
  4681. {
  4682. var list = new List<Tuple<int, string>>();
  4683. var hasArtist = item as IHasArtist;
  4684. if (hasArtist != null)
  4685. {
  4686. list.AddRange(hasArtist.Artists.Select(i => new Tuple<int, string>(0, i)));
  4687. }
  4688. var hasAlbumArtist = item as IHasAlbumArtist;
  4689. if (hasAlbumArtist != null)
  4690. {
  4691. list.AddRange(hasAlbumArtist.AlbumArtists.Select(i => new Tuple<int, string>(1, i)));
  4692. }
  4693. list.AddRange(item.Genres.Select(i => new Tuple<int, string>(2, i)));
  4694. list.AddRange(item.Studios.Select(i => new Tuple<int, string>(3, i)));
  4695. list.AddRange(item.Tags.Select(i => new Tuple<int, string>(4, i)));
  4696. // keywords was 5
  4697. list.AddRange(inheritedTags.Select(i => new Tuple<int, string>(6, i)));
  4698. return list;
  4699. }
  4700. private void UpdateItemValues(Guid itemId, List<Tuple<int, string>> values, IDatabaseConnection db)
  4701. {
  4702. if (itemId == Guid.Empty)
  4703. {
  4704. throw new ArgumentNullException("itemId");
  4705. }
  4706. if (values == null)
  4707. {
  4708. throw new ArgumentNullException("keys");
  4709. }
  4710. CheckDisposed();
  4711. var guidBlob = itemId.ToGuidBlob();
  4712. // First delete
  4713. db.Execute("delete from ItemValues where ItemId=@Id", guidBlob);
  4714. using (var statement = PrepareStatement(db, "insert into ItemValues (ItemId, Type, Value, CleanValue) values (@ItemId, @Type, @Value, @CleanValue)"))
  4715. {
  4716. foreach (var pair in values)
  4717. {
  4718. var itemValue = pair.Item2;
  4719. // Don't save if invalid
  4720. if (string.IsNullOrWhiteSpace(itemValue))
  4721. {
  4722. continue;
  4723. }
  4724. statement.Reset();
  4725. statement.TryBind("@ItemId", guidBlob);
  4726. statement.TryBind("@Type", pair.Item1);
  4727. statement.TryBind("@Value", itemValue);
  4728. if (pair.Item2 == null)
  4729. {
  4730. statement.TryBindNull("@CleanValue");
  4731. }
  4732. else
  4733. {
  4734. statement.TryBind("@CleanValue", GetCleanValue(pair.Item2));
  4735. }
  4736. statement.MoveNext();
  4737. }
  4738. }
  4739. }
  4740. public void UpdatePeople(Guid itemId, List<PersonInfo> people)
  4741. {
  4742. if (itemId == Guid.Empty)
  4743. {
  4744. throw new ArgumentNullException("itemId");
  4745. }
  4746. if (people == null)
  4747. {
  4748. throw new ArgumentNullException("people");
  4749. }
  4750. CheckDisposed();
  4751. using (WriteLock.Write())
  4752. {
  4753. using (var connection = CreateConnection())
  4754. {
  4755. // First delete
  4756. // "delete from People where ItemId=?"
  4757. connection.Execute("delete from People where ItemId=?", itemId.ToGuidBlob());
  4758. var listIndex = 0;
  4759. using (var statement = PrepareStatement(connection,
  4760. "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values (@ItemId, @Name, @Role, @PersonType, @SortOrder, @ListOrder)"))
  4761. {
  4762. foreach (var person in people)
  4763. {
  4764. if (listIndex > 0)
  4765. {
  4766. statement.Reset();
  4767. }
  4768. statement.TryBind("@ItemId", itemId.ToGuidBlob());
  4769. statement.TryBind("@Name", person.Name);
  4770. statement.TryBind("@Role", person.Role);
  4771. statement.TryBind("@PersonType", person.Type);
  4772. statement.TryBind("@SortOrder", person.SortOrder);
  4773. statement.TryBind("@ListOrder", listIndex);
  4774. statement.MoveNext();
  4775. listIndex++;
  4776. }
  4777. }
  4778. }
  4779. }
  4780. }
  4781. private PersonInfo GetPerson(IReadOnlyList<IResultSetValue> reader)
  4782. {
  4783. var item = new PersonInfo();
  4784. item.ItemId = reader.GetGuid(0);
  4785. item.Name = reader.GetString(1);
  4786. if (!reader.IsDBNull(2))
  4787. {
  4788. item.Role = reader.GetString(2);
  4789. }
  4790. if (!reader.IsDBNull(3))
  4791. {
  4792. item.Type = reader.GetString(3);
  4793. }
  4794. if (!reader.IsDBNull(4))
  4795. {
  4796. item.SortOrder = reader.GetInt32(4);
  4797. }
  4798. return item;
  4799. }
  4800. public List<MediaStream> GetMediaStreams(MediaStreamQuery query)
  4801. {
  4802. CheckDisposed();
  4803. if (query == null)
  4804. {
  4805. throw new ArgumentNullException("query");
  4806. }
  4807. var cmdText = "select " + string.Join(",", _mediaStreamSaveColumns) + " from mediastreams where";
  4808. cmdText += " ItemId=@ItemId";
  4809. if (query.Type.HasValue)
  4810. {
  4811. cmdText += " AND StreamType=@StreamType";
  4812. }
  4813. if (query.Index.HasValue)
  4814. {
  4815. cmdText += " AND StreamIndex=@StreamIndex";
  4816. }
  4817. cmdText += " order by StreamIndex ASC";
  4818. using (WriteLock.Read())
  4819. {
  4820. using (var connection = CreateConnection(true))
  4821. {
  4822. var list = new List<MediaStream>();
  4823. using (var statement = PrepareStatementSafe(connection, cmdText))
  4824. {
  4825. statement.TryBind("@ItemId", query.ItemId.ToGuidBlob());
  4826. if (query.Type.HasValue)
  4827. {
  4828. statement.TryBind("@StreamType", query.Type.Value.ToString());
  4829. }
  4830. if (query.Index.HasValue)
  4831. {
  4832. statement.TryBind("@StreamIndex", query.Index.Value);
  4833. }
  4834. foreach (var row in statement.ExecuteQuery())
  4835. {
  4836. list.Add(GetMediaStream(row));
  4837. }
  4838. }
  4839. return list;
  4840. }
  4841. }
  4842. }
  4843. public void SaveMediaStreams(Guid id, List<MediaStream> streams, CancellationToken cancellationToken)
  4844. {
  4845. CheckDisposed();
  4846. if (id == Guid.Empty)
  4847. {
  4848. throw new ArgumentNullException("id");
  4849. }
  4850. if (streams == null)
  4851. {
  4852. throw new ArgumentNullException("streams");
  4853. }
  4854. cancellationToken.ThrowIfCancellationRequested();
  4855. using (WriteLock.Write())
  4856. {
  4857. using (var connection = CreateConnection())
  4858. {
  4859. // First delete chapters
  4860. connection.Execute("delete from mediastreams where ItemId=@ItemId", id.ToGuidBlob());
  4861. using (var statement = PrepareStatement(connection, string.Format("replace into mediastreams ({0}) values ({1})",
  4862. string.Join(",", _mediaStreamSaveColumns),
  4863. string.Join(",", _mediaStreamSaveColumns.Select(i => "@" + i).ToArray()))))
  4864. {
  4865. foreach (var stream in streams)
  4866. {
  4867. var paramList = new List<object>();
  4868. paramList.Add(id.ToGuidBlob());
  4869. paramList.Add(stream.Index);
  4870. paramList.Add(stream.Type.ToString());
  4871. paramList.Add(stream.Codec);
  4872. paramList.Add(stream.Language);
  4873. paramList.Add(stream.ChannelLayout);
  4874. paramList.Add(stream.Profile);
  4875. paramList.Add(stream.AspectRatio);
  4876. paramList.Add(stream.Path);
  4877. paramList.Add(stream.IsInterlaced);
  4878. paramList.Add(stream.BitRate);
  4879. paramList.Add(stream.Channels);
  4880. paramList.Add(stream.SampleRate);
  4881. paramList.Add(stream.IsDefault);
  4882. paramList.Add(stream.IsForced);
  4883. paramList.Add(stream.IsExternal);
  4884. paramList.Add(stream.Width);
  4885. paramList.Add(stream.Height);
  4886. paramList.Add(stream.AverageFrameRate);
  4887. paramList.Add(stream.RealFrameRate);
  4888. paramList.Add(stream.Level);
  4889. paramList.Add(stream.PixelFormat);
  4890. paramList.Add(stream.BitDepth);
  4891. paramList.Add(stream.IsExternal);
  4892. paramList.Add(stream.RefFrames);
  4893. paramList.Add(stream.CodecTag);
  4894. paramList.Add(stream.Comment);
  4895. paramList.Add(stream.NalLengthSize);
  4896. paramList.Add(stream.IsAVC);
  4897. paramList.Add(stream.Title);
  4898. paramList.Add(stream.TimeBase);
  4899. paramList.Add(stream.CodecTimeBase);
  4900. statement.Execute(paramList.ToArray());
  4901. }
  4902. }
  4903. }
  4904. }
  4905. }
  4906. /// <summary>
  4907. /// Gets the chapter.
  4908. /// </summary>
  4909. /// <param name="reader">The reader.</param>
  4910. /// <returns>ChapterInfo.</returns>
  4911. private MediaStream GetMediaStream(IReadOnlyList<IResultSetValue> reader)
  4912. {
  4913. var item = new MediaStream
  4914. {
  4915. Index = reader[1].ToInt()
  4916. };
  4917. item.Type = (MediaStreamType)Enum.Parse(typeof(MediaStreamType), reader[2].ToString(), true);
  4918. if (reader[3].SQLiteType != SQLiteType.Null)
  4919. {
  4920. item.Codec = reader[3].ToString();
  4921. }
  4922. if (reader[4].SQLiteType != SQLiteType.Null)
  4923. {
  4924. item.Language = reader[4].ToString();
  4925. }
  4926. if (reader[5].SQLiteType != SQLiteType.Null)
  4927. {
  4928. item.ChannelLayout = reader[5].ToString();
  4929. }
  4930. if (reader[6].SQLiteType != SQLiteType.Null)
  4931. {
  4932. item.Profile = reader[6].ToString();
  4933. }
  4934. if (reader[7].SQLiteType != SQLiteType.Null)
  4935. {
  4936. item.AspectRatio = reader[7].ToString();
  4937. }
  4938. if (reader[8].SQLiteType != SQLiteType.Null)
  4939. {
  4940. item.Path = reader[8].ToString();
  4941. }
  4942. item.IsInterlaced = reader.GetBoolean(9);
  4943. if (reader[10].SQLiteType != SQLiteType.Null)
  4944. {
  4945. item.BitRate = reader.GetInt32(10);
  4946. }
  4947. if (reader[11].SQLiteType != SQLiteType.Null)
  4948. {
  4949. item.Channels = reader.GetInt32(11);
  4950. }
  4951. if (reader[12].SQLiteType != SQLiteType.Null)
  4952. {
  4953. item.SampleRate = reader.GetInt32(12);
  4954. }
  4955. item.IsDefault = reader.GetBoolean(13);
  4956. item.IsForced = reader.GetBoolean(14);
  4957. item.IsExternal = reader.GetBoolean(15);
  4958. if (reader[16].SQLiteType != SQLiteType.Null)
  4959. {
  4960. item.Width = reader.GetInt32(16);
  4961. }
  4962. if (reader[17].SQLiteType != SQLiteType.Null)
  4963. {
  4964. item.Height = reader.GetInt32(17);
  4965. }
  4966. if (reader[18].SQLiteType != SQLiteType.Null)
  4967. {
  4968. item.AverageFrameRate = reader.GetFloat(18);
  4969. }
  4970. if (reader[19].SQLiteType != SQLiteType.Null)
  4971. {
  4972. item.RealFrameRate = reader.GetFloat(19);
  4973. }
  4974. if (reader[20].SQLiteType != SQLiteType.Null)
  4975. {
  4976. item.Level = reader.GetFloat(20);
  4977. }
  4978. if (reader[21].SQLiteType != SQLiteType.Null)
  4979. {
  4980. item.PixelFormat = reader[21].ToString();
  4981. }
  4982. if (reader[22].SQLiteType != SQLiteType.Null)
  4983. {
  4984. item.BitDepth = reader.GetInt32(22);
  4985. }
  4986. if (reader[23].SQLiteType != SQLiteType.Null)
  4987. {
  4988. item.IsAnamorphic = reader.GetBoolean(23);
  4989. }
  4990. if (reader[24].SQLiteType != SQLiteType.Null)
  4991. {
  4992. item.RefFrames = reader.GetInt32(24);
  4993. }
  4994. if (reader[25].SQLiteType != SQLiteType.Null)
  4995. {
  4996. item.CodecTag = reader.GetString(25);
  4997. }
  4998. if (reader[26].SQLiteType != SQLiteType.Null)
  4999. {
  5000. item.Comment = reader.GetString(26);
  5001. }
  5002. if (reader[27].SQLiteType != SQLiteType.Null)
  5003. {
  5004. item.NalLengthSize = reader.GetString(27);
  5005. }
  5006. if (reader[28].SQLiteType != SQLiteType.Null)
  5007. {
  5008. item.IsAVC = reader[28].ToBool();
  5009. }
  5010. if (reader[29].SQLiteType != SQLiteType.Null)
  5011. {
  5012. item.Title = reader[29].ToString();
  5013. }
  5014. if (reader[30].SQLiteType != SQLiteType.Null)
  5015. {
  5016. item.TimeBase = reader[30].ToString();
  5017. }
  5018. if (reader[31].SQLiteType != SQLiteType.Null)
  5019. {
  5020. item.CodecTimeBase = reader[31].ToString();
  5021. }
  5022. return item;
  5023. }
  5024. }
  5025. }