Under the Covers of the 11.2 Document Repository

Admittedly this is a pretty niche topic but I thought I may as well post it anyway.

On our EPM 11.1.2.3 system we had a mechanism whereby users could upload documents to Workspace and then our batch jobs could run with those files as parameters. In order to do this we would have to reconcile the folder and filename in R&A Workspace with the folder and filename in the RM1 folder. This required getting information out of the database and creating it as a view.

So in 11.1.2.3 a typical view entry would look like the below:


The definition of the view is available below.

/*

MAKE SURE TO CREATE SYNONYMS FIRST! EXAMPLES BELOW:

IF OBJECT_ID('dbo.s_RAF_CONTAINER') IS NOT NULL DROP SYNONYM  [dbo].[s_RAF_CONTAINER]
GO
CREATE SYNONYM [dbo].[s_RAF_CONTAINER] FOR [RAF].[dbo].[V8_CONTAINER]
GO

IF OBJECT_ID('dbo.s_RAF_FOLDER') IS NOT NULL DROP SYNONYM [dbo].[s_RAF_FOLDER]
GO
CREATE SYNONYM [dbo].[s_RAF_FOLDER] FOR [RAF].[dbo].[V8_FOLDER]
GO

*/

IF OBJECT_ID('dbo.V_BATCH_FILE_LOCATION_FILE') IS NOT NULL DROP VIEW dbo.V_BATCH_FILE_LOCATION_FILE;
GO

