[SHARING] Search for string in Repository object code
Since David Briars mentioned a Summit 2016 presentation called 'Reporting from the WebFOCUS Repository' in the post Published status in WebFOCUS Repository, I thought I'd share code I created based on one of the programs from the presentation, 'Search for string in Repository object code'. I found the original report rudimentary so I created my own.
Incidentally, the original report, Search_for_string_in_code.fex, uncovered the fact that many versions of an object (fex, html and probably other object types) are saved in the Repository, but we can only get at the latest from the GUI tools.
The code is below. One can easily build an HTML page that has one text box control for the search string, and one control for the output type.
I'd like to include additional functionality:
Perform case-insensitive search - this one is difficult for some DBMS since the code is contained in a BLOB
Add Object Status (Published/Unpublished) to report - I have not yet figured out how to determine the status
Add file-type control and filter - this might be handy if the search is to be done on specific file types (fex, htm, sty, etc)
SET HOLDLIST = PRINTONLY
SET BYDISPLAY = ON
SET PAGE-NUM = NOLEAD
SET HTMLCSS = ON
SET LINES = 99999
-RUN
-DEFAULT &P_SEARCH_STRING = 'Francis Mariani';
-DEFAULT &WFFMT = 'AHTML';
-SET &P_SEARCH_STRING = LJUST(&P_SEARCH_STRING.LENGTH, &P_SEARCH_STRING, 'A&P_SEARCH_STRING.LENGTH');
-SET &P_SEARCH_STRING = TRUNCATE(&P_SEARCH_STRING);
SET SQLENGINE = SQLORA
SQL SET DEFAULT_CONNECTION WF_Repository
-RUN
SQL
SELECT
DISTINCT
T2.PRT_PATH
, T2.OBJNAME
, T2.OBJTYPE
, T2.CLASSNAME
, T2.CREATEDBY
, TO_CHAR(T2.CREATEDON, 'YYYY-MM-DD HH24:MI:SS') CREATEDON
, T2.LASTMODBY
, TO_CHAR(T2.LASTMODON, 'YYYY-MM-DD HH24:MI:SS') LASTMODON
, T4.OBJDESC
FROM
WF_REPOS.WF_CONTENT_REVS T1
INNER JOIN WF_REPOS.WF_REPOSOBJ T2 ON T1.OBJ_HANDLE = T2.HANDLE
-*LEFT OUTER JOIN WF_REPOS.WF_OBJPROPS T3 ON T2.HANDLE = T3.OBJ_HANDLE
INNER JOIN WF_REPOS.WF_NLSOBJ T4 ON T2.HANDLE = T4.OBJ_HANDLE AND T2.DEF_LNG = T4.WF_LNG
WHERE
DBMS_LOB.INSTR (T1.BCONTENT, UTL_RAW.CAST_TO_RAW ('&P_SEARCH_STRING'), 1, 1) > 0
AND T2.PRT_PATH LIKE '/WFC/Repository%'
;
TABLE ON TABLE HOLD AS TEST/H001
END
-RUN
-IF &LINES EQ 0 GOTO FOC_NODATA;
-SET &I_SEARCH_RESULT_COUNT = &LINES;
-CREATE_REPORT
JOIN CLEAR *
JOIN HANDLE AND DEF_LNG IN WF_REPOSOBJ TO OBJ_HANDLE AND WF_LNG IN WF_NLSOBJ TAG T2 AS J2
-RUN
DEFINE FILE WF_REPOSOBJ
REP_FOLDER/A100 = GETTOK(OBJNAME, 255, 1, ' ', 100, REP_FOLDER);
END
-RUN
TABLE FILE WF_REPOSOBJ
SUM
COMPUTE REP_FOLDER_TITLE/A100 = SUBSTR(1040, OBJDESC, 1, 100, 100, 'A100');
BY REP_FOLDER
WHERE OBJTYPE EQ 101
WHERE PRT_PATH LIKE '/WFC/Repository%'
ON TABLE HOLD AS HFOLDERS FORMAT XFOCUS INDEX REP_FOLDER
END
-RUN
JOIN PRT_FOLDER1 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J1 AS J1
JOIN PRT_FOLDER2 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J2 AS J2
JOIN PRT_FOLDER3 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J3 AS J3
JOIN PRT_FOLDER4 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J4 AS J4
JOIN PRT_FOLDER5 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J5 AS J5
JOIN PRT_FOLDER6 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J6 AS J6
JOIN PRT_FOLDER7 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J7 AS J7
JOIN PRT_FOLDER8 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J8 AS J8
JOIN PRT_FOLDER9 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J9 AS J9
-RUN
DEFINE FILE TEST/H001
PRT_FOLDER1/A100 = GETTOK(PRT_PATH, 1040, 1, '/', 100, 'A100');
PRT_FOLDER2/A100 = GETTOK(PRT_PATH, 1040, 2, '/', 100, 'A100');
PRT_FOLDER3/A100 = GETTOK(PRT_PATH, 1040, 3, '/', 100, 'A100');
PRT_FOLDER4/A100 = GETTOK(PRT_PATH, 1040, 4, '/', 100, 'A100');
PRT_FOLDER5/A100 = GETTOK(PRT_PATH, 1040, 5, '/', 100, 'A100');
PRT_FOLDER6/A100 = GETTOK(PRT_PATH, 1040, 6, '/', 100, 'A100');
PRT_FOLDER7/A100 = GETTOK(PRT_PATH, 1040, 7, '/', 100, 'A100');
PRT_FOLDER8/A100 = GETTOK(PRT_PATH, 1040, 8, '/', 100, 'A100');
PRT_FOLDER9/A100 = GETTOK(PRT_PATH, 1040, 9, '/', 100, 'A100');
TEMP_PATH4/A102 = IF J4.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J4.REP_FOLDER_TITLE;
TEMP_PATH5/A102 = IF J5.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J5.REP_FOLDER_TITLE;
TEMP_PATH6/A102 = IF J6.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J6.REP_FOLDER_TITLE;
TEMP_PATH7/A102 = IF J7.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J7.REP_FOLDER_TITLE;
TEMP_PATH8/A102 = IF J8.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J8.REP_FOLDER_TITLE;
TEMP_PATH9/A102 = IF J9.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J9.REP_FOLDER_TITLE;
REP_FOLDER_PATH/A720 =
J3.REP_FOLDER_TITLE
|| (' ' | TEMP_PATH4)
|| (' ' | TEMP_PATH5)
|| (' ' | TEMP_PATH6)
|| (' ' | TEMP_PATH7)
|| (' ' | TEMP_PATH8)
|| (' ' | TEMP_PATH9);
OBJ_TYPE_DESC/A25 =
IF OBJTYPE EQ 102 THEN 'Reporting Object'
ELSE IF OBJTYPE EQ 103 THEN 'WebFOCUS Style Sheet'
ELSE IF OBJTYPE IN (106, 110) THEN 'Web Page'
ELSE IF OBJTYPE EQ 107 THEN 'Cascading Style Sheet'
ELSE IF OBJTYPE EQ 109 AND PRT_PATH CONTAINS '/~' THEN 'User Procedure'
ELSE IF OBJTYPE EQ 109 THEN 'Procedure'
ELSE IF PRT_PATH CONTAINS 'deferredOutput' THEN 'Deferred Output'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.js' OR OBJNAME LIKE '%.JS') THEN 'JavaScript File'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.mht' OR OBJNAME LIKE '%.MHT') THEN 'Web Archive'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.htm' OR OBJNAME LIKE '%.HTM') THEN 'HTML Report'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.pdf' OR OBJNAME LIKE '%.PDF') THEN 'PDF Report'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.acx' OR OBJNAME LIKE '%.ACX') THEN 'Metadata Access'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.mas' OR OBJNAME LIKE '%.MAS') THEN 'Metadata Master'
ELSE LJUST(9, FPRINT(OBJTYPE, 'I9', 'A9'), 'A9') || (' ' | '(Other)');
END
-RUN
TABLE FILE TEST/H001
PRINT
OBJNAME AS 'Resource Name'
PRT_PATH AS 'Resource Path'
LASTMODBY AS 'Modified By'
LASTMODON AS 'Modified On'
CREATEDBY AS 'Created By'
CREATEDON AS 'Created On'
OBJ_TYPE_DESC AS 'Resource Type'
BY REP_FOLDER_PATH AS 'Repository Folder Path'
BY OBJDESC AS 'Resource Title'
HEADING
"WebFOCUS Repository Content – String Search"
"Resources containing the string: &P_SEARCH_STRING"
ON TABLE SET STYLE *
-*INCLUDE=your-style-sheet.sty, $
TYPE=REPORT, TITLETEXT='WebFOCUS Repository Content – String Search', $
TYPE=REPORT, COLUMN=REP_FOLDER_PATH, WRAP=OFF, $
TYPE=REPORT, COLUMN=PRT_PATH, WRAP=OFF, $
TYPE=REPORT, COLUMN=LASTMODON, WRAP=OFF, $
TYPE=REPORT, COLUMN=CREATEDON, WRAP=OFF, $
-IF &WFFMT EQ 'HTML' GOTO HTML_STYLE;
-IF &WFFMT EQ 'EXL2K' GOTO EXCEL_STYLE;
TYPE=REPORT,
LINES-PER-PAGE=25,
ARGRAPHENGINE=JSCHART,
ALLOW-SENDEMAIL=OFF,
ALLOW-SAVECHANGE=OFF,
-*ALLOW-PAGINATION=ON,
-*ALLOW-FILTER=ON,
-*ALLOW-FREEZE=ON,
-*ALLOW-SORT=ON,
-*ALLOW-PRINT=ON,
-*ALLOW-CALC=OFF,
-*ALLOW-HIDE=OFF,
-*ALLOW-CHART=OFF,
-*ALLOW-EXPORT=OFF,
-*ALLOW-VISUALIZE=OFF,
-*ALLOW-ROLLUP=OFF,
-*ALLOW-PIVOT=OFF,
-*ALLOW-COMMENTS=OFF,
-*ALLOW-WINDOW=OFF,
-*ALLOW-RESTORE=OFF,
-*ALLOW-ACCORDION=OFF,
-*ALLOW-TOOLS=OFF,
$
TYPE=REPORT,
GRAPHCOLOR=RGB(79 157 190),
GRAPHCOLORNEG=RGB(242 139 32),
$
TYPE=REPORT,
OBJECT=CURRENT-ROW,
HOVER-BACKCOLOR=RGB(229 239 240),
$
TYPE=REPORT,
OBJECT=STATUS-AREA,
PAGE-LOCATION=BOTTOM,
JUSTIFY=LEFT,
COLOR=RGB(255 255 255),
BACKCOLOR=RGB(204 181 118),
$
TYPE=REPORT,
OBJECT=MENU,
COLOR=RGB(80 80 80),
HOVER-COLOR=RGB(80 80 80),
BACKCOLOR=RGB(229 239 240),
HOVER-BACKCOLOR=RGB(242 247 247),
BORDER-COLOR=RGB(169 169 169),
$
TYPE=REPORT, FREEZE-COLUMN=OBJDESC, $
-GOTO END_STYLE
-HTML_STYLE
-*TYPE=REPORT,HFREEZE=TOP, SCROLLHEIGHT=400, $
-GOTO END_STYLE
-EXCEL_STYLE
TYPE=REPORT,HFREEZE=TOP, SCROLLHEIGHT=1, $
-GOTO END_STYLE
-END_STYLE
ENDSTYLE
ON TABLE PCHOLD FORMAT &WFFMT
END
-RUN
-GOTO REPORT_END
-FOC_NODATA
TABLE FILE CAR
PRINT
COUNTRY NOPRINT
COMPUTE MSG/A200 = 'Search String not found.'; AS ''
WHERE RECORDLIMIT EQ 1;
END
-RUN
-REPORT_END
This message has been edited. Last edited by: Francis Mariani,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
July 06, 2016, 02:32 PM
GavinL
- FOCUS Man, just FOCUS! ----------------------------- Product: WebFOCUS Version: 8.1.04 Server: Windows 2008 Server
July 28, 2016, 03:50 PM
Francis Mariani
There's a flaw with this thing!
Due to the so-far-undocumented "feature" of multiple versions of a resource, if a resource at one time contained the search string, it will be included in the result set, even if it does not currently contain the string.
Looking into it...
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 28, 2018, 10:52 AM
Francis Mariani
I was told the multiple versions issue existed only for v8.0.08...
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
August 16, 2018, 03:25 PM
TobyMills
Hey guys
I've been using a 3rd party tool for searching for things in the repository and saw this post.
Take a look at this site - it's just a couple of guys I know who decided to make their own 3rd party tool for searching for content.
Take a look at their brochure - I use this just about every week for one thing or another.
I've installed in on 8105 and 8202. Easy import package and tweak to your edasprof to add a couple of global vars.
Be aware though - using this, you'll bypass the UOA security and read the repository directly. Thus if you decide to use this, make sure you are admin to keep your reports secure. It looks through the whole repository.
The guys who wrote it can help you install to try it out.
Good luck! Toby Mills, CISSP
August 21, 2018, 08:01 PM
David Briars
Hey Toby. Thanks for the link to the TWIGI-BI site/brochure.
It does look like a very nice product; and it is great to see 3rd party tools in the WebFOCUS arena.
August 21, 2018, 11:49 PM
TobyMills
No problem David - these guys are looking for more things to add that people like us could use.
Feel free to ask them to think about any extra stuff that we all could find useful.
I used it again today and will be doing some impact analysis for finding SQL server stored procs this week in an attempt to clean up our code and make it more uniform.