Need help with database statement

Plugins
1

Hello, in a plugin, I am trying to show the database size but cannot get the statement to work. In phpMyAdmin it works fine, but the syntax is confusing in Kanboard.

I noticed in Kanboard, SQLite has a function for the database size which is fine, but using PicoDB for MySQL and Postgres is not working.

    public function databaseSize()
    {
        // SELECT table_schema "myppworkspace", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables WHERE table_schema = 'myppworkspace' GROUP BY table_schema;

        if (DB_DRIVER === 'postgres') {
            return $this->db->execute('');
        } elseif (DB_DRIVER === 'mysql') {
            return $this->db->execute('
                SELECT
    table_schema AS "Database",
    ROUND(
        SUM(data_length + index_length) / 1024 / 1024,
        1
    ) "MB"
FROM
    information_schema.tables
WHERE
    table_schema = "myppworkspace";
            ');
        }
    }

Errors:

PHP Fatal error:  Uncaught Error: Object of class PDOStatement could not be converted to string

PHP Parse error:  syntax error, unexpected 'myppworkspace' (T_STRING), expecting ')'

Kanboard cant seem to find WHERE table_schema = myppworkspace; - I have tried "myppworkspace" and 'myppworkspace' and also just myppworkspace. Like I said in phpMyAdmin it works and I get the total MB for the database.

I tried to build the query, but I only got as far as return $this->db->table('information_schema.tables') because I cannot find a way to do the ROUND and SUM function.

Can anybody help?

2

Not sure, but with

SELECT table_schema AS "Database", ...

you temporarily rename table_schema to Database, and later in the same query, you use the original name

WHERE table_schema = "myppworkspace";
3

oh I see, but then why does it work in phpMyAdmin? I will try without renaming

EDIT: na it doesnt work… I tried by removing the line, and then by removing the AS
I get
Internal Error: SQL Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'myppworkspace' in 'where clause'

4

Maybe you have to qualify, partially or full:

WHERE tables.table_schema = "myppworkspace";
5

na, same issue… I’m going to try building it using pdo again and see

6

I assume it cannot be done in one step, or it’s too complicated.

But you could do something like this:

public function getSize($column) 
{
    return $this->db->table('information_schema.tables')
        ->eq('tables.table_schema', 'kanboard')
        ->sum($column) / 1024 / 1024;
}

$dbsize = 
    $this->getSize('tables.data_length') + 
    $this->getSize('tables.index_length');

HTH
abu

1 Like
7

oh wow, thank you so much! I have spent two days trying to sort this! Fantastic… it gives me the size, now I can use php to round the result.

Do you think the same code will work for postgres database?

8

image
image1503Ă—469 40.9 KB

finally… big thanks to @creecros and @alfredb to get this feature sorted

9

No clue, really. I never had to use Postgres database.

1 Like
10

A final amendment:

public function getSize($column) 
{
    return $this->db->table('information_schema.tables')
        ->eq('tables.table_schema', 'kanboard')
        ->sum($column);
}

Pull the / 1024 / 1024 out of the function and do it only once with the formatting.

BTW:

Confusing. Do I have write access now, or am I just mentioned a contributor?

11

I added you both as contributors and was going to mention you both in the initial release so GitHub adds your mentions. I have added you for write access too… check how I formatted it

I left the original code, but in the tooltip it is formatted raw and in the page, it is half rounded up in MB

Check my code, if the code is worng then I will do the suggested amendment, otherwise I think I already formatted it before your comment today :slight_smile:

12

cool, I will leave it as it is and see if anyone creates an issue. A lot to do on this plugin yet so it wont be released for a while I dont think

13

Maybe it was not clear. It is nothing wrong there, but the conversion to megabyte could do once before using the final result, instead of twice, once for each sub-result. That’s all.

14

ok great but please explain where it is formatted twice? Because in the template I have just rounded the MB (Im still learning :slight_smile: )

15

Something like

MB = (bytes + bytes) / 1024 / 1024

instead of

MB = bytes / 1024 / 1024 + bytes / 1024 / 1024

But it’s done now. :wink:

1 Like