From e05f5868fe3c2cb6553855e93a7f104a8e4e6a48 Mon Sep 17 00:00:00 2001 From: =?utf8?q?V=C3=A1s=C3=A1ry=20D=C3=A1niel?= Date: Mon, 23 Jul 2018 07:49:12 +0000 Subject: [PATCH] git-tfs-id: [http://tfs.userrendszerhaz.hu:8080/tfs/DefaultCollection]$/MediaCube;C31205 --- .../sql/update-itemtypes.sql | 40 ++++++++++++++++++- 1 file changed, 39 insertions(+), 1 deletion(-) diff --git a/server/user.jobengine.osgi.db/sql/update-itemtypes.sql b/server/user.jobengine.osgi.db/sql/update-itemtypes.sql index c93fe78a..361bb215 100644 --- a/server/user.jobengine.osgi.db/sql/update-itemtypes.sql +++ b/server/user.jobengine.osgi.db/sql/update-itemtypes.sql @@ -1,7 +1,45 @@ select * from itemtype insert into itemtype (name, description, isstatic) values('Hír bejátszó','Hír bejátszó','N') +select count(*) from media where ITEMTYPEID in (select id from itemtype where name='Generic') +select count(*) from item where ITEMTYPEID in (select id from itemtype where name='Mûsor nyersanyag') + +delete from itemtype where ID in (select id from itemtype where name='Mûsor nyersanyag') + +update media set itemtypeid = 81 where itemtypeid = 66 + + update media set itemtypeid=(select id from itemtype where name='Visszarögzített') where ID in (select mediaid from vw_items where itemtitle like '%Echo TV%' and itemtitle like '2%' and mediatitle like '%PGM%') update media set itemtypeid=(select id from itemtype where name='Hír bejátszó') where ID in (select mediaid from vw_items where itemtitle like '%Echo TV%' and itemtitle like '2%' and mediatitle not like '%CLN%' and mediatitle not like '%PGM%') +update media set itemtypeid=(select id from itemtype where name='Visszarögzített') where ID in (select mediaid from vw_items where mediatitle like '%CLN%') + +select * from vw_items where itemtitle like '%Echo TV%' and itemtitle like '2%' and mediatitle not like '%CLN%' and mediatitle not like '%PGM%' +select * from vw_items where mediatitle like '%CLN%' +select * from vw_items where mediatitle like '%PGM%' + +update media set itemtypeid=(select id from itemtype where name='Hír bejátszó') where ITEMTYPEID=2 and ID in ( +select mediaid from vw_items where regexp_like(mediafilehouseid, '\d{8}_\d{1}-\d{8}\.MXF', 1)) + +update media set itemtypeid=(select id from itemtype where name='Hír nyers') where ITEMTYPEID=2 and ID in ( +select mediaid from vw_items where mediatype='Generic' and MEDIAHOUSEID like 'MC-%' and MEDIAFILEHOUSEID not like 'MC-%' and regexp_like(mediafilehouseid, '\d{6}', 1) and regexp_like(itemtitle, '\d{6}', 1) and itemtitle not like 'MC-%') + +update media set itemtypeid=(select id from itemtype where name='Mûsor') where ITEMTYPEID=2 and ID in ( +select mediaid from vw_items where mediatype='Generic' and regexp_like(mediafilehouseid, 'M\d{6}.\.MXF', 1) +) + +update media set itemtypeid=(select id from itemtype where name='Promo') where ITEMTYPEID=2 and ID in ( +select mediaid from vw_items where mediatype='Generic' and regexp_like(mediafilehouseid, 'P\d{6}.\.MXF', 1) +) + +update media set itemtypeid=(select id from itemtype where name='Reklám') where ITEMTYPEID=2 and ID in ( +select mediaid from vw_items where mediatype='Generic' and regexp_like(mediafilehouseid, 'R\d{6}.\.MXF', 1) +) + +select mediatype, itemtitle, mediatitle, mediahouseid, mediafilehouseid from vw_items where regexp_like(mediafilehouseid, '\d{8}_\d{1}-\d{8}\.MXF', 1) +select itemtitle, mediatitle, mediahouseid, mediafilehouseid from vw_items where mediatype='Generic' and MEDIAHOUSEID like 'MC-%' and MEDIAFILEHOUSEID not like 'MC-%' and regexp_like(mediafilehouseid, '\d{6}', 1) and regexp_like(itemtitle, '\d{6}', 1) and itemtitle not like 'MC-%' +select itemtitle, mediatitle, mediahouseid, mediafilehouseid from vw_items where mediatype='Generic' and regexp_like(mediafilehouseid, 'M\d{6}.\.MXF', 1) +select itemtitle, mediatitle, mediahouseid, mediafilehouseid from vw_items where mediatype='Generic' and regexp_like(mediafilehouseid, 'P\d{6}.\.MXF', 1) +select itemtitle, mediatitle, mediahouseid, mediafilehouseid from vw_items where mediatype='Generic' and regexp_like(mediafilehouseid, 'R\d{6}.\.MXF', 1) +select itemtitle, mediatitle, mediahouseid, mediafilehouseid from vw_items where mediatype='Generic' and itemtitle like '%híradó%' -select * from vw_items where itemtitle like '%Echo TV%' and itemtitle like '2%' and mediatitle not like '%CLN%' and mediatitle not like '%PGM%' \ No newline at end of file +"select * from MEDIADESCRIPTION where contains(description, '170807_DEMO_ERDOGAN_BI-20180720103153*')" -- 2.54.0