[VPM] database frontend

[Peter Scott]

At 12:59 PM 3/18/03 -0800, nkuipers wrote:
> >Eg: what would be presented to a user initially
>
>A welcome page with a list of links, some of which are user/password
>protected.
>One username and one password is sufficient for my purposes; we just want to
>allow any approved guest read-only access to the database.
>
> >what they would do there
>
>No updating the database.  Queries only, with nice webformatted output.  The
>fields of my main table that I want to be queryable are:
>
>locus: int
>cluster: int
>length: int
>
>accession: varchar
>type: varchar
>name: text
>
>All fields should accept lists of arguments, ie., "all repeats located in
>clusters 58,2000"  Numeric fields should accept ranges also ie., "all repeats
>located in clusters 58-2000", or "all repeats where length is less than 500
>and greater than 301".
>
>The second group of fields, other than accession which must be case-sensitive
>and in all other ways totally specific, should allow "fuzzy" querying, ie, a
>request of "all repeats of type sInE" is interpretted as a case-insensitive
>mapping to the actual type "SINE", or "all repeats where name contains the
>text 'fooper dooper'".
>
> >From the above description you can imagine the sorts of buttons and text
>fields might be required.
>
> >what should result, etc.
>
>For now, a simple (but uniformly formatted) HTML dump is sufficient, I'd work
>out the actual formatting later.

I find this sort of thing easiest if I mock up the HTML first and then 
I can imagine the different ways it will be used.  Often there are more 
than I thought of to begin with.  For instance, would the user want to 
query on whether a field is null?  If that ever becomes a requirement 
it can screw up your whole interface unless you thought of it to begin with.

Sounds like you want a table somewhere that describes each field and 
its type, plus what you can do with it.  This is the database schema 
plus a bit more info.  So you could indicate whether or not searches on 
that field are case insensitive, for instance.  Or whether it's 
numeric.  Then your searching code can be completely data-driven and 
just form the query without regard to specific fields.

If efficiency is any concern you want to use placeholders where 
possible and prepare the SQL ahead of time.  This may not be possible 
in this interface, though, where you don't know what the user is going 
to enter.

The requirements you state for handling things like lists and ranges 
are easy to translate to SQL and I've done them before.  I can share 
code if you want.

We could always hack on this tonight as well...

--
Peter Scott
Pacific Systems Design Technologies
http://www.perldebugged.com/