package com.ormlite;

/*
Copyright (c) 2007, David A. Medlock

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
*/

import org.jdom.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.*;
import java.lang.reflect.*;
import javax.sql.DataSource;
import groovy.sql.*;
import org.apache.commons.logging.*;

/*
  Version 0.62

  Current caveats:
    - Do not use literals in your <where> clauses, use variables
    - Column names with spaces will not be correctly rewritten in Querys
    - Cannot populate an object directly, library creates all objects
    - Cannot populate object across different datasources

  ===================================================================

  XML format:
  mapping = data(name,class,source?)+
  data = table(name)+ | child(name,type,many?)
  table = field(name,type,column?,key?,auto?)+
  child = key(field,foreign)

  core types: ( should this require these types for beans? )
    bit = Boolean
    date= java.sql.Date
    float, double = Double
    real = Float
    int = Integer
    smallint, tinyint = Short
    numeric,decimal = BigDecimal
    varchar = String
*/

// Optional base class, to allow ActiveRecord design pattern
public class BaseRecord
{
  private DataDefinition   getDataDef() { return ORM.getDefinition(getClass()); }

  public void    delete()  { getDataDef().delete( this ); }

  public void		 insert()  { getDataDef().insert(this); }
  public void		 update()  { getDataDef().update(this); }

  public List   findChildren(Class cls) {
    return ORM.getDefinition(cls).findChildrenOf( getDataDef(), this );
  }

  public List   findChildren(String name) {
    return ORM.getDefinition(name).findChildrenOf( getDataDef(), this );
  }

  public int   deleteChildren(Class cls) { return ORM.getDefinition(cls).deleteChildrenOf( getDataDef(), this ); }
  public int   deleteChildren(String name) { return ORM.getDefinition(name).deleteChildrenOf( getDataDef(), this ); }

  public  void  loadAllChildren()     { getDataDef().loadAllChildren( this ); }
  public  void  deleteAllChildren()   { getDataDef().deleteAllChildren(this); }

  // the following is undocumented, therefore do not use until it is...
  protected boolean     is_new_record = true;
  public void           save()  { if ( is_new_record ) this.insert(); else this.update(); }
}

private class Utils
{
  static Log     log = LogFactory.getLog("com.ormlite");

  final int BIT = java.sql.Types.BIT;
  final int VARCHAR = java.sql.Types.VARCHAR;
  final int TINYINT = java.sql.Types.TINYINT;
  final int SMALLINT = java.sql.Types.SMALLINT;
  final int INTEGER = java.sql.Types.INTEGER;
  final int FLOAT = java.sql.Types.FLOAT;
  final int DOUBLE = java.sql.Types.DOUBLE;
  final int REAL = java.sql.Types.REAL;
  final int DECIMAL = java.sql.Types.DECIMAL;
  final int NUMERIC = java.sql.Types.NUMERIC
  final int DATE = java.sql.Types.DATE;
  final int TIME = java.sql.Types.TIME;
  final int TIMESTAMP = java.sql.Types.TIMESTAMP;

  protected boolean empty(s) { return (s==null) ? true : s.trim()==""; }

  String	attrib(Element e, String n) // required XML attribute
  {
    def r = e.getAttributeValue(n)
    if ( empty(r) ) throw new Exception("Expected attribute:$n in XML element:" + e.getName() );
    return r;
  }

  String	attrib(Element e, String n, String dflt ) // optional XML attribute
  {
    def r = e.getAttributeValue(n)
    return empty(r) ? dflt : r.trim()
  }

  // normalize a value to one of the supported sql values
  Object	normalize(int type, val)
  {
    if ( val==null ) return val

    switch(type)
    {
    case VARCHAR : return val.toString();
    case BIT:
    case INTEGER: return makeInteger(val);
    case FLOAT:
    case DOUBLE:
    case REAL:
    case NUMERIC:
    case DECIMAL: return makeDecimal(val)
    case DATE:
    case TIMESTAMP: return makeTime(val);
    }
  }

  BigInteger makeInteger(val) { return makeDecimal(val).toBigInteger() }

  BigDecimal makeDecimal(val)
  {
    if ( val instanceof CharSequence ) return new BigDecimal(val.toString());
    switch(val.getClass())
    {
    case Boolean.class:
    case boolean.class:
      if ( val==true ) return new BigDecimal(1)
      else return new BigDecimal(0);

    case double.class:
    case int.class:
    case long.class: return new BigDecimal(String.valueOf(val));

    case Short.class:
    case Long.class:
    case Integer.class:
    case Double.class:
    case Float.class: return new BigDecimal(val.doubleValue())

    case BigInteger.class: return new BigDecimal(val);
    case BigDecimal.class: return val;
    default:
      def clname = val.getClass().getName()
      throw new Exception("Cannot convert: $clname to Numeric SQL type" )
    }
  }

  Timestamp  makeTime(val)
  {
    if ( val instanceof java.sql.Timestamp ) return val;
    if ( val instanceof java.util.Date ) return new Timestamp(val.getTime())
    def clname = val.getClass().getName()
    throw new Exception("Cannot convert: $clname tp SQL type: DATE/TIMESTAMP")
  }

