Document data source

CADMATIC eShare supports a couple of ways of accessing documents stored in a file system. A database adapter can function as a general-purpose document management adapter when document data is available in a certain way:

Document metadata, including the hierarchical folder structure, is stored in a document database.
Documents are stored in a file system that CADMATIC eShare can access, and the document database points to those files.
Each document consists of exactly one PDF file.
To enable the system to inject 3D object links into the documents, the PDF files must be searchable, and text must be stored as text, not as graphics.

For example, you could utilize this functionality by exporting data from a document management system and configuring a database adapter to read the exported data from a specified location.

Creating a document data source

Add a document data source to a database adapter to enable the adapter to provide access to PDF documents and to enable objects in the 3D model to be automatically linked to documents.

Prerequisites

A database adapter that connects to the document database. See Creating a database adapter.
To enable indexing, a suitable document type must be defined. See Adapters and indexing.

Do the following:

Navigate to the project to edit, and then click Project Admin in the main menu. The project administration view opens.
Click Adapters and Data Sources. The Adapters and Data Sources configuration view opens.
On the Adapters list, click the database adapter to which to add a data source.
In the Data Sources section of the adapter settings, click Add data source.
In the Available Data Source Types section, select Document Data Source, and click Create data source.
In the Data Source section, specify the following settings:

Name – Enter a name for the data source.
Description (optional) – Enter a description for the data source.
State – Specify whether the data source is disabled or enabled (default).

In the Groups allowed to see the data section, select user groups that should be allowed to see the data that this data source provides.
- If no groups are selected, only administrators will see the data.
- Add user groups with Add.
- Remove user groups with the remove button.
- If the All Users group is selected, other group selections will be ignored.

The Adapter Configuration section is read-only.

In the Data Source Configuration section, specify the following settings. The different queries that are defined here require unique document identifiers. It is up to you to decide what to use as document ID; typically, it is an internal identifier used in your database.
- Indexing – Select if document indexing is enabled. If you enable it, the system can search for object links in documents, store those links in the project database, and use this document index to create links from 3D objects to relevant documents. The documents must be found in the subfolders of the document base path.
  Note: If you save the data source configuration with indexing, all data sources of the same adapter with indexing enabled, will be indexed again.
  If Enabled, specify the following settings:
  - Indexing Speed – Define the duration of indexing.
    If you select Duration, specify the target duration of each indexing round in Target Indexing Duration field (in minutes). The set duration takes effect only after the first full round of indexing has been completed. The default is 120.
    If you select As Fast As Possible, the target of each indexing round is to be completed in the shortest time as possible.
  - Indexing Mode – Indexing is done every time the data source configuration is saved. Indexing can also be triggered manually from the Project Administration's General view.
    If you select Once, indexing is done only after saving.
    If you select Daily, indexing is done once every day.
    If you select Once per Days of Week, indexing is done once on the selected days.
    If you select Given Times in Days of Week, indexing starts on given times of the day on the selected days. The times are given in the server's local time.
    If you select Days of Month, indexing is done once on the selected days of the month.
    If you select All the Time, indexing is done continuously. Warning: This can consume a lot of resources if As Fast as Possible is selected as indexing speed.
- Load Subfolders On Demand— If enabled, the entire document tree is not loaded at once, but the contents are loaded and visible following the user's examination. The default is Disabled.
  The following Displaying Documents settings specify the document base path in the file system and the document metadata fields that will be available. For more information and query examples, see Displaying documents.
- Load Documents From — Select if the documents are retrieved from File System or Database. The default is file system.
- Document base path – This setting specifies the base location under which documents are stored in the file system. Visible when Load Documents From is set to File System.
- Document File Query – Enter a database query that provides document metadata.
  When documents are loaded from the file system, at the minimum, the query must return the file name (including the path, relative to document base path) and document's intended display name. Additional fields that it returns will be displayed as document attributes in the Documents view.
  When documents are loaded from the database, at the minimum, the query must return the document binary blob and document's intended display name. Additional fields that it returns will be displayed as document attributes in the Documents view.
  Note: When retrieving documents directly from the database, the selected name of the document must include its file extension.
- Test Document ID – Enter a document ID and click Test Query to check that the query returns expected results.
  The following Folder Structure settings allow the document folder structure to be browsed and the documents in it to be opened from the Documents view of CADMATIC eShare. For more information and query examples, see Listing all available documents on the documents tab.
- Query for Root Folders – Enter a database query that defines the top-level folders under the document base path. At minimum, the query must return a unique folder identifier and a folder name. Click Test Query to check that the query returns expected results.
- Query for Subfolders – Enter a database query that defines the subfolders under the root folders. At minimum, the query must return a unique folder identifier and a folder name.
- Test parent ID – Enter an ID of a root folder and click Test Query to check that the query returns expected results.
- Query for Documents in Folder – Enter a database query that defines which documents are contained in each folder. At minimum, the query must return a unique document identifier and a document name.
- Test folder ID –Enter a root folder or subfolder ID and click Test Query to check that the query returns expected results.
  The following Locating Documents settings enable documents to be associated with objects in the 3D model, using document metadata stored in the document database, and to be searchable with simple search.
- Link Queries – Click Add Link Query to define serving tags and database query to use for locating links. For more information and query examples, see Accessing documents from model items.
- Search Query – Enter the database query to use when searching document files with the simple search of CADMATIC eShare. For more information, see Searching.
- Test Search Term – Enter a search term and click Test Query to check that the search query returns expected results.

Results

Depending on which settings you defined, some or all of the following actions will be possible:

The document tree and the documents it contains can be browsed in the Documents view.
Simple search can find documents from the document tree.
The system can associate objects in the 3D model with matching documents.

