REQUEST A DEMO

Deletes (Drops) in SchemaEditor

While discussing Dovetail SchemaEditor recently, a question came up around deletes (drops), i.e. How do you delete a column using SchemaEditor, as it doesn’t seem like SchemaScript supports deletes.

 

First, a little background.

 

SchemaEditor works with Data Dictionary files and with SchemaScript files.

 

A Data Dictionary file is a complete reference of the database schema. This is similar to a Clarify complete schema files.

 

SchemaScript 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.

 

However, only adds and updates can be performed using SchemaScript. Deletes are not currently supported.

 

 

Adds using SchemaScript

 

Here’s a simple example of a SchemaScript file that adds a new column to a table, and also adds this column to an existing view.

 

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">
  <addColumn table="case" name="x_test_integer" dataType="Integer" />
  <addViewColumn view="cas_secure" column="x_test_integer" name="x_test_integer" table="case"/>
</schemaScript>

 

Nice and simple.

Deletes

 

As I mentioned earlier, deletes are not currently supported when using SchemaScript. In order to perform a delete:

 

  1. Export the Schema to a File. This will create a complete Data Dictionary file.
  2. Modify the Schema File, removing the schema elements you wish to delete.
  3. Apply the changes to the database.

 

This is similar to the process when working with Clarify schema files and using Clarify ddcomp or Amdocs Schema Manager.

Delete a column and view column at the same time

 

Can SchemaEditor drop a (table) column and a view column (based on that table column) at the same time?

 

Yes, it can.

 

I suspect the reason for this question was due to a limitation of the Amdocs Schema Manager application.

 

After I added the column and view column using the SchemaScript defined above, I exported my schema to a file, edited that file to remove that column and view column, and applied the changes.

 

Looking at the SchemaEditor output report, I can see that it did just what I was expecting:

 

Dovetail SchemaEditor Difference Report 
———————————————————————————- 
Date: 2013-05-03 11:00:49 
Action: Apply 
User: fcs\gary 
Database Name: classic 
Input File: Schema.xml 
———————————————————————————- 
Changed schema 
    Changed table “case” 
        Removed column “x_test_integer” 
    Changed view “cas_secure” 
        Removed view column “x_test_integer”

 

Notice that it reported that it removed both the column and the view column.

And there you have it. Hope this helps explains a bit about deletes using SchemaEditor.