diff options
| author | Simon Brandhof <simon.brandhof@sonarsource.com> | 2015-07-04 00:34:24 +0200 |
|---|---|---|
| committer | Simon Brandhof <simon.brandhof@sonarsource.com> | 2015-07-04 17:00:08 +0200 |
| commit | 1df148803610cd54f182b8636f01c0e6ece92b19 (patch) | |
| tree | 8b6d2919ebe3575556b8796fd95a2b89996933ff /sonar-db/src/main/resources/org/sonar/db | |
| parent | 1018747567d50056a49aa7c8421d596f18f25344 (diff) | |
| download | sonarqube-1df148803610cd54f182b8636f01c0e6ece92b19.tar.gz sonarqube-1df148803610cd54f182b8636f01c0e6ece92b19.zip | |
Extract module sonar-db
Diffstat (limited to 'sonar-db/src/main/resources/org/sonar/db')
55 files changed, 5830 insertions, 0 deletions
diff --git a/sonar-db/src/main/resources/org/sonar/db/IsAliveMapper.xml b/sonar-db/src/main/resources/org/sonar/db/IsAliveMapper.xml new file mode 100644 index 00000000000..c26b5961f8a --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/IsAliveMapper.xml @@ -0,0 +1,16 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.IsAliveMapper"> + + <select id="isAlive" resultType="int"> + select 1 + <choose> + <when test="_databaseId == 'oracle'"> + from dual + </when> + </choose> + </select> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/activity/ActivityMapper.xml b/sonar-db/src/main/resources/org/sonar/db/activity/ActivityMapper.xml new file mode 100644 index 00000000000..0014464aa2c --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/activity/ActivityMapper.xml @@ -0,0 +1,15 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.activity.ActivityMapper"> + + <insert id="insert" parameterType="Activity" useGeneratedKeys="false"> + insert into activities + (created_at, log_key, log_type, log_action, user_login, data_field, log_message) + values (#{createdAt,jdbcType=TIMESTAMP}, #{key,jdbcType=VARCHAR}, #{type,jdbcType=VARCHAR}, + #{action,jdbcType=VARCHAR}, + #{author,jdbcType=VARCHAR}, #{data,jdbcType=VARCHAR}, #{message,jdbcType=VARCHAR}) + </insert> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/component/ComponentIndexMapper.xml b/sonar-db/src/main/resources/org/sonar/db/component/ComponentIndexMapper.xml new file mode 100644 index 00000000000..2a308781795 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/component/ComponentIndexMapper.xml @@ -0,0 +1,16 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> +<mapper namespace="org.sonar.db.component.ComponentIndexMapper"> + + <select id="selectProjectIdsFromQueryAndViewOrSubViewUuid" parameterType="map" resultType="long"> + SELECT r.resource_id FROM resource_index r + INNER JOIN projects copy ON copy.copy_resource_id = r.resource_id + <where> + AND copy.module_uuid_path LIKE #{viewUuidQuery} + AND r.kee LIKE #{query} + </where> + ORDER BY r.name_size + </select> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/component/ComponentLinkMapper.xml b/sonar-db/src/main/resources/org/sonar/db/component/ComponentLinkMapper.xml new file mode 100644 index 00000000000..c6db203313b --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/component/ComponentLinkMapper.xml @@ -0,0 +1,39 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> +<mapper namespace="org.sonar.db.component.ComponentLinkMapper"> + + <sql id="componentLinkColumns"> + p.id, + p.component_uuid as "componentUuid", + p.link_type as "type", + p.name as name, + p.href as href + </sql> + + <select id="selectByComponentUuid" parameterType="String" resultType="ComponentLink"> + SELECT + <include refid="componentLinkColumns"/> + FROM project_links p + <where> + AND p.component_uuid=#{uuid} + </where> + </select> + + <insert id="insert" parameterType="ComponentLink" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO project_links (component_uuid, link_type, name, href) + VALUES (#{componentUuid,jdbcType=VARCHAR}, #{type,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, + #{href,jdbcType=VARCHAR}) + </insert> + + <insert id="update" parameterType="ComponentLink" useGeneratedKeys="false"> + UPDATE project_links SET component_uuid=#{componentUuid,jdbcType=VARCHAR}, link_type=#{type,jdbcType=VARCHAR}, + name=#{name,jdbcType=VARCHAR}, href=#{href,jdbcType=VARCHAR} + WHERE id=#{id} + </insert> + + <delete id="delete"> + DELETE FROM project_links WHERE id=#{id} + </delete> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml b/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml new file mode 100644 index 00000000000..19740c7f76e --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml @@ -0,0 +1,315 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> +<mapper namespace="org.sonar.db.component.ComponentMapper"> + + <sql id="componentColumns"> + p.id, + p.uuid as uuid, + p.project_uuid as projectUuid, + p.module_uuid as moduleUuid, + p.module_uuid_path as moduleUuidPath, + p.kee as kee, + p.deprecated_kee as deprecatedKey, + p.name as name, + p.long_name as longName, + p.description as description, + p.qualifier as qualifier, + p.scope as scope, + p.language as language, + p.root_id as parentProjectId, + p.path as path, + p.enabled as enabled, + p.copy_resource_id as copyResourceId, + p.authorization_updated_at as authorizationUpdatedAt, + p.created_at as createdAt + </sql> + + <sql id="authorizedComponentColumns"> + p.id, + p.uuid as uuid, + p.kee as kee, + p.qualifier as qualifier, + p.scope as scope + </sql> + + <select id="selectByKey" parameterType="String" resultType="Component"> + SELECT + <include refid="componentColumns"/> + FROM projects p + <where> + AND p.kee=#{key} + </where> + </select> + + <select id="selectById" parameterType="long" resultType="Component"> + SELECT + <include refid="componentColumns"/> + FROM projects p + <where> + AND p.id=#{id} + </where> + </select> + + <select id="selectByUuid" parameterType="String" resultType="Component"> + SELECT + <include refid="componentColumns"/> + FROM projects p + <where> + AND p.uuid=#{uuid} + </where> + </select> + + <select id="countById" parameterType="long" resultType="long"> + SELECT count(p.id) + FROM projects p + <where> + AND p.id=#{id} + </where> + </select> + + <select id="selectByKeys" parameterType="String" resultType="Component"> + select + <include refid="componentColumns"/> + from projects p + <where> + p.enabled=${_true} + and p.kee in + <foreach collection="keys" open="(" close=")" item="key" separator=","> + #{key} + </foreach> + </where> + </select> + + <select id="selectByIds" parameterType="long" resultType="Component"> + select + <include refid="componentColumns"/> + from projects p + <where> + p.enabled=${_true} + and p.id in + <foreach collection="ids" open="(" close=")" item="id" separator=","> + #{id} + </foreach> + </where> + </select> + + <select id="selectByUuids" parameterType="String" resultType="Component"> + select + <include refid="componentColumns"/> + from projects p + <where> + and p.uuid in + <foreach collection="uuids" open="(" close=")" item="uuid" separator=","> + #{uuid} + </foreach> + </where> + </select> + + <select id="selectExistingUuids" parameterType="String" resultType="String"> + select p.uuid + from projects p + <where> + and p.uuid in + <foreach collection="uuids" open="(" close=")" item="uuid" separator=","> + #{uuid} + </foreach> + </where> + </select> + + <select id="selectSubProjectsByComponentUuids" parameterType="String" resultType="Component"> + SELECT + <include refid="componentColumns"/> + FROM projects p + INNER JOIN projects child ON child.root_id=p.id AND child.enabled=${_true} + <where> + AND p.enabled=${_true} + AND p.scope='PRJ' + AND child.uuid in + <foreach collection="uuids" open="(" close=")" item="uuid" separator=","> + #{uuid} + </foreach> + </where> + </select> + + <select id="selectDescendantModules" parameterType="map" resultType="Component"> + SELECT + <include refid="componentColumns"/> + FROM projects p + <include refid="modulesTreeQuery"/> + </select> + + <sql id="modulesTreeQuery"> + INNER JOIN projects module ON module.project_uuid = p.project_uuid AND module.uuid = #{moduleUuid} AND + module.scope='PRJ' AND module.enabled = ${_true} + <where> + <if test="excludeDisabled"> + p.enabled = ${_true} + </if> + AND p.scope = #{scope} + AND + <choose> + <when test="_databaseId == 'mssql'"> + p.module_uuid_path LIKE module.module_uuid_path + '%' + </when> + <when test="_databaseId == 'mysql'"> + p.module_uuid_path LIKE concat(module.module_uuid_path, '%') + </when> + <otherwise> + p.module_uuid_path LIKE module.module_uuid_path || '%' + </otherwise> + </choose> + </where> + </sql> + + <select id="selectEnabledFilesFromProject" parameterType="map" resultType="FilePathWithHash"> + SELECT p.uuid, p.path, p.module_uuid as moduleUuid, fs.src_hash as srcHash + FROM projects p + INNER JOIN file_sources fs ON fs.file_uuid=p.uuid + <where> + AND p.project_uuid=#{projectUuid} + AND p.enabled=${_true} + AND p.scope='FIL' + </where> + </select> + + <select id="selectDescendantFiles" parameterType="map" resultType="FilePathWithHash"> + SELECT p.uuid, p.path, p.module_uuid as moduleUuid, fs.src_hash as srcHash + FROM projects p + INNER JOIN file_sources fs ON fs.file_uuid=p.uuid and fs.data_type='SOURCE' + <include refid="modulesTreeQuery"/> + </select> + + <select id="selectProjectUuids" resultType="String"> + SELECT p.uuid + FROM projects p + <where> + AND p.enabled=${_true} + AND p.scope='PRJ' + AND p.qualifier='TRK' + </where> + </select> + + <select id="selectUuidsForQualifiers" resultType="UuidWithProjectUuid"> + SELECT p.uuid as "uuid", p.project_uuid as "projectUuid" FROM projects p + INNER JOIN snapshots s on s.project_id=p.id AND s.islast=${_true} + <where> + p.enabled=${_true} AND + <foreach collection="qualifiers" open="(" close=")" item="qualifier" separator="OR "> + s.qualifier=#{qualifier} + </foreach> + </where> + </select> + + <select id="selectProjectsFromView" resultType="String"> + SELECT p.uuid FROM projects technical_projects + INNER JOIN projects p on p.id=technical_projects.copy_resource_id AND p.enabled=${_true} + <where> + technical_projects.enabled=${_true} AND technical_projects.project_uuid=#{projectViewUuid} + AND technical_projects.module_uuid_path LIKE #{viewUuidLikeQuery} + </where> + </select> + + <select id="selectComponentsFromProjectKeyAndScope" parameterType="map" resultType="Component"> + SELECT + <include refid="componentColumns"/> + FROM projects p + INNER JOIN projects root ON root.uuid=p.project_uuid AND root.kee=#{projectKey} + <where> + AND p.enabled=${_true} + <if test="scope != null"> + AND p.scope=#{scope} + </if> + </where> + </select> + + <select id="selectProvisionedProjects" parameterType="map" resultType="Component"> + select + <include refid="componentColumns"/> + from projects p + <include refid="provisionClauses"/> + </select> + + <select id="countProvisionedProjects" parameterType="map" resultType="int"> + select count(p.id) + from projects p + <include refid="provisionClauses"/> + </select> + + <sql id="provisionClauses"> + left join snapshots s on s.project_id=p.id + where + s.id is null + and p.enabled=${_true} + and p.qualifier=#{qualifier} + and p.copy_resource_id is null + <if test="query!=null"> + and ( + UPPER(p.name) like #{query} + or UPPER(p.kee) like #{query} + ) + </if> + </sql> + + <select id="selectGhostProjects" parameterType="map" resultType="Component"> + select distinct + <include refid="componentColumns"/> + from projects p + <include refid="ghostClauses"/> + </select> + + <select id="countGhostProjects" parameterType="map" resultType="long"> + select count(p.id) + from projects p + <include refid="ghostClauses"/> + </select> + + <sql id="ghostClauses"> + inner join snapshots s1 on s1.project_id = p.id and s1.status='U' + left join snapshots s2 on s2.project_id = p.id and s2.status='P' + where + s2.id is null + and p.qualifier=#{qualifier} + and p.copy_resource_id is null + <if test="query!=null"> + and ( + UPPER(p.name) like #{query} + or UPPER(p.kee) like #{query} + ) + </if> + </sql> + + <insert id="insert" parameterType="Component" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO projects (kee, deprecated_kee, uuid, project_uuid, module_uuid, module_uuid_path, name, long_name, + qualifier, scope, language, description, root_id, path, copy_resource_id, enabled, + created_at, authorization_updated_at) + VALUES (#{kee,jdbcType=VARCHAR}, #{deprecatedKey,jdbcType=VARCHAR}, #{uuid,jdbcType=VARCHAR}, + #{projectUuid,jdbcType=VARCHAR}, #{moduleUuid,jdbcType=VARCHAR}, #{moduleUuidPath,jdbcType=VARCHAR}, + #{name,jdbcType=VARCHAR}, #{longName,jdbcType=VARCHAR}, #{qualifier,jdbcType=VARCHAR}, #{scope,jdbcType=VARCHAR}, + #{language,jdbcType=VARCHAR}, #{description,jdbcType=VARCHAR}, + #{parentProjectId,jdbcType=BIGINT}, #{path,jdbcType=VARCHAR}, #{copyResourceId,jdbcType=BIGINT}, + #{enabled,jdbcType=BOOLEAN}, + #{createdAt,jdbcType=TIMESTAMP}, #{authorizationUpdatedAt,jdbcType=BIGINT}) + </insert> + + <insert id="update" parameterType="Component" useGeneratedKeys="false"> + UPDATE projects SET + kee=#{kee,jdbcType=VARCHAR}, + deprecated_kee=#{deprecatedKey,jdbcType=VARCHAR}, + project_uuid=#{projectUuid,jdbcType=VARCHAR}, + module_uuid=#{moduleUuid,jdbcType=VARCHAR}, + module_uuid_path=#{moduleUuidPath,jdbcType=VARCHAR}, + name=#{name,jdbcType=VARCHAR}, + long_name=#{longName,jdbcType=VARCHAR}, + qualifier=#{qualifier,jdbcType=VARCHAR}, + scope=#{scope,jdbcType=VARCHAR}, + language=#{language,jdbcType=VARCHAR}, + description=#{description,jdbcType=VARCHAR}, + root_id=#{parentProjectId,jdbcType=BIGINT}, + path=#{path,jdbcType=VARCHAR}, + copy_resource_id=#{copyResourceId,jdbcType=BIGINT}, + enabled=#{enabled,jdbcType=BOOLEAN}, + authorization_updated_at=#{authorizationUpdatedAt,jdbcType=BIGINT} + WHERE uuid=#{uuid} + </insert> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/component/ResourceIndexerMapper.xml b/sonar-db/src/main/resources/org/sonar/db/component/ResourceIndexerMapper.xml new file mode 100644 index 00000000000..82afc7fa85d --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/component/ResourceIndexerMapper.xml @@ -0,0 +1,69 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.component.ResourceIndexerMapper"> + + <!-- + The column PROJECTS.ROOT_ID is not exact on multi-modules projects. The root id must + be loaded from the table SNAPSHOTS + --> + <select id="selectResources" parameterType="map" resultType="Resource"> + select p.name as "name", p.id as "id", p.scope as "scope", p.qualifier as "qualifier", s.root_project_id as "rootId" + from projects p, snapshots s + <where> + p.enabled=${_true} + and p.copy_resource_id is null + and p.id=s.project_id + and s.islast=${_true} + <if test="scopes != null"> + and p.scope in + <foreach item="scope" index="index" collection="scopes" open="(" separator="," close=")">#{scope}</foreach> + </if> + <if test="qualifiers != null"> + and p.qualifier in + <foreach item="qualifier" index="index" collection="qualifiers" open="(" separator="," close=")">#{qualifier} + </foreach> + </if> + <if test="rootProjectId != null"> + and s.root_project_id=#{rootProjectId} + </if> + <if test="nonIndexedOnly"> + and not exists(select * from resource_index ri where ri.resource_id=p.id) + </if> + </where> + order by p.id + </select> + + <select id="selectRootProjectIds" parameterType="map" resultType="int"> + select distinct root_project_id + from snapshots + where islast=${_true} + and scope='PRJ' + and qualifier in ('TRK', 'VW', 'SVW') + </select> + + <select id="selectMasterIndexByResourceId" parameterType="long" resultType="ResourceIndex"> + select kee as "key", resource_id as "resourceId" + from resource_index + where resource_id=#{id} and position=0 + </select> + + <select id="selectResourceToIndex" parameterType="long" resultType="Resource"> + select id, name, root_id as "rootId", qualifier + from projects + where id=#{id} and enabled=${_true} + </select> + + <delete id="deleteByResourceId" parameterType="long"> + delete from resource_index + where resource_id=#{id} + </delete> + + <insert id="insert" parameterType="ResourceIndex" useGeneratedKeys="false"> + insert into resource_index (kee, position, name_size, resource_id, root_project_id, qualifier) + values (#{key}, #{position}, #{nameSize}, + #{resourceId}, #{rootProjectId}, #{qualifier}) + </insert> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/component/ResourceKeyUpdaterMapper.xml b/sonar-db/src/main/resources/org/sonar/db/component/ResourceKeyUpdaterMapper.xml new file mode 100644 index 00000000000..40add31c8cd --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/component/ResourceKeyUpdaterMapper.xml @@ -0,0 +1,39 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.component.ResourceKeyUpdaterMapper"> + + <resultMap id="resourceResultMap" type="Resource"> + <id property="id" column="id"/> + <result property="key" column="kee"/> + <result property="deprecatedKey" column="deprecated_kee"/> + <result property="rootId" column="root_id"/> + <result property="scope" column="scope"/> + </resultMap> + + <select id="countResourceByKey" parameterType="String" resultType="int"> + SELECT count(*) + FROM projects + WHERE kee = #{key} + </select> + + <select id="selectProject" parameterType="long" resultMap="resourceResultMap"> + select * from projects where id=#{id} + </select> + + <select id="selectProjectResources" parameterType="long" resultMap="resourceResultMap"> + select * from projects where root_id=#{id} AND scope!='PRJ' + </select> + + <select id="selectDescendantProjects" parameterType="long" resultMap="resourceResultMap"> + select * from projects where scope='PRJ' and root_id=#{id} + </select> + + <update id="update" parameterType="Resource"> + update projects + set kee = #{key}, deprecated_kee = #{deprecatedKey} + where id = #{id} + </update> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/component/ResourceMapper.xml b/sonar-db/src/main/resources/org/sonar/db/component/ResourceMapper.xml new file mode 100644 index 00000000000..39339fe5ec2 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/component/ResourceMapper.xml @@ -0,0 +1,255 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.component.ResourceMapper"> + + <resultMap id="snapshotResultMap" type="Snapshot"> + <id property="id" column="id"/> + <result property="parentId" column="parent_snapshot_id"/> + <result property="rootId" column="root_snapshot_id"/> + <result property="createdAt" column="created_at"/> + <result property="buildDate" column="build_date"/> + <result property="componentId" column="project_id"/> + <result property="status" column="status"/> + <result property="purgeStatus" column="purge_status"/> + <result property="last" column="islast"/> + <result property="scope" column="scope"/> + <result property="qualifier" column="qualifier"/> + <result property="version" column="version"/> + <result property="path" column="path"/> + <result property="depth" column="depth"/> + <result property="rootProjectId" column="root_project_id"/> + <result property="period1Mode" column="period1_mode"/> + <result property="period2Mode" column="period2_mode"/> + <result property="period3Mode" column="period3_mode"/> + <result property="period4Mode" column="period4_mode"/> + <result property="period5Mode" column="period5_mode"/> + <result property="period1Param" column="period1_param"/> + <result property="period2Param" column="period2_param"/> + <result property="period3Param" column="period3_param"/> + <result property="period4Param" column="period4_param"/> + <result property="period5Param" column="period5_param"/> + <result property="period1Date" column="period1_date"/> + <result property="period2Date" column="period2_date"/> + <result property="period3Date" column="period3_date"/> + <result property="period4Date" column="period4_date"/> + <result property="period5Date" column="period5_date"/> + </resultMap> + + <resultMap id="resourceResultMap" type="Resource"> + <id property="id" column="id"/> + <result property="key" column="kee"/> + <result property="uuid" column="uuid"/> + <result property="projectUuid" column="project_uuid"/> + <result property="moduleUuid" column="module_uuid"/> + <result property="moduleUuidPath" column="module_uuid_path"/> + <result property="deprecatedKey" column="deprecated_kee"/> + <result property="path" column="path"/> + <result property="name" column="name"/> + <result property="longName" column="long_name"/> + <result property="rootId" column="root_id"/> + <result property="scope" column="scope"/> + <result property="qualifier" column="qualifier"/> + <result property="enabled" column="enabled"/> + <result property="description" column="description"/> + <result property="language" column="language"/> + <result property="copyResourceId" column="copy_resource_id"/> + <result property="personId" column="person_id"/> + <result property="createdAt" column="created_at"/> + <result property="authorizationUpdatedAt" column="authorization_updated_at"/> + </resultMap> + + <select id="selectResources" parameterType="map" resultMap="resourceResultMap"> + select * from projects p + <where> + <if test="qualifiers != null and qualifiers.length!=0"> + and p.qualifier in + <foreach item="qualifier" index="index" collection="qualifiers" open="(" separator="," close=")">#{qualifier} + </foreach> + </if> + <if test="key != null"> + and p.kee=#{key} + </if> + <if test="excludeDisabled"> + and p.enabled=${_true} + </if> + </where> + </select> + + <select id="selectResourceIds" parameterType="map" resultType="long"> + select p.id + from projects p + <where> + <if test="qualifiers != null and qualifiers.length!=0"> + and p.qualifier in + <foreach item="qualifier" index="index" collection="qualifiers" open="(" separator="," close=")">#{qualifier} + </foreach> + </if> + <if test="key != null"> + and p.kee=#{key} + </if> + <if test="excludeDisabled"> + and p.enabled=${_true} + </if> + </where> + </select> + + <select id="selectResource" parameterType="long" resultMap="resourceResultMap"> + select * from projects p + where p.id=#{id} + </select> + + <select id="selectResourceByUuid" parameterType="String" resultMap="resourceResultMap"> + select * from projects p + where p.uuid=#{uuid} + </select> + + <select id="selectSnapshot" parameterType="long" resultMap="snapshotResultMap"> + select * from snapshots where id=#{id} + </select> + + <select id="selectLastSnapshotByResourceKey" parameterType="string" resultMap="snapshotResultMap"> + SELECT s.* FROM snapshots s + INNER JOIN projects p on p.id=s.project_id AND p.enabled=${_true} AND p.copy_resource_id IS NULL + <where> + AND p.kee=#{id} + AND s.islast=${_true} + </where> + </select> + + <select id="selectLastSnapshotByResourceUuid" parameterType="string" resultMap="snapshotResultMap"> + SELECT s.* from snapshots s + INNER JOIN projects p on p.id=s.project_id AND p.enabled=${_true} AND p.copy_resource_id IS NULL + <where> + AND p.uuid=#{uuid} + AND s.islast=${_true} + </where> + </select> + + <select id="selectDescendantProjects" parameterType="long" resultMap="resourceResultMap"> + select * from projects where scope='PRJ' and root_id=#{id} + </select> + + <select id="selectRootProjectByComponentKey" parameterType="string" resultMap="resourceResultMap"> + select rootProject.* + from projects p + inner join snapshots s on s.project_id=p.id and s.islast=${_true} + inner join projects rootProject on rootProject.id=s.root_project_id + <where> + and p.kee=#{componentKey} + </where> + </select> + + <select id="selectRootProjectByComponentId" parameterType="long" resultMap="resourceResultMap"> + select rootProject.* + from snapshots s + inner join projects rootProject on rootProject.id=s.root_project_id + where + s.project_id=#{componentId} + and s.islast=${_true} + </select> + + <select id="selectProjectsIncludingNotCompletedOnesByQualifiers" parameterType="map" resultMap="resourceResultMap"> + select * from projects p + <where> + <if test="qualifiers != null and qualifiers.size() > 0"> + and + <foreach item="qualifier" index="index" collection="qualifiers" open="(" separator=" or " close=")"> + p.qualifier=#{qualifier} + </foreach> + </if> + and p.enabled=${_true} + and p.copy_resource_id is null + </where> + </select> + + <select id="selectProjectsByQualifiers" parameterType="map" resultMap="resourceResultMap"> + <include refid="selectProjectsByQualifiersQuery"/> + </select> + + <sql id="selectProjectsByQualifiersQuery"> + select p.* from projects p + inner join snapshots s on s.project_id=p.id + <where> + <if test="qualifiers != null and qualifiers.size() > 0"> + and + <foreach item="qualifier" index="index" collection="qualifiers" open="(" separator=" or " close=")"> + p.qualifier=#{qualifier} + </foreach> + </if> + and p.enabled=${_true} + and p.copy_resource_id is null + and s.islast=${_true} + </where> + </sql> + + <select id="selectGhostsProjects" parameterType="map" resultMap="resourceResultMap"> + select distinct p.* from projects p + inner join snapshots s1 on s1.project_id = p.id and s1.status='U' + left join snapshots s2 on s2.project_id = p.id and s2.status='P' + <where> + and s2.id is null + <if test="qualifiers != null and qualifiers.size() > 0"> + and + <foreach item="qualifier" index="index" collection="qualifiers" open="(" separator=" or " close=")"> + p.qualifier=#{qualifier} + </foreach> + </if> + and p.copy_resource_id is null + </where> + </select> + + <select id="selectProvisionedProjects" parameterType="map" resultMap="resourceResultMap"> + select p.* from projects p + left join snapshots s on s.project_id=p.id + <where> + and s.id is null + <if test="qualifiers != null and qualifiers.size() > 0"> + and + <foreach item="qualifier" index="index" collection="qualifiers" open="(" separator=" or " close=")"> + p.qualifier=#{qualifier} + </foreach> + </if> + and p.copy_resource_id is null + </where> + </select> + + <select id="selectProvisionedProject" parameterType="string" resultMap="resourceResultMap"> + select p.* from projects p + left join snapshots s on s.project_id=p.id + where s.id is null + and p.kee = #{key} + and p.copy_resource_id is null + </select> + + <insert id="insert" parameterType="Resource" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + insert into projects + (uuid, project_uuid, module_uuid, module_uuid_path, name, long_name, description, scope, qualifier, kee, + deprecated_kee, path, language, root_id, copy_resource_id, person_id, + enabled, authorization_updated_at, created_at) + values ( + #{uuid,jdbcType=VARCHAR}, #{projectUuid,jdbcType=VARCHAR}, #{moduleUuid,jdbcType=VARCHAR}, + #{moduleUuidPath,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, + #{longName,jdbcType=VARCHAR}, #{description,jdbcType=VARCHAR}, #{scope,jdbcType=VARCHAR}, + #{qualifier,jdbcType=VARCHAR}, + #{key,jdbcType=VARCHAR}, #{deprecatedKey,jdbcType=VARCHAR}, #{path,jdbcType=VARCHAR}, #{language,jdbcType=VARCHAR}, + #{rootId,jdbcType=INTEGER}, #{copyResourceId,jdbcType=INTEGER}, + #{personId,jdbcType=INTEGER}, #{enabled,jdbcType=BOOLEAN}, #{authorizationUpdatedAt,jdbcType=BIGINT}, + #{createdAt,jdbcType=TIMESTAMP} + ) + </insert> + + <update id="update" parameterType="Resource"> + update projects set name=#{name}, long_name=#{longName}, description=#{description}, + scope=#{scope}, qualifier=#{qualifier}, kee=#{key}, deprecated_kee=#{deprecatedKey}, path=#{path}, + language=#{language}, root_id=#{rootId}, copy_resource_id=#{copyResourceId}, + person_id=#{personId}, enabled=#{enabled} where id=#{id} + </update> + + <update id="updateAuthorizationDate" parameterType="map"> + update projects set authorization_updated_at=#{authorizationDate} + where id=#{projectId} + </update> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/component/SnapshotMapper.xml b/sonar-db/src/main/resources/org/sonar/db/component/SnapshotMapper.xml new file mode 100644 index 00000000000..95af7ba53a8 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/component/SnapshotMapper.xml @@ -0,0 +1,140 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> +<mapper namespace="org.sonar.db.component.SnapshotMapper"> + + <sql id="snapshotColumns"> + s.id, + s.parent_snapshot_id as parentId, + s.root_snapshot_id as rootId, + s.root_project_id as rootProjectId, + s.project_id as componentId, + s.created_at as createdAt, + s.build_date as buildDate, + s.status as status, + s.purge_status as purgeStatus, + s.islast as last, + s.scope as scope, + s.qualifier as qualifier, + s.version as version, + s.path as path, + s.depth as depth, + s.period1_mode as period1Mode, + s.period2_mode as period2Mode, + s.period3_mode as period3Mode, + s.period4_mode as period4Mode, + s.period5_mode as period5Mode, + s.period1_param as period1Param, + s.period2_param as period2Param, + s.period3_param as period3Param, + s.period4_param as period4Param, + s.period5_param as period5Param, + s.period1_date as period1Date, + s.period2_date as period2Date, + s.period3_date as period3Date, + s.period4_date as period4Date, + s.period5_date as period5Date + </sql> + + <select id="selectByKey" parameterType="Long" resultType="Snapshot"> + SELECT + <include refid="snapshotColumns"/> + FROM snapshots s + <where> + AND s.id=#{key} + </where> + </select> + + <select id="selectLastSnapshot" resultType="Snapshot"> + select + <include refid="snapshotColumns"/> + from snapshots s + where s.islast=${_true} and s.project_id = #{resource} + </select> + + <select id="selectSnapshotsByQuery" parameterType="map" resultType="Snapshot"> + SELECT + <include refid="snapshotColumns"/> + FROM snapshots s + <where> + <if test="query.componentId != null"> + AND s.project_id=#{query.componentId} + </if> + <if test="query.status != null"> + AND status=#{query.status} + </if> + <if test="query.version != null"> + AND version=#{query.version} + </if> + <if test="query.isLast != null"> + AND islast=#{query.isLast} + </if> + <if test="query.createdAfter != null"> + AND created_at>=#{query.createdAfter} + </if> + <if test="query.createdBefore != null"> + AND created_at<#{query.createdBefore} + </if> + </where> + <if test="query.sortField != null"> + ORDER BY + <if test="query.sortField == 'created_at'"> + created_at + </if> + <if test="query.sortOrder == 'asc'"> + asc + </if> + <if test="query.sortOrder == 'desc'"> + desc + </if> + </if> + </select> + + <select id="selectPreviousVersionSnapshots" parameterType="map" resultType="Snapshot"> + SELECT + <include refid="snapshotColumns"/> + FROM snapshots s + INNER JOIN events e ON s.id = e.snapshot_id AND e.name <> #{lastVersion} AND e.category='Version' + INNER JOIN projects p ON p.uuid=e.component_uuid AND p.id=#{componentId} + ORDER BY e.event_date DESC + </select> + + <select id="selectSnapshotAndChildrenOfScope" parameterType="map" resultType="Snapshot"> + select + <include refid="snapshotColumns"/> + from snapshots s + where s.scope = #{scope} + AND (s.id = #{snapshot} or s.root_snapshot_id = #{snapshot}) + </select> + + <sql id="insertColumns"> + (parent_snapshot_id, root_snapshot_id, root_project_id, project_id, created_at, build_date, status, purge_status, + islast, scope, qualifier, version, path, depth, + period1_mode, period2_mode, period3_mode, period4_mode, period5_mode, + period1_param, period2_param, period3_param, period4_param, period5_param, + period1_date, period2_date, period3_date, period4_date, period5_date) + </sql> + + <update id="updateSnapshotAndChildrenLastFlagAndStatus" parameterType="map"> + update snapshots + set islast = #{isLast}, status = #{status} + where root_snapshot_id=#{root} or id=#{root} or (path like #{path} and root_snapshot_id=#{pathRootId}) + </update> + + <update id="updateSnapshotAndChildrenLastFlag" parameterType="map"> + update snapshots + set islast = #{isLast} + where root_snapshot_id=#{root} or id=#{root} or (path like #{path} and root_snapshot_id=#{pathRootId}) + </update> + + <insert id="insert" parameterType="Snapshot" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + insert into snapshots + <include refid="insertColumns"/> + values (#{parentId}, #{rootId}, #{rootProjectId}, #{componentId}, #{createdAt}, #{buildDate}, #{status}, + #{purgeStatus}, #{last}, #{scope}, #{qualifier}, #{version}, #{path}, #{depth}, + #{period1Mode}, #{period2Mode}, #{period3Mode}, #{period4Mode}, #{period5Mode}, + #{period1Param}, #{period2Param}, #{period3Param}, #{period4Param}, #{period5Param}, + #{period1Date}, #{period2Date}, #{period3Date}, #{period4Date}, #{period5Date}) + </insert> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/compute/AnalysisReportMapper.xml b/sonar-db/src/main/resources/org/sonar/db/compute/AnalysisReportMapper.xml new file mode 100644 index 00000000000..2a3700186d4 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/compute/AnalysisReportMapper.xml @@ -0,0 +1,82 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.compute.AnalysisReportMapper"> + <sql id="reportColumns"> + <!-- the data report is not brought back by default as it could be too big in memory --> + ar.id, + ar.project_key as projectKey, + ar.project_name as projectName, + ar.report_status as status, + ar.uuid as uuid, + ar.created_at as createdAt, + ar.updated_at as updatedAt, + ar.started_at as startedAt, + ar.finished_at as finishedAt + </sql> + + <insert id="insert" parameterType="AnalysisReport" useGeneratedKeys="true" keyColumn="id" keyProperty="id"> + insert into analysis_reports + (project_key, project_name, uuid, report_status, created_at, updated_at, started_at, finished_at) + values ( + #{projectKey,jdbcType=VARCHAR}, #{projectName,jdbcType=VARCHAR}, #{uuid,jdbcType=VARCHAR}, + #{status,jdbcType=VARCHAR}, + #{createdAt,jdbcType=BIGINT}, #{updatedAt,jdbcType=BIGINT}, #{startedAt,jdbcType=BIGINT}, + #{finishedAt,jdbcType=BIGINT} + ) + </insert> + + <update id="resetAllToPendingStatus" parameterType="map"> + update analysis_reports + set report_status='PENDING', updated_at=#{updatedAt,jdbcType=BIGINT}, started_at=NULL + </update> + + <update id="updateWithBookingReport" parameterType="map"> + update analysis_reports + set report_status=#{busyStatus,jdbcType=VARCHAR}, + started_at=#{startedAt,jdbcType=BIGINT} + where id=#{id} and report_status=#{availableStatus} + </update> + + <delete id="truncate"> + truncate table analysis_reports + </delete> + + <delete id="delete"> + delete from analysis_reports where id=#{id} + </delete> + + <select id="selectById" resultType="AnalysisReport"> + select + <include refid="reportColumns"/> + from analysis_reports ar + where id = #{id} + </select> + + <select id="selectByProjectKey" parameterType="String" resultType="AnalysisReport"> + select + <include refid="reportColumns"/> + from analysis_reports ar + where project_key = #{projectKey} + </select> + + <!-- TODO optimize by restricting results to first row (LIMIT 1 on most dbs) --> + <select id="selectAvailables" parameterType="map" resultType="Long"> + select ar.id + from analysis_reports ar + where ar.report_status=#{availableStatus} + and not exists( + select 1 + from analysis_reports ar2 + where ar.project_key = ar2.project_key + and ar2.report_status=#{busyStatus} + ) + order by ar.created_at asc, ar.id asc + </select> + + <select id="selectAll" resultType="AnalysisReport"> + select + <include refid="reportColumns"/> + from analysis_reports ar + </select> +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/dashboard/ActiveDashboardMapper.xml b/sonar-db/src/main/resources/org/sonar/db/dashboard/ActiveDashboardMapper.xml new file mode 100644 index 00000000000..6c88317e890 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/dashboard/ActiveDashboardMapper.xml @@ -0,0 +1,58 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.dashboard.ActiveDashboardMapper"> + + <insert id="insert" parameterType="ActiveDashboard" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO active_dashboards (dashboard_id, user_id, order_index) + VALUES (#{dashboardId}, #{userId}, #{orderIndex}) + </insert> + + <select id="selectMaxOrderIndexForNullUser" resultType="Integer"> + SELECT MAX(order_index) + FROM active_dashboards + WHERE user_id IS NULL + </select> + + <sql id="dashboardColumns"> + d.id, d.user_id as "userId", d.name, d.description, d.column_layout as "columnLayout", + d.shared, d.is_global as "global", d.created_at as "createdAt", d.updated_at as "updatedAt" + </sql> + + <select id="selectGlobalDashboardsForUserLogin" parameterType="String" resultType="Dashboard"> + SELECT + <include refid="dashboardColumns"/> + FROM dashboards d + INNER JOIN active_dashboards ad on d.id=ad.dashboard_id + LEFT OUTER JOIN users u on u.id=ad.user_id + WHERE d.is_global=${_true} + <choose> + <when test="login == null"> + AND u.login IS NULL + </when> + <otherwise> + AND u.login=#{login} + </otherwise> + </choose> + ORDER BY order_index ASC + </select> + + <select id="selectProjectDashboardsForUserLogin" parameterType="String" resultType="Dashboard"> + SELECT + <include refid="dashboardColumns"/> + FROM dashboards d + INNER JOIN active_dashboards ad on d.id=ad.dashboard_id + LEFT OUTER JOIN users u on u.id=ad.user_id + WHERE d.is_global=${_false} + <choose> + <when test="login == null"> + AND u.login IS NULL + </when> + <otherwise> + AND u.login=#{login} + </otherwise> + </choose> + ORDER BY order_index ASC + </select> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/dashboard/DashboardMapper.xml b/sonar-db/src/main/resources/org/sonar/db/dashboard/DashboardMapper.xml new file mode 100644 index 00000000000..ce27a44c73b --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/dashboard/DashboardMapper.xml @@ -0,0 +1,6 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.dashboard.DashboardMapper"> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/dashboard/WidgetMapper.xml b/sonar-db/src/main/resources/org/sonar/db/dashboard/WidgetMapper.xml new file mode 100644 index 00000000000..d77042a1d07 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/dashboard/WidgetMapper.xml @@ -0,0 +1,38 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.dashboard.WidgetMapper"> + + <sql id="selectColumns"> + w.id as "id", + w.dashboard_id as "dashboardId", + w.widget_key as "widgetKey", + w.name as "name", + w.description as "description", + w.column_index as "columnIndex", + w.row_index as "rowIndex", + w.configured as "configured", + w.created_at as "createdAt", + w.updated_at as "updatedAt", + w.resource_id as "resourceId" + </sql> + + <select id="selectById" parameterType="Integer" resultType="Widget"> + select + <include refid="selectColumns"/> + from widgets w where w.id=#{id} + </select> + + <select id="selectByDashboard" parameterType="Integer" resultType="Widget"> + select + <include refid="selectColumns"/> + from widgets w where w.dashboard_id=#{id} + </select> + + <select id="selectAll" resultType="Widget"> + select + <include refid="selectColumns"/> + from widgets w + </select> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/dashboard/WidgetPropertyMapper.xml b/sonar-db/src/main/resources/org/sonar/db/dashboard/WidgetPropertyMapper.xml new file mode 100644 index 00000000000..a2bf8024a7f --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/dashboard/WidgetPropertyMapper.xml @@ -0,0 +1,12 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.dashboard.WidgetPropertyMapper"> + + <delete id="deleteByWidgetIds" parameterType="map"> + DELETE FROM widget_properties + WHERE widget_id IN + <foreach collection="list" open="(" close=")" item="wid" separator=",">#{wid}</foreach> + </delete> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/debt/CharacteristicMapper.xml b/sonar-db/src/main/resources/org/sonar/db/debt/CharacteristicMapper.xml new file mode 100644 index 00000000000..a5f7aa7848a --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/debt/CharacteristicMapper.xml @@ -0,0 +1,137 @@ +<?xml version="1.0" encoding="UTF-8" ?> + +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mappei.dtd"> + +<mapper namespace="org.sonar.db.debt.CharacteristicMapper"> + + <sql id="characteristicColumns"> + c.id, + c.kee as kee, + c.name as name, + c.parent_id as parentId, + c.characteristic_order as characteristicOrder, + c.enabled as enabled, + c.created_at as createdAt, + c.updated_at as updatedAt + </sql> + + <select id="selectEnabledCharacteristics" parameterType="map" resultType="Characteristic"> + select + <include refid="characteristicColumns"/> + from characteristics c + where c.enabled=${_true} + </select> + + <select id="selectCharacteristics" parameterType="map" resultType="Characteristic"> + select + <include refid="characteristicColumns"/> + from characteristics c + </select> + + <select id="selectEnabledRootCharacteristics" parameterType="map" resultType="Characteristic"> + select + <include refid="characteristicColumns"/> + from characteristics c + <where> + and c.parent_id is null + and c.enabled=${_true} + </where> + order by characteristic_order asc + </select> + + <select id="selectCharacteristicsByParentId" parameterType="map" resultType="Characteristic"> + select + <include refid="characteristicColumns"/> + from characteristics c + <where> + and c.parent_id=#{parent_id} + and c.enabled=${_true} + </where> + </select> + + <select id="selectCharacteristicsByIds" parameterType="map" resultType="Characteristic"> + select + <include refid="characteristicColumns"/> + from characteristics c + <where> + and c.enabled=${_true} + AND (<foreach item="id" index="index" collection="ids" open="(" separator=" or " close=")">c.id=#{id}</foreach>) + </where> + </select> + + <select id="selectByKey" parameterType="String" resultType="Characteristic"> + select + <include refid="characteristicColumns"/> + from characteristics c + <where> + and c.kee=#{key} + and c.enabled=${_true} + </where> + </select> + + <select id="selectById" parameterType="Integer" resultType="Characteristic"> + select + <include refid="characteristicColumns"/> + from characteristics c + <where> + and c.id=#{id} + and c.enabled=${_true} + </where> + </select> + + <select id="selectByName" parameterType="String" resultType="Characteristic"> + select + <include refid="characteristicColumns"/> + from characteristics c + <where> + and c.name=#{name} + and c.enabled=${_true} + </where> + </select> + + <select id="selectMaxCharacteristicOrder" resultType="Integer"> + select max(c.characteristic_order) + from characteristics c + <where> + and c.parent_id is null + and c.enabled=${_true} + </where> + </select> + + <insert id="insert" parameterType="Characteristic" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO characteristics (kee, name, parent_id, characteristic_order, enabled, created_at, updated_at) + VALUES (#{kee,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{parentId,jdbcType=INTEGER}, + #{characteristicOrder,jdbcType=INTEGER}, #{enabled,jdbcType=BOOLEAN}, #{createdAt,jdbcType=TIMESTAMP}, + #{updatedAt,jdbcType=TIMESTAMP}) + </insert> + + <update id="update" parameterType="Characteristic"> + update characteristics set + name=#{name}, + parent_id=#{parentId}, + characteristic_order=#{characteristicOrder}, + enabled=#{enabled}, + updated_at=#{updatedAt} + where id=#{id} + </update> + + <select id="selectDeprecatedRequirements" resultType="RequirementMigration"> + select id as "id", + parent_id as "parentId", + root_id as "rootId", + rule_id as "ruleId", + function_key as "functionKey", + factor_value as "coefficientValue", + factor_unit as "coefficientUnit", + offset_value as "offsetValue", + offset_unit as "offsetUnit", + enabled as "enabled" + from characteristics + where rule_id IS NOT NULL + </select> + + <delete id="deleteRequirementsFromCharacteristicsTable"> + DELETE FROM characteristics WHERE rule_id IS NOT NULL + </delete> +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/duplication/DuplicationMapper.xml b/sonar-db/src/main/resources/org/sonar/db/duplication/DuplicationMapper.xml new file mode 100644 index 00000000000..66212b3b205 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/duplication/DuplicationMapper.xml @@ -0,0 +1,25 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.duplication.DuplicationMapper"> + + <select id="selectCandidates" parameterType="map" resultType="DuplicationUnit"> + SELECT DISTINCT to_blocks.hash as hash, res.kee as resourceKey, to_blocks.index_in_file as indexInFile, + to_blocks.start_line as startLine, to_blocks.end_line as endLine + FROM duplications_index to_blocks, duplications_index from_blocks, snapshots snapshot, projects res + WHERE from_blocks.snapshot_id = #{resource_snapshot_id} + AND to_blocks.hash = from_blocks.hash + AND to_blocks.snapshot_id = snapshot.id + AND snapshot.islast = ${_true} + AND snapshot.project_id = res.id + AND res.language = #{language} + <if test="last_project_snapshot_id != null"> + AND to_blocks.project_snapshot_id != #{last_project_snapshot_id} + </if> + </select> + + <insert id="batchInsert" parameterType="DuplicationUnit" useGeneratedKeys="false"> + INSERT INTO duplications_index (snapshot_id, project_snapshot_id, hash, index_in_file, start_line, end_line) + VALUES (#{snapshotId}, #{projectSnapshotId}, #{hash}, #{indexInFile}, #{startLine}, #{endLine}) + </insert> +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/event/EventMapper.xml b/sonar-db/src/main/resources/org/sonar/db/event/EventMapper.xml new file mode 100644 index 00000000000..fa30a1f766f --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/event/EventMapper.xml @@ -0,0 +1,80 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> +<mapper namespace="org.sonar.db.event.EventMapper"> + + <sql id="eventColumns"> + e.id, + e.name, + e.category, + e.description, + e.event_data as "data", + e.event_date as "date", + e.component_uuid as "componentUuid", + e.snapshot_id as "snapshotId", + e.created_at as "createdAt" + </sql> + + <select id="selectByComponentUuid" parameterType="String" resultType="Event"> + SELECT + <include refid="eventColumns"/> + FROM events e + <where> + AND e.component_uuid=#{uuid} + </where> + </select> + + <insert id="insert" parameterType="Event" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO events (name, category, description, event_data, event_date, component_uuid, snapshot_id, created_at) + VALUES (#{name}, #{category}, #{description}, #{data}, #{date}, #{componentUuid}, #{snapshotId}, #{createdAt}) + </insert> + + <delete id="delete"> + DELETE FROM events WHERE id=#{id} + </delete> + + <select id="findSnapshotIdOfPreviousVersion" parameterType="map" resultType="long"> + SELECT s.id + FROM snapshots s, events e, projects p + <where> + AND p.id=#{componentId} + AND p.uuid=e.component_uuid + AND e.name <> #{currentVersion} + AND e.category='Version' + AND s.id = e.snapshot_id + </where> + ORDER BY e.event_date DESC + LIMIT 1 + </select> + + <!-- SQL Server --> + <select id="findSnapshotIdOfPreviousVersion" parameterType="map" resultType="long" databaseId="mssql"> + SELECT TOP 1 s.id + FROM snapshots s, events e, projects p + <where> + AND p.id=#{componentId} + AND p.uuid=e.component_uuid + AND e.name <> #{currentVersion} + AND e.category='Version' + AND s.id = e.snapshot_id + </where> + ORDER BY e.event_date DESC + </select> + + <!-- Oracle --> + <select id="findSnapshotIdOfPreviousVersion" parameterType="map" resultType="long" databaseId="oracle"> + SELECT * FROM (SELECT s.id + FROM snapshots s, events e, projects p + <where> + AND p.id=#{componentId} + AND p.uuid=e.component_uuid + AND e.name <> #{currentVersion} + AND e.category='Version' + AND s.id = e.snapshot_id + </where> + ORDER BY e.event_date DESC + ) + WHERE ROWNUM <= 1 + </select> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/issue/ActionPlanMapper.xml b/sonar-db/src/main/resources/org/sonar/db/issue/ActionPlanMapper.xml new file mode 100644 index 00000000000..49174ddad74 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/issue/ActionPlanMapper.xml @@ -0,0 +1,88 @@ +<?xml version="1.0" encoding="UTF-8" ?> + +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mappei.dtd"> + +<mapper namespace="org.sonar.db.issue.ActionPlanMapper"> + + <sql id="actionPlanColumns"> + ap.id, + ap.kee as kee, + ap.name as name, + ap.description as description, + ap.user_login as userLogin, + ap.project_id as projectId, + ap.status as status, + ap.deadline as deadLine, + ap.created_at as createdAt, + ap.updated_at as updatedAt, + p.kee as projectKey + </sql> + + <insert id="insert" parameterType="ActionPlanIssue" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO action_plans (kee, name, description, user_login, project_id, status, deadline, created_at, updated_at) + VALUES (#{kee}, #{name}, #{description}, #{userLogin}, #{projectId}, #{status}, #{deadLine}, #{createdAt}, + #{updatedAt}) + </insert> + + <update id="update" parameterType="ActionPlanIssue"> + update action_plans set + name=#{name}, + description=#{description}, + user_login=#{userLogin}, + project_id=#{projectId}, + status=#{status}, + deadline=#{deadLine}, + updated_at=current_timestamp + where kee = #{kee} + </update> + + <delete id="delete" parameterType="String"> + delete from action_plans where kee=#{key} + </delete> + + <select id="findByKey" parameterType="long" resultType="ActionPlanIssue"> + select + <include refid="actionPlanColumns"/> + from action_plans ap, projects p + <where> + and ap.kee=#{key} + and ap.project_id=p.id + </where> + </select> + + <select id="findByKeys" parameterType="long" resultType="ActionPlanIssue"> + select + <include refid="actionPlanColumns"/> + from action_plans ap, projects p + <where> + and ap.kee in + <foreach collection="keys" open="(" close=")" item="key" separator=","> + #{key} + </foreach> + and ap.project_id=p.id + </where> + </select> + + <select id="findOpenByProjectId" parameterType="long" resultType="ActionPlanIssue"> + select + <include refid="actionPlanColumns"/> + from action_plans ap, projects p + <where> + and ap.project_id=#{projectId} + and ap.status='OPEN' + and ap.project_id=p.id + </where> + </select> + + <select id="findByNameAndProjectId" parameterType="long" resultType="ActionPlanIssue"> + select + <include refid="actionPlanColumns"/> + from action_plans ap, projects p + <where> + and ap.project_id=#{projectId} + and ap.name=#{name} + and ap.project_id=p.id + </where> + </select> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/issue/ActionPlanStatsMapper.xml b/sonar-db/src/main/resources/org/sonar/db/issue/ActionPlanStatsMapper.xml new file mode 100644 index 00000000000..ec6ddb40ea3 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/issue/ActionPlanStatsMapper.xml @@ -0,0 +1,35 @@ +<?xml version="1.0" encoding="UTF-8" ?> + +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mappei.dtd"> + +<mapper namespace="org.sonar.db.issue.ActionPlanStatsMapper"> + + <sql id="actionPlanColumns"> + ap.id as id, + ap.kee as kee, + ap.name as name, + ap.description as description, + ap.user_login as userLogin, + ap.project_id as projectId, + ap.status as status, + ap.deadline as deadLine, + ap.created_at as createdAt, + ap.updated_at as updatedAt, + p.kee as projectKey + </sql> + + <select id="findByProjectId" parameterType="map" resultType="ActionPlanStats"> + select<include refid="actionPlanColumns"/>, count(total_issues.id) as totalIssues, count(open_issues.id) as + unresolvedIssues + from action_plans ap + left join projects p on p.id = ap.project_id + left join issues total_issues on total_issues.action_plan_key = ap.kee + left join issues open_issues on open_issues.id = total_issues.id and open_issues.resolution is null + <where> + and ap.project_id = #{projectId} + </where> + group by ap.id, ap.kee, ap.name, ap.description, ap.user_login, ap.project_id, ap.status, ap.deadline, + ap.created_at, ap.updated_at, p.kee + </select> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/issue/IssueChangeMapper.xml b/sonar-db/src/main/resources/org/sonar/db/issue/IssueChangeMapper.xml new file mode 100644 index 00000000000..96caa141092 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/issue/IssueChangeMapper.xml @@ -0,0 +1,71 @@ +<?xml version="1.0" encoding="UTF-8" ?> + +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.issue.IssueChangeMapper"> + + <sql id="issueChangeColumns"> + c.id, + c.kee as kee, + c.issue_key as issueKey, + c.user_login as userLogin, + c.change_type as changeType, + c.change_data as changeData, + c.created_at as createdAt, + c.updated_at as updatedAt, + c.issue_change_creation_date as issueChangeCreationDate + </sql> + + <insert id="insert" parameterType="IssueChange" useGeneratedKeys="false" keyProperty="id"> + INSERT INTO issue_changes (kee, issue_key, user_login, change_type, change_data, created_at, updated_at, + issue_change_creation_date) + VALUES (#{kee,jdbcType=VARCHAR}, #{issueKey,jdbcType=VARCHAR}, #{userLogin,jdbcType=VARCHAR}, + #{changeType,jdbcType=VARCHAR}, #{changeData,jdbcType=VARCHAR}, #{createdAt,jdbcType=BIGINT}, + #{updatedAt,jdbcType=BIGINT}, #{issueChangeCreationDate,jdbcType=BIGINT}) + </insert> + + <delete id="delete" parameterType="string"> + delete from issue_changes where kee=#{id} + </delete> + + <update id="update" parameterType="map"> + update issue_changes set change_data=#{changeData}, updated_at=#{updatedAt} where kee=#{kee} + </update> + + <select id="selectByIssuesAndType" parameterType="map" resultType="IssueChange"> + select + <include refid="issueChangeColumns"/> + from issue_changes c + where c.change_type=#{changeType} and c.issue_key in + <foreach collection="issueKeys" open="(" close=")" item="key" separator=","> + #{key} + </foreach> + order by c.created_at + </select> + + <select id="selectByKeyAndType" parameterType="map" resultType="IssueChange"> + select + <include refid="issueChangeColumns"/> + from issue_changes c + where c.change_type=#{changeType} and c.kee=#{key} + </select> + + <select id="selectByIssue" parameterType="string" resultType="IssueChange"> + select + <include refid="issueChangeColumns"/> + from issue_changes c + where c.issue_key=#{id} + order by created_at asc + </select> + + <select id="selectChangelogOfNonClosedIssuesByComponent" parameterType="map" resultType="IssueChange"> + select + <include refid="issueChangeColumns"/> + from issue_changes c + inner join issues i on i.kee = c.issue_key + where i.component_uuid=#{componentUuid} + and c.change_type=#{changeType} + and i.status <> 'CLOSED' + </select> +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/issue/IssueFilterFavouriteMapper.xml b/sonar-db/src/main/resources/org/sonar/db/issue/IssueFilterFavouriteMapper.xml new file mode 100644 index 00000000000..77db11f635c --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/issue/IssueFilterFavouriteMapper.xml @@ -0,0 +1,41 @@ +<?xml version="1.0" encoding="UTF-8" ?> + +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.issue.IssueFilterFavouriteMapper"> + + <sql id="issueFilterFavouriteColumns"> + filter_favourites.id as id, + filter_favourites.user_login as userLogin, + filter_favourites.issue_filter_id as issueFilterId, + filter_favourites.created_at as createdAt + </sql> + + <select id="selectById" parameterType="long" resultType="issueFilterFavourite"> + select + <include refid="issueFilterFavouriteColumns"/> + from issue_filter_favourites filter_favourites + where filter_favourites.id=#{id} + </select> + + <select id="selectByFilterId" parameterType="long" resultType="issueFilterFavourite"> + select + <include refid="issueFilterFavouriteColumns"/> + from issue_filter_favourites filter_favourites + where filter_favourites.issue_filter_id=#{filterId} + </select> + + <insert id="insert" parameterType="issueFilterFavourite" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO issue_filter_favourites (user_login, issue_filter_id, created_at) + VALUES (#{userLogin}, #{issueFilterId}, current_timestamp) + </insert> + + <delete id="delete" parameterType="long"> + delete from issue_filter_favourites where id=#{id} + </delete> + + <delete id="deleteByFilterId" parameterType="long"> + delete from issue_filter_favourites where issue_filter_id=#{issueFilterId} + </delete> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/issue/IssueFilterMapper.xml b/sonar-db/src/main/resources/org/sonar/db/issue/IssueFilterMapper.xml new file mode 100644 index 00000000000..aca8dffc6c2 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/issue/IssueFilterMapper.xml @@ -0,0 +1,76 @@ +<?xml version="1.0" encoding="UTF-8" ?> + +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.issue.IssueFilterMapper"> + + <sql id="issueFilterColumns"> + filters.id as id, + filters.name as name, + filters.user_login as userLogin, + filters.shared as shared, + filters.description as description, + filters.data as data, + filters.created_at as createdAt, + filters.updated_at as updatedAt + </sql> + + <select id="selectById" parameterType="long" resultType="IssueFilter"> + select + <include refid="issueFilterColumns"/> + from issue_filters filters + where filters.id=#{id} + </select> + + <select id="selectByUser" parameterType="String" resultType="IssueFilter"> + select + <include refid="issueFilterColumns"/> + from issue_filters filters + where filters.user_login=#{user} + </select> + + <select id="selectFavoriteFiltersByUser" parameterType="String" resultType="IssueFilter"> + select + <include refid="issueFilterColumns"/> + from issue_filters filters + inner join issue_filter_favourites fav on fav.issue_filter_id = filters.id + where fav.user_login=#{user} + </select> + + <select id="selectSharedFilters" parameterType="String" resultType="IssueFilter"> + select + <include refid="issueFilterColumns"/> + from issue_filters filters + where filters.shared=${_true} + </select> + + <select id="selectProvidedFilterByName" parameterType="String" resultType="IssueFilter"> + select + <include refid="issueFilterColumns"/> + from issue_filters filters + where filters.user_login is null + and filters.shared=${_true} + and filters.name=#{name} + </select> + + <insert id="insert" parameterType="IssueFilter" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO issue_filters (name, user_login, shared, description, data, created_at, updated_at) + VALUES (#{name}, #{userLogin}, #{shared}, #{description}, #{data}, #{createdAt}, #{updatedAt}) + </insert> + + <update id="update" parameterType="IssueFilter"> + update issue_filters set + name=#{name}, + shared=#{shared}, + description=#{description}, + data=#{data}, + user_login=#{userLogin}, + updated_at=current_timestamp + where id=#{id} + </update> + + <delete id="delete" parameterType="long"> + delete from issue_filters where id=#{id} + </delete> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/issue/IssueMapper.xml b/sonar-db/src/main/resources/org/sonar/db/issue/IssueMapper.xml new file mode 100644 index 00000000000..790040bc6e3 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/issue/IssueMapper.xml @@ -0,0 +1,234 @@ +<?xml version="1.0" encoding="UTF-8" ?> + +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mappei.dtd"> + +<mapper namespace="org.sonar.db.issue.IssueMapper"> + + <sql id="issueColumns"> + i.id, + i.kee as kee, + i.rule_id as ruleId, + i.action_plan_key as actionPlanKey, + i.severity as severity, + i.manual_severity as manualSeverity, + i.message as message, + i.line as line, + i.effort_to_fix as effortToFix, + i.technical_debt as debt, + i.status as status, + i.resolution as resolution, + i.checksum as checksum, + i.reporter as reporter, + i.assignee as assignee, + i.author_login as authorLogin, + i.tags as tagsString, + i.issue_attributes as issueAttributes, + i.issue_creation_date as issueCreationTime, + i.issue_update_date as issueUpdateTime, + i.issue_close_date as issueCloseTime, + i.created_at as createdAt, + i.updated_at as updatedAt, + r.plugin_rule_key as ruleKey, + r.plugin_name as ruleRepo, + r.language as language, + p.kee as componentKey, + i.component_uuid as componentUuid, + p.module_uuid as moduleUuid, + p.module_uuid_path as moduleUuidPath, + p.path as filePath, + root.kee as projectKey, + i.project_uuid as projectUuid + </sql> + + <sql id="sortColumn"> + <if test="query.sort() != null">, + <choose> + <when test="'SEVERITY'.equals(query.sort())"> + i.severity as severity + </when> + <when test="'STATUS'.equals(query.sort())"> + i.status as status + </when> + <when test="'ASSIGNEE'.equals(query.sort())"> + i.assignee as assignee + </when> + <when test="'CREATION_DATE'.equals(query.sort())"> + i.issue_creation_date as issueCreationTime + </when> + <when test="'UPDATE_DATE'.equals(query.sort())"> + i.issue_update_date as issueUpdateTime + </when> + <when test="'CLOSE_DATE'.equals(query.sort())"> + i.issue_close_date as issueCloseTime + </when> + </choose> + </if> + </sql> + + <insert id="insert" parameterType="Issue" useGeneratedKeys="false" keyProperty="id"> + INSERT INTO issues (kee, rule_id, action_plan_key, severity, manual_severity, + message, line, effort_to_fix, technical_debt, status, tags, + resolution, checksum, reporter, assignee, author_login, issue_attributes, issue_creation_date, issue_update_date, + issue_close_date, created_at, updated_at, component_uuid, project_uuid) + VALUES (#{kee,jdbcType=VARCHAR}, #{ruleId,jdbcType=INTEGER}, #{actionPlanKey,jdbcType=VARCHAR}, + #{severity,jdbcType=VARCHAR}, + #{manualSeverity,jdbcType=BOOLEAN}, #{message,jdbcType=VARCHAR}, #{line,jdbcType=INTEGER}, + #{effortToFix,jdbcType=DOUBLE}, #{debt,jdbcType=INTEGER}, #{status,jdbcType=VARCHAR}, + #{tagsString,jdbcType=VARCHAR}, #{resolution,jdbcType=VARCHAR}, #{checksum,jdbcType=VARCHAR}, + #{reporter,jdbcType=VARCHAR}, #{assignee,jdbcType=VARCHAR}, #{authorLogin,jdbcType=VARCHAR}, + #{issueAttributes,jdbcType=VARCHAR}, + #{issueCreationTime,jdbcType=BIGINT},#{issueUpdateTime,jdbcType=BIGINT}, #{issueCloseTime,jdbcType=BIGINT}, + #{createdAt,jdbcType=BIGINT}, #{updatedAt,jdbcType=BIGINT}, + #{componentUuid,jdbcType=VARCHAR}, #{projectUuid,jdbcType=VARCHAR}) + </insert> + + <!-- + IMPORTANT - invariant columns can't be updated. See IssueDto#toDtoForUpdate() + --> + <update id="update" parameterType="Issue"> + update issues set + action_plan_key=#{actionPlanKey,jdbcType=VARCHAR}, + severity=#{severity,jdbcType=VARCHAR}, + manual_severity=#{manualSeverity,jdbcType=BOOLEAN}, + message=#{message,jdbcType=VARCHAR}, + line=#{line,jdbcType=INTEGER}, + effort_to_fix=#{effortToFix,jdbcType=DOUBLE}, + technical_debt=#{debt,jdbcType=INTEGER}, + status=#{status,jdbcType=VARCHAR}, + resolution=#{resolution,jdbcType=VARCHAR}, + checksum=#{checksum,jdbcType=VARCHAR}, + reporter=#{reporter,jdbcType=VARCHAR}, + assignee=#{assignee,jdbcType=VARCHAR}, + author_login=#{authorLogin,jdbcType=VARCHAR}, + tags=#{tagsString,jdbcType=VARCHAR}, + project_uuid=#{projectUuid,jdbcType=VARCHAR}, + issue_attributes=#{issueAttributes,jdbcType=VARCHAR}, + issue_creation_date=#{issueCreationTime,jdbcType=BIGINT}, + issue_update_date=#{issueUpdateTime,jdbcType=BIGINT}, + issue_close_date=#{issueCloseTime,jdbcType=BIGINT}, + updated_at=#{updatedAt,jdbcType=BIGINT} + where kee = #{kee} + </update> + + <!-- + IMPORTANT - invariant columns can't be updated. See IssueDto#toDtoForUpdate() + --> + <update id="updateIfBeforeSelectedDate" parameterType="Issue"> + update issues set + action_plan_key=#{actionPlanKey,jdbcType=VARCHAR}, + severity=#{severity,jdbcType=VARCHAR}, + manual_severity=#{manualSeverity,jdbcType=BOOLEAN}, + message=#{message,jdbcType=VARCHAR}, + line=#{line,jdbcType=INTEGER}, + effort_to_fix=#{effortToFix,jdbcType=DOUBLE}, + technical_debt=#{debt,jdbcType=INTEGER}, + status=#{status,jdbcType=VARCHAR}, + resolution=#{resolution,jdbcType=VARCHAR}, + checksum=#{checksum,jdbcType=VARCHAR}, + reporter=#{reporter,jdbcType=VARCHAR}, + assignee=#{assignee,jdbcType=VARCHAR}, + author_login=#{authorLogin,jdbcType=VARCHAR}, + tags=#{tagsString,jdbcType=VARCHAR}, + project_uuid=#{projectUuid,jdbcType=VARCHAR}, + issue_attributes=#{issueAttributes,jdbcType=VARCHAR}, + issue_creation_date=#{issueCreationTime,jdbcType=BIGINT}, + issue_update_date=#{issueUpdateTime,jdbcType=BIGINT}, + issue_close_date=#{issueCloseTime,jdbcType=BIGINT}, + updated_at=#{updatedAt,jdbcType=BIGINT} + where kee = #{kee} and updated_at <= #{selectedAt} + </update> + + <select id="selectByKey" parameterType="String" resultType="Issue"> + select + <include refid="issueColumns"/> + from issues i + inner join rules r on r.id=i.rule_id + inner join projects p on p.uuid=i.component_uuid + inner join projects root on root.uuid=i.project_uuid + where i.kee=#{kee} + </select> + + <select id="selectNonClosedByComponentUuid" parameterType="String" resultType="Issue"> + select + <include refid="issueColumns"/> + from issues i + inner join rules r on r.id=i.rule_id + inner join projects p on p.uuid=i.component_uuid + inner join projects root on root.uuid=i.project_uuid + where + i.component_uuid=#{componentUuid} and + i.status <> 'CLOSED' + </select> + + <select id="selectNonClosedIssuesByModule" parameterType="long" resultType="Issue"> + select + i.id, + i.kee as kee, + i.rule_id as ruleId, + i.component_uuid as componentUuid, + i.project_uuid as projectUuid, + i.action_plan_key as actionPlanKey, + i.severity as severity, + i.manual_severity as manualSeverity, + i.message as message, + i.line as line, + i.effort_to_fix as effortToFix, + i.technical_debt as debt, + i.status as status, + i.resolution as resolution, + i.checksum as checksum, + i.reporter as reporter, + i.assignee as assignee, + i.author_login as authorLogin, + i.tags as tagsString, + i.issue_attributes as issueAttributes, + i.issue_creation_date as issueCreationTime, + i.issue_update_date as issueUpdateTime, + i.issue_close_date as issueCloseTime, + i.created_at as createdAt, + i.updated_at as updatedAt, + r.plugin_rule_key as ruleKey, + r.plugin_name as ruleRepo, + p.kee as componentKey, + root.kee as projectKey + from issues i + inner join (select p.id, p.uuid,p.kee from projects p where (p.root_id=#{id} and p.qualifier <> 'BRC') or + (p.id=#{id})) p on p.uuid=i.component_uuid + inner join rules r on r.id=i.rule_id + left outer join projects root on root.uuid=i.project_uuid + where i.status <> 'CLOSED' + </select> + + <select id="selectComponentUuidsOfOpenIssuesForProjectUuid" parameterType="string" resultType="string"> + select distinct(i.component_uuid) + from issues i + where i.project_uuid=#{projectUuid} and i.status <> 'CLOSED' + </select> + + <select id="selectByKeys" parameterType="map" resultType="Issue"> + select + <include refid="issueColumns"/> + from issues i + inner join rules r on r.id=i.rule_id + inner join projects p on p.uuid=i.component_uuid + inner join projects root on root.uuid=i.project_uuid + where i.kee in + <foreach collection="list" open="(" close=")" item="key" separator=","> + #{key} + </foreach> + </select> + + <select id="selectByActionPlan" parameterType="map" resultType="Issue"> + select + <include refid="issueColumns"/> + from issues i + inner join rules r on r.id=i.rule_id + inner join projects p on p.uuid=i.component_uuid + inner join projects root on root.uuid=i.project_uuid + <where> + and i.action_plan_key=#{action_plan} + </where> + </select> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/loadedtemplate/LoadedTemplateMapper.xml b/sonar-db/src/main/resources/org/sonar/db/loadedtemplate/LoadedTemplateMapper.xml new file mode 100644 index 00000000000..0395aad517a --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/loadedtemplate/LoadedTemplateMapper.xml @@ -0,0 +1,26 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.loadedtemplate.LoadedTemplateMapper"> + + <resultMap id="loadedTemplateResultMap" type="LoadedTemplate"> + <result property="id" column="id"/> + <result property="key" column="kee"/> + <result property="type" column="template_type"/> + </resultMap> + + <select id="countByTypeAndKey" parameterType="map" resultType="int"> + SELECT count(*) + FROM loaded_templates + WHERE kee = #{key} AND template_type = #{type} + </select> + + <insert id="insert" parameterType="LoadedTemplate" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO loaded_templates (kee, template_type) + VALUES (#{key}, #{type}) + </insert> + + <delete id="delete" parameterType="map"> + delete from loaded_templates where kee = #{key} AND template_type = #{type} + </delete> +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/measure/CustomMeasureMapper.xml b/sonar-db/src/main/resources/org/sonar/db/measure/CustomMeasureMapper.xml new file mode 100644 index 00000000000..119cec178b4 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/measure/CustomMeasureMapper.xml @@ -0,0 +1,83 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.measure.CustomMeasureMapper"> + <sql id="selectColumns"> + m.id, + m.metric_id as metricId, + m.component_uuid as componentUuid, + m.value, + m.text_value as textValue, + m.user_login as userLogin, + m.description, + m.created_at as createdAt, + m.updated_at as updatedAt + </sql> + + <select id="selectById" resultType="CustomMeasure"> + select + <include refid="selectColumns"/> + from manual_measures m + where m.id=#{id} + </select> + + <select id="selectByMetricId" resultType="CustomMeasure"> + select + <include refid="selectColumns"/> + from manual_measures m + where m.metric_id=#{metricId} + </select> + + <select id="selectByComponentUuid" resultType="CustomMeasure"> + select + <include refid="selectColumns"/> + from manual_measures m + where m.component_uuid=#{componentUuid} + </select> + + <insert id="insert" parameterType="CustomMeasure" useGeneratedKeys="true" keyColumn="id" keyProperty="id"> + INSERT INTO manual_measures ( + metric_id, component_uuid, value, text_value, user_login, description, created_at, updated_at + ) + VALUES ( + #{metricId, jdbcType=INTEGER}, #{componentUuid, jdbcType=VARCHAR}, + #{value, jdbcType=DOUBLE}, #{textValue, jdbcType=VARCHAR}, #{userLogin, jdbcType=VARCHAR}, + #{description, jdbcType=VARCHAR}, #{createdAt, jdbcType=BIGINT}, #{updatedAt, jdbcType=BIGINT} + ) + </insert> + + <update id="update" parameterType="CustomMeasure"> + update manual_measures + set value = #{value, jdbcType=DOUBLE}, + text_value = #{textValue, jdbcType=VARCHAR}, + description = #{description, jdbcType=VARCHAR}, + user_login = #{userLogin, jdbcType=VARCHAR}, + updated_at = #{updatedAt, jdbcType=BIGINT} + where id = #{id} + </update> + + <delete id="deleteByMetricIds"> + delete from manual_measures + where metric_id in + <foreach collection="metricIds" item="metricId" open="(" close=")" separator=","> + #{metricId} + </foreach> + </delete> + + <delete id="delete"> + delete from manual_measures + where id=#{id} + </delete> + + <select id="countByComponentUuid" resultType="Integer"> + select count(*) + from manual_measures m + where m.component_uuid=#{componentUuid} + </select> + + <select id="countByComponentIdAndMetricId" resultType="Integer"> + select count(*) + from manual_measures m + where m.metric_id=#{metricId} and m.component_uuid=#{componentUuid} + </select> +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/measure/MeasureFilterMapper.xml b/sonar-db/src/main/resources/org/sonar/db/measure/MeasureFilterMapper.xml new file mode 100644 index 00000000000..85e85b3fa4e --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/measure/MeasureFilterMapper.xml @@ -0,0 +1,17 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.measure.MeasureFilterMapper"> + + <select id="findSystemFilterByName" parameterType="string" resultType="MeasureFilter"> + select id, name, user_id as "userId", shared, description, data, created_at as "createdAt", updated_at as + "updatedAt" + from measure_filters WHERE user_id is null and name=#{id} + </select> + + <insert id="insert" parameterType="MeasureFilter" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO measure_filters (name, user_id, shared, description, data, created_at, updated_at) + VALUES (#{name}, #{userId}, #{shared}, #{description}, #{data}, #{createdAt}, #{updatedAt}) + </insert> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/measure/MeasureMapper.xml b/sonar-db/src/main/resources/org/sonar/db/measure/MeasureMapper.xml new file mode 100644 index 00000000000..7d83e993e05 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/measure/MeasureMapper.xml @@ -0,0 +1,113 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.measure.MeasureMapper"> + + <sql id="measureColumns"> + pm.id, + pm.snapshot_id as snapshotId, + pm.value as value, + pm.text_value as textValue, + pm.alert_status as alertStatus, + pm.alert_text as alertText, + pm.measure_data as dataValue, + pm.variation_value_1 as variation1, + pm.variation_value_2 as variation2, + pm.variation_value_3 as variation3, + pm.variation_value_4 as variation4, + pm.variation_value_5 as variation5, + p.kee as componentKey, + metric.name as metricKey + </sql> + + <select id="selectByComponentAndMetric" parameterType="map" resultType="Measure"> + SELECT metric.name as metric_name, + <include refid="measureColumns"/> + FROM project_measures pm + INNER JOIN snapshots s ON s.id=pm.snapshot_id AND s.islast=${_true} + INNER JOIN projects p ON p.id=s.project_id AND p.enabled=${_true} + INNER JOIN metrics metric ON metric.id=pm.metric_id + <where> + AND p.kee = #{componentKey} + AND metric.name=#{metricKey} + AND pm.rule_id IS NULL + AND pm.characteristic_id IS NULL + AND pm.person_id IS NULL + </where> + </select> + + <select id="selectByComponentAndMetrics" parameterType="map" resultType="Measure"> + SELECT metric.name as metric_name, + <include refid="measureColumns"/> + FROM project_measures pm + INNER JOIN snapshots s ON s.id=pm.snapshot_id AND s.islast=${_true} + INNER JOIN projects p ON p.id=s.project_id AND p.enabled=${_true} + INNER JOIN metrics metric ON metric.id=pm.metric_id + <where> + AND p.kee = #{componentKey} + AND + <foreach item="metricKey" index="index" collection="metricKeys" open="(" separator=" or " close=")"> + metric.name=#{metricKey} + </foreach> + AND pm.rule_id IS NULL + AND pm.characteristic_id IS NULL + AND pm.person_id IS NULL + </where> + </select> + + <select id="countByComponentAndMetric" parameterType="map" resultType="long"> + SELECT count(pm.id) + FROM project_measures pm + INNER JOIN snapshots s ON s.id=pm.snapshot_id AND s.islast=${_true} + INNER JOIN metrics metric ON metric.id=pm.metric_id + INNER JOIN projects p ON p.id=s.project_id AND p.enabled=${_true} + <where> + AND p.kee = #{componentKey} + AND metric.name = #{metricKey} + AND pm.rule_id IS NULL + AND pm.characteristic_id IS NULL + AND pm.person_id IS NULL + </where> + </select> + + <select id="selectByComponentUuidAndProjectSnapshotIdAndStatusAndMetricIds" parameterType="map" + resultType="org.sonar.db.measure.PastMeasureDto"> + SELECT pm.id as id, pm.metric_id as metricId, pm.rule_id as ruleId, pm.characteristic_id as characteristicId, + pm.person_id as personId, pm.value as value + FROM project_measures pm + INNER JOIN snapshots s ON s.id=pm.snapshot_id AND s.status=#{status} + INNER JOIN projects p ON p.id=s.project_id AND p.enabled=${_true} + <where> + AND p.uuid = #{componentUuid} + AND (s.root_snapshot_id=#{rootSnapshotId} OR s.id=#{rootSnapshotId}) + AND + <foreach item="metricId" index="index" collection="metricIds" open="(" separator=" or " close=")"> + pm.metric_id=#{metricId} + </foreach> + </where> + </select> + + <insert id="insert" parameterType="Measure" useGeneratedKeys="false"> + INSERT INTO project_measures ( + value, metric_id, snapshot_id, rule_id, text_value, project_id, alert_status, alert_text, description, + characteristic_id, person_id, variation_value_1, variation_value_2, variation_value_3, variation_value_4, + variation_value_5, measure_data) + VALUES ( + #{value, jdbcType=DOUBLE}, #{metricId, jdbcType=INTEGER}, #{snapshotId, jdbcType=INTEGER}, + #{ruleId, jdbcType=INTEGER}, #{textValue, jdbcType=VARCHAR}, + #{componentId, jdbcType=INTEGER}, #{alertStatus, jdbcType=VARCHAR}, #{alertText, jdbcType=VARCHAR}, + #{description, jdbcType=VARCHAR}, #{characteristicId, jdbcType=INTEGER}, + #{personId, jdbcType=INTEGER}, #{variation1, jdbcType=DOUBLE}, #{variation2, jdbcType=DOUBLE}, + #{variation3, jdbcType=DOUBLE}, + #{variation4, jdbcType=DOUBLE}, #{variation5, jdbcType=DOUBLE}, #{dataValue, jdbcType=BINARY} + ) + </insert> + + <select id="selectMetricKeysForSnapshot" parameterType="long" resultType="string"> + SELECT DISTINCT m.name + FROM project_measures pm + INNER JOIN metrics m ON m.id=pm.metric_id + WHERE pm.snapshot_id=#{snapshotId} + </select> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/metric/MetricMapper.xml b/sonar-db/src/main/resources/org/sonar/db/metric/MetricMapper.xml new file mode 100644 index 00000000000..ca4a79a3a82 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/metric/MetricMapper.xml @@ -0,0 +1,164 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.metric.MetricMapper"> + + <sql id="metricColumns"> + m.id, + m.name as kee, + m.description, + m.direction, + m.domain, + m.short_name as shortName, + m.qualitative, + m.val_type as valueType, + m.user_managed as userManaged, + m.enabled, + m.worst_value as worstValue, + m.best_value as bestValue, + m.optimized_best_value as optimizedBestValue, + m.hidden, + m.delete_historical_data as deleteHistoricalData + </sql> + + <select id="selectByKey" parameterType="map" resultType="org.sonar.db.metric.MetricDto"> + SELECT + <include refid="metricColumns"/> + FROM metrics m + <where> + AND m.name=#{key} + </where> + </select> + + <select id="selectAllEnabled" parameterType="map" resultType="org.sonar.db.metric.MetricDto"> + SELECT + <include refid="metricColumns"/> + FROM metrics m + <where> + AND m.enabled=${_true} + <if test="isCustom!=null"> + <if test="isCustom.equals(true)"> + AND m.user_managed=${_true} + </if> + <if test="isCustom.equals(false)"> + AND m.user_managed=${_false} + </if> + </if> + </where> + ORDER BY UPPER(m.short_name), m.short_name + </select> + + <select id="selectAvailableCustomMetricsByComponentUuid" resultType="org.sonar.db.metric.MetricDto"> + select + <include refid="metricColumns"/> + from metrics m + left join manual_measures mm on mm.metric_id = m.id and mm.component_uuid=#{componentUuid} + where m.enabled=${_true} + and m.user_managed=${_true} + and mm.id is null + ORDER BY UPPER(m.short_name), m.short_name + </select> + + <select id="countEnabled" resultType="Integer"> + SELECT COUNT(*) + FROM metrics m + <where> + AND m.enabled=${_true} + <if test="isCustom!=null"> + <if test="isCustom.equals(true)"> + AND m.user_managed=${_true} + </if> + <if test="isCustom.equals(false)"> + AND m.user_managed=${_false} + </if> + </if> + </where> + </select> + + <insert id="insert" parameterType="org.sonar.db.metric.MetricDto" useGeneratedKeys="true" keyColumn="id" + keyProperty="id"> + INSERT INTO metrics ( + name, description, direction, domain, short_name, qualitative, val_type, user_managed, enabled, worst_value, + best_value, optimized_best_value, hidden, delete_historical_data) + VALUES ( + #{kee, jdbcType=VARCHAR}, #{description, jdbcType=VARCHAR}, #{direction, jdbcType=INTEGER}, + #{domain, jdbcType=VARCHAR}, #{shortName, jdbcType=VARCHAR}, #{qualitative, jdbcType=BOOLEAN}, + #{valueType, jdbcType=VARCHAR}, #{userManaged, jdbcType=BOOLEAN}, #{enabled, jdbcType=BOOLEAN}, + #{worstValue, jdbcType=DOUBLE}, #{bestValue, jdbcType=DOUBLE}, + #{optimizedBestValue, jdbcType=BOOLEAN}, #{hidden, jdbcType=BOOLEAN}, #{deleteHistoricalData, jdbcType=BOOLEAN} + ) + </insert> + + <update id="update" parameterType="org.sonar.db.metric.MetricDto"> + update metrics + set + name=#{key, jdbcType=VARCHAR}, + short_name=#{shortName, jdbcType=VARCHAR}, + val_type=#{valueType, jdbcType=VARCHAR}, + enabled=#{enabled, jdbcType=BOOLEAN}, + domain=#{domain, jdbcType=VARCHAR}, + description=#{description, jdbcType=VARCHAR}, + direction=#{direction, jdbcType=INTEGER}, + hidden=#{hidden, jdbcType=BOOLEAN}, + qualitative=#{qualitative, jdbcType=BOOLEAN} + where id=#{id} + </update> + + <select id="selectDomains" resultType="String"> + select distinct domain + from metrics m + where m.domain is not null and m.enabled=${_true} + </select> + + <update id="disableByIds"> + update metrics + set enabled=${_false} + <where> + AND user_managed=${_true} + AND id in + <foreach item="id" collection="ids" open="(" separator="," close=")"> + #{id} + </foreach> + </where> + </update> + + <update id="disableByKey" parameterType="string"> + update metrics + set enabled=${_false} + where name=#{key} + </update> + + <select id="selectByKeys" resultType="org.sonar.db.metric.MetricDto"> + SELECT + <include refid="metricColumns"/> + FROM metrics m + <where> + AND m.name in + <foreach item="key" collection="keys" open="(" separator="," close=")"> + #{key} + </foreach> + </where> + </select> + + <select id="selectByIds" resultType="org.sonar.db.metric.MetricDto"> + SELECT + <include refid="metricColumns"/> + FROM metrics m + <where> + AND m.id in + <foreach item="id" collection="ids" open="(" separator="," close=")"> + #{id} + </foreach> + </where> + </select> + + <select id="selectById" resultType="org.sonar.db.metric.MetricDto"> + SELECT + <include refid="metricColumns"/> + FROM metrics m + <where> + AND m.id=#{id} + </where> + </select> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/notification/NotificationQueueMapper.xml b/sonar-db/src/main/resources/org/sonar/db/notification/NotificationQueueMapper.xml new file mode 100644 index 00000000000..c09eafc31d0 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/notification/NotificationQueueMapper.xml @@ -0,0 +1,44 @@ +<?xml version="1.0" encoding="UTF-8" ?> + +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mappei.dtd"> + +<mapper namespace="org.sonar.db.notification.NotificationQueueMapper"> + + <insert id="insert" parameterType="NotificationQueue" useGeneratedKeys="false"> + INSERT INTO notifications (data) + VALUES (#{data}) + </insert> + + <delete id="delete" parameterType="long"> + delete from notifications where id=#{id} + </delete> + + <select id="count" resultType="long"> + select count(*) from notifications + </select> + + <select id="findOldest" parameterType="int" resultType="NotificationQueue"> + select id, data + from notifications + order by id asc + limit #{count} + </select> + + <!-- SQL Server --> + <select id="findOldest" parameterType="int" resultType="NotificationQueue" databaseId="mssql"> + select top (#{count}) id, data + from notifications + order by id asc + </select> + + <!-- Oracle --> + <select id="findOldest" parameterType="int" resultType="NotificationQueue" databaseId="oracle"> + select * from (select + id, data + from notifications + order by id asc + ) + where rownum <= #{count} + </select> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/permission/PermissionMapper.xml b/sonar-db/src/main/resources/org/sonar/db/permission/PermissionMapper.xml new file mode 100644 index 00000000000..79a705a44dc --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/permission/PermissionMapper.xml @@ -0,0 +1,69 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.permission.PermissionMapper"> + + <select id="selectUsers" parameterType="map" resultType="UserWithPermission"> + SELECT u.login as login, u.name as name, user_role.role as permission + FROM users u + LEFT JOIN user_roles user_role ON user_role.user_id=u.id + AND user_role.role=#{query.permission} + <if test="componentId != null"> + AND user_role.resource_id=#{componentId} + </if> + <if test="componentId == null"> + AND user_role.resource_id IS NULL + </if> + <where> + u.active = ${_true} + <choose> + <when test="query.membership() == 'IN'"> + AND user_role.role IS NOT NULL + </when> + <when test="query.membership() == 'OUT'"> + AND user_role.role IS NULL + </when> + </choose> + <if test="query.search() != null"> + AND (UPPER(u.name) LIKE #{query.searchSql} ESCAPE '/') + </if> + </where> + ORDER BY u.name + </select> + + <select id="selectGroups" parameterType="map" resultType="GroupWithPermission"> + SELECT name, description, permission FROM + (SELECT g.name as name, g.description as description, group_role.role as permission + FROM groups g + LEFT JOIN group_roles group_role ON group_role.group_id=g.id + AND group_role.role=#{query.permission} + <if test="componentId != null"> + AND group_role.resource_id=#{componentId} + </if> + <if test="componentId == null"> + AND group_role.resource_id IS NULL + </if> + UNION + -- Add Anyone group permission + SELECT #{anyoneGroup} as name, NULL as description, group_role.role as permission + FROM group_roles group_role + <where> + AND group_role.role=#{query.permission} + AND group_role.group_id IS NULL + <if test="componentId != null"> + AND group_role.resource_id=#{componentId} + </if> + <if test="componentId == null"> + AND group_role.resource_id IS NULL + </if> + </where> + ) groups + <where> + <if test="query.search() != null"> + AND (UPPER(groups.name) LIKE #{query.searchSql} ESCAPE '/') + </if> + </where> + ORDER BY groups.name + </select> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/permission/PermissionTemplateMapper.xml b/sonar-db/src/main/resources/org/sonar/db/permission/PermissionTemplateMapper.xml new file mode 100644 index 00000000000..95567f2e5c2 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/permission/PermissionTemplateMapper.xml @@ -0,0 +1,186 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.permission.PermissionTemplateMapper"> + + <insert id="insert" parameterType="PermissionTemplate" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO permission_templates (name, kee, description, key_pattern, created_at, updated_at) + VALUES (#{name}, #{kee}, #{description}, #{keyPattern}, #{createdAt}, #{updatedAt}) + </insert> + + <update id="update" parameterType="PermissionTemplate"> + UPDATE permission_templates + SET name = #{name}, description = #{description}, key_pattern = #{keyPattern}, updated_at = #{updatedAt} + WHERE id = #{id} + </update> + + <delete id="delete" parameterType="long"> + DELETE FROM permission_templates + WHERE id = #{templateId} + </delete> + + <delete id="deleteUsersPermissions" parameterType="long"> + DELETE FROM perm_templates_users + WHERE template_id = #{templateId} + </delete> + + <delete id="deleteGroupsPermissions" parameterType="long"> + DELETE FROM perm_templates_groups + WHERE template_id = #{templateId} + </delete> + + <insert id="insertUserPermission" parameterType="PermissionTemplateUser"> + INSERT INTO perm_templates_users (template_id, user_id, permission_reference, created_at, updated_at) + VALUES (#{templateId}, #{userId}, #{permission}, #{createdAt}, #{updatedAt}) + </insert> + + <delete id="deleteUserPermission" parameterType="PermissionTemplateUser"> + DELETE FROM perm_templates_users + WHERE template_id = #{templateId} + AND user_id = #{userId} + AND permission_reference = #{permission} + </delete> + + <insert id="insertGroupPermission" parameterType="PermissionTemplateGroup"> + INSERT INTO perm_templates_groups (template_id, group_id, permission_reference, created_at, updated_at) + VALUES (#{templateId}, #{groupId}, #{permission}, #{createdAt}, #{updatedAt}) + </insert> + + <delete id="deleteGroupPermission" parameterType="PermissionTemplateGroup"> + DELETE FROM perm_templates_groups + WHERE template_id = #{templateId} + AND permission_reference = #{permission} + AND + <choose> + <when test="groupId != null"> + group_id = #{groupId} + </when> + <otherwise> + group_id IS NULL + </otherwise> + </choose> + </delete> + + <delete id="deleteByGroupId" parameterType="long"> + DELETE FROM perm_templates_groups + WHERE group_id = #{groupId} + </delete> + + <select id="selectUsers" parameterType="map" resultType="UserWithPermission"> + SELECT u.login as login, u.name as name, ptu.permission_reference as permission + FROM users u + LEFT JOIN perm_templates_users ptu ON ptu.user_id=u.id + AND ptu.permission_reference=#{query.permission} + AND ptu.template_id=#{templateId} + <where> + u.active = ${_true} + <choose> + <when test="query.membership() == 'IN'"> + AND ptu.permission_reference IS NOT NULL + </when> + <when test="query.membership() == 'OUT'"> + AND ptu.permission_reference IS NULL + </when> + </choose> + <if test="query.search() != null"> + AND (UPPER(u.name) LIKE #{query.searchSql} ESCAPE '/') + </if> + </where> + ORDER BY u.name + </select> + + <select id="selectGroups" parameterType="map" resultType="GroupWithPermission"> + SELECT name, description, permission FROM + (SELECT g.name as name, g.description as description, ptg.permission_reference as permission + FROM groups g + LEFT JOIN perm_templates_groups ptg ON ptg.group_id=g.id + AND ptg.permission_reference=#{query.permission} + AND ptg.template_id=#{templateId} + UNION + -- Add Anyone group permission + SELECT #{anyoneGroup} as name, NULL as description, ptg.permission_reference as permission + FROM perm_templates_groups ptg + <where> + AND ptg.permission_reference=#{query.permission} + AND ptg.template_id=#{templateId} + AND ptg.group_id IS NULL + </where> + ) groups + <where> + <if test="query.search() != null"> + AND (UPPER(groups.name) LIKE #{query.searchSql} ESCAPE '/') + </if> + </where> + ORDER BY groups.name + </select> + + <select id="selectByKey" parameterType="String" resultType="PermissionTemplate"> + SELECT id, name, kee, description, key_pattern AS keyPattern, created_at AS createdAt, updated_at AS updatedAt + FROM permission_templates + WHERE kee = #{kee} + </select> + + <select id="selectAllPermissionTemplates" resultType="PermissionTemplate"> + SELECT id, name, kee, description, key_pattern AS keyPattern, created_at AS createdAt, updated_at AS updatedAt + FROM permission_templates + </select> + + <select id="selectTemplateUsersPermissions" parameterType="String" resultMap="fullPermissionsTemplateResult"> + SELECT pt.id AS template_id, + pt.name AS template_name, + pt.description AS template_description, + pt.key_pattern AS template_key_pattern, + pt.created_at AS template_created_at, + pt.updated_at AS template_updated_at, + ptu.id AS permission_template_user_id, + ptu.permission_reference AS user_permission, + ptu.user_id AS user_id, + u.name AS user_name, + u.login AS user_login + FROM permission_templates pt + INNER JOIN perm_templates_users ptu ON ptu.template_id = pt.id + INNER JOIN users u ON u.id = ptu.user_id AND u.active = ${_true} + WHERE pt.kee = #{templateKey} + </select> + + <select id="selectTemplateGroupsPermissions" parameterType="String" resultMap="fullPermissionsTemplateResult"> + SELECT pt.id AS template_id, + pt.name AS template_name, + pt.description AS template_description, + pt.key_pattern AS template_key_pattern, + pt.created_at AS template_created_at, + pt.updated_at AS template_updated_at, + ptg.id AS permission_template_group_id, + ptg.permission_reference AS group_permission, + ptg.group_id AS group_id, + g.name AS group_name + FROM permission_templates pt + INNER JOIN perm_templates_groups ptg ON ptg.template_id = pt.id + LEFT OUTER JOIN groups g ON g.id = ptg.group_id + WHERE pt.kee = #{templateKey} + AND (g.name IS NOT NULL OR ptg.group_id IS NULL) + </select> + + <resultMap id="fullPermissionsTemplateResult" type="PermissionTemplate"> + <id property="id" column="template_id"/> + <result property="name" column="template_name"/> + <result property="description" column="template_description"/> + <result property="keyPattern" column="template_key_pattern"/> + <result property="createdAt" column="template_created_at"/> + <result property="updatedAt" column="template_updated_at"/> + <collection property="usersPermissions" ofType="PermissionTemplateUser"> + <id property="id" column="permission_template_user_id"/> + <result property="userId" column="user_id"/> + <result property="permission" column="user_permission"/> + <result property="userName" column="user_name"/> + <result property="userLogin" column="user_login"/> + </collection> + <collection property="groupsPermissions" ofType="PermissionTemplateGroup"> + <id property="id" column="permission_template_group_id"/> + <result property="groupId" column="group_id"/> + <result property="permission" column="group_permission"/> + <result property="groupName" column="group_name"/> + </collection> + </resultMap> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/property/PropertiesMapper.xml b/sonar-db/src/main/resources/org/sonar/db/property/PropertiesMapper.xml new file mode 100644 index 00000000000..d36c205772c --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/property/PropertiesMapper.xml @@ -0,0 +1,142 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.property.PropertiesMapper"> + + <select id="findUsersForNotification" parameterType="map" resultType="String"> + select u.login + from users u + inner join properties p on p.user_id=u.id + <if test="projectUuid == null"> + where p.prop_key = #{notifKey} and p.text_value LIKE 'true' and p.resource_id is null + </if> + <if test="projectUuid != null"> + inner join projects c on c.id=p.resource_id + where p.prop_key = #{notifKey} AND p.text_value LIKE 'true' + and c.uuid = #{projectUuid} and p.resource_id is not null + </if> + </select> + + <select id="findNotificationSubscribers" parameterType="map" resultType="String"> + SELECT U.login + FROM properties P, users U + WHERE P.user_id = U.id AND P.prop_key = #{propKey} AND P.text_value LIKE 'true' + AND ( + P.resource_id is null + <if test="componentKey != null"> + OR P.resource_id in (select id from projects where kee=#{componentKey}) + </if> + ) + </select> + + <select id="selectGlobalProperties" resultType="Property"> + select p.id as id, p.prop_key as "key", p.text_value as value, p.resource_id as resourceId, p.user_id as userId + from properties p + where p.resource_id is null and p.user_id is null + </select> + + <select id="selectProjectProperties" parameterType="String" resultType="Property"> + select p.id as id, p.prop_key as "key", p.text_value as value, p.resource_id as resourceId, p.user_id as userId + from properties p, projects r + where p.resource_id=r.id and p.user_id is null and r.kee=#{resourceKey} + </select> + + <select id="selectProjectPropertiesByResourceId" parameterType="Long" resultType="Property"> + select p.id as id, p.prop_key as "key", p.text_value as value, p.resource_id as resourceId, p.user_id as userId + from properties p + where p.resource_id=#{resourceId} and p.user_id is null + </select> + + <select id="selectDescendantModuleProperties" parameterType="String" resultType="Property"> + SELECT prop.id as id, prop.prop_key as "key", prop.text_value as value, prop.resource_id as resourceId, prop.user_id + as userId + FROM properties prop + INNER JOIN (SELECT p.id FROM projects p<include refid="org.sonar.db.component.ComponentMapper.modulesTreeQuery"/>) + modules on modules.id=prop.resource_id + WHERE prop.user_id IS NULL + </select> + + <select id="selectSetOfResourceProperties" parameterType="map" resultType="Property"> + select p.id as id, p.prop_key as "key", p.text_value as value, p.resource_id as resourceId, p.user_id as userId + from properties p + where p.resource_id=#{rId} and p.prop_key in + <foreach item="propKey" index="index" collection="propKeys" open="(" separator="," close=")">#{propKey}</foreach> + </select> + + <select id="selectByKey" parameterType="map" resultType="Property"> + select p.id as id, p.prop_key as "key", p.text_value as value, p.resource_id as resourceId, p.user_id as userId + from properties p + where p.prop_key=#{key} + <if test="resourceId == null"> + AND p.resource_id is null + </if> + <if test="resourceId != null"> + AND p.resource_id=#{resourceId} + </if> + <if test="userId == null"> + AND p.user_id is null + </if> + <if test="userId != null"> + AND p.user_id=#{userId} + </if> + </select> + + <select id="selectByQuery" parameterType="map" resultType="Property"> + select p.id as id, p.prop_key as "key", p.text_value as value, p.resource_id as resourceId, p.user_id as userId + from properties p + <where> + <if test="query.key() != null"> + AND p.prop_key=#{query.key} + </if> + <if test="query.componentId() != null"> + AND p.resource_id=#{query.componentId} + </if> + <if test="query.userId() != null"> + AND p.user_id=#{query.userId} + </if> + </where> + </select> + + <update id="update" parameterType="Property"> + update properties set text_value = #{value} where id = #{id} + </update> + + <insert id="insert" parameterType="Property" useGeneratedKeys="false"> + INSERT INTO properties (prop_key, resource_id, user_id, text_value) + VALUES (#{key}, #{resourceId}, #{userId}, #{value}) + </insert> + + <delete id="deleteProjectProperty" parameterType="map"> + delete from properties where prop_key=#{key} and resource_id=#{rId} and user_id is null + </delete> + + <delete id="deleteProjectProperties" parameterType="map"> + DELETE FROM properties + WHERE + prop_key=#{key} + AND text_value LIKE #{value} + AND resource_id IS NOT NULL + AND user_id IS NULL + </delete> + + <delete id="deleteGlobalProperty" parameterType="string"> + delete from properties where prop_key=#{id} and resource_id is null and user_id is null + </delete> + + <delete id="deleteGlobalProperties"> + delete from properties where resource_id is null and user_id is null + </delete> + + <delete id="deleteAllProperties" parameterType="string"> + delete from properties where prop_key=#{id} + </delete> + + <update id="renamePropertyKey" parameterType="map"> + update properties set prop_key=#{newKey} where prop_key=#{oldKey} + </update> + + <update id="updateProperties" parameterType="map"> + update properties set text_value=#{newValue} where text_value LIKE #{oldValue} and prop_key=#{key} + </update> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/purge/PurgeMapper.xml b/sonar-db/src/main/resources/org/sonar/db/purge/PurgeMapper.xml new file mode 100644 index 00000000000..1d6bed47806 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/purge/PurgeMapper.xml @@ -0,0 +1,341 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.purge.PurgeMapper"> + + <select id="selectSnapshotIds" parameterType="map" resultType="long"> + select s.id from snapshots s + <where> + <if test="islast != null"> + and s.islast=#{islast} + </if> + <if test="notPurged != null and notPurged"> + and (s.purge_status is null or s.purge_status=0) + </if> + <if test="rootSnapshotId != null"> + and s.root_snapshot_id=#{rootSnapshotId} + </if> + <if test="id != null"> + and s.id=#{id} + </if> + <if test="rootProjectId != null"> + and s.root_project_id=#{rootProjectId} + </if> + <if test="resourceId != null"> + and s.project_id=#{resourceId} + </if> + <if test="status != null"> + and s.status in + <foreach item="s" index="index" collection="status" open="(" separator="," close=")">#{s}</foreach> + </if> + <if test="scopes != null"> + and s.scope in + <foreach item="scope" index="index" collection="scopes" open="(" separator="," close=")">#{scope}</foreach> + </if> + <if test="qualifiers != null"> + and s.qualifier in + <foreach item="qualifier" index="index" collection="qualifiers" open="(" separator="," close=")">#{qualifier} + </foreach> + </if> + <if test="withVersionEvent != null"> + <if test="withVersionEvent"> + and exists(select e.id from events e where e.snapshot_id=s.id and e.category='Version') + </if> + <if test="!withVersionEvent"> + and not exists(select e.id from events e where e.snapshot_id=s.id and e.category='Version') + </if> + </if> + </where> + </select> + + <select id="selectSnapshotIdsByResource" parameterType="map" resultType="long"> + select s.id from snapshots s + <where> + s.project_id in + <foreach collection="resourceIds" open="(" close=")" item="resourceId" separator=","> + #{resourceId} + </foreach> + </where> + </select> + + <select id="selectPurgeableSnapshotsWithEvents" parameterType="long" resultType="PurgeableSnapshot"> + select s.id as "snapshotId", s.created_at as "date", ${_true} as "hasEvents", islast as "isLast" from + snapshots s where + s.project_id=#{id} and s.status='P' and s.qualifier <> 'LIB' and + exists(select e.id from events e where e.snapshot_id=s.id) + </select> + + <select id="selectPurgeableSnapshotsWithoutEvents" parameterType="long" resultType="PurgeableSnapshot"> + select s.id as "snapshotId", s.created_at as "date", ${_false} as "hasEvents", islast as "isLast" from + snapshots s where + s.project_id=#{id} and s.status='P' and s.qualifier <> 'LIB' and + not exists(select e.id from events e where e.snapshot_id=s.id) + </select> + + <select id="selectComponentIdUuidsToDisable" resultType="IdUuidPair" parameterType="long"> + select p.id, p.uuid from projects p + where (p.id=#{id} or p.root_id=#{id}) and p.enabled=${_true} + and not exists(select s.project_id from snapshots s where s.islast=${_true} and s.project_id=p.id) + </select> + + <select id="selectPurgeableFileUuids" resultType="string" parameterType="long"> + select p.uuid from projects p + where (p.id=#{id} or p.root_id=#{id}) and p.enabled=${_true} and p.scope='FIL' + and not exists(select s.project_id from snapshots s where s.islast=${_true} and s.project_id=p.id) + </select> + + <select id="selectMetricIdsWithoutHistoricalData" resultType="long"> + select id from metrics where delete_historical_data=${_true} + </select> + + <select id="selectProjectIdUuidsByRootId" resultType="IdUuidPair" parameterType="long"> + select id, uuid from projects where root_id=#{id} and scope='PRJ' + </select> + + <select id="selectComponentIdUuidsByRootId" resultType="IdUuidPair" parameterType="long"> + select id, uuid from projects where root_id=#{id} or id=#{id} + </select> + + <delete id="deleteSnapshotMeasures" parameterType="map"> + delete from project_measures where snapshot_id in + <foreach collection="snapshotIds" open="(" close=")" item="snapshotId" separator=","> + #{snapshotId} + </foreach> + </delete> + + <delete id="deleteSnapshotDuplications" parameterType="map"> + delete from duplications_index where snapshot_id in + <foreach collection="snapshotIds" open="(" close=")" item="snapshotId" separator=","> + #{snapshotId} + </foreach> + </delete> + + <delete id="deleteSnapshotEvents" parameterType="map"> + delete from events where snapshot_id in + <foreach collection="snapshotIds" open="(" close=")" item="snapshotId" separator=","> + #{snapshotId} + </foreach> + </delete> + + <delete id="deleteSnapshot" parameterType="map"> + delete from snapshots where id in + <foreach collection="snapshotIds" open="(" close=")" item="snapshotId" separator=","> + #{snapshotId} + </foreach> + </delete> + + <delete id="deleteSnapshotWastedMeasures" parameterType="map"> + delete from project_measures + <where> + snapshot_id in + <foreach collection="snapshotIds" open="(" close=")" item="snapshotId" separator=","> + #{snapshotId} + </foreach> + and (rule_id is not null or person_id is not null + <if test="mids.size()>0"> + or metric_id in + <foreach item="mid" index="index" collection="mids" open="(" separator="," close=")">#{mid}</foreach> + </if> + ) + </where> + </delete> + + <update id="updatePurgeStatusToOne" parameterType="long"> + update snapshots set purge_status = 1 where id = #{id} + </update> + + <update id="disableResource" parameterType="long"> + update projects set enabled=${_false} where id=#{id} + </update> + + <update id="resolveResourceIssuesNotAlreadyResolved" parameterType="map"> + UPDATE issues SET status='CLOSED',resolution='REMOVED',updated_at=#{dateAsLong},issue_close_date=#{dateAsLong}, + issue_update_date=#{dateAsLong} + WHERE component_uuid=#{componentUuid} AND resolution IS NULL + </update> + + <delete id="deleteResourceIndex" parameterType="map"> + delete from resource_index where resource_id in + <foreach collection="resourceIds" open="(" close=")" item="resourceId" separator=","> + #{resourceId} + </foreach> + </delete> + + <delete id="deleteEvent" parameterType="map"> + delete from events where id in + <foreach collection="resourceIds" open="(" close=")" item="resourceId" separator=","> + #{resourceId} + </foreach> + </delete> + + <delete id="deleteResourceLinks" parameterType="map"> + delete from project_links where component_uuid in + <foreach collection="componentUuids" open="(" close=")" item="componentUuid" separator=","> + #{componentUuid} + </foreach> + </delete> + + <delete id="deleteResourceProperties" parameterType="map"> + delete from properties where resource_id in + <foreach collection="resourceIds" open="(" close=")" item="resourceId" separator=","> + #{resourceId} + </foreach> + </delete> + + <delete id="deleteResource" parameterType="map"> + delete from projects where id in + <foreach collection="resourceIds" open="(" close=")" item="resourceId" separator=","> + #{resourceId} + </foreach> + </delete> + + <delete id="deleteResourceGroupRoles" parameterType="map"> + delete from group_roles where resource_id in + <foreach collection="resourceIds" open="(" close=")" item="resourceId" separator=","> + #{resourceId} + </foreach> + </delete> + + <delete id="deleteResourceUserRoles" parameterType="map"> + delete from user_roles where resource_id in + <foreach collection="resourceIds" open="(" close=")" item="resourceId" separator=","> + #{resourceId} + </foreach> + </delete> + + <delete id="deleteResourceManualMeasures" parameterType="map"> + delete from manual_measures where component_uuid in + <foreach collection="componentUuids" open="(" close=")" item="componentUuid" separator=","> + #{componentUuid} + </foreach> + </delete> + + <delete id="deleteComponentEvents" parameterType="map"> + delete from events where component_uuid in + <foreach collection="componentUuids" open="(" close=")" item="componentUuid" separator=","> + #{componentUuid} + </foreach> + </delete> + + <delete id="deleteResourceActionPlans" parameterType="map"> + delete from action_plans where project_id in + <foreach collection="resourceIds" open="(" close=")" item="resourceId" separator=","> + #{resourceId} + </foreach> + </delete> + + <delete id="deleteAuthors" parameterType="map"> + delete from authors where person_id in + <foreach collection="resourceIds" open="(" close=")" item="resourceId" separator=","> + #{resourceId} + </foreach> + </delete> + + <update id="setSnapshotIsLastToFalse" parameterType="long"> + update snapshots set islast=${_false} where project_id=#{id} + </update> + + <delete id="deleteComponentIssueChanges" parameterType="map"> + delete from issue_changes ic + where exists (select * from issues i where i.kee=ic.issue_key and i.component_uuid in + <foreach collection="componentUuids" open="(" close=")" item="componentUuid" separator=","> + #{componentUuid} + </foreach> + ) + </delete> + + <!-- Mssql --> + <delete id="deleteComponentIssueChanges" databaseId="mssql" parameterType="map"> + delete issue_changes from issue_changes + inner join issues on issue_changes.issue_key=issues.kee + where issues.component_uuid in + <foreach collection="componentUuids" open="(" close=")" item="componentUuid" separator=","> + #{componentUuid} + </foreach> + </delete> + + <!-- Mysql --> + <delete id="deleteComponentIssueChanges" databaseId="mysql" parameterType="map"> + delete ic from issue_changes as ic, issues as i where ic.issue_key=i.kee and i.component_uuid in + <foreach collection="componentUuids" open="(" close=")" item="componentUuid" separator=","> + #{componentUuid} + </foreach> + </delete> + + <delete id="deleteComponentIssues" parameterType="map"> + delete from issues where component_uuid in + <foreach collection="componentUuids" open="(" close=")" item="componentUuid" separator=","> + #{componentUuid} + </foreach> + </delete> + + <delete id="deleteFileSourcesByProjectUuid"> + delete from file_sources where project_uuid=#{rootProjectUuid} + </delete> + + <delete id="deleteFileSourcesByUuid"> + delete from file_sources where file_uuid=#{fileUuid} + </delete> + + <delete id="deleteOldClosedIssueChanges" parameterType="map"> + delete from issue_changes ic + where exists ( + select * from issues i + where i.project_uuid=#{projectUuid} and i.kee=ic.issue_key + <choose> + <when test="toDate == null"> + and i.issue_close_date is not null + </when> + <otherwise> + and i.issue_close_date < #{toDate} + </otherwise> + </choose> + ) + </delete> + + <!-- Mssql --> + <delete id="deleteOldClosedIssueChanges" databaseId="mssql" parameterType="map"> + delete issue_changes from issue_changes + inner join issues on issue_changes.issue_key=issues.kee + where issues.project_uuid=#{projectUuid} + <choose> + <when test="toDate == null"> + and issues.issue_close_date is not null + </when> + <otherwise> + and issues.issue_close_date < #{toDate} + </otherwise> + </choose> + </delete> + + <!-- Mysql --> + <delete id="deleteOldClosedIssueChanges" databaseId="mysql" parameterType="map"> + delete ic + from issue_changes as ic, issues as i + where i.project_uuid=#{projectUuid} + and ic.issue_key=i.kee + <choose> + <when test="toDate == null"> + and i.issue_close_date is not null + </when> + <otherwise> + and i.issue_close_date < #{toDate} + </otherwise> + </choose> + </delete> + + <delete id="deleteOldClosedIssues" parameterType="map"> + delete from issues + where project_uuid=#{projectUuid} + <choose> + <when test="toDate == null"> + and issue_close_date is not null + </when> + <otherwise> + and issue_close_date < #{toDate} + </otherwise> + </choose> + </delete> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/qualitygate/ProjectQgateAssociationMapper.xml b/sonar-db/src/main/resources/org/sonar/db/qualitygate/ProjectQgateAssociationMapper.xml new file mode 100644 index 00000000000..bbcf7b52121 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/qualitygate/ProjectQgateAssociationMapper.xml @@ -0,0 +1,35 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.qualitygate.ProjectQgateAssociationMapper"> + + <select id="selectProjects" parameterType="map" resultType="ProjectQgateAssociation"> + SELECT proj.id as id, proj.name as name, prop.text_value as gateId + FROM projects proj + <if test="query.projectSearch() != null"> + JOIN resource_index ind ON ind.root_project_id=proj.id + </if> + LEFT JOIN properties prop ON prop.resource_id=proj.id AND prop.prop_key='sonar.qualitygate' AND prop.text_value LIKE + #{gateId} + <where> + <choose> + <when test="query.membership() == 'selected'"> + AND prop.text_value IS NOT NULL + </when> + <when test="query.membership() == 'deselected'"> + AND prop.text_value IS NULL + </when> + </choose> + <if test="query.projectSearch() != null"> + AND ind.kee LIKE #{query.projectSearchSql} + </if> + AND proj.qualifier='TRK' + AND proj.scope='PRJ' + <if test="query.projectSearch() != null"> + AND ind.qualifier='TRK' + </if> + </where> + ORDER BY proj.name + </select> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/qualitygate/QualityGateConditionMapper.xml b/sonar-db/src/main/resources/org/sonar/db/qualitygate/QualityGateConditionMapper.xml new file mode 100644 index 00000000000..0923db5ae60 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/qualitygate/QualityGateConditionMapper.xml @@ -0,0 +1,53 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.qualitygate.QualityGateConditionMapper"> + + <insert id="insert" parameterType="QualityGateCondition" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + insert into quality_gate_conditions (qgate_id, metric_id, operator, value_error, value_warning, period, created_at, + updated_at) + values (#{qualityGateId}, #{metricId}, #{operator}, #{errorThreshold}, #{warningThreshold}, #{period}, #{createdAt}, + #{updatedAt}) + </insert> + + <sql id="conditionColumns"> + id, qgate_id as qualityGateId, metric_id as metricId, operator, value_warning as warningThreshold, value_error as + errorThreshold, period, + created_at as createdAt, updated_at as updatedAt + </sql> + + <select id="selectForQualityGate" resultType="QualityGateCondition" parameterType="long"> + select + <include refid="conditionColumns"/> + from quality_gate_conditions where qgate_id=#{qGateId} + order by id asc + </select> + + <select id="selectById" parameterType="long" resultType="QualityGateCondition"> + select + <include refid="conditionColumns"/> + from quality_gate_conditions where id=#{id} + </select> + + <update id="delete" parameterType="long"> + delete from quality_gate_conditions where id=#{id} + </update> + + <update id="update" parameterType="QualityGateCondition"> + update quality_gate_conditions set + metric_id=#{metricId}, + operator=#{operator}, + value_warning=#{warningThreshold}, + value_error=#{errorThreshold}, + period=#{period}, + updated_at=#{updatedAt} + where id=#{id} + </update> + + <delete id="deleteConditionsWithInvalidMetrics"> + delete from quality_gate_conditions + where metric_id not in (select id from metrics where enabled=${_true}) + </delete> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/qualitygate/QualityGateMapper.xml b/sonar-db/src/main/resources/org/sonar/db/qualitygate/QualityGateMapper.xml new file mode 100644 index 00000000000..64d2e58efb4 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/qualitygate/QualityGateMapper.xml @@ -0,0 +1,48 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.qualitygate.QualityGateMapper"> + + <insert id="insert" parameterType="QualityGate" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + insert into quality_gates (name, created_at, updated_at) + values (#{name}, #{createdAt}, #{updatedAt}) + </insert> + + <sql id="gateColumns"> + id, name, created_at as createdAt, updated_at as updatedAt + </sql> + + <select id="selectAll" resultType="QualityGate"> + select + <include refid="gateColumns"/> + from quality_gates + order by name asc + </select> + + <select id="selectByName" parameterType="String" resultType="QualityGate"> + select + <include refid="gateColumns"/> + from quality_gates + where name=#{name} + </select> + + <select id="selectById" parameterType="long" resultType="QualityGate"> + select + <include refid="gateColumns"/> + from quality_gates + where id=#{id} + </select> + + <update id="delete" parameterType="long"> + delete from quality_gates where id=#{id} + </update> + + <update id="update" parameterType="QualityGate"> + update quality_gates set + name=#{name}, + updated_at=#{updatedAt} + where id=#{id} + </update> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/qualityprofile/ActiveRuleMapper.xml b/sonar-db/src/main/resources/org/sonar/db/qualityprofile/ActiveRuleMapper.xml new file mode 100644 index 00000000000..23ac091ce8a --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/qualityprofile/ActiveRuleMapper.xml @@ -0,0 +1,208 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.qualityprofile.ActiveRuleMapper"> + + <sql id="activeRuleKeyColumns"> + a.id, + a.profile_id as "profileId", + a.rule_id as "ruleId", + a.failure_level as "severity", + a.inheritance as "inheritance", + r.plugin_rule_key as "rulefield", + r.plugin_name as "repository", + qp.kee as "profileKey", + a.created_at as "createdAt", + a.updated_at as "updatedAt" + </sql> + + <sql id="activeRuleKeyJoin"> + LEFT JOIN rules_profiles qp ON qp.id=a.profile_id + LEFT JOIN rules r ON r.id = a.rule_id + </sql> + + + <sql id="activeRuleColumns"> + a.id, + a.profile_id as profileId, + a.rule_id as ruleId, + a.failure_level as severity, + a.inheritance as inheritance, + active_rule_parent.id as parentId, + a.created_at as "createdAt", + a.updated_at as "updatedAt" + </sql> + + <sql id="activeRuleJoin"> + LEFT JOIN rules_profiles qp ON qp.id=a.profile_id + LEFT JOIN rules_profiles profile_parent ON profile_parent.kee=qp.parent_kee + LEFT JOIN active_rules active_rule_parent ON active_rule_parent.profile_id=profile_parent.id AND + a.rule_id=active_rule_parent.rule_id + </sql> + + <select id="selectAfterDate" parameterType="Date" resultType="ActiveRule" + fetchSize="${_scrollFetchSize}" resultSetType="FORWARD_ONLY"> + select + <include refid="activeRuleKeyColumns"/> + from active_rules a + <include refid="activeRuleKeyJoin"/> + <where> + <if test="date != null"> + a.updated_at IS NULL or a.updated_at >= #{date} + </if> + </where> + </select> + + <insert id="insert" parameterType="ActiveRule" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO active_rules (profile_id, rule_id, failure_level, inheritance, created_at, updated_at) + VALUES (#{profileId}, #{ruleId}, #{severity}, #{inheritance}, #{createdAt}, #{updatedAt}) + </insert> + + <update id="update" parameterType="ActiveRule"> + UPDATE active_rules SET + profile_id=#{profileId}, + rule_id=#{ruleId}, + failure_level=#{severity}, + inheritance=#{inheritance}, + updated_at=#{updatedAt} + WHERE id=#{id} + </update> + + <update id="delete" parameterType="int"> + DELETE FROM active_rules WHERE id=#{id} + </update> + + <select id="selectByIds" parameterType="map" resultType="ActiveRule"> + select + <include refid="activeRuleColumns"/> + from active_rules a + <include refid="activeRuleJoin"/> + <where> + (<foreach collection="list" item="id" open="(" separator=" or " close=")"> + a.id=#{id} + </foreach>) + </where> + </select> + + <select id="selectById" parameterType="int" resultType="ActiveRule"> + SELECT + <include refid="activeRuleColumns"/> + FROM active_rules a + <include refid="activeRuleJoin"/> + WHERE a.id=#{id} + </select> + + + <select id="selectByKey" parameterType="map" resultType="ActiveRule"> + SELECT + <include refid="activeRuleKeyColumns"/> + FROM active_rules a + <include refid="activeRuleKeyJoin"/> + WHERE + qp.kee = #{profileKey} + AND r.plugin_rule_key = #{rule} + AND r.plugin_name = #{repository} + </select> + + <select id="selectByProfileKey" parameterType="string" resultType="ActiveRule"> + SELECT + <include refid="activeRuleKeyColumns"/> + FROM active_rules a + <include refid="activeRuleKeyJoin"/> + where qp.kee=#{id} + </select> + + <select id="selectByRuleId" parameterType="Integer" resultType="ActiveRule"> + SELECT + <include refid="activeRuleKeyColumns"/> + FROM active_rules a + <include refid="activeRuleKeyJoin"/> + WHERE a.rule_id=#{ruleId} + </select> + + <select id="selectAll" parameterType="map" resultType="ActiveRule"> + select + <include refid="activeRuleColumns"/> + from active_rules a + <include refid="activeRuleJoin"/> + </select> + + <!-- Parameters --> + + <sql id="activeRuleParamColumns"> + p.id, + p.active_rule_id as activeRuleId, + p.rules_parameter_id as rulesParameterId, + p.rules_parameter_key as kee, + p.value as value + </sql> + + <insert id="insertParameter" parameterType="ActiveRuleParam" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO active_rule_parameters (active_rule_id, rules_parameter_id, rules_parameter_key, value) + VALUES (#{activeRuleId}, #{rulesParameterId}, #{key}, #{value}) + </insert> + + <update id="updateParameter" parameterType="ActiveRuleParam"> + UPDATE active_rule_parameters SET + active_rule_id=#{activeRuleId}, + rules_parameter_id=#{rulesParameterId}, + rules_parameter_key=#{key}, + value=#{value} + WHERE id=#{id} + </update> + + <update id="deleteParameters" parameterType="int"> + DELETE FROM active_rule_parameters WHERE active_rule_id=#{id} + </update> + + <update id="deleteParameter" parameterType="int"> + DELETE FROM active_rule_parameters WHERE id=#{id} + </update> + + <select id="selectParamsByActiveRuleId" parameterType="Integer" resultType="ActiveRuleParam"> + select + <include refid="activeRuleParamColumns"/> + from active_rule_parameters p + <where> + p.active_rule_id=#{id} + </where> + </select> + + <select id="selectParamsByActiveRuleIds" parameterType="map" resultType="ActiveRuleParam"> + select + <include refid="activeRuleParamColumns"/> + from active_rule_parameters p + <where> + (<foreach collection="list" item="id" open="(" separator=" or " close=")"> + p.active_rule_id=#{id} + </foreach>) + </where> + </select> + + <select id="selectParamByActiveRuleAndKey" parameterType="map" resultType="ActiveRuleParam"> + SELECT + <include refid="activeRuleParamColumns"/> + FROM active_rule_parameters p + <where> + AND p.active_rule_id=#{activeRuleId} + AND p.rules_parameter_key=#{key} + </where> + </select> + + <select id="selectParamsByProfileKey" parameterType="string" resultType="ActiveRuleParam"> + select + <include refid="activeRuleParamColumns"/> + from active_rule_parameters p + inner join active_rules ar on ar.id=p.active_rule_id + inner join rules_profiles rp on rp.id=ar.profile_id + where rp.kee=#{id} + </select> + + <select id="selectAllParams" resultType="ActiveRuleParam"> + select + <include refid="activeRuleParamColumns"/> + from active_rule_parameters p + </select> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/qualityprofile/QualityProfileMapper.xml b/sonar-db/src/main/resources/org/sonar/db/qualityprofile/QualityProfileMapper.xml new file mode 100644 index 00000000000..873a983c4cb --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/qualityprofile/QualityProfileMapper.xml @@ -0,0 +1,209 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.qualityprofile.QualityProfileMapper"> + + <sql id="profilesColumns"> + p.id as id, + p.kee as kee, + p.name as name, + p.language as language, + p.parent_kee as parentKee, + p.is_default as isDefault, + p.created_at as createdAt, + p.updated_at as updatedAt, + p.rules_updated_at as rulesUpdatedAt + </sql> + + <insert id="insert" parameterType="QualityProfile" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO rules_profiles (kee, parent_kee, name, language, is_default, created_at, updated_at, rules_updated_at) + VALUES (#{kee}, #{parentKee}, #{name}, #{language}, #{isDefault}, #{createdAt}, #{updatedAt}, #{rulesUpdatedAt,}) + </insert> + + <update id="update" parameterType="QualityProfile"> + UPDATE rules_profiles SET + name=#{name}, + language=#{language}, + is_default=#{isDefault}, + parent_kee=#{parentKee}, + updated_at=#{updatedAt}, + rules_updated_at=#{rulesUpdatedAt} + WHERE id=#{id} + </update> + + <update id="delete" parameterType="int"> + DELETE FROM rules_profiles WHERE id=#{id} + </update> + + <select id="selectAll" parameterType="map" resultType="QualityProfile"> + SELECT + <include refid="profilesColumns"/> + FROM rules_profiles p + ORDER BY p.name, p.language + </select> + + <select id="selectByNameAndLanguage" parameterType="map" resultType="QualityProfile"> + SELECT + <include refid="profilesColumns"/> + FROM rules_profiles p + WHERE p.name=#{name} AND p.language=#{language} + </select> + + <select id="selectByKey" parameterType="string" resultType="QualityProfile"> + SELECT + <include refid="profilesColumns"/> + FROM rules_profiles p + WHERE p.kee=#{id} + </select> + + <select id="selectByLanguage" parameterType="String" resultType="QualityProfile"> + SELECT + <include refid="profilesColumns"/> + FROM rules_profiles p + WHERE p.language=#{language} + ORDER BY p.name + </select> + + <select id="selectById" parameterType="Integer" resultType="QualityProfile"> + SELECT + <include refid="profilesColumns"/> + FROM rules_profiles p + WHERE p.id=#{id} + </select> + + <select id="selectParent" parameterType="string" resultType="QualityProfile"> + SELECT + <include refid="profilesColumns"/> + FROM rules_profiles p + INNER JOIN rules_profiles child ON child.parent_kee=p.kee AND child.kee=#{id} + </select> + + <select id="selectParentById" parameterType="int" resultType="QualityProfile"> + SELECT + <include refid="profilesColumns"/> + FROM rules_profiles p + INNER JOIN rules_profiles child ON child.parent_kee=p.kee and child.id=#{id} + </select> + + <select id="selectChildren" parameterType="string" resultType="QualityProfile"> + SELECT + <include refid="profilesColumns"/> + FROM rules_profiles p + WHERE p.parent_kee=#{id} + ORDER BY p.name + </select> + + <select id="selectDefaultProfile" parameterType="map" resultType="QualityProfile"> + SELECT + <include refid="profilesColumns"/> + FROM rules_profiles p + WHERE p.is_default=${_true} + AND p.language=#{language} + </select> + + <select id="selectProjects" resultType="Component"> + SELECT projects.id as id, projects.name as name, projects.kee as kee, projects.uuid as uuid + FROM projects projects + JOIN project_qprofiles pp ON pp.project_uuid = projects.uuid + JOIN rules_profiles prof ON pp.profile_key = prof.kee + <where> + AND prof.name = #{profileName} + AND prof.language = #{language} + </where> + </select> + + <select id="selectSelectedProjects" resultType="org.sonar.db.qualityprofile.ProjectQprofileAssociationDto"> + SELECT pp.id as id, pj.id as projectId, pj.uuid as projectUuid, pj.name as projectName, pp.profile_key as profileKey + FROM projects pj + JOIN project_qprofiles pp ON pp.project_uuid = pj.uuid + AND pp.profile_key = #{profileKey} + <where> + AND pj.scope='PRJ' AND pj.qualifier='TRK' + AND UPPER(pj.name) LIKE #{nameQuery} + </where> + ORDER BY pj.name ASC + </select> + + <select id="selectDeselectedProjects" resultType="org.sonar.db.qualityprofile.ProjectQprofileAssociationDto"> + SELECT pp.id as id, pj.id as projectId, pj.uuid as projectUuid, pj.name as projectName, pp.profile_key as profileKey + FROM projects pj + LEFT JOIN project_qprofiles pp ON pp.project_uuid = pj.uuid + AND pp.profile_key = #{profileKey} + <where> + AND pj.scope='PRJ' AND pj.qualifier='TRK' + AND UPPER(pj.name) LIKE #{nameQuery} + AND pp.profile_key IS NULL + </where> + ORDER BY pj.name ASC + </select> + + <select id="selectProjectAssociations" resultType="org.sonar.db.qualityprofile.ProjectQprofileAssociationDto"> + SELECT pp.id as id, pj.id as projectId, pj.uuid as projectUuid, pj.name as projectName, pp.profile_key as profileKey + FROM projects pj + LEFT JOIN project_qprofiles pp ON pp.project_uuid = pj.uuid + AND pp.profile_key = #{profileKey} + <where> + AND pj.scope='PRJ' AND pj.qualifier='TRK' + AND UPPER(pj.name) LIKE #{nameQuery} + </where> + ORDER BY pj.name ASC + </select> + + <select id="countProjects" parameterType="Integer" resultType="Integer"> + SELECT count(projects.id) + FROM projects projects + JOIN project_qprofiles pp ON pp.project_uuid=projects.uuid + JOIN rules_profiles prof ON pp.profile_key=prof.kee + <where> + AND prof.language=#{language} + AND prof.name=#{profileName} + </where> + </select> + + <select id="countProjectsByProfile" resultType="org.sonar.db.qualityprofile.QualityProfileProjectCount"> + SELECT pp.profile_key as profileKey, count(projects.id) as projectCount + FROM projects projects + INNER JOIN project_qprofiles pp ON pp.project_uuid=projects.uuid + INNER JOIN rules_profiles prof ON pp.profile_key=prof.kee + WHERE projects.enabled=${_true} + GROUP BY pp.profile_key + </select> + + <select id="selectByProjectIdAndLanguage" parameterType="map" resultType="QualityProfile"> + SELECT + <include refid="profilesColumns"/> + FROM rules_profiles p + JOIN project_qprofiles pp ON pp.profile_key=p.kee + JOIN projects project ON pp.project_uuid=project.uuid + AND project.id=#{projectId} + WHERE p.language=#{language} + </select> + + <select id="selectByProjectAndLanguage" parameterType="map" resultType="QualityProfile"> + SELECT + <include refid="profilesColumns"/> + FROM rules_profiles p + JOIN project_qprofiles pp ON pp.profile_key=p.kee + JOIN projects project ON pp.project_uuid=project.uuid + AND project.kee=#{projectKey} + WHERE p.language=#{language} + </select> + + <insert id="insertProjectProfileAssociation" keyColumn="id" useGeneratedKeys="true"> + INSERT INTO project_qprofiles (project_uuid, profile_key) VALUES (#{projectUuid}, #{profileKey}) + </insert> + + <update id="updateProjectProfileAssociation"> + UPDATE project_qprofiles SET profile_key=#{profileKey} WHERE project_uuid=#{projectUuid} + </update> + + <update id="deleteProjectProfileAssociation"> + DELETE FROM project_qprofiles WHERE project_uuid=#{projectUuid} AND profile_key=#{profileKey} + </update> + + <update id="deleteAllProjectProfileAssociation"> + DELETE FROM project_qprofiles WHERE profile_key=#{profileKey} + </update> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/rule/RuleMapper.xml b/sonar-db/src/main/resources/org/sonar/db/rule/RuleMapper.xml new file mode 100644 index 00000000000..fd93ba7b0a5 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/rule/RuleMapper.xml @@ -0,0 +1,219 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.rule.RuleMapper"> + + <sql id="selectColumns"> + r.id, + r.plugin_rule_key as "ruleKey", + r.plugin_name as "repositoryKey", + r.description, + r.description_format as "descriptionFormat", + r.status, + r.name, + r.plugin_config_key as "configKey", + r.priority as "severity", + r.is_template as "isTemplate", + r.language as "language", + r.template_id as "templateId", + r.note_data as "noteData", + r.note_user_login as "noteUserLogin", + r.note_created_at as "noteCreatedAt", + r.note_updated_at as "noteUpdatedAt", + r.characteristic_id as "subCharacteristicId", + r.default_characteristic_id as "defaultSubCharacteristicId", + r.remediation_function as "remediationFunction", + r.default_remediation_function as "defaultRemediationFunction", + r.remediation_coeff as "remediationCoefficient", + r.default_remediation_coeff as "defaultRemediationCoefficient", + r.remediation_offset as "remediationOffset", + r.default_remediation_offset as "defaultRemediationOffset", + r.effort_to_fix_description as "effortToFixDescription", + r.tags as "tagsField", + r.system_tags as "systemTagsField", + r.created_at as "createdAt", + r.updated_at as "updatedAt" + </sql> + + <select id="selectAll" resultType="Rule"> + select + <include refid="selectColumns"/> + from rules r + </select> + + <select id="selectAfterDate" resultType="Rule" fetchSize="${_scrollFetchSize}" resultSetType="FORWARD_ONLY"> + select + <include refid="selectColumns"/> + from rules r + <where> + <if test="date != null"> + r.updated_at IS NULL or r.updated_at >= #{date} + </if> + </where> + </select> + + <select id="selectEnablesAndNonManual" resultType="Rule"> + select + <include refid="selectColumns"/> + from rules r + where r.status != 'REMOVED' and r.plugin_name != 'manual' + </select> + + <select id="selectById" parameterType="Integer" resultType="Rule"> + select + <include refid="selectColumns"/> + from rules r WHERE r.id=#{id} + </select> + + <select id="selectByKey" parameterType="map" resultType="Rule"> + SELECT + <include refid="selectColumns"/> + FROM rules r WHERE r.plugin_name=#{repository} AND r.plugin_rule_key=#{rule} + </select> + + <select id="selectByName" parameterType="String" resultType="Rule"> + select + <include refid="selectColumns"/> + from rules r WHERE r.name=#{name} + </select> + + <select id="selectNonManual" resultType="Rule"> + select + <include refid="selectColumns"/> + from rules r + where r.plugin_name != 'manual' + </select> + + <select id="selectBySubCharacteristicId" resultType="Rule"> + select + <include refid="selectColumns"/> + from rules r + where (r.characteristic_id=#{subCharacteristicId} or r.default_characteristic_id=#{subCharacteristicId}) + </select> + + <update id="update" parameterType="Rule"> + UPDATE rules SET + plugin_rule_key=#{ruleKey}, + plugin_name=#{repositoryKey}, + description=#{description}, + description_format=#{descriptionFormat}, + status=#{status}, + name=#{name}, + plugin_config_key=#{configKey}, + priority=#{severity}, + is_template=#{isTemplate}, + language=#{language}, + template_id=#{templateId}, + note_data=#{noteData}, + note_user_login=#{noteUserLogin}, + note_created_at=#{noteCreatedAt}, + note_updated_at=#{noteUpdatedAt}, + characteristic_id=#{subCharacteristicId}, + default_characteristic_id=#{defaultSubCharacteristicId}, + remediation_function=#{remediationFunction}, + default_remediation_function=#{defaultRemediationFunction}, + remediation_coeff=#{remediationCoefficient}, + default_remediation_coeff=#{defaultRemediationCoefficient}, + remediation_offset=#{remediationOffset}, + default_remediation_offset=#{defaultRemediationOffset}, + effort_to_fix_description=#{effortToFixDescription}, + updated_at=#{updatedAt}, + tags=#{tagsField}, + system_tags=#{systemTagsField} + WHERE id=#{id} + </update> + + <sql id="insertColumns"> + (plugin_rule_key, plugin_name, description, description_format, status, name, plugin_config_key, priority, + is_template, language, template_id, + characteristic_id, default_characteristic_id, remediation_function, default_remediation_function, + remediation_coeff, default_remediation_coeff, remediation_offset, default_remediation_offset, + effort_to_fix_description, tags, system_tags, note_data, note_user_login, note_created_at, note_updated_at, + created_at, updated_at) + </sql> + + <insert id="insert" parameterType="Rule" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + insert into rules + <include refid="insertColumns"/> + values (#{ruleKey}, #{repositoryKey}, #{description}, #{descriptionFormat}, #{status}, #{name}, #{configKey}, + #{severity}, #{isTemplate}, #{language}, #{templateId}, + #{subCharacteristicId}, #{defaultSubCharacteristicId}, #{remediationFunction}, #{defaultRemediationFunction}, + #{remediationCoefficient}, #{defaultRemediationCoefficient}, #{remediationOffset}, #{defaultRemediationOffset}, + #{effortToFixDescription}, #{tagsField}, #{systemTagsField}, #{noteData}, #{noteUserLogin}, #{noteCreatedAt}, + #{noteUpdatedAt}, #{createdAt}, #{updatedAt}) + </insert> + + <insert id="batchInsert" parameterType="Rule" useGeneratedKeys="false"> + insert into rules + <include refid="insertColumns"/> + values (#{ruleKey,jdbcType=VARCHAR}, #{repositoryKey,jdbcType=VARCHAR}, #{description,jdbcType=CLOB}, + #{descriptionFormat,jdbcType=VARCHAR}, #{status,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, + #{configKey,jdbcType=VARCHAR}, + #{severity,jdbcType=INTEGER}, #{isTemplate,jdbcType=BOOLEAN}, #{language,jdbcType=VARCHAR}, + #{templateId,jdbcType=INTEGER}, + #{subCharacteristicId,jdbcType=INTEGER}, #{defaultSubCharacteristicId,jdbcType=INTEGER}, + #{remediationFunction,jdbcType=VARCHAR}, #{defaultRemediationFunction,jdbcType=VARCHAR}, + #{remediationCoefficient,jdbcType=VARCHAR}, #{defaultRemediationCoefficient,jdbcType=VARCHAR}, + #{remediationOffset,jdbcType=VARCHAR}, #{defaultRemediationOffset,jdbcType=VARCHAR}, + #{effortToFixDescription}, #{tagsField}, #{systemTagsField}, #{noteData}, #{noteUserLogin}, #{noteCreatedAt}, + #{noteUpdatedAt,jdbcType=TIMESTAMP}, #{createdAt,jdbcType=TIMESTAMP}, #{updatedAt,jdbcType=TIMESTAMP}) + </insert> + + <delete id="deleteParams" parameterType="Integer"> + delete from active_rule_parameters where rules_parameter_id=#{id} + </delete> + + <sql id="paramColumns"> + p.id as "id", p.rule_id as "ruleId", p.name as "name", p.param_type as "type", p.default_value as "defaultValue", + p.description as "description" + </sql> + + <select id="selectAllParams" resultType="RuleParam"> + select + <include refid="paramColumns"/> + from rules_parameters p + </select> + + <select id="selectParamsByRuleIds" resultType="RuleParam"> + SELECT + <include refid="paramColumns"/> + FROM rules_parameters p + <where> + AND (<foreach item="id" index="index" collection="ruleIds" open="(" separator=" or " close=")"> + p.rule_id=#{id}</foreach>) + </where> + </select> + + <select id="selectParamsByRuleKey" resultType="RuleParam" parameterType="org.sonar.api.rule.RuleKey"> + SELECT + <include refid="paramColumns"/> + FROM rules_parameters p, rules r + WHERE p.rule_id=r.id + AND r.plugin_name=#{repository} AND r.plugin_rule_key=#{rule} + </select> + + <select id="selectParamByRuleAndKey" resultType="RuleParam"> + SELECT + <include refid="paramColumns"/> + FROM rules_parameters p + WHERE p.rule_id=#{ruleId} AND p.name=#{key} + </select> + + <delete id="deleteParameter" parameterType="Integer"> + delete from rules_parameters where id=#{id} + </delete> + + <insert id="insertParameter" parameterType="RuleParam" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO rules_parameters (rule_id, name, param_type, default_value, description) + VALUES (#{ruleId}, #{name}, #{type}, #{defaultValue}, #{description}) + </insert> + + <update id="updateParameter" parameterType="RuleParam"> + UPDATE rules_parameters SET + param_type=#{type}, + default_value=#{defaultValue}, + description=#{description} + WHERE id=#{id} + </update> +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/semaphore/SemaphoreMapper.xml b/sonar-db/src/main/resources/org/sonar/db/semaphore/SemaphoreMapper.xml new file mode 100644 index 00000000000..5d1c1028478 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/semaphore/SemaphoreMapper.xml @@ -0,0 +1,36 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.semaphore.SemaphoreMapper"> + + <insert id="initialize" parameterType="map" useGeneratedKeys="false"> + INSERT INTO semaphores (name, checksum, created_at, updated_at, locked_at) + VALUES (#{name}, #{checksum}, #{createdAt}, #{updatedAt}, #{lockedAt}) + </insert> + + <update id="acquire" parameterType="map"> + update semaphores + set updated_at = #{now}, locked_at = #{now} + where name=#{name} + <if test="updatedBefore != null"> + AND updated_at < #{updatedBefore} + </if> + </update> + + <delete id="release" parameterType="String"> + delete from semaphores where name=#{id} + </delete> + + <select id="selectSemaphore" parameterType="String" resultType="Semaphore"> + select s.id, s.name as name, s.locked_at as lockedAt, s.created_at as createdAt, s.updated_at as updatedAt + from semaphores s where s.name=#{name} + </select> + + <update id="update" parameterType="map"> + update semaphores + set updated_at = #{now} + where name=#{name} + </update> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/source/FileSourceMapper.xml b/sonar-db/src/main/resources/org/sonar/db/source/FileSourceMapper.xml new file mode 100644 index 00000000000..dd6a83ed5ff --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/source/FileSourceMapper.xml @@ -0,0 +1,47 @@ +<?xml version="1.0" encoding="UTF-8" ?> + +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.source.FileSourceMapper"> + + <select id="select" parameterType="map" resultType="org.sonar.db.source.FileSourceDto"> + SELECT id, project_uuid as projectUuid, file_uuid as fileUuid, created_at as createdAt, updated_at as updatedAt, + binary_data as binaryData, line_hashes as lineHashes, data_hash as dataHash, src_hash as srcHash, data_type as + dataType + FROM file_sources + WHERE file_uuid = #{fileUuid} and data_type = #{dataType} + </select> + + <select id="selectHashesForProject" parameterType="map" resultType="org.sonar.db.source.FileSourceDto"> + SELECT id, file_uuid as fileUuid, data_hash as dataHash, src_hash as srcHash, updated_at as updatedAt + FROM file_sources + WHERE project_uuid = #{projectUuid} and data_type=#{dataType} + </select> + + <insert id="insert" parameterType="org.sonar.db.source.FileSourceDto" useGeneratedKeys="false"> + INSERT INTO file_sources (project_uuid, file_uuid, created_at, updated_at, binary_data, line_hashes, data_hash, + src_hash, data_type) + VALUES (#{projectUuid,jdbcType=VARCHAR}, #{fileUuid,jdbcType=VARCHAR}, #{createdAt,jdbcType=BIGINT}, + #{updatedAt,jdbcType=BIGINT}, #{binaryData,jdbcType=BLOB}, #{lineHashes,jdbcType=CLOB}, + #{dataHash,jdbcType=VARCHAR}, #{srcHash,jdbcType=VARCHAR},#{dataType,jdbcType=VARCHAR}) + </insert> + + <update id="update" parameterType="org.sonar.db.source.FileSourceDto" useGeneratedKeys="false"> + UPDATE file_sources SET + updated_at = #{updatedAt,jdbcType=BIGINT}, + binary_data = #{binaryData,jdbcType=BLOB}, + line_hashes = #{lineHashes,jdbcType=CLOB}, + data_hash = #{dataHash,jdbcType=VARCHAR}, + src_hash = #{srcHash,jdbcType=VARCHAR} + WHERE id = #{id} + </update> + + <update id="updateDateWhenUpdatedDateIsZero" parameterType="org.sonar.db.source.FileSourceDto" + useGeneratedKeys="false"> + UPDATE file_sources SET + updated_at = #{date,jdbcType=BIGINT} + WHERE project_uuid = #{projectUuid} + AND updated_at = 0 + </update> +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/source/SnapshotDataMapper.xml b/sonar-db/src/main/resources/org/sonar/db/source/SnapshotDataMapper.xml new file mode 100644 index 00000000000..022381e4df8 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/source/SnapshotDataMapper.xml @@ -0,0 +1,39 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.source.SnapshotDataMapper"> + + <sql id="snapshotDataColumns"> + snapshot_id AS "snapshotId", + snapshot_data AS "data", + data_type AS "dataType" + </sql> + + <select id="selectSnapshotData" parameterType="map" resultType="SnapshotData"> + SELECT + <include refid="snapshotDataColumns"/> + FROM snapshot_data + WHERE snapshot_id = #{sid} + AND data_type IN + <foreach item="dataType" index="index" collection="dataTypes" open="(" separator="," close=")">#{dataType}</foreach> + </select> + + <select id="selectSnapshotDataByComponentKey" parameterType="map" resultType="SnapshotData"> + SELECT + <include refid="snapshotDataColumns"/> + FROM snapshot_data sd + INNER JOIN projects p ON p.id=sd.resource_id and p.enabled=${_true} + WHERE p.kee = #{componentKey} + AND data_type IN + <foreach item="dataType" index="index" collection="dataTypes" open="(" separator="," close=")">#{dataType}</foreach> + </select> + + <insert id="insert" parameterType="SnapshotData" useGeneratedKeys="false"> + insert into snapshot_data + (resource_id, snapshot_id, snapshot_data, data_type, created_at, updated_at) + values ( + #{resourceId,jdbcType=INTEGER}, #{snapshotId,jdbcType=INTEGER}, #{data,jdbcType=CLOB}, #{dataType,jdbcType=VARCHAR}, + CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) + </insert> +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/user/AuthorMapper.xml b/sonar-db/src/main/resources/org/sonar/db/user/AuthorMapper.xml new file mode 100644 index 00000000000..440d2d74701 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/user/AuthorMapper.xml @@ -0,0 +1,33 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.user.AuthorMapper"> + + <select id="selectByLogin" parameterType="string" resultType="Author"> + SELECT id, person_id AS personId, login, created_at AS createdAt, updated_at AS updatedAt + FROM authors WHERE login=#{id} + </select> + + <select id="countDeveloperLogins" parameterType="long" resultType="int"> + SELECT count(id) + FROM authors WHERE person_id=#{id} + </select> + + <insert id="insert" parameterType="Author" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO authors (person_id, login, created_at, updated_at) + VALUES (#{personId}, #{login}, + #{createdAt}, #{updatedAt}) + </insert> + + <select id="selectScmAccountsByDeveloperUuids" parameterType="String" resultType="String"> + SELECT a.login + FROM authors a + INNER JOIN projects p ON p.id=a.person_id + <where> + and p.uuid in + <foreach collection="uuids" open="(" close=")" item="uuid" separator=","> + #{uuid} + </foreach> + </where> + </select> +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/user/AuthorizationMapper.xml b/sonar-db/src/main/resources/org/sonar/db/user/AuthorizationMapper.xml new file mode 100644 index 00000000000..f33a7ae0640 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/user/AuthorizationMapper.xml @@ -0,0 +1,210 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.user.AuthorizationMapper"> + + <select id="keepAuthorizedComponentKeysForUser" parameterType="map" resultType="string"> + SELECT p.kee + FROM group_roles gr, projects p + WHERE + gr.role=#{role} + and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where gu.user_id=#{userId})) + and (gr.resource_id = p.root_id or gr.resource_id = p.id) and + <foreach collection="componentKeys" open="(" close=")" item="element" index="index" separator=" or "> + p.kee=#{element} + </foreach> + UNION + SELECT p.kee + FROM user_roles ur + INNER JOIN projects p on p.id = ur.resource_id + WHERE + ur.role=#{role} + and ur.user_id=#{userId} and + <foreach collection="componentKeys" open="(" close=")" item="element" index="index" separator=" or "> + p.kee=#{element} + </foreach> + </select> + + <select id="keepAuthorizedComponentKeysForAnonymous" parameterType="map" resultType="string"> + SELECT p.kee + FROM group_roles gr, projects p + WHERE + gr.role=#{role} + and gr.group_id is null + and (gr.resource_id = p.root_id or gr.resource_id = p.id) and + <foreach collection="componentKeys" open="(" close=")" item="element" index="index" separator=" or "> + p.kee=#{element} + </foreach> + </select> + + <select id="keepAuthorizedProjectIdsForUser" parameterType="map" resultType="long"> + SELECT gr.resource_id + FROM group_roles gr + WHERE + gr.role=#{role} + and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where gu.user_id=#{userId})) + and + <foreach collection="componentIds" open="(" close=")" item="element" index="index" separator=" or "> + gr.resource_id=#{element} + </foreach> + UNION + SELECT p.id + FROM user_roles ur + INNER JOIN projects p on p.id = ur.resource_id + WHERE + ur.role=#{role} + and ur.user_id=#{userId} and + <foreach collection="componentIds" open="(" close=")" item="element" index="index" separator=" or "> + p.id=#{element} + </foreach> + </select> + + <select id="keepAuthorizedProjectIdsForAnonymous" parameterType="map" resultType="long"> + SELECT gr.resource_id + FROM group_roles gr + WHERE + gr.role=#{role} + and gr.group_id is null + and + <foreach collection="componentIds" open="(" close=")" item="element" index="index" separator=" or "> + gr.resource_id=#{element} + </foreach> + </select> + + <select id="selectAuthorizedRootProjectsKeys" parameterType="map" resultType="string"> + <include refid="selectAuthorizedRootProjectsKeysQuery"/> + </select> + + <sql id="selectAuthorizedRootProjectsKeysQuery"> + <choose> + <when test="userId != null"> + SELECT p.kee as root_project_kee + FROM group_roles gr + INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL + <where> + and gr.role=#{role} + and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where + gu.user_id=#{userId})) + </where> + UNION + SELECT p.kee as root_project_kee + FROM user_roles ur + INNER JOIN projects p on p.id = ur.resource_id AND p.module_uuid IS NULL + <where> + and ur.role=#{role} + and ur.user_id = #{userId} + </where> + </when> + <otherwise> + SELECT p.kee as root_project_kee + FROM group_roles gr + INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL + <where> + and gr.role=#{role} + and gr.group_id is null + </where> + </otherwise> + </choose> + </sql> + + <select id="selectAuthorizedRootProjectsUuids" parameterType="map" resultType="string"> + <choose> + <when test="userId != null"> + SELECT p.uuid as root_project_uuid + FROM group_roles gr + INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL + <where> + and gr.role=#{role} + and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where + gu.user_id=#{userId})) + </where> + UNION + SELECT p.uuid as root_project_uuid + FROM user_roles ur + INNER JOIN projects p on p.id = ur.resource_id AND p.module_uuid IS NULL + <where> + and ur.role=#{role} + and ur.user_id = #{userId} + </where> + </when> + <otherwise> + SELECT p.uuid as root_project_uuid + FROM group_roles gr + INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL + <where> + and gr.role=#{role} + and gr.group_id is null + </where> + </otherwise> + </choose> + </select> + + <!-- same as selectAuthorizedRootProjectsKeysQuery but returns ids instead of keys --> + <sql id="selectAuthorizedRootProjectIdsQuery"> + <choose> + <when test="userId != null"> + SELECT p.id as root_project_id + FROM group_roles gr + INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL + <where> + and gr.role=#{role} + and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where + gu.user_id=#{userId})) + </where> + UNION + SELECT p.id as root_project_id + FROM user_roles ur + INNER JOIN projects p on p.id = ur.resource_id AND p.module_uuid IS NULL + <where> + and ur.role=#{role} + and ur.user_id = #{userId} + </where> + </when> + <otherwise> + SELECT p.id as root_project_id + FROM group_roles gr + INNER JOIN projects p on p.id = gr.resource_id AND p.module_uuid IS NULL + <where> + and gr.role=#{role} + and gr.group_id is null + </where> + </otherwise> + </choose> + </sql> + + <select id="selectGlobalPermissions" parameterType="map" resultType="String"> + <choose> + <when test="userLogin != null"> + SELECT gr.role + FROM group_roles gr + INNER JOIN groups_users gu on gu.group_id=gr.group_id + INNER JOIN users u on u.id=gu.user_id + <where> + and u.login=#{userLogin} + and gr.resource_id is null + </where> + UNION + SELECT gr.role + FROM group_roles gr + WHERE gr.group_id IS NULL AND gr.resource_id IS NULL + UNION + SELECT ur.role + FROM user_roles ur + INNER JOIN users u on u.id=ur.user_id + <where> + and u.login=#{userLogin} + and ur.resource_id is null + </where> + </when> + <otherwise> + SELECT gr.role + FROM group_roles gr + <where> + and gr.resource_id is null + and gr.group_id is null + </where> + </otherwise> + </choose> + </select> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/user/GroupMapper.xml b/sonar-db/src/main/resources/org/sonar/db/user/GroupMapper.xml new file mode 100644 index 00000000000..0991bcffc0b --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/user/GroupMapper.xml @@ -0,0 +1,77 @@ +<?xml version="1.0" encoding="UTF-8" ?> + +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.user.GroupMapper"> + + <sql id="groupColumns"> + g.id as id, + g.name as name, + g.description as description, + g.created_at as "createdAt", + g.updated_at as "updatedAt" + </sql> + + <select id="selectByKey" parameterType="string" resultType="Group"> + SELECT + <include refid="groupColumns"/> + FROM groups g + <where> + g.name=#{id} + </where> + </select> + + <select id="selectById" parameterType="long" resultType="Group"> + SELECT + <include refid="groupColumns"/> + FROM groups g + <where> + g.id=#{id} + </where> + </select> + + <delete id="deleteById" parameterType="long"> + DELETE FROM groups + <where> + id=#{id} + </where> + </delete> + + <select id="selectByUserLogin" parameterType="string" resultType="Group"> + SELECT + <include refid="groupColumns"/> + FROM groups g + INNER JOIN groups_users gu on gu.group_id=g.id + INNER JOIN users u on u.id=gu.user_id + <where> + u.login=#{login} + </where> + </select> + + <insert id="insert" parameterType="Group" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO groups (name, description, created_at, updated_at) + VALUES (#{name}, #{description}, #{createdAt}, #{updatedAt}) + </insert> + + <update id="update" parameterType="Group"> + UPDATE groups SET + name=#{name}, + description=#{description}, + updated_at=#{updatedAt} + WHERE id=#{id} + </update> + + <select id="selectByQuery" parameterType="map" resultType="Group"> + SELECT + <include refid="groupColumns"/> + FROM groups g + WHERE UPPER(g.name) LIKE #{query} + ORDER BY UPPER(g.name) + </select> + + <select id="countByQuery" parameterType="map" resultType="int"> + SELECT count(g.id) + FROM groups g + WHERE UPPER(g.name) LIKE #{query} + </select> +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/user/GroupMembershipMapper.xml b/sonar-db/src/main/resources/org/sonar/db/user/GroupMembershipMapper.xml new file mode 100644 index 00000000000..36591ceea05 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/user/GroupMembershipMapper.xml @@ -0,0 +1,92 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.user.GroupMembershipMapper"> + + <sql id="commonClauses"> + FROM groups g + LEFT JOIN groups_users gu ON gu.group_id=g.id AND gu.user_id=#{userId} + <where> + <choose> + <when test="query.membership() == 'IN'"> + AND gu.user_id IS NOT NULL + </when> + <when test="query.membership() == 'OUT'"> + AND gu.user_id IS NULL + </when> + </choose> + <if test="query.groupSearch() != null"> + AND (UPPER(g.name) LIKE #{query.groupSearchSql} ESCAPE '/') + </if> + </where> + </sql> + + <select id="selectGroups" parameterType="map" resultType="GroupMembership"> + SELECT g.id as id, g.name as name, g.description as description, gu.user_id as userId + <include refid="commonClauses"/> + ORDER BY g.name + </select> + + <select id="countGroups" parameterType="map" resultType="int"> + SELECT COUNT(g.id) + <include refid="commonClauses"/> + </select> + + <select id="countUsersByGroup" parameterType="long" resultType="org.sonar.db.user.GroupUserCount"> + SELECT g.name as groupName, count(gu.user_id) as userCount + FROM groups g + LEFT JOIN groups_users gu ON gu.group_id=g.id + <where> + g.id in + <foreach collection="groupIds" open="(" close=")" item="id" separator=","> + #{id} + </foreach> + </where> + GROUP BY g.name + </select> + + <select id="selectGroupsByLogins" parameterType="string" resultType="org.sonar.db.user.LoginGroup"> + SELECT u.login as login, g.name as groupName + FROM users u + LEFT JOIN groups_users gu ON gu.user_id=u.id + INNER JOIN groups g ON gu.group_id=g.id + <where> + u.login in + <foreach collection="logins" open="(" close=")" item="login" separator=","> + #{login} + </foreach> + </where> + </select> + + <sql id="userCommonClauses"> + FROM users u + LEFT JOIN groups_users gu ON gu.user_id=u.id AND gu.group_id=#{groupId} + <where> + <choose> + <when test="query.membership() == 'IN'"> + AND gu.group_id IS NOT NULL + </when> + <when test="query.membership() == 'OUT'"> + AND gu.group_id IS NULL + </when> + </choose> + <if test="query.memberSearch() != null"> + AND ((UPPER(u.login) LIKE #{query.memberSearchSql} ESCAPE '/') OR (UPPER(u.name) LIKE #{query.memberSearchSql} + ESCAPE '/')) + </if> + AND u.active=${_true} + </where> + </sql> + + <select id="selectMembers" parameterType="map" resultType="org.sonar.db.user.UserMembershipDto"> + SELECT u.id as id, u.login as login, u.name as name, gu.group_id as groupId + <include refid="userCommonClauses"/> + ORDER BY u.name ASC + </select> + + <select id="countMembers" parameterType="map" resultType="int"> + SELECT COUNT(u.id) + <include refid="userCommonClauses"/> + </select> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/user/RoleMapper.xml b/sonar-db/src/main/resources/org/sonar/db/user/RoleMapper.xml new file mode 100644 index 00000000000..41303206013 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/user/RoleMapper.xml @@ -0,0 +1,118 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.user.RoleMapper"> + + <select id="selectUserPermissions" parameterType="map" resultType="String"> + SELECT ur.role + FROM user_roles ur + INNER JOIN users u ON u.id=ur.user_id AND u.active=${_true} + <where> + AND u.login = #{userLogin} + <choose> + <when test="resourceId != null"> + AND resource_id=#{resourceId} + </when> + <otherwise> + AND resource_id IS NULL + </otherwise> + </choose> + </where> + </select> + + <select id="selectGroupPermissions" parameterType="map" resultType="String"> + SELECT gr.role + FROM group_roles gr + <if test="isAnyOneGroup != true"> + INNER JOIN groups g ON g.id = gr.group_id + </if> + <where> + <choose> + <when test="isAnyOneGroup != true"> + AND g.name = #{groupName} + </when> + <otherwise> + AND gr.group_id IS NULL + </otherwise> + </choose> + <choose> + <when test="resourceId != null"> + AND resource_id=#{resourceId} + </when> + <otherwise> + AND resource_id IS NULL + </otherwise> + </choose> + </where> + </select> + + <insert id="insertGroupRole" parameterType="GroupRole" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO group_roles (group_id, resource_id, role) + VALUES (#{groupId}, #{resourceId}, #{role}) + </insert> + + <insert id="insertUserRole" parameterType="UserRole" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO user_roles (user_id, resource_id, role) + VALUES (#{userId}, #{resourceId}, #{role}) + </insert> + + <delete id="deleteGroupRole" parameterType="map"> + DELETE FROM group_roles + WHERE role=#{role} + AND + <choose> + <when test="resourceId != null"> + resource_id=#{resourceId} + </when> + <otherwise> + resource_id IS NULL + </otherwise> + </choose> + AND + <choose> + <when test="groupId != null"> + group_id=#{groupId} + </when> + <otherwise> + group_id IS NULL + </otherwise> + </choose> + </delete> + + <delete id="deleteUserRole" parameterType="map"> + DELETE FROM user_roles + WHERE user_id=#{userId} + AND role=#{role} + AND + <choose> + <when test="resourceId != null"> + resource_id=#{resourceId} + </when> + <otherwise> + resource_id IS NULL + </otherwise> + </choose> + </delete> + + <delete id="deleteGroupRolesByResourceId" parameterType="long"> + delete from group_roles where resource_id=#{id} + </delete> + + <delete id="deleteUserRolesByResourceId" parameterType="long"> + delete from user_roles where resource_id=#{id} + </delete> + + <select id="countResourceUserRoles" parameterType="long" resultType="int"> + SELECT count(id) + FROM user_roles WHERE resource_id=#{id} + </select> + + <select id="countResourceGroupRoles" parameterType="long" resultType="int"> + SELECT count(id) + FROM group_roles WHERE resource_id=#{id} + </select> + + <delete id="deleteGroupRolesByGroupId" parameterType="long"> + delete from group_roles where group_id=#{id} + </delete> +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/user/UserGroupMapper.xml b/sonar-db/src/main/resources/org/sonar/db/user/UserGroupMapper.xml new file mode 100644 index 00000000000..d2177b452c0 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/user/UserGroupMapper.xml @@ -0,0 +1,26 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.user.UserGroupMapper"> + + <insert id="insert" parameterType="UserGroup" useGeneratedKeys="false"> + INSERT INTO groups_users (user_id, group_id) + VALUES (#{userId}, #{groupId}) + </insert> + + <delete id="delete" parameterType="UserGroup"> + DELETE FROM groups_users + <where> + AND user_id = #{userId} + AND group_id = #{groupId} + </where> + </delete> + + <delete id="deleteMembersByGroup" parameterType="long"> + DELETE FROM groups_users + <where> + AND group_id = #{groupId} + </where> + </delete> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/user/UserMapper.xml b/sonar-db/src/main/resources/org/sonar/db/user/UserMapper.xml new file mode 100644 index 00000000000..d46d44f2eaa --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/user/UserMapper.xml @@ -0,0 +1,141 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.user.UserMapper"> + + <sql id="userColumns"> + u.id as id, + u.login as login, + u.name as name, + u.email as email, + u.active as "active", + u.scm_accounts as "scmAccounts", + u.salt as "salt", + u.crypted_password as "cryptedPassword", + u.created_at as "createdAt", + u.updated_at as "updatedAt" + </sql> + + <select id="selectByLogin" parameterType="String" resultType="User"> + SELECT + <include refid="userColumns"/> + FROM users u + WHERE u.login=#{login} + </select> + + <select id="selectNullableByScmAccountOrLoginOrEmail" parameterType="map" resultType="User"> + SELECT + <include refid="userColumns"/> + FROM users u + WHERE + u.login=#{scmAccount} + OR u.email=#{scmAccount} + OR u.scm_accounts like #{likeScmAccount} + </select> + + <select id="selectUser" parameterType="long" resultType="User"> + SELECT + <include refid="userColumns"/> + FROM users u + WHERE u.id=#{id} + </select> + + <select id="selectUserByLogin" parameterType="string" resultType="User"> + SELECT + <include refid="userColumns"/> + FROM users u + WHERE u.login=#{id} AND u.active=${_true} + </select> + + <select id="selectUsersByLogins" parameterType="map" resultType="User"> + SELECT + <include refid="userColumns"/> + FROM users u WHERE + (<foreach item="login" index="index" collection="logins" open="(" separator=" or " close=")"> + u.login=#{login} + </foreach>) + </select> + + <select id="selectUsers" parameterType="map" resultType="User"> + SELECT + <include refid="userColumns"/> + FROM users u + <where> + <if test="logins != null and logins.size() > 0"> + u.login IN + <foreach item="login" index="index" collection="logins" open="(" separator="," close=")"> + #{login} + </foreach> + </if> + <if test="includeDeactivated==false"> + AND u.active=${_true} + </if> + <if test="searchText != null"> + AND (u.login LIKE #{searchTextSql} ESCAPE '/' OR u.name LIKE #{searchTextSql} ESCAPE '/') + </if> + </where> + ORDER BY u.name + </select> + + <select id="selectGroupByName" parameterType="string" resultType="Group"> + SELECT id, name, description, created_at AS "createdAt", updated_at AS "updatedAt" + FROM groups WHERE name=#{id} + </select> + + <delete id="removeUserFromGroups" parameterType="long"> + DELETE FROM groups_users WHERE user_id=#{id} + </delete> + + <delete id="deleteUserRoles" parameterType="long"> + DELETE FROM user_roles WHERE user_id=#{id} + </delete> + + <delete id="deleteUserProperties" parameterType="long"> + DELETE FROM properties WHERE user_id=#{id} + </delete> + + <delete id="deleteUnsharedUserDashboards" parameterType="long"> + DELETE FROM dashboards WHERE user_id=#{id} and shared <> ${_true} + </delete> + + <delete id="deleteUserActiveDashboards" parameterType="long"> + DELETE FROM active_dashboards WHERE user_id=#{id} + </delete> + + <delete id="deleteUnsharedUserMeasureFilters" parameterType="long"> + DELETE FROM measure_filters WHERE user_id=#{id} and shared <> ${_true} + </delete> + + <delete id="deleteUserMeasureFilterFavourites" parameterType="long"> + DELETE FROM measure_filter_favourites WHERE user_id=#{id} + </delete> + + <delete id="deleteUnsharedUserIssueFilters" parameterType="String"> + DELETE FROM issue_filters WHERE user_login=#{id} and shared <> ${_true} + </delete> + + <delete id="deleteUserIssueFilterFavourites" parameterType="String"> + DELETE FROM issue_filter_favourites WHERE user_login=#{id} + </delete> + + <update id="deactivateUser" parameterType="long"> + UPDATE users SET active=${_false}, updated_at=#{now,jdbcType=BIGINT} WHERE id=#{id} + </update> + + <insert id="insert" parameterType="User" keyColumn="id" useGeneratedKeys="true" keyProperty="id"> + INSERT INTO users (login, name, email, active, scm_accounts, salt, crypted_password, created_at, updated_at) + VALUES (#{login,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{active,jdbcType=BOOLEAN}, + #{scmAccounts,jdbcType=VARCHAR}, + #{salt,jdbcType=VARCHAR}, #{cryptedPassword,jdbcType=VARCHAR}, #{createdAt,jdbcType=BIGINT}, + #{updatedAt,jdbcType=BIGINT}) + </insert> + + <insert id="update" parameterType="User" useGeneratedKeys="false"> + UPDATE users set name=#{name,jdbcType=VARCHAR}, email=#{email,jdbcType=VARCHAR}, active=#{active,jdbcType=BOOLEAN}, + scm_accounts=#{scmAccounts,jdbcType=VARCHAR}, + salt=#{salt,jdbcType=VARCHAR}, crypted_password=#{cryptedPassword,jdbcType=BIGINT}, + updated_at=#{updatedAt,jdbcType=BIGINT} + WHERE login = #{login} + </insert> + +</mapper> diff --git a/sonar-db/src/main/resources/org/sonar/db/version/SchemaMigrationMapper.xml b/sonar-db/src/main/resources/org/sonar/db/version/SchemaMigrationMapper.xml new file mode 100644 index 00000000000..cde37378a00 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/version/SchemaMigrationMapper.xml @@ -0,0 +1,14 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.version.SchemaMigrationMapper"> + + <select id="selectVersions" resultType="int"> + select version from schema_migrations + </select> + + <insert id="insert" parameterType="string" useGeneratedKeys="false"> + insert into schema_migrations(version) values (#{version,jdbcType=VARCHAR}) + </insert> +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql new file mode 100644 index 00000000000..706947ab524 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql @@ -0,0 +1,348 @@ +-- All the rows inserted during Rails migrations. Rows inserted during server startup tasks (Java) are excluded : rules, profiles, metrics, ... + +INSERT INTO GROUPS(ID, NAME, DESCRIPTION, CREATED_AT, UPDATED_AT) VALUES (1, 'sonar-administrators', 'System administrators', '2011-09-26 22:27:51.0', '2011-09-26 22:27:51.0'); +INSERT INTO GROUPS(ID, NAME, DESCRIPTION, CREATED_AT, UPDATED_AT) VALUES (2, 'sonar-users', 'Any new users created will automatically join this group', '2011-09-26 22:27:51.0', '2011-09-26 22:27:51.0'); +ALTER TABLE GROUPS ALTER COLUMN ID RESTART WITH 3; + +INSERT INTO GROUP_ROLES(ID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (1, 1, null, 'admin'); +INSERT INTO GROUP_ROLES(ID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (2, 1, null, 'profileadmin'); +INSERT INTO GROUP_ROLES(ID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (3, 1, null, 'shareDashboard'); +INSERT INTO GROUP_ROLES(ID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (4, null, null, 'scan'); +INSERT INTO GROUP_ROLES(ID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (5, null, null, 'dryRunScan'); +INSERT INTO GROUP_ROLES(ID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (6, 1, null, 'provisioning'); +ALTER TABLE GROUP_ROLES ALTER COLUMN ID RESTART WITH 7; + +INSERT INTO GROUPS_USERS(USER_ID, GROUP_ID) VALUES (1, 1); +INSERT INTO GROUPS_USERS(USER_ID, GROUP_ID) VALUES (1, 2); + + +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('2'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('10'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('11'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('14'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('35'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('48'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('49'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('53'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('54'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('59'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('61'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('62'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('79'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('80'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('86'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('87'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('88'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('93'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('95'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('111'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('115'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('118'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('119'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('120'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('131'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('132'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('133'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('134'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('135'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('136'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('137'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('138'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('139'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('140'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('141'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('142'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('151'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('160'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('162'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('163'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('165'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('166'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('167'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('168'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('169'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('170'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('180'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('190'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('191'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('200'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('201'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('202'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('203'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('210'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('211'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('212'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('213'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('214'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('215'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('216'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('217'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('220'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('221'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('222'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('230'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('231'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('232'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('233'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('234'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('235'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('236'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('237'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('238'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('239'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('240'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('241'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('250'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('251'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('252'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('254'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('255'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('256'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('257'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('258'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('259'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('260'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('261'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('262'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('263'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('280'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('281'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('282'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('283'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('284'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('285'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('286'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('287'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('300'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('301'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('302'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('303'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('304'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('305'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('306'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('320'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('321'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('330'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('331'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('332'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('333'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('334'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('335'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('350'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('351'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('352'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('353'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('354'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('355'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('356'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('358'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('359'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('360'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('361'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('362'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('363'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('370'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('379'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('380'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('381'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('382'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('383'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('387'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('388'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('391'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('392'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('394'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('397'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('398'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('399'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('400'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('401'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('402'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('403'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('404'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('405'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('406'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('410'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('411'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('412'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('413'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('414'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('415'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('416'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('417'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('418'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('419'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('430'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('431'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('432'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('433'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('434'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('440'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('441'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('442'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('443'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('444'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('460'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('461'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('462'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('463'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('464'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('465'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('466'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('467'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('480'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('481'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('482'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('483'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('484'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('485'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('486'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('488'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('489'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('490'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('492'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('493'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('494'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('495'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('496'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('497'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('498'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('510'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('511'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('512'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('513'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('514'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('515'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('516'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('517'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('518'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('519'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('520'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('521'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('522'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('523'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('524'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('525'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('526'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('530'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('531'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('532'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('533'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('534'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('535'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('536'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('537'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('539'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('540'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('541'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('542'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('544'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('545'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('546'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('547'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('548'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('549'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('551'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('552'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('553'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('554'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('555'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('556'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('580'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('581'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('582'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('583'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('584'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('600'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('601'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('603'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('604'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('605'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('702'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('703'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('704'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('705'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('706'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('707'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('708'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('709'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('710'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('711'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('712'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('713'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('714'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('715'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('716'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('717'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('718'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('719'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('720'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('721'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('750'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('752'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('753'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('754'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('755'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('756'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('757'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('758'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('759'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('760'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('761'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('762'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('763'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('764'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('765'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('766'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('768'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('769'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('770'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('771'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('772'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('773'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('775'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('776'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('777'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('778'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('779'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('780'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('781'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('782'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('783'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('784'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('785'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('786'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('787'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('788'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('789'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('790'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('791'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('792'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('793'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('794'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('795'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('796'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('900'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('901'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('902'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('903'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('904'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('905'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('906'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('907'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('908'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('912'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('913'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('915'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('916'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('917'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('918'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('919'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('920'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('921'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('922'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('923'); + +INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, CRYPTED_PASSWORD, SALT, CREATED_AT, UPDATED_AT, REMEMBER_TOKEN, REMEMBER_TOKEN_EXPIRES_AT) VALUES (1, 'admin', 'Administrator', '', 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', '1418215735482', '1418215735482', null, null); +ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2; diff --git a/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl b/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl new file mode 100644 index 00000000000..d0ad5236787 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl @@ -0,0 +1,686 @@ +CREATE TABLE "GROUPS_USERS" ( + "USER_ID" INTEGER, + "GROUP_ID" INTEGER +); + +CREATE TABLE "CHARACTERISTICS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(100), + "NAME" VARCHAR(100), + "PARENT_ID" INTEGER, + "ROOT_ID" INTEGER, + "RULE_ID" INTEGER, + "FUNCTION_KEY" VARCHAR(100), + "FACTOR_VALUE" DOUBLE, + "FACTOR_UNIT" VARCHAR(100), + "OFFSET_VALUE" DOUBLE, + "OFFSET_UNIT" VARCHAR(100), + "CHARACTERISTIC_ORDER" INTEGER, + "ENABLED" BOOLEAN, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "RULES_PARAMETERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "RULE_ID" INTEGER NOT NULL, + "NAME" VARCHAR(128) NOT NULL, + "PARAM_TYPE" VARCHAR(512) NOT NULL, + "DEFAULT_VALUE" VARCHAR(4000), + "DESCRIPTION" VARCHAR(4000) +); + +CREATE TABLE "RULES_PROFILES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(100) NOT NULL, + "LANGUAGE" VARCHAR(20), + "KEE" VARCHAR(255) NOT NULL, + "PARENT_KEE" VARCHAR(255), + "RULES_UPDATED_AT" VARCHAR(100), + "IS_DEFAULT" BOOLEAN NOT NULL DEFAULT FALSE, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "PROJECT_QPROFILES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "PROFILE_KEY" VARCHAR(255) NOT NULL +); + +CREATE TABLE "WIDGETS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "DASHBOARD_ID" INTEGER NOT NULL, + "WIDGET_KEY" VARCHAR(256) NOT NULL, + "NAME" VARCHAR(256), + "DESCRIPTION" VARCHAR(1000), + "COLUMN_INDEX" INTEGER, + "ROW_INDEX" INTEGER, + "CONFIGURED" BOOLEAN, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP, + "RESOURCE_ID" INTEGER +); + +CREATE TABLE "GROUPS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(500), + "DESCRIPTION" VARCHAR(200), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "SNAPSHOTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "CREATED_AT" BIGINT, + "BUILD_DATE" BIGINT, + "PROJECT_ID" INTEGER NOT NULL, + "PARENT_SNAPSHOT_ID" INTEGER, + "STATUS" VARCHAR(4) NOT NULL DEFAULT 'U', + "PURGE_STATUS" INTEGER, + "ISLAST" BOOLEAN NOT NULL DEFAULT FALSE, + "SCOPE" VARCHAR(3), + "QUALIFIER" VARCHAR(10), + "ROOT_SNAPSHOT_ID" INTEGER, + "VERSION" VARCHAR(500), + "PATH" VARCHAR(500), + "DEPTH" INTEGER, + "ROOT_PROJECT_ID" INTEGER, + "PERIOD1_MODE" VARCHAR(100), + "PERIOD1_PARAM" VARCHAR(100), + "PERIOD1_DATE" BIGINT, + "PERIOD2_MODE" VARCHAR(100), + "PERIOD2_PARAM" VARCHAR(100), + "PERIOD2_DATE" BIGINT, + "PERIOD3_MODE" VARCHAR(100), + "PERIOD3_PARAM" VARCHAR(100), + "PERIOD3_DATE" BIGINT, + "PERIOD4_MODE" VARCHAR(100), + "PERIOD4_PARAM" VARCHAR(100), + "PERIOD4_DATE" BIGINT, + "PERIOD5_MODE" VARCHAR(100), + "PERIOD5_PARAM" VARCHAR(100), + "PERIOD5_DATE" BIGINT +); + +CREATE TABLE "SCHEMA_MIGRATIONS" ( +"VERSION" VARCHAR(256) NOT NULL +); + +CREATE TABLE "GROUP_ROLES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "GROUP_ID" INTEGER, + "RESOURCE_ID" INTEGER, + "ROLE" VARCHAR(64) NOT NULL +); + +CREATE TABLE "RULES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PLUGIN_RULE_KEY" VARCHAR(200) NOT NULL, + "PLUGIN_NAME" VARCHAR(255) NOT NULL, + "DESCRIPTION" VARCHAR(16777215), + "DESCRIPTION_FORMAT" VARCHAR(20), + "PRIORITY" INTEGER, + "IS_TEMPLATE" BOOLEAN DEFAULT FALSE, + "TEMPLATE_ID" INTEGER, + "PLUGIN_CONFIG_KEY" VARCHAR(500), + "NAME" VARCHAR(200), + "STATUS" VARCHAR(40), + "LANGUAGE" VARCHAR(20), + "NOTE_DATA" CLOB(2147483647), + "NOTE_USER_LOGIN" VARCHAR(255), + "NOTE_CREATED_AT" TIMESTAMP, + "NOTE_UPDATED_AT" TIMESTAMP, + "CHARACTERISTIC_ID" INTEGER, + "DEFAULT_CHARACTERISTIC_ID" INTEGER, + "REMEDIATION_FUNCTION" VARCHAR(20), + "DEFAULT_REMEDIATION_FUNCTION" VARCHAR(20), + "REMEDIATION_COEFF" VARCHAR(20), + "DEFAULT_REMEDIATION_COEFF" VARCHAR(20), + "REMEDIATION_OFFSET" VARCHAR(20), + "DEFAULT_REMEDIATION_OFFSET" VARCHAR(20), + "EFFORT_TO_FIX_DESCRIPTION" VARCHAR(4000), + "TAGS" VARCHAR(4000), + "SYSTEM_TAGS" VARCHAR(4000), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + + +CREATE TABLE "WIDGET_PROPERTIES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "WIDGET_ID" INTEGER NOT NULL, + "KEE" VARCHAR(100), + "TEXT_VALUE" VARCHAR(4000) +); + +CREATE TABLE "EVENTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(400), + "COMPONENT_UUID" VARCHAR(50), + "SNAPSHOT_ID" INTEGER, + "CATEGORY" VARCHAR(50), + "EVENT_DATE" BIGINT NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "DESCRIPTION" VARCHAR(4000), + "EVENT_DATA" VARCHAR(4000) +); + +CREATE TABLE "QUALITY_GATES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(100) NOT NULL, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP, +); + +CREATE TABLE "QUALITY_GATE_CONDITIONS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "QGATE_ID" INTEGER, + "METRIC_ID" INTEGER, + "OPERATOR" VARCHAR(3), + "VALUE_ERROR" VARCHAR(64), + "VALUE_WARNING" VARCHAR(64), + "PERIOD" INTEGER, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP, +); + +CREATE TABLE "PROPERTIES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROP_KEY" VARCHAR(512), + "RESOURCE_ID" INTEGER, + "TEXT_VALUE" CLOB(2147483647), + "USER_ID" INTEGER +); + +CREATE TABLE "PROJECT_LINKS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "COMPONENT_UUID" VARCHAR(50), + "LINK_TYPE" VARCHAR(20), + "NAME" VARCHAR(128), + "HREF" VARCHAR(2048) NOT NULL +); + +CREATE TABLE "DUPLICATIONS_INDEX" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROJECT_SNAPSHOT_ID" INTEGER NOT NULL, + "SNAPSHOT_ID" INTEGER NOT NULL, + "HASH" VARCHAR(50) NOT NULL, + "INDEX_IN_FILE" INTEGER NOT NULL, + "START_LINE" INTEGER NOT NULL, + "END_LINE" INTEGER NOT NULL +); + +CREATE TABLE "PROJECT_MEASURES" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "VALUE" DOUBLE, + "METRIC_ID" INTEGER NOT NULL, + "SNAPSHOT_ID" INTEGER, + "RULE_ID" INTEGER, + "RULES_CATEGORY_ID" INTEGER, + "TEXT_VALUE" VARCHAR(4000), + "TENDENCY" INTEGER, + "MEASURE_DATE" TIMESTAMP, + "PROJECT_ID" INTEGER, + "ALERT_STATUS" VARCHAR(5), + "ALERT_TEXT" VARCHAR(4000), + "URL" VARCHAR(2000), + "DESCRIPTION" VARCHAR(4000), + "RULE_PRIORITY" INTEGER, + "CHARACTERISTIC_ID" INTEGER, + "PERSON_ID" INTEGER, + "VARIATION_VALUE_1" DOUBLE, + "VARIATION_VALUE_2" DOUBLE, + "VARIATION_VALUE_3" DOUBLE, + "VARIATION_VALUE_4" DOUBLE, + "VARIATION_VALUE_5" DOUBLE, + "MEASURE_DATA" BINARY(167772150) +); + +CREATE TABLE "PROJECTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(400), + "ROOT_ID" INTEGER, + "UUID" VARCHAR(50), + "PROJECT_UUID" VARCHAR(50), + "MODULE_UUID" VARCHAR(50), + "MODULE_UUID_PATH" VARCHAR(4000), + "NAME" VARCHAR(256), + "DESCRIPTION" VARCHAR(2000), + "ENABLED" BOOLEAN NOT NULL DEFAULT TRUE, + "SCOPE" VARCHAR(3), + "QUALIFIER" VARCHAR(10), + "DEPRECATED_KEE" VARCHAR(400), + "PATH" VARCHAR(2000), + "LANGUAGE" VARCHAR(20), + "COPY_RESOURCE_ID" INTEGER, + "LONG_NAME" VARCHAR(256), + "PERSON_ID" INTEGER, + "CREATED_AT" TIMESTAMP, + "AUTHORIZATION_UPDATED_AT" BIGINT +); + +CREATE TABLE "MANUAL_MEASURES" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "METRIC_ID" INTEGER NOT NULL, + "COMPONENT_UUID" VARCHAR(50), + "VALUE" DOUBLE, + "TEXT_VALUE" VARCHAR(4000), + "USER_LOGIN" VARCHAR(255), + "DESCRIPTION" VARCHAR(4000), + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT +); + +CREATE TABLE "ACTIVE_RULES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROFILE_ID" INTEGER NOT NULL, + "RULE_ID" INTEGER NOT NULL, + "FAILURE_LEVEL" INTEGER NOT NULL, + "INHERITANCE" VARCHAR(10), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "NOTIFICATIONS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "DATA" BLOB(167772150) +); + +CREATE TABLE "USER_ROLES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "USER_ID" INTEGER, + "RESOURCE_ID" INTEGER, + "ROLE" VARCHAR(64) NOT NULL +); + +CREATE TABLE "ACTIVE_DASHBOARDS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "DASHBOARD_ID" INTEGER NOT NULL, + "USER_ID" INTEGER, + "ORDER_INDEX" INTEGER +); + +CREATE TABLE "ACTIVE_RULE_PARAMETERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ACTIVE_RULE_ID" INTEGER NOT NULL, + "RULES_PARAMETER_ID" INTEGER NOT NULL, + "RULES_PARAMETER_KEY" VARCHAR(128), + "VALUE" VARCHAR(4000) +); + +CREATE TABLE "USERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "LOGIN" VARCHAR(255), + "NAME" VARCHAR(200), + "EMAIL" VARCHAR(100), + "CRYPTED_PASSWORD" VARCHAR(40), + "SALT" VARCHAR(40), + "REMEMBER_TOKEN" VARCHAR(500), + "REMEMBER_TOKEN_EXPIRES_AT" TIMESTAMP, + "ACTIVE" BOOLEAN DEFAULT TRUE, + "SCM_ACCOUNTS" VARCHAR(4000), + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT +); + +CREATE TABLE "DASHBOARDS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "USER_ID" INTEGER, + "NAME" VARCHAR(256), + "DESCRIPTION" VARCHAR(1000), + "COLUMN_LAYOUT" VARCHAR(20), + "SHARED" BOOLEAN, + "IS_GLOBAL" BOOLEAN, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "METRICS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(64) NOT NULL, + "DESCRIPTION" VARCHAR(255), + "DIRECTION" INTEGER NOT NULL DEFAULT 0, + "DOMAIN" VARCHAR(64), + "SHORT_NAME" VARCHAR(64), + "QUALITATIVE" BOOLEAN NOT NULL DEFAULT FALSE, + "VAL_TYPE" VARCHAR(8), + "USER_MANAGED" BOOLEAN DEFAULT FALSE, + "ENABLED" BOOLEAN DEFAULT TRUE, + "WORST_VALUE" DOUBLE, + "BEST_VALUE" DOUBLE, + "OPTIMIZED_BEST_VALUE" BOOLEAN, + "HIDDEN" BOOLEAN, + "DELETE_HISTORICAL_DATA" BOOLEAN +); + +CREATE TABLE "LOADED_TEMPLATES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(200), + "TEMPLATE_TYPE" VARCHAR(15) +); + +CREATE TABLE "RESOURCE_INDEX" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(400) NOT NULL, + "POSITION" INTEGER NOT NULL, + "NAME_SIZE" INTEGER NOT NULL, + "RESOURCE_ID" INTEGER NOT NULL, + "ROOT_PROJECT_ID" INTEGER NOT NULL, + "QUALIFIER" VARCHAR(10) NOT NULL +); + +CREATE TABLE "ACTION_PLANS" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(100), + "NAME" VARCHAR(200), + "DESCRIPTION" VARCHAR(1000), + "DEADLINE" TIMESTAMP, + "USER_LOGIN" VARCHAR(255), + "PROJECT_ID" INTEGER, + "STATUS" VARCHAR(10), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "AUTHORS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PERSON_ID" INTEGER, + "LOGIN" VARCHAR(100), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "SEMAPHORES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(4000), + "CHECKSUM" VARCHAR(200), + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "LOCKED_AT" BIGINT +); + +CREATE TABLE "MEASURE_FILTERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(100) NOT NULL, + "SHARED" BOOLEAN NOT NULL DEFAULT FALSE, + "USER_ID" INTEGER, + "DESCRIPTION" VARCHAR(4000), + "DATA" CLOB(2147483647), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "MEASURE_FILTER_FAVOURITES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "USER_ID" INTEGER NOT NULL, + "MEASURE_FILTER_ID" INTEGER NOT NULL, + "CREATED_AT" TIMESTAMP +); + +CREATE TABLE "ISSUES" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(50) UNIQUE NOT NULL, + "COMPONENT_UUID" VARCHAR(50), + "PROJECT_UUID" VARCHAR(50), + "RULE_ID" INTEGER, + "SEVERITY" VARCHAR(10), + "MANUAL_SEVERITY" BOOLEAN NOT NULL, + "MESSAGE" VARCHAR(4000), + "LINE" INTEGER, + "EFFORT_TO_FIX" DOUBLE, + "TECHNICAL_DEBT" INTEGER, + "STATUS" VARCHAR(20), + "RESOLUTION" VARCHAR(20), + "CHECKSUM" VARCHAR(1000), + "REPORTER" VARCHAR(255), + "ASSIGNEE" VARCHAR(255), + "AUTHOR_LOGIN" VARCHAR(255), + "ACTION_PLAN_KEY" VARCHAR(50) NULL, + "ISSUE_ATTRIBUTES" VARCHAR(4000), + "TAGS" VARCHAR(4000), + "ISSUE_CREATION_DATE" BIGINT, + "ISSUE_CLOSE_DATE" BIGINT, + "ISSUE_UPDATE_DATE" BIGINT, + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT +); + +CREATE TABLE "ISSUE_CHANGES" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(50), + "ISSUE_KEY" VARCHAR(50) NOT NULL, + "USER_LOGIN" VARCHAR(255), + "CHANGE_TYPE" VARCHAR(40), + "CHANGE_DATA" VARCHAR(16777215), + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "ISSUE_CHANGE_CREATION_DATE" BIGINT +); + +CREATE TABLE "ISSUE_FILTERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(100) NOT NULL, + "SHARED" BOOLEAN NOT NULL DEFAULT FALSE, + "USER_LOGIN" VARCHAR(255), + "DESCRIPTION" VARCHAR(4000), + "DATA" CLOB(2147483647), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "ISSUE_FILTER_FAVOURITES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "USER_LOGIN" VARCHAR(255) NOT NULL, + "ISSUE_FILTER_ID" INTEGER NOT NULL, + "CREATED_AT" TIMESTAMP +); + +CREATE TABLE "PERMISSION_TEMPLATES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "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_USERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "USER_ID" INTEGER NOT NULL, + "TEMPLATE_ID" INTEGER NOT NULL, + "PERMISSION_REFERENCE" VARCHAR(64) NOT NULL, + "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 +); + + +CREATE TABLE "ACTIVITIES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "LOG_KEY" VARCHAR(250), + "CREATED_AT" TIMESTAMP, + "USER_LOGIN" VARCHAR(30), + "LOG_TYPE" VARCHAR(250), + "LOG_ACTION" VARCHAR(250), + "LOG_MESSAGE" VARCHAR(250), + "DATA_FIELD" CLOB(2147483647) +); + +CREATE TABLE "ANALYSIS_REPORTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROJECT_KEY" VARCHAR(400) NOT NULL, + "PROJECT_NAME" VARCHAR(256) NULL, + "REPORT_STATUS" VARCHAR(20) NOT NULL, + "UUID" VARCHAR(50) NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + "STARTED_AT" BIGINT, + "FINISHED_AT" BIGINT +); + +CREATE TABLE "FILE_SOURCES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "FILE_UUID" VARCHAR(50) NOT NULL, + "LINE_HASHES" CLOB(2147483647), + "BINARY_DATA" BLOB(167772150), + "DATA_TYPE" VARCHAR(20), + "DATA_HASH" VARCHAR(50), + "SRC_HASH" VARCHAR(50), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); + +-- ---------------------------------------------- +-- DDL Statements for indexes +-- ---------------------------------------------- + +CREATE UNIQUE INDEX "LOG_KEY_INDEX" ON "ACTIVITIES" ("LOG_KEY"); + +CREATE INDEX "GROUP_ROLES_RESOURCE" ON "GROUP_ROLES" ("RESOURCE_ID"); + +CREATE INDEX "GROUP_ROLES_GROUP" ON "GROUP_ROLES" ("GROUP_ID"); + +CREATE INDEX "USER_ROLES_RESOURCE" ON "USER_ROLES" ("RESOURCE_ID"); + +CREATE INDEX "USER_ROLES_USER" ON "USER_ROLES" ("USER_ID"); + +CREATE INDEX "DUPLICATIONS_INDEX_HASH" ON "DUPLICATIONS_INDEX" ("HASH"); + +CREATE INDEX "DUPLICATIONS_INDEX_SID" ON "DUPLICATIONS_INDEX" ("SNAPSHOT_ID"); + +CREATE INDEX "DUPLICATIONS_INDEX_PSID" ON "DUPLICATIONS_INDEX" ("PROJECT_SNAPSHOT_ID"); + +CREATE INDEX "INDEX_GROUPS_USERS_ON_GROUP_ID" ON "GROUPS_USERS" ("GROUP_ID"); + +CREATE INDEX "INDEX_GROUPS_USERS_ON_USER_ID" ON "GROUPS_USERS" ("USER_ID"); + +CREATE UNIQUE INDEX "GROUPS_USERS_UNIQUE" ON "GROUPS_USERS" ("GROUP_ID", "USER_ID"); + +CREATE INDEX "MEASURES_SID_METRIC" ON "PROJECT_MEASURES" ("SNAPSHOT_ID", "METRIC_ID"); + +CREATE UNIQUE INDEX "METRICS_UNIQUE_NAME" ON "METRICS" ("NAME"); + +CREATE INDEX "EVENTS_SNAPSHOT_ID" ON "EVENTS" ("SNAPSHOT_ID"); + +CREATE INDEX "EVENTS_COMPONENT_UUID" ON "EVENTS" ("COMPONENT_UUID"); + +CREATE INDEX "WIDGETS_WIDGETKEY" ON "WIDGETS" ("WIDGET_KEY"); + +CREATE INDEX "WIDGETS_DASHBOARDS" ON "WIDGETS" ("DASHBOARD_ID"); + +CREATE INDEX "SNAPSHOTS_QUALIFIER" ON "SNAPSHOTS" ("QUALIFIER"); + +CREATE INDEX "SNAPSHOTS_ROOT" ON "SNAPSHOTS" ("ROOT_SNAPSHOT_ID"); + +CREATE INDEX "SNAPSHOTS_PARENT" ON "SNAPSHOTS" ("PARENT_SNAPSHOT_ID"); + +CREATE INDEX "SNAPSHOT_PROJECT_ID" ON "SNAPSHOTS" ("PROJECT_ID"); + +CREATE INDEX "RULES_PARAMETERS_RULE_ID" ON "RULES_PARAMETERS" ("RULE_ID"); + +CREATE INDEX "ACTIVE_DASHBOARDS_DASHBOARDID" ON "ACTIVE_DASHBOARDS" ("DASHBOARD_ID"); + +CREATE INDEX "ACTIVE_DASHBOARDS_USERID" ON "ACTIVE_DASHBOARDS" ("USER_ID"); + +CREATE INDEX "UNIQUE_SCHEMA_MIGRATIONS" ON "SCHEMA_MIGRATIONS" ("VERSION"); + +CREATE INDEX "WIDGET_PROPERTIES_WIDGETS" ON "WIDGET_PROPERTIES" ("WIDGET_ID"); + +CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES" ("PROP_KEY"); + +CREATE INDEX "MANUAL_MEASURES_COMPONENT_UUID" ON "MANUAL_MEASURES" ("COMPONENT_UUID"); + +CREATE INDEX "PROJECTS_KEE" ON "PROJECTS" ("KEE", "ENABLED"); + +CREATE INDEX "PROJECTS_ROOT_ID" ON "PROJECTS" ("ROOT_ID"); + +CREATE UNIQUE INDEX "PROJECTS_UUID" ON "PROJECTS" ("UUID"); + +CREATE INDEX "PROJECTS_PROJECT_UUID" ON "PROJECTS" ("PROJECT_UUID"); + +CREATE INDEX "PROJECTS_MODULE_UUID" ON "PROJECTS" ("MODULE_UUID"); + +CREATE INDEX "RESOURCE_INDEX_KEE" ON "RESOURCE_INDEX" ("KEE"); + +CREATE INDEX "RESOURCE_INDEX_RID" ON "RESOURCE_INDEX" ("RESOURCE_ID"); + +CREATE INDEX "INDEX_ACTION_PLANS_ON_PROJET_ID" ON "ACTION_PLANS" ("PROJECT_ID"); + +CREATE UNIQUE INDEX "UNIQ_SEMAPHORE_CHECKSUMS" ON "SEMAPHORES" ("CHECKSUM"); + +CREATE INDEX "SEMAPHORE_NAMES" ON "SEMAPHORES" ("NAME"); + +CREATE UNIQUE INDEX "UNIQ_AUTHOR_LOGINS" ON "AUTHORS" ("LOGIN"); + +CREATE INDEX "MEASURE_FILTERS_NAME" ON "MEASURE_FILTERS" ("NAME"); + +CREATE INDEX "MEASURE_FILTER_FAVS_USERID" ON "MEASURE_FILTER_FAVOURITES" ("USER_ID"); + +CREATE UNIQUE INDEX "ISSUES_KEE" ON "ISSUES" ("KEE"); + +CREATE INDEX "ISSUES_COMPONENT_UUID" ON "ISSUES" ("COMPONENT_UUID"); + +CREATE INDEX "ISSUES_PROJECT_UUID" ON "ISSUES" ("PROJECT_UUID"); + +CREATE INDEX "ISSUES_RULE_ID" ON "ISSUES" ("RULE_ID"); + +CREATE INDEX "ISSUES_SEVERITY" ON "ISSUES" ("SEVERITY"); + +CREATE INDEX "ISSUES_STATUS" ON "ISSUES" ("STATUS"); + +CREATE INDEX "ISSUES_RESOLUTION" ON "ISSUES" ("RESOLUTION"); + +CREATE INDEX "ISSUES_ASSIGNEE" ON "ISSUES" ("ASSIGNEE"); + +CREATE INDEX "ISSUES_ACTION_PLAN_KEY" ON "ISSUES" ("ACTION_PLAN_KEY"); + +CREATE INDEX "ISSUES_CREATION_DATE" ON "ISSUES" ("ISSUE_CREATION_DATE"); + +CREATE INDEX "ISSUES_UPDATED_AT" ON "ISSUES" ("UPDATED_AT"); + +CREATE INDEX "ISSUE_CHANGES_KEE" ON "ISSUE_CHANGES" ("KEE"); + +CREATE INDEX "ISSUE_CHANGES_ISSUE_KEY" ON "ISSUE_CHANGES" ("ISSUE_KEY"); + +CREATE INDEX "ISSUE_FILTERS_NAME" ON "ISSUE_FILTERS" ("NAME"); + +CREATE INDEX "ISSUE_FILTER_FAVS_USER" ON "ISSUE_FILTER_FAVOURITES" ("USER_LOGIN"); + +CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS" ("LOGIN"); + +CREATE INDEX "USERS_UPDATED_AT" ON "USERS" ("UPDATED_AT"); + +CREATE INDEX "SNAPSHOTS_ROOT_PROJECT_ID" ON "SNAPSHOTS" ("ROOT_PROJECT_ID"); + +CREATE INDEX "GROUP_ROLES_ROLE" ON "GROUP_ROLES" ("ROLE"); + +CREATE UNIQUE INDEX "UNIQ_GROUP_ROLES" ON "GROUP_ROLES" ("GROUP_ID", "RESOURCE_ID", "ROLE"); + +CREATE UNIQUE INDEX "RULES_REPO_KEY" ON "RULES" ("PLUGIN_NAME", "PLUGIN_RULE_KEY"); + +CREATE INDEX "CHARACTERISTICS_ENABLED" ON "CHARACTERISTICS" ("ENABLED"); + +CREATE UNIQUE INDEX "QUALITY_GATES_UNIQUE" ON "QUALITY_GATES" ("NAME"); + +CREATE UNIQUE INDEX "ACTIVE_RULES_UNIQUE" ON "ACTIVE_RULES" ("PROFILE_ID","RULE_ID"); + +CREATE UNIQUE INDEX "PROFILE_UNIQUE_KEY" ON "RULES_PROFILES" ("KEE"); + +CREATE INDEX "FILE_SOURCES_PROJECT_UUID" ON "FILE_SOURCES" ("PROJECT_UUID"); + +CREATE UNIQUE INDEX "FILE_SOURCES_UUID_TYPE_UNIQUE" ON "FILE_SOURCES" ("FILE_UUID", "DATA_TYPE"); + +CREATE INDEX "FILE_SOURCES_UPDATED_AT" ON "FILE_SOURCES" ("UPDATED_AT"); + +CREATE UNIQUE INDEX "PROJECT_QPROFILES_UNIQUE" ON "PROJECT_QPROFILES" ("PROJECT_UUID", "PROFILE_KEY"); diff --git a/sonar-db/src/main/resources/org/sonar/db/version/v44/Migration44Mapper.xml b/sonar-db/src/main/resources/org/sonar/db/version/v44/Migration44Mapper.xml new file mode 100644 index 00000000000..30bcffddccb --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/version/v44/Migration44Mapper.xml @@ -0,0 +1,105 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.version.v44.Migration44Mapper"> + + <select id="selectProfileMeasures" resultType="org.sonar.db.version.v44.ProfileMeasure"> + select pm.id as id, pm.value as profileId, pm.snapshot_id as snapshotId + from project_measures pm + inner join metrics m on m.id=pm.metric_id and m.name='profile' + inner join snapshots s on s.islast=${_true} and pm.snapshot_id=s.id and s.scope='PRJ' + where pm.value is not null + </select> + + <select id="selectProfileVersion" resultType="int" parameterType="long"> + select pm.value from project_measures pm + inner join metrics m on m.id=pm.metric_id and m.name='profile_version' + inner join snapshots s on pm.snapshot_id=s.id + where pm.value is not null and s.id=#{id} + </select> + + <select id="selectProfileById" resultType="org.sonar.db.version.v44.QProfileDto44" parameterType="int"> + select id, kee, name, language + from rules_profiles + where id=#{id} + </select> + + <select id="selectProfileVersionDate" resultType="date" parameterType="map"> + select max(change_date) from active_rule_changes + where profile_id=#{profileId} and profile_version=#{profileVersion} + </select> + + <update id="updateProfileMeasure" parameterType="map"> + update project_measures + set text_value=#{json}, value=null + where id=#{measureId} + </update> + + <delete id="deleteProfileMeasure" parameterType="long"> + delete from project_measures where id=#{id} + </delete> + + <select id="selectMeasuresOnDeletedQualityProfiles" resultType="long"> + select pm.id from project_measures pm + inner join snapshots s on s.id=pm.snapshot_id and s.islast=${_true} + where + pm.metric_id=(select id from metrics where name='quality_profiles') + and pm.value is not null + </select> + + <select id="selectAllProfiles" resultType="org.sonar.db.version.v44.QProfileDto44"> + select id, kee, name, language from rules_profiles + </select> + + <select id="selectProfileUpdatedAt" resultType="date" parameterType="int"> + select max(change_date) from active_rule_changes + where profile_id=#{id} + </select> + + <select id="selectProfileCreatedAt" resultType="date" parameterType="int"> + select min(change_date) from active_rule_changes + where profile_id=#{id} + </select> + + <update id="updateProfileDates" parameterType="map"> + update rules_profiles + set created_at=#{createdAt}, updated_at=#{updatedAt}, rules_updated_at=#{rulesUpdatedAt} + where id=#{profileId} + </update> + + <select id="selectActiveRuleChange" parameterType="Boolean" + resultType="org.sonar.db.version.v44.ChangeLog"> + select + rule_change.id as id, + rule_change.change_date as createdAt, + users.login as userLogin, + rule_def.plugin_name as repository, + rule_def.plugin_rule_key as ruleKey, + profile.kee as profileKey, + rule_change.new_severity as severity , + rule_def.name as ruleName, + rule_def_param.name as paramKey, + rule_param_change.new_value as paramValue + from active_rule_changes rule_change + left join users on users.name = rule_change.username + left join rules rule_def on rule_def.id = rule_change.rule_id + left join rules_profiles profile on profile.id = rule_change.profile_id + left join active_rule_param_changes rule_param_change on rule_param_change.active_rule_change_id = rule_change.id + left join rules_parameters rule_def_param on rule_param_change.rules_parameter_id = rule_def_param.id + WHERE + <choose> + <when test="enabled != null"> + rule_change.enabled = #{enabled} + </when> + <otherwise> + rule_change.enabled is null + </otherwise> + </choose> + AND profile.name is not null + AND profile.language is not null + AND rule_def.plugin_name is not null + AND rule_def.plugin_name is not null + order by rule_change.id ASC + </select> +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/version/v45/Migration45Mapper.xml b/sonar-db/src/main/resources/org/sonar/db/version/v45/Migration45Mapper.xml new file mode 100644 index 00000000000..0b741e6d396 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/version/v45/Migration45Mapper.xml @@ -0,0 +1,7 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.version.v45.Migration45Mapper"> + +</mapper> + diff --git a/sonar-db/src/main/resources/org/sonar/db/version/v50/Migration50Mapper.xml b/sonar-db/src/main/resources/org/sonar/db/version/v50/Migration50Mapper.xml new file mode 100644 index 00000000000..46fd146b117 --- /dev/null +++ b/sonar-db/src/main/resources/org/sonar/db/version/v50/Migration50Mapper.xml @@ -0,0 +1,7 @@ +<?xml version="1.0" encoding="UTF-8" ?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> + +<mapper namespace="org.sonar.db.version.v50.Migration50Mapper"> + +</mapper> + |