de.schwarzrot.data.access.support
Class PgUtils

java.lang.Object
  de.schwarzrot.data.access.support.AbstractDbUtils
      de.schwarzrot.data.access.support.PgUtils

All Implemented Interfaces:

DbUtil

public final class PgUtils
extends AbstractDbUtils

postgresql-variant of utility-class to handle physical dependencies to database, like quoting, schema support, ...

As postgres has very poor jdbc support, a lots of hacks have to been added, to bring this kindof database into play.

First - postgres has no metadata support, where SRJRCFrames heavily uses metadata - so we have to build the metadata from postgres system catalog.

Next - the major ugliness of postgres is the inconsistent usage of implicit statements - undocumented and for some statements impossible to use. So - for me - the only way to get postgres working, was the application user being the owner of a database. That's fucking ugly and insecure. I don't like it and so I'll never use postgres, but there may be users out there thinking different and after all, I'd like to support the way to think different.

So - my rules - to get postgres working with java applications

• application user must be the owner of the database used by that application
• application user must get the select-right to system catalog tables granted, especially pg_authid

Author:

Reinhard Mantey

Field Summary

static String	FIELD_SEPARATOR
static String	SCHEMA_SEPARATOR

Fields inherited from class de.schwarzrot.data.access.support.AbstractDbUtils

skipList

Fields inherited from interface de.schwarzrot.data.access.support.DbUtil

EMPTY_STRING, ONE_SPACE, PK_NAME

Constructor Summary

PgUtils()

Method Summary

List<ColumnMetaData>	columnDefinitions(Connection conn, TableDefinition td)
String	genAccessRightsFor(TableDefinition ti, String defaultUser) generates a SQL-string to grant access rights to tables
String	genAccessRightsForPK(TableDefinition ti, String defaultUser) generates a SQL-string to grant access to an external sequence
protected String	genAutoIncrement(ColumnDefinition cd)
String	genColumnModification(ColumnDefinition cd) generates a SQL string to modify a column of an existing table
String	genDBAccess(DatabaseModel dbInfo, String user) generates a SQL-string to grant access rights to the entire database
protected String	genPrimary(TableDefinition ti)
String	genSchemaAccess(SchemaDefinition schemInfo, String defaultUser) generates a SQL-string to grant access rights to schema
String	genSchemaCreation(SchemaDefinition schemInfo) generates a SQL string to create a schema
String	genTablePKCreation(TableDefinition tbInfo) generates a statement to create an external sequence for autoincrement-column
String	genTablePKMod(TableDefinition tbInfo) generates a statement to modify the external sequence after table creation.
String	genTruncate(EntityDescriptor ed) generates a SQL statement that truncates a database table, which means, that all entries will be removed.
String	genUserCreation(String user, String password, String host) generates a SQL statement to create a user/role/principal.
protected String	getDBCharset()
String	getFieldSeparator() returns the separator used in field lists
protected String	getIndexName(IndexDefinition ii)
String	getMapped(String dbObjName) returns the quoted name of database identifiers (table, column, etc), cause we always want do handle case sensitive identifiers
protected String	getOwner()
String	getSchemaSeparator() returns the schema separator for the actual storage.
protected String	getTableAttributes(TableDefinition td)
String	getTableNameFor(EntityDescriptor ed) returns the tablename for given entity
String	getTableNameFor(TableDefinition ti) returns the tablename of given table model information
String	getTerminator() returns the terminator of SQL statements
protected String	getTypeFor(ColumnDefinition ci)
protected ColumnType	getTypeFrom(String rawType)
protected String	getUsernameFor(String user, String host)
List<IndexMetaData>	indexDefinitions(Connection conn, TableDefinition td)
boolean	isExistsSupported() signals whether current database supports exists on drop tables
boolean	needDropIndexAfterDropTable() signals whether an index must be dropped separately after removal of a table
boolean	needExternalSequenceForSerial() signals whether the database needs an external sequence to support autoincrement columns
boolean	needSequenceResetAfterTruncate() signals whether the database needs to set a new value to the sequence on removing all records of a table
protected Long	ownerID(Connection conn)
String	quoteAndCutValue(Object value, int size) some databases pad strings to an unsupported size, so this call ensures, that the quoted string does not exceed the defined column size.
List<SchemaDefinition>	schemaDefinitions(Connection conn)
void	setOwner(String user)
boolean	supportsGeneratedKeyQuery() signals whether the JDBC implementation supports the call for generated (autoincrement) keys.
protected boolean	supportsNativeSchemata()
List<TableDefinition>	tableDefinitions(Connection conn, SchemaDefinition sd)
protected String	xpandInsert(EntityDescriptor ed) some databases need special keywords on insert statements to return the values of autoincrement keys.

Methods inherited from class de.schwarzrot.data.access.support.AbstractDbUtils

