CREATE EXTENSION IF NOT EXISTS age;

SET search_path = ag_catalog, "$user", public;


SELECT * FROM ag_catalog.create_graph('cmdb_graph');
CREATE INDEX age_edge_all_gin_idx ON cmdb_graph."_ag_label_edge" USING GIN (properties);


SELECT ag_catalog.create_elabel('cmdb_graph', 'Contain');
SELECT * FROM ag_catalog.ag_label;
CREATE INDEX age_edge_contain_gin_idx ON cmdb_graph."Contain" USING GIN (properties);
CREATE UNIQUE INDEX idx_age_edge_contain_unique_index ON cmdb_graph."Contain"(ag_catalog.agtype_access_operator(properties, '"lid"'::agtype), ag_catalog.agtype_access_operator(properties, '"rid"'::agtype));


SELECT ag_catalog.create_elabel('cmdb_graph', 'Deploy');
SELECT * FROM ag_catalog.ag_label;
CREATE INDEX age_edge_deploy_gin_idx ON cmdb_graph."Deploy" USING GIN (properties);
CREATE UNIQUE INDEX idx_age_edge_deploy_unique_index ON cmdb_graph."Deploy"(ag_catalog.agtype_access_operator(properties, '"lid"'::agtype), ag_catalog.agtype_access_operator(properties, '"rid"'::agtype));





SELECT ag_catalog.create_vlabel('cmdb_graph', 'CVM');
SELECT * FROM ag_catalog.ag_label;

CREATE INDEX gin_idx_cvm_properties ON cmdb_graph."CVM" USING GIN (properties);
CREATE UNIQUE INDEX unique_idx_cvm_id ON cmdb_graph."CVM"(ag_catalog.agtype_access_operator(properties, '"id"'::agtype));

------ generate more data -------
DO
$do$
    BEGIN
        FOR i IN 1..10000 LOOP
            EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    CREATE (any_vertex: CVM { `id`: "%s", name: "%s"} )
                    RETURN any_vertex
                $$
            ) as (any_vertex ag_catalog.agtype)',
                   i, (SELECT uuid_in(md5(random()::text || now()::text)::cstring))
                );
        END LOOP;
    END
$do$;

select count(1) from cmdb_graph."CVM" ;


explain analyze
select
    any_profile
from ag_catalog.cypher('cmdb_graph',$$
    MATCH (any_profile:`CVM` { id: '9090' })
    RETURN any_profile
$$
) as (any_profile ag_catalog.agtype);




SELECT ag_catalog.create_vlabel('cmdb_graph', 'CAS_Pool');
SELECT * FROM ag_catalog.ag_label;

CREATE INDEX gin_idx_caspool_properties ON cmdb_graph."CAS_Pool" USING GIN (properties);
CREATE UNIQUE INDEX unique_idx_caspool_id ON cmdb_graph."CAS_Pool"(ag_catalog.agtype_access_operator(properties, '"id"'::agtype));

------ generate more data -------
DO
$do$
    BEGIN
        FOR i IN 10001..30000 LOOP
            EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    CREATE (any_vertex: CAS_Pool { `id`: "%s"} )
                    RETURN any_vertex
                $$
            ) as (any_vertex ag_catalog.agtype)',
                  i
                );
        END LOOP;
    END
$do$;

select count(1) from cmdb_graph."CAS_Pool" ;

explain analyze
select
    any_profile
from ag_catalog.cypher('cmdb_graph',$$
    MATCH (any_profile:`CAS_Pool` { id: '1' })
    RETURN any_profile
$$
) as (any_profile ag_catalog.agtype);




------ generate relation -------
DO
$do$
    BEGIN
        FOR i IN 1..10000 LOOP
            EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    MATCH (d:CVM {id: "%s"}), (r:CAS_Pool {id: "%s"})
                    CREATE (d)-[:Contain {id: "%s", lid: "%s", rid: "%s", displayName: "连接", isAutoGenerate: "1", ruleId: "%s"}]->(r)
                $$
            ) as (any_vertex ag_catalog.agtype)',
                  i,i+10000,i,i,i+10000, i%100
                );
               
           EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    MATCH (d:CVM {id: "%s"}), (r:CAS_Pool {id: "%s"})
                    CREATE (d)-[:Contain {id: "%s", lid: "%s", rid: "%s", displayName: "连接", isAutoGenerate: "1", ruleId: "%s"}]->(r)
                $$
            ) as (any_vertex ag_catalog.agtype)',
                  i,i+20000,i+10000,i,i+20000, i%100
                );    
        END LOOP;
    END
$do$;
select count(1) from cmdb_graph."Contain" ;

