This is an automated email from the git hooks/post-receive script. New commit to branch bow-v2 in repository bow. See https://gitlab.nuiton.org/chorem/bow.git commit 0335e59f46fa2a467ecb2da152fcd8f2010f9482 Author: Benjamin <poussin@codelutin.com> Date: Thu Aug 1 01:49:12 2019 +0200 amelioration pour fusionner les doublons --- .../resources/db/migration/V2__migration_data.sql | 46 +++++++++++++++++----- 1 file changed, 37 insertions(+), 9 deletions(-) diff --git a/src/main/resources/db/migration/V2__migration_data.sql b/src/main/resources/db/migration/V2__migration_data.sql index f983098..2d9cd71 100644 --- a/src/main/resources/db/migration/V2__migration_data.sql +++ b/src/main/resources/db/migration/V2__migration_data.sql @@ -1,3 +1,22 @@ +CREATE OR REPLACE FUNCTION coalesce_agg_sfunc(state anyelement, value anyelement) RETURNS anyelement AS +$$ + SELECT coalesce(state, value); +$$ LANGUAGE SQL; + +CREATE OR REPLACE AGGREGATE coalesce_agg(anyelement) ( + SFUNC = coalesce_agg_sfunc, + STYPE = anyelement); + +CREATE OR REPLACE FUNCTION merge_array_sfunc(state anyarray, value anyarray) RETURNS anyarray AS +$$ + SELECT array( select distinct unnest(state || value)); +$$ LANGUAGE SQL; + +CREATE OR REPLACE AGGREGATE merge_array(anyarray) ( + SFUNC = merge_array_sfunc, + STYPE = anyarray); + + -- to keep creation date ALTER TABLE BowUser DISABLE TRIGGER update_BowUser_createDate; ALTER TABLE Bookmark DISABLE TRIGGER update_Bookmark_createDate; @@ -44,13 +63,8 @@ with 'actions', actions, 'authenticationinfo', authenticationInfo) as __json from badjson n left join all_actions t on n.id=t.id left join all_authenticationInfo a on n.id=a.id) ---, all_json as (select row_to_json((id, creationDate, login, password, tokens, authenticationInfo, autoScreenshot, autoFavicon, maxTagInCloud, maxResult, actions)) as __json from final) -- insert bookmarks in new table insert into bowUser select j.* from all_json, jsonb_populate_record(NULL::bowUser, __json) as j ON CONFLICT (login) DO NOTHING ; ---insert into bowUser (id, creationDate, login, password, tokens, authenticationInfo, autoScreenshot, autoFavicon, maxTagInCloud, maxResult, actions) select id::uuid, creationDate::TIMESTAMP, login::TEXT, password::TEXT, tokens::jsonb, authenticationInfo::jsonb, autoScreenshot::boolean, autoFavicon::boolean, maxTagInCloud::smallint, maxResult::smallint, actions::jsonb[] from final; - - - @@ -94,14 +108,28 @@ with 'authenticationinfo', a.authenticationInfo, 'lang', 'english') as __json from nottags_json n left join tags t on n.id=t.id left join all_authenticationInfo a on n.id=a.id left join bowUser on __json->>'Bookmark.email'=bowUser.login) - +, __row as (select j.* from all_json, jsonb_populate_record(NULL::bookmark, __json) as j inner join bowUser b on b.id=j.owner) +, __row_unique as (select + coalesce_agg(id) as id, + owner, + uri, + string_agg(description, '\n\n') as description, + merge_array(tags) as tags, + min(creationDate) as creationDate, + min(importDate) as importDate, + merge_array(privateAlias) as privateAlias, + merge_array(publicAlias) as publicAlias, + coalesce_agg(favicon) as favicon, + coalesce_agg(screenshot) as screenshot, + sum(visit) as visit, + coalesce_agg(authenticationInfo) as authenticationInfo, + coalesce_agg(lang) as lang + from __row group by owner, uri) -- insert bookmarks in new table -insert into bookmark select j.* from all_json, jsonb_populate_record(NULL::bookmark, __json) as j inner join bowUser b on b.id=j.owner ; -- ON CONFLICT (login) DO NOTHING ; +insert into bookmark (id, owner, uri, description, tags, creationdate, importdate, privatealias, publicalias, favicon, screenshot, visit, authenticationInfo, lang) select * from __row_unique; update bowUser set creationDate = (select min(creationDate) from bookmark where owner=bowUser.id); -- reactivate trigger ALTER TABLE BowUser ENABLE TRIGGER update_BowUser_createDate; ALTER TABLE Bookmark ENABLE TRIGGER update_Bookmark_createDate; - --- bookmark.id,bookmark.owner, bookmark.uri, bookmark.tags, bookmark.creationdate, bookmark.updatedate,bookmark.importdate, bookmark.privatealias, bookmark.publicalias, bookmark.visit, bookmark.lang -- To stop receiving notification emails like this one, please contact chorem.org SCM administrator <admin+scm@chorem.org>.