dhis2-devs team mailing list archive

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

Just forwarding a side discussion with Hieu to the list for discussion
and consideration.

Input welcome.

Regards,
Jason



---------- Forwarded message ----------
From: Jason Pickering <jason.p.pickering@xxxxxxxxx>
Date: Mon, Feb 8, 2010 at 9:25 PM
Subject: Re: [Dhis2-devs] Regular expressions in data validation rules
To: Hieu Dang Duy <hieu.hispvietnam@xxxxxxxxx>


Fantastic. Please review
https://blueprints.launchpad.net/dhis2/+spec/regex-validation
carefully. I have indicated to persistence objects which I think would
be necessary.

The first, would be a table that would store the regex itself. The can
be a one-to-many relationship between regexes and other objects. So,
the first table would simple store the regex itself, and a few other
properties.

So, to create that example query (Detect trailing spaces in names) we
would have a persistence object like this..
a)regexid (taken from hibernate_sequence)
b)name (Detect trailing spaces in names)
c)expression ('\s+$')
d)description('Trailing spaces should not be allowed.')
e)resolution('The following objects have trailing spaces. Please
delete them from the appropriate field')

OK, so that would basically store the regex itself.

Now, another table would store the particulars of what we want to
apply the regex to.

a) regexid (same as the hibernate sequence)
b) table (organisationunit)
c) field (name)
d) negate (false)
e) caseinsentive (defaults to false)

So, we would need two dialogs. One to allow a user to define the
regex, the other to assign a particular regex to a particular field.

Now, this is where I need help. I know the SQL that will return the
record set we want.

SELECT organisationunitid, name from organisationunit where name ~('\s+$')

If we were to select the negate field as TRUE, then the query would be
SELECT organisationunitid, name from organisationunit where name !~('\s+$')

If we select case insensitve the query would be (which of course in
this case is meaningless)
SELECT organisationunitid, name from organisationunit where name !~*('\s+$')



This is Postgresql dialect.The key question for me is how do we make
it database independent? Do we return a recordset from the
organisationunit table, apply a regex with java.util.regex or can we
just push it back to the DB? Both Postres, MySQL and H2 seem to
support this (MySQL uses RLIKE or REGEXP and H2 uses LIKE while
postgres uses ~ ) So, we could create a query and then just execute it
directly on the DB. Otherwise, I am not sure how to do it directly
with Hibernate. Perhaps it is easy, but I reviewed a few pages from
Google, and there did not seem to be a clear answer.

In the end however, the result set would then need to be returned to
the user through during a data integrity check, with the list of
fields that violate the rule, similar to what happens now.

I think this is really incredibly important, and the example I have
given thus far is really just a trivial example. I am sure there are
many more situations where this would come in handy.

Anyway, let me know what you think.

Best regards,
Jason


On Mon, Feb 8, 2010 at 8:39 PM, Hieu Dang Duy
<hieu.hispvietnam@xxxxxxxxx> wrote:
> Dear Jason,
>
> I would like to work with u on this blueprint. As your previous mail, you
> make me open my eyes really at using RegEx in sql query. Absolutely, I've
> never seen this query like that before. I had try running your example on
> orgunit table. Amazing, it ran fine.
>
> Plz show off to me which things what I have to do for u?
> Hopefully that, I will try to help you test this if I can.
>
> On Mon, Feb 8, 2010 at 11:25 PM, Jason Pickering
> <jason.p.pickering@xxxxxxxxx> wrote:
>>
>> Hi there Hieu,
>>
>> I was hoping maybe to work with you on this. I have no idea how to
>> code Java really, but I understand the code fairly well if you can
>> point me in the right direction. I  can certainly  help you with the
>> specs. I know exactly what needs to be done I think (see the
>> blueprint) and can help with the testing and regex definitions.
>>
>> I think we should start with the data integrity checks. This seems
>> like the quickest win.
>>
>> Do you have any time?
>>
>> Best regards,
>> Jason
>>
>>
>> On Sun, Feb 7, 2010 at 7:36 PM, Hieu Dang Duy
>> <hieu.hispvietnam@xxxxxxxxx> wrote:
>> > Hi all,
>> >
>> > I've no idea about using RegEx for validating data in DHIS2. Just a
>> > small
>> > comment, I am also using this many times so my feeling on this is not
>> > easy
>> > but not too difficult when applying RegEx in your coding, ie, javascript
>> > and
>> > java also.
>> > With RegEx, we can easy controlling any thing that we want to force the
>> > user
>> > for entering data (text, number) or something else (a file name is an
>> > example).
>> > Let's try !
>> >
>> > Thanks !
>> >
>> > On Sun, Feb 7, 2010 at 10:24 PM, Jason Pickering
>> > <jason.p.pickering@xxxxxxxxx> wrote:
>> >>
>> >> https://blueprints.launchpad.net/dhis2/+spec/regex-validation
>> >>
>> >> I have updated the blueprint on regular expression use in data
>> >> validation rules. This would really make my life (and I suspect
>> >> others) lives a lot easier, as long as we are using naming
>> >> conventions, lets at least enforce them somehow.
>> >>
>> >> For discussion.
>> >>
>> >> Jason
>> >>
>> >> _______________________________________________
>> >> Mailing list: https://launchpad.net/~dhis2-devs
>> >> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> >> Unsubscribe : https://launchpad.net/~dhis2-devs
>> >> More help   : https://help.launchpad.net/ListHelp
>> >
>> >
>> >
>> > --
>> > Hieu.HISPVietnam
>> > Good Health !
>> >
>
>
>
> --
> Hieu.HISPVietnam
> Good Health !
>