annotate commons/core/sql/DbMySql.py @ 6:769e306b7933

Change the repository level.
author yufei-luo
date Fri, 18 Jan 2013 04:54:14 -0500
parents
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
6
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
1 # Copyright INRA (Institut National de la Recherche Agronomique)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
2 # http://www.inra.fr
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
3 # http://urgi.versailles.inra.fr
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
4 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
5 # This software is governed by the CeCILL license under French law and
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
6 # abiding by the rules of distribution of free software. You can use,
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
7 # modify and/ or redistribute the software under the terms of the CeCILL
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
8 # license as circulated by CEA, CNRS and INRIA at the following URL
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
9 # "http://www.cecill.info".
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
10 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
11 # As a counterpart to the access to the source code and rights to copy,
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
12 # modify and redistribute granted by the license, users are provided only
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
13 # with a limited warranty and the software's author, the holder of the
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
14 # economic rights, and the successive licensors have only limited
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
15 # liability.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
16 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
17 # In this respect, the user's attention is drawn to the risks associated
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
18 # with loading, using, modifying and/or developing or reproducing the
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
19 # software by the user in light of its specific status of free software,
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
20 # that may mean that it is complicated to manipulate, and that also
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
21 # therefore means that it is reserved for developers and experienced
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
22 # professionals having in-depth computer knowledge. Users are therefore
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
23 # encouraged to load and test the software's suitability as regards their
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
24 # requirements in conditions enabling the security of their systems and/or
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
25 # data to be ensured and, more generally, to use and operate it in the
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
26 # same conditions as regards security.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
27 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
28 # The fact that you are presently reading this means that you have had
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
29 # knowledge of the CeCILL license and that you accept its terms.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
30
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
31 # Exception hierarchy:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
32 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
33 # StandardError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
34 # |__Warning
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
35 # |__Error
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
36 # |__InterfaceError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
37 # |__DatabaseError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
38 # |__DataError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
39 # |__OperationalError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
40 # |__IntegrityError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
41 # |__InternalError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
42 # |__ProgrammingError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
43 # |__NotSupportedError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
44
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
45 import os
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
46 import sys
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
47 import time
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
48 import ConfigParser
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
49 import MySQLdb
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
50 from MySQLdb import InterfaceError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
51 from MySQLdb import OperationalError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
52 from MySQLdb import InternalError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
53 from MySQLdb import DatabaseError
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
54 from commons.core.seq.Bioseq import Bioseq
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
55 from commons.core.LoggerFactory import LoggerFactory
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
56 from commons.core.checker.RepetException import RepetException
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
57 from commons.core.sql.TablePathAdaptator import TablePathAdaptator
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
58 from commons.core.sql.TableSetAdaptator import TableSetAdaptator
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
59
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
60 LOG_DEPTH = "repet.commons"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
61
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
62 TABLE_SCHEMA_DESCRIPTOR = {"map": [("name", "varchar(255)"), ("chr", "varchar(255)"), ("start", "int"), ("end", "int")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
63 "set": [("path", "int unsigned"), ("name", "varchar(255)"), ("chr", "varchar(255)"), ("start", "int"), ("end", "int")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
64 "match": [("query_name", "varchar(255)"), ("query_start", "int"), ("query_end", "int"), ("query_length", "int unsigned"), ("query_length_perc", "float"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
65 ("match_length_perc", "float"), ("subject_name", "varchar(255)"), ("subject_start", "int unsigned"), ("subject_end", "int unsigned"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
66 ("subject_length", "int unsigned"), ("subject_length_perc", "float"), ("E_value", "double"), ("score", "int unsigned"), ("identity", "float"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
67 ("path", "int unsigned")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
68 "path": [("path", "int unsigned"), ("query_name", "varchar(255)"), ("query_start", "int"), ("query_end", "int"), ("subject_name", "varchar(255)"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
69 ("subject_start", "int unsigned"), ("subject_end", "int unsigned"), ("E_value", "double"), ("score", "int unsigned"), ("identity", "float")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
70 "align": [("query_name", "varchar(255)"), ("query_start", "int"), ("query_end", "int"), ("subject_name", "varchar(255)"), ("subject_start", "int unsigned"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
71 ("subject_end", "int unsigned"), ("E_value", "double"), ("score", "int unsigned"), ("identity", "float")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
72 "seq": [("accession", "varchar(255)"), ("sequence", "longtext"), ("description", "varchar(255)"), ("length", "int unsigned")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
73 "length": [("accession", "varchar(255)"), ("length", "int unsigned")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
74 "jobs": [("jobid", "int unsigned"), ("jobname", "varchar(255)"), ("groupid", "varchar(255)"), ("launcher", "varchar(1024)"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
75 ("queue", "varchar(255)"), ("resources", "varchar(255)"), ("status", "varchar(255)"), ("time", "datetime"), ("node", "varchar(255)")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
76 "classif": [("seq_name", "varchar(255)"), ("length", "int unsigned"), ("strand", "char"), ("status", "varchar(255)"), ("class_classif", "varchar(255)"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
77 ("order_classif", "varchar(255)"), ("completeness", "varchar(255)"), ("evidence", "text")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
78 "pathstat": [("family", "varchar(255)"), ("maxLength", "int"), ("meanLength", "int"), ("covg", "int"), ("frags", "int"), ("fullLgthFrags", "int"), ("copies", "int"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
79 ("fullLgthCopies", "int"), ("meanId", "varchar(255)"), ("sdId", "varchar(255)"), ("minId", "varchar(255)"), ("q25Id", "varchar(255)"), ("medId", "varchar(255)"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
80 ("q75Id", "varchar(255)"), ("maxId", "varchar(255)"), ("meanLgth", "varchar(255)"), ("sdLgth", "varchar(255)"), ("minLgth", "varchar(255)"), ("q25Lgth", "varchar(255)"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
81 ("medLgth", "varchar(255)"), ("q75Lgth", "varchar(255)"), ("maxLgth", "varchar(255)"), ("meanLgthPerc", "varchar(255)"), ("sdLgthPerc", "varchar(255)"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
82 ("minLgthPerc", "varchar(255)"), ("q25LgthPerc", "varchar(255)"), ("medLgthPerc", "varchar(255)"), ("q75LgthPerc", "varchar(255)"), ("maxLgthPerc", "varchar(255)")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
83 "info_tables":[("name", "varchar(255)"), ("file", "varchar(255)")]
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
84 }
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
85
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
86 TABLE_INDEX_DESCRIPTOR = {"map": [("iname", "name"), ("ichr", "chr"), ("istart", "start"), ("iend", "end"), ("icoord", "start, end")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
87 "set": [("id", "path"), ("iname", "name"), ("ichr", "chr"), ("istart", "start"), ("iend", "end"), ("icoord", "start, end")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
88 "match": [("id", "path"), ("qname", "query_name"), ("qstart", "query_start"), ("qend", "query_end"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
89 ("sname", "subject_name"), ("sstart", "subject_start"), ("send", "subject_end"), ("qcoord", "query_start, query_end")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
90 "path": [("id", "path"), ("qname", "query_name"), ("qstart", "query_start"), ("qend", "query_end"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
91 ("sname", "subject_name"), ("sstart", "subject_start"), ("send", "subject_end"), ("qcoord", "query_start, query_end")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
92 "align": [("qname", "query_name"), ("qstart", "query_start"), ("qend", "query_end"),
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
93 ("sname", "subject_name"), ("sstart", "subject_start"), ("send", "subject_end"), ("qcoord", "query_start, query_end")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
94 "seq": [("iacc", "accession"), ("idescr", "description")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
95 "length": [("iacc", "accession"), ("ilength", "length")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
96 "jobs": [("ijobid", "jobid"), ("ijobname", "jobname"), ("igroupid", "groupid"), ("istatus", "status")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
97 "classif": [("iseq_name", "seq_name"), ("istatus", "status"), ("iclass", "class_classif"), ("iorder", "order_classif"), ("icomp", "completeness")],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
98 "pathstat": [],
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
99 "info_tables": []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
100 }
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
101
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
102 TABLE_TYPE_SYNONYMS = {"tab": "match",
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
103 "fasta": "seq",
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
104 "fa": "seq",
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
105 "fsa": "seq"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
106 }
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
107
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
108 ## Handle connections to MySQL tables formatted for REPET
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
109 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
110 class DbMySql(object):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
111
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
112 ## Constructor
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
113 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
114 # @param user string db user name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
115 # @param host string db host name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
116 # @param passwd string db user password
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
117 # @param dbname string database name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
118 # @param port integer database port
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
119 # @param cfgFileName string configuration file name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
120 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
121 # @note when a parameter is left blank, the constructor is able
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
122 # to set attribute values from environment variables: REPET_HOST,
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
123 # REPET_USER, REPET_PW, REPET_DB, REPET_PORT
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
124 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
125 def __init__(self, user = "", host = "", passwd = "", dbname = "", port = "", cfgFileName = "", verbosity = 1):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
126 self._log = LoggerFactory.createLogger("%s.%s" % (LOG_DEPTH, self.__class__.__name__), verbosity)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
127 if cfgFileName != "":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
128 self.setAttributesFromConfigFile(cfgFileName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
129
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
130 elif host != "" and user != "" and passwd != "" and dbname != "":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
131 self.host = host
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
132 self.user = user
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
133 self.passwd = passwd
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
134 self.dbname = dbname
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
135
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
136 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
137 for envVar in ["REPET_HOST","REPET_USER","REPET_PW","REPET_DB"]:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
138 if os.environ.get( envVar ) == None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
139 msg = "ERROR: can't find environment variable '%s'" % envVar
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
140 self._log.error(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
141 raise RepetException(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
142 self.host = os.environ.get("REPET_HOST")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
143 self.user = os.environ.get("REPET_USER")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
144 self.passwd = os.environ.get("REPET_PW")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
145 self.dbname = os.environ.get("REPET_DB")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
146
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
147 if port != "" and cfgFileName == "":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
148 self.port = int(port)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
149 elif os.environ.get("REPET_PORT") != None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
150 self.port = int(os.environ.get("REPET_PORT"))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
151 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
152 self.port = 3306
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
153
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
154 maxNbTry = 10
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
155 for i in xrange(1,maxNbTry+1):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
156 if not self.open():
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
157 time.sleep(2)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
158 if i == maxNbTry:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
159 msg = "ERROR: failed to connect to the MySQL database"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
160 self._log.error(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
161 raise DatabaseError(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
162 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
163 break
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
164
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
165 self.cursor = self.db.cursor()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
166 self.execute("""use %s""" %(self.dbname))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
167
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
168
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
169 ## Set the attributes from the configuration file
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
170 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
171 # @param configFileName string configuration file name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
172 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
173 def setAttributesFromConfigFile(self, configFileName):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
174 config = ConfigParser.ConfigParser()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
175 config.readfp( open(configFileName) )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
176 self.host = config.get("repet_env","repet_host")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
177 self.user = config.get("repet_env","repet_user")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
178 self.passwd = config.get("repet_env","repet_pw")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
179 self.dbname = config.get("repet_env","repet_db")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
180 self.port = int( config.get("repet_env","repet_port") )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
181
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
182
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
183 ## Connect to the MySQL database
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
184 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
185 def open(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
186 try:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
187 if int(MySQLdb.get_client_info().split(".")[0]) >= 5:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
188 self.db = MySQLdb.connect( user = self.user, host = self.host,\
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
189 passwd = self.passwd, db = self.dbname, \
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
190 port = self.port, \
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
191 local_infile = 1 )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
192 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
193 self.db = MySQLdb.connect( user = self.user, host = self.host,\
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
194 passwd = self.passwd, db = self.dbname, \
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
195 port = self.port )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
196 except MySQLdb.Error, e:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
197 msg = "ERROR %d: %s" % (e.args[0], e.args[1])
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
198 self._log.error(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
199 return False
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
200
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
201 return True
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
202
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
203
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
204 ## Execute a SQL query
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
205 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
206 # @param qry string SQL query to execute
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
207 # @param params parameters of SQL query
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
208 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
209 def execute(self, qry, params = None, nbTry = 3, sleep = 5):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
210 if nbTry:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
211 self._log.debug("################START SQL DEBUG MODE################")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
212 self._log.debug("Current directory: %s" % os.getcwd())
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
213 self._log.debug("Host: %s" % self.host)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
214 self._log.debug("User: %s" % self.user)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
215 self._log.debug("Database: %s" % self.dbname)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
216 self._log.debug("SQL command: %s" % qry)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
217 self._log.debug("################STOP SQL DEBUG MODE################\n")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
218
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
219 try:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
220 if params == None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
221 self.cursor.execute(qry)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
222 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
223 self.cursor.execute(qry, params)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
224 except (InterfaceError, OperationalError, InternalError) as iError:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
225 self._log.error("FAILED to execute query '%s': %s. %s retries left." % (qry, iError.args[1], nbTry - 1))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
226 self._log.debug("WAIT %is to execute '%s'" % (sleep, qry))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
227 time.sleep(sleep)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
228 try:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
229 self.close()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
230 except:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
231 pass
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
232 self.open()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
233 self.cursor = self.db.cursor()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
234 self.execute(qry, params, nbTry - 1, sleep)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
235 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
236 msg = "ERROR: can't execute '%s' after several tries" % qry
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
237 self._log.error(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
238 raise DatabaseError(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
239
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
240 ## Close the connection
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
241 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
242 def close( self ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
243 self.db.close()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
244
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
245
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
246 ## Retrieve the results of a SQL query
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
247 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
248 def fetchall(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
249 return self.cursor.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
250
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
251
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
252 ## Test if a table exists
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
253 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
254 # @param table string table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
255 # @return boolean True if the table exists, False otherwise
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
256 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
257 def doesTableExist( self, table ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
258 self.execute( """SHOW TABLES""" )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
259 results = self.cursor.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
260 if (table,) in results:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
261 return True
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
262 return False
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
263
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
264
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
265 ## Remove a table if it exists
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
266 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
267 # @param table string table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
268 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
269 def dropTable(self, table):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
270 if self.doesTableExist( table ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
271 sqlCmd = "DROP TABLE %s" % table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
272 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
273 sqlCmd = 'DELETE FROM info_tables WHERE name = "%s"' % table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
274 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
275
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
276
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
277 ## Rename a table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
278 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
279 # @param table string existing table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
280 # @param newName string new table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
281 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
282 def renameTable( self, table, newName ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
283 self.dropTable( newName )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
284 self.execute( 'RENAME TABLE %s TO %s ;' % (table, newName) )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
285 self.execute( 'UPDATE info_tables SET name="%s" WHERE name="%s";' % (newName, table) )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
286
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
287
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
288 ## Duplicate a table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
289 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
290 # @param tableName string source table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
291 # @param newTableName string new table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
292 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
293 def copyTable(self, sourcetableName, newTableName):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
294 self.dropTable( newTableName )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
295 sqlCmd = "CREATE TABLE %s LIKE %s;" % (newTableName, sourcetableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
296 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
297 sqlCmd = "INSERT INTO %s SELECT * FROM %s;" % (newTableName, sourcetableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
298 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
299 self._log.info("copying table data,", sourcetableName, "in", newTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
300 self.updateInfoTable(newTableName, "")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
301
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
302
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
303 ## Give the rows number of the table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
304 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
305 # @param tableName string table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
306 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
307 def getSize( self, tableName ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
308 qry = "SELECT count(*) FROM %s;" % (tableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
309 self.execute(qry)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
310 res = self.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
311 return int( res[0][0] )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
312
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
313
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
314 def getTableType(self, tableName):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
315 qry = "SHOW COLUMNS FROM %s;" % (tableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
316 self.execute(qry)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
317 res = self.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
318
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
319 fieldNames = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
320 for row in res:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
321 fieldNames.append(row[0])
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
322
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
323 for tableType, fieldInfos in TABLE_SCHEMA_DESCRIPTOR.items():
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
324 refFieldsNames = [name for name,type in fieldInfos]
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
325 if refFieldsNames == fieldNames:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
326 return tableType
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
327
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
328 return None
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
329
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
330
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
331 ## Test if table is empty
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
332 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
333 # @param tableName string table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
334 # @return boolean True if the table is empty, False otherwise
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
335 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
336 def isEmpty(self, tableName):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
337 return self.getSize(tableName) == 0
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
338
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
339
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
340 ## Record a new table in the 'info_table' table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
341 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
342 # @param tableName string table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
343 # @param info string information on the table origin
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
344 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
345 def updateInfoTable( self, tableName, info ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
346 if not self.doesTableExist( "info_tables" ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
347 sqlCmd = "CREATE TABLE info_tables ( name varchar(255), file varchar(255) )"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
348 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
349 sqlCmd = 'INSERT INTO info_tables VALUES ("%s","%s")' % (tableName, info)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
350 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
351
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
352
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
353 ## Get a list with the fields
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
354 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
355 def getFieldList( self, table ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
356 lFields = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
357 sqlCmd = "DESCRIBE %s" % ( table )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
358 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
359 lResults = self.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
360 for res in lResults:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
361 lFields.append( res[0] )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
362 return lFields
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
363
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
364
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
365 ## Check that the input file has as many fields than it is supposed to according to its format
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
366 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
367 # @note fields should be separated by tab
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
368 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
369 def checkDataFormatting( self, dataType, fileName ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
370 dataType = dataType.lower()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
371 if dataType in ["fa", "fasta", "seq", "classif", "length", "jobs", "pathstat"]:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
372 return
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
373 dDataType2NbFields = { "map": 4, "set": 5, "align": 9, "path": 10, "match": 15, "tab": 15 }
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
374 fileHandler = open( fileName, "r" )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
375 line = fileHandler.readline()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
376 if line != "":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
377 tokens = line.split("\t")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
378 if len(tokens) < dDataType2NbFields[ dataType ]:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
379 msg = "ERROR: '%s' file has less than %i fields" % ( dataType, dDataType2NbFields[ dataType ] )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
380 self._log.error(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
381 raise RepetException(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
382 if len(tokens) > dDataType2NbFields[ dataType ]:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
383 msg = "ERROR: '%s' file has more than %i fields" % ( dataType, dDataType2NbFields[ dataType ] )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
384 self._log.error(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
385 raise RepetException(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
386 fileHandler.close()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
387
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
388
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
389 def createIndex(self, tableName="", tableType=""):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
390 sqlCmd = "SHOW INDEX FROM %s;"% (tableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
391 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
392 res = self.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
393 lIndex = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
394 for i in res:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
395 lIndex.append(i[2])
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
396 self._log.warning("existing indexes:", lIndex)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
397
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
398 for indexName, fieldNames in TABLE_INDEX_DESCRIPTOR.get(tableType):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
399 if not indexName in lIndex:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
400 sqlCmd = "CREATE INDEX %s ON %s ( %s );" % (indexName, tableName, fieldNames)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
401 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
402
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
403
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
404 ## Create a MySQL table of specified data type and load data
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
405 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
406 # @param tableName string name of the table to be created
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
407 # @param fileName string name of the file containing the data to be loaded in the table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
408 # @param dataType string type of the data (map, set, align, path, match, seq, length, jobs)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
409 # @param overwrite boolean (default = False)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
410 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
411 def createTable(self, tableName, dataType, fileName = "", overwrite = False):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
412 self._log.info("creating table '%s' from file '%s' of type '%s'..." % (tableName, fileName, dataType))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
413
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
414 if fileName != "":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
415 self.checkDataFormatting(dataType, fileName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
416
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
417 if overwrite:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
418 self.dropTable(tableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
419
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
420 tableType = dataType.lower()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
421 if TABLE_SCHEMA_DESCRIPTOR.get(tableType,None) is None and TABLE_TYPE_SYNONYMS.get(tableType,None) is None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
422 msg = "ERROR: unknown type %s" % dataType
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
423 self._log.error(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
424 raise RepetException(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
425
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
426 tableType = TABLE_TYPE_SYNONYMS.get(tableType,tableType)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
427
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
428 fields = [" ".join(fieldDescription) for fieldDescription in TABLE_SCHEMA_DESCRIPTOR.get(tableType)]
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
429 sqlCmd = "CREATE TABLE %s (%s)" % (tableName, ",".join(fields))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
430 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
431 self.createIndex(tableName, tableType)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
432
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
433 tmpFileName = ""
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
434 if fileName:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
435 if tableType == "seq":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
436 tmpFileName = "%s.seq" % os.path.basename(fileName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
437 self._convertFastaToSeq(fileName, tmpFileName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
438 fileName = tmpFileName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
439 elif tableType == "length":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
440 tmpFileName = "%s.length" % os.path.basename(fileName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
441 self._convertFastaToLength(fileName, tmpFileName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
442 fileName = tmpFileName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
443
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
444 hasHeaderLine = tableType == "match" or tableType == "pathstat"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
445 self.loadDataFromFile(tableName, fileName, hasHeaderLine)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
446 if tmpFileName:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
447 os.remove(tmpFileName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
448
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
449 if tableType == "path":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
450 self.changePathQueryCoordinatesToDirectStrand( tableName )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
451
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
452 self.updateInfoTable(tableName, fileName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
453 self._log.info("creating table '%s' done!" % tableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
454
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
455
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
456 ## Create a bin table for fast access
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
457 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
458 # @param pathTableName string path table name (input table)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
459 # @param idxTableName string bin path table name (output table)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
460 # @param overwrite boolean default = False
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
461 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
462 def createBinPathTable(self, pathTableName, overwrite = False):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
463 idxTableName = "%s_idx" % pathTableName # is an attribute in TableBinPathAdaptator
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
464 if not self.doesTableExist(pathTableName):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
465 msg = "ERROR: '%s' doesn't exist => '%s' can't be created" % (pathTableName, idxTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
466 self._log.error(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
467 raise RepetException(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
468 self._log.info("creating %s for fast access" % idxTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
469 if overwrite:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
470 self.dropTable(idxTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
471
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
472 sqlCmd = "CREATE TABLE %s ( path int unsigned, idx int unsigned, contig varchar(255), min int, max int, strand int unsigned)" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
473 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
474
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
475 sqlCmd = "CREATE INDEX id ON %s ( path );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
476 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
477 sqlCmd = "CREATE INDEX ibin ON %s ( idx );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
478 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
479 sqlCmd = "CREATE INDEX icontig ON %s ( contig );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
480 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
481 sqlCmd = "CREATE INDEX imin ON %s ( min );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
482 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
483 sqlCmd = "CREATE INDEX imax ON %s ( max );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
484 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
485 sqlCmd = "CREATE INDEX istrand ON %s ( strand );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
486 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
487
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
488 tmpTableName = "%s_tmp" % pathTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
489 self._createPathTableAndGroupByIdAndOrderByStrand(pathTableName, tmpTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
490 iTPA = TablePathAdaptator(self, tmpTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
491 if not self.isEmpty(tmpTableName):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
492 tmpFileName = "%s.tmp%s" % (pathTableName, str(os.getpid()))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
493 with open(tmpFileName, "w") as f:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
494 lQueryNames = iTPA.getQueryList()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
495 for queryName in lQueryNames:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
496 lPaths = iTPA.getPathListFromQuery(queryName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
497 for i in lPaths:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
498 idx = i.range_query.findIdx()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
499 max = i.range_query.getMax()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
500 min = i.range_query.getMin()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
501 strand = i.range_query.isOnDirectStrand()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
502 f.write("%d\t%d\t%s\t%d\t%d\t%d\n"%(i.id, idx, i.range_query.seqname, min, max, strand))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
503 sqlCmd="LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS ESCAPED BY '' " % (tmpFileName, idxTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
504 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
505 self.updateInfoTable(idxTableName, "%s bin indexes" % pathTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
506 os.remove(tmpFileName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
507 self.dropTable(tmpTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
508
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
509
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
510 ## This table summarize the Path list information according to the identifier numbers. The min and max value is taken
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
511 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
512 def _createPathTableAndGroupByIdAndOrderByStrand(self, pathTableName, outTableName):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
513 self.dropTable(outTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
514
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
515 sqlcmd="CREATE TABLE %s SELECT path, query_name, min(query_start) AS query_start, max(query_end) AS query_end, subject_name, min(subject_start) AS subject_start, max(subject_end) AS subject_end, min(e_value) AS e_value, sum(score) AS score, avg(identity) AS identity FROM %s WHERE query_start<query_end and subject_start<subject_end group by path;" % (outTableName, pathTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
516 self.execute(sqlcmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
517
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
518 sqlcmd="INSERT INTO %s SELECT path, query_name, min(query_start) AS query_start, max(query_end) AS query_end, subject_name, max(subject_start) AS subject_start, min(subject_end) AS subject_end, min(e_value) AS e_value, sum(score) AS score, avg(identity) AS identity FROM %s WHERE query_start<query_end and subject_start>subject_end group by path;" % (outTableName, pathTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
519 self.execute(sqlcmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
520
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
521 sqlcmd="INSERT INTO %s SELECT path, query_name, max(query_start) AS query_start, min(query_end) AS query_end, subject_name, min(subject_start) AS subject_start, max(subject_end) AS subject_end, min(e_value) AS e_value, sum(score) AS score, avg(identity) AS identity FROM %s WHERE query_start>query_end and subject_start<subject_end group by path;" % (outTableName, pathTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
522 self.execute(sqlcmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
523
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
524 sqlcmd="INSERT INTO %s SELECT path, query_name, max(query_start) AS query_start, min(query_end) AS query_end, subject_name, max(subject_start) AS subject_start, min(subject_end) AS subject_end, min(e_value) AS e_value, sum(score) AS score, avg(identity) AS identity FROM %s WHERE query_start>query_end and subject_start>subject_end group by path;" % (outTableName, pathTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
525 self.execute(sqlcmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
526
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
527 self.createIndex(outTableName, "path")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
528
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
529
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
530 ## Create a bin table for fast access
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
531 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
532 # @param setTableName string set table name (input table)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
533 # @param idxTableName string bin set table name (output table)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
534 # @param overwrite boolean default = False
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
535 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
536 def createBinSetTable(self, setTableName, overwrite = False):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
537 idxTableName = "%s_idx" % setTableName # is an attribute in TableBinSetAdaptator
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
538 if not self.doesTableExist(setTableName):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
539 msg = "ERROR: '%s' doesn't exist => '%s' can't be created" % (setTableName, idxTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
540 self._log.error(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
541 raise RepetException(msg)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
542 self._log.info("creating %s for fast access" % idxTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
543 if overwrite:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
544 self.dropTable(idxTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
545
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
546 sqlCmd = "CREATE TABLE %s ( path int unsigned, bin float, contig varchar(255), min int, max int, strand int unsigned)" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
547 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
548
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
549 sqlCmd = "CREATE INDEX id ON %s ( path );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
550 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
551 sqlCmd = "CREATE INDEX ibin ON %s ( bin );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
552 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
553 sqlCmd = "CREATE INDEX icontig ON %s ( contig );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
554 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
555 sqlCmd = "CREATE INDEX imin ON %s ( min );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
556 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
557 sqlCmd = "CREATE INDEX imax ON %s ( max );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
558 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
559 sqlCmd = "CREATE INDEX istrand ON %s ( strand );" % idxTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
560 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
561
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
562 tmpTableName = "%s_tmp" % setTableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
563 self._createSetTableAndGroupByIdAndOrderByStrand(setTableName, tmpTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
564 iTSA = TableSetAdaptator(self, tmpTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
565 if not self.isEmpty(tmpTableName):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
566 tmpFileName = "%s.tmp%s" % (setTableName, str(os.getpid()))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
567 with open(tmpFileName, "w") as f:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
568 lSeqNames = iTSA.getSeqNameList()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
569 for seqName in lSeqNames:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
570 lSets = iTSA.getSetListFromSeqName(seqName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
571 for i in lSets:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
572 bin = i.getBin()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
573 max = i.getMax()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
574 min = i.getMin()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
575 strand = i.isOnDirectStrand()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
576 f.write("%d\t%f\t%s\t%d\t%d\t%d\n"%(i.id, bin, i.seqname, min, max, strand))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
577 sqlCmd="LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS ESCAPED BY '' " % (tmpFileName, idxTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
578 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
579 self.updateInfoTable(idxTableName, "%s bin indexes" % setTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
580 os.remove(tmpFileName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
581 self.dropTable(tmpTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
582
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
583
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
584 ## This table summarize the Set list information according to the identifier numbers. The min and max value is taken
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
585 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
586 def _createSetTableAndGroupByIdAndOrderByStrand(self, setTableName, outTableName):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
587 self.dropTable(outTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
588
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
589 sqlcmd="CREATE TABLE %s SELECT path, name, chr, min(start) AS start, max(end) AS end FROM %s WHERE start<end group by path;" % (outTableName, setTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
590 self.execute(sqlcmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
591
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
592 sqlcmd="INSERT INTO %s SELECT path, name, chr, max(start) AS start, min(end) AS end FROM %s WHERE start>end group by path;" % (outTableName, setTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
593 self.execute(sqlcmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
594
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
595 self.createIndex(outTableName, "set")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
596
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
597
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
598 ## Load data from a file into a MySQL table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
599 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
600 # @param tableName string table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
601 # @param fileName string file name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
602 # @param escapeFirstLine boolean True to ignore the first line of file, False otherwise
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
603 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
604 def loadDataFromFile(self, tableName, fileName, escapeFirstLine = False):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
605 if fileName != "":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
606 sqlCmd = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS ESCAPED BY '' " % ( fileName, tableName )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
607 if escapeFirstLine == True:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
608 sqlCmd = "%s IGNORE 1 LINES" %(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
609 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
610
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
611 self._log.info("%i entries in the table %s" % (self.getSize(tableName), tableName))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
612
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
613 ######################################################################################
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
614 #TODO: remove duplication with same methods in fastautils
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
615 ## Convert a fasta file to a length file
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
616 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
617 # @param inFile string name of the input fasta file
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
618 # @param outFile string name of the output file
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
619 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
620 def _convertFastaToLength(self, inFile, outFile = ""):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
621 if outFile == "":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
622 outFile = "%s.length" % inFile
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
623
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
624 if inFile != "":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
625 with open(inFile, "r") as inFH:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
626 with open(outFile, "w") as outFH:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
627 bioseq = Bioseq()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
628 while True:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
629 bioseq.read(inFH)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
630 if bioseq.sequence == None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
631 break
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
632 seqLen = bioseq.getLength()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
633 outFH.write("%s\t%d\n" % (bioseq.header.split()[0], seqLen))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
634
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
635
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
636 ## Convert a fasta file to a seq file
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
637 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
638 # @param inFile string name of the input fasta file
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
639 # @param outFile string name of the output file
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
640 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
641 def _convertFastaToSeq(self, inFile, outFile = ""):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
642 if outFile == "":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
643 outFile = "%s.seq" % inFile
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
644
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
645 if inFile != "":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
646 with open(inFile, "r") as inFH:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
647 with open(outFile, "w") as outFH:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
648 bioseq = Bioseq()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
649 while True:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
650 bioseq.read(inFH)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
651 if bioseq.sequence == None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
652 break
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
653 seqLen = bioseq.getLength()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
654 outFH.write("%s\t%s\t%s\t%d\n" % (bioseq.header.split()[0], \
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
655 bioseq.sequence, bioseq.header, seqLen))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
656
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
657 ######################################################################################
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
658
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
659 ## Change the coordinates such that the query is on the direct strand.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
660 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
661 # @param inTable string path table name to update
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
662 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
663 def changePathQueryCoordinatesToDirectStrand( self, inTable ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
664 sqlCmd = "ALTER TABLE %s ADD COLUMN tmpid INT NOT NULL AUTO_INCREMENT PRIMARY KEY" % ( inTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
665 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
666
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
667 tmpTable = "%s_tmp" % ( inTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
668 sqlCmd = "CREATE TABLE %s SELECT * FROM %s WHERE query_start > query_end" % ( tmpTable, inTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
669 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
670
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
671 sqlCmd = "UPDATE %s, %s" % ( inTable, tmpTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
672 sqlCmd += " SET %s.query_start=%s.query_end," % ( inTable, tmpTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
673 sqlCmd += " %s.query_end=%s.query_start," % ( inTable, tmpTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
674 sqlCmd += " %s.subject_start=%s.subject_end," % ( inTable, tmpTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
675 sqlCmd += " %s.subject_end=%s.subject_start" % ( inTable, tmpTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
676 sqlCmd += " WHERE %s.tmpid=%s.tmpid" % ( inTable, tmpTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
677 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
678
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
679 sqlCmd = "ALTER TABLE %s DROP COLUMN tmpid" % ( inTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
680 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
681 self.dropTable( tmpTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
682
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
683
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
684 ## Export data from a table in a file.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
685 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
686 # @param tableName string table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
687 # @param outFileName string output file name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
688 # @param keepFirstLine boolean if you want the first line (column name) in output file
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
689 # @param param string sql parameters to select data expected
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
690 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
691 def exportDataToFile( self, tableName, outFileName="", keepFirstLine=False, param="" ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
692 if outFileName == "": outFileName = tableName
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
693 prg = "mysql"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
694 cmd = prg
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
695 cmd += " -h %s" % ( self.host )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
696 cmd += " -u %s" % ( self.user )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
697 cmd += " -p\"%s\"" % ( self.passwd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
698 cmd += " --database=%s" % ( self.dbname )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
699 cmd += " -e\"SELECT * FROM %s" % ( tableName )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
700 if param != "": cmd += " %s" % ( param )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
701 cmd += ";\""
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
702 cmd += " > "
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
703 if keepFirstLine == False:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
704 cmd += "%s.tmp" % ( outFileName )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
705 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
706 cmd += "%s" % ( outFileName )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
707 log = os.system( cmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
708 if log != 0: print "ERROR: mysql returned %i" % ( log ); sys.exit(1)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
709
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
710 if keepFirstLine == False:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
711 tmpFileName = "%s.tmp" % ( outFileName )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
712 tmpFile = open( tmpFileName, "r" )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
713 outFile = open( outFileName, "w" )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
714 i = 0
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
715 for line in tmpFile:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
716 if i > 0:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
717 outFile.write( line )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
718 i += 1
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
719 tmpFile.close()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
720 outFile.close()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
721 os.remove( tmpFileName )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
722
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
723
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
724 ## Convert a Path table into an Align table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
725 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
726 # @param inPathTable string name of the input Path table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
727 # @param outAlignTable string name of the output Align table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
728 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
729 def convertPathTableIntoAlignTable( self, inPathTable, outAlignTable ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
730 sqlCmd = "CREATE TABLE %s SELECT query_name,query_start,query_end,subject_name,subject_start,subject_end,E_value,score,identity FROM %s;" % ( outAlignTable, inPathTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
731 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
732 self.updateInfoTable( outAlignTable, "" )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
733
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
734
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
735 ## Create a set table from a map table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
736 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
737 # @param mapTableName string map table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
738 # @param setTableName string new set table name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
739 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
740 def convertMapTableIntoSetTable( self, mapTableName, setTableName ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
741 sqlCmd = "CREATE TABLE %s (path int(10) unsigned auto_increment primary key) select name, chr, start, end from %s;" % (setTableName, mapTableName)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
742 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
743 self.createIndex(setTableName, "set")
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
744
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
745
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
746 ## Convert an Align table into a Path table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
747 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
748 # @param inAlignTable string name of the input Align table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
749 # @param outPathTable string name of the output Path table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
750 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
751 def convertAlignTableIntoPathTable( self, inAlignTable, outPathTable ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
752 self.createTable( outPathTable, "path", "", True )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
753 sqlCmd = "SELECT * FROM %s" % ( inAlignTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
754 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
755 lResults = self.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
756 rowIndex = 0
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
757 for res in lResults:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
758 rowIndex += 1
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
759 sqlCmd = "INSERT INTO %s" % ( outPathTable )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
760 sqlCmd += " (path,query_name,query_start,query_end,subject_name,subject_start,subject_end,E_value,score,identity)"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
761 sqlCmd += " VALUES ( '%i'" % ( rowIndex )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
762 for i in res:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
763 sqlCmd += ', "%s"' % ( i )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
764 sqlCmd += " )"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
765 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
766 self.updateInfoTable( outPathTable, "" )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
767
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
768
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
769 ## Give a list of instances according to the SQL command
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
770 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
771 # @param SQLCmd string is a SQL command
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
772 # @param methodGetInstance2Adapt a getter method name. With this method you choose the type of intances contained in lObjs. See example in Test_DbMySql.py.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
773 # @return lObjs list of instances
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
774 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
775 def getObjectListWithSQLCmd( self, SQLCmd, methodGetInstance2Adapt):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
776 self.execute( SQLCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
777 res = self.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
778 lObjs = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
779 for t in res:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
780 iObj = methodGetInstance2Adapt()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
781 iObj.setFromTuple( t )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
782 lObjs.append( iObj )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
783 return lObjs
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
784
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
785
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
786 ## Give a list of integer according to the SQL command
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
787 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
788 # @param sqlCmd string is a SQL command
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
789 # @return lInteger integer list
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
790 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
791 def getIntegerListWithSQLCmd( self, sqlCmd ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
792 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
793 res = self.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
794 lInteger = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
795 for t in res:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
796 if t[0] != None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
797 lInteger.append(int(t[0]))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
798 return lInteger
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
799
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
800
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
801 ## Give a int according to the SQL command
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
802 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
803 # @param sqlCmd string is a SQL command
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
804 # @return nb integer
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
805 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
806 def getIntegerWithSQLCmd( self, sqlCmd ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
807 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
808 res = self.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
809 nb = res[0][0]
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
810 if nb == None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
811 nb = 0
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
812 return nb
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
813
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
814
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
815 ## Give a list of str according to the SQL command
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
816 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
817 # @param sqlCmd string is a SQL command
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
818 # @return lString str list
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
819 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
820 def getStringListWithSQLCmd( self, sqlCmd ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
821 self.execute(sqlCmd)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
822 res = self.fetchall()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
823 lString = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
824 for i in res:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
825 lString.append(i[0])
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
826 return lString
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
827
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
828 #TODO: use API to add indexes
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
829 ## Remove doublons in a given table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
830 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
831 # @param table string name of a MySQL table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
832 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
833 def removeDoublons( self, table ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
834 tmpTable = "%s_%s" % ( table, time.strftime("%Y%m%d%H%M%S") )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
835 sqlCmd = "CREATE TABLE %s SELECT DISTINCT * FROM %s" % ( tmpTable, table )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
836 self.execute( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
837 self.dropTable( table )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
838 self.renameTable(tmpTable, table)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
839
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
840
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
841 ## Get a list of table names from a pattern
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
842 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
843 # @note for instance pattern = 'MyProject_%'
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
844 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
845 def getTableListFromPattern( self, pattern ):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
846 if pattern == "*" or pattern == "%":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
847 sqlCmd = "SHOW TABLES"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
848 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
849 sqlCmd = "SHOW TABLES like '%s'" % ( pattern )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
850 lTables = self.getStringListWithSQLCmd( sqlCmd )
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
851 return lTables