KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > teamkonzept > webman > db > queries > oracle > UtilityProceduresPackageBody


1 package com.teamkonzept.webman.db.queries.oracle;
2
3 import com.teamkonzept.db.*;
4 //import com.teamkonzept.lib.*;
5
6 import java.io.*;
7 import java.util.*;
8 import java.sql.*;
9
10
11
12     /**
13         Oracle package declaration for utility procedures and functions, originally designed for duplication,
14         but possibly usefull elsewhere.
15         
16         @see UtilityProceduresPackageBody
17     */
18         
19 public class UtilityProceduresPackageBody extends TKPrepQuery{
20
21     public final static boolean isPrepared =
22         true;
23     
24     public final static String JavaDoc[] paramOrder =
25         null;
26     
27     public final static Object JavaDoc[][] paramTypes =
28         null;
29                 
30     public final static boolean[] setRelevants =
31         null;
32         
33     public final static String JavaDoc sqlString =
34
35                                                         
36  " CREATE OR REPLACE PACKAGE BODY utility_procedures IS "
37
38
39  + " FUNCTION ct_subnode(root_id INTEGER, subnode_id INTEGER)RETURN INTEGER "
40  + " AS "
41  + " root_left INTEGER; "
42  + " root_right INTEGER; "
43  + " sub_left INTEGER; "
44  + " sub_right INTEGER; "
45  + " BEGIN "
46  + " SELECT LEFT_NR INTO root_left FROM CONTENT_TREE WHERE CONTENT_NODE_ID = root_id; "
47  + " SELECT RIGHT_NR INTO root_right FROM CONTENT_TREE WHERE CONTENT_NODE_ID = root_id; "
48  + " SELECT LEFT_NR INTO sub_left FROM CONTENT_TREE WHERE CONTENT_NODE_ID = subnode_id; "
49  + " SELECT RIGHT_NR INTO sub_right FROM CONTENT_TREE WHERE CONTENT_NODE_ID = subnode_id; "
50  + " IF(sub_left >= root_left AND sub_right <= root_right) THEN "
51  + " RETURN 1; "
52  + " ELSE "
53  + " RETURN 0; "
54  + " END IF; "
55  + " END ct_subnode; "
56     
57     
58  + " FUNCTION st_subnode(root_id INTEGER, subnode_id INTEGER)RETURN INTEGER "
59  + " AS "
60  + " root_left INTEGER; "
61  + " root_right INTEGER; "
62  + " sub_left INTEGER; "
63  + " sub_right INTEGER; "
64  + " BEGIN "
65  + " SELECT LEFT_NR INTO root_left FROM SITE_TREE WHERE SITE_NODE_ID = root_id; "
66  + " SELECT RIGHT_NR INTO root_right FROM SITE_TREE WHERE SITE_NODE_ID = root_id; "
67  + " SELECT LEFT_NR INTO sub_left FROM SITE_TREE WHERE SITE_NODE_ID = subnode_id; "
68  + " SELECT RIGHT_NR INTO sub_right FROM SITE_TREE WHERE SITE_NODE_ID = subnode_id; "
69  + " IF(sub_left >= root_left AND sub_right <= root_right) THEN "
70  + " RETURN 1; "
71  + " ELSE "
72  + " RETURN 0; "
73  + " END IF; "
74  + " END st_subnode; "
75     
76     
77  + " FUNCTION st_calc_dist(father_id INTEGER, child_id INTEGER) "
78  + " RETURN INTEGER "
79  + " AS "
80  + " dist INTEGER; "
81  + " BEGIN "
82  + " " /* Illegal */
83  + " IF(st_subnode(father_id, child_id) = 0) THEN "
84  + " RETURN -1; "
85  + " END IF; "
86             
87         
88  + " SELECT COUNT(*) INTO dist "
89  + " FROM SITE_TREE "
90  + " WHERE "
91  + " " /* path from root to child */
92  + " LEFT_NR < (SELECT LEFT_NR FROM SITE_TREE WHERE SITE_NODE_ID = child_id) "
93  + " AND "
94  + " RIGHT_NR > (SELECT RIGHT_NR FROM SITE_TREE WHERE SITE_NODE_ID = child_id) "
95  + " AND "
96  + " " /* child of father ? */
97  + " st_subnode(father_id, SITE_NODE_ID) = 1; "
98         
99  + " RETURN dist; "
100  + " END st_calc_dist; "
101     
102     
103  + " FUNCTION calc_rel_ref(src_id INTEGER, dest_id INTEGER, src_id_new INTEGER, ref_type INTEGER) "
104  + " RETURN INTEGER "
105  + " AS "
106  + " dist INTEGER; "
107  + " dest_id_new INTEGER; "
108  + " BEGIN "
109  + " DBMS_OUTPUT.PUT_LINE(' calc_rel_ref calleld for src_id: '|| src_id || ' dest_id: ' || dest_id || ' src_id_new: ' || src_id_new || ' ref_type: '|| ref_type); "
110  + " IF (ref_type != 2) " /* RELATIVE-REFERENCE */
111  + " THEN "
112  + " RETURN dest_id; "
113  + " END IF; "
114         
115  + " " /* distance between originals */
116  + " dist := st_calc_dist(dest_id, src_id); "
117  + " BEGIN "
118  + " SELECT SITE_NODE_ID INTO dest_id_new "
119  + " FROM SITE_TREE "
120  + " WHERE "
121  + " " /* path to src_id_new */
122  + " LEFT_NR <= (SELECT LEFT_NR FROM SITE_TREE WHERE SITE_NODE_ID = src_id_new) "
123  + " AND "
124  + " RIGHT_NR >= (SELECT RIGHT_NR FROM SITE_TREE WHERE SITE_NODE_ID = src_id_new) "
125  + " AND "
126  + " " /* correct distance */
127  + " st_calc_dist(SITE_NODE_ID, src_id_new) = dist; "
128  + " EXCEPTION "
129  + " WHEN NO_DATA_FOUND THEN "
130  + " RAISE ILLEGAL_REL_REF; "
131  + " END; "
132  + " RETURN dest_id_new; "
133  + " END calc_rel_ref; "
134     
135     
136     
137     
138  + " PROCEDURE make_place(dest_right INTEGER, shift INTEGER, treetable VARCHAR2) "
139  + " AS "
140  + " BEGIN "
141  + " EXECUTE IMMEDIATE "
142  + " ' UPDATE ' || treetable || "
143  + " ' SET LEFT_NR = LEFT_NR + ' || shift || "
144  + " ' WHERE LEFT_NR > ' || dest_right; "
145         
146  + " EXECUTE IMMEDIATE "
147  + " ' UPDATE ' || treetable || "
148  + " ' SET RIGHT_NR = RIGHT_NR + '|| shift || "
149  + " ' WHERE RIGHT_NR >= '|| dest_right; "
150  + " END make_place; "
151     
152         
153  + " END utility_procedures; "
154      ;
155     
156     public void initQuery(Connection con) {
157         super.initQuery(
158             con,
159             isPrepared,
160             paramOrder,
161             paramTypes,
162             setRelevants,
163             sqlString );
164     }
165 }
166
167
Popular Tags