owl/trunk/makeNbhTbl.java

/****************************************************************************
 * Make neighbourhood table                                             
 * Author:      Henning Stehr
 * Date:        21/12/2005
 * Usage:       makenbh [start_index] [end_index]
 * Description: Simple command line program to generate the 'neighbourhoods'
 *              table in the 'newmsdgraph' database. The program will
 *              generate rows in the neighbourhood table for each node in
 *              each graph with start_index <= graph_id <= end_index.
 * Contact:     stehr@molgen.mpg.de
 *****************************************************************************/

import java.sql.*;

public class makeNbhTbl {

/* -------------------------------- constants ------------------------------------------*/
        
        // constants for database connection
        static final String    dbDriver = "org.gjt.mm.mysql.Driver";
        static final String    dbServer = "jdbc:mysql://fliederlila.molgen.mpg.de:3036/newmsdgraph";
        static final String    dbUserName = "henning";
        static final String    dbPassword = "das4ke";

        // constants for graph database
        //static final String    listTable = "henning.myfamily_list";
        //static final String    nodeTable = "henning.myfamily_nodes";
        //static final String    edgeTable = "henning.myfamily_edges";
        static final String    listTable = "list";
        static final String    nodeTable = "nodes";
        static final String    edgeTable = "edges";
        
        
        // constants for neighbourhood table
        //static final String    nbhTable = "henning.myfamily_nbhs";
        static final String    nbhTable = "neighbourhoods_sc_sc";
        static final String    edgeType = "SC_SC";
        static final String    tempNodes = "temp_nodes";
        static final String    tempEdges = "temp_edges";
        static final String    nbhTableDefinition = "(`graph_id` int(10) unsigned default NULL," +
                                                                                                " `cid` varchar(6) default NULL," +
                                                                                                " `num` int(5) unsigned default NULL," +
                                                                                                " `res` char(3) default NULL," +
                                                                                                " `entry_id` int(5) unsigned default NULL," +
                                                                                                " `size` int(3) unsigned default NULL, " +
                                                                                                " `hash` char(20) default NULL, " +
                                                                                                " `ALA` int(3) unsigned default NULL," +
                                                                                                " `ARG` int(3) unsigned default NULL," +
                                                                                                " `ASN` int(3) unsigned default NULL," +
                                                                                                " `ASP` int(3) unsigned default NULL," +
                                                                                                " `CYS` int(3) unsigned default NULL," +
                                                                                                " `GLN` int(3) unsigned default NULL," +
                                                                                                " `GLU` int(3) unsigned default NULL," +
                                                                                                " `GLY` int(3) unsigned default NULL," +
                                                                                                " `HIS` int(3) unsigned default NULL," +
                                                                                                " `ILE` int(3) unsigned default NULL," +
                                                                                                " `LEU` int(3) unsigned default NULL," +
                                                                                                " `LYS` int(3) unsigned default NULL," +
                                                                                                " `MET` int(3) unsigned default NULL," +
                                                                                                " `PHE` int(3) unsigned default NULL," +
                                                                                                " `PRO` int(3) unsigned default NULL," +
                                                                                                " `SER` int(3) unsigned default NULL," +
                                                                                                " `THR` int(3) unsigned default NULL," +
                                                                                                " `TRP` int(3) unsigned default NULL," +
                                                                                                " `TYR` int(3) unsigned default NULL," +
                                                                                                " `VAL` int(3) unsigned default NULL," +
                                                                                                " CONSTRAINT PRIMARY KEY (graph_id, cid, num));";
        
        // This array is used to convert an amino acid number to its three letter code.
        // The numbering is from 1 to 20 to ensure compatibility with function aa_three2num().
        static final String[]    aa_num2three = {"???", "ALA", "ARG", "ASN", "ASP", "CYS",
                                                            "GLN", "GLU", "GLY", "HIS", "ILE",
                                                            "LEU", "LYS", "MET", "PHE", "PRO",
                                                            "SER", "THR", "TRP", "TYR", "VAL"};
        
/* ------------------------------ class variables -------------------------------------*/       

        static Connection conn; // global database connection
        
/* -------------------------------- functions -----------------------------------------*/       
        
        // return the number of an amino acid by its three letter code
        public static int aa_three2num(String threeLetterCode) {
                if(threeLetterCode.equalsIgnoreCase("ALA")) return 1;
                if(threeLetterCode.equalsIgnoreCase("ARG")) return 2;
                if(threeLetterCode.equalsIgnoreCase("ASN")) return 3;
                if(threeLetterCode.equalsIgnoreCase("ASP")) return 4;
                if(threeLetterCode.equalsIgnoreCase("CYS")) return 5;
                if(threeLetterCode.equalsIgnoreCase("GLN")) return 6;
                if(threeLetterCode.equalsIgnoreCase("GLU")) return 7;
                if(threeLetterCode.equalsIgnoreCase("GLY")) return 8;
                if(threeLetterCode.equalsIgnoreCase("HIS")) return 9;
                if(threeLetterCode.equalsIgnoreCase("ILE")) return 10;
                if(threeLetterCode.equalsIgnoreCase("LEU")) return 11;
                if(threeLetterCode.equalsIgnoreCase("LYS")) return 12;
                if(threeLetterCode.equalsIgnoreCase("MET")) return 13;
                if(threeLetterCode.equalsIgnoreCase("PHE")) return 14;
                if(threeLetterCode.equalsIgnoreCase("PRO")) return 15;
                if(threeLetterCode.equalsIgnoreCase("SER")) return 16;
                if(threeLetterCode.equalsIgnoreCase("THR")) return 17;
                if(threeLetterCode.equalsIgnoreCase("TRP")) return 18;
                if(threeLetterCode.equalsIgnoreCase("TYR")) return 19;
                if(threeLetterCode.equalsIgnoreCase("VAL")) return 20;
                return -1;
        }
        
        public static String nbhGetHashValue(int[] nbh) {
                
                char             currentChar;
                StringBuffer resultBuffer = new StringBuffer("????????????????????");
                
                // nbh[i] is defined for i=1..20
                for(int i = 1; i <= 20; i++) {
                        currentChar = '?';
                        if(nbh[i] < 0) 
                                System.err.println("Error in function nbhGetHashValue: " +
                                                       "array entry nbh[" + i + "] = " + nbh[i] +" is negative.");
                        //else if(nbh[i] <= 9) currentChar = '0' + nbh[i];
                        //else if(nbh[i] <= 9 + 26) currentChar = 'A' + nbh[i];
                        else if(nbh[i] > 26 + 9)
                                System.err.println("Error in function nbhGetHashValue: " +
                                                           "array entry nbh[" + i + "] = " + nbh[i] + " is too big.");
                        else currentChar = Character.forDigit(nbh[i], 26 + 10);
                        resultBuffer.setCharAt(i-1, currentChar);
                }
                return resultBuffer.toString();
        }
        
        
        // open connection to database
        public static void openDBConnection() {
                
                try { // try to load driver
                    Class.forName(dbDriver).newInstance(); 
                    try {
                                conn = DriverManager.getConnection(dbServer, dbUserName, dbPassword);
                    } catch (SQLException E) {
                        System.err.println("Error: Unable to open database connection.");
                                System.err.println("SQLException: " + E.getMessage());
                                System.err.println("SQLState:     " + E.getSQLState());
                                System.err.println("VendorError:  " + E.getErrorCode());
                    } // end try/catch connection 
                } // end try load Driver 
                catch (Exception E) {
                    System.err.println("Error: Unable to load database driver.");
                    E.printStackTrace();
                } // end catch 
                
                return;
        }
        
        // close database connection
        public static void closeDBConnection() {
                
        try {
                conn.close();
        } catch (SQLException E) {
                System.out.println("Error: Unable to close connection.");
                System.out.println("SQLException: " + E.getMessage());
                System.out.println("SQLState:     " + E.getSQLState());
                System.out.println("VendorError:  " + E.getErrorCode());
                System.exit(1);
        } // end try/catch connection 
                
                return;
        }       
        
        // create new neighbourhood table if not exists
        public static void createNeighbourhoodTableIfNotExists() {
                Statement stmt;
                String query;
                try {
                        query = "CREATE TABLE IF NOT EXISTS " + nbhTable + " " + nbhTableDefinition + ";";
                    stmt = conn.createStatement();      
                    stmt.execute(query);
                } catch (SQLException E) {
                        System.err.println("Error: Unable to create neighbourhood table.");
                    System.err.println("SQLException: " + E.getMessage());
                    System.err.println("SQLState:     " + E.getSQLState());
                    System.err.println("VendorError:  " + E.getErrorCode());
                } // end catch          
                
                return;
        }
        