  // try to make val become an object of type target. Messy but necessary
  // val will always be null,Date,String, or BigDecimal
  Object			coerce(Object val, Class target)
  {
    if ( val==null ) return null;
    Class src = val.getClass();
    if ( src==target ) return val;
    if ( target.isInstance(val) ) return val; // subclass of target

    def str = val.toString();
    switch(target)
    {
    case java.lang.StringBuffer.class: return new StringBuffer(str)
    case java.lang.String.class: return str
    case Integer.class:
    case Long.class:
    case Short.class:
    case short.class:
    case int.class : return val.intValue()

    case Double.class: return new Double(val.doubleValue())
    case Float.class : return new Float(val.floatValue())
    case double.class: return val.doubleValue() as double;
    case float.class: return val.floatValue() as float;

    case java.util.Date.class:
    case java.sql.Date.class:
    case java.sql.Timestamp.class:
      return new java.sql.Date(val.getTime())

    case java.util.Calendar.class:
      def c = Calendar.getInstance()
      c.setTimeInMillis( val.getTime())
      return c;
    }

  	try { // see if the class has a constructor which takes a string the object
  		Class[] types = new Class[1];
      types[0]= java.lang.String.class;
  		def ctor = target.getConstructor(args);
  		return ctor.newInstance( [str].toArray() )
  	}
  	catch( Exception e) { log.error("Error converting class:" +
  	    src.getName() + " to " + target.getName(), e)
  	}
    return val // give up
  }


  // given a name, return a SQL Type code
  int     nameToType(String n)
  {
    switch(n.toLowerCase())
    {
      case "bit": return java.sql.Types.BIT
      case "string" : return java.sql.Types.VARCHAR
      case "int":
      case "integer" : return java.sql.Types.INTEGER
      case "real": return java.sql.Types.FLOAT
      case "float":
      case "double": return java.sql.Types.DOUBLE
      case "decimal" :
      case "numeric": return java.sql.Types.NUMERIC
      case "date" :
      case "timestamp":
      case "time" : return java.sql.Types.TIMESTAMP
      default:
        log.warn( "Unknown type:" + n );
        return -1;
        //throw new Exception("Unkwown SQL data type: $n" )
    }
  }


  // given a java class, return the closest sql type code
  int   classToType( Class cls )
  {
    if ( cls instanceof CharSequence ) return VARCHAR;
    switch(cls)
    {
      case StringBuffer.class:
      case String.class : return VARCHAR;

      case Double.class :
      case double.class: return DOUBLE;
      case Float.class :
      case float.class : return REAL;
      case BigDecimal.class: return NUMERIC;

      case Long.class :
      case Integer.class:
      case Short.class:
      case int.class:
      case short.class:
      case long.class:
      case BigInteger.class : return INTEGER;

      case Boolean.class: return BIT

      case java.util.Date.class:
      case java.sql.Date.class :
      case java.sql.Timestamp.class:
      case Calendar.class :
      case Timestamp.class : return Types.TIMESTAMP;
      default:
        throw new Exception("Bad class->sql conversion:" + cls.getName() )
    }
  }

  String	getTypeName(int type)
  {
    switch(type)
    {
    case BIT : return "BIT"
    case REAL: return "REAL"
    case VARCHAR: return "VARCHAR"
    case SMALLINT: return "SMALLINT";
    case TINYINT: return "TINYINT";
    case INTEGER: return "INTEGER"
    case FLOAT: return "FLOAT"
    case DOUBLE: return "DOUBLE"
    case NUMERIC: return "NUMERIC"
    case DECIMAL: return "DECIMAL"
    case DATE: return "DATE"
    case TIMESTAMP : return "TIMESTAMP"
    }
  }

  def getResultValue( ResultSet rs, col, int typ )
  {
    switch(typ)
    {
      case VARCHAR: return rs.getString(col)
      case BIT:
      case TINYINT:
      case SMALLINT:
      case INTEGER:
      case FLOAT:
      case DOUBLE:
      case DECIMAL:
      case NUMERIC: return rs.getBigDecimal(col); // need to test this with different RDBMS/drivers...

      case DATE:
      case TIME:
      case TIMESTAMP:
        return new java.util.Date( rs.getDate(col).getDate() )
      default:
        throw new Exception("Bad type($typ) specified for column:$col" )
    }
  }

  String properCase(String n) { return n.substring(0,1).toUpperCase() + n.substring(1) }

  Method findMethod( Class cls, String prefix, String name )
  {
    def PUBLIC_BIT = 1 << Member.PUBLIC
    name = prefix + properCase(name);
    Method[] mlist = cls.getMethods()
    Method result = mlist.find { Method m -> return m.getName()==name; }
    if ( result==null ) throw new Exception("Method $name not found in class:" + cls.getName() );
    return result
  }

  void		bindArgs( PreparedStatement ps, List arglist)
  {
    if (arglist==null) return;
    def index =1;
	  arglist.each { a ->
	    log.error("Binding arg: $index, $a" )
	    a.bind( ps, index ); index++;
	  }
  }

