NYCPHP Meetup

[Eric Rank]

Thanks for your suggestions Dan (other Dan too). I should apologize for
being a little vague in my previous post about the details of the project
I'm working on. I mainly wanted to see if any of y'all had faced similar
XML->SQL challenges and if there might be packaged solution was out there to
solve it.

Parsing the XML and encapsulating it into objects is the easy part in this
project. It's trying to unpack it all that gets a little screwy.

My solution is to do the following:

1. First parse the XML content into a multidimensional array that reflects
the xml structure (After thinking about it, this should probably be a more
structured object)
2. Create a database object to encapsulate the database structure
3. parse an xml formatted ruleset file into an object that will determine
how to execute sql statements.

This ruleset object is the key to making the whole thing come together.
Essentially, it does the same thing as a stored procedure would. The
likeness is obvious now that you bring up the topic. I hadn't thought of
that before. Basically, this creates a 'rule' for each table, and
contigencies for the columns and tables as needed. This also serves as a
place to map out the relationship between an xml file and a database
structure. The cool thing is that I should be able to take just about any
database & XML pair, create a ruleset file defining the relationships &
contigencies, and it should all work. Although a new xml parser would need
to be created for the xml file holding the data in order to create the
multidimensional struct the translator object expects.

Right now, I'm to the point where:
1. I'm looping through the list of tables from the database object
    1.1 checking the rules for that table
        1.1.1 if the rule passes, the insert query happens and returns
                success
        1.1.2 if the rule does NOT pass, i need to look at a different table
                so i go through the process recursively. checking table
rules,
                inserting where possible until all records have been saved,
                or, at this point, ad infinitum

I can insert data, and to a certain degree it gets inserted correctly, but
I'm winding up with some corruption of data, which I'm trying to pin down.
Infinite loops are happening right now... Recursion is cool when it works. I
kind of feel like I'm working magic when it happens, but until that magical
point, it's fairly hair raising.




----- Original Message ----- 
From: "Dan Cech" <dcech at phpwerx.net>
To: "NYPHP Talk" <talk at lists.nyphp.org>
Sent: Wednesday, February 11, 2004 1:13 PM
Subject: Re: [nycphp-talk] Translating XML to SQL


> From the work I've done on adodb-xmlschema, I would advise an object
> oriented approach, it really helps to break up the logic.
>
> I would create a class to hold the methods for parsing a document, etc,
> and another class to represent an article.
>
> Once you have a class for the article you can build up all the article
> details from the xml, and finally generate and run the SQL to insert all
> the required records.  You could also use the class for accessing the
> data once it is in the database....
>
> You may also want to create classes for author, etc which could handle
> the logic for updating/inserting the relevant details into those tables.
>
> Anyway, it appears you are looking at a procedural solution right now,
> which will work too....honestly I'd have to have more details about the
> specific issues you are working around in order to give you any concrete
> advice.
>
> Dan
>
> Eric Rank wrote:
> > I'm working on a PHP app that will translate XML to SQL based on a
> > configuration file with rules, and as I'm getting to the end of this
> > project, all the quirks and airbubbles (yes, ok, they're BUGS) are
> > getting squeezed out. And man, it's no fun. So now I'm looking for
> > solutions that may have been created before me.
> >
> > Specifically, the xml contains information from articles in a newspaper.
> > Author, issue, volumne, section, category, headline, story, etc. This
> > xml we get from an export from QuarkXPress (which is its own nightmare)
> >
> > I'm trying to  take this xml structure that contains NO database
> > specific information and place it into a MySQL database with about a
> > dozen related tables. The trick is that in going through an xml file,
> > there are a lot of contigencies to cover before finally reaching the
> > database. For example, some INSERT statements require the inserted id of
> > a previous insert statement in order to make the records relational (eg.
> > we need to insert the 'story' info into the 'article' table to get the
> > inserted id for the article, which then gets inserted into the 'photos'
> > table in the 'articleID' column.). Some insert statements might not need
> > to happen because the information already exists (eg. an author that
> > already exists in the database). You get the idea.
> >
> > Anyone have experience doing this? Is there an open source project out
> > there that's working on this? Third party software even?
> >
> > Thanks for your help,
> >
> > Eric Rank
>
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk