dhis2-devs team mailing list archive

[Hieu Dang Duy <hieu.hispvietnam@xxxxxxxxx>]

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 !