Commits

tbrugz committed 886c2e2

sqlrun: added 'auto-commit' & 'none' commit strategies ; added params

Comments (0)

Files changed (4)

sqlrun.template.properties

 
 sqlrun.dir=${basedir}
 sqlrun.loginvalidstatments=${errordir}/base_statements_errors.sql
-# prop 'sqlrun.commit.strategy' defines, hum, the commit strategy (one of: 'execid', 'run'; default: run)
+# prop 'sqlrun.commit.strategy' defines, hum, the commit strategy
+# (one of: 'autocommit', 'execid', 'run', 'none' ; default: run)
 sqlrun.commit.strategy=execid
 # TODO: sqlrun.commit.strategy=autocommit|statement|file|execid|run
 # TODO: 'sqlrun.commitinverval' - for 'statement' commit strategy (default is 100?)
 
 sqlrun.exec.65.statement=insert into <table> (<cols>) values (<vals>)
 
+sqlrun.exec.68.statement=update table_x set id_y = 0 where id_z = ?
+sqlrun.exec.68.param.1=42
+
 sqlrun.exec.70.files=TABLE_.*\\.sql
 sqlrun.exec.70.dir=${basedir}
 sqlrun.exec.70.loginvalidstatments=${errordir}/statements3_errors.sql

src/tbrugz/sqldump/SQLUtils.java

 		public static final String SUFFIX_INITSQL = ".initsql";
 
 		public static Connection initDBConnection(String propsPrefix, Properties papp) throws Exception {
+			return initDBConnection(propsPrefix, papp, false);
+		}
+
+		public static Connection initDBConnection(String propsPrefix, Properties papp, boolean autoCommit) throws Exception {
 			//init database
 			log.debug("initDBConnection...");
 			
 			Connection conn = DriverManager.getConnection(dbUrl, p);
 			// setAutoCommit(false): needed for postgresql for refcursor dumping. see: http://archives.postgresql.org/pgsql-sql/2005-06/msg00176.php
 			// anyway, i think this should be default
-			conn.setAutoCommit(false);
+			conn.setAutoCommit(autoCommit);
 			
 			String dbInitSql = papp.getProperty(propsPrefix+SUFFIX_INITSQL);
 			if(dbInitSql!=null) {

src_run/tbrugz/sqldump/sqlrun/SQLRun.java

 public class SQLRun {
 	
 	public static enum CommitStrategy {
-		//AUTO_COMMIT, //not implemented yet
+		AUTO_COMMIT,
 		//STATEMENT, //not implemented yet
 		//FILE, //not implemented yet
 		EXEC_ID,
-		RUN
+		RUN,
+		NONE
 	} 
 	
 	static Log log = LogFactory.getLog(SQLRun.class);
 	static String SUFFIX_DIR = ".dir";
 	static String SUFFIX_LOGINVALIDSTATEMENTS = ".loginvalidstatments";
 	static String SUFFIX_SPLIT = ".split"; //by semicolon - ';'
+	static String SUFFIX_PARAM = ".param";
 	
 	//properties
 	static final String PROP_COMMIT_STATEGY = "sqlrun.commit.strategy";
 
 	//suffix groups
 	static String[] PROC_SUFFIXES = { SUFFIX_FILE, SUFFIX_FILES, SUFFIX_STATEMENT, SUFFIX_IMPORT };
-	static String[] AUX_SUFFIXES = { SUFFIX_DIR, SUFFIX_LOGINVALIDSTATEMENTS, SUFFIX_SPLIT };
+	static String[] AUX_SUFFIXES = { SUFFIX_DIR, SUFFIX_LOGINVALIDSTATEMENTS, SUFFIX_SPLIT, SUFFIX_PARAM };
 	List<String> allAuxSuffixes = new ArrayList<String>();
 	
+	//other/reserved props
+	static final String PROP_PROCID = "_procid";
+	
 	Properties papp = new ParametrizedProperties();
 	Connection conn;
 	CommitStrategy commitStrategty = CommitStrategy.RUN;
 		Collections.sort(execkeys);
 		log.info("init processing...");
 		long initTime = System.currentTimeMillis();
-		commitStrategty = getCommitStrategy( papp.getProperty(PROP_COMMIT_STATEGY) );
 
 		Set<String> procIds = new TreeSet<String>();
 		for(String key: execkeys) {
 		for(String key: execkeys) {
 			boolean isExecId = false;
 			String procId = getExecId(key, PREFIX_EXEC);
+			String action = key.substring((PREFIX_EXEC+procId).length());
 			if(endsWithAny(key, PROC_SUFFIXES)) {
-				log.info(">>> processing: id = '"+procId+"'");
+				log.info(">>> processing: id = '"+procId+"' ; action = '"+action+"'");
 				isExecId = true;
 			}
+			papp.setProperty(PROP_PROCID, procId);
 			
 			boolean splitBySemicolon = Utils.getPropBool(papp, PREFIX_EXEC+procId+SUFFIX_SPLIT, true);
 			
 			else if(endsWithAny(key, allAuxSuffixes)) {
 				//do nothing here
 			}
+			else if(startsWithAny(action, allAuxSuffixes)) {
+				//do nothing here
+			}
 			else {
 				log.warn("unknown prop key format: '"+key+"'");
 			}
 		}
 		return false;
 	}
+
+	static boolean startsWithAny(String key, List<String> prefixes) {
+		for(String pre: prefixes) {
+			if(key.startsWith(pre)) { return true; }
+		}
+		return false;
+	}
 	
 	void doCommit() throws SQLException {
 		log.debug("committing...");
 	static CommitStrategy getCommitStrategy(String commit) {
 		CommitStrategy cs = CommitStrategy.RUN;
 		if(commit==null) {}
+		else if(commit.equals("autocommit")) { cs = CommitStrategy.AUTO_COMMIT; }
 		else if(commit.equals("execid")) { cs = CommitStrategy.EXEC_ID; }
 		else if(commit.equals("run")) { cs = CommitStrategy.RUN; }
+		else if(commit.equals("none")) { cs = CommitStrategy.NONE; }
 		else {
 			log.warn("unknown commit strategy: "+commit);
 		}
 		allAuxSuffixes.addAll(Arrays.asList(AUX_SUFFIXES));
 		allAuxSuffixes.addAll(new CSVImporter().getAuxSuffixes());
 		allAuxSuffixes.addAll(new RegexImporter().getAuxSuffixes());
+		
+		commitStrategty = getCommitStrategy( papp.getProperty(PROP_COMMIT_STATEGY) );
+		conn = SQLUtils.ConnectionUtil.initDBConnection(CONN_PROPS_PREFIX, papp, commitStrategty==CommitStrategy.AUTO_COMMIT);
 	}
 	
 	public static List<String> getFiles(String dir, String fileRegex) {
 		SQLRun sqlr = new SQLRun();
 		
 		try {
-			//SQLDump.init(args, sqlr.papp);
 			sqlr.init(args);
-			sqlr.conn = SQLUtils.ConnectionUtil.initDBConnection(CONN_PROPS_PREFIX, sqlr.papp);
 			if(sqlr.conn==null) { return; }
 			sqlr.doIt();
 		}

src_run/tbrugz/sqldump/sqlrun/StmtProc.java

 import java.io.IOException;
 import java.io.Writer;
 import java.sql.Connection;
+import java.sql.PreparedStatement;
 import java.sql.SQLException;
-import java.sql.Statement;
 import java.util.Properties;
 
 import org.apache.commons.logging.Log;
 		}
 		catch (NullPointerException npe) {
 			log.warn("error log file not defined. Ex: "+npe);
+			//npe.printStackTrace();
 			errorFileNotFoundWarned = true;
 		} catch (IOException e) {
 			log.warn("ioexception when opening error log file. Ex: "+e);
 		stmtStr = stmtStr.trim();
 		if(stmtStr.equals("")) { throw new IllegalArgumentException("null parameter"); }
 		
-		Statement stmt = conn.createStatement();
 		logStmt.debug("executing sql: "+stmtStr);
 		try {
-			int urows = stmt.executeUpdate(stmtStr);
+			PreparedStatement stmt = conn.prepareStatement(stmtStr);
+			setParameters(stmt);
+			int urows = stmt.executeUpdate();
 			logStmt.debug("updated "+urows+" rows");
 			return urows;
 		}
 		}
 	}
 	
+	void setParameters(PreparedStatement stmt) throws SQLException {
+		int i=1;
+		while(true) {
+			String key = SQLRun.PREFIX_EXEC+papp.getProperty(SQLRun.PROP_PROCID)+SQLRun.SUFFIX_PARAM+"."+i;
+			String param = papp.getProperty(key);
+			log.debug("param #"+i+"/"+key+": "+param);
+			if(param!=null) {
+				stmt.setString(i, param);
+			}
+			else { return; }
+			i++;
+		}
+	}
+	
 	public Connection getConn() {
 		return conn;
 	}