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

Table of Contents

• Installation
• Supported Databases
• Make a table
• Update a table
• Available Fields
• Available Keys
• Todos
• Fields
  • Number
  • Character
  • Date/Time
  • Logical
  • Array
    • Available Aliases
      • Array of Number
      • Array of Character
      • Array of Date/Time
      • Array of Logical
  • Structure
  • Expression
• Modify Methods
  • renameField
• Field Options
  • Set SQL Type
  • Set SQL Field Name
  • Set SQL Table
• Keys
  • Unique
  • Primary
  • No Null
  • No Duplicates
  • Nulls & Duplicates
• Helpers
  • Sysmod Fields
• Examples
  • Simple create example
  • Complexe create example
  • Modify table example
• 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);	
});

Expected result after running the code:

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

Table complextable has been created successfully.

Credits

Special thanks goes to:

• yim OHG - My old company ❤️ My first version was built there and this version includes also some parts from Version 1 (see inline mentions ;) )
• ironboy - He is the creator of the awesome classier package. I had to modify the class a bit to make it work inside the HPSM.