Workspace/Siman-Common/src/org/splat/dal/dao/som/Database.java

   1 package org.splat.dal.dao.som;
   2
   3 /**
   4  *
   5  * @author    Daniel Brunier-Coulin
   6  * @copyright OPEN CASCADE 2012
   7  */
   8
   9 import java.text.SimpleDateFormat;
  10 import java.util.Date;
  11 import java.util.List;
  12 import java.util.Properties;
  13 import java.io.File;
  14 import java.io.IOException;
  15 import java.sql.Connection;
  16 import java.sql.DatabaseMetaData;
  17 import java.sql.ResultSet;
  18 import java.sql.SQLException;
  19
  20 import org.hibernate.Query;
  21 import org.hibernate.Session;
  22 import org.hibernate.SessionFactory;
  23 import org.hibernate.jdbc.Work;
  24 import org.apache.log4j.Logger;
  25
  26 import org.splat.dal.bo.kernel.User;
  27 import org.splat.dal.bo.som.Document;
  28 import org.splat.dal.bo.som.IDBuilder;
  29 import org.splat.dal.bo.som.KnowledgeElement;
  30 import org.splat.dal.bo.som.ProgressState;
  31 import org.splat.dal.bo.som.SimulationContext;
  32 import org.splat.dal.bo.som.SimulationContextType;
  33 import org.splat.dal.bo.som.Study;
  34 import org.splat.kernel.UserDirectory;
  35 import org.splat.kernel.InvalidPropertyException;
  36 import org.splat.service.technical.IndexService;
  37 import org.splat.service.technical.RepositoryService;
  38
  39 public class Database extends org.splat.dal.dao.kernel.Database {
  40
  41         private int uplevel = 0; // Level of database upgrade
  42         private String basepath = null; // Path of the root directory of repository
  43         private RepositoryService _repositoryService;
  44         private IndexService _indexService;
  45         private SessionFactory _sessionFactory;
  46
  47         private static Database my = null; // Singleton instance
  48
  49         protected class CreateTables extends
  50                         org.splat.dal.dao.kernel.Database.CreateTables {
  51                 // ---------------------------------------------------------------------------
  52                 public void execute(Connection connex) throws SQLException {
  53                         super.execute(connex);
  54
  55                         // Study Entity
  56                         String create = "CREATE TABLE `study` ("
  57                                         + "`rid`      int(10)  UNSIGNED NOT NULL,"
  58                                         + "`sid`      tinytext NOT NULL,"
  59                                         + "`title`    tinytext NOT NULL,"
  60                                         + "`state`    enum('inWORK','inDRAFT','inCHECK','APPROVED', 'TEMPLATE') NOT NULL default 'inWORK',"
  61                                         + "`area`     enum('PRIVATE','PUBLIC','REFERENCE')                      NOT NULL default 'PRIVATE',"
  62                                         + "`manager`  int(10)  NOT NULL,"
  63                                         + "`version`  tinytext NOT NULL,"
  64                                         + "`docount`  int(10)  UNSIGNED NOT NULL,"
  65                                         + "`history`  int(10)  UNSIGNED NOT NULL,"
  66                                         + "`credate`  date     NOT NULL,"
  67                                         + "`lasdate`  date     NOT NULL," + "PRIMARY KEY (`rid`)"
  68                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
  69                         request.execute(create);
  70
  71                         // Scenario Entity
  72                         create = "CREATE TABLE `scenario` ("
  73                                         + "`rid`     int(10)  UNSIGNED NOT NULL,"
  74                                         + "`sid`     int(10)  UNSIGNED NOT NULL,"
  75                                         + "`owner`   int(10)  NOT NULL,"
  76                                         + "`scendex` int(3)   NOT NULL,"
  77                                         + "`title`   tinytext NOT NULL,"
  78                                         + "`manager` int(10)  NOT NULL," + "`cuser`   int(10),"
  79                                         + "`credate` date     NOT NULL,"
  80                                         + "`lasdate` date     NOT NULL," + "PRIMARY KEY (`rid`)"
  81                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
  82                         request.execute(create);
  83
  84                         // Document Entity and document tag (Publication)
  85                         create = "CREATE TABLE `document` ("
  86                                         + "`rid`     int(10)  UNSIGNED NOT NULL,"
  87                                         + "`did`     tinytext NOT NULL,"
  88                                         + "`type`    int(10)  NOT NULL,"
  89                                         + "`step`    int(10)  NOT NULL,"
  90                                         + "`state`   enum('inWORK','inDRAFT','inCHECK','APPROVED','EXTERN') NOT NULL default 'inWORK',"
  91                                         + "`name`    tinytext NOT NULL,"
  92                                         + "`author`  int(10)  NOT NULL," + "`version` tinytext,"
  93                                         + "`countag` int(10)  UNSIGNED NOT NULL,"
  94                                         + "`history` int(10)  NOT NULL,"
  95                                         + "`myfile`  int(10)  NOT NULL,"
  96                                         + "`lasdate` date  NOT NULL," + "PRIMARY KEY (`rid`)"
  97                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
  98                         request.execute(create);
  99                         create = "CREATE TABLE `doctag` ("
 100                                         + "`rid`     int(10)  UNSIGNED NOT NULL auto_increment,"
 101                                         + "`doc`     int(10)  NOT NULL,"
 102                                         + "`owner`   int(10)  NOT NULL,"
 103                                         + "`isnew`   char(1)  NOT NULL," + "PRIMARY KEY (`rid`)"
 104                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 105                         request.execute(create);
 106                         // Document types
 107                         create = "CREATE TABLE `doctype` ("
 108                                         + "`rid`    int(10)  UNSIGNED NOT NULL auto_increment,"
 109                                         + "`name`   tinytext NOT NULL,"
 110                                         + "`state`  enum('inCHECK','APPROVED') NOT NULL default 'inCHECK',"
 111                                         + "`step`   tinytext NOT NULL," + "`result` tinytext,"
 112                                         + "PRIMARY KEY (`rid`)"
 113                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 114                         request.execute(create);
 115                         // Document types dependencies
 116                         create = "CREATE TABLE `docuse` (" + "`owner` int(10) NOT NULL,"
 117                                         + "`rid`   int(10) NOT NULL"
 118                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 119                         request.execute(create);
 120
 121                         // ValidationCycle related object
 122                         create = "CREATE TABLE `cycle` ("
 123                                         + "`rid`   int(10)  UNSIGNED NOT NULL auto_increment,"
 124                                         + "`type`      int(10)  NOT NULL," + "`publisher` int(10),"
 125                                         + "`reviewer`  int(10)," + "`approver`  int(10),"
 126                                         + "`signatory` int(10)," + "PRIMARY KEY (`rid`)"
 127                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 128                         request.execute(create);
 129
 130                         // Timestamp related object
 131                         create = "CREATE TABLE `stamp` ("
 132                                         + "`rid`     int(10)  UNSIGNED NOT NULL auto_increment,"
 133                                         + "`type`    enum('PROMOTION','REVIEW','APPROVAL','ACCEPTANCE','DISTRIBUTION','REFUSAL') NOT NULL,"
 134                                         + "`author`  int(10)  NOT NULL,"
 135                                         + "`date`    datetime NOT NULL," + "PRIMARY KEY (`rid`)"
 136                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 137                         request.execute(create);
 138
 139                         // KnowledgeElements objects
 140                         create = "CREATE TABLE `knowelm` ("
 141                                         + "`rid`    int(10)  UNSIGNED NOT NULL auto_increment,"
 142                                         + "`type`   int(10)  NOT NULL,"
 143                                         + "`owner`  int(10)  NOT NULL,"
 144                                         + "`state`  enum('inWORK','inDRAFT','inCHECK','APPROVED') NOT NULL default 'inDRAFT',"
 145                                         + "`title`  tinytext NOT NULL,"
 146                                         + "`value`  text     NOT NULL,"
 147                                         + "`author` int(10)  NOT NULL,"
 148                                         + "`date`   date     NOT NULL," + "PRIMARY KEY (`rid`)"
 149                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 150                         request.execute(create);
 151                         // KnowledgeElement types
 152                         create = "CREATE TABLE `knowtype` ("
 153                                         + "`rid`  int(10) UNSIGNED NOT NULL auto_increment,"
 154                                         + "`name` tinytext NOT NULL,"
 155                                         + "`state`  enum('inWORK','inCHECK','APPROVED') NOT NULL default 'inCHECK',"
 156                                         + "PRIMARY KEY (`rid`)"
 157                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 158                         request.execute(create);
 159
 160                         // SimulationContext objects
 161                         create = "CREATE TABLE `contelm` ("
 162                                         + "`rid`     int(10) UNSIGNED NOT NULL auto_increment,"
 163                                         + "`type`    int(10) NOT NULL,"
 164                                         + "`step`    int(10) NOT NULL,"
 165                                         + "`state`   enum('inCHECK','APPROVED') NOT NULL default 'inCHECK',"
 166                                         + "`value`   text NOT NULL,"
 167                                         + "`counter` int(10)  UNSIGNED NOT NULL,"
 168                                         + "PRIMARY KEY (`rid`)"
 169                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 170                         request.execute(create);
 171                         // SimulationContext types
 172                         create = "CREATE TABLE `contype` ("
 173                                         + "`rid`  int(10)  UNSIGNED NOT NULL auto_increment,"
 174                                         + "`name` tinytext NOT NULL,"
 175                                         + "`state`  enum('inCHECK','APPROVED') NOT NULL default 'inCHECK',"
 176                                         + "`step` int(10)  NOT NULL," + "PRIMARY KEY (`rid`)"
 177                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 178                         request.execute(create);
 179
 180                         // Many-to-many association between ProjectElement (Study and Scenario) and SimulationContext
 181                         create = "CREATE TABLE `projext` (" + "`owner` int(10) NOT NULL,"
 182                                         + "`ordex` int(10) NOT NULL," + "`rid`   int(10) NOT NULL"
 183                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 184                         request.execute(create);
 185
 186                         // File objects
 187                         create = "CREATE TABLE `file` ("
 188                                         + "`rid`     int(10)  UNSIGNED NOT NULL,"
 189                                         + "`format`  tinytext NOT NULL,"
 190                                         + "`path`    tinytext NOT NULL,"
 191                                         + "`date`    date     NOT NULL," + "PRIMARY KEY (`rid`)"
 192                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 193                         request.execute(create);
 194
 195                         // Reference objects
 196                         create = "CREATE TABLE `refid` (" + "`cycle` int(10) NOT NULL,"
 197                                         + "`base`  int(10) NOT NULL," + "PRIMARY KEY (`cycle`)"
 198                                         + ") ENGINE=MyISAM DEFAULT CHARSET=latin1";
 199                         request.execute(create);
 200                 }
 201         }
 202
 203         protected class CheckVersion implements Work {
 204                 // --------------------------------------------
 205                 public void execute(Connection connex) throws SQLException {
 206                         DatabaseMetaData dbmdata = connex.getMetaData();
 207                         String dbname = connex.getCatalog();
 208                         ResultSet table;
 209
 210                         table = dbmdata.getTables(dbname, null, "study", null);
 211                         if (table.next())
 212                                 return;
 213                         uplevel = -1; // Database not initialized
 214                 }
 215         }
 216
 217         public final static Logger logger = org.splat.dal.dao.kernel.Database.logger;
 218
 219         // ==============================================================================================================================
 220         // Construction
 221         // ==============================================================================================================================
 222
 223         public Database getMe() {
 224                 // -------------------------------
 225                 if (my == null)
 226                         try {
 227                                 my = this;
 228                                 my.checkVersion();
 229                         } catch (Exception error) {
 230                                 logger.fatal("Could not access the database, reason:", error);
 231                         }
 232                 return my;
 233         }
 234
 235         private Database() {
 236         }
 237
 238         private void checkVersion() {
 239                 getSessionFactory().getCurrentSession().doWork(new CheckVersion());
 240         }
 241
 242         // ==============================================================================================================================
 243         // Public member functions
 244         // ==============================================================================================================================
 245
 246         public boolean isInitialized() {
 247                 // -------------------------------
 248                 return (uplevel >= 0);
 249         }
 250
 251         public void initialize() throws IOException, SQLException {
 252                 // -------------------------
 253                 logger.info("Creation of the database.");
 254
 255                 // Creation of the Lucene index
 256                 getIndexService().create(); // May throw IOException if the index repository is improperly configured
 257
 258                 // Creation of the SIMER SQL tables
 259                 Session session = Database.getSession();
 260                 session.doWork(new CreateTables()); // May throw SQLException if the SIMER database does not exist
 261                 session.flush();
 262
 263                 // Population of the database with customized data
 264                 this.populate();
 265
 266                 session.flush();
 267                 uplevel = 0; // The database is now up-to-date
 268         }
 269
 270         // ==============================================================================================================================
 271         // Protected member functions
 272         // ==============================================================================================================================
 273
 274         public void configure(Properties reprop) throws IOException {
 275                 // --------------------------------------------
 276                 basepath = reprop.getProperty("repository");
 277                 getRepositoryService().setBasepath(basepath);
 278                 getIndexService().configure();
 279         }
 280
 281         protected void populate() {
 282                 // --------------------------
 283                 try {
 284                         // Initialization of the schema version
 285                         this.setSchemaVersion("D0.3"); // TODO: Get the version name from the configuration file
 286
 287                         // Creation of the default system administrator
 288                         // TODO: Get the username password from the Hibernate configuration
 289                         User.Properties uprop = new User.Properties();
 290                         uprop.setUsername("simer").setPassword("admin").setName(
 291                                         "Simulation").setFirstName("Manager").setDisplayName(
 292                                         "label.sysadmin").addRole("sysadmin").setMailAddress(
 293                                         "noreply@salome-platform.org");
 294                         uprop.disableCheck();
 295                         UserDirectory.createUser(uprop);
 296                 } catch (Exception e) {
 297                         // Let's continue, hoping the best...
 298                 }
 299         }
 300
 301         // ==============================================================================================================================
 302         // Public services
 303         // ==============================================================================================================================
 304
 305         public static File getDownloadDirectory(User user) {
 306                 return my.getRepositoryService().getDownloadDirectory(user);
 307         }
 308
 309         public static String getTemplatePath() {
 310                 return my.getRepositoryService().getTemplatePath();
 311         }
 312
 313         public static String getRepositoryVaultPath() {
 314                 return my.getRepositoryService().getRepositoryVaultPath();
 315         }
 316
 317         public static Document selectDocument(int index) {
 318                 // -------------------------------------------------
 319                 StringBuffer query = new StringBuffer("from Document where rid='")
 320                                 .append(index).append("'");
 321                 return (Document) Database.getSession().createQuery(query.toString())
 322                                 .uniqueResult();
 323         }
 324
 325         public static Document selectDocument(String refid, String version) {
 326                 // --------------------------------------------------------------------
 327                 StringBuffer query = new StringBuffer("from Document where did='")
 328                                 .append(refid).append("' and version='").append(version)
 329                                 .append("'");
 330                 return (Document) Database.getSession().createQuery(query.toString())
 331                                 .uniqueResult();
 332         }
 333
 334         public static SimulationContext selectSimulationContext(int index) {
 335                 // -------------------------------------------------------------------
 336                 StringBuffer query = new StringBuffer(
 337                                 "from SimulationContext where rid='").append(index).append("'");
 338                 return (SimulationContext) Database.getSession().createQuery(
 339                                 query.toString()).uniqueResult();
 340         }
 341
 342         public static SimulationContext selectSimulationContext(
 343                         SimulationContextType celt, String value) {
 344                 // --------------------------------------------------------------------------------------------------
 345                 SimulationContext result = null;
 346                 try {
 347                         SimulationContext.Properties cprop = new SimulationContext.Properties();
 348                         List<SimulationContext> clist = selectSimulationContextsWhere(cprop
 349                                         .setType(celt).setValue(value));
 350                         if (!clist.isEmpty())
 351                                 result = clist.get(0); // Supposed being the most used one if many exist
 352                 } catch (InvalidPropertyException error) {
 353                         logger.info("Attempt to select a simulation context \""
 354                                         + celt.getName() + "\" with an invalid value.");
 355                 }
 356                 return result;
 357         }
 358
 359         @SuppressWarnings("unchecked")
 360         public static List<SimulationContext> selectSimulationContextsWhere(
 361                         SimulationContext.Properties cprop) {
 362                 // --------------------------------------------------------------------------------------------------------
 363                 StringBuffer query = new StringBuffer("from SimulationContext");
 364                 String separator = " where";
 365                 SimulationContextType celt = cprop.getType();
 366                 String value = cprop.getValue();
 367                 ProgressState state = cprop.getProgressState();
 368                 String order = "";
 369
 370                 if (celt != null) {
 371                         query = query.append(separator).append(" type='").append(
 372                                         celt.getIndex()).append("'");
 373                         separator = " and";
 374                         order = " order by value asc";
 375                 }
 376                 if (value != null) {
 377                         query = query.append(separator).append(" value='").append(value)
 378                                         .append("'");
 379                         separator = " and";
 380                 }
 381                 if (state != null) {
 382                         query = query.append(separator).append(" state='").append(state)
 383                                         .append("'");
 384                         if (celt == null)
 385                                 order = " order by type asc";
 386                 }
 387                 query.append(order);
 388                 return (List<SimulationContext>) Database.getSession().createQuery(
 389                                 query.toString()).list();
 390         }
 391
 392         protected static IDBuilder selectIDBuilder(int cycle) {
 393                 // ------------------------------------------------------
 394                 StringBuffer buffer = new StringBuffer("from IDBuilder where cycle='")
 395                                 .append(cycle).append("'");
 396                 String qstring = buffer.toString();
 397                 Query query = Database.getSession().createQuery(qstring);
 398                 IDBuilder result = (IDBuilder) query.uniqueResult();
 399
 400                 return result;
 401         }
 402
 403         public static IDBuilder selectIDBuilder(Date date) {
 404                 // ------------------------------------------------------
 405                 SimpleDateFormat year = new SimpleDateFormat("yyyy");
 406                 String cycle = year.format(date);
 407                 StringBuffer buffer = new StringBuffer("from IDBuilder where cycle='")
 408                                 .append(cycle).append("'");
 409                 String qstring = buffer.toString();
 410                 Query query = Database.getSession().createQuery(qstring);
 411                 IDBuilder result = (IDBuilder) query.uniqueResult();
 412
 413                 return result;
 414         }
 415
 416         /**
 417          * @return
 418          */
 419         public IndexService getIndexService() {
 420                 return _indexService;
 421         }
 422
 423         /**
 424          * @return
 425          */
 426         public RepositoryService getRepositoryService() {
 427                 return _repositoryService;
 428         }
 429
 430         public void setRepositoryService(RepositoryService repositoryService) {
 431                 _repositoryService = repositoryService;
 432         }
 433
 434         public void setIndexService(IndexService indexService) {
 435                 _indexService = indexService;
 436         }
 437
 438         /**
 439          * Get the sessionFactory.
 440          *
 441          * @return the sessionFactory
 442          */
 443         public SessionFactory getSessionFactory() {
 444                 return _sessionFactory;
 445         }
 446
 447         /**
 448          * Set the sessionFactory.
 449          *
 450          * @param sessionFactory
 451          *            the sessionFactory to set
 452          */
 453         public void setSessionFactory(SessionFactory sessionFactory) {
 454                 _sessionFactory = sessionFactory;
 455         }
 456 }