FullSetOfNBUseCases
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 pg__plSQL 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 typesmyobj., 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.
