From a94fdae8a9690a192c4616d5d55640f3e2cc6359 Mon Sep 17 00:00:00 2001 From: =?utf8?q?V=C3=A1s=C3=A1ry=20D=C3=A1niel?= Date: Mon, 2 Jul 2018 07:30:14 +0000 Subject: [PATCH] git-tfs-id: [http://tfs.userrendszerhaz.hu:8080/tfs/DefaultCollection]$/MediaCube;C31138 --- .../dashboard-remote-debug.launch | 18 -- .../user/jobengine/db/BreakDAO_SJProfile0.ser | Bin 3253 -> 3253 bytes .../db/DomainCategoryDAO_SJProfile0.ser | Bin 2552 -> 2552 bytes .../jobengine/db/DomainDAO_SJProfile0.ser | Bin 4043 -> 4043 bytes .../db/DomainIndexDAO_SJProfile0.ser | Bin 3143 -> 3143 bytes .../jobengine/db/EntityBaseDAO_SJProfile0.ser | Bin 2286 -> 2286 bytes .../jobengine/db/FileTypeDAO_SJProfile0.ser | Bin 3872 -> 3872 bytes .../jobengine/db/FolderDAO_SJProfile0.ser | Bin 2479 -> 2479 bytes .../jobengine/db/HelperDAO_SJProfile0.ser | Bin 1674 -> 1674 bytes .../user/jobengine/db/ItemDAO_SJProfile0.ser | Bin 4337 -> 4337 bytes .../jobengine/db/ItemTypeDAO_SJProfile0.ser | Bin 2933 -> 2933 bytes .../generated/user/jobengine/db/JobDAO.java | 4 +- .../user/jobengine/db/JobDAO_SJProfile0.ser | Bin 4638 -> 4625 bytes .../db/JobParametersDAO_SJProfile0.ser | Bin 2355 -> 2355 bytes .../jobengine/db/MasterIdDAO_SJProfile0.ser | Bin 1655 -> 1655 bytes .../user/jobengine/db/MediaDAO_SJProfile0.ser | Bin 4106 -> 4106 bytes .../user/jobengine/db/MediaFileDAO.java | 110 ++++++++-- .../jobengine/db/MediaFileDAO_SJProfile0.ser | Bin 3572 -> 4175 bytes .../jobengine/db/MetadataDAO_SJProfile0.ser | Bin 6747 -> 6747 bytes .../db/MetadataElementDAO_SJProfile0.ser | Bin 2990 -> 2990 bytes .../db/MetadataTypeDAO_SJProfile0.ser | Bin 3013 -> 3013 bytes .../db/RemoteStoreDAO_SJProfile0.ser | Bin 3178 -> 3178 bytes .../db/SceneContentDAO_SJProfile0.ser | Bin 3376 -> 3376 bytes .../user/jobengine/db/SceneDAO_SJProfile0.ser | Bin 2973 -> 2973 bytes .../db/SearchDefinitionDAO_SJProfile0.ser | Bin 3528 -> 3528 bytes .../user/jobengine/db/ShotDAO_SJProfile0.ser | Bin 3487 -> 3487 bytes .../user/jobengine/db/StoreDAO_SJProfile0.ser | Bin 3164 -> 3164 bytes .../jobengine/db/StoreUriDAO_SJProfile0.ser | Bin 4756 -> 4756 bytes .../jobengine/db/UserInfoDAO_SJProfile0.ser | Bin 3637 -> 3637 bytes .../user/jobengine/db/WorkflowActionDAO.java | 59 +++-- .../db/WorkflowActionDAO_SJProfile0.ser | Bin 4524 -> 4737 bytes server/user.jobengine.osgi.db/sql/alter.sql | 53 +++++ .../sql/cleanup-dev.sql | 205 ++++++++++++++++++ server/user.jobengine.osgi.db/sql/cleanup.sql | 40 +++- server/user.jobengine.osgi.db/sql/ftclob.sql | 25 +++ server/user.jobengine.osgi.db/sql/stat-rd.sql | 113 +++++++++- 36 files changed, 553 insertions(+), 74 deletions(-) delete mode 100644 server/-configuration/dashboard-remote-debug.launch create mode 100644 server/user.jobengine.osgi.db/sql/alter.sql create mode 100644 server/user.jobengine.osgi.db/sql/cleanup-dev.sql create mode 100644 server/user.jobengine.osgi.db/sql/ftclob.sql diff --git a/server/-configuration/dashboard-remote-debug.launch b/server/-configuration/dashboard-remote-debug.launch deleted file mode 100644 index 4baf1a2f..00000000 --- a/server/-configuration/dashboard-remote-debug.launch +++ /dev/null @@ -1,18 +0,0 @@ - - - - - - - - - - - - - - - - - - diff --git a/server/user.jobengine.osgi.db/generated/user/jobengine/db/BreakDAO_SJProfile0.ser b/server/user.jobengine.osgi.db/generated/user/jobengine/db/BreakDAO_SJProfile0.ser index 99f14873cbae9169540689f820434e567380f3e1..c5c009bb6025fb7db5e9994889f133b7a4061211 100644 GIT binary patch delta 18 Zcmdlgxm9ul4*vHsf1&4P?exB);p27Lek delta 18 acmdlgxm9ul4*vHsebn*|x4a{vHIq6b$1 delta 18 acmew%{6lyH4te_DP64*vHsc>n*|wn@c{ruE(ZMo delta 18 acmX>te_DP64uaa>{p4*vHsf4n*|xoxB)`_25SHS delta 18 ZcmX>uaa>{p4*vHsefn*|x~vI77}xd&7L delta 18 acmaDS_)c&G4*vHsd4n*|y9`2aqu1{MGS delta 18 acmZ1=w?J+K4*vHsdKn*|x?aR2~7kOr>+ delta 18 acmZ24yk2+%4*vHse7n*|vk@B;u$3kRwI delta 18 acmeyU_)&2K4*vHsdun*|wDxByCi2PXgk delta 18 Zcmew=_El^H4*vHsexn*|vk@H0M}ypS_*a)My~*P~{?f^(?4>SM( diff --git a/server/user.jobengine.osgi.db/generated/user/jobengine/db/JobParametersDAO_SJProfile0.ser b/server/user.jobengine.osgi.db/generated/user/jobengine/db/JobParametersDAO_SJProfile0.ser index 121a755ee478e6f4619b759ca5e1dc45d29f2a0b..2a5aeb0247cde724a23d3f191b6b0796cdfe683f 100644 GIT binary patch delta 18 Zcmdliv{`5a4*vHsdWn*|w_H~>K)25A5Q delta 18 acmdliv{`5a4*vHseBn*|xuSOH1-2Pgml delta 18 acmey)^POh{4t@|+XpBB diff --git a/server/user.jobengine.osgi.db/generated/user/jobengine/db/MediaDAO_SJProfile0.ser b/server/user.jobengine.osgi.db/generated/user/jobengine/db/MediaDAO_SJProfile0.ser index ea938a1c6b5fead81c6218eebdae304589ef55c4..d893d8f48b2442c248b6400e5f620352e7c77552 100644 GIT binary patch delta 18 ZcmeBD=u+6g!^j$ctiO(HvmoP7J^(p=1|I+b delta 18 ZcmeBD=u+6g!^j#J@$=@D&4P?S`2av32R{G+ diff --git a/server/user.jobengine.osgi.db/generated/user/jobengine/db/MediaFileDAO.java b/server/user.jobengine.osgi.db/generated/user/jobengine/db/MediaFileDAO.java index 515f10d4..353230a0 100644 --- a/server/user.jobengine.osgi.db/generated/user/jobengine/db/MediaFileDAO.java +++ b/server/user.jobengine.osgi.db/generated/user/jobengine/db/MediaFileDAO.java @@ -16,6 +16,7 @@ class MediaFileIter extends sqlj.runtime.ref.ResultSetIterImpl implements sqlj.runtime.NamedIterator { + private int houseIdNdx; private int fileStructInfoNdx; private int relativePathNdx; private int fileTypeIdNdx; @@ -32,6 +33,7 @@ implements sqlj.runtime.NamedIterator fileTypeIdNdx = findColumn("fileTypeId"); relativePathNdx = findColumn("relativePath"); fileStructInfoNdx = findColumn("fileStructInfo"); + houseIdNdx = findColumn("houseId"); } public MediaFileIter(sqlj.runtime.profile.RTResultSet resultSet, int fetchSize, int maxRows) throws java.sql.SQLException @@ -43,6 +45,7 @@ implements sqlj.runtime.NamedIterator fileTypeIdNdx = findColumn("fileTypeId"); relativePathNdx = findColumn("relativePath"); fileStructInfoNdx = findColumn("fileStructInfo"); + houseIdNdx = findColumn("houseId"); } public long id() throws java.sql.SQLException @@ -74,12 +77,17 @@ implements sqlj.runtime.NamedIterator { return resultSet.getString(fileStructInfoNdx); } + public String houseId() + throws java.sql.SQLException + { + return resultSet.getString(houseIdNdx); + } } // ************************************************************ -/*@lineinfo:user-code*//*@lineinfo:9^124*/ +/*@lineinfo:user-code*//*@lineinfo:9^140*/ @SuppressWarnings("unused") public class MediaFileDAO extends EntityBaseDAO { @@ -100,6 +108,7 @@ public class MediaFileDAO extends EntityBaseDAO { entity.setFileTypeId(iterator.fileTypeId()); entity.setRelativePath(iterator.relativePath()); entity.setFileStructInfo(iterator.fileStructInfo()); + entity.setHouseId(iterator.houseId()); if (result == null) result = new ArrayList(); @@ -112,10 +121,10 @@ public class MediaFileDAO extends EntityBaseDAO { @Override protected ResultSetIterImpl selectByKey(DefaultContext context, long id) throws SQLException{ MediaFileIter iter = null; - /*@lineinfo:generated-code*//*@lineinfo:42^2*/ + /*@lineinfo:generated-code*//*@lineinfo:43^2*/ // ************************************************************ -// #sql [context] iter = { SELECT ID, MEDIAID, STOREID, FILETYPEID, RELATIVEPATH, FILESTRUCTINFO FROM MEDIAFILE WHERE ID = :id }; +// #sql [context] iter = { SELECT ID, MEDIAID, STOREID, FILETYPEID, RELATIVEPATH, FILESTRUCTINFO, HOUSEID FROM MEDIAFILE WHERE ID = :id }; // ************************************************************ { @@ -140,17 +149,17 @@ public class MediaFileDAO extends EntityBaseDAO { // ************************************************************ -/*@lineinfo:user-code*//*@lineinfo:42^126*/ +/*@lineinfo:user-code*//*@lineinfo:43^135*/ return iter; } @Override protected ResultSetIterImpl selectByForeignKey(DefaultContext context, long id) throws SQLException{ MediaFileIter iter = null; - /*@lineinfo:generated-code*//*@lineinfo:49^2*/ + /*@lineinfo:generated-code*//*@lineinfo:50^2*/ // ************************************************************ -// #sql [context] iter = { SELECT ID, MEDIAID, STOREID, FILETYPEID, RELATIVEPATH, FILESTRUCTINFO FROM MEDIAFILE WHERE MEDIAID = :id }; +// #sql [context] iter = { SELECT ID, MEDIAID, STOREID, FILETYPEID, RELATIVEPATH, FILESTRUCTINFO, HOUSEID FROM MEDIAFILE WHERE MEDIAID = :id }; // ************************************************************ { @@ -175,17 +184,17 @@ public class MediaFileDAO extends EntityBaseDAO { // ************************************************************ -/*@lineinfo:user-code*//*@lineinfo:49^131*/ +/*@lineinfo:user-code*//*@lineinfo:50^140*/ return iter; } @Override protected ResultSetIterImpl selectAll(DefaultContext context) throws SQLException{ MediaFileIter iter = null; - /*@lineinfo:generated-code*//*@lineinfo:56^2*/ + /*@lineinfo:generated-code*//*@lineinfo:57^2*/ // ************************************************************ -// #sql [context] iter = { SELECT ID, MEDIAID, STOREID, FILETYPEID, RELATIVEPATH, FILESTRUCTINFO FROM MEDIAFILE }; +// #sql [context] iter = { SELECT ID, MEDIAID, STOREID, FILETYPEID, RELATIVEPATH, FILESTRUCTINFO, HOUSEID FROM MEDIAFILE }; // ************************************************************ { @@ -209,13 +218,13 @@ public class MediaFileDAO extends EntityBaseDAO { // ************************************************************ -/*@lineinfo:user-code*//*@lineinfo:56^111*/ +/*@lineinfo:user-code*//*@lineinfo:57^120*/ return iter; } @Override protected void delete(DefaultContext context, long id) throws SQLException{ - /*@lineinfo:generated-code*//*@lineinfo:62^2*/ + /*@lineinfo:generated-code*//*@lineinfo:63^2*/ // ************************************************************ // #sql [context] { DELETE FROM MEDIAFILE WHERE ID = :id }; @@ -243,12 +252,12 @@ public class MediaFileDAO extends EntityBaseDAO { // ************************************************************ -/*@lineinfo:user-code*//*@lineinfo:62^56*/ +/*@lineinfo:user-code*//*@lineinfo:63^56*/ } @Override protected void truncateTable(DefaultContext context) throws SQLException{ - /*@lineinfo:generated-code*//*@lineinfo:67^2*/ + /*@lineinfo:generated-code*//*@lineinfo:68^2*/ // ************************************************************ // #sql [context] { TRUNCATE TABLE MEDIAFILE DROP STORAGE IGNORE DELETE TRIGGERS IMMEDIATE }; @@ -275,7 +284,7 @@ public class MediaFileDAO extends EntityBaseDAO { // ************************************************************ -/*@lineinfo:user-code*//*@lineinfo:67^90*/ +/*@lineinfo:user-code*//*@lineinfo:68^90*/ } @Override @@ -287,11 +296,12 @@ public class MediaFileDAO extends EntityBaseDAO { long fileTypeId = obj.getFileTypeId(); String relativePath = obj.getRelativePath(); String fileStructInfo = obj.getFileStructInfo(); + String houseId = obj.getHouseId(); - /*@lineinfo:generated-code*//*@lineinfo:80^2*/ + /*@lineinfo:generated-code*//*@lineinfo:82^2*/ // ************************************************************ -// #sql [context] { UPDATE MEDIAFILE SET MEDIAID = :mediaId, STOREID = :storeId, FILETYPEID = :fileTypeId, RELATIVEPATH = :relativePath, FILESTRUCTINFO = :fileStructInfo WHERE ID = :id }; +// #sql [context] { UPDATE MEDIAFILE SET MEDIAID = :mediaId, STOREID = :storeId, FILETYPEID = :fileTypeId, RELATIVEPATH = :relativePath, FILESTRUCTINFO = :fileStructInfo, HOUSEID = :houseId WHERE ID = :id }; // ************************************************************ { @@ -308,7 +318,8 @@ public class MediaFileDAO extends EntityBaseDAO { __sJT_stmt.setLong(3, fileTypeId); __sJT_stmt.setString(4, relativePath); __sJT_stmt.setString(5, fileStructInfo); - __sJT_stmt.setLong(6, id); + __sJT_stmt.setString(6, houseId); + __sJT_stmt.setLong(7, id); __sJT_execCtx.executeUpdate(); } finally @@ -321,7 +332,7 @@ public class MediaFileDAO extends EntityBaseDAO { // ************************************************************ -/*@lineinfo:user-code*//*@lineinfo:80^184*/ +/*@lineinfo:user-code*//*@lineinfo:82^204*/ } @Override @@ -332,11 +343,12 @@ public class MediaFileDAO extends EntityBaseDAO { long fileTypeId = obj.getFileTypeId(); String relativePath = obj.getRelativePath(); String fileStructInfo = obj.getFileStructInfo(); + String houseId = obj.getHouseId(); - /*@lineinfo:generated-code*//*@lineinfo:92^2*/ + /*@lineinfo:generated-code*//*@lineinfo:95^2*/ // ************************************************************ -// #sql [context] { INSERT INTO MEDIAFILE (MEDIAID, STOREID, FILETYPEID, RELATIVEPATH, FILESTRUCTINFO) VALUES (:mediaId, :storeId, :fileTypeId, :relativePath, :fileStructInfo) }; +// #sql [context] { INSERT INTO MEDIAFILE (MEDIAID, STOREID, FILETYPEID, RELATIVEPATH, FILESTRUCTINFO, HOUSEID) VALUES (:mediaId, :storeId, :fileTypeId, :relativePath, :fileStructInfo, :houseId) }; // ************************************************************ { @@ -353,6 +365,7 @@ public class MediaFileDAO extends EntityBaseDAO { __sJT_stmt.setLong(3, fileTypeId); __sJT_stmt.setString(4, relativePath); __sJT_stmt.setString(5, fileStructInfo); + __sJT_stmt.setString(6, houseId); __sJT_execCtx.executeUpdate(); } finally @@ -365,7 +378,7 @@ public class MediaFileDAO extends EntityBaseDAO { // ************************************************************ -/*@lineinfo:user-code*//*@lineinfo:92^175*/ +/*@lineinfo:user-code*//*@lineinfo:95^194*/ } void addAll(DefaultContext context, IEntityBase entity) throws SQLException { @@ -386,7 +399,7 @@ public class MediaFileDAO extends EntityBaseDAO { void removeAll(DefaultContext context, long filterId) throws SQLException { manager.traceIn(); - /*@lineinfo:generated-code*//*@lineinfo:113^2*/ + /*@lineinfo:generated-code*//*@lineinfo:116^2*/ // ************************************************************ // #sql [context] { DELETE FROM MEDIAFILE WHERE MEDIAID = :filterId }; @@ -414,9 +427,60 @@ public class MediaFileDAO extends EntityBaseDAO { // ************************************************************ -/*@lineinfo:user-code*//*@lineinfo:113^67*/ +/*@lineinfo:user-code*//*@lineinfo:116^67*/ manager.traceOut(); } + + private MediaFileIter selectByHouseId(DefaultContext context, String houseId) throws SQLException{ + MediaFileIter iter = null; + /*@lineinfo:generated-code*//*@lineinfo:122^2*/ + +// ************************************************************ +// #sql [context] iter = { SELECT ID, MEDIAID, STOREID, FILETYPEID, RELATIVEPATH, FILESTRUCTINFO, HOUSEID FROM MEDIAFILE WHERE HOUSEID = :houseId }; +// ************************************************************ + +{ + sqlj.runtime.ConnectionContext __sJT_connCtx = context; + if (__sJT_connCtx == null) sqlj.runtime.error.RuntimeRefErrors.raise_NULL_DEFAULT_CONN_CTX(); + sqlj.runtime.ExecutionContext __sJT_execCtx = __sJT_connCtx.getExecutionContext(); + if (__sJT_execCtx == null) sqlj.runtime.error.RuntimeRefErrors.raise_NULL_EXEC_CTX(); + synchronized (__sJT_execCtx) { + sqlj.runtime.profile.RTStatement __sJT_stmt = __sJT_execCtx.registerStatement(__sJT_connCtx, MediaFileDAO_SJProfileKeys.getKey(0), 8); + try + { + __sJT_stmt.setString(1, houseId); + iter = new MediaFileIter(__sJT_execCtx.executeQuery(), __sJT_execCtx.getFetchSize(), __sJT_execCtx.getMaxRows()); + } + finally + { + __sJT_execCtx.releaseStatement(); + } + } +} + + +// ************************************************************ + +/*@lineinfo:user-code*//*@lineinfo:122^145*/ + return iter; + } + + public List getByHouseId(String houseId) { + manager.traceIn(); + List result = null; + DefaultContext context = manager.getDbContext(); + try { + ResultSetIterImpl iter = selectByHouseId(context, houseId); + result = getList(context, iter, false); + } catch (Exception e) { + throw new ItemManagerException(e); + } finally { + manager.putDbContext(context); + } + manager.traceOut(); + return result; + } + }/*@lineinfo:generated-code*/class MediaFileDAO_SJProfileKeys { private java.lang.Object[] keys; diff --git a/server/user.jobengine.osgi.db/generated/user/jobengine/db/MediaFileDAO_SJProfile0.ser b/server/user.jobengine.osgi.db/generated/user/jobengine/db/MediaFileDAO_SJProfile0.ser index 4b652a8a9d4d5943219d49ce895abffef7df2fcf..6220aa0deeaf1e320e41830073363b4b2b8b57a5 100644 GIT binary patch delta 571 zcmew&eO_S$4*vHm*I&4P@V8Cf}tix|`<8?uOQ{>PNT$f!Lzo#h>4*5oi&dq(!j z`t0J9*Rmd;?8nVEc`3UfZwUi?Mt*5=s%J_GL+<2T?5>}PqyV% zoV=DZjL~JXEwA`wX|6QJAP|d@eex`>L$VAE3_OJlbqrbvcNT;AQVa|Xos-vb$}{#% zuHeo^a`b&}HO6?53EXUOm8p{xc%5GEPRbt4+z~$;t<%AgO7~%>_LHZ>?;aS4aG;`_o2xjxuqFv zC-d^;PVVGUV2qi>CGN~xT*y$zAjQDI5W~R0!1(|F|KA`1Sr8EbmrKNu3xdm~O+LdT zZJP~J!N9=41JbAkBEsM*3NYj%3Q9prBp4VN7)lsQCRgwVa%n1rIr@aU1}kVxe#ooI RSU8DGak36y;AAcVR{;BtTU-DD diff --git a/server/user.jobengine.osgi.db/generated/user/jobengine/db/MetadataDAO_SJProfile0.ser b/server/user.jobengine.osgi.db/generated/user/jobengine/db/MetadataDAO_SJProfile0.ser index aeda13097a3cac13e652f478599772877675bee3..9af1cb3625cd524d7f3a0cfd7927e36e037fb724 100644 GIT binary patch delta 18 Zcmca@a@%AB4*vHm*c&4P?xk^n{m23`OF delta 18 Zcmca@a@%AB4*vHm)p&4P?`xd1&i1{VMT delta 18 acmZ1{zD|4t4qepGw|4*vHm)f&4P?uxBx(*24nyL delta 18 acmX>qepGw|4*vHm)n&4P@P+yF%;23`OF delta 18 acmaDQ@k(L?4*vHm)j&4P^bJODj!1-$?O delta 18 acmdlWwLxkF4*vHm*W&4P?QTmUheL{Kz4*vHrTy&4P^Ec>qFH2ABW< delta 18 acmX>heL{Kz4*vHrT)&4P@5JODhw1|R1~&iz diff --git a/server/user.jobengine.osgi.db/generated/user/jobengine/db/StoreDAO_SJProfile0.ser b/server/user.jobengine.osgi.db/generated/user/jobengine/db/StoreDAO_SJProfile0.ser index 18b9e5d5d4258b3efd4d4a97bd3f252a2589e807..fcda04769dab264c2fbdacb2be3e184834d0ac58 100644 GIT binary patch delta 18 Zcmca3aYteU4*vHrTW&4P^H+yF!>23`OF delta 18 Zcmca3aYteU4*vHrUJ&4P^0f&e_`1}^{r delta 18 ZcmbQDIz@E@4*vHrU9&4P?7yZ}Fz1_S^A delta 18 acmdlgvsGpT4 getWorkflowActions(Timestamp begin, Timestamp end) { diff --git a/server/user.jobengine.osgi.db/generated/user/jobengine/db/WorkflowActionDAO_SJProfile0.ser b/server/user.jobengine.osgi.db/generated/user/jobengine/db/WorkflowActionDAO_SJProfile0.ser index d18a0605f0920567d6be1885a99a30f0b37605e0..50b109846b77d50841b68748cde5b68d78cb66b4 100644 GIT binary patch delta 465 zcmZ{fKTASk6vod<&HL)BSC_PIe-v%W%}HA-(1sv~LWiJ2LYfNG>k?vG)Ee{^DKJ`vw!Va)AzmRZqcffJ{CJ*o&O@Xz`zYD5{eE6MvizL2aU8q$E?*$$NU;Qq zfc50aGj25DHc6|L3#Ckcub==Xoq{hB5<6OUJ-wD$T1}%N3>0Kg^HHyoXo>(noq8|I zuwpK^it=y*EDZa+NoT|U7%!kH!hDU6nC4q_MfhKHYuId{?M`tTnym5)PUZ3V77^8n z$VNmX#bV`vY7`_QX@xvVjyw|)t5NB(9}kw5FhNAG*zitq2Xk_UKXB!}8$6rP<6MH~ z3FA$Vj&3SP6kUDnyLI_amiyU?9`;jTe(hDP+GnjltcHEBq1Ol3lSysd`^>>DhJO(` Gf9?w`jd6GY delta 381 zcmZovU8B5#hmkcd;^*ysn*|v|nHdcyr?b3c?3f(J>cYsqc{XbW;&H*%`Yh zd$1=@R^pMLe1%<-(PHv{PVvdC9Q=&jlRY`5HydzpGfocY#G+#ZR7WhM%j5>08b;5_4LstLMR>CrW56sO0j|vpc?}rt zH!v_TF#iAl|92rn9fNEM150scRcbMaBgMeLupdPhBq~=>3Zf(!7#J8z7!FM~;$JcO z6~7i^KFC;0F1XHSkWK~$hFFkl6%dgDm+Qcg%bKh!pv%SsQl>pQK|l<~tPyCIfjbT) WTZLl%mdW{oC6nI@+D$eP3IqUoN@wl> diff --git a/server/user.jobengine.osgi.db/sql/alter.sql b/server/user.jobengine.osgi.db/sql/alter.sql new file mode 100644 index 00000000..a02abbab --- /dev/null +++ b/server/user.jobengine.osgi.db/sql/alter.sql @@ -0,0 +1,53 @@ +ALTER TABLE mediafile + ALTER COLUMN relativepath + SET DATA TYPE VARCHAR(1000) + +select i.houseid,i.title,h.duplicates from item i +inner join ( + select title,count(*) as duplicates from item + where title like '2%' and title like '%Echo%' + group by title +) h on i.title=h.title +order by i.created desc + +select * from item where title='2018.01.17 22:00 H�rad� Echo TV' +select * from media where itemid=30677 +select * from media where itemid=30567 + + +alter table media add column description1 CLOB(300K) +update media set description1=description +--alter table clobmedia drop column description +alter table media rename column description to descorig +alter table media rename column description1 to description +--alter table clobmedia ADD CONSTRAINT pk_clobmedia_id PRIMARY KEY (id) + + +alter table workflowaction add duration bigint default 0 +alter table workflowaction add filecreated timestamp default null + + + +CREATE VIEW DB2ADMIN.VW_ARCHIVEDMEDIA as +select +i.id as itemid, +i.houseid as itemhouseid, +i.title as itemtitle, +i.description as itemdescription, +i.created as itemcreated, +m.id as mediaid, +m.houseid as mediahouseid, +m.title as mediatitle, +m.description as mediadescription, +m.created as mediacreated, +m.archived as mediaarchived, +m.length as medialength, +itm.id as mediatypeid, +itm.name as mediatypename, +mf.houseid as mediafilehouseid +from mediafile mf +left outer join media m on (m.id = mf.mediaid) +left outer join item i on (i.id = m.itemid) +left outer join itemtype itm on (itm.id = m.itemtypeid) +where mf.storeid = 1 +go diff --git a/server/user.jobengine.osgi.db/sql/cleanup-dev.sql b/server/user.jobengine.osgi.db/sql/cleanup-dev.sql new file mode 100644 index 00000000..005576fa --- /dev/null +++ b/server/user.jobengine.osgi.db/sql/cleanup-dev.sql @@ -0,0 +1,205 @@ +--Delete wrong metadatas + +--Összes felülírt PGM (törölhetõek) +delete from mediafile where mediaid in ( + select i.mediaid from vw_items i inner join vw_itemduplicates id on i.mediafilehouseid=id.mediafilehouseid and i.archived!=id.maxarchived + where i.mediafilehouseid like '%PGM%' + order by i.mediafilehouseid, i.archived +) + +delete from media where id not in ( + select mediaid from mediafile +) + +--ELLENÕRZÉS Összes felülírt PGM (törölhetõek) +select count(*) from mediafile where mediaid in ( + select i.mediaid from vw_items i inner join vw_itemduplicates id on i.mediafilehouseid=id.mediafilehouseid and i.archived!=id.maxarchived + where i.mediafilehouseid like '%PGM%' + order by i.mediafilehouseid, i.archived +) + +select count(*) from media where id not in ( + select mediaid from mediafile +) + +select * from mediafile where mediaid in ( + select i.mediaid from vw_items i inner join vw_itemduplicates id on i.mediafilehouseid=id.mediafilehouseid and i.archived!=id.maxarchived + where i.mediafilehouseid like '%PGM%' + order by i.mediafilehouseid, i.archived +) + + +select i.itemtitle, i.mediafilehouseid, i.mediaid from vw_items i inner join vw_itemduplicates id on i.mediafilehouseid=id.mediafilehouseid and i.archived!=id.maxarchived +where i.mediafilehouseid like '%PGM%' +order by mediafilehouseid, archived + +--Összes duplikátum számossága, legújabb verzió +drop view vw_itemduplicates + +create view vw_itemduplicates as +select h.mediafilehouseid, h.maxarchived,h.duplicates from vw_items x +inner join ( + select mediafilehouseid, max(archived) as maxarchived, count(*) as duplicates from vw_items group by mediafilehouseid having count(*) > 1 order by mediafilehouseid +) h on x.mediafilehouseid=h.mediafilehouseid and x.archived = h.maxarchived + + +select i.itemtitle, i.mediafilehouseid, i.archived, id.duplicates from vw_items i inner join vw_itemduplicates id on i.mediafilehouseid=id.mediafilehouseid +where i.mediafilehouseid='1700_hirado_PGM_-_CS.MXF' +order by mediafilehouseid, archived + +-- +DROP VIEW VW_ITEMS + +CREATE VIEW VW_ITEMS as +select +i.id as itemid, +i.created as itemcreated, +it.name as itemtype, +i.houseid as itemhouseid, +i.title as itemtitle, +m.id as mediaid, +m.created as mediacreated, +m.archived, +m.length, +it.name as mediatype, +m.houseid as mediahouseid, +m.title as mediatitle, +mf.houseid as mediafilehouseid +from mediafile mf +left outer join media m on (m.id = mf.mediaid) +left outer join item i on (i.id = m.itemid) +left outer join itemtype it on (it.id = i.itemtypeid) +left outer join itemtype itn on (itn.id = m.itemtypeid) +where mf.storeid = 1 + + +SELECT count(*) FROM DB2ADMIN.WORKFLOWACTION where username is not null + +SELECT count(*) FROM media left outer join itemtype on (itemtype.id=media.itemtypeid) where itemtype.name='Visszarögzített' + + +--MEDIAFILE +select mediaid from vw_mediafiles where mediafilecount = 1 + +select mediaid from vw_mediafiles + +select * from mediafile f where f.mediaid in (select mediaid from vw_mediafiles where mediafilecount = 1) +select * from mediafile f where f.mediaid in (select mediaid from vw_mediafiles where mediafilecount = 2) +--delete from mediafile f where f.mediaid in (select mediaid from vw_mediafiles where mediafilecount = 1) +select * from mediafile f where f.mediaid in (select id from media where created > '2017-12-08') +--delete from mediafile f where f.mediaid in (select id from media where created > '2017-12-08') + + +--MEDIA +CREATE VIEW vw_mediafiles as select mediaid, count(*) as mediafilecount from mediafile group by (mediaid) +select m.id, m.title, m.created,f.mediafilecount from media m right outer join vw_mediafiles f on (f.mediaid = m.id) where m.created > '2017-12-14' and f.mediafilecount = 1 +select m.itemid, m.id, m.itemtypeid, m.title, m.created,f.mediafilecount from media m left outer join vw_mediafiles f on (f.mediaid = m.id) where f.mediafilecount is null +select m.itemid, m.id, m.itemtypeid, m.title, m.created,f.mediafilecount from media m left outer join vw_mediafiles f on (f.mediaid = m.id) where f.mediafilecount = 1 +--delete from media where id in (select m.id from media m left outer join vw_mediafiles f on (f.mediaid = m.id) where f.mediafilecount is null) + + + +--ITEM +CREATE VIEW vw_medias as select itemid, count(*) as mediacount from media group by (itemid) +select i.id, i.title, i.created, m.mediacount from item i left outer join vw_medias m on (m.itemid = i.id) where m.mediacount is null +--delete from item where id in (select i.id from item i left outer join vw_medias m on (m.itemid = i.id) where m.mediacount is null) + + + +--Összes klip +select count (*) from media where created like '2017-12%' +select count (distinct(houseid)) from media where created like '2017-12%' +select distinct(houseid) from media where created like '2017-12%' + +select count(distinct(mediahouseid)) from vw_items where mediacreated like '2018-01%' + +--Octopus ID-hez rendelt manuálisan archivált klip +select i.created, i.houseid as ihouse, i.title as ititle, m.houseid as mhouse, m.title as mtitle from item i left outer join media m on (m.itemid = i.id) +select i.created, i.houseid as ihouse, i.title as ititle, m.houseid as mhouse, m.title as mtitle from item i left outer join media m on (m.itemid = i.id) where i.title like 'Echo TV%' + +select i.created, i.houseid as ihouse, i.title as ititle, m.houseid as mhouse, m.title as mtitle from item i left outer join media m on (m.itemid = i.id) where i.title like 'Echo TV%' + +select i.created, i.houseid as ihouse, i.title as ititle, m.houseid as mhouse, m.title as mtitle from item i left outer join media m on (m.itemid = i.id) where i.title like 'Echo TV%' and m.created like '2017-12%' +select i.created, i.houseid as ihouse, i.title as ititle, m.houseid as mhouse, m.title as mtitle, mf.houseid from item i +left outer join media m on (m.itemid = i.id) +left outer join mediafile mf on (mf.mediaid = m.id) +where i.title like 'Echo TV%' and m.created like '2017-12%' + +select count(distinct(m.houseid)) from item i left outer join media m on (m.itemid = i.id) where i.title like 'Echo TV%' and m.created like '2017-12%' + +select m.houseid, count(*) as darab from item i left outer join media m on (m.itemid = i.id) where i.title like 'Echo TV%' group by m.houseid order by houseid + +select * from vw_items where itemtitle like 'Echo TV%' and mediacreated like '2018-01%' +select count(distinct(mediahouseid)) from vw_items where itemtitle like 'Echo TV%' and mediacreated like '2018-01%' + +--Automatikusan archivált bejátszók +select * from vw_items where itemtitle like '%Echo TV%' and itemtitle like '2%' and mediatitle not like '%CLN%' and mediatitle not like '%PGM%' and mediacreated like '2018-01%' +select count(distinct(mediahouseid)) from vw_items where itemtitle like '%Echo TV%' and itemtitle like '2%' and mediatitle not like '%CLN%' and mediatitle not like '%PGM%' and mediacreated like '2018-01%' + +--ID-hez nem rendelt manuálisan archivált egyéb klip (magazin vagy forgatott) +select i.created, i.houseid as ihouse, i.title as ititle, i.description as idesc, m.houseid as mhouse, m.title as mtitle, m.description as mdesc from item i left outer join media m on (m.itemid = i.id) where i.houseid like 'MC-%' and m.houseid like 'MC-%' +select i.created, i.houseid as ihouse, i.title as ititle, i.description as idesc, m.houseid as mhouse, m.title as mtitle, m.description as mdesc from item i left outer join media m on (m.itemid = i.id) where i.houseid like 'MC-%' and m.houseid like 'MC-%' and m.created like '2018-01%' + +select count(distinct(m.houseid)) from item i left outer join media m on (m.itemid = i.id) where i.houseid like 'MC-%' and m.houseid like 'MC-%' and m.created like '2018-01%' +select * from vw_items where itemhouseid like 'MC-%' and mediahouseid like 'MC-%' and mediacreated like '2018-01%' + +select * from vw_items where itemtitle like 'MC-%' and mediahouseid like 'MC-%'and mediacreated like '2018-01%' +select count(distinct(mediahouseid)) from vw_items where itemtitle like 'MC-%' and mediahouseid like 'MC-%'and mediacreated like '2018-01%' + +--Migrált archívum +select i.created, i.houseid as ihouse, i.title as ititle, m.houseid as mhouse, m.title as mtitle from item i left outer join media m on (m.itemid = i.id) where i.houseid not like 'MC-%' and m.houseid like 'MC-%' + +--Reklám ID-hez rendelt klip +select * from vw_items where mediahouseid like 'R%'and mediacreated like '2018-01%' +select count(distinct(mediahouseid)) from vw_items where mediahouseid like 'R%'and mediacreated like '2018-01%' + +--Promo ID-hez rendelt klip +select * from vw_items where mediahouseid like 'P%'and mediacreated like '2018-01%' +select count(distinct(mediahouseid)) from vw_items where mediahouseid like 'P%'and mediacreated like '2018-01%' + +--Mûsor ID-hez rendelt klip +select * from vw_items where mediafilehouseid like 'M%' and mediafilehouseid not like 'MC%' and mediacreated like '2018-01%' +select count(distinct(mediafilehouseid)) from vw_items where mediafilehouseid like 'M%' and mediafilehouseid not like 'MC%' and mediacreated like '2018-01%' + + + +select * from vw_items where mediafilehouseid not like 'M%' and mediahouseid not like 'P%' and mediahouseid not like 'R%' and mediafilehouseid not like 'MC%' and mediacreated like '2018-01%' +and itemtitle not like '%Echo TV%' and itemtitle not like '2%' and mediatitle not like '%CLN%' and mediatitle not like '%PGM%' + +select count(distinct(mediafilehouseid)) from vw_items where mediafilehouseid not like 'M%' and mediahouseid not like 'P%' and mediahouseid not like 'R%' and mediafilehouseid not like 'MC%' and mediacreated like '2018-01%' +and itemtitle not like '%Echo TV%' and itemtitle not like '2%' and mediatitle not like '%CLN%' and mediatitle not like '%PGM%' + + + + + +insert into media (id, itemid, title, ITEMTYPEID, houseid, length) values (13985, 13045, 'xxx', 2, 'xxx', 0) + +select * from media fetch first 10 rows only + +select id from item where created > '2017-12-08' + +SELECT C.TABSCHEMA, C.TABNAME, +C.COLNAME +FROM SYSCAT.TABLES AS T, +SYSCAT.COLUMNS AS C +WHERE T.TBSPACEID = 2 +AND T.TABLEID = 14 +AND C.COLNO = 0 +AND C.TABSCHEMA = T.TABSCHEMA +AND C.TABNAME = T.TABNAME + +--REPLACE--(--source-string--,--search-string--+-------------------+--) + +select * from item where id=15200 +select * from media where houseid='31678529' + +update media set description=replace(description, '
', '\r\n') where id=28573 +update media set description=replace(description, '
', '\r\n') where id=28711 + +update media set description=replace(description, '
', '\r\n') where created > '2018-01-12' and title like '%hirado%' or title like '%hirek%' +update media set itemtypeid=21 where created > '2018-01-12' and title like '%hirado%' or title like '%hirek%' +select * from media where created > '2018-01-12' and title like '%hirado%' or title like '%hirek%' +select * from itemtype + +select i.title, m.* from media m left outer join item i on (i.id=m.itemid) where m.title like '%PGM%' order by i.title diff --git a/server/user.jobengine.osgi.db/sql/cleanup.sql b/server/user.jobengine.osgi.db/sql/cleanup.sql index 5a6e698d..557fe71b 100644 --- a/server/user.jobengine.osgi.db/sql/cleanup.sql +++ b/server/user.jobengine.osgi.db/sql/cleanup.sql @@ -1,4 +1,42 @@ ---Hamis LOWRES +select * from media where id = 76952087 + +select id,archived from media where length=0 order by archived desc +--Letezo mediahousid lekerdezese +drop view vw_rundown_items +create view vw_rundown_items as +select mediaid, MEDIAFILEHOUSEID,archived, +LEFT(mediafilehouseid, LOCATE_IN_STRING(mediafilehouseid,'-',-1) - 1) filename +from VW_ITEMS +where REGEXP_LIKE(left(mediafilehouseid, length(mediafilehouseid)-4), '^[0-9]+_[0-9]+-[0-9]+$') or +REGEXP_LIKE(left(mediafilehouseid, length(mediafilehouseid)-4), '^^[0-9]+_[0-9]+-[0-9]{3}+-[0-9]+$') + +select * from vw_rundown_items where mediafilehouseid like '89214003_1%' order by filename, mediaid +fetch first 1000 rows only + +select itemid, itemhouseid, mediaid, itemtitle, mediafilehouseid, +RIGHT(mediafilehouseid ,LENGTH(mediafilehouseid) - LOCATE_IN_STRING(mediafilehouseid,'-',-1) + 1) filename +from VW_ITEMS +where REGEXP_LIKE(left(mediafilehouseid, length(mediafilehouseid)-4), '^^[0-9]+_[0-9]+-[0-9]{3}+-[0-9]+$') + + +fetch first row only + + +select itemid, itemhouseid, mediaid, itemtitle, mediafilehouseid, +LEFT(mediafilehouseid ,LENGTH(mediafilehouseid) - LOCATE_IN_STRING(mediafilehouseid,'-',-1) - 2) filename, +left(mediafilehouseid, length(mediafilehouseid)-4) +from VW_ITEMS fetch first row only + + + + + +select itemid, mediaid, replace(mediafilehouseid, concat('-', concat(itemhouseid,'.MXF')), '') filename +from vw_items where replace(mediafilehouseid, concat('-', itemhouseid), '') != mediafilehouseid +and replace(mediafilehouseid, concat('-', concat(itemhouseid,'.MXF')), '') = '75586630_1' + + +--Hamis LOWRES drop view vw_mediafiles_path create view vw_mediafiles_path as select relativepath, count(*) as count from mediafile where storeid=21 group by relativepath diff --git a/server/user.jobengine.osgi.db/sql/ftclob.sql b/server/user.jobengine.osgi.db/sql/ftclob.sql new file mode 100644 index 00000000..2a738a47 --- /dev/null +++ b/server/user.jobengine.osgi.db/sql/ftclob.sql @@ -0,0 +1,25 @@ +drop TABLE DB2ADMIN.ftclob + +CREATE TABLE DB2ADMIN.ftclob ( + id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL, + text CLOB(200000) LOGGED NOT COMPACT, + primary key (id) + ) +IN USERSPACE1 +COMPRESS NO +GO + + + + +insert into ftclob (text) values (select description from media where id = '40186') + +update ftclob set text = concat(text, (select description from media where id = '40186')) + +update ftclob set text = concat(text, ' Vásáry Dani ') + +SELECT id FROM ftclob WHERE CONTAINS(text, 'vásáry + dani') + + +select length(TEXT) from ftclob + diff --git a/server/user.jobengine.osgi.db/sql/stat-rd.sql b/server/user.jobengine.osgi.db/sql/stat-rd.sql index 8bc191f3..b6594d26 100644 --- a/server/user.jobengine.osgi.db/sql/stat-rd.sql +++ b/server/user.jobengine.osgi.db/sql/stat-rd.sql @@ -1,14 +1,113 @@ +DROP VIEW DB2ADMIN.VW_ITEMS +go + +CREATE VIEW "DB2ADMIN"."VW_ITEMS" as +select +i.id as itemid, +i.created as itemcreated, +i.houseid as itemhouseid, +i.title as itemtitle, +m.id as mediaid, +m.created as mediacreated, +m.archived, +m.length, +it.name as mediatype, +m.houseid as mediahouseid, +m.title as mediatitle, +mf.id as mediafileid, +mf.houseid as mediafilehouseid +from mediafile mf +left outer join media m on (m.id = mf.mediaid) +left outer join item i on (i.id = m.itemid) +left outer join itemtype it on (it.id = m.itemtypeid) +where mf.storeid = 1 +go + +DROP VIEW DB2ADMIN.VW_ITEMS_LOWRES +go + +CREATE VIEW DB2ADMIN.VW_ITEMS_LOWRES as +select +i.id as itemid, +i.created as itemcreated, +it.name as itemtype, +i.houseid as itemhouseid, +i.title as itemtitle, +m.id as mediaid, +m.created as mediacreated, +m.archived, +m.length, +it.name as mediatype, +m.houseid as mediahouseid, +m.title as mediatitle, +mf.houseid as mediafilehouseid, +mf.relativepath +from mediafile mf +left outer join media m on (m.id = mf.mediaid) +left outer join item i on (i.id = m.itemid) +left outer join itemtype it on (it.id = i.itemtypeid) +left outer join itemtype itn on (itn.id = m.itemtypeid) +where mf.storeid = 21 +GO + +select itemtitle, mediafilehouseid, length, relativepath from vw_items_lowres +fetch first 10 rows only +go + +drop view vw_items_rd +go create view vw_items_rd as select *, replace(mediafilehouseid, concat('-', itemhouseid), '') as filename from vw_items where itemtitle like '%Echo TV%' and itemtitle like '2%' and mediatitle not like '%CLN%' and mediatitle not like '%PGM%' +go + + +select itemtitle, mediafilehouseid, filename, length, relativepath from vw_items_lowres where filename in ( + select filename, count(*) from vw_items_rd group by filename having count(*) > 1 +) +go + +drop view vw_items_rd_dup +go +create view vw_items_rd_dup as + select filename, count(*) as filecount from vw_items_rd group by filename having count(*) > 1 +go + +drop view vw_items_rd_lh +go + +create view vw_items_rd_lh as +select l.itemid, l.mediaid, l.mediatitle, h.mediafilehouseid, replace(h.mediafilehouseid, concat('-', h.itemhouseid), '') as filename, h.mediafileid, l.relativepath + from vw_items_lowres l, vw_items h where l.mediaid = h.mediaid +go + +select * from vw_items_rd_lh lh, vw_items_rd_dup d where lh.FILENAME = d.FILENAME +order by d.filecount, lh.filename +fetch first 10 rows only + + +select lh.* from vw_items_rd_lh lh, vw_items_rd_dup d where lh.FILENAME = d.FILENAME + and d.filecount=22 +order by d.filecount, lh.filename + + +select * from vw_items_rd_lh lh, vw_items_rd_dup d where lh.FILENAME = d.FILENAME +order by lh.filename, d.filecount +fetch first 1000 rows only + +select lh.itemid, lh.mediaid, d.filecount, d.filename, lh.relativepath from vw_items_rd_lh lh, vw_items_rd_dup d where lh.FILENAME = d.FILENAME +order by lh.filename, d.filecount +fetch first 1000 rows only + + +select * from vw_items_rd_dup where filename ='29410790_2.MXF' order by filecount desc +select * from vw_items_rd where filename ='29410790_2.MXF' +select l.itemid, l.mediaid, l.mediatitle, h.mediafilehouseid, + from vw_items_lowres l where h.mediafilehouseid + +select mediafileid, mediafilehouseid, relativepath from vw_items_rd_lh where filename ='29410790_2.MXF' -select * from vw_items_rd -select count(*) from vw_items_rd +select replace(mediafilehouseid, concat('-', concat(itemhouseid,'.MXF')), '') filename from vw_items where replace(mediafilehouseid, concat('-', itemhouseid), '') !=mediafilehouseid -select filename, count(*) as c from vw_items_rd group by filename order by c desc -select distinct(concat('''', concat(mediafilehouseid,''','))) from vw_items_rd -select itemtitle, mediafilehouseid, filename, archived from vw_items_rd where mediafilehouseid in ( - select distinct(mediafilehouseid) from vw_items_rd -) order by filename -- 2.54.0