Skip to contentMethod: areProceduresCreated()
1: package de.fhdw.wtf.persistence.facade;
2:
3: import java.io.BufferedReader;
4: import java.io.IOException;
5: import java.io.InputStream;
6: import java.io.InputStreamReader;
7: import java.nio.charset.Charset;
8: import java.sql.CallableStatement;
9: import java.sql.SQLException;
10: import java.sql.Statement;
11:
12: import oracle.jdbc.OracleTypes;
13: import de.fhdw.wtf.persistence.exception.OtherSQLException;
14: import de.fhdw.wtf.persistence.exception.PersistenceException;
15:
16: /**
17: * This class has the ability to check the database for a valid table and procedure structure which is needed for the
18: * meta model and to create and drop the structure.
19: *
20: * Hint: - The Singleton OracleDatabaseManager must have a database connection before you can use this class. - If you
21: * rename one of the .sql schema script you need change the names here as well.
22: */
23: public class OracleDataBasePreparator {
24:
25: /**
26: * instance of the oracle database manager.
27: */
28: private OracleDatabaseManager databaseManager = null;
29:
30: private static final String CALL_SUFFIX = " end;";
31: private static final String STORED_FUNCTION_PREFIX = "begin ? := ";
32: private static final int ORACLE_EXCEPTION_OBJECT_WITH_NAME_ALREADY_EXISTS = 955;
33: private static final int ORACLE_EXCEPTION_TABLE_OR_VIEW_DOES_NOT_EXIST = 942;
34: private static final int ORACLE_EXCEPTION_SEQUENCER_DOES_NOT_EXIST = 2289;
35: private static final int ORACLE_EXCEPTION_OBJECT_DOES_NOT_EXIST = 4043;
36:
37: // relative path using "../Database" for correct resolution from different
38: // project
39: /** Path to the directory of all database scripts. */
40: private static final String SCRIPT_ROOT_DIR = "scripts/";
41:
42: /**
43: * Name of the procedure which checks the table structure.
44: */
45: private static final String PROCEDURE_NAME_CHECK_VALID_TABLE_STRUCTURE = "isValidTableStructureCreated";
46: /**
47: * Name of the procedure which checks if all necessary procedures are available.
48: */
49: private static final String PROCEDURE_NAME_CHECK_VALID_PROCEDURES = "areProceduresCreated";
50:
51: /**
52: * name of the initRoutines SQL-package.
53: */
54: private static final String PACKAGE_NAME_INIT_ROUTINES = "InitRoutines";
55:
56: /**
57: * filename to the initRoutines SQL-package.
58: */
59: private static final String SCRIPT_NAME_INIT_ROUTINES = "InitRoutines.sql";
60:
61: /**
62: * filename to the initRoutines SQL-package body.
63: */
64: private static final String SCRIPT_NAME_INIT_ROUTINES_BODY = "InitRoutinesBody.sql";
65:
66: /**
67: * filename to the meta model schema.
68: */
69: private static final String SCRIPT_NAME_CREATE_SCHEMA_TABLES = "schemaF.sql";
70:
71: /**
72: * filename to the userexception SQL-package.
73: */
74: private static final String SCRIPT_NAME_CREATE_SCHEMA_EXCEPTION = "userException.sql";
75:
76: /**
77: * filename to the drop statements.
78: */
79: private static final String SCRIPT_NAME_DROP_SCHEMA = "dropStatements.sql";
80:
81: /**
82: * filename to the objectfacade SQL-package.
83: */
84: private static final String SCRIPT_NAME_CREATE_PROCEDURES_OBJECT_FACADE = "objectfacade.sql";
85:
86: /**
87: * filename to the objectfacade SQL-package body.
88: */
89: private static final String SCRIPT_NAME_CREATE_PROCEDURES_OBJECT_FACADE_BODY = "objectfacadebody.sql";
90:
91: /**
92: * filename to the classfacade SQL-package.
93: */
94: private static final String SCRIPT_NAME_CREATE_PROCEDURES_CLASS_FACADE = "classfacade.sql";
95:
96: /**
97: * filename to the classfacade SQL-package body.
98: */
99: private static final String SCRIPT_NAME_CREATE_PROCEDURES_CLASS_FACADE_BODY = "classfacadebody.sql";
100:
101: /**
102: * filename to the accountfacade SQL-package.
103: */
104: private static final String SCRIPT_NAME_CREATE_PROCEDURES_ACCOUNT_FACADE = "accountfacade.sql";
105:
106: /**
107: * filename to the accountfacade SQL-package body.
108: */
109: private static final String SCRIPT_NAME_CREATE_PROCEDURES_ACCOUNT_FACADE_BODY = "accountfacadebody.sql";
110:
111: /**
112: * constructor of the databasepreperator.
113: */
114: public OracleDataBasePreparator() {
115: this.databaseManager = OracleDatabaseManager.getInstance();
116: }
117:
118: private String readFile(final String path, @SuppressWarnings("unused") final Charset encoding) throws IOException {
119: final ClassLoader classLoader = this.getClass().getClassLoader();
120: final InputStream inputStream = classLoader.getResourceAsStream(path);
121: final StringBuilder stringBuilder = new StringBuilder();
122: try (final BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream))) {
123: String line;
124: while ((line = reader.readLine()) != null) {
125: stringBuilder.append(line);
126: stringBuilder.append("\n");
127: }
128: }
129: return stringBuilder.toString();
130: }
131:
132: /**
133: * Creates the initialization routines on the database.
134: *
135: * @throws OtherSQLException
136: * will be thrown when a sql error is thrown
137: * @throws PersistenceException
138: * will be thrown when a persistence error occurs.
139: * @throws IOException
140: * will be thrown when a access to a file is not valid
141: * */
142: private void createInitRoutines() throws PersistenceException, IOException {
143: try (final CallableStatement initRoutines =
144: this.databaseManager.getConnection().prepareCall(
145: this.readFile(SCRIPT_ROOT_DIR + SCRIPT_NAME_INIT_ROUTINES, Charset.defaultCharset()))) {
146: initRoutines.execute();
147: } catch (final SQLException e) {
148: throw new OtherSQLException(e);
149: }
150: try (final CallableStatement initRoutinesBody =
151: this.databaseManager.getConnection().prepareCall(
152: this.readFile(SCRIPT_ROOT_DIR + SCRIPT_NAME_INIT_ROUTINES_BODY, Charset.defaultCharset()))) {
153: initRoutinesBody.execute();
154: } catch (final SQLException e) {
155: throw new OtherSQLException(e);
156: }
157: }
158:
159: /**
160: * This operation checks if the table structure for the given meta model is valid. Returns true if everything is ok.
161: *
162: * @return true if the stucture is valid
163: * @throws PersistenceException
164: * will thrown if a persistence error occurs
165: * @throws IOException
166: * will be thrown when a access to a file is not valid
167: */
168: public boolean isTableStructureValid() throws IOException, PersistenceException {
169: this.createInitRoutines();
170:
171: try (CallableStatement isValidTableStructureCreated =
172: this.databaseManager.getConnection().prepareCall(
173: STORED_FUNCTION_PREFIX + OracleDatabaseManager.getInstance().getSchemaName() + '.'
174: + PACKAGE_NAME_INIT_ROUTINES + '.' + PROCEDURE_NAME_CHECK_VALID_TABLE_STRUCTURE + ';'
175: + CALL_SUFFIX)) {
176: isValidTableStructureCreated.registerOutParameter(1, OracleTypes.NUMBER);
177: try {
178: isValidTableStructureCreated.execute();
179: return isValidTableStructureCreated.getInt(1) != 0;
180: } catch (final SQLException e1) {
181: /* perhaps there is no InitRoutines package yet? assume empty database */
182: return false;
183: }
184: } catch (final SQLException e) {
185: e.printStackTrace();
186: throw new OtherSQLException(e);
187: }
188: }
189:
190: /**
191: * Checks if all needed procedures and functions for the meta model access exist. Returns true if everything is ok.
192: *
193: * @return true if all needed procedures and functions are exists
194: * @throws IOException
195: * will thrown if a file is not accessable
196: * @throws PersistenceException
197: * will thrown if a persistence error occurs
198: * */
199: public boolean areProceduresCreated() throws IOException, PersistenceException {
200: this.createInitRoutines();
201:
202: try (CallableStatement areValidProceduresCreated =
203: this.databaseManager.getConnection().prepareCall(
204: STORED_FUNCTION_PREFIX + OracleDatabaseManager.getInstance().getSchemaName() + '.'
205: + PACKAGE_NAME_INIT_ROUTINES + '.' + PROCEDURE_NAME_CHECK_VALID_PROCEDURES + ';'
206: + CALL_SUFFIX)) {
207: areValidProceduresCreated.registerOutParameter(1, OracleTypes.NUMBER);
208: try {
209: areValidProceduresCreated.execute();
210:• return areValidProceduresCreated.getInt(1) != 0;
211: } catch (final SQLException e1) {
212: /* perhaps there is no InitRoutines package yet? assume empty database */
213: return false;
214: }
215: } catch (final SQLException e1) {
216: throw new OtherSQLException(e1);
217: }
218: }
219:
220: /**
221: * Creates the schema for the database. If a few tables are missing they will be added by this operation.
222: *
223: * @throws PersistenceException
224: * will thrown if a persistence error occurs
225: * @throws IOException
226: * will thrown if a file is not accessible
227: * */
228: public void createWholeSchema() throws PersistenceException, IOException {
229: try (final Statement statement = this.databaseManager.getConnection().createStatement()) {
230: final String tableSchemaString =
231: this.readFile(SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_SCHEMA_TABLES, Charset.defaultCharset());
232:
233: for (final String part : tableSchemaString.split(";")) {
234: try {
235: statement.execute(part);
236: } catch (final SQLException e) {
237: if (e.getErrorCode() != ORACLE_EXCEPTION_OBJECT_WITH_NAME_ALREADY_EXISTS) {
238: throw e;
239: }
240: }
241: }
242:
243: // databaseManager.getConnection().commit();
244: } catch (final SQLException e) {
245: e.printStackTrace();
246: throw new OtherSQLException(e);
247: }
248: }
249:
250: /**
251: * Drops all tables and sequencers of the schema. Warning, all Data stored in the tables will be lost.
252: *
253: * @throws PersistenceException
254: * will thrown if a persistence error occurs
255: * @throws IOException
256: * will thrown if a file is not accessable
257: */
258: public void dropWholeSchema() throws PersistenceException, IOException {
259: try (final Statement statement = this.databaseManager.getConnection().createStatement()) {
260: final String tableSchemaString =
261: this.readFile(SCRIPT_ROOT_DIR + SCRIPT_NAME_DROP_SCHEMA, Charset.defaultCharset());
262:
263: for (String part : tableSchemaString.split(";")) {
264: try {
265: if (part.startsWith("\n")) {
266: part = part.substring(1);
267: }
268: if (!part.isEmpty()) {
269: statement.execute(part);
270: }
271: } catch (final SQLException e) {
272: if (e.getErrorCode() != ORACLE_EXCEPTION_SEQUENCER_DOES_NOT_EXIST
273: && e.getErrorCode() != ORACLE_EXCEPTION_TABLE_OR_VIEW_DOES_NOT_EXIST
274: && e.getErrorCode() != ORACLE_EXCEPTION_OBJECT_DOES_NOT_EXIST) {
275: throw e;
276: }
277: }
278: }
279:
280: // databaseManager.getConnection().commit();
281: } catch (final SQLException e) {
282: e.printStackTrace();
283: throw new OtherSQLException(e);
284: }
285: }
286:
287: /**
288: * Creates all procedures to access the schema tables.
289: *
290: * @throws PersistenceException
291: * will thrown if a persistence error occurs
292: * @throws IOException
293: * will thrown if a file is not accessable
294: */
295: public void createProcedures() throws IOException, PersistenceException {
296: try (final CallableStatement createProceduresCall =
297: this.databaseManager.getConnection().prepareCall(
298: this.readFile(SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_SCHEMA_EXCEPTION, Charset.defaultCharset()))) {
299: createProceduresCall.execute();
300: } catch (final SQLException e) {
301: throw new OtherSQLException(e);
302: }
303: try (final CallableStatement createProceduresCall =
304: this.databaseManager.getConnection().prepareCall(
305: this.readFile(
306: SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_CLASS_FACADE,
307: Charset.defaultCharset()))) {
308: createProceduresCall.execute();
309: } catch (final SQLException e) {
310: throw new OtherSQLException(e);
311: }
312: try (final CallableStatement createProceduresCall =
313: this.databaseManager.getConnection().prepareCall(
314: this.readFile(
315: SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_CLASS_FACADE_BODY,
316: Charset.defaultCharset()))) {
317: createProceduresCall.execute();
318: } catch (final SQLException e) {
319: throw new OtherSQLException(e);
320: }
321: try (final CallableStatement createProceduresCall =
322: this.databaseManager.getConnection().prepareCall(
323: this.readFile(
324: SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_OBJECT_FACADE,
325: Charset.defaultCharset()))) {
326: createProceduresCall.execute();
327: } catch (final SQLException e) {
328: throw new OtherSQLException(e);
329: }
330: try (final CallableStatement createProceduresCall =
331: this.databaseManager.getConnection().prepareCall(
332: this.readFile(
333: SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_OBJECT_FACADE_BODY,
334: Charset.defaultCharset()))) {
335: createProceduresCall.execute();
336: } catch (final SQLException e) {
337: throw new OtherSQLException(e);
338: }
339: try (final CallableStatement createProceduresCall =
340: this.databaseManager.getConnection().prepareCall(
341: this.readFile(
342: SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_ACCOUNT_FACADE,
343: Charset.defaultCharset()))) {
344: createProceduresCall.execute();
345: } catch (final SQLException e) {
346: throw new OtherSQLException(e);
347: }
348: try (final CallableStatement createProceduresCall =
349: this.databaseManager.getConnection().prepareCall(
350: this.readFile(
351: SCRIPT_ROOT_DIR + SCRIPT_NAME_CREATE_PROCEDURES_ACCOUNT_FACADE_BODY,
352: Charset.defaultCharset()))) {
353: createProceduresCall.execute();
354: } catch (final SQLException e) {
355: throw new OtherSQLException(e);
356: }
357: }
358:
359: }
![[Jenkins]](/jenkins/static/5c0124f8/images/svgs/logo.svg){kind=logo}
