List of usage examples for javax.sql.rowset CachedRowSet first
boolean first() throws SQLException;
ResultSet object. From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.ColumnStructureServiceImpl.java
public void updateColumn(OrdsPhysicalDatabase database, String tableName, String columnName, ColumnRequest request, boolean staging) throws Exception { String databaseName = database.getDbConsumedName(); if (staging) { databaseName = this.calculateStagingName(databaseName); }/* w ww . j ava 2 s. co m*/ String server = database.getDatabaseServer(); if (!this.checkColumnExists(columnName, tableName, databaseName, server)) { throw new NotFoundException(String.format("Column name %s does not exist", columnName)); } log.debug("doQuery"); String newName = request.getNewname(); Boolean nullable = request.isNullable(); String datatype = request.getDatatype(); String defaultValue = request.getDefaultvalue(); Boolean autoinc = request.isAutoincrement(); String sequenceName = ""; String query; String message = ""; // Check that some new metadata has been specified if ((newName == null || newName.isEmpty()) && nullable == null && (datatype == null || datatype.isEmpty()) && defaultValue == null && autoinc == null) { log.error("Null values set in column request"); throw new BadParameterException("Null values in column request"); } if (autoinc != null) { // If the auto-incrament status is being changed if (autoinc) { // If we're "enabling" autoincrement, check that a // default value hasn't been set as well. if (defaultValue != null && !defaultValue.isEmpty()) { log.error("Specified a default for an auto-increment field"); throw new BadParameterException("You cannot specify a default for an auto-increment field"); } // Get the data type and any current sequence for the // column. String command = "SELECT data_type, pg_get_serial_sequence(?, ?) AS sequence" + " FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ?" + " AND column_name = ?"; List<Object> parameters = this.createParameterList(tableName, columnName, tableName, columnName); CachedRowSet results = this.runJDBCQuery(command, parameters, server, databaseName); String currentDatatype = ""; while (results.next()) { currentDatatype = results.getString("data_type"); sequenceName = results.getString("sequence"); } if (sequenceName != null && !sequenceName.isEmpty()) { message = String.format("Cannot add auto-increment to field %s: it is already auto-incremented", columnName); log.error(message); throw new BadParameterException(message); } if (!integerTypes.contains(currentDatatype.toLowerCase()) && (datatype == null || datatype.isEmpty() || !integerTypes.contains(datatype.toLowerCase()))) { if (datatype != null && !integerTypes.contains(datatype)) { message = String.format("Attempted to create sequence for column %s with datatype %s", columnName, datatype); } else { message = String.format("Attempted to create sequence for column %s with datatype %s", columnName, currentDatatype); } throw new BadParameterException("Only integer fields can have auto-increment enabled"); } } else { // If we're "disabling" autoincrement, check that there's // an existing sequence to remove. String command = "SELECT pg_get_serial_sequence(?, ?) AS sequence"; List<Object> parameters = this.createParameterList(tableName, columnName); CachedRowSet results = this.runJDBCQuery(command, parameters, server, databaseName); if (!results.first()) { log.error("Attempt to remove autoincrement where non is set"); throw new BadParameterException("Auto-increment is not set so cannot be removed"); } else { sequenceName = results.getString("sequence"); } } } // If a new name for the column is specified, check that a column // with that name doesn't already exist in the table if (newName != null && !newName.isEmpty() && checkColumnExists(newName, tableName, databaseName, server)) { log.error("Attempted to rename column %s to existing name %s in table %s", columnName, newName, tableName); message = String.format("Cannot rename field to %s: field with that name already exists in table %s", newName, tableName); throw new BadParameterException(message); } // Validation all done, now perform the specified operations // If we're changing the nullability, create an execute the // approprate ALTER TABLE query if (nullable != null) { String operation; if (nullable) { operation = "DROP"; message += String.format("Field %s no longer nullable", columnName) + "\n"; } else { operation = "SET"; message += String.format("Field %s now nullable", columnName) + "\n"; } query = String.format("ALTER TABLE %s ALTER %s %s NOT NULL;", quote_ident(tableName), quote_ident(columnName), operation); this.runJDBCQuery(query, null, server, databaseName); } ArrayList<String> statements = new ArrayList<String>(); if (datatype != null && !datatype.isEmpty()) { // If the data type is being altered get the existing data type String convertedCol = quote_ident(columnName); String command = "SELECT data_type" + " FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ?" + " AND column_name = ?"; List<Object> parameters = this.createParameterList(tableName, columnName); CachedRowSet results = this.runJDBCQuery(command, parameters, server, databaseName); String fromDataType = ""; while (results.next()) { fromDataType = results.getString("data_type"); } if (fromDataType.equals("integer") || fromDataType.equals("bigint")) { if (datatype.toLowerCase().contains("date") || datatype.toLowerCase().contains("time")) { convertedCol = String.format("to_timestamp(%s)", quote_ident(columnName)); } } else if (fromDataType.contains("date") || fromDataType.contains("time")) { if (datatype.toLowerCase().equals("integer") || datatype.toLowerCase().equals("bigint")) { convertedCol = String.format("extract(epoch from %s)", quote_ident(columnName)); } } datatype = SqlDesignerTranslations.convertDatatypeForPostgres(request.getDatatype()); statements.add(String.format("ALTER TABLE %1$s ALTER %2$s TYPE %3$s USING CAST(%4$s AS %3$s)", quote_ident(tableName), quote_ident(columnName), datatype, convertedCol)); } if (autoinc != null) { // If we're changing the autoincrement status if (autoinc) { // If we're enabline autoincrement, create a new sequence // and attach it to the column, then alter the default // value of the column to the next value in the sequence sequenceName = generateSequenceName(tableName, columnName); statements.add(String.format("CREATE SEQUENCE %s", quote_ident(sequenceName))); statements.add(String.format("ALTER SEQUENCE %s OWNED BY %s.%s", quote_ident(sequenceName), quote_ident(tableName), quote_ident(columnName))); statements.add(String.format("ALTER TABLE %s ALTER %s SET DEFAULT nextval('%s')", quote_ident(tableName), quote_ident(columnName), sequenceName)); // message += String.format(emd.getMessage("Rst066").getText(), // columnName); } else { // If we're disabling autoincrement, remove the default // value and drop the sequence. statements.add(String.format("ALTER TABLE %s ALTER %s DROP DEFAULT", quote_ident(tableName), quote_ident(columnName))); statements.add(String.format("DROP SEQUENCE %s", sequenceName)); // message += String.format(emd.getMessage("Rst067").getText(), // columnName); } } // Always do defaultValue after autoinc, in case we're removing // an autoinc and adding a default. if (defaultValue != null) { if (defaultValue.isEmpty()) { statements.add(String.format("ALTER TABLE %s ALTER %s DROP DEFAULT", quote_ident(tableName), quote_ident(columnName))); // message += String.format(emd.getMessage("Rst023").getText(), // columnName)+"\n"; } else { statements.add(String.format("ALTER TABLE %s ALTER %s SET DEFAULT %s", quote_ident(tableName), quote_ident(columnName), quote_literal(defaultValue))); // message += String.format(emd.getMessage("Rst024").getText(), // columnName, // defaultValue)+"\n"; } } // Always do the rename last so that everything else works. if (newName != null && !newName.isEmpty()) { statements.add(String.format("ALTER TABLE %s RENAME %s to %s;", quote_ident(tableName), quote_ident(columnName), quote_ident(newName))); // message += String.format(emd.getMessage("Rst019").getText(), // columnName, newName)+"\n"; } // run em in 1 go this.runSQLStatements(statements, server, databaseName); }
From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.ConstraintServiceImpl.java
@Override public void createConstraint(OrdsPhysicalDatabase database, String tableName, String constraintName, ConstraintRequest newConstraint, boolean staging) throws Exception { String query = ""; String databaseName = database.getDbConsumedName(); if (staging) { databaseName = this.calculateStagingName(databaseName); }/*from w w w . jav a2 s. c o m*/ String server = database.getDatabaseServer(); // message = String.format(emd.getMessage("Rst048").getText(), // constraintName); Boolean isUnique = newConstraint.isUnique(); Boolean isForeign = newConstraint.isForeign(); Boolean isPrimary = newConstraint.isPrimary(); List<String> columnsList = new ArrayList<String>(); String[] columnsArray = newConstraint.getColumns(); String columns = ""; if ((isUnique != null && isUnique) || (isForeign != null && isForeign) || (isPrimary != null && isPrimary)) { // If we're creating a Unique, Foreign or Primary Key constraint if (columnsArray == null || columnsArray.length == 0) { // If no columns are specified, return an error // message = emd.getMessage("Rst035").getText(); throw new BadParameterException("No columns specified"); } else if ((isUnique != null && isUnique) || (isPrimary != null && isPrimary)) { // If we're creating a Unique or Primary Key constraint, // join the columns into a string. for (String column : newConstraint.getColumns()) { columnsList.add(quote_ident(column)); } columns = StringUtils.join(columnsList.iterator(), ","); } else { // If we're creating a foreign key, make sure there's // only one column if (columnsArray.length > 1) { // message = emd.getMessage("Rst068").getText(); throw new BadParameterException("Only 1 column can be specified for a foreign key"); } } } // Check that the specified table exists if (!this.checkTableExists(tableName, databaseName, server)) { log.error("Tried to create constraint %s for non-existant table %s", constraintName, tableName); // message = String.format(emd.getMessage("Rst052").getText(), // tableName); throw new NotFoundException(); } // Get the next value from a special sequence created when the // database is first cloned in ORDS, which makes sure we can // create a unique name for the constraint String conIdQuery = "SELECT nextval('ords_constraint_seq'::regclass) AS id"; String uniqueConstraintName = ""; CachedRowSet result = this.runJDBCQuery(conIdQuery, null, server, databaseName); // Object result = this.singleResultQuery(conIdQuery, databaseName, // userName, password); if (result != null && result.size() > 0) { // Actually generate a name for the constraint result.first(); int conId = result.getInt("id"); uniqueConstraintName = String.format(constraintName + "_%d", conId); } else { uniqueConstraintName = constraintName; } // Generate the SQL for creating the constraint if (isUnique != null && isUnique) { query = String.format("ALTER TABLE %s ADD CONSTRAINT %s UNIQUE (%s)", quote_ident(tableName), quote_ident(uniqueConstraintName), columns); } else if (isPrimary != null && isPrimary) { query = String.format("ALTER TABLE %s ADD CONSTRAINT %s PRIMARY KEY (%s)", quote_ident(tableName), quote_ident(uniqueConstraintName), columns); } else if (isForeign != null && isForeign) { String column = newConstraint.getColumns()[0]; String refTable = newConstraint.getReftable(); String refColumn = newConstraint.getRefcolumn(); // If this is a foreign key, make sure there is a // referenced table specified if (refTable == null || refTable.isEmpty()) { // message = emd.getMessage("Rst049").getText(); throw new BadParameterException("A foreign key must have a reference table specified"); } // Make sure there is a referenced column specified if (refColumn == null || refColumn.isEmpty()) { // message = emd.getMessage("Rst051").getText(); throw new BadParameterException("A reference column must be specified"); } query = String.format("ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) " + "REFERENCES %s (%s)", quote_ident(tableName), quote_ident(uniqueConstraintName), quote_ident(column), quote_ident(refTable), quote_ident(refColumn)); } else { // If this isn't a Unique, Foreign or Primary key constraint // make sure a check expression is defined and generate the // SQL. Check constraints currently aren't implemented in // the Schema Designer interface. String checkExpression = newConstraint.getCheckExpression(); if (checkExpression == null || checkExpression.isEmpty()) { // message = emd.getMessage("Rst051").getText(); throw new BadParameterException("Check constraints are not supported"); } query = String.format("ALTER TABLE %s ADD CONSTRAINT %s CHECK (%s) ", quote_ident(tableName), quote_ident(uniqueConstraintName), checkExpression); } // Check that a constraint with this name doesn't already exist. if (this.checkConstraintExists(tableName, uniqueConstraintName, databaseName, server)) { log.error("Tried to create duplicate constraint name %s on table %s", uniqueConstraintName, tableName); // message = String.format(emd.getMessage("Rst053").getText(), // uniqueConstraintName, // tableName); throw new NamingConflictException("Can't duplication constraint name"); } // Create the constraint this.runJDBCQuery(query, null, server, databaseName); }