Re: [hfdb] Re: Grand Unified Hardware Database

[James K. Lowden]

On Fri, 23 Jul 2004, Zenaan Harkness <address@hidden> wrote:
> James I do have one remaining question though, which is why I asked
> about how to enter some data on my "Microsoft Natural Keyboard":
> 
> how do I as a data entry team member, enter data into this database?

As of now, you and I need to discuss how the keyboard will appear in the
database.  Then you or I need to write a stored procedure that inserts a
keyboard.  Then you need to call that procedure, see below.  

> Right now, this is a little too ephemeral for me. With Richard and
> Till's suggestion of using XML files, I can jump straight into an
> editor, throw some suitable tags around the data, and put it straight
> into CVS. I can visualize exactly the steps involved, and I don't have
> to go through any single point, such as yourself, which would be a
> potential scalability problem as we grow.

You won't have to go through me once things are established.  I don't mean
to load a single row; I'm just here to help define what "row" means.  I
think the reason you're having trouble is that we haven't decided how to
store your keyboard yet.  Once we have, the steps are mechnical.  

More work on my part will make for less work on your part.  In the long
run -- even in the short run, given some investment -- less work for you
is a big payoff, because there are many more rows than columns.  That is,
entering the data is much harder than defining the model.  

My idea is to define stored procedures to insert the data.  You call
'PrintersInsert' passing it a bunch of parameters, and it does the rest,
doling out various parts in the related tables that define a printer.  

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.  

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

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.  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.  FSVO presto, of
course.  ;-)

You like?  

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.
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.  

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.  

Regards, 

--jkl