KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derby > impl > tools > dblook > DB_Table


1 /*
2
3    Derby - Class org.apache.derby.impl.tools.dblook.DB_Table
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to you under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */

21
22 package org.apache.derby.impl.tools.dblook;
23
24 import java.sql.Connection JavaDoc;
25 import java.sql.Statement JavaDoc;
26 import java.sql.PreparedStatement JavaDoc;
27 import java.sql.ResultSet JavaDoc;
28 import java.sql.SQLException JavaDoc;
29 import java.sql.ResultSetMetaData JavaDoc;
30
31 import java.util.HashMap JavaDoc;
32 import java.util.Set JavaDoc;
33 import java.util.Iterator JavaDoc;
34
35 import org.apache.derby.tools.dblook;
36
37 public class DB_Table {
38
39     // Prepared statements use throughout the DDL
40
// generation process.
41
private static PreparedStatement JavaDoc getColumnInfoStmt;
42     private static PreparedStatement JavaDoc getColumnTypeStmt;
43     private static PreparedStatement JavaDoc getAutoIncStmt;
44
45     /* ************************************************
46      * Generate the DDL for all user tables in a given
47      * database.
48      * @param conn Connection to the source database.
49      * @param tableIdToNameMap Mapping of table ids to table
50      * names, for quicker reference.
51      * @return The DDL for the tables has been written
52      * to output via Logs.java.
53      ****/

54
55     public static void doTables(Connection JavaDoc conn, HashMap JavaDoc tableIdToNameMap)
56         throws SQLException JavaDoc
57     {
58
59         // Prepare some statements for general use by this class.
60

61         getColumnInfoStmt =
62             conn.prepareStatement("SELECT C.COLUMNNAME, C.REFERENCEID, " +
63             "C.COLUMNNUMBER FROM SYS.SYSCOLUMNS C, SYS.SYSTABLES T WHERE T.TABLEID = ? " +
64             "AND T.TABLEID = C.REFERENCEID ORDER BY C.COLUMNNUMBER");
65
66         getColumnTypeStmt =
67             conn.prepareStatement("SELECT COLUMNDATATYPE, COLUMNDEFAULT FROM SYS.SYSCOLUMNS " +
68             "WHERE REFERENCEID = ? AND COLUMNNAME = ?");
69
70         getAutoIncStmt =
71             conn.prepareStatement("SELECT AUTOINCREMENTSTART, " +
72             "AUTOINCREMENTINC, COLUMNNAME, REFERENCEID, COLUMNDEFAULT FROM SYS.SYSCOLUMNS " +
73             "WHERE COLUMNNAME = ? AND REFERENCEID = ?");
74
75         // Walk through list of tables and generate the DDL for
76
// each one.
77

78         boolean firstTime = true;
79         Set JavaDoc tableIds = tableIdToNameMap.keySet();
80         for (Iterator JavaDoc itr = tableIds.iterator(); itr.hasNext(); ) {
81
82             String JavaDoc tableId = (String JavaDoc)itr.next();
83             String JavaDoc tableName = (String JavaDoc)(tableIdToNameMap.get(tableId));
84             if (dblook.isExcludedTable(tableName))
85             // table isn't included in user-given list; skip it.
86
continue;
87
88             if (firstTime) {
89                 Logs.reportString("----------------------------------------------");
90                 Logs.reportMessage("DBLOOK_TablesHeader");
91                 Logs.reportString("----------------------------------------------\n");
92             }
93
94             Logs.writeToNewDDL("CREATE TABLE " + tableName + " (");
95
96             // Get column list, and write DDL for each column.
97
boolean firstCol = true;
98             getColumnInfoStmt.setString(1, tableId);
99             ResultSet JavaDoc columnRS = getColumnInfoStmt.executeQuery();
100             while (columnRS.next()) {
101                 String JavaDoc colName = dblook.addQuotes(columnRS.getString(1));
102                 String JavaDoc createColString = createColumn(colName, columnRS.getString(2),
103                     columnRS.getInt(3));
104                 if (!firstCol)
105                     createColString = ", " + createColString;
106
107                 Logs.writeToNewDDL(createColString);
108                 firstCol = false;
109             }
110
111             columnRS.close();
112             Logs.writeToNewDDL(")");
113             Logs.writeStmtEndToNewDDL();
114             Logs.writeNewlineToNewDDL();
115             firstTime = false;
116
117         } // outer while.
118

119         getColumnInfoStmt.close();
120         getColumnTypeStmt.close();
121         getAutoIncStmt.close();
122
123     }
124
125     /* ************************************************
126      * Generate the DDL for a specific column of the
127      * the table corresponding to the received tableId.
128      * @param colName the name of the column to generate.
129      * @param tableId Which table the column belongs to.
130      * @param colNum the number of the column to generate (1 =>
131      * 1st column, 2 => 2nd column, etc)
132      * @return The generated DDL, as a string.
133      ****/

134
135     private static String JavaDoc createColumn(String JavaDoc colName, String JavaDoc tableId,
136         int colNum) throws SQLException JavaDoc
137     {
138
139         getColumnTypeStmt.setString(1, tableId);
140         getColumnTypeStmt.setString(2, dblook.stripQuotes(colName));
141
142         ResultSet JavaDoc rs = getColumnTypeStmt.executeQuery();
143         StringBuffer JavaDoc colDef = new StringBuffer JavaDoc();
144         if (rs.next()) {
145
146             colDef.append(dblook.addQuotes(dblook.expandDoubleQuotes(
147                 dblook.stripQuotes(colName))));
148             colDef.append(" ");
149             colDef.append(rs.getString(1));
150             if (!reinstateAutoIncrement(colName, tableId, colDef) &&
151                          rs.getString(2) != null) {
152                 colDef.append(" DEFAULT ");
153                 colDef.append(rs.getString(2));
154             }
155         }
156
157         rs.close();
158         return colDef.toString();
159
160     }
161
162     /* ************************************************
163      * Generate autoincrement DDL for a given column and write it to
164      * received StringBuffer
165      * @param colName: Name of column that is autoincrement.
166      * @param tableId: Id of table in which column exists.
167      * @param colDef: StringBuffer to which DDL will be added.
168      * @return True if autoincrement DDL has been generated.
169      ****/

170
171     public static boolean reinstateAutoIncrement(String JavaDoc colName,
172         String JavaDoc tableId, StringBuffer JavaDoc colDef) throws SQLException JavaDoc
173     {
174
175         getAutoIncStmt.setString(1, dblook.stripQuotes(colName));
176         getAutoIncStmt.setString(2, tableId);
177         ResultSet JavaDoc autoIncCols = getAutoIncStmt.executeQuery();
178         if (autoIncCols.next()) {
179
180             long start = autoIncCols.getLong(1);
181             if (!autoIncCols.wasNull()) {
182                 colDef.append(" GENERATED ");
183                 colDef.append(autoIncCols.getObject(5) == null ?
184                           "ALWAYS ":"BY DEFAULT ");
185                 colDef.append("AS IDENTITY (START WITH ");
186                 colDef.append(autoIncCols.getLong(1));
187                 colDef.append(", INCREMENT BY ");
188                 colDef.append(autoIncCols.getLong(2));
189                 colDef.append(")");
190                 return true;
191             }
192         }
193
194         return false;
195
196     }
197
198 }
199
200
Popular Tags