001
014
015 package com.liferay.portal.upgrade.v6_1_0;
016
017 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018 import com.liferay.portal.kernel.repository.model.FileVersion;
019 import com.liferay.portal.kernel.upgrade.UpgradeProcess;
020 import com.liferay.portal.kernel.util.CharPool;
021 import com.liferay.portal.kernel.util.GetterUtil;
022 import com.liferay.portal.kernel.util.MimeTypesUtil;
023 import com.liferay.portal.kernel.util.SetUtil;
024 import com.liferay.portal.kernel.util.StringBundler;
025 import com.liferay.portal.kernel.util.StringUtil;
026 import com.liferay.portal.repository.liferayrepository.model.LiferayFileVersion;
027 import com.liferay.portal.upgrade.v6_1_0.util.DLFileVersionTable;
028 import com.liferay.portal.util.PropsValues;
029 import com.liferay.portlet.documentlibrary.model.DLFileEntry;
030 import com.liferay.portlet.documentlibrary.model.DLFileVersion;
031 import com.liferay.portlet.documentlibrary.model.impl.DLFileVersionImpl;
032 import com.liferay.portlet.documentlibrary.util.ImageProcessorUtil;
033
034 import java.sql.Connection;
035 import java.sql.PreparedStatement;
036 import java.sql.ResultSet;
037 import java.sql.SQLException;
038 import java.sql.Timestamp;
039
040 import java.util.Set;
041
042
048 public class UpgradeDocumentLibrary extends UpgradeProcess {
049
050 protected void addSync(
051 long syncId, long companyId, Timestamp createDate,
052 Timestamp modifiedDate, long fileId, long repositoryId,
053 long parentFolderId, String event, String type)
054 throws Exception {
055
056 Connection con = null;
057 PreparedStatement ps = null;
058
059 try {
060 con = DataAccess.getUpgradeOptimizedConnection();
061
062 ps = con.prepareStatement(
063 "insert into DLSync (syncId, companyId, createDate, " +
064 "modifiedDate, fileId, repositoryId, parentFolderId, " +
065 "event, type_) values (?, ?, ?, ?, ?, ?, ?, ?, ?)");
066
067 ps.setLong(1, syncId);
068 ps.setLong(2, companyId);
069 ps.setTimestamp(3, createDate);
070 ps.setTimestamp(4, createDate);
071 ps.setLong(5, fileId);
072 ps.setLong(6, repositoryId);
073 ps.setLong(7, parentFolderId);
074 ps.setString(8, event);
075 ps.setString(9, type);
076
077 ps.executeUpdate();
078 }
079 finally {
080 DataAccess.cleanUp(con, ps);
081 }
082 }
083
084 @Override
085 protected void doUpgrade() throws Exception {
086 updateFileEntries();
087 updateFileRanks();
088 updateFileShortcuts();
089 updateFileVersions();
090 updateLocks();
091
092 if (PropsValues.DL_FILE_ENTRY_PREVIEW_AUTO_CREATE_ON_UPGRADE) {
093 updateThumbnails();
094 }
095
096
097 }
098
099 protected long getFileEntryId(long groupId, long folderId, String name)
100 throws Exception {
101
102 Connection con = null;
103 PreparedStatement ps = null;
104 ResultSet rs = null;
105
106 try {
107 con = DataAccess.getUpgradeOptimizedConnection();
108
109 ps = con.prepareStatement(
110 "select fileEntryId from DLFileEntry where groupId = ? and " +
111 "folderId = ? and name = ?");
112
113 ps.setLong(1, groupId);
114 ps.setLong(2, folderId);
115 ps.setString(3, name);
116
117 rs = ps.executeQuery();
118
119 if (rs.next()) {
120 return rs.getLong("fileEntryId");
121 }
122
123 return 0;
124 }
125 finally {
126 DataAccess.cleanUp(con, ps, rs);
127 }
128 }
129
130 protected long getGroupId(long folderId) throws Exception {
131 Connection con = null;
132 PreparedStatement ps = null;
133 ResultSet rs = null;
134
135 long groupId = 0;
136
137 try {
138 con = DataAccess.getUpgradeOptimizedConnection();
139
140 ps = con.prepareStatement(
141 "select groupId from DLFolder where folderId = ?");
142
143 ps.setLong(1, folderId);
144
145 rs = ps.executeQuery();
146
147 if (rs.next()) {
148 groupId = rs.getLong("groupId");
149 }
150 }
151 finally {
152 DataAccess.cleanUp(con, ps, rs);
153 }
154
155 return groupId;
156 }
157
158 protected void updateFileEntries() throws Exception {
159 Connection con = null;
160 PreparedStatement ps = null;
161 ResultSet rs = null;
162
163 try {
164 con = DataAccess.getUpgradeOptimizedConnection();
165
166 ps = con.prepareStatement(
167 "select fileEntryId, extension from DLFileEntry");
168
169 rs = ps.executeQuery();
170
171 while (rs.next()) {
172 long fileEntryId = rs.getLong("fileEntryId");
173 String extension = rs.getString("extension");
174
175 String mimeType = MimeTypesUtil.getContentType(
176 "A." + extension);
177
178 runSQL(
179 "update DLFileEntry set mimeType = '" + mimeType +
180 "' where fileEntryId = " + fileEntryId);
181 }
182 }
183 finally {
184 DataAccess.cleanUp(con, ps, rs);
185 }
186 }
187
188 protected void updateFileRanks() throws Exception {
189 Connection con = null;
190 PreparedStatement ps = null;
191 ResultSet rs = null;
192
193 try {
194 con = DataAccess.getUpgradeOptimizedConnection();
195
196 ps = con.prepareStatement(
197 "select groupId, fileRankId, folderId, name from DLFileRank");
198
199 rs = ps.executeQuery();
200
201 while (rs.next()) {
202 long groupId = rs.getLong("groupId");
203 long fileRankId = rs.getLong("fileRankId");
204 long folderId = rs.getLong("folderId");
205 String name = rs.getString("name");
206
207 long fileEntryId = getFileEntryId(groupId, folderId, name);
208
209 runSQL(
210 "update DLFileRank set fileEntryId = " + fileEntryId +
211 " where fileRankId = " + fileRankId);
212 }
213 }
214 finally {
215 DataAccess.cleanUp(con, ps, rs);
216 }
217
218 runSQL("alter table DLFileRank drop column folderId");
219 runSQL("alter table DLFileRank drop column name");
220 }
221
222 protected void updateFileShortcuts() throws Exception {
223 Connection con = null;
224 PreparedStatement ps = null;
225 ResultSet rs = null;
226
227 try {
228 con = DataAccess.getUpgradeOptimizedConnection();
229
230 ps = con.prepareStatement(
231 "select fileShortcutId, toFolderId, toName from " +
232 "DLFileShortcut");
233
234 rs = ps.executeQuery();
235
236 while (rs.next()) {
237 long fileShortcutId = rs.getLong("fileShortcutId");
238 long toFolderId = rs.getLong("toFolderId");
239 String toName = rs.getString("toName");
240
241 long groupId = getGroupId(toFolderId);
242
243 long toFileEntryId = getFileEntryId(
244 groupId, toFolderId, toName);
245
246 runSQL(
247 "update DLFileShortcut set toFileEntryId = " +
248 toFileEntryId + " where fileShortcutId = " +
249 fileShortcutId);
250 }
251 }
252 finally {
253 DataAccess.cleanUp(con, ps, rs);
254 }
255
256 runSQL("alter table DLFileShortcut drop column toFolderId");
257 runSQL("alter table DLFileShortcut drop column toName");
258 }
259
260 protected void updateFileVersions() throws Exception {
261 Connection con = null;
262 PreparedStatement ps = null;
263 ResultSet rs = null;
264
265 try {
266 con = DataAccess.getUpgradeOptimizedConnection();
267
268 ps = con.prepareStatement(
269 "select groupId, fileVersionId, folderId, name, extension " +
270 "from DLFileVersion");
271
272 rs = ps.executeQuery();
273
274 while (rs.next()) {
275 long groupId = rs.getLong("groupId");
276 long fileVersionId = rs.getLong("fileVersionId");
277 long folderId = rs.getLong("folderId");
278 String name = rs.getString("name");
279 String extension = rs.getString("extension");
280
281 String mimeType = MimeTypesUtil.getContentType(
282 "A." + extension);
283
284 long fileEntryId = getFileEntryId(groupId, folderId, name);
285
286 runSQL(
287 "update DLFileVersion set fileEntryId = " + fileEntryId +
288 ", mimeType = '" + mimeType +
289 "' where fileVersionId = " + fileVersionId);
290 }
291 }
292 finally {
293 DataAccess.cleanUp(con, ps, rs);
294 }
295
296 try {
297 runSQL("alter_column_type DLFileVersion extraSettings TEXT null");
298 runSQL("alter_column_type DLFileVersion title VARCHAR(255) null");
299 runSQL("alter table DLFileVersion drop column name");
300 }
301 catch (SQLException sqle) {
302 upgradeTable(
303 DLFileVersionTable.TABLE_NAME, DLFileVersionTable.TABLE_COLUMNS,
304 DLFileVersionTable.TABLE_SQL_CREATE,
305 DLFileVersionTable.TABLE_SQL_ADD_INDEXES);
306 }
307 }
308
309 protected void updateLocks() throws Exception {
310 Connection con = null;
311 PreparedStatement ps = null;
312 ResultSet rs = null;
313
314 try {
315 con = DataAccess.getUpgradeOptimizedConnection();
316
317 ps = con.prepareStatement(
318 "select lockId, key_ from Lock_ where className = ?");
319
320 ps.setString(1, DLFileEntry.class.getName());
321
322 rs = ps.executeQuery();
323
324 while (rs.next()) {
325 long lockId = rs.getLong("lockId");
326 String key = rs.getString("key_");
327
328 String[] keyArray = StringUtil.split(key, CharPool.POUND);
329
330 if (keyArray.length != 3) {
331 continue;
332 }
333
334 long groupId = GetterUtil.getLong(keyArray[0]);
335 long folderId = GetterUtil.getLong(keyArray[1]);
336 String name = keyArray[2];
337
338 long fileEntryId = getFileEntryId(groupId, folderId, name);
339
340 if (fileEntryId > 0) {
341 runSQL(
342 "update Lock_ set key_ = '" + fileEntryId +
343 "' where lockId = " + lockId);
344 }
345 }
346 }
347 finally {
348 DataAccess.cleanUp(con, ps, rs);
349 }
350 }
351
352 protected void updateSyncs() throws Exception {
353 Connection con = null;
354 PreparedStatement ps = null;
355 ResultSet rs = null;
356
357 try {
358 con = DataAccess.getUpgradeOptimizedConnection();
359
360 StringBundler sb = new StringBundler(10);
361
362 sb.append("select DLFileEntry.fileEntryId as fileId, ");
363 sb.append("DLFileEntry.groupId as groupId, DLFileEntry.companyId");
364 sb.append(" as companyId, DLFileEntry.createDate as createDate, ");
365 sb.append("DLFileEntry.folderId as parentFolderId, 'file' as ");
366 sb.append("type from DLFileEntry union all select ");
367 sb.append("DLFolder.folderId as fileId, DLFolder.groupId as ");
368 sb.append("groupId, DLFolder.companyId as companyId, ");
369 sb.append("DLFolder.createDate as createDate, ");
370 sb.append("DLFolder.parentFolderId as parentFolderId, 'folder' ");
371 sb.append("as type from DLFolder");
372
373 String sql = sb.toString();
374
375 ps = con.prepareStatement(sql);
376
377 rs = ps.executeQuery();
378
379 while (rs.next()) {
380 long fileId = rs.getLong("fileId");
381 long groupId = rs.getLong("groupId");
382 long companyId = rs.getLong("companyId");
383 Timestamp createDate = rs.getTimestamp("createDate");
384 long parentFolderId = rs.getLong("parentFolderId");
385 String type = rs.getString("type");
386
387 addSync(
388 increment(), companyId, createDate, createDate, fileId,
389 groupId, parentFolderId, "add", type);
390 }
391 }
392 finally {
393 DataAccess.cleanUp(con, ps, rs);
394 }
395 }
396
397 protected void updateThumbnails() throws Exception {
398 Connection con = null;
399 PreparedStatement ps = null;
400 ResultSet rs = null;
401
402 try {
403 con = DataAccess.getUpgradeOptimizedConnection();
404
405 ps = con.prepareStatement("select fileEntryId from DLFileEntry");
406
407 rs = ps.executeQuery();
408
409 while (rs.next()) {
410 long fileEntryId = rs.getLong("fileEntryId");
411
412 updateThumbnails(fileEntryId);
413 }
414 }
415 finally {
416 DataAccess.cleanUp(con, ps, rs);
417 }
418 }
419
420 protected void updateThumbnails(long fileEntryId) throws Exception {
421 Connection con = null;
422 PreparedStatement ps = null;
423 ResultSet rs = null;
424
425 try {
426 con = DataAccess.getUpgradeOptimizedConnection();
427
428 ps = con.prepareStatement(
429 "select fileVersionId, userId, extension, mimeType, version " +
430 "from DLFileVersion where fileEntryId = " + fileEntryId +
431 " order by version asc");
432
433 rs = ps.executeQuery();
434
435 while (rs.next()) {
436 long fileVersionId = rs.getLong("fileVersionId");
437 long userId = rs.getLong("userId");
438 String extension = rs.getString("extension");
439 String mimeType = rs.getString("mimeType");
440 String version = rs.getString("version");
441
442 if (_imageMimeTypes.contains(mimeType)) {
443 DLFileVersion dlFileVersion = new DLFileVersionImpl();
444
445 dlFileVersion.setFileVersionId(fileVersionId);
446 dlFileVersion.setUserId(userId);
447 dlFileVersion.setFileEntryId(fileEntryId);
448 dlFileVersion.setExtension(extension);
449 dlFileVersion.setMimeType(mimeType);
450 dlFileVersion.setVersion(version);
451
452 FileVersion fileVersion = new LiferayFileVersion(
453 dlFileVersion);
454
455 ImageProcessorUtil.generateImages(fileVersion);
456 }
457 }
458 }
459 finally {
460 DataAccess.cleanUp(con, ps, rs);
461 }
462 }
463
464 private static Set<String> _imageMimeTypes = SetUtil.fromArray(
465 PropsValues.DL_FILE_ENTRY_PREVIEW_IMAGE_MIME_TYPES);
466
467 }