Automating staging environments for Jira Part 1

We recommend customer set up staging environments for Atlassian Jira. Unfortunately, many customers decide they don’t want to build and maintain a second system. If a staging system is there, it’s rarely kept up to date. This occurs because refreshing a staging environment is a tiresome and error prone task.

Mistakes in the process can have dire consequences. The staging server could:

  • Process the inbound emails of the production one.
  • Send incorrect notifications to users
  • Mislead an admin into thinking they’re making changes in one system instead of the other
  • Redirect admins to the wrong system without them noticing (due to bad base URL)

 

To help mitigate this, we have created a baseline SQL script which you can tweak to match your needs. This will allow you to quickly, easily, and consistently update your Jira staging environment. Now you can go to the beach and relax while your automated staging environment gets updated regularly.

Code is below and on Bitbucket. Part 2 of this will handle the changes that we recommend at the filesystem level.

# The syntax of this file is built for PostgreSQL

#######################################################
#
# ANNOUNCEMENT BANNER
#
# Update the contents that you want to appear in your 
# announcement banner and whether it should be public
# or private
#
#######################################################

update propertytext
        set propertyvalue = 'THIS IS A TEST SYSTEM'
        where propertytext.id = (
        	select id
        		from "propertyentry"
        		where entity_name = 'jira.properties' and entity_id = '1' and property_key = 'jira.alertheader');


update propertystring
        set propertyvalue = 'public'
        where propertystring.id = (
	        select id
		        from "propertyentry"
		        where entity_name = 'jira.properties' and entity_id = '1' and property_key = 'jira.alertheader.visibility');


#######################################################
#
# BASE URL
#
# Update the Base URL 
#
#######################################################


update propertystring
	set propertyvalue = 'http://jira.servername.com'
from propertyentry as PE
	where PE.id=propertystring.id
	and PE.property_key = 'jira.baseurl';



#######################################################
#
# User Directory
#
# Update the User Directory Configuration
# Important ones are basedb, url, username, password, 
# interval, and id. Remember to replicate this as many times as
# directories tyou need to update.
#
# if you need to update anything else, pull it from the
# cwd_directory_attribute table
#
#######################################################

update cwd_directory_attribute
	set attribute_value = 'o=sevenSeas'
	where directory_id = '10000'
	and attribute_name = 'ldap.basedn';

update cwd_directory_attribute
	set attribute_value = 'ldap://localhost:10389'
	where directory_id = '10000'
	and attribute_name = 'ldap.url';

update cwd_directory_attribute
	set attribute_value = 'uid=admin,ou=system'
	where directory_id = '10000'
	and attribute_name = 'ldap.userdn';

update cwd_directory_attribute
	set attribute_value = 'password'
	where directory_id = '10000'
	and attribute_name = 'ldap.password';

update cwd_directory_attribute
	set attribute_value = '3600'
	where directory_id = '10000'
	and attribute_name = 'directory.cache.synchronise.interval';

#######################################################
#
# Incoming Mail Server
#
# Update the host, username, port, and password
# smtp_port is the right value regardless of protocol, 
# its being overloaded
#
# If you want to update other properties, you can get
# them from the mailserver table.
#
#######################################################


UPDATE mailserver 
	SET SERVERNAME = 'example.com', mailusername = 'example', mailpassword = 'password', smtp_port = '110'
	WHERE ID = '10000';


#######################################################
#
# JSD Mail Handlers
#
# You can update the server, and then make sure to
# Update the mail channel as well to point at the new
# email address.
#
#######################################################


UPDATE "AO_2C4E5C_MAILCONNECTION"
	SET "HOST_NAME" = 'imap.gmail.com', "PORT" = '993', "PROTOCOL" = 'imaps', "USER_NAME"  = 'USERNAME', "PASSWORD" = "PASSWORD", "EMAIL_ADDRESS"  = 'username@example.com', "FOLDER" = 'inbox', "TLS"  = 'f'
	WHERE "ID" = 1;


