Search in sources :

Example 11 with ColumnDescription

use of com.axway.ats.core.dbaccess.ColumnDescription in project ats-framework by Axway.

the class AbstractEnvironmentHandler method writeBackupToFile.

/**
     * Write the backup to a file
     *
     * @param fileWriter the file writer
     * @throws IOException on io error
     * @throws DatabaseEnvironmentCleanupException on error
     * @throws DbException on error reading from the database
     * @throws ParseException
     */
protected void writeBackupToFile(FileWriter fileWriter) throws IOException, DatabaseEnvironmentCleanupException, DbException, ParseException {
    if (disableForeignKeys) {
        fileWriter.write(disableForeignKeyChecksStart());
    }
    for (DbTable dbTable : dbTables) {
        if (log.isDebugEnabled()) {
            log.debug("Preparing data for backup of table " + (dbTable != null ? dbTable.getTableName() : null));
        }
        List<ColumnDescription> columnsToSelect = null;
        columnsToSelect = getColumnsToSelect(dbTable, dbConnection.getUser());
        if (columnsToSelect == null || columnsToSelect.size() == 0) {
            // it contains some meaningful data and so it has columns
            throw new DatabaseEnvironmentCleanupException("No columns to backup for table " + (dbTable != null ? dbTable.getTableName() : ""));
        }
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.append("SELECT ");
        selectQuery.append(getColumnsString(columnsToSelect));
        selectQuery.append(" FROM ");
        selectQuery.append(dbTable.getTableName());
        DbQuery query = new DbQuery(selectQuery.toString());
        DbRecordValuesList[] records = dbProvider.select(query, DbReturnModes.ESCAPED_STRING);
        writeTableToFile(columnsToSelect, dbTable, records, fileWriter);
    }
    if (disableForeignKeys) {
        fileWriter.write(disableForeignKeyChecksEnd());
    }
}
Also used : DatabaseEnvironmentCleanupException(com.axway.ats.environment.database.exceptions.DatabaseEnvironmentCleanupException) DbQuery(com.axway.ats.common.dbaccess.DbQuery) DbRecordValuesList(com.axway.ats.core.dbaccess.DbRecordValuesList) ColumnDescription(com.axway.ats.core.dbaccess.ColumnDescription) DbTable(com.axway.ats.environment.database.model.DbTable)

Example 12 with ColumnDescription

use of com.axway.ats.core.dbaccess.ColumnDescription in project ats-framework by Axway.

the class Test_ColumnDescription method isTypeBit.

@Test
public void isTypeBit() {
    ColumnDescription columnDescription = new ColumnDescription("name", "type");
    assertFalse(columnDescription.isTypeBit());
}
Also used : ColumnDescription(com.axway.ats.core.dbaccess.ColumnDescription) BaseTest(com.axway.ats.environment.BaseTest) Test(org.junit.Test)

Example 13 with ColumnDescription

use of com.axway.ats.core.dbaccess.ColumnDescription in project ats-framework by Axway.

the class OracleEnvironmentHandler method writeTableToFile.

