Content Server Database Tables

HI.

I need to access a category attribute of each content server document to validate a date.

I'm using LiveReport / WebReport.

Can you tell me what table I can get the documents from the content server and also have access to the attributes of the categories of those documents?

Regards,

Felipe.

Find more posts tagged with

Comments

Colin_Schmidt

LLATTRDATA.

Attached(hopefully) is a basic relationship Visio drawing, I’ve made back in 2013 and have since updated it. It has some of the “codes” necessary for figuring out which fields to use to extract based on the AttrID & Attrtype.

LLattrdata.id = Dtree.dataid and llattrdata.vernum = dtree.versionnum

If you need more, let me know.

Colin J

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, June 7, 2018 11:56 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Content Server Database Tables

Content Server Database Tables

Posted byfelipe.fonseca@entelgy.com (Open Text, Projeto) On 06/07/2018 12:49 PM

HI.

I need to access a category attribute of each content server document to validate a date.

I'm using LiveReport / WebReport.

Can you tell me what table I can get the documents from the content server and also have access to the attributes of the categories of those documents?

Regards,

Felipe.

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	My Support

This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful. Where permitted by applicable law, this e-mail and other e-mail communications sent to and from Cognizant e-mail addresses may be monitored.

Greg Griffiths

ask your OT Account person for the NDA for the schema, be sure to list all your modules as you may need additional notes for RM etc. Then you can use this with the Schema Companion guide (https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=16448243&objAction=View) to work with the DB. The OT course is also very good.

Colin_Schmidt

These tables are connected to each other for your purpose:

DTREE.DATAID = LLATTRDATA.ID

DTREE.VERSIONNUM = LLATTRDATA.VERNUM

LLATTRDATA.DEFID = CATREGIONMAP.CATID

Inside here, you’ll find an easier way of getting to which attribute is what kind of data item.

Categories are first DTREE Objects. But an intelligible breakdown of their parts are found in the CATREGIONMAP.

In the case of a DATE, the LLATTRDATA.ATTRTYPE = -7, but if you have more than one date attribute, you’ll need to

Know which one by the LLATTRDATA.ATTRIID = <last character/parameter of CATREGIONMAP.REGIONMAP>

Then you pull out the LLATTRDATA.VALDATE.

The NDA is the document to start with, but after you open the DB up with SQL Developer or some DB tool and look over the tables, over time you can figure out the relations on your own. Codes, a fair amount of them, can be found in the Schema Companion, as another reply gave. Others you discover here in the KC.

Colin J

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 11, 2018 5:51 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Content Server Database Tables

Content Server Database Tables

Posted bygreg.a.griffiths@capgemini.com (Griffiths, Greg) On 06/11/2018 06:43 AM

[To post a comment, use the normal reply function]
Topic:	Content Server Database Tables
Forum:	Content Server LiveReports Forum
Content Server:	My Support

Projeto_Open_Text

Thanks all for the help.

Whats is the difference in subtype 144 and subtype 1 in DTREE table?

Wich subtype I can get in DTREE table to find the documents in enterprise workspace?

I've also noticed that any documents included in the content server and deleted later remains in DTREE table. Why does it happen?

If I disable Recycle Bin this wont be happening ?

Thanks.

Regards,

Felipe

Projeto_Open_Text

I'm looking for document and category information as per the query below.

I am using the DTREE and LLATTRDATA tables to validate the date of a category.

