From c42ebc94e34b3a1aa27c292188e73f5b06af814a Mon Sep 17 00:00:00 2001 From: James Moger Date: Tue, 25 Sep 2012 18:05:13 -0400 Subject: [PATCH] Support for read-only views (issue 8) --- docs/04_examples.mkd | 55 ++++++++ docs/05_releases.mkd | 8 +- docs/06_jaqu_comparison.mkd | 3 +- src/com/iciql/Db.java | 27 ++++ src/com/iciql/Define.java | 12 +- src/com/iciql/Iciql.java | 61 +++++++++ src/com/iciql/IciqlException.java | 3 + src/com/iciql/Query.java | 27 +++- src/com/iciql/QueryWhere.java | 8 ++ src/com/iciql/SQLDialect.java | 26 ++++ src/com/iciql/SQLDialectDefault.java | 51 +++++++ src/com/iciql/SQLDialectH2.java | 13 ++ src/com/iciql/SQLDialectHSQL.java | 8 ++ src/com/iciql/SQLDialectMySQL.java | 9 ++ src/com/iciql/SQLStatement.java | 12 ++ src/com/iciql/TableDefinition.java | 125 +++++++++++++++++- tests/com/iciql/test/IciqlSuite.java | 12 +- tests/com/iciql/test/ViewsTest.java | 114 ++++++++++++++++ tests/com/iciql/test/models/ProductView.java | 47 +++++++ .../test/models/ProductViewFromQuery.java | 42 ++++++ .../test/models/ProductViewInherited.java | 44 ++++++ .../models/ProductViewInheritedComplex.java | 28 ++++ 22 files changed, 726 insertions(+), 9 deletions(-) create mode 100644 tests/com/iciql/test/ViewsTest.java create mode 100644 tests/com/iciql/test/models/ProductView.java create mode 100644 tests/com/iciql/test/models/ProductViewFromQuery.java create mode 100644 tests/com/iciql/test/models/ProductViewInherited.java create mode 100644 tests/com/iciql/test/models/ProductViewInheritedComplex.java diff --git a/docs/04_examples.mkd b/docs/04_examples.mkd index 822bea5..6f60ae8 100644 --- a/docs/04_examples.mkd +++ b/docs/04_examples.mkd @@ -111,6 +111,61 @@ List<CustOrder> orders = }}); %ENDCODE% +## View Statements + +%BEGINCODE% +// the view named "ProductView" is created from the "Products" table +@IQView(viewTableName = "Products") +public class ProductView { + + @IQColumn + @IQConstraint("this >= 200 AND this < 300") + Long id; + + @IQColumn + String name; +} + +final ProductView v = new ProductView(); +List<ProductView> allProducts = db.from(v).select(); + +// this version of the view model "ProductView" inherits table metadata +// from the Products class which is annotated with IQTable +@IQView(inheritColumns = true) +public class ProductView extends Products { + + // inherited BUT replaced to define the constraint + @IQColumn + @IQConstraint("this >= 200 AND this < 300") + Long id; + + // inherited from Products + //@IQColumn + //String name; +} + +final ProductView v = new ProductView(); +List<ProductView> allProducts = db.from(v).select(); + +// in this example we are creating a view based on a fluent query +// and using 2 levels of inheritance. IQConstraints are ignored +// when using this approach because we are fluently defining them. +@IQView(inheritColumns = true) +public class ProductViewInherited extends ProductView { + +} + +final Products p = new Products(); +db.from(p).where(p.id).atLeast(200L).and(p.id).lessThan(300L).createView(ProductViewInherited.class); + +// now replace the view with a variation +db.from(p).where(p.id).atLeast(250L).and(p.id).lessThan(350L).replaceView(ProductViewInherited.class); + +// now drop the view from the database +db.dropView(ProductViewInherited.class); + +%ENDCODE% + ## Dynamic Queries Dynamic queries skip all field type checking and, depending on which approach you use, may skip model class/table name checking too. diff --git a/docs/05_releases.mkd b/docs/05_releases.mkd index a209f82..b224363 100644 --- a/docs/05_releases.mkd +++ b/docs/05_releases.mkd @@ -4,10 +4,16 @@ **%VERSION%** ([zip](http://code.google.com/p/iciql/downloads/detail?name=%ZIP%)|[jar](http://code.google.com/p/iciql/downloads/detail?name=%JAR%))   *released %BUILDDATE%* +- Implemented readonly view support. (issue 8)
+View models may be specified using the IQView annotation or Iciql.define(). Views can either be created automatically as part of a query of the view OR views may be constructed from a fluent statement. +- Support inheriting columns from super.super class, if super.super is annotated.
This allows for an inheritance hierarchy like:
+@IQTable class MyTable -> @IQView abstract class MyBaseView -> @IQView class MyConstrainedView - Fixed order of DEFAULT value in create table statement (issue 11) - Support inheritance of IQVersion for DbUpgrader implementations (issue 10) - Fixed password bug in model generator (issue 7) +### Older Releases + **1.1.0**   *released 2012-08-20* - All bulk operations (insert all, update all, delete all) now use JDBC savepoints to ensure atomicity of the transaction @@ -69,8 +75,6 @@ db.executeUpdate(q, new Date()); Iciql maps resultset columns by the index of the model class field from a list. This assumes that *all* columns in the resultset have a corresponding model field definition. This works fine for most queries because iciql explicitly selects columns from the table (*select alpha, beta...*) when you execute *select()*. The problem is when iciql issues a dynamic wildcard query and your model does not represent all columns in the resultset: columns and fields may fail to correctly line-up.

