...

Pentaho Metadata's Dialect Plugin System

Introduced in release BI Suite Release 3.7, Pentaho Metadata supports additional dialects through it's Service Provider Interface (SPI): SQLDialectInterface. A Pentaho Metadata Dialect Plugin is a jar file that consists of a dialect implementation and some configuration. The SPI enables developers to develop new database dialects which Pentaho Metadata will automatically detect and register.

Implementing SQLDialectInterface

Database types are defined in Kettle with a unique database id, e.g. SampleDB. Dialects relate one-to-one with database type ids defined in Kettle DB Core or a Kettle Database Plugin. A dialect is an implementation of the SPI org.pentaho.pms.mql.dialect.SQLDialectInterface

...

TODO.

public interface SQLDialectInterface {
  
  public String getDatabaseType();
  
  public String quoteStringLiteral(Object str);
  
  public boolean isSupportedFunction(String functionName);
  
  public boolean isAggregateFunction(String functionName);
  
  public boolean isSupportedInfixOperator(String operator);
  
  public SQLFunctionGeneratorInterface getFunctionSQLGenerator(String functionName);
  
  public SQLOperatorGeneratorInterface getInfixOperatorSQLGenerator(String operatorName);
  
  public String getDateSQL(int year, int month, int day);
  
  public String generateSelectStatement(SQLQueryModel model);
  
  public int getMaxTableNameLength();
}

A base implementation exists and can be used as a simple starting point for most new dialects. In addition to the SPI, DefaultSQLDialect defines additional extension points to facilitate building new dialects quickly. Below is a dialect that defines the maximum table name length and a trivial method for quoting a String literal for our Sample Database:

public class SampleDialect extends DefaultSQLDialect {
  public SampleDialect() {
    super("SampleDB");
  }

  @Override
  public int getMaxTableNameLength() {
    return 128;
  }

  @Override
  public String quoteStringLiteral(Object str) {
    return "'" + str + "'";
  }
}

Declaring your Dialect as a Service Provider

Pentaho Metdata A jar file containing your new dialect should be created with an additional META-INF entry. Pentaho Metadata utilizes the Java Service Loader API to dynamically look up dialect implementations. The ServiceLoader API requires you list all Service Provider implementations in a file named after the interface of the service they provide in the META-INF/services directory of the jar file:

...

Where org.pentaho.pms.mql.dialect.SampleDialect is the implementation of SQLDialectInterface for our ficticious fictitious database type: SampleDB.

For more information see Jar Service Provider Packaging Notes from Oracle.

...

http://download.oracle.com/javase/6/docs/api/java/util/ServiceLoader.html
http://java.sun.com/developer/technicalArticles/javase/extensible/index.html

Sample Source Code