Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7acc237

Browse files
committed
This patch implements ORACLE's COMMENT SQL command.
>From the ORACLE 7 SQL Language Reference Manual:-----------------------------------------------------COMMENTPurpose:To add a comment about a table, view, snapshot, orcolumn into the data dictionary.Prerequisites:The table, view, or snapshot must be in your ownschemaor you must have COMMENT ANY TABLE system privilege.Syntax:COMMENT ON [ TABLE table ] | [ COLUMN table.column] IS 'text'You can effectively drop a comment from the databaseby setting it to the empty string ''.-----------------------------------------------------Example:COMMENT ON TABLE workorders IS 'Maintains base records for workorder information';COMMENT ON COLUMN workorders.hours IS 'Number of hours the engineer worked on the task';to drop a comment:COMMENT ON COLUMN workorders.hours IS '';The current patch will simply perform the insert intopg_description, as per the TODO. And, of course, whenthe table is dropped, any comments relating to itor any of its attributes are also dropped. I haven'tlooked at the ODBC source yet, but I do know froman ODBC client standpoint that the standard doessupport the notion of table and column comments.Hopefully the ODBC driver is already fetching thesevalues from pg_description, but if not, it should betrivial.Hope this makes the grade,Mike Mascari(mascarim@yahoo.com)
1 parent55fa71a commit7acc237

File tree

12 files changed

+287
-21
lines changed

12 files changed

+287
-21
lines changed

‎src/backend/catalog/heap.c

Lines changed: 153 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.103 1999/10/07 05:48:03 tgl Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.104 1999/10/15 01:49:39 momjian Exp $
1111
*
1212
*
1313
* INTERFACE ROUTINES
@@ -38,6 +38,7 @@
3838
#include"catalog/index.h"
3939
#include"catalog/indexing.h"
4040
#include"catalog/pg_attrdef.h"
41+
#include"catalog/pg_description.h"
4142
#include"catalog/pg_index.h"
4243
#include"catalog/pg_inherits.h"
4344
#include"catalog/pg_ipl.h"
@@ -67,6 +68,7 @@ static void AddNewRelationTuple(Relation pg_class_desc,
6768
Relationnew_rel_desc,Oidnew_rel_oid,unsignednatts,
6869
charrelkind,char*temp_relname);
6970
staticvoidAddToNoNameRelList(Relationr);
71+
7072
staticvoidDeleteAttributeTuples(Relationrel);
7173
staticvoidDeleteRelationTuple(Relationrel);
7274
staticvoidDeleteTypeTuple(Relationrel);
@@ -861,10 +863,11 @@ heap_create_with_catalog(char *relname,
861863
*2)remove inheritance information
862864
*3)remove indexes
863865
*4)remove pg_class tuple
864-
*5)remove pg_attribute tuples
865-
*6)remove pg_type tuples
866-
*7)RemoveConstraints ()
867-
*8)unlink relation
866+
*5)remove pg_attribute tuples and related descriptions
867+
* 6) remove pg_description tuples
868+
*7)remove pg_type tuples
869+
*8)RemoveConstraints ()
870+
*9)unlink relation
868871
*
869872
* old comments
870873
*Except for vital relations, removes relation from
@@ -1269,18 +1272,152 @@ DeleteAttributeTuples(Relation rel)
12691272
attnum++)
12701273
{
12711274
if (HeapTupleIsValid(tup=SearchSysCacheTupleCopy(ATTNUM,
1272-
ObjectIdGetDatum(RelationGetRelid(rel)),
1273-
Int16GetDatum(attnum),
1275+
ObjectIdGetDatum(RelationGetRelid(rel)),
1276+
Int16GetDatum(attnum),
12741277
0,0)))
12751278
{
1276-
heap_delete(pg_attribute_desc,&tup->t_self,NULL);
1277-
pfree(tup);
1279+
DeleteComments(tup->t_data->t_oid);
1280+
heap_delete(pg_attribute_desc,&tup->t_self,NULL);
1281+
pfree(tup);
12781282
}
12791283
}
12801284

12811285
heap_close(pg_attribute_desc,RowExclusiveLock);
12821286
}
12831287

