001: /*
002: * OracleConstraintReader.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.Connection;
015: import java.sql.PreparedStatement;
016: import java.sql.ResultSet;
017:
018: import workbench.db.AbstractConstraintReader;
019: import workbench.db.TableIdentifier;
020: import workbench.log.LogMgr;
021: import workbench.resource.Settings;
022: import workbench.sql.formatter.SQLLexer;
023: import workbench.sql.formatter.SQLToken;
024: import workbench.util.SqlUtil;
025:
026: /**
027: *
028: * @author support@sql-workbench.net
029: */
030: public class OracleConstraintReader extends AbstractConstraintReader {
031: private static final String TABLE_SQL = "SELECT constraint_name, search_condition \n"
032: + "FROM all_constraints cons \n"
033: + "WHERE constraint_type = 'C' \n"
034: + " and owner = ? \n"
035: + " and table_name = ? \n";
036:
037: public OracleConstraintReader() {
038: }
039:
040: public int getIndexForSchemaParameter() {
041: return 1;
042: }
043:
044: public int getIndexForCatalogParameter() {
045: return -1;
046: }
047:
048: public int getIndexForTableNameParameter() {
049: return 2;
050: }
051:
052: public String getPrefixTableConstraintKeyword() {
053: return "CHECK (";
054: }
055:
056: public String getSuffixTableConstraintKeyword() {
057: return ")";
058: }
059:
060: public String getColumnConstraintSql() {
061: return null;
062: }
063:
064: public String getTableConstraintSql() {
065: return TABLE_SQL;
066: }
067:
068: public String getTableConstraints(Connection dbConnection,
069: TableIdentifier aTable, String indent) {
070: String sql = this .getTableConstraintSql();
071: if (sql == null)
072: return null;
073: StringBuilder result = new StringBuilder(100);
074: ResultSet rs = null;
075: PreparedStatement stmt = null;
076: String nl = Settings.getInstance()
077: .getInternalEditorLineEnding();
078: try {
079: stmt = dbConnection.prepareStatement(sql);
080: stmt.setString(1, aTable.getSchema());
081: stmt.setString(2, aTable.getTableName());
082:
083: rs = stmt.executeQuery();
084: int count = 0;
085: while (rs.next()) {
086: String name = rs.getString(1);
087: String constraint = rs.getString(2);
088: if (constraint != null) {
089: // NOT NULL constraints do not need to be taken into account
090: if (isDefaultNNConstraint(constraint))
091: continue;
092: if (count > 0) {
093: result.append(nl);
094: result.append(indent);
095: result.append(',');
096: }
097: if (!name.startsWith("SYS_")) {
098: result.append("CONSTRAINT ");
099: result.append(name);
100: result.append(' ');
101: }
102: result.append("CHECK (");
103: result.append(constraint);
104: result.append(')');
105: count++;
106: }
107: }
108: } catch (Exception e) {
109: LogMgr.logError("OracleConstraintReader",
110: "Error when reading column constraints", e);
111: } finally {
112: SqlUtil.closeAll(rs, stmt);
113: }
114: return result.toString();
115: }
116:
117: /**
118: * Checks if the constraint definition is a "default" Not null definition
119: * as created by Oracle. Those constraints will be included in the column
120: * definition already and do not need to be returned.
121: * but a definition like COL_1 IS NOT NULL OR COL_2 IS NOT NULL must
122: * not be treated as a "default" constraint.
123: *
124: * A "default" NN constraint is assumed if an identifier is
125: * immediately followed by the keyword IS NOT NULL and no further
126: * definitions exist.
127: */
128: protected boolean isDefaultNNConstraint(String definition) {
129: try {
130: SQLLexer lexer = new SQLLexer(definition);
131: SQLToken tok = lexer.getNextToken(false, false);
132: if (tok == null)
133: return false;
134:
135: if (!tok.isIdentifier())
136: return false;
137:
138: // If no further tokens exist, this cannot be a not null constraint
139: tok = lexer.getNextToken(false, false);
140: if (tok == null)
141: return false;
142:
143: SQLToken tok2 = lexer.getNextToken(false, false);
144: if (tok2 == null) {
145: return "IS NOT NULL"
146: .equalsIgnoreCase(tok.getContents());
147: } else {
148: return false;
149: }
150: } catch (Exception e) {
151: return false;
152: }
153: }
154:
155: }
|