  void    close(c)
  {
    if ( c==null ) return;
    try { c.close() } catch(Exception ex){}
  }
}


private class Arg extends Utils
{
  int type
  Object value

  Arg( int i, Object v )
  {
    type =i;
    value = normalize(type,v);
  }

  void bind( PreparedStatement ps, int pos )
  {
    if ( value==null ) ps.setNull( pos, type )
    else switch(type)
  	{
  	case VARCHAR: ps.setString(pos, value.toString()); break;
    case BIT: ps.setBoolean( pos, (value.intValue()!=0) as boolean ); break;
    case SMALLINT:
    case TINYINT: ps.setShort( pos, value.intValue() as short ); break;
  	case INTEGER: ps.setInt(pos, value.intValue() as int );break;
  	case REAL: ps.setFloat(pos, value.floatValue() as float ); break;
  	case FLOAT:
  	case DOUBLE: ps.setDouble(pos, value.doubleValue() as double );break;
  	case DECIMAL:
  	case NUMERIC: ps.setBigDecimal(pos, value); break;
  	case TIMESTAMP:
  	case DATE: ps.setTimestamp( pos, value ); break;
  	default:
  		throw new Exception("Unknown argument type: $type")
  	}
  }

  SimpleDateFormat df = new SimpleDateFormat("yyyyy.MMMMM.dd GGG hh:mm:ss:SSS aaa");

  String toString() {

    if (value==null) return "NULL:" + getTypeName(type);

    if (value instanceof java.util.Date )
      return df.format(value) + ":" + getTypeName(type) +
      		 " type:" + value.getClass().getName()

    return value.toString() + ":" + getTypeName(type) +
    	   " type:" + value.getClass().getName()
  }
}

// A Map of names to arguments
class Binds extends Utils
{
  final Map		args = [:];

  Binds() { super(); }

  Binds(Map m)
  {
    super();
    if ( m != null ) m.each { name, value -> set( name , value ) }
  }

  protected void  clearVars() { args.clear(); }

  private void add( String n, int t, Object v)
  {
    if ( n[0]==':' ) n = n.substring(1)
    assert args.containsKey(n)==false ;
    args[n] =new Arg(t,v);
  }

  void  setNull(String name, int type)    { add(name,type,null) }

  void  set( String name, int type, Object value )
  {
    if ( value==null ) setNull( name, type);
    else add( name, type, value );
  }

  void  set( String name, Object value )
  {
  	if ( value==null ) {
  		log.warning("Null value bound to variable:$name");
  		add( name, VARCHAR, null )
  		return;
    }
    Class cls = value.getClass()
    add( name, classToType(cls), value );
    println "Setting var: $name to " + args[name] ;
  }

  void  set( String name, int n )         {add(name,INTEGER,new Integer(n))}
  void  set( String name, double d )      {add(name,NUMERIC,new Double(d)) }

  void  bind(PreparedStatement ps,String name,int pos)
  {
    Arg a = args.get(name, null)
    if ( a==null ) throw new RuntimeException("SQL Variable $name not found!")
    a.bind( ps, pos );
  }

  String toString()
  {
    StringBuffer result = new StringBuffer();
    int n = 1;
    args.each { name, val -> result.append( "Arg:$n = $val " ); n++ }
    return result.toString();
  }
}



// Mapping a field to a column
class Field extends Utils
{
  boolean	    auto = false;   // auto generated value
  boolean	    key	= false;	  // key field

  Set         validNames = new HashSet(); // Table_name.column or DataDef_name.field_name or just field_name

  String      name;           // java field name
  String      table;          // table name
  String      column;         // column in the database
  int         type = VARCHAR;
  Method      setter;
  Method      getter;

  void configure( DataDefinition dd, Class cls, String tbl, Element elem )
  {
    this.table = tbl;
    auto= ( attrib(elem,'auto','false')=="true" )
    key = ( attrib(elem,'key','false')=='true' )

    this.type= nameToType( attrib(elem,"type", "varchar" ) )
    this.name = attrib(elem,"name")
    this.column = attrib(elem,"column", this.name )

    setter = findMethod( cls, "set", name )
    getter = findMethod( cls, "get", name )

    validNames.add( dd.name + "." + this.name )
    validNames.add( this.table + "." + this.column );
    validNames.add( this.name );
  }

  String		getColumnName()
  {
    if ( column.indexOf(' ')>=0 ) return "[$column]";
    else return column;
  }

  Object    getValue( Object obj )
  {
    final Object[] none = new Object[0];
    if ( obj==null ) throw new NullPointerException("Field.getValue() passed a null object")
    return getter.invoke( obj, none );
  }

  void    store( ResultSet rs, name, Object dest )
  {
    Object val = getResultValue( rs, name, type )
    Class target = setter.getParameterTypes()[0];
    if ( val==null && target.isPrimitive() ) return; // skip null values for primitves
    val = coerce( val, target );
    Object[] args = [val].toArray()

    try {
    	setter.invoke( dest, args )
    } catch(e) {
      log.error("Argument required:" + setter.getParameterTypes()[0]  );
      log.error("Argument passed:" + (val==null ? "null" : val.getClass().getName() ) );
      log.error("Error calling set Method for class:" + val.getClass().getName(), e)

      throw(e)
    }
  }

  public boolean equals(Object o) {
    if ( o==null ) return false;
    return (o instanceof Field) && (o.name==this.name) && (o.column==this.column) && (o.table==this.table);
  }

  String  toString()
  {
    def aval = (auto ? " auto" : "");
    def kval = (key ? " key" : "");
    return "Field(name:$name,column:$column,type:$type$aval$kval)";
  }
}

private class Key
{
  String    field;
  String    foreign;
}

private class Child
{
  boolean   many = false
  String    type ;
  String    name;
  Method    setter;
  List      clauses = [];
  List      keys = []
}

// All the mappings from a database to a Java class, this is the main 'workhorse' of the library
class DataDefinition extends Utils
{
  String      name;
  String      dsource = 'default';
  String      className ;
  Class       cls;
  List        fields = [];        // List of all fields in the order they appear in the XML
  List        selectNames = []    // List of names for select queries, precomputed
  List        joinClauses = [];   // List of conditional clauses, precomputed

  Map         aliases = [:];      // table aliases to avoid name collisions in SELECTs
  Map         byName ;            // Map of name -> Fields
  Map         tables = [:];       // Field.table -> List of Fields
  List        children = [];      // All Child elements in the order they appear

  DataDefinition(String n) { name = n; }

  Connection    getConnection()
  {
    DataSource ds = ORM.getDataSource(dsource);
    if ( ds==null ) throw new Exception("DataSource:$dsource has not been set!!" );
    return ds.getConnection();
  }

  Arg     getArg( Object obj, Field f ) {
    def val = f.getValue(obj)
    return new Arg( f.type, normalize( f.type, val ) )
  }

  List		getArgs(Object obj, List flist)  {
    return flist.collect{ f -> return getArg( obj, f ) }
  }

  private Field findField( String n ) {
    return fields.find { return it.name.equalsIgnoreCase(n) }
  }

  String  getAlias(String tn)    { return aliases.get(tn.toLowerCase(),null); }
  String  getTableAlias(Field f) { return getAlias(f.table); }

  // source name = table_alias.column_name
  String    getSourceName(Field f) {
    return getTableAlias(f) + "." + f.getColumnName();
  }

  // select name = table_alias.column_name as result_name
  String    getSelectName(Field f ) {
    return getSourceName(f) + " AS " + getResultName(f);
  }

  // result name is table_alias_column_name
  String    getResultName(Field f) {
    return getTableAlias(f) + "_" + f.column.replaceAll(' ','_');
  }

  String  toString()
  {
    return "DataDef($name) " + tables.keySet().size() + " tables, and " + fields.size() + " fields ";
  }

  void      configure(Element e )
  {
    this.dsource = attrib(e, 'source', 'default' ).toLowerCase()
    this.className = attrib(e,"class")
    this.cls = Class.forName( className )

    Set seen = new HashSet();
    int  count = 1;
    e.getChildren("table").each {
      Element tbl ->
      String tname = attrib(tbl,"name").toLowerCase();
      log.info( "DataDefinition configured: ${this.name}, Table:$tname" )
      if ( aliases.containsKey(tname) ) throw new Exception("Table $tname is already defined!")
      String alias = 'T' + count;
      aliases[tname] = alias
      count++;

      tbl.getChildren("field").each { Element field_elem ->
        Field f = new Field();
        f.configure( this, cls, tname, field_elem );
        fields.add( f )
        if ( seen.contains( f.column ) ) throw new Exception("Column ${f.column} is already defined in table:${f.table}!");
        seen.add( f.column );
      }
      seen.clear();
    }

    this.tables = fields.groupBy { Field f -> return f.table };

    e.getChildren("child").each{ child ->
      Child temp = new Child()
      temp.type = attrib( child, "type" )
      temp.name = attrib( child, "name" )

      temp.setter = findMethod( this.cls, "set", temp.name );
      if ( temp.setter==null ) throw new Exception("Cannot find set method for child field:" + child.name );
      Class[] types = temp.setter.getParameterTypes();
      if ( types.length!=1 ) throw new Exception("Wrong number of parameter types in method:${dd.classname}.${child.name}" );

      temp.many = attrib( child, "many", "" )=="true";
      child.getChildren("clause").each { cl -> temp.clauses.add( cl.getText() ) }

      child.getChildren("key").each{
        k ->
        Key newkey = new Key();
        newkey.field = attrib( k, "field" )
        newkey.foreign = attrib( k, "foreign", newkey.field )
        temp.keys.add( newkey )
      }
      children.add( temp )
    }

    // we need a Field.Name -> Field[]  mapping
    this.byName = fields.groupBy { f-> return f.name };
    byName.each {
      String name, List items ->
      selectNames.add( getSelectName( items[0] )) // pre build names for SELECT calls

      if ( items.size()<2 ) return; // ignore singular java field -> column

      // now we build join clauses for multi table selects
      eachPair( items ) {
        Field f1, Field f2 ->
        String left = f1.table + '.' + f1.column;
        String right = f2.table + '.' + f2.column;
        joinClauses.add( " $left = $right " );
      }
    }
  }

