Gilad Chaplik has uploaded a new change for review. Change subject: db: Affinity Groups database support ......................................................................
db: Affinity Groups database support * Create affinity groups and members tables * Add affinity group view (join of affinity group and members) * Add CRUD stored procedure for affinity groups, the procedures will control both of the tables, i.e. insert affinity group, will fill its members as well. * Stored procedures for fetching Affinty Groups: * by id * by cluster id * by vm id For more information please refer to: http://www.ovirt.org/Features/VM-Affinity Change-Id: Ib09c50cae8d9a23a08a9e723d0e0498ea2016e6a Bug-Url: https://bugzilla.redhat.com/1036638 Signed-off-by: Gilad Chaplik <gchap...@redhat.com> --- M backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dal/dbbroker/DbFacade.java M backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/BaseDAODbFacade.java M backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/DiskImageDAODbFacadeImpl.java A backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/scheduling/AffinityGroupDao.java A backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/scheduling/AffinityGroupDaoImpl.java M backend/manager/modules/dal/src/main/jdbc-resources/engine-daos.properties A packaging/dbscripts/affinity_groups_sp.sql M packaging/dbscripts/create_views.sql A packaging/dbscripts/upgrade/03_04_0300_create_affinity_group_tables.sql 9 files changed, 275 insertions(+), 11 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/14/22714/1 diff --git a/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dal/dbbroker/DbFacade.java b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dal/dbbroker/DbFacade.java index 3a0c04e..a9da479 100644 --- a/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dal/dbbroker/DbFacade.java +++ b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dal/dbbroker/DbFacade.java @@ -46,7 +46,6 @@ import org.ovirt.engine.core.common.job.Step; import org.ovirt.engine.core.compat.Guid; import org.ovirt.engine.core.dao.ActionGroupDAO; -import org.ovirt.engine.core.dao.DbGroupDAO; import org.ovirt.engine.core.dao.AsyncTaskDAO; import org.ovirt.engine.core.dao.AuditLogDAO; import org.ovirt.engine.core.dao.BaseDAODbFacade; @@ -55,6 +54,7 @@ import org.ovirt.engine.core.dao.BusinessEntitySnapshotDAO; import org.ovirt.engine.core.dao.DAO; import org.ovirt.engine.core.dao.DaoFactory; +import org.ovirt.engine.core.dao.DbGroupDAO; import org.ovirt.engine.core.dao.DbUserDAO; import org.ovirt.engine.core.dao.DiskDao; import org.ovirt.engine.core.dao.DiskImageDAO; @@ -118,6 +118,7 @@ import org.ovirt.engine.core.dao.network.VnicProfileDao; import org.ovirt.engine.core.dao.network.VnicProfileViewDao; import org.ovirt.engine.core.dao.provider.ProviderDao; +import org.ovirt.engine.core.dao.scheduling.AffinityGroupDao; import org.ovirt.engine.core.dao.scheduling.ClusterPolicyDao; import org.ovirt.engine.core.dao.scheduling.PolicyUnitDao; import org.ovirt.engine.core.utils.linq.LinqUtils; @@ -952,6 +953,15 @@ } /** + * Returns the singleton instance of {@link AffinityGroupDao}. + * + * @return the dao instance + */ + public AffinityGroupDao getAffinityGroupDao() { + return getDao(AffinityGroupDao.class); + } + + /** * This call will populate a translation table of OS Ids to they're name * The translation table shall be in use by DWH * diff --git a/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/BaseDAODbFacade.java b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/BaseDAODbFacade.java index 7a9af36..bf2a1ef 100644 --- a/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/BaseDAODbFacade.java +++ b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/BaseDAODbFacade.java @@ -2,8 +2,11 @@ import java.sql.ResultSet; import java.sql.SQLException; +import java.util.ArrayList; +import java.util.Arrays; import java.util.UUID; +import org.apache.commons.lang.StringUtils; import org.ovirt.engine.core.compat.Guid; import org.ovirt.engine.core.dal.dbbroker.CustomMapSqlParameterSource; import org.ovirt.engine.core.dal.dbbroker.DbEngineDialect; @@ -13,6 +16,7 @@ import org.springframework.jdbc.core.RowMapper; public abstract class BaseDAODbFacade { + protected static final String SEPARATOR = ","; protected JdbcTemplate jdbcTemplate; protected DbEngineDialect dialect; @@ -167,4 +171,12 @@ protected static Guid getGuidDefaultNewGuid(ResultSet resultSet, String columnName) throws SQLException { return getGuid(resultSet, columnName, Guid.newGuid()); } + + protected static ArrayList<String> split(String str) { + if (StringUtils.isEmpty(str)) { + return null; + } + + return new ArrayList<String>(Arrays.asList(str.split(SEPARATOR))); + } } diff --git a/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/DiskImageDAODbFacadeImpl.java b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/DiskImageDAODbFacadeImpl.java index 592556f..e517b55 100644 --- a/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/DiskImageDAODbFacadeImpl.java +++ b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/DiskImageDAODbFacadeImpl.java @@ -180,16 +180,6 @@ return new DiskImage(); } - private static final String SEPARATOR = ","; - - private ArrayList<String> split(String str) { - if (StringUtils.isEmpty(str)) { - return null; - } - - return new ArrayList<String>(Arrays.asList(str.split(SEPARATOR))); - } - /** * since quota can be null, we need to preserve null in the list * diff --git a/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/scheduling/AffinityGroupDao.java b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/scheduling/AffinityGroupDao.java new file mode 100644 index 0000000..3583757 --- /dev/null +++ b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/scheduling/AffinityGroupDao.java @@ -0,0 +1,13 @@ +package org.ovirt.engine.core.dao.scheduling; + +import java.util.List; + +import org.ovirt.engine.core.common.scheduling.AffinityGroup; +import org.ovirt.engine.core.compat.Guid; +import org.ovirt.engine.core.dao.GenericDao; + +public interface AffinityGroupDao extends GenericDao<AffinityGroup, Guid> { + List<AffinityGroup> getAllAffinityGroupsByClusterId(Guid clusterId); + + List<AffinityGroup> getAllAffinityGroupsByVmId(Guid vmId); +} diff --git a/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/scheduling/AffinityGroupDaoImpl.java b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/scheduling/AffinityGroupDaoImpl.java new file mode 100644 index 0000000..03ee746 --- /dev/null +++ b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dao/scheduling/AffinityGroupDaoImpl.java @@ -0,0 +1,84 @@ +package org.ovirt.engine.core.dao.scheduling; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.List; + +import org.apache.commons.lang.StringUtils; +import org.ovirt.engine.core.common.scheduling.AffinityGroup; +import org.ovirt.engine.core.common.scheduling.enums.AffinityEnforcementType; +import org.ovirt.engine.core.common.scheduling.enums.AffinityPolarity; +import org.ovirt.engine.core.compat.Guid; +import org.ovirt.engine.core.dao.DefaultGenericDaoDbFacade; +import org.ovirt.engine.core.utils.GuidUtils; +import org.springframework.jdbc.core.RowMapper; +import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; + +public class AffinityGroupDaoImpl extends DefaultGenericDaoDbFacade<AffinityGroup, Guid> implements AffinityGroupDao { + + public AffinityGroupDaoImpl() { + super("AffinityGroup"); + } + + @Override + public List<AffinityGroup> getAllAffinityGroupsByClusterId(Guid clusterId) { + return getCallsHandler().executeReadList("getAllAffinityGroupsByClusterId", + createEntityRowMapper(), + getCustomMapSqlParameterSource().addValue("cluster_id", clusterId)); + } + + @Override + public List<AffinityGroup> getAllAffinityGroupsByVmId(Guid vmId) { + return getCallsHandler().executeReadList("getAllAffinityGroupsByVmId", + createEntityRowMapper(), + getCustomMapSqlParameterSource().addValue("vm_id", vmId)); + } + + @Override + public void save(AffinityGroup entity) { + getCallsHandler().executeModification("InsertAffinityGroupWithMembers", createFullParametersMapper(entity)); + } + + @Override + public void update(AffinityGroup entity) { + getCallsHandler().executeModification("UpdateAffinityGroupWithMembers", createFullParametersMapper(entity)); + } + + @Override + protected MapSqlParameterSource createFullParametersMapper(AffinityGroup entity) { + return createIdParameterMapper(entity.getId()) + .addValue("name", entity.getName()) + .addValue("description", entity.getDescription()) + .addValue("cluster_id", entity.getClusterId()) + .addValue("polarity", entity.getPolarity()) + .addValue("enforcement_type", entity.getEnforcementType()) + .addValue("vm_ids", + entity.getVmIds() == null ? StringUtils.EMPTY : StringUtils.join(entity.getVmIds(), SEPARATOR)); + } + + @Override + protected MapSqlParameterSource createIdParameterMapper(Guid id) { + return getCustomMapSqlParameterSource().addValue("id", id); + } + + @Override + protected RowMapper<AffinityGroup> createEntityRowMapper() { + return new RowMapper<AffinityGroup>() { + + @Override + public AffinityGroup mapRow(ResultSet rs, int arg1) throws SQLException { + AffinityGroup affinityGroup = new AffinityGroup(); + affinityGroup.setId(getGuid(rs, "id")); + affinityGroup.setName(rs.getString("name")); + affinityGroup.setDescription(rs.getString("description")); + affinityGroup.setClusterId(getGuid(rs, "cluster_id")); + affinityGroup.setPolarity(AffinityPolarity.forValue(rs.getInt("polarity"))); + affinityGroup.setEnforcementType(AffinityEnforcementType.forValue(rs.getInt("enforcement_type"))); + affinityGroup.setVmIds(GuidUtils.getGuidListFromString(rs.getString("vm_ids"))); + affinityGroup.setVmNames(split(rs.getString("vm_names"))); + + return affinityGroup; + } + }; + } +} diff --git a/backend/manager/modules/dal/src/main/jdbc-resources/engine-daos.properties b/backend/manager/modules/dal/src/main/jdbc-resources/engine-daos.properties index 60ead40..906d13c 100644 --- a/backend/manager/modules/dal/src/main/jdbc-resources/engine-daos.properties +++ b/backend/manager/modules/dal/src/main/jdbc-resources/engine-daos.properties @@ -70,3 +70,4 @@ PolicyUnitDao=org.ovirt.engine.core.dao.scheduling.PolicyUnitDaoImpl ClusterPolicyDao=org.ovirt.engine.core.dao.scheduling.ClusterPolicyDaoImpl DwhHistoryTimekeepingDao=org.ovirt.engine.core.dao.dwh.DwhHistoryTimekeepingDaoDbFacadeImpl +AffinityGroupDao=org.ovirt.engine.core.dao.scheduling.AffinityGroupDaoImpl diff --git a/packaging/dbscripts/affinity_groups_sp.sql b/packaging/dbscripts/affinity_groups_sp.sql new file mode 100644 index 0000000..31b5e08 --- /dev/null +++ b/packaging/dbscripts/affinity_groups_sp.sql @@ -0,0 +1,112 @@ +-- Affinity Groups Stored Procedures script file + +-- get All Affinity Groups with members by vm id +Create or replace FUNCTION getAllAffinityGroupsByVmId(v_vm_id UUID) RETURNS SETOF affinity_groups_view STABLE +AS $procedure$ +BEGIN + RETURN QUERY + SELECT affinity_groups_view.* + FROM affinity_groups_view + JOIN affinity_group_members ON v_vm_id = affinity_group_members.vm_id + AND affinity_group_members.affinity_group_id = affinity_groups_view.id; +END; $procedure$ +LANGUAGE plpgsql; + +-- get All Affinity Groups with members by cluster id +Create or replace FUNCTION getAllAffinityGroupsByClusterId(v_cluster_id UUID) RETURNS SETOF affinity_groups_view STABLE +AS $procedure$ +BEGIN + RETURN QUERY + SELECT * + FROM affinity_groups_view + WHERE cluster_id = v_cluster_id; +END; $procedure$ +LANGUAGE plpgsql; + +-- get Affinity Group with members by id +Create or replace FUNCTION GetAffinityGroupByAffinityGroupId(v_id UUID) RETURNS SETOF affinity_groups_view STABLE +AS $procedure$ +BEGIN + RETURN QUERY + SELECT * + FROM affinity_groups_view + WHERE id = v_id; +END; $procedure$ +LANGUAGE plpgsql; + +-- Insert Affinity Group with members +Create or replace FUNCTION InsertAffinityGroupWithMembers( + v_id UUID, + v_name VARCHAR(128), + v_description VARCHAR(4000), + v_cluster_id UUID, + v_polarity SMALLINT, + v_enforcement_type SMALLINT, + v_vm_ids VARCHAR(4000) +) +RETURNS VOID +AS $procedure$ +DECLARE + iter_id UUID; +BEGIN + INSERT INTO affinity_groups( + id, + name, + description, + cluster_id, + polarity, + enforcement_type) + VALUES( + v_id, + v_name, + v_description, + v_cluster_id, + v_polarity, + v_enforcement_type); + FOR iter_id IN (SELECT * FROM fnsplitteruuid(v_vm_ids)) + LOOP + INSERT INTO affinity_group_members( + affinity_group_id, + vm_id) + VALUES( + v_id, + iter_id); + END LOOP; +END; $procedure$ +LANGUAGE plpgsql; + +-- Delete Affinity Group (uses cascade to remove members) +Create or replace FUNCTION DeleteAffinityGroup(v_id UUID) +RETURNS VOID +AS $procedure$ +BEGIN + DELETE + FROM affinity_groups + WHERE id = v_id; +END; $procedure$ +LANGUAGE plpgsql; + +-- Update Affinity Group (implemeted using Delete and Insert SPs) +Create or replace FUNCTION UpdateAffinityGroupWithMembers( + v_id UUID, + v_name VARCHAR(128), + v_description VARCHAR(4000), + v_cluster_id UUID, + v_polarity SMALLINT, + v_enforcement_type SMALLINT, + v_vm_ids VARCHAR(4000) +) +RETURNS VOID +AS $procedure$ +BEGIN + PERFORM DeleteAffinityGroup(v_id); + PERFORM InsertAffinityGroupWithMembers( + v_id, + v_name, + v_description, + v_cluster_id, + v_polarity, + v_enforcement_type, + v_vm_ids); +END; $procedure$ +LANGUAGE plpgsql; diff --git a/packaging/dbscripts/create_views.sql b/packaging/dbscripts/create_views.sql index 253dfc8..bcf535d 100644 --- a/packaging/dbscripts/create_views.sql +++ b/packaging/dbscripts/create_views.sql @@ -1642,3 +1642,14 @@ vds_static.vds_name AS server_name FROM gluster_server_hooks INNER JOIN vds_static ON gluster_server_hooks.server_id = vds_static.vds_id; + +-- Affinity Groups view, including members +CREATE OR REPLACE VIEW affinity_groups_view +AS +SELECT affinity_groups.*, + array_to_string(array_agg(affinity_group_members.vm_id), ',') as vm_ids, + array_to_string(array_agg(vm_static.vm_name), ',') as vm_names +FROM affinity_groups +LEFT JOIN affinity_group_members ON affinity_group_members.affinity_group_id = affinity_groups.id +LEFT JOIN vm_static ON vm_static.vm_guid = affinity_group_members.vm_id +GROUP BY affinity_groups.id; diff --git a/packaging/dbscripts/upgrade/03_04_0300_create_affinity_group_tables.sql b/packaging/dbscripts/upgrade/03_04_0300_create_affinity_group_tables.sql new file mode 100644 index 0000000..7060eb9 --- /dev/null +++ b/packaging/dbscripts/upgrade/03_04_0300_create_affinity_group_tables.sql @@ -0,0 +1,31 @@ +CREATE OR REPLACE FUNCTION __temp_Upgrade_AddAffinityGroupsTables() +RETURNS void +AS $function$ +BEGIN + IF EXISTS (SELECT * FROM information_schema.tables WHERE table_name ILIKE 'affinity_groups') THEN + RETURN; + END IF; + -- create affinity_groups table + CREATE TABLE affinity_groups + ( + id UUID NOT NULL CONSTRAINT affinity_group_pk PRIMARY KEY, + name VARCHAR(128) NOT NULL, + description VARCHAR(4000), + cluster_id UUID NOT NULL CONSTRAINT affinity_group_cluster_id_fk REFERENCES vds_groups(vds_group_id) ON DELETE CASCADE, + polarity SMALLINT NOT NULL, + enforcement_type SMALLINT NOT NULL + ); + -- create affinity_groups members table + CREATE TABLE affinity_group_members + ( + affinity_group_id UUID NOT NULL CONSTRAINT affinity_group_member_affinity_id_fk REFERENCES affinity_groups(id) ON DELETE CASCADE, + vm_id UUID NOT NULL CONSTRAINT affinity_group_member_vm_id_fk REFERENCES vm_static(vm_guid) ON DELETE CASCADE + ); + +END; $function$ +LANGUAGE plpgsql; + +SELECT * FROM __temp_Upgrade_AddAffinityGroupsTables(); + +DROP FUNCTION __temp_Upgrade_AddAffinityGroupsTables(); + -- To view, visit http://gerrit.ovirt.org/22714 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ib09c50cae8d9a23a08a9e723d0e0498ea2016e6a Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Gilad Chaplik <gchap...@redhat.com> _______________________________________________ Engine-patches mailing list Engine-patches@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-patches
