Extending the Navigator

Now that Sql Dev 1.1 is out, we can start showing some of the new way to extend it. The new thing I'll talk about is the new navigator. It's been re-written from scratch. This new one is mostly based all in XML much like lots of areas in Sql Dev. The schema will be published later but here's a quick peek at what can be done.

This example will add a node to the connection under Oracle connections only name "Custom Table." I've updated the skeleton extension mentioned before to include this example. There's 1 changed file and 1 new file.

First we'll create the file which has the sql and tree information to be added. This is located here. When you look at this file there's somethings which will look quite familiar like the <query> tag. This is the same tag used by the reports. The parts that add this to the tree for Oracle connections is

<objectType connType="Oracle" id="TABLE" weight="0.0">

It's pretty simple but let's break this down

connType="Oracle" - If you connected to one of the other databases supported this element of the tree would not show.

id="TABLE" - This will key all the editors for table to be used when clicked on. Also things like Edit/Create dialogs and context menus weight="0.0" - This is the sorting order. To make my new node show first , I made it 0.0.

The next 2 tags are fairly obvious

<icon RSKEY="/oracle/dbtools/raptor/images/table_folder.png"/> - This is where to get the icon to show for the folder in the tree.

<label RSKEY="Custom Tables"/> - This is the name which will show in the tree.

Second the file to be edited is the extension.xml. The change should be pretty easy to pick out from the diff. What this is doing is registering the new table.xml file so that Sql Dev will load it. In this case it's inside the package sample.

<sqldev-navigator-hook xmlns="http://xmlns.oracle.com/sqldeveloper/sqldev-navigator">

<descriptor>/sample/table.xml</descriptor>

</sqldev-navigator-hook>

Here's the results:

and expanded:

This should allow anyone to make an extension would could have custom queries for the same base objects we have exposed in Sql Dev. This along with the other posts on this blog now show

1) how to add to the tree

2) how to add editors to what is clicked in the tree

3) how to add context menus to the nodes in the tree

There's a lot more that can be done in the navigator and we'll go into it in more detail later like how to add children , filter , ....

Getting started with an Extension

Sue had blogged on how to get an extension started in jdev in 10 steps. In the presentation at OOW, I showed how little code it could take to start an extension and said we'd get a skeleton out of how to start one yourself. Well here's the Skeleton and here's a zip of it. With this skeleton, there's 3 steps to getting started with an extension.

Step 1: Download the zip.

Step 2: Got to [unziped location]/etc and update the contents of the build.properties. Here's the content of the file and the only line that has to be updated is the sdev.home which points to your install of SQL Developer 1.1

build.dir=built

sdev.home=/Users/klrice/Documents/workspace/raptor_trunk/ide

extension.id=sample.skeleton

extension.name=Skeleton Sample

extension.author=Kris Rice

extension.author.url=http://krisrice.blogspot.com

extension.descr=A Skeleton of an extension

update.url=http://esdev.sf.net

Step 3: Run ant deploy

Well it's done and deployed to your local copy of sqldeveloper. Wasn't that easy and painless? :-) Take a look at the Help->About and the Extensions tab will show the name from Step2.

This added 2 things. First an ls command when a script is run ( F5 ). Second a Reconnect on the connection. Here's a screen capture of both.

In case you didn't look in the source of the extension, it adds 2 things. First just like in the OOW presentation I mentioned there's a new hook in the scripting engine which allows custom command to be added. In this case we add ls as a command here. This extends the abstract class CommandListener which has a few methods:

public abstract class CommandListener {

    public abstract boolean handleEvent(Connection conn, ScriptRunnerContext ctx, SQLCommand cmd);

    public abstract void beginEvent(Connection conn, ScriptRunnerContext ctx, SQLCommand cmd);

    public abstract void endEvent(Connection conn, ScriptRunnerContext ctx, SQLCommand cmd);

    public void beginScript(Connection conn, ScriptRunnerContext ctx, SQLCommand[] cmd){
    }

    public  void endScript(Connection conn, ScriptRunnerContext ctx, SQLCommand[] cmd){        
    }
}

}



By implementing and registering a command listener, an extension writer has the ability to add new command and listen in on when commands are run as scripts. Hopefully the names on the methods are pretty simple. The beginScript is before anything is run and endScript is after everything. The other 3 are before each command, after each command and the command itself.

So in this case I only implemented the handleEvent so I could process the ls then finally return a true to say I did something.

public boolean handleEvent(Connection conn, ScriptRunnerContext ctx, SQLCommand cmd) {
   44         ResultSet rs = null;
   45         if (cmd.getSql().equals("ls")) {
....
   68             return true;
   69         }
   70         return false;
   71     }

The second extension for the Reconnect has been explained here and here. What's new in this example is that the xml references a java class: className="sample.ReconnectAction" . This class extends another abstract here :

public abstract class AbstractMenuAction { 
   public abstract void launch(); 
   public abstract void setArgs(String args); 
} 

In this launch, all we did was get a handle to the connection which will cause the framework to reconnect if the connection was dropped and put a status out to the toolbar.

  // get the connection 
  // this will force a reconnect
  getDBObject().getDatabase().getConnection();
  // status basr entry
  Ide.getStatusBar().setText("Reconnected.");

Now that everyone knows how to get started with extension, we should see lots of cool stuff. When you do create an extension drop Sue or myself a note and we'll add it to the list of known extensions.

As some people may know I have a project on sourceforge for a couple examples on what could be created in an extension. The examples I had out there for Excel via Apache's POI project and support for XMLType support are not that useful anymore since the base sqldeveloper 1.1 now has both of those in it. So, I'm in the process of getting a couple other examples out there which will be roughly based on the presentation from OOW.

The first extension I've done is pretty simple but makes the tool look much nicer on OSX by adding Quaqua as a choice for the Look-and-feel. Non-OSX users can also install this extension and get the UI. Also only on OSX, it adds some support for Growl when a connection is opened or when a script is run. The sources for these are in the esdev project in subversion .

