Skip to main content

My HSQLDB schema inspection story

This is a simple story of my need to inspect the schema of an HSQLDB database for a participar FOREIGN KEY, and the interesting things I had to do to actually inspect it.




I am using an HSQLDB 1.8 database in one of my web applications. The application has been developed using the Play framework, which by default uses JPA and Hibernate.




A few days back, I wanted to inspect the schema which Hibernate had created for one of my model objects. I started the HSQLDB database on my local machine, and then started the database manager with the following command



java -cp ./hsqldb-1.8.0.7.jar org.hsqldb.util.DatabaseManagerSwing


When I tried the view the schema of my table, it showed me the columns and column types on that table, but it did not show me columns were FOREIGN KEYs.
Image and video hosting by TinyPic

Image 1: Table schema as shown by HSQLDB's database manager




I decided to search on StackOverflow and find out how I could view the full schema of the table in question. I got a few hints, and they all pointed to the system tables, so I decided to turn on the "show system tables" option from HSQLDB's view menu.




The first table that caught my eye was information_schema.system_tables, so I fired the query:



select * from information_schema.system_tables;



This gave me all the system as well as application tables, but did not give me the detail I was looking for, which was the FOREIGN KEY contstraints on the 'USERREGISTRATIONDATE' table.




The next table that caught my eye was information_schema.system_table_constraints, so I fired the following query:


select * from information_schema.system_table_constraints where table_name = 'USERREGISTRATIONDATE';



This helped a bit further. It told me that the USERREGISTRATIONDATE table had a FOREIGN KEY and the constraint name was 'FK98DCB61247140EFE'.
Image and video hosting by TinyPic
Image 2: Result from table INFORMATION_SCHEMA.SYSTEM_TABLE_CONSTRAINTS




Nice, but it still did not tell me which column the constraint refered to.




The next table I came across was information_schema.system_crossreference. This seemed to have a column called 'FK_NAME'. Good, I fired the query:


select * from information_schema.system_crossreference where FK_NAME='FK98DCB61247140EFE';



Here is what this table showed me...


Image and video hosting by TinyPic

Image 3: Result from table INFORMATION_SCHEMA.SYSTEM_CROSSREFEREBCE




Awesome, this time it showed me the name of the the FOREIGN KEY 'FK98DCB61247140EFE' references the 'ID' column of table 'USER'



I finally had my answer, but before ending this post, let me mention just one more thing. I realized that I did not need the information_schema.system_table_constraints table at all. I only needed the information_schema.crossreference table with the following query:



select PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_NAME, FKCOLUMN_NAME
from information_schema.system_crossreference
where FKTABLE_NAME='USERREGISTRATIONDATE' and FKCOLUMN_NAME='USER_ID';



Image and video hosting by TinyPic
Image 4: Finally the query that clearly showed me the FOREIGN KEY




Now all this should not have been so time consuimng. The GUI tool should have given me this information right away.

Comments

Popular posts from this blog

Planning a User Guide - Part 3/5 - Co-ordinate the Team

Photo by  Helloquence  on  Unsplash This is the third post in a series of five posts on how to plan a user guide. In the first post , I wrote about how to conduct an audience analysis and the second post discussed how to define the overall scope of the manual. Once the overall scope of the user guide is defined, the next step is to coordinate the team that will work on creating the manual. A typical team will consist of the following roles. Many of these roles will be fulfilled by freelancers since they are one-off or intermittent work engagements. At the end of the article, I have provided a list of websites where you can find good freelancers. Creative Artist You'll need to work with a creative artist to design the cover page and any other images for the user guide. Most small to mid-sized companies don't have a dedicated creative artist on their rolls. But that's not a problem. There are several freelancing websites where you can work with great creative ar

Inheritance vs. composition depending on how much is same and how much differs

I am reading the excellent Django book right now. In the 4th chapter on Django templates , there is an example of includes and inheritance in Django templates. Without going into details about Django templates, the include is very similar to composition where we can include the text of another template for evaluation. Inheritance in Django templates works in a way similar to object inheritance. Django templates can specify certain blocks which can be redefined in subtemplates. The subtemplates use the rest of the parent template as is. Now we have all learned that inheritance is used when we have a is-a relationship between classes, and composition is used when we have a contains-a relationship. This is absolutely right, but while reading about Django templates, I just realized another pattern in these relationships. This is really simple and perhaps many of you may have already have had this insight... We use inheritance when we want to allow reuse of the bulk of one object in other

Running your own one person company

Recently there was a post on PuneTech on mom's re-entering the IT work force after a break. Two of the biggest concerns mentioned were : Coping with vast advances (changes) in the IT landscape Balancing work and family responsibilities Since I have been running a one person company for a good amount of time, I suggested that as an option. In this post I will discuss various aspects of running a one person company. Advantages: You have full control of your time. You can choose to spend as much or as little time as you would like. There is also a good chance that you will be able to decide when you want to spend that time. You get to work on something that you enjoy doing. Tremendous work satisfaction. You have the option of working from home. Disadvantages: It can take a little while for the work to get set, so you may not be able to see revenues for some time. It takes a huge amount of discipline to work without a boss, and without deadlines. You will not get the benefits (insuranc