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