001: /*
002: * OracleSequenceReader.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.oracle;
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: import workbench.resource.Settings;
024: import workbench.log.LogMgr;
025: import workbench.storage.DataStore;
026: import workbench.util.SqlUtil;
027: import workbench.util.StringUtil;
028:
029: /**
030: *
031: * @author support@sql-workbench.net
032: */
033: public class OracleSequenceReader implements SequenceReader {
034: private WbConnection connection;
035:
036: public OracleSequenceReader(WbConnection conn) {
037: this .connection = conn;
038: }
039:
040: public List<SequenceDefinition> getSequences(String owner) {
041: DataStore ds = getRawSequenceDefinition(owner, null);
042: if (ds == null || ds.getRowCount() == 0)
043: return Collections.emptyList();
044: ArrayList<SequenceDefinition> result = new ArrayList<SequenceDefinition>();
045: for (int row = 0; row < ds.getRowCount(); row++) {
046: result.add(createDefinition(ds, row));
047: }
048: return result;
049: }
050:
051: public SequenceDefinition getSequenceDefinition(String owner,
052: String sequence) {
053: DataStore ds = getRawSequenceDefinition(owner, sequence);
054: if (ds == null || ds.getRowCount() == 0)
055: return null;
056: SequenceDefinition def = createDefinition(ds, 0);
057: return def;
058: }
059:
060: public DataStore getRawSequenceDefinition(String owner,
061: String sequence) {
062: String sql = "SELECT SEQUENCE_OWNER, SEQUENCE_NAME, \n "
063: + "MIN_VALUE, \n "
064: + "MAX_VALUE, \n "
065: + "INCREMENT_BY, \n "
066: + "decode(CYCLE_FLAG,'Y','CYCLE','NOCYCLE') AS CYCLE_FLAG, \n "
067: + "decode(ORDER_FLAG,'Y','ORDER','NOORDER') AS ORDER_FLAG, \n "
068: + "CACHE_SIZE \n" + "FROM ALL_SEQUENCES \n"
069: + "WHERE sequence_owner = ?";
070:
071: if (!StringUtil.isEmptyString(sequence)) {
072: sql += " AND sequence_name = ? ";
073: }
074:
075: if (Settings.getInstance().getDebugMetadataSql()) {
076: LogMgr.logInfo(
077: "OracleSequenceReader.getRawSequenceDefinition()",
078: "Using query=\n" + sql);
079: }
080:
081: PreparedStatement stmt = null;
082: ResultSet rs = null;
083: DataStore result = null;
084: try {
085: stmt = this .connection.getSqlConnection().prepareStatement(
086: sql);
087: stmt.setString(1, owner);
088: if (!StringUtil.isEmptyString(sequence))
089: stmt.setString(2, sequence);
090: rs = stmt.executeQuery();
091: result = new DataStore(rs, this .connection, true);
092: } catch (Exception e) {
093: LogMgr.logError("OracleMetaData.getSequenceDefinition()",
094: "Error when retrieving sequence definition", e);
095: } finally {
096: SqlUtil.closeAll(rs, stmt);
097: }
098:
099: return result;
100: }
101:
102: /**
103: * Get a list of sequences for the given owner
104: */
105: public List<String> getSequenceList(String owner) {
106: DataStore ds = getRawSequenceDefinition(owner, null);
107:
108: List<String> result = new LinkedList<String>();
109: if (ds == null || ds.getRowCount() == 0)
110: return result;
111:
112: for (int row = 0; row < ds.getRowCount(); row++) {
113: result.add(ds.getValueAsString(row, "SEQUENCE_NAME"));
114: }
115: return result;
116: }
117:
118: public CharSequence getSequenceSource(String owner, String sequence) {
119: SequenceDefinition def = getSequenceDefinition(owner, sequence);
120: if (def == null)
121: return null;
122: return def.getSource();
123: }
124:
125: private SequenceDefinition createDefinition(DataStore ds, int row) {
126: if (ds == null || row >= ds.getRowCount())
127: return null;
128: String name = ds.getValueAsString(row, "SEQUENCE_NAME");
129: String owner = ds.getValueAsString(row, "SEQUENCE_OWNER");
130: SequenceDefinition result = new SequenceDefinition(owner, name);
131: result.setSequenceProperty("MIN_VALUE", ds.getValue(row,
132: "MIN_VALUE"));
133: result.setSequenceProperty("MAX_VALUE", ds.getValue(row,
134: "MAX_VALUE"));
135: result.setSequenceProperty("INCREMENT", ds.getValue(row,
136: "INCREMENT_BY"));
137: result.setSequenceProperty("CYCLE_FLAG", ds.getValue(row,
138: "CYCLE_FLAG"));
139: result.setSequenceProperty("CACHE_SIZE", ds.getValue(row,
140: "CACHE_SIZE"));
141: result.setSequenceProperty("ORDER_FLAG", ds.getValue(row,
142: "ORDER_FLAG"));
143: readSequenceSource(result);
144: return result;
145: }
146:
147: public void readSequenceSource(SequenceDefinition def) {
148: if (def == null)
149: return;
150: if (def.getSource() != null)
151: return;
152:
153: StringBuilder result = new StringBuilder(100);
154: String nl = Settings.getInstance()
155: .getInternalEditorLineEnding();
156:
157: result.append("CREATE SEQUENCE ");
158: result.append(def.getSequenceName());
159:
160: Number minValue = (Number) def.getSequenceProperty("MIN_VALUE");
161: Number maxValue = (Number) def.getSequenceProperty("MAX_VALUE");
162:
163: Number increment = (Number) def
164: .getSequenceProperty("INCREMENT");
165:
166: String cycle = (String) def.getSequenceProperty("CYCLE_FLAG");
167: String order = (String) def.getSequenceProperty("ORDER_FLAG");
168: Number cache = (Number) def.getSequenceProperty("CACHE_SIZE");
169:
170: result.append(nl + " INCREMENT BY ");
171: result.append(increment);
172:
173: if (minValue != null && minValue.intValue() != 0) {
174: result.append(nl + " NOMINVALUE");
175: } else {
176: result.append(nl + " MINVALUE ");
177: result.append(minValue);
178: }
179:
180: if (maxValue != null
181: && !maxValue.toString().equals(
182: "999999999999999999999999999")) {
183: result.append(nl + " MAXVALUE ");
184: result.append(maxValue);
185: } else {
186: result.append(nl + " NOMAXVALUE");
187: }
188:
189: if (cache != null && cache.longValue() > 0) {
190: result.append(nl + " CACHE ");
191: result.append(cache);
192: } else {
193: result.append(nl + " NOCACHE");
194: }
195: result.append(nl + " ");
196: result.append(cycle);
197:
198: result.append(nl + " ");
199: result.append(order);
200:
201: result.append(';');
202: result.append(nl);
203:
204: def.setSource(result);
205: }
206:
207: }
|