title: Dialect Schema for SQLite and Hibernate
body: <p>I am trying to use Hibernate with SQLite. </p>
  
  <p>But I get with others the following error:</p>
  
  <pre><code>ERROR: HHH000299: Could not complete schema update
  java.lang.UnsupportedOperationException: No add column syntax supported by SQLiteDialect
      at org.hibernate.dialect.Dialect.getAddColumnString(Dialect.java:1850)
      at org.hibernate.mapping.Table.sqlAlterStrings(Table.java:390)
      at org.hibernate.cfg.Configuration.generateSchemaUpdateScriptList(Configuration.java:1180)
      at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:237)
      at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:203)
      at org.hibernate.internal.SessionFactoryImpl.&lt;init&gt;(SessionFactoryImpl.java:509)
      at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1799)
      at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1870)
      at TestInsert.main(TestInsert.java:21)
  </code></pre>
  
  <p>Obviously SQLite is not supported by default, but with correct dialect definitions I guess it should work. I already searched about several Dialects and found the following, which works the best so far, but there are some problems as the one I described:</p>
  
  <pre><code>/*
   * The author disclaims copyright to this source code.  In place of
   * a legal notice, here is a blessing:
   *
   *    May you do good and not evil.
   *    May you find forgiveness for yourself and forgive others.
   *    May you share freely, never taking more than you give.
   *
   */
  
  import java.sql.Types;
  import org.hibernate.dialect.Dialect;
  
  import org.hibernate.dialect.function.AbstractAnsiTrimEmulationFunction;
  import org.hibernate.dialect.function.NoArgSQLFunction;
  import org.hibernate.dialect.function.SQLFunction;
  import org.hibernate.dialect.function.SQLFunctionTemplate;
  import org.hibernate.dialect.function.StandardSQLFunction;
  import org.hibernate.dialect.function.VarArgsSQLFunction;
  import org.hibernate.type.StandardBasicTypes;
  
  public class SQLiteDialect extends Dialect {
    public SQLiteDialect() {
      registerColumnType(Types.BIT, "boolean");
      registerColumnType(Types.TINYINT, "tinyint");
      registerColumnType(Types.SMALLINT, "smallint");
      registerColumnType(Types.INTEGER, "integer");
      registerColumnType(Types.BIGINT, "bigint");
      registerColumnType(Types.FLOAT, "float");
      registerColumnType(Types.REAL, "real");
      registerColumnType(Types.DOUBLE, "double");
      registerColumnType(Types.NUMERIC, "numeric($p, $s)");
      registerColumnType(Types.DECIMAL, "decimal");
      registerColumnType(Types.CHAR, "char");
      registerColumnType(Types.VARCHAR, "varchar($l)");
      registerColumnType(Types.LONGVARCHAR, "longvarchar");
      registerColumnType(Types.DATE, "date");
      registerColumnType(Types.TIME, "time");
      registerColumnType(Types.TIMESTAMP, "datetime");
      registerColumnType(Types.BINARY, "blob");
      registerColumnType(Types.VARBINARY, "blob");
      registerColumnType(Types.LONGVARBINARY, "blob");
      registerColumnType(Types.BLOB, "blob");
      registerColumnType(Types.CLOB, "clob");
      registerColumnType(Types.BOOLEAN, "boolean");
  
      //registerFunction( "abs", new StandardSQLFunction("abs") );
      registerFunction( "concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "", "||", "") );
      //registerFunction( "length", new StandardSQLFunction("length", StandardBasicTypes.LONG) );
      //registerFunction( "lower", new StandardSQLFunction("lower") );
      registerFunction( "mod", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "?1 % ?2" ) );
      registerFunction( "quote", new StandardSQLFunction("quote", StandardBasicTypes.STRING) );
      registerFunction( "random", new NoArgSQLFunction("random", StandardBasicTypes.INTEGER) );
      registerFunction( "round", new StandardSQLFunction("round") );
      registerFunction( "substr", new StandardSQLFunction("substr", StandardBasicTypes.STRING) );
      registerFunction( "substring", new SQLFunctionTemplate( StandardBasicTypes.STRING, "substr(?1, ?2, ?3)" ) );
      registerFunction( "trim", new AbstractAnsiTrimEmulationFunction() {
          protected SQLFunction resolveBothSpaceTrimFunction() {
            return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?1)");
          }
  
          protected SQLFunction resolveBothSpaceTrimFromFunction() {
            return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?2)");
          }
  
          protected SQLFunction resolveLeadingSpaceTrimFunction() {
            return new SQLFunctionTemplate(StandardBasicTypes.STRING, "ltrim(?1)");
          }
  
          protected SQLFunction resolveTrailingSpaceTrimFunction() {
            return new SQLFunctionTemplate(StandardBasicTypes.STRING, "rtrim(?1)");
          }
  
          protected SQLFunction resolveBothTrimFunction() {
            return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?1, ?2)");
          }
  
          protected SQLFunction resolveLeadingTrimFunction() {
            return new SQLFunctionTemplate(StandardBasicTypes.STRING, "ltrim(?1, ?2)");
          }
  
          protected SQLFunction resolveTrailingTrimFunction() {
            return new SQLFunctionTemplate(StandardBasicTypes.STRING, "rtrim(?1, ?2)");
          }
      } );
      //registerFunction( "upper", new StandardSQLFunction("upper") );
    }
  
    public boolean supportsIdentityColumns() {
      return true;
    }
  
    /*
    public boolean supportsInsertSelectIdentity() {
      return true; // As specify in NHibernate dialect
    }
    */
  
    public boolean hasDataTypeInIdentityColumn() {
      return false; // As specify in NHibernate dialect
    }
  
    /*
    public String appendIdentitySelectToInsert(String insertString) {
      return new StringBuffer(insertString.length()+30). // As specify in NHibernate dialect
        append(insertString).
        append("; ").append(getIdentitySelectString()).
        toString();
    }
    */
  
    public String getIdentityColumnString() {
      // return "integer primary key autoincrement";
      return "integer";
    }
  
    public String getIdentitySelectString() {
      return "select last_insert_rowid()";
    }
  
    public boolean supportsLimit() {
      return true;
    }
  
    public boolean bindLimitParametersInReverseOrder() {
      return true;
    }
  
    protected String getLimitString(String query, boolean hasOffset) {
      return new StringBuffer(query.length()+20).
        append(query).
        append(hasOffset ? " limit ? offset ?" : " limit ?").
        toString();
    }
  
    public boolean supportsTemporaryTables() {
      return true;
    }
  
    public String getCreateTemporaryTableString() {
      return "create temporary table if not exists";
    }
  
    public boolean dropTemporaryTableAfterUse() {
      return true; // TODO Validate
    }
  
    public boolean supportsCurrentTimestampSelection() {
      return true;
    }
  
    public boolean isCurrentTimestampSelectStringCallable() {
      return false;
    }
  
    public String getCurrentTimestampSelectString() {
      return "select current_timestamp";
    }
  
    public boolean supportsUnionAll() {
      return true;
    }
  
    public boolean hasAlterTable() {
      return false; // As specify in NHibernate dialect
    }
  
    public boolean dropConstraints() {
      return false;
    }
  
    /*
    public String getAddColumnString() {
      return "add column";
    }
    */
  
    public String getForUpdateString() {
      return "";
    }
  
    public boolean supportsOuterJoinForUpdate() {
      return false;
    }
  
    public String getDropForeignKeyString() {
      throw new UnsupportedOperationException("No drop foreign key syntax supported by SQLiteDialect");
    }
  
    public String getAddForeignKeyConstraintString(String constraintName,
        String[] foreignKey, String referencedTable, String[] primaryKey,
        boolean referencesPrimaryKey) {
      throw new UnsupportedOperationException("No add foreign key syntax supported by SQLiteDialect");
    }
  
    public String getAddPrimaryKeyConstraintString(String constraintName) {
      throw new UnsupportedOperationException("No add primary key syntax supported by SQLiteDialect");
    }
  
    public boolean supportsIfExistsBeforeTableName() {
      return true;
    }
  
    public boolean supportsCascadeDelete() {
      return true;
    }
  
    /* not case insensitive for unicode characters by default (ICU extension needed)
    public boolean supportsCaseInsensitiveLike() {
      return true;
    }
    */
  
    public boolean supportsTupleDistinctCounts() {
      return false;
    }
  
    public String getSelectGUIDString() {
      return "select hex(randomblob(16))";
    }
  }
  </code></pre>
  
  <p>Is there any solution for this or is there any sqlite dialect class which we can rely on ?</p>