001    /**
002     * Copyright (c) 2000-2012 Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portal.upgrade.v5_2_9_to_6_0_11;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.log.Log;
019    import com.liferay.portal.kernel.log.LogFactoryUtil;
020    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
021    import com.liferay.portal.kernel.upgrade.util.UpgradeColumn;
022    import com.liferay.portal.kernel.upgrade.util.UpgradeTable;
023    import com.liferay.portal.kernel.upgrade.util.UpgradeTableFactoryUtil;
024    import com.liferay.portal.kernel.util.StringBundler;
025    import com.liferay.portal.kernel.util.StringUtil;
026    import com.liferay.portal.kernel.workflow.WorkflowConstants;
027    import com.liferay.portal.upgrade.v5_2_8_to_6_0_5.util.DLFileEntryTable;
028    import com.liferay.portal.upgrade.v5_2_8_to_6_0_5.util.DLFileVersionTable;
029    import com.liferay.portal.upgrade.v6_0_0.util.DLFileEntryNameUpgradeColumnImpl;
030    import com.liferay.portal.upgrade.v6_0_0.util.DLFileEntryTitleUpgradeColumnImpl;
031    import com.liferay.portal.upgrade.v6_0_0.util.DLFileEntryVersionUpgradeColumnImpl;
032    import com.liferay.portal.upgrade.v6_0_0.util.DLFileRankTable;
033    import com.liferay.portal.upgrade.v6_0_0.util.DLFileShortcutTable;
034    import com.liferay.portal.util.PortletKeys;
035    import com.liferay.portlet.documentlibrary.model.DLFolderConstants;
036    import com.liferay.portlet.documentlibrary.store.DLStoreUtil;
037    
038    import java.sql.Connection;
039    import java.sql.PreparedStatement;
040    import java.sql.ResultSet;
041    import java.sql.Timestamp;
042    
043    /**
044     * @author Jorge Ferrer
045     * @author Alexander Chow
046     * @author Douglas Wong
047     */
048    public class UpgradeDocumentLibrary extends UpgradeProcess {
049    
050            protected void addFileVersion(
051                            long groupId, long companyId, long userId, String userName,
052                            long folderId, String name, String title, double version, int size,
053                            long fileEntryId)
054                    throws Exception {
055    
056                    Timestamp now = new Timestamp(System.currentTimeMillis());
057    
058                    Connection con = null;
059                    PreparedStatement ps = null;
060    
061                    try {
062                            con = DataAccess.getConnection();
063    
064                            StringBundler sb = new StringBundler(5);
065    
066                            sb.append("insert into DLFileVersion (fileVersionId, groupId, ");
067                            sb.append("companyId, userId, userName, createDate, folderId, ");
068                            sb.append("name, title, version, size_, status, statusByUserId, ");
069                            sb.append("statusByUserName, statusDate, fileEntryId) values ");
070                            sb.append("(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
071    
072                            String sql = sb.toString();
073    
074                            ps = con.prepareStatement(sql);
075    
076                            ps.setLong(1, increment());
077                            ps.setLong(2, groupId);
078                            ps.setLong(3, companyId);
079                            ps.setLong(4, userId);
080                            ps.setString(5, userName);
081                            ps.setTimestamp(6, now);
082                            ps.setLong(7, folderId);
083                            ps.setString(8, name);
084                            ps.setString(9, title);
085                            ps.setDouble(10, version);
086                            ps.setInt(11, size);
087                            ps.setInt(12, WorkflowConstants.STATUS_APPROVED);
088                            ps.setLong(13, userId);
089                            ps.setString(14, userName);
090                            ps.setTimestamp(15, now);
091                            ps.setLong(16, fileEntryId);
092    
093                            ps.executeUpdate();
094                    }
095                    finally {
096                            DataAccess.cleanUp(con, ps);
097                    }
098            }
099    
100            @Override
101            protected void doUpgrade() throws Exception {
102                    Connection con = null;
103                    PreparedStatement ps = null;
104                    ResultSet rs = null;
105    
106                    try {
107                            con = DataAccess.getConnection();
108    
109                            ps = con.prepareStatement("select * from DLFileEntry");
110    
111                            rs = ps.executeQuery();
112    
113                            while (rs.next()) {
114                                    long companyId = rs.getLong("companyId");
115                                    long groupId = rs.getLong("groupId");
116                                    long folderId = rs.getLong("folderId");
117                                    String name = rs.getString("name");
118    
119                                    String portletId = PortletKeys.DOCUMENT_LIBRARY;
120                                    long repositoryId = folderId;
121    
122                                    if (repositoryId ==
123                                                    DLFolderConstants.DEFAULT_PARENT_FOLDER_ID) {
124    
125                                            repositoryId = groupId;
126                                    }
127    
128                                    String newName = DLFileEntryNameUpgradeColumnImpl.getNewName(
129                                            name);
130    
131                                    if (!newName.equals(name)) {
132                                            DLStoreUtil.updateFile(
133                                                    companyId, repositoryId, name, newName);
134                                    }
135                            }
136                    }
137                    finally {
138                            DataAccess.cleanUp(con, ps, rs);
139                    }
140    
141                    synchronizeFileVersions();
142    
143                    // DLFileEntry
144    
145                    UpgradeColumn nameColumn = new DLFileEntryNameUpgradeColumnImpl("name");
146                    UpgradeColumn titleColumn = new DLFileEntryTitleUpgradeColumnImpl(
147                            nameColumn, "title");
148                    UpgradeColumn versionColumn = new DLFileEntryVersionUpgradeColumnImpl(
149                            "version");
150    
151                    UpgradeTable upgradeTable = UpgradeTableFactoryUtil.getUpgradeTable(
152                            DLFileEntryTable.TABLE_NAME, DLFileEntryTable.TABLE_COLUMNS,
153                            nameColumn, titleColumn, versionColumn);
154    
155                    upgradeTable.setCreateSQL(DLFileEntryTable.TABLE_SQL_CREATE);
156                    upgradeTable.setIndexesSQL(DLFileEntryTable.TABLE_SQL_ADD_INDEXES);
157    
158                    upgradeTable.updateTable();
159    
160                    // DLFileRank
161    
162                    upgradeTable = UpgradeTableFactoryUtil.getUpgradeTable(
163                            DLFileRankTable.TABLE_NAME, DLFileRankTable.TABLE_COLUMNS,
164                            nameColumn);
165    
166                    upgradeTable.setCreateSQL(
167                            StringUtil.replace(
168                                    DLFileRankTable.TABLE_SQL_CREATE,
169                                    ",createDate DATE null",
170                                    ",createDate DATE null,fileEntryId LONG"));
171                    upgradeTable.setIndexesSQL(DLFileRankTable.TABLE_SQL_ADD_INDEXES);
172    
173                    upgradeTable.updateTable();
174    
175                    // DLFileShortcut
176    
177                    UpgradeColumn toNameColumn = new DLFileEntryNameUpgradeColumnImpl(
178                            "toName");
179    
180                    upgradeTable = UpgradeTableFactoryUtil.getUpgradeTable(
181                            DLFileShortcutTable.TABLE_NAME, DLFileShortcutTable.TABLE_COLUMNS,
182                            toNameColumn);
183    
184                    upgradeTable.setCreateSQL(
185                            StringUtil.replace(
186                                    DLFileShortcutTable.TABLE_SQL_CREATE,
187                                    ",folderId LONG",
188                                    ",folderId LONG,toFileEntryId LONG"));
189                    upgradeTable.setIndexesSQL(DLFileShortcutTable.TABLE_SQL_ADD_INDEXES);
190    
191                    upgradeTable.updateTable();
192    
193                    // DLFileVersion
194    
195                    upgradeTable = UpgradeTableFactoryUtil.getUpgradeTable(
196                            DLFileVersionTable.TABLE_NAME, DLFileVersionTable.TABLE_COLUMNS,
197                            nameColumn, versionColumn);
198    
199                    upgradeTable.setCreateSQL(
200                            StringUtil.replace(
201                                    DLFileVersionTable.TABLE_SQL_CREATE,
202                                    ",title VARCHAR(75) null",
203                                    ",title VARCHAR(255) null,fileEntryId LONG"));
204                    upgradeTable.setIndexesSQL(DLFileVersionTable.TABLE_SQL_ADD_INDEXES);
205    
206                    upgradeTable.updateTable();
207    
208                    try {
209                            runSQL("drop index IX_CE705D48 on DLFileRank");
210                            runSQL("drop index IX_40B56512 on DLFileRank");
211    
212                            runSQL("drop index IX_55C736AC on DLFileShortcut");
213                            runSQL("drop index IX_346A0992 on DLFileShortcut");
214    
215                            runSQL("drop index IX_B413F1EC on DLFileVersion");
216                            runSQL("drop index IX_94E784D2 on DLFileVersion");
217                            runSQL("drop index IX_2F8FED9C on DLFileVersion");
218                    }
219                    catch (Exception e) {
220                            _log.error(e, e);
221                    }
222            }
223    
224            protected void synchronizeFileVersions() throws Exception {
225                    Connection con = null;
226                    PreparedStatement ps = null;
227                    ResultSet rs = null;
228    
229                    try {
230                            con = DataAccess.getConnection();
231    
232                            StringBundler sb = new StringBundler(5);
233    
234                            sb.append("select * from DLFileEntry dlFileEntry where version ");
235                            sb.append("not in (select version from DLFileVersion ");
236                            sb.append("dlFileVersion where (dlFileVersion.folderId = ");
237                            sb.append("dlFileEntry.folderId) and (dlFileVersion.name = ");
238                            sb.append("dlFileEntry.name))");
239    
240                            String sql = sb.toString();
241    
242                            ps = con.prepareStatement(sql);
243    
244                            rs = ps.executeQuery();
245    
246                            while (rs.next()) {
247                                    long companyId = rs.getLong("companyId");
248                                    long fileEntryId = rs.getLong("fileEntryId");
249                                    long groupId = rs.getLong("groupId");
250                                    long userId = rs.getLong("userId");
251                                    String userName = rs.getString("userName");
252                                    long folderId = rs.getLong("folderId");
253                                    String name = rs.getString("name");
254                                    String title = rs.getString("title");
255                                    double version = rs.getDouble("version");
256                                    int size = rs.getInt("size_");
257    
258                                    addFileVersion(
259                                            groupId, companyId, userId, userName, folderId, name, title,
260                                            version, size, fileEntryId);
261                            }
262                    }
263                    finally {
264                            DataAccess.cleanUp(con, ps);
265                    }
266            }
267    
268            private static Log _log = LogFactoryUtil.getLog(
269                    UpgradeDocumentLibrary.class);
270    
271    }