Class SQLQuery

The SQLQuery object represents the query used to fetch event or other types of data from a database. Since SQL queries aren't stream-based like most of the data processed by Sentinel, some special magic is used to keep track of the records received and processed.

In essence, the SQLQuery object does the following:

Loads the base query from the "sqlquery.base" file in the Collector package (or other file if passed in)
Supplements the base query with a max number of records to receive (from a parameter).
Supplements the base query with a single "offset" which is used to fetch incremental batches of records.
Optionally sets a schema prefix if necessary
Stores the function used to extract the current offset from newly-fetched data.

The DB Connector uses this object to issue the correct query against the target database. Based on the max records to return and the offset, it will get the next set of records. Then, the Collector will process each record in turn. After each record is processed, the offset of that record should be stored back into the SQLQuery object to ensure that the next DB query starts from the correct place. Note that that offset information is persisted in the Sentinel database to ensure that it last across system restarts and will even migrate with the Collector/Connector if you move them to a new Collector Manager. Once the current batch of records is exhausted, a new query will be issued against the DB for the next batch of records. In the input file for the SQL Query, the query should be written exactly as it will be executed, except for:

'%d' - will be replaced with the maximum rows to return with a single query
'%s' - will be replaced with the last offset read from the database
'%p' - will be replaced with the schema prefix for table names.

Defined in: sqlquery.js.

Class Summary
Constructor Attributes	Constructor Name and Description
	SQLQuery(query) Constructs a new instance of a SQL query object.