  // used to build join clauses (above)
  private void   eachPair( List src, Closure cl )
  {
    int pos = 0;
    while( src.size() - pos >= 2 ) {
      cl( src[pos], src[pos+1] );
      pos++;
    }
  }


  Object    create()  { return cls.newInstance(); }

  // INSERT METHODS
  public void insert( Object obj )
  {
    if ( cls.isInstance(obj)==false  ) {
      throw new Exception("Class " + obj.getClass().getName() +
          " does not match <data> class: $classname"  );
    }

    Atomic trans = new Atomic(this);
    tables.each { tname, fields -> trans.insertRow( tname, fields, obj ) }
    trans.execute();
    if ( obj instanceof BaseRecord ) { obj.is_new_record=false;  }
  }

  protected void insertRow( Connection con, String tname, List fields, Object obj )
  {
    def auto = fields.findAll { return it.auto==true };
    if ( auto.size() > 1 ) throw new Exception("Multiple auto generated columns not supported!");

    def callback = { ResultSet rs ->
      showResultSetInfo(rs);
      int pos = 1;
      auto.each { f -> f.store( rs, pos, obj ); pos++ }
    }
    insertRow( con, tname,fields, obj, callback )
  }

  private void showResultSetInfo(ResultSet rs)  // debug function
  {
    def meta = rs.getMetaData()
    int index = 1, max = meta.getColumnCount()
    while( index<= max ) {
      int typ = meta.getColumnType(index)
      def nam = meta.getColumnName(index)
      log.error("Column($index) " + this.getTypeName(typ) + ":$nam" )
      index += 1;
    }
  }

  // inserts a row and calls the closure with any generated keys from the insert
  private void insertRow(Connection con, String tname, List fields, Object obj, Closure funct )
  {
    def writable = fields.findAll { return it.auto==false };                    // no auto fields
    if ( writable.size()==0 ) throw new Exception("Cannot insert into table:$tname, no writable columns" );

    def args = getArgs(obj, writable)                                           // remove NULL arguments?
    def qlist = writable.collect { return "? " }.join(",") 		                  // build parameter list
    def fieldlist = writable.collect{ f-> return f.getColumnName() }.join(",")	// build column list

    String sql = "INSERT INTO $tname( $fieldlist ) values ( $qlist )"
    log.info( "INSERT SQL:\n$sql" )
    def ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    bindArgs(ps, args)
    try {
    	ps.executeUpdate()
    } catch(DataTruncation tr) {
      int pos = tr.getIndex()
      if ( pos<0 ) log.error("Bad truncation index:" + pos)
      else  {
	      String classname = ps.getParameterMetaData().getParameterClassName(pos)
	      int type = ps.getParameterMetaData().getParameterType(pos)
	      def msg = "Error inserting new row for: $t.tablename\n"+
	      			"Column:"+ fields[pos].column + ", Type:" + getTypeName(type) +
	      			", Should be class:" + classname;
	      log.error(msg, tr )
      }
      throw(tr)
    }
    ResultSet rs = ps.getGeneratedKeys();
    if ( rs.next() ) funct( rs ); // shouldnt be more than one record....
    close(rs)
    close(ps)
  }

  // DELETE METHODS
  public void delete(Object obj)
  {
    if ( cls.isInstance(obj)==false ) {
      throw new Exception("Class " + obj.getClass().getName() +
          " does not match <data> class:" + cls.getName() );
    }

    Atomic trans = new Atomic( this );
    tables.each { tname, fields -> trans.deleteRow( tname, fields, obj ) }
    trans.execute();
    if ( obj instanceof BaseRecord ) { obj.is_new_record=true; }
  }

  private void      deleteRow( Connection con, String tname, List fields, Object obj )
  {
    List    keys  = fields.findAll{ f -> return f.key==true }
    String  sql   = "DELETE FROM $tname WHERE " +
     			          keys.collect{ return it.getColumnName() +"=?" }.join(" AND ")
	  List    args  = getArgs(obj,keys)
	  log.info( "DELETE SQL:\n$sql" )
	  PreparedStatement ps = con.prepareStatement(sql)
	  bindArgs(ps,args)
	  ps.executeUpdate()
	  close(ps)
  }


  // UPDATE METHODS
  public void update(Object obj)
  {
    if ( cls.isInstance(obj)==false ) {
      throw new Exception("Class " + obj.getClass().getName() +
          " does not match <data> class:" + cls.getName() );
    }

    Atomic trans = new Atomic(this);
    tables.each { tname, fields -> trans.updateRow( tname, fields, obj ); }
    trans.execute();

    if ( obj instanceof BaseRecord ) { obj.is_new_record=false }
  }


