Examples with DbRecordValue com.axway.ats.core.dbaccess.DbRecordValue used on opensource projects

Example 6 with DbRecordValue

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

the class Test_MysqlEnvironmentHandler method createBackupNegativeSkippedColumnIsNotNullable.

@Test(expected = ColumnHasNoDefaultValueException.class)
public void createBackupNegativeSkippedColumnIsNotNullable() throws DatabaseEnvironmentCleanupException, DbException, IOException, ParseException, SQLException {
    //the columns meta data
    DbRecordValuesList column1MetaData = new DbRecordValuesList();
    column1MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name1"));
    column1MetaData.add(new DbRecordValue("", "COLUMN_TYPE", "varchar(32)"));
    column1MetaData.add(new DbRecordValue("", "COLUMN_DEFAULT", true));
    DbRecordValuesList column2MetaData = new DbRecordValuesList();
    column2MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name2"));
    column2MetaData.add(new DbRecordValue("", "COLUMN_TYPE", "varchar(32)"));
    column2MetaData.add(new DbRecordValue("", "COLUMN_DEFAULT", null));
    DbRecordValuesList column3MetaData = new DbRecordValuesList();
    column3MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name3"));
    column3MetaData.add(new DbRecordValue("", "COLUMN_TYPE", "bit(1)"));
    column3MetaData.add(new DbRecordValue("", "COLUMN_DEFAULT", 1));
    DbRecordValuesList[] columnsMetaData = new DbRecordValuesList[] { column1MetaData, column2MetaData, column3MetaData };
    DbRecordValuesList record1Value = new DbRecordValuesList();
    record1Value.add(new DbRecordValue("table1", "name1", "value1"));
    record1Value.add(new DbRecordValue("table1", "name2", null));
    record1Value.add(new DbRecordValue("table1", "name3", "1"));
    DbRecordValuesList[] recordValues = new DbRecordValuesList[] { record1Value };
    expect(mockDbProvider.getConnection()).andReturn(mockConnection);
    expect(mockConnection.getMetaData()).andReturn(metaData);
    expect(mockDbConnection.getUser()).andReturn("myUserName").atLeastOnce();
    expect(metaData.getDriverMajorVersion()).andReturn(5);
    expect(metaData.getDriverMinorVersion()).andReturn(1);
    expect(mockDbProvider.select(isA(String.class))).andReturn(columnsMetaData);
    expect(mockDbProvider.select(isA(DbQuery.class), eq(DbReturnModes.ESCAPED_STRING))).andReturn(recordValues);
    expect(mockDbProvider.select(isA(String.class))).andReturn(columnsMetaData);
    expect(mockDbProvider.select(isA(DbQuery.class), eq(DbReturnModes.ESCAPED_STRING))).andReturn(recordValues);
    mockFileWriter.write("SET FOREIGN_KEY_CHECKS = 0;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write("LOCK TABLES `table1` WRITE;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write("DELETE FROM `table1`;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write("INSERT INTO `table1` (name1,name2,name3) VALUES('value1',NULL,0x1);" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.flush();
    mockFileWriter.write("UNLOCK TABLES;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write(LINE_SEPARATOR);
    replayAll();
    DbTable table1 = new DbTable("table1");
    List<String> columnsToSkip = new ArrayList<String>();
    columnsToSkip.add("name2");
    DbTable table2 = new DbTable("table2", columnsToSkip);
    MysqlEnvironmentHandler envHandler = new MysqlEnvironmentHandler(mockDbConnection, mockDbProvider);
    envHandler.addTable(table1);
    envHandler.addTable(table2);
    envHandler.writeBackupToFile(mockFileWriter);
    verifyAll();
}

Example 7 with DbRecordValue

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

the class Test_OracleEnvironmentHandler method createBackupSkipColumnsPositive.

@Test
public void createBackupSkipColumnsPositive() throws DatabaseEnvironmentCleanupException, DbException, IOException, ParseException {
    DbTable table1 = new DbTable("table1");
    List<String> columnsToSkip = new ArrayList<String>();
    columnsToSkip.add("name2");
    DbTable table2 = new DbTable("table2", columnsToSkip);
    //the columns meta data
    DbRecordValuesList column1MetaData = new DbRecordValuesList();
    column1MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name1"));
    column1MetaData.add(new DbRecordValue("", "DATA_TYPE", "varchar(32)"));
    column1MetaData.add(new DbRecordValue("", "DATA_DEFAULT", true));
    DbRecordValuesList column2MetaData = new DbRecordValuesList();
    column2MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name2"));
    column2MetaData.add(new DbRecordValue("", "DATA_TYPE", "varchar(32)"));
    column2MetaData.add(new DbRecordValue("", "DATA_DEFAULT", true));
    DbRecordValuesList column3MetaData = new DbRecordValuesList();
    column3MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name3"));
    column3MetaData.add(new DbRecordValue("", "DATA_TYPE", "bit"));
    column3MetaData.add(new DbRecordValue("", "DATA_DEFAULT", true));
    DbRecordValuesList[] columnsMetaData = new DbRecordValuesList[] { column1MetaData, column2MetaData, column3MetaData };
    //to be returned for the first table
    DbRecordValuesList recordValue1 = new DbRecordValuesList();
    recordValue1.add(new DbRecordValue("table1", "name1", "value1"));
    recordValue1.add(new DbRecordValue("table1", "name2", null));
    recordValue1.add(new DbRecordValue("table1", "name3", new String(new char[] { 5 })));
    DbRecordValuesList[] recordValues1 = new DbRecordValuesList[] { recordValue1 };
    //to be returned for the seconds table - one of the columns should be skipped
    DbRecordValuesList recordValue2 = new DbRecordValuesList();
    recordValue2.add(new DbRecordValue("table1", "name1", "value1"));
    recordValue2.add(new DbRecordValue("table1", "name3", new String(new char[] { 5 })));
    DbRecordValuesList[] recordValues2 = new DbRecordValuesList[] { recordValue2 };
    OracleEnvironmentHandler envHandler = new OracleEnvironmentHandler(mockDbConnection, mockDbProvider);
    envHandler.addTable(table1);
    envHandler.addTable(table2);
    expect(mockDbConnection.getUser()).andReturn("myUserName").atLeastOnce();
    expect(mockDbProvider.select(isA(String.class))).andReturn(columnsMetaData);
    expect(mockDbProvider.select(isA(DbQuery.class), eq(DbReturnModes.ESCAPED_STRING))).andReturn(recordValues1);
    expect(mockDbProvider.select(isA(String.class))).andReturn(columnsMetaData);
    expect(mockDbProvider.select(isA(DbQuery.class), eq(DbReturnModes.ESCAPED_STRING))).andReturn(recordValues2);
    //expect the file writer calls
    //foreign keys check start
    mockFileWriter.write("SET CONSTRAINTS ALL DEFERRED;" + EOL_MARKER + LINE_SEPARATOR);
    //table1
    mockFileWriter.write("DELETE FROM table1;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write("INSERT INTO table1(name1,name2,name3) VALUES ('value1',NULL,'" + new String(new char[] { 5 }) + "');" + EOL_MARKER + LINE_SEPARATOR);
    //table2
    mockFileWriter.write("DELETE FROM table2;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write("INSERT INTO table2(name1,name3) VALUES ('value1','" + new String(new char[] { 5 }) + "');" + EOL_MARKER + LINE_SEPARATOR);
    //foreign keys check end
    mockFileWriter.write("");
    replay(mockDbConnection);
    replay(mockDbProvider);
    replay(mockFileWriter);
    envHandler.writeBackupToFile(mockFileWriter);
    verify(mockDbConnection);
    verify(mockDbProvider);
    verify(mockFileWriter);
}

Example 8 with DbRecordValue

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

the class DatabaseOperations method getDatabaseData.

/**
     * Gets some data from a database table. </br>
     * <em>Note</em> that client may need to inspect returned type of each value and
     * optionally convert it to get value in specific format.
     *
     * @param sqlQuery the SQL SELECT query to run. Client is resposible for any
     *      possible escaping needed so this is not security-safe method
     * @return the found database data
     */
@PublicAtsApi
public DatabaseRow[] getDatabaseData(String sqlQuery) {
    List<DatabaseRow> dbRows = new ArrayList<DatabaseRow>();
    try {
        log.debug("Executing query: " + sqlQuery);
        DbRecordValuesList[] rsList = dbProvider.select(sqlQuery);
        if (rsList != null) {
            for (DbRecordValuesList rs : rsList) {
                Iterator<DbRecordValue> it = rs.iterator();
                if (it.hasNext()) {
                    DatabaseRow dbRow = new DatabaseRow();
                    while (it.hasNext()) {
                        DbRecordValue dbRecordValue = it.next();
                        dbRow.addCell(new DatabaseCell(dbRecordValue.getDbColumn().getColumnName(), dbRecordValue.getValueAsString()));
                    }
                    dbRows.add(dbRow);
                }
            }
        }
        return dbRows.toArray(new DatabaseRow[dbRows.size()]);
    } catch (DbException e) {
        throw new DatabaseOperationsException("Error getting data from DB with query '" + sqlQuery + "'", e);
    }
}

Example 9 with DbRecordValue

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

the class MssqlDbProvider method extractTableIndexes.

@Override
protected Map<String, String> extractTableIndexes(String tableName, DatabaseMetaData databaseMetaData, String catalog) throws DbException {
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT");
    sql.append(" indexes.name as Name,");
    sql.append(" indexes.type_desc as Type,");
    sql.append(" ds.name as DataSpaceName,");
    sql.append(" ds.type as DataSpaceType,");
    sql.append(" indexes.is_primary_key as IsPrimaryKey,");
    sql.append(" indexes.is_unique as IsUnique,");
    sql.append(" indexes.ignore_dup_key as IsDuplicateKey,");
    sql.append(" indexes.is_unique_constraint as IsUniqueConstraint");
    sql.append(" FROM");
    sql.append(" sys.indexes as indexes");
    sql.append(" JOIN");
    sql.append(" sys.data_spaces ds on ds.data_space_id = indexes.data_space_id");
    sql.append(" WHERE object_id = (select object_id from sys.objects where name = '" + tableName + "');");
    String indexName = null;
    Map<String, String> indexes = new HashMap<>();
    for (DbRecordValuesList valueList : select(sql.toString())) {
        StringBuilder info = new StringBuilder();
        for (DbRecordValue dbValue : valueList) {
            String value = dbValue.getValueAsString();
            String name = dbValue.getDbColumn().getColumnName();
            if ("Name".equalsIgnoreCase(name)) {
                indexName = value;
            } else {
                info.append(", " + name + "=" + value);
            }
        }
        if (indexName == null) {
            indexName = "NULL_NAME_FOUND_FOR_INDEX_OF_TABLE_" + tableName;
            log.warn("IndexName column not found in query polling for index properties:\nQuery: " + sql.toString() + "\nQuery result: " + valueList.toString() + "\nWe will use the following as an index name: " + indexName);
        }
        indexes.put(indexName, info.toString());
    }
    return indexes;
}

Example 10 with DbRecordValue

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

the class OracleDbProvider method extractTableIndexes.

@Override
protected Map<String, String> extractTableIndexes(String tableName, DatabaseMetaData databaseMetaData, String catalog) throws DbException {
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT");
    sql.append(" ais.INDEX_NAME,");
    sql.append(" ais.PARTITION_NAME,");
    sql.append(" ais.PARTITION_POSITION,");
    sql.append(" ais.OBJECT_TYPE");
    sql.append(" FROM");
    sql.append(" ALL_IND_STATISTICS ais");
    sql.append(" WHERE TABLE_OWNER='" + dbConnection.getUser() + "'");
    sql.append(" AND TABLE_NAME='" + tableName + "'");
    // skip system indexes
    sql.append(" AND ais.INDEX_NAME NOT LIKE 'SYS_%'");
    String indexName = null;
    Map<String, String> indexes = new HashMap<>();
    for (DbRecordValuesList valueList : select(sql.toString())) {
        StringBuilder info = new StringBuilder();
        for (DbRecordValue dbValue : valueList) {
            String value = dbValue.getValueAsString();
            String name = dbValue.getDbColumn().getColumnName();
            if ("INDEX_NAME".equalsIgnoreCase(name)) {
                indexName = value;
            } else {
                info.append(", " + name + "=" + value);
            }
        }
        if (indexName == null) {
            indexName = "NULL_NAME_FOUND_FOR_INDEX_OF_TABLE_" + tableName;
            log.warn("IndexName column not found in query polling for index properties:\nQuery: " + sql.toString() + "\nQuery result: " + valueList.toString() + "\nWe will use the following as an index name: " + indexName);
        }
        indexes.put(indexName, info.toString());
    }
    return indexes;
}