refactoring of database on pac-components

Peter Hormanns

2013-05-13 522b89c5372746a79c500578967c3e8ba276320a

refactoring of database on pac-components

 hsarback/build.xml

@@ -132,6 +132,12 @@
            url="jdbc:postgresql://${database.host}:5432/${database.name}"
            userid="${database.user}" password="${database.password}" 
            src="database/data.sql" />        
        <sql
            classpath="/usr/share/java/postgresql-jdbc3.jar"
            driver="org.postgresql.Driver" 
            url="jdbc:postgresql://${database.host}:5432/${database.name}"
            userid="${database.user}" password="${database.password}" 
            src="database/database_update.sql" />
    </target>
    
    <target name="drop-db" description="make empty database">

 hsarback/database/data.sql

@@ -141,13 +141,13 @@
    SELECT 1, 2, packet.packet_id, 128, current_date FROM packet 
        WHERE packet.packet_name = 'hsh00'; 
INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
    SELECT 1, 3, packet.packet_id, 0, current_date FROM packet 
    SELECT 1, 3, packet.packet_id, 1, current_date FROM packet 
        WHERE packet.packet_name = 'hsh00'; 
INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
    SELECT 1, 4, packet.packet_id, 0, current_date FROM packet 
    SELECT 1, 4, packet.packet_id, 1, current_date FROM packet 
        WHERE packet.packet_name = 'hsh00'; 
INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
    SELECT 1, 5, packet.packet_id, 0, current_date FROM packet 
    SELECT 1, 5, packet.packet_id, 1, current_date FROM packet 
        WHERE packet.packet_name = 'hsh00'; 
INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
    SELECT 1, 1, packet.packet_id, 2, current_date FROM packet 
@@ -156,13 +156,13 @@
    SELECT 1, 2, packet.packet_id, 128, current_date FROM packet 
        WHERE packet.packet_name = 'hsh01'; 
INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
    SELECT 1, 3, packet.packet_id, 0, current_date FROM packet 
    SELECT 1, 3, packet.packet_id, 1, current_date FROM packet 
        WHERE packet.packet_name = 'hsh01'; 
INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
    SELECT 1, 4, packet.packet_id, 0, current_date FROM packet 
    SELECT 1, 4, packet.packet_id, 1, current_date FROM packet 
        WHERE packet.packet_name = 'hsh01'; 
INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created)
    SELECT 1, 5, packet.packet_id, 0, current_date FROM packet 
    SELECT 1, 5, packet.packet_id, 1, current_date FROM packet 
        WHERE packet.packet_name = 'hsh01'; 

--

 hsarback/database/database_update.sql

@@ -25,14 +25,29 @@
    NO MINVALUE
    CACHE 1;

CREATE SEQUENCE component_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE packet_component ADD COLUMN packet_component_id integer 
    DEFAULT nextval(('"packet_component_id_seq"'::text)::regclass) NOT NULL;

ALTER TABLE component ADD COLUMN component_id integer 
    DEFAULT nextval(('"component_id_seq"'::text)::regclass) NOT NULL;
    
ALTER TABLE ONLY packet_component    
    DROP CONSTRAINT pk_packet_component;
    
ALTER TABLE ONLY component	
    DROP CONSTRAINT pk_component CASCADE;

ALTER TABLE ONLY packet_component
    ADD CONSTRAINT pk_packet_component PRIMARY KEY (packet_component_id);

ALTER TABLE ONLY component
    ADD CONSTRAINT pk_component PRIMARY KEY (component_id);

ALTER TABLE ONLY packet ADD COLUMN basepacket_id integer;

@@ -42,3 +57,5 @@
ALTER TABLE ONLY packet
    ADD CONSTRAINT base_packet_ref FOREIGN KEY (basepacket_id) REFERENCES basepacket(basepacket_id);
        
ALTER TABLE ONLY packet_component
    DROP COLUMN basepacket_id;

 hsarback/database/dropschema.sql

@@ -37,3 +37,4 @@
DROP TABLE business_partner CASCADE ;
DROP SEQUENCE business_partner_bp_id_seq ;
-- DROP SEQUENCE packet_component_id_seq ;
-- DROP SEQUENCE component_id_seq ;

 hsarback/src/de/hsadmin/mods/pac/BasePac.java