  private void updateRow(Connection con, String tname, List fields, Object obj )
  {
    // get writeable fields
    def writable = fields.findAll { return !(it.auto || it.key ) }

    // get write-able values
    def args = getArgs(obj,writable)

    // get key fields
    def keys = fields.findAll { return it.key==true }
    if ( keys.size()<1 ) throw new Exception("Cannot update table:$name without primary key")

    // get keyfield values
    args.addAll( getArgs(obj,keys) )

    // build update statement
    def assign = writable.collect{ f -> return f.getColumnName() + "=?" }

    def sql = "UPDATE $tname set " + assign.join(", ") +
    			" WHERE " +
		      keys.collect{ f -> return f.getColumnName() + "=?"}.join(", ")

    log.info( "UPDATE SQL:\n$sql\nArgs:" + args.collect { return it.toString() }.join( "\n") )

    def ps = con.prepareStatement( sql )
    bindArgs(ps, args)
    ps.executeUpdate()
    close(ps)
  }

  // CHILD METHODS
  // Load all the children of this data definition, setting the Java field(s) to the result
  private void  loadAllChildren(Object dest)
  {
    children.each { child ->
      def dd = ORM.getDefinition(child.type);
      List items = dd.findChildrenOf( this, dest );
      def param ;
      if ( child.many ) param = [ items ].toArray() ;
      else param = [ items[0] ].toArray();
      child.setter.invoke( dest, param );
      log.info( "Loaded " + items.size() + " items for child:" + child.name + " of type:" + child.type );
    }
  }

  // find children of the passed object using the child keys in the passed data definition
  private List   findChildrenOf( DataDefinition parent, Object obj )
  {
    Child ch = parent.children.find { return it.type==this.name }
    if ( ch==null ) throw new Exception("No <child> element found for type:$name in DataDefinition:" + dd.name );
    Query q = new Query( this );
    int count = 1;
    ch.keys.each { k ->
      Field left = this.findField( k.foreign ); // our field is foreign to the other data definition
      if ( left==null ) throw new Exception("Could not find field ${k.foreign} in Def:$name");
      Field right = parent.findField( k.field );
      if ( right==null ) throw new Exception("Could not find field ${k.field} in Def:${dd.name}");
      String colName = left.table + "." + left.column ;
      def val = right.getValue( obj )
      if ( val==null ) {
        q.where( colName + " is null ");
      }  else {
        String myvar = ":autovar" + count;
        q.where( colName + " = " + myvar );
        q.set( myvar, val );
        count++;
      }
    }
    return q.all();
  }

  // delete all children of the passed Object, ensuring atomicity
  private void    deleteAllChildren( Object obj )
  {
    List connections = []
    def rollback = { connections.each { con -> con.rollback() } }
    def commit   = { connections.each { con -> con.commit() } }
    def close_all = { connections.each{ con -> close(con) } connections.clear(); }

    try {
      children.each { Child child ->
        def dd = getDefinition(child.type)
        def con = dd.getConnection();
        con.setAutoCommit(false);
        connections.add( con )
        deleteChildrenOf( con, this, obj );
      }
      commit();
    }
    catch( SQLException ex ) { rollback(); throw (ex) }
    finally { close_all(); }
  }

  // delete all children of the passed object, using the passed parent data definition
  private int     deleteChildrenOf( DataDefinition parent, Object obj )
  {
    Connection con = getConnection();
    con.setAutoCommit(false);
    try {
      deleteChildrenOf( con, parent, obj );
      con.commit();
    }
    catch( SQLException ex ) { con.rollback(); log.error("ERROR", ex ); throw(ex) }
    finally { close(con) }
  }

  // delete all children of the passed object, using the passed parent data definition and connection
  private int     deleteChildrenOf( Connection con, DataDefinition parent, Object obj )
  {
    int result = 0;
    tables.each { tname, fields ->
      List clauses = []
      List args = []
      Child ch = parent.children.find { return it.type==this.name };
      if ( ch==null ) return;
      ch.keys.each { k ->
        Field f = parent.findField( k.field )
        def val = f.getValue( obj )
        String colName = f.table + "." + f.column;
        if ( val==null ) {
          throw new Exception("Cannot delete children with a null foreign key!")
          clauses.add( colName + " is null " );
        } else {
          clauses.add( colName + "=?" )
          args.add( getArg( obj, f ) )
        }
      }
      if ( clauses.size()<1 ) return;
      String sql = "DELETE FROM $tname WHERE " + clauses.join(" AND ")
      println "DELETE CHILD SQL:\n$sql "
      args.each{ println "ARG:" + it.toString() }
      try {
        def ps = con.prepareStatement( sql );
        bindArgs( ps, args );
        result = ps.executeUpdate();
      }
      finally { close(ps) }
    }
    return result;
  }
}

// main interface, uses static resources to hold definitions and data sources
// not very OOPish, but shouldn't cause any problems either
class ORM
{
  static Map          data_sources = [:]
  static Map          definitions =  [:]  // name|className -> definition
  static DataSource   default_source = null;

  public static void setDataSource(DataSource ds) { setDataSource('default',ds); }
  public static void setDataSource(String name, DataSource ds)
  {
    data_sources[name] = ds;
  }

  public static DataSource getDataSource(String name) { return ORM.data_sources.get(name,null); }
  public static DataSource getDataSource() { return ORM.data_sources.get('default',null); }