        // insert neighbourhood entries for each graph with from <= graph_id <= to
        public static void insertNeighbourhoods(int fromIdx, int toIdx) {
                Statement   graphsStmt,
                            nodesStmt,
                            edgesStmt;
                ResultSet   graphsRs,
                            nodesRs,
                                        edgesRs;
                String      query,
                            chainId = "?",
                            resType,
                            nbhResType,
                            hash;
                int         graphId = 0,
                            entryId,
                            resNum = 0,
                            numGraphs = 0,
                            numNodes = 0,
//                          existingNodes,
                            resTypeNum,
                            nbhResCount,
                            nbhResTypeNum,
                            nbhSize;
                int[]       nbh;
                
                try {
                        // get number of graphs
                        graphsStmt = conn.createStatement();
                        query = "SELECT COUNT(*) FROM " + listTable + 
                                " WHERE graph_id >= " + fromIdx + " AND graph_id <= " + toIdx + ";";
                    graphsRs = graphsStmt.executeQuery(query);
                    if(graphsRs.next()) {
                        numGraphs = graphsRs.getInt(1);
                    }
                    graphsRs.close();               
                    graphsStmt.close();
                    System.out.println("Processing " + numGraphs + (numGraphs==1?" graph...":" graphs..."));
                        
                        // iterate over all graphs
                        graphsStmt = conn.createStatement();
                        query = "SELECT graph_id, entry_id FROM " + listTable + 
                                " WHERE graph_id >= " + fromIdx + " AND graph_id <= " + toIdx + ";";
                    graphsRs = graphsStmt.executeQuery(query);
                    numGraphs = 0;
                    while(graphsRs.next()) {
                        graphId = graphsRs.getInt(1);
                        entryId = graphsRs.getInt(2);
                        
                        // check whether neighbourhoods for this graph already exist
//                      nodesStmt = conn.createStatement();
//                      query = "SELECT COUNT(*) FROM " + nbhTable + " WHERE graph_id = " + graphId + ";";
//                      nodesRs = nodesStmt.executeQuery(query);
//                      if(nodesRs.next()) {
//                              existingNodes = nodesRs.getInt(1);
//                              if(existingNodes > 0) {
//                                      System.out.println("Warning: Neighbourhoods for the graph with graph_id = "
//                                                                      + graphId + " already exist.");
//                              }
//                      }
//                      nodesRs.close();
//                      nodesStmt.close();
                                
                        // extract all nodes and edges for this graph into temporary tables
                        nodesStmt = conn.createStatement();
                        nodesStmt.addBatch("DROP TEMPORARY TABLE IF EXISTS " + tempNodes + ";");
                        nodesStmt.addBatch("DROP TEMPORARY TABLE IF EXISTS " + tempEdges + ";");
                        nodesStmt.addBatch("CREATE TEMPORARY TABLE " + tempNodes + 
                                                   " SELECT * FROM " + nodeTable + " WHERE graph_id = " + graphId + ";");
                        nodesStmt.addBatch("CREATE TEMPORARY TABLE " + tempEdges + 
                                               " SELECT * FROM " + edgeTable + " WHERE graph_id = " + graphId + 
                                               " AND " + edgeType + " > 0;"); // take only relevant edges
                        // if necessary create indices to speed up queries
                        nodesStmt.executeBatch();
                        nodesStmt.close();
                        
                        // iterate over all nodes for this graph
                        nodesStmt = conn.createStatement();                     
                        query = "SELECT cid, num, res FROM "+ tempNodes + ";"; // graph_id matches automatically
                        nodesRs = nodesStmt.executeQuery(query);
                        while(nodesRs.next()) {
                                chainId = nodesRs.getString(1);
                                resNum = nodesRs.getInt(2);
                                resType = nodesRs.getString(3);
                                resTypeNum = aa_three2num(resType);
                                if(resTypeNum < 1 || resTypeNum > 20) {
                                        System.err.println("Warning: Non standard amino acid " + resType + 
                                                                  " at position " + resNum + " in chain " + chainId + 
                                                                  " of graph " + graphId + ".");
                                }
                                                                
                                // get residue counts from edges table
                                edgesStmt = conn.createStatement();
                                query = "SELECT j_res AS res, COUNT(*) AS count FROM "+ tempEdges + 
                                        " WHERE i_cid = '" + chainId + "' AND i_num = " + resNum + 
                                        " GROUP BY j_res;";
                                //System.out.println(query);
                                edgesRs = edgesStmt.executeQuery(query);
                                
                                nbh = new int[21]; // set all residue counts to zero
                                nbhSize = 0;
                                while(edgesRs.next()) {
                                        nbhResType = edgesRs.getString(1);
                                        nbhResCount = edgesRs.getInt(2);
                                        nbhResTypeNum = aa_three2num(nbhResType);
                                        if(nbhResTypeNum >= 1 && nbhResTypeNum <= 20) {
                                                nbh[nbhResTypeNum] = nbhResCount;
                                                nbhSize += nbhResCount;
                                        }
                                }
                                edgesRs.close();
                                edgesStmt.close();
                                
                                // update nbh table
                                hash = nbhGetHashValue(nbh);
                                query = "INSERT INTO " + nbhTable + " (graph_id, cid, num, res, entry_id, size, hash, "
                                                       + "ALA, ARG, ASN, ASP, CYS, GLN, GLU, GLY, HIS, ILE, "
                                                       + "LEU, LYS, MET, PHE, PRO, SER, THR, TRP, TYR, VAL) " + 
                                        "VALUES (" + graphId + ",'" + chainId + "'," + resNum + ",'" 
                                                   + resType + "'," + entryId + "," + nbhSize + ",'" + hash + "',"
                                                   + nbh[1]  + "," + nbh[2]  + "," + nbh[3]  + "," + nbh[4]  + "," + nbh[5]  + ","
                                                   + nbh[6]  + "," + nbh[7]  + "," + nbh[8]  + "," + nbh[9]  + "," + nbh[10] + ","
                                                   + nbh[11] + "," + nbh[12] + "," + nbh[13] + "," + nbh[14] + "," + nbh[15] + ","
                                                   + nbh[16] + "," + nbh[17] + "," + nbh[18] + "," + nbh[19] + "," + nbh[20] + ");";
                                edgesStmt = conn.createStatement();
                                if(edgesStmt.executeUpdate(query) != 1) {
                                        System.err.println("Error: Insert into table " + nbhTable + " failed.");
                                        System.exit(1);
                                }
                                edgesStmt.close();
                                numNodes++;
                        } // end of while over nodes
                        nodesRs.close();
                        nodesStmt.close();
                        numGraphs++;
                        if(numGraphs % 1000 == 0) {
                                System.out.print(numGraphs + " ");
                                System.out.flush();
                        }
                        System.gc(); // clean up
                    } // end while over all graphs
                    graphsRs.close();               
                    graphsStmt.close();
                } catch (SQLException E) {
                        System.err.println("Error in function insertNeighbourhoods:");
                        System.err.println("graphId = "+ graphId +", chainId = "+ chainId +", resNum = "+ resNum);
                    System.err.println("SQLException: " + E.getMessage());
                    System.err.println("SQLState:     " + E.getSQLState());
                    System.err.println("VendorError:  " + E.getErrorCode());
                } // end catch
                
                System.out.println();
                System.out.println("Extracted "+ numNodes + " neighbourhood" + (numNodes==1?"":"s") 
                                        + " from " + numGraphs + " graph" + (numGraphs==1?"":"s") + ".");                       
                return;
        }
        
