How to look into the HSQLDB?

[Jayashree Shetty]

I have installed the atlassian JDK. Now i want to look into all the projects that i createdby looking into the DB. Since i am using HSQLDB and also i have atlassian SDK installed, how can i see the tables list, their columns in the DB? I did refer https://confluence.atlassian.com/display/JIRA/Running+SQL+commands+in+a+HSQL+databasebut i am unable to find database subdirectory if i go by the tutorial link. I think since i installed SDK version of it this could be the reason. But can anyone help me to know is it still possible to look into the HSQLDB columns,tables if we have atlassian SDK?

[Nic Brough -Adaptavist- · Accepted solution]

You need to find the database.log file - look in your plugin directory for target/jira/home

[Jayashree Shetty]

Yes got it fixed. I wasnt referring to the locarion of the plugin which i was developing instead was looking into jira home db

[Alexej Geldt]

i was unable to find that file. this is the content of target/jira/home for a JIRA 5.2.11 plugin made with SDK drwxrwxr-x 3 alexej alexej 4096 Jan 16 17:55 caches drwxrwxr-x 3 alexej alexej 4096 Jan 16 17:55 data -rw-rw-r-- 1 alexej alexej 411 Mai 28 2014 database.properties -rw-rw-r-- 1 alexej alexej 3859074 Jan 16 17:55 database.script -rw-rw-r-- 1 alexej alexej 325 Jan 16 17:55 dbconfig.xml drwxrwxr-x 2 alexej alexej 4096 Jan 16 17:55 export drwxrwxr-x 4 alexej alexej 4096 Jan 16 17:55 import drwxrwxr-x 2 alexej alexej 4096 Jan 16 17:55 log drwxrwxr-x 2 alexej alexej 4096 Jan 16 17:55 logos drwxrwxr-x 2 alexej alexej 4096 Jan 16 17:55 monitor drwxrwxr-x 5 alexej alexej 4096 Jan 16 17:55 plugins

[Alexej Geldt]

this answer is not complete. Yes, it is clear how to start the hsqldb manager. But it is not clear where is the database. I've looken in every folder of a JIRA plugin and could not locate the database file itself.

[Alexej Geldt]

fixed: correct file name for jira is database.script and not database.log as claimed in the answer.

[Adrian Moerchen]

My preferred tool is

• http://www.dbvis.com

But you may try

• http://squirrel-sql.sourceforge.net

too.

[Onkar Ahire]

Hi Jayashree,

http://sourceforge.net/projects/hsqldbmanager/

and

http://www.hsqldb.org/repos/org/hsqldb/hsqldb/2.2.9/

tool is helpful.

Cheers

Onkar Ahire

[Jayashree Shetty]

Thanks Onkar. I could get the HSQLdb manager as i listed in the comment section above. But i didnt get any tables in it. When i run the jira i can see some projects, but when i check in the tables list of the db manager there's nothing in it. why is it this way?

I followed this tutotial https://confluence.atlassian.com/display/JIRA/Running+SQL+commands+in+a+HSQL+database

Command : The below shows the command at the workign directory command prompt

D:\MY WORK\View Issue Plugin\tutorial-jira-add-content-to-view-issue-screen\target\jira\webapp\WEB-INF\lib> java -cp hsqldb-1.8.0.5.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:D:/MY WORK/View Issue Plugin/tutorial-jira-add-content-to-view-issue-screen/target/jira/home/database/jiradb

[Nic Brough -Adaptavist-]

If this is returning no tables, then you're looking in the wrong place - you've pointed it at an empty hsql database.

[Onkar Ahire]

Nic is right Jayashree, Please check the place properly.

Cheers,

[Alexej Geldt]

what is the correct place? For me it is definetely not /target/home/jira. There is no database, just a database script.

[Jayashree Shetty]

In my scenario i have installed the atlassian jdk in D: drive. So this was the path D:\JIRA\amps-standalone\target\jira\webapp\WEB-INF\lib where my hsqldb-1.8.0.5 JAR FILE was present. Now in cmd prompt navigate to the this path D:\JIRA\amps-standalone\target\jira\webapp\WEB-INF\lib . Run this command java -cp hsqldb-1.8.0.5.jar org.hsqldb.util.DatabaseManager. You should be able to view the HSQL database. Default user id is sa and password is blank.