dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Yes, exactly. :)

But not to get distracted...

What I wanted to highlight was that the DHIS 1.4 data integrity checks
functionality allows predefined queries to be stored in a table, thus making
the data integrity checks extendable.

What I am proposing with the regular expression validation would be the
dynamic contsruction and execution of queries, based on the contents of
those two objects/tables we have been discussing.

 Java would be used to construct the query dynamically based on the the
database system being used. Postgres, MySQL and H2 all have slightly
different syntax when it comes to regular expressions, which are not ANSI
SQL compatible anyway. Hibernate, as far as I can tell, does not support
regular expressions directly, so I think a pass through query would need to
be used in this case.





On Mon, Mar 29, 2010 at 10:20 AM, Hieu Dang Duy
<hieu.hispvietnam@xxxxxxxxx>wrote:

>
> OK, here is the re-construction of the statement query instance which is
> modified for who wanna execute it following DHIS2's contruction in
> PostgresQL.
>
> *
> **SELECT OrgUnit.Name AS OrgUnit, OrgUnitGroupSet.Name AS GroupSet,
> Count(OrgUnitGroupSet.OrgUnitGroupSetID) AS "Group Set Allocations"
> FROM OrganisationUnit AS
>            OrgUnit INNER JOIN (
>            OrgUnitGroupSet INNER JOIN (
>            OrgUnitGroupSetMembers INNER JOIN
>            OrgUnitGroupMembers
>            ON OrgUnitGroupSetMembers.OrgUnitGroupID =
> OrgUnitGroupMembers.OrgUnitGroupID)
>            ON OrgUnitGroupSet.OrgUnitGroupSetID =
> OrgUnitGroupSetMembers.OrgUnitGroupSetID)
>            ON OrgUnit.OrganisationUnitID =
> OrgUnitGroupMembers.OrganisationUnitID
> WHERE OrgUnitGroupSet.Exclusive = true
> GROUP BY OrgUnit.Name, OrgUnitGroupSet.Name
>
> HAVING Count(OrgUnitGroupSet.OrgUnitGroupSetID) > 1
> ORDER BY OrgUnit.Name;
>
> *
> On Mon, Mar 29, 2010 at 2:44 PM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
>> Hi again,
>>
>> If you want, you can download a copy of DHIS 1.4 from hispkerala.org and
>> take  a look at the DataIntegrityCheck table. For instance, the rule
>> "OrgUnit Group Set Exclusive Violation"
>>
>> is defined as
>>
>> SELECT OrgUnit.OrgUnitName AS OrgUnit, OrgUnitGroupSet.OrgUnitGroupSetName
>> AS GroupSet, Count(OrgUnitGroupSet.OrgUnitGroupSetID) AS [Group Set
>> Allocations]
>> FROM OrgUnit INNER JOIN (OrgUnitGroupSet INNER JOIN (OrgUnitGroupSetMember
>> INNER JOIN OrgUnitGroupMember ON OrgUnitGroupSetMember.OrgUnitGroupID =
>> OrgUnitGroupMember.OrgUnitGroupID) ON OrgUnitGroupSet.OrgUnitGroupSetID =
>> OrgUnitGroupSetMember.OrgUnitGroupSetID) ON OrgUnit.OrgUnitID =
>> OrgUnitGroupMember.OrgUnitID
>> WHERE OrgUnitGroupSet.OrgUnitGroupSetExclusive=1 AND
>> OrgUnitGroupMember.Active = 1
>> GROUP BY OrgUnit.OrgUnitName, OrgUnitGroupSet.OrgUnitGroupSetName
>> HAVING Count(OrgUnitGroupSet.OrgUnitGroupSetID)>1
>> ORDER BY OrgUnit.OrgUnitName;
>>
>> The data integrity check functionality in 1.4 is essentially exactly the
>> same as DHIS2, execept it is possible to extend the data integrity checks by
>> adding new SQL statements in the 1.4 database. I would really like this see
>> this feature in 2.0, as right now, all of the rules have been "hard-coded"
>> in Java, and are not extendible, except by modification of the source code
>> (Lars correct me if I am wrong). I expect that we could do it the 1.4 way,
>> by using ANSI compatible queries that would work across Postgres, MySQL and
>> H2. Anyway, that is another blueprint I think.
>>
>> Regards,
>> Jason
>>
>>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+260968395190
>>
>
>
>
> --
> Hieu.HISPVietnam
> Good Health !
>



-- 
--
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+260968395190