Helper Help

[aljawaid]

Helper:

<?php

namespace Kanboard\Plugin\ContentCleaner\Helper;

use Kanboard\Core\Base;

class CleaningHelper extends Base
{
    public function countTablesDB()
    {
        $db_size = $this->applicationCleaningModel->countTables();

        return $db_size;
    }
}

Model:

class ApplicationCleaningModel extends Base
{
    public function countTables()
    {
        //return $this->db->execute('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = `myppworkspace`');

        $table_schema = $this->db->table(self::TABLE);
        return $this->db->execute('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = `'. $table_schema .'`');
    }
}

Calling:
<?= $this->helper->cleaningHelper->countTablesDB() ?>

I am getting error:
Uncaught Error: Undefined class constant 'TABLE'

[aljawaid]

@creecros could you please have a look?

[creecros]

Example:

$count = $db->table('information_schema.tables')
            ->where('table_schema', '=', 'mydatabase')
            ->where('TABLE_TYPE', '=', 'BASE TABLE')
            ->count();

[aljawaid]

Cool thanks for that example, I will try it shortly. I agree with you and thought about picodb but I couldn't find a part for the full dB. Your example looks like a good starting point so I will check that out.

Would the result be return $count?

[creecros]

theoretically, it should be the 59, right?
i could be wrong on the where() method. worth a try though. if not, i know ->eq() works

dont hold me to this, you may have been on the correct path, just explore it more otherwise you will have to account for all db types. im litteraly going off the top of my head on a crappy memory at the moment

[aljawaid]

yes, my result should be 59... I will try it out using return $count

[aljawaid]

This seems to give me 59 without any errors... I think we have got somewhere on the table count

public function countTables()
    {
        //return $this->db->execute('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = `myppworkspace`');
        //SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'myppworkspace' AND TABLE_TYPE = 'BASE TABLE';

        //$table_schema = DB_NAME;
        //return $this->db->execute('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = `'. DB_NAME .'` AND TABLE_TYPE = `BASE TABLE`');

        return $this->db->table('information_schema.tables')
            ->eq('table_schema', 'myppworkspace')
            ->eq('TABLE_TYPE', 'BASE TABLE')
            ->count();
    }

[creecros]

So, this is promising.

<?php

$file = __DIR__.'/../../../../app/Schema/Mysql.php'; // replace with your file path

$tables = array();
$columns = array();

$sql = file_get_contents($file);

// extract table names
preg_match_all("/CREATE\s+TABLE\s+`?(\w+)`?/i", $sql, $matches);
$tables = $matches[1];

// extract column names
preg_match_all("/ALTER\s+TABLE\s+`?(\w+)`?.*ADD\s+`?(\w+)`?/i", $sql, $matches);
foreach ($matches[1] as $i => $table) {
    $column = $matches[2][$i];
    $columns[$table][] = $column;
}

// extract renamed table names
preg_match_all("/ALTER\s+TABLE\s+`?(\w+)`?\s+RENAME\s+TO\s+`?(\w+)`?/i", $sql, $matches);
foreach ($matches[1] as $i => $old_table) {
    $new_table = $matches[2][$i];
    // update table name in $tables array
    $key = array_search($old_table, $tables);
    if ($key !== false) {
        $tables[$key] = $new_table;
    }
    // update table name in $columns array
    if (isset($columns[$old_table])) {
        $columns[$new_table] = $columns[$old_table];
        unset($columns[$old_table]);
    }
}

// extract dropped table names
preg_match_all("/DROP\s+TABLE\s+`?(\w+)`?/i", $sql, $matches);
foreach ($matches[1] as $table) {
    // remove table name from $tables array
    $key = array_search($table, $tables);
    if ($key !== false) {
        unset($tables[$key]);
    }
    // remove table name from $columns array
    if (isset($columns[$table])) {
        unset($columns[$table]);
    }
}

// print the results
print_r(count($tables).'<br>');
foreach ($tables as $table) { print_r($table.'<br>'); }
//print_r(count($columns));

?>

It comes up with 47 Tables:

47
sessions
predefined_task_descriptions
invites
column_has_restrictions
project_role_has_restrictions
project_has_roles
column_has_move_restrictions
tags
task_has_tags
project_has_files
task_has_external_links
password_reset
project_has_groups
groups
group_has_users
user_has_metadata
project_has_metadata
task_has_metadata
project_has_notification_types
user_has_unread_notifications
user_has_notification_types
custom_filters
plugin_schema_versions
project_daily_stats
currencies
transitions
links
task_has_links
subtask_time_tracking
swimlanes
project_daily_summaries
project_activities
settings
user_has_notifications
task_has_subtasks
task_has_files
project_has_categories
remember_me
last_logins
users
projects
project_has_users
columns
tasks
comments
actions
action_has_params

[creecros]

how so? ..\Schema\Mysql.php is not the db, it is the file used to build the db.

[creecros]

a better explanation would be, its no different than you opening that file yourself, reading it with your own eyes, and writing down the name of every table you see.

[aljawaid]

That makes a lot more sense now.... we are just reading the file containing database setup code.... we are not actually using the database. I understand now. Thanks

[aljawaid]

ContentCleaner/Helper/DefaultTableHelper.php

Line 24 in 0d9e8af

$file = $_SERVER['DOCUMENT_ROOT'].'/app/Schema/Mysql.php';

This works for me, but is it correct? If I use your original suggestion of __DIR__ then the path includes the plugins folder and plugin folder

[creecros]

DIR is the base path of where the code is, so when I was testing it, it was valid for where it was at the time. it will be different if it is in a helper now.