---

Themes and Priorities in Database Tooling for the Next Release of NetBeans

Overall focus/theme for the next release

A primary focus for NetBeans 6.5 is PHP support. Another important focus for us is to improve our basic database tooling.

Our user research has shown that the most important areas in an IDE for database users are:

• Text-based SQL query tools
• Schema management and exploration
• Table data creation/manipulation

NetBeans has basic support for the first two, and currently has no real support for the third. Our focus for this release will be to improve support in all these areas. We are going to pay particular attention to what we can do to make it easier to work with SQL inside language editors, particular the PHP editor.

There are other things we would like to do but which are not planned, such as the ability to refactor databases and create migrations ala Ruby. If time permits, nothing is impossible...

Use case

Working with the Database Schema:

UC-S1 - Quick view of table info

Estimated Effort : 1 week SqlYog has a nice feature where there is a tab called "Object Info" that shows the data for the selected object (table, index, view).

Also, in the explorer view, each column shows all the information about it (type, length) without having to bring up a dialog.

UC-S2 - Graphical view of schema (read-only E/R diagram)

Estimated Effort: Possibly done for us, 1 month

Select a connection, select a subset of tables, and generate an E/R diagram. The diagram can not be modified but tables can be added/removed and the layout can be re-arranged.

UC-S2.1 - Export E/R diagram to image

Estimated effort: Not clear, guessing 1 week

UC-S2.2 - Export E/R diagram to static HTML

Estimated effort: 1 week, just embed graphical image

UC-S2.3 - Export E/R diagram to dynamic HTML

Estimated effort: 1 month, but probably won't do it In this use case, the HTML is 'live'. Uses DHTML/AJAX to provide powerful presentation/exploration mechanism for communicating with developers' customers.

Features could include:

• Zoom in/zoom out
• Double-click provides details on an entity or relationship
• "Picture-in-picture" to give context
• Explorer frame that lets you have multiple diagrams you can inspect.
• Ability to modify schema in HTML and then import modifications back into NB

UC-S3 - Generate SQL from database schema

Estimated effort: Assuming we can use DDLUtils, 2 weeks (may be done for us by Egypt team) Open a connection, select a schema, and choose "Generate SQL for schema." Pick target database, and file name, and you're good to go.

UC-S4 - Ability to view any schema in the database

Estimated effort: 1 week

Right now in NetBeans you can only see the schema that the user is logged in to. You can't view the other schemas unless you disconnect and change the schema when you reconnect.

UC-S5 - Quick and easy way to create tables, indexes and views

Estimated effort: 3 weeks You can do this today in NetBeans, but we can make it simpler/easier. SQLYog has some good ideas here.

UC-S6 - Quick and easy way to modify tables, indexes and views

Estimated effort: 6 weeks Make it easy to add, rename, delete, change types of columns, add/remove tables, indexes, add columns to indexes, etc.

The key focus of this feature is enabling this even if the underlying database doesn't support it.

This basically means:

• Create temporary table with new definition based on changes
• Migrate data over to temp table
• Drop old table
• Rename temp table to old table (OR, create new table with same name as old table and migrate data back).

UC-S7 - Build and modify an E/R model (writeable E/R diagram)

Estimated effort: 8 weeks, may get help from Egypt team

The ability to modify an E/R diagram generated from a database, or to create one from scratch. This includes creating new entities, modifying existing entities, adding relationships.

UC-S8 - Build database from an E/R model

Estimated effort: Assuming we can use DDLUtils, 3 weeks

From an E/R diagram, select a connection or create a new connection, select a schema or create a new schema, and then generate a set of database tables from the current E/R diagram.

UC-S9 - Generate SQL from an E/R model

Estimated effort: Assuming we can use DDLUtils, 2 weeks

From an E/R diagram, select a target database (Oracle, Java DB, MySQL, PostgreSQL, etc.), and then generate SQL to create tables for the diagram targetted to that database.

UC-S10 - Database schema change management

Estimated effort: Assuming we can use Liquibase, 8 weeks, lots of design

The user versions a database schema, and enables "track changes." Then at any point they can set a new version of the database schema, and generate the SQL for the differences between those versions.

UC-S11 - Control how much of the schema to pull down

Estimated effort: 2 weeks, not sure if it's worth doing

For schemas with a large number of tables, when opening a connection in DB Explorer, be able to view just a subset of tables, e.g filter by name, or pick a table and get tables that are associated to that table.

UC-S12 - Ability to create and modify PostgreSQL stored procedures and triggers

Estimated effort: 6 weeks We're getting a fair number of requests for this from the survey. It's also important to support PostgreSQL since it is shipping with Solaris.

One implication: this means support for pgplSQL in the query editor when creating a stored procedure.

UC-S13 - Ability to create and modify Oracle stored procedures and triggers

Estimated effort: 6 weeks Another fairly big db vendor :) with procedural language extensions for stored procedures and triggers (PL-SQL).

Querying

UC-Q1 - Auto-complete for tables and columns

Estimated effort: 2 weeks In the SQL editor, auto-complete table names and column names while typing SQL.

UC-Q2 - Support for aliases in SQL Editor

Estimated effort: Requires some design thought, but guessing 3 weeks

