nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

selecting active documents

Josephine_Bush

I am trying to get a handle on the database schema and have a question about retrieving active documents. If I query the DTree table for all documents (subtype 144), will I get a listing of all documents including ones that are deleted? I noticed that if I link DTree.DataID = DeletedDocs.NodeID that I get a large number of results. Do I need to exclude these results to get an accurate result set of the documents that are active (not deleted) in the system?

Find more posts tagged with

Comments

Narasimha_Rao

Message from Narsi <narsi@supaisystems.com> via eLink

Hi,

The following query might help you fetching the Active Documents

SELECT Name AS "Active Documents"
FROM DTree
WHERE SubType = 144
AND SPermissions != 128

Let me know if this helps.

Regards,
Narsi
Supai Systems

On Mon, Oct 11, 2010 at 8:41 PM, eLink Discussion: Open Text Live Reports Discussion <livereportsdiscussion@elinkkc.opentext.com> wrote:

selecting active documents Posted by josephine.bush@res-americas.com (Bush, Josephine) on 2010/10/11 11:06

I am trying to get a handle on the database schema and have a question about retrieving active documents. If I query the DTree table for all documents (subtype 144), will I get a listing of all documents including ones that are deleted? I noticed that if I link DTree.DataID = DeletedDocs.NodeID that I get a large number of results. Do I need to exclude these results to get an accurate result set of the documents that are active (not deleted) in the system?
[To reply to this thread, use your normal E-mail reply function.]
Discussion: Open Text Live Reports Discussion
Livelink Server: knowledge-wlweb01
To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi Josephine,

When a document is deleted by a user, this may be a full and permanent delete or just a first pass if one of two modules is installed and configured.

The modules are Document Undelete and Recycle Bin.

The first one only supports documents (subtype 144), the second can support many object types.

The two modules work in a similar way - if the object being deleted is a supported type and it is configured, then it will be temporarily moved into a different workspace.

From here, a time based agent can perform the final delete after a configured period [safety buffer] or an administrator can purge the items.

Once the object is fully deleted, it no longer exists as a object in DTree - although the audit trail will persist in other tables.

There is no flag on the DTree table to show an object is deleted, because once deleted, it is gone.

So for your requirement you have to determine if a document is not in the trash-can, pending the full delete.

There are two reliable ways you can do this.

As you say, you could join DTree to DeletedDocs (or GDCRecycleBin), according to which module is installed, but that would be likely to be poor for performance.

Alternatively, as the pending deleted object has been moved into that workspace (volume) then you could just check the OwnerID column of the DTree table.

The following would show you what the ownerid would be for any object you will consider inactive (The undelete workspace and recycle bin objects are subtype 402 and 3030 respectively.There should only be 1 row returned as you should only have one module installed and configured.

select ownerid, parentid, dataid, name, subtype from dtree where subtype in (402,3030);

From a couple of different installs, I see the following results...

OWNERID PARENTID DATAID NAME SUBTYPE
---------- ---------- ---------- ------------------------------ ----------
-138263 -1 138263 Recycle Bin 3030

OWNERID PARENTID DATAID NAME SUBTYPE
---------- ---------- ---------- ------------------------------ ----------
-2064 -1 2064 Livelink Undelete Workspace 402

Any item in this half-deleted state will have the ownerid that is returned by the above query.

This workspace will not change once you have set up and configured the relevant module unless you explicitly change it,

so you can use the value as a literal in your query.

Finally then, your original query can use this ownerid as the determining factor and would look like the following....

select <your columns> from DTree

where subtype = 144 and ownerid not = -2064

and <your other criteria>

I hope this provides sufficient background and helps you optimize your query.

Good luck.

Regards

Lindsay Davies

European Escalation Team

Open Text

From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2010 October 11, Mon 16:11
To: eLink Recipient
Subject: selecting active documents

selecting active documents

Posted by josephine.bush@res-americas.com (Bush, Josephine) on 2010/10/11 11:06

Josephine_Bush

Hi Lindsay, I found this worked if I did ownerid <> -2060. Thank you for pointing me in the right direction. There was another reply from a user to use this query: SELECT Name AS "Active Documents" FROM ll9501.DTree WHERE SubType = 144 AND SPermissions != 128 This gets me close to the number that your query produces, but there is a slightly lower number. What would be the reason for the difference? Thank you, Josephine

Josephine_Bush

Hi Narsi, I think this is working. Although I got another response and with this query: select * from ll9501.DTree where subtype = 144 and ownerid <> -2060This gets me close to the number that your query produces, but there is a slightly higher number. Do you know what would be the reason? Thank you, Josephine

Narasimha_Rao

Message from Narsi <narsi@supaisystems.com> via eLink

Hi,

In your system if 2060 is the DataID of Livelink Undelete Workspace, then your query is correct.

Normally in Livelink after Document got deleted Undelete Purge Agent will run based on "Document Undelete Administration" settings in Admin page, and delete those documents from Undeleted Workspace.
As long as Undeleted Workspace is having deleted documents list, you can see same list in DTree as well.

So you can query for Active Documents like :

SELECT Name AS "Active Documents"
FROM DTree
WHERE SubType = 144
AND OwnerID <> -2060

Regards,
Narsi
Supai Systems.

On Wed, Oct 13, 2010 at 4:14 AM, eLink Discussion: Open Text Live Reports Discussion <livereportsdiscussion@elinkkc.opentext.com> wrote:

re- selecting active documents Posted by josephine.bush@res-americas.com (Bush, Josephine) on 2010/10/12 18:39
In reply to: Re selecting active documents Posted by narsi@supaisystems.com (Rao, L.V.Narasimha) on 2010/10/12 04:27

Hi Narsi,

I think this is working. Although I got another response and with this query:

select * from ll9501.DTree
where subtype = 144
and ownerid <> -2060

This gets me close to the number that your query produces, but there is a slightly higher number. Do you know what would be the reason?

Thank you,
Josephine
[To reply to this thread, use your normal E-mail reply function.]
Topic: selecting active documents
Discussion: Open Text Live Reports Discussion
Livelink Server: knowledge-wlweb01
To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi Josephine,

The permissions test is not specific to the fact that an item is partially deleted.

Depending on your configuration, permissions may be reduced when an item is moved to the Undelete Workspace or Recycle Bin when the user 'deletes' it.

But that may also be true of items that are not deleted.

Permissions are separate to the 'partial' delete state.

Regards

Lindsay

From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2010 October 12, Tue 23:39
To: eLink Recipient
Subject: RE selecting active documents

RE selecting active documents		Posted by josephine.bush@res-americas.com (Bush, Josephine) on 2010/10/12 18:37
In reply to: RE selecting active documents		Posted by ldavies (Davies, Lindsay) on 2010/10/12 06:34

Hi Lindsay,

I found this worked if I did ownerid <> -2060. Thank you for pointing me in the right direction.

There was another reply from a user to use this query:
SELECT Name AS "Active Documents"
FROM ll9501.DTree
WHERE SubType = 144
AND SPermissions != 128

This gets me close to the number that your query produces, but there is a slightly lower number. What would be the reason for the difference?

Thank you,
Josephine

Josephine_Bush

Hi Lindsay, Thank you for your response. I really appreciate your help on this.

Josephine_Bush

Hi Narsi, I really appreciate your help on this. Thanks! Josephine