Forums

Adding indexes and foreign key constraints to package tables

Permalink 1 user found helpful
What is currently the correct way to add foreign keys to a Concrete5 database during the installation of a package?

I tried adding CONSTRAINT elements to the db.xml file, like so:

<field name="categoryID" type="I">
   <unsigned />
   <default value="0" />
   <constraint>
      , FOREIGN KEY (categoryID) REFERENCES smrProductCategories(categoryID)
   </constraint>
</field>


But no foreign key constraints were created. One article (http://codingexplained.com/coding/php/concrete5/adding-foreign-keys-in-db-xml-in-concrete5) suggested, that the way to go about it is to add an SQL element and write the necessary queries there, like so:

<sql>
   <query>CREATE INDEX idx_smrproducts_categoryid ON smrProducts (categoryID)</query>
   <!-- and so on... ->
   <query>ALTER TABLE smrProducts ADD CONSTRAINT fk_smrproducts_categoryid FOREIGN KEY (categoryID) REFERENCES smrProductCategories(categoryID)</query>
   <!-- and so on... ->
</sql>


But that didn't work either. No indexes or foreign key constraints were created.

I ended up writing raw queries in the install() method of my package:

$db->query( 'CREATE INDEX idx_smrproducts_categoryid ON smrProducts (categoryID)' );
$db->query( 'ALTER TABLE smrProducts ADD CONSTRAINT fk_smrproducts_categoryid FOREIGN KEY (categoryID) REFERENCES smrProductCategories(categoryID)' );
// and so forth...


That worked of course, but it created another problem: I couldn't upgrade my package anymore, because when comparing the schema described by db.xml and the actual one (modified by my raw queries), Doctrine tries to remove the indexes and fails because of the constraints (this happens in Package::installDB()). It also fails silently, because for some reason the Exception it throws is catched (see concrete/controllers/single_page/dashboard/extend/update.php, line 40), set as an error but isn't displayed, at least not on my installation. I had to var_dump it to find out what was happening:

"An exception occurred while executing 'DROP INDEX idx_smrproductavs_akid ON smrproductattributevalues':
SQLSTATE[HY000]: General error: 1553 Cannot drop index 'idx_smrproductavs_akid': needed in a foreign key constraint"


As a temporary fix I now run a method that removes all constraints and indexes from the package tables before the parent::upgrade() is executed and another one that recreates them afterwards, but that is very hackish.

Is there any way to add indexes and foreign key constraints via db.xml?
Juha
 
ntisithoj replied on at Permalink Reply
ntisithoj
I would like to know the answer to this as well.

But I am also really curious why there is only 1 FK constraints in the entire C57 schema. And as there are no FKs, how does C57 ensure there are not countless orphans laying around? Was this a strategic decision? Have they replaced the inherent functionality that comes with FKs with something else? Should we not use FKs at all in C57?

The 1 FK that does exist is : 

CONSTRAINT `QueueMessages_ibfk_1` FOREIGN KEY (`queue_id`) REFERENCES `queues` (`queue_id`) ON DELETE CASCADE ON UPDATE CASCADE


This is the relevant section of db.xml
<table name="QueueMessages">
    <field name="message_id" type="I" size="20">
      <KEY/>
      <AUTOINCREMENT/>
      <UNSIGNED/>
    </field>
    <field name="queue_id" type="I" size="10">
      <NOTNULL/>
      <UNSIGNED/>
    </field>
    <field name="handle" type="C" size="32"/>
    <field name="body" type="X2">
      <NOTNULL/>
    </field>
    <field name="md5" type="C" size="32">
Viewing 15 lines of 33 lines. View entire code block.


No FK defined here… so where is that FK being defined?
Juha replied on at Permalink Reply
Juha
Looks like it's in the indexAdditionalDatabaseFields() method in concrete/src/Package/StartingPointPackage.php, on line 210:

protected function indexAdditionalDatabaseFields() {
      $db = Loader::db();
        $db->Execute('alter table PagePaths add index (`cPath` (255))');
        $db->Execute('alter table Groups add index (`gPath` (255))');
        $db->Execute('alter table SignupRequests add index (`ipFrom` (32))');
        $db->Execute('alter table UserBannedIPs add unique index (ipFrom (32), ipTo(32))');
        $db->Execute(
            'alter table QueueMessages add FOREIGN KEY (`queue_id`) REFERENCES `Queues` (`queue_id`) ON DELETE CASCADE ON UPDATE CASCADE'
        );
    }


So a raw query is used here too.
ntisithoj replied on at Permalink Reply
ntisithoj
Ah... thanks.

Personally, I think this is less-than-ideal, to be adding FK outside the schema definition. Perhaps this will change now that they are moving to Doctrine.
Juha replied on at Permalink Best Answer Reply
Juha
After wading through some code (src/Database/Schema/Parser/Axmls.php), it seems to me that currently foreign keys can't be added via db.xml, but indexes can. I added this between table tags after field definitions:

<index name="idx_smrproductavs_productid">
   <col>productID</col>
</index>
<index name="idx_smrproductavs_akid">
   <col>akID</col>
</index>


and indexes were created properly. Looking at the code, I THINK these are the options you have:

<!-- unique index -->
<index name="idx_myindexname">
   <col>columnName1</col>
   <unique />
</index>
<!-- fulltext index -->
<index name="idx_mysecondindexname">
   <col>columnName2</col>
   <fulltext />
</index>
<!-- plain index -->
<index name="idx_mythirdindexname">
   <col>columnName3</col>
</index>
<!-- compound/composite index -->
Viewing 15 lines of 19 lines. View entire code block.


Sadly, constraints are completely ignored. So... out of curiosity, I hacked the Axmls parser a bit and added a _setConstraints() method:

// Also needs "$this->_setConstraints($db, $t, $table)" line in
// the parse() method of Axmls to work
protected function _setConstraints(
      \Concrete\Core\Database\Connection\Connection $db,
      \SimpleXMLElement $table,
      $schemaTable
 ) {
   foreach ( $table->constraint as $constraint ) {
      $constraintName = isset( $constraint['name'] ) ? (string)$constraint['name'] : null;
      $foreignTable = (string)$constraint['table'];
      $localColumns = array();
      $foreignColumns = array();
      $options = array();
      if ( isset( $constraint['ondelete'] ) ) {
         $options['onDelete'] = (string)$constraint['ondelete'];
Viewing 15 lines of 26 lines. View entire code block.


and then in the db.xml (after index elements):

<constraint name="fk_smrproductattributevalues_productid" table="smrProducts" ondelete="CASCADE">
   <col local="productID" foreign="productID" />
</constraint>
<constraint name="fk_smrproductattributevalues_akid" table="AttributeKeys" ondelete="CASCADE">
   <col local="akID" foreign="akID" />
</constraint>


and voila! Rudimentary (but working!) foreign key support in db.xml.

Granted, this was gobbled together very quickly just see how easy it would be to do, so it probably needs some spit & shine and better error handling. Also, the element and attribute names may need more consideration to be less ambiguous and technically accurate.

But, if anyone (*cough* core team *cough*) wants to add foreign key support to db.xml, this can be used as a starting point.
ntisithoj replied on at Permalink Reply
ntisithoj
nice job!
ntisithoj replied on at Permalink Reply
ntisithoj
posted a followup question here ->http://www.concrete5.org/index.php?cID=705705...