GitHub - noxify/servicemanager-table-builder: ScriptLibrary to create a new dbdict table via javascript in HP Service Manager.

Currently unmaintained since I have moved from Microfocus Service Manager to ServiceNow - Sorry guys!

Installation
Supported Databases
Make a table
Update a table
Available Fields
Available Keys
Todos
Fields
Modify Methods
- renameField
Field Options
Keys
Helpers
- Sysmod Fields
Examples
Credits

Installation

Just create a new ScriptLibrary for each file and copy the file content inside the new created ScriptLibrary. Currently there is no plan to deliver a unload file.

Supported Databases

MSSQL - tested with SM 9.52 and SM9.60
Postgres - tested with ITSMA ( SM9.52 )

Make a table

To create a new dbdict table, you have to use the make method.

var tableBuilder = system.library.tableBuilder.getClass();
var schema = new tableBuilder();
schema.make('complextable', function(builder) {
	/**
	 * Allowed methods are:
	 * * all methods to add a field
	 * * all methods to add a key
	 */
});

Update a table

To update a existing dbdict table, you have to use the modify method.

var tableBuilder = system.library.tableBuilder.getClass();
var schema = new tableBuilder();
schema.modify('complextable', function(builder) {
	/**
	 * Allowed methods are:
	 * * all methods to add a field
	 * * all methods to add a key
	 * * rename a field
	 */
});

Available Fields

Type	SM Type
Number	1
Character	2
Date/Time	3
Logical	4
Array	8
Structure	9
Expression	11

Available Keys

Unique
Primary
No Nulls
No Duplicates
Nulls & Duplicates

Todos

[] more documentation for each field
[] Add more options

Fields

Number

builder.addNumber('fieldname');

Character

builder.addCharacter('fieldname');

Date/Time

builder.addDatetime('fieldname');

Logical

builder.addLogical('fieldname');

Array

builder.addArray('fieldname', function(item) {
	//all field types are allowed
});

Available Aliases

Array of Number

builder.addArrayOfNumber('fieldname');

Array of Character

builder.addArrayOfCharacter('fieldname');

Array of Date/Time

builder.addArrayOfDatetime('fieldname');

Array of Logical

builder.addArrayOfLogical('fieldname');

Structure

builder.addStructure("filter", function(item) {
	item.addCharacter("filter.sql");
});

Expression

builder.addExpression('fieldname');

Modify Methods

renameField

Currently, the rename works only for simple fields (number, character, date/time, logical).

Rename the field, but keep the SQL Name

builder.renameField('is_active', 'isActive');

Rename the field and update the SQL Name.

builder.renameField('is_active', 'isActive', true);

setLength

Updates the sql length of a field. The method uses the information from the sqldbinfo table to determines if the current field has the Get Size option.

builder.setLength('brief.description', 500);

Field Options

Set SQL Type

For common fields, you can change field SQL Type.

builder.addCharacter("textfield").setSqlType("NVARCHAR(100)");

Set SQL Field Name

Overwrites the default sql field name with the defined value.

builder.addCharacter("textfield").setSqlName("AWESOMETEXTFIELD");

Set SQL Table

If you want, you can move a field to another table alias (e.g. from M1 to M2).

❗ All following fields will be attached to M2. If you want only one field in a new table, add the field at the end of your definition or add .setSqlTable("M1") to the next field.

This is an HPSM problem - I have tested it with the dbdidct utilities and the result was the same!

builder.addCharacter("textfield").setSqlTable("M2");

Keys

Unique

builder.addUniqueKey(['fieldname']);

Primary

builder.addPrimaryKey(['fieldname']);

No Null

builder.addNoNullKey(['fieldname']);

No Duplicates

builder.addNoDuplicateKey(['fieldname']);

Nulls & Duplicates

builder.addNullDuplicateKey(['fieldname']);

Helpers

Sysmod Fields

Instead of

builder.addCharacter('sysmoduser');
builder.addDatetime('sysmodtime');
builder.addNumber('sysmodcount');

in your definition, you can use this:

builder.withSysmodFields();

Examples

Simple create example

var tableBuilder = system.library.tableBuilder.getClass();
var schema = new tableBuilder();
schema.make('simpletable', function(builder) {
	
	builder.addNumber('id');
	builder.addLogical('is_active');
	builder.addCharacter('sysmoduser');
	builder.addDatetime('sysmodtime');
	builder.addNumber('sysmodcount');
	
	builder.addUniqueKey(['id']);
});

Expected result after running the code:

You should see a new entry in your Messages with something like:

Table simpletable has been created successfully.

Complexe create example

var tableBuilder = system.library.tableBuilder.getClass();
var schema = new tableBuilder();
schema.make('complextable', function(builder) {
	
	builder.addNumber('id');
	builder.addLogical('is_active');
	builder.addCharacter('brief.description');
	builder.addArray("longdescription", function(item) {
		item.addCharacter("longdescription");
	});
	
	builder.addStructure("filter", function(item) {
		item.addCharacter("filter.sql");
	});
	
	builder.addArray("rule", function(item) {
		item.addStructure("rule", function(subitem) {
			subitem.addNumber("ruleId");
		});
	});
	builder.withSysmodFields();
	
	builder.addUniqueKey(['id']);
});

Modify table example

var tableBuilder = system.library.tableBuilder.getClass();
var schema = new tableBuilder();
schema.modify('complextable', function(builder) {
	//add a new field to the existing table
	builder.addNumber('reference.id');
	builder.renameField('is_active', 'isActive', true);	
	builder.setLength('brief.description', 500);	
});