Custom Datasources

Developing Custom Datasources for ChartSQL Studio

Overview

ChartSQL has a pluggable datasource architecture. If a supported SQL database is not available in the production build, it is possible to create one.

Datasources Location

Datasources are stored in ./chartsql/core/model/datasources/your_datasource_name/your_datasource_name.cfc

A datasource named "CustomDatasource.cfc" would be saved like:

./chartsql/core/model/datasources/customdatasource/CustomDatasource.cfc

Datasource API

The following empty class is the minimum necessary functions to implement a datasource. In many cases, you can simply extend com.chartsql.core.model.JdbcDatasource. You only need to override functions if your needs differ.

component 
    isStudioDatasource="true"
    accessors="true"
    extends="com.chartsql.core.model.JdbcDatasource"    
{    
    /**
    * @sql A SQL statement to execute
    * @returns query 
    */
    public query function executeSql(string sql) {
        //code...
    }
    
    /**
    * Returns a lucee datasource struct
    */
    public struct function getConnectionInfo() {
        //code...
    }
    
    /**
    * Throws an error if the datasource connection cannot 
    * be verified otherwise we assume it is successfull
    */
    public void function verify(numeric timeout=5){
        //code...
    }
    
    /**
    * Returns an array of TableInfo for ChartSQL to show
    * available table in the schema browser
    */
    public TableInfo[] function getTableInfos(){
	//code...
    }

    /**
    * Returns an array of FieldInfo for ChartSQL to show
    * available fields/columns in the schema browser
    * @tableName A name of a table in the database
    */
    public FieldInfo[] function getFieldInfos(required string tableName){
        //code...		
    }    
    
    public DatasourceProcess[] function getProcesses(){
        //code...
    }
    
    
    public function killProcess(required DatasourceProcess DatasourceProcess){
        //code...
    }


}

Setup Metadata

The settings datasource setup wizard uses meta data defined within your datasource .cfc to determine the title, icon and form fields that are displayed in the editor.

An example of a datasource setup wizard fields

Display Name, Description and Icon

The friendly display name, description and icon are setup in the component meta data attributes

component
	accessors="true"
	extends="com.chartsql.core.model.JdbcDatasource"
	isStudioDatasource="true"
	displayName="SQLite"
	description="SQLite Local Database"
	iconClass="ti ti-file-database"
{
	//... code... 
}

Attribute	Example	Description
displayName	My Datasource	Friendly title for the datasource that shows up in the wizard
description	My Datasource Is Great	Short description for the datasource that shows up in the list of available datasources
iconClass	ti ti-file-database	A CSS class for an icon to use as the icon for the datasource. Available icons can be found here: https://tabler.io/icons

Datasource Properties

Typically you need the user to specify properties for the datasource connector. You configure the properties that the wizard will display by adding properties to the datasource .cfc

component
	accessors="true"
	extends="com.chartsql.core.model.JdbcDatasource"
	isStudioDatasource="true"
	displayName="SQLite"
	description="SQLite Local Database"
	iconClass="ti ti-file-database"
{

	property name="FolderPath" required="true" description="The local folder to store the database file.";
	property name="Database" required="true" description="The name of the database to connect to";
	
	// code...
}

Input Types

Text

The default HTML input type for a property is a simple text input

Custom Methods

A datasource can have custom methods that can be executed from the Studio settings page as additional buttons. This allows you to specify custom actions that users can take in regards to the datasource. It is defined as a remote method.

component
	accessors="true"
	extends="com.chartsql.core.model.JdbcDatasource"
	isStudioDatasource="true"
	displayName="SQLite"
	description="SQLite Local Database"
	iconClass="ti ti-file-database"
{

	property name="FolderPath" required="true" description="The local folder to store the database file.";
	property name="Database" required="true" description="The name of the database to connect to";
	
	remote function customMethod(){
		//... code
	}
}

Datasource Caching

An instance of the datasource is created and cached within the life of the Studio application when the Studio configuration is loaded. If the datasource source code has changed, or its instance needs to be refreshed, you can Settings & Customization or update the datasource from the settings.

Process Cancellation

By default, when the user cancels a running script, the query was likely already sent to the database. In this case, although the ChartSQL execution is marked as cancelled, the database query might still be running on the database. You can add Process Cancellation functions to your datasource so that ChartSQL can also terminate the process running on the datasource.

Cancellation Functions

Implementing process cancellation requires two things:

1. Implement the function getProcesses() which returns an array DatasourceProcess instances which represent running processes on the datasource

2. Implement the function killProcess() which ChartSQL will call to kill the process on the underlying datasource

Function Signatures

public DatasourceProcess[] function getProcesses(){
    //code...
}


public function killProcess(required DatasourceProcess DatasourceProcess){
    //code...
}

MySQL Example

/**
 * A MySQL database connector
*/
import com.chartsql.core.model.DatasourceProcess;
component
	extends="com.chartsql.core.model.JdbcDatasource"
	accessors="true"
	isStudioDatasource="true"
	displayName="MySQL Database"
	description="MySQL Database connnector or MySQL wire protocol"
	iconClass="ti ti-brand-mysql"
{
	property name="Class" default="com.mysql.jdbc.Driver" description="The class name of the JDBC driver to use";
	property name="Port" default="3306" description="The port number of the MySQL server";
	property name="Host" required="true" description="The host name of the database server";
	property name="Database" required="true" description="The name of the database to connect to";
	property name="Username" required="true" description="The username to use when connecting to the database";
	property name="Password" required="true" description="The password to use when connecting to the database";

	public DatasourceProcess[] function getProcesses(){

		var result = this.executeSql("SELECT * FROM information_schema.processlist WHERE command != 'Sleep'");

		var out = [];
		for(var row in result){
			out.append(
				new DatasourceProcess(
					Id = row.ID,
					Sql = row.Info
				)
			)
		}

		return out;

	}

	public function killProcess(required DatasourceProcess DatasourceProcess){

		this.executeSql("KILL #DatasourceProcess.getId()#;");

	}
}

Process Identification

ChartSQL knows which datasource process relates to which execution based on a unique Id that was generated and inserted into the SQL script.