Introduction

Dovetail SchemaEditor is an important part of the Dovetail Agent system. It allows the database schema to be modified and extended to allow for additional features and customizations.

Features:

  • Create, modify, and delete database objects (tables, views, columns, relations, joins, indexes)
  • SchemaScript enables a simple, imperative way of specifying the changes to be made to the database schema
  • Uses standard XML files for defining the schema and schema changes
  • Ships with XML Schema Definition (XSD) files, so that you can use industry standard tools to modify and validate schema files, and be confident that the XML is valid
  • Based on the .NET Managed Code Environment - Since Dovetail SchemaEditor is based on the Microsoft .NET Framework, it inherently gains powerful features such as an object-oriented, managed code, highly secure operating environment.
  • Enhanced Logging - Dovetail SchemaEditor takes advantage of the powerful and flexible logging features of log4net. This makes it easier to troubleshoot and diagnose problems that may arise as well as audit and monitor the detailed processing of Dovetail SchemaEditor.

In This Chapter

Getting Help

Next

Getting Help
Introduction

Did this topic help you? Email us your feedback.
Last updated: 4/1/2009 3:11:27 PM
© by Dovetail Software, Inc.


Getting Help

If you need additional help beyond this user guide, you can use any of the following resources:

  • Dovetail SelfService - Use our online customer support site to notify us of a support issue, to manage your Dovetail products, to seek sales support, or to search the knowledge base.
  • Knowledge Base Articles - Search our online knowledge base for existing issue resolutions.
  • Product Documentation - Find Dovetail's most up-to-date product documentation.
  • Blog Posts - Dovetail developers often publish useful posts about SchemaEditor
  • Contact Us - Contact us directly if you need further assistance.
    • support@dovetailsoftware.com
    • phone 800-684-2055 or 512-610-5400

See Also

Introduction

Next

Upgrade Guide and New Features
Getting Help

Did this topic help you? Email us your feedback.
Last updated: 1/5/2011 5:07:18 PM


Upgrade Guide and New Features

This section describes the differences between versions of Dovetail SchemaEditor.

In This Chapter

Version 2.3.3

Version 2.3.2

Version 2.3.1

Version 2.3

Version 2.2.1

Version 2.2

Version 2.1

Version 2.0

Version 1.5.1

Version 1.5

Version 1.4.5

Version 1.4

Version 1.3

Version 1.2

Version 1.1

Version 1.0

Next

Installation and Configuration

Version 2.3.3

Added an option for Oracle users to change how removing columns works. See oracleDropColumnStrategy for more details.

Additionally, extra validation has been added around column drops and modifications:

  • SchemaEditor will no longer fail when a column is being removed from the schema but has already been manually removed from the database.
  • On Sql Server, when a column being removed from the schema is involved in one or more database indexes. The preview or apply operation will fail and report the indexes to which the affected columns belong.
  • On Oracle, when a column being modified (length, or decimal precision) is involved in one or more functional database indexes. The preview or apply operation will fail and report the functional indexes present affecting the change.

Fixed an Oracle SQL statement ordering bug when modifying a non searchable column to be searchable.

Version 2.3.2

Bug Fixes:

On Oracle changing the width of a table column that is searchable is now updating the width of both base column and "s_" columns.

Oracle 8 has had two issues corrected which were preventing it from working correctly.

  • SQL inserts and updates of text fields could fail due to data typing issue.
  • Creation of tables and views were emitting DDL which was unsupported by Oracle 8.

Objid generation for ADP tables was occurring when it was not necessary. Objid generation for ADP tables will now only be done when the database being modified is Amdocs 6 (or higher).

Version 2.3.1

Installer now enforces required versions of the Microsoft .Net framework. Requires either: 3.5 SP1, or .Net 4.0 Full framework versions.

Bug Fix:

Resolved an intermittent issue with the order in which schema changes were being applied.

Version 2.3

Added configuration setting for Oracle 11.

Added support for ignoring problematic database views via configuration using the ignoreViews Object.

Version 2.2.1

Bug Fix:

Indexes can now be added or updated when using an indexed column/relation name where the casing of the field name(s) does not match the casing of the field definition on the table.

Version 2.2

Added more context to error messages that appear when schema differences are invalid. This should make it easier to find the troubled bit of schema when certain difference validations fail.

Fixed a data access bug which could occur in certain scenarios when adding a column to an table which was also being updated.

When creating table fields in Oracle the Long Text field now defaults to the CLOB data type when they are supported by the database.

Database access support libraries have been updated.

  • Added configuration setting for Microsoft SqlServer 2008.

Version 2.1

To correct instances where the database schema does not match the Clarify schema, DDL will be emitted when dropping Tables, Views, Indexes, and Synonyms (Oracle), only if they exist in the database schema.

Fixed a bug introduced in 2.0 where Dovetail SchemaEditor could only be run from the install directory.

Added a logging appender, which during a preview action, receives the SQL statements that would be used during an apply operation. These statements are intended to be informational and not executed by a database administrator.

Version 2.0

This major release adds support for schema meta-data changes introduced in versions of Amdocs 6 and higher.

Dovetail SchemaEditor now requires version 3.5 of the .Net framework.

Database Specific SQL for SQL Views

Customers utilizing Amdocs 6 and later versions of the database can now specify SQL for sql views that targets a specific database provider. This new functionality is exposed to Data Dictionary XML as the databaseSpecificSql element. Likewise when using Schema Script addSqlView or updateSqlView commands sql elements can specify a target database.

File Formats updated to support new Amdocs 6 functionality

To support new functionality in Amdocs 6 and newer databases, we have updated the namespace of the XML documents used to apply schema changes. Data Dictionary and Schema Script XML documents have new namespaces and matching .xsd files for validation. Dovetail SchemaEditor will still work with old versions of the Schema Script and Data Dictionary XML documents. However, users targeting Amdocs 6 or newer databases will need to use the new file formats to take advantage of new features. For more details please see Schema Script Namespaces or Data Dictionary Namespaces.

Bug Fixes

  • Corrected a bug where SQL Views with base tables could be created incorrectly depending on the order of the view columns.

Version 1.5.1

Corrected a bug where it was possible using Schema Script to add duplicate join's to a view. This would cause an error when applying schema changes to the database.

Version 1.5

Requirement Change:

  • SchemaEditor now requires .Net 2.0 with Service Pack 1.

Bug Fix:

  • Corrected a validation bug that occurred when running against Oracle databases where an existing many to many association table was close to exceeding the Oracle 30 character limit on table names.
  • To be compatible with Clarify applications, we have corrected a class of problems related to the ordering of objects in the Clarify schema metadata.
    • Removing a column from a table requires that the remaining columns on the table have their orders (spec_rel_id) updated to be contiguous.
    • Removing a view column from a view requires that the remaining view columns on the view have their orders (spec_rel_id) updated to be contiguous.
    • Removing a relation from a its tables require that the remaining relations on those tables have their orders (spec_rel_id) updated to be contiguous.

      Note: When re-ordering a many to many relation the 'mtm_' association table will be renamed to follow the Clarify convention.

    • A view column referencing a user defined column cannot be ordered before view columns referencing predefined fields. When this occurs the 'user defined' view columns will be moved to the end of the view column ordering.

To upgrade to this release:

  • Uninstall the existing version of Dovetail SchemaEditor
    Start - Programs - Dovetail Software - Dovetail SchemaEditor - Uninstall Dovetail SchemaEditor
  • Install the new version of Dovetail SchemaEditor using the supplied Microsoft Installer (MSI)

Version 1.4.5

Bug Fix:

  • Fixed a bug in ManyToManyRelationDdlGenerator where Grant statements were not being created for the many to many association table.

Version 1.4

Enhancement:

  • Adds generation of stored procedures for views and sqlviews to achieve better compatibility with early versions of Clarify Classic Client.

Version 1.3

Enhancement:

  • Adds generation of stored procedures for tables and relations to achieve better compatibility with early versions of Clarify Classic Client.

Bug Fix:

  • For better compatibility with Clarify's tools, SchemaEditor no longer sets the isSearchable field for columns when applying schemas to MS SQL Server. For more information, see our knowledge base solution.
  • StorageLocation was not working when applying Schema Script.

Version 1.2

This release adds support for SQL views. View the SQL Views section for more details.
In addition, the performance of schema scripting has been improved. The load on the database and the network during schema script processing has been reduced.

Data Dictionary XML changes:

  • sqlView and sqlViewColumn elements are now supported.

Schema Script XML changes:

  • alias has been added as an optional child element of updateViewColumn.
  • addSqlView, addSqlViewColumn, updateSqlView, and updateSqlViewColumn elements are now supported.

Bug Fixes:

  • Better handling of views with implicit cross joins
  • Fixed a problem in the ordering of objects, which could cause viewColumns to have the the same spec_rel_ids, and could also cause joins to have the wrong spec_rel_id for their relation
  • Fixed a problem with metadata (ADP) generation of joins when the relation went to and from the same table

Version 1.1

This release adds support for schema scripting. This allows users to simply script only the changes to be made to the schema, as opposed to representing the entire schema. View the Working with Schema Script section for more details.

This release also adds support for Microsoft SQL Server 2005 databases.

Data Dictionary XML changes:

  • The kind attribute on the join object has been simplified. Previously, there were three join kinds: Inner, Left, and Right. Now, there are only two: Inner and Outer. Refer to the join reference for additional details and examples.
  • The OneToOnePrimary type attribute on the relation object has been changed to OneToOne.
  • The xmlns attribute on the schema object has been updated to account for the above listed changes to the XML.

.SchemaEditor file changes:

  • The schemaFilePath element has been renamed to inputFilePath.
  • An exportFilePath element has been added.
  • The xmlns attribute on the schema object has been updated to account for the changes to the inputFilePath attribute.

Bug Fixes:

  • New OneToOne relations were being added with the wrong cardinality to the metadata (ADP) tables. A OTOP was being added to the adp_sch_rel_info table as OTOF, and OTOF's were being added as OTOP. Notice that it was backwards. If you added a new OneToOne relation with version 1.0, please contact Dovetail Software for details on how to correct this issue. This issue has been fixed in version 1.1.

Version 1.0

Initial Release

Upgrade Guide and New Features

Did this topic help you? Email us your feedback.
Last updated: 3/23/2009 9:39:15 AM


Installation and Configuration

This section describes how to install and configure SchemaEditor.

In This Chapter

System Requirements

Permissions

Installing SchemaEditor

Limitations

Differences

Configuring SchemaEditor

Next

System Requirements
Installation and Configuration

Did this topic help you? Email us your feedback.
Last updated: 8/16/2007 2:35:15 PM


System Requirements

Dovetail SchemaEditor requires the following:

Operating System
  • Microsoft™ Windows™ XP Professional
    - OR -
  • Microsoft™ Windows™ Server 2003 or 2008
  • Windows Vista
  • Windows 7

Microsoft™ .NET Framework Runtime

  • Install through Windows Update Or download from Microsoft.
  • Requires either:
    • .Net 3.5 Service Pack 1
    • .Net 4.0 Full (Standalone)

Hardware
  • Intel™ Pentium II (or compatible) 400MHz or faster
  • 256 MB RAM (512MB recommended) or higher
  • 20MB (estimate) free hard disk space or more

This version of Dovetail SchemaEditor supports the following environments:

Clarify™ databases
  • Clarify™ versions 7.0 through Amdocs™ versions 7.5

Database Systems
  • Microsoft™ SQL Server™ versions 2000, 2005, 2008
    - OR -
  • Oracle™ Database Server versions 8, 9, 10, 11

See Also

Installation and Configuration

Permissions

Installing SchemaEditor

Limitations

Differences

Configuring SchemaEditor

Next

Permissions
System Requirements

Did this topic help you? Email us your feedback.
Last updated: 8/12/2011 12:20:12 PM


Permissions

Because Dovetail SchemaEditor modifies the physical schema of the database, the database user who performs these operations must have the proper permissions to be able to make these modifications. Dovetail SchemaEditor will not validate or enforce these privileges - the enforcement of these operations is left up to the RDBMS. Typically, Dovetail SchemaEditor connects to the database as a system administrator, such as the "sa" account.

See Also

Installation and Configuration

System Requirements

Installing SchemaEditor

Limitations

Differences

Configuring SchemaEditor

Next

Installing SchemaEditor
Permissions

Did this topic help you? Email us your feedback.
Last updated: 4/3/2009 12:11:29 PM


Installing SchemaEditor

  1. Run the MSI installation process

    Begin the installation by extracting the contents of the Dovetail SchemaEditor ZIP file into a temporary folder. Run the MSI installation process by double-clicking on the Dovetail SchemaEditor.msi file.

    The installation will then proceed through a series of steps or screens which will either provide information about the installation process, or request information necessary to install Dovetail SchemaEditor.
  2. Edit the SchemaEditor file

    Once the MSI installation package has completed, you must edit the SchemaEditor file for Dovetail SchemaEditor. You must manually generate this file, as specified in the .SchemaEditor files section prior to editing. The default SchemaEditor file is named Default.SchemaEditor and is generated in the installation directory (typically C:\Program Files\Dovetail Software\Dovetail SchemaEditor).
  3. Install the license

    You should have received a license file (*.fclic) for Dovetail SchemaEditor either with your distribution or separately. If you have not received a license for Dovetail SchemaEditor, please contact us to obtain one.

    To install your Dovetail SchemaEditor license, copy the license file to the Dovetail SchemaEditor install directory. By default, the install directory will be C:\Program Files\Dovetail Software\Dovetail SchemaEditor\

    Dovetail SchemaEditor will simply read and validate the license from the license file.

See Also

Installation and Configuration

System Requirements

Permissions

Limitations

Differences

Configuring SchemaEditor

Next

Limitations
Installing SchemaEditor

Did this topic help you? Email us your feedback.
Last updated: 8/31/2010 5:22:27 PM


Limitations

This section describes SchemaEditor's limitations.

In This Chapter

Amdocs 6 View Additions

Parent Views

Flexible Attributes

Binary Columns

Views with Implicit Cross Joins

Deletes in Schema Script

Next

Parent Views

Amdocs 6 View Additions

