# Custom Datasources ## 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. ```cfscript 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 ```cfscript 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: | ### 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 ```cfscript 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. ```cfscript 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](https://docs.chartsql.com/settings-and-customization#reload-studio "mention") 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 {% tabs %} {% tab title="Function Signatures" %} ```cfscript public DatasourceProcess[] function getProcesses(){ //code... } public function killProcess(required DatasourceProcess DatasourceProcess){ //code... } ``` {% endtab %} {% tab title="MySQL Example" %} ```cfscript /** * 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()#;"); } } ``` {% endtab %} {% endtabs %} ### 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. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.chartsql.com/chartsql-studio/datasources/custom-datasources.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.