--查询一跳关系
explain (analyze) 
SELECT * FROM cypher('cmdb_graph', $$
   MATCH p = (:CVM  {id: '10'})-[]->(a)
    RETURN relationships(p)
$$) as (r agtype);



SELECT ag_catalog.create_vlabel('cmdb_graph', 'CAS_Cluster');
SELECT * FROM ag_catalog.ag_label;

CREATE INDEX gin_idx_cascluster_properties ON cmdb_graph."CAS_Cluster" USING GIN (properties);
CREATE UNIQUE INDEX unique_idx_cascluster_id ON cmdb_graph."CAS_Cluster"(ag_catalog.agtype_access_operator(properties, '"id"'::agtype));

------ generate more data -------
DO
$do$
    BEGIN
        FOR i IN 30001..70000 LOOP
            EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    CREATE (any_vertex: CAS_Cluster { `id`: "%s"} )
                    RETURN any_vertex
                $$
            ) as (any_vertex ag_catalog.agtype)',
                  i
                );
        END LOOP;
    END
$do$;

select count(1) from cmdb_graph."CAS_Cluster" ;





------ generate relation -------
DO
$do$
    BEGIN
        FOR i IN 10001..30000 LOOP
            EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    MATCH (d:CAS_Pool {id: "%s"}), (r:CAS_Cluster {id: "%s"})
                    CREATE (d)-[:Contain {id: "%s", lid: "%s", rid: "%s", displayName: "连接", isAutoGenerate: "1", ruleId: "%s"}]->(r)
                $$
            ) as (any_vertex ag_catalog.agtype)',
                  i,i+20000,i+20000,i,i+20000, i%100
                );
               
           EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    MATCH (d:CAS_Pool {id: "%s"}), (r:CAS_Cluster {id: "%s"})
                    CREATE (d)-[:Contain {id: "%s", lid: "%s", rid: "%s", displayName: "连接", isAutoGenerate: "1", ruleId: "%s"}]->(r)
                $$
            ) as (any_vertex ag_catalog.agtype)',
                  i,i+40000,i+40000,i,i+40000, i%100
                );    
        END LOOP;
    END
$do$;
select count(1) from cmdb_graph."Contain" ;

--查询一跳关系
explain (analyze) 
SELECT * FROM cypher('cmdb_graph', $$
    MATCH p = (:CVM  {id: '10'})-[]->(a)-[]->(b)
    RETURN relationships(p)
$$) as (r agtype);




SELECT ag_catalog.create_vlabel('cmdb_graph', 'CAS_Host');
SELECT * FROM ag_catalog.ag_label;

CREATE INDEX gin_idx_cashost_properties ON cmdb_graph."CAS_Host" USING GIN (properties);
CREATE UNIQUE INDEX unique_idx_cashost_id ON cmdb_graph."CAS_Host"(ag_catalog.agtype_access_operator(properties, '"id"'::agtype));

------ generate more data -------
DO
$do$
    BEGIN
        FOR i IN 70001..150000 LOOP
            EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    CREATE (any_vertex: CAS_Host { `id`: "%s"} )
                    RETURN any_vertex
                $$
            ) as (any_vertex ag_catalog.agtype)',
                  i
                );
        END LOOP;
    END
$do$;

select count(1) from cmdb_graph."CAS_Host" ;


------ generate relation -------

create or replace procedure "ag_catalog"."createRelation"() 
as $do$
declare 
    i integer := 1;
BEGIN
    FOR i IN 30001..70000 
    loop
        EXECUTE format('
            select * from ag_catalog.cypher(''cmdb_graph'',
            $$
                MATCH (d:CAS_Cluster {id: "%s"}), (r:CAS_Host {id: "%s"})
                CREATE (d)-[:Contain {id: "%s", lid: "%s", rid: "%s", displayName: "连接", isAutoGenerate: "1", ruleId: "%s"}]->(r)
            $$
        ) as (any_vertex ag_catalog.agtype)',
              i,i+40000,i+40000,i,i+40000, i%100
            );
           
        EXECUTE format('
            select * from ag_catalog.cypher(''cmdb_graph'',
            $$
                MATCH (d:CAS_Cluster {id: "%s"}), (r:CAS_Host {id: "%s"})
                CREATE (d)-[:Contain {id: "%s", lid: "%s", rid: "%s", displayName: "连接", isAutoGenerate: "1", ruleId: "%s"}]->(r)
            $$
        ) as (any_vertex ag_catalog.agtype)',
              i,i+80000,i+80000,i,i+80000, i%100
            );    
           
        commit;       
    END LOOP;
return;   
end $do$ language plpgsql;

