Hi all,
I keep getting this syntax error. I am not sure what to do here.

I have a case table, with fields like: content_id (serial) -pk, case_details, etc.
I have a casefiles table, with fields like: casefile_id -pk, content_id (int) - fk, file_upload_url, etc.

I am trying to migrate casefiles into a case field_collection attached to a case_node.

I applied a patch from here: http://drupal.org/node/1175082#comment-7061688 to make this work with field_collection.

I get this error msg which looks like it has nothing to do with the field_collection but my tables.
What could i be doing wrong here? Should i be doing anything differently?

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unsigned NOT NULL, `destid1` INT unsigned NULL DEFAULT NULL COMMENT 'ID of fiel' at line 2: CREATE TABLE {migrate_map_casefile} ( `sourceid1` unsigned NOT NULL, `destid1` INT unsigned NULL DEFAULT NULL COMMENT 'ID of field collection item', `needs_update` TINYINT unsigned NOT NULL DEFAULT 0 COMMENT 'Indicates current status of the source row', `rollback_action` TINYINT unsigned NOT NULL DEFAULT 0 COMMENT 'Flag indicating what to do for this item on rollback', `last_imported` INT unsigned NOT NULL DEFAULT 0 COMMENT 'UNIX timestamp of the last time this row was imported', PRIMARY KEY (`sourceid1`) ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'Mappings from source key to destination key'; Array ( ) in MigrateSQLMap->ensureTables() (line 155 of /webroot/case/sites/all/modules/contrib/migrate/plugins/sources/sqlmap.inc).


class CasefileMigration extends JMKMigration {
	public function __construct() {
		parent::__construct();
		$this -> description = t('Migrate Casefiles into Field Collections. Also attaches each Field Collection to the appropriate Case.');
    	$this->dependencies = array('Case');
		
		$this -> map = new MigrateSQLMap($this -> machineName, 
			array(
				'content_id' => array('
					type' => 'int', 
					'unsigned' => TRUE, 
					'not null' => TRUE,
					'alias' => 'cf',
					)), 
			MigrateDestinationFieldCollection::getKeySchema()
		);
		
		$source_fields = array();

		$query = db_select('casefiles', 'cf')
			  ->fields('cf', array('content_id, file_date', 'file_upload_url', 'file_upload_filename','file_upload_filepath','file_upload_title'));
			$query->rightJoin('cases', 'c', "cf.content_id=c.content_id and cf.file_upload_url like '%pdf%'");
		
               $this->source = new MigrateSourceSQL($query, array(), NULL, array('map_joinable' => FALSE));	

		$this->destination = new MigrateDestinationFieldCollection('field_casefile_collection', array(
		'host_entity_type' => 'node'
		));

    	$this->addFieldMapping('host_entity_id', 'content_id')
			 ->sourceMigration('Case');

		$this->addFieldMapping('field_casefile_date', 'file_date');	  
                $this->addFieldMapping('field_casefile', 'file_upload_filename');
		$this->addFieldMapping('field_casefile:description', 'file_upload_title');

Comments

jumoke’s picture

jumoke commented

Update:

I removed the join and now i get:

MigrateException: Failure to sort migration list - most likely due to circular dependencies involving Casefile in migrate_migrations() (line 80 of /webroot/case/sites/all/modules/contrib/migrate/migrate.module).


class CasefileMigration extends JMKMigration {
	public function __construct() {
		parent::__construct();
		$this -> description = t('Migrate Casefiles into Field Collections. Also attaches each Field Collection to the appropriate Case.');
    	$this->dependencies = array('Case');
		
		$this -> map = new MigrateSQLMap($this -> machineName, 
			array(
				'content_id' => array('
					type' => 'int', 
					//'unsigned' => TRUE, 
					'not null' => TRUE,
					'alias' => 'cf',
					)), 
			MigrateDestinationFieldCollection::getKeySchema()
		);
		
		$source_fields = array();

		$query = db_select('casefiles', 'cf')
			  ->fields('cf', array('content_id, file_date', 'file_upload_url', 'file_upload_filename','file_upload_filepath','file_upload_title'))
                 ->condition('file_upload_url', '%pdf%', 'LIKE');

		$this->source = new MigrateSourceSQL($query, array(), NULL, array('map_joinable' => FALSE));	

		$this->destination = new MigrateDestinationFieldCollection('field_casefile_collection', array(
		'host_entity_type' => 'node'
		));

    	$this->addFieldMapping('host_entity_id', 'content_id')
			 ->sourceMigration('Case');

		$this->addFieldMapping('field_casefile_date', 'file_date');	  
                $this->addFieldMapping('field_casefile', 'file_upload_filename');
		$this->addFieldMapping('field_casefile:description', 'file_upload_title');

Any ideas why?

jumoke’s picture

jumoke commented

Here is the case node migration. It works fine in creating its nodes and populating its tables.

class CaseMigration extends JMKMigration {
	public function __construct() {
		parent::__construct();
		$this -> description = t('Migrating the Case content type');

		$this -> map = new MigrateSQLMap($this -> machineName, 
			array(
				'content_id' => array(
				'type' => 'int', 
				'unsigned' => TRUE, 
				'not null' => TRUE, 
				'alias' => 'c', )
				), 
			MigrateDestinationNode::getKeySchema()
		);

		$query = db_select('cases', 'c')
			  ->fields('c', array(
			  	'title', 'body', 'file_num', 'civil_action_num', 'matter_num', 'docket_num','release_date','content_id'));

		$this->source = new MigrateSourceSQL($query, $source_fields);
		$this->destination = new MigrateDestinationNode('case');

		// Mapped fields

		$this->addFieldMapping('title', 'title');
		$this->addFieldMapping('field_case_summary','body');
               //more fields ....

	    $this->addUnmigratedDestinations(array(
	    	'field_casefile_collection',
	    ));
		
		$this->addUnmigratedSources(array(
      		'content_id',
    	));
    }
mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Status: Active » Postponed (maintainer needs more info)

Your problem is a misplace ':

        $this -> map = new MigrateSQLMap($this -> machineName,
            array(
                'content_id' => array('
                    type' => 'int',

Since no 'type' is seen, the CREATE TABLE statement is not including the type of int, and the SQL is totally bogus.

jumoke’s picture

jumoke commented
Status: Postponed (maintainer needs more info) » Closed (fixed)

thank you Mike, that fixed it. It's working now.