  public static void loadFile(String path) throws FileNotFoundException
  {
    File f = new File(path);
    if ( !f.exists() )  throw new FileNotFoundException("File:" + path + " was not found");
    InputStream src = new FileInputStream(f);
    loadXML(src)
  }

  public static void loadResource(String path) throws IOException
  {
    InputStream src = ORM.getResourceAsStream(path);
    if ( src==null ) throw new IOException("Resource:" + path + " was not found in the classpath");
    loadXML(src);
  }

  private static void loadXML( InputStream src )
  {
    try {
      def builder = new org.jdom.input.SAXBuilder();
      def doc = builder.build(src);
      def root = doc.getRootElement() ;
      root.getChildren( "data" ).each {
        data ->
        String name = data.getAttributeValue("name")
        if(name==null ) throw new Exception("Data definitions must have a name attribute!");
        DataDefinition cur = definitions.get( name );
        if ( cur!=null ) throw new Exception("Data definition already specified for $name");
        cur = new DataDefinition(name)
        cur.configure( data )
        definitions.put( name, cur )
      }
    }
    catch( Exception e ) { Utils.log.error("Error loading mapping XML!", e) ; }
    finally { src.close() }
  }

  static DataDefinition   getDefinition(Class c)
  {
    List result = definitions.values().findAll { return it.cls == c };
    if ( result.isEmpty() ) throw new Exception("Could not find <data> definition for class:" + c.getName() );
    if ( result.size()>1 ) throw new Exception("Multiple <data> definitions found for class:" + c.getName() );
    return (DataDefinition)result[0];
  }

  static DataDefinition   getDefinition(String n)
  {
    Object result = definitions.get(n, null);
    if ( result != null ) return (DataDefinition)result
    throw new Exception("Could not find <data> definition for $n, check mapping XML")
  }

  public static Object 	create(String n) { return getDefinition(n).create(); }


  public static void delete(String name, Object obj)
  {
    getDefinition(name).delete( obj );
  }

  public static void delete(Object obj)
  {
    Class cls = obj.getClass()
    getDefinition(cls).delete( obj );
  }

  public static void  update(String name, Object obj)
  {
    getDefinition(name).update( obj );
  }

  public static void	update(Object obj)
  {
    Class cls = obj.getClass()
	  getDefinition(cls).update(obj);
  }

  public static void insert( String name, Object obj )
  {
    getDefinition(name).insert( obj );
  }

  public static void insert( Object obj )
  {
    Class cls = obj.getClass();
    getDefinition(cls).insert( obj );
  }

  public static List      findAll( String name )  { return new Query(getDefinition(name)).all();  }
  public static List      findAll( Class cls )    { return new Query(getDefinition(cls)).all();   }

  // search for specific results within a set of tables(conditions,range, limit)
  public static Query     find( Class cls )       {  return new Query( getDefinition(cls) ); }
  public static Query     find( String name )     {  return new Query( getDefinition(name) ) }
}

// helper class for Spring
class ORMConfig
{
  public void setDataSources( Map m ) { ORM.data_sources.putAll( m ) }

  public void setResourceFiles(List files)
  {
    files.each { val ->
      try { ORM.loadResource( val ) } catch( ex ) { LogFactory.getLog(this.getClass()).error("Error loading Mapping file $val", ex ) }
    }
  }

  public void setMappingFiles(List files)
  {
    files.each { val ->
      try { ORM.loadFile( val ) } catch( ex ) { LogFactory.getLog(this.getClass()).error("Error loading Mapping file $val", ex ) }
    }
  }
}

// helper class to handle groups of Atomic database update transactions
private class Atomic extends Utils
{
  DataDefinition  parent ;
  List            actions = [];

  protected Atomic( DataDefinition dd ) { this.parent = dd; }

  void execute()
  {
    Connection con = parent.getConnection();
    try {
      con.setAutoCommit(false);
      actions.each { Closure action -> action( con ); }
      con.commit();
    }
    catch( Exception ex ) {
      con.rollback();
      log.error("Transaction Error", ex ); throw(ex)
    }
    finally { if ( con!= null ) con.close() ; }
  }

  void  insertRow( String tname, List fields, Object obj ) {
    def callback = { Connection con -> parent.insertRow( con, tname, fields, obj ) }
    actions.add( callback );
  }

  void  deleteRow( String tname, List fields, Object obj ) {
    def callback = { Connection con -> parent.deleteRow( con, tname, fields, obj ) }
    actions.add( callback );
  }

  void  updateRow( String tname, List fields, Object obj ) {
    def callback = { Connection con -> parent.updateRow( con, tname, fields, obj ); }
    actions.add( callback );
  }
}

// helper class to iterate through results and get specific rowsets
class Query extends Binds
{
  String            orderByClause = null;
  String            groupByClause = null;
  List              clauses = [];
  DataDefinition    parent;

  public Query(String name) { parent = ORM.getDefinition(name); }
  public Query(Class cls )  { parent = ORM.getDefinition(cls);  }

  private Query(DataDefinition dd ) { parent = dd; }

  public  void clearConditions()  { clauses.clear(); }

