1 5 package com.teamkonzept.webman.mainint.db.queries; 6 7 import java.sql.*; 8 9 import com.teamkonzept.webman.mainint.DatabaseDefaults; 10 import com.teamkonzept.db.*; 11 12 21 public class TKDBSiteTreeMoveNode extends TKPrepQuery implements DatabaseDefaults{ 22 23 public final static boolean isPrepared = 24 true; 25 26 public final static String [] paramOrder = 27 { "SRC_NODE_ID" ,"DEST_NODE_ID" ,"UPORDOWN" 28 }; 29 30 public final static Object [][] paramTypes = 31 null; 32 33 public final static boolean[] setRelevants = 34 { false }; 35 36 public final static String sqlString = 37 38 " DECLARE " 39 + " src_id INTEGER; " 40 + " dest_id INTEGER; " 41 + " up_down INTEGER; " 42 + " pred INTEGER; " 43 + " src_left INTEGER; " 44 + " src_right INTEGER; " 45 + " dest_left INTEGER; " 46 + " dest_right INTEGER; " 47 + " node_type INTEGER; " 48 + " new_parent INTEGER; " 49 + " shift INTEGER; " 50 + " inc INTEGER; " 51 + " BEGIN " 52 + " src_id := ? ; " 53 + " dest_id := ? ; " 54 + " up_down := ? ; " 55 56 + " SELECT LEFT_NR INTO src_left FROM SITE_TREE WHERE SITE_NODE_ID = src_id; " 57 + " SELECT RIGHT_NR INTO src_right FROM SITE_TREE WHERE SITE_NODE_ID = src_id; " 58 59 + " SELECT LEFT_NR INTO dest_left FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; " 60 + " SELECT RIGHT_NR INTO dest_right FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; " 61 62 + " SELECT SITE_NODE_TYPE INTO node_type FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; " 63 64 + " IF " 65 + " ( " 66 + " ( " 67 + " src_left <= dest_left AND src_right >= dest_right " 68 + " ) " 69 + " OR " 70 + " ( " 71 + " up_down = 0 AND node_type < " + SITE_DIRECTORY 72 + " ) " 73 + " ) " 74 + " THEN " 75 + " RETURN; " 76 + " END IF; " 77 78 + " IF(up_down = 0) THEN " 79 + " new_parent := dest_id; " 80 + " SELECT LEFT_NR INTO pred FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; " 81 + " ELSIF (up_down = 1)THEN " 82 + " SELECT SITE_NODE_PARENT INTO new_parent FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; " 83 + " SELECT (LEFT_NR -1) INTO pred FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; " 84 + " ELSIF (up_down = -1) THEN " 85 + " SELECT SITE_NODE_PARENT INTO new_parent FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; " 86 + " SELECT RIGHT_NR INTO pred FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; " 87 + " END IF; " 88 89 90 91 + " shift := src_right - src_left +1; " 92 93 94 95 + " " 96 97 + " UPDATE SITE_TREE " 98 + " SET LEFT_NR = LEFT_NR + shift " 99 + " WHERE LEFT_NR > pred; " 100 101 102 + " UPDATE SITE_TREE " 103 + " set RIGHT_NR = RIGHT_NR + shift " 104 + " WHERE RIGHT_NR > pred; " 105 106 + " " 107 108 + " " 109 + " SELECT LEFT_NR INTO src_left FROM SITE_TREE WHERE SITE_NODE_ID = src_id; " 110 + " SELECT RIGHT_NR INTO src_right FROM SITE_TREE WHERE SITE_NODE_ID = src_id; " 111 112 + " inc := pred - src_left +1; " 113 114 + " UPDATE SITE_TREE " 115 + " SET LEFT_NR = LEFT_NR + inc, RIGHT_NR = RIGHT_NR + inc " 116 + " WHERE " 117 + " LEFT_NR >= src_left " 118 + " AND " 119 + " RIGHT_NR <= src_right; " 120 121 122 + " " 123 124 + " UPDATE SITE_TREE " 125 + " SET SITE_NODE_PARENT = new_parent " 126 + " WHERE SITE_NODE_ID = src_id; " 127 + " END; " 128 ; 129 130 public void initQuery(Connection con) { 131 super.initQuery( 132 con, 133 isPrepared, 134 paramOrder, 135 paramTypes, 136 setRelevants, 137 sqlString ); 138 } 139 } 140 141 | Popular Tags |