Java > Open Source Codes > org > infoglue > cmsinstaller > UpgradeManager


1 /* ===============================================================================
2  *
3  * Part of the InfoGlue Content Management Platform (www.infoglue.org)
4  *
5  * ===============================================================================
6  *
7  *Copyright (C)
8  *
9  * This program is free software; you can redistribute it and/or modify it under
10  * the terms of the GNU General Public License version 2, as published by the
11  * Free Software Foundation. See the file LICENSE.html for more information.
12  *
13  * This program is distributed in the hope that it will be useful, but WITHOUT
14  * ANY WARRANTY, including the implied warranty of MERCHANTABILITY or FITNESS
15  * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License along with
18  * this program; if not, write to the Free Software Foundation, Inc. / 59 Temple
19  * Place, Suite 330 / Boston, MA 02111-1307 / USA.
20  *
21  * ===============================================================================
22  */
23
24 package org.infoglue.cmsinstaller;
25
26 import java.sql.*;
27 import java.util.Iterator ;
28 import java.util.List ;
29 import java.io.*;
30
31 import org.dom4j.Document;
32 import org.dom4j.DocumentHelper;
33 import org.dom4j.Element;
34 import org.dom4j.Node;
35 import org.dom4j.XPath;
36 import org.infoglue.cms.util.dom.DOMBuilder;
37
38 /**
39  * This is the class doing updates of earlier installed databases and other resources when upgrading from earlier
40  * versions of infoglue.
41  */
42
43
44 public class UpgradeManager
45 {
46     private boolean convertData = false;
47     private String oldCharset = null;
48     boolean is1_0 = false;
49     
50     public UpgradeManager(boolean convertData, String oldCharset)
51     {
52         this.convertData = convertData;
53         this.oldCharset = oldCharset;
54     }
55
56     /**
57      * This method is the entry point for upgrading from 1.0 / 1.1 to 1.2
58      */
59     
60     public void upgradeToCurrentVersion(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
61     {
62         //upgradeTo1_2(databaseCommander, hostName, portNumber, databaseName, userName, password);
63 String url = databaseCommander.getUrl(hostName, portNumber, databaseName);
64         Connection conn = databaseCommander.getConnection(url, userName, password);
65
66         String previousVersion = getPreviousVersion(conn, databaseCommander.getDatabaseVendor());
67         Logger.logInfo("Previous version of database:" + previousVersion);
68
69         if(previousVersion.equals("1.2"))
70         {
71             upgradeTo1_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
72             upgradeTo1_3_2(databaseCommander, hostName, portNumber, databaseName, userName, password);
73             upgradeTo2_0(databaseCommander, hostName, portNumber, databaseName, userName, password);
74             upgradeTo2_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
75             upgradeTo2_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
76         }
77         else if(previousVersion.equals("1.3"))
78         {
79             upgradeTo1_3_2(databaseCommander, hostName, portNumber, databaseName, userName, password);
80             upgradeTo2_0(databaseCommander, hostName, portNumber, databaseName, userName, password);
81             upgradeTo2_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
82             upgradeTo2_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
83         }
84         else if(previousVersion.equals("1.3.2"))
85         {
86             upgradeTo2_0(databaseCommander, hostName, portNumber, databaseName, userName, password);
87             upgradeTo2_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
88             upgradeTo2_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
89         }
90         else if(previousVersion.equals("2.0"))
91         {
92             upgradeTo2_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
93             upgradeTo2_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
94         }
95         else if(previousVersion.equals("2.1"))
96         {
97             upgradeTo2_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
98         }
99     }
100     
101     /**
102      * This method is the entry point for upgrading from 1.0 / 1.1 to 1.2
103      */
104     
105     public void upgradeTo1_2(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
106     {
107         Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
108         
109         String url = databaseCommander.getUrl(hostName, portNumber, databaseName);
110         String unicodeUrl = databaseCommander.getUnicodeUrl(hostName, portNumber, databaseName);
111         Connection conn = databaseCommander.getConnection(url, userName, password);
112         Connection conn2 = databaseCommander.getConnection(unicodeUrl, userName, password);
113         
114         try
115         {
116             upgradeRepositoryTable(conn);
117             upgradeLanguageTable(conn);
118             
119             if(is1_0 && this.convertData)
120                 upgradeTableContents(conn, conn2, this.oldCharset);
121             
122             upgradeSiteNodeVersionTable(conn);
123             upgradeContentTypeDefinitionTable(conn);
124             upgradeContentTable(conn);
125             
126             updateSystemContentTypesDefinitions(conn);
127             updateSiteNodeTypesDefinitions(conn);
128             updateBaseTasks(conn);
129             
130             conn.close();
131         }
132         catch(SQLException ex)
133         {
134             System.err.println("SQLException: " + ex.getMessage());
135         }
136
137     }
138
139
140     /**
141      * This method is the entry point for upgrading from 1.2 to 1.3
142      */
143     
144     public void upgradeTo1_3(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
145     {
146         Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
147         
148         String url = databaseCommander.getUrl(hostName, portNumber, databaseName);
149         String unicodeUrl = databaseCommander.getUnicodeUrl(hostName, portNumber, databaseName);
150         Connection conn = databaseCommander.getConnection(url, userName, password);
151         Connection conn2 = databaseCommander.getConnection(unicodeUrl, userName, password);
152         
153         try
154         {
155             updateSystemContentTypesDefinitions(conn);
156                         
157             databaseCommander.upgradeTo1_3(conn);
158             
159             if(this.convertData)
160                 upgradeTableContents(conn, conn2, this.oldCharset);
161
162             conn.close();
163         }
164         catch(SQLException ex)
165         {
166             System.err.println("SQLException: " + ex.getMessage());
167         }
168
169     }
170
171     /**
172      * This method is the entry point for upgrading from 1.2 to 1.3
173      */
174     
175     public void upgradeTo1_3_2(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
176     {
177         Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
178         
179         String url = databaseCommander.getUrl(hostName, portNumber, databaseName);
180         Connection conn = databaseCommander.getConnection(url, userName, password);
181         
182         try
183         {
184             databaseCommander.upgradeTo1_3_2(conn);
185         
186             conn.close();
187         }
188         catch(SQLException ex)
189         {
190             System.err.println("SQLException: " + ex.getMessage());
191         }
192
193     }
194
195     /**
196      * This method is the entry point for upgrading from 1.2 to 1.3
197      */
198     
199     public void upgradeTo2_0(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
200     {
201         Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
202         
203         String url = databaseCommander.getUrl(hostName, portNumber, databaseName);
204         Connection conn = databaseCommander.getConnection(url, userName, password);
205         
206         try
207         {
208             databaseCommander.upgradeTo2_0(conn);
209         
210             conn.close();
211         }
212         catch(SQLException ex)
213         {
214             System.err.println("SQLException: " + ex.getMessage());
215         }
216
217     }
218
219     /**
220      * This method is the entry point for upgrading from 2.0 to 2.1
221      */
222     
223     public void upgradeTo2_1(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
224     {
225         Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
226         
227         String url = databaseCommander.getUrl(hostName, portNumber, databaseName);
228         Connection conn = databaseCommander.getConnection(url, userName, password);
229         
230         try
231         {
232             databaseCommander.upgradeTo2_1(conn);
233         
234             conn.close();
235         }
236         catch(SQLException ex)
237         {
238             System.err.println("SQLException: " + ex.getMessage());
239         }
240
241     }
242
243     /**
244      * This method is the entry point for upgrading from 2.0 to 2.1
245      */
246     
247     public void upgradeTo2_3(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
248     {
249         Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
250         
251         String url = databaseCommander.getUrl(hostName, portNumber, databaseName);
252         Connection conn = databaseCommander.getConnection(url, userName, password);
253         
254         try
255         {
256             databaseCommander.upgradeTo2_3(conn);
257         
258             conn.close();
259         }
260         catch(SQLException ex)
261         {
262             System.err.println("SQLException: " + ex.getMessage());
263         }
264
265     }
266
267     /*
268      * This method upgrades the repository table.
269      */
270      
271     private void upgradeRepositoryTable(Connection conn) throws Exception
272     {
273         String sql = "SELECT * FROM cmRepository";
274         PreparedStatement pstmt = conn.prepareStatement(sql);
275         ResultSet rs = pstmt.executeQuery();
276         
277
278         if(rs.getMetaData().getColumnCount() == 3) //This must be the 1.0 as it should be 4 in 1.1
279 {
280             this.is1_0 = true;
281         }
282
283         pstmt.close();
284     
285         if(this.is1_0)
286         {
287             Logger.logInfo("Upgrading repository table from the 1.0-schema to 1.1-schema.");
288                 
289             sql = "ALTER TABLE cmRepository ADD dnsName TEXT NULL;";
290             pstmt = conn.prepareStatement(sql);
291             pstmt.execute();
292             pstmt.close();
293     
294             sql = "UPDATE cmRepository SET dnsName='';";
295             pstmt = conn.prepareStatement(sql);
296             pstmt.execute();
297             pstmt.close();
298     
299             sql = "ALTER TABLE cmRepository MODIFY dnsName TEXT NOT NULL;";
300             pstmt = conn.prepareStatement(sql);
301             pstmt.execute();
302             pstmt.close();
303         }
304     }
305     
306     
307     /*
308      * This method upgrades the language table.
309      */
310      
311     private void upgradeLanguageTable(Connection conn) throws Exception
312     {
313         String sql = "SELECT * FROM cmLanguage";
314         PreparedStatement pstmt = conn.prepareStatement(sql);
315         ResultSet rs = pstmt.executeQuery();
316         
317         if(rs.getMetaData().getColumnCount() == 3) //This must be the 1.0 as it should be 4 in 1.1
318 {
319             this.is1_0 = true;
320         }
321
322         pstmt.close();
323     
324         if(this.is1_0)
325         {
326             Logger.logInfo("Upgrading language table from the 1.0-schema to 1.1-schema.");
327                 
328             sql = "ALTER TABLE cmLanguage ADD charset TEXT NULL;";
329             pstmt = conn.prepareStatement(sql);
330             pstmt.execute();
331             pstmt.close();
332     
333             sql = "UPDATE cmLanguage SET charset='ISO-8859-1';";
334             pstmt = conn.prepareStatement(sql);
335             pstmt.execute();
336             pstmt.close();
337     
338             sql = "ALTER TABLE cmLanguage MODIFY charset TEXT NOT NULL;";
339             pstmt = conn.prepareStatement(sql);
340             pstmt.execute();
341             pstmt.close();
342         }
343     }
344     
345     
346     /*
347      * This method upgrades the SiteNodeVersion table.
348      */
349      
350     private void upgradeSiteNodeVersionTable(Connection conn) throws Exception
351     {
352         String sql = "SELECT * FROM cmSiteNodeVersion";
353         PreparedStatement pstmt = conn.prepareStatement(sql);
354         ResultSet rs = pstmt.executeQuery();
355         
356         boolean needsUpgrade = false;
357         
358         try
359         {
360             int columnNumber = rs.findColumn("isProtected");
361         }
362         catch(Exception e)
363         {
364             Logger.logInfo("The table cmSiteNodeVersion was not up2date.");
365             needsUpgrade = true;
366         }
367         
368         pstmt.close();
369     
370         if(needsUpgrade)
371         {
372             Logger.logInfo("Upgrading cmSiteNodeVersion table from the 1.1-schema to 1.2-schema.");
373                 
374             sql = "ALTER TABLE cmSiteNodeVersion ADD isProtected tinyint(4) NULL;";
375             pstmt = conn.prepareStatement(sql);
376             pstmt.execute();
377             pstmt.close();
378     
379             sql = "UPDATE cmSiteNodeVersion SET isProtected=2;";
380             pstmt = conn.prepareStatement(sql);
381             pstmt.execute();
382             pstmt.close();
383     
384             sql = "ALTER TABLE cmSiteNodeVersion MODIFY isProtected tinyint(4) NOT NULL;";
385             pstmt = conn.prepareStatement(sql);
386             pstmt.execute();
387             pstmt.close();
388             
389             
390             sql = "ALTER TABLE cmSiteNodeVersion ADD disablePageCache tinyint(4) NULL;";
391             pstmt = conn.prepareStatement(sql);
392             pstmt.execute();
393             pstmt.close();
394     
395             sql = "UPDATE cmSiteNodeVersion SET disablePageCache=2;";
396             pstmt = conn.prepareStatement(sql);
397             pstmt.execute();
398             pstmt.close();
399     
400             sql = "ALTER TABLE cmSiteNodeVersion MODIFY disablePageCache tinyint(4) NOT NULL;";
401             pstmt = conn.prepareStatement(sql);
402             pstmt.execute();
403             pstmt.close();
404             
405             
406             sql = "ALTER TABLE cmSiteNodeVersion ADD disableEditOnSight tinyint(4) NULL;";
407             pstmt = conn.prepareStatement(sql);
408             pstmt.execute();
409             pstmt.close();
410
411             sql = "UPDATE cmSiteNodeVersion SET disableEditOnSight=2;";
412             pstmt = conn.prepareStatement(sql);
413             pstmt.execute();
414             pstmt.close();
415
416             sql = "ALTER TABLE cmSiteNodeVersion MODIFY disableEditOnSight tinyint(4) NOT NULL;";
417             pstmt = conn.prepareStatement(sql);
418             pstmt.execute();
419             pstmt.close();
420             
421             
422             sql = "ALTER TABLE cmSiteNodeVersion ADD contentType TEXT NULL;";
423             pstmt = conn.prepareStatement(sql);
424             pstmt.execute();
425             pstmt.close();
426     
427             sql = "UPDATE cmSiteNodeVersion SET contentType='text/html';";
428             pstmt = conn.prepareStatement(sql);
429             pstmt.execute();
430             pstmt.close();
431     
432             sql = "ALTER TABLE cmSiteNodeVersion MODIFY contentType TEXT NOT NULL;";
433             pstmt = conn.prepareStatement(sql);
434             pstmt.execute();
435             pstmt.close();
436         }
437     }
438     
439     
440     /*
441      * This method upgrades the Content table.
442      */
443      
444     private void upgradeContentTable(Connection conn) throws Exception
445     {
446         String sql = "SELECT * FROM cmContent";
447         PreparedStatement pstmt = conn.prepareStatement(sql);
448         ResultSet rs = pstmt.executeQuery();
449         
450         boolean needsUpgrade = false;
451         
452         try
453         {
454             int columnNumber = rs.findColumn("isProtected");
455         }
456         catch(Exception e)
457         {
458             Logger.logInfo("The table cmContent was not up2date.");
459             needsUpgrade = true;
460         }
461         
462         pstmt.close();
463     
464         if(needsUpgrade)
465         {
466             Logger.logInfo("Upgrading cmContent table from the 1.1-schema to 1.2-schema.");
467                 
468             sql = "ALTER TABLE cmContent ADD isProtected tinyint(4) NULL;";
469             pstmt = conn.prepareStatement(sql);
470             pstmt.execute();
471             pstmt.close();
472     
473             sql = "UPDATE cmContent SET isProtected=0;";
474             pstmt = conn.prepareStatement(sql);
475             pstmt.execute();
476             pstmt.close();
477     
478             sql = "ALTER TABLE cmContent MODIFY isProtected tinyint(4) NOT NULL;";
479             pstmt = conn.prepareStatement(sql);
480             pstmt.execute();
481             pstmt.close();
482             
483         }
484     }
485     
486     
487     /*
488      * This method upgrades the ContentTypeDefinition table.
489      */
490      
491     private void upgradeContentTypeDefinitionTable(Connection conn) throws Exception
492     {
493         String sql = "SELECT * FROM cmContentTypeDefinition";
494         PreparedStatement pstmt = conn.prepareStatement(sql);
495         ResultSet rs = pstmt.executeQuery();
496         
497         boolean needsUpgrade = false;
498         
499         try
500         {
501             int columnNumber = rs.findColumn("type");
502         }
503         catch(Exception e)
504         {
505             Logger.logInfo("The table cmContentTypeDefinition was not up2date.");
506             needsUpgrade = true;
507         }
508         
509         pstmt.close();
510     
511         if(needsUpgrade)
512         {
513             Logger.logInfo("Upgrading cmContentTypeDefinition table from the 1.1-schema to 1.2-schema.");
514                 
515             sql = "ALTER TABLE cmContentTypeDefinition ADD type tinyint(4) NULL;";
516             pstmt = conn.prepareStatement(sql);
517             pstmt.execute();
518             pstmt.close();
519     
520             sql = "UPDATE cmContentTypeDefinition SET type=0;";
521             pstmt = conn.prepareStatement(sql);
522             pstmt.execute();
523             pstmt.close();
524     
525             sql = "ALTER TABLE cmContentTypeDefinition MODIFY type tinyint(4) NOT NULL;";
526             pstmt = conn.prepareStatement(sql);
527             pstmt.execute();
528             pstmt.close();
529             
530         }
531     }
532     
533     
534     /*
535      * This method goes through all tables of value and converts the data in them to UTF-8-encoded data.
536      * The method assumes that the given encoding is the current dataformat.
537      */
538
539     private void upgradeTableContents(Connection conn, Connection conn2, String oldCharset) throws Exception
540     {
541         convertTableContents(conn, conn2, "cmAccessRight", new String []{"accessRightId"}, new String []{"roleName"}, oldCharset, 0, true);
542         convertTableContents(conn, conn2, "cmAvailableServiceBinding", new String []{"availableServiceBindingId"}, new String []{"name", "description", "visualizationAction"}, oldCharset, 0, true);
543         convertTableContents(conn, conn2, "cmContent", new String []{"contentId"}, new String []{"name"}, oldCharset, 0, true);
544         convertTableContents(conn, conn2, "cmContentTypeDefinition", new String []{"contentTypeDefinitionId"}, new String []{"name", "schemaValue"}, oldCharset, 0, true);
545         convertTableContents(conn, conn2, "cmContentVersion", new String []{"contentVersionId"}, new String []{"versionValue", "versionComment"}, oldCharset, 0, true);
546         //convertTableContents(conn, conn2, "cmDigitalAsset", "digitalAssetId", new String[]{"assetKey", "assetFileName", "assetFilePath", "assetContentType"}, oldCharset, 0, true);
547 convertTableContents(conn, conn2, "cmEvent", new String []{"eventId"}, new String []{"name", "description", "entityClass"}, oldCharset, 0, true);
548         //convertTableContents(conn, conn2, "cmFunction", "functionId", new String[]{"name", "description", "address"}, oldCharset, 0, true);
549 convertTableContents(conn, conn2, "cmLanguage", new String []{"languageId"}, new String []{"name", "languageCode", "charset"}, oldCharset, 0, true);
550         convertTableContents(conn, conn2, "cmPublication", new String []{"publicationId"}, new String []{"name", "description"}, oldCharset, 0, true);
551         convertTableContents(conn, conn2, "cmPublicationDetail", new String []{"publicationDetailId"}, new String []{"name", "description", "entityClass"}, oldCharset, 0, true);
552         convertTableContents(conn, conn2, "cmQualifyer", new String []{"qualifyerId"}, new String []{"name", "value"}, oldCharset, 0, true);
553         convertTableContents(conn, conn2, "cmRepository", new String []{"repositoryId"}, new String []{"name", "description", "dnsName"}, oldCharset, 0, true);
554         convertTableContents(conn, conn2, "cmRole", new String []{"roleName"}, new String []{"roleName", "description"}, oldCharset, 1, false);
555         convertTableContents(conn, conn2, "cmServiceBinding", new String []{"serviceBindingId"}, new String []{"name", "path"}, oldCharset, 0, true);
556         convertTableContents(conn, conn2, "cmServiceDefinition", new String []{"serviceDefinitionId"}, new String []{"name", "description", "className"}, oldCharset, 0, true);
557         convertTableContents(conn, conn2, "cmSiteNode", new String []{"siteNodeId"}, new String []{"name"}, oldCharset, 0, true);
558         convertTableContents(conn, conn2, "cmSiteNodeTypeDefinition", new String []{"siteNodeTypeDefinitionId"}, new String []{"name", "description", "invokerClassName"}, oldCharset, 0, true);
559         convertTableContents(conn, conn2, "cmSiteNodeVersion", new String []{"siteNodeVersionId"}, new String []{"versionComment"}, oldCharset, 0, true);
560         convertTableContents(conn, conn2, "cmSystemUser", new String []{"userName"}, new String []{"userName", "password", "firstName", "lastName", "email"}, oldCharset, 1, false);
561         convertTableContents(conn, conn2, "cmRoleContentTypeDefinition", new String []{"roleContentTypeDefinitionId"}, new String []{"roleName"}, oldCharset, 0, true);
562         convertTableContents(conn, conn2, "cmRoleProperties", new String []{"rolePropertiesId"}, new String []{"roleName", "value"}, oldCharset, 0, true);
563         convertTableContents(conn, conn2, "cmSystemUserRole", new String []{"userName", "roleName"}, new String []{"userName", "roleName"}, oldCharset, 1, false);
564         convertTableContents(conn, conn2, "cmUserProperties", new String []{"userPropertiesId"}, new String []{"userName", "value"}, oldCharset, 0, true);
565         
566         //convertTableContents(conn, conn2, "cmTransactionHistory", "transactionHistoryId", new String[]{"name", "transactionObjectName", "systemUserName"}, oldCharset);
567 }
568     
569     private void convertTableContents(Connection conn, Connection conn2, String table, String primaryKey[], String [] columns, String oldCharset, int primaryKeyTypeId, boolean useDriverEncoding) throws Exception
570     {
571         String sql = "SELECT * FROM " + table;
572         Logger.logInfo("upgrading content in table " + table + " to UTF-8....");
573         PreparedStatement pstmt = conn.prepareStatement(sql);
574         ResultSet rs = pstmt.executeQuery();
575         
576         String unconvertedValue = "";
577         while(rs.next())
578         {
579             String [] id = new String [primaryKey.length];
580             
581             for(int i=0; i<primaryKey.length; i++)
582             {
583                 if(primaryKeyTypeId == 0)
584                     id[i] = "" + rs.getInt(primaryKey[i]);
585                 if(primaryKeyTypeId == 1)
586                     id[i] = "" + rs.getString(primaryKey[i]) + "";
587             }
588
589             //Logger.logInfo("id:" + id);
590
591             for(int i=0; i < columns.length; i++)
592             {
593                 String columnName = columns[i];
594
595                 unconvertedValue = rs.getString(columnName);
596                 
597                 Logger.logInfo("unconvertedValue:" + unconvertedValue);
598                 String convertedValue = unconvertedValue;
599                 //String convertedValue = new String(unconvertedValue.getBytes("ISO-8859-1"), "UTF-8");
600 //Logger.logInfo("convertedValue:" + convertedValue);
601
602                 //String convertedValue = new String(unconvertedValue.getBytes("ISO-8859-1"));
603
604                 
605                 if(useDriverEncoding)
606                 {
607                     String whereSQL = "";
608                     for(int j=0; j<primaryKey.length; j++)
609                     {
610                         String primaryKeyName = primaryKey[j];
611                         if(whereSQL.length() > 0)
612                             whereSQL += " AND ";
613                         
614                         //whereSQL += primaryKeyName + " = ?";
615 whereSQL += primaryKeyName + " = " + id[j];
616                     }
617                     
618                     //String updateSQL = "UPDATE " + table + " SET " + columnName + " = ? WHERE " + primaryKey + " = " + id;
619 String updateSQL = "UPDATE " + table + " SET " + columnName + " = ? WHERE " + whereSQL;
620                     Logger.logInfo("updateSQL:" + updateSQL);
621                     PreparedStatement rowpstmt = conn2.prepareStatement(updateSQL);
622                     rowpstmt.setString(1, convertedValue);
623                     
624                     /*
625                     for(int idIndex=0; idIndex<id.length; idIndex++)
626                     {
627                         String primaryKeyId = id[idIndex];
628                         rowpstmt.setString(idIndex + 2, primaryKeyId);
629                     }
630                     */
631                     rowpstmt.executeUpdate();
632                     rowpstmt.close();
633                 }
634                 else
635                 {
636                     String whereSQL = "";
637                     for(int j=0; j<primaryKey.length; j++)
638                     {
639                         String primaryKeyName = primaryKey[j];
640                         if(whereSQL.length() > 0)
641                             whereSQL += " AND ";
642                         
643                         whereSQL += primaryKeyName + " = ?";
644                         //whereSQL += primaryKeyName + " = " + id[j];
645 }
646                     
647                     convertedValue = new String (convertedValue.getBytes("UTF-8"), "ISO-8859-1");
648                     String updateSQL = "UPDATE " + table + " SET " + columnName + " = ? WHERE " + whereSQL;
649                     Logger.logInfo("updateSQL:" + updateSQL);
650                     PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
651                     rowpstmt.setString(1, convertedValue);
652                     
653                     for(int idIndex=0; idIndex<id.length; idIndex++)
654                     {
655                         String primaryKeyId = id[idIndex];
656                         rowpstmt.setString(idIndex + 2, primaryKeyId);
657                         Logger.logInfo("index value:" + primaryKeyId);
658                     }
659                     
660                     rowpstmt.executeUpdate();
661                     rowpstmt.close();
662                 }
663                     /*
664                 String convertedValue = new String(unconvertedValue.getBytes("UTF-8"));
665                 String updateSQL = "UPDATE " + table + " SET " + columnName + " = ? WHERE " + primaryKey + " = " + id;
666                 //Logger.logInfo("updateSQL:" + updateSQL);
667                 PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
668                 rowpstmt.setString(1, convertedValue);
669                 */
670                 
671                 //rowpstmt.executeUpdate();
672 //rowpstmt.close();
673 }
674         }
675         
676         pstmt.close();
677     }
678     
679     
680     private void updateSystemContentTypesDefinitions(Connection conn) throws Exception
681     {
682         String sql = "SELECT * FROM cmContentTypeDefinition";
683         Logger.logInfo("upgrading contenttype definitions...");
684         PreparedStatement pstmt = conn.prepareStatement(sql);
685         ResultSet rs = pstmt.executeQuery();
686         
687         boolean hasTaskDefinition = false;
688         
689         while(rs.next())
690         {
691             int id = rs.getInt("contentTypeDefinitionId");
692             String name = rs.getString("name");
693             String schemaValue = rs.getString("schemaValue");
694             
695             if(name.indexOf("Meta info") > -1)
696             {
697                 if(schemaValue.indexOf("ComponentStructure") == -1)
698                 {
699                     DOMBuilder domBuilder = new DOMBuilder();
700                     Document schemaValueDocument = domBuilder.getDocument(schemaValue);
701                     //System.out.println(schemaValueDocument.getRootElement().asXML());
702
703                     Element allElements = getElementsElement(schemaValueDocument);
704                     //System.out.println("allElements:" + allElements.getName());
705
706                     String elementString = "<xs:schema elementFormDefault=\"qualified\" attributeFormDefault=\"unqualified\" version=\"2.1\" xmlns:xi=\"http://www.w3.org/2001/XInclude\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\"><xs:element name=\"ComponentStructure\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined67\" label=\"ComponentStructure\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined38\" label=\"ComponentStructure\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined73\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element></xs:schema>";
707                     Document newElementDocument = domBuilder.getDocument(elementString);
708                     
709                     List children = newElementDocument.getRootElement().elements("element");
710                     Iterator childrenIterator = children.iterator();
711                     while(childrenIterator.hasNext())
712                     {
713                         Element child = (Element)childrenIterator.next();
714                         allElements.add(child.createCopy());
715                     }
716                     
717                     String newValue = schemaValueDocument.asXML();
718                     
719                     String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
720                     PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
721                     rowpstmt.setString(1, newValue);
722                     rowpstmt.executeUpdate();
723                     rowpstmt.close();
724                 }
725             }
726             else if(name.equalsIgnoreCase("Article"))
727             {
728                 if(schemaValue.indexOf("RelatedAreas") == -1)
729                 {
730                     DOMBuilder domBuilder = new DOMBuilder();
731                     Document schemaValueDocument = domBuilder.getDocument(schemaValue);
732
733                     Element allElements = getElementsElement(schemaValueDocument);
734                     //System.out.println("allElements:" + allElements.getName());
735
736                     String elementString = "<xs:schema elementFormDefault=\"qualified\" attributeFormDefault=\"unqualified\" version=\"2.1\" xmlns:xi=\"http://www.w3.org/2001/XInclude\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\"><xs:element name=\"RelatedAreas\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined93\" label=\"Related areas\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined30\" label=\"Points out related areas on the site\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined83\" label=\"normaltextfield\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableContentRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableContentRelationEditor\" label=\"false\"></value></values></param><param id=\"enableStructureRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableStructureRelationEditor\" label=\"true\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element></xs:schema>";
737                     
738                     Document newElementDocument = domBuilder.getDocument(elementString);
739                     
740                     List children = newElementDocument.getRootElement().elements("element");
741                     Iterator childrenIterator = children.iterator();
742                     while(childrenIterator.hasNext())
743                     {
744                         Element child = (Element)childrenIterator.next();
745                         allElements.add(child.createCopy());
746                     }
747                     
748                     String newValue = schemaValueDocument.asXML();
749                     
750                     String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
751                     PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
752                     rowpstmt.setString(1, newValue);
753                     rowpstmt.executeUpdate();
754                     rowpstmt.close();
755                 }
756             }
757             else if(name.equalsIgnoreCase("HTMLTemplate"))
758             {
759                 Logger.logInfo("Found HTMLTemplate...");
760                 if(schemaValue.indexOf("ComponentProperties") == -1)
761                 {
762                     DOMBuilder domBuilder = new DOMBuilder();
763                     Document schemaValueDocument = domBuilder.getDocument(schemaValue);
764
765                     Element allElements = getElementsElement(schemaValueDocument);
766                     //System.out.println("allElements:" + allElements.getName());
767
768                     String elementString = "<xs:schema elementFormDefault=\"qualified\" attributeFormDefault=\"unqualified\" version=\"2.1\" xmlns:xi=\"http://www.w3.org/2001/XInclude\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\"><xs:element name=\"ComponentProperties\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined89\" label=\"ComponentProperties\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined40\" label=\"ComponentProperties\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined93\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element><xs:element name=\"GroupName\" type=\"select\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined89\" label=\"Group Name\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined94\" label=\"The name of the group the component should be in\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined63\" label=\"normaltextfield\"></value></values></param><param id=\"values\" inputTypeId=\"1\"><values><value id=\"Basic Pages\" label=\"Basic Pages\"></value><value id=\"Single Content\" label=\"Single Content\"></value><value id=\"Content Iterators\" label=\"Content Iterators\"></value><value id=\"Navigation\" label=\"Navigation\"></value><value id=\"Layout\" label=\"Layout\"></value><value id=\"Templates\" label=\"Templates\"></value><value id=\"Other\" label=\"Other\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element></xs:schema>";
769                     
770                     Document newElementDocument = domBuilder.getDocument(elementString);
771                     
772                     List children = newElementDocument.getRootElement().elements("element");
773                     Iterator childrenIterator = children.iterator();
774                     while(childrenIterator.hasNext())
775                     {
776                         Element child = (Element)childrenIterator.next();
777                         allElements.add(child.createCopy());
778                     }
779                     
780                     String newValue = schemaValueDocument.asXML();
781                     Logger.logInfo("newValue: " + newValue);
782
783                     String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
784                     PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
785                     rowpstmt.setString(1, newValue);
786                     rowpstmt.executeUpdate();
787                     rowpstmt.close();
788                 }
789             }
790             else if(name.equalsIgnoreCase("HTMLFormular"))
791             {
792                 Logger.logInfo("Found HTMLFormular...");
793                 if(schemaValue.indexOf("<value id=\"enableWYSIWYG\" label=\"true\">") > -1)
794                 {
795                     String newValue = schemaValue.replaceAll("<value id=\"enableWYSIWYG\" label=\"true\">", "<value id=\"enableWYSIWYG\" label=\"false\">");
796                     Logger.logInfo("newValue: " + newValue);
797
798                     String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
799                     PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
800                     rowpstmt.setString(1, newValue);
801                     rowpstmt.executeUpdate();
802                     rowpstmt.close();
803                 }
804             }
805             else if(name.equalsIgnoreCase("TaskDefinition"))
806             {
807                 hasTaskDefinition = true;
808             }
809
810             /*
811             if(name.indexOf("Meta info") > -1)
812             {
813                 if(schemaValue.indexOf("ComponentStructure") == -1)
814                 {
815                     int lastElementIndex = schemaValue.lastIndexOf("</xs:all></xs:complexType></xs:element>");
816                     if(lastElementIndex > -1)
817                     {
818                         String newValue = schemaValue.substring(0, lastElementIndex) +
819                         "<xs:element name=\"ComponentStructure\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined67\" label=\"ComponentStructure\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined38\" label=\"ComponentStructure\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined73\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element>"
820                          + schemaValue.substring(lastElementIndex);
821     
822                         String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
823                         PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
824                         rowpstmt.setString(1, newValue);
825                         rowpstmt.executeUpdate();
826                         rowpstmt.close();
827                     }
828                 }
829             }
830             else if(name.equalsIgnoreCase("Article"))
831             {
832                 if(schemaValue.indexOf("RelatedAreas") == -1)
833                 {
834                     int lastElementIndex = schemaValue.lastIndexOf("</xs:all></xs:complexType></xs:element>");
835                     if(lastElementIndex > -1)
836                     {
837                         String newValue = schemaValue.substring(0, lastElementIndex) +
838                         "<xs:element name=\"RelatedAreas\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined93\" label=\"Related areas\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined30\" label=\"Points out related areas on the site\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined83\" label=\"normaltextfield\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableContentRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableContentRelationEditor\" label=\"false\"></value></values></param><param id=\"enableStructureRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableStructureRelationEditor\" label=\"true\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element>"
839                          + schemaValue.substring(lastElementIndex);
840     
841                         String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
842                         PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
843                         rowpstmt.setString(1, newValue);
844                         rowpstmt.executeUpdate();
845                         rowpstmt.close();
846                     }
847                 }
848             }
849             else if(name.equalsIgnoreCase("HTMLTemplate"))
850             {
851                 Logger.logInfo("Found HTMLTemplate...");
852                 if(schemaValue.indexOf("ComponentProperties") == -1)
853                 {
854                     Logger.logInfo("ComponentProperties was not found in: " + schemaValue);
855
856                     int lastElementIndex = schemaValue.lastIndexOf("</xs:all></xs:complexType></xs:element>");
857                     Logger.logInfo("lastElementIndex: " + lastElementIndex);
858                     if(lastElementIndex > -1)
859                     {
860                         String newValue = schemaValue.substring(0, lastElementIndex) +
861                         "<xs:element name=\"ComponentProperties\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined89\" label=\"ComponentProperties\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined40\" label=\"ComponentProperties\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined93\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element><xs:element name=\"GroupName\" type=\"select\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined89\" label=\"Group Name\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined94\" label=\"The name of the group the component should be in\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined63\" label=\"normaltextfield\"></value></values></param><param id=\"values\" inputTypeId=\"1\"><values><value id=\"Basic Pages\" label=\"Basic Pages\"></value><value id=\"Single Content\" label=\"Single Content\"></value><value id=\"Content Iterators\" label=\"Content Iterators\"></value><value id=\"Navigation\" label=\"Navigation\"></value><value id=\"Layout\" label=\"Layout\"></value><value id=\"Templates\" label=\"Templates\"></value><value id=\"Other\" label=\"Other\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element>"
862                          + schemaValue.substring(lastElementIndex);
863                         
864                         Logger.logInfo("newValue: " + newValue);
865
866                         String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
867                         PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
868                         rowpstmt.setString(1, newValue);
869                         rowpstmt.executeUpdate();
870                         rowpstmt.close();
871                     }
872                 }
873             }
874             else if(name.equalsIgnoreCase("TaskDefinition"))
875             {
876                 hasTaskDefinition = true;
877             }
878             */
879         }
880         
881             
882         if(!hasTaskDefinition)
883         {
884             String newValue = "<xs:schema attributeFormDefault=\"unqualified\" elementFormDefault=\"qualified\" version=\"2.0\" xmlns:xi=\"http://www.w3.org/2001/XInclude\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\"><xs:simpleType name=\"textarea\"><xs:restriction base=\"xs:string\"><xs:maxLength value=\"100\"></xs:maxLength></xs:restriction></xs:simpleType><xs:simpleType name=\"radiobutton\"><xs:restriction base=\"xs:string\"><xs:maxLength value=\"100\"></xs:maxLength></xs:restriction></xs:simpleType><xs:simpleType name=\"checkbox\"><xs:restriction base=\"xs:string\"><xs:maxLength value=\"100\"></xs:maxLength></xs:restriction></xs:simpleType><xs:simpleType name=\"select\"><xs:restriction base=\"xs:string\"><xs:maxLength value=\"100\"></xs:maxLength></xs:restriction></xs:simpleType><xs:simpleType name=\"textfield\"><xs:restriction base=\"xs:string\"><xs:maxLength value=\"100\"></xs:maxLength></xs:restriction></xs:simpleType><xs:complexType name=\"Content\"><xs:all><xs:element name=\"Attributes\"><xs:complexType><xs:all><xs:element name=\"UserInputHTML\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined64\" label=\"UserInputHTML\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined98\" label=\"UserInputHTML\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined26\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element><xs:element name=\"ScriptCode\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined22\" label=\"ScriptCode\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined90\" label=\"The code\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined99\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"600\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element><xs:element name=\"UserOutputHTML\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined63\" label=\"UserOutputHTML\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined22\" label=\"UserOutputHTML\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined28\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element></xs:all></xs:complexType></xs:element></xs:all></xs:complexType></xs:schema>";
885             String insertSQL = "INSERT INTO cmContentTypeDefinition (schemaValue, name) VALUES (?,?);";
886             PreparedStatement rowpstmt = conn.prepareStatement(insertSQL);
887             rowpstmt.setString(1, newValue);
888             rowpstmt.setString(2, "TaskDefinition");
889             rowpstmt.executeUpdate();
890             rowpstmt.close();
891         }
892         
893         pstmt.close();
894     }
895     
896     
897     private Element getElementsElement(Document schemaValueDocument)
898     {
899         Element elementsElement = null;
900         
901         Element schema = schemaValueDocument.getRootElement();
902         //System.out.println("schema:" + schema.getName());
903
904         List complexTypes = schemaValueDocument.getRootElement().elements("complexType");
905         //System.out.println("complexTypes:" + complexTypes.size());
906 Iterator complexTypesIterator = complexTypes.iterator();
907         while(complexTypesIterator.hasNext())
908         {
909             Element complexType = (Element)complexTypesIterator.next();
910             //System.out.println("complexType:" + complexType.getName());
911
912             List allList = complexType.elements("all");
913             //System.out.println("allList:" + allList.size());
914 Iterator allIterator = allList.iterator();
915             while(allIterator.hasNext())
916             {
917                 Element all = (Element)allIterator.next();
918                 //System.out.println("all:" + all.getName());
919
920                 List elementList = all.elements("element");
921                 //System.out.println("elementList:" + elementList.size());
922 Iterator elementListIterator = elementList.iterator();
923                 while(elementListIterator.hasNext())
924                 {
925                     Element element = (Element)elementListIterator.next();
926                     //System.out.println("element:" + element.getName() + ":" + element.attributeValue("name"));
927
928                     List complexType2 = element.elements("complexType");
929                     //System.out.println("complexType2:" + complexType2.size());
930 Iterator complexType2Iterator = complexType2.iterator();
931                     while(complexType2Iterator.hasNext())
932                     {
933                         Element complexType2Element = (Element)complexType2Iterator.next();
934                         //System.out.println("complexType2Element:" + complexType2Element.getName());
935
936                         List all2 = complexType2Element.elements("all");
937                         //System.out.println("all2:" + all2.size());
938 Iterator all2Iterator = all2.iterator();
939                         while(all2Iterator.hasNext())
940                         {
941                             Element all2Element = (Element)all2Iterator.next();
942                             //System.out.println("all2Element:" + all2Element.getName());
943
944                             elementsElement = all2Element;
945                             /*
946                             List element2 = all2Element.elements("element");
947                             System.out.println("element2:" + element2.size());
948                             Iterator element2Iterator = element2.iterator();
949                             while(element2Iterator.hasNext())
950                             {
951                                 Element element2Element = (Element)element2Iterator.next();
952                                 System.out.println("element2Element:" + element2Element.getName());
953                             }
954                             */
955                         }
956                     }
957                 }
958             }
959         }
960         
961         return elementsElement;
962     }
963     
964     private int getMaxContentId(Connection conn) throws Exception
965     {
966         String sql = "SELECT max(contentId) as contentId FROM cmContent";
967         PreparedStatement pstmt = conn.prepareStatement(sql);
968         ResultSet rs = pstmt.executeQuery();
969
970         while(rs.next())
971         {
972             return rs.getInt("contentId");
973         }
974         
975         return -1;
976     }
977     
978     private void updateBaseTasks(Connection conn) throws Exception
979     {
980         String sql = "SELECT * FROM cmContent WHERE name = 'Export Contents Task' OR name = 'Import Contents Task'";
981         Logger.logInfo("upgrading system tasks...");
982         PreparedStatement pstmt = conn.prepareStatement(sql);
983         ResultSet rs = pstmt.executeQuery();
984     
985         boolean hasSystemTasks = false;
986         
987         while(rs.next())
988         {
989             hasSystemTasks = true;
990         }
991     
992         if(!hasSystemTasks)
993         {
994             String command = "INSERT INTO cmContent (name, publishDateTime, expireDateTime, contentTypeDefinitionId, parentContentId, systemUserId, repositoryId, isBranch) VALUES ('Export Contents Task','2003-08-18 13:03:00','2013-08-18 13:03:00','10','30','1','3','0');";
995             //Logger.logInfo("Command: " + command);
996 PreparedStatement insertPstmt = conn.prepareStatement(command);
997             insertPstmt.execute();
998             insertPstmt.close();
999             
1000            int exportContentId = getMaxContentId(conn);
1001            
1002            command = "INSERT INTO cmContent (name, publishDateTime, expireDateTime, contentTypeDefinitionId, parentContentId, systemUserId, repositoryId, isBranch) VALUES ('Import Contents Task','2003-08-18 13:03:00','2013-08-18 13:03:00','10','30','1','3','0');";
1003            //Logger.logInfo("Command: " + command);
1004 insertPstmt = conn.prepareStatement(command);
1005            insertPstmt.execute();
1006            insertPstmt.close();
1007            
1008            int importContentId = getMaxContentId(conn);
1009
1010            
1011            Logger.logInfo("upgrading system tasks...");
1012            try
1013            {
1014                FileInputStream fis = new FileInputStream("infoglue_system_tasks_1_2.sql");
1015                StringBuffer sb = new StringBuffer ();
1016                int c;
1017                while((c = fis.read()) != -1)
1018                {
1019                    char character = (char)c;
1020                    sb.append(character);
1021                }
1022                String script = sb.toString();
1023                script = script.replaceAll("exportContentId", "" + exportContentId);
1024                script = script.replaceAll("importContentId", "" + importContentId);
1025                Logger.logInfo("script:" + script);
1026                
1027                String [] commands = script.split("#endquery");
1028                Logger.logInfo("Parsed " + commands.length + " commands from script");
1029                
1030                for(int i=0; i<commands.length; i++)
1031                {
1032                    command = commands[i];
1033                    
1034                    insertPstmt = conn.prepareStatement(command.trim());
1035                    insertPstmt.execute();
1036                    insertPstmt.close();
1037                }
1038                    
1039            }
1040            catch(Exception e)
1041            {
1042                Logger.logInfo("Error: " + e);
1043            }
1044        }
1045    
1046        pstmt.close();
1047    }
1048    
1049    private void updateSiteNodeTypesDefinitions(Connection conn) throws Exception
1050    {
1051        String sql = "SELECT * FROM cmSiteNodeTypeDefinition";
1052        Logger.logInfo("upgrading cmSiteNodeTypeDefinition...");
1053        PreparedStatement pstmt = conn.prepareStatement(sql);
1054        ResultSet rs = pstmt.executeQuery();
1055        
1056        boolean hasComponentPageDefinition = false;
1057        
1058        while(rs.next())
1059        {
1060            int id = rs.getInt("siteNodeTypeDefinitionId");
1061            String invokerClassName = rs.getString("invokerClassName");
1062
1063            if(invokerClassName.equalsIgnoreCase("org.infoglue.cms.invokers.ComponentBasedHTMLPageInvoker"))
1064            {
1065                hasComponentPageDefinition = true;
1066            }
1067        }
1068        
1069        if(!hasComponentPageDefinition)
1070        {
1071            String insertSQL = "INSERT INTO cmSiteNodeTypeDefinition (invokerClassName, name, description) VALUES (?,?,?);";
1072            PreparedStatement rowpstmt = conn.prepareStatement(insertSQL);
1073            rowpstmt.setString(1, "org.infoglue.cms.invokers.ComponentBasedHTMLPageInvoker");
1074            rowpstmt.setString(2, "ComponentPage");
1075            rowpstmt.setString(3, "The new component type page");
1076            rowpstmt.executeUpdate();
1077            rowpstmt.close();
1078        }
1079        
1080        pstmt.close();
1081    }
1082    
1083    public String getPreviousVersion(Connection conn, String databaseName)
1084    {
1085        String previousVersion = "2.3";
1086
1087        try
1088        {
1089            String sql = "SELECT * FROM cmSiteNodeVersion";
1090            if(databaseName.equalsIgnoreCase("Oracle") || databaseName.equalsIgnoreCase("DB2"))
1091                sql = "SELECT * FROM cmSiNoVer";
1092            
1093            PreparedStatement pstmt = conn.prepareStatement(sql);
1094            ResultSet rs = pstmt.executeQuery();
1095            rs.next();
1096            
1097            rs.getString("disableLanguages"); //If this throws exception then it's older than 2.3
1098 return "2.3";
1099        }
1100        catch(Exception e)
1101        {
1102            e.printStackTrace();
1103            previousVersion = "2.1";
1104        }
1105
1106        try
1107        {
1108            String sql = "SELECT * FROM cmSiteNodeVersion";
1109            if(databaseName.equalsIgnoreCase("Oracle") || databaseName.equalsIgnoreCase("DB2"))
1110                sql = "SELECT * FROM cmSiNoVer";
1111            
1112            PreparedStatement pstmt = conn.prepareStatement(sql);
1113            ResultSet rs = pstmt.executeQuery();
1114            rs.next();
1115            
1116            rs.getString("pageCacheKey"); //If this throws exception then it's older than 2.1
1117 return "2.1";
1118        }
1119        catch(Exception e)
1120        {
1121            e.printStackTrace();
1122            previousVersion = "2.0";
1123        }
1124
1125        try
1126        {
1127            String sql = "SELECT * FROM cmCategory";
1128            PreparedStatement pstmt = conn.prepareStatement(sql);
1129            ResultSet rs = pstmt.executeQuery();
1130            
1131            if(rs.getMetaData().getColumnCount() > 0) //If this is above 0 then the table is there and the version is 1.3.
1132 return "2.0";
1133        }
1134        catch(Exception e)
1135        {
1136            e.printStackTrace();
1137            previousVersion = "1.3.2";
1138        }
1139
1140        try
1141        {
1142            String sql = "SELECT * FROM cmRepositoryLanguage";
1143            PreparedStatement pstmt = conn.prepareStatement(sql);
1144            ResultSet rs = pstmt.executeQuery();
1145            rs.next();
1146            
1147            rs.getString("sortOrder"); //If this throws exception then it's 1.3
1148 return "1.3.2";
1149        }
1150        catch(Exception e)
1151        {
1152            e.printStackTrace();
1153            previousVersion = "1.3";
1154        }
1155
1156        try
1157        {
1158            String sql = "SELECT * FROM cmConsequence";
1159            PreparedStatement pstmt = conn.prepareStatement(sql);
1160            ResultSet rs = pstmt.executeQuery();
1161            
1162            if(rs.getMetaData().getColumnCount() > 0) //If this is above 0 then the table is there and the version is 1.2.
1163 {
1164                return previousVersion = "1.2";
1165            }
1166        }
1167        catch(Exception e)
1168        {
1169            e.printStackTrace();
1170            previousVersion = "1.0";
1171        }
1172
1173        return previousVersion;
1174    }
1175}

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags