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 String user = "lappe"    ; // change user name!!
        static MySQLConnection conn;
        static String prgID = "V02"; 
        static String backgrndDB = "cullpdb_20";  
        static String targetDB = "CASP_decoys"; 
        static String targetNodes = "target_node";
        static String targetEdges = "target_edge";
        static int sTotal, sRank; 
        
        public static void main(String[] args) {
                
                int graph_id, node_id, num, i, j, total, rank, deltaRank=0, counter=0, nullrank=maxRank;
                String sql, scoreTableName, cid, res, sstype, nn, pred="";  
                Statement mstmt, nstmt;  
                ResultSet mrsst;
                                
                try {
                        conn = new MySQLConnection("white",user,"nieve", backgrndDB); // connection to the the background DB!  
                        System.out.println("Scoring Target neighborhoods v.0.4. "); 
                        scoreTableName = targetDB+".score_"+prgID+"_"+backgrndDB; 
                        System.out.println("scoreTableName:"+scoreTableName);
                        
                        // preparing the result db 
                        sql = "drop table IF EXISTS "+scoreTableName+";";
                        mstmt = conn.createStatement();
                        mstmt.executeUpdate(sql); 
                        mstmt.close(); 
                        //sql = "create table "+scoreTableName+" select * from "+targetDB+".target_score where i=0 and j=0;";
                        //sql = "create table "+scoreTableName+" select * from "+targetDB+".target_score where i=0 or j=0;";
                        sql = "create table "+scoreTableName+" select * from "+targetDB+".target_score;"; // where pss='';"; to exclude ss inserts  
                        mstmt = conn.createStatement();
                        mstmt.executeUpdate(sql); 
                        mstmt.close(); 

                        sql = "select graph_id, node_id, cid, num, res, sstype, i, j, nn from "+scoreTableName+" order by graph_id, node_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);
                                node_id  = mrsst.getInt( 2);
                                cid      = mrsst.getString( 3);
                                num      = mrsst.getInt( 4);
                                res      = mrsst.getString( 5);
                                sstype   = mrsst.getString( 6);
                                i        = mrsst.getInt( 7);
                                j        = mrsst.getInt( 8);
                                nn       = mrsst.getString( 9);

                                System.out.print(graph_id+"\t"+node_id+"\t"+cid+"\t"+num+"\t"+res+"\t"+sstype+"\t"+i+"\t"+j+"\t"+nn+"\t");
                                if (j==0) { // top entry / column of movematrix 
                                        pred=nn; 
                                }
                                getCountRank( nn, res, pred); 
                                total = sTotal; 
                                rank = sRank;
                                if (i==0 && j==0) nullrank = rank; 
                                deltaRank = rank-nullrank; 
                                System.out.print("\t"+total+"\t"+rank); 
                                
                                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(); 

                } catch (SQLException e) {
                        e.printStackTrace();
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                } // end try/catch 
                System.out.println("fin."); 
        }       // end main 


        public static void getCountRank( String nbs, String centRes, String predec) {
                String sql, res; 
                Statement stmt;  
                ResultSet rsst;
                int counter=0, c=0, lastc=0, rank = 0; 
                boolean seenCentRes = false; 
                
                try {
                        // Hashing first row tables comes first
//                       Hashing first row tables comes first
                        if (VL>=2) {
                                System.out.println("getCountRank for ");
                                System.out.println("nbs    : "+nbs); 
                                System.out.println("centRes: "+centRes);
                                System.out.println("predec : "+predec);
                        }
                        String this_n = nbs.replace("%",""); 
                        String prec_n = predec.replace("%","");
                        // Check that this is really a superstring of predec ?! 
                        if (VL>=2) System.out.println("this_n: ["+this_n+"]"); 
                        if (VL>=2) System.out.println("prec_n: ["+prec_n+"]");
                        if (prec_n.equals(this_n)) {
                                if (VL>=2) System.out.println("have to create db for this "+prec_n);
                                sql = "create table IF NOT EXISTS nbhashtables."+prec_n+" as select res, n, k from "+backgrndDB+".nbstrings where n like '"+nbs+"';"; 
                                if (VL>=2) System.out.println(" >> "+sql); 
                                stmt = conn.createStatement();
                                stmt.executeUpdate( sql); 
                                stmt.close(); 
                        } else if (VL>=2) System.out.println("using preceding db of "+prec_n); 
                        
                        if (VL>=2) {
                                System.out.println("getCountRank for ");
                                System.out.println("nbs    : "+nbs); 
                                System.out.println("centRes: "+centRes);
                        }
                        
                        // sql = "select count(*) from "+backgrndDB+".single_model_node where n like '"+nbs+"';";
                        sql = "select count(*) from nbhashtables."+prec_n+" where n like '"+nbs+"';";
                        if (VL>=2) System.out.println( sql); 
                        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) {
                                // sql = "select res, count(*) as t from "+backgrndDB+".single_model_node where n like '"+nbs+"' group by res order by t DESC;";
                                sql = "select res, count(*) as t from nbhashtables."+prec_n+" where n like '"+nbs+"' 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; 
                } catch (SQLException e) {
                        e.printStackTrace();
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                }
        } // end of getCountRank 

} // end class 
Revision:	296
Committed:	Tue Aug 21 09:11:57 2007 UTC (17 years, 2 months ago) by lappe
File size:	6715 byte(s)
Log Message:	Lame version that directly updates a table ... have to change to stdoutput
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			static String user = "lappe" ; // change user name!!
19			static MySQLConnection conn;
20			static String prgID = "V02";
21	lappe	296	static String backgrndDB = "cullpdb_20";
22	lappe	289	static String targetDB = "CASP_decoys";
23			static String targetNodes = "target_node";
24			static String targetEdges = "target_edge";
25			static int sTotal, sRank;
26
27			public static void main(String[] args) {
28
29			int graph_id, node_id, num, i, j, total, rank, deltaRank=0, counter=0, nullrank=maxRank;
30			String sql, scoreTableName, cid, res, sstype, nn, pred="";
31			Statement mstmt, nstmt;
32			ResultSet mrsst;
33
34			try {
35			conn = new MySQLConnection("white",user,"nieve", backgrndDB); // connection to the the background DB!
36			System.out.println("Scoring Target neighborhoods v.0.4. ");
37			scoreTableName = targetDB+".score_"+prgID+"_"+backgrndDB;
38			System.out.println("scoreTableName:"+scoreTableName);
39
40			// preparing the result db
41			sql = "drop table IF EXISTS "+scoreTableName+";";
42			mstmt = conn.createStatement();
43			mstmt.executeUpdate(sql);
44			mstmt.close();
45			//sql = "create table "+scoreTableName+" select * from "+targetDB+".target_score where i=0 and j=0;";
46			//sql = "create table "+scoreTableName+" select * from "+targetDB+".target_score where i=0 or j=0;";
47	lappe	296	sql = "create table "+scoreTableName+" select * from "+targetDB+".target_score;"; // where pss='';"; to exclude ss inserts
48	lappe	289	mstmt = conn.createStatement();
49			mstmt.executeUpdate(sql);
50			mstmt.close();
51
52			sql = "select graph_id, node_id, cid, num, res, sstype, i, j, nn from "+scoreTableName+" order by graph_id, node_id, cid, num, i, j;";
53			mstmt = conn.createStatement();
54			mrsst = mstmt.executeQuery(sql);
55			counter=0;
56			while (mrsst.next()) {
57			counter++;
58			System.out.print("\n"+counter+":\t");
59
60			graph_id = mrsst.getInt( 1);
61			node_id = mrsst.getInt( 2);
62			cid = mrsst.getString( 3);
63			num = mrsst.getInt( 4);
64			res = mrsst.getString( 5);
65			sstype = mrsst.getString( 6);
66			i = mrsst.getInt( 7);
67			j = mrsst.getInt( 8);
68			nn = mrsst.getString( 9);
69
70			System.out.print(graph_id+"\t"+node_id+"\t"+cid+"\t"+num+"\t"+res+"\t"+sstype+"\t"+i+"\t"+j+"\t"+nn+"\t");
71			if (j==0) { // top entry / column of movematrix
72			pred=nn;
73			}
74			getCountRank( nn, res, pred);
75			total = sTotal;
76			rank = sRank;
77			if (i==0 && j==0) nullrank = rank;
78			deltaRank = rank-nullrank;
79			System.out.print("\t"+total+"\t"+rank);
80
81			sql = "update "+scoreTableName+" set total="+sTotal+", rank="+sRank+", deltarank="+deltaRank
82			+" where graph_id="+graph_id+" and node_id="+node_id+" and cid='"+cid+"' and num="+num
83			+" and i="+i+" and j="+j+";";
84			nstmt = conn.createStatement();
85			nstmt.executeUpdate(sql);
86			nstmt.close();
87
88			} // end while
89
90			// Cleanup ...
91			mrsst.close();
92			mstmt.close();
93
94			} catch (SQLException e) {
95			e.printStackTrace();
96			System.err.println("SQLException: " + e.getMessage());
97			System.err.println("SQLState: " + e.getSQLState());
98			} // end try/catch
99			System.out.println("fin.");
100			} // end main
101
102
103			public static void getCountRank( String nbs, String centRes, String predec) {
104			String sql, res;
105			Statement stmt;
106			ResultSet rsst;
107			int counter=0, c=0, lastc=0, rank = 0;
108			boolean seenCentRes = false;
109
110			try {
111			// Hashing first row tables comes first
112			// Hashing first row tables comes first
113			if (VL>=2) {
114			System.out.println("getCountRank for ");
115			System.out.println("nbs : "+nbs);
116			System.out.println("centRes: "+centRes);
117			System.out.println("predec : "+predec);
118			}
119			String this_n = nbs.replace("%","");
120			String prec_n = predec.replace("%","");
121			// Check that this is really a superstring of predec ?!
122			if (VL>=2) System.out.println("this_n: ["+this_n+"]");
123			if (VL>=2) System.out.println("prec_n: ["+prec_n+"]");
124			if (prec_n.equals(this_n)) {
125			if (VL>=2) System.out.println("have to create db for this "+prec_n);
126	lappe	296	sql = "create table IF NOT EXISTS nbhashtables."+prec_n+" as select res, n, k from "+backgrndDB+".nbstrings where n like '"+nbs+"';";
127	lappe	289	if (VL>=2) System.out.println(" >> "+sql);
128			stmt = conn.createStatement();
129			stmt.executeUpdate( sql);
130			stmt.close();
131			} else if (VL>=2) System.out.println("using preceding db of "+prec_n);
132
133			if (VL>=2) {
134			System.out.println("getCountRank for ");
135			System.out.println("nbs : "+nbs);
136			System.out.println("centRes: "+centRes);
137			}
138
139			// sql = "select count(*) from "+backgrndDB+".single_model_node where n like '"+nbs+"';";
140			sql = "select count(*) from nbhashtables."+prec_n+" where n like '"+nbs+"';";
141			if (VL>=2) System.out.println( sql);
142			stmt = conn.createStatement();
143			rsst = stmt.executeQuery(sql);
144			if (rsst.next()) sTotal = rsst.getInt( 1);
145			rsst.close();
146			stmt.close();
147			if (VL>=2) System.out.println( "Total : "+sTotal);
148
149			if (sTotal>0) {
150			// sql = "select res, count(*) as t from "+backgrndDB+".single_model_node where n like '"+nbs+"' group by res order by t DESC;";
151			sql = "select res, count(*) as t from nbhashtables."+prec_n+" where n like '"+nbs+"' group by res order by t DESC;";
152			stmt = conn.createStatement();
153			rsst = stmt.executeQuery(sql);
154			if (VL>=2) System.out.println("###\tres\ttotal t");
155			sRank = maxRank;
156			while (rsst.next()) {
157			counter++;
158			res = rsst.getString(1); // 1st column -- res
159			c = rsst.getInt( 2); // 2nds column : count/residue
160			if (VL>=2) System.out.print(counter+"\t"+res+"\t"+c);
161
162			if ((c == lastc) && (lastc>0) && seenCentRes) { // tie
163			if (VL>=2) System.out.print(" <-- TIE!");
164			rank = counter;
165			} // end if
166			if (res.equals(centRes)) {
167			if (VL>=2) System.out.print(" <== " + centRes);
168			seenCentRes = true;
169			rank = counter;
170			}
171			if (VL>=2) System.out.println(".");
172			}
173			if (VL>=2) System.out.println("=> rank "+rank);
174			rsst.close();
175			stmt.close();
176			} // endif sTotal > 0
177			if (rank==0) sRank = maxRank;
178			else sRank = rank;
179			} catch (SQLException e) {
180			e.printStackTrace();
181			System.err.println("SQLException: " + e.getMessage());
182			System.err.println("SQLState: " + e.getSQLState());
183			}
184			} // end of getCountRank
185
186			} // end class