To add these in just do a check-for-update and add http://esdev.sourceforge.net/center.xml as a new update center.

For those on OSX this screen with the blocky buttons probably looks quite normal.

After loading the extension, go into the preferences and there's a Look and Feel Combo box. Choose Quaqua and restart.


With Ququa loaded the screen looks much nicer.


The same could be done to load some of the other LAFs out there like napkin mentioned for jdev by Shay.

XML Extension Points ( Context Menus )

To follow up from last week on how to share reports. The same holds true for context menus. The explanation here about how to create the xml to create a menu action is the same. The method to add context menu is the same as reports. Although these menu actions are not (yet) on the exchange, the preferences can still point to any url, so the shared file could reside on a web server.

Also like with the reports there's also an api call to register context menus:

XMLBasedObjectAction.registerContextMenus(URL);

Even though the explaintion is the same and the way to add them is just like reports there are some new things in this area. The first and probably most useful is we have an XML Schema for what these context menus dialogs.xsd. I'll put up the xml schema for the other extension points and I'll be adding annotations to the schema but most of the things in it should be fairly easy to follow.

There are a few areas in this which changed for example now the prompts can be required and have validators for things like checking what the user has entered against the database charset. To make a prompt and have it checked the xml would look something like this:

<prompt required="true" validator="charset">

<label>Table Name</label> </prompt>

These validators must implement a very simple interface which is :

oracle.dbtools.raptor.controls.validators.IValidator

public boolean isValid(String conn,Object o);

public String getMessage();

Then the validator simply need be registered with a call to

ValidatorRegistry.registerValidator(name,class);

Currently the only other one shipping is "numeric" which checks the input to ensure it's a number.

XML Extension Points ( Reports )

Hopefully, some people came to the presentation at openworld. In the presentation I showed how with just XML and "a couple" lines of java the tool can be extended. After getting settled in and the first EA of sqldev out, I got the "couple" lines out so it's now really simple to have things like shared reports, editors, navigator extensions, context menu actions.

I'll do a blog on each of these options. This preference pane now takes the place of some of the command line flag which were being used in 1.0 and adds some new ones.

Now I'm sure everyone knows about the SQL Developer Exchange since Sue mentioned it before. So now let's add the reports everyone has defined in the exchange. In the exchange if you navigate to the My Folders page there's a download link under the list of folder.

Now save the link which the Download leads to and we'll need that in the next step which is to tell SQL Developer where to get the reports. In the preferences there's a Database->User Defined Extensions. After clicking the add row button there's a drop list of type to pick from, for this we'll choose reports. Then just put in the url we copied into the Location. This location can be any url or file location. For example /Users/klrice/reports.xml or on windows Z:\myshared\folder\reports.xml

The preference doesn't yet warn that a restart is needed but it will before it gets stamped production. However, now that you know it needs a restart. Restart SQL Developer. Once it's restarted take a look at the Reports and you should see something that looks quite like the structure from the exchange under shared reports.

Now that my report is listed I can run it and see that I can save some space by issuing the command listed.

While the exchange was used in this example. The same could apply to simply exporting some reports created and hosting the resulting xml file internally or on the public net. For those who will look and I know there's some, I'll explain how the xml in the reports changed in a later blog.

Now the last thing to mention on this is for people who are writing extension there's a java call to register reports also and it's about as easy:

ReportAddin.registerReport(URL);

Simple RSS via mod_plsql

If anyone hasn't heard there's a website to submit various things to share with SQL Developer users and the development team. This site was built using APEX. The RSS feed is just plain plsql. Here's how simple can be to get an rss feed from the database.

Here's all I did for the feed of the exchange for the tip that have been submitted.

create or replace procedure RECENT_TIPS_RSS
is
begin
rss(p_title =>'SQL Developer Recent Tips and Tricks',
    p_base_link =>'http://www.oracle.com/technology/products/database/sql_developer/index.html',
    p_base_descr =>'This is a list of recently published Tips and Tricks for Oracle SQL Developer',
    p_item_link =>'http://htmldb.oracle.com/pls/otn/f?p=42626:54:::::P54_ID:',
    p_sql =>'select id,name,created_by,created_on,description from (select * from applications where status=''APPROVED''    order by created_on desc ) where rownum < 10 ');
end;

This uses a simple procedure I made which takes in the sql to be run. Most of the parameters are obvious however the item link and the sequence of the select list are the 2 that need to be explained.

The select list is expected to be in this order id , title , author , date , optionally a link to the item. Then the rest of the columns are all dumped into the description. The link is optional because in this tip example there's a p_item_link passed in. When this is passed in the link to the item is this link with the id concatted to the end. So in this feed an item link would be http://htmldb.oracle.com/pls/otn/f?p=42626:54:::::P54_ID:221 where the 221 is the id. If the p_item_link is not passed in, then column 5 is expected to be the link. This should be obvious that this is used when the links go to different urls.

Hopefully, someone find this useful. I also have this as an atom feed if people want that.

procedure rss(p_title varchar2,
              p_base_link varchar2,
              p_base_descr varchar2,
              p_item_link varchar2,
              p_sql varchar2,
              p_debug varchar2 default 'N')
AS
  l_descTbl dbms_sql.desc_tab2;
  l_colCnt NUMBER;
  l_cursor NUMBER;
  l_id varchar2(4000);
  l_title varchar2(4000);
  l_author varchar2(4000);
  l_link varchar2(4000);
  l_date date;
  l_ret number;
  l_col_val varchar2(32767);
  l_start number := 5;
  l_stub varchar2(2000) := ' ';
  i number;
  k number := 0;

  // format an oracle date
  function formatDate(l_d date) return varchar2 as
     begin
        return to_char(l_d,'RRRR-MM-DD')||'T'||to_char(l_d,'HH24:MI:SS')||'Z';
     end;
 begin
   owa_util.mime_header('text/xml', FALSE );
   htp.p('Cache-Control: no-cache');
   htp.p('Pragma: no-cache');
   owa_util.http_header_close;
   htp.p('<?xml version="1.0"?>');
   htp.p('<rss version="2.0">');

