0

I'm trying to use Doctrine in a CLI application that handles data migration. Things were going well until I encountered a table/entity that has no identity increment for the primary key. Well, the primary key is actually a composite of three fields. The table spec (MySql) is:

CREATE TABLE `xcart_variants` (
  `variantid` int(11) NOT NULL DEFAULT '0' COMMENT 'Use XCVariantsChange::addVariant to add a row;AUTO_INCREMENT is removed for performance purpose',
  `productid` int(11) NOT NULL DEFAULT '0',
  `avail` int(11) NOT NULL DEFAULT '0',
  `weight` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Use func_decimal_empty',
  `productcode` varchar(32) NOT NULL DEFAULT '0' COMMENT 'Cannot be unique as used by different providers and may be duplicated in    XCVariantsChange::repairIntegrity()',
  `list_price` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Can be disabled using config[PRoduct_Options][PO_use_list_price_variants].Use func_decimal_empty',
  `def` char(1) NOT NULL DEFAULT '',
  `is_product_row` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'For performance;For replace left join to inner join;Must duplicate product data;Only one such row is allowed per productid;def is 0 for the row',
  PRIMARY KEY (`productid`,`variantid`,`is_product_row`),
  UNIQUE KEY `pp` (`productcode`,`productid`),
  KEY `pi` (`productid`,`is_product_row`),
  KEY `vi` (`variantid`,`is_product_row`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The referenced method is doing something like this under the covers, within a table lock:

$arr['variantid'] = func_query_first_cell("SELECT MAX(variantid)+1 FROM $sql_tbl[variants]");
$res = db_query("INSERT INTO $sql_tbl[variants] 
                        (variantid,productid,
                         avail,weight,
                         productcode, list_price,
                         def,is_product_row) 
                 VALUES ($arr[variantid], '$arr[productid]', 
                         $arr[avail]', '$arr[weight]', 
                         '$arr[productcode]', '$arr[list_price]', 
                         '$arr[def]', '$arr[is_product_row]')");

The problem is that I have other insertions to do within the UnitOfWork, and foreign key relationships need to be wired up. It looks like Doctrine supports straight SQL with custom mapping, but the docs say that's really just for SELECT statements.

What's the best way to handle this case?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
HelluvaEngineer
  • 58
  • 8
  • 1
    I guess you want to use Doctrine's DBAL layer: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html – Miro Apr 26 '16 at 14:07
  • 1
    There are no foreign keys in that table, the keys are all local to that table. What is the EXACT error message that you are getting – RiggsFolly Apr 26 '16 at 14:18
  • Yes, but another table expects to have variantid as a foreign key. Here is the resulting error: `Doctrine\ORM\ORMException: Entity of type OmsCore\XcartVariants is missing an assigned ID for field 'variantid'. The identifier generation strategy for this entity requires the ID field to be populated before EntityManager#persist() is called. If you want automatically generated identifiers instead you need to adjust the metadata mapping accordingly.` – HelluvaEngineer Apr 26 '16 at 14:25
  • As a heads-up, I got this working but it was not easy. I had to create an explicit transaction, lock all affected tables, flush my repo, then do a direct INSERT for the problematic table, then attach my other entities and flush again. – HelluvaEngineer Apr 27 '16 at 14:11

0 Answers0