This is an old revision of the document!
The NovoMotion Schema Editor is used to create or maintain the definition of the elements of a schema generator used with the the SQL environment. Once the user has logged in through the NovoMotion U2 Login, the user will access the Schema Editor through the U2 Options view.
The U2 Options tab allows for the following information to be entered:
Data Type | Description |
---|---|
Variable Character | used for characters with a length greater than 1000 |
Character | used for any string of data up to 1000 characters |
Date | used for any date field |
Integer | used for any numeric field that does not contain decimals |
Numeric | used for most non-integer numbers |
Decimal | inherently the same as numeric |
Floating Point | used when the precision and scale are not distinct |
Key Word | Description |
---|---|
@MULTIVALUEID | Current suggestion is to use multiple primary keys instead of this. However, it is used to explode the controlling attribute of a multi-valued set @MULTIVALUEID:part1:part2 |
@ORDINAL | Assigns a numeric sequence to the value - @ORDINAL:mvinfo. See Ordinal considerations below. |
@ACTION | Used to specify a delete or insert event |
@LITERAL | Used to pass static data through the schema - @LITERAL:1 |
@FIELD | Field based on info - @FIELD:info:delimiter:occurrence:numberOfOccurrences |
@TM | Allows selection of a specific table master - @TM:tablenbr |
@CHANGE | Used to change a specific string from one literal to another - @CHANGE:info:from:to |
@CONVERT | Used to change multiple characters from one character to another - @CONVERT:info:from:to |
@ICONV | Used to convert a string to internal format - @ICONV:mvinfo:conversion |
@OCONV | Used to convert a string to external format - @OCONV:mvinfo:conversion |
@FMT | Used to format a string - @FMT:mvinfo:statment |
@NOT | The reverse of a string evaluation - @NOT:mvinfo |
@TRIM | Removes errant spaces from a string - @TRIM:mvinfo |
@NULL | Returns a null character - @NULL |
Additionally, the SQL Options tab allows the following information to be entered:
The Post Job Options tab allows the user to enter the following information:
The function contains the following drop down menus:
Action | Description |
---|---|
New | used to create a new schema |
New from Template | applies a previously saved template for ease of entry |
Open | used to access an existing schema - including one that was auto-generated |
Save | save changes to a schema - using the existing schema name |
Save As | save changes to a schema - using a new schema name |
Save As Template | save current schema as a template for later use |
Import Schema | used to import a schema that was generated on another system |
Export Schema | used to prepare a schema for distribution to another system |
Delete | remove the current schema |
Close | close the current schema |
Exit | leave the NovoMotion Schema Editor |
Logout | return to the U2 Login screen |
Action | Description |
---|---|
Add Action Row | allows user to automatically add the @ACTION to a schema - @ACTION is used for tables that require row replacement |
Apply Template | apply a previously saved template to the existing schema |
Action | Description |
---|---|
Generate SQL Script | used to create the SQL script |
Validate Schema | checks the schema for valid login information |
Apply Schema to selected Schema | apply a previously saved template to the existing schema |
Launch Interface Control | starts the Interface Control for the existing U2 Login |
Launch File Loader | starts the File Loader - see screen shot below |
Note: The Generate SQL Script is used when one wants to generate script to create the SQL table (See the Creating a SQL script information) to avoid the necessity of creating the SQL table by hand. This is often used multiple times during the development of new schema to perfect the information between U2 and SQL.
The SQL script can be pasted into SQL Management Studio and executed. It will drop any existing SQL table of the name defined in the schema and re-create it with the columns defined in the schema. It does this for both the base SQL table and - if an @ACTION row exists - for the parallel SQL trigger table. See the sql_server_management_studio_example if you would like to review notes about the SQL Server tasks.
Additional Usage Notes: If a SQL table has already been established and the schema needs to change, the user has the following options:
Action | Description |
---|---|
About Schema Editor | used to check the version and build of the editor |