ODBC: Replace om_copy_element() - drupal module function to create a copy of a nested property object

[rburghol]

See also: #434
Tagging @COBrogan @BrendanBrogan @gmahadwar
See also, saving models from VAHydro to om #446

• Options:
  • SQL version (using RECURSIVE query, and temp tables)
  • R Version: similar to old php version
• SQL
  • Very fast
  • Uses recursive query to grab entire object.
  • Prototype:
    • http://deq1.bse.vt.edu:81/d.dh/om-model-info/7692137
    • pid = 7692137
  • Goals:
    • Insert into dh_properties, preserving a list of the new pids, and previous pids
    • Update featureid for all new elements with the new pid corresponding with the old one
    • To update featureid, we need to know what the new pid of the entity that was previously the featureid of a given new property
      • create a column clone_pid in dh_properties to stash the original pid when cloning? alter table dh_properties add column clone_pid bigint;
      • Then use src_pid as map to update featureids, where we use a RETURNING src_pid, pid in the statement that inserts the clone into dh_properties

Shell Script

om_copy_element Uses SQL

• Use: om_copy_property src_entity_type src_featureid dest_entity_type dest_featureid propname[propname|newname] [include_children=1] [overwrite=0] [debug=0]
• Ex: om_copy_property dh_properties 6403272 dh_properties 4823737 available_mgd 1 1

SQL Development

Get root_pid by propname and parent

\set template_pid 6717035
\set template_entity_type 'dh_properties'
\set propname 'Reservoir Operations'
select pid as root_pid from dh_properties where featureid = :template_pid and entity_type = :'template_entity_type' and propname = :'propname' \gset

Set up arguments

\set root_pid 6541489
\set include_children 1
\set dest_parent 480505
\set dest_name 'South Fork Powell River at East Stone Gap'
\set dest_entity_type 'dh_feature'

Do the base copy

BEGIN;

create temp table ptemp as select * from dh_properties_fielded limit 0;

WITH RECURSIVE prop_tree AS (
      SELECT p.*
      FROM dh_properties_fielded as p 
      WHERE p.pid = :root_pid 
      UNION
      SELECT c.*
      FROM dh_properties_fielded as c 
      inner join prop_tree as p
      on (c.featureid = p.pid and c.entity_type = 'dh_properties' and :include_children = 1)
    )
INSERT INTO ptemp SELECT * from prop_tree;

create temp table map_clone_pids (pid BIGINT, clone_pid BIGINT);

with rows as (
  INSERT INTO dh_properties(featureid, entity_type, bundle, module, status, 
    propname, propcode, propvalue, varid, 
    modified,startdate, enddate, clone_pid, vid, uid
  ) 
  SELECT featureid, entity_type, bundle, module, status, 
    propname, propcode, propvalue, varid, 
    modified,startdate, enddate, pid, vid, uid 
  FROM ptemp RETURNING pid, clone_pid
)
INSERT INTO map_clone_pids (pid, clone_pid)
SELECT pid, clone_pid
FROM rows ;

-- Now, connect to the newly created parent, the new parent will be the 
update dh_properties set featureid = map_clone_pids.pid from map_clone_pids 
where dh_properties.featureid = map_clone_pids.clone_pid 
and dh_properties.entity_type = 'dh_properties' 
and dh_properties.pid in (select pid from map_clone_pids);

update dh_properties set featureid = :dest_parent, propname = :'dest_name' 
where pid in (select pid from map_clone_pids where clone_pid = :root_pid);

insert into dh_properties_revision(featureid, entity_type, bundle, module, status, 
    propname, propcode, propvalue, varid, 
    modified,startdate, enddate, pid, uid
  ) 
select featureid, entity_type, bundle, module, status, 
    propname, propcode, propvalue, varid, 
    modified,startdate, enddate, pid, uid 
from dh_properties where pid in (select pid from map_clone_pids);

update dh_properties set vid = a.vid 
from (
  select a.pid, b.vid 
  from map_clone_pids as a
  left outer join dh_properties_revision as b
  on (a.pid = b.pid)
  where b.vid is not null
) as a
where dh_properties.pid = a.pid
; 

-- Copy proptext

