SONAR-9136 replace AnyOne by default group in default template

5 files changed, 490 insertions, 2 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java
index e596a7024af..0f7f7817a1b 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java
@@ -69,6 +69,7 @@ public class DbVersion64 implements DbVersion {
       .add(1638, "Add column ORGANIZATIONS.NEW_PROJECT_PRIVATE", AddColumnNewProjectPrivate.class)
       .add(1639, "Set ORGANIZATIONS.NEW_PROJECT_PRIVATE to false", SetNewProjectPrivateToFalse.class)
       .add(1640, "Make column ORGANIZATIONS.NEW_PROJECT_PRIVATE not nullable", MakeColumnNewProjectPrivateNotNullable.class)
-      .add(1641, "Make components private based on permissions", MakeComponentsPrivateBasedOnPermissions.class);
+      .add(1641, "Make components private based on permissions", MakeComponentsPrivateBasedOnPermissions.class)
+      .add(1642, "Support private project in default permission template", SupportPrivateProjectInDefaultPermissionTemplate.class);
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/SupportPrivateProjectInDefaultPermissionTemplate.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/SupportPrivateProjectInDefaultPermissionTemplate.java
new file mode 100644
index 00000000000..6ddf5b4ec4d
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/SupportPrivateProjectInDefaultPermissionTemplate.java
@@ -0,0 +1,157 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2017 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v64;
+
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Date;
+import java.util.List;
+import javax.annotation.CheckForNull;
+import javax.annotation.Nullable;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.step.DataChange;
+import org.sonar.server.platform.db.migration.step.Select;
+import org.sonar.server.platform.db.migration.version.v63.DefaultOrganizationUuid;
+
+import static com.google.common.base.Preconditions.checkState;
+
+public class SupportPrivateProjectInDefaultPermissionTemplate extends DataChange {
+  private static final String PERMISSION_USER = "user";
+  private static final String PERMISSION_CODEVIEWER = "codeviewer";
+
+  private DefaultOrganizationUuid defaultOrganizationUuid;
+
+  public SupportPrivateProjectInDefaultPermissionTemplate(Database db, DefaultOrganizationUuid defaultOrganizationUuid) {
+    super(db);
+    this.defaultOrganizationUuid = defaultOrganizationUuid;
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    String defaultOrganizationUuid = this.defaultOrganizationUuid.get(context);
+
+    ResolvedOrganizationProperties organizationProperties = readOrganizationProperties(context, defaultOrganizationUuid);
+
+    int defaultGroupId = organizationProperties.defaultGroupId;
+    for (Integer groupId : Arrays.asList(organizationProperties.getProjectId(), organizationProperties.getViewId())) {
+      if (groupId != null) {
+        insertGroupPermissionIfNotPresent(context, defaultGroupId, groupId, PERMISSION_USER);
+        insertGroupPermissionIfNotPresent(context, defaultGroupId, groupId, PERMISSION_CODEVIEWER);
+      }
+    }
+  }
+
+  private static void insertGroupPermissionIfNotPresent(Context context, int groupId, int templateId, String permission) throws SQLException {
+    if (!groupHasPermissionInTemplate(context, templateId, groupId, permission)) {
+      insertGroupPermission(context, templateId, groupId, permission);
+    }
+  }
+
+  private static boolean groupHasPermissionInTemplate(Context context, int templateId, int groupId, String permission) throws SQLException {
+    List<Integer> rows = context.prepareSelect("select 1 from perm_templates_groups where" +
+      " template_id = ?" +
+      " and group_id=?" +
+      " and permission_reference=?")
+      .setInt(1, templateId)
+      .setInt(2, groupId)
+      .setString(3, permission)
+      .list(row -> row.getInt(1));
+    return !rows.isEmpty();
+  }
+
+  private static void insertGroupPermission(Context context, int templateId, int groupId, String permission) throws SQLException {
+    Date now = new Date();
+    context.prepareUpsert("insert into perm_templates_groups (group_id, template_id, permission_reference, created_at, updated_at) values (?,?,?,?,?)")
+      .setInt(1, groupId)
+      .setInt(2, templateId)
+      .setString(3, permission)
+      .setDate(4, now)
+      .setDate(5, now)
+      .execute()
+      .commit();
+  }
+
+  private static ResolvedOrganizationProperties readOrganizationProperties(Context context, String defaultOrganizationUuid) throws SQLException {
+    Select select = context.prepareSelect("select default_group_id, default_perm_template_project, default_perm_template_view from organizations where uuid=?")
+      .setString(1, defaultOrganizationUuid);
+    List<OrganizationProperties> rows = select
+      .list(row -> new OrganizationProperties(row.getNullableInt(1), row.getNullableString(2), row.getNullableString(3)));
+    checkState(!rows.isEmpty(), "Default organization with uuid '%s' does not exist in table ORGANIZATIONS", defaultOrganizationUuid);
+    OrganizationProperties rawProperties = rows.iterator().next();
+    checkState(rawProperties.defaultGroupId != null, "No default group id is defined for default organization (uuid=%s)", defaultOrganizationUuid);
+    checkState(rawProperties.projectUuid != null || rawProperties.viewUuid == null,
+      "Inconsistent state for default organization (uuid=%s): no project default template is defined but view default template is", defaultOrganizationUuid);
+    return new ResolvedOrganizationProperties(
+      rawProperties.defaultGroupId,
+      getPermTemplateId(context, rawProperties.projectUuid),
+      getPermTemplateId(context, rawProperties.viewUuid));
+  }
+
+  @CheckForNull
+  private static Integer getPermTemplateId(Context context, @Nullable String permissionTemplateUuid) throws SQLException {
+    if (permissionTemplateUuid == null) {
+      return null;
+    }
+    List<Integer> ids = context.prepareSelect("select id from permission_templates where kee=?")
+      .setString(1, permissionTemplateUuid)
+      .list(row -> row.getInt(1));
+    checkState(!ids.isEmpty(), "Permission template with uuid %s not found", permissionTemplateUuid);
+    checkState(ids.size() == 1, "Multiple permission templates found with uuid %s", permissionTemplateUuid);
+    return ids.iterator().next();
+  }
+
+  private static final class OrganizationProperties {
+    private final Integer defaultGroupId;
+    private final String projectUuid;
+    private final String viewUuid;
+
+    private OrganizationProperties(@Nullable Integer defaultGroupId, @Nullable String projectUuid, @Nullable String viewUuid) {
+      this.defaultGroupId = defaultGroupId;
+      this.projectUuid = projectUuid;
+      this.viewUuid = viewUuid;
+    }
+  }
+
+  private static final class ResolvedOrganizationProperties {
+    private final int defaultGroupId;
+    private final Integer projectId;
+    private final Integer viewId;
+
+    private ResolvedOrganizationProperties(int defaultGroupId, @Nullable Integer projectId, @Nullable Integer viewId) {
+      this.defaultGroupId = defaultGroupId;
+      this.projectId = projectId;
+      this.viewId = viewId;
+    }
+
+    int getDefaultGroupId() {
+      return defaultGroupId;
+    }
+
+    Integer getProjectId() {
+      return projectId;
+    }
+
+    @CheckForNull
+
+    Integer getViewId() {
+      return viewId;
+    }
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java
index 336f8b89dbd..adbf5e94fee 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java
@@ -35,6 +35,6 @@ public class DbVersion64Test {
 
   @Test
   public void verify_migration_count() {
-    verifyMigrationCount(underTest, 42);
+    verifyMigrationCount(underTest, 43);
   }
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/SupportPrivateProjectInDefaultPermissionTemplateTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/SupportPrivateProjectInDefaultPermissionTemplateTest.java
new file mode 100644
index 00000000000..1dc8ae5d97c
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/SupportPrivateProjectInDefaultPermissionTemplateTest.java
@@ -0,0 +1,275 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2017 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v64;
+
+import java.sql.SQLException;
+import java.util.Random;
+import java.util.stream.Stream;
+import javax.annotation.Nullable;
+import org.junit.Rule;
+import org.junit.Test;
+import org.junit.rules.ExpectedException;
+import org.sonar.core.util.stream.MoreCollectors;
+import org.sonar.db.CoreDbTester;
+import org.sonar.server.platform.db.migration.version.v63.DefaultOrganizationUuidImpl;
+
+import static java.lang.String.valueOf;
+import static org.apache.commons.lang.math.RandomUtils.nextLong;
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class SupportPrivateProjectInDefaultPermissionTemplateTest {
+
+  private static final String DEFAULT_ORGANIZATION_UUID = "def org uuid";
+  private static final String OTHER_ORGANIZATION_UUID = "not def org uuid";
+  private static final String PERMISSION_USER = "user";
+  private static final String PERMISSION_CODEVIEWER = "codeviewer";
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(SupportPrivateProjectInDefaultPermissionTemplateTest.class, "organizations_and_groups_and_permission_templates.sql");
+  @Rule
+  public ExpectedException expectedException = ExpectedException.none();
+
+  private SupportPrivateProjectInDefaultPermissionTemplate underTest = new SupportPrivateProjectInDefaultPermissionTemplate(db.database(), new DefaultOrganizationUuidImpl());
+
+  @Test
+  public void fails_with_ISE_when_no_default_organization_is_set() throws SQLException {
+    expectedException.expect(IllegalStateException.class);
+    expectedException.expectMessage("Default organization uuid is missing");
+
+    underTest.execute();
+  }
+
+  @Test
+  public void fails_with_ISE_when_default_organization_does_not_exist_in_table_ORGANIZATIONS() throws SQLException {
+    setDefaultOrganizationProperty("blabla");
+
+    expectedException.expect(IllegalStateException.class);
+    expectedException.expectMessage("Default organization with uuid 'blabla' does not exist in table ORGANIZATIONS");
+
+    underTest.execute();
+  }
+
+  @Test
+  public void execute_fails_with_ISE_when_default_organization_has_no_default_groupId() throws SQLException {
+    setupDefaultOrganization(null, "pt1", "pt2");
+
+    expectedException.expect(IllegalStateException.class);
+    expectedException.expectMessage("No default group id is defined for default organization (uuid=def org uuid)");
+
+    underTest.execute();
+  }
+
+  @Test
+  public void execute_fails_with_ISE_when_default_group_of_default_organization_does_not_exist() throws SQLException {
+    setupDefaultOrganization(112, "pT1", "pT2");
+
+    expectedException.expect(IllegalStateException.class);
+    expectedException.expectMessage("Permission template with uuid pT1 not found");
+
+    underTest.execute();
+  }
+
+  @Test
+  public void execute_does_nothing_when_default_organization_has_default_permission_template_for_projects() throws SQLException {
+    int groupId = insertGroup(DEFAULT_ORGANIZATION_UUID);
+    setupDefaultOrganization(groupId, null, null);
+
+    underTest.execute();
+  }
+
+  @Test
+  public void execute_fails_with_ISE_when_default_organization_has_default_permission_template_for_views_but_not_for_projects() throws SQLException {
+    int groupId = insertGroup(DEFAULT_ORGANIZATION_UUID);
+    setupDefaultOrganization(groupId, null, "pt1");
+
+    expectedException.expect(IllegalStateException.class);
+    expectedException.expectMessage("Inconsistent state for default organization (uuid=def org uuid): no project default template is defined but view default template is");
+
+    underTest.execute();
+  }
+
+  @Test
+  public void execute_fails_with_ISE_when_default_permission_template_for_projects_of_default_organization_does_not_exist() throws SQLException {
+    int groupId = insertGroup(DEFAULT_ORGANIZATION_UUID);
+    setupDefaultOrganization(groupId, "foBar2000", "pt2");
+
+    expectedException.expect(IllegalStateException.class);
+    expectedException.expectMessage("Permission template with uuid foBar2000 not found");
+
+    underTest.execute();
+  }
+
+  @Test
+  public void execute_does_not_fail_when_default_organization_has_default_permission_template_for_view() throws SQLException {
+    int groupId = insertGroup(DEFAULT_ORGANIZATION_UUID);
+    IdAndUuid projectDefPermTemplate = insertPermissionTemplate(DEFAULT_ORGANIZATION_UUID);
+    setupDefaultOrganization(groupId, projectDefPermTemplate.uuid, null);
+
+    underTest.execute();
+  }
+
+  @Test
+  public void execute_adds_permission_USER_and_CODEVIEWER_to_default_group_of_default_organization_in_its_default_project_template() throws SQLException {
+    int groupId = insertGroup(DEFAULT_ORGANIZATION_UUID);
+    IdAndUuid projectDefPermTemplate = insertPermissionTemplate(DEFAULT_ORGANIZATION_UUID);
+    setupDefaultOrganization(groupId, projectDefPermTemplate.uuid, null);
+    int otherGroupId = insertGroup(OTHER_ORGANIZATION_UUID);
+    IdAndUuid otherProjectDefPermTemplate = insertPermissionTemplate(OTHER_ORGANIZATION_UUID);
+    insertOrganization(OTHER_ORGANIZATION_UUID, otherGroupId, otherProjectDefPermTemplate.uuid, null);
+
+    underTest.execute();
+
+    verifyPermissionOfGroupInTemplate(projectDefPermTemplate, groupId, PERMISSION_USER, PERMISSION_CODEVIEWER);
+    verifyPermissionOfGroupInTemplate(otherProjectDefPermTemplate, otherGroupId);
+  }
+
+  @Test
+  public void execute_does_not_fail_if_default_group_already_has_permission_USER_and_adds_only_CODEVIEWER_to_default_group_of_default_organization_in_its_default_project_template()
+    throws SQLException {
+    int groupId = insertGroup(DEFAULT_ORGANIZATION_UUID);
+    IdAndUuid projectDefPermTemplate = insertPermissionTemplate(DEFAULT_ORGANIZATION_UUID);
+    insertGroupPermission(projectDefPermTemplate, groupId, PERMISSION_USER);
+    setupDefaultOrganization(groupId, projectDefPermTemplate.uuid, null);
+    int otherGroupId = insertGroup(OTHER_ORGANIZATION_UUID);
+    IdAndUuid otherProjectDefPermTemplateUuid = insertPermissionTemplate(OTHER_ORGANIZATION_UUID);
+    insertOrganization(OTHER_ORGANIZATION_UUID, otherGroupId, otherProjectDefPermTemplateUuid.uuid, null);
+
+    underTest.execute();
+
+    verifyPermissionOfGroupInTemplate(projectDefPermTemplate, groupId, PERMISSION_USER, PERMISSION_CODEVIEWER);
+    verifyPermissionOfGroupInTemplate(otherProjectDefPermTemplateUuid, otherGroupId);
+  }
+
+  @Test
+  public void execute_does_not_fail_if_default_group_already_has_permission_CODEVIEWER_and_adds_only_USER_to_default_group_of_default_organization_in_its_default_project_template()
+    throws SQLException {
+    int groupId = insertGroup(DEFAULT_ORGANIZATION_UUID);
+    IdAndUuid projectDefPermTemplate = insertPermissionTemplate(DEFAULT_ORGANIZATION_UUID);
+    insertGroupPermission(projectDefPermTemplate, groupId, PERMISSION_CODEVIEWER);
+    setupDefaultOrganization(groupId, projectDefPermTemplate.uuid, null);
+    int otherGroupId = insertGroup(OTHER_ORGANIZATION_UUID);
+    IdAndUuid otherProjectDefPermTemplateUuid = insertPermissionTemplate(OTHER_ORGANIZATION_UUID);
+    insertOrganization(OTHER_ORGANIZATION_UUID, otherGroupId, otherProjectDefPermTemplateUuid.uuid, null);
+
+    underTest.execute();
+
+    verifyPermissionOfGroupInTemplate(projectDefPermTemplate, groupId, PERMISSION_USER, PERMISSION_CODEVIEWER);
+    verifyPermissionOfGroupInTemplate(otherProjectDefPermTemplateUuid, otherGroupId);
+  }
+
+  @Test
+  public void execute_is_reentrant()
+    throws SQLException {
+    int groupId = insertGroup(DEFAULT_ORGANIZATION_UUID);
+    IdAndUuid projectDefPermTemplate = insertPermissionTemplate(DEFAULT_ORGANIZATION_UUID);
+    setupDefaultOrganization(groupId, projectDefPermTemplate.uuid, null);
+    int otherGroupId = insertGroup(OTHER_ORGANIZATION_UUID);
+    IdAndUuid otherProjectDefPermTemplateUuid = insertPermissionTemplate(OTHER_ORGANIZATION_UUID);
+    insertOrganization(OTHER_ORGANIZATION_UUID, otherGroupId, otherProjectDefPermTemplateUuid.uuid, null);
+
+    underTest.execute();
+
+    underTest.execute();
+
+    verifyPermissionOfGroupInTemplate(projectDefPermTemplate, groupId, PERMISSION_USER, PERMISSION_CODEVIEWER);
+    verifyPermissionOfGroupInTemplate(otherProjectDefPermTemplateUuid, otherGroupId);
+  }
+
+  private void insertGroupPermission(IdAndUuid permissionTemplate, int groupId, String permission) {
+    db.executeInsert(
+      "PERM_TEMPLATES_GROUPS",
+      "GROUP_ID", groupId,
+      "TEMPLATE_ID", permissionTemplate.id,
+      "PERMISSION_REFERENCE", permission);
+  }
+
+  private void verifyPermissionOfGroupInTemplate(IdAndUuid permTemplate, int groupId, String... permissions) {
+    verifyPermissionOfGroupInTemplate(permTemplate.uuid, groupId, permissions);
+  }
+
+  private void verifyPermissionOfGroupInTemplate(String permTemplateUuid, int groupId, String... permissions) {
+    assertThat(
+      db.select("select permission_reference as \"permission\" from perm_templates_groups ptg inner join permission_templates pt on pt.kee='" + permTemplateUuid
+        + "' where ptg.template_id=pt.id and group_id=" + groupId)
+        .stream()
+        .flatMap(row -> Stream.of((String) row.get("permission")))
+        .collect(MoreCollectors.toList()))
+          .containsOnly(permissions);
+  }
+
+  private void setupDefaultOrganization(@Nullable Integer defaultGroupId, @Nullable String projectPermTemplateUuid, @Nullable String viewPermTemplateUuid) {
+    setDefaultOrganizationProperty(DEFAULT_ORGANIZATION_UUID);
+    insertOrganization(DEFAULT_ORGANIZATION_UUID, defaultGroupId, projectPermTemplateUuid, viewPermTemplateUuid);
+  }
+
+  private void setDefaultOrganizationProperty(String defaultOrganizationUuid) {
+    db.executeInsert(
+      "INTERNAL_PROPERTIES",
+      "KEE", "organization.default",
+      "IS_EMPTY", "false",
+      "TEXT_VALUE", defaultOrganizationUuid);
+  }
+
+  private void insertOrganization(String uuid, @Nullable Integer defaultGroupId, @Nullable String projectPermTemplateUuid, @Nullable String viewPermTemplateUuid) {
+    db.executeInsert("ORGANIZATIONS",
+      "UUID", uuid,
+      "KEE", uuid,
+      "NAME", uuid,
+      "GUARDED", false,
+      "default_group_id", defaultGroupId == null ? null : valueOf(defaultGroupId),
+      "default_perm_template_project", projectPermTemplateUuid,
+      "default_perm_template_view", viewPermTemplateUuid,
+      "CREATED_AT", nextLong(),
+      "UPDATED_AT", nextLong());
+  }
+
+  private int insertGroup(String organizationUuid) {
+    String name = "name" + new Random().nextInt(122);
+    db.executeInsert(
+      "GROUPS",
+      "ORGANIZATION_UUID", organizationUuid,
+      "NAME", name);
+
+    return ((Long) db.selectFirst("select id as \"ID\" from groups where name='" + name + "'").get("ID")).intValue();
+  }
+
+  private IdAndUuid insertPermissionTemplate(String organizationUuid) {
+    int i = new Random().nextInt(1222);
+    String uuid = "ptUuid" + i;
+    db.executeInsert(
+      "PERMISSION_TEMPLATES",
+      "ORGANIZATION_UUID", organizationUuid,
+      "NAME", "name" + i,
+      "KEE", uuid);
+    return new IdAndUuid(
+      ((Long) db.selectFirst("select id as \"ID\" from permission_templates where kee='" + uuid + "'").get("ID")).intValue(),
+      uuid);
+  }
+
+  private static final class IdAndUuid {
+    private final int id;
+    private final String uuid;
+
+    private IdAndUuid(int id, String uuid) {
+      this.id = id;
+      this.uuid = uuid;
+    }
+  }
+}
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/SupportPrivateProjectInDefaultPermissionTemplateTest/organizations_and_groups_and_permission_templates.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/SupportPrivateProjectInDefaultPermissionTemplateTest/organizations_and_groups_and_permission_templates.sql
new file mode 100644
index 00000000000..93ab5a73b9f
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/SupportPrivateProjectInDefaultPermissionTemplateTest/organizations_and_groups_and_permission_templates.sql
@@ -0,0 +1,55 @@
+CREATE TABLE "ORGANIZATIONS" (
+  "UUID" VARCHAR(40) NOT NULL PRIMARY KEY,
+  "KEE" VARCHAR(32) NOT NULL,
+  "NAME" VARCHAR(64) NOT NULL,
+  "DESCRIPTION" VARCHAR(256),
+  "URL" VARCHAR(256),
+  "AVATAR_URL" VARCHAR(256),
+  "GUARDED" BOOLEAN NOT NULL,
+  "USER_ID" INTEGER,
+  "DEFAULT_PERM_TEMPLATE_PROJECT" VARCHAR(40),
+  "DEFAULT_PERM_TEMPLATE_VIEW" VARCHAR(40),
+  "DEFAULT_GROUP_ID" INTEGER,
+  "CREATED_AT" BIGINT NOT NULL,
+  "UPDATED_AT" BIGINT NOT NULL
+);
+CREATE UNIQUE INDEX "PK_ORGANIZATIONS" ON "ORGANIZATIONS" ("UUID");
+CREATE UNIQUE INDEX "ORGANIZATION_KEY" ON "ORGANIZATIONS" ("KEE");
+
+CREATE TABLE "INTERNAL_PROPERTIES" (
+  "KEE" VARCHAR(50) NOT NULL PRIMARY KEY,
+  "IS_EMPTY" BOOLEAN NOT NULL,
+  "TEXT_VALUE" VARCHAR(4000),
+  "CLOB_VALUE" CLOB,
+  "CREATED_AT" BIGINT
+);
+CREATE UNIQUE INDEX "UNIQ_INTERNAL_PROPERTIES" ON "INTERNAL_PROPERTIES" ("KEE");
+
+CREATE TABLE "GROUPS" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "ORGANIZATION_UUID" VARCHAR(40) NOT NULL,
+  "NAME" VARCHAR(500),
+  "DESCRIPTION" VARCHAR(200),
+  "CREATED_AT" TIMESTAMP,
+  "UPDATED_AT" TIMESTAMP
+);
+
+CREATE TABLE "PERMISSION_TEMPLATES" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "ORGANIZATION_UUID" VARCHAR(40) NOT NULL,
+  "NAME" VARCHAR(100) NOT NULL,
+  "KEE" VARCHAR(100) NOT NULL,
+  "DESCRIPTION" VARCHAR(4000),
+  "KEY_PATTERN" VARCHAR(500),
+  "CREATED_AT" TIMESTAMP,
+  "UPDATED_AT" TIMESTAMP
+);
+
+CREATE TABLE "PERM_TEMPLATES_GROUPS" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "GROUP_ID" INTEGER,
+  "TEMPLATE_ID" INTEGER NOT NULL,
+  "PERMISSION_REFERENCE" VARCHAR(64) NOT NULL,
+  "CREATED_AT" TIMESTAMP,
+  "UPDATED_AT" TIMESTAMP
+);