From 84786d56996bcb032332ef03ca36d0e8700c2382 Mon Sep 17 00:00:00 2001 From: =?utf8?q?V=C3=A1s=C3=A1ry=20D=C3=A1niel?= Date: Mon, 6 Aug 2018 09:59:15 +0000 Subject: [PATCH] git-tfs-id: [http://tfs.userrendszerhaz.hu:8080/tfs/DefaultCollection]$/MediaCube;C31228 --- .../log/mediacube-err.log | 0 .../log/mediacube.log | 0 .../sql/update-itemtypes.sql | 38 ++++++++++++++++++- 3 files changed, 37 insertions(+), 1 deletion(-) create mode 100644 server/user.jobengine.osgi.commons/log/mediacube-err.log create mode 100644 server/user.jobengine.osgi.commons/log/mediacube.log diff --git a/server/user.jobengine.osgi.commons/log/mediacube-err.log b/server/user.jobengine.osgi.commons/log/mediacube-err.log new file mode 100644 index 00000000..e69de29b diff --git a/server/user.jobengine.osgi.commons/log/mediacube.log b/server/user.jobengine.osgi.commons/log/mediacube.log new file mode 100644 index 00000000..e69de29b diff --git a/server/user.jobengine.osgi.db/sql/update-itemtypes.sql b/server/user.jobengine.osgi.db/sql/update-itemtypes.sql index 361bb215..9764463e 100644 --- a/server/user.jobengine.osgi.db/sql/update-itemtypes.sql +++ b/server/user.jobengine.osgi.db/sql/update-itemtypes.sql @@ -35,6 +35,7 @@ update media set itemtypeid=(select id from itemtype where name='Rekl 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) @@ -42,4 +43,39 @@ select itemtitle, mediatitle, mediahouseid, mediafilehouseid from vw_items where 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 MEDIADESCRIPTION where contains(description, '170807_DEMO_ERDOGAN_BI-20180720103153*')" +select itemtitle, mediatitle, mediahouseid, mediafilehouseid, tag, username, source from vw_items, vw_workflow where mediatype='Generic' and itemtitle like '%híradó%' and filename=mediafilehouseid + +select itemtitle, mediatitle, mediahouseid, mediafilehouseid, tag, username, source from vw_items +left outer join vw_workflow on (filename=mediafilehouseid) +where mediatype='Generic' and itemtitle like '%híradó%' + +select * from vw_items +where mediatype='Generic' and itemtitle like '%híradó%' and mediafilehouseid not in (select filename from vw_workflow) + +--Törökõr +select mediatype, itemtitle, itemhouseid, mediatitle, mediahouseid, mediafilehouseid from vw_items where mediahouseid like 'MC-%' and regexp_like(itemtitle, '^\d{6}', 1) order by itemtitle asc + +drop view vw_wflog +go +CREATE OR REPLACE view vw_workflow as +( +select +*, +RIGHT(REPLACE(destination, '\', '/'), LENGTH(REPLACE(destination, '\', '/')) - LOCATE_IN_STRING(REPLACE(destination, '\', '/'),'/',-1)) filename + from WORKFLOWACTION +) +go +select * from vw_workflow +fetch first 100 rows only + +select normdest, RIGHT(normdest, LENGTH(normdest) - LOCATE_IN_STRING(normdest,'/',-1)) filename from vw_wflog +fetch first 1000 rows only +where destination like '%26506533-15%' + + +select * from media where title like '%elgekko%' +select * from mediafile where mediaid = 122645 + +INSERT INTO DB2ADMIN.MEDIAFILE(MEDIAID, STOREID, FILETYPEID, RELATIVEPATH, HOUSEID) + VALUES(122645, 1, 1, 'xxxx', 'xxxx') +GO -- 2.54.0