Search in sources :

Example 1 with CSVReader

use of liquibase.util.csv.CSVReader in project liquibase by liquibase.

the class LoadDataChange method getCSVReader.

public CSVReader getCSVReader() throws IOException, LiquibaseException {
    ResourceAccessor resourceAccessor = Scope.getCurrentScope().getResourceAccessor();
    if (resourceAccessor == null) {
        throw new UnexpectedLiquibaseException("No file resourceAccessor specified for " + getFile());
    }
    String relativeTo = getRelativeTo();
    InputStream stream = resourceAccessor.openStream(relativeTo, file);
    if (stream == null) {
        return null;
    }
    Reader streamReader = StreamUtil.readStreamWithReader(stream, getEncoding());
    char quotchar;
    if (StringUtil.trimToEmpty(this.quotchar).isEmpty()) {
        // hope this is impossible to have a field surrounded with non ascii char 0x01
        quotchar = '\1';
    } else {
        quotchar = this.quotchar.charAt(0);
    }
    if (separator == null) {
        separator = CSVReader.DEFAULT_SEPARATOR + "";
    }
    return new CSVReader(streamReader, separator.charAt(0), quotchar);
}
Also used : ResourceAccessor(liquibase.resource.ResourceAccessor) CSVReader(liquibase.util.csv.CSVReader) EmptyLineAndCommentSkippingInputStream(liquibase.io.EmptyLineAndCommentSkippingInputStream) InputStream(java.io.InputStream) CSVReader(liquibase.util.csv.CSVReader) Reader(java.io.Reader)

Example 2 with CSVReader

use of liquibase.util.csv.CSVReader in project liquibase by liquibase.

the class LoadDataChange method generateStatements.

