Differences

This shows you the differences between two versions of the page.

--- v5:dictionary:comments [2026/03/22 21:11] – mnewnham
+++ v5:dictionary:comments [2026/03/22 23:45] (current) – mnewnham
@@ Line 2: / Line 2: @@
 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.
-^                       ^ db2 ^mssqlnative^mysql^oci8 ^postgres9^sqlite3^
+^                        ^  db2  ^  mssqlnative  ^  mysql  ^  oci8  ^  postgres9  ^  sqlite3  ^
-^ Table On Creation     |     |           | YES |     |         |     |
+^ Table On Creation      |       |               |  YES    |        |             |           |
-^ Column On Creation    |     |           | YES |     |         |     |
+^ Column On Creation     |       |               |  YES    |        |             |           |
-^ Index On Creation     |     |           | YES |     |         |     |
+^ Index On Creation      |       |               |  YES    |        |             |           |
-^ Table After Creation  | YES |   YES     |     | YES | YES     |     |
+^ Table After Creation   |  YES  |  YES          |         |  YES   |  YES        |           |
-^ Column After Creation | YES |   YES     |     | YES | YES     |     |
+^ Column After Creation  |  YES  |  YES          |         |  YES   |  YES        |           |
-^ Index After Creation  | YES |   YES     |     |     | YES     |     |
+^ Index After Creation   |  YES  |  YES          |         |        |  YES        |           |
+==== Creating Comments During Table Creation ====
+Creation during data dictionary functions such as createTableSql(), addColumnSql() are built as follows:
+=== Table ===
+<code php>
+$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);
+</code>
+==== Column ====
+<code php>
+$datadict = newDataDictionary($db);
+$flds = "SOME_FIELD C(60) NOTNULL COMMENT 'THIS IS A COLUMN COMMENT'";
+$sqlarray = $datadit->alterColumnSql('some_table',$flds);
+$datadict->executeSqlArray($sqlArray);
+</code>
+=== Index ===
+<code php>
+$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);
+</code>
+==== Creating Comments Using XMLSchema ====
+  * 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.
+<code xml>
+<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>
+</code>
+==== Modifying Comments ====
+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.
+===== Creating Comments After Table Creation =====
+For databases that provide the ability to create comments after table creation, the following methods are provided
+==== Table ====
+<WRAP right box>
+== Syntax ==
+    ?string setTableCommentSql(
+        string $tableName,
+        ?string $comment
+    )
+</WRAP>
+**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.
+---------------------------------------
+<code php>
+/*
+* 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);
+</code>
+==== Column ====
+<WRAP right box>
+== Syntax ==
+    ?string setColumnCommentSql(
+        string $tableName,
+        string $columnName,
+        ?string $comment
+    )
+</WRAP>
+**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.
+---------------------------------------
+==== Index ====
+<WRAP right box>
+== Syntax ==
+    ?string setIndexCommentSql(
+        string $tableName,
+        string $indexName,
+        ?string $comment
+    )
+</WRAP>
+**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.
+---------------------------------------
+===== Reading Comments =====
+All databases use the same methods to retrieve comments set in the database.
+==== Table ====
+<WRAP right box>
+== Syntax ==
+    ?string getTableCommentSql(
+        string $tableName
+    )
+</WRAP>
+**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.
+---------------------------------------
+<code php>
+/*
+* 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);
+</code>
+==== Column ====
+<WRAP right box>
+== Syntax ==
+    ?string getColumnCommentSql(
+        string $tableName,
+        string $columnName
+    )
+</WRAP>
+**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.
+---------------------------------------
+==== Index ====
+<WRAP right box>
+== Syntax ==
+    ?string getIndexCommentSql(
+        string $tableName,
+        string $indexName
+    )
+</WRAP>
+**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.
+----------------------------------
+===== Deprecated Functions =====
+The functions **getCommentSql()** and **setCommentSql()** which get/set table comments in the oci8 driver are deprecated and will be removed in a later release. They currently alias **getTableCommentSql()** and **setTableCommentSql()**