New view functionality was added in Amdocs 6. The following schema features are not yet supported by Dovetail SchemaEditor:

View filters

View filters allow schema level definition of where clauses that should be applied to existing views. For example, you could add a View Filter to a view related to cases so that it would only return open cases owned by active employees.

View Cross Joins

View Cross Joins are joins that do not use a where clause to constrain the results. This returns a cartesian product of the two joined tables. More on Cross Joins at Wikipedia.

Union Views

Union views are built on top of existing views and unionize their results into one result set. More on Unions at Wikipedia.

Limitations

Did this topic help you? Email us your feedback.
Last updated: 5/26/2011 11:30:06 AM


Parent Views

With Clarify 12.5, a new concept called parent views has come to existence, which allows views to include fields from other views. For example, the view schdl_tracker_sub_v6 (view #: 6022) contains fields from the view hgsl2hgse (view #: 6016).

Note that there is not a join on the schdl_tracker_sub_v6 view that joins in the hgsl2hgse view.

Dovetail SchemaEditor will not export these parent views from the database when exporting the schema, and these views will not pass the validation step if manually added to a schema file.

Existing views of this type that already exist in the database will be left untouched.

See Also

Limitations

Amdocs 6 View Additions

Flexible Attributes

Binary Columns

Views with Implicit Cross Joins

Deletes in Schema Script

Next

Flexible Attributes
Parent Views

Did this topic help you? Email us your feedback.
Last updated: 4/13/2009 2:40:22 PM


Flexible Attributes

In later versions of Clarify, there is a new concept called Flexible Attributes. If a table (such as the case table) is marked to allow flexible attributes (using the ALLOW_FLEX attribute in Clarify syntax, or the areFlexibleAttributesAllowed attribute in Dovetail SchemaEditor syntax), then Clarify tools will automatically create a new "hidden" table named fa_case, along with relations from table_fa_case to table_case. Clarify tools hide these fa_* tables and relations, and does not include them in their schema file.

Dovetail SchemaEditor will export these fa_* tables from the database when exporting the schema, and they will appear just as other tables do. However, Dovetail SchemaEditor will not automatically create these tables or relations for you if you simply mark a table to allow flexible attributes. If you need to allow flexible attributes for a table, you should create the fa_ table and its associated relations. If you need assistance with this, please contact Dovetail Software.

See Also

Limitations

Amdocs 6 View Additions

Parent Views

Binary Columns

Views with Implicit Cross Joins

Deletes in Schema Script

Next

Binary Columns
Flexible Attributes

Did this topic help you? Email us your feedback.
Last updated: 3/23/2009 11:54:00 AM


Binary Columns

Columns marked as BINARY (isBinaryData=true) are typically not used within the Clarify schema. They are artifacts from early Clarify releases.

Examples of binary columns:

  • prob_ref_array on table_case
  • end_gates on table_probdesc
  • dkt_layout on table_user

Dovetail SchemaEditor will not export binary columns from the database when exporting the schema.

Existing binary columns that already exist in the database will be left untouched.

See Also

Limitations

Amdocs 6 View Additions

Parent Views

Flexible Attributes

Views with Implicit Cross Joins

Deletes in Schema Script

Next

Views with Implicit Cross Joins
Binary Columns

Did this topic help you? Email us your feedback.
Last updated: 3/23/2009 11:54:02 AM


Views with Implicit Cross Joins

Views typically have view columns whose data comes from a table referenced in one of the joins on that view.

If a view column references a table that is not joined in, then this is an implicit cross join.
If a view does not have any joins, and the view columns do not all come from the same table, then this is also an implicit cross join.

Note: There are baseline views that have implicit cross joins.

For example, consider the baseline view case2rip_dfe:

View Columns:

View Column | Table | Column
case_id | case | objid
dfe_id | disptchfe | objid
rip_id | case | ripbin

Joins:

Join From | Join To
disptchfe.disptchfe2case | case.case_onsite2disptchfe

This view has view columns coming from three tables: case, disptchfe, and ripbin. But there is only one join: from disptchfe to case. This means that the join to ripbin is an implicit cross join. Note that this is not the typical use of views and joins.

Dovetail SchemaEditor will read in these existing views from the database and from a Data Dictionary file.

However, Dovetail SchemaEditor does not fully support modifications of views with implicit cross joins:

  • New views of this type are not allowed to be created
  • These views cannot be modified, unless the implicitness of the cross join is removed.
  • Joins cannot be modified so that it becomes an implicit cross join

Dovetail SchemaEditor does allow these views to be deleted/dropped.

See Also

Limitations

Amdocs 6 View Additions

Parent Views

Flexible Attributes

Binary Columns

Deletes in Schema Script

Next

Deletes in Schema Script
Views with Implicit Cross Joins

Did this topic help you? Email us your feedback.
Last updated: 4/8/2009 4:16:16 PM


Deletes in Schema Script

Dropping/deleting of objects is not supported in Schema Script. A complete schema file must be used if an object is to be deleted.

See Also

Limitations

Amdocs 6 View Additions

Parent Views

Flexible Attributes

Binary Columns

Views with Implicit Cross Joins

Next

Differences
Deletes in Schema Script

Did this topic help you? Email us your feedback.
Last updated: 4/8/2009 4:09:51 PM


Differences

Dovetail SchemaEditor has a few behavior differences when compared to Clarify/Amdocs schema editing tools.

In This Chapter

User Defined

New Column Initialization

Next

User Defined
Differences

Did this topic help you? Email us your feedback.
Last updated: 5/26/2011 12:13:21 PM


User Defined

User defined is the default

By default, any schema additions made using Dovetail SchemaEditor are automatically marked as User Defined. This is different than tools such as ddcomp, where Baseline is the default, and User Defined must be explicitly declared.

See Also

Differences

New Column Initialization

Next

New Column Initialization
User Defined

Did this topic help you? Email us your feedback.
Last updated: 5/26/2011 11:31:51 AM


New Column Initialization

New Columns are not initialized

When new columns are added to an existing table using Dovetail SchemaEditor, they are not initialized.

Clarify/Amdocs schema editing tools (such as ddcomp) will initialize the column.

What does it mean to initialize a column?

Initialization means that all existing rows in the table will be updated, setting the new column's value.

What value does the Clarify/Amdocs tool initialize a column to?

Integer columns will be set to zero.

DateTime columns will be set to 1/1/1753 12:00:00 AM.

String columns will be set to an empty string.

What does the SQL look like when a column is initialized?

Examples:

  • update table_case set x_my_integer_column = 0
  • update table_case set x_my_date_column = '1/1/1753'
  • update table_case set x_my_string_column = ''

What value will a new column have after adding it with Dovetail SchemaEditor?

It's value will be NULL.

Why doesn't Dovetail SchemaEditor initialize the column?

Dovetail has chosen to de-couple the schema change from data changes. This gives more control to the developer as to how and when the column initialization occurs, or if it occurs at all.

The initialization can be a very time-consuming and space-consuming process. On large tables, initializing a column can take hours, and the database also needs to account for rollback of this transaction if it fails, which can consume large amounts of rollback space. If the rollback space fills up, the transaction can fail.

In most applications, it doesn't matter if a column is NULL vs. zero, or NULL vs. empty string.

Does it matter?

Typically, no. Applications that use this data, such as the Clarify Client (and its underlying POMS layer), or Dovetail applications (and the underlying Dovetail SDK), know how to handle NULLs just fine.

Can I initialize the column myself?

Absolutely. Use your favorite SQL tool and initialize the column as you want.

Are there more efficient ways to initialize a column on a very large table?

Yes. Your DBA would be a good resource in this instance.

Additional information is available in the Dovetail Knowledgebase article titled "Efficient way to update all rows in a table", available online at:
http://support.dovetailsoftware.com/selfservice/solutions/show/503

See Also

Differences

User Defined

Next

Configuring SchemaEditor
New Column Initialization

Did this topic help you? Email us your feedback.
Last updated: 5/26/2011 12:16:36 PM


Configuring SchemaEditor

Dovetail SchemaEditor users will most often need to edit the .SchemaEditor file. SchemaEditor files are used to define the configuration for a particular execution of Dovetail SchemaEditor, including database connection information, and schema file definition. Refer to the SchemaEditor files section for more details.

Additional application configuration parameters for Dovetail SchemaEditor are specified in the Dovetail SchemaEditor.exe.config found in the Dovetail SchemaEditor install directory. The main reason you would want to update the <SchemApp> application configuration file would be to customize logging. Dovetail SchemaEditor uses the log4Net logging framework to provide detailed information about Dovetail SchemaEditor's actions. You can find the log4Net configuration in the log4Net section of the SchemaEditor.exe.config file.

See Also

Installation and Configuration

System Requirements

Permissions

Installing SchemaEditor

Limitations

Differences

Next

Working with SchemaEditor
Configuring SchemaEditor

Did this topic help you? Email us your feedback.
Last updated: 4/1/2009 3:26:03 PM


Working with SchemaEditor

This section describes how to work with SchemaEditor.

In This Chapter

Quick Start

.SchemaEditor files

Command Line Options

Working with SchemaScript

Working with a Data Dictionary

Next

Quick Start
Working with SchemaEditor

Did this topic help you? Email us your feedback.
Last updated: 8/16/2007 3:07:23 PM


Quick Start

The general steps for modifying the database schema using Schema Script:

  1. BACKUP YOUR DATABASE
  2. Edit the .SchemaEditor configuration file for your environment
  3. Create a schema script
  4. Preview the changes
  5. Apply the changes to the database

The general steps for modifying the database schema using a Data Dictionary file:

  1. BACKUP YOUR DATABASE
  2. Edit the .SchemaEditor configuration file for your environment
  3. Export the Schema to a File
  4. Modify the Schema File
  5. Preview the changes
  6. Apply the changes to the database

See Also

Working with SchemaEditor

.SchemaEditor files

Command Line Options

Working with SchemaScript

Working with a Data Dictionary

Next

.SchemaEditor files
Quick Start

Did this topic help you? Email us your feedback.
Last updated: 4/8/2009 4:09:44 PM


.SchemaEditor files

.SchemaEditor files are used to define the configuration for a particular execution of Dovetail SchemaEditor, including database connection information, schema file definition, and physical storage definitions. An example .SchemaEditor file ships with Dovetail SchemaEditor, and an example file can be generated by running Dovetail SchemaEditor with the -generate option.

This section describes the configuration file sections:

  • database - Used to configure the database connection
  • inputFilePath - Specifies the name of the input file. This can be either a Data Dictionary file or a Schema Script file.
  • exportFilePath - Specifies the name of the schema file to be generated when performing an export operation.
  • reportFile - Report file generated by preview of the differencing
  • storageLocations - Used to define physical storage locations of tables and indexes
  • ignoreViews - Defines certain views in the database to be ignored.

A common usage is to have one .SchemaEditor file per database. For example, one may have a development.SchemaEditor file, a test.SchemaEditor file, and a production.SchemaEditor file. Within each file is the individual database connection information, individual physical storage locations, etc.

See Also

Working with SchemaEditor

Quick Start

Command Line Options

Working with SchemaScript

Working with a Data Dictionary

Next

database Section
.SchemaEditor files

Did this topic help you? Email us your feedback.
Last updated: 6/6/2011 5:02:37 PM


database Section

Database configuration

Parameter Name

Required

Default

Description

type

Yes

MsSqlServer2000

Specifies the type of database. Valid values are:

  • MsSqlServer7
  • MsSqlServer2000
  • MsSqlServer2005
  • MsSqlServer2008
  • Oracle8
  • Oracle9
  • Oracle10
  • Oracle11

connectionString

Yes

N/A

An ADO.NET connection string to use with the specified DB type (or default if none is specified). The default provider is MSSQL and the connection string must be useable by the System.Data.SqlClient.SqlConnection class. Consult the .NET framework documentation for specifics about what parameters are allowed in the connection string. Please consult the documentation for other ADO.NET providers if you plan on using a different data provider type (like Oracle or Sybase).

<!-- Sample MSSQL Database Connection String -->
<connectionString>Data Source=databaseServer; Initial Catalog=databaseName; User Id=userName; Password=password;</connectionString>

<!-- Sample Oracle Database Connection String -->
<connectionString>Data Source=databaseSID;user id=userName;password=password; </connectionString>

ignoreViews

No

Allows for certain views in the database to be ignored. Refer to the ignoreViews Object for details and an example.

See Also

.SchemaEditor files

inputFilePath Section

exportFilePath Section

reportFilePath Section

oracleDropColumnStrategy Section

storageLocations Section

ignoreViews Section

Next

inputFilePath Section
database Section

Did this topic help you? Email us your feedback.
Last updated: 5/26/2011 12:43:52 PM


inputFilePath Section

Parameter Name | Required | Default | Description
inputFilePath | Yes | DataDictionary.xml | Specifies the name of the input file. It can be either in the Data Dictionary format or in the Schema Script format.

See Also

.SchemaEditor files

database Section

exportFilePath Section

reportFilePath Section

oracleDropColumnStrategy Section

storageLocations Section

ignoreViews Section

Next

exportFilePath Section
inputFilePath Section

Did this topic help you? Email us your feedback.
Last updated: 4/13/2009 2:43:56 PM


exportFilePath Section

Parameter Name | Required | Default | Description
exportFilePath | Yes | ExportedSchema.xml | Specifies the name of the schema file to be generated when performing an export operation.

See Also

.SchemaEditor files

database Section

inputFilePath Section

reportFilePath Section

oracleDropColumnStrategy Section

storageLocations Section

ignoreViews Section

Next

reportFilePath Section
exportFilePath Section

Did this topic help you? Email us your feedback.
Last updated: 3/23/2009 11:57:01 AM


reportFilePath Section

Parameter Name | Required | Default | Description
reportFilePath | Yes | SchemaDifferenceReport.txt | Report file generated by performing a preview operation

See Also

.SchemaEditor files

database Section

inputFilePath Section

exportFilePath Section

oracleDropColumnStrategy Section

storageLocations Section

ignoreViews Section

Next

oracleDropColumnStrategy Section
reportFilePath Section

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 11:24:07 AM


oracleDropColumnStrategy Section

Normally when a column is removed from the schema it is dropped from the database table it belonged to. On Oracle when the database table is very large dropping a column is a slow operation. This setting allows Oracle users to choose an alternative strategy SET UNUSED.

When this optional setting is set to unused rather then dropping the column from the database table the column will be set as unused. Later the physical columns can be removed with a SQL operation.

Parameter Name | Required | Default | Description
oracleDropColumnStrategy | No | drop | Valid values are: drop - A DROP COLUMN directive is issued when dropping database columns. unused - A SET UNUSED directive is issued when dropping database columns.

See Also

.SchemaEditor files

database Section

inputFilePath Section

exportFilePath Section

reportFilePath Section

storageLocations Section

ignoreViews Section

Next

storageLocations Section
oracleDropColumnStrategy Section

Did this topic help you? Email us your feedback.
Last updated: 6/26/2013 10:54:51 AM


storageLocations Section

Dovetail SchemaEditor allows administrators to specify where particular tables and indexes get physically stored. On Oracle, a physical location is a tablespace, while on Microsoft SQL Server it is a filegroup.

These storage location parameters only apply when creating new tables and indexes. Setting a storage location for an existing table or index has no effect.

The storage location must already exist. Dovetail SchemaEditor will NOT create tablespaces or filegroups.

Example:

   <storageLocations defaultLocation="USERS">
      <storageLocation location="X_CUSTOM_NAME">
         <table name="x_custom_table" />
         <index tableName="x_custom_table" name="x_custom_index" />
      </storageLocation>
   </storageLocations>

See Also

.SchemaEditor files

database Section

inputFilePath Section

exportFilePath Section

reportFilePath Section

oracleDropColumnStrategy Section

ignoreViews Section

Next

storageLocations Object
storageLocations Section

Did this topic help you? Email us your feedback.
Last updated: 4/3/2009 12:43:18 PM


storageLocations Object

Parameter Name | Required | Default | Description
defaultLocation | No |  | Specifies the name of the default storage location. All new tables and indexes will be created in this location.

storageLocations can contain multiple storageLocation objects.

See Also

storageLocations Section

storageLocation Objects

table Objects

index Objects

Next

storageLocation Objects
storageLocations Object

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 11:25:14 AM


storageLocation Objects

Defines a particular Physical Storage Location. On Oracle, this represents a tablespace. On Microsoft SQL Server, this represents a filegroup.

Parameter Name | Required | Default | Description
location | No |  | Specifies the name of the storage location

storageLocation can contain multiple table and/or index objects.

See Also

storageLocations Section

storageLocations Object

table Objects

index Objects

Next

table Objects
storageLocation Objects

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 11:27:15 AM


table Objects

Defines a table to be stored in a particular Physical Storage Location

Parameter Name | Required | Default | Description
name | No |  | Specifies the name of the table to be stored in the storage location

See Also

storageLocations Section

storageLocations Object

storageLocation Objects

index Objects

Next

index Objects
table Objects

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 11:27:38 AM


index Objects

Defines an indexto be stored in a particular Physical Storage Location

Parameter Name | Required | Default | Description
tableName | No |  | Specifies the table name where the index exists
name | No |  | Specifies the name of the index to be stored in the storage location

See Also

storageLocations Section

storageLocations Object

storageLocation Objects

table Objects

Next

ignoreViews Section
index Objects

Did this topic help you? Email us your feedback.
Last updated: 4/3/2009 12:43:42 PM


ignoreViews Section

Dovetail SchemaEditor allows for certain views in the database to be ignored.

This is useful when you are making a schema change, but there is a view that is in some way "problematic". If the current operation does not involve the problematic view, then we can explicitly define this as a view to be ignored.

A "problematic" view is one that Dovetail SchemaEditor cannot fully resolve. For example, a view may have a view column that references a non-existent column on a table. Or have a join that references a non-existent relation. Or have duplicate aliases defined.

Example of Dovetail SchemaEditor errors that indicate there is a problematic view:

  • The join being added has an alias which is already being used by another join on the view for a different table.
  • Cannot add Join to View as it already belongs to View
  • A Join with the same key is already in the View
  • A ViewColumn with the same order is already in the View
  • A ViewColumn with the same name is already in the View

The correct way to deal with this issue is to fix the view in question so that it is correct, and can be fully resolved by Dovetail SchemaEditor. However, this may be time consuming and difficult, especially if the current schema change being performed has nothing to do with this particular view. So, we can simply ignore this view for the moment.

To ignore a view, add an ignoreViews section to the .SchemaEditor file.

Then, add one (or more) view elements to the ignoreViews section.

ignoreViews section parameters

Parameter Name | Required | Default | Description
ignoreViews | No |  | Section header for views to be ignored.

Note: ignoreViews can contain multiple view Objects.

Example:

<ignoreViews>
    <view name="bad_view_name"></view>
    <view name="another_bad_view_name"></view>
</ignoreViews>

See Also

.SchemaEditor files

database Section

inputFilePath Section

exportFilePath Section

reportFilePath Section

oracleDropColumnStrategy Section

storageLocations Section

Next

view Objects
ignoreViews Section

Did this topic help you? Email us your feedback.
Last updated: 6/6/2011 4:54:00 PM


view Objects

Defines a view to be ignored from the schema

Parameter Name | Required | Default | Description
name | No |  | Specifies the name of the view to be ignored.

Refer to the ignoreViews Object for an example.

See Also

ignoreViews Section

Next

Command Line Options
view Objects

Did this topic help you? Email us your feedback.
Last updated: 5/26/2011 12:41:37 PM


Command Line Options

Usage: SchemaEditor.exe [filename] [-generate | -export | -preview | -apply | -help]

Parameter Name | Description
filename | The SchemaEditor file. If none specified, a file with a ".SchemaEditor" extension in the working folder will be used. If multiple files with the ".SchemaEditor" are found in the working directory, no default is used.
-generate, -g | Generate a sample configuration file. The file will be named "Default.SchemaEditor" and will be located in the working folder
-export, -e | Exports the database specified in the "connectionString" setting to the file named in the "exportFilePath" setting
-preview, -p | Previews the changes to apply to the database specified in the "connectionString" setting based on the new schema described in the schema file named in the "inputFilePath" setting. The results are written to the file named in the "reportFilePath" setting.
-apply, -a | Apply the changes to the database specified in the "connectionString" setting based on the new schema described in the schema file named in the "inputFilePath" setting
-help, -h | Display help for Dovetail SchemaEditor

See Also

Working with SchemaEditor

Quick Start

.SchemaEditor files

Working with SchemaScript

Working with a Data Dictionary

Next

Working with SchemaScript
Command Line Options

Did this topic help you? Email us your feedback.
Last updated: 4/13/2009 2:45:34 PM


Working with SchemaScript

Schema Script is a simple, imperative way of specifying the changes to be made to the database schema.

A complete schema file (Data Dictionary), because of its size and complexity, can quickly become unwieldy and cumbersome to work with.

Schema Script simplifies the process of schema changes by allowing a user to specify only the changes to be made, as opposed to the complete schema.

To modify the database schema using Schema Script:

  1. BACKUP YOUR DATABASE
    It is highly recommended that you always backup your database before applying any schema changes.
  2. Edit the .SchemaEditor configuration file for your environment
  3. Create a schema script
    Use your favorite text or XML editor to create a Schema Script file. Refer to the Schema Script XML Reference for complete details on schema scripting.
  4. Preview the Schema Changes
    To preview the changes to be applied to the database, open a command line and use the -preview option to produce a difference report. For example:

    SchemaEditor.exe Default.SchemaEditor -preview

    The difference report will be saved to the file defined by the reportFile parameter in the .SchemaEditor file.
  5. Apply the changes to the database
    Open a command line, and use the -apply option to upgrade the database schema. For example:

    SchemaEditor.exe Default.SchemaEditor -apply

Note: Only adds and updates can be performed using SchemaScript. Deletes are not currently supported. When performing an add operation (such as addTable or addColumn), if that object already exists in the database, this object will be "skipped", and will not cause any database changes to be performed.

See Also

Working with SchemaEditor

Quick Start

.SchemaEditor files

Command Line Options

Working with a Data Dictionary

Next

SchemaScript Example
Working with SchemaScript

Did this topic help you? Email us your feedback.
Last updated: 4/8/2009 4:11:49 PM


SchemaScript Example

The following Schema Script example adds a new table with multiple columns, one relation, and one index to the database schema.

Example Schema Script files are also included in the examples directory. There is an example for Clarify databases version 7.0 and later, and an example for Amdocs databases version 6 and later.

Note: Only adds and updates can be performed using Schema Script. Deletes are not supported.

Note: When performing an add operation (such as addTable or addColumn), if that object already exists in the database, this object will be "skipped", and will not cause any database changes to be performed.

Note: This is the entire schema script file.

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">

   <addTable name="fc_login_monitor" id="4634">

      <description>Tracks application logins and logouts</description>

      <groupName>FC Login Monitor</groupName>

   </addTable>

   <addColumn name="objid" dataType="Integer" table="fc_login_monitor" />

   <addColumn name="logout_time" dataType="DateTime" table="fc_login_monitor" />

   <addColumn name="login_time" dataType="DateTime" table="fc_login_monitor" />

   <addColumn name="application" dataType="String" table="fc_login_monitor" >

    <length>255</length>

   </addColumn>

   <addColumn name="comments" dataType="String" table="fc_login_monitor" >

    <length>255</length>

   </addColumn>

   <addColumn name="login_name" dataType="String" table="fc_login_monitor" >

    <length>80</length>

   </addColumn>

   <addColumn name="fcsessionid" dataType="String" table="fc_login_monitor" >

    <length>80</length>

   </addColumn>

   <addRelation name="user2fc_login_monitor" type="OneToMany" table="user"

    inverseRelationName="fc_login_monitor2user" inverseTable="fc_login_monitor" />

   <addIndex name="sessionid_ind" columns="fcsessionid" table="fc_login_monitor">

    <isUnique>True</isUnique>

   </addIndex>

</schemaScript>

See Also
Working with SchemaScript
Script Ordering
Schema Script Namespaces

Next
Script Ordering

 SchemaScript Example

            Did this topic help you? Email us your feedback.

Last updated: 6/23/2011 9:37:31 AM


Script Ordering

Schema Script is a linear procedural script. This means that validation happens as each script command is read from the script file. Thus, the order that commands appear in the script is important.

For example, an <addColumn> command cannot occur before the table has been added.

Command Dependencies:

Command

Is Dependent On

<addColumn>

The table must already exist in the database, or an <addTable> command must have already occurred in the script.

<addRelation>

The table must already exist in the database, or an <addTable> command must have already occurred in the script.

<addIndex>

The table must already exist in the database, or an <addTable> command must have already occurred in the script.

<addJoin>

The view must already exist in the database, or an <addView> command must have already occurred in the script.

The table must already exist in the database, or an <addTable> command must have already occurred in the script.

The relation must already exist in the database, or an <addRelation> command must have already occurred in the script.

<addViewColumn>

The join that this viewColumn comes from must already exist in the database, or an <addJoin> command must have already occurred in the script.

Refer to the Schema Script XML Reference for complete details on schema scripting.

See Also

Working with SchemaScript

SchemaScript Example

Schema Script Namespaces

Next

Schema Script Namespaces
Script Ordering

Did this topic help you? Email us your feedback.
Last updated: 4/8/2009 4:12:26 PM


Schema Script Namespaces

To avoid breaking compatibility for customers that have not upgraded to an Amdocs 6 databases, we have introduced a new Schema Script document namespace and accompanying .xsd file used for validation. This new Schema Script file format will contain the additional database schema features introduced by Amdocs 6.

Backwards Compatiblity

Existing Dovetail SchemaEditor customers should have no problem using their existing Schema Script documents with Dovetail SchemaEditor.

To avoid ambiguity, we chose to not allow use of the new Amdocs 6 compatible schema with pre Amdocs 6 databases. Schema Scripts created with Amdocs 6 specific elements will not be usable on non Amdocs 6 databases. However, it is quite easy to manually remove the Amdocs 6 specific tags and change the namespace of the document.

Schema Script Example Documents

Below are examples of Schema Script documents of each file format. The differences are emphasized.

Pre Amdocs 6 Schema Script

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">

   <addSqlView name="my_new_SQL_view" id="4040">
    <description>my SQL view description</description>
    <sql>select title from table_case</sql>
   </addSqlView>

   <addSqlViewColumn name="owner" sqlView="my_new_SQL_view" datatype="String" >

    <length>40</length>

    <description>description for sql view column</description>

   </addSqlViewColumn>

</schemaScript>

Amdocs 6 or later Schema Script

<schemaScript xmlns="http://www.dovetailsoftware.com/2009/03/SchemaScript.xsd">

   <addSqlView name="my_new_SQL_view" id="4040">

    <description>my SQL view description</description>

    <sql>select title from table_case</sql>

    <sql targetDatabase="mssql">select title from table_case</sql>

   </addSqlView>

   <addSqlViewColumn name="owner" sqlView="my_new_SQL_view" datatype="String" >

    <length>40</length>

    <description>description for sql view column</description>

   </addSqlViewColumn>

</schemaScript>

Upgrading to Amdocs 6

Users of Dovetail SchemaEditor version who have upgraded to Amdocs 6 should update the namespace of their Schema Script documents to match the Amdocs 6 example. In particular, the xmlns attribute on the root schemaScript element should be:

http://www.dovetailsoftware.com/2009/03/SchemaScript.xsd

See Also
Working with SchemaScript
SchemaScript Example
Script Ordering

Next
Working with a Data Dictionary

 Schema Script Namespaces

            Did this topic help you? Email us your feedback.

Last updated: 4/15/2009 4:27:52 PM


Working with a Data Dictionary

A Data Dictionary file is a complete XML reference of the database schema.

The general steps for modifying the database schema:

  1. BACKUP YOUR DATABASE
  2. Edit the .SchemaEditor configuration file for your environment
  3. Export the Schema to a File
  4. Modify the Schema File
  5. Preview the changes
  6. Apply the changes to the database

See Also

Working with SchemaEditor

Quick Start

.SchemaEditor files

Command Line Options

Working with SchemaScript

Next

Exporting a Schema
Working with a Data Dictionary

Did this topic help you? Email us your feedback.
Last updated: 4/8/2009 3:56:33 PM


Exporting a Schema

Export the Schema to a file.

Open a command line, and use the -export option to save the current schema of the database to an XML file. For example:

SchemaEditor.exe Default.SchemaEditor -export

The schema will be saved to the file as defined by the inputFilePath parameter in the .SchemaEditor file.

See Also

Working with a Data Dictionary

Modifying the Schema File

Preview Schema Changes

Applying Schema Changes

Data Dictionary Namespaces

Next

Modifying the Schema File
Exporting a Schema

Did this topic help you? Email us your feedback.
Last updated: 4/3/2009 12:46:10 PM


Modifying the Schema File

Schema files are in an XML format, and can be modified with any text editor. However, an editor that supports XML formatting may be useful.

Schema File Format

<schema>

   <table>

      <column></column>

      <!-- Many columns -->

      <index></index>

      <!-- Many indexes -->

   </table>

   <!-- Many tables -->

   <view>

      <viewColumn></viewColumn>

      <!-- Many viewColumns -->

      <join></join>

      <!-- Many joins -->

   </view>

   <!-- Many views -->

   <relation>

      <fromSide></fromSide>

      <toSide></toSide>

   </relation>

   <!-- Many relations -->

   <exclusiveRelationSet>

      <relation>

         <fromSide></fromSide>

         <toSide></toSide>

      </relation>

   </exclusiveRelationSet>

   <!-- Many exclusiveRelationSets -->

</schema>

See Also
Working with a Data Dictionary
Exporting a Schema
Preview Schema Changes
Applying Schema Changes
Data Dictionary Namespaces

Next
Preview Schema Changes

 Modifying the Schema File

            Did this topic help you? Email us your feedback.

Last updated: 4/13/2009 2:56:26 PM


Preview Schema Changes

Previews the changes to be applied to the database.

Open a command line, and use the -preview option to produce a difference report. For example:

SchemaEditor.exe Default.SchemaEditor -preview

The difference report will be saved to the file as defined by the reportFile parameter in the .schemaEditor file.

See Also

Working with a Data Dictionary

Exporting a Schema

Modifying the Schema File

Applying Schema Changes

Data Dictionary Namespaces

Next

Applying Schema Changes
Preview Schema Changes

Did this topic help you? Email us your feedback.
Last updated: 1/3/2008 2:11:18 PM


Applying Schema Changes

Open a command line, and use the -apply option to upgrade the database schema. For example:

SchemaEditor.exe Default.SchemaEditor -apply

See Also

Working with a Data Dictionary

Exporting a Schema

Modifying the Schema File

Preview Schema Changes

Data Dictionary Namespaces

Next

Data Dictionary Namespaces
Applying Schema Changes

Did this topic help you? Email us your feedback.
Last updated: 1/3/2008 2:15:04 PM


Data Dictionary Namespaces

To avoid breaking compatibility for customers that have not upgraded to an Amdocs 6 databases, we have introduced a new Data Dictionary document namespace and accompanying .xsd file used for validation. This new Data Dictionary file format will contain the additional database schema features introduced by Amdocs 6.

Backwards Compatiblity

Existing Dovetail SchemaEditor customers should have no problem using their existing Data Dictionary documents with Dovetail SchemaEditor.

To avoid ambiguity, we chose to not allow use of the new Amdocs 6 compatible schema with pre Amdocs 6 databases. Data Dictionaries created with Amdocs 6 specific elements will not be usable on non Amdocs 6 databases. However, it is quite easy to manually remove the Amdocs 6 specific tags and change the namespace of the document.

Data Dictionary Example Documents

Below are examples of Data Dictionary documents of each file format. The differences are emphasized.

Pre Amdocs 6 Data Dictionary

<schema xmlns="http://www.dovetailsoftware.com/2006/10/DataDictionary.xsd">

  <sqlView id="2001" name="case_id_view" groupName="Support">

    <sql><![CDATA[Select objid from table_case]]></sql>

    <description><![CDATA[Display a case database identifier]]></description>

    <sqlViewColumn name="objid" dataType="Integer" clarifyGenericFieldID="3" isPredefined="true" isMandatory="true" isUserDefined="false" isDirect="true">

      <description><![CDATA[Internal record number]]></description>

    </sqlViewColumn>

  </sqlView>

</schema>

Amdocs 6 or later Data Dictionary

<schema xmlns="http://www.dovetailsoftware.com/2009/03/DataDictionary.xsd">

  <sqlView id="2001" name="case_id_view" groupName="Support">

    <sql><![CDATA[Select objid from table_case]]></sql>

    <databaseSpecificSql>

         <description><![CDATA[Nothing is really that special about this oracle specific SQL]]></description>

         <sql targetDatabase="oracle"><![CDATA[select objid from table_case]]></sql>

       </databaseSpecificSql>

       <description><![CDATA[Display a case database identifier]]></description>

       <sqlViewColumn name="objid" dataType="Integer" clarifyGenericFieldID="3" isPredefined="true" isMandatory="true" isUserDefined="false" isDirect="true">

         <description><![CDATA[Internal record number]]></description>

       </sqlViewColumn>

  </sqlView>

</schema>

Upgrading to Amdocs 6

Users of Dovetail SchemaEditor version who have upgraded to Amdocs 6 should update the namespace of their Data Dictionary documents to match the Amdocs 6 example. In particular, the xmlns attribute on the root schema element should be:

http://www.dovetailsoftware.com/2009/03/DataDictionary.xsd

See Also
Working with a Data Dictionary
Exporting a Schema
Modifying the Schema File
Preview Schema Changes
Applying Schema Changes

Next
Schema Script XML Reference

 Data Dictionary Namespaces

            Did this topic help you? Email us your feedback.

Last updated: 4/13/2009 3:35:15 PM


Schema Script XML Reference

This section is a reference for the Schema Script XML.

In This Chapter

schemaScript

Next

schemaScript
Schema Script XML Reference

Did this topic help you? Email us your feedback.
Last updated: 8/16/2007 1:16:06 PM


schemaScript

The schemaScript element is the top level element that defines the XML as a schema script. This element is required.

Example:

<schemaScript xmlns="http://www.dovetailsoftware.com/2009/03/SchemaScript.xsd">

   <addTable name="my_new_table" id="2001" />

   <addColumn name="objid" dataType="Long" table="my_new_table" />

   <addColumn name="x_my_new_column" dataType="DateTime" table="my_new_table" />

</schemaScript>

Required Attributes:

Attribute Name | Description
xmlns | Defines the namespace to be used. Do not change or remove this value.

See Also
Schema Script XML Reference

Next
addColumn

 schemaScript

            Did this topic help you? Email us your feedback.

Last updated: 3/23/2009 3:19:34 PM


addColumn

The addColumn element allows a new column to be added to the schema.

Example:

<addColumn name="x_my_new_column" dataType="Integer" table="case">
    <description>This is a description for my new column.</description>
</addColumn>

Required Attributes:

Attribute Name

Description

name

Specifies the name of the column.

dataType

The data type of the column. Valid values are:

  • String
  • Integer
  • SmallInteger
  • TinyInteger
  • Real
  • Double
  • Character
  • String
  • LongString
  • DateTime
  • Decimal

table

Specifies the name of the table on which this column should be added.

Optional Child Elements:

Element Name | Default | Description
length | 0 | For string columns, the length (in characters) of the column. This is required for string columns. The length can be increased, but cannot be decreased, as this could cause data loss.
description |  | Description of the column and its purpose.
isSearchable | false | For Oracle database systems, indicates if this column should be searchable in a case-insensitive manner or not.
defaultValue |  | The default value to be placed in this column when a new row is inserted.
isUserDefined | true | Indicates if this column has been defined by the customer.
decimalWidth | 0 | For decimal data types, specifies the maximum number of integers before and after the decimal point.
mantissaWidth | 0 | For decimal data types, specifies the maximum number of integers following the decimal point.
clarifyGenericFieldID | -1 | This data is used by Amdocs/Clarify applications to identify fields in different objects that are handled in a similar way. For example, a WIPbin contains different types of objects, such as cases and bugs. Certain fields in these objects (such as the ID number and title) are treated in the same way by the application and are assigned the same clarifyGenericFieldID.
isNullable | false | Specifies if the column should be nullable or not.
isCurrencyData | false | Indicates if this column holds monetary (currency) data.
isAChangeField | false | Indicates that this is a column that I want to know about changes to. When this column's data is changed, the column indicated by the isChangeDateField is updated to the current date/time. There can be multiple isAChangeField columns and one isChangeDateField column for each table.
isChangeDateField | false | Indicates that when a isAChangeField column's data changes, this column is set to the current date/time. There can be multiple isAChangeField columns and one isChangeDateField column for each table. .
isNotClientDistributable | false | In a mobile (Traveler) implementation, indicates if the column data should be synced to the client or not.
isNotServerDistributable | false | In a mobile (Traveler) implementation, indicates if the column data should be synced to the server or not.
isPredefined | false | This attribute is not typically used.
isComputed | false | This attribute is not typically used.
isMandatory | false | This attribute is not typically used.
isDirect | false | This attribute is not typically used.
isBinaryData | false | This attribute is not typically used.
isUserModified | false | This attribute is not typically used.

See Also

schemaScript

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateIndex

updateJoin

updateRelation

updateTable

updateSchema

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

addExclusiveRelationSet
addColumn

Did this topic help you? Email us your feedback.
Last updated: 1/3/2008 1:39:58 PM


addExclusiveRelationSet

The addExclusiveRelationSet element allows a new exclusive relation set to be added to the schema.

Example:

<addExclusiveRelationSet name="my_relation_set_name" table="site" tableIdColumn="type" keyColumn="objid" />

<addRelation name="employee2state" type="OneToMany" table="employee" inverseRelationName="state2employee" inverseTable="state_prov">

   <exclusiveRelationSet>my_relation_set_name</exclusiveRelationSet>

</addRelation>

Required Attributes:

Attribute Name | Description
name | The name of the exclusive set. This allows for multiple exclusive relation sets on a single table.
table | The name of the table on which the exclusive relation set exists on, and on which the physical columns exist.
tableIdColumn | The name of the column on the toSideTable table that stores the table ID of the related data.
keyColumn | The name of the column on the toSideTable table that stores the foreign key(the objid) of the related data.

Optional Child Elements:
None

See Also
schemaScript
addColumn
addIndex
addJoin
addRelation
addSqlView
addSqlViewColumn
addTable
addView
addViewColumn
updateColumn
updateIndex
updateJoin
updateRelation
updateTable
updateSchema
updateSqlView
updateSqlViewColumn
updateView
updateViewColumn

Next
addIndex

 addExclusiveRelationSet

            Did this topic help you? Email us your feedback.

Last updated: 11/9/2007 9:24:01 AM


addIndex

The addIndex element allows a new index to be added to the schema.

Example:

<addIndex name="my_new_index" columns="alt_address, case_wip2wipbin" table="case">
   <description>This is the description of my custom index</description>
</addIndex>

Required Attributes:

Attribute Name | Description
name | Specifies the name of the index.
columns | A comma separated list of columns that make up the index. This can include fields and relations.
table | Specifies the name of the table on which this index should be added.

Optional Child Elements:

Element Name | Default | Description
description |  | Description of the index and its purpose.
isUnique |  | Specifies if the index is a unique index or not.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateIndex

updateJoin

updateRelation

updateTable

updateSchema

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

addJoin
addIndex

Did this topic help you? Email us your feedback.
Last updated: 4/3/2009 2:31:30 PM


addJoin

The addJoin element allows a new join to be added to the schema.

Example:

<addJoin kind="Inner" view="my_new_view" relation="relationName" table="tableName">
   <description>description</description>
</addJoin>

Required Attributes:

Attribute Name

Description

kind

Specifies the kind of the join. Valid values are:

  • Inner
  • Outer

view

The name of the view on which this join should be added to.

table

The name of the table to be joined to.

relation

The name of the relation on the table used for the join.

Optional Child Elements:

Element Name | Default | Description
description |  | Description of the join.
alias |  | Name of the alias for the table defined in the table attribute.
toAlias |  | Name of the alias for the table referenced by the other side of the relation.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateIndex

updateJoin

updateRelation

updateTable

updateSchema

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

addJoin Examples
addJoin

Did this topic help you? Email us your feedback.
Last updated: 1/3/2008 1:02:22 PM


addJoin Examples

The join examples in this section use the following two tables:

Contact:

Objid | First Name | Last Name
1 | Bill | Smith
2 | Mary | Jones
3 | Fred | Miller

Web_user:

Objid | Login Name | web_user2contact
7 | bsmith | 1
8 | mjones | 2
9 | tnorton | null

See Also

addJoin

Next

1 - Inner Join
addJoin Examples

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 9:25:26 AM


1 - Inner Join

<addJoin kind="Inner" table="web_user"  relation="web_user2contact" view="web_user_contact_view" />

This inner join will result in the following data being returned:

Contact Objid | First Name | Last Name | Web User Objid | Login Name
1 | Bill | Smith | 7 | bsmith
2 | Mary | Jones | 8 | mjones

See Also

addJoin Examples

2 - Outer Join

3 - Outer Join

4 - Inner Join with Aliases

Next

2 - Outer Join
1 - Inner Join

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 11:30:55 AM


2 - Outer Join

<addJoin kind="Outer" table="web_user" relation="web_user2contact"  view="web_user_contact_view"/>

This will return all of the web_user records, and the associated contact records if they exist.
This outer join will result in the following data being returned:

Contact Objid | First Name | Last Name | Web User Objid | Login Name
1 | Bill | Smith | 7 | bsmith
2 | Mary | Jones | 8 | mjones
null | null | null | 9 | tnorton

See Also

addJoin Examples

1 - Inner Join

3 - Outer Join

4 - Inner Join with Aliases

Next

3 - Outer Join
2 - Outer Join

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 11:31:14 AM


3 - Outer Join

<addJoin kind="Outer" table="contact"  relation="contact2web_user" view="web_user_contact_view"/>

This will return all of the contact records, and the associated web_user records if they exist.
This outer join will result in the following data being returned:

Contact Objid | First Name | Last Name | Web User Objid | Login Name
1 | Bill | Smith | 7 | bsmith
2 | Mary | Jones | 8 | mjones
3 | Fred | Miller | null | null

See Also

addJoin Examples

1 - Inner Join

2 - Outer Join

4 - Inner Join with Aliases

Next

4 - Inner Join with Aliases
3 - Outer Join

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 11:31:31 AM


4 - Inner Join with Aliases

<addJoin kind="Inner" view="fc_wipelm_case" relation="resp_priority2case" table="gbst_elm">
   <alias>gse_priority</alias>
</addJoin>
<addViewColumn view="fc_wipelm_case" name="priority" column="title" table="gbst_elm" >
   <alias>gse_priority</alias>
</addViewColumn>

See Also

addJoin Examples

1 - Inner Join

2 - Outer Join

3 - Outer Join

Next

addRelation
4 - Inner Join with Aliases

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 9:45:22 AM


addRelation

The addRelation element allows a new relation to be added to the schema.

Example:

<addRelation name="employee2state" type="OneToMany" table="employee" inverseRelationName="state2employee" inverseTable="state_prov">
   <description>my description for employee2state</description>
   <inverseDescription>my description for state2employee</inverseDescription>
</addRelation>

Required Attributes:

Attribute Name

Description

name

Specifies the name of the relation

type

Cardinality of the relation. Valid values are:

  • OneToOne
  • OneToMany
  • ManyToMany

A relation and its inverse relation need only be defined once in a schema script file. Because of this, there is not a need for a ManyToOneRelation (MTO) object.

table

Specifies the name of the table on which this relation should be added.

inverseRelationName

Specifies the name of the inverse relation.

inverseTable

Specifies the name of the table on which the inverse relation should be added.
Note that this inverseTable is where the actual column will be created on for OneToOne and OneToMany relations.

Optional Child Elements:

Element Name | Default | Description
description |  | Description of the relation.
inverseDescription |  | Description of the inverse relation.
exclusiveRelationSet |  | The name of the exclusive relation set to which this relation should be part of.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateIndex

updateJoin

updateRelation

updateTable

updateSchema

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

addSqlView
addRelation

Did this topic help you? Email us your feedback.
Last updated: 4/3/2009 3:29:23 PM


addSqlView

The addSqlView element allows a new SQL View to be added to the schema. When creating a new SqlView, you must create at least one SqlView column, as shown in the example below.

Example:

<addSqlView name="my_new_SQL_view" id="4040">
    <description>my SQL view description</description>
    <sql>select web_login as owner from table_user union select req_originator as owner from table_fcs_detail</sql>
 </addSqlView>

 <addSqlViewColumn name="owner" sqlView="my_new_SQL_view" dataType="String" >

     <length>40</length>

     <description>description for sql view column</description>

  </addSqlViewColumn>

Required Attributes:

Attribute Name | Description
name | Specifies the name of the SQL View. This must be a unique name amongst all tables and views.
id | Specifies the id number of the SQL View. This must be a unique id amongst all tables and views. The customer range for IDs is 430-511 and 2000-4999.

Required Child Elements:

Element Name

Description

sql

The actual SQL that defines the view.

Optional Attributes

Name

Description

targetDatabase

Users of Amdocs 6 or later databases can specify this optional targetDatabase attribute when using Amdocs 6 compatible Schema scripts.

Sql elements with no targetDatabase attribute are the default SQL statement for the view.

When a sql view has a targetDatabase specified that matches the database the schema script is being applied against. Database specific version of the SQL statement will be used.

Possible targetDatabase attribute values are:

  • mssql
  • oracle
  • db2

Optional Child Elements:

Element Name | Default | Description
description |  | Description of the SQL View and its purpose.
groupName |  | Specifies the name of the application module that the SQL View is used by.
baseTable |  | Specifies the name of the main tables used in the view.

See Also
schemaScript
addColumn
addExclusiveRelationSet
addIndex
addJoin
addRelation
addSqlViewColumn
addTable
addView
addViewColumn
updateColumn
updateIndex
updateJoin
updateRelation
updateTable
updateSchema
updateSqlView
updateSqlViewColumn
updateView
updateViewColumn

Next
addSqlViewColumn

 addSqlView

            Did this topic help you? Email us your feedback.

Last updated: 7/23/2009 2:07:45 PM


addSqlViewColumn

The addSqlViewColumn element allows a new column to be added to a SQL view in the schema.

Example:

<addSqlViewColumn name="num_units" sqlView="my_new_SQL_view" datatype="Integer">
    <description>This is the description of the SqlViewColumn.</description>
</addSqlViewColumn>

Required Attributes:

Attribute Name

Description

name

Specifies the name of the column.

sqlView

Specifies the name of the SQL View on which this column should be added.

dataType

The data type of the column. Valid values are:

  • String
  • Integer
  • SmallInteger
  • TinyInteger
  • Real
  • Double
  • Character
  • String
  • LongString
  • DateTime
  • Decimal

Optional Child Elements:

Element Name | Default | Description
length | 0 | For string columns, the length (in characters) of the column. This is required for string columns. The length can be increased, but cannot be decreased, as this could cause data loss.
description |  | Description of the column and its purpose.
isSearchable | false | For Oracle database systems, indicates if this column should be searchable in a case-insensitive manner or not.
isUserDefined | true | Indicates if this column has been defined by the customer.
clarifyGenericFieldID | -1 | This data is used by Amdocs/Clarify applications to identify fields in different objects that are handled in a similar way. For example, a WIPbin contains different types of objects, such as cases and bugs. Certain fields in these objects (such as the ID number and title) are treated in the same way by the application and are assigned the same clarifyGenericFieldID.
isCurrencyData | false | Indicates if this column holds monetary (currency) data.
isPredefined | false | This attribute is not typically used.
isMandatory | false | This attribute is not typically used.
isDirect | false | This attribute is not typically used.
isUserModified |  | This attribute is not typically used.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addTable

addView

addViewColumn

updateColumn

updateIndex

updateJoin

updateRelation

updateTable

updateSchema

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

addTable
addSqlViewColumn

Did this topic help you? Email us your feedback.
Last updated: 1/3/2008 1:10:58 PM


addTable

The addTable element allows a new table to be added to the schema.

Example:

<addTable name="my_new_table" id="2001">
   <description>This is the description of the new table.</description>
   <groupName>my custom group</groupName>
</addTable>

<addColumn name="objid" dataType="Integer" table="my_new_table" />

<addColumn name="x_my_new_column" dataType="DateTime" table="my_new_table" />

Required Attributes:

Attribute Name | Description
name | Specifies the name of the table. This must be a unique name amongst all tables and views.
id | Specifies the id number of the table. This must be a unique id amongst all tables and views. The customer range for IDs is 430-511 and 2000-4999.

Optional Child Elements:

Element Name | Default | Description
description |  | Description of the table and its purpose.
groupName |  | Specifies the name of the application module that the table is used by.
areIDsCached | false | Specifies if objid caching is enabled for this table when using the Clarify Classic Client.
areFlexibleAttributesAllowed | false | Specifies if the object can have flexible attributes.
isFlexibleAttributeTable | false | Specifies if the object is a flexible attributes table.
isOnlyMetadata | false | Indicates if the table is only represented in the metadata layer (ADP tables), and not a physical table.

See Also
schemaScript
addColumn
addExclusiveRelationSet
addIndex
addJoin
addRelation
addSqlView
addSqlViewColumn
addView
addViewColumn
updateColumn
updateIndex
updateJoin
updateRelation
updateTable
updateSchema
updateSqlView
updateSqlViewColumn
updateView
updateViewColumn

Next
addView

 addTable

            Did this topic help you? Email us your feedback.

Last updated: 1/3/2008 1:11:50 PM


addView

The addView element allows a new view to be added to the schema.

The example below is a typical view that joins two tables and has a view column for each table.

Example:

<addView name="my_new_view" id="3001">
    <description>This is the description of the new view.</description>
</addView>

<addJoin kind="Inner" view="my_new_view" relation="case_reporter2contact" table="case" />

<addViewColumn name="case_reason" view="my_new_view" column="objid" table="contact" />

<addViewColumn name="case_objid" view="my_new_view" column="objid" table="case" />

Required Attributes:

Attribute Name | Description
name | Specifies the name of the view. This must be a unique name amongst all tables and views.
id | Specifies the id number of the view. This must be a unique id amongst all tables and views. The customer range for IDs is 430-511 and 2000-4999.

Optional Child Elements:

Element Name | Default | Description
description |  | Description of the view and its purpose.
groupName |  | Specifies the name of the application module that the view is used by.

See Also
schemaScript
addColumn
addExclusiveRelationSet
addIndex
addJoin
addRelation
addSqlView
addSqlViewColumn
addTable
addViewColumn
updateColumn
updateIndex
updateJoin
updateRelation
updateTable
updateSchema
updateSqlView
updateSqlViewColumn
updateView
updateViewColumn

Next
addViewColumn

 addView

            Did this topic help you? Email us your feedback.

Last updated: 1/3/2008 1:12:44 PM


addViewColumn

The addViewColumn element allows a new view column to be added to the schema.

Example:

<addViewColumn name="case_reason" view="case_view" column="x_reason" table="case">
    <description>This is the description of the new view column.</description>
</addViewColumn>

Required Attributes:

Attribute Name | Description
name | Specifies the name of the view column.
view | Specifies the name of the view that this view column should be added to.
table | Specifies the name of the table that this view column comes from.
column | Specifies the name of the column on the table that this view column comes from.

Optional Child Elements:

Element Name

Default

Description

description

Description of the index and its purpose.

alias

If referencing a join that has an alias, the name of the alias.

clarifyGenericFieldID

-1

This data is used by Amdocs/Clarify applications to identify fields in different objects that are handled in a similar way. For example, a WIPbin contains different types of objects, such as cases and bugs. Certain fields in these objects (such as the ID number and title) are treated in the same way by the application and are assigned the same clarifyGenericFieldID.

Note: There is not an isUnique attribute. To indicate a viewColumn as unique, set the clarifyGenericFieldID to 3.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

updateColumn

updateIndex

updateJoin

updateRelation

updateTable

updateSchema

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

updateColumn
addViewColumn

Did this topic help you? Email us your feedback.
Last updated: 1/3/2008 1:15:15 PM


updateColumn

The updateColumn element allows an existing column to be modified.

Example:

<updateColumn name="x_my_new_column"  table="case" >
   <description>This is the description of the updated column.</description>
</updateColumn>

Required Attributes:

Attribute Name | Description
name | Specifies the name of the column.
table | Specifies the name of the table on which this column exists.

Optional Child Elements:

Element Name | Description
length | For string columns, the length (in characters) of the column. This is required for string columns. The length can be increased, but cannot be decreased, as this could cause data loss.
description | Description of the column and its purpose.
isSearchable | For Oracle database systems, indicates if this column should be searchable in a case-insensitive manner or not.
defaultValue | The default value to be placed in this column when a new row is inserted.
isUserDefined | Indicates if this column has been defined by the customer.
decimalWidth | For decimal data types, specifies the maximum number of integers before and after the decimal point. [1]
mantissaWidth | For decimal data types, specifies the maximum number of integers following the decimal point. [1]
clarifyGenericFieldID | This data is used by Amdocs/Clarify applications to identify fields in different objects that are handled in a similar way. For example, a WIPbin contains different types of objects, such as cases and bugs. Certain fields in these objects (such as the ID number and title) are treated in the same way by the application and are assigned the same clarifyGenericFieldID.
isNullable | Specifies if the column should be nullable or not.
isCurrencyData | Indicates if this column holds monetary (currency) data.
isAChangeField | Indicates that this is a column that I want to know about changes to. When this column's data is changed, the column indicated by the isChangeDateField is updated to the current date/time. There can be multiple isAChangeField columns and one isChangeDateField column for each table.
isChangeDateField | Indicates that when a isAChangeField column's data changes, this column is set to the current date/time. There can be multiple isAChangeField columns and one isChangeDateField column for each table.
isNotClientDistributable | In a mobile (Traveler) implementation, indicates if the column data should be synced to the client or not.
isNotServerDistributable | In a mobile (Traveler) implementation, indicates if the column data should be synced to the server or not.
isPredefined | This attribute is not typically used.
isComputed | This attribute is not typically used.
isMandatory | This attribute is not typically used.
isDirect | This attribute is not typically used.
isBinaryData | This attribute is not typically used.
isUserModified | This attribute is not typically used.

Notes:

[1] The only changes to a decimal type column precision and mantissa are these which don't cause data loss. For example, if mantissaWidth increases, the decimalWidth must also increase accordingly.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateIndex

updateJoin

updateRelation

updateTable

updateSchema

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

updateIndex
updateColumn

Did this topic help you? Email us your feedback.
Last updated: 4/13/2009 3:08:21 PM


updateIndex

The updateIndex element allows an existing index to be modified.

Example:

<updateIndex name="my_existing_index" table="case">
   <columns>newIndexedColumn, newIndexedRelation</columns>
   <description>This is the new description of my custom index.</description>
</updateIndex>

Required Attributes:

Attribute Name | Description
name | Specifies the name of the index.
table | Specifies the name of the table on which this index exists.

Optional Child Elements:

Element Name | Description
columns | A comma separated list of columns that make up the index. This can include fields and relations.
description | Description of the index and its purpose.
isUnique | Specifies if the index is a unique index or not.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateJoin

updateRelation

updateTable

updateSchema

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

updateJoin
updateIndex

Did this topic help you? Email us your feedback.
Last updated: 4/3/2009 2:37:07 PM


updateJoin

The updateJoin element allows an existing join to be modified.

Example:

<updateJoin view="my_existing_view" relation="relationName" table="tableName" alias="" toAlias="">
   <description>This is the description of the updated join.</description>
   <kind>Inner</kind>
</updateJoin>

Required Attributes:

Attribute Name | Description
view | The name of the view on which this join exists on.
table | The name of the table to be joined to.
relation | The name of the relation on the table used for the join.
alias | Name of the alias for the table defined in the table attribute.
toAlias | Name of the alias for the table referenced by the other side of the relation.

Optional Child Elements:

Element Name

Description

kind

Specifies the kind of the join. Valid values are:

  • Inner
  • Outer

description

Description of the join.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateIndex

updateRelation

updateTable

updateSchema

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

updateRelation
updateJoin

Did this topic help you? Email us your feedback.
Last updated: 1/2/2008 1:15:56 PM


updateRelation

The updateRelation element allows an existing relation to be modified.

Example:

<updateRelation name="creator2case" table="employee">
   <description>This is the description of creator2case.</description>
   <inverseDescription>This is the description of case2creator.</inverseDescription>
</updateRelation>

Required Attributes:

Attribute Name | Description
name | Specifies the name of the relation.
table | Specifies the name of the table on which this relation should be added.

Optional Child Elements:

Element Name | Description
description | Description of the relation.
inverseDescription | Description of the inverse relation.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateIndex

updateJoin

updateTable

updateSchema

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

updateTable
updateRelation

Did this topic help you? Email us your feedback.
Last updated: 1/2/2008 1:17:20 PM


updateTable

The updateTable element allows an existing table to be modified.

Example:

<updateTable name="my_new_table" id="2001">
   <description>This is the description of the updated table.</description>
   <groupName>This is the custom group.</groupName>
</updateTable>

Required Attributes:

Attribute Name | Description
name | Specifies the name of the table. This must be a unique name amongst all tables and views.
id | Specifies the id number of the table. This must be a unique id amongst all tables and views. The customer range for IDs is 430-511 and 2000-4999.

Optional Child Elements:

Element Name | Description
description | Description of the table and its purpose.
groupName | Specifies the name of the application module that the table is used by.
areIDsCached | Specifies if objid caching is enabled for this table when using the Clarify Classic Client.
areFlexibleAttributesAllowed | Specifies if the object can have flexible attributes.
isFlexibleAttributeTable | Specifies if the object is a flexible attributes table.
isOnlyMetadata | Indicates if the table is only represented in the metadata layer (ADP tables), and not a physical table.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateIndex

updateJoin

updateRelation

updateSchema

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

updateSchema
updateTable

Did this topic help you? Email us your feedback.
Last updated: 1/2/2008 1:18:17 PM


updateSchema

The updateSchema element allows properties of the schema to be modified.

Example:

<updateSchema>
   <schemaRevision>99</schemaRevision>
   <customerRevision>15</customerRevision>
</updateSchema>

Required Attributes:
None

Optional Child Elements:

Element Name | Description
schemaRevision | Specifies the vendor product schema revision.
customerRevision | Specifies the customer schema revision.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateIndex

updateJoin

updateRelation

updateTable

updateSqlView

updateSqlViewColumn

updateView

updateViewColumn

Next

updateSqlView
updateSchema

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 11:39:10 AM


updateSqlView

The updateSqlView element allows an existing SQL View to be modified.

Example:

<updateSqlView name="wipelm_all" >
    <groupName>sqlviewgroupname</groupName>
    <description>This is the description of the updated SqlView.</description>
    <sql>select web_login as owner from table_empl_user union select req_originator as owner from table_fcs_detail</sql>
 </updateSqlView>

Required Attributes:

Attribute Name | Description
name | Specifies the name of the SQL View.

Optional Child Elements:

Element Name

Description

description

Description of the SQL View and its purpose.

groupName

Specifies the name of the application module used by the SQL View.

baseTable

Specifies the name of the main tables used in the view.

sql

The actual SQL that defines the view.

Optional Attributes

Name

Description

targetDatabase

Users of Amdocs 6 or later databases can specify this optional targetDatabase attribute when using Amdocs 6 compatible Schema scripts.

Sql elements with no targetDatabase attribute are the default SQL statement for the view.

When a sql view has a targetDatabase specified that matches the database the schema script is being applied against. Database specific version of the SQL statement will be used.

Possible targetDatabase attribute values are:

  • mssql
  • oracle
  • db2

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateIndex

updateJoin

updateRelation

updateTable

updateSchema

updateSqlViewColumn

updateView

updateViewColumn

Next

updateSqlViewColumn
updateSqlView

Did this topic help you? Email us your feedback.
Last updated: 4/13/2009 2:36:34 PM


updateSqlViewColumn

The updateSqlViewColumn element allows an existing column on a SQL view to be modified.

Example:

<updateSqlViewColumn name="title" sqlView="my_SQL_view" >
     <description>This is a new description for my sql view column.</description>
</updateSqlViewColumn>

Required Attributes:

Attribute Name | Description
name | Specifies the name of the SQL view column.
sqlView | Specifies the name of the SQL view on which this column exists.

Optional Child Elements:

Element Name

Description

dataType

The data type of the SQL view column. Valid values are:

  • String
  • Integer
  • SmallInteger
  • TinyInteger
  • Real
  • Double
  • Character
  • String
  • LongString
  • DateTime
  • Decimal

length

For string columns, the length (in characters) of the column. This is required for string columns. The length can be increased, but cannot be decreased, as this could cause data loss.

description

Description of the column and its purpose.

isSearchable

For Oracle database systems, indicates if this column should be searchable in a case-insensitive manner or not.

isUserDefined

Indicates if this column has been defined by the customer.

clarifyGenericFieldID

This data is used by Amdocs/Clarify applications to identify fields in different objects that are handled in a similar way. For example, a WIPbin contains different types of objects, such as cases and bugs. Certain fields in these objects (such as the ID number and title) are treated in the same way by the application and are assigned the same clarifyGenericFieldID.

isCurrencyData

Indicates if this column holds monetary (currency) data.

isPredefined

This attribute is not typically used.

isComputed

This attribute is not typically used.

isMandatory

This attribute is not typically used.

isDirect

This attribute is not typically used.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateIndex

updateJoin

updateRelation

updateTable

updateSchema

updateSqlView

updateView

updateViewColumn

Next

updateView
updateSqlViewColumn

Did this topic help you? Email us your feedback.
Last updated: 1/2/2008 1:19:06 PM


updateView

The updateView element allows an existing view to be modified.

Example:

<updateView name="my_new_view" id="3001" >
   <description>This is the description of the updated view.</description>
</updateView>

Required Attributes:

Attribute Name | Description
name | Specifies the name of the view. This must be a unique name amongst all tables and views.
id | Specifies the id number of the view. This must be a unique id amongst all tables and views. The customer range for IDs is 430-511 and 2000-4999.

Optional Child Elements:

Element Name | Description
description | Description of the view and its purpose.
groupName | Specifies the name of the application module that the view is used by.

See Also

schemaScript

addColumn

addExclusiveRelationSet

addIndex

addJoin

addRelation

addSqlView

addSqlViewColumn

addTable

addView

addViewColumn

updateColumn

updateIndex

updateJoin

updateRelation

updateTable

updateSchema

updateSqlView

updateSqlViewColumn

updateViewColumn

Next

updateViewColumn
updateView

Did this topic help you? Email us your feedback.
Last updated: 1/2/2008 1:19:21 PM


updateViewColumn

The updateViewColumn element allows an existing view column to be modified.

Example:

<!-- change to a new column on the same table -->
<updateViewColumn name="viewColumnName" view="viewName">
   <column>newColumnName</column>
</updateViewColumn>

<!-- change to a new column on a new table -->

<updateViewColumn name="viewColumnName" view="viewName">

   <table>newTableName</table>

   <column>newColumnName</column>

</updateViewColumn>

<!-- change to the same column name on a different table -->

<updateViewColumn name="viewColumnName" view="viewName">

   <table>newTableName</table>

</updateViewColumn>

Required Attributes:

Attribute Name | Description
name | Specifies the name of the view column
view | Specifies the name of the view

Optional Child Elements:

Element Name

Description

table

Specifies the name of the table that this view column comes from

column

Specifies the name of the column on the table that this view column comes from

description

Description of the index and its purpose

alias

If referencing a join that has an alias, the name of the alias.

clarifyGenericFieldID

This data is used by Amdocs/Clarify applications to identify fields in different objects that are handled in a similar way. For example, a WIPbin contains different types of objects, such as cases and bugs. Certain fields in these objects (such as the ID number and title) are treated in the same way by the application and are assigned the same clarifyGenericFieldID.

Note: There is not an isUnique attribute. To indicate a viewColumn as unique, set the clarifyGenericFieldID to 3.

See Also
schemaScript
addColumn
addExclusiveRelationSet
addIndex
addJoin
addRelation
addSqlView
addSqlViewColumn
addTable
addView
addViewColumn
updateColumn
updateIndex
updateJoin
updateRelation
updateTable
updateSchema
updateSqlView
updateSqlViewColumn
updateView

Next
DataDictionary XML Reference

 updateViewColumn

            Did this topic help you? Email us your feedback.

Last updated: 11/9/2007 10:03:18 AM


DataDictionary XML Reference

This section is a reference for the Data Dictionary XML.

In This Chapter

schema

Next

schema
DataDictionary XML Reference

Did this topic help you? Email us your feedback.
Last updated: 4/8/2009 4:16:40 PM


schema

XML format for the schema object

The schema object is the top level object.

<schema schemaRevision="60" customerRevision="" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://dovetailsoftware.com/2006/10/DataDictionary.xsd">
...
</schema>

Attribute Name | Required | Description
schemaRevision | Yes | Specifies the vendor schema revision.
customerRevision | Yes | Specifies the customer schema revision.
xmlns:xsi | Yes | Defines the type of XML schema. Do not change or remove this value.
xmlns | Yes | Defines the namespace to be used. Do not change or remove this value.

See Also

DataDictionary XML Reference

Next

table
schema

Did this topic help you? Email us your feedback.
Last updated: 5/27/2010 11:13:59 AM


table

XML format for the table object

The table element is a child of the schema object.

The table contains children elements of type column and index.

<schema schemaRevision="60" customerRevision=""
   <table name="case" id=0 >
      <column .../>
      <index>...</index>
   </table>
</schema>

Attribute Name | Required | Default | Description
name | Yes |  | Specifies the name of the table. This must be a unique name amongst all tables and views.
id | Yes |  | Specifies the id number of the table. This must be a unique id amongst all tables and views. The customer range for IDs is 430-511 and 2000-4999.
description | No |  | Description of the table and its purpose.
groupName | No |  | Specifies the name of the application module that the table is used by.
areIDsCached | No | false | Specifies if objid caching is enabled for this table when using the Clarify Classic Client.
areFlexibleAttributesAllowed | No | false | Specifies if the object can have flexible attributes.
isFlexibleAttributeTable | No | false | Specifies if the object is a flexible attributes table.
isOnlyMetadata | No | false | Indicates if the table is only represented in the metadata layer (ADP tables), and not a physical table.

See Also

schema

relation

view

exclusiveRelationSet

sqlView

Next

column
table

Did this topic help you? Email us your feedback.
Last updated: 3/30/2009 11:33:51 AM


column

XML format for the column object

The column element is a child of the table object.

<table name="...">
   <column name="my_field" dataType="String" length=40 .../>
</table>

Attribute Name

Required

Default

Description

name

Yes

Specifies the name of the column.

dataType

Yes

The data type of the column. Valid values are:

  • String
  • Integer
  • SmallInteger
  • TinyInteger
  • Real
  • Double
  • Character
  • String
  • LongString
  • DateTime
  • Decimal

length

No

0

For string columns, the length (in characters) of the column. This is required for string columns. The length can be increased, but cannot be decreased, as this could cause data loss.

description

No

Description of the column and its purpose.

isSearchable

No

false

For Oracle database systems, indicates if this column should be searchable in a case-insensitive manner or not.

defaultValue

No

The default value to be placed in this column when a new row is inserted.

isUserDefined

No

true

Indicates if this column has been defined by the customer.

decimalWidth

No

0

For decimal data types, specifies the maximum number of integers before and after the decimal point.

mantissaWidth

No

0

For decimal data types, specifies the maximum number of integers following the decimal point.

clarifyGenericFieldID

No

-1

This data is used by Amdocs/Clarify applications to identify fields in different objects that are handled in a similar way. For example, a WIPbin contains different types of objects, such as cases and bugs. Certain fields in these objects (such as the ID number and title) are treated in the same way by the application and are assigned the same clarifyGenericFieldID.

isNullable

No

false

Specifies if the column should be nullable or not.

isCurrencyData

No

false

Indicates if this column holds monetary (currency) data.

isAChangeField

No

false

Indicates that this is a column that I want to know about changes to. When this column's data is changed, the column indicated by the isChangeDateField is updated to the current date/time. There can be multiple isAChangeField columns and one isChangeDateField column for each table.

isChangeDateField

No

false

Indicates that when a isAChangeField column's data changes, this column is set to the current date/time. There can be multiple isAChangeField columns and one isChangeDateField column for each table.

isNotClientDistributable

No

false

In a mobile (Traveler) implementation, indicates if the column data should be synced to the client or not.

isNotServerDistributable

No

false

In a mobile (Traveler) implementation, indicates if the column data should be synced to the server or not.

isPredefined

No

false

This attribute is not typically used.

isComputed

No

false

This attribute is not typically used.

isMandatory

No

false

This attribute is not typically used.

isDirect

No

false

This attribute is not typically used.

isBinaryData

No

false

This attribute is not typically used.

isUserModified

No

false

This attribute is not typically used.

order

No

The order of the column within the table.

See Also

table

index

Next

index
column

Did this topic help you? Email us your feedback.
Last updated: 3/30/2009 11:33:38 AM


index

XML format for the index object

The index element is a child of the table object.

The table contains children elements of type indexColumn and indexRelation.

<table name="...">
      <index name="ct_bus_index" isUnique="true">
         <description><![CDATA[]]></description>
         <indexColumn   name="role_name"              order="0" />
         <indexColumn   name="focus_type"             order="1" />
         <indexRelation name="ct_bus_role2contact"    order="2" />
         <indexRelation name="ct_bus_role2bus_org"    order="3" />
      </index>
</table>

Attribute Name | Required | Description
name | Yes | Specifies the name of the index.
description | No | Description of the index and its purpose.
isUnique | No | Specifies if the index is a unique index or not.

See Also

table

column

Next

indexColumn
index

Did this topic help you? Email us your feedback.
Last updated: 3/30/2009 11:33:58 AM


indexColumn

XML format for the indexColumn object

The indexColumn element is a child of the index object.

<table name="...">
      <index name="ct_bus_index" isUnique="true">
         <description><![CDATA[]]></description>
         <indexColumn   name="role_name"              order="0" />
         <indexColumn   name="focus_type"             order="1" />
         <indexRelation name="ct_bus_role2contact"    order="2" />
         <indexRelation name="ct_bus_role2bus_org"    order="3" />
      </index>
</table>

Attribute Name | Required | Default | Description
name | Yes |  | Specifies the name of the column to be indexed.
order | Yes |  | Order of the column/relation within the index.

See Also

index

indexRelation

Next

indexRelation
indexColumn

Did this topic help you? Email us your feedback.
Last updated: 3/30/2009 11:33:33 AM


indexRelation

XML format for the IndexRelation object

The indexRelation element is a child of the index object.

<table name="...">
      <index name="ct_bus_index" isUnique="true">
         <description><![CDATA[]]></description>
         <indexColumn   name="role_name"              order="0" />
         <indexColumn   name="focus_type"             order="1" />
         <indexRelation name="ct_bus_role2contact"    order="2" />
         <indexRelation name="ct_bus_role2bus_org"    order="3" />
      </index>
</table>

Attribute Name | Required | Default | Description
name | Yes |  | Specifies the name of the relation to be indexed.
order | Yes |  | Order of the column/relation within the index.

See Also

index

indexColumn

Next

relation
indexRelation

Did this topic help you? Email us your feedback.
Last updated: 3/30/2009 11:33:27 AM


relation

XML format for the relation object

The relation element is a child of the schema object.

The table contains children elements of type fromSide and toSide.

A relation and its inverse relation need only be defined once in a schema file.
Because of this, there is not a need for a ManyToOneRelation (MTO) object.

Note that the actual column will be created on the toSide for OneToOne and OneToMany relations.

<relation type="OneToMany">
   <fromSide table="site" name="cust_loc2case" >
      <description><![CDATA[Cases reported from the site ]]></description>
   </fromSide>
   <toSide table="case" name="case_reporter2site" >
      <description><![CDATA[Reporting customer site ]]></description>
   </toSide>
</relation>

<relation type="ManyToMany">

   <fromSide table="queue" name="queue2user" >

      <description><![CDATA[]]></description>

   </fromSide>

   <toSide table="user" name="user_assigned2queue" >

      <description><![CDATA[]]></description>

   </toSide>

</relation>

<relation type="OneToOne">

   <fromSide table="employee" name="employee2user" order="1"></fromSide>

   <toSide table="user" name="user2employee" order="1"></toSide>

</relation>

Attribute Name

Required

Default

Description

type

Yes

Cardinality of the relation. Valid values are:

  • OneToOne
  • OneToMany
  • ManyToMany
A relation and its inverse relation need only be defined once in a schema file. Because of this, there is not a need for a ManyToOneRelation (MTO) object.

isUserDefined

No

true

Indicates if this relation has been created by the customer.

See Also
schema
table
view
exclusiveRelationSet
sqlView

Next
fromSide

 relation

            Did this topic help you? Email us your feedback.

Last updated: 4/3/2009 2:40:15 PM


fromSide

XML format for the fromSide object

The fromSide element is a child of the relation object.

<relation type="OneToMany">
   <fromSide table="site" name="cust_loc2case" >
      <description><![CDATA[Cases reported from the site ]]></description>
   </fromSide>
   <toSide table="case" name="case_reporter2site" >
      <description><![CDATA[Reporting customer site ]]></description>
   </toSide>
</relation>

Attribute Name | Required | default | Description
name | Yes |  | Specifies the name of the relation.
table | Yes |  | The name of the table to which this relation belongs to.
description | No |  | Description of the relation and its purpose.
isRequired | No | false | Indicates if this relation is required or not.
order | No |  | Order of the relation within its parent table.

See Also

relation

toSide

Next

toSide
fromSide

Did this topic help you? Email us your feedback.
Last updated: 3/30/2009 11:34:16 AM


toSide

XML format for the toSide object

The toSide element is a child of the relation object.

<relation type="OneToMany">
   <fromSide table="site" name="cust_loc2case" >
      <description><![CDATA[Cases reported from the site ]]></description>
   </fromSide>
   <toSide table="case" name="case_reporter2site" >
      <description><![CDATA[Reporting customer site ]]></description>
   </toSide>
</relation>

Attribute Name | Required | default | Description
name | Yes |  | Specifies the name of the relation.
table | Yes |  | The name of the table to which this relation belongs to. Note that this table is where the actual column will be created on for OneToOne and OneToMany relations.
description | No |  | Description of the relation and its purpose.
isRequired | No | false | Indicates if this relation is required or not.
order | No |  | Order of the relation within its parent table.

See Also

relation

fromSide

Next

view
toSide

Did this topic help you? Email us your feedback.
Last updated: 3/30/2009 11:34:13 AM


view

XML format for the view object

The view element is a child of the schema object.

The view contains children elements of type viewColumn and join.

<schema schemaRevision="60" customerRevision=""
   <view name="rol_contct" id=289 >
      <viewcolumn name="first_name" tableName="contact" columnName="first_name" .../>
   </view>
</schema>

Attribute Name | Required | Default | Description
name | Yes |  | Specifies the name of the view. This must be a unique name amongst all tables and views.
id | Yes |  | Specifies the id number of the view. This must be a unique id amongst all tables and views. The customer range for IDs is 430-511 and 2000-4999.
description | No |  | Description of the view and its purpose.
groupName | No |  | Specifies the name of the application module that the view is used by.

See Also

schema

table

relation

exclusiveRelationSet

sqlView

Next

viewColumn
view

Did this topic help you? Email us your feedback.
Last updated: 3/30/2009 11:34:24 AM


viewColumn

XML format for the viewColumn object

The viewColumn element is a child of the view object.

<schema schemaRevision="60" customerRevision=""
   <view name="rol_contct" id=289 >
      <viewColumn name="first_name" tableName="contact" columnName="first_name" .../>
   </view>
</schema>

Attribute Name

Required

Default

Description

name

Yes

Specifies the name of the column.

tableName

Yes

Specifies the name of the table that this view column comes from.

columnName

Yes

Specifies the name of the column on the tableName table that this view column comes from.

description

No

Description of the viewColumn and its purpose.

alias

No

If referencing a join that has an alias, the name of the alias.

order

No

The order of the view column within the view.

clarifyGenericFieldID

No

-1

This data is used by Amdocs/Clarify applications to identify fields in different objects that are handled in a similar way. For example, a WIPbin contains different types of objects, such as cases and bugs. Certain fields in these objects (such as the ID number and title) are treated in the same way by the application and are assigned the same clarifyGenericFieldID.

Note: There is not an isUnique attribute. To indicate a viewColumn as unique, set the clarifyGenericFieldID to 3.

See Also

view

join

Next

join
viewColumn

Did this topic help you? Email us your feedback.
Last updated: 3/30/2009 11:33:15 AM


join

XML format for the join object

The join element is a child of the view object.

<join kind="inner" table="case" alias="" relation="case2x_platform" toAlias="" >
   <description><![CDATA[]]></description>
</join>

Attribute Name

Required

Default

Description

kind

Yes

Specifies the kind of the join. Valid values are:

  • Inner
  • Outer

description

No

Description of the join.

table

Yes

The name of the table to be joined to.

relation

Yes

The name of the relation on the table used for the join.

alias

No

Name of the alias for the table defined in the table attribute.

toAlias

No

Name of the alias for the table referenced by the other side of the relation.

See Also

view

viewColumn

Next

join Examples
join

Did this topic help you? Email us your feedback.
Last updated: 4/13/2009 3:09:04 PM


join Examples

The join examples in this section use the following two tables:

Contact:

Objid | First Name | Last Name
1 | Bill | Smith
2 | Mary | Jones
3 | Fred | Miller

Web_user:

Objid | Login Name | web_user2contact
7 | bsmith | 1
8 | mjones | 2
9 | tnorton | null

See Also

join

Next

1 - Inner Join
join Examples

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 10:29:30 AM


1 - Inner Join

<view ...>
   <join kind="Inner" table="web_user" relation="web_user2contact"></join>
</view>

This inner join will result in the following data being returned:

Contact Objid | First Name | Last Name | Web User Objid | Login Name
1 | Bill | Smith | 7 | bsmith
2 | Mary | Jones | 8 | mjones

See Also

join Examples

2 - Outer Join

3 - Outer Join

4 - Example of Inner Join with Aliases

Next

2 - Outer Join
1 - Inner Join

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 10:31:01 AM


2 - Outer Join

<view ...>
   <join kind="Outer" table="web_user" relation="web_user2contact"></join>
</view>

This will return all of the web_user records, and the associated contact records if they exist.
This outer join will result in the following data being returned:

Contact Objid | First Name | Last Name | Web User Objid | Login Name
1 | Bill | Smith | 7 | bsmith
2 | Mary | Jones | 8 | mjones
null | null | null | 9 | tnorton

See Also

join Examples

1 - Inner Join

3 - Outer Join

4 - Example of Inner Join with Aliases

Next

3 - Outer Join
2 - Outer Join

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 10:31:37 AM


3 - Outer Join

<view ...>
   <join kind="Outer" table="contact" relation="contact2web_user"></join>
</view>

This will return all of the contact records, and the associated web_user records if they exist.
This outer join will result in the following data being returned:

Contact Objid | First Name | Last Name | Web User Objid | Login Name
1 | Bill | Smith | 7 | bsmith
2 | Mary | Jones | 8 | mjones
3 | Fred | Miller | null | null

See Also

join Examples

1 - Inner Join

2 - Outer Join

4 - Example of Inner Join with Aliases

Next

4 - Example of Inner Join with Aliases
3 - Outer Join

Did this topic help you? Email us your feedback.
Last updated: 11/9/2007 10:32:31 AM


4 - Example of Inner Join with Aliases

<view id="4352" name="fc_wipelm_case" groupName="Support">
   <viewColumn name="priority" tableName="gbst_elm" columnName="title" alias="gse_priority"></viewColumn>
   <viewColumn name="severity" tableName="gbst_elm" columnName="title" alias="gse_severity"></viewColumn>
   other view columns here...

   <join kind="Inner" table="gbst_elm" alias="gse_priority" relation="resp_priority2case" toAlias="" ></join>

   <join kind="Inner" table="gbst_elm" alias="gse_severity" relation="resp_severity2case" toAlias="" ></join>

   other joins here...

</view>

See Also
join Examples
1 - Inner Join
2 - Outer Join
3 - Outer Join

Next
exclusiveRelationSet

 4 - Example of Inner Join with Aliases

            Did this topic help you? Email us your feedback.

Last updated: 11/9/2007 10:32:38 AM


exclusiveRelationSet

XML format for the exclusiveRelationSet object

The exclusiveRelationSet contains children elements of type relation.

<exclusiveRelationSet
   name="route_for"
   toSideTable="r_rqst"
   tableIdColumn="source_type"
   keyColumn="source_lowid"
   >

   <relation type="OneToMany">
         <fromSide name="case2r_rqst"  table="case" ></fromSide>
         <toSide   name="r_rqst2case"  table="r_rqst" ></toSide>
   </relation>

   <relation type="OneToMany">

         <fromSide name="subcase2r_rqst"  table="subcase" ></fromSide>

         <toSide   name="r_rqst2subcase"  table="r_rqst" ></toSide>

   </relation>

</exclusiveRelationSet>

Attribute Name | Required | default | Description
name | Yes |  | The name of the exclusive set. This allows for multiple exclusive relation sets on a single table.
toSideTable | Yes |  | The name of the table on which the exclusive relation set exists on, and on which the physical columns exist.
tableIdColumn | Yes |  | The name of the column on the toSideTable table that stores the table ID of the related data.
keyColumn | Yes |  | The name of the column on the toSideTable table that stores the foreign key(the objid) of the related data.

See Also
schema
table
relation
view
sqlView

Next
sqlView

 exclusiveRelationSet

            Did this topic help you? Email us your feedback.

Last updated: 3/30/2009 11:34:50 AM


sqlView

XML format for the sqlView object

The sqlView element is a child of the schema object.

The sqlView contains children elements of type sqlViewColumn and databaseSpecificSql.

<sqlView name="my_new_SQL_view" id=4040 >
   <sql>select web_login as owner from table_user union select req_originator as owner from table_fcs_detail</sql>

   <sqlViewColumn name="owner" datatype="String"  length="40">

           <description>this is the owner</description>

   </sqlViewColumn>

</sqlView >

Attribute Name | Required | Default | Description
name | Yes |  | Specifies the name of the sqlView. This must be a unique name amongst all tables and views.
id | Yes |  | Specifies the id number of the sqlView. This must be a unique id amongst all tables and views. The customer range for IDs is 430-511 and 2000-4999.
sql | Yes |  | Specifies the actual SQL for the view.
description | No |  | Description of the sqlView and its purpose.
groupName | No |  | Specifies the name of the application module that the sqlView is used by.
baseTable | No |  | Specifies the name of the main tables used in the view.

See Also
schema
table
relation
view
exclusiveRelationSet

Next
sqlViewColumn

 sqlView

            Did this topic help you? Email us your feedback.

Last updated: 3/30/2009 11:34:57 AM


sqlViewColumn

XML format for the sqlViewColumn object

The sqlViewColumn element is a child of the sqlView object.

   <sqlViewColumn name="owner" datatype="String"  length="40">
           <description>this is the owner</description>
   </sqlViewColumn>

Attribute Name

Required

Default

Description

name

Yes

Specifies the name of the sqlViewColumn.

dataType

Yes

The data type of the sqlViewColumn. Valid values are:

  • String
  • Integer
  • SmallInteger
  • TinyInteger
  • Real
  • Double
  • Character
  • String
  • LongString
  • DateTime
  • Decimal

length

No

0

For string columns, the length (in characters) of the column. This is required for string columns. The length can be increased, but cannot be decreased, as this could cause data loss.

description

No

Description of the sqlViewColumn and its purpose.

isSearchable

No

false

For Oracle database systems, indicates if this column should be searchable in a case-insensitive manner or not.

isUserDefined

No

true

Indicates if this sqlViewColumn has been defined by the customer.

clarifyGenericFieldID

No

-1

This data is used by Amdocs/Clarify applications to identify fields in different objects that are handled in a similar way. For example, a WIPbin contains different types of objects, such as cases and bugs. Certain fields in these objects (such as the ID number and title) are treated in the same way by the application and are assigned the same clarifyGenericFieldID.

isCurrencyData

No

false

Indicates if this sqlViewColumn holds monetary (currency) data.

isPredefined

No

false

This attribute is not typically used.

isMandatory

No

false

This attribute is not typically used.

isDirect

No

false

This attribute is not typically used.

order

No

The order of the sqlViewColumn within the table.

See Also

sqlView

databaseSpecificSql

Next

databaseSpecificSql
sqlViewColumn

Did this topic help you? Email us your feedback.
Last updated: 3/30/2009 11:33:07 AM


databaseSpecificSql

Dovetail SchemaEditor customers using Amdocs 6 or later databases can specify database specific SQL statements for their SQL Views.

XML format for the databaseSpecificSql object

The databaseSpecificSql element is a child of the sqlView object.

<databaseSpecificSql description="sql strings for this sqlView" isUserDefined="true">
   <sql targetDatabase="mssql" ><![CDATA[(select id_number from table_contact)]]></sql>
   <sql targetDatabase="oracle" ><![CDATA[(select id_number from table_contact)]]></sql>
   <sql targetDatabase="db2" ><![CDATA[(select id_number from table_contact)]]></sql>
</databaseSpecificSql>

Attribute Name

Required

Default

Description

description

No

Description of the database specific sql statements and their purpose.

isUserDefined

No

true

Indicates if this databaseSpecificSql element has been defined by the customer.

sql (element)

At least one is required.

One or more sql elements can be used to define the database specific sql statements for the target database.

targetDatabase

Yes

Each sql element requires that you select which database your database specific sql statement is targeting.

Valid values are:

  • mssql
  • oracle
  • db2

See Also

sqlView

sqlViewColumn

Next

Tips & Tricks
databaseSpecificSql

Did this topic help you? Email us your feedback.
Last updated: 4/13/2009 2:18:01 PM


Tips & Tricks

This section describes some tips and tricks for Dovetail SchemaEditor.

In This Chapter

Valid Ranges for Table and View id

Identifier lengths on Oracle databases

Database specific SchemaEditor files

XSD-based IntelliSense

Join Examples

SQL Views

Notes on Indexes

Special Characters in XML

Next

Valid Ranges for Table and View id
Tips & Tricks

Did this topic help you? Email us your feedback.
Last updated: 3/23/2009 3:02:00 PM


Valid Ranges for Table and View id

The valid range for new table and view ids is 430-571 and 2000-4999. This prevents any conflicts with existing or future vendor defined id numbers.

See Also

Tips & Tricks

Identifier lengths on Oracle databases

Database specific SchemaEditor files

XSD-based IntelliSense

Join Examples

SQL Views

Notes on Indexes

Special Characters in XML

Next

Identifier lengths on Oracle databases
Valid Ranges for Table and View id

Did this topic help you? Email us your feedback.
Last updated: 8/14/2007 10:27:39 AM


Identifier lengths on Oracle databases

  • On Oracle databases, table and view names must not be greater than 24 characters.
  • On Oracle databases, column and relation names must not be greater than 30 characters.
  • On Oracle databases, index names must not be greater than 30 minus the length of the table name.

    For example, on the employee table, the name of an index added to this table cannot be greater than 22 characters.

    30 - length("employee") = 30 - 8 = 22

See Also

Tips & Tricks

Valid Ranges for Table and View id

Database specific SchemaEditor files

XSD-based IntelliSense

Join Examples

SQL Views

Notes on Indexes

Special Characters in XML

Next

Database specific SchemaEditor files
Identifier lengths on Oracle databases

Did this topic help you? Email us your feedback.
Last updated: 1/3/2008 2:17:39 PM


Database specific SchemaEditor files

A common usage of .SchemaEditor files is to have one .SchemaEditor file per database. For example, one may have a development.SchemaEditor file, a test.SchemaEditor file, and a production.SchemaEditor file. Within each file is the individual database connection information, individual physical storage locations, etc.

See Also

Tips & Tricks

Valid Ranges for Table and View id

Identifier lengths on Oracle databases

XSD-based IntelliSense

Join Examples

SQL Views

Notes on Indexes

Special Characters in XML

Next

XSD-based IntelliSense
Database specific SchemaEditor files

Did this topic help you? Email us your feedback.
Last updated: 8/14/2007 10:27:42 AM


XSD-based IntelliSense

Many XML editors support the use of XSD-based IntelliSense, which makes it easy to create properly formatted and valid XML.
Dovetail SchemaEditor ships with XSD files for both Schema Script and for Data Dictionary files.

To configure Visual Studio 2005 to use custom XSD files for IntelliSense

There are multiple ways to get Visual Studio 2005 to use custom XSD files for IntelliSense.
One easy option is to simply place a copy of the XSD file(s) into the folder %ProgramFiles%\Microsoft Visual Studio 8\XML\Schemas

Reference this MSDN blog article for a good tutorial with multiple options on how to configure Visual Studio 2005 to use custom XSD files for IntelliSense.

See Also

Tips & Tricks

Valid Ranges for Table and View id

Identifier lengths on Oracle databases

Database specific SchemaEditor files

Join Examples

SQL Views

Notes on Indexes

Special Characters in XML

Next

Join Examples
XSD-based IntelliSense

Did this topic help you? Email us your feedback.
Last updated: 4/8/2009 4:09:27 PM


Join Examples

Joins, especially outer joins, can be particularly confusing.

Refer to the following document topics for examples of using inner and outer joins:

  • Examples of using joins in a Data Dictionary File
  • Examples of using joins in a Schema Script File

See Also

Tips & Tricks

Valid Ranges for Table and View id

Identifier lengths on Oracle databases

Database specific SchemaEditor files

XSD-based IntelliSense

SQL Views

Notes on Indexes

Special Characters in XML

Next

SQL Views
Join Examples

Did this topic help you? Email us your feedback.
Last updated: 4/8/2009 4:13:06 PM


SQL Views

A new concept called SQL views was introduced in Clarify 11.5, which allows an administrator to define the actual SQL for a view, as opposed to the schema tool dynamically building the SQL.

SQL views allow for:

  • A view with only one table. A common use of this is to take advantage of aggregates (max, min, count, sum, avg, etc.).
  • A view using multiple, unrelated tables.
  • A view that uses the UNION keyword to consolidate between multiple tables or views.

For example, the baseline view wipelm_all (view #: 5748) is a SQL view in order to take advantage of the UNION syntax.

Dovetail SchemaEditor supports these SQL views, both in Schema Script and in Data Dictionary files.

Note that a SQL view's definition in XML (Data Dictionary or Schema Script) resembles a table more than a view.

See Also

Tips & Tricks

Valid Ranges for Table and View id

Identifier lengths on Oracle databases

Database specific SchemaEditor files

XSD-based IntelliSense

Join Examples

Notes on Indexes

Special Characters in XML

Next

Note on Column Ordering
SQL Views

Did this topic help you? Email us your feedback.
Last updated: 4/13/2009 2:41:30 PM


Note on Column Ordering

An important point to keep in mind is that the order of the SQL view columns must be the same in the SQL as in the list of SQL view columns. For example, if your SQL select statement is select first_name, last_name, favorite_color..., then the list of columns should be in the same order, i.e. first_name is defined first, followed by last_name, and then favorite_color.

See Also

SQL Views

Note for Oracle Database Users

Next

Note for Oracle Database Users
Note on Column Ordering

Did this topic help you? Email us your feedback.
Last updated: 8/16/2007 2:28:31 PM


Note for Oracle Database Users

If a SQL view column is defined as searchable (using the isSearchable attribute), then the SQL statement should include the S_ column immediately after the non S_ column. The list of SQL view columns should include the normal column as well as the S_ column.

Example:

<addSqlView name="bugs_and_cases" id="4040">
    <description>my SQL view description</description>
    <sql>select title, s_title, id_number from table_case UNION select title, s_title, id_number from table_bug</sql>
 </addSqlView>

 <addSqlViewColumn name="title" sqlView="bugs_and_cases" datatype="String" >

     <length>255</length>

     <isSearchable>true</isSearchable>

  </addSqlViewColumn>

 <addSqlViewColumn name="s_title" sqlView="bugs_and_cases" datatype="String" >

     <length>255</length>

  </addSqlViewColumn>

 <addSqlViewColumn name="id" sqlView="bugs_and_cases" datatype="String" >

     <length>80</length>

  </addSqlViewColumn>

See Also
SQL Views
Note on Column Ordering

Next
Notes on Indexes

 Note for Oracle Database Users

            Did this topic help you? Email us your feedback.

Last updated: 4/13/2009 3:02:07 PM


Notes on Indexes

There are multiple ways to create indexes in the database:

  • Create an index using a schema file and a schema editing tool (Dovetail SchemaEditor, DD Editor, ddcomp). These indexes are stored in the metadata (ADP) tables.
  • -OR- Create an index using direct SQL (dbtune.sql script, Enterprise Manager, TOAD, other database tools, etc.)

Dovetail SchemaEditor does not track or maintain indexes that do not exist in the metadata (ADP) tables.

For example, if a column is being dropped by Dovetail SchemaEditor, and there is an index on that column, and that index exists in the metadata tables, then Dovetail SchemaEditor is aware of that index, and thus can validate the drop operation and its effect on the index. If that index was created using database tools directly, then Dovetail SchemaEditor is not aware of that index, thus it will not perform any validation.

Whenever a table is modified, existing indexes on that table are left as-is, regardless of how they were created. Other tools (such as DD Editor and ddcomp) will sometimes drop and re-create these indexes.

See Also

Tips & Tricks

Valid Ranges for Table and View id

Identifier lengths on Oracle databases

Database specific SchemaEditor files

XSD-based IntelliSense

Join Examples

SQL Views

Special Characters in XML

Next

Special Characters in XML
Notes on Indexes

Did this topic help you? Email us your feedback.
Last updated: 3/23/2009 11:54:42 AM


Special Characters in XML

There are a few characters in XML that need to be represented in a special fashion.

Character | Should be represented as | Comments
< | &lt; | left angle bracket
> | &gt; | right angle bracket
& | &amp; | ampersand
' | &apos; | single quote (an apostrophe)
" | &quot; | double quote

For example, this is invalid:

<table name="myTable" groupName="bread&butter" ...>

Replacing the ampersand with the proper escaped sequence yields valid XML:

<table name="myTable" groupName="bread&amp;butter" ...>

For more information on XML and its formatting, visit one of the many online resources, such as www.xml.com.

See Also

Tips & Tricks

Valid Ranges for Table and View id

Identifier lengths on Oracle databases

Database specific SchemaEditor files

XSD-based IntelliSense

Join Examples

SQL Views

Notes on Indexes

Next

Using CDATA for Text with Special Characters
Special Characters in XML

Did this topic help you? Email us your feedback.
Last updated: 1/3/2008 2:19:42 PM


Using CDATA for Text with Special Characters

XML parsers normally parse all the text in an XML document. When an XML element is parsed, the text between the XML tags is also parsed.

<description>This text is also parsed</description>

XML parsing is hindered when the text contains special characters, such as angle brackets, single quotes, or double quotes.

A CDATA element can be used to assist with this. Everything inside a CDATA section is ignored by the parser.

A CDATA section starts with "<![CDATA[" and ends with "]]>"

<description><![CDATA[ This is someone's complex description that says a < b when c = "foo" ]]></description>

For more information on XML and its formatting, visit one of the many online resources, such as www.xml.com.

See Also

Special Characters in XML

Next

Copyright Notice and Trademarks
Using CDATA for Text with Special Characters

Did this topic help you? Email us your feedback.
Last updated: 1/3/2008 2:19:56 PM


Copyright Notice and Trademarks

Copyright © 2010 Dovetail Software, Inc.
All rights reserved

Dovetail Software intends the information in this document solely for its licensees and to aid prospective customers in evaluating Dovetail Software products. Any other use is prohibited. Dovetail Software makes a written warranty to its licensees, as is stated in its written license agreement. Dovetail Software makes no warranty to any other party regarding the accuracy or usefulness of the information contained in this document. Dovetail Software reserves the right to modify the content of this document without obligation to notify any party. Dovetail Software is not liable for consequential or incidental damages.

Dovetail Software develops and supplies software and customizations to the following Clarify products: ClearSupport, ClearQuality, ClearHelpDesk, ClearContracts, Policies and Customers, ClearSales, ProductManager, ClearCallCenter, and ClearLogistics.

ManyDovetail Software products use the following Clarify tools: User Interface Editor, ClearBasic Exchange, Data Dictionary Editor, ClearBasic Batch, Data Exchange, Clarify Business Objects, RuleManager, eBusiness Framework, ClearBasic, and Clarify Low-Level API©.

Clarify, its product names, and tools are trademarks of Amdocs, which may include the following: AmdocsCRM, ClearConfigurator, SalesBasic, ClearConfigurator Workshop, Notifier, eSupport, Classification Engine, eOrder Routing Server, eResponse Manager, ClearEnterprise, Email Clerk, Email Manager, Traveler, e.link, Diagnosis Engine, and Flexible Deployment.

Next

log4net
Copyright Notice and Trademarks

Did this topic help you? Email us your feedback.
Last updated: 12/14/2010 5:57:37 PM


log4net

This Dovetail Software product references the log4net assembly. Log4net is part of the Apache Logging Services project. More information about log4net can be found on the log4net Project Home Page.

log4net is Copyright (C) 1999 The Apache Software Foundation. All rights reserved.

log4net

Did this topic help you? Email us your feedback.
Last updated: 4/1/2009 3:18:26 PM


SchemaEditor


Did this topic help you? Email us your feedback.
Last updated: 6/26/2013 10:55:34 AM


Contents Index Search