Affichage des articles dont le libellé est SQL. Afficher tous les articles

vendredi 12 février 2021

Jira and Confluence DB setup PostgreSQL

Atlassian Confluence

https://confluence.atlassian.com/doc/configuring-a-datasource-connection-937166084.html

$ sudo -u postgres psql postgres
postgres=# CREATE USER confluence;
CREATE ROLE
postgres=# ALTER USER confluence with PASSWORD 'confluencepwd';
ALTER ROLE
postgres=# CREATE DATABASE confluencedb WITH OWNER = confluence ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
CREATE DATABASE
postgres=# \q

choose the "production install" to make sure you're offered the DB configuration option

Confluence 5.5 confluence.cfg.xml :

confluence5.5$ cat confluence.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>

<confluence-configuration>

<setupStep>finishsetup</setupStep>

<setupType>custom</setupType>

<property name="attachments.dir">${confluenceHome}/attachments</property>

<property name="confluence.license.hash">zdDzjZdbADawoyURqdGzoPaZBAgZKUHUYsHVjDZIEvvPyszv2XGziugEuwkSfQ&tg;Gdt6xeE2Xd9zIm1EpkgsJaGpbCyrw</property>

<property name="confluence.license.message">zbAbzjWIpGHwCEPxwadtQDNsBirASzeAvdenNYxIEfgRidiyujEcxrnDWSbWuGVKbgehfZHRPfcxbDFasXfbofNaPgcAwQJuzycjPPOPFvpjyUgxKytaugGgBKevkPAgDRJwDOcKFdTNguqgEqzjtXoOHFbACGavHChWJweOuokZevvxXiTBydzgzbrcXhFWQIHDrqeEHaPXCgzCBqjVBdTyNYCrADHZKIDhcpxTNCjJjewXaicAOvssJvkRQcBqeyoeKcuXyrtvtyDXefYaeffWFbiQYcTBPAdBPGtenCKxZqpubCHYPdaXuqvSARJcuQBzBFvkuovnVJNNDBPpvSDAZpaOaJDOTkqvCXjoIxeDCsqkqdwwsfwnvXWsyzyITiQRPNShDmUESMzT5Imuvab8oNWjYvUj9Xe24N4jL6Wt8GLkwcyaWLBYZgSPeqPgRLozPuPlPjMObtRzOHaqj4m3e4o5KssixIGh&yg;1vT6qpRg0INFzR6OmnAkxOstG9OwNOCev&tg;a7tX9rxXs6u8i1jDyhH3ldYs8QUaqXeMrknEN5cxbIXbp9QAEjM7Z7AMHlaH4mcUgCc1B61j&yg;k&yg;7Ek&tg;fux6A9pr&yg;&yg;Ht&tg;hmHH2q5TFZd5EcZOqJQrp0nFuX9Gi1skZYGOxW&tg;BygPOwDXLkVZpe8WQPAqYCSedTpKGR2nDkWVETxEILEU&tg;kOeNoTlfbviJq4a5egI6eLA6Me3jlfBQL7f9pw&yg;fwS5zmtQJOOz7L&tg;qph1b&yg;DK8pTZNrkCzi4i38f</property>

<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>

<property name="hibernate.connection.password">confluencepwd</property>

<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/confluencedb</property>

<property name="hibernate.connection.username">confluence</property>

<property name="hibernate.database.lower_non_ascii_supported">false</property>

<property name="hibernate.dialect">net.sf.hibernate.dialect.PostgreSQLDialect</property>

<property name="lucene.index.dir">${localHome}/index</property>

<property name="webwork.multipart.saveDir">${localHome}/temp</property>

</properties>

</confluence-configuration>

Confluence 6.13.19 confluence.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>

<confluence-configuration>

<setupStep>complete</setupStep>

<setupType>custom</setupType>

<property name="access.mode">READ_WRITE</property>

<property name="admin.ui.allow.daily.backup.custom.location">false</property>

<property name="admin.ui.allow.manual.backup.download">false</property>

<property name="admin.ui.allow.site.support.email">false</property>

