001 // Copyright (c) 2001 Hursh Jain (http://www.mollypages.org) 002 // The Molly framework is freely distributable under the terms of an 003 // MIT-style license. For details, see the molly pages web site at: 004 // http://www.mollypages.org/. Use, modify, have fun ! 005 006 package fc.jdbc; 007 008 import java.io.*; 009 import java.util.*; 010 import java.util.regex.*; 011 import java.lang.reflect.*; 012 import fc.io.*; 013 import fc.util.*; 014 015 /** 016 Loads sql queries from a file. Storing queries in a file (when 017 the number of queries is greater than 10 or so, is a lot more 018 manageable than embeddding these as strings in the program). 019 020 <p> 021 In the query file: 022 <ul style="list-style: square inside url('data:image/gif;base64,R0lGODlhBQAKAIABAAAAAP///yH5BAEAAAEALAAAAAAFAAoAAAIIjI+ZwKwPUQEAOw==')"> 023 024 <li>comments start with hash or //</li> 025 026 <li>// comments can also end a line and anything after a // on a line is ignored.</li> 027 028 <li>empty lines (just whitespaces) are ignored</li> 029 030 <li>queries are of format: 031 <blockquote><pre> 032 queryname = querycontent 033 ...querycontent continued... 034 ; 035 </pre></blockquote> 036 </li> 037 <li>a ';' character by itself (on a separate line) ends a name = content 038 section. The query is stored and can be retrieved via it's name. 039 </li> 040 041 <li><p> 042 a special section within the content is enclosed within <b>$...$</b> 043 and is processed when this file is read. This section (if specified) refers 044 to a corresponding molly DBO class. For example: 045 </p> 046 <blockquote><pre> 047 userDetailQuery = 048 select 049 <b>$</b>package.foo<b>$</b>, <b>$</b>x.y.bar<b>$</b> from 050 ...rest of query... 051 ; 052 </pre></blockquote> 053 In this example, <tt>package.foo</tt> will be replaced by the call to 054 <tt>package.<b>fooMgr</b>.columns()</tt> and <tt>$x.y.bar$</tt> will be replaced 055 by a call to <tt>x.y.<b>barMgr</b>.columns()</tt>. Note, the names must be 056 fully qualified (that is, including the package name) since otherwise we won't 057 be able to locate the corresponding Mgr class at runtime. 058 <p> 059 The <b>$...$</b> can end with an optional <i>prefix</i> 060 after the ending $, for example, $...$<b><font color=blue>xxx</font></b> 061 </p> 062 <p> 063 If present, the <tt>package.<b>fooMgr</b>.columns(<font 064 color=blue><i>prefix</i></font>)</tt> is invoked to get the column 065 names. A prefix other than the default <i>tablename</i>.colname 066 formulation, (such as <i>xxx</i>_colname) is necessary, if the queryname 067 uses a <i>tablename <b>AS</b> <font color=blue>xxx</font></i> abbreviation anywhere 068 in the query (if an abbreviation is used via the SQL AS clause, then that 069 abbreviation must be used everywhere, not the tablename). 070 </p> 071 </li> 072 073 <li>to access a constant static final class attribute, use <b>$$...$$</b>. This is useful when constant values (declared in code) are to be used in a query. 074 <blockquote><pre> 075 userDetailQuery = 076 select 077 <b>$$</b>x.y.SOME_VAL<b>$$</b> 078 ...rest of query... 079 ; 080 </pre></blockquote> 081 This will replace the value of the java field/attribute <tt>x.y.XYZ</tt> (in declaring class <tt>y</tt> located in package <tt>x</tt>). 082 This method does a straight value interpolation and does not add SQL stringify quotes if they are 083 needed. For String or character types, quotes should be manually placed around the replaced value, 084 such as: 085 <blockquote><pre> 086 userDetailQuery = 087 select 088 <font color=red>"</font><b>$$</b>x.y.SOME_STRING_VALUE<b>$$</b><font color=red>"</font> 089 ; 090 </pre></blockquote> 091 <p> 092 Note: the fields accessed this way must be delared as <b>final</b> and <b>static</b>. If they are not, then a error will be thrown when processing the query. 093 </li> 094 095 <hr> 096 097 Here is a real world <i>example</i> of this file in action: 098 <p> 099 <ol> 100 101 <li> 102 The following file is called <b>my.queries</b> (the name is arbitrary) and 103 stored under <i>WEB-INF/foo/bar</i> 104 <pre> 105 # Get all friends of X 106 # - get list of friends for X (friend uids from friends table) 107 # - get info about those friend uids from the users table 108 userFriends = 109 SELECT 110 u.name, u.status, u.gender, u.likes, u.pictime, u.hbtime, 111 f.friend_uid, f.is_liked, f.is_unliked, 112 is_friend(u.uid, ?) as twoway 113 FROM 114 users as u, friends as f 115 WHERE 116 f.uid = ? 117 and u.uid = f.friend_uid 118 ; 119 120 # Get unread messages sent to user X 121 # Unread messages are grouped by the the sender id and message count per sender 122 # 123 unreadMessages = 124 SELECT 125 from_uid, count(is_retrieved) as count 126 FROM 127 messages 128 WHERE 129 to_uid = ? and is_retrieved = false 130 GROUP BY 131 from_uid 132 ; 133 134 # Gets detailed information about place X 135 placeDetail = 136 SELECT 137 $my.dbobj.location$ 138 FROM 139 location 140 WHERE 141 location_id = ? 142 ; 143 </pre> 144 </li> 145 146 <li> 147 This is then initialized/accessed from a servlet via the following code snippet: 148 <pre> 149 try 150 { 151 //queryMgr is an instance variable in the servlet (can be later accessed 152 //from other methods). webroot is a file pointing to the root directory 153 //of this context. 154 155 queryMgr = new QueryReader( 156 new File(webroot, "<b>WEB-INF/foo/bar/my.queries</b>"), log); 157 log.info("Available queries: ", queryMgr.getQueries().keySet()); 158 } 159 catch (IOException e) { 160 throw new ServletException(IOUtil.throwableToString(e)); 161 } 162 </pre> 163 </li> 164 165 <li> 166 Note: the placeDetail query in the example file contains the $..$ 167 replaceable text <i>$my.dbobj.location$</i>. This means, that the 168 <i>my.dbobj.*</i> classes should be included in the invoking servlet's 169 classpath, such that the corresponding <i>my.dbobj.locationMgr</i> 170 class is found. 171 <p> 172 This would be ensured by putting the following at the top of the 173 servlet code above: 174 <p> 175 import <i>my.dbobj.*</i>; 176 </p> 177 </li> 178 </ol> 179 **/ 180 public final class QueryReader 181 { 182 private static final boolean dbg = false; 183 public static final int TEST_FIELD_1 = 1; 184 public static final String TEST_FIELD_2 = "hello"; 185 186 Log log; 187 Map queries = new LinkedHashMap(); 188 189 /** 190 Creates a query reader that reads queries from the specified file, 191 using the UTF-8 encoding and a default logger. 192 193 @throws IOException on error reading from the file 194 */ 195 public QueryReader(File f) throws IOException 196 { 197 this(f, Log.getDefault()); 198 } 199 200 /** 201 Creates a query reader that reads queries from the specified file, 202 using the UTF-8 encoding and the specified logger. 203 204 @throws IOException on error reading from the file 205 */ 206 public QueryReader(File f, Log logger) throws IOException 207 { 208 log = logger; 209 BufferedReader in 210 = new BufferedReader( 211 new InputStreamReader(new FileInputStream(f), "UTF-8")); 212 213 String line = null; 214 StringBuilder buf = new StringBuilder (1024); 215 216 while ( (line = in.readLine()) != null) 217 { 218 String trimline = line.trim(); //this gets rid of spaces, empty newlines, etc 219 220 if (trimline.length() == 0 221 || trimline.startsWith("#") 222 || trimline.startsWith("//")) 223 { 224 if (dbg) System.out.println("Skipping: " + line); 225 continue; 226 } 227 228 //this skips a series of lines containing ; 229 if (buf.length() == 0 && trimline.equals(";")) 230 { 231 if (dbg) System.out.println("Skipping: " + line); 232 continue; 233 } 234 235 //ignore trailing comments starting with "//" 236 String[] split = line.split("\\s+//", 2); 237 line = split[0]; 238 239 if (dbg && split.length > 1) { 240 System.out.println("Splitting line with trailing //"); 241 System.out.println("split=" + Arrays.asList(split)); 242 } 243 244 if (trimline.equals(";")) 245 { 246 processBuffer(buf); 247 buf = new StringBuilder(); 248 } 249 else if (line.trim().endsWith(";")) 250 { 251 //Not in spec but added just for safety in case ';' appears 252 //on the same line 253 buf.append(line.substring(0, line.lastIndexOf(';'))); 254 processBuffer(buf); 255 buf = new StringBuilder(); 256 } 257 else{ 258 //append original line, not trimline, this allows us 259 //to keep original leading spaces in the query 260 buf.append(line); 261 //this is important, either append a newline or a space 262 //otherwise separate lines in the query file run into each 263 //other IF there is no trailing/leading spaces on each 264 //line. newline good since it preserves original formatting 265 buf.append("\n"); 266 } 267 } 268 269 String remaining = buf.toString().trim(); 270 if (remaining.length() > 0) { 271 log.error("No ending delimiter (';') seen, the following section was NOT processed: \n", remaining); 272 } 273 274 in.close(); 275 log.info("Processed: ", queries.size(), " queries from file: ", f.getAbsolutePath()); 276 } 277 278 //process 1 query at a time (invoked when ending ';' for each query is seen) 279 void processBuffer(StringBuilder buf) 280 { 281 String[] keyval = buf.toString().split("=", 2); 282 283 if (dbg) { 284 System.out.println(Arrays.asList(keyval)); 285 } 286 287 if (keyval.length != 2) { 288 log.error("Query sections must be of form 'name = value'. Badly formed section, NOT processed: \n", buf); 289 return; 290 } 291 292 String name = keyval[0].trim(); 293 String value = keyval[1].trim(); 294 295 if (queries.get(name) != null) { 296 log.error("This query name ", name, " already exists prior to this section. Duplicate name NOT processed: \n", buf); 297 return; 298 } 299 300 StringBuffer sb = new StringBuffer(); 301 302 //dbo columns pattern 303 //the first [^$] skips the $$ part (for field value expressions). 304 Pattern p = Pattern.compile("[^$]\\$\\s*([a-zA-Z_0-9.]+)\\s*\\$([a-zA-Z_0-9.]*)"); //=> \$(...)\$optionalprefix 305 Matcher m = p.matcher(value); 306 while (m.find()) 307 { 308 if (dbg) System.out.println("Matched columns $regex: " + m.group()); 309 String dbo = m.group(1); 310 String mgrName = dbo + "Mgr"; 311 312 String prefix = m.group(2); 313 314 if (dbg) System.out.println("Manager name = " + mgrName); 315 if (dbg) System.out.println("Column prefix name = " + prefix); 316 317 try { 318 Class mgr = Class.forName(mgrName, true, 319 Thread.currentThread().getContextClassLoader()); 320 321 Method method = null; 322 String columns = null; 323 324 if (prefix != null && ! prefix.equals("")) { 325 method = mgr.getMethod("columns", new Class[] {String.class}); 326 columns = (String) method.invoke(null, prefix); 327 } 328 else{ 329 method = mgr.getMethod("columns", null); 330 columns = (String) method.invoke(null, null); 331 } 332 333 m.appendReplacement(sb, columns); 334 if (dbg) System.out.println("replacing: [" + dbo + "] with [" + columns + "]"); 335 } 336 catch (ClassNotFoundException e) { 337 log.error("Manager [", mgrName, "] for [$", dbo + "$] not found, this query will NOT be added. Query:\n-----------------------\n", buf, "\n-----------------------\n"); 338 return; 339 } 340 catch (Exception e2) { 341 log.error("Internal error while processing: ", buf); 342 log.error("This query was NOT added"); 343 log.error(IOUtil.throwableToString(e2)); 344 return; 345 } 346 } //~while 347 348 m.appendTail(sb); 349 350 //constant field values pattern $$...$$ 351 p = Pattern.compile("\\$\\$\\s*([a-zA-Z_0-9.]+)\\.([a-zA-Z_0-9]+)\\s*\\$\\$"); 352 m = p.matcher(sb.toString()); 353 sb = new StringBuffer(); 354 355 while (m.find()) 356 { 357 if (dbg) System.out.println("Matched constant field $regex: " + m.group()); 358 String classname = m.group(1); 359 String fieldname = m.group(2); 360 361 if (dbg) System.out.println("class name = " + classname); 362 if (dbg) System.out.println("field name = " + fieldname); 363 364 try { 365 Class c = Class.forName(classname, true, Thread.currentThread().getContextClassLoader()); 366 367 Field field = c.getDeclaredField(fieldname); 368 int modifiers = field.getModifiers(); 369 if (! Modifier.isStatic(modifiers) || ! Modifier.isFinal(modifiers)) { 370 throw new Exception("Darn! Field: [" + field + "] was not declared static or final. It must be both for this reference to work!"); 371 } 372 if (! Modifier.isPublic(modifiers)) { 373 field.setAccessible(true); 374 } 375 376 Object fieldval = field.get(null); 377 378 m.appendReplacement(sb, String.valueOf(fieldval)); 379 if (dbg) System.out.println("replacing: [" + field + "] with [" + fieldval + "]"); 380 } 381 catch (ClassNotFoundException e) { 382 log.error("Class [", classname, "] not found, this query will NOT be added. Query:\n-----------------------\n", buf, "\n-----------------------\n"); 383 return; 384 } 385 catch (NoSuchFieldException e) { 386 log.error("Field [", fieldname, "] not found, this query will NOT be added. Query:\n-----------------------\n", buf, "\n-----------------------\n"); 387 return; 388 } 389 catch (Exception e2) { 390 log.error("Internal error while processing: ", buf); 391 log.error("This query was NOT added"); 392 log.error(IOUtil.throwableToString(e2)); 393 return; 394 } 395 } //~while 396 397 m.appendTail(sb); 398 399 queries.put(name, sb.toString()); 400 } 401 402 /** 403 returns the query with the specified name or <tt>null</tt> if the query 404 does not exist. 405 */ 406 public String getQuery(String name) { 407 return (String) queries.get(name); 408 } 409 410 /** 411 returns the entire query map containing all successfully read queries. 412 */ 413 public Map getQueries() { 414 return queries; 415 } 416 417 418 public static void main (String args[]) throws IOException 419 { 420 Args myargs = new Args(args); 421 String filestr = myargs.getRequired("file"); 422 QueryReader qr = new QueryReader(new File(filestr)); 423 System.out.println("----------------- processed queries ------------------"); 424 System.out.println(qr.queries); 425 } 426 }