X-Git-Url: http://git.phpeclipse.com

diff --git a/archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/util/sql/SQLInstructionBuilder.java b/archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/util/sql/SQLInstructionBuilder.java
new file mode 100644
index 0000000..63ae94e
--- /dev/null
+++ b/archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/util/sql/SQLInstructionBuilder.java
@@ -0,0 +1,218 @@
+/*
+ * Created on 12.08.2004
+ *
+ */
+package com.quantum.util.sql;
+
+import java.sql.SQLException;
+
+import com.quantum.adapters.DatabaseAdapter;
+import com.quantum.model.Bookmark;
+import com.quantum.model.Column;
+import com.quantum.model.Entity;
+import com.quantum.model.NotConnectedException;
+import com.quantum.util.StringMatrix;
+
+/**
+ * Functions to build SQL instructions adapted to the particular database
+ * 
+ * @author Julen
+ */
+public class SQLInstructionBuilder {
+	
+	/**
+	 * Generates an Insert SQL instruction for each row of data in an StrigMatrix
+	 * @param entity	The entity to generate the instruction for
+	 * @param columns	A StringMatrix holding the names and values of the columns to insert 
+	 * @return	A String with the insert sentences generated
+	 */
+	public static String buildInsert(Entity entity, StringMatrix columns)
+	{
+		if (entity == null || columns == null ) return "";
+		StringBuffer valuesClause = new StringBuffer();
+		StringBuffer namesClause = new StringBuffer();
+		String insertSentences = "";
+		
+		// We generate an update sentence for each row in the StringMatrix
+		for (int iRow = 0; iRow < columns.size(); iRow++) {
+			for (int iCol = 0; iCol < columns.getNumColumns(); iCol++) {
+				if (iCol > 0) {
+					namesClause.append(", "); //$NON-NLS-1$
+					valuesClause.append(", "); //$NON-NLS-1$
+				}
+				namesClause.append(columns.getHeaderColumn(iCol));
+				valuesClause.append(quoteValue( entity, columns.getHeaderColumn(iCol), columns.get(iCol, iRow)));
+			}
+			if (iRow > 0) insertSentences += ";\n";
+			insertSentences += "INSERT INTO " + entity.getQuotedTableName(); //$NON-NLS-1$
+			insertSentences += "(" + namesClause + " )"; //$NON-NLS-1$
+			insertSentences +=  " VALUES " + " ( " + valuesClause + " )" ; //$NON-NLS-1$
+		}
+		return insertSentences;
+	}
+
+	/**
+	 * Generates an UPDATE SQL instruction for each row of data in an StrigMatrix
+	 * @param entity	The entity to generate the instruction for
+	 * @param columns	A StringMatrix holding the names and values of the columns to insert 
+	 * @param key		A StringMatrix holding the names and values of the columns of the key 
+	 * @return	A String with the insert sentences generated
+	 */
+	public static String buildUpdate(Entity entity, StringMatrix columns, StringMatrix key)
+	{
+		if (entity == null || columns == null ) return "";
+		StringBuffer setClause = new StringBuffer();
+		String whereClause = "";
+		String updateSentences = "";
+		
+		// We generate an update sentence for each row in the StringMatrix
+		for (int iRow = 0; iRow < columns.size(); iRow++) {
+			for (int iCol = 0; iCol < columns.getNumColumns(); iCol++) {
+				if (iCol > 0) setClause.append(", "); //$NON-NLS-1$
+				setClause.append(columns.getHeaderColumn(iCol));
+				setClause.append(" = "); //$NON-NLS-1$
+				setClause.append(quoteValue( entity, columns.getHeaderColumn(iCol), columns.get(iCol, iRow)));
+			}
+			if (key != null && iRow < key.size()) {
+				whereClause = getWhereClause(entity, key, iRow);
+			}
+			if (iRow > 0) updateSentences += ";\n";
+			updateSentences += "UPDATE " + entity.getQuotedTableName(); //$NON-NLS-1$
+			updateSentences += " SET " + setClause.toString(); //$NON-NLS-1$
+			if (whereClause.length() > 0) 
+				updateSentences += " WHERE " + whereClause; //$NON-NLS-1$
+			}
+		return updateSentences;
+	}
+	/**
+	 * @param entity
+	 * @param key
+	 * @return
+	 */
+	public static String buildDelete(Entity entity, StringMatrix key)
+	{
+		if (entity == null ) return "";
+		String deleteSentences = "";
+		String whereClause = "";
+		
+		// We generate an update sentence for each row in the StringMatrix
+		if (key == null) return "DELETE FROM " + entity.getQuotedTableName(); //$NON-NLS-1$
+		
+		for (int iRow = 0; iRow < key.size(); iRow++) {
+			if (key != null && iRow < key.size()) {
+				whereClause = getWhereClause(entity, key, iRow);
+			}
+			if (iRow > 0) deleteSentences += ";\n";
+			deleteSentences += "DELETE FROM " + entity.getQuotedTableName(); //$NON-NLS-1$
+			if (whereClause.length() > 0) 
+				deleteSentences += " WHERE " + whereClause; //$NON-NLS-1$
+			}
+		return deleteSentences;
+	}
+	/**
+	 * Builds a Select query with all columns and no rows (useful for structure querying)
+	 * @param entity
+	 * @return
+	 */
+	public static String buildSelectAllColumnsNoRows(Entity entity) {
+		return "SELECT * FROM " + entity.getQuotedTableName() + " WHERE (1 = 0)"; //$NON-NLS-1$ //$NON-NLS-2$
+	}
+	/**
+	 * Builds a Select query with all columns and no rows (useful for structure querying)
+	 * @param entity
+	 * @return
+	 */
+	public static String buildSelectAllColumnsAllRows(Entity entity) {
+		return "SELECT * FROM " + entity.getQuotedTableName() ; //$NON-NLS-1$ //$NON-NLS-2$
+	}
+	/**
+	 * Builds a Select query with the selected columns and the selected rows (useful for structure querying)
+	 * @param entity
+	 * @param columns Selected columns. 
+	 * @param key	Selected key and values, in row 0. Only 1 select query will be generated.
+	 * @return
+	 */
+	public static String buildSelect(Entity entity, Column[] columns, StringMatrix key) {
+		if (entity == null || columns == null ) return "";
+		StringBuffer columnsList = new StringBuffer();
+		String whereClause = "";
+		String selectQuery = "";
+		
+		for (int iCol = 0; iCol < columns.length; iCol++) {
+			if (iCol > 0) columnsList.append(", "); //$NON-NLS-1$
+			columnsList.append(columns[iCol].getName());
+		}
+		if (key != null) {
+			whereClause = getWhereClause(entity, key, 0);
+		}
+		selectQuery += "SELECT " + columnsList; //$NON-NLS-1$
+		selectQuery += " FROM " + entity.getQuotedTableName(); //$NON-NLS-1$
+		if (whereClause.length() > 0) 
+			selectQuery += " WHERE " + whereClause; //$NON-NLS-1$
+
+		return selectQuery;
+		
+	}
+	
+	/**
+	 * @param entity	The entity to get the where clause for
+	 * @param key		A StringMatrix with the colums that form the key and rows with the values
+	 * @param iRow		The key to the row that contains the values we are interested in
+	 * @return 	A String with where clause (without the 'WHERE' reserved word), adapted to the database of the entity
+	 */
+	public static String getWhereClause(Entity entity, StringMatrix key, int iRow) {
+		StringBuffer whereClause = new StringBuffer();
+		for (int iKey = 0; iKey < key.getNumColumns(); iKey++) {
+			if (iKey > 0) whereClause.append(" AND "); //$NON-NLS-1$
+			whereClause.append("("); //$NON-NLS-1$
+			whereClause.append(key.getHeaderColumn(iKey));
+			whereClause.append(" = "); //$NON-NLS-1$
+			whereClause.append(quoteValue( entity, key.getHeaderColumn(iKey), key.get(iKey, iRow)));
+			whereClause.append(")"); //$NON-NLS-1$
+		}
+		return whereClause.toString();
+	}
+	/**
+	 * Default-value function.
+	 * Generates a whereClause adapted to the entity´s database
+	 * with the data of the first row of the StringMatrix (row 0).
+	 * @see com.quantum.util.sql.SQLInstructionBuilder#getWhereClause
+	 */
+	public static String getWhereClause(Entity entity, StringMatrix key) {
+		return getWhereClause(entity, key, 0);
+	}
+
+	/**
+	 * Quotes the 'value' according with the type of the column and the database
+	 * @param entity	Entity 
+	 * @param columnName	Name of the column in the Entity
+	 * @param value		Value of the column, to be quoted
+	 */
+	public static String quoteValue(Entity entity, String columnName, String value) {
+		Bookmark bookmark = entity.getBookmark();
+		DatabaseAdapter adapter = bookmark.getAdapter();
+		
+		if (adapter != null && entity != null && getColumn(entity, columnName) != null) {
+			Column column = getColumn(entity, columnName);
+			 return adapter.quote(value, column.getType(), column.getTypeName());
+		} else {
+			return value;
+		}
+}
+	/**
+	 * Wrapper function to avoid exception handling
+	 * @param entity	The entity that has the column
+	 * @param columnName	The column name
+	 * @return	A Column object from that entity, with the given name. null if not found or not connected. 
+	 */
+	public static Column getColumn(Entity entity, String columnName)  {
+		try {
+			return entity == null ? null : entity.getColumn(columnName);
+		} catch (NotConnectedException e) {
+			return null;
+		} catch (SQLException e) {
+			return null;
+		}
+	}
+
+}