<img height="1" width="1" style="display:none;" alt="" src="https://px.ads.linkedin.com/collect/?pid=299788&amp;fmt=gif">

Power Atlassian Server Adminning With Sqlline

Atlassian, Database

Sqlline-post

What the Heck is SQLLine?

Sqlline is a tool created 17+ years ago that allows someone to execute sql queries from any command line prompt. The only requirements are you have a Java JRE, the SQLLine jar file distribution, and the appropriate JDBC driver available in your local path.

Why the Heck is SQLLine?

Everyone, even my Isos coworkers, asks me why I'm so excited about SQLLine. Well, I have a ton of reasons including:

  • No need for native SQL tools: In many (if not most) cases an Atlassian administrator will have full access to each server instance of Jira/Confluence/etc but they do not have direct access to their DB server. Further, most installations do not install native DB tools on server instances for security, maintainability and other considerations.  SQLLine will allow you to do almost any sql query without needing to install these tools.|
  • No need for GUI access to your database: All operations of SQLLine are done via a command prompt, you don't need direct network access from your workstation to query and manipulate your Atlassian data on a RMDBS.
  • Consistent commands across database types:  When querying various databases using native tools, simple operations like getting data meta data, outputting data to file, etc the commands issued can be radically different. With SQLLine, almost all those operations are identical between RMDBS vendors.
  • Objectively good interface and features: Even if you are also the DB administrator for your Atlassian stack and have the native tools at your disposal,  there's still a compelling argument to use SQLLine. I personally prefer using it over Oracle's command line tools for instance.

How Do you Install and Launch SQLLine?

Getting SQLLine up and running is easy.  The below example is for PostgreSQL. However the process will be similar for other supported databases.

Prereqs:

  • Java 8+
  • JDBC driver for your RMDBS

Simple Install for Linux:


prompt$ mkdir sqlline
prompt$ cd sqlline
# Get SQLLine from the Maven repo
prompt$ wget -O sqlline-1.8.0-jar-with-dependencies.jar https://search.maven.org/remotecontent?filepath=sqlline/sqlline/1.8.0/sqlline-1.8.0-jar-with-dependencies.jar
# Get the Postgresql JDBC driver
prompt$ wget -O postgresql-42.2.6.jar https://jdbc.postgresql.org/download/postgresql-42.2.6.jar
 

Start SQLLine


# Start SQLLine
prompt$ java -cp "./*" sqlline.SqlLine
#Connect to Postgresql database
>sqlline> !connect jdbc:postgresql://isos-jira.us-west-1.rds.amazonaws.com:5432/jiradb jirauser db pass
0: jdbc:postgresql://isos-jira>

Now you are ready to start querying!

Awesome Things You Can Do!

Normal SQL Queries!

First and foremost, you can run all the 'famous' SQL queries feature on the Atlassian support forums and documentation. For instance, here's the query that tells you how many users you are consuming for Jira Software:


0: jdbc:postgresql://isos-jira> SELECT count(DISTINCT u.lower_user_name) 
FROM   cwd_user u
       JOIN cwd_membership m
         ON u.id = m.child_id
            AND u.directory_id = m.directory_id
       JOIN licenserolesgroup lrg
         ON Lower(m.parent_name) = Lower(lrg.group_id)
       JOIN cwd_directory d
         ON m.directory_id = d.id
WHERE  d.active = '1'
       AND u.active = '1'
    AND license_role_name = 'jira-software';
+-------+
| count |
+-------+
| 87 |
+-------+
1 row selected (0.004 seconds)

 

Drop All Tables!

Every database vendor has a completely different and almost always complicated way to drop all tables in your database. In fact, most vendors want you to drop the whole DB and recreate. This will not work for most Atlassian admins because 'create/drop' table is one permission Atlassian apps do NOT have. With SQLLine line, the operation is easy and it is the same across all vendors.


0: jdbc:postgresql://isos-jira> !dropall

That's it.. All the tables are gone!


-+-------------+------------+---------------------+-----------+-------------+-------------+---+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME |     COLUMN_NAME     | DATA_TYPE |  TYPE_NAME  | COLUMN_SIZE | B |
+-----------+-------------+------------+---------------------+-----------+-------------+-------------+---+
|           | public      | cwd_user   | id                  | 2         | numeric     | 18          |   |
|           | public      | cwd_user   | directory_id        | 2         | numeric     | 18          |   |
|           | public      | cwd_user   | user_name           | 12        | varchar     | 255         |   |
|           | public      | cwd_user   | lower_user_name     | 12        | varchar     | 255         |   |
|           | public      | cwd_user   | active              | 2         | numeric     | 9           |   |
|           | public      | cwd_user   | created_date        | 93        | timestamptz | 35          |   |
|           | public      | cwd_user   | updated_date        | 93        | timestamptz | 35          |   |
|           | public      | cwd_user   | first_name          | 12        | varchar     | 255         |   |
|           | public      | cwd_user   | lower_first_name    | 12        | varchar     | 255         |   |
|           | public      | cwd_user   | last_name           | 12        | varchar     | 255         |   |
|           | public      | cwd_user   | lower_last_name     | 12        | varchar     | 255         |   |
|           | public      | cwd_user   | display_name        | 12        | varchar     | 255         |   |
|           | public      | cwd_user   | lower_display_name  | 12        | varchar     | 255         |   |
|           | public      | cwd_user   | email_address       | 12        | varchar     | 255         |   |
|           | public      | cwd_user   | lower_email_address | 12        | varchar     | 255         |   |
|           | public      | cwd_user   | credential          | 12        | varchar     | 255         |   |
|           | public      | cwd_user   | deleted_externally  | 2         | numeric     | 9           |   |
|           | public      | cwd_user   | external_id         | 12        | varchar     | 255         |   |
+-----------+-------------+------------+---------------------+-----------+-------------+-------------+---+

 

Record Commands!

Many times when you run a bunch of SQL commands it would be handy if you can explicitly save your session to a file. In SQLLine, that's easy. In this example, we are saving our session to a file called session.cmds.


0: jdbc:postgresql://isos-jira> !script session.cmds
Saving command script to "/home/mmarch/sqlline/session.cmds". Enter "script" with no arguments to stop it.
0: jdbc:postgresql://isos-jira> select count(*) from cwd_user;
+-------+
| count |
+-------+
| 605 |
+-------+
1 row selected (0.002 seconds)
0: jdbc:postgresql://isos-jira> !script
Script closed. Enter "run /home/mmarch/sqlline/session.cmds" to replay it.

 

Finally, Replay Your Recorded Commands!


0: jdbc:postgresql://isos-jira> !run session.cmds
1/1 select count(*) from cwd_user;
+-------+
| count |
+-------+
| 605 |
+-------+
1 row selected (0.005 seconds)

Managing JIRA at Scale White Paper

TAGS: Atlassian, Database

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Subscribe to Our Newsletter

Recent Blog Posts