dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #03801
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 1259: Improved sql to decompose datavalue table to EAV dataset for a given dataelement, period, source, ...
------------------------------------------------------------
revno: 1259
committer: Jason Pickering <jason@jason-laptop>
branch nick: dhis2
timestamp: Mon 2009-12-21 17:09:30 +0200
message:
Improved sql to decompose datavalue table to EAV dataset for a given dataelement, period, source, catcombooption id combination. Added
seperate function for creation of a EAV dataset for a given orgunit. This datatable can then be fed into Postgres crosstab function for creation of arbitrary crosstab tables.
modified:
resources/sql/create_eav_dataset.sql
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription.
=== modified file 'resources/sql/create_eav_dataset.sql'
--- resources/sql/create_eav_dataset.sql 2009-12-19 12:06:29 +0000
+++ resources/sql/create_eav_dataset.sql 2009-12-21 15:09:30 +0000
@@ -1,85 +1,80 @@
+DROP TYPE IF EXISTS eav_text CASCADE;
+CREATE TYPE eav_text AS (objectid bigint, attribute text, "value" text);
+
+-- Sequence: datavalueid
+
+-- DROP SEQUENCE datavalueid;
+
+CREATE SEQUENCE datavalueid
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 2830543
+ CACHE 1;
+ALTER TABLE datavalueid OWNER TO postgres;
+
+
+
-- Function: create_eav_datavalue(integer, integer, integer, integer)
---Returns an (objectid, attribute, value) triplet for a given
--- dataelement and sourceid
--- DROP FUNCTION create_eav_datavalueset(integer, integer, integer, integer);
--- CREATE TYPE eav_text as (objectid integer, attribute text, "value" text);
-
-CREATE OR REPLACE FUNCTION create_eav_datavalueset(mydataelementid integer, mysourceid integer)
+
+-- DROP FUNCTION create_eav_datavalue(integer, integer, integer, integer);
+
+CREATE OR REPLACE FUNCTION create_eav_datavalue(mydataelementid integer, myperiodid integer, mysourceid integer, mycategoryoptioncomboid integer)
RETURNS SETOF eav_text AS
$BODY$
DECLARE
-this_objectid integer DEFAULT 0;
-periods record;
-categoryoptioncomboids record;
+this_objectid bigint DEFAULT 0;
+rec record;
+total_records integer DEFAULT 0;
BEGIN
- EXECUTE 'DROP TABLE IF EXISTS _eav_dataset';
- EXECUTE 'CREATE TABLE _eav_dataset ( objectid integer, attribute text, "value" text)';
-
-FOR periods IN
-
-SELECT DISTINCT periodid FROM datavalue where dataelementid = mydataelementid AND
- sourceid = mysourceid LOOP
- FOR categoryoptioncomboids IN
- SELECT DISTINCT categoryoptioncomboid FROM datavalue
- where dataelementid = mydataelementid
- LOOP
-
-EXECUTE 'INSERT INTO _eav_dataset (objectid, attribute, "value")'
- || 'SELECT '
- || this_objectid
- || ', ''dataelementname''::text, "name"::text'
- || ' FROM dataelement where dataelementid = '
- || mydataelementid
- || ' UNION'
- || ' SELECT '
- || this_objectid
- || ', ''startdate''::text, startdate::text FROM period where periodid = '
- || periods.periodid
- || ' UNION SELECT '
- || this_objectid
- ||', ''enddatte''::text, enddate::text '
- || ' FROM period where periodid = '
- || periods.periodid
- ||' UNION'
- ||' SELECT '
- || this_objectid
- || ', ''orgunitname''::text, "name"::text'
- || ' FROM organisationunit where organisationunitid = '
- || mysourceid
- || ' UNION '
- || ' SELECT '
- || this_objectid
- || ', ''value''::text, "value"::text'
- || ' FROM datavalue where sourceid = '
- || mysourceid
- || ' AND periodid = '
- || periods.periodid
- || 'AND dataelementid = '
- || mydataelementid
- || ' AND categoryoptioncomboid = '
- || categoryoptioncomboids.categoryoptioncomboid
- || 'UNION '
- || ' SELECT '
- || this_objectid
- || ' , dataelementcategory.name, dataelementcategoryoption.name
- FROM categories_categoryoptions
- INNER JOIN dataelementcategory ON
- dataelementcategory.categoryid = categories_categoryoptions.categoryid
- INNER JOIN dataelementcategoryoption ON
- dataelementcategoryoption.categoryoptionid = categories_categoryoptions.categoryoptionid
- WHERE categories_categoryoptions.categoryoptionid IN
- (SELECT categoryoptionid FROM categoryoptioncombos_categoryoptions
- where categoryoptioncomboid = '
- || categoryoptioncomboids.categoryoptioncomboid ||')'
-
- ;
- this_objectid := this_objectid + 1;
- END LOOP;
-
-END LOOP;
+this_objectid := 1;
+FOR rec in ( SELECT COUNT(value) as myvalue, nextval('datavalueid'::regclass) as myobjectid FROM datavalue
+ where dataelementid = mydataelementid
+ AND periodid = myperiodid AND sourceid = mysourceid AND
+ categoryoptioncomboid = mycategoryoptioncomboid)
+ LOOP
+ total_records := rec.myvalue;
+ this_objectid := rec.myobjectid;
+ END LOOP;
+
+
+
+ IF total_records = 1 THEN
+FOR rec in (
+SELECT this_objectid, 'dataelementname'::text, "name"::text
+ FROM dataelement where dataelementid = mydataelementid
+UNION
+SELECT this_objectid, 'startdate'::text, startdate::text
+ FROM period where periodid = myperiodid
+ UNION
+SELECT this_objectid, 'enddatte'::text, enddate::text
+ FROM period where periodid = myperiodid
+UNION
+SELECT this_objectid, 'orgunitname'::text, "name"::text
+FROM organisationunit where organisationunitid = mysourceid
+UNION
+SELECT this_objectid, 'value'::text, "value"::text
+FROM datavalue where sourceid = mysourceid
+AND periodid = myperiodid AND dataelementid = mydataelementid
+AND categoryoptioncomboid = mycategoryoptioncomboid
+UNION
+SELECT this_objectid, dataelementcategory.name, dataelementcategoryoption.name FROM categories_categoryoptions
+INNER JOIN dataelementcategory ON dataelementcategory.categoryid = categories_categoryoptions.categoryid
+INNER JOIN dataelementcategoryoption ON dataelementcategoryoption.categoryoptionid = categories_categoryoptions.categoryoptionid
+WHERE categories_categoryoptions.categoryoptionid IN
+(SELECT categoryoptionid FROM categoryoptioncombos_categoryoptions where categoryoptioncomboid = mycategoryoptioncomboid ))
+
+
+ LOOP
+ RETURN NEXT rec;
+ END LOOP;
+
+ ELSE
+ END IF;
+
END;
@@ -87,6 +82,67 @@
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
- ROWS 1000;
+ ROWS 1;
ALTER FUNCTION create_eav_datavalue(integer, integer, integer, integer) OWNER TO postgres;
+
+-- Function: create_eav_orgunit(integer)
+
+-- DROP FUNCTION create_eav_orgunit(integer);
+
+CREATE OR REPLACE FUNCTION create_eav_orgunit(mysourceid integer)
+ RETURNS integer AS
+$BODY$
+
+DECLARE
+periods record;
+dataelements record;
+catoptions record;
+
+BEGIN
+
+ EXECUTE 'DROP TABLE IF EXISTS _eav_dataset_' || mysourceid::text;
+ EXECUTE 'CREATE TABLE _eav_dataset_'
+ || mysourceid::text
+ ||'( objectid integer, attribute text, "value" text)';
+
+
+FOR periods in
+ SELECT DISTINCT periodid from datavalue where sourceid = mysourceid LOOP
+ FOR dataelements in
+ SELECT DISTINCT dataelementid from datavalue where sourceid = mysourceid AND periodid = periods.periodid LOOP
+
+ FOR catoptions IN
+ SELECT DISTINCT categoryoptioncomboid from datavalue where
+ dataelementid = dataelements.dataelementid
+ AND sourceid = mysourceid
+ AND periodid = periods.periodid LOOP
+
+ EXECUTE '
+ INSERT INTO _eav_dataset_'
+ || mysourceid::text
+ || '(objectid, attribute, "value")
+ SELECT * FROM
+ create_eav_datavalue('
+ || dataelements.dataelementid
+ || ','
+ || periods.periodid
+ || ','
+ || mysourceid
+ || ','
+ || catoptions.categoryoptioncomboid
+ || ')';
+ END LOOP;
+ END LOOP;
+ END LOOP;
+
+ RETURN 1;
+
+END;
+
+
+ $BODY$
+ LANGUAGE 'plpgsql' VOLATILE
+ COST 100;
+ALTER FUNCTION create_eav_orgunit(integer) OWNER TO postgres;
+
