Java API By Example, From Geeks To Geeks.

1 /* 2   Copyright (C) 2003 Know Gate S.L. All rights reserved. 3                       C/Oña, 107 1º2 28050 Madrid (Spain) 4 5   Redistribution and use in source and binary forms, with or without 6   modification, are permitted provided that the following conditions 7   are met: 8 9   1. Redistributions of source code must retain the above copyright 10      notice, this list of conditions and the following disclaimer. 11 12   2. The end-user documentation included with the redistribution, 13      if any, must include the following acknowledgment: 14      "This product includes software parts from hipergate 15      (http://www.hipergate.org/)." 16      Alternately, this acknowledgment may appear in the software itself, 17      if and wherever such third-party acknowledgments normally appear. 18 19   3. The name hipergate must not be used to endorse or promote products 20      derived from this software without prior written permission. 21      Products derived from this software may not be called hipergate, 22      nor may hipergate appear in their name, without prior written 23      permission. 24 25   This library is distributed in the hope that it will be useful, 26   but WITHOUT ANY WARRANTY; without even the implied warranty of 27   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 28 29   You should have received a copy of hipergate License with this code; 30   if not, visit http://www.hipergate.org or mail to info@hipergate.org 31 */ 32 33 package com.knowgate.hipergate.datamodel; 34 35 import java.io.FileNotFoundException ; 36 import java.io.IOException ; 37 import java.io.InputStream ; 38 import java.io.FileInputStream ; 39 import java.io.FileOutputStream ; 40 import java.io.FileWriter ; 41 import java.io.InputStreamReader ; 42 import java.io.StringBufferInputStream ; 43 import java.io.ByteArrayInputStream ; 44 import java.io.ByteArrayOutputStream ; 45 46 import java.sql.Connection ; 47 import java.sql.DatabaseMetaData ; 48 import java.sql.SQLException ; 49 import java.sql.DriverManager ; 50 import java.sql.Connection ; 51 import java.sql.CallableStatement ; 52 import java.sql.Statement ; 53 import java.sql.PreparedStatement ; 54 import java.sql.ResultSet ; 55 import java.sql.ResultSetMetaData ; 56 import java.sql.Types ; 57 58 import java.util.Properties ; 59 import java.util.LinkedList ; 60 import java.util.ListIterator ; 61 62 import bsh.Interpreter; 63 import bsh.TargetError; 64 import bsh.EvalError; 65 66 import com.knowgate.debug.DebugFile; 67 import com.knowgate.misc.Gadgets; 68 69 /** 70  * <p>hipergate Data Model Manager</p> 71  * <p>This class is used for programatically creating the full underlying 72  * data model for hipergate Java packages.</p> 73  * <p>It may be used as an alternative method to database dumps for initial data loading, 74  * or, also, as a tool for porting the data model to a new DBMS in a structured way.</p> 75  * @author Sergio Montoro ten 76  * @version 3.0 77  */ 78 79 public class ModelManager { 80 81   private static final String VERSION = "2.1.1"; 82 83   private static final int BULK_PROCEDURES = 1; 84   private static final int BULK_STATEMENTS = 2; 85   private static final int BULK_BATCH = 3; 86   private static final int BULK_PLSQL = 4; 87   private static final int FILE_STATEMENTS = 5; 88 89   private static final int DBMS_MYSQL = 1; 90   private static final int DBMS_POSTGRESQL = 2; 91   private static final int DBMS_MSSQL = 3; 92   private static final int DBMS_ORACLE = 5; 93   private static final int DBMS_DB2 = 6; 94 95   private static final String CURRENT_TIMESTAMP = "CURRENT_TIMESTAMP"; 96   private static final int CURRENT_TIMESTAMP_LEN = 17; 97 98   private static final String DATETIME = "DATETIME"; 99   private static final int DATETIME_LEN = 8; 100 101   private static final String LONGVARBINARY = "LONGVARBINARY"; 102   private static final int LONGVARBINARY_LEN = 13; 103 104   private static final String LONGVARCHAR = "LONGVARCHAR"; 105   private static final int LONGVARCHAR_LEN = 11; 106 107   private static final String FLOAT_NUMBER = "FLOAT"; 108   private static final int FLOAT_NUMBER_LEN = 5; 109 110   private static final String NUMBER_6 = "SMALLINT"; 111   private static final int NUMBER_6_LEN = 8; 112 113   private static final String NUMBER_11 = "INTEGER"; 114   private static final int NUMBER_11_LEN = 7; 115 116   private static final String CHARACTER_VARYING = "VARCHAR"; 117   private static final int CHARACTER_VARYING_LEN = 7; 118 119   private static final String SERIAL = "SERIAL"; 120   private static final int SERIAL_LEN = 6; 121 122   private static final String CHAR_LENGTH = "LENGTH("; 123   private static final int CHAR_LENGTH_LEN = 7; 124 125   private static final String BLOB = "BLOB"; 126   private static final int BLOB_LEN = 4; 127 128   private static final String CLOB = "CLOB"; 129   private static final int CLOB_LEN = 4; 130 131   private static final String CurrentTimeStamp[] = { null, "CURRENT_TIMESTAMP", "CURRENT_TIMESTAMP", "GETDATE()", null, "SYSDATE" }; 132   private static final String DateTime[] = { null, "TIMESTAMP", "TIMESTAMP", "DATETIME", null, "DATE" }; 133   private static final String LongVarChar[] = { null, "MEDIUMTEXT", "TEXT", "NTEXT", null, "LONG" }; 134   private static final String LongVarBinary[] = { null, "MEDIUMBLOB", "BYTEA", "IMAGE", null, "LONG RAW" }; 135   private static final String CharLength[] = { null, "CHAR_LENGTH(", "char_length(", "LEN(", null, "LENGTH(" }; 136   private static final String Serial[] = { null, "INTEGER NOT NULL AUTO_INCREMENT", "SERIAL", "INTEGER IDENTITY", null, "NUMBER(11)" }; 137   private static final String VarChar[] = { null, "VARCHAR", "VARCHAR", "NVARCHAR", null, "VARCHAR2" }; 138   private static final String Blob[] = { null, "MEDIUMBLOB", "BYTEA", "IMAGE", null, "BLOB" }; 139   private static final String Clob[] = { null, "MEDIUMTEXT", "TEXT", "NTEXT", null, "CLOB" }; 140 141   private boolean bStopOnError; 142   private Connection oConn; 143   private String sDbms; 144   private String sSchema; 145   private int iDbms; 146   private int iErrors; 147   private StringBuffer oStrLog; 148   private String sEncoding; 149   private boolean bASCII; 150 151   // --------------------------------------------------------------------------- 152 153   private class Constraint { 154     public String constraintname; 155     public String tablename; 156 157     public Constraint(String sConstraintName, String sTableName) { 158       constraintname = sConstraintName; 159       tablename = sTableName; 160     } 161   } 162 163   // --------------------------------------------------------------------------- 164 165   public ModelManager() { 166 167     if (DebugFile.trace) { 168       DebugFile.writeln("hipergate ModelManager build " + VERSION); 169       DebugFile.envinfo(); 170     } 171 172     iDbms = 0; 173     sDbms = null; 174     oConn = null; 175     oStrLog = null; 176     bStopOnError = false; 177     sEncoding = "UTF-8"; 178     bASCII = false; 179   } 180 181   // --------------------------------------------------------------------------- 182 183   public String getEncoding () { 184     return sEncoding; 185   } 186 187   public void setEncoding (String sCharset) { 188     sEncoding = sCharset; 189   } 190 191   /** 192    * <p>Set whether or not create() and drop() methods should stop on error</p> 193    * @param bStop <b>true</b>=stop on error, <b>false</b>=don not stop 194    */ 195   public void stopOnError(boolean bStop) { 196     bStopOnError = bStop; 197   } 198 199   // --------------------------------------------------------------------------- 200 201   /** 202    * <p>Get whether or not create() and drop() methods will stop on error</p> 203    * @return bStop <b>true</b>=stop on error, <b>false</b>=don not stop 204    */ 205   public boolean stopOnError() { 206     return bStopOnError; 207   } 208 209   // --------------------------------------------------------------------------- 210 211   /** 212    * <p>Connect to database</p> 213    * Connection autocommit is set to ON. 214    * @param sDriver JDBC driver class name 215    * @param sUrl Database URL 216    * @param sUsr Database User 217    * @param sPwd Database Password 218    * @throws SQLException 219    * @throws ClassNotFoundException If class for JDBC driver is not found 220    * @throws IllegalStateException If already connected to database 221    * @throws UnsupportedOperationException If DBMS is not recognized, 222    * currently only Oracle, Microsoft SQL Server and PostgreSQL are recognized. 223    */ 224   public void connect(String sDriver, String sUrl, String sSch, String sUsr, String sPwd) 225     throws SQLException , ClassNotFoundException , IllegalStateException , 226            UnsupportedOperationException { 227 228     if (DebugFile.trace) { 229      DebugFile.writeln("Begin ModelManager.connect(" + sDriver + "," + sUrl + ", ...)"); 230      DebugFile.incIdent(); 231     } 232 233     if (null!=oConn) 234       throw new IllegalStateException ("already connected to database"); 235 236     Connection oNewConn; 237     Class oDriver = Class.forName(sDriver); 238 239     oNewConn = DriverManager.getConnection(sUrl, sUsr, sPwd); 240     oNewConn.setAutoCommit(true); 241 242     setConnection(oNewConn); 243 244     sSchema = sSch; 245 246     if (DebugFile.trace) { 247       DebugFile.decIdent(); 248       DebugFile.writeln("End ModelManager.connect()"); 249     } 250 251   } // connect 252 253   // --------------------------------------------------------------------------- 254 255   /** 256    * <p>Disconnect from database</p> 257    * @throws SQLException 258    */ 259   public void disconnect() throws SQLException { 260 261     if (DebugFile.trace) { 262      DebugFile.writeln("Begin ModelManager.disconnect()"); 263      DebugFile.incIdent(); 264     } 265 266     if (null!=oConn) { 267       if (!oConn.isClosed()) { 268         oConn.close(); 269         sDbms = null; 270       } // fi (!isClosed()) 271 } // fi (oConn) 272 273     if (DebugFile.trace) { 274       DebugFile.decIdent(); 275       DebugFile.writeln("End ModelManager.disconnect()"); 276     } 277   } // disconnect 278 279   // --------------------------------------------------------------------------- 280 281   /** 282    * <p>Assign an external conenction to ModelManager</p> 283    * Use this method when ModelManager must not connect itself to database but 284    * reuse an already existing connection. 285    * @param oJDBCConn Database Connection 286    * @throws SQLException 287    * @throws UnsupportedOperationException If DBMS is not recognized 288    * @throws NullPointerException if oJDBCConn is <b>null</b> 289    */ 290   public void setConnection(Connection oJDBCConn) 291       throws SQLException ,UnsupportedOperationException ,NullPointerException { 292 293     if (DebugFile.trace) { 294      DebugFile.writeln("Begin ModelManager.setConnection([Connection])"); 295      DebugFile.incIdent(); 296     } 297 298     if (null==oJDBCConn) throw new NullPointerException ("Connection parameter may not be null"); 299 300     oConn = oJDBCConn; 301 302     DatabaseMetaData oMDat = oConn.getMetaData(); 303     String sDatabaseProductName = oMDat.getDatabaseProductName(); 304 305     if (sDatabaseProductName.equals("Microsoft SQL Server")) { 306       sDbms = "mssql"; 307       iDbms = DBMS_MSSQL; 308     } 309     else if (sDatabaseProductName.equals("PostgreSQL")) { 310       sDbms = "postgresql"; 311       iDbms = DBMS_POSTGRESQL; 312     } 313     else if (sDatabaseProductName.equals("Oracle")) { 314       sDbms = "oracle"; 315       iDbms = DBMS_ORACLE; 316     } 317     else if (sDatabaseProductName.startsWith("DB2")) { 318       sDbms = "db2"; 319       iDbms = DBMS_DB2; 320     } 321     else if (sDatabaseProductName.startsWith("MySQL")) { 322       sDbms = "mysql"; 323       iDbms = DBMS_MYSQL; 324     } 325     else { 326       sDbms = oMDat.getDatabaseProductName().toLowerCase(); 327       iDbms = 0; 328     } 329     oMDat = null; 330 331     if (0==iDbms) { 332       oConn.close(); 333       oConn = null; 334       throw new UnsupportedOperationException ("DataBase Management System not supported"); 335     } 336 337     oStrLog = new StringBuffer (); 338 339     if (DebugFile.trace) { 340       DebugFile.decIdent(); 341       DebugFile.writeln("End ModelManager.setConnection()"); 342     } 343   } // setConnection() 344 345   // --------------------------------------------------------------------------- 346 347   /** 348    * Clear internal operation log 349    */ 350   public void clear() { 351     oStrLog.setLength(0); 352   } 353 354   // --------------------------------------------------------------------------- 355 356   /** 357    * Get reference to opened database connection 358    */ 359   public Connection getConnection() { 360     return oConn; 361   } 362 363   // --------------------------------------------------------------------------- 364 365   /** 366    * Print internal operation log to a String 367    */ 368   public String report() { 369     String sRep; 370 371     if (null!=oStrLog) 372       sRep = oStrLog.toString(); 373     else 374       sRep = ""; 375 376     return sRep; 377   } // report 378 379   // --------------------------------------------------------------------------- 380 381   /** 382    * <p>Translate SQL statement for a particular DBMS</p> 383    * @param sSQL SQL to be translated 384    * @throws NullPointerException if sSQL is <b>null</b> 385    * @return SQL statement translated for the active DBMS 386    */ 387   private String translate(String sSQL) 388     throws NullPointerException { 389 390     if (DebugFile.trace) { 391      DebugFile.writeln("Begin ModelManager.translate(" + sSQL + ")"); 392      DebugFile.incIdent(); 393     } 394 395     if (null==sSQL) { 396       if (DebugFile.trace) DebugFile.decIdent(); 397       throw new NullPointerException ("Sentence to translate may not be null"); 398     } 399 400     final int iLen = sSQL.length(); 401 402     if (iLen<=0) { 403       if (DebugFile.trace) { 404         DebugFile.decIdent(); 405         DebugFile.writeln("End ModelManager.translate()"); 406       } 407       return ""; 408     } 409     else { 410       int iPos, iOff; 411       boolean bMatch; 412 413       StringBuffer oTrn = new StringBuffer (iLen); 414 415       for (int p=0; p<iLen; p++) { 416 417         bMatch = true; 418         for (iPos=0, iOff=p; iPos<CURRENT_TIMESTAMP_LEN && iOff<iLen && bMatch; iOff++, iPos++) 419           bMatch = (sSQL.charAt(iOff) == CURRENT_TIMESTAMP.charAt(iPos)); 420 421         if (bMatch) { 422 423           oTrn.append(CurrentTimeStamp[iDbms]); 424           p += CURRENT_TIMESTAMP_LEN-1; 425         } 426         else { 427 428           bMatch = true; 429           for (iPos=0, iOff=p; iPos<DATETIME_LEN && iOff<iLen && bMatch; iOff++, iPos++) 430             bMatch = (sSQL.charAt(iOff) == DATETIME.charAt(iPos)); 431 432           if (bMatch) { 433 434             oTrn.append(DateTime[iDbms]); 435             p += DATETIME_LEN-1; 436           } 437           else { 438 439             if (p>0) 440               bMatch = sSQL.charAt(p-1)!='N'; 441             else 442               bMatch = true; 443 444             for (iPos=0, iOff=p; iPos<CHARACTER_VARYING_LEN && iOff<iLen && bMatch; iOff++, iPos++) 445               bMatch &= (sSQL.charAt(iOff) == CHARACTER_VARYING.charAt(iPos)); 446 447             if (bMatch) { 448 449               oTrn.append(VarChar[iDbms]); 450               p += CHARACTER_VARYING_LEN-1; 451             } 452             else { 453 454               bMatch = true; 455               for (iPos=0, iOff=p; iPos<LONGVARCHAR_LEN && iOff<iLen && bMatch; iOff++, iPos++) 456                 bMatch = (sSQL.charAt(iOff) == LONGVARCHAR.charAt(iPos)); 457 458               if (bMatch) { 459 460                 oTrn.append(LongVarChar[iDbms]); 461                 p += LONGVARCHAR_LEN-1; 462               } 463               else { 464 465                 bMatch = true; 466                 for (iPos=0, iOff=p; iPos<LONGVARBINARY_LEN && iOff<iLen && bMatch; iOff++, iPos++) 467                   bMatch = sSQL.charAt(iOff) == LONGVARBINARY.charAt(iPos); 468 469                 if (bMatch) { 470 471                   oTrn.append(LongVarBinary[iDbms]); 472                   p += LONGVARBINARY_LEN-1; 473                 } 474                 else { 475 476                   bMatch = true; 477                   for (iPos=0, iOff=p; iPos<CHAR_LENGTH_LEN && iOff<iLen && bMatch; iOff++, iPos++) 478                     bMatch = sSQL.charAt(iOff) == CHAR_LENGTH.charAt(iPos); 479 480                   if (bMatch) { 481 482                     oTrn.append(CharLength[iDbms]); 483                     p += CHAR_LENGTH_LEN-1; 484                   } 485 486                   else { 487                     bMatch = true; 488                     for (iPos = 0, iOff = p; 489                          iPos < SERIAL_LEN && iOff < iLen && bMatch; iOff++, iPos++) 490                       bMatch = sSQL.charAt(iOff) == SERIAL.charAt(iPos); 491 492                     if (bMatch) { 493 494                       oTrn.append(Serial[iDbms]); 495                       p += SERIAL_LEN - 1; 496                     } 497 498                     else { 499 500                       if (DBMS_ORACLE == iDbms) { 501 502                         bMatch = true; 503                         for (iPos = 0, iOff = p; 504                              iPos < NUMBER_6_LEN && iOff < iLen && bMatch; iOff++, 505                              iPos++) 506                           bMatch = sSQL.charAt(iOff) == NUMBER_6.charAt(iPos); 507 508                         if (bMatch) { 509 510                           oTrn.append("NUMBER(6,0)"); 511                           p += NUMBER_6_LEN - 1; 512                         } 513                         else { 514 515                           bMatch = true; 516                           for (iPos = 0, iOff = p; 517                                iPos < NUMBER_11_LEN && iOff < iLen && bMatch; 518                                iOff++, iPos++) 519                             bMatch = sSQL.charAt(iOff) == NUMBER_11.charAt(iPos); 520 521                           if (bMatch) { 522 523                             oTrn.append("NUMBER(11,0)"); 524                             p += NUMBER_11_LEN - 1; 525                           } 526                           else { 527 528                             bMatch = true; 529                             for (iPos = 0, iOff = p; 530                                  iPos < FLOAT_NUMBER_LEN && iOff < iLen && bMatch; 531                                  iOff++, iPos++) 532                               bMatch = sSQL.charAt(iOff) == 533                                   FLOAT_NUMBER.charAt(iPos); 534 535                             if (bMatch) { 536 537                               oTrn.append("NUMBER"); 538                               p += FLOAT_NUMBER_LEN - 1; 539                             } 540                             else { 541                               oTrn.append(sSQL.charAt(p)); 542                             } 543                           } // // fi (NUMBER(11)) 544 545                         } // fi (NUMBER(6)) 546 547                       } // fi (DBMS_ORACLE) 548 549                       else { 550 551                         bMatch = true; 552                         for (iPos = 0, iOff = p; 553                              iPos < BLOB_LEN && iOff < iLen && bMatch; iOff++, 554                              iPos++) 555                           bMatch = sSQL.charAt(iOff) == BLOB.charAt(iPos); 556 557                         if (bMatch) { 558                           oTrn.append(Blob[iDbms]); 559                           p += BLOB_LEN - 1; 560                         } 561                         else { 562                           bMatch = true; 563                           for (iPos = 0, iOff = p; 564                                iPos < CLOB_LEN && iOff < iLen && bMatch; iOff++, 565                                iPos++) 566                             bMatch = sSQL.charAt(iOff) == CLOB.charAt(iPos); 567 568                           if (bMatch) { 569                             oTrn.append(Clob[iDbms]); 570                             p += CLOB_LEN - 1; 571                           } 572                           else 573                             oTrn.append(sSQL.charAt(p)); 574                         } 575                       } 576                     } 577                   } 578                 } // fi (no matching translation found) 579 } 580             } 581           } 582         } 583       } // next 584 585       if (DebugFile.trace) { 586         DebugFile.decIdent(); 587         DebugFile.writeln("End ModelManager.translate() :\n" + oTrn.toString() + "\n"); 588       } 589 590       if (bASCII) { 591         int iTrn = oTrn.length(); 592         StringBuffer oAsc = new StringBuffer (iTrn); 593         char[] cTrn = new char[1]; 594         for (int i=0; i<iTrn; i++) { 595           oTrn.getChars(i, i + 1, cTrn, 0); 596           if ((int)cTrn[0] <= 255) 597             oAsc.append(cTrn[0]); 598           else 599             oAsc.append('?'); 600         } // next 601 return oAsc.toString().replace((char)13, (char)32); 602       } 603       else { 604         return oTrn.toString().replace((char)13, (char)32); 605       } // fi (iLen) 606 } 607 608   } // translate 609 610   // --------------------------------------------------------------------------- 611 612   private String [] split(StringBuffer oBuffer, char cDelimiter, String sGo) { 613 614     // Fast String spliter routine specially tuned for SQL sentence batches 615 616     if (DebugFile.trace) { 617      DebugFile.writeln("Begin ModelManager.split(...)"); 618      DebugFile.incIdent(); 619     } 620 621     final int iLen = oBuffer.length(); 622     int iGo; 623 624     if (null!=sGo) 625       iGo = sGo.length(); 626     else 627       iGo = 0; 628 629     char cAt; 630 631     boolean bActive = true; 632     int iSize = 0; 633 634     for (int c=0; c<iLen; c++) { 635       cAt = oBuffer.charAt(c); 636 637       if (cAt==39) bActive = !bActive; 638 639       if (cAt==cDelimiter && bActive) { 640         if (null==sGo) 641           iSize++; 642         else if (c>=iGo) 643           if (oBuffer.substring(c-iGo,c).equalsIgnoreCase(sGo)) 644             iSize++; 645       } 646     } // next 647 648     String aArray[] = new String [iSize]; 649     int iMark = 0; 650     int iTail; 651     int iIndex = 0; 652 653     for (int c=0; c<iLen; c++) { 654       cAt = oBuffer.charAt(c); 655 656       if (cAt==39) bActive = !bActive; 657 658       if ((cAt==cDelimiter && bActive) && (null==sGo || (c>=iGo && oBuffer.substring(c-iGo,c).equalsIgnoreCase(sGo)))) { 659 660         for ( iTail=c-1; iTail>0; iTail--) 661           if (oBuffer.charAt(iTail)>32 && null==sGo) 662             break; 663           else if (null!=sGo) { 664             iTail -= iGo; 665             break; 666           } 667 668         aArray[iIndex++] = oBuffer.substring(iMark,iTail+1); 669 670         for ( iMark=c+1; iMark<iLen; iMark++) 671           if (oBuffer.charAt(iMark)>32) break; 672 673       } // fi (found delimiter) 674 } // next 675 676     if (iIndex<iSize-1 && iMark<iLen-1) aArray[iIndex] = oBuffer.substring(iMark); 677 678     if (DebugFile.trace) { 679       DebugFile.decIdent(); 680       DebugFile.writeln("End ModelManager.split()"); 681     } 682 683     return aArray; 684   } // split 685 686   // --------------------------------------------------------------------------- 687 688   public void executeSQLScript (String sScriptSource, String sDelimiter) 689     throws SQLException ,InterruptedException ,IllegalArgumentException { 690 691     if (DebugFile.trace) { 692       DebugFile.writeln("Begin ModelManager.executeSQLScript()"); 693       DebugFile.incIdent(); 694     } 695 696     String sSQL; 697     String aStatements[]; 698 699     if (sDelimiter.equals("GO;")) 700       aStatements = split (new StringBuffer (sScriptSource), ';', "GO"); 701     else 702       aStatements = split (new StringBuffer (sScriptSource), sDelimiter.charAt(0), null); 703 704     int iStatements = aStatements.length; 705 706     Statement oStmt = oConn.createStatement(); 707 708     try { oStmt.setQueryTimeout(10); } catch (SQLException ignore) { } 709 710     for (int s = 0; s < iStatements; s++) { 711       sSQL = aStatements[s]; 712       if (sSQL.length() > 0) { 713         oStrLog.append(sSQL + "\n\\\n"); 714 715         try { 716           oStmt.execute (sSQL); 717         } 718         catch (SQLException sqle) { 719           iErrors++; 720           oStrLog.append("SQLException: " + sqle.getMessage() + "\n"); 721 722           if (bStopOnError) { 723             try { if (null!=oStmt) oStmt.close(); } catch (SQLException ignore) { } 724             throw new InterruptedException (sqle.getMessage() + " " + sSQL); 725           } 726         } 727       } // fi (sSQL) 728 } // next 729 oStmt.close(); 730     oStmt = null; 731 732     if (DebugFile.trace) { 733       DebugFile.decIdent(); 734       DebugFile.writeln("End ModelManager.executeSQLScript()"); 735     } 736   } 737 738   // --------------------------------------------------------------------------- 739 740   private StringBuffer getSQLBuffer(String sResourcePath, int iBatchType) 741     throws FileNotFoundException , IOException ,SQLException ,InterruptedException { 742 743       if (DebugFile.trace) { 744         DebugFile.writeln("Begin ModelManager.getSQLBuffer(" + sResourcePath + ")"); 745         DebugFile.incIdent(); 746       } 747 748       int iReaded, iSkip; 749       final int iBufferSize = 16384; 750       char Buffer[] = new char[iBufferSize]; 751       InputStream oInStrm; 752       InputStreamReader oStrm; 753       StringBuffer oBuffer = new StringBuffer (); 754 755       iErrors = 0; 756 757       if (FILE_STATEMENTS == iBatchType) { 758         if (DebugFile.trace) DebugFile.writeln("new FileInputStream("+sResourcePath+")"); 759         oStrLog.append("Open file " + sResourcePath + " as " + sEncoding + "\n"); 760         oInStrm = new FileInputStream (sResourcePath); 761       } 762       else { 763         if (DebugFile.trace) DebugFile.writeln(getClass().getName()+".getResourceAsStream("+sResourcePath+")"); 764         oStrLog.append("Get resource " + sResourcePath + " as " + sEncoding + "\n"); 765         oInStrm = getClass().getResourceAsStream(sResourcePath); 766       } 767 768       if (null == oInStrm) { 769         iErrors = 1; 770         oStrLog.append("FileNotFoundException "+sResourcePath); 771         if (DebugFile.trace) DebugFile.decIdent(); 772         throw new FileNotFoundException ("executeBulk() " + sResourcePath); 773       } 774 775       if (DebugFile.trace) DebugFile.writeln("new InputStreamReader([InputStream], "+sEncoding); 776 777       oStrm = new InputStreamReader (oInStrm, sEncoding); 778 779       try { 780         while (true) { 781           iReaded = oStrm.read(Buffer,0,iBufferSize); 782 783           if (-1==iReaded) break; 784 785           // Skip FF FE character mark for Unidode files 786 iSkip = ((int)Buffer[0]==65279 || (int)Buffer[0]==65534 ? 1 : 0); 787 788           oBuffer.append(Buffer, iSkip, iReaded-iSkip); 789 790         } 791         oStrm.close(); 792         oInStrm.close(); 793       } 794       catch (IOException ioe) { 795         iErrors = 1; 796         oStrLog.append("IOException "+ioe.getMessage()); 797         if (DebugFile.trace) DebugFile.decIdent(); 798         throw new IOException (ioe.getMessage()); 799       } 800 801       if (DebugFile.trace) { 802         DebugFile.decIdent(); 803         DebugFile.writeln("End ModelManager.getSQLStream()"); 804       } 805 806       return oBuffer; 807   } 808 809   // --------------------------------------------------------------------------- 810 811   private int executeBulk(StringBuffer oBuffer, String sResourcePath, int iBatchType) 812     throws FileNotFoundException , IOException , SQLException ,InterruptedException { 813 814     if (DebugFile.trace) { 815       DebugFile.writeln("Begin ModelManager.executeBulk(" + sResourcePath + "," + 816                         String.valueOf(iBatchType) + ")"); 817       DebugFile.incIdent(); 818     } 819 820     int iStatements; 821     CallableStatement oCall = null; 822     Statement oStmt = null; 823     String sSQL = null; 824     String aStatements[]; 825 826     iErrors = 0; 827 828     if (sResourcePath.endsWith(".ddl") || sResourcePath.endsWith(".DDL")) { 829       aStatements = split(oBuffer, ';', "GO"); 830     } 831     else { 832       aStatements = split(oBuffer, ';', null); 833     } 834 835     iStatements = aStatements.length; 836 837       switch (iBatchType) { 838         case BULK_PROCEDURES: 839           for (int s = 0; s < iStatements; s++) { 840             sSQL = aStatements[s]; 841             if (sSQL.length() > 0) { 842               oStrLog.append(sSQL + "\n"); 843               try { 844                 oCall = oConn.prepareCall(sSQL); 845                 oCall.execute(); 846                 oCall.close(); 847                 oCall = null; 848               } 849               catch (SQLException sqle) { 850                 iErrors++; 851                 oStrLog.append("SQLException: " + sqle.getMessage() + "\n"); 852                 try { if (null!=oCall) oCall.close(); } catch (SQLException ignore) { } 853                 if (bStopOnError) throw new java.lang.InterruptedException (); 854               } 855             } // fi (sSQL) 856 } // next 857 break; 858 859         case BULK_STATEMENTS: 860         case FILE_STATEMENTS: 861         case BULK_BATCH: 862 863           oStmt = oConn.createStatement(); 864           for (int s = 0; s < iStatements; s++) { 865 866             try { 867               sSQL = translate(aStatements[s]); 868             } 869             catch (NullPointerException npe) { 870               oStrLog.append (" NullPointerException: at " + sResourcePath + " statement " + String.valueOf(s) + "\n"); 871               sSQL = ""; 872             } 873 874             if (sSQL.length() > 0) { 875               oStrLog.append(sSQL + "\n\\\n"); 876               try { 877                 oStmt.executeUpdate(sSQL); 878               } 879               catch (SQLException sqle) { 880                 iErrors++; 881                 oStrLog.append ("SQLException: " + sqle.getMessage() + "\n"); 882 883                 if (bStopOnError) { 884                   try { if (null!=oStmt) oStmt.close(); } catch (SQLException ignore) { } 885                   throw new java.lang.InterruptedException (); 886                 } 887               } 888             } // fi (sSQL) 889 } // next 890 oStmt.close(); 891           oStmt = null; 892           break; 893 894         case BULK_PLSQL: 895           oStmt = oConn.createStatement(); 896           for (int s = 0; s < iStatements; s++) { 897             sSQL = aStatements[s]; 898             if (sSQL.length() > 0) { 899               oStrLog.append(sSQL + "\n\\\n"); 900               try { 901                 oStmt.execute(sSQL); 902               } 903               catch (SQLException sqle) { 904                 iErrors++; 905                 oStrLog.append("SQLException: " + sqle.getMessage() + "\n"); 906 907                 if (bStopOnError) { 908                   try { if (null!=oStmt) oStmt.close(); } catch (SQLException ignore) { } 909                   throw new java.lang.InterruptedException (); 910                 } 911               } 912             } // fi (sSQL) 913 } // next 914 oStmt.close(); 915           oStmt = null; 916           break; 917 /* 918         case BULK_BATCH: 919           oStmt = oConn.createStatement(); 920           for (int s = 0; s < iStatements; s++) { 921             sSQL = aStatements[s]; 922             if (sSQL.length() > 0) { 923                 oStmt.addBatch(sSQL); 924             } // fi (sSQL) 925             int[] results = oStmt.executeBatch(); 926             for (int r=0; r<results.length; r++) { 927               if (results[r]==1) 928                 oStrLog.append(aStatements[r] + "\n\\\n"); 929               else { 930                 iErrors++; 931                 oStrLog.append("ERROR: " + aStatements[r] + "\n\\\n"); 932                 if (bStopOnError) { 933                   try { if (null!=oStmt) oStmt.close(); } catch (SQLException ignore) { } 934                   throw new java.lang.InterruptedException(); 935                 } 936               } 937             } 938           } // next 939           oStmt.close(); 940           oStmt = null; 941           break; 942  */ 943       } // end switch() 944 945     if (DebugFile.trace) { 946       DebugFile.decIdent(); 947       DebugFile.writeln("End ModelManager.executeBulk()"); 948     } 949 950     return iErrors; 951   } // executeBulk 952 953   // --------------------------------------------------------------------------- 954 955   private int executeBulk(String sResourcePath, int iBatchType) 956     throws FileNotFoundException , IOException , SQLException ,InterruptedException { 957     StringBuffer oBuffer = getSQLBuffer(sResourcePath, iBatchType); 958     return executeBulk(oBuffer, sResourcePath, iBatchType); 959   } 960 961   // --------------------------------------------------------------------------- 962 963   private StringBuffer changeSchema(String sResourcePath, int iType, String sOriginalSchema, String sNewSchema) 964     throws InterruptedException , SQLException , IOException , FileNotFoundException { 965 966     StringBuffer oBuffer = getSQLBuffer(sResourcePath, iType); 967     String sBuffer = ""; 968 969     try { 970       sBuffer = Gadgets.replace(oBuffer.toString(), sOriginalSchema+".", sNewSchema+"."); 971       oBuffer = new StringBuffer (sBuffer); 972     } catch (org.apache.oro.text.regex.MalformedPatternException neverthrown) {} 973 974     return oBuffer; 975   } 976 977   // --------------------------------------------------------------------------- 978 979   /** 980    * <p>Create a functional module</p> 981    * @param sModuleName Name of module to create { kernel | lookups | security | 982    * jobs | thesauri | categories | products | addrbook | webbuilder | crm | 983    * lists | shops | projtrack | billing | hipermail } 984    * @return <b>true</b> if module was successfully created, <b>false</b> if errors 985    * occured during module creation. Even if error occur module may still be partially 986    * created at database after calling create() 987    * @throws IllegalStateException If not connected to database 988    * @throws FileNotFoundException If any of the internal files for module is not found 989    * @throws SQLException 990    * @throws IOException 991    */ 992   public boolean create(String sModuleName) 993     throws IllegalStateException , SQLException , FileNotFoundException , IOException { 994 995     if (DebugFile.trace) { 996       DebugFile.writeln("Begin ModelManager.create(" + sModuleName + ")"); 997       DebugFile.incIdent(); 998     } 999 1000    boolean bRetVal = true; 1001 1002    if (null==oConn) 1003      throw new IllegalStateException ("Not connected to database"); 1004 1005    try { 1006    if (sModuleName.equals("kernel")) { 1007 1008      executeBulk("tables/kernel.ddl", BULK_STATEMENTS); 1009      executeBulk("data/k_sequences.sql", BULK_STATEMENTS); 1010      executeBulk("data/k_version.sql", BULK_STATEMENTS); 1011      executeBulk("indexes/kernel.sql", BULK_STATEMENTS); 1012 1013      switch (iDbms) { 1014        case DBMS_MSSQL: 1015          executeBulk("procedures/mssql/kernel.ddl", BULK_PLSQL); 1016          break; 1017        case DBMS_MYSQL: 1018          executeBulk("procedures/mysql/kernel.ddl", BULK_PLSQL); 1019          break; 1020        default: 1021          executeBulk("procedures/" + sDbms + "/kernel.sql", BULK_PLSQL); 1022          break; 1023      } // end switch 1024 1025    } else if (sModuleName.equals("lookups")) { 1026 1027      executeBulk("tables/lookups.ddl", BULK_STATEMENTS); 1028 1029      executeBulk("data/k_lu_languages.sql" , BULK_STATEMENTS); 1030      executeBulk("data/k_lu_countries.sql" , BULK_STATEMENTS); 1031      executeBulk("data/k_lu_currencies.sql" , BULK_STATEMENTS); 1032      executeBulk("data/k_lu_status.sql" , BULK_BATCH); 1033      executeBulk("data/k_lu_cont_types.sql" , BULK_BATCH); 1034      executeBulk("data/k_lu_prod_types.sql" , BULK_BATCH); 1035      executeBulk("data/k_classes.sql" , BULK_BATCH); 1036 1037    } else if (sModuleName.equals("security")) { 1038 1039      executeBulk("tables/security.ddl", BULK_STATEMENTS); 1040 1041      executeBulk("data/k_lu_permissions.sql", BULK_STATEMENTS); 1042 1043      executeBulk("data/k_apps.sql", BULK_STATEMENTS); 1044 1045      executeBulk("data/k_domains.sql", BULK_STATEMENTS); 1046 1047      executeBulk("data/k_acl_groups.sql", BULK_STATEMENTS); 1048 1049      executeBulk("data/k_users.sql", BULK_STATEMENTS); 1050 1051      executeBulk("data/k_x_group_user.sql", BULK_STATEMENTS); 1052 1053      executeBulk("data/k_workareas.sql", BULK_STATEMENTS); 1054 1055      executeBulk("data/k_x_app_workarea.sql", BULK_STATEMENTS); 1056 1057      executeBulk("data/k_x_portlet_user.sql", BULK_STATEMENTS); 1058 1059      executeBulk("constraints/security.sql", BULK_STATEMENTS); 1060 1061      executeBulk("views/security.sql", BULK_STATEMENTS); 1062 1063      executeBulk("procedures/" + sDbms + "/security.ddl", BULK_PLSQL); 1064 1065      switch (iDbms) { 1066        case DBMS_MSSQL: 1067          executeBulk("triggers/mssql/security.ddl", BULK_PLSQL); 1068          break; 1069      } 1070    } else if (sModuleName.equals("jobs")) { 1071 1072      executeBulk("tables/jobs.ddl", BULK_STATEMENTS); 1073 1074      executeBulk("views/jobs.sql", BULK_STATEMENTS); 1075 1076      executeBulk("procedures/" + sDbms + "/jobs.ddl", BULK_PLSQL); 1077 1078      executeBulk("data/k_lu_job_status.sql" , BULK_BATCH); 1079 1080      executeBulk("data/k_lu_job_commands.sql" , BULK_BATCH); 1081 1082      executeBulk("constraints/jobs.sql", BULK_STATEMENTS); 1083 1084      executeBulk("indexes/jobs.sql", BULK_STATEMENTS); 1085 1086    } else if (sModuleName.equals("categories")) { 1087 1088      executeBulk("tables/categories.ddl", BULK_STATEMENTS); 1089 1090      executeBulk("constraints/categories.sql", BULK_STATEMENTS); 1091 1092      executeBulk("data/all_categories.sql", BULK_BATCH); 1093 1094      executeBulk("procedures/" + sDbms + "/categories.ddl", BULK_PLSQL); 1095 1096      executeBulk("indexes/categories.sql", BULK_STATEMENTS); 1097 1098      if (iDbms==DBMS_MSSQL) { 1099        executeBulk(changeSchema("views/" + sDbms + "/categories.sql", BULK_STATEMENTS, "dbo", sSchema), 1100                                 "views/" + sDbms + "/categories.sql", BULK_STATEMENTS); 1101        executeBulk("triggers/" + sDbms + "/categories.ddl", BULK_STATEMENTS); 1102      } 1103      else { 1104        executeBulk("views/" + sDbms + "/categories.sql", BULK_STATEMENTS); 1105      } 1106 1107      if (iDbms==DBMS_ORACLE) 1108        executeBulk("triggers/" + sDbms + "/categories.ddl", BULK_STATEMENTS); 1109 1110    } else if (sModuleName.equals("thesauri")) { 1111 1112      executeBulk("tables/thesauri.ddl", BULK_STATEMENTS); 1113 1114      executeBulk("indexes/thesauri.sql", BULK_STATEMENTS); 1115 1116      executeBulk("constraints/thesauri.sql", BULK_STATEMENTS); 1117 1118    } else if (sModuleName.equals("products")) { 1119 1120      executeBulk("tables/products.ddl", BULK_STATEMENTS); 1121 1122      executeBulk("constraints/products.sql", BULK_STATEMENTS); 1123 1124      executeBulk("views/" + sDbms + "/products.sql", BULK_STATEMENTS); 1125 1126      executeBulk("indexes/products.sql", BULK_STATEMENTS); 1127 1128      executeBulk("procedures/" + sDbms + "/products.ddl", BULK_PLSQL); 1129 1130    } else if (sModuleName.equals("addrbook")) { 1131 1132      executeBulk("tables/addrbook.ddl", BULK_STATEMENTS); 1133 1134      executeBulk("constraints/addrbook.sql", BULK_STATEMENTS); 1135 1136      executeBulk("procedures/" + sDbms + "/addrbook.ddl", BULK_PLSQL); 1137 1138      executeBulk("data/k_to_do_lookup.sql" , BULK_STATEMENTS); 1139 1140      executeBulk("indexes/addrbook.sql", BULK_STATEMENTS); 1141 1142    } else if (sModuleName.equals("forums")) { 1143 1144      executeBulk("tables/forums.ddl", BULK_STATEMENTS); 1145 1146      executeBulk("constraints/forums.sql", BULK_STATEMENTS); 1147 1148      executeBulk("procedures/" + sDbms + "/forums.ddl", BULK_PLSQL); 1149 1150      executeBulk("data/k_newsgroups.sql", BULK_STATEMENTS); 1151 1152      executeBulk("indexes/forums.sql", BULK_STATEMENTS); 1153 1154    } else if (sModuleName.equals("crm")) { 1155 1156      executeBulk("tables/crm.ddl", BULK_STATEMENTS); 1157 1158      executeBulk("constraints/crm.sql", BULK_STATEMENTS); 1159 1160      executeBulk("procedures/" + sDbms + "/crm.ddl", BULK_PLSQL); 1161 1162      executeBulk("data/k_companies_lookup.sql" , BULK_STATEMENTS); 1163 1164      executeBulk("data/k_contacts_lookup.sql" , BULK_STATEMENTS); 1165 1166      executeBulk("data/k_oportunities_lookup.sql" , BULK_STATEMENTS); 1167 1168      executeBulk("indexes/crm.sql", BULK_STATEMENTS); 1169 1170      if (iDbms==DBMS_MSSQL) { 1171        executeBulk(changeSchema("views/" + sDbms + "/crm.sql", BULK_STATEMENTS, "dbo", sSchema), 1172                    "views/" + sDbms + "/crm.sql", BULK_STATEMENTS); 1173 1174      } 1175      else { 1176        executeBulk("views/" + sDbms + "/crm.sql", BULK_STATEMENTS); 1177      } 1178    } else if (sModuleName.equals("lists")) { 1179 1180      executeBulk("tables/lists.ddl", BULK_STATEMENTS); 1181 1182      executeBulk("procedures/" + sDbms + "/lists.ddl", BULK_PLSQL); 1183 1184      executeBulk("views/" + sDbms + "/lists.sql", BULK_STATEMENTS); 1185 1186      executeBulk("indexes/lists.sql", BULK_STATEMENTS); 1187 1188      executeBulk("constraints/lists.sql", BULK_STATEMENTS); 1189 1190    } else if (sModuleName.equals("projtrack")) { 1191 1192      executeBulk("tables/projtrack.ddl", BULK_STATEMENTS); 1193 1194      executeBulk("views/" + sDbms + "/projtrack.sql", BULK_STATEMENTS); 1195 1196      executeBulk("data/k_projects_lookup.sql" , BULK_STATEMENTS); 1197 1198      executeBulk("data/k_duties_lookup.sql" , BULK_STATEMENTS); 1199 1200      executeBulk("data/k_bugs_lookup.sql" , BULK_STATEMENTS); 1201 1202      executeBulk("indexes/projtrack.sql", BULK_STATEMENTS); 1203 1204      executeBulk("constraints/projtrack.sql", BULK_STATEMENTS); 1205 1206      if (iDbms==DBMS_MSSQL) { 1207        // Microsoft SQL Server functions and views must be created specifying 1208 // explicitly the schema, so change the default "dbo" to the current one 1209 executeBulk(changeSchema("procedures/" + sDbms + "/projtrack.ddl", BULK_PLSQL, "dbo", sSchema), 1210                    "procedures/" + sDbms + "/projtrack.ddl", BULK_PLSQL); 1211      } 1212      else { 1213        executeBulk("procedures/" + sDbms + "/projtrack.ddl", BULK_PLSQL); 1214      } 1215    } else if (sModuleName.equals("webbuilder")) { 1216 1217      executeBulk("tables/webbuilder.ddl", BULK_STATEMENTS); 1218 1219      executeBulk("constraints/webbuilder.sql", BULK_STATEMENTS); 1220 1221      executeBulk("data/k_microsites.sql" , BULK_STATEMENTS); 1222 1223      executeBulk("indexes/webbuilder.sql", BULK_STATEMENTS); 1224 1225      executeBulk("procedures/" + sDbms + "/webbuilder.ddl", BULK_PLSQL); 1226 1227    } else if (sModuleName.equals("shops")) { 1228 1229      executeBulk("tables/shops.ddl", BULK_STATEMENTS); 1230 1231      executeBulk("constraints/shops.sql", BULK_STATEMENTS); 1232 1233      executeBulk("procedures/" + sDbms + "/shops.ddl", BULK_PLSQL); 1234 1235      if (iDbms==DBMS_MSSQL) { 1236        executeBulk(changeSchema("views/" + sDbms + "/shops.sql", BULK_STATEMENTS, "dbo", sSchema), 1237                    "views/" + sDbms + "/shops.sql", BULK_STATEMENTS); 1238      } 1239      else { 1240        executeBulk("views/" + sDbms + "/shops.sql", BULK_STATEMENTS); 1241      } 1242    } else if (sModuleName.equals("billing")) { 1243 1244      executeBulk("tables/billing.ddl", BULK_STATEMENTS); 1245 1246      executeBulk("constraints/billing.sql", BULK_STATEMENTS); 1247 1248      executeBulk("procedures/" + sDbms + "/billing.ddl", BULK_PLSQL); 1249 1250  } else if (sModuleName.equals("hipermail")) { 1251 1252    executeBulk("tables/hipermail.ddl", BULK_STATEMENTS); 1253 1254    executeBulk("constraints/hipermail.sql", BULK_STATEMENTS); 1255 1256    executeBulk("indexes/hipermail.sql", BULK_STATEMENTS); 1257 1258    executeBulk("procedures/" + sDbms + "/hipermail.ddl", BULK_PLSQL); 1259 1260  } else if (sModuleName.equals("training")) { 1261 1262    executeBulk("tables/training.ddl", BULK_STATEMENTS); 1263 1264    executeBulk("views/training.sql", BULK_STATEMENTS); 1265 1266    executeBulk("procedures/" + sDbms + "/training.ddl", BULK_PLSQL); 1267  } 1268  } catch (InterruptedException ie) { 1269    oStrLog.append("STOP ON ERROR SET TO ON: SCRIPT INTERRUPTED\n"); 1270    bRetVal = false; 1271  } 1272 1273  if (DebugFile.trace) { 1274    DebugFile.decIdent(); 1275    DebugFile.writeln("End ModelManager.create() : " + String.valueOf(bRetVal)); 1276  } 1277 1278  return bRetVal; 1279 } // create 1280 1281  // --------------------------------------------------------------------------- 1282 1283  /** 1284   * <p>Drop a functional module</p> 1285   * @param sModuleName Name of module to drop { kernel | lookups | security | 1286   * jobs | thesauri | categories | products | addrbook | webbuilder | crm | 1287   * lists | shops | projtrack | billing | hipermail } 1288   * @return <b>true</b> if module was successfully droped, <b>false</b> if errors 1289   * occured during droping module. 1290   * Even if error occur module may still be partially droped at database after calling drop() 1291   * @throws IllegalStateException 1292   * @throws SQLException 1293   * @throws FileNotFoundException 1294   * @throws IOException 1295   */ 1296  public boolean drop(String sModuleName) 1297    throws IllegalStateException , SQLException , FileNotFoundException ,IOException { 1298 1299    if (DebugFile.trace) { 1300      DebugFile.writeln("Begin ModelManager.drop(" + sModuleName + ")"); 1301      DebugFile.incIdent(); 1302    } 1303 1304    boolean bRetVal = true; 1305 1306    if (null==oConn) 1307      throw new IllegalStateException ("Not connected to database"); 1308 1309    try { 1310    if (sModuleName.equals("kernel")) { 1311 1312      executeBulk("drop/" + sDbms + "/kernel.sql", BULK_STATEMENTS); 1313      executeBulk("drop/kernel.sql", BULK_STATEMENTS); 1314 1315    } else if (sModuleName.equals("lookups")) { 1316 1317      executeBulk("drop/lookups.sql", BULK_STATEMENTS); 1318 1319    } else if (sModuleName.equals("security")) { 1320 1321      executeBulk("drop/" + sDbms + "/security.sql", BULK_STATEMENTS); 1322 1323      executeBulk("drop/security.sql", BULK_STATEMENTS); 1324 1325    } else if (sModuleName.equals("jobs")) { 1326 1327      executeBulk("drop/" + sDbms + "/jobs.sql", BULK_STATEMENTS); 1328 1329      executeBulk("drop/jobs.sql", BULK_STATEMENTS); 1330 1331    } else if (sModuleName.equals("categories")) { 1332 1333      executeBulk("drop/" + sDbms + "/categories.sql", BULK_STATEMENTS); 1334 1335      executeBulk("drop/categories.sql", BULK_STATEMENTS); 1336 1337    } else if (sModuleName.equals("thesauri")) { 1338 1339      executeBulk("drop/thesauri.sql", BULK_STATEMENTS); 1340 1341    } else if (sModuleName.equals("addrbook")) { 1342 1343      executeBulk("drop/" + sDbms + "/addrbook.sql", BULK_STATEMENTS); 1344 1345      executeBulk("drop/addrbook.sql", BULK_STATEMENTS); 1346 1347    } else if (sModuleName.equals("forums")) { 1348 1349      executeBulk("drop/" + sDbms + "/forums.sql", BULK_STATEMENTS); 1350 1351      executeBulk("drop/forums.sql", BULK_STATEMENTS); 1352 1353    } else if (sModuleName.equals("products")) { 1354 1355      executeBulk("drop/" + sDbms + "/products.sql", BULK_STATEMENTS); 1356 1357      executeBulk("drop/products.sql", BULK_STATEMENTS); 1358 1359    } else if (sModuleName.equals("crm")) { 1360 1361      executeBulk("drop/" + sDbms + "/crm.sql", BULK_STATEMENTS); 1362 1363      executeBulk("drop/crm.sql", BULK_STATEMENTS); 1364 1365    } else if (sModuleName.equals("lists")) { 1366 1367      executeBulk("drop/" + sDbms + "/lists.sql", BULK_STATEMENTS); 1368 1369      executeBulk("drop/lists.sql", BULK_STATEMENTS); 1370 1371    } else if (sModuleName.equals("projtrack")) { 1372 1373      if (iDbms==DBMS_MSSQL) { 1374        executeBulk(changeSchema("drop/" + sDbms + "/projtrack.sql", BULK_STATEMENTS, "dbo", sSchema), 1375                    "drop/" + sDbms + "/projtrack.sql", BULK_STATEMENTS); 1376      } 1377      else { 1378        executeBulk("drop/" + sDbms + "/projtrack.sql", BULK_STATEMENTS); 1379      } 1380 1381      executeBulk("drop/projtrack.sql", BULK_STATEMENTS); 1382 1383    } else if (sModuleName.equals("webbuilder")) { 1384 1385      executeBulk("drop/" + sDbms + "/webbuilder.sql", BULK_STATEMENTS); 1386 1387      executeBulk("drop/webbuilder.sql", BULK_STATEMENTS); 1388 1389    } else if (sModuleName.equals("shops")) { 1390 1391      executeBulk("drop/" + sDbms + "/shops.sql", BULK_STATEMENTS); 1392 1393      executeBulk("drop/shops.sql", BULK_STATEMENTS); 1394 1395    } else if (sModuleName.equals("billing")) { 1396 1397      executeBulk("drop/" + sDbms + "/billing.sql", BULK_STATEMENTS); 1398 1399      executeBulk("drop/billing.sql", BULK_STATEMENTS); 1400 1401    } else if (sModuleName.equals("hipermail")) { 1402 1403      executeBulk("drop/" + sDbms + "/hipermail.sql", BULK_STATEMENTS); 1404 1405      executeBulk("drop/hipermail.sql", BULK_STATEMENTS); 1406 1407    } else if (sModuleName.equals("training")) { 1408 1409      executeBulk("drop/training.sql", BULK_STATEMENTS); 1410 1411      executeBulk("drop/" + sDbms + "/training.sql", BULK_STATEMENTS); 1412    } 1413    } catch (InterruptedException ie) { 1414      oStrLog.append("STOP ON ERROR SET TO ON: SCRIPT INTERRUPTED\n"); 1415      bRetVal = false; 1416    } 1417 1418    if (DebugFile.trace) { 1419      DebugFile.decIdent(); 1420      DebugFile.writeln("End ModelManager.drop() : " + String.valueOf(bRetVal)); 1421    } 1422 1423  return bRetVal; 1424  } // drop 1425 1426  // --------------------------------------------------------------------------- 1427 1428  /** 1429   * <p>Create all modules</p> 1430   * The created modules will be (in order): 1431   * kernel, lookups, security, jobs, categories, thesauri, products, addrbook, 1432   * forums, crm, projtrack, lists, webbuilder, shops, billing, hipermail, training 1433   * @throws FileNotFoundException If any of the internal files for modules are not found 1434   * @throws IllegalStateException 1435   * @throws SQLException 1436   * @throws IOException 1437   */ 1438  public boolean createAll() 1439    throws IllegalStateException , SQLException , FileNotFoundException , IOException { 1440 1441    if (!create ("kernel") && bStopOnError) return false; 1442    if (!create ("lookups") && bStopOnError) return false; 1443    if (!create ("security") && bStopOnError) return false; 1444    if (!create ("jobs") && bStopOnError) return false; 1445    if (!create ("categories") && bStopOnError) return false; 1446    if (!create ("thesauri") && bStopOnError) return false; 1447    if (!create ("products") && bStopOnError) return false; 1448    if (!create ("addrbook") && bStopOnError) return false; 1449    if (!create ("forums") && bStopOnError) return false; 1450    if (!create ("crm") && bStopOnError) return false; 1451    if (!create ("projtrack") && bStopOnError) return false; 1452    if (!create ("lists") && bStopOnError) return false; 1453    if (!create ("webbuilder") && bStopOnError) return false; 1454    if (!create ("shops") && bStopOnError) return false; 1455    if (!create ("billing") && bStopOnError) return false; 1456    if (!create ("hipermail") && bStopOnError) return false; 1457    if (!create ("training") && bStopOnError) return false; 1458 1459    if (DBMS_ORACLE==iDbms) { 1460      try { 1461        recompileOrcl(); 1462      } 1463        catch (SQLException sqle) { 1464          if (bStopOnError) throw new SQLException ("SQLException: " + sqle.getMessage(), sqle.getSQLState(), sqle.getErrorCode()); 1465      } 1466 1467      Statement oStmt = null; 1468      ResultSet oRSet = null; 1469 1470      try { 1471 1472        oStmt = oConn.createStatement(); 1473        oRSet = oStmt.executeQuery("SELECT OBJECT_TYPE,OBJECT_NAME FROM USER_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE IN ('PROCEDURE','VIEW','TRIGGER')"); 1474 1475        while (oRSet.next()) { 1476          iErrors++; 1477          oStrLog.append(oRSet.getString(1) + " " + oRSet.getString(2) + " is invalid after recompile\n"); 1478        } // wend 1479 1480      } catch (SQLException sqle) { 1481        iErrors++; 1482        oStrLog.append(sqle + "\n"); 1483 1484        if (bStopOnError) 1485          throw new SQLException ("SQLException: " + sqle.getMessage(), sqle.getSQLState(), sqle.getErrorCode()); 1486      } 1487      finally { 1488        if (null!=oRSet) oRSet.close(); 1489        oRSet = null; 1490        if (null!=oStmt) oStmt.close(); 1491        oStmt = null; 1492      } 1493    } 1494    return true; 1495  } // createAll 1496 1497  // --------------------------------------------------------------------------- 1498 1499  /** 1500   * <p>Drop all modules</p> 1501   * The created modules will be (in order): 1502   * billing, shops, webbuilder, lists, projtrack, crm, forums, addrbook, 1503   * products, thesauri, categories, jobs, security, lookups, kernel 1504   * @throws IllegalStateException 1505   * @throws SQLException 1506   * @throws FileNotFoundException 1507   * @throws IOException 1508   */ 1509  public boolean dropAll() 1510    throws IllegalStateException , SQLException , FileNotFoundException , IOException { 1511 1512    if (!drop ("training") && bStopOnError) return false; 1513    if (!drop ("hipermail") && bStopOnError) return false; 1514    if (!drop ("billing") && bStopOnError) return false; 1515    if (!drop ("shops") && bStopOnError) return false; 1516    if (!drop ("webbuilder") && bStopOnError) return false; 1517    if (!drop ("lists") && bStopOnError) return false; 1518    if (!drop ("projtrack") && bStopOnError) return false; 1519    if (!drop ("crm") && bStopOnError) return false; 1520    if (!drop ("forums") && bStopOnError) return false; 1521    if (!drop ("addrbook") && bStopOnError) return false; 1522    if (!drop ("products") && bStopOnError) return false; 1523    if (!drop ("thesauri") && bStopOnError) return false; 1524    if (!drop ("categories") && bStopOnError) return false; 1525    if (!drop ("jobs") && bStopOnError) return false; 1526    if (!drop ("security") && bStopOnError) return false; 1527    if (!drop ("lookups") && bStopOnError) return false; 1528    if (!drop ("kernel") && bStopOnError) return false; 1529 1530    return true; 1531  } // createAll 1532 1533  // --------------------------------------------------------------------------- 1534 1535  /** 1536   * <p>Create a default database ready for use</p> 1537   * All modules for the full suite will be created at the new database.<br> 1538   * The new database will contain 5 domains and 5 workareas:<br> 1539   * SYSTEM, MODEL, TEST, DEMO and REAL<br> 1540   * SYSTEM and MODEL domains are for administrative purposed only and should 1541   * not be used by programmers.<br> 1542   * Domains TEST, DEMO and REAL are intended for development/testing, 1543   * aceptance/demostration and real/production usage.<br> 1544   * Error messages are written to internal ModelManager log and can be inspected by 1545   * calling report() method after createDefaultDatabase() 1546   * @throws FileNotFoundException If any of the internal files for modules are not found 1547   * @throws EvalError Java BeanShell script domain_create.js as a syntax error 1548   * @throws org.xml.sax.SAXException Parsing error at file workarea_clon.xml 1549   * @throws InstantiationException SAX parser is not properly installed 1550   * @throws IllegalAccessException SAX parser is not properly installed 1551   * @throws ClassNotFoundException SAX parser is not properly installed 1552   * @throws IOException 1553   * @throws SQLException 1554   */ 1555  public boolean createDefaultDatabase() 1556    throws FileNotFoundException , IOException , SQLException , EvalError, 1557    InstantiationException , IllegalAccessException , ClassNotFoundException , 1558    org.xml.sax.SAXException { 1559 1560    Statement oStmt = null; 1561 1562    if (DebugFile.trace) { 1563      DebugFile.decIdent(); 1564      DebugFile.writeln("Begin ModelManager.createDefaultDatabase()"); 1565    } 1566 1567    // Ensure that SAXParser can be instantiated before initiating the database creation process 1568 if (DebugFile.trace) 1569      DebugFile.writeln("Class.forName(org.apache.xerces.parsers.SAXParser)"); 1570    Class SAXParserClass = Class.forName("org.apache.xerces.parsers.SAXParser"); 1571 1572    if (DBMS_MSSQL==iDbms) { 1573      try { 1574        oStmt = oConn.createStatement(); 1575 1576        if (DebugFile.trace) 1577          DebugFile.writeln("Statement.execute(ALTER DATABASE " + oConn.getCatalog() + " SET ARITHABORT ON)"); 1578 1579        oStmt.execute("ALTER DATABASE " + oConn.getCatalog() + " SET ARITHABORT ON"); 1580        oStmt.close(); 1581        oStmt=null; 1582 1583        oStrLog.append("ALTER DATABASE " + oConn.getCatalog() + " SET ARITHABORT ON\n"); 1584      } 1585      catch (SQLException sqle) { 1586         if (DebugFile.trace) 1587           DebugFile.writeln("SQLException " + sqle.getMessage()); 1588         iErrors++; 1589         oStrLog.append("SQLException: " + sqle.getMessage() + "\n"); 1590      } 1591    } 1592 1593    boolean bRetVal = createAll(); 1594 1595    createDomain("TEST"); 1596    createDomain("DEMO"); 1597    createDomain("REAL"); 1598 1599    cloneWorkArea("MODEL.model_default", "TEST.test_default"); 1600    cloneWorkArea("MODEL.model_default", "DEMO.demo_default"); 1601    cloneWorkArea("MODEL.model_default", "REAL.real_default"); 1602 1603    // After installing on Windows replace slashes with backslashes 1604 1605    if (System.getProperty("os.name").startsWith("Windows")) { 1606      oStmt = oConn.createStatement(); 1607      try { 1608        switch (iDbms) { 1609          case DBMS_MSSQL: 1610          case DBMS_ORACLE: 1611          case DBMS_MYSQL: 1612          case DBMS_DB2: 1613            oStmt.executeUpdate( 1614                "UPDATE k_microsites SET path_metadata=REPLACE(path_metadata,'/','\\\\')"); 1615            oStmt.executeUpdate( 1616                "UPDATE k_pagesets SET path_data=REPLACE(path_data,'/','\\\\')"); 1617            break; 1618          case DBMS_POSTGRESQL: 1619            oStmt.executeUpdate( 1620                "UPDATE k_microsites SET path_metadata=translate(path_metadata,'/','\\\\')"); 1621            oStmt.executeUpdate( 1622                "UPDATE k_pagesets SET path_data=translate(path_data,'/','\\\\')"); 1623            break; 1624        } // end switch(iDbms) 1625 oStmt.close(); 1626        oStmt=null; 1627      } catch (SQLException sqle) { 1628        if (DebugFile.trace) DebugFile.writeln("SQLException " + sqle.getMessage()); 1629        iErrors++; 1630        oStrLog.append("SQLException: " + sqle.getMessage() + "\n"); 1631        try { oStmt.close(); } catch (Exception ignore) {} 1632      } 1633    } 1634 1635    if (DebugFile.trace) { 1636      DebugFile.decIdent(); 1637      DebugFile.writeln("End ModelManager.createDefaultDatabase() : " + String.valueOf(bRetVal)); 1638    } 1639 1640    return bRetVal; 1641  } // createDefaultDatabase 1642 1643  // --------------------------------------------------------------------------- 1644 1645  public void scriptData (String sTableName, String sWhere, String sFilePath) 1646    throws SQLException , IOException { 1647 1648    Statement oStmt; 1649    ResultSet oRSet; 1650    ResultSetMetaData oMDat; 1651    FileOutputStream oWriter; 1652    String sColumns; 1653    Object oValue; 1654    String sValue; 1655    String sValueEscaped; 1656    int iCols; 1657    byte[] byComma = new String (",").getBytes(sEncoding); 1658    byte[] byNull = new String ("NULL").getBytes(sEncoding); 1659    byte[] byCRLF = new String (");\n").getBytes(sEncoding); 1660 1661    oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 1662    if (sWhere==null) 1663      oRSet = oStmt.executeQuery("SELECT * FROM " + sTableName + " ORDER BY 1"); 1664    else 1665      oRSet = oStmt.executeQuery("SELECT * FROM " + sTableName + " WHERE " + sWhere + " ORDER BY 1"); 1666 1667    oMDat = oRSet.getMetaData(); 1668    iCols = oMDat.getColumnCount(); 1669 1670    sColumns = ""; 1671 1672    for (int c=1; c<=iCols; c++) { 1673      if (!oMDat.getColumnName(c).equalsIgnoreCase("dt_created")) { 1674        if (c!=1) sColumns += ","; 1675        sColumns += oMDat.getColumnName(c); 1676      } 1677    } // next 1678 1679    oWriter = new FileOutputStream (sFilePath); 1680 1681    while (oRSet.next()) { 1682      sValue = "INSERT INTO " + sTableName + " (" + sColumns + ") VALUES ("; 1683 1684      oWriter.write(sValue.getBytes(sEncoding)); 1685 1686      for (int c=1; c<=iCols; c++) { 1687 1688        if (!oMDat.getColumnName(c).equalsIgnoreCase("dt_created")) { 1689 1690          if (c!=1) oWriter.write(byComma); 1691 1692          switch (oMDat.getColumnType(c)) { 1693 1694            case Types.CHAR: 1695            case Types.VARCHAR: 1696              sValue = oRSet.getString(c); 1697              if (oRSet.wasNull()) 1698                sValueEscaped = "NULL"; 1699              else if (sValue.indexOf(39)>=0) { 1700                sValueEscaped = "'"; 1701                for (int n=0; n<sValue.length(); n++) 1702                  sValueEscaped += (sValue.charAt(n)!=39 ? sValue.substring(n,n+1) : "''"); 1703                sValueEscaped += "'"; 1704              } 1705              else 1706                sValueEscaped = "'" + sValue + "'"; 1707              oWriter.write(sValueEscaped.getBytes(sEncoding)); 1708              break; 1709 1710            case Types.SMALLINT: 1711              oValue = oRSet.getObject(c); 1712              if (oRSet.wasNull()) 1713                oWriter.write(byNull); 1714              else 1715                oWriter.write(String.valueOf(oRSet.getShort(c)).getBytes(sEncoding)); 1716              break; 1717 1718            case Types.INTEGER: 1719 1720              oValue = oRSet.getObject(c); 1721              if (oRSet.wasNull()) 1722                oWriter.write(byNull); 1723              else 1724                oWriter.write(String.valueOf(oRSet.getInt(c)).getBytes(sEncoding)); 1725              break; 1726 1727            case Types.DATE: 1728            case Types.TIMESTAMP: 1729              oWriter.write(byNull); 1730              break; 1731 1732          } // end switch 1733 } // fi (dt_created) 1734 } // next 1735 oWriter.write(byCRLF); 1736    } // wend 1737 1738    oWriter.close(); 1739  } // scriptData 1740 1741  // ---------------------------------------------------------- 1742 1743  /** 1744   * <p>Get an embedded resource file as a String</p> 1745   * @param sResourcePath Relative path at JAR file from com/knowgate/hipergate/datamodel/ModelManager 1746   * @param sEncoding Character encoding for resource if it is a text file.<br> 1747   * If sEncoding is <b>null</b> then UTF-8 is assumed. 1748   * @return Readed file 1749   * @throws FileNotFoundException 1750   * @throws IOException 1751   */ 1752  public static String getResourceAsString (String sResourcePath, String sEncoding) 1753      throws FileNotFoundException , IOException { 1754 1755    if (DebugFile.trace) { 1756      DebugFile.writeln("Begin ModelManager.getResourceAsString(" + sResourcePath + "," + sEncoding + ")"); 1757      DebugFile.incIdent(); 1758    } 1759 1760    StringBuffer oXMLSource = new StringBuffer (12000); 1761    char[] Buffer = new char[4000]; 1762    InputStreamReader oReader = null; 1763    int iReaded, iSkip; 1764 1765    if (null==sEncoding) sEncoding = "UTF-8"; 1766 1767    try { 1768      oReader = new InputStreamReader (Class.forName("com.knowgate.hipergate.datamodel.ModelManager").getResourceAsStream(sResourcePath), sEncoding); 1769    } 1770    catch (ClassNotFoundException cnfe) { } 1771 1772    while (true) { 1773      iReaded = oReader.read(Buffer, 0, 4000); 1774 1775      if (-1==iReaded) break; 1776 1777      // Skip FF FE character mark for Unidode files 1778 iSkip = ((int)Buffer[0]==65279 || (int)Buffer[0]==65534 ? 1 : 0); 1779 1780      oXMLSource.append(Buffer, iSkip, iReaded-iSkip); 1781    } // wend 1782 1783    oReader.close(); 1784 1785    if (DebugFile.trace) { 1786      DebugFile.decIdent(); 1787      DebugFile.writeln("End ModelManager.getResourceAsString()"); 1788    } 1789 1790    return oXMLSource.toString(); 1791 1792  } // getResourceAsString 1793 1794  // --------------------------------------------------------------------------- 1795 1796  /** 1797  * <p>Re-compile invalid objects for an Oracle database</p> 1798  * @throws SQLException 1799  * @throws FileNotFoundException 1800  * @throws IOException 1801  */ 1802  public void recompileOrcl () throws SQLException { 1803 1804    String sqlgencmd; 1805    Statement oStmt; 1806    CallableStatement oCall; 1807    ResultSet oRSet; 1808    String sAlterSql = ""; 1809 1810    if (DebugFile.trace) { 1811      DebugFile.writeln("Begin ModelManager.recompileOrcl()"); 1812      DebugFile.incIdent(); 1813    } 1814 1815    // This SQL query produces the alter statements for recompile the objects which status is 'INVALID' 1816 sqlgencmd = " SELECT 'ALTER ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || "; 1817    sqlgencmd += "object_name || ' COMPILE' || DECODE(object_type, 'PACKAGE BODY', ' BODY', '') "; 1818    sqlgencmd += " cmd "; 1819    sqlgencmd += "FROM USER_OBJECTS "; 1820    sqlgencmd += "WHERE status = 'INVALID' AND "; 1821    sqlgencmd += "object_type IN ('TRIGGER','PACKAGE','PACKAGEBODY','VIEW','PROCEDURE','FUNCTION') AND "; 1822    sqlgencmd += "(object_type <> 'PACKAGE BODY' OR "; 1823    sqlgencmd += " (object_name) NOT IN "; 1824    sqlgencmd += " (SELECT object_name "; 1825    sqlgencmd += " FROM USER_OBJECTS "; 1826    sqlgencmd += " WHERE object_type = 'PACKAGE' AND status = 'INVALID'))"; 1827 1828    oStrLog.append(sqlgencmd+"\n"); 1829 1830    oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 1831    oRSet = oStmt.executeQuery(sqlgencmd); 1832 1833    while (oRSet.next()) { 1834 1835      try { 1836           sAlterSql = oRSet.getString(1); 1837           oCall = oConn.prepareCall(sAlterSql); 1838           oCall.execute(); 1839           oCall.close(); 1840 1841           oStrLog.append(sAlterSql+"\n"); 1842      } 1843      catch (SQLException sqle) { 1844 1845           iErrors++; 1846           oStrLog.append("SQLException: " + sqle.getMessage() + "\n"); 1847           oStrLog.append(sAlterSql + "\n"); 1848 1849           if (bStopOnError) { 1850             oRSet.close(); 1851             oRSet = null ; 1852             oStmt.close(); 1853             oStmt = null ; 1854 1855             throw new SQLException ("SQLException: " + sqle.getMessage(), sqle.getSQLState(), sqle.getErrorCode()); 1856           } 1857      } 1858 1859    } // wend 1860 if (null!=oRSet) oRSet.close(); 1861    if (null!=oStmt) oStmt.close(); 1862 1863    if (DebugFile.trace) { 1864      DebugFile.decIdent(); 1865      DebugFile.writeln("End ModelManager.recompileOrcl()"); 1866    } 1867  } // recompileOrcl 1868 1869  // ---------------------------------------------------------- 1870 1871  /** 1872   * <p>Create a clone of a WorkArea</p> 1873   * WorkAreas are cloned by following instructions contained in 1874   * com/knowgate/hipergate/datamodel/scripts/<i>dbms</i>/workarea_clon.xml file. 1875   * @param sOriginWorkArea String of the form domain_name.workarea_name, 1876   * for example "MODEL.default_workarea" 1877   * @param sTargetWorkArea String of the form domain_name.workarea_name, 1878   * for example "TEST1.devel_workarea" 1879   * @return GUID of new WorkArea or <b>null</b> if clone could not be created.<br> 1880   * Error messages are written to internal ModelManager log and can be inspected by 1881   * calling report() method after cloneWorkArea() 1882   * @throws SQLException Most probably raised because data at model_default workarea is corrupted 1883   * @throws InstantiationException SAX parser is not properly installed 1884   * @throws IllegalAccessException SAX parser is not properly installed 1885   * @throws ClassNotFoundException SAX parser is not properly installed 1886   * @throws org.xml.sax.SAXException Parsing error at file workarea_clon.xml 1887   * @throws IOException 1888   * @see com.knowgate.workareas.WorkArea#delete(JDCConnection,String) 1889   */ 1890  public String cloneWorkArea(String sOriginWorkArea, String sTargetWorkArea) 1891      throws SQLException ,IOException ,InstantiationException ,IllegalAccessException , 1892             ClassNotFoundException , org.xml.sax.SAXException { 1893 1894    PreparedStatement oPrep; 1895    Statement oStmt; 1896    ResultSet oRSet; 1897 1898    if (DebugFile.trace) { 1899      DebugFile.writeln("Begin ModelManager.cloneWorkArea(" + sOriginWorkArea + "," + sTargetWorkArea + ")"); 1900      DebugFile.incIdent(); 1901    } 1902 1903    if (null==oConn) 1904      throw new IllegalStateException ("Not connected to database"); 1905 1906    // Get a JDC Connection Wrapper 1907 com.knowgate.jdc.JDCConnection oJDC = new com.knowgate.jdc.JDCConnection(oConn, null); 1908 1909    // Split Domain and WorkArea names 1910 String [] aOriginWrkA = com.knowgate.misc.Gadgets.split2 (sOriginWorkArea, '.'); 1911    String [] aTargetWrkA = com.knowgate.misc.Gadgets.split2 (sTargetWorkArea, '.'); 1912 1913    int iSourceDomainId = com.knowgate.acl.ACLDomain.getIdFromName(oJDC, aOriginWrkA[0]); 1914 1915    if (0==iSourceDomainId) { 1916      iErrors++; 1917      oStrLog.append("Domain " + aOriginWrkA[0] + " not found\n"); 1918      return null; 1919    } 1920 1921    int iTargetDomainId = com.knowgate.acl.ACLDomain.getIdFromName(oJDC, aTargetWrkA[0]); 1922 1923    if (0==iTargetDomainId) { 1924      iErrors++; 1925      oStrLog.append("Domain " + aTargetWrkA[0] + " not found\n"); 1926      return null; 1927    } 1928 1929    String sSourceWorkAreaId = com.knowgate.workareas.WorkArea.getIdFromName(oJDC, iSourceDomainId, aOriginWrkA[1]); 1930 1931    if (null==sSourceWorkAreaId) { 1932      iErrors++; 1933      oStrLog.append("WorkArea " + aOriginWrkA[1] + " not found at Domain " + aOriginWrkA[0] + "\n"); 1934      return null; 1935    } 1936 1937    String sTargetWorkAreaId = com.knowgate.workareas.WorkArea.getIdFromName(oJDC, iTargetDomainId, aTargetWrkA[1]); 1938 1939    if (null==sTargetWorkAreaId) 1940      sTargetWorkAreaId = Gadgets.generateUUID(); 1941 1942    oStrLog.append("SELECT gu_owner,gu_admins FROM k_domains WHERE id_domain=" + String.valueOf(iTargetDomainId)); 1943 1944    oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 1945 1946    if (DebugFile.trace) 1947      DebugFile.writeln("Statement.executeQuery(SELECT gu_owner,gu_admins FROM k_domains WHERE id_domain=" + String.valueOf(iTargetDomainId) + ")"); 1948 1949    oRSet = oStmt.executeQuery("SELECT gu_owner,gu_admins FROM k_domains WHERE id_domain=" + String.valueOf(iTargetDomainId)); 1950    oRSet.next(); 1951    String sOwnerId = oRSet.getString(1); 1952    String sAdminId = oRSet.getString(2); 1953    oRSet.close(); 1954    oStmt.close(); 1955 1956    Properties oParams = new Properties (); 1957    oParams.put("SourceWorkAreaId", sSourceWorkAreaId); 1958    oParams.put("TargetDomainId", String.valueOf(iTargetDomainId)); 1959    oParams.put("TargetWorkAreaId", sTargetWorkAreaId); 1960    oParams.put("TargetWorkAreaNm", String.valueOf(aTargetWrkA[1])); 1961    oParams.put("OwnerId", sOwnerId); 1962 1963    com.knowgate.datacopy.DataStruct oDS = new com.knowgate.datacopy.DataStruct(); 1964 1965    oDS.setOriginConnection(oConn); 1966    oDS.setTargetConnection(oConn); 1967 1968    switch (oJDC.getDataBaseProduct()) { 1969 1970      case com.knowgate.jdc.JDCConnection.DBMS_MSSQL: 1971        oDS.parse (getResourceAsString("scripts/mssql/workarea_clon.xml", sEncoding), oParams); 1972        break; 1973 1974      case com.knowgate.jdc.JDCConnection.DBMS_MYSQL: 1975        oDS.parse (getResourceAsString("scripts/mysql/workarea_clon.xml", sEncoding), oParams); 1976        break; 1977 1978      case com.knowgate.jdc.JDCConnection.DBMS_ORACLE: 1979        oDS.parse (getResourceAsString("scripts/oracle/workarea_clon.xml", sEncoding), oParams); 1980        break; 1981 1982      case com.knowgate.jdc.JDCConnection.DBMS_POSTGRESQL: 1983        oDS.parse (getResourceAsString("scripts/postgresql/workarea_clon.xml", sEncoding), oParams); 1984        break; 1985    } 1986 1987    Object [] oPKOr = { }; 1988    Object [] oPKTr = { }; 1989 1990    oDS.update(oPKOr, oPKTr, 0); 1991    oDS.clear(); 1992 1993    oStrLog.append("New WorkArea " + sTargetWorkAreaId + " created successfully\n"); 1994 1995    // *********************************************************** 1996 // Give permissions to domain administrators over applications 1997 String sSQL; 1998 1999    oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 2000    LinkedList oApps = new LinkedList (); 2001    sSQL = "SELECT id_app FROM k_x_app_workarea WHERE gu_workarea='" + sSourceWorkAreaId + "'"; 2002    oRSet = oStmt.executeQuery(sSQL); 2003    while (oRSet.next()) oApps.add(oRSet.getObject(1)); 2004    oRSet.close(); 2005    oStmt.close(); 2006 2007    ListIterator oIter = oApps.listIterator(); 2008    oPrep = oConn.prepareStatement("DELETE FROM k_x_app_workarea WHERE gu_workarea='" + sTargetWorkAreaId + "' AND id_app=?"); 2009    while (oIter.hasNext()) { 2010      oPrep.setObject(1, oIter.next()); 2011      oPrep.executeUpdate(); 2012    } 2013    oPrep.close(); 2014    oIter = null; 2015    oApps = null; 2016 2017    oStmt = oConn.createStatement(); 2018    sSQL = "INSERT INTO k_x_app_workarea (id_app,gu_workarea,gu_admins,path_files) SELECT id_app,'" + sTargetWorkAreaId + "','" + sAdminId + "','" + aTargetWrkA[1].toLowerCase() + "' FROM k_x_app_workarea WHERE gu_workarea='" + sSourceWorkAreaId + "'"; 2019 2020    oStrLog.append("Statement.executeUpdate(" + sSQL + ")\n"); 2021    if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(" + sSQL + ")"); 2022 2023    oStmt.executeUpdate(sSQL); 2024 2025    oStmt.close(); 2026 2027    if (DebugFile.trace) { 2028      DebugFile.decIdent(); 2029      DebugFile.writeln("End ModelManager.cloneWorkArea() : " + sTargetWorkAreaId); 2030    } 2031 2032    return sTargetWorkAreaId; 2033  } // cloneWorkArea() 2034 2035  // ---------------------------------------------------------- 2036 2037  /** 2038   * <p>Drop WorkArea</p> 2039   * THIS METHOD DROPS ALL WORKAREA DATA. USE IT WITH CARE. 2040   * @param sDomainDotWorkAreaNm Domain Name and WorkArea Name with a middle dot. <i>Domain_Name.WorkArea_Name</i> 2041   * @param oProps Environment properties (as readed from hipergate.cnf) 2042   * @return GUID of droped WorkArea or <b>null</b> if WorkArea is not found. 2043   * @throws SQLException 2044   * @throws IOException 2045   * @see com.knowgate.workareas.WorkArea#delete (JDCConnection,String,java.util.Properties) 2046   */ 2047  public String dropWorkArea(String sDomainDotWorkAreaNm, Properties oProps) 2048      throws SQLException ,IOException ,Exception { 2049    com.knowgate.jdc.JDCConnection oJDC = new com.knowgate.jdc.JDCConnection(oConn, null); 2050 2051    if (DebugFile.trace) { 2052      DebugFile.writeln("Begin ModelManager.dropWorkArea(" + sDomainDotWorkAreaNm + ", ...)"); 2053      DebugFile.incIdent(); 2054    } 2055 2056    String [] aDomWrkA = com.knowgate.misc.Gadgets.split2 (sDomainDotWorkAreaNm, '.'); 2057 2058    int iDomainId = com.knowgate.acl.ACLDomain.getIdFromName(oJDC, aDomWrkA[0]); 2059 2060    String sWorkAreaId = com.knowgate.workareas.WorkArea.getIdFromName(oJDC, iDomainId, aDomWrkA[1]); 2061 2062    if (null!=sWorkAreaId) 2063      if (oProps==null) 2064        com.knowgate.workareas.WorkArea.delete (oJDC, sWorkAreaId); 2065      else 2066        com.knowgate.workareas.WorkArea.delete (oJDC, sWorkAreaId, oProps); 2067 2068    if (DebugFile.trace) { 2069      DebugFile.decIdent(); 2070      DebugFile.writeln("End ModelManager.dropWorkArea() : " + sWorkAreaId); 2071    } 2072 2073    return sWorkAreaId; 2074  } // dropWorkArea 2075 2076  // ---------------------------------------------------------- 2077 2078  /** 2079   * <p>Drop Domain</p> 2080   * Drop a Domain, all its WorkAreas and associated data.<br> 2081   * Internally executes scripts/domain_drop.js Java BeanShell Script contained 2082   * inside JAR file under com/knowgate/hipergate/datamodel.<br> 2083   * THIS METHOD DROPS ALL DOMAIN DATA. USE IT WITH CARE.<br> 2084   * @param sDomainNm Name of domain to be droped. 2085   * @return Numeric identifier for droped domain. 2086   * @throws EvalError 2087   * @throws SQLException 2088   * @throws IOException 2089   * @see com.knowgate.acl.ACLDomain#delete(JDCConnection,int); 2090   */ 2091  public int dropDomain(String sDomainNm) throws EvalError,SQLException ,IOException { 2092 2093    if (DebugFile.trace) { 2094      DebugFile.writeln("Begin ModelManager.dropDomain(" + sDomainNm + ")"); 2095      DebugFile.incIdent(); 2096    } 2097 2098    Interpreter oInterpreter = new Interpreter(); 2099 2100    oInterpreter.set ("DomainNm", sDomainNm); 2101    oInterpreter.set ("DefaultConnection", oConn); 2102 2103    oInterpreter.eval(getResourceAsString("scripts/domain_drop.js", sEncoding)); 2104 2105    Object obj = oInterpreter.get("ErrorCode"); 2106 2107    Integer oCodError = (Integer ) oInterpreter.get("ErrorCode"); 2108 2109    if (oCodError.compareTo(new Integer (0))!=0) { 2110      iErrors++; 2111      oStrLog.append("EvalError: " + oInterpreter.get("ErrorMessage") + "\n"); 2112 2113      throw new SQLException ((String ) oInterpreter.get("ErrorMessage")); 2114    } 2115 2116    if (DebugFile.trace) { 2117      DebugFile.decIdent(); 2118      DebugFile.writeln("End ModelManager.dropDomain()"); 2119    } 2120 2121    if (oInterpreter.get("ReturnValue")!=null) 2122      return ( (Integer ) oInterpreter.get("ReturnValue")).intValue(); 2123    else 2124      return 0; 2125  } // dropDomain 2126 2127  // ---------------------------------------------------------- 2128 2129  /** 2130   * <p>Create New Domain</p> 2131   * Internally executes scripts/domain_create.js Java BeanShell Script contained 2132   * inside JAR file under com/knowgate/hipergate/datamodel.<br> 2133   * @param sDomainNm New Domain Name 2134   * @return Autogenerated unique numeric identifier for new domain 2135   * @throws EvalError Java BeanShell script domain_create.js as a syntax error 2136   * @throws IOException 2137   * @throws FileNotFoundException 2138   * @throws SQLException 2139   */ 2140  public int createDomain(String sDomainNm) 2141    throws EvalError, IOException , FileNotFoundException , SQLException { 2142    String sErrMsg; 2143 2144    if (DebugFile.trace) { 2145      DebugFile.writeln("Begin ModelManager.createDomain(" + sDomainNm + ")"); 2146      DebugFile.incIdent(); 2147    } 2148 2149    int iDominId = 0; 2150    int iRetVal; 2151 2152    Interpreter oInterpreter = new Interpreter(); 2153 2154    oInterpreter.set ("DomainNm", sDomainNm); 2155    oInterpreter.set ("DefaultConnection", oConn); 2156    oInterpreter.set ("AlternativeConnection", oConn); 2157 2158    if (DebugFile.trace) DebugFile.writeln("Interpreter.eval(getResourceAsString(scripts/domain_create.js,"+sEncoding); 2159 2160    oInterpreter.eval(getResourceAsString("scripts/domain_create.js", sEncoding)); 2161 2162    Object obj = oInterpreter.get("ErrorCode"); 2163 2164    Integer oCodError = (Integer ) oInterpreter.get("ErrorCode"); 2165 2166    if (oCodError.intValue()!=0) { 2167      sErrMsg = (String ) oInterpreter.get("ErrorMessage"); 2168      iErrors++; 2169      oStrLog.append("EvalError: " + sErrMsg + "\n"); 2170      if (DebugFile.trace) { 2171        DebugFile.writeln("SQLException "+sErrMsg); 2172        DebugFile.decIdent(); 2173      } 2174      throw new SQLException (sErrMsg); 2175    } // fi () 2176 2177    obj = oInterpreter.get("ReturnValue"); 2178 2179    if ( null != obj ) { 2180      iDominId = ( (Integer ) obj).intValue(); 2181 2182      Statement oStmt = oConn.createStatement(); 2183      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE k_workareas SET nm_workarea='" + sDomainNm.toLowerCase() + "_default' WHERE id_domain=" + String.valueOf(iDominId) + " AND nm_workarea='model_default')"); 2184      oStmt.executeUpdate("UPDATE k_workareas SET nm_workarea='" + sDomainNm.toLowerCase() + "_default' WHERE id_domain=" + String.valueOf(iDominId) + " AND nm_workarea='model_default'"); 2185      oStmt.close(); 2186 2187      oStrLog.append("New Domain " + oInterpreter.get("ReturnValue") + " created successfully\n"); 2188      iRetVal = iDominId; 2189    } 2190    else { 2191      oStrLog.append( oInterpreter.get("ErrorMessage") + ": Domain not created."); 2192      iRetVal = 0; 2193    } 2194 2195    if (DebugFile.trace) { 2196      DebugFile.decIdent(); 2197      DebugFile.writeln("End ModelManager.createDomain() : " + String.valueOf(iRetVal)); 2198    } 2199 2200    return iRetVal; 2201  } // createDomain 2202 2203  // ---------------------------------------------------------- 2204 2205  private LinkedList listConstraints (Connection oJCon) 2206    throws SQLException { 2207 2208    if (DebugFile.trace) { 2209      DebugFile.writeln("Begin ModelManager.listConstraints()"); 2210      DebugFile.incIdent(); 2211    } 2212 2213    LinkedList oConstraintList = new LinkedList (); 2214    Statement oStmt = oJCon.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 2215    ResultSet oRSet = null; 2216    int iCount = 0; 2217    String sSQL = null; 2218 2219    switch (iDbms) { 2220      case DBMS_MSSQL: 2221        sSQL = "SELECT foreignkey.name AS constraintname,foreigntable.name AS tablename FROM sysforeignkeys sysfks, sysobjects foreignkey, sysobjects foreigntable WHERE sysfks.constid=foreignkey.id AND sysfks.fkeyid=foreigntable.id"; 2222        break; 2223      case DBMS_POSTGRESQL: 2224        sSQL = "SELECT c.conname,t.relname FROM pg_constraint c, pg_class t WHERE c.conrelid=t.oid AND c.contype='f'"; 2225        break; 2226      case DBMS_ORACLE: 2227        sSQL = "SELECT CONSTRAINT_NAME,TABLE_NAME FROM USER_CONSTRAINTS WHERE R_CONSTRAINT_NAME IS NOT NULL"; 2228        break; 2229    } 2230 2231    if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSQL + ")"); 2232 2233    oRSet = oStmt.executeQuery (sSQL); 2234 2235    while (oRSet.next()) { 2236      oConstraintList.add (new Constraint(oRSet.getString(1),oRSet.getString(2))); 2237      iCount++; 2238    } 2239 2240    oRSet.close(); 2241    oStmt.close(); 2242 2243    if (DebugFile.trace) { 2244      DebugFile.decIdent(); 2245      DebugFile.writeln("End ModelManager.listConstraints() : " + String.valueOf(iCount)); 2246    } 2247 2248    return oConstraintList; 2249  } // listConstraints 2250 2251  // ---------------------------------------------------------- 2252 2253  private void upgrade1x2x (Connection jCon1, Connection jCon2, Properties oProps) 2254    throws IllegalStateException , SQLException { 2255 2256    if (DebugFile.trace) { 2257      DebugFile.writeln("Begin ModelManager.upgrade1x2x()"); 2258      DebugFile.incIdent(); 2259    } 2260 2261     String [] sTables = new String []{ 2262         // Kernel 2263 "k_classes","k_sequences", 2264         // Lookups 2265 "k_lu_meta_attrs", 2266         // Security 2267 "k_lu_permissions","k_domains","k_users","k_acl_groups","k_x_group_user","k_apps","k_workareas","k_x_app_workarea", 2268         // Jobs 2269 "k_lu_job_commands","k_lu_job_status","k_jobs","k_job_atoms","k_job_atoms_archived","k_queries", 2270         // Categories 2271 "k_categories","k_cat_labels","k_cat_root","k_cat_tree","k_x_cat_group_acl","k_x_cat_objs","k_x_cat_user_acl", 2272         // Thesauri 2273 "k_thesauri","k_thesauri_root","k_images","k_addresses_lookup","k_addresses","k_bank_accounts", 2274         // Products 2275 "k_products","k_prod_attr","k_prod_attrs","k_prod_keywords","k_prod_locats", 2276         // Addrbook 2277 "k_lu_fellow_titles","k_fellows_lookup","k_fellows", "k_fellows_attach", 2278         "k_rooms_lookup","k_rooms", 2279         "k_meetings","k_x_meeting_contact","k_x_meeting_fellow","k_x_meeting_room", 2280         // Forums 2281 "k_newsgroups","k_newsmsgs", 2282         // CRM 2283 "k_companies_lookup","k_companies","k_companies_attrs","k_x_company_addr","k_x_company_bank", 2284         "k_contacts_lookup","k_contacts","k_contact_attachs","k_contact_notes","k_contacts_attrs","k_x_contact_addr","k_x_contact_bank", 2285         "k_oportunities_lookup","k_oportunities","k_oportunities_attrs", 2286         "k_sales_men","k_sales_objectives", 2287         // Lists 2288 "k_lists","k_list_members", 2289         // Webbuilder 2290 "k_microsites","k_pagesets_lookup","k_pagesets","k_pageset_pages", 2291         // Projects 2292 "k_projects_lookup","k_projects", 2293         "k_duties_lookup","k_duties","k_duties_attach","k_x_duty_resource", 2294         "k_bugs_lookup","k_bugs","k_bugs_attach", 2295         // Shop 2296 "k_shops","k_warehouses","k_sale_points", 2297         "k_orders_lookup","k_orders","k_order_lines", 2298         "k_invoices_lookup","k_invoices","k_invoice_schedules","k_invoice_lines", 2299         "k_x_orders_invoices" }; 2300 2301     final int iTables = sTables.length; 2302 2303     Statement oStmt; 2304 2305     // ******************************************************** 2306 // Drop f1_domains foreign key constraint before proceeding 2307 2308     if (DebugFile.trace) { 2309       DebugFile.writeln("Statement.execute(ALTER TABLE k_domains DROP CONSTRAINT f1_domains)"); 2310     } 2311 2312     oStmt = jCon2.createStatement(); 2313     oStmt.execute("ALTER TABLE k_domains DROP CONSTRAINT f1_domains"); 2314     oStmt.close(); 2315 2316     // **************************************************** 2317 // Disable all foreign key constraint before proceeding 2318 2319     LinkedList oConstraints = listConstraints(jCon2); 2320 2321     ListIterator oIter; 2322     Constraint oCons; 2323     String sSQL = null; 2324 2325     try { 2326 2327       oStmt = oConn.createStatement(); 2328 2329       oIter = oConstraints.listIterator(); 2330 2331       while (oIter.hasNext()) { 2332         oCons = (Constraint) oIter.next(); 2333 2334         switch (iDbms) { 2335           case DBMS_MSSQL: 2336             sSQL = "ALTER TABLE " + oCons.tablename + " NOCHECK CONSTRAINT " + oCons.constraintname; 2337             break; 2338           case DBMS_POSTGRESQL: 2339             sSQL = "UPDATE pg_class SET reltriggers=0 WHERE relname = '" + oCons.tablename + "'"; 2340             break; 2341           case DBMS_ORACLE: 2342             sSQL = "ALTER TABLE " + oCons.tablename + " DISABLE CONSTRAINT " + oCons.constraintname; 2343             break; 2344         } 2345 2346         if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")"); 2347 2348         oStmt.execute(sSQL); 2349       } // wend 2350 oStmt.close(); 2351       sSQL = null; 2352     } 2353     catch (SQLException sqle) { 2354       DebugFile.writeln("SQLException: " + sqle.getMessage() + " " + sSQL + "\n"); 2355       iErrors++; 2356       oStrLog.append("SQLException: " + sqle.getMessage() + " " + sSQL + "\n"); 2357     } 2358 2359     // ******************************************** 2360 // Copy data from 1.x version datamodel to v2.0 2361 2362     com.knowgate.datacopy.CopyRegisters oCopy = new com.knowgate.datacopy.CopyRegisters(oProps.getProperty("schema1"), jCon1.getCatalog()); 2363 2364     for (int t=0; t<iTables; t++) { 2365       try { 2366         if (DBMS_ORACLE==iDbms) sTables[t] = sTables[t].toUpperCase(); 2367 2368         int iAppended = oCopy.append (jCon1, jCon2, sTables[t], sTables[t], null); 2369 2370         oStrLog.append(String.valueOf(iAppended) + " registers appended or updated at table " + sTables[t] + "\n"); 2371       } 2372       catch (SQLException sqle) { 2373         DebugFile.writeln("SQLException: CopyRegisters.append(" + sTables[t] + ") " + sqle.getMessage() + "\n"); 2374         DebugFile.decIdent(); 2375         iErrors++; 2376         oStrLog.append("SQLException: CopyRegisters.append(" + sTables[t] + ") " + sqle.getMessage() + " " + "\n"); 2377       } 2378     } // next (t) 2379 2380     // ************************************ 2381 // Additional tables witout primary key 2382 2383     PreparedStatement oWrtStm; 2384     Statement oReadStm; 2385     ResultSet oReadSet; 2386 2387     oWrtStm = jCon2.prepareStatement("INSERT INTO k_cat_expand (gu_rootcat,gu_category,od_level,od_walk,gu_parent_cat) VALUES (?,?,?,?,?)"); 2388 2389     oReadStm = jCon1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 2390     oReadSet = oReadStm.executeQuery("SELECT gu_rootcat,gu_category,od_level,od_walk,gu_parent_cat FROM k_cat_expand"); 2391 2392     while (oReadSet.next()) { 2393       oWrtStm.setObject(1, oReadSet.getObject(1)); 2394       oWrtStm.setObject(2, oReadSet.getObject(2)); 2395       oWrtStm.setObject(3, oReadSet.getObject(3)); 2396       oWrtStm.setObject(4, oReadSet.getObject(4)); 2397       oWrtStm.setObject(5, oReadSet.getObject(5)); 2398       oWrtStm.executeUpdate(); 2399     } // wend 2400 2401     oReadSet.close(); 2402     oReadStm.close(); 2403     oWrtStm.close(); 2404 2405     oWrtStm = jCon2.prepareStatement("INSERT INTO k_project_expand (gu_rootprj,gu_project,nm_project,od_level,od_walk,gu_parent) VALUES (?,?,?,?,?,?)"); 2406 2407     oReadStm = jCon1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 2408     oReadSet = oReadStm.executeQuery("SELECT gu_rootprj,gu_project,nm_project,od_level,od_walk,gu_parent FROM k_project_expand"); 2409 2410     while (oReadSet.next()) { 2411       oWrtStm.setObject(1, oReadSet.getObject(1)); 2412       oWrtStm.setObject(2, oReadSet.getObject(2)); 2413       oWrtStm.setObject(3, oReadSet.getObject(3)); 2414       oWrtStm.setObject(4, oReadSet.getObject(4)); 2415       oWrtStm.setObject(5, oReadSet.getObject(5)); 2416       oWrtStm.setObject(6, oReadSet.getObject(6)); 2417       oWrtStm.executeUpdate(); 2418     } // wend 2419 2420     oReadSet.close(); 2421     oReadStm.close(); 2422     oWrtStm.close(); 2423 2424     oWrtStm = jCon2.prepareStatement("INSERT INTO k_x_list_members (gu_list,tx_email,tx_name,tx_surname,tx_salutation,bo_active,dt_created,tp_member,gu_company,gu_contact,id_format,dt_modified) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"); 2425 2426     oReadStm = jCon1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 2427     oReadSet = oReadStm.executeQuery("SELECT gu_list,tx_email,tx_name,tx_surname,tx_salutation,bo_active,dt_created,tp_member,gu_company,gu_contact,id_format,dt_modified FROM k_x_list_members"); 2428 2429     while (oReadSet.next()) { 2430       oWrtStm.setObject(1, oReadSet.getObject(1)); 2431       oWrtStm.setObject(2, oReadSet.getObject(2)); 2432       oWrtStm.setObject(3, oReadSet.getObject(3)); 2433       oWrtStm.setObject(4, oReadSet.getObject(4)); 2434       oWrtStm.setObject(5, oReadSet.getObject(5)); 2435       oWrtStm.setObject(6, oReadSet.getObject(6)); 2436       oWrtStm.setObject(7, oReadSet.getObject(7)); 2437       oWrtStm.setObject(8, oReadSet.getObject(8)); 2438       oWrtStm.setObject(9, oReadSet.getObject(9)); 2439       oWrtStm.setObject(10, oReadSet.getObject(10)); 2440       oWrtStm.setObject(11, oReadSet.getObject(11)); 2441       oWrtStm.setObject(12, oReadSet.getObject(12)); 2442       oWrtStm.executeUpdate(); 2443     } // wend 2444 2445     oReadSet.close(); 2446     oReadStm.close(); 2447     oWrtStm.close(); 2448 2449     // ************************************************** 2450 // Enable all foreign key constraint after processing 2451 2452     try { 2453 2454       oStmt = oConn.createStatement(); 2455 2456       oIter = oConstraints.listIterator(); 2457 2458       while (oIter.hasNext()) { 2459         oCons = (Constraint) oIter.next(); 2460 2461         switch (iDbms) { 2462           case DBMS_MSSQL: 2463             sSQL = "ALTER TABLE " + oCons.tablename + " CHECK CONSTRAINT " + oCons.constraintname; 2464             break; 2465           case DBMS_POSTGRESQL: 2466             sSQL = "UPDATE pg_class SET reltriggers = COUNT(*) FROM pg_trigger WHERE pg_class.oid=tgrelid AND relname='" + oCons.tablename + "'"; 2467             break; 2468           case DBMS_ORACLE: 2469             sSQL = "ALTER TABLE " + oCons.tablename + " ENABLE CONSTRAINT " + oCons.constraintname; 2470             break; 2471         } 2472 2473         if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")"); 2474 2475         oStmt.execute(sSQL); 2476       } // wend 2477 oStmt.close(); 2478       sSQL = null; 2479     } 2480     catch (SQLException sqle) { 2481       DebugFile.writeln("SQLException: " + sqle.getMessage() + " " + sSQL + "\n"); 2482       iErrors++; 2483       oStrLog.append("SQLException: " + sqle.getMessage() + " " + sSQL + "\n"); 2484     } 2485 2486     if (DebugFile.trace) { 2487       DebugFile.decIdent(); 2488       DebugFile.writeln("End ModelManager.upgrade1x2x()"); 2489     } 2490  } // upgrade1x2x 2491 2492  // ---------------------------------------------------------- 2493 2494  public void upgrade(String sOldVersion, String sNewVersion, Properties oProps) 2495    throws IllegalStateException , SQLException , FileNotFoundException , IOException { 2496 2497    Statement oStmt; 2498    ResultSet oRSet; 2499    String sDetectedVersion; 2500 2501    try { 2502      com.knowgate.jdc.JDCConnection jCon = new com.knowgate.jdc.JDCConnection(getConnection(), null); 2503 2504      if (sOldVersion.equals("105") && sNewVersion.equals("110")) { 2505        executeBulk("upgrade/" + sDbms + "/" + sOldVersion + "-" + sNewVersion + ".ddl", BULK_STATEMENTS); 2506      } 2507      else if ((sOldVersion.equals("200") || sOldVersion.equals("201")) && sNewVersion.equals("202")) { 2508        executeBulk("upgrade/" + sDbms + "/200-202.ddl", BULK_PLSQL); 2509      } 2510      else if ((sOldVersion.equals("105") || sOldVersion.equals("110")) && 2511               (sNewVersion.equals("200") || sNewVersion.equals("201") || sNewVersion.equals("202"))) { 2512 2513        Connection jCon2 = getConnection(); 2514 2515        oStmt = jCon2.createStatement(); 2516 2517        try { 2518          oRSet = oStmt.executeQuery("SELECT vs_stamp FROM k_version"); 2519          if (oRSet.next()) 2520            sDetectedVersion = oRSet.getString(1); 2521          else 2522            sDetectedVersion = "unknown"; 2523          oRSet.close(); 2524        } 2525        catch (SQLException sqle) { 2526          sDetectedVersion = "1.0.5"; 2527        } 2528        oStmt.close(); 2529 2530        if (DebugFile.trace) { 2531          DebugFile.writeln("Target model current version is " + sDetectedVersion); 2532        } 2533 2534        if (!sDetectedVersion.startsWith("2.")) { 2535          throw new SQLException ("Target database cannot be recognized as hipergate 2.x version"); 2536        } 2537 2538        if (DebugFile.trace) { 2539          DebugFile.writeln("DriverManager.getConnection(" + oProps.getProperty("dburl1") + "," + oProps.getProperty("dbuser1") + ")"); 2540        } 2541 2542        Connection jCon1 = DriverManager.getConnection(oProps.getProperty("dburl1"), oProps.getProperty("dbuser1"), oProps.getProperty("dbpassword1")); 2543 2544        oStmt = jCon1.createStatement(); 2545 2546        try { 2547          oRSet = oStmt.executeQuery("SELECT vs_stamp FROM k_version"); 2548          if (oRSet.next()) 2549            sDetectedVersion = oRSet.getString(1); 2550          else 2551            sDetectedVersion = "unknown"; 2552          oRSet.close(); 2553        } 2554        catch (SQLException sqle) { 2555          sDetectedVersion = "1.0.5"; 2556        } 2557        oStmt.close(); 2558 2559        if (DebugFile.trace) { 2560          DebugFile.writeln("Source model current version is " + sDetectedVersion); 2561        } 2562 2563        if (!sDetectedVersion.startsWith("1.")) { 2564          jCon1.close(); 2565          throw new SQLException ("Source database cannot be recognized as hipergate 1.x version"); 2566        } 2567 2568        jCon1.setAutoCommit(true); 2569 2570        upgrade1x2x (jCon1, jCon2, oProps); 2571 2572        jCon1.close(); 2573      } 2574      else if ((sOldVersion.equals("200") || sOldVersion.equals("201") || 2575                sOldVersion.equals("202") || sOldVersion.equals("203") || 2576                sOldVersion.equals("204"))&& sNewVersion.equals("208") ) { 2577 2578         if (sOldVersion.equals("200") || sOldVersion.equals("201")) 2579           executeBulk("upgrade/" + sDbms + "/200-202.ddl", BULK_PLSQL); 2580 2581         if (iDbms==DBMS_MSSQL) { 2582           executeBulk("drop/mssql/lists.sql", BULK_STATEMENTS); 2583           executeBulk("procedures/mssql/lists.ddl", BULK_PLSQL); 2584         } 2585      } 2586      else if ((sOldVersion.equals("200") || sOldVersion.equals("201") || 2587                sOldVersion.equals("202") || sOldVersion.equals("203") || 2588                sOldVersion.equals("204") || sNewVersion.equals("208"))&& 2589                sNewVersion.equals("210")) { 2590         executeBulk("upgrade/" + sDbms + "/208-210.ddl", BULK_PLSQL); 2591         create("hipermail"); 2592         if (iDbms==DBMS_ORACLE) recompileOrcl(); 2593      } 2594      else if (sOldVersion.equals("210") && 2595               sNewVersion.equals("300")) { 2596         executeBulk("upgrade/" + sDbms + "/210-300.ddl", BULK_PLSQL); 2597         if (iDbms==DBMS_ORACLE) recompileOrcl(); 2598      } 2599      else 2600        throw new SQLException ("ERROR: ModelManager.upgrade() Source or Target version not recognized."); 2601 2602    } catch (InterruptedException ie) { 2603      oStrLog.append("STOP ON ERROR SET TO ON: SCRIPT INTERRUPTED\n"); 2604    } 2605  } // upgrade 2606 2607  // ---------------------------------------------------------- 2608 2609  private static void printUsage() { 2610    System.out.println(""); 2611    System.out.println("Usage:\n"); 2612    System.out.println("Creating and dropping the database"); 2613    System.out.println("ModelManager cnf_path command {database|all|module_name|domain|workarea} [domain_name|domain_name.workarea_name] [verbose]\n"); 2614    System.out.println("Cloning workareas"); 2615    System.out.println("ModelManager cnf_path clone workarea domain_name.workarea_name domain_name.workarea_name [verbose]\n"); 2616    System.out.println("Executing a SQL script"); 2617    System.out.println("ModelManager cnf_path execute sql_script_path\n"); 2618    System.out.println("Generating SQL scripts for a table data"); 2619    System.out.println("ModelManager cnf_path script table_name output_path\n"); 2620    System.out.println("Parameters"); 2621    System.out.println("cnf_path: path to hipergate.cnf file ej. /opt/knowgate/hipergate.cnf"); 2622    System.out.println("output_path: path where SQL statements for inserting data on a table will be generated ej. /tmp/x_companies.sql"); 2623    System.out.println("command: { create | drop | clone | execute }"); 2624    System.out.println("module: { all | kernel | lookups | security | jobs | categories | addrbook | webbuilder | crm | shops | projtrack }"); 2625    System.out.println("domain_name: name of domain to create or drop"); 2626    System.out.println("workarea_name: name of workarea to drop"); 2627    System.out.println("verbose: show executed SQL"); 2628  } 2629 2630  // ---------------------------------------------------------- 2631 2632  /** 2633   * <p>Method for calling ModelManager from the command line</p> 2634   * Usage:<br> 2635   * java com.knowgate.hipergate.datamodel.ModelManager /etc/hipergate.cnf <i>command</i> <i>module</i> [domain_name|domain_name.workarea_name] [verbose]<br> 2636   * Path "/etc/hipergate.cnf" must point to where hipergate.cnf file is located.<br> 2637   * @param argv Array of Strings with 3 to 6 elements<br> 2638   * <b>argv[0]</b>: (Command) May be { create | drop | clone | execute | script | upgrade }<br> 2639   * <b>argv[1]</b>: (Object) May be { database | <i>a module_name</i> | all | domain | workarea }<br> 2640   * Valid module names are: { { all | kernel | lookups | security | jobs | categories | addrbook | webbuilder | crm | shops | projtrack }<br> 2641   * "all" will create or drop all modules.<br> 2642   * Example for initial database creation :<br> 2643   * java com.knowgate.hipergate.datamodel.ModelManager /etc/hipergate.cnf create database<br> 2644   * Example for version upgrade script:<br> 2645   * java com.knowgate.hipergate.datamodel.ModelManager /etc/hipergate.cnf upgrade 105 110 verbose<br> 2646   * java com.knowgate.hipergate.datamodel.ModelManager /etc/hipergate.cnf execute /tmp/statements.sql verbose<br> 2647   * Example for generating a SQL script for a table :<br> 2648   * java com.knowgate.hipergate.datamodel.ModelManager /etc/hipergate.cnf script k_lu_languages /tmp/langs.sql<br> 2649   */ 2650  public static void main(String [] argv) { 2651    ModelManager oMan = new ModelManager(); 2652    FileInputStream oInStrm; 2653    Properties oProps; 2654 2655    if (argv.length<3 || argv.length>6) 2656      printUsage(); 2657    else if (!argv[1].equals("create") && !argv[1].equals("drop") && !argv[1].equals("clone") && !argv[1].equals("execute") && !argv[1].equals("script") && !argv[1].equals("upgrade")) 2658      printUsage(); 2659    else if ((argv[1].equals("create") || argv[1].equals("drop")) && argv.length>5) 2660      printUsage(); 2661    else if (argv[1].equals("execute") && argv.length>4) 2662      printUsage(); 2663    else if (argv[1].equals("script") && argv.length>4) 2664      printUsage(); 2665    else if (argv[1].equals("clone") && argv.length<5) 2666      printUsage(); 2667    else if (argv[1].equals("upgrade") && argv.length<4) 2668      printUsage(); 2669    else { 2670      oProps = new Properties (); 2671      oInStrm = null; 2672 2673      try { 2674        oInStrm = new FileInputStream (argv[0]); 2675        oProps.load(oInStrm); 2676        oInStrm.close(); 2677 2678        oMan.connect (oProps.getProperty("driver"), oProps.getProperty("dburl"), oProps.getProperty("schema",""), oProps.getProperty("dbuser"), oProps.getProperty("dbpassword")); 2679 2680        if (argv[1].equals("create")) { 2681 2682          if (argv[2].equals("domain")) 2683            if (argv.length<4) 2684              printUsage(); 2685            else if (argv[3].equals("verbose")) 2686              printUsage(); 2687            else 2688              oMan.createDomain(argv[3]); 2689 2690          else if (argv[2].equals("all")) 2691 2692            oMan.createAll(); 2693 2694          else if (argv[2].equals("database")) { 2695 2696            if (oProps.getProperty("dburl1")==null) { 2697              oMan.createDefaultDatabase(); 2698            } 2699            else { 2700              oMan.createAll(); 2701              oMan.upgrade("110", "200", oProps); 2702            } 2703          } 2704          else 2705            oMan.create (argv[2]); 2706 2707        } else if (argv[1].equals("clone")) { 2708 2709          if (argv[2].equals("workarea")) { 2710            oMan.cloneWorkArea(argv[3], argv[4]); 2711          } 2712          else 2713            printUsage(); 2714 2715        } else if (argv[1].equals("drop")) { 2716 2717          if (argv[2].equals("domain")) 2718            if (argv.length<4) 2719              printUsage(); 2720            else if (argv[3].equals("verbose")) 2721              printUsage(); 2722            else 2723              oMan.dropDomain(argv[3]); 2724 2725          if (argv[2].equals("workarea")) 2726            if (argv.length<4) 2727              printUsage(); 2728            else if (argv[3].equals("verbose")) 2729              printUsage(); 2730            else if (argv[3].indexOf('.')<0) 2731              printUsage(); 2732            else 2733              oMan.dropWorkArea(argv[3], oProps); 2734 2735          else if (argv[2].equals("all") || argv[2].equals("database")) 2736            oMan.dropAll(); 2737          else 2738            oMan.drop (argv[2]); 2739 2740        } else if (argv[1].equals("execute")) { 2741          oMan.executeBulk(argv[2], FILE_STATEMENTS); 2742        } 2743         else if (argv[1].equals("script")) { 2744          oMan.scriptData(argv[2], null, argv[3]); 2745        } 2746        else if (argv[1].equals("upgrade")) { 2747          oMan.upgrade(argv[2],argv[3], oProps); 2748        } 2749 2750        switch (argv.length) { 2751          case 4: 2752            if (argv[3].equals("verbose")) 2753              System.out.println(oMan.oStrLog.toString()); 2754            break; 2755          case 5: 2756            if (argv[4].equals("verbose")) 2757              System.out.println(oMan.oStrLog.toString()); 2758            break; 2759          case 6: 2760            if (argv[5].equals("verbose")) 2761              System.out.println(oMan.oStrLog.toString()); 2762            break; 2763        } 2764 2765        oMan.disconnect(); 2766      } 2767      catch (org.xml.sax.SAXException saxe) { 2768        System.out.print(argv[0] + " SAXException " + saxe.getMessage()); 2769      } 2770      catch (InstantiationException inste) { 2771        System.out.print(argv[0] + " InstantiationException " + inste.getMessage()); 2772      } 2773      catch (IllegalAccessException ille) { 2774        System.out.print(argv[0] + " IllegalAccessException " + ille.getMessage()); 2775      } 2776      catch (FileNotFoundException fnfe) { 2777        System.out.print(argv[0] + " FileNotFoundException " + fnfe.getMessage()); 2778      } 2779      catch (IOException ioe) { 2780        System.out.print("IOException " + ioe.getMessage() + " not found"); 2781      } 2782      catch (ClassNotFoundException cnfe) { 2783        System.out.print(cnfe.getMessage() + " Check class for JDBC driver " + oProps.getProperty("driver")); 2784      } 2785      catch (SQLException sqle) { 2786        System.out.println(sqle.getMessage()); 2787      } 2788      catch (EvalError eval) { 2789        System.out.print("EvalError: " + eval.getErrorText() + "\n at line " + String.valueOf(eval.getErrorLineNumber()) + "\n" + eval.getScriptStackTrace() + "\n"); 2790      } 2791      catch (ArrayIndexOutOfBoundsException aiob) { 2792        System.out.print("ArrayIndexOutOfBoundsException " + aiob.getMessage()); 2793      } 2794      catch (NullPointerException npe) { 2795        System.out.print("NullPointerException " + npe.getMessage()); 2796      } 2797      catch (Exception xcpt) { 2798        System.out.print("Exception " + xcpt.getMessage()); 2799      } 2800    } 2801  } 2802 2803} // ModelManager 2804

A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame Popular Tags