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