/*

  * $Source$

  * $Revision$

  *

  * Copyright (C) 2000 David Warnock

  * 

  * Part of Melati (http://melati.org), a framework for the rapid

  * development of clean, maintainable web applications.

  *

  * Melati is free software; Permission is granted to copy, distribute

  * and/or modify this software under the terms either:

  *

  * a) the GNU General Public License as published by the Free Software

  *    Foundation; either version 2 of the License, or (at your option)

  *    any later version,

  *

  *    or

  *

  * b) any version of the Melati Software License, as published

  *    at http://melati.org

  *

  * You should have received a copy of the GNU General Public License and

  * the Melati Software License along with this program;

  * if not, write to the Free Software Foundation, Inc.,

  * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA to obtain the

  * GNU General Public License and visit http://melati.org to obtain the

  * Melati Software License.

  *

  * Feel free to contact the Developers of Melati (http://melati.org),

  * if you would like to work out a different arrangement than the options

  * outlined here.  It is our intention to allow Melati to be used by as

  * wide an audience as possible.

  *

  * This program is distributed in the hope that it will be useful,

  * but WITHOUT ANY WARRANTY; without even the implied warranty of

  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

  * GNU General Public License for more details.

  *

  * Contact details for copyright holder:

  *

  *     David Warnock (david At sundayta.co.uk)

  *     Sundayta Ltd

  *     International House, 

  *     174 Three Bridges Road, 

  *     Crawley, West Sussex 

  *     RH10 1LE, UK

  *

  */

 package org.melati.poem.dbms;

 import java.sql.DatabaseMetaData;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import org.melati.poem.BinaryPoemType;

 import org.melati.poem.BooleanPoemType;

 import org.melati.poem.Column;

 import org.melati.poem.DatePoemType;

 import org.melati.poem.DoublePoemType;

 import org.melati.poem.PoemType;

 import org.melati.poem.SQLPoemType;

 import org.melati.poem.SQLType;

 import org.melati.poem.StringPoemType;

 import org.melati.poem.TimestampPoemType;

 import org.melati.poem.util.StringUtils;

 /**

  * A Driver for the Microsoft SQL server.

  */

 public class SQLServer extends AnsiStandard {

   /**

    * SQL Server does not have a pleasant <code>TEXT</code> datatype, so we use

    * an arbetary value in a <code>VARCHAR</code>.

    */

   public static final int sqlServerTextHack = 2333;

   /**

    * Maximum size of a binary field.

    */

   public static final int sqlServerMaxBinarySize = 2147483647;

   /**

    * Constructor.

    */

   public SQLServer() {

     // buggy

     // setDriverClassName("com.merant.datadirect.jdbc.sqlserver.SQLServerDriver");

     // setDriverClassName("sun.jdbc.odbc.JdbcOdbcDriver"); //does not work

     // setDriverClassName("com.ashna.jturbo.driver.Driver"); //works

     // setDriverClassName("com.jnetdirect.jsql.JSQLDriver"); //works

     // 2003

     // does not return indices without schema name ?

     // setDriverClassName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

     // FreeTDS driver has many unimplemented features and => does not work.

     // Works with 2003

     // setDriverClassName("com.inet.tds.TdsDriver");

     // Not working

     //setDriverClassName("easysoft.sql.jobDriver"); 

     // 2005

     setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

   }

   /**

    * Get the user we are connected as and return that as the schema.

    *

    * @see org.melati.poem.dbms.Dbms#getSchema()

    * @see org.melati.poem.dbms.AnsiStandard#getSchema()

    */

   public String getSchema() {

     return null;

   }

   // Commented out as PMD objects to over riding method which only call

   // super.

   // public String getQuotedName(String name) {

   // if you don't want to set 'use ANSI quoted identifiers' database property

   // to 'true' (on SQL Server)

   /*

    * if(name.equalsIgnoreCase("nullable")) return "\"" + name+"\"";

    * if(name.equalsIgnoreCase("unique")) return "\"" + name+"\"";

    * if(name.equalsIgnoreCase("user")) return "q" + name;

    * if(name.equalsIgnoreCase("group")) return "q" + name; return name;

    */

   // if you already set 'use ANSI quoted identifiers' property to 'true'

   // return super.getQuotedName(name);

   // }

   /**

    * {@inheritDoc}

    * 

    * @see org.melati.poem.dbms.AnsiStandard#getSqlDefinition(java.lang.String)

    */

   public String getSqlDefinition(String sqlTypeName) {

     if (sqlTypeName.equals("BOOLEAN")) {

       return ("BIT");

     }

     if (sqlTypeName.equals("DATE")) {

       return ("DATETIME");

     }

     if (sqlTypeName.equals("TIMESTAMP")) {

       return ("DATETIME");

     }

     return super.getSqlDefinition(sqlTypeName);

   }

   /**

    * {@inheritDoc}

    * 

    * @see org.melati.poem.dbms.AnsiStandard#getStringSqlDefinition(int)

    */

   public String getStringSqlDefinition(int size) throws SQLException {

     if (size < 0) { // Don't use TEXT as it doesn't support

       // indexing or comparison

       return "VARCHAR(" + sqlServerTextHack + ")";

     }

     return super.getStringSqlDefinition(size);

   }

   /**

    * Translates a MSSQL String into a Poem <code>StringPoemType</code>.

    */

   //public static class SQLServerStringPoemType extends StringPoemType {

     /**

      * Constructor.

      * 

      * @param nullable

      *          nullability

      * @param size

      *          length

      */

     //public SQLServerStringPoemType(boolean nullable, int size) {

     //  super(nullable, size);

     //}

     // MSSQL returns metadata info size 2147483647 for its TEXT type

     // We set size to sqlServerTextHack for our Text type

     //protected boolean _canRepresent(SQLPoemType other) {

     //  return (getSize() < 0 || getSize() == 2147483647

     //          || getSize() == sqlServerTextHack || getSize() >= ((StringPoemType)other)

     //          .getSize());

     //}

     /**

      * {@inheritDoc}

      * 

      * @see org.melati.poem.BasePoemType#canRepresent(PoemType)

      */

     /*

      * public PoemType canRepresent(PoemType other) { return other instanceof

      * StringPoemType && _canRepresent((StringPoemType) other) &&

      * !(!getNullable() && ((StringPoemType) other).getNullable()) ? other :

      * null; }

      */

   //}

   /**

    * Accomodate our String size hack. {@inheritDoc}

    * 

    * @see org.melati.poem.dbms.AnsiStandard#canRepresent

    */

   public <S,O>PoemType<O> canRepresent(PoemType<S> storage, PoemType<O> type) {

     if (storage instanceof StringPoemType && type instanceof StringPoemType) {

       if (((StringPoemType)storage).getSize() == sqlServerTextHack

               && ((StringPoemType)type).getSize() == -1

               && !(!storage.getNullable() && type.getNullable())) {

         return type;

       } else {

         return storage.canRepresent(type);

       }

     } else if (storage instanceof BinaryPoemType

             && type instanceof BinaryPoemType) {

       if (((BinaryPoemType)storage).getSize() == sqlServerMaxBinarySize

               && ((BinaryPoemType)type).getSize() == -1

               && !(!storage.getNullable() && type.getNullable())) {

         return type;

       } else {

         return storage.canRepresent(type);

       }

     } else if (storage instanceof DatePoemType

             && type instanceof TimestampPoemType) {

       if (!(!storage.getNullable() && type.getNullable())) {

         return type;

       } else {

         return storage.canRepresent(type);

       }

     } else {

       return super.canRepresent(storage, type);

     }

   }

   /**

    * Translates a MSSQL Date into a Poem <code>DatePoemType</code>.

    */

  // public static class SQLServerDatePoemType extends DatePoemType {

     /**

      * Constructor.

      * 

      * @param nullable

      *          nullability

      */

    // public SQLServerDatePoemType(boolean nullable) {

    //   super(Types.DATE, "DATETIME", nullable);

    // }

    // protected boolean _canRepresent(SQLPoemType other) {

    //   return other instanceof DatePoemType

    //           || other instanceof TimestampPoemType;

    // }

  // }

   /**

    * Translates a MSSQL Date into a Poem <code>TimestampPoemType</code>.

    */

   //public static class SQLServerTimestampPoemType extends TimestampPoemType {

     /**

      * Constructor.

      * 

      * @param nullable

      *          nullability

      */

     //public SQLServerTimestampPoemType(boolean nullable) {

     //  super(Types.TIMESTAMP, "DATETIME", nullable);

     //}

   //}

   /**

    * {@inheritDoc}

    * 

    * @see org.melati.poem.dbms.AnsiStandard#sqlBooleanValueOfRaw(java.lang.Object)

    */

   public String sqlBooleanValueOfRaw(Object raw) {

     if (((Boolean)raw).booleanValue())

       return "1";

     else

       return "0";

   }

   /**

    * Translates an SQLServer Boolean into a Poem <code>BooleanPoemType</code>.

    */

   public static class SQLServerBooleanPoemType extends BooleanPoemType {

     /**

      * Constructor.

      * 

      * @param nullable

      *          nullability

      */

     public SQLServerBooleanPoemType(boolean nullable) {

       super(nullable);

     }

     protected Boolean _getRaw(ResultSet rs, int col) throws SQLException {

       synchronized (rs) {

         boolean v = rs.getBoolean(col);

         return rs.wasNull() ? null : (v ? Boolean.TRUE : Boolean.FALSE);

       }

     }

     protected void _setRaw(PreparedStatement ps, int col, Object bool)

             throws SQLException {

       ps.setInt(col, ((Boolean)bool).booleanValue() ? 1 : 0);

     }

   }

   /**

    * {@inheritDoc}

    * 

    * @see org.melati.poem.dbms.Dbms#getLongSqlDefinition()

    */

   public String getLongSqlDefinition() {

     return "BIGINT";

   }

   /**

    * {@inheritDoc}

    * 

    * @see org.melati.poem.dbms.Dbms#getBinarySqlDefinition(int)

    */

   public String getBinarySqlDefinition(int size) throws SQLException {

     if (size < 0)

       return "VARBINARY(MAX)";

     return "VARBINARY(" + size + ")";

   }

   /**

    * {@inheritDoc}

    * 

    * @see org.melati.poem.dbms.AnsiStandard#defaultPoemTypeOfColumnMetaData(

    *      java.sql.ResultSet)

    */

   public SQLPoemType<?> defaultPoemTypeOfColumnMetaData(ResultSet md)

           throws SQLException {

     /*

     ResultSetMetaData rsmd = md.getMetaData();

     int cols = rsmd.getColumnCount();

     for (int i = 1; i <= cols; i++) {

       String table = rsmd.getTableName(i);

       System.err.println("table name: " + table);

       String column = rsmd.getColumnName(i);

       System.err.println("column name: " + column);

       int type = rsmd.getColumnType(i);

       System.err.println("type: " + type);

       String typeName = rsmd.getColumnTypeName(i);

       System.err.println("type Name: " + typeName);

       String className = rsmd.getColumnClassName(i);

       System.err.println("class Name: " + className);

       System.err.println("String val: " + md.getString(i));

       System.err.println("");

     }

     */

     // Not used in Poem 

     //if (md.getString("TYPE_NAME").equals("text"))

     //  return new SQLServerStringPoemType(

     //          md.getInt("NULLABLE") == DatabaseMetaData.columnNullable, md

     //                  .getInt("COLUMN_SIZE"));

     // We use a magic number for text fields

     if (md.getString("TYPE_NAME").equals("varchar")

             && md.getInt("COLUMN_SIZE") == sqlServerTextHack)

       return new StringPoemType(

               md.getInt("NULLABLE") == DatabaseMetaData.columnNullable, -1);

     // Not used in Poem 

     if (md.getString("TYPE_NAME").equals("char"))

       return new StringPoemType(

               md.getInt("NULLABLE") == DatabaseMetaData.columnNullable, md

                       .getInt("COLUMN_SIZE"));

     if (md.getString("TYPE_NAME").equals("float"))

       return new DoublePoemType(

               md.getInt("NULLABLE") == DatabaseMetaData.columnNullable);

     if (md.getString("TYPE_NAME").equals("datetime"))

       return new DatePoemType(

               md.getInt("NULLABLE") == DatabaseMetaData.columnNullable);

     if (md.getString("TYPE_NAME").equals("bit"))

       return new SQLServerBooleanPoemType(

               md.getInt("NULLABLE") == DatabaseMetaData.columnNullable);

     /*

      * // MSSQL returns type -2 (BINARY) not 93 (TIMESTAMP) 

      * // They don't mean what we mean by timestamp

      * // They mean a one-per-record record creation timestamp  

      * if( md.getString("TYPE_NAME").equals("timestamp"))

      *   return new TimestampPoemType(md.getInt("NULLABLE")== DatabaseMetaData.columnNullable);

      */

     return super.defaultPoemTypeOfColumnMetaData(md);

   }

   /**

    * Ignore <TT>dtproperties</TT> as it is a 'System' table used to store

    * Entity Relationship diagrams which have a jdbc type of TABLE when it should

    * probably have a jdbc type of 'SYSTEM TABLE'. 

    * 

    * {@inheritDoc}

    * @see org.melati.poem.dbms.AnsiStandard#melatiName(java.lang.String)

    */

   public String melatiName(String name) {

     if (name == null)

       return null;

     if (name.equalsIgnoreCase("dtproperties"))

       return null;

     return name;

   }

   /**

    * MSSQL cannot index TEXT fields. Probably means that if you are serious

    * about using MSSQL you should use a varchar.

    * 

    * If a field is defined as Text in the DSD we use a VARCHAR. Not sure what

    * happens if a legacy db really uses TEXT.

    * 

    * @return whether it is allowed.

    */

   public boolean canBeIndexed(Column<?> column) {

     PoemType<?> t = column.getType();

     if (t instanceof StringPoemType && ((StringPoemType)t).getSize() < 0)

       return false;

     return true;

   }

   /**

    * {@inheritDoc}

    * 

    * @see org.melati.poem.dbms.AnsiStandard#caseInsensitiveRegExpSQL

    */

   public String caseInsensitiveRegExpSQL(String term1, String term2) {

     if (StringUtils.isQuoted(term2)) {

       term2 = term2.substring(1, term2.length() - 1);

     }

     term2 = StringUtils.quoted(StringUtils.quoted(term2, '%'), '\'');

     return term1 + " LIKE " + term2;

   }

   /**

    * Slightly different - embrace and extend.

    */

   public String getForeignKeyDefinition(String tableName, String fieldName,

           String targetTableName, String targetTableFieldName, String fixName) {

     StringBuffer sb = new StringBuffer();

     sb.append(" ADD FOREIGN KEY (" + getQuotedName(fieldName) + ") REFERENCES "

             + getQuotedName(targetTableName) + "("

             + getQuotedName(targetTableFieldName) + ")");

     if (fixName.equals("prevent"))

       sb.append(" ON DELETE NO ACTION");

     if (fixName.equals("delete"))

       sb.append(" ON DELETE CASCADE");

     if (fixName.equals("clear"))

       sb.append(" ON DELETE SET NULL");

     return sb.toString();

   }

   /**

    * Accommodate SQLServer syntax.

    */

   public String alterColumnNotNullableSQL(String tableName, Column<?> column) {

     return "ALTER TABLE " + getQuotedName(tableName) + " ALTER COLUMN "

             + getQuotedName(column.getName()) + " "

             + column.getSQLType().sqlDefinition(this);

   }

   /**

    * {@inheritDoc}

    * 

    * @see org.melati.poem.dbms.Dbms#selectLimit(java.lang.String, int)

    */

   public String selectLimit(String querySelection, int limit) {

     return "SELECT TOP " + limit + querySelection;

   }

   /**

    * {@inheritDoc}

    * @see org.melati.poem.dbms.Dbms#booleanTrueExpression(org.melati.poem.Column)

    */

   public String booleanTrueExpression(Column<Boolean> booleanColumn) {

     return booleanColumn.fullQuotedName() + "=1";

   }

   /**

    * {@inheritDoc}

    * @see org.melati.poem.dbms.AnsiStandard#getSqlDefaultValue(org.melati.poem.SQLType)

    */

   public String getSqlDefaultValue(SQLType<?> sqlType) {

     if (sqlType instanceof BooleanPoemType) {

       return ("0");

     }

     if (sqlType instanceof BinaryPoemType) {

       return "convert(varbinary, '')";

     }

     return super.getSqlDefaultValue(sqlType);

   }

   public String getQuotedValue(SQLType<?> sqlType, String value) {

     if (sqlType instanceof BinaryPoemType) {

       return value;

     }

     return super.getQuotedValue(sqlType, value);

   }  

 }