With CDbSchema, I can get metadata on tables and on columns.
But I need to access (and in the future, set) the table and column comments.
Actually, I don't see this.
For PgSQL, some changes could be done to get
- the list of tables could be done in CPgsqlSchema::findTableNames() :
(I'm not sure it's the right place as a simple list is returned)
SELECT tablename, obj_description( (SELECT c.oid FROM pg_catalog.pg_class c WHERE c.relname=tablename), 'pg_class') AS comment FROM pg_tables WHERE schemaname != 'information_schema' AND schemaname != 'pg_catalog'
- the list of columns in CPgsqlSchema:: findColumns() :
SELECT a.attname, LOWER(format_type(a.atttypid, a.atttypmod)) AS type, d.adsrc, a.attnotnull, a.atthasdef pg_catalog.col_description(a.attrelid, a.attnum) AS comment, FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=':table' AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = ':schema')) ORDER BY a.attnum ;
In MySQL:
SELECT COLUMN_NAME AS 'field', DATA_TYPE AS 'type', IS_NULLABLE AS 'null', COLUMN_KEY AS 'key', COLUMN_DEFAULT AS 'default', COLUMN_COMMENT AS 'comment' FROM information_schema.columns WHERE TABLE_SCHEMA=':schema' AND TABLE_NAME=':table';
and
SELECT table_name AS tablename, table_comment AS comment FROM information_schema.tables WHERE table_schema=':schema_name';
or
SHOW TABLE STATUS;
I know how to set comments on PgSQL (
COMMENT ON TABLE…), but in MySQL, I don’t know.