1288+
/* ----------------------------------------------------------
1289+
* CreateComments
1290+
*
1291+
* This routine is handed the oid and the command associated
1292+
* with that id and will insert, update, or delete (if the
1293+
* comment is an empty string or a NULL pointer) the associated
1294+
* comment from the system cataloge, pg_description.
1295+
*
1296+
* ----------------------------------------------------------
1297+
*/
1298+
1299+
void
1300+
CreateComments(Oidoid,char*comment)
1301+
{
1302+
1303+
Relationdescription;
1304+
TupleDesctupDesc;
1305+
HeapScanDescscan;
1306+
ScanKeyDataentry;
1307+
HeapTupledesctuple,searchtuple;
1308+
Datumvalues[Natts_pg_description];
1309+
charnulls[Natts_pg_description];
1310+
charreplaces[Natts_pg_description];
1311+
boolmodified= false;
1312+
inti;
1313+
1314+
/*** Open pg_description, form a new tuple, if necessary ***/
1315+
1316+
description=heap_openr(DescriptionRelationName,RowExclusiveLock);
1317+
tupDesc=description->rd_att;
1318+
if ((comment!=NULL)&& (strlen(comment)>0)) {
1319+
for (i=0;i<Natts_pg_description;i++) {
1320+
nulls[i]=' ';
1321+
replaces[i]='r';
1322+
values[i]= (Datum)NULL;
1323+
}
1324+
i=0;
1325+
values[i++]=ObjectIdGetDatum(oid);
1326+
values[i++]= (Datum)fmgr(F_TEXTIN,comment);
1327+
}
1328+
1329+
/*** Now, open pg_description and attempt to find the old tuple ***/
1330+
1331+
ScanKeyEntryInitialize(&entry,0x0,Anum_pg_description_objoid,F_OIDEQ,
1332+
ObjectIdGetDatum(oid));
1333+
scan=heap_beginscan(description, false,SnapshotNow,1,&entry);
1334+
searchtuple=heap_getnext(scan,0);
1335+
1336+
/*** If a previous tuple exists, either delete it or prepare a replacement ***/
1337+
1338+
if (HeapTupleIsValid(searchtuple)) {
1339+
1340+
/*** If the comment is blank, call heap_delete, else heap_replace ***/
1341+
1342+
if ((comment==NULL)|| (strlen(comment)==0)) {
1343+
heap_delete(description,&searchtuple->t_self,NULL);
1344+
}else {
1345+
desctuple=heap_modifytuple(searchtuple,description,values,nulls,replaces);
1346+
setheapoverride(true);
1347+
heap_replace(description,&searchtuple->t_self,desctuple,NULL);
1348+
setheapoverride(false);
1349+
modified= TRUE;
1350+
}
1351+
1352+
}else {
1353+
desctuple=heap_formtuple(tupDesc,values,nulls);
1354+
heap_insert(description,desctuple);
1355+
modified= TRUE;
1356+
}
1357+
1358+
/*** Complete the scan, update indices, if necessary ***/
1359+
1360+
heap_endscan(scan);
1361+
1362+
if (modified) {
1363+
if (RelationGetForm(description)->relhasindex) {
1364+
Relationidescs[Num_pg_description_indices];
1365+
1366+
CatalogOpenIndices(Num_pg_description_indices,Name_pg_description_indices,idescs);
1367+
CatalogIndexInsert(idescs,Num_pg_description_indices,description,desctuple);
1368+
CatalogCloseIndices(Num_pg_description_indices,idescs);
1369+
}
1370+
pfree(desctuple);
1371+
1372+
}
1373+
1374+
heap_close(description,RowExclusiveLock);
1375+
1376+
}
1377+
1378+
/* --------------------------------
1379+
* DeleteComments
1380+
*
1381+
* This routine is used to purge any comments
1382+
* associated with the Oid handed to this routine,
1383+
* regardless of the actual object type. It is
1384+
* called, for example, when a relation is destroyed.
1385+
* --------------------------------
1386+
*/
1387+
1388+
void
1389+
DeleteComments(Oidoid)
1390+
{
1391+
1392+
Relationdescription;
1393+
TupleDesctupDesc;
1394+
ScanKeyDataentry;
1395+
HeapScanDescscan;
1396+
HeapTuplesearchtuple;
1397+
1398+
description=heap_openr(DescriptionRelationName,RowExclusiveLock);
1399+
tupDesc=description->rd_att;
1400+
1401+
/*** Now, open pg_description and attempt to find the old tuple ***/
1402+
1403+
ScanKeyEntryInitialize(&entry,0x0,Anum_pg_description_objoid,F_OIDEQ,
1404+
ObjectIdGetDatum(oid));
1405+
scan=heap_beginscan(description, false,SnapshotNow,1,&entry);
1406+
searchtuple=heap_getnext(scan,0);
1407+
1408+
/*** If a previous tuple exists, delete it ***/
1409+
1410+
if (HeapTupleIsValid(searchtuple)) {
1411+
heap_delete(description,&searchtuple->t_self,NULL);
1412+
}
1413+
1414+
/*** Complete the scan, update indices, if necessary ***/
1415+
1416+
heap_endscan(scan);
1417+
heap_close(description,RowExclusiveLock);
1418+
1419+
}
1420+
12841421
/* --------------------------------
12851422
*DeleteTypeTuple
12861423
*
@@ -1471,6 +1608,13 @@ heap_destroy_with_catalog(char *relname)
14711608
*/
14721609
DeleteAttributeTuples(rel);
14731610

