annotate SMART/Java/Python/mySql/MySqlTable.py @ 16:6135c3075bc5

Deleted selected files
author m-zytnicki
date Mon, 22 Apr 2013 11:09:41 -0400
parents 769e306b7933
children 94ab73e8a190
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 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
2 # Copyright INRA-URGI 2009-2010
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
3 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
4 # This software is governed by the CeCILL license under French law and
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
5 # abiding by the rules of distribution of free software. You can use,
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
6 # modify and/ or redistribute the software under the terms of the CeCILL
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
7 # license as circulated by CEA, CNRS and INRIA at the following URL
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
8 # "http://www.cecill.info".
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
9 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
10 # As a counterpart to the access to the source code and rights to copy,
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
11 # modify and redistribute granted by the license, users are provided only
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
12 # with a limited warranty and the software's author, the holder of the
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
13 # economic rights, and the successive licensors have only limited
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
14 # liability.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
15 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
16 # In this respect, the user's attention is drawn to the risks associated
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
17 # with loading, using, modifying and/or developing or reproducing the
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
18 # software by the user in light of its specific status of free software,
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
19 # that may mean that it is complicated to manipulate, and that also
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
20 # therefore means that it is reserved for developers and experienced
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
21 # professionals having in-depth computer knowledge. Users are therefore
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
22 # encouraged to load and test the software's suitability as regards their
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
23 # requirements in conditions enabling the security of their systems and/or
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
24 # data to be ensured and, more generally, to use and operate it in the
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
25 # same conditions as regards security.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
26 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
27 # The fact that you are presently reading this means that you have had
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
28 # knowledge of the CeCILL license and that you accept its terms.
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
29 #
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
30 import re
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
31 import sys
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
32
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
33 class MySqlTable(object):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
34 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
35 Store a table of a mySQL database, used for transcripts or exons
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
36 Record a a name and a type (int, float, double) for each column
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
37 @ivar name: name of the table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
38 @type name: string
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
39 @ivar variables: name of the columns
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
40 @type variables: list of string
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
41 @ivar types: type of the columns
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
42 @type types: dict of string
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
43 @ivar mySqlConnection: connection to a database
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
44 @type mySqlConnection: class L{MySqlConnection<MySqlConnection>}
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
45 @ivar nbLines: number of rows
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
46 @type nbLines: int
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
47 @ivar verbosity: verbosity
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
48 @type verbosity: int
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
49 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
50
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
51 def __init__(self, connection, name, verbosity = 0):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
52 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
53 Constructor
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
54 Possibly retrieve column names and types if table exists
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
55 @param mySqlConnection: connection to a databas
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
56 @type mySqlConnection: class L{MySqlConnection<MySqlConnection>}
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
57 @param name: name of the table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
58 @type name: string
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
59 @param verbosity: verbosity
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
60 @type verbosity: int
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
61 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
62 self.name = name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
63 self.variables = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
64 self.types = {}
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
65 self.sizes = {}
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
66 self.nbLines = None
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
67 self.verbosity = verbosity
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
68 self.mySqlConnection = connection
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
69 queryTables = self.mySqlConnection.executeQuery("SELECT name FROM sqlite_master WHERE type LIKE 'table' AND name LIKE '%s'" % (self.name))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
70 self.created = not queryTables.isEmpty()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
71 if self.created:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
72 queryFields = self.mySqlConnection.executeQuery("PRAGMA table_info('%s')" % (name))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
73 for field in queryFields.getIterator():
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
74 if field[1] != "id":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
75 self.variables.append(field[1])
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
76 self.types[field[1]] = field[2]
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
77 self.sizes[field[1]] = field[3]
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
78
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
79
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
80 def getName(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
81 return self.name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
82
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
83
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
84 def create(self, variables, types, sizes):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
85 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
86 Create a table using give column names and types
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
87 @param variables: names of the columns
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
88 @type variables: list of string
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
89 @param types: types of the columns
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
90 @type types: dict of string
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
91 @param sizes: sizes of the types
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
92 @type sizes: dict of int
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
93 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
94 self.variables = variables
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
95 self.types = types
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
96 self.sizes = sizes
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
97 if self.created:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
98 self.remove()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
99 query = "CREATE TABLE '%s' (id INTEGER PRIMARY KEY" % (self.name)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
100 for variable in variables:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
101 query = "%s, %s %s(%d)" % (query, variable, types[variable], sizes[variable])
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
102 query += ")"
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
103 self.mySqlConnection.executeQuery(query)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
104 self.created = True
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
105
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
106
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
107 def insertMany(self, lines):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
108 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
109 Insert many lines
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
110 @param lines: the list of values
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
111 @type lines: list of lists
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
112 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
113 commands = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
114 for values in lines:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
115 commands.append("INSERT INTO '%s' (%s) VALUES (%s)" % (self.name, ", ".join(self.variables), ", ".join([MySqlTable.formatSql(values[variable], self.types[variable], self.sizes[variable]) for variable in self.variables])))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
116 self.mySqlConnection.executeManyQueries(commands)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
117
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
118
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
119 def rename(self, name):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
120 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
121 Rename the table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
122 @param name: the new name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
123 @type name: string
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
124 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
125 self.mySqlConnection.executeQuery("RENAME TABLE '%s' TO '%s'" % (self.name, name))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
126 self.name = name
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
127
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
128
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
129 def copy(self, table):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
130 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
131 Copy the given table this one
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
132 @param table: the table to be copied
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
133 @type table: class L{MySqlTable<MySqlTable>}
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
134 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
135 variables = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
136 types = {}
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
137 sizes = {}
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
138 fields = self.mySqlConnection.executeQuery("PRAGMA table_info(%s)" % (table.name))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
139 for field in fields.getIterator():
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
140 if field[1] != "id":
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
141 variables.append(field[1])
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
142 m = re.search(r"(\w+)\((\d+)\)", field[2])
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
143 if m == None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
144 raise Exception("\nFormat %s in table %s is strange." % (field[2], table.name))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
145 types[field[1]] = m.group(1)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
146 sizes[field[1]] = int(m.group(2))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
147 self.create(variables, types, sizes)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
148 self.mySqlConnection.executeQuery("INSERT INTO '%s' SELECT * FROM %s" % (self.name, table.name))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
149
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
150
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
151 def add(self, table):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
152 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
153 Add the content of a table to this one
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
154 @param table: the table to be added
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
155 @type table: class L{MySqlTable<MySqlTable>}
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
156 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
157 self.mySqlConnection.executeQuery("INSERT INTO '%s' SELECT * FROM %s" % (self.name, table.name))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
158 self.created = True
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
159
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
160
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
161 def exists(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
162 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
163 Check if the table exists in mySQL
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
164 @return: true if it exits
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
165 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
166 return self.created
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 def remove(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
170 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
171 Remove this table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
172 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
173 if self.exists():
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
174 query = "DROP TABLE IF EXISTS '%s'" % (self.name)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
175 self.mySqlConnection.executeQuery(query)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
176 self.created = False
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
177
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
178
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
179 def clear(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
180 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
181 Clear the content of this table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
182 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
183 self.mySqlConnection.executeQuery("DELETE FROM '%s'" % (self.name))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
184
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
185
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
186 def getNbElements(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
187 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
188 Count the number of rows in the table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
189 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
190 command = "SELECT COUNT(id) FROM '%s'" % (self.name)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
191 query = self.mySqlConnection.executeQuery(command)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
192 return int(query.getLine()[0])
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
193
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
194
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
195 def formatSql(self, value, type, size):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
196 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
197 Format a value using MySQL encapsulation
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
198 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
199 if type.find("int") != -1:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
200 return "%d" % value
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
201 if type.find("float") != -1:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
202 return "%.10f" % value
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
203 if type.find("double") != -1:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
204 return "%.20f" % value
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
205 if type.find("varchar") != -1:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
206 if len(value) > size:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
207 return "'%s'" % value[0:size]
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
208 return "'%s'" % value
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
209 raise Exception("Do not understand type %s" % (type))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
210 formatSql = classmethod(formatSql)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
211
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
212
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
213 def addLine(self, values):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
214 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
215 Add a row to this table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
216 @param values: the values of the row
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
217 @type values: dict
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
218 @return: the id of the added row
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
219 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
220 sqlValues = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
221 for variable in self.variables:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
222 sqlValues.append(self.formatSql(values[variable], self.types[variable], self.sizes[variable]))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
223 command = "INSERT INTO '%s' (%s) VALUES (%s)" % (self.name, ", ".join(self.variables), ", ".join(sqlValues))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
224 id = self.mySqlConnection.executeQuery(command, True)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
225 return id
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
226
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
227
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
228 def retrieveFromId(self, id):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
229 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
230 Retrieve a row from its id
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
231 @param id: the id of the row
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
232 @type id: int
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
233 @return: the row
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
234 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
235 query = self.mySqlConnection.executeQuery("SELECT * FROM '%s' WHERE id = %d" % (self.name, id))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
236 result = query.getLine()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
237 if result == None:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
238 raise Exception("Error! Id %d is not in the table %s!" % (id, self.name))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
239 return result
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
240
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
241
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
242 def retrieveBulkFromId(self, ids):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
243 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
244 Retrieve a row from its id
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
245 @param id: the ids of the row
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
246 @type id: list of int
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
247 @return: the row
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
248 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
249 if not ids:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
250 return []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
251 MAXSIZE = 1000
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
252 results = []
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
253 for batch in range(len(ids) / MAXSIZE + 1):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
254 theseIds = ids[batch * MAXSIZE : (batch+1) * MAXSIZE]
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
255 if theseIds:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
256 query = self.mySqlConnection.executeQuery("SELECT * FROM '%s' WHERE id IN (%s)" % (self.name, ", ".join(["%d" % (id) for id in theseIds])))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
257 lines = query.getLines()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
258 if len(lines) != len(theseIds):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
259 raise Exception("Error! Some Ids of (%s) is are missing in the table '%s' (got %d instead of %d)!" % (", ".join(["%d" % (id) for id in theseIds]), self.name, len(lines)), len(theseIds))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
260 results.extend(lines)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
261 return results
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
262
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
263
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
264 def removeFromId(self, id):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
265 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
266 Remove a row from its id
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
267 @param id: the id of the row
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
268 @type id: int
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
269 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
270 self.mySqlConnection.executeQuery("DELETE FROM '%s' WHERE id = %d" % (self.name, id))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
271
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
272
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
273 def getIterator(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
274 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
275 Iterate on the content of table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
276 @return: iterator to the rows of the table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
277 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
278 if not self.created:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
279 return
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
280 MAXSIZE = 1000
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
281 query = self.mySqlConnection.executeQuery("SELECT count(id) FROM '%s'" % (self.name))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
282 nbRows = int(query.getLine()[0])
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
283 for chunk in range((nbRows / MAXSIZE) + 1):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
284 query = self.mySqlConnection.executeQuery("SELECT * FROM '%s' LIMIT %d, %d" % (self.name, chunk * MAXSIZE, MAXSIZE))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
285 for line in query.getIterator():
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
286 yield line
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
287
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
288
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
289 def createIndex(self, indexName, values, unique = False, fullText = False):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
290 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
291 Add an index on the table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
292 @param indexName: name of the index
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
293 @type indexName: string
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
294 @param values: values to be indexed
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
295 @type values: string
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
296 @param unique: if the index is unique
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
297 @type unique: boolean
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
298 @param fullText: whether full text should be indexed
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
299 @type fullText: boolean
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
300 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
301 self.mySqlConnection.executeQuery("CREATE %s%sINDEX '%s' ON '%s' (%s)" % ("UNIQUE " if unique else "", "FULLTEXT " if fullText else "", indexName, self.name, ", ".join(values)))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
302
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
303
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
304 def setDefaultTagValue(self, field, name, value):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
305 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
306 Add a tag value
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
307 @param name: name of the tag
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
308 @type name: string
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
309 @param value: value of the tag
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
310 @type value: string or int
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
311 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
312 newData = {}
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
313 for line in MySqlTable.getIterator(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
314 id = line[0]
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
315 tags = line[field]
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
316 if tags == '':
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
317 newTag = "%s=%s" % (name, value)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
318 else:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
319 newTag = "%s;%s=%s" % (tags, name, value)
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
320 if name not in [tag.split("=")[0] for tag in tags.split(";")]:
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
321 newData[id] = newTag
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
322 for id, tag in newData.iteritems():
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
323 query = self.mySqlConnection.executeQuery("UPDATE '%s' SET tags = '%s' WHERE id = %i" % (self.name, tag, id))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
324
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
325
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
326
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
327 def show(self):
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
328 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
329 Drop the content of the current table
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
330 """
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
331 query = self.mySqlConnection.executeQuery("SELECT * FROM '%s'" % (self.name))
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
332 print query.getLines()
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
333
769e306b7933 Change the repository level.
yufei-luo
parents:
diff changeset
334