---------------------------------------------------------------------------
Practices and Guidelines for Connect Database Update Scripts
---------------------------------------------------------------------------

Please refer to the active version of this document in the wiki for most current information:-
  https://zerowing.corp.adobe.com/display/Breeze/Connect+Database+Update+Script+Practices

Naming Convention

  Each update file gets executed in order by the installer if the product version is higher than the update file version.

  Each update file has the following format: update_<major>_<minor/SP>_<update>_<revision>.sql

  E.g. for 8.2.1: update_8_2_1_0.sql


Supporting a New Version

  Add a new update script
  If no update file exists for the release being worked on, follow these steps:

  1. Copy update_x_x_x_x.sql.template to a file named according to the naming shown above

  2. Set the +k flag on the file type in Perforce.
     The file type must be ktext (+keyword) to allow keyword expansion of the $ID and $CHANGE fields in the script execution logger.

  3. Update the version
     a. Update the database version insert statement in the database_data.sql file to reflect this latest version.
     b. Update the database version insert statement in the update_x_x_x_x.sql file to reflect the same version as the update file name.
		E.g.
			UPDATE PPS_DATABASE_VERSION 
			SET MAJOR_RELEASE = 8, MINOR_RELEASE = 2, [UPDATE] = 1, REVISION = 0
			GO

			
DB Revision Requirements & Conventions
   * Place all statements to be run between the start & end comment blocks.
   * Terminate all sql statements with semi-colons.
   * Wrap all IF statements with begin/end indicators.
   * For any SQL to be executed only on hosted databases, use the dbo.isHosted() function call, e.g.
		 IF dbo.isHosted() = 1 
		 BEGIN 
		   -- Hosted-only operations here --
		 END
   * Schema changes
     Add all schema changes to database_schema.sql
     Changes should be done to the initial declaration, not as ALTERations later in the script.
   * Data changes
     Add all data changes to database_data.sql
