Nodes of Table

Nodes of table definition. It contains not only well known attributes like fields and keys but also virtual fields, join table and scripts. Those are features which usually coded in functions. As they are integrated in table definition in X-TEA Editor/Driver, each function will become thin and typical. Consequently it is getting easier to maintain whole system.

Table List
Select this node to list table definitions in content pane. Use context-menu to add a new definition of table. You can change subsystem of tables by drag-dropping them into tables list of target subsystem.

Table Definition
Select the node to edit properties of table in content pane. The icon is variable according to table module status. If it is as , the table module is not created yet for the definition. If it is as , table module and definition is not synchronized.

Editor Example


Table Definitions in Detail
Basic Attribute...ID, name etc.
Fields...Field definitions
Keys...Keys and indexes of table
Join Tables...Tables refered to by this table
Scripts...Scripts executed before/after table operations
Where Used...Elements using this table
Data...Utility to edit table records

< Basic Attributes>
Table ID - The identifielr of the table definition. Use capital letters and underbar(_). You can not change ID once definition registered.
Table Name - Descriptive name of the table.
Module ID - Physical name of the table module. Default value is "*ID" which means ID is applicated to the name.
Primary Key - Representative unique key of the table. This is a requrired attribute to process table by X-TEA Driver. You can not change primary key of the table with module created. Delete the module to edit primary key then.
Database - Select one of databases listed which are definind in System node. Note that sub-DB is for read-only tables.
Update Counter - This is a numeric field used for optimistic locking of table records. Default value(*Default) represents the INTEGER field named "UPDCOUNTER" which is hidden in X-TEA Editor. You can cancel optimistic locking by specifying the value as "*None" although it is not recommended.
Module Check - The button to show dialog to operate table module. You can create, alter and drop table and also check consistency of table module on the dialog. Use SQL Console to drop unique constraint of tables.
Remarks - Edit as needed.


< Fields>
The area to edit fields definitions of the table. Use context menu of the field list to add/delete fields. Drag rows on the list to change the listing order. By selecting a specific row of field you can edit various properties under-mentioned.

Field ID - The physical name of the field. Only capital letters are accepted and you can not change the value once you added the field.
Physical Field - Represents the field is physically stored on table records. Uncheck the box if you need to set it as a virtual field.
Data Type - Select one of basic data types listed in the box. Various types like CHARAVARCHAR(*1)AINTEGERADATETIME(*2) are supported. Refer to Data Type Mapping by DBMS. Note that you can not change data types of fields consisting of PK.
Size, Decimal, Minus Accepted, Edit Code - Properties related length of the field. Some of them are for numeric types only.
File Type Field - A property for BYTEA field of PostreSQL. The field specified here represents file type extension of the binary data(encoded by Base64) stored in the BYTEA field.
Field Name - Descriptive name of the field. Defaul value of field caption.
Column Name - Default value of column caption. If blank, field name will be applied.
Nullable - If the field should accept null value, check this box. Null value here means blank for string fields, zero for numeric fields and null for date type fields.
No Update - If checked, the field will be presented an non-editable field on updating functions.
Ext-Type - Specify extended data type for the field. Each means as mentioned below.
* None
* 2Bytes Char
Selectable only for CHAR fields. If selected, FEP will be activated in double byte mode when field is focused on applications.
* Kana
Selectable only for CHAR fields. If selected, FEP will be activated in Japanese kana mode when field is focused on applications.
* Auto-No
Selectable only for CHAR fields. Select this with its numbering key ID. The value of the field will be set automatically when records of the table are created by applications.
* U.Def-Code
User defined code. Selectable only for CHAR fields. Select this with its code ID which is registered in the table of User Defined Codes. If selected, list box of values for the code ID is presented on the applications.
* URL
Selectable only for CHAR fields. If selected, the field is processed as URL. Namely it is presented in blue with underline on applications and if you click on the field, corresponding program will be launched.
* ZIP No
Selectable only for 8 byte CHAR fields. ZIP No here means Japanese postal code formatted as "0000-000".
* Linked Add.
Selectable only for fields of CHAR or VARCHAR. Specific web service is called to get detail address according to ZIP No mentioned above.
* Phone
Selectable only for CHAR fields. If selected, input value will be validated to be consist of numbers and "-".
* Boolean
Selectable only for CHAR fields. Specify boolen values like "T;F"(former represents true and latter does false. No need to put quotations). The field will be presented as a check box in applications. When you use bool of PostgreSQL, define the field as CHAR(1) and specify Boolean with value of "true;false".
* Year Month
Selectable only for CHAR(6) fields. If selected, list box of year/month will be presented for the fields on the applications.
* Fiscal Year
Selectable only for CHAR(4) fields. If selected, list box of fiscal year will be presented for the fields on the applications. Fiscal year is controled by value of hFIRST_MONTHh registered in the table of System Variants.
* Fiscal M.No
Fiscal month number which means the month order counted from hFIRST_MONTHh registered in the table of System Variants. For example if hFIRST_MONTHh is 4(which means April), fiscal month number of June is 3. If selected, list box of year/month will be presented for the fields on the applications. You can use some Session Methods to process fiscal month number.
* Image File
Selectable only for CHAR fields. If selected, value of the field is recognized as the name of image file. The applications will search image files to display on them. Folder of image file is specified in Image Folder of System node.
* List Value
Selectable only for CHAR fields. Specify values like "Value1;Value2;Value3"(No need to put quotations). The field will be presented as a list box with those values in applications.
Appropriate Usages of User-Defined-Code, Boolean and List-Value
User-Defined-Code, Boolean and List-Value are suitable for those fields which acceptable values are limitted abouts 10 in their variation. Note the appropriate usages of them. User-Defined-Code is for those fields which values are registered in tables by users. Whereas values of List-Value field and Boolean field will be prescribed on the field definition using X-TEA Editor. And Boolean(presented as check-box in applications) is not suitable for fields which can be used as listing criteria for function types XF100, XF110, XF300. It is because you usualy need to specify the criteria value as not only true and false but ignore which is not covered in check-box components.

