KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > teamkonzept > webman > mainint > db > queries > duplication > oracle > AssureTempTables


1 package com.teamkonzept.webman.mainint.db.queries.duplication.oracle;
2
3
4 import com.teamkonzept.db.*;
5 //import com.teamkonzept.lib.*;
6
7 import java.io.*;
8 import java.util.*;
9 import java.sql.*;
10
11
12     /**
13     Dieser anonyme Block stellt sicher, dass die benötigten temporären Tabellen
14     und die Sequenz vorhanden und im korrekten Zustand sind sind,
15     Es kann erforderlich sein, dass die folgenden privileges vorhanden sind:
16     1. CREATE ANY TABLE
17     2. CREATE ANY SEQUENCE
18
19         
20         @see UtilityProceduresPackageDeclaration
21         @see DuplicationPackageBody
22     */
23         
24 public class AssureTempTables extends TKPrepQuery{
25
26     public final static boolean isPrepared =
27         true;
28     
29     public final static String JavaDoc[] paramOrder =
30         null;
31     
32     public final static Object JavaDoc[][] paramTypes =
33         null;
34                 
35     public final static boolean[] setRelevants =
36         null;
37         
38     public final static String JavaDoc sqlString =
39
40                                                         
41     /* Translation Tables and Sequence for IDs */
42      " DECLARE "
43      + " table_exists INTEGER; "
44      + " sequence_exists INTEGER; "
45      + " sequence_start INTEGER; "
46      + " cursor_id INTEGER; "
47      + " st_translate VARCHAR(255); "
48      + " ct_translate VARCHAR(255); "
49      + " dup_sequence VARCHAR(255); "
50      + " ci_sequence VARCHAR(255); "
51      + " cv_sequence VARCHAR(255); "
52      + " c_sequence VARCHAR(255); "
53      + " new_ct_id INTEGER; "
54
55      + " BEGIN "
56      + " st_translate := 'ST_TRANSLATE'; "
57      + " ct_translate := 'CT_TRANSLATE'; "
58      + " dup_sequence := 'DUP_SEQUENCE'; "
59      + " ci_sequence := 'CI_SEQUENCE'; "
60      + " cv_sequence := 'CV_SEQUENCE'; "
61      + " c_sequence := 'C_SEQUENCE'; "
62             
63      + " SELECT COUNT(*) into table_exists from USER_CATALOG WHERE TABLE_NAME = st_translate; "
64      + " IF(table_exists != 1) THEN "
65      + " EXECUTE IMMEDIATE ' CREATE GLOBAL TEMPORARY TABLE ' || st_translate || ' (OLD_ID int, NEW_ID int NULL)'; "
66      + " DBMS_OUTPUT.PUT_LINE('Created table ' || st_translate); "
67      + " END IF; "
68      + " SELECT COUNT(*) into table_exists from USER_CATALOG WHERE TABLE_NAME = ct_translate; "
69      + " IF(table_exists != 1) THEN "
70      + " EXECUTE IMMEDIATE ' CREATE GLOBAL TEMPORARY TABLE ' || ct_translate || ' (OLD_ID int, NEW_ID int NULL) '; "
71      + " DBMS_OUTPUT.PUT_LINE('Created table ' || ct_translate); "
72      + " END IF; "
73         
74         
75      + " " /*DUP_SEQUENCE */
76      + " SELECT COUNT(*) into sequence_exists from USER_SEQUENCES WHERE SEQUENCE_NAME = dup_sequence; "
77      + " IF(sequence_exists = 1) THEN "
78      + " EXECUTE IMMEDIATE ' DROP SEQUENCE ' || dup_sequence ; "
79      + " DBMS_OUTPUT.PUT_LINE('Dropped ' || dup_sequence); "
80      + " END IF; "
81         
82      + " SELECT MAX(MAX_ID) + 1 into sequence_start "
83      + " FROM "
84      + " (SELECT MAX (CONTENT_NODE_ID) AS MAX_ID FROM CONTENT_TREE "
85      + " UNION "
86      + " SELECT MAX (SITE_NODE_ID) AS MAX_ID FROM SITE_TREE); "
87      + " EXECUTE IMMEDIATE ' CREATE SEQUENCE ' || dup_sequence || ' MINVALUE ' || sequence_start; "
88      + " DBMS_OUTPUT.PUT_LINE(' Created dup_sequence starting at ' || sequence_start ); "
89
90
91      + " " /* CI_SEQUENCE */
92      + " SELECT COUNT(*) into sequence_exists from USER_SEQUENCES WHERE SEQUENCE_NAME = ci_sequence; "
93      + " IF(sequence_exists = 1) THEN "
94      + " EXECUTE IMMEDIATE ' DROP SEQUENCE ' || ci_sequence ; "
95      + " DBMS_OUTPUT.PUT_LINE('Dropped ' || ci_sequence); "
96      + " END IF; "
97         
98      + " SELECT MAX(INSTANCE_ID) + 1 into sequence_start "
99      + " FROM CONTENT_INSTANCE; "
100             
101      + " EXECUTE IMMEDIATE ' CREATE SEQUENCE ' || ci_sequence || ' MINVALUE ' || sequence_start; "
102      + " DBMS_OUTPUT.PUT_LINE(' Created ' || ci_sequence || ' starting at ' || sequence_start ); "
103         
104         
105      + " " /* CV_SEQUENCE */
106      + " SELECT COUNT(*) into sequence_exists from USER_SEQUENCES WHERE SEQUENCE_NAME = cv_sequence; "
107      + " IF(sequence_exists = 1) THEN "
108      + " EXECUTE IMMEDIATE ' DROP SEQUENCE ' || cv_sequence ; "
109      + " DBMS_OUTPUT.PUT_LINE('Dropped ' || cv_sequence); "
110      + " END IF; "
111         
112      + " SELECT MAX(VERSION_ID) + 1 into sequence_start "
113      + " FROM CONTENT_VERSION; "
114             
115      + " EXECUTE IMMEDIATE ' CREATE SEQUENCE ' || cv_sequence || ' MINVALUE ' || sequence_start; "
116      + " DBMS_OUTPUT.PUT_LINE(' Created ' || cv_sequence || ' starting at ' || sequence_start ); "
117         
118         
119      + " " /* C_SEQUENCE */
120      + " SELECT COUNT(*) into sequence_exists from USER_SEQUENCES WHERE SEQUENCE_NAME = c_sequence; "
121      + " IF(sequence_exists = 1) THEN "
122      + " EXECUTE IMMEDIATE ' DROP SEQUENCE ' || c_sequence ; "
123      + " DBMS_OUTPUT.PUT_LINE('Dropped ' || c_sequence); "
124      + " END IF; "
125         
126      + " SELECT MAX(CONTENT_ID) + 1 into sequence_start "
127      + " FROM CONTENT; "
128             
129      + " EXECUTE IMMEDIATE ' CREATE SEQUENCE ' || c_sequence || ' MINVALUE ' || sequence_start; "
130      + " DBMS_OUTPUT.PUT_LINE(' Created ' || c_sequence || ' starting at ' || sequence_start ); "
131         
132
133      + " " /* empty tables */
134      + " EXECUTE IMMEDIATE ' DELETE FROM ' || ct_translate ; "
135      + " EXECUTE IMMEDIATE ' DELETE FROM ' || st_translate ; "
136      + " END; "
137
138      ;
139     
140     public void initQuery(Connection con) {
141         super.initQuery(
142             con,
143             isPrepared,
144             paramOrder,
145             paramTypes,
146             setRelevants,
147             sqlString );
148     }
149 }
150
151
Popular Tags