owl/trunk/scoreTargetMoves.java

import tools.MySQLConnection;

import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

public class scoreTargetMoves {

        /**
         * 
         * calculate count, score etc. for all targetMoves 
         * 
         * @author lappe
         */
        static int maxRank = 21; // value to replace for non-existence of central redue in the resultvector (rank=0) 
        // higher values should penalize non-existence more
        static int VL=1; // Verbosity Level 
        static final String dbserver = "white";
        static final String user = MySQLConnection.getUserName();
        static final String pwd = "nieve";
        static MySQLConnection conn;
        static String prgID = "V04"; 
        static String backgrndDB = "cullpdb_90";   // change!!
        static String hashDB = "nbhashing";
        static String targetDB = "caspm"; 
        static int sTotal, sRank; 
        
        public static void main(String[] args) throws SQLException{
                
                if (args.length<2) {
                        System.err.println("Must passed 2 parameters: <graph_id>, <target moves table>");
                        System.exit(1);
                } 
                int graph_id = Integer.parseInt(args[0]);
                String targetMovesTable = targetDB+"."+args[1];
                
                int num, i, j, total, rank, deltaRank=0, counter=0, nullrank=maxRank, minus, mcn, plus, pcn;
                String sql, cid, res, sstype, nn, pred="", mres, mss, pres, pss;  
                Statement mstmt;  
                ResultSet mrsst;
                                
                conn = new MySQLConnection(dbserver,user,pwd, backgrndDB); // connection to the the background DB!  
                //System.out.println("Scoring Target neighborhoods "+prgID); 
 

                // preparing the result db 
                //sql = "drop table IF EXISTS "+scoreTableName+";";
                //mstmt = conn.createStatement();
                //mstmt.executeUpdate(sql); 
                //mstmt.close(); 
                //sql = "create table "+scoreTableName+" select * from "+targetScore;  
                //mstmt = conn.createStatement();
                //mstmt.executeUpdate(sql); 
                //mstmt.close(); 

                sql = "select graph_id, cid, num, res, sstype, i, j, minus, mres, mss, mcn, plus, pres, pss, pcn, nn" +
                " from "+targetMovesTable+
                " WHERE graph_id="+graph_id+
                " order by graph_id, cid, num, i, j ;";

                mstmt = conn.createStatement();
                mrsst = mstmt.executeQuery(sql); 
                counter=0; 
                while (mrsst.next()) {
                        counter++;
                        // System.out.print("\n"+counter+":\t"); 

                        //graph_id = mrsst.getInt( 1);
                        cid      = mrsst.getString(2);
                        num      = mrsst.getInt(3);
                        res      = mrsst.getString(4);
                        sstype   = mrsst.getString(5);
                        i        = mrsst.getInt(6);
                        j        = mrsst.getInt(7);
                        minus    = mrsst.getInt(8);
                        mres     = mrsst.getString(9);
                        mss      = mrsst.getString(10);
                        mcn      = mrsst.getInt(11);
                        plus     = mrsst.getInt(12);
                        pres     = mrsst.getString(13);
                        pss      = mrsst.getString(14);
                        pcn      = mrsst.getInt(15);
                        nn       = mrsst.getString(16);

//                      graph_id | cid | num | res  | sstype | i | j  | minus | mres | mss  | mcn | plus | pres | pss  | pcn | nn | total | rank | deltarank | score
                        System.out.print(graph_id+"\t"+cid+"\t"+num+"\t"+res+"\t"+sstype+"\t"+i+"\t"+j+"\t"); 
                        System.out.print(minus+"\t"+mres+"\t"+mss+"\t"+mcn+"\t"+plus+"\t"+pres+"\t"+pss+"\t"+pcn+"\t"+nn+"\t");

                        if (j==0) { // top entry / column of movematrix 
                                pred=nn; 
                        }
                        if (VL>=2) System.out.println("\n["+i+","+j+"]");
                        getCountRank( nn, res, pred); 
                        total = sTotal; 
                        rank = sRank;
                        if (i==0 && j==0) nullrank = rank; 
                        deltaRank = rank-nullrank; 
                        System.out.println(total+"\t"+rank+"\t"+deltaRank+"\t"+(pcn*mcn*deltaRank)); 
                        // graph_id | node_id | cid | num | res  | sstype | i | j  | minus | mres | mss  | mcn | plus | pres | pss  | pcn | nn | total | rank | deltarank | score

                        /*sql = "update "+scoreTableName+" set total="+sTotal+", rank="+sRank+", deltarank="+deltaRank
                                +" where graph_id="+graph_id+" and node_id="+node_id+" and cid='"+cid+"' and num="+num
                                +" and i="+i+" and j="+j+";";
                                nstmt = conn.createStatement();
                                nstmt.executeUpdate(sql); 
                                nstmt.close(); 
                         */ 
                } // end while 

                // Cleanup ... 
                mrsst.close(); 
                mstmt.close(); 

                //System.out.println("fin."); 
        }       // end main 


