Plugin : Remove DB Table on Plugin Delete ..

[ChrisColeman]

I have created an ADMIN plugin which creates and uses a database table.

When the plugin is DELETED I need to be able to remove the DB table, could someone point me in the right direction  ?

Many Thanks,

Chris Coleman. 

Edited December 8, 2022 by ChrisColeman

[bsmither]

This is what I have come up with.

There is a Feature Request in the Github:

https://github.com/cubecart/v6/issues/701

The code to put in effect is:

In the plugin's config.xml file, find:

</installer>

Just above that, add:

	<database>
		<!-- For deleting tables added by this plugin. -->
		<!-- Put the name of the table in CDATA. -->
<!--<table><![CDATA[Table Name]]></table>-->
		<!-- For deleting columns added to tables not belonging to this plugin. -->
		<!-- Specify the name of the table. Put the name of the column in CDATA. -->
<!--<column table="Table Name"><![CDATA[Column Name]]></column>-->
		<!-- For deleting indexes added to columns in tables not belonging to this plugin. -->
		<!-- If type is PRIMARY, use PRIMARY as the name of the index. -->
		<!-- Specify the name of the table. Put the name of the index in CDATA. -->
<!--<index table="Table Name"><![CDATA[id]]></index>-->
	</database>

Repeat the <table>, <column>, and <index> nodes for each to be removed.

Then,

In /admin/sources/plugins.index.inc.php, find:

if (isset($_GET['delete']) && $_GET['delete']==1 && !empty($_GET['type']) && !empty($_GET['module'])) {
    $dir = CC_ROOT_DIR.'/modules/'.$_GET['type'].'/'.$_GET['module'];
    if (file_exists($dir)) {

Add after:

        // Read in module's config.xml file, if it exists.
        $module_xml = $dir."/config.xml";
        if (file_exists($module_xml)) {
            try {
                $xml   = new SimpleXMLElement(file_get_contents($module_xml));
            } catch (Exception $e) {
                trigger_error($e, E_USER_WARNING);
            }
            if (is_object($xml) && is_object($xml->database)) {
                // Delete indexes from the database.
                if (is_object($xml->database->index)) {
                  foreach ($xml->database->index as $delete_index) {
                    $delete_record = array(
                        'name' => (string)$delete_index,
                        'table' => (string)$delete_index->attributes()->table,
                    );
                    $GLOBALS['db']->misc("ALTER TABLE `".$GLOBALS['config']->get('config', 'dbprefix').$delete_record['table']."` DROP KEY IF EXISTS `".$delete_record['name']."`", false, true);
                    unset($delete_record);
                  }
                }
                // Delete columns from the database.
                if (is_object($xml->database->column)) {
                  foreach ($xml->database->column as $delete_column) {
                    $delete_record = array(
                        'name' => (string)$delete_column,
                        'table' => (string)$delete_column->attributes()->table,
                    );
                    $GLOBALS['db']->misc("ALTER TABLE `".$GLOBALS['config']->get('config', 'dbprefix').$delete_record['table']."` DROP COLUMN IF EXISTS `".$delete_record['name']."`", false, true);
                    unset($delete_record);
                  }
                }
                // Delete tables from the database.
                if (is_object($xml->database->table)) {
                  foreach ($xml->database->table as $delete_table) {
                    $delete_record = array(
                        'name' => (string)$delete_table,
                    );
                    $GLOBALS['db']->misc("DROP TABLE `".$GLOBALS['config']->get('config', 'dbprefix').$delete_record['name']."`", false, true);
                    unset($delete_record);
                  }
                }
            }
        }

Deleting a PRIMARY index is tricky. However, odds are low that a plugin will want to be adding a PRIMARY index to someone else's table.

Deleting a plugin's table and columns in other tables will probably be all that is necessary.

[ChrisColeman]

Hi Bill ,

         I have found a way around it, but I think we need the feature. 
 

I won’t use the feature unless it becomes part of CubeCart base because my clients would probably loose the update whilst upgrading - ie. those that don’t ask me to do the upgrades. 
 

Anyway Thank You,

[ChrisColeman]

Well, it seems to me that it might be useful to include some (admin) interaction in this.

Maybe the user is not aware of the consequences of removing the plug-in.

A message could be generated via tpl -

Quote

eg. Removing this plug-in will delete a database table, which might be useful when/if you use the plug-in again,  click to cancel .. etc. 

Just an idea ..

[bsmither]

CubeCart, when the admin clicks the trash bin link to delete (most) anything, a javascript-powered alert is displayed asking for confirmation for the delete action. The alert phrase is the same for all such delete action.

Regarding this custom capability to undo database changes, should a module's author take advantage of it, because there would be the requirement for specific data to be included in the module's config.xml file, it would fall on the module's author to make mention in the module's documentation that deleting this module will undo the database changes.

As this custom capability stands, there is no method to have a choice in the matter, other than to to edit the config.xml file and delete the specific data related to undoing the customizations made to the database.