<property name="atlassian.license.message">NNNOoN0BQNbCrAc9xI1CtmNHuh/7X0v80EtzME/dxvMBjZxPL8d+fhlzxbBefeX1OrKs27UAe0FGK iFpAa36ChpxmYxkbAmNJX8hoaroopBWk4Mg2Ev9DwHSVIaBPr5L1dI11Jkv5BEp0HDA6EeVF5Rk2 xucHHW2H1WqpSKWEcXiHMYmgXUifEXVRtJtHFTFSMKtHtIx975cLECoXTNWpNar+4nW6ghuorWCz OqFyi1Ul/nC3SB5VdUm5gmqE4H7w9uf4toOOIgxbGtsvPyIg3CEJm30emrwdvpzizdKP3+23GeQi e1z1co1yzEWxZMkOMml5V9/1rzTksbWEWEBcYMRmRZ3IyDbRPFyzLEwBg8ytr/T3gNZpXqwgIcnc X7Vm04xavyaxddq8hxkbQTWxFBt7i6JIkZCwUT1tKbdGuFT3dCw94Xwy+ZLorFPGNGo1NDgA80X0 PTA0kuRPrWf2GH8oonbLruehk/uO4RfhHbjYNVHN70z74+a+XYnayGBhydYW82HVG4PSRna3Jf1k 1m9QN7Jp7sdpRJhgb1yK02uc</property>

<property name="attachments.dir">${confluenceHome}/attachments</property>

<property name="confluence.database.choice">postgresql</property>

<property name="confluence.database.connection.type">database-type-standard</property>

<property name="confluence.setup.server.id">SERVER-ID</property>

<property name="hibernate.c3p0.preferredTestQuery">select 1</property>

<property name="hibernate.c3p0.validate">false</property>

<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>

<property name="hibernate.connection.password">confluencepwd</property>

<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/confluencedb</property>

<property name="hibernate.connection.username">confluencedbuser</property>

<property name="hibernate.dialect">com.atlassian.confluence.impl.hibernate.dialect.PostgreSQLDialect</property>