Remarks - Edit as needed.
Where-Used - Element list which is used by this field. You can not delete the field definition if any are listed.

*1.If VARCHAR or LONG VARCHAR is selected, these fields are presented as text-area components in flat panel applications. In edit mode you can not input tab character by keying Enter as it is reserved for error-cheicking for the panel applications. Instead use AlT+Enter to input tab character in the text.
*2.If DATE is selected, these fields are presented as text field with button to show calendar component. If you have 4 byte time field with value like "17:55" define the field as INTEGER(4) or SMALLINT(4) with edit-code of Time(HH:MM). Although you can not create MySQL table with DATETIME fields, you can synchronize table definition by specifying the field as CHAR(23) in X-TEA Editor.


< Keys>
The area for definitions of table key and index. Right-click on the list to acces to context menus for adding and deleting. Select a specific row to edit the definition. Note that once you create the table module you can not change PK. This means that you need to delete table module to edit PK. By checking "Process last key field as row number"for PK, the value of last integer field will be automatically set when inserting table records.

Procedures to change PK
As mentioned above, you can not change PK of the table with its module already created. Even if table module is not created, you can not change PK if any definitions of function are existing which process the table. Confer to the following procedures to change PK of tables with records already stored.
1. Export records of Table A into Excel
2. Copy the definition of Table A and paste as Table A' (no need to create module)
3. Change PK of Table A' as required
4. Refer to functions using Table A and edit them to replace into Table A'
5. If no functions left using Table A, delete the module of Table A
6. Change PK of Table A and re-create the modle
7. Edit functions to replace Table A' into Table A
8. Import Excel data into Table A


< Join Tables>
The area to define join-tables for the subject table. Records on join-tables are automatically read when records of subject table are read by functions. Note that only values of those defined as join-fields are read. Join-tables are defined with join-key composed of physical fields on the subject table. These are called "static join". On the other hand you can also define "dinamic join" with join-key composed of virtual fields of the subject table and join fields of preceding join-table. Right click on the list to add/delete a join table definition. Select a specific row to edit various properties of join table under-mentioned.

