HSAdmin Backend Domains, E-Mail, Datenbanken
Peter Hormanns
2013-05-13 522b89c5372746a79c500578967c3e8ba276320a
refactoring of database on pac-components
9 files modified
2 files deleted
232 ■■■■■ changed files
hsarback/build.xml 6 ●●●●● patch | view | raw | blame | history
hsarback/database/data.sql 12 ●●●● patch | view | raw | blame | history
hsarback/database/database_update.sql 27 ●●●● patch | view | raw | blame | history
hsarback/database/dropschema.sql 1 ●●●● patch | view | raw | blame | history
hsarback/src/de/hsadmin/mods/pac/BasePac.java 2 ●●● patch | view | raw | blame | history
hsarback/src/de/hsadmin/mods/pac/Component.java 45 ●●●●● patch | view | raw | blame | history
hsarback/src/de/hsadmin/mods/pac/ComponentId.java 51 ●●●●● patch | view | raw | blame | history
hsarback/src/de/hsadmin/mods/pac/Pac.java 23 ●●●●● patch | view | raw | blame | history
hsarback/src/de/hsadmin/mods/pac/PacComponent.java 20 ●●●●● patch | view | raw | blame | history
hsarback/src/de/hsadmin/mods/pac/PacComponentId.java 43 ●●●●● patch | view | raw | blame | history
hsarback/src/de/hsadmin/mods/pac/PacModuleImpl.java 2 ●●● patch | view | raw | blame | history
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,20 +25,37 @@
    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;
UPDATE packet SET basepacket_id = ( SELECT basepacket_id FROM packet_component 
        WHERE packet_component.packet_id = packet.packet_id LIMIT 1 );
    WHERE packet_component.packet_id = packet.packet_id LIMIT 1 );
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>();