createColumnDef, dumpRS, genColumnCreation, genCondition, genCondition, genCondition, genDBCreation, genIndexCreation, genIndexRemoval, genInsert, genInsertIdQuery, genOrder, genOrder, genRemove, genSequenceReset, genTableCreation, genTableMetaAccess, genTableRemoval, genTableRemoval, genUpdate, genUpdate, genUserCreation, getLogger, getMapped, getTypeFrom, needsUpdate, needsUpdate

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

FIELD_SEPARATOR

public static final String FIELD_SEPARATOR

See Also:

Constant Field Values

SCHEMA_SEPARATOR

public static final String SCHEMA_SEPARATOR

See Also:

Constant Field Values

Constructor Detail

PgUtils

public PgUtils()

Method Detail

columnDefinitions

public List<ColumnMetaData> columnDefinitions(Connection conn,
                                              TableDefinition td)

Specified by:

columnDefinitions in interface DbUtil

Overrides:

columnDefinitions in class AbstractDbUtils

genAccessRightsFor

public String genAccessRightsFor(TableDefinition ti,
                                 String defaultUser)

Description copied from interface: DbUtil

generates a SQL-string to grant access rights to tables

Parameters:

ti - - table model information

defaultUser - - the user that gets the rights granted

Returns:

- the ready to use SQL statement

genAccessRightsForPK

public String genAccessRightsForPK(TableDefinition ti,
                                   String defaultUser)

Description copied from interface: DbUtil

generates a SQL-string to grant access to an external sequence

Specified by:

genAccessRightsForPK in interface DbUtil

Overrides:

genAccessRightsForPK in class AbstractDbUtils

Parameters:

ti - - table model information

defaultUser - - the user that gets the rights granted

Returns:

- the ready to use SQL statement

genColumnModification

public String genColumnModification(ColumnDefinition cd)

Description copied from interface: DbUtil

generates a SQL string to modify a column of an existing table

Specified by:

genColumnModification in interface DbUtil

Overrides:

genColumnModification in class AbstractDbUtils

Parameters:

cd - - column model information

Returns:

-the ready to use SQL statement

genDBAccess

public String genDBAccess(DatabaseModel dbInfo,
                          String user)

Description copied from interface: DbUtil

generates a SQL-string to grant access rights to the entire database

Parameters:

dbInfo - - database model information

user - - the user that gets the rights granted

Returns:

- the ready to use SQL statement

genSchemaAccess

public String genSchemaAccess(SchemaDefinition schemInfo,
                              String defaultUser)

Description copied from interface: DbUtil

generates a SQL-string to grant access rights to schema

Specified by:

genSchemaAccess in interface DbUtil

Overrides:

genSchemaAccess in class AbstractDbUtils

Parameters:

schemInfo - - schema model information

defaultUser - - the user that gets the rights granted

Returns:

- the ready to use SQL statement

genSchemaCreation

public String genSchemaCreation(SchemaDefinition schemInfo)

Description copied from interface: DbUtil

generates a SQL string to create a schema

Specified by:

genSchemaCreation in interface DbUtil

Overrides:

genSchemaCreation in class AbstractDbUtils

Parameters:

schemInfo - - schema model information

Returns:

- the ready to use SQL statement

genTablePKCreation

public String genTablePKCreation(TableDefinition tbInfo)

Description copied from interface: DbUtil

generates a statement to create an external sequence for autoincrement-column

Specified by:

genTablePKCreation in interface DbUtil

Overrides:

genTablePKCreation in class AbstractDbUtils

Parameters:

tbInfo - - table model information

Returns:

- the ready to use SQL statement

genTablePKMod

public String genTablePKMod(TableDefinition tbInfo)

Description copied from interface: DbUtil

generates a statement to modify the external sequence after table creation.

Specified by:

genTablePKMod in interface DbUtil

Overrides:

genTablePKMod in class AbstractDbUtils

Parameters:

tbInfo - - table model information

Returns:

- the ready to use SQL statement

genTruncate

public String genTruncate(EntityDescriptor ed)

Description copied from interface: DbUtil

generates a SQL statement that truncates a database table, which means, that all entries will be removed. The truncation of a db table is vendor specific, so for database systems, that support truncation, that statement should be created. For database systems, that don't support truncation, a delete without where clause will be used.

Truncation of a table may require additional permissions beside the permission to delete records.

Specified by:

genTruncate in interface DbUtil

Overrides:

genTruncate in class AbstractDbUtils

Parameters:

ed - - descriptor of the Entity

Returns:

- the ready to use truncate expression

genUserCreation

public String genUserCreation(String user,
                              String password,
                              String host)

Description copied from interface: DbUtil

generates a SQL statement to create a user/role/principal. Use this call, if the users permissions should be bound to given host

Parameters:

user - - name of the user

password - - password for that user

host - - name of the host

Returns:

- the generated SQL statement

getFieldSeparator

public String getFieldSeparator()

Description copied from interface: DbUtil

