1 package org.splat.dal.dao.som;
5 * @author Daniel Brunier-Coulin
6 * @copyright OPEN CASCADE 2012
9 import java.text.SimpleDateFormat;
10 import java.util.Date;
11 import java.util.List;
12 import java.util.Properties;
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;
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;
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;
39 public class Database extends org.splat.dal.dao.kernel.Database {
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;
47 private static Database my = null; // Singleton instance
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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();
210 table = dbmdata.getTables(dbname, null, "study", null);
213 uplevel = -1; // Database not initialized
217 public final static Logger logger = org.splat.dal.dao.kernel.Database.logger;
219 // ==============================================================================================================================
221 // ==============================================================================================================================
223 public Database getMe() {
224 // -------------------------------
229 } catch (Exception error) {
230 logger.fatal("Could not access the database, reason:", error);
238 private void checkVersion() {
239 getSessionFactory().getCurrentSession().doWork(new CheckVersion());
242 // ==============================================================================================================================
243 // Public member functions
244 // ==============================================================================================================================
246 public boolean isInitialized() {
247 // -------------------------------
248 return (uplevel >= 0);
251 public void initialize() throws IOException, SQLException {
252 // -------------------------
253 logger.info("Creation of the database.");
255 // Creation of the Lucene index
256 getIndexService().create(); // May throw IOException if the index repository is improperly configured
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
263 // Population of the database with customized data
267 uplevel = 0; // The database is now up-to-date
270 // ==============================================================================================================================
271 // Protected member functions
272 // ==============================================================================================================================
274 public void configure(Properties reprop) throws IOException {
275 // --------------------------------------------
276 basepath = reprop.getProperty("repository");
277 getRepositoryService().setBasepath(basepath);
278 getIndexService().configure();
281 protected void populate() {
282 // --------------------------
284 // Initialization of the schema version
285 this.setSchemaVersion("D0.3"); // TODO: Get the version name from the configuration file
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...
301 // ==============================================================================================================================
303 // ==============================================================================================================================
305 public static File getDownloadDirectory(User user) {
306 return my.getRepositoryService().getDownloadDirectory(user);
309 public static String getTemplatePath() {
310 return my.getRepositoryService().getTemplatePath();
313 public static String getRepositoryVaultPath() {
314 return my.getRepositoryService().getRepositoryVaultPath();
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())
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)
330 return (Document) Database.getSession().createQuery(query.toString())
334 public static KnowledgeElement selectKnowledgeElement(int index) {
335 // -----------------------------------------------------------------
336 StringBuffer query = new StringBuffer(
337 "from KnowledgeElement where rid='").append(index).append("'");
338 KnowledgeElement result = (KnowledgeElement) Database.getSession()
339 .createQuery(query.toString()).uniqueResult();
341 result.getOwnerScenario().getOwnerStudy().loadWorkflow();
345 public static SimulationContext selectSimulationContext(int index) {
346 // -------------------------------------------------------------------
347 StringBuffer query = new StringBuffer(
348 "from SimulationContext where rid='").append(index).append("'");
349 return (SimulationContext) Database.getSession().createQuery(
350 query.toString()).uniqueResult();
353 public static SimulationContext selectSimulationContext(
354 SimulationContextType celt, String value) {
355 // --------------------------------------------------------------------------------------------------
356 SimulationContext result = null;
358 SimulationContext.Properties cprop = new SimulationContext.Properties();
359 List<SimulationContext> clist = selectSimulationContextsWhere(cprop
360 .setType(celt).setValue(value));
361 if (!clist.isEmpty())
362 result = clist.get(0); // Supposed being the most used one if many exist
363 } catch (InvalidPropertyException error) {
364 logger.info("Attempt to select a simulation context \""
365 + celt.getName() + "\" with an invalid value.");
370 @SuppressWarnings("unchecked")
371 public static List<SimulationContext> selectSimulationContextsWhere(
372 SimulationContext.Properties cprop) {
373 // --------------------------------------------------------------------------------------------------------
374 StringBuffer query = new StringBuffer("from SimulationContext");
375 String separator = " where";
376 SimulationContextType celt = cprop.getType();
377 String value = cprop.getValue();
378 ProgressState state = cprop.getProgressState();
382 query = query.append(separator).append(" type='").append(
383 celt.getIndex()).append("'");
385 order = " order by value asc";
388 query = query.append(separator).append(" value='").append(value)
393 query = query.append(separator).append(" state='").append(state)
396 order = " order by type asc";
399 return (List<SimulationContext>) Database.getSession().createQuery(
400 query.toString()).list();
403 public static Study selectStudy(int index) {
404 // -------------------------------------------
405 StringBuffer query = new StringBuffer("from Study where rid='").append(
407 Study result = (Study) Database.getSession().createQuery(
408 query.toString()).uniqueResult();
410 result.loadWorkflow();
414 public static Study selectStudy(String refid) {
415 // ----------------------------------------------
416 StringBuffer query = new StringBuffer("from Study where sid='").append(
418 Study result = (Study) Database.getSession().createQuery(
419 query.toString()).uniqueResult();
421 result.loadWorkflow();
425 protected static IDBuilder selectIDBuilder(int cycle) {
426 // ------------------------------------------------------
427 StringBuffer buffer = new StringBuffer("from IDBuilder where cycle='")
428 .append(cycle).append("'");
429 String qstring = buffer.toString();
430 Query query = Database.getSession().createQuery(qstring);
431 IDBuilder result = (IDBuilder) query.uniqueResult();
436 public static IDBuilder selectIDBuilder(Date date) {
437 // ------------------------------------------------------
438 SimpleDateFormat year = new SimpleDateFormat("yyyy");
439 String cycle = year.format(date);
440 StringBuffer buffer = new StringBuffer("from IDBuilder where cycle='")
441 .append(cycle).append("'");
442 String qstring = buffer.toString();
443 Query query = Database.getSession().createQuery(qstring);
444 IDBuilder result = (IDBuilder) query.uniqueResult();
452 public IndexService getIndexService() {
453 return _indexService;
459 public RepositoryService getRepositoryService() {
460 return _repositoryService;
463 public void setRepositoryService(RepositoryService repositoryService) {
464 _repositoryService = repositoryService;
467 public void setIndexService(IndexService indexService) {
468 _indexService = indexService;
472 * Get the sessionFactory.
474 * @return the sessionFactory
476 public SessionFactory getSessionFactory() {
477 return _sessionFactory;
481 * Set the sessionFactory.
483 * @param sessionFactory
484 * the sessionFactory to set
486 public void setSessionFactory(SessionFactory sessionFactory) {
487 _sessionFactory = sessionFactory;