@Override
public SqlStatement[] generateStatements(Database database) {
    boolean databaseSupportsBatchUpdates = false;
    try {
        if (!(database instanceof MySQLDatabase)) {
            // mysql supports batch updates, but the performance vs. the big insert is worse
            databaseSupportsBatchUpdates = database.supportsBatchUpdates();
        }
    } catch (DatabaseException e) {
        throw new UnexpectedLiquibaseException(e);
    }
    CSVReader reader = null;
    try {
        reader = getCSVReader();
        if (reader == null) {
            throw new UnexpectedLiquibaseException("Unable to read file " + this.getFile());
        }
        String[] headers = reader.readNext();
        if (headers == null) {
            throw new UnexpectedLiquibaseException("Data file " + getFile() + " was empty");
        }
        // Make sure all take the column list we interpolated from the CSV headers
        addColumnsFromHeaders(headers);
        // If we have an real JDBC connection to the database, ask the database for any missing column types.
        try {
            retrieveMissingColumnLoadTypes(columns, database);
        } catch (DatabaseException e) {
            throw new UnexpectedLiquibaseException(e);
        }
        List<ExecutablePreparedStatementBase> preparedStatements = new ArrayList<>();
        boolean anyPreparedStatements = false;
        String[] line;
        // Start at '1' to take into account the header (already processed):
        int lineNumber = 1;
        boolean isCommentingEnabled = StringUtil.isNotEmpty(commentLineStartsWith);
        List<SqlStatement> statements = new ArrayList<>();
        while ((line = reader.readNext()) != null) {
            lineNumber++;
            if ((line.length == 0) || ((line.length == 1) && (StringUtil.trimToNull(line[0]) == null)) || (isCommentingEnabled && isLineCommented(line))) {
                // nothing interesting on this line
                continue;
            }
            // (Failure could indicate unquoted strings with commas, for example).
            if (line.length != headers.length) {
                throw new UnexpectedLiquibaseException("CSV file " + getFile() + " Line " + lineNumber + " has " + line.length + " values defined, Header has " + headers.length + ". Numbers MUST be equal (check for unquoted string with embedded commas)");
            }
            boolean needsPreparedStatement = false;
            List<LoadDataColumnConfig> columnsFromCsv = new ArrayList<>();
            for (int i = 0; i < headers.length; i++) {
                String value = line[i];
                String columnName = headers[i].trim();
                LoadDataColumnConfig valueConfig = new LoadDataColumnConfig();
                LoadDataColumnConfig columnConfig = getColumnConfig(i, columnName);
                if (columnConfig != null) {
                    if ("skip".equalsIgnoreCase(columnConfig.getType())) {
                        continue;
                    }
                    // don't overwrite header name unless there is actually a value to override it with
                    if (columnConfig.getName() != null) {
                        columnName = columnConfig.getName();
                    }
                    // 
                    if ("NULL".equalsIgnoreCase(value)) {
                        valueConfig.setType(columnConfig.getType());
                    }
                    valueConfig.setName(columnName);
                    valueConfig.setAllowUpdate(columnConfig.getAllowUpdate());
                    if (value.isEmpty()) {
                        value = columnConfig.getDefaultValue();
                    }
                    if (StringUtil.equalsWordNull(value)) {
                        valueConfig.setValue(null);
                    } else if (columnConfig.getType() == null) {
                        // columnConfig did not specify a type
                        valueConfig.setValue(value);
                    } else if (columnConfig.getTypeEnum() == LOAD_DATA_TYPE.UNKNOWN) {
                        // columnConfig did not match a specific type
                        valueConfig.setValue(value);
                    } else if (columnConfig.getTypeEnum() == LOAD_DATA_TYPE.BOOLEAN) {
                        if (value == null) {
                            // TODO getDefaultValueBoolean should use BooleanUtil.parseBoolean also for consistent behaviour
                            valueConfig.setValueBoolean(columnConfig.getDefaultValueBoolean());
                        } else {
                            valueConfig.setValueBoolean(BooleanUtil.parseBoolean(value));
                        }
                    } else if (columnConfig.getTypeEnum() == LOAD_DATA_TYPE.NUMERIC) {
                        if (value != null) {
                            valueConfig.setValueNumeric(value);
                        } else {
                            valueConfig.setValueNumeric(columnConfig.getDefaultValueNumeric());
                        }
                    } else if (columnConfig.getType().toLowerCase().contains("date") || columnConfig.getType().toLowerCase().contains("time")) {
                        if ("NULL".equalsIgnoreCase(value) || "".equals(value)) {
                            valueConfig.setValue(null);
                        } else {
                            try {
                                // Need the column type for handling 'NOW' or 'TODAY' type column value
                                valueConfig.setType(columnConfig.getType());
                                if (value != null) {
                                    valueConfig.setValueDate(value);
                                } else {
                                    valueConfig.setValueDate(columnConfig.getDefaultValueDate());
                                }
                            } catch (DateParseException e) {
                                throw new UnexpectedLiquibaseException(e);
                            }
                        }
                    } else if (columnConfig.getTypeEnum() == LOAD_DATA_TYPE.STRING) {
                        valueConfig.setType(columnConfig.getType());
                        valueConfig.setValue(value == null ? "" : value);
                    } else if (columnConfig.getTypeEnum() == LOAD_DATA_TYPE.COMPUTED) {
                        if (null != value) {
                            liquibase.statement.DatabaseFunction function = new liquibase.statement.DatabaseFunction(value);
                            valueConfig.setValueComputed(function);
                        } else {
                            valueConfig.setValueComputed(columnConfig.getDefaultValueComputed());
                        }
                    } else if (columnConfig.getTypeEnum() == LOAD_DATA_TYPE.SEQUENCE) {
                        if (value == null) {
                            throw new UnexpectedLiquibaseException("Must set a sequence name in the loadData column defaultValue attribute");
                        }
                        liquibase.statement.SequenceNextValueFunction function = new liquibase.statement.SequenceNextValueFunction(getSchemaName(), value);
                        valueConfig.setValueComputed(function);
                    } else if (columnConfig.getType().equalsIgnoreCase(LOAD_DATA_TYPE.BLOB.toString())) {
                        if ("NULL".equalsIgnoreCase(value)) {
                            valueConfig.setValue(null);
                        } else if (BASE64_PATTERN.matcher(value).matches()) {
                            valueConfig.setType(columnConfig.getType());
                            valueConfig.setValue(value);
                            needsPreparedStatement = true;
                        } else {
                            valueConfig.setValueBlobFile(value);
                            needsPreparedStatement = true;
                        }
                    } else if (columnConfig.getTypeEnum() == LOAD_DATA_TYPE.CLOB) {
                        valueConfig.setValueClobFile(value);
                        needsPreparedStatement = true;
                    } else if (columnConfig.getTypeEnum() == LOAD_DATA_TYPE.UUID) {
                        valueConfig.setType(columnConfig.getType());
                        if ("NULL".equalsIgnoreCase(value)) {
                            valueConfig.setValue(null);
                        } else {
                            valueConfig.setValue(value);
                        }
                    } else if (columnConfig.getType().equalsIgnoreCase(LOAD_DATA_TYPE.OTHER.toString())) {
                        valueConfig.setType(columnConfig.getType());
                        if ("NULL".equalsIgnoreCase(value)) {
                            valueConfig.setValue(null);
                        } else {
                            valueConfig.setValue(value);
                        }
                    } else {
                        throw new UnexpectedLiquibaseException(String.format(coreBundle.getString("loaddata.type.is.not.supported"), columnConfig.getType()));
                    }
                } else {
                    // No columnConfig found. Assume header column name to be the table column name.
                    if (columnName.contains("(") || (columnName.contains(")") && (database instanceof AbstractJdbcDatabase))) {
                        columnName = ((AbstractJdbcDatabase) database).quoteObject(columnName, Column.class);
                    }
                    valueConfig.setName(columnName);
                    valueConfig.setValue(getValueToWrite(value));
                }
                columnsFromCsv.add(valueConfig);
            }
            // end of: iterate through all the columns of a CSV line
            // Try to use prepared statements if any of the following conditions apply:
            // 1. There is no other option than using a prepared statement (e.g. in cases of LOBs) regardless
            // of whether the 'usePreparedStatement' is set to false
            // 2. The database supports batched statements (for improved performance) AND we are not in an
            // "SQL" mode (i.e. we generate an SQL file instead of actually modifying the database).
            // BUT: if the user specifically requests usePreparedStatement=false, then respect that
            boolean actuallyUsePreparedStatements = false;
            if (hasPreparedStatementsImplemented()) {
                if (usePreparedStatements != null) {
                    if (!usePreparedStatements && needsPreparedStatement) {
                        throw new UnexpectedLiquibaseException("loadData is requesting usePreparedStatements=false but prepared statements are required");
                    }
                    actuallyUsePreparedStatements = usePreparedStatements;
                } else {
                    actuallyUsePreparedStatements = needsPreparedStatement || (databaseSupportsBatchUpdates && !isLoggingExecutor(database));
                }
            }
            if (actuallyUsePreparedStatements) {
                anyPreparedStatements = true;
                ExecutablePreparedStatementBase stmt = this.createPreparedStatement(database, getCatalogName(), getSchemaName(), getTableName(), columnsFromCsv, getChangeSet(), Scope.getCurrentScope().getResourceAccessor());
                preparedStatements.add(stmt);
            } else {
                InsertStatement insertStatement = this.createStatement(getCatalogName(), getSchemaName(), getTableName());
                for (LoadDataColumnConfig column : columnsFromCsv) {
                    String columnName = column.getName();
                    Object value = column.getValueObject();
                    if (value == null) {
                        value = "NULL";
                    }
                    insertStatement.addColumnValue(columnName, value);
                    if (insertStatement instanceof InsertOrUpdateStatement) {
                        ((InsertOrUpdateStatement) insertStatement).setAllowColumnUpdate(columnName, column.getAllowUpdate() == null || column.getAllowUpdate());
                    }
                }
                statements.add(insertStatement);
            }
        // end of: will we use a PreparedStatement?
        }
        if (anyPreparedStatements) {
            // If we have only prepared statements and the database supports batching, let's roll
            if (databaseSupportsBatchUpdates && statements.isEmpty() && (!preparedStatements.isEmpty())) {
                if (database instanceof PostgresDatabase) {
                    // we don't do batch updates for Postgres but we still send as a prepared statement, see LB-744
                    return preparedStatements.toArray(new SqlStatement[preparedStatements.size()]);
                } else {
                    return new SqlStatement[] { new BatchDmlExecutablePreparedStatement(database, getCatalogName(), getSchemaName(), getTableName(), columns, getChangeSet(), Scope.getCurrentScope().getResourceAccessor(), preparedStatements) };
                }
            } else {
                return statements.toArray(new SqlStatement[statements.size()]);
            }
        } else {
            if (statements.isEmpty()) {
                // avoid returning unnecessary dummy statement
                return new SqlStatement[0];
            }
            InsertSetStatement statementSet = this.createStatementSet(getCatalogName(), getSchemaName(), getTableName());
            for (SqlStatement stmt : statements) {
                statementSet.addInsertStatement((InsertStatement) stmt);
            }
            if ((database instanceof MSSQLDatabase) || (database instanceof MySQLDatabase) || (database instanceof PostgresDatabase)) {
                List<InsertStatement> innerStatements = statementSet.getStatements();
                if ((innerStatements != null) && (!innerStatements.isEmpty()) && (innerStatements.get(0) instanceof InsertOrUpdateStatement)) {
                    // cannot do insert or update in a single statement
                    return statementSet.getStatementsArray();
                }
                // should the need arise, on generation.
                return new SqlStatement[] { statementSet };
            } else {
                return statementSet.getStatementsArray();
            }
        }
    } catch (CsvMalformedLineException e) {
        throw new RuntimeException("Error parsing " + getRelativeTo() + " on line " + e.getLineNumber() + ": " + e.getMessage());
    } catch (IOException | LiquibaseException e) {
        throw new RuntimeException(e);
    } catch (UnexpectedLiquibaseException ule) {
        if ((getChangeSet() != null) && (getChangeSet().getFailOnError() != null) && !getChangeSet().getFailOnError()) {
            LOG.info("Change set " + getChangeSet().toString(false) + " failed, but failOnError was false.  Error: " + ule.getMessage());
            return new SqlStatement[0];
        } else {
            throw ule;
        }
    } finally {
        if (null != reader) {
            try {
                reader.close();
            } catch (Exception e) {
            // Do nothing
            }
        }
    }
}
Also used : InsertOrUpdateStatement(liquibase.statement.core.InsertOrUpdateStatement) CsvMalformedLineException(com.opencsv.exceptions.CsvMalformedLineException) InsertStatement(liquibase.statement.core.InsertStatement) SqlStatement(liquibase.statement.SqlStatement) BatchDmlExecutablePreparedStatement(liquibase.statement.BatchDmlExecutablePreparedStatement) Column(liquibase.structure.core.Column) MSSQLDatabase(liquibase.database.core.MSSQLDatabase) CSVReader(liquibase.util.csv.CSVReader) MySQLDatabase(liquibase.database.core.MySQLDatabase) IOException(java.io.IOException) AbstractJdbcDatabase(liquibase.database.AbstractJdbcDatabase) CsvMalformedLineException(com.opencsv.exceptions.CsvMalformedLineException) InvalidExampleException(liquibase.snapshot.InvalidExampleException) IOException(java.io.IOException) ExecutablePreparedStatementBase(liquibase.statement.ExecutablePreparedStatementBase) PostgresDatabase(liquibase.database.core.PostgresDatabase) InsertSetStatement(liquibase.statement.core.InsertSetStatement)