1611+
/* ----------------
1612+
* delete comments
1613+
* ----------------
1614+
*/
1615+
1616+
DeleteComments(RelationGetRelid(rel));
1617+
14741618
if (istemp)
14751619
remove_temp_relation(rid);
14761620

‎src/backend/catalog/indexing.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/catalog/indexing.c,v 1.47 1999/09/30 10:31:42 wieck Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/catalog/indexing.c,v 1.48 1999/10/15 01:49:39 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -55,6 +55,9 @@ char *Name_pg_trigger_indices[Num_pg_trigger_indices] = {TriggerRelidIndex,
5555
TriggerConstrNameIndex,
5656
TriggerConstrRelidIndex};
5757

58+
char*Name_pg_description_indices[Num_pg_description_indices]= {DescriptionObjIndex};
59+
60+
5861

5962
staticHeapTupleCatalogIndexFetchTuple(RelationheapRelation,
6063
Relationidesc,

‎src/backend/commands/creatinh.c

Lines changed: 49 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/creatinh.c,v 1.48 1999/10/03 23:55:27 tgl Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/creatinh.c,v 1.49 1999/10/15 01:49:39 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -16,10 +16,12 @@
1616

1717
#include"access/heapam.h"
1818
#include"catalog/catname.h"
19+
#include"catalog/indexing.h"
1920
#include"catalog/heap.h"
2021
#include"catalog/pg_inherits.h"
2122
#include"catalog/pg_ipl.h"
2223
#include"catalog/pg_type.h"
24+
#include"catalog/pg_description.h"
2325
#include"commands/creatinh.h"
2426
#include"utils/syscache.h"
2527

@@ -232,6 +234,52 @@ TruncateRelation(char *name)
232234
heap_truncate(name);
233235
}
234236

