Skip to main content
Kofax

Online learning folders in the database

3032662

Question / Problem: 

As part of troubleshooting, how can online learning folders be identified in the database?

Answer / Solution: 

At the direction of technical support, you may be asked to run a diagnostic query to get information about the online learning folders in the database.  Use the query below for the version of KTA in the environment.

TotalAgility 7.8 and later

If database names are different in the target environment replace "TotalAgility_Documents" with the documents database name and then "TotalAgility" with the main database name.

--List OL folders, subfolders, and doc counts, by project and type
--If needed, replace "TotalAgility." with the main DB name and "TotalAgility_Documents." with the docs DB name
SELECT  
    co.NAME,
    CASE 
        WHEN co.OBJECT_TYPE=0 THEN 'Shared'
        WHEN co.OBJECT_TYPE=1 THEN 'Classification'
        WHEN co.OBJECT_TYPE=2 THEN 'Extraction'
    ELSE 'Unknown' END AS OBJECT_TYPE,
    CONVERT(VARCHAR(36), olm.ONLINE_LEARNING_FOLDER_ID,2) AS TrainMapFolderId, olroot.Id AS RootFolderId, 
    ns.Id AS NewSampleFolderId, tmp.Id AS TempHoldingId,
    (SELECT COUNT(*) FROM TotalAgility_Documents.dbo.DocumentData WITH(NOLOCK) 
        WHERE ParentId=olmf.InternalId) AS TrainMapDocCount,
    (SELECT COUNT(*) FROM TotalAgility_Documents.dbo.DocumentData WITH(NOLOCK) 
        WHERE ParentId=tmp.InternalId) AS TempDocCount,
    (SELECT COUNT(*) FROM TotalAgility_Documents.dbo.DocumentData AS nsDoc WITH(NOLOCK)
        WHERE nsDoc.ParentId IN (
            SELECT InternalId FROM TotalAgility_Documents.dbo.FolderData WITH(NOLOCK) WHERE ParentId=ns.InternalId)
    ) AS NewSamplesCount,
    (SELECT COUNT(*) FROM TotalAgility_Documents.dbo.DocumentData WITH(NOLOCK) 
        WHERE RootId=olroot.InternalId) AS TotalDocs
FROM TotalAgility.dbo.CAPTURE_ONLINE_LEARNING_MAPPING AS olm WITH(NOLOCK)
INNER JOIN (SELECT DISTINCT ID, NAME, OBJECT_TYPE FROM TotalAgility.dbo.CAPTURE_OBJECT WITH(NOLOCK)) 
    AS co ON co.ID=olm.CAPTURE_OBJECT_ID
LEFT JOIN TotalAgility_Documents.dbo.FolderData AS olmf WITH(NOLOCK)
    ON REPLACE(CONVERT(VARCHAR(36), olmf.Id,2),'-','')=CONVERT(VARCHAR(36), olm.ONLINE_LEARNING_FOLDER_ID,2)
LEFT JOIN TotalAgility_Documents.dbo.FolderData AS olroot WITH(NOLOCK) 
    ON olroot.InternalId=olmf.ParentId
LEFT JOIN TotalAgility_Documents.dbo.FolderData AS ns WITH(NOLOCK) 
    ON ns.ParentId=olroot.InternalId AND ns.FolderIndex=0
LEFT JOIN TotalAgility_Documents.dbo.FolderData AS tmp WITH(NOLOCK) 
    ON tmp.ParentId=olroot.InternalId AND tmp.FolderIndex=1
ORDER BY co.NAME, co.OBJECT_TYPE

TotalAgility 7.7 and earlier

Execute this script against the TotalAgility_Documents database.  If non-default database names are used in the target environment, then replace [TotalAgility] with the correct name of the main database.

