PDO integration is rough

[athoune]

I'm trying to use Mysql PDO connector (with a Mariadb 10.1), with XHGui 0.13.0 :

• The lazy table creation never throw error in src/xhgui/vendor/perftools/xhgui-collector/src/Xhgui/Saver/Pdo.php
• Table name is escaped with ", and mysql cli doesn't like it, ` seems to be more standard
• The id column is not specified enough : ERROR 1170 (42000): BLOB/TEXT column 'id' used in key specification without a key length

[glensc]

Now that xhgui-collector is imported back to this project:

• Import perftools/xhgui-collector with complete history #310

can perhaps use doctrine or cakephp/database abstractions for database.

altho these indicated by you could be just fixed to be compatible with sqlite/mysql/pgsql simple changes.

[glensc]

The lazy table creation never throw error in src/xhgui/vendor/perftools/xhgui-collector/src/Xhgui/Saver/Pdo.php

could you please elaborate what that statement means?

[glensc]

Table name is escaped with ", and mysql cli doesn't like it, ` seems to be more standard

seems pgsql/sqlite accept ", MySQL accepts `, so the only portable way without too much abstraction is to remove escaping at all, i.e use bare words.

some alternative solutions is to let it be configurable

ref:

• https://evertpot.com/writing-sql-for-postgres-mysql-sqlite/

Quoting

In MySQL and SQLite you use backticks for identifiers. PostgreSQL uses single quotes. SQlite can also use single quotes here if the result is unambigious, but I would strongly suggest to avoid that.

[glensc]

The id column is not specified enough : ERROR 1170 (42000): BLOB/TEXT column 'id' used in key specification without a key length

changed id column to match MongoId length: 24 characters:

• https://www.php.net/manual/en/mongodb-bson-objectid.construct.php

NOTE: this will break on 2038/2106 year (depending on cpu bits). likely fix is to increase column length to be 25 characters. but deal with the problem when it's due :)

[athoune]

The lazy table creation never throw error in src/xhgui/vendor/perftools/xhgui-collector/src/Xhgui/Saver/Pdo.php
could you please elaborate what that statement means?

My ugly debug is :

$ok = $pdo->exec(sprintf(self::TABLE_DDL, $table));
error_log("Create table : $ok");

Throwing an exception when $ok is false can be more simple.

[athoune]

I just inject my first xhprof trace to Mysql with the 322-pdo branch !

[glensc]

The lazy table creation never throw error in src/xhgui/vendor/perftools/xhgui-collector/src/Xhgui/Saver/Pdo.php

so your statement means table creation errors are not checked!

[glensc]

Besides table name, GET column also needs to be escaped for MySQL (at least for 5.7)

So, omitting escaping is out of the question. Still need to abstract db layer

[glensc]

also, pgsql fails with:

1) XHGui\Test\Controller\RunTest::testIndexEmpty
PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR:  LIMIT #,# syntax is not supported
LINE 20:           LIMIT 0, 25

so, I think currently only sqlite was really supported?

[athoune]

Ok, you are speaking about this part of Doctrine : https://github.com/doctrine/dbal/ not the ORM part.

[glensc]

@athoune can you find compatible quoting for table names and column names that are valid for all common pdo modules:

• mysql
• pgsql
• sqlite

i think no such exist, and can't omit quoting as GET is reserved keyword in mysql?

[athoune]

The Doctrine's DBAL handles all the little differences between databases :

• https://github.com/doctrine/dbal/blob/2.11.x/lib/Doctrine/DBAL/Schema/MySqlSchemaManager.php
• https://github.com/doctrine/dbal/blob/2.11.x/lib/Doctrine/DBAL/Schema/PostgreSqlSchemaManager.php
• https://github.com/doctrine/dbal/blob/2.11.x/lib/Doctrine/DBAL/Schema/SqliteSchemaManager.php

I don't know what is the simplest way to do, trusting Doctrine job for true universal DB handling, or tiny handcrafted code.

[glensc]

@athoune can test this PR?

• Use ansi quoting for columns and table name #336