Displaying documents

To be able to read the document content, the adapter needs two settings: document file base path and document file query.

Document base path is the root file path where the documents provided by this data source reside, and it must be accessible to CADMATIC eShare server to be used. A trailing backslash for the directory is not necessary.

Document base path example:

C:\Documents\ExampleProject

The document file query provides the document's metadata (attributes) as well as the relative path of its file. Use the Document Identifier provided by a Link Query, a Search Query, or a Query for Documents in Folder as the key of the query. Return the name of the file, including the path relative to the base path, as the first column in the query. Return the document name shown to the user as the second column in the query. If your query returns more columns, these are used as metadata shown to users in the documents view, and can be used to define links in different document types.

Document file query example:

SELECT Filename, Name FROM Documents WHERE Id = @Key

@Key will be replaced with the value of the document identifier for the document that triggered the query.

Listing all available documents on the documents tab

In the Documents view, users can browse documents using either a tree view or list view. In order to populate this view, a project administrator must define three queries in the Document data source:

A query for Root Folders.
A query for Subfolders.
A query for Documents in Folder.

Together these three queries define a tree structure of documents that users can browse.

Query for root folders

You can define all the top-level (root) folders relative to the Document base path using this query. The root folder query must return two columns for each folder. The first column defines a unique identifier for the folder. The second query defines a display name for the folder. The users will see the name of the folder in the Documents view.

Root folder query example:

SELECT Id AS FolderId, Name AS FolderName

FROM DocumentFolders WHERE ParentId IS NULL

You can verify that the root folder query is working as you intended by clicking the Test Query button under the root query.

Note that folder identifiers do not need to be integers: you can also use the folder paths as the identifiers, for example. The only requirement is that the identifiers are unique within the document data source.

Handling folder paths in SQL without a table specifically for the folders can be awkward. For example, if you have a single table with relative file names in it, the root folders can be queried with:

SELECT DISTINCT
SUBSTRING([File], 1, CHARINDEX('\', [File])) AS FolderId,
SUBSTRING([File], 1, CHARINDEX('\', [File])-1) AS FolderName
FROM [TableName]

Due to the complexity of this and the other queries described below, it is recommended that you include a second table in your database to describe the folder structure, if possible.

Query for subfolders

Use this query to define the subfolders of a given folder. The given folder identifier can be that of a top-level folder or a subfolder. The subfolder query must return two columns for each subfolder. The first column defines a unique identifier for the subfolder. The second column defines the name of the subfolder. The users will see the name of the subfolder in the Documents view.

Subfolder query example:

SELECT Id AS FolderId, Name AS FolderName
FROM DocumentFolders WHERE ParentId = @Key

You can test the subfolder query by providing a parent folder identifier and clicking the Test Query button under the subfolder query. You can find a valid parent identifier by testing the root folder query.

If the specified folder does not have any subfolders, the query should not produce any results.

Note that a subfolder query is required even if your hierarchy only contains a single level. In this case, you can use a query like:

SELECT Id AS FolderId, Name AS FolderName
FROM DocumentFolders WHERE 1 = 0

In the example above with relative file names in a single table, subfolders of a given folder can be queried with:

SELECT DISTINCT
(@Key + SUBSTRING(SUBSTRING([File],LEN(@Key)+1,100000),1,CHARINDEX('\', SUBSTRING([File], LEN(@Key)+1, 100000)))) AS FolderId,
(SUBSTRING(SUBSTRING([File],LEN(@Key)+1,100000),1,CHARINDEX('\', SUBSTRING([File], LEN(@Key)+1, 100000)))) AS FolderName
FROM [TableName]
WHERE SUBSTRING([File], 1, LEN(@Key)) = @Key
AND CHARINDEX('\', SUBSTRING([File], LEN(@Key)+1, 100000)) > 0

Query for documents in folder

Use this query to define which documents are contained in each folder. The given folder identifier can be that of a top-level folder or subfolder. The document query must return two columns for each file. The first column returns the identifier of the document. The second column returns the name of the document. The name is displayed in the Documents view.

Note that you can combine several columns into one result column, for example to display both the drawing number and title as the document name.

Document query example:

SELECT Id AS DocumentId, DrawingNr + ' ' + Title AS Name
FROM Documents WHERE FolderId = @Key

When defining a document query, you can test it by providing a folder identifier and clicking the Test Query button. You can find a valid folder identifier by testing either the root folder query or the subfolder query.

Again referring to the example above with only relative file names in the database, documents in a given folder can be queried with:

SELECT Id AS DocumentId, Title AS Name
FROM [TableName]
WHERE SUBSTRING([File], 1, LEN(@Key)) = @Key
AND CHARINDEX('\', SUBSTRING([File], LEN(@Key) + 1, 10000)) <= 0

Accessing documents from model items

Link queries answer the question “Which documents are associated with model item X?”. This mapping must be based on metadata in your document database. For example, documents can have a field ‘System’ that contains a system name which can be found in the model tag ‘sys’.

For each query, define the model tags that are using the query by adding the tags to the Serving Tags field, one tag per line. Then write the query to map the tag value to a document. Return the identifier of the document as the first column of the result. You can return the name of the document as the second column of the result, but this is optional.

Link query example:

SELECT Id AS DocumentId, Name AS DocumentName
FROM Documents WHERE SystemName = @Key

Searching

If you want to search for documents from a document data source, use the Search Query to return document identifiers based on a search string. The first column that the query returns is the document identifier, and any subsequent columns are ignored. You can use this query through the simple search user interface.

Search query example:

SELECT Id AS DocumentId FROM Documents WHERE Name LIKE '%' + @Key +'%'