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 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    }