Iciql now maps all fields by their column name, not by their position. -### Older Releases - **0.7.3**   *released 2011-12-06* - api change release (API v8) diff --git a/docs/06_jaqu_comparison.mkd b/docs/06_jaqu_comparison.mkd index 73f8c4a..87629dd 100644 --- a/docs/06_jaqu_comparison.mkd +++ b/docs/06_jaqu_comparison.mkd @@ -13,8 +13,9 @@ This is an overview of the fundamental differences between the original JaQu pro column mappingswildcard queries index result sets by column nameall result sets built by field index
this can fail for wildcard queries savepointsbulk operations (insert, update, delete) use savepoints with rollback in the event of failure-- syntax and api +VIEWscreate readonly views either from a class definition or from a fluent statement-- dynamic queriesmethods and where clauses for dynamic queries that build iciql objects-- -DROPsyntax to drop a table +DROPsyntax to drop a table or view BETWEENsyntax for specifying a BETWEEN x AND y clause-- types primitivesfully supported-- diff --git a/src/com/iciql/Db.java b/src/com/iciql/Db.java index 90e7613..caec637 100644 --- a/src/com/iciql/Db.java +++ b/src/com/iciql/Db.java @@ -38,6 +38,7 @@ import javax.sql.DataSource; import com.iciql.DbUpgrader.DefaultDbUpgrader; import com.iciql.Iciql.IQTable; import com.iciql.Iciql.IQVersion; +import com.iciql.Iciql.IQView; import com.iciql.util.IciqlLogger; import com.iciql.util.JdbcUtils; import com.iciql.util.StringUtils; @@ -284,6 +285,27 @@ public class Db { return rc; } + @SuppressWarnings("unchecked") + public int dropView(Class modelClass) { + TableDefinition def = (TableDefinition) define(modelClass); + SQLStatement stat = new SQLStatement(this); + getDialect().prepareDropView(stat, def); + IciqlLogger.drop(stat.getSQL()); + int rc = 0; + try { + rc = stat.executeUpdate(); + } catch (IciqlException e) { + if (e.getIciqlCode() != IciqlException.CODE_OBJECT_NOT_FOUND) { + throw e; + } + } + // remove this model class from the table definition cache + classMap.remove(modelClass); + // remove this model class from the upgrade checked cache + upgradeChecked.remove(modelClass); + return rc; + } + public List buildObjects(Class modelClass, ResultSet rs) { return buildObjects(modelClass, false, rs); } @@ -400,6 +422,11 @@ public class Db { // initializer T t = instance(clazz); def.mapObject(t); + } else if (clazz.isAnnotationPresent(IQView.class)) { + // annotated classes skip the Define().define() static + // initializer + T t = instance(clazz); + def.mapObject(t); } } return def; diff --git a/src/com/iciql/Define.java b/src/com/iciql/Define.java index 3b58231..53f9862 100644 --- a/src/com/iciql/Define.java +++ b/src/com/iciql/Define.java @@ -59,6 +59,11 @@ public class Define { currentTableDefinition.defineTableName(tableName); } + public static void viewTableName(String viewTableName) { + checkInDefine(); + currentTableDefinition.defineViewTableName(viewTableName); + } + public static void memoryTable() { checkInDefine(); currentTableDefinition.defineMemoryTable(); @@ -98,7 +103,12 @@ public class Define { checkInDefine(); currentTableDefinition.defineDefaultValue(column, defaultValue); } - + + public static void constraint(Object column, String constraint) { + checkInDefine(); + currentTableDefinition.defineConstraint(column, constraint); + } + static synchronized void define(TableDefinition tableDefinition, Iciql table) { currentTableDefinition = tableDefinition; currentTable = table; diff --git a/src/com/iciql/Iciql.java b/src/com/iciql/Iciql.java index eaa256a..7b3a7c1 100644 --- a/src/com/iciql/Iciql.java +++ b/src/com/iciql/Iciql.java @@ -293,6 +293,67 @@ public interface Iciql { String[] value() default {}; } + /** + * Annotation to define a view. + */ + @Retention(RetentionPolicy.RUNTIME) + @Target(ElementType.TYPE) + public @interface IQView { + + /** + * The view name. If not specified the class name is used as the view + * name. + *

+ * The view name may still be overridden in the define() method if the + * model class is not annotated with IQView. Default: unspecified. + */ + String name() default ""; + + /** + * The source table for the view. + *

+ * The view name may still be overridden in the define() method if the + * model class is not annotated with IQView. Default: unspecified. + */ + String tableName() default ""; + + /** + * The inherit columns allows this model class to inherit columns from + * its super class. Any IQTable annotation present on the super class is + * ignored. Default: false. + */ + boolean inheritColumns() default false; + + /** + * Whether or not iciql tries to create the view. Default: + * true. + */ + boolean create() default true; + + /** + * If true, only fields that are explicitly annotated as IQColumn are + * mapped. Default: true. + */ + boolean annotationsOnly() default true; + } + + /** + * String snippet defining SQL constraints for a field. Use "this" as + * a placeholder for the column name. "this" will be substituted at + * runtime. + *

+ * IQConstraint("this > 2 AND this <= 7") + *

+ * This snippet may still be overridden in the define() method if the + * model class is not annotated with IQTable or IQView. Default: unspecified. + */ + @Retention(RetentionPolicy.RUNTIME) + @Target(ElementType.FIELD) + public @interface IQConstraint { + + String value() default ""; + } + /** * Annotation to specify multiple indexes. */ diff --git a/src/com/iciql/IciqlException.java b/src/com/iciql/IciqlException.java index 7e7021e..07fd363 100644 --- a/src/com/iciql/IciqlException.java +++ b/src/com/iciql/IciqlException.java @@ -121,6 +121,9 @@ public class IciqlException extends RuntimeException { } else if ("42P01".equals(state)) { // PostgreSQL table not found iciqlCode = CODE_OBJECT_NOT_FOUND; + } else if ("X0X05".equals(state)) { + // Derby view/table not found exists + iciqlCode = CODE_OBJECT_NOT_FOUND; } else if ("X0Y32".equals(state)) { // Derby table already exists iciqlCode = CODE_OBJECT_ALREADY_EXISTS; diff --git a/src/com/iciql/Query.java b/src/com/iciql/Query.java index 6c0ecf9..5dc78a5 100644 --- a/src/com/iciql/Query.java +++ b/src/com/iciql/Query.java @@ -107,6 +107,23 @@ public class Query { List list = (List) select(x); return list.isEmpty() ? null : list.get(0); } + + public void createView(Class viewClass) { + TableDefinition viewDef = db.define(viewClass); + + SQLStatement fromWhere = new SQLStatement(db); + appendFromWhere(fromWhere, false); + + SQLStatement stat = new SQLStatement(db); + db.getDialect().prepareCreateView(stat, viewDef, fromWhere.toSQL()); + IciqlLogger.create(stat.toSQL()); + stat.execute(); + } + + public void replaceView(Class viewClass) { + db.dropView(viewClass); + createView(viewClass); + } public String getSQL() { SQLStatement stat = getSelectStatement(false); @@ -803,8 +820,12 @@ public class Query { } } } - + void appendFromWhere(SQLStatement stat) { + appendFromWhere(stat, true); + } + + void appendFromWhere(SQLStatement stat, boolean log) { stat.appendSQL(" FROM "); from.appendSQL(stat); for (SelectTable join : joins) { @@ -834,7 +855,9 @@ public class Query { } } db.getDialect().appendLimitOffset(stat, limit, offset); - IciqlLogger.select(stat.getSQL()); + if (log) { + IciqlLogger.select(stat.getSQL()); + } } /** diff --git a/src/com/iciql/QueryWhere.java b/src/com/iciql/QueryWhere.java index 3f1afe1..5baa5ab 100644 --- a/src/com/iciql/QueryWhere.java +++ b/src/com/iciql/QueryWhere.java @@ -347,6 +347,14 @@ public class QueryWhere { public List selectDistinct() { return query.selectDistinct(); } + + public void createView(Class viewClass) { + query.createView(viewClass); + } + + public void replaceView(Class viewClass) { + query.replaceView(viewClass); + } /** * Order by primitive boolean field diff --git a/src/com/iciql/SQLDialect.java b/src/com/iciql/SQLDialect.java index 28f5566..8e3e3d2 100644 --- a/src/com/iciql/SQLDialect.java +++ b/src/com/iciql/SQLDialect.java @@ -79,6 +79,32 @@ public interface SQLDialect { */ void prepareDropTable(SQLStatement stat, TableDefinition def); + + /** + * Get the CREATE VIEW statement. + * + * @param stat + * @param def + */ + void prepareCreateView(SQLStatement stat, TableDefinition def); + + /** + * Get the CREATE VIEW statement. + * + * @param stat + * @param def + * @param fromWhere + */ + void prepareCreateView(SQLStatement stat, TableDefinition def, String fromWhere); + + /** + * Get the DROP VIEW statement. + * + * @param stat + * @param def + */ + void prepareDropView(SQLStatement stat, TableDefinition def); + /** * Get the CREATE INDEX statement. * diff --git a/src/com/iciql/SQLDialectDefault.java b/src/com/iciql/SQLDialectDefault.java index 7cc6bd7..0cd0448 100644 --- a/src/com/iciql/SQLDialectDefault.java +++ b/src/com/iciql/SQLDialectDefault.java @@ -166,6 +166,57 @@ public class SQLDialectDefault implements SQLDialect { stat.setSQL(buff.toString()); } + @Override + public void prepareDropView(SQLStatement stat, TableDefinition def) { + StatementBuilder buff = new StatementBuilder("DROP VIEW " + + prepareTableName(def.schemaName, def.tableName)); + stat.setSQL(buff.toString()); + return; + } + + protected String prepareCreateView(TableDefinition def) { + return "CREATE VIEW"; + } + + @Override + public void prepareCreateView(SQLStatement stat, TableDefinition def) { + StatementBuilder buff = new StatementBuilder(); + buff.append(" FROM "); + buff.append(prepareTableName(def.schemaName, def.viewTableName)); + + StatementBuilder where = new StatementBuilder(); + for (FieldDefinition field : def.fields) { + if (!StringUtils.isNullOrEmpty(field.constraint)) { + where.appendExceptFirst(", "); + String col = prepareColumnName(field.columnName); + String constraint = field.constraint.replace("{0}", col).replace("this", col); + where.append(constraint); + } + } + if (where.length() > 0) { + buff.append(" WHERE "); + buff.append(where.toString()); + } + + prepareCreateView(stat, def, buff.toString()); + } + + @Override + public void prepareCreateView(SQLStatement stat, TableDefinition def, String fromWhere) { + StatementBuilder buff = new StatementBuilder(); + buff.append(prepareCreateView(def)); + buff.append(" "); + buff.append(prepareTableName(def.schemaName, def.tableName)); + + buff.append(" AS SELECT "); + for (FieldDefinition field : def.fields) { + buff.appendExceptFirst(", "); + buff.append(prepareColumnName(field.columnName)); + } + buff.append(fromWhere); + stat.setSQL(buff.toString()); + } + protected boolean isIntegerType(String dataType) { if ("INT".equals(dataType)) { return true; diff --git a/src/com/iciql/SQLDialectH2.java b/src/com/iciql/SQLDialectH2.java index 1da45f6..6b3bab1 100644 --- a/src/com/iciql/SQLDialectH2.java +++ b/src/com/iciql/SQLDialectH2.java @@ -37,6 +37,19 @@ public class SQLDialectH2 extends SQLDialectDefault { return "CREATE CACHED TABLE IF NOT EXISTS"; } } + + @Override + protected String prepareCreateView(TableDefinition def) { + return "CREATE VIEW IF NOT EXISTS"; + } + + @Override + public void prepareDropView(SQLStatement stat, TableDefinition def) { + StatementBuilder buff = new StatementBuilder("DROP VIEW IF EXISTS " + + prepareTableName(def.schemaName, def.tableName)); + stat.setSQL(buff.toString()); + return; + } @Override protected boolean prepareColumnDefinition(StatementBuilder buff, String dataType, diff --git a/src/com/iciql/SQLDialectHSQL.java b/src/com/iciql/SQLDialectHSQL.java index 9975be6..82e6833 100644 --- a/src/com/iciql/SQLDialectHSQL.java +++ b/src/com/iciql/SQLDialectHSQL.java @@ -38,6 +38,14 @@ public class SQLDialectHSQL extends SQLDialectDefault { return "CREATE CACHED TABLE IF NOT EXISTS"; } } + + @Override + public void prepareDropView(SQLStatement stat, TableDefinition def) { + StatementBuilder buff = new StatementBuilder("DROP VIEW IF EXISTS " + + prepareTableName(def.schemaName, def.tableName)); + stat.setSQL(buff.toString()); + return; + } @Override protected boolean prepareColumnDefinition(StatementBuilder buff, String dataType, diff --git a/src/com/iciql/SQLDialectMySQL.java b/src/com/iciql/SQLDialectMySQL.java index 7fa1fa9..52676d4 100644 --- a/src/com/iciql/SQLDialectMySQL.java +++ b/src/com/iciql/SQLDialectMySQL.java @@ -36,6 +36,15 @@ public class SQLDialectMySQL extends SQLDialectDefault { protected String prepareCreateTable(TableDefinition def) { return "CREATE TABLE IF NOT EXISTS"; } + + @Override + public void prepareDropView(SQLStatement stat, TableDefinition def) { + StatementBuilder buff = new StatementBuilder("DROP VIEW IF EXISTS " + + prepareTableName(def.schemaName, def.tableName)); + stat.setSQL(buff.toString()); + return; + } + @Override public String prepareColumnName(String name) { return "`" + name + "`"; diff --git a/src/com/iciql/SQLStatement.java b/src/com/iciql/SQLStatement.java index 2f97829..394fc42 100644 --- a/src/com/iciql/SQLStatement.java +++ b/src/com/iciql/SQLStatement.java @@ -115,6 +115,18 @@ public class SQLStatement { params.add(o); return this; } + + void execute() { + PreparedStatement ps = null; + try { + ps = prepare(false); + ps.execute(); + } catch (SQLException e) { + throw IciqlException.fromSQL(getSQL(), e); + } finally { + JdbcUtils.closeSilently(ps); + } + } ResultSet executeQuery() { try { diff --git a/src/com/iciql/TableDefinition.java b/src/com/iciql/TableDefinition.java index f6a8c26..aa25722 100644 --- a/src/com/iciql/TableDefinition.java +++ b/src/com/iciql/TableDefinition.java @@ -24,12 +24,15 @@ import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; import java.util.IdentityHashMap; +import java.util.LinkedHashSet; import java.util.List; import java.util.Map; +import java.util.Set; import com.iciql.Iciql.EnumId; import com.iciql.Iciql.EnumType; import com.iciql.Iciql.IQColumn; +import com.iciql.Iciql.IQConstraint; import com.iciql.Iciql.IQEnum; import com.iciql.Iciql.IQIgnore; import com.iciql.Iciql.IQIndex; @@ -37,6 +40,7 @@ import com.iciql.Iciql.IQIndexes; import com.iciql.Iciql.IQSchema; import com.iciql.Iciql.IQTable; import com.iciql.Iciql.IQVersion; +import com.iciql.Iciql.IQView; import com.iciql.Iciql.IndexType; import com.iciql.util.IciqlLogger; import com.iciql.util.StatementBuilder; @@ -81,6 +85,7 @@ public class TableDefinition { String defaultValue; EnumType enumType; boolean isPrimitive; + String constraint; Object getValue(Object obj) { try { @@ -140,6 +145,7 @@ public class TableDefinition { public ArrayList fields = Utils.newArrayList(); String schemaName; String tableName; + String viewTableName; int tableVersion; List primaryKeyColumnNames; boolean memoryTable; @@ -172,6 +178,10 @@ public class TableDefinition { this.tableName = tableName; } + void defineViewTableName(String viewTableName) { + this.viewTableName = viewTableName; + } + void defineMemoryTable() { this.memoryTable = true; } @@ -302,6 +312,13 @@ public class TableDefinition { } } + void defineConstraint(Object column, String constraint) { + FieldDefinition def = fieldMap.get(column); + if (def != null) { + def.constraint = constraint; + } + } + void mapFields() { boolean byAnnotationsOnly = false; boolean inheritColumns = false; @@ -311,11 +328,33 @@ public class TableDefinition { inheritColumns = tableAnnotation.inheritColumns(); } + if (clazz.isAnnotationPresent(IQView.class)) { + IQView viewAnnotation = clazz.getAnnotation(IQView.class); + byAnnotationsOnly = viewAnnotation.annotationsOnly(); + inheritColumns = viewAnnotation.inheritColumns(); + } + List classFields = Utils.newArrayList(); classFields.addAll(Arrays.asList(clazz.getDeclaredFields())); if (inheritColumns) { Class superClass = clazz.getSuperclass(); classFields.addAll(Arrays.asList(superClass.getDeclaredFields())); + + if (superClass.isAnnotationPresent(IQView.class)) { + IQView superView = superClass.getAnnotation(IQView.class); + if (superView.inheritColumns()) { + // inherit columns from super.super.class + Class superSuperClass = superClass.getSuperclass(); + classFields.addAll(Arrays.asList(superSuperClass.getDeclaredFields())); + } + } else if (superClass.isAnnotationPresent(IQTable.class)) { + IQTable superTable = superClass.getAnnotation(IQTable.class); + if (superTable.inheritColumns()) { + // inherit columns from super.super.class + Class superSuperClass = superClass.getSuperclass(); + classFields.addAll(Arrays.asList(superSuperClass.getDeclaredFields())); + } + } } Set uniqueFields = new LinkedHashSet(); @@ -336,6 +375,7 @@ public class TableDefinition { boolean nullable = !f.getType().isPrimitive(); EnumType enumType = null; String defaultValue = ""; + String constraint = ""; // configure Java -> SQL enum mapping if (f.getType().isEnum()) { enumType = EnumType.DEFAULT_TYPE; @@ -388,9 +428,18 @@ public class TableDefinition { defaultValue = col.defaultValue(); } } + + boolean hasConstraint = f.isAnnotationPresent(IQConstraint.class); + if (hasConstraint) { + IQConstraint con = f.getAnnotation(IQConstraint.class); + // annotation overrides + if (!StringUtils.isNullOrEmpty(con.value())) { + constraint = con.value(); + } + } boolean reflectiveMatch = !byAnnotationsOnly; - if (reflectiveMatch || hasAnnotation) { + if (reflectiveMatch || hasAnnotation || hasConstraint) { FieldDefinition fieldDef = new FieldDefinition(); fieldDef.isPrimitive = f.getType().isPrimitive(); fieldDef.field = f; @@ -404,6 +453,7 @@ public class TableDefinition { fieldDef.defaultValue = defaultValue; fieldDef.enumType = enumType; fieldDef.dataType = ModelUtils.getDataType(fieldDef); + fieldDef.constraint = constraint; uniqueFields.add(fieldDef); } } @@ -540,6 +590,9 @@ public class TableDefinition { } long insert(Db db, Object obj, boolean returnKey) { + if (!StringUtils.isNullOrEmpty(viewTableName)) { + throw new IciqlException("Iciql does not support inserting rows into views!"); + } SQLStatement stat = new SQLStatement(db); StatementBuilder buff = new StatementBuilder("INSERT INTO "); buff.append(db.getDialect().prepareTableName(schemaName, tableName)).append('('); @@ -615,6 +668,9 @@ public class TableDefinition { } int update(Db db, Object obj) { + if (!StringUtils.isNullOrEmpty(viewTableName)) { + throw new IciqlException("Iciql does not support updating rows in views!"); + } if (primaryKeyColumnNames == null || primaryKeyColumnNames.size() == 0) { throw new IllegalStateException("No primary key columns defined for table " + obj.getClass() + " - no update possible"); @@ -661,6 +717,9 @@ public class TableDefinition { } int delete(Db db, Object obj) { + if (!StringUtils.isNullOrEmpty(viewTableName)) { + throw new IciqlException("Iciql does not support deleting rows from views!"); + } if (primaryKeyColumnNames == null || primaryKeyColumnNames.size() == 0) { throw new IllegalStateException("No primary key columns defined for table " + obj.getClass() + " - no update possible"); @@ -703,7 +762,11 @@ public class TableDefinition { return this; } SQLStatement stat = new SQLStatement(db); - db.getDialect().prepareCreateTable(stat, this); + if (StringUtils.isNullOrEmpty(viewTableName)) { + db.getDialect().prepareCreateTable(stat, this); + } else { + db.getDialect().prepareCreateView(stat, this); + } IciqlLogger.create(stat.getSQL()); try { stat.executeUpdate(); @@ -773,6 +836,64 @@ public class TableDefinition { } } + if (clazz.isAnnotationPresent(IQView.class)) { + IQView viewAnnotation = clazz.getAnnotation(IQView.class); + + // setup view name mapping, if properly annotated + // set this as the table name so it fits in seemlessly with iciql + if (!StringUtils.isNullOrEmpty(viewAnnotation.name())) { + tableName = viewAnnotation.name(); + } else { + tableName = clazz.getSimpleName(); + } + + // setup source table name mapping, if properly annotated + if (!StringUtils.isNullOrEmpty(viewAnnotation.tableName())) { + viewTableName = viewAnnotation.tableName(); + } else { + // check for IQTable annotation on super class + Class superClass = clazz.getSuperclass(); + if (superClass.isAnnotationPresent(IQTable.class)) { + IQTable table = superClass.getAnnotation(IQTable.class); + if (StringUtils.isNullOrEmpty(table.name())) { + // super.SimpleClassName + viewTableName = superClass.getSimpleName(); + } else { + // super.IQTable.name() + viewTableName = table.name(); + } + } else if (superClass.isAnnotationPresent(IQView.class)) { + // super class is a view + IQView parentView = superClass.getAnnotation(IQView.class); + if (StringUtils.isNullOrEmpty(parentView.tableName())) { + // parent view does not define a tableName, must be inherited + Class superParent = superClass.getSuperclass(); + if (superParent != null && superParent.isAnnotationPresent(IQTable.class)) { + IQTable superParentTable = superParent.getAnnotation(IQTable.class); + if (StringUtils.isNullOrEmpty(superParentTable.name())) { + // super.super.SimpleClassName + viewTableName = superParent.getSimpleName(); + } else { + // super.super.IQTable.name() + viewTableName = superParentTable.name(); + } + } + } else { + // super.IQView.tableName() + viewTableName = parentView.tableName(); + } + } + + if (StringUtils.isNullOrEmpty(viewTableName)) { + // still missing view table name + throw new IciqlException("View model class \"{0}\" is missing a table name!", tableName); + } + } + + // allow control over createTableIfRequired() + createIfRequired = viewAnnotation.create(); + } + if (clazz.isAnnotationPresent(IQIndex.class)) { // single table index IQIndex index = clazz.getAnnotation(IQIndex.class); diff --git a/tests/com/iciql/test/IciqlSuite.java b/tests/com/iciql/test/IciqlSuite.java index 000b1c8..68156f8 100644 --- a/tests/com/iciql/test/IciqlSuite.java +++ b/tests/com/iciql/test/IciqlSuite.java @@ -60,6 +60,10 @@ import com.iciql.test.models.Product; import com.iciql.test.models.ProductAnnotationOnly; import com.iciql.test.models.ProductInheritedAnnotation; import com.iciql.test.models.ProductMixedAnnotation; +import com.iciql.test.models.ProductView; +import com.iciql.test.models.ProductViewFromQuery; +import com.iciql.test.models.ProductViewInherited; +import com.iciql.test.models.ProductViewInheritedComplex; import com.iciql.test.models.SupportedTypes; import com.iciql.util.IciqlLogger; import com.iciql.util.IciqlLogger.IciqlListener; @@ -85,7 +89,7 @@ import com.iciql.util.Utils; @SuiteClasses({ AliasMapTest.class, AnnotationsTest.class, BooleanModelTest.class, ClobTest.class, ConcurrencyTest.class, EnumsTest.class, ModelsTest.class, PrimitivesTest.class, RuntimeQueryTest.class, SamplesTest.class, UpdateTest.class, UpgradesTest.class, JoinTest.class, - UUIDTest.class }) + UUIDTest.class, ViewsTest.class }) public class IciqlSuite { private static final TestDb[] TEST_DBS = { @@ -155,6 +159,12 @@ public class IciqlSuite { } db = Db.open(dataSource); + // drop views + db.dropView(ProductView.class); + db.dropView(ProductViewInherited.class); + db.dropView(ProductViewFromQuery.class); + db.dropView(ProductViewInheritedComplex.class); + // drop tables db.dropTable(BooleanModel.class); db.dropTable(ComplexObject.class); diff --git a/tests/com/iciql/test/ViewsTest.java b/tests/com/iciql/test/ViewsTest.java new file mode 100644 index 0000000..be2e085 --- /dev/null +++ b/tests/com/iciql/test/ViewsTest.java @@ -0,0 +1,114 @@ +/* + * Copyright 2012 James Moger. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package com.iciql.test; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertTrue; + +import java.util.List; + +import org.junit.After; +import org.junit.Before; +import org.junit.Test; + +import com.iciql.Db; +import com.iciql.test.models.ProductAnnotationOnly; +import com.iciql.test.models.ProductView; +import com.iciql.test.models.ProductViewFromQuery; +import com.iciql.test.models.ProductViewInherited; +import com.iciql.test.models.ProductViewInheritedComplex; +import com.mysql.jdbc.StringUtils; + +/** + * Test annotation processing. + */ +public class ViewsTest { + + /** + * This object represents a database (actually a connection to the + * database). + */ + + private Db db; + + @Before + public void setUp() { + db = IciqlSuite.openNewDb(); + db.insertAll(ProductAnnotationOnly.getList()); + } + + @After + public void tearDown() { + db.close(); + } + + @Test + public void testProductView() { + ProductView view = new ProductView(); + List products = db.from(view).select(); + assertEquals(5, products.size()); + for (int i = 0; i < products.size(); i++) { + assertEquals(3 + i, products.get(i).productId.intValue()); + } + } + + @Test + public void testProductViewInherited() { + ProductViewInherited view = new ProductViewInherited(); + List products = db.from(view).select(); + assertEquals(5, products.size()); + for (int i = 0; i < products.size(); i++) { + assertEquals(3 + i, products.get(i).productId.intValue()); + } + } + + @Test + public void testComplexInheritance() { + ProductViewInheritedComplex view = new ProductViewInheritedComplex(); + List products = db.from(view).select(); + assertEquals(5, products.size()); + for (int i = 0; i < products.size(); i++) { + assertEquals(3 + i, products.get(i).productId.intValue()); + assertTrue(!StringUtils.isNullOrEmpty(products.get(i).productName)); + } + } + + @Test + public void testCreateViewFromQuery() { + // create view from query + ProductAnnotationOnly product = new ProductAnnotationOnly(); + db.from(product).where(product.productId).exceeds(2L).and(product.productId).atMost(7L).createView(ProductViewFromQuery.class); + + // select from the created view + ProductViewFromQuery view = new ProductViewFromQuery(); + List products = db.from(view).select(); + assertEquals(5, products.size()); + for (int i = 0; i < products.size(); i++) { + assertEquals(3 + i, products.get(i).productId.intValue()); + } + + // replace the view + db.from(product).where(product.productId).exceeds(3L).and(product.productId).atMost(8L).replaceView(ProductViewFromQuery.class); + + // select from the replaced view + products = db.from(view).select(); + assertEquals(5, products.size()); + for (int i = 0; i < products.size(); i++) { + assertEquals(4 + i, products.get(i).productId.intValue()); + } + } +} diff --git a/tests/com/iciql/test/models/ProductView.java b/tests/com/iciql/test/models/ProductView.java new file mode 100644 index 0000000..2efe9eb --- /dev/null +++ b/tests/com/iciql/test/models/ProductView.java @@ -0,0 +1,47 @@ +/* + * Copyright 2012 James Moger. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package com.iciql.test.models; + +import com.iciql.Iciql.IQColumn; +import com.iciql.Iciql.IQConstraint; +import com.iciql.Iciql.IQView; + +/** + * A view containing product data. + */ + +@IQView(name = "AnnotatedProductView", tableName = "AnnotatedProduct") +public class ProductView { + + public String unmappedField; + + @IQColumn(name = "id", autoIncrement = true) + @IQConstraint("this <= 7 AND this > 2") + public Long productId; + + @IQColumn(name = "name") + public String productName; + + public ProductView() { + // public constructor + } + + public String toString() { + return productName + " (" + productId + ")"; + } + +} diff --git a/tests/com/iciql/test/models/ProductViewFromQuery.java b/tests/com/iciql/test/models/ProductViewFromQuery.java new file mode 100644 index 0000000..2f2f194 --- /dev/null +++ b/tests/com/iciql/test/models/ProductViewFromQuery.java @@ -0,0 +1,42 @@ +/* + * Copyright 2012 James Moger. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package com.iciql.test.models; + +import com.iciql.Iciql.IQColumn; +import com.iciql.Iciql.IQView; + +/** + * A view containing product data. + */ + +@IQView(name = "AnnotatedProductViewInherited", inheritColumns = true) +public class ProductViewFromQuery extends ProductAnnotationOnly { + + public String unmappedField; + + @IQColumn(name = "id") + public Long productId; + + public ProductViewFromQuery() { + // public constructor + } + + public String toString() { + return productName + " (" + productId + ")"; + } + +} diff --git a/tests/com/iciql/test/models/ProductViewInherited.java b/tests/com/iciql/test/models/ProductViewInherited.java new file mode 100644 index 0000000..e9c274b --- /dev/null +++ b/tests/com/iciql/test/models/ProductViewInherited.java @@ -0,0 +1,44 @@ +/* + * Copyright 2012 James Moger. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package com.iciql.test.models; + +import com.iciql.Iciql.IQColumn; +import com.iciql.Iciql.IQConstraint; +import com.iciql.Iciql.IQView; + +/** + * A view containing product data. + */ + +@IQView(name = "AnnotatedProductViewInherited", inheritColumns = true) +public class ProductViewInherited extends ProductAnnotationOnly { + + public String unmappedField; + + @IQColumn(name = "id", autoIncrement = true) + @IQConstraint("this <= 7 AND this > 2") + public Long productId; + + public ProductViewInherited() { + // public constructor + } + + public String toString() { + return productName + " (" + productId + ")"; + } + +} diff --git a/tests/com/iciql/test/models/ProductViewInheritedComplex.java b/tests/com/iciql/test/models/ProductViewInheritedComplex.java new file mode 100644 index 0000000..55e7ba8 --- /dev/null +++ b/tests/com/iciql/test/models/ProductViewInheritedComplex.java @@ -0,0 +1,28 @@ +/* + * Copyright 2012 James Moger. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package com.iciql.test.models; + +import com.iciql.Iciql.IQView; + +/** + * A view containing product data. + */ + +@IQView(inheritColumns = true) +public class ProductViewInheritedComplex extends ProductViewInherited { + +} -- 2.39.5