Field Summary
Field Attributes	Field Name and Description
	baseQuery The base query is read from the sqlquery.base file in the Collector package.
	CACHE The cache which will hold the suboffset IDs to indicate that records have already been parsed
	fullBatch This is a flag which tracks when a full batch is requested
	maxRows The maxRows value is typically set by the Max_Rows_To_Return parameter.
	needQuery This flag tells us that we need to send a query, e.g.
	noNewData This is a flag which tracks if any new data was found in a new batch
	offset The offset value is used to determine where to start the query for the next batch of records.
	queryDelay This is a flag which tracks if a query has just been sent, in which case we suppress sending a new one until we see data or a nodata record.
	queryID The queryID is just a number used to help us make sure that old query data isn't hanging around in the Connector.
	queryMap A query is actually a hash that includes the query itself and a query ID.
	queryScheduled This variable works in conjunction with the queryDelay field to help us schedule queries to run in the future, thus allowing us to introduce delays in queries without actually making the Collector sleep (in case there's other data to process from other sources)
	schemaPrefix Sets a schema prefix to apply to database queries.
	SubOffsets Default suboffset behavior is false (off)

Method Summary
Method Attributes	Method Name and Description
	addParser(parser) Adds a parser that will be automatically called to determine the current offset.
	addSubParser(subparser) Enable suboffset handling for this SQLQuery and add a handler that will be automatically called to determine the suboffset.
	buildQuery() The buildQuery method builds a new SQL query to run against the database.
	Parser(input) The parser used to pull the latest offset out of the last database record.
	setMax(max) The setMax method sets the maximum records to fetch in a single query.
	setOffset(newOffset) The setOffset method is used to directly set the offset value for the next batch query.
	SubParser(input) The parser used to pull the latest offset out of the last database record.

Class Detail

SQLQuery(query)

Constructs a new instance of a SQL query object.
Author: Novell Engineering.

Parameters:
{File} query: The base SQL query; only used if not using default sqlquery.base file.

Field Detail

baseQuery

The base query is read from the sqlquery.base file in the Collector package. The base query can use two possible replacement values:

%d: The max # of records to return (typically set by parameter)
%s: The offset, meaning the place to start the next query from. This is typically a record number, date, or some other incrementing field.

CACHE

The cache which will hold the suboffset IDs to indicate that records have already been parsed

fullBatch

This is a flag which tracks when a full batch is requested

maxRows

The maxRows value is typically set by the Max_Rows_To_Return parameter. Please ensure that this parameter is included with DB Connectors if necessary. Any references to '%d' in the SQL Query will be replaced with this value.

needQuery

This flag tells us that we need to send a query, e.g. we've seen a -2, -1, or 0 record which indicates that we don't have any (more) real data to process.

noNewData

This is a flag which tracks if any new data was found in a new batch

offset

The offset value is used to determine where to start the query for the next batch of records. This is typically a record number, date, or some other incrementing field. Any references to '%s' in the SQL Query will be replaced with this value.

queryDelay

This is a flag which tracks if a query has just been sent, in which case we suppress sending a new one until we see data or a nodata record.

queryID

The queryID is just a number used to help us make sure that old query data isn't hanging around in the Connector.

queryMap

A query is actually a hash that includes the query itself and a query ID.

queryScheduled

This variable works in conjunction with the queryDelay field to help us schedule queries to run in the future, thus allowing us to introduce delays in queries without actually making the Collector sleep (in case there's other data to process from other sources)

schemaPrefix

Sets a schema prefix to apply to database queries. In certain rare cases, database queries require a schema name to be applied before the table names in the query (such as dbo.TABLE). This attribute sets the schema prefix that should be injected into the SQL Query - the initial value for this attribute will be taken from the instance.CONFIG.params.Schema_Prefix parameter - include the schema_prefix.xml template parameter if this is needed. Any references to '%p' in the SQL Query will be replaced with this value.

SubOffsets

Default suboffset behavior is false (off)

Method Detail

{Boolean} addParser(parser)

Adds a parser that will be automatically called to determine the current offset. The parser should be defined as a function that can be attached to this SQLQuery. instance.PARSER is provided as a convenient place to store pre-defined parser routines.

Example:

// ...in initialize() method
instance.PARSER.getOffset = function(input) {
  return lastrec.RecNumber;
  ...
}
// ..in preParse() method, after the first record is received
instance.CONFIG.DBQuery.addParser(instance.PARSER.getOffset);

Note that you could simply assign the parser to the default template SQLQuery object, but you can also predefine several parsers and dynamically create SQLQueries if you wish.

Parameters:
{Function} parser: - The parser function to attach to this session

Returns:: {Boolean} Result

addSubParser(subparser)

Enable suboffset handling for this SQLQuery and add a handler that will be automatically called to determine the suboffset. Many databases don't use an incremental event ID that can be guaranteed to be unique per event, and properly increment over time. To handle this scenario, we commonly query based on a timestamp offset, but in many cases the resolution of the timestamp is such that we may get multiple events for any given offset. Since we can't guarantee that we will get all the events assigned to a single offset (either because we hit MaxRows or because some of the events hadn't been generated yet), we need to query the same offset again until we see the next offset in the received data (which implies that we've passed into the next timeslice). The resulting issue, however, is that we may get duplicate events in subsequent queries which we must filter out be keeping track of which events we've already processed. To do this, we need to have a way to uniquely identify each event by some field or combination of fields, but this value need not be incremental. This feature uses a global cache of processed events, plus a registered function to extract the unique value from processed events.

Parameters:
subparser

{HashMap} buildQuery()

The buildQuery method builds a new SQL query to run against the database. It embeds the latest offset to ensure that only new records are collected.

Returns:: {HashMap} The SQL query and associated meta-information

Parser(input)

The parser used to pull the latest offset out of the last database record.

Parameters:
input

setMax(max)

The setMax method sets the maximum records to fetch in a single query. This method is generally not used; the maxRecs value is typically set by parameter. It is only provided for rare cases where direct override is needed.

Parameters:
{Number} max: Integer number of records to return per query

setOffset(newOffset)

The setOffset method is used to directly set the offset value for the next batch query. The offset value is typically configured by data that is received from the Connector, but a method is provided for setting it directly here. Do not use this outside the template unless you are doing something special. Note that if no offset is passed in to this function, it will automatically calculate the offset from the current Record.

Parameters:
{String} newOffset: The offset to set for the SQLQuery

SubParser(input)

The parser used to pull the latest offset out of the last database record.

Parameters:
input

Classes

Class SQLQuery