SQLstream Objects

SQLstream Objects

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.

Schema Objects

The following objects must be defined within schemas:

  • Stream. A relation that does not store data. 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 associated with a server, that provides 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 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 be inserted into a 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: The SQL standard 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 UD 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”)));