How can one identify whether a .fex is published or un-published use the WebFOCUS repository? (WF_REPOSOBJ)

This message has been edited. Last edited by: FP Mod Chuck,

Hi Mark J. Lora,
If the color of the fex is blue then it is published and if the color is grey then it is unpublished or you can just right click on the particular fex file and identify whether it is published or unpublished.

Thanks
WebFOCUS
8.1.04

Mark, I have the same question.

Ruchika, we would like to know if a fex is published from reading the Repository database tables, not from pointing and clicking. Thanks.

I think this is a properly constructed SQL statement to retrieve information regarding Repository objects. I created two fexes, one Published and one not. I do not see the Published attribute appearing in the result set. Incidentally, the SQL reveals that multiple versions of the BLOB are saved for each object - one for every time the object is saved from an editor. I wonder if this is documented.

SELECT
  'WF_CONTENT_REVS' WF_CONTENT_REVS
, T1.*
, 'WF_REPOSOBJ' AS WF_REPOSOBJ
, T2.*
, 'WF_OBJPROPS' AS WF_OBJPROPS
, T3.*
, 'WF_NLSOBJ' AS WF_NLSOBJ
, T4.*
, 'WF_ITEM' AS WF_ITEM
, T5.*
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
INNER JOIN WF_REPOS.WF_ITEM T5 ON T2.HANDLE = T5.HANDLE
WHERE
T2.OBJNAME LIKE 'wf_repository_test%'
ORDER BY T2.OBJNAME
;

Note that the SQL may return multiple rows per object version - one for each set attribute: hidden, runasolap, defer, schedonly.

I don't think that there is any "flag" that indicates where an object is published or not, I think that it may just be down to who "owns" it.

If the owner is a user (normally indicated with the tilde "~" prefix if memory serves) then it is private to that user.

I may be wrong here as I haven't had a lot of time to be able to look at this and I do not currently have any repository tables available to me.

T

My SQL retrieves and displays all the available columns and I don't see an owner column or an indication of Published status. I thought perhaps it's hidden in a BIP_ table, but those tables appear to be for objects in BI Portals only.

As I mentioned, I don't have access currently to a set of repository tables but it might be something like PRT_PATH or CHD_PATH, somewhere where the userid is prefixed with the tilde character.

T

How can a WebFOCUS "Expert" walk around without a Repository in his back pocket? It's like a nun without her rosary!

It would be great if we could get a layout and description of all the repository items and tables. I've been wanting to create a list of available portals for a user to pick from and run, but I haven't been able to find the right stuff in those tables yet. I know there's a "portal tree" available to place on portal pages, but I was wanting to write an html page for this (and other) purposes.

Yes, we need documentation on all the Repository tables, but it's not likely we'll receive any.

I have the beginnings of some SQL, but I haven't tied the User info to the BIP info yet.

List all published B.I. Portals:

SELECT 
  WF_REPOS.BIP_NLS.BIP_NLS_COMPONENT_VALUE
, WF_REPOS.BIP_COMPONENT_METADATA.BIP_COMPONENT_NAME
, WF_REPOS.BIP_COMPONENT.BIP_COMPONENT_PATH

, WF_REPOS.BIP_COMPONENT.*
, WF_REPOS.BIP_COMPONENT_METADATA.*
, WF_REPOS.BIP_NLS.*
FROM WF_REPOS.BIP_COMPONENT
INNER JOIN WF_REPOS.BIP_COMPONENT_METADATA ON WF_REPOS.BIP_COMPONENT.BIP_COMPONENT_ID = WF_REPOS.BIP_COMPONENT_METADATA.BIP_COMPONENT_ID
INNER JOIN WF_REPOS.BIP_NLS ON WF_REPOS.BIP_COMPONENT.BIP_COMPONENT_ID = WF_REPOS.BIP_NLS.BIP_NLS_COMPONENT_ID
WHERE
BIP_COMPONENT_TYPE = 'viewroot'
AND BIP_COMPONENT_PROPERTIES LIKE '%publish="true"%'
ORDER BY 
WF_REPOS.BIP_COMPONENT.BIP_COMPONENT_ID
;

List all users:

SELECT 
  WF_REPOS.UOA_USERS.DESCRIPTION
, WF_REPOS.UOA_USERS.NAME

, WF_REPOS.UOA_USERS.*
, WF_REPOS.BIP_USER_PROFILE.*
FROM WF_REPOS.UOA_USERS
INNER JOIN WF_REPOS.BIP_USER_PROFILE ON WF_REPOS.BIP_USER_PROFILE.BIP_USER_PROFILE_UOA_ID = WF_REPOS.UOA_USERS.ID
WHERE WF_REPOS.UOA_USERS.NAME = 'fmariani'
ORDER BY WF_REPOS.UOA_USERS.NAME
;

Now, how to determine all the B.I. Portals one user has access to?