CREATE VIEW dbo.V_BATCH_FILE_LOCATION_FILE AS
WITH ContainerAll AS (
  SELECT
    [CONTAINER_UUID] AS UUID,
    [PARENT_FOLDER_UUID] AS ParentUUID,
    [NAME] AS Name,
    [LAST_MODIFIED_DATE] AS LastModified,
    [OWNER_LOGIN] AS Owner,
    [IS_FOLDER]
  FROM
    [dbo].[s_RAF_CONTAINER]
  WHERE
    [OWNER_LOGIN] IS NOT NULL
),
Container AS (
  SELECT
    UUID,
    ParentUUID,
    Name
  FROM
    ContainerAll
  WHERE
    [IS_FOLDER] = 0
),
[File] AS (
  SELECT
    UUID,
    ParentUUID,
    Name,
    LastModified,
    Owner
  FROM
    ContainerAll
  WHERE
    [IS_FOLDER] = 1
),
ContainerChild AS (
  SELECT
    c.UUID,
    c.ParentUUID,
    c.Name,
    CAST('\' + (c.Name) as varchar(255)) AS Path
  FROM
    Container c
  WHERE
    c.ParentUUID IS NULL
    
  UNION ALL
  
  SELECT
    c.UUID,
    c.ParentUUID,
    c.Name,
    CAST((c2.Path + '\' + c.Name) as varchar(255)) AS Path
  FROM
    Container c
    INNER JOIN ContainerChild c2 on c.ParentUUID = c2.UUID
),
Path AS (
  SELECT
    cc.UUID,
    REPLACE(SUBSTRING(cc.Path, 6, LEN(cc.Path)), '\', '/') AS Path,
    fs.FOLDER_FULL_PATH,
    CASE 
      WHEN fs.FOLDER_FULL_PATH LIKE '/\%' THEN
      REPLACE(SUBSTRING(fs.FOLDER_FULL_PATH, 2, LEN(fs.FOLDER_FULL_PATH)), '\', '/')
      WHEN fs.FOLDER_FULL_PATH LIKE '/%' THEN 
        REPLACE('\' + SUBSTRING(fs.FOLDER_FULL_PATH, 2, LEN(fs.FOLDER_FULL_PATH)), '\', '/')
      ELSE
        REPLACE(fs.FOLDER_FULL_PATH, '\', '/')
    END AS FileSystemPath
  FROM
    ContainerChild cc
    JOIN [dbo].[s_RAF_FOLDER] fs ON cc.UUID = fs.CONTAINER_UUID
  WHERE
    cc.UUID IN (SELECT ParentUUID FROM [File])
)
SELECT
  f.UUID,
  f.Name,
  p.Path,
  p.FileSystemPath,
  p.Path + '/' + f.Name AS FilePath,
  p.FileSystemPath + '/' + f.UUID + '.1' AS FileFileSystemPath,
  f.Owner,
  f.LastModified
FROM
  [File] f
  JOIN Path p ON f.ParentUUID = p.UUID
  WHERE
    f.ParentUUID NOT IN ('CORPWORKSPACEPAGEFOLDER', 'DATASOURCESFOLD',
                         'REPORTMART', 'Script_Repository', 'SSR_TEMPLATE',
                         'SYSTEM', 'USERPOVFOLD')

Now in 11.2 the RA repository is no more - but there is something similar. So we wanted to create functionality that could do the same thing. In 11.2 the tables are very, very different. The file itself (in fact most document properties) are saved as hex values in the database table, so additionally we would have to change our scripts to pull the file data out of the table first and then run operations on it.

The document repository tables relevant for us are:

• REPOSITORY_OBJECTS - gives us the UUID of a repository object, the object type, parent object, owner and last modified date
• REPOSITORY_OBJECT_PROPERTIES - allows us to view the properties of an object, encoded as a hex string - properties include the name, description, file size, original filename and content
• REPOSITORY_OBJECT_PROP_INDEX - is essentially the properties of the properties of an object, giving us the data type and string value for properties of a certain object

So seeing the data in these tables I knew we would be able to build a view to give the information we needed. The tables above had the information we needed - the object hierarchy, object names, UUIDs, owners and last modified dates. The only thing I needed to do was pull all the data together.

I wanted to keep the exact same view columns so I knew I would have to somehow change the existing view definition into something that can be used with 11.2. Now I am not the best SQL coder so I am sure there are much better ways of achieving the same result but I managed to adapt the view, so now it will give a view entry like the below:


The new definition of the view is available below.

/*

MAKE SURE TO CREATE SYNONYMS FIRST! EXAMPLES BELOW:

IF OBJECT_ID('dbo.s_DOCREP_OBJECTS') IS NOT NULL DROP SYNONYM [dbo].[s_DOCREP_OBJECTS]
GO
CREATE SYNONYM [dbo].[s_DOCREP_OBJECTS] FOR [HSS].[dbo].[REPOSITORY_OBJECTS]
GO

IF OBJECT_ID('dbo.s_DOCREP_OBJECT_PROP_INDEX') IS NOT NULL DROP SYNONYM [dbo].[s_DOCREP_OBJECT_PROP_INDEX]
GO
CREATE SYNONYM [dbo].[s_DOCREP_OBJECT_PROP_INDEX] FOR [HSS].[dbo].[REPOSITORY_OBJECT_PROP_INDEX]
GO

*/

IF OBJECT_ID('dbo.V_BATCH_FILE_LOCATION_FILE') IS NOT NULL DROP VIEW dbo.V_BATCH_FILE_LOCATION_FILE;
GO

CREATE VIEW dbo.V_BATCH_FILE_LOCATION_FILE AS
WWITH ContainerAll AS (
  SELECT
    DISTINCT do.[OBJECT_UUID] AS UUID,
    do.[PARENT_FOLDER_UUID] AS ParentUUID,
    dopi.STRING_VALUE AS Name,
    do.LAST_ACCESSED AS LastModified,
    do.AUTHOR AS Owner,
    CASE
   WHEN do.TYPE = 'FOLDER' THEN 0
   ELSE 1
 END AS IS_FOLDER
  FROM
    [dbo].[s_DOCREP_OBJECTS] as do
  INNER JOIN 
    [dbo].[s_DOCREP_OBJECT_PROP_INDEX] as dopi
  ON do.OBJECT_UUID=dopi.OBJECT_UUID COLLATE Latin1_General_CI_AS
  WHERE
    KEYWORD='NAME' AND NOT do.TYPE IN ('des', 'rog', 'kbk', 'dso', 'pov', 'rpt', 'bch', 'bjo')
),
Container AS (
  SELECT
    UUID,
    ParentUUID,
    Name
  FROM
    ContainerAll
  WHERE
    [IS_FOLDER] = 0
),
[File] AS (
  SELECT
    UUID,
    ParentUUID,
    Name,
    LastModified,
    Owner
  FROM
    ContainerAll
  WHERE
    [IS_FOLDER] = 1
),
ContainerChild AS (
  SELECT
    c.UUID,
    c.ParentUUID,
    c.Name,
    CAST('\' + (c.Name) as varchar(999)) AS Path
  FROM
    Container c
    
  UNION ALL
  
  SELECT
    c.UUID,
    c.ParentUUID,
    c.Name,
    CAST((c2.Path + '\' + c.Name) as varchar(999)) AS Path
  FROM
    Container c
    INNER JOIN ContainerChild c2 on c.ParentUUID = c2.UUID
),
Path AS (
  SELECT
    cc.UUID,
    REPLACE(cc.Path, '\', '/') AS Path
  FROM
    ContainerChild cc
  WHERE
    cc.UUID IN (SELECT ParentUUID FROM [File])
)
SELECT
  f.UUID COLLATE Latin1_General_CI_AS AS UUID,
  f.Name COLLATE Latin1_General_CI_AS AS Name,
  p.Path COLLATE Latin1_General_CI_AS AS Path,
  FileSystemPath = NULL,
  p.Path + '/' + f.Name COLLATE Latin1_General_CI_AS AS FilePath,
  FileFileSystemPath = NULL,
  f.Owner COLLATE Latin1_General_CI_AS AS Owner,
  f.LastModified
FROM
  [File] f
  JOIN Path p ON f.ParentUUID = p.UUID
  WHERE
    f.ParentUUID NOT IN ('CORPWORKSPACEPAGEFOLDER', 'DATASOURCESFOLD',
                         'REPORTMART', 'Script_Repository', 'SSR_TEMPLATE',
                         'SYSTEM', 'USERPOVFOLD', 'ROOTfr', '')
 AND p.Path + '/' + f.Name LIKE '/Root/%'

Hopefully this information will prove useful to someone! If someone finds a way to optimise the view and suggest improvements I am very open to that. For us the next step is to create a function that pulls the related object out of the SQL tables. So the query would do something like:

SELECT PROPERTY_VALUE FROM [HSS].[dbo].[REPOSITORY_OBJECT_PROPERTIES]
WHERE OBJECT_UUID = '...' AND PROPERTY_NAME = 'jcr:data'