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 <[email protected]>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 < > [email protected]> 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: [email protected] >> tel:+260968395190 >> > > > > -- > Hieu.HISPVietnam > Good Health ! > -- -- Jason P. Pickering email: [email protected] tel:+260968395190
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