-- debug print the sql as a comment
   if ( p_debug = 'Y' ) then
      htp.p('<!-- '||p_sql ||'-->');
   end if;

-- print the header info
   htp.p('<channel>');
   htp.p('<title>'||p_title||'</title>');
   htp.p('<link>'||p_base_link||'</link>');
   htp.p('<description>'||p_base_descr||'</description>');
   htp.p('<language>enus</language>');
   htp.p('<lastBuildDate>Tue, 10 Jun 2003 09:41:01 GMT</lastBuildDate>');
   htp.p('<docs>http://blogs.law.harvard.edu/tech/rss</docs>');
   htp.p('<generator>mod_plsql rss</generator>');
   htp.p('<managingEditor>kris.rice@oracle.com</managingEditor>');
   htp.p('<webMaster>kris.rice@oracle.com</webMaster>');

-- open the cursor and start looping
   l_cursor := dbms_sql.open_cursor;
   dbms_sql.parse(l_cursor, p_sql||l_stub, dbms_sql.native);
   dbms_sql.describe_columns2( l_cursor,l_colCnt,l_descTbl );
   l_ret := dbms_sql.execute(l_cursor);
   -- define the columns
   for i in 1.. l_colcnt loop
      if ( i != 4 ) then
         dbms_sql.define_column(l_cursor, i, l_col_val, 32767 );
      else
         dbms_sql.define_column(l_cursor, i, l_date);
      end if;
   end loop;
-- 
-- Main cursor loop
-- 
loop
   if dbms_sql.fetch_rows(l_cursor) = 0 then
      exit;
   end if;
--
-- Expected sequence of columns is id , title , author , date , optionally a link to the item
--
   dbms_sql.column_value(l_cursor, 1, l_id);
   dbms_sql.column_value(l_cursor, 2, l_title);
   dbms_sql.column_value(l_cursor, 3, l_author);
   dbms_sql.column_value(l_cursor, 4, l_date);
   htp.p('<item>');
   htp.p('<title><![CDATA['||l_title||']]> </title>');
   htp.p('<author><![CDATA['||l_author||']]> </author>');
   htp.p('<pubDate><![CDATA['||formatDate(l_date)||']]> </pubDate>');

--
-- If p_item_link was set for the item print it and tack on the id to the end
-- Otherwise print colum 5 as the link
-- 
   if ( p_item_link is not null ) then
      htp.p('<link>'||p_item_link||l_id||'</link>');
   else
      dbms_sql.column_value(l_cursor, 5, l_link);
      htp.p('<link>'||l_link||'</link>');
      l_start := l_start +1;
   end if;

--
-- Now loop the rest of the columns in the select list and dump them into the description
--
   htp.p('<description><![CDATA[');
   for i in l_start.. l_colcnt loop
      -- Dump the cols into the description
      dbms_sql.column_value(l_cursor, i, l_col_val);
      htp.p(lower(l_desctbl(i).col_name) ||':'||l_col_val||'<br />'); 
   end loop; -- col loop


   htp.p(']]> </description>');
   htp.p('<guid>');
   htp.p(l_id);
   htp.p('</guid>');
   htp.p('</item>');
   end loop; -- cursor loop

   htp.p('</channel>');
   htp.p('</rss>');
   exception when others then
      htp.p(SQLERRM);
end rss;

New Datatype support and how to add more

I figured it's about time I blog something again. Sue has been covering a bunch of 1.1 features that we've been doing. I'll cover a couple more and how they are implemented and what that means to people writing custom extension.

The general idea is to make datatypes extensible such that anyone can write a presentation for one with very little code. There's a new interface which is registered by the class you want to render such as this first example which is for XMLType.

The call to register would look like this.

CellEditingFactory.registerCellRenderer(OPAQUE.class, new XMLTypeEditor());

Then the class XMLTypeEditor simply implements the new interface which is pretty leaves things open enough to do anything. There's 2 methods in it. The first getComponent this will put in the place of the cell in the grid what ever is returned and it override the default. For this example there's a JPanel created which is just the xml with a button. Since the button is part of the component, it can do anything which in this case pops up a modal with more space to view the data.

The second method is called when the first returns null.  The idea behind this is that someone may not need or want to build out a Component to render but simply want to create the text representation of the data.  This will use the text returned and put it into a normal table cell.

public interface ICellEditor {
	public abstract Component getComponent(JTable table, Object value, int row, int column);
	public abstract String getText(JTable table, Object value,int row, int column);
}

Here's the what it takes to handle XMLType

public class XMLTypeEditor implements ICellEditor {



