Helix ALM License Server 2017.2

Helix ALM License Server 2017.2 - 2018.1 ERD

Table details

Table: EMAIL

Comment Contains email addresses for license server users

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	Email address unique identifier
USERID	No	NUMERIC(10)	Yes	User the email address is associated with. Foreign key to the LSUSER table.
TYPEID	No	NUMERIC(10)	No	Email address type: 1 - Internet 2 - MAPI 3 - Other
ADDRESS	No	VARCHAR(189)	No	User email address

Table: EVENT

Comment Contains information about events that affect the number of licenses available to users, such as adding or removing licenses from the server and users logging in and out of Perforce products

Columns:

Column name	Primary key	Data type	Not NULL	Comment
ID	Yes	NUMERIC(10)	Yes	Event unique identifier
EVENTDATE	No	DATETIME	Yes	Date/time the event occurred
EVENTTYPEID	No	NUMERIC(10)	Yes	Event type defined by the EVENTTYPE table

Table: EVENTDETAILS

Comment Contains information about the number of licenses in each license pool when an event occurred

Columns:

Column name	Primary key	Data type	Not NULL	Comment
EVENTID	Yes	NUMERIC(10)	Yes	License event. Foreign key to the EVENT table.
POOLID	Yes	NUMERIC(10)	Yes	License pool the licenses were retrieved from. Foreign key to the POOL table.
USED	No	NUMERIC(10)	No	Number of used floating licenses in the license pool when the event occurred
AVAILABLE	No	NUMERIC(10)	No	Number of available floating licenses in the license pool when the event occurred

Table: EVENTTYPE

Comment Contains descriptions of events in the EVENT table

Columns:

Column name	Primary key	Data type	Not NULL	Comment
ID	Yes	NUMERIC(10)	Yes	Event type unique identifier
NAME	No	VARCHAR(40)	No	Event description

Table: EVENTUSER

Comment Contains the users associated with each license event

Columns:

Column name	Primary key	Data type	Not NULL	Comment
ID	Yes	NUMERIC(10)	Yes	Event user unique identifier
EVENTID	No	NUMERIC(10)	Yes	Event that occurred. Foreign key to the EVENT table.
USERID	No	NUMERIC(10)	Yes	User associated with the event. Foreign key to the LSUSER table.
PRODUCTID	No	NUMERIC(10)	Yes	Perforce product used when the event occurred. Foreign key to the PRODUCTNAME table.
TYPEID	No	NUMERIC(10)	Yes	License type used. Foreign key to the LICENSETYPE table.

Table: EXTERNALAUTH

Comment Contains external authentication permission descriptions

Columns:

Column name	Primary key	Data type	Not NULL	Comment
ID	Yes	NUMERIC(10)	Yes	External authentication permission unique identifier
NAME	No	VARCHAR(40)	No	External authentication permission description

Table: LDAPFIELD

Comment Contains information about LDAP or Active Directory user fields mapped to Helix ALM License Server user fields

Columns:

Column name	Primary key	Data type	Not NULL	Comment
LDAPSERVERID	Yes	NUMERIC(10)	Yes	LDAP or Active Directory server the field is associated with
USERFIELDID	Yes	NUMERIC(10)	Yes	License server field in the USERFIELD table
LDAPFIELD	No	TEXT	No	LDAP or Active Directory field name

Table: LICENSE

Comment Contains information about licenses managed on the license server

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	License unique identifier
LKEY	No	VARCHAR(32)	No	License key
DISABLED	No	NUMERIC(1)	No	Indicates if the license is inactive in the license server admin utility
MAINTEXT	No	NUMERIC(1)	No	Indicates if the license is a maintenance or term license extender

Table: LICENSETYPE

Comment Contains license types available in the license server

Columns:

Column name	Primary key	Data type	Not NULL	Comment
ID	Yes	NUMERIC(10)	Yes	License type unique identifier
NAME	No	VARCHAR(40)	No	License type description

Table: LSLDAPSERVER