@Override
protected void writeTableToFile(List<ColumnDescription> columns, DbTable table, DbRecordValuesList[] records, FileWriter fileWriter) throws IOException, ParseException {
    if (this.includeDeleteStatements) {
        fileWriter.write("DELETE FROM " + table.getTableName() + ";" + EOL_MARKER + LINE_SEPARATOR);
    }
    if (records.length > 0) {
        if (containsBinaryTypes(columns)) {
            /*
                 * If the binary value is too long the INSERT operation will fail.
                 * The workaround is to assign the binary value to a variable and then use it in the INSERT(or UPDATE) statement
                 * We have to use DECLARE/BEGIN/END anonymous block statement like this:
                 *
                   DECLARE
                      binValue_0 TABLE_NAME.BLOB_COLUMN_NAME%type;
                    BEGIN
                      binValue_0 := to_blob('3C3F786D6C2076657273696F6E3D22312E3022');
                      INSERT INTO TABLE_NAME(NAME,BLOB_COLUMN_NAME,DESCRIPTION) VALUES ('index.xhtml', binValue_0, 'Index file');
                    END; -- ATS EOL;
                 */
            final String INDENTATION = "  ";
            final String VAR_PREFIX = "binValue_";
            StringBuilder stmtBlockBuilder = new StringBuilder("DECLARE" + LINE_SEPARATOR);
            int variableIndex = 0;
            for (ColumnDescription column : columns) {
                if (column.isTypeBinary()) {
                    stmtBlockBuilder.append(INDENTATION + VAR_PREFIX + (variableIndex++) + " " + table.getTableName() + "." + column.getName() + "%type;" + LINE_SEPARATOR);
                }
            }
            stmtBlockBuilder.append("BEGIN" + LINE_SEPARATOR);
            int stmtBlockStart = stmtBlockBuilder.length();
            String insertBegin = INDENTATION + "INSERT INTO " + table.getTableName() + "(" + getColumnsString(columns) + ") VALUES (";
            String insertEnd = ");" + LINE_SEPARATOR;
            for (DbRecordValuesList record : records) {
                StringBuilder insertStatement = new StringBuilder();
                variableIndex = 0;
                for (int i = 0; i < record.size(); i++) {
                    ColumnDescription column = columns.get(i);
                    DbRecordValue recordValue = record.get(i);
                    // extract the value depending on the column type
                    String fieldValue = extractValue(column, (String) recordValue.getValue()).toString();
                    if (column.isTypeBinary()) {
                        String varName = VAR_PREFIX + (variableIndex++);
                        stmtBlockBuilder.append(INDENTATION + varName + " := " + fieldValue + ";" + LINE_SEPARATOR);
                        insertStatement.append(varName);
                    } else {
                        insertStatement.append(fieldValue);
                    }
                    insertStatement.append(",");
                }
                //remove the last comma
                insertStatement.delete(insertStatement.length() - 1, insertStatement.length());
                stmtBlockBuilder.append(insertBegin);
                stmtBlockBuilder.append(insertStatement.toString());
                stmtBlockBuilder.append(insertEnd);
                stmtBlockBuilder.append("END;" + EOL_MARKER + LINE_SEPARATOR);
                fileWriter.write(stmtBlockBuilder.toString());
                fileWriter.flush();
                // clear to block BEGIN tag
                stmtBlockBuilder.delete(stmtBlockStart, stmtBlockBuilder.length());
            }
        } else {
            StringBuilder insertStatement = new StringBuilder();
            String insertBegin = "INSERT INTO " + table.getTableName() + "(" + getColumnsString(columns) + ") VALUES (";
            String insertEnd = ");" + EOL_MARKER + LINE_SEPARATOR;
            for (DbRecordValuesList record : records) {
                insertStatement.append(insertBegin);
                for (int i = 0; i < record.size(); i++) {
                    DbRecordValue recordValue = record.get(i);
                    String fieldValue = (String) recordValue.getValue();
                    // extract specific values depending on their type
                    insertStatement.append(extractValue(columns.get(i), fieldValue));
                    insertStatement.append(",");
                }
                //remove the last comma
                insertStatement.delete(insertStatement.length() - 1, insertStatement.length());
                insertStatement.append(insertEnd);
            }
            fileWriter.write(insertStatement.toString());
        }
    }
// TODO : exclusive table locks END
}
Also used : DbRecordValuesList(com.axway.ats.core.dbaccess.DbRecordValuesList) DbRecordValue(com.axway.ats.core.dbaccess.DbRecordValue) OracleColumnDescription(com.axway.ats.core.dbaccess.OracleColumnDescription) ColumnDescription(com.axway.ats.core.dbaccess.ColumnDescription)

Example 14 with ColumnDescription

use of com.axway.ats.core.dbaccess.ColumnDescription in project ats-framework by Axway.

the class MysqlEnvironmentHandler method getColumnsToSelect.

