001: package com.teamkonzept.webman.mainint.db.queries;
002:
003: import java.sql.*;
004: import java.util.Enumeration;
005: import com.teamkonzept.db.*;
006: import com.teamkonzept.webman.mainint.db.queries.content.*;
007: import com.teamkonzept.webman.mainint.WebmanExceptionHandler;
008:
009: /*
010: * TKDBContentTreeInsertNode
011: * Fuegt einen neuen Knoten unterhalb eines Parent Nodes ein
012: * Input: NODE_ID (Knoten des Parents ) und die neuen Werte
013: * "CONTENT_NODE_NAME", "CONTENT_NODE_TYPE", "CONTENT_FORM"
014: * Output: der neu erzeugte Datensatz
015: */
016: public class TKDBContentTreeInsertNode extends TKExtendedPrepQuery {
017:
018: public final static boolean isPrepared = true;
019:
020: public final static String[] paramOrder = { "NODE_ID",
021: "CONTENT_NODE_NAME", "CONTENT_NODE_SHORTNAME",
022: "CONTENT_NODE_TYPE", "CONTENT_FORM", "TREE_ID",
023: "PROTOTYPE_ID" };
024:
025: public final static Object[][] types = {
026: { "CONTENT_NODE_NAME", new Integer(Types.VARCHAR) },
027: { "CONTENT_NODE_SHORTNAME", new Integer(Types.VARCHAR) } };
028:
029: public final static boolean[] setRelevants = { true };
030:
031: /* public final static String sqlString =
032: "DECLARE @PR_NR INT " +
033: "DECLARE @N_ID INT " +
034: "DECLARE @ID INT " +
035:
036: "BEGIN TRANSACTION " +
037:
038: "SELECT @N_ID = ? " +
039:
040: "SELECT " +
041: " @PR_NR=RIGHT_NR " +
042: "FROM " +
043: " CONTENT_TREE " +
044: "WHERE " +
045: " CONTENT_NODE_ID = @N_ID " +
046:
047: "UPDATE " +
048: " CONTENT_TREE " +
049: "SET " +
050: " RIGHT_NR = RIGHT_NR + 2 " +
051: "WHERE " +
052: " RIGHT_NR >= @PR_NR " +
053:
054: "UPDATE " +
055: " CONTENT_TREE " +
056: "SET " +
057: " LEFT_NR = LEFT_NR + 2 " +
058: "WHERE " +
059: " LEFT_NR > @PR_NR " +
060:
061: "SELECT " +
062: " @ID = MAX(CONTENT_NODE_ID) + 1 " +
063: "FROM " +
064: " CONTENT_TREE " +
065:
066: "INSERT INTO " +
067: "CONTENT_TREE " +
068: " (CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_SHORTNAME, " +
069: " CONTENT_NODE_TYPE, LEFT_NR, RIGHT_NR, " +
070: " CONTENT_FORM, CONTENT_NODE_PARENT, TREE_ID, PROTOTYPE_ID) " +
071: "VALUES " +
072: " (@ID, ?, ?, ?, @PR_NR, @PR_NR + 1, ?, @N_ID, ?, ?) " +
073:
074: "COMMIT TRANSACTION " +
075:
076: "SELECT " +
077: " CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_SHORTNAME, " +
078: " CONTENT_NODE_TYPE, LEFT_NR, RIGHT_NR, " +
079: " CONTENT_FORM " +
080: "FROM " +
081: " CONTENT_TREE " +
082: "WHERE " +
083: " CONTENT_NODE_ID = @ID "; */
084:
085: protected static Class[] queryClasses = {
086: SelectRightNrFromContentTree.class,
087: UpdateContentTreeRightNrEqual.class,
088: UpdateContentTreeLeftnrGreater.class,
089: SelectMaxContentTree.class,
090: InsertContentTreePrototype.class, SelectContentTree.class };
091:
092: public boolean execute() {
093: try {
094:
095: init(queryClasses); // init query objects
096:
097: Object nid = queryParams.get("NODE_ID");
098:
099: boolean isNotOpen = aTKDBConnection.isAutoCommit();
100: if (isNotOpen) {
101: TKDBManager.beginTransaction();
102: }
103:
104: // SELECT @PR_NR=RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @N_ID
105: queries[0].setQueryParams("NODE_ID", nid);
106: queries[0].execute();
107: Object prNr = null;
108: ResultSet rs = queries[0].fetchResultSet();
109: if (rs != null && rs.next()) {
110: prNr = rs.getObject(1);
111: }
112:
113: // UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + 2 WHERE RIGHT_NR >= @PR_NR
114: queries[1].setQueryParams("PR_NR", prNr);
115: queries[1].execute();
116:
117: // UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR + 2 WHERE LEFT_NR > @PR_NR
118: queries[2].setQueryParams("PR_NR", prNr);
119: queries[2].execute();
120:
121: // SELECT @ID = MAX(CONTENT_NODE_ID) + 1 FROM CONTENT_TREE
122: Object id = null;
123: Integer prNrPlusOne = null;
124: queries[3].execute();
125: rs = queries[3].fetchResultSet();
126: if (rs != null && rs.next()) {
127: id = rs.getObject(1);
128: }
129:
130: if (prNr != null && prNr instanceof Number) {
131: prNrPlusOne = new Integer(
132: ((Number) prNr).intValue() + 1);
133: }
134:
135: // INSERT INTO CONTENT_TREE (CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_SHORTNAME,
136: // CONTENT_NODE_TYPE, LEFT_NR, RIGHT_NR, CONTENT_FORM, CONTENT_NODE_PARENT, TREE_ID, PROTOTYPE_ID)
137: // VALUES (@ID, ?, ?, ?, @PR_NR, @PR_NR + 1, ?, @N_ID, ?, ?)
138: queries[4].setQueryParams("ID", id);
139: queries[4].setQueryParams("PR_NR", prNr);
140: queries[4].setQueryParams("PR_NR_PLUS_ONE", prNrPlusOne);
141: queries[4].setQueryParams("NODE_ID", queryParams
142: .get("NODE_ID"));
143: queries[4].setQueryParams("CONTENT_NODE_NAME", queryParams
144: .get("CONTENT_NODE_NAME"));
145: queries[4].setQueryParams("CONTENT_NODE_SHORTNAME",
146: queryParams.get("CONTENT_NODE_SHORTNAME"));
147: queries[4].setQueryParams("CONTENT_NODE_TYPE", queryParams
148: .get("CONTENT_NODE_TYPE"));
149: queries[4].setQueryParams("CONTENT_FORM", queryParams
150: .get("CONTENT_FORM"));
151: queries[4].setQueryParams("TREE_ID", queryParams
152: .get("TREE_ID"));
153: queries[4].setQueryParams("PROTOTYPE_ID", queryParams
154: .get("PROTOTYPE_ID"));
155: queries[4].execute();
156:
157: if (isNotOpen) {
158: TKDBManager.commitTransaction();
159: }
160:
161: // SELECT CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_SHORTNAME,
162: // CONTENT_NODE_TYPE, LEFT_NR, RIGHT_NR, CONTENT_FORM
163: // FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @ID
164: queries[5].setQueryParams("ID", id);
165: queries[5].execute();
166: addResult(queries[5].fetchResultSet());
167:
168: } catch (Throwable t) {
169: TKDBManager.safeRollbackTransaction(t);
170: }
171: return hasResults();
172: }
173:
174: public void initQuery(Connection con) {
175: super.initQuery(con, isPrepared, paramOrder, types,
176: setRelevants, null);
177: }
178: }
|