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

Unknown said…
Trắng da có nhiều phương pháp , nhưng hầu hết mọi người điều sử dụng các loại thuốc trắng da nhưng đặt ra nhiều câu hỏi quanh các loại thuốc này như thuốc uống trắng da an toàn , thuốc trắng da ivory caps có hại không , thuốc trắng da ivory caps có tốt không ? Đó là một vài câu hỏi liên quan đến thuốc làm trăng da ivory caps , cùng tìm hiểu nhé . Ngoài trắng da thì my pham nhat ban sakura còn giúp da giữ được nét tự nhiên và chống lão hóa vì dùng kem chong lao hoa sakura , ngoài làm trắng da thì còn là kem chống nắng tốt và giá cả thì cũng là vấn đề vì vậy có nhiều câu hỏi như kem chống nắng neutrogena có tốt không ,.. Có nhiều loại nên có nhiều giá khác nhau . Nếu gót chân bị nứt nẻ thì nên dùng kem kem trị nứt gót chân neutrogena foot cream sẽ giúp bạn có được bàn chân đẹp như ý.
Danh Ngọc said…
Thoái hóa cột sống là bệnh rất thường gặp đặc biệt là ở người già , vậy cách chữa benh thoai hoa cot song như thế nào an toàn và hiệu quả , cùng chờ xem nhé. Collagen là sản phẩm giúp làn da trắng đep và trẻ hóa , chống lại sự già cỗi ngày càng tăng của làn da , có nhiều loại collagen tốt hiện nay như super collagen + c , collagen shiseido enriched và sản phẩm collagen nhat ban cũng không phải ngoại lệ. Ngoài ra còn có các loại collagen khác như fish collagen . Có nhiều dạng collagen như nước , bột , vien uong collagen ,.. Khi dùng có nhiều câu hỏi đặt ra như collagen neocell có tốt không ? uống collagen shiseido có tốt không ? Tất cả những câu hỏi này sẽ được trả lời sau đây.

Popular posts from this blog

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 (insuran

Testing Groovy domain classes

If you are trying to test Grails domain class constraints by putting your unit test cases in the 'test/unit' directory, then your tests will fail because the domain objects will not have the 'valdate' method. This can be resolved in two ways: Place the test cases inside test/integration (which will slow things down) Use the method 'mockForConstraintsTests(Trail)' to create mock method in your domain class and continue writing your test cases in 'test/unit' What follows is some example code around this finding. I am working on a Groovy on Grails project for a website to help programmers keep up and refresh their skills. I started with some domain classes and then moved on to write some unit tests. When we create a Grails project using grails create-app , it creates several directories, one of which is a directory called 'test' for holding unit tests. This directory contains two directories, 'unit', and 'integration' for uni

Some thoughts on redesigning education

Some time back I read a blog post on redesigning education. It asked some very good questions. Stuff which I had been thinking of myself. I left my thoughts on the blog, but I would also like to start a conversation around these ideas with those who read this blog as well. I would like to know what other people think of the issue of redesigning (college) education. I have often thought about how college education can be improved. To answer this question, we first have to ask a very basic question. What is the purpose of education? To me, we need education for 3 things: To learn more about the world around us To lead positive constructive lives To earn a good living / fulfill our ambitions I think education has to a large extent evolved to fulfill #3 (with a bias towards earning a comfortable living). The semester system, along with multiple choice tests, and grading, has made our education system into an assembly line. Students are pushed into the assembly line, given classes, admini