--Update main database from [TotalAgility] in below script if needed 
SELECT olroot.Id AS OLRoot, co.NAME,
    CASE 
        WHEN co.OBJECT_TYPE=0 THEN 'Shared'
        WHEN co.OBJECT_TYPE=1 THEN 'Classification'
        WHEN co.OBJECT_TYPE=2 THEN 'Extraction'
    ELSE 'Unknown' END AS OBJECT_TYPE, 
    ns.NewSampleRoot, olm.ONLINE_LEARNING_FOLDER_ID AS OLMappingReadyToTrain, temp.TempHolding, 
    (olmap.ReadyToTrainCount + ns.NewSampleCount + temp.TempHoldingCount) AS TotalDocs,
    ns.NewSampleCount, olmap.ReadyToTrainCount, temp.TempHoldingCount
FROM Folder AS olroot WITH(NOLOCK)
LEFT JOIN (--New Samples by OL Root
    SELECT olr.Id AS OLRoot, nsb.ParentId AS NewSampleRoot, (SELECT COUNT(*) FROM Folder AS n 
        INNER JOIN Document AS d ON d.ParentId=n.Id WHERE n.ParentId=nsb.ParentId) AS NewSampleCount
    FROM Folder AS olr WITH(NOLOCK)
    --20 block folders for new samples
    INNER JOIN Folder AS nsb WITH(NOLOCK) ON nsb.RootId=olr.Id AND nsb.TypeId='Id10' 
    WHERE olr.TypeId='Id1'
    GROUP BY olr.Id, nsb.ParentId
) AS ns ON ns.OLRoot=olroot.Id
LEFT JOIN (-- Temp holding docs by OL Root
    SELECT olr.Id AS OLRoot, temp.Id AS TempHolding, 
    (SELECT COUNT(*) FROM Document AS d WHERE d.ParentId=temp.Id) AS TempHoldingCount
    FROM Folder AS olr WITH(NOLOCK)
    INNER JOIN Folder AS temp WITH(NOLOCK) ON temp.RootId=olr.Id AND temp.NamingCustomText='Temp'
    WHERE olr.TypeId='Id1'
    GROUP BY olr.Id, temp.Id
) AS temp ON temp.OLRoot=olroot.Id
LEFT JOIN (-- OLMapping/Ready-to-train docs by OL Root
    SELECT olroot.Id AS OLRoot, rtt.Id AS OLMapping, 
    (SELECT COUNT(*) FROM Document AS d WHERE d.ParentId=rtt.Id) AS ReadyToTrainCount
    FROM Folder AS olroot WITH(NOLOCK)
    INNER JOIN Folder AS rtt WITH(NOLOCK) ON rtt.RootId=olroot.Id AND rtt.NamingCustomText='Train'
    WHERE olroot.TypeId='Id1'
    GROUP BY olroot.Id, rtt.Id
) AS olmap ON olmap.OLRoot=olroot.Id
FULL JOIN [TotalAgility].[dbo].[CAPTURE_ONLINE_LEARNING_MAPPING] AS olm  WITH(NOLOCK)
    ON REPLACE(CONVERT(VARCHAR(36), olmap.OLMapping,2),'-','')=
    CONVERT(VARCHAR(36), olm.ONLINE_LEARNING_FOLDER_ID,2)
LEFT JOIN (
    SELECT co.ID, MAX(co.VERSION) AS MaxVersion 
    FROM [TotalAgility].[dbo].[CAPTURE_OBJECT] AS co WITH(NOLOCK)
    GROUP BY co.ID
) AS m ON m.ID=olm.CAPTURE_OBJECT_ID
LEFT JOIN [TotalAgility].[dbo].[CAPTURE_OBJECT] AS co WITH(NOLOCK) 
    ON (olm.CAPTURE_OBJECT_ID=co.ID AND co.VERSION=m.MaxVersion)
WHERE olroot.TypeId='Id1' OR olroot.TypeId IS NULL --OL Root folder type

Applies to:  

Product Version
KTA All

 

 

  • Was this article helpful?