Code Structure for Implementing Plugins

<< Click to Display Table of Contents >>

Navigation:  Integrating SQLstream Blaze with Other Systems > Appendix A: Legacy Adapters and Agents >

Code Structure for Implementing Plugins

Previous pageReturn to chapter overviewNext page

Note: This topic describes a legacy adapter. See the topics Reading from Other Sources and Writing to Other Destinations for current solutions.

Adapter and UDX code shares a common pattern, as follows:

An "installing" section

A "defining" section

A "calling" section

"Installing" sections for adapters and UDXes

Note: If you try to replace a JAR that is currently loading, you will receive a NullPointerException error. This is a limitation of the Sun Java Runtime Environment. More information about this limitation is available at http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6832972.

Installing section for the TableUpdate Adapter

CREATE OR REPLACE FOREIGN DATA WRAPPER "TableUpdate"

 LIBRARY 'class com.sqlstream.plugin.tableupdate.TableUpdateStreamControlPlugin'

 LANGUAGE JAVA

 DESCRIPTION 'adapter for applying external database changes';

 

Installing section for the TableLookup UDX

CREATE JAR "TableLookup"

 LIBRARY 'file:plugin/TableLookup.jar'

 OPTIONS(2);

 

"Defining" sections for adapters and UDXes

TableUpdate Adapter --- Defining section for a server

Define one SQL/MED external/foreign server for each set of tables using the same options

(Each table will have its own unique foreign stream; but one server can handle multiple such streams.)

CREATE OR REPLACE SERVER "MySQL_TableUpdates"

 FOREIGN DATA WRAPPER "TableUpdate"  --must match foreign data wrapper label above

   OPTIONS (

     SKIPVALIDATION 'false',

     URI 'jdbc:mysql://localhost:3306/mysql_2010_downloadstats',

     DRIVER 'com.mysql.jdbc.Driver',

     connParamPrefix 'dbConn_',

     "dbConn_databaseName" 'mysql_2010_downloadstats',

     "dbConn_user" 'SQLstream',

     "dbConn_password" '',

     "dbConn_applicationName" 'SQLstream TableUpdate Adapter',

     sqlDialect 'MySQL 5.x',

     pollingMillis '5000')

   DESCRIPTION 'MySQL 2010 TableUpdates';

 

TableUpdate Adapter --- Defining section for the foreign stream for that server

-- Define a foreign stream that will upsert to the mysql_2010_downloadstats table

CREATE OR REPLACE FOREIGN STREAM "Persist_DownloadsPerVersion" (

 "SQLS_opcode" CHAR(2) NOT NULL,

 "SQLS_chg" VARBINARY(32),

 "ts" TIMESTAMP NOT NULL,

 "product" VARCHAR(12) NOT NULL,

 "version" VARCHAR(12) NOT NULL,

 "dlType" VARCHAR(15),

 "country" VARCHAR(3),

 "count" INTEGER NOT NULL)

 SERVER "MySQL_TableUpdates"

 OPTIONS (

   TYPE 'tableUpdates',

   MASTER 'true',

   RequirePK 'false',

   updatesTable 'DownloadsPerVersion')

 DESCRIPTION 'apply streaming upserts to "DownloadsPerVersion"';

Defining sections for the TableLookup UDX

Define the function that the TableLookup UDX will use to access the Java extension

CREATE OR REPLACE FUNCTION "getDealerData"(

      inputRows CURSOR,

    controlCmds CURSOR,

         dsName VARCHAR(64),

      tableName VARCHAR(128),

        colName VARCHAR(128),

      cacheSize INTEGER,

   prefetchRows BOOLEAN,

   fuzzyLookups BOOLEAN)

   RETURNS TABLE(

               "dealer" VARCHAR(64),

                 "tstamp" TIMESTAMP,

               "full_day" BOOLEAN,

                "new_day" BOOLEAN,

  "dealer_events_today" INTEGER,

     "dealer_daily_avg" FLOAT,

  "dealer_window_total" INTEGER,

       "all_window_total" INTEGER,

            "num_updates" INTEGER,

   "dealer_sum_updates" BIGINT,

   "dealer_sum_squares" BIGINT,

        "all_sum_updates" BIGINT,

        "all_sum_squares" BIGINT)

   LANGUAGE JAVA

   PARAMETER STYLE SYSTEM DEFINED JAVA

   NO SQL

   EXTERNAL NAME '"TableLookup":com.sqlstream.plugin.tablelookup.TableLookup.lookup';

 

Define a TableLookup UDX Control Stream

CREATE OR REPLACE STREAM "DealerDataControl" (

   "SQLS_cmd" VARCHAR(12) NOT NULL, -- e.g. 'pause'/'suspend', 'resume', 'clearCache'

   "SQLS_reason" VARCHAR(100)

) DESCRIPTION 'TableLookup UDX control stream for getDealerData() function';

"Calling" sections for adapters and UDXes

The examples below show how adapters and UDXes are invoked.

Calling Section for the TableLookup UDX

Create a view that calls the getDealerData TableLookup Function

CREATE OR REPLACE VIEW "DailyAgencyDescription"

DESCRIPTION 'aggregated per-agency activity with historical description for that agency' AS

  SELECT STREAM

    "dealer",

    "tstamp",

    "full_day",

    "new_day",

    "dealer_events_today",   -- per-dealer count since midnight

    "dealer_daily_avg",      -- per-dealer historical daily average

    "dealer_window_total",   -- per-dealer count over current period

    "all_window_total",      -- all-dealers count over current period

    "num_updates",           -- number of times historical description has been updated [J]

    "dealer_sum_updates",    -- per-dealer sum of all historical updates [J]

    "dealer_sum_squares",    -- per-dealer "sum of the squares" of

                                                all historical updates [J]

    "all_sum_updates",       -- (repeated) grand total of

                                                all dealer dealer_sum_updates [J]

    "all_sum_squares"        -- (repeated) grand total of

                                                all dealer dealer_sum_squares [J]

  FROM TABLE("MyTable"."getDealerData"(

          CURSOR(SELECT STREAM * FROM "DailyAgencyActivity"),

          CURSOR(SELECT STREAM * FROM "DealerDataControl"),

          'descriptions_prod',     -- prod credentials

          'dealer_description',    -- table

          'dealer',                -- PK

          5,                       -- cache

          false,                   -- no preload

          false                    -- no fuzzy

      ));