SQLstream Objects

<< Click to Display Table of Contents >>

Navigation:  Understanding Streaming SQL Concepts >

SQLstream Objects

Previous pageReturn to chapter overviewNext page

The following sections briefly describe each of the object types supported by the SQLstream catalog.

Schema. A named catalog object containing the definitions of schema objects, such as streams, tables, views, procedures and user defined functions.\

The following objects must be defined within schemas:

Schema Objects

Stream. A relation that does not store data, as a finite relation does. Instead, a stream implements a publish-subscribe protocol. It can be written to by multiple writers and read from by multiple readers.

Foreign Stream. A stream defined in the context of a schema and associated with a server, which itself is an instance of a foreign data wrapper to provide access within SQLstream to a flow of data either from or to an external system.

Foreign Table. A schema object that records the metadata necessary for SQLstream s-Server to be able to access a table (or similar data structure) in a remote database. A foreign table can be used in SQL (both queries and updates) just like a native table.

View. A relation that provides a reusable definition of a query. SQLstream supports views based on both streaming and non-streaming (finite) SELECT statements.

Pump. A SQLstream repository object that provides a continuously running   INSERT INTO stream SELECT ... FROM query   functionality, thereby enabling the results of a query to enter a named stream.

Repository Objects

The following objects are defined within s-Server and do not need to be in a schema:

Foreign Data Wrappers. These "wrap" external data sources or sinks to make them look like part of  s-Server. One common use of these in s-Server is "adapters," which are fundamentally pieces of Java code that plug in to the server and adapt outside data to make it look as if it were inside.

Server Object. A named catalog object that defines a particular named use of a foreign or local data wrapper, including as options any information needed to connect to the data source. This could be an external database, the location of a network feed, or the location of a log file. Note: SQL:2008 refers to this as a foreign server when using a foreign data wrapper.

User-Defined Routine. This is any user-defined program object, the generic term for a user-defined function, user-defined procedure or user-defined transform.

The following table describes the differences between user-defined functions, procedures, and transforms:

Type of UDR

Created by ...

Accepts ...

Returns ...

Comments

User-Defined Function (UDF)

CREATE FUNCTION

 

Accepts ... Zero or more parameters

 

Returns ... Scalar value

--- A UDF is used in a scalar expression; it takes (0 .. n) scalar arguments and returns a scalar value.

--- When used in a query, a scalar expression is evaluated for each row.

--- SQLstream allows the creation of Java or SQL functions.

User-Defined Procedure (UDP)

CREATE PROCEDURE

 

Accepts ... Zero or more parameters

 

Returns ... No values returned

A UDP is just a UDF that returns no value; it is evaluated for a side effect.

User-Defined Transform (UDX)

CREATE FUNCTION

 

Accepts ... Zero or more relations

 

Returns ... A streaming relation

--- A UDX is different: its output is a stream of rows, and its inputs can be scalars or be streams. In SQL,

--- a streaming argument to a UDX is represented as

     CURSOR(SELECT STREAM ...)

--- and a scalar is represented by any scalar expression.

The invocation of a UDX is quite often preceded by the keyword TABLE. For example:

 SELECT STREAM *

  FROM TABLE(

   filterSignal( CURSOR( SELECT STREAM * FROM "RawSignals")));