3 * Copyright (C) 2009-2022 SonarSource SA
4 * mailto:info AT sonarsource DOT com
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU Lesser General Public
8 * License as published by the Free Software Foundation; either
9 * version 3 of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 * Lesser General Public License for more details.
16 * You should have received a copy of the GNU Lesser General Public License
17 * along with this program; if not, write to the Free Software Foundation,
18 * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
20 package org.sonar.server.platform.db.migration.version.v91;
22 import com.google.common.collect.Sets;
23 import java.io.IOException;
24 import java.io.InputStream;
25 import java.io.InputStreamReader;
26 import java.io.StringReader;
27 import java.sql.SQLException;
28 import java.util.ArrayList;
29 import java.util.HashMap;
30 import java.util.HashSet;
31 import java.util.LinkedHashMap;
32 import java.util.LinkedList;
33 import java.util.List;
35 import java.util.Optional;
37 import java.util.TreeSet;
38 import java.util.regex.Pattern;
39 import java.util.stream.Collectors;
40 import javax.annotation.CheckForNull;
41 import javax.annotation.Nullable;
42 import javax.xml.XMLConstants;
43 import javax.xml.parsers.ParserConfigurationException;
44 import javax.xml.parsers.SAXParser;
45 import javax.xml.parsers.SAXParserFactory;
46 import javax.xml.stream.XMLInputFactory;
47 import javax.xml.stream.XMLStreamException;
48 import javax.xml.transform.sax.SAXSource;
49 import javax.xml.validation.SchemaFactory;
50 import org.apache.commons.lang.StringUtils;
51 import org.codehaus.staxmate.SMInputFactory;
52 import org.codehaus.staxmate.in.SMHierarchicCursor;
53 import org.codehaus.staxmate.in.SMInputCursor;
54 import org.sonar.api.utils.System2;
55 import org.sonar.core.util.UuidFactory;
56 import org.sonar.db.Database;
57 import org.sonar.db.DatabaseUtils;
58 import org.sonar.server.platform.db.migration.step.DataChange;
59 import org.sonar.server.platform.db.migration.step.MassUpdate;
60 import org.sonar.server.platform.db.migration.step.Select;
61 import org.sonar.server.platform.db.migration.step.Upsert;
62 import org.sonar.server.platform.db.migration.version.v91.MigratePortfoliosToNewTables.ViewXml.ViewDef;
63 import org.xml.sax.InputSource;
64 import org.xml.sax.SAXException;
65 import org.xml.sax.SAXParseException;
66 import org.xml.sax.helpers.DefaultHandler;
68 import static java.nio.charset.StandardCharsets.UTF_8;
69 import static java.util.Optional.ofNullable;
70 import static javax.xml.XMLConstants.FEATURE_SECURE_PROCESSING;
71 import static org.apache.commons.io.IOUtils.toInputStream;
72 import static org.apache.commons.lang.StringUtils.trim;
74 public class MigratePortfoliosToNewTables extends DataChange {
75 private static final String SELECT_DEFAULT_VISIBILITY = "select text_value from properties where prop_key = 'projects.default.visibility'";
76 private static final String SELECT_UUID_VISIBILITY_BY_COMPONENT_KEY = "select c.uuid, c.private from components c where c.kee = ?";
77 private static final String SELECT_PORTFOLIO_UUID_AND_SELECTION_MODE_BY_KEY = "select uuid,selection_mode from portfolios where kee = ?";
78 private static final String SELECT_PROJECT_KEYS_BY_PORTFOLIO_UUID = "select p.kee from portfolio_projects pp "
79 + "join projects p on pp.project_uuid = p.uuid where pp.portfolio_uuid = ?";
80 private static final String SELECT_PROJECT_UUIDS_BY_KEYS = "select p.uuid,p.kee from projects p where p.kee in (PLACEHOLDER)";
81 private static final String VIEWS_DEF_KEY = "views.def";
82 private static final String PLACEHOLDER = "PLACEHOLDER";
84 private final UuidFactory uuidFactory;
85 private final System2 system;
87 private boolean defaultPrivateFlag;
89 public enum SelectionMode {
90 NONE, MANUAL, REGEXP, REST, TAGS
93 public MigratePortfoliosToNewTables(Database db, UuidFactory uuidFactory, System2 system) {
96 this.uuidFactory = uuidFactory;
101 protected void execute(Context context) throws SQLException {
102 String xml = getViewsDefinition(context);
103 // skip migration if `views.def` does not exist in the db
108 this.defaultPrivateFlag = ofNullable(context.prepareSelect(SELECT_DEFAULT_VISIBILITY)
109 .get(row -> "private".equals(row.getString(1))))
113 Map<String, ViewXml.ViewDef> portfolioXmlMap = ViewXml.parse(xml);
114 List<ViewXml.ViewDef> portfolios = new LinkedList<>(portfolioXmlMap.values());
116 Map<String, PortfolioDb> portfolioDbMap = new HashMap<>();
117 for (ViewXml.ViewDef portfolio : portfolios) {
118 PortfolioDb createdPortfolio = insertPortfolio(context, portfolio);
119 if (createdPortfolio.selectionMode == SelectionMode.MANUAL) {
120 insertPortfolioProjects(context, portfolio, createdPortfolio);
122 portfolioDbMap.put(createdPortfolio.kee, createdPortfolio);
124 // all portfolio has been created and new uuids assigned
125 // update portfolio hierarchy parent/root
126 insertReferences(context, portfolioXmlMap, portfolioDbMap);
127 updateHierarchy(context, portfolioXmlMap, portfolioDbMap);
128 } catch (Exception e) {
129 throw new IllegalStateException("Failed to migrate views definitions property.", e);
133 private PortfolioDb insertPortfolio(Context context, ViewXml.ViewDef portfolioFromXml) throws SQLException {
134 long now = system.now();
135 PortfolioDb portfolioDb = context.prepareSelect(SELECT_PORTFOLIO_UUID_AND_SELECTION_MODE_BY_KEY)
136 .setString(1, portfolioFromXml.key)
137 .get(r -> new PortfolioDb(r.getString(1), portfolioFromXml.key, SelectionMode.valueOf(r.getString(2))));
139 Optional<ComponentDb> componentDbOpt = ofNullable(context.prepareSelect(SELECT_UUID_VISIBILITY_BY_COMPONENT_KEY)
140 .setString(1, portfolioFromXml.key)
141 .get(row -> new ComponentDb(row.getString(1), row.getBoolean(2))));
143 // no portfolio -> insert
144 if (portfolioDb == null) {
145 Upsert insertPortfolioQuery = context.prepareUpsert("insert into " +
146 "portfolios(uuid, kee, private, name, description, root_uuid, parent_uuid, selection_mode, selection_expression, updated_at, created_at) " +
147 "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
149 String portfolioUuid = componentDbOpt.map(c -> c.uuid).orElse(uuidFactory.create());
150 insertPortfolioQuery.setString(1, portfolioUuid)
151 .setString(2, portfolioFromXml.key)
152 .setBoolean(3, componentDbOpt.map(c -> c.visibility).orElse(this.defaultPrivateFlag))
153 .setString(4, portfolioFromXml.name)
154 .setString(5, portfolioFromXml.desc)
155 .setString(6, PLACEHOLDER)
156 .setString(7, PLACEHOLDER);
157 SelectionMode selectionMode = SelectionMode.NONE;
158 String selectionExpression = null;
159 if (portfolioFromXml.getProjects() != null && !portfolioFromXml.getProjects().isEmpty()) {
160 selectionMode = SelectionMode.MANUAL;
161 } else if (portfolioFromXml.regexp != null && !portfolioFromXml.regexp.isBlank()) {
162 selectionMode = SelectionMode.REGEXP;
163 selectionExpression = portfolioFromXml.regexp;
164 } else if (portfolioFromXml.def) {
165 selectionMode = SelectionMode.REST;
166 } else if (portfolioFromXml.tagsAssociation != null && !portfolioFromXml.tagsAssociation.isEmpty()) {
167 selectionMode = SelectionMode.TAGS;
168 selectionExpression = String.join(",", portfolioFromXml.tagsAssociation);
171 insertPortfolioQuery.setString(8, selectionMode.name())
172 .setString(9, selectionExpression)
178 return new PortfolioDb(portfolioUuid, portfolioFromXml.key, selectionMode);
183 private void insertPortfolioProjects(Context context, ViewDef portfolio, PortfolioDb createdPortfolio) throws SQLException {
184 long now = system.now();
185 // select all already added project uuids
186 Set<String> alreadyAddedPortfolioProjects = new HashSet<>(
187 context.prepareSelect(SELECT_PROJECT_KEYS_BY_PORTFOLIO_UUID)
188 .setString(1, createdPortfolio.uuid)
189 .list(r -> r.getString(1)));
191 Set<String> projectKeysFromXml = new HashSet<>(portfolio.getProjects());
192 Set<String> projectKeysToBeAdded = Sets.difference(projectKeysFromXml, alreadyAddedPortfolioProjects);
194 if (!projectKeysToBeAdded.isEmpty()) {
195 List<ProjectDb> projects = findPortfolioProjects(context, projectKeysToBeAdded);
197 var upsert = context.prepareUpsert("insert into " +
198 "portfolio_projects(uuid, portfolio_uuid, project_uuid, created_at) " +
199 "values (?, ?, ?, ?)");
200 for (ProjectDb projectDb : projects) {
201 upsert.setString(1, uuidFactory.create())
202 .setString(2, createdPortfolio.uuid)
203 .setString(3, projectDb.uuid)
207 if (!projects.isEmpty()) {
214 private static List<ProjectDb> findPortfolioProjects(Context context, Set<String> projectKeysToBeAdded) {
215 return DatabaseUtils.executeLargeInputs(projectKeysToBeAdded, keys -> {
217 String selectQuery = SELECT_PROJECT_UUIDS_BY_KEYS.replace(PLACEHOLDER,
218 keys.stream().map(key -> "'" + key + "'").collect(
219 Collectors.joining(",")));
220 return context.prepareSelect(selectQuery)
221 .list(r -> new ProjectDb(r.getString(1), r.getString(2)));
222 } catch (SQLException e) {
223 throw new IllegalStateException("Could not execute 'in' query", e);
228 private void insertReferences(Context context, Map<String, ViewDef> portfolioXmlMap,
229 Map<String, PortfolioDb> portfolioDbMap) throws SQLException {
230 Upsert insertQuery = context.prepareUpsert("insert into portfolio_references(uuid, portfolio_uuid, reference_uuid, created_at) values (?, ?, ?, ?)");
232 long now = system.now();
233 boolean shouldExecuteQuery = false;
234 for (ViewDef portfolio : portfolioXmlMap.values()) {
235 var currentPortfolioUuid = portfolioDbMap.get(portfolio.key).uuid;
236 Set<String> referencesFromXml = new HashSet<>(portfolio.getReferences());
237 Set<String> referencesFromDb = new HashSet<>(context.prepareSelect("select pr.reference_uuid from portfolio_references pr where pr.portfolio_uuid = ?")
238 .setString(1, currentPortfolioUuid)
239 .list(row -> row.getString(1)));
241 for (String appOrPortfolio : referencesFromXml) {
242 // if portfolio and hasn't been added already
243 if (portfolioDbMap.containsKey(appOrPortfolio) && !referencesFromDb.contains(portfolioDbMap.get(appOrPortfolio).uuid)) {
245 .setString(1, uuidFactory.create())
246 .setString(2, currentPortfolioUuid)
247 .setString(3, portfolioDbMap.get(appOrPortfolio).uuid)
250 shouldExecuteQuery = true;
252 // if application exist and haven't been added
253 String appUuid = context.prepareSelect("select p.uuid from projects p where p.kee = ?")
254 .setString(1, appOrPortfolio)
255 .get(row -> row.getString(1));
256 if (appUuid != null && !referencesFromDb.contains(appUuid)) {
258 .setString(1, uuidFactory.create())
259 .setString(2, currentPortfolioUuid)
260 .setString(3, appUuid)
263 shouldExecuteQuery = true;
268 if (shouldExecuteQuery) {
276 private static void updateHierarchy(Context context, Map<String, ViewXml.ViewDef> defs, Map<String, PortfolioDb> portfolioDbMap) throws SQLException {
277 MassUpdate massUpdate = context.prepareMassUpdate();
278 massUpdate.select("select uuid, kee from portfolios where root_uuid = ? or parent_uuid = ?")
279 .setString(1, PLACEHOLDER)
280 .setString(2, PLACEHOLDER);
281 massUpdate.update("update portfolios set root_uuid = ?, parent_uuid = ? where uuid = ?");
282 massUpdate.execute((row, update) -> {
283 String currentPortfolioUuid = row.getString(1);
284 String currentPortfolioKey = row.getString(2);
286 var currentPortfolio = defs.get(currentPortfolioKey);
287 String parentUuid = ofNullable(currentPortfolio.parent).map(parent -> portfolioDbMap.get(parent).uuid).orElse(null);
288 String rootUuid = ofNullable(currentPortfolio.root).map(root -> portfolioDbMap.get(root).uuid).orElse(currentPortfolioUuid);
289 update.setString(1, rootUuid)
290 .setString(2, parentUuid)
291 .setString(3, currentPortfolioUuid);
297 private static String getViewsDefinition(DataChange.Context context) throws SQLException {
298 Select select = context.prepareSelect("select text_value,clob_value from internal_properties where kee=?");
299 select.setString(1, VIEWS_DEF_KEY);
300 return select.get(row -> {
301 String v = row.getString(1);
305 return row.getString(2);
310 private static class ComponentDb {
314 public ComponentDb(String uuid, boolean visibility) {
316 this.visibility = visibility;
320 private static class PortfolioDb {
323 SelectionMode selectionMode;
325 PortfolioDb(String uuid, String kee,
326 SelectionMode selectionMode) {
329 this.selectionMode = selectionMode;
333 private static class ProjectDb {
337 ProjectDb(String uuid, String kee) {
343 static class ViewXml {
344 static final String SCHEMA_VIEWS = "/static/views.xsd";
345 static final String VIEWS_HEADER_BARE = "<views>";
346 static final Pattern VIEWS_HEADER_BARE_PATTERN = Pattern.compile(VIEWS_HEADER_BARE);
347 static final String VIEWS_HEADER_FQ = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>"
348 + "<views xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns=\"http://sonarsource.com/schema/views\">";
351 // nothing to do here
354 private static Map<String, ViewDef> parse(String xml) throws ParserConfigurationException, SAXException, IOException, XMLStreamException {
355 if (StringUtils.isEmpty(xml)) {
356 return new LinkedHashMap<>(0);
361 SMInputFactory inputFactory = initStax();
362 SMHierarchicCursor rootC = inputFactory.rootElementCursor(new StringReader(xml));
363 rootC.advance(); // <views>
364 SMInputCursor cursor = rootC.childElementCursor();
365 views = parseViewDefinitions(cursor);
367 Map<String, ViewDef> result = new LinkedHashMap<>(views.size());
368 for (ViewDef def : views) {
369 result.put(def.key, def);
375 private static void validate(String xml) throws IOException, SAXException, ParserConfigurationException {
376 // Replace bare, namespace unaware header with fully qualified header (with schema declaration)
377 String fullyQualifiedXml = VIEWS_HEADER_BARE_PATTERN.matcher(xml).replaceFirst(VIEWS_HEADER_FQ);
378 try (InputStream xsd = MigratePortfoliosToNewTables.class.getResourceAsStream(SCHEMA_VIEWS)) {
379 InputSource viewsDefinition = new InputSource(new InputStreamReader(toInputStream(fullyQualifiedXml, UTF_8), UTF_8));
381 SchemaFactory saxSchemaFactory = SchemaFactory.newInstance(XMLConstants.W3C_XML_SCHEMA_NS_URI);
382 saxSchemaFactory.setProperty(XMLConstants.ACCESS_EXTERNAL_SCHEMA, "");
383 saxSchemaFactory.setProperty(XMLConstants.ACCESS_EXTERNAL_DTD, "");
385 SAXParserFactory parserFactory = SAXParserFactory.newInstance();
386 parserFactory.setFeature(FEATURE_SECURE_PROCESSING, true);
387 parserFactory.setNamespaceAware(true);
388 parserFactory.setSchema(saxSchemaFactory.newSchema(new SAXSource(new InputSource(xsd))));
390 SAXParser saxParser = parserFactory.newSAXParser();
391 saxParser.setProperty(XMLConstants.ACCESS_EXTERNAL_DTD, "");
392 saxParser.setProperty(XMLConstants.ACCESS_EXTERNAL_SCHEMA, "");
393 saxParser.parse(viewsDefinition, new ViewsValidator());
397 private static List<ViewDef> parseViewDefinitions(SMInputCursor viewsCursor) throws XMLStreamException {
398 List<ViewDef> views = new ArrayList<>();
399 while (viewsCursor.getNext() != null) {
400 ViewDef viewDef = new ViewDef();
401 viewDef.setKey(viewsCursor.getAttrValue("key"));
402 viewDef.setDef(Boolean.parseBoolean(viewsCursor.getAttrValue("def")));
403 viewDef.setParent(viewsCursor.getAttrValue("parent"));
404 viewDef.setRoot(viewsCursor.getAttrValue("root"));
405 SMInputCursor viewCursor = viewsCursor.childElementCursor();
406 while (viewCursor.getNext() != null) {
407 String nodeName = viewCursor.getLocalName();
408 parseChildElement(viewDef, viewCursor, nodeName);
415 private static void parseChildElement(ViewDef viewDef, SMInputCursor viewCursor, String nodeName) throws XMLStreamException {
416 if (StringUtils.equals(nodeName, "name")) {
417 viewDef.setName(trim(viewCursor.collectDescendantText()));
418 } else if (StringUtils.equals(nodeName, "desc")) {
419 viewDef.setDesc(trim(viewCursor.collectDescendantText()));
420 } else if (StringUtils.equals(nodeName, "regexp")) {
421 viewDef.setRegexp(trim(viewCursor.collectDescendantText()));
422 } else if (StringUtils.equals(nodeName, "language")) {
423 viewDef.setLanguage(trim(viewCursor.collectDescendantText()));
424 } else if (StringUtils.equals(nodeName, "tag_key")) {
425 viewDef.setTagKey(trim(viewCursor.collectDescendantText()));
426 } else if (StringUtils.equals(nodeName, "tag_value")) {
427 viewDef.setTagValue(trim(viewCursor.collectDescendantText()));
428 } else if (StringUtils.equals(nodeName, "p")) {
429 viewDef.addProject(trim(viewCursor.collectDescendantText()));
430 } else if (StringUtils.equals(nodeName, "vw-ref")) {
431 viewDef.addReference(trim(viewCursor.collectDescendantText()));
432 } else if (StringUtils.equals(nodeName, "qualifier")) {
433 viewDef.setQualifier(trim(viewCursor.collectDescendantText()));
434 } else if (StringUtils.equals(nodeName, "tagsAssociation")) {
435 parseTagsAssociation(viewDef, viewCursor);
439 private static void parseTagsAssociation(ViewDef def, SMInputCursor viewCursor) throws XMLStreamException {
440 SMInputCursor projectCursor = viewCursor.childElementCursor();
441 while (projectCursor.getNext() != null) {
442 def.addTagAssociation(trim(projectCursor.collectDescendantText()));
446 private static SMInputFactory initStax() {
447 XMLInputFactory xmlFactory = XMLInputFactory.newInstance();
448 xmlFactory.setProperty(XMLInputFactory.IS_COALESCING, Boolean.TRUE);
449 xmlFactory.setProperty(XMLInputFactory.IS_NAMESPACE_AWARE, Boolean.FALSE);
450 // just so it won't try to load DTD in if there's DOCTYPE
451 xmlFactory.setProperty(XMLInputFactory.SUPPORT_DTD, Boolean.FALSE);
452 xmlFactory.setProperty(XMLInputFactory.IS_VALIDATING, Boolean.FALSE);
453 return new SMInputFactory(xmlFactory);
456 static class ViewDef {
459 String parent = null;
465 List<String> p = new ArrayList<>();
467 List<String> vwRef = new ArrayList<>();
473 String regexp = null;
475 String language = null;
477 String tagKey = null;
479 String tagValue = null;
481 String qualifier = null;
483 Set<String> tagsAssociation = new TreeSet<>();
485 public List<String> getProjects() {
489 public List<String> getReferences() {
493 public ViewDef setKey(String key) {
498 public ViewDef setParent(String parent) {
499 this.parent = parent;
503 public ViewDef setRoot(@Nullable String root) {
508 public ViewDef setDef(boolean def) {
513 public ViewDef addProject(String project) {
518 public ViewDef setName(String name) {
523 public ViewDef setDesc(@Nullable String desc) {
528 public ViewDef setRegexp(@Nullable String regexp) {
529 this.regexp = regexp;
533 public ViewDef setLanguage(@Nullable String language) {
534 this.language = language;
538 public ViewDef setTagKey(@Nullable String tagKey) {
539 this.tagKey = tagKey;
543 public ViewDef setTagValue(@Nullable String tagValue) {
544 this.tagValue = tagValue;
548 public ViewDef addReference(String reference) {
549 this.vwRef.add(reference);
553 public ViewDef setQualifier(@Nullable String qualifier) {
554 this.qualifier = qualifier;
558 public ViewDef addTagAssociation(String tag) {
559 this.tagsAssociation.add(tag);
564 private static final class ViewsValidator extends DefaultHandler {
566 public void error(SAXParseException exception) throws SAXException {
571 public void warning(SAXParseException exception) throws SAXException {
576 public void fatalError(SAXParseException exception) throws SAXException {