I have two schemas of a DB, both generated by Hibernate, one in which the list of elements inside a Section is a @OneToMany relation ordered using an @OrderColumn and another in which the Section elements order is a double-linked list with references to the previous and next elements.
I am now trying to migrate data from the original DB to the new one using a pure-SQL script.
So far I am able to do this for one element of one section, but I cant generalize it for all the items nor all the sections.
(I could manually write the piece for each possible index (there are less than 20 elements per section), but not for all the sections (there are thousands)).
This is the DB Schemas
Schema 1
Annotations
@Entity
@Table(name = "elements")
public class Element {
private UUID id;
...
}
@Entity
@Table(name = "sections")
public class Section {
...
@OneToMany
@OrderColumn(name = "order")
private List<Element> elements = new ArrayList<Element>();
...
}
which generates
CREATE TABLE public.elements
(
id uuid NOT NULL,
section_id uuid,
...
}
CREATE TABLE public.sections_elements
(
section_id uuid NOT NULL,
element_id uuid NOT NULL,
order integer NOT NULL,
CONSTRAINTS ...
)
CREATE TABLE public.sections
(
id uuid NOT NULL,
...
)
Schema 2
Annotations
@Entity
@Table(name = "elements")
public class Element {
private UUID id;
@ManyToOne
private ModelSection section;
@ManyToOne
private Element beforeEl;
@ManyToOne
private Element afterEl;
...
}
@Entity
@Table(name = "sections")
public class Section {
...
(No reference to the elements)
...
}
which generates
CREATE TABLE public.elements
(
id uuid NOT NULL,
after_el_id uuid,
before_el_id uuid,
section_id uuid,
...
)
CREATE TABLE public.sections
(
id uuid NOT NULL,
...
)
This is the script that updates the before_el_id and after_el_id of the first two Elements of one Section (specified by a hard-coded id)
-- SET THE ID OF THE BEFORE ELEMENT ID OF THE ELEMENT AT INDEX 0 WITH THAT AT INDEX 1
UPDATE elements els
SET before_el_id = (
SELECT element_id
FROM OLD_DB.section_elements sec_els
JOIN elements els
ON sec_els.element_id = els.id
WHERE sec_els.order = 1
AND sec_els.sec_id = 'ac1031fa-5e4d-1452-815e-51cdc32d002f')
WHERE id = (
SELECT element_id
FROM OLD_DB.section_elements sec_els
JOIN elements els
ON sec_els.element_id = els.id
WHERE sec_els.order = 0
AND sec_els.sec_id = 'ac1031fa-5e4d-1452-815e-51cdc32d002f')
);
-- SET THE ID OF THE AFTER ELEMENT ID OF THE ELEMENT AT INDEX 1 WITH THAT AT INDEX 0
UPDATE elements els
SET before_el_id = (
SELECT element_id
FROM OLD_DB.section_elements sec_els
JOIN elements els
ON sec_els.element_id = els.id
WHERE sec_els.order = 1
AND sec_els.sec_id = 'ac1031fa-5e4d-1452-815e-51cdc32d002f')
WHERE id = (
SELECT element_id
FROM OLD_DB.section_elements sec_els
JOIN elements els
ON sec_els.element_id = els.id
WHERE sec_els.order = 0
AND sec_els.sec_id = 'ac1031fa-5e4d-1452-815e-51cdc32d002f')
);
Question
Is there a way to generalize this to, at least, update all the first Elements of the lists for all Sections?
Is there a way to generalize this to update all Elements of all Sections?
If this is not possible with plain SQL (I am actually using PostgreSQL), maybe there are some script tools that would help?
