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

Commit85c165c

Browse files
committed
New findoidjoins examines oid columns to find join relationships.
1 parentffb120e commit85c165c

File tree

8 files changed

+266
-1
lines changed

8 files changed

+266
-1
lines changed

‎contrib/README

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,11 @@ earthdistance -
1818
Operator for computing earth distance for two points
1919
by Hal Snyder <hal@vailsys.com>
2020

21+
findoidjoins -
22+
Finds the joins used by oid columns by examining the actual
23+
values in the oid columns and row oids.
24+
by Bruce Momjian <root@candle.pha.pa.us>
25+
2126
fulltextindex -
2227
Full text indexing using triggers
2328
by Maarten Boekhold <maartenb@dutepp0.et.tudelft.nl>

‎contrib/findoidjoins/Makefile

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
#
2+
# Makefile, requires pgsql/contrib/pginterface
3+
#
4+
#
5+
PGINTERFACE = pginterface.o halt.o# these have to be in your library search path
6+
TARGET = findoidjoins
7+
CFLAGS = -g -Wall -I. -I../../src/interfaces/libpq -I/usr/local/pgsql/include
8+
LDFLAGS = -L/usr/local/pgsql/lib -lpq
9+
10+
all :$(TARGET)
11+
12+
findoidjoins:$(PGINTERFACE) findoidjoins.c
13+
gcc -o$@$(CFLAGS)$@.c$(PGINTERFACE)$(LDFLAGS)
14+
15+
clean:
16+
rm -f*.o$(TARGET) log core
17+
18+
install:
19+
install -s -o bin -g bin$(TARGET) /usr/local/pgsql/bin
20+

‎contrib/findoidjoins/README

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,68 @@
1+
2+
3+
findoidjoins
4+
5+
This program scans the a database, and prints oid fields, and the tables
6+
they join to. PostgreSQL version 6.3.2 crashes with aggregates on
7+
views, so I have removed the view pg_user from the list of relations to
8+
examine.
9+
10+
Run on am empty database, it returns the system join relationships:
11+
12+
---------------------------------------------------------------------------
13+
14+
Join pg_aggregate.aggfinaltype => pg_proc.oid
15+
Join pg_aggregate.aggfinaltype => pg_type.oid
16+
Join pg_aggregate.aggowner => pg_proc.oid
17+
Join pg_aggregate.aggbasetype => pg_proc.oid
18+
Join pg_aggregate.aggbasetype => pg_type.oid
19+
Join pg_aggregate.aggtranstype1 => pg_proc.oid
20+
Join pg_aggregate.aggtranstype1 => pg_type.oid
21+
Join pg_aggregate.aggtranstype2 => pg_type.oid
22+
Join pg_am.amowner => pg_proc.oid
23+
Join pg_amop.amopid => pg_am.oid
24+
Join pg_amop.amopopr => pg_operator.oid
25+
Join pg_amop.amopclaid => pg_opclass.oid
26+
Join pg_amproc.amproc => pg_operator.oid
27+
Join pg_amproc.amproc => pg_proc.oid
28+
Join pg_amproc.amopclaid => pg_opclass.oid
29+
Join pg_amproc.amopclaid => pg_operator.oid
30+
Join pg_amproc.amopclaid => pg_proc.oid
31+
Join pg_amproc.amid => pg_am.oid
32+
Join pg_attribute.attrelid => pg_class.oid
33+
Join pg_attribute.atttypid => pg_type.oid
34+
Join pg_class.relam => pg_am.oid
35+
Join pg_class.reltype => pg_type.oid
36+
Join pg_class.relowner => pg_proc.oid
37+
Join pg_description.objoid => pg_proc.oid
38+
Join pg_description.objoid => pg_type.oid
39+
Join pg_index.indexrelid => pg_class.oid
40+
Join pg_index.indrelid => pg_class.oid
41+
Join pg_index.indproc => pg_proc.oid
42+
Join pg_opclass.opcdeftype => pg_type.oid
43+
Join pg_operator.oprcom => pg_operator.oid
44+
Join pg_operator.oprrsortop => pg_operator.oid
45+
Join pg_operator.oprlsortop => pg_operator.oid
46+
Join pg_operator.oprnegate => pg_operator.oid
47+
Join pg_operator.oprresult => pg_type.oid
48+
Join pg_operator.oprright => pg_type.oid
49+
Join pg_operator.oprleft => pg_type.oid
50+
Join pg_operator.oprowner => pg_proc.oid
51+
Join pg_parg.partype => pg_type.oid
52+
Join pg_parg.parproid => pg_operator.oid
53+
Join pg_parg.parproid => pg_proc.oid
54+
Join pg_proc.prolang => pg_language.oid
55+
Join pg_proc.prorettype => pg_type.oid
56+
Join pg_proc.proowner => pg_proc.oid
57+
Join pg_rewrite.ev_class => pg_class.oid
58+
Join pg_statistic.starelid => pg_class.oid
59+
Join pg_type.typrelid => pg_class.oid
60+
Join pg_type.typowner => pg_proc.oid
61+
Join pg_type.typelem => pg_operator.oid
62+
Join pg_type.typelem => pg_proc.oid
63+
Join pg_type.typelem => pg_type.oid
64+
65+
66+
---------------------------------------------------------------------------
67+
68+
Bruce Momjian (root@candle.pha.pa.us)

