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';
We promise we don't send spam