Comment Contains information about LDAP and Active Directory servers configured on the license server

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	LDAP or Active Directory server unique identifier
UORDER	No	NUMERIC(10)	No	Index number for the server based on the order displayed in the Server Options dialog box in the license server admin utility
DISABLED	No	NUMERIC(1)	No	Indicates if the LDAP or Active Directory server is inactive in the license server admin utility
NAME	No	VARCHAR(192)	No	LDAP or Active Directory server name in the license server admin utility
BASE	No	VARCHAR(765)	No	Base LDAP or Active Directory domain for the server connection
HOST	No	VARCHAR(384)	No	LDAP or Active Directory server IP address
PORT	No	NUMERIC(10)	No	LDAP or Active Directory server port number
BKHOST	No	VARCHAR(384)	No	LDAP or Active Directory backup server IP address
BKPORT	No	NUMERIC(10)	No	LDAP or Active Directory backup server port number
ANONLOGIN	No	NUMERIC(1)	No	Indicates if anonymous binding is used with the LDAP server. Does not apply to Active Directory servers.
USERNAME	No	TEXT	No	Username used to connect to the LDAP or Active Directory server
USERDN	No	VARCHAR(765)	No	Domain name of account used to connect to the LDAP or Active Directory server
PASSWORD	No	TEXT	No	Password used to connect to the LDAP or Active Directory server
ISAD	No	NUMERIC(1)	No	Indicates if the server is configured as an Active Directory server
ATTRUNAME	No	VARCHAR(40)	No	Reserved for future use
ATTREMAIL	No	VARCHAR(40)	No	Reserved for future use
DOMAIN	No	VARCHAR(765)	No	Microsoft networking domain of the Active Directory server
SSO	No	NUMERIC(10)	No	Single sign-on setting for the LDAP or Active Directory server: 0 - Disabled 1 - Enabled 2 - Required
AUTHMETH	No	NUMERIC(10)	No	Indicates if SSL is enabled for LDAP or Active Directory server connections: 2 - Enabled 6 - Disabled
SYNCACTIVE	No	NUMERIC(1)	No	Indicates if the LDAP or Active Directory server should synchronize user activation

Table: LSUSER

Comment Contains information about users added to the license server

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	User unique identifier
FIRSTNAME	No	VARCHAR(96)	No	User first name
LASTNAME	No	VARCHAR(96)	No	User last name
LOGINNAME	No	TEXT	Yes	Username used to log in to Perforce products (encrypted)
PASSWORD	No	TEXT	No	Password used to log in to Perforce products (encrypted)
NOTES	No	TEXT	No	Notes about the user
DATELOGOUT	No	DATETIME	No	Date/time the user last logged out of a Perforce product
COMPANY	No	VARCHAR(192)	No	User company name
DIVISION	No	VARCHAR(192)	No	User division
DEPARTMENT	No	VARCHAR(192)	No	User department
ADDRESS	No	VARCHAR(765)	No	User address
ISCUSTOMER	No	NUMERIC(1)	No	Indicates if this user is a customer
LDAPSRVID	No	NUMERIC(10)	No	Record ID of the LDAP or Active Directory server the user is associated with
COMMDENIED	No	VARCHAR(765)	No	Indicates the license server admin utility security permissions the user does not have: 1 - Can administer all license server functions 2 - Can only manage global users 3 - Can retrieve global users, but cannot log in to admin utility If all three are listed, the user cannot log in to the admin utility or manage users.
ISDISABLED	No	NUMERIC(1)	No	Indicates if the user record is inactive
DTPWDCHG	No	DATETIME	No	Date/time the user password was last changed
PWDMUSTCHG	No	NUMERIC(1)	No	Indicates if the user must change their password at next login
ISDELETED	No	NUMERIC(1)	No	Indicates if the user record is deleted. User records are never removed from the database.
DATEMODIFY	No	DATETIME	No	Date/time the user record was last modified
CANCHNGPWD	No	NUMERIC(1)	No	Indicates if the user can change their password
ALLOWSSO	No	NUMERIC(1)	No	Indicates if the user can use single sign-on
INITIALS	No	VARCHAR(24)	No	User middle initial
ADDLINFO	No	TEXT	No	Reserved for future use
LOCKED	No	NUMERIC(1)	No	Indicates if the user is locked because of failed password attempts
LOCKTIME	No	DATETIME	No	Date/time the user was locked
EXTERNALAUTHID	No	NUMERIC(10)	No	Indicates permissions the user must use external authentication. Foreign key to the EXTERNALAUTH table.
NOEXPIREPSWD	No	NUMERIC(1)	No	Indicates if the user's password does not expire
DTCREATED	No	DATETIME	No	Date/time the user record was created
ALLOWEXAUTH	No	NUMERIC(1)	No	Indicates if the user can use external authentication
CLOUDID	No	VARCHAR(38)	No	For cloud use only

Table: LSUSERAVATAR

Comment Contains user photos

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	User photo unique identifier
USERID	No	NUMERIC(10)	Yes	User the photo is associated with
AVATAR	No	TEXT	No	Photo binary data

Table: MAILMSG

Comment Reserved for future use

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	Reserved for future use
DATASENT	No	DATETIME	No	Reserved for future use
SENDERADDR	No	VARCHAR(63)	No	Reserved for future use
SENDERNAME	No	VARCHAR(63)	No	Reserved for future use
SUBJECT	No	VARCHAR(255)	No	Reserved for future use
BODYTEXT	No	VARCHAR(255)	No	Reserved for future use
MESGPERRECP	No	NUMERIC(1)	No	Reserved for future use
DATELSTTRY	No	DATETIME	No	Reserved for future use
NUMOFTRIES	No	NUMERIC(5)	No	Reserved for future use
MSGHTML	No	NUMERIC(1)	No	Reserved for future use
SENDERROR	No	VARCHAR(255)	No	Reserved for future use
MSGTYPE	No	NUMERIC(5)	No	Reserved for future use
ADDLHEADER	No	VARCHAR(255)	No	Reserved for future use
SENDERID	No	NUMERIC(10)	No	Reserved for future use

Table: MAILRECP

Comment Reserved for future use

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	Reserved for future use
IDMAILMSG	No	NUMERIC(10)	No	Reserved for future use
RECIPID	No	NUMERIC(10)	No	Reserved for future use
NAME	No	VARCHAR(63)	No	Reserved for future use
ADDRESS	No	VARCHAR(63)	No	Reserved for future use
ADDRTYPE	No	NUMERIC(2)	No	Reserved for future use
RECIPTYPE	No	NUMERIC(1)	No	Reserved for future use
CLDNOTSEND	No	NUMERIC(1)	No	Reserved for future use

Table: MSGLOG

Comment Contains server log data

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	Log message unique identifier
LOGDATE	No	DATETIME	No	Date/time the log message was added
LOGLEVEL	No	NUMERIC(1)	No	Indicates the log message level: 1 - Severe Error 2 - Error 3 - Warning 4 - Unusual Activity 5 - Information
MESSAGE	No	VARCHAR(765)	No	Log message
ERRORNUM	No	NUMERIC(10)	No	Indicates the source of the log message
USERNAME	No	TEXT	No	User who performed the action that generated the log message

Table: OLDPASSWORD

Comment Contains previously used passwords for a user. Used when license server options are set to prevent users from reusing passwords.

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	Password unique identifier
USERID	No	NUMERIC(10)	Yes	User the password is associated with
PASSWORD	No	TEXT	No	Old password (encrypted)
DTLASTUSED	No	DATETIME	No	Date/time the password was last used

Table: OLDUSERNAME

Comment Contains previous usernames for a user. Usernames cannot be reused.

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	Username unique identifier
USERID	No	NUMERIC(10)	Yes	User record the username is associated with
USERNAME	No	TEXT	No	Old username (encrypted)

Table: OWNERLCK

Comment Contains the current version of the license server database. Used to determine if an upgrade is required.

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	Owner lock unique identifier
DBVERSION	No	VARCHAR(10)	No	Database version

Table: PHONEEMAILTYPE

Comment Reserved for future use

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	Reserved for future use
TYPE	No	NUMERIC(10)	No	Reserved for future use

Table: PHONENUM

Comment Contains user phone numbers

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	Phone number unique identifier
TYPEID	No	NUMERIC(10)	No	Phone number type: 1 - Work 2 - Fax 3 - Home 4 - Pager 5 - Mobile
USERID	No	NUMERIC(10)	Yes	User the phone number is associated with
NUM	No	VARCHAR(192)	No	Phone number

Table: POOL

Comment Contains license pool information

Columns:

Column name	Primary key	Data type	Not NULL	Comment
ID	Yes	NUMERIC(10)	Yes	License pool unique identifier
NAME	No	VARCHAR(40)	Yes	License pool description
PRODUCTID	No	NUMERIC(10)	Yes	Perforce product the pool is associated with. Foreign key to the PRODUCTNAME table.
TYPEID	No	NUMERIC(10)	Yes	License type in the pool. Foreign key to the LICENSETYPE table.
DELETED	No	NUMERIC(1)	Yes	Indicates if the pool is deleted in the license server admin utility
ISSYSTEM	No	NUMERIC(1)	Yes	Indicates if the pool is the default for the license type. Default pools are automatically created by the license server and cannot be deleted.

Table: POOLLICENSE

Comment Contains information about how many users are associated with license pools. Links the POOL and LICENSE tables.

Columns:

Column name	Primary key	Data type	Not NULL	Comment
POOLID	Yes	NUMERIC(10)	Yes	Pool the license is associated with. Foreign key to the POOL table.
LICENSEID	Yes	NUMERIC(10)	Yes	License the pool is associated with. Foreign key to the LICENSE pool.
COUNT	No	NUMERIC(10)	No	Number of licenses included in the pool-license relationship

Table: PRODUCTNAME

Comment Lists the Perforce products the license server supports

Columns:

Column name	Primary key	Data type	Not NULL	Comment
ID	Yes	NUMERIC(10)	Yes	Perforce product unique identifier
NAME	No	VARCHAR(40)	No	Perforce product name

Table: RDBMSOPTNS

Comment Contains RDBMS connection information

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	RDBMS connection unique identifier
OPTDESC	No	VARCHAR(189)	No	RDBMS connection field name
OPTVALUE	No	VARCHAR(765)	No	RDBMS connection field value

Table: SERVERID

Comment Contains information about the license server installation

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	License server unique identifier
SERVUUID	No	VARCHAR(38)	No	Universally unique identifier (UUID) generated when the license server starts the first time
REFID	No	NUMERIC(10)	No	Additional license server identifier to match the database to the server
SERVSTART	No	DATETIME	No	Date/time the server last started

Table: SERVOPTN

Comment Contains information about license server options

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	Server option unique identifier
OPTDESC	No	VARCHAR(189)	No	Server option description
OPTVALUE	No	VARCHAR(765)	No	Server option value

Table: USERFIELD

Comment Contains the license server user fields that LDAP or Active Directory server fields can be mapped to

Columns:

Column name	Primary key	Data type	Not NULL	Comment
ID	Yes	NUMERIC(10)	Yes	User field unique identifier
FIELD	No	VARCHAR(15)	No	User field description

Table: USERLICENSE

Comment Links user records with assigned licenses

Columns:

Column name	Primary key	Data type	Not NULL	Comment
IDRECORD	Yes	NUMERIC(10)	Yes	User/license combination unique identifier
USERID	No	NUMERIC(10)	Yes	User ID associated with the license
TYPE	No	NUMERIC(10)	No	Indicates the assigned license type: 3 - Helix ALM Issue Management Floating 5 - Helix ALM Issue Management Floating Evaluation 9 - Helix ALM Issue Management Evaluation 10 - Helix ALM SOAP 13 - Surround SCM Named 17 - Helix ALM Issue Management Named 18 - Surround SCM Floating 19 - QA Wizard Pro Floating 20 - QA Wizard Pro Dedicated 21 - QA Wizard Pro Runtime Floating 22 - QA Wizard Pro Runtime Dedicated 23 - Helix ALM Issue Management Named Evaluation 24 - QA Wizard Pro OCR Dedicated 25 - QA Wizard Pro OCR Floating 26 - QA Wizard Pro Load Testing 27 - QA Wizard Pro Load Testing Evaluation 28 - QA Wizard Pro Load Testing Evaluation 34 - Helix ALM/Surround SCM Integration 35 - Helix ALM Test Case Management Floating 36 - Helix ALM Test Case Management Named 37 - Helix ALM Test Case Management Floating Evaluation 38 - Helix ALM Test Case Management Named Evaluation 39 - Helix ALM Test Case Management Evaluation 42 - Helix ALM Requirements Management Floating 43 - Helix ALM Requirements Management Named 44 - Helix ALM Requirements Management Floating Evaluation 45 - Helix ALM Requirements Management Named Evaluation 46 - Helix ALM Requirements Management Evaluation 47 - ALM Data Warehouse 48 - ALM Data Warehouse Evaluation 49 - ALM Data Warehouse Evaluation 50 - Helix ALM suite Floating 51 - Helix ALM suite Named 52 - Helix ALM suite Floating Evaluation 53 - Helix ALM suite Named Evaluation 54 - Helix ALM suite Evaluation 55 - Helix ALM Requirements Reviewer Floating 56 - Helix ALM Requirements Reviewer Named 57 - Helix ALM Requirements Reviewer Floating Evaluation 58 - Helix ALM Requirements Reviewer Named Evaluation 59 - Helix ALM Requirements Reviewer Evaluation 60 - Resource Thief Floating 61 - Resource Thief Floating Evaluation 62 - Resource Thief Evaluation 63 - Resource Thief Dedicated 64 - Defect Scribe Floating 65 - Defect Scribe Dedicated 66 - Defect Scribe Floating Evaluation 67 - Defect Scribe Evaluation
NAMEDSNID	No	NUMERIC(10)	No	Named license assigned to the user
POOLID	No	NUMERIC(10)	No	License pool the user is assigned to