package com.teamkonzept.webman.mainint.db.queries.oracle;
import java.sql.*;
import com.teamkonzept.db.*;
import com.teamkonzept.webman.mainint.db.queries.content.*;
import com.teamkonzept.webman.mainint.WebmanExceptionHandler;
/*
* TKDBContentTreeMoveNode
* Fuegt einen neuen Knoten ueber/unterhalb eines Nodes ein
* Input:
* SRC_NODE_ID (Knoten des zu verschiebenen Knotens )
* DEST_NODE_ID (Knoten vor/nach dem eingefuegt werden soll)
* UPORDOWN UP = 1 Down = -1
* Output:NIX
*/
public class TKDBContentTreeMoveNode extends TKExtendedPrepQuery
{
public final static boolean isPrepared = true;
public final static String[] paramOrder =
{ "SRC_NODE_ID", "DEST_NODE_ID", "UPORDOWN"
};
public final static Object[][] types = null;
public final static boolean[] setRelevants = { false };
/* public final static String sqlString =
"DECLARE @SRC INT " +
"DECLARE @DEST INT " +
"DECLARE @UPORDOWN INT " +
"DECLARE @SRC_L INT " +
"DECLARE @SRC_R INT " +
"DECLARE @DEST_NR INT " +
"DECLARE @DIFF INT " +
"DECLARE @MAX_RIGHT INT " +
"SELECT @SRC = ? " +
"SELECT @DEST = ? " +
"SELECT @UPORDOWN = ? " +
"SELECT " +
"@SRC_L = LEFT_NR, " +
"@SRC_R = RIGHT_NR " +
"FROM " +
"CONTENT_TREE " +
"WHERE " +
"CONTENT_NODE_ID = @SRC " +
"IF ( " +
"(@SRC != @DEST) " +
"AND NOT " +
"( " +
"@UPORDOWN = 0 " +
"AND " +
"EXISTS (SELECT 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST AND CONTENT_NODE_TYPE > 1 ) " +
")" +
"AND NOT " +
"EXISTS (SELECT 1 FROM CONTENT_TREE WHERE LEFT_NR > @SRC_L AND RIGHT_NR < @SRC_R AND CONTENT_NODE_ID = @DEST) " +
")" +
"BEGIN " +
"BEGIN TRANSACTION " +
"SELECT " +
"@MAX_RIGHT = MAX(RIGHT_NR) " +
"FROM " +
"CONTENT_TREE " +
"SELECT " +
"@SRC_L = LEFT_NR, " +
"@SRC_R = RIGHT_NR " +
"FROM " +
"CONTENT_TREE " +
"WHERE " +
"CONTENT_NODE_ID = @SRC " +
// Den Teilbaum (der verschoben werden soll) erst mal ganz raus
"UPDATE " +
"CONTENT_TREE " +
"SET " +
"RIGHT_NR = RIGHT_NR + @MAX_RIGHT, " +
"LEFT_NR = LEFT_NR + @MAX_RIGHT " +
"WHERE " +
"LEFT_NR >= @SRC_L " +
"AND " +
"RIGHT_NR <= @SRC_R " +
"SELECT @DIFF = (@SRC_R - @SRC_L) + 1 " +
//Denn restlichen Baum wieder zurecht ruecken
"UPDATE " +
"CONTENT_TREE " +
"SET " +
"LEFT_NR = LEFT_NR - @DIFF " +
"WHERE " +
"LEFT_NR >= @SRC_L " +
"AND " +
"RIGHT_NR <= @MAX_RIGHT " +
"UPDATE " +
"CONTENT_TREE " +
"SET " +
"RIGHT_NR = RIGHT_NR - @DIFF " +
"WHERE " +
"RIGHT_NR >= @SRC_L " +
"AND " +
"RIGHT_NR <= @MAX_RIGHT " +
// Ziel ist vor oder hinter Zielknoten ?
"IF (@UPORDOWN = 1) " +
"BEGIN " +
"SELECT " +
"@DEST_NR = LEFT_NR " +
"FROM " +
"CONTENT_TREE " +
"WHERE " +
"CONTENT_NODE_ID = @DEST " +
"END ELSE BEGIN " +
"IF (@UPORDOWN = -1) " +
"BEGIN " +
"SELECT " +
"@DEST_NR = RIGHT_NR + 1 " +
"FROM " +
"CONTENT_TREE " +
"WHERE " +
"CONTENT_NODE_ID = @DEST " +
"END ELSE BEGIN " +
"SELECT " +
"@DEST_NR = LEFT_NR + 1 " +
"FROM " +
"CONTENT_TREE " +
"WHERE " +
"CONTENT_NODE_ID = @DEST " +
"END " +
"END " +
// An der richtigen Stelle Platz schaffen
"UPDATE " +
"CONTENT_TREE " +
"SET " +
"LEFT_NR = LEFT_NR + @DIFF " +
"WHERE " +
"LEFT_NR >= @DEST_NR " +
"AND " +
"RIGHT_NR <= @MAX_RIGHT " +
"UPDATE " +
"CONTENT_TREE " +
"SET " +
"RIGHT_NR = RIGHT_NR + @DIFF " +
"WHERE " +
"RIGHT_NR >= @DEST_NR " +
"AND " +
"RIGHT_NR <= @MAX_RIGHT " +
"SELECT @DIFF = @DEST_NR - @SRC_L - @MAX_RIGHT " +
// Src Knoten an diese Stelle einfuegen
"UPDATE " +
"CONTENT_TREE " +
"SET " +
"RIGHT_NR = RIGHT_NR + @DIFF, " +
"LEFT_NR = LEFT_NR + @DIFF " +
"WHERE " +
"RIGHT_NR > @MAX_RIGHT " +
// Und den neuen Parent des Src Knotens setzen
"IF (@UPORDOWN = 0) " +
"BEGIN " +
"UPDATE " +
"CONTENT_TREE " +
"SET " +
"CONTENT_NODE_PARENT = @DEST " +
"WHERE " +
"CONTENT_NODE_ID = @SRC " +
"END ELSE BEGIN " +
"UPDATE " +
"CONTENT_TREE " +
"SET " +
"CONTENT_NODE_PARENT = " +
"(SELECT CONTENT_NODE_PARENT FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST) " +
"WHERE " +
"CONTENT_NODE_ID = @SRC " +
"END " +
"COMMIT TRANSACTION " +
"END ";
*/
protected static Class[] queryClasses = {
SelectLeftRightNrFromContentTree.class, // [0]
SelectOneFromContentTreeContentType.class, // [1]
SelectOneFromContentTreeLeftRightNr.class, // [2]
SelectMaxFromContentTree.class, // [3]
SelectLeftRightNrFromContentTree.class, // [4]
UpdateContentTreeLeftRightNr.class, // [5]
UpdateContentTreeSetLeftNr.class, // [6]
UpdateContentTreeSetRightNr.class, // [7]
SelectLeftNrFromContentTree.class, // [8]
SelectRightNrPlusOneFromContentTree.class, // [9]
SelectLeftNrPlusOneFromContentTree.class, // [10]
UpdateContentTreeSetLeftNrPlus.class, // [11]
UpdateContentTreeSetRightNrPlus.class, // [12]
UpdateContentTreeSetRightNrGreater.class, // [13]
UpdateContentTreeSetContentNodeParent.class, // [14]
UpdateContentTreeSetContentNodeParentSelect.class // [15]
};
public boolean execute()
{
try
{
init(queryClasses); // init query objects
boolean isNotOpen = aTKDBConnection.isAutoCommit();
if (isNotOpen)
{
TKDBManager.beginTransaction(); // begin transaction
}
// SELECT @SRC = ?
// SELECT @DEST = ?
// SELECT @UPORDOWN = ?
Integer src = (Integer) queryParams.get("SRC_NODE_ID");
Integer dest = (Integer) queryParams.get("DEST_NODE_ID");
Integer upordown = (Integer) queryParams.get("UPORDOWN");
// OLD: SELECT @SRC_L = LEFT_NR, @SRC_R = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @SRC
// NEW: SELECT LEFT_NR, RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
// CLASS: SelectLeftRightNrFromContentTree.class
// PARAMS: NODE_ID
// [0]
queries[0].setQueryParams("NODE_ID", src);
queries[0].execute();
Integer leftNr = null;
Integer rightNr = null;
int left_native = 0;
int right_native = 0;
ResultSet rs0 = queries[0].fetchResultSet();
if (rs0 != null && rs0.next())
{
left_native = rs0.getInt(1);
right_native = rs0.getInt(2);
}
leftNr = new Integer(left_native);
rightNr = new Integer(right_native);
// OLD:
// IF ( (@SRC != @DEST)
// AND NOT (
// @UPORDOWN = 0
// AND
// EXISTS (SELECT 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST AND CONTENT_NODE_TYPE > 2 )
// )
// AND NOT
// EXISTS (SELECT 1 FROM CONTENT_TREE WHERE LEFT_NR > @SRC_L AND RIGHT_NR < @SRC_R AND CONTENT_NODE_ID = @DEST)
// )
// NEW:
// SELECT 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ? AND CONTENT_NODE_TYPE > 2
// CLASS: SelectOneFromContentTreeContentType.class
// PARAMS: NODE_ID
// [1]
queries[1].setQueryParams("NODE_ID", dest);
queries[1].execute();
ResultSet rs1 = queries[1].fetchResultSet();
// SELECT 1 FROM CONTENT_TREE WHERE LEFT_NR > ? AND RIGHT_NR < ? AND CONTENT_NODE_ID = ?
// CLASS: SelectOneFromContentTreeLeftRightNr.class
// PARAMS: LEFT_NR, RIGHT_NR, NODE_ID
// [2]
queries[2].setQueryParams("LEFT_NR", leftNr);
queries[2].setQueryParams("RIGHT_NR", rightNr);
queries[2].setQueryParams("NODE_ID", dest);
queries[2].execute();
ResultSet rs2 = queries[2].fetchResultSet();
boolean hasResult1 = rs1 != null && rs1.next();
boolean hasResult2 = rs2 != null && rs2.next();
// if ((src.intValue() != dest.intValue())
// && !(upordown.intValue() == 0
// &&
// (rs1 != null && rs1.next()))
// && !((rs2 != null && rs2.next())))
if ((src.intValue() != dest.intValue())
&& !(upordown.intValue() == 0 && hasResult1)
&& !hasResult2)
{
// OLD: SELECT @MAX_RIGHT = MAX(RIGHT_NR) FROM CONTENT_TREE
// NEW: SELECT MAX(RIGHT_NR) FROM CONTENT_TREE
// CLASS: SelectMaxFromContentTree.class
// [3]
Object maxRight = null;
queries[3].execute();
ResultSet rs3 = queries[3].fetchResultSet();
if (rs3 != null && rs3.next())
{
maxRight = rs3.getObject(1);
}
// OLD: SELECT @SRC_L = LEFT_NR, @SRC_R = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @SRC
// NEW: SELECT LEFT_NR, RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
// CLASS: SelectLeftRightNrFromContentTree.class
// PARAMS: NODE_ID
// [4]
queries[4].setQueryParams("NODE_ID", src);
queries[4].execute();
leftNr = null;
rightNr = null;
left_native = 0;
right_native = 0;
ResultSet rs4 = queries[4].fetchResultSet();
if (rs4 != null && rs4.next())
{
left_native = rs4.getInt(1);
right_native = rs4.getInt(2);
}
leftNr = new Integer(left_native);
rightNr = new Integer(right_native);
// Den Teilbaum (der verschoben werden soll) erst mal ganz raus
// OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + @MAX_RIGHT,
// LEFT_NR = LEFT_NR + @MAX_RIGHT
// WHERE LEFT_NR >= @SRC_L AND RIGHT_NR <= @SRC_R
// NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + ?, LEFT_NR = LEFT_NR + ? WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
// CLASS: UpdateContentTreeLeftRightNr.class
// PARAMS: MAX_RIGHT, LEFT_NR, RIGHT_NR
// [5]
queries[5].setQueryParams("MAX_RIGHT", maxRight);
queries[5].setQueryParams("LEFT_NR", leftNr);
queries[5].setQueryParams("RIGHT_NR", rightNr);
queries[5].execute();
// SELECT @DIFF = (@SRC_R - @SRC_L) + 1
int leftVal = 0;
int rightVal = 0;
if (leftNr != null)
{
leftVal = leftNr.intValue();
}
if (rightNr != null)
{
rightVal = rightNr.intValue();
}
Integer diff = new Integer(rightVal - leftVal + 1);
//Denn restlichen Baum wieder zurecht ruecken
// OLD: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR - @DIFF WHERE LEFT_NR >= @SRC_L AND RIGHT_NR <= @MAX_RIGHT
// NEW: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR - ? WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
// CLASS: UpdateContentTreeSetLeftNr.class
// PARAMS: DIFF, LEFT_NR, RIGHT_NR
// [6]
queries[6].setQueryParams("DIFF", diff);
queries[6].setQueryParams("LEFT_NR", leftNr);
queries[6].setQueryParams("RIGHT_NR", maxRight);
queries[6].execute();
// OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR - @DIFF WHERE RIGHT_NR >= @SRC_L AND RIGHT_NR <= @MAX_RIGHT
// NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR - ? WHERE RIGHT_NR >= ? AND RIGHT_NR <= ?
// CLASS: UpdateContentTreeSetRightNr.class
// PARAMS: DIFF, LEFT_NR, RIGHT_NR
// [7]
queries[7].setQueryParams("DIFF", diff);
queries[7].setQueryParams("LEFT_NR", leftNr);
queries[7].setQueryParams("RIGHT_NR", maxRight);
queries[7].execute();
// Ziel ist vor oder hinter Zielknoten ?
// OLD: IF (@UPORDOWN = 1)
Object destNr = null;
if (upordown.intValue() == 1)
{
// OLD: SELECT @DEST_NR = LEFT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST
// NEW: SELECT LEFT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
// CLASS: SelectLeftNrFromContentTree.class
// PARAMS: NODE_ID
// [8]
queries[8].setQueryParams("NODE_ID", dest);
queries[8].execute();
ResultSet rs8 = queries[8].fetchResultSet();
//destNr = null;
if (rs8 != null && rs8.next())
{
destNr = rs8.getObject(1);
}
}
// OLD: END ELSE BEGIN
else
{
// OLD: IF (@UPORDOWN = -1)
if (upordown.intValue() == -1)
{
// OLD: SELECT @DEST_NR = RIGHT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST
// NEW: SELECT RIGHT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
// CLASS: SelectRightNrPlusOneFromContentTree.class
// PARAMS: NODE_ID
// [9]
queries[9].setQueryParams("NODE_ID", dest);
queries[9].execute();
destNr = null;
ResultSet rs9 = queries[9].fetchResultSet();
if (rs9 != null && rs9.next())
{
destNr = rs9.getObject(1);
}
}
// END ELSE BEGIN
else
{
// OLD: SELECT @DEST_NR = LEFT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST
// NEW: SELECT LEFT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
// CLASS: SelectLeftNrPlusOneFromContentTree.class
// PARAMS: NODE_ID
// [10]
queries[10].setQueryParams("NODE_ID", dest);
queries[10].execute();
destNr = null;
ResultSet rs10 = queries[10].fetchResultSet();
if (rs10 != null && rs10.next())
{
destNr = rs10.getObject(1);
}
}
}
// An der richtigen Stelle Platz schaffen
// OLD: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR + @DIFF WHERE LEFT_NR >= @DEST_NR AND RIGHT_NR <= @MAX_RIGHT
// NEW: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR + ? WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
// CLASS: UpdateContentTreeSetLeftNrPlus.class
// PARAMS: DIFF, LEFT_NR, RIGHT_NR
// [11]
queries[11].setQueryParams("DIFF", diff);
queries[11].setQueryParams("LEFT_NR", destNr);
queries[11].setQueryParams("RIGHT_NR", maxRight);
queries[11].execute();
// OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + @DIFF WHERE RIGHT_NR >= @DEST_NR AND RIGHT_NR <= @MAX_RIGHT
// NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + ? WHERE RIGHT_NR >= ? AND RIGHT_NR <= ?
// CLASS: UpdateContentTreeSetRightNrPlus.class
// PARAMS: DIFF, LEFT_NR, RIGHT_NR
// [12]
queries[12].setQueryParams("DIFF", diff);
queries[12].setQueryParams("LEFT_NR", destNr);
queries[12].setQueryParams("RIGHT_NR", maxRight);
queries[12].execute();
// SELECT @DIFF = @DEST_NR - @SRC_L - @MAX_RIGHT
int destVal = 0;
leftVal = 0; // defined above!
int maxVal = 0;
if (destNr != null && destNr instanceof Number)
{
destVal = ((Number) destNr).intValue();
}
if (leftNr != null && leftNr instanceof Number)
{
leftVal = ((Number) leftNr).intValue();
}
if (maxRight != null && maxRight instanceof Number)
{
maxVal = ((Number) maxRight).intValue();
}
diff = new Integer(destVal - leftVal - maxVal);
// Src Knoten an diese Stelle einfuegen
// OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + @DIFF, LEFT_NR = LEFT_NR + @DIFF WHERE RIGHT_NR > @MAX_RIGHT
// NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + ?, LEFT_NR = LEFT_NR + ? WHERE RIGHT_NR > ?
// CLASS: UpdateContentTreeSetRightNrGreater.class
// PARAMS: DIFF (2x), RIGHT_NR
// [13]
queries[13].setQueryParams("DIFF", diff);
queries[13].setQueryParams("RIGHT_NR", maxRight);
queries[13].execute();
// Und den neuen Parent des Src Knotens setzen
// IF (@UPORDOWN = 0)
if (upordown.intValue() == 0)
{
// OLD: UPDATE CONTENT_TREE SET CONTENT_NODE_PARENT = @DEST WHERE CONTENT_NODE_ID = @SRC
// NEW: UPDATE CONTENT_TREE SET CONTENT_NODE_PARENT = ? WHERE CONTENT_NODE_ID = ?
// CLASS: UpdateContentTreeSetContentNodeParent.class
// PARAMS: CONTENT_NODE_PARENT, CONTENT_NODE_ID
// [14]
queries[14].setQueryParams("CONTENT_NODE_PARENT", dest);
queries[14].setQueryParams("CONTENT_NODE_ID", src);
queries[14].execute();
}
// END ELSE BEGIN
else
{
// OLD: UPDATE CONTENT_TREE SET CONTENT_NODE_PARENT = (SELECT CONTENT_NODE_PARENT FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST) WHERE CONTENT_NODE_ID = @SRC
// NEW: UPDATE CONTENT_TREE SET CONTENT_NODE_PARENT = (SELECT CONTENT_NODE_PARENT FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?) WHERE CONTENT_NODE_ID = ?
// CLASS: UpdateContentTreeSetContentNodeParentSelect.class
// PARAMS: DEST, SRC
// [15]
queries[15].setQueryParams("DEST", dest);
queries[15].setQueryParams("SRC", src);
queries[15].execute();
}
// END (of first if)
} // of first if
if (isNotOpen)
{
aTKDBConnection.commitTransaction(); // commit all changes
}
}
catch(Throwable t)
{
TKDBManager.safeRollbackTransaction(t);
}
return hasResults();
}
public void initQuery(Connection con)
{
super.initQuery(con,
isPrepared, paramOrder, types, setRelevants, null);
}
}
|