REQUEST A DEMO

Authoring Schema Script to ‘Upsert’ changes to your database. (a.k.a Add and/or Update)

Authoring Schema Script

This post will teach you the best way to author your Schema Editor schema script to do upserts, adds and/or updates to your Amdocs database schema. Luckily, you do not often have to worry about doing upserts but there is one situation where not doing them can get you into trouble.

Anytime you provide schema script which should work whether the object exists or not and must change properties of the original.

Sorry if that just lost you. Let’s start out with the basics. 

What is this Schema Script?

We created Schema Editor for Amdocs users who need a better way to update the database schema of their Clarify/Amdocs database. When we created SchemaEditor, Gary Sherman was adamant that we needed a new workflow for updating the schema. The classic workflow involved exporting the database schema, manually editing the result, and reapplying the changes. It is not easy to automate the classic workflow. Thus, SchemaScript was born as a one shot way of applying additional schema (tables, fields, views.. what not) to your database. Best to illustrate with an example. 

Example of adding a table

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">
 
	<!-- Schema required for password reset functionality -->
	<addTable id="4304" name="password_reset"/>
	<addColumn table="password_reset" name="objid" dataType="Integer">
		<clarifyGenericFieldID>3</clarifyGenericFieldID>
	</addColumn>
	<addColumn table="password_reset" name="email" dataType="String">
		<length>80</length>
	</addColumn>
	<addColumn table="password_reset" name="loginName" dataType="String">
		<length>30</length>
	</addColumn>
	<addColumn table="password_reset" name="token" dataType="String">
		<length>80</length>
	</addColumn>
	<addColumn table="password_reset" name="expiration_date" dataType="DateTime"/>
	<addColumn table="password_reset" name="user_objid" dataType="Integer" />
 
</schemaScript>

The script above adds a table called password_reset which is used by or web clients to facilitate, guess what.. resetting passwords.

When you apply this script against your database, it does not matter if the table already exists. Schema scripts are additive. When you say addTable and the table already exists nothing changes. 

Updating existing schema

Besides adding new schema you can update existing tables, views, what-have-you. The difference between an add and an update is that the what-have-you needs to exist or you’ll get an error.

So let’s update the password_reset table’s description using schema script as it’s always a good idea to document your schema.

Example of updating a table

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">
 
	<updateTable name="password_reset">
		<description>Used by Dovetail client applications to facilitate resetting user passwords without administrator intervention</description>
	</updateTable>
 
</schemaScript>

Upserting Schema

Finally, here is the point of the post. Sometimes you have to do both an add and an update of your schema. Why? Read on.

When packaging schema script for our applications, I recently ran into a situation where I needed to add a column to a SQL view which was already in use by customers and thus already in my test database. So, updated my schema script to have an additional field and updated the SQL statement appropriately. Here is the result:

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">
 
	<addSqlView name="fc_contact_view" id="4759">
		<baseTable>contact</baseTable>
		<groupName>Dovetail Support</groupName>
		<description>Distinct contacts at a site with no duplicates in the case where a contact has multiple roles at a site.</description>
		<sql>
			SELECT DISTINCT r.objid, c.objid as contact_objid, s.objid as site_objid, c.first_name, c.last_name, c.phone, c.update_stamp as last_modified,
			s.name as site_name, s.site_id as site_id, r.primary_site as is_primary_site, c.status as is_active, c.e_mail, r.role_name
			FROM table_contact c
			INNER JOIN table_contact_role r ON c.objid = r.contact_role2contact
			INNER JOIN table_site s ON s.objid = r.contact_role2site
			WHERE c.status = 0 AND 0=0
		</sql>
	</addSqlView>
	 
	<addSqlViewColumn name="contact_role_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
	<addSqlViewColumn name="contact_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
	<addSqlViewColumn name="site_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
	<addSqlViewColumn name="first_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="last_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="phone" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="last_modified" sqlView="fc_contact_view" dataType="DateTime"></addSqlViewColumn>
	<addSqlViewColumn name="site_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="site_id" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="is_primary_site" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
	<addSqlViewColumn name="is_active" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
	<addSqlViewColumn name="e_mail" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="role_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
 
</schemaScript>

The highlighted lines show the schema script for the new view column and SQL statement changes.

Oops, that didn’t work

When these changes are applied and the SQL view already exists in the schema you will get this error:

Error Occurred : ‘table_fc_contact_view’ has fewer columns than were specified in the column list.

This is an error coming from the database complaining that the fields in you SQL query do not match the number of columns you’ve defined. Why? Because the SQL statement is contained in an additive operation addSqlView it is ignored when that view already exists in your database. Schema Editor will ignore an add operation when it figures out that that object is already present. This warning from Schema Editor should clue you in.

Warning: SqlView “fc_contact_view” was not added. It is already in the schema.

Because the addSqlView was ignored the new SQL statement did not make it into the schema and thus the number of view columns does not match the SQL statement.

The Solution

The way to solve this problem is to do both an add and update operations. An add to put the object in the schema in the case where it does not yet exist. And an update to make sure any new changes get written to the schema object. You’ll need to do this anytime you want to change the state of an object that may already exist in your schema yet want to create the object for the situation where it does not yet exist.

Like I mentioned at the beginning, most people will not run into this issue but for Dovetail when we ship updates to products which modify existing schema we need to worry about this.

The full correct schema script

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">
 
	<addSqlView name="fc_contact_view" id="4759"><sql/></addSqlView>
	<updateSqlView name="fc_contact_view">
		<baseTable>contact</baseTable>
		<groupName>Dovetail Support</groupName>
		<description>Distinct contacts at a site with no duplicates in the case where a contact has multiple roles at a site.</description>
		<sql>
			SELECT DISTINCT r.objid as contact_Role_objid, c.objid as contact_objid, s.objid as site_objid, c.first_name, c.last_name, c.phone, c.update_stamp as last_modified,
			s.name as site_name, s.site_id as site_id, r.primary_site as is_primary_site, c.status as is_active, c.e_mail, r.role_name
			FROM table_contact c
			INNER JOIN table_contact_role r ON c.objid = r.contact_role2contact
			INNER JOIN table_site s ON s.objid = r.contact_role2site
			WHERE c.status = 0 AND 0=0
		</sql>
	</updateSqlView>
	<addSqlViewColumn name="contact_role_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>   
	<addSqlViewColumn name="contact_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
	<addSqlViewColumn name="site_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
	<addSqlViewColumn name="first_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="last_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="phone" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="last_modified" sqlView="fc_contact_view" dataType="DateTime"></addSqlViewColumn>
	<addSqlViewColumn name="site_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="site_id" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="is_primary_site" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
	<addSqlViewColumn name="is_active" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
	<addSqlViewColumn name="e_mail" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
	<addSqlViewColumn name="role_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
 
</schemaScript>

 

Update (July 30th, 2010)

I moved the contact_role_objid addSqlViewColumn element to the end of the XML above. The reason is when adding sql view columns to the view, the view columns appear in the select statement in the order they are added. I also corrected the SQL to reflect the expected order of the view columns in the generated view.

Conclusion

Schema script is a great feature of Schema Editor. I hope you do use it when scripting changes to your database schema. I do realize that we may want to add native support for ‘upserts’ to Schema Editor, but this scenario is relatively rare and the workaround I talk about is easy to use when you run into it. Let me know what you think and about any of the cool stuff you’ve done with Schema Editor.