001: /*
002: * PostgresSequenceReader.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.postgres;
013:
014: import java.sql.DatabaseMetaData;
015: import java.sql.PreparedStatement;
016: import java.sql.ResultSet;
017: import java.sql.SQLException;
018: import java.sql.Savepoint;
019: import java.sql.Statement;
020: import java.util.ArrayList;
021: import java.util.Collections;
022: import java.util.List;
023: import workbench.db.SequenceDefinition;
024: import workbench.db.SequenceReader;
025: import workbench.db.WbConnection;
026: import workbench.log.LogMgr;
027: import workbench.resource.Settings;
028: import workbench.util.SqlUtil;
029: import workbench.storage.DataStore;
030:
031: /**
032: * @author support@sql-workbench.net
033: */
034: public class PostgresSequenceReader implements SequenceReader {
035: private WbConnection dbConnection;
036:
037: public PostgresSequenceReader(WbConnection conn) {
038: this .dbConnection = conn;
039: }
040:
041: public void readSequenceSource(SequenceDefinition def) {
042: if (def == null)
043: return;
044: String nl = Settings.getInstance()
045: .getInternalEditorLineEnding();
046:
047: StringBuilder buf = new StringBuilder(250);
048:
049: try {
050: String name = def.getSequenceName();
051: Long max = (Long) def.getSequenceProperty("MAXVALUE");
052: Long min = (Long) def.getSequenceProperty("MINVALUE");
053: Long inc = (Long) def.getSequenceProperty("INCREMENT");
054: Long cache = (Long) def.getSequenceProperty("CACHE");
055: String cycle = (String) def.getSequenceProperty("CYCLE");
056:
057: buf.append("CREATE SEQUENCE ");
058: buf.append(name);
059: if (inc != 1) {
060: buf.append("\n INCREMENT ");
061: buf.append(inc);
062: }
063: if (min != 1) {
064: buf.append(nl + " MINVALUE ");
065: buf.append(min);
066: }
067: long maxMarker = 9223372036854775807l;
068: if (max != maxMarker) {
069: buf.append(nl + " MAXVALUE ");
070: buf.append(max.toString());
071: }
072: if (cache != 1) {
073: buf.append(nl + " CACHE ");
074: buf.append(cache);
075: }
076: if ("true".equalsIgnoreCase(cycle)) {
077: buf.append(nl + " CYCLE");
078: }
079: buf.append(";\n");
080: } catch (Exception e) {
081: LogMgr.logError("PgSequenceReader.getSequenceSource()",
082: "Error reading sequence definition", e);
083: }
084:
085: def.setSource(buf);
086:
087: return;
088: }
089:
090: /**
091: * Return the source SQL for a PostgreSQL sequence definition.
092: *
093: * @return The SQL to recreate the given sequence
094: */
095: public CharSequence getSequenceSource(String owner, String aSequence) {
096: SequenceDefinition def = getSequenceDefinition(owner, aSequence);
097: return def.getSource();
098: }
099:
100: public List<String> getSequenceList(String owner) {
101: // Already returned by JDBC driver
102: return Collections.emptyList();
103: }
104:
105: /**
106: * Retrieve the list of full SequenceDefinitions from the database.
107: */
108: public List<SequenceDefinition> getSequences(String owner) {
109: List<SequenceDefinition> result = new ArrayList<SequenceDefinition>();
110:
111: ResultSet rs = null;
112: PreparedStatement stmt = null;
113: Savepoint sp = null;
114: try {
115: sp = this .dbConnection.setSavepoint();
116: DatabaseMetaData meta = this .dbConnection
117: .getSqlConnection().getMetaData();
118: rs = meta.getTables(null, owner, "%",
119: new String[] { "SEQUENCE" });
120: while (rs.next()) {
121: String seq_name = rs.getString("TABLE_NAME");
122: String schema = rs.getString("TABLE_SCHEM");
123: SequenceDefinition def = getSequenceDefinition(schema,
124: seq_name);
125: result.add(def);
126: }
127: this .dbConnection.releaseSavepoint(sp);
128: } catch (SQLException e) {
129: this .dbConnection.rollback(sp);
130: LogMgr.logError("PostgresSequenceReader.getSequences()",
131: "Error retrieving sequences", e);
132: return null;
133: } finally {
134: SqlUtil.closeAll(rs, stmt);
135: }
136: return result;
137: }
138:
139: public SequenceDefinition getSequenceDefinition(String owner,
140: String sequence) {
141: if (sequence == null)
142: return null;
143:
144: int pos = sequence.indexOf('.');
145: if (pos > 0) {
146: sequence = sequence.substring(pos);
147: }
148:
149: String nl = Settings.getInstance()
150: .getInternalEditorLineEnding();
151:
152: Statement stmt = null;
153: ResultSet rs = null;
154: SequenceDefinition result = new SequenceDefinition(owner,
155: sequence);
156: Savepoint sp = null;
157: try {
158: String sql = "SELECT max_value, min_value, increment_by, cache_value, is_cycled FROM "
159: + sequence;
160: sp = this .dbConnection.setSavepoint();
161: stmt = this .dbConnection.createStatement();
162: rs = stmt.executeQuery(sql);
163: if (rs.next()) {
164: long max = rs.getLong(1);
165: long min = rs.getLong(2);
166: long inc = rs.getLong(3);
167: long cache = rs.getLong(4);
168: String cycle = rs.getString(5);
169:
170: result.setSequenceProperty("INCREMENT", new Long(inc));
171: result.setSequenceProperty("MINVALUE", new Long(min));
172: result.setSequenceProperty("CACHE", cache);
173: result.setSequenceProperty("CYCLE", cycle);
174: result.setSequenceProperty("MAXVALUE", new Long(max));
175: readSequenceSource(result);
176: }
177: this .dbConnection.releaseSavepoint(sp);
178: } catch (SQLException e) {
179: this .dbConnection.rollback(sp);
180: LogMgr.logDebug("PgSequenceReader.getSequenceDefinition()",
181: "Error reading sequence definition", e);
182: result = null;
183: } finally {
184: SqlUtil.closeAll(rs, stmt);
185: }
186: return result;
187: }
188:
189: public DataStore getRawSequenceDefinition(String owner,
190: String sequence) {
191: // The definition can be displayed by doing a SELECT * FROM sequence
192: // so we don't need to build up the datastore here.
193: return null;
194: }
195: }
|