Often, custom plugins require persistent data storage. While there are many solutions for persistent storage, one option is to use the Verint Community database. Plugins can identify that they need database access and can then install their own schema and use it to implement their behavior.
Creating a Plugin with Database Access
To receive database access details in a custom plugin, the plugin must implement the ISqlServerConnectedPlugin plugin type. The ISqlServerConnectedPlugin extends IInstallablePlugin to add support for receiving database connection details.
The ISqlServerConnectedPlugin interface adds only a single method, SetController() which is used by Verint Community to provide the plugin with database connection details when the plugin is enabled:
- On Verint-hosted instances of Verint Community, each enabled plugin gets a custom schema and a connection string which provides access only to that schema. This helps to prevent conflicts between different plugins within access to the database and prevents plugins from accessing the Verint Community schema which is not an extensibility point.
- On self-hosted instances of Verint Community, Environment Configuration must be setup to provide plugins with connection strings manually.
ISqlServerConnectedPluginsin self-hosted environments without corresponding connection strings defined in the Plugin Connection Strings section of environment configuration will not be initialized and an exception will be logged identifying the missing environment configuration. Even when self-hosting, it is recommended to create unique schemas for each plugin and set that schema as the default schema for plugin-specific logins for each database-connected plugin.
The parameter to SetController() provides the plugin with the SQL connection string and the name of the schema for the plugin. The plugin should save this information privately for use in the implementation of the plugin's behavior.
ISqlConnectedPlugins are expected to install their tables and other database resources and remove any relevant resources through the IInstallablePlugin implementation. Stored data does not need to be removed via the Uninstall() method, however, since it is scoped to the plugin's specific schema and can persist even when the plugin is disabled or uninstalled. For Verint-hosted instances of Verint Community, plugin-hosted schemas are displayed in Administration > Site > Extension Data and individual schemas can be deleted when the associated plugin is disabled or uninstalled. For self-hosted instances of Verint Community, plugin-hosted schemas must be removed manually.
Implementation Example
The following code example shows a simple key/value get/set/delete API exposed to scripting with SQL Server-persisted storage via an ISqlServerConnectedPlugin implementation.
using System;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using Telligent.Evolution.Extensibility.Api.Entities.Version1;
using Telligent.Evolution.Extensibility.UI.Version1;
using Telligent.Evolution.Extensibility.Version1;
namespace PluginSamples
{
public class SqlServerConnectedPlugin : ISqlServerConnectedPlugin, IScriptedContentFragmentExtension
{
ISqlServerConnectedPlugionController? _sqlConnectionController;
#region IPlugin Implementation
public string Name => "SQL Server Connected Plugin Sample";
public string Description => "Exposes a scripting API (samples_v1_sqlconnectedplugin) to enable custom key/value storage in SQL Server.";
public void Initialize()
{
}
#endregion
#region ISqlServerConnectedPlugin Implementation
public void SetController(ISqlServerConnectedPlugionController controller)
{
_sqlConnectionController = controller;
}
#endregion
#region IScriptedContentFragmentExtension Implementation
public string ExtensionName => "samples_v1_sqlconnectedplugin";
public object? Extension
{
get
{
if (_sqlConnectionController != null)
return new ScriptApi(_sqlConnectionController.ConnectionString, _sqlConnectionController.Schema);
return null;
}
}
#endregion
#region IInstallablePlugin Implementation
public Version Version => new Version(1, 0, 0, 0);
public void Install(Version lastInstalledVersion)
{
if (_sqlConnectionController != null)
{
using (var connection = new SqlConnection(_sqlConnectionController.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand($""""
IF OBJECT_ID(N'{_sqlConnectionController.Schema}.data', N'U') IS NULL
BEGIN
EXEC sp_executesql N'
CREATE TABLE [{_sqlConnectionController.Schema}].data
(
[Key] NVARCHAR(255) NOT NULL,
[Value] NVARCHAR(255) NOT NULL,
CONSTRAINT PK_data PRIMARY KEY CLUSTERED
(
[Key]
)
)'
END
"""", connection))
{
connection.Open();
cmd.ExecuteNonQuery();
}
}
}
}
public Task InstallAsync(Version lastInstalledVersion, CancellationToken cancellationToken)
{
return TaskUtility.FromSync(() => Install(lastInstalledVersion));
}
public void Uninstall()
{
// Verint Community exposes options to remove schema after plugin installation to prevent
// data loss. There is nothing else to uninstall.
}
public Task UninstallAsync(CancellationToken cancellationToken)
{
// Verint Community exposes options to remove schema after plugin installation to prevent
// data loss. There is nothing else to uninstall.
return Task.CompletedTask;
}
#endregion
}
#region Script API
public class ScriptApi
{
string _connectionString;
string _schema;
public ScriptApi(string connectionString, string schema)
{
_connectionString = connectionString;
_schema = schema;
}
public AdditionalInfo Set(string key, string value)
{
try
{
using (var connection = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand($""""
SET NOCOUNT ON
DECLARE @Err INT
DECLARE @Rowcount INT
BEGIN TRAN
UPDATE d
SET [Value] = @Value
FROM [{_schema}].data d
WHERE d.[Key] = @Key
SELECT @Err = @@ERROR, @Rowcount = @@ROWCOUNT
IF @Err <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
IF @Rowcount = 0
BEGIN
INSERT INTO {_schema}.data ( [Key], [Value] )
VALUES ( @Key, @Value )
SET @Err = @@ERROR
IF @Err <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
END
COMMIT TRAN
"""", connection))
{
cmd.Parameters.Add(new SqlParameter("@Key", SqlDbType.NVarChar, 255)).Value = key;
cmd.Parameters.Add(new SqlParameter("@Value", SqlDbType.NVarChar, 255)).Value = value;
connection.Open();
cmd.ExecuteNonQuery();
}
}
return new AdditionalInfo();
}
catch (Exception ex)
{
return new AdditionalInfo(ex);
}
}
public string? Get(string key)
{
try
{
using (var connection = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand($""""
SELECT [Value]
FROM [{_schema}].data
WHERE [Key] = @Key
"""", connection))
{
cmd.Parameters.Add(new SqlParameter("@Key", SqlDbType.NVarChar, 255)).Value = key;
connection.Open();
using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (reader.Read())
return reader.GetString("Value");
}
}
}
return null;
}
catch (Exception ex)
{
new AdditionalInfo(ex);
return null;
}
}
public AdditionalInfo Delete(string key)
{
try
{
using (var connection = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand($""""
DELETE
FROM [{_schema}].data
WHERE [Key] = @Key
"""", connection))
{
cmd.Parameters.Add(new SqlParameter("@Key", SqlDbType.NVarChar, 255)).Value = key;
connection.Open();
cmd.ExecuteNonQuery();
}
}
return new AdditionalInfo();
}
catch (Exception ex)
{
return new AdditionalInfo(ex);
}
}
}
#endregion
}