001: /**
002: Copyright (C) 2002-2003 Together
003:
004: This library is free software; you can redistribute it and/or
005: modify it under the terms of the GNU Lesser General Public
006: License as published by the Free Software Foundation; either
007: version 2.1 of the License, or (at your option) any later version.
008:
009: This library is distributed in the hope that it will be useful,
010: but WITHOUT ANY WARRANTY; without even the implied warranty of
011: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
012: Lesser General Public License for more details.
013:
014: You should have received a copy of the GNU Lesser General Public
015: License along with this library; if not, write to the Free Software
016: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
017:
018: */package org.relique.jdbc.csv;
019:
020: import java.io.File;
021: import java.io.IOException;
022: import java.sql.SQLException;
023: import java.util.ArrayList;
024: import java.util.Vector;
025:
026: /**
027: * This class is a helper class that handles the reading and parsing of data
028: * from a .csv file.
029: *
030: * @author Sinisa Milosevic
031: * @author Zoran Milakovic
032: */
033:
034: public class CsvWriter {
035: private CsvRandomAccessFile randomCsvFile;
036: private File file;
037: private String[] columnNames;
038: private String[] columnTypes;
039: private String[] columns;
040: private java.lang.String buf = null;
041: private char separator = CsvDriver.DEFAULT_SEPARATOR;
042: private long maxFileSize = CsvDriver.DEFAULT_FILE_MAXSIZE;
043: private String extension = CsvDriver.DEFAULT_EXTENSION;
044: private String tableName;
045: private String fileName;
046: private int counter;
047: private long current;
048: private long endLine;
049: private String charset;
050: private String QUOTE = "\"";
051: private boolean useQuotesEscape = true;
052:
053: /**
054: * Used with statement.
055: *
056: * @param fileName
057: * @param separator
058: * @param extension
059: * @param maxFileSize
060: * @throws java.lang.Exception
061: */
062: public CsvWriter(String fileName, char separator, String extension,
063: long maxFileSize, String charset, boolean useQuotes,
064: boolean useQuotesEscape) throws java.lang.Exception {
065: this .separator = separator;
066: this .extension = extension;
067: this .maxFileSize = maxFileSize;
068: if (!useQuotes)
069: this .QUOTE = "";
070: if (fileName != null) {
071: this .fileName = fileName;
072: this .randomCsvFile = new CsvRandomAccessFile(fileName,
073: charset);
074: this .file = new File(fileName);
075: String headerLine = this .randomCsvFile.readCsvLine();
076: columnNames = parseCsvLineAsHeader(headerLine);
077: }
078: this .charset = charset;
079: this .useQuotesEscape = useQuotesEscape;
080: counter = 1;
081: current = 0;
082: endLine = 0;
083:
084: }
085:
086: /**
087: * When use split files, this is used when file name is changed.
088: * @param name
089: * @throws Exception
090: */
091: public void setFileName(String name) throws Exception {
092: this .fileName = name;
093: if (this .randomCsvFile != null)
094: this .randomCsvFile.close();
095: this .file = new File(this .fileName);
096: this .randomCsvFile = new CsvRandomAccessFile(this .fileName,
097: charset);
098: }
099:
100: public void fillTableColumnNames() throws java.lang.Exception {
101: String headerLine = this .randomCsvFile.readCsvLine();
102: this .columnNames = parseCsvLineAsHeader(headerLine);
103: }
104:
105: /**
106: * Gets the columnNames attribute of the CsvReader object
107: *
108: * @return The columnNames value
109: * @since
110: */
111: public String[] getColumnNames() {
112: return columnNames;
113: }
114:
115: private String getNextFileName(String currentFileName) {
116: String newName = "";
117: String number = "";
118: //name without extension
119: String currentFileExtension = currentFileName.substring(
120: currentFileName.lastIndexOf("."), currentFileName
121: .length());
122: currentFileName = currentFileName.substring(0, currentFileName
123: .lastIndexOf("."));
124: if (currentFileExtension.endsWith(CsvDriver.FILE_NAME_EXT)) {
125: number += currentFileName.substring(currentFileName
126: .length() - 3, currentFileName.length());
127: long num = Long.valueOf(number).longValue() + 1;
128: if (num >= 100 && num < 1000)
129: number = String.valueOf(num);
130: else if (num >= 10 && num < 100)
131: number = "0" + String.valueOf(num);
132: else if (num > 1 && num < 10)
133: number = "00" + String.valueOf(num);
134: currentFileName = currentFileName.substring(0,
135: currentFileName.length() - 3);
136: newName = currentFileName + number + currentFileExtension;
137: } else {
138: newName = currentFileName.toUpperCase() + "001"
139: + this .extension + CsvDriver.FILE_NAME_EXT;
140: }
141: return newName;
142: }
143:
144: public String getTableName() {
145: if (tableName != null)
146: return tableName;
147:
148: int lastSlash = 0;
149: for (int i = fileName.length() - 1; i >= 0; i--)
150: if (fileName.charAt(i) == '/' || fileName.charAt(i) == '\\') {
151: lastSlash = i;
152: break;
153: }
154: tableName = fileName.substring(lastSlash + 1,
155: fileName.length() - 4);
156: return tableName;
157: }
158:
159: /**
160: * Get the value of the column at the specified index.
161: *
162: * @param columnIndex Description of Parameter
163: * @return The column value
164: * @since
165: */
166:
167: public String getColumn(int columnIndex) {
168: return columns[columnIndex];
169: }
170:
171: /**
172: * Get value from column at specified name.
173: * If the column name is not found, throw an error.
174: *
175: * @param columnName Description of Parameter
176: * @return The column value
177: * @exception SQLException Description of Exception
178: * @since
179: */
180:
181: public String getColumn(String columnName) throws SQLException {
182: for (int loop = 0; loop < columnNames.length; loop++) {
183: if (columnName.equalsIgnoreCase(columnNames[loop])
184: || columnName.equalsIgnoreCase(getTableName() + "."
185: + columnNames[loop])) {
186: return getColumn(loop);
187: }
188: }
189: throw new SQLException("Column '" + columnName + "' not found.");
190: }
191:
192: /**
193: *Description of the Method
194: *
195: * @return Description of the Returned Value
196: * @exception SQLException Description of Exception
197: * @since
198: */
199: private long lastCurrent = -1;
200:
201: public boolean next() throws SQLException, IOException {
202: columns = new String[columnNames.length];
203: String dataLine = null;
204: if (lastCurrent == -1) {
205: current = randomCsvFile.getFilePointer();
206: } else {
207: current = lastCurrent;
208: }
209:
210: try {
211: if (buf != null) {
212: // The buffer is not empty yet, so use this first.
213: dataLine = buf;
214: buf = null;
215: } else {
216: // read new line of data from input.
217: if (lastCurrent != -1)
218: randomCsvFile.seek(lastCurrent);
219: dataLine = this .randomCsvFile.readCsvLine();
220: }
221: if (dataLine == null) {
222: String nextFileName = getNextFileName(this .fileName);
223: if (new File(nextFileName).exists()) {
224: this .fileName = nextFileName;
225: if (randomCsvFile != null)
226: randomCsvFile.close();
227: randomCsvFile = new CsvRandomAccessFile(
228: this .fileName, charset);
229: counter = 1;
230: current = 0;
231: endLine = 0;
232: //skip header
233: dataLine = randomCsvFile.readCsvLine();
234: } else {
235: randomCsvFile.close();
236: return false;
237: }
238: }
239:
240: } catch (IOException e) {
241: throw new SQLException(e.toString());
242: }
243: columns = parseCsvLine(dataLine);
244: endLine = randomCsvFile.getFilePointer();
245: return true;
246: }
247:
248: /**
249: *Description of the Method
250: *
251: * @since
252: */
253: public void close() {
254: try {
255: this .file = null;
256: this .randomCsvFile.close();
257: buf = null;
258: } catch (Exception e) {
259: }
260: }
261:
262: /**
263: *
264: * @param line
265: * @return array with values or column names.
266: * @throws SQLException
267: */
268: protected String[] parseCsvLine(String line) throws SQLException {
269: ArrayList values = new ArrayList();
270: boolean inQuotedString = false;
271: String value = "";
272: String orgLine = line;
273: int currentPos = 0;
274: int fullLine = 0;
275: int currentColumn = 0;
276: char currentChar;
277:
278: line += separator;
279: long lineLength = line.length();
280: while (fullLine == 0) {
281: currentPos = 0;
282: while (currentPos < lineLength) {
283:
284: //handle BINARY columns
285: if (!(this .columnTypes.length <= currentColumn)) {
286: if (this .columnTypes[currentColumn]
287: .equals(CsvDriver.BINARY_TYPE)) {
288: String binaryValue = "";
289: currentChar = line.charAt(currentPos);
290: if (currentChar == ',') {
291: values.add(binaryValue); //binary value is null;
292: currentPos++;
293: } else if (currentChar == '"') {
294: if (line.charAt(currentPos + 1) == '"') {
295: values.add(binaryValue); //binary value is null
296: currentPos = currentPos + 3;
297: } else {
298: // take all until next separator, and that is value
299: // do not insert BinaryObject+index into line, just set right currentPos
300: // and insert value into vector
301: // binary value is always beteween quotes (")
302: binaryValue = line
303: .substring(currentPos);
304: binaryValue = binaryValue
305: .substring(1, binaryValue
306: .indexOf(separator) - 1);
307: values.add(binaryValue);
308: currentPos += binaryValue.length() + 3;
309: }
310: }
311: //set currentColumn++
312: currentColumn++;
313: continue;
314: }
315: } else {
316: throw new SQLException(
317: "Invalid csv format : file = "
318: + new File(fileName)
319: .getAbsolutePath()
320: + ", line = " + line);
321: }
322:
323: //parse one by one character
324: currentChar = line.charAt(currentPos);
325: if (value.length() == 0 && currentChar == '"'
326: && !inQuotedString) {
327: //enter here if we are at start of column value
328: currentPos++;
329: inQuotedString = true;
330: continue;
331: }
332:
333: if (currentChar == '"') {
334: //get next character
335: char nextChar = line.charAt(currentPos + 1);
336: //if we have "", consider it as ", and add it to value
337: if (nextChar == '"') {
338: value += currentChar;
339: currentPos++;
340: } else {
341: //enter here if we are at end of column value
342: if (!inQuotedString) {
343: throw new SQLException(
344: "Unexpected '\"' in position "
345: + currentPos + ". Line="
346: + orgLine);
347: }
348: if (inQuotedString && nextChar != separator) {
349: throw new SQLException("Expecting "
350: + separator + " in position "
351: + (currentPos + 1) + ". Line="
352: + orgLine);
353: }
354:
355: //set currentPos to comma after value
356: currentPos++;
357: //if value is empty string between double quotes consider it as empty string
358: //else if value is empty string between commas consider it as null value
359: values.add(value);
360: currentColumn++;
361: value = "";
362: inQuotedString = false;
363: }
364: }
365:
366: else {
367: //when we are at end of column value, and value is not inside of double quotes
368: if (currentChar == separator) {
369: //when have separator in data
370: if (inQuotedString) {
371: value += currentChar;
372: } else {
373: //if value is empty string between double quotes consider it as empty string
374: //else if value is empty string between commas consider it as null value
375: if (value.equals(""))
376: value = null;
377: values.add(value);
378: currentColumn++;
379: value = "";
380: }
381: } else {
382: value += currentChar;
383: }
384: }
385:
386: currentPos++;
387: } //end while
388:
389: if (inQuotedString) {
390: // Remove extra , added at start
391: value = value.substring(0, value.length() - 1);
392: try {
393: line = randomCsvFile.readCsvLine();
394: } catch (IOException e) {
395: throw new SQLException(e.toString());
396: }
397: } else {
398: fullLine = 1;
399: }
400:
401: }// end while( fullLine == 0 )
402: String[] retVal = new String[values.size()];
403: values.toArray(retVal);
404:
405: return retVal;
406: }
407:
408: /**
409: *
410: * @param line
411: * @return array with values or column names.
412: * @throws SQLException
413: */
414: protected String[] parseCsvLineAsHeader(String line)
415: throws SQLException {
416: //JOptionPane.showMessageDialog(null,"line = "+line);
417: Vector values = new Vector();
418: ArrayList columnTypesList = new ArrayList();
419: boolean inQuotedString = false;
420: String value = "";
421: String orgLine = line;
422: int currentPos = 0;
423: int fullLine = 0;
424:
425: while (fullLine == 0) {
426: currentPos = 0;
427: line += separator;
428: while (currentPos < line.length()) {
429: char currentChar = line.charAt(currentPos);
430: if (value.length() == 0 && currentChar == '"'
431: && !inQuotedString) {
432: currentPos++;
433: inQuotedString = true;
434: continue;
435: }
436: if (currentChar == '"') {
437: char nextChar = line.charAt(currentPos + 1);
438: if (nextChar == '"') {
439: value += currentChar;
440: currentPos++;
441: } else {
442: if (!inQuotedString) {
443: throw new SQLException(
444: "Unexpected '\"' in position "
445: + currentPos + ". Line="
446: + orgLine);
447: }
448: if (inQuotedString && nextChar != separator) {
449: throw new SQLException("Expecting "
450: + separator + " in position "
451: + (currentPos + 1) + ". Line="
452: + orgLine);
453: }
454: if (value.endsWith("-" + CsvDriver.BINARY_TYPE)) {
455: columnTypesList.add(CsvDriver.BINARY_TYPE);
456: value = value.substring(0, value
457: .indexOf("-"
458: + CsvDriver.BINARY_TYPE));
459: } else
460: columnTypesList.add(CsvDriver.VARCHAR_TYPE);
461: values.add(value);
462: value = "";
463: inQuotedString = false;
464: currentPos++;
465: }
466: } else {
467: if (currentChar == separator) {
468: if (inQuotedString) {
469: value += currentChar;
470: } else {
471: if (value.endsWith("-"
472: + CsvDriver.BINARY_TYPE)) {
473: columnTypesList
474: .add(CsvDriver.BINARY_TYPE);
475: value = value
476: .substring(
477: 0,
478: value
479: .indexOf("-"
480: + CsvDriver.BINARY_TYPE));
481: } else
482: columnTypesList
483: .add(CsvDriver.VARCHAR_TYPE);
484: values.add(value);
485: value = "";
486: }
487: } else {
488: value += currentChar;
489: }
490: }
491: currentPos++;
492: }
493: if (inQuotedString) {
494: // Remove extra , added at start
495: value = value.substring(0, value.length() - 1);
496: try {
497: line = randomCsvFile.readCsvLine();
498: } catch (IOException e) {
499: throw new SQLException(e.toString());
500: }
501: } else {
502: fullLine = 1;
503: }
504: }
505: String[] retVal = new String[values.size()];
506: values.copyInto(retVal);
507:
508: this .columnTypes = new String[columnTypesList.size()];
509: columnTypesList.toArray(columnTypes);
510:
511: return retVal;
512:
513: }
514:
515: protected boolean newLine(String[] colNames, String[] colValues)
516: throws IOException {
517: String newLine = "";
518: boolean more = true;
519: boolean createNewOutput = false;
520: //find out if file size is out of range, and if so create new file
521: while (more) {
522: if ((this .maxFileSize != -1)
523: && (this .file.length() > this .maxFileSize)) {
524: String newTableName = this
525: .getNextFileName(this .fileName);
526: this .fileName = newTableName;
527: this .createExtTable(this .columnNames, this .columnTypes,
528: newTableName);
529: } else {
530: createNewOutput = true;
531: more = false;
532: }
533: }
534: try {
535: if (createNewOutput) {
536: this .randomCsvFile.close();
537: this .randomCsvFile = new CsvRandomAccessFile(
538: this .fileName, charset);
539: }
540: } catch (Exception e) {
541: e.printStackTrace();
542: }
543:
544: for (int i = 0; i < columnNames.length; i++) {
545: boolean find = false;
546: for (int j = 0; j < colNames.length; j++) {
547: if (colNames[j].equalsIgnoreCase(columnNames[i])) {
548: if (colValues[j] == null)
549: newLine = newLine + separator;
550: else {
551: if (!this .useQuotesEscape)
552: colValues[j] = Utils.replaceAll(
553: colValues[j],
554: CsvSqlParser.DOUBLE_QUOTE_ESCAPE,
555: "\"");
556: newLine = newLine + this .QUOTE + colValues[j]
557: + this .QUOTE + separator;
558: }
559: find = true;
560: }
561: }
562: if (!find)
563: newLine = newLine + separator;
564: }
565: if (!newLine.equals(""))
566: newLine = newLine.substring(0, newLine.length() - 1);
567:
568: long l = this .randomCsvFile.length();
569: this .randomCsvFile.seek(l);
570: this .write(this .randomCsvFile, "\n" + newLine);
571: this .randomCsvFile.close();
572:
573: return true;
574: }
575:
576: protected boolean createTable(String[] colNames, String table)
577: throws IOException {
578: String newLine = "";
579: for (int i = 0; i < colNames.length; i++) {
580: newLine = newLine + this .QUOTE + colNames[i] + this .QUOTE
581: + separator;
582: }
583: if (!newLine.equals(""))
584: newLine = newLine.substring(0, newLine.length() - 1);
585:
586: this .fileName = table;
587:
588: this .file = new File(this .fileName);
589:
590: if (this .randomCsvFile != null)
591: this .randomCsvFile.close();
592: this .randomCsvFile = new CsvRandomAccessFile(fileName, charset);
593: this .write(this .randomCsvFile, newLine);
594: this .randomCsvFile.close();
595:
596: return true;
597: }
598:
599: protected boolean createExtTable(String[] colNames,
600: String[] colTypes, String table) throws IOException {
601: String newLine = "";
602: for (int i = 0; i < colNames.length; i++) {
603: if (colTypes[i].equals(CsvDriver.BINARY_TYPE))
604: newLine = newLine + this .QUOTE + colNames[i] + "-"
605: + colTypes[i] + this .QUOTE + separator;
606: else
607: newLine = newLine + this .QUOTE + colNames[i]
608: + this .QUOTE + separator;
609: }
610: if (!newLine.equals(""))
611: newLine = newLine.substring(0, newLine.length() - 1);
612:
613: this .fileName = table;
614:
615: this .file = new File(this .fileName);
616:
617: if (!this .file.exists()) {
618: CsvRandomAccessFile temp = new CsvRandomAccessFile(
619: this .fileName, charset);
620: this .write(temp, newLine);
621: temp.close();
622: }
623:
624: return true;
625: }
626:
627: protected boolean updateFields(String[] colNames,
628: String[] colValues, String[] colWhereNames,
629: String[] colWhereValues) throws IOException, SQLException {
630:
631: boolean isUpdated = false;
632:
633: while (next()) {
634: isUpdated = false;
635: counter++;
636: boolean find = false;
637: out: for (int i = 0; i < colWhereNames.length; i++) {
638: //compare values
639: if (!Utils.compareValues(getColumn(colWhereNames[i]),
640: colWhereValues[i]))
641: break out;
642: if (i == (colWhereNames.length - 1)) {
643: find = true;
644: }
645: }
646: //if there is no where clause
647: if (colWhereNames.length == 0)
648: find = true;
649:
650: //go to next line
651: lastCurrent = randomCsvFile.getFilePointer();
652: if (find) {
653: for (int i = 0; i < columnNames.length; i++) {
654: for (int j = 0; j < colNames.length; j++) {
655: if (colNames[j]
656: .equalsIgnoreCase(columnNames[i])) {
657: if (!this .useQuotesEscape)
658: colValues[j] = Utils
659: .replaceAll(
660: colValues[j],
661: CsvSqlParser.DOUBLE_QUOTE_ESCAPE,
662: "\"");
663: columns[i] = colValues[j];
664: }
665: }
666: }
667: String updatedLine = "";
668: for (int i = 0; i < columns.length; i++) {
669: if (columns[i] == null)
670: updatedLine = updatedLine + separator;
671: else {
672: updatedLine = updatedLine + this .QUOTE
673: + columns[i] + this .QUOTE + separator;
674: }
675:
676: }
677: if (!updatedLine.equals("")) {
678: randomCsvFile.seek(endLine);
679: String line = "";
680: String newLine = "";
681:
682: while ((newLine = randomCsvFile.readCsvLine()) != null) {
683: line += newLine + "\n";
684: }
685: if (line != null) {
686: if (!line.equals(""))
687: line = line.substring(0, line.length() - 1);
688: }
689: updatedLine = updatedLine.substring(0, updatedLine
690: .length() - 1);
691: randomCsvFile.seek(current);
692: if (Utils.isUTF16(this .charset))
693: this .write(randomCsvFile, "\n" + updatedLine);
694: else
695: this .write(randomCsvFile, updatedLine);
696: //go to next line
697: lastCurrent = randomCsvFile.getFilePointer();
698: if (randomCsvFile.getFilePointer() != randomCsvFile
699: .length()) {
700: this .write(randomCsvFile, "\n");
701: //go to next line
702: lastCurrent = randomCsvFile.getFilePointer();
703: if (line != null) {
704: if (!line.equals(""))
705: this .write(randomCsvFile, line);
706: randomCsvFile.setLength(randomCsvFile
707: .getFilePointer());
708: }
709: }
710: isUpdated = false;
711: }
712: }
713: }
714: return isUpdated;
715: }
716:
717: /**
718: * Write to file using specified encoding of string msg.
719: * @param file
720: * @param msg
721: */
722: private void write(CsvRandomAccessFile file, String line)
723: throws IOException {
724: try {
725: if (Utils.isUTF16(this .charset)) {
726: if (file.length() == 0)
727: file.write(line.getBytes(this .charset), 0, line
728: .getBytes(this .charset).length);
729: else
730: file.write(line.getBytes(this .charset), 2, line
731: .getBytes(this .charset).length - 2);
732: } else {
733: if (this .charset != null)
734: file.write(line.getBytes(charset));
735: else
736: file.write(line.getBytes());
737: }
738: } catch (IOException e) {
739: throw e;
740: }
741: }
742:
743: }
|