dhis2-devs team mailing list archive

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

OK, let me try and explain. Remember, I think in terms of databases, you
think in terms of Java, so I think we are getting tripped up on the
vocabulary. :)

Basically, what I mean by "field level validation" is the ability to check
an individual field in a database table/object. So, in the example that I
have been using, organisationunit is the table, and "name" is the field to
be checked with. I guess in Java terms this would correspond to an object
and a property.

So, again, I have been envisioning this functioning as a field/property
level validation function, similar to the data integrity checks. If you look
at DHIS 1.4, there are data integrity checks stored as SQL statements in the
database, which allows flexible definition of data integrity rules. What I
am envisioning is similar to this functionality, but allowing users the
ability to define a regular expression and then a field/property to match it
against.

Hope this makes this more clear?


Regards,
Jason


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

>
>
> On Mon, Mar 29, 2010 at 3:08 AM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
>> I have been pondering over this over the weekend, and have a few more
>> thoughts here.
>>
>> The more I think about it, the more I think this is similar to something I
>> have been thinking about for some time, which is the ability to execute
>> passthrough SQL queries on the DHIS2 database. I know that H2, Postgres and
>> MySQL all have support for regular expressions, with slight variations. So,
>> what if we broke this blueprint up into several pieces.
>>
>>
> Yes, me too. But too bad I could not think out any thing. Anyway, I thought
> that let's do this in a very simple step first. Maybe, a blueprint regex
> validation for testing first which the same of validation rule.
>
>
>> 1) A GUI element to allow users to enter field-level validation with
>> regular expressions, as I described in my previous mail. These regexes could
>> conceivably be reused during data import from external systems (SDMX-HD).
>> Seems pretty simple.
>>
>>
> Sorry Jason,
>
> I am still not un-clearly about this *level *field. So, could you explain
> me more about this one. Is it a new field in the Regex table or the
> referencing table (the second one)? What is it using for ? I would like to
> see some of examples with this field, can you show up them, please ?
>
>
>
>> 2) A query executor, that for right now, would execute a select query
>> based on the stuff stored in the regex table, and execute a query against
>> the database. As an example, these two objects would be stored in the
>> database
>>
>>
>> 1) A regular expression
>>
>> a) 1 (or something from the hibernate_sequence) (regexid)
>> b) No trailing spaces (name)
>> c) \s+$ (regexexpression)
>> d) This field is not allowed to have trailing spaces (violation_message)
>> e) Please remove all trailing spaces from this field. This means any
>> spaces after the last character. (violation_resolution)
>>
>> 2) What the regular expression should operate on
>>
>> a) 1 (foreign key reference to the regex_objects table /object)
>> b) organisationunit (object/table)
>> c) name (property)
>> d)  FALSE (negate)
>> e)  TRUE (case_insensitive)
>> f)   NULL locale
>>
>> Java can work some magic here, and convert this into  an SQL query for the
>> particular DB system we are using...
>>
>> SELECT * FROM organisationunit where name ~('\\s+$') for postgres
>>
>> In MySQL this would become..
>>
>> SELECT * FROM `organisationunit` WHERE `name` REGEXP '\\s$'
>>
>> I think it is possible in H2, but no idea what the grammar is.
>>
>>
> Yes, I think we can do this later. Maybe should not too worry about this.
>
>
>>
>> Some sort of list (name, shortname, some other fields) would be returned
>> to the user along with instructions (1.e) about how to resolve it.
>>
>> There could be many more uses for a passthrough SQL query executor, thus
>> it might be useful for constructing it in a way to make in generic to
>> execute arbitrary SELECT statements. I will not address this in this thread,
>> but perhaps a separate blueprint.
>>
>>
> As I said which thing is above, we just do it as testing blueprint first to
> jump in the next one.
>
>
>> --
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+260968395190
>>
>
>
>
> --
> Hieu.HISPVietnam
> Good Health !
>



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