1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
|
#!/usr/bin/python -S
#
# Copyright 2009 Canonical Ltd. This software is licensed under the
# GNU Affero General Public License version 3 (see the file LICENSE).
# pylint: disable-msg=W0403
"""Populate schema additions for Translations Message Sharing.
This fills three new `TranslationMessage` columns: potemplate, language,
and variant. It also creates linking table entries connecting the
existing `POTMsgSet`s to their `POTemplate`s.
Since the schema additions are not in use yet, this script doesn't need
to be careful about grouping by template, preserving any kind of order,
and so on.
On the other hand, the Python code tree should already be initializing
the new columns and the linking table by the time this script is run.
So we do have to be careful not to interfere with that, or stumble when
some records have already been initialized properly.
"""
import _pythonpath
from zope.interface import implements
from canonical.database.postgresql import drop_tables
from canonical.database.sqlbase import cursor, sqlvalues
from canonical.launchpad.interfaces.looptuner import ITunableLoop
from lp.services.scripts.base import LaunchpadScript
from canonical.launchpad.utilities.looptuner import DBLoopTuner
class PopulateTranslationMessage:
"""`ITunableLoop` to populate new TranslationMessage columns."""
implements(ITunableLoop)
def __init__(self, txn, logger):
self.txn = txn
self.logger = logger
self.last_id = 0
cur = cursor()
cur.execute("""
SELECT id
INTO TEMP TABLE temp_todo
FROM TranslationMessage
WHERE potemplate IS NULL
ORDER BY id
""")
cur.execute(
"CREATE UNIQUE INDEX temp_todo__pkey ON temp_todo(id)")
cur.execute("ANALYZE temp_todo(id)")
cur.execute("SELECT max(id) FROM temp_todo")
max_id, = cur.fetchone()
if max_id is None:
self.finish_id = 0
else:
self.finish_id = max_id + 1
def isDone(self):
"""See `ITunableLoop`."""
done = (self.last_id >= self.finish_id)
if done:
drop_tables(cursor(), 'temp_todo')
return done
def __call__(self, chunk_size):
"""See `ITunableLoop`."""
chunk_size = int(chunk_size)
cur = cursor()
cur.execute("""
SELECT id
FROM temp_todo
WHERE id >= %s
ORDER BY id
OFFSET %s
LIMIT 1
""" % sqlvalues(self.last_id, chunk_size))
batch_limit = cur.fetchone()
if batch_limit is None:
end_id = self.finish_id
else:
end_id, = batch_limit
cur.execute("""
UPDATE TranslationMessage AS Msg
SET
potemplate = POFile.potemplate,
language = POFile.language,
variant = POFile.variant
FROM POFile
WHERE
POFile.id = Msg.pofile AND
Msg.potemplate IS NULL AND
Msg.id IN (
SELECT id
FROM temp_todo
WHERE id >= %s AND id < %s
)
""" % sqlvalues(self.last_id, end_id))
self.logger.info(
"Updated %d rows: %d - %d." % (
cur.rowcount, self.last_id, end_id))
self.txn.commit()
self.txn.begin()
self.last_id = end_id
class PopulateTranslationTemplateItem:
"""`ITunableLoop` to populate TranslationTemplateItem linking table."""
implements(ITunableLoop)
def __init__(self, txn, logger):
self.txn = txn
self.done = False
self.logger = logger
self.last_id = 0
cur = cursor()
cur.execute("""
SELECT POTMsgSet.id
INTO TEMP TABLE temp_todo
FROM POTMsgSet
LEFT JOIN TranslationTemplateItem AS ExistingEntry ON
ExistingEntry.potmsgset = potmsgset.id
WHERE
POTMsgSet.sequence > 0 AND
ExistingEntry.id IS NULL
ORDER BY id
""")
cur.execute(
"CREATE UNIQUE INDEX temp_todo__pkey ON temp_todo(id)")
cur.execute("ANALYZE temp_todo(id)")
cur.execute("SELECT max(id) FROM temp_todo")
max_id, = cur.fetchone()
if max_id is None:
self.finish_id = 0
else:
self.finish_id = max_id + 1
def isDone(self):
"""See `ITunableLoop`."""
done = (self.last_id >= self.finish_id)
if done:
drop_tables(cursor(), 'temp_todo')
return done
def __call__(self, chunk_size):
"""See `ITunableLoop`."""
chunk_size = int(chunk_size)
cur = cursor()
cur.execute("""
SELECT id
FROM temp_todo
WHERE id >= %s
ORDER BY id
OFFSET %s
LIMIT 1
""" % sqlvalues(self.last_id, chunk_size))
batch_limit = cur.fetchone()
if batch_limit is None:
end_id = self.finish_id
else:
end_id, = batch_limit
cur.execute("""
INSERT INTO TranslationTemplateItem(
potemplate, sequence, potmsgset)
SELECT POTMsgSet.potemplate, POTMsgSet.sequence, POTMsgSet.id
FROM POTMsgSet
LEFT JOIN TranslationTemplateItem AS ExistingEntry ON
ExistingEntry.potmsgset = potmsgset.id
WHERE
POTMsgSet.id >= %s AND
POTMsgSet.id < %s AND
POTMsgSet.sequence > 0 AND
ExistingEntry.id IS NULL
""" % sqlvalues(self.last_id, end_id))
self.logger.info("Inserted %d rows." % cur.rowcount)
self.txn.commit()
self.txn.begin()
self.last_id = end_id
class PopulateMessageSharingSchema(LaunchpadScript):
description = (
"Populate columns and linking table added for Translations Message "
"sharing.")
def main(self):
self.logger.info("Populating new TranslationMessage columns.")
tm_loop = PopulateTranslationMessage(self.txn, self.logger)
DBLoopTuner(tm_loop, 2, log=self.logger).run()
self.logger.info("Populating TranslationTemplateItem.")
tti_loop = PopulateTranslationTemplateItem(self.txn, self.logger)
DBLoopTuner(tti_loop, 2, log=self.logger).run()
if __name__ == '__main__':
script = PopulateMessageSharingSchema(
'canonical.launchpad.scripts.message-sharing-populate')
script.run()
|