ParosTableParam.java

/*
 * Zed Attack Proxy (ZAP) and its related class files.
 * 
 * ZAP is an HTTP/HTTPS proxy for assessing web application security.
 * 
 * Copyright 2010 psiinon@gmail.com
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); 
 * you may not use this file except in compliance with the License. 
 * You may obtain a copy of the License at 
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0 
 *   
 * Unless required by applicable law or agreed to in writing, software 
 * distributed under the License is distributed on an "AS IS" BASIS, 
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 
 * See the License for the specific language governing permissions and 
 * limitations under the License. 
 */

package org.parosproxy.paros.db.paros;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.parosproxy.paros.db.DatabaseException;
import org.parosproxy.paros.db.DbUtils;
import org.parosproxy.paros.db.RecordParam;
import org.parosproxy.paros.db.TableParam;

public class ParosTableParam extends ParosAbstractTable implements TableParam {
    
    private static final String TABLE_NAME = "PARAM";
    
    private static final String PARAMID	= "PARAMID";
    private static final String SITE	= "SITE";
    private static final String TYPE	= "TYPE";
    private static final String NAME	= "NAME";
    private static final String USED	= "USED";
    private static final String FLAGS	= "FLAGS";
    private static final String VALUES	= "VALS";
    
    private PreparedStatement psRead = null;
    private PreparedStatement psInsert = null;
    private CallableStatement psGetIdLastInsert = null;
    private PreparedStatement psUpdate = null;
    private PreparedStatement psGetAll = null;

    public ParosTableParam() {
        
    }
        
    @Override
    protected void reconnect(Connection conn) throws DatabaseException {
        try {
			if (!DbUtils.hasTable(conn, TABLE_NAME)) {
			    // Need to create the table
			    DbUtils.executeAndClose(
			            conn.prepareStatement("CREATE cached TABLE PARAM (paramid bigint generated by default as identity (start with 1), site varchar(32768) not null, " +
			            "type varchar(32768) not null, name varchar(32768) not null, used int not null, flags varchar(32768) not null, vals varchar(8388608) not null)"));
			}

			psRead	= conn.prepareStatement("SELECT * FROM PARAM WHERE " + PARAMID + " = ?");
			
			psInsert = conn.prepareStatement("INSERT INTO PARAM (" + SITE + "," + TYPE + "," + NAME + "," + USED + "," + FLAGS + "," + VALUES + ") VALUES (?, ?, ?, ?, ?, ?)");
			psGetIdLastInsert = conn.prepareCall("CALL IDENTITY();");

			psUpdate = conn.prepareStatement("UPDATE PARAM SET " +
					USED + " = ?," + 
					FLAGS + " = ?," + 
					VALUES + " = ? " +
					"WHERE " + PARAMID + " = ?");

			psGetAll = conn.prepareStatement("SELECT * FROM PARAM");
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
    }
  
	/* (non-Javadoc)
	 * @see org.parosproxy.paros.db.paros.TableParam#read(long)
	 */
	@Override
	public synchronized RecordParam read(long urlId) throws DatabaseException {
		try {
			psRead.setLong(1, urlId);
			
			try (ResultSet rs = psRead.executeQuery()) {
				RecordParam result = build(rs);
				return result;
			}
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
	}
	
    /* (non-Javadoc)
	 * @see org.parosproxy.paros.db.paros.TableParam#getAll()
	 */
    @Override
	public List<RecordParam> getAll () throws DatabaseException {
    	try {
			List<RecordParam> result = new ArrayList<>();
			try (ResultSet rs = psGetAll.executeQuery()) {
				while (rs.next()) {
					result.add(new RecordParam(rs.getLong(PARAMID), rs.getString(SITE), rs.getString(TYPE),  
							rs.getString(NAME), rs.getInt(USED), rs.getString(FLAGS), rs.getString(VALUES)));
				}
			}
			
			return result;
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
    }

    /* (non-Javadoc)
	 * @see org.parosproxy.paros.db.paros.TableParam#insert(java.lang.String, java.lang.String, java.lang.String, int, java.lang.String, java.lang.String)
	 */
    @Override
	public synchronized RecordParam insert(String site, String type, String name, int used, String flags,
			String values) throws DatabaseException {
        try {
			psInsert.setString(1, site);
			psInsert.setString(2, type);
			psInsert.setString(3, name);
			psInsert.setInt(4, used);
			psInsert.setString(5, flags);
			psInsert.setString(6, values);
			psInsert.executeUpdate();
			
			long id;
			try (ResultSet rs = psGetIdLastInsert.executeQuery()) {
				rs.next();
				id = rs.getLong(1);
			}
			return read(id);
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
    }
    
    /* (non-Javadoc)
	 * @see org.parosproxy.paros.db.paros.TableParam#update(long, int, java.lang.String, java.lang.String)
	 */
    @Override
	public synchronized void update(long paramId, int used, String flags,
			String values) throws DatabaseException {
        try {
			psUpdate.setInt(1, used);
			psUpdate.setString(2, flags);
			psUpdate.setString(3, values);
			psUpdate.setLong(4, paramId);
			psUpdate.executeUpdate();
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
    }
    
    private RecordParam build(ResultSet rs) throws DatabaseException {
        try {
			RecordParam rt = null;
			if (rs.next()) {
			    rt = new RecordParam(rs.getLong(PARAMID), rs.getString(SITE), rs.getString(TYPE), 
			    		rs.getString(NAME), rs.getInt(USED), rs.getString(FLAGS), rs.getString(VALUES));            
			}
			return rt;
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
    }

}