        public static void createIndicesOnNeighbourhoodTable() {
                // index on graph_id
                // index on graph_id, cid, num
                // index on res
                // index on size
                // index on hash
        }
        
/* ----------------------------------- main -------------------------------------------*/       
        
        public static void main(String[] args) {
                
                int from = 0,
                    to = 0;
                
                // read command line parameters         
                if ( args.length != 2) {
                    System.err.println("Usage: makeNbhTbl [start_index] [end_index]");
                    System.exit(1);
                } else {
                
                // read parameter 1
                        
                        try {
                                from = Integer.parseInt(args[0]); 
                                if(from <= 0) { 
                                        System.err.println("Parameter 1 has to be positive");
                                        System.exit(1);
                                }
                        }
                        catch(NumberFormatException e) {
                                System.err.println("Parameter 1 has to be an integer");
                                System.exit(1);
                        }

                // read parameter 2
                        
                        try { 
                                to = Integer.parseInt(args[1]);
                                if(to <= 0) { 
                                        System.err.println("Parameter 2 has to be positive");
                                        System.exit(1);
                                } else
                                if(from > to) {
                                        System.err.println("Start index has to be smaller or equal than end index");
                                        System.exit(1);
                                }
                        }
                        catch(NumberFormatException e) { 
                                System.err.println("Parameter 2 has to be an integer");
                                System.exit(1);
                        }
                        
                } 

                // execute              
                openDBConnection();
                createNeighbourhoodTableIfNotExists();
                insertNeighbourhoods(from, to);
                createIndicesOnNeighbourhoodTable();
                closeDBConnection();
                
        } // end of main

} // end of class makeNbhTbl
Revision:	492
Committed:	Wed Jan 2 13:18:57 2008 UTC (16 years, 8 months ago) by duarte
File size:	16108 byte(s)
Log Message:	Copied the aglappe-jung branch into trunk.
Line	User	Rev	File contents
1	stehr	24	/****************************************************************************
2			* Make neighbourhood table
3			* Author: Henning Stehr
4			* Date: 21/12/2005
5			* Usage: makenbh [start_index] [end_index]
6			* Description: Simple command line program to generate the 'neighbourhoods'
7			* table in the 'newmsdgraph' database. The program will
8			* generate rows in the neighbourhood table for each node in
9			* each graph with start_index <= graph_id <= end_index.
10			* Contact: stehr@molgen.mpg.de
11			*****************************************************************************/
12
13			import java.sql.*;
14
15			public class makeNbhTbl {
16
17			/* -------------------------------- constants ------------------------------------------*/
18
19			// constants for database connection
20			static final String dbDriver = "org.gjt.mm.mysql.Driver";
21			static final String dbServer = "jdbc:mysql://fliederlila.molgen.mpg.de:3036/newmsdgraph";
22			static final String dbUserName = "henning";
23			static final String dbPassword = "das4ke";
24
25			// constants for graph database
26			//static final String listTable = "henning.myfamily_list";
27			//static final String nodeTable = "henning.myfamily_nodes";
28			//static final String edgeTable = "henning.myfamily_edges";
29			static final String listTable = "list";
30			static final String nodeTable = "nodes";
31			static final String edgeTable = "edges";
32
33
34			// constants for neighbourhood table
35			//static final String nbhTable = "henning.myfamily_nbhs";
36			static final String nbhTable = "neighbourhoods_sc_sc";
37			static final String edgeType = "SC_SC";
38			static final String tempNodes = "temp_nodes";
39			static final String tempEdges = "temp_edges";
40			static final String nbhTableDefinition = "(`graph_id` int(10) unsigned default NULL," +
41			" `cid` varchar(6) default NULL," +
42			" `num` int(5) unsigned default NULL," +
43			" `res` char(3) default NULL," +
44			" `entry_id` int(5) unsigned default NULL," +
45			" `size` int(3) unsigned default NULL, " +
46			" `hash` char(20) default NULL, " +
47			" `ALA` int(3) unsigned default NULL," +
48			" `ARG` int(3) unsigned default NULL," +
49			" `ASN` int(3) unsigned default NULL," +
50			" `ASP` int(3) unsigned default NULL," +
51			" `CYS` int(3) unsigned default NULL," +
52			" `GLN` int(3) unsigned default NULL," +
53			" `GLU` int(3) unsigned default NULL," +
54			" `GLY` int(3) unsigned default NULL," +
55			" `HIS` int(3) unsigned default NULL," +
56			" `ILE` int(3) unsigned default NULL," +
57			" `LEU` int(3) unsigned default NULL," +
58			" `LYS` int(3) unsigned default NULL," +
59			" `MET` int(3) unsigned default NULL," +
60			" `PHE` int(3) unsigned default NULL," +
61			" `PRO` int(3) unsigned default NULL," +
62			" `SER` int(3) unsigned default NULL," +
63			" `THR` int(3) unsigned default NULL," +
64			" `TRP` int(3) unsigned default NULL," +
65			" `TYR` int(3) unsigned default NULL," +
66			" `VAL` int(3) unsigned default NULL," +
67			" CONSTRAINT PRIMARY KEY (graph_id, cid, num));";
68
69			// This array is used to convert an amino acid number to its three letter code.
70			// The numbering is from 1 to 20 to ensure compatibility with function aa_three2num().
71			static final String[] aa_num2three = {"???", "ALA", "ARG", "ASN", "ASP", "CYS",
72			"GLN", "GLU", "GLY", "HIS", "ILE",
73			"LEU", "LYS", "MET", "PHE", "PRO",
74			"SER", "THR", "TRP", "TYR", "VAL"};
75
76			/* ------------------------------ class variables -------------------------------------*/
77
78			static Connection conn; // global database connection
79
80			/* -------------------------------- functions -----------------------------------------*/
81
82			// return the number of an amino acid by its three letter code
83			public static int aa_three2num(String threeLetterCode) {
84			if(threeLetterCode.equalsIgnoreCase("ALA")) return 1;
85			if(threeLetterCode.equalsIgnoreCase("ARG")) return 2;
86			if(threeLetterCode.equalsIgnoreCase("ASN")) return 3;
87			if(threeLetterCode.equalsIgnoreCase("ASP")) return 4;
88			if(threeLetterCode.equalsIgnoreCase("CYS")) return 5;
89			if(threeLetterCode.equalsIgnoreCase("GLN")) return 6;
90			if(threeLetterCode.equalsIgnoreCase("GLU")) return 7;
91			if(threeLetterCode.equalsIgnoreCase("GLY")) return 8;
92			if(threeLetterCode.equalsIgnoreCase("HIS")) return 9;
93			if(threeLetterCode.equalsIgnoreCase("ILE")) return 10;
94			if(threeLetterCode.equalsIgnoreCase("LEU")) return 11;
95			if(threeLetterCode.equalsIgnoreCase("LYS")) return 12;
96			if(threeLetterCode.equalsIgnoreCase("MET")) return 13;
97			if(threeLetterCode.equalsIgnoreCase("PHE")) return 14;
98			if(threeLetterCode.equalsIgnoreCase("PRO")) return 15;
99			if(threeLetterCode.equalsIgnoreCase("SER")) return 16;
100			if(threeLetterCode.equalsIgnoreCase("THR")) return 17;
101			if(threeLetterCode.equalsIgnoreCase("TRP")) return 18;
102			if(threeLetterCode.equalsIgnoreCase("TYR")) return 19;
103			if(threeLetterCode.equalsIgnoreCase("VAL")) return 20;
104			return -1;
105			}
106
107			public static String nbhGetHashValue(int[] nbh) {
108
109			char currentChar;
110			StringBuffer resultBuffer = new StringBuffer("????????????????????");
111
112			// nbh[i] is defined for i=1..20
113			for(int i = 1; i <= 20; i++) {
114			currentChar = '?';
115			if(nbh[i] < 0)
116			System.err.println("Error in function nbhGetHashValue: " +
117			"array entry nbh[" + i + "] = " + nbh[i] +" is negative.");
118			//else if(nbh[i] <= 9) currentChar = '0' + nbh[i];
119			//else if(nbh[i] <= 9 + 26) currentChar = 'A' + nbh[i];
120			else if(nbh[i] > 26 + 9)
121			System.err.println("Error in function nbhGetHashValue: " +
122			"array entry nbh[" + i + "] = " + nbh[i] + " is too big.");
123			else currentChar = Character.forDigit(nbh[i], 26 + 10);
124			resultBuffer.setCharAt(i-1, currentChar);
125			}
126			return resultBuffer.toString();
127			}
128
129
130			// open connection to database
131			public static void openDBConnection() {
132
133			try { // try to load driver
134			Class.forName(dbDriver).newInstance();
135			try {
136			conn = DriverManager.getConnection(dbServer, dbUserName, dbPassword);
137			} catch (SQLException E) {
138			System.err.println("Error: Unable to open database connection.");
139			System.err.println("SQLException: " + E.getMessage());
140			System.err.println("SQLState: " + E.getSQLState());
141			System.err.println("VendorError: " + E.getErrorCode());
142			} // end try/catch connection
143			} // end try load Driver
144			catch (Exception E) {
145			System.err.println("Error: Unable to load database driver.");
146			E.printStackTrace();
147			} // end catch
148
149			return;
150			}
151
152			// close database connection
153			public static void closeDBConnection() {
154
155			try {
156			conn.close();
157			} catch (SQLException E) {
158			System.out.println("Error: Unable to close connection.");
159			System.out.println("SQLException: " + E.getMessage());
160			System.out.println("SQLState: " + E.getSQLState());
161			System.out.println("VendorError: " + E.getErrorCode());
162			System.exit(1);
163			} // end try/catch connection
164
165			return;
166			}
167
168			// create new neighbourhood table if not exists
169			public static void createNeighbourhoodTableIfNotExists() {
170			Statement stmt;
171			String query;
172			try {
173			query = "CREATE TABLE IF NOT EXISTS " + nbhTable + " " + nbhTableDefinition + ";";
174			stmt = conn.createStatement();
175			stmt.execute(query);
176			} catch (SQLException E) {
177			System.err.println("Error: Unable to create neighbourhood table.");
178			System.err.println("SQLException: " + E.getMessage());
179			System.err.println("SQLState: " + E.getSQLState());
180			System.err.println("VendorError: " + E.getErrorCode());
181			} // end catch
182
183			return;
184			}
185
186			// insert neighbourhood entries for each graph with from <= graph_id <= to
187			public static void insertNeighbourhoods(int fromIdx, int toIdx) {
188			Statement graphsStmt,
189			nodesStmt,
190			edgesStmt;
191			ResultSet graphsRs,
192			nodesRs,
193			edgesRs;
194			String query,
195			chainId = "?",
196			resType,
197			nbhResType,
198			hash;
199			int graphId = 0,
200			entryId,
201			resNum = 0,
202			numGraphs = 0,
203			numNodes = 0,
204			// existingNodes,
205			resTypeNum,
206			nbhResCount,
207			nbhResTypeNum,
208			nbhSize;
209			int[] nbh;
210
211			try {
212			// get number of graphs
213			graphsStmt = conn.createStatement();
214			query = "SELECT COUNT(*) FROM " + listTable +
215			" WHERE graph_id >= " + fromIdx + " AND graph_id <= " + toIdx + ";";
216			graphsRs = graphsStmt.executeQuery(query);
217			if(graphsRs.next()) {
218			numGraphs = graphsRs.getInt(1);
219			}
220			graphsRs.close();
221			graphsStmt.close();
222			System.out.println("Processing " + numGraphs + (numGraphs==1?" graph...":" graphs..."));
223
224			// iterate over all graphs
225			graphsStmt = conn.createStatement();
226			query = "SELECT graph_id, entry_id FROM " + listTable +
227			" WHERE graph_id >= " + fromIdx + " AND graph_id <= " + toIdx + ";";
228			graphsRs = graphsStmt.executeQuery(query);
229			numGraphs = 0;
230			while(graphsRs.next()) {
231			graphId = graphsRs.getInt(1);
232			entryId = graphsRs.getInt(2);
233
234			// check whether neighbourhoods for this graph already exist
235			// nodesStmt = conn.createStatement();
236			// query = "SELECT COUNT(*) FROM " + nbhTable + " WHERE graph_id = " + graphId + ";";
237			// nodesRs = nodesStmt.executeQuery(query);
238			// if(nodesRs.next()) {
239			// existingNodes = nodesRs.getInt(1);
240			// if(existingNodes > 0) {
241			// System.out.println("Warning: Neighbourhoods for the graph with graph_id = "
242			// + graphId + " already exist.");
243			// }
244			// }
245			// nodesRs.close();
246			// nodesStmt.close();
247
248			// extract all nodes and edges for this graph into temporary tables
249			nodesStmt = conn.createStatement();
250			nodesStmt.addBatch("DROP TEMPORARY TABLE IF EXISTS " + tempNodes + ";");
251			nodesStmt.addBatch("DROP TEMPORARY TABLE IF EXISTS " + tempEdges + ";");
252			nodesStmt.addBatch("CREATE TEMPORARY TABLE " + tempNodes +
253			" SELECT * FROM " + nodeTable + " WHERE graph_id = " + graphId + ";");
254			nodesStmt.addBatch("CREATE TEMPORARY TABLE " + tempEdges +
255			" SELECT * FROM " + edgeTable + " WHERE graph_id = " + graphId +
256			" AND " + edgeType + " > 0;"); // take only relevant edges
257			// if necessary create indices to speed up queries
258			nodesStmt.executeBatch();
259			nodesStmt.close();
260
261			// iterate over all nodes for this graph
262			nodesStmt = conn.createStatement();
263			query = "SELECT cid, num, res FROM "+ tempNodes + ";"; // graph_id matches automatically
264			nodesRs = nodesStmt.executeQuery(query);
265			while(nodesRs.next()) {
266			chainId = nodesRs.getString(1);
267			resNum = nodesRs.getInt(2);
268			resType = nodesRs.getString(3);
269			resTypeNum = aa_three2num(resType);
270			if(resTypeNum < 1 \|\| resTypeNum > 20) {
271			System.err.println("Warning: Non standard amino acid " + resType +
272			" at position " + resNum + " in chain " + chainId +
273			" of graph " + graphId + ".");
274			}
275
276			// get residue counts from edges table
277			edgesStmt = conn.createStatement();
278			query = "SELECT j_res AS res, COUNT(*) AS count FROM "+ tempEdges +
279			" WHERE i_cid = '" + chainId + "' AND i_num = " + resNum +
280			" GROUP BY j_res;";
281			//System.out.println(query);
282			edgesRs = edgesStmt.executeQuery(query);
283
284			nbh = new int[21]; // set all residue counts to zero
285			nbhSize = 0;
286			while(edgesRs.next()) {
287			nbhResType = edgesRs.getString(1);
288			nbhResCount = edgesRs.getInt(2);
289			nbhResTypeNum = aa_three2num(nbhResType);
290			if(nbhResTypeNum >= 1 && nbhResTypeNum <= 20) {
291			nbh[nbhResTypeNum] = nbhResCount;
292			nbhSize += nbhResCount;
293			}
294			}
295			edgesRs.close();
296			edgesStmt.close();
297
298			// update nbh table
299			hash = nbhGetHashValue(nbh);
300			query = "INSERT INTO " + nbhTable + " (graph_id, cid, num, res, entry_id, size, hash, "
301			+ "ALA, ARG, ASN, ASP, CYS, GLN, GLU, GLY, HIS, ILE, "
302			+ "LEU, LYS, MET, PHE, PRO, SER, THR, TRP, TYR, VAL) " +
303			"VALUES (" + graphId + ",'" + chainId + "'," + resNum + ",'"
304			+ resType + "'," + entryId + "," + nbhSize + ",'" + hash + "',"
305			+ nbh[1] + "," + nbh[2] + "," + nbh[3] + "," + nbh[4] + "," + nbh[5] + ","
306			+ nbh[6] + "," + nbh[7] + "," + nbh[8] + "," + nbh[9] + "," + nbh[10] + ","
307			+ nbh[11] + "," + nbh[12] + "," + nbh[13] + "," + nbh[14] + "," + nbh[15] + ","
308			+ nbh[16] + "," + nbh[17] + "," + nbh[18] + "," + nbh[19] + "," + nbh[20] + ");";
309			edgesStmt = conn.createStatement();
310			if(edgesStmt.executeUpdate(query) != 1) {
311			System.err.println("Error: Insert into table " + nbhTable + " failed.");
312			System.exit(1);
313			}
314			edgesStmt.close();
315			numNodes++;
316			} // end of while over nodes
317			nodesRs.close();
318			nodesStmt.close();
319			numGraphs++;
320			if(numGraphs % 1000 == 0) {
321			System.out.print(numGraphs + " ");
322			System.out.flush();
323			}
324			System.gc(); // clean up
325			} // end while over all graphs
326			graphsRs.close();
327			graphsStmt.close();
328			} catch (SQLException E) {
329			System.err.println("Error in function insertNeighbourhoods:");
330			System.err.println("graphId = "+ graphId +", chainId = "+ chainId +", resNum = "+ resNum);
331			System.err.println("SQLException: " + E.getMessage());
332			System.err.println("SQLState: " + E.getSQLState());
333			System.err.println("VendorError: " + E.getErrorCode());
334			} // end catch
335
336			System.out.println();
337			System.out.println("Extracted "+ numNodes + " neighbourhood" + (numNodes==1?"":"s")
338			+ " from " + numGraphs + " graph" + (numGraphs==1?"":"s") + ".");
339			return;
340			}
341
342			public static void createIndicesOnNeighbourhoodTable() {
343			// index on graph_id
344			// index on graph_id, cid, num
345			// index on res
346			// index on size
347			// index on hash
348			}
349
350			/* ----------------------------------- main -------------------------------------------*/
351
352			public static void main(String[] args) {
353
354			int from = 0,
355			to = 0;
356
357			// read command line parameters
358			if ( args.length != 2) {
359			System.err.println("Usage: makeNbhTbl [start_index] [end_index]");
360			System.exit(1);
361			} else {
362
363			// read parameter 1
364
365			try {
366			from = Integer.parseInt(args[0]);
367			if(from <= 0) {
368			System.err.println("Parameter 1 has to be positive");
369			System.exit(1);
370			}
371			}
372			catch(NumberFormatException e) {
373			System.err.println("Parameter 1 has to be an integer");
374			System.exit(1);
375			}
376
377			// read parameter 2
378
379			try {
380			to = Integer.parseInt(args[1]);
381			if(to <= 0) {
382			System.err.println("Parameter 2 has to be positive");
383			System.exit(1);
384			} else
385			if(from > to) {
386			System.err.println("Start index has to be smaller or equal than end index");
387			System.exit(1);
388			}
389			}
390			catch(NumberFormatException e) {
391			System.err.println("Parameter 2 has to be an integer");
392			System.exit(1);
393			}
394
395			}
396
397			// execute
398			openDBConnection();
399			createNeighbourhoodTableIfNotExists();
400			insertNeighbourhoods(from, to);
401			createIndicesOnNeighbourhoodTable();
402			closeDBConnection();
403
404			} // end of main
405
406			} // end of class makeNbhTbl