Re: [hfdb] Re: Grand Unified Hardware Database

[Zenaan Harkness]

On Mon, 2004-07-26 at 09:56, James K. Lowden wrote:
> My idea is to define stored procedures to insert the data.  You call

Here's another question - do you have experience with stored procedures
with more than one RDBMS backend, such that you can say how "specific to
RDBMS backend" stored procedures can be?

And how cross-db can they be made?

I have a bit of understanding that stored procedures can be in different
languages, such as oracle-proprietary (C-like?), or something embedded
such as Java, or perhaps just defined in SQL?

I am of course thinking about replicating this in my local Postgres
install...

> 'PrintersInsert' passing it a bunch of parameters, and it does the rest,
> doling out various parts in the related tables that define a printer.  

And checking constraints? Or are constraints specified in some other
manner (or a combination)?

> On the ground, you *could* execute that procedure from an ISQL shell (e.g.
> sqsh www.sqsh.org).  That's the fat-finger approach.  
> 
> I imagine your "Microsoft Natural Keyboard" to be a record in a file.  A
> little DBI Perl script will read the records, taking the name of the
> stored procedure as a parameter.  Note we need only 1 such script, because
> it calls any procedure with any number of parameters.  If the database
> rejects a record, it can note that in a log file and proceed to the next
> one.  

For me-as-end-user submitting my single keyboard record, that file will
be a plain text file with just the data for my keyboard - or now that I
write that I imagine a little more structure will need to be defined,
and me-as-hfdb-team-member will have to make sure that file (/email) is
in the "correct" format?

> One advantage of using DBI is that moving to another database will require
> very few changes.  

This is good :)

> You probably noticed that I left out the form of the script's input.  I
> personally don't care, but like you I'm limited by what I know.  It's
> possible for Perl to parse either valid XML or simple tab-delimited files.
>  I agree an XML file is self-describing, but parsing XML isn't something I
> know much about.  (Unfortunately.  It's on my list, but I don't want my
> education to get on this project's critical path.)  So, if the raw data
> (if you will) is to be in XML, someone with XML experience/ambition is
> going to have to do that end.  The DBI part, I know.  
> 
> Tell you what I'll do, just for you, special offer, today only.  I'll
> write the aforementioned script to read a flat file.  It will treat each
> line as a tab-delimited set of arguments to a stored procedure.  It will
> call that procedure once per line in the file, writing errors to stderr. 
> At a bare minimum, we need that, it seems to me.

Is it necessary to go as far as having multiple entries in such a file?
If I "submit" my keyboard data, it's only one keyboard.

I imagine that for importing pccids, printer db, etc, a custom script
will be needed for each set of data we import, right? But I see that as
a different task to entering a new device. Not sure though...

>   Then, if someone wants
> to do the XML end, he can use my script as a model, or modify it, or write
> another that calls it.  Presto, we have XML loading.

Actually I did a bit of templating stuff (you can see via my homepage,
soulsound.net) with Ruby. It was quite nice. I did a comparison with two
data formats (XML and YAML), and had a look at a Java version I think. I
don't know perl myself.

>   FSVO presto, of
> course.  ;-)
>
> You like?  

Whatever works/ makes sense for getting data stored. As long as I can
enter data in a not overly obtuse way, it doesn't really matter how I'd
think.

The other part is of course getting the data back out.

> BTW, if we're to use an RDBMS, it's not terribly clear to me what are the
> benefits of storing the inputs in CVS.  RMS sees XML as a useful product
> unto itself, but from my point of view the only data that count are in the
> database.  I can *imagine* archiving database dumps in CVS.  I'm not sure
> we want to do that.  
> 
> In short, the data loading task breaks down this way:
> 
> 1.  Acquire data, using fingers if need be.

Or scripts for large volumes of readily-available data.

> 2.  Transform data into a form acceptable for loading.
> 3.  Load.
> 4.  Deal with errors, repeat.  
> 5.  Brag. 
> 
> There are several one-time steps before you get to #1, though:
> 
> 0.  Understand data.  
> 1.  Devise schema (moi).  
> 2.  Write procedure(s).  
> 3.  Install tools.

That step 3 is the thing it would be nice to be able to minimize for
people who join our data-entry team.

I would like to ensure we can backup our data, ideally in a
version-controlled, textual format. There is a certain unixy + robust
feel to being able to do this. It will also lead to ready cross-RDBMS
backend capability (for those willing to write a set of stored
procedures, etc.).

Again, I'll get to some reading over the next week or so.

> If the project grows such that we have eager data developers who are
> daunted by installing tools and loading the database directly, it seems to
> me it wouldn't be hard to write a mail client that would accept our
> script's input as an attachment, apply it to the database, and return the
> logs.  Longer round trips, but nothing to install.  

Sounds fine. I'm sure there will be suitable solutions.

Thanks
Zen