[sf-perl] Code Question

David Fetter david at fetter.org
Wed Jun 27 16:52:14 PDT 2007


On Wed, Jun 27, 2007 at 04:04:08PM -0700, Brian Hamlin wrote:
> Hello Perl Veterans
> 
> I have a question about DBI, Files and some XML. Useful References 
> appreciated.

Brian,

I don't mean offense here, but it appears from your description that
you are trying to use an SQL database as a dumb data store,
reinventing many wheels along the way.

DBMSs are subtle and quick to anger, and when they get upset, they
take their vengeance, both sooner and later.  Rather than picking a
fight with your software, consider rethinking this approach and
instead using the features of one or other DBMS so it does a lot of
the work for you.

You can probably figure out which DBMS I'll suggest ;)

A few URLs to ponder on tree structures in databases:

http://www.sai.msu.su/~megera/postgres/gist/ltree/
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/tablefunc/
http://threebit.net/tutorials/nestedset/tutorial1.html
http://www.sitepoint.com/print/hierarchical-data-database
http://www.fuzzy.cz/databaze/index-en.php
http://www.grzm.com/fornow/archives/2004/07/10/static_hierarchies
http://mrnaz.com/static/articles/trees_in_sql_tutorial/index.php
http://www.mrnaz.com/static/articles/trees_in_sql_tutorial/

On XML in particular:

http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/xml2/
http://developer.postgresql.org/index.php/XML_Support
http://gborg.postgresql.org/project/xpsql/projdisplay.php

History and features which will be in the upcoming PostgreSQL 8.3:

http://www.pgcon.org/2007/schedule/events/14.en.html

Cheers,
David.

> I wrote a functional DBI program that just reads data on request, 
> invoked as a cgi, for light weight use. That works fine. The data is 
> returned as an XML structure that I write out manually using print.
> 
> Now, I want to do something more elaborate-
> 
> * read data from an external source (thats working, its a Salesforce 
> SOAP query)
>     comes back as XML, I use Soap::Lite
> * Geocode the addresses in the table, add the results to the XML 
> structure
>      thats working already in an individual script, LWP get
> * take a list of numbers in one field and make a separate "many to 
> many" table out of them
> * take the rest of the data and make a table out of it
>      meaning, write SQL statements manually I suppose. I have that 
> working in individual scripts
>      a lot of things I have looked at look like more work to get started 
> that it is to do this by hand
>      I have an idiom someone showed me, which works, using a local array 
> with Field Names as a template
>        to write the SQL statements
> * EXPORT the previous data via DBI and write it to a time-stamped text 
> file
>      eg MerchTBl_26Jun07.sql/xml/whatever
> * REPLACE the contents of the table completely with the fresh data
>      the client is not going to pay for a custom system, I am only so 
> good at this so far
> 
> Each step is doable, especially since I have already solved many of the 
> unusual cases
> manually once. It is really the rote, ordinary things that are new to 
> me, and of course, general
> good practices, etc. I dont have the DBI cookbook
> 
> Code samples etc apropos to this are appreciated. I do read newsgroups 
> and books etc, but since
> Perl has been around for a while, the volume of such things is very 
> large, and for someone getting
> their footing its hard to wade through. Hence the question to the list.
> 
> thanks in advance
>     -Brian
> 
> _______________________________________________
> SanFrancisco-pm mailing list
> SanFrancisco-pm at pm.org
> http://mail.pm.org/mailman/listinfo/sanfrancisco-pm

-- 
David Fetter <david at fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate


More information about the SanFrancisco-pm mailing list