quote:

Originally posted by Francis Mariani:
How can a WebFOCUS "Expert" walk around without a Repository in his back pocket? It's like a nun without her rosary!

Sounds like a bad habit to get into.

quote:

How can a WebFOCUS "Expert" walk around without a Repository in his back pocket?

I know that this was tongue in cheek Francis, but someone might believe that you were "having a dig".

What I actually meant is that I had signed off for the day (I posted at 20:30 BST) and did not have access to a repository that I could interrogate - but then you knew that!

T

Tony, tongue firmly planted in cheek. And the IBI cops that usually rap my knuckles haven't called yet.

I hope you're having a wonderful day and will be able to leave work at a reasonable hour.

Cheers,

quote:

Originally posted by Emily Max:

quote:

Originally posted by Francis Mariani:
How can a WebFOCUS "Expert" walk around without a Repository in his back pocket? It's like a nun without her rosary!

Sounds like a bad habit to get into.

Yes, it's not very convent-ional behavior.

Barry Austin gave a presentation at Summit 2016 entitled 'Reporting from the WebFOCUS Repository'.

He has since published the materials/links on the 'Tips and Techniques' website:
http://www.informationbuilders...-webfocus-repository

If anyone implements these utilities at their site, maybe they can report the results here?

(At our site we use the WebFOCUS RESTful Web Services Requests to get info from the repository. I think we will plan to stick with RESTful, as it is per a published manual.)

David,

Thanks for the info on the presentation.

I found the "Search for string in code" report to be rudimentary so I adapted and improved upon it. I'd like to include a status column in the report Published/Unpublished. Mine works for Oracle.

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.

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

quote:

BIP_COMPONENT_PROPERTIES LIKE '%publish="true"%'

If this is where the published status is set, then this will save me a lot of time

This message has been edited. Last edited by: Waz,

Waz, does the BIP_COMPONENT_PROPERTIES LIKE '%publish="true"%' code work for .fex files that are not (necessarily) in the BI Portal?

Haven't checked yet

Unfortunately, BIP_COMPONENT_PROPERTIES (contained in table BIP_COMPONENT) is for B.I. Portal components only - portals, pages, containers, etc., NOT for Repository contents.

I was hoping for the location of content published flag.

I have written some analysis fexes used in our release process and there are a lot of unpublished stuff coming through clogging the results.

I am still of the belief that the fact that it is "owned" by a user is the likely method.

The column to check is PRT_PATH in WF_REPOSOBJ and check for a tilde character

e.g. WHERE PRT_PATH CONTAINS '~'

If you compare the XML outputs from a Rest properties call, you will see that the only difference between a call when the procedure is unpublished or published is a list of 4 attributes prefixed "owner"

• ownerType="U"
• ownerPath="IBFS:/SSYS/USERS/admin"
• ownerName="admin"
• ownerId="10001"

T

WF_REPOSOBJ.PRT_PATH contains the Repository path of the object in WF_REPOSOBJ.OBJNAME. It's just the path. I have a sub-folder folder called FrancisMariani in a folder called Test in the Repository. the value for WF_REPOSOBJ.PRT_PATH for ALL the objects is /WFC/Repository/Test/FrancisMariani, published or not.

I think this SQL is correct and I think it joins all the Repository-related tables:

SELECT
/*DISTINCT*/
  T2.PRT_PATH
, T2.OBJNAME
, T1.*
, T2.*
, T3.*
, T4.*
, T5.*
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
INNER JOIN WF_REPOS.WF_ITEM T5 ON T1.OBJ_HANDLE = T5.HANDLE
WHERE
    T2.PRT_PATH = '/WFC/Repository/Test/FrancisMariani'
AND T2.OBJNAME LIKE '%published%'
ORDER BY T2.PRT_PATH, T2.OBJNAME, VERSION DESC
;

I created two fexes in a folder - one called published.fex (which I Published) and one called published_not.fex (which I left Unpublished). The WHERE statement above filters on the folder that contains the two fexes and filters on the object names.

I cannot see anything that indicates the Published fex.

PRT_PATH values that contain ~ (tilde) reference "My Content" folders. Irrelevant to this discussion, the objects in these folders cannot be Published, though they can be Shared.

quote:

"My Content" folders

That'll teach me to use a repository to which I have GUI access and not an old copy of a MSSSQL one

T

I'm still lost and the minders haven't piped in

Let's REALLY hope not, but perhaps the Published Status is stuffed into the BLOB

Phew. This seems to suggest Published Status is not in the BLOB:

select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BCONTENT)) from WF_REPOS.WF_CONTENT_REVS where WF_REPOS.WF_CONTENT_REVS.obj_handle in ('0cf72fab_ee98_4390_a141_6cfc1b95415c', 'd47cc6be_9050_4990_b7dc_9804ce4f45dd');