Database development, scripting and navigation tool (for Oracle)

This product aims to add extensive database development and maintenance capabilities to the IntelliJ IDEA development environment. Along with a qualified and IDE-compliant SQL editor, it provides database connection management, script execution support, database objects browsing, data and code editor, support for database compiler operations, as well as various navigation capabilities between all its components.

The tool is currently only supporting Oracle databases, and has been tested only on MS Windows environments.

Features overview

Script editor

Script editor providing advanced editing capabilities
  • SQL and PL/SQL token highlighting (configurable in the "Colors & Fonts" section)
  • Code formatting, folding and bracket matching support
  • Qualified code completion, suggesting only suitable variants for the scope and cursor position
  • Two configurable levels of code completion, supporting Basic and SmartType completion
  • Error highlighting for incomplete statements or invalid references to database entities
  • Basic structure projection in the "Structure" tool window, displaying the list of executable statements

Database connection management

  • Flexible connection administration - connections are configurable on project or module scope
  • Connection file mapping - database script files can be associated to certain database connection for providing advanced editing capabilities like code completion, validation and navigation
  • Connection pooling - support for concurrent database access and for dedicated connectivity

Statement execution engine

  • Statement execution trigger at editor gutter
  • Navigate to result feature - sticky linkage between statement and result window, providing back and forth navigation even if the file is being edited
  • Execution messages tool window (displaying execution errors or confirmations)
  • Tabular result panels, for statement executions with data result

Database object browser

Tool window displaying available connections and their data models
  • Tree-wise visualization of available database connections and hierarchical objects structure
  • Navigate to entity feature (clicking on a resolved identifier in the editor will trigger navigation to respective database entity in the browser)
  • Navigation capability between linked entities (e.g. Column to Constraints)
  • Speed search feature, for locating objects by name

Data editor

Tabular dataset editor for manipulating data in tables and editable views
  • Complex cell editors for several data types (data choosers, calendar, large text editor)
  • Data sorting and filtering capabilities
  • Gutter highlighting - inserted, changed or removed entries are emphasized in the editor gutter
  • Input validation and error highlighting - erroneous inputs are leniently notified (light error pop-up)
  • Structure projection in the default IDE "Structure" tool window

Database code editor

Highlighted code editor for program-objects (e.g. functions)
  • Language highlighting for SQL and PL/SQL (configurable in the "Colors & Fonts" section)
  • Show changes facility - visualization of local changes and changes against database version
  • Basic team coding support - prevent blind overwriting in case of concurrent editing
  • DDL file mapping - locating and aggregating project ddl-files with the database objects being edited
  • Compiler support - database compiler messages are displayed in the messaging box of the execution engine, providing "navigate to code" capability

Current critical limitations

(as of 20th of November 2007)
  • SQL language definition is only to 30% completed (select and alter statements)
  • Browser tree is not able to refresh (e.g. if objects added, removed or changed)
  • Data editor does not provide support for large objects (e.g. BLOB, CLOB)
  • PL/SQL editors do not provide advanced editing capabilities besides of syntax highlighting (no code completion, folding, navigation...)
  • "Execute as script" from editor is not supported
  • Data editor does not provide data filtering capabilities
  • Overall limitations of tool configurability

On the pipe

  • PL/SQL parser (for advanced editing / navigation)
  • Creation and refactoring wizards for database objects
  • "Find usages" feature for database objects
  • Execution engine for program-objects (e.g. procedures, functions)
  • Usability enhancements for data editor
  • Database module - IDE project module for organizing database files (sql scripts, ddl files)

Why only Oracle?

Even if started with the intention of generically supporting all known databases, this tool ended up supporting only Oracle. The default JDBC layer is too generic to cover each database particularity, which made necessary implementing a custom data dictionary layer for supporting the database operations this tool is supposed to offer. This custom layer is kept conceptually very generic and implemented for now only for Oracle, but can be theoretically implemented for any other database. Nevertheless, the support for other databases will have to wait until this custom API reaches a stable and mature state.