@@ -47,7 +47,7 @@
    @Column(name = "valid", columnDefinition = "boolean")
    private boolean valid;

    @OneToMany(fetch = FetchType.LAZY, cascade=ALL, mappedBy="basePac")
    @OneToMany(fetch = FetchType.LAZY, cascade=ALL, mappedBy="basePacket")
    private Set<Component> components;

    public long id() {

 hsarback/src/de/hsadmin/mods/pac/Component.java

@@ -1,31 +1,37 @@
package de.hsadmin.mods.pac;

import static javax.persistence.GenerationType.SEQUENCE;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity(name = "Components")
@Table(name = "component")
@IdClass(ComponentId.class)
@SequenceGenerator(name = "CompSeqGen", sequenceName = "component_id_seq")
public class Component implements Serializable {
    
    private static final long serialVersionUID = 970709621200712794L;

    @Id
    @ManyToOne
    @JoinColumn(name="basepacket_id")
    private BasePac basePac;
    @GeneratedValue(strategy = SEQUENCE, generator = "CompSeqGen")
    @Column(name = "component_id", columnDefinition = "integer")
    private long componentId;
    
    @Id
    @ManyToOne
    @JoinColumn(name="basecomponent_id")
    private BaseComponent baseComponent;
	
    @ManyToOne
    @JoinColumn(name="basepacket_id")
    private BasePac basePacket;
    
    @Column(name = "article_number", columnDefinition = "integer", nullable=false)
    private int articleNumber;
@@ -47,17 +53,6 @@

    @Column(name = "admin_only", columnDefinition = "boolean")
    private boolean adminOnly;

    public Component() {
    }

    public BasePac getBasePac() {
        return basePac;
    }

    public void setBasePac(BasePac basePac) {
        this.basePac = basePac;
    }

    public BaseComponent getBaseComponent() {
        return baseComponent;
@@ -123,4 +118,20 @@
        this.articleNumber = articleNumber;
    }

    public long getComponentId() {
        return componentId;
    }

    public void setComponentId(long componentId) {
        this.componentId = componentId;
    }

    public BasePac getBasePacket() {
        return basePacket;
    }

    public void setBasePacket(BasePac basePacket) {
        this.basePacket = basePacket;
    }

}

 hsarback/src/de/hsadmin/mods/pac/ComponentId.java

File was deleted

 hsarback/src/de/hsadmin/mods/pac/Pac.java

@@ -23,7 +23,6 @@
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Transient;

import de.hsadmin.core.model.AbstractEntity;
import de.hsadmin.core.model.AnnModuleImpl;
@@ -57,6 +56,10 @@
    @ManyToOne(fetch = EAGER)
    private Customer customer;

    @JoinColumn(name = "basepacket_id")
    @ManyToOne(fetch = EAGER)
    private BasePac basePac;

    @JoinColumn(name = "hive_id")
    @ManyToOne(fetch = EAGER)
    private Hive hive;
@@ -86,18 +89,14 @@
    @OneToMany(fetch = LAZY, cascade = ALL, mappedBy="pac")
    private Set<UnixUser> unixUser;

    @Transient
    private BasePac basepac;

    public void initPacComponents(EntityManager em, BasePac aBasepac, boolean setDefaults) {
        Query qAttachedBasepac = em.createQuery("SELECT b FROM BasePacs b WHERE b.valid = :valid AND b.name = :name");
        qAttachedBasepac.setParameter("valid", Boolean.TRUE);
        qAttachedBasepac.setParameter("name", aBasepac.getName());
        basepac = (BasePac) qAttachedBasepac.getSingleResult();
        setBasepac(basepac);
        basePac = (BasePac) qAttachedBasepac.getSingleResult();
        pacComponents = new HashSet<PacComponent>();
        Date today = new Date();
        for (Component comp : basepac.getComponents()) {
        for (Component comp : basePac.getComponents()) {
            PacComponent pacComp = new PacComponent();
            pacComp.setCreated(today);
            pacComp.setComponent(comp);
@@ -198,17 +197,11 @@
    }

    public BasePac getBasepac() {
        if (basepac == null || basepac.getName() == null) {
            Set<PacComponent> pacComps = getPacComponents();
            if (pacComps != null) {
                basepac = pacComps.iterator().next().getBasePac();
            }
        }
        return basepac;
        return basePac;
    }

    public void setBasepac(BasePac basepac) {
        this.basepac = basepac;
        this.basePac = basepac;
    }

    public Set<PacComponent> getPacComponents() {

 hsarback/src/de/hsadmin/mods/pac/PacComponent.java

@@ -1,37 +1,39 @@
package de.hsadmin.mods.pac;

import static javax.persistence.GenerationType.SEQUENCE;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.JoinColumn;
import javax.persistence.JoinColumns;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity(name = "PacComponents")
@Table(name = "packet_component")
@IdClass(PacComponentId.class)
@SequenceGenerator(name = "PacCompSeqGen", sequenceName = "packet_component_id_seq")
public class PacComponent implements Serializable {
    
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = SEQUENCE, generator = "PacCompSeqGen")
    @Column(name = "packet_component_id")
    private long pacComponentId;
	
    @ManyToOne
    @JoinColumn(name = "packet_id")
    private Pac pac;
    
    @Id
    @ManyToOne
    @JoinColumns({
        @JoinColumn(name = "basecomponent_id"),
        @JoinColumn(name = "basepacket_id")
    })
    @JoinColumn(name = "basecomponent_id")
    private Component component;

    @Column(name = "quantity", columnDefinition = "integer")
@@ -46,7 +48,7 @@
    private Date cancelled;

    public BasePac getBasePac() {
        return getComponent().getBasePac();
        return pac.getBasepac();
    }

    public BaseComponent getBaseComponent() {

 hsarback/src/de/hsadmin/mods/pac/PacComponentId.java

File was deleted

 hsarback/src/de/hsadmin/mods/pac/PacModuleImpl.java

@@ -71,7 +71,7 @@
        hive = (Hive) qHive.getSingleResult();
        pac.setHive(hive);
        hive.getPacs().add(pac);
        Query qComponents = em.createQuery("SELECT c FROM Components c WHERE c.basePac.basePacId = :basepac");
        Query qComponents = em.createQuery("SELECT c FROM Components c WHERE c.basePacket.basePacId = :basepac");
        qComponents.setParameter("basepac", basepac.id());
        List<?> componentsList = qComponents.getResultList();
        Set<PacComponent> pacComponents = new HashSet<PacComponent>();