Example 3 with CSVReader

use of liquibase.util.csv.CSVReader in project liquibase by liquibase.

the class OfflineChangeLogHistoryService method appendChangeSet.

protected void appendChangeSet(ChangeSet changeSet, ChangeSet.ExecType execType) throws DatabaseException {
    File oldFile = this.changeLogFile;
    File newFile = new File(oldFile.getParentFile(), oldFile.getName() + ".new");
    try (Reader reader = new InputStreamReader(new FileInputStream(oldFile), GlobalConfiguration.OUTPUT_FILE_ENCODING.getCurrentValue());
        Writer writer = new OutputStreamWriter(new FileOutputStream(newFile), GlobalConfiguration.OUTPUT_FILE_ENCODING.getCurrentValue());
        CSVReader csvReader = new CSVReader(reader);
        CSVWriter csvWriter = new CSVWriter(writer)) {
        String[] line;
        while ((line = csvReader.readNext()) != null) {
            csvWriter.writeNext(line);
        }
        String tag = "";
        for (Change change : changeSet.getChanges()) {
            if (change instanceof TagDatabaseChange) {
                TagDatabaseChange tagChange = (TagDatabaseChange) change;
                tag = tagChange.getTag();
            }
        }
        String[] newLine = new String[Columns.values().length];
        newLine[Columns.ID.ordinal()] = changeSet.getId();
        newLine[Columns.AUTHOR.ordinal()] = changeSet.getAuthor();
        newLine[Columns.FILENAME.ordinal()] = changeSet.getFilePath();
        newLine[Columns.DATEEXECUTED.ordinal()] = new ISODateFormat().format(new java.sql.Timestamp(new Date().getTime()));
        newLine[Columns.ORDEREXECUTED.ordinal()] = String.valueOf(getNextSequenceValue());
        newLine[Columns.EXECTYPE.ordinal()] = execType.value;
        newLine[Columns.MD5SUM.ordinal()] = changeSet.generateCheckSum().toString();
        newLine[Columns.DESCRIPTION.ordinal()] = changeSet.getDescription();
        newLine[Columns.COMMENTS.ordinal()] = changeSet.getComments();
        newLine[Columns.TAG.ordinal()] = tag;
        newLine[Columns.LIQUIBASE.ordinal()] = LiquibaseUtil.getBuildVersion();
        newLine[Columns.CONTEXTS.ordinal()] = (changeSet.getContexts() == null) ? null : changeSet.getContexts().toString();
        newLine[Columns.LABELS.ordinal()] = (changeSet.getLabels() == null) ? null : changeSet.getLabels().toString();
        newLine[Columns.DEPLOYMENT_ID.ordinal()] = getDeploymentId();
        csvWriter.writeNext(newLine);
    } catch (Exception e) {
        throw new DatabaseException(e);
    }
    oldFile.delete();
    newFile.renameTo(oldFile);
}
Also used : CSVReader(liquibase.util.csv.CSVReader) TagDatabaseChange(liquibase.change.core.TagDatabaseChange) CSVReader(liquibase.util.csv.CSVReader) CSVWriter(liquibase.util.csv.CSVWriter) Change(liquibase.change.Change) TagDatabaseChange(liquibase.change.core.TagDatabaseChange) Date(java.util.Date) UnexpectedLiquibaseException(liquibase.exception.UnexpectedLiquibaseException) DatabaseException(liquibase.exception.DatabaseException) LiquibaseException(liquibase.exception.LiquibaseException) ISODateFormat(liquibase.util.ISODateFormat) DatabaseException(liquibase.exception.DatabaseException) CSVWriter(liquibase.util.csv.CSVWriter)

