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.
Display Name, Description and Icon
The friendly display name, description and icon are setup in the component meta data attributes
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 #reload-studio 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:
Implement the function getProcesses() which returns an array DatasourceProcess instances which represent running processes on the datasource
Implement the function killProcess() which ChartSQL will call to kill the process on the underlying datasource
public DatasourceProcess[] function getProcesses(){
//code...
}
public function killProcess(required DatasourceProcess DatasourceProcess){
//code...
}
/**
* 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.
