XML with 'concatenated' primary key [#1376520]

I have an XML file that I can importing into a custom table [as part of a larger routine].
In general, importing into the custom table works well.
But some of the XML files are structured a little differently, and I'm not sure how to approach it.

<CourseProperty>
	<CourseID>52797</CourseID>
	<PropertyID>27</PropertyID>
	<PropertyValue>ABC</PropertyValue>
</CourseProperty>
<CourseProperty>
	<CourseID>52797</CourseID>
	<PropertyID>31</PropertyID>
	<PropertyValue>More text here</PropertyValue>
</CourseProperty>

I'm unsure how to setup the query in the import class

 $item_xpath = '/ROOT/CourseProperty';  // relative to document
 $item_ID_xpath = 'CourseID' . 'PropertyID';         // relative to item_xpath and gets assembled

Any suggestions?

Comments

Comment #1

alanburke commented 20 December 2011 at 11:50

It looks like I have 2 different issues.
One is setting up the query in the import class.
I tried

    $item_xpath = '/ROOT/CourseProperty';  // relative to document
     $item_ID_xpath = '*';         // relative to item_xpath and gets assembled

And in 6.2 stable version, this returns the correct number of records in the XML file.
However, I got an error Call to undefined function _drupal_decode_exception() when trying to run the import.
So I updated to the dev version, which gives a longer and more helpful SQL error
but now the list of records is reduced to the number of distinct CourseIDs in the file.

The more helpful SQL error on import now says

SQLSTATE[HY000]: General error: 1364 Field 'sourceid2' doesn't have a default value' in /Users/alanburke/projects/annertech-dev/skillnets/website/sites/default/modules/dbtng/database/database.inc:2045

This error is possibly related to how I have set up the map

 19     $this->map = new MigrateSQLMap($this->machineName,
 20       array('CourseID' => array(
 21               'type' => 'int',
 22               'unsigned' => TRUE,
 23               'not null' => TRUE,
 24               'description' => 'Course ID',
 25            ),
 26             'PropertyID' => array(
 27               'type' => 'int',
 28               'unsigned' => TRUE,
 29               'not null' => TRUE,
 30               'description' => 'Course ID',
 31            ),
 32          ),
 33       MigrateDestinationTable::getKeySchema($table_name)
 34     );

Any pointers welcome.

Comment #2

mikeryan

he/him

English

Pittsfield, MA, USA

commented 20 December 2011 at 18:28

Status:

Active

» Postponed (maintainer needs more info)

Yes, the XML sources do assume you're dealing with a single-value primary key. To deal with multi-part keys, you'll need to override the XML source (I'm guessing you're using MigrateItemsXML?). Add a second ID xpath to the constructor and save it, and override getItemID to apply both xpaths and concatenate the strings using a pipe or something like as a separator. I'm sure there will be more tweaking necessary, but that should get you started.

Is this helpful?

Comment #3

alanburke commented 20 December 2011 at 22:11

Status:

Postponed (maintainer needs more info)

» Active

Hi Mike
That's a great start. Thanks!
I've done as suggested, with that.
One bit I don't quite understand is the function getItem($id)
It is called from within multiitems.inc but where is the ID generated?
I think I need to set that ID to match the ID I am generating in getItemID