LexBuild Database Tables

• TABLE - EUI:

  This table is used for generating EUI in LexBuild.

  Field	Type	Null	Index	Default	Extra	Description
  id	INT	No				EUI number

• TABLE - USERS (JavaDb) USER (MySql):

  This table stores information for all users in this tool.

  Field	Type	Null	Key	Default	Extra	Description
  id	INT	No	PRI	0	Auto_Increment	User's ID
  fullName	VARCHAR(32)	Yes		Null		Full name
  userName	VARCHAR(32)	No		Guest		User name for the account
  password	VARCHAR(32)	Yes		Null		Encrypted Password
  userType	INT	No		0		Account type: 0: inactive 1: user 2: manager 3: administrator
  eMail	VARCHAR(32)	Yes		Null		Email address
  office	VARCHAR(20)	Yes		Null		Office phone number
  home	VARCHAR(20)	Yes		Null		Home phone number
  eNotify	VARCHAR(8)	No		True		Notified by Email
  lastLogin	VARCHAR(10)	Yes		Null		Last login date (use for review list)

• TABLE - LEX_RECORD:

  This table stores all information for all (approved) lexical record in lexicon. lastAction flag represent the status of last action.

  • Create (1): records are new and created.
  • Modify (2): records exist in Lexicon before and modified.
  • Delete (3): records are deleted.
  • Pending (4): records are submitted for modifying, deleting, restoring and waiting for approval.
  • Restore (5): records were deleted and restored.

  Field	Type	Null	Key	Default	Extra	Description
  eui	VARCHAR(8)	No	PRI	0		EUI
  base	VARCHAR(150)	No	Indexed			Base form
  category	VARCHAR(5)	No	Indexed	noun		Category
  cSignature	VARCHAR(20)	No		None		Creation Signature
  mSignature	VARCHAR(20)	No		None		Modification Signature
  aSignature	VARCHAR(20)	No		None		Approval Signature
  lastAction	INT	No	Indexed	None(0)		Create(1)/Modify(2)/Delete(3)/Pending(4)/Restore(5)
  cDate	VARCHAR(10)	Yes		Null		Created date
  mDate	VARCHAR(10)	Yes		Null		Modified date
  aDate	VARCHAR(10)	Yes		Null		Approved date
  lexRecord	LONG VARCHAR (TEXT)	No				Lexicon Record

  Sometimes, there is an operation mistake to make eui=E0000000 and this required manual fix by
  delete from LEX_RECORD where eui='E0000000'

  Other useful SQL command:
  select count(*) from LEX_RECORD where mSignature='mccreedy' and mDate like '2017%'
select count(*) from LEX_RECORD where cSignature='nace' and cDate > '2016-07' and cDate < '2017-01'
select count(*) from LEX_RECORD where lastAction != 3 and lexRecord like '%class_type=TBD%'

• TABLE - LEX_RECORD_TEMP:

  This table stores all information for all records waiting for approval. It includes newly added lexical records, modified lexical records, and deleted lexical records, and restored records. Records are deleted from this table after they are approved. In other words, this table should be empty if all records are marked as approved.

  Field	Type	Null	Key	Default	Extra	Description
  eui	VARCHAR(8)	No	PRI	0		EUI
  base	VARCHAR(150)	No	Indexed			Base form
  category	VARCHAR(5)	No	Indexed	noun		Category
  cSignature	VARCHAR(20)	No		None		Creation Signature
  mSignature	VARCHAR(20)	No		None		Modification Signature
  aSignature	VARCHAR(20)	No		None		Approval Signature
  lastAction	INT	No	Indexed	None(0)		Create(1)/Modify(2)/Delete(3)/Restore(5)/lock+create(11)/lock+modify(12)
  cDate	VARCHAR(10)	Yes		Null		Created date
  mDate	VARCHAR(10)	Yes		Null		Modified date
  aDate	VARCHAR(10)	Yes		Null		Approved date
  lexRecord	LONG VARCHAR (TEXT)	No				Lexicon Record

• TABLE - LEX_RECORD_DENY:

  This table stores all denied records during the approval process. In general, all submitted lexRecord are validated by linguists and should be approved. In case there is a mistake, the manager should contact (eMail) the linguist to modify the submitted lexRecords for approval. Deny should not be used in practice. This table keeps records for all denied submission.

  Field	Type	Null	Key	Default	Extra	Description
  eui	VARCHAR(8)	No	PRI	0		EUI
  base	VARCHAR(150)	No	Indexed			Base form
  category	VARCHAR(5)	No	Indexed	noun		Category
  cSignature	VARCHAR(20)	No		None		Creation Signature
  mSignature	VARCHAR(20)	No		None		Modification Signature
  aSignature	VARCHAR(20)	No		None		Approval Signature
  lastAction	INT	No	Indexed	None(0)		Create(1)/Modify(2)/Delete(3)/Restore(5)
  cDate	VARCHAR(10)	Yes		Null		Created date
  mDate	VARCHAR(10)	Yes		Null		Modified date
  aDate	VARCHAR(10)	Yes		Null		Approved date
  lexRecord	LONG VARCHAR (TEXT)	No				Lexicon Record

• TABLE - INFL_VARS:

  This table stores all inflectional variables of lexical records. This table is used for finding the close match. In order to find the best results of close match, this table should be updated with the latest Inflectional variables (INFL_VARS_TEMP).

  Field	Type	Null	Key	Default	Extra	Description
  inflVarLc	VARCHAR(150)	No	Indexed	0		Inflectional variables, Lowercase
  inflVar	VARCHAR(150)	No		0		Inflectional variables
  cat	INT			0		Category
  inflection	INT			0		Inflection
  eui	VARCHAR(8)	No	Indexed	0		EUI
  unInfl	VARCHAR(150)	No		0		Uninflected variables
  citation	VARCHAR(150)					Citation form

• TABLE - INFL_VARS_TEMP:

  This table stores all inflectional variables of lexical records which are submitted for approval. This table is used in LexAccess and close match. The content of this table should be moved to INFL_VARS when the record is approved.

  Field	Type	Null	Key	Default	Extra	Description
  inflVarLc	VARCHAR(150)	No	Indexed	0		Inflectional variables, Lowercase
  inflVar	VARCHAR(150)	No		0		Inflectional variables
  cat	INT			0		Category
  inflection	INT			0		Inflection
  eui	VARCHAR(8)	No	Indexed	0		EUI
  unInfl	VARCHAR(150)	No		0		Uninflected variables
  citation	VARCHAR(150)					Citation form

• TABLE - NEW_TERMS:

  This table stores terms (base) for new lexical records. These new terms come from nominalization or other field while build the Lexicon. Basically, these new terms should be added into Lexicon and this table should be empty.

  Field	Type	Null	Index	Default	Extra	Description
  id	INT	No	Indexed	0	Auto_Increment	New term's ID
  term	VARCHAR(150)	No	Indexed	0		Inflectional variables
  cat	VARCHAR(5)			0		Category
  eui	VARCHAR(8)			0		Ref. Eui
  sourceNotes	VARCHAR(60)			0		Notes

• TABLE - LEX_RECORD_COMMENTS:

  This table is used for the comments on lexical records. These comments are used internally between lexBuilder and don't go to public. One record is used for one Eui.

  Field	Type	Null	Index	Default	Extra	Description
  eui	VARCHAR(8)	No	PRI	0		EUI
  mDate	VARCHAR(10)	Yes		Null		Last Modified date
  modifier	VARCHAR(20)	No		None		Last modifier
  comments	LONG VARCHAR (TEXT)	No				Comments