MySQL Documentation In Completion

Issue 156370

Review Page MySQLDocumentationInCompletionReview

Description

Incorporate the documentation with code completion.

To see if it's reasonable to use documentation, one html file per function, I created a test and basic implementation.

To store all files took about 3 seconds. To locate and read a file from a HashMap takes 1 millisecond or less.

An html file per function looks reasonable to me.

Also, since some functions have different parameter lists such as SUBSTRING(...) to show the correct documentation for that function is not as straightforward.

To consider function arguments, we'll include the count of the arguments and index as part of the html file name. The index is only needed if there are multiple formats of a function with the same number of arguments. One drawback is the multiple html files for some functions, but there aren't a lot of functions to begin with.

Each html file will have a naming convention:

[functionName]-[argument-count]-[index].html

For example, the html filename for SUBSTRING(str, len) would be SUBSTRING-2-0.html

or SUBSTRING(str FROM pos FOR len); would be SUBSTRING-2-2.html

Then the documentation for the function with specific arguments could be found easily.

The result: To make the documentation for MySQL functions available as part of the code completion in NB. Let's say you're typing a SQL SELECT statement, and you want to use a date function. But you can't remember what date functions are available or what they do exactly. Rather than having to go browse the MySQL docs, we want to provide the docs right there when you're typing.

e.g. "SELECT DA" and then you hit CTRL-Space to invoke completion. You get a drop-down that shows any column names or table names starting with "DA", but we want it to also show all functions starting with "DA". You then arrow down to "DATEDIFF", and then a window comes up off of that item that shows the documentation and URL for the DATEDIFF function.

Use cases

use case1: user types function_na then types Ctrl-space

result: matching method names with arguments plus documentation appearing in a popup

use case 2: user types function_name( then types Ctrl-space result: arguments that belong to function_name plus documentation appearing in a popup

use case 3: user types function_name(arg1 then types Ctrl-space

result: remaining list of args belonging to function_name plus documentation appearing in a popup

use case 4: user types function that doesn't match

result: no matches

use case 5: user types function that matches but argument that doesn't match

result: only return function

Prototype

I developed a class that parses html and stores the function names in a HashMap. Then the function name can be looked up in the HashMap. See BaseDocumentationProvider.java and BaseDocumentationProviderTest.java

The data used consisted of the 233 MySQL documentation files

To store all files took about 3 seconds. To locate and read a file from a HashMap takes 1 millisecond or less.

Design

A new module will be created with a public package containing classes to manage the function documentation.

Module contains:

• MySQL Function documentation (individual HTML files)
• Public class (BaseDocumentationProvider) to manage function documentation
• MySQL Reference documentation (TODO for M3)

FunctionDocumentationProvider will do the following:

• when a MySQL connection is established, load function documentation files module jar and extract to a new folder in the NetBeans system directory (userdir)
• store functions and documentation in a HashMap
• provide a retrieve API that returns a list of function documentation where functions match.
  • The retreive API expects to deal with functions.
  • If no function found then empty list returned, not null.
  • For closely matching function names or partial arguments then multiple Strings containing HTML may be returned.
  • If there's an exact match then only one String containing HTML is returned.
  • If no MySQL connection had beenestablished then an empty List will be returned.
  • If any exceptions should occur they will be handled within the retrieve method and logged. An empty list will be returned

API

Use FunctionDocSupport to retrieve the function definition.

MySQLFunctionDocProvider will provide a public method to retrieve the HTML source for a given function, partial function name and matching function name and arguments

 
public interface FunctionDocProvider {
    
    /*
     * Return a list of function documentation
     *
     * @ param vendor is the database vendor name
     * @ param functionName can be a complete or partial function name
     * @ param argsCount is the count of the number of function arguments.  If no arguments available then pass -1
     * @ param index is used if function can't be distinguished by argument.  If index is not needed then pass -1
     * @ return a list of Strings that contains the function documentation or empty list if no matches found, not null
     */
     public List<String> retrieve(String vendor, String functionName, int argsCount, int index) ;
}

@org.openide.util.lookup.ServiceProvider(service=org.netbeans.modules.db.sql.docs.FunctionDocProvider.class)
public class FunctionDocProviderImpl implements FunctionDocProvider {
    
     public List<String> retrieve(String functionName, int argsCount, int index) {
     ...
     }
}

public class FunctionDocSupport {

    public static List<String> retrieve(String vendor, String functionName, int argsCount, int index) {
        FunctionDocProvider provider = (FunctionDocProvider)Lookup.getDefault().lookup(FunctionDocrProvider.class);
        List<String> functionDef = new ArrayList<String>();
        if (provider != null) {
            functionDef = provider.retrieve(functionName, argsCount, index);
        }
        return functionDef;
    }
}

Attachments

BaseDocumentationProvider.java		6403 bytes
BaseDocumentationProviderTest.java		5411 bytes
DocumentationProvider.java		2706 bytes
eclipse_mysql_doc.png		207095 bytes
mysql_doc.png		57348 bytes