Custom SQL

Rather than calling raw SQL queries at runtime, the Caliper SDK uses “stored procedures” registered ahead of time. Most of the DbObjects rely on stored procedures, and it is possible to write your own– you can even build new table schema.

Custom stored procedures are to be placed in the install/sql_server directory of your extension and will be run in alphanumeric order. These SQL scripts will only run in the sandbox environments, and will not run in the Q2 datacenter.

Why you might need to write your own

If the DbObjects provided by the Caliper SDK are not sufficient to access the data you need
If you are creating your own custom database table, specific to your use-case

Note

Reach out to the SDK team before writing custom SQL. Unexpected custom SQL can lead to much longer code reviews and deployment delays because the team will need to review and approve all SQL before running in the datacenter.

Caveats

To minimize the security and functionality risks of using custom SQL, please follow these steps:

Ensure all your custom stored procedures start with a common prefix that is NOT sdk_ or Q2_.
Any custom schema that you add must be non-destructive. You do NOT want a future re-run of your query to wipe out all data that has been written to your table.
Ensure your stored procedures are added to the Q2_ApiStoredProc table, either in the SQL itself or as part of the extension’s DbPlan, via use of the ApiStoredProc InstallStep. If this step is missed, you will not be able to call the stored procedure.
Test your SQL! Run it multiple times in a row with q2 run_sql. Make sure it works before running it against a production database! We cannot review every possible case under which your SQL could be run.

Automating installation

By default, these stored procedures will only be installed manually. Depending on how frequent the SQL will need to be installed, the SDK team may convert the SQL into something called an insight feature, or a marketplace feature. After review and approval by Q2’s database team, the sql will then be registered under a name. The addition of that name to your DbPlan’s insight_features, or marketplace_features list will then enable the feature– and your stored procs– as part of the installation.

Examples

A few examples of some custom SQL templates:

Custom Stored Procedure

CREATE OR ALTER PROCEDURE dbo.example_StoredProc @user_id    INT
AS
BEGIN

    SELECT * FROM dbo.example_Table AS et
    WHERE et.UserID = @user_id

END
GO

DECLARE @spShortName VARCHAR(50) ='example_StoredProc' /*a short name for your stored procedure.  This does not have to be the same as the SP name*/
DECLARE @spName VARCHAR(80) ='example_StoredProc' /*the name of your stored procedure*/
DECLARE @spDescription VARCHAR(120) ='Description goes here' /*a short description of what it does*/
DECLARE @spCategory VARCHAR(25) ='SDK' /*a general category for this (Feel free to reuse one of the existing categories in Q2_AuditCategory*/
DECLARE @spCategoryDescription VARCHAR(80) ='SDK action or stored proc execution' /*a short category description*/

INSERT INTO dbo.Q2_AuditCategory(ShortName, Description)
SELECT @spCategory, @spCategoryDescription
WHERE  NOT EXISTS (SELECT NULL FROM dbo.Q2_AuditCategory ac WHERE ac.ShortName=@spCategory)

INSERT INTO dbo.Q2_AuditAction(ShortName, Description, CategoryID, LogThisAction, ShowInCentral, SyslogThisAction,
                               IsRiskAction, AuditThisAction)
SELECT @spName, @spDescription, ac.CategoryID,
	1, /*this should always be 1*/
	1, /*If you do not want this to display in central or console, set this to 0 otherwise this should always be 1*/
	1, /*this should always be 1*/
	0, /*If this is a risky action.  Ie it moves money or alters demographic data use a 1 otherwise use a 0*/
	1 /*this should always be 1*/
FROM   dbo.Q2_AuditCategory ac
WHERE  ac.ShortName=@spCategory AND NOT EXISTS (SELECT NULL FROM dbo.Q2_AuditAction aa WHERE aa.ShortName=@spName)

INSERT INTO dbo.Q2_ApiStoredProc(StoredProcShortName, Description, StoredProcName, CommandTimeout, ActionID)
SELECT @spShortName, @spDescription, @spName,
	60,
	aa.ActionID
FROM   dbo.Q2_AuditAction aa
WHERE  aa.ShortName=@spName AND NOT EXISTS (SELECT NULL
                                            FROM   dbo.Q2_ApiStoredProc asp
                                            WHERE  asp.StoredProcShortName=@spShortName)

When creating a custom stored procedure, keep performance in mind. The team will look for indexed columns in WHERE and ORDER BY clauses as well as SARGability.

Custom Table

IF NOT EXISTS(SELECT NULL
              FROM sys.tables t
                       INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
              WHERE t.name = 'sdk_CaliperAPICustomEndpoint'
                AND s.name = 'dbo')
    BEGIN
        CREATE TABLE [dbo].[sdk_CaliperAPICustomEndpoint]
        (
            [CustomEndpointID] [int]          NOT NULL IDENTITY (1, 1),
            [ShortName]        [varchar](50)  NOT NULL,
            [Description]      [varchar](100) NULL,
            [WedgeAddressID]   [int]          NOT NULL,
            [Author]           [varchar](50)  NULL
        );


        ALTER TABLE [dbo].[sdk_CaliperAPICustomEndpoint]
            ADD CONSTRAINT [PKC_sdk_CaliperAPICustomEndpoint_ShortName] PRIMARY KEY CLUSTERED ([ShortName]);

        ALTER TABLE [dbo].[sdk_CaliperAPICustomEndpoint]
            ADD CONSTRAINT [FK_sdk_CaliperAPICustomEndpoint_Q2_WedgeAddress] FOREIGN KEY ([WedgeAddressID]) REFERENCES [dbo].[Q2_WedgeAddress] ([WedgeAddressID]);

    END

GO

When creating a custom table, keep these in mind:

Always check for the tables existence
All tables must have a clustered index
- It is easier to do this in a different script that will also check for the existence of the index before creation
Indexes and other constraints must be named
- Q2’s constraint naming convention is as follows: [PK/FK/IX][u if unique][c if clustered]_[table name]_[column names]_[inc if included fields]
Table names should have a grouping prefix, similar to how the Q2 tables are Q2_ or SDK tables are SDK_
All fields should be explicitly named NULL or NOT NULL
Tables should have a mechanism for removing old rows that are not needed anymore

Reviewing Custom SQL

If there are custom database scripts (stored procedures or tables), this will be reviewed and highly scrutinized. The database is currently Q2’s single point of failure, so we want to make sure that your custom scripts do not threaten the technology stack. These reviews will add more time to the deployment SLA. To reduce long wait times for deployments, here is a list of things we will check:

Does the SQL need to exist? Are there any other ways to solve the issue?
Does a DB object or HQ method already do the same procedure

If there is just a small change to a DB object to get it where it needs to be, we may insist on making the change

Are procedures and/or table names prefixed and have a schema identifier, ie. dbo.companyname_GetSomeData.
Are any custom tables using foreign keys to other Q2 tables? We don’t want to affect any Q2 operation that might fail because of a foreign key constraint on a custom table.
Are any procedures included to delete or cleanup rows on a custom table, to prevent it from being forever growing?
Are the procedures performant?

Are the queries sargable?

Do queries that return a large amount of data use pagination?

Are queries being performed on high traffic tables? These include, but are not limited to, the Q2_Audit, Q2_Message, and Q2_Notification tables

If a stored procedure is querying a list of entities, like Q2_Users, are deleted entities being filtered out?
Does the stored procedure have any EXEC functions?