Hi Jason Looks good. I think we might want to push ahead and make the tables anyway. Then you can already start taking advantage of them with your queries outside of the system. We can then start implementing the other use cases as we go along - and hopefully not hit too many problems with dialect. Having a few regexes there might be a starting point for investigating the portability issues. I am pretty sure I will take advantage of such a table at some point to do schema based validation on import for example.
Cheers Bob On 9 February 2010 05:20, Jason Pickering <jason.p.picker...@gmail.com> wrote: > 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.picker...@gmail.com> > Date: Mon, Feb 8, 2010 at 9:25 PM > Subject: Re: [Dhis2-devs] Regular expressions in data validation rules > To: Hieu Dang Duy <hieu.hispviet...@gmail.com> > > > 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.hispviet...@gmail.com> 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.picker...@gmail.com> 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.hispviet...@gmail.com> 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.picker...@gmail.com> 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@lists.launchpad.net >>> >> Unsubscribe : https://launchpad.net/~dhis2-devs >>> >> More help : https://help.launchpad.net/ListHelp >>> > >>> > >>> > >>> > -- >>> > Hieu.HISPVietnam >>> > Good Health ! >>> > >> >> >> >> -- >> Hieu.HISPVietnam >> Good Health ! >> > > _______________________________________________ > Mailing list: https://launchpad.net/~dhis2-devs > Post to : dhis2-devs@lists.launchpad.net > Unsubscribe : https://launchpad.net/~dhis2-devs > More help : https://help.launchpad.net/ListHelp > _______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