    public Component getComponent(final JTable table, Object value, int row, int column) {
        XMLType xml = null;
        final JPanel pnl = new JPanel(new BorderLayout());
        // convert value to xml for convience
        try {
            if (value instanceof XMLType) {
                xml = (XMLType) value;
            } else if (value instanceof OPAQUE && ((OPAQUE) value).getSQLTypeName().equals("SYS.XMLTYPE")) {
                xml = XMLType.createXML((OPAQUE) value);
            }
        }
        catch (SQLException se) {}
        // must not be interested
        // return null and let someone else render it
        if (xml == null) {
            return null;
        }
        try {
            // create the returned component
            JLabel lbl = new JLabel();
            // set the text
            lbl.setText(xml.getStringVal());
            // set the tooltop
            lbl.setToolTipText(xml.getStringVal());
            // add the label to the center of the panel 
            pnl.add(lbl, BorderLayout.CENTER);
            // create the ... button
            final JButton btn = new JButton(IdeIcons.getIcon(IdeIcons.EDIT_ICON));
            btn.setMargin(new Insets(2, 2, 2, 2));
            // add the button to the panel
            pnl.add(btn, BorderLayout.EAST);

            // make a final so the action listener can access the xml 
            final XMLType xmlForPopup = xml;
            btn.addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent ae) {
                    // create editor pane
                    BasicEditorPane codeArea = EditorFactory.createIdeEditorPane();
                    // Force the editor to highlight with xml
                    codeArea.setDocument(new BasicDocument("foo.xml")); // NORES
                    // Add a line highlight plugin to highlight the caret
                    // position line
                    try {
                        codeArea.setText(xmlForPopup.getStringVal());
                    }
                    catch (SQLException e) {
                        e.printStackTrace();
                    }
                    // add the highlighter
                    codeArea.installPlugin(new LineHighlightPlugin());
                    // non- editable
                    codeArea.setEditable(false);
                    // add the gutter for line numbers
                    LineGutterPlugin sqlGutter = new LineGutterPlugin();
                    codeArea.installPlugin(sqlGutter);
                    // put the editor into a scrollpane
                    JScrollPane sp = new JScrollPane(codeArea, JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED, JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
                    sp.setRowHeaderView(sqlGutter);
                    JPanel popupPanel = new JPanel(new BorderLayout());
                    popupPanel.add(sp, BorderLayout.CENTER);
                    popupPanel.setPreferredSize(new Dimension(200, 200));
                    // helper class to show the panel int a popup
                    UIUtils.showPanelAsDialog(popupPanel, "XML Data", null, JEWTDialog.BUTTON_CLOSE);
                    // grab the focus back to the table so <TAB> keeps working
                    table.requestFocusInWindow();
                }
            });
        }
        catch (SQLException e) {
            // Guess we didn't care if and exception was thrown
            // so return null so the next one can try and render
            return null;
        }
        return pnl;
    }
    public String getText(JTable table, Object value, int row, int column) {
        return null;
    }

Here's another screen shot of an implementation for a cursor

Hopefully this is easy enough so that anyone can create renders for datatype which have not made it yet.

[posted with ecto]

Updated Esdev... finally

I finally got the esdev.sf.net updated. The export to excel is updated to spill to the next worksheet when the first one fills up. It will also now create a sheet which has the sql used to get the data.


To try and minimize the amount of people using command line flag for adding snippets and reports. I've added a preference pane for each to specify files which contain these. The preference panes can handle both files and urls. So a couple examples would be :
/Users/klrice/reports.xml
C:\reports.xml
http://www.jokr.net/reports.xml

The only thing to keep in mind for these panes is items are not removed at runtime. So if something is removed, the only way to see that reflected is to restart the tool. For the next release, I'll make this type of feature in the tools and have it handle removals.

Also there's a preference pane for turning code insight off. So no more command line flags.

I think this center file will work http://umn.dl.sourceforge.net/sourceforge/esdev/center.xml

Sourceforge seems to frown on direct linking to files.

Forgotten tabs part 2

Now that's I know there's some interest in people writing, I'll expand on my previous post and show how to have shared queries, master/detail , 2 queries and the bottom query being text.

When there's sql which has to used on multiple object types like tables and view, the sql can be defined once and shared to simplify the xml file.

<item type="sharedQuery" id="grants">
	    <query>
	       <sql><![CDATA[Select PRIVILEGE, GRANTEE, GRANTABLE, GRANTOR, COLUMN_NAME object_name from all_col_privs where table_schema = :OBJECT_OWNER and TABLE_NAME =  :OBJECT_NAME union all Select PRIVILEGE, GRANTEE, GRANTABLE, GRANTOR, table_NAME object_name from all_tab_privs where table_schema = :OBJECT_OWNER and TABLE_NAME = :OBJECT_NAME]]></sql>
        </query>
  </item>

Since this item's type is sharedQuery it's now available to be used. This is very easily done as such

 <item type="editor" node="TableNode">
    <title><![CDATA[Grants]]></title>
	    <query id="grants" />
 </item>

From here out I'll just use sharedQueries so the xml is easier to read but anywhere there's a query tag it can be a tag that includes the sql.

So now you say you need 2 independent grid of data for a given node. This is easy as well.

 <item type="editor" node="TableNode">
   <title>Dependencies</title>
	<query id="grants" />
	<bottomquery>
	    <title>References</title>
		<query id="someothersql" />
	</bottomquery>  >
 </item>

Ok now 2 grid of stuff is good but now you want master detail. This will re-run the bottom code with the row which is double clicked on in the top grid ( master ). The thing to notice in the following examples is the use of bind variables. The subquery can reference and column in the master as a bind. In this first example there's a column names TABLE_NAME in the first sql statement. The second sql has :TABLE_NAME.

  <item type="editor" node="MViewNode">
    <title><![CDATA[Indexes]]></title>
	      
        <subquery>
           <query>
	        <sql><![CDATA[ SELECT :TABLE_NAME FROM SYS.DUAL]]></sql>
          </query>
        </subquery>
 </item>

Lastly, master detail again but the detail is text like the triggers tab on table. The only difference here is that the subquery has a type of "code". This keys SQL Developer to get the first column of the resulting sql statement and dump it into a text area.

<item type="editor" node="TableNode">
    <title><![CDATA[Triggers]]></title> 
	    <query id="triggerSql" />
        <subquery type="code">
	      <query>
	        <sql><![CDATA[ SELECT DBMS_METADATA.GET_DDL('TRIGGER',:TRIGGER_NAME,:OBJECT_OWNER) SQL FROM SYS.DUAL]]></sql>
          </query>
        </subquery>
 </item>

XML Based right click menus

See what I miss by going on vacation. I just happen to be looking at old blogs and ran across this one. So this one will explain how to add to the right click menu using nothing but xml. Hopefully most of the xml is obvious. As an example, I'll use the right for table to change logging vs. nologging.

