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
006package fc.jdbc;
007
008import java.io.*;
009import java.util.*;
010import java.util.regex.*;
011import java.lang.reflect.*;
012import fc.io.*;
013import fc.util.*;
014
015/** 
016Loads sql queries from a file. Storing queries in a file (when
017the number of queries is greater than 10 or so, is a lot more 
018manageable than embeddding these as strings in the program).
019
020<p>
021In 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>
032queryname = querycontent
033  ...querycontent continued...
034  ;
035</pre></blockquote>
036</li>
037<li>a ';' character by itself (on a separate line) ends a name = content
038section. The query is stored and can be retrieved via it's name.
039</li>
040
041<li><p>
042a special section within the content is enclosed within <b>$...$</b>
043and is processed when this file is read. This section (if specified) refers
044to a corresponding molly DBO class. For example:
045</p>
046<blockquote><pre>
047userDetailQuery = 
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>
053In 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
055by a call to <tt>x.y.<b>barMgr</b>.columns()</tt>. Note, the names must be
056fully qualified (that is, including the package name) since otherwise we won't
057be able to locate the corresponding Mgr class at runtime.
058<p>
059The <b>$...$</b> can end with an optional <i>prefix</i>
060after the ending $, for example, $...$<b><font color=blue>xxx</font></b>
061</p>
062<p>
063If present, the <tt>package.<b>fooMgr</b>.columns(<font
064color=blue><i>prefix</i></font>)</tt> is invoked to get the column
065names. A prefix other than the default <i>tablename</i>.colname
066formulation, (such as <i>xxx</i>_colname) is necessary, if the queryname
067uses a <i>tablename <b>AS</b> <font color=blue>xxx</font></i> abbreviation anywhere 
068in the query (if an abbreviation is used via the SQL AS clause, then that
069abbreviation 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>
075userDetailQuery = 
076  select 
077    <b>$$</b>x.y.SOME_VAL<b>$$</b> 
078  ...rest of query...
079;
080</pre></blockquote>
081This 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>).
082This method does a straight value interpolation and does not add SQL stringify quotes if they are 
083needed. For String or character types, quotes should be manually placed around the replaced value, 
084such as:
085<blockquote><pre>
086userDetailQuery = 
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>
092Note: 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
097Here is a real world <i>example</i> of this file in action:
098<p>
099<ol>
100
101<li>
102The following file is called <b>my.queries</b> (the name is arbitrary) and
103stored 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
108userFriends = 
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#
123unreadMessages = 
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
135placeDetail = 
136  SELECT
137      $my.dbobj.location$
138  FROM 
139    location 
140  WHERE
141  location_id = ?
142;
143</pre>
144</li>
145
146<li>
147This is then initialized/accessed from a servlet via the following code snippet:
148<pre>
149try 
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  }
159catch (IOException e) {
160  throw new ServletException(IOUtil.throwableToString(e));
161  }
162</pre>
163</li>
164
165<li>
166Note: the placeDetail query in the example file contains the $..$
167replaceable text <i>$my.dbobj.location$</i>. This means, that the
168<i>my.dbobj.*</i> classes should be included in the invoking servlet's
169classpath, such that the corresponding <i>my.dbobj.locationMgr</i>
170class is found.
171<p>
172This would be ensured by putting the following at the top of the
173servlet code above:
174<p>
175import <i>my.dbobj.*</i>;
176</p>
177</li>
178</ol>
179**/
180public final class QueryReader
181{
182private static final boolean dbg = false;
183public static final int   TEST_FIELD_1 = 1;
184public static final String  TEST_FIELD_2 = "hello";
185
186Log   log;
187Map   queries = new LinkedHashMap();
188
189/** 
190Creates a query reader that reads queries from the specified file,
191using the UTF-8 encoding and a default logger.
192
193@throws IOException   on error reading from the file
194*/
195public QueryReader(File f) throws IOException
196  {
197  this(f, Log.getDefault());
198  }
199  
200/** 
201Creates a query reader that reads queries from the specified file,
202using the UTF-8 encoding and the specified logger.
203
204@throws IOException   on error reading from the file
205*/
206public 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)
279void 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/** 
403returns the query with the specified name or <tt>null</tt> if the query
404does not exist. 
405*/
406public String getQuery(String name) {
407  return (String) queries.get(name);
408  }
409
410/** 
411returns the entire query map containing all successfully read queries.
412*/
413public Map getQueries() {
414  return queries;
415  }
416
417  
418public 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}