001: /*
002: * Db2SequenceReader.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.ibm;
013:
014: import java.sql.PreparedStatement;
015: import java.sql.ResultSet;
016: import java.util.ArrayList;
017: import java.util.Collections;
018: import java.util.LinkedList;
019: import java.util.List;
020: import workbench.db.SequenceDefinition;
021: import workbench.db.SequenceReader;
022: import workbench.db.WbConnection;
023:
024: import workbench.log.LogMgr;
025: import workbench.resource.Settings;
026: import workbench.storage.DataStore;
027: import workbench.util.SqlUtil;
028: import workbench.util.StringUtil;
029:
030: /**
031: * A class to read sequence definitions from a DB2 database.
032: *
033: * @author support@sql-workbench.net
034: */
035: public class Db2SequenceReader implements SequenceReader {
036: private WbConnection connection;
037:
038: public Db2SequenceReader(WbConnection conn) {
039: this .connection = conn;
040: }
041:
042: public List<SequenceDefinition> getSequences(String owner) {
043: DataStore ds = getRawSequenceDefinition(owner, null);
044: if (ds == null || ds.getRowCount() != 1)
045: return Collections.emptyList();
046: List<SequenceDefinition> result = new ArrayList<SequenceDefinition>(
047: ds.getRowCount());
048: for (int row = 0; row < ds.getRowCount(); row++) {
049: result.add(createSequenceDefinition(ds, row));
050: }
051: return result;
052: }
053:
054: public SequenceDefinition getSequenceDefinition(String owner,
055: String sequence) {
056: DataStore ds = getRawSequenceDefinition(owner, sequence);
057: if (ds == null || ds.getRowCount() != 1)
058: return null;
059: return createSequenceDefinition(ds, 0);
060: }
061:
062: private SequenceDefinition createSequenceDefinition(DataStore ds,
063: int row) {
064: String name = ds.getValueAsString(row, "SEQNAME");
065: SequenceDefinition result = new SequenceDefinition(null, name);
066: result.setSequenceProperty("START", ds.getValue(row, "START"));
067: result.setSequenceProperty("MINVALUE", ds.getValue(row,
068: "MINVALUE"));
069: result.setSequenceProperty("MAXVALUE", ds.getValue(row,
070: "MAXVALUE"));
071: result.setSequenceProperty("INCREMENT", ds.getValue(row,
072: "INCREMENT"));
073: result.setSequenceProperty("CYCLE", ds.getValue(row, "CYCLE"));
074: result.setSequenceProperty("ORDER", ds.getValue(row, "ORDER"));
075: result.setSequenceProperty("CACHE", ds.getValue(row, "CACHE"));
076: result.setSequenceProperty("DATATYPEID", ds.getValue(row,
077: "DATATYPEID"));
078: readSequenceSource(result);
079: return result;
080: }
081:
082: public DataStore getRawSequenceDefinition(String schema,
083: String sequence) {
084: String sql = "SELECT SEQNAME, \n" + " START, \n"
085: + " MINVALUE, \n" + " MAXVALUE, \n"
086: + " INCREMENT, \n" + " CYCLE, \n"
087: + " ORDER, \n" + " CACHE, \n"
088: + " DATATYPEID \n"
089: + "FROM syscat.sequences \n" + "WHERE seqschema = ?";
090:
091: if (!StringUtil.isEmptyString(sequence)) {
092: sql += " AND seqname = ? ";
093: }
094:
095: if (Settings.getInstance().getDebugMetadataSql()) {
096: LogMgr.logInfo(
097: "Db2SequenceReader.getRawSequenceDefinition()",
098: "Using query=\n" + sql);
099: }
100:
101: PreparedStatement stmt = null;
102: ResultSet rs = null;
103: DataStore result = null;
104: try {
105: stmt = this .connection.getSqlConnection().prepareStatement(
106: sql);
107: stmt.setString(1, schema);
108: if (!StringUtil.isEmptyString(sequence))
109: stmt.setString(2, sequence);
110: rs = stmt.executeQuery();
111: result = new DataStore(rs, this .connection, true);
112: } catch (Exception e) {
113: LogMgr.logError("OracleMetaData.getSequenceDefinition()",
114: "Error when retrieving sequence definition", e);
115: } finally {
116: SqlUtil.closeAll(rs, stmt);
117: }
118:
119: return result;
120: }
121:
122: /**
123: * Get a list of sequences for the given owner
124: */
125: public List<String> getSequenceList(String schema) {
126: DataStore ds = getRawSequenceDefinition(schema, null);
127: if (ds == null || ds.getRowCount() == 0)
128: return Collections.emptyList();
129: List<String> result = new LinkedList<String>();
130:
131: for (int row = 0; row < ds.getRowCount(); row++) {
132: result.add(ds.getValueAsString(row, "SEQNAME"));
133: }
134: return result;
135: }
136:
137: public CharSequence getSequenceSource(String schema, String sequence) {
138: SequenceDefinition def = getSequenceDefinition(schema, sequence);
139: if (def == null)
140: return null;
141: return def.getSource();
142: }
143:
144: public void readSequenceSource(SequenceDefinition def) {
145: StringBuilder result = new StringBuilder(100);
146:
147: String nl = Settings.getInstance()
148: .getInternalEditorLineEnding();
149:
150: result.append("CREATE SEQUENCE ");
151: result.append(def.getSequenceName());
152:
153: Number start = (Number) def.getSequenceProperty("START");
154: Number minvalue = (Number) def.getSequenceProperty("MINVALUE");
155: Number maxvalue = (Number) def.getSequenceProperty("MAXVALUE");
156: Number increment = (Number) def
157: .getSequenceProperty("INCREMENT");
158: String cycle = (String) def.getSequenceProperty("CYCLE");
159: String order = (String) def.getSequenceProperty("ORDER");
160: Number cache = (Number) def.getSequenceProperty("CACHE");
161: Number typeid = (Number) def.getSequenceProperty("typeid");
162:
163: if (typeid != null) {
164: result.append(" AS " + typeIdToName(typeid.intValue()));
165: }
166: result.append(nl + " INCREMENT BY ");
167: result.append(increment);
168:
169: if (start.longValue() > 0) {
170: result.append(nl + " START WITH ");
171: result.append(start);
172: }
173:
174: if (minvalue == null || minvalue.longValue() == 0) {
175: result.append(nl + " NO MINVALUE");
176: } else {
177: result.append(nl + " MINVALUE ");
178: result.append(minvalue);
179: }
180:
181: if (maxvalue == null || maxvalue.longValue() == -1) {
182: result.append(nl + " MAXVALUE ");
183: result.append(maxvalue);
184: } else {
185: result.append(nl + " NO MAXVALUE");
186: }
187: if (cache != null || cache.longValue() > 0) {
188: result.append(nl + " CACHE ");
189: result.append(cache);
190: } else {
191: result.append(nl + " NO CACHE");
192: }
193:
194: result.append(nl + " ");
195: if (cycle != null && cycle.equals("Y")) {
196: result.append("CYCLE");
197: } else {
198: result.append("NO CYCLE");
199: }
200:
201: result.append(nl + " ");
202: if (order != null && order.equals("Y")) {
203: result.append("ORDER");
204: } else {
205: result.append("NO ORDER");
206: }
207:
208: result.append(';');
209: result.append(nl);
210:
211: def.setSource(result);
212: }
213:
214: private String typeIdToName(int id) {
215: switch (id) {
216: case 20:
217: return "BIGINT";
218: case 28:
219: return "SMALLINT";
220: case 16:
221: return "DECIMAL";
222: }
223: return "INTEGER";
224: }
225: }
|