select d.name, a.valdate from DTREE d JOIN LLATTRDATA a on a.id = d.DataID where valdate < (select ADD_YEARS(TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'),-20) from DUMMY)

I search the field date in a category and check if the field date is greater than 20 years. That way I'll know if I can discard the document.

Is this information secure? Or do you think I should include in the JOIN DTREE.VERSIONNUM = LLATTRDATA.VERNUM and LLATTRDATA.DEFID = CATREGIONMAP.CATID?

Quoted Colin Schmidt on 06/11/2018 05:55 PM:

These tables are connected to each other for your purpose:

DTREE.DATAID = LLATTRDATA.ID

DTREE.VERSIONNUM = LLATTRDATA.VERNUM

LLATTRDATA.DEFID = CATREGIONMAP.CATID

Inside here, you’ll find an easier way of getting to which attribute is what kind of data item.

Categories are first DTREE Objects. But an intelligible breakdown of their parts are found in the CATREGIONMAP.

In the case of a DATE, the LLATTRDATA.ATTRTYPE = -7, but if you have more than one date attribute, you’ll need to

Know which one by the LLATTRDATA.ATTRIID = <last character/parameter of CATREGIONMAP.REGIONMAP>

Then you pull out the LLATTRDATA.VALDATE.

Colin J

Content Server Database Tables

Posted by greg.a.griffiths@capgemini.com (Griffiths, Greg) On 06/11/2018 06:43 AM

[To post a comment, use the normal reply function]
Topic:	Content Server Database Tables
Forum:	Content Server LiveReports Forum
Content Server:	My Support

Colin_Schmidt

Most definitely add those fields. Without them, you don’t know if you’re getting the Attributes for the current Version. You will also need to add the qualifying part to get the specific Attribute that contains the Date you’re after. LLATTRDATA.ATTRTYPE = -7 for DATEs and yet, if you have more than one Date Attribute, you will need to check that get the right one with LLATTRDATA.ATTRID which can be matched up somewhat to the CATREGIONMAP.REGIONMAP field(last part of value).

Colin J

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 11, 2018 5:06 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: [EXTERNAL] - RE Content Server Database Tables 2

[EXTERNAL] - RE Content Server Database Tables 2

Posted byfelipe.fonseca@entelgy.com (Open Text, Projeto) On 06/11/2018 05:55 PM

I'm looking for document and category information as per the query below.

I am using the DTREE and LLATTRDATA tables to validate the date of a category.

select d.name, a.valdate from DTREE d JOIN LLATTRDATA a on a.id = d.DataID where valdate < (select ADD_YEARS(TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'),-20) from DUMMY)

I search the field date in a category and check if the field date is greater than 20 years. That way I'll know if I can discard the document.

Is this information secure? Or do you think I should include in the JOIN DTREE.VERSIONNUM = LLATTRDATA.VERNUM and LLATTRDATA.DEFID = CATREGIONMAP.CATID?

Quoted Colin Schmidt on 06/11/2018 05:55 PM:

These tables are connected to each other for your purpose:

DTREE.DATAID = LLATTRDATA.ID

DTREE.VERSIONNUM = LLATTRDATA.VERNUM

LLATTRDATA.DEFID = CATREGIONMAP.CATID

Inside here, you’ll find an easier way of getting to which attribute is what kind of data item.

Categories are first DTREE Objects. But an intelligible breakdown of their parts are found in the CATREGIONMAP.

In the case of a DATE, the LLATTRDATA.ATTRTYPE = -7, but if you have more than one date attribute, you’ll need to

Know which one by the LLATTRDATA.ATTRIID = <last character/parameter of CATREGIONMAP.REGIONMAP>

Then you pull out the LLATTRDATA.VALDATE.

Colin J

Content Server Database Tables

Posted by greg.a.griffiths@capgemini.com (Griffiths, Greg) On 06/11/2018 06:43 AM

[To post a comment, use the normal reply function]
Topic:	Content Server Database Tables
Forum:	Content Server LiveReports Forum
Content Server:	My Support

[To post a comment, use the normal reply function]
Topic:	Content Server Database Tables
Forum:	Content Server LiveReports Forum
Content Server:	My Support

Greg Griffiths

Felipe,

for a list of subtype values and what they mean, I would recommend https://www.greggriffiths.org/livelink/reference/subtypes/. The reason the content remains in DTREE is that even through the object is "deleted" it is in the Recycle Bin, which is part of the system, if you empty the Recycle Bin it should go.

Revabth_424

Hi all,
How can we fetch the Location attribute which is located in the properties-->categories-->Personal information-->Location: for an employee workspace folder. Please suggest the attrtype

appuq

Show a screencap so that forum users can know if it is coming fro a category or if it is part of OTDS or UserInfo

Revabth_424

Please find the above category information information for a particular employee workspace, we have to fetch only "Location" attribute which is present in Categories —> Personal Information —> Location. We already have an query to fetch the workspace name of employee workspace and folders and files inside it. But now we have to add the new lines into in the query to fetch Location of that employee workspace. Please help me with the LLAttrData table info and what is the attribute I'd ifor this Location attriute.

Greg Griffiths

if you have the Category and Attribute IDs of it you can find it in the database, you can also use CatRegionMap table if you have different IDs in each environment.

appuq

?func=attributes.dump is a RH that is used,it reads the table CatRegionMap that Greg mentions

https://forums.opentext.com/forums/developer/discussion/comment/969720#Comment_969720

I have a query here that I used to pull Similar things the LLA1, and LLA2 are multiple joins to the same table. This is a common pattern one uses, there's an official one as well. The clue is to find the

LLA1.AttrID in (2) and 3 and so on which you get from CatRegionMap or ?func=attributes.dump.In my case all the data was defined as String hence the use of ValStr.Note that I am pulling from two categories

Enterprise Global,

Facility Global hence the differing numbers (270280 and 2331738)

select DT.DataID,DT.Name,

LLA4.ValStr "Facility Global:Discipline",

LLA5.ValStr "Facility Global:DocType",

LLA6.ValStr "Facility Global:ContentType",

LLA7.ValStr "Facility Global:Collection",

LLA8.ValStr "Facility Global:Business Function",

LLA1.ValStr "Enterprise Global:Business Function",

LLA2.ValStr "Enterprise Global:Collection",

LLA3.ValStr "Enterprise Global:Content Type",

DT.OwnerID,DT.ParentID,DT.SubType,DT.VersionNum

from DTree DT 

left   join  LLAttrData LLA1  on DT.DataID=LLA1.ID 

and LLA1.DefID=270280 and DT.VersionNum=LLA1.VerNum and LLA1.AttrID in (2)

left   join LLAttrData LLA2  on DT.DataID=LLA2.ID 

and LLA2.DefID=270280 and DT.VersionNum=LLA2.VerNum and LLA2.AttrID in (3)

left   join LLAttrData LLA3  on DT.DataID=LLA3.ID 

and LLA3.DefID=270280 and DT.VersionNum=LLA3.VerNum and LLA3.AttrID in (4)

left   join LLAttrData LLA4  on DT.DataID=LLA4.ID 

and LLA4.DefID=2331738 and DT.VersionNum=LLA4.VerNum and LLA4.AttrID in (2)

left   join LLAttrData LLA5  on DT.DataID=LLA5.ID 

and LLA5.DefID=2331738 and DT.VersionNum=LLA5.VerNum and LLA5.AttrID in (7)

left   join LLAttrData LLA6  on DT.DataID=LLA6.ID 

and LLA6.DefID=2331738 and DT.VersionNum=LLA6.VerNum and LLA6.AttrID in (11)

left   join LLAttrData LLA7  on DT.DataID=LLA7.ID 

and LLA7.DefID=2331738 and DT.VersionNum=LLA7.VerNum and LLA7.AttrID in (8)

left   join LLAttrData LLA8  on DT.DataID=LLA8.ID 

and LLA8.DefID=2331738 and DT.VersionNum=LLA8.VerNum and LLA8.AttrID in (12)

where  DT.DataID in (select TOP 30000 DataID FROM DTreeAncestors where AncestorID=2332140);

OT used to have a BI tool not sure if they still do. Its developers created these however this actually creates database objectshttps://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=12740255&objAction=ArticleView

Revabth_424

hi @appuq,

Thankyou I have explored the list of attributes by using ?func=attributes.dump, now I am able to execute the query with appropriate DefId and attrId.

I have an other question on differences between test and production database tables, I am getting the results by executing below query in test environment, but unable to get the results for prod environment.

SELECT ws.name AS "Employee_Workspace_Name",
fol.name AS "Folder_Name",
doc.name AS "Document_Name"
FROM dtree doc
INNER JOIN dtree fol ON doc.parentid = fol.dataid
INNER JOIN dtree ws ON doc.ownerid = ws.dataid
WHERE doc.subtype = 144
AND fol.subtype = 0
AND ws.subtype = 848
AND fol.name = '200 Miscellaneous'
ORDER BY ws.name;

With this query I am able to see the results in test environment, but in prod we are getting empty results, though we have same folder structure, please suggest me if any changes needed in the query for production.

Hans Stoop

The query does look OK and should not need to be changed in PROD.

What you should check if the data is as you expect in your query.

Look for a document in a folder '200 Miscellaneous'. Check if the folder is actually named like that in DTree.

Check if the ParentID and OwnerID are as expected on the document.

Revabth_424

It does not stating any errors , but taking long time to run and at the end it showing like empty results with column names Type and Name. Any clue on how to check Dtree table on columns in content server itself , as we do not have access to databse

Hans Stoop

If this is in a livereport you need to change the report type to Automatic LiveReport.

Revabth_424

By changing the formate to auto Live report I am able to view the results now, Thankyou so much Sir. Now, with this we can generate a report right with web report help. This auto live report format did not effect in report generation right?

appuq

No, the auto livereport is intended when you don't need to alias names. The standard LR format allows automatic hyperlinks in the output if certain key table columns are used DTree.dataid, KUAF.ID,webnodes, etc.

in an autolivereport, if you wrote select * from DTree it will spit out columns

in LR you would have to say the column names(aliases)

in your WR you will need to read the column returned either using positional parameters or column names.

The WR/LR should not be made by hand in production use a transport.

DarrenHodder

Hi. in WebReports, the CAT and CATACTION sub-tags should let you do what you need to do in terms of accessing category attribute info and updating it without necessarily knowing the table schema.