1 package com.teamkonzept.webman.db.queries.oracle; 2 3 import com.teamkonzept.db.*; 4 6 import java.io.*; 7 import java.util.*; 8 import java.sql.*; 9 10 11 12 18 19 public class UtilityProceduresPackageBody extends TKPrepQuery{ 20 21 public final static boolean isPrepared = 22 true; 23 24 public final static String [] paramOrder = 25 null; 26 27 public final static Object [][] paramTypes = 28 null; 29 30 public final static boolean[] setRelevants = 31 null; 32 33 public final static String 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 + " " 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 + " " 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 + " " 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) " 111 + " THEN " 112 + " RETURN dest_id; " 113 + " END IF; " 114 115 + " " 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 + " " 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 + " " 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 |