# H2 Plugin

## Author

- Rob Manning

## Introduction

The H2 plugin adds H2-specific functionality to the SQuirreL SQL Client. Read access is required to the following system views in order for this additional functionality to work correctly:

- INFORMATION_SCHEMA.INDEXES
- INFORMATION_SCHEMA.SEQUENCES
- INFORMATION_SCHEMA.TRIGGERS
- INFORMATION_SCHEMA.VIEWS

## New Tabs

Indexes and Views are shown in the object tree and have a "Source" tab which displays the source of the selected object and a "Details" tab which gives H2-specific information about the object. Sequences and Indexes are also shown in the object tree and have a details tab giving H2-specific information about them.

## Index Details Tab

The index details tab can be accessed by navigating the object tree to the INDEX folder beneath any table that has one or multiple indexes. The index(es) will be listed by name under the table folder, and selecting one will place a details tab in the right-hand view of the session window.

The information provided by the details tab for indexes is derived by the following query:

    	SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,
    	NON_UNIQUE,ORDINAL_POSITION,COLUMN_NAME,
    	CARDINALITY,PRIMARY_KEY,INDEX_TYPE_NAME,
    	IS_GENERATED,INDEX_TYPE,ASC_OR_DESC,PAGES,
    	FILTER_CONDITION,REMARKS
    	FROM INFORMATION_SCHEMA.INDEXES
    	WHERE TABLE_SCHEMA = ?
    	AND INDEX_NAME = ?

## Index Source Tab

The index source tab can be accessed by navigating the object tree as above and selecting the "Source" tab which is adjacent to the "Details" tab. An example is shown below:

The information provided by the source tab for indexes is derived by the following query:

    	select
    	'create '||index_type_name||' '||index_name||' ON '||table_name||'('||column_name||')'
    	from INFORMATION_SCHEMA.INDEXES
    	where table_schema = ?
    	and index_name = ?

## Sequence Details Tab

Sequences will appear in the object tree under the SCHEMA folder. The details tab for sequences displays information about the selected sequence according to the system catalog. An example of this is shown in the following picture:

The information in the details tab for a sequence is derived from the following query:

    	SELECT SEQUENCE_CATALOG,SEQUENCE_SCHEMA,
    	CURRENT_VALUE,INCREMENT,IS_GENERATED,REMARKS
    	FROM INFORMATION_SCHEMA.SEQUENCES
    	WHERE SEQUENCE_SCHEMA = ?
    	AND SEQUENCE_NAME = ?

## Trigger Details Tab

The details tab for triggers displays information about the selected trigger according to the system catalog. An example of this is shown in the following picture:

The information in the details tab for a trigger is derived from the following query:

    	SELECT TRIGGER_CATALOG,TRIGGER_SCHEMA,TRIGGER_NAME,
    	TRIGGER_TYPE,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,
    	BEFORE,JAVA_CLASS,QUEUE_SIZE,NO_WAIT,REMARKS,SQL
    	FROM INFORMATION_SCHEMA.TRIGGERS
    	WHERE TABLE_SCHEMA = ?
    	AND TRIGGER_NAME = ?

## View Source Tab

The source tab for views displays the source for a view and can be accessed by navigating to the "VIEW" folder beneath a schema object in the object tree. An example of this is shown in the following picture:

The source code for views is derived from the following query:

    	select view_definition
    	from information_schema.views
    	where table_schema = ?
    	and table_name = ?