<property name="jwt.private.key">ZVVT/tVONQNAOtxduxvT9j0ONDRSNNFPOhtjttoxNtRNNbVOtDPDXUoNQgeeET8v/rvaFZCT1XjVJIAl2RhnB1xq5MkWkNoelY+BI3HYicksyT36sySKYJTcsyfG1sPDejf/RniqrH0aU72CL5bOmY/kHlC2TjUF9sokDG+52Y0trfRHKAyehAHqcfiI53LT1fOoV8Q9tSScy7iVdU44qG2XfOMI2wI0Kx2ttwxUVEC4R5Y7kPuQBxH8eQ1kg8RiqZBGhZXUhZ/U7J5hiJ3IweoRZsvRg1W2DgXwNDn0zbcwstUwl/vb+H0JX55f3ZN1eKwqeWG1q8RN/qThvSH/oSiydoGP9XYJhxnV1YxaLiiFp0mnB0Z9Zro3D6hE5OizH/B2vFpXc354xArlalDPL/kG48j9K1xCNaQ6DEXaBD6J39FD/FNPvco9RxH9bRt6rQTtbzOW44HbK/zGIUWofJ39JjL9dSrWm53SrnBt8lB3LcKrGrXwV2suCkjbn3tKEpg7zXHhQsE4muwhDyLHaItZ4HonxW8kfgcxa7fODj3YTz9W+8xPNjRNNDXPNLOenofE3/++mK8O069T9wQdAtm5X5KPCBtLD6OwMaYAuNXxYGPBUkkKL++tFQvdYCGhjPUR6FSm5wHMzIDU6zYx4m1MdLmIPdU2AIaCghAHiHercrpnKRCsqy91yNUW6zHJahVf9qgekfgdmExTm909Ou+wGviqy6504hn4Y/k0r+YLxziR7HZDmAGHD8TOIhrcZNUz82/IP9LzEuc0A3n7Xwza/bVXtMfIEDScegV+RIdtD6biFgE0CoWs78C4hRTkIyKUhY4zMK7ZN/abQzRJGgoGVQrydsXuTqpAnHPkxXBf4qUwGKLJJpjfqBScCC6CRjhD+6rvGsZNm1TFSTMU2W6RK9bPWlJkVWeuoDbsIkpjXALg4pCbQEU+YHoeRqQ1BiV6o0G+85pfBnQhoWYZUS82lQUJR+8czk9tvfXhcWrAkeujokc0M0D7lefG3H1rcmIf0XmnMdjxMub1q3lc4k1RiGKNjrOqW7pC5OkY0u5Wx153pQ4BcEE34kTtbNRPtpRNjhTBOK5SxPfcYtn/P0Esoze7L05FKwP8PH7tQpleO9GSzSY9B2Dqva7d3jmLavlfwscCJLU/LRCF8nENSOvJLXFohFdxfXBiRB6C5gCydXNGZALdG/SOefLvjqRJ1TBut2t+OSVHYxTZuhyKlYOu3igCTc8dD/5FuwFKQm9pDKx0fN8VPxniI3KiYbynS3x2xCciOHaPeJEyTAfApjR6kWTEdTM+bjOX97J/alBmkpxh5UmpkDibIn/59X8uVzf5NbUONY1rtAL4wzT1Sr55RU16E3DPrRGE/8yWgjB43c17IOATIzQh14w/M8SuPVrkvLmO4polItOpLb70L2WwpImW2yE1kTLb+pdqb3CkXdqzxx0oyjMiJXbRiO3jTo09JsKK69nxHyRz3CN0eKSsrIpWV1maHO+dUs9m9GkjPiIRi34bhcA4na7iU2U5QZW6dyMA9WOs5RzxODbj+fqY4pbKc/weiLsYE8EfrjAp665nKKhXW1yqKmCZyWemY96RSJ7SRDXOjDPuZiKWZKCISi6CWrSgYaqW3WOblzMmckuQRf1rKZr0FWaEfNCe1cct7l7HcpztiD9JhMOqI5lbDwVN0351bDwlx/nQdsExpTQyCcNyiJ0tzWvUZi/49UHPKe5gI8wfxvHAw3XAUS+5P1g0IEiM05lOI5gAFN8fNiv8YyNghcyGEmQ/2Z8ejBOISvMfqtmuxqOQPNEOrLit5HFP1A9OT+yPb666s12Z8mpCj3Ga7O5BVzK5qupsajCyeEFmNkVQnURPtpNpLwQCnkF0Dz8lTxISrKTPFhu58Qi1xX30lq3pUhZtYUr/+nlzcEzWT6RdeLdsMJDD7YiSCOBgg6J51bixF4RSQp3ACwRXyVjhHnFPPAn+5soPPFuk+1zl5j0Ry1PUw8PFbUvFU1NLIAm2IRtpdbe5KgMpzXC8nDLS9NyNo/u/0Im9j5h6jA6xHn7+62Uvb7nKrGs/on0igA2kk4oyDckmd+akx5+QXagMdeQZcyQPgcqyEdPOMOJJRNAWk+EdtCRPtpRNxnXoH3SMKxTNTIjqjYt9EvwmPs+RXwzd1zSGlOKNl2PHJL6EzDYzu4py0ys3iUBL0pDUvDCr14AYnR4xItE/ypystGQXNCy7QNzfCJ3xQkrHGnCZAH3EfgnVJ8NjhbPTjCwSxwD9INq/eWlOBmMm9S4i0/H3ds9tAbBl+R7aKv4Dm76O/L95uwfrQ6L5W1DvyxgLQsArCRuaDbG0sKv+TaGVig6JwAg2OIOpLW1ZfS9iviwuoHeY184oDlmhgrSD123123</property>

<property name="jwt.public.key">ZVVObwNAOtxduxvT9j0ONDRSNNBPNL8NZVVOvtXPNLRNxPu2jN7n60EiVi3bc0wQkgFfPSyGpguYzwgMUrJpFpDT68v/wyq1P76pK5Eg+a5EIl1udK5oR9KjxX8YCkTe3KyAWk+9w2BnNpl/8IZw9ufO0iK28HR/hqv9VUeOSSmMn7wIUnoY1rq2OgoNJlCN/LOEnMr7lXu+BUH9veNJIqb1qS5AbVV5OlRG+OBF+8DbDmcSCXj9posOY3GQx7wPu7wCk+1hoe1g1L62kQU4uYqFqxYFbjRTgWdXL34O48i4dCyASvhroAmNAn143nlH9KsONC3EebuIC2ko5nz0jiFv1ecTvAF5W2Y70aAZ2wgQCGUz90BexrDo5yCmgbxaPdq+rWQKfc8xNzC8H+CZCI9MQjWj+xRFcmxByg/HxC0tNbdJ/EWSCnOVBatkbXWtFrBSXS/5x1ElJ7Sg/IfTCnuKvp+qkKzwbCZwg2XI3x3vblAa4G8pXTg4S0KYr5vyYt30rZ4L7xWJSW1LQBST2cPsZoYnMW+7NHZAlkciFsiWNtZONNR=</property>

