SQLWindowController.java

/*
 *   This software is distributed under the terms of the FSF 
 *   Gnu Lesser General Public License (see lgpl.txt). 
 *
 *   This program is distributed WITHOUT ANY WARRANTY. See the
 *   GNU General Public License for more details.
 */
package com.scooterframework.builtin.databrowser;

import static com.scooterframework.web.controller.ActionControl.*;

import java.util.HashMap;
import java.util.Map;

import com.scooterframework.admin.Constants;
import com.scooterframework.admin.EnvConfig;
import com.scooterframework.builtin.AdminSignonController;
import com.scooterframework.common.util.Converters;
import com.scooterframework.common.util.CurrentThreadCacheClient;
import com.scooterframework.orm.misc.Paginator;
import com.scooterframework.orm.misc.SqlPageListSourceImpl;
import com.scooterframework.orm.sqldataexpress.processor.DataProcessor;
import com.scooterframework.orm.sqldataexpress.service.SqlServiceClient;
import com.scooterframework.web.controller.ACH;
import com.scooterframework.web.controller.ActionResult;
import com.scooterframework.web.util.W;

/**
 * DatabasesController class handles database related access.
 * 
 * @author (Fei) John Chen
 */
public class SQLWindowController extends ApplicationController {

	static {
		filterManagerFor(SQLWindowController.class).declareBeforeFilter(
				AdminSignonController.class, "loginRequired");
	}
	
    public String index() {
    	removeFromSessionData("database");
    	removeFromSessionData("limit");
    	removeFromSessionData("sql");
    	setViewData("databases", Database.getConnectionNames());
        return null;
    }
	
    public String execute() {
    	setViewData("databases", Database.getConnectionNames());
    	
    	String database = session("database");
    	String limit = session("limit");
    	String sql = session("sql");
    	String error = "";
    	
    	try {
        	if (sql != null && !"".equals(sql)) {
            	if (isSelectSQL(sql) && "true".equals(p(Constants.PAGED))) {
            		Map<String, String> pagingOptions = Converters.convertMapToMapSS(ACH.getAC().getParameterDataAsMap());
            		pagingOptions.put("limit", limit);
            		Paginator page = new Paginator(new SqlPageListSourceImpl(database, sql), pagingOptions);
                    setViewData("paged_records", page);
                    return ActionResult.forwardTo(viewPath("paged_list"));
            	}
            	else {
                	Map<String, Object> inputs = new HashMap<String, Object>();
                	inputs.put(DataProcessor.input_key_database_connection_name, database);
                	int count = SqlServiceClient.executeSQL(sql, inputs);
            		if (isDDL(sql)) {
                    	setViewData("sql_result", String.format("Success. Affected records count: %d", count));
            		}
            		else {
                    	setViewData("sql_result", "Success");
            		}
                    return ActionResult.forwardTo(viewPath("sql_result"));
            	}
        	}
        	else {
        		error = "Please enter a SQL statement.";
        	}
    	}
    	catch(Exception ex) {
    		error = "Error: " + ex.getMessage();
    		log.error("Error in execute()", ex);
    	}
    	
    	return html(color(error, "red"));
    }
    
    protected String viewPath(String action) {
        String controller = CurrentThreadCacheClient.controller();
        return EnvConfig.getViewURI(controller, action);
    }
    
    private boolean isSelectSQL(String sql) {
		return (sql.trim().toUpperCase().startsWith("SELECT")) ? true : false;
    }
    
    private boolean isDDL(String sql) {
		return ((sql.trim().toUpperCase().startsWith("CREATE")) || 
				(sql.trim().toUpperCase().startsWith("UPDATE")) || 
				(sql.trim().toUpperCase().startsWith("INSERT")) || 
				(sql.trim().toUpperCase().startsWith("DELETE"))) ? true : false;
    }
    
    private String session(String key) {
    	String value = p(key);
    	if (value == null || "".equals(value)) {
    		value = W.value(key);
    	}
    	else {
    		storeToSession(key, value);
    	}
    	return value;
    }
}