D-Net

     * Create charts.

     *

     * @throws Exception

     */

    public void createCharts() throws Exception {

        log.info(" Creating Chart Tables...");

        Connection con = statsDatasource.getConnection();

        startTime = System.currentTimeMillis();

        String q = "{call create_charts()}";

        CallableStatement st = con.prepareCall(q);

        st.execute();

        st.close();

        con.close();

        endtime = System.currentTimeMillis();

        log.info("Time to create chart tables: " + ((endtime - startTime) / 60000) + " minutes ");

    }

    /**

     * Create chart indexes.

     *

     * @throws Exception

     */

    public void createChartIndexes() throws Exception {

        log.info(" Create Chart Indexes...");

        Connection con = statsDatasource.getConnection();

        startTime = System.currentTimeMillis();

        String q = "{call create_chart_indexes()}";

        CallableStatement st = con.prepareCall(q);

        st.execute();

        st.close();

        con.close();

        endtime = System.currentTimeMillis();

        log.info("Time to create chart indexes : " + ((endtime - startTime) / 60000) + " minutes ");

    }

    /**

            statsDao.createCharts();

            statsDao.createChartIndexes();

--

-- Name: create_charts(); Type: FUNCTION; Schema: shadow; Owner: sqoop

--

CREATE or replace FUNCTION "shadow".create_charts() RETURNS void

    LANGUAGE plpgsql

    AS $$

BEGIN

SELECT rd.datasource, rc.type, count(distinct rd.id) INTO "shadow".chart_datasource_pubs FROM "shadow".result_datasources rd, "shadow".result_classifications rc WHERE rd.id=rc.id GROUP BY rd.datasource, rc.type;

SELECT rd.datasource, r.year, count(distinct rd.id) INTO "shadow".chart_datasource_year FROM "shadow".result r, "shadow".result_datasources rd WHERE rd.id=r.id GROUP By rd.datasource, r.year;

SELECT rd.datasource, p.funder, count(distinct rd.id) INTO "shadow".chart_datasource_funders FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr WHERE p.id=pr.id AND pr.result=rd.id GROUP BY rd.datasource, p.funder;

SELECT rd.datasource, p.title, count(distinct rd.id) INTO "shadow".chart_datasource_projects FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr WHERE p.id=pr.id AND pr.result=rd.id GROUP BY rd.datasource, p.title;

  END;$$;

-- Name: create_charts(); Type: FUNCTION; Schema: shadow; Owner: sqoop

--

CREATE or replace FUNCTION "shadow".create_chart_indexes() RETURNS void

    LANGUAGE plpgsql

    AS $$

BEGIN

CREATE INDEX chart_datasource_pubs_datasource ON "shadow".chart_datasource_pubs USING btree(datasource);

CREATE INDEX chart_datasource_year_datasource ON "shadow".chart_datasource_year USING btree(datasource);

CREATE INDEX chart_datasource_funders_datasource ON "shadow".chart_datasource_funders USING btree(datasource);

CREATE INDEX chart_datasource_projects_datasource ON "shadow".chart_datasource_projects USING btree(datasource);

  END;$$;

--