// Copyright (c) 2001 Hursh Jain (http://www.mollypages.org) 
// The Molly framework is freely distributable under the terms of an
// MIT-style license. For details, see the molly pages web site at:
// http://www.mollypages.org/. Use, modify, have fun !

package fc.jdbc;

import java.io.*;
import java.util.*;
import java.util.regex.*;
import java.lang.reflect.*;
import fc.io.*;
import fc.util.*;

/** 
Loads sql queries from a file. Storing queries in a file (when
the number of queries is greater than 10 or so, is a lot more 
manageable than embeddding these as strings in the program).

<p>
In the query file:
<ul style="list-style: square inside url('data:image/gif;base64,R0lGODlhBQAKAIABAAAAAP///yH5BAEAAAEALAAAAAAFAAoAAAIIjI+ZwKwPUQEAOw==')">

<li>comments start with hash or //</li>

<li>// comments can also end a line and anything after a // on a line is ignored.</li>

<li>empty lines (just whitespaces) are ignored</li>

<li>queries are of format:
<blockquote><pre>
queryname = querycontent
  ...querycontent continued...
  ;
</pre></blockquote>
</li>
<li>a ';' character by itself (on a separate line) ends a name = content
section. The query is stored and can be retrieved via it's name.
</li>

<li><p>
a special section within the content is enclosed within <b>$...$</b>
and is processed when this file is read. This section (if specified) refers
to a corresponding molly DBO class. For example:
</p>
<blockquote><pre>
userDetailQuery = 
  select 
    <b>$</b>package.foo<b>$</b>, <b>$</b>x.y.bar<b>$</b> from
  ...rest of query...
;
</pre></blockquote>
In this example, <code>package.foo</code> will be replaced by the call to
<code>package.<b>fooMgr</b>.columns()</code> and <code>$x.y.bar$</code> will be replaced
by a call to <code>x.y.<b>barMgr</b>.columns()</code>. Note, the names must be
fully qualified (that is, including the package name) since otherwise we won't
be able to locate the corresponding Mgr class at runtime.
<p>
The <b>$...$</b> can end with an optional <i>prefix</i>
after the ending $, for example, $...$<b><span style="color:blue">xxx</span></b>
<p>
If present, the <code>package.<b>fooMgr</b>.columns(<span style="color:blue"><i>prefix</i></span>)</code> is invoked to get the column
names. A prefix other than the default <i>tablename</i>.colname
formulation, (such as <i>xxx</i>_colname) is necessary, if the queryname
uses a <i>tablename <b>AS</b> <span style="color:blue">xxx</span></i> abbreviation anywhere 
in the query (if an abbreviation is used via the SQL AS clause, then that
abbreviation must be used everywhere, not the tablename).
</p>
</li>

<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.
<blockquote><pre>
userDetailQuery = 
  select 
    <b>$$</b>x.y.SOME_VAL<b>$$</b> 
  ...rest of query...
;
</pre></blockquote>
This will replace the value of the java field/attribute <code>x.y.XYZ</code> (in declaring class <code>y</code> located in package <code>x</code>).
This method does a straight value interpolation and does not add SQL stringify quotes if they are 
needed. For String or character types, quotes should be manually placed around the replaced value, 
such as:
<blockquote><pre>
userDetailQuery = 
  select 
    <span style="color:red">"</span><b>$$</b>x.y.SOME_STRING_VALUE<b>$$</b><span style="color:red">"</span>
;
</pre></blockquote>
<p>
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.
</li>
</ul>

<hr>

Here is a real world <i>example</i> of this file in action:
<p>
<ol>

<li>
The following file is called <b>my.queries</b> (the name is arbitrary) and
stored under <i>WEB-INF/foo/bar</i>
<pre>
# Get all friends of X
#   - get list of friends for X (friend uids from friends table)
# - get info about those friend uids from the users table
userFriends = 
  SELECT 
  u.name, u.status, u.gender, u.likes, u.pictime, u.hbtime,
  f.friend_uid, f.is_liked, f.is_unliked,
  is_friend(u.uid, ?) as twoway
  FROM
  users as u, friends as f
  WHERE
  f.uid = ? 
  and u.uid = f.friend_uid
;

# Get unread messages sent to user X
# Unread messages are grouped by the the sender id and message count per sender
#
unreadMessages = 
  SELECT
  from_uid, count(is_retrieved) as count
  FROM 
  messages 
  WHERE 
  to_uid = ? and is_retrieved = false 
  GROUP BY 
  from_uid
;

# Gets detailed information about place X
placeDetail = 
  SELECT
      $my.dbobj.location$
  FROM 
    location 
  WHERE
  location_id = ?
;
</pre>
</li>

<li>
This is then initialized/accessed from a servlet via the following code snippet:
<pre>
try 
  {
  //queryMgr is an instance variable in the servlet (can be later accessed
  //from other methods). webroot is a file pointing to the root directory
  //of this context.

  queryMgr = new QueryReader(
    new File(webroot, "<b>WEB-INF/foo/bar/my.queries</b>"), log); 
  log.info("Available queries: ", queryMgr.getQueries().keySet());
  }
catch (IOException e) {
  throw new ServletException(IOUtil.throwableToString(e));
  }
</pre>
</li>

<li>
Note: the placeDetail query in the example file contains the $..$
replaceable text <i>$my.dbobj.location$</i>. This means, that the
<i>my.dbobj.*</i> classes should be included in the invoking servlet's
classpath, such that the corresponding <i>my.dbobj.locationMgr</i>
class is found.
<p>
This would be ensured by putting the following at the top of the
servlet code above:
<p>
import <i>my.dbobj.*</i>;
</p>
</li>
</ol>
**/
public final class QueryReader
{
private static final boolean dbg = false;
public static final int   TEST_FIELD_1 = 1;
public static final String  TEST_FIELD_2 = "hello";

Log   log;
Map   queries = new LinkedHashMap();

/** 
Creates a query reader that reads queries from the specified file,
using the UTF-8 encoding and a default logger.

@throws IOException   on error reading from the file
*/
public QueryReader(File f) throws IOException
  {
  this(f, Log.getDefault());
  }
  
/** 
Creates a query reader that reads queries from the specified file,
using the UTF-8 encoding and the specified logger.

@throws IOException   on error reading from the file
*/
public QueryReader(File f, Log logger) throws IOException
  {
  log = logger; 
  BufferedReader in
      = new BufferedReader(
          new InputStreamReader(new FileInputStream(f), "UTF-8"));
  
  String line = null;
  StringBuilder buf = new StringBuilder (1024);
  
  while ( (line = in.readLine()) != null)
    {
    String trimline = line.trim();  //this gets rid of spaces, empty newlines, etc

    if (trimline.length() == 0 
      || trimline.startsWith("#") 
      || trimline.startsWith("//")) 
      {
      if (dbg) System.out.println("Skipping: " + line);
      continue;
      }

    //this skips a series of lines containing ;
    if (buf.length() == 0 && trimline.equals(";"))
      {
      if (dbg) System.out.println("Skipping: " + line);
      continue;
      }

    //ignore trailing comments starting with "//"
    String[] split = line.split("\\s+//", 2);
    line = split[0];
  
    if (dbg && split.length > 1) {
      System.out.println("Splitting line with trailing //");
      System.out.println("split=" + Arrays.asList(split));
      }
    
    if (trimline.equals(";")) 
      {
      processBuffer(buf);
      buf = new StringBuilder();
      }
    else if (line.trim().endsWith(";")) 
      {
      //Not in spec but added just for safety in case ';' appears 
      //on the same line 
      buf.append(line.substring(0, line.lastIndexOf(';')));
      processBuffer(buf);  
      buf = new StringBuilder();
      }
    else{
      //append original line, not trimline, this allows us
      //to keep original leading spaces in the query 
      buf.append(line);
      //this is important, either append a newline or a space
      //otherwise separate lines in the query file run into each
      //other IF there is no trailing/leading spaces on each
      //line. newline good since it preserves original formatting
      buf.append("\n");
      }
    }
    
  String remaining = buf.toString().trim();
  if (remaining.length() > 0) {
    log.error("No ending delimiter (';') seen, the following section was NOT processed: \n", remaining);
    }
    
  in.close();
  log.info("Processed: ", queries.size(), " queries from file: ", f.getAbsolutePath());
  }

//process 1 query at a time (invoked when ending ';' for each query is seen)
void processBuffer(StringBuilder buf)
  {
  String[] keyval = buf.toString().split("=", 2);

  if (dbg) {
    System.out.println(Arrays.asList(keyval));
    }

  if (keyval.length != 2) {
    log.error("Query sections must be of form 'name = value'. Badly formed section, NOT processed: \n", buf); 
    return;
    }
    
  String name = keyval[0].trim();
  String value = keyval[1].trim();

  if (queries.get(name) != null) {
    log.error("This query name ", name, " already exists prior to this section. Duplicate name NOT processed: \n", buf);  
    return;
    }

  StringBuffer sb = new StringBuffer();
  
  //dbo columns pattern
  //the first [^$] skips the $$ part (for field value expressions). 
  Pattern p = Pattern.compile("[^$]\\$\\s*([a-zA-Z_0-9.]+)\\s*\\$([a-zA-Z_0-9.]*)");  //=>  \$(...)\$optionalprefix
  Matcher m = p.matcher(value);
  while (m.find()) 
    {
    if (dbg) System.out.println("Matched columns $regex: " + m.group());
    String dbo = m.group(1);
    String mgrName = dbo + "Mgr";

    String prefix = m.group(2);
      
    if (dbg) System.out.println("Manager name = " +  mgrName);
    if (dbg) System.out.println("Column prefix name = " +  prefix);
    
    try {
      Class mgr = Class.forName(mgrName, true,
        Thread.currentThread().getContextClassLoader());
    
      Method method = null;
      String columns = null;
    
      if (prefix != null && ! prefix.equals("")) {
        method = mgr.getMethod("columns", new Class[] {String.class});
        columns = (String) method.invoke(null, prefix);
        }
      else{
        method = mgr.getMethod("columns", null);
        columns = (String) method.invoke(null, null);
        }
      
      m.appendReplacement(sb, columns);
      if (dbg) System.out.println("replacing: [" + dbo + "] with [" + columns + "]");
      }
    catch (ClassNotFoundException e) {
      log.error("Manager [", mgrName, "] for [$", dbo + "$] not found, this query will NOT be added. Query:\n-----------------------\n", buf, "\n-----------------------\n");
      return;     
      }
    catch (Exception e2) {
      log.error("Internal error while processing: ", buf);
      log.error("This query was NOT added");
      log.error(IOUtil.throwableToString(e2));
      return;           
      }
    } //~while
    
  m.appendTail(sb);

  //constant field values pattern $$...$$
  p = Pattern.compile("\\$\\$\\s*([a-zA-Z_0-9.]+)\\.([a-zA-Z_0-9]+)\\s*\\$\\$");  
  m = p.matcher(sb.toString());
  sb = new StringBuffer();
  
  while (m.find()) 
    {
    if (dbg) System.out.println("Matched constant field $regex: " + m.group());
    String classname = m.group(1);
    String fieldname = m.group(2);
      
    if (dbg) System.out.println("class name = " +  classname);
    if (dbg) System.out.println("field name = " +  fieldname);
    
    try {
      Class c = Class.forName(classname, true, Thread.currentThread().getContextClassLoader());
    
      Field field = c.getDeclaredField(fieldname);
      int modifiers = field.getModifiers();
      if (! Modifier.isStatic(modifiers) || ! Modifier.isFinal(modifiers)) {
        throw new Exception("Darn! Field: [" + field + "] was not declared static or final. It must be both for this reference to work!");
        }
      if (! Modifier.isPublic(modifiers)) {
        field.setAccessible(true);
        }
      
      Object fieldval = field.get(null);  
      
      m.appendReplacement(sb, String.valueOf(fieldval));
      if (dbg) System.out.println("replacing: [" + field + "] with [" + fieldval + "]");
      }
    catch (ClassNotFoundException e) {
      log.error("Class [", classname, "] not found, this query will NOT be added. Query:\n-----------------------\n", buf, "\n-----------------------\n");
      return;     
      }
    catch (NoSuchFieldException e) {
      log.error("Field [", fieldname, "] not found, this query will NOT be added. Query:\n-----------------------\n", buf, "\n-----------------------\n");
      return;     
      }
    catch (Exception e2) {
      log.error("Internal error while processing: ", buf);
      log.error("This query was NOT added");
      log.error(IOUtil.throwableToString(e2));
      return;           
      }
    } //~while

  m.appendTail(sb);

  queries.put(name, sb.toString()); 
  }
  
/** 
returns the query with the specified name or <code>null</code> if the query
does not exist. 
*/
public String getQuery(String name) {
  return (String) queries.get(name);
  }

/** 
returns the entire query map containing all successfully read queries.
*/
public Map getQueries() {
  return queries;
  }

  
public static void main (String args[]) throws IOException
  {
  Args myargs = new Args(args);
  String filestr = myargs.getRequired("file");
  QueryReader qr = new QueryReader(new File(filestr));
  System.out.println("----------------- processed queries ------------------");
  System.out.println(qr.queries);
  }
}