Join Table - ID/Name/Primary Key of join-table. You can not change them once you have added.
Alias - Alias of join-table. You can not change them once you have added.
Join-To Key - Unique key(PK or SK) of join-table for this join definition. You can not change them once you have added.
Join-With Key - Access key to the record of join-table. They are composed of fields of the subject table or predefingly defined join fields on join-tables.
Join Order-By - Required if join-tables have range key.
Record Exist/Optional - If you check this box, Driver will ignore those join-tables which had no records to be joinned.
Join Fields - Fields to be joinned on the join-table. Note that although you can select virtual fields on join tables as join fields, their values are left initialized. If you need their values for your functions, put scripts on the subject table to setup values of virtual fields of join-table.


< Scripts>
The area to edit JavaScript texts. These are executed before/after operations of the subject table. They have various purposes like validating input values, setting values of virtual fields, setting editable/non-editable of fields, and so on. Right click on the list to add/delete a script. Select a specific row to edit properties of script under-mentioned.

Name - Name of the script which has to be unique in the subject table.
Execute When - Select timings when the script is executed at. Some of them also require if it is executed before/after reading join-tables. Check "Hold" to hold being executed for tests.
Script - The area to edit JavaScript codes. Push the button to edit full screen mode. Legends are given to indicate field ID and field property list available in the script text. You can use keys of Ctrl+I(Indent), Ctrl+N(Name mode), Ctrl+/(Comment), Ctrl+Z/Y(Undo/Redo), F3(Scan string), F7(Check syntax error). And you can code with session object and instance object. Followings are sample codings.

- Sample1: Setting value of FIELD1 on TABLE1 by multiplication
// Normal style //
TABLE1_FIELD1.value = TABLE1_FIELD2.value * TABLE1_FIELD3.value;

// Alternative style using a method of instance object //
var field1 = instance.getFieldObjectByID('TABLE1', 'FIELD1');
var field2 = instance.getFieldObjectByID('TABLE1', 'FIELD2');
var field3 = instance.getFieldObjectByID('TABLE1', 'FIELD3');
field1.value = field2.value * field3.value;
- Sample2: Setting value of FIELD1 on TABLE1 by addition
TABLE1_FIELD1.value = Number(TABLE1_FIELD2.value) + Number(TABLE1_FIELD3.value);
- Sample3: Set color of field according to its value
if (TABLE1_FIELD1.value < 0) {TABLE1_FIELD1.color = 'red';}
- Sample4: Field Validation
if (TABLE1_FIELD1.value < 100) {TABLE1_FIELD1.error = 'Error as the value is less than 100';}
- Sample5: Edit Control (1)
if (TABLE1_FIELD1.value == 'A') {TABLE2_FIELD2.editable = false;}
- Sample6: Edit Control (2)
if (instance.functionID == 'ABC100') {TABLE1_FIELD1.editable = false;}
- Sample7: Comparing date values
var date1 = new Date(stringDate1);
var date2 = new Date(stringDate2);
if (date1.getTime() > date2.getTime()) { ... }
- Sample8: Presenting the field as ListBox by setting array values
TABLE1_FIELD1.valueList = ['1,000', '2,000', '3,000'];
- Sample9: Common mistakes
// WRONG //
TABLE1_FIELD = variant1; ...
if (TABLE1_FIELD1 == '') {TABLE1_FIELD1.error = 'Error as the value is blank';}

// RIGHT //
TABLE1_FIELD.value = variant1; ...
if (TABLE1_FIELD1.value == '') {TABLE1_FIELD1.error = 'Error as the value is blank';}
- Sample10: Check value of a variant to debug
showMessage('Value of variant1:' + variant1);

< Where Used>

Elements using this table are listed. Select a specific row and right-click to jump to the definition of the element.


< Data>
The area called "Data Maintenance Utility" to inquire/edit records of the table. It comes visible when the definition is synchronized to the module and connection to database is active. Because of dificulty of handling values, the utility ignores BLOB fields on purpose.

Push the button "List" to list records with values of "Select Where" and "Order By" as needed. Right-click on the list to export into Excel sheet. Exporting path is specified by "Output Folder" of System Node.

The button of "Compress" is to compress deleted record space. It is activated only for JavaDB(Derby) database. "List Log" is to list user operations for the table in Driver sessions.

"Import" is to import text data into the table. Prepare plain text file with tab-separated values: the first row is for field IDs(or Names) and following rows are for field values. If the record exists with value(s) of primary key, it will be updated and if one does not exist, it will be inserted..

H O M E