branch bow-v2 updated (4f7430e -> 0335e59)
This is an automated email from the git hooks/post-receive script. New change to branch bow-v2 in repository bow. See https://gitlab.nuiton.org/chorem/bow.git from 4f7430e migration completement fonctionnel new 0335e59 amelioration pour fusionner les doublons The 1 revisions listed above as "new" are entirely new to this repository and will be described in separate emails. The revisions listed as "adds" were already present in the repository and have only been added to this reference. Detailed log of new commits: commit 0335e59f46fa2a467ecb2da152fcd8f2010f9482 Author: Benjamin <poussin@codelutin.com> Date: Thu Aug 1 01:49:12 2019 +0200 amelioration pour fusionner les doublons Summary of changes: .../resources/db/migration/V2__migration_data.sql | 46 +++++++++++++++++----- 1 file changed, 37 insertions(+), 9 deletions(-) -- To stop receiving notification emails like this one, please contact chorem.org SCM administrator <admin+scm@chorem.org>.
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>.
participants (1)
-
chorem.org scm