Forums

Package Install/Upgrades

Permalink
I am having problems uninstalling and upgrading my package. I have multiple databases with foreign keys, so whenever I try to Install after a Uninstall or upgrade, I get a mysql error.

mysql error: [1553: Cannot drop index 'group_id': needed in a foreign key constraint] in EXECUTE("DROP INDEX group_id ON HomebuilderPrices")

I don't want to drop these tables in the uninstall and upgrade functions because the contain data that I don't want to lose. Is there a way to tell concrete5 to not display the error, or continue on error?

A clean Install works fine, obviously, since there are no tables that exist.
Silasj
 
Silasj replied on at Permalink Reply
Silasj
*Multiple Database Tables, that is, not multiple databases.
Silasj replied on at Permalink Reply
Silasj
I've found a workaround by dropping the foreign keys from the db.xml and using php ActiveRecord to handle relationships.
pumppi replied on at Permalink Reply
Can you provide example? I have same situation!
Silasj replied on at Permalink Reply
Silasj
What I did was remove the foreign keys from the db.xml. I previously had this: 
<?xml version="1.0"?>
<schema version="0.3">
   <table name="HomebuilderPlans">
      <field name="id" type="I">
         <key ></key>
         <unsigned ></unsigned>
         <autoincrement ></autoincrement>
      </field>
      <field name="plan_name" type="X" ></field>
      <field name="bed" type="I" ></field>
      <field name="bath" type="X" ></field>
      <field name="floors" type="N" ></field>
      <field name="description" type="X" ></field>
      <field name="garage" type="X" ></field>
      <field name="sq_ft" type="I" ></field>
Viewing 15 lines of 32 lines. View entire code block.


And now I have this:

<?xml version="1.0"?>
<schema version="0.3">
   <table name="HomebuilderPlans">
      <field name="id" type="I">
         <key ></key>
         <unsigned ></unsigned>
         <autoincrement ></autoincrement>
      </field>
      <field name="plan_name" type="X" ></field>
      <field name="bed" type="I" ></field>
      <field name="bath" type="X" ></field>
      <field name="floors" type="N" ></field>
      <field name="description" type="X" ></field>
      <field name="garage" type="X" ></field>
      <field name="sq_ft" type="I" ></field>
Viewing 15 lines of 30 lines. View entire code block.


And in my model I extend ActiveRecord:

<?php   
   class HomebuilderPlan extends ActiveRecord\Model {
      public static $table_name = "HomebuilderPlans";
      static $has_many = array(
         array("images", "class_name" => "HomebuilderImage", "foreign_key" => "HomebuilderPlan_id"),
         array("renderings", "class_name" => "HomebuilderImage", "foreign_key" => "HomebuilderPlan_id", "conditions" => "image_class = 'rendering'"),
      );
      static $has_one = array (
         array("main_image", "class_name" => "HomebuilderImage", "foreign_key" => "HomebuilderPlan_id", "conditions" => "image_class = 'main_image'"),
         array("main_rendering", "class_name" => "HomebuilderImage", "foreign_key" => "HomebuilderPlan_id", "conditions" => "image_class = 'main_rendering'")
      );
      function delete() {
         if($this->images) {
            foreach($this->images as $img) {
               $img->delete();
Viewing 15 lines of 35 lines. View entire code block.


As you can see, ActiveRecord will handle the associations, and you can have a "Dumb" database table that won't complain about upgrading.
pumppi replied on at Permalink Reply
Thank you!

I will use that kind of solution.
daenu replied on at Permalink Reply
daenu
Hi there
had the same problem:
I solved it in the controller's install/uninstall methods.

C5 Version is 5.6.2.1
db.xml
<?xml version="1.0"?>
<schema version="0.3">
   <table name="table_1">
      <field name="primary_key_1" type="I">
            <key ></key>
         <unsigned></unsigned>
      </field>
    </table>
    <table name="table_2">
        <field name="primary_key_2" type="I">
            <key ></key>
            <unsigned></unsigned>
        </field>
        <field name="foreign_key_for_table_1" type="I">
            <unsigned></unsigned>
Viewing 15 lines of 18 lines. View entire code block.

Override the install & uninstall methods in the packages's controller file.
In the install method, call first the parent install and ev. block installers and at the end, alter the corresponding table:
public function install() {
        $db = Loader::db();
        $queryFKey = 'ALTER TABLE table_2
                        ADD CONSTRAINT foreign_key_for_table_1
                        FOREIGN KEY fkey1 (foreign_key_for_table_1)
                        REFERENCES table_1(primary_key_1)
                        ON DELETE CASCADE
                        ON UPDATE CASCADE';
        $pkg = parent::install();
        BlockType::installBlockTypeFromPackage($this->pkgHandle, $pkg);
        // at the end
        $db->Execute($queryFKey);
    }

In the uninstall method, first uninstall the package again with its parent method, then drop the foreign key and at least, drop the tables of the package:
public function uninstall() {
        $db = Loader::db();
        parent::uninstall();
        $schema = Database::getADOSchema();
        $sql = $schema->RemoveSchema('db.xml');
        $schema->RemoveSchema('db.xml');
        $db->Execute('alter table table_2 drop foreign key foreign_key_for_table_1');
        $db->Execute('drop table if exists table_1');
        $db->Execute('drop table if exists table_2');
}