Leftover database tables from uninstalled plugins

[alfredbuehler]

I often wanted a tool to remove such waste. OTOH, sometimes it's better, not to remove, in case you reinstall.

[aljawaid]

I agree, you originally highlighted the problem a few months ago and now I understand it better. I'm trying to think of a few ways around it. I am creating my first plugin with a database, after that, I will look into this for sure as I will want to delete my own tables too after uninstalling.

[aljawaid]

after your code update, thanks for that... but its still spinning...

removeTable() is coded how you mentioned above... but its still spinning in the modal without a result or error.

is this controlller function correct?

public function confirm()
    {
        $this->response->html($this->template->render('contentCleaner:config/remove', array(
            'table' => $this->request->getStringParam('table'),     ////this gives no error, no result
//or should it be             'table' => $table,    // this gives undefined variable but still no result
        )));
    }

[creecros]

your gonna make me download this and figure it out arent you. give me a sec.

[aljawaid]

oh i see another mistake

<?= $this->modal->confirmButtons(
        'CleaningController',
        'removeTable',
        array($table => 'table', 'plugin' => 'ContentCleaner'),
        t('Delete')
    ) ?>

$table is flipped with 'table' should be 'table' => $table

that worked! and also the below:

public function confirm()
    {
        $this->response->html($this->template->render('contentCleaner:config/remove', array(
            'table' => $this->request->getStringParam('table'),
            //'table' => $table,
        )));
    }

THANKS SO MUCH

[aljawaid]

your gonna make me download this and figure it out arent you. give me a sec.

no need, you fixed it :) tabel got deleted.... now I can create different buttons for different plugins and different sql queries for core tables to cleanup data.... @alfredbuehler looks like thanks to creecros, thsi plugin will see a release :)

[creecros]

aborting download 🛑

[creecros]

Plausible start, had ChatGPT help me on this one. Haven't actually tested it. (ChatGPT could be full of shit though)

function getTableColumns($schema_file_path) {
  // Load the schema file
  $schema = file_get_contents($schema_file_path);

  // Split the schema into individual statements
  $statements = preg_split('/\s*;\s*/', $schema, -1, PREG_SPLIT_NO_EMPTY);

  // Initialize an array to store the table names and column names
  $table_columns = array();

  // Loop through each statement
  foreach ($statements as $statement) {
    // Check if the statement is a CREATE TABLE statement
    if (preg_match('/^CREATE\s+TABLE\s+`?(\w+)`?/i', $statement, $matches)) {
      // Extract the table name
      $table_name = $matches[1];

      // Extract the column names
      preg_match_all('/`?(\w+)`?\s+\w+\s*(?:\(\s*\d+\s*\))?\s*(?:NOT\s+NULL)?\s*(?:AUTO_INCREMENT)?(?:\s*DEFAULT\s*\'?\w+\'?)?/i', $statement, $matches);
      $column_names = $matches[1];

      // Add the table name and column names to the result array
      $table_columns[$table_name] = $column_names;
    }

    // Check if the statement is an ALTER TABLE statement for dropping a column
    if (preg_match('/^ALTER\s+TABLE\s+`?(\w+)`?\s+DROP\s+COLUMN\s+`?(\w+)`?/i', $statement, $matches)) {
      // Remove the column name from the corresponding table's column list
      $table_name = $matches[1];
      $column_name = $matches[2];
      $key = array_search($column_name, $table_columns[$table_name]);
      if ($key !== false) {
        unset($table_columns[$table_name][$key]);
      }
    }

    // Check if the statement is an ALTER TABLE statement for renaming a column
    if (preg_match('/^ALTER\s+TABLE\s+`?(\w+)`?\s+CHANGE\s+`?(\w+)`?\s+`?(\w+)`?\s+\w+\s*(?:\(\s*\d+\s*\))?\s*(?:NOT\s+NULL)?\s*(?:AUTO_INCREMENT)?(?:\s*DEFAULT\s*\'?\w+\'?)?/i', $statement, $matches)) {
      // Rename the column in the corresponding table's column list
      $table_name = $matches[1];
      $old_column_name = $matches[2];
      $new_column_name = $matches[3];
      $key = array_search($old_column_name, $table_columns[$table_name]);
      if ($key !== false) {
        $table_columns[$table_name][$key] = $new_column_name;
      }
    }

    // Check if the statement is a DROP TABLE statement
    if (preg_match('/^DROP\s+TABLE\s+`?(\w+)`?/i', $statement, $matches)) {
      // Remove the table and its column names from the result array
      $table_name = $matches[1];
      unset($table_columns[$table_name]);
    }
  }

  return $table_columns;
}

[aljawaid]

besides the comments, the rest is a bit complicated lol.... but too complicated for me to do if I dont understand it at all.

[creecros]

i dont understand the point of the plugin if the user has to decide what to delete. thats scary as hell.

[creecros]

chatgpt lied anyway. shit dont work.