insert into field_data_proptext(entity_type , bundle, deleted, entity_id, revision_id, language, delta, proptext_value, proptext_format)
select a.entity_type , a.bundle, a.deleted, b.pid, a.revision_id, a.language, a.delta, a.proptext_value, a.proptext_format
from dh_properties as b, field_data_proptext as a, map_clone_pids as c
WHERE a.entity_id in (select clone_pid from map_clone_pids)
  and a.entity_id = b.clone_pid
  and a.entity_type = 'dh_properties' 
  and b.pid = c.pid 
  and b.pid not in (
     select entity_id from field_data_proptext
     where entity_id in (select pid from map_clone_pids) 
     and entity_type = 'dh_properties'
  ) 
;

-- Copy normal matrices

insert into field_data_field_dh_matrix (entity_type , bundle, deleted, entity_id, revision_id, language, delta, field_dh_matrix_value, field_dh_matrix_format)
select a.entity_type , a.bundle, a.deleted, b.pid, a.revision_id, a.language, a.delta, a.field_dh_matrix_value, a.field_dh_matrix_format
from map_clone_pids as c, dh_properties as b, field_data_field_dh_matrix as a
WHERE a.entity_id in (select clone_pid from map_clone_pids)
  and a.entity_id = b.clone_pid
  and a.entity_type = 'dh_properties' 
  and b.pid = c.pid 
  and b.pid not in (
     select entity_id from field_data_field_dh_matrix 
     where entity_id in (select pid from map_clone_pids) 
     and entity_type = 'dh_properties'
  ) 
;
-- Copy WSP projection matrices
-- This is NA for model nested properties, but for a single WSP property it is relevant

insert into field_data_field_projection_table (entity_type , bundle, deleted, entity_id, revision_id, language, delta, field_projection_table_value, field_projection_table_format)
select a.entity_type , a.bundle, a.deleted, b.pid, a.revision_id, a.language, a.delta, a.field_projection_table_value, a.field_projection_table_format
from map_clone_pids as c, dh_properties as b, field_data_field_projection_table as a
WHERE a.entity_id in (select clone_pid from map_clone_pids)
  and a.entity_id = b.clone_pid
  and a.entity_type = 'dh_properties' 
  and b.pid = c.pid 
  and b.pid not in (
     select entity_id from field_data_field_projection_table 
     where entity_id in (select pid from map_clone_pids) 
     and entity_type = 'dh_properties'
  ) 
;

COMMIT;

[rburghol]

Test with Runit_mode (clone to same parent)

\set root_pid 6541948
\set include_children 1
\set dest_parent 6541489
\set dest_name 'Test Copy'

• check the matches:

select dh_properties.clone_pid, dh_properties.pid, 
  propname, propvalue, propcode, varid, featureid 
from dh_properties, map_clone_pids 
where dh_properties.featureid = map_clone_pids.clone_pid 
and dh_properties.entity_type = 'dh_properties' 
and dh_properties.pid in (select pid from map_clone_pids) 
order by dh_properties.clone_pid;

[rburghol]

Test that ptemp has all the data it needs:

WITH RECURSIVE prop_tree AS (
      SELECT p.*
      FROM ptemp as p 
      WHERE p.pid = :root_pid 
      UNION
      SELECT c.*
      FROM ptemp as c 
      inner join prop_tree as p
      on (c.featureid = p.pid and c.entity_type = 'dh_properties' and :include_children = 1)
    )
SELECT * from prop_tree;

select a.entity_type , a.bundle, a.deleted, b.pid, a.revision_id, a.language, a.delta, a.proptext_value, a.proptext_format
from map_clone_pids as c left outer join dh_properties as b
on (
  b.pid = c.pid 
)
left outer join field_data_proptext as a
on ( 
  a.entity_id = b.clone_pid
  and a.entity_type = 'dh_properties' 
) 
where a.entity_id is not null 
  and b.pid not in (
     select entity_id from field_data_proptext
     where entity_id in (select pid from dh_properties ) 
     and entity_type = 'dh_properties'
  ) 
;

[rburghol]

"Reservoir Operations"

Get root_pid by propname and parent

\set template_pid 6717035
\set template_entity_type 'dh_properties'
\set propname 'Reservoir Operations'
select pid as root_pid from dh_properties where featureid = :template_pid and entity_type = :'template_entity_type' and propname = :'propname' \gset

Set up arguments

\set include_children 1
\set dest_parent 4823737
\set dest_name 'Reservoir Operations'

[rburghol]

Verify data matrix copying fac_demand_mgy

• already copied, but clone lacked data_matrix
• copy again
• om_copy_property dh_properties 4823737 dh_properties 7693370 fac_demand_mgy 1 1
• NOPE