<items>
<item type="TABLE">
   	<title>Logging</title>
  	<prompt>
	      <label>Logging:</label>	      <value><![CDATA[STATIC:LOGGING:NOLOGGING]]></value>
	      <default><![CDATA[select logging from all_tables where owner = :OBJECT_OWNER and table_name = :OBJECT_NAME]]></default>
  	</prompt>  	
  	<prompt type="confirm">
	       <label>Change logging status of selected table?</label>
  	</prompt>  	
  	<sql><![CDATA[alter table "#OBJECT_OWNER#"."#OBJECT_NAME#" #0#]]></sql>
  	<help>Change the logging status of a table.  LOGGING will log changes to the redo log file.<br/>
  NOLOGGING will not log changes to the redo log file.</help>
  	<confirmation>
  	       <title>Confirmation</title>
  	       <prompt>Table "#OBJECT_NAME#" set to #0#</prompt>
                </confirmation>
  </item>
  </items>

While there's a lot more xml, hopefully the simplicity of this is not lost. Here's what it looks like in the tool:



Hopefully most of the xml is obvious. Here's a break down of the tags:

Prompts:
prompt - This is a prompt for user input. The user's input can be referenced when submitted as #0# for direct substituition.
label - This is the label of the prompt
value - This has a few choices.
- If omitted , the user will be presented a text field for free-form input
- To have a static list of values STATIC:A:B:B
- To have a select base list <value><![CDATA[select :OBJECT_OWNER from dual ]]></value> ( more on binds later )
default - This can be a single value or if a select SQL Developer will grab the first column of the first row

The next prompt is a confirm, a prompt of this type is just text to the user. There is no input.

Now for the actual code to be executed against the DB. The contents of the sql tag will have #0# ... #N# replaced with the values from the user prompts. Also available are:
#OBJECT_NAME# - name of the object
#OBJECT_TYPE# - type of the object
#OBJECT_OWNER# - owner of the object
#COLUMN_NAME# - name of the column ( if a column was right clicked
#OBJECT_ID# - object_id from all_objects
#USER# - user which is connected to the database

The rest should be easy if your eyes have not glazed over by now the rest should be easy. The help is just what is shown when the help button is clicked.
The confirmation is what is presented to the user once the sql has been executed successfully.

Finally pass the command like -Draptor.user.contextmenu=/path/to/file:/path/to/file2

Now that you've made it this far. You can see how the values entered by the user are passed to the sql to be executed. This mean the code in the sql block has to handle all the inputs and deal with them. If you really want to see this, check out the code generated by Table->Column->Normalize.


----- Updated 5/9------
I should have said to pass the new menus, these are the choices
sqldeveloper -J-Draptor.user.contextmenu=/path/to/file:/path/to/file2
or edit sqldeveloper.conf
AddVMOption -Draptor.user.contextmenu=/path/to/file:/path/to/file2

Forgotten tabs

So, you say we missed adding a partition tab or a MV column stats. I'd say it's an opportunity for you to add it exactly how you'd like it. This can all be done via xml and a command line flag


It's really easy to add tabs to an object when clicked in the tree.

<?xml version="1.0" encoding="UTF-8"?>
<items>
 <item type="editor" node="TableNode"  vertical="true">
    <title><![CDATA[Modifications]]></title>
      <query>
         <sql><![CDATA[select * from ALL_TAB_MODIFICATIONS where  table_owner = :OBJECT_OWNER and table_name = :OBJECT_NAME]]></sql>
        </query>
 </item>
</items>

Hopefully most of this is pretty straight forward. Here's a few thing explained:

First the item tag has a couple attibutes

type="editor"This is required right now and the only value is "editor"

node="TableNode" This is the node which the tab will show up on. See the list below for all supported values.

vertical="true"This will make the report vertical since I know this sql will return only 1 row. It makes it much easier to read. Leave this attribute out or no value and the report will be a normal data grid of the results

Here's the list of valid values for the node attribute:
IndexNode
TriggerNode
PlSqlBaseNode
MViewLogNode
DatabaseLinkNode
RecycledObjectNode
SequenceNode
SynonymNode
MViewNode
ViewNode

The thing that probably stands out is the binds. Here's the list of binds that are supported:
:OBJECT_NAME name of the object
:OBJECT_TYPE type of the object
:OBJECT_OWNER owner of the object
:COLUMN_NAME name of the column ( if a column was right clicked
:OBJECT_ID object_id from all_objects
:USER user which is connected to the database

Now all that's needed is to tell SQL Developer to load the file. This is very easy:

-Draptor.user.editors=/home/klrice/editors.xml

There can only be one file right now. In a future release, I'll get a registerEditors call added as well as the ability to have multiple files passed on the command line just like the snippets and reports have now.

If people find this usefull , well I'll do it anyway. I'll follow this up with how to add master/detail ( index columns ) and top results with bottom code ( triggers ).

SQL Developer Extensions and Eclipse

Want to write an extension to SQL Developer but you only use Eclipse? There's a post on the forum about how to use Eclipse to write an extension to sql developer so I figured I'd put it here so it's not lost into the of the forum.


This is actually quite easy to setup. This can be checked by running the shell script with a -x, so bash -x sqldeveloper. This will show what is being executed

First make a run/debug configuration.

1. Main Tab
   
   • Main Class: oracle.ideimpl.Main
     
   • Check the Include libraries when searching for a main class checkbox
     
   
   
2. Arguments Tab
   
   • The paths in the VM arguments should be adjusted to your specifics. Also if there is a space in the paths they will have to quoted like "C:\Document and Settings\sqldeveloper...."
     
     
   • VM Arguments: -Dide.product=oracle.sqldeveloper -Dide.splash.screen=raptor-splash.gif -Xverify:none -Xmx512M -XX:MaxPermSize=128M -Dice.browser.forcegc=false -Dice.pilots.html4.tileOptThreshold=0 -DEDITOR_J2SE_VERSION=1.5 -DINSIGHT_OMIT_HIDDEN=true -Doracle.xdkjava.compatibility.version=9.0.4 -Dide.debug.controller.stack=false -Dceditor.java.parse.small=300 -Dceditor.java.parse.large=1500 -Dide.extension.cpmissing.nowarn=true -Dcompiler.vmargs=-mx512m -Djbo.debugoutput=silent -Djbo.showdebugwarningbanner=false -Dide.config_pathname=/home/klrice/sqldeveloper/jdev/bin/sqldeveloper.conf -Dide.startingcwd=/home/klrice/sqldeveloper/jdev/bin -Dide.user.dir=/tmp
     
   • Working directory: choose Other . Then enter the path to /jdev/bin
     
   
   
3. Classpath Tab
   
   • Add the following ( all paths are relative to )
     
   • /ide/lib/ide.jar
     
   • /ide/lib/javatools.jar
     
   • /ide/lib/xmlparserv2.jar
     
   • /ide/lib/help4.jar
     
   • /ide/lib/share.jar
     
   • /ide/lib/javax-ide.jar
     
   
   

Now the run configuration is setup and ready to use. You should now be able to run and debug an extension.

Bash completion for Sql*Plus

If most people are like me, you don't like to type something twice. Here's something I wrote a while back to add completion to sqlplus in bash. I had copied it to a new machine to and thought I'd post it in case someone might find it usefull so here it is.


This file will check the ~/.bash_history for previous sqlplus commands as well as complete @.../foo.sql

To try this grab this and drop it into the ~/.bashrc or ~/.bash_profile. If Bash Completion is loaded, it can be dropped into /etc/bash_completion.d/ .


------FILE-------
_sqlplus()
{
local cur
COMPREPLY=()
cur=${COMP_WORDS[COMP_CWORD]}
prev=${COMP_WORDS[COMP_CWORD-1]}

if [ $COMP_CWORD -eq 1 ] && [[ "$cur" == -* ]]; then
# return a list of switched
COMPREPLY=( $( compgen -W '-H -V -C -L -M -R -S' -- $cur ) )
elif [[ "$cur" == "/" ]]; then
# only /nolog is possible
COMPREPLY=( $( compgen -W '/nolog' -- $cur ) )
elif [[ "$prev" == "as" ]]; then
# as sysdba sysoper
COMPREPLY=( $( compgen -W 'sysdba sysoper' -- $cur ) )
elif [[ "$prev" == "-R" ]]; then
# added for completness
COMPREPLY=( $( compgen -W '1 2 3' -- $cur ) )
elif [[ "$cur" =~ "@" ]]; then
# if @
base=${cur:1}
COMPREPLY=( ${COMPREPLY[@]:-} $( compgen -f -P "@" -X "$xspec" -- "$base" ) $( compgen -d -P "@" -- "$base" ) )
elif [[ "$prev" =~ "@" ]]; then
# if @
COMPREPLY=( ${COMPREPLY[@]:-} $( compgen -f -P "@" -X "$xspec" -- "$cur" ) $( compgen -d -P "@" -- "$cur" ) )
elif [[ "$*" =~ "/" ]] ;then
# already has a / assume it's the pass
COMPREPLY=
else
#default
_history
fi
}
complete -F _sqlplus $nospace $filenames sqlplus


_history()
{
local cur
cur=${COMP_WORDS[COMP_CWORD]}
COMPREPLY=( $( compgen -W '$( command grep "^sqlplus" ~/.bash_history ) ' -- $cur ) )
}
------FILE-------

If you notice the history funcion is pretty simple so modifying it to check other files would be very easy

Drillable Reports

Sue blogged about how some reports have drill thru ability to get the underlying object. This rasises the question of how can I do that to my report which lists my most interesting objects?

The answer to this is very simple. Here's an example which is simple query of which object were altered most recently and makes it a drill thru.


This SQL run in the worksheet gives you a list of object.
select owner,
object_type,
object_name,
last_ddl_time
from all_objects ao
where owner <> 'SYS'
order by last_ddl_time desc;

Instead of then finding your way to that object in the tree let's make it drill thru. This is done very easily by adding a few columns.
select owner,
object_type,
object_name,
last_ddl_time ,
owner sdev_link_owner,
object_type sdev_link_type,
object_name sdev_link_name
from all_objects ao
where owner <> 'SYS'
order by last_ddl_time desc;


The fist thing that is obvious will be that these columns will not show in the results. Now when the query is run and a row is double clicked you will be taken to that object. The only catches are it has to be an object which is supported ( basically see the tree ) and you must have access. Before anyone askes there is currently no way to go to tab #X of the object.

I'm sure someone can make better use of this for things like my largest object report or maybe a report on V$DB_OBJECT_CACHE

This can be used in the worksheet or in the sql of a report.

SQL Developer Podcast

A few weeks ago I sat down with Tom Haunert and we recorded a podcast. Now that SQL Developer has gone published Here's a podcast which was done a few weeks ago.

Listed here http://www.oracle.com/technology/syndication/techcasts/index.html

And direct here MP3

1.0 has landed

Ok if the name had stayed raptor the title would have made more sense.

As Brian Duff and Eddie Awad and Andrew Clarke have all stated SQL Developer is finally gone 1.0.

The interest as truely amazed me. The first code checkins were early September. From then we demoed in Chuck and Andy's keynotes as well as did numerous demos in the demo pods stealing time from the migrations and apex pods. In under 6 months, we have made or 1.0 release available with 7 early access releases which started December 27th.

Here's some quick stats on the tool up thru yesterday.

• Over 80,000 Downloads
  
• Over 140 countries ( 72% of countries in the world have downloaded and according to Number of Countries )
  
• 94% Windows , 5% linux and 0.6% Mac OSX
  

Another thing that surprised me was that there were 2 extensions written which we were still in an EA status. First was georaptor followed by Fourth Elepant's Insider for SQL Developer

Here's some of the press coverage

• Officel PR
  
• Info Week
  
• eWeek
  
• Internetnews.com
  
• InfoWorld
  
• Computer World
  

Well now we're on to 1.1 planning but that doesn't mean we'll be silent. I almost have a SQL Developer Studio done which will allow for 3rd parties to register extensions, define reports , snippets and who know what else I can get squeezed in. I'll keep the examples coming as well as any updates I can share.

Enhanced Excel and Save Snippets

I just updated the files out on esdev.sf.net.
Here's the changes in the export to Excel:

• Print column names into a frozen row
  
• Now it can be canceled
  
• Remembers last file
  
• Number are truely number in the cells
  

There's also some new stuff around snippets.


I've added the ability to hightligh some text in the worksheet and save it as a snippet so it can be easily referenced later. It's pretty simple take as you will see


First select the text to be saved in the worksheet.




Now give it a name , category and description.



Now use it.


This will save the file into ~/.snippets.xml

This file is added different than my previous blog on snippets. It uses
SnippetAddin.registerSnippet(SnippetController.snippetFile.toURL());

This call could be used by other extensions to add custom snippets more easily.

There's a similar call to add reports by bunlding an xml compliant file to your jar file
ReportAddin.registerSnippet(someUrlObject);

XMLTypes , Excel and an Example Extension

Since new coding is pretty much ramped down for SQL Developer , I needed a place to test out some features in a way that everyone can benifit. I've created a project on sourceforge, http://sourceforge.net/projects/esdev. The current extension there enables support for readonly XMLTypes and adds export to excel in native excel inthe worksheet thanks to Poi.


I hope this project also shows how easy it is to extend base functionality. The web cvs isn't working yet but once it is, take a look and see what it took to add this.

If you would like to see these examples in action, add the center.xml to your check for update. The center file is http://osdn.dl.sourceforge.net/sourceforge/esdev/center.xml

I'll probably tinker here with trying to intergrate some open source projects into sql developer.

Wonder twin powers activate!

Form of an Application, Form of a debugger..

The standard manner to debug any APEX application is done with wwv_flow.debug which execute when the page is run by a developer in debug. Wouldn't it be nice to have a full stack of the code executing with inspection? Read on to see how to initiate a full debug session in SQL Developer from an APEX based application.


There's some setup involved the form of persmission which have to be granted to the user who owns the code as well as the user who is configured to run APEX.

First grant the ability to connect a debug session to the parse_as in APEX. In this case klrice.
SQL> grant DEBUG CONNECT SESSION to klrice;
Grant succeeded.

Second grant the ability to debug to the user configured to run APEX in mod_plsql. Since I'm using XE as my example it's public.
SQL> grant debug on klrice.ls to public;
Grant succeeded.


Now everything is setup and on to the APEX application. This is a very simple example but should show enough of how to make it more generic.

Here's a screenshot of my page.



There is nothing special in the plsql region except it's a plsql region running a very small procedure name LS which loops and does a HTP.P. This procedure needs to be compiled for debug.

Next there's a checkbox for flagging when to connect for debugging and not. Then the real debugger code is in the Processes. The first is a Before Header to be able to debug anything executed on the page. This process is conditional based ont he value of the P1_DEBUG checkbox as follows:

dbms_debug_jdwp.connect_tcp('localhost',4000);

While localhost is hardcoded something like owa_util.get_cgi_env('REMOTE_ADDR') could be used to make it more generic. Also note the port is hardcoded to 4000 , this is important in a sec.

Since the debugger would now be connected, it's nice to disconnect so that's done like this in the After Footer Process:

dbms_debug_jdwp.disconnect;
:P1_DEBUG := 'N';


I've also reset the value of P1_DEBUG to force the checkbox to be choosen each time.

Now into SQL Developer. Right Click on the connection to the user which is the same as the APEX application and choose Remote Debug



Now to bring it all together. Run the application check the checkbox to start the debugger.

This is what should result in SQL Developer:


I hope this helps in the much enjoyed task of bug fixing.

Forgotten datatypes part 2....

It was just pointed out to me that many people many not know how to get the code for a custom renderer into an extension that SQL Developer will use. So here's the rest of the steps to getting your custom column renderer into SQL Developer.


The first thing which will be needed is an extensio.xml file to describe the extension.


<extension xmlns="http://jcp.org/jsr/198/extension-manifest"
id="net.jokr.columnrenderer"
version="0.0.0.1"
esdk-version="1.0">
<name>Example Custom Column Renderer</name>
<owner>Kris Rice</owner>
<hooks>
<jdeveloper-hook xmlns="http://xmlns.oracle.com/jdeveloper/1013/extension">
<addins>
<addin>CustomColRendererAddin</addin>
</addins>
</jdeveloper-hook>
<feature-hook>
<description>Example Column Display</description>
<optional>true</optional>
</feature-hook>
</hooks>
</extension>


Now to stich it all together, here's the ant build file I used for this example. There's a few properties to setup the location of SQL Developer which will of course have to be changed per install. The extension.xml from above is use in the jar which created. The final step to getting this all to work is simply putting the produced jar file into the /jdev/extensions folder.


<project name="Custom Columns Renderer" default="deploy">
<target name="init">
<property name="sdev.home" value="../raptor/ide/" />
<property name="extension.filename" value="net.jokr.xmlcolumnrenderer.jar" />
<property name="built" value="build" />

<path id="compile.classpath">
<fileset dir="${sdev.home}/ide/lib" includes="*.jar" />
<fileset dir="${sdev.home}/jdbc/lib" includes="*.jar" />
<fileset dir="${sdev.home}/jdev/extensions" includes="oracle.onlinedb.jar" />
<fileset dir="${sdev.home}/jdev/extensions" includes="oracle.sqldeveloper.jar" />
<fileset dir="${sdev.home}/jdev/lib" includes="jdev.jar, ojc.jar, jdev-patch.jar" />
</path>
</target>

<target name="compile" depends="init"
description="Compile java code">
<delete dir="${built}/classes" />
<mkdir dir="${built}/classes" />
<javac classpathref="compile.classpath" destdir="${built}/classes"
source="1.5" target="1.5" debug="true" includeAntRuntime="false">
<src path="src" />
</javac>
</target>

<target name="jar" depends="compile"
description="Create the extension jar">

<jar basedir="${built}/classes"
destfile="${built}/${extension.filename}">
<zipfileset prefix="META-INF" dir="." includes="extension.xml" />
</jar>
</target>

<target name="deploy" depends="jar"
description="Deploy into the ide extensions directory">
<copy file="${built}/${extension.filename}"
tofile="${sdev.home}/jdev/extensions/${extension.filename}" />
</target>

</project>

Forgotten datatypes

After seeing the Georaptor project, I figured SQL Developer could use a method for rendering custom datatypes. Here's a quick example of how this is now possible in EA4.

The first thing is to make a class which is an Addin which registers the custom renderer.


import oracle.dbtools.raptor.controls.cellrenderers.CellRenderingFactory;
import oracle.ide.Addin;
import oracle.sql.CLOB;

public class CustomColRendererAddin implements Addin{

public void initialize() {
// register the custom renderer
CellRenderingFactory.registerCellRenderer(CLOB.class, new CLOBRenderer());

}
}

Next the actual renderer. This is a very simple example. The ICellRenderer interface has 2 methods on it. The getComponent is called first. If nothing is returned from any of the registered addins the getText is called next and will just be used in the standard component.



import java.awt.Component;
import javax.swing.JLabel;
import javax.swing.JTable;
import oracle.dbtools.raptor.controls.cellrenderers.ICellRenderer;
import oracle.sql.CLOB;

public class CLOBRenderer implements ICellRenderer {

public String getText(JTable table, Object value,
boolean isSelected, boolean hasFocus, int row, int column) {
// if getComponent returns null
// get text will be called to get the text for to be placed in the
// default component
return null;
}

public Component getComponent(JTable table, Object value,
boolean isSelected, boolean hasFocus, int row, int column) {
//
Component ret = null;
CLOB lob = (CLOB) value;
try {
JLabel l = new JLabel(lob.getSubString(1,20));
// the only real difference over the base renderer
// is the added tooltip
l.setToolTipText(lob.getSubString(1,1000));
ret = l;
} catch (Exception e) {
e.printStackTrace();
}
return ret;
}

}


The end results....

Reports with binds and versions

To add reports which have bind variables edit your UserReports.xml on linux this is found in ~/.raptor on windows this is in C:\Documents and Settings\[user]\.raptor If the file doesn't exist, within Raptor right click on the "User Defined Reports" and add either a folder or report this will get you the skeleton of the file.

Here's a very simple sql statement which has bind variables. Notice the sql has :BIND1. Then BIND1 is later defined as a bind with a name, description and tooltip. Also BIND1 can have a default value specified in the value tag. If the text "NULL_VALUE" is placed into the default, the prompt for binds will have the null checked.

Notes:

• Currently there is a bug which requires there be no whitespace between the <binds><bind> and the </bind></binds> This all apply if there is 2 binds the </bind><bind> should have no whitepace. This will be fixed in the next drop.
  
• If the reports are in anyway changed in raptor the results will be that the UserReports.xml will be regenerated without the extra information.
  

<report type="report" enable="true">
 <name><![CDATA[Test Report]]></name>
   <description><![CDATA[Kris's test report with binds]]></description>
     <tooltip><![CDATA[Tooltip for when the bind name is hovered over]]></tooltip>
     <query>
       <sql><![CDATA[select :BIND1 from dual]]></sql>
       <binds><bind id="BIND1">
            <prompt><![CDATA[First Bind Variable]]></prompt>
            <tooltip><![CDATA[Name or part of name to search for (case insensitive).]]></tooltip>
            <value><![CDATA[NULL_VALUE]]></value>
         </bind></binds>
      </query>
</report> 

Now you can also have report which execute difference sql depending on the version of the database. The only difference is in the query xml tag. By adding minversion="XX.ZZ", the query will be limited to run only in that version. I would recommend having a default query for other versions however if there is none the UI will state no applicable query is found for this version.

<report type="report" enable="true">
  <name><![CDATA[Test Report]]></name>
  <description><![CDATA[Kris's test report with binds]]></description>
  <tooltip><![CDATA[Tooltip for when the bind name is hovered over]]></tooltip>
  <query minversion="10.1">
   <sql><![CDATA[select :BIND10 from dual]]></sql>
   <binds><bind id="BIND10">
    <prompt><![CDATA[First Bind Variable]]></prompt>
    <tooltip><![CDATA[Name or part of name to search for (case insensitive).]]></tooltip>
    <value><![CDATA[NULL_VALUE]]></value>
   </bind></binds>
 </query>
 <query>
  <sql><![CDATA[select :BIND_OTHER from dual]]></sql>
   <binds><bind id="BIND_OTHER">
    <prompt><![CDATA[First Bind Variable]]></prompt>
    <tooltip><![CDATA[Name or part of name to search for (case insensitive).]]></tooltip>
    <value><![CDATA[NULL_VALUE]]></value>
   </bind></binds>
 </query>
</report> 

Custom Snippets in Raptor EA 3 v 897

Raptor EA 3 is out build #897. Here's how to add snippets in this build. Eventually a UI could be built around managing the snippets and maybe even subscribe via an rss feed but in the mean time give this a try.

Adding Custom Snippets:

raptor -Draptor.user.snippets=/home/klrice/snippets.xml
or edit the raptor.conf file and add
AddVMOption -Draptor.user.snippets=/home/klrice/snippets.xml


The snippets file should look like this:

<?xml version = '1.0' encoding = 'UTF-8'?>
<snippets>
  <group category="Kris' custom snippets" language="PLSQL">
    <snippet name="Select EMP" description="Too lazy to type it out">
      <code><![CDATA[select * from scott.emp]]>
    </snippet>
    <snippet name="Select EMP Local" description="Too lazy to type it out">
      <code><![CDATA[select * from emp]]>
    </snippet> 
</group>
  <group category="Kris' other snippets" language="PLSQL">
    <snippet name="Select DEPT" description="Too lazy to type it out">
      <code><![CDATA[select * from scott.dept]]>
    </snippet>
 </group>
</snippets>

Furthurmore the snippets window will reload eveytime it's opened if the file changes.

The Results

Raptor EA #2

Raptor Early Access V2 is out. Grab it here

Here's some stats.
over 100 fixes in EA #2

In 2 weeks time...
over 20,000 downloads in 2 weeks.
over 27,000 views on the forums. 6th in over DB category.