<commit_before>begin;
drop table if exists redundant_joints;
create table redundant_joints (
    station_id   varchar(64),
    line_id      varchar(64) array,
    connected_id varchar(64) array,
    primary key (station_id)
);

/* Collect all joint stations that connect just two stations, for they
* are target for removal. */

insert into redundant_joints (station_id, line_id, connected_id)
    select station_id, array_agg(line_id) as line_id,
           array_agg(distinct connected_id) as connected_id from (
        select n.station_id, e.line_id, unnest(e.station_id) as connected_id
            from topology_nodes n
            join topology_edges e on e.line_id = any(n.line_id)
           where n.topology_name = 'joint'
    ) f where station_id != connected_id
    group by station_id having count(distinct connected_id) <= 2;

/* very much faster than the other one */
select j.station_id,
	array((select line_id from topology_edges e where e.line_id = any(j.line_id) and j.connected_id[1] = any(e.station_id))),
	array((select line_id from topology_edges e where e.line_id = any(j.line_id) and j.connected_id[2] = any(e.station_id))),
        j.connected_id
	from redundant_joints j; /* where array_length(jline_id, 1) > 1 */
/* but this one ensures that we're having both of them */
select j.station_id, array_agg(l.line_id), array_agg(r.line_id), j.connected_id
	from redundant_joints j
	join topology_edges l on l.line_id = any(j.line_id) and j.connected_id[1] = any(l.station_id)
	join topology_edges r on r.line_id = any(j.line_id) and j.connected_id[2] = any(r.station_id)
	group by j.station_id limit 100;

commit;
<commit_msg>Remove nodes from topology<commit_after>begin;
drop table if exists redundant_joints;
drop table if exists joint_edge_pair;
drop table if exists removed_nodes;
drop table if exists removed_edges;

create table redundant_joints (
    station_id   varchar(64),
    line_id      varchar(64) array,
    connected_id varchar(64) array,
    primary key (station_id)
);



create table removed_nodes (
    station_id varchar(64) primary key
);

create table removed_edges (
    line_id varchar(64) primary key
);


-- Collect all joint stations that connect just two stations, for they
-- are target for removal.

insert into redundant_joints (station_id, line_id, connected_id)
    select station_id, array_agg(line_id) as line_id,
           array_agg(distinct connected_id) as connected_id from (
        select n.station_id, e.line_id, unnest(e.station_id) as connected_id
            from topology_nodes n
            join topology_edges e on e.line_id = any(n.line_id)
           where n.topology_name = 'joint'
    ) f where station_id != connected_id
    group by station_id having count(distinct connected_id) <= 2;

/* very much faster than the other one */
/*
select j.station_id, j.connected_id
        array((select line_id from topology_edges e where e.line_id = any(j.line_id) and j.connected_id[1] = any(e.station_id))) as left_id,
        array((select line_id from topology_edges e where e.line_id = any(j.line_id) and j.connected_id[2] = any(e.station_id))) as right_id,
        from redundant_joints j where array_length(connected_id, 1) = 2;

*/
-- remove dangling joints
insert into removed_nodes (station_id)
    select station_id from redundant_joints where array_length(connected_id, 1) = 1;



-- remove nodes, maybe make this a procedure?
delete from topology_nodes where station_id in (select station_id from removed_nodes);
insert into removed_edges (line_id)
    select distinct line_id from topology_edges e join removed_nodes n on n.station_id = any(e.station_id);

delete from topology_edges where line_id in (select line_id from removed_edges);
update topology_nodes n set line_id = array_remove(n.line_id, e.line_id)
       from removed_edges e where e.line_id = any(n.line_id);

select 1/0;
commit;

{'fraction_non_alphanumeric': 0.08228011348981171, 'fraction_numerical': 0.009027598658756771, 'mean_word_length': 3.9781771501925545, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2959419', 'n_tokens_mistral': 1308, 'n_tokens_neox': 1242, 'n_words': 390}