UPDATE "AO_54307E_EMAILCHANNELSETTING"
	SET "EMAIL_ADDRESS" = 'username@example.com'
	WHERE "ID" = 1;



#######################################################
#
# Introduction Gadget Text
#
# Update the content of the Introduction Gadget
#
#######################################################




update propertytext
        set propertyvalue = 'System was refreshed at ' || timeofday() || ' Thanks for your cooperation.'
        where id = (
        	select id
        		from propertyentry
        		where entity_name = 'jira.properties' and entity_id = '1' and property_key = 'jira.introduction');


#######################################################
#
# Application Links
#
# Delete all Application links
#
# Note: There is something that this is still 
# overlooking which results in some harmless but spammy
# log messages 
#
# Note 2: This portion was only tested with MS SQL
#
#######################################################


DELETE FROM oauthspconsumer
DELETE FROM oauthconsumer;
DELETE FROM oauthconsumertoken 
DELETE FROM trustedapp;

DELETE FROM propertystring WHERE id IN (
	SELECT id FROM propertyentry 
	INNER JOIN (SELECT substring(a.property_key,16,36) as prop_key FROM propertyentry a join propertystring b on a.id=b.id where a.property_key like 'applinks.admin%name') L
	ON property_key LIKE 'applinks.%'+L.prop_key+'%'
);


DELETE p FROM propertyentry p
INNER JOIN (SELECT substring(a.property_key,16,36) as prop_key FROM propertyentry a join propertystring b on a.id=b.id where a.property_key like 'applinks.admin%name') L
ON property_key LIKE 'applinks.%'+L.prop_key+'%';

DELETE p FROM propertytext p WHERE id in (
	SELECT id FROM propertyentry 
	INNER JOIN (SELECT substring(a.property_key,16,36) as prop_key FROM propertyentry a join propertystring b on a.id=b.id where a.property_key like 'applinks.admin%name') L
	ON property_key LIKE '%ual.'+L.prop_key+'%');

DELETE p FROM propertyentry p
INNER JOIN (SELECT substring(a.property_key,16,36) as prop_key FROM propertyentry a join propertystring b on a.id=b.id where a.property_key like 'applinks.admin%name') L
ON property_key LIKE '%ual.'+L.prop_key+'%';

DELETE p FROM propertyentry p WHERE id in (
	SELECT id FROM propertystring
	INNER JOIN (SELECT substring(a.property_key,16,36) as prop_key FROM propertyentry a join propertystring b on a.id=b.id where a.property_key like 'applinks.admin%name') L
	ON propertyvalue LIKE '%'+L.prop_key+'%');

DELETE p FROM propertystring p
INNER JOIN (SELECT substring(a.property_key,16,36) as prop_key FROM propertyentry a join propertystring b on a.id=b.id where a.property_key like 'applinks.admin%name') L
ON propertyvalue LIKE '%'+L.prop_key+'%';

DELETE p FROM propertyentry p WHERE id in (
	SELECT id FROM propertytext 
	INNER JOIN (SELECT substring(a.property_key,16,36) as prop_key FROM propertyentry a join propertystring b on a.id=b.id where a.property_key like 'applinks.admin%name') L
	ON propertyvalue LIKE '%'+L.prop_key+'%');

DELETE p FROM propertytext p
INNER JOIN (SELECT substring(a.property_key,16,36) as prop_key FROM propertyentry a join propertystring b on a.id=b.id where a.property_key like 'applinks.admin%name') L
ON propertyvalue LIKE '%'+L.prop_key+'%';

DELETE p FROM propertystring p where id in (select id from propertyentry where property_key like 'applinks.global%');

Signup for more Atlassian Jira tips and tricks

(3) Comments
  1. Useful! Another thing I do is to change the color of the top header to something very different. Helps give people a clue that they are not on the production server any more.

Leave a Reply