001: package liquibase.database;
002:
003: import liquibase.database.sql.RawSqlStatement;
004: import liquibase.database.sql.SqlStatement;
005: import liquibase.exception.JDBCException;
006: import liquibase.util.StringUtils;
007:
008: import java.sql.Connection;
009: import java.sql.Types;
010: import java.text.ParseException;
011: import java.util.HashSet;
012: import java.util.Set;
013:
014: /**
015: * Encapsulates PostgreSQL database support.
016: */
017: public class PostgresDatabase extends AbstractDatabase {
018: public static final String PRODUCT_NAME = "PostgreSQL";
019:
020: private Set<String> systemTablesAndViews = new HashSet<String>();
021:
022: public PostgresDatabase() {
023: // systemTablesAndViews.add("pg_logdir_ls");
024: // systemTablesAndViews.add("administrable_role_authorizations");
025: // systemTablesAndViews.add("applicable_roles");
026: // systemTablesAndViews.add("attributes");
027: // systemTablesAndViews.add("check_constraint_routine_usage");
028: // systemTablesAndViews.add("check_constraints");
029: // systemTablesAndViews.add("column_domain_usage");
030: // systemTablesAndViews.add("column_privileges");
031: // systemTablesAndViews.add("column_udt_usage");
032: // systemTablesAndViews.add("columns");
033: // systemTablesAndViews.add("constraint_column_usage");
034: // systemTablesAndViews.add("constraint_table_usage");
035: // systemTablesAndViews.add("data_type_privileges");
036: // systemTablesAndViews.add("domain_constraints");
037: // systemTablesAndViews.add("domain_udt_usage");
038: // systemTablesAndViews.add("domains");
039: // systemTablesAndViews.add("element_types");
040: // systemTablesAndViews.add("enabled_roles");
041: // systemTablesAndViews.add("key_column_usage");
042: // systemTablesAndViews.add("parameters");
043: // systemTablesAndViews.add("referential_constraints");
044: // systemTablesAndViews.add("role_column_grants");
045: // systemTablesAndViews.add("role_routine_grants");
046: // systemTablesAndViews.add("role_table_grants");
047: // systemTablesAndViews.add("role_usage_grants");
048: // systemTablesAndViews.add("routine_privileges");
049: // systemTablesAndViews.add("routines");
050: // systemTablesAndViews.add("schemata");
051: // systemTablesAndViews.add("sequences");
052: // systemTablesAndViews.add("sql_features");
053: // systemTablesAndViews.add("sql_implementation_info");
054: // systemTablesAndViews.add("sql_languages");
055: // systemTablesAndViews.add("sql_packages");
056: // systemTablesAndViews.add("sql_parts");
057: // systemTablesAndViews.add("sql_sizing");
058: // systemTablesAndViews.add("sql_sizing_profiles");
059: // systemTablesAndViews.add("table_constraints");
060: // systemTablesAndViews.add("table_privileges");
061: // systemTablesAndViews.add("tables");
062: // systemTablesAndViews.add("triggers");
063: // systemTablesAndViews.add("usage_privileges");
064: // systemTablesAndViews.add("view_column_usage");
065: // systemTablesAndViews.add("view_routine_usage");
066: // systemTablesAndViews.add("view_table_usage");
067: // systemTablesAndViews.add("views");
068: // systemTablesAndViews.add("information_schema_catalog_name");
069: // systemTablesAndViews.add("triggered_update_columns");
070: // systemTablesAndViews.add("book_pkey");
071: }
072:
073: public String getProductName() {
074: return "PostgreSQL";
075: }
076:
077: public String getTypeName() {
078: return "postgresql";
079: }
080:
081: public Set<String> getSystemTablesAndViews() {
082: return systemTablesAndViews;
083: }
084:
085: public boolean supportsInitiallyDeferrableColumns() {
086: return true;
087: }
088:
089: public boolean isCorrectDatabaseImplementation(Connection conn)
090: throws JDBCException {
091: return PRODUCT_NAME
092: .equalsIgnoreCase(getDatabaseProductName(conn));
093: }
094:
095: public String getDefaultDriver(String url) {
096: if (url.startsWith("jdbc:postgresql:")) {
097: return "org.postgresql.Driver";
098: }
099: return null;
100: }
101:
102: public String getBooleanType() {
103: return "BOOLEAN";
104: }
105:
106: public String getCurrencyType() {
107: return "DECIMAL";
108: }
109:
110: public String getUUIDType() {
111: return "CHAR(36)";
112: }
113:
114: public String getClobType() {
115: return "TEXT";
116: }
117:
118: public String getBlobType() {
119: return "BYTEA";
120: }
121:
122: public String getDateTimeType() {
123: return "TIMESTAMP WITH TIME ZONE";
124: }
125:
126: public boolean supportsSequences() {
127: return true;
128: }
129:
130: public String getCurrentDateTimeFunction() {
131: return "NOW()";
132: }
133:
134: protected String getDefaultDatabaseSchemaName()
135: throws JDBCException {
136: return null;
137: }
138:
139: public String getDefaultCatalogName() throws JDBCException {
140: return "PUBLIC";
141: }
142:
143: public String getDatabaseChangeLogTableName() {
144: return super .getDatabaseChangeLogTableName().toLowerCase();
145: }
146:
147: public String getDatabaseChangeLogLockTableName() {
148: return super .getDatabaseChangeLogLockTableName().toLowerCase();
149: }
150:
151: // public void dropDatabaseObjects(String schema) throws JDBCException {
152: // try {
153: // if (schema == null) {
154: // schema = getConnectionUsername();
155: // }
156: // new JdbcTemplate(this).execute(new RawSqlStatement("DROP OWNED BY " + schema));
157: //
158: // getConnection().commit();
159: //
160: // changeLogTableExists = false;
161: // changeLogLockTableExists = false;
162: // changeLogCreateAttempted = false;
163: // changeLogLockCreateAttempted = false;
164: //
165: // } catch (SQLException e) {
166: // throw new JDBCException(e);
167: // }
168: // }
169:
170: public SqlStatement createFindSequencesSQL(String schema)
171: throws JDBCException {
172: return new RawSqlStatement(
173: "SELECT relname AS SEQUENCE_NAME FROM pg_class, pg_namespace WHERE relkind='S' AND pg_class.relnamespace = pg_namespace.oid AND nspname = '"
174: + convertRequestedSchemaToSchema(schema)
175: + "' AND 'nextval(''"
176: + (schema == null ? "" : schema + ".")
177: + "'||relname||'''::regclass)' not in (select adsrc from pg_attrdef where adsrc is not null) AND 'nextval('''||relname||'''::regclass)' not in (select adsrc from pg_attrdef where adsrc is not null)");
178: }
179:
180: public boolean isSystemTable(String catalogName, String schemaName,
181: String tableName) {
182: return super .isSystemTable(catalogName, schemaName, tableName)
183: || "pg_catalog".equals(schemaName)
184: || "pg_toast".equals(schemaName)
185: || tableName.endsWith("_seq")
186: || tableName.endsWith("_key")
187: || tableName.endsWith("_pkey")
188: || tableName.startsWith("idx_")
189: || tableName.startsWith("pk_");
190: }
191:
192: public boolean supportsTablespaces() {
193: return true;
194: }
195:
196: public SqlStatement getViewDefinitionSql(String schemaName,
197: String name) throws JDBCException {
198: return new RawSqlStatement(
199: "select definition from pg_views where viewname='"
200: + name + "' AND schemaname='"
201: + convertRequestedSchemaToSchema(schemaName)
202: + "'");
203: }
204:
205: public String getColumnType(String columnType, Boolean autoIncrement) {
206: if (columnType.startsWith("java.sql.Types.VARCHAR")) { //returns "name" for type
207: return columnType.replace("java.sql.Types.", "");
208: }
209:
210: String type = super .getColumnType(columnType, autoIncrement);
211:
212: if (type.startsWith("TEXT(")) {
213: return getClobType();
214: } else if (type.toLowerCase().startsWith("float8")) {
215: return "FLOAT8";
216: } else if (type.toLowerCase().startsWith("float4")) {
217: return "FLOAT4";
218: }
219:
220: if (autoIncrement != null && autoIncrement) {
221: if ("integer".equals(type.toLowerCase())) {
222: return "serial";
223: } else if ("bigint".equals(type.toLowerCase())) {
224: return "bigserial";
225: } else {
226: // Unknown integer type, default to "serial"
227: return "serial";
228: }
229: }
230:
231: return type;
232: }
233:
234: public String getAutoIncrementClause() {
235: return "";
236: }
237:
238: public Object convertDatabaseValueToJavaObject(Object defaultValue,
239: int dataType, int columnSize, int decimalDigits)
240: throws ParseException {
241: if (defaultValue != null) {
242: if (defaultValue instanceof String) {
243: defaultValue = ((String) defaultValue).replaceAll(
244: "'::[\\w\\s]+$", "'");
245:
246: if (dataType == Types.DATE || dataType == Types.TIME
247: || dataType == Types.TIMESTAMP) {
248: //remove trailing time zone info
249: defaultValue = ((String) defaultValue)
250: .replaceFirst("-\\d+$", "");
251: }
252: }
253: }
254: return super .convertDatabaseValueToJavaObject(defaultValue,
255: dataType, columnSize, decimalDigits);
256:
257: }
258:
259: public String convertRequestedSchemaToSchema(String requestedSchema)
260: throws JDBCException {
261: if (requestedSchema == null) {
262: return "public";
263: } else {
264: return StringUtils.trimToNull(requestedSchema)
265: .toLowerCase();
266: }
267: }
268:
269: public String convertRequestedSchemaToCatalog(String requestedSchema)
270: throws JDBCException {
271: return null;
272: }
273: }
|