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:
- 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.
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 …
|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”)));