call "ag_catalog"."createRelation"() ;







SELECT ag_catalog.create_vlabel('cmdb_graph', 'CAS_VM');
SELECT * FROM ag_catalog.ag_label;

CREATE INDEX gin_idx_casvm_properties ON cmdb_graph."CAS_VM" USING GIN (properties);
CREATE UNIQUE INDEX unique_idx_casvm_id ON cmdb_graph."CAS_VM"(ag_catalog.agtype_access_operator(properties, '"id"'::agtype));

------ generate more data -------
DO
$do$
    BEGIN
        FOR i IN 150001..310000 LOOP
            EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    CREATE (any_vertex: CAS_VM { `id`: "%s"} )
                    RETURN any_vertex
                $$
            ) as (any_vertex ag_catalog.agtype)',
                  i
                );
        END LOOP;
    END
$do$;

select count(1) from cmdb_graph."CAS_VM" ;

create or replace procedure "ag_catalog"."createRelation"() 
as $do$
declare 
    i integer := 1;
BEGIN
    FOR i IN 70001..150000 LOOP
        EXECUTE format('
            select * from ag_catalog.cypher(''cmdb_graph'',
            $$
                MATCH (d:CAS_Host {id: "%s"}), (r:CAS_VM {id: "%s"})
                CREATE (d)-[:Contain {id: "%s", lid: "%s", rid: "%s", displayName: "连接", isAutoGenerate: "1", ruleId: "%s"}]->(r)
            $$
        ) as (any_vertex ag_catalog.agtype)',
              i,i+80000,i+80000,i,i+80000, i%100
            );
           
       EXECUTE format('
            select * from ag_catalog.cypher(''cmdb_graph'',
            $$
                MATCH (d:CAS_Host {id: "%s"}), (r:CAS_VM {id: "%s"})
                CREATE (d)-[:Contain {id: "%s", lid: "%s", rid: "%s", displayName: "连接", isAutoGenerate: "1", ruleId: "%s"}]->(r)
            $$
        ) as (any_vertex ag_catalog.agtype)',
              i,i+160000,i+160000,i,i+160000, i%100
            );  
       commit;           
    END LOOP;
return;   
end $do$ language plpgsql;

call "ag_catalog"."createRelation"() ; 



SELECT ag_catalog.create_vlabel('cmdb_graph', 'Server');
SELECT * FROM ag_catalog.ag_label;

CREATE INDEX gin_idx_server_properties ON cmdb_graph."Server" USING GIN (properties);
CREATE UNIQUE INDEX unique_idx_server_id ON cmdb_graph."Server"(ag_catalog.agtype_access_operator(properties, '"id"'::agtype));

------ generate more data -------
DO
$do$
    BEGIN
        FOR i IN 1000001..1010000 LOOP
            EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    CREATE (any_vertex: Server { `id`: "%s", name: "%s"} )
                    RETURN any_vertex
                $$
            ) as (any_vertex ag_catalog.agtype)',
                   i, (SELECT uuid_in(md5(random()::text || now()::text)::cstring))
                );
        END LOOP;
    END
$do$;


------ generate relation -------
DO
$do$
    BEGIN
        FOR i IN 1000001..1010000 LOOP
            EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    MATCH (d:Server {id: "%s"}), (r:CVM {id: "%s"})
                    CREATE (d)-[:Deploy {id: "%s", lid: "%s", rid: "%s", displayName: "连接", isAutoGenerate: "1", ruleId: "%s"}]->(r)
                $$
            ) as (any_vertex ag_catalog.agtype)',
                  i,i-1000000,i,i,i-1000000, i%100
                );
        END LOOP;
    END
$do$;
DO
$do$
    BEGIN
        FOR i IN 1000001..1010000 LOOP
            EXECUTE format('
                select * from ag_catalog.cypher(''cmdb_graph'',
                $$
                    MATCH (d:CVM {id: "%s"}), (r:Server {id: "%s"})
                    CREATE (d)-[:Deploy {id: "%s", lid: "%s", rid: "%s", displayName: "连接", isAutoGenerate: "1", ruleId: "%s"}]->(r)
                $$
            ) as (any_vertex ag_catalog.agtype)',
                  i-1000000,i,i+10000,i-1000000,i,i%100
                );
        END LOOP;
    END
$do$;
select count(1) from cmdb_graph."Deploy" ;























SELECT * FROM cypher('cmdb_graph', $$
    MATCH p = (:CVM  {id: '10'})-[*2]-()
    unwind nodes(p) as n
      with p, size(collect(distinct n)) as testLength
    where testLength = LENGTH(p) + 1
    RETURN relationships(p)
$$) as (r agtype);