001 package liquibase.change.core;
002
003 import java.io.IOException;
004 import java.io.InputStream;
005 import java.io.InputStreamReader;
006 import java.util.ArrayList;
007 import java.util.List;
008
009 import liquibase.change.AbstractChange;
010 import liquibase.change.ChangeMetaData;
011 import liquibase.change.ChangeWithColumns;
012 import liquibase.change.CheckSum;
013 import liquibase.change.ColumnConfig;
014 import liquibase.database.Database;
015 import liquibase.exception.UnexpectedLiquibaseException;
016 import liquibase.resource.ResourceAccessor;
017 import liquibase.statement.SqlStatement;
018 import liquibase.statement.core.InsertStatement;
019 import liquibase.util.StringUtils;
020 import liquibase.util.csv.CSVReader;
021
022 public class LoadDataChange extends AbstractChange implements ChangeWithColumns {
023
024 private String schemaName;
025 private String tableName;
026 private String file;
027 private String encoding = null;
028 private String separator = liquibase.util.csv.opencsv.CSVReader.DEFAULT_SEPARATOR + "";
029 private String quotchar = liquibase.util.csv.opencsv.CSVReader.DEFAULT_QUOTE_CHARACTER + "";
030
031 private List<LoadDataColumnConfig> columns = new ArrayList<LoadDataColumnConfig>();
032
033 public LoadDataChange() {
034 super("loadData", "Load Data", ChangeMetaData.PRIORITY_DEFAULT);
035 }
036
037 protected LoadDataChange(String changeName, String changeDescription) {
038 super(changeName, changeDescription, ChangeMetaData.PRIORITY_DEFAULT);
039 }
040
041 public String getSchemaName() {
042 return schemaName;
043 }
044
045 public void setSchemaName(String schemaName) {
046 this.schemaName = StringUtils.trimToNull(schemaName);
047 }
048
049 public String getTableName() {
050 return tableName;
051 }
052
053 public void setTableName(String tableName) {
054 this.tableName = tableName;
055 }
056
057 public String getFile() {
058 return file;
059 }
060
061 public void setFile(String file) {
062 this.file = file;
063 }
064
065 public String getEncoding() {
066 return encoding;
067 }
068
069 public void setEncoding(String encoding) {
070 this.encoding = encoding;
071 }
072
073 public String getSeparator() {
074 return separator;
075 }
076
077 public void setSeparator(String separator) {
078 this.separator = separator;
079 }
080
081 public String getQuotchar() {
082 return quotchar;
083 }
084
085 public void setQuotchar(String quotchar) {
086 this.quotchar = quotchar;
087 }
088
089 @Override
090 public void addColumn(ColumnConfig column) {
091 columns.add((LoadDataColumnConfig) column);
092 }
093
094 @Override
095 public List<ColumnConfig> getColumns() {
096 return (List<ColumnConfig>) (List) columns;
097 }
098
099 @Override
100 public SqlStatement[] generateStatements(Database database) {
101 CSVReader reader = null;
102 try {
103 reader = getCSVReader();
104
105 String[] headers = reader.readNext();
106 if (headers == null) {
107 throw new UnexpectedLiquibaseException("Data file " + getFile() + " was empty");
108 }
109
110 List<SqlStatement> statements = new ArrayList<SqlStatement>();
111 String[] line = null;
112 int lineNumber = 0;
113
114 while ((line = reader.readNext()) != null) {
115 lineNumber++;
116
117 if (line.length == 0 || (line.length == 1 && StringUtils.trimToNull(line[0]) == null)) {
118 continue; // nothing on this line
119 }
120 InsertStatement insertStatement = this.createStatement(getSchemaName(), getTableName());
121 for (int i = 0; i < headers.length; i++) {
122 String columnName = null;
123 if (i >= line.length) {
124 throw new UnexpectedLiquibaseException("CSV Line " + lineNumber + " has only " + (i - 1)
125 + " columns, the header has " + headers.length);
126 }
127
128 Object value = line[i];
129
130 ColumnConfig columnConfig = getColumnConfig(i, headers[i]);
131 if (columnConfig != null) {
132 columnName = columnConfig.getName();
133
134 if (value.toString().equalsIgnoreCase("NULL")) {
135 value = "NULL";
136 } else if (columnConfig.getType() != null) {
137 ColumnConfig valueConfig = new ColumnConfig();
138 if (columnConfig.getType().equalsIgnoreCase("BOOLEAN")) {
139 valueConfig.setValueBoolean(Boolean.parseBoolean(value.toString().toLowerCase()));
140 } else if (columnConfig.getType().equalsIgnoreCase("NUMERIC")) {
141 valueConfig.setValueNumeric(value.toString());
142 } else if (columnConfig.getType().toLowerCase().contains("date")
143 || columnConfig.getType().toLowerCase().contains("time")) {
144 valueConfig.setValueDate(value.toString());
145 } else if (columnConfig.getType().equalsIgnoreCase("STRING")) {
146 valueConfig.setValue(value.toString());
147 } else if (columnConfig.getType().equalsIgnoreCase("COMPUTED")) {
148 valueConfig.setValue(value.toString());
149 } else {
150 throw new UnexpectedLiquibaseException("loadData type of " + columnConfig.getType()
151 + " is not supported. Please use BOOLEAN, NUMERIC, DATE, STRING, or COMPUTED");
152 }
153 value = valueConfig.getValueObject();
154 }
155 }
156
157 if (columnName == null) {
158 columnName = headers[i];
159 }
160
161 insertStatement.addColumnValue(columnName, value);
162 }
163 statements.add(insertStatement);
164 }
165
166 return statements.toArray(new SqlStatement[statements.size()]);
167 } catch (IOException e) {
168 throw new RuntimeException(e);
169 } finally {
170 if (null != reader) {
171 try {
172 reader.close();
173 } catch (IOException e) {
174 ;
175 }
176 }
177 }
178 }
179
180 protected CSVReader getCSVReader() throws IOException {
181 ResourceAccessor opener = getResourceAccessor();
182 if (opener == null) {
183 throw new UnexpectedLiquibaseException("No file opener specified for " + getFile());
184 }
185 InputStream stream = opener.getResourceAsStream(getFile());
186 if (stream == null) {
187 throw new UnexpectedLiquibaseException("Data file " + getFile() + " was not found");
188 }
189
190 InputStreamReader streamReader;
191 if (getEncoding() == null) {
192 streamReader = new InputStreamReader(stream);
193 } else {
194 streamReader = new InputStreamReader(stream, getEncoding());
195 }
196
197 char quotchar;
198 if (0 == this.quotchar.length()) {
199 // hope this is impossible to have a field surrounded with non ascii char 0x01
200 quotchar = '\1';
201 } else {
202 quotchar = this.quotchar.charAt(0);
203 }
204
205 CSVReader reader = new CSVReader(streamReader, separator.charAt(0), quotchar);
206
207 return reader;
208 }
209
210 protected InsertStatement createStatement(String schemaName, String tableName) {
211 return new InsertStatement(schemaName, tableName);
212 }
213
214 protected ColumnConfig getColumnConfig(int index, String header) {
215 for (LoadDataColumnConfig config : columns) {
216 if (config.getIndex() != null && config.getIndex().equals(index)) {
217 return config;
218 }
219 if (config.getHeader() != null && config.getHeader().equalsIgnoreCase(header)) {
220 return config;
221 }
222
223 if (config.getName() != null && config.getName().equalsIgnoreCase(header)) {
224 return config;
225 }
226 }
227 return null;
228 }
229
230 @Override
231 public String getConfirmationMessage() {
232 return "Data loaded from " + getFile() + " into " + getTableName();
233 }
234
235 @Override
236 public CheckSum generateCheckSum() {
237 InputStream stream = null;
238 try {
239 stream = getResourceAccessor().getResourceAsStream(getFile());
240 if (stream == null) {
241 throw new RuntimeException(getFile() + " could not be found");
242 }
243 return CheckSum.compute(stream);
244 } catch (IOException e) {
245 throw new RuntimeException(e);
246 } finally {
247 if (stream != null) {
248 try {
249 stream.close();
250 } catch (IOException e) {
251 ;
252 }
253 }
254 }
255 }
256 }