returns the separator used in field lists

Returns:

a separator string

getMapped

public String getMapped(String dbObjName)

Description copied from interface: DbUtil

returns the quoted name of database identifiers (table, column, etc), cause we always want do handle case sensitive identifiers

Parameters:

dbObjName - - the identifier to quote

Returns:

- the quoted identifier

getSchemaSeparator

public String getSchemaSeparator()

Description copied from interface: DbUtil

returns the schema separator for the actual storage. Storage systems, that don't support schemata should return an '_' (underscore).

Returns:

- separator used for schema handling

getTableNameFor

public String getTableNameFor(EntityDescriptor ed)

Description copied from interface: DbUtil

returns the tablename for given entity

Parameters:

ed - - descriptor of an entity

Returns:

- a tablename

getTableNameFor

public String getTableNameFor(TableDefinition ti)

Description copied from interface: DbUtil

returns the tablename of given table model information

Parameters:

ti - - table model information

Returns:

- the table name

getTerminator

public String getTerminator()

Description copied from interface: DbUtil

returns the terminator of SQL statements

Returns:

- a SQL statement terminator

indexDefinitions

public List<IndexMetaData> indexDefinitions(Connection conn,
                                            TableDefinition td)

Specified by:

indexDefinitions in interface DbUtil

Overrides:

indexDefinitions in class AbstractDbUtils

isExistsSupported

public boolean isExistsSupported()

Description copied from class: AbstractDbUtils

signals whether current database supports exists on drop tables

Specified by:

isExistsSupported in class AbstractDbUtils

Returns:

true if conditional drop tables are supported, otherwise false

needDropIndexAfterDropTable

public boolean needDropIndexAfterDropTable()

Description copied from interface: DbUtil

signals whether an index must be dropped separately after removal of a table

Returns:

true if index must be dropped separately, otherwise false

needExternalSequenceForSerial

public boolean needExternalSequenceForSerial()

Description copied from interface: DbUtil

signals whether the database needs an external sequence to support autoincrement columns

Returns:

- true if the database needs an external sequence, otherwise false

needSequenceResetAfterTruncate

public boolean needSequenceResetAfterTruncate()

Description copied from interface: DbUtil

signals whether the database needs to set a new value to the sequence on removing all records of a table

Returns:

- true if the database needs to set a value to the sequence, otherwise false

quoteAndCutValue

public String quoteAndCutValue(Object value,
                               int size)

Description copied from interface: DbUtil

some databases pad strings to an unsupported size, so this call ensures, that the quoted string does not exceed the defined column size.

Parameters:

value - - the value to store

size - - declared field size

Returns:

- the quoted string that fits the field

schemaDefinitions

public List<SchemaDefinition> schemaDefinitions(Connection conn)

Specified by:

schemaDefinitions in interface DbUtil

Overrides:

schemaDefinitions in class AbstractDbUtils

setOwner

public final void setOwner(String user)

supportsGeneratedKeyQuery

public boolean supportsGeneratedKeyQuery()

Description copied from interface: DbUtil

signals whether the JDBC implementation supports the call for generated (autoincrement) keys.

Returns:

- true if the JDBC implementation supports the call of generatedKeys, otherwise false

tableDefinitions

public List<TableDefinition> tableDefinitions(Connection conn,
                                              SchemaDefinition sd)

Specified by:

tableDefinitions in interface DbUtil

Overrides:

tableDefinitions in class AbstractDbUtils

genAutoIncrement

protected String genAutoIncrement(ColumnDefinition cd)

Specified by:

genAutoIncrement in class AbstractDbUtils

genPrimary

protected String genPrimary(TableDefinition ti)

Specified by:

genPrimary in class AbstractDbUtils

getDBCharset

protected String getDBCharset()

Specified by:

getDBCharset in class AbstractDbUtils

getIndexName

protected String getIndexName(IndexDefinition ii)

Specified by:

getIndexName in class AbstractDbUtils

getOwner

protected final String getOwner()

getTableAttributes

protected String getTableAttributes(TableDefinition td)

Specified by:

getTableAttributes in class AbstractDbUtils

getTypeFor

protected String getTypeFor(ColumnDefinition ci)

Overrides:

getTypeFor in class AbstractDbUtils

getTypeFrom

protected ColumnType getTypeFrom(String rawType)

getUsernameFor

protected String getUsernameFor(String user,
                                String host)

Specified by:

getUsernameFor in class AbstractDbUtils

ownerID

protected Long ownerID(Connection conn)

supportsNativeSchemata

protected final boolean supportsNativeSchemata()

Specified by:

supportsNativeSchemata in class AbstractDbUtils

xpandInsert

protected String xpandInsert(EntityDescriptor ed)

Description copied from class: AbstractDbUtils

some databases need special keywords on insert statements to return the values of autoincrement keys.

Overrides:

xpandInsert in class AbstractDbUtils