From 62020fc68c12726683f396095ba23755ab1ea0c1 Mon Sep 17 00:00:00 2001 From: =?utf8?q?V=C3=A1s=C3=A1ry=20D=C3=A1niel?= Date: Wed, 16 May 2018 08:23:28 +0000 Subject: [PATCH] git-tfs-id: [http://tfs.userrendszerhaz.hu:8080/tfs/DefaultCollection]$/MediaCube;C31092 --- .../-configuration-nexio-ingest.json | 2 +- ...-studio.json => configuration-studio.json} | 0 client/Maestro/Maestro.csproj | 3 +- client/Maestro/MaestroForm.Designer.cs | 68 +++++--- client/Maestro/MaestroForm.Source.cs | 15 +- client/Maestro/MaestroForm.cs | 28 +++ client/Maestro/MaestroForm.resx | 4 +- client/Maestro/Properties/AssemblyInfo.cs | 4 +- .../Maestro/Properties/Resources.Designer.cs | 12 +- client/Maestro/Properties/Resources.resx | 3 + .../Resources/ic_cached_black_18dp.png | Bin 0 -> 206 bytes client/Maestro/Sources/FileSystemSource.cs | 2 +- client/Maestro/Sources/ISource.cs | 1 + client/Maestro/Sources/NexioRESTSource.cs | 7 +- .../Controls/DoubleClickTreeView.cs | 5 +- .../run-mediacube-server-bsh.launch | 2 +- .../jobtemplates/batch-retrieve-ondemand.xml | 16 -- .../sql/5-CreateFTIndex.db2 | 28 +++ .../sql/mediadescription-merge.sql | 163 ++++++++++++++++++ .../src/user/jobengine/db/ItemDAOEx.java | 78 +++++---- .../pages/searchitems.zul | 27 +-- .../resources/i3-label_hu.properties | 2 +- .../zk/model/ShortDateConverter.java | 23 +++ .../osgi/ws/nexio/NexioWSServlet.java | 3 +- 24 files changed, 391 insertions(+), 105 deletions(-) rename client/Maestro/Configuration/{-configuration-studio.json => configuration-studio.json} (100%) create mode 100644 client/Maestro/Resources/ic_cached_black_18dp.png create mode 100644 server/user.jobengine.osgi.db/sql/mediadescription-merge.sql create mode 100644 server/user.jobengine.osgi.server/src/user/jobengine/zk/model/ShortDateConverter.java diff --git a/client/Maestro/Configuration/-configuration-nexio-ingest.json b/client/Maestro/Configuration/-configuration-nexio-ingest.json index 2686a69c..fbb58f35 100644 --- a/client/Maestro/Configuration/-configuration-nexio-ingest.json +++ b/client/Maestro/Configuration/-configuration-nexio-ingest.json @@ -1,6 +1,6 @@ { "title": "NEXIO betöltő", - "active": false, + "active": true, "startInTray": false, "enableCustomMetadataId": true, "player": { diff --git a/client/Maestro/Configuration/-configuration-studio.json b/client/Maestro/Configuration/configuration-studio.json similarity index 100% rename from client/Maestro/Configuration/-configuration-studio.json rename to client/Maestro/Configuration/configuration-studio.json diff --git a/client/Maestro/Maestro.csproj b/client/Maestro/Maestro.csproj index 2335d5e7..619c9e42 100644 --- a/client/Maestro/Maestro.csproj +++ b/client/Maestro/Maestro.csproj @@ -333,7 +333,7 @@ Always - + Always @@ -394,6 +394,7 @@ + diff --git a/client/Maestro/MaestroForm.Designer.cs b/client/Maestro/MaestroForm.Designer.cs index 62e3f21b..f3a3bec3 100644 --- a/client/Maestro/MaestroForm.Designer.cs +++ b/client/Maestro/MaestroForm.Designer.cs @@ -43,8 +43,9 @@ namespace Maestro { this.treeFolders = new MaestroShared.Controls.DoubleClickTreeView(); this.ilFolders = new System.Windows.Forms.ImageList(this.components); this.pSourceFilter = new System.Windows.Forms.TableLayoutPanel(); + this.pictureBox1 = new System.Windows.Forms.PictureBox(); this.txtSourceFilter = new System.Windows.Forms.TextBox(); - this.picClearFilter = new System.Windows.Forms.PictureBox(); + this.picRefreshFileList = new System.Windows.Forms.PictureBox(); this.pSourceDisplay = new System.Windows.Forms.TableLayoutPanel(); this.tsSource = new System.Windows.Forms.ToolStrip(); this.btnShowFolders = new System.Windows.Forms.ToolStripButton(); @@ -98,7 +99,8 @@ namespace Maestro { ((System.ComponentModel.ISupportInitialize)(this.dgSource)).BeginInit(); ((System.ComponentModel.ISupportInitialize)(this.bindingSource)).BeginInit(); this.pSourceFilter.SuspendLayout(); - ((System.ComponentModel.ISupportInitialize)(this.picClearFilter)).BeginInit(); + ((System.ComponentModel.ISupportInitialize)(this.pictureBox1)).BeginInit(); + ((System.ComponentModel.ISupportInitialize)(this.picRefreshFileList)).BeginInit(); this.pSourceDisplay.SuspendLayout(); this.tsSource.SuspendLayout(); ((System.ComponentModel.ISupportInitialize)(this.scOperations)).BeginInit(); @@ -229,46 +231,60 @@ namespace Maestro { // pSourceFilter // this.pSourceFilter.AutoSize = true; - this.pSourceFilter.ColumnCount = 2; + this.pSourceFilter.ColumnCount = 3; this.pSourceFilter.ColumnStyles.Add(new System.Windows.Forms.ColumnStyle(System.Windows.Forms.SizeType.Percent, 100F)); this.pSourceFilter.ColumnStyles.Add(new System.Windows.Forms.ColumnStyle()); + this.pSourceFilter.ColumnStyles.Add(new System.Windows.Forms.ColumnStyle()); + this.pSourceFilter.Controls.Add(this.pictureBox1, 0, 0); this.pSourceFilter.Controls.Add(this.txtSourceFilter, 0, 0); - this.pSourceFilter.Controls.Add(this.picClearFilter, 1, 0); + this.pSourceFilter.Controls.Add(this.picRefreshFileList, 2, 0); this.pSourceFilter.Dock = System.Windows.Forms.DockStyle.Top; this.pSourceFilter.Location = new System.Drawing.Point(10, 23); this.pSourceFilter.Margin = new System.Windows.Forms.Padding(0); this.pSourceFilter.Name = "pSourceFilter"; - this.pSourceFilter.Padding = new System.Windows.Forms.Padding(0, 0, 0, 4); this.pSourceFilter.RowCount = 1; this.pSourceFilter.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Percent, 100F)); - this.pSourceFilter.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Absolute, 20F)); this.pSourceFilter.Size = new System.Drawing.Size(330, 24); this.pSourceFilter.TabIndex = 17; // + // pictureBox1 + // + this.pictureBox1.BackColor = System.Drawing.Color.Transparent; + this.pictureBox1.Cursor = System.Windows.Forms.Cursors.Hand; + this.pictureBox1.Image = ((System.Drawing.Image)(resources.GetObject("pictureBox1.Image"))); + this.pictureBox1.ImageLocation = ""; + this.pictureBox1.Location = new System.Drawing.Point(284, 2); + this.pictureBox1.Margin = new System.Windows.Forms.Padding(2); + this.pictureBox1.Name = "pictureBox1"; + this.pictureBox1.Size = new System.Drawing.Size(20, 20); + this.pictureBox1.SizeMode = System.Windows.Forms.PictureBoxSizeMode.StretchImage; + this.pictureBox1.TabIndex = 18; + this.pictureBox1.TabStop = false; + // // txtSourceFilter // this.txtSourceFilter.Dock = System.Windows.Forms.DockStyle.Top; - this.txtSourceFilter.Location = new System.Drawing.Point(0, 0); - this.txtSourceFilter.Margin = new System.Windows.Forms.Padding(0); + this.txtSourceFilter.Location = new System.Drawing.Point(2, 2); + this.txtSourceFilter.Margin = new System.Windows.Forms.Padding(2); this.txtSourceFilter.Name = "txtSourceFilter"; - this.txtSourceFilter.Size = new System.Drawing.Size(310, 20); + this.txtSourceFilter.Size = new System.Drawing.Size(278, 20); this.txtSourceFilter.TabIndex = 16; this.txtSourceFilter.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtSourceFilter_KeyDown); // - // picClearFilter - // - this.picClearFilter.BackColor = System.Drawing.Color.Transparent; - this.picClearFilter.Cursor = System.Windows.Forms.Cursors.Hand; - this.picClearFilter.Image = ((System.Drawing.Image)(resources.GetObject("picClearFilter.Image"))); - this.picClearFilter.ImageLocation = ""; - this.picClearFilter.Location = new System.Drawing.Point(310, 0); - this.picClearFilter.Margin = new System.Windows.Forms.Padding(0); - this.picClearFilter.Name = "picClearFilter"; - this.picClearFilter.Size = new System.Drawing.Size(20, 20); - this.picClearFilter.SizeMode = System.Windows.Forms.PictureBoxSizeMode.StretchImage; - this.picClearFilter.TabIndex = 17; - this.picClearFilter.TabStop = false; - this.picClearFilter.Click += new System.EventHandler(this.picClearFilter_Click); + // picRefreshFileList + // + this.picRefreshFileList.BackColor = System.Drawing.Color.Transparent; + this.picRefreshFileList.Cursor = System.Windows.Forms.Cursors.Hand; + this.picRefreshFileList.Image = global::Maestro.Properties.Resources.ic_refresh_black_24dp_1x; + this.picRefreshFileList.ImageLocation = ""; + this.picRefreshFileList.Location = new System.Drawing.Point(308, 2); + this.picRefreshFileList.Margin = new System.Windows.Forms.Padding(2); + this.picRefreshFileList.Name = "picRefreshFileList"; + this.picRefreshFileList.Size = new System.Drawing.Size(20, 20); + this.picRefreshFileList.SizeMode = System.Windows.Forms.PictureBoxSizeMode.StretchImage; + this.picRefreshFileList.TabIndex = 17; + this.picRefreshFileList.TabStop = false; + this.picRefreshFileList.Click += new System.EventHandler(this.picRefreshFileList_Click); // // pSourceDisplay // @@ -937,7 +953,8 @@ namespace Maestro { ((System.ComponentModel.ISupportInitialize)(this.bindingSource)).EndInit(); this.pSourceFilter.ResumeLayout(false); this.pSourceFilter.PerformLayout(); - ((System.ComponentModel.ISupportInitialize)(this.picClearFilter)).EndInit(); + ((System.ComponentModel.ISupportInitialize)(this.pictureBox1)).EndInit(); + ((System.ComponentModel.ISupportInitialize)(this.picRefreshFileList)).EndInit(); this.pSourceDisplay.ResumeLayout(false); this.pSourceDisplay.PerformLayout(); this.tsSource.ResumeLayout(false); @@ -1017,7 +1034,7 @@ namespace Maestro { private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn2; private System.Windows.Forms.TableLayoutPanel pSourceFilter; private System.Windows.Forms.TextBox txtSourceFilter; - private System.Windows.Forms.PictureBox picClearFilter; + private System.Windows.Forms.PictureBox picRefreshFileList; private System.Windows.Forms.Button btnLookupMetadata; private System.Windows.Forms.ToolStrip tsMetadata; private System.Windows.Forms.ToolStripButton btnEditMetadata; @@ -1038,6 +1055,7 @@ namespace Maestro { private DataGridViewTextBoxColumn columnKillDate; private DataGridViewTextBoxColumn Message; private Commons.DataGridViewProgressColumn dataGridViewProgressColumn1; + private PictureBox pictureBox1; } } diff --git a/client/Maestro/MaestroForm.Source.cs b/client/Maestro/MaestroForm.Source.cs index 2be7542f..885fcdf3 100644 --- a/client/Maestro/MaestroForm.Source.cs +++ b/client/Maestro/MaestroForm.Source.cs @@ -130,22 +130,28 @@ namespace Maestro { } if (folders == null) return; + + Array.Sort(folders, StringComparer.InvariantCultureIgnoreCase); treeFolders.BeginUpdate(); foreach (var folder in folders) { TreeNode folderNode = parent.Nodes.Add(GetLastSegment(folder)); folderNode.Nodes.Add(".."); } - treeFolders.Sort(); + //treeFolders.Sort(); parent.EnsureVisible(); treeFolders.EndUpdate(); } private void OnBeforeExpandFolder(object sender, TreeViewCancelEventArgs e) { - if (e.Node.Nodes.Count != 1 || !"..".Equals(e.Node.Nodes[0].Text)) + logger.Info(" Nothing to do"); return; + } e.Node.Nodes.Clear(); Uri address = new Uri(Path.Combine(Configuration.Source.Local.Address.LocalPath, GetPath(e.Node))); AddFolders(address, e.Node); + logger.Info("OnBeforeExpandFolder>"); } private void OnDoubleClickFolder(object sender, TreeNodeMouseClickEventArgs args) { @@ -432,6 +438,11 @@ namespace Maestro { ResetSourceSearch(); } + private void picRefreshFileList_Click(object sender, EventArgs e) { + ISource source = bindingSource.DataSource as ISource; + source.Reset(); + } + private void ResetSourceSearch() { txtSourceFilter.Text = null; bindingSource.Filter = null; diff --git a/client/Maestro/MaestroForm.cs b/client/Maestro/MaestroForm.cs index a5742abb..1356429f 100644 --- a/client/Maestro/MaestroForm.cs +++ b/client/Maestro/MaestroForm.cs @@ -288,5 +288,33 @@ namespace Maestro { if (e.Node != null) e.Node.Expand(); } + + //private void OnEditMetadata(object sender, EventArgs e) { + + //} + + //private void OnDefineSegments(object sender, EventArgs e) { + + //} + + //private void OnLookupByMetadata(object sender, EventArgs e) { + + //} + + //private void OnMetadataIDChanged(object sender, EventArgs e) { + + //} + + //private void OnSelectedMetadataKeyDown(object sender, KeyEventArgs e) { + + //} + + //private void OnTargetPanelsMouseClick(object sender, MouseEventArgs e) { + + //} + + //private void OnExecuteClick(object sender, EventArgs e) { + + //} } } diff --git a/client/Maestro/MaestroForm.resx b/client/Maestro/MaestroForm.resx index 6f3dee41..a38e785d 100644 --- a/client/Maestro/MaestroForm.resx +++ b/client/Maestro/MaestroForm.resx @@ -128,7 +128,7 @@ AAEAAAD/////AQAAAAAAAAAMAgAAAFdTeXN0ZW0uV2luZG93cy5Gb3JtcywgVmVyc2lvbj00LjAuMC4w LCBDdWx0dXJlPW5ldXRyYWwsIFB1YmxpY0tleVRva2VuPWI3N2E1YzU2MTkzNGUwODkFAQAAACZTeXN0 ZW0uV2luZG93cy5Gb3Jtcy5JbWFnZUxpc3RTdHJlYW1lcgEAAAAERGF0YQcCAgAAAAkDAAAADwMAAAAS - CAAAAk1TRnQBSQFMAgEBAgEAATgBAQE4AQEBEAEAARABAAT/AQkBAAj/AUIBTQE2AQQGAAE2AQQCAAEo + CAAAAk1TRnQBSQFMAgEBAgEAAVgBAQFYAQEBEAEAARABAAT/AQkBAAj/AUIBTQE2AQQGAAE2AQQCAAEo AwABQAMAARADAAEBAQABCAYAAQQYAAGAAgABgAMAAoABAAGAAwABgAEAAYABAAKAAgADwAEAAcAB3AHA AQAB8AHKAaYBAAEzBQABMwEAATMBAAEzAQACMwIAAxYBAAMcAQADIgEAAykBAANVAQADTQEAA0IBAAM5 AQABgAF8Af8BAAJQAf8BAAGTAQAB1gEAAf8B7AHMAQABxgHWAe8BAAHWAucBAAGQAakBrQIAAf8BMwMA @@ -166,7 +166,7 @@ - + iVBORw0KGgoAAAANSUhEUgAAABgAAAAYCAYAAADgdz34AAAABGdBTUEAALGPC/xhBQAAAJ9JREFUSEvt lFEKgzAQBXOJ9hC2vf+XHsK257FvAgtrkLjB9EsHHhow86KRpIvz8lJG5ZZHde7KpDzyKAgTFuWt1EqQ diff --git a/client/Maestro/Properties/AssemblyInfo.cs b/client/Maestro/Properties/AssemblyInfo.cs index 88f47f37..898b06c3 100644 --- a/client/Maestro/Properties/AssemblyInfo.cs +++ b/client/Maestro/Properties/AssemblyInfo.cs @@ -32,5 +32,5 @@ using System.Runtime.InteropServices; // You can specify all the values or you can default the Build and Revision Numbers // by using the '*' as shown below: // [assembly: AssemblyVersion("1.0.*")] -[assembly: AssemblyVersion("2.0.7.8")] -[assembly: AssemblyFileVersion("2.0.7.8")] +[assembly: AssemblyVersion("2.0.8.0")] +[assembly: AssemblyFileVersion("2.0.8.0")] diff --git a/client/Maestro/Properties/Resources.Designer.cs b/client/Maestro/Properties/Resources.Designer.cs index 382c721c..180d0c30 100644 --- a/client/Maestro/Properties/Resources.Designer.cs +++ b/client/Maestro/Properties/Resources.Designer.cs @@ -19,7 +19,7 @@ namespace Maestro.Properties { // class via a tool like ResGen or Visual Studio. // To add or remove a member, edit your .ResX file then rerun ResGen // with the /str option, or rebuild your VS project. - [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Resources.Tools.StronglyTypedResourceBuilder", "4.0.0.0")] + [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Resources.Tools.StronglyTypedResourceBuilder", "15.0.0.0")] [global::System.Diagnostics.DebuggerNonUserCodeAttribute()] [global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()] internal class Resources { @@ -70,6 +70,16 @@ namespace Maestro.Properties { } } + /// + /// Looks up a localized resource of type System.Drawing.Bitmap. + /// + internal static System.Drawing.Bitmap ic_cached_black_18dp { + get { + object obj = ResourceManager.GetObject("ic_cached_black_18dp", resourceCulture); + return ((System.Drawing.Bitmap)(obj)); + } + } + /// /// Looks up a localized resource of type System.Drawing.Bitmap. /// diff --git a/client/Maestro/Properties/Resources.resx b/client/Maestro/Properties/Resources.resx index 9ba30307..2775772d 100644 --- a/client/Maestro/Properties/Resources.resx +++ b/client/Maestro/Properties/Resources.resx @@ -172,4 +172,7 @@ ..\Resources\ic_refresh_black_24dp_1x.png;System.Drawing.Bitmap, System.Drawing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a + + ..\Resources\ic_cached_black_18dp.png;System.Drawing.Bitmap, System.Drawing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a + \ No newline at end of file diff --git a/client/Maestro/Resources/ic_cached_black_18dp.png b/client/Maestro/Resources/ic_cached_black_18dp.png new file mode 100644 index 0000000000000000000000000000000000000000..f8d3a3904a0dba99a3ec307fd07090be5d38232e GIT binary patch literal 206 zcmV;<05SiGP)u8Yt`SN5Xa&MBgPM*DcbtBlCe0#9yD2HX|Vxux>!;)l8y(K4c$;!6TluUMNXS1Sg zVWHDgZzRIO}AiJCT(~pMK4H2W!{z4@EFC{AVA$-v9sr07*qo IM6N<$f&xKV?EnA( literal 0 HcmV?d00001 diff --git a/client/Maestro/Sources/FileSystemSource.cs b/client/Maestro/Sources/FileSystemSource.cs index 5f95d306..7d6682da 100644 --- a/client/Maestro/Sources/FileSystemSource.cs +++ b/client/Maestro/Sources/FileSystemSource.cs @@ -149,7 +149,7 @@ namespace Maestro.Sources { return true; } - private void Reset() { + public void Reset() { Clear(); cache = null; Shutdown(); diff --git a/client/Maestro/Sources/ISource.cs b/client/Maestro/Sources/ISource.cs index 23801d7d..433662db 100644 --- a/client/Maestro/Sources/ISource.cs +++ b/client/Maestro/Sources/ISource.cs @@ -6,5 +6,6 @@ namespace Maestro.Sources { public interface ISource : IBindingListView { DataGridViewColumn[] Columns { get; } void Startup(Uri address); + void Reset(); } } \ No newline at end of file diff --git a/client/Maestro/Sources/NexioRESTSource.cs b/client/Maestro/Sources/NexioRESTSource.cs index 6f0416b2..c13bee02 100644 --- a/client/Maestro/Sources/NexioRESTSource.cs +++ b/client/Maestro/Sources/NexioRESTSource.cs @@ -335,6 +335,11 @@ namespace Maestro.Sources { InnerStartup(); } - + public void Reset() { + if (nexioAPI != null) + nexioAPI.Close(); + Clear(); + InnerStartup(); + } } } diff --git a/client/MaestroShared/Controls/DoubleClickTreeView.cs b/client/MaestroShared/Controls/DoubleClickTreeView.cs index 34475d1e..f736de40 100644 --- a/client/MaestroShared/Controls/DoubleClickTreeView.cs +++ b/client/MaestroShared/Controls/DoubleClickTreeView.cs @@ -1,10 +1,11 @@ -using System; +using NLog; +using System; using System.Windows.Forms; namespace MaestroShared.Controls { public class DoubleClickTreeView : TreeView { public delegate void OnNodeDoubleClick(object sender, TreeNodeMouseClickEventArgs args); - + private static Logger logger = LogManager.GetCurrentClassLogger(); public OnNodeDoubleClick NodeDoubleClick; protected override void WndProc(ref Message m) { diff --git a/server/-configuration/run-mediacube-server-bsh.launch b/server/-configuration/run-mediacube-server-bsh.launch index ec4eca74..c987230e 100644 --- a/server/-configuration/run-mediacube-server-bsh.launch +++ b/server/-configuration/run-mediacube-server-bsh.launch @@ -19,7 +19,7 @@ - + diff --git a/server/user.jobengine.executors/jobtemplates/batch-retrieve-ondemand.xml b/server/user.jobengine.executors/jobtemplates/batch-retrieve-ondemand.xml index 14cebcde..141af1e4 100644 --- a/server/user.jobengine.executors/jobtemplates/batch-retrieve-ondemand.xml +++ b/server/user.jobengine.executors/jobtemplates/batch-retrieve-ondemand.xml @@ -2,27 +2,14 @@ - - - - - - - - - - - - - @@ -32,9 +19,6 @@ - - - diff --git a/server/user.jobengine.osgi.db/sql/5-CreateFTIndex.db2 b/server/user.jobengine.osgi.db/sql/5-CreateFTIndex.db2 index 4b9fcfd1..5783d24a 100644 --- a/server/user.jobengine.osgi.db/sql/5-CreateFTIndex.db2 +++ b/server/user.jobengine.osgi.db/sql/5-CreateFTIndex.db2 @@ -37,3 +37,31 @@ db2ts "update index media_houseid1 for text connect to mc" db2ts "drop index media_title1 for text connect to mc" db2ts "drop index media_desc1 for text connect to mc" db2ts "drop index media_houseid1 for text connect to mc" + + +DROP TABLE DB2ADMIN.MEDIADESCRIPTION + +CREATE TABLE DB2ADMIN.MEDIADESCRIPTION ( + ID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL, + ITEMID BIGINT NOT NULL, + MEDIAID BIGINT NOT NULL, + MEDIAFILEID BIGINT NOT NULL, + DESCRIPTION CLOB(400000) NOT LOGGED COMPACT, + CONSTRAINT PK_MEDIADESCRIPTION PRIMARY KEY(ID) +) + +ALTER TABLE DB2ADMIN.MEDIADESCRIPTION +ADD CONSTRAINT FK_MEDIADESCRIPTION_ITEMID + FOREIGN KEY(ITEMID) + REFERENCES DB2ADMIN.ITEM(ID) +ADD CONSTRAINT FK_MEDIADESCRIPTION_MEDIAID + FOREIGN KEY(MEDIAID) + REFERENCES DB2ADMIN.MEDIA(ID) +ADD CONSTRAINT FK_MEDIADESCRIPTION_MEDIAFILEID + FOREIGN KEY(MEDIAFILEID) + REFERENCES DB2ADMIN.MEDIAFILE(ID) + +db2ts "drop index media_description for text connect to mc" +db2ts "create index media_description for text on MEDIADESCRIPTION(DESCRIPTION) UPDATE FREQUENCY D(*) H(*) M(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59) UPDATE MINIMUM 1 connect to mc" +db2ts "update index media_description for text connect to mc" + \ No newline at end of file diff --git a/server/user.jobengine.osgi.db/sql/mediadescription-merge.sql b/server/user.jobengine.osgi.db/sql/mediadescription-merge.sql new file mode 100644 index 00000000..9bbceebf --- /dev/null +++ b/server/user.jobengine.osgi.db/sql/mediadescription-merge.sql @@ -0,0 +1,163 @@ +select * from media where archived is null +update media set archived = modified where archived is null + +--Extra view for FT update +CREATE VIEW DB2ADMIN.VW_ITEMS_DESCRIPTION as +select +i.id as itemid, +i.created as itemcreated, +i.houseid as itemhouseid, +i.title as itemtitle, +i.description as itemdescription, +m.id as mediaid, +m.created as mediacreated, +m.archived, +m.length, +m.description as mediadescription, +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 + +--Manual update +insert into mediadescription (itemid, mediaid, mediafileid, description) +select + itemid, + mediaid, + mediafileid, + COALESCE(hundate(archived), '') concat ' ' + concat COALESCE(defdate(archived), '') concat ' ' + concat COALESCE(itemhouseid, '') concat ' ' + concat COALESCE(itemtitle, '') concat ' ' + concat COALESCE(itemdescription,'') concat ' ' + concat COALESCE(mediahouseid, '') concat ' ' + concat COALESCE(mediatitle, '') concat ' ' + concat COALESCE(mediadescription, '') concat ' ' + concat COALESCE(mediafilehouseid, '') +from vw_items_description +where mediafileid not in (select mediafileid from mediadescription) + +select count(*) from vw_items_description where mediafileid not in (select mediafileid from mediadescription) +select * from vw_items_description where mediafileid not in (select mediafileid from mediadescription) + +--Insert trigger +create trigger trg_mediafile_after_insert after insert +on mediafile +referencing new as n +for each row +when (n.storeid = 1) +begin atomic + insert into mediadescription (itemid, mediaid, mediafileid, description) + select + itemid, + mediaid, + mediafileid, + COALESCE(hundate(archived), '') concat ' ' + concat COALESCE(defdate(archived), '') concat ' ' + concat COALESCE(itemhouseid, '') concat ' ' + concat COALESCE(itemtitle, '') concat ' ' + concat COALESCE(itemdescription,'') concat ' ' + concat COALESCE(mediahouseid, '') concat ' ' + concat COALESCE(mediatitle, '') concat ' ' + concat COALESCE(mediadescription, '') concat ' ' + concat COALESCE(mediafilehouseid, '') + from vw_items_description + where mediafileid = n.id; +end + + +--Extra FT table +DROP TABLE DB2ADMIN.MEDIADESCRIPTION + +CREATE TABLE DB2ADMIN.MEDIADESCRIPTION ( + ID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL, + ITEMID BIGINT NOT NULL, + MEDIAID BIGINT NOT NULL, + MEDIAFILEID BIGINT NOT NULL, + DESCRIPTION CLOB(400000) NOT LOGGED COMPACT, + CONSTRAINT PK_MEDIADESCRIPTION PRIMARY KEY(ID) +) + +ALTER TABLE DB2ADMIN.MEDIADESCRIPTION +ADD CONSTRAINT FK_MEDIADESCRIPTION_ITEMID + FOREIGN KEY(ITEMID) + REFERENCES DB2ADMIN.ITEM(ID) +ADD CONSTRAINT FK_MEDIADESCRIPTION_MEDIAID + FOREIGN KEY(MEDIAID) + REFERENCES DB2ADMIN.MEDIA(ID) +ADD CONSTRAINT FK_MEDIADESCRIPTION_MEDIAFILEID + FOREIGN KEY(MEDIAFILEID) + REFERENCES DB2ADMIN.MEDIAFILE(ID) + +--Timestamp to formatted string +create function hundate(TS timestamp) +returns varchar(10) +return +with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as +( + select + substr( digits (day(TS)),9), + substr( digits (month(TS)),9) , + rtrim(char(year(TS))) , + substr( digits (hour(TS)),9), + substr( digits (minute(TS)),9), + substr( digits (second(TS)),9), + rtrim(char(microsecond(TS))) + from sysibm.sysdummy1 + ) +select yyyy || '.' || mm || '.' || dd from tmp + +create function defdate(TS timestamp) +returns varchar(10) +return +with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as +( + select + substr( digits (day(TS)),9), + substr( digits (month(TS)),9) , + rtrim(char(year(TS))) , + substr( digits (hour(TS)),9), + substr( digits (minute(TS)),9), + substr( digits (second(TS)),9), + rtrim(char(microsecond(TS))) + from sysibm.sysdummy1 + ) +select yyyy || mm || dd from tmp + + +SELECT hundate(TIMESTAMP ('2002-10-20-12.00.00.000000')) FROM SYSIBM.SYSDUMMY1 +SELECT defdate(TIMESTAMP ('2002-10-20-12.00.00.000000')) FROM SYSIBM.SYSDUMMY1 +SELECT hundate(null) FROM SYSIBM.SYSDUMMY1 + + +--TESZT +drop table test1 +create table test1 ( + value varchar(10) +) +drop table test2 +create table test2 ( + value varchar(10) +) + + +drop trigger trg_test1 +create trigger trg_test1 after insert +on test1 +referencing new as n +for each row +when (n.value='ccc') +begin atomic + insert into test2 (value) values (n.value); +end + +insert into test1 (value) values ('ccc'),('ddd') + +select * from test2 + diff --git a/server/user.jobengine.osgi.db/src/user/jobengine/db/ItemDAOEx.java b/server/user.jobengine.osgi.db/src/user/jobengine/db/ItemDAOEx.java index bdbd2a9b..88d49d4e 100644 --- a/server/user.jobengine.osgi.db/src/user/jobengine/db/ItemDAOEx.java +++ b/server/user.jobengine.osgi.db/src/user/jobengine/db/ItemDAOEx.java @@ -58,43 +58,6 @@ public class ItemDAOEx { return resultQuery.toString(); } - // sql.append("select"); - // sql.append(" "); - // sql.append("i.id itemid,"); - // sql.append("i.created itemcreated,"); - // sql.append("i.houseid itemhouseid,"); - // sql.append("i.title itemtitle,"); - // sql.append("m.id mediaid,"); - // sql.append("m.created mediacreated,"); - // sql.append("m.archived,"); - // sql.append("m.length,"); - // sql.append("it.name mediatype,"); - // sql.append("m.houseid mediahouseid,"); - // sql.append("m.title mediatitle,"); - // sql.append("mf.id mediafileid,"); - // sql.append("mf.houseid mediafilehouseid"); - // sql.append(" "); - // sql.append("from mediafile mf"); - // sql.append(" "); - // sql.append("left outer join media m on (m.id = mf.mediaid)"); - // sql.append(" "); - // sql.append("left outer join item i on (i.id = m.itemid)"); - // sql.append(" "); - // sql.append("left outer join itemtype it on (it.id = m.itemtypeid)"); - // sql.append(" "); - // sql.append("where mf.storeid = 1 and ("); - // sql.append("contains(i.title, ?) >= 1"); - // sql.append(" "); - // sql.append("or contains(i.description, ?) >= 1"); - // sql.append(" "); - // sql.append("or contains(m.title, ?) >= 1"); - // sql.append(" "); - // sql.append("or contains(m.description, ?) >= 1"); - // sql.append(" "); - // sql.append("or contains(mf.houseid, ?) >= 1"); - // sql.append(")"); - // if (typeIDs != null) - // sql.append(" and m.itemtypeid in ").append(typeCriteria.toString()); private PreparedStatement createArchiveMediaSearchStatement(Connection connection, String criteria, String orderBy, boolean orderAscending, List typeIDs) throws SQLException { StringBuilder typeCriteria = new StringBuilder(); @@ -108,6 +71,47 @@ public class ItemDAOEx { typeCriteria.append(")"); } + String columNames = getColumnNames(); + + StringBuilder sql = new StringBuilder(); + sql.append("SELECT "); + sql.append(columNames); + sql.append(" FROM ITEM i, MEDIA m, MEDIAFILE mf, MEDIADESCRIPTION md WHERE i.id = md.itemid AND m.id = md.mediaid AND mf.id = md.mediafileid"); + if (typeIDs != null) { + sql.append(" AND m.itemtypeid IN "); + sql.append(typeCriteria.toString()); + } + + sql.append(" AND CONTAINS(md.description, ?) >= 1"); + sql.append(" ORDER BY "); + + if (orderBy == null) { + sql.append("itemtitle desc, mediaitemtypeid"); + } else { + sql.append(orderBy); + sql.append(orderAscending ? " ASC" : " DESC"); + } + + String query = sql.toString(); + logger.info(query); + PreparedStatement st = connection.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); + st.setString(1, criteria); + return st; + } + + private PreparedStatement createArchiveMediaSearchStatement1(Connection connection, String criteria, String orderBy, boolean orderAscending, + List typeIDs) throws SQLException { + StringBuilder typeCriteria = new StringBuilder(); + if (typeIDs != null) { + typeCriteria.append("("); + for (int i = 0; i < typeIDs.size(); i++) { + typeCriteria.append(typeIDs.get(i)); + if (i < typeIDs.size() - 1) + typeCriteria.append(","); + } + typeCriteria.append(")"); + } + String columNames = getColumnNames(); StringBuilder innerSql = new StringBuilder(); innerSql.append("SELECT DISTINCT "); diff --git a/server/user.jobengine.osgi.server/pages/searchitems.zul b/server/user.jobengine.osgi.server/pages/searchitems.zul index 4c52bbe7..9d353115 100644 --- a/server/user.jobengine.osgi.server/pages/searchitems.zul +++ b/server/user.jobengine.osgi.server/pages/searchitems.zul @@ -215,17 +215,22 @@ div.z-row-content span {