---

BatchTool.java

Go to the documentation of this file.

package net.threebit.utils.sosc;

/*
        Copyright 2003 Shawn Deleurme
        Copyright 2003 Kevin O'Donnell

        This program is free software; you can redistribute it and/or modify
        it under the terms of the GNU General Public License as published by
        the Free Software Foundation; either version 2 of the License, or
        (at your option) any later version.

        This program is distributed in the hope that it will be useful,
        but WITHOUT ANY WARRANTY; without even the implied warranty of
        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
        GNU General Public License for more details.

        You should have received a copy of the GNU General Public License
        along with this program; if not, write to the Free Software
        Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

import java.io.*;
import java.sql.*;
import java.util.*;

public class BatchTool extends DbTool {

        public String CONTENTARGUMENTS = "_PREFIX__B_atchContentArguments";
        public String CONTENTS = "_PREFIX__B_atchContents";
        public String BATCHES = "_PREFIX__B_atches";
        public String PROFILES = "_PREFIX__B_atchProfiles";

        final static String MSSQL = 
                " create table _PROFILES_ ( \n" +
                "       id int primary key identity, \n" +
                "       name varchar(100) \n" +
                " ); \n" +
                " create table _BATCHES_ ( \n" +
                "       id int primary key identity, \n" +
                "       profile int foreign key references _PROFILES_(id), \n" +
                "       created datetime default getdate(), \n" +
                "   status varchar(20) not null default 'review' \n" +
                " ); \n" +
                " create table _CONTENTS_ ( \n" +
                "       id int primary key identity, \n" +
                "       batch int foreign key references _BATCHES_(id), \n" +
                "       i int, \n" +
                "   v varchar(1000), \n" +
                "               killed bit default 0 \n" +
                " ); \n" +
                " create table _CONTENTARGUMENTS_ ( \n" +
                "       id int primary key identity, \n" +
                "       content int foreign key references _CONTENTS_(id), \n" +
                "   k varchar(100), \n" +
                "   v varchar(1000) \n" +
                " ); \n"
                ;

        private String prefix = null;

        public BatchTool (String prefix, String className, String url, String userName, String password) throws Exception {
                super(className, url, userName, password);
                setPrefix(prefix);
        }

        public BatchTool (String prefix, Connection connection) throws Exception {
                super(connection);
                setPrefix(prefix);
        }

        private void setPrefix (String prefix) throws Exception {
                if (prefix == null) { throw new Exception("Cannot specify a null prefix value; use an empty string instead"); }
                this.prefix = prefix;

                String b = "B";
                if (prefix.equals("")) { b = "b"; }
                CONTENTARGUMENTS = CONTENTARGUMENTS.replaceAll("_PREFIX_",prefix).replaceAll("_B_",b);
                CONTENTS = CONTENTS.replaceAll("_PREFIX_",prefix).replaceAll("_B_",b);
                BATCHES = BATCHES.replaceAll("_PREFIX_",prefix).replaceAll("_B_",b);
                PROFILES = PROFILES.replaceAll("_PREFIX_",prefix).replaceAll("_B_",b);
        }

        public String getSchemaSQL (String dbType)  throws Exception {
                String sql = null;
                if (dbType.equals("pgsql")) { throw new Exception("Sorry, pgsql is not supported yet;"); }
                else if (dbType.equals("mssql")) { sql = MSSQL; }
                else { throw new Exception("Unsupported dbType: '" + dbType + "'"); }
                sql = sql.replaceAll("_PROFILES_",PROFILES);
                sql = sql.replaceAll("_CONTENTARGUMENTS_",CONTENTARGUMENTS);
                sql = sql.replaceAll("_CONTENTS_",CONTENTS);
                sql = sql.replaceAll("_BATCHES_",BATCHES);
                return sql;
        }

        public void dropSchema (String dbType) throws Exception {
                try {
                        update(
                                " drop table " + CONTENTARGUMENTS + ";\n" +
                                " drop table " + CONTENTS + ";\n" +
                                " drop table " + BATCHES + ";\n" +
                                " drop table " + PROFILES + ";\n" 
                        );
                }
                catch (Exception e) {
                        // just assume this is a "the tables did not exist error anyway";
                        e.printStackTrace(System.out);
                }
        }

        public void createSchema (String dbType) throws Exception {
                update( getSchemaSQL(dbType));
        }

        public int createProfile (String name) throws Exception {

                boolean exists = false;
                int id = 0;
                try {
                        id = getProfileId(name);
                        exists = true;
                }
                catch (Exception e) {
                        // ok!  it means the profile does not exist
                }

                if (exists) { throw new Exception("A profile with id " + id + " already exists named '" + name + "'"); }

                // Create it and return it's ID
                update("insert into " + PROFILES + " (name) values (" + quote(name) + ")");
                return getProfileId(name);
        };
        
        public int getProfileId (String name) throws Exception {
                ResultSet r = query("select id from " + PROFILES + " where name = " + quote(name));
                int rowCount = rowCount(r);
                if (rowCount != 1) { throw new Exception("Too many or too few rows returned (" + rowCount + ")"); }
                r.first();
                return r.getInt("id");
        }

        public int createBatch (String profile) throws Exception {
                int profileId = getProfileId(profile);
                update("insert into " + BATCHES + " (profile) values ("+profileId+")");
                return getInt("select max(id) from " + BATCHES + " where profile = " + profileId);
        }

        public void addIntContents (int batch, ResultSet r) throws Exception {

                StringBuffer sql = new StringBuffer(); // insert into _CONTENTS_
                Map args = new HashMap();

                Map m = null;
                int content = -1;
                while (r.next()) {
                        content = r.getInt("content");
                        sql.append(" insert into " + CONTENTS + " (batch,i) values ("+batch+","+content+");\n");
                        m = toMap(r,false);
                        m.remove("content"); // this is not an 'argument'
                        args.put(new Integer(content),m);
                }
                // Actually add the contents
                update(sql.toString());
                r.close(); // release early, release often

                // Now add the arguments for each content member
                sql = new StringBuffer();
                r = getBatchContents(batch);
                int id = -1;
                content = -1;
                while (r.next()) {
                        id = r.getInt("id");
                        content = r.getInt("i");
                        m = (Map) args.get(new Integer(content));
                        for (Iterator i = m.keySet().iterator(); i.hasNext(); ) {
                                String key = (String) i.next();
                                String value = (String) m.get(key);
                                sql.append(" insert into "+CONTENTARGUMENTS+" (content,k,v) values ("+id+","+quote(key)+","+quote(value)+"); \n");
                        }
                }
                update(sql.toString());
        }

        public void addIntContents (String query) throws Exception {
                update( " insert into " + CONTENTS + " (batch,i) \n" + query);
        }

        public void addVarcharContents (String query) throws Exception {
                update( " insert into " + CONTENTS + " (batch,v) \n" + query);
        }

        public void addContentArguments () throws Exception {
        }

        public ResultSet getContentArguments(int batchId) throws Exception {
                // This will contain all of the necessary SQL for the "content arguments"
                StringBuffer sb = new StringBuffer();

                // Get the list of keys
                ResultSet r = query(
                        "select distinct k from "+CONTENTARGUMENTS+" bca \n" +
                        "  join "+CONTENTS+" bc on bc.id = bca.content and bc.killed = 0 \n" +
                        "  where bc.batch = " + batchId
                );

                int rows = rowCount(r);
                r.beforeFirst();

                // Build an array of keys that must be VIEWED
                String[] keys = new String[rows];
                for (int x = 0; x < rows; x++) { r.next(); keys[x] = r.getString("k"); }

                // First, the outer select
                sb.append("select content,\n");
                sb.append("  max(contentI) as contentI,\n");
                sb.append("  max(contentV) as contentV,\n");
                for (int x = 0; x < keys.length; x++) {
                        sb.append("  max("+keys[x]+") as "+keys[x]+",\n");
                }
                sb.deleteCharAt( sb.length()-2 ); // trailing ","
                sb.append("from (\n");

                // Now create a one-time view of all of the key/value pairs.  Performance
                // sucks the big here, but this should not be on the critical path anyway.
                for (int x = 0; x < keys.length; x++) {
                        sb.append("  select bca.id, content, bc.i as contentI, bc.v as contentv, ");
                        for (int y = 0; y < keys.length; y++) {
                                sb.append((x==y) ? " bca.v" : " null");
                                sb.append(" as " + keys[y]);
                                sb.append(((y+1)==keys.length) ? "" : ",");
                        }
                        sb.append(" from "+CONTENTARGUMENTS+" bca \n");
                        sb.append("   join "+CONTENTS+" bc on bc.id = bca.content \n");
                        sb.append("   where \n");
                        sb.append("     bc.batch = " + batchId + "\n");
                        sb.append("     and k = '" + keys[x] + "'\n");
                        sb.append("     and bc.killed = 0 \n");
                        sb.append(((x+1)==keys.length) ? "" : "    union\n");
                }

                // Finish the outer select
                sb.append(") as s group by content\n");

                return query(sb.toString());
        }

        public ResultSet getBatchContents (int batch) throws Exception {
                return query("select * from " + CONTENTS + " where batch = " + batch + " and killed = 0 ");
        };

        public ResultSet batchSummary (int profileId) throws Exception {
                return query(
                        " select b.id, b.created, sum(case when bc.id is not null then 1 else 0 end) as size from " + BATCHES + " b " +
                        "   left join " + CONTENTS + " bc on bc.batch = b.id " +
                        "   where b.profile = " + profileId + " and bc.killed = 0 " +
                        "   group by b.id, b.created "
                );
        }

}

---