001: /*
002: * IngresMetadata.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db.ingres;
013:
014: import java.math.BigInteger;
015: import java.sql.Connection;
016: import java.sql.PreparedStatement;
017: import java.sql.ResultSet;
018: import java.sql.SQLException;
019: import java.util.LinkedList;
020: import java.util.List;
021: import workbench.db.SequenceDefinition;
022: import workbench.db.SequenceReader;
023: import workbench.db.SynonymReader;
024: import workbench.db.TableIdentifier;
025: import workbench.util.ExceptionUtil;
026: import workbench.log.LogMgr;
027: import workbench.storage.DataStore;
028: import workbench.util.SqlUtil;
029:
030: /**
031: * Ingres related metadata information.
032: *
033: * @author support@sql-workbench.net
034: */
035: public class IngresMetadata implements SynonymReader, SequenceReader {
036: private Connection dbConn;
037:
038: public IngresMetadata(Connection conn) {
039: dbConn = conn;
040: }
041:
042: /**
043: * Get a list of synonyms for the given owner
044: */
045: public List<String> getSynonymList(Connection conn, String owner) {
046: ResultSet rs = null;
047: PreparedStatement stmt = null;
048: List<String> result = new LinkedList<String>();
049:
050: StringBuilder sql = new StringBuilder(200);
051: sql.append("SELECT synonym_name FROM iisynonyms ");
052: if (owner != null) {
053: sql.append(" WHERE synonym_owner = ?");
054: }
055:
056: try {
057: stmt = conn.prepareStatement(sql.toString());
058: if (owner != null)
059: stmt.setString(1, owner);
060: rs = stmt.executeQuery();
061: while (rs.next()) {
062: String seq = rs.getString(1);
063: if (seq != null)
064: result.add(seq.trim());
065: }
066: } catch (Exception e) {
067: LogMgr.logError("OracleMetaData.getSynonymList()",
068: "Error when retrieving synonyms", e);
069: } finally {
070: SqlUtil.closeAll(rs, stmt);
071: }
072: return result;
073: }
074:
075: public TableIdentifier getSynonymTable(Connection con,
076: String anOwner, String aSynonym) throws SQLException {
077: StringBuilder sql = new StringBuilder(200);
078: sql
079: .append("SELECT synonym_name, table_owner, table_name FROM iisynonyms ");
080: sql.append(" WHERE synonym_name = ? AND synonym_owner = ?");
081:
082: PreparedStatement stmt = con.prepareStatement(sql.toString());
083: stmt.setString(1, aSynonym);
084: stmt.setString(2, anOwner);
085:
086: ResultSet rs = stmt.executeQuery();
087: String table = null;
088: String owner = null;
089: TableIdentifier result = null;
090: try {
091: if (rs.next()) {
092: owner = rs.getString(2);
093: table = rs.getString(3);
094: if (table != null) {
095: result = new TableIdentifier(null, owner, table);
096: }
097: }
098: } finally {
099: SqlUtil.closeAll(rs, stmt);
100: }
101:
102: return result;
103: }
104:
105: public String getSynonymSource(Connection con, String anOwner,
106: String aSynonym) throws SQLException {
107: TableIdentifier id = getSynonymTable(con, anOwner, aSynonym);
108: StringBuilder result = new StringBuilder(200);
109: result.append("CREATE SYNONYM ");
110: result.append(aSynonym);
111: result.append("\n FOR ");
112: result.append(id.getTableExpression());
113: result.append(";\n");
114: return result.toString();
115: }
116:
117: public List<SequenceDefinition> getSequences(String owner) {
118: return null;
119: }
120:
121: public SequenceDefinition getSequenceDefinition(String owner,
122: String sequence) {
123: return null;
124: }
125:
126: public DataStore getRawSequenceDefinition(String owner,
127: String sequence) {
128: String sql = "SELECT * FROM iisequences WHERE seq_owner = ? AND seq_name = ? ";
129: PreparedStatement stmt = null;
130: ResultSet rs = null;
131: DataStore result = null;
132: try {
133: stmt = this .dbConn.prepareStatement(sql);
134: stmt.setString(1, owner.trim());
135: stmt.setString(2, sequence.trim());
136: rs = stmt.executeQuery();
137: result = new DataStore(rs, true);
138: } catch (Throwable e) {
139: LogMgr.logError(
140: "IngresMetaData.getRawSequenceDefinition()",
141: "Error when retrieving sequence definition", e);
142: } finally {
143: SqlUtil.closeAll(rs, stmt);
144: }
145:
146: return result;
147: }
148:
149: public List<String> getSequenceList(String owner) {
150: ResultSet rs = null;
151: PreparedStatement stmt = null;
152: List<String> result = new LinkedList<String>();
153:
154: StringBuilder sql = new StringBuilder(200);
155: sql.append("SELECT seq_name FROM iisequences ");
156: if (owner != null) {
157: sql.append(" WHERE seq_owner = ?");
158: }
159:
160: try {
161: stmt = this .dbConn.prepareStatement(sql.toString());
162: if (owner != null)
163: stmt.setString(1, owner.trim());
164: rs = stmt.executeQuery();
165: while (rs.next()) {
166: String seq = rs.getString(1);
167: if (seq != null)
168: result.add(seq.trim());
169: }
170: } catch (Throwable e) {
171: LogMgr.logError("IngresMetaData.getSequenceList()",
172: "Error when retrieving sequences", e);
173: } finally {
174: SqlUtil.closeAll(rs, stmt);
175: }
176: return result;
177: }
178:
179: public void readSequenceSource(SequenceDefinition def) {
180: if (def == null)
181: return;
182: CharSequence s = getSequenceSource(def.getSequenceOwner(), def
183: .getSequenceName());
184: def.setSource(s);
185: }
186:
187: public String getSequenceSource(String owner, String sequence) {
188: ResultSet rs = null;
189: PreparedStatement stmt = null;
190: StringBuilder result = new StringBuilder(100);
191:
192: String sql = "SELECT trim(SEQ_NAME), \n"
193: + " MIN_VALUE, \n"
194: + " MAX_VALUE, \n"
195: + " INCREMENT_value, \n"
196: + " case CYCLE_FLAG when 'Y' then 'CYCLE' else 'NO CYCLE' end, \n"
197: + " case ORDER_FLAG when 'Y' then 'ORDER' else 'NO ORDER' end, \n"
198: + " CACHE_SIZE \n" + "FROM iisequences \n"
199: + " WHERE seq_owner = ?" + " AND seq_name = ?";
200: try {
201: stmt = this .dbConn.prepareStatement(sql);
202: stmt.setString(1, owner.trim());
203: stmt.setString(2, sequence.trim());
204:
205: rs = stmt.executeQuery();
206: if (rs.next()) {
207: result.append("CREATE SEQUENCE ");
208: result.append(rs.getString(1));
209:
210: BigInteger minvalue = rs.getBigDecimal(2)
211: .toBigInteger();
212: BigInteger maxvalue = rs.getBigDecimal(3)
213: .toBigInteger();
214: long increment = rs.getLong(4);
215: String cycle = rs.getString(5);
216: String order = rs.getString(6);
217: long cache = rs.getLong(7);
218:
219: result.append("\n INCREMENT BY ");
220: result.append(increment);
221:
222: BigInteger one = new BigInteger("1");
223: BigInteger max = new BigInteger(Integer
224: .toString(Integer.MAX_VALUE));
225:
226: if (minvalue.compareTo(one) == 0) {
227: result.append("\n NO MINVALUE");
228: } else {
229: result.append("\n MINVALUE ");
230: result.append(minvalue);
231: }
232:
233: if (maxvalue.compareTo(max) == -1) {
234: result.append("\n MAXVALUE ");
235: result.append(maxvalue);
236: } else {
237: result.append("\n NO MAXVALUE");
238: }
239: if (cache > 0) {
240: result.append("\n CACHE ");
241: result.append(cache);
242: } else {
243: result.append("\n NO CACHE");
244: }
245: result.append("\n ");
246: result.append(cycle);
247:
248: result.append("\n ");
249: result.append(order);
250:
251: result.append(";\n");
252: }
253: } catch (Throwable e) {
254: LogMgr.logError("IngresMetaData.getSequenceSource()",
255: "Error when retrieving sequence source", e);
256: result = new StringBuilder(ExceptionUtil.getDisplay(e));
257: } finally {
258: SqlUtil.closeAll(rs, stmt);
259: }
260: return result.toString();
261: }
262:
263: }
|