@Override
protected List<ColumnDescription> getColumnsToSelect(DbTable table, String userName) throws DbException, ColumnHasNoDefaultValueException {
    // TODO Might be replaced with JDBC DatabaseMetaData.getColumns() but should be verified with default values
    ArrayList<ColumnDescription> columnsToSelect = new ArrayList<ColumnDescription>();
    DbRecordValuesList[] columnsMetaData = null;
    try {
        columnsMetaData = dbProvider.select("SHOW COLUMNS FROM " + table.getTableName());
    } catch (DbException e) {
        log.error("Could not get columns for table " + table.getTableName() + ". Check if the table is existing and that the user has permissions. See more details in the trace.");
        throw e;
    }
    for (DbRecordValuesList columnMetaData : columnsMetaData) {
        String columnName = (String) columnMetaData.get(MysqlColumnNames.COLUMN_NAME.getName(isJDBC4));
        //check if the column should be skipped in the backup
        if (!table.getColumnsToExclude().contains(columnName)) {
            ColumnDescription colDescription = new MysqlColumnDescription(columnName, (String) columnMetaData.get(MysqlColumnNames.COLUMN_TYPE.getName(isJDBC4)));
            columnsToSelect.add(colDescription);
        } else {
            //if this column has no default value, we cannot skip it in the backup
            if (columnMetaData.get(MysqlColumnNames.DEFAULT_COLUMN.getName(isJDBC4)) == null) {
                log.error("Cannot skip columns with no default values while creating backup");
                throw new ColumnHasNoDefaultValueException(table.getTableName(), columnName);
            }
        }
    }
    return columnsToSelect;
}
Also used : DbRecordValuesList(com.axway.ats.core.dbaccess.DbRecordValuesList) ColumnHasNoDefaultValueException(com.axway.ats.environment.database.exceptions.ColumnHasNoDefaultValueException) MysqlColumnDescription(com.axway.ats.core.dbaccess.MysqlColumnDescription) ColumnDescription(com.axway.ats.core.dbaccess.ColumnDescription) ArrayList(java.util.ArrayList) MysqlColumnDescription(com.axway.ats.core.dbaccess.MysqlColumnDescription) DbException(com.axway.ats.core.dbaccess.exceptions.DbException)

Example 15 with ColumnDescription

use of com.axway.ats.core.dbaccess.ColumnDescription in project ats-framework by Axway.

the class Test_ColumnDescription method isTypeBinaryPositive.

@Test
public void isTypeBinaryPositive() {
    List<String> binaryTypes = new ArrayList<String>();
    binaryTypes.add("blob");
    binaryTypes.add("longblob");
    binaryTypes.add("binary");
    binaryTypes.add("binary(5)");
    binaryTypes.add("varbinary");
    binaryTypes.add("varbinary(5)");
    for (String numericType : binaryTypes) {
        ColumnDescription columnDescription = new ColumnDescription("name", numericType);
        assertTrue(columnDescription.isTypeBinary());
    }
}
Also used : ColumnDescription(com.axway.ats.core.dbaccess.ColumnDescription) ArrayList(java.util.ArrayList) BaseTest(com.axway.ats.environment.BaseTest) Test(org.junit.Test)

Aggregations

ColumnDescription (com.axway.ats.core.dbaccess.ColumnDescription)17 BaseTest (com.axway.ats.environment.BaseTest)7 ArrayList (java.util.ArrayList)7 Test (org.junit.Test)7 DbRecordValuesList (com.axway.ats.core.dbaccess.DbRecordValuesList)6 DbException (com.axway.ats.core.dbaccess.exceptions.DbException)3 ColumnHasNoDefaultValueException (com.axway.ats.environment.database.exceptions.ColumnHasNoDefaultValueException)3 MssqlColumnDescription (com.axway.ats.core.dbaccess.MssqlColumnDescription)2 OracleColumnDescription (com.axway.ats.core.dbaccess.OracleColumnDescription)2 DbQuery (com.axway.ats.common.dbaccess.DbQuery)1 DbRecordValue (com.axway.ats.core.dbaccess.DbRecordValue)1 MysqlColumnDescription (com.axway.ats.core.dbaccess.MysqlColumnDescription)1 CassandraDbProvider (com.axway.ats.core.dbaccess.cassandra.CassandraDbProvider)1 DatabaseEnvironmentCleanupException (com.axway.ats.environment.database.exceptions.DatabaseEnvironmentCleanupException)1 DbTable (com.axway.ats.environment.database.model.DbTable)1 List (java.util.List)1 Map (java.util.Map)1 Entry (java.util.Map.Entry)1 Set (java.util.Set)1