TKDBContentTreeMoveNode.java :  » Content-Management-System » webman » com » teamkonzept » webman » mainint » db » queries » oracle » Java Open Source

Java Open Source » Content Management System » webman 
webman » com » teamkonzept » webman » mainint » db » queries » oracle » TKDBContentTreeMoveNode.java
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);
    }
}
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.