Introduction
Last week, I shared some scripts to automatically bind custom properties from an Enterprise Data Dictionary, to specific ER Objects.
Today, I’ll share some scripts which use the Team Server REST API to get the attributes of an entity or the columns of a table with their attachments & security properties, in order to display all these information in a custom page.
System requirements
To host my web files (HTML, JS, CSS), I have used Team Server Core. I’d recommend to use your own web server to properly manage the authentication.
⚠️ The web pages are using a style sheet from Team Server Core: you’d need to update the path accordingly to your own environment.
Obviously, to use the REST API, you’d need to be able to connect to the Team Server Core.
I’ll use existing ClientId & Secret. You can check this previous post which shows how to register your own application.
To get the required access token to query the API, I have used the password authorization flow with a dedicated user created in Team Server Core itself with limited permissions.
Scripts
I have the following structure:
⚠️ aproperties.htm & cproperties.htm are both using the css & js files.
- aproperties.htm – Attributes
- cproperties.htm – Columns
- properties.js – the code
- properties.css – the style
aproperties.htm
Used to display the attributes with their respective attachments & data security information.
The entity id needs to be specified in the query string.
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Attributes' properties</title> <script type="text/javascript"> sParentObjects = 'entities'; sChildObjects = 'attributes'; </script> <script src="./js/properties.js"></script> <link href="/themes/new/reset.css" rel="stylesheet" type="text/css"> <link rel="stylesheet" type="text/css" href="./css/properties.css" /> </head> <body> <table id="attributesTable" border="1"> <thead> <tr> <th colspan="4" id="TableHeader">Entity</th> </tr> <tr> <th>Attribute Name ▲</th> <th>Data Type</th> <th id="attachmentsHeader">Attachments</th> <th id="securityPropertiesHeader">Security Properties</th> </tr> </thead> <tbody> <!-- Table rows will be added dynamically using JavaScript --> </tbody> </table> </body> </html>
cproperties.htm
Used to display the columns with their respective attachments & data security information.
The table id needs to be specified in the query string.
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Columns' properties</title> <script type="text/javascript"> sParentObjects = 'tables'; sChildObjects = 'columns'; </script> <script src="./js/properties.js"></script> <link href="/themes/new/reset.css" rel="stylesheet" type="text/css"> <link rel="stylesheet" type="text/css" href="./css/properties.css" /> </head> <body> <table id="attributesTable" border="1"> <thead> <tr> <th colspan="4" id="TableHeader">Table</th> </tr> <tr> <th>Column Name ▲</th> <th>Data Type</th> <th id="attachmentsHeader">Attachments</th> <th id="securityPropertiesHeader">Security Properties</th> </tr> </thead> <tbody> <!-- Table rows will be added dynamically using JavaScript --> </tbody> </table> </body> </html>
./js/properties.js
This file is used by the 2 scripts above.
⚠️ You need to edit the first 4 lines and also the line 22 which uses the credentials from the user with limited permissions previously created to use the API.
const SERVER = "https://win2019tsc:8443"; const LIMIT = 20; const client_id = 'f6767ac9-d27f-4956-9fe1-2e07c230e8d8'; // https://docwiki.embarcadero.com/TeamServer/ERStudio/en/OAuth_2.0_API#GET_oauth.2Fauthorize const client_secret = '53368d3f-ab78-4bf0-93ae-0cff75bd7027'; // https://docwiki.embarcadero.com/TeamServer/ERStudio/en/OAuth_2.0_API#GET_oauth.2Ftoken var TOKEN = ""; const getCookieValue = (name) => ( document.cookie.match('(^|;)\\s*' + name + '\\s*=\\s*([^;]+)')?.pop() || '' ) let access_token = getCookieValue("access_token"); if (access_token != "") { TOKEN = access_token; fetchData(); } else { getAccessToken(); } function getAccessToken() { var url_token = '/api/oauth/token?username=APIViewer_username&password=APIViewer_password' + '&client_id=' + client_id + '&client_secret=' + client_secret + '&grant_type=password'; try { fetch(url_token) .then((response) => { if (response.status == 200) { return response.json(); } else { throw new Error("TOKEN NOT LOADED!"); } }) .then((wtoken) => { parseToken(wtoken); }) .catch(error => { console.error(error) }); } catch (error) { console.error(error) } } function parseToken(jtoken) { document.cookie = 'access_token=' + jtoken.access_token + "; path=/"; document.cookie = 'refresh_token=' + jtoken.refresh_token + "; path=/"; TOKEN = jtoken.access_token; fetchData(); } // Sort attachments & securityproperties function sortAttachmentsAndSecurityProperties(attributes) { attributes.forEach(attribute => { attribute.attachments.sort((a, b) => (a.name > b.name) ? 1 : -1); attribute.securityproperties.sort((a, b) => (a.name > b.name) ? 1 : -1); }); const tableHeader = document.getElementById('TableHeader'); if (tableHeader !== null) { if (attributes?.[0]?.tableLink && attributes?.[0]?.tableName) { tableHeader.innerHTML = '<a href="' + attributes[0].tableLink + '">' + attributes[0].tableName + '</a>'; } else if (attributes?.[0]?.entityLink && attributes?.[0]?.tableName) { // entityName NA tableHeader.innerHTML = '<a href="' + attributes[0].entityLink + '">' + attributes[0].tableName + '</a>'; } } } // Get the Entity ID from the query string function getEntityNumberFromURL() { const queryString = window.location.search.substr(1); const params = new URLSearchParams(queryString); for (const param of params) { if (param[0] !== '') { return param[0]; } } } // Update colspans function adjustColspans() { const attachmentsHeader = document.getElementById('attachmentsHeader'); const securityPropertiesHeader = document.getElementById('securityPropertiesHeader'); iNbCol = 2; const maxAttachments = calculateMaxAttributes('attachments'); const maxSecurityProperties = calculateMaxAttributes('securityproperties'); if (maxAttachments > 0) { attachmentsHeader.colSpan = maxAttachments; iNbCol += maxAttachments; } else { attachmentsHeader.parentNode.removeChild(attachmentsHeader); } if (maxSecurityProperties > 0) { securityPropertiesHeader.colSpan = maxSecurityProperties; iNbCol += maxSecurityProperties; } else { securityPropertiesHeader.parentNode.removeChild(securityPropertiesHeader); } if (iNbCol != 4) { const tableHeader = document.getElementById('TableHeader'); if (tableHeader !== null) { tableHeader.colSpan = iNbCol; } } } // Get the number of items for attachments or security properties function calculateMaxAttributes(propertyName) { const attributes = document.querySelectorAll('#attributesTable tbody tr'); let max = 0; attributes.forEach(attribute => { const cells = attribute.querySelectorAll(`td[data-type="${propertyName}"]`); max = Math.max(max, cells.length); }); return max; } // Display the attributes or columns function displayAttributes(attributes) { const tableBody = document.querySelector('#attributesTable tbody'); attributes.forEach(attribute => { const row = document.createElement('tr'); row.innerHTML = ` <td>${attribute.primaryKey === "True" ? '<strong>' : ''}<a href="${attribute.link}">${attribute.name}</a>${attribute.primaryKey === "True" ? '</strong>' : ''}</td> <td>${attribute.datatype}</td> ${getCellsHTML(attribute.attachments, 'attachments')} ${getCellsHTML(attribute.securityproperties, 'securityproperties')} `; tableBody.appendChild(row); }); } // Format the attachments and security properties function getCellsHTML(properties, propertyName) { const max = Math.max(calculateMaxAttributes(propertyName), properties.length); let html = ''; for (let i = 0; i < max; i++) { const property = properties[i]; if (property) { html += ` <td data-type="${propertyName}"><strong>${property.name}:</strong><br/>${property.value}</td> `; } else { html += ` <td> </td> `; } } return html; } // Get the JSON from the Team Server Core REST API function fetchData() { const ENTITY_NUMBER = getEntityNumberFromURL(); if (!ENTITY_NUMBER) { console.error('Entity number not found in URL.'); return; } const apiUrl = `${SERVER}/api/v1/${sParentObjects}/${ENTITY_NUMBER}/${sChildObjects}?limit=${LIMIT}&access_token=${TOKEN}`; fetch(apiUrl) .then(response => { if (response.status == 200) { return response.json(); } else { // if (response.status == 401) { // } throw new Error(response.status); } }) .then(data => { console.log("Showing data"); sortAttachmentsAndSecurityProperties(data[sChildObjects]); displayAttributes(data[sChildObjects]); adjustColspans(); }) .catch(error => { console.error('Error fetching data:', error); if (error.message !== "401") { const tableBody = document.querySelector('#attributesTable tbody'); const row = document.createElement('tr'); row.innerHTML = ` <td colspan="4" style="text-align: center;"><a title="${error}" href="javascript:location.reload();">Data not available</a></td> `; tableBody.appendChild(row); } else { console.log("Trying to get a new access token"); // Access Token expired // Remove cookie with old value document.cookie = 'access_token=; path=/; expires=Thu, 01 Jan 1970 00:00:00 UTC'; // Get a new access Token getAccessToken(); } }); }
./css/properties.css
This file is used by the 2 HTML files.
table { border-collapse: collapse; margin: 25px 0; box-shadow: 0 0 20px rgba(0, 0, 0, 0.15); } thead { position: sticky; top: 0px; /* Don't forget this, required for the stickiness */ } thead > tr { background-color: #6d5534; } thead > tr > th { text-align: center; font-size: larger; color: white; } th, td { border: 1px solid #ddd; padding: 8px; max-width: 250px; text-align: center; overflow: hidden; text-overflow: ellipsis; } tbody > tr { transition: all 0.5s ease-out; } tbody > tr:nth-of-type(even) { background-color: #f3f3f3; } tbody > tr:nth-of-type(odd) { background-color: #fff; } tbody > tr:hover { background-color: #95c93f; color: white; } body { width: 100%; height: 100vh; display: flex; justify-content: center; background-size: 200% 200%; background-image: linear-gradient( to right, #002395 0%, #FFFFFF 33%, #FFFFFF 67%, #ED2939 100% ); animation: AnimateBG 120s ease infinite; } @keyframes AnimateBG { 0%{background-position:0% 50%} 50%{background-position:100% 50%} 100%{background-position:0% 50%} }
Usage
When we are using Team Server Core, we can easily view detailed information regarding our Entities and Tables.
For example, in the screenshot below, we can view an entity named OFFICE_LOCATION and its details. In the address bar, the URL shows the Team Server Core ID of this specific entity: 120074
We can use this ID with the newly created page: aproperties.htm
You’d need to change the URL to match your environment. Just add a question mark followed by the entity ID.
Summary
The JavaScript has called 3 different methods from the API:
- /api/oauth/token to request an access token from credentials.
- /api/v1/entities/<entity id>/attributes to get from an entity, all the attributes with their attachments and data security information.
- /api/v1/tables/<table id>/columns to get from a table, all the columns with their attachments and data security information.
Team Server Core also provides custom properties. The pages above display the attachments and data security information defined in the diagrams using ER/Studio Enterprise Team edition.
Many other methods are available to get the metadata you need. Check the documentation for more information.