Example 4 with CSVReader

use of liquibase.util.csv.CSVReader in project liquibase by liquibase.

the class OfflineChangeLogHistoryService method getNextSequenceValue.

@Override
public int getNextSequenceValue() throws LiquibaseException {
    if (lastChangeSetSequenceValue == null) {
        lastChangeSetSequenceValue = 0;
        try (Reader reader = new InputStreamReader(new FileInputStream(this.changeLogFile), GlobalConfiguration.OUTPUT_FILE_ENCODING.getCurrentValue())) {
            CSVReader csvReader = new CSVReader(reader);
            // skip header line
            String[] line = csvReader.readNext();
            List<RanChangeSet> returnList = new ArrayList<>();
            while ((line = csvReader.readNext()) != null) {
                try {
                    lastChangeSetSequenceValue = Integer.valueOf(line[Columns.ORDEREXECUTED.ordinal()]);
                } catch (NumberFormatException ignore) {
                // ignore.
                }
            }
        } catch (Exception ignore) {
        // ignore
        }
    }
    return ++lastChangeSetSequenceValue;
}
Also used : CSVReader(liquibase.util.csv.CSVReader) ArrayList(java.util.ArrayList) CSVReader(liquibase.util.csv.CSVReader) UnexpectedLiquibaseException(liquibase.exception.UnexpectedLiquibaseException) DatabaseException(liquibase.exception.DatabaseException) LiquibaseException(liquibase.exception.LiquibaseException)

