|
1
5 package com.teamkonzept.webman.mainint.db.queries.sybase;
6
7 import java.sql.*;
8
9 import com.teamkonzept.db.*;
10
11
18 public class TKDBSiteTreeDeleteNode extends TKPrepQuery{
19
20 public final static boolean isPrepared =
21 true;
22
23 public final static String  [] paramOrder =
24 { "SITE_NODE_ID" };
25
26 public final static Object [][] paramTypes =
27 null;
28
29 public final static boolean[] setRelevants =
30 { false };
31
32 public final static String sqlString =
33 "DECLARE @L_NR INT " +
34 "DECLARE @R_NR INT " +
35 "DECLARE @DIFF INT " +
36
37
38 "CREATE TABLE #temp " +
39 "( " +
40 " SITE_NODE_ID int " +
41 ") " +
42
43 "CREATE TABLE #temp2 " +
44 "( " +
45 " SELECTION_ID int " +
46 ") " +
47
48 "BEGIN TRANSACTION " +
49
50 "SELECT " +
51 " @L_NR = LEFT_NR, @R_NR = RIGHT_NR " +
52 "FROM " +
53 " SITE_TREE " +
54 "WHERE " +
55 " SITE_NODE_ID = ? " +
56
57 "SELECT @DIFF = @R_NR - @L_NR + 1 " +
58
59 "INSERT INTO " +
60 " #temp " +
61 " (SITE_NODE_ID) " +
62 "SELECT " +
63 " SITE_NODE_ID " +
64 "FROM " +
65 " SITE_TREE " +
66 "WHERE " +
67 " LEFT_NR >= @L_NR " +
68 "AND " +
69 " RIGHT_NR <= @R_NR " +
70
71 "INSERT INTO " +
72 " #temp2 " +
73 "SELECT " +
74 " SELECTION_ID " +
75 "FROM " +
76 " DOCUMENT_CONTENT " +
77 "WHERE " +
78 " SELECTION_ID IS NOT NULL " +
79 "AND " +
80 " SITE_NODE_ID IN " +
81 " (SELECT SITE_NODE_ID FROM #temp) " +
82
83 "INSERT INTO " +
84 " #temp2 " +
85 "SELECT " +
86 " PG_SELECTION_ID " +
87 "FROM " +
88 " SITE_TREE " +
89 "WHERE " +
90 " LEFT_NR >= @L_NR " +
91 "AND " +
92 " RIGHT_NR <= @R_NR " +
93 "AND " +
94 " PG_SELECTION_ID IS NOT NULL " +
95
96 "DELETE FROM " +
97 " DOCUMENT_CONTENT " +
98 "WHERE " +
99 " SITE_NODE_ID IN " +
100 " (SELECT SITE_NODE_ID FROM #temp) " +
101
102 "DELETE FROM " +
103 " DOCUMENT_REFERENCE " +
104 "WHERE " +
105 " SRC_SITE_NODE_ID IN " +
106 " (SELECT SITE_NODE_ID FROM #temp) OR " +
107 " DEST_SITE_NODE_ID IN " +
108 " (SELECT SITE_NODE_ID FROM #temp) " +
109
110 "DELETE FROM " +
111 " SITE_DOCUMENT " +
112 "WHERE " +
113 " SITE_NODE_ID IN " +
114 " (SELECT SITE_NODE_ID FROM #temp) " +
115
116 "DELETE FROM " +
117 " STRUCTURED_CONTENT " +
118 "WHERE " +
119 " SITE_NODE_ID IN " +
120 " (SELECT SITE_NODE_ID FROM #temp) " +
121
122 "DELETE FROM " +
123 " SITE_TREE " +
124 "WHERE " +
125 " LEFT_NR >= @L_NR " +
126 "AND " +
127 " RIGHT_NR <= @R_NR " +
128
129 "DELETE FROM " +
130 " CONTENT_SELECTION " +
131 "WHERE " +
132 " SELECTION_ID IN (" +
133 "SELECT " +
134 " SELECTION_ID " +
135 "FROM " +
136 " #temp2 " +
137 ") " +
138
139 "UPDATE " +
140 " SITE_TREE " +
141 "SET " +
142 " RIGHT_NR = RIGHT_NR - @DIFF " +
143 "WHERE " +
144 " RIGHT_NR > @R_NR " +
145
146 "UPDATE " +
147 " SITE_TREE " +
148 "SET " +
149 " LEFT_NR = LEFT_NR - @DIFF " +
150 "WHERE " +
151 " LEFT_NR > @L_NR " +
152
153 "COMMIT TRANSACTION " +
154
155 "DROP TABLE #temp " +
156 "DROP TABLE #temp2 ";
157
158 public void initQuery(Connection con) {
159 super.initQuery(
160 con,
161 isPrepared,
162 paramOrder,
163 paramTypes,
164 setRelevants,
165 sqlString );
166 }
167 }
168
169
170 |
Popular Tags |
Java API By Example, From Geeks To Geeks.