  public  void    orderBy(String fields) { orderByClause = rewriteColumns( fields ); }
  public  void    groupBy(String fields) { groupByClause = rewriteColumns( fields ); }

  public Query    where( String cond ) { return where(cond, null); }
  public Query    where( String cond, Map arglist )
  {
    if (( cond.indexOf("'")>=0 )|| (cond.indexOf('"')>=0) )
        throw new Exception("String literals inside WHERE clauses are not supported."+
							"Use a variable binding instead.");

    clauses.add( cond );
    if (arglist!=null) arglist.each { name, val -> this.set( name, val ); }
    return this
  }


  private void    doSelect( String sql, List arglist, Closure callback )
  {
    Connection con = parent.getConnection();
    PreparedStatement ps ;
    ResultSet rs;
    try  {
      ps = con.prepareStatement( sql )
      bindArgs( ps, arglist );
      rs = ps.executeQuery();
      callback( rs );
    }
    finally {
      close(rs);
      close(ps);
      close(con);
    }
  }

  private Object  createObject(ResultSet rs)
  {
    Object result = parent.create();
    parent.byName.each { String name, List items ->
      Field f = items[0];
      f.store( rs, parent.getResultName(f), result );
    }
    return result;
  }


  public List     all()
  {
    List result = []
    List arglist = []
    String sql = buildSQL(arglist);
    doSelect( sql, arglist ) {
      ResultSet rs ->
      while( rs.next() ) { result.add( createObject(rs) ); }
    }
    return result;
  }

  public List     first( int count )
  {
    if ( count<1 ) return []

    List arglist = []
    List result = []
    String sql = buildSQL(arglist);
    doSelect( sql, arglist ) {
      ResultSet rs ->
      while( count>0 && rs.next() ) { result.add( createObject(rs) ); count--;  }
    }
    return result;
  }

  public Object     first()
  {
    List r = first(1);
    return r.isEmpty() ? null : r.get(0);
  }

  public List     from( int start, int end )
  {
    if ( end<start ) return []
    List result = []
    List arglist = []
    String sql = buildSQL(arglist)

    doSelect( sql, arglist ) {
      ResultSet rs ->
      rs.absolute( start );
      while( start<=end && rs.next() ) { result.add( createObject(rs) ) ; start++; }
    }
    return result;
  }

  private String    buildSQL(List arglist)
  {
    def tnames = []   // get the table names
    parent.tables.each { tname, fields ->
      tnames.add( tname + " AS " + parent.getAlias(tname) );
    }

    String orderClause =  ""
    if ( !empty(orderByClause) )  orderClause = " ORDER BY $orderByClause ";

    String groupClause =  ""
    if ( !empty(groupByClause) ) groupClause = " GROUP BY $groupByClause ";

    String result = "SELECT " + parent.selectNames.join(",") +
                    " FROM " + tnames.join( ", " ) +
                    getWhereClause( clauses + parent.joinClauses, arglist ) +
                    orderClause +
                    groupClause ;

    log.error( "Query SQL:\n" + result.replaceAll( "[,]", ",\n" ) )
    return  result ;
  }

  String    getWhereClause(List items, List arglist)
  {
    if ( items.size()<1 ) return "";
    String text = items.join( " AND " );
    //println "WHERE CLAUES:\n$text";
    text = parseVars( text, arglist );
    text = rewriteColumns( text );
    return " WHERE $text " ;
  }

  private String parseVars( String text, List arglist )
  {
    final Pattern var_pattern = Pattern.compile("[:]\\w+");
    Matcher m = var_pattern.matcher(text.toLowerCase());

    StringBuffer result = new StringBuffer();
    int base = 0;

    while( m.find() )
    {
      String prefix = text.substring( base, m.start() );
      result.append( prefix )

      String name = text.substring( m.start()+1, m.end() );
      Object val = this.args[name]
      println "VARIABLE($name) = " + val;
      arglist.add( val )
      result.append( " ? ");
      base = m.end();
    }

    result.append( text.substring(base) );
    return result.toString();
  }

  private String  rewriteColumns( String text )
  {
    final Pattern name_pattern = Pattern.compile( "[:]?(\\w+[.])?\\w+" );

    StringBuffer result = new StringBuffer();

    int base = 0;
    Matcher m = name_pattern.matcher(text.toLowerCase());
    while( m.find() )
    {
      result.append( ' ' );
      if ( m.start()>=base ) result.append( text.substring( base, m.start() ) );
      String word = text.substring( m.start(), m.end() )
      if ( word.charAt(0)==':' ) result.append( word )
      else {
        word = word.toLowerCase()
        List items = parent.fields.findAll { f -> return f.validNames.contains(word) };
        if ( items.size()>1 ) throw new Exception( "Ambiguous column reference: $word found in clause:'$text'" );
        if ( items.size()==0 ) result.append( word );
        if ( items.size()==1 ) {
          Field f = items[0];
          result.append( parent.getTableAlias(f) + "." + f.getColumnName() );
        }
      }
      base = m.end() + 1 ;
    }
    if ( base < text.length() ) result.append( text.substring(base) );
    return result.toString();
  }
}