        private static void getCountRank( String nbs, String centRes, String predec) throws SQLException {
                String sql, res; 
                Statement stmt;  
                ResultSet rsst;
                int counter=0, c=0, lastc=0, rank = 0; 
                boolean seenCentRes = false; 
                
                if (VL>=2) {
                        System.out.println("getCountRank for ");
                        System.out.println("nbs    : "+nbs); 
                        System.out.println("centRes: "+centRes);
                }
                String hashKey = ""; 
                String hashTable = "";
                if (nbs.replace("%", "").length()>2) { // case 1: neighbourhood at least length 2
                        hashKey = nbs.replace("%","").substring(0, 2);
                        hashTable = hashDB+"."+backgrndDB+"_"+hashKey;
                        sql = "select sum(c) from "+hashTable+" where n like '"+nbs+"';";
                        stmt = conn.createStatement();
                        rsst = stmt.executeQuery(sql);
                        if (rsst.next()) sTotal = rsst.getInt( 1);
                        rsst.close(); 
                        stmt.close(); 
                } else { // case 2: length on neighbourhood is 1: we just have 'x', we query whole table
                        sql = "select sum(c) from "+backgrndDB+".nb_equals;";
                        stmt = conn.createStatement();
                        rsst = stmt.executeQuery(sql);
                        if (rsst.next()) sTotal = rsst.getInt( 1);
                        rsst.close(); 
                        stmt.close();                           
                }

                if (VL>=2) System.out.println( "Total : "+sTotal); 

                if (sTotal>0) {
                        if (!hashTable.equals("")) { // i.e. we set hashTable (case 1 above) 
                                sql = "select res, sum(c) as t from "+hashTable+" where n like '"+nbs+"' group by res order by t DESC;";
                        } else { // i.e. we didn't set hashTable (case 2 above)
                                sql = "select res, sum(c) as t from "+backgrndDB+".nb_equals group by res order by t DESC;";
                        }
                        stmt = conn.createStatement();
                        rsst = stmt.executeQuery(sql);
                        if (VL>=2) System.out.println("###\tres\ttotal t");
                        sRank = maxRank; 
                        while (rsst.next()) {   
                                counter++; 
                                res = rsst.getString(1); // 1st column -- res
                                c = rsst.getInt( 2); // 2nds column : count/residue
                                if (VL>=2) System.out.print(counter+"\t"+res+"\t"+c);

                                if ((c == lastc) && (lastc>0) && seenCentRes) { // tie 
                                        if (VL>=2) System.out.print(" <-- TIE!");
                                        rank = counter;                 
                                } // end if 
                                if (res.equals(centRes)) { 
                                        if (VL>=2) System.out.print(" <== " + centRes);
                                        seenCentRes = true;
                                        rank = counter; 
                                }
                                if (VL>=2) System.out.println(".");
                        }
                        if (VL>=2) System.out.println("=> rank "+rank); 
                        rsst.close(); 
                        stmt.close(); 
                } // endif sTotal > 0 
                if (rank==0) sRank = maxRank;
                else sRank = rank; 
        } // end of getCountRank 

} // end class 
Revision:	419
Committed:	Thu Nov 22 14:09:18 2007 UTC (16 years, 11 months ago) by duarte
Original Path:	*branches/aglappe-jung/scoreTargetMoves.java*
File size:	6454 byte(s)
Log Message:	Re-branching for JUNG2 development
Line	User	Rev	File contents
1	lappe	289	import tools.MySQLConnection;
2
3			import java.sql.SQLException;
4			import java.sql.Statement;
5			import java.sql.ResultSet;
6
7			public class scoreTargetMoves {
8
9			/**
10			*
11			* calculate count, score etc. for all targetMoves
12			*
13			* @author lappe
14			*/
15			static int maxRank = 21; // value to replace for non-existence of central redue in the resultvector (rank=0)
16			// higher values should penalize non-existence more
17			static int VL=1; // Verbosity Level
18	duarte	375	static final String dbserver = "white";
19			static final String user = MySQLConnection.getUserName();
20			static final String pwd = "nieve";
21	lappe	289	static MySQLConnection conn;
22	duarte	375	static String prgID = "V04";
23	duarte	390	static String backgrndDB = "cullpdb_90"; // change!!
24	duarte	339	static String hashDB = "nbhashing";
25	duarte	375	static String targetDB = "caspm";
26	lappe	289	static int sTotal, sRank;
27
28	duarte	366	public static void main(String[] args) throws SQLException{
29	lappe	289
30	duarte	375	if (args.length<2) {
31	duarte	390	System.err.println("Must passed 2 parameters: <graph_id>, <target moves table>");
32	duarte	375	System.exit(1);
33			}
34	duarte	339	int graph_id = Integer.parseInt(args[0]);
35	duarte	390	String targetMovesTable = targetDB+"."+args[1];
36	duarte	339
37	duarte	375	int num, i, j, total, rank, deltaRank=0, counter=0, nullrank=maxRank, minus, mcn, plus, pcn;
38			String sql, cid, res, sstype, nn, pred="", mres, mss, pres, pss;
39	lappe	300	Statement mstmt;
40	lappe	289	ResultSet mrsst;
41
42	duarte	375	conn = new MySQLConnection(dbserver,user,pwd, backgrndDB); // connection to the the background DB!
43	duarte	390	//System.out.println("Scoring Target neighborhoods "+prgID);
44	duarte	375
45	lappe	289
46	duarte	366	// preparing the result db
47	duarte	390	//sql = "drop table IF EXISTS "+scoreTableName+";";
48			//mstmt = conn.createStatement();
49			//mstmt.executeUpdate(sql);
50			//mstmt.close();
51			//sql = "create table "+scoreTableName+" select * from "+targetScore;
52			//mstmt = conn.createStatement();
53			//mstmt.executeUpdate(sql);
54			//mstmt.close();
55	lappe	289
56	duarte	375	sql = "select graph_id, cid, num, res, sstype, i, j, minus, mres, mss, mcn, plus, pres, pss, pcn, nn" +
57	duarte	390	" from "+targetMovesTable+
58	duarte	366	" WHERE graph_id="+graph_id+
59	duarte	375	" order by graph_id, cid, num, i, j ;";
60	duarte	366
61			mstmt = conn.createStatement();
62			mrsst = mstmt.executeQuery(sql);
63			counter=0;
64			while (mrsst.next()) {
65			counter++;
66			// System.out.print("\n"+counter+":\t");
67
68			//graph_id = mrsst.getInt( 1);
69	duarte	375	cid = mrsst.getString(2);
70			num = mrsst.getInt(3);
71			res = mrsst.getString(4);
72			sstype = mrsst.getString(5);
73			i = mrsst.getInt(6);
74			j = mrsst.getInt(7);
75			minus = mrsst.getInt(8);
76			mres = mrsst.getString(9);
77			mss = mrsst.getString(10);
78			mcn = mrsst.getInt(11);
79			plus = mrsst.getInt(12);
80			pres = mrsst.getString(13);
81			pss = mrsst.getString(14);
82			pcn = mrsst.getInt(15);
83			nn = mrsst.getString(16);
84	duarte	366
85	duarte	375	// graph_id \| cid \| num \| res \| sstype \| i \| j \| minus \| mres \| mss \| mcn \| plus \| pres \| pss \| pcn \| nn \| total \| rank \| deltarank \| score
86			System.out.print(graph_id+"\t"+cid+"\t"+num+"\t"+res+"\t"+sstype+"\t"+i+"\t"+j+"\t");
87	duarte	366	System.out.print(minus+"\t"+mres+"\t"+mss+"\t"+mcn+"\t"+plus+"\t"+pres+"\t"+pss+"\t"+pcn+"\t"+nn+"\t");
88
89			if (j==0) { // top entry / column of movematrix
90			pred=nn;
91			}
92			if (VL>=2) System.out.println("\n["+i+","+j+"]");
93			getCountRank( nn, res, pred);
94			total = sTotal;
95			rank = sRank;
96			if (i==0 && j==0) nullrank = rank;
97			deltaRank = rank-nullrank;
98			System.out.println(total+"\t"+rank+"\t"+deltaRank+"\t"+(pcnmcndeltaRank));
99			// graph_id \| node_id \| cid \| num \| res \| sstype \| i \| j \| minus \| mres \| mss \| mcn \| plus \| pres \| pss \| pcn \| nn \| total \| rank \| deltarank \| score
100
101			/*sql = "update "+scoreTableName+" set total="+sTotal+", rank="+sRank+", deltarank="+deltaRank
102	lappe	289	+" where graph_id="+graph_id+" and node_id="+node_id+" and cid='"+cid+"' and num="+num
103			+" and i="+i+" and j="+j+";";
104			nstmt = conn.createStatement();
105			nstmt.executeUpdate(sql);
106			nstmt.close();
107	duarte	366	*/
108			} // end while
109	lappe	289
110	duarte	366	// Cleanup ...
111			mrsst.close();
112			mstmt.close();
113
114	duarte	390	//System.out.println("fin.");
115	lappe	289	} // end main
116
117
118	duarte	375	private static void getCountRank( String nbs, String centRes, String predec) throws SQLException {
119	lappe	289	String sql, res;
120			Statement stmt;
121			ResultSet rsst;
122			int counter=0, c=0, lastc=0, rank = 0;
123			boolean seenCentRes = false;
124
125	duarte	366	if (VL>=2) {
126			System.out.println("getCountRank for ");
127			System.out.println("nbs : "+nbs);
128			System.out.println("centRes: "+centRes);
129			}
130			String hashKey = "";
131			String hashTable = "";
132			if (nbs.replace("%", "").length()>2) { // case 1: neighbourhood at least length 2
133			hashKey = nbs.replace("%","").substring(0, 2);
134			hashTable = hashDB+"."+backgrndDB+"_"+hashKey;
135			sql = "select sum(c) from "+hashTable+" where n like '"+nbs+"';";
136			stmt = conn.createStatement();
137			rsst = stmt.executeQuery(sql);
138			if (rsst.next()) sTotal = rsst.getInt( 1);
139			rsst.close();
140			stmt.close();
141			} else { // case 2: length on neighbourhood is 1: we just have 'x', we query whole table
142			sql = "select sum(c) from "+backgrndDB+".nb_equals;";
143			stmt = conn.createStatement();
144			rsst = stmt.executeQuery(sql);
145			if (rsst.next()) sTotal = rsst.getInt( 1);
146			rsst.close();
147			stmt.close();
148			}
149
150			if (VL>=2) System.out.println( "Total : "+sTotal);
151
152			if (sTotal>0) {
153			if (!hashTable.equals("")) { // i.e. we set hashTable (case 1 above)
154			sql = "select res, sum(c) as t from "+hashTable+" where n like '"+nbs+"' group by res order by t DESC;";
155			} else { // i.e. we didn't set hashTable (case 2 above)
156			sql = "select res, sum(c) as t from "+backgrndDB+".nb_equals group by res order by t DESC;";
157	lappe	289	}
158	duarte	366	stmt = conn.createStatement();
159			rsst = stmt.executeQuery(sql);
160			if (VL>=2) System.out.println("###\tres\ttotal t");
161			sRank = maxRank;
162			while (rsst.next()) {
163			counter++;
164			res = rsst.getString(1); // 1st column -- res
165			c = rsst.getInt( 2); // 2nds column : count/residue
166			if (VL>=2) System.out.print(counter+"\t"+res+"\t"+c);
167	lappe	289
168	duarte	366	if ((c == lastc) && (lastc>0) && seenCentRes) { // tie
169			if (VL>=2) System.out.print(" <-- TIE!");
170			rank = counter;
171			} // end if
172			if (res.equals(centRes)) {
173			if (VL>=2) System.out.print(" <== " + centRes);
174			seenCentRes = true;
175			rank = counter;
176	lappe	289	}
177	duarte	366	if (VL>=2) System.out.println(".");
178			}
179			if (VL>=2) System.out.println("=> rank "+rank);
180			rsst.close();
181			stmt.close();
182			} // endif sTotal > 0
183			if (rank==0) sRank = maxRank;
184			else sRank = rank;
185	lappe	289	} // end of getCountRank
186
187			} // end class