<property name="lucene.index.dir">${localHome}/index</property>

<property name="webwork.multipart.saveDir">${localHome}/temp</property>

</properties>

</confluence-configuration>

Atlassian Jira

pg_ctl -D /usr/local/var/postgres start && brew services start postgresql
psql postgres
CREATE ROLE otrs WITH LOGIN PASSWORD 'otrs'; 
ALTER ROLE otrs CREATEDB;
\q
 
#create user and db
psql postgres -U otrs
CREATE DATABASE otrs;
GRANT ALL PRIVILEGES ON DATABASE otrs TO otrs;
 
#show databases
\list 
 
#import dump
psql -h localhost -d otrs -U otrs -f /Users/tokamak/Desktop/IN/MISSIONS/TOTAL/OTRS/Files/psql_otrs_dump.sql
 
#show all db sizes
SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database;
 
 
 
#Create jiradb and user
CREATE USER jiradbuser WITH PASSWORD 'jiraSQL';
CREATE DATABASE jiradb WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
GRANT ALL PRIVILEGES ON DATABASE jiradb TO jiradbuser;

https://www.codementor.io/engineerapart/getting-started-with-postgresql-on-mac-osx-are8jcopb

mardi 1 octobre 2019

JIRA - find a plugin usage in a Workflow (groovy Script Runner + SQL version)

Know where/if a plugin is used. Both have to be adapted to match a specific plugin.

First version gives more data but requires ScriptRunner.

Second version only uses SQL, but is less extensive.

You need to know what you're looking for : com.innovalog.jmwe.jira-misc-workflow-extensions

Script Groovy

Adapted from : https://answers.atlassian.com/questions/205094/how-to-find-all-workflows-which-are-using-postfunctions-of-a-plugin

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.workflow.JiraWorkflow
import com.atlassian.jira.workflow.WorkflowManager
import java.util.regex.Matcher
  
String searchText = 'com.innovalog.jmwe.jira-misc-workflow-extensions'
  
WorkflowManager workflowManager = ComponentAccessor.getWorkflowManager()
Collection<JiraWorkflow> workflows = workflowManager.getWorkflows()
  
String result = "Workflow;Function;Type;Action;Step\r\n<br/>"
  
workflows.each{workflow ->
    def workflowXml = workflow.descriptor.asXML()
    Matcher m = workflowXml =~ /${searchText}/
    if (m.find()) {
        String workflow_name = "${workflow.name}${if(!workflow.isActive()){";(inactive)"} else ';active'}${if(workflow.isDraftWorkflow()){"(draft)"} else ''}"
        def wf = new XmlParser().parseText(workflowXml)
        List<Node> wf_flat = wf.depthFirst()
        wf_flat.each{
            if (it.text() ==~ /.*${searchText}.*/){
                result += "$workflow_name;${getNodeInfo(it,searchText)}\n\n<br/>"
            }
        }
    }
}
result
  
String getNodeInfo(Node n, String search) {
    String nodetext = ''
    nodetext += "${n.text() - search}"
    def p = n.parent()
    while (p) {
        switch (p.name()) {
            case '"post-functions"':
                nodetext += ";post-function "; break
            case 'validators':
                nodetext += ";validator "; break
            case 'conditions':
                nodetext += ";condition "; break
            case 'action':
                nodetext += ";${p.attribute('name')} (${p.attribute('id')})"; break
            case 'step':
                nodetext += ";${p.attribute('name')} (${p.attribute('id')})"; break
            case 'global-actions':
                nodetext += ";GLOBAL "; break
            case 'initial-actions':
                nodetext += ";INITIAL "; break
  
        }
        p = p.parent()
    }
    return nodetext
}

SQL Query

From : https://answers.atlassian.com/questions/93165/find-which-workflows-that-uses-a-specific-validation-plugin

SELECT
  workflowscheme.name,
  workflowschemeentity.scheme,
  issuetype.pname,
  workflowschemeentity.issuetype
FROM
  issuetype,
  workflowschemeentity,
  workflowscheme
WHERE
issuetype.id = workflowschemeentity.issuetype and
workflowscheme.id = workflowschemeentity.scheme and
  workflowschemeentity.workflow in
    (SELECT
        jiraworkflows.workflowname
    FROM
        jiraworkflows
    WHERE
        jiraworkflows.descriptor like '%com.company.plugin%');

Commandes en Vrac