Team Server REST API – Create custom pages

はじめに

Last week, I shared some scripts to automatically bind custom properties from an エンタープライズデータディクショナリ, 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">エンティティ</th>
				</tr>
				<tr>
					<th>属性&nbsp;Name&nbsp;▲</th>
					<th>データ&nbsp;Type</th>
					<th id="attachmentsHeader">Attachments</th>
					<th id="securityPropertiesHeader">セキュリティ&nbsp;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">テーブル</th>
				</tr>
				<tr>
					<th>コラム&nbsp;Name&nbsp;▲</th>
					<th>データ&nbsp;Type</th>
					<th id="attachmentsHeader">Attachments</th>
					<th id="securityPropertiesHeader">セキュリティ&nbsp;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();
}

機能 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)
	}
}

機能 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
機能 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
機能 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
機能 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
機能 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
機能 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
機能 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>&nbsp;</td>
`;
	}
  }
  return html;
}

// Get the JSON from the Team Server Core REST API
機能 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

Entity's detailed information — Entity’s detailed information

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.

概要

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.