[Faxtomail-commits] r543 - in trunk/faxtomail-service/src: main/java/com/franciaflex/faxtomail/services/service/migration main/resources/db/migration main/resources/db/migration/h2 main/resources/db/migration/postgres main/resources/db/migration/sqlserver test/java/com/franciaflex/faxtomail/services/service/migration

Author: echatellier Date: 2014-08-13 14:13:19 +0200 (Wed, 13 Aug 2014) New Revision: 543 Url: http://forge.codelutin.com/projects/faxtomail/repository/revisions/543 Log: Duplicates migration scripts for h2/postgres/sqlserver. Added: trunk/faxtomail-service/src/main/resources/db/migration/h2/ trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_442__move_object_to_folder_configuration.sql trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_448__add_mail_folder_level_configuration.sql trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_454__add_ldap_config.sql trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_520__add_performance_indexes.sql trunk/faxtomail-service/src/main/resources/db/migration/postgres/ trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_442__move_object_to_folder_configuration.sql trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_448__add_mail_folder_level_configuration.sql trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_454__add_ldap_config.sql trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_520__add_performance_indexes.sql trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/ trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_442__move_object_to_folder_configuration.sql trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_448__add_mail_folder_level_configuration.sql trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_454__add_ldap_config.sql trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_520__add_performance_indexes.sql Removed: trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_442__move_object_to_folder_configuration.sql trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_448__add_mail_folder_level_configuration.sql trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_454__add_ldap_config.sql 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_520__add_performance_indexes.sql Modified: trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/migration/FaxtomailFlywayMigrationService.java trunk/faxtomail-service/src/test/java/com/franciaflex/faxtomail/services/service/migration/FlywayMigrationTest.java 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-13 10:16:46 UTC (rev 542) +++ trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/migration/FaxtomailFlywayMigrationService.java 2014-08-13 12:13:19 UTC (rev 543) @@ -1,14 +1,5 @@ package com.franciaflex.faxtomail.services.service.migration; -import java.util.HashMap; -import java.util.Map; - -import org.apache.commons.lang3.StringUtils; -import org.flywaydb.core.Flyway; -import org.hibernate.cfg.Environment; -import org.nuiton.topia.flyway.TopiaFlywayServiceImpl; -import org.nuiton.topia.persistence.TopiaApplicationContext; - /* * #%L * FaxToMail :: Service @@ -33,6 +24,12 @@ * #L% */ +import org.apache.commons.lang3.StringUtils; +import org.flywaydb.core.Flyway; +import org.hibernate.cfg.Environment; +import org.nuiton.topia.flyway.TopiaFlywayServiceImpl; +import org.nuiton.topia.persistence.TopiaApplicationContext; + /** * Surcharge du service de migration flayway par default pour pouvoir définir des scripts pour * plusieurs type de base de données. @@ -42,30 +39,19 @@ public class FaxtomailFlywayMigrationService extends TopiaFlywayServiceImpl { @Override - protected void doExtraConfiguration(Flyway flyway, TopiaApplicationContext topiaApplicationContext) { + protected void setLocations(Flyway flyway, TopiaApplicationContext topiaApplicationContext) { - Map<String, String> placeholders = new HashMap<>(); + String specificDirectory; String dialect = (String)topiaApplicationContext.getConfiguration().get(Environment.DIALECT); if (StringUtils.startsWith(dialect, "org.hibernate.dialect.SQLServer")) { - // type for sqlserver - placeholders.put("timestampType", "datetime2"); - placeholders.put("integerType", "bigint"); - placeholders.put("booleanType", "bit"); - placeholders.put("indexType", "integer"); + specificDirectory = "db/migration/sqlserver"; } 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"); + specificDirectory = "db/migration/postgres"; } else { - // types for h2 - placeholders.put("timestampType", "timestamp"); - placeholders.put("integerType", "bigint"); - placeholders.put("booleanType", "boolean"); - placeholders.put("indexType", "integer"); + specificDirectory = "db/migration/h2"; } - flyway.setPlaceholders(placeholders); + flyway.setLocations(specificDirectory); } + } Deleted: trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_442__move_object_to_folder_configuration.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_442__move_object_to_folder_configuration.sql 2014-08-13 10:16:46 UTC (rev 542) +++ trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_442__move_object_to_folder_configuration.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -1,60 +0,0 @@ - --- move lock out of business model and to his own table -alter table email drop column lockedby; -create table mailLock ( - topiaId varchar(255) not null, - topiaVersion ${integerType} not null, - topiaCreateDate ${timestampType}, - lockOn varchar(255) not null, - lockBy varchar(255) not null, - primary key (topiaId) -); -alter table mailLock - add constraint UK_cebqxr5mtsd4wmm0x0nwaa5km unique (lockOn); -alter table mailLock - add constraint FK_cebqxr5mtsd4wmm0x0nwaa5km - foreign key (lockOn) - references email; -alter table mailLock - add constraint FK_gwxpc59s0wjg60djbk5xa4d5d - foreign key (lockBy) - references faxToMailUser; - --- edi transfer for each folder -alter table demandtype drop column ediTransfer; -alter table mailfolder add column ediTransfer ${booleanType}; -update mailfolder set ediTransfer = 'f' where parent is null; - --- reject allowed for email account -alter table EmailAccount add column rejectAllowed ${booleanType}; -update EmailAccount set rejectAllowed = 'f'; - --- demandtype per folder -create table demandtypes_mailfolder ( - mailFolder varchar(255) not null, - demandTypes varchar(255) not null -); -alter table demandtypes_mailfolder - add constraint FK_apld4ycj71ouug7vmg5wtr1y9 - foreign key (demandTypes) - references demandType; -alter table demandtypes_mailfolder - add constraint FK_pmybd6fsyapv8ygtn7pjw258k - foreign key (mailFolder) - references mailFolder; -CREATE INDEX idx_MailFolder_demandTypes ON demandtypes_mailfolder(mailFolder); - --- range per folder -create table mailfolder_ranges ( - mailFolder varchar(255) not null, - ranges varchar(255) not null -); -alter table mailfolder_ranges - add constraint FK_kiolyiaeicw5he7xlima0ugbb - foreign key (ranges) - references range; -alter table mailfolder_ranges - add constraint FK_h78fwd9gc92wh7vw612q48xrr - foreign key (mailFolder) - references mailFolder; -CREATE INDEX idx_MailFolder_ranges ON mailfolder_ranges(mailFolder); Deleted: trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_448__add_mail_folder_level_configuration.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_448__add_mail_folder_level_configuration.sql 2014-08-13 10:16:46 UTC (rev 542) +++ trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_448__add_mail_folder_level_configuration.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -1,6 +0,0 @@ - --- add mail folder level configuration -alter table mailfolder add column useCurrentLevelDemandType ${booleanType}; -alter table mailfolder add column useCurrentLevelRange ${booleanType}; -update mailfolder set useCurrentLevelDemandType = 'f'; -update mailfolder set useCurrentLevelRange = 'f'; Deleted: trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_454__add_ldap_config.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_454__add_ldap_config.sql 2014-08-13 10:16:46 UTC (rev 542) +++ trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_454__add_ldap_config.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -1,6 +0,0 @@ - --- add user and group hidden configuration -alter table FaxToMailUserGroup add column hidden ${booleanType}; -update FaxToMailUserGroup set hidden = 'f'; -alter table FaxToMailUser add column hidden ${booleanType}; -update FaxToMailUser set hidden = 'f'; Deleted: 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 2014-08-13 10:16:46 UTC (rev 542) +++ trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -1,39 +0,0 @@ - --- 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) -); Deleted: trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_520__add_performance_indexes.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_520__add_performance_indexes.sql 2014-08-13 10:16:46 UTC (rev 542) +++ trunk/faxtomail-service/src/main/resources/db/migration/V1_0_0_520__add_performance_indexes.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -1,18 +0,0 @@ -CREATE INDEX email_receptionDate_idx ON email(receptionDate ASC); -CREATE INDEX email_archiveDate_idx ON email(archiveDate NULLS FIRST); -CREATE INDEX email_mailFolder_idx ON email(mailFolder); -CREATE INDEX email_demandStatus_idx ON email(demandStatus); - -CREATE INDEX email_takenBy_idx ON email(takenBy); -CREATE INDEX email_client_idx ON email(client); -CREATE INDEX email_etatAttente_idx ON email(etatAttente); -CREATE INDEX email_priority_idx ON email(priority); -CREATE INDEX email_demandType_idx ON email(demandType); - -CREATE INDEX demandType_fields_OWNER_idx ON demandType_fields(OWNER); -CREATE INDEX rangeRow_range_idx ON rangeRow(range); -CREATE INDEX history_faxToMailUser_idx ON history(faxToMailUser); -CREATE INDEX history_fields_OWNER_idx ON history_fields(OWNER); - -CREATE INDEX mailfolder_readrightusers_readRightUsers_idx ON mailfolder_readrightusers(readRightUsers); -CREATE INDEX mailfolder_readrightgroups_readRightGroups_idx ON mailfolder_readrightgroups(readRightGroups); Added: trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_442__move_object_to_folder_configuration.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_442__move_object_to_folder_configuration.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_442__move_object_to_folder_configuration.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,60 @@ + +-- move lock out of business model and to his own table +alter table email drop column lockedby; +create table mailLock ( + topiaId varchar(255) not null, + topiaVersion bigint not null, + topiaCreateDate timestamp, + lockOn varchar(255) not null, + lockBy varchar(255) not null, + primary key (topiaId) +); +alter table mailLock + add constraint UK_cebqxr5mtsd4wmm0x0nwaa5km unique (lockOn); +alter table mailLock + add constraint FK_cebqxr5mtsd4wmm0x0nwaa5km + foreign key (lockOn) + references email; +alter table mailLock + add constraint FK_gwxpc59s0wjg60djbk5xa4d5d + foreign key (lockBy) + references faxToMailUser; + +-- edi transfer for each folder +alter table demandtype drop column editransfer; +alter table mailfolder add editransfer boolean; +update mailfolder set editransfer = 'f' where parent is null; + +-- reject allowed for email account +alter table EmailAccount add rejectAllowed boolean; +update EmailAccount set rejectAllowed = 'f'; + +-- demandtype per folder +create table demandtypes_mailfolder ( + mailFolder varchar(255) not null, + demandTypes varchar(255) not null +); +alter table demandtypes_mailfolder + add constraint FK_apld4ycj71ouug7vmg5wtr1y9 + foreign key (demandTypes) + references demandType; +alter table demandtypes_mailfolder + add constraint FK_pmybd6fsyapv8ygtn7pjw258k + foreign key (mailFolder) + references mailFolder; +CREATE INDEX idx_MailFolder_demandTypes ON demandtypes_mailfolder(mailFolder); + +-- range per folder +create table mailfolder_ranges ( + mailFolder varchar(255) not null, + ranges varchar(255) not null +); +alter table mailfolder_ranges + add constraint FK_kiolyiaeicw5he7xlima0ugbb + foreign key (ranges) + references range; +alter table mailfolder_ranges + add constraint FK_h78fwd9gc92wh7vw612q48xrr + foreign key (mailFolder) + references mailFolder; +CREATE INDEX idx_MailFolder_ranges ON mailfolder_ranges(mailFolder); Added: trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_448__add_mail_folder_level_configuration.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_448__add_mail_folder_level_configuration.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_448__add_mail_folder_level_configuration.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,6 @@ + +-- add mail folder level configuration +alter table mailfolder add useCurrentLevelDemandType boolean; +alter table mailfolder add useCurrentLevelRange boolean; +update mailfolder set useCurrentLevelDemandType = 'f'; +update mailfolder set useCurrentLevelRange = 'f'; Added: trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_454__add_ldap_config.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_454__add_ldap_config.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_454__add_ldap_config.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,6 @@ + +-- add user and group hidden configuration +alter table FaxToMailUserGroup add hidden boolean; +alter table FaxToMailUser add hidden boolean; +update FaxToMailUserGroup set hidden = 'f'; +update FaxToMailUser set hidden = 'f'; Added: trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,39 @@ + +-- remove unused association +ALTER TABLE faxtomailusergroup DROP COLUMN faxtomailusergroup; + +-- add column faxAccountType on emailAccount +ALTER TABLE emailAccount add faxAccountType boolean; +update emailAccount set faxAccountType = 'f'; + +-- add email_idx on attachment +ALTER TABLE attachment ADD email_idx integer; + +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 integer; + +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 email_idx integer; + +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 email_idx integer; + +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) +); Added: trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_520__add_performance_indexes.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_520__add_performance_indexes.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/h2/V1_0_0_520__add_performance_indexes.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,18 @@ +CREATE INDEX email_receptionDate_idx ON email(receptionDate ASC); +CREATE INDEX email_archiveDate_idx ON email(archiveDate NULLS FIRST); +CREATE INDEX email_mailFolder_idx ON email(mailFolder); +CREATE INDEX email_demandStatus_idx ON email(demandStatus); + +CREATE INDEX email_takenBy_idx ON email(takenBy); +CREATE INDEX email_client_idx ON email(client); +CREATE INDEX email_etatAttente_idx ON email(etatAttente); +CREATE INDEX email_priority_idx ON email(priority); +CREATE INDEX email_demandType_idx ON email(demandType); + +CREATE INDEX demandType_fields_OWNER_idx ON demandType_fields(OWNER); +CREATE INDEX rangeRow_range_idx ON rangeRow(range); +CREATE INDEX history_faxToMailUser_idx ON history(faxToMailUser); +CREATE INDEX history_fields_OWNER_idx ON history_fields(OWNER); + +CREATE INDEX mailfolder_readrightusers_readRightUsers_idx ON mailfolder_readrightusers(readRightUsers); +CREATE INDEX mailfolder_readrightgroups_readRightGroups_idx ON mailfolder_readrightgroups(readRightGroups); Added: trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_442__move_object_to_folder_configuration.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_442__move_object_to_folder_configuration.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_442__move_object_to_folder_configuration.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,60 @@ + +-- move lock out of business model and to his own table +alter table email drop column lockedby; +create table mailLock ( + topiaId varchar(255) not null, + topiaVersion int8 not null, + topiaCreateDate timestamp, + lockOn varchar(255) not null, + lockBy varchar(255) not null, + primary key (topiaId) +); +alter table mailLock + add constraint UK_cebqxr5mtsd4wmm0x0nwaa5km unique (lockOn); +alter table mailLock + add constraint FK_cebqxr5mtsd4wmm0x0nwaa5km + foreign key (lockOn) + references email; +alter table mailLock + add constraint FK_gwxpc59s0wjg60djbk5xa4d5d + foreign key (lockBy) + references faxToMailUser; + +-- edi transfer for each folder +alter table demandtype drop column editransfer; +alter table mailfolder add editransfer boolean; +update mailfolder set editransfer = 'f' where parent is null; + +-- reject allowed for email account +alter table EmailAccount add rejectAllowed boolean; +update EmailAccount set rejectAllowed = 'f'; + +-- demandtype per folder +create table demandtypes_mailfolder ( + mailFolder varchar(255) not null, + demandTypes varchar(255) not null +); +alter table demandtypes_mailfolder + add constraint FK_apld4ycj71ouug7vmg5wtr1y9 + foreign key (demandTypes) + references demandType; +alter table demandtypes_mailfolder + add constraint FK_pmybd6fsyapv8ygtn7pjw258k + foreign key (mailFolder) + references mailFolder; +CREATE INDEX idx_MailFolder_demandTypes ON demandtypes_mailfolder(mailFolder); + +-- range per folder +create table mailfolder_ranges ( + mailFolder varchar(255) not null, + ranges varchar(255) not null +); +alter table mailfolder_ranges + add constraint FK_kiolyiaeicw5he7xlima0ugbb + foreign key (ranges) + references range; +alter table mailfolder_ranges + add constraint FK_h78fwd9gc92wh7vw612q48xrr + foreign key (mailFolder) + references mailFolder; +CREATE INDEX idx_MailFolder_ranges ON mailfolder_ranges(mailFolder); Added: trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_448__add_mail_folder_level_configuration.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_448__add_mail_folder_level_configuration.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_448__add_mail_folder_level_configuration.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,6 @@ + +-- add mail folder level configuration +alter table mailfolder add useCurrentLevelDemandType boolean; +alter table mailfolder add useCurrentLevelRange boolean; +update mailfolder set useCurrentLevelDemandType = 'f'; +update mailfolder set useCurrentLevelRange = 'f'; Added: trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_454__add_ldap_config.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_454__add_ldap_config.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_454__add_ldap_config.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,6 @@ + +-- add user and group hidden configuration +alter table FaxToMailUserGroup add hidden boolean; +alter table FaxToMailUser add hidden boolean; +update FaxToMailUserGroup set hidden = 'f'; +update FaxToMailUser set hidden = 'f'; Added: trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,39 @@ + +-- remove unused association +ALTER TABLE faxtomailusergroup DROP COLUMN faxtomailusergroup; + +-- add column faxAccountType on emailAccount +ALTER TABLE emailAccount add faxAccountType boolean; +update emailAccount set faxAccountType = 'f'; + +-- add email_idx on attachment +ALTER TABLE attachment ADD email_idx int4; + +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 int4; + +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 email_idx int4; + +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 email_idx int4; + +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) +); Added: trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_520__add_performance_indexes.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_520__add_performance_indexes.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/postgres/V1_0_0_520__add_performance_indexes.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,18 @@ +CREATE INDEX email_receptionDate_idx ON email(receptionDate ASC); +CREATE INDEX email_archiveDate_idx ON email(archiveDate NULLS FIRST); +CREATE INDEX email_mailFolder_idx ON email(mailFolder); +CREATE INDEX email_demandStatus_idx ON email(demandStatus); + +CREATE INDEX email_takenBy_idx ON email(takenBy); +CREATE INDEX email_client_idx ON email(client); +CREATE INDEX email_etatAttente_idx ON email(etatAttente); +CREATE INDEX email_priority_idx ON email(priority); +CREATE INDEX email_demandType_idx ON email(demandType); + +CREATE INDEX demandType_fields_OWNER_idx ON demandType_fields(OWNER); +CREATE INDEX rangeRow_range_idx ON rangeRow(range); +CREATE INDEX history_faxToMailUser_idx ON history(faxToMailUser); +CREATE INDEX history_fields_OWNER_idx ON history_fields(OWNER); + +CREATE INDEX mailfolder_readrightusers_readRightUsers_idx ON mailfolder_readrightusers(readRightUsers); +CREATE INDEX mailfolder_readrightgroups_readRightGroups_idx ON mailfolder_readrightgroups(readRightGroups); Added: trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_442__move_object_to_folder_configuration.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_442__move_object_to_folder_configuration.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_442__move_object_to_folder_configuration.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,66 @@ + +-- move lock out of business model and to his own table +alter table email drop constraint FK_970tl3l0qssnsrvvb3m1n6ivp; +GO +alter table email drop column lockedby; +GO +create table mailLock ( + topiaId varchar(255) not null, + topiaVersion bigint not null, + topiaCreateDate datetime2, + lockOn varchar(255) not null, + lockBy varchar(255) not null, + primary key (topiaId) +); +alter table mailLock + add constraint UK_cebqxr5mtsd4wmm0x0nwaa5km unique (lockOn); +alter table mailLock + add constraint FK_cebqxr5mtsd4wmm0x0nwaa5km + foreign key (lockOn) + references email; +alter table mailLock + add constraint FK_gwxpc59s0wjg60djbk5xa4d5d + foreign key (lockBy) + references faxToMailUser; + +-- edi transfer for each folder +alter table demandtype drop column editransfer; +alter table mailfolder add editransfer bit; +GO +update mailfolder set editransfer = 0 where parent is null; + +-- reject allowed for email account +alter table EmailAccount add rejectAllowed bit; +GO +update EmailAccount set rejectAllowed = 0; + +-- demandtype per folder +create table demandtypes_mailfolder ( + mailFolder varchar(255) not null, + demandTypes varchar(255) not null +); +alter table demandtypes_mailfolder + add constraint FK_apld4ycj71ouug7vmg5wtr1y9 + foreign key (demandTypes) + references demandType; +alter table demandtypes_mailfolder + add constraint FK_pmybd6fsyapv8ygtn7pjw258k + foreign key (mailFolder) + references mailFolder; +CREATE INDEX idx_MailFolder_demandTypes ON demandtypes_mailfolder(mailFolder); + +-- range per folder +create table mailfolder_ranges ( + mailFolder varchar(255) not null, + ranges varchar(255) not null +); +alter table mailfolder_ranges + add constraint FK_kiolyiaeicw5he7xlima0ugbb + foreign key (ranges) + references range; +alter table mailfolder_ranges + add constraint FK_h78fwd9gc92wh7vw612q48xrr + foreign key (mailFolder) + references mailFolder; +CREATE INDEX idx_MailFolder_ranges ON mailfolder_ranges(mailFolder); +GO \ No newline at end of file Added: trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_448__add_mail_folder_level_configuration.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_448__add_mail_folder_level_configuration.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_448__add_mail_folder_level_configuration.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,7 @@ + +-- add mail folder level configuration +alter table mailfolder add useCurrentLevelDemandType bit; +alter table mailfolder add useCurrentLevelRange bit; +GO +update mailfolder set useCurrentLevelDemandType = 0; +update mailfolder set useCurrentLevelRange = 0; Added: trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_454__add_ldap_config.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_454__add_ldap_config.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_454__add_ldap_config.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,7 @@ + +-- add user and group hidden configuration +alter table FaxToMailUserGroup add hidden bit; +alter table FaxToMailUser add hidden bit; +GO +update FaxToMailUserGroup set hidden = 0; +update FaxToMailUser set hidden = 0; Added: trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_480__remove_faxtomailusergroup_loop_association_and_add_idx.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,47 @@ + +-- remove unused association +ALTER TABLE faxtomailusergroup DROP constraint FK_8efs0kgau1bt0iifrn8bdbecy; +ALTER TABLE faxtomailusergroup DROP COLUMN faxtomailusergroup; + +-- add column faxAccountType on emailAccount +ALTER TABLE emailAccount add faxAccountType bit; +GO +update emailAccount set faxAccountType = 0; + +-- see http://www.sql-server-helper.com/tips/date-formats.aspx for convert + +-- add email_idx on attachment +ALTER TABLE attachment ADD email_idx integer; +GO +UPDATE attachment SET email_idx = ( + SELECT COUNT(topiaid) FROM attachment sub + WHERE sub.email = attachment.email + AND (CONVERT(VARCHAR(23), sub.topiacreatedate, 126) + sub.topiaid) < (CONVERT(VARCHAR(23), attachment.topiacreatedate, 126) + attachment.topiaid) +); + +-- add email_idx on history +ALTER TABLE history ADD email_idx integer; +GO +UPDATE history SET email_idx = ( + SELECT COUNT(topiaid) FROM history sub + WHERE sub.email = history.email + AND (CONVERT(VARCHAR(23), sub.topiacreatedate, 126) + sub.topiaid) < (CONVERT(VARCHAR(23), history.topiacreatedate, 126) + history.topiaid) +); + +-- add email_idx on rangeRow +ALTER TABLE rangeRow ADD email_idx integer; +GO +UPDATE rangeRow SET email_idx = ( + SELECT COUNT(topiaid) FROM rangeRow sub + WHERE sub.email = rangeRow.email + AND (CONVERT(VARCHAR(23), sub.topiacreatedate, 126) + sub.topiaid) < (CONVERT(VARCHAR(23), rangeRow.topiacreatedate, 126) + rangeRow.topiaid) +); + +-- add email_idx on reply +ALTER TABLE reply ADD email_idx integer; +GO +UPDATE reply SET email_idx = ( + SELECT COUNT(topiaid) FROM reply sub + WHERE sub.email = reply.email + AND (CONVERT(VARCHAR(23), sub.topiacreatedate, 126) + sub.topiaid) < (CONVERT(VARCHAR(23), reply.topiacreatedate, 126) + reply.topiaid) +); Added: trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_520__add_performance_indexes.sql =================================================================== --- trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_520__add_performance_indexes.sql (rev 0) +++ trunk/faxtomail-service/src/main/resources/db/migration/sqlserver/V1_0_0_520__add_performance_indexes.sql 2014-08-13 12:13:19 UTC (rev 543) @@ -0,0 +1,18 @@ +CREATE INDEX email_receptionDate_idx ON email(receptionDate ASC); +CREATE INDEX email_archiveDate_idx ON email(archiveDate); +CREATE INDEX email_mailFolder_idx ON email(mailFolder); +CREATE INDEX email_demandStatus_idx ON email(demandStatus); + +CREATE INDEX email_takenBy_idx ON email(takenBy); +CREATE INDEX email_client_idx ON email(client); +CREATE INDEX email_etatAttente_idx ON email(etatAttente); +CREATE INDEX email_priority_idx ON email(priority); +CREATE INDEX email_demandType_idx ON email(demandType); + +CREATE INDEX demandType_fields_OWNER_idx ON demandType_fields(OWNER); +CREATE INDEX rangeRow_range_idx ON rangeRow(range); +CREATE INDEX history_faxToMailUser_idx ON history(faxToMailUser); +CREATE INDEX history_fields_OWNER_idx ON history_fields(OWNER); + +CREATE INDEX mailfolder_readrightusers_readRightUsers_idx ON mailfolder_readrightusers(readRightUsers); +CREATE INDEX mailfolder_readrightgroups_readRightGroups_idx ON mailfolder_readrightgroups(readRightGroups); Modified: trunk/faxtomail-service/src/test/java/com/franciaflex/faxtomail/services/service/migration/FlywayMigrationTest.java =================================================================== --- trunk/faxtomail-service/src/test/java/com/franciaflex/faxtomail/services/service/migration/FlywayMigrationTest.java 2014-08-13 10:16:46 UTC (rev 542) +++ trunk/faxtomail-service/src/test/java/com/franciaflex/faxtomail/services/service/migration/FlywayMigrationTest.java 2014-08-13 12:13:19 UTC (rev 543) @@ -73,7 +73,6 @@ hibernateH2Config.put(Environment.PASS, ""); hibernateH2Config.put(Environment.HBM2DDL_AUTO, ""); hibernateH2Config.put(Environment.URL, "jdbc:h2:file:" + databaseFile.getAbsolutePath() + "/h2data-1_0_0_rc2;MODE=MSSQLServer"); - System.out.println(databaseFile.getAbsolutePath() + "/h2data-1_0_0_rc2;"); // flyway config hibernateH2Config.put("topia.service.migration", FaxtomailFlywayMigrationService.class.getName()); @@ -90,7 +89,7 @@ public void testFlywayMigrations() { // recupere le nombre de fichier de migration - List<File> migrationFiles = FileUtil.find(new File("src/main/resources/db/migration"), ".*\\.sql", false); + List<File> migrationFiles = FileUtil.find(new File("src/main/resources/db/migration/h2"), ".*\\.sql", false); // recupere dans la base le nombre de migration qui sont passée Integer migrationCount = getServiceContext().getPersistenceContext().getSqlSupport().findSingleResult(new TopiaSqlQuery<Integer>() {