Automating staging environments for Jira Part 1

We recommend customer set up Atlassian Jira staging environments. 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.

1| # The syntax of this file is built for PostgreSQL
2| 
3| #######################################################
4| #
5| # ANNOUNCEMENT BANNER
6| #
7| # Update the contents that you want to appear in your
8| # announcement banner and whether it should be public
9| # or private
10| #
11| #######################################################
12| 
13| update propertytext
14| set propertyvalue = 'THIS IS A TEST SYSTEM'
15| where propertytext.id = (
16| select id
17| from "propertyentry"
18| where entity_name = 'jira.properties' and entity_id = '1' and property_key = 'jira.alertheader');
19| 
20| 
21| update propertystring
22| set propertyvalue = 'public'
23| where propertystring.id = (
24| select id
25| from "propertyentry"
26| where entity_name = 'jira.properties' and entity_id = '1' and property_key = 'jira.alertheader.visibility');
27| 
28| 
29| #######################################################
30| #
31| # BASE URL
32| #
33| # Update the Base URL
34| #
35| #######################################################
36| 
37| 
38| update propertystring
39| set propertyvalue = 'http://jira.servername.com'
40| from propertyentry as PE
41| where PE.id=propertystring.id
42| and PE.property_key = 'jira.baseurl';
43| 
44| 
45| 
46| #######################################################
47| #
48| # User Directory
49| #
50| # Update the User Directory Configuration
51| # Important ones are basedb, url, username, password,
52| # interval, and id. Remember to replicate this as many times as
53| # directories tyou need to update.
54| #
55| # if you need to update anything else, pull it from the
56| # cwd_directory_attribute table
57| #
58| #######################################################
59| 
60| update cwd_directory_attribute
61| set attribute_value = 'o=sevenSeas'
62| where directory_id = '10000'
63| and attribute_name = 'ldap.basedn';
64| 
65| update cwd_directory_attribute
66| set attribute_value = 'ldap://localhost:10389'
67| where directory_id = '10000'
68| and attribute_name = 'ldap.url';
69| 
70| update cwd_directory_attribute
71| set attribute_value = 'uid=admin,ou=system'
72| where directory_id = '10000'
73| and attribute_name = 'ldap.userdn';
74| 
75| update cwd_directory_attribute
76| set attribute_value = 'password'
77| where directory_id = '10000'
78| and attribute_name = 'ldap.password';
79| 
80| update cwd_directory_attribute
81| set attribute_value = '3600'
82| where directory_id = '10000'
83| and attribute_name = 'directory.cache.synchronise.interval';
84| 
85| #######################################################
86| #
87| # Incoming Mail Server
88| #
89| # Update the host, username, port, and password
90| # smtp_port is the right value regardless of protocol,
91| # its being overloaded
92| #
93| # If you want to update other properties, you can get
94| # them from the mailserver table.
95| #
96| #######################################################
97| 
98| 
99| UPDATE mailserver
100| SET SERVERNAME = 'example.com', mailusername = 'example', mailpassword = 'password', smtp_port = '110'
101| WHERE ID = '10000';
102| 
103| 
104| #######################################################
105| #
106| # JSD Mail Handlers
107| #
108| # You can update the server, and then make sure to
109| # Update the mail channel as well to point at the new
110| # email address.
111| #
112| #######################################################
113| 
114| 
115| UPDATE "AO_2C4E5C_MAILCONNECTION"
116| SET "HOST_NAME" = 'imap.gmail.com', "PORT" = '993', "PROTOCOL" = 'imaps', "USER_NAME"  = 'USERNAME', "PASSWORD" = "PASSWORD", "EMAIL_ADDRESS"  = 'username@example.com', "FOLDER" = 'inbox', "TLS"  = 'f'
117| WHERE "ID" = 1;
118| 
119| 
120| UPDATE "AO_54307E_EMAILCHANNELSETTING"
121| SET "EMAIL_ADDRESS" = 'username@example.com'
122| WHERE "ID" = 1;
123| 
124| 
125| 
126| #######################################################
127| #
128| # Introduction Gadget Text
129| #
130| # Update the content of the Introduction Gadget
131| #
132| #######################################################
133| 
134| 
135| 
136| 
137| update propertytext
138| set propertyvalue = 'System was refreshed at ' || timeofday() || ' Thanks for your cooperation.'
139| where id = (
140| select id
141| from propertyentry
142| where entity_name = 'jira.properties' and entity_id = '1' and property_key = 'jira.introduction');
143| 
144| 
145| #######################################################
146| #
147| # Application Links
148| #
149| # Delete all Application links
150| #
151| # Note: There is something that this is still
152| # overlooking which results in some harmless but spammy
153| # log messages
154| #
155| # Note 2: This portion was only tested with MS SQL
156| #
157| #######################################################
158| 
159| 
160| DELETE FROM oauthspconsumer
161| DELETE FROM oauthconsumer;
162| DELETE FROM oauthconsumertoken
163| DELETE FROM trustedapp;
164| DELETE FROM oauthsptoken;
165| 
166| 
167| DELETE FROM propertystring WHERE id IN (
168| SELECT id FROM propertyentry
169| 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
170| ON property_key LIKE 'applinks.%'+L.prop_key+'%'
171| );
172| 
173| 
174| DELETE p FROM propertyentry p
175| 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
176| ON property_key LIKE 'applinks.%'+L.prop_key+'%';
177| 
178| DELETE p FROM propertytext p WHERE id in (
179| SELECT id FROM propertyentry
180| 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
181| ON property_key LIKE '%ual.'+L.prop_key+'%');
182| 
183| DELETE p FROM propertyentry p
184| 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
185| ON property_key LIKE '%ual.'+L.prop_key+'%';
186| 
187| DELETE p FROM propertyentry p WHERE id in (
188| SELECT id FROM propertystring
189| 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
190| ON propertyvalue LIKE '%'+L.prop_key+'%');
191| 
192| DELETE p FROM propertystring p
193| 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
194| ON propertyvalue LIKE '%'+L.prop_key+'%';
195| 
196| DELETE p FROM propertyentry p WHERE id in (
197| SELECT id FROM propertytext
198| 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
199| ON propertyvalue LIKE '%'+L.prop_key+'%');
200| 
201| DELETE p FROM propertytext p
202| 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
203| ON propertyvalue LIKE '%'+L.prop_key+'%';
204| 
205| DELETE p FROM propertystring p where id in (select id from propertyentry where property_key like 'applinks.global%');
206| 
207| DELETE from AO_21D670_WHITELIST_RULES where "TYPE" = 'APPLICATION_LINK';
208| 
209| Delete from propertyentry where PROPERTY_KEY like 'applinks.admin%';
210| 
211| # Don't remember how we got the ID in this one:
212| # UPDATE propertytext SET propertyvalue = '#java.util.List\n' WHERE id = '83329';
update.sqlview raw

Related Article

We promise we don't send spam