1 /*
  2  * Copyright © [2008-2009] Novell, Inc.  All Rights Reserved.
  3  * 
  4  * USE AND REDISTRIBUTION OF THIS WORK IS SUBJECT TO THE DEVELOPER LICENSE AGREEMENT
  5  * OR OTHER AGREEMENT THROUGH WHICH NOVELL, INC. MAKES THE WORK AVAILABLE.  THIS WORK 
  6  * MAY NOT BE ADAPTED WITHOUT NOVELL'S PRIOR WRITTEN CONSENT.
  7  * 
  8  * NOVELL PROVIDES THE WORK "AS IS," WITHOUT ANY EXPRESS OR IMPLIED WARRANTY, 
  9  * INCLUDING WITHOUT LIMITATION THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR 
 10  * A PARTICULAR PURPOSE, AND NON-INFRINGEMENT.  NOVELL, THE AUTHORS OF THE WORK, AND THE 
 11  * OWNERS OF COPYRIGHT IN THE WORK ARE NOT LIABLE FOR ANY CLAIM, DAMAGES, OR OTHER 
 12  * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT, OR OTHERWISE, ARISING FROM, OUT OF, 
 13  * OR IN CONNECTION WITH THE WORK OR THE USE OR OTHER DEALINGS IN THE WORK.
 14  */
 15 
 16 /**
 17  * @fileoverview
 18  * This file defines the SQLQuery class, which represents the SQL query that will be
 19  * issued against database-class data sources.
 20  */
 21 
 22 /**
 23  * Constructs a new instance of a SQL query object.
 24  * @class
 25  * The SQLQuery object represents the query used to fetch event or other types of data
 26  * from a database. Since SQL queries aren't stream-based like most of the data processed
 27  * by Sentinel, some special magic is used to keep track of the records received and processed. 
 28  * <p>
 29  * In essence, the SQLQuery object does the following:
 30  * <ul>
 31  * <li>Loads the base query from the "sqlquery.base" file in the Collector package (or other file if passed in)
 32  * <li>Supplements the base query with a max number of records to receive (from a parameter).
 33  * <li>Supplements the base query with a single "offset" which is used to fetch
 34  * incremental batches of records.
 35  * <li>Optionally sets a schema prefix if necessary
 36  * <li>Stores the function used to extract the current offset from newly-fetched data.
 37  * </ul>
 38  * The DB Connector uses this object to issue the correct query against the target database. Based
 39  * on the max records to return and the offset, it will get the next set of records. Then, the
 40  * Collector will process each record in turn. After each record is processed, the offset of that
 41  * record should be stored back into the SQLQuery object to ensure that the next DB query starts from
 42  * the correct place. Note that that offset information is persisted in the Sentinel database to 
 43  * ensure that it last across system restarts and will even migrate with the Collector/Connector
 44  * if you move them to a new Collector Manager. Once the current batch of records is exhausted, a
 45  * new query will be issued against the DB for the next batch of records.
 46  * In the input file for the SQL Query, the query should be written exactly as it will be executed, except for:
 47  * <ul>
 48  * <li>'%d' - will be replaced with the maximum rows to return with a single query
 49  * <li>'%s' - will be replaced with the last offset read from the database
 50  * <li>'%p' - will be replaced with the schema prefix for table names.
 51  * </ul>
 52  * @author   Novell Engineering
 53  * @version  6.1
 54  * @constructor
 55  * @param {File} query  The base SQL query; only used if not using default sqlquery.base file.
 56  */
 57 function SQLQuery(query) {
 58 	
 59 	if (typeof query == "undefined") {
 60 		query = "sqlquery.base";
 61 	}
 62 	/**
 63 	 * A query is actually a hash that includes the query itself and a query ID.
 64 	 */
 65 	this.queryMap = new HashMap();
 66 	
 67 	/**
 68 	 * The queryID is just a number used to help us make sure that old query data isn't
 69 	 * hanging around in the Connector.
 70 	 */
 71 	this.queryID = 1;
 72 	
 73 	var queryFile = new File(instance.CONFIG.collDir + "/" + query);
 74 	if (queryFile === "") { log("Could not find base query file"); return false; }
 75 	
 76 	/** 
 77 	 * The base query is read from the sqlquery.base file in the Collector package.
 78 	 * The base query can use two possible replacement values:
 79 	 * <dl>
 80 	 * <dt>%d</dt><dd>The max # of records to return (typically set by parameter)
 81 	 * <dt>%s</dt><dd>The offset, meaning the place to start the next query from. This is typically a
 82 	 * record number, date, or some other incrementing field.
 83 	 * </dl>
 84 	 */
 85 	this.baseQuery = "";
 86 	for (var inString = queryFile.readLine(); typeof inString != 'undefined'; inString = queryFile.readLine()) {
 87 		if (inString.charAt(0) != "~" && inString.charAt(0) != "-") {
 88 			this.baseQuery = this.baseQuery + inString + " ";
 89 		}
 90 	}
 91 	this.baseQuery = this.baseQuery.replace(/\n/, " ");
 92 	queryFile.close();
 93 	
 94 	/** 
 95 	 * The maxRows value is typically set by the Max_Rows_To_Return parameter. Please ensure
 96 	 * that this parameter is included with DB Connectors if necessary. Any references to '%d' in 
 97 	 * the SQL Query will be replaced with this value.
 98 	 */
 99 	this.maxRows = Number(instance.CONFIG.params.Max_Rows_To_Return);
100 	
101 	/**
102 	 * The setMax method sets the maximum records to fetch in a single query.
103 	 * This method is generally not used; the maxRecs value is typically set by parameter.
104 	 * It is only provided for rare cases where direct override is needed.
105 	 * @param {Number} max  Integer number of records to return per query
106 	 */
107 	this.setMax = function(max) {
108 		if ( max !== "" && max > 0 ) { this.maxRows = max; return true; }
109 		return false;
110 	};
111 	
112 	/**
113 	 * The offset value is used to determine where to start the query for the next batch
114 	 * of records.  This is typically a record number, date, or some other incrementing field.
115 	 * Any references to '%s' in the SQL Query will be replaced with this value.
116 	 */
117 	this.offset = 0;
118 	this.baseoffset = 0;
119 		
120 	/**
121 	 * The setOffset method is used to directly set the offset value for the next batch
122 	 * query.
123 	 * The offset value is typically configured by data that is received from the Connector,
124 	 * but a method is provided for setting it directly here. Do not use this outside
125 	 * the template unless you are doing something special.
126 	 * Note that if no offset is passed in to this function, it will automatically calculate
127 	 * the offset from the current Record.
128 	 * @param {String} newOffset  The offset to set for the SQLQuery
129 	 */	
130 	this.setOffset = function(newOffset) {
131 		if ( typeof newOffset == "undefined" ) { newOffset = this.Parser(rec); }
132 		if ( typeof newOffset != "undefined" && newOffset != "" ) {
133 			if ( this.offset != newOffset ) {
134 				this.offset = newOffset;
135 				if (typeof rec != "undefined") {
136 					instance.CONFIG.scriptContext.setOffset( rec.s_RV24, this.offset );
137 				}
138 				if ( this.SubOffsets ) { // new offset, so we can clear the cached suboffsets
139 					delete this.CACHE; 
140 					this.CACHE = {};
141 				}
142 			}
143 			if ( typeof rec != "undefined" && this.SubOffsets ) {
144 				this.CACHE[this.SubParser(rec)] = {}; // cache the suboffset
145 			}
146 			return true; 
147 		}
148 		return false;
149 	};
150 	
151 	/**
152 	 * Sets a schema prefix to apply to database queries.
153 	 * In certain rare cases, database queries require a schema name to be applied before the table names in the query (such as dbo.TABLE).
154 	 * This attribute sets the schema prefix that should be injected into the SQL Query - the initial value for this attribute will be
155 	 * taken from the instance.CONFIG.params.Schema_Prefix parameter - include the schema_prefix.xml template parameter if this is needed.
156 	 * Any references to '%p' in the SQL Query will be replaced with this value.
157 	 */
158 	this.schemaPrefix = instance.CONFIG.params.Schema_Prefix;
159 
160 	/**
161 	 * Adds a parser that will be automatically called to determine the current offset.
162 	 * The parser should be defined as a function that can be attached to this SQLQuery.
163 	 * instance.PARSER is provided as a convenient place to store pre-defined parser routines.
164 	 * <p>
165 	 * Example:
166 	 * <pre>
167 	 * // ...in initialize() method
168 	 * instance.PARSER.getOffset = function(input) {
169 	 *   return lastrec.RecNumber;
170 	 *   ...
171 	 * }
172 	 * // ..in preParse() method, after the first record is received
173 	 * instance.CONFIG.DBQuery.addParser(instance.PARSER.getOffset);
174 	 * </pre>
175 	 * Note that you could simply assign the parser to the default template SQLQuery object,
176 	 * but you can also predefine several parsers and dynamically create SQLQueries if you wish.
177 	 * @param {Function} parser  - The parser function to attach to this session
178 	 * @return {Boolean} Result
179 	 */
180 	this.addParser = function( parser ) {
181 			if ( typeof parser != 'function' ) { return false; }
182 			this.Parser = parser;
183 			return true;
184 	};
185 		
186 	/**
187 	 * The parser used to pull the latest offset out of the last database record.
188 	 */
189 	this.Parser = function(input) {
190 		return true;
191 	};
192 
193 	/**
194 	 * Enable suboffset handling for this SQLQuery and add a handler that will be automatically called to determine the suboffset.
195 	 * Many databases don't use an incremental event ID that can be guaranteed to be unique per event, and properly increment over time.
196 	 * To handle this scenario, we commonly query based on a timestamp offset, but in many cases the resolution of the timestamp is such that 
197 	 * 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
198 	 * (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
199 	 * see the next offset in the received data (which implies that we've passed into the next timeslice).
200 	 * The resulting issue, however, is that we may get duplicate events in subsequent queries which we must filter out be keeping track of 
201 	 * 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, 
202 	 * but this value need not be incremental.
203 	 * This feature uses a global cache of processed events, plus a registered function to extract the unique value from processed events. 
204 	 */
205 	this.addSubParser = function( subparser ) {
206 		if ( typeof subparser != 'function' ) { return false; }
207 		this.SubParser = subparser;
208 		this.SubOffsets = true;
209 		return true;
210 	}
211 	
212 	/**
213 	 * The parser used to pull the latest offset out of the last database record.
214 	 */
215 	this.SubParser = function(input) {
216 		return true;
217 	};
218 	
219 	/**
220 	 * Default suboffset behavior is false (off)
221 	 */
222 	this.SubOffsets = false;
223 	
224 	/**
225 	 * This is a flag which tracks when a full batch is requested
226 	 */
227 	this.fullBatch = true;
228 	
229 	/**
230 	 * This is a flag which tracks if any new data was found in a new batch
231 	 */
232 	this.noNewData = true;
233 	
234 	/**
235 	 * 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)
236 	 * real data to process.
237 	 */
238 	this.needQuery = false;
239 	
240 	/**
241 	 * 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
242 	 * data or a nodata record.
243 	 */
244 	this.queryDelay = 0;
245 	
246 	/**
247 	 * This variable works in conjunction with the queryDelay field to help us schedule queries to run in the future,
248 	 * thus allowing us to introduce delays in queries without actually making the Collector sleep (in case there's other
249 	 * data to process from other sources)
250 	 */
251 	this.queryScheduled=new Date();
252 	
253 	/**
254 	 * The cache which will hold the suboffset IDs to indicate that records have already been parsed
255 	 */
256 	this.CACHE = {};
257 		
258 	/**
259 	 * The buildQuery method builds a new SQL query to run against the database. It embeds
260 	 * the latest offset to ensure that only new records are collected.
261 	 * @return {HashMap} The SQL query and associated meta-information
262 	 */
263 	this.buildQuery = function() {
264 		var newQuery = this.baseQuery.replace(/%d/, this.maxRows);
265 		newQuery = newQuery.replace(/%s/, this.offset);
266 		newQuery = newQuery.replace(/%p/g, this.schemaPrefix);
267 		this.queryMap.put("DATA", newQuery);
268 	 	this.queryMap.put("QueryID", String(this.queryID));
269 		this.queryID++;
270 		return this.queryMap;
271 	};
272 	
273 	return true;
274 }
275