In the SQL editor, allow auto-complete to work with aliases for tabe names. Also, automatically fix SQL statement if you change the alias for a table name.

UC-Q3 - Command history tab

Estimated effort: 1 week

In query editor, have an associated tab that shows the command history. This includes not just commands run in the SQL editor, but also commands run "internally" by the Database Explorer. Easily select a command and re-execute.

UC-Q4 - Ability to edit results of a query

Estimated effort: 2 weeks

Execute a query, and then user can choose one of the tables in the query, and be able to edit columns for that table in results.

UC-Q4.4 - Ability to save contents of SQL editor to a file

Estimated effort: I thought we could do this, but 1 week

Simple way to save what you have typed or pasted in SQL editor to a file

UC-Q4.5 - Make query result columns sortable

Estimated effort: 1 week

Double-click on a column and sort the results ascending/descending on that column

UC-Q4.6 - Control maximum number of rows returned from a query

Estimated effort: 1 week

Be able to specify the maximum number of rows that will be returned from a query.

UC-Q5 - In-place query-editing for SQL embedded in code

Differentiator

Estimated Effort: Needs some design work, cooperation with editor team, etc. 8 weeks

In your Java, PHP, or other editor, if typing in SQL, be able to quickly engage auto-completion and other features of SQL query editing in place in your editor. Improve overall app development flow.

UC-Q5.5 - In-place testing of SQL embedded in code

Differentiator

Estimated Effort - Needs design thought, help from editor teams, 6 weeks

In your Java, PHP or other editor, be able to quickly test SQL embedded in the code. Place cursor inside SQL string, choose "Test SQL". This brings up table where you can enter in values for substitution parameters (with defaults provided) or modify the SQL string, and then click Execute and look at the results.

Adjust the SQL as necessary until you get the right results, click Done, and the SQL (modified if needed) is pasted back into your code.

UC-Q6 - Quick schema information while working on query

Estimated effort Make use of E/R diagram functionality, maybe not high priority, 3 weeks

In this use case, a user can hit a key or a choose a menu option and the tables currently used in the query are shown in a separate window/ pane along with the ability to quickly add associated tables as needed.

UC-Q7 - Improved visual query editor

Estimated effort <not clear> 4 weeks

Focus on improving visual query editor. Not clear at this time what improvements need to be made here -- will focus more on this if we determine this is a priority.

UC-Q8 - Integrate visual query editor with query editor

Estimated effort: Needs some thought, how to deal with queries that can not be represented in visual pane, 3 weeks

Make visual query part of regular query editor, as an optional pane in the editor

Working With Data

UC-D1 - Easily add data using a form

Estimated effort: 2 weeks

Provide a simple, spreadsheet-like view to add data to a table.

UC-D2 - Import data from another database

Estimated effort: 2 weeks if both connections are with same database vendor, 6 weeks if different vendor

Select a source and target connection, and import both schema and data from the database. The issue with this is that it becomes a Data Migration Featre, which is a huge rathole.

Probably should just support importing from between different db instances for the same db vendor (e.g. Java DB to Java DB)

UC-D3 - Import data from a CSV file

Estimated effort: 3 weeks

Select a file, map columns to columns in one or more database tables. Auto-create the tables or map to existing tables. into

UC-D4 - Export data to a CSV file

Estimated effort 3 weeks

Select one or more tables, map columns to fields (or use defaults) and generate CSV file

UC-D5 - Export data to XML file

Estimated effort 3 weeks

Select one or more tables and generate XML for the data in those tables, using default mapping of table metadata->XML schema

UC-D6 - Export data as INSERT statements

Estimated effort: 3 weeks

Select a table, or select a table from the results of a query, and generate INSERT statements for the data in the table or results. This is useful for quickly sharing your test data with others on the development team.

Ruby-Specific Features

Ruby developers generally don't work directly with a database. Instead, their view of persistent data is through migrations (when defining the schema) and ActiveRecord (in the code).

A big challenge with developing in Ruby is that in the code you can't tell what methods exist on an ActiveRecord model object because these methods are generated dynamically at runtime based on the columns and associations in a table.

Also, you do not change a table schema directly in Ruby. Instead, you define migrations, and then Ruby applies migrations to the database for you.

There are times when you do need to drop into SQL in Ruby, in which case the standard SQL editing tools are useful.

UC-R1 - Auto-complete for Ruby ActiveRecord model objects

Estimated effort Tor's already done it.

User types myobj., and we introspect the table associated with the object and provide auto-complete that includes the accessors for the column names.

Auto-complete can also include support for associations (e.g. if Persons has many Addresses in the database, then show the person object with a getAddresses method) - N.B. need to check Ruby syntax).

Auto-complete would also show methods added by hand to the model object. We could color-code which methods are from the db and which are hand-added, and also allow the user to toggle to see just derived methods or just hand-added methods.

UC-R2 - Ability to explore ActiveRecord model 'schema'

Estimated effort Maybe we don't own this, Ruby team does, but guessing 4 weeks

The user can selects a connection (or migration?), and then graphically view the model objects that are derived from this, along with their various associations.

Being able to see this alongside their code (e.g two separate panes) would be very useful.