From e262f3648077e4d72c51a3b6077b73e93eb439a9 Mon Sep 17 00:00:00 2001 From: elgekko Date: Mon, 25 Sep 2023 23:29:09 +0200 Subject: [PATCH] XLS import with sync data --- mc-vod-sync/QUESTIONS.md | 4 + .../java/hu/user/mcvodsync/VodSyncEntry.java | 5 +- .../src/main/resources/application-dev.yaml | 1 + .../hu/user/mcvodsync/VodXlsProcessorIT.java | 11 +-- ..._Selekt_jogositott_tartalmak_20230906.xlsx | Bin 1778427 -> 1779152 bytes ...chema.sql => 002_create_system_tables.sql} | 16 ++-- .../scripts/003_create_business_tables.sql | 43 ++++++++++ .../scripts/004_create_test_data.sql | 13 --- .../main/java/hu/user/mcvodsync/db/Asset.java | 57 +++++++++++++ .../java/hu/user/mcvodsync/db/AssetId.java | 25 ++++++ .../java/hu/user/mcvodsync/db/AssetSync.java | 36 ++++++++ .../java/hu/user/mcvodsync/db/SyncType.java | 5 ++ .../java/hu/user/mcvodsync/db/UploadFile.java | 4 + .../db/repository/AssetRepository.java | 8 ++ .../db/repository/AssetSyncRepository.java | 8 ++ .../src/main/resources/fields.md | 47 +++++++++++ mc-vod-sync/mc-vod-sync-service/pom.xml | 4 - .../service/data/EntityDataService.java | 4 +- .../service/xls/AssetImportService.java | 64 ++++++++++++++ .../mcvodsync/service/xls/AssetMapper.java | 27 ++++++ .../service/xls/VodXlsProcessor.java | 79 ++++++++++++++---- mc-vod-sync/pom.xml | 42 +++++++++- 22 files changed, 444 insertions(+), 59 deletions(-) create mode 100644 mc-vod-sync/QUESTIONS.md rename mc-vod-sync/mc-vod-sync-db/migrations/scripts/{002_create_initial_schema.sql => 002_create_system_tables.sql} (56%) create mode 100644 mc-vod-sync/mc-vod-sync-db/migrations/scripts/003_create_business_tables.sql delete mode 100644 mc-vod-sync/mc-vod-sync-db/migrations/scripts/004_create_test_data.sql create mode 100644 mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/Asset.java create mode 100644 mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/AssetId.java create mode 100644 mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/AssetSync.java create mode 100644 mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/SyncType.java create mode 100644 mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/repository/AssetRepository.java create mode 100644 mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/repository/AssetSyncRepository.java create mode 100644 mc-vod-sync/mc-vod-sync-db/src/main/resources/fields.md create mode 100644 mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/AssetImportService.java create mode 100644 mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/AssetMapper.java diff --git a/mc-vod-sync/QUESTIONS.md b/mc-vod-sync/QUESTIONS.md new file mode 100644 index 00000000..08494491 --- /dev/null +++ b/mc-vod-sync/QUESTIONS.md @@ -0,0 +1,4 @@ +* Ha hiba történik az import vagy a szinkronizálás során a teljes fájl tartalmat el kell vetni (rollback), vagy ami jó + az frissüljön be? +* Ha hibás egy sor a táblázatban, akkor a teljes importot el kelll dobni, vagy csak a sort kell kihagyni? pl. üres + hubinfo vagy episodetitle \ No newline at end of file diff --git a/mc-vod-sync/mc-vod-sync-app/src/main/java/hu/user/mcvodsync/VodSyncEntry.java b/mc-vod-sync/mc-vod-sync-app/src/main/java/hu/user/mcvodsync/VodSyncEntry.java index 9093e9bf..9bf7e9dd 100644 --- a/mc-vod-sync/mc-vod-sync-app/src/main/java/hu/user/mcvodsync/VodSyncEntry.java +++ b/mc-vod-sync/mc-vod-sync-app/src/main/java/hu/user/mcvodsync/VodSyncEntry.java @@ -6,15 +6,11 @@ package hu.user.mcvodsync; import lombok.extern.log4j.Log4j2; -import org.apache.logging.log4j.LogManager; -import org.apache.logging.log4j.Logger; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.boot.builder.SpringApplicationBuilder; import org.springframework.boot.web.servlet.support.SpringBootServletInitializer; import org.springframework.context.ApplicationContext; -import org.springframework.context.ApplicationListener; -import org.springframework.context.event.ContextRefreshedEvent; import org.springframework.core.SpringVersion; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; import org.springframework.web.servlet.config.annotation.EnableWebMvc; @@ -23,6 +19,7 @@ import org.springframework.web.servlet.config.annotation.EnableWebMvc; @Log4j2 @EnableJpaRepositories("hu.user.mcvodsync.db.repository") @SpringBootApplication(scanBasePackages = {"hu.user.mcvodsync"}) + public class VodSyncEntry extends SpringBootServletInitializer { diff --git a/mc-vod-sync/mc-vod-sync-app/src/main/resources/application-dev.yaml b/mc-vod-sync/mc-vod-sync-app/src/main/resources/application-dev.yaml index ce99c570..dcc1edac 100644 --- a/mc-vod-sync/mc-vod-sync-app/src/main/resources/application-dev.yaml +++ b/mc-vod-sync/mc-vod-sync-app/src/main/resources/application-dev.yaml @@ -21,6 +21,7 @@ spring: datasource: type: com.zaxxer.hikari.HikariDataSource url: jdbc:db2://localhost:50000/vodsync + retrieveMessagesFromServerOnGetMessage: true username: db2admin password: password camunda.bpm: diff --git a/mc-vod-sync/mc-vod-sync-app/src/test/java/hu/user/mcvodsync/VodXlsProcessorIT.java b/mc-vod-sync/mc-vod-sync-app/src/test/java/hu/user/mcvodsync/VodXlsProcessorIT.java index 89476882..0baa47bb 100644 --- a/mc-vod-sync/mc-vod-sync-app/src/test/java/hu/user/mcvodsync/VodXlsProcessorIT.java +++ b/mc-vod-sync/mc-vod-sync-app/src/test/java/hu/user/mcvodsync/VodXlsProcessorIT.java @@ -5,7 +5,6 @@ package hu.user.mcvodsync; -import hu.user.mcvodsync.db.UploadFile; import hu.user.mcvodsync.service.xls.VodXlsProcessor; import lombok.extern.log4j.Log4j2; import org.junit.Test; @@ -32,13 +31,9 @@ public class VodXlsProcessorIT { VodXlsProcessor vodXlsProcessor; @Test - public void listProjects() throws IOException { - Path xlsFile = Paths.get("src", "test", "resources", "AMC_Selekt_jogositott_tartalmak_20230906.xlsx"); - log.info(xlsFile.toAbsolutePath()); - UploadFile uploadFile = UploadFile.builder() - .file(Files.readAllBytes(xlsFile)) - .build(); - vodXlsProcessor.process(uploadFile); + public void processXLS() throws IOException { + Path xlsFile = Paths.get("src/test/resources/AMC_Selekt_jogositott_tartalmak_20230906.xlsx"); + vodXlsProcessor.process(xlsFile.getFileName().toString(), Files.readAllBytes(xlsFile)); } } diff --git a/mc-vod-sync/mc-vod-sync-app/src/test/resources/AMC_Selekt_jogositott_tartalmak_20230906.xlsx b/mc-vod-sync/mc-vod-sync-app/src/test/resources/AMC_Selekt_jogositott_tartalmak_20230906.xlsx index ac221fbba597acec332eeac8d9ff85b1b72bee4b..3c28ea29bf0d99527ea4d88d549c2f1b0b597ec5 100644 GIT binary patch delta 3901 zcma)9X*ARg`~KO6#xP@-u`feonXDnk*tam*LfMj#Wvtl^lF5=KF_^MsEn7lELljv` zwkf+pS+YyEJoum9|2fa|zVD~^`gWglo$K5m?&~`Db@a|>=z|Xr)|8H(6955B004*p z7v$SABWVC2p`J&K7H@5edwU-8+F}x48Ti=Kh2WJh#lNTOXY^bM#z(&InI)wHIXRx* z8d9R+iZ?AmZ0|o8fcC7Go7=*MR7pwNlRRg}r@h=hT#6b*M+}N;TiRAl+V^&Wu&IXo zp`8=mbO?T5c@C~jd?x!2nD6e>9hbvW|KiJC?ZpqB7MN|~@lk90g*P^2;>rY@c&{r( z-O-WtHu*Bv+GeA0!B)I(DpJ-4@o>MXkEigvZK-tOQRZw=|4bH^u?Sqa{i}Q_=6^`rkubcVbcp4#)Llw8iDc8n+ z_S}6p-J%~M_#Hp;sf)AhzQXOgBM81VRYC3!oZ0JGf0k9COB19JQ%8iksxxJ(D9_eV z1+^);(p(seBnr8;CANLs@!XZ5f!Na9Eg=(P%W5$(BU!T=KR+?~hjZ;&G~V9`2gPGR ztm(h(8iaVmzh85_?dg#ZicYxNeOCQ90|1b(l`6V zJgupYSPSpd?&@Ya9+lTF4#nueSW|Pm@L>{l^~n3?)=6sV#8-nkk4vxHQ{!DnYL;Ih zMio&qha(wd``qHL=NRmfOrd9bTR9TmFG!coe|vcVb$F*&@jmevL*1qjO1L!Uw@Ni1H=FESGCX#1}p6yd_d=B%S7b`|Gb= zAF`wdHp^(Xy54Ai@=2%?Q&V`q9FL1f{m#($4_s}rP`HO4-GmVtR#H^a2LAfBcZ(}x zzR{~~SZ@|x4Uw?Xy2E>RNcXj3%A=<^HD>6FV&1FKuKjD?8y<4jewnf(m@z{E&tJ<#xq52Kx;L zw)cxE!de-h=jy(OOPQ^mcqQ8~YF5Bo$Znd8l+VZW5q9IBf0%8)yO1|N!55Le0El)W1xfIg0D%_GFAgtvV(wMhv%Km><{ z9&LB7ROfp{!`{*tVOK-K&M!8nx;gUeWv?3H1@u`8*Q~7;KqtO?^=K8*uP@DQ&Qitu z=8?pZ@pCe8A(p18J{^t9dF!OfVA10G*N?%s(_sR{S}ky{?3*fh$cx)07>DXJ(?+3Ih6 z+YP34__nAo-DA#ZXA^OO#!3#iP0kM>d5Yu5pW@`zE!@cIbce~BL;%lUYtCVmNt5O0 zHAv4cKY=e<3&&I$H>FhE68|%j~N`b_9MuE=gbOT?$1zf;>PIF{0iPu_3@c ztYH?eQm8%6W>HzUo6xjhnpY^fsp-0|Y0UT0=!J{QcLUEIDc_lrxw$?SjcUAY*sSeQQEJr~+5 zBMJ`rT&@p0V6YGf>3!6r%6+h_N!Kv*C=x|5ZuMSXY@CaUO}k|pKv5Hwy09KdI>1F> zq`EE@FgJZij^ynzHQkeix%aJo>@$FV2_JbRX|kOl{QeV3F$b-Wy0tP6o6N9aPu`eU z<7IOy?Ou2vv4|AcZ?lyLwY5Acp;*C-9@azax(|qNac!irVRh&hcQiyc(_M(j#t!Eq zkkAkU3C%(vp;-weG?YL>!w4ia8-avoCy>w_1QME)KtliFaT7>r9s&u?OCX`ol1XU3 zw2?E~?FTLs006uvDzL$k`0C6z=AGvum`$UtHCl21|<(IBq)I~DiGW)QskQmC}=WCt6_$B)*WZCvo z!=~oL=ysYQ)rD7wxZ8Y*yvyhArr))ztM*Us$wR_E7_GdS4ALJcZnEK?4oTnB(hN+h zwstaZ5_GUN;L!}tB=7Ma8jjSUhob5Jm8bZ_kF!JXXaE4q0sxW#0EGFV`~$uGg53kH z+=GL?{5*pGdzP2G?&Y^;Gia)#4LccaRJxl`By^#q!!$m5sNM;N?30F2N%SVhahWI3 z;g+ivCy>F%8euEE1y8h!YbVIK!r#>%*b~fq``fuQ(Tez;_13%z$7{u@$N_9ACx-T1 z0!;g%VyaARa)701o&RoL0>_i2XSg|AOvjyzxPHeX@rh~SX z&>a%xO5|@c=zQ>7<)yN1^cl%!-i}R9#q+;aBgY*nnK_!?-Thj5DeIGKy&RT>@cya` zi%n(`XEO+y_>m|IXU{}-g1_!P|BA1tl?eyE1s|%So&H z@Djq0@AaM%mc>R&>;oX=yDH1!pD%G&ZAMeyc}rHynp`;9^hh^-?fO$QeDSK)4WgQa z){+$T_RQ10E(44qf8_X*>Vc+#;#cc-s}zk_#mCrFT zjLOT}UA|>B%q_#`F?QulofL{iBliXEH}Ua~tj4ldli~L*$=7`j!JG{On?p|5@HqzZ zOX-Lnu5UOQMt%;34lqAQzx*`NxuU1XSUM|tW7jX4TUa%bmebQC;8W68`dHlMy}>wJ zwIJk=m)V=a_aXFok z@}~9q(}bvZU~t`$rG00sUX6U0zq zxCXro7yz=&z+NB&r{?C@cug+$bw|WK=_C}B5)+lB2h;Kj)6SWhy1a6P;j`9 zJ8?(^&P6N{f%DP4CcYMdOTzz7KNw`6A0R7>x Q04#tf9ROI0!T|vIKgi4TTL1t6 delta 3191 zcmY+Gc{tPy7sr1XV}@Z|J3mau$dXW)lw}N2B3t&QZuXs!HHOG2lcB+ctYPdVONm0( z;YvgpONCU*QZkLm``r7!?|tuk{yfilzJGnrIiIoNfS-KM@(6=!COE+jo zCYi3(e9^Dv=HhCdl=pE4_G(jNLjQ~l>0##CE)jF^$w=S8JGo9#_?;%F$x#E9hy*um z=hHmp2x+yq0cnpbQ}>dx9BjrEM(%M9A1UX#jgIFv{b>7uU~QVwcK>C8@;$l6EbAJX z3W~hKWP8PO^l%Z=oc)!;oq_%SoNS(zxIk{B&o)7|zl=EtX6_u6hLDl(;*&@(hL4`kl|D3H50z~HY}HJ5sRDKj*e&|sK;!+G z*)DORoS2r&A6n1647;Y+_U(<%R|wY+f@r$ztQW2D#-WVFiHr!T9l=c6!Mj$&*k?Xt zQUT%{4?ms&s9Tbt(8C@MIS%*_i`)?{4gf$>Hlbn!*Wi3Lp}3;vgM>Zv?rX^q%>o0U z&RW|x@by!hYRQo6!d)YG58F;2JoSf}OqgAm;*-O+HjrO*GJk~b2D&cK3JPm3t%$u9 z6!dL2i=xx}#P&OGf0r5eGofcKca*troB3~l`MgG*5EdO4Up3_g=rN?@ibodJK4I>2 zR#`+5j7%MJ!>st>h7Z&}owK|%C0^b%$UCgTWsG;Yc?xn#GO2qtEN3i3{yg4vCJ~k= z2;W{K*m{w2>9rM~JtA;$%_UXY`KP>KF z&akTyMqOv(H6n-qFz}{@_B7);fLm?Z@}dYVJ>};oq(&BzH;qn7kuU z9OH~UDm2lk`&9=SUu|>49-Tl2L(cF`?j!HW-uyZZsA?ac{~1e8%-D=|bTujI{uv#` zR?;bB!S8d{yZte7mER-)mveJ-Ybi~AvsR6yGau?&t35S1&Y>;wF@oQ@(0`yC)7$Wd zB=!R?e|rRb(q_zkS-*!bM`ft}Qg;Pmu`&G2Qs10T`=>?C{n?sO2iqELWBcza48O{{ z4M(_D3WsrPTJvgrsuc2?OG_G4Ia}Ppp@Qr1{9GhmL|myR=Hg&z03AT$+@#eJ}_1@@P49pHg;Eue#!39@;tzJTfrnN}; zQB}X0onl4kcB8wwPT2AXFOMhf>dAWuDaJU>+{dV1^oG@&>b=VL^#k&!sdJ1CoY*Z- z!jjS#Dop!rbKOo8K`jTSo&2ivY9Rnn!cX$;7g3wJ>#Gb*_yc` zLSrl9cEky}rbN@Kb$9I@TM!%U)kgUilDPT&dwd#UVr)fF$BQ4M5#fb2zHF1e0~17hF1jr z4z6jQA82i>R|I(v(xq(onWOB_otiw3-V}%+o%Hrvy(iW5NZZzP z6D@Jnwi98Jm7G|~(~|Q&LR@eET1Re5A~w2n)YLZ3^M_J+ROHWJ^Y1G2ygLPZAYGPg zo{!YMaAbGa&!@B3&Jw;D@h%3~Tv&nXVHpk^U}l4}rER~4Nz)^`r&I*uTR+h-5ZlC$ ztk$wA#Y&mT(&zN|W8|3+?-FLGs)y|5KL0!& zbrC{?#w+d}Gn-g=HfS?cYULDY1{t|x|7~T*drNsVE%9UM0pX%X^1$t=OgsHEMvHa= zCR{3?2B*QHro@rfnq1U!jQFqfyG5YG%}&hv^~HUjU{QZKH^Y_l_O$Zz*yGO#c^;&c ztuLkD$5a*5ca{0fp6<$=vl3&AJ}V~Ha)8FZPf{@LBBW?fH6JN2Ji0BYlcg^`ZxB$} zq4w2in@o@m5h83CskH3_zDqJ2(IicsR=a~)i2s8jWI3xo9d%=F9U(+9;HXV{zxJA| zgr`vjL-z4=^ znF(i)Za9R`5rO-Y%W_e7m}ebgF(&unCSD^IF7QBULis#%a(mMwU)l~+GUOOjQpKJH}B-ML2k;DTSYB+o|-ERpagr)i2N&gUBnI5jw~1_9nN z1yI7R!;Ne^i6o{h=gIcn0+r;Gj`i7OYfJgQfX25i6jG=6XjfyfD(YBs9q(AuoM76o z8;^B<*Y$>h{p5TcW@{vkBemfCq4i0{aM7^!bFO6F7YM_m86Xz>tyU_o|K5vnkL@e& zKI-7MoX3*IQN!UG!1aZTroGv~%W$l|0#pBZdt7;ocP|uH?#`uhhB@=Q#}^;H_?@py zOGs_hLbA7E@SgEl)?UDJ^sNN*<l+8lAFjv^0cv#2tySD$(g%HA7Ruc&;&5h=&aU* zc-Xwbr!sXzlwxiwBT@*u|711f(LBCzqfBodZdX`0up4eP_Q7!G@WAVf{D+TG>MVZ` ztwo)Z9YNa_OPspzyOQ}zKxg^!Y6J8@*#~qb$As~|Z{J-JzgTL@J%a6E1Av1A4&c9H zLoF5pp;VeI$WMge5JU)$mk7b}5g|Av5rX@(_Zty{6CgrxfThzP+66CpSeA_ON& zgy6)85S(}$1Sb(c4zIlT9q0f6K#rRB8>mD`CU!0m@yll?-WcIGH9qtG^rHDmf5WT`&BV}3vN&O({gG3=5~H-|emvI&+xg@- z&OWDz<;6U%OxL(fKch3-g}xx^FLr@jyL_6YA!~{n^rSJb^k)rdRVKW@#Zh(DD!^v% zgoplqpHLu8rqvbxZX0oug<~)~HJh35u@qAr+NNjtw0P>3;xas)nX+h2-&3w^x4yLU zN%Y^>k;)?n^0&1Lffg{xZf-_*8(a!hU?T)QKO|(dn%&No_Lvt#rwv{y!_q4=mE`s- z)gh|z>TZ#-Qy*(hhnzYdxUEi{t~-?+yZClNetgS#^(B`Bja%J7Lq>sJmb+@iFIif2 zA}STV`Z!ZZ6+s#kk(BU$ZC|C3tDvkiT?$zfex#5=axaxEX<^DepT4Q-vwr7|8lK=H zVi$%=kGNr~vhagDgqWc>_2%(%;SyAgDvv&Os9Z~CI5NKtP*htk?v5h*8f=kYCrz%u zNVGc{=C{)3`5`GKw!&r*(x$`d=)enyUQ4c9r&b|)knG?m< zJoD(Bc)2HN=U?cb{Z9RXqsM4L(T$4*s=|YeKx|c{FPPV{*x>9OwFvVQ`j_Ulm7u_GcKF|(P4!2E)@QiAF$ t2kI&bN`Zh!u)Aewa7dWy<&Y4SsK5YAQ%>lgQ3wD52yy_xI0^&+;9qU^rX&CW diff --git a/mc-vod-sync/mc-vod-sync-db/migrations/scripts/002_create_initial_schema.sql b/mc-vod-sync/mc-vod-sync-db/migrations/scripts/002_create_system_tables.sql similarity index 56% rename from mc-vod-sync/mc-vod-sync-db/migrations/scripts/002_create_initial_schema.sql rename to mc-vod-sync/mc-vod-sync-db/migrations/scripts/002_create_system_tables.sql index f6f8da99..db08459c 100644 --- a/mc-vod-sync/mc-vod-sync-db/migrations/scripts/002_create_initial_schema.sql +++ b/mc-vod-sync/mc-vod-sync-db/migrations/scripts/002_create_system_tables.sql @@ -1,4 +1,4 @@ --- // create_initial_schema +-- // Create system tables -- Migration SQL that makes the change goes here. CREATE TABLE ASSOCIATE ( id BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, @@ -9,17 +9,11 @@ CREATE TABLE ASSOCIATE ( CONSTRAINT pk_associate PRIMARY KEY (id) ); -CREATE TABLE UPLOAD_FILE ( - id BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, - created TIMESTAMP, - name VARCHAR(255), - size INTEGER NOT NULL, - file BLOB, - CONSTRAINT pk_uploadfile PRIMARY KEY (id) -); - +INSERT INTO ASSOCIATE + (name, login, password, active) +VALUES + ('Test User', 'user', 'password', 1); -- //@UNDO -- SQL to undo the change goes here. -DROP TABLE UPLOAD_FILE; DROP TABLE ASSOCIATE; diff --git a/mc-vod-sync/mc-vod-sync-db/migrations/scripts/003_create_business_tables.sql b/mc-vod-sync/mc-vod-sync-db/migrations/scripts/003_create_business_tables.sql new file mode 100644 index 00000000..d6e5c228 --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-db/migrations/scripts/003_create_business_tables.sql @@ -0,0 +1,43 @@ +-- // Create business tables +-- Migration SQL that makes the change goes here. + +CREATE TABLE UPLOAD_FILE ( + id BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, + created TIMESTAMP, + name VARCHAR(255), + size INTEGER NOT NULL, + file BLOB, + CONSTRAINT pk_uploadfile PRIMARY KEY (id) +); + +CREATE TABLE ASSET ( + episode_id VARCHAR(20) NOT NULL, + season_id BIGINT NOT NULL, + series_id BIGINT NOT NULL, + hub_info VARCHAR(255) NOT NULL, + episode_title VARCHAR(255) NOT NULL, + season_title VARCHAR(255), + series_title VARCHAR(255), + season_nr INTEGER, + episode_nr INTEGER, + production_year INTEGER, + country VARCHAR(1000), + age_rating VARCHAR(10), + CONSTRAINT pk_asset PRIMARY KEY (episode_id, season_id, series_id) +); + +CREATE TABLE ASSET_SYNC ( + execution_id VARCHAR(36) NOT NULL, + episode_id VARCHAR(20) NOT NULL, + season_id BIGINT NOT NULL, + series_id BIGINT NOT NULL, + sync_type VARCHAR(6) NOT NULL, + completed TIMESTAMP, + CONSTRAINT pk_asset_sync PRIMARY KEY (episode_id, season_id, series_id) +); + +-- //@UNDO +-- SQL to undo the change goes here. +DROP TABLE ASSET; +DROP TABLE ASSET_SYNC; +DROP TABLE UPLOAD_FILE; diff --git a/mc-vod-sync/mc-vod-sync-db/migrations/scripts/004_create_test_data.sql b/mc-vod-sync/mc-vod-sync-db/migrations/scripts/004_create_test_data.sql deleted file mode 100644 index 81e01ea4..00000000 --- a/mc-vod-sync/mc-vod-sync-db/migrations/scripts/004_create_test_data.sql +++ /dev/null @@ -1,13 +0,0 @@ --- // create test data --- Migration SQL that makes the change goes here. - -INSERT INTO ASSOCIATE - (name, login, password, active) -VALUES - ('Test User', 'user', 'password', 1); - - --- //@UNDO --- SQL to undo the change goes here. - -DELETE FROM ASSOCIATE; diff --git a/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/Asset.java b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/Asset.java new file mode 100644 index 00000000..314e4cd9 --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/Asset.java @@ -0,0 +1,57 @@ +package hu.user.mcvodsync.db; + +import lombok.*; + +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.Id; +import javax.persistence.IdClass; +import java.io.Serializable; + +@Getter +@Setter +@Entity +@Builder +@ToString +@NoArgsConstructor +@AllArgsConstructor +@IdClass(AssetId.class) +public class Asset implements Serializable { + @Id + private String episodeId; + + @Id + private Long seasonId; + + @Id + private Long seriesId; + + + @Column(nullable = false) + private String hubInfo; + + + @Column(nullable = false) + private String episodeTitle; + + + private String seasonTitle; + + + private String seriesTitle; + + + private Integer seasonNr; + + + private Integer episodeNr; + + + private Integer productionYear; + + + private String country; + + + private String ageRating; +} diff --git a/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/AssetId.java b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/AssetId.java new file mode 100644 index 00000000..5ff15f98 --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/AssetId.java @@ -0,0 +1,25 @@ +package hu.user.mcvodsync.db; + +import lombok.*; + +import javax.persistence.Column; +import java.io.Serializable; + +@Getter +@Setter +@Builder +@ToString +@NoArgsConstructor +@AllArgsConstructor +public class AssetId implements Serializable { + + @Column(nullable = false) + private String episodeId; + + @Column(nullable = false) + private Long seasonId; + + @Column(nullable = false) + private Long seriesId; + +} diff --git a/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/AssetSync.java b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/AssetSync.java new file mode 100644 index 00000000..f9fc2a41 --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/AssetSync.java @@ -0,0 +1,36 @@ +package hu.user.mcvodsync.db; + +import lombok.*; + +import javax.persistence.*; +import java.io.Serializable; +import java.util.Date; + +@Getter +@Setter +@Entity +@Builder +@ToString +@NoArgsConstructor +@AllArgsConstructor +@IdClass(AssetId.class) +public class AssetSync implements Serializable { + + @Column(nullable = false) + private String executionId; + + @Id + private String episodeId; + + @Id + private Long seasonId; + + @Id + private Long seriesId; + + @Enumerated(EnumType.STRING) + @Column(nullable = false) + private SyncType syncType; + + private Date completed; +} diff --git a/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/SyncType.java b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/SyncType.java new file mode 100644 index 00000000..1622e91e --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/SyncType.java @@ -0,0 +1,5 @@ +package hu.user.mcvodsync.db; + +public enum SyncType { + INSERT, UPDATE, DELETE, ERROR +} diff --git a/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/UploadFile.java b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/UploadFile.java index 79885a0c..4043e59e 100644 --- a/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/UploadFile.java +++ b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/UploadFile.java @@ -19,8 +19,12 @@ public class UploadFile implements Serializable { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) Long id; + Date created; + String name; + int size; + byte[] file; } diff --git a/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/repository/AssetRepository.java b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/repository/AssetRepository.java new file mode 100644 index 00000000..6fb2e770 --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/repository/AssetRepository.java @@ -0,0 +1,8 @@ +package hu.user.mcvodsync.db.repository; + +import hu.user.mcvodsync.db.Asset; +import hu.user.mcvodsync.db.AssetId; +import org.springframework.data.jpa.repository.JpaRepository; + +public interface AssetRepository extends JpaRepository { +} diff --git a/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/repository/AssetSyncRepository.java b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/repository/AssetSyncRepository.java new file mode 100644 index 00000000..048fa038 --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-db/src/main/java/hu/user/mcvodsync/db/repository/AssetSyncRepository.java @@ -0,0 +1,8 @@ +package hu.user.mcvodsync.db.repository; + +import hu.user.mcvodsync.db.AssetId; +import hu.user.mcvodsync.db.AssetSync; +import org.springframework.data.jpa.repository.JpaRepository; + +public interface AssetSyncRepository extends JpaRepository { +} diff --git a/mc-vod-sync/mc-vod-sync-db/src/main/resources/fields.md b/mc-vod-sync/mc-vod-sync-db/src/main/resources/fields.md new file mode 100644 index 00000000..d1688ea6 --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-db/src/main/resources/fields.md @@ -0,0 +1,47 @@ +0 CATALOG_ID +1 SEASON_CATALOG_ID +2 SERIES_GROUP_CATALOG_ID +3 HUB_INFO +4 PROG_ORIG_TITLE +5 PROG_LOCAL_TITLE +6 SEASON_ORIG_TITLE +7 SEASON_LOCAL_TITLE +8 SER_GROUP_ORIG_TITLE +9 SERIES_GROUP_LOCAL_TITLE +10 SEASON_NUMBER +11 EPISODE_NR +12 PROG_SYNOPSIS_FULL_ENG +13 PROG_SYNOPSIS_FULL_HUN +14 PROG_SYNOPSIS_SHORT_ENG +15 PROG_SYNOPSIS_SHORT_HUN +16 SEASON_SYNOPSIS_FULL_ENG +17 SEASON_SYNOPSIS_FULL_HUN +18 SEASON_SYNOPSIS_SHORT_ENG +19 SEASON_SYNOPSIS_SHORT_HUN +20 SERIES_GROUP_SYNOPSIS_FULL_ENG +21 SERIES_GROUP_SYNOPSIS_FULL_HUN +22 SERIES_GROUP_SYNOPSIS_SHORT_ENG +23 SERIES_GROUP_SYNOPSIS_SHORT_HUN +24 ORIGINAL_LANGUAGE +25 CONTENT_TYPE +26 CONTENT_SOURCE +27 AGE_RATING +28 PG_SEX +29 PG_LANGUAGE +30 PG_VIOLENCE +31 PG_SUBSTANCES +32 PG_FEAR +33 PG_RELIGION +34 COUNTRY_OF_ORIGIN +35 PRODUCTION_YEAR +36 DIRECTOR +37 X_CAST +38 SERIES_GROUP_GENRE_1 +39 SERIES_SEASON_GENRE_1 +40 PROG_GENRE_1 +41 SERIES_GROUP_GENRE_2 +42 SERIES_SEASON_GENRE_2 +43 PROG_GENRE_2 +44 SERIES_GROUP_GENRE_3 +45 SERIES_SEASON_GENRE_3 +46 PROG_GENRE_3 diff --git a/mc-vod-sync/mc-vod-sync-service/pom.xml b/mc-vod-sync/mc-vod-sync-service/pom.xml index ee71d40a..5d0ed5a1 100644 --- a/mc-vod-sync/mc-vod-sync-service/pom.xml +++ b/mc-vod-sync/mc-vod-sync-service/pom.xml @@ -8,10 +8,6 @@ mc-vod-sync 0.0.1-SNAPSHOT - - - - org.springframework diff --git a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/data/EntityDataService.java b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/data/EntityDataService.java index e7fc6e15..bacc853f 100644 --- a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/data/EntityDataService.java +++ b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/data/EntityDataService.java @@ -60,7 +60,7 @@ public class EntityDataService extends EntityDataService JSONAssert.assertEquals(json1, json2, JSONCompareMode.LENIENT); } catch (Throwable e) { result = false; - log.error(e); + //log.error(e); } return result; @@ -78,7 +78,7 @@ public class EntityDataService extends EntityDataService JSONAssert.assertEquals(json1, json2, JSONCompareMode.LENIENT); } catch (Throwable e) { result = true; - log.error(e); + //log.error(e); } } return result; diff --git a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/AssetImportService.java b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/AssetImportService.java new file mode 100644 index 00000000..723d866b --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/AssetImportService.java @@ -0,0 +1,64 @@ +package hu.user.mcvodsync.service.xls; + + +import hu.user.mcvodsync.db.Asset; +import hu.user.mcvodsync.db.AssetId; +import hu.user.mcvodsync.db.AssetSync; +import hu.user.mcvodsync.db.SyncType; +import hu.user.mcvodsync.db.repository.AssetRepository; +import hu.user.mcvodsync.db.repository.AssetSyncRepository; +import hu.user.mcvodsync.service.data.EntityDataService; +import lombok.extern.log4j.Log4j2; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Service; +import org.springframework.transaction.annotation.Transactional; + +import java.util.Objects; +import java.util.Optional; + +@Log4j2 +@Service +public class AssetImportService { + @Autowired + AssetRepository assetRepository; + + @Autowired + AssetSyncRepository assetSyncRepository; + + @Autowired + EntityDataService entityDataService; + + @Transactional + public void processAsset(String executionId, Asset asset) { + AssetId assetId = AssetId.builder() + .episodeId(asset.getEpisodeId()) + .seasonId(asset.getSeasonId()) + .seriesId(asset.getSeriesId()) + .build(); + Optional optionalEntity = assetRepository.findById(assetId); + + AssetSync assetSync = AssetSync.builder() + .executionId(executionId) + .episodeId(asset.getEpisodeId()) + .seasonId(asset.getSeasonId()) + .seriesId(asset.getSeriesId()) + .build(); + + if (optionalEntity.isPresent()) { + boolean areDifferent = entityDataService.areDifferent(asset, optionalEntity.get()); + if (areDifferent) { +// log.info("History UPDATE registered for {}", assetId); + assetSync.setSyncType(SyncType.UPDATE); + } + } else { +// log.info("History INSERT registered for {}", assetId); + assetSync.setSyncType(SyncType.INSERT); + } + + if (Objects.nonNull(assetSync.getSyncType())) { + assetSyncRepository.save(assetSync); + } + assetRepository.save(asset); + } + +} diff --git a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/AssetMapper.java b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/AssetMapper.java new file mode 100644 index 00000000..26a2e84e --- /dev/null +++ b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/AssetMapper.java @@ -0,0 +1,27 @@ +package hu.user.mcvodsync.service.xls; + +import hu.user.mcvodsync.db.Asset; +import org.mapstruct.Mapper; +import org.mapstruct.Mapping; +import org.mapstruct.ReportingPolicy; + +import java.util.Map; + + +@Mapper(unmappedTargetPolicy = ReportingPolicy.IGNORE) +public interface AssetMapper { + @Mapping(target = "episodeId", source = "CATALOG_ID") + @Mapping(target = "seasonId", source = "SEASON_CATALOG_ID") + @Mapping(target = "seriesId", source = "SERIES_GROUP_CATALOG_ID") + @Mapping(target = "hubInfo", source = "HUB_INFO") + @Mapping(target = "episodeTitle", source = "PROG_ORIG_TITLE") + @Mapping(target = "seasonTitle", source = "SEASON_ORIG_TITLE") + @Mapping(target = "seriesTitle", source = "SER_GROUP_ORIG_TITLE") + @Mapping(target = "seasonNr", source = "SEASON_NUMBER") + @Mapping(target = "episodeNr", source = "EPISODE_NR") + @Mapping(target = "ageRating", source = "AGE_RATING") + @Mapping(target = "country", source = "COUNTRY_OF_ORIGIN") + @Mapping(target = "productionYear", source = "PRODUCTION_YEAR") + Asset toEntity(Map xlsRowData); + +} diff --git a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/VodXlsProcessor.java b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/VodXlsProcessor.java index d0b60fd1..6eb4bfc6 100644 --- a/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/VodXlsProcessor.java +++ b/mc-vod-sync/mc-vod-sync-service/src/main/java/hu/user/mcvodsync/service/xls/VodXlsProcessor.java @@ -1,45 +1,92 @@ package hu.user.mcvodsync.service.xls; -import hu.user.mcvodsync.db.UploadFile; +import hu.user.mcvodsync.db.Asset; import lombok.extern.log4j.Log4j2; +import org.apache.commons.lang3.StringUtils; +import org.apache.commons.lang3.time.DurationFormatUtils; +import org.dhatim.fastexcel.reader.Cell; import org.dhatim.fastexcel.reader.ReadableWorkbook; import org.dhatim.fastexcel.reader.Row; import org.dhatim.fastexcel.reader.Sheet; +import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.io.ByteArrayInputStream; import java.io.IOException; import java.io.InputStream; -import java.util.Collections; -import java.util.List; +import java.time.Duration; +import java.time.Instant; +import java.util.*; +import java.util.stream.Collectors; import java.util.stream.Stream; @Log4j2 @Service public class VodXlsProcessor { - private final boolean firstRowAsHeader = true; private final List mappings = Collections.emptyList(); + private List headers; - public void process(UploadFile uploadFile) { - try (InputStream is = new ByteArrayInputStream(uploadFile.getFile()); + @Autowired + AssetMapper assetMapper; + + @Autowired + AssetImportService assetImportService; + + public void process(String fileName, byte[] xlsData) { + Instant start = Instant.now(); + String executionId = UUID.randomUUID().toString(); + try (InputStream is = new ByteArrayInputStream(xlsData); ReadableWorkbook wb = new ReadableWorkbook(is)) { Sheet sheet = wb.getFirstSheet(); + try (Stream rows = sheet.openStream()) { - rows.forEach(r -> { -// if (firstRowAsHeader && r.getRowNum() == 1) { -// continue; -// } - String str = r.getCellAsString(0).orElse(null); - log.info("{} {}", r.getRowNum(), str); -// BigDecimal num = r.getCellAsNumber(0).orElse(null); -// String str = r.getCellAsString(1).orElse(null); -// LocalDateTime date = r.getCellAsDate(2).orElse(null); + rows.forEach(row -> { + if (row.getRowNum() == 1) { + headers = getHeaders(row); + } else { + processRow(executionId, row); + } }); } } catch (IOException e) { - log.error("Excel file reading error from {}. System message: {}", uploadFile.getName(), e.getMessage()); + log.error("Excel file reading error from {}. System message: {}", fileName, e.getMessage()); + } + Duration executionDuration = Duration.between(start, Instant.now()); + String duration = DurationFormatUtils.formatDuration(executionDuration.toMillis(), "H:mm:ss", true); + log.info("Processing took {}", duration); + } + + private List getHeaders(Row row) { + return row.stream().collect(Collectors.toList()); + } + + private void processRow(String executionId, Row r) { + Map rowData = new HashMap<>(); + r.stream().filter(Objects::nonNull).forEach(c -> { + try { + String data = c.getRawValue(); + rowData.put(headers.get(c.getColumnIndex()).getRawValue(), data); + } catch (Exception e) { + log.error("Cell error!", e); + } + }); + Asset asset = assetMapper.toEntity(rowData); + if (Objects.isNull(asset.getSeasonId())) { + asset.setSeasonId(0L); + } + if (Objects.isNull(asset.getSeriesId())) { + asset.setSeriesId(0L); } + if (StringUtils.isBlank(asset.getHubInfo()) || StringUtils.isBlank(asset.getEpisodeTitle())) { + //log.error("Hub not defined for {}", asset); + } else { + try { + assetImportService.processAsset(executionId, asset); + } catch (Exception e) { + log.error("Processing error for {}", asset); + } + } } } diff --git a/mc-vod-sync/pom.xml b/mc-vod-sync/pom.xml index 8b4ddbeb..243eef56 100644 --- a/mc-vod-sync/pom.xml +++ b/mc-vod-sync/pom.xml @@ -18,6 +18,8 @@ 1.8 1.8 7.19.0 + 1.5.5.Final + 1.18.26 org.springframework.boot @@ -27,13 +29,51 @@ + + + + org.apache.maven.plugins + maven-compiler-plugin + 3.8.0 + + + + org.mapstruct + mapstruct-processor + ${org.mapstruct.version} + + + org.projectlombok + lombok + ${lombok.version} + + + org.projectlombok + lombok-mapstruct-binding + 0.2.0 + + + + + -Amapstruct.defaultComponentModel=spring + + + + + + org.projectlombok lombok - 1.18.26 + ${lombok.version} provided + + org.mapstruct + mapstruct + ${org.mapstruct.version} + org.apache.logging.log4j log4j-api -- 2.54.0