Author: echatellier Date: 2014-08-06 16:09:16 +0200 (Wed, 06 Aug 2014) New Revision: 481 Url: http://forge.codelutin.com/projects/faxtomail/repository/revisions/481 Log: Add indexed collection migration Added: trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql Modified: trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/FaxToMailConfiguration.java trunk/faxtomail-persistence/src/main/xmi/faxtomail.properties trunk/faxtomail-persistence/src/main/xmi/faxtomail.zargo trunk/faxtomail-service/pom.xml trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/migration/FaxtomailFlywayMigrationService.java trunk/pom.xml Modified: trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/FaxToMailConfiguration.java =================================================================== --- trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/FaxToMailConfiguration.java 2014-08-06 12:24:06 UTC (rev 480) +++ trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/FaxToMailConfiguration.java 2014-08-06 14:09:16 UTC (rev 481) @@ -202,10 +202,17 @@ return isDevMode; } + /** + * Get sub properties containing all options starting with "hibernate" and "topia". + * + * @return new sub map + */ public Map<String, String> getTopiaProperties() { Map<String, String> jpaParameters = Maps.newHashMap(); Properties hibernateProperties = applicationConfig.getOptionStartsWith("hibernate"); jpaParameters.putAll((Map) hibernateProperties); + Properties topiaProperties = applicationConfig.getOptionStartsWith("topia"); + jpaParameters.putAll((Map) topiaProperties); return jpaParameters; } Modified: trunk/faxtomail-persistence/src/main/xmi/faxtomail.properties =================================================================== --- trunk/faxtomail-persistence/src/main/xmi/faxtomail.properties 2014-08-06 12:24:06 UTC (rev 480) +++ trunk/faxtomail-persistence/src/main/xmi/faxtomail.properties 2014-08-06 14:09:16 UTC (rev 481) @@ -84,18 +84,18 @@ # Email com.franciaflex.faxtomail.persistence.entities.Email.attribute.mailFolder.tagvalue.notNull=true com.franciaflex.faxtomail.persistence.entities.Email.attribute.originalEmail.tagvalue.notNull=true -com.franciaflex.faxtomail.persistence.entities.Email.attribute.rangeRow.tagvalue.lazy=false -com.franciaflex.faxtomail.persistence.entities.Email.attribute.attachment.tagvalue.lazy=false -com.franciaflex.faxtomail.persistence.entities.Email.attribute.history.tagvalue.lazy=false -com.franciaflex.faxtomail.persistence.entities.Email.attribute.client.tagvalue.lazy=false -com.franciaflex.faxtomail.persistence.entities.Email.attribute.demandType.tagvalue.lazy=false -com.franciaflex.faxtomail.persistence.entities.Email.attribute.demandStatus.tagvalue.lazy=false -com.franciaflex.faxtomail.persistence.entities.Email.attribute.mailFolder.tagvalue.lazy=false -com.franciaflex.faxtomail.persistence.entities.Email.attribute.priority.tagvalue.lazy=false -com.franciaflex.faxtomail.persistence.entities.Email.attribute.etatAttente.tagvalue.lazy=false -com.franciaflex.faxtomail.persistence.entities.Email.attribute.emailGroup.tagvalue.lazy=false -com.franciaflex.faxtomail.persistence.entities.Email.attribute.replies.tagvalue.lazy=false -com.franciaflex.faxtomail.persistence.entities.Email.attribute.takenBy.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.rangeRow.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.attachment.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.history.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.client.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.demandType.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.demandStatus.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.mailFolder.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.priority.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.etatAttente.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.emailGroup.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.replies.tagvalue.lazy=false +#com.franciaflex.faxtomail.persistence.entities.Email.attribute.takenBy.tagvalue.lazy=false # History com.franciaflex.faxtomail.persistence.entities.History.attribute.type.tagvalue.notNull=true Modified: trunk/faxtomail-persistence/src/main/xmi/faxtomail.zargo =================================================================== (Binary files differ) Modified: trunk/faxtomail-service/pom.xml =================================================================== --- trunk/faxtomail-service/pom.xml 2014-08-06 12:24:06 UTC (rev 480) +++ trunk/faxtomail-service/pom.xml 2014-08-06 14:09:16 UTC (rev 481) @@ -57,6 +57,11 @@ <groupId>org.nuiton.topia</groupId> <artifactId>topia-service-flyway</artifactId> </dependency> + + <dependency> + <groupId>org.flywaydb</groupId> + <artifactId>flyway-core</artifactId> + </dependency> <dependency> <groupId>org.hibernate</groupId> Modified: trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/migration/FaxtomailFlywayMigrationService.java =================================================================== --- trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/migration/FaxtomailFlywayMigrationService.java 2014-08-06 12:24:06 UTC (rev 480) +++ trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/migration/FaxtomailFlywayMigrationService.java 2014-08-06 14:09:16 UTC (rev 481) @@ -51,12 +51,19 @@ placeholders.put("timestampType", "datetime2"); placeholders.put("integerType", "bigint"); placeholders.put("booleanType", "bit"); - } else { - - // types for postgresql and h2 + placeholders.put("indexType", "integer"); + } else if (StringUtils.startsWith(dialect, "org.hibernate.dialect.PostgreSQL")) { + // type for postgres placeholders.put("timestampType", "timestamp"); placeholders.put("integerType", "int8"); placeholders.put("booleanType", "boolean"); + placeholders.put("indexType", "int4"); + } else { + // types for h2 + placeholders.put("timestampType", "timestamp"); + placeholders.put("integerType", "bigint"); + placeholders.put("booleanType", "boolean"); + placeholders.put("indexType", "integer"); } flyway.setPlaceholders(placeholders); Added: trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql 2014-08-06 14:09:16 UTC (rev 481) @@ -0,0 +1,39 @@ + +-- remove unused association +ALTER TABLE faxtomailusergroup DROP COLUMN faxtomailusergroup; + +-- add column faxAccountType on emailAccount +ALTER TABLE emailAccount add column faxAccountType ${booleanType}; +update emailAccount set faxAccountType = 'f'; + +-- add email_idx on attachment +ALTER TABLE attachment ADD COLUMN email_idx ${indexType}; + +UPDATE attachment SET email_idx = ( + SELECT COUNT(topiaid) FROM attachment sub + WHERE sub.email = attachment.email AND (sub.topiacreatedate || sub.topiaid) < (attachment.topiacreatedate || attachment.topiaid) +); + +-- add email_idx on history +ALTER TABLE history ADD COLUMN email_idx ${indexType}; + +UPDATE history SET email_idx = ( + SELECT COUNT(topiaid) FROM history sub + WHERE sub.email = history.email AND (sub.topiacreatedate || sub.topiaid) < (history.topiacreatedate || history.topiaid) +); + +-- add email_idx on rangeRow +ALTER TABLE rangeRow ADD COLUMN email_idx ${indexType}; + +UPDATE rangeRow SET email_idx = ( + SELECT COUNT(topiaid) FROM rangeRow sub + WHERE sub.email = rangeRow.email AND (sub.topiacreatedate || sub.topiaid) < (rangeRow.topiacreatedate || rangeRow.topiaid) +); + +-- add email_idx on reply +ALTER TABLE reply ADD COLUMN email_idx ${indexType}; + +UPDATE reply SET email_idx = ( + SELECT COUNT(topiaid) FROM reply sub + WHERE sub.email = reply.email AND (sub.topiacreatedate || sub.topiaid) < (reply.topiacreatedate || reply.topiaid) +); Modified: trunk/pom.xml =================================================================== --- trunk/pom.xml 2014-08-06 12:24:06 UTC (rev 480) +++ trunk/pom.xml 2014-08-06 14:09:16 UTC (rev 481) @@ -203,6 +203,12 @@ <artifactId>topia-service-flyway</artifactId> <version>${topiaVersion}</version> </dependency> + + <dependency> + <groupId>org.flywaydb</groupId> + <artifactId>flyway-core</artifactId> + <version>3.0</version> + </dependency> <dependency> <groupId>org.hibernate</groupId>