ADOdb

Database support for adding comments varies from full support (IBM DB2, PostgreSQL) to none (SQLite) with differing implementation methods. Databases that support it do it via 1 of 2 methods, Either be adding the comment as part of the table creation and modification process, or by adding a comment to a previously created table. In addition, databases have varying levels of support for adding comments to tables, columns and indexes. The following matrix shows available methods across supported databases.

Creation during data dictionary functions such as createTableSql(), addColumnSql() are built as follows:

$datadict = newDataDictionary($db);
 
$flds = 'an ADOdb fields definition...';
$tableOpts = ['COMMENT' => 'This is a table Comment' ];
 
$sqlarray = $datadict->createTableSql('some_table',$flds, $tableopts);
 
$datadict->executeSqlArray($sqlArray);

$datadict = newDataDictionary($db);
 
$flds = "SOME_FIELD C(60) NOTNULL COMMENT 'THIS IS A COLUMN COMMENT'";
 
$sqlarray = $datadit->alterColumnSql('some_table',$flds);
 
$datadict->executeSqlArray($sqlArray);

$datadict = newDataDictionary($db);
 
$flds = "VARCHAR_FIELD, DATE_FIELD, INTEGER_FIELD";
$indexOptions = array(
    'UNIQUE',
    'COMMENT' => 'THIS IS AN INDEX COMMENT'
);
 
$sqlarray = $datadict->addIndexSql('some_index', 'some_table',$flds, $indexopts);
 
$datadict->executeSqlArray($sqlArray);

• Comments can now be added to table, column and index elements and are added if possible to the database schema. If the database does not support the operation they are ignored.
• This is done by adding <comment></comment> as part of the element
• The <descr> tag no longer attempts to create comments for tables and columns. Any DESCR tags are used solely as XML descriptions.

<table name="xml_schema_test">
  <descr>A test table for xmlschema unit testing</descr>
  <comment>XML SCHEMA COMMENT</comment> 
  <field name="id" type="I">
  <descr>A unique ID assigned to each record.</descr>
  <KEY/>
  <AUTOINCREMENT/>
  <comment>This is a column comment</comment>
  </field> 
  <index name="index1">
     <col>id</col>
     <comment>This is an index comment</comment>
  </index>
 
</table>

Databases that support creating comments via dictionary table functions have limited ability to modify comments after creation. Only columns comments can be modified, and this requires the entire column specification to be provided.

For databases that provide the ability to create comments after table creation, the following methods are provided

  ?string setTableCommentSql(
      string $tableName,
      ?string $comment
  )

ADODataDictionary::setTableCommentSql() returns an SQL Statement that can be executed to set or update a table comment in the currently connected database. If the database does not support adding a comment this way, the method returns NULL.

/*
* Assume connection to IBM DB2 database
*/
 
$table = 'some_table';
$comment = 'This is a table comment';
 
$datadict = newDataDictionary($db);
$tableCommentSql = $datadict->setTableCommentSql($table, $comment);
 
/*
* Returns  "COMMENT ON TABLE some_table IS 'This is a table comment'"
*/
 
$db->execute($tableCommentSql);

  ?string setColumnCommentSql(
      string $tableName,
      string $columnName,
      ?string $comment
  )

ADODataDictionary::setColumnCommentSql() returns an SQL Statement that can be executed to set or update a column comment in the currently connected database. If the database does not support adding a comment this way, the method returns NULL.

  ?string setIndexCommentSql(
      string $tableName,
      string $indexName,
      ?string $comment
  )

ADODataDictionary::setIndexCommentSql() returns an SQL Statement that can be executed to set or update an index comment in the currently connected database. If the database does not support adding a comment this way, the method returns NULL.

All databases use the same methods to retrieve comments set in the database.

  ?string getTableCommentSql(
      string $tableName
  )

ADODataDictionary::getTableCommentSql() returns an SQL Statement that can be executed to retrieve a table comment from the currently connected database. If the database does not support retrieving a comment this way, the method returns NULL.

/*
* Assume connection to a PostgresQL database
*/
 
$table = 'some_table';
 
 
$datadict = newDataDictionary($db);
$tableCommentSql = $datadict->getTableCommentSql($table);
 
/*
* Returns  "SELECT obj_description('public.some_table'::regclass, 'pg_class')"
*/
 
$comment = $db->getOne($tableCommentSql);

  ?string getColumnCommentSql(
      string $tableName,
      string $columnName
  )

ADODataDictionary::getColumnCommentSql() returns an SQL Statement that can be executed to retrieve a column comment in the currently connected database. If the database does not support retrieving a comment this way, the method returns NULL.

  ?string getIndexCommentSql(
      string $tableName,
      string $indexName
  )

ADODataDictionary::getIndexCommentSql() returns an SQL Statement that can be executed to retrieve an index comment from the currently connected database. If the database does not support retrieving a comment this way, the method returns NULL.

The functions getCommentSql() and setCommentSql() which get/set table comments in the oci8 driver are deprecated and will be removed in a later release.