Example 5 with CSVReader

use of liquibase.util.csv.CSVReader in project liquibase by liquibase.

the class OfflineChangeLogHistoryService method replaceChangeSet.

protected void replaceChangeSet(ChangeSet changeSet, ReplaceChangeSetLogic replaceLogic) throws DatabaseException {
    File oldFile = this.changeLogFile;
    File newFile = new File(oldFile.getParentFile(), oldFile.getName() + ".new");
    try (Reader reader = new InputStreamReader(new FileInputStream(oldFile), GlobalConfiguration.OUTPUT_FILE_ENCODING.getCurrentValue());
        Writer writer = new OutputStreamWriter(new FileOutputStream(newFile), GlobalConfiguration.OUTPUT_FILE_ENCODING.getCurrentValue());
        CSVReader csvReader = new CSVReader(reader);
        CSVWriter csvWriter = new CSVWriter(writer)) {
        String[] line;
        while ((line = csvReader.readNext()) != null) {
            if ((changeSet == null) || (line[Columns.ID.ordinal()].equals(changeSet.getId()) && line[Columns.AUTHOR.ordinal()].equals(changeSet.getAuthor()) && line[Columns.FILENAME.ordinal()].equals(changeSet.getFilePath()))) {
                line = replaceLogic.execute(line);
            }
            if (line != null) {
                csvWriter.writeNext(line);
            }
        }
    } catch (Exception e) {
        throw new DatabaseException(e);
    }
    oldFile.delete();
    newFile.renameTo(oldFile);
}
Also used : CSVReader(liquibase.util.csv.CSVReader) CSVReader(liquibase.util.csv.CSVReader) CSVWriter(liquibase.util.csv.CSVWriter) UnexpectedLiquibaseException(liquibase.exception.UnexpectedLiquibaseException) DatabaseException(liquibase.exception.DatabaseException) LiquibaseException(liquibase.exception.LiquibaseException) DatabaseException(liquibase.exception.DatabaseException) CSVWriter(liquibase.util.csv.CSVWriter)

Aggregations

CSVReader (liquibase.util.csv.CSVReader)6 DatabaseException (liquibase.exception.DatabaseException)4 LiquibaseException (liquibase.exception.LiquibaseException)4 UnexpectedLiquibaseException (liquibase.exception.UnexpectedLiquibaseException)4 ArrayList (java.util.ArrayList)2 ISODateFormat (liquibase.util.ISODateFormat)2 CSVWriter (liquibase.util.csv.CSVWriter)2 CsvMalformedLineException (com.opencsv.exceptions.CsvMalformedLineException)1 IOException (java.io.IOException)1 InputStream (java.io.InputStream)1 Reader (java.io.Reader)1 Date (java.util.Date)1 ContextExpression (liquibase.ContextExpression)1 Labels (liquibase.Labels)1 Change (liquibase.change.Change)1 TagDatabaseChange (liquibase.change.core.TagDatabaseChange)1 AbstractJdbcDatabase (liquibase.database.AbstractJdbcDatabase)1 MSSQLDatabase (liquibase.database.core.MSSQLDatabase)1 MySQLDatabase (liquibase.database.core.MySQLDatabase)1 PostgresDatabase (liquibase.database.core.PostgresDatabase)1