‎contrib/findoidjoins/findoidjoins.c

Lines changed: 93 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,93 @@
1+
/*
2+
* findoidjoins.c, required pgsql/contrib/pginterface
3+
*
4+
*/
5+
6+
#include<stdio.h>
7+
#include"halt.h"
8+
#include<libpq-fe.h>
9+
#include"pginterface.h"
10+
11+
PGresult*attres,*relres;
12+
13+
int
14+
main(intargc,char**argv)
15+
{
16+
charquery[4000];
17+
charrelname[256];
18+
charrelname2[256];
19+
charattname[256];
20+
intcount;
21+
22+
if (argc!=2)
23+
halt("Usage: %s database\n",argv[0]);
24+
25+
connectdb(argv[1],NULL,NULL,NULL,NULL);
26+
on_error_continue();
27+
on_error_stop();
28+
29+
doquery("BEGIN WORK");
30+
doquery("\
31+
DECLARE c_attributes BINARY CURSOR FOR \
32+
SELECT relname, a.attname \
33+
FROM pg_class c, pg_attribute a, pg_type t \
34+
WHERE a.attnum > 0 AND \
35+
relkind = 'r' AND \
36+
typname = 'oid' AND \
37+
a.attrelid = c.oid AND \
38+
a.atttypid = t.oid \
39+
ORDER BY 1; \
40+
");
41+
doquery("FETCH ALL IN c_attributes");
42+
attres=get_result();
43+
44+
doquery("\
45+
DECLARE c_relations BINARY CURSOR FOR \
46+
SELECT relname \
47+
FROM pg_class c \
48+
WHERE relkind = 'r' AND \
49+
relname != 'pg_user' \
50+
ORDER BY 1; \
51+
");
52+
doquery("FETCH ALL IN c_relations");
53+
relres=get_result();
54+
55+
set_result(attres);
56+
while (fetch(relname,attname)!=END_OF_TUPLES)
57+
{
58+
set_result(relres);
59+
reset_fetch();
60+
while (fetch(relname2)!=END_OF_TUPLES)
61+
{
62+
unset_result(relres);
63+
sprintf(query,"\
64+
DECLARE c_matches BINARY CURSOR FOR \
65+
SELECTcount(*)
66+
FROM%st1, %st2 \
67+
WHEREt1.%s=t2.oid",relname,relname2,attname);
68+
69+
doquery(query);
70+
doquery("FETCH ALL IN c_matches");
71+
fetch(&count);
72+
if (count!=0)
73+
printf("Join %s.%s => %s.oid\n",relname,attname,relname2);
74+
doquery("CLOSE c_matches");
75+
set_result(relres);
76+
}
77+
set_result(attres);
78+
}
79+
80+
set_result(relres);
81+
doquery("CLOSE c_relations");
82+
PQclear(relres);
83+
84+
set_result(attres);
85+
doquery("CLOSE c_attributes");
86+
PQclear(attres);
87+
unset_result(attres);
88+
89+
doquery("COMMIT WORK");
90+
91+
disconnectdb();
92+
return0;
93+
}

‎contrib/pginterface/README

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,11 @@ useful if you are running the query engine on a system with a different
2222
architecture than the database server. If you pass a NULL pointer, the
2323
column is skipped, and you can use libpq to handle it as you wish.
2424

25+
There are two functions, get_result() and set_result, that allow you to
26+
handle multiple result sets at the same time.
27+
28+
There is a reset_fetch() that starts the fetch back at the beginning.
29+
2530
There is a demo program called pginsert that demonstrates how the
2631
library can be used.
2732

‎contrib/pginterface/pginterface.c

Lines changed: 71 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,9 @@ static PGresult *res = NULL;
2222

2323
staticinton_error_state=ON_ERROR_STOP;
2424

25+
staticin_result_block= false;
26+
staticwas_get_unset_result= false;
27+
2528
/* LOCAL VARIABLES */
2629
staticinttuple;
2730

@@ -64,9 +67,10 @@ disconnectdb()
6467
PGresult*
6568
doquery(char*query)
6669
{
67-
if (res!=NULL)
70+
if (res!=NULL&&in_result_block== false&&was_get_unset_result== false)
6871
PQclear(res);
6972

73+
was_get_unset_result= false;
7074
res=PQexec(conn,query);
7175

7276
if (on_error_state==ON_ERROR_STOP&&
@@ -187,3 +191,69 @@ on_error_continue()
187191
{
188192
on_error_state=ON_ERROR_CONTINUE;
189193
}
194+
195+
196+
/*
197+
**
198+
**get_result
199+
**
200+
*/
201+
PGresult*get_result()
202+
{
203+
was_get_unset_result= true;
204+
/* we have to store the fetch location somewhere */
205+
memcpy(&res->cmdStatus[CMDSTATUS_LEN-sizeof(tuple)],&tuple,sizeof(tuple));
206+
returnres;
207+
}
208+
209+
/*
210+
**
211+
**set_result
212+
**
213+
*/
214+
voidset_result(PGresult*newres)
215+
{
216+
if (newres==NULL)
217+
halt("set_result called with null result pointer\n");
218+
219+
if (res!=NULL&&was_get_unset_result== false)
220+
if (in_result_block== false)
221+
PQclear(res);
222+
else
223+
memcpy(&res->cmdStatus[CMDSTATUS_LEN-sizeof(tuple)],&tuple,sizeof(tuple));
224+
225+
in_result_block= true;
226+
was_get_unset_result= false;
227+
memcpy(&tuple,&newres->cmdStatus[CMDSTATUS_LEN-sizeof(tuple)],sizeof(tuple));
228+
res=newres;
229+
}
230+
231+
232+
/*
233+
**
234+
**unset_result
235+
**
236+
*/
237+
voidunset_result(PGresult*oldres)
238+
{
239+
if (oldres==NULL)
240+
halt("unset_result called with null result pointer\n");
241+
242+
if (in_result_block== false)
243+
halt("Unset of result without being set.\n");
244+
245+
was_get_unset_result= true;
246+
memcpy(&oldres->cmdStatus[CMDSTATUS_LEN-sizeof(tuple)],&tuple,sizeof(tuple));
247+
in_result_block= false;
248+
}
249+
250+
/*
251+
**
252+
**reset_fetch
253+
**
254+
*/
255+
voidreset_fetch()
256+
{
257+
tuple=0;
258+
}
259+

‎contrib/pginterface/pginterface.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,5 +10,9 @@ intfetch(void *param,...);
1010
intfetchwithnulls(void*param,...);
1111
voidon_error_continue();
1212
voidon_error_stop();
13+
PGresult*get_result();
14+
voidset_result(PGresult*newres);
15+
voidunset_result(PGresult*oldres);
16+
voidreset_fetch();
1317

1418
#defineEND_OF_TUPLES(-1)

‎doc/src/graphics/catalogs.ps

33.8 KB
Binary file not shown.

0 commit comments

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