237+
/*------------------------------------------------------------------
238+
* CommentRelation --
239+
* Adds a comment to pg_description for the associated
240+
* relation or relation attribute.
241+
*
242+
* Note:
243+
* The comment is dropped on the relation or attribute if
244+
* the comment is an empty string.
245+
*------------------------------------------------------------------
246+
*/
247+
void
248+
CommentRelation(char*relname,char*attrname,char*comments)
249+
{
250+
251+
Relationrelation;
252+
HeapTupleattrtuple;
253+
Oidoid;
254+
255+
/*** First ensure relname is valid ***/
256+
257+
relation=heap_openr(relname,AccessShareLock);
258+
259+
/*** Now, if an attribute was specified, fetch its oid, else use relation's oid ***/
260+
261+
if (attrname!=NULL) {
262+
attrtuple=SearchSysCacheTuple(ATTNAME,ObjectIdGetDatum(relation->rd_id),
263+
PointerGetDatum(attrname),0,0);
264+
if (!HeapTupleIsValid(attrtuple)) {
265+
elog(ERROR,"CommentRelation: attribute \"%s\" is not an attribute of relation \"%s\"",
266+
attrname,relname);
267+
}
268+
oid=attrtuple->t_data->t_oid;
269+
}else {
270+
oid=RelationGetRelid(relation);
271+
}
272+
273+
/*** Call CreateComments() to create/drop the comments ***/
274+
275+
CreateComments(oid,comments);
276+
277+
/*** Now, close the heap relation ***/
278+
279+
heap_close(relation,AccessShareLock);
280+
281+
}
282+
235283
/*
236284
* MergeAttributes
237285
*Returns new schema given initial schema and supers.

‎src/backend/parser/gram.y

Lines changed: 32 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.108 1999/10/07 04:23:12 tgl Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.109 1999/10/15 01:49:41 momjian Exp $
1414
*
1515
* HISTORY
1616
* AUTHORDATEMAJOR EVENT
@@ -118,7 +118,7 @@ Oidparam_type(int t); /* used in parse_expr.c */
118118
%type <node>stmt,
119119
AddAttrStmt, ClosePortalStmt,
120120
CopyStmt, CreateStmt, CreateAsStmt, CreateSeqStmt, DefineStmt, DestroyStmt,
121-
TruncateStmt,
121+
TruncateStmt, CommentStmt,
122122
ExtendStmt, FetchStmt,GrantStmt, CreateTrigStmt, DropTrigStmt,
123123
CreatePLangStmt, DropPLangStmt,
124124
IndexStmt, ListenStmt, UnlistenStmt, LockStmt, OptimizableStmt,
@@ -314,7 +314,7 @@ Oidparam_type(int t); /* used in parse_expr.c */
314314
*/
315315
%tokenABORT_TRANS, ACCESS, AFTER, AGGREGATE, ANALYZE,
316316
BACKWARD, BEFORE, BINARY,
317-
CACHE, CLUSTER, COPY, CREATEDB, CREATEUSER, CYCLE,
317+
CACHE, CLUSTER,COMMENT,COPY, CREATEDB, CREATEUSER, CYCLE,
318318
DATABASE, DELIMITERS, DO,
319319
EACH, ENCODING, EXCLUSIVE, EXPLAIN, EXTEND,
320320
FORWARD, FUNCTION, HANDLER,
@@ -402,6 +402,7 @@ stmt : AddAttrStmt
402402
| DefineStmt
403403
| DestroyStmt
404404
| TruncateStmt
405+
| CommentStmt
405406
| DropPLangStmt
406407
| DropTrigStmt
407408
| DropUserStmt
@@ -1539,6 +1540,32 @@ TruncateStmt: TRUNCATE TABLE relation_name
15391540
}
15401541
;
15411542

1543+
/*****************************************************************************
1544+
*
1545+
* QUERY:
1546+
* comment on [ table <relname> | column <relname>.<attribute> ]
1547+
* is 'text'
1548+
*
1549+
*****************************************************************************/
1550+
1551+
CommentStmt:COMMENT ON COLUMN relation_name '.' attr_name IS Sconst
1552+
{
1553+
CommentStmt *n = makeNode(CommentStmt);
1554+
n->relname = $4;
1555+
n->attrname = $6;
1556+
n->comment = $8;
1557+
$$ = (Node *) n;
1558+
}
1559+
| COMMENT ON TABLE relation_name IS Sconst
1560+
{
1561+
CommentStmt *n = makeNode(CommentStmt);
1562+
n->relname = $4;
1563+
n->attrname = NULL;
1564+
n->comment = $6;
1565+
$$ = (Node *) n;
1566+
}
1567+
;
1568+
15421569
/*****************************************************************************
15431570
*
15441571
*QUERY:
@@ -5011,6 +5038,7 @@ ColId: IDENT{ $$ = $1; }
50115038
| BACKWARD{ $$ = "backward"; }
50125039
| BEFORE{ $$ = "before"; }
50135040
| CACHE{ $$ = "cache"; }
5041+
| COMMENT{ $$ = "comment"; }
50145042
| COMMITTED{ $$ = "committed"; }
50155043
| CONSTRAINTS{ $$ = "constraints"; }
50165044
| CREATEDB{ $$ = "createdb"; }
@@ -5081,6 +5109,7 @@ ColId: IDENT{ $$ = $1; }
50815109
| TIMEZONE_HOUR{ $$ = "timezone_hour"; }
50825110
| TIMEZONE_MINUTE{ $$ = "timezone_minute"; }
50835111
| TRIGGER{ $$ = "trigger"; }
5112+
| TRUNCATE{ $$ = "truncate"; }
50845113
| TRUSTED{ $$ = "trusted"; }
50855114
| TYPE_P{ $$ = "type"; }
50865115
| VALID{ $$ = "valid"; }

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp